← All changes | modules/logs/classes/class-log-query.php +435 -51 5.2 → trunk View file →

		@@ -10,8 +10,13 @@
10	10	use MainWP\Dashboard\MainWP_DB;
11	11	use MainWP\Dashboard\MainWP_DB_Client;
12	12	use MainWP\Dashboard\MainWP_Utility;
13	13
	14	+// Exit if accessed directly.
	15	+if ( ! defined( 'ABSPATH' ) ) {
	16	+ exit;
	17	+}
	18	+
14	19	/**
15	20	* Class - Log_Query
16	21	*/
17	22	class Log_Query {
		@@ -31,24 +36,66 @@
31	36	*/
32	37	public function query( $args ) { //phpcs:ignore -- NOSONAR - complex method.
33	38	global $wpdb;
34	39
35		- $join = '';
36		- $where = '';
	40	+ // To support none mainwp actions.
	41	+ $log_id = isset( $args['log_id'] ) ? intval( $args['log_id'] ) : 0;
	42	+ $site_id = isset( $args['wpid'] ) ? $args['wpid'] : 0; // int or array of int site ids.
	43	+ $where_extra = ''; // compatible.
	44	+ $check_access = isset( $args['check_access'] ) ? $args['check_access'] : true;
	45	+ $view = isset( $args['view'] ) ? sanitize_text_field( $args['view'] ) : '';
	46	+ $optimize_get_dt = isset( $args['optimize'] ) && ! empty( $args['optimize'] ) ? true : false;
	47	+ $opti_with_meta = isset( $args['optimize_with_meta'] ) && ! empty( $args['optimize_with_meta'] ) ? true : false;
	48	+ $with_logs_meta = ! empty( $args['with_all_logs_meta'] );
37	49
	50	+ $join = '';
	51	+ $join_meta = '';
	52	+ $select_view = '';
	53	+
	54	+ $where = '';
	55	+ $search_str = '';
	56	+
	57	+ $count_only = ! empty( $args['count_only'] ) ? true : false;
	58	+ $not_count = ! empty( $args['not_count'] ) ? true : false;
	59	+ $optimize_has_more = ! empty( $args['optimize_has_more'] ) ? true : false;
	60	+
38	61	if ( ! empty( $args['search'] ) ) {
39		- $field = ! empty( $args['search_field'] ) ? $args['search_field'] : 'item';
	62	+ $search_str = MainWP_DB::instance()->escape( $args['search'] );
	63	+ // for searching.
	64	+ if ( ! empty( $search_str ) ) {
	65	+ $search_str = trim( $search_str );
	66	+ // prepare search value for searching.
	67	+ if ( ! empty( $search_str ) ) {
	68	+ $where_search = ' AND ( wp.name LIKE "%' . $search_str . '%" OR lg.action LIKE "%' . $search_str . '%" ';
	69	+ $where_search .= ' OR ( CASE ';
	70	+ $where_search .= " WHEN lg.action = 'sync' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Sync Data', 'mainwp' ) ) . "' ";
	71	+ $where_search .= " WHEN lg.action = 'activate' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Activated', 'mainwp' ) ) . "' ";
	72	+ $where_search .= " WHEN lg.action = 'deactivate' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Deactivated', 'mainwp' ) ) . "' ";
	73	+ $where_search .= " WHEN lg.action = 'install' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Installed', 'mainwp' ) ) . "' ";
	74	+ $where_search .= " WHEN lg.action = 'updated' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Updated', 'mainwp' ) ) . "' ";
	75	+ $where_search .= " WHEN lg.action = 'delete' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Deleted', 'mainwp' ) ) . "' ";
	76	+ $where_search .= " WHEN lg.action = 'suspend' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Suspended', 'mainwp' ) ) . "' ";
	77	+ $where_search .= ' ELSE lg.action ';
	78	+ $where_search .= ' END ) LIKE "%' . $search_str . '%" ';
	79	+ $where_search .= ' OR lg.log_id LIKE "%' . $search_str . '%" OR lg.user_id LIKE "%' . $search_str . '%" ';
	80	+ $where_search .= ' OR lg.item LIKE "%' . $search_str . '%" ';
	81	+ if ( 'events_list' === $view ) {
	82	+ $where_search .= ' OR ( CASE ';
	83	+ $where_search .= " WHEN lg.connector = 'non-mainwp-changes' THEN 'WP Admin' ";
	84	+ $where_search .= " ELSE 'Dashboard' ";
	85	+ $where_search .= ' END ) LIKE "%' . $search_str . '%" ';
	86	+ $where_search .= ' OR lg.user_login LIKE "%' . $search_str . '%" ';
	87	+ }
	88	+ $where_search .= ') ';
	89	+ $where .= $where_search;
40	90
41		- // Sanitize field.
42		- $allowed_fields = array( 'log_id', 'site_id', 'user_id', 'created', 'item', 'connector', 'context', 'action' );
43		- if ( in_array( $field, $allowed_fields, true ) ) {
44		- $where .= $wpdb->prepare( " AND lg.{$field} LIKE %s", "%{$args['search']}%" ); // @codingStandardsIgnoreLine can't prepare column name
	91	+ }
45	92	}
46	93	}
47	94
48		- $array_groups_ids = array();
49		- $array_clients_ids = array();
50		- $array_users_ids = array();
	95	+ if ( isset( $args['dismiss'] ) ) {
	96	+ $where .= ' AND lg.dismiss = ' . ( ! empty( $args['dismiss'] ) ? 1 : 0 ) . ' ';
	97	+ }
51	98
52	99	if ( ! empty( $args['groups_ids'] ) && is_array( $args['groups_ids'] ) ) {
53	100	$array_groups_ids = MainWP_Utility::array_numeric_filter( $args['groups_ids'] );
54	101	if ( ! empty( $array_groups_ids ) ) {
		@@ -86,9 +133,26 @@
86	133	}
87	134	}
88	135	}
89	136
90		- if ( ! empty( $args['user_ids'] ) && is_array( $args['user_ids'] ) ) {
	137	+ $where_users_filter = '';
	138	+ if ( ! empty( $args['usersfilter_sites_ids'] ) && is_array( $args['usersfilter_sites_ids'] ) ) {
	139	+ $usersfilter_sites_ids = $args['usersfilter_sites_ids'];
	140	+ $cond_users = array();
	141	+ foreach ( $usersfilter_sites_ids as $user_filter ) {
	142	+ if ( false !== strpos( $user_filter, '-' ) ) { // new users filter.
	143	+ list( $uid, $sid, $is_dash_user ) = explode( '-', $user_filter );
	144	+ if ( $is_dash_user ) {
	145	+ $cond_users[] = ' lg.user_id = ' . (int) $uid . ' AND lg.connector != "non-mainwp-changes" '; // dashboard user does not need to check site ids.
	146	+ } else {
	147	+ $cond_users[] = ' lg.user_id = ' . (int) $uid . ' AND lg.site_id = ' . (int) $sid . ' AND lg.connector = "non-mainwp-changes" '; // child site user need to check site ids.
	148	+ }
	149	+ }
	150	+ }
	151	+ if ( ! empty( $cond_users ) ) {
	152	+ $where_users_filter = ' AND ( ' . implode( ') OR (', $cond_users ) . ') ';
	153	+ }
	154	+ } elseif ( ! empty( $args['user_ids'] ) && is_array( $args['user_ids'] ) ) { // compatible.
91	155	$array_users_ids = MainWP_Utility::array_numeric_filter( $args['user_ids'] );
92	156	if ( ! empty( $array_users_ids ) ) {
93	157	$where .= " AND lg.user_id IN ('" . implode("','",$array_users_ids) . "') "; // phpcs:ignore -- ok.
94	158	}
		@@ -94,11 +158,56 @@
94	158	}
95	159	}
96	160
97	161	if ( ! empty( $args['timestart'] ) && ! empty( $args['timestop'] ) ) {
98		- $where .= $wpdb->prepare( ' AND `lg`.`created` >= %d AND `lg`.`created` <= %d', $args['timestart'], $args['timestop'] );
	162	+ $timestart_us = (int) $args['timestart'] * 1000000;
	163	+ $timestop_us = (int) $args['timestop'] * 1000000;
	164	+ $where .= $wpdb->prepare( ' AND `lg`.`created` >= %d AND `lg`.`created` <= %d', $timestart_us, $timestop_us );
99	165	}
100	166
	167	+ // available sources conds values: wp-admin-only|dashboard-only|empty.
	168	+ if ( ! empty( $args['sources_conds'] ) ) {
	169	+ if ( 'wp-admin-only' === $args['sources_conds'] ) {
	170	+ $where .= ' AND ( `lg`.`connector` = "non-mainwp-changes" ) ';
	171	+ } elseif ( 'dashboard-only' === $args['sources_conds'] ) {
	172	+ $where .= ' AND `lg`.`connector` != "non-mainwp-changes" ';
	173	+ }
	174	+ }
	175	+
	176	+ if ( ! empty( $args['contexts'] ) ) {
	177	+ $contexts_list = explode( ',', $args['contexts'] );
	178	+ $contexts_list = array_map(
	179	+ function ( $value ) {
	180	+ return MainWP_DB::instance()->escape( $value );
	181	+ },
	182	+ (array) $contexts_list
	183	+ );
	184	+ $contexts_list = array_filter( $contexts_list );
	185	+ if ( ! empty( $contexts_list ) ) {
	186	+ $where .= ' AND lg.context IN ( "' . implode( '","', $contexts_list ) . '" ) ';
	187	+ }
	188	+ }
	189	+
	190	+ if ( ! empty( $args['sites_ids'] ) ) {
	191	+ $where_site_ids = implode( ',', array_filter( array_map( 'intval', (array) $args['sites_ids'] ) ) );
	192	+ if ( ! empty( $where_site_ids ) ) {
	193	+ $where .= ' AND lg.site_id IN ( ' . $where_site_ids . ' ) ';
	194	+ }
	195	+ }
	196	+
	197	+ if ( ! empty( $args['events'] ) ) {
	198	+ $events_list = array_map(
	199	+ function ( $value ) {
	200	+ return MainWP_DB::instance()->escape( $value );
	201	+ },
	202	+ (array) $args['events']
	203	+ );
	204	+ $events_list = array_filter( $events_list );
	205	+ if ( ! empty( $events_list ) ) {
	206	+ $where .= ' AND lg.action IN ( "' . implode( '","', $events_list ) . '" ) ';
	207	+ }
	208	+ }
	209	+
101	210	/**
102	211	* PARSE PAGINATION PARAMS
103	212	*/
104	213	$limits = '';
		@@ -104,86 +213,307 @@
104	213	$limits = '';
105	214	$start = absint( $args['start'] );
106	215	$per_page = absint( $args['records_per_page'] );
107	216
108		- if ( $per_page >= 0 ) {
109		- $limits = "LIMIT {$start}, {$per_page}";
	217	+ if ( $per_page > 0 ) {
	218	+ $per_page_more = $optimize_has_more ? $per_page + 1 : $per_page; // Fetch one extra record to determine whether to show the "More" button.
	219	+ $limits = "LIMIT {$start}, {$per_page_more}";
110	220	}
111	221
112		- $limits_recent_count = '';
113		-
114		- // list recent events.
115		- if ( ! empty( $args['recent_number'] ) ) {
116		- $limits_recent_count = ' LIMIT ' . intval( $args['recent_number'] );
	222	+ // Show the recent records first by default.
	223	+ $order = 'DESC';
	224	+ if ( 'ASC' === strtoupper( $args['order'] ) ) {
	225	+ $order = 'ASC';
117	226	}
118	227
119	228	/**
120	229	* PARSE ORDER PARAMS
121	230	*/
122		- $orderable = array( 'site_id', 'name', 'url', 'user_id', 'item', 'created', 'connector', 'context', 'action', 'duration', 'state' );
	231	+ $orderable = array( 'site_id', 'name', 'url', 'user_id', 'item', 'created', 'connector', 'context', 'action', 'event', 'duration', 'state' );
123	232
124		- // Default to sorting by record ID.
125		- $orderby = 'lg.log_id';
	233	+ // Default to sorting by.
	234	+ $orderby = 'lg.created';
126	235
127	236	if ( in_array( $args['orderby'], $orderable, true ) ) {
128	237	if ( in_array( $args['orderby'], array( 'name', 'url' ) ) ) {
129		- $orderby = sprintf( '%s.%s', 'meta_view', $args['orderby'] );
	238	+ $orderby = sprintf( '%s', $args['orderby'] );
	239	+ } elseif ( 'event' === $args['orderby'] || 'action' === $args['orderby'] ) {
	240	+ $orderby = sprintf( '%s.%s', 'lg', 'action' );
130	241	} else {
131	242	$orderby = sprintf( '%s.%s', 'lg', $args['orderby'] );
132	243	}
133	244	}
134	245
135		- // Show the recent records first by default.
136		- $order = 'DESC';
137		- if ( 'ASC' === strtoupper( $args['order'] ) ) {
138		- $order = 'ASC';
	246	+ if ( 'source' === $args['orderby'] ) {
	247	+ $orderby = " ORDER BY
	248	+ CASE
	249	+ WHEN connector = 'non-mainwp-changes' THEN 2
	250	+ ELSE 1
	251	+ END " . $order;
	252	+ } elseif ( 'log_object' === $args['orderby'] ) {
	253	+ $orderby = sprintf( 'ORDER BY %s %s', $orderby, $order );
	254	+ } else {
	255	+ $orderby = sprintf( 'ORDER BY %s %s', $orderby, $order );
139	256	}
140	257
141		- $orderby = sprintf( 'ORDER BY %s %s', $orderby, $order );
	258	+ $where_actions = '';
142	259
	260	+ if ( ! empty( $log_id ) ) {
	261	+ $where_actions .= ' AND lg.log_id = ' . $log_id;
	262	+ } else {
	263	+ $sql_and = '';
	264	+ if ( ! empty( $site_id ) ) {
	265	+ if ( is_array( $site_id ) ) {
	266	+ $site_ids = array_map( 'intval', $site_id );
	267	+ $site_ids = array_filter( $site_ids );
	268	+ if ( ! empty( $site_ids ) ) {
	269	+ $site_ids = implode( ',', $site_ids );
	270	+ $sql_and = ' AND ';
	271	+ $where_actions .= $sql_and . ' lg.site_id IN ( ' . $site_ids . ' )';
	272	+ }
	273	+ } elseif ( is_numeric( $site_id ) ) {
	274	+ $sql_and = ' AND ';
	275	+ $where_actions .= $sql_and . ' lg.site_id = ' . intval( $site_id );
	276	+ }
	277	+ }
	278	+ }
	279	+
	280	+ $where .= $where_actions . $where_extra;
	281	+
143	282	/**
144	283	* PARSE FIELDS PARAMETER
145	284	*/
146	285	$selects = array();
147	286	$selects[] = 'lg.*';
148		- $selects[] = 'meta_view.*';
149		- $select = implode( ', ', $selects );
150	287
151		- $join = ' LEFT JOIN ' . $this->get_log_meta_view() . ' meta_view ON lg.log_id = meta_view.view_log_id ';
	288	+ if ( 'api-view' !== $view ) {
	289	+ $selects[] = 'wp.url as url';
	290	+ $selects[] = 'wp.name as log_site_name';
	291	+ }
152	292
	293	+ if ( 'api-view' !== $view ) {
	294	+ $join = ' INNER JOIN ' . $wpdb->mainwp_tbl_wp . ' wp ON lg.site_id = wp.id ';
	295	+ // Improve query.
	296	+ if ( $check_access && 'api-view' !== $view ) {
	297	+ $join .= MainWP_DB::instance()->get_sql_where_allow_access_sites( 'wp' ); // Example: AND wp.is_staging = 0 or empty.
	298	+ }
	299	+ }
	300	+
	301	+ $mt_params = array();
	302	+
	303	+ $optimize_get_meta = false;
	304	+ if ( ! $optimize_get_dt ) {
	305	+ $join_meta .= ' LEFT JOIN ' . $this->get_log_meta_view( $mt_params ) . ' meta_view ON lg.log_id = meta_view.log_id ';
	306	+ } elseif ( $opti_with_meta ) {
	307	+ $optimize_get_meta = true;
	308	+ }
	309	+
	310	+ if ( 'events_list' === $view ) {
	311	+ $select_view .= $this->select_fields_view();
	312	+ }
	313	+
	314	+ $recent_where = '';
	315	+ $recent_query = '';
	316	+ // list recent events.
	317	+ if ( ! empty( $args['recent_number'] ) ) {
	318	+ $recent_limits = ' LIMIT ' . intval( $args['recent_number'] );
	319	+
	320	+ $recent_query = "SELECT MAX( lg.created )
	321	+ FROM $wpdb->mainwp_tbl_logs as lg
	322	+ {$join}
	323	+ {$join_meta}
	324	+ WHERE `lg`.`connector` != 'compact' ORDER BY lg.created DESC {$recent_limits}";
	325	+
	326	+ $recent_created = $wpdb->get_var( $recent_query ); //phpcs:ignore -- NOSONAR - ok.
	327	+
	328	+ $recent_where = ' AND lg.created <= ' . (int) $recent_created;
	329	+ }
	330	+
	331	+ if ( ! empty( $recent_where ) ) {
	332	+ $orderby = '';
	333	+ $limits = '';
	334	+ }
	335	+
	336	+ if ( ! empty( $join_meta ) ) {
	337	+ // Improve select.
	338	+ $selects[] = 'meta_view.meta_name';
	339	+ $selects[] = 'meta_view.user_meta_json';
	340	+ $selects[] = 'meta_view.usermeta';
	341	+ $selects[] = 'meta_view.extra_info';
	342	+ $selects[] = 'lg.log_id as view_log_id'; // deprecate compatible.
	343	+ }
	344	+
	345	+ $select = implode( ', ', $selects );
	346	+
153	347	/**
154	348	* BUILD THE FINAL QUERY
155	349	*/
156		- $query = "SELECT {$select}
	350	+ $query = "SELECT {$select}{$select_view}
157	351	FROM $wpdb->mainwp_tbl_logs as lg
158	352	{$join}
159		- WHERE `lg`.`connector` != 'compact' {$where}
	353	+ {$join_meta}
	354	+ WHERE `lg`.`connector` != 'compact' {$where} {$where_users_filter} {$recent_where}
160	355	{$orderby}
161	356	{$limits}";
162	357
163	358	// Build result count query.
164		- $count_query = "SELECT COUNT(*) as found
	359	+ // Join meta, join sub for search conditionals if existed.
	360	+ $count_query = "SELECT COUNT(*)
165	361	FROM $wpdb->mainwp_tbl_logs as lg
166		- {$join}
167		- WHERE `lg`.`connector` != 'compact' {$where}
168		- {$limits_recent_count}";
	362	+ {$join}";
	363	+ if ( ! empty( $search_str ) ) {
	364	+ $count_query .= "{$join_meta}";
	365	+ }
	366	+ $count_query .= " WHERE `lg`.`connector` != 'compact' {$where} {$where_users_filter} {$recent_where} ";
169	367
170		- //phpcs:ignore Squiz.PHP.CommentedOutCode.Found
171		- // error_log( print_r( $args, true ) );//.
	368	+ if ( ! empty( $recent_query ) ) {
	369	+ MainWP_DB::instance()->log_system_query( $args, $recent_query, $this );
	370	+ }
172	371
173		- //phpcs:ignore Squiz.PHP.CommentedOutCode.Found
174		- // error_log( $query );//.
	372	+ if ( ! $count_only ) {
	373	+ MainWP_DB::instance()->log_system_query( $args, $query, $this );
	374	+ }
175	375
176		- //phpcs:ignore Squiz.PHP.CommentedOutCode.Found
177		- // error_log( $count_query );//.
	376	+ if ( $count_only || ! $not_count ) {
	377	+ MainWP_DB::instance()->log_system_query( $args, $count_query, $this );
	378	+ }
178	379
	380	+ // Generate cache key for count query.
	381	+ $cache_key = 'mainwp_logs_count_' . md5( serialize( $args ) ); // NOSONAR - MD5 used for cache key generation only, not cryptographic purposes.
	382	+
	383	+ if ( $count_only ) {
	384	+ $cached_count = wp_cache_get( $cache_key, 'mainwp_logs' );
	385	+ if ( false !== $cached_count ) {
	386	+ return array(
	387	+ 'count' => $cached_count,
	388	+ );
	389	+ }
	390	+
	391	+ $count = absint( $wpdb->get_var( $count_query ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- Count query is built with properly escaped and validated SQL fragments (WHERE clauses use escape(), intval(), and sanitize_text_field(); JOIN and recent_where are static or int-cast).
	392	+ wp_cache_set( $cache_key, $count, 'mainwp_logs', HOUR_IN_SECONDS );
	393	+
	394	+ return array(
	395	+ 'count' => $count,
	396	+ );
	397	+ }
	398	+
	399	+ $items = $wpdb->get_results( $query ); // phpcs:ignore -- ok.
	400	+
	401	+ $count_har_more = 0;
	402	+ if ( $optimize_has_more ) {
	403	+ $count_har_more = $items && is_array( $items ) ? count( $items ) : 0; // to support "More" button.
	404	+ if ( $count_har_more > $per_page ) {
	405	+ array_pop( $items ); // Do not show the last item.
	406	+ }
	407	+ }
	408	+
	409	+ if ( ( ( $optimize_get_dt && $optimize_get_meta ) || $with_logs_meta ) && $items ) {
	410	+
	411	+ $ids = array_map( 'absint', wp_list_pluck( $items, 'log_id' ) );
	412	+
	413	+ $start_slice = 0;
	414	+ $max_slice = 100;
	415	+ $count = count( $ids );
	416	+
	417	+ while ( $start_slice <= $count ) {
	418	+ $slice_ids = array_slice( $ids, $start_slice, $max_slice );
	419	+ $start_slice += $max_slice;
	420	+
	421	+ if ( ! empty( $slice_ids ) ) {
	422	+
	423	+ $sql_meta = sprintf(
	424	+ "SELECT * FROM $wpdb->mainwp_tbl_logs_meta WHERE meta_log_id IN ( %s )",
	425	+ implode( ',', $slice_ids )
	426	+ );
	427	+
	428	+ $meta_records = $wpdb->get_results( $sql_meta ); //phpcs:ignore -- ok.
	429	+ $ids_flip = array_flip( $ids );
	430	+
	431	+ if ( is_array( $meta_records ) ) {
	432	+ foreach ( $meta_records as $meta_record ) {
	433	+ if ( ! empty( $meta_record->meta_value ) ) {
	434	+ // compatible format.
	435	+ if ( in_array( $meta_record->meta_key, array( 'user_meta_json', 'user_login', 'extra_info' ) ) ) {
	436	+ $items[ $ids_flip[ $meta_record->meta_log_id ] ]->{$meta_record->meta_key} = $meta_record->meta_value;
	437	+ } else {
	438	+ if ( empty( $items[ $ids_flip[ $meta_record->meta_log_id ] ]->meta ) ) {
	439	+ $items[ $ids_flip[ $meta_record->meta_log_id ] ]->meta = array();
	440	+ }
	441	+ $items[ $ids_flip[ $meta_record->meta_log_id ] ]->meta[ $meta_record->meta_key ] = $meta_record->meta_value;
	442	+ }
	443	+ }
	444	+ }
	445	+ }
	446	+ }
	447	+ }
	448	+ }
	449	+
	450	+ $sites_opts = array();
	451	+ // get sites meta data.
	452	+ if ( $items ) {
	453	+ $ids = array_map( 'absint', wp_list_pluck( $items, 'site_id' ) );
	454	+
	455	+ $start_slice = 0;
	456	+ $max_slice = 100;
	457	+ $count = count( $ids );
	458	+
	459	+ while ( $start_slice <= $count ) {
	460	+ $slice_ids = array_slice( $ids, $start_slice, $max_slice );
	461	+ $start_slice += $max_slice;
	462	+
	463	+ if ( ! empty( $slice_ids ) ) {
	464	+
	465	+ $wp_opts = $with_logs_meta ? array( 'site_info', 'cust_site_icon_info', 'favi_icon' ) : array( 'site_info' );
	466	+
	467	+ $opts_records = Log_DB_Helper::instance()->get_sites_options( $slice_ids, $wp_opts );
	468	+
	469	+ if ( is_array( $opts_records ) ) {
	470	+ foreach ( $opts_records as $opt_record ) {
	471	+ if ( ! isset( $sites_opts[ $opt_record->wpid ] ) ) {
	472	+ $sites_opts[ $opt_record->wpid ] = array();
	473	+ }
	474	+ if ( ! empty( $opt_record->value ) ) {
	475	+ $values = $opt_record->value;
	476	+ if ( 'site_info' === $opt_record->name ) {
	477	+ $values = json_decode( $values, true );
	478	+ if ( ! is_array( $values ) ) {
	479	+ $values = array();
	480	+ }
	481	+ }
	482	+ $sites_opts[ $opt_record->wpid ][ $opt_record->name ] = $values;
	483	+ }
	484	+ }
	485	+ }
	486	+ }
	487	+ }
	488	+ }
	489	+
179	490	/**
180	491	* QUERY THE DATABASE FOR RESULTS
181	492	*/
182		- return array(
183		- 'items' => $wpdb->get_results( $query ), // phpcs:ignore -- ok.
184		- 'count' => absint( $wpdb->get_var( $count_query ) ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	493	+ $results = array(
	494	+ 'items' => $items,
	495	+ 'sites_opts' => $sites_opts,
	496	+ 'extra_data' => array(),
185	497	);
	498	+
	499	+ if ( ! $not_count ) {
	500	+ $cached_count = wp_cache_get( $cache_key, 'mainwp_logs' );
	501	+ if ( false !== $cached_count ) {
	502	+ $results['count'] = $cached_count;
	503	+ } else {
	504	+ $count = absint( $wpdb->get_var( $count_query ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- NOSONAR Count query is built with properly escaped and validated SQL fragments (WHERE clauses use escape(), intval(), and sanitize_text_field(); JOIN and recent_where are static or int-cast).
	505	+ wp_cache_set( $cache_key, $count, 'mainwp_logs', HOUR_IN_SECONDS );
	506	+ $results['count'] = $count;
	507	+ }
	508	+ }
	509	+
	510	+ if ( $optimize_has_more ) {
	511	+ $results['count'] = $start + $count_har_more; // to fix offset issue for next loading.
	512	+ $results['extra_data']['has_more'] = $count_har_more > $per_page ? true : false;
	513	+ }
	514	+
	515	+ return $results;
186	516	}
187	517
188	518	/**
189	519	* Get logs meta database table view.
		@@ -191,12 +521,66 @@
191	521	* @return string logs meta view.
192	522	*/
193	523	public function get_log_meta_view() {
194	524	global $wpdb;
195		- $view = '(SELECT intlog.log_id AS view_log_id ';
196		- $view .= ',(SELECT site_name.meta_value FROM ' . $wpdb->mainwp_tbl_logs_meta . ' site_name WHERE site_name.meta_log_id = intlog.log_id AND site_name.meta_key = "site_name" LIMIT 1) AS log_site_name,
197		- (SELECT siteurl.meta_value FROM ' . $wpdb->mainwp_tbl_logs_meta . ' siteurl WHERE siteurl.meta_log_id = intlog.log_id AND siteurl.meta_key = "siteurl" LIMIT 1) AS url,';
198		- $view .= '(SELECT extra_info.meta_value FROM ' . $wpdb->mainwp_tbl_logs_meta . ' extra_info WHERE extra_info.meta_log_id = intlog.log_id AND extra_info.meta_key = "extra_info" LIMIT 1) AS extra_info ';
199		- $view .= ' FROM ' . $wpdb->mainwp_tbl_logs . ' intlog)';
	525	+ // Improve select.
	526	+ return " ( SELECT
	527	+ meta_log_id AS log_id,
	528	+ MAX(CASE WHEN meta_key = 'name' THEN meta_value END) AS meta_name,
	529	+ MAX(CASE WHEN meta_key = 'user_meta_json' THEN meta_value END) AS user_meta_json,
	530	+ MAX(CASE WHEN meta_key = 'user_meta' THEN meta_value END) AS usermeta,
	531	+ MAX(CASE WHEN meta_key = 'extra_info' THEN meta_value END) AS extra_info
	532	+ FROM " . $wpdb->mainwp_tbl_logs_meta . "
	533	+ WHERE meta_key IN ('name', 'user_meta_json', 'user_meta', 'extra_info')
	534	+ GROUP BY meta_log_id ) ";
	535	+ }
	536	+
	537	+ /**
	538	+ * Method get_sub_query to support seaching in events table.
	539	+ * deprecated @since 6.0.
	540	+ *
	541	+ * @return string sub query view.
	542	+ */
	543	+ public function get_sub_query_view() {
	544	+ global $wpdb;
	545	+ $view = ' (SELECT sub_tbl.log_id AS sub_log_id, ';
	546	+ $view .= ' CASE WHEN sub_tbl.connector = "non-mainwp-changes" THEN "WP Admin" ';
	547	+ $view .= ' ELSE "Dashboard" ';
	548	+ $view .= ' END AS source, ';
	549	+ // to support searching on events column.
	550	+ $view .= " CASE
	551	+ WHEN sub_tbl.action = 'sync' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Sync Data', 'mainwp' ) ) . "'
	552	+ WHEN sub_tbl.action = 'activate' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Activated', 'mainwp' ) ) . "'
	553	+ WHEN sub_tbl.action = 'deactivate' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Deactivated', 'mainwp' ) ) . "'
	554	+ WHEN sub_tbl.action = 'install' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Installed', 'mainwp' ) ) . "'
	555	+ WHEN sub_tbl.action = 'updated' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Updated', 'mainwp' ) ) . "'
	556	+ WHEN sub_tbl.action = 'delete' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Deleted', 'mainwp' ) ) . "'
	557	+ WHEN sub_tbl.action = 'suspend' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Suspended', 'mainwp' ) ) . "'
	558	+ ELSE sub_tbl.action
	559	+ END AS action_display ";
	560	+ $view .= ' FROM ' . $wpdb->mainwp_tbl_logs . ' sub_tbl) ';
200	561	return $view;
	562	+ }
	563	+
	564	+ /**
	565	+ * Method select_fields_view to support seaching in events table.
	566	+ *
	567	+ * @return string sub query view.
	568	+ */
	569	+ public function select_fields_view() {
	570	+ return ", CASE
	571	+ WHEN lg.connector = 'non-mainwp-changes'
	572	+ THEN 'WP Admin'
	573	+ ELSE 'Dashboard'
	574	+ END AS source,
	575	+ CASE
	576	+ WHEN lg.action = 'sync' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Sync Data', 'mainwp' ) ) . "'
	577	+ WHEN lg.action = 'activate' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Activated', 'mainwp' ) ) . "'
	578	+ WHEN lg.action = 'deactivate' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Deactivated', 'mainwp' ) ) . "'
	579	+ WHEN lg.action = 'install' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Installed', 'mainwp' ) ) . "'
	580	+ WHEN lg.action = 'updated' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Updated', 'mainwp' ) ) . "'
	581	+ WHEN lg.action = 'delete' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Deleted', 'mainwp' ) ) . "'
	582	+ WHEN lg.action = 'suspend' THEN '" . MainWP_DB::instance()->escape( esc_html__( 'Suspended', 'mainwp' ) ) . "'
	583	+ ELSE lg.action
	584	+ END AS action_display ";
201	585	}
202	586	}