public function feedMainCount() { $vks = new Vks(); $start = date_create($this->request->query->get('start')); $end = date_create($this->request->query->get('end')); $db = App::$instance->capsule->getConnection(); //get local events $cacheName = App::$instance->tbId . ".vks.events.calendar_counters.{$start->getTimestamp()}.{$end->getTimestamp()}"; $events = App::$instance->cache->get($cacheName); if (!$events) { $sql = "SELECT COUNT(*) as counter, date, status\n FROM " . App::$instance->db->prefix . $vks->getTable() . "\n WHERE start_date_time >= '" . $start->setTime(0, 0)->format("Y-m-d H:i:s") . "'\n AND start_date_time <= '" . $end->setTime(23, 59)->format("Y-m-d H:i:s") . "'\n AND status in (" . VKS_STATUS_PENDING . ", " . VKS_STATUS_APPROVED . ")\n GROUP BY date, status"; $raw_results = $db->select($db->raw($sql)); $result = array(); if (count($raw_results)) { foreach ($raw_results as $vksCounter) { $formatDate = date_create($vksCounter['date'])->format("Y-m-d"); if ($vksCounter['status'] === VKS_STATUS_APPROVED) { $result[$formatDate][VKS_STATUS_APPROVED] = $vksCounter['counter']; } else { $result[$formatDate][VKS_STATUS_PENDING] = $vksCounter['counter']; } } } while ($start < $end) { $formatDate = $start->format("Y-m-d"); if (!array_key_exists($formatDate, $result)) { $result[$formatDate][VKS_STATUS_APPROVED] = 0; $result[$formatDate][VKS_STATUS_PENDING] = 0; } else { //check not existed keys and fill them with zeros if (!isset($result[$formatDate][VKS_STATUS_APPROVED])) { $result[$formatDate][VKS_STATUS_APPROVED] = 0; } if (!isset($result[$formatDate][VKS_STATUS_PENDING])) { $result[$formatDate][VKS_STATUS_PENDING] = 0; } } $start->modify("+1 day"); } foreach ($result as $date => $counters) { $events[] = array('start' => $date, 'php_date' => $date, 'counters' => $counters); } //cache it $cachedObj = new CachedObject($events, ['tag.' . $cacheName, "tag." . App::$instance->tbId . ".vks.events.calendar_counters"]); App::$instance->cache->set($cacheName, $cachedObj, 3600 * 24 * 3); } print json_encode($events); //output }