/**
* @inheritdoc
*/
public function getListByCategory($products, Struct\ShopContextInterface $context)
{
if (!$this->config->offsetExists('similarLimit') || $this->config->get('similarLimit') <= 0) {
return [];
}
$ids = [];
foreach ($products as $product) {
$ids[] = $product->getId();
}
$ids = array_unique($ids);
$categoryId = 1;
if ($context->getShop() && $context->getShop()->getCategory()) {
$categoryId = $context->getShop()->getCategory()->getId();
}
$query = $this->connection->createQueryBuilder();
$query->select(['main.articleID', "GROUP_CONCAT(subVariant.ordernumber SEPARATOR '|') as similar"]);
$query->from('s_articles_categories', 'main');
$query->innerJoin('main', 's_articles_categories', 'sub', 'sub.categoryID = main.categoryID AND sub.articleID != main.articleID');
$query->innerJoin('sub', 's_articles_details', 'subVariant', 'subVariant.articleID = sub.articleID AND subVariant.kind = 1');
$query->innerJoin('main', 's_categories', 'category', 'category.id = sub.categoryID AND category.id = main.categoryID');
$query->where('main.articleID IN (:ids)')->andWhere('category.path LIKE :path');
$query->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY)->setParameter(':path', '%|' . (int) $categoryId . '|');
$query->groupBy('main.articleID');
$statement = $query->execute();
$data = $statement->fetchAll(\PDO::FETCH_ASSOC);
$limit = (int) $this->config->get('similarLimit');
$result = [];
foreach ($data as $row) {
$similar = explode('|', $row['similar']);
$result[$row['articleID']] = array_slice($similar, 0, $limit);
}
return $result;
}
/**
* Returns all config elements of the passed template.
*
* @param \Shopware\Models\Shop\Template $template
* @return ArrayCollection
*/
private function getElements(Shop\Template $template)
{
$builder = $this->entityManager->createQueryBuilder();
$builder->select('elements')->from('Shopware\\Models\\Shop\\TemplateConfig\\Element', 'elements')->where('elements.templateId = :templateId')->setParameter('templateId', $template->getId());
$elements = $builder->getQuery()->getResult();
$elements = $this->eventManager->filter('Theme_Configurator_Elements_Loaded', $elements, array('template' => $template));
return new ArrayCollection($elements);
}
/**
* collects the settings data for the committed shopId
*
* @param $shopId
* @return array
*/
public function getSettings($shopId)
{
if (!$shopId) {
/** @var Repository $shopRepository */
$shopRepository = $this->em->getRepository('Shopware\\Models\\Shop\\Shop');
$shop = $shopRepository->getActiveDefault();
$shopId = $shop->getId();
}
$builder = $this->em->createQueryBuilder();
$builder->select(array('settings'))->from('Shopware\\CustomModels\\TrustedShops\\TrustedShops', 'settings')->where('settings.shopId = :shopId')->setParameters(array('shopId' => $shopId));
$data = $builder->getQuery()->getArrayResult();
if (empty($data)) {
$data = $this->getDefaultData($shopId, $builder);
}
if (array_key_exists(0, $data)) {
$data = $data[0];
}
return $data;
}
/**
* Returns the query builder object to select the theme configuration for the
* current shop.
*
* @param \Shopware\Models\Shop\Template $template
* @param boolean $lessCompatible
* @return \Doctrine\ORM\QueryBuilder|\Shopware\Components\Model\QueryBuilder
* @throws \Enlight_Event_Exception
*/
private function getShopConfigQuery(Shop\Template $template, $lessCompatible)
{
$builder = $this->entityManager->createQueryBuilder();
$builder->select(array('element.name', 'values.value', 'element.defaultValue', 'element.type'));
$builder->from('Shopware\\Models\\Shop\\TemplateConfig\\Element', 'element')->leftJoin('element.values', 'values', 'WITH', 'values.shopId = :shopId')->where('element.templateId = :templateId');
if ($lessCompatible) {
$builder->andWhere('element.lessCompatible = 1');
}
$this->eventManager->notify('Theme_Inheritance_Shop_Query_Built', array('builder' => $builder, 'template' => $template));
return $builder;
}
/**
* @param $start
* @param $limit
* @param $filter
* @return array
*/
public function readRecordIds($start, $limit, $filter)
{
$builder = $this->manager->createQueryBuilder();
$builder->select('t.id')->from(Translation::class, 't')->where('t.type IN (:types)')->setParameter('types', ['article', 'variant']);
if ($start) {
$builder->setFirstResult($start);
}
if ($limit) {
$builder->setMaxResults($limit);
}
$records = $builder->getQuery()->getArrayResult();
$result = array_column($records, 'id');
return $result;
}
/**
* Returns the sort mode for the passed value ids.
* If the value ids contains more than one property set, the
* global fallback sort mode is used.
*
* @param array $valueIds
* @return int
*/
private function getSortMode(array $valueIds)
{
$query = $this->connection->createQueryBuilder();
$query->select('DISTINCT propertySet.sortmode')->from('s_filter', 'propertySet');
$query->innerJoin('propertySet', 's_filter_relations', 'relations', 'relations.groupID = propertySet.id');
$query->innerJoin('relations', 's_filter_values', 'propertyOption', 'relations.optionID = propertyOption.optionID');
$query->where('propertyOption.id IN (:ids)')->setParameter(':ids', $valueIds, Connection::PARAM_INT_ARRAY);
/**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
$statement = $query->execute();
$rows = $statement->fetchAll(\PDO::FETCH_COLUMN);
if (count($rows) == 1) {
return $rows[0];
} else {
return $this->config->get('defaultFilterSort', self::FILTERS_SORT_POSITION);
}
}
/**
* Returns the configuration sets for the passed template.
* This function returns additionally the inheritance
* configuration sets of the passed template.
* The sets are translated automatically.
*
* @param Shop\Template $template
* @return array
*/
public function getConfigSets(Shop\Template $template)
{
$builder = $this->entityManager->createQueryBuilder();
$builder->select(array('template', 'sets'))->from('Shopware\\Models\\Shop\\Template', 'template')->innerJoin('template.configSets', 'sets')->where('sets.templateId = :templateId')->orderBy('sets.name')->setParameter('templateId', $template->getId());
$themes = $builder->getQuery()->getArrayResult();
$namespace = $this->getConfigSnippetNamespace($template);
$namespace->read();
foreach ($themes as &$theme) {
$theme = $this->translateThemeData($theme, $namespace);
foreach ($theme['configSets'] as &$set) {
$set = $this->translateConfigSet($set, $namespace);
}
}
$instance = $this->util->getThemeByTemplate($template);
if ($template->getParent() instanceof Shop\Template && $instance->useInheritanceConfig()) {
$themes = array_merge($themes, $this->getConfigSets($template->getParent()));
}
return $themes;
}
/**
* @inheritdoc
*/
public function getList(array $valueIds, Struct\ShopContextInterface $context)
{
$query = $this->connection->createQueryBuilder();
$query->addSelect('relations.position as __relations_position')->addSelect($this->fieldHelper->getPropertySetFields())->addSelect($this->fieldHelper->getPropertyGroupFields())->addSelect($this->fieldHelper->getPropertyOptionFields())->addSelect($this->fieldHelper->getMediaFields());
$query->from('s_filter', 'propertySet');
$query->innerJoin('propertySet', 's_filter_relations', 'relations', 'relations.groupID = propertySet.id');
$query->leftJoin('propertySet', 's_filter_attributes', 'propertySetAttribute', 'propertySetAttribute.filterID = propertySet.id');
$query->innerJoin('relations', 's_filter_options', 'propertyGroup', 'relations.optionID = propertyGroup.id
AND filterable = 1');
$query->innerJoin('propertyGroup', 's_filter_values', 'propertyOption', 'propertyOption.optionID = propertyGroup.id');
$query->leftJoin('propertyOption', 's_media', 'media', 'propertyOption.media_id = media.id');
$query->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id');
$query->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID');
$this->fieldHelper->addAllPropertyTranslations($query, $context);
$query->groupBy('propertyOption.id');
$query->where('propertyOption.id IN (:ids)')->setParameter(':ids', $valueIds, Connection::PARAM_INT_ARRAY);
$query->orderBy('propertySet.position');
/**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
$statement = $query->execute();
$rows = $statement->fetchAll(\PDO::FETCH_ASSOC);
return $this->propertyHydrator->hydrateValues($rows);
}
/**
* @param $columns
* @param $ids
* @return QueryBuilder
*/
public function getBuilder($columns, $ids)
{
$builder = $this->modelManager->createQueryBuilder();
$builder->select($columns)->from(Detail::class, 'variant')->leftJoin('variant.article', 'article')->leftJoin('article.supplier', 'articleSupplier')->leftJoin('variant.prices', 'prices')->leftJoin('prices.customerGroup', 'customerGroup')->leftJoin('article.tax', 'articleTax')->where('variant.id IN (:ids)')->andWhere('prices.customerGroup = :customergroup')->setParameters(['ids' => $ids, 'customergroup' => 'EK']);
return $builder;
}
/**
* @param array $columns
* @param array $ids
* @return \Shopware\Components\Model\QueryBuilder
*/
public function getBuilder($columns, $ids)
{
$builder = $this->manager->createQueryBuilder();
$builder->select($columns)->from(Customer::class, 'customer')->join('customer.billing', 'billing')->leftJoin('customer.shipping', 'shipping')->leftJoin('customer.orders', 'orders', 'WITH', 'orders.status <> -1 AND orders.status <> 4')->leftJoin('billing.attribute', 'billingAttribute')->leftJoin('shipping.attribute', 'shippingAttribute')->leftJoin('customer.attribute', 'attribute')->groupBy('customer.id')->where('customer.id IN (:ids)')->setParameter('ids', $ids);
return $builder;
}
/**
* @param $columns
* @param $ids
* @return QueryBuilder
*/
public function getBuilder($columns, $ids)
{
$builder = $this->manager->createQueryBuilder();
$builder->select($columns)->from(Address::class, 'na')->leftJoin('na.newsletterGroup', 'ng')->leftJoin(ContactData::class, 'cd', Join::WITH, 'na.email = cd.email')->leftJoin('na.customer', 'c')->leftJoin('c.billing', 'cb')->where('na.id IN (:ids)')->setParameter('ids', $ids);
return $builder;
}
/**
* Create the Doctrine Querybuilder
*
* @return \Doctrine\ORM\QueryBuilder|QueryBuilder
*/
private function getQueryBuilder()
{
$builder = $this->em->createQueryBuilder();
$builder->select(array('article.id'))->from('Shopware\\Models\\Article\\Article', 'article')->innerJoin('article.attribute', 'attribute')->innerJoin('article.details', 'details')->andWhere('article.name = :name')->andWhere('attribute.swagIsTrustedShopsArticle = 1')->andWhere('details.number LIKE :number')->setParameter('name', 'Käuferschutz')->setParameter('number', 'TS%');
return $builder;
}
/**
* @param array $columns
* @param array $ids
* @return \Doctrine\ORM\QueryBuilder|\Shopware\Components\Model\QueryBuilder
*/
public function getBuilder($columns, $ids)
{
$builder = $this->manager->createQueryBuilder();
$builder->select($columns)->from(Image::class, 'aimage')->innerJoin('aimage.article', 'article')->leftJoin('Shopware\\Models\\Article\\Detail', 'mv', Join::WITH, 'mv.articleId=article.id AND mv.kind=1')->leftJoin('aimage.mappings', 'im')->leftJoin('im.rules', 'mr')->leftJoin('mr.option', 'co')->leftJoin('co.group', 'cg')->where('aimage.id IN (:ids)')->groupBy('aimage.id')->setParameter('ids', $ids);
return $builder;
}
/**
* @param $columns
* @param $ids
* @return QueryBuilder
*/
private function getBuilder($columns, $ids)
{
$builder = $this->modelManager->createQueryBuilder();
$builder->select($columns)->from('Shopware\\Models\\Order\\Detail', 'details')->leftJoin('details.order', 'orders')->leftJoin('details.tax', 'taxes')->leftJoin('orders.billing', 'billing')->leftJoin('billing.country', 'billingCountry')->leftJoin('orders.shipping', 'shipping')->leftJoin('shipping.country', 'shippingCountry')->leftJoin('orders.payment', 'payment')->leftJoin('orders.paymentStatus', 'paymentStatus')->leftJoin('orders.orderStatus', 'orderStatus')->leftJoin('orders.dispatch', 'dispatch')->leftJoin('orders.customer', 'customer')->leftJoin('orders.attribute', 'attr')->where('details.id IN (:ids)')->setParameter('ids', $ids);
return $builder;
}
/**
* @param array $ids
* @return \Doctrine\ORM\QueryBuilder|\Shopware\Components\Model\QueryBuilder
*/
public function getTaxSumBuilder($ids)
{
$builder = $this->modelManager->createQueryBuilder();
$builder->select(['details.orderId, orders.invoiceAmount, orders.invoiceAmountNet, orders.invoiceShipping, orders.invoiceShippingNet, orders.net, details.price, details.quantity, details.taxId, details.taxRate'])->from(Order::class, 'orders')->leftJoin('orders.details', 'details')->where('details.orderId IN (:ids)')->andWhere('orders.taxFree = 0')->setParameter('ids', $ids)->orderBy('details.orderId, details.taxRate', 'ASC');
return $builder;
}
/**
* Returns an object list with all installed and activated plugins.
*
* @return array
*/
public function getActivePlugins()
{
$builder = $this->entityManager->createQueryBuilder();
$builder->select(array('plugins'))->from('Shopware\\Models\\Plugin\\Plugin', 'plugins')->where('plugins.active = true')->andWhere('plugins.installed IS NOT NULL');
return $builder->getQuery()->getResult(AbstractQuery::HYDRATE_OBJECT);
}
/**
* @param $columns
* @param $ids
* @return \Doctrine\ORM\QueryBuilder|\Shopware\Components\Model\QueryBuilder
*/
public function getCategoryBuilder($columns, $ids)
{
$categoryBuilder = $this->modelManager->createQueryBuilder();
$categoryBuilder->select($columns)->from('Shopware\\Models\\Article\\Article', 'article')->leftJoin('article.categories', 'categories')->where('article.id IN (:ids)')->andWhere('categories.id IS NOT NULL')->setParameter('ids', $ids);
return $categoryBuilder;
}
/**
* @param $columns
* @param $ids
* @return \Shopware\Components\Model\QueryBuilder
*/
public function getBuilder($columns, $ids)
{
$builder = $this->modelManager->createQueryBuilder();
$builder->select($columns)->from(Category::class, 'c')->leftJoin('c.attribute', 'attr')->leftJoin('c.customerGroups', 'customerGroups')->where('c.id IN (:ids)')->setParameter('ids', $ids, Connection::PARAM_INT_ARRAY)->distinct();
return $builder;
}
