/** * @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; }