public function findDocumentoSemImagem($limit = 100)
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping($this->_em);
$rsm->addScalarResult('sq_artefato', 'sqArtefato', 'integer');
/**
* somente documento que não possua vinculo com ninguem
*/
$strQuery = sprintf('SELECT DISTINCT t.*
FROM(SELECT a.sq_artefato
FROM artefato AS a
LEFT JOIN (
SELECT DISTINCT ON (ai.sq_artefato)
ai.sq_artefato,
ai.sq_artefato_imagem
FROM artefato_imagem ai
ORDER BY ai.sq_artefato, ai.dt_operacao DESC, ai.sq_artefato_imagem DESC
) uia ON a.sq_artefato = uia.sq_artefato
LEFT JOIN tipo_artefato_assunto AS taa USING(sq_tipo_artefato_assunto)
LEFT JOIN artefato_processo AS ap ON a.sq_artefato = ap.sq_artefato
LEFT JOIN tmp_artefato_migration as tmp ON a.sq_artefato = tmp.sq_artefato
WHERE taa.sq_tipo_artefato = %1$d
AND ap.sq_artefato IS NULL
AND uia.sq_artefato_imagem IS NULL
AND tmp.sq_artefato IS NULL
) t
JOIN vw_imagem_sgdoc_fisico AS isf USING(sq_artefato)
ORDER BY sq_artefato DESC
LIMIT %2$d', \Core_Configuration::getSgdoceTipoArtefatoDocumento(), $limit);
return $this->_em->createNativeQuery($strQuery, $rsm)->useResultCache(false)->getArrayResult();
}
private function _fetchPaginationNativeQuery($params, \Doctrine\ORM\NativeQuery $nQuery)
{
$strQuery = $nQuery->getSQL();
# encapsula a consulta enviada numa subconsulta
# para permitir a paginacao e limitacao do resultado
# sem necessitar alterar a query enviada
$improvedQuery = sprintf('SELECT * FROM (%s) AS foo ', $strQuery);
$improvedQueryCount = sprintf('SELECT count(1) total FROM (%s) C', $improvedQuery);
# se necesario, aplica ordenacao
if (isset($params['order'])) {
foreach ($params['order'] as $order) {
$orderBy[] = sprintf('%s %s', $order['sort'], $order['order']);
}
if (!empty($orderBy)) {
$improvedQuery .= sprintf(' %s %s', self::T_ORDER_BY, implode(', ', $orderBy));
}
}
/**
* Cria uma query para recupera o total geral de registros
*/
$rsmCount = new \Doctrine\ORM\Query\ResultSetMapping($this->_em);
$rsmCount->addScalarResult('total', 'total', 'integer');
$nativeQueryCount = $this->_em->createNativeQuery($improvedQueryCount, $rsmCount);
foreach ($nQuery->getParameters() as $key => $value) {
$nativeQueryCount->setParameter(":{$key}", $value);
}
$improvedQuery .= sprintf(' %1$s %4$d %2$s %3$d', self::T_LIMIT, self::T_OFFSET, $params['iDisplayStart'], $params['iDisplayLength']);
$nQuery->setSQL($improvedQuery);
$result['data'] = $nQuery->getResult();
$result['total'] = $nativeQueryCount->getSingleScalarResult();
return $result;
}
public function getConsumoAcumuladoGeneral($month, $anno, $id_servicio)
{
$em = $this->getEntityManager();
$fecha_inicial = date("Y-m-d", strtotime($anno . '/' . $month . '/01'));
$fecha_final = date("Y-m-d", strtotime($anno . '/' . $month . '/31'));
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('cantidad', 'cantidad');
$rsm->addScalarResult('cantidadHP', 'cantidadHP');
$query = $em->createNativeQuery('Select SUM(consumo) as cantidad, SUM(consumo_horariopico) as cantidadHP
FROM lectura_diaria_servicio
Where (id_servicio =:servicio) and (fecha >=:fecha_inicial) and (fecha <=:fecha_final)
Group by id_servicio', $rsm);
/*$query->select('registro,SUM(registro.plan)')
->from('sisconeeAppBundle:LecturaDiariaServicio', 'registro')
->where('registro.idServicio = ?1')
->andWhere('registro.fecha >= ?2')
->andWhere('registro.fecha <= ?3')
->groupBy('registro.id')*/
$query->setParameters(array('servicio' => $id_servicio, 'fecha_inicial' => $fecha_inicial, 'fecha_final' => $fecha_final));
//->setParameters(array(1=> $id_servicio));
$result = $query->getResult();
if (sizeof($result) >= 1) {
return $result;
} else {
return array(0 => array('cantidad' => 0, 'cantidadHP' => 0));
}
}
/**
* Sets the value of the location property.
*
* You can set the value of this property using a GeoPoint object or using an array,
*
* <code>
* // example with GeoPoint
* $entity->location = new GeoPoint(-45.123, -23.345);
*
* // example with arrays
* $entity->location = [-45.123, -23.345];
* $entity->location = ['x' => -45.123, 'y' => -23.345];
* $entity->location = ['longitude' => -45.123, 'latitude' => -23.345];
*
* </code>
*
* This method sets the value of the property _geoLocation with a PostGIS ST_Geography type.
*
* @param \MapasCulturais\Types\GeoPoint|array $location
*/
function setLocation($location)
{
$x = $y = null;
if (!$location instanceof GeoPoint) {
if (is_array($location) && key_exists('x', $location) && key_exists('y', $location)) {
$x = $location['x'];
$y = $location['y'];
} elseif (is_array($location) && key_exists('longitude', $location) && key_exists('latitude', $location)) {
$x = $location['longitude'];
$y = $location['latitude'];
} elseif (is_array($location) && count($location) === 2 && is_numeric($location[0]) && is_numeric($location[1])) {
$x = $location[0];
$y = $location[1];
} else {
throw new \Exception(App::txt('The location must be an instance of \\MapasCulturais\\Types\\GeoPoint or an array with two numeric values'));
}
$location = new GeoPoint($x, $y);
}
if (is_numeric($x) && is_numeric($y)) {
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$sql = "SELECT ST_GeographyFromText('POINT({$location->longitude} {$location->latitude})') AS geo";
$rsm->addScalarResult('geo', 'geo');
$query = App::i()->em->createNativeQuery($sql, $rsm);
$this->_geoLocation = $query->getSingleScalarResult();
}
$this->location = $location;
}
function API_list()
{
$app = App::i();
$result = [];
if (isset($this->data['includeData'])) {
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('type', 'type');
$rsm->addScalarResult('name', 'name');
$strNativeQuery = "SELECT type, name FROM geo_division";
$query = $app->getEm()->createNativeQuery($strNativeQuery, $rsm);
$divisions = $query->getScalarResult();
}
foreach ($app->getRegisteredGeoDivisions() as $geoDivision) {
$r = clone $geoDivision;
if (isset($this->data['includeData'])) {
foreach ($divisions as $index => $division) {
if (!isset($r->data)) {
$r->data = [];
}
if ($r->key === $division['type']) {
$r->data[] = $division['name'];
}
}
}
$result[] = $r;
}
$this->json($result);
}
/**
* Realiza verificacao dos usuario com status pendente de ativacao
*/
public function getUsuariosExterno()
{
$sql = 'SELECT u.sq_usuario_externo FROM sicae.usuario_externo u
WHERE u.st_registro_ativo = ' . self::ST_REGISTRO_PENDENTE . 'AND (DATE(NOW()) - DATE(u.dt_cadastro)) >= ' . self::NU_DIAS;
$rsm = new \Doctrine\ORM\Query\ResultSetMapping($this->_em);
$rsm->addScalarResult('sq_usuario_externo', 'sqUsuarioExterno', 'integer');
return $this->_em->createNativeQuery($sql, $rsm)->getResult();
}
function createQuery($date1, $date2)
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addEntityResult('MapasCulturais\\Entities\\EventOccurrence', 'e');
$rsm->addFieldResult('e', 'id', 'id');
$strNativeQuery = "SELECT * FROM recurring_event_occurrence_for('{$date1}', '{$date2}', 'Etc/UTC', NULL)";
return $this->app->em->createNativeQuery($strNativeQuery, $rsm);
}
/**
*
* @param \Core_Dto_Search $dto
* @return boolean
*/
public function isTramiteExterno(\Core_Dto_Search $dto)
{
$sql = "SELECT (uo.sq_pessoa is null) AS is_tramite_externo\n FROM sgdoce.vw_ultimo_tramite_artefato uta\n JOIN corporativo.vw_pessoa pes\n ON pes.sq_pessoa = uta.sq_pessoa_destino\n LEFT JOIN corporativo.vw_unidade_org uo\n ON pes.sq_pessoa = uo.sq_pessoa\n WHERE sq_artefato = :sqArtefato";
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('is_tramite_externo', 'isTramiteExterno', 'boolean');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter('sqArtefato', $dto->getSqArtefato());
return $query->getSingleScalarResult();
}
public function getKeyArquivamento($sqArtefato)
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('sq_artefato_arquivo_setorial', 'sqArtefatoArquivoSetorial', 'integer');
$sql = "\n SELECT sq_artefato_arquivo_setorial\n FROM artefato_arquivo_setorial\n WHERE sq_artefato = :sqArtefato\n AND dt_desarquivamento IS NULL\n LIMIT 1";
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter('sqArtefato', $sqArtefato)->useResultCache(false);
return $query->getSingleScalarResult();
}
/**
* Consulta de processos.
*
* @return QueryBuilder
*/
public function listGridProcesso($dto)
{
$listCondition = array('getInteressado' => array("ilike" => array("OR" => array("trim(pse.no_pessoa)", "trim(vue.sg_unidade_org || ' - ' || pse.no_pessoa)"), 'tlp' => array('trim(%s)', 'trim(%s)'))), 'getNuArtefato' => array("regex" => array("AND" => array('art.nu_artefato'), 'tlp' => array('%s'))), 'getSqAssunto' => array("=" => array("AND" => 'ass.sq_assunto')), 'getTxAssuntoComplementar' => array("ilike" => array("AND" => 'art.tx_assunto_complementar')), 'getOrigem' => array("ilike" => array('OR' => array('trim(pfo.no_pessoa)', "trim(pfo.no_pessoa)"), 'tlp' => array('trim(%s)', 'trim(%s)'))), 'getNuDigital' => array("ilike" => array("AND" => 'cast(ard.nu_digital as text)')));
$listPeriodo = array('dtCadastro' => 'art.dt_cadastro', 'dtAutuacao' => 'art.dt_artefato', 'dtPrazo' => 'art.dt_prazo');
$sqPeriodo = $dto->getSqPeriodo();
$periodoColumn = null;
if (isset($listPeriodo[$sqPeriodo])) {
$periodoColumn = $listPeriodo[$sqPeriodo];
}
$where = $this->getEntityManager()->getRepository('app:Artefato')->getCriteriaText($listCondition, $dto, $periodoColumn);
$rsm = new \Doctrine\ORM\Query\ResultSetMapping($this->_em);
$rsm->addScalarResult('total_record', 'totalRecord', 'integer');
$rsm->addScalarResult('sq_artefato', 'sqArtefato', 'integer');
$rsm->addScalarResult('nu_artefato', 'nuArtefato', 'string');
$rsm->addScalarResult('tx_assunto', 'txAssunto', 'string');
$rsm->addScalarResult('origem', 'noPessoaOrigem', 'string');
$rsm->addScalarResult('interessados', 'noPessoaInteressados', 'string');
$rsm->addScalarResult('tx_movimentacao', 'txMovimentacao', 'string');
$sql = "SELECT\n COUNT(art.sq_artefato) OVER() AS total_record,\n art.sq_artefato,\n formata_numero_artefato(art.nu_artefato, atp.co_ambito_processo) AS nu_artefato,\n ass.sq_assunto,\n ass.tx_assunto,\n art.tx_assunto_complementar,\n string_agg(pse.no_pessoa, ', ') as interessados,\n pfo.sq_pessoa_corporativo as origem_id,\n pfo.no_pessoa as origem,\n art.dt_artefato,\n art.dt_prazo,\n art.dt_cadastro,\n ard.nu_digital,\n sgdoce.ultima_movimentacao_artefato(art.sq_artefato) as tx_movimentacao,\n vuo.sg_unidade_org\n FROM sgdoce.artefato art\n JOIN sgdoce.artefato_processo atp\n ON art.sq_artefato = atp.sq_artefato\n JOIN sgdoce.tipo_artefato_assunto taa\n ON art.sq_tipo_artefato_assunto = taa.sq_tipo_artefato_assunto\n JOIN sgdoce.assunto ass\n ON taa.sq_assunto = ass.sq_assunto\n JOIN sgdoce.pessoa_artefato pao\n ON art.sq_artefato = pao.sq_artefato AND pao.sq_pessoa_funcao = " . \Core_Configuration::getSgdocePessoaFuncaoOrigem() . "\n JOIN sgdoce.pessoa_sgdoce pfo\n ON pao.sq_pessoa_sgdoce = pfo.sq_pessoa_sgdoce\n LEFT JOIN corporativo.vw_unidade_org vuo\n ON pfo.sq_pessoa_corporativo = vuo.sq_pessoa\n LEFT JOIN sgdoce.pessoa_interessada_artefato pai\n ON art.sq_artefato = pai.sq_artefato\n LEFT JOIN sgdoce.pessoa_sgdoce pse\n ON pai.sq_pessoa_sgdoce = pse.sq_pessoa_sgdoce\n LEFT JOIN corporativo.vw_unidade_org vue\n ON pse.sq_pessoa_corporativo = vue.sq_pessoa\n LEFT JOIN sgdoce.artefato_vinculo arv\n ON art.sq_artefato = arv.sq_artefato_pai AND arv.sq_tipo_vinculo_artefato = " . \Core_Configuration::getSgdoceTipoVinculoArtefatoAutuacao() . "\n LEFT JOIN sgdoce.artefato ard\n ON ard.sq_artefato = arv.sq_artefato_filho\n %s\n GROUP BY art.sq_artefato,\n art.nu_artefato,\n atp.co_ambito_processo,\n ass.sq_assunto,\n ass.tx_assunto,\n art.tx_assunto_complementar,\n pfo.sq_pessoa_corporativo,\n pfo.no_pessoa,\n art.dt_artefato,\n art.dt_prazo,\n art.dt_cadastro,\n ard.nu_digital,\n tx_movimentacao,\n vuo.sg_unidade_org";
if ($where != "") {
$where = "WHERE " . $where;
} else {
$where = "WHERE 1 <> 1";
}
$sql = sprintf($sql, $where);
return $this->_em->createNativeQuery($sql, $rsm);
}
public function getDbversion()
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('v', 'v');
$version = $this->app['orm.em']->createNativeQuery('select version() as v', $rsm)->getSingleScalarResult();
if ($this->app['config']['database']['driver'] == 'pdo_mysql') {
return 'MySQL ' . $version;
} else {
return $version;
}
}
public function testNativeQueryResultCaching()
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('id', 'u');
$query = $this->_em->createNativeQuery('select u.id FROM cms_users u WHERE u.id = ?', $rsm);
$query->setParameter(1, 10);
$cache = new ArrayCache();
$query->setResultCacheDriver($cache)->useResultCache(true);
$this->assertEquals(0, count($cache->getIds()));
$query->getResult();
$this->assertEquals(1, count($cache->getIds()));
return $query;
}
/**
* Gets an approximate row count from the information_schema.tables for the current entity
* @return int|NULL
*/
public function findApproximateCount()
{
$database = $this->getEntityManager()->getConnection()->getDatabase();
$tableName = $this->getClassMetadata()->table['name'];
$sql = 'SELECT table_rows FROM information_schema.tables WHERE TABLE_SCHEMA=? AND TABLE_NAME=?';
$resultSetMapping = new \Doctrine\ORM\Query\ResultSetMapping();
$resultSetMapping->addScalarResult('table_rows', 'table_rows');
$query = $this->_em->createNativeQuery($sql, $resultSetMapping);
$query->setParameter(1, $database);
$query->setParameter(2, $tableName);
$result = $query->getOneOrNullResult();
return $result['table_rows'];
}
public function getFoundRows()
{
// Run FOUND_ROWS query and add to results array
$sql = 'SELECT FOUND_ROWS() AS foundRows';
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('foundRows', 'foundRows');
$query = $this->em->createNativeQuery($sql, $rsm);
$foundRows = $query->getResult();
if ($foundRows[0]['foundRows']) {
return $foundRows[0]['foundRows'];
} else {
return 0;
}
}
/**
* Define check exception query
*
* FIXME: decompose; move query definition to a separate method
*
* @param array $ids Option ids list
*
* @return \Doctrine\ORM\NativeQuery
*/
protected function defineCheckExceptionQuery(array $ids)
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('cnt', 'cnt');
$keys = array();
$parameters = array();
foreach ($ids as $id) {
$keys[] = ':id' . $id;
$parameters['id' . $id] = $id;
}
$query = $this->_em->createNativeQuery('SELECT COUNT(e1.option_id) as cnt ' . 'FROM ' . $this->_class->getTableName() . ' as e1 ' . 'WHERE e1.option_id IN (' . implode(', ', $keys) . ') ' . 'GROUP BY e1.exception_id ' . 'HAVING cnt = (' . 'SELECT COUNT(e2.option_id) ' . 'FROM ' . $this->_class->getTableName() . ' as e2 ' . 'WHERE e2.exception_id = e1.exception_id ' . 'GROUP BY e2.exception_id' . ') ' . 'LIMIT 1', $rsm);
foreach ($parameters as $key => $value) {
$query->setParameter($key, $value);
}
return $query;
}
/**
* Counts Assets with the given Tag assigned
*
* @param \TYPO3\Media\Domain\Model\Tag $tag
* @param AssetCollection $assetCollection
* @return integer
*/
public function countByTag(\TYPO3\Media\Domain\Model\Tag $tag, AssetCollection $assetCollection = NULL)
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('c', 'c');
if ($assetCollection === NULL) {
$queryString = 'SELECT count(a.persistence_object_identifier) c FROM typo3_media_domain_model_asset a LEFT JOIN typo3_media_domain_model_asset_tags_join mm ON a.persistence_object_identifier = mm.media_asset WHERE mm.media_tag = ?';
} else {
$queryString = 'SELECT count(a.persistence_object_identifier) c FROM typo3_media_domain_model_asset a LEFT JOIN typo3_media_domain_model_asset_tags_join tagmm ON a.persistence_object_identifier = tagmm.media_asset LEFT JOIN typo3_media_domain_model_assetcollection_assets_join collectionmm ON a.persistence_object_identifier = collectionmm.media_asset WHERE tagmm.media_tag = ? AND collectionmm.media_assetcollection = ?';
}
$query = $this->entityManager->createNativeQuery($queryString, $rsm);
$query->setParameter(1, $tag);
if ($assetCollection !== NULL) {
$query->setParameter(2, $assetCollection);
}
return $query->getSingleScalarResult();
}
public function getLastHistorico($sqArtefato)
{
$sql = 'SELECT ch.sq_tipo_historico_arquivo
FROM sgdoce.caixa_historico ch
JOIN ( SELECT sq_artefato,
MAX(dt_operacao) dt_operacao
FROM sgdoce.caixa_historico
GROUP BY 1
) ch_max ON (ch.sq_artefato, ch.dt_operacao) = (ch_max.sq_artefato, ch_max.dt_operacao)
WHERE ch.sq_artefato = :sqArtefato';
$rsm = new \Doctrine\ORM\Query\ResultSetMapping($this->_em);
$rsm->addScalarResult('sq_tipo_historico_arquivo', 'sqTipoHistoricoArquivo', 'integer');
$nq = $this->_em->createNativeQuery($sql, $rsm);
$nq->setParameter('sqArtefato', $sqArtefato);
$result = $nq->getOneOrNullResult();
return $result ? $result['sqTipoHistoricoArquivo'] : NULL;
}
public function getDatasMaxMin($sqVolume)
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping($this->_em);
$rsm->addScalarResult('dt_abertura', 'dtAbertura', 'string');
$rsm->addScalarResult('dt_encerramento', 'dtEncerramento', 'string');
$query = $this->_em->createNativeQuery('
SELECT
to_char(ant.dt_encerramento, \'DD/MM/YYYY\') AS dt_encerramento,
to_char(post.dt_abertura, \'DD/MM/YYYY\') AS dt_abertura
FROM
sgdoce.processo_volume pv
LEFT JOIN sgdoce.processo_volume ant ON (ant.sq_artefato = pv.sq_artefato AND ant.nu_volume = (pv.nu_volume - 1))
LEFT JOIN sgdoce.processo_volume post ON (post.sq_artefato = pv.sq_artefato AND post.nu_volume = (pv.nu_volume + 1))
WHERE
pv.sq_volume = :sqVolume', $rsm);
$query->setParameter('sqVolume', $sqVolume);
return $query->getResult();
}
public function getNextBoxNumber(\Core_Dto_Search $dto)
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping($this->_em);
$rsm->addScalarResult('nu_caixa', 'nuCaixa', 'string');
$sql = sprintf("\n SELECT lpad((COALESCE(max(nu_caixa::INTEGER),0) + 1)::TEXT,7,'0') AS nu_caixa\n FROM caixa\n WHERE sq_unidade_org = %d", $dto->getSqUnidadeOrg());
$nativeQuery = $this->_em->createNativeQuery($sql, $rsm);
$result = $nativeQuery->getSingleResult();
return $result['nuCaixa'];
}
public function findByEventsAndDateInterval($event_ids = array(), $date_from = null, $date_to = null, $limit = null, $offset = null)
{
if (!$event_ids) {
return array();
}
if (is_null($date_from)) {
$date_from = date('Y-m-d');
} else {
if ($date_from instanceof \DateTime) {
$date_from = $date_from->format('Y-m-d');
}
}
if (is_null($date_to)) {
$date_to = $date_from;
} else {
if ($date_to instanceof \DateTime) {
$date_to = $date_to->format('Y-m-d');
}
}
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addEntityResult('MapasCulturais\\Entities\\Space', 'e');
$metadata = $this->getClassMetadata();
foreach ($metadata->fieldMappings as $map) {
$rsm->addFieldResult('e', $map['columnName'], $map['fieldName']);
}
$dql_limit = $dql_offset = '';
if ($limit) {
$dql_limit = 'LIMIT ' . $limit;
}
if ($offset) {
$dql_offset = 'OFFSET ' . $offset;
}
$dql_event_ids = $event_ids ? 'AND eo.event_id IN (' . implode(',', $event_ids) . ')' : '';
$strNativeQuery = "\n SELECT\n e.*\n FROM\n space e\n JOIN\n recurring_event_occurrence_for(:date_from, :date_to, 'Etc/UTC', NULL) eo\n ON eo.space_id = e.id {$dql_event_ids}\n\n WHERE e.status > 0\n\n {$dql_limit} {$dql_offset}\n\n ORDER BY\n eo.starts_on, eo.starts_at";
$query = $this->_em->createNativeQuery($strNativeQuery, $rsm);
$app = \MapasCulturais\App::i();
if ($app->config['app.useEventsCache']) {
$query->useResultCache(true, $app->config['app.eventsCache.lifetime']);
}
$query->setParameters(array('date_from' => $date_from, 'date_to' => $date_to));
return $query->getResult();
}
/**
* Walks down a SelectStatement AST node, wrapping it in a SELECT DISTINCT
*
* @param SelectStatement $AST
* @return string
*/
public function walkSelectStatement(SelectStatement $AST)
{
$innerSql = parent::walkSelectStatement($AST);
// Find out the SQL alias of the identifier column of the root entity
// It may be possible to make this work with multiple root entities but that
// would probably require issuing multiple queries or doing a UNION SELECT
// so for now, It's not supported.
// Get the root entity and alias from the AST fromClause
$from = $AST->fromClause->identificationVariableDeclarations;
if (count($from) !== 1) {
throw new \RuntimeException("Cannot count query which selects two FROM components, cannot make distinction");
}
$rootAlias = $from[0]->rangeVariableDeclaration->aliasIdentificationVariable;
$rootClass = $this->queryComponents[$rootAlias]['metadata'];
$rootIdentifier = $rootClass->identifier;
// For every identifier, find out the SQL alias by combing through the ResultSetMapping
$sqlIdentifier = array();
foreach ($rootIdentifier as $property) {
if (isset($rootClass->fieldMappings[$property])) {
foreach (array_keys($this->rsm->fieldMappings, $property) as $alias) {
if ($this->rsm->columnOwnerMap[$alias] == $rootAlias) {
$sqlIdentifier[$property] = $alias;
}
}
}
if (isset($rootClass->associationMappings[$property])) {
$joinColumn = $rootClass->associationMappings[$property]['joinColumns'][0]['name'];
foreach (array_keys($this->rsm->metaMappings, $joinColumn) as $alias) {
if ($this->rsm->columnOwnerMap[$alias] == $rootAlias) {
$sqlIdentifier[$property] = $alias;
}
}
}
}
if (count($rootIdentifier) != count($sqlIdentifier)) {
throw new \RuntimeException(sprintf('Not all identifier properties can be found in the ResultSetMapping: %s', implode(', ', array_diff($rootIdentifier, array_keys($sqlIdentifier)))));
}
// Build the counter query
$sql = sprintf('SELECT DISTINCT %s FROM (%s) dctrn_result', implode(', ', $sqlIdentifier), $innerSql);
if ($this->platform instanceof PostgreSqlPlatform) {
//http://www.doctrine-project.org/jira/browse/DDC-1958
$this->getPostgresqlSql($AST, $sqlIdentifier, $innerSql, $sql);
}
// Apply the limit and offset
$sql = $this->platform->modifyLimitQuery($sql, $this->maxResults, $this->firstResult);
// Add the columns to the ResultSetMapping. It's not really nice but
// it works. Preferably I'd clear the RSM or simply create a new one
// but that is not possible from inside the output walker, so we dirty
// up the one we have.
foreach ($sqlIdentifier as $property => $alias) {
$this->rsm->addScalarResult($alias, $property);
}
return $sql;
}
public function deleteInteressadoSemSqCorporativo($dto)
{
$sql = "SELECT ps.sq_pessoa_sgdoce\n FROM pessoa_interessada_artefato pa\n JOIN pessoa_sgdoce ps using(sq_pessoa_sgdoce)\n WHERE sq_artefato = :sqArtefato\n AND sq_pessoa_corporativo IS NULL";
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('sq_pessoa_sgdoce', 'sqPessoaSgdoce', 'integer');
$nq = $this->_em->createNativeQuery($sql, $rsm);
$nq->setParameter('sqArtefato', $dto->getSqArtefato());
$result = $nq->getArrayResult();
if (!$result) {
return 0;
}
$aux = array();
foreach ($result as $value) {
$aux[] = $value['sqPessoaSgdoce'];
}
$queryBuilder = $this->_em->createQueryBuilder();
$delete = $queryBuilder->delete($this->_entityName, 'pa')->andWhere('pa.sqArtefato = :sqArtefato')->andWhere($queryBuilder->expr()->in('pa.sqPessoaSgdoce', $aux))->setParameter('sqArtefato', $dto->getSqArtefato());
$out = $delete->getQuery()->execute();
return $out;
}
public function indexAction()
{
$this->updateLayoutWithIdentity();
$page = intval($this->params()->fromQuery('page', 1), 10);
$rank = is_null($this->getIdentityEntity()) ? 0 : $this->getIdentityEntity()->rank;
$maxResults = 15;
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('theCount', 'count');
$result = $this->getEntityManager()->createNativeQuery("SELECT COUNT(id) AS theCount FROM announcement WHERE lowestReadableRank <= {$rank}", $rsm)->execute();
$pages = ceil($result[0]['count'] / $maxResults);
if ($page > $pages) {
$page = $pages;
}
$offset = ($page - 1) * $maxResults;
$announcementRepo = $this->getEntityManager()->getRepository('NightsWatch\\Entity\\Announcement');
$criteria = Criteria::create()->where(Criteria::expr()->lte('lowestReadableRank', $rank))->orderBy(['timestamp' => 'DESC'])->setFirstResult($offset)->setMaxResults($maxResults);
/** @var \NightsWatch\Entity\Announcement[] $announcements */
$announcements = $announcementRepo->matching($criteria);
return new ViewModel(['announcements' => $announcements, 'pages' => $pages, 'page' => $page, 'identity' => $this->getIdentityEntity()]);
}
public function getDbversion()
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('v', 'v');
switch ($this->app['config']['database']['driver']) {
case 'pdo_sqlite':
$prefix = 'SQLite version ';
$func = 'sqlite_version()';
break;
case 'pdo_mysql':
$prefix = 'MySQL ';
$func = 'version()';
break;
case 'pdo_pgsql':
default:
$prefix = '';
$func = 'version()';
}
$version = $this->app['orm.em']->createNativeQuery('select ' . $func . ' as v', $rsm)->getSingleScalarResult();
return $prefix . $version;
}
/**
* @param \Core_Dto_Search $dto
* Perfil do Usuário ex. SGI = \Core_Configuration::getSicaeTipoPerfilAdministrador()
* @return QueryBuilder
*/
public function listUsuarioPorPerfil($dto)
{
$sqPerfil = null;
$sql = "SELECT DISTINCT\n u.sq_usuario,\n u.sq_pessoa,\n u.st_ativo,\n pe.no_pessoa,\n p.no_perfil,\n p.in_perfil_externo\n FROM sicae.vw_usuario u\n JOIN sicae.vw_usuario_perfil up USING(sq_usuario)\n JOIN sicae.vw_perfil p USING(sq_perfil)\n JOIN corporativo.vw_pessoa pe USING(sq_pessoa)\n WHERE u.st_ativo = TRUE\n AND p.sq_perfil = :sqPerfil\n ORDER BY pe.no_pessoa";
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('sq_usuario', 'sqUsuario');
$rsm->addScalarResult('sq_pessoa', 'sqPessoa');
$rsm->addScalarResult('st_ativo', 'stAtivo');
$rsm->addScalarResult('no_pessoa', 'noPessoa');
$rsm->addScalarResult('no_perfil', 'noPerfil');
$rsm->addScalarResult('in_perfil_externo', 'inPerfilExterno');
$nativeQuery = $this->_em->createNativeQuery($sql, $rsm);
$nativeQuery->setParameter('sqPerfil', $dto->getSqPerfil());
return $nativeQuery->useResultCache(TRUE, NULL, __METHOD__)->getArrayResult();
}
/**
* @see Console\Command\Command
*/
protected function execute(Console\Input\InputInterface $input, Console\Output\OutputInterface $output)
{
$em = $this->getApplication()->getKernel()->getContainer()->getService('em');
$sessionDiff = 3600 * 48;
$requestTimeDiff = 3600;
// select sections older than 48 hours
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addEntityResult('\\Newscoop\\Entity\\Session', 's');
$rsm->addFieldResult('s', 'id', 'id');
$rsm->addFieldResult('s', 'start_time', 'start_time');
$rsm->addFieldResult('s', 'user_id', 'user_id');
$rsm->addJoinedEntityResult('\\Newscoop\\Entity\\Request', 'r', 's', null);
$rsm->addFieldResult('r', 'last_stats_update', 'last_stats_update');
$rsm->addFieldResult('r', 'session_id', 'session');
$rsm->addScalarResult('last_update_diff', 'last_update_diff');
$sql = "SELECT\r\n s.id,\r\n s.start_time,\r\n s.user_id,\r\n MIN(TIME_TO_SEC(TIMEDIFF(NOW(), r.last_stats_update))) AS last_update_diff\r\n FROM\r\n Sessions s\r\n LEFT JOIN\r\n Requests r ON s.id = r.session_id\r\n WHERE\r\n TIME_TO_SEC(TIMEDIFF(NOW(), s.start_time)) >= {$sessionDiff}\r\n ";
$query = $em->createNativeQuery($sql, $rsm);
$sessions = $query->getResult();
if (count($sessions) == 0 || $sessions[0][0] == null) {
$output->writeln('<error>There is nothing to remove.</error>');
return;
}
foreach ($sessions as $session) {
if ($session['last_update_diff'] < $requestTimeDiff) {
// if there was a request for this session less than one hour ago do not process the session
continue;
}
foreach ($session[0]->getRequests() as $request) {
$em->remove($request);
$output->writeln('<info>Request for session with id: ' . $session[0]->getId() . ' was removed.</info>');
}
$em->remove($session[0]);
$output->writeln('<info>Session with id: ' . $session[0]->getId() . ' was removed.</info>');
}
$em->flush();
return true;
}
public function count()
{
if ($this->largeTableComputedCount === null) {
$query = $this->getQuery();
$sql = $query->getSQL();
if (substr_count($sql, 'SELECT') == 1 && strpos($sql, 'GROUP BY') === false && strpos($sql, 'SUM(') === false && strpos($sql, 'COUNT(') === false) {
$sql = preg_replace(array('/^SELECT ((?! FROM).+) FROM/ims', '/ORDER BY .*$/ims', '/LIMIT BY .*$/ims'), array('SELECT COUNT(*) AS count_for_paginator FROM', '', ''), $sql);
$em = $query->getEntityManager();
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('count_for_paginator', 'count_for_paginator');
$countQuery = $em->createNativeQuery($sql, $rsm);
$i = 0;
foreach ($query->getParameters() as $parameter) {
$v = $parameter->getValue();
$countQuery->setParameter($i++, is_object($v) ? $v->getId() : $v);
}
$result = $countQuery->getSingleScalarResult();
$this->largeTableComputedCount = (int) $result;
} else {
$this->largeTableComputedCount = $this->count();
}
}
return $this->largeTableComputedCount;
}
/**
* Merge second user into first one and returns it
* @param User $userByEmail user to be kept
* @param User $userByIdentity user to be deleted
* @param Identity $identity identity to be moved from the deleted user to kept user
* @return User
*/
private function mergeUser(User $userByEmail, User $userByIdentity, Identity $identity)
{
// Gather all references to the user that we are going to delete
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('TABLE_NAME', 'TABLE_NAME');
$rsm->addScalarResult('COLUMN_NAME', 'COLUMN_NAME');
$qb = $this->getEntityManager()->createNativeQuery("\n SELECT TABLE_NAME, COLUMN_NAME\n FROM `information_schema`.`KEY_COLUMN_USAGE`\n WHERE\n REFERENCED_TABLE_SCHEMA = :database\n AND REFERENCED_TABLE_NAME = 'user'\n AND REFERENCED_COLUMN_NAME = 'id'", $rsm);
$database = $this->getEntityManager()->getConnection()->getDatabase();
$qb->setParameters(['database' => $database]);
$records = $qb->getResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);
// Update all references from the old user to the user we're going to keep
foreach ($records as $r) {
$table = $r['TABLE_NAME'];
$field = $r['COLUMN_NAME'];
$query = "UPDATE `{$table}` SET `{$field}` = :newUser WHERE `{$field}` = :oldUser";
$this->getEntityManager()->getConnection()->executeUpdate($query, ['newUser' => $userByEmail->getId(), 'oldUser' => $userByIdentity->getId()]);
}
// This is not strictly necessary since the DB just has been update a
// few lines before, but just to keep the model in memory up to date we do it "again"
$identity->setUser($userByEmail);
// Delete the duplicated user
$this->getEntityManager()->remove($userByIdentity);
return $userByEmail;
}
/**
* Método de consulta de campos dos padrões de documentos campos para preenchimento do combo
* @return array
*/
public function listItensPadraoModeloDocCampos($dtoSearch)
{
if ($dtoSearch->getSqModeloDocumento()) {
$sql = " SELECT pmdc.sq_padrao_modelo_documento_cam,\n marcado.sq_padrao_modelo_documento checked,\n c.sq_campo,c.no_campo,gc.sq_grupo_campo,gc.no_grupo_campo,pmd.no_padrao_modelo_documento\n ,pmdc.in_visivel_documento\n FROM campo c\n INNER JOIN grupo_campo gc\n ON (c.sq_grupo_campo = gc.sq_grupo_campo)\n INNER JOIN padrao_modelo_documento_campo pmdc\n ON (pmdc.sq_campo = c.sq_campo)\n INNER JOIN padrao_modelo_documento pmd\n ON (pmd.sq_padrao_modelo_documento = pmdc.sq_padrao_modelo_documento)\n LEFT JOIN\n (SELECT pmdc.sq_padrao_modelo_documento_cam, pmdc.sq_padrao_modelo_documento\n FROM modelo_documento md\n INNER JOIN modelo_documento_campo mdc\n ON (md.sq_modelo_documento = mdc.sq_modelo_documento)\n INNER JOIN padrao_modelo_documento_campo pmdc\n ON (mdc.sq_padrao_modelo_documento_cam = pmdc.sq_padrao_modelo_documento_cam)\n WHERE md.sq_modelo_documento = {$dtoSearch->getSqModeloDocumento()}) marcado\n ON (marcado.sq_padrao_modelo_documento_cam = pmdc.sq_padrao_modelo_documento_cam\n )\n WHERE pmd.sq_padrao_modelo_documento = {$dtoSearch->getSqPadraoModeloDocumento()}\n ORDER BY pmdc.nu_ordem, gc.sq_grupo_campo, c.sq_campo ASC\n ";
$rsm = new \Doctrine\ORM\Query\ResultSetMapping($this->_em);
$rsm->addScalarResult('sq_padrao_modelo_documento_cam', 'sqPadraoModeloDocumentoCam', 'string');
$rsm->addScalarResult('checked', 'checked', 'string');
$rsm->addScalarResult('no_padrao_modelo_documento', 'noPadraoModeloDocumento', 'string');
$rsm->addScalarResult('no_campo', 'noCampo', 'string');
$rsm->addScalarResult('no_grupo_campo', 'noGrupoCampo', 'string');
$rsm->addScalarResult('sq_grupo_campo', 'sqGrupoCampo', 'string');
$rsm->addScalarResult('in_visivel_documento', 'inVisivelDocumento', 'boolean');
$result = $this->_em->createNativeQuery($sql, $rsm)->execute();
return $result;
} else {
$queryBuilder = $this->_em->createQueryBuilder()->select("pmdc.sqPadraoModeloDocumentoCam,\n pmd.noPadraoModeloDocumento,\n c.noCampo,\n gc.noGrupoCampo,\n gc.sqGrupoCampo,\n '' checked", 'pmdc.nuOrdem,
pmdc.inVisivelDocumento')->from('app:PadraoModeloDocumentoCampo', 'pmdc')->innerJoin('pmdc.sqPadraoModeloDocumento', 'pmd')->innerJoin('pmdc.sqCampo', 'c')->innerJoin('c.sqGrupoCampo', 'gc')->orderBy('pmdc.nuOrdem, gc.sqGrupoCampo, c.sqCampo', 'ASC');
if ($dtoSearch->getSqPadraoModeloDocumento()) {
$queryBuilder->andWhere('pmd.sqPadraoModeloDocumento = :padraoModeloDoc')->setParameter('padraoModeloDoc', $dtoSearch->getSqPadraoModeloDocumento());
}
$out = $queryBuilder->getQuery()->getArrayResult();
return $out;
}
}
/**
* Método para pesquisa de artefato por ID,
* não é necessário o artefato estar na área do trabalho para retornar a pesquisa.
*
* @return
*/
public function findById($sqArtefato)
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('sq_artefato', 'sqArtefato', 'integer');
$rsm->addScalarResult('nu_digital', 'nuDigital', 'string');
$rsm->addScalarResult('nu_artefato', 'nuArtefato', 'string');
$rsm->addScalarResult('sq_tipo_documento', 'sqTipoDocumento', 'integer');
$rsm->addScalarResult('no_tipo_documento', 'noTipoDocumento', 'string');
$rsm->addScalarResult('sq_tipo_artefato', 'sqTipoArtefato', 'integer');
$rsm->addScalarResult('dt_cadastro', 'dtCadastro', 'zenddate');
$rsm->addScalarResult('co_ambito_processo', 'coAmbitoProcessos', 'string');
$rsm->addScalarResult('no_pessoa_origem', 'noPessoaOrigem', 'string');
$sql = "SELECT\n art.sq_artefato,\n art.nu_digital,\n art.nu_artefato,\n art.dt_cadastro,\n art_ass.sq_tipo_artefato,\n art_pro.co_ambito_processo,\n tip_doc.sq_tipo_documento,\n tip_doc.no_tipo_documento,\n pes_sgd.no_pessoa as no_pessoa_origem\n FROM artefato art\n JOIN tipo_artefato_assunto art_ass ON art.sq_tipo_artefato_assunto = art_ass.sq_tipo_artefato_assunto\n JOIN tipo_documento tip_doc ON art.sq_tipo_documento = tip_doc.sq_tipo_documento\n -- ORIGEM\n JOIN pessoa_artefato pes_art ON art.sq_artefato = pes_art.sq_artefato\n JOIN pessoa_sgdoce pes_sgd ON pes_art.sq_pessoa_sgdoce = pes_sgd.sq_pessoa_sgdoce \n LEFT JOIN artefato_processo art_pro ON art.sq_artefato = art_pro.sq_artefato\n WHERE art.sq_artefato = :sqArtefato\n AND pes_art.sq_pessoa_funcao = :sqPessoaFuncao";
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter('sqArtefato', $sqArtefato)->setParameter('sqPessoaFuncao', \Core_Configuration::getSgdocePessoaFuncaoOrigem())->useResultCache(false);
return $query->getSingleResult();
}
