| 1 |
<?php |
| 2 |
/** |
| 3 |
* Queries the database for logs records. |
| 4 |
* |
| 5 |
* @package MainWP/Dashboard |
| 6 |
*/ |
| 7 |
|
| 8 |
namespace MainWP\Dashboard\Module\Log; |
| 9 |
|
| 10 |
use MainWP\Dashboard\MainWP_DB; |
| 11 |
use MainWP\Dashboard\MainWP_DB_Client; |
| 12 |
use MainWP\Dashboard\MainWP_Utility; |
| 13 |
|
| 14 |
/** |
| 15 |
* Class - Log_Query |
| 16 |
*/ |
| 17 |
class Log_Query { |
| 18 |
/** |
| 19 |
* Hold the number of records found |
| 20 |
* |
| 21 |
* @var int |
| 22 |
*/ |
| 23 |
public $found_records = 0; |
| 24 |
|
| 25 |
/** |
| 26 |
* Query records |
| 27 |
* |
| 28 |
* @param array $args Arguments to filter the records by. |
| 29 |
* |
| 30 |
* @return array Logs Records |
| 31 |
*/ |
| 32 |
public function query( $args ) { //phpcs:ignore -- NOSONAR - complex method. |
| 33 |
global $wpdb; |
| 34 |
|
| 35 |
// To support none mainwp actions. |
| 36 |
$log_id = isset( $args['log_id'] ) ? intval( $args['log_id'] ) : 0; |
| 37 |
$site_id = isset( $args['wpid'] ) ? $args['wpid'] : 0; // int or array of int site ids. |
| 38 |
$object_id = isset( $args['object_id'] ) ? sanitize_text_field( $args['object_id'] ) : ''; |
| 39 |
$where_extra = ''; // compatible. |
| 40 |
$check_access = isset( $args['check_access'] ) ? $args['check_access'] : true; |
| 41 |
$view = isset( $args['view'] ) ? sanitize_text_field( $args['view'] ) : ''; |
| 42 |
|
| 43 |
$join = ''; |
| 44 |
$where = ''; |
| 45 |
|
| 46 |
$count_only = ! empty( $args['count_only'] ) ? true : false; |
| 47 |
|
| 48 |
if ( ! empty( $args['search'] ) ) { |
| 49 |
$search_str = MainWP_DB::instance()->escape( $args['search'] ); |
| 50 |
// for searching. |
| 51 |
if ( ! empty( $search_str ) ) { |
| 52 |
$search_str = trim( $search_str ); |
| 53 |
$where_search = ' AND ( lg.action LIKE "%' . $search_str . '%" OR lg.log_id LIKE "%' . $search_str . '%" OR lg.user_id LIKE "%' . $search_str . '%" '; |
| 54 |
|
| 55 |
// prepare search value for searching. |
| 56 |
if ( 'plugin' === substr( strtolower( $search_str ), -6 ) ) { |
| 57 |
$tmp_search = substr( $search_str, 0, -6 ); |
| 58 |
} elseif ( 'theme' === substr( strtolower( $search_str ), -5 ) ) { |
| 59 |
$tmp_search = substr( $search_str, 0, -5 ); |
| 60 |
} |
| 61 |
$tmp_search = trim( $tmp_search ); |
| 62 |
if ( ! empty( $tmp_search ) ) { |
| 63 |
$where_search .= ' OR lg.item LIKE "%' . $tmp_search . '%" '; |
| 64 |
} |
| 65 |
|
| 66 |
if ( 'events_list' === $view ) { |
| 67 |
$where_search .= ' OR sub_lg.source LIKE "%' . $search_str . '%" '; |
| 68 |
} |
| 69 |
|
| 70 |
$where_search .= ') '; |
| 71 |
$where .= $where_search; |
| 72 |
} |
| 73 |
} |
| 74 |
|
| 75 |
if ( isset( $args['dismiss'] ) ) { |
| 76 |
$where .= ' AND lg.dismiss = ' . ( ! empty( $args['dismiss'] ) ? 1 : 0 ) . ' '; |
| 77 |
} |
| 78 |
|
| 79 |
if ( ! empty( $args['groups_ids'] ) && is_array( $args['groups_ids'] ) ) { |
| 80 |
$array_groups_ids = MainWP_Utility::array_numeric_filter( $args['groups_ids'] ); |
| 81 |
if ( ! empty( $array_groups_ids ) ) { |
| 82 |
$groups_sites = MainWP_DB_Client::instance()->get_websites_by_group_ids( $array_groups_ids ); |
| 83 |
$array_website_ids = array(); |
| 84 |
if ( $groups_sites ) { |
| 85 |
foreach ( $groups_sites as $website ) { |
| 86 |
$array_website_ids[] = $website->id; |
| 87 |
} |
| 88 |
} |
| 89 |
unset( $groups_sites ); |
| 90 |
if ( ! empty( $array_website_ids ) ) { |
| 91 |
$where .= " AND lg.site_id IN ('" . implode( "','", $array_website_ids ) . "') "; |
| 92 |
} else { |
| 93 |
$where .= ' AND false '; |
| 94 |
} |
| 95 |
} |
| 96 |
} |
| 97 |
|
| 98 |
if ( ! empty( $args['client_ids'] ) && is_array( $args['client_ids'] ) ) { |
| 99 |
$array_clients_ids = MainWP_Utility::array_numeric_filter( $args['client_ids'] ); |
| 100 |
if ( ! empty( $array_clients_ids ) ) { |
| 101 |
$client_sites = MainWP_DB_Client::instance()->get_websites_by_client_ids( $array_clients_ids ); |
| 102 |
$array_website_ids = array(); |
| 103 |
if ( $client_sites ) { |
| 104 |
foreach ( $client_sites as $website ) { |
| 105 |
$array_website_ids[] = $website->id; |
| 106 |
} |
| 107 |
} |
| 108 |
unset( $client_sites ); |
| 109 |
if ( ! empty( $array_website_ids ) ) { |
| 110 |
$where .= " AND lg.site_id IN ('" . implode("','",$array_website_ids) . "') "; // phpcs:ignore -- ok. |
| 111 |
} else { |
| 112 |
$where .= ' AND false '; |
| 113 |
} |
| 114 |
} |
| 115 |
} |
| 116 |
|
| 117 |
if ( ! empty( $args['user_ids'] ) && is_array( $args['user_ids'] ) ) { |
| 118 |
$array_users_ids = MainWP_Utility::array_numeric_filter( $args['user_ids'] ); |
| 119 |
if ( ! empty( $array_users_ids ) ) { |
| 120 |
$where .= " AND lg.user_id IN ('" . implode("','",$array_users_ids) . "') "; // phpcs:ignore -- ok. |
| 121 |
} |
| 122 |
} |
| 123 |
|
| 124 |
if ( ! empty( $args['timestart'] ) && ! empty( $args['timestop'] ) ) { |
| 125 |
$where .= $wpdb->prepare( ' AND `lg`.`created` >= %d AND `lg`.`created` <= %d', $args['timestart'], $args['timestop'] ); |
| 126 |
} |
| 127 |
|
| 128 |
if ( ! empty( $args['sources_conds'] ) ) { |
| 129 |
if ( 'wp-admin-only' === $args['sources_conds'] ) { |
| 130 |
$where .= ' AND `lg`.`connector` = "non-mainwp-changes" '; |
| 131 |
} elseif ( 'dashboard-only' === $args['sources_conds'] ) { |
| 132 |
$where .= ' AND `lg`.`connector` != "non-mainwp-changes" '; |
| 133 |
} |
| 134 |
} |
| 135 |
|
| 136 |
if ( ! empty( $args['sites_ids'] ) ) { |
| 137 |
$where_site_ids = implode( ',', array_filter( array_map( 'intval', (array) $args['sites_ids'] ) ) ); |
| 138 |
if ( ! empty( $where_site_ids ) ) { |
| 139 |
$where .= ' AND lg.site_id IN ( ' . $where_site_ids . ' ) '; |
| 140 |
} |
| 141 |
} |
| 142 |
|
| 143 |
if ( ! empty( $args['events'] ) ) { |
| 144 |
$events_list = array_map( |
| 145 |
function ( $value ) { |
| 146 |
return MainWP_DB::instance()->escape( $value ); |
| 147 |
}, |
| 148 |
(array) $args['events'] |
| 149 |
); |
| 150 |
$events_list = array_filter( $events_list ); |
| 151 |
if ( ! empty( $events_list ) ) { |
| 152 |
$where .= ' AND lg.action IN ( "' . implode( '","', $events_list ) . '" ) '; |
| 153 |
} |
| 154 |
} |
| 155 |
|
| 156 |
/** |
| 157 |
* PARSE PAGINATION PARAMS |
| 158 |
*/ |
| 159 |
$limits = ''; |
| 160 |
$start = absint( $args['start'] ); |
| 161 |
$per_page = absint( $args['records_per_page'] ); |
| 162 |
|
| 163 |
if ( $per_page >= 0 ) { |
| 164 |
$limits = "LIMIT {$start}, {$per_page}"; |
| 165 |
} |
| 166 |
|
| 167 |
// list recent events. |
| 168 |
if ( ! empty( $args['recent_number'] ) ) { |
| 169 |
$limits = ' LIMIT ' . intval( $args['recent_number'] ); |
| 170 |
} |
| 171 |
|
| 172 |
// Show the recent records first by default. |
| 173 |
$order = 'DESC'; |
| 174 |
if ( 'ASC' === strtoupper( $args['order'] ) ) { |
| 175 |
$order = 'ASC'; |
| 176 |
} |
| 177 |
|
| 178 |
/** |
| 179 |
* PARSE ORDER PARAMS |
| 180 |
*/ |
| 181 |
$orderable = array( 'site_id', 'name', 'url', 'user_id', 'item', 'created', 'connector', 'context', 'action', 'event', 'duration', 'state' ); |
| 182 |
|
| 183 |
// Default to sorting by. |
| 184 |
$orderby = 'lg.created'; |
| 185 |
|
| 186 |
if ( in_array( $args['orderby'], $orderable, true ) ) { |
| 187 |
if ( in_array( $args['orderby'], array( 'name', 'url' ) ) ) { |
| 188 |
$orderby = sprintf( '%s.%s', 'meta_view', $args['orderby'] ); |
| 189 |
} elseif ( 'event' === $args['orderby'] ) { |
| 190 |
$orderby = sprintf( '%s.%s', 'lg', 'action' ); |
| 191 |
} else { |
| 192 |
$orderby = sprintf( '%s.%s', 'lg', $args['orderby'] ); |
| 193 |
} |
| 194 |
} |
| 195 |
|
| 196 |
if ( 'source' === $args['orderby'] ) { |
| 197 |
$orderby = " ORDER BY |
| 198 |
CASE |
| 199 |
WHEN connector = 'non-mainwp-changes' THEN 2 |
| 200 |
ELSE 1 |
| 201 |
END " . $order; |
| 202 |
} elseif ( 'log_object' === $args['orderby'] ) { |
| 203 |
$orderby = sprintf( 'ORDER BY %s %s', $orderby, $order ); |
| 204 |
} else { |
| 205 |
$orderby = sprintf( 'ORDER BY %s %s', $orderby, $order ); |
| 206 |
} |
| 207 |
|
| 208 |
$where_actions = ''; |
| 209 |
|
| 210 |
if ( ! empty( $log_id ) ) { |
| 211 |
$where_actions .= ' AND lg.log_id = ' . $log_id; |
| 212 |
} else { |
| 213 |
$sql_and = ''; |
| 214 |
if ( ! empty( $site_id ) ) { |
| 215 |
if ( is_array( $site_id ) ) { |
| 216 |
$site_ids = array_map( 'intval', $site_id ); |
| 217 |
$site_ids = array_filter( $site_ids ); |
| 218 |
if ( ! empty( $site_ids ) ) { |
| 219 |
$site_ids = implode( ',', $site_ids ); |
| 220 |
$sql_and = ' AND '; |
| 221 |
$where_actions .= $sql_and . ' lg.site_id IN ( ' . $site_ids . ' )'; |
| 222 |
} |
| 223 |
} elseif ( is_numeric( $site_id ) ) { |
| 224 |
$sql_and = ' AND '; |
| 225 |
$where_actions .= $sql_and . ' lg.site_id = ' . intval( $site_id ); |
| 226 |
} |
| 227 |
} |
| 228 |
if ( ! empty( $object_id ) ) { |
| 229 |
if ( empty( $sql_and ) ) { |
| 230 |
$sql_and = ' AND '; |
| 231 |
} |
| 232 |
$where_actions .= $sql_and . ' lg.object_id = "' . $object_id . '" '; |
| 233 |
} |
| 234 |
} |
| 235 |
|
| 236 |
if ( $check_access ) { |
| 237 |
$where_actions .= MainWP_DB::instance()->get_sql_where_allow_access_sites( 'wp' ); |
| 238 |
} |
| 239 |
|
| 240 |
$where_dismiss = ! empty( $args['dismiss'] ) ? ' AND dismiss = 1 ' : ' AND dismiss = 0 '; |
| 241 |
|
| 242 |
$where .= $where_actions . $where_extra . $where_dismiss; |
| 243 |
|
| 244 |
if ( ! empty( $args['nonemainwp'] ) ) { |
| 245 |
$where .= ' AND lg.connector = "non-mainwp-changes" '; |
| 246 |
} |
| 247 |
|
| 248 |
/** |
| 249 |
* PARSE FIELDS PARAMETER |
| 250 |
*/ |
| 251 |
$selects = array(); |
| 252 |
$selects[] = 'lg.*'; |
| 253 |
$selects[] = 'wp.url as url'; |
| 254 |
$selects[] = 'wp.name as log_site_name'; |
| 255 |
$selects[] = 'meta_view.*'; |
| 256 |
|
| 257 |
if ( 'events_list' === $view ) { |
| 258 |
$selects[] = 'sub_lg.*'; |
| 259 |
} |
| 260 |
|
| 261 |
$select = implode( ', ', $selects ); |
| 262 |
|
| 263 |
$join = ' LEFT JOIN ' . $wpdb->mainwp_tbl_wp . ' wp ON lg.site_id = wp.id '; |
| 264 |
$join .= ' LEFT JOIN ' . $this->get_log_meta_view() . ' meta_view ON lg.log_id = meta_view.view_log_id '; |
| 265 |
|
| 266 |
if ( 'events_list' === $view ) { |
| 267 |
$join .= ' LEFT JOIN ' . $this->get_sub_query_view() . ' sub_lg ON lg.log_id = sub_lg.sub_log_id '; |
| 268 |
} |
| 269 |
|
| 270 |
/** |
| 271 |
* BUILD THE FINAL QUERY |
| 272 |
*/ |
| 273 |
$query = "SELECT {$select} |
| 274 |
FROM $wpdb->mainwp_tbl_logs as lg |
| 275 |
{$join} |
| 276 |
WHERE `lg`.`connector` != 'compact' {$where} |
| 277 |
{$orderby} |
| 278 |
{$limits}"; |
| 279 |
|
| 280 |
// Build result count query. |
| 281 |
$count_query = "SELECT COUNT(*) |
| 282 |
FROM $wpdb->mainwp_tbl_logs as lg |
| 283 |
{$join} |
| 284 |
WHERE `lg`.`connector` != 'compact' {$where}"; |
| 285 |
|
| 286 |
if ( $count_only ) { |
| 287 |
return array( |
| 288 |
'count' => absint( $wpdb->get_var( $count_query ) ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 289 |
); |
| 290 |
} |
| 291 |
|
| 292 |
if ( ! empty( $args['dev_log_query'] ) ) { |
| 293 |
//phpcs:disable Squiz.PHP.CommentedOutCode.Found,WordPress.PHP.DevelopmentFunctions |
| 294 |
error_log( print_r( $args, true ) ); |
| 295 |
error_log( $query ); |
| 296 |
error_log( $count_query ); |
| 297 |
//phpcs:enable Squiz.PHP.CommentedOutCode.Found,WordPress.PHP.DevelopmentFunctions |
| 298 |
} |
| 299 |
|
| 300 |
/** |
| 301 |
* QUERY THE DATABASE FOR RESULTS |
| 302 |
*/ |
| 303 |
return array( |
| 304 |
'items' => $wpdb->get_results( $query ), // phpcs:ignore -- ok. |
| 305 |
'count' => absint( $wpdb->get_var( $count_query ) ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 306 |
); |
| 307 |
} |
| 308 |
|
| 309 |
/** |
| 310 |
* Get logs meta database table view. |
| 311 |
* |
| 312 |
* @return string logs meta view. |
| 313 |
*/ |
| 314 |
public function get_log_meta_view() { |
| 315 |
global $wpdb; |
| 316 |
$view = '(SELECT intlog.log_id AS view_log_id, '; |
| 317 |
$view .= '(SELECT meta_name.meta_value FROM ' . $wpdb->mainwp_tbl_logs_meta . ' meta_name WHERE meta_name.meta_log_id = intlog.log_id AND meta_name.meta_key = "name" LIMIT 1) AS meta_name, '; |
| 318 |
$view .= '(SELECT user_meta_json.meta_value FROM ' . $wpdb->mainwp_tbl_logs_meta . ' user_meta_json WHERE user_meta_json.meta_log_id = intlog.log_id AND user_meta_json.meta_key = "user_meta_json" LIMIT 1) AS user_meta_json, '; |
| 319 |
$view .= '(SELECT usermeta.meta_value FROM ' . $wpdb->mainwp_tbl_logs_meta . ' usermeta WHERE usermeta.meta_log_id = intlog.log_id AND usermeta.meta_key = "user_meta" LIMIT 1) AS usermeta, '; // compatible. |
| 320 |
$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 '; |
| 321 |
$view .= ' FROM ' . $wpdb->mainwp_tbl_logs . ' intlog)'; |
| 322 |
return $view; |
| 323 |
} |
| 324 |
|
| 325 |
/** |
| 326 |
* Method get_sub_query to support seaching in events table. |
| 327 |
* |
| 328 |
* @return string sub query view. |
| 329 |
*/ |
| 330 |
public function get_sub_query_view() { |
| 331 |
global $wpdb; |
| 332 |
$view = ' (SELECT sub_tbl.log_id AS sub_log_id, '; |
| 333 |
$view .= ' CASE WHEN sub_tbl.connector = "non-mainwp-changes" THEN "WP Admin" '; |
| 334 |
$view .= ' ELSE "Dashboard" '; |
| 335 |
$view .= ' END AS source '; |
| 336 |
$view .= ' FROM ' . $wpdb->mainwp_tbl_logs . ' sub_tbl) '; |
| 337 |
return $view; |
| 338 |
} |
| 339 |
} |
| 340 |
|
