| 1 |
<?php |
| 2 |
|
| 3 |
namespace IvyForms\Repository\Payment; |
| 4 |
|
| 5 |
use IvyForms\Common\Exceptions\QueryExecutionException; |
| 6 |
use IvyForms\Entity\Payment\Payment; |
| 7 |
use IvyForms\Factory\Payment\PaymentFactory; |
| 8 |
use IvyForms\Repository\AbstractRepository; |
| 9 |
use IvyForms\Services\InstallActions\DB\EntryField\EntryFieldsTable; |
| 10 |
use IvyForms\Services\InstallActions\DB\Field\FieldsTable; |
| 11 |
use IvyForms\Services\InstallActions\DB\Form\FormsTable; |
| 12 |
use IvyForms\Services\Translations\BackendStrings; |
| 13 |
|
| 14 |
class PaymentRepository extends AbstractRepository implements PaymentRepositoryInterface |
| 15 |
{ |
| 16 |
public const FACTORY = PaymentFactory::class; |
| 17 |
|
| 18 |
/** |
| 19 |
* @param Payment $entity |
| 20 |
* @return int |
| 21 |
* @throws QueryExecutionException |
| 22 |
*/ |
| 23 |
public function add($entity): int |
| 24 |
{ |
| 25 |
$data = $entity->toArray(); |
| 26 |
$transactionId = (string) ($data['transactionId'] ?? ''); |
| 27 |
|
| 28 |
if ($transactionId === '' || $this->existsByTransactionId($transactionId)) { |
| 29 |
throw new QueryExecutionException( |
| 30 |
BackendStrings::getPaymentStrings()['duplicate_payment_transaction'] |
| 31 |
); |
| 32 |
} |
| 33 |
|
| 34 |
$entryId = $data['entryId'] ?? null; |
| 35 |
$this->writeDroppingUnknownColumns([ |
| 36 |
'entryId' => $entryId !== null && (int) $entryId > 0 ? (int) $entryId : null, |
| 37 |
'formId' => $data['formId'], |
| 38 |
'transactionId' => $transactionId, |
| 39 |
'paymentMethod' => $data['paymentMethod'], |
| 40 |
'customerName' => $data['customerName'] ?? null, |
| 41 |
'amount' => $data['amount'], |
| 42 |
'currency' => $data['currency'], |
| 43 |
'squareReceiptUrl' => $data['squareReceiptUrl'], |
| 44 |
'status' => $data['status'] ?? 'succeeded', |
| 45 |
'providerMeta' => $data['providerMeta'] ?? null, |
| 46 |
'dateCreated' => current_time('mysql'), |
| 47 |
], 0); |
| 48 |
|
| 49 |
return (int) $this->wpdb->insert_id; |
| 50 |
} |
| 51 |
|
| 52 |
public function getByEntryId(int $entryId): ?Payment |
| 53 |
{ |
| 54 |
$row = $this->wpdb->get_row( |
| 55 |
$this->wpdb->prepare( |
| 56 |
$this->selectQuery() . " WHERE {$this->table}.entryId = %d LIMIT 1", |
| 57 |
$entryId |
| 58 |
), |
| 59 |
ARRAY_A |
| 60 |
); |
| 61 |
|
| 62 |
return $this->createFromRow($row); |
| 63 |
} |
| 64 |
|
| 65 |
public function existsByTransactionId(string $transactionId): bool |
| 66 |
{ |
| 67 |
if ($transactionId === '') { |
| 68 |
return false; |
| 69 |
} |
| 70 |
|
| 71 |
$found = $this->wpdb->get_var( |
| 72 |
$this->wpdb->prepare( |
| 73 |
"SELECT id FROM {$this->table} WHERE transactionId = %s LIMIT 1", |
| 74 |
$transactionId |
| 75 |
) |
| 76 |
); |
| 77 |
|
| 78 |
return $found !== null; |
| 79 |
} |
| 80 |
|
| 81 |
public function getByTransactionId(string $transactionId): ?Payment |
| 82 |
{ |
| 83 |
if ($transactionId === '') { |
| 84 |
return null; |
| 85 |
} |
| 86 |
|
| 87 |
$row = $this->wpdb->get_row( |
| 88 |
$this->wpdb->prepare( |
| 89 |
$this->selectQuery() . " WHERE {$this->table}.transactionId = %s LIMIT 1", |
| 90 |
$transactionId |
| 91 |
), |
| 92 |
ARRAY_A |
| 93 |
); |
| 94 |
|
| 95 |
return $this->createFromRow($row); |
| 96 |
} |
| 97 |
|
| 98 |
public function findByProviderOrderId(string $orderId): ?Payment |
| 99 |
{ |
| 100 |
if ($orderId === '') { |
| 101 |
return null; |
| 102 |
} |
| 103 |
|
| 104 |
$like = '%' . $this->wpdb->esc_like('"orderId":"' . $orderId . '"') . '%'; |
| 105 |
$row = $this->wpdb->get_row( |
| 106 |
$this->wpdb->prepare( |
| 107 |
$this->selectQuery() . " WHERE {$this->table}.providerMeta LIKE %s LIMIT 1", |
| 108 |
$like |
| 109 |
), |
| 110 |
ARRAY_A |
| 111 |
); |
| 112 |
|
| 113 |
return $this->createFromRow($row); |
| 114 |
} |
| 115 |
|
| 116 |
public function updateStatus(int $id, string $status): bool |
| 117 |
{ |
| 118 |
if ($id <= 0 || $status === '') { |
| 119 |
return false; |
| 120 |
} |
| 121 |
|
| 122 |
$result = $this->wpdb->update( |
| 123 |
$this->table, |
| 124 |
['status' => $status], |
| 125 |
['id' => $id], |
| 126 |
['%s'], |
| 127 |
['%d'] |
| 128 |
); |
| 129 |
|
| 130 |
return $result !== false; |
| 131 |
} |
| 132 |
|
| 133 |
public function update(Payment $entity): void |
| 134 |
{ |
| 135 |
$data = $entity->toArray(); |
| 136 |
$id = (int) ($data['id'] ?? 0); |
| 137 |
if ($id <= 0) { |
| 138 |
throw new QueryExecutionException( |
| 139 |
BackendStrings::getExceptionStrings()['unable_to_add_data'] . __CLASS__ |
| 140 |
); |
| 141 |
} |
| 142 |
|
| 143 |
$entryId = $data['entryId'] ?? null; |
| 144 |
$this->writeDroppingUnknownColumns( |
| 145 |
[ |
| 146 |
'entryId' => $entryId !== null && (int) $entryId > 0 ? (int) $entryId : null, |
| 147 |
'formId' => $data['formId'], |
| 148 |
'transactionId' => $data['transactionId'], |
| 149 |
'paymentMethod' => $data['paymentMethod'], |
| 150 |
'customerName' => $data['customerName'] ?? null, |
| 151 |
'amount' => $data['amount'], |
| 152 |
'currency' => $data['currency'], |
| 153 |
'squareReceiptUrl' => $data['squareReceiptUrl'], |
| 154 |
'status' => $data['status'] ?? 'succeeded', |
| 155 |
'providerMeta' => $data['providerMeta'] ?? null, |
| 156 |
], |
| 157 |
$id |
| 158 |
); |
| 159 |
} |
| 160 |
|
| 161 |
/** |
| 162 |
* @param array<string, mixed>|null $params |
| 163 |
* @return array<string, mixed> |
| 164 |
*/ |
| 165 |
public function search(?array $params): array |
| 166 |
{ |
| 167 |
$formsTable = FormsTable::getTableName(); |
| 168 |
$paymentsTable = $this->table; |
| 169 |
$queryParams = []; |
| 170 |
$whereClauses = $this->buildPaymentWhereClauses($params, $queryParams); |
| 171 |
|
| 172 |
$page = max(1, (int) ($params['page'] ?? 1)); |
| 173 |
$perPage = max(1, min(100, (int) ($params['perPage'] ?? 20))); |
| 174 |
$offset = ($page - 1) * $perPage; |
| 175 |
|
| 176 |
$orderBy = $this->resolveOrderColumn($params); |
| 177 |
$order = strtoupper((string) ($params['order'] ?? 'DESC')) === 'ASC' ? 'ASC' : 'DESC'; |
| 178 |
|
| 179 |
$sql = "SELECT p.*, f.name AS formName, f.integrationSettings AS formIntegrationSettings |
| 180 |
FROM {$paymentsTable} p |
| 181 |
LEFT JOIN {$formsTable} f ON f.id = p.formId |
| 182 |
WHERE {$whereClauses} |
| 183 |
ORDER BY {$orderBy} {$order} |
| 184 |
LIMIT %d OFFSET %d"; |
| 185 |
|
| 186 |
$mainParams = array_merge($queryParams, [$perPage, $offset]); |
| 187 |
$results = $this->wpdb->get_results($this->wpdb->prepare($sql, $mainParams), ARRAY_A); |
| 188 |
|
| 189 |
if ($results === false) { |
| 190 |
throw new QueryExecutionException( |
| 191 |
BackendStrings::getExceptionStrings()['unable_search_entries'] . __CLASS__ |
| 192 |
); |
| 193 |
} |
| 194 |
|
| 195 |
$countSql = "SELECT COUNT(*) FROM {$paymentsTable} p |
| 196 |
LEFT JOIN {$formsTable} f ON f.id = p.formId |
| 197 |
WHERE {$whereClauses}"; |
| 198 |
$total = empty($queryParams) |
| 199 |
? (int) $this->wpdb->get_var($countSql) |
| 200 |
: (int) $this->wpdb->get_var($this->wpdb->prepare($countSql, $queryParams)); |
| 201 |
|
| 202 |
return [ |
| 203 |
'data' => is_array($results) ? $results : [], |
| 204 |
'meta' => [ |
| 205 |
'page' => $page, |
| 206 |
'perPage' => $perPage, |
| 207 |
'total' => $total, |
| 208 |
], |
| 209 |
]; |
| 210 |
} |
| 211 |
|
| 212 |
/** |
| 213 |
* @param array<string, mixed>|null $params |
| 214 |
* @return int |
| 215 |
*/ |
| 216 |
public function getCount(?array $params): int |
| 217 |
{ |
| 218 |
$formsTable = FormsTable::getTableName(); |
| 219 |
$paymentsTable = $this->table; |
| 220 |
$queryParams = []; |
| 221 |
$whereClauses = $this->buildPaymentWhereClauses($params, $queryParams); |
| 222 |
|
| 223 |
$countSql = "SELECT COUNT(*) FROM {$paymentsTable} p |
| 224 |
LEFT JOIN {$formsTable} f ON f.id = p.formId |
| 225 |
WHERE {$whereClauses}"; |
| 226 |
|
| 227 |
return empty($queryParams) |
| 228 |
? (int) $this->wpdb->get_var($countSql) |
| 229 |
: (int) $this->wpdb->get_var($this->wpdb->prepare($countSql, $queryParams)); |
| 230 |
} |
| 231 |
|
| 232 |
/** |
| 233 |
* @param array<string, mixed>|null $params |
| 234 |
* @param array<int|string, mixed> $queryParams |
| 235 |
*/ |
| 236 |
private function buildPaymentWhereClauses(?array $params, array &$queryParams): string |
| 237 |
{ |
| 238 |
$whereClauses = ['1=1']; |
| 239 |
|
| 240 |
if (!empty($params['search'])) { |
| 241 |
$searchEscaped = '%' . $this->wpdb->esc_like($params['search']) . '%'; |
| 242 |
$whereClauses[] = '(p.transactionId LIKE %s OR f.name LIKE %s OR ' |
| 243 |
. $this->customerNameSearchSql() |
| 244 |
. ')'; |
| 245 |
$queryParams[] = $searchEscaped; |
| 246 |
$queryParams[] = $searchEscaped; |
| 247 |
$queryParams[] = $searchEscaped; |
| 248 |
$queryParams[] = $searchEscaped; |
| 249 |
} |
| 250 |
|
| 251 |
if (!empty($params['filters']['formId'])) { |
| 252 |
$whereClauses[] = 'p.formId = %d'; |
| 253 |
$queryParams[] = (int) $params['filters']['formId']; |
| 254 |
} |
| 255 |
|
| 256 |
if (!empty($params['filters']['status'])) { |
| 257 |
$whereClauses[] = 'p.status = %s'; |
| 258 |
$queryParams[] = sanitize_key((string) $params['filters']['status']); |
| 259 |
} |
| 260 |
|
| 261 |
if (!empty($params['dateRange']) && !empty($params['dateRange'][0])) { |
| 262 |
$dateRange = $params['dateRange']; |
| 263 |
if (empty($dateRange[1])) { |
| 264 |
$dateRange[1] = $dateRange[0]; |
| 265 |
} |
| 266 |
$start = substr((string) $dateRange[0], 0, 10) . ' 00:00:00'; |
| 267 |
$end = substr((string) $dateRange[1], 0, 10) . ' 23:59:59'; |
| 268 |
$whereClauses[] = 'p.dateCreated >= %s'; |
| 269 |
$queryParams[] = $start; |
| 270 |
$whereClauses[] = 'p.dateCreated <= %s'; |
| 271 |
$queryParams[] = $end; |
| 272 |
} |
| 273 |
|
| 274 |
return implode(' AND ', $whereClauses); |
| 275 |
} |
| 276 |
|
| 277 |
/** |
| 278 |
* Matches persisted customerName or the same entry name/email fields used |
| 279 |
* when that column is empty. Two %s placeholders. |
| 280 |
*/ |
| 281 |
private function customerNameSearchSql(): string |
| 282 |
{ |
| 283 |
$entryFieldsTable = EntryFieldsTable::getTableName(); |
| 284 |
$fieldsTable = FieldsTable::getTableName(); |
| 285 |
|
| 286 |
return "(p.customerName LIKE %s |
| 287 |
OR ( |
| 288 |
(p.customerName IS NULL OR TRIM(p.customerName) = '') |
| 289 |
AND p.entryId > 0 |
| 290 |
AND EXISTS ( |
| 291 |
SELECT 1 |
| 292 |
FROM {$entryFieldsTable} ef |
| 293 |
INNER JOIN {$fieldsTable} fld ON fld.id = ef.fieldId |
| 294 |
WHERE ef.entryId = p.entryId |
| 295 |
AND (fld.parentId IS NULL OR fld.parentId = 0) |
| 296 |
AND fld.type IN ('name', 'email') |
| 297 |
AND ef.fieldValue LIKE %s |
| 298 |
) |
| 299 |
))"; |
| 300 |
} |
| 301 |
|
| 302 |
/** |
| 303 |
* @param array<string, mixed>|null $params |
| 304 |
*/ |
| 305 |
private function resolveOrderColumn(?array $params): string |
| 306 |
{ |
| 307 |
$orderBy = (string) ($params['orderBy'] ?? 'dateCreated'); |
| 308 |
$map = [ |
| 309 |
'id' => 'p.id', |
| 310 |
'transactionId' => 'p.transactionId', |
| 311 |
'formName' => 'f.name', |
| 312 |
'customerName' => 'p.customerName', |
| 313 |
'amount' => 'p.amount', |
| 314 |
'paymentMethod' => 'p.paymentMethod', |
| 315 |
'status' => 'p.status', |
| 316 |
'dateCreated' => 'p.dateCreated', |
| 317 |
]; |
| 318 |
|
| 319 |
return $map[$orderBy] ?? 'p.dateCreated'; |
| 320 |
} |
| 321 |
|
| 322 |
public function selectQuery(): string |
| 323 |
{ |
| 324 |
return "SELECT {$this->table}.* FROM {$this->table}"; |
| 325 |
} |
| 326 |
|
| 327 |
/** |
| 328 |
* @param array<string, mixed> $row |
| 329 |
*/ |
| 330 |
private function writeDroppingUnknownColumns(array $row, int $existingId): void |
| 331 |
{ |
| 332 |
$formatMap = [ |
| 333 |
'entryId' => '%d', |
| 334 |
'formId' => '%d', |
| 335 |
'transactionId' => '%s', |
| 336 |
'paymentMethod' => '%s', |
| 337 |
'customerName' => '%s', |
| 338 |
'amount' => '%d', |
| 339 |
'currency' => '%s', |
| 340 |
'squareReceiptUrl' => '%s', |
| 341 |
'status' => '%s', |
| 342 |
'providerMeta' => '%s', |
| 343 |
'dateCreated' => '%s', |
| 344 |
]; |
| 345 |
|
| 346 |
while ($row !== []) { |
| 347 |
$formats = []; |
| 348 |
foreach (array_keys($row) as $column) { |
| 349 |
$formats[] = $formatMap[$column] ?? '%s'; |
| 350 |
} |
| 351 |
|
| 352 |
$result = $existingId > 0 |
| 353 |
? $this->wpdb->update($this->table, $row, ['id' => $existingId], $formats, ['%d']) |
| 354 |
: $this->wpdb->insert($this->table, $row, $formats); |
| 355 |
if ($result !== false) { |
| 356 |
return; |
| 357 |
} |
| 358 |
|
| 359 |
if ( |
| 360 |
preg_match("/Unknown column '([^']+)'/", (string) $this->wpdb->last_error, $matches) !== 1 |
| 361 |
|| !array_key_exists($matches[1], $row) |
| 362 |
) { |
| 363 |
break; |
| 364 |
} |
| 365 |
|
| 366 |
unset($row[$matches[1]]); |
| 367 |
} |
| 368 |
|
| 369 |
$dbError = (string) $this->wpdb->last_error; |
| 370 |
throw new QueryExecutionException( |
| 371 |
BackendStrings::getExceptionStrings()['unable_to_add_data'] . __CLASS__ . ': ' . $dbError |
| 372 |
); |
| 373 |
} |
| 374 |
|
| 375 |
/** |
| 376 |
* @param array<string, mixed>|null $row |
| 377 |
*/ |
| 378 |
private function createFromRow(?array $row): ?Payment |
| 379 |
{ |
| 380 |
return empty($row) ? null : PaymentFactory::create($row); |
| 381 |
} |
| 382 |
|
| 383 |
/** |
| 384 |
* @return string[] |
| 385 |
*/ |
| 386 |
protected function getAllowedForeignColumns(): array |
| 387 |
{ |
| 388 |
return ['formId', 'entryId']; |
| 389 |
} |
| 390 |
|
| 391 |
protected function getDateColumn(): string |
| 392 |
{ |
| 393 |
return 'dateCreated'; |
| 394 |
} |
| 395 |
} |
| 396 |
|
