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