/**
* 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;
}
