1307 lines
50 KiB
PHP
1307 lines
50 KiB
PHP
<?php
|
|
|
|
namespace KupShop\WarehouseBundle\Util;
|
|
|
|
use Doctrine\DBAL\Exception\DriverException;
|
|
use KupShop\KupShopBundle\Config;
|
|
use KupShop\KupShopBundle\Context\CurrencyContext;
|
|
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
|
|
use KupShop\KupShopBundle\Util\Price\ProductPrice;
|
|
use KupShop\WarehouseBundle\Entity\StoreItem;
|
|
use KupShop\WarehouseBundle\Event\WarehouseProductEvent;
|
|
use KupShop\WarehouseBundle\Event\WarehouseProductMoveEvent;
|
|
use KupShop\WarehouseBundle\Exception\WarehouseBaseException;
|
|
use Order;
|
|
use Product;
|
|
use Query\Operator;
|
|
use Query\QueryBuilder;
|
|
use Symfony\Component\EventDispatcher\EventDispatcherInterface;
|
|
use Symfony\Component\HttpKernel\HttpCache\Store;
|
|
|
|
class StoreItemWorker
|
|
{
|
|
/**
|
|
* @var Logger
|
|
*/
|
|
private $logger;
|
|
|
|
private $startOrderCheckoutStatuses;
|
|
|
|
/**
|
|
* @var EventDispatcherInterface
|
|
*/
|
|
private $eventDispatcher;
|
|
|
|
public function __construct(Logger $logger)
|
|
{
|
|
$this->logger = $logger;
|
|
}
|
|
|
|
public const LOCATION_TABLE = 1;
|
|
public const LOCATION_BOX = 2;
|
|
|
|
protected $box_position;
|
|
|
|
public function returnError($errorMessage)
|
|
{
|
|
return [
|
|
'result' => false,
|
|
'message' => $errorMessage,
|
|
];
|
|
}
|
|
|
|
public function getStats($user)
|
|
{
|
|
$cfg = Config::get();
|
|
|
|
$userScore = sqlQueryBuilder()
|
|
->select('SUM((1 * GREATEST(wl.score_multiplier, wl2.score_multiplier))) as count')
|
|
->from('warehouse_log', 'wlog')
|
|
->leftJoin('wlog', 'warehouse_positions', 'wpos', 'wpos.id = wlog.old_id_position')
|
|
->leftJoin('wpos', 'warehouse_locations', 'wl', 'wl.id = wpos.id_location')
|
|
->leftJoin('wlog', 'warehouse_positions', 'wpos2', 'wpos2.id = wlog.new_id_position')
|
|
->leftJoin('wpos2', 'warehouse_locations', 'wl2', 'wl2.id = wpos2.id_location')
|
|
->where(Operator::equals(['wlog.id_admin' => $user->id]))
|
|
->andWhere('DATE(wlog.update_datetime) = CURDATE()')
|
|
->execute()
|
|
->fetchColumn();
|
|
|
|
$remainingOrders = null;
|
|
if (isset($cfg['Order']['Status']['warehouse_remaining'])) {
|
|
$remainingOrders = sqlQueryBuilder()->select('COUNT(o.id) as count')
|
|
->from('orders', 'o')
|
|
->leftJoin('o', 'warehouse_orders', 'wo', 'wo.id_order = o.id')
|
|
->where(Operator::inIntArray($cfg['Order']['Status']['warehouse_remaining'], 'o.status'))
|
|
->andWhere('wo.id IS NULL OR wo.date_finish IS NULL')
|
|
->execute()
|
|
->fetchColumn();
|
|
}
|
|
|
|
$remainingStockIn = sqlQueryBuilder()->select('COUNT(*) as count')
|
|
->from('warehouse_products')
|
|
->where('id_position IN (SELECT id FROM warehouse_positions WHERE id_location = :id_location)')
|
|
->setParameter('id_location', self::LOCATION_TABLE)
|
|
->execute()->fetchColumn();
|
|
|
|
return [
|
|
'result' => true,
|
|
'stats' => [
|
|
'userScore' => $userScore ? $userScore : 0,
|
|
'remainingOrders' => $remainingOrders ? $remainingOrders : 0,
|
|
'remainingStockIn' => $remainingStockIn ? $remainingStockIn : 0,
|
|
],
|
|
];
|
|
}
|
|
|
|
public function getAllTables()
|
|
{
|
|
$tables = sqlQueryBuilder()
|
|
->select('id, code, (SELECT COUNT(*) as count FROM warehouse_products WHERE id_position = id) as products_count')
|
|
->from('warehouse_positions')
|
|
->where(Operator::equals(['id_location' => self::LOCATION_TABLE]))
|
|
->execute()->fetchAll();
|
|
|
|
return $tables ?: [];
|
|
}
|
|
|
|
// DB converts some strings to 0 when comparing with int
|
|
protected function isEanInvalid($ean)
|
|
{
|
|
return !is_numeric($ean) && intval($ean) === 0;
|
|
}
|
|
|
|
public function checkProductByEanAndLocation($ean, $id_location = null, $code_position = null, $batchId = false)
|
|
{
|
|
if ($this->isEanInvalid($ean)) {
|
|
return false;
|
|
}
|
|
|
|
$currencyContext = ServiceContainer::getService(CurrencyContext::class);
|
|
$row = sqlQueryBuilder()
|
|
->select('p.title, COALESCE(pv.ean, p.ean) as ean, COALESCE(pv.code, p.code) as code, pv.title as variation_title,
|
|
p.id as id_product, pv.id as id_variation, wp.pieces, COALESCE(pv.note, p.note) as note, wpos.code as position, wp.inventory_date, p.discount, COALESCE(pv.price, p.price) as price, COALESCE(pv.in_store, p.in_store) as in_store')
|
|
->addSelect('COALESCE(pv.in_store, p.in_store) as in_store')
|
|
->addSelect(\Query\Product::withVat())
|
|
->from('products', 'p')
|
|
->joinVariationsOnProducts()
|
|
->joinVatsOnProducts()
|
|
->join('p', 'warehouse_products', 'wp', 'p.id=wp.id_product AND (pv.id IS NULL OR pv.id=wp.id_variation)')
|
|
->join('wp', 'warehouse_positions', 'wpos', 'wp.id_position=wpos.id')
|
|
->leftJoin('wp', 'products_of_suppliers', 'pos', 'wp.id_product=pos.id_product AND (wp.id_variation IS NULL OR wp.id_variation=pos.id_variation)')
|
|
->andWhere(Operator::equals(['pv.ean' => $ean, 'p.ean' => $ean, 'pos.ean' => $ean], 'OR'))
|
|
->addSelect(\Query\Product::withProductPhotoId(true))
|
|
->setMaxResults(1)
|
|
->orderBy('wp.pieces', 'DESC');
|
|
|
|
if (findModule(\Modules::PRODUCTS_BATCHES)) {
|
|
$row->addSelect('wp.id_product_batch');
|
|
if ($batchId !== false) {
|
|
$row->andWhere(Operator::equalsNullable(['wp.id_product_batch' => $batchId]));
|
|
}
|
|
}
|
|
|
|
if ($id_location) {
|
|
$row->andWhere(Operator::equals(['wpos.id_location' => $id_location]));
|
|
} elseif ($code_position) {
|
|
$row->andWhere(Operator::equals(['wp.id_position' => $this->getPositionIdByCode($code_position)]));
|
|
}
|
|
|
|
$row = $row->execute()
|
|
->fetch();
|
|
|
|
if (!empty($row['id_product'])) {
|
|
$row['image'] = getImage($row['id_photo'], null, null, 4);
|
|
$row['ean'] = formatEAN($row['ean']);
|
|
$productPrice = new ProductPrice(toDecimal($row['price']), $currencyContext->getDefault(), getVat($row['vat']), $row['discount']);
|
|
$row['priceWithVatWithDiscount'] = $productPrice->getPriceWithVat(true)->asFloat();
|
|
$row['priceWithVatWithOutDiscount'] = $productPrice->getPriceWithVatWithoutDiscount(true)->asFloat();
|
|
$row['piecesInOrder'] = $this->getPiecesInOrder($row, $code_position);
|
|
$row['sellPerDay'] = round(sqlQueryBuilder()
|
|
->select('SUM(oi.pieces) / 365')
|
|
->from('orders', 'o')
|
|
->join('o', 'order_items', 'oi', 'oi.id_order=o.id')
|
|
->andWhere('o.date_handle >= NOW() - INTERVAL 365 DAY')
|
|
->andWhere('(oi.id_product=:id_product AND (oi.id_variation IS NULL OR oi.id_variation=:id_variation))')
|
|
->setParameters([
|
|
'id_product' => $row['id_product'],
|
|
'id_variation' => $row['id_variation'],
|
|
])
|
|
->groupBy('oi.id_product, oi.id_variation')
|
|
->execute()
|
|
->fetchColumn(), 2);
|
|
$positionsQb = sqlQueryBuilder()
|
|
->select('wp.*')
|
|
->addSelect('(SELECT code FROM warehouse_positions WHERE id = wp.id_position LIMIT 1) as code')
|
|
->from('warehouse_products', 'wp')
|
|
->where(Operator::equals(['id_product' => $row['id_product']]))
|
|
->orderBy('wp.pieces', 'DESC');
|
|
if ($row['id_variation']) {
|
|
$positionsQb->andWhere(Operator::equals(['wp.id_variation' => $row['id_variation']]));
|
|
} else {
|
|
$positionsQb->andWhere(Operator::isNull('wp.id_variation'));
|
|
}
|
|
$row['positions'] = $positionsQb->execute()->fetchAll();
|
|
}
|
|
|
|
return $row;
|
|
}
|
|
|
|
/**
|
|
* @param $spec callable|string Spec to filter product
|
|
*
|
|
* @return array of positions
|
|
*/
|
|
public function getProductPositions($spec)
|
|
{
|
|
$positions = sqlQueryBuilder()
|
|
->select('wpos.code, wp.pieces')
|
|
->from('products', 'p')
|
|
->joinVariationsOnProducts()
|
|
->leftJoin('p', 'warehouse_products', 'wp', 'p.id = wp.id_product AND pv.id <=> wp.id_variation')
|
|
->join('wp', 'warehouse_positions', 'wpos', 'wpos.id=wp.id_position')
|
|
->join('wpos', 'warehouse_locations', 'wl', 'wl.id = wpos.id_location')
|
|
->andWhere($spec);
|
|
|
|
if (findModule(\Modules::PRODUCTS_BATCHES)) {
|
|
$positions->addSelect('wp.id_product_batch, pb.code batch_code, pb.date_expiry')
|
|
->leftJoin('wp', 'products_batches', 'pb', 'wp.id_product_batch = pb.id');
|
|
}
|
|
|
|
$positions = $this
|
|
->applyProductPositionOrdering($positions)
|
|
->execute()
|
|
->fetchAll();
|
|
|
|
return $positions;
|
|
}
|
|
|
|
protected function applyProductPositionOrdering(QueryBuilder $qb): QueryBuilder
|
|
{
|
|
$qb->orderBy('wp.pieces > 0', 'DESC');
|
|
|
|
if (findModule(\Modules::PRODUCTS_BATCHES)) {
|
|
$qb->addOrderBy('pb.date_expiry IS NULL')
|
|
->addOrderBy('pb.date_expiry');
|
|
}
|
|
|
|
$qb->addOrderBy('wp.over_supply = "N"', 'DESC')
|
|
->addOrderBy('wl.sort_index', 'ASC')
|
|
->addOrderBy('wp.pieces', 'ASC')
|
|
->addOrderBy('wpos.code', 'ASC');
|
|
|
|
return $qb;
|
|
}
|
|
|
|
public function createStoreItem(StoreItem $storeItem, int $pieces, ?int $position = null, $note = null, $birth = false)
|
|
{
|
|
if (!$position) {
|
|
$position = $this->findTablePosition();
|
|
}
|
|
|
|
$whereBatch = (findModule(\Modules::PRODUCTS_BATCHES)) ? ['id_product_batch' => $storeItem->getIdProductsBatch()] : [];
|
|
if (sqlQueryBuilder()
|
|
->select('id_product')
|
|
->from('warehouse_products')
|
|
->andWhere(Operator::equalsNullable(
|
|
[
|
|
'id_product' => $storeItem->getIdProduct(),
|
|
'id_variation' => $storeItem->getIdVariation(),
|
|
'id_position' => $position,
|
|
] + $whereBatch))
|
|
->execute()
|
|
->fetch()) {
|
|
self::updateOnPosition($storeItem, $position, $pieces);
|
|
} else {
|
|
self::insertIntoPosition($storeItem, $position, $pieces);
|
|
}
|
|
|
|
if ($birth) {
|
|
$this->logger->logProductMove($storeItem, $pieces, null, $position, null, $note);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @param bool $overSupply
|
|
*
|
|
* @return string
|
|
*/
|
|
public function storeItemFromTable($ean, $pieces, $positionFrom_code, $positionTo_code, $overSupply = false, $batchId = false)
|
|
{
|
|
$product_row = self::checkProductByEanAndLocation($ean, null, $positionFrom_code, $batchId);
|
|
$product_row['over_supply'] = ($overSupply ? 'Y' : 'N');
|
|
$id_position = self::getPositionIdByCode($positionTo_code);
|
|
$id_position_from = self::getPositionIdByCode($positionFrom_code);
|
|
|
|
if (empty($id_position)) {
|
|
return 'Zvolena pozice neexistuje';
|
|
}
|
|
|
|
if (empty($id_position_from)) {
|
|
return 'Zdrojová pozice neexistuje';
|
|
}
|
|
|
|
if ($pieces > $product_row['pieces']) {
|
|
return 'Nelze naskladnit tolik kusu!';
|
|
}
|
|
|
|
sqlGetConnection()->transactional(function () use ($pieces, $product_row, $id_position, $id_position_from, &$result) {
|
|
self::moveBetweenPositions(new StoreItem($product_row), $pieces, $id_position_from, $id_position);
|
|
|
|
$result = true;
|
|
});
|
|
|
|
return $result ? $result : 'Nedefinovaná chyba';
|
|
}
|
|
|
|
/**
|
|
* @throws \Exception
|
|
*/
|
|
public function findTablePosition()
|
|
{
|
|
if ($this->box_position) {
|
|
return $this->box_position;
|
|
}
|
|
|
|
return $this->box_position = $this->findTablePositions()[0];
|
|
}
|
|
|
|
/**
|
|
* @return int[]
|
|
*
|
|
* @throws \Exception
|
|
*/
|
|
public function findTablePositions()
|
|
{
|
|
return array_values(sqlFetchAll(sqlQueryBuilder()
|
|
->select('id')
|
|
->from('warehouse_positions')
|
|
->where(Operator::equals(['id_location' => self::LOCATION_TABLE]))
|
|
->orderBy('id', 'ASC')
|
|
->execute(), ['id' => 'id']));
|
|
}
|
|
|
|
/**
|
|
* @return bool|string
|
|
*
|
|
* @throws \Exception
|
|
*/
|
|
public function getPositionIdByCode($code)
|
|
{
|
|
return sqlQueryBuilder()->select('id')->from('warehouse_positions')->where(Operator::like(['code' => $code]))->execute()->fetchColumn();
|
|
}
|
|
|
|
protected function insertIntoPosition(StoreItem $storeItem, int $id_position, int $pieces)
|
|
{
|
|
$whereBatch = (findModule(\Modules::PRODUCTS_BATCHES)) ? ['id_product_batch' => $storeItem->getIdProductsBatch()] : [];
|
|
sqlQueryBuilder()->insert('warehouse_products')
|
|
->directValues([
|
|
'id_product' => $storeItem->getIdProduct(),
|
|
'id_variation' => $storeItem->getIdVariation(),
|
|
'pieces' => $pieces,
|
|
'id_position' => $id_position,
|
|
'over_supply' => $storeItem->isOverSupply() ? 'Y' : 'N',
|
|
] + $whereBatch)->execute();
|
|
}
|
|
|
|
/**
|
|
* @return \Doctrine\DBAL\Driver\Statement|int
|
|
*/
|
|
public function updateOnPosition(StoreItem $storeItem, int $id_position, int $pieces)
|
|
{
|
|
$whereBatch = (findModule(\Modules::PRODUCTS_BATCHES)) ? ['id_product_batch' => $storeItem->getIdProductsBatch()] : [];
|
|
|
|
$qb = sqlQueryBuilder()->update('warehouse_products')
|
|
->set('pieces', 'pieces + :pieces')
|
|
->where(Operator::equalsNullable([
|
|
'id_position' => $id_position,
|
|
'id_product' => $storeItem->getIdProduct(),
|
|
'id_variation' => $storeItem->getIdVariation(),
|
|
] + $whereBatch))
|
|
->setParameter('pieces', $pieces);
|
|
|
|
if ($storeItem->isOverSupply()) {
|
|
$qb->set('over_supply', "'Y'");
|
|
}
|
|
|
|
try {
|
|
$updatedRows = $qb->execute();
|
|
} catch (DriverException $e) {
|
|
if (strpos($e->getMessage(), 'Numeric value out of range')) {
|
|
throw $this->preparePositionException($storeItem, $id_position, $pieces);
|
|
}
|
|
}
|
|
|
|
if ($updatedRows == 0) {
|
|
throw $this->preparePositionException($storeItem, $id_position, $pieces);
|
|
}
|
|
|
|
if (sqlQueryBuilder()
|
|
->select('1')
|
|
->from('warehouse_products', 'wp')
|
|
->join('wp', 'warehouse_positions', 'wpos', 'wpos.id=wp.id_position')
|
|
->join('wpos', 'warehouse_locations', 'wl', 'wl.id=wpos.id_location')
|
|
->where(Operator::equalsNullable([
|
|
'id_product' => $storeItem->getIdProduct(),
|
|
'id_variation' => $storeItem->getIdVariation(),
|
|
'id_position' => $id_position,
|
|
'pieces' => 0,
|
|
'wl.delete_empty' => 'Y',
|
|
] + $whereBatch))
|
|
->execute()
|
|
->fetchAll()) {
|
|
$deleteRows = sqlQueryBuilder()->delete('warehouse_products')->where(Operator::equalsNullable(
|
|
[
|
|
'id_product' => $storeItem->getIdProduct(),
|
|
'id_variation' => $storeItem->getIdVariation(),
|
|
'id_position' => $id_position,
|
|
'pieces' => 0,
|
|
] + $whereBatch))->execute();
|
|
|
|
assert($deleteRows == 1, 'StoreItemWorker::updateOnPosition::deleteIf does not remove any row');
|
|
}
|
|
|
|
return $updatedRows;
|
|
}
|
|
|
|
private function preparePositionException(StoreItem $storeItem, $id_position, $pieces)
|
|
{
|
|
$position = sqlQueryBuilder()->select('code')->from('warehouse_positions')->where(Operator::equals(['id' => $id_position]))->execute()->fetchColumn();
|
|
if ($storeItem->getIdVariation()) {
|
|
$product = new \Variation($storeItem->getIdProduct(), $storeItem->getIdVariation());
|
|
$product->createFromDB();
|
|
} else {
|
|
$product = new \Product($storeItem->getIdProduct());
|
|
$product->createFromDB();
|
|
}
|
|
|
|
$msg = 'Na zdrojové pozici ('.$position.') není dostatek kusů ('.abs($pieces).')! Produkt: '.$product->title;
|
|
|
|
if (findModule(\Modules::PRODUCTS_BATCHES) && $storeItem->getIdProductsBatch()) {
|
|
$msg .= ' Šarže: ';
|
|
$msg .= sqlQueryBuilder()->select('code')->from('products_batches',
|
|
'pb')->where(Operator::equals(['id' => $storeItem->getIdProductsBatch()]))->execute()->fetchOne();
|
|
}
|
|
|
|
return new WarehouseBaseException($msg);
|
|
}
|
|
|
|
/**
|
|
* @return QueryBuilder
|
|
*
|
|
* @throws \Exception
|
|
*/
|
|
public function getMissingProductsQuery($showNotInStore = false)
|
|
{
|
|
$cfg = Config::get();
|
|
|
|
$warehouseQuantity = sqlQueryBuilder()->select('wp.id_product', 'wp.id_variation', 'SUM(wp.pieces) quantity')
|
|
->from('warehouse_products', 'wp')
|
|
->groupBy('wp.id_product', 'wp.id_variation');
|
|
|
|
$ordersQuantity = sqlQueryBuilder()->select('oi.id_product', 'oi.id_variation', 'SUM(pieces) quantity')
|
|
->from('order_items', 'oi')
|
|
->leftJoin('oi', 'orders', 'o', 'oi.id_order = o.id')
|
|
->leftJoin('oi', 'warehouse_orders', 'wo', 'oi.id_order = wo.id_order')
|
|
->where(Operator::andX(
|
|
Operator::inIntArray(array_merge(getStatuses('notpacked'), $cfg['Order']['Status']['warehouse_remaining'] ?? []), 'o.status'),
|
|
'wo.id_position IS NOT NULL OR wo.id IS NULL'
|
|
))
|
|
->andWhere(\Query\Order::activeOnly())
|
|
->andWhere(Operator::not(\Query\Order::byItemType('return')))
|
|
->groupBy('oi.id_product', 'oi.id_variation');
|
|
|
|
$qb = sqlQueryBuilder()
|
|
->select();
|
|
|
|
if ($showNotInStore) {
|
|
$qb->fromProducts()
|
|
->joinVariationsOnProducts()
|
|
->leftJoin('p', 'warehouse_products', 'wp', 'p.id = wp.id_product AND (pv.id=wp.id_variation OR wp.id_variation IS NULL)')
|
|
->andWhere('wp.id_product IS NULL');
|
|
} else {
|
|
$qb->from('warehouse_products', 'wp')
|
|
->leftJoin('wp', 'products', 'p', 'p.id = wp.id_product')
|
|
->joinVariationsOnProducts();
|
|
}
|
|
|
|
$qb->leftJoinSubQuery('p', $warehouseQuantity, 'wq', 'wq.id_product = p.id AND (wq.id_variation = pv.id OR (wq.id_variation IS NULL AND pv.id IS NULL))')
|
|
->leftJoinSubQuery('p', $ordersQuantity, 'oq', 'oq.id_product = p.id AND (oq.id_variation = pv.id OR (oq.id_variation IS NULL AND pv.id IS NULL))')
|
|
->andwhere('COALESCE(pv.in_store, p.in_store) != COALESCE(wq.quantity, 0) - COALESCE(oq.quantity, 0)')
|
|
->groupBy('p.id, pv.id');
|
|
|
|
if (findModule(\Modules::PRODUCT_SETS, \Modules::SUB_CALCULATE_STOCK)) {
|
|
// Hide sets - virtual store
|
|
$isSetMaster = sqlQueryBuilder()->select('1')
|
|
->from('products_sets', 'ps')
|
|
->where('ps.id_product = p.id');
|
|
|
|
$qb->andWhere(Operator::not(Operator::exists($isSetMaster)));
|
|
}
|
|
|
|
return $qb;
|
|
}
|
|
|
|
/**
|
|
* @return array
|
|
*
|
|
* @throws \Exception
|
|
*/
|
|
public function getProductsOnPositions(array $id_positions)
|
|
{
|
|
$products = sqlQueryBuilder()
|
|
->select('wp.id_product, wp.id_variation, p.title as p_title, pv.title as pv_title,
|
|
COALESCE(pv.ean, p.ean) as ean,
|
|
COALESCE(pv.code, p.code) as code,
|
|
wp.pieces, wpos.code as position, wp.inventory_date, wp.over_supply,
|
|
DATEDIFF(NOW(),wpos.inventory_date) days_after_inventory,
|
|
GROUP_CONCAT(pos.ean SEPARATOR ";") as supplier_eans, GROUP_CONCAT(pos.code SEPARATOR ";") as supplier_codes
|
|
')
|
|
->from('warehouse_products', 'wp')
|
|
->leftJoin('wp', 'warehouse_positions', 'wpos', 'wpos.id=wp.id_position')
|
|
->leftJoin('wp', 'products', 'p', 'p.id=wp.id_product')
|
|
->leftJoin('wp', 'products_variations', 'pv', 'pv.id=wp.id_variation')
|
|
->leftJoin('wp', 'products_of_suppliers', 'pos', 'wp.id_product=pos.id_product AND (wp.id_variation=pos.id_variation OR wp.id_variation IS NULL)')
|
|
->where(Operator::inIntArray($id_positions, 'wpos.id'))
|
|
->addSelect(\Query\Product::withProductPhotoId(true))
|
|
->orderBy('wpos.id')
|
|
->addOrderBy('wp.pieces', 'DESC')
|
|
->groupBy('wp.id_product, wp.id_variation');
|
|
|
|
if (findModule(\Modules::PRODUCTS_BATCHES)) {
|
|
$products->leftJoin('wp', 'products_batches', 'pb', 'pb.id = wp.id_product_batch')
|
|
->addSelect('wp.id_product_batch, pb.code batch_code, pb.date_expiry, p.batch_number_require')
|
|
->addGroupBy('wp.id_product_batch');
|
|
}
|
|
$products = $products->execute()->fetchAll();
|
|
foreach ($products as $key => &$item) {
|
|
$item['image'] = getImage($item['id_photo'], null, null, 4);
|
|
$item['over_supply'] = $item['over_supply'] == 'Y';
|
|
$item['ean'] = formatEAN($item['ean']);
|
|
|
|
$item['suppliers_eans'] = [];
|
|
foreach (explode(';', $item['supplier_eans']) as $ean) {
|
|
$item['suppliers_eans'][formatEAN($ean)] = formatEAN($ean);
|
|
}
|
|
|
|
$codes = explode(';', $item['supplier_codes']);
|
|
$item['suppliers_codes'] = array_combine($codes, $codes);
|
|
}
|
|
|
|
return $products;
|
|
}
|
|
|
|
/**
|
|
* @return array
|
|
*
|
|
* @throws \Exception
|
|
*/
|
|
public function startCheckout($order_no, $box)
|
|
{
|
|
return sqlGetConnection()->transactional(function () use ($order_no, $box) {
|
|
$checkoutValid = $this->startCheckoutValidate($order_no, $box);
|
|
|
|
if ($checkoutValid !== true) {
|
|
return $checkoutValid;
|
|
}
|
|
|
|
return $this->startCheckoutRetrieveItems($order_no, $box);
|
|
});
|
|
}
|
|
|
|
public function startMassCheckout($ordersBoxes)
|
|
{
|
|
return sqlGetConnection()->transactional(function () use ($ordersBoxes) {
|
|
foreach ($ordersBoxes as $orderBox) {
|
|
$checkoutValid = $this->startCheckoutValidate($orderBox->order_no, $orderBox->box);
|
|
|
|
if ($checkoutValid !== true) {
|
|
return $checkoutValid;
|
|
}
|
|
}
|
|
$ordersItemsAll = [];
|
|
$ordersIds = [];
|
|
$ordersStateAll = [];
|
|
$notesWarehouse = [];
|
|
$resultAll = true;
|
|
foreach ($ordersBoxes as $orderBox) {
|
|
$order = $this->startCheckoutRetrieveItems($orderBox->order_no, $orderBox->box);
|
|
$ordersItemsAll = array_merge($ordersItemsAll, $order['order']['items']);
|
|
$ordersIds[$orderBox->order_no] = $order['order']['id'];
|
|
$notesWarehouse[$orderBox->order_no] = $order['order']['note_warehouse'];
|
|
$ordersStateAll = array_merge($ordersStateAll, $order['state']->checkouts);
|
|
if (!$order['result']) {
|
|
$resultAll = false;
|
|
}
|
|
}
|
|
|
|
$this->sortByPositionString($ordersItemsAll, 'mainPosition');
|
|
|
|
$order = [
|
|
'items' => $ordersItemsAll,
|
|
'note_warehouse' => $notesWarehouse,
|
|
];
|
|
|
|
return [
|
|
'orders' => $ordersIds,
|
|
'order' => $order,
|
|
'state' => ['checkouts' => $ordersStateAll],
|
|
'result' => $resultAll,
|
|
];
|
|
});
|
|
}
|
|
|
|
protected function startCheckoutValidate($order_no, $box)
|
|
{
|
|
try {
|
|
$order = \Order::createFromDbOrderNo($order_no);
|
|
} catch (\InvalidArgumentException $e) {
|
|
return $this->returnError("Objednávka {$order_no} neexistuje!");
|
|
}
|
|
$box_code = $box;
|
|
$box = sqlQueryBuilder()->select('wpos.id, wo.id_order', 'COUNT(wp.id_product) products')
|
|
->from('warehouse_positions', 'wpos')
|
|
->leftJoin('wpos', 'warehouse_orders', 'wo', 'wpos.id = wo.id_position')
|
|
->leftJoin('wpos', 'warehouse_products', 'wp', 'wp.id_position = wpos.id')
|
|
->where(Operator::like(['wpos.code' => $box, 'wpos.id_location' => static::LOCATION_BOX]))
|
|
->groupBy('wpos.id')
|
|
->execute()->fetch();
|
|
|
|
$warehouseOrder = $this->getWarehouseOrder($order->id);
|
|
|
|
if (!empty($this->startOrderCheckoutStatuses)) {
|
|
if (!in_array($order->status, $this->startOrderCheckoutStatuses)) {
|
|
return $this->returnError("Objednávku {$order_no} nelze vychystat, protože je v nesprávném stavu!");
|
|
}
|
|
}
|
|
|
|
if (!$box) {
|
|
return $this->returnError("Přepravka {$box_code} neexistuje!");
|
|
} elseif (empty($warehouseOrder) && $order->status_storno) {
|
|
return $this->returnError("Objednávka {$order_no} byla stornována!");
|
|
} elseif ($box['id_order'] && $box['id_order'] != $order->id) {
|
|
return $this->returnError("Přepravka {$box_code} už obsahuje objednávku {$box['id_order']}!");
|
|
} elseif (!empty($warehouseOrder) && $warehouseOrder['id_position'] != $box['id']) {
|
|
if ($warehouseOrder['id_position']) {
|
|
return $this->returnError("Objednávka {$order_no} už je vychystávána do přepravky {$warehouseOrder['code']}!");
|
|
} else {
|
|
return $this->returnError("Objednávka {$order_no} už byla vychystána {$warehouseOrder['date_start']}!");
|
|
}
|
|
} elseif ($warehouseOrder === false && $box['products'] > 0) {
|
|
return $this->returnError("Přepravka {$box_code} není prázdná!");
|
|
}
|
|
|
|
// Assert box is empty
|
|
if (!($warehouseOrder['state'] ?? false)) {
|
|
assert((int) returnSQLResult('SELECT COUNT(*) FROM warehouse_products WHERE id_position=:id', ['id' => $box['id']]) === 0, 'Box for new order is empty');
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
protected function startCheckoutRetrieveItems($order_no, $box)
|
|
{
|
|
$order = \Order::createFromDbOrderNo($order_no);
|
|
$box = sqlQueryBuilder()->select('wpos.id', 'wpos.code')
|
|
->from('warehouse_positions', 'wpos')
|
|
->where(Operator::like(['wpos.code' => $box, 'wpos.id_location' => static::LOCATION_BOX]))
|
|
->execute()->fetch();
|
|
|
|
// Assign box to order
|
|
sqlQuery(
|
|
'INSERT INTO warehouse_orders (id_order, id_position, date_start) VALUES (:id_order, :id_position, NOW())
|
|
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)',
|
|
['id_position' => $box['id'], 'id_order' => $order->id]
|
|
);
|
|
$id_warehouse_order = sqlInsertId();
|
|
|
|
list($checkouts, $items) = $this->getCheckoutItems($id_warehouse_order, $order);
|
|
|
|
// Log to order used BOX
|
|
$order->logHistory("Objednávka se vychystává do přepravky: {$box['code']}", false);
|
|
|
|
$order = [
|
|
'id' => $order->id,
|
|
'id_warehouse' => $id_warehouse_order,
|
|
'order_no' => $order->order_no,
|
|
'items' => array_values($items), // Pass as array to preserve ordering
|
|
'note_warehouse' => $order->getData('note_warehouse'),
|
|
];
|
|
|
|
$state = json_decode_strict($warehouseOrder['state'] ?? '{}');
|
|
$state->checkouts = $checkouts;
|
|
|
|
return [
|
|
'order' => $order,
|
|
'state' => $state,
|
|
'result' => !empty($order),
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @param $statuses array|null
|
|
*/
|
|
public function setStartOrderCheckoutStatuses($statuses)
|
|
{
|
|
$this->startOrderCheckoutStatuses = $statuses;
|
|
}
|
|
|
|
/**
|
|
* @param null $date
|
|
*
|
|
* @throws \Throwable
|
|
*/
|
|
public function moveBetweenPositions(StoreItem $storeItem, int $pieces, int $old_position, int $new_position, $date = null)
|
|
{
|
|
sqlGetConnection()->transactional(function () use ($storeItem, $pieces, $old_position, $new_position, $date, &$result) {
|
|
$this->updateOnPosition($storeItem, $old_position, -$pieces);
|
|
|
|
$this->createStoreItem($storeItem, $pieces, $new_position);
|
|
|
|
$this->eventDispatcher->dispatch(new WarehouseProductMoveEvent($storeItem, $pieces, $old_position, $new_position));
|
|
|
|
$result = $this->logger->logProductMove($storeItem, $pieces, $old_position, $new_position, $date);
|
|
});
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* @throws \Exception
|
|
* @throws \Throwable
|
|
*/
|
|
public function finish($id_warehouse_order, $state)
|
|
{
|
|
sqlGetConnection()->transactional(function () use ($id_warehouse_order, $state, &$return) {
|
|
$result = $this->commit($id_warehouse_order, $state);
|
|
|
|
if (!$result['result']) {
|
|
return $result;
|
|
}
|
|
|
|
// Mark order finished
|
|
sqlQuery('UPDATE warehouse_orders SET date_finish=NOW() WHERE id=:id',
|
|
['id' => $id_warehouse_order]);
|
|
|
|
$return = [
|
|
'result' => true,
|
|
];
|
|
});
|
|
|
|
return $return;
|
|
}
|
|
|
|
public function finishCompletedOrders($orders)
|
|
{
|
|
foreach ($orders as $orderId) {
|
|
$warehouseId = sqlQueryBuilder()->select('id')->from('warehouse_orders')
|
|
->where(Operator::equals(['id_order' => $orderId]))
|
|
->execute()->fetchColumn();
|
|
|
|
$differences = $this->checkOrderMatchesBoxContent($warehouseId);
|
|
if (empty($differences)) {
|
|
sqlQueryBuilder()->update('warehouse_orders')->set('date_finish', 'NOW()')
|
|
->where(Operator::equals(['id_order' => $orderId]))->execute();
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @throws \Exception
|
|
* @throws \Throwable
|
|
*/
|
|
public function finishMass($ordersIds, $state)
|
|
{
|
|
sqlGetConnection()->transactional(function () use ($ordersIds, $state, &$return) {
|
|
$result = $this->commitMass($state);
|
|
|
|
if (!$result['result']) {
|
|
return $result;
|
|
}
|
|
|
|
// Mark orders finished
|
|
sqlQueryBuilder()->update('warehouse_orders')->set('date_finish', 'NOW()')
|
|
->where(Operator::inStringArray($ordersIds, 'id_order'))->execute();
|
|
|
|
$return = [
|
|
'result' => true,
|
|
];
|
|
});
|
|
|
|
return $return;
|
|
}
|
|
|
|
/**
|
|
* @return array
|
|
*
|
|
* @throws \Exception
|
|
* @throws \Throwable
|
|
*/
|
|
public function commit($id_warehouse_order, &$state)
|
|
{
|
|
sqlGetConnection()->transactional(function () use ($id_warehouse_order, &$state, &$return) {
|
|
$checkouts = &$state->checkouts;
|
|
|
|
$warehouse_order = sqlFetch(sqlQuery('SELECT * from warehouse_orders where id=:id FOR UPDATE', ['id' => $id_warehouse_order]));
|
|
|
|
if (!$warehouse_order) {
|
|
return $return = $this->returnError('Nemohu najít rozpracovanou objednávku!');
|
|
}
|
|
|
|
$commits = json_decode_strict($warehouse_order['state'] ?? '{}' ?: '{}', true)['commits'] ?? [];
|
|
|
|
foreach ($checkouts as &$checkedItem) {
|
|
$this->commitOrderItem($commits, $checkedItem, $warehouse_order['id_order'], $warehouse_order['id_position']);
|
|
}
|
|
|
|
$state->commits = $commits;
|
|
|
|
// Save order status
|
|
sqlQuery('UPDATE warehouse_orders SET state=:state WHERE id=:id',
|
|
['id' => $id_warehouse_order, 'state' => json_encode($state)]);
|
|
|
|
$return = [
|
|
'result' => true,
|
|
];
|
|
});
|
|
|
|
return $return;
|
|
}
|
|
|
|
/**
|
|
* @return array
|
|
*
|
|
* @throws \Exception
|
|
* @throws \Throwable
|
|
*/
|
|
public function commitMass(&$state)
|
|
{
|
|
sqlGetConnection()->transactional(function () use (&$state, &$return) {
|
|
$checkouts = &$state->checkouts;
|
|
|
|
$warehouse_orders_positions = [];
|
|
|
|
$orderIds = array_unique(array_filter(array_map(function ($checkout) {
|
|
return $checkout->id_order ?? false;
|
|
}, $checkouts)));
|
|
|
|
$commits = [];
|
|
foreach ($orderIds as $orderId) {
|
|
$warehouse_order = sqlFetch(sqlQuery('SELECT * from warehouse_orders where id_order=:id_order FOR UPDATE', ['id_order' => $orderId]));
|
|
if (!$warehouse_order) {
|
|
return $return = $this->returnError('Nemohu najít rozpracovanou objednávku!');
|
|
}
|
|
// separate commits when multipicking
|
|
$commits[$orderId] = json_decode_strict($warehouse_order['state'] ?? '{}' ?: '{}', true)['commits'] ?? [];
|
|
$warehouse_orders_positions[$warehouse_order['id_order']] = $warehouse_order['id_position'];
|
|
}
|
|
|
|
foreach ($checkouts as &$checkedItem) {
|
|
$orderId = $checkedItem->id_order ?? false;
|
|
if ($orderId) {
|
|
$this->commitOrderItem($commits[$orderId], $checkedItem, $orderId, $warehouse_orders_positions[$orderId]);
|
|
}
|
|
}
|
|
|
|
$checkoutsOriginal = $state->checkouts;
|
|
$commitsAll = [];
|
|
|
|
// Save order status
|
|
foreach ($commits as $orderId => $orderCommits) {
|
|
$state->commits = $orderCommits;
|
|
$commitsAll = $orderCommits + $commitsAll;
|
|
|
|
// filter checkouts for multipicking
|
|
$state->checkouts = array_filter($checkoutsOriginal, function ($checkout) use ($orderId) {
|
|
return ($checkout->id_order ?? false) == $orderId;
|
|
});
|
|
|
|
sqlQuery('UPDATE warehouse_orders SET state=:state WHERE id_order=:id_order',
|
|
['id_order' => $orderId, 'state' => json_encode($state)]);
|
|
}
|
|
|
|
$state->checkouts = $checkoutsOriginal;
|
|
$state->commits = $commitsAll;
|
|
|
|
$return = [
|
|
'result' => true,
|
|
];
|
|
});
|
|
|
|
return $return;
|
|
}
|
|
|
|
protected function commitOrderItem(&$commits, $checkedItem, $orderId, $warehousePositionId)
|
|
{
|
|
if ($checkedItem->uuid ?? '') {
|
|
// Skip if this uuid already committed
|
|
if (array_key_exists($checkedItem->uuid, $commits)) {
|
|
return;
|
|
}
|
|
|
|
if (strpos($checkedItem->id_item, '_')) {
|
|
// V id neni id polozky, ale produkt_variata, protoze je to smazana polozka
|
|
$parts = explode('_', $checkedItem->id_item);
|
|
$order_item = ['id_product' => $parts[0], 'id_variation' => $parts[1] ?: null];
|
|
} else {
|
|
$order_item = sqlQueryBuilder()->select('*')
|
|
->from('order_items', 'oi')
|
|
->where(Operator::equals(['oi.id' => $checkedItem->id_item]))
|
|
->execute()
|
|
->fetch();
|
|
}
|
|
|
|
$item_position = $this->getPositionIdByCode($checkedItem->position);
|
|
|
|
if (findModule(\Modules::PRODUCTS_BATCHES) && ($checkedItem->productBatch ?? false)) {
|
|
$order_item['id_product_batch'] = $checkedItem->productBatch->id ?? null;
|
|
}
|
|
|
|
$date = $checkedItem->date ?? null;
|
|
|
|
if ($date) {
|
|
$date = new \DateTime($date);
|
|
$date->setTimezone(new \DateTimeZone(date_default_timezone_get()));
|
|
}
|
|
$storeItem = new StoreItem($order_item);
|
|
$this->logger->activateOrder($orderId, function () use (&$commits, $checkedItem, $storeItem, $item_position, $date, $warehousePositionId) {
|
|
if ($checkedItem->pieces > 0) {
|
|
$commits[$checkedItem->uuid] = $this->moveBetweenPositions($storeItem, $checkedItem->pieces, $item_position, $warehousePositionId, $date);
|
|
} else {
|
|
$commits[$checkedItem->uuid] = $this->moveBetweenPositions($storeItem, -$checkedItem->pieces, $warehousePositionId, $item_position, $date);
|
|
}
|
|
});
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @throws \Exception
|
|
*/
|
|
public function cleanCompletedBox($box_id)
|
|
{
|
|
sqlGetConnection()->transactional(function () use ($box_id) {
|
|
// Delete products from BOX - takto sklad opouštějí produky
|
|
$items = sqlQueryBuilder()->select('*')
|
|
->from('warehouse_products')
|
|
->where(Operator::equals(['id_position' => $box_id]))
|
|
->execute()->fetchAll();
|
|
|
|
foreach ($items as $item) {
|
|
$this->logger->logProductMove(new StoreItem($item), $item['pieces'], $box_id, null, null);
|
|
}
|
|
|
|
sqlQuery('DELETE FROM warehouse_products WHERE id_position=:id', ['id' => $box_id]);
|
|
|
|
// Detach order from box
|
|
sqlQuery('UPDATE warehouse_orders SET id_position = NULL, date_close = NOW() WHERE id_position=:id_position', ['id_position' => $box_id]);
|
|
});
|
|
}
|
|
|
|
/**
|
|
* @return array
|
|
*
|
|
* @throws \Throwable
|
|
*/
|
|
public function moveProduct($ean, $pieces, $positionFrom, $positionTo, $overSupply = false, $batchId = false)
|
|
{
|
|
$product = self::checkProductByEanAndLocation($ean, null, $positionFrom, $batchId);
|
|
$positionFrom = self::getPositionIdByCode($positionFrom);
|
|
$positionTo = self::getPositionIdByCode($positionTo);
|
|
|
|
if (!$product) {
|
|
return $this->returnError('Produkt nenalezen!');
|
|
}
|
|
|
|
if (!$positionFrom) {
|
|
return $this->returnError('Zdrojová pozice nenalezena!');
|
|
}
|
|
|
|
if (!$positionTo) {
|
|
return $this->returnError('Cílová pozice nenalezena!');
|
|
}
|
|
|
|
$this->moveBetweenPositions(new StoreItem($product + ['over_supply' => $overSupply]), (int) $pieces, $positionFrom, $positionTo, null);
|
|
|
|
return [
|
|
'result' => true,
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @throws \Exception
|
|
*/
|
|
public function checkOrderMatchesBoxContent($id_warehouse_order, $id_order = null, $id_box = null): array
|
|
{
|
|
$warehouse_order = sqlQueryBuilder()->select('*')->from('warehouse_orders')->where(Operator::equals(['id' => $id_warehouse_order]))->execute()->fetch();
|
|
if (!$warehouse_order && $id_order !== null) {
|
|
$orderID = $id_order;
|
|
} else {
|
|
$orderID = $warehouse_order['id_order'];
|
|
}
|
|
|
|
$idBoxToCheck = $id_box ?: ($warehouse_order['id_position'] ?? null);
|
|
$positionSubselect = sqlQueryBuilder()
|
|
->select('CONCAT(wl.sort_index, "|", wpos.code)')
|
|
->from('warehouse_locations', 'wl')
|
|
->join('wl', 'warehouse_positions', 'wpos', 'wpos.id_location = wl.id AND wpos.id_location > 2')
|
|
->join('wpos', 'warehouse_products', 'wp', 'wpos.id = wp.id_position')
|
|
->where('oi.id_product = wp.id_product AND (oi.id_variation=wp.id_variation OR wp.id_variation IS NULL)')
|
|
->setMaxResults(1);
|
|
|
|
if (findModule(\Modules::PRODUCTS_BATCHES)) {
|
|
$positionSubselect->leftJoin('wp', 'products_batches', 'pb', 'wp.id_product_batch = pb.id');
|
|
}
|
|
|
|
$this->applyProductPositionOrdering($positionSubselect);
|
|
|
|
$order_items = sqlQueryBuilder()->select('oi.id_product, oi.id_variation, SUM(oi.pieces) as pieces, oi.id as id_item, oi.id_order')
|
|
->addSubselect($positionSubselect, 'position')
|
|
->from('order_items', 'oi')
|
|
->where(Operator::equals(['id_order' => $orderID]))
|
|
->andWhere('oi.id_product IS NOT NULL')
|
|
->andWhere('oi.pieces > 0')
|
|
->groupBy('oi.id_product, oi.id_variation')
|
|
->execute()->fetchAll();
|
|
|
|
$box_items = sqlQueryBuilder()->select('wp.id_product, wp.id_variation, SUM(wp.pieces) as pieces')
|
|
->addSelect($orderID.' as id_order')
|
|
->addSelect('(SELECT CONCAT(MIN(wl.sort_index), "|", MIN(wpos.code))
|
|
FROM warehouse_locations wl
|
|
JOIN warehouse_positions wpos ON wpos.id_location = wl.id
|
|
WHERE wpos.id = wp.id_position) AS position')
|
|
->from('warehouse_products', 'wp')
|
|
->where(Operator::equals(['id_position' => $idBoxToCheck]))
|
|
->groupBy('wp.id_product, wp.id_variation');
|
|
|
|
if (findModule(\Modules::PRODUCTS_BATCHES)) {
|
|
$box_items->addSelect('GROUP_CONCAT(wp.id_product_batch) as batches_in_box');
|
|
}
|
|
|
|
$box_items = $box_items->execute()->fetchAll();
|
|
|
|
foreach ($box_items as &$box_item) {
|
|
if (empty($box_item['batches_in_box'])) {
|
|
continue;
|
|
}
|
|
$box_item['batches_in_box'] = sqlQueryBuilder()->select('pb.id id_product_batch, pb.code batch_code, pb.date_expiry, wp.pieces')
|
|
->from('warehouse_products', 'wp')
|
|
->innerJoin('wp', 'products_batches', 'pb', 'wp.id_product_batch = pb.id')
|
|
->where(Operator::equals(['wp.id_position' => $idBoxToCheck]))
|
|
->andWhere(Operator::inIntArray(explode(',', $box_item['batches_in_box']), 'pb.id'))
|
|
->execute()->fetchAllAssociative();
|
|
}
|
|
|
|
$errors = $this->compareItemsVsBox($order_items, $box_items);
|
|
|
|
return $errors;
|
|
}
|
|
|
|
public function getItemsFromOrdersBox($id_order)
|
|
{
|
|
return sqlQueryBuilder()->select('wp.*')
|
|
->from('warehouse_orders', 'wo')
|
|
->join('wo', 'warehouse_products', 'wp', 'wp.id_position = wo.id_position')
|
|
->where(Operator::equals(['wo.id_order' => $id_order]))
|
|
->execute()->fetchAll();
|
|
}
|
|
|
|
/**
|
|
* @param array $difference
|
|
*
|
|
* @return array
|
|
*/
|
|
public function generateCheckoutItems($difference)
|
|
{
|
|
$checkouts = [];
|
|
$items = array_filter(array_map(function ($item) use (&$checkouts) {
|
|
if ($item['pieces'] < 0) {
|
|
return false;
|
|
}
|
|
$item_id = $item['id_item'] ?? "{$item['id_product']}_{$item['id_variation']}";
|
|
|
|
$variation = sqlQueryBuilder()->select('COALESCE(pv.ean, p.ean) ean', 'COALESCE(pv.code, p.code) code, pv.title title')
|
|
->fromProducts()
|
|
->joinVariationsOnProducts()
|
|
->where(Operator::equalsNullable(['p.id' => $item['id_product'], 'pv.id' => $item['id_variation']]))
|
|
->addSelect(\Query\Product::withProductPhotoId(true))
|
|
->execute()
|
|
->fetch();
|
|
|
|
$variation['ean'] = formatEAN($variation['ean']);
|
|
$image = getImage($variation['id_photo'], null, null, 4);
|
|
|
|
if ($item['pieces_box'] > 0) {
|
|
$checkouts[] = [
|
|
'ean' => $variation['ean'],
|
|
'position' => '',
|
|
'id_item' => $item_id,
|
|
'id_order' => $item['id_order'] ?? null,
|
|
'pieces' => (int) $item['pieces_box'],
|
|
'productsBatches' => $item['batches_in_box'] ?? [],
|
|
];
|
|
}
|
|
|
|
return [
|
|
'id' => $item_id,
|
|
'id_product' => $item['id_product'],
|
|
'id_variation' => $item['id_variation'],
|
|
'pieces' => (int) $item['pieces'],
|
|
'title' => $item['product']->title,
|
|
'ean' => $variation['ean'],
|
|
'eans' => $variation['ean'] ? [$variation['ean']] : [],
|
|
'code' => $variation['code'],
|
|
'variation_title' => $variation['title'] ?? '',
|
|
'note' => '',
|
|
'image' => $image,
|
|
'id_order' => $item['id_order'] ?? null,
|
|
'mainPosition' => $item['position'],
|
|
'positions' => $this->getProductPositions(Operator::andX(
|
|
Operator::equalsNullable(['p.id' => $item['id_product'], 'pv.id' => $item['id_variation']]),
|
|
Operator::not(Operator::inIntArray([self::LOCATION_BOX], 'wpos.id_location')))
|
|
),
|
|
];
|
|
}, $difference));
|
|
|
|
return [$checkouts, $items];
|
|
}
|
|
|
|
/**
|
|
* @return array
|
|
*/
|
|
public function compareItemsVsBox($items, $box_items)
|
|
{
|
|
$errors = [];
|
|
// Missing in box, incorrect pieces
|
|
foreach ($items as $item) {
|
|
$box_found = array_filter($box_items, function ($x) use ($item) {
|
|
return $x['id_product'] === $item['id_product'] && $x['id_variation'] === $item['id_variation'];
|
|
});
|
|
|
|
$box_item = reset($box_found);
|
|
|
|
if (!$box_item) {
|
|
if ($item['pieces'] > 0) {
|
|
$errors[] = array_merge($item, ['message' => "V přepravce chybí {$item['pieces']}x produkt {PRODUCT}", 'pieces_box' => '0']);
|
|
}
|
|
continue;
|
|
}
|
|
|
|
if ($box_item['pieces'] != $item['pieces']) {
|
|
$errors[] = array_merge($item, [
|
|
'message' => "V přepravce je špatný počet kusů ({$box_item['pieces']} přepravka != {$item['pieces']} objednávka) produktu {PRODUCT}",
|
|
'pieces_box' => $box_item['pieces'],
|
|
'batches_in_box' => $box_item['batches_in_box'],
|
|
]);
|
|
}
|
|
|
|
unset($box_items[key($box_found)]);
|
|
}
|
|
|
|
// Missing in order (remaining in box)
|
|
foreach ($box_items as $box_item) {
|
|
if ($box_item['pieces'] > 0) {
|
|
$errors[] = array_merge($box_item, ['message' => "V přepravce přebývá {$box_item['pieces']}x produkt {PRODUCT}", 'pieces_box' => $box_item['pieces'], 'pieces' => '0']);
|
|
}
|
|
}
|
|
|
|
foreach ($errors as $key => &$error) {
|
|
/** @var \Product $product */
|
|
$product = new \Product();
|
|
$product->createFromDB($error['id_product']);
|
|
if ($product->isVirtual()) {
|
|
unset($errors[$key]);
|
|
continue;
|
|
}
|
|
|
|
$error['product'] = $product;
|
|
|
|
$productName = sqlQueryBuilder()->select('CONCAT_WS(" ", p.title, pv.title)')
|
|
->fromProducts()->joinVariationsOnProducts()
|
|
->where(Operator::equalsNullable(['p.id' => $error['id_product'], 'pv.id' => $error['id_variation']]))
|
|
->execute()->fetchColumn();
|
|
|
|
$error['message'] = replacePlaceholders($error['message'], ['PRODUCT' => $productName]);
|
|
}
|
|
|
|
$this->sortByPositionString($errors, 'position');
|
|
|
|
return $errors;
|
|
}
|
|
|
|
public function getWarehouseOrder($id_order)
|
|
{
|
|
$warehouseOrder = sqlQueryBuilder()->select('wo.*, wp.code')
|
|
->from('warehouse_orders', 'wo')
|
|
->leftJoin('wo', 'warehouse_positions', 'wp', 'wp.id = wo.id_position')
|
|
->where(Operator::equals(['id_order' => $id_order]))
|
|
->execute()->fetch();
|
|
|
|
return $warehouseOrder;
|
|
}
|
|
|
|
/**
|
|
* @param string $id_warehouse_order
|
|
* @param \Order $order
|
|
*
|
|
* @return array
|
|
*
|
|
* @throws \Exception
|
|
*/
|
|
public function getCheckoutItems($id_warehouse_order, &$order)
|
|
{
|
|
// Get box vs order difference
|
|
$difference = $this->checkOrderMatchesBoxContent($id_warehouse_order);
|
|
|
|
// Get unfinished order items and generate checkouts
|
|
list($checkouts, $items) = $this->generateCheckoutItems($difference);
|
|
|
|
// Get EANs from suppliers
|
|
$suppliers_eans = sqlQueryBuilder()->select('oi.id id_item, pos.ean, pos.id_product, pos.id_variation')
|
|
->from('order_items', 'oi')
|
|
->join('oi', 'products_of_suppliers', 'pos', 'pos.id_product = oi.id_product AND '.Operator::equalsToOrNullable('pos.id_variation', 'oi.id_variation'))
|
|
->where(Operator::inIntArray(array_keys($order->fetchItems()), 'oi.id'))
|
|
->execute()->fetchAll();
|
|
|
|
foreach ($items as &$item) {
|
|
foreach ($suppliers_eans as $row) {
|
|
if ($item['id'] == $row['id_item'] || $item['id'] == "{$row['id_product']}_{$row['id_variation']}") {
|
|
$item['eans'][] = formatEAN($row['ean']);
|
|
}
|
|
}
|
|
}
|
|
|
|
return [$checkouts, $items];
|
|
}
|
|
|
|
/**
|
|
* @return array
|
|
*
|
|
* @throws \Exception
|
|
*/
|
|
public function loadOrderStoreItems($id_warehouse_order, $id_order = null)
|
|
{
|
|
$warehouse_order = sqlQueryBuilder()->select('id, id_order')
|
|
->from('warehouse_orders');
|
|
|
|
if ($id_order) {
|
|
$warehouse_order->where(Operator::equals(['id_order' => $id_order]));
|
|
} else {
|
|
$warehouse_order->where(Operator::equals(['id' => $id_warehouse_order]));
|
|
}
|
|
$warehouse_order = $warehouse_order->execute()->fetch();
|
|
|
|
$order = new \Order($warehouse_order['id_order']);
|
|
|
|
return $this->getCheckoutItems($warehouse_order['id'], $order);
|
|
}
|
|
|
|
/**
|
|
* @return array
|
|
*
|
|
* @throws \Exception
|
|
*/
|
|
public function loadOrderStoreMassItems($ordersId)
|
|
{
|
|
$checkoutsAll = [];
|
|
$itemsAll = [];
|
|
foreach ($ordersId as $orderId) {
|
|
list($checkouts, $items) = $this->loadOrderStoreItems(null, $orderId);
|
|
$checkoutsAll = array_merge($checkoutsAll, $checkouts);
|
|
$itemsAll = array_merge($itemsAll, $items);
|
|
}
|
|
|
|
$this->sortByPositionString($itemsAll, 'mainPosition');
|
|
|
|
return [$checkoutsAll, $itemsAll];
|
|
}
|
|
|
|
protected function sortByPositionString(&$items, $field)
|
|
{
|
|
usort($items, function ($x, $y) use ($field) {
|
|
return explode('|', $x[$field]) <=> explode('|', $y[$field]);
|
|
});
|
|
}
|
|
|
|
protected function getPiecesInOrder($row, $code_position)
|
|
{
|
|
$event = new WarehouseProductEvent();
|
|
$row['table_position'] = $code_position;
|
|
$event->setStoreInItemRow($row);
|
|
$this->eventDispatcher->dispatch($event, WarehouseProductEvent::PIECES_IN_ORDERS_SPEC);
|
|
|
|
return sqlQueryBuilder()
|
|
->select('SUM(oi.pieces) as piecesInOrder')
|
|
->from('orders', 'o')
|
|
->leftJoin('o', 'order_items', 'oi', 'oi.id_order = o.id')
|
|
->setParameters(
|
|
[
|
|
'id_product' => $row['id_product'],
|
|
'id_variation' => $row['id_variation'],
|
|
]
|
|
)
|
|
->andWhere(
|
|
'oi.id_product=:id_product AND (oi.id_variation IS NULL OR oi.id_variation=:id_variation)'
|
|
)
|
|
->andWhere(Operator::andX($event->getPiecesInOrdersSpec()))
|
|
->andWhere(\Query\Order::notPackedOrders())
|
|
->groupBy('oi.id_product, oi.id_variation')
|
|
->execute()
|
|
->fetchColumn();
|
|
}
|
|
|
|
/**
|
|
* @required
|
|
*/
|
|
public function setEventDispatcher(EventDispatcherInterface $eventDispatcher): void
|
|
{
|
|
$this->eventDispatcher = $eventDispatcher;
|
|
}
|
|
|
|
public function getLocationByPosition($position)
|
|
{
|
|
return sqlQueryBuilder()->select('wp.id_location')
|
|
->from('warehouse_positions', 'wp')
|
|
->where(Operator::equals(['wp.id' => $position]))
|
|
->execute()->fetchOne();
|
|
}
|
|
}
|