function listIcalEventsByCat($catids, $showrepeats = false, $total = 0, $limitstart = 0, $limit = 0, $order = "rpt.startrepeat asc, rpt.endrepeat ASC, det.summary ASC", $filters = false, $extrafields = "", $extratables = "") { $db = JFactory::getDBO(); $user = JFactory::getUser(); // Use catid in accessibleCategoryList to pick up offsping too! $aid = null; $catidlist = implode(",", $catids); // process the new plugins // get extra data and conditionality from plugins $extrafields = ""; // must have comma prefix $extratables = ""; // must have comma prefix $extrawhere = array(); $extrajoin = array(); $needsgroup = false; if (!$this->cfg->get("showyearpast", 1)) { list($year, $month, $day) = JEVHelper::getYMD(); $startdate = JevDate::mktime(0, 0, 0, $month, $day, $year); $today = JevDate::strtotime("+0 days"); if ($startdate < $today) { $startdate = $today; } $startdate = JevDate::strftime('%Y-%m-%d 00:00:00', $startdate); $extrawhere[] = "rpt.endrepeat >= '{$startdate}'"; } if (!$filters) { $filters = jevFilterProcessing::getInstance(array("published", "justmine", "category", "search", "repeating")); $filters->setWhereJoin($extrawhere, $extrajoin); $needsgroup = $filters->needsGroupBy(); $dispatcher = JEventDispatcher::getInstance(); $dispatcher->trigger('onListIcalEvents', array(&$extrafields, &$extratables, &$extrawhere, &$extrajoin, &$needsgroup)); } else { $filters->setWhereJoin($extrawhere, $extrajoin); } $catwhere = "\n WHERE ev.catid IN(" . $this->accessibleCategoryList() . ")"; $params = JComponentHelper::getParams("com_jevents"); if ($params->get("multicategory", 0)) { $extrajoin[] = "\n #__jevents_catmap as catmap ON catmap.evid = rpt.eventid"; $extrajoin[] = "\n #__categories AS catmapcat ON catmap.catid = catmapcat.id"; $extrafields .= ", GROUP_CONCAT(DISTINCT catmap.catid SEPARATOR ',') as catids"; $extrawhere[] = " catmapcat.access IN (" . JEVHelper::getAid($user) . ")"; $extrawhere[] = " catmap.catid IN(" . $this->accessibleCategoryList() . ")"; $needsgroup = true; $catwhere = "\n WHERE 1 "; } $extrajoin = count($extrajoin) ? " \n LEFT JOIN " . implode(" \n LEFT JOIN ", $extrajoin) : ''; $extrawhere = count($extrawhere) ? ' AND ' . implode(' AND ', $extrawhere) : ''; if ($limit > 0 || $limitstart > 0) { if (empty($limitstart) || !$limitstart) { $limitstart = 0; } $rows_per_page = $limit; $limit = " LIMIT {$limitstart}, {$rows_per_page}"; } else { $limit = ""; } if ($order != "") { $order = (strpos($order, 'ORDER BY') === false ? " ORDER BY " : " ") . $order; } $user = JFactory::getUser(); if ($showrepeats) { $query = "SELECT ev.*, rpt.*, rr.*, det.* {$extrafields}" . "\n , YEAR(rpt.startrepeat) as yup, MONTH(rpt.startrepeat ) as mup, DAYOFMONTH(rpt.startrepeat ) as dup" . "\n , YEAR(rpt.endrepeat ) as ydn, MONTH(rpt.endrepeat ) as mdn, DAYOFMONTH(rpt.endrepeat ) as ddn" . "\n , HOUR(rpt.startrepeat) as hup, MINUTE(rpt.startrepeat ) as minup, SECOND(rpt.startrepeat ) as sup" . "\n , HOUR(rpt.endrepeat ) as hdn, MINUTE(rpt.endrepeat ) as mindn, SECOND(rpt.endrepeat ) as sdn" . "\n FROM #__jevents_vevent as ev" . "\n LEFT JOIN #__jevents_icsfile as icsf ON icsf.ics_id=ev.icsid" . "\n LEFT JOIN #__jevents_repetition as rpt ON rpt.eventid = ev.ev_id" . "\n LEFT JOIN #__jevents_rrule as rr ON rr.eventid = ev.ev_id" . "\n LEFT JOIN #__jevents_vevdetail as det ON det.evdet_id = rpt.eventdetail_id" . $extrajoin . $catwhere . $extrawhere . "\n AND icsf.state=1" . "\n AND ev.access IN (" . JEVHelper::getAid($user) . ")" . "\n GROUP BY rpt.rp_id" . $order . $limit; } else { // TODO find a single query way of doing this !!! $query = "SELECT MIN(rpt.rp_id) as rp_id FROM #__jevents_repetition as rpt " . "\n LEFT JOIN #__jevents_vevent as ev ON rpt.eventid = ev.ev_id" . "\n LEFT JOIN #__jevents_rrule as rr ON rr.eventid = ev.ev_id" . "\n LEFT JOIN #__jevents_vevdetail as det ON det.evdet_id = rpt.eventdetail_id" . "\n LEFT JOIN #__jevents_icsfile as icsf ON icsf.ics_id=ev.icsid" . $extrajoin . $catwhere . $extrawhere . "\n AND icsf.state=1" . "\n AND ev.access IN (" . JEVHelper::getAid($user) . ")" . "\n GROUP BY ev.ev_id"; $db->setQuery($query); //echo $db->explain(); $rplist = $db->loadColumn(); $rplist = implode(',', array_merge(array(-1), $rplist)); $query = "SELECT rpt.rp_id,ev.*, rpt.*, rr.*, det.* {$extrafields}" . "\n , YEAR(rpt.startrepeat) as yup, MONTH(rpt.startrepeat ) as mup, DAYOFMONTH(rpt.startrepeat ) as dup" . "\n , YEAR(rpt.endrepeat ) as ydn, MONTH(rpt.endrepeat ) as mdn, DAYOFMONTH(rpt.endrepeat ) as ddn" . "\n , HOUR(rpt.startrepeat) as hup, MINUTE(rpt.startrepeat ) as minup, SECOND(rpt.startrepeat ) as sup" . "\n , HOUR(rpt.endrepeat ) as hdn, MINUTE(rpt.endrepeat ) as mindn, SECOND(rpt.endrepeat ) as sdn" . "\n FROM #__jevents_repetition as rpt " . "\n LEFT JOIN #__jevents_vevent as ev ON rpt.eventid = ev.ev_id" . "\n LEFT JOIN #__jevents_icsfile as icsf ON icsf.ics_id=ev.icsid" . "\n LEFT JOIN #__jevents_rrule as rr ON rr.eventid = ev.ev_id" . "\n LEFT JOIN #__jevents_vevdetail as det ON det.evdet_id = rpt.eventdetail_id" . $extrajoin . $catwhere . $extrawhere . "\n AND rpt.rp_id IN({$rplist})" . "\n AND icsf.state=1" . "\n AND ev.access IN (" . JEVHelper::getAid($user) . ")" . ($needsgroup ? "\n GROUP BY rpt.rp_id" : "") . $order . $limit; } $cache = JEVHelper::getCache(JEV_COM_COMPONENT); $lang = JFactory::getLanguage(); $langtag = $lang->getTag(); $rows = $cache->call(array($this, '_cachedlistIcalEvents'), $query, $langtag); $dispatcher = JEventDispatcher::getInstance(); $dispatcher->trigger('onDisplayCustomFieldsMultiRowUncached', array(&$rows)); return $rows; }