/**
* Query
*/
function buildQuery()
{
$app = JFactory::getApplication();
$jemsettings = JemHelper::config();
$filter_order = $app->getUserStateFromRequest('com_jem.contactelement.filter_order', 'filter_order', 'con.ordering', 'cmd');
$filter_order_Dir = $app->getUserStateFromRequest('com_jem.contactelement.filter_order_Dir', 'filter_order_Dir', '', 'word');
$filter_order = JFilterInput::getinstance()->clean($filter_order, 'cmd');
$filter_order_Dir = JFilterInput::getinstance()->clean($filter_order_Dir, 'word');
$filter_type = $app->getUserStateFromRequest('com_jem.contactelement.filter_type', 'filter_type', '', 'int');
$search = $app->getUserStateFromRequest('com_jem.contactelement.filter_search', 'filter_search', '', 'string');
$search = $this->_db->escape(trim(JString::strtolower($search)));
// start query
$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select(array('con.*'));
$query->from('#__contact_details as con');
// where
$where = array();
$where[] = 'con.published = 1';
// search
if ($search) {
switch ($filter_type) {
case 1:
/* name */
$where[] = 'LOWER(con.name) LIKE \'%' . $search . '%\' ';
break;
case 2:
/* address */
$where[] = 'LOWER(con.address) LIKE \'%' . $search . '%\' ';
break;
case 3:
/* city */
$where[] = 'LOWER(con.suburb) LIKE \'%' . $search . '%\' ';
break;
case 4:
/* state */
$where[] = 'LOWER(con.state) LIKE \'%' . $search . '%\' ';
break;
}
}
$query->where($where);
// order
if ($filter_order != '') {
$orderby = $filter_order . ' ' . $filter_order_Dir;
} else {
$orderby = 'con.name';
}
$query->order($orderby);
return $query;
}
/**
* venue-query
*/
function buildQuery()
{
$app = JFactory::getApplication();
$jinput = $app->input;
$db = JFactory::getDBO();
$jemsettings = JemHelper::config();
$itemid = $jinput->getInt('id', 0) . ':' . $jinput->getInt('Itemid', 0);
$filter_order = $app->getUserStateFromRequest('com_jem.venueelement.' . $itemid . '.filter_order', 'filter_order', 'l.ordering', 'cmd');
$filter_order_Dir = $app->getUserStateFromRequest('com_jem.venueelement.' . $itemid . '.filter_order_Dir', 'filter_order_Dir', '', 'word');
$filter_order = JFilterInput::getinstance()->clean($filter_order, 'cmd');
$filter_order_Dir = JFilterInput::getinstance()->clean($filter_order_Dir, 'word');
$filter_type = $app->getUserStateFromRequest('com_jem.venueelement.' . $itemid . '.filter_type', 'filter_type', '', 'int');
$search = $app->getUserStateFromRequest('com_jem.venueelement.' . $itemid . '.filter_search', 'filter_search', '', 'string');
$search = $db->escape(trim(JString::strtolower($search)));
// Query
$query = $db->getQuery(true);
$query->select(array('l.id', 'l.state', 'l.city', 'l.country', 'l.published', 'l.venue', 'l.ordering'));
$query->from('#__jem_venues as l');
// where
$where = array();
$where[] = 'l.published = 1';
/* something to search for? (we like to search for "0" too) */
if ($search || $search === "0") {
switch ($filter_type) {
case 1:
/* Search venues */
$where[] = 'LOWER(l.venue) LIKE "%' . $search . '%"';
break;
case 2:
// Search city
$where[] = 'LOWER(l.city) LIKE "%' . $search . '%"';
break;
case 3:
// Search state
$where[] = 'LOWER(l.state) LIKE "%' . $search . '%"';
}
}
$query->where($where);
$orderby = array($filter_order . ' ' . $filter_order_Dir, 'l.venue ASC');
$query->order($orderby);
return $query;
}
/**
* contacts-query
*/
function buildQueryContacts()
{
$app = JFactory::getApplication();
$jemsettings = JemHelper::config();
$filter_order = $app->getUserStateFromRequest('com_jem.selectcontact.filter_order', 'filter_order', 'con.ordering', 'cmd');
$filter_order_Dir = $app->getUserStateFromRequest('com_jem.selectcontact.filter_order_Dir', 'filter_order_Dir', '', 'word');
$filter_order = JFilterInput::getinstance()->clean($filter_order, 'cmd');
$filter_order_Dir = JFilterInput::getinstance()->clean($filter_order_Dir, 'word');
$filter_type = $app->getUserStateFromRequest('com_jem.selectcontact.filter_type', 'filter_type', '', 'int');
$search = $app->getUserStateFromRequest('com_jem.selectcontact.filter_search', 'filter_search', '', 'string');
$search = $this->_db->escape(trim(JString::strtolower($search)));
// Query
$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select(array('con.*'));
$query->from('#__contact_details As con');
// where
$where = array();
$where[] = 'con.published = 1';
/* something to search for? (we like to search for "0" too) */
if ($search || $search === "0") {
switch ($filter_type) {
case 1:
/* Search name */
$where[] = ' LOWER(con.name) LIKE \'%' . $search . '%\' ';
break;
case 2:
/* Search address (not supported yet, privacy) */
//$where[] = ' LOWER(con.address) LIKE \'%' . $search . '%\' ';
break;
case 3:
// Search city
$where[] = ' LOWER(con.suburb) LIKE \'%' . $search . '%\' ';
break;
case 4:
// Search state
$where[] = ' LOWER(con.state) LIKE \'%' . $search . '%\' ';
break;
}
}
$query->where($where);
// ordering
// ensure it's a valid order direction (asc, desc or empty)
if (!empty($filter_order_Dir) && strtoupper($filter_order_Dir) !== 'DESC') {
$filter_order_Dir = 'ASC';
}
if ($filter_order != '') {
$orderby = $filter_order . ' ' . $filter_order_Dir;
if ($filter_order != 'con.name') {
$orderby = array($orderby, 'con.name');
// in case of city or state we should have a useful second ordering
}
} else {
$orderby = 'con.name';
}
$query->order($orderby);
return $query;
}
/**
* Method to get categories item data
*
* @access public
* @return array
*/
function getData()
{
$app = JFactory::getApplication();
$db = JFactory::getDBO();
$itemid = $app->input->getInt('id', 0) . ':' . $app->input->getInt('Itemid', 0);
static $items;
if (isset($items)) {
return $items;
}
$limit = $app->getUserStateFromRequest('com_jem.limit', 'limit', $app->getCfg('list_limit'), 'int');
$limitstart = $app->getUserStateFromRequest('com_jem.limitstart', 'limitstart', 0, 'int');
$limitstart = $limit ? (int)(floor($limitstart / $limit) * $limit) : 0;
$filter_order = $app->getUserStateFromRequest('com_jem.categoryelement.filter_order', 'filter_order', 'c.lft', 'cmd');
$filter_order_Dir = $app->getUserStateFromRequest('com_jem.categoryelement.filter_order_Dir', 'filter_order_Dir', '', 'word');
$filter_state = $app->getUserStateFromRequest('com_jem.categoryelement.'.$itemid.'.filter_state', 'filter_state', '', 'string');
$search = $app->getUserStateFromRequest('com_jem.categoryelement.'.$itemid.'.filter_search', 'filter_search', '', 'string');
$search = $db->escape(trim(JString::strtolower($search)));
$filter_order = JFilterInput::getinstance()->clean($filter_order, 'cmd');
$filter_order_Dir = JFilterInput::getinstance()->clean($filter_order_Dir, 'word');
$orderby = ' ORDER BY ' . $filter_order . ' ' . $filter_order_Dir;
$state = array(1);
if (is_numeric($filter_state)) {
$where = ' WHERE c.published = '.(int) $filter_state;
} else {
$where = ' WHERE c.published IN (' . implode(',', $state) . ')';
//$where .= ' AND c.alias NOT LIKE "root"';
}
$where2 = ' AND c.published IN (' . implode(',', $state) . ')';
//$where2 .= ' AND c.alias NOT LIKE "root"';
// select the records
// note, since this is a tree we have to do the limits code-side
if ($search) {
$query = 'SELECT c.id FROM #__jem_categories AS c' . ' WHERE LOWER(c.catname) LIKE ' . $db->Quote('%' . $this->_db->escape($search, true) . '%', false) . $where2;
$db->setQuery($query);
$search_rows = $db->loadColumn();
}
$query = 'SELECT c.*, u.name AS editor, g.title AS groupname, gr.name AS catgroup'
. ' FROM #__jem_categories AS c' . ' LEFT JOIN #__viewlevels AS g ON g.id = c.access'
. ' LEFT JOIN #__users AS u ON u.id = c.checked_out'
. ' LEFT JOIN #__jem_groups AS gr ON gr.id = c.groupid'
. $where
// . ' ORDER BY c.parent_id, c.ordering';
. $orderby;
$db->setQuery($query);
$mitems = $db->loadObjectList();
// Check for a database error.
if ($db->getErrorNum()) {
JError::raiseNotice(500, $db->getErrorMsg());
}
if (! $mitems) {
$mitems = array();
$children = array();
$parentid = $mitems;
} else {
$children = array();
// First pass - collect children
foreach ($mitems as $v) {
$pt = $v->parent_id;
$list = @$children[$pt] ? $children[$pt] : array();
array_push($list, $v);
$children[$pt] = $list;
}
// list childs of "root" which has no parent and normally id 1
$parentid = intval(@isset($children[0][0]->id) ? $children[0][0]->id : 1);
}
// get list of the items
$list = JEMCategories::treerecurse($parentid, '', array(), $children, 9999, 0, 0);
// eventually only pick out the searched items.
if ($search) {
$list1 = array();
foreach ($search_rows as $sid) {
foreach ($list as $item) {
if ($item->id == $sid) {
$list1[] = $item;
}
}
}
// replace full list with found items
$list = $list1;
}
$total = count($list);
jimport('joomla.html.pagination');
$this->_pagination = new JPagination($total, $limitstart, $limit);
// slice out elements based on limits
$list = array_slice($list, $this->_pagination->limitstart, $this->_pagination->limit);
return $list;
}
/**
* Method to build the orderby clause of the query for the attendees
*
* @access private
* @return integer
*
*/
protected function _buildContentOrderBy()
{
$app = JFactory::getApplication();
$filter_order = $app->getUserStateFromRequest('com_jem.attendees.filter_order', 'filter_order', 'r.waiting', 'cmd' );
$filter_order_Dir = $app->getUserStateFromRequest('com_jem.attendees.filter_order_Dir', 'filter_order_Dir', 'ASC', 'word' );
if ($this->_reguser && ($filter_order == 'u.username')) {
$filter_order = 'u.name';
}
$filter_order = JFilterInput::getinstance()->clean($filter_order, 'cmd');
$filter_order_Dir = JFilterInput::getinstance()->clean($filter_order_Dir, 'word');
$orderby = ' ORDER BY '.$filter_order.' '.$filter_order_Dir.', u.name';
return $orderby;
}
/**
* Method to get categories item data
*
* @access public
* @return array
*/
function getData()
{
$app = JFactory::getApplication();
$db = JFactory::getDBO();
$jinput = $app->input;
$itemid = $jinput->getInt('id', 0) . ':' . $jinput->getInt('Itemid', 0);
static $items;
if (isset($items)) {
return $items;
}
$filter_order = $app->getUserStateFromRequest('com_jem.categoryelement.filter_order', 'filter_order', 'c.lft', 'cmd');
$filter_order_Dir = $app->getUserStateFromRequest('com_jem.categoryelement.filter_order_Dir', 'filter_order_Dir', '', 'word');
$filter_state = $app->getUserStateFromRequest('com_jem.categoryelement.' . $itemid . '.filter_state', 'filter_state', '', 'string');
$search = $app->getUserStateFromRequest('com_jem.categoryelement.' . $itemid . '.filter_search', 'filter_search', '', 'string');
$search = $db->escape(trim(JString::strtolower($search)));
$filter_order = JFilterInput::getinstance()->clean($filter_order, 'cmd');
$filter_order_Dir = JFilterInput::getinstance()->clean($filter_order_Dir, 'word');
$state = array(1);
$query = $db->getQuery(true);
$query->select(array('c.*', 'u.name AS editor', 'g.title AS groupname', 'gr.name AS catgroup'));
$query->from('#__jem_categories AS c');
$query->join('LEFT', '#__viewlevels AS g ON g.id = c.access');
$query->join('LEFT', '#__users AS u ON u.id = c.checked_out');
$query->join('LEFT', '#__jem_groups AS gr ON gr.id = c.groupid');
if (is_numeric($filter_state)) {
$query->where('c.published = ' . (int) $filter_state);
} else {
$query->where('c.published IN (' . implode(',', $state) . ')');
}
$query->order($filter_order . ' ' . $filter_order_Dir);
$db->setQuery($query);
$mitems = $db->loadObjectList();
// Check for a database error.
if ($db->getErrorNum()) {
JError::raiseNotice(500, $db->getErrorMsg());
}
if (!$mitems) {
$mitems = array();
$children = array();
$parentid = $mitems;
} else {
$mitems_temp = $mitems;
$children = array();
// First pass - collect children
foreach ($mitems as $v) {
$pt = $v->parent_id;
$list = @$children[$pt] ? $children[$pt] : array();
array_push($list, $v);
$children[$pt] = $list;
}
$parentid = intval($mitems[0]->parent_id);
}
// get list of the items
$list = JemCategories::treerecurse($parentid, '', array(), $children, 9999, 0, 0);
// note, since this is a tree we have to do the limits code-side
if ($search) {
$query = $db->getQuery(true);
$query->select('c.id');
$query->from('#__jem_categories AS c');
$query->where(array('LOWER(c.catname) LIKE ' . $db->Quote('%' . $this->_db->escape($search, true) . '%', false), 'c.published IN (' . implode(',', $state) . ')'));
$db->setQuery($query);
$search_rows = $db->loadColumn();
}
// eventually only pick out the searched items.
if ($search) {
$list1 = array();
foreach ($search_rows as $sid) {
foreach ($list as $item) {
if ($item->id == $sid) {
$list1[] = $item;
}
}
}
// replace full list with found items
$list = $list1;
}
$total = count($list);
jimport('joomla.html.pagination');
$this->_pagination = new JPagination($total, $this->getState('limitstart'), $this->getState('limit'));
// slice out elements based on limits
$list = array_slice($list, $this->_pagination->limitstart, $this->_pagination->limit);
return $list;
}
/**
* Method to build the orderby clause of the query for the attendees
*
* @access private
* @return integer
*
*/
protected function _buildContentOrderBy()
{
$app = JFactory::getApplication();
$jinput = JFactory::getApplication()->input;
$itemid = $jinput->getInt('id', 0) . ':' . $jinput->getInt('Itemid', 0);
$filter_order = $app->getUserStateFromRequest('com_jem.attendees.' . $itemid . '.filter_order', 'filter_order', 'u.username', 'cmd');
$filter_order_Dir = $app->getUserStateFromRequest('com_jem.attendees.' . $itemid . '.filter_order_Dir', 'filter_order_Dir', '', 'word');
$filter_order = JFilterInput::getinstance()->clean($filter_order, 'cmd');
$filter_order_Dir = JFilterInput::getinstance()->clean($filter_order_Dir, 'word');
$orderby = ' ORDER BY ' . $filter_order . ' ' . $filter_order_Dir . ', u.name';
return $orderby;
}
/**
* Build the query
*
* @access private
* @return string
*/
protected function _buildQuery()
{
$app = JFactory::getApplication();
$jinput = $app->input;
$jemsettings = JemHelper::config();
$itemid = $jinput->getInt('id', 0) . ':' . $jinput->getInt('Itemid', 0);
$db = JFactory::getDBO();
$user = JFactory::getUser();
$levels = $user->getAuthorisedViewLevels();
$filter_order = $app->getUserStateFromRequest('com_jem.eventelement.' . $itemid . '.filter_order', 'filter_order', 'a.dates', 'cmd');
$filter_order_Dir = $app->getUserStateFromRequest('com_jem.eventelement.' . $itemid . '.filter_order_Dir', 'filter_order_Dir', '', 'word');
$filter_order = JFilterInput::getinstance()->clean($filter_order, 'cmd');
$filter_order_Dir = JFilterInput::getinstance()->clean($filter_order_Dir, 'word');
$published = $app->getUserStateFromRequest('com_jem.eventelement.' . $itemid . '.filter_state', 'filter_state', '', 'string');
$filter_type = $app->getUserStateFromRequest('com_jem.eventelement.' . $itemid . '.filter_type', 'filter_type', '', 'int');
$search = $app->getUserStateFromRequest('com_jem.eventelement.' . $itemid . '.filter_search', 'filter_search', '', 'string');
$search = $db->escape(trim(JString::strtolower($search)));
// Query
$query = $db->getQuery(true);
$query->select(array('a.*', 'loc.venue', 'loc.city', 'c.catname'));
$query->from('#__jem_events as a');
$query->join('LEFT', '#__jem_venues AS loc ON loc.id = a.locid');
$query->join('LEFT', '#__jem_cats_event_relations AS rel ON rel.itemid = a.id');
$query->join('LEFT', '#__jem_categories AS c ON c.id = rel.catid');
// where
$where = array();
// Filter by published state
if (is_numeric($published)) {
$where[] = 'a.published = ' . (int) $published;
} elseif ($published === '') {
$where[] = '(a.published IN (1))';
}
$where[] = ' c.published = 1';
$where[] = ' c.access IN (' . implode(',', $levels) . ')';
/* something to search for? (we like to search for "0" too) */
if ($search || $search === "0") {
switch ($filter_type) {
case 1:
$where[] = ' LOWER(a.title) LIKE \'%' . $search . '%\' ';
break;
case 2:
$where[] = ' LOWER(loc.venue) LIKE \'%' . $search . '%\' ';
break;
case 3:
$where[] = ' LOWER(loc.city) LIKE \'%' . $search . '%\' ';
break;
case 4:
$where[] = ' LOWER(c.catname) LIKE \'%' . $search . '%\' ';
break;
}
}
$query->where($where);
$query->group('a.id');
$orderby = array($filter_order . ' ' . $filter_order_Dir, 'a.dates ASC');
$query->order($orderby);
return $query;
}
