Symfony Profiler

<?php
/*
* Copyright (c) Pickware GmbH. All rights reserved.
* This file is part of software that is released under a proprietary license.
* You must not copy, modify, distribute, make publicly available, or execute
* its contents or parts thereof without express permission by the copyright
* holder, unless otherwise permitted by law.
*/
declare(strict_types=1);
namespace Pickware\PickwareErpStarter\Stock;
use Doctrine\DBAL\Connection;
use Pickware\DalBundle\RetryableTransaction;
use Pickware\PickwareErpStarter\Stock\Model\LocationTypeDefinition;
use Pickware\PickwareErpStarter\Stock\Model\StockMovementDefinition;
use Shopware\Core\Defaults;
use Shopware\Core\Framework\Context;
use Shopware\Core\Framework\DataAbstractionLayer\Event\EntityWrittenEvent;
use Shopware\Core\Framework\Uuid\Uuid;
use Symfony\Component\EventDispatcher\EventDispatcherInterface;
use Symfony\Component\EventDispatcher\EventSubscriberInterface;
class WarehouseStockUpdater implements EventSubscriberInterface
{
private Connection $db;
private EventDispatcherInterface $eventDispatcher;
private WarehouseStockInitializer $warehouseStockInitializer;
/**
* @deprecated next major version: $eventDispatcher argument will be non-optional
*/
public function __construct(
Connection $db,
?EventDispatcherInterface $eventDispatcher,
?WarehouseStockInitializer $warehouseStockInitializer
) {
$this->db = $db;
if ($eventDispatcher) {
$this->eventDispatcher = $eventDispatcher;
}
if ($warehouseStockInitializer) {
$this->warehouseStockInitializer = $warehouseStockInitializer;
}
}
public static function getSubscribedEvents(): array
{
return [StockMovementDefinition::ENTITY_WRITTEN_EVENT => 'stockMovementWritten'];
}
public function stockMovementWritten(EntityWrittenEvent $entityWrittenEvent): void
{
if ($entityWrittenEvent->getContext()->getVersionId() !== Defaults::LIVE_VERSION) {
return;
}
$stockMovementIds = [];
foreach ($entityWrittenEvent->getWriteResults() as $writeResult) {
if ($writeResult->getExistence()->exists()) {
// Updating stock movements is not supported yet
// In case a stock location is deleted, this code path is also reached. This is because an
// EntityWrittenEvent is triggered when an entity field gets null-ed because of a SET NULL constraint
// of a FK.
continue;
}
$payload = $writeResult->getPayload();
$stockMovementIds[] = $payload['id'];
}
$this->indexStockMovements($stockMovementIds, $entityWrittenEvent->getContext());
}
/**
* Updates the warehouse stocks incrementally by iterating the given stock movements.
*
* DEPENDS ON `pickware_erp_warehouse_stock` being correctly calculated for all other stock movements for the same
* products and warehouse stocks besides the given ones.
*/
public function indexStockMovements(array $stockMovementIds, Context $context): void
{
$stockMovementIds = array_values(array_unique($stockMovementIds));
$stockMovements = $this->db->fetchAllAssociative(
'SELECT
LOWER(HEX(product_id)) AS productId,
LOWER(HEX(product_version_id)) AS productVersionId,
quantity,
LOWER(HEX(COALESCE(
source_warehouse_id,
sourceBinLocation.warehouse_id
))) AS sourceWarehouseId,
LOWER(HEX(COALESCE(
destination_warehouse_id,
destinationBinLocation.warehouse_id
))) AS destinationWarehouseId
FROM pickware_erp_stock_movement stockMovement
LEFT JOIN pickware_erp_bin_location sourceBinLocation ON sourceBinLocation.id = stockMovement.source_bin_location_id
LEFT JOIN pickware_erp_bin_location destinationBinLocation ON destinationBinLocation.id = stockMovement.destination_bin_location_id
WHERE stockMovement.id IN (:stockMovementIds) AND product_version_id = :liveVersionId',
[
'stockMovementIds' => array_map('hex2bin', $stockMovementIds),
'liveVersionId' => hex2bin(Defaults::LIVE_VERSION),
],
[
'stockMovementIds' => Connection::PARAM_STR_ARRAY,
],
);
// Update warehouse stocks if stock was moved to or from a warehouse or bin location (in that warehouse) or
// stock container (in that warehouse).
$warehouseIds = [];
$productIds = [];
foreach ($stockMovements as $stockMovement) {
if ($stockMovement['sourceWarehouseId'] === $stockMovement['destinationWarehouseId']) {
// If the source and destination warehouse is identical (e.g. a stock move from one location in the
// warehouse to another location in that warehouse), we do not need to track that warehouse id for the
// warehouse stock change event. Because the stock in that warehouse did not change (stock ∓0).
continue;
}
$productIds = array_unique(array_merge(
$productIds,
[$stockMovement['productId']],
));
if ($stockMovement['sourceWarehouseId']) {
$this->persistWarehouseStockChange([
'productId' => $stockMovement['productId'],
'productVersionId' => $stockMovement['productVersionId'],
'warehouseId' => $stockMovement['sourceWarehouseId'],
'changeAmount' => -1 * $stockMovement['quantity'],
]);
$warehouseIds = array_unique(array_merge(
$warehouseIds,
[$stockMovement['sourceWarehouseId']],
));
}
if ($stockMovement['destinationWarehouseId']) {
$this->persistWarehouseStockChange([
'productId' => $stockMovement['productId'],
'productVersionId' => $stockMovement['productVersionId'],
'warehouseId' => $stockMovement['destinationWarehouseId'],
'changeAmount' => 1 * $stockMovement['quantity'],
]);
$warehouseIds = array_unique(array_merge(
$warehouseIds,
[$stockMovement['destinationWarehouseId']],
));
}
}
if (count($warehouseIds) > 0) {
$this->eventDispatcher->dispatch(
new WarehouseStockUpdatedEvent($warehouseIds, $productIds, $context),
WarehouseStockUpdatedEvent::EVENT_NAME,
);
}
}
private function persistWarehouseStockChange(array $payload): void
{
$this->db->executeStatement(
'INSERT INTO pickware_erp_warehouse_stock (
id,
product_id,
product_version_id,
quantity,
warehouse_id,
created_at
) VALUES (
:id,
:productId,
:productVersionId,
:changeAmount,
:warehouseId,
NOW(3)
) ON DUPLICATE KEY UPDATE
quantity = quantity + VALUES(quantity),
updated_at = NOW(3)',
[
'id' => Uuid::randomBytes(),
'productId' => hex2bin($payload['productId']),
'productVersionId' => hex2bin($payload['productVersionId']),
'warehouseId' => hex2bin($payload['warehouseId']),
'changeAmount' => $payload['changeAmount'],
],
);
}
/**
* This is the indexer scenario. Updates all warehouse stocks for the given products.
*
* DEPENDS ON pickware_erp_stock to have been calculated before for the given products.
*
* The warehouse stocks are summed up from all warehouse-stock-relevant `pickware_erp_stock`s. The reference to the
* respective warehouse needs to be manually selected (not automatically). Therefore, when the list of
* warehouse-stock-relevant stock locations changes, we also need to update this query!
*
* @param String[] $productIds
*/
public function calculateWarehouseStockForProducts(array $productIds): void
{
RetryableTransaction::retryable($this->db, function () use ($productIds): void {
$this->warehouseStockInitializer->ensureProductWarehouseStockForProductsExist($productIds);
});
RetryableTransaction::retryable($this->db, function () use ($productIds): void {
$this->db->executeStatement(
'UPDATE `pickware_erp_warehouse_stock`
LEFT JOIN (
SELECT
stock.`product_id` as productId,
stock.`product_version_id` as productVersionId,
SUM(stock.`quantity`) as quantity,
COALESCE(
stock.`warehouse_id`, # stock location "warehouse"
binLocation.`warehouse_id` # stock location "bin_location"
) as warehouseId
FROM `pickware_erp_stock` stock
LEFT JOIN `pickware_erp_bin_location` binLocation
ON stock.`bin_location_id` = binLocation.`id`
WHERE stock.`product_id` IN (:productIds)
AND stock.`product_version_id` = :liveVersionId
AND stock.`location_type_technical_name` IN (:warehouseStockRelevantStockLocationTypes)
GROUP BY COALESCE(
stock.`warehouse_id`,
binLocation.`warehouse_id`
),
stock.`product_id`,
stock.`product_version_id`
) newWarehouseStocks
ON `pickware_erp_warehouse_stock`.`product_id` = newWarehouseStocks.productId
AND `pickware_erp_warehouse_stock`.`product_version_id` = newWarehouseStocks.productVersionId
AND `pickware_erp_warehouse_stock`.`warehouse_id` = newWarehouseStocks.warehouseId
SET `pickware_erp_warehouse_stock`.quantity = COALESCE(newWarehouseStocks.`quantity`, 0)
WHERE `pickware_erp_warehouse_stock`.`product_id` IN (:productIds)',
[
'productIds' => array_map('hex2bin', $productIds),
'liveVersionId' => hex2bin(Defaults::LIVE_VERSION),
'warehouseStockRelevantStockLocationTypes' => [
LocationTypeDefinition::TECHNICAL_NAME_WAREHOUSE,
LocationTypeDefinition::TECHNICAL_NAME_BIN_LOCATION,
],
],
[
'productIds' => Connection::PARAM_STR_ARRAY,
'warehouseStockRelevantStockLocationTypes' => Connection::PARAM_STR_ARRAY,
],
);
});
}
}

custom/plugins/PickwareErpStarter/src/Stock/WarehouseStockUpdater.php line 53