custom/plugins/PickwareErpStarter/src/Stock/ProductStockUpdater.php line 65

Open in your IDE?
  1. <?php
  2. /*
  3.  * Copyright (c) Pickware GmbH. All rights reserved.
  4.  * This file is part of software that is released under a proprietary license.
  5.  * You must not copy, modify, distribute, make publicly available, or execute
  6.  * its contents or parts thereof without express permission by the copyright
  7.  * holder, unless otherwise permitted by law.
  8.  */
  9. declare(strict_types=1);
  10. namespace Pickware\PickwareErpStarter\Stock;
  11. use Doctrine\DBAL\Connection;
  12. use Doctrine\DBAL\Exception;
  13. use LogicException;
  14. use Pickware\DalBundle\EntityPreWriteValidationEvent;
  15. use Pickware\DalBundle\EntityPreWriteValidationEventDispatcher;
  16. use Pickware\DalBundle\RetryableTransaction;
  17. use Pickware\DalBundle\Sql\SqlUuid;
  18. use Pickware\PickwareErpStarter\Stock\Model\LocationTypeDefinition;
  19. use Pickware\PickwareErpStarter\Stock\Model\StockMovementDefinition;
  20. use Pickware\PickwareErpStarter\Warehouse\Model\ProductWarehouseConfigurationDefinition;
  21. use Shopware\Core\Content\Product\DataAbstractionLayer\ProductStreamUpdater;
  22. use Shopware\Core\Defaults;
  23. use Shopware\Core\Framework\Context;
  24. use Shopware\Core\Framework\DataAbstractionLayer\EntityWriteResult;
  25. use Shopware\Core\Framework\DataAbstractionLayer\Event\EntityWrittenEvent;
  26. use Shopware\Core\Framework\DataAbstractionLayer\Write\Command\ChangeSetAware;
  27. use Shopware\Core\Framework\Uuid\Uuid;
  28. use Symfony\Component\EventDispatcher\EventDispatcherInterface;
  29. use Symfony\Component\EventDispatcher\EventSubscriberInterface;
  30. class ProductStockUpdater implements EventSubscriberInterface
  31. {
  32.     private Connection $db;
  33.     private EventDispatcherInterface $eventDispatcher;
  34.     private ?WarehouseStockUpdater $warehouseStockUpdater;
  35.     private ?ProductStreamUpdater $productStreamUpdater;
  36.     /**
  37.      * @deprecated next major version: $warehouseStockUpdater argument will be non-optional
  38.      */
  39.     public function __construct(
  40.         Connection $db,
  41.         EventDispatcherInterface $eventDispatcher,
  42.         ?WarehouseStockUpdater $warehouseStockUpdater null,
  43.         ?ProductStreamUpdater $productStreamUpdater null
  44.     ) {
  45.         $this->db $db;
  46.         $this->eventDispatcher $eventDispatcher;
  47.         $this->warehouseStockUpdater $warehouseStockUpdater;
  48.         $this->productStreamUpdater $productStreamUpdater;
  49.     }
  50.     public static function getSubscribedEvents(): array
  51.     {
  52.         return [
  53.             EntityPreWriteValidationEventDispatcher::getEventName(ProductWarehouseConfigurationDefinition::ENTITY_NAME) => 'preWriteValidation',
  54.             StockMovementDefinition::ENTITY_WRITTEN_EVENT => 'stockMovementWritten',
  55.             ProductWarehouseConfigurationDefinition::ENTITY_WRITTEN_EVENT => 'productWarehouseConfigurationWritten',
  56.         ];
  57.     }
  58.     public function preWriteValidation($event): void
  59.     {
  60.         if (!($event instanceof EntityPreWriteValidationEvent)) {
  61.             // The subscriber is probably instantiated in its old version (with the Shopware PreWriteValidationEvent) in
  62.             // the container and will be updated on the next container rebuild (next request). Early return.
  63.             return;
  64.         }
  65.         foreach ($event->getCommands() as $command) {
  66.             if ($command instanceof ChangeSetAware) {
  67.                 $command->requestChangeSet();
  68.             }
  69.         }
  70.     }
  71.     public function stockMovementWritten(EntityWrittenEvent $entityWrittenEvent): void
  72.     {
  73.         if ($entityWrittenEvent->getContext()->getVersionId() !== Defaults::LIVE_VERSION) {
  74.             return;
  75.         }
  76.         $stockMovementIds = [];
  77.         foreach ($entityWrittenEvent->getWriteResults() as $writeResult) {
  78.             if ($writeResult->getExistence()->exists()) {
  79.                 // Updating stock movements is not supported yet
  80.                 // In case a stock location is deleted, this code path is also reached. This is because an
  81.                 // EntityWrittenEvent is triggered when an entity field gets null-ed because of a SET NULL constraint
  82.                 // of a FK.
  83.                 continue;
  84.             }
  85.             $payload $writeResult->getPayload();
  86.             $stockMovementIds[] = $payload['id'];
  87.         }
  88.         $this->recalculateStockFromStockMovements($stockMovementIds$entityWrittenEvent->getContext());
  89.     }
  90.     /**
  91.      * This is the indexer scenario. Updates product stocks for the given products.
  92.      *
  93.      * DEPENDS ON no other calculation beforehand.
  94.      */
  95.     public function recalculateStockFromStockMovementsForProducts(array $productIdsContext $context): void
  96.     {
  97.         if (!$this->warehouseStockUpdater) {
  98.             throw new LogicException(sprintf(
  99.                 'The method "%s" cannot be called when the WarehouseStockUpdater is not initialized.',
  100.                 __METHOD__,
  101.             ));
  102.         }
  103.         RetryableTransaction::retryable($this->db, function () use ($productIds): void {
  104.             $this->db->executeStatement(
  105.                 'DELETE FROM `pickware_erp_stock`
  106.                 WHERE `product_id` IN (:productIds)',
  107.                 [
  108.                     'productIds' => array_map('hex2bin'$productIds),
  109.                 ],
  110.                 [
  111.                     'productIds' => Connection::PARAM_STR_ARRAY,
  112.                 ],
  113.             );
  114.             // Some version fields are NULL, some version fields are in the live version. That's why we cannot group by
  115.             // these version fields (NULL and live version values should be grouped together).
  116.             $this->db->executeStatement(
  117.                 'INSERT INTO
  118.                     `pickware_erp_stock`
  119.                 SELECT
  120.                     ' SqlUuid::UUID_V4_GENERATION ' AS id,
  121.                     SUM(`quantity`) AS quantity,
  122.                     `product_id`,
  123.                     :liveVersionId,
  124.                     `location_type_technical_name`,
  125.                     `warehouse_id`,
  126.                     `bin_location_id`,
  127.                     `order_id`,
  128.                     :liveVersionId,
  129.                     `supplier_order_id`,
  130.                     `stock_container_id`,
  131.                     `goods_receipt_id`,
  132.                     `return_order_id`,
  133.                     :liveVersionId,
  134.                     `special_stock_location_technical_name`,
  135.                     NOW(3),
  136.                     null
  137.                 FROM (
  138.                     SELECT
  139.                         `product_id`,
  140.                         `product_version_id`,
  141.                         -1 * `quantity` AS quantity,
  142.                         `source_location_type_technical_name` AS location_type_technical_name,
  143.                         `source_special_stock_location_technical_name` AS special_stock_location_technical_name,
  144.                         `source_warehouse_id` AS warehouse_id,
  145.                         `source_bin_location_id` AS bin_location_id,
  146.                         `source_order_id` AS order_id,
  147.                         `source_supplier_order_id` AS supplier_order_id,
  148.                         `source_stock_container_id` AS stock_container_id,
  149.                         `source_goods_receipt_id` AS goods_receipt_id,
  150.                         `source_return_order_id` AS return_order_id
  151.                     FROM
  152.                         `pickware_erp_stock_movement`
  153.                     WHERE
  154.                         product_id IN (:productIds)
  155.                         AND product_version_id = :liveVersionId
  156.                     UNION ALL -- It is very important to use UNION ALL because UNION selects only distinct values by default
  157.                     SELECT
  158.                         `product_id`,
  159.                         `product_version_id`,
  160.                         `quantity` AS quantity,
  161.                         `destination_location_type_technical_name` AS location_type_technical_name,
  162.                         `destination_special_stock_location_technical_name` AS special_stock_location_technical_name,
  163.                         `destination_warehouse_id` AS warehouse_id,
  164.                         `destination_bin_location_id` AS bin_location_id,
  165.                         `destination_order_id` AS order_id,
  166.                         `destination_supplier_order_id` AS supplier_order_id,
  167.                         `destination_stock_container_id` AS stock_container_id,
  168.                         `destination_goods_receipt_id` AS goods_receipt_id,
  169.                         `destination_return_order_id` AS return_order_id
  170.                     FROM
  171.                         `pickware_erp_stock_movement`
  172.                     WHERE
  173.                         `product_id` IN (:productIds)
  174.                         AND `product_version_id` = :liveVersionId
  175.                 ) AS someAlias
  176.                 GROUP BY
  177.                     `product_id`,
  178.                     `product_version_id`,
  179.                     `location_type_technical_name`,
  180.                     `warehouse_id`,
  181.                     `bin_location_id`,
  182.                     `order_id`,
  183.                     `supplier_order_id`,
  184.                     `stock_container_id`,
  185.                     `goods_receipt_id`,
  186.                     `return_order_id`,
  187.                     `special_stock_location_technical_name`
  188.                 HAVING
  189.                     `quantity` != 0;',
  190.                 [
  191.                     'liveVersionId' => hex2bin(Defaults::LIVE_VERSION),
  192.                     'productIds' => array_map('hex2bin'$productIds),
  193.                 ],
  194.                 [
  195.                     'productIds' => Connection::PARAM_STR_ARRAY,
  196.                 ],
  197.             );
  198.         });
  199.         $this->recalculateProductStock($productIds);
  200.     }
  201.     public function recalculateStockFromStockMovements(array $stockMovementIdsContext $context null): void
  202.     {
  203.         $stockMovementIds array_values(array_unique($stockMovementIds));
  204.         $stockMovements $this->db->fetchAllAssociative(
  205.             'SELECT
  206.                 LOWER(HEX(product_id)) AS productId,
  207.                 LOWER(HEX(product_version_id)) AS productVersionId,
  208.                 source_location_type_technical_name AS sourceLocationTypeTechnicalName,
  209.                 LOWER(HEX(source_warehouse_id)) AS sourceWarehouseId,
  210.                 LOWER(HEX(source_bin_location_id)) AS sourceBinLocationId,
  211.                 LOWER(HEX(source_order_id)) AS sourceOrderId,
  212.                 LOWER(HEX(source_order_version_id)) AS sourceOrderVersionId,
  213.                 LOWER(HEX(source_supplier_order_id)) AS sourceSupplierOrderId,
  214.                 LOWER(HEX(source_stock_container_id)) AS sourceStockContainerId,
  215.                 LOWER(HEX(source_return_order_id)) AS sourceReturnOrderId,
  216.                 LOWER(HEX(source_return_order_version_id)) AS sourceReturnOrderVersionId,
  217.                 LOWER(HEX(source_goods_receipt_id)) AS sourceGoodsReceiptId,
  218.                 source_special_stock_location_technical_name AS sourceSpecialStockLocationTechnicalName,
  219.                 destination_location_type_technical_name AS destinationLocationTypeTechnicalName,
  220.                 LOWER(HEX(destination_warehouse_id)) AS destinationWarehouseId,
  221.                 LOWER(HEX(destination_bin_location_id)) AS destinationBinLocationId,
  222.                 LOWER(HEX(destination_order_id)) AS destinationOrderId,
  223.                 LOWER(HEX(destination_order_version_id)) AS destinationOrderVersionId,
  224.                 LOWER(HEX(destination_supplier_order_id)) AS destinationSupplierOrderId,
  225.                 LOWER(HEX(destination_stock_container_id)) AS destinationStockContainerId,
  226.                 LOWER(HEX(destination_return_order_id)) AS destinationReturnOrderId,
  227.                 LOWER(HEX(destination_return_order_version_id)) AS destinationReturnOrderVersionId,
  228.                 LOWER(HEX(destination_goods_receipt_id)) AS destinationGoodsReceiptId,
  229.                 destination_special_stock_location_technical_name AS destinationSpecialStockLocationTechnicalName,
  230.                 SUM(quantity) AS quantity
  231.             FROM pickware_erp_stock_movement
  232.             WHERE id IN (:stockMovementIds) AND product_version_id = :liveVersionId
  233.             GROUP BY
  234.                 `product_id`,
  235.                 `source_location_type_technical_name`,
  236.                 `source_warehouse_id`,
  237.                 `source_bin_location_id`,
  238.                 `source_order_id`,
  239.                 `source_order_version_id`,
  240.                 `source_supplier_order_id`,
  241.                 `source_stock_container_id`,
  242.                 `source_return_order_id`,
  243.                 `source_return_order_version_id`,
  244.                 `source_goods_receipt_id`,
  245.                 `source_special_stock_location_technical_name`,
  246.                 `destination_location_type_technical_name`,
  247.                 `destination_warehouse_id`,
  248.                 `destination_bin_location_id`,
  249.                 `destination_order_id`,
  250.                 `destination_order_version_id`,
  251.                 `destination_supplier_order_id`,
  252.                 `destination_stock_container_id`,
  253.                 `destination_return_order_id`,
  254.                 `destination_return_order_version_id`,
  255.                 `destination_goods_receipt_id`,
  256.                 `destination_special_stock_location_technical_name`',
  257.             [
  258.                 'stockMovementIds' => array_map('hex2bin'$stockMovementIds),
  259.                 'liveVersionId' => hex2bin(Defaults::LIVE_VERSION),
  260.             ],
  261.             ['stockMovementIds' => Connection::PARAM_STR_ARRAY],
  262.         );
  263.         RetryableTransaction::retryable($this->db, function () use ($stockMovements$context): void {
  264.             $productIds = [];
  265.             foreach ($stockMovements as $stockMovement) {
  266.                 $this->persistStockChange(
  267.                     [
  268.                         'productId' => $stockMovement['productId'],
  269.                         'productVersionId' => $stockMovement['productVersionId'],
  270.                         'locationTypeTechnicalName' => $stockMovement['sourceLocationTypeTechnicalName'],
  271.                         'warehouseId' => $stockMovement['sourceWarehouseId'] ?? null,
  272.                         'binLocationId' => $stockMovement['sourceBinLocationId'] ?? null,
  273.                         'orderId' => $stockMovement['sourceOrderId'] ?? null,
  274.                         'orderVersionId' => $stockMovement['sourceOrderVersionId'] ?? null,
  275.                         'supplierOrderId' => $stockMovement['sourceSupplierOrderId'] ?? null,
  276.                         'stockContainerId' => $stockMovement['sourceStockContainerId'] ?? null,
  277.                         'returnOrderId' => $stockMovement['sourceReturnOrderId'] ?? null,
  278.                         'returnOrderVersionId' => $stockMovement['sourceReturnOrderVersionId'] ?? null,
  279.                         'goodsReceiptId' => $stockMovement['sourceGoodsReceiptId'] ?? null,
  280.                         'specialStockLocationTechnicalName' => $stockMovement['sourceSpecialStockLocationTechnicalName'] ?? null,
  281.                         'changeAmount' => -$stockMovement['quantity'],
  282.                     ],
  283.                 );
  284.                 $this->persistStockChange(
  285.                     [
  286.                         'productId' => $stockMovement['productId'],
  287.                         'productVersionId' => $stockMovement['productVersionId'],
  288.                         'locationTypeTechnicalName' => $stockMovement['destinationLocationTypeTechnicalName'],
  289.                         'warehouseId' => $stockMovement['destinationWarehouseId'] ?? null,
  290.                         'binLocationId' => $stockMovement['destinationBinLocationId'] ?? null,
  291.                         'orderId' => $stockMovement['destinationOrderId'] ?? null,
  292.                         'orderVersionId' => $stockMovement['destinationOrderVersionId'] ?? null,
  293.                         'supplierOrderId' => $stockMovement['destinationSupplierOrderId'] ?? null,
  294.                         'stockContainerId' => $stockMovement['destinationStockContainerId'] ?? null,
  295.                         'returnOrderId' => $stockMovement['destinationReturnOrderId'] ?? null,
  296.                         'returnOrderVersionId' => $stockMovement['destinationReturnOrderVersionId'] ?? null,
  297.                         'goodsReceiptId' => $stockMovement['destinationGoodsReceiptId'] ?? null,
  298.                         'specialStockLocationTechnicalName' => $stockMovement['destinationSpecialStockLocationTechnicalName'] ?? null,
  299.                         'changeAmount' => $stockMovement['quantity'],
  300.                     ],
  301.                 );
  302.                 $productIds[] = $stockMovement['productId'];
  303.             }
  304.             $this->cleanUpStocks($productIds);
  305.             $this->recalculateProductStock($productIds);
  306.             if ($this->productStreamUpdater) {
  307.                 // Product streams can use the stock as a filter. Because of this we need to update the product stream
  308.                 // mappings via the productStreamUpdater to make sure dynamic product groups are updated.
  309.                 // For further reference see https://github.com/pickware/shopware-plugins/issues/3232
  310.                 $this->productStreamUpdater->updateProducts($productIds$context);
  311.             }
  312.         });
  313.         $this->eventDispatcher->dispatch(new StockUpdatedForStockMovementsEvent($stockMovements));
  314.     }
  315.     public function productWarehouseConfigurationWritten(EntityWrittenEvent $entityWrittenEvent): void
  316.     {
  317.         if ($entityWrittenEvent->getContext()->getVersionId() !== Defaults::LIVE_VERSION) {
  318.             return;
  319.         }
  320.         $writeResults $entityWrittenEvent->getWriteResults();
  321.         $oldProductBinLocations = [];
  322.         $newProductBinLocations = [];
  323.         foreach ($writeResults as $writeResult) {
  324.             $changeSet $writeResult->getChangeSet();
  325.             $payload $writeResult->getPayload();
  326.             if ($changeSet && $changeSet->hasChanged('default_bin_location_id')) {
  327.                 $productId $changeSet->getBefore('product_id');
  328.                 $oldDefaultBinLocationId $changeSet->getBefore('default_bin_location_id');
  329.                 if ($oldDefaultBinLocationId) {
  330.                     $oldProductBinLocations[] = new ProductBinLocation(bin2hex($productId), bin2hex($oldDefaultBinLocationId));
  331.                 }
  332.                 $newDefaultBinLocationId $changeSet->getAfter('default_bin_location_id');
  333.                 if ($newDefaultBinLocationId) {
  334.                     $newProductBinLocations[] = new ProductBinLocation(bin2hex($productId), bin2hex($newDefaultBinLocationId));
  335.                 }
  336.             } elseif ($writeResult->getOperation() === EntityWriteResult::OPERATION_INSERT) {
  337.                 $defaultBinLocationId $payload['defaultBinLocationId'] ?? null;
  338.                 if ($defaultBinLocationId) {
  339.                     $newProductBinLocations[] = new ProductBinLocation($payload['productId'], $defaultBinLocationId);
  340.                 }
  341.             }
  342.         }
  343.         $this->deleteStockEntriesForOldDefaultBinLocations($oldProductBinLocations);
  344.         $this->upsertStockEntriesForDefaultBinLocations($newProductBinLocations);
  345.     }
  346.     public function upsertStockEntriesForDefaultBinLocationsOfProducts(array $productIds): void
  347.     {
  348.         $configurations $this->db->fetchAllAssociative(
  349.             'SELECT
  350.                 LOWER(HEX(product_id)) AS productId,
  351.                 LOWER(HEX(default_bin_location_id)) AS binLocationId
  352.             FROM pickware_erp_product_warehouse_configuration
  353.             WHERE product_id IN (:productIds)
  354.                 AND product_version_id = :liveVersionId
  355.                 AND default_bin_location_id IS NOT NULL',
  356.             [
  357.                 'productIds' => array_map('hex2bin'$productIds),
  358.                 'liveVersionId' => hex2bin(Defaults::LIVE_VERSION),
  359.             ],
  360.             [
  361.                 'productIds' => Connection::PARAM_STR_ARRAY,
  362.             ],
  363.         );
  364.         $productBinLocations array_map(static fn (array $configuration) => new ProductBinLocation($configuration['productId'], $configuration['binLocationId']), $configurations);
  365.         $this->upsertStockEntriesForDefaultBinLocations($productBinLocations);
  366.     }
  367.     /**
  368.      * @param ProductBinLocation[] $productBinLocations
  369.      * @throws Exception
  370.      */
  371.     private function upsertStockEntriesForDefaultBinLocations(array $productBinLocations): void
  372.     {
  373.         if (count($productBinLocations) > 0) {
  374.             $tuples implode(', 'array_map(static function (ProductBinLocation $productBinLocation) {
  375.                 return sprintf(
  376.                     '(UNHEX(\'%s\'), UNHEX(\'%s\'), UNHEX(\'%s\'), "%s", UNHEX(\'%s\'), 0, NOW())',
  377.                     Uuid::randomHex(),
  378.                     $productBinLocation->getProductId(),
  379.                     Defaults::LIVE_VERSION,
  380.                     LocationTypeDefinition::TECHNICAL_NAME_BIN_LOCATION,
  381.                     $productBinLocation->getBinLocationId(),
  382.                 );
  383.             }, $productBinLocations));
  384.             $query sprintf(
  385.                 'INSERT IGNORE INTO `pickware_erp_stock`
  386.                 (
  387.                     `id`,
  388.                     `product_id`,
  389.                     `product_version_id`,
  390.                     `location_type_technical_name`,
  391.                     `bin_location_id`,
  392.                     `quantity`,
  393.                     `created_at`
  394.                 ) VALUES %s',
  395.                 $tuples,
  396.             );
  397.             $this->db->executeStatement($query);
  398.         }
  399.     }
  400.     /**
  401.      * Deletes stock entries for the given default bin location and products if it has no stock.
  402.      *
  403.      * @param ProductBinLocation[] $productBinLocations
  404.      * @throws Exception
  405.      */
  406.     private function deleteStockEntriesForOldDefaultBinLocations(array $productBinLocations): void
  407.     {
  408.         if (count($productBinLocations) > 0) {
  409.             $tuples implode(', 'array_map(static function (ProductBinLocation $productBinLocation) {
  410.                 return sprintf(
  411.                     '(UNHEX(\'%s\'), UNHEX(\'%s\'))',
  412.                     $productBinLocation->getProductId(),
  413.                     $productBinLocation->getBinLocationId(),
  414.                 );
  415.             }, $productBinLocations));
  416.             $query sprintf(
  417.                 'DELETE `pickware_erp_stock` FROM `pickware_erp_stock`
  418.                 WHERE `pickware_erp_stock`.`quantity` = 0
  419.                 AND `pickware_erp_stock`.`product_version_id` = :liveVersionId
  420.                 AND (`pickware_erp_stock`.`product_id`, `pickware_erp_stock`.`bin_location_id`) IN (%s)',
  421.                 $tuples,
  422.             );
  423.             $this->db->executeStatement(
  424.                 $query,
  425.                 ['liveVersionId' => hex2bin(Defaults::LIVE_VERSION)],
  426.             );
  427.         }
  428.     }
  429.     private function persistStockChange(array $payload): void
  430.     {
  431.         $this->db->executeStatement(
  432.             'INSERT INTO pickware_erp_stock (
  433.                 id,
  434.                 product_id,
  435.                 product_version_id,
  436.                 quantity,
  437.                 location_type_technical_name,
  438.                 warehouse_id,
  439.                 bin_location_id,
  440.                 order_id,
  441.                 order_version_id,
  442.                 supplier_order_id,
  443.                 stock_container_id,
  444.                 return_order_id,
  445.                 return_order_version_id,
  446.                 goods_receipt_id,
  447.                 special_stock_location_technical_name,
  448.                 created_at
  449.             ) VALUES (
  450.                 :id,
  451.                 :productId,
  452.                 :productVersionId,
  453.                 :changeAmount,
  454.                 :locationTypeTechnicalName,
  455.                 :warehouseId,
  456.                 :binLocationId,
  457.                 :orderId,
  458.                 :orderVersionId,
  459.                 :supplierOrderId,
  460.                 :stockContainerId,
  461.                 :returnOrderId,
  462.                 :returnOrderVersionId,
  463.                 :goodsReceiptId,
  464.                 :specialStockLocationTechnicalName,
  465.                 NOW(3)
  466.             ) ON DUPLICATE KEY UPDATE
  467.                 quantity = quantity + VALUES(quantity),
  468.                 updated_at = NOW(3)',
  469.             [
  470.                 'id' => Uuid::randomBytes(),
  471.                 'locationTypeTechnicalName' => $payload['locationTypeTechnicalName'],
  472.                 'productId' => hex2bin($payload['productId']),
  473.                 'productVersionId' => hex2bin($payload['productVersionId']),
  474.                 'warehouseId' => $payload['warehouseId'] ? hex2bin($payload['warehouseId']) : null,
  475.                 'binLocationId' => $payload['binLocationId'] ? hex2bin($payload['binLocationId']) : null,
  476.                 'orderId' => $payload['orderId'] ? hex2bin($payload['orderId']) : null,
  477.                 'orderVersionId' => $payload['orderVersionId'] ? hex2bin($payload['orderVersionId']) : null,
  478.                 'supplierOrderId' => $payload['supplierOrderId'] ? hex2bin($payload['supplierOrderId']) : null,
  479.                 'stockContainerId' => $payload['stockContainerId'] ? hex2bin($payload['stockContainerId']) : null,
  480.                 'returnOrderId' => $payload['returnOrderId'] ? hex2bin($payload['returnOrderId']) : null,
  481.                 'returnOrderVersionId' => $payload['returnOrderVersionId'] ? hex2bin($payload['returnOrderVersionId']) : null,
  482.                 'goodsReceiptId' => $payload['goodsReceiptId'] ? hex2bin($payload['goodsReceiptId']) : null,
  483.                 'specialStockLocationTechnicalName' => $payload['specialStockLocationTechnicalName'],
  484.                 'changeAmount' => $payload['changeAmount'],
  485.             ],
  486.         );
  487.     }
  488.     /**
  489.      * Clears (deletes) stock values that are irrelevant. These are stocks that
  490.      *   - have quantity 0 or
  491.      *   - are in any non-special stock location that was deleted
  492.      */
  493.     private function cleanUpStocks(array $productIds): void
  494.     {
  495.         $this->db->executeStatement(
  496.             'DELETE `stock`
  497.             FROM `pickware_erp_stock` AS `stock`
  498.             LEFT JOIN `pickware_erp_product_warehouse_configuration` AS `product_warehouse_configuration`
  499.                 ON `stock`.`product_id` = `product_warehouse_configuration`.product_id
  500.                     AND `stock`.`bin_location_id` = `product_warehouse_configuration`.`default_bin_location_id`
  501.             WHERE
  502.                 (
  503.                     `stock`.`quantity` = 0 OR
  504.                     (`stock`.`location_type_technical_name` = "warehouse" AND `stock`.`warehouse_id` IS NULL) OR
  505.                     (`stock`.`location_type_technical_name` = "bin_location" AND `stock`.`bin_location_id` IS NULL) OR
  506.                     (`stock`.`location_type_technical_name` = "order" AND `stock`.`order_id` IS NULL) OR
  507.                     (`stock`.`location_type_technical_name` = "supplier_order" AND `stock`.`supplier_order_id` IS NULL) OR
  508.                     (`stock`.`location_type_technical_name` = "stock_container" AND `stock`.`stock_container_id` IS NULL) OR
  509.                     (`stock`.`location_type_technical_name` = "return_order" AND `stock`.`return_order_id` IS NULL) OR
  510.                     (`stock`.`location_type_technical_name` = "goods_receipt" AND `stock`.`goods_receipt_id` IS NULL)
  511.                 )
  512.             AND `stock`.`product_version_id` = :liveVersionId
  513.             AND `stock`.`product_id` IN (:productIds)
  514.             AND `product_warehouse_configuration`.`default_bin_location_id` IS NULL
  515.             ',
  516.             [
  517.                 'liveVersionId' => hex2bin(Defaults::LIVE_VERSION),
  518.                 'productIds' => array_map('hex2bin'$productIds),
  519.             ],
  520.             [
  521.                 'productIds' => Connection::PARAM_STR_ARRAY,
  522.             ],
  523.         );
  524.     }
  525.     private function recalculateProductStock(array $productIds): void
  526.     {
  527.         $query '
  528.             UPDATE `product`
  529.             LEFT JOIN (
  530.                 SELECT
  531.                     `stock`.`product_id` as `product_id`,
  532.                     `stock`.`product_version_id` as `product_version_id`,
  533.                     SUM(`stock`.`quantity`) AS `quantity`
  534.                 FROM `pickware_erp_stock` `stock`
  535.                 LEFT JOIN `pickware_erp_location_type` AS `location_type`
  536.                     ON `stock`.`location_type_technical_name` = `location_type`.`technical_name`
  537.                 WHERE `location_type`.`internal` = 1
  538.                 AND `stock`.`product_id` IN (:productIds) AND `stock`.`product_version_id` = :liveVersionId
  539.                 GROUP BY
  540.                     `stock`.`product_id`,
  541.                     `stock`.`product_version_id`
  542.             ) AS `totalStocks`
  543.                 ON
  544.                     `totalStocks`.`product_id` = `product`.`id`
  545.                     AND `totalStocks`.`product_version_id` = `product`.`version_id`
  546.             SET `product`.`stock` = IFNULL(`totalStocks`.`quantity`, 0)
  547.             WHERE `product`.`version_id` = :liveVersionId
  548.             AND `product`.`id` IN (:productIds)';
  549.         $params = [
  550.             'liveVersionId' => hex2bin(Defaults::LIVE_VERSION),
  551.             'productIds' => array_map('hex2bin'$productIds),
  552.         ];
  553.         $paramTypes = [
  554.             'productIds' => Connection::PARAM_STR_ARRAY,
  555.         ];
  556.         $this->db->executeStatement($query$params$paramTypes);
  557.     }
  558. }