/** * @param Warehouse $warehouse * @param \DateTime $from * @param \DateTime $to * @param callable $callback * @return LevelChange[] */ public function getChangelogInRange(Warehouse $warehouse, \DateTime $from, \DateTime $to, callable $callback = null) { $builder = $this->em()->createQueryBuilder(); $builder->select(LevelChange::ALIAS, Stock::ALIAS, StockItem::ALIAS, StockUnit::ALIAS, User::ALIAS)->from(LevelChange::class, LevelChange::ALIAS)->leftJoin(sprintf('%s.stock', LevelChange::ALIAS), Stock::ALIAS)->leftJoin(sprintf('%s.stock_item', Stock::ALIAS), StockItem::ALIAS)->leftJoin(sprintf('%s.storage_unit', StockItem::ALIAS), StockUnit::ALIAS)->leftJoin(sprintf('%s.corrector', LevelChange::ALIAS), User::ALIAS)->where(sprintf('%s.warehouse = :id', Stock::ALIAS))->andWhere(sprintf('(%s.created_at between :from and :to)', LevelChange::ALIAS))->orderBy(sprintf('%s.created_at', LevelChange::ALIAS), 'desc')->setParameter('id', $warehouse->getId())->setParameter('from', $from)->setParameter('to', $to); if ($callback) { $callback($builder); } return $builder->getQuery()->getResult(); }
/** * @param Warehouse $warehouse * @return array */ private function findStockItemInWarehouse(Warehouse $warehouse) { return $this->mapper(Stock::class)->filterAndCollect(function (QueryBuilder $builder) use($warehouse) { $builder->addSelect('sti, w', 'ssu', 'suu'); $builder->leftJoin('st.stock_item', 'sti'); $builder->leftJoin('sti.storage_unit', 'ssu'); $builder->leftJoin('sti.usage_unit', 'suu'); $builder->leftJoin('st.warehouse', 'w'); $builder->where('w.id = :id'); $builder->setParameter('id', $warehouse->getId()); }, $this->stockEntityToFlatArray()); }
/** * @param Warehouse $warehouse * @return array */ public function getStockItemInWarehouse(Warehouse $warehouse) { $this->assert(intval($warehouse->getId()) > 0); $sql = "SELECT" . "\n sti.id AS item_id,\n sti.name AS item_name,\n sti.code AS item_code,\n sti.description AS item_description,\n su.id AS storage_unit_id,\n su.name AS storage_unit_name,\n su.description AS storage_unit_description,\n uu.id AS usage_unit_id,\n uu.name AS usage_unit_name,\n uu.description AS usage_unit_description,\n st.id AS stock_id,\n st.current_unit_price AS current_unit_price,\n (st.current_level / su.ratio) AS current_level,\n (st.reorder_level / su.ratio) AS reorder_level,\n st.last_change AS last_change,\n st.last_purchase AS last_purchase\n FROM stock_items sti\n INNER JOIN stock_units su ON su.id = sti.storage_unit_id\n INNER JOIN stock_units uu ON uu.id = sti.usage_unit_id\n LEFT JOIN (\n SELECT st.id,\n st.stock_item_id,\n st.current_unit_price,\n st.current_level,\n st.reorder_level,\n st.last_change,\n st.last_purchase\n FROM stocks st\n WHERE st.warehouse_id = ?\n GROUP BY st.id\n ) st ON st.stock_item_id = sti.id\n GROUP BY sti.id\n "; $stmt = $this->db()->prepare($sql); $stmt->bindValue(1, $warehouse->getId()); $stmt->execute(); return $stmt->fetchAll(\PDO::FETCH_ASSOC); }
/** * @return int|null */ public function getRecordIdOrNull() { return $this->existingRecord ? $this->existingRecord->getId() : null; }