PluginProbe
MainWP Dashboard: Self-hosted WordPress Management for Agencies / 4.0.5
MainWP Dashboard: Self-hosted WordPress Management for Agencies v4.0.5
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.0.5, at class/class-mainwp-db.php

2,012 lines 71.7 KB
No matching file
Up and down to move Enter to open Esc to close
Raw Download Zip
1 <?php
2
3 class MainWP_DB {
4
5 //Config
6 private $mainwp_db_version = '8.14';
7 //Private
8 private $table_prefix;
9 //Singleton
10 /** @var $instance MainWP_DB */
11 private static $instance = null;
12
13 /** @var $wpdb wpdb */
14 private $wpdb;
15
16 /**
17 * @static
18 * @return MainWP_DB
19 */
20 static function Instance() {
21 if ( MainWP_DB::$instance == null ) {
22 MainWP_DB::$instance = new MainWP_DB();
23 }
24
25 MainWP_DB::$instance->test_connection();
26
27 return MainWP_DB::$instance;
28 }
29
30 //Constructor
31 function __construct() {
32 /** @var $this ->wpdb wpdb */
33 global $wpdb;
34
35 $this->wpdb = &$wpdb;
36 $this->table_prefix = $wpdb->prefix . 'mainwp_';
37 }
38
39 private function test_connection() {
40 if ( !self::ping( $this->wpdb->dbh ) ) {
41 MainWP_Logger::Instance()->info( __( 'Trying to reconnect WordPress database connection...', 'mainwp' ) );
42 $this->wpdb->db_connect();
43 }
44 }
45
46 private function tableName( $suffix, $tablePrefix = null ) {
47 return ( $tablePrefix == null ? $this->table_prefix : $tablePrefix ) . $suffix;
48 }
49
50 //Installs new DB
51 function install() {
52 //get_site_option is multisite aware!
53 $currentVersion = get_site_option( 'mainwp_db_version' );
54
55 if ( empty( $currentVersion ) ) {
56 //set_transient( '_mainwp_activation_redirect', 1, 30 );
57 update_option( 'mainwp_run_quick_setup', 'yes' );
58 MainWP_Utility::update_option( 'mainwp_enableLegacyBackupFeature', 0 );
59 } else if ( false === get_option( 'mainwp_enableLegacyBackupFeature' ) ) {
60 MainWP_Utility::update_option( 'mainwp_enableLegacyBackupFeature', 1 );
61 }
62
63 $rslt = MainWP_DB::Instance()->query( "SHOW TABLES LIKE '" . $this->tableName( 'wp' ) . "'" );
64 if ( @MainWP_DB::num_rows( $rslt ) == 0 ) {
65 $currentVersion = false;
66 }
67
68 if ( $currentVersion == $this->mainwp_db_version ) {
69 return;
70 }
71
72 $charset_collate = $this->wpdb->get_charset_collate();
73
74 $sql = array();
75 $tbl = 'CREATE TABLE ' . $this->tableName( 'wp' ) . ' (
76 id int(11) NOT NULL auto_increment,
77 userid int(11) NOT NULL,
78 adminname text NOT NULL,
79 name text NOT NULL,
80 url text NOT NULL,
81 pubkey text NOT NULL,
82 privkey text NOT NULL,
83 nossl tinyint(1) NOT NULL,
84 nosslkey text NOT NULL,
85 siteurl text NOT NULL,
86 ga_id text NOT NULL,
87 gas_id int(11) NOT NULL,
88 offline_checks text NOT NULL,
89 offline_checks_last int(11) NOT NULL,
90 offline_check_result int(11) NOT NULL,
91 http_response_code int(11) NOT NULL DEFAULT 0,
92 note text NOT NULL,
93 note_lastupdate int(11) NOT NULL DEFAULT 0,
94 statsUpdate int(11) NOT NULL,
95 pagerank int(11) NOT NULL,
96 indexed int(11) NOT NULL,
97 alexia int(11) NOT NULL,
98 pagerank_old int(11) DEFAULT NULL,
99 indexed_old int(11) DEFAULT NULL,
100 alexia_old int(11) DEFAULT NULL,
101 directories longtext NOT NULL,
102 plugin_upgrades longtext NOT NULL,
103 theme_upgrades longtext NOT NULL,
104 translation_upgrades longtext NOT NULL,
105 premium_upgrades longtext NOT NULL,
106 securityIssues longtext NOT NULL,
107 themes longtext NOT NULL,
108 ignored_themes longtext NOT NULL,
109 plugins longtext NOT NULL,
110 ignored_plugins longtext NOT NULL,
111 pages longtext NOT NULL,
112 users longtext NOT NULL,
113 categories longtext NOT NULL,
114 pluginDir text NOT NULL,
115 automatic_update tinyint(1) NOT NULL,
116 backup_before_upgrade tinyint(1) NOT NULL DEFAULT 2,
117 last_db_backup_size int(11) NOT NULL,
118 backups text NOT NULL,
119 mainwpdir int(11) NOT NULL,
120 loadFilesBeforeZip tinyint(1) NOT NULL DEFAULT 1,
121 is_ignoreCoreUpdates tinyint(1) NOT NULL DEFAULT 0,
122 is_ignorePluginUpdates tinyint(1) NOT NULL DEFAULT 0,
123 is_ignoreThemeUpdates tinyint(1) NOT NULL DEFAULT 0,
124 verify_certificate tinyint(1) NOT NULL DEFAULT 1,
125 force_use_ipv4 tinyint(1) NOT NULL DEFAULT 0,
126 ssl_version tinyint(1) NOT NULL DEFAULT 0,
127 ip text NOT NULL DEFAULT "",
128 uniqueId text NOT NULL,
129 maximumFileDescriptorsOverride tinyint(1) NOT NULL DEFAULT 0,
130 maximumFileDescriptorsAuto tinyint(1) NOT NULL DEFAULT 1,
131 maximumFileDescriptors int(11) NOT NULL DEFAULT 150,
132 http_user text NOT NULL DEFAULT "",
133 http_pass text NOT NULL DEFAULT "",
134 wpe tinyint(1) NOT NULL,
135 is_staging tinyint(1) NOT NULL DEFAULT 0,
136 KEY idx_userid (userid)';
137 if ( $currentVersion == '' ) {
138 $tbl .= ',
139 PRIMARY KEY (id) ';
140 }
141 $tbl .= ') ' . $charset_collate;
142 $sql[] = $tbl;
143
144 $tbl = 'CREATE TABLE ' . $this->tableName( 'wp_sync' ) . ' (
145 wpid int(11) NOT NULL,
146 version text NOT NULL DEFAULT "",
147 sync_errors longtext NOT NULL DEFAULT "",
148 uptodate longtext NOT NULL DEFAULT "",
149 dtsAutomaticSync int(11) NOT NULL DEFAULT 0,
150 dtsAutomaticSyncStart int(11) NOT NULL DEFAULT 0,
151 dtsSync int(11) NOT NULL DEFAULT 0,
152 dtsSyncStart int(11) NOT NULL DEFAULT 0,
153 totalsize int(11) NOT NULL DEFAULT 0,
154 dbsize int(11) NOT NULL DEFAULT 0,
155 extauth text NOT NULL DEFAULT "",
156 last_post_gmt int(11) NOT NULL DEFAULT 0,
157 KEY idx_wpid (wpid)) ' . $charset_collate;
158 $sql[] = $tbl;
159
160 $tbl = 'CREATE TABLE ' . $this->tableName( 'wp_options' ) . ' (
161 wpid int(11) NOT NULL,
162 name text NOT NULL DEFAULT "",
163 value longtext NOT NULL DEFAULT "",
164 KEY idx_wpid (wpid)) ' . $charset_collate;
165 $sql[] = $tbl;
166
167 $tbl = 'CREATE TABLE ' . $this->tableName( 'wp_settings_backup' ) . ' (
168 wpid int(11) NOT NULL,
169 archiveFormat text NOT NULL,
170 KEY idx_wpid (wpid)) ' . $charset_collate;
171 $sql[] = $tbl;
172
173 $tbl = 'CREATE TABLE ' . $this->tableName( 'tips' ) . ' (
174 id int(11) NOT NULL auto_increment,
175 seq int(11) NOT NULL,
176 content text NOT NULL';
177 if ( $currentVersion == '' ) {
178 $tbl .= ',
179 PRIMARY KEY (id) ';
180 }
181 $tbl .= ') ' . $charset_collate;
182 $sql[] = $tbl;
183
184 $tbl = 'CREATE TABLE ' . $this->tableName( 'users' ) . " (
185 userid int(11) NOT NULL,
186 user_email text NOT NULL DEFAULT '',
187 tips tinyint(1) NOT NULL DEFAULT '1',
188 offlineChecksOnlineNotification tinyint(1) NOT NULL DEFAULT '0',
189 heatMap tinyint(1) NOT NULL DEFAULT '0',
190 ignored_plugins longtext NOT NULL DEFAULT '',
191 trusted_plugins longtext NOT NULL DEFAULT '',
192 trusted_plugins_notes longtext NOT NULL DEFAULT '',
193 ignored_themes longtext NOT NULL DEFAULT '',
194 trusted_themes longtext NOT NULL DEFAULT '',
195 trusted_themes_notes longtext NOT NULL DEFAULT '',
196 site_view tinyint(1) NOT NULL DEFAULT '0',
197 pluginDir text NOT NULL DEFAULT '',
198 dismissed_plugins longtext NOT NULL DEFAULT '',
199 dismissed_themes longtext NOT NULL DEFAULT ''";
200 if ( $currentVersion == '' ) {
201 $tbl .= ',
202 PRIMARY KEY (userid) ';
203 }
204 $tbl .= ') ' . $charset_collate;
205 $sql[] = $tbl;
206
207 $tbl = 'CREATE TABLE ' . $this->tableName( 'group' ) . ' (
208 id int(11) NOT NULL auto_increment,
209 userid int(11) NOT NULL,
210 name text NOT NULL';
211 if ( $currentVersion == '' ) {
212 $tbl .= ',
213 PRIMARY KEY (id) ';
214 }
215 $tbl .= ') ' . $charset_collate;
216 $sql[] = $tbl;
217
218 $sql[] = 'CREATE TABLE ' . $this->tableName( 'wp_group' ) . ' (
219 wpid int(11) NOT NULL,
220 groupid int(11) NOT NULL,
221 KEY idx_wpid (wpid),
222 KEY idx_groupid (groupid)
223 ) ' . $charset_collate;
224
225 $tbl = 'CREATE TABLE ' . $this->tableName( 'wp_backup_progress' ) . ' (
226 task_id int(11) NOT NULL,
227 wp_id int(11) NOT NULL,
228 dtsFetched int(11) NOT NULL DEFAULT 0,
229 fetchResult text NOT NULL DEFAULT "",
230 downloadedDB text NOT NULL DEFAULT "",
231 downloadedFULL text NOT NULL DEFAULT "",
232 downloadedDBComplete tinyint(1) NOT NULL DEFAULT 0,
233 downloadedFULLComplete tinyint(1) NOT NULL DEFAULT 0,
234 removedFiles tinyint(1) NOT NULL DEFAULT 0,
235 attempts int(11) NOT NULL DEFAULT 0,
236 last_error text NOT NULL DEFAULT "",
237 pid int(11) NOT NULL DEFAULT 0,
238 KEY idx_task_id (task_id)
239 ) ' . $charset_collate;
240 $sql[] = $tbl;
241
242 $tbl = 'CREATE TABLE ' . $this->tableName( 'wp_backup' ) . ' (
243 id int(11) NOT NULL auto_increment,
244 userid int(11) NOT NULL,
245 name text NOT NULL,
246 schedule text NOT NULL,
247 type text NOT NULL,
248 exclude text NOT NULL,
249 sites text NOT NULL,
250 groups text NOT NULL,
251 last int(11) NOT NULL,
252 last_run int(11) NOT NULL,
253 lastStartNotificationSent int(11) NOT NULL DEFAULT 0,
254 last_run_manually int(11) NOT NULL,
255 completed_sites text NOT NULL,
256 completed int(11) NOT NULL,
257 backup_errors text NOT NULL,
258 subfolder text NOT NULL,
259 filename text NOT NULL,
260 paused tinyint(1) NOT NULL,
261 template tinyint(1) DEFAULT 0,
262 excludebackup tinyint(1) DEFAULT 0,
263 excludecache tinyint(1) DEFAULT 0,
264 excludenonwp tinyint(1) DEFAULT 0,
265 excludezip tinyint(1) DEFAULT 0,
266 archiveFormat text NOT NULL,
267 loadFilesBeforeZip tinyint(1) NOT NULL DEFAULT 1,
268 maximumFileDescriptorsOverride tinyint(1) NOT NULL DEFAULT 0,
269 maximumFileDescriptorsAuto tinyint(1) NOT NULL DEFAULT 1,
270 maximumFileDescriptors int(11) NOT NULL DEFAULT 150';
271 if ( $currentVersion == '' ) {
272 $tbl .= ',
273 PRIMARY KEY (id) ';
274 }
275 $tbl .= ') ' . $charset_collate;
276 $sql[] = $tbl;
277
278 $tbl = 'CREATE TABLE ' . $this->tableName( 'request_log' ) . ' (
279 id int(11) NOT NULL auto_increment,
280 wpid int(11) NOT NULL,
281 ip text NOT NULL DEFAULT "",
282 subnet text NOT NULL DEFAULT "",
283 micro_timestamp_stop DECIMAL( 12, 2 ) NOT NULL DEFAULT 0,
284 micro_timestamp_start DECIMAL( 12, 2 ) NOT NULL DEFAULT 0';
285 if ( $currentVersion == '' || version_compare( $currentVersion, '5.7', '<=' ) ) {
286 $tbl .= ',
287 PRIMARY KEY (id) ';
288 }
289 $tbl .= ') ' . $charset_collate . ";";
290 $sql[] = $tbl;
291
292 require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
293 foreach ( $sql as $query ) {
294 dbDelta( $query );
295 }
296
297 // /** @var $this->wpdb wpdb */
298 // $this->wpdb->query('CREATE OR REPLACE VIEW ' . $this->tableName('wp_optionview') . ' AS
299 // SELECT intwp.id AS wpid,
300 // recent_comments.value AS recent_comments,
301 // recent_posts.value AS recent_posts,
302 // recent_pages.value AS recent_pages
303 // FROM ' . $this->tableName('wp') . ' intwp
304 // LEFT JOIN ' . $this->tableName('wp_options') . ' recent_comments ON recent_comments.wpid = intwp.id AND recent_comments.name = "recent_comments"
305 // LEFT JOIN ' . $this->tableName('wp_options') . ' recent_posts ON recent_posts.wpid = intwp.id AND recent_posts.name = "recent_posts"
306 // LEFT JOIN ' . $this->tableName('wp_options') . ' recent_pages ON recent_pages.wpid = intwp.id AND recent_pages.name = "recent_pages"');
307
308 $this->post_update();
309
310 if ( !is_multisite() ) {
311 MainWP_Utility::update_option( 'mainwp_db_version', $this->mainwp_db_version );
312 } else {
313 update_site_option( 'mainwp_db_version', $this->mainwp_db_version );
314 }
315 }
316
317 //Check for update - if required, update..
318 function update() {
319
320 }
321
322 function getOptionView( $extra = array()) {
323 $view = '(SELECT intwp.id AS wpid,
324 (SELECT recent_comments.value FROM ' . $this->tableName( 'wp_options' ) . ' recent_comments WHERE recent_comments.wpid = intwp.id AND recent_comments.name = "recent_comments" LIMIT 1) AS recent_comments,
325 (SELECT recent_posts.value FROM ' . $this->tableName( 'wp_options' ) . ' recent_posts WHERE recent_posts.wpid = intwp.id AND recent_posts.name = "recent_posts" LIMIT 1) AS recent_posts,
326 (SELECT recent_pages.value FROM ' . $this->tableName( 'wp_options' ) . ' recent_pages WHERE recent_pages.wpid = intwp.id AND recent_pages.name = "recent_pages" LIMIT 1) AS recent_pages,
327 (SELECT phpversion.value FROM ' . $this->tableName( 'wp_options' ) . ' phpversion WHERE phpversion.wpid = intwp.id AND phpversion.name = "phpversion" LIMIT 1) AS phpversion,
328 (SELECT wp_upgrades.value FROM ' . $this->tableName( 'wp_options' ) . ' wp_upgrades WHERE wp_upgrades.wpid = intwp.id AND wp_upgrades.name = "wp_upgrades" LIMIT 1) AS wp_upgrades ';
329
330 if (is_array($extra)) {
331 foreach($extra as $field) {
332 if (empty($field))
333 continue;
334 $view .= ', ';
335 $view .= '(SELECT ' . $this->escape($field) . '.value FROM ' . $this->tableName( '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);
336 }
337 }
338
339 $view .= ' FROM ' . $this->tableName( 'wp' ) . ' intwp)';
340
341 return $view;
342 }
343
344 function post_update() {
345 //get_site_option is multisite aware!
346 $currentVersion = get_site_option( 'mainwp_db_version' );
347 if ( $currentVersion === false ) {
348 return;
349 }
350
351 // if ( version_compare( $currentVersion, '2.8', '<' ) ) {
352 // $this->wpdb->update( $this->tableName( 'wp_backup' ), array( 'subfolder' => 'MainWP Backups/%url%/%type%/%date%' ), array( 'template' => '0' ) );
353 // }
354
355 // if ( version_compare( $currentVersion, '4.3', '<' ) ) {
356 // $row = $this->wpdb->get_row( 'SELECT * FROM ' . $this->tableName( 'users' ), OBJECT );
357 // if ( $row != null ) {
358 // $row->userid = 0;
359 // $this->updateUserExtension( $row );
360 // }
361 // }
362
363 // if ( version_compare( $currentVersion, '5.3', '<' ) ) {
364 // if ( MainWP_System::Instance()->isSingleUser() ) {
365 // $row = $this->wpdb->get_row( 'SELECT * FROM ' . $this->tableName( 'ga' ), OBJECT );
366 //
367 // $this->wpdb->update( $this->tableName( 'ga' ), array( 'userid' => 0 ), array( 'userid' => $row->userid ) );
368 // }
369 // }
370
371 // if ( version_compare( $currentVersion, '6.0', '=' ) ) {
372 // $this->wpdb->query( 'ALTER TABLE ' . $this->tableName( 'request_log' ) . ' CHANGE micro_timestamp_stop micro_timestamp_stop DECIMAL( 12, 2 ) NOT NULL DEFAULT 0' );
373 // $this->wpdb->query( 'ALTER TABLE ' . $this->tableName( 'request_log' ) . ' CHANGE micro_timestamp_start micro_timestamp_start DECIMAL( 12, 2 ) NOT NULL DEFAULT 0' );
374 // $this->wpdb->query( 'DELETE FROM ' . $this->tableName( 'request_log' ) . ' WHERE 1 ' );
375 // }
376
377 // if ( version_compare( $currentVersion, '6.2', '<' ) ) {
378 // $options = array(
379 // 'mainwp_db_version',
380 // //'mainwp_requests',
381 // 'mainwp_plugin_version',
382 // 'mainwp_upgradeVersionInfo',
383 // 'mainwp_cron_last_offlinecheck',
384 // 'mainwp_cron_last_updatescheck',
385 // 'mainwp_automaticUpdate_backupChecks',
386 // 'mainwp_updatescheck_mail_update_core_new',
387 // 'mainwp_updatescheck_mail_update_plugins_new',
388 // 'mainwp_updatescheck_mail_update_themes_new',
389 // 'mainwp_updatescheck_mail_update_core',
390 // 'mainwp_updatescheck_mail_update_plugins',
391 // 'mainwp_updatescheck_mail_update_themes',
392 // 'mainwp_updatescheck_mail_ignore_core',
393 // 'mainwp_updatescheck_mail_ignore_plugins',
394 // 'mainwp_updatescheck_mail_ignore_themes',
395 // 'mainwp_updatescheck_mail_ignore_core_new',
396 // 'mainwp_updatescheck_mail_ignore_plugins_new',
397 // 'mainwp_updatescheck_mail_ignore_themes_new',
398 // 'mainwp_updatescheck_last',
399 // 'mainwp_updatescheck_mail_email',
400 // 'mainwp_cron_last_ping',
401 // 'mainwp_cron_last_backups_continue',
402 // 'mainwp_cron_last_backups',
403 // 'mainwp_cron_last_stats',
404 // 'mainwp_backupsOnServer',
405 // 'mainwp_maximumFileDescriptors',
406 // 'mainwp_backupOnExternalSources',
407 // 'mainwp_notificationOnBackupFail',
408 // 'mainwp_notificationOnBackupStart',
409 // 'mainwp_chunkedBackupTasks',
410 // 'mainwp_maximumRequests',
411 // 'mainwp_minimumDelay',
412 // 'mainwp_maximumIPRequests',
413 // 'mainwp_minimumIPDelay',
414 // 'mainwp_extensions',
415 // 'mainwp_api_username',
416 // 'mainwp_api_password',
417 // 'mainwp_extension_widget_view',
418 // 'mainwp_news',
419 // 'mainwp_news_timestamp',
420 // 'mainwp_optimize',
421 // 'mainwp_automaticDailyUpdate',
422 // 'mainwp_backup_before_upgrade',
423 // 'mainwp_show_language_updates',
424 // 'mainwp_maximumPosts',
425 // 'mainwp_maximumComments',
426 // 'mainwp_cron_jobs',
427 // 'mainwp_wp_cron',
428 // );
429 // foreach ( $options as $option ) {
430 // MainWP_Utility::fix_option( $option );
431 // }
432 // }
433
434 // if ( version_compare( $currentVersion, '7.3', '<' ) ) {
435 // //get all sites
436 // $sites = $this->wpdb->get_results( 'SELECT id FROM ' . $this->tableName( 'wp' ) );
437 // if ( !empty( $sites ) ) {
438 // foreach ( $sites as $site ) {
439 // $this->wpdb->insert( $this->tableName( 'wp_settings_backup' ), array(
440 // 'wpid' => $site->id,
441 // 'archiveFormat' => 'global',
442 // ) );
443 // }
444 // }
445 // }
446
447 // if ( version_compare( $currentVersion, '8', '<' ) ) {
448 // $apiPass = get_option( 'mainwp_api_password' );
449 // MainWP_Utility::update_option( 'mainwp_api_password', MainWP_Utility::encrypt( $apiPass, 'MainWPAPI' ) );
450 // }
451
452 if ( version_compare( $currentVersion, '8.1', '<' ) ) {
453 //We can't split up here!
454 $wpSyncColumns = array(
455 'version',
456 'totalsize',
457 'dbsize',
458 'extauth',
459 'last_post_gmt',
460 'uptodate',
461 'sync_errors',
462 'dtsSync',
463 'dtsSyncStart',
464 'dtsAutomaticSync',
465 'dtsAutomaticSyncStart',
466 );
467 foreach ( $wpSyncColumns as $wpSyncColumn ) {
468 $rslts = $this->wpdb->get_results( 'SELECT id,' . $wpSyncColumn . ' FROM ' . $this->tableName( 'wp' ), ARRAY_A );
469 if ( empty( $rslts ) ) {
470 continue;
471 }
472
473 foreach ( $rslts as $rslt ) {
474 $exists = $this->wpdb->get_results( 'SELECT wpid FROM ' . $this->tableName( 'wp_sync' ) . ' WHERE wpid = ' . $rslt[ 'id' ], ARRAY_A );
475 if ( empty( $exists ) ) {
476 $this->wpdb->insert( $this->tableName( 'wp_sync' ), array(
477 'wpid' => $rslt[ 'id' ],
478 $wpSyncColumn => $rslt[ $wpSyncColumn ],
479 ) );
480 } else {
481 $this->wpdb->update( $this->tableName( 'wp_sync' ), array( $wpSyncColumn => $rslt[ $wpSyncColumn ] ), array( 'wpid' => $rslt[ 'id' ] ) );
482 }
483 }
484
485 $suppress = $this->wpdb->suppress_errors();
486 $this->wpdb->query( 'ALTER TABLE ' . $this->tableName( 'wp' ) . ' DROP COLUMN ' . $wpSyncColumn );
487 $this->wpdb->suppress_errors( $suppress );
488 }
489
490 $optionColumns = array(
491 'last_wp_upgrades',
492 'last_plugin_upgrades',
493 'last_theme_upgrades',
494 'wp_upgrades',
495 'recent_comments',
496 'recent_posts',
497 'recent_pages',
498 );
499 foreach ( $optionColumns as $optionColumn ) {
500 $rslts = $this->wpdb->get_results( 'SELECT id,' . $optionColumn . ' FROM ' . $this->tableName( 'wp' ), ARRAY_A );
501 if ( empty( $rslts ) ) {
502 continue;
503 }
504
505 foreach ( $rslts as $rslt ) {
506 MainWP_DB::updateWebsiteOption( (object) $rslt, $optionColumn, $rslt[ $optionColumn ] );
507 }
508
509 $suppress = $this->wpdb->suppress_errors();
510 $this->wpdb->query( 'ALTER TABLE ' . $this->tableName( 'wp' ) . ' DROP COLUMN ' . $optionColumn );
511 $this->wpdb->suppress_errors( $suppress );
512 }
513 }
514 }
515
516 public function getFirstSyncedSite( $userId = null ) {
517 if ( ( $userId == null ) && MainWP_System::Instance()->isMultiUser() ) {
518 global $current_user;
519 $userId = $current_user->ID;
520 }
521 $where = ( $userId != null ) ? ' userid = ' . $userId : '';
522 $where .= $this->getWhereAllowAccessSites( 'wp' );
523 $qry = 'SELECT wp_sync.dtsSync FROM ' . $this->tableName( 'wp' ) . ' wp JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE wp_sync.sync_errors = "" ' . $where . ' ORDER BY wp_sync.dtsSync ASC LIMIT 1';
524
525 return $this->wpdb->get_var( $qry );
526 }
527
528 public function getLastSyncStatus( $userId = null ) {
529 $sql = MainWP_DB::Instance()->getSQLWebsitesForCurrentUser();
530 $websites = MainWP_DB::Instance()->query( $sql );
531
532 $return = array(
533 'sync_status' => false,
534 'last_sync' => 0
535 );
536
537 if ( !$websites ) {
538 $return[ 'sync_status' ] = 'all_synced';
539 return $return;
540 }
541
542 $total_sites = 0;
543 $synced_sites = 0;
544 $last_sync = 0;
545 @MainWP_DB::data_seek( $websites, 0 );
546 while ( $websites && ( $website = @MainWP_DB::fetch_object( $websites ) ) ) {
547 if ( empty( $website ) || $website->sync_errors != '' ) {
548 continue;
549 }
550 $total_sites++;
551 if ( time() - $website->dtsSync < 60 * 60 * 24 ) {
552 $synced_sites++;
553 }
554 if ( $last_sync < $website->dtsSync ) {
555 $last_sync = $website->dtsSync;
556 }
557 }
558
559 if ( $total_sites == $synced_sites ) {
560 $return[ 'sync_status' ] = 'all_synced';
561 } else if ( $synced_sites == 0 ) {
562 $return[ 'sync_status' ] = 'not_synced';
563 }
564 $return[ 'last_sync' ] = $last_sync;
565 return $return;
566 }
567
568 public function getDisconnectedWebsites() {
569 $sql = MainWP_DB::Instance()->getSQLWebsitesForCurrentUser();
570 $websites = MainWP_DB::Instance()->query( $sql );
571
572 if ( !$websites ) {
573 return array();
574 }
575
576 $disc_sites = array();
577
578 @MainWP_DB::data_seek( $websites, 0 );
579 while ( $websites && ( $website = @MainWP_DB::fetch_object( $websites ) ) ) {
580 if ( empty( $website ) ) {
581 continue;
582 }
583 if ( $website->sync_errors != '' ) {
584 $disc_sites[$website->id] = $website->url;
585 }
586
587 }
588
589 return $disc_sites;
590 }
591
592 public function getRequestsSince( $pSeconds ) {
593 $where = $this->getWhereAllowAccessSites( 'wp' );
594 $qry = 'SELECT count(*) FROM ' . $this->tableName( 'wp' ) . ' wp JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid WHERE wp_sync.dtsSyncStart > ' . ( time() - $pSeconds ) . $where;
595
596 return $this->wpdb->get_var( $qry );
597 }
598
599 //Database actions
600 public function getWebsitesCount( $userId = null, $all_access = false ) {
601 if ( ( $userId == null ) && MainWP_System::Instance()->isMultiUser() ) {
602 global $current_user;
603 $userId = $current_user->ID;
604 }
605 $where = ( $userId == null ? '' : ' wp.userid = ' . $userId );
606 if ( ! $all_access ) {
607 $where .= $this->getWhereAllowAccessSites( 'wp' );
608 }
609 $qry = 'SELECT COUNT(wp.id) FROM ' . $this->tableName( 'wp' ) . ' wp WHERE 1 ' . $where;
610
611 return $this->wpdb->get_var( $qry );
612 }
613
614 public function getWebsiteOption( $website, $option ) {
615
616 if ( is_array($website) ) {
617 if ( isset($website[$option]) ) {
618 return $website[$option];
619 }
620 $site_id = $website['id'];
621 } else if ( is_object($website) ) {
622 if ( property_exists( $website, $option ) ) {
623 return $website->{$option};
624 }
625 $site_id = $website->id;
626 }
627
628 return $this->wpdb->get_var( 'SELECT value FROM ' . $this->tableName( 'wp_options' ) . ' WHERE wpid = ' . $site_id . ' AND name = "' . $this->escape( $option ) . '"' );
629 }
630
631 public function updateWebsiteOption( $website, $option, $value ) {
632 $rslt = $this->wpdb->get_results( 'SELECT name FROM ' . $this->tableName( 'wp_options' ) . ' WHERE wpid = ' . $website->id . ' AND name = "' . $this->escape( $option ) . '"' );
633 if ( count( $rslt ) > 0 ) {
634 $this->wpdb->delete( $this->tableName( 'wp_options' ), array(
635 'wpid' => $website->id,
636 'name' => $this->escape( $option ),
637 ) );
638 $rslt = $this->wpdb->get_results( 'SELECT name FROM ' . $this->tableName( 'wp_options' ) . ' WHERE wpid = ' . $website->id . ' AND name = "' . $this->escape( $option ) . '"' );
639 }
640
641 if ( count( $rslt ) == 0 ) {
642 $this->wpdb->insert( $this->tableName( 'wp_options' ), array(
643 'wpid' => $website->id,
644 'name' => $option,
645 'value' => $value,
646 ) );
647 } else {
648 $this->wpdb->update( $this->tableName( 'wp_options' ), array( 'value' => $value ), array(
649 'wpid' => $website->id,
650 'name' => $option,
651 ) );
652 }
653 }
654
655 public function getWebsitesByUserId( $userid, $selectgroups = false, $search_site = null, $orderBy = 'wp.url' ) {
656 return $this->getResultsResult( $this->getSQLWebsitesByUserId( $userid, $selectgroups, $search_site, $orderBy ) );
657 }
658
659 public function getSQLWebsites() {
660 $where = $this->getWhereAllowAccessSites( 'wp' );
661
662 return 'SELECT wp.*,wp_sync.*,wp_optionview.*
663 FROM ' . $this->tableName( 'wp' ) . ' wp
664 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
665 JOIN ' . $this->getOptionView() . ' wp_optionview ON wp.id = wp_optionview.wpid
666 ' . $where;
667 }
668
669 public function getSQLWebsitesByUserId( $userid, $selectgroups = false, $search_site = null, $orderBy = 'wp.url',
670 $offset = false, $rowcount = false ) {
671 if ( MainWP_Utility::ctype_digit( $userid ) ) {
672 $where = '';
673 if ( $search_site !== null ) {
674 $search_site = trim( $search_site );
675 $where = ' AND (wp.name LIKE "%' . $search_site . '%" OR wp.url LIKE "%' . $search_site . '%") ';
676 }
677
678 $where .= $this->getWhereAllowAccessSites( 'wp' );
679
680 if ( $selectgroups ) {
681 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ", ") as wpgroups
682 FROM ' . $this->tableName( 'wp' ) . ' wp
683 LEFT JOIN ' . $this->tableName( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
684 LEFT JOIN ' . $this->tableName( 'group' ) . ' gr ON wpgr.groupid = gr.id
685 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
686 JOIN ' . $this->getOptionView() . ' wp_optionview ON wp.id = wp_optionview.wpid
687 WHERE wp.userid = ' . $userid . "
688 $where
689 GROUP BY wp.id
690 ORDER BY " . $orderBy;
691 } else {
692 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*
693 FROM ' . $this->tableName( 'wp' ) . ' wp
694 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
695 JOIN ' . $this->getOptionView() . ' wp_optionview ON wp.id = wp_optionview.wpid
696 WHERE wp.userid = ' . $userid . "
697 $where
698 ORDER BY " . $orderBy;
699 }
700
701 if ( ( $offset !== false ) && ( $rowcount !== false ) ) {
702 $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
703 }
704
705 return $qry;
706 }
707
708 return null;
709 }
710
711 public function getSQLWebsitesForCurrentUser( $selectgroups = false, $search_site = null, $orderBy = 'wp.url',
712 $offset = false, $rowcount = false, $extraWhere = null, $for_manager = false,
713 $extra_view = array( 'favi_icon' ), $is_staging = 'no' ) {
714 $where = '';
715 if ( MainWP_System::Instance()->isMultiUser() ) {
716 global $current_user;
717 $where .= ' AND wp.userid = ' . $current_user->ID . ' ';
718 }
719
720 if ( $search_site !== null ) {
721 $search_site = trim( $search_site );
722 $where .= ' AND (wp.name LIKE "%' . $search_site . '%" OR wp.url LIKE "%' . $search_site . '%") ';
723 }
724
725 if ( $extraWhere !== null ) {
726 $where .= ' AND ' . $extraWhere;
727 }
728
729 if ( !$for_manager ) {
730 $where .= $this->getWhereAllowAccessSites( 'wp', $is_staging );
731 }
732
733 if ( $orderBy == 'wp.url' ) {
734 $orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www', '')";
735 }
736
737 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax
738 if ( $selectgroups ) {
739 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ", ") as wpgroups
740 FROM ' . $this->tableName( 'wp' ) . ' wp
741 LEFT JOIN ' . $this->tableName( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
742 LEFT JOIN ' . $this->tableName( 'group' ) . ' gr ON wpgr.groupid = gr.id
743 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
744 JOIN ' . $this->getOptionView( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
745 WHERE 1 ' . $where . '
746 GROUP BY wp.id
747 ORDER BY ' . $orderBy;
748 } else {
749 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*
750 FROM ' . $this->tableName( 'wp' ) . ' wp
751 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
752 JOIN ' . $this->getOptionView( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
753 WHERE 1 ' . $where . '
754 ORDER BY ' . $orderBy;
755 }
756
757 if ( ( $offset !== false ) && ( $rowcount !== false ) ) {
758 $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
759 }
760
761 return $qry;
762 }
763
764 public function getSQLSearchWebsitesForCurrentUser( $params ) {
765
766 if ( !is_array($params) )
767 $params = array();
768
769 $selectgroups = isset($params['selectgroups']) && $params['selectgroups'] ? true : false;
770 $search_site = isset($params['search']) ? $this->escape(trim($params['search'])) : null;
771 $orderBy = isset($params['orderby']) ? $params['orderby'] : 'wp.url';
772 $offset = isset($params['offset']) ? intval($params['offset']) : false;
773 $rowcount = isset($params['rowcount']) ? intval($params['rowcount']) : false;
774 $extraWhere = isset($params['extra_where']) ? $params['extra_where'] : null;
775 $for_manager = isset($params['for_manager']) && $params['for_manager'] ? true : false;
776 $extra_view = isset($params['extra_view']) ? $params['extra_view'] : array( 'favi_icon' ) ;
777 $is_staging = isset($params['is_staging']) && $params['is_staging'] == 'yes' ? 'yes' : 'no' ;
778
779 $group_id = isset($params['group_id']) && $params['group_id'] ? intval($params['group_id']) : false;
780
781 if ( $selectgroups ) {
782 if ( $staging_group = get_option( 'mainwp_stagingsites_group_id' ) ) {
783 if ( $group_id == $staging_group ) {
784 $is_staging = 'yes'; // will list staging sites
785 }
786 }
787 }
788
789 $where = '';
790 if ( MainWP_System::Instance()->isMultiUser() ) {
791 global $current_user;
792 $where .= ' AND wp.userid = ' . $current_user->ID . ' ';
793 }
794
795 // for searching
796 if ( $search_site !== null && $search_site != '' ) {
797 $where .= ' AND (wp.name LIKE "%' . $search_site . '%" OR wp.url LIKE "%' . $search_site . '%") ';
798 }
799
800 if ( $extraWhere !== null ) {
801 $where .= ' AND ' . $extraWhere;
802 }
803
804 if ( ! $for_manager ) {
805 $where .= $this->getWhereAllowAccessSites( 'wp', $is_staging );
806 }
807
808 if ( $orderBy == 'wp.url' ) {
809 $orderBy = "replace(replace(replace(replace(replace(wp.url, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www', '')";
810 }
811
812 $join_group = "";
813 $where_group = "";
814
815 if ( MainWP_Utility::ctype_digit( $group_id ) && $group_id > 0 ) {
816 $join_group = " JOIN " . $this->tableName( 'wp_group' ) . " wpgroup ON wp.id = wpgroup.wpid ";
817 $where_group = " AND wpgroup.groupid = " . $group_id;
818 }
819
820 // wpgroups to fix issue for mysql 8.0, as groups will generate error syntax
821 if ( $selectgroups ) {
822 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ", ") as wpgroups
823 FROM ' . $this->tableName( 'wp' ) . ' wp ' .
824 $join_group . '
825 LEFT JOIN ' . $this->tableName( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
826 LEFT JOIN ' . $this->tableName( 'group' ) . ' gr ON wpgr.groupid = gr.id
827 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
828 JOIN ' . $this->getOptionView( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
829 WHERE 1 ' . $where . $where_group . '
830 GROUP BY wp.id
831 ORDER BY ' . $orderBy;
832 } else {
833 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*
834 FROM ' . $this->tableName( 'wp' ) . ' wp ' .
835 $join_group . '
836 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
837 JOIN ' . $this->getOptionView( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
838 WHERE 1 ' . $where . $where_group . '
839 ORDER BY ' . $orderBy;
840 }
841
842 if ( ( $offset !== false ) && ( $rowcount !== false ) ) {
843 $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
844 }
845
846 return $qry;
847 }
848
849
850 public function getWhereAllowAccessSites( $site_table_alias = '', $is_staging = 'no' ) {
851
852 if ( empty( $site_table_alias ) ) {
853 $site_table_alias = $this->tableName( 'wp' );
854 }
855
856 // check to filter the staging sites
857 $where_staging = ' AND ' . $site_table_alias . '.is_staging = 0 ';
858 if ( $is_staging == 'no' ) {
859 $where_staging = ' AND ' . $site_table_alias . '.is_staging = 0 ';
860 } else if ( $is_staging == 'yes' ) {
861 $where_staging = ' AND ' . $site_table_alias . '.is_staging = 1 ';
862 } else if ( $is_staging == 'nocheckstaging' ) {
863 $where_staging = '';
864 }
865 // end staging filter
866
867 $_where = $where_staging;
868 // To fix bug run from cron job
869 if ( defined( 'DOING_CRON' ) && DOING_CRON ) {
870 return $_where;
871 }
872
873 // To fix bug run from wp cli
874 if ( defined( 'WP_CLI' ) && WP_CLI ) {
875 return $_where;
876 }
877
878 $allowed_sites = apply_filters( 'mainwp_currentuserallowedaccesssites', 'all' );
879
880 if ( $allowed_sites == 'all' ) {
881 return $_where; // allow access all sites
882 }
883
884
885 if ( is_array( $allowed_sites ) && count( $allowed_sites ) > 0 ) {
886 $_where .= ' AND ' . $site_table_alias . '.id IN (' . implode( ',', $allowed_sites ) . ') ';
887 } else {
888 $_where .= ' AND 0 '; // denied access
889 }
890
891 return $_where;
892 }
893
894 public function getWhereAllowGroups( $group_table_alias = '', $with_staging = 'no' ) {
895 // To fix bug run from cron job
896 if ( defined( 'DOING_CRON' ) && DOING_CRON ) {
897 return '';
898 }
899
900 if ( empty( $group_table_alias ) ) {
901 $group_table_alias = $this->tableName( 'group' );
902 }
903
904 // check to filter the staging group
905 $where_staging_group = '';
906 $staging_group = get_option( 'mainwp_stagingsites_group_id' );
907 if ( $staging_group ) {
908 $where_staging_group = ' AND ' . $group_table_alias . '.id <> ' . $staging_group . ' '; // filter the staging group
909 if ( $with_staging == 'yes' ) {
910 $where_staging_group = ''; // will do not the filter staging group
911 }
912 }
913 // end staging filter
914
915 $_where = $where_staging_group;
916
917 $allowed_groups = apply_filters( 'mainwp_currentuserallowedaccessgroups', 'all' );
918
919 if ( $allowed_groups == 'all' ) {
920 return $_where;
921 } // allow all groups
922
923
924 if ( is_array( $allowed_groups ) && count( $allowed_groups ) > 0 ) {
925 return ' AND ' . $group_table_alias . '.id IN (' . implode( ',', $allowed_groups ) . ') ' . $_where;
926 } else {
927 return ' AND 0 '; // not allow access groups
928 }
929 }
930
931 public function getGroupByNameForUser( $name, $userid = null ) {
932 if ( ( $userid == null ) && MainWP_System::Instance()->isMultiUser() ) {
933 global $current_user;
934 $userid = $current_user->ID;
935 }
936 $where = ( $userid != null ) ? ' AND userid=' . $userid : '';
937 $where .= $this->getWhereAllowGroups();
938
939 return $this->wpdb->get_row( 'SELECT * FROM ' . $this->tableName( 'group' ) . ' WHERE 1 ' . $where . ' AND name="' . $this->escape( $name ) . '"' );
940 }
941
942 public function getGroupById( $id ) {
943 if ( MainWP_Utility::ctype_digit( $id ) ) {
944 return $this->wpdb->get_row( 'SELECT * FROM ' . $this->tableName( 'group' ) . ' WHERE id=' . $id );
945 }
946
947 return null;
948 }
949
950 public function getGroupsByUserId( $userid ) {
951 if ( MainWP_Utility::ctype_digit( $userid ) ) {
952 $where = $this->getWhereAllowGroups();
953
954 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'group' ) . ' WHERE userid = ' . $userid . $where . ' ORDER BY name', OBJECT_K );
955 }
956
957 return null;
958 }
959
960 public function getGroupsForManageSites() {
961 $where = ' 1 ';
962 if ( MainWP_System::Instance()->isMultiUser() ) {
963 global $current_user;
964 $where = ' userid = ' . $current_user->ID . ' ';
965 }
966 $with_staging = 'yes';
967 $staging_enabled = apply_filters('mainwp-extension-available-check', 'mainwp-staging-extension') || apply_filters('mainwp-extension-available-check', 'mainwp-timecapsule-extension');
968
969 if ( !$staging_enabled ) {
970 $with_staging = 'no';
971 }
972
973 $where .= $this->getWhereAllowGroups('' , $with_staging );
974 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'group' ) . ' WHERE ' . $where . ' ORDER BY name', OBJECT_K );
975 }
976
977 public function getGroupsForCurrentUser() {
978 $where = ' 1 ';
979 if ( MainWP_System::Instance()->isMultiUser() ) {
980 global $current_user;
981 $where = ' userid = ' . $current_user->ID . ' ';
982 }
983 $where .= $this->getWhereAllowGroups();
984
985 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'group' ) . ' WHERE ' . $where . ' ORDER BY name', OBJECT_K );
986 }
987
988 public function getGroupsByWebsiteId( $websiteid ) {
989 if ( MainWP_Utility::ctype_digit( $websiteid ) ) {
990 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'group' ) . ' gr
991 JOIN ' . $this->tableName( 'wp_group' ) . ' wpgr ON gr.id = wpgr.groupid
992 WHERE wpgr.wpid = ' . $websiteid . ' ORDER BY name', OBJECT_K );
993 }
994
995 return null;
996 }
997
998 public function getGroupsAndCount( $userid = null, $for_manager = false ) {
999 if ( ( $userid == null ) && MainWP_System::Instance()->isMultiUser() ) {
1000 global $current_user;
1001 $userid = $current_user->ID;
1002 }
1003
1004 $where = '';
1005
1006 if ( $userid != null ) {
1007 $where = ' AND gr.userid = ' . $userid;
1008 }
1009
1010 if ( !$for_manager ) {
1011 $where .= $this->getWhereAllowGroups( 'gr' );
1012 }
1013
1014 return $this->wpdb->get_results( 'SELECT gr.*, COUNT(DISTINCT(wpgr.wpid)) as nrsites
1015 FROM ' . $this->tableName( 'group' ) . ' gr
1016 LEFT JOIN ' . $this->tableName( 'wp_group' ) . ' wpgr ON gr.id = wpgr.groupid
1017 WHERE 1 ' . $where . '
1018 GROUP BY gr.id
1019 ORDER BY gr.name', OBJECT_K );
1020 }
1021
1022 public function getGroupsByName( $name ) {
1023 return $this->wpdb->get_results( 'SELECT gr.*
1024 FROM ' . $this->tableName( 'group' ) . ' gr
1025 WHERE gr.name = "' . $this->escape( $name ) . '"'
1026 , OBJECT_K );
1027 }
1028
1029 public function getNotEmptyGroups( $userid = null, $enableOfflineSites = true ) {
1030 if ( ( $userid == null ) && MainWP_System::Instance()->isMultiUser() ) {
1031 global $current_user;
1032 $userid = $current_user->ID;
1033 }
1034
1035 $where = ' WHERE 1 ';
1036 $where .= $this->getWhereAllowGroups( 'g' );
1037
1038 if ( $userid != null ) {
1039 $where .= ' AND g.userid = ' . $userid;
1040 }
1041 if ( !$enableOfflineSites ) {
1042 $where .= ' AND wp_sync.sync_errors = ""';
1043 }
1044
1045 return $this->wpdb->get_results( 'SELECT DISTINCT(g.id), g.name, count(wp.wpid)
1046 FROM ' . $this->tableName( 'group' ) . ' g
1047 JOIN ' . $this->tableName( 'wp_group' ) . ' wp ON g.id = wp.groupid
1048 JOIN ' . $this->tableName( 'wp' ) . ' wpsite ON wp.wpid = wpsite.id
1049 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.wpid = wp_sync.wpid
1050 ' . $where . '
1051 GROUP BY g.id
1052 HAVING count(wp.wpid) > 0
1053 ORDER BY g.name', OBJECT_K );
1054 }
1055
1056 public function getWebsitesByUrl( $url ) {
1057 if ( substr( $url, - 1 ) != '/' ) {
1058 $url .= '/';
1059 }
1060 $where = '';
1061 $results = $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'wp' ) . ' WHERE url = "' . $this->escape( $url ) . '"' . $where, OBJECT );
1062 if ( $results ) {
1063 return $results;
1064 }
1065
1066 if ( stristr( $url, '/www.' ) ) {
1067 //remove www if it's there!
1068 $url = str_replace( '/www.', '/', $url );
1069 } else {
1070 //add www if it's not there!
1071 $url = str_replace( 'https://', 'https://www.', $url );
1072 $url = str_replace( 'http://', 'http://www.', $url );
1073 }
1074
1075 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'wp' ) . ' WHERE url = "' . $this->escape( $url ) . '"' . $where, OBJECT );
1076 }
1077
1078 public function getWebsiteBackupSettings( $websiteid ) {
1079 if ( !MainWP_Utility::ctype_digit( $websiteid ) ) {
1080 return null;
1081 }
1082
1083 return $this->getRowResult( 'SELECT * FROM ' . $this->tableName( 'wp_settings_backup' ) . ' WHERE wpid = ' . $websiteid );
1084 }
1085
1086 public function getWebsiteById( $id, $selectGroups = false ) {
1087 return $this->getRowResult( $this->getSQLWebsiteById( $id, $selectGroups ) );
1088 }
1089
1090 public function getSQLWebsiteById( $id, $selectGroups = false, $extra_view = array( 'favi_icon' ) ) {
1091 if ( MainWP_Utility::ctype_digit( $id ) ) {
1092 $where = $this->getWhereAllowAccessSites( 'wp', 'nocheckstaging' ); // no check staging
1093 if ( $selectGroups ) {
1094 return 'SELECT wp.*,wp_sync.*,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ", ") as wpgroups
1095 FROM ' . $this->tableName( 'wp' ) . ' wp
1096 LEFT JOIN ' . $this->tableName( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1097 LEFT JOIN ' . $this->tableName( 'group' ) . ' gr ON wpgr.groupid = gr.id
1098 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1099 JOIN ' . $this->getOptionView( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1100 WHERE wp.id = ' . $id . $where . '
1101 GROUP BY wp.id';
1102 }
1103
1104 return 'SELECT wp.*,wp_sync.*,wp_optionview.*
1105 FROM ' . $this->tableName( 'wp' ) . ' wp
1106 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1107 JOIN ' . $this->getOptionView( $extra_view ) . ' wp_optionview ON wp.id = wp_optionview.wpid
1108 WHERE id = ' . $id . $where;
1109 }
1110
1111 return null;
1112 }
1113
1114 public function getWebsitesByIds( $ids, $userId = null ) {
1115 if ( ( $userId == null ) && MainWP_System::Instance()->isMultiUser() ) {
1116 global $current_user;
1117 $userId = $current_user->ID;
1118 }
1119 $where = $this->getWhereAllowAccessSites();
1120
1121 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'wp' ) . ' WHERE id IN (' . implode( ',', $ids ) . ')' . ( $userId != null ? ' AND userid = ' . $userId : '' ) . $where, OBJECT );
1122 }
1123
1124 public function getWebsitesByGroupIds( $ids, $userId = null ) {
1125 if ( empty( $ids ) ) {
1126 return array();
1127 }
1128 if ( ( $userId == null ) && MainWP_System::Instance()->isMultiUser() ) {
1129 global $current_user;
1130 $userId = $current_user->ID;
1131 }
1132
1133 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'wp' ) . ' wp JOIN ' . $this->tableName( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid WHERE wpgroup.groupid IN (' . implode( ',', $ids ) . ') ' . ( $userId != null ? ' AND wp.userid = ' . $userId : '' ), OBJECT );
1134 }
1135
1136 public function getWebsitesByGroupId( $id ) {
1137 return $this->getResultsResult( $this->getSQLWebsitesByGroupId( $id ) );
1138 }
1139
1140 public function getSQLWebsitesByGroupId( $id, $selectgroups = false, $orderBy = 'wp.url', $offset = false,
1141 $rowcount = false, $where = null, $search_site = null) {
1142
1143 $is_staging = 'no';
1144 if ( $selectgroups ) {
1145 if ( $staging_group = get_option( 'mainwp_stagingsites_group_id' ) ) {
1146 if ( $id == $staging_group ) {
1147 $is_staging = 'yes'; // will list staging sites
1148 }
1149 }
1150 }
1151
1152 $where_search = '';
1153 if ( !empty( $search_site ) ) {
1154 $search_site = trim( $search_site );
1155 $where_search .= ' AND (wp.name LIKE "%' . $this->escape( $search_site ) . '%" OR wp.url LIKE "%' . $this->escape( $search_site ) . '%") ';
1156 }
1157
1158 if ( MainWP_Utility::ctype_digit( $id ) ) {
1159 $where_allowed = $this->getWhereAllowAccessSites( 'wp', $is_staging );
1160 if ( $selectgroups ) {
1161 $qry = 'SELECT wp.*,wp_sync.*,wp_optionview.*, GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ", ") as wpgroups
1162 FROM ' . $this->tableName( 'wp' ) . ' wp
1163 JOIN ' . $this->tableName( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
1164 LEFT JOIN ' . $this->tableName( 'wp_group' ) . ' wpgr ON wp.id = wpgr.wpid
1165 LEFT JOIN ' . $this->tableName( 'group' ) . ' gr ON wpgr.groupid = gr.id
1166 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1167 JOIN ' . $this->getOptionView() . ' wp_optionview ON wp.id = wp_optionview.wpid
1168 WHERE wpgroup.groupid = ' . $id . ' ' .
1169 ( $where == null ? '' : ' AND ' . $where ) . $where_allowed . $where_search . '
1170 GROUP BY wp.id
1171 ORDER BY ' . $orderBy;
1172 } else {
1173 $qry = 'SELECT wp.*,wp_sync.* FROM ' . $this->tableName( 'wp' ) . ' wp
1174 JOIN ' . $this->tableName( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
1175 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1176 WHERE wpgroup.groupid = ' . $id . ' ' . $where_allowed . $where_search .
1177 ( $where == null ? '' : ' AND ' . $where ) . ' ORDER BY ' . $orderBy;
1178 }
1179 if ( ( $offset !== false ) && ( $rowcount !== false ) ) {
1180 $qry .= ' LIMIT ' . $offset . ', ' . $rowcount;
1181 }
1182
1183 return $qry;
1184 }
1185
1186 return null;
1187 }
1188
1189 public function getWebsitesByGroupName( $userid, $groupname ) {
1190 return $this->getResultsResult( $this->getSQLWebsitesByGroupName( $groupname, $userid ) );
1191 }
1192
1193 public function getSQLWebsitesByGroupName( $groupname, $userid = null ) {
1194 if ( ( $userid == null ) && MainWP_System::Instance()->isMultiUser() ) {
1195 global $current_user;
1196 $userid = $current_user->ID;
1197 }
1198
1199 $sql = 'SELECT wp.*,wp_sync.*,wp_optionview.* FROM ' . $this->tableName( 'wp' ) . ' wp
1200 INNER JOIN ' . $this->tableName( 'wp_group' ) . ' wpgroup ON wp.id = wpgroup.wpid
1201 JOIN ' . $this->tableName( 'group' ) . ' g ON wpgroup.groupid = g.id
1202 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1203 JOIN ' . $this->getOptionView() . ' wp_optionview ON wp.id = wp_optionview.wpid
1204 WHERE g.name="' . $this->escape( $groupname ) . '"';
1205 if ( $userid != null ) {
1206 $sql .= ' AND g.userid = "' . $userid . '"';
1207 }
1208
1209 return $sql;
1210 }
1211
1212 public function getWPIp( $wpid ) {
1213 return $this->wpdb->get_var( 'SELECT ip FROM ' . $this->tableName( 'request_log' ) . ' WHERE wpid = "' . $wpid . '"' );
1214 }
1215
1216 public function insertOrUpdateRequestLog( $wpid, $ip, $start, $stop ) {
1217 $updateValues = array();
1218 if ( $ip != null ) {
1219 $updateValues[ 'ip' ] = $ip;
1220 }
1221 if ( $start != null ) {
1222 $updateValues[ 'micro_timestamp_start' ] = $start;
1223 }
1224 if ( $stop != null ) {
1225 $updateValues[ 'micro_timestamp_stop' ] = $stop;
1226 }
1227
1228 $var = $this->wpdb->get_var( 'SELECT id FROM ' . $this->tableName( 'request_log' ) . ' WHERE wpid = "' . $wpid . '"' );
1229 if ( $var !== null ) {
1230 $this->wpdb->update( $this->tableName( 'request_log' ), $updateValues, array( 'wpid' => $wpid ) );
1231 } else {
1232 $updateValues[ 'wpid' ] = $wpid;
1233 $this->wpdb->insert( $this->tableName( 'request_log' ), $updateValues );
1234 }
1235 }
1236
1237 public function closeOpenRequests() {
1238 //Close requests open longer then 7 seconds.. something is wrong here..
1239 $this->wpdb->query( 'UPDATE ' . $this->tableName( 'request_log' ) . ' SET micro_timestamp_stop = micro_timestamp_start WHERE micro_timestamp_stop < micro_timestamp_start and ' . microtime( true ) . ' - micro_timestamp_start > 7' );
1240 }
1241
1242 public function getNrOfOpenRequests( $ip = null ) {
1243 if ( $ip == null ) {
1244 return $this->wpdb->get_var( 'select count(id) from ' . $this->tableName( 'request_log' ) . ' where micro_timestamp_stop < micro_timestamp_start' );
1245 }
1246
1247 return $this->wpdb->get_var( 'select count(id) from ' . $this->tableName( 'request_log' ) . ' where micro_timestamp_stop < micro_timestamp_start and ip = "' . esc_sql( $ip ) . '"' );
1248 }
1249
1250 public function getLastRequestTimestamp( $ip = null ) {
1251 if ( $ip == null ) {
1252 return $this->wpdb->get_var( 'select micro_timestamp_start from ' . $this->tableName( 'request_log' ) . ' order by micro_timestamp_start desc limit 1' );
1253 }
1254
1255 return $this->wpdb->get_var( 'SELECT micro_timestamp_start FROM ' . $this->tableName( 'request_log' ) . ' WHERE ip = "' . esc_sql( $ip ) . '" order by micro_timestamp_start desc limit 1' );
1256 }
1257
1258 public function addWebsite( $userid, $name, $url, $admin, $pubkey, $privkey, $nossl, $nosslkey, $groupids, $groupnames,
1259 $verifyCertificate = 1, $uniqueId = '', $http_user, $http_pass, $sslVersion = 0, $wpe = 0, $isStaging = 0 ) {
1260 if ( MainWP_Utility::ctype_digit( $userid ) && ( $nossl == 0 || $nossl == 1 ) ) {
1261 $values = array(
1262 'userid' => $userid,
1263 'adminname' => $this->escape( $admin ),
1264 'name' => $this->escape( strip_tags( $name ) ), // escape by insert
1265 'url' => $this->escape( $url ),
1266 'pubkey' => $this->escape( $pubkey ),
1267 'privkey' => $this->escape( $privkey ),
1268 'nossl' => $nossl,
1269 'nosslkey' => ( $nosslkey == null ? '' : $this->escape( $nosslkey ) ),
1270 'siteurl' => '',
1271 'ga_id' => '',
1272 'gas_id' => 0,
1273 'offline_checks' => '',
1274 'offline_checks_last' => 0,
1275 'offline_check_result' => 0,
1276 'note' => '',
1277 'statsUpdate' => 0,
1278 'pagerank' => 0,
1279 'indexed' => 0,
1280 'alexia' => 0,
1281 'pagerank_old' => 0,
1282 'indexed_old' => 0,
1283 'alexia_old' => 0,
1284 'directories' => '',
1285 'plugin_upgrades' => '',
1286 'theme_upgrades' => '',
1287 'translation_upgrades' => '',
1288 'securityIssues' => '',
1289 'themes' => '',
1290 'ignored_themes' => '',
1291 'plugins' => '',
1292 'ignored_plugins' => '',
1293 'pages' => '',
1294 'users' => '',
1295 'categories' => '',
1296 'pluginDir' => '',
1297 'automatic_update' => 0,
1298 'backup_before_upgrade' => 2,
1299 'verify_certificate' => intval( $verifyCertificate ),
1300 'ssl_version' => $sslVersion,
1301 'uniqueId' => $uniqueId,
1302 'mainwpdir' => 0,
1303 'http_user' => $http_user,
1304 'http_pass' => $http_pass,
1305 'wpe' => $wpe,
1306 'is_staging' => $isStaging
1307 );
1308
1309 $syncValues = array(
1310 'dtsSync' => 0,
1311 'dtsSyncStart' => 0,
1312 'dtsAutomaticSync' => 0,
1313 'dtsAutomaticSyncStart' => 0,
1314 'totalsize' => 0,
1315 'extauth' => '',
1316 'sync_errors' => '',
1317 );
1318 if ( $this->wpdb->insert( $this->tableName( 'wp' ), $values ) ) {
1319 $websiteid = $this->wpdb->insert_id;
1320 $syncValues[ 'wpid' ] = $websiteid;
1321 $this->wpdb->insert( $this->tableName( 'wp_sync' ), $syncValues );
1322 $this->wpdb->insert( $this->tableName( 'wp_settings_backup' ), array(
1323 'wpid' => $websiteid,
1324 'archiveFormat' => 'global',
1325 ) );
1326
1327 foreach ( $groupnames as $groupname ) {
1328 if ( $this->wpdb->insert( $this->tableName( 'group' ), array(
1329 'userid' => $userid,
1330 'name' => $this->escape( htmlspecialchars( $groupname ) ),
1331 ) )
1332 ) {
1333 $groupids[] = $this->wpdb->insert_id;
1334 }
1335 }
1336 //add groupids
1337 foreach ( $groupids as $groupid ) {
1338 $this->wpdb->insert( $this->tableName( 'wp_group' ), array(
1339 'wpid' => $websiteid,
1340 'groupid' => $groupid,
1341 ) );
1342 }
1343
1344 return $websiteid;
1345 }
1346 }
1347
1348 return false;
1349 }
1350
1351 public function updateGroupSite( $groupId, $websiteId ) {
1352 $this->wpdb->insert( $this->tableName( 'wp_group' ), array( 'wpid' => $websiteId, 'groupid' => $groupId ) );
1353 }
1354
1355 public function clearGroup( $groupId ) {
1356 $this->wpdb->query( 'DELETE FROM ' . $this->tableName( 'wp_group' ) . ' WHERE groupid=' . $groupId );
1357 }
1358
1359 public function addGroup( $userid, $name ) {
1360 if ( MainWP_Utility::ctype_digit( $userid ) ) {
1361 if ( $this->wpdb->insert( $this->tableName( 'group' ), array(
1362 'userid' => $userid,
1363 'name' => $this->escape( $name ),
1364 ) )
1365 ) {
1366 return $this->wpdb->insert_id;
1367 }
1368 }
1369
1370 return false;
1371 }
1372
1373 public function removeWebsite( $websiteid ) {
1374 if ( MainWP_Utility::ctype_digit( $websiteid ) ) {
1375 $nr = $this->wpdb->query( 'DELETE FROM ' . $this->tableName( 'wp' ) . ' WHERE id=' . $websiteid );
1376 $this->wpdb->query( 'DELETE FROM ' . $this->tableName( 'wp_group' ) . ' WHERE wpid=' . $websiteid );
1377 $this->wpdb->query( 'DELETE FROM ' . $this->tableName( 'wp_sync' ) . ' WHERE wpid=' . $websiteid );
1378 $this->wpdb->query( 'DELETE FROM ' . $this->tableName( 'wp_options' ) . ' WHERE wpid=' . $websiteid );
1379
1380 return $nr;
1381 }
1382
1383 return false;
1384 }
1385
1386 public function removeGroup( $groupid ) {
1387 if ( MainWP_Utility::ctype_digit( $groupid ) ) {
1388 $nr = $this->wpdb->query( 'DELETE FROM ' . $this->tableName( 'group' ) . ' WHERE id=' . $groupid );
1389 $this->wpdb->query( 'DELETE FROM ' . $this->tableName( 'wp_group' ) . ' WHERE groupid=' . $groupid );
1390
1391 return $nr;
1392 }
1393
1394 return false;
1395 }
1396
1397 public function updateNote( $websiteid, $note ) {
1398 $this->wpdb->query( 'UPDATE ' . $this->tableName( 'wp' ) . ' SET note="' . $this->escape( $note ) . '", note_lastupdate = ' . time() . ' WHERE id=' . $websiteid );
1399 }
1400
1401 public function updateWebsiteOfflineCheckSetting( $websiteid, $offlineChecks ) {
1402 return $this->wpdb->query( 'UPDATE ' . $this->tableName( 'wp' ) . ' SET offline_checks="' . $this->escape( $offlineChecks ) . '" WHERE id=' . $websiteid, OBJECT );
1403 }
1404
1405 public function updateWebsiteValues( $websiteid, $fields ) {
1406 if ( count( $fields ) > 0 ) {
1407 return $this->wpdb->update( $this->tableName( 'wp' ), $fields, array( 'id' => $websiteid ) );
1408 }
1409
1410 return false;
1411 }
1412
1413 public function updateWebsiteSyncValues( $websiteid, $fields ) {
1414 if ( count( $fields ) > 0 ) {
1415 return $this->wpdb->update( $this->tableName( 'wp_sync' ), $fields, array( 'wpid' => $websiteid ) );
1416 }
1417
1418 return false;
1419 }
1420
1421 public function updateWebsite( $websiteid, $url, $userid, $name, $siteadmin, $groupids, $groupnames, $offlineChecks,
1422 $pluginDir, $maximumFileDescriptorsOverride, $maximumFileDescriptorsAuto, $maximumFileDescriptors,
1423 $verifyCertificate = 1, $archiveFormat, $uniqueId = '', $http_user = null, $http_pass = null, $sslVersion = 0,
1424 $wpe = 0 ) {
1425 if ( MainWP_Utility::ctype_digit( $websiteid ) && MainWP_Utility::ctype_digit( $userid ) ) {
1426 $website = MainWP_DB::Instance()->getWebsiteById( $websiteid );
1427 if ( MainWP_Utility::can_edit_website( $website ) ) {
1428 //update admin
1429 $this->wpdb->query( 'UPDATE ' . $this->tableName( 'wp' ) . ' SET url="' . $this->escape( $url ) . '", name="' . $this->escape( strip_tags($name) ) . '", adminname="' . $this->escape( $siteadmin ) . '",offline_checks="' . $this->escape( $offlineChecks ) . '",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 ) . '" WHERE id=' . $websiteid );
1430 $this->wpdb->query( 'UPDATE ' . $this->tableName( 'wp_settings_backup' ) . ' SET archiveFormat = "' . $this->escape( $archiveFormat ) . '" WHERE wpid=' . $websiteid );
1431 //remove groups
1432 $this->wpdb->query( 'DELETE FROM ' . $this->tableName( 'wp_group' ) . ' WHERE wpid=' . $websiteid );
1433 //Remove GA stats
1434 $showErrors = $this->wpdb->hide_errors();
1435 do_action( 'mainwp_ga_delete_site', $websiteid );
1436 if ( $showErrors ) {
1437 $this->wpdb->show_errors();
1438 }
1439 //add groups with groupnames
1440 foreach ( $groupnames as $groupname ) {
1441 if ( $this->wpdb->insert( $this->tableName( 'group' ), array(
1442 'userid' => $userid,
1443 'name' => $this->escape( $groupname ),
1444 ) )
1445 ) {
1446 $groupids[] = $this->wpdb->insert_id;
1447 }
1448 }
1449 //add groupids
1450 foreach ( $groupids as $groupid ) {
1451 $this->wpdb->insert( $this->tableName( 'wp_group' ), array(
1452 'wpid' => $websiteid,
1453 'groupid' => $groupid,
1454 ) );
1455 }
1456
1457 return true;
1458 }
1459 }
1460
1461 return false;
1462 }
1463
1464 public function updateGroup( $groupid, $groupname ) {
1465 if ( MainWP_Utility::ctype_digit( $groupid ) ) {
1466 //update groupname
1467 $this->wpdb->query( 'UPDATE ' . $this->tableName( 'group' ) . ' SET name="' . $this->escape( $groupname ) . '" WHERE id=' . $groupid );
1468
1469 return true;
1470 }
1471
1472 return false;
1473 }
1474
1475 public function updateBackupTaskProgress( $task_id, $wp_id, $values ) {
1476 $this->wpdb->update( $this->tableName( 'wp_backup_progress' ), $values, array(
1477 'task_id' => $task_id,
1478 'wp_id' => $wp_id,
1479 ) );
1480
1481 return $this->getBackupTaskProgress( $task_id, $wp_id );
1482 }
1483
1484 public function addBackupTaskProgress( $task_id, $wp_id, $information ) {
1485 $values = array(
1486 'task_id' => $task_id,
1487 'wp_id' => $wp_id,
1488 'dtsFetched' => time(),
1489 'fetchResult' => json_encode( $information ),
1490 'removedFiles' => 0,
1491 'downloadedDB' => '',
1492 'downloadedFULL' => '',
1493 );
1494
1495 if ( $this->wpdb->insert( $this->tableName( 'wp_backup_progress' ), $values ) ) {
1496 return $this->getBackupTaskProgress( $task_id, $wp_id );
1497 }
1498
1499 return null;
1500 }
1501
1502 public function getBackupTaskProgress( $task_id, $wp_id ) {
1503 $progress = $this->wpdb->get_row( 'SELECT * FROM ' . $this->tableName( 'wp_backup_progress' ) . ' WHERE task_id= ' . $task_id . ' AND wp_id = ' . $wp_id );
1504
1505 if ( $progress->fetchResult != '' ) {
1506 $progress->fetchResult = json_decode( $progress->fetchResult, true );
1507 }
1508
1509 return $progress;
1510 }
1511
1512 public function backupFullTaskRunning( $wp_id ) {
1513
1514 if (!get_option('mainwp_enableLegacyBackupFeature')) {
1515 return false;
1516 }
1517
1518 $progresses = $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'wp_backup_progress' ) . ' WHERE wp_id = ' . $wp_id . ' AND dtsFetched > ' . (time() - (30 * 60)) );
1519 if ( is_array( $progresses ) ) {
1520 foreach ( $progresses as $progress ) {
1521 if ( ( $progress->downloadedDBComplete == 0 ) && ( $progress->downloadedFULLComplete == 0 ) ) {
1522 if ( $task = $this->getBackupTaskById( $progress->task_id ) ) {
1523 if ( ( 'full' == $task->type ) && !$task->paused ) {
1524 return true;
1525 }
1526 }
1527 }
1528 }
1529 }
1530 return false;
1531 }
1532
1533 public function removeBackupTask( $id ) {
1534 $this->wpdb->query( 'DELETE FROM ' . $this->tableName( 'wp_backup' ) . ' WHERE id = ' . $id );
1535 }
1536
1537 public function getBackupTaskById( $id ) {
1538 return $this->wpdb->get_row( 'SELECT * FROM ' . $this->tableName( 'wp_backup' ) . ' WHERE id= ' . $id );
1539 }
1540
1541 public function getBackupTasksForUser( $orderBy = 'name' ) {
1542 if ( MainWP_System::Instance()->isSingleUser() ) {
1543 return $this->getBackupTasks( null, $orderBy );
1544 }
1545
1546 global $current_user;
1547
1548 return $this->getBackupTasks( $current_user->ID, $orderBy );
1549 }
1550
1551 public function getBackupTasks( $userid = null, $orderBy = null ) {
1552 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'wp_backup' ) . ' WHERE ' . ( $userid == null ? '' : 'userid= ' . $userid . ' AND ' ) . ' template = 0 ' . ( $orderBy != null ? 'ORDER BY ' . $orderBy : '' ), OBJECT );
1553 }
1554
1555 public function addBackupTask( $userid, $name, $schedule, $type, $exclude, $sites, $groups, $subfolder, $filename,
1556 $template, $excludebackup, $excludecache, $excludenonwp, $excludezip, $archiveFormat,
1557 $maximumFileDescriptorsOverride, $maximumFileDescriptorsAuto, $maximumFileDescriptors, $loadFilesBeforeZip ) {
1558 if ( MainWP_Utility::ctype_digit( $userid ) ) {
1559 $values = array(
1560 'userid' => $userid,
1561 'name' => $name,
1562 'schedule' => $schedule,
1563 'type' => $type,
1564 'exclude' => $exclude,
1565 'sites' => $sites,
1566 'groups' => $groups,
1567 'last' => 0,
1568 'last_run' => 0,
1569 'last_run_manually' => 0,
1570 'completed_sites' => '',
1571 'completed' => 0,
1572 'backup_errors' => '',
1573 'subfolder' => MainWP_Utility::removePreSlashSpaces( $subfolder ),
1574 'filename' => $filename,
1575 'paused' => 0,
1576 'template' => $template,
1577 'excludebackup' => $excludebackup,
1578 'excludecache' => $excludecache,
1579 'excludenonwp' => $excludenonwp,
1580 'excludezip' => $excludezip,
1581 'archiveFormat' => $archiveFormat,
1582 'loadFilesBeforeZip' => $loadFilesBeforeZip,
1583 'maximumFileDescriptorsOverride' => $maximumFileDescriptorsOverride,
1584 'maximumFileDescriptorsAuto' => $maximumFileDescriptorsAuto,
1585 'maximumFileDescriptors' => $maximumFileDescriptors,
1586 );
1587
1588 if ( $this->wpdb->insert( $this->tableName( 'wp_backup' ), $values ) ) {
1589 return $this->getBackupTaskById( $this->wpdb->insert_id );
1590 }
1591 }
1592
1593 return false;
1594 }
1595
1596 public function updateBackupTask( $id, $userid, $name, $schedule, $type, $exclude, $sites, $groups, $subfolder,
1597 $filename, $excludebackup, $excludecache, $excludenonwp, $excludezip, $archiveFormat,
1598 $maximumFileDescriptorsOverride, $maximumFileDescriptorsAuto, $maximumFileDescriptors, $loadFilesBeforeZip ) {
1599 if ( MainWP_Utility::ctype_digit( $userid ) && MainWP_Utility::ctype_digit( $id ) ) {
1600 return $this->wpdb->update( $this->tableName( 'wp_backup' ), array(
1601 'userid' => $userid,
1602 'name' => $name,
1603 'schedule' => $schedule,
1604 'type' => $type,
1605 'exclude' => $exclude,
1606 'sites' => $sites,
1607 'groups' => $groups,
1608 'subfolder' => MainWP_Utility::removePreSlashSpaces( $subfolder ),
1609 'filename' => $filename,
1610 'excludebackup' => $excludebackup,
1611 'excludecache' => $excludecache,
1612 'excludenonwp' => $excludenonwp,
1613 'excludezip' => $excludezip,
1614 'archiveFormat' => $archiveFormat,
1615 'loadFilesBeforeZip' => $loadFilesBeforeZip,
1616 'maximumFileDescriptorsOverride' => $maximumFileDescriptorsOverride,
1617 'maximumFileDescriptorsAuto' => $maximumFileDescriptorsAuto,
1618 'maximumFileDescriptors' => $maximumFileDescriptors,
1619 ), array( 'id' => $id ) );
1620 }
1621
1622 return false;
1623 }
1624
1625 public function updateBackupTaskWithValues( $id, $values ) {
1626 if ( !is_array( $values ) ) {
1627 return false;
1628 }
1629
1630 return $this->wpdb->update( $this->tableName( 'wp_backup' ), $values, array( 'id' => $id ) );
1631 }
1632
1633 public function updateBackupRun( $id ) {
1634 if ( MainWP_Utility::ctype_digit( $id ) ) {
1635 return $this->wpdb->update( $this->tableName( 'wp_backup' ), array(
1636 'last_run' => time(),
1637 'last' => time(),
1638 'completed_sites' => json_encode( array() ),
1639 ), array( 'id' => $id ) );
1640 }
1641
1642 return false;
1643 }
1644
1645 public function updateBackupRunManually( $id ) {
1646 if ( MainWP_Utility::ctype_digit( $id ) ) {
1647 return $this->wpdb->update( $this->tableName( 'wp_backup' ), array( 'last_run_manually' => time() ), array( 'id' => $id ) );
1648 }
1649
1650 return false;
1651 }
1652
1653 public function updateBackupCompleted( $id ) {
1654 if ( MainWP_Utility::ctype_digit( $id ) ) {
1655 return $this->wpdb->update( $this->tableName( 'wp_backup' ), array( 'completed' => time() ), array( 'id' => $id ) );
1656 }
1657
1658 return false;
1659 }
1660
1661 public function updateBackupErrors( $id, $errors ) {
1662 if ( MainWP_Utility::ctype_digit( $id ) ) {
1663 if ( $errors == '' ) {
1664 return $this->wpdb->update( $this->tableName( 'wp_backup' ), array( 'backup_errors' => '' ), array( 'id' => $id ) );
1665 } else {
1666 $task = $this->getBackupTaskById( $id );
1667
1668 return $this->wpdb->update( $this->tableName( 'wp_backup' ), array( 'backup_errors' => $task->backup_errors . $errors ), array( 'id' => $id ) );
1669 }
1670 }
1671
1672 return false;
1673 }
1674
1675 public function updateCompletedSites( $id, $completedSites ) {
1676 if ( MainWP_Utility::ctype_digit( $id ) ) {
1677 return $this->wpdb->update( $this->tableName( 'wp_backup' ), array( 'completed_sites' => json_encode( $completedSites ) ), array( 'id' => $id ) );
1678 }
1679
1680 return false;
1681 }
1682
1683 public function getOfflineChecks() {
1684 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'wp' ) . ' WHERE (offline_checks="hourly" AND ' . time() . ' - offline_checks_last >= ' . ( 60 * 60 * 1 ) . ') OR (offline_checks="2xday" AND ' . time() . ' - offline_checks_last >= ' . ( 60 * 60 * 12 * 1 ) . ') OR (offline_checks="daily" AND ' . time() . ' - offline_checks_last >= ' . ( 60 * 60 * 24 * 1 ) . ') OR (offline_checks="weekly" AND ' . time() . ' - offline_checks_last >= ' . ( 60 * 60 * 24 * 7 ) . ')', OBJECT );
1685 }
1686
1687 public function getWebsitesCheckUpdatesCount() {
1688 $where = $this->getWhereAllowAccessSites( 'wp' );
1689
1690 return $this->wpdb->get_var( 'SELECT count(wp.id) FROM ' . $this->tableName( 'wp' ) . ' wp
1691 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1692 WHERE (wp_sync.dtsAutomaticSyncStart = 0 OR DATE(FROM_UNIXTIME(wp_sync.dtsAutomaticSyncStart)) <> DATE(NOW())) ' . $where );
1693 }
1694
1695 public function getWebsitesCountWhereDtsAutomaticSyncSmallerThenStart() {
1696 $where = $this->getWhereAllowAccessSites( 'wp' );
1697
1698 //once a day
1699 return $this->wpdb->get_var( 'SELECT count(wp.id) FROM ' . $this->tableName( 'wp' ) . ' wp
1700 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1701 WHERE ((wp_sync.dtsAutomaticSync < wp_sync.dtsAutomaticSyncStart) OR (wp_sync.dtsAutomaticSyncStart = 0) OR (DATE(FROM_UNIXTIME(wp_sync.dtsAutomaticSyncStart)) <> DATE(NOW()))) ' . $where );
1702 }
1703
1704 public function getWebsitesLastAutomaticSync() {
1705 //once a day
1706 return $this->wpdb->get_var( 'SELECT MAX(wp_sync.dtsAutomaticSync) FROM ' . $this->tableName( 'wp' ) . ' wp
1707 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid' );
1708 }
1709
1710 public function getWebsitesCheckUpdates( $limit ) {
1711 $where = $this->getWhereAllowAccessSites( 'wp' );
1712
1713 //once a day
1714 return $this->wpdb->get_results( 'SELECT wp.*,wp_sync.*,wp_optionview.*
1715 FROM ' . $this->tableName( 'wp' ) . ' wp
1716 JOIN ' . $this->tableName( 'wp_sync' ) . ' wp_sync ON wp.id = wp_sync.wpid
1717 JOIN ' . $this->getOptionView() . ' wp_optionview ON wp.id = wp_optionview.wpid
1718 WHERE (wp_sync.dtsAutomaticSyncStart = 0 OR DATE(FROM_UNIXTIME(wp_sync.dtsAutomaticSyncStart)) <> DATE(NOW())) ' . $where . ' LIMIT 0,' . $limit, OBJECT );
1719 }
1720
1721 public function getWebsitesStatsUpdateSQL() {
1722 $where = $this->getWhereAllowAccessSites();
1723
1724 //once a week
1725 return 'SELECT * FROM ' . $this->tableName( 'wp' ) . ' WHERE (statsUpdate = 0 OR ' . time() . ' - statsUpdate >= ' . ( 60 * 60 * 24 * 7 ) . ')' . $where . ' ORDER BY statsUpdate ASC';
1726 }
1727
1728 public function updateWebsiteStats( $websiteid, $statsUpdated ) {
1729 return $this->wpdb->update( $this->tableName( 'wp' ), array(
1730 'statsUpdate' => $statsUpdated,
1731 ), array( 'id' => $websiteid ) );
1732 }
1733
1734 public function getBackupTasksToComplete() {
1735 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'wp_backup' ) . ' WHERE paused = 0 AND completed < last_run'//AND '. time() . ' - last_run >= 120 AND ' . time() . ' - last >= 120'
1736 , OBJECT );
1737 }
1738
1739 public function getBackupTasksTodoDaily() {
1740 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'wp_backup' ) . ' WHERE paused = 0 AND schedule="daily" AND ' . time() . ' - last_run >= ' . ( 60 * 60 * 24 ), OBJECT );
1741 }
1742
1743 public function getBackupTasksTodoWeekly() {
1744 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'wp_backup' ) . ' WHERE paused = 0 AND schedule="weekly" AND ' . time() . ' - last_run >= ' . ( 60 * 60 * 24 * 7 ), OBJECT );
1745 }
1746
1747 public function getBackupTasksTodoMonthly() {
1748 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'wp_backup' ) . ' WHERE paused = 0 AND schedule="monthly" AND ' . time() . ' - last_run >= ' . ( 60 * 60 * 24 * 30 ), OBJECT );
1749 }
1750
1751 public function getUserNotificationEmail( $userid ) {
1752 $theUserId = $userid;
1753 if ( MainWP_System::Instance()->isSingleUser() ) {
1754 $theUserId = 0;
1755 }
1756 $user_email = $this->wpdb->get_var( 'SELECT user_email FROM ' . $this->tableName( 'users' ) . ' WHERE userid = ' . $theUserId );
1757
1758 if ( $user_email == null || $user_email == '' ) {
1759 $user_email = $this->wpdb->get_var( 'SELECT user_email FROM ' . $this->wpdb->prefix . 'users WHERE id = ' . $userid );
1760 }
1761
1762 return $user_email;
1763 }
1764
1765 public function getUserExtension() {
1766 global $current_user;
1767
1768 if ( empty( $current_user ) ) {
1769 if ( MainWP_System::Instance()->isSingleUser() ) {
1770 $userid = 0;
1771 } else {
1772 return false;
1773 }
1774 } else {
1775 $userid = $current_user->ID;
1776 }
1777
1778 return $this->getUserExtensionByUserId( $userid );
1779 }
1780
1781 public function getUserExtensionByUserId( $userid ) {
1782 if ( MainWP_System::Instance()->isSingleUser() ) {
1783 $userid = 0;
1784 }
1785
1786 $row = $this->wpdb->get_row( 'SELECT * FROM ' . $this->tableName( 'users' ) . ' WHERE userid= ' . $userid, OBJECT );
1787 if ( $row == null ) {
1788 $this->createUserExtension( $userid );
1789 $row = $this->wpdb->get_row( 'SELECT * FROM ' . $this->tableName( 'users' ) . ' WHERE userid= ' . $userid, OBJECT );
1790 }
1791
1792 return $row;
1793 }
1794
1795 protected function createUserExtension( $userId ) {
1796 $fields = array(
1797 'userid' => $userId,
1798 'user_email' => '',
1799 'ignored_plugins' => '',
1800 'trusted_plugins' => '',
1801 'trusted_plugins_notes' => '',
1802 'ignored_themes' => '',
1803 'trusted_themes' => '',
1804 'trusted_themes_notes' => '',
1805 'pluginDir' => ''
1806 );
1807
1808 $this->wpdb->insert( $this->tableName( 'users' ), $fields );
1809 }
1810
1811 public function updateUserExtension( $userExtension ) {
1812
1813 if (is_object($userExtension)) {
1814 $userid = $userExtension->userid;
1815 } else if (is_array($userExtension)) {
1816 $userid = $userExtension['userid']; // fix for some
1817 } else {
1818 $userid = null;
1819 }
1820
1821 if ( $userid == null ) {
1822 if ( MainWP_System::Instance()->isSingleUser() ) {
1823 $userid = '0';
1824 } else {
1825 global $current_user;
1826 $userid = $current_user->ID;
1827 }
1828 }
1829 $row = $this->wpdb->get_row( 'SELECT * FROM ' . $this->tableName( 'users' ) . ' WHERE userid= ' . $userid, OBJECT );
1830 if ( $row == null ) {
1831 $this->createUserExtension( $userid );
1832 }
1833
1834 $fields = array();
1835 foreach ( $userExtension as $field => $value ) {
1836 if ( $value != $row->$field ) {
1837 $fields[ $field ] = $value;
1838 }
1839 }
1840
1841 if ( count( $fields ) > 0 ) {
1842 $this->wpdb->update( $this->tableName( 'users' ), $fields, array( 'userid' => $userid ) );
1843 }
1844
1845 $row = $this->wpdb->get_row( 'SELECT * FROM ' . $this->tableName( 'users' ) . ' WHERE userid= ' . $userid, OBJECT );
1846
1847 return $row;
1848 }
1849
1850 public function getTips() {
1851 return $this->wpdb->get_results( 'SELECT * FROM ' . $this->tableName( 'tips' ) . ' ORDER BY seq ASC', OBJECT );
1852 }
1853
1854 public function addTip( $tip_seq, $tip_content ) {
1855 return $this->wpdb->insert( $this->tableName( 'tips' ), array( 'seq' => $tip_seq, 'content' => $tip_content ) );
1856 }
1857
1858 public function updateTip( $tip_id, $tip_seq, $tip_content ) {
1859 return $this->wpdb->update( $this->tableName( 'tips' ), array(
1860 'seq' => $tip_seq,
1861 'content' => $tip_content,
1862 ), array( 'id' => $tip_id ) );
1863 }
1864
1865 public function deleteTip( $tip_id ) {
1866 return $this->wpdb->query( 'DELETE FROM ' . $this->tableName( 'tips' ) . ' WHERE id = ' . $tip_id );
1867 }
1868
1869 public function getMySQLVersion() {
1870 return $this->wpdb->get_var( 'SHOW VARIABLES LIKE "version"', 1 );
1871 }
1872
1873 public function getRowResult( $sql ) {
1874 if ( $sql == null ) {
1875 return null;
1876 }
1877
1878 return $this->wpdb->get_row( $sql, OBJECT );
1879 }
1880
1881 public function getResultsResult( $sql ) {
1882 if ( $sql == null ) {
1883 return null;
1884 }
1885
1886 return $this->wpdb->get_results( $sql, OBJECT_K );
1887 }
1888
1889 public function query( $sql ) {
1890 if ( $sql == null ) {
1891 return false;
1892 }
1893
1894 $result = @self::_query( $sql, $this->wpdb->dbh );
1895
1896 if ( !$result || ( @MainWP_DB::num_rows( $result ) == 0 ) ) {
1897 return false;
1898 }
1899
1900 return $result;
1901 }
1902
1903 protected function escape( $data ) {
1904 if ( function_exists( 'esc_sql' ) ) {
1905 return esc_sql( $data );
1906 } else {
1907 return $this->wpdb->escape( $data );
1908 }
1909 }
1910
1911 //Support old & new versions of wordpress (3.9+)
1912 public static function use_mysqli() {
1913 /** @var $this ->wpdb wpdb */
1914 if ( !function_exists( 'mysqli_connect' ) ) {
1915 return false;
1916 }
1917
1918 return ( self::$instance->wpdb->dbh instanceof mysqli );
1919 }
1920
1921 public static function ping( $link ) {
1922 if ( self::use_mysqli() ) {
1923 return mysqli_ping( $link );
1924 } else {
1925 return mysql_ping( $link );
1926 }
1927 }
1928
1929 public static function _query( $query, $link ) {
1930 if ( self::use_mysqli() ) {
1931 return mysqli_query( $link, $query );
1932 } else {
1933 return mysql_query( $query, $link );
1934 }
1935 }
1936
1937 public static function fetch_object( $result ) {
1938 if ( $result === false ) {
1939 return false;
1940 }
1941
1942 if ( self::use_mysqli() ) {
1943 return mysqli_fetch_object( $result );
1944 } else {
1945 return mysql_fetch_object( $result );
1946 }
1947 }
1948
1949 public static function free_result( $result ) {
1950 if ( $result === false ) {
1951 return false;
1952 }
1953
1954 if ( self::use_mysqli() ) {
1955 return mysqli_free_result( $result );
1956 } else {
1957 return mysql_free_result( $result );
1958 }
1959 }
1960
1961 public static function data_seek( $result, $offset ) {
1962 if ( $result === false ) {
1963 return false;
1964 }
1965
1966 if ( self::use_mysqli() ) {
1967 return mysqli_data_seek( $result, $offset );
1968 } else {
1969 return mysql_data_seek( $result, $offset );
1970 }
1971 }
1972
1973 public static function fetch_array( $result, $result_type = null ) {
1974 if ( $result === false ) {
1975 return false;
1976 }
1977
1978 if ( self::use_mysqli() ) {
1979 return mysqli_fetch_array( $result, ( $result_type == null ? MYSQLI_BOTH : $result_type ) );
1980 } else {
1981 return mysql_fetch_array( $result, ( $result_type == null ? MYSQL_BOTH : $result_type ) );
1982 }
1983 }
1984
1985 public static function num_rows( $result ) {
1986 if ( $result === false ) {
1987 return 0;
1988 }
1989
1990 if ( self::use_mysqli() ) {
1991 return mysqli_num_rows( $result );
1992 } else {
1993 return mysql_num_rows( $result );
1994 }
1995 }
1996
1997 public static function is_result( $result ) {
1998 if ( $result === false ) {
1999 return false;
2000 }
2001
2002 if ( self::use_mysqli() ) {
2003 return ( $result instanceof mysqli_result );
2004 } else {
2005 return is_resource( $result );
2006 }
2007 }
2008
2009 }
2010
2011 ?>
2012