/** * @param $rewrittenUrl * * @return array|mixed|\Propel\Runtime\Collection\ObjectCollection */ public function getResolverSearch($rewrittenUrl) { $redirectedJoin = new Join(); $redirectedJoin->addExplicitCondition(RewritingUrlTableMap::TABLE_NAME, 'REDIRECTED', 'ru', RewritingUrlTableMap::TABLE_NAME, 'ID', 'is_redirected'); $redirectedJoin->setJoinType(Criteria::LEFT_JOIN); $search = RewritingArgumentQuery::create()->joinRewritingUrl('ru', Criteria::RIGHT_JOIN)->addJoinObject($redirectedJoin)->where('`ru`.URL = ?', $rewrittenUrl, \PDO::PARAM_STR)->withColumn('`ru`.URL', 'ru_url')->withColumn('`ru`.VIEW', 'ru_view')->withColumn('`ru`.VIEW_LOCALE', 'ru_locale')->withColumn('`ru`.VIEW_ID', 'ru_viewId')->withColumn('`is_redirected`.URL', 'ru_redirected_to_url')->find(); return $search; }
public function buildModelCriteria() { $search = AttributeAvQuery::create(); /* manage translations */ $this->configureI18nProcessing($search); $id = $this->getId(); if (null !== $id) { $search->filterById($id, Criteria::IN); } $exclude = $this->getExclude(); if (null !== $exclude) { $search->filterById($exclude, Criteria::NOT_IN); } $attribute = $this->getAttribute(); if (null !== $attribute) { $search->filterByAttributeId($attribute, Criteria::IN); } $product = $this->getProduct(); if (null !== $product) { // Return only Attributes Av that are part on a product's combination /* The request is: select * from attribute_av aav left join attribute_combination ac on ac.attribute_av_id = aav.id left join product_sale_elements pse on pse.id = ac.product_sale_elements_id where aav.attribute_id=3 and pse.product_id = 279 group by aav.id */ $pseJoin = new Join(); $pseJoin->addCondition(AttributeCombinationTableMap::PRODUCT_SALE_ELEMENTS_ID, ProductSaleElementsTableMap::ID, Criteria::EQUAL); $pseJoin->setJoinType(Criteria::LEFT_JOIN); $search->leftJoinAttributeCombination('attribute_combination')->groupById()->addJoinObject($pseJoin)->where(ProductSaleElementsTableMap::PRODUCT_ID . "=?", $product, \PDO::PARAM_INT); } $orders = $this->getOrder(); foreach ($orders as $order) { switch ($order) { case 'id': $search->orderById(Criteria::ASC); break; case 'id_reverse': $search->orderById(Criteria::DESC); break; case "alpha": $search->addAscendingOrderByColumn('i18n_TITLE'); break; case "alpha_reverse": $search->addDescendingOrderByColumn('i18n_TITLE'); break; case "manual": $search->orderByPosition(Criteria::ASC); break; case "manual_reverse": $search->orderByPosition(Criteria::DESC); break; } } return $search; }
/** * Join products and their URLs * * @param Criteria $query */ protected function addJoinProduct(Criteria &$query) { // Join RewritingURL with Product to have only visible products $join = new Join(); $join->addExplicitCondition(RewritingUrlTableMap::TABLE_NAME, 'VIEW_ID', null, ProductTableMap::TABLE_NAME, 'ID', null); $join->setJoinType(Criteria::INNER_JOIN); $query->addJoinObject($join, 'productJoin'); // Get only visible products $query->addJoinCondition('productJoin', ProductTableMap::VISIBLE, 1, Criteria::EQUAL, \PDO::PARAM_INT); }
public function buildModelCriteria() { /** @var AttributeQuery $query */ $query = parent::buildModelCriteria(); if (null !== ($attributeCategory = $this->getCategory())) { $categoryJoin = new Join(); $categoryJoin->addExplicitCondition(TemplateTableMap::TABLE_NAME, 'ID', null, CategoryTableMap::TABLE_NAME, 'DEFAULT_TEMPLATE_ID', null); $categoryJoin->setJoinType(Criteria::JOIN); $query->useAttributeTemplateQuery()->useTemplateQuery()->addJoinObject($categoryJoin, 'category_join')->addJoinCondition('category_join', '`category`.`id` IN (' . implode(',', $attributeCategory) . ')')->endUse()->endUse(); } return $query; }
public function buildModelCriteria() { $query = CategoryQuery::create(); if ($this->getCategoryId()) { $query->filterById($this->getCategoryId()); } $this->configureI18nProcessing($query, array('TITLE')); $taxonomyJoin = new Join(); $taxonomyJoin->addExplicitCondition(CategoryTableMap::TABLE_NAME, 'ID', null, GoogleshoppingTaxonomyTableMap::TABLE_NAME, 'THELIA_CATEGORY_ID', 'taxonomy'); $taxonomyJoin->setJoinType(Criteria::JOIN); $query->addJoinObject($taxonomyJoin, 'taxonomy_join')->addJoinCondition('taxonomy_join', 'taxonomy.lang_id = ' . $this->getLangId())->withColumn('taxonomy.google_category', 'google_category')->addAscendingOrderByColumn('i18n_TITLE'); return $query; }
/** * @param \DateTime $startDate * @param \DateTime $endDate * @param $includeShipping * * @return int */ public static function getSaleStats(\DateTime $startDate, \DateTime $endDate, $includeShipping) { $orderTaxJoin = new Join(); $orderTaxJoin->addExplicitCondition(OrderProductTableMap::TABLE_NAME, 'ID', null, OrderProductTaxTableMap::TABLE_NAME, 'ORDER_PRODUCT_ID', null); $orderTaxJoin->setJoinType(Criteria::INNER_JOIN); $query = self::create('o')->filterByCreatedAt(sprintf("%s 00:00:00", $startDate->format('Y-m-d')), Criteria::GREATER_EQUAL)->filterByCreatedAt(sprintf("%s 23:59:59", $endDate->format('Y-m-d')), Criteria::LESS_EQUAL)->filterByStatusId([2, 3, 4], Criteria::IN)->innerJoinOrderProduct()->addJoinObject($orderTaxJoin); if ($includeShipping) { $query->withColumn("SUM((`order_product`.QUANTITY * IF(`order_product`.WAS_IN_PROMO,`order_product`.PROMO_PRICE+`order_product_tax`.PROMO_AMOUNT,`order_product`.PRICE+`order_product_tax`.AMOUNT)) + `order`.postage )", 'TOTAL'); } else { $query->withColumn("SUM((`order_product`.QUANTITY * IF(`order_product`.WAS_IN_PROMO,`order_product`.PROMO_PRICE+`order_product_tax`.PROMO_AMOUNT,`order_product`.PRICE+`order_product_tax`.AMOUNT)))", 'TOTAL'); } $query->select('TOTAL'); $amount = $query->findOne(); return null === $amount ? 0 : round($amount, 2); }
public function buildModelCriteria() { $query = parent::buildModelCriteria(); $productJoin = new Join(); $productJoin->addExplicitCondition(BrandTableMap::TABLE_NAME, 'ID', null, ProductTableMap::TABLE_NAME, 'BRAND_ID', null); $productJoin->setJoinType(Criteria::JOIN); $query->addJoinObject($productJoin); if (null !== ($categories = $this->getCategory())) { $productCategoryJoin = new Join(); $productCategoryJoin->addExplicitCondition(ProductTableMap::TABLE_NAME, 'ID', null, ProductCategoryTableMap::TABLE_NAME, 'PRODUCT_ID', null); $productCategoryJoin->setJoinType(Criteria::JOIN); $query->addJoinObject($productCategoryJoin, 'product_category_join')->addJoinCondition('product_category_join', ProductCategoryTableMap::CATEGORY_ID . ' IN (' . implode(',', $categories) . ')'); } $query->groupById(); return $query; }
public function buildModelCriteria() { $query = parent::buildModelCriteria(); if (false === $this->getReturnEmpty()) { $featureAvProductJoin = new Join(); $featureAvProductJoin->addExplicitCondition(FeatureAvTableMap::TABLE_NAME, 'ID', null, FeatureProductTableMap::TABLE_NAME, 'FEATURE_AV_ID', null); $featureAvProductJoin->setJoinType(Criteria::JOIN); $query->addJoinObject($featureAvProductJoin, 'feature_av_product_join'); if (null !== ($categories = $this->getCategory())) { $featureAvProductCategoryJoin = new Join(); $featureAvProductCategoryJoin->addExplicitCondition(FeatureProductTableMap::TABLE_NAME, 'PRODUCT_ID', null, ProductCategoryTableMap::TABLE_NAME, 'PRODUCT_ID', null); $featureAvProductCategoryJoin->setJoinType(Criteria::JOIN); $query->addJoinObject($featureAvProductCategoryJoin, 'feature_av_product_category_join')->addJoinCondition('feature_av_product_category_join', ProductCategoryTableMap::CATEGORY_ID . ' IN (' . implode(',', $categories) . ')'); } } $query->groupById(); return $query; }
/** * Join products and their URLs * * @param Criteria $query */ protected function addJoinProductI18n(Criteria &$query) { // Join RewritingURL with Product to have only visible products $join = new Join(); $join->addExplicitCondition(RewritingUrlTableMap::TABLE_NAME, 'VIEW_ID', null, ProductTableMap::TABLE_NAME, 'ID', null); $join->setJoinType(Criteria::INNER_JOIN); $query->addJoinObject($join, 'productJoin'); $query->addJoinCondition('productJoin', ProductTableMap::VISIBLE, 1, Criteria::EQUAL, \PDO::PARAM_INT); // Join RewritingURL with ProductI18n to have product title for it's image $joinI18n = new Join(); $joinI18n->addExplicitCondition(RewritingUrlTableMap::TABLE_NAME, 'VIEW_ID', null, ProductI18nTableMap::TABLE_NAME, 'ID', null); $joinI18n->addExplicitCondition(RewritingUrlTableMap::TABLE_NAME, 'VIEW_LOCALE', null, ProductI18nTableMap::TABLE_NAME, 'LOCALE', null); $joinI18n->setJoinType(Criteria::INNER_JOIN); $query->addJoinObject($joinI18n); // Join RewritingURL with ProductImage to have image file $joinImage = new Join(); $joinImage->addExplicitCondition(RewritingUrlTableMap::TABLE_NAME, 'VIEW_ID', null, ProductImageTableMap::TABLE_NAME, 'PRODUCT_ID', null); $joinImage->setJoinType(Criteria::INNER_JOIN); $query->addJoinObject($joinImage, 'productImageJoin'); $query->addJoinCondition('productImageJoin', ProductImageTableMap::VISIBLE, 1, Criteria::EQUAL, \PDO::PARAM_INT); }
public function buildModelCriteria() { $query = parent::buildModelCriteria(); if (false === $this->getReturnEmpty()) { $attributeAvCombinationJoin = new Join(); $attributeAvCombinationJoin->addExplicitCondition(AttributeAvTableMap::TABLE_NAME, 'ID', null, AttributeCombinationTableMap::TABLE_NAME, 'ATTRIBUTE_AV_ID', null); $attributeAvCombinationJoin->setJoinType(Criteria::JOIN); $query->addJoinObject($attributeAvCombinationJoin, 'attribute_av_combination'); if (null !== ($categories = $this->getCategory())) { $pseJoin = new Join(); $pseJoin->addExplicitCondition(AttributeCombinationTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', null, ProductSaleElementsTableMap::TABLE_NAME, 'ID', null); $pseJoin->setJoinType(Criteria::JOIN); $query->addJoinObject($pseJoin, 'pse_join'); $attributeAvProductCategorieJoin = new Join(); $attributeAvProductCategorieJoin->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'PRODUCT_ID', null, ProductCategoryTableMap::TABLE_NAME, 'PRODUCT_ID', null); $attributeAvProductCategorieJoin->setJoinType(Criteria::JOIN); $query->addJoinObject($attributeAvProductCategorieJoin, 'attribute_av_product_category_join')->addJoinCondition('attribute_av_product_category_join', ProductCategoryTableMap::CATEGORY_ID . ' IN (' . implode(',', $categories) . ')'); } } $query->groupById(); return $query; }
public function buildModelCriteria() { $search = TaxRuleCountryQuery::create(); $ask = $this->getAsk(); $country = $this->getCountry(); $taxRule = $this->getTaxRule(); if ($ask === 'countries') { $this->taxCountForOriginCountry = TaxRuleCountryQuery::create()->filterByCountryId($country)->count(); if ($this->taxCountForOriginCountry > 0) { $search->groupByCountryId(); $originalCountryJoin = new Join(); $originalCountryJoin->addExplicitCondition(TaxRuleCountryTableMap::TABLE_NAME, 'TAX_RULE_ID', null, TaxRuleCountryTableMap::TABLE_NAME, 'TAX_RULE_ID', 'origin'); $originalCountryJoin->addExplicitCondition(TaxRuleCountryTableMap::TABLE_NAME, 'TAX_ID', null, TaxRuleCountryTableMap::TABLE_NAME, 'TAX_ID', 'origin'); $originalCountryJoin->addExplicitCondition(TaxRuleCountryTableMap::TABLE_NAME, 'POSITION', null, TaxRuleCountryTableMap::TABLE_NAME, 'POSITION', 'origin'); $originalCountryJoin->addExplicitCondition(TaxRuleCountryTableMap::TABLE_NAME, 'COUNTRY_ID', null, TaxRuleCountryTableMap::TABLE_NAME, 'COUNTRY_ID', 'origin', Criteria::NOT_EQUAL); $originalCountryJoin->setJoinType(Criteria::LEFT_JOIN); $search->addJoinObject($originalCountryJoin, 's_to_o'); $search->where('`origin`.`COUNTRY_ID`' . Criteria::EQUAL . '?', $country, \PDO::PARAM_INT); //$search->having('COUNT(*)=?', $this->taxCountForOriginCountry, \PDO::PARAM_INT); $search->filterByTaxRuleId($taxRule); /* manage tax translation */ $this->configureI18nProcessing($search, array('TITLE', 'CHAPO', 'DESCRIPTION', 'POSTSCRIPTUM'), CountryTableMap::TABLE_NAME, 'COUNTRY_ID'); $search->addAscendingOrderByColumn('`' . CountryTableMap::TABLE_NAME . '_i18n_TITLE`'); } else { $search = CountryQuery::create()->joinTaxRuleCountry('trc', Criteria::LEFT_JOIN); /* manage tax translation */ $this->configureI18nProcessing($search); $search->where('ISNULL(`trc`.`COUNTRY_ID`)'); $search->addAscendingOrderByColumn('i18n_TITLE'); } } elseif ($ask === 'taxes') { $search->filterByCountryId($country); /* manage tax translation */ $this->configureI18nProcessing($search, array('TITLE', 'DESCRIPTION'), TaxTableMap::TABLE_NAME, 'TAX_ID'); $search->filterByTaxRuleId($taxRule); $search->orderByPosition(Criteria::ASC); } return $search; }
/** * @param \DateTime $startDate * @param \DateTime $endDate * @param $includeShipping * * @return int */ public static function getSaleStats(\DateTime $startDate, \DateTime $endDate, $includeShipping) { $orderTaxJoin = new Join(); $orderTaxJoin->addExplicitCondition(OrderProductTableMap::TABLE_NAME, 'ID', null, OrderProductTaxTableMap::TABLE_NAME, 'ORDER_PRODUCT_ID', null); $orderTaxJoin->setJoinType(Criteria::LEFT_JOIN); $query = self::baseSaleStats($startDate, $endDate, 'o')->innerJoinOrderProduct()->addJoinObject($orderTaxJoin)->withColumn("SUM((`order_product`.QUANTITY * IF(`order_product`.WAS_IN_PROMO,`order_product`.PROMO_PRICE,`order_product`.PRICE)))", 'TOTAL')->withColumn("SUM((`order_product`.QUANTITY * IF(`order_product`.WAS_IN_PROMO,`order_product_tax`.PROMO_AMOUNT,`order_product_tax`.AMOUNT)))", 'TAX')->select(['TOTAL', 'TAX']); $arrayAmount = $query->findOne(); $amount = $arrayAmount['TOTAL'] + $arrayAmount['TAX']; if (null === $amount) { $amount = 0; } $discountQuery = self::baseSaleStats($startDate, $endDate)->withColumn("SUM(`order`.discount)", 'DISCOUNT')->select('DISCOUNT'); $discount = $discountQuery->findOne(); if (null === $discount) { $discount = 0; } $amount = $amount - $discount; if ($includeShipping) { $query = self::baseSaleStats($startDate, $endDate)->withColumn("SUM(`order`.postage)", 'POSTAGE')->select('POSTAGE'); $amount += $query->findOne(); } return null === $amount ? 0 : round($amount, 2); }
/** * Add a join with multiple conditions * @deprecated use Join::setJoinCondition($criterion) instead * * @see http://propel.phpdb.org/trac/ticket/167, http://propel.phpdb.org/trac/ticket/606 * * Example usage: * $c->addMultipleJoin(array( * array(LeftTableMap::LEFT_COLUMN, RightTableMap::RIGHT_COLUMN), // if no third argument, defaults to Criteria::EQUAL * array(FoldersTableMap::alias( 'fo', FoldersTableMap::LFT ), FoldersTableMap::alias( 'parent', FoldersTableMap::RGT ), Criteria::LESS_EQUAL ) * ), * Criteria::LEFT_JOIN * ); * * @see addJoin() * @param array $conditions An array of conditions, each condition being an array (left, right, operator) * @param string $joinType A String with the join operator. Defaults to an implicit join. * * @return $this|Criteria A modified Criteria object. */ public function addMultipleJoin($conditions, $joinType = null) { $join = new Join(); $join->setIdentifierQuoting($this->isIdentifierQuotingEnabled()); $joinCondition = null; foreach ($conditions as $condition) { $left = $condition[0]; $right = $condition[1]; $pos = strrpos($left, '.'); if ($pos) { $leftTableAlias = substr($left, 0, $pos); $leftColumnName = substr($left, $pos + 1); list($leftTableName, $leftTableAlias) = $this->getTableNameAndAlias($leftTableAlias); } else { list($leftTableName, $leftTableAlias) = [null, null]; $leftColumnName = $left; } $pos = strrpos($right, '.'); if ($pos) { $rightTableAlias = substr($right, 0, $pos); $rightColumnName = substr($right, $pos + 1); list($rightTableName, $rightTableAlias) = $this->getTableNameAndAlias($rightTableAlias); } else { list($rightTableName, $rightTableAlias) = [null, null]; $rightColumnName = $right; } if (!$join->getRightTableName()) { $join->setRightTableName($rightTableName); } if (!$join->getRightTableAlias()) { $join->setRightTableAlias($rightTableAlias); } $conditionClause = $leftTableAlias ? $leftTableAlias . '.' : ($leftTableName ? $leftTableName . '.' : ''); $conditionClause .= $leftColumnName; $conditionClause .= isset($condition[2]) ? $condition[2] : Join::EQUAL; $conditionClause .= $rightTableAlias ? $rightTableAlias . '.' : ($rightTableName ? $rightTableName . '.' : ''); $conditionClause .= $rightColumnName; $criterion = $this->getNewCriterion($leftTableName . '.' . $leftColumnName, $conditionClause, Criteria::CUSTOM); if (null === $joinCondition) { $joinCondition = $criterion; } else { $joinCondition = $joinCondition->addAnd($criterion); } } $join->setJoinType($joinType); $join->setJoinCondition($joinCondition); return $this->addJoinObject($join); }
public function buildModelCriteria() { Tlog::getInstance()->debug("-- Starting new product build criteria"); $currencyId = $this->getCurrency(); if (null !== $currencyId) { $currency = CurrencyQuery::create()->findOneById($currencyId); if (null === $currency) { throw new \InvalidArgumentException('Cannot found currency id: `' . $currency . '` in product_sale_elements loop'); } } else { $currency = $this->request->getSession()->getCurrency(); } $defaultCurrency = CurrencyQuery::create()->findOneByByDefault(1); $defaultCurrencySuffix = '_default_currency'; $priceToCompareAsSQL = ''; $isPSELeftJoinList = []; $isProductPriceFirstLeftJoin = []; $search = ProductQuery::create(); $complex = $this->getComplex(); if (!$complex) { $search->innerJoinProductSaleElements('pse'); $search->addJoinCondition('pse', '`pse`.IS_DEFAULT=1'); $search->innerJoinProductSaleElements('pse_count'); $priceJoin = new Join(); $priceJoin->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', 'pse', ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'price'); $priceJoin->setJoinType(Criteria::LEFT_JOIN); $search->addJoinObject($priceJoin, 'price_join')->addJoinCondition('price_join', '`price`.`currency_id` = ?', $currency->getId(), null, \PDO::PARAM_INT); if ($defaultCurrency->getId() != $currency->getId()) { $priceJoinDefaultCurrency = new Join(); $priceJoinDefaultCurrency->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', 'pse', ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'price' . $defaultCurrencySuffix); $priceJoinDefaultCurrency->setJoinType(Criteria::LEFT_JOIN); $search->addJoinObject($priceJoinDefaultCurrency, 'price_join' . $defaultCurrencySuffix)->addJoinCondition('price_join' . $defaultCurrencySuffix, '`price' . $defaultCurrencySuffix . '`.`currency_id` = ?', $defaultCurrency->getId(), null, \PDO::PARAM_INT); /** * rate value is checked as a float in overloaded getRate method. */ $priceToCompareAsSQL = 'CASE WHEN ISNULL(`price`.PRICE) OR `price`.FROM_DEFAULT_CURRENCY = 1 THEN CASE WHEN `pse`.PROMO=1 THEN `price' . $defaultCurrencySuffix . '`.PROMO_PRICE ELSE `price' . $defaultCurrencySuffix . '`.PRICE END * ' . $currency->getRate() . ' ELSE CASE WHEN `pse`.PROMO=1 THEN `price`.PROMO_PRICE ELSE `price`.PRICE END END'; $search->withColumn('ROUND(' . $priceToCompareAsSQL . ', 2)', 'real_price'); $search->withColumn('CASE WHEN ISNULL(`price`.PRICE) OR `price`.FROM_DEFAULT_CURRENCY = 1 THEN `price' . $defaultCurrencySuffix . '`.PRICE * ' . $currency->getRate() . ' ELSE `price`.PRICE END', 'price'); $search->withColumn('CASE WHEN ISNULL(`price`.PRICE) OR `price`.FROM_DEFAULT_CURRENCY = 1 THEN `price' . $defaultCurrencySuffix . '`.PROMO_PRICE * ' . $currency->getRate() . ' ELSE `price`.PROMO_PRICE END', 'promo_price'); } else { $priceToCompareAsSQL = 'CASE WHEN `pse`.PROMO=1 THEN `price`.PROMO_PRICE ELSE `price`.PRICE END'; $search->withColumn('ROUND(' . $priceToCompareAsSQL . ', 2)', 'real_price'); $search->withColumn('`price`.PRICE', 'price'); $search->withColumn('`price`.PROMO_PRICE', 'promo_price'); } } /* manage translations */ $this->configureI18nProcessing($search, ['TITLE', 'CHAPO', 'DESCRIPTION', 'POSTSCRIPTUM', 'META_TITLE', 'META_DESCRIPTION', 'META_KEYWORDS']); $id = $this->getId(); if (!is_null($id)) { $search->filterById($id, Criteria::IN); } $ref = $this->getRef(); if (!is_null($ref)) { $search->filterByRef($ref, Criteria::IN); } $title = $this->getTitle(); if (!is_null($title)) { $search->where("CASE WHEN NOT ISNULL(`requested_locale_i18n`.ID) THEN `requested_locale_i18n`.`TITLE` ELSE `default_locale_i18n`.`TITLE` END " . Criteria::LIKE . " ?", "%" . $title . "%", \PDO::PARAM_STR); } $manualOrderAllowed = false; if (null !== ($categoryDefault = $this->getCategoryDefault())) { // Select the products which have $categoryDefault as the default category. $search->useProductCategoryQuery('DefaultCategorySelect')->filterByDefaultCategory(true)->filterByCategoryId($categoryDefault, Criteria::IN)->endUse(); // We can only sort by position if we have a single category ID $manualOrderAllowed = 1 == count($categoryDefault); } if (null !== ($categoryIdList = $this->getCategory())) { // Select all products which have one of the required categories as the default one, or an associated one $depth = $this->getDepth(); $allCategoryIDs = CategoryQuery::getCategoryTreeIds($categoryIdList, $depth); $search->useProductCategoryQuery('CategorySelect')->filterByCategoryId($allCategoryIDs, Criteria::IN)->endUse(); // We can only sort by position if we have a single category ID, with a depth of 1 $manualOrderAllowed = 1 == $depth && 1 == count($categoryIdList); } $current = $this->getCurrent(); if ($current === true) { $search->filterById($this->request->get("product_id"), Criteria::EQUAL); } elseif ($current === false) { $search->filterById($this->request->get("product_id"), Criteria::NOT_IN); } $brand_id = $this->getBrand(); if ($brand_id !== null) { $search->filterByBrandId($brand_id, Criteria::IN); } $sale_id = $this->getSale(); if ($sale_id !== null) { $search->useSaleProductQuery("SaleProductSelect")->filterBySaleId($sale_id)->groupByProductId()->endUse(); } $current_category = $this->getCurrent_category(); if ($current_category === true) { $search->filterByCategory(CategoryQuery::create()->filterByProduct(ProductCategoryQuery::create()->findPk($this->request->get("product_id")), Criteria::IN)->find(), Criteria::IN); } elseif ($current_category === false) { $search->filterByCategory(CategoryQuery::create()->filterByProduct(ProductCategoryQuery::create()->findPk($this->request->get("product_id")), Criteria::IN)->find(), Criteria::NOT_IN); } $visible = $this->getVisible(); if ($visible !== Type\BooleanOrBothType::ANY) { $search->filterByVisible($visible ? 1 : 0); } $virtual = $this->getVirtual(); if ($virtual !== Type\BooleanOrBothType::ANY) { $search->filterByVirtual($virtual ? 1 : 0); } $exclude = $this->getExclude(); if (!is_null($exclude)) { $search->filterById($exclude, Criteria::NOT_IN); } $exclude_category = $this->getExclude_category(); if (!is_null($exclude_category)) { $search->useProductCategoryQuery('ExcludeCategorySelect')->filterByCategoryId($exclude_category, Criteria::NOT_IN)->endUse(); } $new = $this->getNew(); $promo = $this->getPromo(); $min_stock = $this->getMin_stock(); $min_weight = $this->getMin_weight(); $max_weight = $this->getMax_weight(); $min_price = $this->getMin_price(); $max_price = $this->getMax_price(); if ($complex) { if ($new === true) { $isPSELeftJoinList[] = 'is_new'; $search->joinProductSaleElements('is_new', Criteria::LEFT_JOIN)->where('`is_new`.NEWNESS' . Criteria::EQUAL . '1')->where('NOT ISNULL(`is_new`.ID)'); } elseif ($new === false) { $isPSELeftJoinList[] = 'is_new'; $search->joinProductSaleElements('is_new', Criteria::LEFT_JOIN)->where('`is_new`.NEWNESS' . Criteria::EQUAL . '0')->where('NOT ISNULL(`is_new`.ID)'); } if ($promo === true) { $isPSELeftJoinList[] = 'is_promo'; $search->joinProductSaleElements('is_promo', Criteria::LEFT_JOIN)->where('`is_promo`.PROMO' . Criteria::EQUAL . '1')->where('NOT ISNULL(`is_promo`.ID)'); } elseif ($promo === false) { $isPSELeftJoinList[] = 'is_promo'; $search->joinProductSaleElements('is_promo', Criteria::LEFT_JOIN)->where('`is_promo`.PROMO' . Criteria::EQUAL . '0')->where('NOT ISNULL(`is_promo`.ID)'); } if (null != $min_stock) { $isPSELeftJoinList[] = 'is_min_stock'; $search->joinProductSaleElements('is_min_stock', Criteria::LEFT_JOIN)->where('`is_min_stock`.QUANTITY' . Criteria::GREATER_THAN . '?', $min_stock, \PDO::PARAM_INT)->where('NOT ISNULL(`is_min_stock`.ID)'); } if (null != $min_weight) { $isPSELeftJoinList[] = 'is_min_weight'; $search->joinProductSaleElements('is_min_weight', Criteria::LEFT_JOIN)->where('`is_min_weight`.WEIGHT' . Criteria::GREATER_THAN . '?', $min_weight, \PDO::PARAM_STR)->where('NOT ISNULL(`is_min_weight`.ID)'); } if (null != $max_weight) { $isPSELeftJoinList[] = 'is_max_weight'; $search->joinProductSaleElements('is_max_weight', Criteria::LEFT_JOIN)->where('`is_max_weight`.WEIGHT' . Criteria::LESS_THAN . '?', $max_weight, \PDO::PARAM_STR)->where('NOT ISNULL(`is_max_weight`.ID)'); } $attributeNonStrictMatch = $this->getAttribute_non_strict_match(); if ($attributeNonStrictMatch != '*') { if ($attributeNonStrictMatch == 'none') { $actuallyUsedAttributeNonStrictMatchList = $isPSELeftJoinList; } else { $actuallyUsedAttributeNonStrictMatchList = array_values(array_intersect($isPSELeftJoinList, $attributeNonStrictMatch)); } foreach ($actuallyUsedAttributeNonStrictMatchList as $key => $actuallyUsedAttributeNonStrictMatch) { if ($key == 0) { continue; } $search->where('`' . $actuallyUsedAttributeNonStrictMatch . '`.ID=' . '`' . $actuallyUsedAttributeNonStrictMatchList[$key - 1] . '`.ID'); } } if (null !== $min_price) { if (false === ConfigQuery::useTaxFreeAmounts()) { // @todo } $isPSELeftJoinList[] = 'is_min_price'; $isProductPriceFirstLeftJoin = ['is_min_price', 'min_price_data']; $minPriceJoin = new Join(); $minPriceJoin->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', 'is_min_price', ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'min_price_data'); $minPriceJoin->setJoinType(Criteria::LEFT_JOIN); $search->joinProductSaleElements('is_min_price', Criteria::LEFT_JOIN)->addJoinObject($minPriceJoin, 'is_min_price_join')->addJoinCondition('is_min_price_join', '`min_price_data`.`currency_id` = ?', $currency->getId(), null, \PDO::PARAM_INT); if ($defaultCurrency->getId() != $currency->getId()) { $minPriceJoinDefaultCurrency = new Join(); $minPriceJoinDefaultCurrency->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', 'is_min_price', ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'min_price_data' . $defaultCurrencySuffix); $minPriceJoinDefaultCurrency->setJoinType(Criteria::LEFT_JOIN); $search->addJoinObject($minPriceJoinDefaultCurrency, 'is_min_price_join' . $defaultCurrencySuffix)->addJoinCondition('is_min_price_join' . $defaultCurrencySuffix, '`min_price_data' . $defaultCurrencySuffix . '`.`currency_id` = ?', $defaultCurrency->getId(), null, \PDO::PARAM_INT); /** * In propel we trust : $currency->getRate() always returns a float. * Or maybe not : rate value is checked as a float in overloaded getRate method. */ $MinPriceToCompareAsSQL = 'CASE WHEN ISNULL(CASE WHEN `is_min_price`.PROMO=1 THEN `min_price_data`.PROMO_PRICE ELSE `min_price_data`.PRICE END) OR `min_price_data`.FROM_DEFAULT_CURRENCY = 1 THEN CASE WHEN `is_min_price`.PROMO=1 THEN `min_price_data' . $defaultCurrencySuffix . '`.PROMO_PRICE ELSE `min_price_data' . $defaultCurrencySuffix . '`.PRICE END * ' . $currency->getRate() . ' ELSE CASE WHEN `is_min_price`.PROMO=1 THEN `min_price_data`.PROMO_PRICE ELSE `min_price_data`.PRICE END END'; } else { $MinPriceToCompareAsSQL = 'CASE WHEN `is_min_price`.PROMO=1 THEN `min_price_data`.PROMO_PRICE ELSE `min_price_data`.PRICE END'; } $search->where('ROUND(' . $MinPriceToCompareAsSQL . ', 2)>=?', $min_price, \PDO::PARAM_STR); } if (null !== $max_price) { $isPSELeftJoinList[] = 'is_max_price'; $isProductPriceFirstLeftJoin = ['is_max_price', 'max_price_data']; $maxPriceJoin = new Join(); $maxPriceJoin->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', 'is_max_price', ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'max_price_data'); $maxPriceJoin->setJoinType(Criteria::LEFT_JOIN); $search->joinProductSaleElements('is_max_price', Criteria::LEFT_JOIN)->addJoinObject($maxPriceJoin, 'is_max_price_join')->addJoinCondition('is_max_price_join', '`max_price_data`.`currency_id` = ?', $currency->getId(), null, \PDO::PARAM_INT); if ($defaultCurrency->getId() != $currency->getId()) { $maxPriceJoinDefaultCurrency = new Join(); $maxPriceJoinDefaultCurrency->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', 'is_max_price', ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'max_price_data' . $defaultCurrencySuffix); $maxPriceJoinDefaultCurrency->setJoinType(Criteria::LEFT_JOIN); $search->addJoinObject($maxPriceJoinDefaultCurrency, 'is_max_price_join' . $defaultCurrencySuffix)->addJoinCondition('is_max_price_join' . $defaultCurrencySuffix, '`max_price_data' . $defaultCurrencySuffix . '`.`currency_id` = ?', $defaultCurrency->getId(), null, \PDO::PARAM_INT); /** * In propel we trust : $currency->getRate() always returns a float. * Or maybe not : rate value is checked as a float in overloaded getRate method. */ $MaxPriceToCompareAsSQL = 'CASE WHEN ISNULL(CASE WHEN `is_max_price`.PROMO=1 THEN `max_price_data`.PROMO_PRICE ELSE `max_price_data`.PRICE END) OR `min_price_data`.FROM_DEFAULT_CURRENCY = 1 THEN CASE WHEN `is_max_price`.PROMO=1 THEN `max_price_data' . $defaultCurrencySuffix . '`.PROMO_PRICE ELSE `max_price_data' . $defaultCurrencySuffix . '`.PRICE END * ' . $currency->getRate() . ' ELSE CASE WHEN `is_max_price`.PROMO=1 THEN `max_price_data`.PROMO_PRICE ELSE `max_price_data`.PRICE END END'; } else { $MaxPriceToCompareAsSQL = 'CASE WHEN `is_max_price`.PROMO=1 THEN `max_price_data`.PROMO_PRICE ELSE `max_price_data`.PRICE END'; } $search->where('ROUND(' . $MaxPriceToCompareAsSQL . ', 2)<=?', $max_price, \PDO::PARAM_STR); } /* * for ordering and outputs, the product will be : * - new if at least one the criteria matching PSE is new * - in promo if at least one the criteria matching PSE is in promo */ /* if we don't have any join yet, let's make a global one */ if (empty($isProductPriceFirstLeftJoin)) { if (count($isPSELeftJoinList) == 0) { $joiningTable = "global"; $isPSELeftJoinList[] = $joiningTable; $search->joinProductSaleElements('global', Criteria::LEFT_JOIN); } else { $joiningTable = $isPSELeftJoinList[0]; } $isProductPriceFirstLeftJoin = [$joiningTable, 'global_price_data']; $globalPriceJoin = new Join(); $globalPriceJoin->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', $joiningTable, ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'global_price_data'); $globalPriceJoin->setJoinType(Criteria::LEFT_JOIN); $search->addJoinObject($globalPriceJoin, 'global_price_join')->addJoinCondition('global_price_join', '`global_price_data`.`currency_id` = ?', $currency->getId(), null, \PDO::PARAM_INT); if ($defaultCurrency->getId() != $currency->getId()) { $globalPriceJoinDefaultCurrency = new Join(); $globalPriceJoinDefaultCurrency->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', $joiningTable, ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'global_price_data' . $defaultCurrencySuffix); $globalPriceJoinDefaultCurrency->setJoinType(Criteria::LEFT_JOIN); $search->addJoinObject($globalPriceJoinDefaultCurrency, 'global_price_join' . $defaultCurrencySuffix)->addJoinCondition('global_price_join' . $defaultCurrencySuffix, '`global_price_data' . $defaultCurrencySuffix . '`.`currency_id` = ?', $defaultCurrency->getId(), null, \PDO::PARAM_INT); } } /* * we need to test all promo field from our previous conditions. Indeed ie: * product P0, attributes color : red * P0red is in promo and is the only attribute combinaton availability. * so the product might be consider as in promo (in outputs and ordering) * We got the following loop to display in promo AND new product but we don't care it's the same attribute which is new and in promo : * {loop type="product" promo="1" new="1" attribute_non_strict_match="promo,new"} {/loop} * our request will so far returns 1 line * * is_promo.ID | is_promo.PROMO | is_promo.NEWNESS | is_new.ID | is_new.PROMO | is_new.NEWNESS * NULL NULL NULL red_id 1 0 * * So that we can say the product is in global promo only with is_promo.PROMO, we must acknowledge it with (is_promo.PROMO OR is_new.PROMO) */ $booleanMatchedPromoList = []; $booleanMatchedNewnessList = []; foreach ($isPSELeftJoinList as $isPSELeftJoin) { $booleanMatchedPromoList[] = '`' . $isPSELeftJoin . '`.PROMO'; $booleanMatchedNewnessList[] = '`' . $isPSELeftJoin . '`.NEWNESS'; } $search->withColumn('ROUND(MAX(' . implode(' OR ', $booleanMatchedPromoList) . '), 2)', 'main_product_is_promo'); $search->withColumn('ROUND(MAX(' . implode(' OR ', $booleanMatchedNewnessList) . '), 2)', 'main_product_is_new'); $booleanMatchedPrice = 'CASE WHEN `' . $isProductPriceFirstLeftJoin[0] . '`.PROMO=1 THEN `' . $isProductPriceFirstLeftJoin[1] . '`.PROMO_PRICE ELSE `' . $isProductPriceFirstLeftJoin[1] . '`.PRICE END'; $booleanMatchedPriceDefaultCurrency = 'CASE WHEN `' . $isProductPriceFirstLeftJoin[0] . '`.PROMO=1 THEN `' . $isProductPriceFirstLeftJoin[1] . $defaultCurrencySuffix . '`.PROMO_PRICE ELSE `' . $isProductPriceFirstLeftJoin[1] . $defaultCurrencySuffix . '`.PRICE END'; if ($defaultCurrency->getId() != $currency->getId()) { /** * In propel we trust : $currency->getRate() always returns a float. * Or maybe not : rate value is checked as a float in overloaded getRate method. */ $priceToCompareAsSQL = 'CASE WHEN ISNULL(' . $booleanMatchedPrice . ') THEN ' . $booleanMatchedPriceDefaultCurrency . ' * ' . $currency->getRate() . ' ELSE ' . $booleanMatchedPrice . ' END'; } else { $priceToCompareAsSQL = $booleanMatchedPrice; } $search->withColumn('ROUND(MAX(' . $priceToCompareAsSQL . '), 2)', 'real_highest_price'); $search->withColumn('ROUND(MIN(' . $priceToCompareAsSQL . '), 2)', 'real_lowest_price'); } else { if ($new === true) { $search->where('`pse`.NEWNESS' . Criteria::EQUAL . '1'); } elseif ($new === false) { $search->where('`pse`.NEWNESS' . Criteria::EQUAL . '0'); } if ($promo === true) { $search->where('`pse`.PROMO' . Criteria::EQUAL . '1'); } elseif ($promo === false) { $search->where('`pse`.PROMO' . Criteria::EQUAL . '0'); } if (null != $min_stock) { $search->where('`pse`.QUANTITY' . Criteria::GREATER_THAN . '?', $min_stock, \PDO::PARAM_INT); } if (null != $min_weight) { $search->where('`pse`.WEIGHT' . Criteria::GREATER_THAN . '?', $min_weight, \PDO::PARAM_STR); } if (null != $max_weight) { $search->where('`is_max_weight`.WEIGHT' . Criteria::LESS_THAN . '?', $max_weight, \PDO::PARAM_STR); } if (null !== $min_price) { if (false === ConfigQuery::useTaxFreeAmounts()) { // @todo } $search->where('ROUND(' . $priceToCompareAsSQL . ', 2)>=?', $min_price, \PDO::PARAM_STR); } if (null !== $max_price) { if (false === ConfigQuery::useTaxFreeAmounts()) { // @todo } $search->where('ROUND(' . $priceToCompareAsSQL . ', 2)<=?', $max_price, \PDO::PARAM_STR); } } // First join sale_product table... $search->leftJoinSaleProduct('SaleProductPriceDisplay'); // ... then the sale table... $salesJoin = new Join(); $salesJoin->addExplicitCondition('SaleProductPriceDisplay', 'SALE_ID', null, SaleTableMap::TABLE_NAME, 'ID', 'SalePriceDisplay'); $salesJoin->setJoinType(Criteria::LEFT_JOIN); $search->addJoinObject($salesJoin, 'SalePriceDisplay')->addJoinCondition('SalePriceDisplay', '`SalePriceDisplay`.`active` = 1'); // ... to get the DISPLAY_INITIAL_PRICE column ! $search->withColumn('`SalePriceDisplay`.DISPLAY_INITIAL_PRICE', 'display_initial_price'); $feature_availability = $this->getFeature_availability(); $this->manageFeatureAv($search, $feature_availability); $feature_values = $this->getFeature_values(); $this->manageFeatureValue($search, $feature_values); $search->groupBy(ProductTableMap::ID); if (!$complex) { $search->withColumn('`pse`.ID', 'pse_id'); $search->withColumn('`pse`.NEWNESS', 'is_new'); $search->withColumn('`pse`.PROMO', 'is_promo'); $search->withColumn('`pse`.QUANTITY', 'quantity'); $search->withColumn('`pse`.WEIGHT', 'weight'); $search->withColumn('`pse`.EAN_CODE', 'ean_code'); $search->withColumn('COUNT(`pse_count`.ID)', 'pse_count'); } $orders = $this->getOrder(); foreach ($orders as $order) { switch ($order) { case "id": $search->orderById(Criteria::ASC); break; case "id_reverse": $search->orderById(Criteria::DESC); break; case "alpha": $search->addAscendingOrderByColumn('i18n_TITLE'); break; case "alpha_reverse": $search->addDescendingOrderByColumn('i18n_TITLE'); break; case "min_price": if ($complex) { $search->addAscendingOrderByColumn('real_lowest_price', Criteria::ASC); } else { $search->addAscendingOrderByColumn('real_price'); } break; case "max_price": if ($complex) { $search->addDescendingOrderByColumn('real_lowest_price'); } else { $search->addDescendingOrderByColumn('real_price'); } break; case "manual": if (!$manualOrderAllowed) { throw new \InvalidArgumentException('Manual order require a *single* category ID or category_default ID, and a depth <= 1'); } $search->orderByPosition(Criteria::ASC); break; case "manual_reverse": if (!$manualOrderAllowed) { throw new \InvalidArgumentException('Manual reverse order require a *single* category ID or category_default ID, and a depth <= 1'); } $search->orderByPosition(Criteria::DESC); break; case "ref": $search->orderByRef(Criteria::ASC); break; case "ref_reverse": $search->orderByRef(Criteria::DESC); break; case "promo": if ($complex) { $search->addDescendingOrderByColumn('main_product_is_promo'); } else { $search->addDescendingOrderByColumn('is_promo'); } break; case "new": if ($complex) { $search->addDescendingOrderByColumn('main_product_is_new'); } else { $search->addDescendingOrderByColumn('is_new'); } break; case "created": $search->addAscendingOrderByColumn('created_at'); break; case "created_reverse": $search->addDescendingOrderByColumn('created_at'); break; case "updated": $search->addAscendingOrderByColumn('updated_at'); break; case "updated_reverse": $search->addDescendingOrderByColumn('updated_at'); break; case "given_id": if (null === $id) { throw new \InvalidArgumentException('Given_id order cannot be set without `id` argument'); } foreach ($id as $singleId) { $givenIdMatched = 'given_id_matched_' . $singleId; $search->withColumn(ProductTableMap::ID . "='{$singleId}'", $givenIdMatched); $search->orderBy($givenIdMatched, Criteria::DESC); } break; case "random": $search->clearOrderByColumns(); $search->addAscendingOrderByColumn('RAND()'); break 2; } } return $search; }
public function testSetJoinType() { $j = new Join(); $j->setJoinType('foo'); $this->assertEquals('foo', $j->getJoinType()); }
/** * Copy of default product loop price filter but with tax applied to asked price * @param $search * @param $minPriceTTC * @param $maxPriceTTC * @throws \Propel\Runtime\Exception\PropelException */ protected function managePriceFilter(&$search, $minPriceTTC, $maxPriceTTC) { $categoryId = $this->getCategoryId(); $taxeRuleQuery = TaxRuleQuery::create(); $categoryJoin = new Join(); $categoryJoin->addExplicitCondition(TaxRuleTableMap::TABLE_NAME, 'ID', null, CriteriaSearchCategoryTaxRuleTableMap::TABLE_NAME, 'TAX_RULE_ID', null); $categoryJoin->setJoinType(Criteria::LEFT_JOIN); $taxeRuleQuery->addJoinObject($categoryJoin, 'category_join')->addJoinCondition('category_join', CriteriaSearchCategoryTaxRuleTableMap::CATEGORY_ID . ' = ' . $categoryId); $taxeRule = $taxeRuleQuery->findOne(); $taxCountry = $this->container->get('thelia.taxEngine')->getDeliveryCountry(); $calculator = new Calculator(); $calculator->loadTaxRuleWithoutProduct($taxeRule, $taxCountry); $currencyId = $this->getCurrency(); if (null !== $currencyId) { $currency = CurrencyQuery::create()->findOneById($currencyId); if (null === $currency) { throw new \InvalidArgumentException('Cannot found currency id: `' . $currency . '` in product_sale_elements loop'); } } else { $currency = $this->request->getSession()->getCurrency(); } $defaultCurrency = CurrencyQuery::create()->findOneByByDefault(1); $defaultCurrencySuffix = '_default_currency'; if (null !== $minPriceTTC) { $minPriceHt = round($calculator->getUntaxedPrice($minPriceTTC), 2); $isPSELeftJoinList[] = 'is_min_price'; $minPriceJoin = new Join(); $minPriceJoin->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', 'is_min_price_ttc', ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'min_price_ttc_data'); $minPriceJoin->setJoinType(Criteria::LEFT_JOIN); $search->joinProductSaleElements('is_min_price_ttc', Criteria::LEFT_JOIN)->addJoinObject($minPriceJoin, 'is_min_price_ttc_join')->addJoinCondition('is_min_price_ttc_join', '`min_price_ttc_data`.`currency_id` = ?', $currency->getId(), null, \PDO::PARAM_INT); if ($defaultCurrency->getId() != $currency->getId()) { $minPriceJoinDefaultCurrency = new Join(); $minPriceJoinDefaultCurrency->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', 'is_min_price_ttc', ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'min_price_ttc_data' . $defaultCurrencySuffix); $minPriceJoinDefaultCurrency->setJoinType(Criteria::LEFT_JOIN); $search->addJoinObject($minPriceJoinDefaultCurrency, 'is_min_price_ttc_join' . $defaultCurrencySuffix)->addJoinCondition('is_min_price_ttc_join' . $defaultCurrencySuffix, '`min_price_ttc_data' . $defaultCurrencySuffix . '`.`currency_id` = ?', $defaultCurrency->getId(), null, \PDO::PARAM_INT); /** * In propel we trust : $currency->getRate() always returns a float. * Or maybe not : rate value is checked as a float in overloaded getRate method. */ $MinPriceToCompareAsSQL = 'CASE WHEN ISNULL(CASE WHEN `is_min_price_ttc`.PROMO=1 THEN `min_price_ttc_data`.PROMO_PRICE ELSE `min_price_ttc_data`.PRICE END) OR `min_price_ttc_data`.FROM_DEFAULT_CURRENCY = 1 THEN CASE WHEN `is_min_price_ttc`.PROMO=1 THEN `min_price_ttc_data' . $defaultCurrencySuffix . '`.PROMO_PRICE ELSE `min_price_ttc_data' . $defaultCurrencySuffix . '`.PRICE END * ' . $currency->getRate() . ' ELSE CASE WHEN `is_min_price_ttc`.PROMO=1 THEN `min_price_ttc_data`.PROMO_PRICE ELSE `min_price_ttc_data`.PRICE END END'; } else { $MinPriceToCompareAsSQL = 'CASE WHEN `is_min_price_ttc`.PROMO=1 THEN `min_price_ttc_data`.PROMO_PRICE ELSE `min_price_ttc_data`.PRICE END'; } $search->where('ROUND(' . $MinPriceToCompareAsSQL . ', 2)>=?', $minPriceHt, \PDO::PARAM_STR); } if (null !== $maxPriceTTC) { $maxPriceHt = round($calculator->getUntaxedPrice($maxPriceTTC), 2); $isPSELeftJoinList[] = 'is_max_price_ttc'; $maxPriceJoin = new Join(); $maxPriceJoin->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', 'is_max_price_ttc', ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'max_price_ttc_data'); $maxPriceJoin->setJoinType(Criteria::LEFT_JOIN); $search->joinProductSaleElements('is_max_price_ttc', Criteria::LEFT_JOIN)->addJoinObject($maxPriceJoin, 'is_max_price_ttc_join')->addJoinCondition('is_max_price_ttc_join', '`max_price_ttc_data`.`currency_id` = ?', $currency->getId(), null, \PDO::PARAM_INT); if ($defaultCurrency->getId() != $currency->getId()) { $maxPriceJoinDefaultCurrency = new Join(); $maxPriceJoinDefaultCurrency->addExplicitCondition(ProductSaleElementsTableMap::TABLE_NAME, 'ID', 'is_max_price_ttc', ProductPriceTableMap::TABLE_NAME, 'PRODUCT_SALE_ELEMENTS_ID', 'max_price_ttc_data' . $defaultCurrencySuffix); $maxPriceJoinDefaultCurrency->setJoinType(Criteria::LEFT_JOIN); $search->addJoinObject($maxPriceJoinDefaultCurrency, 'is_max_price_ttc_join' . $defaultCurrencySuffix)->addJoinCondition('is_max_price_ttc_join' . $defaultCurrencySuffix, '`max_price_ttc_data' . $defaultCurrencySuffix . '`.`currency_id` = ?', $defaultCurrency->getId(), null, \PDO::PARAM_INT); /** * In propel we trust : $currency->getRate() always returns a float. * Or maybe not : rate value is checked as a float in overloaded getRate method. */ $MaxPriceToCompareAsSQL = 'CASE WHEN ISNULL(CASE WHEN `is_max_price_ttc`.PROMO=1 THEN `max_price_ttc_data`.PROMO_PRICE ELSE `max_price_ttc_data`.PRICE END) OR `min_price_data`.FROM_DEFAULT_CURRENCY = 1 THEN CASE WHEN `is_max_price_ttc`.PROMO=1 THEN `max_price_ttc_data' . $defaultCurrencySuffix . '`.PROMO_PRICE ELSE `max_price_ttc_data' . $defaultCurrencySuffix . '`.PRICE END * ' . $currency->getRate() . ' ELSE CASE WHEN `is_max_price_ttc`.PROMO=1 THEN `max_price_ttc_data`.PROMO_PRICE ELSE `max_price_ttc_data`.PRICE END END'; } else { $MaxPriceToCompareAsSQL = 'CASE WHEN `is_max_price_ttc`.PROMO=1 THEN `max_price_ttc_data`.PROMO_PRICE ELSE `max_price_ttc_data`.PRICE END'; } $search->where('ROUND(' . $MaxPriceToCompareAsSQL . ', 2)<=?', $maxPriceHt, \PDO::PARAM_STR); } }
public static function getBackEndI18n(ModelCriteria &$search, $requestedLocale, $columns = array('TITLE', 'CHAPO', 'DESCRIPTION', 'POSTSCRIPTUM'), $foreignTable = null, $foreignKey = 'ID') { if ($foreignTable === null) { $foreignTable = $search->getTableMap()->getName(); $aliasPrefix = ''; } else { $aliasPrefix = $foreignTable . '_'; } $requestedLocaleI18nAlias = $aliasPrefix . 'requested_locale_i18n'; $requestedLocaleJoin = new Join(); $requestedLocaleJoin->addExplicitCondition($search->getTableMap()->getName(), $foreignKey, null, $foreignTable . '_i18n', 'ID', $requestedLocaleI18nAlias); $requestedLocaleJoin->setJoinType(Criteria::LEFT_JOIN); $search->addJoinObject($requestedLocaleJoin, $requestedLocaleI18nAlias)->addJoinCondition($requestedLocaleI18nAlias, '`' . $requestedLocaleI18nAlias . '`.LOCALE = ?', $requestedLocale, null, \PDO::PARAM_STR); $search->withColumn('NOT ISNULL(`' . $requestedLocaleI18nAlias . '`.`ID`)', $aliasPrefix . 'IS_TRANSLATED'); foreach ($columns as $column) { $search->withColumn('`' . $requestedLocaleI18nAlias . '`.`' . $column . '`', $aliasPrefix . 'i18n_' . $column); } }