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