/**
* Migrate data to plugin database from core table
*
* @param EntityManager $em
* @param OutputInterface $output
*
* @return void
*/
public function migrateData($em, $output)
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Newscoop\\CommunityTickerBundle\\Entity\\CommunityTickerEvent', 'e');
$rsm->addFieldResult('e', 'id', 'id');
$rsm->addFieldResult('e', 'event', 'event');
$rsm->addFieldResult('e', 'params', 'params');
$rsm->addFieldResult('e', 'created', 'created');
$rsm->addJoinedEntityResult('Newscoop\\Entity\\User', 'u', 'e', 'user');
$rsm->addFieldResult('u', 'Id', 'id');
$query = $em->createNativeQuery('SELECT e.id, e.event, e.params, e.created, u.Id FROM community_ticker_event e ' . 'LEFT JOIN liveuser_users u ON u.id = e.user_id', $rsm);
$events = $query->getArrayResult();
foreach ($events as $key => $event) {
$user = $em->getRepository('Newscoop\\Entity\\User')->findOneBy(array('id' => $event['user']['id']));
$existingEvent = $em->getRepository('Newscoop\\CommunityTickerBundle\\Entity\\CommunityTickerEvent')->findOneBy(array('created' => $event['created'], 'params' => $event['params']));
if (!$existingEvent) {
$newEvent = new CommunityTickerEvent();
$newEvent->setEvent($event['event']);
$newEvent->setParams($event['params'] != '[]' ? json_decode($event['params'], true) : array());
$newEvent->setCreated($event['created']);
$newEvent->setIsActive(true);
if ($user) {
$newEvent->setUser($user);
}
$em->persist($newEvent);
}
}
$em->flush();
$output->writeln('<info>Data migrated to plugin table!</info>');
$output->writeln('<info>Removing old table...</info>');
}
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']);
}
/**
* @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;
}
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'];
}
/**
* @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 getNotasPorPeriodo($periodo, $alumno)
{
$planestuRepository = $this->getDoctrine()->getRepository('AppBundle:Planestu');
$em = $this->getDoctrine()->getManager();
// Configuracion de la entidad a adoptar la consulta
$rsm = new ResultSetMapping();
$rsm->addEntityResult('AppBundle:Materias', 'u');
$rsm->addFieldResult('u', 'cmate', 'cmate');
$rsm->addFieldResult('u', 'nmate', 'nmate');
// plan de estudio exceptuando las materias excluidas
$query = $em->createNativeQuery('SELECT * FROM materias LEFT JOIN planestu
ON materias.cmate = planestu.cmate WHERE planestu.cgrupo=?
AND materias.cmate NOT IN (SELECT mateexclu.cmate FROM mateexclu WHERE calum =?
AND (cperi =? OR cperi =?)) ORDER BY orden', $rsm);
// set parametros al query
$query->setParameter(1, $alumno->getCgrupo()->getCgrupo());
$query->setParameter(2, $alumno->getCalum());
$query->setParameter(3, $periodo);
$query->setParameter(4, 'T');
$materias = $query->getResult();
$notasArray = array();
foreach ($materias as $materia) {
$nota = $this->getNota($materia->getCmate(), $alumno->getCalum(), $periodo);
$notasArray[$materia->getNmate()] = $nota;
}
return $notasArray;
}
/**
* @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'));
}
/**
*
* 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;
}
/**
* select u.name from CmsUser u where u.id = 1
*
* @dataProvider singleScalarResultSetProvider
*/
public function testHydrateSingleScalar($name, $resultSet)
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u');
$rsm->addFieldResult('u', 'u__id', 'id');
$rsm->addFieldResult('u', 'u__name', 'name');
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new \Doctrine\ORM\Internal\Hydration\SingleScalarHydrator($this->_em);
if ($name == 'result1') {
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->assertEquals('romanb', $result);
} else {
if ($name == 'result2') {
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->assertEquals(1, $result);
} else {
if ($name == 'result3' || $name == 'result4') {
try {
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->fail();
} catch (\Doctrine\ORM\NonUniqueResultException $e) {
}
}
}
}
}
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 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 $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 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();
}
/**
* 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'];
}
/**
* 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'];
}
public function hasPasswordExpired($userid, $em)
{
$settings = $em->getRepository("\\Application\\Entity\\Settings")->find(1);
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Application\\Entity\\User', 'u');
$rsm->addFieldResult('u', 'PASSWORDLASTCHANGED', 'passwordlastchanged');
$query = $em->createNativeQuery("SELECT PASSWORDLASTCHANGED FROM user " . " where DATEDIFF(CURDATE(),PASSWORDLASTCHANGED) > :num1 AND PK_USERID = :num2 ", $rsm);
$query->setParameter('num1', $settings->getPasswordExpireydays());
$query->setParameter('num2', $userid);
return count($query->getResult()) > 0 ? true : false;
}
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);
}
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();
}
/**
* @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 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;
}
}
public function testHydrateColumn()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u');
$rsm->addFieldResult('u', 'u__name', 'name');
// Faked result set
$resultSet = array(array('u__name' => 'romanb'), array('u__name' => 'jwage'));
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new ColumnHydrator($this->_em);
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->assertEquals(array('romanb', 'jwage'), $result);
}
/**
* Returns all competences linked to the specific matiere
*
* @param $matiereID
*/
public function findByExamenId($examenID)
{
$sql = "SELECT c.id, c.name " . "FROM c3csi_examen exam " . "LEFT JOIN c3csi_examen_rel_competence exam_comp ON exam_comp.examen_id = exam.id " . "LEFT JOIN c3csi_competence c ON c.id = exam_comp.competence_id " . "WHERE exam.id = ?";
$rsm = new ResultSetMapping();
$rsm->addEntityResult('SkillBundle\\Entity\\Competence', 'c');
$rsm->addFieldResult('c', 'id', 'id');
$rsm->addFieldResult('c', 'name', 'name');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $examenID);
$result = $query->getResult();
return $result;
}
public function pointIsInRegion($regionId, $point)
{
$rsm = new ResultSetMapping();
$rsm->addScalarResult('result', 'result');
$sql = "SELECT ST_Contains( polygon, ST_SetSRID(ST_MakePoint( :x, :y ),4326) ) as result FROM region WHERE id = :regionId";
$query = $this->em->createNativeQuery($sql, $rsm);
$query->setParameter(':x', $point->getX());
$query->setParameter(':y', $point->getY());
$query->setParameter(':regionId', $regionId);
$result = $query->getSingleResult();
return $result['result'];
}
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]);
}
/**
* @param Entity\Contact $contact
* @param Entity\Facebook $facebook
* @return bool
*/
public function isContactInFacebook(Entity\Contact $contact, Entity\Facebook $facebook)
{
$resultSetMap = new ResultSetMapping();
$resultSetMap->addEntityResult('Contact\\Entity\\Contact', 'c');
/**
* Don't map the contact_id because that will overwrite the existing contact object leaving an emtpy one
*/
$resultSetMap->addFieldResult('c', 'email', 'email');
$queryInString = sprintf("SELECT %s FROM %s WHERE %s", $facebook->getContactKey(), $facebook->getFromClause(), $facebook->getWhereClause());
$query = $this->getEntityManager()->createNativeQuery(sprintf("SELECT email FROM contact WHERE contact_id = %s AND contact_id IN (%s) AND date_end IS NULL", $contact->getId(), $queryInString), $resultSetMap);
return sizeof($query->getResult()) > 0;
}
protected function execute(InputInterface $input, OutputInterface $output)
{
/** @var ObjectManager $em */
$em = $this->getContainer()->get('doctrine')->getManager();
$currentUtc = (new \DateTime())->setTimezone(new \DateTimeZone('UTC'));
$calculationDate = clone $currentUtc;
$calculationDate->modify('midnight');
$systemOffset = (new \DateTime())->getOffset();
$rsm = new ResultSetMapping();
$rsm->addEntityResult('AcmeUserBundle:User', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'timezone', 'timezone');
$str = 'select u.id as id,
u.timezone
from ed_users u
WHERE DATE_ADD("' . $currentUtc->format('Y-m-d H:i') . '", INTERVAL u.timezone HOUR) > "' . $calculationDate->format('Y-m-d H:i') . '" AND
NOT EXISTS(SELECT * FROM ed_progress_statistics ps WHERE ps.user_id=u.id AND DATE_ADD(ps.calculated_at, INTERVAL (u.timezone - ' . $systemOffset / 3600 . ') HOUR) > "' . $calculationDate->format('Y-m-d H:i') . '")
';
$query = $em->createNativeQuery($str, $rsm);
$users = $query->getResult();
$dayStart = clone $calculationDate;
$dayStart->sub(new \DateInterval('P1D'));
$dayFinish = clone $calculationDate;
foreach ($users as $user) {
// $user = $em->find('AcmeUserBundle:User', $user->getId());
$progress = new ProgressStatistic();
$progress->setUser($user);
$queryBuilder = $em->createQueryBuilder();
$actions = $queryBuilder->from('AcmeEdelaBundle:UserAction', 'ua')->addSelect('uap.result as progress')->addSelect('IDENTITY(a.goal) as goal_id')->leftJoin('AcmeEdelaBundle:Action', 'a', Join::WITH, 'ua.action=a')->leftJoin('AcmeUserBundle:User', 'u', Join::WITH, 'ua.user=u')->leftJoin('AcmeEdelaBundle:UserActionProgress', 'uap', Join::WITH, 'uap.userAction=ua AND
DATE_ADD2(uap.createdAt, INTERVAL (u.timezone - ' . $systemOffset / 3600 . ') HOUR) > :dayStart AND
DATE_ADD2(uap.createdAt, INTERVAL (u.timezone - ' . $systemOffset / 3600 . ') HOUR) < :dayFinish
')->setParameter('dayFinish', $dayFinish)->setParameter('dayStart', $dayStart)->where('ua.user = :user')->setParameter('user', $user);
$orX = $queryBuilder->expr()->orX();
$orX->add($queryBuilder->expr()->gt('BIT_AND(ua.periodicity, :dayOfWeek)', '0'));
$orX->add($queryBuilder->expr()->eq('MOD(DATE_DIFF(ua.createdAt, :date), ua.periodicityInterval)', '0'));
$actions->andWhere($orX)->setParameter('date', $dayStart)->setParameter('dayOfWeek', (int) (1 << $dayStart->format('w')));
$actions->andWhere('ua.startAt < :dayFinish');
var_dump($actions->getQuery()->getSQL(), $dayFinish, $dayStart);
$actions = $actions->getQuery()->getArrayResult();
var_dump($actions);
$progress->setTotalActions(count($actions));
$progress->setProgressedActions(count(array_filter($actions, function ($item) {
return (bool) $item['progress'];
})));
$goaledActions = count(array_filter($actions, function ($item) {
return (bool) $item['goal_id'];
}));
$progress->setEfficiency($progress->getProgressedActions() * 3 + $goaledActions);
$em->persist($progress);
}
$em->flush();
$output->writeln('Done');
}
public function emailDisponible($email, $idUsuario = '')
{
$rsm = new ResultSetMapping();
$rsm->addScalarResult('resp', 'resp');
$sql = "SELECT count (*) resp\n FROM sca_usuario\n WHERE sca_usuario.email = ?\n AND audit_deleted = false";
if ($idUsuario != '' && $idUsuario != null) {
$sql = $sql . ' AND usuario_codigo <> ' . $idUsuario;
}
$query = $this->em->createNativeQuery($sql, $rsm);
$query->setParameter(1, trim($email));
$x = $query->getResult();
return $x[0]['resp'];
}
protected function pointsAsPolygon($tmp_table_name)
{
$alias = 'as_text';
$sql = "SELECT ST_AsEWKB(pgr_pointsAsPolygon('SELECT 1::int4 AS id, ST_X(point)::float8 AS x, ST_Y(point)::float8 AS y FROM {$tmp_table_name} '::text)) AS {$alias}";
$rsm = new ResultSetMapping();
$rsm->addScalarResult($alias, $alias);
$query = $this->em->createNativeQuery($sql, $rsm);
$result = $query->getSingleResult();
$sqlExpr = $result[$alias];
$geom_type = \Doctrine\DBAL\Types\Type::getType('polygon');
$polygon = $geom_type->convertToPHPValue($sqlExpr, $this->em->getConnection()->getDatabasePlatform());
return $polygon;
}
/**
*
* 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();
}
public function findTopScoresForEntity($targetClassName, $limit, $reversed)
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult($targetClassName, 't');
$rsm->addFieldResult('score', 'vote', 'score');
$sql = sprintf('SELECT SUM(v.vote) AS score FROM votes v
INNER JOIN %s t ON v.target_id = t.id
GROUP BY v.target_id
HAVING score %s 0
ORDER BY score %s
LIMIT %d', $targetClassName, $reversed ? '<' : '>', $reversed ? 'ASC' : 'DESC', $limit);
$query = $this->_em->createNativeQuery($sql, $rsm);
return $query->getResult();
}
