public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
$paramcount = 0;
if (isset($this->gID) && $this->gID > 0) {
$query->where('gID = ?')->setParameter($paramcount++, $this->gID);
}
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 ($this->search) {
$query->andWhere('pName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
}
return $query;
}
protected function executeSortBy($column, $direction = 'asc')
{
if (in_array(strtolower($direction), array('asc', 'desc'))) {
$this->query->orderBy($column, $direction);
} else {
throw new \Exception(t('Invalid SQL in order by'));
}
}
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 "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;
}
/**
* Applies operation to data source and returns modified data source.
*
* @param QueryBuilder $src
* @param OperationInterface|SortOperation $operation
* @return QueryBuilder
*/
public function process($src, OperationInterface $operation)
{
$field = $operation->getField();
$order = $operation->getOrder();
$src->orderBy($field, $order);
return $src;
}
/**
* @param QueryBuilder $queryBuilder
* @param SortInterface $sort
* @param Mapping $mapping
*/
public static function sort(QueryBuilder $queryBuilder, SortInterface $sort, Mapping $mapping)
{
$columns = $mapping->map();
foreach ($sort->orders() as $propertyName => $order) {
self::guardColumnExists($columns, $propertyName);
$queryBuilder->orderBy($columns[$propertyName], $order->isAscending() ? Order::ASCENDING : Order::DESCENDING);
}
}
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
$paramcount = 0;
if (isset($this->gID) && $this->gID > 0) {
$query->where('gID = ?')->setParameter($paramcount++, $this->gID);
}
switch ($this->sortBy) {
case "alpha":
$query->orderBy('drName', 'ASC');
break;
}
if ($this->search) {
$query->andWhere('drName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
}
$query->andWhere('drDeleted is NULL');
return $query;
}
/**
* @see \ComPHPPuebla\Doctrine\TableGateway\Specification\QueryBuilderSpecification::match()
*/
public function match(QueryBuilder $qb)
{
if ($this->has('latitude') && $this->has('longitude')) {
$qb->addSelect(<<<SELECT
(6371
* (2 * ATAN(SQRT(SIN(((:latitude - s.latitude) * (PI()/180))/2) * SIN(((:latitude - s.latitude) * (PI()/180))/2)
+ COS(:latitude * (PI()/180)) * COS(s.latitude * (PI()/180))
* SIN(((:longitude - s.longitude) * (PI()/180))/2) * SIN(((:longitude - s.longitude) * (PI()/180))/2)),
SQRT(1-(sin(((:latitude - s.latitude) * (PI()/180))/2) * SIN(((:latitude - s.latitude) * (PI()/180))/2)
+ COS(:latitude * (PI()/180) * COS(s.latitude * (PI()/180)
* SIN(((:longitude - s.longitude) * (PI()/180))/2) * SIN(((:longitude - s.longitude) * (PI()/180))/2))))))))
AS distance
SELECT
);
$qb->orderBy('distance');
$qb->setParameter('latitude', $this->get('latitude'));
$qb->setParameter('longitude', $this->get('longitude'));
}
}
/**
* @return QueryBuilder|null
*/
public function getQueryBuilder()
{
if (is_null($this->queryBuilder)) {
$this->queryBuilder = new QueryBuilder($this->connection);
$this->adaptQueryBuilder($this->queryBuilder);
// Apply filters
$filters = $this->getFilterBuilder()->getCurrentFilters();
foreach ($filters as $filter) {
/* @var AbstractDBALFilterType $type */
$type = $filter->getType();
$type->setQueryBuilder($this->queryBuilder);
$filter->apply();
}
// Apply sorting
if (!empty($this->orderBy)) {
$orderBy = $this->orderBy;
$this->queryBuilder->orderBy($orderBy, $this->orderDirection == 'DESC' ? 'DESC' : 'ASC');
}
}
return $this->queryBuilder;
}
/**
* Specifies an ordering for the query results.
* Replaces any previously specified orderings, if any.
*
* @param string $sort The ordering expression.
* @param string $order The ordering direction.
* @return QueryBuilder This QueryBuilder instance.
*/
public function orderBy($sort, $order = null)
{
$connection = $this->getConnection();
return parent::orderBy($connection->quoteIdentifier($sort), $order);
}
/**
* Conditionally add LIMIT and ORDER BY to a QueryBuilder query.
*
* @param QueryBuilder $query
* @param array $options Additional options:
* - 'limit' (integer): Maximum number of results to return
* - 'order' (string): Field to order by
* - 'direction' (string): ASC or DESC
* - 'contentid' (integer): Filter further by content ID
* - 'id' (integer): Filter by a specific change log entry ID
*/
protected function setLimitOrder(QueryBuilder $query, array $options)
{
if (isset($options['order'])) {
$query->orderBy($options['order'], $options['direction']);
}
if (isset($options['limit'])) {
$query->setMaxResults(intval($options['limit']));
if (isset($options['offset'])) {
$query->setFirstResult(intval($options['offset']));
}
}
}
/**
* 加载数据库结果集
*
* @param bool $multiple 是否加载多行数据
* @return $this|mixed
*/
protected function _loadResult($multiple = false)
{
$this->_dbBuilder->from($this->db()->quoteIdentifier($this->_tableName), $this->db()->quoteIdentifier($this->_objectName));
// 只获取单条记录
if (false === $multiple) {
$this->_dbBuilder->setMaxResults(1);
}
// 默认选择所有字段
$this->_dbBuilder->addSelect($this->_buildSelect());
// 处理排序问题
if (!isset($this->_dbApplied['orderBy']) && !empty($this->_sorting)) {
foreach ($this->_sorting as $column => $direction) {
if (false === strpos($column, '.')) {
// Sorting column for use in JOINs
$column = $this->_objectName . '.' . $column;
}
$this->_dbBuilder->orderBy($column, $direction);
}
}
if (true === $multiple) {
$result = $this->_dbBuilder->execute();
$result->setFetchMode(PDO::FETCH_CLASS, $this->_loadMultiResultFetcherClass(), $this->_loadMultiResultFetcherConstructor());
$this->reset();
return $result->fetchAll();
} else {
$result = $this->_dbBuilder->execute()->fetch();
$this->reset();
if ($result) {
$this->_loadValues($result);
} else {
$this->clear();
}
return $this;
}
}
/**
* @test
*/
public function orderByQuotesIdentifierAndDelegatesToConcreteQueryBuilder()
{
$this->connection->quoteIdentifier('aField')->shouldBeCalled()->willReturnArgument(0);
$this->concreteQueryBuilder->orderBy('aField', null)->shouldBeCalled()->willReturn($this->subject);
$this->subject->orderBy('aField');
}
/**
* {@inheritdoc}
*/
public function orderBy($field, $direction)
{
return $this->queryBuilder->orderBy($field, $direction);
}
/**
* Modify database query for fetching the line time chart data
*
* @param QueryBuilder $query
* @param string $column name
* @param string $tablePrefix
*/
public function modifyTimeDataQuery(&$query, $column, $tablePrefix = 't')
{
// Convert time unitst to the right form for current database platform
$dbUnit = $this->translateTimeUnit($this->unit);
$limit = $this->countAmountFromDateRange($this->unit);
$groupBy = '';
if (isset($filters['groupBy'])) {
$groupBy = ', ' . $tablePrefix . '.' . $filters['groupBy'];
unset($filters['groupBy']);
}
$dateConstruct = 'DATE_FORMAT(' . $tablePrefix . '.' . $column . ', \'' . $dbUnit . '\')';
$query->select($dateConstruct . ' AS date, COUNT(*) AS count')->groupBy($dateConstruct . $groupBy);
$query->orderBy($dateConstruct, 'ASC')->setMaxResults($limit);
}
/**
* Specifies an ordering for the query results.
* Replaces any previously specified orderings, if any.
*
* @param string $sort The ordering expression.
* @param string $order The ordering direction.
*
* @return \OCP\DB\QueryBuilder\IQueryBuilder This QueryBuilder instance.
*/
public function orderBy($sort, $order = null)
{
$this->queryBuilder->orderBy($this->helper->quoteColumnName($sort), $order);
return $this;
}
/**
* Specifies an ordering for the query results.
* Replaces any previously specified orderings, if any.
*
* @param string $fieldName The fieldName to order by. Will be quoted according to database platform automatically.
* @param string $order The ordering direction. No automatic quoting/escaping.
*
* @return QueryBuilder This QueryBuilder instance.
*/
public function orderBy(string $fieldName, string $order = null) : QueryBuilder
{
$this->concreteQueryBuilder->orderBy($this->connection->quoteIdentifier($fieldName), $order);
return $this;
}
protected function executeSortBy($column, $direction = 'asc')
{
$this->query->orderBy($column, $direction);
}
/**
* @param QueryBuilder $builder The query builder
*
* @return QueryBuilder
*/
private function applyOrdering($builder)
{
foreach ($this->ordering as $column => $order) {
$builder->orderBy($column, $order);
}
return $builder;
}
/**
* Specifies an ordering for the query results.
* Replaces any previously specified orderings, if any.
*
* @param string $sort The ordering expression.
* @param string $order The ordering direction.
*
* @return self
*/
public function orderBy($sort, $order = null)
{
$this->qb->orderBy($sort, $order);
return $this;
}
/**
* Adds sorting parameters to the query.
*
* @param QueryBuilder $queryBuilder
* the query
* @param string|null $sortField
* the sort field
* @param boolean|null $sortAscending
* true if sort ascending, false if descending
*/
protected function addSort(QueryBuilder $queryBuilder, $sortField, $sortAscending)
{
if ($sortField !== null) {
$type = $this->definition->getType($sortField);
if ($type === 'many') {
$sortField = $this->definition->getInitialSortField();
}
$order = $sortAscending === true ? 'ASC' : 'DESC';
$queryBuilder->orderBy('`' . $sortField . '`', $order);
}
}
/**
* Adds sorting parameters to the query.
*
* @param QueryBuilder $queryBuilder
* the query
* @param $sortField
* the sort field
* @param $sortAscending
* true if sort ascending, false if descending
*/
protected function addSort(QueryBuilder $queryBuilder, $sortField, $sortAscending)
{
if ($sortField !== null) {
$order = $sortAscending === true ? 'ASC' : 'DESC';
$queryBuilder->orderBy($sortField, $order);
}
}
function gridFiltersExt(\Doctrine\DBAL\Query\QueryBuilder &$dbalQuery, $params, $aColumnMapping)
{
if (is_array($aColumnMapping) && count($aColumnMapping) > 0) {
foreach ($aColumnMapping as $value) {
if ($value["ref"] == $params['sSortBy']) {
if (!$value["table"]) {
$params['sSortBy'] = $value["col"];
} else {
$params['sSortBy'] = $value["table"] . "." . $value["col"];
}
break;
}
}
}
if (is_array($params['aFilters'])) {
foreach ($params['aFilters'] as $oFilter) {
$sFieldname = $oFilter->field;
if (is_array($aColumnMapping) && count($aColumnMapping) > 0) {
foreach ($aColumnMapping as $value) {
if ($value["ref"] == $sFieldname) {
/**
* Update Catalin:
* Exista situatii cand nu am nevoie de tabel.coloana ex cand folosesc functii gen GROUP_CONCAT
*/
if (!$value["table"]) {
$sFieldname = $value["col"];
} else {
$sFieldname = $value["table"] . "." . $value["col"];
}
break;
}
}
}
$sValue = $oFilter->value;
$sCompare = isset($oFilter->comparison) ? $oFilter->comparison : NULL;
$sFilterType = $oFilter->type;
switch ($sFilterType) {
case 'string':
$dbalQuery->andWhere($sFieldname . " like '%{$sValue}%'");
break;
case 'list':
if (strstr($sValue, ',')) {
$aValues = explode(',', $sValue);
$this->db->andWhere_in($sFieldname, $aValues);
} else {
$this->db->andWhere($sFieldname, $sValue);
}
break;
case 'boolean':
$this->db->andWhere($sFieldname, $sValue);
break;
case 'combo':
$this->db->andWhere($sFieldname, $sValue);
break;
case 'numeric':
switch ($sCompare) {
case 'eq':
$dbalQuery->andWhere($sFieldname . '=' . $sValue);
break;
case 'lt':
$dbalQuery->andWhere($sFieldname . '<' . $sValue);
break;
case 'gt':
$dbalQuery->andWhere($sFieldname . '>' . $sValue);
break;
case 'gte':
$dbalQuery->andWhere($sFieldname . '>=' . $sValue);
break;
case 'lte':
$dbalQuery->andWhere($sFieldname . '<=' . $sValue);
break;
}
break;
case 'date':
switch ($sCompare) {
case 'eq':
$dbalQuery->andWhere("DATE_FORMAT({$sFieldname}, '%Y-%m-%d')='" . date('Y-m-d', strtotime($sValue)) . "'");
break;
case 'lt':
$dbalQuery->andWhere("DATE_FORMAT({$sFieldname}, '%Y-%m-%d')<='" . date('Y-m-d', strtotime($sValue)) . "'");
break;
case 'gt':
$dbalQuery->andWhere("DATE_FORMAT({$sFieldname}, '%Y-%m-%d')>='" . date('Y-m-d', strtotime($sValue)) . "'");
break;
}
break;
}
}
}
if (!$params['bIsExport']) {
$dbalQuery->orderBy($params['sSortBy'], $params['sDir']);
$dbalQuery->setFirstResult($params['sStart']);
$dbalQuery->setMaxResults($params['sLimit']);
} else {
//Limita maxima de inregistrari
$dbalQuery->setMaxResults(50000);
}
}
