/**
* Method to build the WHERE clause
*
* @access private
* @return array
*/
function _buildCategoryWhere()
{
$app =& JFactory::getApplication();
// Get the paramaters of the active menu item
$params =& $app->getParams();
$top_category = $params->get('top_category', 0);
$task = JRequest::getWord('task');
// First thing we need to do is to select only the published events
if ($task == 'archive') {
$where = ' WHERE a.published = -1 ';
} else {
$where = ' WHERE a.published = 1 ';
}
// only select events within specified dates. (chosen month)
$monthstart = mktime(0, 0, 1, strftime('%m', $this->_date), 1, strftime('%Y', $this->_date));
$monthend = mktime(0, 0, -1, strftime('%m', $this->_date) + 1, 1, strftime('%Y', $this->_date));
$filter_date_from = $this->_db->Quote(strftime('%Y-%m-%d', $monthstart));
$where .= ' AND DATEDIFF(IF (a.enddates IS NOT NULL AND a.enddates <> ' . $this->_db->Quote('0000-00-00') . ', a.enddates, a.dates), ' . $filter_date_from . ') >= 0';
$filter_date_to = $this->_db->Quote(strftime('%Y-%m-%d', $monthend));
$where .= ' AND DATEDIFF(a.dates, ' . $filter_date_to . ') <= 0';
if ($top_category) {
$children = eventlist_cats::getChilds($top_category);
if (count($children)) {
$where .= ' AND r.catid IN (' . implode(',', $children) . ')';
}
}
return $where;
}
/**
* Build the where clause
*
* @access private
* @return string
*/
function _buildEventListWhere()
{
$app =& JFactory::getApplication();
// Get the paramaters of the active menu item
$params =& $app->getParams();
$top_category = $params->get('top_category', 0);
$task = JRequest::getWord('task');
// First thing we need to do is to select only needed events
if ($task == 'archive') {
$where = ' WHERE a.published = -1';
} else {
$where = ' WHERE a.published = 1';
}
$filter = JRequest::getString('filter', '', 'request');
$filter_type = JRequest::getWord('filter_type', '', 'request');
$filter_continent = $app->getUserStateFromRequest('com_eventlist.search.filter_continent', 'filter_continent', '', 'string');
$filter_country = $app->getUserStateFromRequest('com_eventlist.search.filter_country', 'filter_country', '', 'string');
$filter_city = $app->getUserStateFromRequest('com_eventlist.search.filter_city', 'filter_city', '', 'string');
$filter_date_from = $app->getUserStateFromRequest('com_eventlist.search.filter_date_from', 'filter_date_from', '', 'string');
$filter_date_to = $app->getUserStateFromRequest('com_eventlist.search.filter_date_to', 'filter_date_to', '', 'string');
$filter_category = $app->getUserStateFromRequest('com_eventlist.search.filter_category', 'filter_category', 0, 'int');
$filter_category = $filter_category ? $filter_category : $top_category;
// no result if no filter:
if (!($filter || $filter_continent || $filter_country || $filter_city || $filter_date_from || $filter_date_to || $filter_category != $top_category)) {
return ' WHERE 0 ';
}
if ($filter) {
// clean filter variables
$filter = JString::strtolower($filter);
$filter = $this->_db->Quote('%' . $this->_db->getEscaped($filter, true) . '%', false);
$filter_type = JString::strtolower($filter_type);
switch ($filter_type) {
case 'title':
$where .= ' AND LOWER( a.title ) LIKE ' . $filter;
break;
case 'venue':
$where .= ' AND LOWER( l.venue ) LIKE ' . $filter;
break;
case 'city':
$where .= ' AND LOWER( l.city ) LIKE ' . $filter;
break;
}
}
// filter date
$nulldate = '0000-00-00';
if ($params->get('date_filter_type', 0) == 1) {
if ($filter_date_from && strtotime($filter_date_from)) {
$filter_date_from = $this->_db->Quote(strftime('%Y-%m-%d', strtotime($filter_date_from)));
$where .= ' AND DATEDIFF(IF (a.enddates IS NOT NULL AND a.enddates <> ' . $this->_db->Quote($nulldate) . ', a.enddates, a.dates), ' . $filter_date_from . ') >= 0';
}
if ($filter_date_to && strtotime($filter_date_to)) {
$filter_date_to = $this->_db->Quote(strftime('%Y-%m-%d', strtotime($filter_date_to)));
$where .= ' AND DATEDIFF(a.dates, ' . $filter_date_to . ') <= 0';
}
} else {
if ($filter_date_from && strtotime($filter_date_from)) {
$filter_date_from = $this->_db->Quote(strftime('%Y-%m-%d', strtotime($filter_date_from)));
$where .= ' AND DATEDIFF(a.dates, ' . $filter_date_from . ') >= 0';
}
if ($filter_date_to && strtotime($filter_date_to)) {
$filter_date_to = $this->_db->Quote(strftime('%Y-%m-%d', strtotime($filter_date_to)));
$where .= ' AND DATEDIFF(a.dates, ' . $filter_date_to . ') <= 0';
}
}
// filter country
if ($filter_continent) {
$where .= ' AND c.continent = ' . $this->_db->Quote($filter_continent);
}
// filter country
if ($filter_country) {
$where .= ' AND l.country = ' . $this->_db->Quote($filter_country);
}
// filter city
if ($filter_country && $filter_city) {
$where .= ' AND l.city = ' . $this->_db->Quote($filter_city);
}
// filter category
if ($filter_category) {
$cats = eventlist_cats::getChilds((int) $filter_category);
$where .= ' AND rel.catid IN (' . implode(', ', $cats) . ')';
}
return $where;
}
