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);
}
function findByEventsAndSpaces(array $events, array $spaces, $date_from = null, $date_to = null, $limit = null, $offset = null)
{
$map_function = function ($e) {
return $e->id;
};
$event_ids = array_map($map_function, $events);
$space_ids = array_map($map_function, $spaces);
$app = App::i();
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\\EventOccurrence', 'eo');
$rsm->addFieldResult('eo', 'id', 'id');
$rsm->addFieldResult('eo', 'starts_on', '_startsOn');
$rsm->addFieldResult('eo', 'until', '_until');
$rsm->addFieldResult('eo', 'starts_at', '_startsAt');
$rsm->addFieldResult('eo', 'ends_at', '_endsAt');
$rsm->addFieldResult('eo', 'rule', '_rule');
$rsm->addFieldResult('eo', 'space_id', 'spaceId');
$rsm->addFieldResult('eo', 'event_id', 'eventId');
$dql_limit = $dql_offset = '';
if ($limit) {
$dql_limit = 'LIMIT ' . $limit;
}
if ($offset) {
$dql_offset = 'OFFSET ' . $offset;
}
$strNativeQuery = "\n SELECT\n eo.*\n FROM\n event_occurrence eo\n \n WHERE eo.id IN (\n SELECT DISTINCT id FROM recurring_event_occurrence_for(:date_from, :date_to, 'Etc/UTC', NULL)\n WHERE space_id IN(:spaces)\n AND event_id IN(:events)\n ) \n\n {$dql_limit} {$dql_offset}\n\n ORDER BY\n eo.starts_on, eo.starts_at";
$query = $app->em->createNativeQuery($strNativeQuery, $rsm);
if ($app->config['app.useEventsCache']) {
$query->useResultCache(true, $app->config['app.eventsCache.lifetime']);
}
$query->setParameters(array('date_from' => $date_from, 'date_to' => $date_to, 'spaces' => $space_ids, 'events' => $event_ids));
$result = $query->getResult();
return $result ? $result : array();
}
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();
}
/**
* @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;
}
private function getSubscritpionsSections($subscriptionId)
{
$sectionsCount = $this->em->getRepository('Newscoop\\Subscription\\Section')->createQueryBuilder('s');
// select sections older than 48 hours
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addEntityResult('\\Newscoop\\Subscription\\Section', 'ss');
$rsm->addFieldResult('ss', 'StartDate', 'startDate');
$rsm->addFieldResult('ss', 'PaidDays', 'paidDays');
$rsm->addFieldResult('ss', 'IdSubscription', 'subscription');
$rsm->addFieldResult('ss', 'NoticeSent', 'noticeSent');
$rsm->addScalarResult('formated_start_date', 'formated_start_date');
$rsm->addScalarResult('to_days_start_date', 'to_days_start_date');
$rsm->addScalarResult('to_days_now', 'to_days_now');
$rsm->addScalarResult('formated_end_date', 'formated_end_date');
$sql = "SELECT \r\n ss.StartDate, \r\n DATE_FORMAT(ss.StartDate, '%M %D, %Y') AS formated_start_date, \r\n ss.PaidDays, \r\n TO_DAYS(ss.StartDate) AS to_days_start_date, \r\n TO_DAYS(now()) AS to_days_now, \r\n DATE_FORMAT(ADDDATE(ss.StartDate, INTERVAL ss.PaidDays DAY), '%M %D, %Y') AS formated_end_date\r\n FROM \r\n SubsSections ss\r\n WHERE \r\n ss.IdSubscription = {$subscriptionId} \r\n AND \r\n ss.NoticeSent = 'N' \r\n GROUP BY \r\n ss.StartDate, ss.PaidDays\r\n ";
$query = $this->em->createNativeQuery($sql, $rsm);
return $query->getResult();
}
private static function getAllChildFilePathsRecursive($base_path, $folder_id, $db, array $paths = null)
{
$result_mapping = new Doctrine\ORM\Query\ResultSetMapping();
$result_mapping->addEntityResult('App_Model_File', 'f');
$result_mapping->addFieldResult('f', 'id', 'id');
$result_mapping->addFieldResult('f', 'extension', 'extension');
$query = $db->createNativeQuery('SELECT id, extension FROM file WHERE folder_id = ?', $result_mapping);
$query->setParameters(array(1 => $folder_id));
$files = $query->getResult();
if (!$paths) {
$paths = array();
}
foreach ($files as $file) {
$paths[] = $file->getFullPath($base_path);
}
unset($files);
$result_mapping = new Doctrine\ORM\Query\ResultSetMapping();
$result_mapping->addScalarResult('id', 'id');
$query = $db->createNativeQuery('SELECT id FROM folder WHERE parent_id = ?', $result_mapping);
$query->setParameters(array(1 => $folder_id));
$query_results = $query->getResult();
foreach ($query_results as $r) {
$paths = self::getAllChildFilePathsRecursive($base_path, $r['id'], $db, $paths);
}
return $paths;
}
public function findOccurrences($date_from = null, $date_to = null, $limit = null, $offset = null)
{
$app = App::i();
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\\EventOccurrence', 'e');
$rsm->addFieldResult('e', 'id', 'id');
$rsm->addFieldResult('e', 'starts_on', '_startsOn');
$rsm->addFieldResult('e', 'until', '_until');
$rsm->addFieldResult('e', 'starts_at', '_startsAt');
$rsm->addFieldResult('e', 'ends_at', '_endsAt');
$rsm->addFieldResult('e', 'space_id', 'spaceId');
$dql_limit = $dql_offset = '';
if ($limit) {
$dql_limit = 'LIMIT ' . $limit;
}
if ($offset) {
$dql_offset = 'OFFSET ' . $offset;
}
$strNativeQuery = "\n SELECT\n nextval('occurrence_id_seq'::regclass) as id,\n starts_on, until, starts_at, ends_at, space_id\n FROM\n recurring_event_occurrence_for(:date_from, :date_to, 'Etc/UTC', NULL) eo\n WHERE eo.event_id = :event_id\n\n {$dql_limit} {$dql_offset}\n\n ORDER BY\n eo.starts_on, eo.starts_at";
$query = $app->em->createNativeQuery($strNativeQuery, $rsm);
if ($app->config['app.useEventsCache']) {
$query->useResultCache(true, $app->config['app.eventsCache.lifetime']);
}
$query->setParameters(['date_from' => $date_from, 'date_to' => $date_to, 'event_id' => $this->id]);
$result = $query->getResult();
return $result ? $result : [];
}
public function findByDateInterval($date_from = null, $date_to = null, $limit = null, $offset = null, $only_ids = false)
{
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\\Event', 'e');
if ($only_ids) {
$select = 'id';
$rsm->addFieldResult('e', 'id', 'id');
} else {
$select = '*';
$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;
}
$strNativeQuery = "\n SELECT\n e.{$select}\n FROM\n event e\n JOIN \n event_occurrence eo \n ON eo.event_id = e.id \n AND eo.space_id IN (SELECT id FROM space WHERE status > 0)\n AND eo.status > 0\n\n WHERE\n e.status > 0 AND\n e.id IN (\n SELECT \n event_id \n FROM \n recurring_event_occurrence_for(:date_from, :date_to, 'Etc/UTC', NULL) \n WHERE \n space_id IN (SELECT id FROM space WHERE status > 0)\n )\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(['date_from' => $date_from, 'date_to' => $date_to]);
if ($only_ids) {
$result = array_map(function ($e) {
return $e['e_id'];
}, $query->getScalarResult());
} else {
$result = $query->getResult();
}
$this->_em->clear();
return $result;
}
/**
* Intoarce lista parintilor in ordine de jos in sus.
* IMPORTANT: Procedura intoarce pe ultima pozitie si categoria data ca parametru
* @return \NeoMvc\Models\Entities\Category
*/
public function getParents($id_category)
{
$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addEntityResult("Entities:Category", "c");
$rsm->addFieldResult("c", "id_category", "id_category");
$rsm->addFieldResult("c", "category_name", "name");
$rsm->addFieldResult("c", "id_parent", "id_parent");
$rsm->addFieldResult("c", "slug", "slug");
$rsm->addScalarResult("v_depth", "depth");
$rsm->addFieldResult("c", "thumb", "thumb");
$rsm->addFieldResult("c", "cover", "cover");
$query = $this->em->createNativeQuery("call get_root_categories(:id_category)", $rsm);
$query->setParameter(":id_category", $id_category);
$query->setResultCacheDriver(new \Doctrine\Common\Cache\ApcCache());
$query->useResultCache(true, 3600);
$categories = $query->getResult();
return $categories;
}
