mainwp / class / class-mainwp-db-client.php

class-mainwp-db-client.php in MainWP Dashboard: Self-hosted WordPress Management for Agencies 5.3, at class/class-mainwp-db-client.php

1,375 lines 51.1 KB

Raw Download Zip

1	<?php
2	/**
3	* MainWP Database Client
4	*
5	* This file handles all interactions with the Client DB.
6	*
7	* @package MainWP/Dashboard
8	*/
9
10	namespace MainWP\Dashboard;
11
12	/**
13	* Class MainWP_DB_Client
14	*
15	* @package MainWP\Dashboard
16	*/
17	class MainWP_DB_Client extends MainWP_DB { // phpcs:ignore Generic.Classes.OpeningBraceSameLine.ContentAfterBrace -- NOSONAR.
18
19	// phpcs:disable WordPress.DB.RestrictedFunctions, WordPress.DB.PreparedSQL.NotPrepared, Generic.Metrics.CyclomaticComplexity -- unprepared SQL ok, accessing the database directly to custom database functions.
20
21	/**
22	* Private static variable to hold the single instance of the class.
23	*
24	* @static
25	*
26	* @var mixed Default null
27	*/
28	private static $instance = null;
29
30	/**
31	* Method instance()
32	*
33	* Create public static instance.
34	*
35	* @static
36	* @return MainWP_DB_Client
37	*/
38	public static function instance() {
39	if ( null === static::$instance ) {
40	static::$instance = new self();
41	}
42	return static::$instance;
43	}
44
45	/**
46	* Constructor.
47	*
48	* Run each time the class is called.
49	*/
50	public function __construct() {
51	parent::__construct();
52	add_filter( 'mainwp_db_install_tables', array( $this, 'hook_db_install_tables' ), 10, 3 );
53	}
54
55	/**
56	* Method hook_db_install_tables()
57	*
58	* Get the query to install db tables.
59	*
60	* @param array $sql input filter.
61	* @param string $currentVersion Current db Version.
62	* @param string $charset_collate charset collate.
63	*
64	* @return array $sql queries.
65	*/
66	public function hook_db_install_tables( $sql, $currentVersion, $charset_collate ) {
67
68	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_clients' ) . " (
69	client_id int(11) NOT NULL auto_increment,
70	image varchar(255) NOT NULL default '',
71	name varchar(255) NOT NULL,
72	address_1 varchar(255) NOT NULL default '',
73	address_2 varchar(255) NOT NULL default '',
74	city varchar(100) NOT NULL default '',
75	zip varchar(32) NOT NULL default '',
76	state varchar(200) NOT NULL default '',
77	country varchar(200) NOT NULL default '',
78	note longtext NOT NULL default '',
79	selected_icon_info varchar(64) NOT NULL default '',
80	client_email varchar(191) NOT NULL,
81	client_phone varchar(100) NOT NULL default '',
82	client_facebook varchar(255) NOT NULL default '',
83	client_twitter varchar(255) NOT NULL default '',
84	client_instagram varchar(255) NOT NULL default '',
85	client_linkedin varchar(255) NOT NULL default '',
86	created int(11) NOT NULL DEFAULT 0,
87	`suspended` tinyint(1) NOT NULL DEFAULT 0,
88	primary_contact_id int(11) NOT NULL default 0";
89
90	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '8.61', '<=' ) ) {
91	$tbl .= ',
92	PRIMARY KEY (client_id)';
93	}
94	$tbl .= ') ' . $charset_collate;
95	$sql[] = $tbl;
96
97	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_clients_fields' ) . ' (
98	field_id int(11) NOT NULL auto_increment,
99	field_name varchar(191) NOT NULL DEFAULT "",
100	field_desc varchar(255) NOT NULL DEFAULT "",
101	client_id int(11) NOT NULL,
102	UNIQUE KEY client_id_field_name (client_id,field_name)';
103
104	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '8.61', '<=' ) ) {
105	$tbl .= ',
106	PRIMARY KEY (`field_id`) ';
107	}
108
109	$tbl .= ') ' . $charset_collate;
110	$sql[] = $tbl;
111
112	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_clients_field_values' ) . ' (
113	value_id int(11) NOT NULL auto_increment,
114	field_id int(11) NOT NULL,
115	field_value longtext NOT NULL DEFAULT "",
116	value_client_id int(11) NOT NULL,
117	UNIQUE KEY value_client_id_field_id (value_client_id, field_id)';
118
119	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '8.61', '<=' ) ) {
120	$tbl .= ',
121	PRIMARY KEY (`value_id`) ';
122	}
123
124	$tbl .= ') ' . $charset_collate;
125	$sql[] = $tbl;
126
127	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_clients_contacts' ) . ' (
128	contact_id int(11) NOT NULL auto_increment,
129	contact_client_id int(11) NOT NULL,
130	contact_email varchar(191) NOT NULL,
131	contact_image varchar(255) NOT NULL default "",
132	contact_icon_info varchar(64) NOT NULL default "",
133	contact_name varchar(255) NOT NULL,
134	contact_phone varchar(100) NOT NULL default "",
135	contact_role varchar(255) NOT NULL default "",
136	facebook varchar(255) NOT NULL default "",
137	twitter varchar(255) NOT NULL default "",
138	instagram varchar(255) NOT NULL default "",
139	linkedin varchar(255) NOT NULL default "",
140	UNIQUE KEY contact_email(contact_email)';
141
142	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '8.69', '<=' ) ) {
143	$tbl .= ',
144	PRIMARY KEY (contact_id)';
145	}
146	$tbl .= ') ' . $charset_collate;
147	$sql[] = $tbl;
148
149	return $sql;
150	}
151
152	/**
153	* Method check_to_updates_reports_data_861()
154	*
155	* Check version less than 8.61 to import reports data.
156	*
157	* @param string $currentVersion Current db Version.
158	*
159	* @return void.
160	*/
161	public function check_to_updates_reports_data_861( $currentVersion ) {
162
163	$convert_pro_reports_dt = false;
164	$convert_client_reports_dt = false;
165	if ( is_plugin_active( 'mainwp-pro-reports-extension/mainwp-pro-reports-extension.php' ) ) {
166	$convert_pro_reports_dt = true;
167	} elseif ( is_plugin_active( 'mainwp-client-reports-extension/mainwp-client-reports-extension.php' ) ) {
168	$convert_client_reports_dt = true;
169	}
170
171	if ( ! $convert_pro_reports_dt && ! $convert_client_reports_dt ) {
172	$rslt = $this->query( "SHOW TABLES LIKE '" . $this->table_name( 'pro_reports_token' ) . "'" );
173	if ( static::num_rows( $rslt ) ) {
174	$convert_pro_reports_dt = true;
175	}
176
177	$rslt = $this->query( "SHOW TABLES LIKE '" . $this->table_name( 'client_report_token' ) . "'" );
178	if ( static::num_rows( $rslt ) ) {
179	$convert_client_reports_dt = true;
180	}
181	}
182
183	$this->insert_client_fields_861( $currentVersion );
184
185	if ( $convert_pro_reports_dt ) {
186	$this->pro_reports_check_updates_861( $currentVersion );
187	} elseif ( $convert_client_reports_dt ) {
188	$this->client_reports_check_updates_861( $currentVersion );
189	}
190	}
191
192	/**
193	* Method insert_client_fields_861()
194	*
195	* Check version less than 8.61 to import client reports data.
196	*
197	* @param string $currentVersion Current db Version.
198	*
199	* @return null.
200	*/
201	public function insert_client_fields_861( $currentVersion ) {
202
203	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '8.61', '>' ) ) {
204	return;
205	}
206
207	$default_tokens = array(
208	'client.name' => 'Displays the Client Name',
209	'client.contact.name' => 'Displays the Client Contact Name',
210	'client.contact.address.1' => 'Displays the Client Contact Address 1',
211	'client.contact.address.2' => 'Displays the Client Contact Address 2',
212	'client.city' => 'Displays the Client City',
213	'client.state' => 'Displays the Client State',
214	'client.zip' => 'Displays the Client Zip',
215	'client.phone' => 'Displays the Client Phone',
216	'client.email' => 'Displays the Client Email',
217	'client.note' => 'Displays the Client Note',
218	);
219
220	// create or update default token.
221	foreach ( $default_tokens as $field_name => $field_desc ) {
222	$current = $this->get_client_fields_by( 'field_name', $field_name );
223	if ( $current ) {
224	$this->update_client_field(
225	$current->field_id,
226	array(
227	'field_name' => $field_name,
228	'field_desc' => $field_desc,
229	)
230	);
231	} else {
232	$this->add_client_field(
233	array(
234	'field_name' => $field_name,
235	'field_desc' => $field_desc,
236	'client_id' => 0,
237	)
238	);
239
240	}
241	}
242	}
243
244	/**
245	* Method client_reports_check_updates_861()
246	*
247	* Check version less than 8.61 to import client reports data.
248	*
249	* @param string $currentVersion Current db Version.
250	*
251	* @return null.
252	*/
253	public function client_reports_check_updates_861( $currentVersion ) {
254
255	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '8.61', '>' ) ) {
256	return;
257	}
258
259	$tokens = $this->client_reports_get_tokens();
260	$sites_tokens_values = $this->client_reports_get_site_token_values();
261
262	$this->reports_check_updates_861( $tokens, $sites_tokens_values );
263	}
264
265	/**
266	* Method pro_reports_check_updates_861()
267	*
268	* Check version less than 8.61 to import pro reports data.
269	*
270	* @param string $currentVersion Current db Version.
271	*
272	* @return null.
273	*/
274	public function pro_reports_check_updates_861( $currentVersion ) {
275
276	if ( empty( $currentVersion ) \|\| version_compare( $currentVersion, '8.61', '>' ) ) {
277	return;
278	}
279
280	$tokens = $this->pro_reports_get_tokens();
281	$sites_tokens_values = $this->pro_reports_get_site_token_values();
282
283	$this->reports_check_updates_861( $tokens, $sites_tokens_values );
284	}
285
286
287	/**
288	* Method reports_check_updates_861()
289	*
290	* Check version less than 8.61 to import reports data.
291	*
292	* @param array $tokens Tokens.
293	* @param array $sites_tokens_values Tokens values.
294	*
295	* @return void.
296	*/
297	public function reports_check_updates_861( $tokens, $sites_tokens_values ) { // phpcs:ignore -- NOSONAR complexex function. Current complexity is the only way to achieve desired results, pull request solutions appreciated.
298
299	$default_client_fields = MainWP_Client_Handler::get_default_client_fields();
300	$default_contact_fields = MainWP_Client_Handler::get_default_contact_fields();
301
302	$compatible_tokens = MainWP_Client_Handler::get_compatible_tokens();
303
304	$ids_to_tokens = array();
305
306	if ( $tokens ) {
307	foreach ( $tokens as $token ) {
308	$tok_name = $token->token_name;
309	$ids_to_tokens[ $token->id ] = array(
310	'token_name' => $tok_name,
311	'token_desc' => $token->token_description,
312	);
313	}
314	}
315
316	$ids_to_tokens_values = array();
317
318	if ( $sites_tokens_values ) {
319	foreach ( $sites_tokens_values as $token ) {
320	if ( ! isset( $ids_to_tokens[ $token->token_id ] ) ) {
321	continue;
322	}
323	if ( empty( $token->token_value ) ) {
324	continue;
325	}
326	$tok_name = $ids_to_tokens[ $token->token_id ]['token_name'];
327
328	$ids_to_tokens_values[ $token->site_id ][ $tok_name ] = array(
329	'token_value' => $token->token_value,
330	'token_desc' => $ids_to_tokens[ $token->token_id ]['token_desc'],
331	);
332	}
333	}
334
335	$clients_to_add = array();
336	$primary_contacts_to_add = array();
337	$custom_tokens_to_add = array();
338
339	foreach ( $ids_to_tokens_values as $site_id => $tokens ) {
340
341	if ( empty( $tokens['client.email'] ) ) {
342	continue;
343	}
344
345	$email = $tokens['client.email']['token_value'];
346
347	if ( ! isset( $clients_to_add[ $email ] ) ) {
348	$clients_to_add[ $email ] = array();
349	}
350
351	if ( ! isset( $clients_to_add[ $email ]['site_ids'] ) ) {
352	$clients_to_add[ $email ]['site_ids'] = array();
353	}
354
355	$clients_to_add[ $email ]['site_ids'][] = $site_id;
356
357	// prepare clients to add.
358	foreach ( $default_client_fields as $field_name => $field ) {
359	if ( ! isset( $tokens[ $field_name ] ) ) {
360	continue;
361	}
362	$clients_to_add[ $email ][ $field['db_field'] ] = $tokens[ $field_name ]['token_value'];
363	}
364
365	$flip_compatible_tokens = array_flip( $compatible_tokens ); // flip: new_token_name => old_token_name.
366	// prepare primary contacts to add.
367	foreach ( $default_contact_fields as $field_name => $field ) {
368
369	// $field_name - new_token_name.
370	// $tok_name - old_token_name.
371	$tok_name = isset( $flip_compatible_tokens[ $field_name ] ) ? $flip_compatible_tokens[ $field_name ] : $field_name;
372	if ( ! isset( $tokens[ $tok_name ] ) ) {
373	continue;
374	}
375
376	$primary_contacts_to_add[ $email ][ $field['db_field'] ] = $tokens[ $tok_name ]['token_value'];
377	}
378
379	foreach ( $tokens as $tok_name => $tok_value_desc ) {
380	if ( isset( $default_client_fields[ $tok_name ] ) \|\| isset( $default_contact_fields[ $tok_name ] ) ) {
381	continue;
382	}
383	if ( 'client.site.url' === $tok_name \|\| 'client.site.name' === $tok_name ) { // avoid those tokens.
384	continue;
385	}
386	if ( isset( $compatible_tokens[ $tok_name ] ) ) {
387	continue;
388	}
389	$custom_tokens_to_add[ $email ][ $tok_name ] = $tok_value_desc;
390	}
391	}
392
393	$emails_to_ids = array();
394	foreach ( $clients_to_add as $email => $client_add ) {
395	$selected_sites = array();
396	if ( isset( $client_add['site_ids'] ) ) {
397	$selected_sites = $client_add['site_ids'];
398	unset( $client_add['site_ids'] );
399	}
400
401	$inserted = $this->update_client( $client_add ); // add new client.
402
403	if ( $inserted ) {
404	$client_id = $inserted->client_id;
405	$this->update_selected_sites_for_client( $client_id, $selected_sites );
406	$emails_to_ids[ $email ] = $client_id;
407	}
408	}
409
410	foreach ( $emails_to_ids as $email => $client_id ) {
411
412	if ( isset( $primary_contacts_to_add[ $email ] ) ) {
413	$contact_add = $primary_contacts_to_add[ $email ];
414	$contact_add['contact_client_id'] = $client_id;
415	$inserted = $this->update_client_contact( $contact_add );
416	if ( $inserted ) {
417	$params = array(
418	'client_id' => $client_id, // update the client.
419	'primary_contact_id' => $inserted->contact_id,
420	);
421	$this->update_client( $params );
422	}
423	}
424
425	if ( isset( $custom_tokens_to_add[ $email ] ) ) {
426	$custom_tokens = $custom_tokens_to_add[ $email ];
427	foreach ( $custom_tokens as $tok_name => $tok_value_desc ) {
428	$field_id = 0;
429	$current = $this->get_client_fields_by( 'field_name', $tok_name ); // get general field by name.
430	if ( $current ) {
431	$field_id = $current->field_id;
432	} else {
433	$field = array(
434	'field_name' => $tok_name,
435	'field_desc' => $tok_value_desc['token_desc'],
436	'client_id' => 0, // it is general tokens.
437	);
438	$new_field = $this->add_client_field( $field );
439	if ( $new_field ) {
440	$field_id = $new_field->field_id;
441	}
442	}
443
444	if ( $field_id ) {
445	$this->update_client_field_value( $field_id, $tok_value_desc['token_value'], $client_id );
446	}
447	}
448	}
449	}
450	}
451
452
453	/**
454	* Method update_client.
455	*
456	* Create or update client.
457	*
458	* @param array $data Client data.
459	* @param bool $throw_out Throw or return error.
460	*
461	* @throws MainWP_Exception On errors.
462	*
463	* @return bool.
464	*/
465	public function update_client( $data, $throw_out = false ) {
466	if ( empty( $data ) \|\| ! is_array( $data ) ) {
467	return false;
468	}
469
470	$client_id = isset( $data['client_id'] ) ? (int) $data['client_id'] : 0;
471
472	if ( isset( $data['client_email'] ) && ! empty( $data['client_email'] ) ) {
473	$client_existed = $this->get_wp_client_by( 'client_email', $data['client_email'], ARRAY_A );
474	if ( is_array( $client_existed ) && isset( $client_existed['client_id'] ) && ( empty( $client_id ) \|\| $client_id !== (int) $client_existed['client_id'] ) ) {
475	if ( $throw_out ) {
476	throw new MainWP_Exception( esc_html__( 'Client email exists. Please try again.', 'mainwp' ) );
477	} else {
478	return false;
479	}
480	}
481	}
482
483	if ( ! empty( $client_id ) ) {
484	$this->wpdb->update( $this->table_name( 'wp_clients' ), $data, array( 'client_id' => intval( $client_id ) ) );
485	return $this->get_wp_client_by( 'client_id', $client_id );
486	} elseif ( $this->wpdb->insert( $this->table_name( 'wp_clients' ), $data ) ) {
487	return $this->get_wp_client_by( 'client_id', $this->wpdb->insert_id );
488	}
489	return false;
490	}
491
492
493	/**
494	* Method update_client.
495	*
496	* Create or update client contact.
497	*
498	* @param array $data Contact data.
499	*
500	* @return bool.
501	*/
502	public function update_client_contact( $data ) { // phpcs:ignore -- NOSONAR - complex.
503
504	if ( empty( $data ) ) {
505	return false;
506	}
507
508	$contact_id = isset( $data['contact_id'] ) ? (int) $data['contact_id'] : 0;
509
510	$contact_email = isset( $data['contact_email'] ) ? $data['contact_email'] : '';
511	if ( ! empty( $contact_email ) ) {
512	$existed_email = false;
513	// check to valid email.
514	$curret_contact = $this->get_wp_client_contact_by( 'contact_email', $contact_email );
515	if ( $curret_contact && empty( $contact_id ) ) { // add new contact with existed email => failed.
516	$existed_email = true;
517	}
518
519	if ( $curret_contact && ! empty( $contact_id ) && (int) $curret_contact->contact_id !== $contact_id ) { // update contact with existed email => failed.
520	$existed_email = true;
521	}
522
523	if ( $existed_email ) {
524	MainWP_Utility::update_flash_message( 'contact_existed_emails', $contact_email );
525	return false;
526	}
527	}
528
529	if ( ! empty( $contact_id ) ) {
530	$this->wpdb->update( $this->table_name( 'wp_clients_contacts' ), $data, array( 'contact_id' => intval( $contact_id ) ) );
531	return $this->get_wp_client_contact_by( 'contact_id', $contact_id );
532	} elseif ( $this->wpdb->insert( $this->table_name( 'wp_clients_contacts' ), $data ) ) {
533	return $this->get_wp_client_contact_by( 'contact_id', $this->wpdb->insert_id );
534	}
535	return false;
536	}
537
538	/**
539	* Method get_wp_client_contact_by.
540	*
541	* Get client by.
542	*
543	* @param string $by by.
544	* @param mixed $value by value.
545	* @param mixed $obj Format data.
546	* @param array $params other params.
547	*
548	* @return mixed $result results.
549	*/
550	public function get_wp_client_contact_by( $by = 'contact_id', $value = null, $obj = OBJECT, $params = array() ) { // phpcs:ignore -- NOSONAR - complex.
551
552	$sql = '';
553
554	if ( 'client_id' === $by && ! empty( $value ) ) {
555	$sql = $this->wpdb->prepare( 'SELECT wc.* FROM ' . $this->table_name( 'wp_clients_contacts' ) . ' wc WHERE wc.contact_client_id=%d ', $value );
556	}
557
558	if ( ! empty( $sql ) ) {
559	if ( OBJECT === $obj ) {
560	$result = $this->wpdb->get_results( $sql, OBJECT );
561	} else {
562	$result = $this->wpdb->get_results( $sql, ARRAY_A );
563	}
564	return $result;
565	}
566
567	if ( 'contact_id' === $by && is_numeric( $value ) ) {
568	$sql = $this->wpdb->prepare( 'SELECT wc.* FROM ' . $this->table_name( 'wp_clients_contacts' ) . ' wc WHERE wc.contact_id=%d ', $value );
569	} elseif ( 'contact_email' === $by && ! empty( $value ) ) {
570	$sql = $this->wpdb->prepare( 'SELECT wc.* FROM ' . $this->table_name( 'wp_clients_contacts' ) . ' wc WHERE wc.contact_email=%s ', $value );
571	}
572
573	$result = null;
574	if ( ! empty( $sql ) ) {
575	if ( OBJECT === $obj ) {
576	$result = $this->wpdb->get_row( $sql, OBJECT );
577	} else {
578	$result = $this->wpdb->get_row( $sql, ARRAY_A );
579	}
580	}
581	return $result;
582	}
583
584
585	/**
586	* Method delete_client.
587	*
588	* Delete client.
589	*
590	* @param int $client_id Client id of the contact.
591	* @param int $contact_id Contact id to delete.
592	*
593	* @return bool Results.
594	*/
595	public function delete_client_contact( $client_id, $contact_id ) {
596	$current = $this->get_wp_client_contact_by( 'contact_id', $contact_id );
597	if ( $current && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_contacts' ) . ' WHERE contact_client_id=%d AND contact_id = %d', $client_id, $contact_id ) ) ) {
598	if ( ! empty( $current->contact_image ) ) {
599	$dirs = MainWP_System_Utility::get_mainwp_dir( 'client-images', true );
600	$base_dir = $dirs[0];
601	$old_file = $base_dir . '/' . $current->contact_image;
602	MainWP_Utility::delete_file( $old_file );
603	}
604	return true;
605	}
606	return false;
607	}
608
609
610	/**
611	* Method delete_client_contacts_by_client_id.
612	*
613	* Delete client contacts by client id.
614	*
615	* @param int $client_id Client id.
616	*
617	* @return bool Results.
618	*/
619	public function delete_client_contacts_by_client_id( $client_id ) {
620
621	if ( empty( $client_id ) ) {
622	return false;
623	}
624
625	$client_contacts = $this->get_wp_client_contact_by( 'client_id', $client_id, ARRAY_A );
626	if ( $client_contacts ) {
627	foreach ( $client_contacts as $contact ) {
628	$this->delete_client_contact( $client_id, $contact['contact_id'] );
629	}
630	}
631	return true;
632	}
633
634
635	/**
636	* Method get_wp_client_by.
637	*
638	* Get client by.
639	*
640	* @param string $by by.
641	* @param mixed $value by value.
642	* @param mixed $obj Format data.
643	* @param bool $params Others params.
644	*
645	* @return mixed $result results.
646	*/
647	public function get_wp_client_by( $by = 'client_id', $value = null, $obj = OBJECT, $params = array() ) { // phpcs:ignore -- NOSONAR - complexex function. Current complexity is the only way to achieve desired results, pull request solutions appreciated.
648
649	$with_selected_sites = isset( $params['with_selected_sites'] ) && $params['with_selected_sites'] ? true : false;
650	$with_tags = isset( $params['with_tags'] ) && $params['with_tags'] ? true : false;
651	$group_ids = isset( $params['by_tags'] ) ? $params['by_tags'] : '';
652
653	// valid group ids.
654	if ( is_array( $group_ids ) ) {
655	$group_ids = array_filter(
656	$group_ids,
657	function ( $e ) {
658	if ( 'nogroups' === $e ) {
659	return true;
660	}
661	return ( is_numeric( $e ) && 0 < $e ) ? true : false;
662	}
663	);
664	} else {
665	$group_ids = '';
666	}
667
668	$select_sites = '';
669	$select_tags = '';
670	$where_clients = '';
671
672	$sql = '';
673
674	if ( $with_selected_sites ) {
675	$select_sites .= ',( SELECT GROUP_CONCAT(wp.id SEPARATOR ",") FROM ' . $this->table_name( 'wp' ) . ' wp WHERE wp.client_id = wc.client_id ) as selected_sites ';
676	}
677
678	if ( ! empty( $group_ids ) ) {
679
680	$join_group = '';
681	$where_group = '';
682
683	if ( in_array( 'nogroups', $group_ids ) ) {
684	$join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
685	$group_ids = array_filter(
686	$group_ids,
687	function ( $e ) {
688	return 'nogroups' !== $e;
689	}
690	);
691	if ( ! empty( $group_ids ) ) {
692	$groups = implode( ',', $group_ids );
693	$where_group = ' AND ( wpgroup.groupid IS NULL OR wpgroup.groupid IN (' . $groups . ') ) ';
694	} else {
695	$where_group = ' AND wpgroup.groupid IS NULL ';
696	}
697	} else {
698	$groups = implode( ',', $group_ids );
699	$join_group = ' JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
700	$where_group = ' AND wpgroup.groupid IN (' . $groups . ') ';
701	}
702
703	$sql_tags = ' SELECT wp.client_id FROM ' . $this->table_name( 'wp' ) . ' wp ';
704	$sql_tags .= $join_group;
705	$sql_tags .= ' WHERE 1 ' . $where_group;
706	$result_tags = $this->wpdb->get_results( $sql_tags );
707
708	if ( empty( $result_tags ) ) {
709	return array();
710	} else {
711	$cli_ids = array();
712	foreach ( $result_tags as $item ) {
713	$cli_ids[] = $item->client_id;
714	}
715	$cli_ids = array_values( array_unique( $cli_ids ) );
716	$where_clients = ' AND wc.client_id IN (' . implode( ',', $cli_ids ) . ') ';
717	}
718	}
719
720	if ( $with_tags ) {
721	$select_tags .= ", ( SELECT GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ',') FROM " . $this->table_name( 'wp' ) . ' wp ';
722	$select_tags .= ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid ';
723	$select_tags .= ' LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id ';
724	$select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroups ';
725
726	$select_tags .= ", ( SELECT GROUP_CONCAT(gr.id ORDER BY gr.id SEPARATOR ',') FROM " . $this->table_name( 'wp' ) . ' wp ';
727	$select_tags .= ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid ';
728	$select_tags .= ' LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id ';
729	$select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroupids ';
730	}
731
732	$join_contact = ' LEFT JOIN ' . $this->table_name( 'wp_clients_contacts' ) . ' cc ON wc.primary_contact_id = cc.contact_id ';
733
734	if ( 'all' === $by ) {
735	$sql = ' SELECT wc., cc. ' . $select_sites . $select_tags;
736	$sql .= ' FROM ' . $this->table_name( 'wp_clients' ) . ' wc ';
737	$sql .= $join_contact;
738	$sql .= ' WHERE 1 ' . $where_clients;
739	$sql .= ' ORDER BY wc.name';
740	}
741
742	if ( ! empty( $sql ) ) {
743	if ( OBJECT === $obj ) {
744	$result = $this->wpdb->get_results( $sql, OBJECT );
745	} else {
746	$result = $this->wpdb->get_results( $sql, ARRAY_A );
747	}
748	return $result;
749	}
750
751	if ( 'client_id' === $by && is_numeric( $value ) ) {
752	$sql = $this->wpdb->prepare( 'SELECT wc., cc. ' . $select_sites . $select_tags . ' FROM ' . $this->table_name( 'wp_clients' ) . ' wc ' . $join_contact . ' WHERE wc.client_id=%d ', $value );
753	} elseif ( 'client_email' === $by && ! empty( $value ) ) {
754	$sql = $this->wpdb->prepare( 'SELECT wc., cc. ' . $select_sites . $select_tags . ' FROM ' . $this->table_name( 'wp_clients' ) . ' wc ' . $join_contact . ' WHERE wc.client_email=%s ', $value );
755	}
756
757	$result = null;
758	if ( ! empty( $sql ) ) {
759	if ( OBJECT === $obj ) {
760	$result = $this->wpdb->get_row( $sql, OBJECT );
761	} else {
762	$result = $this->wpdb->get_row( $sql, ARRAY_A );
763	}
764	}
765	return $result;
766	}
767
768	/**
769	* Method get_wp_clients.
770	*
771	* Get clients.
772	*
773	* @param array $params params.
774	*
775	* @return mixed result.
776	*/
777	public function get_wp_clients( $params = array() ) { // phpcs:ignore -- NOSONAR - complex.
778
779	$custom_field = false;
780	$with_tags = false;
781	$with_contacts = false;
782	$count_only = false;
783
784	$s = '';
785	$exclude = array();
786	$include = array();
787	$limit = '';
788
789	$where = '';
790	$select_tags = '';
791
792	if ( $params && is_array( $params ) ) {
793	$client = isset( $params['client'] ) ? sanitize_text_field( wp_unslash( $params['client'] ) ) : '';
794	if ( '' !== $client ) {
795	$clients = preg_split( '/[;,]/', $client );
796	$clients = array_filter( array_map( 'trim', $clients ) );
797
798	$clientWhere = '';
799	foreach ( $clients as $clt ) {
800	if ( is_numeric( $clt ) ) {
801	$clientWhere .= intval( $clt ) . ', ';
802	}
803	}
804	$clientWhere = rtrim( $clientWhere, ', ' );
805
806	$where .= ' AND wc.client_id IN (' . $clientWhere . ') ';
807	}
808	$custom_field = $params['custom_fields'] ? true : false;
809
810	$with_tags = isset( $params['with_tags'] ) && $params['with_tags'] ? true : false;
811	$with_contacts = isset( $params['with_contacts'] ) && $params['with_contacts'] ? true : false;
812	$count_only = isset( $params['count_only'] ) && $params['count_only'] ? true : false;
813
814	$s = isset( $params['s'] ) ? $params['s'] : '';
815	$exclude = isset( $params['exclude'] ) && ! empty( $params['exclude'] ) ? wp_parse_id_list( $params['exclude'] ) : array();
816	$include = isset( $params['include'] ) && ! empty( $params['include'] ) ? wp_parse_id_list( $params['include'] ) : array();
817	$status = isset( $params['status'] ) && ! empty( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
818	$page = isset( $params['page'] ) ? intval( $params['page'] ) : false;
819	$per_page = isset( $params['per_page'] ) ? intval( $params['per_page'] ) : false;
820
821	if ( ! empty( $s ) ) {
822	$where .= ' AND ( wc.client_id LIKE "%' . $this->escape( $s ) . '%" OR wc.name LIKE "%' . $this->escape( $s ) . '%" OR wc.client_email LIKE "%' . $this->escape( $s ) . '%" ) ';
823	}
824
825	if ( ! empty( $exclude ) ) {
826	$where .= ' AND wc.client_id NOT IN (' . implode( ',', $exclude ) . ') ';
827	}
828
829	if ( ! empty( $include ) ) {
830	$where .= ' AND wc.client_id IN (' . implode( ',', $include ) . ') ';
831	}
832
833	if ( ! empty( $status ) ) {
834	$suspended_status = array_map(
835	function ( $value ) {
836	$map_status_db = array(
837	'active' => 0,
838	'suspended' => 1,
839	'lead' => 2,
840	'lost' => 3,
841	);
842	return isset( $map_status_db[ $value ] ) ? $map_status_db[ $value ] : '';
843	},
844	$status
845	);
846	$suspended_status = array_filter(
847	$suspended_status,
848	function ( $value ) {
849	return '' !== $value;
850	}
851	);
852	if ( ! empty( $suspended_status ) ) {
853	$where .= ' AND wc.suspended IN (' . implode( ',', $suspended_status ) . ') ';
854	}
855	}
856
857	if ( ! empty( $page ) && ! empty( $per_page ) ) {
858	$limit = ' LIMIT ' . ( $page - 1 ) * $per_page . ',' . $per_page;
859	}
860	}
861
862	if ( $with_tags ) {
863	$select_tags .= ", ( SELECT GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ',') FROM " . $this->table_name( 'wp' ) . ' wp ';
864	$select_tags .= ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid ';
865	$select_tags .= ' LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id ';
866	$select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroups ';
867
868	$select_tags .= ", ( SELECT GROUP_CONCAT(gr.id ORDER BY gr.id SEPARATOR ',') FROM " . $this->table_name( 'wp' ) . ' wp ';
869	$select_tags .= ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid ';
870	$select_tags .= ' LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id ';
871	$select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroupids ';
872	}
873
874	if ( $count_only ) {
875	$sql = ' SELECT count(*) ';
876	$sql .= ' FROM ' . $this->table_name( 'wp_clients' ) . ' wc ';
877	$sql .= ' WHERE 1 ' . $where;
878	return $this->wpdb->get_var( $sql );
879	} else {
880	$sql = ' SELECT wc.* ';
881	$sql .= ',( SELECT GROUP_CONCAT(wp.id SEPARATOR ",") FROM ' . $this->table_name( 'wp' ) . ' wp WHERE wp.client_id = wc.client_id ) as selected_sites ' . $select_tags;
882	$sql .= ' FROM ' . $this->table_name( 'wp_clients' ) . ' wc ';
883	$sql .= ' WHERE 1 ' . $where . ' ORDER BY wc.name ' . $limit;
884	}
885
886	$result = $this->wpdb->get_results( $sql, ARRAY_A );
887
888	$contact_fields = array(
889	'contact_id',
890	'contact_client_id',
891	'contact_email',
892	'contact_name',
893	'contact_phone',
894	'contact_role',
895	'contact_image',
896	'facebook',
897	'twitter',
898	'instagram',
899	'linkedin',
900	);
901
902	$output = array();
903
904	foreach ( $result as $rst ) {
905
906	if ( $custom_field ) {
907	$custom_fields = $this->get_client_fields( true, $rst['client_id'] );
908	if ( $custom_fields ) {
909	foreach ( $custom_fields as $field ) {
910	$rst[ $field->field_name ] = $field->field_value;
911	}
912	}
913	}
914	if ( $with_tags && ! empty( $rst['wpgroupids'] ) && ! empty( $rst['wpgroups'] ) ) {
915	$wpgroupids = explode( ',', $rst['wpgroupids'] );
916	$wpgroups = explode( ',', $rst['wpgroups'] );
917
918	$tags = array();
919	if ( is_array( $wpgroupids ) ) {
920	foreach ( $wpgroupids as $gidx => $groupid ) {
921	if ( $groupid && ! isset( $tags[ $groupid ] ) ) {
922	$tags[ $groupid ] = isset( $wpgroups[ $gidx ] ) ? $wpgroups[ $gidx ] : '';
923	}
924	}
925	}
926
927	$rst['tags'] = $tags;
928	}
929
930	if ( $with_contacts ) {
931	$client_contacts = $this->get_wp_client_contact_by( 'client_id', $rst['client_id'], ARRAY_A );
932
933	$contacts = array();
934
935	if ( is_array( $client_contacts ) ) {
936	foreach ( $client_contacts as $gidx => $contact ) {
937	$contacts[ $contact['contact_id'] ] = MainWP_Utility::map_fields( $contact, $contact_fields, false );
938	}
939	}
940	$rst['contacts'] = $contacts;
941	}
942
943	$output[] = $rst;
944	}
945	return $output;
946	}
947
948	/**
949	* Method update_selected_sites_for_client.
950	*
951	* Update client.
952	*
953	* @param int $client_id client id.
954	* @param array $site_ids site ids.
955	*
956	* @return bool true\|false.
957	*/
958	public function update_selected_sites_for_client( $client_id, $site_ids ) {
959	if ( empty( $client_id ) ) {
960	return false;
961	}
962
963	$site_ids = array_filter(
964	$site_ids,
965	function ( $e ) {
966	return ( is_numeric( $e ) && 0 < $e ) ? true : false;
967	}
968	);
969
970	if ( is_array( $site_ids ) && ! empty( $site_ids ) ) {
971	$this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET client_id=0 WHERE client_id=%d AND id NOT IN (' . implode( ',', $site_ids ) . ')', $client_id ) );
972	$this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET client_id=%d WHERE id IN (' . implode( ',', $site_ids ) . ')', $client_id ) );
973	return true;
974	} else {
975	$this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET client_id=0 WHERE client_id=%d ', $client_id ) );
976	return true;
977	}
978	}
979
980	/**
981	* Method delete_client.
982	*
983	* Delete client.
984	*
985	* @param int $client_id Client id to delete.
986	* @return bool Results.
987	*/
988	public function delete_client( $client_id ) {
989
990	$current = $this->get_wp_client_by( 'client_id', $client_id );
991	if ( $current && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients' ) . ' WHERE client_id=%d ', $client_id ) ) ) {
992	$this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET client_id=0 WHERE client_id=%d ', $client_id ) );
993	$fields = $this->get_client_fields_by( 'client_id', $client_id );
994	if ( $fields ) {
995	foreach ( $fields as $field ) {
996	$this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_fields' ) . ' WHERE field_id=%d ', $field->field_id ) );
997	$this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_field_values' ) . ' WHERE field_id=%d AND value_client_id=%d ', $field->field_id, $client_id ) );
998	}
999	}
1000
1001	if ( ! empty( $current->image ) ) {
1002	$dirs = MainWP_System_Utility::get_mainwp_dir( 'client-images', true );
1003	$base_dir = $dirs[0];
1004	$old_file = $base_dir . '/' . $current->image;
1005	MainWP_Utility::delete_file( $old_file );
1006	}
1007
1008	$this->delete_client_contacts_by_client_id( $client_id );
1009
1010	/**
1011	* Delete client
1012	*
1013	* Fires after delete a client.
1014	*
1015	* @param object $current client deleted.
1016	*
1017	* @since 4.5.1.1
1018	*/
1019	do_action( 'mainwp_client_deleted', $current );
1020
1021	return true;
1022	}
1023	return false;
1024	}
1025
1026	/**
1027	* Method get_websites_by_client_ids.
1028	*
1029	* Get websites by client.
1030	*
1031	* @param int $client_ids Client ids.
1032	* @param array $params other params.
1033	*
1034	* @return mixed $result Results.
1035	*/
1036	public function get_websites_by_client_ids( $client_ids, $params = array() ) {
1037
1038	if ( empty( $client_ids ) ) {
1039	return false;
1040	}
1041
1042	if ( ! is_array( $params ) ) {
1043	$params = array();
1044	}
1045
1046	$client = '';
1047	if ( is_array( $client_ids ) ) {
1048	// valid client ids.
1049	$client_ids = array_filter(
1050	$client_ids,
1051	function ( $e ) {
1052	return is_numeric( $e ) && ! empty( $e ) ? true : false; // to valid client ids.
1053	}
1054	);
1055	$client = implode( ';', $client_ids );
1056	} else {
1057	$client = $client_ids;
1058	}
1059
1060	$params['client'] = $client;
1061
1062	return MainWP_DB::instance()->get_websites_for_current_user( $params );
1063	}
1064
1065	/**
1066	* Method get_number_sites_of_client.
1067	*
1068	* Get client by.
1069	*
1070	* @param int $client_id Client id.
1071	*
1072	* @return int Number of sites.
1073	*/
1074	public function get_number_sites_of_client( $client_id ) {
1075	if ( empty( $client_id ) ) {
1076	return false;
1077	}
1078	return $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT COUNT(wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp WHERE wp.client_id =%d', $client_id ) );
1079	}
1080
1081	/**
1082	* Method count_total_clients.
1083	*
1084	* Count total of Clients.
1085	*
1086	* @return int Total number.
1087	*/
1088	public function count_total_clients() {
1089	return $this->wpdb->get_var( 'SELECT COUNT(client_id) FROM ' . $this->table_name( 'wp_clients' ) );
1090	}
1091
1092
1093	/**
1094	* Method add_client_field.
1095	*
1096	* Add client field.
1097	*
1098	* @param mixed $field Client fields data.
1099	*
1100	* @return mixed bool\|results.
1101	*/
1102	public function add_client_field( $field ) {
1103	if ( ! empty( $field['field_name'] ) && isset( $field['client_id'] ) ) {
1104	if ( $this->get_client_fields_by( 'field_name', $field['field_name'], $field['client_id'] ) ) { // field name existed for the client can not create.
1105	return false;
1106	}
1107	if ( $this->wpdb->insert( $this->table_name( 'wp_clients_fields' ), $field ) ) {
1108	return $this->get_client_fields_by( 'field_id', $this->wpdb->insert_id );
1109	}
1110	}
1111	return false;
1112	}
1113
1114	/**
1115	* Method update_client_field.
1116	*
1117	* Update client field.
1118	*
1119	* @param int $field_id field id.
1120	* @param array $field field data.
1121	*
1122	* @return mixed\|false field.
1123	*/
1124	public function update_client_field( $field_id, $field ) {
1125	if ( $field_id && ! empty( $field['field_name'] ) ) {
1126	$current = $this->get_client_fields_by( 'field_id', $field_id );
1127	if ( $current && $this->wpdb->update( $this->table_name( 'wp_clients_fields' ), $field, array( 'field_id' => $field_id ) ) ) {
1128	return $this->get_client_fields_by( 'field_id', $current->field_id );
1129	}
1130	}
1131	return false;
1132	}
1133
1134	/**
1135	* Method update_client_field_value.
1136	*
1137	* Update client field value.
1138	*
1139	* @param int $field_id field id.
1140	* @param mixed $value field value.
1141	* @param int $client_id client id.
1142	*
1143	* @return mixed\|false field data.
1144	*/
1145	public function update_client_field_value( $field_id, $value, $client_id ) {
1146	if ( ! empty( $field_id ) ) {
1147	$row = $this->wpdb->get_row( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp_clients_field_values' ) . ' WHERE field_id = %d AND value_client_id=%d ', $field_id, $client_id ) );
1148	if ( $row ) {
1149	if ( $row->field_value != $value ) { //phpcs:ignore -- to valid.
1150	$this->wpdb->update( $this->table_name( 'wp_clients_field_values' ), array( 'field_value' => $value ), array( 'value_id' => $row->value_id ) );
1151	}
1152	return true;
1153	} else {
1154	return $this->wpdb->insert(
1155	$this->table_name( 'wp_clients_field_values' ),
1156	array(
1157	'field_id' => $field_id,
1158	'field_value' => $value,
1159	'value_client_id' => $client_id,
1160	)
1161	);
1162	}
1163	}
1164	return false;
1165	}
1166
1167	/**
1168	* Method get_client_fields_by.
1169	*
1170	* Get client field.
1171	*
1172	* @param string $by by.
1173	* @param mixed $value field value.
1174	* @param int $client_id client id.
1175	*
1176	* @return mixed\|null $field field data.
1177	*/
1178	public function get_client_fields_by( $by = 'field_id', $value = null, $client_id = 0 ) {
1179
1180	if ( empty( $by ) \|\| empty( $value ) ) {
1181	return null;
1182	}
1183
1184	$sql = '';
1185
1186	if ( 'client_id' === $by ) {
1187	$sql = $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp_clients_fields' ) . ' WHERE `client_id`=%d ', $value );
1188	return $this->wpdb->get_results( $sql );
1189	}
1190
1191	if ( 'field_name' === $by ) {
1192	$value = str_replace( array( '[', ']' ), '', $value );
1193	}
1194
1195	if ( 'field_id' === $by ) {
1196	$sql = $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp_clients_fields' ) . ' WHERE `field_id`=%d', $value );
1197	} elseif ( 'field_name' === $by ) {
1198	$sql = $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp_clients_fields' ) . ' WHERE `field_name` = %s AND `client_id`=%d ', $value, $client_id );
1199	}
1200
1201	$field = null;
1202
1203	if ( ! empty( $sql ) ) {
1204	$field = $this->wpdb->get_row( $sql );
1205	}
1206
1207	return $field;
1208	}
1209
1210	/**
1211	* Method get_client_fields()
1212	*
1213	* Get client fields.
1214	*
1215	* @param bool $general Get general fields OR not.
1216	* @param int $client_id Client id.
1217	* @param bool $field_name_index index result with field name.
1218	*
1219	* @return mixed results.
1220	*/
1221	public function get_client_fields( $general = true, $client_id = 0, $field_name_index = false ) { // phpcs:ignore -- NOSONAR - complex.
1222	$where = '';
1223
1224	if ( $client_id ) {
1225	if ( true === $general ) {
1226	$where = ' f.client_id = ' . intval( $client_id ) . ' OR ( f.client_id = 0 AND ( v.value_client_id IS NULL OR v.value_client_id = ' . intval( $client_id ) . ') ) ';
1227	} else {
1228	$where = ' f.client_id = ' . intval( $client_id );
1229	}
1230	} elseif ( true === $general ) {
1231	$where = ' f.client_id = 0';
1232	}
1233
1234	if ( '' === $where ) {
1235	return false;
1236	}
1237
1238	$gene_results = array();
1239
1240	if ( $general ) {
1241	// to fix: missing general fields for client without client fields values.
1242	$gene_sql = 'SELECT f.* FROM ' . $this->table_name( 'wp_clients_fields' ) . ' f WHERE f.client_id = 0 ORDER BY f.field_name ';
1243	$gene_results = $this->wpdb->get_results( $gene_sql );
1244	}
1245
1246	if ( $client_id ) { // get fields values of client.
1247	$sql = 'SELECT f.*, v.field_value as field_value FROM ' . $this->table_name( 'wp_clients_fields' ) . ' f LEFT JOIN ' .
1248	$this->table_name( 'wp_clients_field_values' ) . ' v ON f.field_id = v.field_id WHERE ' . $where . ' ORDER BY f.field_name ';
1249	} else {
1250	$sql = 'SELECT f.* FROM ' . $this->table_name( 'wp_clients_fields' ) . ' f WHERE ' . $where . ' ORDER BY f.field_name '; // value_client_id to compatible result.
1251	}
1252
1253	$results = $this->wpdb->get_results( $sql );
1254
1255	$fields_list = array();
1256	if ( $field_name_index ) {
1257	if ( $results ) {
1258	foreach ( $results as $item ) {
1259	$fields_list[ $item->field_name ] = $item;
1260	}
1261	}
1262	if ( $gene_results ) {
1263	foreach ( $gene_results as $gene_item ) {
1264	if ( ! isset( $fields_list[ $gene_item->field_name ] ) ) {
1265	$gene_item->field_value = '';
1266	$fields_list[ $gene_item->field_name ] = $gene_item;
1267	}
1268	}
1269	}
1270	} else {
1271	$check_list = array();
1272	if ( $results ) {
1273	foreach ( $results as $item ) {
1274	$check_list[ $item->field_name ] = true;
1275	$fields_list[] = $item;
1276	}
1277	}
1278	if ( $gene_results ) {
1279	foreach ( $gene_results as $gene_item ) {
1280	if ( ! isset( $check_list[ $gene_item->field_name ] ) ) {
1281	$check_list[ $gene_item->field_name ] = true;
1282	$gene_item->field_value = '';
1283	$fields_list[] = $gene_item;
1284	}
1285	}
1286	}
1287	}
1288	return $fields_list;
1289	}
1290
1291	/**
1292	* Suspend - Unsuspended websites.
1293	*
1294	* @param int $client_id Client id.
1295	* @param int $suspend_val Suspend value: 0 or 1.
1296	*
1297	* @return int\|boolean The result.
1298	*/
1299	public function suspend_unsuspend_websites_by_client_id( $client_id, $suspend_val ) {
1300	if ( empty( $client_id ) ) {
1301	return false;
1302	}
1303	return $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET suspended=%d WHERE client_id=%d ', $suspend_val, $client_id ) );
1304	}
1305
1306	/**
1307	* Method delete_client_field_by.
1308	*
1309	* Delete client field by.
1310	*
1311	* @param string $by by.
1312	* @param mixed $value field value.
1313	* @param int $client_id client id.
1314	*
1315	* @return bool Deleted or not.
1316	*/
1317	public function delete_client_field_by( $by = 'field_id', $value = false, $client_id = false ) {
1318
1319	if ( 'field_id' === $by && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_fields' ) . ' WHERE field_id=%d AND client_id=%d', $value, $client_id ) ) ) { // delete individual or general client field.
1320	if ( $client_id > 0 ) { // individual field value.
1321	$this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_field_values' ) . ' WHERE field_id=%d AND value_client_id=%d', $value, $client_id ) ); // delete individual tokens values, for one client.
1322	} else {
1323	$this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_clients_field_values' ) . ' WHERE field_id=%d', $value ) ); // delete general tokens values, multi clients (value_client_id).
1324	}
1325	return true;
1326	}
1327
1328	return false;
1329	}
1330
1331	/**
1332	* Method pro_reports_get_tokens()
1333	*
1334	* Get pro reports get tokens data.
1335	*
1336	* @return mixed results.
1337	*/
1338	public function pro_reports_get_tokens() {
1339	return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'pro_reports_token' ) . ' WHERE 1 = 1 ORDER BY type DESC, token_name ASC' );
1340	}
1341
1342	/**
1343	* Method pro_reports_get_site_token_values()
1344	*
1345	* Get pro reports get tokens values data.
1346	*
1347	* @return mixed results.
1348	*/
1349	public function pro_reports_get_site_token_values() {
1350	return $this->wpdb->get_results( ' SELECT * FROM ' . $this->table_name( 'pro_reports_site_token' ) . ' WHERE 1 ' );
1351	}
1352
1353	/**
1354	* Method client_reports_get_tokens()
1355	*
1356	* Get client reports get tokens data.
1357	*
1358	* @return mixed results.
1359	*/
1360	public function client_reports_get_tokens() {
1361	return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'client_report_token' ) . ' WHERE 1 = 1 ORDER BY token_name ASC' );
1362	}
1363
1364	/**
1365	* Method client_reports_get_site_token_values()
1366	*
1367	* Get client reports get tokens values data.
1368	*
1369	* @return mixed results.
1370	*/
1371	public function client_reports_get_site_token_values() {
1372	return $this->wpdb->get_results( ' SELECT * FROM ' . $this->table_name( 'client_report_site_token' ) . ' WHERE 1 ' );
1373	}
1374	}
1375