v1.1.0.0
StocketBase
- <?php
-
- declare(strict_types=1);
-
- require_once __DIR__ . '/database.php';
- require_once __DIR__ . '/site-front.php';
- require_once __DIR__ . '/shop.php';
- require_once __DIR__ . '/digital-files.php';
- require_once __DIR__ . '/licenses.php';
-
- final class Orders
- {
- public const PAID_STATUSES = ['paid', 'processing', 'fulfilled', 'shipped', 'completed'];
- public const CLOSED_STATUSES = ['canceled', 'refunded', 'failed'];
-
- public static function find(int $orderId): ?array
- {
- if ($orderId <= 0) {
- return null;
- }
- $statement = Database::users()->prepare('SELECT * FROM orders WHERE id = :id LIMIT 1');
- $statement->execute(['id' => $orderId]);
-
- return $statement->fetch() ?: null;
- }
-
- public static function items(int $orderId): array
- {
- $statement = Database::users()->prepare('SELECT * FROM order_items WHERE order_id = :id ORDER BY id ASC');
- $statement->execute(['id' => $orderId]);
-
- return $statement->fetchAll();
- }
-
- public static function history(int $orderId): array
- {
- $statement = Database::users()->prepare('SELECT * FROM order_status_history WHERE order_id = :id ORDER BY created_at ASC, id ASC');
- $statement->execute(['id' => $orderId]);
-
- return $statement->fetchAll();
- }
-
- public static function findWithToken(int $orderId, string $token): ?array
- {
- $token = strtolower(trim($token));
- if (preg_match('/^[a-f0-9]{64}$/', $token) !== 1) {
- return null;
- }
- $order = self::find($orderId);
- if ($order === null || empty($order['access_token']) || !hash_equals((string) $order['access_token'], $token)) {
- return null;
- }
-
- return $order;
- }
-
- public static function ensureAccessToken(array $order): array
- {
- if (!empty($order['access_token'])) {
- return $order;
- }
- $token = bin2hex(random_bytes(32));
- $update = Database::users()->prepare('UPDATE orders SET access_token = :token WHERE id = :id AND access_token IS NULL');
- $update->execute(['token' => $token, 'id' => (int) $order['id']]);
- if ($update->rowCount() > 0) {
- $order['access_token'] = $token;
-
- return $order;
- }
-
- return self::find((int) $order['id']) ?? $order;
- }
-
- public static function customerEmail(array $order): ?string
- {
- if ($order['user_account_id'] !== null) {
- $statement = Database::users()->prepare('SELECT email FROM user_accounts WHERE id = :id LIMIT 1');
- $statement->execute(['id' => (int) $order['user_account_id']]);
- $email = (string) $statement->fetchColumn();
- if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
- return $email;
- }
- }
- $guest = (string) ($order['guest_email'] ?? '');
-
- return $guest !== '' && filter_var($guest, FILTER_VALIDATE_EMAIL) ? $guest : null;
- }
-
- public static function addHistory(int $orderId, string $status, ?string $note = null, ?int $staffId = null): void
- {
- Database::users()->prepare(
- 'INSERT INTO order_status_history (order_id, status, note, changed_by_team_account_id) VALUES (:order_id, :status, :note, :staff_id)'
- )->execute([
- 'order_id' => $orderId,
- 'status' => $status,
- 'note' => $note !== null ? mb_substr($note, 0, 500) : null,
- 'staff_id' => $staffId,
- ]);
- }
-
- public static function reserveStock(int $orderId): void
- {
- $order = self::find($orderId);
- if ($order === null || (int) ($order['stock_reserved'] ?? 0) === 1) {
- return;
- }
- $claim = Database::users()->prepare('UPDATE orders SET stock_reserved = 1 WHERE id = :id AND stock_reserved = 0');
- $claim->execute(['id' => $orderId]);
- if ($claim->rowCount() === 0) {
- return;
- }
- $lowStock = [];
- self::adjustStock(self::items($orderId), -1, $lowStock);
- self::alertLowStock($lowStock);
- }
-
- public static function markPaid(int $orderId, array $payment = [], string $note = 'Payment confirmed', ?int $staffId = null, ?string $finalStatus = null): bool
- {
- $usersDb = Database::users();
- $update = $usersDb->prepare(
- "UPDATE orders SET status = 'paid', paid_at = COALESCE(paid_at, NOW()), canceled_at = NULL,
- provider = COALESCE(:provider, provider),
- provider_checkout_id = COALESCE(:checkout_id, provider_checkout_id),
- provider_payment_id = COALESCE(:payment_id, provider_payment_id),
- provider_customer_id = COALESCE(:customer_id, provider_customer_id)
- WHERE id = :id AND status IN ('pending', 'failed')"
- );
- $update->execute([
- 'provider' => self::nullable($payment['provider'] ?? null),
- 'checkout_id' => self::nullable($payment['checkout_id'] ?? null),
- 'payment_id' => self::nullable($payment['payment_id'] ?? null),
- 'customer_id' => self::nullable($payment['customer_id'] ?? null),
- 'id' => $orderId,
- ]);
- if ($update->rowCount() === 0) {
- return false;
- }
-
- self::addHistory($orderId, 'paid', $note, $staffId);
-
- $order = self::ensureAccessToken(self::find($orderId) ?? []);
- $items = self::items($orderId);
- $siteDb = Database::site();
-
- $lowStock = [];
- $claim = $usersDb->prepare('UPDATE orders SET stock_reserved = 1 WHERE id = :id AND stock_reserved = 0');
- $claim->execute(['id' => $orderId]);
- if ($claim->rowCount() > 0) {
- self::adjustStock($items, -1, $lowStock);
- }
-
- foreach ($items as $item) {
- $siteDb->prepare('UPDATE products SET sales_count = sales_count + :qty WHERE id = :id')
- ->execute(['qty' => (int) $item['quantity'], 'id' => (int) $item['product_id']]);
- }
-
- if (!empty($order['discount_code'])) {
- $siteDb->prepare('UPDATE discounts SET times_redeemed = times_redeemed + 1 WHERE UPPER(code) = UPPER(:code)')
- ->execute(['code' => $order['discount_code']]);
- }
-
- self::assignInvoiceNumber($orderId);
-
- $downloadLinks = [];
- try {
- $downloadLinks = DigitalFiles::grantsForOrder($usersDb, $order, $items);
- } catch (Throwable $exception) {
- error_log('StocketBase: could not create download access for order ' . $orderId . ': ' . $exception->getMessage());
- }
-
- $licenseResult = ['keys' => [], 'missing' => []];
- try {
- $licenseResult = Licenses::assignForOrder($order, $items);
- } catch (Throwable $exception) {
- error_log('StocketBase: could not assign license keys for order ' . $orderId . ': ' . $exception->getMessage());
- }
- if (!empty($licenseResult['missing'])) {
- self::addHistory($orderId, 'paid', 'Not enough license keys in the pool. Add keys and use "Send keys again".', null);
- }
-
- if ($finalStatus !== null && $finalStatus !== 'paid' && in_array($finalStatus, self::PAID_STATUSES, true)) {
- $usersDb->prepare('UPDATE orders SET status = :status WHERE id = :id')->execute(['status' => $finalStatus, 'id' => $orderId]);
- self::addHistory($orderId, $finalStatus, null, $staffId);
- }
-
- $order = self::find($orderId) ?? $order;
-
- self::mailSafely(function () use ($order, $items, $downloadLinks, $licenseResult): void {
- $email = self::customerEmail($order);
- if ($email !== null) {
- $message = MailTemplates::orderConfirmationEmail($order, $items, $downloadLinks, $licenseResult['keys'], Shop::orderUrl($order));
- Mailer::send($email, $message['subject'], $message['html']);
- }
- });
- self::notifyAdmin($order, $items, !empty($licenseResult['missing']));
- self::alertLowStock($lowStock);
-
- return true;
- }
-
- public static function close(int $orderId, string $status, ?string $note = null, ?int $staffId = null, bool $restock = true): bool
- {
- if (!in_array($status, self::CLOSED_STATUSES, true)) {
- return false;
- }
- $order = self::find($orderId);
- if ($order === null || $order['status'] === $status) {
- return false;
- }
-
- $usersDb = Database::users();
- $usersDb->prepare('UPDATE orders SET status = :status, canceled_at = COALESCE(canceled_at, NOW()), updated_at = NOW() WHERE id = :id')
- ->execute(['status' => $status, 'id' => $orderId]);
- self::addHistory($orderId, $status, $note, $staffId);
-
- DigitalFiles::revokeGrantsForOrder($orderId);
- Licenses::revokeForOrder($orderId);
-
- if ($restock && (int) ($order['stock_reserved'] ?? 0) === 1) {
- $release = $usersDb->prepare('UPDATE orders SET stock_reserved = 0 WHERE id = :id AND stock_reserved = 1');
- $release->execute(['id' => $orderId]);
- if ($release->rowCount() > 0) {
- $ignored = [];
- self::adjustStock(self::items($orderId), 1, $ignored);
- }
- }
-
- if (in_array($order['status'], self::PAID_STATUSES, true)) {
- foreach (self::items($orderId) as $item) {
- Database::site()->prepare('UPDATE products SET sales_count = GREATEST(0, CAST(sales_count AS SIGNED) - :qty) WHERE id = :id')
- ->execute(['qty' => (int) $item['quantity'], 'id' => (int) $item['product_id']]);
- }
- }
-
- return true;
- }
-
- public static function resendConfirmation(int $orderId): bool
- {
- $order = self::find($orderId);
- if ($order === null || !in_array($order['status'], self::PAID_STATUSES, true)) {
- return false;
- }
- $order = self::ensureAccessToken($order);
- $items = self::items($orderId);
- $email = self::customerEmail($order);
- if ($email === null) {
- return false;
- }
-
- $usersDb = Database::users();
- $downloadLinks = DigitalFiles::grantsForOrder($usersDb, $order, $items);
- $licenseResult = Licenses::assignForOrder($order, $items);
- $message = MailTemplates::orderConfirmationEmail($order, $items, $downloadLinks, $licenseResult['keys'], Shop::orderUrl($order));
-
- return self::loadMailer() && Mailer::send($email, $message['subject'], $message['html']);
- }
-
- public static function sendAwaitingPayment(array $order, array $items): void
- {
- $order = self::ensureAccessToken($order);
- self::mailSafely(function () use ($order, $items): void {
- $email = self::customerEmail($order);
- if ($email !== null) {
- $message = MailTemplates::orderPlacedEmail($order, $items, Shop::orderUrl($order));
- Mailer::send($email, $message['subject'], $message['html']);
- }
- });
- self::notifyAdmin($order, $items, false);
- }
-
- public static function claimGuestOrders(int $userAccountId, string $email): int
- {
- $email = strtolower(trim($email));
- if ($email === '') {
- return 0;
- }
- $usersDb = Database::users();
- $statement = $usersDb->prepare('SELECT id FROM orders WHERE user_account_id IS NULL AND LOWER(guest_email) = :email');
- $statement->execute(['email' => $email]);
- $orderIds = array_map('intval', $statement->fetchAll(PDO::FETCH_COLUMN));
- foreach ($orderIds as $orderId) {
- $usersDb->prepare('UPDATE orders SET user_account_id = :user_id WHERE id = :id AND user_account_id IS NULL')
- ->execute(['user_id' => $userAccountId, 'id' => $orderId]);
- $usersDb->prepare('UPDATE download_grants SET user_account_id = :user_id WHERE order_id = :id AND user_account_id IS NULL')
- ->execute(['user_id' => $userAccountId, 'id' => $orderId]);
- Licenses::linkToAccount($orderId, $userAccountId);
- }
-
- return count($orderIds);
- }
-
- public static function stockProblems(array $items): array
- {
- $problems = [];
- foreach ($items as $item) {
- if (empty($item['track_inventory']) || $item['stock_quantity'] === null) {
- continue;
- }
- if ((int) $item['quantity'] > (int) $item['stock_quantity']) {
- $problems[] = $item['title'] . ($item['variant_name'] !== null ? ' — ' . $item['variant_name'] : '');
- }
- }
-
- return $problems;
- }
-
- private static function adjustStock(array $items, int $direction, array &$lowStock): void
- {
- $siteDb = Database::site();
- foreach ($items as $item) {
- $productId = (int) $item['product_id'];
- $variantId = $item['variant_id'] !== null ? (int) $item['variant_id'] : null;
- $quantity = max(0, (int) $item['quantity']);
- if ($quantity === 0) {
- continue;
- }
-
- $productStatement = $siteDb->prepare('SELECT track_inventory FROM products WHERE id = :id LIMIT 1');
- $productStatement->execute(['id' => $productId]);
- if ((int) $productStatement->fetchColumn() !== 1) {
- continue;
- }
-
- $expression = $direction < 0 ? 'GREATEST(0, stock_quantity - :qty)' : 'stock_quantity + :qty';
- $variantUpdated = 0;
- if ($variantId !== null) {
- $variantUpdate = $siteDb->prepare('UPDATE product_variants SET stock_quantity = ' . $expression . ' WHERE id = :id AND stock_quantity IS NOT NULL');
- $variantUpdate->execute(['qty' => $quantity, 'id' => $variantId]);
- $variantUpdated = $variantUpdate->rowCount();
- }
- if ($variantUpdated === 0) {
- $siteDb->prepare('UPDATE products SET stock_quantity = ' . $expression . ' WHERE id = :id AND stock_quantity IS NOT NULL')
- ->execute(['qty' => $quantity, 'id' => $productId]);
- }
- if ($direction < 0) {
- $lowStock[$productId] = true;
- }
- }
- }
-
- private static function alertLowStock(array $productIds): void
- {
- if (empty($productIds)) {
- return;
- }
- $threshold = max(0, (int) Shop::setting('shop_low_stock_threshold', '3'));
- $recipient = self::adminEmail();
- if ($recipient === null) {
- return;
- }
-
- $siteDb = Database::site();
- $low = [];
- foreach (array_keys($productIds) as $productId) {
- $statement = $siteDb->prepare(
- 'SELECT p.id, p.title, p.stock_quantity,
- (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
- FROM products p WHERE p.id = :id AND p.track_inventory = 1
- AND (p.low_stock_alerted_at IS NULL OR p.low_stock_alerted_at < NOW() - INTERVAL 1 DAY) LIMIT 1'
- );
- $statement->execute(['id' => (int) $productId]);
- $product = $statement->fetch();
- if (!$product) {
- continue;
- }
- $levels = array_filter([$product['stock_quantity'], $product['lowest_variant']], static fn($value): bool => $value !== null);
- if (empty($levels)) {
- continue;
- }
- $lowest = (int) min(array_map('intval', $levels));
- if ($lowest > $threshold) {
- continue;
- }
- $siteDb->prepare('UPDATE products SET low_stock_alerted_at = NOW() WHERE id = :id')->execute(['id' => (int) $product['id']]);
- $low[] = ['title' => (string) $product['title'], 'stock' => $lowest, 'id' => (int) $product['id']];
- }
-
- if (!empty($low)) {
- self::mailSafely(function () use ($recipient, $low): void {
- $message = MailTemplates::lowStockEmail($low);
- Mailer::send($recipient, $message['subject'], $message['html']);
- });
- }
- }
-
- private static function notifyAdmin(array $order, array $items, bool $licensesMissing): void
- {
- $recipient = self::adminEmail();
- if ($recipient === null || Shop::setting('shop_notify_new_orders', '1') !== '1') {
- return;
- }
- self::mailSafely(function () use ($recipient, $order, $items, $licensesMissing): void {
- $message = MailTemplates::adminNewOrderEmail($order, $items, $licensesMissing);
- Mailer::send($recipient, $message['subject'], $message['html']);
- });
- }
-
- public static function adminEmail(): ?string
- {
- foreach (['shop_notify_email', 'shop_contact_email', 'mail_from_email'] as $key) {
- $email = trim(Shop::setting($key, ''));
- if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
- return $email;
- }
- }
-
- return null;
- }
-
- private static function assignInvoiceNumber(int $orderId): void
- {
- try {
- $order = self::find($orderId);
- if ($order === null || !empty($order['invoice_number'])) {
- return;
- }
- $siteDb = Database::site();
- $siteDb->exec(
- "INSERT INTO site_settings (setting_key, setting_value) VALUES ('shop_invoice_counter', LAST_INSERT_ID(1))
- ON DUPLICATE KEY UPDATE setting_value = LAST_INSERT_ID(CAST(setting_value AS UNSIGNED) + 1)"
- );
- $number = (int) $siteDb->query('SELECT LAST_INSERT_ID()')->fetchColumn();
- if ($number <= 0) {
- return;
- }
- $format = trim(Shop::setting('shop_invoice_format', ''));
- if ($format === '' || !str_contains($format, '{number}')) {
- $format = 'INV-{year}-{number}';
- }
- $invoiceNumber = strtr($format, [
- '{year}' => date('Y'),
- '{month}' => date('m'),
- '{number}' => str_pad((string) $number, 5, '0', STR_PAD_LEFT),
- ]);
- Database::users()->prepare('UPDATE orders SET invoice_number = :number WHERE id = :id AND invoice_number IS NULL')
- ->execute(['number' => mb_substr($invoiceNumber, 0, 40), 'id' => $orderId]);
- } catch (Throwable $exception) {
- error_log('StocketBase: could not assign an invoice number to order ' . $orderId . ': ' . $exception->getMessage());
- }
- }
-
- private static function nullable($value): ?string
- {
- $value = is_scalar($value) ? trim((string) $value) : '';
-
- return $value !== '' ? $value : null;
- }
-
- private static function loadMailer(): bool
- {
- if (!is_file(__DIR__ . '/mailer.php') || !is_file(__DIR__ . '/mail-templates.php')) {
- return false;
- }
- require_once __DIR__ . '/mailer.php';
- require_once __DIR__ . '/mail-templates.php';
-
- return class_exists('Mailer') && class_exists('MailTemplates');
- }
-
- private static function mailSafely(callable $send): void
- {
- if (!self::loadMailer()) {
- return;
- }
- try {
- $send();
- } catch (Throwable $exception) {
- error_log('StocketBase: email failed: ' . $exception->getMessage());
- }
- }
- }
-