/**
* @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;
}
/**
* Pre selection of the cheapest prices ids.
*
* @param Struct\BaseProduct[] $products
* @param Struct\Customer\Group $customerGroup
* @return array
*/
private function getCheapestPriceIds($products, Struct\Customer\Group $customerGroup)
{
$ids = [];
foreach ($products as $product) {
$ids[] = $product->getId();
}
$ids = array_unique($ids);
$subQuery = $this->connection->createQueryBuilder();
$subQuery->select('prices.id')->from('s_articles_prices', 'prices');
/**
* joins the product variants for the min purchase calculation.
* The cheapest price is defined by prices.price * variant.minpurchase (the real basket price)
*/
$subQuery->innerJoin('prices', 's_articles_details', 'variant', 'variant.id = prices.articledetailsID');
/**
* Joins the products for the closeout validation.
* Required to select only product prices which product variant can be added to the basket and purchased
*/
$subQuery->innerJoin('variant', 's_articles', 'product', 'product.id = variant.articleID');
$graduation = 'prices.from = 1';
if ($this->config->get('useLastGraduationForCheapestPrice')) {
$graduation = "prices.to = 'beliebig'";
}
$subQuery->where('prices.pricegroup = :customerGroup')->andWhere($graduation)->andWhere('variant.active = 1')->andWhere('prices.articleID = outerPrices.articleID');
/**
* This part of the query handles the closeout products.
*
* The `laststock` column contains "1" if the product is a closeout product.
* In the case that the product contains the closeout flag,
* the stock and minpurchase are used as they defined in the database
*
* In the case that the product isn't a closeout product,
* the stock and minpurchase are set to 0
*/
$subQuery->andWhere('(product.laststock * variant.instock) >= (product.laststock * variant.minpurchase)');
$subQuery->setMaxResults(1);
if ($this->config->get('calculateCheapestPriceWithMinPurchase')) {
/**
* Sorting by the cheapest available price
*/
$subQuery->orderBy('(prices.price * variant.minpurchase)');
} else {
/**
* Sorting by the cheapest unit price
*/
$subQuery->orderBy('prices.price');
}
/**
* Creates an outer query which allows to
* select multiple cheapest product prices.
*/
$query = $this->connection->createQueryBuilder();
$query->setParameter(':customerGroup', $customerGroup->getKey());
$query->select('(' . $subQuery->getSQL() . ') as priceId')->from('s_articles_prices', 'outerPrices')->where('outerPrices.articleID IN (:products)')->setParameter(':products', $ids, Connection::PARAM_INT_ARRAY)->groupBy('outerPrices.articleID')->having('priceId IS NOT NULL');
$statement = $query->execute();
return $statement->fetchAll(\PDO::FETCH_COLUMN);
}
/**
* @param Models\Customer\Group $group
* @return Struct\Customer\Group
*/
public function convertCustomerGroup(Models\Customer\Group $group)
{
$customerGroup = new Struct\Customer\Group();
$customerGroup->setKey($group->getKey());
$customerGroup->setUseDiscount($group->getMode());
$customerGroup->setId($group->getId());
$customerGroup->setPercentageDiscount($group->getDiscount());
$customerGroup->setDisplayGrossPrices($group->getTax());
$customerGroup->setMinimumOrderValue($group->getMinimumOrder());
$customerGroup->setSurcharge($group->getMinimumOrderSurcharge());
return $customerGroup;
}
/**
* @param Struct\Customer\Group $customerGroup
* @return NotFilter
*/
protected function getCustomerGroupFilter(Struct\Customer\Group $customerGroup)
{
return new NotFilter(new TermsFilter('blockedCustomerGroupIds', [$customerGroup->getId()]));
}
/**
* @param Group $customerGroup used for the price definition
* @param string $number
* @param array $data Contains nested configurator group > option array.
* @return array
*/
public function getConfigurator(Group $customerGroup, $number, array $data = array())
{
if (empty($data)) {
$data = array('Farbe' => array('rot', 'gelb', 'blau'));
}
$groups = $this->insertConfiguratorData($data);
$configurator = $this->createConfiguratorSet($groups);
$variants = array_merge(array('prices' => $this->getGraduatedPrices($customerGroup->getKey())), $this->getUnitData());
$variants = $this->generateVariants($configurator['groups'], $number, $variants);
return array('configuratorSet' => $configurator, 'variants' => $variants);
}
/**
* @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 $data
* @return Struct\Customer\Group
*/
public function hydrate(array $data)
{
$customerGroup = new Struct\Customer\Group();
$customerGroup->setId((int) $data['__customerGroup_id']);
$customerGroup->setName($data['__customerGroup_description']);
$customerGroup->setDisplayGrossPrices((bool) $data['__customerGroup_tax']);
$customerGroup->setKey($data['__customerGroup_groupkey']);
$customerGroup->setMinimumOrderValue((double) $data['__customerGroup_minimumorder']);
$customerGroup->setPercentageDiscount((double) $data['__customerGroup_discount']);
$customerGroup->setSurcharge((double) $data['__customerGroup_minimumordersurcharge']);
$customerGroup->setUseDiscount((bool) $data['__customerGroup_mode']);
if (!empty($data['__customerGroupAttribute_id'])) {
$attribute = $this->attributeHydrator->hydrate($this->extractFields('__customerGroupAttribute_', $data));
$customerGroup->addAttribute('core', $attribute);
}
return $customerGroup;
}
