← All changes | modules/logs/classes/class-log-query.php +551 -100 4.6.0.1 → 6.2.2 View file →

		@@ -7,129 +7,580 @@
7	7
8	8	namespace MainWP\Dashboard\Module\Log;
9	9
10	10	use MainWP\Dashboard\MainWP_DB;
	11	+use MainWP\Dashboard\MainWP_DB_Client;
	12	+use MainWP\Dashboard\MainWP_Utility;
11	13
	14	+// Exit if accessed directly.
	15	+if ( ! defined( 'ABSPATH' ) ) {
	16	+ exit;
	17	+}
	18	+
12	19	/**
13	20	* Class - Log_Query
14	21	*/
15	22	class Log_Query {
16		- /**
17		- * Hold the number of records found
18		- *
19		- * @var int
20		- */
21		- public $found_records = 0;
	23	+ /**
	24	+ * Hold the number of records found
	25	+ *
	26	+ * @var int
	27	+ */
	28	+ public $found_records = 0;
22	29
23		- /**
24		- * Query records
25		- *
26		- * @param array $args Arguments to filter the records by.
27		- *
28		- * @return array Logs Records
29		- */
30		- public function query( $args ) {
31		- global $wpdb;
	30	+ /**
	31	+ * Query records
	32	+ *
	33	+ * @param array $args Arguments to filter the records by.
	34	+ *
	35	+ * @return array Logs Records
	36	+ */
	37	+ public function query( $args ) { //phpcs:ignore -- NOSONAR - complex method.
	38	+ global $wpdb;
32	39
33		- $join = '';
34		- $where = " AND lg.connector != 'compact' ";
	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'] );
35	49
36		- /**
37		- * PARSE CORE PARAMS
38		- */
	50	+ $join = '';
	51	+ $join_meta = '';
	52	+ $select_view = '';
39	53
40		- if ( ! empty( $args['search'] ) ) {
41		- $field = ! empty( $args['search_field'] ) ? $args['search_field'] : 'item';
	54	+ $where = '';
	55	+ $search_str = '';
42	56
43		- // Sanitize field.
44		- $allowed_fields = array( 'log_id', 'site_id', 'user_id', 'created', 'item', 'connector', 'context', 'action' );
45		- if ( in_array( $field, $allowed_fields, true ) ) {
46		- $where .= $wpdb->prepare( " AND lg.{$field} LIKE %s", "%{$args['search']}%" ); // @codingStandardsIgnoreLine can't prepare column name
47		- }
48		- }
	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;
49	60
50		- /**
51		- * PARSE PAGINATION PARAMS
52		- */
53		- $limits = '';
54		- $start = absint( $args['start'] );
55		- $per_page = absint( $args['records_per_page'] );
	61	+ if ( ! empty( $args['search'] ) ) {
	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;
56	90
57		- if ( $per_page >= 0 ) {
58		- $limits = "LIMIT {$start}, {$per_page}";
59		- }
	91	+ }
	92	+ }
	93	+ }
60	94
61		- /**
62		- * PARSE ORDER PARAMS
63		- */
64		- $orderable = array( 'site_id', 'name', 'url', 'user_id', 'item', 'created', 'connector', 'context', 'action', 'duration', 'state' );
	95	+ if ( isset( $args['dismiss'] ) ) {
	96	+ $where .= ' AND lg.dismiss = ' . ( ! empty( $args['dismiss'] ) ? 1 : 0 ) . ' ';
	97	+ }
65	98
66		- // Default to sorting by record ID.
67		- $orderby = 'lg.log_id';
	99	+ if ( ! empty( $args['groups_ids'] ) && is_array( $args['groups_ids'] ) ) {
	100	+ $array_groups_ids = MainWP_Utility::array_numeric_filter( $args['groups_ids'] );
	101	+ if ( ! empty( $array_groups_ids ) ) {
	102	+ $groups_sites = MainWP_DB_Client::instance()->get_websites_by_group_ids( $array_groups_ids );
	103	+ $array_website_ids = array();
	104	+ if ( $groups_sites ) {
	105	+ foreach ( $groups_sites as $website ) {
	106	+ $array_website_ids[] = $website->id;
	107	+ }
	108	+ }
	109	+ unset( $groups_sites );
	110	+ if ( ! empty( $array_website_ids ) ) {
	111	+ $where .= " AND lg.site_id IN ('" . implode( "','", $array_website_ids ) . "') ";
	112	+ } else {
	113	+ $where .= ' AND false ';
	114	+ }
	115	+ }
	116	+ }
68	117
69		- if ( in_array( $args['orderby'], $orderable, true ) ) {
70		- if ( in_array( $args['orderby'], array( 'name', 'url' ) ) ) {
71		- $orderby = sprintf( '%s.%s', 'meta_view', $args['orderby'] );
72		- } else {
73		- $orderby = sprintf( '%s.%s', 'lg', $args['orderby'] );
74		- }
75		- }
	118	+ if ( ! empty( $args['client_ids'] ) && is_array( $args['client_ids'] ) ) {
	119	+ $array_clients_ids = MainWP_Utility::array_numeric_filter( $args['client_ids'] );
	120	+ if ( ! empty( $array_clients_ids ) ) {
	121	+ $client_sites = MainWP_DB_Client::instance()->get_websites_by_client_ids( $array_clients_ids );
	122	+ $array_website_ids = array();
	123	+ if ( $client_sites ) {
	124	+ foreach ( $client_sites as $website ) {
	125	+ $array_website_ids[] = $website->id;
	126	+ }
	127	+ }
	128	+ unset( $client_sites );
	129	+ if ( ! empty( $array_website_ids ) ) {
	130	+ $where .= " AND lg.site_id IN ('" . implode("','",$array_website_ids) . "') "; // phpcs:ignore -- ok.
	131	+ } else {
	132	+ $where .= ' AND false ';
	133	+ }
	134	+ }
	135	+ }
76	136
77		- // Show the recent records first by default.
78		- $order = 'DESC';
79		- if ( 'ASC' === strtoupper( $args['order'] ) ) {
80		- $order = 'ASC';
81		- }
	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.
	155	+ $array_users_ids = MainWP_Utility::array_numeric_filter( $args['user_ids'] );
	156	+ if ( ! empty( $array_users_ids ) ) {
	157	+ $where .= " AND lg.user_id IN ('" . implode("','",$array_users_ids) . "') "; // phpcs:ignore -- ok.
	158	+ }
	159	+ }
