/** * Add inventory condition to search in-stock products * * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare * * @return void */ protected function prepareCndInventoryIn(\Doctrine\ORM\QueryBuilder $queryBuilder) { $queryBuilder->linkLeft('p.variants', 'pv'); $orCnd = new \Doctrine\ORM\Query\Expr\Orx(); $orCnd->add('i.enabled = :disabled'); $orCnd->add('i.amount > :zero'); $orCnd->add('pv.amount > :zero'); $queryBuilder->andWhere($orCnd)->setParameter('disabled', false)->setParameter('zero', 0); }
/** * Prepare certain search condition * * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare * @param array $value Condition data * @param boolean $countOnly Count only flag * * @return void */ protected function prepareCndParticipateSale(\Doctrine\ORM\QueryBuilder $queryBuilder, $value, $countOnly) { $cnd = new \Doctrine\ORM\Query\Expr\Orx(); $pricePercentCnd = new \Doctrine\ORM\Query\Expr\Andx(); $pricePercentCnd->add('p.discountType = :discountTypePercent'); $pricePercentCnd->add('p.salePriceValue > 0'); $priceAbsoluteCnd = new \Doctrine\ORM\Query\Expr\Andx(); $priceAbsoluteCnd->add('p.discountType = :discountTypePrice'); $priceAbsoluteCnd->add('p.price > p.salePriceValue'); $cnd->add($pricePercentCnd); $cnd->add($priceAbsoluteCnd); if (!$countOnly) { $queryBuilder->addSelect('if(p.discountType = :discountTypePercent, p.salePriceValue, 100 - 100 * p.salePriceValue / p.price) ' . static::PERCENT_CALCULATED_FIELD); } $queryBuilder->andWhere('p.participateSale = :participateSale')->andWhere($cnd)->setParameter('participateSale', $value)->setParameter('discountTypePercent', \XLite\Module\CDev\Sale\Model\Product::SALE_DISCOUNT_TYPE_PERCENT)->setParameter('discountTypePrice', \XLite\Module\CDev\Sale\Model\Product::SALE_DISCOUNT_TYPE_PRICE); }
/** * Bind pattern condition * * @param string $value Condition * * @return \XLite\Model\QueryBuilder\AQueryBuilder */ public function bindPattern($value) { if (!empty($value)) { $cnd = new \Doctrine\ORM\Query\Expr\Orx(); $this->prepareField($this, 'firstname')->prepareField($this, 'lastname'); foreach ($this->getNameSubstringSearchFields() as $field) { $cnd->add($field . ' LIKE :pattern'); } $this->andWhere($cnd)->setParameter('pattern', '%' . $value . '%'); } return $this; }
public function buildQuery(QueryConfigInterface $query, TableInterface $table) { parent::buildQuery($query, $table); $_query = $table->getOption('_query'); $params = $table->getOption('rows_params'); if ($table->getOption('allow_select')) { if (!isset($_query['selectedRows'])) { throw new \InvalidArgumentException('selectedRows is required'); } if (count($_query['selectedRows']) === 0) { $query->setValid(false); return; } if (count($params) === 1) { $name = key($params); $param = reset($params); $selectedRowIds = array(); foreach ($_query['selectedRows'] as $row) { if (!isset($row[$name])) { throw new \UnexpectedValueException('Parameter "' . $name . '" not found.'); } $selectedRowIds[] = $row[$name]; } $orX = new \Doctrine\ORM\Query\Expr\Orx(); $orX->add($param . ' in (:selectedRowIds)'); $query->setConstraints($orX)->addParameter('selectedRowIds', $selectedRowIds); } else { $reversedParams = array_flip($params); if (count($params) !== count($reversedParams)) { throw new \RuntimeException('rows_params values must be unique'); } $query->setParameters(array()); $orX = new \Doctrine\ORM\Query\Expr\Orx(); $selectedRows = array_values($_query['selectedRows']); foreach ($selectedRows as $i => $row) { $row = $this->type->resolveParams($reversedParams, $row); $andX = new \Doctrine\ORM\Query\Expr\Andx(); foreach ($row as $param => $value) { $andX->add($param . ' = :' . $reversedParams[$param] . $i); $query->addParameter($reversedParams[$param] . $i, $value); } $orX->add($andX); } $query->setConstraints($orX); } } }
/** * Prepare certain search condition * * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare * @param string|null $value Condition data * * @return void */ protected function prepareCndSubstring(\Doctrine\ORM\QueryBuilder $queryBuilder, $value) { $searchWords = $this->getSearchWords($value); $cnd = new \Doctrine\ORM\Query\Expr\Orx(); foreach ($this->getSubstringSearchFields() as $field) { foreach ($searchWords as $index => $word) { // Collect OR expressions $cnd->add($field . ' LIKE :word' . $index); $queryBuilder->setParameter('word' . $index, '%' . $word . '%'); } } $queryBuilder->andWhere($cnd); }
/** * Prepare certain search condition * * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare * @param integer $value Condition data * * @return void */ protected function prepareCndCustomerName(\Doctrine\ORM\QueryBuilder $queryBuilder, $value) { if (!empty($value)) { $queryBuilder->linkLeft('p.addresses', 'addresses'); $this->prepareOrderByAddressField($queryBuilder, 'firstname'); $this->prepareOrderByAddressField($queryBuilder, 'lastname'); $cnd = new \Doctrine\ORM\Query\Expr\Orx(); foreach ($this->getCustomerNameSearchFields() as $field) { $cnd->add($field . ' LIKE :customerName'); } $queryBuilder->andWhere($cnd)->setParameter('customerName', '%' . $value . '%'); } }
/** * Prepare certain search condition * * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare * @param string $value Condition data * * @return void */ protected function prepareCndInventory(\Doctrine\ORM\QueryBuilder $queryBuilder, $value = self::INV_ALL) { $queryBuilder->innerJoinInventory(); if (in_array($value, array(self::INV_LOW, self::INV_OUT))) { $queryBuilder->andWhere('i.enabled = :enabled')->setParameter('enabled', true); } if ($value === self::INV_LOW) { $queryBuilder->andWhere('i.lowLimitEnabled = :lowLimitEnabled')->setParameter('lowLimitEnabled', true)->andWhere('i.amount < i.lowLimitAmount'); } elseif ($value === self::INV_OUT) { $queryBuilder->andWhere('i.amount <= :zero')->setParameter('zero', 0); } elseif ($value == self::INV_IN) { $orCnd = new \Doctrine\ORM\Query\Expr\Orx(); $orCnd->add('i.enabled = :disabled'); $orCnd->add('i.amount > :zero'); $queryBuilder->andWhere($orCnd)->setParameter('disabled', false)->setParameter('zero', 0); } }
/** * Prepare certain search condition * * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare * @param string|null $value Condition data * * @return void */ protected function prepareCndSubstring(\Doctrine\ORM\QueryBuilder $queryBuilder, $value) { $searchWords = $this->getSearchWords($value); $searchPhrase = implode(' ', $searchWords); $cnd = new \Doctrine\ORM\Query\Expr\Orx(); foreach ($this->getSubstringSearchFields() as $field) { foreach ($searchWords as $index => $word) { // Collect OR expressions $cnd->add($field . ' LIKE :word' . $index); $queryBuilder->setParameter('word' . $index, '%' . $word . '%'); } } if ($searchPhrase) { $queryBuilder->addSelect(sprintf('RELEVANCE(\'%s\', %s, %s) as relevance', $value, $this->getRelevanceTitleField(), $this->getRelevanceTextField())); $orderBys = $queryBuilder->getDQLPart('orderBy'); $queryBuilder->resetDQLPart('orderBy'); $queryBuilder->addOrderBy('relevance', 'desc'); foreach ($orderBys as $value) { $queryBuilder->add('orderBy', $value, true); } } $queryBuilder->andWhere($cnd); }
/** * Prepare certain search condition * * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare * @param array $value Condition data * @param boolean $countOnly "Count only" flag. Do not need to add "order by" clauses if only count is needed. * * @return void */ protected function prepareCndSubtotal(\Doctrine\ORM\QueryBuilder $queryBuilder, $value, $countOnly) { $cnd = new \Doctrine\ORM\Query\Expr\Orx(); $cnd->add('v.subtotalRangeEnd > :subtotal'); $cnd->add('v.subtotalRangeEnd = 0'); $queryBuilder->andWhere('v.subtotalRangeBegin <= :subtotal')->andWhere($cnd)->setParameter('subtotal', $value); }
/** * Prepare certain search condition * * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare * @param \XLite\Model\Profile $value Profile * * @return void */ protected function prepareCndSubstring(\Doctrine\ORM\QueryBuilder $queryBuilder, $value) { if (!empty($value)) { $queryBuilder->leftJoin('l.translations', 'lt'); $or = new \Doctrine\ORM\Query\Expr\Orx(); // Use non-standard Doctrine function CAST(expr AS CHAR) $or->add('CastChar(l.name) LIKE :substring'); $and = new \Doctrine\ORM\Query\Expr\Andx(); $and->add('lt.label LIKE :substring'); $and->add($queryBuilder->expr()->in('lt.code', $this->searchCodes)); $or->add($and); $queryBuilder->andWhere($or)->setParameter('substring', '%' . $value . '%'); } }
/** * Define query for findAllProfilesArray() method * * @return \Doctrine\ORM\QueryBuilder */ protected function defineAllProfilesArrayQuery() { $cnd = new \Doctrine\ORM\Query\Expr\Orx(); $cnd->add('p.cms_name = :cmsName'); $cnd->add('p.cms_name = \'\''); return $this->createQueryBuilder()->select('p.profile_id')->addSelect('p.login')->addSelect('p.cms_profile_id')->addSelect('p.cms_name')->andWhere('p.order is null')->andWhere($cnd)->setParameter('cmsName', \XLite\Module\CDev\DrupalConnector\Handler::getInstance()->getCMSName()); }
/** * Prepare certain search condition * * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare * @param array $value Condition data * @param boolean $countOnly "Count only" flag. Do not need to add "order by" clauses if only count is needed. * * @return void */ protected function prepareCndMinQuantity(\Doctrine\ORM\QueryBuilder $queryBuilder, $value, $countOnly) { $cnd = new \Doctrine\ORM\Query\Expr\Orx(); $cnd->add('w.quantityRangeEnd >= :minQty'); $cnd->add('w.quantityRangeEnd = 0'); $queryBuilder->andWhere($cnd)->setParameter('minQty', $value); }
/** * prepareCndAddress * * @param \Doctrine\ORM\QueryBuilder $queryBuilder QueryBuilder instance * @param mixed $value Searchable value * * @return void */ protected function prepareCndAddress(\Doctrine\ORM\QueryBuilder $queryBuilder, $value) { if (!empty($value)) { $cnd = new \Doctrine\ORM\Query\Expr\Orx(); foreach ($this->getAddressSubstringSearchFields() as $field) { $cnd->add($field . ' LIKE :addressPattern'); } $queryBuilder->andWhere($cnd)->setParameter('addressPattern', '%' . $value . '%'); } }