1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
|
<?php
/**
* Repository class for handling operations on the wp_wpsmtp_logs table.
*
* @package Solid_SMTP\Repository
*/
namespace SolidWP\Mail\Repository;
use SolidWP\Mail\App;
/**
* Class LogsRepository
*
* Handles operations related to the wp_wpsmtp_logs table, including
* retrieving email logs and searching the logs by term.
*/
class LogsRepository {
/**
* Name of the table to interact with.
*
* @var string
*/
private string $table;
/**
* LogsRepository constructor.
*/
public function __construct() {
global $wpdb;
$this->table = $wpdb->prefix . 'wpsmtp_logs';
}
/**
* Retrieves email logs from the database.
*
* This function fetches email logs based on the provided pagination, search term,
* and sorting options. It sanitizes and validates the input parameters and constructs
* the SQL query accordingly.
*
* @param array $params {
* Array of parameters for fetching email logs.
*
* @type int $page The page number for pagination (0-based index).
* @type string $search_term The term to search for in email subjects, messages, and recipients.
* @type string $orderby The column to order the results by. Default is 'timestamp'.
* @type string $order The order direction ('ASC' or 'DESC'). Default is 'desc'.
* @type int $per_page The number of items per page.
* }
*
* @return array The array of email logs.
*/
public function get_email_logs( array $params = [] ): array {
$page = isset( $params['page'] ) ? max( $params['page'], 1 ) : 1;
$search_term = isset( $params['search_term'] ) ? trim( $params['search_term'] ) : '';
$orderby = $params['orderby'] ?? 'timestamp';
$order = $params['order'] ?? 'desc';
$per_page = $params['per_page'] ?? App::get( 'LOGS_PER_PAGE' );
$offset = ( $page - 1 ) * $per_page;
global $wpdb;
$valid_orderby = in_array( $orderby, [ 'timestamp', 'to', 'subject' ], true ) ? $orderby : 'timestamp';
$valid_order = in_array( strtoupper( $order ), [ 'ASC', 'DESC' ], true ) ? strtoupper( $order ) : 'DESC';
$search_sql = '';
$search_term = trim( $search_term );
if ( ! empty( $search_term ) ) {
$search_term = '%' . $wpdb->esc_like( $search_term ) . '%';
$search_sql = $wpdb->prepare(
'WHERE `subject` LIKE %s OR `message` LIKE %s OR `to` LIKE %s',
$search_term,
$search_term,
$search_term
);
}
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
// @TODO: Consider using wp_cache_get() / wp_cache_set() or wp_cache_delete()
$data = $wpdb->get_results(
$wpdb->prepare(
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
"SELECT * FROM {$this->table} {$search_sql} ORDER BY {$valid_orderby} {$valid_order} LIMIT %d OFFSET %d",
$per_page,
$offset
),
ARRAY_A
);
// sometimes we need to serialize the array.
foreach ( $data as &$val ) {
$val['to'] = maybe_unserialize( $val['to'] );
$val['to'] = is_array( $val['to'] ) ? implode( ', ', $val['to'] ) : $val['to'];
}
return $data;
}
/**
* Retrieves email logs within a specified date range.
*
* @param int $from Unix timestamp representing the start of the date range.
* @param int $to Unix timestamp representing the end of the date range.
*
* @return array Array of email logs, each log being an associative array containing
* the log details. Returns an empty array if the input parameters are invalid.
*/
public function get_email_logs_by_date( int $from, int $to ): array {
if ( $from <= 0 || $to <= 0 || $from > $to ) {
// should not come to this.
return [];
}
global $wpdb;
$data = $wpdb->get_results(
$wpdb->prepare(
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
"SELECT * FROM {$this->table} WHERE `timestamp` BETWEEN %s AND %s ORDER BY `timestamp` ASC",
wp_date( 'Y-m-d H:i:s', $from ),
wp_date( 'Y-m-d H:i:s', $to )
),
ARRAY_A
);
// Process each result to format the 'to' field properly.
foreach ( $data as &$val ) {
$val['to'] = maybe_unserialize( $val['to'] );
$val['to'] = is_array( $val['to'] ) ? implode( ', ', $val['to'] ) : $val['to'];
}
return $data;
}
/**
* Deletes a specific email log by ID.
*
* @param int $log_id ID of the log to delete.
*
* @return bool True on success, false on failure.
*/
public function delete_log( int $log_id ): bool {
global $wpdb;
return (bool) $wpdb->delete( $this->table, [ 'mail_id' => $log_id ], [ '%d' ] );
}
/**
* Deletes multiple logs from the database.
*
* This function performs a mass delete of logs whose IDs are specified
* in the input array. It constructs a single SQL query using the `IN` clause
* to delete all specified logs in one go, which is more efficient than deleting
* each log individually.
*
* @param array $ids Array of log IDs to be deleted.
*
* @return bool True if the logs were successfully deleted, false otherwise.
*/
public function delete_logs( array $ids ): bool {
global $wpdb;
if ( empty( $ids ) ) {
return false;
}
$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
$results = $wpdb->query(
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
$wpdb->prepare( "DELETE FROM {$this->table} WHERE mail_id IN ($placeholders)", $ids )
);
return $results !== false;
}
/**
* Counts all email log records.
*
* @param string $search_term Optional. The search term used to filter email log records.
*
* @return int Total number of email log records.
*/
public function count_all_logs( string $search_term = '' ): int {
global $wpdb;
$search_sql = '';
$search_term = trim( $search_term );
if ( ! empty( $search_term ) ) {
$search_term = '%' . $wpdb->esc_like( $search_term ) . '%';
$search_sql = $wpdb->prepare(
'WHERE `subject` LIKE %s OR `message` LIKE %s OR `to` LIKE %s',
$search_term,
$search_term,
$search_term
);
}
return absint(
$wpdb->get_var(
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
"SELECT COUNT(*) FROM {$this->table} $search_sql"
)
);
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
}
}
|