/** * Fetch data from DB */ protected function getDBitems() { // Check valid NEXT DATE icagendaEventsData::getNext(); $app = JFactory::getApplication(); $jinput = $app->input; $params = $app->getParams(); // Get Settings $filterTime = $params->get('time', 1); $jlayout = JRequest::getCmd('layout', ''); $layouts_array = array('event', 'registration', 'actions'); $layout = in_array($jlayout, $layouts_array) ? $jlayout : ''; // Set vars $nodate = '0000-00-00 00:00:00'; $eventTimeZone = null; $datetime_today = JHtml::date('now', 'Y-m-d H:i:s'); // Joomla Time Zone $date_today = JHtml::date('now', 'Y-m-d'); // Joomla Time Zone $time_today = JHtml::date('now', 'H:i:s'); // Joomla Time Zone // Get List Type option (list of events / list of dates) $allDatesDisplay = $this->options['datesDisplay']; // Preparing connection to db $db = Jfactory::getDbo(); // Preparing the query $query = $db->getQuery(true); // Selectable items $query->select('e.*, e.place as place_name, e.coordinate as coordinate, e.lat as lat, e.lng as lng, c.id as cat_id, c.title as cat_title, c.color as cat_color, c.desc as cat_desc, c.alias as cat_alias'); // join $query->from('`#__icagenda_events` AS e'); $query->leftJoin('`#__icagenda_category` AS c ON c.id = e.catid'); $query->where('c.state = 1'); // Where (filters) $filters = $this->filters; $where = 'e.state = ' . $filters['state']; $user = JFactory::getUser(); $userLevels = $user->getAuthorisedViewLevels(); $userGroups = $user->groups; $groupid = JComponentHelper::getParams('com_icagenda')->get('approvalGroups', array("8")); $groupid = is_array($groupid) ? $groupid : array($groupid); // Test if user login have Approval Rights if (!array_intersect($userGroups, $groupid) && !in_array('8', $userGroups)) { $where .= ' AND e.approval <> 1'; } else { $where .= ' AND e.approval < 2'; } // ACCESS Filtering (if not list, use layout access control (event, registration)) if (!$layout && !in_array('8', $userGroups)) { $useraccess = implode(', ', $userLevels); $where .= ' AND e.access IN (' . $useraccess . ')'; } // LANGUAGE Filtering $where .= ' AND (e.language in (' . $db->quote(JFactory::getLanguage()->getTag()) . ',' . $db->quote('*') . '))'; unset($filters['state']); $k = '0'; $this_id = null; if (isset($filters)) { foreach ($filters as $k => $v) { // normal cases if ($k != 'key' && $k != 'next' && $k != 'e.catid' && $k != 'id') { $where .= ' AND ' . $k . ' LIKE "%' . $v . '%"'; } // in case of search if ($k == 'key') { $keys = explode(' ', $v); foreach ($keys as $ke) { $where .= ' AND (e.title LIKE \'%' . $ke . '%\' OR '; $where .= ' e.desc LIKE \'%' . $ke . '%\' OR '; $where .= ' e.address LIKE \'%' . $ke . '%\' OR '; $where .= ' e.place LIKE \'%' . $ke . '%\' OR '; $where .= ' c.title LIKE \'%' . $ke . '%\')'; } } // in the case of category $mcatidtrue = $this->options['mcatid']; if (!is_array($mcatidtrue)) { $catold = $mcatidtrue; $mcatid = array($mcatidtrue); } else { $catold = '0'; $mcatid = $mcatidtrue; } if (!in_array('0', $mcatid) || $catold != 0) { if ($k == 'e.catid') { if (!is_array($v)) { $v = array('' . $v . ''); } $v = implode(', ', $v); $where .= ' AND ' . $k . ' IN (' . $v . ')'; } } // in case of id if ($k == 'id') { //check if ID is a number if (is_numeric($v)) { $this_id = (int) $v; // if event id is set in url $where .= ' AND e.id=' . $v; } else { //ERROR Message } } } } // Features - extract the number of displayable icons per event $query->select('feat.count AS features'); $sub_query = $db->getQuery(true); $sub_query->select('fx.event_id, COUNT(*) AS count'); $sub_query->from('`#__icagenda_feature_xref` AS fx'); $sub_query->innerJoin("`#__icagenda_feature` AS f ON fx.feature_id=f.id AND f.state=1 AND f.icon<>'-1'"); $sub_query->group('fx.event_id'); $query->leftJoin('(' . (string) $sub_query . ') AS feat ON e.id=feat.event_id'); // Filter by Features if (!$layout) { $query->where(icagendaEventsData::getFeaturesFilter()); } // Registrations total $query->select('r.count AS registered'); $sub_query = $db->getQuery(true); $sub_query->select('r.eventid, sum(r.people) AS count'); $sub_query->from('`#__icagenda_registration` AS r'); $get_date = JRequest::getVar('date', ''); if ($get_date) { $ex = explode('-', $get_date); if (strlen(iCDate::dateToNumeric($get_date)) != '12') { $event_url = JURI::getInstance()->toString(); $cleanurl = preg_replace('/&date=[^&]*/', '', $event_url); $cleanurl = preg_replace('/\\?date=[^\\?]*/', '', $cleanurl); // redirect and remove date var, if not correctly set // $app->redirect($cleanurl , JText::_( 'COM_ICAGENDA_ERROR_URL_DATE_NOT_FOUND' )); $app->redirect($cleanurl); return false; } if (count($ex) == 5) { $dateday = $ex['0'] . '-' . $ex['1'] . '-' . $ex['2'] . ' ' . $ex['3'] . ':' . $ex['4'] . ':00'; $sub_query->where('r.date = ' . $db->q($dateday)); } } $sub_query->where('r.state > 0'); $sub_query->group('r.eventid'); $query->leftJoin('(' . (string) $sub_query . ') AS r ON e.id=r.eventid'); if (!$layout) { $number_per_page = $this->options['number']; $orderdate = $this->options['orderby']; $getpage = JRequest::getVar('page', '1'); $start = $number_per_page * ($getpage - 1); $all_dates_with_id = icagendaEventsData::getAllDates(); $count_all_dates = count($all_dates_with_id); // Set list of PAGE:IDS $pages = ceil($count_all_dates / $number_per_page); $list_id = array(); for ($n = 1; $n <= $pages; $n++) { $dpp_array = array(); $page_nb = $number_per_page * ($n - 1); $dates_per_page = array_slice($all_dates_with_id, $page_nb, $number_per_page, true); foreach ($dates_per_page as $dpp) { $dpp_alldates_array = explode('_', $dpp); $dpp_date = $dpp_alldates_array['0']; $dpp_id = $dpp_alldates_array['1']; $dpp_array[] = $dpp_id; } $list_id[] = implode(', ', $dpp_array) . '::' . $n; } $this_ic_ids = ''; if ($list_id) { foreach ($list_id as $a) { $ex_listid = explode('::', $a); $ic_page = $ex_listid[1]; $ic_ids = $ex_listid[0]; if ($ic_page == $getpage) { $this_ic_ids = $ic_ids ? $ic_ids : '0'; } } if ($this_ic_ids) { $where .= ' AND (e.id IN (' . $this_ic_ids . '))'; } else { return false; // No Event (if 'All Dates' option selected) } } } // Query $where list $query->where($where); $db->setQuery($query); $loaddb = $db->loadObjectList(); $registrations = icagendaEventsData::registeredList($this_id); // Extract the feature details, if needed foreach ($loaddb as $record) { if (is_null($record->features)) { $record->features = array(); } else { $query = $db->getQuery(true); $query->select('DISTINCT f.icon, f.icon_alt'); $query->from('`#__icagenda_feature_xref` AS fx'); $query->innerJoin("`#__icagenda_feature` AS f ON fx.feature_id=f.id AND f.state=1 AND f.icon<>'-1'"); $query->where('fx.event_id=' . $record->id); $query->order('f.ordering DESC'); // Order descending because the icons are floated right $db->setQuery($query); $record->features = $db->loadObjectList(); } // if (is_null($record->registered)) // { // $record->registered = array(); // } // else // { $record_registered = array(); foreach ($registrations as $reg_by_event) { $ex_reg_by_event = explode('@@', $reg_by_event); if ($ex_reg_by_event[0] == $record->id) { $record_registered[] = $ex_reg_by_event[0] . '@@' . $ex_reg_by_event[1] . '@@' . $ex_reg_by_event[2]; } } $record->registered = $record_registered; // } } if (!$layout && count($all_dates_with_id) > 0 || $layout) { return $loaddb; } }
/** * Get Records. * * @return object list. * @since 3.3.8 */ public function getRecords() { // Get the current user for authorisation checks $user = JFactory::getUser(); // Get Params for current view $app = JFactory::getApplication(); $params = $app->getParams(); // Select the required fields from the table. $db = JFactory::getDBO(); $query = $db->getQuery(true); $query->select('e.*')->from($db->qn('#__icagenda_events') . ' AS e'); // Join over the language $query->select('l.title AS language_title')->join('LEFT', $db->quoteName('#__languages') . ' AS l ON l.lang_code = e.language'); // Join over the users for the checked out user. // $query->select('uc.name AS editor'); // $query->join('LEFT', '#__users AS uc ON uc.id=e.checked_out'); // Join over the asset groups. $query->select('ag.title AS access_level')->join('LEFT', '#__viewlevels AS ag ON ag.id = e.access'); // Join the category $query->select('c.title AS category, c.color AS catcolor'); $query->join('LEFT', '#__icagenda_category AS c ON c.id = e.catid'); $query->where('c.state = 1'); // Join over the users for the author. // $query->select('ua.name AS author_name, ua.username AS author_username') // ->join('LEFT', '#__users AS ua ON ua.id = e.created_by'); // Filter by published state $query->where('e.state = 1'); // Event is approved $query->where('e.approval <> 1'); // Filter by access level. $access_levels = implode(',', $user->getAuthorisedViewLevels()); $query->where('e.access IN (' . $db->q($access_levels) . ')'); // ->where('c.access IN (' . $db->q($access_levels) . ')'); // To be added later, when access integrated to category // Filter by language $query->where('e.language in (' . $db->q(JFactory::getLanguage()->getTag()) . ',' . $db->q('*') . ')'); // Filter by Features $query->where(icagendaEventsData::getFeaturesFilter()); // Filter by dates $dates_filter = $params->get('time', 1); // Default Current and Upcoming Events // Get today date and datetime based on Joomla Config Timezone. $datetime_today = JHtml::date('now', 'Y-m-d H:i:s'); $date_today = JHtml::date('now', 'Y-m-d'); if (!empty($dates_filter)) { // COM_ICAGENDA_OPTION_TODAY_AND_UPCOMING if ($dates_filter == '1') { $where_current_upcoming = $db->qn('e.next') . ' >= ' . $db->q($date_today); $where_current_upcoming .= ' OR (' . $db->qn('e.next') . ' < ' . $db->q($datetime_today) . ' AND ' . $db->qn('e.startdate') . ' <> "0000-00-00 00:00:00" AND ' . $db->qn('e.enddate') . ' > ' . $db->q($datetime_today) . ')'; $query->where($where_current_upcoming); } elseif ($dates_filter == '2') { $where_past = '('; // Period dates with no weekdays filter $where_past .= $db->qn('e.next') . ' < ' . $db->q($datetime_today) . ')'; $where_past .= ' AND (' . $db->qn('e.enddate') . ' < ' . $db->q($datetime_today); $where_past .= ' )'; $query->where($where_past); } elseif ($dates_filter == '3') { $where_upcoming = '('; $where_upcoming .= $db->qn('e.next') . ' > ' . $db->q($datetime_today); $where_upcoming .= ' )'; $query->where($where_upcoming); } elseif ($dates_filter == '4') { $where_today = '( '; // One day dates filter $where_today .= ' ('; $where_today .= ' (' . $db->qn('e.next') . ' >= ' . $db->q($datetime_today) . ')'; $where_today .= ' AND (' . $db->qn('e.next') . ' < ' . $db->q($date_today) . ' + INTERVAL 1 DAY)'; $where_today .= ' )'; // Period dates with no weekdays filter $where_today .= ' OR ( '; $where_today .= ' (' . $db->qn('e.next') . ' > ' . $db->q($date_today) . ')'; $where_today .= ' AND (' . $db->qn('e.weekdays') . ' = "")'; $where_today .= ' AND ' . $db->qn('e.enddate') . ' <> "0000-00-00 00:00:00" AND (' . $db->qn('e.enddate') . ' >= ' . $db->q($date_today) . ')'; $where_today .= ' AND ' . $db->qn('e.startdate') . ' <> "0000-00-00 00:00:00" AND (' . $db->qn('e.startdate') . ' < ' . $db->q($date_today) . ')'; $where_today .= ' )'; $where_today .= ' )'; $query->where($where_today); } } // Order Next Date DESC $orderby = $params->get('orderby', 2); // Default ASC $ordering = $orderby == 1 ? 'DESC' : 'ASC'; $query->order('e.next ' . $ordering); // Tell the database connector what query to run. $db->setQuery($query); // Invoke the query or data retrieval helper. $db_list = $db->loadObjectList(); return $db_list; }