ImportService::findRemoteArticleIdsByCategoryId() - 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-10-06 07:47 UTC

ImportService::findRemoteArticleIdsByCategoryId() A

↳ Parent: ImportService

Complexity

Conditions	1
Paths	1

Size

Total Lines	13
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	13
rs	9.4285
c	0
b	0
f	0
cc	1
eloc	7
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 = [];
        $categories[] = $categoryId;
        $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();

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

        $attributeStatement = $this->manager->getConnection()->prepare('
                    UPDATE s_articles_attributes 
                    SET connect_mapped_category = NULL 
                    WHERE articleID IN (' . implode(', ', $articleIds) . ') 
                      AND 
                        (SELECT COUNT(*) FROM s_articles_categories 
                            INNER JOIN s_categories_attributes ON s_articles_categories.categoryID = s_categories_attributes.categoryID
                            WHERE s_articles_categories.articleID = s_articles_attributes.articleID 
                                AND s_categories_attributes.connect_imported_category = 1
                        ) = 0
                ');
        $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();
    }
}


1			<?php
2			/**
3			* (c) shopware AG <[email protected]>
4			* For the full copyright and license information, please view the LICENSE
5			* file that was distributed with this source code.
6			*/
7
8			namespace ShopwarePlugins\Connect\Components;
9
10			use Doctrine\DBAL\Connection;
11			use Shopware\Bundle\AttributeBundle\Service\DataPersister;
12			use Shopware\Components\Model\CategoryDenormalization;
13			use ShopwarePlugins\Connect\Components\CategoryResolver\AutoCategoryResolver;
14			use Shopware\Components\Model\ModelManager;
15			use Shopware\Components\MultiEdit\Resource\ResourceInterface;
16			use Shopware\CustomModels\Connect\ProductToRemoteCategoryRepository;
17			use Shopware\CustomModels\Connect\RemoteCategoryRepository;
18			use Shopware\Models\Article\Repository as ArticleRepository;
19			use Shopware\Models\Category\Repository as CategoryRepository;
20
21			class ImportService
22			{
23			/**
24			* @var ModelManager
25			*/
26			private $manager;
27
28			/**
29			* @var \Shopware\Components\MultiEdit\Resource\ResourceInterface
30			*/
31			private $productResource;
32
33			/**
34			* @var \Shopware\Models\Category\Repository
35			*/
36			private $categoryRepository;
37
38			/**
39			* @var ArticleRepository
40			*/
41			private $articleRepository;
42
43			/**
44			* @var RemoteCategoryRepository
45			*/
46			private $remoteCategoryRepository;
47
48			/**
49			* @var ProductToRemoteCategoryRepository
50			*/
51			private $productToRemoteCategoryRepository;
52
53			/**
54			* @var CategoryResolver
55			*/
56			private $autoCategoryResolver;
57
58			/**
59			* @var CategoryExtractor
60			*/
61			private $categoryExtractor;
62
63			/**
64			* @var CategoryDenormalization
65			*/
66			private $categoryDenormalization;
67
68			/**
69			* @var DataPersister
70			*/
71			private $dataPersister;
72
73			public function __construct(
74			ModelManager $manager,
75			ResourceInterface $productResource,
76			CategoryRepository $categoryRepository,
77			ArticleRepository$articleRepository,
78			RemoteCategoryRepository $remoteCategoryRepository,
79			ProductToRemoteCategoryRepository $productToRemoteCategoryRepository,
80			AutoCategoryResolver $categoryResolver,
81			CategoryExtractor $categoryExtractor,
82			CategoryDenormalization $categoryDenormalization,
83			DataPersister $dataPersister
84			) {
85			$this->manager = $manager;
86			$this->productResource = $productResource;
87			$this->categoryRepository = $categoryRepository;
88			$this->articleRepository = $articleRepository;
89			$this->remoteCategoryRepository = $remoteCategoryRepository;
90			$this->productToRemoteCategoryRepository = $productToRemoteCategoryRepository;
91			$this->autoCategoryResolver = $categoryResolver;
92			$this->categoryExtractor = $categoryExtractor;
93			$this->categoryDenormalization = $categoryDenormalization;
94			$this->dataPersister = $dataPersister;
95			}
96
97			public function findBothArticlesType($categoryId, $query = '', $showOnlyConnectArticles = true, $limit = 10, $offset = 0)
98			{
99			if ($categoryId == 0) {
100			return [];
101			}
102
103			return $this->productResource->filter($this->getAst($categoryId, $query, $showOnlyConnectArticles), $offset, $limit);
104			}
105
106			/**
107			* @param $categoryId
108			* @return bool
109			*/
110			public function hasCategoryChildren($categoryId)
111			{
112			return (bool) $this->categoryRepository->getChildrenCountList($categoryId);
113			}
114
115			public function assignCategoryToArticles($categoryId, array $articleIds)
116			{
117			$articles = $this->articleRepository->findBy(['id' => $articleIds]);
118
119			if (empty($articles)) {
120			throw new \RuntimeException('Invalid article ids');
121			}
122
123			/** @var \Shopware\Models\Category\Category $category */
124			$category = $this->categoryRepository->find($categoryId);
125			if (!$category) {
126			throw new \RuntimeException('Invalid category id');
127			}
128
129			/** @var \Shopware\Models\Article\Article $article */
130			foreach ($articles as $article) {
131			$article->addCategory($category);
132			$this->manager->persist($article);
133			/** @var \Shopware\Models\Article\Detail $detail */
134			foreach ($article->getDetails() as $detail) {
135			$attribute = $detail->getAttribute();
136			$attribute->setConnectMappedCategory(true);
137			$this->manager->persist($attribute);
138			}
139			}
140
141			$this->manager->flush();
142			}
143
144			/**
145			* Unassign categories from given article ids
146			* for the given categoryId and all childcategories
147			* or for all categories if $categoryId is null
148			* Set connect_mapped_category flag in article
149			* attributes to NULL
150			*
151			* @param array $articleIds
152			* @param int\|null $categoryId
153			* @throws \Doctrine\DBAL\ConnectionException
154			* @throws \Exception
155			*/
156			public function unAssignArticleCategories(array $articleIds, $categoryId = null)
157			{
158			if (!empty($articleIds)) {
159			// cast all items in $articleIds to int
160			// before use them in WHERE IN clause
161			foreach ($articleIds as $key => $articleId) {
162			$articleIds[$key] = (int) $articleId;
163			}
164
165			$connection = $this->manager->getConnection();
166			$connection->beginTransaction();
167
168			try {
169			if ($categoryId !== null) {
170			$this->unAssignArticlesFromCategory($articleIds, $categoryId);
171			} else {
172			$this->unAssignArticlesFromAllCategories($articleIds);
173			}
174
175			$connection->commit();
176			} catch (\Exception $e) {
177			$connection->rollBack();
178			throw new \Exception($e->getMessage());
179			}
180			}
181			}
182
183			/**
184			* Collect remote article ids by given category id
185			*
186			* @param int $localCategoryId
187			* @return array
188			*/
189			public function findRemoteArticleIdsByCategoryId($localCategoryId)
190			{
191			$connection = $this->manager->getConnection();
192			$sql = 'SELECT sac.articleID
193			FROM s_articles_categories sac
194			LEFT JOIN s_articles_attributes saa ON sac.articleID = saa.articleID
195			WHERE sac.categoryID = :categoryId AND saa.connect_mapped_category = 1';
196			$rows = $connection->fetchAll($sql, [':categoryId' => $localCategoryId]);
197
198			return array_map(function ($row) {
199			return $row['articleID'];
200			}, $rows);
201			}
202
203			/**
204			* @param array $categoryIds
205			* @return int
206			*/
207			public function deactivateLocalCategoriesByIds(array $categoryIds)
208			{
209			$builder = $this->manager->getConnection()->createQueryBuilder();
210			$rowCount = $builder->update('s_categories', 'c')
211			->set('c.active', 0)
212			->where('c.id IN (:categoryIds)')
213			->setParameter('categoryIds', $categoryIds, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY)
214			->execute();
215
216			return $rowCount;
217			}
218
219			/**
220			* Collect all child categories by given
221			* remote category key and create same
222			* categories structure as Shopware Connect structure.
223			* Find all remote products which belong to these categories
224			* and assign them.
225			*
226			* @param int $localCategoryId
227			* @param string $remoteCategoryKey
228			* @param string $remoteCategoryLabel
229			* @return void
230			*/
231			public function importRemoteCategory($localCategoryId, $remoteCategoryKey, $remoteCategoryLabel)
232			{
233			/** @var \Shopware\Models\Category\Category $localCategory */
234			$localCategory = $this->categoryRepository->find((int) $localCategoryId);
235			if (!$localCategory) {
236			throw new \RuntimeException('Local category not found!');
237			}
238
239			/** @var \Shopware\CustomModels\Connect\RemoteCategory $remoteCategory */
240			$remoteCategory = $this->remoteCategoryRepository->findOneBy(['categoryKey' => $remoteCategoryKey]);
241			if (!$remoteCategory) {
242			throw new \RuntimeException('Remote category not found!');
243			}
244
245			// collect his child categories and
246			// generate remote category tree by given remote category
247			$remoteCategoryChildren = $this->categoryExtractor->getRemoteCategoriesTree($remoteCategoryKey, true);
248			$remoteCategoryNodes = [
249			[
250			'name' => $remoteCategoryLabel,
251			'categoryId' => $remoteCategoryKey,
252			'leaf' => empty($remoteCategoryChildren) ? true : false,
253			'children' => $remoteCategoryChildren,
254			]
255			];
256
257			// create same category structure as Shopware Connect structure
258			$categories = $this->autoCategoryResolver->convertTreeToKeys($remoteCategoryNodes, $localCategory->getId(), false);
259
260			foreach ($categories as $category) {
261			$articleIds = $this->productToRemoteCategoryRepository->findArticleIdsByRemoteCategory($category['remoteCategory']);
262			foreach ($articleIds as $articleId) {
263			$this->categoryDenormalization->addAssignment($articleId, $category['categoryKey']);
264			$this->categoryDenormalization->removeAssignment($articleId, $category['parentId']);
265			$this->manager->getConnection()->executeQuery(
266			'INSERT IGNORE INTO `s_articles_categories` (`articleID`, `categoryID`) VALUES (?, ?)',
267			[$articleId, $category['categoryKey']]
268			);
269			$this->manager->getConnection()->executeQuery(
270			'DELETE FROM `s_articles_categories` WHERE `articleID` = :articleID AND `categoryID` = :categoryID',
271			[
272			':articleID' => $articleId,
273			':categoryID' => $category['parentId']
274			]
275			);
276			$detailId = $this->manager->getConnection()->fetchColumn(
277			'SELECT id FROM `s_articles_details` WHERE `articleID` = :articleID',
278			['articleID' => $articleId]
279			);
280			$this->manager->getConnection()->executeQuery(
281			'INSERT INTO `s_articles_attributes` (`articleID`, `articledetailsID`, `connect_mapped_category`)
282			VALUES (?, ?, 1)
283			ON DUPLICATE KEY UPDATE `connect_mapped_category` = 1
284			',
285			[$articleId, $detailId]
286			);
287			}
288			}
289			}
290
291			/**
292			* @param array $articleIds
293			*/
294			public function activateArticles(array $articleIds)
295			{
296			$articleBuilder = $this->manager->createQueryBuilder();
297			$articleBuilder->update('\Shopware\Models\Article\Article', 'a')
298			->set('a.active', 1)
299			->where('a.id IN (:articleIds)')
300			->setParameter(':articleIds', $articleIds, Connection::PARAM_STR_ARRAY);
301
302			$articleBuilder->getQuery()->execute();
303
304			$detailBuilder = $this->manager->createQueryBuilder();
305			$detailBuilder->update('\Shopware\Models\Article\Detail', 'd')
306			->set('d.active', 1)
307			->where('d.articleId IN (:articleIds)')
308			->setParameter(':articleIds', $articleIds, Connection::PARAM_STR_ARRAY);
309
310			$detailBuilder->getQuery()->execute();
311			}
312
313			/**
314			* Store remote categories in Connect tables
315			* and add relations between categories and products.
316			*
317			* @param array $remoteItems
318			*
319			* @throws \Exception
320			*/
321			public function storeRemoteCategories(array $remoteItems)
322			{
323			$connection = $this->manager->getConnection();
324
325			$connection->beginTransaction();
326			try {
327			foreach ($remoteItems as $articleId => $categories) {
328			foreach ($categories as $categoryKey => $category) {
329			$connection->executeQuery(
330			'INSERT IGNORE INTO `s_plugin_connect_categories` (`category_key`, `label`) VALUES (?, ?)',
331			[$categoryKey, $category]
332			);
333
334			$connection->executeQuery(
335			'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 = ?), ?)',
336			[$categoryKey, $articleId]
337			);
338			}
339			}
340			$connection->commit();
341			} catch (\Exception $e) {
342			$connection->rollBack();
343
344			throw $e;
345			}
346			}
347
348			/**
349			* Fetch remote (Connect) categories by given article ids
350			* @param array $articleIds
351			* @return array
352			*/
353			public function fetchRemoteCategoriesByArticleIds(array $articleIds)
354			{
355			$remoteCategoryIds = [];
356			while ($currentIdBatch = array_splice($articleIds, 0, 500)) {
357			$sql = 'SELECT sac.categoryID
358			FROM s_articles_categories sac
359			LEFT JOIN s_categories_attributes attr ON sac.categoryID = attr.categoryID
360			WHERE attr.connect_imported_category = 1 AND sac.articleID IN (' . implode(', ', $currentIdBatch) . ') GROUP BY sac.categoryID';
361			$rows = $this->manager->getConnection()->fetchAll($sql);
362
363			$remoteCategoryIds = array_merge($remoteCategoryIds, array_map(function ($row) {
364			return $row['categoryID'];
365			}, $rows));
366			}
367
368			return array_unique($remoteCategoryIds);
369			}
370
371			/**
372			* Fetch all articles where categories are auto imported
373			* and there isn't record in s_plugin_connect_product_to_categories for them.
374			* Returned array contains key = articleId and value = array of categories
375			*
376			* @return array
377			*/
378			public function getArticlesWithAutoImportedCategories()
379			{
380			$statement = $this->manager->getConnection()->prepare(
381			'SELECT b.article_id, b.category
382			FROM s_plugin_connect_items b
383			LEFT JOIN s_plugin_connect_product_to_categories a ON b.article_id = a.articleID
384			WHERE b.shop_id > 0 AND a.connect_category_id IS NULL GROUP BY b.article_id'
385			);
386			$statement->execute();
387
388			$remoteItems = [];
389			foreach ($statement->fetchAll(\PDO::FETCH_ASSOC) as $item) {
390			$categories = json_decode($item['category'], true);
391			if (is_array($categories) && count($categories) > 0) {
392			$articleId = $item['article_id'];
393			$remoteItems[$articleId] = $categories;
394			}
395			}
396
397			return $remoteItems;
398			}
399
400			/**
401			* Helper function to create filter values
402			* @param int $categoryId
403			* @param bool $showOnlyConnectArticles
404			* @param string $query
405			* @return array
406			*/
407			private function getAst($categoryId, $query = '', $showOnlyConnectArticles = true)
408			{
409			$ast = [
410			[
411			'type' => 'nullaryOperators',
412			'token' => 'ISMAIN',
413			]
414			];
415
416			if (trim($query) !== '') {
417			$queryArray = [
418			[
419			'type' => 'boolOperators',
420			'token' => 'AND',
421			],
422			[
423			'type' => 'subOperators',
424			'token' => '(',
425			],
426			[
427			'type' => 'attribute',
428			'token' => 'ARTICLE.NAME'
429			],
430			[
431			'type' => 'binaryOperator',
432			'token' => '~'
433			],
434			[
435			'type' => 'values',
436			'token' => '"' . $query . '"'
437			],
438			[
439			'type' => 'boolOperators',
440			'token' => 'OR',
441			],
442			[
443			'type' => 'attribute',
444			'token' => 'SUPPLIER.NAME'
445			],
446			[
447			'type' => 'binaryOperator',
448			'token' => '~'
449			],
450			[
451			'type' => 'values',
452			'token' => '"' . $query . '"'
453			],
454			[
455			'type' => 'boolOperators',
456			'token' => 'OR',
457			],
458			[
459			'type' => 'attribute',
460			'token' => 'DETAIL.NUMBER'
461			],
462			[
463			'type' => 'binaryOperator',
464			'token' => '~'
465			],
466			[
467			'type' => 'values',
468			'token' => '"' . $query . '"'
469			],
470			[
471			'type' => 'subOperators',
472			'token' => ')',
473			]
474			];
475			$ast = array_merge($ast, $queryArray);
476			}
477
478			$categoryArray = [
479			[
480			'type' => 'boolOperators',
481			'token' => 'AND',
482			],
483			[
484			'type' => 'subOperators',
485			'token' => '(',
486			],
487			[
488			'type' => 'attribute',
489			'token' => 'CATEGORY.PATH',
490			],
491			[
492			'type' => 'binaryOperators',
493			'token' => '=',
494			],
495			[
496			'type' => 'values',
497			'token' => '"%\|' . $categoryId . '\|%"',
498			],
499			[
500			'type' => 'boolOperators',
501			'token' => 'OR',
502			],
503			[
504			'type' => 'attribute',
505			'token' => 'CATEGORY.ID',
506			],
507			[
508			'type' => 'binaryOperators',
509			'token' => '=',
510			],
511			[
512			'type' => 'values',
513			'token' => $categoryId,
514			],
515			[
516			'type' => 'subOperators',
517			'token' => ')',
518			]
519			];
520
521			$ast = array_merge($ast, $categoryArray);
522
523			if ($showOnlyConnectArticles === true) {
524			$ast = array_merge($ast, [
525			[
526			'type' => 'boolOperators',
527			'token' => 'AND',
528			],
529			[
530			'type' => 'attribute',
531			'token' => 'ATTRIBUTE.CONNECTMAPPEDCATEGORY',
532			],
533			[
534			'type' => 'binaryOperators',
535			'token' => '!=',
536			],
537			[
538			'type' => 'values',
539			'token' => 'NULL',
540			],
541			]);
542			}
543
544			return $ast;
545			}
546
547			/**
548			* @param array $articleIds
549			* @param $categoryId
550			*/
551			private function unAssignArticlesFromCategory(array $articleIds, $categoryId)
552			{
553			$categories = [];
554			$categories[] = $categoryId;
555			$childCategories = $this->manager->getConnection()->executeQuery('SELECT id FROM s_categories WHERE path LIKE ?',
556			["%\|$categoryId\|%"]);
557
558			while ($childCategory = $childCategories->fetchColumn()) {
559			$categories[] = (int) $childCategory;
560			}
561
562			$categoriesStatement = $this->manager->getConnection()->prepare('DELETE FROM s_articles_categories WHERE articleID IN (' . implode(', ', $articleIds) . ') AND categoryID IN (' . implode(', ', $categories) . ')');
563			$categoriesStatement->execute();
564
565			$categoriesStatement = $this->manager->getConnection()->prepare('DELETE FROM s_articles_categories_ro WHERE articleID IN (' . implode(', ', $articleIds) . ') AND parentCategoryID IN (' . implode(', ', $categories) . ')');
566			$categoriesStatement->execute();
567
568			$attributeStatement = $this->manager->getConnection()->prepare('
569			UPDATE s_articles_attributes
570			SET connect_mapped_category = NULL
571			WHERE articleID IN (' . implode(', ', $articleIds) . ')
572			AND
573			(SELECT COUNT(*) FROM s_articles_categories
574			INNER JOIN s_categories_attributes ON s_articles_categories.categoryID = s_categories_attributes.categoryID
575			WHERE s_articles_categories.articleID = s_articles_attributes.articleID
576			AND s_categories_attributes.connect_imported_category = 1
577			) = 0
578			');
579			$attributeStatement->execute();
580			}
581
582			/**
583			* Unassign all categories from given article ids
584			* @param array $articleIds
585			* @throws \Doctrine\DBAL\ConnectionException
586			* @throws \Exception
587			*/
588			private function unAssignArticlesFromAllCategories(array $articleIds)
589			{
590			$attributeStatement = $this->manager->getConnection()->prepare(
591			'UPDATE s_articles_attributes SET connect_mapped_category = NULL WHERE articleID IN (' . implode(', ', $articleIds) . ')'
592			);
593			$attributeStatement->execute();
594			$categoriesStatement = $this->manager->getConnection()->prepare('DELETE FROM s_articles_categories WHERE articleID IN (' . implode(', ', $articleIds) . ')');
595			$categoriesStatement->execute();
596			$categoryLogStatement = $this->manager->getConnection()->prepare('DELETE FROM s_articles_categories_ro WHERE articleID IN (' . implode(', ', $articleIds) . ')');
597			$categoryLogStatement->execute();
598			}
599			}
600

shopware / SwagConnect

Pull Request — master (#394)

ImportService::findRemoteArticleIdsByCategoryId() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like