82	160
83		- $orderby = sprintf( 'ORDER BY %s %s', $orderby, $order );
	161	+ if ( ! empty( $args['timestart'] ) && ! empty( $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 );
	165	+ }
84	166
85		- /**
86		- * PARSE FIELDS PARAMETER
87		- */
88		- $selects = array();
89		- $selects[] = 'lg.*';
90		- $selects[] = 'meta_view.*';
91		- $select = implode( ', ', $selects );
	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	+ }
92	175
93		- $join = ' LEFT JOIN ' . $this->get_log_meta_view() . ' meta_view ON lg.log_id = meta_view.view_log_id ';
	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	+ }
94	189
95		- /**
96		- * BUILD THE FINAL QUERY
97		- */
98		- $query = "SELECT {$select}
99		- FROM $wpdb->mainwp_tbl_logs as lg
100		- {$join}
101		- WHERE `lg`.`connector` != 'compact' {$where}
102		- {$orderby}
103		- {$limits}";
	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	+ }
104	196
105		- // Build result count query.
106		- $count_query = "SELECT COUNT(*) as found
107		- FROM $wpdb->mainwp_tbl_logs as lg
108		- {$join}
109		- WHERE `lg`.`connector` != 'compact' {$where}";
	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	+ }
110	209
111		- /**
112		- * QUERY THE DATABASE FOR RESULTS
113		- */
114		- $result = array(
115		- 'items' => $wpdb->get_results( $query ), // phpcs:ignore -- ok.
116		- 'count' => absint( $wpdb->get_var( $count_query ) ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
117		- );
	210	+ /**
	211	+ * PARSE PAGINATION PARAMS
	212	+ */
	213	+ $limits = '';
	214	+ $start = absint( $args['start'] );
	215	+ $per_page = absint( $args['records_per_page'] );
118	216
119		- return $result;
120		- }
	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}";
	220	+ }
