custom/plugins/PickwareErpStarter/src/Stock/StockNotAvailableForSaleUpdater.php line 93

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 Pickware\DalBundle\EntityPreWriteValidationEvent;
  13. use Pickware\DalBundle\EntityPreWriteValidationEventDispatcher;
  14. use Pickware\PickwareErpStarter\Product\PickwareProductInitializer;
  15. use Pickware\PickwareErpStarter\Stock\Model\StockMovementDefinition;
  16. use Pickware\PickwareErpStarter\Warehouse\Model\WarehouseDefinition;
  17. use Shopware\Core\Defaults;
  18. use Shopware\Core\Framework\DataAbstractionLayer\EntityWriteResult;
  19. use Shopware\Core\Framework\DataAbstractionLayer\Event\EntityWrittenEvent;
  20. use Shopware\Core\Framework\DataAbstractionLayer\Write\Command\UpdateCommand;
  21. use Symfony\Component\EventDispatcher\EventDispatcherInterface;
  22. use Symfony\Component\EventDispatcher\EventSubscriberInterface;
  23. class StockNotAvailableForSaleUpdater implements EventSubscriberInterface
  24. {
  25.     private Connection $db;
  26.     private EventDispatcherInterface $eventDispatcher;
  27.     private PickwareProductInitializer $pickwareProductInitializer;
  28.     public function __construct(
  29.         Connection $db,
  30.         EventDispatcherInterface $eventDispatcher null,
  31.         PickwareProductInitializer $pickwareProductInitializer null
  32.     ) {
  33.         $this->db $db;
  34.         $this->eventDispatcher $eventDispatcher;
  35.         $this->pickwareProductInitializer $pickwareProductInitializer;
  36.     }
  37.     public static function getSubscribedEvents(): array
  38.     {
  39.         return [
  40.             StockMovementDefinition::ENTITY_WRITTEN_EVENT => 'stockMovementWritten',
  41.             WarehouseDefinition::ENTITY_WRITTEN_EVENT => 'warehouseWritten',
  42.             EntityPreWriteValidationEventDispatcher::getEventName(WarehouseDefinition::ENTITY_NAME) => 'triggerChangeSetForWarehouseChanges',
  43.         ];
  44.     }
  45.     public function stockMovementWritten(EntityWrittenEvent $entityWrittenEvent): void
  46.     {
  47.         if ($entityWrittenEvent->getContext()->getVersionId() !== Defaults::LIVE_VERSION) {
  48.             return;
  49.         }
  50.         $stockMovementIds = [];
  51.         foreach ($entityWrittenEvent->getWriteResults() as $writeResult) {
  52.             if ($writeResult->getExistence()->exists()) {
  53.                 // Updating stock movements is not supported yet
  54.                 // In case a stock location is deleted, this code path is also reached. This is because an
  55.                 // EntityWrittenEvent is triggered when an entity field gets null-ed because of a SET NULL constraint
  56.                 // of a FK.
  57.                 continue;
  58.             }
  59.             $payload $writeResult->getPayload();
  60.             $stockMovementIds[] = $payload['id'];
  61.         }
  62.         $this->updateProductStockNotAvailableForSaleByStockMovements($stockMovementIds);
  63.     }
  64.     public function triggerChangeSetForWarehouseChanges($event): void
  65.     {
  66.         if (!($event instanceof EntityPreWriteValidationEvent)) {
  67.             // The subscriber is probably instantiated in its old version (with the Shopware PreWriteValidationEvent) in
  68.             // the container and will be updated on the next container rebuild (next request). Early return.
  69.             return;
  70.         }
  71.         foreach ($event->getCommands() as $command) {
  72.             if (!($command instanceof UpdateCommand)
  73.                 || $command->getDefinition()->getEntityName() !== WarehouseDefinition::ENTITY_NAME) {
  74.                 continue;
  75.             }
  76.             if ($command->hasField('is_stock_available_for_sale')) {
  77.                 $command->requestChangeSet();
  78.             }
  79.         }
  80.     }
  81.     public function warehouseWritten(EntityWrittenEvent $entityWrittenEvent): void
  82.     {
  83.         if ($entityWrittenEvent->getContext()->getVersionId() !== Defaults::LIVE_VERSION) {
  84.             return;
  85.         }
  86.         $warehouseIdsToDecreaseStockNotAvailableForSale = [];
  87.         $warehouseIdsToIncreaseStockNotAvailableForSale = [];
  88.         foreach ($entityWrittenEvent->getWriteResults() as $writeResult) {
  89.             $payload $writeResult->getPayload();
  90.             // Should be not null when 'isStockAvailableForSale' has changed as we requested a change set in
  91.             // triggerChangeSetForWarehouseChanges.
  92.             $changeSet $writeResult->getChangeSet();
  93.             if (($writeResult->getOperation() !== EntityWriteResult::OPERATION_UPDATE)
  94.                 || !array_key_exists('isStockAvailableForSale'$payload)
  95.                 || !$changeSet
  96.                 || !$changeSet->hasChanged('is_stock_available_for_sale')) {
  97.                 continue;
  98.             }
  99.             if ($payload['isStockAvailableForSale']) {
  100.                 // Warehouse stock is now available for sale, decrease stockNotAvailableForSale
  101.                 $warehouseIdsToDecreaseStockNotAvailableForSale[] = $writeResult->getPrimaryKey();
  102.             } else {
  103.                 // Warehouse stock is no longer available for sale, increase stockNotAvailableForSale
  104.                 $warehouseIdsToIncreaseStockNotAvailableForSale[] = $writeResult->getPrimaryKey();
  105.             }
  106.         }
  107.         if (count($warehouseIdsToDecreaseStockNotAvailableForSale) > 0) {
  108.             $this->updateProductStockNotAvailableForSaleByWarehouseStock(-1$warehouseIdsToDecreaseStockNotAvailableForSale);
  109.             $this->eventDispatcher->dispatch(new StockNotAvailableForSaleUpdatedForAllProductsInWarehousesEvent(
  110.                 $warehouseIdsToDecreaseStockNotAvailableForSale,
  111.                 false,
  112.             ));
  113.         }
  114.         if (count($warehouseIdsToIncreaseStockNotAvailableForSale) > 0) {
  115.             $this->updateProductStockNotAvailableForSaleByWarehouseStock(1$warehouseIdsToIncreaseStockNotAvailableForSale);
  116.             $this->eventDispatcher->dispatch(new StockNotAvailableForSaleUpdatedForAllProductsInWarehousesEvent(
  117.                 $warehouseIdsToIncreaseStockNotAvailableForSale,
  118.                 true,
  119.             ));
  120.         }
  121.     }
  122.     /**
  123.      * Updates the not available for sale stocks incrementally by iterating the given stock movements.
  124.      *
  125.      * DEPENDS ON stock_not_available_for_sale being correctly calculated for all other stock movements for the same
  126.      * products besides the given ones.
  127.      */
  128.     public function updateProductStockNotAvailableForSaleByStockMovements(array $stockMovementIds): void
  129.     {
  130.         $stockMovementIds array_values(array_unique($stockMovementIds));
  131.         $stockMovements $this->db->fetchAllAssociative(
  132.             'SELECT
  133.                 LOWER(HEX(product_id)) AS productId,
  134.                 quantity,
  135.                 COALESCE(
  136.                     sourceWarehouse.id,
  137.                     sourceBinLocationWarehouse.id
  138.                 ) AS sourceWarehouseId,
  139.                 COALESCE(
  140.                     sourceWarehouse.is_stock_available_for_sale,
  141.                     sourceBinLocationWarehouse.is_stock_available_for_sale
  142.                 ) AS sourceWarehouseIsStockAvailableForSale,
  143.                 COALESCE(
  144.                     destinationWarehouse.id,
  145.                     destinationBinLocationWarehouse.id
  146.                 ) AS destinationWarehouseId,
  147.                 COALESCE(
  148.                     destinationWarehouse.is_stock_available_for_sale,
  149.                     destinationBinLocationWarehouse.is_stock_available_for_sale
  150.                 ) AS destinationWarehouseIsStockAvailableForSale
  151.             FROM pickware_erp_stock_movement stockMovement
  152.             LEFT JOIN pickware_erp_warehouse sourceWarehouse ON sourceWarehouse.id = stockMovement.source_warehouse_id
  153.             LEFT JOIN pickware_erp_bin_location sourceBinLocation ON sourceBinLocation.id = stockMovement.source_bin_location_id
  154.                 LEFT JOIN pickware_erp_warehouse sourceBinLocationWarehouse ON sourceBinLocationWarehouse.id = sourceBinLocation.warehouse_id
  155.             LEFT JOIN pickware_erp_warehouse destinationWarehouse ON destinationWarehouse.id = stockMovement.destination_warehouse_id
  156.             LEFT JOIN pickware_erp_bin_location destinationBinLocation ON destinationBinLocation.id = stockMovement.destination_bin_location_id
  157.                 LEFT JOIN pickware_erp_warehouse destinationBinLocationWarehouse ON destinationBinLocationWarehouse.id = destinationBinLocation.warehouse_id
  158.             WHERE stockMovement.id IN (:stockMovementIds)
  159.               AND product_version_id = :liveVersionId
  160.               AND (
  161.                   # Note that "<>" comparator does not work with NULL values. Hence, the verbose check.
  162.                   COALESCE(sourceWarehouse.id,sourceBinLocationWarehouse.id) IS NULL && COALESCE(destinationWarehouse.id, destinationBinLocationWarehouse.id) IS NOT NULL ||
  163.                   COALESCE(sourceWarehouse.id,sourceBinLocationWarehouse.id) IS NOT NULL && COALESCE(destinationWarehouse.id, destinationBinLocationWarehouse.id) IS NULL ||
  164.                   COALESCE(sourceWarehouse.id,sourceBinLocationWarehouse.id) <> COALESCE(destinationWarehouse.id, destinationBinLocationWarehouse.id)
  165.               )',
  166.             [
  167.                 'stockMovementIds' => array_map('hex2bin'$stockMovementIds),
  168.                 'liveVersionId' => hex2bin(Defaults::LIVE_VERSION),
  169.             ],
  170.             [
  171.                 'stockMovementIds' => Connection::PARAM_STR_ARRAY,
  172.             ],
  173.         );
  174.         $stockNotAvailableForSaleChanges = [];
  175.         foreach ($stockMovements as $stockMovement) {
  176.             $sourceIsWarehouse = (bool) $stockMovement['sourceWarehouseId'];
  177.             $sourceWarehouseIsStockAvailableForSale = (bool) $stockMovement['sourceWarehouseIsStockAvailableForSale'];
  178.             $destinationIsWarehouse = (bool) $stockMovement['destinationWarehouseId'];
  179.             $destinationWarehouseIsStockAvailableForSale = (bool) $stockMovement['destinationWarehouseIsStockAvailableForSale'];
  180.             if ($sourceIsWarehouse && !$sourceWarehouseIsStockAvailableForSale && ($destinationWarehouseIsStockAvailableForSale || !$destinationIsWarehouse)) {
  181.                 $stockNotAvailableForSaleChanges[] = [
  182.                     'productId' => $stockMovement['productId'],
  183.                     'change' => -* (int) $stockMovement['quantity'],
  184.                 ];
  185.             }
  186.             if ($destinationIsWarehouse && !$destinationWarehouseIsStockAvailableForSale && ($sourceWarehouseIsStockAvailableForSale || !$sourceIsWarehouse)) {
  187.                 $stockNotAvailableForSaleChanges[] = [
  188.                     'productId' => $stockMovement['productId'],
  189.                     'change' => (int) $stockMovement['quantity'],
  190.                 ];
  191.             }
  192.         }
  193.         if (count($stockNotAvailableForSaleChanges) > 0) {
  194.             $productIds array_values(array_unique(array_column($stockNotAvailableForSaleChanges'productId')));
  195.             $this->pickwareProductInitializer->ensurePickwareProductsExist($productIds);
  196.             foreach ($stockNotAvailableForSaleChanges as $stockAvailableForSaleChange) {
  197.                 $this->persistStockNotAvailableForSaleChange(
  198.                     $stockAvailableForSaleChange['productId'],
  199.                     $stockAvailableForSaleChange['change'],
  200.                 );
  201.             }
  202.             $this->eventDispatcher->dispatch(new StockNotAvailableForSaleUpdatedEvent($productIds));
  203.         }
  204.     }
  205.     private function persistStockNotAvailableForSaleChange(string $productIdint $change): void
  206.     {
  207.         $this->db->executeStatement(
  208.             'UPDATE `pickware_erp_pickware_product`
  209.             SET `pickware_erp_pickware_product`.`stock_not_available_for_sale` = `pickware_erp_pickware_product`.`stock_not_available_for_sale` + (:change)
  210.             WHERE `pickware_erp_pickware_product`.`product_id` = :productId
  211.             AND `pickware_erp_pickware_product`.`product_version_id` = :liveVersionId;',
  212.             [
  213.                 'productId' => hex2bin($productId),
  214.                 'change' => $change,
  215.                 'liveVersionId' => hex2bin(Defaults::LIVE_VERSION),
  216.             ],
  217.             [
  218.                 'productIds' => Connection::PARAM_STR_ARRAY,
  219.             ],
  220.         );
  221.     }
  222.     /**
  223.      * When a warehouse "isStockAvailableForSale" flag is changed, we need to update the stock_not_available_for_sale of
  224.      * _ALL AFFECTED PRODUCTS_. Since the warehouse stock does not need to be recalculated, we can simply add/subtract
  225.      * it from the stock_not_available_for_sale for all affected products.
  226.      *
  227.      * @param int $stockNotAvailableForSaleFactor 1 or -1 whether or not the online not available stock should be
  228.      * increased (1) or decreased (-1)
  229.      */
  230.     private function updateProductStockNotAvailableForSaleByWarehouseStock(
  231.         int $stockNotAvailableForSaleFactor,
  232.         array $warehouseIds
  233.     ): void {
  234.         $this->db->executeStatement(
  235.             'UPDATE `pickware_erp_pickware_product` pickwareProduct
  236.             INNER JOIN `pickware_erp_warehouse_stock` warehouseStock
  237.             ON warehouseStock.`product_id` = pickwareProduct.`product_id`
  238.             AND warehouseStock.`product_version_id` = pickwareProduct.`product_version_id`
  239.             SET pickwareProduct.`stock_not_available_for_sale` = pickwareProduct.`stock_not_available_for_sale` + (' $stockNotAvailableForSaleFactor ' * warehouseStock.`quantity`)
  240.             WHERE warehouseStock.`warehouse_id` IN (:warehouseIds)
  241.             AND warehouseStock.`quantity` > 0
  242.             AND pickwareProduct.`product_version_id` = :liveVersionId;',
  243.             [
  244.                 'warehouseIds' => array_map('hex2bin'$warehouseIds),
  245.                 'liveVersionId' => hex2bin(Defaults::LIVE_VERSION),
  246.             ],
  247.             [
  248.                 'warehouseIds' => Connection::PARAM_STR_ARRAY,
  249.             ],
  250.         );
  251.     }
  252.     /**
  253.      * This is the indexer scenario when the stock not available for sale needs to be recalculated from the ground up.
  254.      * This can be done in a single query since the given number of product ids and number of warehouses is manageable.
  255.      *
  256.      * DEPENDS ON pickware_erp_warehouse_stock to have been calculated correctly before for the given products.
  257.      *
  258.      * @param String[] $productIds
  259.      */
  260.     public function calculateStockNotAvailableForSaleForProducts(array $productIds): void
  261.     {
  262.         $this->db->executeStatement(
  263.             'UPDATE `pickware_erp_pickware_product` pickwareProduct
  264.             INNER JOIN (
  265.                 SELECT
  266.                     warehouseStock.`product_id` AS productId,
  267.                     warehouseStock.`product_version_id` AS productVersionId,
  268.                     SUM(
  269.                         IF(
  270.                             `is_stock_available_for_sale` = 1,
  271.                             0,
  272.                             warehouseStock.quantity
  273.                         )
  274.                     ) AS quantity
  275.                 FROM `pickware_erp_warehouse_stock` warehouseStock
  276.                 INNER JOIN `pickware_erp_warehouse` warehouse ON warehouseStock.`warehouse_id` = warehouse.`id`
  277.                 WHERE warehouseStock.`product_id` IN (:productIds)
  278.                 AND warehouseStock.`product_version_id` = :liveVersionId
  279.                 GROUP BY warehouseStock.`product_id`
  280.             ) AS aggregatedStockNotAvailableForSale
  281.             ON pickwareProduct.`product_id` = aggregatedStockNotAvailableForSale.productId
  282.             AND pickwareProduct.`product_version_id` = aggregatedStockNotAvailableForSale.productVersionId
  283.             SET pickwareProduct.`stock_not_available_for_sale` = aggregatedStockNotAvailableForSale.quantity
  284.             WHERE pickwareProduct.`product_id` IN (:productIds)
  285.             AND pickwareProduct.`product_version_id` = :liveVersionId;',
  286.             [
  287.                 'productIds' => array_map('hex2bin'$productIds),
  288.                 'liveVersionId' => hex2bin(Defaults::LIVE_VERSION),
  289.             ],
  290.             [
  291.                 'productIds' => Connection::PARAM_STR_ARRAY,
  292.             ],
  293.         );
  294.         $this->eventDispatcher->dispatch(new StockNotAvailableForSaleUpdatedEvent($productIds));
  295.     }
  296. }