AutomationRunLogStorage.php
1 year ago
AutomationRunStorage.php
2 months ago
AutomationStatisticsStorage.php
1 month ago
AutomationStorage.php
2 months ago
index.php
3 years ago
AutomationRunStorage.php
382 lines
| 1 | <?php declare(strict_types = 1); |
| 2 | |
| 3 | namespace MailPoet\Automation\Engine\Storage; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use MailPoet\Automation\Engine\Data\Automation; |
| 9 | use MailPoet\Automation\Engine\Data\AutomationRun; |
| 10 | use MailPoet\Automation\Engine\Data\Subject; |
| 11 | use MailPoet\Automation\Engine\Exceptions; |
| 12 | |
| 13 | class AutomationRunStorage { |
| 14 | /** @var string */ |
| 15 | private $table; |
| 16 | |
| 17 | /** @var string */ |
| 18 | private $subjectTable; |
| 19 | |
| 20 | public function __construct() { |
| 21 | global $wpdb; |
| 22 | $this->table = $wpdb->prefix . 'mailpoet_automation_runs'; |
| 23 | $this->subjectTable = $wpdb->prefix . 'mailpoet_automation_run_subjects'; |
| 24 | } |
| 25 | |
| 26 | public function createAutomationRun(AutomationRun $automationRun): int { |
| 27 | global $wpdb; |
| 28 | $automationTableData = $automationRun->toArray(); |
| 29 | $subjectTableData = $automationTableData['subjects']; |
| 30 | unset($automationTableData['subjects']); |
| 31 | $result = $wpdb->insert($this->table, $automationTableData); |
| 32 | if ($result === false) { |
| 33 | $this->throwDatabaseError(); |
| 34 | } |
| 35 | $automationRunId = $wpdb->insert_id; // phpcs:ignore Squiz.NamingConventions.ValidVariableName.MemberNotCamelCaps |
| 36 | |
| 37 | if (!$subjectTableData) { |
| 38 | //We allow for AutomationRuns with no subjects. |
| 39 | return $automationRunId; |
| 40 | } |
| 41 | |
| 42 | |
| 43 | $values = []; |
| 44 | foreach ($subjectTableData as $entry) { |
| 45 | $values[] = $wpdb->prepare('(%d,%s,%s,%s)', $automationRunId, $entry['key'], $entry['args'], $entry['hash']); |
| 46 | } |
| 47 | |
| 48 | $result = $wpdb->query( |
| 49 | $wpdb->prepare('INSERT INTO %i (`automation_run_id`, `key`, `args`, `hash`) VALUES ', $this->subjectTable) . implode(',', $values) |
| 50 | ); |
| 51 | if ($result === false) { |
| 52 | $this->throwDatabaseError(); |
| 53 | } |
| 54 | |
| 55 | return $automationRunId; |
| 56 | } |
| 57 | |
| 58 | public function getAutomationRun(int $id): ?AutomationRun { |
| 59 | global $wpdb; |
| 60 | |
| 61 | $data = $wpdb->get_row( |
| 62 | $wpdb->prepare('SELECT * FROM %i WHERE id = %d', $this->table, $id), |
| 63 | ARRAY_A |
| 64 | ); |
| 65 | |
| 66 | if (!is_array($data) || !$data) { |
| 67 | return null; |
| 68 | } |
| 69 | |
| 70 | $subjects = $wpdb->get_results( |
| 71 | $wpdb->prepare('SELECT * FROM %i WHERE automation_run_id = %d', $this->subjectTable, $id), |
| 72 | ARRAY_A |
| 73 | ); |
| 74 | $data['subjects'] = is_array($subjects) ? $subjects : []; |
| 75 | return AutomationRun::fromArray((array)$data); |
| 76 | } |
| 77 | |
| 78 | /** |
| 79 | * @param Automation $automation |
| 80 | * @return AutomationRun[] |
| 81 | */ |
| 82 | public function getAutomationRunsForAutomation(Automation $automation): array { |
| 83 | global $wpdb; |
| 84 | |
| 85 | $automationRuns = $wpdb->get_results( |
| 86 | $wpdb->prepare( |
| 87 | 'SELECT * FROM %i WHERE automation_id = %d ORDER BY id', |
| 88 | $this->table, |
| 89 | $automation->getId() |
| 90 | ), |
| 91 | ARRAY_A |
| 92 | ); |
| 93 | if (!is_array($automationRuns) || !$automationRuns) { |
| 94 | return []; |
| 95 | } |
| 96 | |
| 97 | $automationRunIds = array_column($automationRuns, 'id'); |
| 98 | $subjects = $wpdb->get_results( |
| 99 | $wpdb->prepare( |
| 100 | ' |
| 101 | SELECT * |
| 102 | FROM %i |
| 103 | WHERE automation_run_id IN (' . implode(',', array_fill(0, count($automationRunIds), '%s')) . ') |
| 104 | ORDER BY automation_run_id, id |
| 105 | ', |
| 106 | array_merge( |
| 107 | [$this->subjectTable], |
| 108 | $automationRunIds, |
| 109 | ) |
| 110 | ), |
| 111 | ARRAY_A |
| 112 | ); |
| 113 | |
| 114 | return array_map( |
| 115 | function($runData) use ($subjects): AutomationRun { |
| 116 | /** @var array $runData - PHPStan expects as array_map first parameter (callable(mixed): mixed)|null */ |
| 117 | $runData['subjects'] = array_values(array_filter( |
| 118 | is_array($subjects) ? $subjects : [], |
| 119 | function($subjectData) use ($runData): bool { |
| 120 | /** @var array $subjectData - PHPStan expects as array_map first parameter (callable(mixed): mixed)|null */ |
| 121 | return (int)$subjectData['automation_run_id'] === (int)$runData['id']; |
| 122 | } |
| 123 | )); |
| 124 | return AutomationRun::fromArray($runData); |
| 125 | }, |
| 126 | $automationRuns |
| 127 | ); |
| 128 | } |
| 129 | |
| 130 | /** |
| 131 | * @param Automation $automation |
| 132 | * @return int |
| 133 | */ |
| 134 | public function getCountByAutomationAndSubject(Automation $automation, Subject $subject): int { |
| 135 | global $wpdb; |
| 136 | |
| 137 | $result = $wpdb->get_col( |
| 138 | $wpdb->prepare( |
| 139 | ' |
| 140 | SELECT count(DISTINCT runs.id) AS count FROM %i AS runs |
| 141 | JOIN %i AS subjects ON runs.id = subjects.automation_run_id |
| 142 | WHERE runs.automation_id = %d |
| 143 | AND subjects.hash = %s |
| 144 | AND subjects.key = %s |
| 145 | ', |
| 146 | $this->table, |
| 147 | $this->subjectTable, |
| 148 | $automation->getId(), |
| 149 | $subject->getHash(), |
| 150 | $subject->getKey() |
| 151 | ) |
| 152 | ); |
| 153 | |
| 154 | return $result ? (int)current($result) : 0; |
| 155 | } |
| 156 | |
| 157 | public function getCountByAutomationTriggerAndSubject(Automation $automation, string $triggerKey, Subject $subject): int { |
| 158 | global $wpdb; |
| 159 | |
| 160 | $result = $wpdb->get_col( |
| 161 | $wpdb->prepare( |
| 162 | ' |
| 163 | SELECT count(DISTINCT runs.id) AS count FROM %i AS runs |
| 164 | JOIN %i AS subjects ON runs.id = subjects.automation_run_id |
| 165 | WHERE runs.automation_id = %d |
| 166 | AND runs.trigger_key = %s |
| 167 | AND subjects.hash = %s |
| 168 | AND subjects.key = %s |
| 169 | ', |
| 170 | $this->table, |
| 171 | $this->subjectTable, |
| 172 | $automation->getId(), |
| 173 | $triggerKey, |
| 174 | $subject->getHash(), |
| 175 | $subject->getKey() |
| 176 | ) |
| 177 | ); |
| 178 | |
| 179 | return $result ? (int)current($result) : 0; |
| 180 | } |
| 181 | |
| 182 | public function getCountForAutomation(Automation $automation, string ...$status): int { |
| 183 | global $wpdb; |
| 184 | |
| 185 | if (!count($status)) { |
| 186 | $result = $wpdb->get_col( |
| 187 | $wpdb->prepare( |
| 188 | 'SELECT COUNT(id) as count FROM %i WHERE automation_id = %d', |
| 189 | $this->table, |
| 190 | $automation->getId() |
| 191 | ) |
| 192 | ); |
| 193 | return $result ? (int)current($result) : 0; |
| 194 | } |
| 195 | |
| 196 | $result = $wpdb->get_col( |
| 197 | // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The number of replacements is dynamic. |
| 198 | $wpdb->prepare( |
| 199 | ' |
| 200 | SELECT COUNT(id) as count |
| 201 | FROM %i |
| 202 | WHERE automation_id = %d |
| 203 | AND status IN (' . implode(',', array_fill(0, count($status), '%s')) . ') |
| 204 | ', |
| 205 | $this->table, |
| 206 | $automation->getId(), |
| 207 | ...$status |
| 208 | ) |
| 209 | ); |
| 210 | return $result ? (int)current($result) : 0; |
| 211 | } |
| 212 | |
| 213 | /** |
| 214 | * @return array<int, AutomationRun|null> |
| 215 | */ |
| 216 | public function getLastAutomationRunsForAutomations(Automation ...$automations): array { |
| 217 | global $wpdb; |
| 218 | |
| 219 | if (!count($automations)) { |
| 220 | return []; |
| 221 | } |
| 222 | |
| 223 | $automationIds = array_map(function (Automation $automation) { |
| 224 | return $automation->getId(); |
| 225 | }, $automations); |
| 226 | |
| 227 | // Using a subquery to get the max ID per automation_id (most recent run) |
| 228 | // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The number of replacements is dynamic. |
| 229 | $runs = $wpdb->get_results( |
| 230 | $wpdb->prepare( |
| 231 | ' |
| 232 | SELECT r.* |
| 233 | FROM %i AS r |
| 234 | INNER JOIN ( |
| 235 | SELECT automation_id, MAX(id) AS max_id |
| 236 | FROM %i |
| 237 | WHERE automation_id IN (' . implode(',', array_fill(0, count($automationIds), '%d')) . ') |
| 238 | GROUP BY automation_id |
| 239 | ) AS latest ON r.id = latest.max_id |
| 240 | ', |
| 241 | array_merge([$this->table, $this->table], $automationIds) |
| 242 | ), |
| 243 | ARRAY_A |
| 244 | ); |
| 245 | |
| 246 | if (!is_array($runs) || !$runs) { |
| 247 | return array_fill_keys($automationIds, null); |
| 248 | } |
| 249 | |
| 250 | // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The number of replacements is dynamic. |
| 251 | $subjects = $wpdb->get_results( |
| 252 | $wpdb->prepare( |
| 253 | ' |
| 254 | SELECT * |
| 255 | FROM %i |
| 256 | WHERE automation_run_id IN (' . implode(',', array_fill(0, count($runs), '%d')) . ') |
| 257 | ', |
| 258 | array_merge([$this->subjectTable], array_column($runs, 'id')) |
| 259 | ), |
| 260 | ARRAY_A |
| 261 | ); |
| 262 | |
| 263 | $result = array_fill_keys($automationIds, null); |
| 264 | |
| 265 | foreach ($runs as $runData) { |
| 266 | if (!is_array($runData)) { |
| 267 | continue; |
| 268 | } |
| 269 | $runId = is_numeric($runData['id'] ?? null) ? (int)$runData['id'] : 0; |
| 270 | $runData['subjects'] = array_values(array_filter( |
| 271 | is_array($subjects) ? $subjects : [], |
| 272 | function ($subjectData) use ($runId): bool { |
| 273 | if (!is_array($subjectData)) { |
| 274 | return false; |
| 275 | } |
| 276 | $subjectRunId = $subjectData['automation_run_id'] ?? null; |
| 277 | return is_numeric($subjectRunId) && (int)$subjectRunId === $runId; |
| 278 | } |
| 279 | )); |
| 280 | $automationId = is_numeric($runData['automation_id'] ?? null) ? (int)$runData['automation_id'] : 0; |
| 281 | $result[$automationId] = AutomationRun::fromArray($runData); |
| 282 | } |
| 283 | |
| 284 | return $result; |
| 285 | } |
| 286 | |
| 287 | public function updateStatus(int $id, string $status): void { |
| 288 | global $wpdb; |
| 289 | $result = $wpdb->query( |
| 290 | $wpdb->prepare( |
| 291 | ' |
| 292 | UPDATE %i |
| 293 | SET status = %s, updated_at = current_timestamp() |
| 294 | WHERE id = %d |
| 295 | ', |
| 296 | $this->table, |
| 297 | $status, |
| 298 | $id |
| 299 | ) |
| 300 | ); |
| 301 | if ($result === false) { |
| 302 | $this->throwDatabaseError(); |
| 303 | } |
| 304 | } |
| 305 | |
| 306 | public function updateNextStep(int $id, ?string $nextStepId): void { |
| 307 | global $wpdb; |
| 308 | $result = $wpdb->query( |
| 309 | $wpdb->prepare( |
| 310 | ' |
| 311 | UPDATE %i |
| 312 | SET next_step_id = %s, updated_at = current_timestamp() |
| 313 | WHERE id = %d |
| 314 | ', |
| 315 | $this->table, |
| 316 | $nextStepId, |
| 317 | $id |
| 318 | ) |
| 319 | ); |
| 320 | if ($result === false) { |
| 321 | $this->throwDatabaseError(); |
| 322 | } |
| 323 | } |
| 324 | |
| 325 | public function getNextStepId(int $id): ?string { |
| 326 | global $wpdb; |
| 327 | $result = $wpdb->get_row( |
| 328 | $wpdb->prepare( |
| 329 | 'SELECT next_step_id FROM %i WHERE id = %d', |
| 330 | $this->table, |
| 331 | $id |
| 332 | ), |
| 333 | ARRAY_A |
| 334 | ); |
| 335 | if (!is_array($result) || !array_key_exists('next_step_id', $result)) { |
| 336 | return null; |
| 337 | } |
| 338 | $value = $result['next_step_id']; |
| 339 | return $value !== null ? (string)$value : null; |
| 340 | } |
| 341 | |
| 342 | public function getAutomationStepStatisticForTimeFrame(int $automationId, string $status, \DateTimeImmutable $after, \DateTimeImmutable $before, ?int $versionId = null): array { |
| 343 | global $wpdb; |
| 344 | $andWhere = $versionId ? 'AND version_id = %d' : ''; |
| 345 | $result = $wpdb->get_results( |
| 346 | // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The number of replacements is dynamic. |
| 347 | $wpdb->prepare( |
| 348 | ' |
| 349 | SELECT COUNT(id) AS count, next_step_id |
| 350 | FROM %i AS log |
| 351 | WHERE automation_id = %d AND status = %s AND created_at BETWEEN %s AND %s |
| 352 | ' . $andWhere . /* phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The condition uses placeholders. */ ' |
| 353 | GROUP BY next_step_id |
| 354 | ', |
| 355 | array_merge( |
| 356 | [ |
| 357 | $this->table, |
| 358 | $automationId, |
| 359 | $status, |
| 360 | $after->format('Y-m-d H:i:s'), |
| 361 | $before->format('Y-m-d H:i:s'), |
| 362 | ], |
| 363 | $versionId ? [$versionId] : [] |
| 364 | ) |
| 365 | ), |
| 366 | ARRAY_A |
| 367 | ); |
| 368 | return is_array($result) ? $result : []; |
| 369 | } |
| 370 | |
| 371 | public function truncate(): void { |
| 372 | global $wpdb; |
| 373 | $wpdb->query($wpdb->prepare('TRUNCATE %i', $this->table)); |
| 374 | $wpdb->query($wpdb->prepare('TRUNCATE %i', $this->subjectTable)); |
| 375 | } |
| 376 | |
| 377 | private function throwDatabaseError(): void { |
| 378 | global $wpdb; |
| 379 | throw Exceptions::databaseError($wpdb->last_error); // phpcs:ignore Squiz.NamingConventions.ValidVariableName.MemberNotCamelCaps |
| 380 | } |
| 381 | } |
| 382 |