Example #1
0
 /**
  * @inheritdoc
  */
 public function getList($products, Struct\ShopContextInterface $context)
 {
     $ids = [];
     foreach ($products as $product) {
         $ids[] = $product->getId();
     }
     $ids = array_unique($ids);
     $query = $this->connection->createQueryBuilder();
     $query->addSelect($this->fieldHelper->getVoteFields());
     $query->from('s_articles_vote', 'vote')->where('vote.articleID IN (:ids)')->andWhere('vote.active = 1')->orderBy('vote.articleID', 'DESC')->addOrderBy('vote.datum', 'DESC')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
     $votes = [];
     foreach ($data as $row) {
         $id = $row['__vote_articleID'];
         $votes[$id][] = $this->voteHydrator->hydrate($row);
     }
     $result = [];
     foreach ($products as $product) {
         $id = $product->getId();
         if (!isset($votes[$id])) {
             continue;
         }
         $number = $product->getNumber();
         $result[$number] = $votes[$id];
     }
     return $result;
 }
 /**
  * @inheritdoc
  */
 public function getList($products, Struct\ShopContextInterface $context)
 {
     $ids = [];
     foreach ($products as $product) {
         $ids[] = $product->getId();
     }
     $ids = array_unique($ids);
     $query = $this->connection->createQueryBuilder();
     $query->select(['relation.article_id']);
     $query->addSelect($this->fieldHelper->getRelatedProductStreamFields());
     $query->from('s_product_streams_articles', 'relation');
     $query->innerJoin('relation', 's_product_streams', 'stream', 'stream.id = relation.stream_id');
     $this->fieldHelper->addProductStreamTranslation($query, $context);
     $query->where('relation.article_id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_GROUP);
     $related = [];
     foreach ($data as $productId => $data) {
         $related[$productId] = [];
         foreach ($data as $row) {
             $related[$productId][] = $this->hydrator->hydrate($row);
         }
     }
     return $related;
 }
Example #3
0
 /**
  * @inheritdoc
  */
 public function getList($products, Struct\ShopContextInterface $context)
 {
     $ids = [];
     foreach ($products as $product) {
         $ids[] = $product->getId();
     }
     $ids = array_unique($ids);
     $query = $this->connection->createQueryBuilder();
     $query->select($this->fieldHelper->getDownloadFields());
     $query->from('s_articles_downloads', 'download')->leftJoin('download', 's_articles_downloads_attributes', 'downloadAttribute', 'downloadAttribute.downloadID = download.id');
     $query->where('download.articleID IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
     $downloads = [];
     foreach ($data as $row) {
         $key = $row['__download_articleID'];
         $download = $this->downloadHydrator->hydrate($row);
         $downloads[$key][] = $download;
     }
     $result = [];
     foreach ($products as $product) {
         if (isset($downloads[$product->getId()])) {
             $result[$product->getNumber()] = $downloads[$product->getId()];
         }
     }
     return $result;
 }
 /**
  * @inheritdoc
  */
 public function get(Struct\BaseProduct $product, Struct\ShopContextInterface $context)
 {
     $query = $this->getQuery();
     $query->addSelect($this->fieldHelper->getConfiguratorSetFields())->addSelect($this->fieldHelper->getConfiguratorGroupFields())->addSelect($this->fieldHelper->getConfiguratorOptionFields());
     $this->fieldHelper->addConfiguratorTranslation($query, $context);
     $query->where('products.id = :id')->setParameter(':id', $product->getId());
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
     return $this->configuratorHydrator->hydrate($data);
 }
 /**
  * @inheritdoc
  */
 public function getList(array $keys)
 {
     $query = $this->connection->createQueryBuilder();
     $query->select($this->fieldHelper->getCustomerGroupFields());
     $query->from('s_core_customergroups', 'customerGroup')->leftJoin('customerGroup', 's_core_customergroups_attributes', 'customerGroupAttribute', 'customerGroupAttribute.customerGroupID = customerGroup.id');
     $query->where('customerGroup.groupkey IN (:keys)')->setParameter(':keys', $keys, Connection::PARAM_STR_ARRAY);
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
     $customerGroups = [];
     foreach ($data as $group) {
         $key = $group['__customerGroup_groupkey'];
         $customerGroups[$key] = $this->customerGroupHydrator->hydrate($group);
     }
     return $customerGroups;
 }
Example #6
0
 /**
  * @inheritdoc
  */
 public function getList(array $valueIds, Struct\ShopContextInterface $context)
 {
     $query = $this->connection->createQueryBuilder();
     $sortMode = $this->getSortMode($valueIds);
     $query->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')->addOrderBy('propertySet.id')->addOrderBy('relations.position')->addOrderBy('propertyGroup.name');
     switch ($sortMode) {
         case self::FILTERS_SORT_NUMERIC:
             $query->addOrderBy('propertyOption.value_numeric');
             break;
         case self::FILTERS_SORT_POSITION:
             $query->addOrderBy('propertyOption.position');
             break;
         default:
             $query->addOrderBy('propertyOption.value');
     }
     $query->addOrderBy('propertyOption.id');
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $rows = $statement->fetchAll(\PDO::FETCH_ASSOC);
     return $this->propertyHydrator->hydrateValues($rows);
 }
 /**
  * @inheritdoc
  */
 public function getList($products, Struct\ShopContextInterface $context)
 {
     $ids = [];
     foreach ($products as $product) {
         $ids[] = $product->getId();
     }
     $ids = array_unique($ids);
     $query = $this->connection->createQueryBuilder();
     $query->addSelect('products.id as productId')->addSelect($this->fieldHelper->getPropertySetFields())->addSelect($this->fieldHelper->getPropertyGroupFields())->addSelect($this->fieldHelper->getPropertyOptionFields())->addSelect($this->fieldHelper->getMediaFields());
     $query->addSelect('
     (
         CASE
             WHEN propertySet.sortmode = 1 THEN propertyOption.value_numeric
             WHEN propertySet.sortmode = 3 THEN propertyOption.position
             ELSE propertyOption.value
         END
     ) as sortRelevance');
     $query->from('s_filter_articles', 'filterArticles');
     $query->innerJoin('filterArticles', 's_articles', 'products', 'products.id = filterArticles.articleID');
     $query->innerJoin('filterArticles', 's_filter_values', 'propertyOption', 'propertyOption.id = filterArticles.valueID');
     $query->innerJoin('products', 's_filter', 'propertySet', 'propertySet.id = products.filtergroupID');
     $query->leftJoin('propertySet', 's_filter_attributes', 'propertySetAttribute', 'propertySetAttribute.filterID = propertySet.id');
     $query->innerJoin('propertySet', 's_filter_relations', 'relations', 'relations.groupID = propertySet.id');
     $query->innerJoin('propertyOption', 's_filter_options', 'propertyGroup', 'propertyGroup.id = propertyOption.optionID AND relations.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->where('products.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     $query->orderBy('filterArticles.articleID')->addOrderBy('relations.position')->addOrderBy('propertyGroup.name')->addOrderBy('sortRelevance')->addOrderBy('propertyOption.id');
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_GROUP);
     $properties = [];
     foreach ($data as $productId => $values) {
         $properties[$productId] = $this->propertyHydrator->hydrateValues($values);
     }
     $result = [];
     foreach ($products as $product) {
         if (!isset($properties[$product->getId()])) {
             continue;
         }
         $sets = $properties[$product->getId()];
         $result[$product->getNumber()] = array_shift($sets);
     }
     return $result;
 }
Example #8
0
 /**
  * @return \Doctrine\DBAL\Query\QueryBuilder
  */
 private function getQuery()
 {
     $query = $this->connection->createQueryBuilder();
     $query->select($this->fieldHelper->getMediaFields());
     $query->from('s_media', 'media')->innerJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id');
     $query->where('media.id IN (:ids)');
     return $query;
 }
 /**
  * @inheritdoc
  */
 public function getList(array $ids, Struct\ShopContextInterface $context)
 {
     $query = $this->connection->createQueryBuilder();
     $query->addSelect($this->fieldHelper->getManufacturerFields());
     $query->from('s_articles_supplier', 'manufacturer')->leftJoin('manufacturer', 's_articles_supplier_attributes', 'manufacturerAttribute', 'manufacturerAttribute.supplierID = manufacturer.id');
     $query->where('manufacturer.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     $this->fieldHelper->addManufacturerTranslation($query, $context);
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
     $manufacturers = [];
     foreach ($data as $row) {
         $id = $row['__manufacturer_id'];
         $manufacturers[$id] = $this->manufacturerHydrator->hydrate($row);
     }
     return $manufacturers;
 }
 /**
  * @param \Shopware\Bundle\StoreFrontBundle\Struct\ShopContextInterface $context
  * @return \Doctrine\DBAL\Query\QueryBuilder
  */
 private function getQuery(Struct\ShopContextInterface $context)
 {
     $query = $this->connection->createQueryBuilder();
     $query->addSelect('variant.ordernumber as number')->addSelect($this->fieldHelper->getMediaFields())->addSelect($this->fieldHelper->getImageFields());
     $this->fieldHelper->addImageTranslation($query, $context);
     $query->from('s_articles_img', 'image')->innerJoin('image', 's_media', 'media', 'image.media_id = media.id')->innerJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')->innerJoin('image', 's_articles_img', 'childImage', 'childImage.parent_id = image.id')->innerJoin('image', 's_articles_details', 'variant', 'variant.id = childImage.article_detail_id')->leftJoin('image', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = image.media_id')->leftJoin('image', 's_articles_img_attributes', 'imageAttribute', 'imageAttribute.imageID = image.id');
     return $query;
 }
Example #11
0
 /**
  * @param ShopContextInterface $context
  * @return \Doctrine\DBAL\Query\QueryBuilder
  */
 private function getQuery(ShopContextInterface $context)
 {
     $query = $this->connection->createQueryBuilder();
     $query->addSelect($this->fieldHelper->getPropertyGroupFields())->addSelect($this->fieldHelper->getPropertyOptionFields())->addSelect($this->fieldHelper->getMediaFields());
     $query->from('s_filter_options', 'propertyGroup')->innerJoin('propertyGroup', 's_filter_values', 'propertyOption', 'propertyOption.optionID = propertyGroup.id')->leftJoin('propertyOption', 's_media', 'media', 'propertyOption.media_id = media.id')->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id')->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID');
     $this->fieldHelper->addPropertyGroupTranslation($query, $context);
     $this->fieldHelper->addPropertyOptionTranslation($query, $context);
     $query->where('propertyGroup.id = :id');
     return $query;
 }
Example #12
0
 /**
  * @inheritdoc
  */
 public function getList(array $ids, Struct\ShopContextInterface $context)
 {
     $query = $this->connection->createQueryBuilder();
     $query->select($this->fieldHelper->getCategoryFields())->addSelect($this->fieldHelper->getMediaFields())->addSelect("GROUP_CONCAT(customerGroups.customergroupID) as __category_customer_groups");
     $query->from('s_categories', 'category');
     $query->leftJoin('category', 's_categories_attributes', 'categoryAttribute', 'categoryAttribute.categoryID = category.id')->leftJoin('category', 's_categories_avoid_customergroups', 'customerGroups', 'customerGroups.categoryID = category.id')->leftJoin('category', 's_media', 'media', 'media.id = category.mediaID')->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id');
     $query->addOrderBy('category.position')->addOrderBy('category.id')->addGroupBy('category.id');
     $query->where('category.id IN (:categories)')->andWhere('category.active = 1');
     $query->setParameter(':categories', $ids, Connection::PARAM_INT_ARRAY);
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
     $categories = [];
     foreach ($data as $row) {
         $id = $row['__category_id'];
         $categories[$id] = $this->categoryHydrator->hydrate($row);
     }
     return $categories;
 }
Example #13
0
 /**
  * @param int[] $ids
  * @return \array[]
  */
 private function getShops($ids)
 {
     $query = $this->connection->createQueryBuilder();
     $query->addSelect($this->fieldHelper->getShopFields())->addSelect($this->fieldHelper->getCurrencyFields())->addSelect($this->fieldHelper->getTemplateFields())->addSelect($this->fieldHelper->getLocaleFields())->addSelect($this->fieldHelper->getCustomerGroupFields())->addSelect($this->fieldHelper->getCategoryFields())->addSelect($this->fieldHelper->getMediaFields());
     $query->from('s_core_shops', 'shop')->leftJoin('shop', 's_core_currencies', 'currency', 'currency.id = shop.currency_id')->leftJoin('shop', 's_core_templates', 'template', 'shop.template_id = template.id')->leftJoin('shop', 's_core_locales', 'locale', 'locale.id = shop.locale_id')->leftJoin('shop', 's_core_customergroups', 'customerGroup', 'customerGroup.id = shop.customer_group_id')->leftJoin('customerGroup', 's_core_customergroups_attributes', 'customerGroupAttribute', 'customerGroupAttribute.customerGroupID = customerGroup.id')->leftJoin('shop', 's_categories', 'category', 'category.id = shop.category_id')->leftJoin('category', 's_categories_attributes', 'categoryAttribute', 'categoryAttribute.categoryID = category.id')->leftJoin('category', 's_categories_avoid_customergroups', 'customerGroups', 'customerGroups.categoryID = category.id')->leftJoin('category', 's_media', 'media', 'media.id = category.mediaID')->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id');
     $query->where('shop.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     $data = $query->execute()->fetchAll(\PDO::FETCH_ASSOC);
     $result = [];
     foreach ($data as $row) {
         $result[$row['__shop_id']] = $row;
     }
     return $result;
 }
 /**
  * @param Struct\Customer\Group $customerGroup
  * @param Struct\ShopContextInterface $context
  * @return array|Struct\Product\PriceGroup[]
  */
 public function getPriceGroups(Struct\Customer\Group $customerGroup, Struct\ShopContextInterface $context)
 {
     $query = $this->connection->createQueryBuilder();
     $query->addSelect('priceGroupDiscount.groupID')->addSelect($this->fieldHelper->getPriceGroupDiscountFields())->addSelect($this->fieldHelper->getPriceGroupFields());
     $query->from('s_core_pricegroups_discounts', 'priceGroupDiscount')->innerJoin('priceGroupDiscount', 's_core_pricegroups', 'priceGroup', 'priceGroup.id = priceGroupDiscount.groupID');
     $query->andWhere('priceGroupDiscount.customergroupID = :customerGroup');
     $query->groupBy('priceGroupDiscount.id');
     $query->orderBy('priceGroupDiscount.groupID')->addOrderBy('priceGroupDiscount.discountstart');
     $query->setParameter(':customerGroup', $customerGroup->getId());
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_GROUP);
     $priceGroups = [];
     foreach ($data as $row) {
         $priceGroup = $this->priceHydrator->hydratePriceGroup($row);
         foreach ($priceGroup->getDiscounts() as $discount) {
             $discount->setCustomerGroup($customerGroup);
         }
         $priceGroups[$priceGroup->getId()] = $priceGroup;
     }
     return $priceGroups;
 }
 /**
  * @inheritdoc
  */
 public function getList($products, Struct\Customer\Group $customerGroup)
 {
     $ids = [];
     foreach ($products as $product) {
         $ids[] = $product->getVariantId();
     }
     $ids = array_unique($ids);
     $query = $this->connection->createQueryBuilder();
     $query->select($this->fieldHelper->getPriceFields());
     $query->addSelect('variants.ordernumber as number');
     $query->from('s_articles_prices', 'price')->innerJoin('price', 's_articles_details', 'variants', 'variants.id = price.articledetailsID')->leftJoin('price', 's_articles_prices_attributes', 'priceAttribute', 'priceAttribute.priceID = price.id');
     $query->where('price.articledetailsID IN (:products)')->andWhere('price.pricegroup = :customerGroup')->setParameter(':products', $ids, Connection::PARAM_INT_ARRAY)->setParameter(':customerGroup', $customerGroup->getKey());
     $query->orderBy('price.articledetailsID', 'ASC')->addOrderBy('price.from', 'ASC');
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
     $prices = [];
     foreach ($data as $row) {
         $product = $row['number'];
         $prices[$product][] = $this->priceHydrator->hydratePriceRule($row);
     }
     return $prices;
 }
Example #16
0
 /**
  * @inheritdoc
  */
 public function getStates(array $ids, Struct\ShopContextInterface $context)
 {
     $query = $this->connection->createQueryBuilder();
     $query->select($this->fieldHelper->getStateFields());
     $query->from('s_core_countries_states', 'countryState')->leftJoin('countryState', 's_core_countries_states_attributes', 'countryStateAttribute', 'countryStateAttribute.stateID = countryState.id');
     $this->fieldHelper->addCountryStateTranslation($query, $context);
     $query->where('countryState.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
     $states = [];
     foreach ($data as $row) {
         $states[] = $this->countryHydrator->hydrateState($row);
     }
     return $states;
 }
Example #17
0
 /**
  * @param Struct\Customer\Group $customerGroup
  * @param Struct\Country\Area $area
  * @param Struct\Country $country
  * @param Struct\Country\State $state
  * @return \Doctrine\DBAL\Query\QueryBuilder
  */
 private function getAreaQuery(Struct\Customer\Group $customerGroup = null, Struct\Country\Area $area = null, Struct\Country $country = null, Struct\Country\State $state = null)
 {
     $query = $this->connection->createQueryBuilder();
     $query->select($this->fieldHelper->getTaxRuleFields());
     $query->from('s_core_tax_rules', 'taxRule');
     $areaId = $area ? $area->getId() : null;
     $countryId = $country ? $country->getId() : null;
     $stateId = $state ? $state->getId() : null;
     $query->andWhere('(taxRule.areaID = :area OR taxRule.areaID IS NULL)')->setParameter(':area', $areaId);
     $query->andWhere('(taxRule.countryID = :country OR taxRule.countryID IS NULL)')->setParameter(':country', $countryId);
     $query->andWhere('(taxRule.stateID = :state OR taxRule.stateID IS NULL)')->setParameter(':state', $stateId);
     $query->andWhere('(taxRule.customer_groupID = :customerGroup OR taxRule.customer_groupID IS NULL)')->setParameter(':customerGroup', $customerGroup->getId());
     $query->andWhere('taxRule.groupID = :taxId')->andWhere('taxRule.active = 1');
     $query->orderBy('taxRule.customer_groupID', 'DESC')->addOrderBy('taxRule.areaID', 'DESC')->addOrderBy('taxRule.countryID', 'DESC')->addOrderBy('taxRule.stateID', 'DESC');
     $query->setFirstResult(0)->setMaxResults(1);
     return $query;
 }
 /**
  * @param array $numbers
  * @param Struct\ShopContextInterface $context
  * @return \Doctrine\DBAL\Query\QueryBuilder
  */
 protected function getQuery(array $numbers, Struct\ShopContextInterface $context)
 {
     $esdQuery = $this->getEsdQuery();
     $customerGroupQuery = $this->getCustomerGroupQuery();
     $availableVariantQuery = $this->getHasAvailableVariantQuery();
     $fallbackPriceQuery = $this->getPriceCountQuery(':fallback');
     $query = $this->connection->createQueryBuilder();
     $query->select($this->fieldHelper->getArticleFields())->addSelect($this->fieldHelper->getTopSellerFields())->addSelect($this->fieldHelper->getVariantFields())->addSelect($this->fieldHelper->getUnitFields())->addSelect($this->fieldHelper->getTaxFields())->addSelect($this->fieldHelper->getPriceGroupFields())->addSelect($this->fieldHelper->getManufacturerFields())->addSelect($this->fieldHelper->getEsdFields())->addSelect('(' . $esdQuery->getSQL() . ') as __product_has_esd')->addSelect('(' . $customerGroupQuery->getSQL() . ') as __product_blocked_customer_groups')->addSelect('(' . $availableVariantQuery->getSQL() . ') as __product_has_available_variants')->addSelect('(' . $fallbackPriceQuery->getSQL() . ') as __product_fallback_price_count');
     $query->setParameter(':fallback', $context->getFallbackCustomerGroup()->getKey());
     if ($context->getCurrentCustomerGroup()->getId() !== $context->getFallbackCustomerGroup()->getId()) {
         $customerPriceQuery = $this->getPriceCountQuery(':current');
         $query->addSelect('(' . $customerPriceQuery->getSQL() . ') as __product_custom_price_count');
         $query->setParameter(':current', $context->getCurrentCustomerGroup()->getKey());
     }
     $query->from('s_articles_details', 'variant')->innerJoin('variant', 's_articles', 'product', 'product.id = variant.articleID')->innerJoin('product', 's_core_tax', 'tax', 'tax.id = product.taxID')->leftJoin('variant', 's_core_units', 'unit', 'unit.id = variant.unitID')->leftJoin('product', 's_articles_supplier', 'manufacturer', 'manufacturer.id = product.supplierID')->leftJoin('product', 's_core_pricegroups', 'priceGroup', 'priceGroup.id = product.pricegroupID')->leftJoin('variant', 's_articles_attributes', 'productAttribute', 'productAttribute.articledetailsID = variant.id')->leftJoin('product', 's_articles_supplier_attributes', 'manufacturerAttribute', 'manufacturerAttribute.supplierID = product.supplierID')->leftJoin('product', 's_articles_top_seller_ro', 'topSeller', 'topSeller.article_id = product.id')->leftJoin('variant', 's_articles_esd', 'esd', 'esd.articledetailsID = variant.id')->leftJoin('esd', 's_articles_esd_attributes', 'esdAttribute', 'esdAttribute.esdID = esd.id')->where('variant.ordernumber IN (:numbers)')->andWhere('variant.active = 1')->andWhere('product.active = 1')->setParameter(':numbers', $numbers, Connection::PARAM_STR_ARRAY);
     $this->fieldHelper->addProductTranslation($query, $context);
     $this->fieldHelper->addVariantTranslation($query, $context);
     $this->fieldHelper->addManufacturerTranslation($query, $context);
     $this->fieldHelper->addUnitTranslation($query, $context);
     return $query;
 }
Example #19
0
 /**
  * @param ListProduct[] $products
  * @param ShopContextInterface $context
  * @return \array[]
  */
 private function getProperties($products, ShopContextInterface $context)
 {
     $ids = array_map(function (ListProduct $product) {
         return $product->getId();
     }, $products);
     $query = $this->connection->createQueryBuilder();
     $query->addSelect('filterArticles.articleID as productId')->addSelect($this->fieldHelper->getPropertyOptionFields())->addSelect($this->fieldHelper->getMediaFields())->from('s_filter_articles', 'filterArticles')->innerJoin('filterArticles', 's_filter_values', 'propertyOption', 'propertyOption.id = filterArticles.valueID')->leftJoin('propertyOption', 's_media', 'media', 'propertyOption.media_id = media.id')->leftJoin('media', 's_media_attributes', 'mediaAttribute', 'mediaAttribute.mediaID = media.id')->leftJoin('media', 's_media_album_settings', 'mediaSettings', 'mediaSettings.albumID = media.albumID')->where('filterArticles.articleID IN (:ids)')->addOrderBy('filterArticles.articleID')->addOrderBy('propertyOption.value')->addOrderBy('propertyOption.id')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     $this->fieldHelper->addPropertyOptionTranslation($query, $context);
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_GROUP);
     $properties = [];
     $hydrator = $this->propertyHydrator;
     foreach ($data as $productId => $values) {
         $options = array_map(function ($row) use($hydrator) {
             return $hydrator->hydrateOption($row);
         }, $values);
         $properties[$productId] = $options;
     }
     return $properties;
 }
 /**
  * @inheritdoc
  */
 public function getList($products, Struct\ShopContextInterface $context, Struct\Customer\Group $customerGroup)
 {
     /**
      * Contains the cheapest price logic which product price should be selected.
      */
     $ids = $this->getCheapestPriceIds($products, $customerGroup);
     $query = $this->connection->createQueryBuilder();
     $query->select($this->fieldHelper->getPriceFields())->addSelect($this->fieldHelper->getUnitFields());
     $query->from('s_articles_prices', 'price')->innerJoin('price', 's_articles_details', 'variant', 'variant.id = price.articledetailsID')->innerJoin('variant', 's_articles', 'product', 'product.id = variant.articleID')->leftJoin('variant', 's_core_units', 'unit', 'unit.id = variant.unitID')->leftJoin('price', 's_articles_prices_attributes', 'priceAttribute', 'priceAttribute.priceID = price.id');
     $this->fieldHelper->addUnitTranslation($query, $context);
     $this->fieldHelper->addVariantTranslation($query, $context);
     $query->andWhere('price.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_ASSOC);
     $prices = [];
     foreach ($data as $row) {
         $product = $row['__price_articleID'];
         $prices[$product] = $this->priceHydrator->hydrateCheapestPrice($row);
     }
     return $prices;
 }
 /**
  * @inheritdoc
  */
 public function getList($products, Struct\ShopContextInterface $context)
 {
     if (empty($products)) {
         return [];
     }
     $ids = [];
     foreach ($products as $product) {
         $ids[] = $product->getVariantId();
     }
     $ids = array_unique($ids);
     $query = $this->getQuery()->select('variants.ordernumber as number')->addSelect($this->fieldHelper->getConfiguratorGroupFields())->addSelect($this->fieldHelper->getConfiguratorOptionFields());
     $this->fieldHelper->addConfiguratorTranslation($query, $context);
     $query->where('relations.article_id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     $query->addOrderBy('configuratorGroup.id');
     /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
     $statement = $query->execute();
     $data = $statement->fetchAll(\PDO::FETCH_GROUP);
     $result = [];
     foreach ($data as $key => $groups) {
         $result[$key] = $this->configuratorHydrator->hydrateGroups($groups);
     }
     return $result;
 }
Example #22
0
 /**
  * @param int[] $ids
  * @return \array[]
  */
 private function getCurrencies($ids)
 {
     $query = $this->connection->createQueryBuilder();
     $query->addSelect($this->fieldHelper->getCurrencyFields())->from('s_core_currencies', 'currency')->where('currency.id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY);
     return $query->execute()->fetchAll(\PDO::FETCH_ASSOC);
 }