select('pi.id_user', 'pi.id_preorder_date', 'SUM(pi.pieces) - SUM(pi.pieces_sent) AS piece_diff', 'SUM(pi.pieces_sent) AS sent_sum') ->from('preorders_items', 'pi') ->groupBy('pi.id_user', 'pi.id_preorder_date'); $statusCond = <<= CURDATE(), 'open', IF (diff.sent_sum = 0, 'received', IF (diff.piece_diff > 0, 'partiallyClosed', 'closed') ) ) SQL; $defaultCurrency = Contexts::get(CurrencyContext::class)->getDefaultId(); return sqlQueryBuilder() ->select('CONCAT(u.id, \'-\', pd.id) AS id', 'pd.id AS id_preorder_date', 'po.id AS id_preorder', 'u.id AS id_user', 'u.firm AS user_firm', 'u.surname AS user_surname', 'u.name AS user_name', 'po.name', 'pd.date_start', 'pd.date_end', 'pd.date_shipment', 'pd.description AS date_description', 'SUM(pi.piece_price * (pi.pieces - pi.pieces_sent)) AS price_remaining', 'SUM(pi.piece_price * pi.pieces) AS price_total', "COALESCE(JSON_VALUE(pud.custom_data, '$.currency'), '{$defaultCurrency}') AS currency", "{$statusCond} AS status") ->from('preorders_items', 'pi') ->leftJoin('pi', 'users', 'u', 'pi.id_user = u.id') ->leftJoin('pi', 'preorders_dates', 'pd', 'pi.id_preorder_date = pd.id') ->leftJoin('pd', 'preorders', 'po', 'pd.id_preorder = po.id') ->leftJoinSubQuery('pi', $difSubQ, 'diff', 'pd.id = diff.id_preorder_date AND u.id = diff.id_user') ->leftJoin('pi', 'preorders_users_data', 'pud', 'pi.id_user = pud.id_user AND pi.id_preorder_date = pud.id_preorder_date') ->groupBy('u.id', 'pd.id'); } public function __construct( public readonly int $userId, public readonly int $preorderDateId, ) { } public function getUser(): \User { if ($this->user === null) { $this->user = \User::createFromId($this->userId); } return $this->user; } public function getItems(bool $reFetch = false, bool $withProductPhotos = false): ?array { if ($this->items === null || $reFetch) { $qb = sqlQueryBuilder() ->select( 'pi.*', 'p.vat', 'p.title AS product_title', 'pv.title AS variation_title', 'CONCAT_WS(\' \', p.title, pv.title) AS title', 'pi.piece_price * pi.pieces AS price_total', 'pi.piece_price * (pi.pieces - pi.pieces_sent) AS price_remaining', '(SELECT EXISTS (SELECT * FROM products_variations WHERE id_product = pi.id_product)) AS has_variations', ) ->from('preorders_items', 'pi') ->leftJoin('pi', 'products', 'p', 'p.id = pi.id_product') ->leftJoin('pi', 'products_variations', 'pv', 'pv.id = pi.id_variation') ->orderBy('pi.pieces - pi.pieces_sent', 'DESC') ->where(Op::equals(['pi.id_user' => $this->userId, 'pi.id_preorder_date' => $this->preorderDateId])); if ($withProductPhotos) { $qb->addSelect(Product::withProductPhotoId()); } $this->items = $qb->execute()->fetchAllAssociative(); } return $this->items; } /** * Updates item count, if item was already pre-ordered before. Or inserts if not. * * @return int|null id of inserted/updated/deleted preorder item * * @throws \Exception * @throws Exception */ public function setItem( ?int $preorderItemId = null, ?int $idProduct = null, ?int $idVariation = null, ?int $count = null, ?int $piecesSent = null, ?Price $piecePrice = null, ): ?int { $existing = null; foreach ($this->getItems() as $item) { $matchesProduct = $item['id_product'] == $idProduct && ($idVariation === null || $item['id_variation'] == $idVariation); $matchesId = $preorderItemId == $item['id']; if ($matchesId || $matchesProduct) { $existing = $item; break; } } // item not yet pre-ordered if ($existing === null) { if ($count > 0) { return $this->insertNewItem($idProduct, $idVariation, $piecePrice, $count); } return null; } $qb = sqlQueryBuilder() ->where(Op::equals(['pi.id' => $existing['id']])); // remove item from preorder if ($count === 0) { $qb->delete('preorders_items', 'pi') ->execute(); return (int) $existing['id']; } $sets = []; if ($piecePrice !== null) { $sets['piece_price'] = $piecePrice->getPriceWithoutVat(); $sets['currency'] = $piecePrice->getCurrency()->getId(); } if ($count !== null && $count != $existing['pieces']) { if ($count < ($piecesSent ?? $existing['pieces_sent'])) { $count = $piecesSent ?? $existing['pieces_sent']; } $sets['pieces'] = $count; } if ($piecesSent !== null && $piecesSent != $existing['pieces_sent']) { if ($piecesSent > ($count ?? $existing['pieces'])) { $piecesSent = $count ?? $existing['pieces']; } $sets['pieces_sent'] = $piecesSent; } if (!empty($sets)) { foreach ($sets as $key => $value) { $paramKey = ":_{$key}"; $qb->set($key, $paramKey); $qb->addParameters([ $paramKey => $value, ]); } $qb->update('preorders_items', 'pi')->execute(); return (int) $existing['id']; } return null; } /** * @throws Exception */ private function insertNewItem(int $idProduct, ?int $idVariation, ?Price $piecePrice, int $count = 1): int { $item = [ 'id_user' => $this->userId, 'id_preorder_date' => $this->preorderDateId, 'id_product' => $idProduct, 'pieces' => $count, ]; if ($piecePrice !== null) { $item['piece_price'] = $piecePrice->getPriceWithoutVat(); $item['currency'] = $piecePrice->getCurrency(); } if ($idVariation !== null) { $item['id_variation'] = $idVariation; } sqlQueryBuilder() ->insert('preorders_items') ->directValues($item) ->execute(); return (int) sqlInsertId(); } public function getTemplateVars(): ?array { $spec = Op::equals(['u.id' => $this->userId, 'pi.id_preorder_date' => $this->preorderDateId]); try { $preorderRes = self::getAllUserPreordersQuery() ->andWhere($spec) ->execute() ->fetchAllAssociative(); $items = $this->getItems(); } catch (\Throwable) { return null; } if (count($preorderRes) !== 1) { return null; } return $preorderRes[0] + ['items' => $items]; } public function getPreorderDate(): ?array { if ($this->preorderDate === null) { $qb = sqlQueryBuilder() ->select('pd.*', 'po.*', 'pd.id AS id') ->from('preorders_dates', 'pd') ->leftJoin('pd', 'preorders', 'po', 'pd.id_preorder = po.id') ->where(Op::equals(['pd.id' => $this->preorderDateId])); $found = $qb->execute()->fetchAllAssociative(); if (count($found) !== 1) { return null; } $this->preorderDate = $found[0]; if (!empty($this->preorderDate['settings'])) { $this->preorderDate['settings'] = json_decode($this->preorderDate['settings'], true); } if (!empty($this->preorderDate['min_price'])) { $this->preorderDate['min_price'] = toDecimal($this->preorderDate['min_price']); } } return $this->preorderDate; } public function getPreorder(): ?Preorder { if ($this->preorder === null) { $date = $this->getPreorderDate(); if (!$date || empty($date['id_preorder'])) { return null; } $this->preorder = new Preorder((int) $date['id_preorder']); } return $this->preorder; } public function getStatus(): string { $end = \DateTimeImmutable::createFromFormat('Y-m-d', $this->getPreorderDate()['date_end']); if (!(new \DateTimeImmutable())->diff($end)->invert) { return self::STATUS_OPEN; } $piecesSent = 0; $piecesTotal = 0; foreach ($this->getItems() as $item) { $piecesSent += $item['pieces_sent']; $piecesTotal += $item['pieces']; } if ($piecesTotal > 0 && $piecesSent == $piecesTotal) { return self::STATUS_CLOSED; } if ($piecesTotal > 0 && $piecesSent == 0) { return self::STATUS_RECEIVED; } return self::STATUS_PARTIALLY_CLOSED; } /** * @return PersistentCollection */ public function getMessages(): PersistentCollection { return $this->messages ??= new UserPreorderMessages($this); } /** * @return PersistentCollection */ public function getCustomData(): PersistentCollection { return $this->customData ??= new UserPreorderCustomData($this); } }