| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentMail\App\Models; |
| 4 |
|
| 5 |
use Exception; |
| 6 |
use InvalidArgumentException; |
| 7 |
use FluentMail\Includes\Support\Arr; |
| 8 |
|
| 9 |
class Logger extends Model |
| 10 |
{ |
| 11 |
const STATUS_PENDING = 'pending'; |
| 12 |
const STATUS_FAILED = 'failed'; |
| 13 |
const STATUS_SENT = 'sent'; |
| 14 |
|
| 15 |
protected $fillables = [ |
| 16 |
'to', |
| 17 |
'from', |
| 18 |
'subject', |
| 19 |
'body', |
| 20 |
'status', |
| 21 |
'response', |
| 22 |
'extra', |
| 23 |
'created_at' |
| 24 |
]; |
| 25 |
|
| 26 |
protected $searchables = [ |
| 27 |
'to', |
| 28 |
'from', |
| 29 |
'subject' |
| 30 |
]; |
| 31 |
|
| 32 |
/* |
| 33 |
* Columns log navigation may filter on. `filter_by` arrives from the |
| 34 |
* request and lands in the query as an identifier, which prepare() cannot |
| 35 |
* bind, so a value outside this list is dropped instead of interpolated. |
| 36 |
* Every key is re-checked against it in buildWhere()'s loop, which is the |
| 37 |
* one place an identifier is written into SQL. |
| 38 |
*/ |
| 39 |
protected $filterables = [ |
| 40 |
'status', |
| 41 |
'created_at', |
| 42 |
'to', |
| 43 |
'from', |
| 44 |
'subject' |
| 45 |
]; |
| 46 |
|
| 47 |
protected $table = null; |
| 48 |
|
| 49 |
public function __construct() |
| 50 |
{ |
| 51 |
parent::__construct(); |
| 52 |
|
| 53 |
$this->table = $this->db->prefix . FLUENT_MAIL_DB_PREFIX . 'email_logs'; |
| 54 |
} |
| 55 |
|
| 56 |
public function get($data) |
| 57 |
{ |
| 58 |
$db = $this->getDb(); |
| 59 |
$page = isset($data['page']) ? (int)$data['page'] : 1; |
| 60 |
$perPage = isset($data['per_page']) ? (int)$data['per_page'] : 15; |
| 61 |
$offset = ($page - 1) * $perPage; |
| 62 |
|
| 63 |
$query = $db->table(FLUENT_MAIL_DB_PREFIX . 'email_logs') |
| 64 |
->limit($perPage) |
| 65 |
->offset($offset) |
| 66 |
->orderBy('id', 'DESC'); |
| 67 |
|
| 68 |
if (!empty($data['status'])) { |
| 69 |
$query->where('status', sanitize_text_field($data['status'])); |
| 70 |
} |
| 71 |
|
| 72 |
if (!empty($data['date_range']) && is_array($data['date_range']) && count($data['date_range']) == 2) { |
| 73 |
$dateRange = $data['date_range']; |
| 74 |
$from = $dateRange[0] . ' 00:00:01'; |
| 75 |
$to = $dateRange[1] . ' 23:59:59'; |
| 76 |
$query->whereBetween('created_at', $from, $to); |
| 77 |
} |
| 78 |
|
| 79 |
if (!empty($data['search'])) { |
| 80 |
$search = trim(sanitize_text_field($data['search'])); |
| 81 |
$query->where(function ($q) use ($search) { |
| 82 |
$searchColumns = $this->searchables; |
| 83 |
|
| 84 |
$columnSearch = false; |
| 85 |
if (strpos($search, ':')) { |
| 86 |
$searchArray = explode(':', $search); |
| 87 |
$column = array_shift($searchArray); |
| 88 |
if (in_array($column, $this->fillables)) { |
| 89 |
$columnSearch = true; |
| 90 |
$q->where($column, 'LIKE', '%' . trim(implode(':', $searchArray)) . '%'); |
| 91 |
} |
| 92 |
} |
| 93 |
|
| 94 |
if (!$columnSearch) { |
| 95 |
$firstColumn = array_shift($searchColumns); |
| 96 |
$q->where($firstColumn, 'LIKE', '%' . $search . '%'); |
| 97 |
foreach ($searchColumns as $column) { |
| 98 |
$q->orWhere($column, 'LIKE', '%' . $search . '%'); |
| 99 |
} |
| 100 |
} |
| 101 |
|
| 102 |
}); |
| 103 |
} |
| 104 |
|
| 105 |
$result = $query->paginate(); |
| 106 |
$result['data'] = $this->formatResult($result['data']); |
| 107 |
|
| 108 |
return $result; |
| 109 |
} |
| 110 |
|
| 111 |
protected function buildWhere($data) |
| 112 |
{ |
| 113 |
$where = []; |
| 114 |
|
| 115 |
$filterBy = Arr::get($data, 'filter_by'); |
| 116 |
|
| 117 |
if (isset($data['filter_by_value']) && in_array($filterBy, $this->filterables, true)) { |
| 118 |
$value = $this->normalizeFilterValue($filterBy, $data['filter_by_value']); |
| 119 |
|
| 120 |
if (!is_null($value)) { |
| 121 |
$where[$filterBy] = $value; |
| 122 |
} |
| 123 |
} |
| 124 |
|
| 125 |
/* |
| 126 |
* The date range travels beside filter_by rather than through it. |
| 127 |
* |
| 128 |
* There is only one filter_by slot and the logs screen already spends it on |
| 129 |
* status, so a viewer opened from a date-filtered list had no range at all and |
| 130 |
* its Prev/Next walked straight out of the result set the user was looking at. |
| 131 |
* Normalized through the same helper so the pair reaching the loop is scalar. |
| 132 |
*/ |
| 133 |
$dateRange = $this->normalizeFilterValue('created_at', Arr::get($data, 'date_range')); |
| 134 |
|
| 135 |
if (!isset($where['created_at']) && is_array($dateRange)) { |
| 136 |
$where['created_at'] = $dateRange; |
| 137 |
} |
| 138 |
|
| 139 |
if (isset($data['query']) && is_scalar($data['query']) && trim($data['query']) !== '') { |
| 140 |
$query = trim($data['query']); |
| 141 |
$columns = $this->searchables; |
| 142 |
|
| 143 |
/* |
| 144 |
* The same reading get() gives the search box: `subject:invoice` is a |
| 145 |
* search of that one column, not of every column for the literal text. |
| 146 |
* The viewer's Prev and Next walk the list the search produced, so a |
| 147 |
* column search that the list understood and the navigation did not |
| 148 |
* left Next with nothing to land on. |
| 149 |
*/ |
| 150 |
if (strpos($query, ':')) { |
| 151 |
$parts = explode(':', $query); |
| 152 |
$column = array_shift($parts); |
| 153 |
|
| 154 |
if (in_array($column, $this->filterables, true)) { |
| 155 |
$columns = [$column]; |
| 156 |
$query = trim(implode(':', $parts)); |
| 157 |
} |
| 158 |
} |
| 159 |
|
| 160 |
foreach ($columns as $column) { |
| 161 |
if (isset($where[$column])) { |
| 162 |
$where[$column] .= '|' . $query; |
| 163 |
} else { |
| 164 |
$where[$column] = $query; |
| 165 |
} |
| 166 |
} |
| 167 |
} |
| 168 |
|
| 169 |
$args = [1]; |
| 170 |
$andWhere = $orWhere = ''; |
| 171 |
$whereClause = "WHERE 1 = '%d'"; |
| 172 |
|
| 173 |
foreach ($where as $key => $value) { |
| 174 |
if (!in_array($key, $this->filterables, true)) { |
| 175 |
continue; |
| 176 |
} |
| 177 |
|
| 178 |
if (in_array($key, ['status', 'created_at'])) { |
| 179 |
if ($key == 'created_at') { |
| 180 |
if (is_array($value)) { |
| 181 |
$args[] = $value[0]; |
| 182 |
$args[] = $value[1]; |
| 183 |
} else { |
| 184 |
$args[] = $value; |
| 185 |
$args[] = $value; |
| 186 |
} |
| 187 |
$andWhere .= " AND `{$key}` >= '%s' AND `{$key}` < '%s' + INTERVAL 1 DAY"; |
| 188 |
} else { |
| 189 |
$args[] = $value; |
| 190 |
$andWhere .= " AND `{$key}` = '%s'"; |
| 191 |
} |
| 192 |
} else { |
| 193 |
if (strpos($value, '|')) { |
| 194 |
$nestedOr = ''; |
| 195 |
$values = explode('|', $value); |
| 196 |
foreach ($values as $itemValue) { |
| 197 |
$args[] = '%' . $this->db->esc_like($itemValue) . '%'; |
| 198 |
$nestedOr .= " OR `{$key}` LIKE '%s'"; |
| 199 |
} |
| 200 |
$orWhere .= ' OR (' . trim($nestedOr, 'OR ') . ')'; |
| 201 |
} else { |
| 202 |
$args[] = '%' . $this->db->esc_like($value) . '%'; |
| 203 |
$orWhere .= " OR `{$key}` LIKE '%s'"; |
| 204 |
} |
| 205 |
} |
| 206 |
} |
| 207 |
|
| 208 |
if ($orWhere) { |
| 209 |
$orWhere = 'AND (' . trim($orWhere, 'OR ') . ')'; |
| 210 |
} |
| 211 |
|
| 212 |
$whereClause = implode(' ', [$whereClause, trim($andWhere), $orWhere]); |
| 213 |
|
| 214 |
return [$whereClause, $args]; |
| 215 |
} |
| 216 |
|
| 217 |
/** |
| 218 |
* `created_at` is the one filter carried as a [from, to] pair; every other |
| 219 |
* column takes a single value. The request can send either as an array, so |
| 220 |
* anything that would reach the string operations in buildWhere() as a |
| 221 |
* non-scalar is normalized here or dropped. Returns null when there is |
| 222 |
* nothing usable left. |
| 223 |
*/ |
| 224 |
protected function normalizeFilterValue($column, $value) |
| 225 |
{ |
| 226 |
if (is_scalar($value)) { |
| 227 |
return $value; |
| 228 |
} |
| 229 |
|
| 230 |
if ($column != 'created_at' || !is_array($value)) { |
| 231 |
return null; |
| 232 |
} |
| 233 |
|
| 234 |
$value = array_values(array_filter($value, 'is_scalar')); |
| 235 |
|
| 236 |
if (count($value) > 1) { |
| 237 |
return [$value[0], $value[1]]; |
| 238 |
} |
| 239 |
|
| 240 |
return count($value) ? $value[0] : null; |
| 241 |
} |
| 242 |
|
| 243 |
protected function formatResult($result) |
| 244 |
{ |
| 245 |
$result = is_array($result) ? $result : func_get_args(); |
| 246 |
foreach ($result as $key => $row) { |
| 247 |
$result[$key] = $this->maybeUnserialize((array)$row); |
| 248 |
$result[$key]['id'] = (int)$result[$key]['id']; |
| 249 |
$result[$key]['retries'] = (int)$result[$key]['retries']; |
| 250 |
$result[$key]['from'] = htmlspecialchars($result[$key]['from']); |
| 251 |
/* |
| 252 |
* No wp_kses_post() here. Both consumers render the subject as text |
| 253 |
* — {{ }} in Logs.vue and LogViewer.vue, which escapes on its own — |
| 254 |
* so kses adds no safety, and its entity normalization rewrites a |
| 255 |
* subject reading "Tom & Jerry" to "Tom & Jerry", which the |
| 256 |
* page then shows verbatim. Anything that puts a subject into |
| 257 |
* HTML has to escape it at that point, as digest_email.php does. |
| 258 |
*/ |
| 259 |
$result[$key]['subject'] = wp_unslash($result[$key]['subject']); |
| 260 |
} |
| 261 |
|
| 262 |
return $result; |
| 263 |
} |
| 264 |
|
| 265 |
protected function maybeUnserialize(array $data) |
| 266 |
{ |
| 267 |
foreach ($data as $key => $value) { |
| 268 |
if ($this->isUnserializable($key)) { |
| 269 |
$data[$key] = $this->unserialize($value); |
| 270 |
} |
| 271 |
} |
| 272 |
|
| 273 |
return $data; |
| 274 |
} |
| 275 |
|
| 276 |
protected function isUnserializable($key) |
| 277 |
{ |
| 278 |
$allowedFields = [ |
| 279 |
'to', |
| 280 |
'headers', |
| 281 |
'attachments', |
| 282 |
'response', |
| 283 |
'extra' |
| 284 |
]; |
| 285 |
|
| 286 |
return in_array($key, $allowedFields); |
| 287 |
} |
| 288 |
|
| 289 |
protected function unserialize($data) |
| 290 |
{ |
| 291 |
if (is_serialized($data)) { |
| 292 |
if (preg_match('/(^|;)O:[0-9]+:/', $data)) { |
| 293 |
return $data; |
| 294 |
} |
| 295 |
return unserialize(trim($data), ['allowed_classes' => false]); |
| 296 |
} |
| 297 |
|
| 298 |
return $data; |
| 299 |
} |
| 300 |
|
| 301 |
protected function formatHeaders($headers) |
| 302 |
{ |
| 303 |
foreach ((array)$headers as $key => $header) { |
| 304 |
if (is_array($header)) { |
| 305 |
$header = $this->formatHeaders($header); |
| 306 |
} else { |
| 307 |
$header = htmlspecialchars($header); |
| 308 |
} |
| 309 |
|
| 310 |
$headers[$key] = $header; |
| 311 |
} |
| 312 |
|
| 313 |
return $headers; |
| 314 |
} |
| 315 |
|
| 316 |
public function add($data) |
| 317 |
{ |
| 318 |
try { |
| 319 |
$data = array_merge($data, [ |
| 320 |
'created_at' => current_time('mysql') |
| 321 |
]); |
| 322 |
|
| 323 |
return $this->getDb()->table(FLUENT_MAIL_DB_PREFIX . 'email_logs') |
| 324 |
->insert($data); |
| 325 |
|
| 326 |
} catch (Exception $e) { |
| 327 |
return $e; |
| 328 |
} |
| 329 |
} |
| 330 |
|
| 331 |
public function delete(array $id) |
| 332 |
{ |
| 333 |
if ($id && $id[0] == 'all') { |
| 334 |
// TRUNCATE doesn't support parameterization |
| 335 |
// Table name is safe - constructed from constants in __construct() |
| 336 |
return $this->db->query("TRUNCATE TABLE {$this->table}"); |
| 337 |
} |
| 338 |
|
| 339 |
$ids = array_filter($id, 'intval'); |
| 340 |
|
| 341 |
if ($ids) { |
| 342 |
return $this->getDb()->table(FLUENT_MAIL_DB_PREFIX . 'email_logs') |
| 343 |
->whereIn('id', $ids) |
| 344 |
->delete(); |
| 345 |
} |
| 346 |
|
| 347 |
return false; |
| 348 |
} |
| 349 |
|
| 350 |
public function navigate($data) |
| 351 |
{ |
| 352 |
$filterBy = Arr::get($data, 'filter_by'); |
| 353 |
foreach (['date', 'daterange', 'datetime', 'datetimerange'] as $field) { |
| 354 |
if ($filterBy == $field) { |
| 355 |
$data['filter_by'] = 'created_at'; |
| 356 |
} |
| 357 |
} |
| 358 |
|
| 359 |
/* |
| 360 |
* The cursor is bound as '%d', so it cannot carry SQL either way, but |
| 361 |
* an array-shaped `id` from the request reaches prepare() as an |
| 362 |
* unsupported argument type and trips _doing_it_wrong(), which prints |
| 363 |
* a notice into the AJAX response body on a debug site. Narrowed to an |
| 364 |
* integer here instead. |
| 365 |
*/ |
| 366 |
$id = isset($data['id']) && is_scalar($data['id']) ? (int) $data['id'] : 0; |
| 367 |
|
| 368 |
$dir = isset($data['dir']) ? $data['dir'] : null; |
| 369 |
|
| 370 |
list($where, $args) = $this->buildWhere($data); |
| 371 |
|
| 372 |
$args = array_merge($args, [$id]); |
| 373 |
|
| 374 |
$sqlNext = "SELECT * FROM {$this->table} {$where} AND `id` > '%d' ORDER BY id LIMIT 2"; |
| 375 |
$sqlPrev = "SELECT * FROM {$this->table} {$where} AND `id` < '%d' ORDER BY id DESC LIMIT 2"; |
| 376 |
|
| 377 |
if ($dir == 'next') { |
| 378 |
$query = $this->db->prepare($sqlNext, $args); |
| 379 |
} else if ($dir == 'prev') { |
| 380 |
$query = $this->db->prepare($sqlPrev, $args); |
| 381 |
} else { |
| 382 |
foreach (['next' => $sqlNext, 'prev' => $sqlPrev] as $key => $sql) { |
| 383 |
|
| 384 |
$keyResult = $this->db->get_results( |
| 385 |
$this->db->prepare($sql, $args) |
| 386 |
); |
| 387 |
|
| 388 |
$result[$key] = $this->formatResult($keyResult); |
| 389 |
} |
| 390 |
|
| 391 |
return $result; |
| 392 |
} |
| 393 |
|
| 394 |
$result = $this->db->get_results($query); |
| 395 |
|
| 396 |
if (count($result) > 1) { |
| 397 |
$next = true; |
| 398 |
$prev = true; |
| 399 |
} else { |
| 400 |
if ($dir == 'next') { |
| 401 |
$next = false; |
| 402 |
$prev = true; |
| 403 |
} else { |
| 404 |
$next = true; |
| 405 |
$prev = false; |
| 406 |
} |
| 407 |
} |
| 408 |
|
| 409 |
return [ |
| 410 |
'log' => $result ? $this->formatResult($result[0])[0] : null, |
| 411 |
'next' => $next, |
| 412 |
'prev' => $prev |
| 413 |
]; |
| 414 |
} |
| 415 |
|
| 416 |
public function find($id) |
| 417 |
{ |
| 418 |
|
| 419 |
$row = $this->getDb()->table(FLUENT_MAIL_DB_PREFIX . 'email_logs') |
| 420 |
->where('id', $id) |
| 421 |
->first(); |
| 422 |
|
| 423 |
$row->extra = $this->unserialize($row->extra); |
| 424 |
|
| 425 |
$row->response = $this->unserialize($row->response); |
| 426 |
|
| 427 |
return (array)$row; |
| 428 |
} |
| 429 |
|
| 430 |
public function resendEmailFromLog($id, $type = 'retry', $recipients = []) |
| 431 |
{ |
| 432 |
$email = $this->find($id); |
| 433 |
|
| 434 |
$email['to'] = $this->unserialize($email['to']); |
| 435 |
$email['headers'] = $this->unserialize($email['headers']); |
| 436 |
$email['attachments'] = $this->unserialize($email['attachments']); |
| 437 |
$email['extra'] = $this->unserialize($email['extra']); |
| 438 |
|
| 439 |
// Convert PHPMailer attachment format to wp_mail format |
| 440 |
$wpMailAttachments = []; |
| 441 |
if (!empty($email['attachments']) && is_array($email['attachments'])) { |
| 442 |
foreach ($email['attachments'] as $attachment) { |
| 443 |
if (is_array($attachment)) { |
| 444 |
// PHPMailer format: [path, filename, name, encoding, type, isString, disposition, cid] |
| 445 |
if (isset($attachment[0]) && is_string($attachment[0])) { |
| 446 |
$filePath = $attachment[0]; |
| 447 |
if (file_exists($filePath) && is_readable($filePath)) { |
| 448 |
$wpMailAttachments[] = $filePath; |
| 449 |
} |
| 450 |
} |
| 451 |
} elseif (is_string($attachment)) { |
| 452 |
if (file_exists($attachment) && is_readable($attachment)) { |
| 453 |
$wpMailAttachments[] = $attachment; |
| 454 |
} |
| 455 |
} |
| 456 |
} |
| 457 |
} |
| 458 |
|
| 459 |
// When custom recipients are provided, drop cc/bcc headers so the email |
| 460 |
// is only delivered to the requested address(es). |
| 461 |
$hasCustomRecipients = !empty($recipients) && is_array($recipients); |
| 462 |
$skipHeaderKeys = $hasCustomRecipients ? ['cc', 'bcc'] : []; |
| 463 |
|
| 464 |
$headers = []; |
| 465 |
|
| 466 |
foreach ($email['headers'] as $key => $value) { |
| 467 |
|
| 468 |
if (in_array(strtolower((string) $key), $skipHeaderKeys, true)) { |
| 469 |
continue; |
| 470 |
} |
| 471 |
|
| 472 |
if($key == 'content-type' && $value == 'multipart/alternative') { |
| 473 |
$value = 'text/html'; |
| 474 |
} |
| 475 |
|
| 476 |
if (is_array($value)) { |
| 477 |
$values = []; |
| 478 |
$value = array_filter($value); |
| 479 |
foreach ($value as $v) { |
| 480 |
if (is_array($v) && isset($v['email'])) { |
| 481 |
$v = $v['email']; |
| 482 |
} |
| 483 |
$values[] = $v; |
| 484 |
} |
| 485 |
if ($values) { |
| 486 |
$headers[] = "{$key}: " . implode(';', $values); |
| 487 |
} |
| 488 |
} else { |
| 489 |
if ($value) { |
| 490 |
$headers[] = "{$key}: $value"; |
| 491 |
} |
| 492 |
} |
| 493 |
} |
| 494 |
|
| 495 |
$headers = array_merge($headers, [ |
| 496 |
'From: ' . $email['from'] |
| 497 |
]); |
| 498 |
|
| 499 |
if ($hasCustomRecipients) { |
| 500 |
$to = array_values($recipients); |
| 501 |
} else { |
| 502 |
$to = []; |
| 503 |
foreach ($email['to'] as $recipient) { |
| 504 |
if (isset($recipient['name'])) { |
| 505 |
$to[] = $recipient['name'] . ' <' . $recipient['email'] . '>'; |
| 506 |
} else { |
| 507 |
$to[] = $recipient['email']; |
| 508 |
} |
| 509 |
} |
| 510 |
} |
| 511 |
|
| 512 |
try { |
| 513 |
if (!defined('FLUENTMAIL_LOG_OFF')) { |
| 514 |
define('FLUENTMAIL_LOG_OFF', true); |
| 515 |
} |
| 516 |
|
| 517 |
$startedAt = microtime(true); |
| 518 |
|
| 519 |
$result = wp_mail( |
| 520 |
$to, |
| 521 |
$email['subject'], |
| 522 |
$email['body'], |
| 523 |
$headers, |
| 524 |
$wpMailAttachments // Use the converted attachment format |
| 525 |
); |
| 526 |
|
| 527 |
$durationMs = round((microtime(true) - $startedAt) * 1000, 1); |
| 528 |
|
| 529 |
$updateData = [ |
| 530 |
'status' => 'sent', |
| 531 |
'updated_at' => current_time('mysql'), |
| 532 |
]; |
| 533 |
|
| 534 |
if (!$result && $type == 'check_realtime' && $email['status'] == 'failed') { |
| 535 |
$updateData['status'] = 'failed'; |
| 536 |
} |
| 537 |
|
| 538 |
if ($type == 'resend') { |
| 539 |
$updateData['resent_count'] = intval($email['resent_count']) + 1; |
| 540 |
$updateData['extra'] = maybe_serialize( |
| 541 |
$this->appendResendRecord($email['extra'], $to, (bool)$result, $durationMs) |
| 542 |
); |
| 543 |
} else { |
| 544 |
$updateData['retries'] = intval($email['retries']) + 1; |
| 545 |
} |
| 546 |
|
| 547 |
if ($this->updateLog($updateData, ['id' => $id])) { |
| 548 |
$email = $this->find($id); |
| 549 |
$email['to'] = $this->unserialize($email['to']); |
| 550 |
$email['headers'] = $this->unserialize($email['headers']); |
| 551 |
$email['attachments'] = $this->unserialize($email['attachments']); |
| 552 |
$email['extra'] = $this->unserialize($email['extra']); |
| 553 |
return $email; |
| 554 |
} |
| 555 |
} catch (\PHPMailer\PHPMailer\Exception $e) { |
| 556 |
throw $e; |
| 557 |
} |
| 558 |
} |
| 559 |
|
| 560 |
/** |
| 561 |
* Record where a resend actually went. |
| 562 |
* |
| 563 |
* A resend can now be redirected to an address other than the original |
| 564 |
* recipient, and resent_count alone only says that it happened, not where |
| 565 |
* it landed - so a log row could show three resends with nothing to say |
| 566 |
* that two of them went to somebody else entirely. The trail lives in the |
| 567 |
* existing `extra` column, which needs no schema change, and the row's own |
| 568 |
* `to` stays untouched as the record of the original send. |
| 569 |
* |
| 570 |
* @param mixed $extra The unserialized `extra` column. |
| 571 |
* @param array $to Recipients this resend was addressed to. |
| 572 |
* @param bool $sent Whether the send itself reported success. |
| 573 |
* @param float $ms How long the send took, in milliseconds. |
| 574 |
* @return array |
| 575 |
*/ |
| 576 |
protected function appendResendRecord($extra, $to, $sent, $ms = null) |
| 577 |
{ |
| 578 |
$extra = is_array($extra) ? $extra : []; |
| 579 |
|
| 580 |
$resends = []; |
| 581 |
|
| 582 |
if (!empty($extra['resends']) && is_array($extra['resends'])) { |
| 583 |
$resends = $extra['resends']; |
| 584 |
} |
| 585 |
|
| 586 |
$user = wp_get_current_user(); |
| 587 |
|
| 588 |
$resends[] = [ |
| 589 |
'at' => current_time('mysql'), |
| 590 |
'to' => array_values(array_map('strval', (array)$to)), |
| 591 |
// The display name as it stood at the time. Storing an ID would |
| 592 |
// leave the trail unreadable once the account is deleted, which is |
| 593 |
// exactly when it matters. |
| 594 |
'by' => ($user && $user->exists()) ? $user->display_name : '', |
| 595 |
'sent' => $sent, |
| 596 |
'ms' => $ms |
| 597 |
]; |
| 598 |
|
| 599 |
// Keep the tail. A row that gets resent all day should not grow its |
| 600 |
// extra column without bound. |
| 601 |
$limit = apply_filters('fluentsmtp_resend_history_limit', 20); |
| 602 |
|
| 603 |
if (count($resends) > $limit) { |
| 604 |
$resends = array_slice($resends, -$limit); |
| 605 |
} |
| 606 |
|
| 607 |
$extra['resends'] = array_values($resends); |
| 608 |
|
| 609 |
return $extra; |
| 610 |
} |
| 611 |
|
| 612 |
public function updateLog($data, $where) |
| 613 |
{ |
| 614 |
return $this->db->update($this->table, $data, $where); |
| 615 |
} |
| 616 |
|
| 617 |
public function getStats() |
| 618 |
{ |
| 619 |
// Status values are hardcoded, no need for prepare() |
| 620 |
$succeeded = $this->db->get_var("SELECT COUNT(id) FROM {$this->table} WHERE status = 'sent'"); |
| 621 |
$failed = $this->db->get_var("SELECT COUNT(id) FROM {$this->table} WHERE status = 'failed'"); |
| 622 |
|
| 623 |
return [ |
| 624 |
'sent' => $succeeded, |
| 625 |
'failed' => $failed |
| 626 |
]; |
| 627 |
} |
| 628 |
|
| 629 |
public function deleteLogsOlderThan($days) |
| 630 |
{ |
| 631 |
try { |
| 632 |
|
| 633 |
$date = gmdate('Y-m-d H:i:s', current_time('timestamp') - $days * DAY_IN_SECONDS); |
| 634 |
|
| 635 |
/* |
| 636 |
* Deleted in batches rather than in one statement. A site that has |
| 637 |
* been logging for months can have this cron pass hit hundreds of |
| 638 |
* thousands of rows, and a single unbounded DELETE holds locks for |
| 639 |
* the whole scan - long enough to time out and leave the backlog |
| 640 |
* permanently uncleared, since the next run faces the same pile. |
| 641 |
* Batches let each statement commit and release. |
| 642 |
*/ |
| 643 |
$batchSize = (int)apply_filters('fluentmail_log_delete_batch_size', 2000); |
| 644 |
$batchSize = max(1, $batchSize); |
| 645 |
|
| 646 |
$deleted = 0; |
| 647 |
|
| 648 |
do { |
| 649 |
$query = $this->db->prepare( |
| 650 |
"DELETE FROM {$this->table} WHERE `created_at` < %s LIMIT %d", |
| 651 |
$date, |
| 652 |
$batchSize |
| 653 |
); |
| 654 |
|
| 655 |
$result = $this->db->query($query); |
| 656 |
|
| 657 |
if (!$result) { |
| 658 |
break; |
| 659 |
} |
| 660 |
|
| 661 |
$deleted += $result; |
| 662 |
} while ($result >= $batchSize); |
| 663 |
|
| 664 |
return $deleted; |
| 665 |
|
| 666 |
} catch (Exception $e) { |
| 667 |
fluentMailDebugLog('Failed to delete old email logs - ' . $e->getMessage()); |
| 668 |
} |
| 669 |
} |
| 670 |
|
| 671 |
public function getTotalCountStat($status, $startDate, $endDate = false) |
| 672 |
{ |
| 673 |
if ($endDate) { |
| 674 |
$query = $this->db->prepare( |
| 675 |
"SELECT COUNT(*) |
| 676 |
FROM {$this->table} |
| 677 |
WHERE status = %s |
| 678 |
AND created_at >= %s |
| 679 |
AND created_at <= %s", |
| 680 |
$status, |
| 681 |
$startDate, |
| 682 |
$endDate |
| 683 |
); |
| 684 |
} else { |
| 685 |
$query = $this->db->prepare( |
| 686 |
"SELECT COUNT(*) |
| 687 |
FROM {$this->table} |
| 688 |
WHERE status = %s |
| 689 |
AND created_at >= %s", |
| 690 |
$status, |
| 691 |
$startDate |
| 692 |
); |
| 693 |
} |
| 694 |
|
| 695 |
return (int)$this->db->get_var($query); |
| 696 |
} |
| 697 |
|
| 698 |
public function getSubjectCountStat($status, $startDate, $endDate) |
| 699 |
{ |
| 700 |
$query = $this->db->prepare( |
| 701 |
"SELECT COUNT(DISTINCT(subject)) |
| 702 |
FROM {$this->table} |
| 703 |
WHERE status = %s |
| 704 |
AND created_at >= %s |
| 705 |
AND created_at <= %s", |
| 706 |
$status, |
| 707 |
$startDate, |
| 708 |
$endDate |
| 709 |
); |
| 710 |
|
| 711 |
return (int)$this->db->get_var($query); |
| 712 |
} |
| 713 |
|
| 714 |
public function getSubjectStat($status, $statDate, $endDate, $limit = 5) |
| 715 |
{ |
| 716 |
// Sanitize and validate limit as positive integer |
| 717 |
$limit = max(1, absint($limit)) ?: 5; |
| 718 |
|
| 719 |
$query = $this->db->prepare( |
| 720 |
"SELECT subject, |
| 721 |
COUNT(DISTINCT id) AS emails_sent |
| 722 |
FROM {$this->table} |
| 723 |
WHERE created_at >= %s |
| 724 |
AND created_at <= %s |
| 725 |
AND status = %s |
| 726 |
GROUP BY subject |
| 727 |
ORDER BY emails_sent DESC |
| 728 |
LIMIT %d", |
| 729 |
$statDate, |
| 730 |
$endDate, |
| 731 |
$status, |
| 732 |
$limit |
| 733 |
); |
| 734 |
|
| 735 |
return $this->db->get_results($query, ARRAY_A); |
| 736 |
} |
| 737 |
|
| 738 |
} |
| 739 |
|
