simple-urls / classes / class-lasso-db.php

class-lasso-db.php in Lasso Lite – Affiliate Link Manager & Product Displays 158, at classes/class-lasso-db.php

1,293 lines 32.5 KB

Raw Download Zip

1	<?php
2	/**
3	* Declare class DB
4	*
5	* @package DB
6	*/
7
8	namespace LassoLite\Classes;
9
10	use LassoLite\Classes\Setting_Enum;
11
12	use LassoLite\Admin\Constant;
13	use LassoLite\Models\Url_Details;
14	use LassoLite\Models\Model;
15	use LassoLite\Classes\Db\Revert_Repository;
16	use LassoLite\Models\Revert;
17
18	/**
19	* Lasso_DB
20	*/
21	class Lasso_DB {
22
23	/**
24	* Database name
25	*
26	* @var string
27	*/
28	public $dbname;
29
30	/**
31	* Database table prefix
32	*
33	* @var string
34	*/
35	public $prefix;
36
37	/**
38	* Current domain
39	*
40	* @var string
41	*/
42	public $current_domain;
43
44	/**
45	* WordPress posts table
46	*
47	* @var string
48	*/
49	public $posts;
50
51	/**
52	* WordPress postmeta table
53	*
54	* @var string
55	*/
56	public $postmeta;
57
58	/**
59	* WordPress terms table
60	*
61	* @var string
62	*/
63	public $terms;
64
65	/**
66	* WordPress term_taxonomy table
67	*
68	* @var string
69	*/
70	public $term_taxonomy;
71
72	/**
73	* WordPress term_relationships table
74	*
75	* @var string
76	*/
77	public $term_relationships;
78
79	/**
80	* WordPress options table
81	*
82	* @var string
83	*/
84	public $options;
85
86	/**
87	* Lasso amazon_products table
88	*
89	* @var string
90	*/
91	public $amazon_products;
92
93	/**
94	* Pretty Links table
95	*
96	* @var string
97	*/
98	public $pretty_links;
99
100	/**
101	* AAWP products table
102	*
103	* @var string
104	*/
105	public $aawp;
106
107	/**
108	* AAWP lists table
109	*
110	* @var string
111	*/
112	public $aawp_list;
113
114	/**
115	* Construction of Lasso_DB
116	*/
117	public function __construct() {
118	global $wpdb;
119
120	$this->dbname = $wpdb->dbname;
121	$this->prefix = $wpdb->prefix;
122	$this->current_domain = str_replace( 'https://', '', str_replace( 'http://', '', strtolower( get_site_url() ) ) );
123
124	// ? WP Tables
125	$this->posts = $wpdb->posts;
126	$this->postmeta = $wpdb->postmeta;
127	$this->terms = $wpdb->terms;
128	$this->term_taxonomy = $wpdb->term_taxonomy;
129	$this->term_relationships = $wpdb->term_relationships;
130	$this->options = $wpdb->options;
131
132	// ? Lasso Tables
133	$this->amazon_products = $wpdb->prefix . Constant::LASSO_AMAZON_PRODUCTS_DB;
134
135	// ? Import Tables (external products)
136	$this->pretty_links = $wpdb->prefix . 'prli_links';
137	$this->aawp = $wpdb->prefix . 'aawp_products';
138	$this->aawp_list = $wpdb->prefix . 'aawp_lists';
139	}
140
141	/**
142	* Get url detail
143	*
144	* @param int $lasso_id Lasso post id.
145	*/
146	public function get_url_details( $lasso_id ) {
147	$sql = '
148	SELECT *
149	FROM ' . ( new Url_Details() )->get_table_name() . '
150	WHERE lasso_id = %s
151	';
152	$sql = Url_Details::prepare( $sql, $lasso_id );
153
154	return $this->get_row( $sql );
155	}
156
157	/**
158	* Run query
159	*
160	* @param string $sql Sql query.
161	*/
162	public function query( $sql ) {
163	global $wpdb;
164
165	$results = $wpdb->query( $sql ); // phpcs:ignore
166	$this->log_error( $wpdb->last_error );
167
168	return $results;
169	}
170
171	/**
172	* Get row
173	* Get row from cache if existed
174	*
175	* @param string $sql Sql query.
176	* @param string $output Type of results.
177	* @param boolean $is_use_cache Is use cache.
178	*/
179	public function get_row( $sql, $output = 'OBJECT', $is_use_cache = false ) {
180	$results = false;
181	$cache_string = md5( trim( (string) $sql ) . $output );
182
183	if ( $is_use_cache ) {
184	$results = Cache_Per_Process::get_instance()->get_cache( $cache_string );
185	}
186
187	if ( false === $results ) {
188	global $wpdb;
189
190	$results = $wpdb->get_row( $sql, $output ); // phpcs:ignore
191	$this->log_error( $wpdb->last_error );
192
193	if ( $is_use_cache ) {
194	Cache_Per_Process::get_instance()->set_cache( $cache_string, $results );
195	}
196	}
197
198	return $results;
199	}
200
201	/**
202	* Get var
203	* Get var from cache if existed
204	*
205	* @param string $sql Sql query.
206	* @param boolean $is_use_cache Is use cache.
207	*/
208	public function get_var( $sql, $is_use_cache = false ) {
209	$results = false;
210	$cache_string = md5( trim( (string) $sql ) );
211
212	if ( $is_use_cache ) {
213	$results = Cache_Per_Process::get_instance()->get_cache( $cache_string );
214	}
215
216	if ( false === $results ) {
217	global $wpdb;
218
219	$results = $wpdb->get_var( $sql ); // phpcs:ignore
220	$this->log_error( $wpdb->last_error );
221
222	if ( $is_use_cache ) {
223	Cache_Per_Process::get_instance()->set_cache( $cache_string, $results );
224	}
225	}
226
227	return $results;
228	}
229
230	/**
231	* Get col
232	* Get col from cache if existed
233	*
234	* @param string $sql Sql query.
235	* @param boolean $is_use_cache Is use cache.
236	*/
237	public function get_col( $sql, $is_use_cache = false ) {
238	$results = false;
239	$cache_string = md5( trim( (string) $sql ) );
240
241	if ( $is_use_cache ) {
242	$results = Cache_Per_Process::get_instance()->get_cache( $cache_string );
243	}
244
245	if ( false === $results ) {
246	global $wpdb;
247
248	$results = $wpdb->get_col( $sql ); // phpcs:ignore
249	$this->log_error( $wpdb->last_error );
250
251	if ( $is_use_cache ) {
252	Cache_Per_Process::get_instance()->set_cache( $cache_string, $results );
253	}
254	}
255
256	return $results;
257	}
258
259	/**
260	* Print error log message to log file
261	*
262	* @param string $error Error message.
263	*/
264	public function log_error( $error ) {
265	if ( ! empty( $error ) ) {
266	if ( strpos( $error, 'Illegal mix of collations' ) !== false
267	\|\| strpos( $error, 'Unknown column' ) !== false
268	) {
269	Update_DB::create_tables();
270	}
271
272	// ? Add force write log for lasso_debug, to see what happen when Lasso call query.
273	trigger_error( '[lasso-lite-sql] ' . $error, E_USER_NOTICE ); // phpcs:ignore
274	}
275	}
276
277	/**
278	* Get lasso post id by product id and product_type
279	*
280	* @param string $product_id Product id.
281	* @param string $product_type Product type. Default is amazon.
282	*/
283	public function get_lasso_id_by_product_id_and_type( $product_id, $product_type = Amazon_Api::PRODUCT_TYPE ) {
284	if ( ! $product_id ) {
285	return false;
286	}
287
288	$detail = Url_Details::get_by_product_id_and_type( $product_id, $product_type );
289	if ( ! $detail \|\| ! $detail->lasso_id ) {
290	return false;
291	}
292
293	if ( get_post( $detail->lasso_id ) ) {
294	return (int) $detail->lasso_id;
295	}
296
297	return false;
298	}
299
300	/**
301	* Update data in url details table
302	*
303	* @param int $lasso_id Lasso post id.
304	* @param string $redirect_url Redirect url.
305	* @param string $base_domain Base domain.
306	* @param int $is_opportunity Is Opportunity.
307	* @param string $product_id Product id. Default to empty.
308	* @param string $product_type Product type. Default to empty.
309	*/
310	public function update_url_details( $lasso_id, $redirect_url, $base_domain, $is_opportunity, $product_id = '', $product_type = '' ) {
311	$url_detail_model = new Url_Details();
312	$redirect_url = trim( $redirect_url );
313	$redirect_url = addcslashes( $redirect_url, "'" );
314	$sql = '
315	INSERT INTO ' . $url_detail_model->get_table_name() . ' (lasso_id, redirect_url, base_domain, is_opportunity, product_id, product_type)
316	VALUES (%d, %s, %s, %d, %s, %s)
317	ON DUPLICATE KEY
318	UPDATE
319	redirect_url = %s,
320	base_domain = %s,
321	is_opportunity = %d,
322	product_id = %s,
323	product_type = %s
324	';
325	$prepare = Url_Details::prepare(
326	$sql,
327	// ? insert
328	$lasso_id,
329	$redirect_url,
330	$base_domain,
331	$is_opportunity,
332	$product_id,
333	$product_type,
334	// ? on duplicate update
335	$redirect_url,
336	$base_domain,
337	$is_opportunity,
338	$product_id,
339	$product_type
340	);
341
342	Url_Details::query( $prepare );
343
344	// ? Unset deprecated cache
345	Cache_Per_Process::get_instance()->un_set( Amazon_Api::OBJECT_KEY . '_' . Amazon_Api::FUNCTION_NAME_GET_LASSO_ID_BY_PRODUCT_ID_AND_TYPE . '_' . $product_id . '_' . $product_type );
346	}
347
348	/**
349	* Get importable url query
350	*
351	* @param bool $include_imported Include imported or not. Default to true.
352	* @param string $search Search text. Default to empty.
353	* @param string $group_by Group by column. Default to empty.
354	* @param string $filter_plugin Plugin name.
355	*/
356	public function get_importable_urls_query( $include_imported = true, $search = '', $group_by = '', $filter_plugin = null ) {
357	$is_prlipro_installed = $this->is_pretty_links_pro_installed();
358	$is_aawp_installed = $this->is_aawp_installed();
359	$posts_tbl = Model::get_wp_table_name( 'posts' );
360	$postmeta_tbl = Model::get_wp_table_name( 'postmeta' );
361	$pretty_link_tbl = Model::get_wp_table_name( 'prli_links' );
362
363	$post_status_allow = "('publish', 'pending', 'draft', 'future', 'private', 'inherit', 'trash')";
364
365	if ( '' === $group_by ) {
366	$group_by = 'BASE.import_source, BASE.id, BASE.post_type, BASE.post_name, BASE.post_title, BASE.check_status, BASE.check_disabled';
367	$select = '*';
368	$order_by = 'BASE.check_status, BASE.import_source, BASE.post_title';
369	} else {
370	$select = $group_by;
371	$order_by = $group_by;
372	}
373
374	if ( ! $filter_plugin ) {
375	$where = '';
376	} else {
377	$where = 'AND import_source = %s';
378	$where = Model::prepare( $where, $filter_plugin );
379	}
380
381	$support_plugin = Setting_Enum::SUPPORT_IMPORT_PLUGINS;
382	$support_plugin_flip = array_flip( $support_plugin );
383	$sql = '';
384
385	$revert_table = ( new Revert() )->get_table_name();
386
387	// ? SQL pre-processing
388	if ( $is_prlipro_installed && $support_plugin[ Setting_Enum::PRETTY_LINK_SLUG ] === $filter_plugin ) {
389	$prlipro_post_name = "
390	CASE
391	WHEN p.post_type = 'pretty-link'
392	THEN CONVERT(pl.slug USING utf8)
393	ELSE CONVERT(p.post_name USING utf8)
394	END as post_name,
395	";
396	$prlipro_join = '
397	LEFT JOIN
398	' . $this->pretty_links . " as pl
399	ON p.post_type = 'pretty-link'
400	AND p.id = pl.link_cpt_id
401	";
402	} else {
403	$prlipro_post_name = 'CONVERT(p.post_name USING utf8) as post_name,';
404	$prlipro_join = '';
405	}
406
407	// ? Start SQL Statement
408	// AAWP plugin
409	if ( ( $is_aawp_installed && empty( $filter_plugin ) ) \|\| ( $support_plugin[ Setting_Enum::AAWP_SLUG ] === $filter_plugin ) ) {
410	// ? aawp products
411	$sql .= "
412	SELECT
413	CONVERT(asin USING utf8) AS id,
414	'aawp' AS post_type,
415	'AAWP' AS import_source,
416	CONVERT(asin USING utf8) AS post_name,
417	CONVERT(title USING utf8) AS post_title,
418	'' AS check_status,
419	'' AS check_disabled
420	FROM
421	" . $this->aawp . ' AS ap
422	LEFT JOIN
423	' . $revert_table . ' AS r
424	ON CONVERT(ap.asin USING utf8) = CONVERT(r.old_uri USING utf8)
425	WHERE
426	r.old_uri IS NULL
427	UNION
428	';
429
430	// ? aawp lists
431	$sql .= "
432	SELECT
433	id,
434	'aawp_list' AS post_type,
435	'AAWP' AS import_source,
436	type AS post_name,
437	keywords AS post_title,
438	'' AS check_status,
439	'' AS check_disabled
440	FROM " . $this->aawp_list . ' AS ap
441
442	UNION
443	';
444	}
445
446	// ? Earnist plugin
447	$sql = $sql . "
448	SELECT
449	p.id,
450	p.post_type,
451	'Earnist' AS import_source,
452	CONVERT(p.post_name USING utf8) as post_name,
453	CONVERT(p.post_title USING utf8) AS post_title,
454	'' AS check_status,
455	'' AS check_disabled
456	FROM " . $posts_tbl . " AS p
457	WHERE
458	post_type = 'earnist'
459	AND p.ID NOT IN (
460	SELECT post_id
461	FROM " . $postmeta_tbl . "
462	WHERE meta_key = 'old_status'
463	AND meta_value != ''
464	)
465	AND post_status IN " . $post_status_allow;
466
467	// ? Pretty Links plugin
468	if ( $is_prlipro_installed && ( empty( $filter_plugin ) \|\| $support_plugin[ Setting_Enum::PRETTY_LINK_SLUG ] === $filter_plugin ) ) {
469	$sql = $sql . "
470	UNION
471
472	SELECT
473	p.id,
474	p.post_type,
475	'Pretty Links' AS import_source,
476	" . $prlipro_post_name . "
477	CONVERT(p.post_title USING utf8) AS post_title,
478	'' AS check_status,
479	'' AS check_disabled
480	FROM " . $posts_tbl . ' AS p
481	' . $prlipro_join . "
482	WHERE
483	post_type = 'pretty-link'
484	AND p.ID NOT IN (
485	SELECT post_id
486	FROM " . $postmeta_tbl . "
487	WHERE meta_key = 'old_status'
488	AND meta_value != ''
489	)
490	AND p.ID IN (
491	SELECT link_cpt_id
492	FROM " . $pretty_link_tbl . '
493	)
494	AND post_status IN ' . $post_status_allow;
495	}
496
497	// ? Easyazon plugin
498	if ( empty( $filter_plugin ) \|\| $support_plugin[ Setting_Enum::EASYAZON_SLUG ] === $filter_plugin ) {
499	$sql = $sql . "
500	UNION
501
502	SELECT DISTINCT
503	CONVERT(substring_index( substring_index(option_name, 'easyazon_item_', -1), '_', 1) USING utf8) AS id,
504	'easyazon' AS post_type,
505	'EasyAzon' AS import_source,
506	'' AS post_name,
507	CONVERT(substring_index( option_name, '_', 4) USING utf8) AS post_title,
508	CASE
509	WHEN CONVERT(substring_index( substring_index(option_name, 'easyazon_item_', -1), '_', 1) USING utf8) IN (
510	SELECT CONVERT(old_uri USING utf8) AS old_uri
511	FROM " . $revert_table . "
512	WHERE plugin = 'easyazon'
513	)
514	THEN 'checked'
515	ELSE ''
516	END AS check_status,
517	'' AS check_disabled
518	FROM " . $this->options . " AS ap
519	WHERE option_name LIKE 'easyazon_item_%'
520	";
521	}
522
523	// ? Easy Affiliate Link plugin - EAL is having 2 types "HTML code" and "Text Link", we only get "text" value.
524	if ( empty( $filter_plugin ) \|\| $support_plugin[ Setting_Enum::EASY_AFFILIATE_LINK_SLUG ] === $filter_plugin ) {
525	$sql = $sql . "
526	UNION
527
528	SELECT
529	po.ID as id,
530	po.post_type,
531	'Easy Affiliate Links' as import_source,
532	CONVERT(po.post_name USING utf8) as post_name,
533	CONVERT(po.post_title USING utf8) as post_title,
534	'' as check_status,
535	'' as check_disabled
536	FROM " . $this->posts . ' as po
537	INNER JOIN ' . $this->postmeta . ' as pom ON po.ID = pom.post_id
538	WHERE po.post_type = %s
539	AND pom.meta_value = %s
540	';
541
542	$sql = Model::prepare( $sql, Setting_Enum::EASY_AFFILIATE_LINK_SLUG, 'text' );
543	}
544
545	// ? Affiliate URL Automation plugin
546	if ( empty( $filter_plugin ) \|\| $support_plugin[ Setting_Enum::AFFILIATE_URL_SLUG ] === $filter_plugin ) {
547	$sql = $sql . "
548	UNION
549
550	SELECT
551	po.ID AS id,
552	po.post_type,
553	'Affiliate URLs' AS import_source,
554	CONVERT(po.post_name USING utf8) AS post_name,
555	CONVERT(po.post_title USING utf8) AS post_title,
556	'' AS check_status,
557	'' AS check_disabled
558	FROM " . $this->posts . ' AS po
559	INNER JOIN ' . $this->postmeta . ' AS pom
560	ON po.ID = pom.post_id
561	WHERE po.post_type = %s
562	AND pom.meta_key = %s
563	AND pom.meta_value IS NOT NULL
564	AND pom.meta_value <> ""
565	';
566
567	$sql = Model::prepare( $sql, Setting_Enum::AFFILIATE_URL_SLUG, '_affiliate_url_redirect' );
568	}
569
570	// ? Thirsty Affiliates plugin
571	if ( empty( $filter_plugin ) \|\| $support_plugin[ Setting_Enum::THIRSTYLINK_SLUG ] === $filter_plugin ) {
572	$sql = $sql . "
573	UNION
574
575	SELECT
576	po.ID AS id,
577	po.post_type,
578	'Thirsty Affiliates' AS import_source,
579	CONVERT(po.post_name USING utf8) AS post_name,
580	CONVERT(po.post_title USING utf8) AS post_title,
581	'' AS check_status,
582	'' AS check_disabled
583	FROM " . $this->posts . ' AS po
584	WHERE po.post_type = %s
585	AND po.ID IN (
586	SELECT post_id
587	FROM ' . $this->postmeta . '
588	WHERE meta_key = %s
589	AND meta_value IS NOT NULL
590	AND meta_value <> ""
591	)
592	';
593
594	$sql = Model::prepare( $sql, Setting_Enum::THIRSTYLINK_SLUG, '_ta_destination_url' );
595	}
596
597	// ? Lasso Pro plugin
598	$url_details_table = Model::get_wp_table_name( 'lasso_url_details' );
599	if ( Model::table_exists( $url_details_table ) && ( empty( $filter_plugin ) \|\| $support_plugin[ Setting_Enum::LASSO_PRO_SLUG ] === $filter_plugin ) ) {
600	$pro_revert_table = Model::get_wp_table_name( 'lasso_revert' );
601
602	$sql = $sql . "
603	UNION
604
605	SELECT
606	po.ID as id,
607	po.post_type,
608	'Lasso Pro' as import_source,
609	CONVERT(po.post_name USING utf8) as post_name,
610	CONVERT(po.post_title USING utf8) as post_title,
611	'' as check_status,
612	'' as check_disabled
613	FROM " . $this->posts . ' as po
614	INNER JOIN ' . $url_details_table . ' AS lud ON lud.lasso_id = po.ID
615	LEFT JOIN ' . $pro_revert_table . ' AS r
616	ON po.ID = r.lasso_id
617	AND r.plugin = %s
618	WHERE po.post_type = %s
619	AND lud.redirect_url IS NOT NULL
620	AND lud.redirect_url <> ""
621	AND r.lasso_id IS NULL
622	';
623
624	$sql = Model::prepare( $sql, Setting_Enum::SURL_SLUG, Setting_Enum::LASSO_PRO_SLUG );
625	}
626
627	if ( $include_imported ) {
628	if ( in_array(
629	$filter_plugin,
630	array(
631	$support_plugin[ Setting_Enum::PRETTY_LINK_SLUG ],
632	$support_plugin[ Setting_Enum::THIRSTYLINK_SLUG ],
633	$support_plugin[ Setting_Enum::EARNIST_SLUG ],
634	$support_plugin[ Setting_Enum::AFFILIATE_URL_SLUG ],
635	$support_plugin[ Setting_Enum::AAWP_SLUG ],
636	$support_plugin[ Setting_Enum::EASY_AFFILIATE_LINK_SLUG ],
637	$support_plugin[ Setting_Enum::LASSO_PRO_SLUG ],
638	),
639	true
640	)
641	) {
642	$r_plugin_where = "r.plugin IN ('%s')";
643	$r_plugin_where = sprintf( $r_plugin_where, $support_plugin_flip[ $filter_plugin ] );
644	} else {
645	$r_plugin_where = "r.plugin IN ('%s', '%s', '%s', '%s', '%s', '%s', '%s')";
646	$r_plugin_where = sprintf(
647	$r_plugin_where,
648	Setting_Enum::PRETTY_LINK_SLUG,
649	Setting_Enum::THIRSTYLINK_SLUG,
650	Setting_Enum::EARNIST_SLUG,
651	Setting_Enum::AFFILIATE_URL_SLUG,
652	Setting_Enum::AAWP_SLUG,
653	Setting_Enum::EASY_AFFILIATE_LINK_SLUG,
654	Setting_Enum::LASSO_PRO_SLUG,
655	);
656	}
657	$sql = $sql . "
658	UNION
659
660	SELECT
661	p.id,
662	p.post_type,
663	CASE
664	WHEN r.plugin = 'pretty-link'
665	THEN 'Pretty Links'
666	WHEN r.plugin = 'thirstylink'
667	THEN 'Thirsty Affiliates'
668	WHEN r.plugin = 'earnist'
669	THEN 'Earnist'
670	WHEN r.plugin = 'affiliate_url'
671	THEN 'Affiliate URLs'
672	WHEN r.plugin = 'aawp'
673	THEN 'AAWP'
674	WHEN r.plugin = 'easyazon'
675	THEN 'EasyAzon'
676	WHEN r.plugin = 'amalinks'
677	THEN 'Amalinks Pro'
678	WHEN r.plugin = 'easy_affiliate_link'
679	THEN 'Easy Affiliate Links'
680	WHEN r.plugin = '" . Setting_Enum::LASSO_PRO_SLUG . "'
681	THEN 'Lasso Pro'
682	ELSE 'Unknown'
683	END as import_source,
684	CASE
685	WHEN r.plugin = 'aawp'
686	THEN CONVERT(r.old_uri USING utf8)
687	ELSE CONVERT(p.post_name USING utf8)
688	END as post_name,
689	CONVERT(p.post_title USING utf8) as post_title,
690	'checked' as check_status,
691	'' as check_disabled
692	FROM " . $this->posts . ' as p
693	INNER JOIN
694	' . $revert_table . " as r
695	ON p.id = r.lasso_id
696	WHERE
697	$r_plugin_where
698	";
699
700	// ? AmaLinks Pro plugin - imported
701	if ( empty( $filter_plugin ) \|\| $support_plugin[ Setting_Enum::AFFILIATE_URL_SLUG ] === $filter_plugin ) {
702	$sql = $sql . "
703	UNION
704
705	SELECT DISTINCT
706	CONVERT(r.lasso_id USING utf8) as id,
707	'amalinkspro' as post_type,
708	'AmaLinks Pro' as import_source,
709	CASE
710	WHEN CONVERT(la.monetized_url USING utf8) != ''
711	THEN CONVERT(la.monetized_url USING utf8)
712	WHEN CONVERT(wpp.guid USING utf8) != ''
713	THEN CONVERT(wpp.guid USING utf8)
714	ELSE ''
715	END as post_name,
716	CASE
717	WHEN CONVERT(la.default_product_name USING utf8) != ''
718	THEN CONVERT(la.default_product_name USING utf8)
719	WHEN CONVERT(wpp.post_title USING utf8) != ''
720	THEN CONVERT(wpp.post_title USING utf8)
721	ELSE ''
722	END as post_title,
723	'checked' as check_status,
724	'' as check_disabled
725	FROM " . $revert_table . ' as r
726	LEFT JOIN ' . $this->posts . ' as wpp
727	ON r.lasso_id = wpp.ID
728	LEFT JOIN ' . $this->amazon_products . " as la
729	ON r.old_uri = la.amazon_id
730	WHERE r.plugin = 'amalinkspro'
731	";
732	}
733	}
734
735	$include_imported_where = $include_imported ? '' : 'AND check_status != "checked"';
736
737	$sql = '
738	SELECT ' . $select . '
739	FROM
740	(
741	' . $sql . '
742	) as BASE
743	WHERE
744	1=1
745	' . $where . '
746	' . $include_imported_where . '
747	' . $search . '
748	GROUP BY ' . $group_by . '
749	ORDER BY ' . $order_by;
750
751	return $sql;
752	}
753
754	/**
755	* Check whether pretty links pro plugin is install or not
756	*/
757	public function is_pretty_links_pro_installed() {
758	return Model::column_exists( $this->pretty_links, 'link_cpt_id' );
759	}
760
761	/**
762	* Check whether aawp plugin is installed or not
763	*/
764	public function is_aawp_installed() {
765	return Model::table_exists( $this->aawp );
766	}
767
768	/**
769	* Get pretty link by id
770	*
771	* @param int $id Id of pretty link.
772	*/
773	public function get_pretty_link_by_id( $id ) {
774	$sql = '
775	SELECT *
776	FROM ' . $this->pretty_links . '
777	WHERE link_cpt_id = ' . $id . ';
778	';
779
780	return $this->get_row( $sql );
781	}
782
783	/**
784	* Get aawp product
785	*
786	* @param string $product_id Amazon product id.
787	*/
788	public function get_aawp_product( $product_id ) {
789	$sql = '
790	SELECT *
791	FROM ' . $this->aawp . '
792	WHERE asin = %s';
793	$sql = Model::prepare( $sql, $product_id );
794
795	$row = $this->get_row( $sql );
796
797	if ( $row ) {
798	$lasso_amazon_api = new Amazon_Api();
799
800	$row_url = maybe_unserialize( $row->urls ?? $row->url ?? '' );
801	$url = $row_url['basic'] ?? $row_url;
802	$url = is_string( $url ) ? $url : '';
803
804	$row->url = $lasso_amazon_api->get_amazon_product_url( $url, true, false );
805
806	$images = $row->image_ids;
807	$images = explode( ',', $images );
808	$image_id = $images[0];
809	$image_url = 'https://m.media-amazon.com/images/I/' . $image_id . '.jpg';
810
811	$row->image_url = $image_url;
812	$row->features = maybe_unserialize( $row->features ?? array() ); // phpcs:ignore
813	}
814
815	return $row;
816	}
817
818	/**
819	* Get aawp lists
820	*
821	* @param string $id Id.
822	*/
823	public function get_aawp_list( $id ) {
824	$sql = '
825	SELECT *
826	FROM ' . $this->aawp_list . '
827	WHERE id = %s';
828	$sql = Model::prepare( $sql, $id );
829
830	return $this->get_row( $sql );
831	}
832
833	/**
834	* Check EasyAzon product id is imported or not
835	*
836	* @param string $asin Amazon product id.
837	*/
838	public function is_easyazon_product_imported( $asin ) {
839	global $wpdb;
840
841	$sql = '
842	select id, lasso_id
843	from ' . ( new Revert() )->get_table_name() . '
844	where old_uri = %s and plugin = \'easyazon\'
845	';
846	$prepare = $wpdb->prepare( $sql, $asin ); // phpcs:ignore
847
848	return $this->get_row( $prepare );
849	}
850
851	/**
852	* Get url detail by product id (Amazon/Extend product)
853	*
854	* @param string $product_id Product id.
855	* @param string $product_type Product type.
856	*/
857	public function get_url_details_by_product_id( $product_id, $product_type ) {
858	if ( ! $product_id ) {
859	return null;
860	}
861
862	global $wpdb;
863
864	$sql = '
865	SELECT *
866	FROM ' . ( new Url_Details() )->get_table_name() . ' as lud
867	LEFT JOIN ' . $this->posts . ' as wpp
868	ON lud.lasso_id = wpp.ID
869	WHERE lud.product_id = %s
870	AND wpp.ID is not null
871	AND wpp.post_status = "publish"
872	AND wpp.post_type = %s
873	AND lud.product_type = %s
874	ORDER BY lasso_id desc
875	';
876	$prepare = $wpdb->prepare( $sql, $product_id, Constant::LASSO_POST_TYPE, $product_type ); // phpcs:ignore
877
878	return $this->get_row( $prepare );
879	}
880
881	/**
882	* Process import
883	*
884	* @param int $id Post id.
885	* @param string $slug Link slug.
886	* @param string $old_uri Old URI.
887	* @param string $plugin Plugin name.
888	*/
889	public function process_import( $id, $slug, $old_uri, $plugin ) {
890	if ( empty( $id ) \|\| empty( $slug ) ) {
891	return false;
892	}
893
894	global $wpdb;
895	clean_post_cache( $id );
896
897	$result1 = true;
898	if ( ! in_array( $plugin, array( 'aawp', 'amalinkspro', 'easyazon' ), true ) ) {
899	// ? Flip post time and potentially the slug
900	$update_sql = '
901	UPDATE ' . $this->posts . '
902	SET
903	post_name = %s,
904	post_type = %s,
905	post_modified = NOW(),
906	post_modified_gmt = NOW()
907	WHERE ID = %d;
908	';
909	$update_sql = $wpdb->prepare( $update_sql, $slug, SIMPLE_URLS_SLUG, $id ); // phpcs:ignore
910	Model::query( $update_sql );
911	$result1 = SIMPLE_URLS_SLUG === get_post_type( $id );
912	}
913
914	// ? Log what we imported for potential reverts
915	$insert_sql = '
916	INSERT INTO ' . ( new Revert() )->get_table_name() . ' (lasso_id, old_uri, plugin, revert_dt)
917	VALUES (%d, %s, %s, NOW());
918	';
919	$prepare = Model::prepare( $insert_sql, $id, $old_uri, $plugin );
920	$result2 = Model::query( $prepare );
921
922	clean_post_cache( $id );
923
924	return $result1 && $result2;
925	}
926
927	/**
928	* Process revert
929	*
930	* @param int $id Post id.
931	* @param bool $custom_post_type It is custom post type or not. Default to true.
932	*/
933	public function process_revert( $id, $custom_post_type = true ) {
934	// ? Get post type from revert table
935	$result1 = true;
936	if ( empty( $id ) ) {
937	return false;
938	}
939
940	if ( $custom_post_type ) {
941	$revert_data = $this->get_revert_by_id( $id );
942	$post_type = get_post_type( $id );
943	if ( ! empty( $revert_data ) && Constant::LASSO_POST_TYPE === $post_type ) {
944	// ? Switch back
945	if ( isset( $revert_data->plugin ) && 'pretty-link' === $revert_data->plugin ) {
946	$pretty_link_data = $this->get_pretty_link_by_id( $id );
947	$update_sql = '
948	UPDATE ' . $this->posts . '
949	SET
950	post_name = %s,
951	post_type = %s
952	WHERE id = %d;
953	';
954	$update_sql = Model::prepare( $update_sql, $pretty_link_data->slug, $revert_data->plugin, $id );
955	} else {
956	$update_sql = '
957	UPDATE ' . $this->posts . '
958	SET post_type = %s
959	WHERE id = %d;
960	';
961	$update_sql = Model::prepare( $update_sql, $revert_data->plugin, $id );
962	}
963	$result1 = Model::query( $update_sql );
964	}
965	}
966
967	// ? Delete tracking record
968	$delete_sql = '
969	DELETE FROM ' . ( new Revert() )->get_table_name() . '
970	WHERE lasso_id = %d;
971	';
972	$delete_sql = Model::prepare( $delete_sql, $id );
973	$result2 = Model::query( $delete_sql );
974
975	clean_post_cache( $id );
976
977	return $result1 && $result2;
978	}
979
980	/**
981	* Get revert row by Lasso post id.
982	*
983	* @param int $id Lasso post id (matches `lasso_id` column, not revert table primary key).
984	*/
985	public function get_revert_by_id( $id ) {
986	return ( new Revert_Repository() )->get_by_lasso_id( $id );
987	}
988
989	/**
990	* Delete url detail
991	*
992	* @param int $lasso_id Lasso post id.
993	*/
994	public function delete_url_details( $lasso_id ) {
995	$sql = '
996	DELETE FROM ' . ( new Url_Details() )->get_table_name() . '
997	WHERE lasso_id = %d';
998	$sql = Model::prepare( $sql, $lasso_id );
999
1000	return Model::query( $sql );
1001	}
1002
1003	/**
1004	* Get EasyAzon product
1005	*
1006	* @param string $asin Amazon product id.
1007	*/
1008	public function get_easyazon_product( $asin ) {
1009	if ( ! $asin ) {
1010	return null;
1011	}
1012
1013	$search = 'easyazon_item_' . $asin . '%';
1014	$sql = '
1015	select option_value
1016	from ' . $this->options . '
1017	where option_name like %s
1018	order by option_id desc
1019	limit 1
1020	';
1021	$prepare = Model::prepare( $sql, $search ); // phpcs:ignore
1022
1023	return Model::get_row( $prepare );
1024	}
1025
1026	/**
1027	* Remove: all background processes of Lasso
1028	*/
1029	public function remove_all_lasso_processes() {
1030	$sql = '
1031	DELETE FROM ' . $this->options . "
1032	WHERE option_name like '%lassolite_%_batch_%'
1033	";
1034	$this->query( $sql );
1035	}
1036
1037	/**
1038	* Check whether the process is empty
1039	*/
1040	public function check_empty_process() {
1041	$count_query = '
1042	SELECT count(option_id) as `total`
1043	FROM ' . $this->options . "
1044	WHERE
1045	option_name LIKE '%lassolite_%_batch_%'
1046	AND option_value LIKE 'a:1:{i:0;i:%'
1047	";
1048	$total = $this->get_row( $count_query );
1049	$total = $total->total ?? 0;
1050	$total = intval( $total );
1051
1052	// ? delete empty processes if there are more 10 empty processes
1053	if ( $total > 10 ) {
1054	$this->remove_empty_process();
1055	}
1056	}
1057
1058	/**
1059	* Remove: empty process
1060	*/
1061	public function remove_empty_process() {
1062	$sql = '
1063	DELETE FROM ' . $this->options . "
1064	WHERE
1065	option_name LIKE '%lassolite_%_batch_%'
1066	AND option_value LIKE 'a:1:{i:0;i:%'
1067	";
1068	$this->query( $sql );
1069	}
1070
1071	/**
1072	* Paginate items by a sql query
1073	*
1074	* @param string $sql Sql query.
1075	* @param int $page Number of page.
1076	* @param int $limit Number of results. Default to 10.
1077	*/
1078	public function paginate( $sql, $page, $limit = 10 ) {
1079	$start_index = ( $page - 1 ) * $limit;
1080	return $sql . ' LIMIT ' . $start_index . ', ' . $limit;
1081	}
1082
1083	/**
1084	* Get revertable url query
1085	*
1086	* @param string $filter_plugin Plugin name. Default to null (all plugins).
1087	*/
1088	public function get_revertable_urls_query( $filter_plugin = null ) {
1089	$key = array_search( $filter_plugin, Setting_Enum::SUPPORT_IMPORT_PLUGINS, true );
1090
1091	if ( false === $key ) {
1092	$where = "`plugin` IN ('pretty-link', 'thirstylink', 'earnist', 'affiliate_url', 'aawp', 'easyazon', 'amalinkspro', '" . Setting_Enum::LASSO_PRO_SLUG . "')";
1093	} else {
1094	$where = '`plugin` = %s';
1095	$where = Model::prepare( $where, $key );
1096	}
1097
1098	$sql = '
1099	SELECT lasso_id AS import_id, `plugin` AS import_source
1100	FROM ' . ( new Revert() )->get_table_name() . '
1101	WHERE ' . $where . '
1102	';
1103
1104	return $sql;
1105	}
1106
1107	/**
1108	* Get easyazon option by option_name
1109	*
1110	* @param string $option_name A part of option name.
1111	* @return mixed
1112	*/
1113	public static function get_easyazon_option( $option_name ) {
1114	$sql = '
1115	SELECT option_value
1116	FROM ' . Model::get_wp_table_name( 'options' ) . '
1117	WHERE option_name LIKE %s
1118	';
1119
1120	$option_name = Helper::esc_like_query( $option_name, true );
1121	$sql = Model::prepare( $sql, $option_name );
1122
1123	$result = Model::get_var( $sql );
1124	$result = maybe_unserialize( $result );
1125
1126	return $result;
1127	}
1128
1129	/**
1130	* Get import plugin options to filter
1131	*
1132	* @param bool $get_count Get result count.
1133	* @return array\|int\|mixed
1134	*/
1135	public function get_import_plugins( $get_count = false ) {
1136	$sql = $this->get_importable_urls_query( true, '', 'BASE.import_source' );
1137
1138	if ( $get_count ) {
1139	return Model::get_count( $sql );
1140	}
1141
1142	$import_results = Model::get_results( $sql );
1143
1144	$result = array();
1145
1146	foreach ( $import_results as $import_result ) {
1147	$result[] = $import_result->import_source;
1148	}
1149
1150	return $result;
1151	}
1152
1153
1154	/**
1155	* Get lasso lite post by uri
1156	*
1157	* @param string $uri Uri.
1158	*/
1159	public function get_lasso_by_uri( $uri ) {
1160	if ( empty( $uri ) ) {
1161	return null;
1162	}
1163
1164	global $wpdb;
1165
1166	$explode = explode( '/', $uri );
1167	$post_name = end( $explode );
1168
1169	$sql = '
1170	SELECT *
1171	FROM ' . $this->posts . '
1172	WHERE post_name = %s AND post_type = %s
1173	';
1174	$prepare = $wpdb->prepare( $sql, $post_name, Constant::LASSO_POST_TYPE ); // phpcs:ignore
1175
1176	return $this->get_row( $prepare, 'OBJECT', true );
1177	}
1178
1179	/**
1180	* Get url detail by url
1181	*
1182	* @param string $url URL.
1183	*/
1184	public function get_url_details_by_url( $url ) {
1185	global $wpdb;
1186	$sql = '
1187	SELECT
1188	lud.*
1189	FROM
1190	' . ( new Url_Details() )->get_table_name() . ' AS lud
1191	LEFT JOIN
1192	' . $this->posts . " AS wpp
1193	ON
1194	lud.lasso_id = wpp.ID
1195	WHERE
1196	redirect_url LIKE %s
1197	AND wpp.ID != ''
1198	";
1199	$prepare = $wpdb->prepare( $sql, $url ); // phpcs:ignore
1200
1201	return $this->get_row( $prepare, 'OBJECT', true );
1202	}
1203
1204	/**
1205	* Check whether an amazon product existed in DB or not
1206	*
1207	* @param string $product_id Amazon product id.
1208	*/
1209	public function check_amazon_product_exist( $product_id ) {
1210	global $wpdb;
1211
1212	$query = '
1213	SELECT `amazon_id`
1214	FROM ' . $this->amazon_products . '
1215	WHERE `amazon_id` = %s
1216	';
1217
1218	$prepare = $wpdb->prepare( $query, $product_id ); // phpcs:ignore
1219
1220	return $this->get_row( $prepare, ARRAY_A );
1221	}
1222
1223	/**
1224	* Get lasso lite id from url by checking the post-meta key '_surl_redirect'
1225	*
1226	* @param string $url Url.
1227	* @return int
1228	*/
1229	public static function get_lasso_lite_id_by_url_from_post_meta( $url ) {
1230	$url_without_slash_at_the_end = rtrim( $url, '/' );
1231	$url_with_slash_at_the_end_wildcard = rtrim( $url, '/' ) . '_';
1232
1233	$sql = '
1234	SELECT p.ID
1235	FROM ' . Model::get_wp_table_name( 'posts' ) . ' AS p
1236	INNER JOIN ' . Model::get_wp_table_name( 'postmeta' ) . ' AS pm
1237	ON pm.post_id = p.ID
1238	WHERE p.post_type = %s
1239	AND pm.meta_key = %s
1240	AND ( pm.meta_value = %s OR pm.meta_value LIKE %s )
1241	';
1242	$sql = Model::prepare( $sql, Constant::LASSO_POST_TYPE, '_surl_redirect', $url_without_slash_at_the_end, $url_with_slash_at_the_end_wildcard );
1243
1244	$lasso_id = Model::get_var( $sql );
1245
1246	return intval( $lasso_id );
1247	}
1248
1249	/**
1250	* Get Lasso Pro amazon product
1251	*
1252	* @param string $product_id Amazon product id.
1253	*/
1254	public function get_lasso_pro_amazon_product( $product_id ) {
1255	$sql = '
1256	SELECT amazon_id, base_url, default_image AS image_url, default_product_name, latest_price, is_prime,
1257	currency, savings_amount, savings_percent, savings_basis, out_of_stock, last_updated, rating, reviews, features
1258	FROM ' . Model::get_wp_table_name( 'lasso_amazon_products' ) . '
1259	WHERE amazon_id = %s';
1260	$sql = Model::prepare( $sql, $product_id );
1261
1262	$row = $this->get_row( $sql );
1263
1264	if ( $row ) {
1265	$lasso_amazon_api = new Amazon_Api();
1266	$row->url = $lasso_amazon_api->get_amazon_product_url( $row->base_url, true, false );
1267	$row->features = $row->features ? json_decode( $row->features ) : array();
1268	$row->quantity = 0 === intval( $row->out_of_stock ) ? 200 : 0;
1269	}
1270
1271	return $row;
1272	}
1273
1274	/**
1275	* Total record url detail
1276	*/
1277	public function get_total_url_detail() {
1278	$wp_posts_table = Model::get_wp_table_name( 'posts' );
1279	$url_details_table = ( new Url_Details() )->get_table_name();
1280
1281	$sql = '
1282	SELECT COUNT(*) AS total_url
1283	FROM ' . $wp_posts_table . ' AS p
1284	INNER JOIN ' . $url_details_table . ' AS llud
1285	ON p.ID = llud.lasso_id
1286	WHERE llud.base_domain = %s
1287	AND p.post_status = %s
1288	';
1289	$sql = Model::prepare( $sql, 'amazon.com', 'publish' );
1290	return $this->get_row( $sql );
1291	}
1292	}
1293