/** * 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; } }
/** * Build an SQL query to load the list data. * * @return JDatabaseQuery * @since 3.4.0 */ protected function getListQuery() { // Get the current user for authorisation checks $user = JFactory::getUser(); // Create a new query object. $db = $this->getDbo(); $query = $db->getQuery(true); // Select the required fields from the table. $query->select($this->getState('list.select', 'e.*')); $query->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.id AS cat_id, c.title AS cat_title, c.color AS cat_color, c.desc AS cat_desc, 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'); // 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'); // Join Total of registrations $query->select('r.count AS registered'); $sub_query = $db->getQuery(true); $sub_query->select('r.state, r.date, r.eventid, sum(r.people) AS count'); $sub_query->from('`#__icagenda_registration` AS r'); $sub_query->where('r.state > 0'); $sub_query->group('r.date, r.eventid'); $query->leftJoin('(' . (string) $sub_query . ') AS r ON ((e.next = r.date OR r.date = "") AND e.id = r.eventid)'); // 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 $published = $this->getState('filter.state'); if (is_numeric($published)) { $query->where('e.state = ' . (int) $published); } elseif ($published === '') { $query->where('(e.state IN (0, 1))'); } $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)) { $query->where('e.approval <> 1'); } else { $query->where('e.approval < 2'); } // Filter by access level. $access = $this->getState('filter.access'); if (!empty($access) && !in_array('8', $userGroups)) { $access_levels = implode(',', $user->getAuthorisedViewLevels()); $query->where('e.access IN (' . $access_levels . ')'); // ->where('c.access IN (' . $access_levels . ')'); // To be added later, when access integrated to category } // Filter by language if ($this->getState('filter.language')) { $query->where('e.language in (' . $db->q(JFactory::getLanguage()->getTag()) . ',' . $db->q('*') . ')'); } // Filter by search in title // $search = $this->getState('filter.search'); // if (!empty($search)) // { // if (stripos($search, 'id:') === 0) // { // $query->where('e.id = '.(int) substr($search, 3)); // } // else // { // $search = $db->Quote('%'.$db->escape($search, true).'%'); // $query->where('( e.title LIKE '.$search.' OR e.username LIKE '.$search.' OR e.id LIKE '.$search.' OR e.email LIKE '.$search.' OR e.file LIKE '.$search.' OR e.place LIKE '.$search.' OR e.city LIKE '.$search.' OR e.country LIKE '.$search.' OR e.desc LIKE '.$search.' OR c.title LIKE '.$search.')'); // } // } // Filter by categories. $categoryId = $this->getState('filter.category_id'); if (is_numeric($categoryId) && !empty($categoryId)) { $query->where('e.catid = ' . $categoryId . ''); } elseif (is_array($categoryId) && !empty($categoryId) && !in_array('0', $categoryId)) { JArrayHelper::toInteger($categoryId); $categoryId = implode(',', $categoryId); $query->where('e.catid IN (' . $categoryId . ')'); } // Filter by Dates $dates_filter = $this->getState('filter.upcoming', '0'); // 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'); $all_dates_with_id = icagendaEventsData::getAllDates($dates_filter, '2', $this->state->get('list.direction'), 'no'); $dpp_array = $dpp_dates = array(); foreach ($all_dates_with_id as $dpp) { $dpp_alldates_array = explode('_', $dpp); $dpp_date = $dpp_alldates_array['0']; $dpp_id = $dpp_alldates_array['1']; $dpp_dates[] = $dpp_date; $dpp_array[] = $dpp_id; } $list_id = implode(', ', $dpp_array); if (count($dpp_array)) { $query->where('e.id IN (' . $list_id . ')'); } else { return false; } // Add the list ordering clause. $orderCol = $this->state->get('list.ordering'); $orderDirn = $this->state->get('list.direction'); if ($orderCol && $orderDirn) { $query->order($db->escape($orderCol . ' ' . $orderDirn)); } return $query; }
/** * Execute and display a template script. * * @param string $tpl The name of the template file to parse; automatically searches through the template paths. * * @return mixed A string if successful, otherwise a Error object. */ public function display($tpl = null) { $app = JFactory::getApplication(); $document = JFactory::getDocument(); $this->params = $app->getParams(); $params = $this->params; // For Dev. $time_loading = $params->get('time_loading', ''); if ($time_loading) { $starttime_list = iCLibrary::getMicrotime(); } // loading data $this->data = $this->getModel()->getData(); $this->getAllDates = icagendaEventsData::getAllDates(); $this->form = $this->getModel()->getForm(); // Registration Form $this->state = $this->get('State'); //Following variables used more than once // $this->sortColumn = $this->state->get('list.ordering'); // $this->sortDirection = $this->state->get('list.direction'); $this->searchterms = $this->state->get('filter.search'); // Menu Options $this->atlist = $params->get('atlist', 0); $this->template = $params->get('template'); $this->title = $params->get('title'); $this->number = $params->get('number', 5); $this->orderby = $params->get('orderby', 2); $this->time = $params->get('time', 1); // Component Options $this->iconPrint_global = $params->get('iconPrint_global', 0); $this->iconAddToCal_global = $params->get('iconAddToCal_global', 0); $this->iconAddToCal_options = $params->get('iconAddToCal_options', 0); $this->copy = $params->get('copy'); $this->navposition = $params->get('navposition', 1); $this->arrowtext = $params->get('arrowtext', 1); $this->GoogleMaps = $params->get('GoogleMaps', 1); $this->pagination = $params->get('pagination', 1); $this->day_display_global = $params->get('day_display_global', 1); $this->month_display_global = $params->get('month_display_global', 1); $this->year_display_global = $params->get('year_display_global', 1); $this->time_display_global = $params->get('time_display_global', 0); $this->venue_display_global = $params->get('venue_display_global', 1); $this->city_display_global = $params->get('city_display_global', 1); $this->country_display_global = $params->get('country_display_global', 1); $this->shortdesc_display_global = $params->get('shortdesc_display_global', ''); $this->statutReg = $params->get('statutReg', 0); $this->dates_display = $params->get('datesDisplay', 1); $this->reg_captcha = $params->get('reg_captcha', 0); $this->reg_form_validation = $params->get('reg_form_validation', ''); $this->cat_description = $params->get('displayCatDesc_menu', 'global') == 'global' ? $params->get('CatDesc_global', '0') : $params->get('displayCatDesc_menu', ''); $cat_options = $params->get('displayCatDesc_menu', 'global') == 'global' ? $params->get('CatDesc_checkbox', '') : $params->get('displayCatDesc_checkbox', ''); $this->cat_options = is_array($cat_options) ? $cat_options : array(); $this->pageclass_sfx = htmlspecialchars($params->get('pageclass_sfx')); // Check for errors. if (count($errors = $this->get('Errors'))) { JError::raiseError(500, implode("\n", $errors)); return false; } $vcal = $app->input->get('vcal'); if ($vcal) { $tpl = 'vcal'; } // Process the content plugins. JPluginHelper::importPlugin('content'); if (version_compare(JVERSION, '3.0', 'ge')) { $this->dispatcher = JEventDispatcher::getInstance(); } else { $this->dispatcher = JDispatcher::getInstance(); } $eventid = $app->input->get('id'); if ($eventid) { // Set Item Object $this_item = (array) $this->data->items; $item = array_shift($this_item); $this->actions = $this->dispatcher->trigger('onRegistrationActions', array('com_icagenda.actions', &$item, &$this->params)); } $this->_prepareDocument(); $isVcal = JRequest::getVar('vcal', ''); if (!$isVcal) { icagendaInfo::commentVersion(); } // Loads jQuery Library if (version_compare(JVERSION, '3.0', 'lt')) { // Joomla 2.5 JHtml::stylesheet('com_icagenda/icagenda-front.j25.css', false, true); JHtml::_('behavior.mootools'); // load jQuery, if not loaded before $scripts = array_keys($document->_scripts); $scriptFound = false; for ($i = 0; $i < count($scripts); $i++) { if (stripos($scripts[$i], 'jquery.min.js') !== false || stripos($scripts[$i], 'jquery.js') !== false) { $scriptFound = true; } } // jQuery Library Loader if (!$scriptFound) { // load jQuery, if not loaded before if (!$app->get('jquery')) { $app->set('jquery', true); // Add jQuery Library $document->addScript('https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js'); JHtml::script('com_icagenda/jquery.noconflict.js', false, true); } } } else { JHtml::_('bootstrap.framework'); JHtml::_('jquery.framework'); } parent::display($tpl); // For Dev. if ($time_loading) { $endtime_list = iCLibrary::getMicrotime(); echo '<center style="font-size:8px;">Time to create page: ' . round($endtime_list - $starttime_list, 3) . ' seconds</center>'; } icagendaEvents::isListOfEvents(); $jlayout = JRequest::getCmd('layout', ''); $layouts_array = array('event', 'registration', 'actions'); $layout = in_array($jlayout, $layouts_array) ? $jlayout : ''; // Loading Script tipTip used for iCtips JHtml::script('com_icagenda/jquery.tipTip.js', false, true); if (!$layout || $layout == 'list') { // Add RSS Feeds $menu = $app->getMenu()->getActive()->id; $feed = 'index.php?option=com_icagenda&view=list&Itemid=' . (int) $menu . '&format=feed'; $rss = array('type' => 'application/rss+xml', 'title' => 'RSS 2.0'); $document->addHeadLink(JRoute::_($feed . '&type=rss'), 'alternate', 'rel', $rss); } }