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

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

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