PluginProbe
MainWP Dashboard: Self-hosted WordPress Management for Agencies / 4.4.1
MainWP Dashboard: Self-hosted WordPress Management for Agencies v4.4.1
6.2 6.1.8 6.1.7 6.1.6 6.1.5 6.1.4 6.1.3 6.1.2 6.1.1 6.1 6.0.12 6.0.11 4.6.0.1 5.0 5.0.1 5.0.2 5.0.3 5.0.3.1 5.0.3.2 5.1 5.1.1 5.2 5.2.1 5.2.2 5.3 All 153 releases
mainwp / class / class-mainwp-db.php

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

2,115 lines 71.8 KB
No matching file
Up and down to move Enter to open Esc to close
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 * @uses \MainWP\Dashboard\MainWP_DB_Base
18 */
19 class MainWP_DB extends MainWP_DB_Base {
20
21 // 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.
22
23 /**
24 * Private static variable to hold the single instance of the class.
25 *
26 * @static
27 *
28 * @var mixed Default null
29 */
30 private static $instance = null;
31
32 /**
33 * Private static variable to hold the single instance.
34 *
35 * @static
36 *
37 * @var mixed Default null
38 */
39 private static $general_options = null;
40
41 /**
42 * Create public static instance.
43 *
44 * @static
45 *
46 * @return MainWP_DB
47 */
48 public static function instance() {
49 if ( null == self::$instance ) {
50 self::$instance = new self();
51 }
52
53 self::$instance->test_connection();
54
55 return self::$instance;
56 }
57
58 /**
59 * Get wp_options database table view.
60 *
61 * @param array $fields Extra option fields.
62 * @param bool $default Whether or not to get default option fields.
63 *
64 * @return array wp_options view.
65 */
66 public function get_option_view( $fields = array(), $default = true ) {
67
68 $view = '(SELECT intwp.id AS wpid ';
69
70 if ( empty( $fields ) || $default ) {
71 $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,
72 (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,
73 (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,
74 (SELECT phpversion.value FROM ' . $this->table_name( 'wp_options' ) . ' phpversion WHERE phpversion.wpid = intwp.id AND phpversion.name = "phpversion" LIMIT 1) AS phpversion,
75 (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 ';
76 }
77
78 if ( is_array( $fields ) ) {
79 foreach ( $fields as $field ) {
80 if ( empty( $field ) ) {
81 continue;
82 }
83 $view .= ', ';
84 $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 );
85 }
86 }
87
88 $view .= ' FROM ' . $this->table_name( 'wp' ) . ' intwp)';
89
90 return $view;
91 }
92
93 /**
94 * Get connected child sites.
95 *
96 * @param array $sites_ids Websites ids - option field.
97 *
98 * @return array $connected_sites Array of connected sites.
99 */
100 public function get_connected_websites( $sites_ids = false ) {
101 $where = $this->get_sql_where_allow_access_sites( 'wp' );
102
103 $sql = 'SELECT wp.*,wp_sync.*
104 FROM ' . $this->table_name( 'wp' ) . ' wp
105 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync
106 ON wp.id = wp_sync.wpid
107 WHERE (wp_sync.sync_errors IS NOT NULL) AND (wp_sync.sync_errors = "") ' .
108 $where;
109
110 $websites = $this->wpdb->get_results( $sql );
111 $connected_sites = array();
112 if ( $websites ) {
113 foreach ( $websites as $website ) {
114
115 if ( ! empty( $sites_ids ) ) {
116 // filter sites.
117 if ( ! in_array( $website->id, $sites_ids ) ) {
118 continue;
119 }
120 }
121
122 $connected_sites[] = array(
123 'id' => $website->id,
124 'name' => $website->name,
125 'url' => $website->url,
126 );
127 }
128 }
129 return $connected_sites;
130 }
131
132 /**
133 * Get disconnected child sites.
134 *
135 * @param array $sites_ids Websites ids - option field.
136 *
137 * @return array $disc_sites Array of disonnected sites.
138 */
139 public function get_disconnected_websites( $sites_ids = false ) {
140 $where = $this->get_sql_where_allow_access_sites( 'wp' );
141
142 $sql = 'SELECT wp.*,wp_sync.*
143 FROM ' . $this->table_name( 'wp' ) . ' wp
144 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync
145 ON wp.id = wp_sync.wpid
146 WHERE (wp_sync.sync_errors IS NOT NULL) AND (wp_sync.sync_errors <> "") ' .
147 $where;
148
149 $websites = $this->wpdb->get_results( $sql );
150 $disc_sites = array();
151 if ( $websites ) {
152 foreach ( $websites as $website ) {
153
154 if ( ! empty( $sites_ids ) ) {
155 // filter sites.
156 if ( ! in_array( $website->id, $sites_ids ) ) {
157 continue;
158 }
159 }
160
161 $disc_sites[] = array(
162 'id' => $website->id,
163 'name' => $website->name,
164 'url' => $website->url,
165 );
166 }
167 }
168 return $disc_sites;
169 }
170
171 /**
172 * Get child site count.
173 *
174 * @param null $userId Current user ID.
175 * @param bool $all_access Check if user has access to all sites.
176 *
177 * @return int Child site count.
178 *
179 * @uses \MainWP\Dashboard\MainWP_System::is_multi_user()
180 */
181 public function get_websites_count( $userId = null, $all_access = false ) {
182 if ( ( null == $userId ) && MainWP_System::instance()->is_multi_user() ) {
183
184 /**
185 * Current user global.
186 *
187 * @global string
188 */
189 global $current_user;
190
191 $userId = $current_user->ID;
192 }
193 $where = ( null == $userId ? '' : ' wp.userid = ' . $userId );
194 if ( ! $all_access ) {
195 $where .= $this->get_sql_where_allow_access_sites( 'wp' );
196 }
197 $qry = 'SELECT COUNT(wp.id) FROM ' . $this->table_name( 'wp' ) . ' wp WHERE 1 ' . $where;
198
199 return $this->wpdb->get_var( $qry );
200 }
201
202 /**
203 * Get Child site wp_options database table.
204 *
205 * @param array $website Child Site array.
206 * @param mixed $option Child Site wp_options table name.
207 * @param mixed $default default value.
208 *
209 * @return string|null Database query result (as string), or null on failure.
210 */
211 public function get_website_option( $website, $option, $default = null ) {
212
213 if ( is_array( $website ) ) {
214 if ( isset( $website[ $option ] ) ) {
215 return $website[ $option ];
216 }
217 $site_id = $website['id'];
218 } elseif ( is_object( $website ) ) {
219 if ( property_exists( $website, $option ) ) {
220 return $website->{$option};
221 }
222 $site_id = $website->id;
223 } elseif ( is_numeric( $website ) ) { // to support $site_id = 0, for global options.
224 $site_id = $website;
225 } else {
226 return false;
227 }
228
229 $var = $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 ) );
230
231 if ( null === $var && null !== $default ) {
232 $var = $default;
233 }
234 return $var;
235 }
236
237 /**
238 * Get child site options.
239 *
240 * @param array $website Child site.
241 * @param mixed $options Child site options name.
242 *
243 * @return string|null Database query result (as string), or null on failure.
244 */
245 public function get_website_options_array( &$website, $options ) {
246
247 if ( ! is_array( $options ) || empty( $options ) ) {
248 return array();
249 }
250
251 if ( is_array( $website ) ) {
252 $site_id = $website['id'];
253 } elseif ( is_object( $website ) ) {
254 $site_id = $website->id;
255 } elseif ( is_numeric( $website ) ) { // to support $site_id = 0 for global options.
256 $site_id = $website;
257 } else {
258 return array();
259 }
260
261 $arr_options = array();
262 $get_options = array();
263
264 foreach ( $options as $option ) {
265 if ( is_array( $website ) ) {
266 if ( isset( $website[ $option ] ) ) {
267 $arr_options[ $option ] = $website[ $option ];
268 } else {
269 $get_options[] = $option;
270 }
271 } elseif ( is_object( $website ) ) {
272 if ( property_exists( $website, $option ) ) {
273 $arr_options[ $option ] = $website->{$option};
274 } else {
275 $get_options[] = $option;
276 }
277 } else {
278 $get_options[] = $option;
279 }
280 }
281
282 if ( empty( $get_options ) ) {
283 return $arr_options; // all options.
284 }
285
286 $options_name = implode( "','", $get_options );
287 $options_name = "'" . $options_name . "'";
288
289 $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 ) );
290
291 $fill_options = array(
292 'primary_lasttime_backup',
293 );
294
295 foreach ( (array) $options_db as $o ) {
296 $arr_options[ $o->name ] = $o->value;
297 if ( in_array( $o->name, $fill_options ) ) {
298 if ( is_array( $website ) ) {
299 if ( ! isset( $website[ $o->name ] ) ) {
300 $website[ $o->name ] = $o->value;
301 }
302 } elseif ( is_object( $website ) ) {
303 if ( ! property_exists( $website, $o->name ) ) {
304 $website->{$o->name} = $o->value;
305 }
306 }
307 }
308 }
309 return $arr_options;
310 }
311
312 /**
313 * Update child site options.
314 *
315 * @param object $website Child site object.
316 * @param mixed $option Option to update.
317 * @param mixed $value Value to update with.
318 */
319 public function update_website_option( $website, $option, $value ) {
320 $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 ) );
321 if ( 0 === count( $rslt ) ) {
322 $this->wpdb->insert(
323 $this->table_name( 'wp_options' ),
324 array(
325 'wpid' => $website->id,
326 'name' => $option,
327 'value' => $value,
328 )
329 );
330 } else {
331 $this->wpdb->update(
332 $this->table_name( 'wp_options' ),
333 array( 'value' => $value ),
334 array(
335 'wpid' => $website->id,
336 'name' => $option,
337 )
338 );
339 }
340 }
341
342
343 /**
344 * Get general Child site option.
345 *
346 * @param mixed $option Child Site option name.
347 *
348 * @return string|null Database query result (as string), or null on failure.
349 */
350 private function get_general_website_option( $option ) {
351
352 if ( null !== self::$general_options ) {
353 if ( isset( self::$general_options[ $option ] ) ) {
354 return self::$general_options[ $option ];
355 }
356 } else {
357 self::$general_options[] = array();
358 }
359
360 $val = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT value FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', 0 ) );
361
362 self::$general_options[ $option ] = $val;
363 return $val;
364 }
365
366 /**
367 * Get child site options.
368 *
369 * @param mixed $options Child site options name.
370 *
371 * @return string|null Database query result (as string), or null on failure.
372 */
373 public function get_general_options_array( $options ) {
374
375 if ( ! is_array( $options ) || empty( $options ) ) {
376 return array();
377 }
378
379 $return_options = array();
380 if ( null !== self::$general_options ) {
381 foreach ( self::$general_options as $opt => $val ) {
382 if ( in_array( $opt, $options ) ) {
383 $return_options[ $opt ] = $val;
384 }
385 }
386 } else {
387 self::$general_options[] = array();
388 }
389
390 $diff_options = array();
391 foreach ( $options as $opt ) {
392 if ( ! isset( $return_options[ $opt ] ) ) {
393 $diff_options[] = $opt;
394 }
395 }
396
397 $options_name = implode( "','", $diff_options );
398 $options_name = "'" . $options_name . "'";
399
400 $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 . ')', 0 ) );
401
402 foreach ( (array) $options_db as $o ) {
403 $return_options[ $o->name ] = $o->value;
404 self::$general_options[ $o->name ] = $o->value;
405 }
406 return $return_options;
407 }
408
409 /**
410 * Update general site options.
411 *
412 * @param mixed $option Option to update.
413 * @param mixed $value Value to update with.
414 * @param string $type_value Type values: single|array.
415 */
416 public function update_general_option( $option, $value, $type_value = 'single' ) {
417
418 if ( 'array' === $type_value ) {
419 if ( empty( $value ) ) {
420 $value = array();
421 } elseif ( ! is_array( $value ) ) {
422 return false;
423 }
424 $value = wp_json_encode( $value );
425 }
426
427 if ( null === self::$general_options ) {
428 self::$general_options[] = array();
429 }
430 self::$general_options[ $option ] = $value;
431
432 $rslt = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT name FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid = %d AND name = "' . $this->escape( $option ) . '"', 0 ) );
433
434 if ( 0 === count( $rslt ) ) {
435 $this->wpdb->insert(
436 $this->table_name( 'wp_options' ),
437 array(
438 'wpid' => 0,
439 'name' => $option,
440 'value' => $value,
441 )
442 );
443 } else {
444 $this->wpdb->update(
445 $this->table_name( 'wp_options' ),
446 array( 'value' => $value ),
447 array(
448 'wpid' => 0,
449 'name' => $option,
450 )
451 );
452 }
453 return true;
454 }
455
456 /**
457 * Get general Child site option.
458 *
459 * @param mixed $opt Child Site option name.
460 * @param string $type_value Type values: single|array.
461 *
462 * @return string|null Database query result (as string), or null on failure.
463 */
464 public function get_general_option( $opt, $type_value = 'single' ) {
465 if ( 'single' === $type_value ) {
466 return $this->get_general_website_option( $opt );
467 } elseif ( 'array' === $type_value ) {
468 $json_value = $this->get_general_website_option( $opt );
469 if ( empty( $json_value ) ) {
470 return array();
471 }
472 return json_decode( $json_value, true );
473 }
474 return false;
475 }
476
477 /**
478 * Get child sites by user ID.
479 *
480 * @param int $userid User ID.
481 * @param bool $selectgroups Selected groups.
482 * @param null $search_site Site search field value.
483 * @param string $orderBy Order list by. Default: URL.
484 *
485 * @return array|object|null Database query results or null on failer.
486 */
487 public function get_websites_by_user_id( $userid, $selectgroups = false, $search_site = null, $orderBy = 'wp.url' ) {
488 return $this->get_results_result( $this->get_sql_websites_by_user_id( $userid, $selectgroups, $search_site, $orderBy ) );
489 }
490
491 /**
492 * Get child sites.
493 *
494 * @return string SQL string.
495 */
496 public function get_sql_websites() {
497 $where = $this->get_sql_where_allow_access_sites( 'wp' );
498
499 return 'SELECT wp.*,wp_sync.*,wp_optionview.*
500 FROM ' . $this->table_name( 'wp' ) . ' wp
501 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
502 JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
503 WHERE 1 ' . $where;
504 }
505
506 /**
507 * Get child sites to run the status check process.
508 *
509 * @param int $last_check Time of the last check.
510 * @param int $count Number of websites.
511 *
512 * @return string SQL string.
513 */
514 public function get_sql_websites_to_check_status( $last_check, $count = 20 ) {
515 $where = $this->get_sql_where_allow_access_sites( 'wp' );
516 $sql = 'SELECT wp.*
517 FROM ' . $this->table_name( 'wp' ) . ' wp
518 WHERE wp.disable_status_check <> 1 AND ( wp.status_check_interval = 0 AND wp.offline_checks_last < ' . intval( $last_check ) . ' )' .
519 $where . '
520 LIMIT ' . intval( $count );
521 return $sql;
522 }
523
524
525 /**
526 * Get child sites to run the status individual check process.
527 *
528 * @param int $count Number of websites.
529 *
530 * @return string SQL string.
531 */
532 public function get_sql_websites_to_check_individual_status( $count = 20 ) {
533 $where = $this->get_sql_where_allow_access_sites( 'wp' );
534 $sql = 'SELECT wp.*
535 FROM ' . $this->table_name( 'wp' ) . ' wp
536 WHERE wp.disable_status_check <> 1 AND ( wp.status_check_interval <> 0 AND ( wp.offline_checks_last + wp.status_check_interval * 60 < UNIX_TIMESTAMP() ) )' .
537 $where . '
538 LIMIT ' . intval( $count );
539 return $sql;
540 }
541
542 /**
543 * Get child sites by user id via SQL.
544 *
545 * @param int $userid Given user ID.
546 * @param bool $selectgroups Selected groups. Default: false.
547 * @param null $search_site Site search field value. Default: null.
548 * @param string $orderBy Order list by. Default: URL.
549 * @param bool $offset Query offset. Default: false.
550 * @param bool $rowcount Row count. Default: falese.
551 *
552 * @return object|null Return database query or null on failure.
553 *
554 * @uses \MainWP\Dashboard\MainWP_Utility::ctype_digit()
555 */
556 public function get_sql_websites_by_user_id( $userid, $selectgroups = false, $search_site = null, $orderBy = 'wp.url', $offset = false, $rowcount = false ) {
557 if ( MainWP_Utility::ctype_digit( $userid ) ) {
558 $where = '';
559 if ( null !== $search_site ) {
560 $search_site = trim( $search_site );
561 $where = ' AND (wp.name LIKE "%' . $search_site . '%" OR wp.url LIKE "%' . $search_site . '%") ';
562 }
563
564 $where .= $this->get_sql_where_allow_access_sites( 'wp' );
565
566 if ( $selectgroups ) {
567 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ",") as wpgroups, GROUP_CONCAT(gr.id ORDER BY gr.name SEPARATOR ",") as wpgroupids
568 FROM ' . $this->table_name( 'wp' ) . ' wp
569 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
570 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
571 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
572 JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
573 WHERE wp.userid = ' . $userid . "
574 $where
575 GROUP BY wp.id, wp_sync.sync_id
576 ORDER BY " . $orderBy;
577 } else {
578 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*
579 FROM ' . $this->table_name( 'wp' ) . ' wp
580 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
581 JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
582 WHERE wp.userid = ' . $userid . "
583 $where
584 ORDER BY " . $orderBy;
585 }
586
587 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
588 $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
589 } elseif ( false !== $rowcount ) {
590 $qry .= ' LIMIT ' . $rowcount;
591 }
592
593 return $qry;
594 }
595
596 return null;
597 }
598
599 /**
600 * Get SQL to get child sites for current user.
601 *
602 * @param bool $selectgroups Selected groups. Default: false.
603 * @param null $search_site Site search field value. Default: null.
604 * @param string $orderBy Order list by. Default: URL.
605 * @param bool $offset Query offset. Default: false.
606 * @param bool $rowcount Row count. Default: false.
607 * @param null $extraWhere Extra WHERE. Default: null.
608 * @param bool $for_manager For role manager. Default: false.
609 * @param mixed $extra_view Extra view. Default favi_icon.
610 * @param string $is_staging yes|no Is child site a staging site.
611 * @param array $params other params.
612 *
613 * @return object|null Database query results or null on failure.
614 *
615 * @uses \MainWP\Dashboard\MainWP_System::is_multi_user()
616 */
617 public function get_sql_websites_for_current_user(
618 $selectgroups = false,
619 $search_site = null,
620 $orderBy = 'wp.url',
621 $offset = false,
622 $rowcount = false,
623 $extraWhere = null,
624 $for_manager = false,
625 $extra_view = array( 'favi_icon' ),
626 $is_staging = 'no',
627 $params = array() ) {
628
629 $where = '';
630 if ( MainWP_System::instance()->is_multi_user() ) {
631
632 /**
633 * Current user global.
634 *
635 * @global string
636 */
637 global $current_user;
638
639 $where .= ' AND wp.userid = ' . $current_user->ID . ' ';
640 }
641
642 if ( null !== $search_site ) {
643 $search_site = trim( $search_site );
644 $where .= ' AND (wp.name LIKE "%' . $search_site . '%" OR wp.url LIKE "%' . $search_site . '%") ';
645 }
646
647 if ( null !== $extraWhere ) {
648 $where .= ' AND ' . $extraWhere;
649 }
650
651 if ( ! $for_manager ) {
652 $where .= $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
653 }
654
655 $connected_sql = '';
656 if ( is_array( $params ) ) {
657 if ( isset( $params['connected'] ) ) {
658 if ( 'yes' == $params['connected'] ) {
659 $connected_sql = ' AND wp_sync.sync_errors = "" ';
660 } elseif ( 'no' == $params['connected'] ) {
661 $connected_sql = ' AND wp_sync.sync_errors <> "" ';
662 }
663 }
664 }
665
666 if ( 'wp.url' === $orderBy ) {
667 $orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '')";
668 }
669
670 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
671 if ( $selectgroups ) {
672 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ",") as wpgroups, GROUP_CONCAT(gr.id ORDER BY gr.name SEPARATOR ",") as wpgroupids,
673 wpclient.name as client_name
674 FROM ' . $this->table_name( 'wp' ) . ' wp
675 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
676 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
677 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
678 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
679 JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
680 WHERE 1 ' . $where . $connected_sql . '
681 GROUP BY wp.id, wp_sync.sync_id
682 ORDER BY ' . $orderBy;
683 } else {
684 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, wpclient.name as client_name
685 FROM ' . $this->table_name( 'wp' ) . ' wp
686 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
687 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
688 JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
689 WHERE 1 ' . $where . $connected_sql . '
690 GROUP BY wp.id, wp_sync.sync_id
691 ORDER BY ' . $orderBy;
692 }
693
694 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
695 $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
696 } elseif ( false !== $rowcount ) {
697 $qry .= ' LIMIT ' . $rowcount;
698 }
699
700 return $qry;
701 }
702
703 /**
704 * Get SQL to get wp child sites for current user.
705 *
706 * @since 4.3
707 *
708 * @param array $params params .
709 *
710 * @return object|null Database query results or null on failure.
711 */
712 public function get_sql_wp_for_current_user( $params = array() ) {
713 if ( ! is_array( $params ) ) {
714 $params = array();
715 }
716
717 $selectgroups = isset( $params['select_groups'] ) ? $params['select_groups'] : false;
718 $search_site = isset( $params['search_site'] ) && ! empty( $params['search_site'] ) ? $params['search_site'] : null;
719 $orderBy = isset( $params['order_by'] ) && ! empty( $params['order_by'] ) ? $params['order_by'] : 'wp.url';
720 $offset = isset( $params['offset'] ) ? $params['offset'] : false;
721 $rowcount = isset( $params['row_count'] ) ? $params['row_count'] : false;
722 $for_manager = isset( $params['for_manager'] ) ? $params['for_manager'] : false;
723 $extraWhere = isset( $params['extra_where'] ) && ! empty( $params['extra_where'] ) ? $params['extra_where'] : null;
724 $extra_view = isset( $params['extra_view'] ) && is_array( $params['extra_view'] ) && ! empty( $params['extra_view'] ) ? $params['extra_view'] : array( 'favi_icon' );
725 $extra_join = isset( $params['extra_join'] ) ? $params['extra_join'] : '';
726
727 $extra_select_wp_fields = isset( $params['extra_select_wp_fields'] ) && is_array( $params['extra_select_wp_fields'] ) && ! empty( $params['extra_select_wp_fields'] ) ? $params['extra_select_wp_fields'] : array();
728 $extra_select_sql_fields = isset( $params['extra_select_sql_fields'] ) && ! empty( $params['extra_select_sql_fields'] ) ? $params['extra_select_sql_fields'] : '';
729
730 $is_staging = isset( $params['is_staging'] ) && 'yes' === $params['is_staging'] ? 'yes' : 'no';
731
732 $where = '';
733
734 if ( null !== $search_site ) {
735 $search_site = trim( $search_site );
736 $where .= ' AND (wp.name LIKE "%' . $this->escape( $search_site ) . '%" OR wp.url LIKE "%' . $this->escape( $search_site ) . '%") ';
737 }
738
739 if ( null !== $extraWhere ) {
740 $where .= ' AND ' . $extraWhere;
741 }
742
743 if ( ! $for_manager ) {
744 $where .= $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
745 }
746
747 if ( 'wp.url' === $orderBy ) {
748 $orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '')";
749 }
750
751 $select_wp_fields = $this->get_sql_select_wp_valid_fields( $extra_select_wp_fields );
752
753 if ( ! empty( $extra_select_sql_fields ) ) {
754 $extra_select_sql_fields = ',' . $extra_select_sql_fields;
755 }
756
757 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
758 if ( $selectgroups ) {
759 $qry = 'SELECT' . $select_wp_fields . $extra_select_sql_fields . ',wp_sync.sync_errors,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ",") as wpgroups, GROUP_CONCAT(gr.id ORDER BY gr.name SEPARATOR ",") as wpgroupids,
760 wpclient.name as client_name
761 FROM ' . $this->table_name( 'wp' ) . ' wp
762 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
763 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
764 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
765 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
766 JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid ' .
767 $extra_join . '
768 WHERE 1 ' . $where . '
769 GROUP BY wp.id, wp_sync.sync_id
770 ORDER BY ' . $orderBy;
771 } else {
772 $qry = 'SELECT ' . $select_wp_fields . $extra_select_sql_fields . ',wp_sync.sync_errors,wp_optionview.*, wpclient.name as client_name
773 FROM ' . $this->table_name( 'wp' ) . ' wp
774 LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id
775 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
776 JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid ' .
777 $extra_join . '
778 WHERE 1 ' . $where . '
779 GROUP BY wp.id, wp_sync.sync_id
780 ORDER BY ' . $orderBy;
781 }
782
783 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
784 $qry .= ' LIMIT ' . intval( $offset ) . ', ' . intval( $rowcount );
785 } elseif ( false !== $rowcount ) {
786 $qry .= ' LIMIT ' . intval( $rowcount );
787 }
788 return $qry;
789 }
790 /**
791 * Get SQL select websites fields.
792 *
793 * @since 4.3
794 *
795 * @param array $other_fields extra select wp fields .
796 *
797 * @return string sql string.
798 */
799 public function get_sql_select_wp_valid_fields( $other_fields = array() ) {
800
801 $allow_other_fields = array(
802 'offline_checks_last',
803 'offline_check_result',
804 'http_response_code',
805 'disable_status_check',
806 'disable_health_check',
807 'status_check_interval',
808 'health_threshold',
809 'note',
810 'statsUpdate',
811 'directories',
812 'plugin_upgrades',
813 'theme_upgrades',
814 'translation_upgrades',
815 'premium_upgrades',
816 'securityIssues',
817 'themes',
818 'ignored_themes',
819 'plugins',
820 'ignored_plugins',
821 'users',
822 'categories',
823 'pluginDir',
824 'automatic_update',
825 'backup_before_upgrade',
826 'mainwpdir',
827 'is_ignoreCoreUpdates',
828 'is_ignorePluginUpdates',
829 'is_ignoreThemeUpdates',
830 'verify_certificate',
831 'force_use_ipv4',
832 'ssl_version',
833 'http_user',
834 'http_pass',
835 'wpe',
836 'is_staging',
837 'client_id',
838 );
839
840 $default_fields = array( 'id', 'url', 'name', 'adminname', 'verify_certificate', 'ssl_version', 'http_user', 'http_pass', 'suspended' );
841
842 $select = ' ';
843
844 foreach ( $default_fields as $field ) {
845 $select .= 'wp.' . $this->escape( $field ) . ',';
846 }
847 foreach ( $other_fields as $field ) {
848 if ( ! in_array( $field, $allow_other_fields ) ) {
849 continue;
850 }
851 $select .= 'wp.' . $this->escape( $field ) . ',';
852 }
853 $select = rtrim( $select, ',' );
854 return $select;
855 }
856
857 /**
858 * Get child sites for current user.
859 *
860 * @param array $params to get sites. Default: array().
861 *
862 * @return array Results or null on failure.
863 *
864 * @uses \MainWP\Dashboard\MainWP_Utility::map_site()
865 */
866 public function get_websites_for_current_user( $params = array() ) {
867 if ( ! is_array( $params ) ) {
868 $params = array();
869 }
870
871 $selectgroups = isset( $params['selectgroups'] ) ? $params['selectgroups'] : false;
872 $search_site = isset( $params['search_site'] ) ? $params['search_site'] : null;
873 $orderBy = isset( $params['order_by'] ) ? $params['order_by'] : 'wp.url';
874 $offset = isset( $params['offset'] ) ? $params['offset'] : false;
875 $rowcount = isset( $params['rowcount'] ) ? $params['rowcount'] : false;
876 $extraWhere = isset( $params['where'] ) ? $params['where'] : null;
877 $extra_view = isset( $params['extra_view'] ) && is_array( $params['extra_view'] ) ? $params['extra_view'] : array( 'favi_icon' );
878 $is_staging = isset( $params['is_staging'] ) ? $params['is_staging'] : 'no';
879 $full_data = isset( $params['full_data'] ) && $params['full_data'] && ( 'no' !== $params['full_data'] ) ? true : false;
880 $select_data = isset( $params['select_data'] ) && is_array( $params['select_data'] ) ? $params['select_data'] : false;
881 $format = isset( $params['format'] ) ? $params['format'] : '';
882 $clients = isset( $params['client'] ) ? $params['client'] : '';
883
884 $for_manager = false;
885
886 $urlsWhere = '';
887
888 if ( isset( $params['urls'] ) && ! empty( $params['urls'] ) ) {
889 $urls = explode( ';', $params['urls'] );
890 foreach ( $urls as $url ) {
891 $url = str_replace( array( 'https://www.', 'http://www.', 'https://', 'http://', 'www.' ), array( '', '', '', '', '' ), $url );
892 if ( '/' != substr( $url, - 1 ) ) {
893 $url .= '/';
894 }
895 $urlsWhere .= '"' . $this->escape( $url ) . '", ';
896 }
897 $urlsWhere = rtrim( $urlsWhere, ', ' );
898 }
899
900 if ( ! empty( $urlsWhere ) ) {
901 $urlsWhere = " ( replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '') IN ( " . $urlsWhere . ') ) ';
902
903 if ( empty( $extraWhere ) ) {
904 $extraWhere = $urlsWhere;
905 } else {
906 $extraWhere = $extraWhere . ' AND ' . $urlsWhere;
907 }
908 }
909
910 $clientWhere = '';
911 if ( ! empty( $clients ) ) {
912 $clients = explode( ';', $clients );
913 foreach ( $clients as $client ) {
914 if ( is_numeric( $client ) ) {
915 $clientWhere .= intval( $client ) . ', ';
916 }
917 }
918 $clientWhere = rtrim( $clientWhere, ', ' );
919 }
920
921 if ( ! empty( $clientWhere ) ) {
922 $clientWhere = ' ( wp.client_id IN ( ' . $clientWhere . ') ) ';
923 if ( empty( $extraWhere ) ) {
924 $extraWhere = $clientWhere;
925 } else {
926 $extraWhere = $extraWhere . ' AND ' . $clientWhere;
927 }
928 }
929
930 $data = array( 'id', 'url', 'name' );
931
932 if ( $full_data ) {
933 $data = array(
934 'id',
935 'url',
936 'name',
937 'offline_checks_last',
938 'offline_check_result',
939 'http_response_code',
940 'disable_status_check',
941 'disable_health_check',
942 'status_check_interval',
943 'health_threshold',
944 'note',
945 'plugin_upgrades',
946 'theme_upgrades',
947 'translation_upgrades',
948 'securityIssues',
949 'themes',
950 'plugins',
951 'automatic_update',
952 'sync_errors',
953 'dtsAutomaticSync',
954 'dtsAutomaticSyncStart',
955 'dtsSync',
956 'dtsSyncStart',
957 'last_post_gmt',
958 'health_value',
959 'phpversion',
960 'wp_upgrades',
961 'security_stats',
962 'client_id',
963 );
964
965 if ( ! in_array( 'security_stats', $extra_view ) ) {
966 $extra_view[] = 'security_stats';
967 }
968 }
969
970 if ( ! empty( $select_data ) && is_array( $select_data ) ) {
971 $data = $select_data;
972 }
973
974 if ( $selectgroups ) {
975 $data[] = 'wpgroups';
976 }
977
978 $dbwebsites = array();
979 $websites = self::instance()->query( self::instance()->get_sql_websites_for_current_user( $selectgroups, $search_site, $orderBy, $offset, $rowcount, $extraWhere, $for_manager, $extra_view, $is_staging ) );
980 while ( $websites && ( $website = self::fetch_object( $websites ) ) ) {
981 $obj_data = MainWP_Utility::map_site( $website, $data );
982
983 if ( $full_data ) {
984 $sum_upgrades = 0;
985 if ( '' != $obj_data->plugin_upgrades ) {
986 $plugin_upgrades = json_decode( $obj_data->plugin_upgrades, true );
987 if ( is_array( $plugin_upgrades ) ) {
988 $sum_upgrades += count( $plugin_upgrades );
989 }
990 }
991
992 if ( '' != $obj_data->theme_upgrades ) {
993 $theme_upgrades = json_decode( $obj_data->theme_upgrades, true );
994 if ( is_array( $theme_upgrades ) ) {
995 $sum_upgrades += count( $theme_upgrades );
996 }
997 }
998
999 if ( '' != $obj_data->wp_upgrades ) {
1000 $wp_upgrades = json_decode( $obj_data->wp_upgrades, true );
1001 if ( is_array( $wp_upgrades ) ) {
1002 $sum_upgrades += count( $wp_upgrades );
1003 }
1004 }
1005 $obj_data->sum_of_upgrades = $sum_upgrades;
1006 }
1007
1008 if ( 'array' == $format ) {
1009 $dbwebsites[] = $obj_data;
1010 } else {
1011 $dbwebsites[ $website->id ] = $obj_data;
1012 }
1013 }
1014 self::free_result( $websites );
1015 return $dbwebsites;
1016 }
1017
1018 /**
1019 * Get the child sites the current user has searched for.
1020 *
1021 * @param array $params Query parameters.
1022 *
1023 * @return boolean|null $qry Database query results or null on failure.
1024 *
1025 * @uses \MainWP\Dashboard\MainWP_System::is_multi_user()
1026 */
1027 public function get_sql_search_websites_for_current_user( $params ) {
1028
1029 if ( ! is_array( $params ) ) {
1030 $params = array();
1031 }
1032
1033 $selectgroups = isset( $params['selectgroups'] ) && $params['selectgroups'] ? true : false;
1034 $search_site = isset( $params['search'] ) ? $this->escape( trim( $params['search'] ) ) : null;
1035 $orderBy = isset( $params['orderby'] ) ? $params['orderby'] : 'wp.url';
1036 $offset = isset( $params['offset'] ) ? intval( $params['offset'] ) : false;
1037 $rowcount = isset( $params['rowcount'] ) ? intval( $params['rowcount'] ) : false;
1038 $extraWhere = isset( $params['extra_where'] ) ? $params['extra_where'] : null;
1039 $for_manager = isset( $params['for_manager'] ) && $params['for_manager'] ? true : false;
1040 $extra_view = isset( $params['extra_view'] ) ? $params['extra_view'] : array( 'favi_icon' );
1041 $is_staging = isset( $params['is_staging'] ) && 'yes' == $params['is_staging'] ? 'yes' : 'no';
1042 $is_count = isset( $params['count_only'] ) && $params['count_only'] ? true : false;
1043 $group_ids = isset( $params['group_id'] ) && ! empty( $params['group_id'] ) ? $params['group_id'] : array();
1044 $client_ids = isset( $params['client_id'] ) && ! empty( $params['client_id'] ) ? $params['client_id'] : array();
1045 $is_not = isset( $params['isnot'] ) && ! empty( $params['isnot'] ) ? true : false;
1046
1047 if ( ! is_array( $group_ids ) ) {
1048 $group_ids = array();
1049 }
1050
1051 // valid group ids.
1052 $group_ids = array_filter(
1053 $group_ids,
1054 function( $e ) {
1055 if ( 'nogroups' == $e ) {
1056 return true;
1057 }
1058 $e = intval( $e );
1059 return ( 0 < $e ) ? true : false;
1060 }
1061 );
1062
1063 if ( ! is_array( $client_ids ) ) {
1064 $client_ids = array();
1065 }
1066
1067 // valid group ids.
1068 $client_ids = array_filter(
1069 $client_ids,
1070 function( $e ) {
1071 if ( 'noclients' == $e ) {
1072 return true;
1073 }
1074 return is_numeric( $e ) && ! empty( $e ) ? true : false; // to valid client ids.
1075 }
1076 );
1077
1078 if ( $selectgroups ) {
1079 $staging_group = get_option( 'mainwp_stagingsites_group_id' );
1080 if ( $staging_group ) {
1081 if ( in_array( $staging_group, $group_ids ) ) {
1082 if ( 0 == count( $group_ids ) ) {
1083 $is_staging = 'yes';
1084 } else {
1085 $is_staging = 'nocheckstaging';
1086 }
1087 }
1088 }
1089 }
1090
1091 $where = '';
1092 if ( MainWP_System::instance()->is_multi_user() ) {
1093
1094 /**
1095 * Current user global.
1096 *
1097 * @global string
1098 */
1099 global $current_user;
1100
1101 $where .= ' AND wp.userid = ' . $current_user->ID . ' ';
1102 }
1103
1104 // for searching.
1105 if ( null !== $search_site && '' !== $search_site ) {
1106 $where .= ' AND (wp.name LIKE "%' . $search_site . '%" OR wp.url LIKE "%' . $search_site . '%") ';
1107 }
1108
1109 if ( null !== $extraWhere ) {
1110 $where .= ' AND ' . $extraWhere;
1111 }
1112
1113 if ( ! $for_manager ) {
1114 $where .= $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
1115 }
1116
1117 if ( $is_count ) {
1118 $orderBy = '';
1119 } elseif ( 'wp.url' === $orderBy ) {
1120 $orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '')";
1121 }
1122
1123 if ( ! empty( $orderBy ) ) {
1124 $orderBy = ' ORDER BY ' . $orderBy;
1125 }
1126
1127 $join_group = '';
1128 $where_group = '';
1129
1130 if ( in_array( 'nogroups', $group_ids ) ) {
1131 $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1132 $group_ids = array_filter(
1133 $group_ids,
1134 function( $e ) {
1135 return 'nogroups' != $e;
1136 }
1137 );
1138 if ( 0 < count( $group_ids ) ) {
1139 $groups = implode( ',', $group_ids );
1140 if ( $is_not ) {
1141 $where_group = ' AND wpgroup.groupid IS NOT NULL AND wpgroup.groupid NOT IN (' . $groups . ') ';
1142 // to fix.
1143 $sub_select_is_not = ' SELECT wp.id FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid WHERE wpgroup.groupid IN (' . $groups . ') ';
1144 $where_group .= ' AND wp.id NOT IN ( ' . $sub_select_is_not . ' ) ';
1145 } else {
1146 $where_group = ' AND ( wpgroup.groupid IS NULL OR wpgroup.groupid IN (' . $groups . ') ) ';
1147 }
1148 } else {
1149 if ( $is_not ) {
1150 $where_group = ' AND wpgroup.groupid IS NOT NULL ';
1151 } else {
1152 $where_group = ' AND wpgroup.groupid IS NULL ';
1153 }
1154 }
1155 } elseif ( $group_ids && 0 < count( $group_ids ) ) {
1156 $groups = implode( ',', $group_ids );
1157 if ( $is_not ) {
1158 $join_group = ' LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1159 $where_group = ' AND ( wpgroup.groupid NOT IN (' . $groups . ') OR wpgroup.groupid IS NULL ) ';
1160 // to fix.
1161 $sub_select_is_not = ' SELECT wp.id FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid WHERE wpgroup.groupid IN (' . $groups . ') ';
1162 $where_group .= ' AND wp.id NOT IN ( ' . $sub_select_is_not . ' ) ';
1163 } else {
1164 $join_group = ' JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid ';
1165 $where_group = ' AND wpgroup.groupid IN (' . $groups . ') ';
1166 }
1167 }
1168
1169 $join_client = '';
1170 $where_client = '';
1171
1172 if ( in_array( 'noclients', $client_ids ) ) {
1173 $join_client = ' LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id ';
1174 $client_ids = array_filter(
1175 $client_ids,
1176 function( $e ) {
1177 return 'noclients' != $e;
1178 }
1179 );
1180 if ( 0 < count( $client_ids ) ) {
1181 $clients = implode( ',', $client_ids );
1182 if ( $is_not ) {
1183 $where_client = ' AND wpclient.client_id IS NOT NULL AND wp.client_id NOT IN (' . $clients . ') ';
1184 } else {
1185 $where_client = ' AND wpclient.client_id IN (' . $clients . ') ';
1186 }
1187 } else {
1188 if ( $is_not ) {
1189 $where_client = ' AND wpclient.client_id IS NOT NULL ';
1190 } else {
1191 $where_client = ' AND wpclient.client_id IS NULL ';
1192 }
1193 }
1194 } elseif ( $client_ids && 0 < count( $client_ids ) ) {
1195 $clients = implode( ',', $client_ids );
1196 if ( $is_not ) {
1197 $join_client = ' LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id ';
1198 $where_client = ' AND ( wpclient.client_id NOT IN (' . $clients . ') OR wpclient.client_id IS NULL ) ';
1199 } else {
1200 $join_client = ' JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id ';
1201 $where_client = ' AND wpclient.client_id IN (' . $clients . ') ';
1202 }
1203 }
1204
1205 if ( '' === $join_client ) {
1206 $join_client = ' LEFT JOIN ' . $this->table_name( 'wp_clients' ) . ' wpclient ON wp.client_id = wpclient.client_id ';
1207 }
1208 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax.
1209 if ( $selectgroups ) {
1210 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ",") as wpgroups, GROUP_CONCAT(gr.id ORDER BY gr.name SEPARATOR ",") as wpgroupids, wpclient.name as client_name
1211 FROM ' . $this->table_name( 'wp' ) . ' wp ' .
1212 $join_group . ' ' .
1213 $join_client . '
1214 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1215 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
1216
1217 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1218 JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1219 WHERE 1 ' . $where . $where_group . $where_client . '
1220 GROUP BY wp.id, wp_sync.sync_id ' .
1221 $orderBy;
1222 } else {
1223 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, wpclient.name as client_name
1224 FROM ' . $this->table_name( 'wp' ) . ' wp ' .
1225 $join_group . ' ' .
1226 $join_client . '
1227 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1228 JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1229 WHERE 1 ' . $where . $where_group . $where_client . '
1230 GROUP BY wp.id, wp_sync.sync_id ' .
1231 $orderBy;
1232 }
1233
1234 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
1235 $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
1236 } elseif ( false !== $rowcount ) {
1237 $qry .= ' LIMIT ' . $rowcount;
1238 }
1239 return $qry;
1240 }
1241
1242 /**
1243 * Get child sites where allowed access via SQL.
1244 *
1245 * @param string $site_table_alias Child site table alias.
1246 * @param string $is_staging yes|no Is child site a staging site.
1247 *
1248 * @return boolean|null $_where Database query results or null on failure.
1249 */
1250 public function get_sql_where_allow_access_sites( $site_table_alias = '', $is_staging = 'no' ) {
1251
1252 if ( empty( $site_table_alias ) ) {
1253 $site_table_alias = $this->table_name( 'wp' );
1254 }
1255
1256 // check to filter the staging sites.
1257 $where_staging = ' AND ' . $site_table_alias . '.is_staging = 0 ';
1258 if ( 'no' === $is_staging ) {
1259 $where_staging = ' AND ' . $site_table_alias . '.is_staging = 0 ';
1260 } elseif ( 'yes' === $is_staging ) {
1261 $where_staging = ' AND ' . $site_table_alias . '.is_staging = 1 ';
1262 } elseif ( 'nocheckstaging' === $is_staging ) {
1263 $where_staging = '';
1264 }
1265 // end staging filter.
1266
1267 $_where = $where_staging;
1268 // To fix bug run from cron job.
1269 if ( defined( 'DOING_CRON' ) && DOING_CRON ) {
1270 return $_where;
1271 }
1272
1273 // To fix bug run from wp cli.
1274 if ( defined( 'WP_CLI' ) && WP_CLI ) {
1275 return $_where;
1276 }
1277
1278 // Run from Rest Api.
1279 if ( defined( 'MAINWP_REST_API' ) && MAINWP_REST_API ) {
1280 return $_where;
1281 }
1282
1283 /**
1284 * Filter: mainwp_currentuserallowedaccesssites
1285 *
1286 * Filters allowed sites for the current user.
1287 *
1288 * @since Unknown
1289 */
1290 $allowed_sites = apply_filters( 'mainwp_currentuserallowedaccesssites', 'all' );
1291
1292 if ( 'all' === $allowed_sites ) {
1293 return $_where;
1294 }
1295
1296 if ( is_array( $allowed_sites ) && 0 < count( $allowed_sites ) ) {
1297 $_where .= ' AND ' . $site_table_alias . '.id IN (' . implode( ',', $allowed_sites ) . ') ';
1298 } else {
1299 $_where .= ' AND 0 ';
1300 }
1301
1302 return $_where;
1303 }
1304
1305 /**
1306 * Get groupd where allowed access via SQL.
1307 *
1308 * @param string $group_table_alias Child site table alias.
1309 * @param string $with_staging yes|no Is child site a staging site.
1310 *
1311 * @return boolean|null $_where Database query results or null on failer.
1312 */
1313 public function get_sql_where_allow_groups( $group_table_alias = '', $with_staging = 'no' ) {
1314
1315 if ( empty( $group_table_alias ) ) {
1316 $group_table_alias = $this->table_name( 'group' );
1317 }
1318
1319 // check to filter the staging group.
1320 $where_staging_group = '';
1321 $staging_group = get_option( 'mainwp_stagingsites_group_id' );
1322 if ( $staging_group ) {
1323 $where_staging_group = ' AND ' . $group_table_alias . '.id <> ' . $staging_group . ' ';
1324 if ( 'yes' === $with_staging ) {
1325 $where_staging_group = '';
1326 }
1327 }
1328
1329 // end staging filter.
1330 $_where = $where_staging_group;
1331
1332 // To fix bug run from cron job.
1333 if ( defined( 'DOING_CRON' ) && DOING_CRON ) {
1334 return $_where;
1335 }
1336
1337 // Run from wp cli.
1338 if ( defined( 'WP_CLI' ) && WP_CLI ) {
1339 return $_where;
1340 }
1341
1342 // Run from Rest Api.
1343 if ( defined( 'MAINWP_REST_API' ) && MAINWP_REST_API ) {
1344 return $_where;
1345 }
1346
1347 /**
1348 * Filter: mainwp_currentuserallowedaccessgroups
1349 *
1350 * Filters allowed groups for the current user.
1351 *
1352 * @since Unknown
1353 */
1354 $allowed_groups = apply_filters( 'mainwp_currentuserallowedaccessgroups', 'all' );
1355
1356 if ( 'all' === $allowed_groups ) {
1357 return $_where;
1358 }
1359
1360 if ( is_array( $allowed_groups ) && 0 < count( $allowed_groups ) ) {
1361 return ' AND ' . $group_table_alias . '.id IN (' . implode( ',', $allowed_groups ) . ') ' . $_where;
1362 } else {
1363 return ' AND 0 ';
1364 }
1365 }
1366
1367 /**
1368 * Get child site by id.
1369 *
1370 * @param int $id Child site ID.
1371 * @param array $selectGroups Select groups.
1372 * @param array $extra_view Get extra option fields.
1373 *
1374 * @return object|null Database query results or null on failure.
1375 */
1376 public function get_website_by_id( $id, $selectGroups = false, $extra_view = array() ) {
1377 return $this->get_row_result( $this->get_sql_website_by_id( $id, $selectGroups, $extra_view ) );
1378 }
1379
1380 /**
1381 * Get child site by id via SQL.
1382 *
1383 * @param int $id Child site ID.
1384 * @param bool $selectGroups Selected groups.
1385 * @param mixed $extra_view Extra view value.
1386 *
1387 * @return object|null Database query result or null on failure.
1388 *
1389 * @uses \MainWP\Dashboard\MainWP_Utility::ctype_digit()
1390 */
1391 public function get_sql_website_by_id( $id, $selectGroups = false, $extra_view = array() ) {
1392
1393 if ( ! is_array( $extra_view ) || empty( $extra_view ) ) {
1394 $extra_view = array( 'favi_icon', 'site_info' );
1395 }
1396
1397 if ( MainWP_Utility::ctype_digit( $id ) ) {
1398 $where = $this->get_sql_where_allow_access_sites( 'wp', 'nocheckstaging' );
1399 if ( $selectGroups ) {
1400 return 'SELECT wp.*,wp_sync.*,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ",") as wpgroups, GROUP_CONCAT(gr.id ORDER BY gr.name SEPARATOR ",") as wpgroupids
1401 FROM ' . $this->table_name( 'wp' ) . ' wp
1402 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1403 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
1404 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1405 JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1406 WHERE wp.id = ' . $id . $where . '
1407 GROUP BY wp.id, wp_sync.sync_id';
1408 }
1409
1410 return 'SELECT wp.*,wp_sync.*,wp_optionview.*
1411 FROM ' . $this->table_name( 'wp' ) . ' wp
1412 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1413 JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1414 WHERE id = ' . $id . $where;
1415 }
1416
1417 return null;
1418 }
1419
1420 /**
1421 * Method get_websites_by_ids()
1422 *
1423 * Get child sites by child site IDs.
1424 *
1425 * @param array $ids Child site IDs.
1426 * @param int $userId User ID.
1427 *
1428 * @return object|null Database query result or null on failure.
1429 *
1430 * @uses \MainWP\Dashboard\MainWP_System::is_multi_user()
1431 */
1432 public function get_websites_by_ids( $ids, $userId = null ) {
1433 if ( ( null == $userId ) && MainWP_System::instance()->is_multi_user() ) {
1434
1435 /**
1436 * Current user global.
1437 *
1438 * @global string
1439 */
1440 global $current_user;
1441
1442 $userId = $current_user->ID;
1443 }
1444 $where = $this->get_sql_where_allow_access_sites();
1445
1446 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' WHERE id IN (' . implode( ',', $ids ) . ')' . ( null != $userId ? ' AND userid = ' . $userId : '' ) . $where, OBJECT );
1447 }
1448
1449 /**
1450 * Get child sites by groups IDs.
1451 *
1452 * @param array $ids Groups IDs.
1453 * @param int $userId User ID.
1454 *
1455 * @return object|null Database query result or null on failure.
1456 *
1457 * @uses \MainWP\Dashboard\MainWP_System::is_multi_user()
1458 */
1459 public function get_websites_by_group_ids( $ids, $userId = null ) {
1460 if ( empty( $ids ) ) {
1461 return array();
1462 }
1463 if ( ( null == $userId ) && MainWP_System::instance()->is_multi_user() ) {
1464
1465 /**
1466 * Current user global.
1467 *
1468 * @global string
1469 */
1470 global $current_user;
1471
1472 $userId = $current_user->ID;
1473 }
1474
1475 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 );
1476 }
1477
1478 /**
1479 * Get child sites by group ID.
1480 *
1481 * @param int $id Group ID.
1482 *
1483 * @return object|null Database query result or null on failure.
1484 */
1485 public function get_websites_by_group_id( $id ) {
1486 return $this->get_results_result( $this->get_sql_websites_by_group_id( $id ) );
1487 }
1488
1489 /**
1490 * Get child sites by group id via SQL.
1491 *
1492 * @param int $id Group ID.
1493 * @param bool $selectgroups Selected groups. Default: false.
1494 * @param string $orderBy Order list by. Default: URL.
1495 * @param bool $offset Query offset. Default: false.
1496 * @param bool $rowcount Row count. Default: falese.
1497 * @param null $where SQL WHERE value.
1498 * @param null $search_site Site search field value. Default: null.
1499 *
1500 * @return object|null Return database query or null on failure.
1501 *
1502 * @uses \MainWP\Dashboard\MainWP_Utility::ctype_digit()
1503 */
1504 public function get_sql_websites_by_group_id(
1505 $id,
1506 $selectgroups = false,
1507 $orderBy = 'wp.url',
1508 $offset = false,
1509 $rowcount = false,
1510 $where = null,
1511 $search_site = null ) {
1512
1513 $is_staging = 'no';
1514 if ( $selectgroups ) {
1515 $staging_group = get_option( 'mainwp_stagingsites_group_id' );
1516 if ( $staging_group ) {
1517 if ( $id == $staging_group ) {
1518 $is_staging = 'yes';
1519 }
1520 }
1521 }
1522
1523 $where_search = '';
1524 if ( ! empty( $search_site ) ) {
1525 $search_site = trim( $search_site );
1526 $where_search .= ' AND (wp.name LIKE "%' . $this->escape( $search_site ) . '%" OR wp.url LIKE "%' . $this->escape( $search_site ) . '%") ';
1527 }
1528
1529 if ( MainWP_Utility::ctype_digit( $id ) ) {
1530 $where_allowed = $this->get_sql_where_allow_access_sites( 'wp', $is_staging );
1531 if ( $selectgroups ) {
1532 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ",") as wpgroups, GROUP_CONCAT(gr.id ORDER BY gr.name SEPARATOR ",") as wpgroupids
1533 FROM ' . $this->table_name( 'wp' ) . ' wp
1534 JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
1535 LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1536 LEFT JOIN ' . $this->table_name( 'group' ) . ' gr ON wpgr.groupid = gr.id
1537 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1538 JOIN ' . $this->get_option_view( array( 'site_info' ), true ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1539 WHERE wpgroup.groupid = ' . $id . ' ' .
1540 ( null == $where ? '' : ' AND ' . $where ) . $where_allowed . $where_search . '
1541 GROUP BY wp.id, wp_sync.sync_id
1542 ORDER BY ' . $orderBy;
1543 } else {
1544 $qry = 'SELECT wp.*,wp_optionview.*, wp_sync.* FROM ' . $this->table_name( 'wp' ) . ' wp
1545 JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
1546 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1547 JOIN ' . $this->get_option_view( array( 'site_info' ), false ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1548 WHERE wpgroup.groupid = ' . $id . ' ' . $where_allowed . $where_search .
1549 ( null == $where ? '' : ' AND ' . $where ) . ' ORDER BY ' . $orderBy;
1550 }
1551 if ( ( false !== $offset ) && ( false !== $rowcount ) ) {
1552 $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
1553 } elseif ( false !== $rowcount ) {
1554 $qry .= ' LIMIT ' . $rowcount;
1555 }
1556
1557 return $qry;
1558 }
1559
1560 return null;
1561 }
1562
1563 /**
1564 * Get child sites by group name.
1565 *
1566 * @param int $userid Current user ID.
1567 * @param string $groupname Group name.
1568 *
1569 * @return object|null Database query result or null on failure.
1570 */
1571 public function get_websites_by_group_name( $userid, $groupname ) {
1572 return $this->get_results_result( $this->get_sql_websites_by_group_name( $groupname, $userid ) );
1573 }
1574
1575 /**
1576 * Get child sites by group name.
1577 *
1578 * @param string $groupname Group name.
1579 * @param int $userid Current user ID.
1580 *
1581 * @return object|null Database query result or null on failure.
1582 *
1583 * @uses \MainWP\Dashboard\MainWP_System::is_multi_user()
1584 */
1585 public function get_sql_websites_by_group_name( $groupname, $userid = null ) {
1586 if ( ( null == $userid ) && MainWP_System::instance()->is_multi_user() ) {
1587
1588 /**
1589 * Current user global.
1590 *
1591 * @global string
1592 */
1593 global $current_user;
1594
1595 $userid = $current_user->ID;
1596 }
1597
1598 $sql = 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
1599 INNER JOIN ' . $this->table_name( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
1600 JOIN ' . $this->table_name( 'group' ) . ' g ON wpgroup.groupid = g.id
1601 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1602 JOIN ' . $this->get_option_view() . ' wp_optionview ON wp.id = wp_optionview.wpid
1603 WHERE g.name="' . $this->escape( $groupname ) . '"';
1604 if ( null != $userid ) {
1605 $sql .= ' AND g.userid = "' . $userid . '"';
1606 }
1607
1608 return $sql;
1609 }
1610
1611 /**
1612 * Get child site IP address.
1613 *
1614 * @param int $wpid Child site ID.
1615 *
1616 * @return string|null Child site IP address or null on failure.
1617 */
1618 public function get_wp_ip( $wpid ) {
1619 return $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT ip FROM ' . $this->table_name( 'request_log' ) . ' WHERE wpid = %d', $wpid ) );
1620 }
1621
1622 /**
1623 * Add website to the MainWP Dashboard.
1624 *
1625 * @param int $userid Current user ID.
1626 * @param string $name Child site name.
1627 * @param string $url Child site URL.
1628 * @param string $admin Child site administrator username.
1629 * @param string $pubkey OpenSSL public key.
1630 * @param string $privkey OpenSSL private key.
1631 * @param mixed $nossl SSL suppoted connection.
1632 * @param mixed $nosslkey SSL not supported connection key.
1633 * @param array $groupids Group IDs.
1634 * @param array $groupnames Group names.
1635 * @param int $verifyCertificate Whether or not to verify SSL Certificate.
1636 * @param string $uniqueId Unique security ID.
1637 * @param string $http_user HTTP Basic Authentication username.
1638 * @param string $http_pass HTTP Basic Authentication password.
1639 * @param int $sslVersion SSL Version.
1640 * @param int $wpe Is it WP Engine hosted site.
1641 * @param int $isStaging Whether or not child site is staging site.
1642 *
1643 * @return int|false Child site ID or false on failure.
1644 *
1645 * @uses \MainWP\Dashboard\MainWP_Utility::ctype_digit()
1646 */
1647 public function add_website(
1648 $userid,
1649 $name,
1650 $url,
1651 $admin,
1652 $pubkey,
1653 $privkey,
1654 $nossl,
1655 $nosslkey,
1656 $groupids,
1657 $groupnames,
1658 $verifyCertificate = 1,
1659 $uniqueId = '',
1660 $http_user = null,
1661 $http_pass = null,
1662 $sslVersion = 0,
1663 $wpe = 0,
1664 $isStaging = 0 ) {
1665
1666 if ( MainWP_Utility::ctype_digit( $userid ) && ( 0 == $nossl || 1 == $nossl ) ) {
1667 if ( '/' != substr( $url, - 1 ) ) {
1668 $url .= '/';
1669 }
1670 $values = array(
1671 'userid' => $userid,
1672 'adminname' => $this->escape( $admin ),
1673 'name' => $this->escape( wp_strip_all_tags( $name ) ),
1674 'url' => $this->escape( $url ),
1675 'pubkey' => $this->escape( $pubkey ),
1676 'privkey' => $this->escape( $privkey ),
1677 'nossl' => $nossl,
1678 'nosslkey' => ( null == $nosslkey ? '' : $this->escape( $nosslkey ) ),
1679 'siteurl' => '',
1680 'ga_id' => '',
1681 'gas_id' => 0,
1682 'offline_checks_last' => 0,
1683 'offline_check_result' => 0,
1684 'note' => '',
1685 'statsUpdate' => 0,
1686 'directories' => '',
1687 'plugin_upgrades' => '',
1688 'theme_upgrades' => '',
1689 'translation_upgrades' => '',
1690 'securityIssues' => '',
1691 'themes' => '',
1692 'ignored_themes' => '',
1693 'plugins' => '',
1694 'ignored_plugins' => '',
1695 'users' => '',
1696 'categories' => '',
1697 'pluginDir' => '',
1698 'automatic_update' => 0,
1699 'backup_before_upgrade' => 2,
1700 'verify_certificate' => intval( $verifyCertificate ),
1701 'ssl_version' => $sslVersion,
1702 'uniqueId' => $uniqueId,
1703 'mainwpdir' => 0,
1704 'http_user' => $http_user,
1705 'http_pass' => $http_pass,
1706 'wpe' => $wpe,
1707 'is_staging' => $isStaging,
1708 );
1709
1710 $syncValues = array(
1711 'dtsSync' => 0,
1712 'dtsSyncStart' => 0,
1713 'dtsAutomaticSync' => 0,
1714 'dtsAutomaticSyncStart' => 0,
1715 'totalsize' => 0,
1716 'extauth' => '',
1717 'sync_errors' => '',
1718 );
1719 if ( $this->wpdb->insert( $this->table_name( 'wp' ), $values ) ) {
1720 $websiteid = $this->wpdb->insert_id;
1721 $syncValues['wpid'] = $websiteid;
1722 $this->wpdb->insert( $this->table_name( 'wp_sync' ), $syncValues );
1723 $this->wpdb->insert(
1724 $this->table_name( 'wp_settings_backup' ),
1725 array(
1726 'wpid' => $websiteid,
1727 'archiveFormat' => 'global',
1728 )
1729 );
1730
1731 foreach ( $groupnames as $groupname ) {
1732 if ( $this->wpdb->insert(
1733 $this->table_name( 'group' ),
1734 array(
1735 'userid' => $userid,
1736 'name' => $this->escape( htmlspecialchars( $groupname ) ),
1737 )
1738 )
1739 ) {
1740 $groupids[] = $this->wpdb->insert_id;
1741 }
1742 }
1743 // add groupids.
1744 foreach ( $groupids as $groupid ) {
1745 $this->wpdb->insert(
1746 $this->table_name( 'wp_group' ),
1747 array(
1748 'wpid' => $websiteid,
1749 'groupid' => $groupid,
1750 )
1751 );
1752 }
1753
1754 return $websiteid;
1755 }
1756 }
1757
1758 return false;
1759 }
1760
1761 /**
1762 * Remove child site from the MainWP Dashboard.
1763 *
1764 * @param int $websiteid Child site ID.
1765 *
1766 * @return int|boolean Return child site ID that was removed or false on failure.
1767 *
1768 * @uses \MainWP\Dashboard\MainWP_Utility::ctype_digit()
1769 */
1770 public function remove_website( $websiteid ) {
1771 if ( MainWP_Utility::ctype_digit( $websiteid ) ) {
1772 $nr = $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp' ) . ' WHERE id=%d', $websiteid ) );
1773 $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE wpid=%d', $websiteid ) );
1774 $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_sync' ) . ' WHERE wpid=%d', $websiteid ) );
1775 $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_options' ) . ' WHERE wpid=%d', $websiteid ) );
1776 $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_status' ) . ' WHERE wpid=%d', $websiteid ) );
1777
1778 return $nr;
1779 }
1780
1781 return false;
1782 }
1783
1784 /**
1785 * Update child site db values.
1786 *
1787 * @param int $websiteid Child site ID.
1788 * @param array $fields Database fields to update.
1789 *
1790 * @return int|boolean The number of rows updated, or false on error.
1791 */
1792 public function update_website_values( $websiteid, $fields ) {
1793 if ( 0 < count( $fields ) ) {
1794 return $this->wpdb->update( $this->table_name( 'wp' ), $fields, array( 'id' => $websiteid ) );
1795 }
1796
1797 return false;
1798 }
1799
1800 /**
1801 * Update child site sync values.
1802 *
1803 * @param int $websiteid Child site ID.
1804 * @param array $fields Database fields to update.
1805 *
1806 * @return int|boolean The number of rows updated, or false on error.
1807 */
1808 public function update_website_sync_values( $websiteid, $fields ) {
1809 if ( 0 < count( $fields ) ) {
1810 return $this->wpdb->update( $this->table_name( 'wp_sync' ), $fields, array( 'wpid' => $websiteid ) );
1811 }
1812
1813 return false;
1814 }
1815
1816 /**
1817 * Update child site.
1818 *
1819 * @param int $websiteid Website ID.
1820 * @param string $url Child site URL.
1821 * @param int $userid Current user ID.
1822 * @param string $name Child site name.
1823 * @param string $siteadmin Child site administrator username.
1824 * @param array $groupids Group IDs.
1825 * @param array $groupnames Group Names.
1826 * @param string $pluginDir Plugin directory.
1827 * @param mixed $maximumFileDescriptorsOverride Overwrite the Maximum File Descriptors option.
1828 * @param mixed $maximumFileDescriptorsAuto Auto set the Maximum File Descriptors option.
1829 * @param mixed $maximumFileDescriptors Set the Maximum File Descriptors option.
1830 * @param int $verifyCertificate Whether or not to verify SSL Certificate.
1831 * @param mixed $archiveFormat Backup archive formate.
1832 * @param string $uniqueId Unique security ID.
1833 * @param string $http_user HTTP Basic Authentication username.
1834 * @param string $http_pass HTTP Basic Authentication password.
1835 * @param int $sslVersion SSL Version.
1836 * @param int $disableChecking Wether or not disable sites status checking.
1837 * @param int $checkInterval Status checking interval.
1838 * @param bool $disableHealthChecking Disable Site health threshold.
1839 * @param int $healthThreshold Site health threshold.
1840 * @param int $wpe Is it WP Engine hosted site.
1841 *
1842 * @return boolean ture on success or false on failure.
1843 *
1844 * @uses \MainWP\Dashboard\MainWP_System_Utility::can_edit_website()
1845 * @uses \MainWP\Dashboard\MainWP_Utility::ctype_digit()
1846 */
1847 public function update_website(
1848 $websiteid,
1849 $url,
1850 $userid,
1851 $name,
1852 $siteadmin,
1853 $groupids,
1854 $groupnames,
1855 $pluginDir,
1856 $maximumFileDescriptorsOverride,
1857 $maximumFileDescriptorsAuto,
1858 $maximumFileDescriptors,
1859 $verifyCertificate = 1,
1860 $archiveFormat = 'global',
1861 $uniqueId = '',
1862 $http_user = null,
1863 $http_pass = null,
1864 $sslVersion = 0,
1865 $disableChecking = 1,
1866 $checkInterval = 1440,
1867 $disableHealthChecking = 1,
1868 $healthThreshold = 80,
1869 $wpe = 0 ) {
1870
1871 if ( MainWP_Utility::ctype_digit( $websiteid ) && MainWP_Utility::ctype_digit( $userid ) ) {
1872 $website = self::instance()->get_website_by_id( $websiteid );
1873 if ( MainWP_System_Utility::can_edit_website( $website ) ) {
1874 // update admin.
1875 $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 ) . '", 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 ) );
1876 $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp_settings_backup' ) . ' SET archiveFormat = "' . $this->escape( $archiveFormat ) . '" WHERE wpid=%d', $websiteid ) );
1877
1878 if ( get_option( 'mainwp_enableLegacyBackupFeature' ) ) {
1879 $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET maximumFileDescriptorsOverride = ' . ( $maximumFileDescriptorsOverride ? 1 : 0 ) . ',maximumFileDescriptorsAuto= ' . ( $maximumFileDescriptorsAuto ? 1 : 0 ) . ',maximumFileDescriptors = ' . $maximumFileDescriptors . ' WHERE id=%d', $websiteid ) );
1880 }
1881
1882 // remove groups.
1883 $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE wpid=%d', $websiteid ) );
1884 // Remove GA stats.
1885 $showErrors = $this->wpdb->hide_errors();
1886
1887 /**
1888 * Action: mainwp_ga_delete_site
1889 *
1890 * Fires upon site removal process in order to delete Google Analytics data.
1891 *
1892 * @param int $websiteid Child site ID.
1893 *
1894 * @since Unknown
1895 */
1896 do_action( 'mainwp_ga_delete_site', $websiteid );
1897
1898 if ( $showErrors ) {
1899 $this->wpdb->show_errors();
1900 }
1901 // add groups with groupnames.
1902 foreach ( $groupnames as $groupname ) {
1903 if ( $this->wpdb->insert(
1904 $this->table_name( 'group' ),
1905 array(
1906 'userid' => $userid,
1907 'name' => $this->escape( $groupname ),
1908 )
1909 )
1910 ) {
1911 $groupids[] = $this->wpdb->insert_id;
1912 }
1913 }
1914 // add groupids.
1915 foreach ( $groupids as $groupid ) {
1916 $this->wpdb->insert(
1917 $this->table_name( 'wp_group' ),
1918 array(
1919 'wpid' => $websiteid,
1920 'groupid' => $groupid,
1921 )
1922 );
1923 }
1924
1925 return true;
1926 }
1927 }
1928
1929 return false;
1930 }
1931
1932 /**
1933 * Get websites check updates count.
1934 *
1935 * @param int $lasttime_start Lasttime start automatic update.
1936 *
1937 * @return int Child sites update count.
1938 */
1939 public function get_websites_check_updates_count( $lasttime_start ) {
1940 $where = $this->get_sql_where_allow_access_sites( 'wp' );
1941
1942 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 );
1943 }
1944
1945 /**
1946 * Get child site count where date & time Session sync is smaller then start.
1947 *
1948 * @param int $lasttime_start Last time start automatic.
1949 *
1950 * @return int Returned child site count.
1951 */
1952 public function get_websites_count_where_dts_automatic_sync_smaller_then_start( $lasttime_start ) {
1953 $where = $this->get_sql_where_allow_access_sites( 'wp' );
1954
1955 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 AND wp_sync.dtsAutomaticSyncStart > ' . intval( $lasttime_start ) . ') OR (wp_sync.dtsAutomaticSyncStart = 0)) ' . $where );
1956 }
1957
1958 /**
1959 * Get child site last automatic sync date & time.
1960 *
1961 * @return string Date and time of last automatic sync.
1962 */
1963 public function get_websites_last_automatic_sync() {
1964 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' );
1965 }
1966
1967 /**
1968 * Get child sites check updates.
1969 *
1970 * @param int $limit Query limit.
1971 * @param int $lasttime_start Lasttime start automatic update.
1972 *
1973 * @return object|null Database query result or null on failure.
1974 */
1975 public function get_websites_check_updates( $limit, $lasttime_start ) {
1976 $where = $this->get_sql_where_allow_access_sites( 'wp' );
1977
1978 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 . ' ORDER BY wp_sync.dtsAutomaticSyncStart ASC LIMIT ' . $limit, OBJECT );
1979 }
1980
1981 /**
1982 * Get website update stats via SQL.
1983 *
1984 * @return object|null Database query result of null on failure.
1985 */
1986 public function get_websites_stats_update_sql() {
1987 $where = $this->get_sql_where_allow_access_sites( 'wp' );
1988 return 'SELECT wp.*,wp_sync.sync_errors FROM ' . $this->table_name( 'wp' ) . ' wp JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE (wp.statsUpdate = 0 OR ' . time() . ' - wp.statsUpdate >= ' . ( 60 * 60 * 24 ) . ')' . $where . ' ORDER BY wp.statsUpdate ASC';
1989 }
1990
1991 /**
1992 * Update child site statistics.
1993 *
1994 * Update whether or not a child site has been updated.
1995 *
1996 * @param mixed $websiteid Child site ID.
1997 * @param mixed $statsUpdated Child site Update status.
1998 *
1999 * @return (int|boolean) Number of rows effected in update or false on failure.
2000 */
2001 public function update_website_stats( $websiteid, $statsUpdated ) {
2002 return $this->wpdb->update(
2003 $this->table_name( 'wp' ),
2004 array( 'statsUpdate' => $statsUpdated ),
2005 array( 'id' => $websiteid )
2006 );
2007 }
2008
2009 /**
2010 * Get child site by url.
2011 *
2012 * @param string $url Child site URL.
2013 *
2014 * @return object|null Database query result or null on failure.
2015 */
2016 public function get_websites_by_url( $url ) {
2017 if ( '/' != substr( $url, - 1 ) ) {
2018 $url .= '/';
2019 }
2020 $results = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' WHERE url = %s ', $this->escape( $url ) ), OBJECT );
2021 if ( $results ) {
2022 return $results;
2023 }
2024
2025 if ( stristr( $url, '/www.' ) ) {
2026 // remove www if it's there!
2027 $url = str_replace( '/www.', '/', $url );
2028 } else {
2029 // add www if it's not there!
2030 $url = str_replace( 'https://', 'https://www.', $url );
2031 $url = str_replace( 'http://', 'http://www.', $url );
2032 }
2033
2034 $results = $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp' ) . ' WHERE url = %s ', $this->escape( $url ) ), OBJECT );
2035 if ( $results ) {
2036 return $results;
2037 }
2038
2039 $url = str_replace( array( 'https://www.', 'http://www.', 'https://', 'http://', 'www.' ), array( '', '', '', '', '' ), $url );
2040
2041 return $this->wpdb->get_results( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'wp' ) . " WHERE replace(replace(replace(replace(replace(url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', '') = %s ", $this->escape( $url ) ), OBJECT );
2042 }
2043
2044 /**
2045 * Get websites offline status.
2046 *
2047 * @return array Child site monitoring status.
2048 */
2049 public function get_websites_offline_status_to_send_notice() {
2050 $where = $this->get_sql_where_allow_access_sites( 'wp' );
2051 $extra_view = array( 'monitoring_notification_emails', 'settings_notification_emails' );
2052
2053 return $this->wpdb->get_results(
2054 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2055 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2056 JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2057 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.
2058 $where,
2059 OBJECT
2060 );
2061 }
2062
2063 /**
2064 * Method get_websites_to_notice_health_threshold()
2065 *
2066 * Get websites to notice site health.
2067 *
2068 * @param int $globalThreshold Global site health threshold.
2069 * @param int $count Limit count.
2070 */
2071 public function get_websites_to_notice_health_threshold( $globalThreshold, $count = 10 ) {
2072
2073 $where = $this->get_sql_where_allow_access_sites( 'wp' );
2074 $extra_view = array( 'monitoring_notification_emails', 'settings_notification_emails' );
2075
2076 if ( 80 >= $globalThreshold ) { // actual is 80.
2077 // should-be-improved site health.
2078 $where_global_threshold = '( wp.health_threshold = 0 AND wp_sync.health_value < 80 )';
2079 } else {
2080 // good site health.
2081 $where_global_threshold = '( wp.health_threshold = 0 AND wp_sync.health_value >= 80 )';
2082 }
2083
2084 $where_site_threshold = ' ( wp.health_threshold = 80 AND wp_sync.health_value < 80 ) '; // should-be-improved site health.
2085 $where_site_threshold .= ' OR ( wp.health_threshold = 100 AND wp_sync.health_value >= 80 ) '; // good site health.
2086
2087 return $this->wpdb->get_results(
2088 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2089 JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
2090 JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2091 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 ' .
2092 $where,
2093 OBJECT
2094 );
2095 }
2096
2097 /**
2098 * Get websites offline status.
2099 *
2100 * @return array Sites with offline status.
2101 */
2102 public function get_websites_offline_check_status() {
2103 $where = $this->get_sql_where_allow_access_sites( 'wp' );
2104 $extra_view = array( 'settings_notification_emails' );
2105
2106 return $this->wpdb->get_results(
2107 'SELECT wp.*,wp_optionview.* FROM ' . $this->table_name( 'wp' ) . ' wp
2108 JOIN ' . $this->get_option_view( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
2109 WHERE wp.disable_status_check <> 1 AND wp.offline_check_result = -1' . // offline checked status.
2110 $where,
2111 OBJECT
2112 );
2113 }
2114 }
2115