simple-urls / models / class-amazon-products.php

class-amazon-products.php in Lasso Lite – Affiliate Link Manager & Product Displays 153, at models/class-amazon-products.php

165 lines 3.8 KB

Raw Download Zip

1	<?php
2	/**
3	* Models
4	*
5	* @package Models
6	*/
7
8	namespace LassoLite\Models;
9
10	use LassoLite\Admin\Constant;
11
12	use LassoLite\Models\Url_Details;
13
14	/**
15	* Model
16	*/
17	class Amazon_Products extends Model {
18	/**
19	* Table name
20	*
21	* @var string
22	*/
23	protected $table = 'lasso_lite_amazon_products';
24
25	/**
26	* Columns of the table
27	*
28	* @var array
29	*/
30	protected $columns = array(
31	'amazon_id',
32	'default_product_name',
33	'latest_price',
34	'base_url',
35	'monetized_url',
36
37	'default_image',
38	'out_of_stock',
39	'is_prime',
40	'currency',
41	'savings_amount',
42
43	'savings_percent',
44	'savings_basis',
45	'features',
46	'is_manual',
47	'last_updated',
48	);
49
50	/**
51	* Primary key of the table
52	*
53	* @var string
54	*/
55	protected $primary_key = 'amazon_id';
56
57	/**
58	* Create table
59	*/
60	public function create_table() {
61	$columns_sql = '
62	amazon_id varchar(20) NOT NULL,
63	default_product_name varchar(2000) NOT NULL,
64	latest_price varchar(32) NULL,
65	base_url varchar(2000) NOT NULL,
66	monetized_url varchar(2000) NULL,
67	default_image varchar(10000) NULL,
68	out_of_stock TINYINT(1) NULL DEFAULT 0,
69	is_prime TINYINT(1) NOT NULL DEFAULT 0,
70	currency VARCHAR(5) NOT NULL DEFAULT \'USD\',
71	savings_amount VARCHAR(10) NULL,
72	savings_percent INT(10) NULL,
73	savings_basis VARCHAR(10) NULL,
74	features TEXT NULL,
75	is_manual TINYINT(1) NOT NULL DEFAULT 0,
76	rating VARCHAR(10) NULL DEFAULT 0,
77	reviews INT(11) NULL DEFAULT 0,
78	last_updated datetime NOT NULL,
79	PRIMARY KEY (amazon_id)
80	';
81	$sql = '
82	CREATE TABLE ' . $this->get_table_name() . ' (
83	' . $columns_sql . '
84	) ' . $this->get_charset_collate();
85
86	return $this->modify_table( $sql, $this->get_table_name() );
87	}
88
89	/**
90	* Count total products in wp_lasso_amazon_products table
91	*/
92	public static function count_amazon_product_in_db() {
93	$amz_tbl = self::get_sql_query_amazon_products_need_to_be_updated();
94	$mysql = '
95	select count(amz_tbl.amazon_id) as count
96	from (' . $amz_tbl . ') as amz_tbl
97	';
98
99	$result = Model::get_col( $mysql );
100
101	return intval( $result[0] ?? 0 );
102	}
103
104	/**
105	* Get mysql query to get amazon products need to be update pricing
106	*/
107	public static function get_sql_query_amazon_products_need_to_be_updated() {
108	$now = gmdate( 'Y-m-d H:i:s', time() );
109	$posts = Model::get_wp_table_name( 'posts' );
110	$amazon_products = new Amazon_Products();
111	$url_details = new Url_Details();
112	$sql = '
113	SELECT
114	ap.*
115	FROM ' . $url_details->get_table_name() . ' AS ud
116	INNER JOIN ' . $posts . ' AS p
117	ON ud.lasso_id = p.ID
118	INNER JOIN ' . $amazon_products->get_table_name() . ' AS ap
119	ON ap.amazon_id = ud.product_id
120	WHERE p.post_type = %s
121	AND p.post_status = %s
122	AND ud.product_type = %s
123	AND ap.last_updated < DATE_SUB(%s, INTERVAL 24 HOUR)
124	ORDER BY
125	ap.last_updated ASC
126	';
127	$prepare = Model::prepare( $sql, Constant::LASSO_POST_TYPE, 'publish', 'amazon', $now );
128
129	return $prepare;
130	}
131
132	/**
133	* Get products in wp_lasso_amazon_products table
134	*
135	* @param int $limit Number of results. Default to 100.
136	*/
137	public static function get_amazon_product_in_db( $limit = 100 ) {
138	$mysql = self::get_sql_query_amazon_products_need_to_be_updated();
139	$mysql .= '
140	LIMIT %d
141	';
142	$prepare = Model::prepare( $mysql, $limit ); // phpcs:ignore
143
144	return Model::get_results( $prepare, ARRAY_A );
145	}
146
147	/**
148	* Update a field for an Amazon product
149	*
150	* @param string $amazon_id Amazon product id.
151	* @param string $field_name Field name.
152	* @param string $field_value Field value.
153	*/
154	public function update_amazon_field( $amazon_id, $field_name, $field_value ) {
155	$sql = '
156	update ' . $this->get_table_name() . '
157	set `' . $field_name . '` = %s
158	where amazon_id = %s
159	';
160	$prepare = Model::prepare( $sql, $field_value, $amazon_id ); // phpcs:ignore
161
162	return Model::query( $prepare ); // phpcs:ignore
163	}
164	}
165