ImportService::activateArticles() - Code Metrics - Inspection of "CON-4832: unassign articles from local categories..." - shopware/SwagConnect - Measure and Improve Code Quality continuously with Scrutinizer

Completed
Pull Request — master (#394)

by Jonas
created 2017-09-28 12:07 UTC
ImportService::activateArticles() A

↳ Parent: ImportService
Complexity

Conditions	1
Paths	1
Size

Total Lines	18
Code Lines	13
Duplication

Lines	0
Ratio	0 %
Importance

Changes
Metric	Value
dl	0
loc	18
rs	9.4285
c	0
b	0
f	0
cc	1
eloc	13
nc	1
nop	1
<?php
/**
 * (c) shopware AG <[email protected]>
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace ShopwarePlugins\Connect\Components;

use Doctrine\DBAL\Connection;
use Shopware\Bundle\AttributeBundle\Service\DataPersister;
use Shopware\Components\Model\CategoryDenormalization;
use ShopwarePlugins\Connect\Components\CategoryResolver\AutoCategoryResolver;
use Shopware\Components\Model\ModelManager;
use Shopware\Components\MultiEdit\Resource\ResourceInterface;
use Shopware\CustomModels\Connect\ProductToRemoteCategoryRepository;
use Shopware\CustomModels\Connect\RemoteCategoryRepository;
use Shopware\Models\Article\Repository as ArticleRepository;
use Shopware\Models\Category\Repository as CategoryRepository;

class ImportService
{
    /**
     * @var ModelManager
     */
    private $manager;

    /**
     * @var \Shopware\Components\MultiEdit\Resource\ResourceInterface
     */
    private $productResource;

    /**
     * @var \Shopware\Models\Category\Repository
     */
    private $categoryRepository;

    /**
     * @var ArticleRepository
     */
    private $articleRepository;

    /**
     * @var RemoteCategoryRepository
     */
    private $remoteCategoryRepository;

    /**
     * @var ProductToRemoteCategoryRepository
     */
    private $productToRemoteCategoryRepository;

    /**
     * @var CategoryResolver
     */
    private $autoCategoryResolver;

    /**
     * @var CategoryExtractor
     */
    private $categoryExtractor;

    /**
     * @var CategoryDenormalization
     */
    private $categoryDenormalization;

    /**
     * @var DataPersister
     */
    private $dataPersister;

    public function __construct(
        ModelManager $manager,
        ResourceInterface $productResource,
        CategoryRepository $categoryRepository,
        ArticleRepository$articleRepository,
        RemoteCategoryRepository $remoteCategoryRepository,
        ProductToRemoteCategoryRepository $productToRemoteCategoryRepository,
        AutoCategoryResolver $categoryResolver,
        CategoryExtractor $categoryExtractor,
        CategoryDenormalization $categoryDenormalization,
        DataPersister $dataPersister
    ) {
        $this->manager = $manager;
        $this->productResource = $productResource;
        $this->categoryRepository = $categoryRepository;
        $this->articleRepository = $articleRepository;
        $this->remoteCategoryRepository = $remoteCategoryRepository;
        $this->productToRemoteCategoryRepository = $productToRemoteCategoryRepository;
        $this->autoCategoryResolver = $categoryResolver;
        $this->categoryExtractor = $categoryExtractor;
        $this->categoryDenormalization = $categoryDenormalization;
        $this->dataPersister = $dataPersister;
    }

    public function findBothArticlesType($categoryId, $query = '', $showOnlyConnectArticles = true, $limit = 10, $offset = 0)
    {
        if ($categoryId == 0) {
            return [];
        }

        return $this->productResource->filter($this->getAst($categoryId, $query, $showOnlyConnectArticles), $offset, $limit);
    }

    /**
     * @param $categoryId
     * @return bool
     */
    public function hasCategoryChildren($categoryId)
    {
        return (bool) $this->categoryRepository->getChildrenCountList($categoryId);
    }

    public function assignCategoryToArticles($categoryId, array $articleIds)
    {
        $articles = $this->articleRepository->findBy(['id' => $articleIds]);

        if (empty($articles)) {
            throw new \RuntimeException('Invalid article ids');
        }

        /** @var \Shopware\Models\Category\Category $category */
        $category = $this->categoryRepository->find($categoryId);
        if (!$category) {
            throw new \RuntimeException('Invalid category id');
        }

        /** @var \Shopware\Models\Article\Article $article */
        foreach ($articles as $article) {
            $article->addCategory($category);
            $this->manager->persist($article);
            /** @var \Shopware\Models\Article\Detail $detail */
            foreach ($article->getDetails() as $detail) {
                $attribute = $detail->getAttribute();
                $attribute->setConnectMappedCategory(true);
                $this->manager->persist($attribute);
            }
        }

        $this->manager->flush();
    }

    /**
     * Unassign categories from given article ids
     * for the given categoryId and all childcategories
     * or for all categories if $categoryId is null
     * Set connect_mapped_category flag in article
     * attributes to NULL
     *
     * @param array $articleIds
     * @param int|null $categoryId
     * @throws \Doctrine\DBAL\ConnectionException
     * @throws \Exception
     */
    public function unAssignArticleCategories(array $articleIds, $categoryId = null)
    {
        if (!empty($articleIds)) {
            // cast all items in $articleIds to int
            // before use them in WHERE IN clause
            foreach ($articleIds as $key => $articleId) {
                $articleIds[$key] = (int) $articleId;
            }

            $connection = $this->manager->getConnection();
            $connection->beginTransaction();

            try {
                if ($categoryId != null) {

                    $this->unAssignArticlesFromCategory($articleIds, $categoryId);
                } else {
                    $this->unAssignArticlesFromAllCategories($articleIds);
                }

                $connection->commit();
            } catch (\Exception $e) {
                $connection->rollBack();
                throw new \Exception($e->getMessage());
            }
        }
    }

    /**
     * Collect remote article ids by given category id
     *
     * @param int $localCategoryId
     * @return array
     */
    public function findRemoteArticleIdsByCategoryId($localCategoryId)
    {
        $connection = $this->manager->getConnection();
        $sql = 'SELECT sac.articleID
            FROM s_articles_categories sac
            LEFT JOIN s_articles_attributes saa ON sac.articleID = saa.articleID
            WHERE sac.categoryID = :categoryId AND saa.connect_mapped_category = 1';
        $rows = $connection->fetchAll($sql, [':categoryId' => $localCategoryId]);

        return array_map(function ($row) {
            return $row['articleID'];
        }, $rows);
    }

    /**
     * @param array $categoryIds
     * @return int
     */
    public function deactivateLocalCategoriesByIds(array $categoryIds)
    {
        $builder = $this->manager->getConnection()->createQueryBuilder();
        $rowCount = $builder->update('s_categories', 'c')
            ->set('c.active', 0)
            ->where('c.id IN (:categoryIds)')
            ->setParameter('categoryIds', $categoryIds, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY)
            ->execute();

        return $rowCount;
    }

    /**
     * Collect all child categories by given
     * remote category key and create same
     * categories structure as Shopware Connect structure.
     * Find all remote products which belong to these categories
     * and assign them.
     *
     * @param int $localCategoryId
     * @param string $remoteCategoryKey
     * @param string $remoteCategoryLabel
     * @return void
     */
    public function importRemoteCategory($localCategoryId, $remoteCategoryKey, $remoteCategoryLabel)
    {
        /** @var \Shopware\Models\Category\Category $localCategory */
        $localCategory = $this->categoryRepository->find((int) $localCategoryId);
        if (!$localCategory) {
            throw new \RuntimeException('Local category not found!');
        }

        /** @var \Shopware\CustomModels\Connect\RemoteCategory $remoteCategory */
        $remoteCategory = $this->remoteCategoryRepository->findOneBy(['categoryKey' => $remoteCategoryKey]);
        if (!$remoteCategory) {
            throw new \RuntimeException('Remote category not found!');
        }

        // collect his child categories and
        // generate remote category tree by given remote category
        $remoteCategoryChildren = $this->categoryExtractor->getRemoteCategoriesTree($remoteCategoryKey, true);
        $remoteCategoryNodes = [
            [
                'name' => $remoteCategoryLabel,
                'categoryId' => $remoteCategoryKey,
                'leaf' => empty($remoteCategoryChildren) ? true : false,
                'children' => $remoteCategoryChildren,
            ]
        ];

        // create same category structure as Shopware Connect structure
        $categories = $this->autoCategoryResolver->convertTreeToKeys($remoteCategoryNodes, $localCategory->getId(), false);

        foreach ($categories as $category) {
            $articleIds = $this->productToRemoteCategoryRepository->findArticleIdsByRemoteCategory($category['remoteCategory']);
            foreach ($articleIds as $articleId) {
                $this->categoryDenormalization->addAssignment($articleId, $category['categoryKey']);
                $this->categoryDenormalization->removeAssignment($articleId, $category['parentId']);
                $this->manager->getConnection()->executeQuery(
                    'INSERT IGNORE INTO `s_articles_categories` (`articleID`, `categoryID`) VALUES (?, ?)',
                    [$articleId,  $category['categoryKey']]
                );
                $this->manager->getConnection()->executeQuery(
                    'DELETE FROM `s_articles_categories` WHERE `articleID` = :articleID AND `categoryID` = :categoryID',
                    [
                        ':articleID' => $articleId,
                        ':categoryID' => $category['parentId']
                    ]
                );
                $detailId = $this->manager->getConnection()->fetchColumn(
                    'SELECT id FROM `s_articles_details` WHERE `articleID` = :articleID',
                    ['articleID' => $articleId]
                );
                $this->manager->getConnection()->executeQuery(
                    'INSERT  INTO `s_articles_attributes` (`articleID`, `articledetailsID`, `connect_mapped_category`) 
                        VALUES (?, ?, 1)
                        ON DUPLICATE KEY UPDATE `connect_mapped_category` = 1
                    ',
                    [$articleId,  $detailId]
                );
            }
        }
    }

    /**
     * @param array $articleIds
     */
    public function activateArticles(array $articleIds)
    {
        $articleBuilder = $this->manager->createQueryBuilder();
        $articleBuilder->update('\Shopware\Models\Article\Article', 'a')
            ->set('a.active', 1)
            ->where('a.id IN (:articleIds)')
            ->setParameter(':articleIds', $articleIds, Connection::PARAM_STR_ARRAY);

        $articleBuilder->getQuery()->execute();

        $detailBuilder = $this->manager->createQueryBuilder();
        $detailBuilder->update('\Shopware\Models\Article\Detail', 'd')
            ->set('d.active', 1)
            ->where('d.articleId IN (:articleIds)')
            ->setParameter(':articleIds', $articleIds, Connection::PARAM_STR_ARRAY);

        $detailBuilder->getQuery()->execute();
    }

    /**
     * Store remote categories in Connect tables
     * and add relations between categories and products.
     *
     * @param array $remoteItems
     *
     * @throws \Exception
     */
    public function storeRemoteCategories(array $remoteItems)
    {
        $connection = $this->manager->getConnection();

        $connection->beginTransaction();
        try {
            foreach ($remoteItems as $articleId => $categories) {
                foreach ($categories as $categoryKey => $category) {
                    $connection->executeQuery(
                        'INSERT IGNORE INTO `s_plugin_connect_categories` (`category_key`, `label`) VALUES (?, ?)',
                        [$categoryKey, $category]
                    );

                    $connection->executeQuery(
                        'INSERT IGNORE INTO `s_plugin_connect_product_to_categories` (`connect_category_id`, `articleID`) VALUES ((SELECT c.id FROM s_plugin_connect_categories c WHERE c.category_key = ?), ?)',
                        [$categoryKey, $articleId]
                    );
                }
            }
            $connection->commit();
        } catch (\Exception $e) {
            $connection->rollBack();

            throw $e;
        }
    }

    /**
     * Fetch remote (Connect) categories by given article ids
     * @param array $articleIds
     * @return array
     */
    public function fetchRemoteCategoriesByArticleIds(array $articleIds)
    {
        $remoteCategoryIds = [];
        while ($currentIdBatch = array_splice($articleIds, 0, 500)) {
            $sql = 'SELECT sac.categoryID
            FROM s_articles_categories sac
            LEFT JOIN s_categories_attributes attr ON sac.categoryID = attr.categoryID
            WHERE attr.connect_imported_category = 1 AND sac.articleID IN (' . implode(', ', $currentIdBatch) . ') GROUP BY sac.categoryID';
            $rows = $this->manager->getConnection()->fetchAll($sql);

            $remoteCategoryIds = array_merge($remoteCategoryIds, array_map(function ($row) {
                return $row['categoryID'];
            }, $rows));
        }

        return array_unique($remoteCategoryIds);
    }

    /**
     * Fetch all articles where categories are auto imported
     * and there isn't record in s_plugin_connect_product_to_categories for them.
     * Returned array contains key = articleId and value = array of categories
     *
     * @return array
     */
    public function getArticlesWithAutoImportedCategories()
    {
        $statement = $this->manager->getConnection()->prepare(
            'SELECT b.article_id, b.category
            FROM s_plugin_connect_items b
            LEFT JOIN s_plugin_connect_product_to_categories a ON b.article_id = a.articleID
            WHERE b.shop_id > 0 AND a.connect_category_id IS NULL GROUP BY b.article_id'
        );
        $statement->execute();

        $remoteItems = [];
        foreach ($statement->fetchAll(\PDO::FETCH_ASSOC) as $item) {
            $categories = json_decode($item['category'], true);
            if (is_array($categories) && count($categories) > 0) {
                $articleId = $item['article_id'];
                $remoteItems[$articleId] = $categories;
            }
        }

        return $remoteItems;
    }

    /**
     * Helper function to create filter values
     * @param int $categoryId
     * @param bool $showOnlyConnectArticles
     * @param string $query
     * @return array
     */
    private function getAst($categoryId, $query = '', $showOnlyConnectArticles = true)
    {
        $ast = [
            [
                'type' => 'nullaryOperators',
                'token' => 'ISMAIN',
            ]
        ];

        if (trim($query) !== '') {
            $queryArray = [
                [
                    'type' => 'boolOperators',
                    'token' => 'AND',
                ],
                [
                    'type' => 'subOperators',
                    'token' => '(',
                ],
                [
                    'type' => 'attribute',
                    'token' => 'ARTICLE.NAME'
                ],
                [
                    'type' => 'binaryOperator',
                    'token' => '~'
                ],
                [
                    'type' => 'values',
                    'token' => '"' . $query . '"'
                ],
                [
                    'type' => 'boolOperators',
                    'token' => 'OR',
                ],
                [
                    'type' => 'attribute',
                    'token' => 'SUPPLIER.NAME'
                ],
                [
                    'type' => 'binaryOperator',
                    'token' => '~'
                ],
                [
                    'type' => 'values',
                    'token' => '"' . $query . '"'
                ],
                [
                    'type' => 'boolOperators',
                    'token' => 'OR',
                ],
                [
                    'type' => 'attribute',
                    'token' => 'DETAIL.NUMBER'
                ],
                [
                    'type' => 'binaryOperator',
                    'token' => '~'
                ],
                [
                    'type' => 'values',
                    'token' => '"' . $query . '"'
                ],
                [
                    'type' => 'subOperators',
                    'token' => ')',
                ]
            ];
            $ast = array_merge($ast, $queryArray);
        }

        $categoryArray = [
            [
            'type' => 'boolOperators',
            'token' => 'AND',
            ],
            [
                'type' => 'subOperators',
                'token' => '(',
            ],
            [
                'type' => 'attribute',
                'token' => 'CATEGORY.PATH',
            ],
            [
                'type' => 'binaryOperators',
                'token' => '=',
            ],
            [
                'type' => 'values',
                'token' => '"%|' . $categoryId . '|%"',
            ],
            [
                'type' => 'boolOperators',
                'token' => 'OR',
            ],
            [
                'type' => 'attribute',
                'token' => 'CATEGORY.ID',
            ],
            [
                'type' => 'binaryOperators',
                'token' => '=',
            ],
            [
                'type' => 'values',
                'token' => $categoryId,
            ],
            [
                'type' => 'subOperators',
                'token' => ')',
            ]
        ];

        $ast = array_merge($ast, $categoryArray);

        if ($showOnlyConnectArticles === true) {
            $ast = array_merge($ast, [
                [
                    'type' => 'boolOperators',
                    'token' => 'AND',
                ],
                [
                    'type' => 'attribute',
                    'token' => 'ATTRIBUTE.CONNECTMAPPEDCATEGORY',
                ],
                [
                    'type' => 'binaryOperators',
                    'token' => '!=',
                ],
                [
                    'type' => 'values',
                    'token' => 'NULL',
                ],
            ]);
        }

        return $ast;
    }

    /**
     * @param array $articleIds
     * @param $categoryId
     */
    private function unAssignArticlesFromCategory(array $articleIds, $categoryId)
    {
        $categories[] = $categoryId;
foreach ($collection as $item) {
    $myArray['foo'] = $item->getFoo();

    if ($item->hasBar()) {
        $myArray['bar'] = $item->getBar();
    }

    // do something with $myArray
}
        $childCategories = $this->manager->getConnection()->executeQuery('SELECT id FROM s_categories WHERE path LIKE ?',
            ["%|$categoryId|%"]);

        while ($childCategory = $childCategories->fetchColumn()) {
            $categories[] = (int) $childCategory;
        }

        $categoriesStatement = $this->manager->getConnection()->prepare('DELETE FROM s_articles_categories WHERE articleID IN (' . implode(', ', $articleIds) . ') AND categoryID IN (' . implode(', ', $categories) . ')');
        $categoriesStatement->execute();

        //Deletes all entries in s_articles_categories_ro that are not in the Path to a assigned category
        //need the nested select because mysql got an error otherwise
        //see: https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause
        $this->manager->getConnection()->executeQuery('
                  DELETE FROM s_articles_categories_ro
                  WHERE id NOT IN 
                    (SELECT id FROM 
                      (SELECT DISTINCT s_articles_categories_ro.id AS id FROM s_articles_categories_ro 
                        INNER JOIN s_articles_categories ON s_articles_categories.articleID = s_articles_categories_ro.articleID
                        INNER JOIN s_categories ON s_articles_categories.categoryID = s_categories.id
                        WHERE (s_categories.path LIKE CONCAT("%|", s_articles_categories_ro.categoryID, "|%") OR s_categories.id = s_articles_categories_ro.categoryID) 
                      ) AS temp
                    )
                  ');

        //we have to check the parent category too, because we can have following entries in s_articles_category_id:
        //|articleID|categoryID|parentCategoryID

        //|        1|         3|              5

        //|        1|         3|              6

        // -> category 6 got deleted from article but isn't delete in previous statement because categoryID is valid
        $this->manager->getConnection()->executeQuery('
                  DELETE FROM s_articles_categories_ro
                  WHERE parentCategoryID NOT IN 
                    (SELECT categoryID
                      FROM s_articles_categories
                      WHERE s_articles_categories.articleID = s_articles_categories_ro.articleID
                    )
                  ');


        $attributeStatement = $this->manager->getConnection()->prepare('
                    UPDATE s_articles_attributes 
                    SET connect_mapped_category = NULL 
                    WHERE articleID IN (' . implode(', ', $articleIds) . ') 
                      AND articleID NOT IN 
                        (SELECT DISTINCT articleID FROM s_articles_categories)
                ');
        $attributeStatement->execute();
    }

    /**
     * Unassign all categories from given article ids
     * @param array $articleIds
     * @throws \Doctrine\DBAL\ConnectionException
     * @throws \Exception
     */
    private function unAssignArticlesFromAllCategories(array $articleIds)
    {
        $attributeStatement = $this->manager->getConnection()->prepare(
            'UPDATE s_articles_attributes SET connect_mapped_category = NULL WHERE articleID IN (' . implode(', ', $articleIds) . ')'
        );
        $attributeStatement->execute();
        $categoriesStatement = $this->manager->getConnection()->prepare('DELETE FROM s_articles_categories WHERE articleID IN (' . implode(', ', $articleIds) . ')');
        $categoriesStatement->execute();
        $categoryLogStatement = $this->manager->getConnection()->prepare('DELETE FROM s_articles_categories_ro WHERE articleID IN (' . implode(', ', $articleIds) . ')');
        $categoryLogStatement->execute();
    }
}

shopware / SwagConnect

Pull Request — master (#394)

ImportService::activateArticles() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like
