mainwp / class / class-mainwp-db.php

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

1,424 lines 48.7 KB

Raw Download Zip

1	<?php
2	/**
3	* MainWP Database Controller
4	*
5	* This file handles all interactions with the DB.
6	*
7	* @package MainWP/Dashboard
8	*/
9
10	namespace MainWP\Dashboard;
11
12	/**
13	* Class MainWP_DB
14	*
15	* @package MainWP\Dashboard
16	*/
17	class MainWP_DB extends MainWP_DB_Base {
18
19	// phpcs:disable WordPress.DB.RestrictedFunctions, WordPress.DB.PreparedSQL.NotPrepared, Generic.Metrics.CyclomaticComplexity -- This is the only way to achieve desired results, pull request solutions appreciated.
20
21	/**
22	* Private static variable to hold the single instance of the class.
23	*
24	* @static
25	*
26	* @var mixed Default null
27	*/
28	private static $instance = null;
29
30	/**
31	* Create public static instance.
32	*
33	* @static
34	*
35	* @return MainWP_DB
36	*/
37	public static function instance() {
38	if ( null == self::$instance ) {
39	self::$instance = new self();
40	}
41
42	self::$instance->test_connection();
43
44	return self::$instance;
45	}
46
47	/**
48	* Get wp_options database table view.
49	*
50	* @param array $fields Extra option fields.
51	* @param bool $default Whether or not to get default option fields.
52	*
53	* @return array wp_options view.
54	*/
55	public function get_option_view( $fields = array(), $default = true ) {
56
57	$view = '(SELECT intwp.id AS wpid,';
58
59	if ( empty( $fields ) \|\| $default ) {
60	$view .= ' (SELECT recent_comments.value FROM ' . $this->table_name( 'wp_options' ) . ' recent_comments WHERE recent_comments.wpid = intwp.id AND recent_comments.name = "recent_comments" LIMIT 1) AS recent_comments,
61	(SELECT recent_posts.value FROM ' . $this->table_name( 'wp_options' ) . ' recent_posts WHERE recent_posts.wpid = intwp.id AND recent_posts.name = "recent_posts" LIMIT 1) AS recent_posts,
62	(SELECT recent_pages.value FROM ' . $this->table_name( 'wp_options' ) . ' recent_pages WHERE recent_pages.wpid = intwp.id AND recent_pages.name = "recent_pages" LIMIT 1) AS recent_pages,
63	(SELECT phpversion.value FROM ' . $this->table_name( 'wp_options' ) . ' phpversion WHERE phpversion.wpid = intwp.id AND phpversion.name = "phpversion" LIMIT 1) AS phpversion,
64	(SELECT wp_upgrades.value FROM ' . $this->table_name( 'wp_options' ) . ' wp_upgrades WHERE wp_upgrades.wpid = intwp.id AND wp_upgrades.name = "wp_upgrades" LIMIT 1) AS wp_upgrades ';
65	}
66
67	if ( is_array( $fields ) ) {
68	foreach ( $fields as $field ) {
69	if ( empty( $field ) ) {
70	continue;
71	}
72	$view .= ', ';
73	$view .= '(SELECT ' . $this->escape( $field ) . '.value FROM ' . $this->table_name( 'wp_options' ) . ' ' . $this->escape( $field ) . ' WHERE ' . $this->escape( $field ) . '.wpid = intwp.id AND ' . $this->escape( $field ) . '.name = "' . $this->escape( $field ) . '" LIMIT 1) AS ' . $this->escape( $field );
74	}
75	}
76
77	$view .= ' FROM ' . $this->table_name( 'wp' ) . ' intwp)';
78
79	return $view;
80	}
81
82	/**
83	* Get disconnected child sites.
84	*
85	* @param array $sites_ids Websites ids - option field.
86	*
87	* @return array $disc_sites Array of disonnected sites.
88	*/
89	public function get_disconnected_websites( $sites_ids = false ) {
90	$where = $this->get_sql_where_allow_access_sites( 'wp' );
91
92	$sql = 'SELECT wp.,wp_sync.
93	FROM ' . $this->table_name( 'wp' ) . ' wp
94	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync
95	ON wp.id = wp_sync.wpid
96	WHERE (wp_sync.sync_errors IS NOT NULL) AND (wp_sync.sync_errors <> "") ' .
97	$where;
98
99	$websites = $this->wpdb->get_results( $sql );
100	$disc_sites = array();
101	if ( $websites ) {
102	foreach ( $websites as $website ) {
103
104	if ( ! empty( $sites_ids ) ) {
105	// filter sites.
106	if ( ! in_array( $website->id, $sites_ids ) ) {
107	continue;
108	}
109	}
110
111	$disc_sites[] = array(
112	'id' => $website->id,
113	'name' => $website->name,
114	'url' => $website->url,
115	);
116	}
117	}
118	return $disc_sites;
119	}
120
121	/**
122	* Get child site count.
123	*
124	* @param null $userId Current user ID.
125	* @param bool $all_access Check if user has access to all sites.
126	*
127	* @return int Child site count.
128	*/
129	public function get_websites_count( $userId = null, $all_access = false ) {
130	if ( ( null == $userId ) && MainWP_System::instance()->is_multi_user() ) {
131
132	/**
133	* Current user global.
134	*
135	* @global string
136	*/
137	global $current_user;
138
139	$userId = $current_user->ID;
140	}
141	$where = ( null == $userId ? '' : ' wp.userid = ' . $userId );
142	if ( ! $all_access ) {
143	$where .= $this->get_sql_where_allow_access_sites( 'wp' );
144	}
145	$qry = 'SELECT COUNT(wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp WHERE 1 ' . $where;
146
147	return $this->wpdb->get_var( $qry );
148	}
149
150	/**
151	* Get Child site wp_options database table.
152	*
153	* @param array $website Child Site array.
154	* @param mixed $option Child Site wp_options table name.
155	*
156	* @return string\|null Database query result (as string), or null on failure.
157	*/
158	public function get_website_option( $website, $option ) {
159
160	if ( is_array( $website ) ) {
161	if ( isset( $website[ $option ] ) ) {
162	return $website[ $option ];
163	}
164	$site_id = $website['id'];
165	} elseif ( is_object( $website ) ) {
166	if ( property_exists( $website, $option ) ) {
167	return $website->{$option};
168	}
169	$site_id = $website->id;
170	}
171
172	return $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT value FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', $site_id ) );
173	}
174
175	/**
176	* Get child site options.
177	*
178	* @param array $website Child site.
179	* @param mixed $options Child site options name.
180	*
181	* @return string\|null Database query result (as string), or null on failure.
182	*/
183	public function get_website_options_array( &$website, $options ) {
184
185	if ( ! is_array( $options ) \|\| empty( $options ) ) {
186	return array();
187	}
188
189	if ( is_array( $website ) ) {
190	$site_id = $website['id'];
191	} elseif ( is_object( $website ) ) {
192	$site_id = $website->id;
193	}
194
195	$options_name = implode( "','", $options );
196	$options_name = "'" . $options_name . "'";
197
198	$options_db = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT name, value FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name IN (' . $options_name . ')', $site_id ) );
199
200	$fill_options = array(
201	'primary_lasttime_backup',
202	);
203
204	$arr_options = array();
205
206	foreach ( (array) $options_db as $o ) {
207	$arr_options[ $o->name ] = $o->value;
208	if ( in_array( $o->name, $fill_options ) ) {
209	if ( is_array( $website ) ) {
210	if ( ! isset( $website[ $o->name ] ) ) {
211	$website[ $o->name ] = $o->value;
212	}
213	} elseif ( is_object( $website ) ) {
214	if ( ! property_exists( $website, $o->name ) ) {
215	$website->{$o->name} = $o->value;
216	}
217	}
218	}
219	}
220	return $arr_options;
221	}
222
223	/**
224	* Update child site options.
225	*
226	* @param object $website Child site object.
227	* @param mixed $option Option to update.
228	* @param mixed $value Value to update with.
229	*/
230	public function update_website_option( $website, $option, $value ) {
231	$rslt = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT name FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', $website->id ) );
232	if ( 0 < count( $rslt ) ) {
233	$this->wpdb->delete(
234	$this->table_name( 'wp_options' ),
235	array(
236	'wpid' => $website->id,
237	'name' => $this->escape( $option ),
238	)
239	);
240	$rslt = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT name FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', $website->id ) );
241	}
242
243	if ( 0 === count( $rslt ) ) {
244	$this->wpdb->insert(
245	$this->table_name( 'wp_options' ),
246	array(
247	'wpid' => $website->id,
248	'name' => $option,
249	'value' => $value,
250	)
251	);
252	} else {
253	$this->wpdb->update(
254	$this->table_name( 'wp_options' ),
255	array( 'value' => $value ),
256	array(
257	'wpid' => $website->id,
258	'name' => $option,
259	)
260	);
261	}
262	}
263
264	/**
265	* Get child sites by user ID.
266	*
267	* @param int $userid User ID.
268	* @param bool $selectgroups Selected groups.
269	* @param null $search_site Site search field value.
270	* @param string $orderBy Order list by. Default: URL.
271	*
272	* @return array\|object\|null Database query results or null on failer.
273	*/
274	public function get_websites_by_user_id( $userid, $selectgroups = false, $search_site = null, $orderBy = 'wp.url' ) {
275	return $this->get_results_result( $this->get_sql_websites_by_user_id( $userid, $selectgroups, $search_site, $orderBy ) );
276	}
277
278	/**
279	* Get child sites.
280	*
281	* @return string SQL string.
282	*/
283	public function get_sql_websites() {
284	$where = $this->get_sql_where_allow_access_sites( 'wp' );
285
286	return 'SELECT wp.,wp_sync.,wp_optionview.*
287	FROM ' . $this->table_name( 'wp' ) . ' wp
288	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
289	JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
290	WHERE 1 ' . $where;
291	}
292
293	/**
294	* Get child sites to run the status check process.
295	*
296	* @param int $last_check Time of the last check.
297	* @param int $count Number of websites.
298	*
299	* @return string SQL string.
300	*/
301	public function get_sql_websites_to_check_status( $last_check, $count = 20 ) {
302	$where = $this->get_sql_where_allow_access_sites( 'wp' );
303	$sql = 'SELECT wp.*
304	FROM ' . $this->table_name( 'wp' ) . ' wp
305	WHERE wp.disable_status_check <> 1 AND ( ( wp.status_check_interval = 0 AND wp.offline_checks_last < ' . intval( $last_check ) . ' )
306	OR ( wp.status_check_interval <> 0 AND ( wp.offline_checks_last + wp.status_check_interval * 60 < UNIX_TIMESTAMP() ) ) )' .
307	$where . '
308	LIMIT 0, ' . intval( $count );
309	return $sql;
310	}
311
312	/**
313	* Get child sites by user id via SQL.
314	*
315	* @param int $userid Given user ID.
316	* @param bool $selectgroups Selected groups. Default: false.
317	* @param null $search_site Site search field value. Default: null.
318	* @param string $orderBy Order list by. Default: URL.
319	* @param bool $offset Query offset. Default: false.
320	* @param bool $rowcount Row count. Default: falese.
321	*
322	* @return object\|null Return database query or null on failer.
323	*/
324	public function get_sql_websites_by_user_id( $userid, $selectgroups = false, $search_site = null, $orderBy = 'wp.url', $offset = false, $rowcount = false ) {
325	if ( MainWP_Utility::ctype_digit( $userid ) ) {
326	$where = '';
327	if ( null !== $search_site ) {
328	$search_site = trim( $search_site );
329	$where = ' AND (wp.name LIKE "%' . $search_site . '%" OR wp.url LIKE "%' . $search_site . '%") ';
330	}
331
332	$where .= $this->get_sql_where_allow_access_sites( 'wp' );
333
334	if ( $selectgroups ) {
335	$qry = 'SELECT wp.,wp_sync.,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ", ") as wpgroups
336	FROM ' . $this->table_name( 'wp' ) . ' wp
337	LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
338	LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
339	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
340	JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
341	WHERE wp.userid = ' . $userid . "
342	$where
343	GROUP BY wp.id
344	ORDER BY " . $orderBy;
345	} else {
346	$qry = 'SELECT wp.,wp_sync.,wp_optionview.*
347	FROM ' . $this->table_name( 'wp' ) . ' wp
348	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
349	JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
350	WHERE wp.userid = ' . $userid . "
351	$where
352	ORDER BY " . $orderBy;
353	}
354
355	if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
356	$qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
357	}
358
359	return $qry;
360	}
361
362	return null;
363	}
364
365	/**
366	* Get child sites for current user via SQL.
367	*
368	* @param bool $selectgroups Selected groups. Default: false.
369	* @param null $search_site Site search field value. Default: null.
370	* @param string $orderBy Order list by. Default: URL.
371	* @param bool $offset Query offset. Default: false.
372	* @param bool $rowcount Row count. Default: false.
373	* @param null $extraWhere Extra WHERE. Default: null.
374	* @param bool $for_manager For role manager. Default: false.
375	* @param mixed $extra_view Extra view. Default favi_icon.
376	* @param string $is_staging yes\|no Is child site a staging site.
377	*
378	* @return object\|null Database query results or null on failer.
379	*/
380	public function get_sql_websites_for_current_user(
381	$selectgroups = false,
382	$search_site = null,
383	$orderBy = 'wp.url',
384	$offset = false,
385	$rowcount = false,
386	$extraWhere = null,
387	$for_manager = false,
388	$extra_view = array( 'favi_icon' ),
389	$is_staging = 'no' ) {
390
391	$where = '';
392	if ( MainWP_System::instance()->is_multi_user() ) {
393
394	/**
395	* Current user global.
396	*
397	* @global string
398	*/
399	global $current_user;
400
401	$where .= ' AND wp.userid = ' . $current_user->ID . ' ';
402	}
403
404	if ( null !== $search_site ) {
405	$search_site = trim( $search_site );
406	$where .= ' AND (wp.name LIKE "%' . $search_site . '%" OR wp.url LIKE "%' . $search_site . '%") ';
407	}
408
409	if ( null !== $extraWhere ) {
410	$where .= ' AND ' . $extraWhere;
411	}
412
413	if ( ! $for_manager ) {
414	$where .= $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
415	}
416
417	if ( 'wp.url' === $orderBy ) {
418	$orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www', '')";
419	}
420
421	// wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
422	if ( $selectgroups ) {
423	$qry = 'SELECT wp.,wp_sync.,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ", ") as wpgroups
424	FROM ' . $this->table_name( 'wp' ) . ' wp
425	LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
426	LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
427	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
428	JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
429	WHERE 1 ' . $where . '
430	GROUP BY wp.id
431	ORDER BY ' . $orderBy;
432	} else {
433	$qry = 'SELECT wp.,wp_sync.,wp_optionview.*
434	FROM ' . $this->table_name( 'wp' ) . ' wp
435	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
436	JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
437	WHERE 1 ' . $where . '
438	ORDER BY ' . $orderBy;
439	}
440
441	if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
442	$qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
443	}
444
445	return $qry;
446	}
447
448	/**
449	* Get the child sites the current user has searched for.
450	*
451	* @param array $params Query parameters.
452	*
453	* @return boolean\|null $qry Database query results or null on failer.
454	*/
455	public function get_sql_search_websites_for_current_user( $params ) {
456
457	if ( ! is_array( $params ) ) {
458	$params = array();
459	}
460
461	$selectgroups = isset( $params['selectgroups'] ) && $params['selectgroups'] ? true : false;
462	$search_site = isset( $params['search'] ) ? $this->escape( trim( $params['search'] ) ) : null;
463	$orderBy = isset( $params['orderby'] ) ? $params['orderby'] : 'wp.url';
464	$offset = isset( $params['offset'] ) ? intval( $params['offset'] ) : false;
465	$rowcount = isset( $params['rowcount'] ) ? intval( $params['rowcount'] ) : false;
466	$extraWhere = isset( $params['extra_where'] ) ? $params['extra_where'] : null;
467	$for_manager = isset( $params['for_manager'] ) && $params['for_manager'] ? true : false;
468	$extra_view = isset( $params['extra_view'] ) ? $params['extra_view'] : array( 'favi_icon' );
469	$is_staging = isset( $params['is_staging'] ) && 'yes' == $params['is_staging'] ? 'yes' : 'no';
470	$is_count = isset( $params['count_only'] ) && $params['count_only'] ? true : false;
471	$group_ids = isset( $params['group_id'] ) && ! empty( $params['group_id'] ) ? $params['group_id'] : array();
472	$is_not = isset( $params['isnot'] ) && ! empty( $params['isnot'] ) ? true : false;
473
474	if ( ! is_array( $group_ids ) ) {
475	$group_ids = array();
476	}
477
478	// valid group ids.
479	$group_ids = array_filter(
480	$group_ids,
481	function( $e ) {
482	if ( 'nogroups' == $e ) {
483	return true;
484	}
485	$e = intval( $e );
486	return ( 0 < $e ) ? true : false;
487	}
488	);
489
490	if ( $selectgroups ) {
491	$staging_group = get_option( 'mainwp_stagingsites_group_id' );
492	if ( $staging_group ) {
493	if ( in_array( $staging_group, $group_ids ) ) {
494	if ( 0 == count( $group_ids ) ) {
495	$is_staging = 'yes';
496	} else {
497	$is_staging = 'nocheckstaging';
498	}
499	}
500	}
501	}
502
503	$where = '';
504	if ( MainWP_System::instance()->is_multi_user() ) {
505
506	/**
507	* Current user global.
508	*
509	* @global string
510	*/
511	global $current_user;
512
513	$where .= ' AND wp.userid = ' . $current_user->ID . ' ';
514	}
515
516	// for searching.
517	if ( null !== $search_site && '' !== $search_site ) {
518	$where .= ' AND (wp.name LIKE "%' . $search_site . '%" OR wp.url LIKE "%' . $search_site . '%") ';
519	}
520
521	if ( null !== $extraWhere ) {
522	$where .= ' AND ' . $extraWhere;
523	}
524
525	if ( ! $for_manager ) {
526	$where .= $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
527	}
528
529	if ( $is_count ) {
530	$orderBy = '';
531	} elseif ( 'wp.url' === $orderBy ) {
532	$orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www', '')";
533	}
534
535	if ( ! empty( $orderBy ) ) {
536	$orderBy = ' ORDER BY ' . $orderBy;
537	}
538
539	$join_group = '';
540	$where_group = '';
541
542	if ( in_array( 'nogroups', $group_ids ) ) {
543	$join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
544	$group_ids = array_filter(
545	$group_ids,
546	function( $e ) {
547	return 'nogroups' != $e;
548	}
549	);
550	if ( 0 < count( $group_ids ) ) {
551	$groups = implode( ',', $group_ids );
552	if ( $is_not ) {
553	$where_group = ' AND wpgroup.groupid IS NOT NULL AND wpgroup.groupid NOT IN (' . $groups . ') ';
554	} else {
555	$where_group = ' AND ( wpgroup.groupid IS NULL OR wpgroup.groupid IN (' . $groups . ') ) ';
556	}
557	} else {
558	if ( $is_not ) {
559	$where_group = ' AND wpgroup.groupid IS NOT NULL ';
560	} else {
561	$where_group = ' AND wpgroup.groupid IS NULL ';
562	}
563	}
564	} elseif ( $group_ids && 0 < count( $group_ids ) ) {
565	$groups = implode( ',', $group_ids );
566	$join_group = ' JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
567	if ( $is_not ) {
568	$where_group = ' AND wpgroup.groupid NOT IN (' . $groups . ') ';
569	} else {
570	$where_group = ' AND wpgroup.groupid IN (' . $groups . ') ';
571	}
572	}
573
574	// wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
575	if ( $selectgroups ) {
576	$qry = 'SELECT wp.,wp_sync.,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ", ") as wpgroups
577	FROM ' . $this->table_name( 'wp' ) . ' wp ' .
578	$join_group . '
579	LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
580	LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
581	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
582	JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
583	WHERE 1 ' . $where . $where_group . '
584	GROUP BY wp.id ' .
585	$orderBy;
586	} else {
587	$qry = 'SELECT wp.,wp_sync.,wp_optionview.*
588	FROM ' . $this->table_name( 'wp' ) . ' wp ' .
589	$join_group . '
590	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
591	JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
592	WHERE 1 ' . $where . $where_group .
593	$orderBy;
594	}
595
596	if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
597	$qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
598	}
599	return $qry;
600	}
601
602	/**
603	* Get child sites where allowed access via SQL.
604	*
605	* @param string $site_table_alias Child site table alias.
606	* @param string $is_staging yes\|no Is child site a staging site.
607	*
608	* @return boolean\|null $_where Database query results or null on failure.
609	*/
610	public function get_sql_where_allow_access_sites( $site_table_alias = '', $is_staging = 'no' ) {
611
612	if ( empty( $site_table_alias ) ) {
613	$site_table_alias = $this->table_name( 'wp' );
614	}
615
616	// check to filter the staging sites.
617	$where_staging = ' AND ' . $site_table_alias . '.is_staging = 0 ';
618	if ( 'no' === $is_staging ) {
619	$where_staging = ' AND ' . $site_table_alias . '.is_staging = 0 ';
620	} elseif ( 'yes' === $is_staging ) {
621	$where_staging = ' AND ' . $site_table_alias . '.is_staging = 1 ';
622	} elseif ( 'nocheckstaging' === $is_staging ) {
623	$where_staging = '';
624	}
625	// end staging filter.
626
627	$_where = $where_staging;
628	// To fix bug run from cron job.
629	if ( defined( 'DOING_CRON' ) && DOING_CRON ) {
630	return $_where;
631	}
632
633	// To fix bug run from wp cli.
634	if ( defined( 'WP_CLI' ) && WP_CLI ) {
635	return $_where;
636	}
637
638	/**
639	* Filter: mainwp_currentuserallowedaccesssites
640	*
641	* Filters allowed sites for the current user.
642	*
643	* @since Unknown
644	*/
645	$allowed_sites = apply_filters( 'mainwp_currentuserallowedaccesssites', 'all' );
646
647	if ( 'all' === $allowed_sites ) {
648	return $_where;
649	}
650
651	if ( is_array( $allowed_sites ) && 0 < count( $allowed_sites ) ) {
652	$_where .= ' AND ' . $site_table_alias . '.id IN (' . implode( ',', $allowed_sites ) . ') ';
653	} else {
654	$_where .= ' AND 0 ';
655	}
656
657	return $_where;
658	}
659
660	/**
661	* Get groupd where allowed access via SQL.
662	*
663	* @param string $group_table_alias Child site table alias.
664	* @param string $with_staging yes\|no Is child site a staging site.
665	*
666	* @return boolean\|null $_where Database query results or null on failer.
667	*/
668	public function get_sql_where_allow_groups( $group_table_alias = '', $with_staging = 'no' ) {
669	// To fix bug run from cron job.
670	if ( defined( 'DOING_CRON' ) && DOING_CRON ) {
671	return '';
672	}
673
674	if ( empty( $group_table_alias ) ) {
675	$group_table_alias = $this->table_name( 'group' );
676	}
677
678	// check to filter the staging group.
679	$where_staging_group = '';
680	$staging_group = get_option( 'mainwp_stagingsites_group_id' );
681	if ( $staging_group ) {
682	$where_staging_group = ' AND ' . $group_table_alias . '.id <> ' . $staging_group . ' ';
683	if ( 'yes' === $with_staging ) {
684	$where_staging_group = '';
685	}
686	}
687
688	// end staging filter.
689	$_where = $where_staging_group;
690
691	/**
692	* Filter: mainwp_currentuserallowedaccessgroups
693	*
694	* Filters allowed groups for the current user.
695	*
696	* @since Unknown
697	*/
698	$allowed_groups = apply_filters( 'mainwp_currentuserallowedaccessgroups', 'all' );
699
700	if ( 'all' === $allowed_groups ) {
701	return $_where;
702	}
703
704	if ( is_array( $allowed_groups ) && 0 < count( $allowed_groups ) ) {
705	return ' AND ' . $group_table_alias . '.id IN (' . implode( ',', $allowed_groups ) . ') ' . $_where;
706	} else {
707	return ' AND 0 ';
708	}
709	}
710
711	/**
712	* Get child site by id.
713	*
714	* @param int $id Child site ID.
715	* @param array $selectGroups Select groups.
716	* @param array $fields Get extra option fields.
717	*
718	* @return object\|null Database query results or null on failure.
719	*/
720	public function get_website_by_id( $id, $selectGroups = false, $fields = array() ) {
721	return $this->get_row_result( $this->get_sql_website_by_id( $id, $selectGroups, $fields ) );
722	}
723
724	/**
725	* Get child site by id via SQL.
726	*
727	* @param int $id Child site ID.
728	* @param array $selectGroups Selected groups.
729	* @param mixed $extra_view Extra view value.
730	*
731	* @return object\|null Database query result or null on failure.
732	*/
733	public function get_sql_website_by_id( $id, $selectGroups = false, $extra_view = array() ) {
734
735	if ( empty( $extra_view ) ) {
736	$extra_view = array( 'favi_icon' );
737	}
738
739	if ( MainWP_Utility::ctype_digit( $id ) ) {
740	$where = $this->get_sql_where_allow_access_sites( 'wp', 'nocheckstaging' );
741	if ( $selectGroups ) {
742	return 'SELECT wp.,wp_sync.,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ", ") as wpgroups
743	FROM ' . $this->table_name( 'wp' ) . ' wp
744	LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
745	LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
746	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
747	JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
748	WHERE wp.id = ' . $id . $where . '
749	GROUP BY wp.id';
750	}
751
752	return 'SELECT wp.,wp_sync.,wp_optionview.*
753	FROM ' . $this->table_name( 'wp' ) . ' wp
754	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
755	JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
756	WHERE id = ' . $id . $where;
757	}
758
759	return null;
760	}
761
762	/**
763	* Method get_websites_by_ids()
764	*
765	* Get child sites by child site IDs.
766	*
767	* @param array $ids Child site IDs.
768	* @param int $userId User ID.
769	*
770	* @return object\|null Database query result or null on failure.
771	*/
772	public function get_websites_by_ids( $ids, $userId = null ) {
773	if ( ( null == $userId ) && MainWP_System::instance()->is_multi_user() ) {
774
775	/**
776	* Current user global.
777	*
778	* @global string
779	*/
780	global $current_user;
781
782	$userId = $current_user->ID;
783	}
784	$where = $this->get_sql_where_allow_access_sites();
785
786	return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' WHERE id IN (' . implode( ',', $ids ) . ')' . ( null != $userId ? ' AND userid = ' . $userId : '' ) . $where, OBJECT );
787	}
788
789	/**
790	* Get child sites by groups IDs.
791	*
792	* @param array $ids Groups IDs.
793	* @param int $userId User ID.
794	*
795	* @return object\|null Database query result or null on failure.
796	*/
797	public function get_websites_by_group_ids( $ids, $userId = null ) {
798	if ( empty( $ids ) ) {
799	return array();
800	}
801	if ( ( null == $userId ) && MainWP_System::instance()->is_multi_user() ) {
802
803	/**
804	* Current user global.
805	*
806	* @global string
807	*/
808	global $current_user;
809
810	$userId = $current_user->ID;
811	}
812
813	return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid WHERE wpgroup.groupid IN (' . implode( ',', $ids ) . ') ' . ( null != $userId ? ' AND wp.userid = ' . $userId : '' ), OBJECT );
814	}
815
816	/**
817	* Get child sites by group ID.
818	*
819	* @param int $id Group ID.
820	*
821	* @return object\|null Database query result or null on failure.
822	*/
823	public function get_websites_by_group_id( $id ) {
824	return $this->get_results_result( $this->get_sql_websites_by_group_id( $id ) );
825	}
826
827	/**
828	* Get child sites by group id via SQL.
829	*
830	* @param int $id Group ID.
831	* @param bool $selectgroups Selected groups. Default: false.
832	* @param string $orderBy Order list by. Default: URL.
833	* @param bool $offset Query offset. Default: false.
834	* @param bool $rowcount Row count. Default: falese.
835	* @param null $where SQL WHERE value.
836	* @param null $search_site Site search field value. Default: null.
837	*
838	* @return object\|null Return database query or null on failure.
839	*/
840	public function get_sql_websites_by_group_id(
841	$id,
842	$selectgroups = false,
843	$orderBy = 'wp.url',
844	$offset = false,
845	$rowcount = false,
846	$where = null,
847	$search_site = null ) {
848
849	$is_staging = 'no';
850	if ( $selectgroups ) {
851	$staging_group = get_option( 'mainwp_stagingsites_group_id' );
852	if ( $staging_group ) {
853	if ( $id == $staging_group ) {
854	$is_staging = 'yes';
855	}
856	}
857	}
858
859	$where_search = '';
860	if ( ! empty( $search_site ) ) {
861	$search_site = trim( $search_site );
862	$where_search .= ' AND (wp.name LIKE "%' . $this->escape( $search_site ) . '%" OR wp.url LIKE "%' . $this->escape( $search_site ) . '%") ';
863	}
864
865	if ( MainWP_Utility::ctype_digit( $id ) ) {
866	$where_allowed = $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
867	if ( $selectgroups ) {
868	$qry = 'SELECT wp.,wp_sync.,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ", ") as wpgroups
869	FROM ' . $this->table_name( 'wp' ) . ' wp
870	JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
871	LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
872	LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
873	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
874	JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
875	WHERE wpgroup.groupid = ' . $id . ' ' .
876	( null == $where ? '' : ' AND ' . $where ) . $where_allowed . $where_search . '
877	GROUP BY wp.id
878	ORDER BY ' . $orderBy;
879	} else {
880	$qry = 'SELECT wp.,wp_sync. FROM ' . $this->table_name( 'wp' ) . ' wp
881	JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
882	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
883	WHERE wpgroup.groupid = ' . $id . ' ' . $where_allowed . $where_search .
884	( null == $where ? '' : ' AND ' . $where ) . ' ORDER BY ' . $orderBy;
885	}
886	if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
887	$qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
888	}
889
890	return $qry;
891	}
892
893	return null;
894	}
895
896	/**
897	* Get child sites by group name.
898	*
899	* @param int $userid Current user ID.
900	* @param string $groupname Group name.
901	*
902	* @return object\|null Database query result or null on failure.
903	*/
904	public function get_websites_by_group_name( $userid, $groupname ) {
905	return $this->get_results_result( $this->get_sql_websites_by_group_name( $groupname, $userid ) );
906	}
907
908	/**
909	* Get child sites by group name.
910	*
911	* @param string $groupname Group name.
912	* @param int $userid Current user ID.
913	*
914	* @return object\|null Database query result or null on failure.
915	*/
916	public function get_sql_websites_by_group_name( $groupname, $userid = null ) {
917	if ( ( null == $userid ) && MainWP_System::instance()->is_multi_user() ) {
918
919	/**
920	* Current user global.
921	*
922	* @global string
923	*/
924	global $current_user;
925
926	$userid = $current_user->ID;
927	}
928
929	$sql = 'SELECT wp.,wp_sync.,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
930	INNER JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
931	JOIN ' . $this->table_name( 'group' ) . ' g ON wpgroup.groupid = g.id
932	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
933	JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
934	WHERE g.name="' . $this->escape( $groupname ) . '"';
935	if ( null != $userid ) {
936	$sql .= ' AND g.userid = "' . $userid . '"';
937	}
938
939	return $sql;
940	}
941
942	/**
943	* Get child site IP address.
944	*
945	* @param int $wpid Child site ID.
946	*
947	* @return string\|null Child site IP address or null on failure.
948	*/
949	public function get_wp_ip( $wpid ) {
950	return $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT ip FROM ' . $this->table_name( 'request_log' ) . ' WHERE wpid = %d', $wpid ) );
951	}
952
953	/**
954	* Add website to the MainWP Dashboard.
955	*
956	* @param int $userid Current user ID.
957	* @param string $name Child site name.
958	* @param string $url Child site URL.
959	* @param string $admin Child site administrator username.
960	* @param string $pubkey OpenSSL public key.
961	* @param string $privkey OpenSSL private key.
962	* @param mixed $nossl SSL suppoted connection.
963	* @param mixed $nosslkey SSL not supported connection key.
964	* @param array $groupids Group IDs.
965	* @param array $groupnames Group names.
966	* @param int $verifyCertificate Whether or not to verify SSL Certificate.
967	* @param string $uniqueId Unique security ID.
968	* @param string $http_user HTTP Basic Authentication username.
969	* @param string $http_pass HTTP Basic Authentication password.
970	* @param int $sslVersion SSL Version.
971	* @param int $wpe Is it WP Engine hosted site.
972	* @param int $isStaging Whether or not child site is staging site.
973	*
974	* @return int\|false Child site ID or false on failure.
975	*/
976	public function add_website(
977	$userid,
978	$name,
979	$url,
980	$admin,
981	$pubkey,
982	$privkey,
983	$nossl,
984	$nosslkey,
985	$groupids,
986	$groupnames,
987	$verifyCertificate = 1,
988	$uniqueId = '',
989	$http_user,
990	$http_pass,
991	$sslVersion = 0,
992	$wpe = 0,
993	$isStaging = 0 ) {
994
995	if ( MainWP_Utility::ctype_digit( $userid ) && ( 0 == $nossl \|\| 1 == $nossl ) ) {
996	$values = array(
997	'userid' => $userid,
998	'adminname' => $this->escape( $admin ),
999	'name' => $this->escape( wp_strip_all_tags( $name ) ),
1000	'url' => $this->escape( $url ),
1001	'pubkey' => $this->escape( $pubkey ),
1002	'privkey' => $this->escape( $privkey ),
1003	'nossl' => $nossl,
1004	'nosslkey' => ( null == $nosslkey ? '' : $this->escape( $nosslkey ) ),
1005	'siteurl' => '',
1006	'ga_id' => '',
1007	'gas_id' => 0,
1008	'offline_checks_last' => 0,
1009	'offline_check_result' => 0,
1010	'note' => '',
1011	'statsUpdate' => 0,
1012	'directories' => '',
1013	'plugin_upgrades' => '',
1014	'theme_upgrades' => '',
1015	'translation_upgrades' => '',
1016	'securityIssues' => '',
1017	'themes' => '',
1018	'ignored_themes' => '',
1019	'plugins' => '',
1020	'ignored_plugins' => '',
1021	'pages' => '',
1022	'users' => '',
1023	'categories' => '',
1024	'pluginDir' => '',
1025	'automatic_update' => 0,
1026	'backup_before_upgrade' => 2,
1027	'verify_certificate' => intval( $verifyCertificate ),
1028	'ssl_version' => $sslVersion,
1029	'uniqueId' => $uniqueId,
1030	'mainwpdir' => 0,
1031	'http_user' => $http_user,
1032	'http_pass' => $http_pass,
1033	'wpe' => $wpe,
1034	'is_staging' => $isStaging,
1035	);
1036
1037	$syncValues = array(
1038	'dtsSync' => 0,
1039	'dtsSyncStart' => 0,
1040	'dtsAutomaticSync' => 0,
1041	'dtsAutomaticSyncStart' => 0,
1042	'totalsize' => 0,
1043	'extauth' => '',
1044	'sync_errors' => '',
1045	);
1046	if ( $this->wpdb->insert( $this->table_name( 'wp' ), $values ) ) {
1047	$websiteid = $this->wpdb->insert_id;
1048	$syncValues['wpid'] = $websiteid;
1049	$this->wpdb->insert( $this->table_name( 'wp_sync' ), $syncValues );
1050	$this->wpdb->insert(
1051	$this->table_name( 'wp_settings_backup' ),
1052	array(
1053	'wpid' => $websiteid,
1054	'archiveFormat' => 'global',
1055	)
1056	);
1057
1058	foreach ( $groupnames as $groupname ) {
1059	if ( $this->wpdb->insert(
1060	$this->table_name( 'group' ),
1061	array(
1062	'userid' => $userid,
1063	'name' => $this->escape( htmlspecialchars( $groupname ) ),
1064	)
1065	)
1066	) {
1067	$groupids[] = $this->wpdb->insert_id;
1068	}
1069	}
1070	// add groupids.
1071	foreach ( $groupids as $groupid ) {
1072	$this->wpdb->insert(
1073	$this->table_name( 'wp_group' ),
1074	array(
1075	'wpid' => $websiteid,
1076	'groupid' => $groupid,
1077	)
1078	);
1079	}
1080
1081	return $websiteid;
1082	}
1083	}
1084
1085	return false;
1086	}
1087
1088	/**
1089	* Remove child site from the MainWP Dashboard.
1090	*
1091	* @param int $websiteid Child site ID.
1092	*
1093	* @return int\|boolean Return child site ID that was removed or false on failure.
1094	*/
1095	public function remove_website( $websiteid ) {
1096	if ( MainWP_Utility::ctype_digit( $websiteid ) ) {
1097	$nr = $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp' ) . ' WHERE id=%d', $websiteid ) );
1098	$this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE wpid=%d', $websiteid ) );
1099	$this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_sync' ) . ' WHERE wpid=%d', $websiteid ) );
1100	$this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid=%d', $websiteid ) );
1101	$this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_status' ) . ' WHERE wpid=%d', $websiteid ) );
1102
1103	return $nr;
1104	}
1105
1106	return false;
1107	}
1108
1109	/**
1110	* Update child site db values.
1111	*
1112	* @param int $websiteid Child site ID.
1113	* @param array $fields Database fields to update.
1114	*
1115	* @return int\|boolean The number of rows updated, or false on error.
1116	*/
1117	public function update_website_values( $websiteid, $fields ) {
1118	if ( 0 < count( $fields ) ) {
1119	return $this->wpdb->update( $this->table_name( 'wp' ), $fields, array( 'id' => $websiteid ) );
1120	}
1121
1122	return false;
1123	}
1124
1125	/**
1126	* Update child site sync values.
1127	*
1128	* @param int $websiteid Child site ID.
1129	* @param array $fields Database fields to update.
1130	*
1131	* @return int\|boolean The number of rows updated, or false on error.
1132	*/
1133	public function update_website_sync_values( $websiteid, $fields ) {
1134	if ( 0 < count( $fields ) ) {
1135	return $this->wpdb->update( $this->table_name( 'wp_sync' ), $fields, array( 'wpid' => $websiteid ) );
1136	}
1137
1138	return false;
1139	}
1140
1141	/**
1142	* Update child site.
1143	*
1144	* @param int $websiteid Website ID.
1145	* @param string $url Child site URL.
1146	* @param int $userid Current user ID.
1147	* @param string $name Child site name.
1148	* @param string $siteadmin Child site administrator username.
1149	* @param array $groupids Group IDs.
1150	* @param array $groupnames Group Names.
1151	* @param string $pluginDir Plugin directory.
1152	* @param mixed $maximumFileDescriptorsOverride Overwrite the Maximum File Descriptors option.
1153	* @param mixed $maximumFileDescriptorsAuto Auto set the Maximum File Descriptors option.
1154	* @param mixed $maximumFileDescriptors Set the Maximum File Descriptors option.
1155	* @param int $verifyCertificate Whether or not to verify SSL Certificate.
1156	* @param mixed $archiveFormat Backup archive formate.
1157	* @param string $uniqueId Unique security ID.
1158	* @param string $http_user HTTP Basic Authentication username.
1159	* @param string $http_pass HTTP Basic Authentication password.
1160	* @param int $sslVersion SSL Version.
1161	* @param int $disableChecking Wether or not disable sites status checking.
1162	* @param int $checkInterval Status checking interval.
1163	* @param bool $disableHealthChecking Disable Site health threshold.
1164	* @param int $healthThreshold Site health threshold.
1165	* @param int $wpe Is it WP Engine hosted site.
1166	*
1167	* @return boolean ture on success or false on failure.
1168	*/
1169	public function update_website(
1170	$websiteid,
1171	$url,
1172	$userid,
1173	$name,
1174	$siteadmin,
1175	$groupids,
1176	$groupnames,
1177	$pluginDir,
1178	$maximumFileDescriptorsOverride,
1179	$maximumFileDescriptorsAuto,
1180	$maximumFileDescriptors,
1181	$verifyCertificate = 1,
1182	$archiveFormat,
1183	$uniqueId = '',
1184	$http_user = null,
1185	$http_pass = null,
1186	$sslVersion = 0,
1187	$disableChecking,
1188	$checkInterval,
1189	$disableHealthChecking,
1190	$healthThreshold,
1191	$wpe = 0 ) {
1192
1193	if ( MainWP_Utility::ctype_digit( $websiteid ) && MainWP_Utility::ctype_digit( $userid ) ) {
1194	$website = self::instance()->get_website_by_id( $websiteid );
1195	if ( MainWP_System_Utility::can_edit_website( $website ) ) {
1196	// update admin.
1197	$this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET url="' . $this->escape( $url ) . '", name="' . $this->escape( wp_strip_all_tags( $name ) ) . '", adminname="' . $this->escape( $siteadmin ) . '",pluginDir="' . $this->escape( $pluginDir ) . '",maximumFileDescriptorsOverride = ' . ( $maximumFileDescriptorsOverride ? 1 : 0 ) . ',maximumFileDescriptorsAuto= ' . ( $maximumFileDescriptorsAuto ? 1 : 0 ) . ',maximumFileDescriptors = ' . $maximumFileDescriptors . ', verify_certificate="' . intval( $verifyCertificate ) . '", ssl_version="' . intval( $sslVersion ) . '", wpe="' . intval( $wpe ) . '", uniqueId="' . $this->escape( $uniqueId ) . '", http_user="' . $this->escape( $http_user ) . '", http_pass="' . $this->escape( $http_pass ) . '", disable_status_check="' . $this->escape( $disableChecking ) . '", status_check_interval="' . $this->escape( $checkInterval ) . '", disable_health_check="' . $this->escape( $disableHealthChecking ) . '", health_threshold="' . $this->escape( $healthThreshold ) . '" WHERE id=%d', $websiteid ) );
1198	$this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp_settings_backup' ) . ' SET archiveFormat = "' . $this->escape( $archiveFormat ) . '" WHERE wpid=%d', $websiteid ) );
1199	// remove groups.
1200	$this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE wpid=%d', $websiteid ) );
1201	// Remove GA stats.
1202	$showErrors = $this->wpdb->hide_errors();
1203
1204	/**
1205	* Action: mainwp_ga_delete_site
1206	*
1207	* Fires upon site removal process in order to delete Google Analytics data.
1208	*
1209	* @param int $websiteid Child site ID.
1210	*
1211	* @since Unknown
1212	*/
1213	do_action( 'mainwp_ga_delete_site', $websiteid );
1214
1215	if ( $showErrors ) {
1216	$this->wpdb->show_errors();
1217	}
1218	// add groups with groupnames.
1219	foreach ( $groupnames as $groupname ) {
1220	if ( $this->wpdb->insert(
1221	$this->table_name( 'group' ),
1222	array(
1223	'userid' => $userid,
1224	'name' => $this->escape( $groupname ),
1225	)
1226	)
1227	) {
1228	$groupids[] = $this->wpdb->insert_id;
1229	}
1230	}
1231	// add groupids.
1232	foreach ( $groupids as $groupid ) {
1233	$this->wpdb->insert(
1234	$this->table_name( 'wp_group' ),
1235	array(
1236	'wpid' => $websiteid,
1237	'groupid' => $groupid,
1238	)
1239	);
1240	}
1241
1242	return true;
1243	}
1244	}
1245
1246	return false;
1247	}
1248
1249	/**
1250	* Get websites check updates count.
1251	*
1252	* @param int $lasttime_start Lasttime start automatic update.
1253	*
1254	* @return int Child sites update count.
1255	*/
1256	public function get_websites_check_updates_count( $lasttime_start ) {
1257	$where = $this->get_sql_where_allow_access_sites( 'wp' );
1258
1259	return $this->wpdb->get_var( 'SELECT count(wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE ( wp_sync.dtsAutomaticSyncStart = 0 OR wp_sync.dtsAutomaticSyncStart < ' . intval( $lasttime_start ) . ')' . $where );
1260	}
1261
1262	/**
1263	* Get child site count where date & time Session sync is smaller then start.
1264	*
1265	* @return int Returned child site count.
1266	*/
1267	public function get_websites_count_where_dts_automatic_sync_smaller_then_start() {
1268	$where = $this->get_sql_where_allow_access_sites( 'wp' );
1269
1270	return $this->wpdb->get_var( 'SELECT count(wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE ((wp_sync.dtsAutomaticSync < wp_sync.dtsAutomaticSyncStart) OR (wp_sync.dtsAutomaticSyncStart = 0)) ' . $where );
1271	}
1272
1273	/**
1274	* Get child site last automatic sync date & time.
1275	*
1276	* @return string Date and time of last automatic sync.
1277	*/
1278	public function get_websites_last_automatic_sync() {
1279	return $this->wpdb->get_var( 'SELECT MAX(wp_sync.dtsAutomaticSync) FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid' );
1280	}
1281
1282	/**
1283	* Get child sites check updates.
1284	*
1285	* @param int $limit Query limit.
1286	* @param int $lasttime_start Lasttime start automatic update.
1287	*
1288	* @return object\|null Database query result or null on failure.
1289	*/
1290	public function get_websites_check_updates( $limit, $lasttime_start ) {
1291	$where = $this->get_sql_where_allow_access_sites( 'wp' );
1292
1293	return $this->wpdb->get_results( 'SELECT wp.,wp_sync.,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid WHERE ( wp_sync.dtsAutomaticSync = 0 OR wp_sync.dtsAutomaticSyncStart = 0 OR wp_sync.dtsAutomaticSyncStart < ' . intval( $lasttime_start ) . ') ' . $where . ' LIMIT 0,' . $limit, OBJECT );
1294	}
1295
1296	/**
1297	* Get website update stats via SQL.
1298	*
1299	* @return object\|null Database query result of null on failure.
1300	*/
1301	public function get_websites_stats_update_sql() {
1302	$where = $this->get_sql_where_allow_access_sites();
1303	return 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' WHERE (statsUpdate = 0 OR ' . time() . ' - statsUpdate >= ' . ( 60 * 60 * 24 * 7 ) . ')' . $where . ' ORDER BY statsUpdate ASC';
1304	}
1305
1306	/**
1307	* Update child site statistics.
1308	*
1309	* Update whether or not a child site has been updated.
1310	*
1311	* @param mixed $websiteid Child site ID.
1312	* @param mixed $statsUpdated Child site Update status.
1313	*
1314	* @return (int\|boolean) Number of rows effected in update or false on failure.
1315	*/
1316	public function update_website_stats( $websiteid, $statsUpdated ) {
1317	return $this->wpdb->update(
1318	$this->table_name( 'wp' ),
1319	array( 'statsUpdate' => $statsUpdated ),
1320	array( 'id' => $websiteid )
1321	);
1322	}
1323
1324	/**
1325	* Get child site by url.
1326	*
1327	* @param string $url Child site URL.
1328	*
1329	* @return object\|null Database query result or null on failure.
1330	*/
1331	public function get_websites_by_url( $url ) {
1332	if ( '/' != substr( $url, - 1 ) ) {
1333	$url .= '/';
1334	}
1335	$where = '';
1336	$results = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' WHERE url = %s ' . $where, $this->escape( $url ) ), OBJECT );
1337	if ( $results ) {
1338	return $results;
1339	}
1340
1341	if ( stristr( $url, '/www.' ) ) {
1342	// remove www if it's there!
1343	$url = str_replace( '/www.', '/', $url );
1344	} else {
1345	// add www if it's not there!
1346	$url = str_replace( 'https://', 'https://www.', $url );
1347	$url = str_replace( 'http://', 'http://www.', $url );
1348	}
1349
1350	return $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' WHERE url = %s ' . $where, $this->escape( $url ) ), OBJECT );
1351	}
1352
1353	/**
1354	* Get websites offline status.
1355	*
1356	* @return array Child site monitoring status.
1357	*/
1358	public function get_websites_offline_status_to_send_notice() {
1359	$where = $this->get_sql_where_allow_access_sites( 'wp' );
1360	$extra_view = array( 'monitoring_notification_emails', 'settings_notification_emails' );
1361
1362	return $this->wpdb->get_results(
1363	'SELECT wp.,wp_sync.,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
1364	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1365	JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1366	WHERE wp.disable_status_check <> 1 AND wp.offline_check_result <> 1 AND wp.offline_check_result <> 0 AND wp.http_code_noticed = 0' . // http_code_noticed = 0: not noticed yet.
1367	$where,
1368	OBJECT
1369	);
1370	}
1371
1372	/**
1373	* Method get_websites_to_notice_health_threshold()
1374	*
1375	* Get websites to notice site health.
1376	*
1377	* @param int $globalThreshold Global site health threshold.
1378	* @param int $count Limit count.
1379	*/
1380	public function get_websites_to_notice_health_threshold( $globalThreshold, $count = 10 ) {
1381
1382	$where = $this->get_sql_where_allow_access_sites( 'wp' );
1383	$extra_view = array( 'monitoring_notification_emails', 'settings_notification_emails' );
1384
1385	if ( 80 >= $globalThreshold ) { // actual is 80.
1386	// should-be-improved site health.
1387	$where_global_threshold = '( wp.health_threshold = 0 AND wp_sync.health_value < 80 )';
1388	} else {
1389	// good site health.
1390	$where_global_threshold = '( wp.health_threshold = 0 AND wp_sync.health_value >= 80 )';
1391	}
1392
1393	$where_site_threshold = ' ( wp.health_threshold = 80 AND wp_sync.health_value < 80 ) '; // should-be-improved site health.
1394	$where_site_threshold .= ' OR ( wp.health_threshold = 100 AND wp_sync.health_value >= 80 ) '; // good site health.
1395
1396	return $this->wpdb->get_results(
1397	'SELECT wp.,wp_sync.,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
1398	JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1399	JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1400	WHERE wp.disable_health_check <> 1 AND wp.offline_check_result = 1 AND ( ' . $where_global_threshold . ' OR' . $where_site_threshold . ' ) AND wp_sync.health_site_noticed = 0 ' .
1401	$where,
1402	OBJECT
1403	);
1404	}
1405
1406	/**
1407	* Get websites offline status.
1408	*
1409	* @return array Sites with offline status.
1410	*/
1411	public function get_websites_offline_check_status() {
1412	$where = $this->get_sql_where_allow_access_sites( 'wp' );
1413	$extra_view = array( 'settings_notification_emails' );
1414
1415	return $this->wpdb->get_results(
1416	'SELECT wp.,wp_optionview. FROM ' . $this->table_name( 'wp' ) . ' wp
1417	JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1418	WHERE wp.disable_status_check <> 1 AND wp.offline_check_result = -1' . // offline checked status.
1419	$where,
1420	OBJECT
1421	);
1422	}
1423	}
1424