WebOrbiton
v1.1.0.0

StocketBase

473 lines · 19.1 KB
  1. <?php
  2. ​
  3. declare(strict_types=1);
  4. ​
  5. require_once __DIR__ . '/database.php';
  6. require_once __DIR__ . '/site-front.php';
  7. require_once __DIR__ . '/shop.php';
  8. require_once __DIR__ . '/digital-files.php';
  9. require_once __DIR__ . '/licenses.php';
  10. ​
  11. final class Orders
  12. {
  13. public const PAID_STATUSES = ['paid', 'processing', 'fulfilled', 'shipped', 'completed'];
  14. public const CLOSED_STATUSES = ['canceled', 'refunded', 'failed'];
  15. ​
  16. public static function find(int $orderId): ?array
  17. {
  18. if ($orderId <= 0) {
  19. return null;
  20. }
  21. $statement = Database::users()->prepare('SELECT * FROM orders WHERE id = :id LIMIT 1');
  22. $statement->execute(['id' => $orderId]);
  23. ​
  24. return $statement->fetch() ?: null;
  25. }
  26. ​
  27. public static function items(int $orderId): array
  28. {
  29. $statement = Database::users()->prepare('SELECT * FROM order_items WHERE order_id = :id ORDER BY id ASC');
  30. $statement->execute(['id' => $orderId]);
  31. ​
  32. return $statement->fetchAll();
  33. }
  34. ​
  35. public static function history(int $orderId): array
  36. {
  37. $statement = Database::users()->prepare('SELECT * FROM order_status_history WHERE order_id = :id ORDER BY created_at ASC, id ASC');
  38. $statement->execute(['id' => $orderId]);
  39. ​
  40. return $statement->fetchAll();
  41. }
  42. ​
  43. public static function findWithToken(int $orderId, string $token): ?array
  44. {
  45. $token = strtolower(trim($token));
  46. if (preg_match('/^[a-f0-9]{64}$/', $token) !== 1) {
  47. return null;
  48. }
  49. $order = self::find($orderId);
  50. if ($order === null || empty($order['access_token']) || !hash_equals((string) $order['access_token'], $token)) {
  51. return null;
  52. }
  53. ​
  54. return $order;
  55. }
  56. ​
  57. public static function ensureAccessToken(array $order): array
  58. {
  59. if (!empty($order['access_token'])) {
  60. return $order;
  61. }
  62. $token = bin2hex(random_bytes(32));
  63. $update = Database::users()->prepare('UPDATE orders SET access_token = :token WHERE id = :id AND access_token IS NULL');
  64. $update->execute(['token' => $token, 'id' => (int) $order['id']]);
  65. if ($update->rowCount() > 0) {
  66. $order['access_token'] = $token;
  67. ​
  68. return $order;
  69. }
  70. ​
  71. return self::find((int) $order['id']) ?? $order;
  72. }
  73. ​
  74. public static function customerEmail(array $order): ?string
  75. {
  76. if ($order['user_account_id'] !== null) {
  77. $statement = Database::users()->prepare('SELECT email FROM user_accounts WHERE id = :id LIMIT 1');
  78. $statement->execute(['id' => (int) $order['user_account_id']]);
  79. $email = (string) $statement->fetchColumn();
  80. if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
  81. return $email;
  82. }
  83. }
  84. $guest = (string) ($order['guest_email'] ?? '');
  85. ​
  86. return $guest !== '' && filter_var($guest, FILTER_VALIDATE_EMAIL) ? $guest : null;
  87. }
  88. ​
  89. public static function addHistory(int $orderId, string $status, ?string $note = null, ?int $staffId = null): void
  90. {
  91. Database::users()->prepare(
  92. 'INSERT INTO order_status_history (order_id, status, note, changed_by_team_account_id) VALUES (:order_id, :status, :note, :staff_id)'
  93. )->execute([
  94. 'order_id' => $orderId,
  95. 'status' => $status,
  96. 'note' => $note !== null ? mb_substr($note, 0, 500) : null,
  97. 'staff_id' => $staffId,
  98. ]);
  99. }
  100. ​
  101. public static function reserveStock(int $orderId): void
  102. {
  103. $order = self::find($orderId);
  104. if ($order === null || (int) ($order['stock_reserved'] ?? 0) === 1) {
  105. return;
  106. }
  107. $claim = Database::users()->prepare('UPDATE orders SET stock_reserved = 1 WHERE id = :id AND stock_reserved = 0');
  108. $claim->execute(['id' => $orderId]);
  109. if ($claim->rowCount() === 0) {
  110. return;
  111. }
  112. $lowStock = [];
  113. self::adjustStock(self::items($orderId), -1, $lowStock);
  114. self::alertLowStock($lowStock);
  115. }
  116. ​
  117. public static function markPaid(int $orderId, array $payment = [], string $note = 'Payment confirmed', ?int $staffId = null, ?string $finalStatus = null): bool
  118. {
  119. $usersDb = Database::users();
  120. $update = $usersDb->prepare(
  121. "UPDATE orders SET status = 'paid', paid_at = COALESCE(paid_at, NOW()), canceled_at = NULL,
  122. provider = COALESCE(:provider, provider),
  123. provider_checkout_id = COALESCE(:checkout_id, provider_checkout_id),
  124. provider_payment_id = COALESCE(:payment_id, provider_payment_id),
  125. provider_customer_id = COALESCE(:customer_id, provider_customer_id)
  126. WHERE id = :id AND status IN ('pending', 'failed')"
  127. );
  128. $update->execute([
  129. 'provider' => self::nullable($payment['provider'] ?? null),
  130. 'checkout_id' => self::nullable($payment['checkout_id'] ?? null),
  131. 'payment_id' => self::nullable($payment['payment_id'] ?? null),
  132. 'customer_id' => self::nullable($payment['customer_id'] ?? null),
  133. 'id' => $orderId,
  134. ]);
  135. if ($update->rowCount() === 0) {
  136. return false;
  137. }
  138. ​
  139. self::addHistory($orderId, 'paid', $note, $staffId);
  140. ​
  141. $order = self::ensureAccessToken(self::find($orderId) ?? []);
  142. $items = self::items($orderId);
  143. $siteDb = Database::site();
  144. ​
  145. $lowStock = [];
  146. $claim = $usersDb->prepare('UPDATE orders SET stock_reserved = 1 WHERE id = :id AND stock_reserved = 0');
  147. $claim->execute(['id' => $orderId]);
  148. if ($claim->rowCount() > 0) {
  149. self::adjustStock($items, -1, $lowStock);
  150. }
  151. ​
  152. foreach ($items as $item) {
  153. $siteDb->prepare('UPDATE products SET sales_count = sales_count + :qty WHERE id = :id')
  154. ->execute(['qty' => (int) $item['quantity'], 'id' => (int) $item['product_id']]);
  155. }
  156. ​
  157. if (!empty($order['discount_code'])) {
  158. $siteDb->prepare('UPDATE discounts SET times_redeemed = times_redeemed + 1 WHERE UPPER(code) = UPPER(:code)')
  159. ->execute(['code' => $order['discount_code']]);
  160. }
  161. ​
  162. self::assignInvoiceNumber($orderId);
  163. ​
  164. $downloadLinks = [];
  165. try {
  166. $downloadLinks = DigitalFiles::grantsForOrder($usersDb, $order, $items);
  167. } catch (Throwable $exception) {
  168. error_log('StocketBase: could not create download access for order ' . $orderId . ': ' . $exception->getMessage());
  169. }
  170. ​
  171. $licenseResult = ['keys' => [], 'missing' => []];
  172. try {
  173. $licenseResult = Licenses::assignForOrder($order, $items);
  174. } catch (Throwable $exception) {
  175. error_log('StocketBase: could not assign license keys for order ' . $orderId . ': ' . $exception->getMessage());
  176. }
  177. if (!empty($licenseResult['missing'])) {
  178. self::addHistory($orderId, 'paid', 'Not enough license keys in the pool. Add keys and use "Send keys again".', null);
  179. }
  180. ​
  181. if ($finalStatus !== null && $finalStatus !== 'paid' && in_array($finalStatus, self::PAID_STATUSES, true)) {
  182. $usersDb->prepare('UPDATE orders SET status = :status WHERE id = :id')->execute(['status' => $finalStatus, 'id' => $orderId]);
  183. self::addHistory($orderId, $finalStatus, null, $staffId);
  184. }
  185. ​
  186. $order = self::find($orderId) ?? $order;
  187. ​
  188. self::mailSafely(function () use ($order, $items, $downloadLinks, $licenseResult): void {
  189. $email = self::customerEmail($order);
  190. if ($email !== null) {
  191. $message = MailTemplates::orderConfirmationEmail($order, $items, $downloadLinks, $licenseResult['keys'], Shop::orderUrl($order));
  192. Mailer::send($email, $message['subject'], $message['html']);
  193. }
  194. });
  195. self::notifyAdmin($order, $items, !empty($licenseResult['missing']));
  196. self::alertLowStock($lowStock);
  197. ​
  198. return true;
  199. }
  200. ​
  201. public static function close(int $orderId, string $status, ?string $note = null, ?int $staffId = null, bool $restock = true): bool
  202. {
  203. if (!in_array($status, self::CLOSED_STATUSES, true)) {
  204. return false;
  205. }
  206. $order = self::find($orderId);
  207. if ($order === null || $order['status'] === $status) {
  208. return false;
  209. }
  210. ​
  211. $usersDb = Database::users();
  212. $usersDb->prepare('UPDATE orders SET status = :status, canceled_at = COALESCE(canceled_at, NOW()), updated_at = NOW() WHERE id = :id')
  213. ->execute(['status' => $status, 'id' => $orderId]);
  214. self::addHistory($orderId, $status, $note, $staffId);
  215. ​
  216. DigitalFiles::revokeGrantsForOrder($orderId);
  217. Licenses::revokeForOrder($orderId);
  218. ​
  219. if ($restock && (int) ($order['stock_reserved'] ?? 0) === 1) {
  220. $release = $usersDb->prepare('UPDATE orders SET stock_reserved = 0 WHERE id = :id AND stock_reserved = 1');
  221. $release->execute(['id' => $orderId]);
  222. if ($release->rowCount() > 0) {
  223. $ignored = [];
  224. self::adjustStock(self::items($orderId), 1, $ignored);
  225. }
  226. }
  227. ​
  228. if (in_array($order['status'], self::PAID_STATUSES, true)) {
  229. foreach (self::items($orderId) as $item) {
  230. Database::site()->prepare('UPDATE products SET sales_count = GREATEST(0, CAST(sales_count AS SIGNED) - :qty) WHERE id = :id')
  231. ->execute(['qty' => (int) $item['quantity'], 'id' => (int) $item['product_id']]);
  232. }
  233. }
  234. ​
  235. return true;
  236. }
  237. ​
  238. public static function resendConfirmation(int $orderId): bool
  239. {
  240. $order = self::find($orderId);
  241. if ($order === null || !in_array($order['status'], self::PAID_STATUSES, true)) {
  242. return false;
  243. }
  244. $order = self::ensureAccessToken($order);
  245. $items = self::items($orderId);
  246. $email = self::customerEmail($order);
  247. if ($email === null) {
  248. return false;
  249. }
  250. ​
  251. $usersDb = Database::users();
  252. $downloadLinks = DigitalFiles::grantsForOrder($usersDb, $order, $items);
  253. $licenseResult = Licenses::assignForOrder($order, $items);
  254. $message = MailTemplates::orderConfirmationEmail($order, $items, $downloadLinks, $licenseResult['keys'], Shop::orderUrl($order));
  255. ​
  256. return self::loadMailer() && Mailer::send($email, $message['subject'], $message['html']);
  257. }
  258. ​
  259. public static function sendAwaitingPayment(array $order, array $items): void
  260. {
  261. $order = self::ensureAccessToken($order);
  262. self::mailSafely(function () use ($order, $items): void {
  263. $email = self::customerEmail($order);
  264. if ($email !== null) {
  265. $message = MailTemplates::orderPlacedEmail($order, $items, Shop::orderUrl($order));
  266. Mailer::send($email, $message['subject'], $message['html']);
  267. }
  268. });
  269. self::notifyAdmin($order, $items, false);
  270. }
  271. ​
  272. public static function claimGuestOrders(int $userAccountId, string $email): int
  273. {
  274. $email = strtolower(trim($email));
  275. if ($email === '') {
  276. return 0;
  277. }
  278. $usersDb = Database::users();
  279. $statement = $usersDb->prepare('SELECT id FROM orders WHERE user_account_id IS NULL AND LOWER(guest_email) = :email');
  280. $statement->execute(['email' => $email]);
  281. $orderIds = array_map('intval', $statement->fetchAll(PDO::FETCH_COLUMN));
  282. foreach ($orderIds as $orderId) {
  283. $usersDb->prepare('UPDATE orders SET user_account_id = :user_id WHERE id = :id AND user_account_id IS NULL')
  284. ->execute(['user_id' => $userAccountId, 'id' => $orderId]);
  285. $usersDb->prepare('UPDATE download_grants SET user_account_id = :user_id WHERE order_id = :id AND user_account_id IS NULL')
  286. ->execute(['user_id' => $userAccountId, 'id' => $orderId]);
  287. Licenses::linkToAccount($orderId, $userAccountId);
  288. }
  289. ​
  290. return count($orderIds);
  291. }
  292. ​
  293. public static function stockProblems(array $items): array
  294. {
  295. $problems = [];
  296. foreach ($items as $item) {
  297. if (empty($item['track_inventory']) || $item['stock_quantity'] === null) {
  298. continue;
  299. }
  300. if ((int) $item['quantity'] > (int) $item['stock_quantity']) {
  301. $problems[] = $item['title'] . ($item['variant_name'] !== null ? ' — ' . $item['variant_name'] : '');
  302. }
  303. }
  304. ​
  305. return $problems;
  306. }
  307. ​
  308. private static function adjustStock(array $items, int $direction, array &$lowStock): void
  309. {
  310. $siteDb = Database::site();
  311. foreach ($items as $item) {
  312. $productId = (int) $item['product_id'];
  313. $variantId = $item['variant_id'] !== null ? (int) $item['variant_id'] : null;
  314. $quantity = max(0, (int) $item['quantity']);
  315. if ($quantity === 0) {
  316. continue;
  317. }
  318. ​
  319. $productStatement = $siteDb->prepare('SELECT track_inventory FROM products WHERE id = :id LIMIT 1');
  320. $productStatement->execute(['id' => $productId]);
  321. if ((int) $productStatement->fetchColumn() !== 1) {
  322. continue;
  323. }
  324. ​
  325. $expression = $direction < 0 ? 'GREATEST(0, stock_quantity - :qty)' : 'stock_quantity + :qty';
  326. $variantUpdated = 0;
  327. if ($variantId !== null) {
  328. $variantUpdate = $siteDb->prepare('UPDATE product_variants SET stock_quantity = ' . $expression . ' WHERE id = :id AND stock_quantity IS NOT NULL');
  329. $variantUpdate->execute(['qty' => $quantity, 'id' => $variantId]);
  330. $variantUpdated = $variantUpdate->rowCount();
  331. }
  332. if ($variantUpdated === 0) {
  333. $siteDb->prepare('UPDATE products SET stock_quantity = ' . $expression . ' WHERE id = :id AND stock_quantity IS NOT NULL')
  334. ->execute(['qty' => $quantity, 'id' => $productId]);
  335. }
  336. if ($direction < 0) {
  337. $lowStock[$productId] = true;
  338. }
  339. }
  340. }
  341. ​
  342. private static function alertLowStock(array $productIds): void
  343. {
  344. if (empty($productIds)) {
  345. return;
  346. }
  347. $threshold = max(0, (int) Shop::setting('shop_low_stock_threshold', '3'));
  348. $recipient = self::adminEmail();
  349. if ($recipient === null) {
  350. return;
  351. }
  352. ​
  353. $siteDb = Database::site();
  354. $low = [];
  355. foreach (array_keys($productIds) as $productId) {
  356. $statement = $siteDb->prepare(
  357. 'SELECT p.id, p.title, p.stock_quantity,
  358. (SELECT MIN(v.stock_quantity) FROM product_variants v WHERE v.product_id = p.id AND v.stock_quantity IS NOT NULL) AS lowest_variant
  359. FROM products p WHERE p.id = :id AND p.track_inventory = 1
  360. AND (p.low_stock_alerted_at IS NULL OR p.low_stock_alerted_at < NOW() - INTERVAL 1 DAY) LIMIT 1'
  361. );
  362. $statement->execute(['id' => (int) $productId]);
  363. $product = $statement->fetch();
  364. if (!$product) {
  365. continue;
  366. }
  367. $levels = array_filter([$product['stock_quantity'], $product['lowest_variant']], static fn($value): bool => $value !== null);
  368. if (empty($levels)) {
  369. continue;
  370. }
  371. $lowest = (int) min(array_map('intval', $levels));
  372. if ($lowest > $threshold) {
  373. continue;
  374. }
  375. $siteDb->prepare('UPDATE products SET low_stock_alerted_at = NOW() WHERE id = :id')->execute(['id' => (int) $product['id']]);
  376. $low[] = ['title' => (string) $product['title'], 'stock' => $lowest, 'id' => (int) $product['id']];
  377. }
  378. ​
  379. if (!empty($low)) {
  380. self::mailSafely(function () use ($recipient, $low): void {
  381. $message = MailTemplates::lowStockEmail($low);
  382. Mailer::send($recipient, $message['subject'], $message['html']);
  383. });
  384. }
  385. }
  386. ​
  387. private static function notifyAdmin(array $order, array $items, bool $licensesMissing): void
  388. {
  389. $recipient = self::adminEmail();
  390. if ($recipient === null || Shop::setting('shop_notify_new_orders', '1') !== '1') {
  391. return;
  392. }
  393. self::mailSafely(function () use ($recipient, $order, $items, $licensesMissing): void {
  394. $message = MailTemplates::adminNewOrderEmail($order, $items, $licensesMissing);
  395. Mailer::send($recipient, $message['subject'], $message['html']);
  396. });
  397. }
  398. ​
  399. public static function adminEmail(): ?string
  400. {
  401. foreach (['shop_notify_email', 'shop_contact_email', 'mail_from_email'] as $key) {
  402. $email = trim(Shop::setting($key, ''));
  403. if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
  404. return $email;
  405. }
  406. }
  407. ​
  408. return null;
  409. }
  410. ​
  411. private static function assignInvoiceNumber(int $orderId): void
  412. {
  413. try {
  414. $order = self::find($orderId);
  415. if ($order === null || !empty($order['invoice_number'])) {
  416. return;
  417. }
  418. $siteDb = Database::site();
  419. $siteDb->exec(
  420. "INSERT INTO site_settings (setting_key, setting_value) VALUES ('shop_invoice_counter', LAST_INSERT_ID(1))
  421. ON DUPLICATE KEY UPDATE setting_value = LAST_INSERT_ID(CAST(setting_value AS UNSIGNED) + 1)"
  422. );
  423. $number = (int) $siteDb->query('SELECT LAST_INSERT_ID()')->fetchColumn();
  424. if ($number <= 0) {
  425. return;
  426. }
  427. $format = trim(Shop::setting('shop_invoice_format', ''));
  428. if ($format === '' || !str_contains($format, '{number}')) {
  429. $format = 'INV-{year}-{number}';
  430. }
  431. $invoiceNumber = strtr($format, [
  432. '{year}' => date('Y'),
  433. '{month}' => date('m'),
  434. '{number}' => str_pad((string) $number, 5, '0', STR_PAD_LEFT),
  435. ]);
  436. Database::users()->prepare('UPDATE orders SET invoice_number = :number WHERE id = :id AND invoice_number IS NULL')
  437. ->execute(['number' => mb_substr($invoiceNumber, 0, 40), 'id' => $orderId]);
  438. } catch (Throwable $exception) {
  439. error_log('StocketBase: could not assign an invoice number to order ' . $orderId . ': ' . $exception->getMessage());
  440. }
  441. }
  442. ​
  443. private static function nullable($value): ?string
  444. {
  445. $value = is_scalar($value) ? trim((string) $value) : '';
  446. ​
  447. return $value !== '' ? $value : null;
  448. }
  449. ​
  450. private static function loadMailer(): bool
  451. {
  452. if (!is_file(__DIR__ . '/mailer.php') || !is_file(__DIR__ . '/mail-templates.php')) {
  453. return false;
  454. }
  455. require_once __DIR__ . '/mailer.php';
  456. require_once __DIR__ . '/mail-templates.php';
  457. ​
  458. return class_exists('Mailer') && class_exists('MailTemplates');
  459. }
  460. ​
  461. private static function mailSafely(callable $send): void
  462. {
  463. if (!self::loadMailer()) {
  464. return;
  465. }
  466. try {
  467. $send();
  468. } catch (Throwable $exception) {
  469. error_log('StocketBase: email failed: ' . $exception->getMessage());
  470. }
  471. }
  472. }
  473. ​