/**
* @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;
}
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);
}
}
/**
* 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;
}
/**
* @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);
}
public function buildDataSet(Lang $lang)
{
/** @var \Thelia\Model\AttributeCombinationQuery $query */
$query = parent::buildDataSet($lang);
$pseJoin = new Join(AttributeCombinationTableMap::PRODUCT_SALE_ELEMENTS_ID, ProductSaleElementsTableMap::ID);
$pseJoin->setRightTableAlias("pse_tax_join");
$productJoin = new Join(ProductSaleElementsTableMap::ID, ProductTableMap::ID);
$productJoin->setRightTableAlias("product_tax_join");
$taxJoin = new Join("`product_tax_join`.TAX_RULE_ID", TaxRuleTableMap::ID, Criteria::LEFT_JOIN);
$taxI18nJoin = new Join(TaxRuleTableMap::ID, TaxRuleI18nTableMap::ID, Criteria::LEFT_JOIN);
$query->addJoinObject($pseJoin, "pse_tax_join")->addJoinObject($productJoin, "product_tax_join")->addJoinObject($productJoin)->addJoinObject($taxJoin)->addJoinObject($taxI18nJoin)->addAsColumn("product_TAX_TITLE", TaxRuleI18nTableMap::TITLE)->addAsColumn("tax_ID", TaxRuleTableMap::ID)->select($query->getSelect() + ["product_TAX_TITLE", "tax_ID"]);
I18n::addI18nCondition($query, TaxRuleI18nTableMap::TABLE_NAME, TaxRuleTableMap::ID, TaxRuleI18nTableMap::ID, TaxRuleI18nTableMap::LOCALE, $lang->getLocale());
$dataSet = $query->keepQuery(true)->find()->toArray();
$productSaleElements = ProductSaleElementsQuery::create()->find()->toKeyIndex("Id");
$currencies = CurrencyQuery::create()->find()->toKeyIndex("Code");
foreach ($dataSet as &$line) {
/** @var \Thelia\Model\ProductSaleElements $pse */
$pse = $productSaleElements[$line["product_sale_elements_ID"]];
$pricesTools = $pse->getPricesByCurrency($currencies[$line["currency_CODE"]]);
$line["price_PRICE"] = $pricesTools->getPrice();
$line["price_PROMO_PRICE"] = $pricesTools->getPromoPrice();
}
return $dataSet;
}
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;
}
/**
* 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 testJoinObject()
{
$j = new Join('TABLE_A.COL_1', 'TABLE_B.COL_2');
$this->assertEquals('INNER JOIN', $j->getJoinType());
$this->assertEquals('TABLE_A.COL_1', $j->getLeftColumn());
$this->assertEquals('TABLE_A', $j->getLeftTableName());
$this->assertEquals('COL_1', $j->getLeftColumnName());
$this->assertEquals('TABLE_B.COL_2', $j->getRightColumn());
$this->assertEquals('TABLE_B', $j->getRightTableName());
$this->assertEquals('COL_2', $j->getRightColumnName());
$j = new Join('TABLE_A.COL_1', 'TABLE_B.COL_1', Criteria::LEFT_JOIN);
$this->assertEquals('LEFT JOIN', $j->getJoinType());
$this->assertEquals('TABLE_A.COL_1', $j->getLeftColumn());
$this->assertEquals('TABLE_B.COL_1', $j->getRightColumn());
$j = new Join('TABLE_A.COL_1', 'TABLE_B.COL_1', Criteria::RIGHT_JOIN);
$this->assertEquals('RIGHT JOIN', $j->getJoinType());
$this->assertEquals('TABLE_A.COL_1', $j->getLeftColumn());
$this->assertEquals('TABLE_B.COL_1', $j->getRightColumn());
$j = new Join('TABLE_A.COL_1', 'TABLE_B.COL_1', Criteria::INNER_JOIN);
$this->assertEquals('INNER JOIN', $j->getJoinType());
$this->assertEquals('TABLE_A.COL_1', $j->getLeftColumn());
$this->assertEquals('TABLE_B.COL_1', $j->getRightColumn());
$j = new Join(array('TABLE_A.COL_1', 'TABLE_A.COL_2'), array('TABLE_B.COL_1', 'TABLE_B.COL_2'), Criteria::INNER_JOIN);
$this->assertEquals('TABLE_A.COL_1', $j->getLeftColumn(0));
$this->assertEquals('TABLE_A.COL_2', $j->getLeftColumn(1));
$this->assertEquals('TABLE_B.COL_1', $j->getRightColumn(0));
$this->assertEquals('TABLE_B.COL_2', $j->getRightColumn(1));
}
/**
* @api
*
* @param int $idNode
*
* @return \Orm\Zed\Category\Persistence\SpyCategoryClosureTableQuery
*/
public function queryClosureTableParentEntries($idNode)
{
$query = $this->getFactory()->createCategoryClosureTableQuery();
$query->setModelAlias('node');
$joinCategoryNodeDescendant = new Join('node.fk_category_node_descendant', 'descendants.fk_category_node_descendant', Criteria::JOIN);
$joinCategoryNodeDescendant->setRightTableName('spy_category_closure_table')->setRightTableAlias('descendants')->setLeftTableName('spy_category_closure_table')->setLeftTableAlias('node');
$joinCategoryNodeAscendant = new Join('descendants.fk_category_node', 'ascendants.fk_category_node', Criteria::LEFT_JOIN);
$joinCategoryNodeAscendant->setRightTableName('spy_category_closure_table')->setRightTableAlias('ascendants')->setLeftTableName('spy_category_closure_table')->setLeftTableAlias('descendants');
$query->addJoinObject($joinCategoryNodeDescendant);
$query->addJoinObject($joinCategoryNodeAscendant, 'ascendantsJoin');
$query->addJoinCondition('ascendantsJoin', 'ascendants.fk_category_node_descendant = node.fk_category_node');
$query->where('descendants.fk_category_node = ' . $idNode)->where('ascendants.fk_category_node IS NULL');
return $query;
}
public function testEquality()
{
$j1 = new Join('foo', 'bar', 'INNER JOIN');
$this->assertFalse($j1->equals(null), 'Join and null is not equal');
$j2 = new Join('foo', 'bar', 'LEFT JOIN');
$this->assertFalse($j1->equals($j2), 'INNER JOIN and LEFT JOIN are not equal');
$j3 = new Join('foo', 'bar', 'INNER JOIN');
$j3->addCondition('baz.foo', 'baz.bar');
$this->assertFalse($j1->equals($j3), 'Joins with differend conditionsare not equal');
$j4 = new Join('foo', 'bar', 'INNER JOIN');
$j4->addExplicitCondition('book', 'AUTHOR_ID', null, 'author', 'ID', 'a', Join::EQUAL);
$this->assertFalse($j1->equals($j4), 'Joins with differend clauses not equal');
$j5 = new Join('foo', 'bar');
$j6 = new Join('foo', 'bar');
$this->assertTrue($j5->equals($j6), 'Joins without specified join type should be equal as they fallback to default join type');
}
public function __toString()
{
return parent::toString() . ' tableMap: ' . ($this->tableMap ? get_class($this->tableMap) : 'null') . ' relationMap: ' . $this->relationMap->getName() . ' previousJoin: ' . ($this->previousJoin ? '(' . $this->previousJoin . ')' : 'null') . ' relationAlias: ' . $this->rightTableAlias;
}
/**
* @param null|Join $join
* @return bool
*/
public function equals($join)
{
$parametersOfThisClauses = array();
$parametersOfJoinClauses = array();
return null !== $join && $join instanceof Join && $this->getJoinType() === $join->getJoinType() && $this->getConditions() == $join->getConditions() && $this->getClause($parametersOfThisClauses) == $join->getClause($parametersOfJoinClauses);
}
public function testCountConditions()
{
$j = new Join();
$this->assertEquals(0, $j->countConditions());
$j->addCondition('foo', 'bar');
$this->assertEquals(1, $j->countConditions());
$j->addCondition('foo1', 'bar1');
$this->assertEquals(2, $j->countConditions());
}
/**
* @api
*
* @param int $idGroup
*
* @return \Orm\Zed\User\Persistence\SpyUserQuery
*/
public function queryGroupUsers($idGroup)
{
$query = $this->getFactory()->createUserQuery();
$join = new Join();
$join->addCondition(SpyUserTableMap::COL_ID_USER, SpyAclUserHasGroupTableMap::COL_FK_USER);
$query->addJoinObject($join, self::GROUP_JOIN);
$condition = sprintf('%s = %s', SpyAclUserHasGroupTableMap::COL_FK_ACL_GROUP, $idGroup);
$query->addJoinCondition(self::GROUP_JOIN, $condition);
return $query;
}
/**
* 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);
}
}