/**
* @param Collection $collection
* @param array|string $catalogNumbers
* @return array
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function findBestTaxonsByCatalogNumbers(Collection $collection, $catalogNumbers)
{
if (!is_array($catalogNumbers)) {
$catalogNumbers = [$catalogNumbers];
}
$rsm = new ResultSetMapping();
$rsm->addScalarResult('scientificname', 'scientificname');
$rsm->addScalarResult('scientificnameauthorship', 'scientificnameauthorship');
$rsm->addScalarResult('catalognumber', 'catalognumber');
$nativeSqlTaxon = '
WITH FirstIDentified AS (
SELECT First_Value(t.taxonid) OVER (PARTITION BY catalognumber ORDER BY identificationverifstatus) First,
t.taxonid, t.scientificname, t.scientificnameauthorship,
s.catalognumber
FROM Taxons t
JOIN Determinations d ON t.taxonid = d.taxonid
JOIN Specimens s on d.occurrenceid = s.occurrenceid
WHERE s.collectioncode = :collectionCode AND
s.catalognumber IN (:catalogNumbers)
)
SELECT catalognumber, scientificname, scientificnameauthorship FROM FirstIdentified
WHERE taxonid = First
';
$this->getEntityManager()->getConnection()->setFetchMode(\PDO::FETCH_ASSOC);
$results = $this->getEntityManager()->getConnection()->executeQuery($nativeSqlTaxon, ['collectionCode' => $collection->getCollectioncode(), 'catalogNumbers' => $catalogNumbers], ['catalogNumbers' => Connection::PARAM_STR_ARRAY])->fetchAll();
$formattedResult = [];
if (count($results)) {
foreach ($results as $values) {
$formattedResult[$values['CATALOGNUMBER']] = Taxon::toString($values['SCIENTIFICNAME'], $values['SCIENTIFICNAMEAUTHORSHIP']);
}
}
return $formattedResult;
}
/**
* @Route("/overallscore/{id}",name="result_report")
*
*/
public function resultAction($id)
{
$rsm = new ResultSetMapping();
$rsm->addScalarResult('amount', 'a');
$rsm->addScalarResult('total', 't');
$em = $this->getDoctrine()->getManager();
$verbalr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'verbal', $rsm);
$mathr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'mathematical', $rsm);
$spatialr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'spatial', $rsm);
$visualr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'visualization', $rsm);
$classifyr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'classification', $rsm);
$logicr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'logic', $rsm);
$patternr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'pattern recognition', $rsm);
$verbal = implode(",", $verbalr[0]);
$math = implode(",", $mathr[0]);
$spatial = implode(",", $spatialr[0]);
$visual = implode(",", $visualr[0]);
$classify = implode(",", $classifyr[0]);
$logic = implode(",", $logicr[0]);
$pattern = implode(",", $patternr[0]);
if (!$verbalr || !$mathr || !$visualr || !$classifyr || !$patternr || !$logicr || !$spatialr) {
throw $this->createNotFoundException('No Data found for Person');
}
return $this->render('ReportBundle::user.html.twig', array('verbal' => $verbal, 'math' => $math, 'visual' => $visual, 'classify' => $classify, 'pattern' => $pattern, 'logic' => $logic, 'spatial' => $spatial, 'name' => 'User Result'));
}
public function __construct(\Doctrine\ORM\EntityManagerInterface $entityManager)
{
parent::__construct($entityManager);
$resultSetMapping = new ResultSetMapping();
$resultSetMapping->addScalarResult('ID', 'id');
$resultSetMapping->addScalarResult('Name', 'name');
$this->setResultMapping($resultSetMapping);
}
public function top20TableSizes()
{
$sql = "SELECT nspname || '.' || relname AS relation,\n pg_size_pretty(pg_relation_size(C.oid)) AS size\n FROM pg_class C\n LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\n WHERE nspname NOT IN ('pg_catalog', 'information_schema')\n AND C.relkind <> 'i'\n AND nspname !~ '^pg_toast'\n AND nspname || '.' || relname != 'public.spatial_ref_sys'\n ORDER BY pg_relation_size(C.oid) DESC\n LIMIT 20";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('relation', 'relation');
$rsm->addScalarResult('size', 'size');
$query = $this->em->createNativeQuery($sql, $rsm);
return $query->getResult();
}
public function __construct(\Doctrine\ORM\EntityManagerInterface $entityManager)
{
parent::__construct($entityManager);
$resultSetMapping = new ResultSetMapping();
$resultSetMapping->addScalarResult('DestinationNumber', 'receiver');
$resultSetMapping->addScalarResult('TextDecoded', 'message');
$resultSetMapping->addScalarResult('Status', 'status');
$resultSetMapping->addScalarResult('SenderID', 'phone');
$this->setResultMapping($resultSetMapping);
}
/**
* Lists all Empleado entities.
*
* @Route("/", name="admin_empleado")
* @Method("GET")
* @Template()
*/
public function indexAction()
{
$entity = new Empleado();
$form = $this->createCreateForm($entity);
$rsm = new ResultSetMapping();
$em = $this->getDoctrine()->getManager();
$sql = "select per.nombres as pnombre, per.apellidos as papellido, " . "per.direccion as direccion, per.telefono as tel, per.email as email, emp.id as idemp, emp.cargo as cargo, emp.foto as foto, emp.estado as estado " . "from empleado emp inner join persona per on emp.persona = per.id where emp.estado=true order by per.apellidos";
$rsm->addScalarResult('idemp', 'idemp');
$rsm->addScalarResult('pnombre', 'pnombre');
//$rsm->addScalarResult('snombre','snombre');
$rsm->addScalarResult('papellido', 'papellido');
//$rsm->addScalarResult('sapellido','sapellido');
//$rsm->addScalarResult('casada','casada');
$rsm->addScalarResult('direccion', 'direccion');
$rsm->addScalarResult('tel', 'tel');
$rsm->addScalarResult('email', 'email');
$rsm->addScalarResult('cargo', 'cargo');
$rsm->addScalarResult('foto', 'foto');
//$rsm->addScalarResult('sucursal','sucursal');
$rsm->addScalarResult('estado', 'estado');
$empleados = $em->createNativeQuery($sql, $rsm)->getResult();
$usuario = $this->get('security.token_storage')->getToken()->getUser();
$foto = $usuario->getPersona()->getEmpleado()[0]->getFoto();
return array('empleados' => $empleados, 'entity' => $entity, 'form' => $form->createView(), 'foto' => $foto);
}
/**
*
* Relatório de Configurações das Classes WMI Dinâmico Detalhes
*
*/
public function relatorioWmiDinamico($property, $dataInicio, $dataFim)
{
$rsm = new ResultSetMapping();
$rsm->addScalarResult('nm_rede', 'nm_rede');
$rsm->addScalarResult('dt_hr_ult_acesso', 'dt_hr_ult_acesso');
$rsm->addScalarResult('id_computador', 'id_computador');
$rsm->addScalarResult('nm_computador', 'nm_computador');
$rsm->addScalarResult('te_node_address', 'te_node_address');
$rsm->addScalarResult('te_ip_computador', 'te_ip_computador');
$rsm->addScalarResult('te_ip_rede', 'te_ip_rede');
$rsm->addScalarResult('nm_rede', 'nm_rede');
$sql = 'SELECT c.id_computador, c.nm_computador, c.te_node_address, c.te_ip_computador, r.te_ip_rede, r.nm_rede, c.dt_hr_ult_acesso, ';
foreach ($property as $elm) {
$sql = $sql . "(CASE WHEN rc.{$elm} IS NOT NULL\n THEN rc.{$elm}\n ELSE 'Não identificado'\n END) as {$elm}, ";
$rsm->addScalarResult($elm, $elm);
}
$size = strlen($sql);
$sql = substr($sql, 0, $size - 2);
$sql = $sql . " FROM relatorio_coleta rc\n INNER JOIN computador c ON rc.id_computador = c.id_computador\n INNER JOIN rede r ON r.id_rede = c.id_rede\n WHERE (c.ativo IS NULL or c.ativo = 't')";
if (!empty($dataInicio)) {
$sql .= " AND c.dt_hr_ult_acesso >= '{$dataInicio} 00:00:00'";
}
if (!empty($dataFim)) {
$sql .= " AND c.dt_hr_ult_acesso <= '{$dataFim} 23:59:59'";
}
$result = $this->getEntityManager()->createNativeQuery($sql, $rsm)->execute();
return $result;
}
public function findEntityUsersRight($entity, $em)
{
$entityName = get_class($entity);
$entityId = $entity->getId();
$tables = array('acl_classes' => $this->container->getParameter('security.acl.dbal.class_table_name'), 'acl_security_identities' => $this->container->getParameter('security.acl.dbal.sid_table_name'), 'acl_object_identities' => $this->container->getParameter('security.acl.dbal.oid_table_name'), 'acl_object_ancestors' => $this->container->getParameter('security.acl.dbal.oid_ancestors_table_name'), 'acl_entries' => $this->container->getParameter('security.acl.dbal.entry_table_name'));
$rsm = new ResultSetMapping();
$rsm->addScalarResult('identifier', 'identifier');
$rsm->addScalarResult('mask', 'mask');
$query = $em->createNativeQuery('SELECT SI.IDENTIFIER,E.MASK ' . 'FROM ' . $tables['acl_classes'] . ' C ' . 'JOIN ' . $tables['acl_object_identities'] . ' OI ON C.ID = OI.CLASS_ID ' . 'JOIN ' . $tables['acl_entries'] . ' E ON E.CLASS_ID = C.ID AND E.OBJECT_IDENTITY_ID = OI.ID ' . 'JOIN ' . $tables['acl_security_identities'] . ' SI ON SI.ID = E.SECURITY_IDENTITY_ID ' . 'WHERE C.CLASS_TYPE = ? ' . 'AND OI.OBJECT_IDENTIFIER = ? ' . 'ORDER BY SI.IDENTIFIER', $rsm);
$query->setParameter(1, $entityName);
$query->setParameter(2, $entityId);
$result = array();
$users = array();
$previousIdentifier = null;
foreach ($query->getResult() as $i => $row) {
if (strpos($row['identifier'], '-') === FALSE) {
$login = $row['identifier'];
$result[$login]['type'] = 'role';
} else {
$login = substr($row['identifier'], strpos($row['identifier'], '-') + 1);
$result[$login]['type'] = 'user';
}
if (!array_key_exists($login, $result)) {
$result[$login] = array();
}
$result[$login]['masks'][] = $row['mask'];
$result[$login]['user'] = array();
$result[$login]['login'] = $login;
// $result[$login]['mask'] = $row['mask'];
$result[$login]['rights'][] = 'role.mask.' . $row['mask'];
$result[$login]['descriptions'][] = 'role.rightdescription.' . $row['mask'];
$users[] = $result[$login]['login'];
}
if ($users != null) {
$qb = $em->createQueryBuilder();
$qb->add('select', 'u.id,u.username,u.firstName,u.lastName,u.email,u.locked')->add('from', 'LowbiSystemBundle:User u');
$qb->add('where', $qb->expr()->in('u.username', '?1'));
$qb->setParameter(1, $users);
$query = $qb->getQuery();
$userresult = $query->getResult();
$usersArray = array();
foreach ($userresult as $item) {
$usersArray[$item['username']] = $item;
}
foreach ($result as $key => $item) {
if ($result[$key]['type'] == 'user') {
$result[$key]['user'] = $usersArray[$item['login']];
}
}
return $result;
} else {
return null;
}
}
/**
* @group DDC-407
*/
public function testHydrateScalarResults()
{
$rsm = new ResultSetMapping();
$rsm->addScalarResult('foo1', 'foo');
$rsm->addScalarResult('bar2', 'bar');
$rsm->addScalarResult('baz3', 'baz');
$resultSet = array(array('foo1' => 'A', 'bar2' => 'B', 'baz3' => 'C'));
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new \Doctrine\ORM\Internal\Hydration\ScalarHydrator($this->_em);
$result = $hydrator->hydrateAll($stmt, $rsm);
}
public function __construct(\Doctrine\ORM\EntityManagerInterface $entityManager)
{
parent::__construct($entityManager);
$resultSetMapping = new ResultSetMapping();
$resultSetMapping->addScalarResult('ID', 'id');
$resultSetMapping->addScalarResult('SenderNumber', 'sender');
$resultSetMapping->addScalarResult('TextDecoded', 'message');
$resultSetMapping->addScalarResult('Processed', 'processed');
$resultSetMapping->addScalarResult('RecipientID', 'phone');
$this->setResultMapping($resultSetMapping);
}
private function getJournals()
{
$sql = <<<SQL
SELECT id,footer_text FROM journal WHERE journal.footer_text is not null
SQL;
$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id');
$rsm->addScalarResult('footer_text', 'text');
$query = $this->em->createNativeQuery($sql, $rsm);
return $query->getResult();
}
public function testFindByNativeCQL()
{
$em = $this->getEntityManager();
$rsm = new ResultSetMapping();
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('price', 'price');
$query = $em->createNativeQuery('SELECT * FROM product WHERE name = ?', $rsm);
$query->setParameter(1, 'prod2');
$products = $query->getResult();
$this->assertNotEmpty($products);
$this->assertInternalType('array', $products[0]);
}
/**
*
* get contentious points by the create event
*
**/
public function contentiousPointsByCreateEventId($createEventId)
{
if (empty($createEventId)) {
throw new \InvalidArgumentException('createEventId may not be empty');
}
$sql = "SELECT ST_X(point) as x, ST_Y(point) as y FROM contentious_point WHERE create_event_id = :createEventId";
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult('x', 'x');
$rsm->addScalarResult('y', 'y');
$query = $this->em->createNativeQuery($sql, $rsm);
$query->setParameter('createEventId', $createEventId);
return $query->getResult();
}
/**
* Retrieves BranchEmailConfiguration using $supportAddress and $customerDomain as Criteria
*
* @param $supportAddress
* @param $customerDomain
* @return int|null
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getBySupportAddressAndCustomerDomainCriteria($supportAddress, $customerDomain)
{
$customerDomainRegExp = "[[:<:]]" . $customerDomain . "[[:>:]]";
$rsm = new ResultSetMapping();
$rsm->addEntityResult('DiamanteDeskBundle:BranchEmailConfiguration', 'j');
$rsm->addScalarResult('branch_id', 'branch_id');
$rsm->addScalarResult('criteria', 'criteria');
$result = $this->getEntityManager()->createNativeQuery("\n SELECT j.branch_id as branch_id,\n (j.support_address = :supportAddress AND\n j.customer_domains REGEXP :customerDomainRegExp) * 3 +\n (j.support_address = :supportAddress AND\n j.customer_domains = '') * 2 +\n (j.support_address = '' AND\n j.customer_domains REGEXP :customerDomainRegExp) * 1\n AS criteria\n FROM diamante_branch_email_configuration j ORDER BY criteria DESC LIMIT 1\n ", $rsm)->setParameter('supportAddress', $supportAddress)->setParameter('customerDomainRegExp', $customerDomainRegExp)->getOneOrNullResult();
if ($result['criteria']) {
return $result['branch_id'];
} else {
return null;
}
}
/**
* @group DDC-644
*/
public function testSkipUnknownColumns()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u');
$rsm->addFieldResult('u', 'u__id', 'id');
$rsm->addFieldResult('u', 'u__name', 'name');
$rsm->addScalarResult('foo1', 'foo');
$rsm->addScalarResult('bar2', 'bar');
$rsm->addScalarResult('baz3', 'baz');
$resultSet = array(array('u__id' => '1', 'u__name' => 'romanb', 'foo1' => 'A', 'bar2' => 'B', 'baz3' => 'C', 'foo' => 'bar'));
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new \Doctrine\ORM\Internal\Hydration\ScalarHydrator($this->_em);
$result = $hydrator->hydrateAll($stmt, $rsm);
}
/**
* Select u.id, u.name from CmsUser u
*/
public function testNewHydrationSimpleEntityQuery()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Kitpages\\DataGridBundle\\Tests\\TestEntities\\Node', 'node');
$rsm->addEntityResult('Kitpages\\DataGridBundle\\Tests\\TestEntities\\NodeAssoc', 'assoc');
$rsm->addFieldResult('node', 'id1', 'id');
$rsm->addFieldResult('node', 'user2', 'user');
$rsm->addFieldResult('node', 'content3', 'content');
$rsm->addFieldResult('node', 'parent_id4', 'parentId');
$rsm->addFieldResult('node', 'created_at5', 'createdAt');
$rsm->addFieldResult('assoc', 'id6', 'id');
$rsm->addFieldResult('assoc', 'name7', 'name');
$rsm->addScalarResult('intervals8', 'intervals');
// Faked result set
$resultSet = array(array('id1' => 11, 'user2' => 'toto', 'content3' => 'I like it!', 'parent_id4' => 0, 'created_at5' => new \DateTime('2010-04-21 12:14:20'), 'id6' => 1, 'name7' => 'tutu', 'intervals8' => 10));
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new DataGridHydrator($this->getEntityManager());
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->assertTrue(is_array($result));
$this->assertEquals(1, count($result));
$this->assertEquals(11, $result[0]['node.id']);
$this->assertEquals('toto', $result[0]['node.user']);
$this->assertEquals('I like it!', $result[0]['node.content']);
$this->assertEquals(0, $result[0]['node.parentId']);
$this->assertEquals(new \DateTime('2010-04-21 12:14:20'), $result[0]['node.createdAt']);
$this->assertEquals(1, $result[0]['assoc.id']);
$this->assertEquals('tutu', $result[0]['assoc.name']);
$this->assertEquals(10, $result[0]['intervals']);
}
/**
* Walks down a SelectStatement AST node, wrapping it in a SELECT DISTINCT.
* This method is for platforms which DO NOT support ROW_NUMBER.
*
* @param SelectStatement $AST
* @param bool $addMissingItemsFromOrderByToSelect
*
* @return string
*
* @throws \RuntimeException
*/
public function walkSelectStatementWithoutRowNumber(SelectStatement $AST, $addMissingItemsFromOrderByToSelect = true)
{
// We don't want to call this recursively!
if ($AST->orderByClause instanceof OrderByClause && $addMissingItemsFromOrderByToSelect) {
// In the case of ordering a query by columns from joined tables, we
// must add those columns to the select clause of the query BEFORE
// the SQL is generated.
$this->addMissingItemsFromOrderByToSelect($AST);
}
// Remove order by clause from the inner query
// It will be re-appended in the outer select generated by this method
$orderByClause = $AST->orderByClause;
$AST->orderByClause = null;
$innerSql = $this->getInnerSQL($AST);
$sqlIdentifier = $this->getSQLIdentifier($AST);
// Build the counter query
$sql = sprintf('SELECT DISTINCT %s FROM (%s) dctrn_result', implode(', ', $sqlIdentifier), $innerSql);
// http://www.doctrine-project.org/jira/browse/DDC-1958
$sql = $this->preserveSqlOrdering($sqlIdentifier, $innerSql, $sql, $orderByClause);
// 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);
}
// Restore orderByClause
$AST->orderByClause = $orderByClause;
return $sql;
}
/**
* @param array $associationMapping
* @param array $entityIds
* @param array $config
*
* @return array [['entityId' => mixed, 'relatedEntityId' => mixed], ...]
*/
public function getRelatedItemsIds($associationMapping, $entityIds, $config)
{
$limit = isset($config[ConfigUtil::MAX_RESULTS]) ? $config[ConfigUtil::MAX_RESULTS] : -1;
if ($limit > 0 && count($entityIds) > 1) {
$selectStmt = null;
$subQueries = [];
foreach ($entityIds as $id) {
$subQuery = $this->getRelatedItemsIdsQuery($associationMapping, [$id], $config);
$subQuery->setMaxResults($limit);
// We should wrap all subqueries with brackets for PostgreSQL queries with UNION and LIMIT
$subQueries[] = '(' . QueryUtils::getExecutableSql($subQuery) . ')';
if (null === $selectStmt) {
$mapping = QueryUtils::parseQuery($subQuery)->getResultSetMapping();
$selectStmt = sprintf('entity.%s AS entityId, entity.%s AS relatedEntityId', QueryUtils::getColumnNameByAlias($mapping, 'entityId'), QueryUtils::getColumnNameByAlias($mapping, 'relatedEntityId'));
}
}
$rsm = new ResultSetMapping();
$rsm->addScalarResult('entityId', 'entityId')->addScalarResult('relatedEntityId', 'relatedEntityId');
$qb = new SqlQueryBuilder($this->doctrineHelper->getEntityManager($associationMapping['targetEntity']), $rsm);
$qb->select($selectStmt)->from('(' . implode(' UNION ALL ', $subQueries) . ')', 'entity');
$rows = $qb->getQuery()->getScalarResult();
} else {
$query = $this->getRelatedItemsIdsQuery($associationMapping, $entityIds, $config);
if ($limit >= 0) {
$query->setMaxResults($limit);
}
$rows = $query->getScalarResult();
}
return $rows;
}
public function countRecord()
{
$resultMapping = new ResultSetMapping();
$resultMapping->addScalarResult('total', 'total');
$result = $this->execute('SELECT SUM(ID) AS total FROM ' . $this->getTable(), array(), $resultMapping);
return $result['total'];
}
public function getCompanyStats(int $companyId) : CompanyStatsDTO
{
$resultSetMapping = new ResultSetMapping();
$resultSetMapping->addScalarResult('sclr_0', 'totalActiveEmployees', 'integer')->addScalarResult('sclr_1', 'totalInactiveEmployees', 'integer');
$companyStatsArray = $this->getEntityManager()->createQueryBuilder()->addSelect('SUM(IF(Employee.isActive=1,1,0)) AS totalActiveEmployees')->addSelect('SUM(IF(Employee.isActive=0,1,0)) AS totalInactiveEmployees')->from(Employee::class, 'Employee')->where('Employee.company = :companyId')->setParameter('companyId', $companyId)->setMaxResults(1)->getQuery()->setResultSetMapping($resultSetMapping)->getArrayResult();
return new CompanyStatsDTO($companyStatsArray[0]['totalActiveEmployees'], $companyStatsArray[0]['totalInactiveEmployees']);
}
public function getStatsByMonth()
{
$rsm = new ResultSetMapping();
$rsm->addScalarResult('m', 'month');
$rsm->addScalarResult('y', 'year');
$rsm->addScalarResult('n', 'number');
$em = $this->getEntityManager();
$query = $em->createNativeQuery('
SELECT MONTH( jlm_core_calendar.dt ) AS m , YEAR( jlm_core_calendar.dt ) AS y, COUNT( transmitters_transmitters.id ) AS n
FROM transmitters_transmitters
LEFT JOIN transmitters_attributions ON transmitters_attributions.id = transmitters_transmitters.attribution_id
LEFT JOIN jlm_core_calendar ON jlm_core_calendar.dt = transmitters_attributions.creation
GROUP BY MONTH( jlm_core_calendar.dt ),YEAR(jlm_core_calendar.dt)
ORDER BY m,y
', $rsm);
return $query->getResult();
}
public function getRanking(Categoria $categoria = null, Usuario $usuario = null)
{
if (!$categoria) {
return false;
}
$sql = "\n SELECT p.id, p.nombre AS nombre, avg(v.posicion) AS posicion \n FROM participante p \n INNER JOIN categoria c\n ON c.id = p.categoria_id\n AND c.id = " . $categoria->getId() . "\n LEFT JOIN voto v \n ON v.participante_id = p.id \n ";
if ($usuario) {
$sql .= "AND v.usuario_id = '" . $usuario->getId() . "'";
}
$sql .= "\n GROUP BY p.id, p.nombre\n ORDER BY ISNULL(posicion), posicion, p.nombre\n ";
$rsm = new ResultSetMapping();
$rsm->addScalarResult('id', 'id');
$rsm->addScalarResult('nombre', 'nombre');
$rsm->addScalarResult('posicion', 'posicion');
$query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
return $query->getResult();
}
public function getMonthlySummaryByType($user_id, $operationType, $year)
{
if ($operationType == 'income') {
$operationType = 1;
} else {
$operationType = 3;
}
$sql = 'SELECT SUM(o.amount) AS total, YEAR(o.date) AS year, MONTH(o.date) AS month ' . 'FROM operation o ' . 'WHERE o.user_id = ? AND o.operation_type_id = ? AND YEAR(o.date) = ?' . 'GROUP BY YEAR(o.date), MONTH(o.date)';
$rsm = new ResultSetMapping();
$rsm->addScalarResult('total', 'total');
$rsm->addScalarResult('year', 'year');
$rsm->addScalarResult('month', 'month');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $user_id);
$query->setParameter(2, $operationType);
$query->setParameter(3, $year);
return $query->getResult();
}
public function countDifferencesBetweenEnvironment($source, $target)
{
$sql = 'select count(*) foundRows from (select r.ouuid from environment_revision e, revision r, content_type ct where e.environment_id in (' . $source . ' ,' . $target . ') and r.id = e.revision_id and ct.id = r.`content_type_id` and ct.deleted = 0 group by ct.id, r.ouuid, ct.orderKey having count(*) = 1 or max(r.`id`) <> min(r.`id`)) tmp';
$rsm = new ResultSetMapping();
$rsm->addScalarResult('foundRows', 'foundRows');
$query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
$foundRows = $query->getResult();
return $foundRows[0]['foundRows'];
}
/**
* @param AST\NewObjectExpression $newObjectExpression
*
* @return string The SQL.
*/
public function walkNewObject($newObjectExpression)
{
$sqlSelectExpressions = array();
$objIndex = $this->newObjectCounter++;
foreach ($newObjectExpression->args as $argIndex => $e) {
$resultAlias = $this->scalarResultCounter++;
$columnAlias = $this->getSQLColumnAlias('sclr');
switch (true) {
case ($e instanceof AST\NewObjectExpression):
$sqlSelectExpressions[] = $e->dispatch($this);
break;
default:
$sqlSelectExpressions[] = trim($e->dispatch($this)) . ' AS ' . $columnAlias;
break;
}
switch (true) {
case ($e instanceof AST\PathExpression):
$fieldName = $e->field;
$dqlAlias = $e->identificationVariable;
$qComp = $this->queryComponents[$dqlAlias];
$class = $qComp['metadata'];
$fieldType = $class->getTypeOfField($fieldName);
break;
case ($e instanceof AST\Literal):
switch ($e->type) {
case AST\Literal::BOOLEAN:
$fieldType = 'boolean';
break;
case AST\Literal::NUMERIC:
$fieldType = is_float($e->value) ? 'float' : 'integer';
break;
}
break;
default:
$fieldType = 'string';
break;
}
$this->scalarResultAliasMap[$resultAlias] = $columnAlias;
$this->rsm->addScalarResult($columnAlias, $resultAlias, $fieldType);
$this->rsm->newObjectMappings[$columnAlias] = array(
'className' => $newObjectExpression->className,
'objIndex' => $objIndex,
'argIndex' => $argIndex
);
}
return implode(', ', $sqlSelectExpressions);
}
/**
* @param $menu
* @return int
*/
public function fetchMaxOrder($menu)
{
$rsm = new ResultSetMapping();
$rsm->addScalarResult('max_order', 'max_order', 'integer');
$query = $this->getEntityManager()->createNativeQuery('SELECT MAX(`order`) AS max_order FROM cms_menu AS m WHERE menu = :menu', $rsm);
$query->setParameter('menu', $menu);
$res = $query->getOneOrNullResult();
return $res['max_order'];
}
public function getGeonamePOIsInSquareAction(Request $request, $long1, $lat1, $long2, $lat2)
{
// First we will protect us from repeated queries from the same user
$session = $this->get('session');
$noPolygonsUntil = $session->get('nopolygonsuntil');
$now = new \DateTime();
$session->set('nopolygonsuntil', $now->add(new \DateInterval('PT2S')));
if (isset($noPolygonsUntil) && $now < $noPolygonsUntil) {
$session->set('nopolygonsuntil', $now->add(new \DateInterval('PT2S')));
$response = new Response();
$response->setStatusCode(503);
return $response;
}
// Second, we want to normalize the window, to improve cache. This should be done outside controller
$long1 = ceil($long1 * 10) / 10;
$lat1 = ceil($lat1 * 10) / 10;
$long2 = floor($long2 * 10) / 10;
$lat2 = floor($lat2 * 10) / 10;
$em = $this->getDoctrine()->getManager();
$rsm = new ResultSetMapping();
$rsm->addScalarResult('point', 'point');
$rsm->addScalarResult('name', 'name');
$rsm->addScalarResult('fcode', 'fcode');
$query = $em->createNativeQuery('SELECT name, fcode, ST_AsGeoJSON(coordinate) as point ' . 'FROM public.geoname ' . "WHERE fcode like 'PPL%' " . 'AND st_within(geoname.coordinate, ST_MakeEnvelope(:long1,:lat1,:long2,:lat2,4326)) ' . 'ORDER BY st_distance(coordinate, ST_Centroid(ST_MakeEnvelope(:long1,:lat1,:long2,:lat2,4326))) ASC ' . 'LIMIT 500', $rsm);
$query->setParameter('long1', $long1);
$query->setParameter('lat1', $lat1);
$query->setParameter('long2', $long2);
$query->setParameter('lat2', $lat2);
$results = $query->getResult();
//dump($results); die();
$features = array();
foreach ($results as $res) {
$features[] = array('type' => 'Feature', 'properties' => array('name' => $res['name'], 'fcode' => $res['fcode']), 'geometry' => json_decode($res['point']));
/*
$features []= array(
'type'=>'Feature',
'geometry'=>json_decode( $res['point'] )
);*/
}
$obj = array('type' => 'FeatureCollection', 'features' => $features);
$response = new Response(json_encode($obj));
$response->headers->set('Content-Type', 'application/json');
return $response;
}
/**
* return number times one user is into project
*
* @param $project
* @return array
*/
public function isUserInProject($user, $project)
{
$em = $this->getEntityManager();
$rsm = new ResultSetMapping();
$rsm->addScalarResult('total', 'total');
$sql = "SELECT COUNT(*) as total FROM users_projects WHERE user_id = " . $user->getId() . " AND project_id = " . $project->getId();
$query = $em->createNativeQuery($sql, $rsm);
$result = $query->getResult();
return $result[0]['total'];
}
/**
* Checks whether the migration has been run before
*/
public function isExecuted()
{
$em = $this->container->get('Doctrine')->getManager();
$rsm = new ResultSetMapping();
$rsm->addScalarResult('executed', 'executed');
$query = $em->createNativeQuery("SELECT executed FROM datamigrations WHERE version=?", $rsm);
$query->setParameter(1, $this->version);
$result = $query->getResult();
return empty($result) ? false : $result[0]['executed'];
}
private function getFileIds($tagIds, $meta)
{
$sql = $this->craftSql($tagIds, $meta);
$rsm = new ResultSetMapping($this->em);
$rsm->addScalarResult("file_id", "id");
$list = $this->em->createNativeQuery($sql, $rsm)->getScalarResult();
// clean up the list so it's a flat array of integers
return array_map(function ($e) {
return (int) $e['id'];
}, $list);
}
