Symfony Profiler

<?php
namespace App\AtechNet\CoreBundle\Repository;
use App\AtechNet\CoreBundle\Entity\Album;
use App\AtechNet\CoreBundle\Entity\Attribute;
use App\AtechNet\CoreBundle\Entity\AttributeOption;
use App\AtechNet\CoreBundle\Entity\Basket;
use App\AtechNet\CoreBundle\Entity\BasketItem;
use App\AtechNet\CoreBundle\Entity\CategoryPage;
use App\AtechNet\CoreBundle\Entity\V2Attribute;
use App\AtechNet\CoreBundle\Entity\Domain;
use App\AtechNet\CoreBundle\Entity\ErrorCode;
use App\AtechNet\CoreBundle\Entity\Notification;
use App\AtechNet\CoreBundle\Entity\NotificationConfig;
use App\AtechNet\CoreBundle\Entity\Order;
use App\AtechNet\CoreBundle\Entity\OrderComment;
use App\AtechNet\CoreBundle\Entity\Photo;
use App\AtechNet\CoreBundle\Entity\Product;
use App\AtechNet\CoreBundle\Entity\ProductAttribute;
use App\AtechNet\CoreBundle\Entity\ProductCommission;
use App\AtechNet\CoreBundle\Entity\ProductContainer;
use App\AtechNet\CoreBundle\Entity\ProductContainerMeta;
use App\AtechNet\CoreBundle\Entity\ProductMeta;
use App\AtechNet\CoreBundle\Entity\ProductModel;
use App\AtechNet\CoreBundle\Entity\ProductPriceLog;
use App\AtechNet\CoreBundle\Entity\ProductTag;
use App\AtechNet\CoreBundle\Entity\ProductType;
use App\AtechNet\CoreBundle\Entity\StockItem;
use App\AtechNet\CoreBundle\Entity\StockMovement;
use App\AtechNet\CoreBundle\Entity\StockMovementItem;
use App\AtechNet\CoreBundle\Entity\StockPosition;
use App\AtechNet\CoreBundle\Entity\User;
use App\AtechNet\CoreBundle\Entity\Vendor;
use App\AtechNet\CoreBundle\Helper\Furbify\MultiStore;
use App\AtechNet\CoreBundle\Helper\Furbify\ProductHelper;
use App\AtechNet\CoreBundle\Helper\Furbify\SeoUrlHelper;
use DateTime;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\DBAL\Exception;
use Doctrine\DBAL\ParameterType;
use Doctrine\ORM\Exception\NotSupported;
use Doctrine\ORM\Exception\ORMException;
use Doctrine\ORM\NonUniqueResultException;
use Doctrine\ORM\NoResultException;
use Doctrine\ORM\OptimisticLockException;
use Doctrine\ORM\Query;
use Doctrine\ORM\Query\ResultSetMappingBuilder;
use Doctrine\ORM\QueryBuilder;
use Doctrine\ORM\Tools\Pagination\Paginator;
use Doctrine\ORM\TransactionRequiredException;
use Doctrine\Persistence\ManagerRegistry;
use Generator;
use Psr\Cache\CacheItemPoolInterface;
use Psr\SimpleCache\InvalidArgumentException;
use Symfony\Component\DependencyInjection\ParameterBag\ParameterBagInterface;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Routing\Exception\InvalidParameterException;
use Symfony\Component\Security\Core\Security;
use Symfony\Contracts\Translation\TranslatorInterface;
/**
* @extends AbstractRepository<Product>
*
* @method Product|null find($id, $lockMode = null, $lockVersion = null)
* @method Product|null findOneBy(array $criteria, array $orderBy = null)
* @method Product[] findAll()
* @method Product[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class ProductRepository extends AbstractRepository
{
public function __construct(
ManagerRegistry $registry,
Security $security,
TranslatorInterface $translator,
ParameterBagInterface $parameterBag,
private CacheItemPoolInterface $cache
)
{
parent::__construct($registry, Product::class);
$this->security = $security;
$this->translator = $translator;
$this->parameterBag = $parameterBag;
}
public const LAST_AVG_PRICE_CACHE_KEY = 'lastAndAveragePrices.v2';
/**
* TEST_GROUP -g ProductRepositoryTest_getStockItems
* @param Product $product
* @param array $filter
* @return array
*/
public function getStockItems(Product $product, array $filter = []): array
{
if (!isset($filter['inStock'])) {
$filter['inStock'] = null;
}
if (!isset($filter['available'])) {
$filter['available'] = null;
}
if (!empty($filter['sortBy'])) {
if ($filter['sortBy'] == 'created-asc') {
$orderBy = " ORDER BY si.created ASC, sm.created ASC";
} else {
$orderBy = " ORDER BY si.created DESC, sm.created ASC";
}
} else {
$orderBy = " ORDER BY si.created DESC, sm.created ASC";
}
// Entity Manager
$entityManager = $this->getEntityManager();
//⢀⡀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀
//⢻⣿⡗⢶⣤⣀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣀⣠⣄
//⠀⢻⣇⠀⠈⠙⠳⣦⣀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣀⣤⠶⠛⠋⣹⣿⡿
//⠀⠀⠹⣆⠀⠀⠀⠀⠙⢷⣄⣀⣀⣀⣤⣤⣤⣄⣀⣴⠞⠋⠉⠀⠀⠀⢀⣿⡟⠁
//⠀⠀⠀⠙⢷⡀⠀⠀⠀⠀⠉⠉⠉⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⣠⡾⠋⠀⠀
//⠀⠀⠀⠀⠈⠻⡶⠂⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⢠⣠⡾⠋⠀⠀⠀⠀
//⠀⠀⠀⠀⠀⣼⠃⠀⢠⠒⣆⠀⠀⠀⠀⠀⠀⢠⢲⣄⠀⠀⠀⢻⣆⠀⠀⠀⠀⠀
//⠀⠀⠀⠀⢰⡏⠀⠀⠈⠛⠋⠀⢀⣀⡀⠀⠀⠘⠛⠃⠀⠀⠀⠈⣿⡀⠀⠀⠀⠀
//⠀⠀⠀⠀⣾⡟⠛⢳⠀⠀⠀⠀⠀⣉⣀⠀⠀⠀⠀⣰⢛⠙⣶⠀⢹⣇⠀⠀⠀⠀
//⠀⠀⠀⠀⢿⡗⠛⠋⠀⠀⠀⠀⣾⠋⠀⢱⠀⠀⠀⠘⠲⠗⠋⠀⠈⣿⠀⠀⠀⠀
//⠀⠀⠀⠀⠘⢷⡀⠀⠀⠀⠀⠀⠈⠓⠒⠋⠀⠀⠀⠀⠀⠀⠀⠀⠀⢻⡇⠀⠀⠀
//⠀⠀⠀⠀⠀⠈⡇⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⢸⣧⠀⠀⠀
// mikor meglattam
//-> Result Mapping
$rsm = new ResultSetMappingBuilder($entityManager, ResultSetMappingBuilder::COLUMN_RENAMING_CUSTOM);
//-> Stock Item ->
// Alias
$alias = 'si';
// Entity Name
$entityName = 'App\AtechNet\CoreBundle\Entity\StockItem';
// Add Entity
$rsm->addEntityResult($entityName, $alias);
// Meta Data of Entity
$entityMetaData = $entityManager->getClassMetadata($entityName);
// Fields of Entity
$fields = $entityMetaData->getFieldNames();
foreach ($fields as $field) {
// Column
$column = $entityMetaData->getColumnName($field);
// Add Field
$rsm->addFieldResult($alias, $column, $field, $entityName);
}
//<- Stock Item
//-> Photo Album ->
// Alias
$alias = 'album';
// Entity Name
$entityName = 'App\AtechNet\CoreBundle\Entity\Album';
// Add Joined Entity
$rsm->addJoinedEntityResult($entityName, $alias, 'si', 'album');
// Meta Data of Entity
$entityMetaData = $entityManager->getClassMetadata($entityName);
// Fields of Entity
$fields = $entityMetaData->getFieldNames();
foreach ($fields as $field) {
// Column
$column = $entityMetaData->getColumnName($field);
// Add Field
$rsm->addFieldResult($alias, $alias . '_' . $column, $field, $entityName);
}
//<- Photo Album
//-> Tester ->
// Alias
$alias = 't';
// Entity Name
$entityName = 'App\AtechNet\CoreBundle\Entity\User';
// Add Joined Entity
$rsm->addJoinedEntityResult($entityName, $alias, 'si', 'tester');
// Meta Data of Entity
$entityMetaData = $entityManager->getClassMetadata($entityName);
// Fields of Entity
$fields = $entityMetaData->getFieldNames();
foreach ($fields as $field) {
// Column
$column = $entityMetaData->getColumnName($field);
// Add Field
$rsm->addFieldResult($alias, $alias . '_' . $column, $field, $entityName);
}
//<- Tester
//-> Stock Movement Items ->
// Alias
$alias = 'smi';
// Entity Name
$entityName = StockMovementItem::class;
// Add Joined Entity
$rsm->addJoinedEntityResult($entityName, $alias, 'si', 'movementItems');
// Meta Data of Entity
$entityMetaData = $entityManager->getClassMetadata($entityName);
// Fields of Entity
$fields = $entityMetaData->getFieldNames();
foreach ($fields as $field) {
// Column
$column = $entityMetaData->getColumnName($field);
// Add Field
$rsm->addFieldResult($alias, $alias . '_' . $column, $field, $entityName);
}
//Stock Movement Items
//-> Shipment Item ->
// Alias
$alias = 'shi';
// Entity Name
$entityName = 'App\AtechNet\CoreBundle\Entity\AikenShipmentItem';
// Add Joined Entity
$rsm->addJoinedEntityResult($entityName, $alias, 'si', 'aikenShipmentItem');
// Meta Data of Entity
$entityMetaData = $entityManager->getClassMetadata($entityName);
// Fields of Entity
$fields = $entityMetaData->getFieldNames();
foreach ($fields as $field) {
// Column
$column = $entityMetaData->getColumnName($field);
// Add Field
$rsm->addFieldResult($alias, $alias . '_' . $column, $field, $entityName);
}
//<- Shipment Item
//-> Stock Movement ->
// Alias
$alias = 'sm';
// Entity Name
$entityName = 'App\AtechNet\CoreBundle\Entity\StockMovement';
// Add Joined Entity
$rsm->addJoinedEntityResult($entityName, $alias, 'smi', 'movement');
// Meta Data of Entity
$entityMetaData = $entityManager->getClassMetadata($entityName);
// Fields of Entity
$fields = $entityMetaData->getFieldNames();
foreach ($fields as $field) {
// Column
$column = $entityMetaData->getColumnName($field);
// Add Field
$rsm->addFieldResult($alias, $alias . '_' . $column, $field, $entityName);
}
//<- Stock Movement
//-> Product ->
// Alias
$alias = 'p';
// Entity Name
$entityName = Product::class;
// Add Joined Entity
$rsm->addJoinedEntityResult($entityName, $alias, 'smi', 'product');
// Meta Data of Entity
$entityMetaData = $entityManager->getClassMetadata($entityName);
// Fields of Entity
$fields = $entityMetaData->getFieldNames();
foreach ($fields as $field) {
// Column
$column = $entityMetaData->getColumnName($field);
// Add Field
$rsm->addFieldResult($alias, $alias . '_' . $column, $field, $entityName);
}
$rsm->addMetaResult($alias, 'p_product_model_id', 'product_model_id', false, 'integer');
// <- Product
//-> Stock ->
// Alias
$alias = 's';
// Entity Name
$entityName = 'App\AtechNet\CoreBundle\Entity\Stock';
// Add Joined Entity
$rsm->addJoinedEntityResult($entityName, $alias, 'sm', 'stock');
// Meta Data of Entity
$entityMetaData = $entityManager->getClassMetadata($entityName);
// Fields of Entity
$fields = $entityMetaData->getFieldNames();
foreach ($fields as $field) {
// Column
$column = $entityMetaData->getColumnName($field);
// Add Field
$rsm->addFieldResult($alias, $alias . '_' . $column, $field, $entityName);
}
//<- Stock
//-> Customer ->
// Alias
$alias = 'c';
// Entity Name
$entityName = 'App\AtechNet\CoreBundle\Entity\Customer';
// Add Joined Entity
$rsm->addJoinedEntityResult($entityName, $alias, 'sm', 'merchant');
// Meta Data of Entity
$entityMetaData = $entityManager->getClassMetadata($entityName);
// Fields of Entity
$fields = $entityMetaData->getFieldNames();
foreach ($fields as $field) {
// Column
$column = $entityMetaData->getColumnName($field);
// Add Field
$rsm->addFieldResult($alias, $alias . '_' . $column, $field, $entityName);
}
//<- Customer
//-> Domain ->
// Alias
$alias = 'd';
// Entity Name
$entityName = Domain::class;
// Add Joined Entity
$rsm->addJoinedEntityResult($entityName, $alias, 'c', 'domain');
// Meta Data of Entity
$entityMetaData = $entityManager->getClassMetadata($entityName);
// Fields of Entity
$fields = $entityMetaData->getFieldNames();
foreach ($fields as $field) {
// Column
$column = $entityMetaData->getColumnName($field);
// Add Field
$rsm->addFieldResult($alias, $alias . '_' . $column, $field, $entityName);
}
//<- Domain
//-> Created By ->
// Alias
$alias = 'cr';
// Entity Name
$entityName = 'App\AtechNet\CoreBundle\Entity\User';
// Add Joined Entity
$rsm->addJoinedEntityResult($entityName, $alias, 'sm', 'createdBy');
// Meta Data of Entity
$entityMetaData = $entityManager->getClassMetadata($entityName);
// Fields of Entity
$fields = $entityMetaData->getFieldNames();
foreach ($fields as $field) {
// Column
$column = $entityMetaData->getColumnName($field);
// Add Field
$rsm->addFieldResult($alias, $alias . '_' . $column, $field, $entityName);
}
//<- Created By
//<- Result Mapping
// Select Expression
$select = $rsm->__toString();
// SQL
$sql = "SELECT $select
FROM stock_items AS si
LEFT JOIN albums AS album ON (album.id = si.photo_album_id)
LEFT JOIN users AS t ON ( t.id = si.tester_id )
INNER JOIN stock_movement_items_stock_items AS smisi ON ( smisi.stockitem_id = si.id )
INNER JOIN stock_movement_item AS smi ON ( smi.id = smisi.stockmovementitem_id )
LEFT JOIN aiken_shipment_item AS shi ON ( shi.id = si.aiken_shipment_item_id )
INNER JOIN stock_movements AS sm ON ( sm.id = smi.stock_movement_id )
LEFT JOIN stocks AS s ON ( s.id = sm.stock_id )
LEFT JOIN customers AS c ON ( c.id = sm.merchant_id )
LEFT JOIN domains AS d ON ( d.id = c.domain_id )
LEFT JOIN users AS cr ON ( cr.id = sm.created_by )
LEFT JOIN products AS p ON ( p.id = smi.product_id )
WHERE smi.product_id = :product";
// Where Conditions
$where = $parameters = [];
if (!empty($filter['filterEsetCommentSerials'])) {
$sql .= " AND si.serial_number NOT IN(:usedEsetSerials)";
$parameters['usedEsetSerials'] = $filter['filterEsetCommentSerials'];
}
if (!empty($filter['filterSerialNumber'])) {
$where[] = "si.serial_number LIKE :serialNumber";
$parameters['serialNumber'] = $filter['filterSerialNumber'];
}
if (!is_null($filter['inStock'])) {
$where[] = "si.in_stock = :inStock";
$parameters['inStock'] = ($filter['inStock'] ? 1 : 0);
}
if (!is_null($filter['available'])) {
if ($filter['available']) {
$where[] = "( si.in_stock = 1 AND si.available = 1 )";
} else {
$where[] = "( si.in_stock = 0 OR si.available = 0 )";
}
}
if ( !empty($filter['filterBacklit']) && $filter['filterBacklit'] === true ) {
$where[] = "si.keyboard_backlight = :filterBacklit";
$parameters['filterBacklit'] = 1;
}
if ($filter['inStock'] === true) {
$where[] = "( SELECT COUNT(_sm.id) FROM stock_movements AS _sm WHERE _sm.id = ( SELECT sm2.id FROM stock_items AS si2 INNER JOIN stock_movement_items_stock_items AS smisi2 ON ( smisi2.stockitem_id = si2.id ) INNER JOIN stock_movement_item AS smi2 ON ( smi2.id = smisi2.stockmovementitem_id AND smi2.product_id = :product ) INNER JOIN stock_movements AS sm2 ON ( sm2.id = smi2.stock_movement_id ) WHERE si2.id = si.id ORDER BY sm2.created DESC LIMIT 1 ) AND _sm.type = " . StockMovement::TYPE_OUT . " ) = 0";
}
if (!empty($where)) {
$sql .= ' AND ' . implode(' AND ', $where);
}
// Order By
$sql .= $orderBy;
// Query
$query = $entityManager->createNativeQuery($sql, $rsm);
// Quer Parameters
$parameters['product'] = $product->getId();
$query->setParameters($parameters);
// Results
$results = $query->getResult();
if (!empty($filter['sortBy'])) {
switch ($filter['sortBy']) {
case 'purchase-price-desc':
// Sort Order
$sortOrder = [];
foreach ($results as $index => $result) {
$lastInMoveItem = null;
foreach ($result->getMovementItems() as $movementItem) {
if ($movementItem->getMovement()->getType() == StockMovement::TYPE_IN) {
$lastInMoveItem = $movementItem;
break;
}
}
$purchasePrice = $lastInMoveItem->getTotalPricePerItem() / $lastInMoveItem->getMovement()->getMerchant()->getDomain()->getPriceFactor();
$sortOrder[$index] = $purchasePrice;
}
arsort($sortOrder);
$_results = [];
foreach ($sortOrder as $index => $value) {
$_results[] = $results[$index];
}
$results = $_results;
break;
case 'purchase-price-asc':
// Sort Order
$sortOrder = [];
foreach ($results as $index => $result) {
$lastInMoveItem = null;
foreach ($result->getMovementItems() as $movementItem) {
if ($movementItem->getMovement()->getType() == StockMovement::TYPE_IN) {
$lastInMoveItem = $movementItem;
break;
}
}
$purchasePrice = $lastInMoveItem->getTotalPricePerItem() / $lastInMoveItem->getMovement()->getMerchant()->getDomain()->getPriceFactor();
$sortOrder[$index] = $purchasePrice;
}
asort($sortOrder);
$_results = [];
foreach ($sortOrder as $index => $value) {
$_results[] = $results[$index];
}
$results = $_results;
break;
case 'selling-price-desc':
// Sort Order
$sortOrder = [];
foreach ($results as $index => $result) {
$lastMoveItem = $result->getFirstMovementItem();
if ($lastMoveItem->getMovement()->getType() == StockMovement::TYPE_OUT) {
$sellingPrice = $lastMoveItem->getPrice() / $lastMoveItem->getQuantity() / $lastMoveItem->getMovement()->getMerchant()->getDomain()->getPriceFactor();
} else {
$sellingPrice = 0;
}
$sortOrder[$index] = $sellingPrice;
}
arsort($sortOrder);
$_results = [];
foreach ($sortOrder as $index => $value) {
$_results[] = $results[$index];
}
$results = $_results;
break;
case 'selling-price-asc':
// Sort Order
$sortOrder = [];
foreach ($results as $index => $result) {
$lastMoveItem = $result->getFirstMovementItem();
if ($lastMoveItem->getMovement()->getType() == StockMovement::TYPE_OUT) {
$sellingPrice = $lastMoveItem->getPrice() / $lastMoveItem->getQuantity() / $lastMoveItem->getMovement()->getMerchant()->getDomain()->getPriceFactor();
} else {
$sellingPrice = 0;
}
$sortOrder[$index] = $sellingPrice;
}
asort($sortOrder);
$_results = [];
foreach ($sortOrder as $index => $value) {
$_results[] = $results[$index];
}
$results = $_results;
break;
}
}
// Limit
if (isset($filter['limit']) && $filter['limit'] > 0) {
$results = array_slice($results, $filter['offset'], $filter['limit']);
}
return $results;
}
/**
* @param int $productId
* @param bool $isContainer
* @return bool
*/
public function hasStockItemAlbum(int $productId, bool $isContainer = false): bool
{
return count($this->getResultForStockItemAlbums($productId, $isContainer)) > 0;
}
/**
* @param int $productId
* @param bool $isContainer
* @return null|array
*/
public function getStockItemAlbums(int $productId, bool $isContainer = false): ?array
{
$stockItems = $this->getResultForStockItemAlbums($productId, $isContainer);
$albums = [];
if (count($stockItems) > 0) {
foreach ($stockItems as $si) {
$albums[$si->getId()]['albumId'] = $si->getAlbum()->getId();
$albums[$si->getId()]['aikenCode'] = $si->getAikenCode();
}
}
return $albums;
}
/**
* TEST_GROUP -g ProductRepositoryTest_getResultForStockItemAlbums
* @param int $productId
* @param bool $isContainer
* @return array
*/
private function getResultForStockItemAlbums(int $productId, bool $isContainer = false): array
{
if ($isContainer) {
$containerProducts = $this->getEntityManager()->getConnection()->fetchAllAssociative("SELECT child_id FROM product_container WHERE product_id = ?", [$productId], [ParameterType::INTEGER]);
if ($containerProducts) {
$subQuery_1 = $subQuery_2 = "IDENTITY(mi.product) IN (" . implode(',', array_map(function ($product) {
return (int)$product['child_id'];
}, $containerProducts)) . ")";
} else {
$subQuery_1 = "IDENTITY(mi.product) IN ( SELECT IDENTITY(pc1.child) FROM AtechNetCoreBundle:ProductContainer pc1 WHERE pc1.product = $productId )";
$subQuery_2 = "IDENTITY(mi.product) IN ( SELECT IDENTITY(pc2.child) FROM AtechNetCoreBundle:ProductContainer pc2 WHERE pc2.product = $productId )";
}
} else {
$subQuery_1 = $subQuery_2 = "IDENTITY(mi.product) = $productId";
}
$query = $this->getEntityManager()
->createQuery(
"SELECT si
FROM AtechNetCoreBundle:StockItem si
INNER JOIN si.movementItems mi
INNER JOIN mi.movement sm WITH $subQuery_1
INNER JOIN AtechNetCoreBundle:Photo photo WITH photo.album = si.album
WHERE
si.inStock = 1 AND
si.available = 1 AND
si.album > 0 AND
$subQuery_2 AND
si.aikenCode NOT IN (
SELECT s.aikenCode
FROM AtechNetCoreBundle:StockItem s
INNER JOIN AtechNetCoreBundle:OrderComment oc WITH oc.text LIKE '%" . StockItem::PREFIX_FOR_SELECTED_ITEMS . "%'
INNER JOIN oc.order o WITH o.status <= " . Order::STATUS_WAITING_FOR_ANSWER . "
WHERE
IDENTITY(s.album) > 0 AND
oc.text LIKE CONCAT('%', s.aikenCode, '%') AND
oc.type = " . OrderComment::TYPE_FOR_PREPARATION . " AND
IDENTITY(oc.createdBy) = " . User::CENTRALIS_USER_ID . "
)
GROUP BY si.id"
);
return $query->getResult();
}
/**
* @param StockItem $stockItem
*
* @return array
*/
public function getVisibleErrorCodes(StockItem $stockItem): array
{
return $this->getEntityManager()->getRepository(ErrorCode::class)
->createQueryBuilder('ec')
->where('ec.visibleOnWeb = 1')
->andWhere('ec.code IN (:errorCodes)')
->setParameter('errorCodes', explode(' ', $stockItem->getErrorCodes()))
->getQuery()
->getResult();
}
/**
* TEST_GROUP -g ProductRepositoryTest_getTotalStockItems
* @param Product $product
* @param array $filter
* @return int
* @throws Exception
* @throws \Doctrine\DBAL\Driver\Exception
*/
public function getTotalStockItems(Product $product, array $filter = []): int
{
if (!isset($filter['inStock'])) {
$filter['inStock'] = null;
}
if (!isset($filter['available'])) {
$filter['available'] = null;
}
$entityManager = $this->getEntityManager();
$connection = $entityManager->getConnection();
$sql = "SELECT COUNT(DISTINCT si.id) AS total FROM stock_items AS si INNER JOIN stock_movement_items_stock_items AS smisi ON ( smisi.stockitem_id = si.id ) INNER JOIN stock_movement_item AS smi ON ( smi.id = smisi.stockmovementitem_id ) INNER JOIN stock_movements AS sm ON ( sm.id = smi.stock_movement_id )";
// Where Conditions
$where = $parameters = [];
$where[] = "smi.product_id = ?";
$parameters[] = $product->getId();
if (!empty($filter['filterSerialNumber'])) {
$where[] = "si.serial_number LIKE ?";
$parameters[] = $filter['filterSerialNumber'];
}
if (!is_null($filter['inStock'])) {
$where[] = "si.in_stock = ?";
$parameters[] = ($filter['inStock'] ? 1 : 0);
}
if (!is_null($filter['available'])) {
if ($filter['available']) {
$where[] = "( si.in_stock = 1 AND si.available = 1 )";
} else {
$where[] = "( si.in_stock = 0 OR si.available = 0 )";
}
}
if ($filter['inStock'] === true) {
$where[] = "( SELECT COUNT(_sm.id) FROM stock_movements AS _sm WHERE _sm.id = ( SELECT sm2.id FROM stock_items AS si2 INNER JOIN stock_movement_items_stock_items AS smisi2 ON ( smisi2.stockitem_id = si2.id ) INNER JOIN stock_movement_item AS smi2 ON ( smi2.id = smisi2.stockmovementitem_id AND smi2.product_id = ? ) INNER JOIN stock_movements AS sm2 ON ( sm2.id = smi2.stock_movement_id ) WHERE si2.id = si.id ORDER BY sm2.created DESC LIMIT 1 ) AND _sm.type = " . StockMovement::TYPE_OUT . " ) = 0";
$parameters[] = $product->getId();
}
if (!empty($where)) {
$sql .= ' WHERE ' . implode(' AND ', $where);
}
$query = $connection->executeQuery($sql, $parameters);
$results = $query->fetchAllAssociative();
$result = array_pop($results);
return !empty($result['total']) ? (int)$result['total'] : 0;
}
/**
* TEST_GROUP -g ProductRepositoryTest_getStockItemsQuantity
* @param Product $product
* @param null|bool $inStock
* @param null|bool $available
* @param null $all
* @return int|array
*/
public function getStockItemsQuantity(Product $product, bool $inStock = null, bool $available = null, $all = null): int|array
{
$allInQuantity = 0;
$allOutQuantity = 0;
$allNeutralQuantity = 0;
$queryArray = [
'In' => StockMovement::TYPE_IN,
'Out' => StockMovement::TYPE_OUT,
'Neutral' => StockMovement::TYPE_NEUTRAL . ' AND sm.subType != ' . StockMovement::SUB_TYPE_NEUTRAL_IN_STOCK,
];
foreach ($queryArray as $type => $queryWhere) {
$quantityQuery = 'SELECT SUM(mi.quantity) as stockItemQuantity
FROM AtechNetCoreBundle:StockMovementItem mi
JOIN mi.movement sm
WHERE mi.product = :product
AND sm.type = ' . $queryWhere;
$results = $this->getEntityManager()
->createQuery($quantityQuery)
->setParameter('product', $product->getId())
->getResult();
${'all' . $type . 'Quantity'} = $results['0']['stockItemQuantity'];
if (is_null(${'all' . $type . 'Quantity'})) {
${'all' . $type . 'Quantity'} = 0;
}
}
if ($all) {
return [
'in' => $allInQuantity,
'out' => $allOutQuantity,
'neutral' => $allNeutralQuantity,
];
}
if (!is_null($inStock) || !is_null($available)) {
if (!is_null($inStock) && $inStock && is_null($available)) { // in stock stockItems count
return $allInQuantity - $allOutQuantity;
}
if (!is_null($inStock) && !$inStock && is_null($available)) { // out of stock stockItems count
return $allOutQuantity;
}
if (is_null($inStock) && !is_null($available) && $available) { // available stockItems count
return $allInQuantity - $allOutQuantity - $allNeutralQuantity;
}
if (is_null($inStock) && !is_null($available) && !$available) { // unavailable stockItems count
return $allInQuantity + $allNeutralQuantity;
}
}
return $allInQuantity; // all stockItems count
}
/**
* @param Product $product
* @return array
*/
public function getInStockStockItems(Product $product): array
{
return $this->getStockItems($product, [
'inStock' => true,
]);
}
/**
* @param Product $product
* @return array
*/
public function getOutOfStockStockItems(Product $product): array
{
return $this->getStockItems($product, [
'inStock' => false,
]);
}
/**
* @param Product $product
* @return array
*/
public function getAvailableStockItems(Product $product): array
{
return $this->getStockItems($product, [
'available' => true,
]);
}
/**
* @param Product $product
* @return array
*/
public function getUnavailableStockItems(Product $product): array
{
return $this->getStockItems($product, [
'available' => false,
]);
}
/**
* @param Product $product
* @return array
*/
public function getInStockStockItemsQuantity(Product $product)
{
if ($product->isContainer()) {
return $this->getMinAvailableOrInStock($product->getId(), 'in_stock');
}
return $this->getStockItemsQuantity($product, true);
}
/**
* @param Product $product
* @return array
*/
public function getOutOfStockStockItemsQuantity(Product $product)
{
return $this->getStockItemsQuantity($product, false, null);
}
/**
* @param Product $product
* @return array
*/
public function getAvailableStockItemsQuantity(Product $product)
{
if ($product->isContainer()) {
return $this->getMinAvailableOrInStock($product->getId(), 'available');
}
return $this->getStockItemsQuantity($product, null, true);
}
/**
* @param $prodId
* @param $col
* @return mixed
*/
private function getMinAvailableOrInStock($prodId, $col)
{
$query = "SELECT MIN(p2.$col DIV pc.multiplier) AS min_val
FROM products p
JOIN product_container pc ON p.id = pc.product_id
JOIN products p2 ON p2.id = pc.child_id
WHERE pc.multiplier > 0 AND p.id = $prodId";
$connection = $this->getEntityManager()->getConnection();
$result = $connection->fetchAllAssociative($query);
return $result[0]['min_val'];
}
/**
* @param Product $product
* @return array
*/
public function getUnavailableStockItemsQuantity(Product $product)
{
return $this->getStockItemsQuantity($product, null, false);
}
/**
* TEST_GROUP -g ProductRepositoryTest_getStockMovements
* @param Product $product
* @param array $filter
* @return array
*/
public function getStockMovements(Product $product, array $filter = []): array
{
$where = [];
if ($filter['type'] > 0) {
$where[] = 'sm.type = ' . $filter['type'];
}
if ($filter['subType'] > 0) {
$where[] = 'sm.subType = ' . $filter['subType'];
}
if (isset($filter['domain']) && (int)$filter['domain'] > 0) {
$where[] = 'm.domain = ' . (int)$filter['domain'];
}
$where[] = 'p = :product';
$where = implode(" AND ", $where);
$query = $this->getEntityManager()
->createQuery('SELECT sm, mi, PARTIAL d.{id,code}
FROM AtechNetCoreBundle:StockMovement sm
JOIN sm.movementItems mi
JOIN mi.product p
JOIN sm.merchant m
LEFT JOIN sm.documents d
WHERE ' . $where . '
ORDER BY sm.created DESC')
->setParameter('product', $product)
->setMaxResults($filter['limit'])
->setFirstResult($filter['offset']);
return iterator_to_array((new Paginator($query, true)));
}
/**
* TEST_GROUP -g ProductRepositoryTest_getTotalStockMovements
* @param Product $product
* @param array $filter
* @return int
* @throws NoResultException
* @throws NonUniqueResultException
*/
public function getTotalStockMovements(Product $product, array $filter = []): int
{
$where = [];
if ((int)$filter['type'] > 0) {
$where[] = 'sm.type = ' . (int)$filter['type'];
}
if ((int)$filter['subType'] > 0) {
$where[] = 'sm.subType = ' . (int)$filter['subType'];
}
if (isset($filter['domain']) && (int)$filter['domain'] > 0) {
$where[] = 'm.domain = ' . (int)$filter['domain'];
}
$where[] = 'p = :product';
$where = implode(" AND ", $where);
return $this->getEntityManager()
->createQuery(
"SELECT COUNT(DISTINCT sm.id)
FROM AtechNetCoreBundle:StockMovement sm
JOIN sm.movementItems mi
JOIN mi.product p
JOIN sm.merchant m
WHERE $where"
)
->setParameter('product', $product)
->getSingleScalarResult();
}
/**
* TEST_GROUP -g ProductRepositoryTest_getLastPrices
* @param array $productIds
* @return array
* @throws Exception
*/
public function getLastPrices(array $productIds = []): array
{
// Unset product ids for shipping related products
if (count($productIds) > 0) {
unset($productIds[Product::SHIPPING_ID]);
unset($productIds[Product::CASH_ON_DELIVERY_PRODUCT_ID]);
unset($productIds[Product::MPL_PACKAGE_FEE_PRODUCT_ID]);
}
$productCondition = count($productIds) > 0 ? ' AND smi.product_id IN (' . implode(',', $productIds) . ')' : '';
$connection = $this->getEntityManager()->getConnection();
// Last price - select only last in movement which has the correct product id
$data = $connection->executeQuery('
SELECT l.product_id AS id, l.last_price FROM (
SELECT smi.product_id, (sm.extra_price/sm.quantity + smi.price/smi.quantity) / sm.exchange_rate AS last_price
FROM stock_movement_item smi
LEFT JOIN stock_movements sm ON sm.id = smi.stock_movement_id
WHERE sm.type = ' . StockMovement::TYPE_IN . '
AND (sm.extra_price > 0 OR smi.price > 0)
' . $productCondition . '
ORDER BY sm.created DESC
) l')->fetchAllAssociative();
$result = [];
foreach ($productIds as $productId) {
$result[$productId] = [
'last_price' => 0,
];
}
foreach ($data as $r) {
$result[$r['id']] = [
'last_price' => $r['last_price'],
];
}
return $result;
}
/**
* TEST_GROUP -g ProductRepositoryTest_getLastPrice
* @param int $productId
* @return array
* @throws Exception
*/
public function getLastPrice(int $productId): array
{
return $this->getLastPrices([$productId]);
}
/**
* TEST_GROUP -g ProductRepositoryTest_getProductTypeAndDateSpecificAveragePrices
* @param int $productTypeId
* @param DateTime $datetime
* @param $productSavedAveragePrice
* @return int|mixed
*/
public function getProductTypeAndDateSpecificAveragePrices(int $productTypeId, DateTime $datetime, $productSavedAveragePrice)
{
$eligibleProductTypes = $this->getProductTypesWithDifferentAveragePricesByDate();
if (isset($eligibleProductTypes[$productTypeId]) && $eligibleProductTypes[$productTypeId]['averagePriceDateChange'] < (int)$datetime->format('YmdHi')) {
return $eligibleProductTypes[$productTypeId]['averagePrice'];
} else {
return $productSavedAveragePrice;
}
}
/**
* TEST_GROUP -g ProductRepositoryTest_getProductTypeAndDateSpecificAveragePrices
* @return array[]
*
* DATETIME FORMAT: YmdHi
* PRICES IN EUR WITHOUT VAT
*/
public function getProductTypesWithDifferentAveragePricesByDate(): array
{
return [
ProductType::PRODUCT_TYPE_ID_POWER_ADAPTER => [
'averagePriceDateChange' => 202101010000,
'averagePrice' => 0.1,
],
ProductType::PRODUCT_TYPE_ID_SERVICE => [
'averagePriceDateChange' => 202505061557,
'averagePrice' => 0.0,
],
];
}
/**
* TEST_GROUP -g ProductRepositoryTest_getAvailableAndInStockCounts
* @param array $productIds
* @return array
* @throws Exception
*/
public function getAvailableAndInStockCounts(array $productIds = []): array
{
$counts = $this->getEntityManager()->getConnection()->fetchAllAssociative("SELECT p.id, p.available,t3.available AS available, p.in_stock, t3.instock AS instock FROM products p JOIN (
select id, SUM(case when instock='+' then total when instock='-' then -total else 0 end) instock,
SUM(case when available='+' then total when available='-' then -total else 0 end) available
FROM (
select id, SUM(quantity) total, LEFT(instock_available,1) instock, RIGHT(instock_available,1) available FROM (
SELECT p.id, smi.quantity,
case
when sm.type = " . StockMovement::TYPE_IN . " then '++'
when sm.type = " . StockMovement::TYPE_OUT . " then '--'
when sm.type = " . StockMovement::TYPE_NEUTRAL . " then '0-'
else '00'
end AS instock_available
FROM products p
JOIN stock_movement_item smi ON p.id = smi.product_id
JOIN stock_movements sm ON sm.id = smi.stock_movement_id" . (count($productIds) > 0 ? " WHERE p.id IN(" . implode(',', $productIds) . ")" : "") . "
) t group by id,instock_available
) t2 group by id
) t3 ON t3.id = p.id
WHERE p.type_id <> " . ProductType::PRODUCT_TYPE_ID_SERVICE);
$result = [];
foreach ($counts as $r) {
$result[$r['id']] = [
'available' => $r['available'],
'in_stock' => $r['instock'],
];
}
return $result;
}
/**
* TEST_GROUP -g ProductRepositoryTest_updateProductInStockAndAvailable
* @param Product $product
* @param int $count
* @return Product|null
* @throws NotSupported
* @throws ORMException
* @throws OptimisticLockException
* @throws TransactionRequiredException
* @throws \Doctrine\ORM\ORMException
*/
public function updateProductInStockAndAvailable(Product $product, int $count = 0): ?Product
{
// Product over the stock movement limit are updated with command once a day in the evening
if (in_array($product->getId(), Product::getExcludededProductIdsFromUpdate())
|| in_array($product->getType()->getId(), Product::getExcludededProductTypeIdsFromUpdate())
|| $product->getStockMovementItems()->count() >= Product::PRODUCT_IN_STOCK_AVAILABLE_STOCK_MOVEMENT_QTY_REFRESH_LIMIT
) {
return $product;
}
$this->getEntityManager()->refresh($product);
if ($this->updateProductInStock($product, false, $count) && $this->updateProductAvailable($product, false, $count)) {
if (!$product->isContainer()) {
$this->updateLinkedContainerProducts($product);
}
return $product;
}
return null;
}
/**
* TEST_GROUP -g ProductRepositoryTest_updateLastAndAvgPrices
* @param ArrayCollection<Product> $products
* @return bool
* @throws InvalidArgumentException
* @throws ORMException
* @throws OptimisticLockException
* @throws \Doctrine\ORM\ORMException
*/
public function updateLastAndAvgPrices(ArrayCollection $products): bool
{
if ($products->count() > 0) {
$entityManager = $this->getEntityManager();
$productIds = [];
/** @var Product $product */
foreach ($products as $product) {
if (in_array($product->getId(), Product::getExcludededProductIdsFromUpdate())) {
continue;
}
if (in_array($product->getType()->getId(), Product::getExcludededProductTypeIdsFromUpdate())) {
continue;
}
$productIds[] = $product->getId();
}
if (count($productIds) > 0) {
$lastPrices = $this->updateLastProductPricesInCache($productIds);
/** @var Product $product */
foreach ($products as $product) {
if (in_array($product->getId(), Product::getExcludededProductIdsFromUpdate())) {
continue;
}
$lastPrice = isset($lastPrices[$product->getId()]) && !is_null($lastPrices[$product->getId()]['last_price']) ? $lastPrices[$product->getId()]['last_price'] : 0;
if ($product->isScannableWithType()) {
$avgPrice = $this->getAdjustedAverageBuyingPrice($product);
} else {
$avgPrice = $this->getNotScannableAverageBuyingPrice($product);
}
$product->setLastPrice(number_format($lastPrice, 2, '.', ''));
$product->setAvgPrice(number_format($avgPrice, 2, '.', ''));
}
$entityManager->flush();
}
return false;
}
return false;
}
/**
* TEST_GROUP -g ProductRepositoryTest_updateProductInStockAndAvailable
* @param Product $product
* @return void
* @throws ORMException
* @throws OptimisticLockException
* @throws TransactionRequiredException
* @throws \Doctrine\ORM\ORMException
*/
private function updateLinkedContainerProducts(Product $product)
{
$entityManager = $this->getEntityManager();
$linkedProds = $entityManager->createQuery('SELECT p
FROM AtechNetCoreBundle:Product p
JOIN p.productContainers pc
WHERE pc.child = :prod AND p.archived = 0')
->setParameter('prod', $product)
->getResult();
if ($linkedProds) {
// newest data needed in DB
$entityManager->flush();
foreach ($linkedProds as $lp) {
$entityManager->refresh($lp);
$this->updateProductInStock($lp, false);
$this->updateProductAvailable($lp, false);
}
}
}
/**
* TEST_GROUP -g ProductRepositoryTest_updateProductInStockAndAvailable
* @param Product $product
* @param bool $refreshEntity
* @param int $count
* @return bool
*/
public function updateProductInStock(Product $product, bool $refreshEntity = true, int $count = 0): bool
{
// return true;
if ($refreshEntity) {
$this->getEntityManager()->refresh($product);
}
$productIsService = $product->getType()->getId() === ProductType::PRODUCT_TYPE_ID_SERVICE;
$inStock = $productIsService ? 999 : ($count !== 0 ? $product->getInStock() + $count : $this->getInStockStockItemsQuantity($product));
$product->setInStock($inStock);
if ($inStock <= 0 && $product->getStockPosition() != '') {
$product->setStockPosition(null);
}
return true;
}
/**
* TEST_GROUP -g ProductRepositoryTest_updateProductInStockAndAvailable
* @param Product $product
* @param bool $refreshEntity
* @param int $count
* @return bool
* @throws NotSupported
*/
public function updateProductAvailable(Product $product, bool $refreshEntity = true, int $count = 0): bool
{
// return true;
if ($refreshEntity) {
$this->getEntityManager()->refresh($product);
}
$productIsService = $product->getType()->getId() === ProductType::PRODUCT_TYPE_ID_SERVICE;
$available = $productIsService ? 999 : ($count !== 0 ? $product->getAvailable() + $count : $this->getAvailableStockItemsQuantity($product));
$product->setAvailable($available);
if (!$product->isAvailable()) {
foreach ($product->getMetas() as $meta) {
$meta->setOrderable(false);
$meta->setListed(false);
}
}
$this->createNotifications($product);
return true;
}
/**
* @param Product $product
* @throws NotSupported
*/
private function createNotifications(Product $product)
{
$notifRepo = $this->getEntityManager()->getRepository(Notification::class);
$ntfConfs = $notifRepo->getNotificationConfigs(NotificationConfig::TYPE_PRODUCT_AVAILABLE_LOW, $product->getId());
$text = $this->translator->trans('Low availability of') . ' ' . $product->getName();
foreach ($ntfConfs as $nc) {
if ($nc->getIntParam() >= $product->getAvailable()) {
$notifRepo->createNotifications($nc, $text);
}
}
}
/**
* @param bool $skipContainer
* @return array
*/
public function getProductsForSelect(bool $skipContainer = false): array
{
return $this->getEntityManager()->createQuery('SELECT PARTIAL p.{id, code, name, cacheDescription, inStock, available}, PARTIAL v.{id, name}, PARTIAL t.{id,title}
FROM AtechNetCoreBundle:Product p
LEFT JOIN p.type t
LEFT JOIN p.vendor v
WHERE p.archived = 0'
. ($skipContainer ? ' AND p.container = 0' : ''))->getResult();
}
/**
* TEST_GROUP -g ProductRepository_getProductsForStockReviewSelect
* @param string $searchText
* @param int $limitResult
* @return array
*/
public function getProductsForStockReviewSelect(string $searchText, int $limitResult): array
{
$query = $this->getEntityManager()->createQuery('SELECT PARTIAL p.{id, code, name, cacheDescription, inStock, available}, PARTIAL v.{id, name}, PARTIAL t.{id,title}
FROM AtechNetCoreBundle:Product p
LEFT JOIN p.type t
LEFT JOIN p.vendor v
WHERE p.archived = 0 AND p.inStock > 0'
. ($searchText ? " AND CONCAT(p.code, ' ', t.title, ' ', v.name, ' ', p.name, ' ', p.cacheDescription) LIKE '%" . $searchText . "%'" : ''));
if ($limitResult) {
$query->setMaxResults($limitResult);
}
return $query->getResult();
}
/**
* TEST_GROUP -g ProductRepository_getProductsForCouponSelect
* @param string|null $searchText
* @param int $limitResult
* @return array
*/
public function getProductsForCouponSelect(?string $searchText = null, int $limitResult = 0): array
{
$query = $this->getEntityManager()->createQuery(
"SELECT PARTIAL p.{id, code, name, cacheDescription, inStock, available}, PARTIAL v.{id, name}, PARTIAL t.{id,title}
FROM AtechNetCoreBundle:Product p
LEFT JOIN p.type t
LEFT JOIN p.vendor v
WHERE p.archived = 0 AND p.inStock > 0"
. (!is_null($searchText) ? " AND CONCAT(p.code, ' ', t.title, ' ', v.name, ' ', p.name, ' ', p.cacheDescription) LIKE '%" . $searchText . "%'" : "")
);
if ($limitResult > 0) {
$query->setMaxResults($limitResult);
}
return $query->getResult();
}
/**
* TEST_GROUP -g ProductRepository_getProductsForAjaxSelect
* @param bool $count
* @param string $search
* @param string $limit
* @param string $offset
* @param array $filter
* @return int|array
* @throws NoResultException
* @throws NonUniqueResultException
*/
public function getProductsForAjaxSelect(bool $count, string $search = '', string $limit = '', string $offset = '', array $filter = [])
{
$products = $this->getEntityManager()->createQueryBuilder()
->select($count ? 'count(p)' : 'p')
->from(Product::class, 'p')
->join('p.type', 't')
->join('p.vendor', 'v')
->where('p.archived = 0');
if ($search) {
$expressionWhere = '';
$expressions = $hits = [];
// Split search string
$expression = strtok($search, ' ');
while ($expression !== false) {
$expressions[] = trim($expression);
$expression = strtok(' ');
}
if (count($expressions) > 1) {
foreach ($expressions as $expression) {
$hits[] = "( CASE WHEN ( p.cacheDescription LIKE '%$expression%' OR p.name LIKE '%$expression%' OR v.name LIKE '%$expression%' OR t.title LIKE '%$expression%' ) THEN 1 ELSE 0 END )";
}
$expressionWhere = " OR ( " . implode(' + ', $hits) . " ) >= " . count($expressions);
}
$products->andWhere("p.code LIKE '%" . $search . "%' OR t.title LIKE '%" . $search . "%' OR v.name LIKE'%" . $search . "%' OR p.name LIKE '%" . $search . "%' OR p.cacheDescription LIKE '%" . $search . "%' OR CONCAT(v.name, ' ', t.title, ' ', p.name) LIKE '%" . $search . "%' OR CONCAT(v.name, ' ', p.name, ' ', t.title) LIKE '%" . $search . "%'" . $expressionWhere);
}
if (isset($filter['filterOnlyAvailable']) && (int)$filter['filterOnlyAvailable'] == 1) {
$products->andWhere("p.available > 0");
}
if (!empty($filter['filterProductType'])) {
$products->andWhere('t.id = :filterProductType')
->setParameter('filterProductType', (int)$filter['filterProductType']);
}
if ($limit && !$count) {
$products->setMaxResults($limit);
}
if ($offset && !$count) {
$products->setFirstResult($offset);
}
return $count ? $products->getQuery()->getSingleScalarResult() : $products->getQuery()->getResult();
}
/**
* @param bool $count
* @param string $search
* @param string $limit
* @param string $offset
* @param array $filter
* @return int|array
* @throws NoResultException
* @throws NonUniqueResultException
*/
public function getProductsV2ForAjaxSelect(bool $count, string $search = '', string $limit = '', string $offset = '', array $filter = [])
{
$products = $this->getEntityManager()->createQueryBuilder()
->select($count ? 'count(p)' : 'p')
->from(Product::class, 'p')
->join('p.productModel', 'pm')
->join('pm.type', 't')
->join('pm.vendor', 'v')
->where('p.archived = 0');
if ($search) {
$expressionWhere = '';
$expressions = $hits = [];
// Split search string
$expression = strtok($search, ' ');
while ($expression !== false) {
$expressions[] = trim($expression);
$expression = strtok(' ');
}
if (count($expressions) > 1) {
foreach ($expressions as $expression) {
$hits[] = "( CASE WHEN ( p.cacheDescription LIKE '%$expression%' OR p.name LIKE '%$expression%' OR v.name LIKE '%$expression%' OR t.title LIKE '%$expression%' ) THEN 1 ELSE 0 END )";
}
$expressionWhere = " OR ( " . implode(' + ', $hits) . " ) >= " . count($expressions);
}
$products->andWhere("p.code LIKE '%" . $search . "%' OR t.title LIKE '%" . $search . "%' OR v.name LIKE'%" . $search . "%' OR p.name LIKE '%" . $search . "%' OR p.cacheDescription LIKE '%" . $search . "%' OR CONCAT(v.name, ' ', t.title, ' ', p.name) LIKE '%" . $search . "%' OR CONCAT(v.name, ' ', p.name, ' ', t.title) LIKE '%" . $search . "%'" . $expressionWhere);
}
if (isset($filter['filterOnlyAvailable']) && (int)$filter['filterOnlyAvailable'] == 1) {
$products->andWhere("p.available > 0");
}
if (!empty($filter['filterProductModelId'])) {
$products->andWhere('pm.id = :filterProductModelId')
->setParameter('filterProductModelId', (int)$filter['filterProductModelId']);
}
if ($limit && !$count) {
$products->setMaxResults($limit);
}
if ($offset && !$count) {
$products->setFirstResult($offset);
}
return $count ? $products->getQuery()->getSingleScalarResult() : $products->getQuery()->getResult();
}
/**
* @param ProductType $productType
* @param int|null $quality
* @param DateTime $dateFrom
* @param DateTime $dateTo
* @return mixed
*/
public function getSumOfSoldProductsByProductTypeAndQuality(ProductType $productType, DateTime $dateFrom, DateTime $dateTo, $quality = null)
{
$params = [
'dateFrom' => $dateFrom,
'dateTo' => $dateTo,
'type' => $productType,
'outMovementBasic' => StockMovement::SUB_TYPE_OUT_BASIC,
];
$additionalWhere = '';
if (!is_null($quality) && is_numeric($quality)) {
$additionalWhere = 'AND p.qualityStatus = :quality';
$params['quality'] = $quality;
}
return $this->getEntityManager()->createQuery("SELECT SUM(smi.quantity) AS sumOfProducts
FROM AtechNetCoreBundle:StockMovement sm
LEFT JOIN sm.movementItems smi
LEFT JOIN smi.product p
LEFT JOIN p.type pt
WHERE sm.created > :dateFrom
AND sm.created < :dateTo
AND sm.subType = :outMovementBasic
AND pt = :type
" . $additionalWhere)
->setParameters($params)
->setMaxResults(1)
->getResult()[0]['sumOfProducts'];
}
/**
* @param ProductType $productType
* @param int|null $quality
* @return mixed
*/
public function getSumOfInStockProductsByProductTypeAndQuality(ProductType $productType, int $quality = null)
{
$params = [
'type' => $productType,
];
$additionalWhere = '';
if (!is_null($quality)) {
$additionalWhere = 'AND p.qualityStatus = :quality';
$params['quality'] = $quality;
}
return $this->getEntityManager()->createQuery('SELECT SUM(p.inStock) AS sumOfProducts
FROM AtechNetCoreBundle:Product p
LEFT JOIN p.type pt
WHERE pt = :type AND p.inStock > 0
' . $additionalWhere)
->setParameters($params)
->setMaxResults(1)
->getResult()[0]['sumOfProducts'];
}
/**
* @param ProductMeta $productMeta
* @return float
*/
public function calculateSuggestedPrice(ProductMeta $productMeta)
{
$product = $productMeta->getProduct();
$productTypeId = $product->getType()->getId();
$sellingPrice = $productMeta->isDiscounted() ? $productMeta->getDiscount() : $productMeta->getPrice();
if ($productTypeId == 52 || $productTypeId == 60) { // PC or NB
$suggestedPrice = $sellingPrice < 150 ? $sellingPrice + 40 : $sellingPrice + 50;
} elseif ($productTypeId == 44) { // Monitor
$suggestedPrice = $sellingPrice < 60 ? $sellingPrice + 10 : $sellingPrice + 15;
} else {
$suggestedPrice = $sellingPrice < 10 ? $sellingPrice + 10 : $sellingPrice + 15;
}
return $suggestedPrice;
}
/**
* @param Product $product
* @return int|string
* @throws Exception
*/
public function getAdjustedAverageBuyingPrice(Product $product)
{
$averageBuyingPrice = 0;
$itemCount = 0;
$itemPrices = 0;
if ($product->isScannable()) {
/**
* @var StockItem $stockItem
*/
$stockItems = $this->getAvailableStockItems($product);
if (count($stockItems) > 0) {
foreach ($stockItems as $stockItem) {
$itemPrices += $stockItem->getPurchasePriceCalculated();
$itemCount++;
}
if ($itemPrices > 0 && $itemCount > 0) {
$averageBuyingPrice = number_format($itemPrices / $itemCount, 2, '.', '');
}
} else {
$lastBuyingPrice = $this->getLastPrice($product->getId());
if (isset($lastBuyingPrice[$product->getId()]['last_price'])) {
$averageBuyingPrice = $lastBuyingPrice[$product->getId()]['last_price'];
}
}
}
return $averageBuyingPrice;
}
/**
* @param Product $product
* @return float|int
* @throws Exception
*/
public function getNotScannableAverageBuyingPrice(Product $product)
{
if ($product->isScannableWithType()) {
return 0;
}
$totalValue = $totalQuantity = 0;
$availableQuantity = $product->getAvailable();
if ($availableQuantity > 0) {
// ponytail: scalar stream instead of $product->getStockMovementItems() — that hydrated the
// product's whole movement history (100k+ rows for cables/RAM/SSD, plus one StockMovement
// entity each) only to read the newest few IN rows. Streams rows, breaks early, no entities.
$rows = $this->getEntityManager()
->createQuery(
'SELECT smi.quantity AS quantity, smi.price AS price, sm.quantity AS movementQuantity,
sm.extraPrice AS extraPrice, sm.exchangeRate AS exchangeRate
FROM AtechNetCoreBundle:StockMovementItem smi
JOIN smi.movement sm
WHERE smi.product = :product AND sm.type = :typeIn
ORDER BY smi.created DESC'
)
->setParameter('product', $product)
->setParameter('typeIn', StockMovement::TYPE_IN)
->toIterable([], Query::HYDRATE_SCALAR);
foreach ($rows as $row) {
$currentQuantity = (int)$row['quantity'];
if ($currentQuantity <= $availableQuantity) {
$quantityForPriceCalculation = $currentQuantity;
} else {
$quantityForPriceCalculation = $availableQuantity;
}
$totalQuantity = $totalQuantity + $quantityForPriceCalculation;
$exchangeRate = $row['exchangeRate'];
$pricePerItem = StockMovementItem::calculateTotalPricePerItem(
(float)$row['price'],
$currentQuantity,
(float)$row['extraPrice'],
(int)$row['movementQuantity']
);
if ($exchangeRate != 1 && $pricePerItem > 0) {
$pricePerItem = (float)number_format($pricePerItem / $exchangeRate, 2, '.', '');
}
$totalValue = $totalValue + ($quantityForPriceCalculation * $pricePerItem);
$availableQuantity = $availableQuantity - $quantityForPriceCalculation;
if ($availableQuantity <= 0) {
break;
}
}
} else {
if ($product->getLastPrice() > 0) {
return $product->getLastPrice();
} else {
return $this->getLastPrice($product->getId())[$product->getId()]['last_price'];
}
}
if ($totalValue == 0 || $totalQuantity == 0) {
return 0;
}
return (float)number_format($totalValue / $totalQuantity, 2, '.', '');
}
/**
* @param Product $product
* @return string
*/
public function getProductKeyboardLanguageSetting(Product $product): string
{
$keyboardLanguage = '-';
if ($product->getType()->getId() == 52) {
$entityManager = $this->getEntityManager();
$keyboardLanguageProductAttribute = $entityManager->createQuery("SELECT pa
FROM AtechNetCoreBundle:ProductAttribute pa
LEFT JOIN pa.option o
LEFT JOIN o.attribute a
WHERE a.id = :keyboardLanguageAttributeId AND pa.product = :product")
->setParameters([
'keyboardLanguageAttributeId' => 94,
'product' => $product,
])
->setMaxResults(1)
->getResult();
if (isset($keyboardLanguageProductAttribute[0])) {
$keyboardLanguage = $keyboardLanguageProductAttribute[0]->getOption()->getTitle();
} else {
foreach ($this->getAvailableStockItems($product) as $si) {
if ($keyboardLanguage != '-') {
continue;
}
if (!is_null($si->getKeyboardLanguage()) && $si->getKeyboardLanguage() != '') {
$keyboardLanguage = $si->getKeyboardLanguage();
}
}
}
}
return $keyboardLanguage;
}
/**
* @param Product $product
* @param ProductType $productType
* @param User $creator
* @param false $asContainer
* @param bool $preserveProductSettings
* @param bool $duplicatePhotos
* @param bool $withConnectedProducts
* @return Product
* @throws NotSupported
*/
public function duplicateProduct(Product $product, ProductType $productType, User $creator, bool $asContainer = false, bool $preserveProductSettings = false, bool $duplicatePhotos = true, bool $withConnectedProducts = false)
{
$entityManager = $this->getEntityManager();
$album = new Album();
$entityManager->persist($album);
$entityManager->flush();
if ($duplicatePhotos) {
$photos = $product?->getAlbum()?->getPhotos() ?? [];
$i = 1;
foreach ($photos as $photo) {
$newPhoto = new Photo();
$newPhoto->setAlbum($album);
$newPhoto->setSortIndex($i);
$newPhoto->setOriginalName($photo->getOriginalName());
$newPhoto->setContexts($photo->getContexts());
$newPhoto->setMimeType($photo->getMimeType());
$entityManager->persist($newPhoto);
$entityManager->flush();
if (file_exists($photo->getWebPath())) {
copy($photo->getWebPath(), $newPhoto->getWebPath());
$thumb_ends = ['eshop'];
foreach ($thumb_ends as $thumb_end) {
copy(
$photo->getUd() . 'gen/' . $photo->buildFn($thumb_end),
$newPhoto->getUd() . 'gen/' . $newPhoto->buildFn($thumb_end)
);
}
} else {
// Cloudflare
$cloudflare = $this->getCloudflareImage();
// Download
$cloudflare->downloadPhoto($photo, $newPhoto);
}
$i++;
// Skip Missing Photo
if (!file_exists($newPhoto->getAbsolutePath())) {
continue;
}
// Upload Photo to Cloudflare
$cloudflare = $this->getCloudflareImage();
// JPEG
$cloudflare->uploadPhoto($newPhoto);
}
$entityManager->flush();
}
$product_duplicate = new Product();
$product_duplicate->setVendor($product->getVendor());
$product_duplicate->setType($productType);
$product_duplicate->setAlbum($album);
$product_duplicate->setCreatedBy($creator);
$product_duplicate->setUpdatedBy($creator);
$product_duplicate->setCode($this->generateProductCode($productType));
$product_duplicate->setName(str_replace(' - NOT SCANNABLE', '', $product->getName()));
$product_duplicate->setWarranty($product->getWarranty());
$product_duplicate->setPartNumber($product->getPartNumber());
$product_duplicate->setEan('');
$product_duplicate->setGtin('');
$product_duplicate->setAmazonCode('');
$product_duplicate->setUnit($product->getUnit());
$product_duplicate->setLength($product->getLength());
$product_duplicate->setWidth($product->getWidth());
$product_duplicate->setHeight($product->getHeight());
$product_duplicate->setWeight($product->getWeight());
$product_duplicate->setBadges($product->getBadges());
$product_duplicate->setDetail($product->getDetail());
$product_duplicate->setOutOfStockStatus($product->getOutOfStockStatus());
$product_duplicate->setQualityStatus($product->getQualityStatus());
$product_duplicate->setArchived(false);
$product_duplicate->setScannable($product->isScannable());
$product_duplicate->setMarginLevel(Product::MARGIN_LEVEL_E);
if ($withConnectedProducts) {
$connectedProductModels = $product->getConnectedProductModels();
foreach ($connectedProductModels as $connectedProductModel) {
$connectedProductModel->addConnectedProduct($product_duplicate);
}
}
if ($preserveProductSettings) {
$product_duplicate->setStockPosition($product->getStockPosition());
} else {
$product_duplicate->setStockPosition(null);
}
$entityManager->persist($product_duplicate);
$entityManager->flush();
/**
* Copy ProductAttributes
*/
$product_attributes = $product->getProductAttributes();
foreach ($product_attributes as $pa) {
$pa_copy = new ProductAttribute();
$pa_copy->setProduct($product_duplicate);
$pa_copy->setOption($pa->getOption());
$pa_copy->setUsed($pa->isUsed());
$pa_copy->setMultiplier($pa->getMultiplier());
$pa_copy->setAlways($pa->isAlways());
$entityManager->persist($pa_copy);
}
$entityManager->flush();
/**
* Copy Product Metas
*/
$product_metas = $product->getMetas();
foreach ($product_metas as $pm) {
$pm_copy = new ProductMeta();
$pm_copy->setDomain($pm->getDomain());
$pm_copy->setProduct($product_duplicate);
if ($preserveProductSettings) {
$pm_copy->setPrice($pm->getPrice());
$pm_copy->setDiscount($pm->getDiscount());
$pm_copy->setSuggestedPrice($pm->getSuggestedPrice());
$pm_copy->setActive($pm->isActive());
$pm_copy->setListed($pm->isListed());
$pm_copy->setDiscounted($pm->isDiscounted());
} else {
$pm_copy->setPrice(0);
$pm_copy->setDiscount(0);
$pm_copy->setSuggestedPrice(0);
$pm_copy->setActive(false);
$pm_copy->setListed(false);
$pm_copy->setDiscounted(false);
}
$pm_copy->setVat($pm->getVat());
$pm_copy->setDetail(null);
$pm_copy->setWarranty($pm->getWarranty());
$pm_copy->setBazaar($pm->isBazaar());
$pm_copy->setBestSeller($pm->isBestSeller());
$pm_copy->setReLenovo($pm->isReLenovo());
$pm_copy->setHpRenew($pm->isHpRenew());
$pm_copy->setOrderable($pm->isOrderable());
$pm_copy->setSpecialPromotion($pm->isSpecialPromotion());
$entityManager->persist($pm_copy);
}
/**
* Copy Product Containers
*/
if ($asContainer) {
$product_duplicate->setContainer(true);
$product_duplicate->setScannable(false);
foreach ($product->getProductContainers() as $pc) {
$pc_copy = new ProductContainer();
$pc_copy->setProduct($product_duplicate);
$pc_copy->setChild($pc->getChild());
$pc_copy->setProductTag($pc->getProductTag());
$pc_copy->setMultiplier($pc->getMultiplier());
$entityManager->persist($pc_copy);
$product_duplicate->getProductContainers()->add($pc_copy);
foreach ($pc->getMetas() as $pcm) {
$pcm_copy = new ProductContainerMeta();
$pcm_copy->setPrice($pcm->getPrice());
$pcm_copy->setDiscount($pcm->getDiscount());
$pcm_copy->setDomain($pcm->getDomain());
$pcm_copy->setProductContainer($pc_copy);
$entityManager->persist($pcm_copy);
$pc_copy->getMetas()->add($pcm_copy);
}
}
}
$entityManager->flush();
return $product_duplicate;
}
/**
* @param ProductType $productType
* @return string
* @throws NotSupported
*/
public function generateProductCode(ProductType $productType): string
{
$products = $this->getEntityManager()->getRepository(Product::class)->findBy(
[
'type' => $productType,
],
[
'created' => 'DESC',
'code' => 'DESC',
],
1
);
if (count($products) > 0) {
/**
* @var Product $product
*/
$product = $products[array_keys($products)[0]];
$codeWithoutTypeCode = (int)substr($product->getCode(), strlen($product->getType()->getCode())) + 1;
} else {
$codeWithoutTypeCode = 1;
}
return $productType->getCode() . sprintf("%'.04d", ((int)$codeWithoutTypeCode));
}
/**
* @param ProductType $productType
*
* @return string
* @throws NonUniqueResultException
*/
public function generateProductCodeForV2Models(ProductType $productType): string
{
$entityManager = $this->getEntityManager();
$qb = $entityManager->createQueryBuilder();
$qb->select('p.code')
->from(Product::class, 'p')
->join('p.productModel', 'pm')
->where(
$qb->expr()->orX(
'pm.type = :type',
'p.type = :type'
)
)->setParameter('type', $productType)
->orderBy('p.created', 'DESC')
->addOrderBy('p.id', 'DESC')
->setMaxResults(1);
$productCode = $qb->getQuery()->getOneOrNullResult();
if ($productCode) {
$codeWithoutTypeCode = (int)substr($productCode['code'], strlen($productType->getCode())) + 1;
} else {
$codeWithoutTypeCode = 1;
}
return $productType->getCode() . str_pad($codeWithoutTypeCode, 4, '0', STR_PAD_LEFT);
}
/**
* @param Product $product
* @param false $flushEntityManager
* @return bool
* @throws Exception
*/
public function updateProductStockPosition(Product $product, bool $flushEntityManager = false): bool
{
if (!$product->isScannableWithType()) {
return true;
}
$entityManager = $this->getEntityManager();
$connection = $entityManager->getConnection();
$originalStockPositions = explode(',', (string)$product->getStockPosition());
$stockItems = $connection->executeQuery(
"SELECT si.id,
COALESCE((
SELECT sip.stock_position_id
FROM stock_items_position sip
WHERE sip.stock_item_id = si.id
ORDER BY sip.created DESC, sip.id DESC
LIMIT 1
), '') AS position
FROM stock_movement_item smi
INNER JOIN stock_movement_items_stock_items smisi ON smisi.stockmovementitem_id = smi.id
INNER JOIN stock_items si ON si.id = smisi.stockitem_id
WHERE smi.product_id = ?
AND si.in_stock = 1
AND smi.id = (
SELECT smi2.id
FROM stock_movement_item smi2
INNER JOIN stock_movement_items_stock_items smisi2 ON smisi2.stockmovementitem_id = smi2.id
WHERE smisi2.stockitem_id = si.id
ORDER BY smi2.created DESC, smi2.id DESC
LIMIT 1
)",
[
$product->getId()
]
);
$positions = [];
foreach ($stockItems->fetchAllAssociative() as $row) {
if (!in_array($row['position'], $positions) && trim($row['position']) != '') {
$positions[] = $row['position'];
}
}
// Sort
asort($positions, SORT_NUMERIC);
asort($originalStockPositions, SORT_NUMERIC);
// Compare
$newStockPositions = implode(',', $positions);
$originalStockPositions = implode(',', $originalStockPositions);
if ($originalStockPositions != $newStockPositions) {
$product->setStockPosition($newStockPositions);
if ($flushEntityManager) {
$entityManager->flush();
}
}
return true;
}
/**
* @param array $categoryPageIds
* @return string
* @throws Exception
* @throws \Doctrine\DBAL\Driver\Exception
*/
public function getProductTypeIds(array $categoryPageIds): string
{
$whereIn = implode(',', $categoryPageIds);
$connection = $this->getEntityManager()->getConnection();
$sql = '
SELECT cppt.product_type_id AS productTypeId
FROM category_pages_product_types AS cppt
WHERE cppt.category_page_id IN(' . $whereIn . ')
GROUP BY cppt.product_type_id';
$stmt = $connection->prepare($sql);
$result = $stmt->execute();
$productTypeIds = $result->fetchAllAssociative();
$ret = [];
foreach ($productTypeIds as $key => $value) {
array_push($ret, $value['productTypeId']);
}
return implode(',', $ret);
}
/**
* @param int $cacheLifeTime
* @param bool $forceRegereration
* @return array
* @throws \Psr\Cache\InvalidArgumentException
*/
public function getLastPricesFromCache(int $cacheLifeTime = 172800, bool $forceRegereration = false): array
{
$lastPricesCacheKey = self::LAST_AVG_PRICE_CACHE_KEY;
$cacheItem = $this->cache->getItem($lastPricesCacheKey);
if ($cacheItem->isHit() && !$forceRegereration) {
$lastPrices = $cacheItem->get();
} else {
$lastPrices = [];
$cacheItem->set($lastPrices);
$cacheItem->expiresAfter($cacheLifeTime);
$this->cache->save($cacheItem);
}
return $lastPrices;
}
/**
* @param array $productIds
* @return array|null
* @throws Exception
* @throws \Psr\Cache\InvalidArgumentException
*/
public function updateLastProductPricesInCache(array $productIds = []): ?array
{
if (empty($productIds)) {
return null;
}
$lastPrices = $this->getLastPricesFromCache();
foreach ($this->getLastPrices($productIds) as $productId => $productPrices) {
$lastPrices[$productId] = $productPrices;
}
$cacheItem = $this->cache->getItem(self::LAST_AVG_PRICE_CACHE_KEY);
$cacheItem->set($lastPrices);
$cacheItem->expiresAfter(345600);
$this->cache->save($cacheItem);
return $lastPrices;
}
/**
* @param ProductMeta $productMeta
* @param float $priceOld
* @param float $priceNew
* @param User|null $user
* @return void
* @throws NotSupported
* @throws ORMException
* @throws \Doctrine\ORM\ORMException
*/
public function addPriceLogEntry(ProductMeta $productMeta, float $priceOld, float $priceNew, User $user = null): void
{
$entityManager = $this->getEntityManager();
$changelog = new ProductPriceLog();
$changelog->setDomain($productMeta->getDomain());
$changelog->setProduct($productMeta->getProduct());
if ($user) {
$changelog->setCreatedBy($user);
} else {
$centralisUser = $entityManager->getRepository(User::class)->find(User::CENTRALIS_USER_ID);
$changelog->setCreatedBy($centralisUser);
}
$changelog->setOldPrice($priceOld);
$changelog->setNewPrice($priceNew);
$entityManager->persist($changelog);
}
/**
* @var Product $product
* @var Domain $domain
* @var DateTime $dateTimeValue
*/
public function getPriceFromPriceLogsByGivenDate(Product $product, Domain $domain, DateTime $dateTimeValue)
{
/**
* @var ProductPriceLog $firstPriceLogBeforeGivenDate
*/
$firstPriceLogBeforeGivenDate = $product->getPriceLogs()->filter(
function (ProductPriceLog $ppl) use ($domain, $dateTimeValue) {
return $ppl->getDomain() == $domain && $ppl->getCreated() < $dateTimeValue;
}
)->last();
return $firstPriceLogBeforeGivenDate ? $firstPriceLogBeforeGivenDate->getNewPrice() : 0;
}
/**
* Entity Product->getCommissions() with custom query
* @param int $productId
* @param int $limit
* @return float|int|mixed|string
*/
public function getLimitedCommissions(int $productId, int $limit = 0)
{
$entityManager = $this->getEntityManager();
$results = $entityManager->createQuery(
"SELECT cm
FROM AtechNetCoreBundle:ProductCommission cm
LEFT JOIN cm.product pr
WHERE pr.id = $productId AND cm.status IN (" . ProductCommission::Status_AtPartner . ", " . ProductCommission::Status_Sold . ") AND (cm.invoiceNumber = '' OR cm.invoiceNumber IS NULL)"
);
if ($limit > 0) {
$results->setMaxResults($limit);
}
return $results->getResult();
}
/**
* Entity Product->getReservations() with custom query
* @param int $productId
* @param int $limit
* @return array|\mixed[][]
* @throws Exception
* @throws \Doctrine\DBAL\Driver\Exception
*/
public function getLimitedReservations(int $productId, int $limit = 0)
{
$connection = $this->getEntityManager()->getConnection();
$stockMovementType = StockMovement::TYPE_OUT;
$query = "SELECT o.id, oi.quantity
FROM orders__items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE oi.product_id = $productId AND (o.invoice_code IS NULL OR o.invoice_code = '' OR o.invoice_code = 0)
AND (SELECT sm.type
FROM stock_movements sm
WHERE sm.order_id = o.id
ORDER BY sm.created_by DESC
LIMIT 1) = $stockMovementType
ORDER BY o.created DESC
";
if ($limit > 0) {
$query .= " LIMIT $limit";
}
$orders = $connection->executeQuery($query);
return $orders->fetchAllAssociative();
}
/**
* Entity Product->getReservationsCount() with custom query
* @param int $productId
* @return int|null
* @throws Exception
* @throws \Doctrine\DBAL\Driver\Exception
*/
public function getReservationsCount(int $productId): ?int
{
$connection = $this->getEntityManager()->getConnection();
$stockMovementType = StockMovement::TYPE_OUT;
$query = "SELECT SUM(oi.quantity) as total
FROM orders__items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE oi.product_id = $productId AND o.invoice_code IN('', null)
AND (SELECT sm.type
FROM stock_movements sm
WHERE sm.order_id = o.id
ORDER BY sm.created_by DESC
LIMIT 1) = $stockMovementType
";
$orders = $connection->executeQuery($query);
return $orders->fetchAllAssociative()[0]['total'];
}
/**
* Entity Product->getSold() with custom query
* @param int $productId
* @param int $limit
* @return mixed[]|\mixed[][]
* @throws Exception
*/
public function getLimitedSold(int $productId, int $limit = 0): array
{
$connection = $this->getEntityManager()->getConnection();
$stockMovementType = StockMovement::TYPE_OUT;
$query = "SELECT o.*
FROM orders__items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE oi.product_id = $productId
AND (SELECT sm.type
FROM stock_movements sm
WHERE sm.order_id = o.id
ORDER BY sm.created_by DESC
LIMIT 1) = $stockMovementType
ORDER BY o.created DESC
";
if ($limit > 0) {
$query .= " LIMIT $limit";
}
$orders = $connection->executeQuery($query);
return $orders->fetchAllAssociative();
}
/**
* Entity Product->getSoldCount() with custom query
* @param int $productId
* @return int
* @throws Exception
*/
public function getSoldCount(int $productId): ?int
{
$connection = $this->getEntityManager()->getConnection();
$stockMovementType = StockMovement::TYPE_OUT;
$query = "SELECT SUM(oi.quantity) as total
FROM orders__items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE oi.product_id = $productId
AND (SELECT sm.type
FROM stock_movements sm
WHERE sm.order_id = o.id
ORDER BY sm.created_by DESC
LIMIT 1) = $stockMovementType
";
$orders = $connection->executeQuery($query);
return $orders->fetchAllAssociative()[0]['total'];
}
/**
* @return array
*/
public function incomingProductsTable(): array
{
$entityManager = $this->getEntityManager();
$thisYear = (new DateTime())->format('Y');
return $entityManager->createQuery("SELECT
pt.title AS productType,
sm.id AS stockMovementId,
d.shortTitle AS domain,
m.businessName AS Merchant,
mi.quantity AS quantityInMovement,
p.inStock AS allInStock,
(mi.price / mi.quantity) AS pricePerItem,
mi.price AS totalPrice,
sm.shipmentCode,
sm.note
FROM AtechNetCoreBundle:StockMovementItem mi
LEFT JOIN mi.movement sm
LEFT JOIN mi.product p
LEFT JOIN p.type pt
LEFT JOIN sm.stock s
LEFT JOIN sm.merchant m
LEFT JOIN m.domain d
WHERE p.name LIKE '%INCOMING @$thisYear%'
GROUP BY sm")
->getResult();
}
/**
* @param int $attributeId
* @return array
*/
public function getProductTypeListForAttribute(int $attributeId): ?array
{
$entityManager = $this->getEntityManager();
return $entityManager->createQuery("SELECT pt
FROM AtechNetCoreBundle:ProductType pt
INNER JOIN AtechNetCoreBundle:Product p WITH p.type = pt
INNER JOIN AtechNetCoreBundle:ProductAttribute pa WITH pa.product = p AND
IDENTITY(pa.option) IN(
SELECT ao.id
FROM AtechNetCoreBundle:AttributeOption ao
WHERE IDENTITY(ao.attribute) = $attributeId
)
GROUP BY pa.product
")
->getResult();
}
/**
* @param array $priceMonitorData
* @return array
*/
public function getPriceMonitorListForProduct(array $priceMonitorData): ?array
{
$entityManager = $this->getEntityManager();
$whereString = '';
if ($priceMonitorData['domainId']) {
$whereString .= 'IDENTITY(cn.domain) = ' . $priceMonitorData['domainId'];
}
if ($priceMonitorData['notified'] != 0) {
if ($whereString != '') {
$whereString .= ' AND ';
}
switch ($priceMonitorData['notified']) {
case '1':
$whereString .= 'cn.notified IS NOT NULL';
break;
case '2':
$whereString .= 'cn.notified IS NULL';
break;
}
}
if ($whereString != '') {
$whereString = "WHERE $whereString";
}
$orderByColumn = $priceMonitorData['order'][0] ?? '';
$orderByType = strlen($priceMonitorData['order']) == 2 ? ($priceMonitorData['order'][1] === 'A' ? 'A' : 'D') : 'D';
/**
* Array for order by fields
*/
$availableOrderBy = ['C', 'N', 'D', 'S', 'P', 'F', 'I'];
// Build order by sql statement
if ($orderByColumn != '' && in_array($orderByColumn, $availableOrderBy)) {
if ($orderByColumn == 'C') {
$orderBySql = ' ORDER BY cnp.code';
} elseif ($orderByColumn == 'N') {
$orderBySql = " ORDER BY CONCAT(cnpt.title, ' ', cnpv.name, ' ', cnp.name)";
} elseif ($orderByColumn == 'S') {
$orderBySql = ' ORDER BY cn.sourcePrice';
} elseif ($orderByColumn == 'P') {
$orderBySql = ' ORDER BY cn.price';
} elseif ($orderByColumn == 'F') {
$orderBySql = ' ORDER BY pm.currentVatPrice';
} elseif ($orderByColumn == 'I') {
$orderBySql = ' ORDER BY cnp.inStock';
} else {
$orderBySql = ' ORDER BY cn.created';
}
if ($orderByType == 'D') {
$orderBySql .= ' DESC';
}
} else {
$orderBySql = ' ORDER BY cn.created DESC';
}
return $entityManager->createQuery('SELECT cn
FROM AtechNetCoreBundle:CustomerNotification cn
INNER JOIN AtechNetCoreBundle:Product p WITH cn.product = p AND IDENTITY(cn.product) = ' . $priceMonitorData['productId'] . "
$whereString
$orderBySql
")->getResult();
}
/**
* Get parts sections with categories for the parts page
* @param Domain $domain
* @param SeoUrlHelper $seoUrlHelper
* @param ProductHelper $productHelper
* @param MultiStore $multiStore
* @return array
*/
public function getPartsSections(Domain $domain, SeoUrlHelper $seoUrlHelper, ProductHelper $productHelper, MultiStore $multiStore): array
{
$sections = [];
$translator = $this->translator;
switch ($domain->getId()) {
case Domain::DOMAIN_ID_RT_SK:
case Domain::DOMAIN_ID_WS_SK:
$sections = [
[
// Parts
'title' => $translator->trans('Parts'), // Alkatrészek
'description' => $translator->trans('Parts description'),
'categoryId' => 447,
'categories' => [
[
// Motherboard
'subCategoryId' => 104,
'image' => 'furbify-komp-motherboard.jpg',
],
[
// Video card
'subCategoryId' => 36,
'image' => 'furbify-komp-videokartya-graficka-karta.jpg',
],
[
// Cable and reduction
'subCategoryId' => 227,
'image' => 'furbify-komp-kabel-adapter.jpg',
],
[
// Laptop memory (RAM)
'subCategoryId' => 25,
'image' => 'furbify-komp-laptop-notebook-memory.jpg',
],
[
// PC memory (RAM)
'subCategoryId' => 26,
'image' => 'furbify-komp-pc-memory.jpg',
],
[
// Optical disk drive
'subCategoryId' => 32,
'image' => 'furbify-komp-optical-drive.jpg',
],
[
// HDD / SSD
'subCategoryId' => 27,
'image' => 'furbify-komp-ssd.jpg',
],
[
// Processors
'subCategoryId' => 29,
'image' => 'furbify-komp-processor.jpg',
],
[
// Laptop display
'subCategoryId' => 34,
'image' => 'furbify-komp-laptop-display.jpg',
],
[
// Laptop keyboard
'subCategoryId' => 35,
'image' => 'furbify-komp-laptop-billentyuzet-notebook-klavesnica.jpg',
],
[
// Power supply
'subCategoryId' => 100,
'image' => 'furbify-komp-zdroj-tapegyseg.jpg',
],
[
// Notebook battery
'subCategoryId' => 234,
'image' => 'furbify-komp-notebook-akku.jpg',
],
[
// Monitor stand
'subCategoryId' => 366,
'image' => 'furbify-monitor-stand.jpg',
],
[
// Laptop adapter
'subCategoryId' => 369,
'image' => 'furbify-laptop-adapter.jpg',
], [
// Laptop touchpad
'subCategoryId' => 402,
'image' => 'furbify-laptop-touchpad.jpg',
], [
// Laptop top cover
'subCategoryId' => 403,
'image' => 'furbify-laptop-top-cover.jpg',
], [
// Laptop bottom cover
'subCategoryId' => 404,
'image' => 'furbify-laptop-bottom-cover.jpg',
], [
// Laptop bottom base cover
'subCategoryId' => 405,
'image' => 'furbify-laptop-bottom-base-cover.jpg',
], [
// Laptop internal base plate
'subCategoryId' => 406,
'image' => 'furbify-laptop-internal-base-plate.jpg',
], [
// Laptop palmrest
'subCategoryId' => 407,
'image' => 'furbify-laptop-palmrest.jpg',
], [
// Laptop display hinge
'subCategoryId' => 408,
'image' => 'furbify-laptop-display-hinge.jpg',
], [
// Laptop display hinge cover
'subCategoryId' => 409,
'image' => 'furbify-laptop-display-hinge-cover.jpg',
], [
// Laptop bezel
'subCategoryId' => 410,
'image' => 'furbify-laptop-bezel.jpg',
], [
// Laptop speaker
'subCategoryId' => 411,
'image' => 'furbify-laptop-speaker.jpg',
], [
// Laptop usb module
'subCategoryId' => 412,
'image' => 'furbify-laptop-usb-module.jpg',
], [
// Laptop cooling fan
'subCategoryId' => 413,
'image' => 'furbify-laptop-cooling-fan.jpg',
], [
// Laptop heatsing
'subCategoryId' => 414,
'image' => 'furbify-laptop-heatsing.jpg',
], [
// Laptop LVDS cable
'subCategoryId' => 415,
'image' => 'furbify-laptop-lvds-cable.jpg',
], [
// Laptop sata cable
'subCategoryId' => 416,
'image' => 'furbify-laptop-sata-cable.jpg',
], [
// Laptop flex cable
'subCategoryId' => 417,
'image' => 'furbify-laptop-flex-cable.jpg',
], [
// Laptop internal cable
'subCategoryId' => 418,
'image' => 'furbify-laptop-internal-cable.jpg',
], [
// PC internal cable
'subCategoryId' => 419,
'image' => 'furbify-pc-internal-cable.jpg',
], [
// PC heatsin
'subCategoryId' => 420,
'image' => 'furbify-pc-heatsin.jpg',
], [
// PC cooling fan
'subCategoryId' => 421,
'image' => 'furbify-pc-cooling-fan.jpg',
], [
// Sound card
'subCategoryId' => 541,
'image' => 'furbify-sound-card.png',
], [
// PC speaker
'subCategoryId' => 514,
'image' => 'furbify-pc-speaker.png',
], [
// PC-internal-part
'subCategoryId' => 560,
'image' => 'furbify-pc-internal-part.png',
], [
// PC-internal-module
'subCategoryId' => 553,
'image' => 'furbify-pc-internal-module.png',
], [
// PCI-express-card
'subCategoryId' => 544,
'image' => 'furbify-pci-express-card.png',
], [
// PC case
'subCategoryId' => 517,
'image' => 'furbify-pc-case.png',
], [
// Notebook-webcam
'subCategoryId' => 511,
'image' => 'furbify-notebook-webcam.png',
], [
// Notebook-other-cover
'subCategoryId' => 547,
'image' => 'furbify-notebook-other-cover.png',
], [
// Notebook_microphone
'subCategoryId' => 523,
'image' => 'furbify-notebook-microphone.png',
], [
// Notebook_internal_part
'subCategoryId' => 562,
'image' => 'furbify-notebook-internal-part.png',
], [
// Notebook_internal_module
'subCategoryId' => 551,
'image' => 'furbify-notebook-internal-module.png',
], [
// HDD_SSD_Caddy
'subCategoryId' => 505,
'image' => 'furbify-hdd-ssd-caddy.png',
], [
// Other product
'subCategoryId' => 362,
'image' => 'furbify-other-product.png', // (kwap-kwap)
],
],
],
];
break;
case Domain::DOMAIN_ID_RT_HU:
case Domain::DOMAIN_ID_WS_HU:
$sections = [
[
// Parts
'title' => $translator->trans('Parts'),
'description' => $translator->trans('Parts description'),
'categoryId' => 44,
'categories' => [
[
// Motherboard
'subCategoryId' => 105,
'image' => 'furbify-komp-motherboard.jpg',
],
[
// Video card
'subCategoryId' => 73,
'image' => 'furbify-komp-videokartya-graficka-karta.jpg',
],
[
// Cable and reduction
'subCategoryId' => 223,
'image' => 'furbify-komp-kabel-adapter.jpg',
],
[
// Laptop memory (RAM)
'subCategoryId' => 63,
'image' => 'furbify-komp-laptop-notebook-memory.jpg',
],
[
// PC memory (RAM)
'subCategoryId' => 64,
'image' => 'furbify-komp-pc-memory.jpg',
],
[
// Optical disk drive
'subCategoryId' => 70,
'image' => 'furbify-komp-optical-drive.jpg',
],
[
// HDD / SSD
'subCategoryId' => 65,
'image' => 'furbify-komp-ssd.jpg',
],
[
// Processors
'subCategoryId' => 67,
'image' => 'furbify-komp-processor.jpg',
],
[
// Laptop display
'subCategoryId' => 71,
'image' => 'furbify-komp-laptop-display.jpg',
],
[
// Laptop keyboard
'subCategoryId' => 72,
'image' => 'furbify-komp-laptop-billentyuzet-notebook-klavesnica.jpg',
],
[
// Power supply
'subCategoryId' => 101,
'image' => 'furbify-komp-zdroj-tapegyseg.jpg',
],
[
// Notebook battery
'subCategoryId' => 45,
'image' => 'furbify-komp-notebook-akku.jpg',
],
[
// Monitor stand
'subCategoryId' => 365,
'image' => 'furbify-monitor-stand.jpg',
],
[
// Laptop adapter
'subCategoryId' => 368,
'image' => 'furbify-laptop-adapter.jpg',
], [
// Laptop touchpad
'subCategoryId' => 382,
'image' => 'furbify-laptop-touchpad.jpg',
], [
// Laptop top cover
'subCategoryId' => 383,
'image' => 'furbify-laptop-top-cover.jpg',
], [
// Laptop bottom cover
'subCategoryId' => 384,
'image' => 'furbify-laptop-bottom-cover.jpg',
], [
// Laptop bottom base cover
'subCategoryId' => 385,
'image' => 'furbify-laptop-bottom-base-cover.jpg',
], [
// Laptop internal base plate
'subCategoryId' => 386,
'image' => 'furbify-laptop-internal-base-plate.jpg',
], [
// Laptop palmrest
'subCategoryId' => 387,
'image' => 'furbify-laptop-palmrest.jpg',
], [
// Laptop display hinge
'subCategoryId' => 388,
'image' => 'furbify-laptop-display-hinge.jpg',
], [
// Laptop display hinge cover
'subCategoryId' => 389,
'image' => 'furbify-laptop-display-hinge-cover.jpg',
], [
// Laptop bezel
'subCategoryId' => 390,
'image' => 'furbify-laptop-bezel.jpg',
], [
// Laptop speaker
'subCategoryId' => 391,
'image' => 'furbify-laptop-speaker.jpg',
], [
// Laptop usb module
'subCategoryId' => 392,
'image' => 'furbify-laptop-usb-module.jpg',
], [
// Laptop cooling fan
'subCategoryId' => 393,
'image' => 'furbify-laptop-cooling-fan.jpg',
], [
// Laptop heatsing
'subCategoryId' => 394,
'image' => 'furbify-laptop-heatsing.jpg',
], [
// Laptop LVDS cable
'subCategoryId' => 395,
'image' => 'furbify-laptop-lvds-cable.jpg',
], [
// Laptop sata cable
'subCategoryId' => 396,
'image' => 'furbify-laptop-sata-cable.jpg',
], [
// Laptop flex cable
'subCategoryId' => 397,
'image' => 'furbify-laptop-flex-cable.jpg',
], [
// Laptop internal cable
'subCategoryId' => 398,
'image' => 'furbify-laptop-internal-cable.jpg',
], [
// PC internal cable
'subCategoryId' => 399,
'image' => 'furbify-pc-internal-cable.jpg',
], [
// PC heatsin
'subCategoryId' => 400,
'image' => 'furbify-pc-heatsin.jpg',
], [
// PC cooling fan
'subCategoryId' => 401,
'image' => 'furbify-pc-cooling-fan.jpg',
], [
// Sound card
'subCategoryId' => 540,
'image' => 'furbify-sound-card.png',
], [
// PC speaker
'subCategoryId' => 513,
'image' => 'furbify-pc-speaker.png',
], [
// PC-internal-part
'subCategoryId' => 557,
'image' => 'furbify-pc-internal-part.png',
], [
// PC-internal-module
'subCategoryId' => 550,
'image' => 'furbify-pc-internal-module.png',
], [
// PCI-express-card
'subCategoryId' => 543,
'image' => 'furbify-pci-express-card.png',
], [
// PC case
'subCategoryId' => 516,
'image' => 'furbify-pc-case.png',
], [
// Notebook-webcam
'subCategoryId' => 510,
'image' => 'furbify-notebook-webcam.png',
], [
// Notebook-other-cover
'subCategoryId' => 546,
'image' => 'furbify-notebook-other-cover.png',
], [
// Notebook_microphone
'subCategoryId' => 522,
'image' => 'furbify-notebook-microphone.png',
], [
// Notebook_internal_part
'subCategoryId' => 555,
'image' => 'furbify-notebook-internal-part.png',
], [
// Notebook_internal_module
'subCategoryId' => 549,
'image' => 'furbify-notebook-internal-module.png',
], [
// HDD_SSD_Caddy
'subCategoryId' => 504,
'image' => 'furbify-hdd-ssd-caddy.png',
], [
// Other product
'subCategoryId' => 363,
'image' => 'furbify-other-product.png', // (kwap-kwap)
],
],
],
];
break;
case Domain::DOMAIN_ID_RT_CZ:
case Domain::DOMAIN_ID_WS_CZ:
$sections = [
[
// Parts
'title' => $translator->trans('Parts'),
'description' => $translator->trans('Parts description'),
'categoryId' => 446,
'categories' => [
[
// Motherboard
'subCategoryId' => 167,
'image' => 'furbify-komp-motherboard.jpg',
],
[
// Video card
'subCategoryId' => 147,
'image' => 'furbify-komp-videokartya-graficka-karta.jpg',
],
[
// Cable and reduction
'subCategoryId' => 232,
'image' => 'furbify-komp-kabel-adapter.jpg',
],
[
// Laptop memory (RAM)
'subCategoryId' => 137,
'image' => 'furbify-komp-laptop-notebook-memory.jpg',
],
[
// PC memory (RAM)
'subCategoryId' => 138,
'image' => 'furbify-komp-pc-memory.jpg',
],
[
// Optical disk drive
'subCategoryId' => 144,
'image' => 'furbify-komp-optical-drive.jpg',
],
[
// HDD / SSD
'subCategoryId' => 139,
'image' => 'furbify-komp-ssd.jpg',
],
[
// Processors
'subCategoryId' => 141,
'image' => 'furbify-komp-processor.jpg',
],
[
// Laptop display
'subCategoryId' => 145,
'image' => 'furbify-komp-laptop-display.jpg',
],
[
// Laptop keyboard
'subCategoryId' => 146,
'image' => 'furbify-komp-laptop-billentyuzet-notebook-klavesnica.jpg',
],
[
// Power supply
'subCategoryId' => 166,
'image' => 'furbify-komp-zdroj-tapegyseg.jpg',
],
[
// Notebook battery
'subCategoryId' => 119,
'image' => 'furbify-komp-notebook-akku.jpg',
],
[
// Monitor stand
'subCategoryId' => 367,
'image' => 'furbify-monitor-stand.jpg',
], [
// Laptop adapter
'subCategoryId' => 370,
'image' => 'furbify-laptop-adapter.jpg',
], [
// Laptop touchpad
'subCategoryId' => 422,
'image' => 'furbify-laptop-touchpad.jpg',
], [
// Laptop top cover
'subCategoryId' => 423,
'image' => 'furbify-laptop-top-cover.jpg',
], [
// Laptop bottom cover
'subCategoryId' => 424,
'image' => 'furbify-laptop-bottom-cover.jpg',
], [
// Laptop bottom base cover
'subCategoryId' => 425,
'image' => 'furbify-laptop-bottom-base-cover.jpg',
], [
// Laptop internal base plate
'subCategoryId' => 426,
'image' => 'furbify-laptop-internal-base-plate.jpg',
], [
// Laptop palmrest
'subCategoryId' => 427,
'image' => 'furbify-laptop-palmrest.jpg',
], [
// Laptop display hinge
'subCategoryId' => 428,
'image' => 'furbify-laptop-display-hinge.jpg',
], [
// Laptop display hinge cover
'subCategoryId' => 429,
'image' => 'furbify-laptop-display-hinge-cover.jpg',
], [
// Laptop bezel
'subCategoryId' => 430,
'image' => 'furbify-laptop-bezel.jpg',
], [
// Laptop speaker
'subCategoryId' => 431,
'image' => 'furbify-laptop-speaker.jpg',
], [
// Laptop usb module
'subCategoryId' => 432,
'image' => 'furbify-laptop-usb-module.jpg',
], [
// Laptop cooling fan
'subCategoryId' => 433,
'image' => 'furbify-laptop-cooling-fan.jpg',
], [
// Laptop heatsing
'subCategoryId' => 434,
'image' => 'furbify-laptop-heatsing.jpg',
], [
// Laptop LVDS cable
'subCategoryId' => 435,
'image' => 'furbify-laptop-lvds-cable.jpg',
], [
// Laptop sata cable
'subCategoryId' => 436,
'image' => 'furbify-laptop-sata-cable.jpg',
], [
// Laptop flex cable
'subCategoryId' => 437,
'image' => 'furbify-laptop-flex-cable.jpg',
], [
// Laptop internal cable
'subCategoryId' => 438,
'image' => 'furbify-laptop-internal-cable.jpg',
], [
// PC internal cable
'subCategoryId' => 439,
'image' => 'furbify-pc-internal-cable.jpg',
], [
// PC heatsin
'subCategoryId' => 440,
'image' => 'furbify-pc-heatsin.jpg',
], [
// PC cooling fan
'subCategoryId' => 441,
'image' => 'furbify-pc-cooling-fan.jpg',
], [
// Sound card
'subCategoryId' => 542,
'image' => 'furbify-sound-card.png',
], [
// PC speaker
'subCategoryId' => 515,
'image' => 'furbify-pc-speaker.png',
], [
// PC-internal-part
'subCategoryId' => 559,
'image' => 'furbify-pc-internal-part.png',
], [
// PC-internal-module
'subCategoryId' => 554,
'image' => 'furbify-pc-internal-module.png',
], [
// PCI-express-card
'subCategoryId' => 545,
'image' => 'furbify-pci-express-card.png',
], [
// PC case
'subCategoryId' => 518,
'image' => 'furbify-pc-case.png',
], [
// Notebook-webcam
'subCategoryId' => 512,
'image' => 'furbify-notebook-webcam.png',
], [
// Notebook-other-cover
'subCategoryId' => 548,
'image' => 'furbify-notebook-other-cover.png',
], [
// Notebook_microphone
'subCategoryId' => 524,
'image' => 'furbify-notebook-microphone.png',
], [
// Notebook_internal_part
'subCategoryId' => 561,
'image' => 'furbify-notebook-internal-part.png',
], [
// Notebook_internal_module
'subCategoryId' => 552,
'image' => 'furbify-notebook-internal-module.png',
], [
// HDD_SSD_Caddy
'subCategoryId' => 506,
'image' => 'furbify-hdd-ssd-caddy.png',
], [
// Other product
'subCategoryId' => 380,
'image' => 'furbify-other-product.png', // (kwap-kwap)
],
],
],
];
break;
case Domain::DOMAIN_ID_RT_EU:
case Domain::DOMAIN_ID_WS_EU:
$sections = [
[
// Parts
'title' => $translator->trans('Parts'), // Parts
'description' => $translator->trans('Parts description'),
'categoryId' => 458,
'categories' => [
[
// Motherboard
'subCategoryId' => 321,
'image' => 'furbify-komp-motherboard.jpg',
],
[
// Video card
'subCategoryId' => 302,
'image' => 'furbify-komp-videokartya-graficka-karta.jpg',
],
[
// Cable and reduction
'subCategoryId' => 336,
'image' => 'furbify-komp-kabel-adapter.jpg',
],
[
// Laptop memory (RAM)
'subCategoryId' => 292,
'image' => 'furbify-komp-laptop-notebook-memory.jpg',
],
[
// PC memory (RAM)
'subCategoryId' => 293,
'image' => 'furbify-komp-pc-memory.jpg',
],
[
// Optical disk drive
'subCategoryId' => 299,
'image' => 'furbify-komp-optical-drive.jpg',
],
[
// HDD / SSD
'subCategoryId' => 294,
'image' => 'furbify-komp-ssd.jpg',
],
[
// Processors
'subCategoryId' => 296,
'image' => 'furbify-komp-processor.jpg',
],
[
// Laptop display
'subCategoryId' => 300,
'image' => 'furbify-komp-laptop-display.jpg',
],
[
// Laptop keyboard
'subCategoryId' => 301,
'image' => 'furbify-komp-laptop-billentyuzet-notebook-klavesnica.jpg',
],
[
// Power supply
'subCategoryId' => 320,
'image' => 'furbify-komp-zdroj-tapegyseg.jpg',
],
[
// Notebook battery
'subCategoryId' => 274,
'image' => 'furbify-komp-notebook-akku.jpg',
],
[
// Monitor stand
'subCategoryId' => 465,
'image' => 'furbify-monitor-stand.jpg',
],
[
// Laptop adapter
'subCategoryId' => 466,
'image' => 'furbify-laptop-adapter.jpg',
], [
// Laptop touchpad
'subCategoryId' => 467,
'image' => 'furbify-laptop-touchpad.jpg',
], [
// Laptop top cover
'subCategoryId' => 468,
'image' => 'furbify-laptop-top-cover.jpg',
], [
// Laptop bottom cover
'subCategoryId' => 469,
'image' => 'furbify-laptop-bottom-cover.jpg',
], [
// Laptop bottom base cover
'subCategoryId' => 470,
'image' => 'furbify-laptop-bottom-base-cover.jpg',
], [
// Laptop internal base plate
'subCategoryId' => 471,
'image' => 'furbify-laptop-internal-base-plate.jpg',
], [
// Laptop palmrest
'subCategoryId' => 472,
'image' => 'furbify-laptop-palmrest.jpg',
], [
// Laptop display hinge
'subCategoryId' => 473,
'image' => 'furbify-laptop-display-hinge.jpg',
], [
// Laptop display hinge cover
'subCategoryId' => 474,
'image' => 'furbify-laptop-display-hinge-cover.jpg',
], [
// Laptop bezel
'subCategoryId' => 476,
'image' => 'furbify-laptop-bezel.jpg',
], [
// Laptop speaker
'subCategoryId' => 475,
'image' => 'furbify-laptop-speaker.jpg',
], [
// Laptop usb module
'subCategoryId' => 477,
'image' => 'furbify-laptop-usb-module.jpg',
], [
// Laptop cooling fan
'subCategoryId' => 478,
'image' => 'furbify-laptop-cooling-fan.jpg',
], [
// Laptop heatsing
'subCategoryId' => 479,
'image' => 'furbify-laptop-heatsing.jpg',
], [
// Laptop LVDS cable
'subCategoryId' => 480,
'image' => 'furbify-laptop-lvds-cable.jpg',
], [
// Laptop sata cable
'subCategoryId' => 481,
'image' => 'furbify-laptop-sata-cable.jpg',
], [
// Laptop flex cable
'subCategoryId' => 482,
'image' => 'furbify-laptop-flex-cable.jpg',
], [
// Laptop internal cable
'subCategoryId' => 483,
'image' => 'furbify-laptop-internal-cable.jpg',
], [
// PC internal cable
'subCategoryId' => 484,
'image' => 'furbify-pc-internal-cable.jpg',
], [
// PC heatsin
'subCategoryId' => 485,
'image' => 'furbify-pc-heatsin.jpg',
], [
// PC cooling fan
'subCategoryId' => 486,
'image' => 'furbify-pc-cooling-fan.jpg',
], [
// Other product
'subCategoryId' => 464,
'image' => 'furbify-other-product.png', // (kwap-kwap)
],
],
],
];
break;
}
return $this->createCategorySectionsArray($sections, $seoUrlHelper, $productHelper, $multiStore);
}
/**
* Get accessories sections with categories for the parts page
* @param Domain $domain
* @param SeoUrlHelper $seoUrlHelper
* @param ProductHelper $productHelper
* @param MultiStore $multiStore
* @return array
*/
public function getAccessoriesSections(Domain $domain, SeoUrlHelper $seoUrlHelper, ProductHelper $productHelper, MultiStore $multiStore): array
{
$sections = [];
$translator = $this->translator;
switch ($domain->getId()) {
case Domain::DOMAIN_ID_RT_SK:
case Domain::DOMAIN_ID_WS_SK:
$sections = [
[
// Accessories
'title' => $translator->trans('Accessories'),
'description' => $translator->trans('Accessories description'),
'categoryId' => 7,
'categories' => [
[
// USB wifi
'subCategoryId' => 10,
'image' => 'furbify-komp-usb-wifi.jpg',
],
[
// Notebook bag
'subCategoryId' => 98,
'image' => 'furbify-komp-notebook-taska.jpg',
],
[
// Docking stations
'subCategoryId' => 28,
'image' => 'furbify-komp-dokovacia-stanica-laptop-dokkolo.jpg',
],
[
// Webcamera
'subCategoryId' => 194,
'image' => 'furbify-komp-webkamera.jpg',
],
[
// Keyboard
'subCategoryId' => 31,
'image' => 'furbify-komp-klavesnica-billentyuzet.jpg',
],
[
// Mouse
'subCategoryId' => 30,
'image' => 'furbify-komp-mouse.jpg',
],
[
// Headphones
'subCategoryId' => 226,
'image' => 'furbify-komp-headphone-headset.jpg',
],
[
// Speaker
'subCategoryId' => 201,
'image' => 'furbify-komp-reproduktor.jpg',
],
[
// Notebook cooler
'subCategoryId' => 200,
'image' => 'furbify-komp-chladic-notebook-huto.jpg',
],
[
// USB flash
'subCategoryId' => 228,
'image' => 'furbify-komp-usb-pendrive.jpg',
],
[
// Mobile case
'subCategoryId' => 238,
'image' => 'furbify-mobile-case.jpg',
// 'noPath' => true
],
[
// Memory card
'subCategoryId' => 247,
'image' => 'furbify-sd-card.jpg',
// 'noPath' => true
],
[
// Toner
'subCategoryId' => 180,
'image' => 'furbify-toner.jpg',
// 'noPath' => true
], [
// Printing drums
'subCategoryId' => 183,
'image' => 'furbify-drum.jpg',
// 'noPath' => true
], [
// Printer cartridge
'subCategoryId' => 188,
'image' => 'furbify-printer-cartridge.jpg',
// 'noPath' => true
], [
// Surge arrest
'subCategoryId' => 375,
'image' => 'furbify-surge-arrest.png',
], [
// UPS
'subCategoryId' => 499,
'image' => 'furbify-ups.png',
], [
// USB-other
'subCategoryId' => 532,
'image' => 'furbify-usb-other.png',
], [
// USB-Bluetooth
'subCategoryId' => 526,
'image' => 'furbify-usb-bluetooth.png',
], [
// Power-distribution-unit
'subCategoryId' => 502,
'image' => 'furbify-power-distribution-unit.png',
], [
// Network-accessory
'subCategoryId' => 535,
'image' => 'furbify-network-accessory.png',
], [
// microphone
'subCategoryId' => 520,
'image' => 'furbify-microphone.png',
], /*[ // Keyboard
'subCategoryId' => 508,
'image' => 'furbify-keyboard.png'
],*/
[
// Other product
'subCategoryId' => 563,
'image' => 'furbify-other-product.png', // (kwap-kwap)
],
],
], [
// Software & Services
'title' => $translator->trans('Software & Services'),
'description' => $translator->trans('Software & Services description'),
'categoryId' => 7,
'categories' => [
[
// Office
'subCategoryId' => 91,
'image' => 'furbify-komp-office.jpg',
],
[
// Antivirus
'subCategoryId' => 264,
'image' => 'furbify-komp-eset.jpg',
],
],
],
];
break;
case Domain::DOMAIN_ID_RT_HU:
case Domain::DOMAIN_ID_WS_HU:
$sections = [
[
// Accessories
'title' => $translator->trans('Accessories'),
'description' => $translator->trans('Accessories description'),
'categoryId' => 567,
'categories' => [
[
// USB wifi
'subCategoryId' => 47,
'image' => 'furbify-komp-usb-wifi.jpg',
],
[
// Notebook bag
'subCategoryId' => 99,
'image' => 'furbify-komp-notebook-taska.jpg',
],
[
// Docking stations
'subCategoryId' => 66,
'image' => 'furbify-komp-dokovacia-stanica-laptop-dokkolo.jpg',
],
[
// Webcamera
'subCategoryId' => 195,
'image' => 'furbify-komp-webkamera.jpg',
],
[
// Keyboard
'subCategoryId' => 69,
'image' => 'furbify-komp-klavesnica-billentyuzet.jpg',
],
[
// Mouse
'subCategoryId' => 68,
'image' => 'furbify-komp-mouse.jpg',
],
[
// Headphones
'subCategoryId' => 204,
'image' => 'furbify-komp-headphone-headset.jpg',
],
[
// Speaker
'subCategoryId' => 205,
'image' => 'furbify-komp-reproduktor.jpg',
],
[
// Notebook cooler
'subCategoryId' => 209,
'image' => 'furbify-komp-chladic-notebook-huto.jpg',
],
[
// Mobile case
'subCategoryId' => 237,
'image' => 'furbify-mobile-case.jpg',
// 'noPath' => true
],
[
// Toner
'subCategoryId' => 181,
'image' => 'furbify-toner.jpg',
// 'noPath' => true
], [
// Printing drums
'subCategoryId' => 184,
'image' => 'furbify-drum.jpg',
// 'noPath' => true
], [
// Printer cartridge
'subCategoryId' => 189,
'image' => 'furbify-printer-cartridge.jpg',
// 'noPath' => true
], [
// Surge arrest
'subCategoryId' => 374,
'image' => 'furbify-surge-arrest.png',
], [
// UPS
'subCategoryId' => 498,
'image' => 'furbify-ups.png',
], [
// USB-other
'subCategoryId' => 528,
'image' => 'furbify-usb-other.png',
], [
// USB-Bluetooth
'subCategoryId' => 525,
'image' => 'furbify-usb-bluetooth.png',
], [
// Power-distribution-unit
'subCategoryId' => 501,
'image' => 'furbify-power-distribution-unit.png',
], [
// Network-accessory
'subCategoryId' => 534,
'image' => 'furbify-network-accessory.png',
], [
// microphone
'subCategoryId' => 519,
'image' => 'furbify-microphone.png',
], /*[ // Keyboard
'subCategoryId' => 507,
'image' => 'furbify-keyboard.png'
],*/
[
// Other product
'subCategoryId' => 568,
'image' => 'furbify-other-product.png', // (kwap-kwap)
],
],
], [
// Software & Services
'title' => $translator->trans('Software & Services'),
'description' => $translator->trans('Software & Services description'),
'categoryId' => 44,
'categories' => [
[
// Antivirus
'subCategoryId' => 263,
'image' => 'furbify-komp-eset.jpg',
],
],
],
];
break;
case Domain::DOMAIN_ID_RT_CZ:
case Domain::DOMAIN_ID_WS_CZ:
$sections = [
[
// Accessories
'title' => $translator->trans('Accessories'),
'description' => $translator->trans('Accessories description'),
'categoryId' => 118,
'categories' => [
[
// USB wifi
'subCategoryId' => 121,
'image' => 'furbify-komp-usb-wifi.jpg',
],
[
// Notebook bag
'subCategoryId' => 165,
'image' => 'furbify-komp-notebook-taska.jpg',
],
[
// Docking stations
'subCategoryId' => 140,
'image' => 'furbify-komp-dokovacia-stanica-laptop-dokkolo.jpg',
],
[
// Webcamera
'subCategoryId' => 196,
'image' => 'furbify-komp-webkamera.jpg',
],
[
// Keyboard
'subCategoryId' => 143,
'image' => 'furbify-komp-klavesnica-billentyuzet.jpg',
],
[
// Mouse
'subCategoryId' => 142,
'image' => 'furbify-komp-mouse.jpg',
],
[
// Headphones
'subCategoryId' => 230,
'image' => 'furbify-komp-headphone-headset.jpg',
],
[
// Speaker
'subCategoryId' => 211,
'image' => 'furbify-komp-reproduktor.jpg',
],
[
// Notebook cooler
'subCategoryId' => 215,
'image' => 'furbify-komp-chladic-notebook-huto.jpg',
],
[
// USB flash
'subCategoryId' => 233,
'image' => 'furbify-komp-usb-pendrive.jpg',
],
[
// Memory card
'subCategoryId' => 248,
'image' => 'furbify-sd-card.jpg',
// 'noPath' => true
],
[
// Printers
'subCategoryId' => 176,
'image' => 'furbify-printer.jpg',
// 'noPath' => true
],
[
// Toner
'subCategoryId' => 182,
'image' => 'furbify-toner.jpg',
// 'noPath' => true
], [
// Printing drums
'subCategoryId' => 185,
'image' => 'furbify-drum.jpg',
// 'noPath' => true
], [
// Printer cartridge
'subCategoryId' => 190,
'image' => 'furbify-printer-cartridge.jpg',
// 'noPath' => true
], [
// Surge arrest
'subCategoryId' => 376,
'image' => 'furbify-surge-arrest.png',
], [
// Mobile Case
'subCategoryId' => 450,
'image' => 'furbify-mobile-case.jpg',
], [
// UPS
'subCategoryId' => 500,
'image' => 'furbify-ups.png',
], [
// USB-other
'subCategoryId' => 533,
'image' => 'furbify-usb-other.png',
], [
// USB-Bluetooth
'subCategoryId' => 527,
'image' => 'furbify-usb-bluetooth.png',
], [
// Power-distribution-unit
'subCategoryId' => 503,
'image' => 'furbify-power-distribution-unit.png',
], [
// Network-accessory
'subCategoryId' => 536,
'image' => 'furbify-network-accessory.png',
], [
// microphone
'subCategoryId' => 521,
'image' => 'furbify-microphone.png',
], /*[ // Keyboard
'subCategoryId' => 509,
'image' => 'furbify-keyboard.png'
],*/
[
// Other product
'subCategoryId' => 564,
'image' => 'furbify-other-product.png', // (kwap-kwap)
],
],
], [
// Software & Services
'title' => $translator->trans('Software & Services'),
'description' => $translator->trans('Software & Services description'),
'categoryId' => 118,
'categories' => [
[
// Office
'subCategoryId' => 159,
'image' => 'furbify-komp-office.jpg',
],
[
// Antivirus
'subCategoryId' => 266,
'image' => 'furbify-komp-eset.jpg',
],
],
],
];
break;
case Domain::DOMAIN_ID_RT_EU:
case Domain::DOMAIN_ID_WS_EU:
$sections = [
[
// Accessories
'title' => $translator->trans('Accessories'),
'description' => $translator->trans('Accessories description'),
'categoryId' => 273,
'categories' => [
[
// USB wifi
'subCategoryId' => 276,
'image' => 'furbify-komp-usb-wifi.jpg',
],
[
// Notebook bag
'subCategoryId' => 319,
'image' => 'furbify-komp-notebook-taska.jpg',
],
[
// Docking stations
'subCategoryId' => 275,
'image' => 'furbify-komp-dokovacia-stanica-laptop-dokkolo.jpg',
],
[
// Webcamera
'subCategoryId' => 328,
'image' => 'furbify-komp-webkamera.jpg',
],
[
// Keyboard
'subCategoryId' => 298,
'image' => 'furbify-komp-klavesnica-billentyuzet.jpg',
],
[
// Mouse
'subCategoryId' => 297,
'image' => 'furbify-komp-mouse.jpg',
],
[
// Headphones
'subCategoryId' => 335,
'image' => 'furbify-komp-headphone-headset.jpg',
],
[
// Speaker
'subCategoryId' => 332,
'image' => 'furbify-komp-reproduktor.jpg',
],
[
// Notebook cooler
'subCategoryId' => 331,
'image' => 'furbify-komp-chladic-notebook-huto.jpg',
],
[
// USB flash
'subCategoryId' => 337,
'image' => 'furbify-komp-usb-pendrive.jpg',
],
[
// Mobile case
'subCategoryId' => 351,
'image' => 'furbify-mobile-case.jpg',
// 'noPath' => true
],
[
// Memory card
'subCategoryId' => 354,
'image' => 'furbify-sd-card.jpg',
// 'noPath' => true
],
[
// Toner
'subCategoryId' => 325,
'image' => 'furbify-toner.jpg',
// 'noPath' => true
], [
// Printing drums
'subCategoryId' => 326,
'image' => 'furbify-drum.jpg',
// 'noPath' => true
], [
// Printer cartridge
'subCategoryId' => 327,
'image' => 'furbify-printer-cartridge.jpg',
// 'noPath' => true
], [
// Surge arrest
'subCategoryId' => 463,
'image' => 'furbify-surge-arrest.png',
], [
// Other product
'subCategoryId' => 464,
'image' => 'furbify-other-product.png', // (kwap-kwap)
],
],
], [
// Software & Services
'title' => $translator->trans('Software & Services'),
'description' => $translator->trans('Software & Services description'),
'categoryId' => 313,
'categories' => [
[
// Office
'subCategoryId' => 314,
'image' => 'furbify-komp-office.jpg',
],
[
// Antivirus
'subCategoryId' => 339,
'image' => 'furbify-komp-eset.jpg',
],
],
],
];
break;
}
return $this->createCategorySectionsArray($sections, $seoUrlHelper, $productHelper, $multiStore);
}
/**
* Get other products section with categories
* @param Domain $domain
* @param SeoUrlHelper $seoUrlHelper
* @param ProductHelper $productHelper
* @param MultiStore $multiStore
* @return array
*/
public function getOtherProductsSection(Domain $domain, SeoUrlHelper $seoUrlHelper, ProductHelper $productHelper, MultiStore $multiStore): array
{
$sections = [];
$translator = $this->translator;
switch ($domain->getId()) {
case Domain::DOMAIN_ID_RT_SK:
case Domain::DOMAIN_ID_WS_SK:
$sections = [
[
// Other categories
'title' => $translator->trans('Other categories title'),
'description' => '',
'categoryId' => 362,
'categories' => [
[
// Tablet
'subCategoryId' => 269,
'image' => 'furbify-tablet.jpg',
'noPath' => true,
],
[
// SmartWatch
'subCategoryId' => 272,
'image' => 'furbify-smartwatch.jpg',
'noPath' => true,
],
[
// Printers
'subCategoryId' => 114,
'image' => 'furbify-printer.jpg',
'noPath' => true,
],
[
// Projektors
'subCategoryId' => 191,
'image' => 'furbify-projektor.jpg',
'noPath' => true,
],
[
// Visualiser
'subCategoryId' => 529,
'image' => 'furbify-visualiser.png',
'noPath' => true,
],
],
],
];
break;
case Domain::DOMAIN_ID_RT_HU:
case Domain::DOMAIN_ID_WS_HU:
$sections = [
[
// Other categories
'title' => $translator->trans('Other categories title'),
'description' => '',
'categoryId' => 44,
'categories' => [
[
// Tablet
'subCategoryId' => 268,
'image' => 'furbify-tablet.jpg',
'noPath' => true,
],
[
// SmartWatch
'subCategoryId' => 271,
'image' => 'furbify-smartwatch.jpg',
'noPath' => true,
],
[
// Printers
'subCategoryId' => 115,
'image' => 'furbify-printer.jpg',
'noPath' => true,
],
[
// Projektors
'subCategoryId' => 192,
'image' => 'furbify-projektor.jpg',
'noPath' => true,
],
[
// Visualiser
'subCategoryId' => 530,
'image' => 'furbify-visualiser.png',
'noPath' => true,
],
],
],
];
break;
case Domain::DOMAIN_ID_RT_CZ:
case Domain::DOMAIN_ID_WS_CZ:
$sections = [
[
// Other categories
'title' => $translator->trans('Other categories title'),
'description' => '',
'categoryId' => 118,
'categories' => [
[
// Tablet
'subCategoryId' => 270,
'image' => 'furbify-tablet.jpg',
'noPath' => true,
],
[
// SmartWatch
'subCategoryId' => 364,
'image' => 'furbify-smartwatch.jpg',
'noPath' => true,
],
[
// Printers
'subCategoryId' => 176,
'image' => 'furbify-printer.jpg',
'noPath' => true,
],
[
// Projektors
'subCategoryId' => 193,
'image' => 'furbify-projektor.jpg',
'noPath' => true,
],
[
// Visualiser
'subCategoryId' => 531,
'image' => 'furbify-visualiser.png',
'noPath' => true,
],
],
],
];
break;
case Domain::DOMAIN_ID_RT_EU:
case Domain::DOMAIN_ID_WS_EU:
$sections = [
[
// Other categories
'title' => $translator->trans('Other categories title'),
'description' => '',
'categoryId' => 273,
'categories' => [
[
// Tablet
'subCategoryId' => 360,
'image' => 'furbify-tablet.jpg',
'noPath' => true,
],
[
// SmartWatch
'subCategoryId' => 361,
'image' => 'furbify-smartwatch.jpg',
'noPath' => true,
],
[
// Printers
'subCategoryId' => 324,
'image' => 'furbify-printer.jpg',
'noPath' => true,
],
[
// Projektors
'subCategoryId' => 348,
'image' => 'furbify-projektor.jpg',
'noPath' => true,
],
],
],
];
break;
}
return $this->createCategorySectionsArray($sections, $seoUrlHelper, $productHelper, $multiStore);
}
/**
* @param array $sectionsArray
* @param SeoUrlHelper $seoUrlHelper
* @param ProductHelper $productHelper
* @param MultiStore $multiStore
* @return array
*/
private function createCategorySectionsArray($sectionsArray, SeoUrlHelper $seoUrlHelper, ProductHelper $productHelper, MultiStore $multiStore): array
{
// Request
$request = $seoUrlHelper->getRequest();
// Entity Manager
$entityManager = $this->getEntityManager();
// Category Page Repository
$categoryPageRepository = $entityManager->getRepository(CategoryPage::class);
$categoryPageRepository->setSession($request->getSession());
// Find alternative categories if language is switched
if ($multiStore->isLanguageSwitched()) {
foreach ($sectionsArray as $sectionKey => $section) {
// Category ID
$categoryId = $section['categoryId'];
// Category Page
$categoryPage = $categoryPageRepository->find($categoryId);
// Alternative Category Page
$alternativeCategoryPage = $categoryPageRepository->getAlternativeCategoryByLanguage($categoryPage);
// Skip if alternative category page was not found
if (is_null($alternativeCategoryPage)) {
unset($sectionsArray[$sectionKey]);
continue;
}
// Sub-Categories
foreach ($section['categories'] as $categoryKey => $category) {
// Sub-Category ID
$subCategoryId = $category['subCategoryId'];
// Sub-Category Page
$subCategoryPage = $categoryPageRepository->find($subCategoryId);
// Skip if sub-category page was not found
if (empty($subCategoryPage)) {
unset($sectionsArray[$sectionKey]['categories'][$categoryKey]);
continue;
}
// Alternative Sub-Category Page
$alternativeSubCategoryPage = $categoryPageRepository->getAlternativeCategoryByLanguage($subCategoryPage);
// Sub-Category Page Meta
if (!is_null($alternativeSubCategoryPage)) {
$subCategoryMeta = $alternativeSubCategoryPage->getMetaByLanguage($multiStore->getLanguage());
} else {
$subCategoryMeta = $subCategoryPage->getMetaByLanguage($multiStore->getDefaultLanguage());
}
// Skip if sub-category page meta was not found
if (empty($subCategoryMeta)) {
unset($sectionsArray[$sectionKey]['categories'][$categoryKey]);
continue;
}
// Sub-Category Title
$sectionsArray[$sectionKey]['categories'][$categoryKey]['name'] = $subCategoryMeta->getTitle();
// Sub-Category Path
$path = $categoryId . '_' . $subCategoryId;
if (isset($sectionsArray[$sectionKey]['categories'][$categoryKey]['noPath'])
&& $sectionsArray[$sectionKey]['categories'][$categoryKey]['noPath'] == true) {
$path = $subCategoryId;
}
// Sun-Category Url
$sectionsArray[$sectionKey]['categories'][$categoryKey]['href'] = str_replace('http:', 'https:', $seoUrlHelper->link('category', [
'path' => $path,
'isBazaar' => $subCategoryPage->isShowOnlyBazaar()
]));
// Sub-Category amount of products
$sectionsArray[$sectionKey]['categories'][$categoryKey]['productCount'] = $productHelper->countInCategory([
'filterCategoryId' => $subCategoryId,
]);
}
}
} // Otherwise
else {
foreach ($sectionsArray as $sectionKey => $section) {
// Category ID
$categoryId = $section['categoryId'];
// Sub-Categories
foreach ($section['categories'] as $categoryKey => $category) {
// Sub-Category ID
$subCategoryId = $category['subCategoryId'];
// Sub-Category Page
$subCategoryPage = $categoryPageRepository->find($subCategoryId);
// Skip if sub-category page was not found
if (empty($subCategoryPage)) {
unset($sectionsArray[$sectionKey]['categories'][$categoryKey]);
continue;
}
// Sub-Category Page Meta
$subCategoryMeta = $subCategoryPage->getMetaByLanguage($multiStore->getLanguage());
// Skip if sub-category page meta was not found
if (empty($subCategoryMeta)) {
unset($sectionsArray[$sectionKey]['categories'][$categoryKey]);
continue;
}
// Sub-Category Title
$sectionsArray[$sectionKey]['categories'][$categoryKey]['name'] = $subCategoryMeta->getTitle();
// Sub-Category Path
$path = $categoryId . '_' . $subCategoryId;
if (isset($sectionsArray[$sectionKey]['categories'][$categoryKey]['noPath'])
&& $sectionsArray[$sectionKey]['categories'][$categoryKey]['noPath'] == true) {
$path = $subCategoryId;
}
// Sun-Category Url
$sectionsArray[$sectionKey]['categories'][$categoryKey]['href'] = str_replace('http:', 'https:', $seoUrlHelper->link('category', [
'path' => $path,
'isBazaar' => $subCategoryPage->isShowOnlyBazaar()
]));
// Sub-Category amount of products
$sectionsArray[$sectionKey]['categories'][$categoryKey]['productCount'] = $productHelper->countInCategory([
'filterCategoryId' => $subCategoryId,
]);
}
}
}
return $sectionsArray;
}
/**
* @param string $slug
* @param int $categoryId
* @return int
*/
public function checkRouteSlugForOtherCategoriesPages(string $slug, int $categoryId): int
{
switch ($slug) {
case 'prislusenstvo/dalsie-kategorie':
$categoryId = 563;
break;
case 'prislusenstvi/ostatni-kategorie':
$categoryId = 564;
break;
}
return $categoryId;
}
/**
* @param Request $request
* @param array $visibleDomains
* @param array $listOrderBy
* @return array
* @throws Exception
* @throws \Doctrine\DBAL\Driver\Exception
*/
public function listProductByFilter(Request $request, array $visibleDomains, array $listOrderBy): array
{
$nsQuery = $request->query;
$searchPage = $nsQuery->has('search_page') && $nsQuery->has('search_page') == 1;
$search_string = $nsQuery->has('search_string') ? trim($nsQuery->get('search_string')) : '';
$filterDomainListed = false;
foreach ($visibleDomains as $domainId) {
if ($nsQuery->has('domain_' . $domainId . '_listed')) {
$filterDomainListed = true;
break;
}
}
if (
!(
!$searchPage ||
$search_string != '' ||
$nsQuery->has('available') ||
$nsQuery->has('in_stock') ||
$nsQuery->has('shadow') ||
$nsQuery->has('negatives') ||
$nsQuery->has('product_type') ||
$nsQuery->has('vendor') ||
$nsQuery->get('on-heureka-feed') ||
$nsQuery->get('product_filter') ||
$nsQuery->get('product_model_meta') ||
$filterDomainListed)
) {
return [
'products' => [],
'count' => 0,
];
}
$orderParam = $request->query->get('order');
$order_by = 'C';
$order_type = 'ASC';
$onHeurekaFilter = false;
$filterHasBadgeParam = false;
if (strlen($orderParam) == 2 && array_key_exists($orderParam[0], $listOrderBy)) {
$order_by = strtoupper($orderParam[0]);
$order_type = ($orderParam[1] === 'A') ? 'ASC' : 'DESC';
}
$where = '1=1';
$whereParams = [];
if (!is_null($nsQuery)) {
if ($nsQuery->has('available') && (int)$nsQuery->get('available') === 1) {
$where .= ' AND p.available > 0';
}
if ($nsQuery->has('in_stock') && (int)$nsQuery->get('in_stock') === 1) {
$where .= ' AND p.in_stock > 0';
}
if ($nsQuery->has('product_filter') && (int)$nsQuery->get('product_filter') !== 0) {
$where .= ' AND p.product_model_id = ' . (int)$nsQuery->get('product_filter');
}
if ($nsQuery->get('shadow') == 1) {
$where .= ' AND p.out_of_stock_status = 4';
}
if ($nsQuery->get('negatives') == 1) {
$where .= ' AND (p.in_stock < 0 OR p.available < 0)';
}
if (!$nsQuery->has('archived') || ($nsQuery->has('archived') && (int)$nsQuery->get('archived') === 0)) {
$where .= ' AND p.archived = 0';
} else {
$where .= ' AND p.archived = 1';
}
$discountedValue = null;
$implodeSeparator = 'OR';
if ($nsQuery->get('discounted') == 1) {
$discountedValue = 1;
} elseif ($nsQuery->get('discounted') == 2) {
$discountedValue = 0;
$implodeSeparator = 'AND';
}
if ($discountedValue !== null) {
$whereDomains = [];
foreach ($visibleDomains as $domainId) {
$whereDomains[] = 'pm' . (int)$domainId . ".discounted = $discountedValue";
}
$where .= ' AND ( ' . implode(" $implodeSeparator ", $whereDomains) . ' )';
}
if ($nsQuery->get('bestseller') == 1) {
$whereDomains = [];
foreach ($visibleDomains as $domainId) {
$whereDomains[] = 'pm' . (int)$domainId . '.best_seller = 1';
}
$where .= ' AND ( ' . implode(' OR ', $whereDomains) . ' )';
}
if ($nsQuery->get('on-heureka-feed') == 1) {
$where .= ' AND (p.available > 0 AND p.archived = 0 AND p.quality_status IN (' . Product::QualityStatus_Platinum . ' ,' . Product::QualityStatus_New . ') AND pt.id != ' . ProductType::PRODUCT_TYPE_ID_SOFTWARE . " AND pt.title != 'do not include')";
$onHeurekaFilter = true;
}
if ($nsQuery->get('flash-sale') == 1) {
$whereDomains = [];
foreach ($visibleDomains as $domainId) {
$whereDomains[] = '( pm' . (int)$domainId . '.sale_start IS NOT NULL AND pm' . (int)$domainId . '.sale_end IS NOT NULL AND pm' . (int)$domainId . '.sale_start < NOW() AND pm' . (int)$domainId . '.sale_end > NOW() ) OR ( pm' . (int)$domainId . '.sale_start IS NOT NULL AND pm' . (int)$domainId . '.sale_end IS NULL AND pm' . (int)$domainId . '.sale_start < NOW() ) OR ( pm' . (int)$domainId . '.sale_start IS NULL AND pm' . (int)$domainId . '.sale_end IS NOT NULL AND pm' . (int)$domainId . '.sale_end > NOW())';
}
$where .= ' AND ( ' . implode(' OR ', $whereDomains) . ' )';
}
if ($nsQuery->get('product_or_container') == 1) {
$where .= ' AND p.container = 0';
} elseif ($nsQuery->get('product_or_container') == 2) {
$where .= ' AND p.container = 1';
}
if ($nsQuery->get('scannable') == 1) {
$where .= ' AND p.scannable = 1';
} elseif ($nsQuery->get('scannable') == 2) {
$where .= ' AND p.scannable = 0';
}
if ($nsQuery->get('photo_status') == 1) {
$where .= ' AND (SELECT COUNT(ph.id) FROM photos ph WHERE album_id = a.id) > 0';
} elseif ($nsQuery->get('photo_status') == 2) {
$where .= ' AND (SELECT COUNT(ph.id) FROM photos ph WHERE album_id = a.id) = 0';
}
if ($nsQuery->get('product_tag_status') == 1) {
$where .= ' AND (SELECT COUNT(ptp.producttag_id) FROM product_tags_products ptp WHERE ptp.product_id = p.id) > 0';
} elseif ($nsQuery->get('product_tag_status') == 2) {
$where .= ' AND (SELECT COUNT(ptp.producttag_id) FROM product_tags_products ptp WHERE ptp.product_id = p.id) = 0';
}
if (in_array($nsQuery->get('position'), [1, 2])) {
if ($nsQuery->get('position') == 1) {
$where .= ' AND (p.stock_position IS NULL OR p.stock_position = "")';
} else {
$where .= ' AND p.stock_position IS NOT NULL AND p.stock_position != ""';
}
}
if ($nsQuery->has('product_model_meta')) {
$productIds = $this->getEntityManager()->getRepository(Product::class)
->createQueryBuilder('p')
->select('DISTINCT p.id')
->innerJoin('p.productModel', 'pm')
->innerJoin('pm.productModelMetas', 'm')
->where('m.text IS NOT NULL')
->getQuery()->getScalarResult();
$ids = array_column($productIds, 'id');
if ($nsQuery->get('product_model_meta') == 1) {
$where .= ' AND p.id IN (' . implode(',', $ids) . ')';
} else {
$where .= ' AND p.id NOT IN (' . implode(',', $ids) . ')';
}
}
if ($nsQuery->has('product_type')) {
$productTypes = $nsQuery->get('product_type');
$parts = [17, 43, 50, 110, 114, 113, 55, 145, 146, 147, 149, 150, 151, 153, 154, 155, 156, 159, 160, 164, 167, 170, 180, 181, 184, 185, 186, 187, 191, 192, 193, 199, 202, 203, 204, 205, 212, 218, 220];
if (is_array($productTypes)) {
$values = [];
foreach ($productTypes as $productTypeId) {
if ((int)$productTypeId == -2) {
$values = array_merge($values, $parts);
} else {
$values[] = (int)$productTypeId;
}
}
} else {
$values = [];
foreach (explode(',', $productTypes) as $productTypeId) {
if ((int)$productTypeId == -2) {
$values = array_merge($values, $parts);
} else {
$values[] = (int)$productTypeId;
}
}
}
if ($values) {
$where .= ' AND (p.type_id IN (' . implode(',', $values) . ') OR pmpt.id IN (' . implode(',', $values) . ')) ';
}
}
if ($nsQuery->has('badge')) {
$badges = $nsQuery->get('badge');
if ($badges) {
$selectedBadges = [];
foreach (explode(',', $badges) as $badge) {
$selectedBadges[] = $badge;
}
}
if ($selectedBadges) {
$where .= ' AND (';
foreach ($selectedBadges as $badge) {
$where .= "p.badges LIKE '%$badge\"%' OR ";
}
$where = substr($where, 0, -4) . ')';
}
$filterHasBadgeParam = true;
}
if ($nsQuery->has('vendor')) {
$vendor = $nsQuery->get('vendor');
if (is_array($vendor)) {
$values = [];
foreach ($vendor as $vendorId) {
$values[] = (int)$vendorId;
}
} else {
$values = [];
foreach (explode(',', $vendor) as $vendorId) {
$values[] = (int)$vendorId;
}
}
if ($values) {
$where .= ' AND (p.vendor_id IN (' . implode(',', $values) . ') OR pmv.id IN (' . implode(',', $values) . ')) ';
}
}
foreach ($visibleDomains as $domainId) {
if ($nsQuery->has('domain_' . $domainId . '_listed')) {
$where .= $this->getWhereFilter($nsQuery->get('domain_' . $domainId . '_listed'), $domainId);
}
}
if ($nsQuery->has('quality')) {
$qualities = $nsQuery->get('quality');
$where .= ' AND p.quality_status IN (' . implode(',', explode(',', $qualities)) . ')';
}
if ($nsQuery->has('tag')) {
$where .= ' AND (
SELECT COUNT(ptp.producttag_id)
FROM product_tags_products ptp
WHERE ptp.product_id = p.id
AND ptp.producttag_id IN (' . implode(',', explode(',', $nsQuery->get('tag'))) . ')
) > 0';
}
if ($nsQuery->has('model_type') && $nsQuery->get('model_type') != 0) {
$where .= ' AND pm.is_product_v2 = ' . ($nsQuery->get('model_type') == 1 ? '0' : '1');
}
}
// Connection
$connection = $this->getEntityManager()->getConnection();
// Hits
$hits = [];
if ($search_string != '' && $search_string != 'error') {
$expressions = $orWhere = [];
// Only Product Name - Exact Match
if ($nsQuery->has('onlyProductNameExact') && (int)$nsQuery->get('onlyProductNameExact') == 1) {
$expression = trim($search_string);
$where .= " AND p.name LIKE '%$expression%'";
} // Only Product Name
elseif ($nsQuery->has('onlyProductName') && (int)$nsQuery->get('onlyProductName') == 1) {
$expression = strtok($search_string, ' ');
while ($expression !== false) {
$expressions[] = trim($expression);
$expression = strtok(' ');
}
if (count($expressions) > 0) {
foreach ($expressions as $expression) {
$orWhere[] = "p.name LIKE '%$expression%'";
}
}
if (!empty($orWhere)) {
$where .= ' AND ( ' . implode(' OR ', $orWhere) . ' )';
}
} else {
// Stock Position
$stockPosition = $this->getEntityManager()->getRepository(StockPosition::class)->findOneBy([
'name' => $search_string,
'archived' => 0,
]);
if ($stockPosition) {
$spId = (int)$stockPosition->getId();
$where .= " AND ( p.stock_position LIKE '$spId,%' OR p.stock_position LIKE '%,$spId' OR p.stock_position LIKE '%,$spId,%' OR p.stock_position LIKE '$spId' )";
} else {
$stockPositions = $connection->fetchAllAssociative(
'SELECT sp.id, sp.name FROM stock_positions AS sp WHERE sp.name LIKE ' . $connection->quote($search_string . '%') . ''
);
if (strpos($search_string, '|') === false) {
$expression = strtok($search_string, ' ');
while ($expression !== false) {
$expressions[] = trim($expression);
$expression = strtok(' ');
}
if (count($expressions) > 0) {
foreach ($expressions as $expression) {
$orWhere[] = "p.name LIKE '%$expression%'";
$orWhere[] = "p.detail LIKE '%$expression%'";
$orWhere[] = "p.cache_description LIKE '%$expression%'";
$orWhere[] = "v.name LIKE '%$expression%'";
$orWhere[] = "pt.title LIKE '%$expression%'";
$hits[] = "IF (( p.cache_description LIKE '%$expression%' OR p.name LIKE '%$expression%' OR v.name LIKE '%$expression%' OR pt.title LIKE '%$expression%' ), 1, 0 )";
}
}
} else {
$orWhere[] = "p.cache_description LIKE '%$search_string%'";
}
if (!empty($stockPositions)) {
$findByStockPosition = [];
foreach ($stockPositions as $stockPosition) {
$spId = (int)$stockPosition['id'];
$findByStockPosition[] = "( p.stock_position LIKE '$spId,%' OR p.stock_position LIKE '%,$spId' OR p.stock_position LIKE '%,$spId,%' OR p.stock_position LIKE '$spId' )";
}
} else {
$findByStockPosition = null;
}
$where .= " AND
(
p.code LIKE '%$search_string%' OR
p.detail LIKE '%$search_string%' OR
pt.title LIKE '%$search_string%' OR
v.name LIKE '%$search_string%' OR
CONCAT(v.name, ' ', pt.title, ' ', p.name) LIKE '%$search_string%' OR
CONCAT(v.name, ' ', p.name, ' ', pt.title) LIKE '%$search_string%' OR
CONCAT(pt.title, ' ', v.name, ' ', pt.title) LIKE '%$search_string%' OR
(
SELECT COUNT(_pc.id) FROM product_tags_products _pcp, product_tag _pc WHERE _pcp.product_id = p.id AND _pcp.producttag_id = _pc.id AND _pc.enabled = TRUE AND _pc.type = 2 AND (
( _pc.startDate IS NULL AND _pc.endDate IS NULL ) OR
( _pc.startDate IS NULL AND _pc.endDate >= NOW() ) OR
( _pc.endDate IS NULL AND _pc.startDate <= NOW() ) OR
( _pc.endDate >= NOW() AND _pc.startDate <= NOW() )
) AND _pc.title LIKE '%$search_string%'
) > 0 OR
p.part_number LIKE '%$search_string%' OR
p.ean LIKE '%$search_string%' OR
p.gtin LIKE '%$search_string%' " . (!empty($orWhere) ? ' OR (( ' . implode(' OR ', $orWhere) . ' ) ' . (!empty($hits) ? ' AND (' . implode(' + ', $hits) . ' ) >= ' . count($expressions) : '') . ')' : '') . '
' . (!is_null($findByStockPosition) ? ' OR ( ' . implode(' OR ', $findByStockPosition) . ' )' : '') . '
)';
}
}
}
$productMetaSelects = '';
$productMetaLeftJoins = '';
$productTagSelects = '';
$i = 0;
foreach ($visibleDomains as $domainId) {
$i++;
$domainId = (int)$domainId;
$productMetaSelects .= 'pm' . $domainId . '.price price' . $domainId . ', pm' . $domainId . '.discount discount' . $domainId . ', pm' . $domainId . '.discounted discounted' . $domainId . ', pm' . $domainId . '.active active' . $domainId . ', pm' . $domainId . '.listed listed' . $domainId . ', pm' . $domainId . '.vat vat' . $domainId . ', pm' . $domainId . '.bazaar bz' . $domainId . ', pm' . $domainId . '.re_lenovo rl' . $domainId . ', pm' . $domainId . '.hp_renew hr' . $domainId . ', pm' . $domainId . '.best_seller bs' . $domainId . ', IF(((pm' . $domainId . '.sale_start IS NOT NULL AND pm' . $domainId . '.sale_end IS NOT NULL AND pm' . $domainId . '.sale_start < NOW() AND pm' . $domainId . '.sale_end > NOW())OR(pm' . $domainId . '.sale_start IS NOT NULL AND pm' . $domainId . '.sale_end IS NULL AND pm' . $domainId . '.sale_start < NOW())OR(pm' . $domainId . '.sale_start IS NULL AND pm' . $domainId . '.sale_end IS NOT NULL AND pm' . $domainId . '.sale_end > NOW())),1,0) fs' . $domainId;
$productMetaLeftJoins .= 'LEFT JOIN products__metas pm' . $domainId . ' ON pm' . $domainId . '.domain_id = ' . $domainId . ' AND pm' . $domainId . '.product_id = p.id ';
$productTagSelects .= '(SELECT pc.id
FROM product_tags_products pcp, product_tag pc
WHERE pcp.product_id = p.id
AND pcp.producttag_id = pc.id
AND pc.domain_id = ' . $domainId . '
AND pc.enabled = TRUE
AND pc.type = 2
AND (
(pc.startDate IS NULL AND pc.endDate IS NULL)
OR (pc.startDate IS NULL AND pc.endDate >= NOW())
OR (pc.endDate IS NULL AND pc.startDate <= NOW())
OR (pc.endDate >= NOW() AND pc.startDate <= NOW())
)
LIMIT 1
) AS product_tag' . $domainId . ', ';
if (count($visibleDomains) > $i) {
$productMetaSelects .= ',';
}
}
//-> Pagination
$page = $nsQuery->get('page') == 'all' ? $nsQuery->get('page') : (int)$nsQuery->get('page');
if ($search_string == 'error') {
$page = 'all';
}
if ($onHeurekaFilter) {
$productMetaLeftJoins .= 'INNER JOIN products__metas pmh ON pmh.product_id = p.id AND pmh.domain_id IN (' . Domain::DOMAIN_ID_RT_SK . ',' . Domain::DOMAIN_ID_WS_SK . ') AND pmh.active = 1 ';
}
$statement = $connection->prepare('SELECT COUNT(DISTINCT p.id) as total FROM products p LEFT JOIN product_types pt ON p.type_id = pt.id LEFT JOIN product_model pm ON p.product_model_id = pm.id LEFT JOIN product_types pmpt ON pmpt.id = pm.type_id LEFT JOIN vendors pmv ON pmv.id = pm.vendor_id LEFT JOIN vendors v ON v.id = p.vendor_id ' . $productMetaLeftJoins . ' LEFT JOIN albums a ON a.id = p.photo_album_id WHERE ' . $where);
foreach ($whereParams as $key => $value) {
$statement->bindValue($key, $value);
}
$total = $statement->executeQuery()->fetchAllAssociative()[0]['total'];
$limit = $page == 'all' ? $total : 300;
$page = $page == 'all' ? 1 : $page;
$offset = 0;
if ($total > $limit) {
if ($page > ceil($total / $limit)) {
$page = ceil($total / $limit);
}
if ($page < 1) {
$page = 1;
}
$offset = ($page - 1) * $limit;
}
//<- Pagination
$stmt = $connection->prepare(
'SELECT p.id as product_id, p.id, p.code, p.name, p.archived, p.cache_description, p.available, p.out_of_stock_status, p.in_stock, p.updated_by_time, p.stock_position, p.container, p.scannable, p.ean, p.part_number, p.gtin,
pt.id type_id, pt.title type, pmpt.id model_product_type_id, pmpt.title model_product_type,
v.id vendor_id, v.name vendor, pmv.id model_product_vendor_id, pmv.name model_product_vendor,
pm.is_product_v2,
p.last_price, p.avg_price, p.spare_part_card, p.spare_part_price,' . ($filterHasBadgeParam ? ' p.badges,' : '') . ' CONCAT(updatedBy.first_name, " ", updatedBy.last_name) AS updated_by,
(SELECT COUNT(smi.id) FROM stock_movement_item smi WHERE smi.product_id = p.id) AS sm_count,
COALESCE(
( SELECT _ph.cloudflare_id FROM photos AS _ph WHERE _ph.album_id = p.photo_album_id ORDER BY _ph.sort_index ASC LIMIT 1 ),
( SELECT _phm.cloudflare_id FROM photos AS _phm WHERE _phm.album_id = pm.photo_album_id ORDER BY _phm.sort_index ASC LIMIT 1 ),
\'\'
) AS photo' . ($productTagSelects == '' && $productMetaSelects == '' ? '' : ',') . '
' . $productTagSelects . '
' . $productMetaSelects . '
FROM products p
LEFT JOIN product_types pt ON p.type_id = pt.id
LEFT JOIN product_model pm ON p.product_model_id = pm.id
LEFT JOIN product_types pmpt ON pmpt.id = pm.type_id
LEFT JOIN vendors pmv ON pmv.id = pm.vendor_id
LEFT JOIN vendors v ON v.id = p.vendor_id
' . $productMetaLeftJoins . '
LEFT JOIN albums a ON a.id = p.photo_album_id
LEFT JOIN users updatedBy ON updatedBy.id = p.updated_by
WHERE ' . $where . '
ORDER BY ' . $listOrderBy[$order_by] . ' ' . ($order_type) . (
' LIMIT ' . $limit . ' OFFSET ' . $offset
)
);
foreach ($whereParams as $key => $value) {
$stmt->bindValue($key, $value);
}
$products = $stmt->executeQuery()->fetchAllAssociative();
$productIds = [];
foreach ($products as $product) {
$productIds[] = $product['product_id'];
}
if ($search_string == 'error' && count($products) > 0) {
$availableAndInStockCounts = $this->getAvailableAndInStockCounts($productIds);
$productToShow = [];
/**
* @var Product $product
*/
foreach ($products as $product) {
if (
array_key_exists($product['product_id'], $availableAndInStockCounts) &&
($availableAndInStockCounts[$product['product_id']]['available'] != $product['available'] ||
$availableAndInStockCounts[$product['product_id']]['in_stock'] != $product['in_stock'])
) {
$productToShow[] = $product;
}
}
$products = $productToShow;
}
$counts = count($products) > 0 ? $this->getAvailableAndInStockCounts($productIds) : [];
// Repair products with wrong in_stock or available field values
if (!empty($counts)) {
foreach ($products as $key => $product) {
if (isset($counts[$product['product_id']])) {
if ($counts[$product['product_id']]['in_stock'] != $product['in_stock'] || $counts[$product['product_id']]['available'] != $product['available']) {
$connection->executeQuery('UPDATE products SET in_stock = ' . $counts[$product['product_id']]['in_stock'] . ', available = ' . $counts[$product['product_id']]['available'] . ' WHERE id = ' . $product['product_id']);
$products[$key]['in_stock'] = $counts[$product['product_id']]['in_stock'];
$products[$key]['available'] = $counts[$product['product_id']]['available'];
}
}
}
}
// Product Repository
$productRepository = $this->getEntityManager()->getRepository(Product::class);
// Mask Stock Positions
foreach ($products as $key => $product) {
$products[$key]['stock_position'] = $productRepository->getNamedStockPositions($product['stock_position']);
}
if ($listOrderBy[$order_by] == 'stock_position') {
if ($order_type == 'DESC') {
usort($products, function ($a, $b) {
return strcmp($b['stock_position'], $a['stock_position']);
});
} else {
usort($products, function ($a, $b) {
return strcmp($a['stock_position'], $b['stock_position']);
});
}
}
// Product Tags
$productTags = [];
$productTagsResults = $this->getEntityManager()->getRepository(ProductTag::class)->findAll();
foreach ($productTagsResults as $productTag) {
$productTags[$productTag->getId()] = [
'title' => $productTag->getTitle(),
'color' => $productTag->getColor(),
];
}
return [
'products' => $products,
'count' => $counts,
'pagination' => [
'page' => $page,
'limit' => $limit,
'total' => $total,
],
'productTags' => $productTags,
];
}
/**
* @param int $listParam
* @param int $domainId
* @return string
*/
private function getWhereFilter(int $listParam, int $domainId): string
{
// out_of_stock_status ?
$pre = 'pm' . $domainId;
if ($listParam == 1) { // On web
return " AND p.available > 0 AND {$pre}.active = 1 AND {$pre}.listed = 1 AND {$pre}.orderable = 1 AND {$pre}.price != 0";
}
if ($listParam == 2) { // From link
return " AND p.available > 0 AND {$pre}.active = 0 AND {$pre}.listed = 1 AND {$pre}.orderable = 1 AND {$pre}.price != 0";
}
if ($listParam == 3) { // None
return " AND (p.available <= 0 OR {$pre}.active = 0 OR {$pre}.listed = 0 OR {$pre}.orderable = 0 OR {$pre}.price = 0)";
}
return '';
}
/**
* @param $products
* @param $fistRow
* @return string
*/
public function getCSVListOfProducts($products, $fistRow = true): string
{
$entityManager = $this->getEntityManager();
$domains = $entityManager->createQuery('SELECT d
FROM AtechNetCoreBundle:Domain d
WHERE d.id IN (' . implode(',', Domain::DOMAINS_WITH_WEBSITE) . ')')
->getResult();
$firsRow = [
'Code',
'Container?',
'Type',
'Brand',
'Name',
'Processor',
'Display size',
'Display resolution',
'RAM',
'HDD',
'SSD',
'Video card',
'Chassis',
'OS',
'Keyboard language',
'Touchscreen',
'Webcam',
'Quality',
'Available',
'Stock position',
'Last buying price (EUR) no VAT',
'Average buying price (EUR) no VAT',
'WS SK (EUR) with VAT',
'WS SK DISC (EUR) with VAT',
'WS SK fLash sale START',
'WS SK fLash sale END',
'WS HU (HUF) with VAT',
'WS HU DISC (HUF) with VAT',
'WS HU fLash sale START',
'WS HU fLash sale END',
'WS CZ (CZK) with VAT',
'WS CZ DISC (CZK) with VAT',
'WS CZ fLash sale START',
'WS CZ fLash sale END',
'WS EU (EUR) without VAT',
'WS EU DISC (EUR) without VAT',
'WS EU fLash sale START',
'WS EU fLash sale END',
'RT SK (EUR) with VAT',
'RT SK DISC (EUR) with VAT',
'RT SK fLash sale START',
'RT SK fLash sale END',
'RT HU (HUF) with VAT',
'RT HU DISC (HUF) with VAT',
'RT HU fLash sale START',
'RT HU fLash sale END',
'RT CZ (CZK) with VAT',
'RT CZ DISC (CZK) with VAT',
'RT CZ fLash sale START',
'RT CZ fLash sale END',
'RT EU (EUR) with VAT',
'RT EU DISC (EUR) with VAT',
'RT EU fLash sale START',
'RT EU fLash sale END',
'Keyboard language',
'WS SK DISCOUNTED',
'WS HU DISCOUNTED',
'WS CZ DISCOUNTED',
'WS EU DISCOUNTED',
'RT SK DISCOUNTED',
'RT HU DISCOUNTED',
'RT CZ DISCOUNTED',
'RT EU DISCOUNTED',
'WS SK ONLINE',
'WS HU ONLINE',
'WS CZ ONLINE',
'WS EU ONLINE',
'RT SK ONLINE',
'RT HU ONLINE',
'RT CZ ONLINE',
'RT EU ONLINE',
'LAST SALE',
'BADGES',
'PRODUCT TAGS',
'Product in stock',
'Container-child? & NOT-ONLINE',
];
$lines = [];
if ($fistRow) {
$lines[] = implode("\t", $firsRow);
}
/**
* @var Product $product
* @var ProductAttribute $prodAttr
*/
if (count($products) > 0) {
$productRepo = $entityManager->getRepository(Product::class);
$productList = is_array($products) ? array_values($products) : $products->toArray();
// Pre-warm lazy collections — eliminates N+1 per product
$entityManager->createQuery(
'SELECT p, pa, o, a FROM AtechNetCoreBundle:Product p
LEFT JOIN p.productAttributes pa LEFT JOIN pa.option o LEFT JOIN o.attribute a
WHERE p IN (:products)'
)->setParameter('products', $productList)->getResult();
$entityManager->createQuery(
'SELECT p, pm, d FROM AtechNetCoreBundle:Product p
LEFT JOIN p.metas pm LEFT JOIN pm.domain d
WHERE p IN (:products)'
)->setParameter('products', $productList)->getResult();
$entityManager->createQuery(
'SELECT p, pt FROM AtechNetCoreBundle:Product p
LEFT JOIN p.tags pt WHERE p IN (:products)'
)->setParameter('products', $productList)->getResult();
// Batch container-child check — replaces findBy() per product
$productIds = array_map(fn ($p) => $p->getId(), $productList);
$containerChildSet = array_flip(array_column(
$entityManager->createQuery(
'SELECT IDENTITY(pc.child) AS childId FROM AtechNetCoreBundle:ProductContainer pc
WHERE pc.child IN (:ids)'
)->setParameter('ids', $productIds)->getScalarResult(),
'childId'
));
// Batch keyboard language for type-52 products — replaces per-product DQL query
$keyboardLangMap = [];
$type52Ids = array_values(array_map(
fn ($p) => $p->getId(),
array_filter($productList, fn ($p) => $p->getType()->getId() === 52)
));
if ($type52Ids) {
foreach ($entityManager->createQuery(
'SELECT IDENTITY(pa.product) AS pid, o.title AS title
FROM AtechNetCoreBundle:ProductAttribute pa
LEFT JOIN pa.option o LEFT JOIN o.attribute a
WHERE a.id = :attr AND pa.product IN (:ids)'
)->setParameters(['attr' => 94, 'ids' => $type52Ids])->getScalarResult() as $row) {
$keyboardLangMap[(int) $row['pid']] = $row['title'];
}
}
foreach ($products as $product) {
$specAttributes = $product->getSpecificationProductAttributeOptions();
$processor = $ram = $hdd = $ssd = $videoCard = $chassis = $os = $keyboardLang = $displaySize = $touchscreen = $webcam = $displayResolution = '';
foreach ($specAttributes as $prodAttr) {
switch ($prodAttr->getOption()->getAttribute()->getId()) {
case 1:
$chassis = $prodAttr->getOption()->getTitle();
break;
case 3:
case 4:
$ram = $prodAttr->getOption()->getTitle();
break;
case 6:
case 7:
case 8:
case 9:
$hdd = $prodAttr->getOption()->getFullTitle();
break;
case 5:
$ssd = $prodAttr->getOption()->getFullTitle();
break;
case 12:
$displaySize = $prodAttr->getOption()->getTitle();
break;
case 139:
$touchscreen = $prodAttr->getOption()->getTitle();
break;
case 19:
$webcam = $prodAttr->getOption()->getTitle();
break;
case 25:
$videoCard .= ($videoCard != '' ? ', ' : '') . $prodAttr->getOption()->getTitle();
break;
case 32:
case 33:
$processor = $prodAttr->getOption()->getTitle();
break;
case 41:
$os = $prodAttr->getOption()->getTitle();
break;
case 63:
$displayResolution = $prodAttr->getOption()->getTitle();
break;
case 94:
$keyboardLang = $prodAttr->getoption()->getTitle();
break;
}
}
$WSSKMeta = $product->getDomainMeta($domains[1]);
$WSHUMeta = $product->getDomainMeta($domains[3]);
$WSCZMeta = $product->getDomainMeta($domains[4]);
$WSEUMeta = $product->getDomainMeta($domains[5]);
$RTSKMeta = $product->getDomainMeta($domains[0]);
$RTHUMeta = $product->getDomainMeta($domains[2]);
$RTCZMeta = $product->getDomainMeta($domains[6]);
$RTEUMeta = $product->getDomainMeta($domains[7]);
$productTags = '';
foreach ($product->getTags() as $productTag) {
if ($productTags != '') {
$productTags .= ',';
}
$productTags .= $productTag->getTitle();
}
$productInStock = $product->getInStock();
$isContainerChild = isset($containerChildSet[$product->getId()]);
$isContainerChildAndNotAvailableOnWeb = $isContainerChild && (
($WSSKMeta->isActive() && $WSSKMeta->isListed() && $WSSKMeta->isOrderable()) ||
($WSHUMeta->isActive() && $WSHUMeta->isListed() && $WSHUMeta->isOrderable()) ||
($WSCZMeta->isActive() && $WSCZMeta->isListed() && $WSCZMeta->isOrderable()) ||
($WSEUMeta->isActive() && $WSEUMeta->isListed() && $WSEUMeta->isOrderable()) ||
($RTSKMeta->isActive() && $RTSKMeta->isListed() && $RTSKMeta->isOrderable()) ||
($RTHUMeta->isActive() && $RTHUMeta->isListed() && $RTHUMeta->isOrderable()) ||
($RTCZMeta->isActive() && $RTCZMeta->isListed() && $RTCZMeta->isOrderable()) ||
($RTEUMeta->isActive() && $RTEUMeta->isListed() && $RTEUMeta->isOrderable())
) ? '1' : '0';
$row = [
$product->getCode(),
$product->isContainer() ? '1' : '0',
$product->getType()->getTitle(),
$product->getVendor()->getName(),
$product->getName(),
$processor,
$displaySize,
$displayResolution,
$ram,
$hdd,
$ssd,
$videoCard,
$chassis,
$os,
$keyboardLang,
$touchscreen,
$webcam,
$product->getQualityStatusTitle($product->getQualityStatus()),
$product->getAvailable(),
$productRepo->getNamedStockPositions($product->getStockPosition()),
str_replace('.', ',', $product->getLastPrice()),
str_replace('.', ',', $product->getAvgPrice()),
str_replace('.', ',', $WSSKMeta->getDomainVatPrice()),
str_replace('.', ',', $WSSKMeta->getDomainVatDiscount()),
$WSSKMeta->getSaleStart() ? $WSSKMeta->getSaleStart()->format('Y.m.d, H:i') : '',
$WSSKMeta->getSaleEnd() ? $WSSKMeta->getSaleEnd()->format('Y.m.d, H:i') : '',
str_replace('.', ',', $WSHUMeta->getDomainVatPrice()),
str_replace('.', ',', $WSHUMeta->getDomainVatDiscount()),
$WSHUMeta->getSaleStart() ? $WSHUMeta->getSaleStart()->format('Y.m.d, H:i') : '',
$WSHUMeta->getSaleEnd() ? $WSHUMeta->getSaleEnd()->format('Y.m.d, H:i') : '',
str_replace('.', ',', $WSCZMeta->getDomainVatPrice()),
str_replace('.', ',', $WSCZMeta->getDomainVatDiscount()),
$WSCZMeta->getSaleStart() ? $WSCZMeta->getSaleStart()->format('Y.m.d, H:i') : '',
$WSCZMeta->getSaleEnd() ? $WSCZMeta->getSaleEnd()->format('Y.m.d, H:i') : '',
str_replace('.', ',', $WSEUMeta->getDomainPrice()),
str_replace('.', ',', $WSEUMeta->getDomainDiscount()),
$WSEUMeta->getSaleStart() ? $WSEUMeta->getSaleStart()->format('Y.m.d, H:i') : '',
$WSEUMeta->getSaleEnd() ? $WSEUMeta->getSaleEnd()->format('Y.m.d, H:i') : '',
str_replace('.', ',', $RTSKMeta->getDomainVatPrice()),
str_replace('.', ',', $RTSKMeta->getDomainVatDiscount()),
$RTSKMeta->getSaleStart() ? $RTSKMeta->getSaleStart()->format('Y.m.d, H:i') : '',
$RTSKMeta->getSaleEnd() ? $RTSKMeta->getSaleEnd()->format('Y.m.d, H:i') : '',
str_replace('.', ',', $RTHUMeta->getDomainVatPrice()),
str_replace('.', ',', $RTHUMeta->getDomainVatDiscount()),
$RTHUMeta->getSaleStart() ? $RTHUMeta->getSaleStart()->format('Y.m.d, H:i') : '',
$RTHUMeta->getSaleEnd() ? $RTHUMeta->getSaleEnd()->format('Y.m.d, H:i') : '',
str_replace('.', ',', $RTCZMeta->getDomainVatPrice()),
str_replace('.', ',', $RTCZMeta->getDomainVatDiscount()),
$RTCZMeta->getSaleStart() ? $RTCZMeta->getSaleStart()->format('Y.m.d, H:i') : '',
$RTCZMeta->getSaleEnd() ? $RTCZMeta->getSaleEnd()->format('Y.m.d, H:i') : '',
str_replace('.', ',', $RTEUMeta->getDomainVatPrice()),
str_replace('.', ',', $RTEUMeta->getDomainVatDiscount()),
$RTEUMeta->getSaleStart() ? $RTEUMeta->getSaleStart()->format('Y.m.d, H:i') : '',
$RTEUMeta->getSaleEnd() ? $RTEUMeta->getSaleEnd()->format('Y.m.d, H:i') : '',
$product->getType()->getId() !== 52 ? '-' : ($keyboardLangMap[$product->getId()] ?? $productRepo->getProductKeyboardLanguageSetting($product)),
$WSSKMeta->isDiscounted() ? '1' : '0',
$WSHUMeta->isDiscounted() ? '1' : '0',
$WSCZMeta->isDiscounted() ? '1' : '0',
$WSEUMeta->isDiscounted() ? '1' : '0',
$RTSKMeta->isDiscounted() ? '1' : '0',
$RTHUMeta->isDiscounted() ? '1' : '0',
$RTCZMeta->isDiscounted() ? '1' : '0',
$RTEUMeta->isDiscounted() ? '1' : '0',
$product->getAvailable() > 0 && $WSSKMeta->isActive() && $WSSKMeta->isListed() && $WSSKMeta->isOrderable() ? '1' : '0',
$product->getAvailable() > 0 && $WSHUMeta->isActive() && $WSHUMeta->isListed() && $WSHUMeta->isOrderable() ? '1' : '0',
$product->getAvailable() > 0 && $WSCZMeta->isActive() && $WSCZMeta->isListed() && $WSCZMeta->isOrderable() ? '1' : '0',
$product->getAvailable() > 0 && $WSEUMeta->isActive() && $WSEUMeta->isListed() && $WSEUMeta->isOrderable() ? '1' : '0',
$product->getAvailable() > 0 && $RTSKMeta->isActive() && $RTSKMeta->isListed() && $RTSKMeta->isOrderable() ? '1' : '0',
$product->getAvailable() > 0 && $RTHUMeta->isActive() && $RTHUMeta->isListed() && $RTHUMeta->isOrderable() ? '1' : '0',
$product->getAvailable() > 0 && $RTCZMeta->isActive() && $RTCZMeta->isListed() && $RTCZMeta->isOrderable() ? '1' : '0',
$product->getAvailable() > 0 && $RTEUMeta->isActive() && $RTEUMeta->isListed() && $RTEUMeta->isOrderable() ? '1' : '0',
!is_null($product->getLastSale()) ? $product->getLastSale()->format('Y.m.d, H:i') : '-',
implode(',', $product->getBadges()),
$productTags,
$productInStock,
$isContainerChildAndNotAvailableOnWeb,
];
$lines[] = implode("\t", $row);
}
}
return implode("\n", $lines);
}
/**
* @param array $items
* @param bool $printFirstRow
* @return string
*/
public function getCSVListOfStockItems(array $items, bool $printFirstRow): string
{
$firsRow = [
'First in',
'Shipment',
'Code',
'Serial number',
'Product type',
'Brand',
'Name',
'Available quantity',
'In stock quantity',
'Stock position',
'Last buy no VAT (EUR)',
'Average buy no VAT (EUR)',
];
$csv = [];
if ($printFirstRow) {
$csv[] = $firsRow;
}
/**
* @var Product $product
* @var ProductAttribute $prodAttr
*/
if (count($items) > 0) {
/** @var StockItem $item */
foreach ($items as $item) {
$product = $item->getProduct();
$row = [
$item->getFirstMovement()->getCreated()->format('Y-m-d'),
$item->getFirstMovement()?->getShipment()?->getShipmentId() ?? '-',
$product->getCode(),
$item->getSerialNumber(),
$product->getType()->getTitle(),
$product->getVendor()->getName(),
$product->getName(),
$product->getAvailable(),
$product->getInStock(),
$item->getLastKnownStockPosition()?->getName() ?? 'unknown',
$item->getPurchasePriceCalculated(),
$product->getAvgPrice(),
];
$csv[] = $row;
}
}
$output = '';
$fp = fopen('php://temp', 'r+');
foreach ($csv as $row) {
fputcsv($fp, $row, "\t", '"', "\\");
}
rewind($fp);
$output = stream_get_contents($fp);
fclose($fp);
return $output;
}
/**
* @param string $productCodeList
* @return string
* @throws Exception
*/
public function getCSVListOfAvailableStockItems(string $productCodeList): string
{
$entityManager = $this->getEntityManager();
$connection = $entityManager->getConnection();
$query = "SELECT
si.aiken_code,
p.code AS product_code,
p.cache_description AS config,
si.purchase_price,
sp.name AS stock_position,
si.note,
si.error_codes,
si.available AS is_available
FROM stock_items si
LEFT JOIN stock_movement_item smiOldest ON smiOldest.id = (
SELECT smi2.stockmovementitem_id
FROM stock_movement_items_stock_items smi2
LEFT JOIN stock_movement_item smiC ON smi2.stockmovementitem_id = smiC.id
WHERE smi2.stockitem_id = si.id
ORDER BY smiC.created DESC
LIMIT 1
)
LEFT JOIN stock_movement_item smiIn ON smiIn.id = (
SELECT smi3.stockmovementitem_id
FROM stock_movement_items_stock_items smi3
LEFT JOIN stock_movement_item smiB ON smi3.stockmovementitem_id = smiB.id
WHERE smi3.stockitem_id = si.id
ORDER BY smiB.created ASC
LIMIT 1
)
LEFT JOIN stock_movements smOldest ON smOldest.id = smiOldest.stock_movement_id
LEFT JOIN stock_movements smIn ON smIn.id = smiIn.stock_movement_id
INNER JOIN products p ON p.id = smiOldest.product_id AND p.id IN($productCodeList)
LEFT JOIN stock_positions AS sp ON ( sp.id = si.last_stock_position_id )
WHERE si.in_stock = '1'
AND smOldest.type != 2
";
$stockItemList = $connection->executeQuery($query);
$stockItemList = $stockItemList->fetchAllAssociative();
$firsRow = [
'aiken_code',
'product_code',
'config',
'purchase_price',
'stock_position',
'is_available',
'note',
'errors',
];
$csv[] = $firsRow;
$allErrorCode = $entityManager->getRepository(ErrorCode::class)->findAll();
$errorList = [];
/**
* @var ErrorCode $errorCode
*/
foreach ($allErrorCode as $errorCode) {
$errorList[$errorCode->getCode()] = $errorCode->getHuText();
}
foreach ($stockItemList as $stockItemData) {
$stockItemErrorCodeList = explode(' ', $stockItemData['error_codes']);
$stockItemErrorListHu = [];
foreach ($stockItemErrorCodeList as $errorCode) {
if ($errorCode != '' && !empty($errorList[$errorCode])) {
$stockItemErrorListHu[] = $errorList[$errorCode];
}
}
$csv[] = [
$stockItemData['aiken_code'],
$stockItemData['product_code'],
$stockItemData['config'],
$stockItemData['purchase_price'],
$stockItemData['stock_position'],
$stockItemData['is_available'] == 1 ? 'true' : 'false',
preg_replace('/\s+/', ' ', $stockItemData['note']),
implode(' | ', $stockItemErrorListHu),
];
}
$output = '';
foreach ($csv as $row) {
$output .= implode(';', $row) . "\n";
}
return $output;
}
/**
* @param ProductMeta $productMeta
* @return void
* @throws NotSupported
* @throws OptimisticLockException
*/
public function updateAllContainerChildPrice(ProductMeta $productMeta): void
{
$entityManager = $this->getEntityManager();
$productContainers = $entityManager->getRepository(ProductContainer::class)->findBy([
'child' => $productMeta->getProduct(),
'allowPriceSyncFromChildProduct' => true,
]);
$domain = $productMeta->getDomain();
foreach ($productContainers as $container) {
$containerItemMetaToUpdate = $entityManager->getRepository(ProductContainerMeta::class)->findOneBy([
'productContainer' => $container,
'domain' => $domain,
]);
$containerItemMetaToUpdate->setPrice($productMeta->getPrice());
$containerItemMetaToUpdate->setDiscount($productMeta->getDiscount());
$entityManager->persist($containerItemMetaToUpdate);
$entityManager->flush($containerItemMetaToUpdate);
$this->syncContainerPricesByDomain($container, $domain);
}
}
/**
* @param ProductContainer $productContainer
* @param Domain $domain
* @param bool $skipFlush
* @return void
* @throws Exception
*/
public function syncContainerPricesByDomain(ProductContainer $productContainer, Domain $domain, bool $skipFlush = false): void
{
$conn = $this->getEntityManager()->getConnection();
$productId = $productContainer->getProduct()->getId();
$domainId = $domain->getId();
$sums = $conn->executeQuery(
'SELECT COALESCE(SUM(pcm.price), 0) AS total_price, COALESCE(SUM(pcm.discount), 0) AS total_discount
FROM product_container pc
INNER JOIN product_container_metas pcm ON pcm.product_container_id = pc.id
WHERE pc.product_id = :productId AND pcm.domain_id = :domainId',
['productId' => $productId, 'domainId' => $domainId]
)->fetchAssociative();
$conn->executeStatement(
'UPDATE products__metas SET price = :price, discount = :discount
WHERE product_id = :productId AND domain_id = :domainId',
[
'price' => $sums['total_price'],
'discount' => $sums['total_discount'],
'productId' => $productId,
'domainId' => $domainId,
]
);
if (!$skipFlush) {
$this->getEntityManager()->flush();
}
}
/**
* @param Basket $basket
* @return bool
*/
public function isBasketIncludesGamerProduct(Basket $basket): bool
{
/**
* @var BasketItem $bi
*/
foreach ($basket->getBasketItems() as $bi) {
if ($bi->getProduct()->hasBadgeItem('gamer')) {
return true;
}
}
return false;
}
/**
* @param int $osAttributeOptionId
* @param int $attributeId
* @return void
* @var Product $product
*/
public function checkAndUpdateOSAttribute(Product $product, int $osAttributeOptionId, int $attributeId): void
{
if ($osAttributeOptionId == AttributeOption::ATTRIBUTE_OPTION_ID_WINDOWS_11) {
if (!$product->isWindows11Compatible()) {
return;
}
}
if (str_contains(strtolower($product->getFullTitle()), 'chromebook')) {
return;
}
if ($osAttributeOptionId == AttributeOption::ATTRIBUTE_OPTION_ID_MAC_OS) {
if ($product->getVendor()->getId() != Vendor::VENDOR_ID_APPLE) {
return;
}
if (!in_array($product->getType()->getId(), [ProductType::PRODUCT_TYPE_ID_PC, ProductType::PRODUCT_TYPE_ID_NOTEBOOK])) {
return;
}
}
$productAttributeOperatingSystem = $this->getEntityManager()
->createQuery(
'SELECT pa
FROM AtechNetCoreBundle:ProductAttribute pa
INNER JOIN pa.option o WITH IDENTITY(o.attribute) = ' . $attributeId . '
WHERE IDENTITY(pa.product) = ' . $product->getId()
)
->getResult();
$selectedOperationSystem = count($productAttributeOperatingSystem) > 0 ? $productAttributeOperatingSystem[0]->getOption() : false;
if ($selectedOperationSystem) {
if ($selectedOperationSystem->getId() != $osAttributeOptionId) {
$em = $this->getEntityManager();
$em->remove($productAttributeOperatingSystem[0]);
$em->flush();
$this->addProductAttribute($product, $osAttributeOptionId);
}
return;
}
$this->addProductAttribute($product, $osAttributeOptionId);
}
/**
* @param Product $product
* @param int $attributeOptionId
* @return void
*/
private function addProductAttribute(Product $product, int $attributeOptionId): void
{
$entityManager = $this->getEntityManager();
$newProductAttribute = new ProductAttribute();
$attributeOption = $entityManager->getRepository(AttributeOption::class)->find($attributeOptionId);
$newProductAttribute->setProduct($product);
$newProductAttribute->setOption($attributeOption);
$entityManager->persist($newProductAttribute);
$entityManager->flush();
}
/**
* @param int $type
* @return null|array
*/
public function getAllProductTagsByType(int $type): ?array
{
$productTags = $this->getEntityManager()->createQuery('SELECT pt
FROM AtechNetCoreBundle:ProductTag pt
WHERE pt.type = :type')
->setParameters([
'type' => $type,
])
->getResult();
$formattedProductTagList = [];
/**
* @var ProductTag $productTags
*/
foreach ($productTags as $productTag) {
$formattedProductTagList[$productTag->getId()] = $productTag->getTitle();
}
return $formattedProductTagList;
}
/**
* @param array $attributes
* @return void
*/
public function getCpuGrouppedProductUAttributeArray(array &$attributes): void
{
$oldAttributes = $attributes;
if (!(array_key_exists(Attribute::ATTRIBUTE_ID_CPU_GENERATION, $attributes) || array_key_exists(Attribute::ATTRIBUTE_ID_CPU_PC_FAMILY, $attributes) || array_key_exists(Attribute::ATTRIBUTE_ID_CPU_NB_FAMILY, $attributes))) {
return;
}
if (!(array_key_exists(Attribute::ATTRIBUTE_ID_CPU_PC, $attributes) || array_key_exists(Attribute::ATTRIBUTE_ID_CPU_NB, $attributes))) {
return;
}
$cpuAttribute = array_key_exists(Attribute::ATTRIBUTE_ID_CPU_PC, $attributes) ? $attributes[Attribute::ATTRIBUTE_ID_CPU_PC] : $attributes[Attribute::ATTRIBUTE_ID_CPU_NB];
$cpuGenerationAttribute = array_key_exists(Attribute::ATTRIBUTE_ID_CPU_GENERATION, $attributes) ? $attributes[Attribute::ATTRIBUTE_ID_CPU_GENERATION] : null;
$cpuFamilyAttribute = array_key_exists(Attribute::ATTRIBUTE_ID_CPU_PC_FAMILY, $attributes) ? $attributes[Attribute::ATTRIBUTE_ID_CPU_PC_FAMILY] : (array_key_exists(Attribute::ATTRIBUTE_ID_CPU_NB_FAMILY, $attributes) ? $attributes[Attribute::ATTRIBUTE_ID_CPU_NB_FAMILY] : null);
unset($oldAttributes[Attribute::ATTRIBUTE_ID_CPU_PC]);
unset($oldAttributes[Attribute::ATTRIBUTE_ID_CPU_NB]);
unset($oldAttributes[Attribute::ATTRIBUTE_ID_CPU_PC_FAMILY]);
unset($oldAttributes[Attribute::ATTRIBUTE_ID_CPU_NB_FAMILY]);
unset($oldAttributes[Attribute::ATTRIBUTE_ID_CPU_GENERATION]);
$attributes = array_merge([$cpuAttribute, $cpuFamilyAttribute, $cpuGenerationAttribute], $oldAttributes);
}
/**
* @param Product $product
* @return bool
*/
public function isFreeEsetCompatible(Product $product): bool
{
if (!in_array($product->getType()->getId(), ProductType::getFreeEsetCompatibleList())) {
return false;
}
if (in_array('ChromeBook', $product->getBadges()) || str_contains(strtolower($product->getFullTitle()), 'chromebook')) {
return false;
}
if ($product->getVendor()->getName() == 'Apple') {
return false;
}
/**
* @var ProductAttribute $productAttribute
*/
foreach ($product->getConfigurationAttributes() as $productAttribute) {
if ($productAttribute->getOption()->getAttribute()->getId() == Attribute::ATTRIBUTE_ID_CPU_GENERATION) {
$selectedOption = $productAttribute->getOption();
$cpuGenerationNumber = (int)str_replace('. Generation', '', $selectedOption->getTitle());
return $cpuGenerationNumber >= 6;
}
}
return false;
}
public function getAllStockPositionList()
{
$query = $this->getEntityManager()->createQuery('SELECT DISTINCT sp.name FROM AtechNetCoreBundle:StockItemPosition sip JOIN AtechNetCoreBundle:StockPosition sp WITH sp.id = sip.stockPosition');
$positionListArrays = $query->getResult();
$positionList = [];
foreach ($positionListArrays as $arrayItem) {
$positionList[] = $arrayItem['name'];
}
return $positionList;
}
/**
* @param ProductMeta $productMeta
* @param bool $isDiscounted
* @return void
*/
public function syncContainerChildMetaPrice(&$entityManager, ProductMeta $productMeta, float $price, bool $isDiscounted = false, bool $skipFlush = false): void
{
$query = $entityManager->createQuery(
'SELECT pcMeta
FROM AtechNetCoreBundle:ProductContainerMeta pcMeta
INNER JOIN pcMeta.productContainer pc WITH pc.child = :metaProduct AND pc.allowPriceSyncFromChildProduct = 1
WHERE pcMeta.domain = :domain'
)
->setParameters([
'metaProduct' => $productMeta->getProduct(),
'domain' => $productMeta->getDomain(),
]);
$conn = $entityManager->getConnection();
$column = $isDiscounted ? 'discount' : 'price';
/**
* @var ProductContainerMeta $containerMeta
*/
foreach ($query->getResult() as $containerMeta) {
$conn->executeStatement(
"UPDATE product_container_metas SET {$column} = :price WHERE id = :id",
['price' => $price, 'id' => $containerMeta->getId()]
);
$this->syncContainerPricesByDomain($containerMeta->getProductContainer(), $containerMeta->getDomain(), $skipFlush);
}
}
/**
* @param Product $product
* @return void
*/
public function checkAndUpdateRAMAttributes(Product $product): void
{
$ramProductAttributeIds = [
Attribute::ATTRIBUTE_ID_RAM_PC,
Attribute::ATTRIBUTE_ID_RAM_NB,
];
/**
* @var ProductAttribute $ramProductAttribute
*/
$ramProductAttribute = $product->getProductAttributes()->filter(
function (ProductAttribute $pa) use ($ramProductAttributeIds) {
return in_array($pa->getOption()->getAttribute()->getId(), $ramProductAttributeIds);
}
)->first();
if (!$ramProductAttribute) {
return;
}
$sizeMatches = $ddrMatches = [];
preg_match('/([0-9]+?GB)/', $ramProductAttribute->getOption()->getTitle(), $sizeMatches);
preg_match('/(DDR[0-9])/', $ramProductAttribute->getOption()->getTitle(), $ddrMatches);
if (count($ddrMatches) < 1 && count($sizeMatches) < 1) {
return;
}
$ramSize = (int)str_replace('GB', '', $sizeMatches[0]);
$ddrInfo = $ddrMatches[0] ?? '';
if ($ramSize < 2 || $ramProductAttribute->getMultiplier() > 0) {
return;
}
switch ($ramProductAttribute->getOption()->getAttribute()->getId()) {
case Attribute::ATTRIBUTE_ID_RAM_PC:
$ramTypeAttributeIdToAdd = Attribute::ATTRIBUTE_ID_RAM_PC_TYPE;
break;
case Attribute::ATTRIBUTE_ID_RAM_NB:
$ramTypeAttributeIdToAdd = Attribute::ATTRIBUTE_ID_RAM_NB_TYPE;
break;
}
$ramData = [
'size' => $ramSize,
'type' => $ddrInfo,
'ramTypeAttributeIdToAdd' => $ramTypeAttributeIdToAdd,
];
$productAttributeOptionsToAdd = $this->prepareAttributeOptionsByRamData($ramData);
$alreadyAddedSizeAndTypeRamAttribute = $this->getAddedSizeAndTypeRamAttribute($product);
$entityManager = $this->getEntityManager();
/**
* @var AttributeOption $attributeOption
*/
foreach ($productAttributeOptionsToAdd as $attributeId => $attributeOption) {
if (count($alreadyAddedSizeAndTypeRamAttribute) < 1) {
$this->addProductAttribute($product, $attributeOption['option']->getId());
continue;
}
// if same attributes added
if ($alreadyAddedSizeAndTypeRamAttribute[$attributeId] == $attributeOption['option']->getId()) {
continue;
}
// update ProductAttribute
$productAttributeToUpdate = $entityManager->getRepository(ProductAttribute::class)->findOneBy([
'product' => $product,
'option' => $alreadyAddedSizeAndTypeRamAttribute[$attributeId],
]);
if ($productAttributeToUpdate) {
$productAttributeToUpdate->setOption($attributeOption['option']);
$entityManager->persist($productAttributeToUpdate);
}
}
$entityManager->flush();
}
/**
* @param array $ramData
* @return null|array
*/
private function prepareAttributeOptionsByRamData(array $ramData): ?array
{
$entityManager = $this->getEntityManager();
$attributeOptionsToReturn = [];
$ramSizeAttributeOption = $entityManager->getRepository(AttributeOption::class)->findOneBy([
'attribute' => Attribute::ATTRIBUTE_ID_RAM_SIZE,
'title' => "{$ramData['size']}GB",
]);
if ($ramSizeAttributeOption) {
$attributeOptionsToReturn[Attribute::ATTRIBUTE_ID_RAM_SIZE] = [
'title' => $ramSizeAttributeOption->getTitle(),
'option' => $ramSizeAttributeOption,
];
}
$ramTypeAttributeOption = $entityManager->getRepository(AttributeOption::class)->findOneBy([
'attribute' => $ramData['ramTypeAttributeIdToAdd'],
'title' => $ramData['type'],
]);
if ($ramTypeAttributeOption) {
$attributeOptionsToReturn[$ramData['ramTypeAttributeIdToAdd']] = [
'title' => $ramTypeAttributeOption->getTitle(),
'option' => $ramTypeAttributeOption,
];
}
return $attributeOptionsToReturn;
}
/**
* @param Product $product
* @return array
*/
private function getAddedSizeAndTypeRamAttribute(Product $product): array
{
$sizeAndTypeRamAttributeList = [
Attribute::ATTRIBUTE_ID_RAM_SIZE,
Attribute::ATTRIBUTE_ID_RAM_PC_TYPE,
Attribute::ATTRIBUTE_ID_RAM_NB_TYPE,
];
$addedRamAttributes = $product->getProductAttributes()->filter(
function (ProductAttribute $pa) use ($sizeAndTypeRamAttributeList) {
return in_array($pa->getOption()->getAttribute()->getId(), $sizeAndTypeRamAttributeList);
}
);
$ramAttributes = [];
/**
* @var ProductAttribute $productAttribute
*/
foreach ($addedRamAttributes as $productAttribute) {
$ramAttributes[$productAttribute->getOption()->getAttribute()->getId()] = $productAttribute->getOption()->getId();
}
return $ramAttributes;
}
/**
* @return array
*/
public function exportQuarantineSerialNumbers(): array
{
$entityManager = $this->getEntityManager();
$output = [];
$output[] = 'ID;Code;Type;Vendor;Product;Serial;Aiken;Stock Item Created;Movement created;MovementID;MovementComment';
$sql = <<<SQL
SELECT
`p`.`id` AS `productId`,
`p`.`code` AS `productCode`,
`pt`.`title` AS `productType`,
`v`.`name` AS `productVendor`,
`p`.`name` AS `productName`,
`sm`.`id` AS `movementId`,
`sm`.`note` AS `movementNote`,
`si`.`serial_number` AS `serialNumber`,
`si`.`aiken_code` AS `aikenCode`,
`si`.`created` AS `stockItemCreated`,
`sm`.`created` AS `movementCreated`,
`si`.`purchase_price` AS `purchasePrice`
FROM `stock_movements` AS `sm`
INNER JOIN `stock_movement_item` AS `smi` ON ( `smi`.`stock_movement_id` = `sm`.`id` )
INNER JOIN `stock_movement_items_stock_items` AS `smisi` ON ( `smisi`.`stockmovementitem_id` = `smi`.`id` )
INNER JOIN `stock_items` AS `si` ON ( `si`.`id` = `smisi`.`stockitem_id` )
INNER JOIN `products` AS `p` ON ( `p`.`id` = `smi`.`product_id` )
LEFT JOIN `product_types` AS `pt` ON ( `pt`.`id` = `p`.`type_id` )
LEFT JOIN `vendors` AS `v` ON ( `v`.`id` = `p`.`vendor_id` )
WHERE `sm`.`sub_type` = :subType
ORDER BY `sm`.`created` ASC
SQL;
$quarantineMovementItems = $entityManager->getConnection()->executeQuery($sql, [
'subType' => StockMovement::SUB_TYPE_NEUTRAL_QUARANTINE
]);
while ( $item = $quarantineMovementItems->fetchAssociative() ) {
$output[$item['serialNumber']] = implode(';', [
$item['productId'],
$item['productCode'],
$item['productType'],
$item['productVendor'],
$item['productName'],
$item['serialNumber'],
$item['aikenCode'],
$item['stockItemCreated'],
$item['movementCreated'],
$item['movementId'],
( !is_null($item['movementNote']) ? trim(preg_replace('/\s+/', ' ', $item['movementNote'])) : '' ),
$item['purchasePrice']
]);
}
$params = [
'name' => '%QUARANTINE%',
'movementOut' => StockMovement::TYPE_OUT
];
$types = [
'name' => ParameterType::STRING,
'movementOut' => ParameterType::INTEGER
];
$sql = <<<SQL
SELECT
`p`.`id` AS `productId`,
`p`.`code` AS `productCode`,
`pt`.`title` AS `productType`,
`v`.`name` AS `productVendor`,
`p`.`name` AS `productName`,
`sm`.`created` AS `movementCreated`,
`sm`.`id` AS `movementId`,
`sm`.`note` AS `movementNote`,
`sm`.`created` AS `movementCreated`,
`si`.`serial_number` AS `serialNumber`,
`si`.`aiken_code` AS `aikenCode`,
`si`.`created` AS `stockItemCreated`,
`si`.`purchase_price` AS `purchasePrice`
FROM `stock_items` AS `si`
INNER JOIN `stock_movement_items_stock_items` AS `smisi` ON ( `smisi`.`stockitem_id` = `si`.`id` )
INNER JOIN `stock_movement_item` AS `smi` ON ( `smi`.`id` = `smisi`.`stockmovementitem_id` )
INNER JOIN `products` AS `p` ON ( `p`.`id` = `smi`.`product_id` )
LEFT JOIN `product_types` AS `pt` ON ( `pt`.`id` = `p`.`type_id` )
LEFT JOIN `vendors` AS `v` ON ( `v`.`id` = `p`.`vendor_id` )
LEFT JOIN `stock_movement_item` AS `smi2` ON ( `smi2`.`id` = (
SELECT `_smi`.`id` FROM `stock_movement_item` AS `_smi` WHERE `_smi`.`product_id` = `p`.`id` ORDER BY `_smi`.`created` DESC LIMIT 1
))
LEFT JOIN `stock_movements` AS `sm` ON ( `sm`.`id` = `smi`.`stock_movement_id` )
WHERE (
SELECT COUNT(`_sm`.`id`)
FROM `stock_movements` AS `_sm`
WHERE `_sm`.`id` = (
SELECT `__sm`.`id`
FROM `stock_items` AS `_si`
INNER JOIN `stock_movement_items_stock_items` AS `_smisi` ON ( `_smisi`.`stockitem_id` = `_si`.`id` )
INNER JOIN `stock_movement_item` AS `__smi` ON ( `__smi`.`id` = `_smisi`.`stockmovementitem_id` AND `__smi`.`product_id` = `p`.`id` )
INNER JOIN `stock_movements` AS `__sm` ON ( `__sm`.`id` = `__smi`.`stock_movement_id` )
WHERE `_si`.`id` = `si`.`id`
ORDER BY `__sm`.`created` DESC
LIMIT 1
) AND `_sm`.`type` = :movementOut
) = 0 AND `p`.`name` LIKE :name
SQL;
$quarantineProducts = $entityManager->getConnection()->executeQuery($sql, $params, $types);
while ( $item = $quarantineProducts->fetchAssociative() ) {
$output[$item['serialNumber']] = implode(';', [
$item['productId'],
$item['productCode'],
$item['productType'],
$item['productVendor'],
$item['productName'],
$item['serialNumber'],
$item['aikenCode'],
$item['stockItemCreated'],
$item['movementCreated'],
$item['movementId'],
( !is_null($item['movementNote']) ? trim(preg_replace('/\s+/', ' ', $item['movementNote'])) : '' ),
$item['purchasePrice']
]);
}
return $output;
}
/**
* @param string|null $productStockPosition
* @return string
* @throws \Psr\Cache\InvalidArgumentException
*/
public function getNamedStockPositions(?string $productStockPosition): string
{
// Return on null
if (is_null($productStockPosition)) {
return '';
}
// Cache Key
$cacheKey = 'productStockPositions.' . md5($productStockPosition);
// Cache Item
$cacheItem = $this->cache->getItem($cacheKey);
// Return Data from Cache
if ($cacheItem->isHit()) {
// return $cacheItem->get(); // Disable for a while
}
// Entity Manager
$entityManager = $this->getEntityManager();
// Stock Positions
$stockPositions = [];
foreach (explode(',', $productStockPosition) as $stockPositionId) {
$stockPosition = $entityManager->getRepository(StockPosition::class)->find($stockPositionId);
if ($stockPosition) {
$stockPositions[] = $stockPosition->getName();
}
}
// Return Data
$stockPositions = implode(', ', $stockPositions);
// Cache it
$cacheItem->set($stockPositions);
$cacheItem->expiresAfter(31536000); // 1 year
$this->cache->save($cacheItem);
return $stockPositions;
}
/**
* @param ProductTag $productTag
* @return Product|null
*/
public function getAvailableProductByProductTag(ProductTag $productTag): ?Product
{
$entityManager = $this->getEntityManager();
$products = $entityManager->createQuery('SELECT p
FROM AtechNetCoreBundle:Product p
LEFT JOIN p.tags productTag
WHERE productTag.id = :productTagId AND p.available > 0')
->setParameters([
'productTagId' => $productTag->getId(),
])
->getResult();
if (count($products) > 0) {
return $products[0];
}
return null;
}
/**
* @param Product $product
* @return void
*/
public function checkAndUpdatePassmarkValue(Product $product): void
{
if ($product->getCpuMark() > 99) {
return;
}
$cpuProductAttributeIds = [
Attribute::ATTRIBUTE_ID_CPU_PC,
Attribute::ATTRIBUTE_ID_CPU_NB,
Attribute::ATTRIBUTE_ID_CPU_SERVER,
Attribute::ATTRIBUTE_ID_CPU_TABLET,
Attribute::ATTRIBUTE_ID_CPU_PHONE,
];
/**
* @var ProductAttribute $cpuProductAttribute
*/
$cpuProductAttribute = $product->getProductAttributes()->filter(
function (ProductAttribute $pa) use ($cpuProductAttributeIds) {
return in_array($pa->getOption()->getAttribute()->getId(), $cpuProductAttributeIds);
}
)->first();
if (!$cpuProductAttribute) {
return;
}
$cpuTitle = $cpuProductAttribute->getOption()->getTitle();
$product->setCpuMark($this->getCpuMarkByTitle($cpuTitle));
$this->getEntityManager()->flush();
}
/**
* @param string $cpuTitle
* @return int
*/
private function getCpuMarkByTitle(string $cpuTitle): int
{
$csvPath = __DIR__ . '/../../../../web/uploads/cpu_mark_data.csv';
$file = fopen($csvPath, 'r');
$cpuMarkValue = 0;
while (($line = fgetcsv($file, null, ',')) !== false) {
if ($line[0] != 'cpu_title' && $line[0] == $cpuTitle) {
$cpuMarkValue = (int)str_replace([' ', ' '], '', $line[1]);
break;
}
}
fclose($file);
return $cpuMarkValue;
}
/**
* Get Product Sales Chart Data
*
* @param Product $product
* @param string $filterDateStart
* @param string $filterDateEnd
*
* @return array
*/
public function getChartProductSales(Product $product, string $filterDateStart = '', string $filterDateEnd = ''): array
{
// Entity Manager
$entityManager = $this->getEntityManager();
// Filter
$filter = [
'product' => $product->getId(),
'stockMovementTypeOut' => StockMovement::TYPE_OUT,
'subTypeConfigChange' => StockMovement::SUB_TYPE_OUT_CONFIG_CHANGE,
'subTypeOrderConfigChange' => StockMovement::SUB_TYPE_OUT_ORDER_CONFIG_CHANGE,
'subTypeToOtherStock' => StockMovement::SUB_TYPE_OUT_TO_OTHER_STOCK,
'subTypeReturnedToSupplier' => StockMovement::SUB_TYPE_OUT_RETURNED_TO_SUPPLIER,
'domainOther' => Domain::DOMAIN_ID_OTHER,
'domainShop' => Domain::DOMAIN_ID_PC_SHOP,
'dateStart' => $filterDateStart,
'dateEnd' => $filterDateEnd,
];
// Results
$stockMovementItems = $entityManager->createQuery('SELECT smi
FROM AtechNetCoreBundle:StockMovementItem smi
JOIN smi.movement sm JOIN smi.product p
LEFT JOIN sm.merchant m
WHERE sm.type = :stockMovementTypeOut
AND sm.subType NOT IN ( :subTypeConfigChange, :subTypeOrderConfigChange, :subTypeToOtherStock, :subTypeReturnedToSupplier )
AND m.domain NOT IN ( :domainOther, :domainShop )
AND smi.product = :product
AND sm.created >= :dateStart
AND sm.created <= :dateEnd
AND smi.price > 0
ORDER BY p.code ASC')
->setParameters($filter)
->getResult();
// Chart Data
$data = [];
/**
* @var StockMovementItem $stockMovementItem
*/
foreach ($stockMovementItems as $stockMovementItem) {
// Stock Movement
$stockMovement = $stockMovementItem->getMovement();
// Customer
$customer = $stockMovement->getMerchant();
// Domain
$domain = $customer->getDomain();
// Date
$date = $stockMovement->getCreated();
// Date Key
$dateKey = (clone $date)->format('Ymd');
// Entry
if (!isset($data[$dateKey])) {
$data[$dateKey] = [
'date' => $date->format('Y-m-d'),
'domains' => [],
'market' => [
'retail' => 0,
'wholesale' => 0,
'marketplace' => 0,
],
];
}
if (!isset($data[$dateKey]['domains'][$domain->getId()])) {
$data[$dateKey]['domains'][$domain->getId()] = 0;
}
$data[$dateKey]['domains'][$domain->getId()] += $stockMovementItem->getQuantity();
if (in_array($domain->getId(), [Domain::DOMAIN_ID_RT_SK, Domain::DOMAIN_ID_RT_CZ, Domain::DOMAIN_ID_RT_HU, Domain::DOMAIN_ID_RT_EU])) {
$data[$dateKey]['market']['retail'] += $stockMovementItem->getQuantity();
}
if (in_array($domain->getId(), [Domain::DOMAIN_ID_WS_SK, Domain::DOMAIN_ID_WS_CZ, Domain::DOMAIN_ID_WS_HU, Domain::DOMAIN_ID_WS_EU])) {
$data[$dateKey]['market']['wholesale'] += $stockMovementItem->getQuantity();
}
if (in_array($domain->getId(), [Domain::DOMAIN_ID_B2C_EU])) {
$data[$dateKey]['market']['marketplace'] += $stockMovementItem->getQuantity();
}
}
// Sort
ksort($data);
// Chart Data
return $data;
}
/**
* @param Product $product
* @param string $filterDateStart
* @param string $filterDateEnd
* @param array $filterDomains
*
* @return array
*/
public function getChartPriceChangelog(Product $product, string $filterDateStart = '', string $filterDateEnd = '', array $filterDomains = []): array
{
// Entity Manager
$entityManager = $this->getEntityManager();
// Filter
$filter = [
'product' => $product->getId(),
'domains' => (
!empty($filterDomains) ? $filterDomains : [
Domain::DOMAIN_ID_RT_SK, Domain::DOMAIN_ID_WS_SK,
Domain::DOMAIN_ID_RT_HU, Domain::DOMAIN_ID_WS_HU,
Domain::DOMAIN_ID_RT_CZ, Domain::DOMAIN_ID_WS_CZ,
Domain::DOMAIN_ID_RT_EU, Domain::DOMAIN_ID_WS_EU,
]
),
'dateStart' => $filterDateStart,
'dateEnd' => $filterDateEnd,
];
// Results
$results = $entityManager->createQuery("SELECT ppl.created AS date, ppl.newPrice AS price, COALESCE(u.firstName, '') AS firstName, COALESCE(u.lastName, '') AS lastName, d.id AS domainId FROM AtechNetCoreBundle:ProductPriceLog ppl JOIN ppl.domain d LEFT JOIN ppl.createdBy u WHERE ppl.product = :product AND ppl.domain IN (:domains) AND ppl.created >= :dateStart AND ppl.created <= :dateEnd ORDER BY ppl.created ASC")
->setParameters($filter)
->getResult();
// Determine Date Interval
$minDate = $maxDate = [];
foreach ($results as $result) {
$dateTime = (clone $result['date'])->setTime(0, 0)->getTimestamp();
if (!isset($minDate[$result['domainId']])) {
$minDate[$result['domainId']] = $dateTime;
}
if ($minDate[$result['domainId']] > $dateTime) {
$minDate[$result['domainId']] = $dateTime;
}
if (!isset($maxDate[$result['domainId']])) {
$maxDate[$result['domainId']] = $dateTime;
}
if ($maxDate[$result['domainId']] < $dateTime) {
$maxDate[$result['domainId']] = $dateTime;
}
}
// Chart Data
$data = [];
foreach ($minDate as $domainId => $dateTime) {
// Domain Data
if (!isset($data[$domainId])) {
$data[$domainId] = [];
}
// Domain Min Date
$domainMinDate = $dateTime;
// Domain Max Date
$domainMaxDate = $maxDate[$domainId];
// Previous Data
$previousData = [];
// Review Dates
while ($domainMinDate <= $domainMaxDate) {
foreach ($results as $result) {
if ($result['domainId'] == $domainId) {
$originalValue = false;
$_dateTime = (clone $result['date'])->setTime(0, 0)->getTimestamp();
if ($_dateTime == $domainMinDate) {
$originalValue = true;
$_data = $previousData = [
'tooltipDate' => date('H:i M d, Y', (clone $result['date'])->getTimestamp()),
'createdBy' => trim($result['firstName'] . ' ' . $result['lastName']),
'price' => $result['price'],
];
$key = (clone $result['date'])->format('YmdHis');
$data[$domainId][$key] = array_merge($_data, [
'date' => date('Y-m-d', $domainMinDate),
'originalValue' => $originalValue,
]);
} else {
$_data = $previousData;
$key = date('YmdHis', $domainMinDate);
$data[$domainId][$key] = array_merge($_data, [
'date' => date('Y-m-d', $domainMinDate),
'currentDate' => date('M d, Y', $domainMinDate),
'originalValue' => $originalValue,
]);
}
}
}
$domainMinDate = strtotime('+1 day', $domainMinDate);
}
}
return $data;
}
/**
* @param Product $product
* @param string $filterDateStart
* @param string $filterDateEnd
* @param array $filterDomains
*
* @return array
*/
public function getChartSellingPrices(Product $product, string $filterDateStart = '', string $filterDateEnd = '', array $filterDomains = []): array
{
// Entity Manager
$entityManager = $this->getEntityManager();
// Filter
$filter = [
'product' => $product->getId(),
'domains' => (
!empty($filterDomains) ? $filterDomains : [
Domain::DOMAIN_ID_RT_SK, Domain::DOMAIN_ID_WS_SK,
Domain::DOMAIN_ID_RT_HU, Domain::DOMAIN_ID_WS_HU,
Domain::DOMAIN_ID_RT_CZ, Domain::DOMAIN_ID_WS_CZ,
Domain::DOMAIN_ID_RT_EU, Domain::DOMAIN_ID_WS_EU,
Domain::DOMAIN_ID_B2C_EU,
]
),
'status' => Order::STATUS_FINISHED,
'dateStart' => $filterDateStart,
'dateEnd' => $filterDateEnd,
];
// Results
$results = $entityManager->createQuery('SELECT oi.price, d.id AS domainId, o.created AS date FROM AtechNetCoreBundle:OrderItem oi JOIN oi.order o JOIN o.domain d WHERE oi.product = :product AND o.status = :status AND o.created >= :dateStart AND o.created <= :dateEnd AND o.domain IN (:domains) ORDER BY o.created ASC')
->setParameters($filter)
->getResult();
// Determine Date Interval
$minDate = $maxDate = [];
foreach ($results as $result) {
$dateTime = $result['date']->setTime(0, 0)->getTimestamp();
if (!isset($minDate[$result['domainId']])) {
$minDate[$result['domainId']] = $dateTime;
}
if ($minDate[$result['domainId']] > $dateTime) {
$minDate[$result['domainId']] = $dateTime;
}
if (!isset($maxDate[$result['domainId']])) {
$maxDate[$result['domainId']] = $dateTime;
}
if ($maxDate[$result['domainId']] < $dateTime) {
$maxDate[$result['domainId']] = $dateTime;
}
}
// Chart Data
$data = [];
foreach ($minDate as $domainId => $dateTime) {
// Domain Data
if (!isset($data[$domainId])) {
$data[$domainId] = [];
}
// Domain Min Date
$domainMinDate = $dateTime;
// Domain Max Date
$domainMaxDate = $maxDate[$domainId];
// Previous Price
$previousPrice = 0;
// Review Dates
while ($domainMinDate <= $domainMaxDate) {
foreach ($results as $result) {
if ($result['domainId'] == $domainId) {
$originalValue = false;
$dateTime = $result['date']->setTime(0, 0)->getTimestamp();
if ($dateTime == $domainMinDate) {
$originalValue = true;
$price = $previousPrice = $result['price'];
} else {
$price = $previousPrice;
}
$data[$domainId][] = [
'date' => date('Y-m-d', $domainMinDate),
'tooltipDate' => date('M d, Y', $domainMinDate),
'price' => $price,
'originalValue' => $originalValue,
];
}
}
$domainMinDate = strtotime('+1 day', $domainMinDate);
}
}
return $data;
}
/**
* @param Product $product
* @return array
*/
public function getChartPurchasePrices(Product $product): array
{
$results = $this->getEntityManager()->createQuery('SELECT smi.price, d.priceFactor, smi.quantity, sm.id AS stockMovementId, sm.quantity AS stockMovementQuantity, sm.created, sm.extraPrice, c.businessName
FROM AtechNetCoreBundle:Product p
LEFT JOIN p.stockMovementItems smi
LEFT JOIN smi.movement sm
LEFT JOIN sm.merchant c
LEFT JOIN c.domain d
WHERE smi.product = :product AND sm.subType = :movementSubTypeInBasic
ORDER BY sm.created ASC')
->setParameters(
[
'product' => $product,
'movementSubTypeInBasic' => StockMovement::SUB_TYPE_IN_SHIPMENT,
]
)
->getResult();
$data = [];
foreach ($results as $r) {
if ($r['priceFactor'] <= 0) {
$r['priceFactor'] = 1;
}
$pricePerItem = $r['price'] != 0 ? ($r['price'] / $r['priceFactor']) / $r['quantity'] : 0;
$extraPricePerItem = $r['extraPrice'] > 0 ? ($r['extraPrice'] / $r['priceFactor']) / $r['stockMovementQuantity'] : 0;
$purchasePricePerItem = number_format($pricePerItem + $extraPricePerItem, 2, '.', '');
$data[$r['stockMovementId']]['stockMovementId'] = $r['stockMovementId'];
$data[$r['stockMovementId']]['merchant'] = $r['businessName'];
$data[$r['stockMovementId']]['purchasePrice'] = $purchasePricePerItem;
$data[$r['stockMovementId']]['date'] = $r['created']->format('d.m.Y');
}
return $data;
}
/**
* @param Product $product
* @param bool $flush
* @return ProductModel|null
*/
public function handleProductModelCreation(Product $product, bool $flush = true): ?ProductModel
{
if (!in_array($product->getType()->getId(), ProductType::getTypesWithProductModels())) {
return null;
}
if ($product->isContainer() && !$this->containerContainsOnlyNotebookAndBattery($product)) {
return null;
}
$entityManager = $this->getEntityManager();
$productModelTitle = $this->getProductModelTitle($product, $this->getExcludableExpressionsForProductModelCreate());
$productModel = $entityManager->getRepository(ProductModel::class)->findOneBy([
'title' => $productModelTitle,
'isProductV2' => false,
]);
if ($productModel) {
$product->setProductModel($productModel);
} else {
$productModel = new ProductModel();
$productModel->setTitle($productModelTitle);
/** @var User $currentUser */
$currentUser = $this->getUser();
$productModel->setCreatedBy($currentUser);
$entityManager->persist($productModel);
if ($flush) {
$entityManager->flush();
}
$product->setProductModel($productModel);
}
if ($flush) {
$entityManager->flush();
}
return $productModel;
}
/**
* @param Product $product
* @param bool $onlyNew
*
* @return bool
*/
public function containerContainsOnlyNotebookAndBattery(Product $product, bool $onlyNew = false): bool
{
if ($product->getProductContainers()->count() != 2) {
return false;
}
$hasNotebookType = $hasBatteryType = false;
/** @var ProductContainer $containerProduct */
foreach ($product->getProductContainers() as $containerProduct) {
if (is_null($containerProduct->getChild())) {
continue;
}
if ($containerProduct->getChild()->getType()->getId() == ProductType::PRODUCT_TYPE_ID_NOTEBOOK) {
$hasNotebookType = true;
continue;
}
if ($containerProduct->getChild()->getType()->getId() == ProductType::PRODUCT_TYPE_ID_NOTEBOOK_BATTERY) {
if ($onlyNew) {
if ($containerProduct->getChild()->getQualityStatus() == Product::QualityStatus_New) {
$hasBatteryType = true;
}
} else {
$hasBatteryType = true;
}
}
}
return $hasNotebookType && $hasBatteryType;
}
/**
* @param Product $product
* @param array $textToExcludeFromName
* @return string
*/
public function getProductModelTitle(Product $product, array $textToExcludeFromName = []): string
{
$productModelTitle = $product->getName();
foreach ($textToExcludeFromName as $excludeableText) {
$productModelTitle = trim(str_replace($excludeableText, '', $productModelTitle));
$excludeableTextUcFirst = implode(' ', array_map('ucfirst', explode(' ', $excludeableText)));
$productModelTitle = trim(str_replace($excludeableTextUcFirst, '', $productModelTitle));
$productModelTitle = preg_replace('/\s+/', ' ', $productModelTitle);
$productModelTitle = str_replace(['á', 'é', 'í', 'ó', 'ő', 'ú', 'ű'], ['a', 'e', 'i', 'o', 'o', 'u', 'u'], $productModelTitle);
}
if ($product->getType()->getId() == ProductType::PRODUCT_TYPE_ID_NOTEBOOK) {
$processorFamily = '';
$processorGen = '';
$attributeOptionCpuNB = '';
foreach ($product->getSpecificationProductAttributeOptions() as $productAttributeOption) {
$option = $productAttributeOption->getOption();
if ($option->getAttribute()->getId() == Attribute::ATTRIBUTE_ID_CPU_NB) {
$processorFamily = $option->getParent()->getTitle();
$attributeOptionCpuNB = $option->getTitle();
}
if ($option->getAttribute()->getId() == Attribute::ATTRIBUTE_ID_CPU_GENERATION) {
$processorGen = $option->getTitle();
}
}
$productModelTitle = $productModelTitle . ($processorFamily != '' ? '-' . $processorFamily : '') . ($processorGen != '' ? '-' . str_replace('. Generation', '', $processorGen) : $this->getCPUGenfromAttributeOptionCPU($attributeOptionCpuNB));
}
if (in_array($product->getType()->getId(), [ProductType::PRODUCT_TYPE_ID_PC, ProductType::PRODUCT_TYPE_ID_ALL_IN_ONE])) {
$processorFamily = '';
$processorGen = '';
$attributeOptionCpuPC = '';
foreach ($product->getSpecificationProductAttributeOptions() as $productAttributeOption) {
$option = $productAttributeOption->getOption();
if ($option->getAttribute()->getId() == Attribute::ATTRIBUTE_ID_CPU_PC) {
$processorFamily = $option->getParent()->getTitle();
$attributeOptionCpuPC = $option->getTitle();
}
if ($option->getAttribute()->getId() == Attribute::ATTRIBUTE_ID_CPU_GENERATION) {
$processorGen = $option->getTitle();
}
}
$productModelTitle = $productModelTitle . ($processorFamily != '' ? '-' . $processorFamily : '') . ($processorGen != '' ? '-' . str_replace('. Generation', '', $processorGen) : $this->getCPUGenfromAttributeOptionCPU($attributeOptionCpuPC));
}
return $productModelTitle;
}
/**
* @param string $attributeOptionCpu
* @return string
*/
private function getCPUGenfromAttributeOptionCPU(string $attributeOptionCpu): string
{
if (!str_starts_with($attributeOptionCpu, 'i')) {
return '';
}
$cpuGenFirstNumber = substr($attributeOptionCpu, 3, 1);
$cpuGenNumber = $cpuGenFirstNumber == 1 ? substr($attributeOptionCpu, 3, 2) : substr($attributeOptionCpu, 3, 1);
return strlen($attributeOptionCpu) > 0 ? "-$cpuGenNumber" : '';
}
/**
* @return array
*/
public function getExcludableExpressionsForProductModelCreate(): array
{
$textToExcludeFromProductModelTitle = [];
$notebookSkinList = $this->getEntityManager()->getRepository(AttributeOption::class)->findBy([
'attribute' => Attribute::ATTRIBUTE_ID_NOTEBOOK_SKIN,
]);
/**
* @var AttributeOption $notebookSkin
*/
foreach ($notebookSkinList as $notebookSkin) {
$textToExcludeFromProductModelTitle[] = $notebookSkin->getTitle();
}
return array_merge([], $textToExcludeFromProductModelTitle, [
'Satin kirby pink',
'White',
'Metallic rose gold',
'Wave',
'Metalic Rosegold',
// '(Quality: Bazár)',
'Candy Red',
'Brushed Antracite',
'Gloss Pink',
'Gunmetal Gray',
'Blue',
'Matte Dark Gray',
'Matte',
'Matte Black',
'Red',
'Black',
'Ultramatte Burgundy',
'Gloss Burgundy',
'Gloss Amethyst',
'WHITE STARLIGHT',
'WAVE 3D',
'Metallic Rosengold',
'Lime metalic',
'Lime green',
'white starlight',
'Purple blue',
'Satin metal mint',
'starlight',
'Grey',
'BLUE',
'RED',
'Metal',
'Pink',
'Rugged',
'GOLD',
'Antracit',
'Silver',
'Space grey',
'Glossy',
'Brushed',
'Magenta',
'Satin Kirby pink',
'Satin Kirby pink',
'Burgundy',
'Light',
'Purple',
'Royal',
'JUNGLE',
'MATTE METAL',
'PLUM VIOLET',
'SATIN METAL MINT',
'Gloss',
'Kirby',
'Rush',
'Lime matte metalic',
'brown',
'Cement',
'chrome blue',
'matt lightning',
'Camouflage',
'Carbon Fiber',
'Carbon',
]);
}
/**
* @param string $listingTitle
* @return string|null
*/
public function setProductModelNameByExpressions(string $listingTitle): ?string
{
$listingTitle = strtolower($listingTitle);
$expresionsToCheck = [
't460' => 'ThinkPad T460-i5-6',
'5490' => 'Latitude 5490-i5-8',
'830 g5' => 'EliteBook 830 G5-i5-8',
'840 g6' => 'EliteBook 840 G6-i5-8',
'x390' => 'ThinkPad x390-i5-8',
'840 g5' => 'EliteBook 840 G5-i5-8',
'7490' => 'Latitude 7490-i5-8',
'5470' => 'Latitude E5470-i5-6',
// 'l560' => 'i5-8',
'5480' => 'Latitude 5480-i5-6',
't460s' => 'ThinkPad T460s-i5-6',
'7450' => 'Latitude E7450-i5-5',
'850 g5' => 'EliteBook 850 G5-i5-8',
'840 g2' => 'EliteBook 840 G2-i5-5',
't440' => 'ThinkPad T440-i5-4',
'850 g6' => 'EliteBook 850 G6-i5-8',
'x360 1030 g2' => 'EliteBook x360 1030 G2-i5-7',
'820 g1' => 'EliteBook 820 G1-i5-4',
'T495s' => 'ThinkPad T495s-AMD Ryzen',
'T495' => 'ThinkPad T495-AMD Ryzen',
];
foreach ($expresionsToCheck as $expression => $productModelTitle) {
if (str_contains($listingTitle, $expression)) {
return $productModelTitle;
}
}
$expresionsToCheck = [
't490s;i7' => 'ThinkPad T490s-i7-8',
't490s;i5' => 'ThinkPad T490s-i5-8',
't480;i7' => 'ThinkPad T480-i7-8',
't480;i5' => 'ThinkPad T480-i5-8',
'yoga 370;i5' => 'ThinkPad Yoga 370-i5-7',
'yoga 370;i7' => 'ThinkPad Yoga 370-i7-7',
't480s;i5' => 'ThinkPad T480s-i5-8',
't480s;i7' => 'ThinkPad T480s-i7-8',
't490;i7' => 'ThinkPad T490-i7-8',
't490;i5' => 'ThinkPad T490-i5-8',
'7470;i5' => 'Latitude E7470-i5-6',
'7470;i7' => 'Latitude E7470-i7-6',
't470;i5' => 'ThinkPad T470-i5-7',
't470;i7' => 'ThinkPad T470-i7-7',
't470s;i5' => 'ThinkPad T470s-i5-7',
't470s;i7' => 'ThinkPad T470s-i7-7',
'840 g3;i3' => 'EliteBook 840 G3-i3-6',
'840 g3;i5' => 'EliteBook 840 G3-i5-6',
'840 g3;i7' => 'EliteBook 840 G3-i7-6',
'840 g1;i5' => 'EliteBook 840 G1-i5-4',
'l460;pentium' => 'ThinkPad L460-Celeron',
'850 g3;i5' => 'EliteBook 850 G3-i5-6',
'850 g3;i7' => 'EliteBook 850 G3-i7-6',
'latitude 5510;i5' => 'Latitude 5510-i5-10',
'820 g3;i3' => 'EliteBook 820 G3-i3-6',
'820 g3;i5' => 'EliteBook 820 G3-i5-6',
'820 g3;i7' => 'EliteBook 820 G3-i7-6',
'l450;i3' => 'ThinkPad L450-i3-5',
'l450;i5' => 'ThinkPad L450-i5-5',
't560;i5' => 'ThinkPad T560-i5-6',
't560;i7' => 'ThinkPad T560-i7-6',
't580;i5' => 'ThinkPad T580-i5-8',
't580;i7' => 'ThinkPad T580-i7-8',
'p50s;i7' => 'ThinkPad P50s-i7-6',
'p50;i7' => 'ThinkPad P50-i7-6',
't570;i5' => 'ThinkPad T570-i5-7',
't570;i7' => 'ThinkPad T570-i7-7',
'thinkpad x1 yoga g2;i7-7' => 'ThinkPad X1 Yoga Gen 2-i7-7',
'thinkpad x1 yoga g2;i5-7' => 'ThinkPad X1 Yoga Gen 2-i5-7',
'thinkpad x1 yoga g2;i5-8' => "ThinkPad X1 Yoga Gen 2-i5-8",
'optiplex 7020 sff;i5-4' => 'OptiPlex 7020 SFF-i5-4',
'optiplex 3010 sff;i5-3' => 'OptiPlex 3010 SFF-i5-3',
'elitedesk 800 g2 sff;i7-6' => 'EliteDesk 800 G2 SFF-i7-6',
'elitedesk 800 g2 dm;i5-6' => 'EliteDesk 800 G2 DM-i5-6',
'esprimo q920;i5-4' => 'Esprimo Q920 USFF-i5-4',
'elitedesk 800 g1 usdt;i5-4' => 'EliteDesk 800 G1 USDT-i5-4',
'elitedesk 800 g1 usdt;i3-4' => 'EliteDesk 800 G1 USDT-i3-4',
'elitedesk 800 g1 usdt;i7-4' => 'EliteDesk 800 G1 USDT-i7-4',
];
foreach ($expresionsToCheck as $expressions => $productModelTitle) {
$expressionPairToCheck = explode(';', $expressions);
if (str_contains($listingTitle, $expressionPairToCheck[0]) && str_contains($listingTitle, $expressionPairToCheck[1])) {
return $productModelTitle;
}
}
return null;
}
/**
* @param string $listingTitle
* @return string
*/
public function getModelNameFromListingTitle(string $listingTitle): string
{
$listingTitle = substr(strstr($listingTitle, ' '), 1);
$cpuGen = '';
if (str_contains($listingTitle, '| i')) {
$cpuData = explode(' | ', $listingTitle)[1];
$cpuGen = strlen($cpuData) == 8 ? substr_replace($cpuGen, '', 0, 4) : substr_replace($cpuGen, '', 0, 5);
$cpuGen = "-$cpuGen";
}
$searchedCharacterIndex = strpos($listingTitle, ' |');
$clearTitle = substr_replace($listingTitle, '', $searchedCharacterIndex, strlen($listingTitle) - $searchedCharacterIndex);
$searchedInchIndex = strpos($listingTitle, '-inch');
if ($searchedInchIndex) {
$clearTitle = substr_replace($listingTitle, '', $searchedInchIndex, strlen($listingTitle) - $searchedInchIndex);
}
$searchedTumIndex = strpos($listingTitle, '-tum');
if ($searchedTumIndex) {
$clearTitle = substr_replace($listingTitle, '', $searchedTumIndex, strlen($listingTitle) - $searchedTumIndex);
}
return str_replace(['Latitude 14 5495', 'Business PC'], ['Latitude 5495', ''], $clearTitle) . $cpuGen;
}
/**
* @param int $marginLevel
* @return void
* @throws Exception
*/
public function updateMarginLevelForAllProducts(int $marginLevel): void
{
if ($marginLevel < Product::MARGIN_LEVEL_NO || $marginLevel > Product::MARGIN_LEVEL_E) {
throw new InvalidParameterException('Invalid marginLevel');
}
$entityManager = $this->getEntityManager();
$connection = $entityManager->getConnection();
$sql = 'UPDATE products SET margin_level = ' . $marginLevel;
$connection->executeQuery($sql);
}
/**
* @param array<string> $csvFileContent returned by file()
*/
public function updateMarginLevelFromCSV(array $csvFileContent): Generator
{
$csv = array_map('str_getcsv', $csvFileContent);
array_walk($csv, function (&$a) use ($csv) {
$a = array_combine($csv[0], $a);
});
array_shift($csv);
$marginLevels = array_flip(Product::getMarginLevelChoices());
foreach ($csv as $line) {
$product = $this->findOneByCode($line['CODE']);
if ($product && isset($marginLevels[$line['MARGIN_LEVEL']])) {
yield $product->getCode() => $line['MARGIN_LEVEL'];
$product->setMarginLevel($marginLevels[$line['MARGIN_LEVEL']]);
}
}
}
/**
* @param Basket|null $basket
* @return bool
*/
public function isBasketIncludesOnlyGiftCart(?Basket $basket): bool
{
if (is_null($basket)) {
return false;
}
foreach ($basket->getBasketItems() as $bi) {
if (!in_array($bi->getProduct()->getId(), Product::giftCartIds)) {
return false;
}
}
return true;
}
/**
* @param int $attributeId
* @param array $attributeOptionIdList
* @param array $productIdList
* @return float|int|mixed|string
*/
public function getAttributeOptionList(int $attributeId, array $attributeOptionIdList = [], array $productIdList = [])
{
return $this->getOptionListByQuery($attributeId, $attributeOptionIdList, $productIdList);
}
/**
* @param int $attributeId
* @return string
*/
public function getAttributeOptionElementList(int $attributeId): string
{
$optionList = $this->getOptionListByQuery($attributeId);
return $this->createHtmlElementsByOptionList($optionList);
}
/**
* @param array $optionList
* @return string
*/
public function getAttributeOptionElementListByOptionList(array $optionList): string
{
return $this->createHtmlElementsByOptionList($optionList);
}
/**
* @param $attributeId
* @param array $attributeOptionIdList
* @param array $productIdList
* @return float|int|mixed|string
*/
private function getOptionListByQuery($attributeId, array $attributeOptionIdList = [], array $productIdList = [])
{
if (count($attributeOptionIdList) > 0) {
$attributeOptionIdListTextFormat = implode(',', $attributeOptionIdList);
$productIdListTextFormat = implode(',', $productIdList);
return $this->getEntityManager()->createQuery("SELECT ao.id AS optionId, ao.title AS optionTitle, a.id AS attributeId, a.title AS attributeTitle
FROM AtechNetCoreBundle:AttributeOption ao
INNER JOIN ao.attribute a
LEFT JOIN AtechNetCoreBundle:ProductAttribute pa WITH pa.option = ao.id
WHERE a.id = :attributeId AND ao.archived = 0 AND (ao.id IN ($attributeOptionIdListTextFormat) OR IDENTITY(pa.product) IN ($productIdListTextFormat))
GROUP BY ao.id
ORDER BY ao.parent ASC, ao.sortIndex ASC")
->setParameters([
'attributeId' => $attributeId,
])
->getResult();
}
return $this->getEntityManager()->createQuery('SELECT ao
FROM AtechNetCoreBundle:AttributeOption ao
LEFT JOIN ao.attribute a
WHERE a.id = :attributeId AND ao.archived = 0
ORDER BY ao.parent ASC, ao.sortIndex ASC')
->setParameters([
'attributeId' => $attributeId,
])
->getResult();
}
/**
* @param $optionList
* @return string
*/
private function createHtmlElementsByOptionList($optionList): string
{
$returnOptions = '';
$parent = 0;
$removeOptionsWithParents = false;
foreach ($optionList as $option) {
if ($option->getParent() != null) {
$removeOptionsWithParents = true;
}
}
foreach ($optionList as $option) {
if ($removeOptionsWithParents && $option->getParent() == null && $option->getId() != 1132 && substr($option->getTitle(), 0, 3) !== 'NO ') {
continue;
}
$separator = false;
if ($option->getParent() != null) {
if ($parent == 0) {
$parent = $option->getParent()->getId();
$separator = true;
} else {
if ($parent != $option->getParent()->getId()) {
$parent = $option->getParent()->getId();
$separator = true;
}
}
}
if ($separator) {
$returnOptions .= '<option disabled>------------- ' . $option->getParent()->getTitle() . ' -------------</option>';
}
$returnOptions .= '<option value="' . $option->getId() . '" data-attribute-option-id="' . $option->getId() . '">' . $option->getTitle() . '</option>';
}
return $returnOptions;
}
/**
* @param Request $request
* @return float|int|mixed|string
*/
public function searchProductsByRequest(Request $request): mixed
{
$term = $request->query->get('term');
$domainId = $request->query->get('domainId');
$onlyFlashSales = $request->query->get('onlyFlashSales');
$maxResults = $request->query->get('maxResults') ? $request->query->get('maxResults') : 1;
if ($maxResults == 1) {
$select = ' p.code = ' . $term;
} else {
$select = " CONCAT(p.code, ' ', pt.title, ' ', v.name, ' ', p.name, ' ', p.cacheDescription) LIKE '%" . $term . "%'";
}
$flashSaleWhere = '';
if ($onlyFlashSales == '1') {
$flashSaleWhere .= ' AND (pm.saleStart IS NOT NULL OR pm.saleEnd IS NOT NULL)';
}
return $this->getEntityManager()->createQuery("SELECT p.id, p.code, CONCAT(pt.title, ' ', v.name,' ', p.name) AS title, p.cacheDescription, p.available, p.inStock
FROM AtechNetCoreBundle:Product p
LEFT JOIN p.vendor v
LEFT JOIN p.type pt
LEFT JOIN AtechNetCoreBundle:ProductMeta pm WITH pm.product = p AND pm.domain = $domainId
WHERE pm.active = 1 AND pm.orderable = 1 AND pm.listed = 1 AND p.available > 0 AND" . $select . $flashSaleWhere)
->setMaxResults($maxResults)
->getResult();
}
/**
* @param ProductModel $productModel
* @return QueryBuilder
*/
public function getProductModels(ProductModel $productModel): QueryBuilder
{
$entityManager = $this->getEntityManager();
return $entityManager->getRepository(Product::class)
->createQueryBuilder('p')
->where('p.productModel = :productModel')
->setParameter(
'productModel',
$productModel->getId()
);
}
/**
* @param string $term
* @return float|int|mixed|string
*/
public function searchProduct(string $term): mixed
{
return $this->getEntityManager()
->createQuery('SELECT p.id, p.name
FROM AtechNetCoreBundle:Product p
LEFT JOIN p.productModel pm
WHERE p.name LIKE :term AND pm.id IS NULL
GROUP BY p.name
ORDER BY p.name ASC')
->setParameters([
'term' => '%' . $term . '%',
])
->getResult();
}
/**
* @param array $modelIds
* @param bool $onlyIds
* @param array $oldProductAttributeOptionFilters
* @return array
*/
public function findProductsByModelIds(array $modelIds, bool $onlyIds = false, array $oldProductAttributeOptionFilters = []): array
{
$result = $this->createQueryBuilder('p')
->innerJoin('p.productModel', 'pm')
->where('pm.id IN (:ids)')
->setParameter('ids', $modelIds);
if (!empty($oldProductAttributeOptionFilters)) {
$result->innerJoin('p.productAttributes', 'pa')
->innerJoin('pa.option', 'ao')
->andWhere('ao.id IN (:filterIds)')
->setParameter('filterIds', $oldProductAttributeOptionFilters)
->groupBy('p.id')
->having('COUNT(DISTINCT ao.id) = :filterCount')
->setParameter('filterCount', count($oldProductAttributeOptionFilters));
}
if ($onlyIds) {
$result = $result->select('p.id');
return $result->getQuery()->getSingleColumnResult();
}
return $result->getQuery()->getResult();
}
/**
* @param array $productIds
* @param bool $onlyIds
* @param array $oldProductAttributeOptionFilters
* @return array
*/
public function findProductsByIds(array $productIds, bool $onlyIds = false, array $oldProductAttributeOptionFilters = []): array
{
$result = $this->createQueryBuilder('p')
->where('p.id IN (:ids)')
->setParameter('ids', $productIds);
if (!empty($oldProductAttributeOptionFilters)) {
$result->innerJoin('p.productAttributes', 'pa')
->innerJoin('pa.option', 'ao')
->andWhere('ao.id IN (:filterIds)')
->setParameter('filterIds', $oldProductAttributeOptionFilters)
->groupBy('p.id')
->having('COUNT(DISTINCT ao.id) = :filterCount')
->setParameter('filterCount', count($oldProductAttributeOptionFilters));
}
if ($onlyIds) {
$result = $result->select('p.id');
return $result->getQuery()->getSingleColumnResult();
}
return $result->getQuery()->getResult();
}
/**
* @param array $productIds
* @param bool $onlyIds
* @param array $oldProductAttributeOptionFilters
* @return array
*/
public function findAllProductsByProductIds(array $productIds, bool $onlyIds = false, array $oldProductAttributeOptionFilters = []): array
{
$modelIds = $this->getEntityManager()->getRepository(ProductModel::class)->createQueryBuilder('pm')
->select('pm.id')
->innerJoin('pm.products', 'p')
->where('p.id IN (:ids)')
->setParameter('ids', $productIds)
->getQuery()->getSingleColumnResult();
$result = $this->createQueryBuilder('p')
->innerJoin('p.productModel', 'pm')
->where('pm.id IN (:ids)')
->setParameter('ids', $modelIds);
if (!empty($oldProductAttributeOptionFilters)) {
$result->innerJoin('p.productAttributes', 'pa')
->innerJoin('pa.option', 'ao')
->andWhere('ao.id IN (:filterIds)')
->setParameter('filterIds', $oldProductAttributeOptionFilters)
->groupBy('p.id')
->having('COUNT(DISTINCT ao.id) = :filterCount')
->setParameter('filterCount', count($oldProductAttributeOptionFilters));
}
if ($onlyIds) {
$result = $result->select('p.id');
return $result->getQuery()->getSingleColumnResult();
}
return $result->getQuery()->getResult();
}
/**
* @param int $modelId
* @param array $attributeOptionIds
* @return array
*/
public function findProductsByModelIdsAndAttributeOptionIds(int $modelId, array $attributeOptionIds): array
{
return $this->createQueryBuilder('p')
->innerJoin('p.fixedAttributeOptionCombination', 'fac')
->innerJoin('fac.attributeOptions', 'ao')
->where('ao.id IN (:attributeOptionIds)')
->andWhere('p.productModel = :productModelId')
->groupBy('p.id')
->having('COUNT(DISTINCT ao.id) = :count')
->setParameter('attributeOptionIds', $attributeOptionIds)
->setParameter('productModelId', $modelId)
->setParameter('count', count($attributeOptionIds))
->getQuery()->getResult();
}
/**
* @param int $type
* @return iterable
*/
public function getProductsFroUnasExport(int $type): iterable
{
$qb = $this->createQueryBuilder('p');
$sub = $this->getEntityManager()->createQueryBuilder()
->select('1')
->from(ProductMeta::class, 'pm2')
->where('pm2.product = p')
->andWhere('pm2.domain = :domain')
->andWhere('pm2.active = 1')
->andWhere('pm2.orderable = 1')
->andWhere('pm2.price > 0');
$qb->where('p.type = :type')
->andWhere('p.inStock > 0 AND p.available > 0')
->andWhere('p.archived = 0')
->andWhere('p.qualityStatus != :bazaarQualityStatus')
->andWhere($qb->expr()->exists($sub->getDQL()))
->orderBy('p.id', 'ASC')
->setParameters([
'bazaarQualityStatus' => Product::QualityStatus_Bazaar,
'domain' => Domain::DOMAIN_ID_RT_EU,
'type' => $type,
]);
return $qb->getQuery()->toIterable([], Query::HYDRATE_OBJECT);
}
/**
* @param ProductType|string|array $productType
*
* @return array
*/
public function findByProductType(ProductType|string|array $productType): array
{
if ($productType === 'cable') {
$queryBuilder = $this->createQueryBuilder('p')
->innerJoin('p.type', 'pt');
$queryBuilder->where(
$queryBuilder->expr()->like(
'pt.title',
$queryBuilder->expr()->literal('Cable%')
)
);
$queryBuilder->andWhere('p.available > 0');
$queryBuilder->andWhere('p.scannable = 0');
$query = $queryBuilder->setFirstResult(0)->getQuery();
return $query->getResult();
} elseif ($productType === 'packing-material') {
$queryBuilder = $this->createQueryBuilder('p');
$queryBuilder->andWhere('p.type = 211');
$queryBuilder->andWhere('p.available > 0');
$query = $queryBuilder->setFirstResult(0)->getQuery();
return $query->getResult();
} elseif ($productType === 'cable-material') {
$queryBuilder = $this->createQueryBuilder('p');
$queryBuilder->andWhere('p.id IN ( 68732, 2285, 2485, 12637, 12638, 26954, 64928, 17834, 2486, 2488, 2697 )');
$queryBuilder->andWhere('p.available > 0');
$query = $queryBuilder->setFirstResult(0)->getQuery();
return $query->getResult();
} elseif ($productType === 'other-material') {
$queryBuilder = $this->createQueryBuilder('p');
$queryBuilder->andWhere('p.id IN ( 191, 2656 )');
$queryBuilder->andWhere('p.available > 0');
$query = $queryBuilder->setFirstResult(0)->getQuery();
return $query->getResult();
}
return $this->findBy([
'type' => $productType
]);
}
public function getLastInMovementShipmentForStockItems(array $stockItems): array
{
if (empty($stockItems)) {
return [];
}
$ids = array_map(fn($si) => $si->getId(), $stockItems);
$sql = '
SELECT ranked.si_id, ranked.shipment_code, ranked.s_shipment_id
FROM (
SELECT
smisi.stockitem_id AS si_id,
sm.shipment_code AS shipment_code,
s.shipment_id AS s_shipment_id,
ROW_NUMBER() OVER (PARTITION BY smisi.stockitem_id ORDER BY sm.created DESC) AS rn
FROM stock_movement_items_stock_items smisi
INNER JOIN stock_movement_item smi ON smi.id = smisi.stockmovementitem_id
INNER JOIN stock_movements sm ON sm.id = smi.stock_movement_id
LEFT JOIN shipments s ON s.id = sm.shipment_id
WHERE smisi.stockitem_id IN (' . implode(',', array_fill(0, count($ids), '?')) . ')
AND sm.type = 1
AND (sm.shipment_id IS NOT NULL OR sm.shipment_code IS NOT NULL)
) ranked
WHERE ranked.rn = 1
';
$rows = $this->getEntityManager()
->getConnection()
->executeQuery($sql, $ids)
->fetchAllAssociative();
$result = [];
foreach ($rows as $row) {
$result[(int)$row['si_id']] = [
'shipmentId' => $row['s_shipment_id'],
'shipmentCode' => $row['shipment_code'],
];
}
return $result;
}
}

src/AtechNet/CoreBundle/Repository/ProductRepository.php line 83