/**
* 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);
}
/**
* 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;
}
/**
* 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);
}
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 . '%');
}
}
