| 1 |
<?php |
| 2 |
/** |
| 3 |
* Charitable Log Query class. |
| 4 |
* |
| 5 |
* SQL query builder with parameterized filters. |
| 6 |
* |
| 7 |
* @package Charitable/Classes/Charitable_Log_Query |
| 8 |
* @author David Bisset |
| 9 |
* @copyright Copyright (c) 2023, WP Charitable LLC |
| 10 |
* @license http://opensource.org/licenses/gpl-2.0.php GNU Public License |
| 11 |
* @since 1.8.11 |
| 12 |
*/ |
| 13 |
|
| 14 |
// Exit if accessed directly. |
| 15 |
if ( ! defined( 'ABSPATH' ) ) { |
| 16 |
exit; |
| 17 |
} |
| 18 |
|
| 19 |
if ( ! class_exists( 'Charitable_Log_Query' ) ) : |
| 20 |
|
| 21 |
/** |
| 22 |
* Charitable_Log_Query |
| 23 |
* |
| 24 |
* @since 1.8.11 |
| 25 |
*/ |
| 26 |
class Charitable_Log_Query { |
| 27 |
|
| 28 |
/** |
| 29 |
* The database instance. |
| 30 |
* |
| 31 |
* @since 1.8.11 |
| 32 |
* |
| 33 |
* @var Charitable_Log_DB |
| 34 |
*/ |
| 35 |
private $db; |
| 36 |
|
| 37 |
/** |
| 38 |
* Constructor. |
| 39 |
* |
| 40 |
* @since 1.8.11 |
| 41 |
* |
| 42 |
* @param Charitable_Log_DB $db Database instance. |
| 43 |
*/ |
| 44 |
public function __construct( Charitable_Log_DB $db ) { |
| 45 |
$this->db = $db; |
| 46 |
} |
| 47 |
|
| 48 |
/** |
| 49 |
* Get log records with filtering and pagination. |
| 50 |
* |
| 51 |
* @since 1.8.11 |
| 52 |
* |
| 53 |
* @param array $args { |
| 54 |
* Query arguments. |
| 55 |
* |
| 56 |
* @type int $limit Number of records per page. Default 20. |
| 57 |
* @type int $offset Offset for pagination. Default 0. |
| 58 |
* @type string $search Search term for title and message. Default ''. |
| 59 |
* @type string $type Log type to filter by (REGEXP on comma-separated). Default ''. |
| 60 |
* @type string $level Log level to filter by (exact match). Default ''. |
| 61 |
* @type string $source Log source to filter by (exact match). Default ''. |
| 62 |
* @type int $campaign_id Campaign ID filter. Default 0. |
| 63 |
* @type int $donation_id Donation ID filter. Default 0. |
| 64 |
* @type int $donor_id Donor ID filter. Default 0. |
| 65 |
* @type string $date_from Start date (Y-m-d format). Default ''. |
| 66 |
* @type string $date_to End date (Y-m-d format). Default ''. |
| 67 |
* @type string $orderby Column to sort by. Default 'id'. |
| 68 |
* @type string $order Sort direction (ASC/DESC). Default 'DESC'. |
| 69 |
* } |
| 70 |
* @return array { |
| 71 |
* @type Charitable_Log_Records $records Collection of log records. |
| 72 |
* @type int $total Total count of matching records. |
| 73 |
* } |
| 74 |
*/ |
| 75 |
public function get( $args = array() ) { |
| 76 |
global $wpdb; |
| 77 |
|
| 78 |
$defaults = array( |
| 79 |
'limit' => 20, |
| 80 |
'offset' => 0, |
| 81 |
'search' => '', |
| 82 |
'type' => '', |
| 83 |
'level' => '', |
| 84 |
'source' => '', |
| 85 |
'campaign_id' => 0, |
| 86 |
'donation_id' => 0, |
| 87 |
'donor_id' => 0, |
| 88 |
'date_from' => '', |
| 89 |
'date_to' => '', |
| 90 |
'orderby' => 'id', |
| 91 |
'order' => 'DESC', |
| 92 |
); |
| 93 |
|
| 94 |
$args = wp_parse_args( $args, $defaults ); |
| 95 |
|
| 96 |
$table = $this->db->table_name; |
| 97 |
$where = array(); |
| 98 |
$values = array(); |
| 99 |
|
| 100 |
// Search filter. |
| 101 |
if ( ! empty( $args['search'] ) ) { |
| 102 |
$like = '%' . $wpdb->esc_like( $args['search'] ) . '%'; |
| 103 |
$where[] = '(title LIKE %s OR message LIKE %s)'; |
| 104 |
$values[] = $like; |
| 105 |
$values[] = $like; |
| 106 |
} |
| 107 |
|
| 108 |
// Type filter (REGEXP on comma-separated field). |
| 109 |
if ( ! empty( $args['type'] ) ) { |
| 110 |
$type = sanitize_key( $args['type'] ); |
| 111 |
$where[] = 'types REGEXP %s'; |
| 112 |
// Match the type as a standalone value in comma-separated list. |
| 113 |
$values[] = '(^|,)' . preg_quote( $type, '/' ) . '(,|$)'; |
| 114 |
} |
| 115 |
|
| 116 |
// Level filter. |
| 117 |
if ( ! empty( $args['level'] ) ) { |
| 118 |
$where[] = 'level = %s'; |
| 119 |
$values[] = sanitize_key( $args['level'] ); |
| 120 |
} |
| 121 |
|
| 122 |
// Source filter. |
| 123 |
if ( ! empty( $args['source'] ) ) { |
| 124 |
$where[] = 'source = %s'; |
| 125 |
$values[] = sanitize_key( $args['source'] ); |
| 126 |
} |
| 127 |
|
| 128 |
// Campaign ID filter. |
| 129 |
if ( ! empty( $args['campaign_id'] ) ) { |
| 130 |
$where[] = 'campaign_id = %d'; |
| 131 |
$values[] = absint( $args['campaign_id'] ); |
| 132 |
} |
| 133 |
|
| 134 |
// Donation ID filter. |
| 135 |
if ( ! empty( $args['donation_id'] ) ) { |
| 136 |
$where[] = 'donation_id = %d'; |
| 137 |
$values[] = absint( $args['donation_id'] ); |
| 138 |
} |
| 139 |
|
| 140 |
// Donor ID filter. |
| 141 |
if ( ! empty( $args['donor_id'] ) ) { |
| 142 |
$where[] = 'donor_id = %d'; |
| 143 |
$values[] = absint( $args['donor_id'] ); |
| 144 |
} |
| 145 |
|
| 146 |
// Date range filters. |
| 147 |
if ( ! empty( $args['date_from'] ) ) { |
| 148 |
$where[] = 'create_at >= %s'; |
| 149 |
$values[] = sanitize_text_field( $args['date_from'] ) . ' 00:00:00'; |
| 150 |
} |
| 151 |
|
| 152 |
if ( ! empty( $args['date_to'] ) ) { |
| 153 |
$where[] = 'create_at <= %s'; |
| 154 |
$values[] = sanitize_text_field( $args['date_to'] ) . ' 23:59:59'; |
| 155 |
} |
| 156 |
|
| 157 |
// Build WHERE clause. |
| 158 |
$where_clause = ''; |
| 159 |
if ( ! empty( $where ) ) { |
| 160 |
$where_clause = 'WHERE ' . implode( ' AND ', $where ); |
| 161 |
} |
| 162 |
|
| 163 |
// Validate orderby. |
| 164 |
$allowed_orderby = array( 'id', 'title', 'types', 'level', 'source', 'create_at' ); |
| 165 |
$orderby = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'id'; |
| 166 |
$order = strtoupper( $args['order'] ) === 'ASC' ? 'ASC' : 'DESC'; |
| 167 |
|
| 168 |
// Build the query. |
| 169 |
$limit = absint( $args['limit'] ); |
| 170 |
$offset = absint( $args['offset'] ); |
| 171 |
|
| 172 |
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM {$table} {$where_clause} ORDER BY {$orderby} {$order} LIMIT {$limit} OFFSET {$offset}"; |
| 173 |
|
| 174 |
if ( ! empty( $values ) ) { |
| 175 |
$sql = $wpdb->prepare( $sql, ...$values ); // phpcs:ignore |
| 176 |
} |
| 177 |
|
| 178 |
$rows = $wpdb->get_results( $sql ); // phpcs:ignore |
| 179 |
$total = (int) $wpdb->get_var( 'SELECT FOUND_ROWS()' ); // phpcs:ignore |
| 180 |
|
| 181 |
// Build records collection. |
| 182 |
$records = new Charitable_Log_Records(); |
| 183 |
|
| 184 |
if ( $rows ) { |
| 185 |
foreach ( $rows as $row ) { |
| 186 |
$records->push( new Charitable_Log_Record( $row ) ); |
| 187 |
} |
| 188 |
} |
| 189 |
|
| 190 |
return array( |
| 191 |
'records' => $records, |
| 192 |
'total' => $total, |
| 193 |
); |
| 194 |
} |
| 195 |
|
| 196 |
/** |
| 197 |
* Get a single record by ID. |
| 198 |
* |
| 199 |
* @since 1.8.11 |
| 200 |
* |
| 201 |
* @param int $id Record ID. |
| 202 |
* @return Charitable_Log_Record|null |
| 203 |
*/ |
| 204 |
public function get_by_id( $id ) { |
| 205 |
global $wpdb; |
| 206 |
|
| 207 |
$row = $wpdb->get_row( |
| 208 |
$wpdb->prepare( |
| 209 |
"SELECT * FROM {$this->db->table_name} WHERE id = %d", |
| 210 |
absint( $id ) |
| 211 |
) |
| 212 |
); |
| 213 |
|
| 214 |
return $row ? new Charitable_Log_Record( $row ) : null; |
| 215 |
} |
| 216 |
} |
| 217 |
|
| 218 |
endif; |
| 219 |
|
