| 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 -- complex method. |
| 33 |
global $wpdb; |
| 34 |
|
| 35 |
$join = ''; |
| 36 |
$where = ''; |
| 37 |
|
| 38 |
if ( ! empty( $args['search'] ) ) { |
| 39 |
$field = ! empty( $args['search_field'] ) ? $args['search_field'] : 'item'; |
| 40 |
|
| 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 |
| 45 |
} |
| 46 |
} |
| 47 |
|
| 48 |
$array_groups_ids = array(); |
| 49 |
$array_clients_ids = array(); |
| 50 |
$array_users_ids = array(); |
| 51 |
|
| 52 |
if ( ! empty( $args['groups_ids'] ) && is_array( $args['groups_ids'] ) ) { |
| 53 |
$array_groups_ids = MainWP_Utility::array_numeric_filter( $args['groups_ids'] ); |
| 54 |
if ( ! empty( $array_groups_ids ) ) { |
| 55 |
$groups_sites = MainWP_DB_Client::instance()->get_websites_by_group_ids( $array_groups_ids ); |
| 56 |
$array_website_ids = array(); |
| 57 |
if ( $groups_sites ) { |
| 58 |
foreach ( $groups_sites as $website ) { |
| 59 |
$array_website_ids[] = $website->id; |
| 60 |
} |
| 61 |
} |
| 62 |
unset( $groups_sites ); |
| 63 |
if ( ! empty( $array_website_ids ) ) { |
| 64 |
$where .= " AND lg.site_id IN ('" . implode( "','", $array_website_ids ) . "') "; |
| 65 |
} else { |
| 66 |
$where .= ' AND false '; |
| 67 |
} |
| 68 |
} |
| 69 |
} |
| 70 |
|
| 71 |
if ( ! empty( $args['client_ids'] ) && is_array( $args['client_ids'] ) ) { |
| 72 |
$array_clients_ids = MainWP_Utility::array_numeric_filter( $args['client_ids'] ); |
| 73 |
if ( ! empty( $array_clients_ids ) ) { |
| 74 |
$client_sites = MainWP_DB_Client::instance()->get_websites_by_client_ids( $array_clients_ids ); |
| 75 |
$array_website_ids = array(); |
| 76 |
if ( $client_sites ) { |
| 77 |
foreach ( $client_sites as $website ) { |
| 78 |
$array_website_ids[] = $website->id; |
| 79 |
} |
| 80 |
} |
| 81 |
unset( $client_sites ); |
| 82 |
if ( ! empty( $array_website_ids ) ) { |
| 83 |
$where .= " AND lg.site_id IN ('" . implode("','",$array_website_ids) . "') "; // phpcs:ignore -- ok. |
| 84 |
} else { |
| 85 |
$where .= ' AND false '; |
| 86 |
} |
| 87 |
} |
| 88 |
} |
| 89 |
|
| 90 |
if ( ! empty( $args['user_ids'] ) && is_array( $args['user_ids'] ) ) { |
| 91 |
$array_users_ids = MainWP_Utility::array_numeric_filter( $args['user_ids'] ); |
| 92 |
if ( ! empty( $array_users_ids ) ) { |
| 93 |
$where .= " AND lg.user_id IN ('" . implode("','",$array_users_ids) . "') "; // phpcs:ignore -- ok. |
| 94 |
} |
| 95 |
} |
| 96 |
|
| 97 |
$where_prev = ''; |
| 98 |
|
| 99 |
if ( ! empty( $args['timestart'] ) && ! empty( $args['timestop'] ) ) { |
| 100 |
$where .= $wpdb->prepare( ' AND `lg`.`created` >= %d AND `lg`.`created` <= %d', $args['timestart'], $args['timestop'] ); |
| 101 |
} |
| 102 |
|
| 103 |
/** |
| 104 |
* PARSE PAGINATION PARAMS |
| 105 |
*/ |
| 106 |
$limits = ''; |
| 107 |
$start = absint( $args['start'] ); |
| 108 |
$per_page = absint( $args['records_per_page'] ); |
| 109 |
|
| 110 |
if ( $per_page >= 0 ) { |
| 111 |
$limits = "LIMIT {$start}, {$per_page}"; |
| 112 |
} |
| 113 |
|
| 114 |
$limits_recent_count = ''; |
| 115 |
|
| 116 |
// list recent events. |
| 117 |
if ( ! empty( $args['recent_number'] ) ) { |
| 118 |
$limits_recent_count = ' LIMIT ' . intval( $args['recent_number'] ); |
| 119 |
} |
| 120 |
|
| 121 |
/** |
| 122 |
* PARSE ORDER PARAMS |
| 123 |
*/ |
| 124 |
$orderable = array( 'site_id', 'name', 'url', 'user_id', 'item', 'created', 'connector', 'context', 'action', 'duration', 'state' ); |
| 125 |
|
| 126 |
// Default to sorting by record ID. |
| 127 |
$orderby = 'lg.log_id'; |
| 128 |
|
| 129 |
if ( in_array( $args['orderby'], $orderable, true ) ) { |
| 130 |
if ( in_array( $args['orderby'], array( 'name', 'url' ) ) ) { |
| 131 |
$orderby = sprintf( '%s.%s', 'meta_view', $args['orderby'] ); |
| 132 |
} else { |
| 133 |
$orderby = sprintf( '%s.%s', 'lg', $args['orderby'] ); |
| 134 |
} |
| 135 |
} |
| 136 |
|
| 137 |
// Show the recent records first by default. |
| 138 |
$order = 'DESC'; |
| 139 |
if ( 'ASC' === strtoupper( $args['order'] ) ) { |
| 140 |
$order = 'ASC'; |
| 141 |
} |
| 142 |
|
| 143 |
$orderby = sprintf( 'ORDER BY %s %s', $orderby, $order ); |
| 144 |
|
| 145 |
/** |
| 146 |
* PARSE FIELDS PARAMETER |
| 147 |
*/ |
| 148 |
$selects = array(); |
| 149 |
$selects[] = 'lg.*'; |
| 150 |
$selects[] = 'meta_view.*'; |
| 151 |
$select = implode( ', ', $selects ); |
| 152 |
|
| 153 |
$join = ' LEFT JOIN ' . $this->get_log_meta_view() . ' meta_view ON lg.log_id = meta_view.view_log_id '; |
| 154 |
|
| 155 |
/** |
| 156 |
* BUILD THE FINAL QUERY |
| 157 |
*/ |
| 158 |
$query = "SELECT {$select} |
| 159 |
FROM $wpdb->mainwp_tbl_logs as lg |
| 160 |
{$join} |
| 161 |
WHERE `lg`.`connector` != 'compact' {$where} |
| 162 |
{$orderby} |
| 163 |
{$limits}"; |
| 164 |
|
| 165 |
// Build result count query. |
| 166 |
$count_query = "SELECT COUNT(*) as found |
| 167 |
FROM $wpdb->mainwp_tbl_logs as lg |
| 168 |
{$join} |
| 169 |
WHERE `lg`.`connector` != 'compact' {$where} |
| 170 |
{$limits_recent_count}"; |
| 171 |
|
| 172 |
//phpcs:ignore Squiz.PHP.CommentedOutCode.Found |
| 173 |
// error_log( print_r( $args, true ) );//. |
| 174 |
|
| 175 |
//phpcs:ignore Squiz.PHP.CommentedOutCode.Found |
| 176 |
// error_log( $query );//. |
| 177 |
|
| 178 |
//phpcs:ignore Squiz.PHP.CommentedOutCode.Found |
| 179 |
// error_log( $count_query );//. |
| 180 |
|
| 181 |
/** |
| 182 |
* QUERY THE DATABASE FOR RESULTS |
| 183 |
*/ |
| 184 |
$result = array( |
| 185 |
'items' => $wpdb->get_results( $query ), // phpcs:ignore -- ok. |
| 186 |
'count' => absint( $wpdb->get_var( $count_query ) ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared |
| 187 |
); |
| 188 |
|
| 189 |
return $result; |
| 190 |
} |
| 191 |
|
| 192 |
/** |
| 193 |
* Get logs meta database table view. |
| 194 |
* |
| 195 |
* @return string logs meta view. |
| 196 |
*/ |
| 197 |
public function get_log_meta_view() { |
| 198 |
global $wpdb; |
| 199 |
$view = '(SELECT intlog.log_id AS view_log_id '; |
| 200 |
$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, |
| 201 |
(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,'; |
| 202 |
$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 '; |
| 203 |
$view .= ' FROM ' . $wpdb->mainwp_tbl_logs . ' intlog)'; |
| 204 |
return $view; |
| 205 |
} |
| 206 |
} |
| 207 |
|