121	221
122		- /**
123		- * Get logs meta database table view.
124		- *
125		- * @return string logs meta view.
126		- */
127		- public function get_log_meta_view() {
128		- global $wpdb;
129		- $view = '(SELECT intlog.log_id AS view_log_id ';
130		- $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 name,
131		- (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 ';
132		- $view .= ' FROM ' . $wpdb->mainwp_tbl_logs . ' intlog)';
133		- return $view;
134		- }
	222	+ // Show the recent records first by default.
	223	+ $order = 'DESC';
	224	+ if ( 'ASC' === strtoupper( $args['order'] ) ) {
	225	+ $order = 'ASC';
	226	+ }
	227	+
	228	+ /**
	229	+ * PARSE ORDER PARAMS
	230	+ */
	231	+ $orderable = array( 'site_id', 'name', 'url', 'user_id', 'item', 'created', 'connector', 'context', 'action', 'event', 'duration', 'state' );
	232	+
	233	+ // Default to sorting by.
	234	+ $orderby = 'lg.created';
	235	+
	236	+ if ( in_array( $args['orderby'], $orderable, true ) ) {
	237	+ if ( in_array( $args['orderby'], array( 'name', 'url' ) ) ) {
	238	+ $orderby = sprintf( '%s', $args['orderby'] );
	239	+ } elseif ( 'event' === $args['orderby'] || 'action' === $args['orderby'] ) {
	240	+ $orderby = sprintf( '%s.%s', 'lg', 'action' );
	241	+ } else {
	242	+ $orderby = sprintf( '%s.%s', 'lg', $args['orderby'] );
	243	+ }
	244	+ }
	245	+
	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 );
	256	+ }
	257	+
	258	+ $where_actions = '';
	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	+
	282	+ /**
	283	+ * PARSE FIELDS PARAMETER
	284	+ */
	285	+ $selects = array();
	286	+ $selects[] = 'lg.*';
	287	+
	288	+ if ( 'api-view' !== $view ) {
	289	+ $selects[] = 'wp.url as url';
	290	+ $selects[] = 'wp.name as log_site_name';
	291	+ }
	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	+
	347	+ /**
	348	+ * BUILD THE FINAL QUERY
	349	+ */
	350	+ $query = "SELECT {$select}{$select_view}
	351	+ FROM $wpdb->mainwp_tbl_logs as lg
	352	+ {$join}
	353	+ {$join_meta}
	354	+ WHERE `lg`.`connector` != 'compact' {$where} {$where_users_filter} {$recent_where}
	355	+ {$orderby}
	356	+ {$limits}";
	357	+
	358	+ // Build result count query.
	359	+ // Join meta, join sub for search conditionals if existed.
	360	+ $count_query = "SELECT COUNT(*)
	361	+ FROM $wpdb->mainwp_tbl_logs as lg
	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} ";
	367	+
	368	+ if ( ! empty( $recent_query ) ) {
	369	+ MainWP_DB::instance()->log_system_query( $args, $recent_query, $this );
	370	+ }
	371	+
	372	+ if ( ! $count_only ) {
	373	+ MainWP_DB::instance()->log_system_query( $args, $query, $this );
	374	+ }
	375	+
	376	+ if ( $count_only || ! $not_count ) {
	377	+ MainWP_DB::instance()->log_system_query( $args, $count_query, $this );
	378	+ }
	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	+
	490	+ /**
	491	+ * QUERY THE DATABASE FOR RESULTS
	492	+ */
	493	+ $results = array(
	494	+ 'items' => $items,
	495	+ 'sites_opts' => $sites_opts,
	496	+ 'extra_data' => array(),
	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;
	516	+ }
	517	+
	518	+ /**
	519	+ * Get logs meta database table view.
	520	+ *
	521	+ * @return string logs meta view.
	522	+ */
	523	+ public function get_log_meta_view() {
	524	+ global $wpdb;
	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) ';
	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 ";
	585	+ }
135	586	}