mainwp / class / class-mainwp-db-common.php

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

638 lines 16.5 KB

Raw Download Zip

1	<?php
2	/**
3	* MainWP Database Controller
4	*
5	* This file handles all interactions with the DB.
6	*
7	* @package MainWP/Dashboard
8	*/
9
10	namespace MainWP\Dashboard;
11
12	/**
13	* Class MainWP_DB_Common
14	*
15	* @package MainWP\Dashboard
16	*/
17	class MainWP_DB_Common extends MainWP_DB {
18
19	// phpcs:disable WordPress.DB.RestrictedFunctions, WordPress.DB.PreparedSQL.NotPrepared -- unprepared SQL ok, accessing the database directly to custom database functions.
20
21	/**
22	* Private static variable to hold the single instance of the class.
23	*
24	* @static
25	*
26	* @var mixed Default null
27	*/
28	private static $instance = null;
29
30	/**
31	* Method instance()
32	*
33	* Create public static instance.
34	*
35	* @static
36	* @return MainWP_DB_Common
37	*/
38	public static function instance() {
39	if ( null == self::$instance ) {
40	self::$instance = new self();
41	}
42	return self::$instance;
43	}
44
45	/**
46	* Method get_last_sync_status()
47	*
48	* Get last sync status.
49	*
50	* @param string\|null $userId User ID.
51	*
52	* @return string $return all_synced\|not_synced\|last_sync
53	*/
54	public function get_last_sync_status( $userId = null ) {
55	$sql = $this->get_sql_websites_for_current_user();
56	$websites = $this->query( $sql );
57
58	$return = array(
59	'sync_status' => false,
60	'last_sync' => 0,
61	);
62
63	if ( ! $websites ) {
64	$return['sync_status'] = 'all_synced';
65	return $return;
66	}
67
68	$total_sites = 0;
69	$synced_sites = 0;
70	$last_sync = 0;
71	self::data_seek( $websites, 0 );
72	while ( $websites && ( $website = self::fetch_object( $websites ) ) ) {
73	if ( empty( $website ) \|\| '' !== $website->sync_errors ) {
74	continue;
75	}
76	$total_sites++;
77	if ( 60 * 60 * 24 > time() - $website->dtsSync ) {
78	$synced_sites++;
79	}
80	if ( $last_sync < $website->dtsSync ) {
81	$last_sync = $website->dtsSync;
82	}
83	}
84
85	if ( $total_sites == $synced_sites ) {
86	$return['sync_status'] = 'all_synced';
87	} elseif ( 0 === $synced_sites ) {
88	$return['sync_status'] = 'not_synced';
89	}
90	$return['last_sync'] = $last_sync;
91	return $return;
92	}
93
94	/**
95	* Method get_group_by_name()
96	*
97	* Get group by name.
98	*
99	* @param mixed $name Group name.
100	* @param null $userid user ID.
101	*
102	* @return (object\|null) Database query result for chosen group name or null on failure
103	*/
104	public function get_group_by_name( $name, $userid = null ) {
105	if ( ( null == $userid ) && MainWP_System::instance()->is_multi_user() ) {
106
107	/**
108	* Current user global.
109	*
110	* @global string
111	*/
112	global $current_user;
113
114	$userid = $current_user->ID;
115	}
116	$where = ( null != $userid ) ? ' AND userid=' . $userid : '';
117	$where .= $this->get_sql_where_allow_groups();
118
119	return $this->wpdb->get_row( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'group' ) . ' WHERE 1 ' . $where . ' AND name= %s', $this->escape( $name ) ) );
120	}
121
122	/**
123	* Method get_group_by_id()
124	*
125	* Get group by ID.
126	*
127	* @param mixed $id Group ID.
128	*
129	* @return (object\|null) Database query result for chosen Group ID or null on failure.
130	*/
131	public function get_group_by_id( $id ) {
132	if ( MainWP_Utility::ctype_digit( $id ) ) {
133	return $this->wpdb->get_row( $this->wpdb->prepare( 'SELECT * FROM ' . $this->table_name( 'group' ) . ' WHERE id= %d', $id ) );
134	}
135
136	return null;
137	}
138
139	/**
140	* Method get_groups_for_manage_sites()
141	*
142	* Get groups for mananged sites.
143	*
144	* @return (object\|null) Database query result for Managed Sites Groups or null on failure.
145	*/
146	public function get_groups_for_manage_sites() {
147	$where = ' 1 ';
148	if ( MainWP_System::instance()->is_multi_user() ) {
149
150	/**
151	* Current user global.
152	*
153	* @global string
154	*/
155	global $current_user;
156
157	$where = ' userid = ' . $current_user->ID . ' ';
158	}
159	$with_staging = 'yes';
160	$staging_enabled = is_plugin_active( 'mainwp-staging-extension/mainwp-staging-extension.php' ) \|\| is_plugin_active( 'mainwp-timecapsule-extension/mainwp-timecapsule-extension.php' );
161
162	if ( ! $staging_enabled ) {
163	$with_staging = 'no';
164	}
165
166	$where .= $this->get_sql_where_allow_groups( '', $with_staging );
167
168	return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'group' ) . ' WHERE ' . $where . ' ORDER BY name', OBJECT_K );
169	}
170
171	/**
172	* Method get_groups_for_current_user()
173	*
174	* Get groups for current user.
175	*
176	* @return (object\|null) Database query result for Current User Groups or null on failure.
177	*/
178	public function get_groups_for_current_user() {
179	$where = ' 1 ';
180	if ( MainWP_System::instance()->is_multi_user() ) {
181
182	/**
183	* Current user global.
184	*
185	* @global string
186	*/
187	global $current_user;
188
189	$where = ' userid = ' . $current_user->ID . ' ';
190	}
191	$where .= $this->get_sql_where_allow_groups();
192
193	return $this->wpdb->get_results( 'SELECT * FROM ' . $this->table_name( 'group' ) . ' WHERE ' . $where . ' ORDER BY name', OBJECT_K );
194	}
195
196	/**
197	* Method get_groups_by_website_id()
198	*
199	* Get groups by website ID.
200	*
201	* @param mixed $websiteid Child Site ID.
202	*
203	* @return (object\|null) Database query result for groups by website ID or null on failure.
204	*/
205	public function get_groups_by_website_id( $websiteid ) {
206	if ( MainWP_Utility::ctype_digit( $websiteid ) ) {
207	return $this->wpdb->get_results(
208	$this->wpdb->prepare(
209	'SELECT * FROM ' . $this->table_name( 'group' ) . ' gr JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON gr.id = wpgr.groupid WHERE wpgr.wpid = %d ORDER BY name',
210	$websiteid
211	),
212	OBJECT_K
213	);
214	}
215
216	return null;
217	}
218
219	/**
220	* Medthod get_groups_and_count()
221	*
222	* Get groups and count.
223	*
224	* @param null $userid Current user ID.
225	* @param bool $for_manager Default: false.
226	*
227	* @return (object\|null) Database query result for groups and count or null on failure.
228	*/
229	public function get_groups_and_count( $userid = null, $for_manager = false ) {
230	if ( ( null == $userid ) && MainWP_System::instance()->is_multi_user() ) {
231
232	/**
233	* Current user global.
234	*
235	* @global string
236	*/
237	global $current_user;
238
239	$userid = $current_user->ID;
240	}
241
242	$where = '';
243
244	if ( null != $userid ) {
245	$where = ' AND gr.userid = ' . $userid;
246	}
247
248	if ( ! $for_manager ) {
249	$where .= $this->get_sql_where_allow_groups( 'gr' );
250	}
251
252	return $this->wpdb->get_results( 'SELECT gr.*, COUNT(DISTINCT(wpgr.wpid)) as nrsites FROM ' . $this->table_name( 'group' ) . ' gr LEFT JOIN ' . $this->table_name( 'wp_group' ) . ' wpgr ON gr.id = wpgr.groupid WHERE 1 ' . $where . ' GROUP BY gr.id ORDER BY gr.name', OBJECT_K );
253	}
254
255	/**
256	* Method get_not_empty_groups()
257	*
258	* Get non-empty groups.
259	*
260	* @param null $userid Current user ID.
261	* @param bool $enableOfflineSites Include offline sites? Default: true.
262	*
263	* @return (object\|null) Database query result for non-empty groups or null on failure.
264	*/
265	public function get_not_empty_groups( $userid = null, $enableOfflineSites = true ) {
266	if ( ( null == $userid ) && MainWP_System::instance()->is_multi_user() ) {
267
268	/**
269	* Current user global.
270	*
271	* @global string
272	*/
273	global $current_user;
274
275	$userid = $current_user->ID;
276	}
277
278	$where = ' WHERE 1 ';
279	$where .= $this->get_sql_where_allow_groups( 'g' );
280
281	if ( null != $userid ) {
282	$where .= ' AND g.userid = ' . $userid;
283	}
284	if ( ! $enableOfflineSites ) {
285	$where .= ' AND wp_sync.sync_errors = ""';
286	}
287
288	return $this->wpdb->get_results( 'SELECT DISTINCT(g.id), g.name, count(wp.wpid) FROM ' . $this->table_name( 'group' ) . ' g JOIN ' . $this->table_name( 'wp_group' ) . ' wp ON g.id = wp.groupid JOIN ' . $this->table_name( 'wp' ) . ' wpsite ON wp.wpid = wpsite.id JOIN ' . $this->table_name( 'wp_sync' ) . ' wp_sync ON wp.wpid = wp_sync.wpid ' . $where . ' GROUP BY g.id HAVING count(wp.wpid) > 0 ORDER BY g.name', OBJECT_K );
289	}
290
291	/**
292	* Method insert_or_update_request_log()
293	*
294	* Insert or update request log.
295	*
296	* @param mixed $wpid WordPress ID.
297	* @param mixed $ip IP address.
298	* @param mixed $start Start time.
299	* @param mixed $stop Stop Time.
300	*
301	* @return void
302	*/
303	public function insert_or_update_request_log( $wpid, $ip, $start, $stop ) {
304	$updateValues = array();
305	if ( null != $ip ) {
306	$updateValues['ip'] = $ip;
307	}
308	if ( null != $start ) {
309	$updateValues['micro_timestamp_start'] = $start;
310	}
311	if ( null != $stop ) {
312	$updateValues['micro_timestamp_stop'] = $stop;
313	}
314
315	$var = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT id FROM ' . $this->table_name( 'request_log' ) . ' WHERE wpid = %d ', $wpid ) );
316	if ( null !== $var ) {
317	$this->wpdb->update( $this->table_name( 'request_log' ), $updateValues, array( 'wpid' => $wpid ) );
318	} else {
319	$updateValues['wpid'] = $wpid;
320	$this->wpdb->insert( $this->table_name( 'request_log' ), $updateValues );
321	}
322	}
323
324	/**
325	* Method close_open_requests()
326	*
327	* Close open request.
328	*
329	* @return void
330	*/
331	public function close_open_requests() {
332	// Close requests open longer then 7 seconds.. something is wrong here.
333	$this->wpdb->query( 'UPDATE ' . $this->table_name( 'request_log' ) . ' SET micro_timestamp_stop = micro_timestamp_start WHERE micro_timestamp_stop < micro_timestamp_start and ' . microtime( true ) . ' - micro_timestamp_start > 7' );
334	}
335
336	/**
337	* Method get_nrof_open_requests()
338	*
339	* Get number of requests.
340	*
341	* @param null $ip IP Address.
342	*
343	* @return (string\|null) Database query result for number of requests or null on failure.
344	*/
345	public function get_nrof_open_requests( $ip = null ) {
346	if ( null == $ip ) {
347	return $this->wpdb->get_var( 'select count(id) from ' . $this->table_name( 'request_log' ) . ' where micro_timestamp_stop < micro_timestamp_start' );
348	}
349
350	return $this->wpdb->get_var( 'select count(id) from ' . $this->table_name( 'request_log' ) . ' where micro_timestamp_stop < micro_timestamp_start and ip = "' . esc_sql( $ip ) . '"' );
351	}
352
353	/**
354	* Method get_last_request_timestamp()
355	*
356	* Get timestamp of last request sent.
357	*
358	* @param null $ip Child Site IP address, default: null.
359	*
360	* @return (int\|null) Database query result for timestamp of last request sent or null on failure.
361	*/
362	public function get_last_request_timestamp( $ip = null ) {
363	if ( null == $ip ) {
364	return $this->wpdb->get_var( 'select micro_timestamp_start from ' . $this->table_name( 'request_log' ) . ' order by micro_timestamp_start desc limit 1' );
365	}
366
367	return $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT micro_timestamp_start FROM ' . $this->table_name( 'request_log' ) . ' WHERE ip = %s order by micro_timestamp_start desc limit 1', esc_sql( $ip ) ) );
368	}
369
370	/**
371	* Method update_group_site()
372	*
373	* @param mixed $groupId Group ID.
374	* @param mixed $websiteId Child Site ID.
375	*
376	* @return void
377	*/
378	public function update_group_site( $groupId, $websiteId ) {
379	$this->wpdb->insert(
380	$this->table_name( 'wp_group' ),
381	array(
382	'wpid' => $websiteId,
383	'groupid' => $groupId,
384	)
385	);
386	}
387
388	/**
389	* Method clear_group()
390	*
391	* Clear sites in group.
392	*
393	* @param mixed $groupId ID of group.
394	*/
395	public function clear_group( $groupId ) {
396	$this->wpdb->query( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE groupid=' . $groupId );
397	}
398
399
400	/**
401	* Method add_group()
402	*
403	* Add group.
404	*
405	* @param mixed $userid Current User ID.
406	* @param mixed $name Name of group to add.
407	*
408	* @return boolean true
409	*/
410	public function add_group( $userid, $name ) {
411	if ( MainWP_Utility::ctype_digit( $userid ) ) {
412	if ( $this->wpdb->insert(
413	$this->table_name( 'group' ),
414	array(
415	'userid' => $userid,
416	'name' => $this->escape( $name ),
417	)
418	)
419	) {
420	return $this->wpdb->insert_id;
421	}
422	}
423
424	return false;
425	}
426
427	/**
428	* Method remove_group()
429	*
430	* Remove group.
431	*
432	* @param mixed $groupid Group ID.
433	*
434	* @return (int\|boolean) Group that was deleted or false on failer.
435	*/
436	public function remove_group( $groupid ) {
437	if ( MainWP_Utility::ctype_digit( $groupid ) ) {
438	$nr = $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'group' ) . ' WHERE id=%d', $groupid ) );
439	$this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $this->table_name( 'wp_group' ) . ' WHERE groupid=%d', $groupid ) );
440
441	return $nr;
442	}
443
444	return false;
445	}
446
447	/**
448	* Method update_note()
449	*
450	* Update Note.
451	*
452	* @param mixed $websiteid Child Site ID.
453	* @param mixed $note Note data.
454	*
455	* @return void
456	*/
457	public function update_note( $websiteid, $note ) {
458	$this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'wp' ) . ' SET note= %s, note_lastupdate = %d WHERE id=%d', $this->escape( $note ), time(), $websiteid ) );
459	}
460
461	/**
462	* Method update_group()
463	*
464	* Update group.
465	*
466	* @param mixed $groupid Group ID.
467	* @param mixed $groupname Group Name.
468	*
469	* @return boolean true\|false.
470	*/
471	public function update_group( $groupid, $groupname ) {
472	if ( MainWP_Utility::ctype_digit( $groupid ) ) {
473	// update groupname.
474	$this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $this->table_name( 'group' ) . ' SET name=%s WHERE id=%d', $this->escape( $groupname ), $groupid ) );
475
476	return true;
477	}
478
479	return false;
480	}
481
482	/**
483	* Method get_user_notification_email()
484	*
485	* Get user notification email.
486	*
487	* @param mixed $userid Current user ID.
488	*
489	* @return string $user_email User email address.
490	*/
491	public function get_user_notification_email( $userid = 0 ) {
492	$theUserId = $userid;
493	if ( MainWP_System::instance()->is_single_user() ) {
494	$theUserId = 0;
495	}
496	$user_email = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT user_email FROM ' . $this->table_name( 'users' ) . ' WHERE userid = %d', $theUserId ) );
497
498	if ( null == $user_email \|\| '' == $user_email ) {
499	$user_email = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT user_email FROM ' . $this->wpdb->prefix . 'users WHERE id = %d', $userid ) );
500	}
501
502	return $user_email;
503	}
504
505	/**
506	* Method get_user_extension()
507	*
508	* Get user extension.
509	*
510	* @return boolean\|int false\|get_user_extension_by_user_id()
511	*/
512	public function get_user_extension() {
513
514	/**
515	* Current user global.
516	*
517	* @global string
518	*/
519	global $current_user;
520
521	if ( empty( $current_user ) ) {
522	if ( MainWP_System::instance()->is_single_user() ) {
523	$userid = 0;
524	} else {
525	return false;
526	}
527	} else {
528	$userid = $current_user->ID;
529	}
530
531	return $this->get_user_extension_by_user_id( $userid );
532	}
533
534	/**
535	* Method get_user_extension_by_user_id()
536	*
537	* Get user extension by user id.
538	*
539	* @param mixed $userid Current user ID.
540	*
541	* @return object $row User extension.
542	*/
543	public function get_user_extension_by_user_id( $userid ) {
544	if ( MainWP_System::instance()->is_single_user() ) {
545	$userid = 0;
546	}
547
548	$row = $this->wpdb->get_row( 'SELECT * FROM ' . $this->table_name( 'users' ) . ' WHERE userid= ' . $userid, OBJECT );
549	if ( null == $row ) {
550	$this->create_user_extension( $userid );
551	$row = $this->wpdb->get_row( 'SELECT * FROM ' . $this->table_name( 'users' ) . ' WHERE userid= ' . $userid, OBJECT );
552	}
553
554	return $row;
555	}
556
557	/**
558	* Method create_user_extension()
559	*
560	* Create user extension
561	*
562	* @param mixed $userId Current user ID.
563	*
564	* @return void
565	*/
566	protected function create_user_extension( $userId ) {
567	$fields = array(
568	'userid' => $userId,
569	'user_email' => '',
570	'ignored_plugins' => '',
571	'trusted_plugins' => '',
572	'trusted_plugins_notes' => '',
573	'ignored_themes' => '',
574	'trusted_themes' => '',
575	'trusted_themes_notes' => '',
576	'pluginDir' => '',
577	);
578
579	$this->wpdb->insert( $this->table_name( 'users' ), $fields );
580	}
581
582	/**
583	* Method update_user_extension()
584	*
585	* Update user extension.
586	*
587	* @param mixed $userExtension User extention to update.
588	*
589	* @return object $row User extension.
590	*/
591	public function update_user_extension( $userExtension ) {
592
593	if ( is_object( $userExtension ) ) {
594	$userid = $userExtension->userid;
595	} elseif ( is_array( $userExtension ) ) {
596	$userid = $userExtension['userid'];
597	} else {
598	$userid = null;
599	}
600
601	if ( null == $userid ) {
602	if ( MainWP_System::instance()->is_single_user() ) {
603	$userid = '0';
604	} else {
605
606	/**
607	* Current user global.
608	*
609	* @global string
610	*/
611	global $current_user;
612
613	$userid = $current_user->ID;
614	}
615	}
616	$row = $this->wpdb->get_row( 'SELECT * FROM ' . $this->table_name( 'users' ) . ' WHERE userid= ' . $userid, OBJECT );
617	if ( null == $row ) {
618	$this->create_user_extension( $userid );
619	}
620
621	$fields = array();
622	foreach ( $userExtension as $field => $value ) {
623	if ( $value != $row->$field ) {
624	$fields[ $field ] = $value;
625	}
626	}
627
628	if ( 0 < count( $fields ) ) {
629	$this->wpdb->update( $this->table_name( 'users' ), $fields, array( 'userid' => $userid ) );
630	}
631
632	$row = $this->wpdb->get_row( 'SELECT * FROM ' . $this->table_name( 'users' ) . ' WHERE userid= ' . $userid, OBJECT );
633
634	return $row;
635	}
636
637	}
638