/**
* Build the data query
*
* @return QueryBuilder
*/
public function getDataQuery()
{
$this->buildFilteredQuery();
if ($this->offset !== null) {
$this->currentQueryBuilder->setFirstResult($this->offset);
}
if ($this->length !== null) {
$this->currentQueryBuilder->setMaxResults($this->length);
}
if ($this->orders !== null) {
foreach ($this->orders as $order) {
list($column, $dir) = $order;
$this->currentQueryBuilder->addOrderBy($column->sourceSort, $dir);
}
}
if (isset($this->conf->group)) {
$this->currentQueryBuilder->groupBy($this->conf->group);
}
return $this->currentQueryBuilder;
}
/**
* @test
*/
public function groupByQuotesIdentifierAndDelegatesToConcreteQueryBuilder()
{
$this->connection->quoteIdentifiers(['aField', 'anotherField'])->shouldBeCalled()->willReturnArgument(0);
$this->concreteQueryBuilder->groupBy('aField', 'anotherField')->shouldBeCalled()->willReturn($this->subject);
$this->subject->groupBy('aField', 'anotherField');
}
/**
* Specifies a grouping over the results of the query.
* Replaces any previously specified groupings, if any.
*
* @param mixed,... $groupBy The grouping expression.
*
* @return QueryBuilder This QueryBuilder instance.
*/
public function groupBy(...$groupBy) : QueryBuilder
{
$this->concreteQueryBuilder->groupBy(...$this->quoteIdentifiers($groupBy));
return $this;
}
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
$paramcount = 0;
if (!empty($this->gIDs)) {
$validgids = array();
foreach ($this->gIDs as $gID) {
if ($gID > 0) {
$validgids[] = $gID;
}
}
if (!empty($validgids)) {
$query->innerJoin('p', 'VividStoreProductGroups', 'g', 'p.pID = g.pID and g.gID in (' . implode(',', $validgids) . ')');
if (!$this->groupMatchAny) {
$query->having('count(g.gID) = ' . count($validgids));
}
}
}
switch ($this->sortBy) {
case "alpha":
$query->orderBy('pName', 'ASC');
break;
case "date":
$query->orderBy('pDateAdded', 'DESC');
break;
}
switch ($this->featured) {
case "featured":
$query->andWhere("pFeatured = 1");
break;
case "nonfeatured":
$query->andWhere("pFeatured = 0");
break;
}
if ($this->activeOnly) {
$query->andWhere("pActive = 1");
}
if (is_array($this->cIDs) && !empty($this->cIDs)) {
$query->innerJoin('p', 'VividStoreProductLocations', 'l', 'p.pID = l.pID and l.cID in (' . implode(',', $this->cIDs) . ')');
}
$query->groupBy('p.pID');
if ($this->search) {
$query->andWhere('pName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
}
return $query;
}
public function groupBy($groupBy)
{
$connection = $this->getConnection();
return parent::groupBy($connection->quoteIdentifier($groupBy));
}
/**
* Specifies a grouping over the results of the query.
* Replaces any previously specified groupings, if any.
*
* @param mixed $groupBy The grouping expression.
*
* @return self
*/
public function groupBy($groupBy)
{
$this->qb->groupBy($groupBy);
return $this;
}
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
$paramcount = 0;
if (!empty($this->gIDs)) {
$validgids = array();
foreach ($this->gIDs as $gID) {
if ($gID > 0) {
$validgids[] = $gID;
}
}
if (!empty($validgids)) {
$query->innerJoin('p', 'VividStoreProductGroups', 'g', 'p.pID = g.pID and g.gID in (' . implode(',', $validgids) . ')');
if (!$this->groupMatchAny) {
$query->having('count(g.gID) = ' . count($validgids));
}
}
}
switch ($this->sortBy) {
case "alpha":
$query->orderBy('pName', 'ASC');
break;
case "date":
$query->orderBy('pDateAdded', 'DESC');
break;
case "pricelth":
$query->orderBy('pPrice', 'ASC');
break;
case "pricehtl":
$query->orderBy('pPrice', 'DESC');
break;
case "popular":
$pr = new StoreProductReport();
$pr->sortByPopularity();
$products = $pr->getProducts();
$pIDs = array();
foreach ($products as $product) {
$pIDs[] = $product['pID'];
}
foreach ($pIDs as $pID) {
$query->addOrderBy("pID = ?", 'DESC')->setParameter($paramcount++, $pID);
}
break;
}
switch ($this->featured) {
case "featured":
$query->andWhere("pFeatured = 1");
break;
case "nonfeatured":
$query->andWhere("pFeatured = 0");
break;
}
if (!$this->showOutOfStock) {
$query->andWhere("pQty > 0 OR pQtyUnlim = 1");
}
if ($this->activeOnly) {
$query->andWhere("pActive = 1");
}
if (is_array($this->cIDs) && !empty($this->cIDs)) {
$query->innerJoin('p', 'VividStoreProductLocations', 'l', 'p.pID = l.pID and l.cID in (' . implode(',', $this->cIDs) . ')');
}
$query->groupBy('p.pID');
if ($this->search) {
$query->andWhere('pName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
}
return $query;
}
/**
* @param QueryBuilder $q
* @param array $tables $tables[0] should be primary table
* @param bool $innerJoinTables
* @param null $whereExpression
* @param null $having
*/
protected function applySearchQueryRelationship(QueryBuilder $q, array $tables, $innerJoinTables, $whereExpression = null, $having = null)
{
$primaryTable = $tables[0];
unset($tables[0]);
$joinType = $innerJoinTables ? 'join' : 'leftJoin';
$this->useDistinctCount = true;
$joins = $q->getQueryPart('join');
if (!array_key_exists($primaryTable['alias'], $joins)) {
$q->{$joinType}($primaryTable['from_alias'], MAUTIC_TABLE_PREFIX . $primaryTable['table'], $primaryTable['alias'], $primaryTable['condition']);
foreach ($tables as $table) {
$q->{$joinType}($table['from_alias'], MAUTIC_TABLE_PREFIX . $table['table'], $table['alias'], $table['condition']);
}
if ($whereExpression) {
$q->andWhere($whereExpression);
}
if ($having) {
$q->andHaving($having);
}
$q->groupBy('l.id');
}
}
