/**
* @param QueryBuilder $queryBuilder
* @param Parameter $parameters
* @return QueryBuilder
*/
protected function generateQueryParamFilter(QueryBuilder $queryBuilder, Parameters $parameters)
{
if (isset($parameters['query']) && is_array($parameters['query'])) {
$i = 1;
foreach ($parameters['query'] as $query) {
$type = '=';
if ($query['type'] == 'like') {
$type = 'LIKE';
}
$whereString = sprintf('row.%s %s :%sValue%d', $query['field'], $type, $query['field'], $i);
if (!isset($query['where']) || $query['where'] == 'and') {
$queryBuilder->andWhere($whereString);
} else {
if ($query['where'] == 'or') {
$queryBuilder->orWhere($whereString);
}
}
$queryBuilder->setParameter(sprintf('%sValue%d', $query['field'], $i), $query['value']);
$i++;
}
}
if (isset($parameters['orderBy']) && is_array($parameters['orderBy'])) {
foreach ($parameters['orderBy'] as $orderKey => $orderDirection) {
$queryBuilder->addOrderBy('row.' . $orderKey, strtoupper($orderDirection));
}
}
return $queryBuilder;
}
public function getNuArtefatoNuDigital(\Doctrine\ORM\QueryBuilder &$queryBuilder, \Core_Dto_Search $dto)
{
if ($dto->getNuArtefato()) {
$nuArtefato = str_replace(' ', '', str_replace('.', '', str_replace('/', '', str_replace('-', '', $dto->getNuArtefato()))));
$queryBuilder->orWhere('vwca.nuDigital like :nuDigital')->setParameter('nuDigital', '%' . $dto->getNuArtefato() . '%');
$queryBuilder->orWhere('vwca.nuArtefato like :nuArtefato')->setParameter('nuArtefato', '%' . $nuArtefato . '%');
}
}
/**
* {@inheritdoc}
*/
public function restrict($expression, $condition = DataSourceInterface::CONDITION_AND)
{
switch ($condition) {
case DataSourceInterface::CONDITION_AND:
$this->queryBuilder->andWhere($expression);
break;
case DataSourceInterface::CONDITION_OR:
$this->queryBuilder->orWhere($expression);
break;
}
}
private function addQueryFilter(QueryBuilder $query, $filter)
{
$expr = $query->expr();
/*$query->orWhere($expr->eq('entity.id', ':filter_int'));*/
$query->orWhere($expr->like('entity.code', ':filter_str'));
$query->orWhere($expr->like('entity.oName', ':filter_str'));
$query->orWhere($expr->like('entity.dName', ':filter_str'));
/*$query->setParameter('filter_int', $filter);*/
$query->setParameter('filter_str', '%' . $filter . '%');
return $query;
}
/**
* Quick search
*
* @return \ZfTable\Source\DoctrineQueryBuilder
*/
protected function search()
{
if ($search = $this->getParamAdapter()->getQuickSearch()) {
foreach ($this->getTable()->getHeaders() as $k => $v) {
$column = isset($v['column']) ? $v['column'] : $k;
if (isset($v['tableAlias'])) {
$this->query->orWhere($this->query->expr()->like($v['tableAlias'] . '.' . $column, "'%" . $search . "%'"));
}
}
}
return $this;
}
private function whereToPublish(QueryBuilder $builder)
{
$builder->where('r.published = :published');
$builder->setParameter('published', false);
$builder->orWhere('p.modified = :modified');
$builder->setParameter('modified', true);
return $this;
}
/**
* Adds a new WHERE or HAVING restriction depends on the given parameters.
*
* @param mixed $restriction The restriction to add.
* @param string $condition The condition.
* Can be FilterUtility::CONDITION_OR or FilterUtility::CONDITION_AND.
* @param bool $isComputed Specifies whether the restriction should be added to the HAVING part of a query.
*/
public function addRestriction($restriction, $condition, $isComputed = false)
{
$restriction = $this->qbTools->replaceAliasesWithFields($restriction);
if ($condition === FilterUtility::CONDITION_OR) {
if ($isComputed) {
$this->qb->orHaving($restriction);
} else {
$this->qb->orWhere($restriction);
}
} else {
if ($isComputed) {
$this->qb->andHaving($restriction);
} else {
$this->qb->andWhere($restriction);
}
}
}
/**
* Adds a new WHERE or HAVING restriction depends on the given parameters.
*
* @param mixed $restriction The restriction to add.
* @param string $condition The condition.
* Can be FilterUtility::CONDITION_OR or FilterUtility::CONDITION_AND.
* @param bool $isComputed Specifies whether the restriction should be added to the HAVING part of a query.
*/
public function addRestriction($restriction, $condition, $isComputed = false)
{
if ($this->fixComparison($restriction, $condition)) {
return;
}
if ($condition === FilterUtility::CONDITION_OR) {
if ($isComputed) {
$this->qb->orHaving($restriction);
} else {
$this->qb->orWhere($restriction);
}
} else {
if ($isComputed) {
$this->qb->andHaving($restriction);
} else {
$this->qb->andWhere($restriction);
}
}
}
/**
* Apply to given query builder object additional conditions
* for integrate activity lists from inheritance targets
*
* @param QueryBuilder $qb
* @param string $entityClass
* @param integer $entityId
*/
public function applyInheritanceActivity(QueryBuilder $qb, $entityClass, $entityId)
{
if (!$this->hasInheritances($entityClass)) {
return;
}
$inheritanceTargets = $this->getInheritanceTargetsRelations($entityClass);
foreach ($inheritanceTargets as $key => $inheritanceTarget) {
$alias = 'ta_' . $key;
$qb->leftJoin('activity.' . $inheritanceTarget['targetClassAlias'], $alias);
$qb->orWhere($qb->expr()->andX($qb->expr()->andX($qb->expr()->in($alias . '.id', $this->getSubQuery($inheritanceTarget['targetClass'], $inheritanceTarget['path'], $entityId, $key)->getDQL()))));
}
}
/**
* Multiple filtering
*
* @param array $rules array of rules fore multiple filtering
* @param array $options array of search options
*
* @return void
*/
protected function _multiFilter($rules, $options = array())
{
$boolean = strtoupper($options['boolean']);
foreach ($rules as $rule) {
if ($boolean == 'OR') {
$this->_qb->orWhere($this->getService()->getAlias() . '.' . $rule['field'] . ' ' . str_replace('?', ':' . $rule['field'], $this->_operator[$rule['expression']]));
} else {
$this->_qb->andWhere($this->getService()->getAlias() . '.' . $rule['field'] . ' ' . str_replace('?', ':' . $rule['field'], $this->_operator[$rule['expression']]));
}
$this->_qb->setParameter($rule['field'], $this->_setWildCardInValue($rule['expression'], $rule['value']));
}
}
/**
* @param \Doctrine\ORM\Query|\Doctrine\ORM\QueryBuilder $query
* @param array $array
* @param string $contiene
* @return \Doctrine\ORM\Query|\Doctrine\ORM\QueryBuilder
*/
public function consultaContiene($query, $array, $contiene)
{
if ($contiene != "") {
$fields = array_keys($this->getClassMetadata()->fieldNames);
$alias = $query->getRootAlias();
$count = 0;
foreach ($array as $field) {
$fieldMapping = $this->getClassMetadata()->getFieldForColumn($field);
$where = sprintf("UPPER(%s.%s) LIKE :condicion", $alias, $fieldMapping);
if ($count == 0) {
$query->andWhere($where);
} else {
$query->orWhere($where);
}
$count++;
}
$query->setParameter("condicion", "%" . strtoupper($contiene) . "%");
}
return $query;
}
/**
* {@inheritdoc}
*/
public function search(QueryBuilder $queryBuilder, $searchString)
{
$alias = $queryBuilder->getRootAliases();
$queryBuilder->orWhere('_client.name LIKE :search')->orWhere($alias[0] . '.status LIKE :search')->orWhere($alias[0] . '.total LIKE :search')->setParameter('search', "%{$searchString}%");
}
protected function buildQueryAutoCompleteTerm(array $fields, $term)
{
$qb = new QueryBuilder($this->_em);
$qb->select('e')->from($this->_entityName, 'e');
if (count($fields) > 0 && $term != "") {
// term in quotes bedeutet wörtliche suche
if (\Psc\Preg::match($term, '/^\\s*("|\')(.*)("|\')\\s*$/', $m)) {
$term = $m[2];
foreach ($fields as $field) {
$qb->orWhere('e.' . $field . ' = ?1 ');
}
$qb->setParameter(1, $term);
} else {
/* normale suche */
foreach ($fields as $field) {
$qb->orWhere('e.' . $field . ' = ?1 ');
for ($i = 2; $i <= 4; $i++) {
$qb->orWhere('e.' . $field . ' LIKE ?' . $i);
}
}
$qb->setParameter(1, $term);
$qb->setParameter(2, '%' . $term);
$qb->setParameter(3, '%' . $term . '%');
$qb->setParameter(4, $term . '%');
}
}
return $qb;
}
/**
* Create a LIKE comparison with entity texts colunms.
*
* @param string $pattern
* @param DoctrineORMQueryBuilder $qb
* @param string $alias
*/
protected function classicLikeComparison($pattern, \Doctrine\ORM\QueryBuilder &$qb, $alias = "obj")
{
/*
* get fields needed for a search
* query
*/
$metadatas = $this->_em->getClassMetadata($this->getEntityName());
$criteriaFields = [];
$cols = $metadatas->getColumnNames();
foreach ($cols as $col) {
$field = $metadatas->getFieldName($col);
$type = $metadatas->getTypeOfField($field);
if (in_array($type, $this->searchableTypes) && $field != 'folder' && $field != 'childrenOrder' && $field != 'childrenOrderDirection') {
$criteriaFields[$field] = '%' . strip_tags($pattern) . '%';
}
}
foreach ($criteriaFields as $key => $value) {
$qb->orWhere($qb->expr()->like($alias . '.' . $key, $qb->expr()->literal($value)));
}
}
/**
* {@inheritdoc}
*/
public function orWhere($where)
{
$this->queryBuilder->orWhere($where);
return $this;
}
/**
* Applies filtering to the query and calls back the custom filtering function, if required.
*
* @param QueryBuilder $qb The query builder
* @param ManagerFilter $filter The query filter
*/
protected function applyFiltering(QueryBuilder $qb, ManagerFilter $filter)
{
if ($filter->getFilter() !== null && $filter->getFilterField() !== null) {
$aOrWhereFields = array();
if (is_array($filter->getFilterField())) {
foreach ($filter->getFilterField() as $field) {
$aOrWhereFields[] = "LOWER(q." . $field . ") LIKE :filter";
}
} else {
$aOrWhereFields[] = "LOWER(q." . $filter->getFilterField() . ") LIKE :filter";
}
foreach ($aOrWhereFields as $or) {
$qb->orWhere($or);
}
$qb->setParameter("filter", "%" . strtolower($filter->getFilter()) . "%");
}
if ($filter->getFilterCallback() !== null) {
call_user_func($filter->getFilterCallback(), $qb);
}
}
public function handleSearch(Builder $qb, $query = null)
{
if (!isset($query)) {
$query = $this->getRequest()->getQuery('query');
}
$filters = ['attributeDesc'];
if ($query) {
$where = [];
$params = [];
$i = 0;
foreach ($filters as $condition) {
if (isset($query[$condition])) {
if (is_array($query[$condition])) {
foreach ($query[$condition] as $criteria => $values) {
$qb->innerJoin('e.attributes', 'v' . $i);
$qb->innerJoin('v' . $i . '.attribute', 'a' . $i);
$qb->andWhere('a' . $i . '= 1');
switch ($condition) {
case 'attributeDesc':
$expr = $qb->expr();
$andX = $expr->andX();
$where["desc_{$i}"] = "%{$criteria}%";
$andX->add($expr->like("a" . $i . ".attributeDesc", ":desc_{$i}"));
if (is_array($values)) {
$j = 0;
foreach ($values as $value) {
$where["value_{$j}"] = "%{$value}%";
$andX->add($expr->like("v" . $i . ".value", ":value_{$j}"));
$j++;
}
} else {
$where["value_{$i}"] = "%{$values}%";
$andX->add($expr->like("v" . $i . ".value", ":value_{$i}"));
}
$qb->orWhere($andX);
$i++;
break;
}
}
}
} elseif ("" !== $query[$condition]) {
$qb->innerJoin('e.attributes', 'v');
$qb->innerJoin('v.attribute', 'a');
switch ($condition) {
case 'attributeDesc':
$where['attributeDesc'] = "%{$query[$condition]}%";
$qb->andWhere("a.attributeDesc LIKE :attributeDesc");
break;
}
}
}
if ($where) {
foreach ($where as $key => $value) {
$qb->setParameter($key, $value);
}
}
}
$qb->andWhere('e.active = 1');
return $qb;
}
/**
* método que adiciona filtro para a pesquisa da caixa de minutas
* @param \Doctrine\ORM\QueryBuilder &$queryBuilder
* @param Core_Dto_Search $dto
*/
protected function addWhere(\Doctrine\ORM\QueryBuilder &$queryBuilder, \Core_Dto_Search $dto)
{
$isDate = FALSE;
$data = explode('/', $dto->getDataSearch());
if (count($data) == 3) {
if (checkdate($data[1], $data[0], $data[2])) {
$isDate = TRUE;
$newDate = $data[2] . '-' . $data[1] . '-' . $data[0];
$queryBuilder->andWhere('vcm.dataCriacao = :data')->setParameter('data', $newDate);
$queryBuilder->orWhere('vcm.prazo = :data')->setParameter('data', $newDate);
}
}
if (!$isDate && $dto->getDataSearch() != '') {
$query = mb_strtolower($dto->getDataSearch(), 'UTF-8');
$queryBuilder->andWhere('(((LOWER(vcm.tipo) like :query)');
$queryBuilder->orWhere('(LOWER(vcm.origem) like :query)');
$queryBuilder->orWhere('(LOWER(vcm.assunto) like :query)');
$queryBuilder->orWhere('(LOWER(vcm.autor) like :query)');
$queryBuilder->orWhere('(LOWER(vcm.status) like :query)))');
$queryBuilder->setParameter('query', '%' . $query . '%');
$queryBuilder->andWhere('vcm.sqStatusArtefato <> :sqStatusArtefato54')->setParameter('sqStatusArtefato54', \Core_Configuration::getSgdoceStatusExcluida());
}
}
/**
* Adds a new condition to the given QueryBuilder.
*
* @param QueryBuilder $qb
* @param string $field The field to be used for the condition
* @param string $op The operator to be used for the condition.
* @param string $filter
*/
private function _addCondition(QueryBuilder $qb, $field, $op, $filter)
{
$pn = $this->_getNewParameterNumber();
$pnUse = '?' . $pn;
$func = 'where';
if (strpos($field, '(') !== false) {
$func = 'having';
}
$havingPart = $qb->getDQLPart('having');
$wherePart = $qb->getDQLPart('where');
if ($func == 'having' && !empty($havingPart)) {
$func = 'andHaving';
} elseif ($func == 'where' && !empty($wherePart)) {
$func = 'andWhere';
}
switch (strtolower($op)) {
case 'sqlexp':
$qb->{$func}($filter);
break;
case 'isnull':
$qb->{$func}($field . ' IS NULL ');
break;
case 'isnotnull':
$qb->{$func}($field . ' IS NOT NULL ');
break;
case 'empty':
$expr = $qb->expr()->eq($field, '');
$qb->{$func}($expr);
break;
case 'equal':
case '=':
$expr = $qb->expr()->eq($field, $pnUse);
$qb->{$func}($expr);
$qb->setParameter($pn, $filter);
break;
case 'regex':
$qb->{$func}($field . " REGEXP " . $pnUse);
$qb->setParameter($pn, $filter);
break;
case 'rlike':
$expr = $qb->expr()->like($field, $pnUse);
$qb->{$func}($expr);
$qb->setParameter($pn, $filter . "%");
break;
case 'llike':
$expr = $qb->expr()->like($field, $pnUse);
$qb->{$func}($expr);
$qb->setParameter($pn, "%" . $filter);
break;
case '>=':
$expr = $qb->expr()->gte($field, $pnUse);
$qb->{$func}($expr);
$qb->setParameter($pn, $filter);
break;
case '>':
$expr = $qb->expr()->gt($field, $pnUse);
$qb->{$func}($expr);
$qb->setParameter($pn, $filter);
break;
case '<>':
case '!=':
$expr = $qb->expr()->neq($field, $pnUse);
$qb->{$func}($expr);
$qb->setParameter($pn, $filter);
break;
case '<=':
$expr = $qb->expr()->lte($field, $pnUse);
$qb->{$func}($expr);
$qb->setParameter($pn, $filter);
break;
case '<':
$expr = $qb->expr()->lt($field, $pnUse);
$qb->{$func}($expr);
$qb->setParameter($pn, $filter);
break;
case 'in':
$filter = explode(',', $filter);
$qb->{$func}($qb->expr()->in($field, $filter));
break;
case '&':
case 'and':
case 'AND':
case 'flag':
case 'FLAG':
$qb->{$func}($field . " & " . $pnUse . " <> 0");
$qb->setParameter($pn, $filter);
break;
case 'range':
$pn2 = $this->_getNewParameterNumber();
$pn2Use = '?' . $pn2;
$expr = $qb->expr()->between($field, $pnUse, $pn2Use);
$start = substr($filter, 0, strpos($filter, '<>'));
$end = substr($filter, strpos($filter, '<>') + 2);
$qb->{$func}($expr);
$qb->setParameter($pn, $start);
$qb->setParameter($pn2, $end);
break;
case '||':
$expr = $qb->expr()->like($field, $pnUse);
$qb->orWhere($expr);
$qb->setParameter($pn, "%" . $filter . "%");
break;
case 'like':
default:
$expr = $qb->expr()->like($field, $pnUse);
$qb->{$func}($expr);
$qb->setParameter($pn, "%" . $filter . "%");
break;
}
return $this;
}
/**
* Adiciona parametros para realizar o filtro da grid
* @param \Doctrine\ORM\QueryBuilder $query
* @param \Core_Dto_Abstract $dto
*/
public function addWhere(\Doctrine\ORM\QueryBuilder $query, \Core_Dto_Abstract $dto)
{
$inputs = array('tp.sqTipoPessoa' => $dto->getSqTipoPessoa(), 'pf.nuCpf' => $dto->getNuCpf(), 'pj.nuCnpj' => $dto->getNuCnpj(), 'p.stRegistroAtivo' => $dto->getStRegistroAtivo());
switch ($dto->getSqTipoPessoa()) {
case srvPessoa::SQ_TIPO_PESSOA_FISICA:
$inputs['p.noPessoa'] = $dto->getNoPessoaFisica();
break;
case srvPessoa::SQ_TIPO_PESSOA_JURIDICA:
$inputs['p.noPessoa'] = $dto->getNoPessoaJuridica();
$inputs['nj.sqNaturezaJuridicaPai'] = $dto->getSqNaturezaJuridicaPai();
$inputs['nj.sqNaturezaJuridica'] = $dto->getSqNaturezaJuridica();
break;
default:
$inputs['p.noPessoa'] = $dto->getNoPessoaSemClass();
break;
}
$countParams = 0;
foreach ($inputs as $field => $value) {
if ($value != '') {
if (strstr($field, 'sq') || strstr($field, 'nu') || strstr($field, 'st')) {
if ($field == 'tp.sqTipoPessoa' && $value == srvPessoa::SEM_CLASSIFICACAO) {
$query->andWhere($this->_em->createQueryBuilder()->expr()->in('tp.sqTipoPessoa', ':expre'))->setParameter('expre', array(srvPessoa::SQ_TIPO_PESSOA_FISICA, srvPessoa::SQ_TIPO_PESSOA_JURIDICA));
} else {
$query->andWhere($field . ' = ?' . $countParams)->setParameter($countParams, \Zend_Filter::filterStatic($value, 'Digits'));
}
} else {
$expre = $query->expr()->lower($query->expr()->trim($field));
$value = "%" . mb_strtolower(trim($value), 'UTF-8') . "%";
$query->andWhere($query->expr()->like('clear_accentuation(' . $expre . ')', $query->expr()->literal($this->translate($value))));
if ($dto->getSqTipoPessoa() == srvPessoa::SEM_CLASSIFICACAO || $dto->getSqTipoPessoa() == srvPessoa::SQ_TIPO_PESSOA_JURIDICA) {
$expre = $query->expr()->lower($query->expr()->trim('pj.noFantasia'));
$query->orWhere($query->expr()->like('clear_accentuation(' . $expre . ')', $query->expr()->literal($this->translate($value))));
}
}
$countParams++;
}
}
}
/**
* @param \Doctrine\ORM\QueryBuilder $queryBuilder
* @param $filter
*
* @return QueryBuilder
*/
private function processFilter(QueryBuilder $queryBuilder, $filter)
{
if (isset($filter['searchString']) && '' !== $filter['searchString']) {
$locationExpr = $queryBuilder->expr()->orX();
foreach (explode(' ', $filter['searchString']) as $keywordIndex => $keyword) {
$queryParameter = 'searchString' . $keywordIndex;
$queryBuilder->andWhere('event.title LIKE :' . $queryParameter);
$queryBuilder->setParameter($queryParameter, '%' . trim($keyword) . '%');
$locationExpr->add($queryBuilder->expr()->like('event.city', ':' . $queryParameter));
}
if (!isset($filter['lat']) && !isset($filter['long'])) {
$queryBuilder->orWhere($locationExpr);
}
}
if (isset($filter['eventIds'])) {
$queryBuilder->andWhere('event.id IN (:eventIds)');
$queryBuilder->setParameter('eventIds', $filter['eventIds']);
}
if (isset($filter['isTopEvent'])) {
$queryBuilder->andWhere('event.isTopEvent = :isTopEvent');
$queryBuilder->setParameter('isTopEvent', $filter['isTopEvent']);
}
if (isset($filter['categories']) && '' !== $filter['categories']) {
$queryBuilder->leftJoin('event.categories', 'eventCategories');
$queryBuilder->andWhere('eventCategories.id IN (:categories)');
$queryBuilder->setParameter('categories', $filter['categories']);
}
if (isset($filter['dateFrom']) && '' !== $filter['dateFrom']) {
/** @var DateTime $dateFrom */
$dateFrom = $filter['dateFrom'];
$queryBuilder->andWhere('event.startDate >= :startDate');
$queryBuilder->setParameter('startDate', $dateFrom->format('Y-m-d'));
}
if (isset($filter['dateFrom']) && '' !== $filter['dateFrom'] && (isset($filter['dateTo']) && '' !== $filter['dateTo'])) {
/** @var DateTime $dateTo */
$dateTo = $filter['dateTo'];
$queryBuilder->andWhere('event.startDate <= :startEnd');
$queryBuilder->setParameter('startEnd', $dateTo->format('Y-m-d'));
} elseif (isset($filter['dateTo']) && '' !== $filter['dateTo']) {
/** @var DateTime $dateTo */
$dateTo = $filter['dateTo'];
$queryBuilder->andWhere('event.startDate <= :startEnd');
$queryBuilder->setParameter('startEnd', $dateTo->format('Y-m-d'));
}
if (isset($filter['lat']) && isset($filter['long'])) {
$queryBuilder->addSelect('( 3959 * acos(cos(radians(:latitude)) ' . '* cos(radians(event.latitude)) ' . '* cos(radians(event.longitude) ' . '- radians(:longitude)) ' . '+ sin(radians(:latitude)) ' . '* sin(radians(event.latitude)))) AS distance');
$queryBuilder->setParameter('latitude', $filter['lat']);
$queryBuilder->setParameter('longitude', $filter['long']);
$queryBuilder->having('distance <= :area');
$queryBuilder->setParameter('area', $filter['area']);
$queryBuilder->orderBy('distance', 'ASC');
} else {
$queryBuilder->orderBy('event.startDate', 'ASC');
}
if (isset($filter['country'])) {
$queryBuilder->andWhere('event.country = :country');
$queryBuilder->setParameter('country', $filter['country']);
}
return $queryBuilder;
}
/**
* Adiciona condição
* @param \Doctrine\ORM\QueryBuilder $queryBuilder
* @param \Core_Dto_Search $dto
*/
protected function addWhere(\Doctrine\ORM\QueryBuilder &$queryBuilder, \Core_Dto_Search $dto)
{
$isDate = FALSE;
$dataSearch = strtotime($dto->getDataSearch());
if (checkdate(date('m', $dataSearch), date('d', $dataSearch), date('Y', $dataSearch))) {
$isDate = TRUE;
$newDate = date('Y-m-d', $dataSearch);
$queryBuilder->andWhere('vcm.dataCriacao = :data')->setParameter('data', $newDate);
$queryBuilder->orWhere('vcm.prazo = :data')->setParameter('data', $newDate);
}
if (!$isDate && $dto->getDataSearch() != '') {
$query = mb_strtolower($dto->getDataSearch(), 'UTF-8');
$queryBuilder->andWhere('(LOWER(vcm.tipo) like :query)');
$queryBuilder->orWhere('(LOWER(vcm.origem) like :query)');
$queryBuilder->orWhere('(LOWER(vcm.assunto) like :query)');
$queryBuilder->orWhere('(LOWER(vcm.autor) like :query)');
$queryBuilder->setParameter('query', '%' . $query . '%');
}
}
/**
* @param QueryBuilder $qb
* @param array $params
*
* @return array
* @throws \Exception
*/
public function getDatatables(QueryBuilder $qb, array $params)
{
if ($this->getAColumns() != '' && $this->getAColumnsArray() != '') {
if ($_GET['sSearch'] != "") {
for ($i = 0; $i < count($this->getAColumns()); $i++) {
if ($this->getAColumns()[$i]['type'] == "string") {
$qb->orWhere("LOWER(remove_accents(" . $this->getAColumns()[$i]['campo'] . ")) LIKE LOWER(remove_accents(:busca" . $i . "))")->setParameter("busca" . $i, '%' . $params['sSearch'] . '%');
} else {
if (is_int($params['sSearch'])) {
$qb->orWhere($this->getAColumns()[$i]['campo'] . " = '" . $params['sSearch'] . "'");
}
}
}
}
// Select total
$stmt = $this->getEntityManager()->getConnection()->prepare("SELECT count(a) FROM (:sql) AS a")->setParameter("sql", $this->get_raw_sql($qb));
$stmt->execute();
if (isset($params['iSortCol_0'])) {
$primeiro = true;
for ($i = 0; $i < intval($params['iSortingCols']); $i++) {
if ($params['bSortable_' . intval($params['iSortCol_' . $i])] == "true") {
if ($primeiro) {
$primeiro = false;
$qb->orderBy($this->getAColumns()[intval($params['iSortCol_' . $i])]['campo'], $params['sSortDir_' . $i]);
} else {
$qb->add($this->getAColumns()[intval($params['iSortCol_' . $i])]['campo'], $params['sSortDir_' . $i]);
}
}
}
}
for ($i = 0; $i < count($this->getAColumns()); $i++) {
if ($_GET['bSearchable_' . $i] == "true" && $params['sSearch_' . $i] != '') {
if ($this->getAColumns()[$i]['type'] == "string") {
$qb->orWhere("LOWER(remove_accents(" . $this->getAColumns()[$i]['campo'] . ")) LIKE LOWER(remove_accents(:busca" . $i . "))")->setParameter("busca" . $i, '%' . $params['sSearch_' . $i] . '%');
} else {
if (is_int($params['sSearch'])) {
$qb->andWhere($this->getAColumns()[$i]['campo'] . " = '" . $params['sSearch_' . $i] . "'");
}
}
}
}
$qb->setMaxResults($params['iDisplayLength']);
$qb->setFirstResult($params['iDisplayStart']);
$rows = array();
foreach ($qb->getQuery()->getResult() as $key => $value) {
$aRow = $value->toArray();
$row = array();
for ($i = 0; $i < count($this->getAColumns()); $i++) {
if (isset($this->getAColumnsArray()[$i]['html'])) {
$html = $this->getAColumnsArray()[$i]['html'];
foreach ($aRow as $key => $value) {
$html = str_replace("{" . $key . "}", $value, $html);
}
$row[] = $html;
} else {
$row[] = $aRow[$this->getAColumnsArray()[$i]['campo']];
}
}
$rows[] = $row;
}
$total = $stmt->fetchAll()[0]['count'];
$output = array("sEcho" => $params['sEcho'], "iTotalRecords" => $total, "iTotalDisplayRecords" => $total, "iDisplayLength" => $params['iDisplayLength'], "aaData" => $rows);
return $output;
} else {
throw new \Exception("The value(getAColumns, getAColumnsArray) is required", 1);
}
}
public function handleSearch(Builder $qb)
{
$query = $this->getRequest()->getQuery();
$filters = ['description'];
if ($query) {
$where = [];
$params = [];
foreach ($filters as $condition) {
if (isset($query[$condition]) && "" !== $query[$condition]) {
switch ($condition) {
case 'description':
$where['description'] = "%{$query[$condition]}%";
$qb->andWhere("e.description LIKE :description");
$qb->orWhere("e.name LIKE :description");
break;
}
}
}
if ($where) {
foreach ($where as $key => $value) {
$qb->setParameter($key, $value);
}
}
}
$qb->andWhere('e.active = 1');
return $qb;
}
private static function likeExpr(QueryBuilder $qb, $field, $value)
{
$qb->orWhere($qb->expr()->like(sprintf('LOWER(a.%s)', $field), $qb->expr()->literal($value)));
}
/**
* @param QueryBuilder $qb
* @param array $value
*/
protected function addWhere(QueryBuilder $qb, $value)
{
if (isset($value['where'])) {
if (isset($value['where']['and'])) {
foreach ((array) $value['where']['and'] as $where) {
$qb->andWhere($where);
}
}
if (isset($value['where']['or'])) {
foreach ((array) $value['where']['or'] as $where) {
$qb->orWhere($where);
}
}
}
}
/**
* @param QueryBuilder $queryBuilder
* @param Request $request
* @param bool $or
*/
private function prepareParams(QueryBuilder $queryBuilder, Request $request, $or)
{
$firstQuery = true;
if (!empty($request->query->get('search'))) {
$searchItems = explode(',', $request->query->get('search'));
foreach ($searchItems as $item) {
$query = explode(':', $item);
if ($firstQuery) {
$queryBuilder->where('R.' . $query[0] . " " . $query[1] . ' :' . $query[0]);
$firstQuery = false;
} else {
if ($or) {
$queryBuilder->orWhere('R.' . $query[0] . " " . $query[1] . ' :' . $query[0]);
} else {
$queryBuilder->andWhere('R.' . $query[0] . " " . $query[1] . ' :' . $query[0]);
}
}
}
$searchItems = explode(',', $request->query->get('search'));
foreach ($searchItems as $item) {
$parameter = explode(':', $item);
$queryBuilder->setParameter($parameter[0], $parameter[2]);
}
if (!empty($request->query->get('orderBy'))) {
$order = explode(',', $request->query->get('orderBy'));
$queryBuilder->orderBy('R.' . $order[0], $order[1]);
}
}
}
