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

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

1,601 lines 63.7 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	// "0" is a name the column stores, so the write is refused only for a name that is missing
1200	// or empty, not for every name PHP reads as falsy. A name that is not a string is refused
1201	// outright: false is set and is not '', and it would reach the column as an empty name.
1202	if ( isset( $field['field_name'] ) && is_string( $field['field_name'] ) && '' !== $field['field_name'] && isset( $field['client_id'] ) ) {
1203	if ( $this->get_client_fields_by( 'field_name', $field['field_name'], $field['client_id'] ) ) { // field name existed for the client can not create.
1204	return false;
1205	}
1206	if ( $this->wpdb->insert( $this->table_name( 'wp_clients_fields' ), $field ) ) {
1207	return $this->get_client_fields_by( 'field_id', $this->wpdb->insert_id );
1208	}
1209	}
1210	return false;
1211	}
1212
1213	/**
1214	* Method update_client_field.
1215	*
1216	* Update client field.
1217	*
1218	* @param int $field_id field id.
1219	* @param array $field field data.
1220	*
1221	* @return mixed\|false field.
1222	*/
1223	public function update_client_field( $field_id, $field ) {
1224	// Same as the add above: a rename to "0" is a rename, not a name left out, and a name that
1225	// is not a string would be written over the stored name as an empty one.
1226	if ( $field_id && isset( $field['field_name'] ) && is_string( $field['field_name'] ) && '' !== $field['field_name'] ) {
1227	$current = $this->get_client_fields_by( 'field_id', $field_id );
1228	if ( $current && $this->wpdb->update( $this->table_name( 'wp_clients_fields' ), $field, array( 'field_id' => $field_id ) ) ) {
1229	return $this->get_client_fields_by( 'field_id', $current->field_id );
1230	}
1231	}
1232	return false;
1233	}
1234
1235	/**
1236	* Method update_client_field_value.
1237	*
1238	* Update client field value.
1239	*
1240	* @param int $field_id field id.
1241	* @param mixed $value field value.
1242	* @param int $client_id client id.
1243	*
1244	* @return mixed\|false field data.
1245	*/
1246	public function update_client_field_value( $field_id, $value, $client_id ) {
1247	if ( ! empty( $field_id ) ) {
1248	$table_field_values = esc_sql( $this->table_name( 'wp_clients_field_values' ) );
1249	$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 ) );
1250	if ( $row ) {
1251	if ( $row->field_value != $value ) { //phpcs:ignore -- to valid.
1252	$this->wpdb->update( $table_field_values, array( 'field_value' => $value ), array( 'value_id' => $row->value_id ) );
1253	}
1254	return true;
1255	} else {
1256	return $this->wpdb->insert(
1257	$table_field_values,
1258	array(
1259	'field_id' => $field_id,
1260	'field_value' => $value,
1261	'value_client_id' => $client_id,
1262	)
1263	);
1264	}
1265	}
1266	return false;
1267	}
1268
1269	/**
1270	* Method get_client_fields_by.
1271	*
1272	* Get client field.
1273	*
1274	* @param string $by by.
1275	* @param mixed $value field value.
1276	* @param int $client_id client id.
1277	*
1278	* @return mixed\|null $field field data.
1279	*/
1280	public function get_client_fields_by( $by = 'field_id', $value = null, $client_id = 0 ) {
1281
1282	// "0" is a name the column stores, so a falsy test here would make a field named "0" unfindable
1283	// and let a second one be created under the same name. Only the name lookup takes that
1284	// exception: every other column keeps refusing a falsy value, so a client_id of 0 stays "no
1285	// client" instead of matching every general field.
1286	$missing = 'field_name' === $by ? ( null === $value \|\| '' === $value ) : empty( $value );
1287
1288	if ( empty( $by ) \|\| $missing ) {
1289	return null;
1290	}
1291
1292	$sql = '';
1293	$table_fields = esc_sql( $this->table_name( 'wp_clients_fields' ) );
1294
1295	if ( 'client_id' === $by ) {
1296	$sql = $this->wpdb->prepare( 'SELECT * FROM ' . $table_fields . ' WHERE `client_id`=%d ', $value );
1297	return $this->wpdb->get_results( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() with escaped table name, safe to use directly.
1298	}
1299
1300	if ( 'field_name' === $by ) {
1301	$value = str_replace( array( '[', ']' ), '', $value );
1302	}
1303
1304	if ( 'field_id' === $by ) {
1305	$sql = $this->wpdb->prepare( 'SELECT * FROM ' . $table_fields . ' WHERE `field_id`=%d', $value );
1306	} elseif ( 'field_name' === $by ) {
1307	$sql = $this->wpdb->prepare( 'SELECT * FROM ' . $table_fields . ' WHERE `field_name` = %s AND `client_id`=%d ', $value, $client_id );
1308	}
1309
1310	$field = null;
1311
1312	if ( ! empty( $sql ) ) {
1313	$field = $this->wpdb->get_row( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() with escaped table name, safe to use directly.
1314	}
1315
1316	return $field;
1317	}
1318
1319	/**
1320	* Method get_client_fields_by_params.
1321	*
1322	* Get client fields by params.
1323	*
1324	* @param array $params params.
1325	* @param string $obj OBJECT\|ARRAY_A.
1326	*
1327	* @return mixed\|null $fields fields data.
1328	*/
1329	public function get_client_fields_by_params( $params = array(), $obj = OBJECT ) { // phpcs:ignore -- NOSONAR - complex.
1330
1331	if ( ! is_array( $params ) ) {
1332	$params = array();
1333	}
1334
1335	// Initialize custom_where if it does not exist.
1336	$where = '';
1337	$limit = '';
1338	$orderby = 'clients_fields.field_id DESC';
1339
1340	// Parse exclude and include lists.
1341	$parse_lists = static function ( $lists ) {
1342	// Convert string to array.
1343	if ( is_string( $lists ) ) {
1344	$lists = array_map( 'trim', explode( ',', $lists ) );
1345	}
1346
1347	// Out default.
1348	$out = array(
1349	'ids' => array(),
1350	'names' => array(),
1351	);
1352
1353	foreach ( (array) $lists as $v ) {
1354	if ( '' === $v \|\| null === $v ) {
1355	continue;
1356	}
1357
1358	if ( is_int( $v ) \|\| ( is_string( $v ) && ctype_digit( $v ) ) ) {
1359	$out['ids'][] = (int) $v;
1360	} else {
1361	$out['names'][] = (string) $v;
1362	}
1363	}
1364	return $out;
1365	};
1366
1367	// Handle exclude.
1368	if ( isset( $params['exclude'] ) && ! empty( $params['exclude'] ) ) {
1369	['ids' => $ids, 'names' => $names] = $parse_lists( $params['exclude'] );
1370	$sub_conditions = array();
1371	$sub_conditions[] = empty( $ids ) ? '' : $this->wpdb->prepare( 'clients_fields.field_id NOT IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')', ...$ids );
1372	$sub_conditions[] = empty( $names ) ? '' : $this->wpdb->prepare( 'clients_fields.field_name NOT IN (' . implode( ',', array_fill( 0, count( $names ), '%s' ) ) . ')', ...$names );
1373	$sub_conditions = array_filter( $sub_conditions );
1374	$where .= empty( $sub_conditions ) ? '' : ' AND (' . implode( ' AND ', $sub_conditions ) . ')';
1375	}
1376
1377	// Handle include.
1378	if ( isset( $params['include'] ) && ! empty( $params['include'] ) ) {
1379	['ids' => $ids, 'names' => $names] = $parse_lists( $params['include'] );
1380	$sub_conditions = array();
1381	$sub_conditions[] = empty( $ids ) ? '' : $this->wpdb->prepare( 'clients_fields.field_id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')', ...$ids );
1382	$sub_conditions[] = empty( $names ) ? '' : $this->wpdb->prepare( 'clients_fields.field_name IN (' . implode( ',', array_fill( 0, count( $names ), '%s' ) ) . ')', ...$names );
1383	$sub_conditions = array_filter( $sub_conditions );
1384	$where .= empty( $sub_conditions ) ? '' : ' AND (' . implode( ' AND ', $sub_conditions ) . ')';
1385	}
1386
1387	// Handle search.
1388	if ( isset( $params['search'] ) && ! empty( $params['search'] ) ) {
1389	$search_term = $this->escape( htmlspecialchars( $params['search'] ) );
1390	$where .= ' AND clients_fields.field_name LIKE "%' . $search_term . '%"';
1391	}
1392
1393	// Handle exact field name. The value is queried as it is stored, with no bracket stripping, so
1394	// the column collation decides equality the same way the unique index does. An empty string
1395	// filters on an empty name; pass null to leave the filter off.
1396	if ( isset( $params['field_name_exact'] ) && is_string( $params['field_name_exact'] ) ) {
1397	$where .= $this->wpdb->prepare( ' AND clients_fields.field_name = %s', $params['field_name_exact'] );
1398	}
1399
1400	// Handle client id.
1401	if ( isset( $params['client_id'] ) && ! empty( $params['client_id'] ) ) {
1402	$client_ids = wp_parse_id_list( $params['client_id'] );
1403	$where .= empty( $client_ids ) ? '' : ' AND clients_fields.client_id IN (' . implode( ',', $client_ids ) . ')';
1404	}
1405
1406	// Handel page and per_page.
1407	$page = '';
1408	$per_page = '';
1409	if ( isset( $params['page'] ) && ! empty( $params['page'] ) ) {
1410	$page = (int) $params['page'];
1411	}
1412
1413	if ( isset( $params['per_page'] ) && ! empty( $params['per_page'] ) ) {
1414	$per_page = (int) $params['per_page'];
1415	}
1416
1417	if ( ! empty( $page ) && ! empty( $per_page ) ) {
1418	$limit = ' LIMIT ' . ( $page - 1 ) * $per_page . ', ' . $per_page;
1419	}
1420
1421	// Build SQL.
1422	$sql = 'SELECT * FROM ' . $this->table_name( 'wp_clients_fields' ) . ' clients_fields WHERE 1=1 ' . $where . ' ORDER BY ' . $orderby . $limit;
1423
1424	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.
1425	}
1426
1427	/**
1428	* Method get_client_fields()
1429	*
1430	* Get client fields.
1431	*
1432	* @param bool $general Get general fields OR not.
1433	* @param int $client_id Client id.
1434	* @param bool $field_name_index index result with field name.
1435	*
1436	* @return mixed results.
1437	*/
1438	public function get_client_fields( $general = true, $client_id = 0, $field_name_index = false ) { // phpcs:ignore -- NOSONAR - complex.
1439	$where = '';
1440
1441	if ( $client_id ) {
1442	if ( true === $general ) {
1443	$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 ) . ') ) ';
1444	} else {
1445	$where = ' f.client_id = ' . intval( $client_id );
1446	}
1447	} elseif ( true === $general ) {
1448	$where = ' f.client_id = 0';
1449	}
1450
1451	if ( '' === $where ) {
1452	return false;
1453	}
1454
1455	$gene_results = array();
1456	$table_fields = esc_sql( $this->table_name( 'wp_clients_fields' ) );
1457	$table_field_values = esc_sql( $this->table_name( 'wp_clients_field_values' ) );
1458
1459	if ( $general ) {
1460	$gene_sql = 'SELECT f.* FROM ' . $table_fields . ' f WHERE f.client_id = 0 ORDER BY f.field_name ';
1461	$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.
1462	}
1463
1464	if ( $client_id ) { // get fields values of client.
1465	$sql = 'SELECT f.*, v.field_value as field_value FROM ' . $table_fields . ' f LEFT JOIN ' .
1466	$table_field_values . ' v ON f.field_id = v.field_id WHERE ' . $where . ' ORDER BY f.field_name ';
1467	} else {
1468	$sql = 'SELECT f.* FROM ' . $table_fields . ' f WHERE ' . $where . ' ORDER BY f.field_name '; // value_client_id to compatible result.
1469	}
1470
1471	$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.
1472
1473	$fields_list = array();
1474	if ( $field_name_index ) {
1475	if ( $results ) {
1476	foreach ( $results as $item ) {
1477	$fields_list[ $item->field_name ] = $item;
1478	}
1479	}
1480	if ( $gene_results ) {
1481	foreach ( $gene_results as $gene_item ) {
1482	if ( ! isset( $fields_list[ $gene_item->field_name ] ) ) {
1483	$gene_item->field_value = '';
1484	$fields_list[ $gene_item->field_name ] = $gene_item;
1485	}
1486	}
1487	}
1488	} else {
1489	$check_list = array();
1490	if ( $results ) {
1491	foreach ( $results as $item ) {
1492	$check_list[ $item->field_name ] = true;
1493	$fields_list[] = $item;
1494	}
1495	}
1496	if ( $gene_results ) {
1497	foreach ( $gene_results as $gene_item ) {
1498	if ( ! isset( $check_list[ $gene_item->field_name ] ) ) {
1499	$check_list[ $gene_item->field_name ] = true;
1500	$gene_item->field_value = '';
1501	$fields_list[] = $gene_item;
1502	}
1503	}
1504	}
1505	}
1506	return $fields_list;
1507	}
1508
1509	/**
1510	* Suspend - Unsuspended websites.
1511	*
1512	* @param int $client_id Client id.
1513	* @param int $suspend_val Suspend value: 0 or 1.
1514	*
1515	* @return int\|boolean The result.
1516	*/
1517	public function suspend_unsuspend_websites_by_client_id( $client_id, $suspend_val ) {
1518	if ( empty( $client_id ) ) {
1519	return false;
1520	}
1521	$table_wp = esc_sql( $this->table_name( 'wp' ) );
1522	return $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $table_wp . ' SET suspended=%d WHERE client_id=%d ', $suspend_val, $client_id ) );
1523	}
1524
1525	/**
1526	* Method delete_client_field_by.
1527	*
1528	* Delete client field by.
1529	*
1530	* @param string $by by.
1531	* @param mixed $value field value.
1532	* @param int $client_id client id.
1533	*
1534	* @return bool Deleted or not.
1535	*/
1536	public function delete_client_field_by( $by = 'field_id', $value = false, $client_id = false ) {
1537
1538	$table_fields = esc_sql( $this->table_name( 'wp_clients_fields' ) );
1539	$table_field_values = esc_sql( $this->table_name( 'wp_clients_field_values' ) );
1540
1541	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.
1542	if ( $client_id > 0 ) { // individual field value.
1543	$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.
1544	} else {
1545	$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).
1546	}
1547	return true;
1548	}
1549
1550	return false;
1551	}
1552
1553	/**
1554	* Method pro_reports_get_tokens()
1555	*
1556	* Get pro reports get tokens data.
1557	*
1558	* @return mixed results.
1559	*/
1560	public function pro_reports_get_tokens() {
1561	$table_token = esc_sql( $this->table_name( 'pro_reports_token' ) );
1562	return $this->wpdb->get_results( 'SELECT * FROM ' . $table_token . ' WHERE 1 = 1 ORDER BY type DESC, token_name ASC' );
1563	}
1564
1565	/**
1566	* Method pro_reports_get_site_token_values()
1567	*
1568	* Get pro reports get tokens values data.
1569	*
1570	* @return mixed results.
1571	*/
1572	public function pro_reports_get_site_token_values() {
1573	$table_site_token = esc_sql( $this->table_name( 'pro_reports_site_token' ) );
1574	return $this->wpdb->get_results( ' SELECT * FROM ' . $table_site_token . ' WHERE 1 ' );
1575	}
1576
1577	/**
1578	* Method client_reports_get_tokens()
1579	*
1580	* Get client reports get tokens data.
1581	*
1582	* @return mixed results.
1583	*/
1584	public function client_reports_get_tokens() {
1585	$table_token = esc_sql( $this->table_name( 'client_report_token' ) );
1586	return $this->wpdb->get_results( 'SELECT * FROM ' . $table_token . ' WHERE 1 = 1 ORDER BY token_name ASC' );
1587	}
1588
1589	/**
1590	* Method client_reports_get_site_token_values()
1591	*
1592	* Get client reports get tokens values data.
1593	*
1594	* @return mixed results.
1595	*/
1596	public function client_reports_get_site_token_values() {
1597	$table_site_token = esc_sql( $this->table_name( 'client_report_site_token' ) );
1598	return $this->wpdb->get_results( ' SELECT * FROM ' . $table_site_token . ' WHERE 1 ' );
1599	}
1600	}
1601