function display($cachable = false, $urlparams = array()) { $mainframe = JFactory::getApplication(); $option = JRequest::getCmd('option'); $section = JRequest::getVar('section'); $db = JFactory::getDBO(); $clmAccess = clm_core::$access; $usertypestring = $clmAccess->usertypelist(); // usertypes, die der aktive user NICHT ändern darf $filter_order = $mainframe->getUserStateFromRequest("{$option}.filter_order", 'filter_order', 'a.id', 'cmd'); $filter_order_Dir = $mainframe->getUserStateFromRequest("{$option}.filter_order_Dir", 'filter_order_Dir', '', 'word'); $filter_state = $mainframe->getUserStateFromRequest("{$option}.filter_state", 'filter_state', '', 'word'); $filter_sid = $mainframe->getUserStateFromRequest("{$option}.filter_sid", 'filter_sid', clm_core::$access->getSeason(), 'int'); $filter_vid = $mainframe->getUserStateFromRequest("{$option}.filter_vid", 'filter_vid', 0, 'string'); $filter_usertype = $mainframe->getUserStateFromRequest("{$option}.filter_usertype", 'filter_usertype', 0, 'string'); $search = $mainframe->getUserStateFromRequest("{$option}.search", 'search', '', 'string'); $search = JString::strtolower($search); $limit = $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int'); $limitstart = $mainframe->getUserStateFromRequest($option . '.limitstart', 'limitstart', 0, 'int'); $where = array(); $where[] = ' c.published = 1'; if ($filter_usertype) { $where[] = "a.usertype = '{$filter_usertype}'"; } if ($filter_sid) { $where[] = 'a.sid = ' . $filter_sid; } if ($filter_vid) { $where[] = "a.zps = '{$filter_vid}'"; } if ($search) { $where[] = 'LOWER(a.name) LIKE "' . $db->escape('%' . $search . '%') . '"'; } if ($filter_state) { if ($filter_state == 'P') { $where[] = 'a.published = 1'; } else { if ($filter_state == 'U') { $where[] = 'a.published = 0'; } } } if ($usertypestring != "") { $where[] = ' a.usertype OUT (' . $usertypestring . ' ) '; } $where = count($where) ? ' WHERE ' . implode(' AND ', $where) : ''; if ($filter_order == 'a.id') { $orderby = ' ORDER BY d.ordering ASC, c.id ' . $filter_order_Dir; } elseif ($filter_order == 'd.name') { $orderby = ' ORDER BY d.ordering ' . $filter_order_Dir . ', a.id'; } elseif ($filter_order == 'name' or $filter_order == 'd.name' or $filter_order == 'b.Vereinname' or $filter_order == 'c.name' or $filter_order == 'u.lastvisitDate' or $filter_order == 'a.aktive' or $filter_order == 'a.published' or $filter_order == 'a.ordering') { $orderby = ' ORDER BY ' . $filter_order . ' ' . $filter_order_Dir . ', a.id'; } else { $orderby = ""; $filter_order = 'a.id'; } // get the total number of records $query = ' SELECT COUNT(*) ' . ' FROM #__clm_user AS a' . ' LEFT JOIN #__clm_saison AS c ON c.id = a.sid' . $where; $db->setQuery($query); $total = $db->loadResult(); jimport('joomla.html.pagination'); $pageNav = new JPagination($total, $limitstart, $limit); // nur wegen leistungsschwacher Provider $query = " SET SQL_BIG_SELECTS=1"; $db->setQuery($query); $db->query(); // get the subset (based on limits) of required records $query = 'SELECT a.*, c.name AS saison, b.Vereinname as verein, u.name AS editor, d.name as funktion' . ' , d.ordering as ut_ordering, u.lastvisitDate as date' . ' FROM #__clm_user AS a' . ' LEFT JOIN #__clm_saison AS c ON c.id = a.sid' . ' LEFT JOIN #__users AS u ON u.id = a.jid' . ' LEFT JOIN #__clm_dwz_vereine AS b ON a.zps = b.ZPS AND a.sid = b.sid' . ' LEFT JOIN #__clm_vereine AS e ON e.zps = a.zps AND e.sid = a.sid' . ' LEFT JOIN #__clm_usertype AS d ON d.usertype = a.usertype' . $where . $orderby; $db->setQuery($query, $pageNav->limitstart, $pageNav->limit); $rows = $db->loadObjectList(); if ($rows == 0) { JError::raiseNotice(6000, JText::_('USERS_NO_USER')); } if ($db->getErrorNum()) { echo $db->stderr(); return false; } // Statusfilter $lists['state'] = JHTML::_('grid.state', $filter_state); // Saisonfilter $sql = 'SELECT id, name FROM #__clm_saison WHERE published=1'; $db->setQuery($sql); $saisonlist[] = JHTML::_('select.option', '0', JText::_('USERS_SAISON'), 'id', 'name'); $saisonlist = array_merge($saisonlist, $db->loadObjectList()); $lists['sid'] = JHTML::_('select.genericlist', $saisonlist, 'filter_sid', 'class="inputbox" size="1" onchange="document.adminForm.submit();"', 'id', 'name', $filter_sid); // Vereinefilter laden $vereinlist = CLMFilterVerein::vereine_filter(0); $lists['vid'] = JHTML::_('select.genericlist', $vereinlist, 'filter_vid', 'class="inputbox" size="1" onchange="document.adminForm.submit();"', 'zps', 'name', $filter_vid); // Funktionsliste $sql = 'SELECT usertype, name FROM #__clm_usertype '; if ($usertypestring != "") { $sql .= ' WHERE usertype OUT (' . $usertypestring . ' ) '; } $sql .= ' ORDER BY ordering ASC '; $db->setQuery($sql); $usertypelist[] = JHTML::_('select.option', '0', JText::_('USERS_BENUTZER_DD'), 'usertype', 'name'); $usertypelist = array_merge($usertypelist, $db->loadObjectList()); $lists['usertype'] = JHTML::_('select.genericlist', $usertypelist, 'filter_usertype', 'class="inputbox" size="1" onchange="document.adminForm.submit();"', 'usertype', 'name', intval($filter_usertype)); // Ordering $lists['order_Dir'] = $filter_order_Dir; $lists['order'] = $filter_order; // Suchefilter $lists['search'] = $search; require_once JPATH_COMPONENT . DS . 'views' . DS . 'users.php'; CLMViewUsers::users($rows, $lists, $pageNav, $option); }
function display() { global $mainframe, $option; $section = JRequest::getVar('section'); $db =& JFactory::getDBO(); $filter_order = $mainframe->getUserStateFromRequest("{$option}.filter_order", 'filter_order', 'a.id', 'cmd'); $filter_order_Dir = $mainframe->getUserStateFromRequest("{$option}.filter_order_Dir", 'filter_order_Dir', '', 'word'); $filter_state = $mainframe->getUserStateFromRequest("{$option}.filter_state", 'filter_state', '', 'word'); $filter_sid = $mainframe->getUserStateFromRequest("{$option}.filter_sid", 'filter_sid', 0, 'int'); $filter_vid = $mainframe->getUserStateFromRequest("{$option}.filter_vid", 'filter_vid', 0, 'string'); $filter_usertype = $mainframe->getUserStateFromRequest("{$option}.filter_usertype", 'filter_usertype', 0, 'int'); $search = $mainframe->getUserStateFromRequest("{$option}.search", 'search', '', 'string'); $search = JString::strtolower($search); $limit = $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int'); $limitstart = $mainframe->getUserStateFromRequest($option . '.limitstart', 'limitstart', 0, 'int'); $where = array(); $where[] = ' c.archiv = 0'; // AND c.published = 1'; if ($filter_usertype) { $where[] = 'a.user_clm = ' . (int) $filter_usertype; } if ($filter_sid) { $where[] = 'a.sid = ' . (int) $filter_sid; } if ($filter_vid) { $where[] = "a.zps = '{$filter_vid}'"; } if ($search) { $where[] = 'LOWER(a.name) LIKE ' . $db->Quote('%' . $db->getEscaped($search, true) . '%', false); } if ($filter_state) { if ($filter_state == 'P') { $where[] = 'a.published = 1'; } else { if ($filter_state == 'U') { $where[] = 'a.published = 0'; } } } $where = count($where) ? ' WHERE ' . implode(' AND ', $where) : ''; if ($filter_order == 'a.id') { $orderby = ' ORDER BY c.id ' . $filter_order_Dir . ', a.user_clm DESC'; } else { if ($filter_order == 'name' or $filter_order == 'd.name' or $filter_order == 'b.Vereinname' or $filter_order == 'c.name' or $filter_order == 'u.lastvisitDate' or $filter_order == 'a.aktive' or $filter_order == 'a.published' or $filter_order == 'a.ordering') { $orderby = ' ORDER BY ' . $filter_order . ' ' . $filter_order_Dir . ', a.id'; } else { $filter_order = 'a.id'; } } // get the total number of records $query = ' SELECT COUNT(*) ' . ' FROM #__clm_user AS a' . ' LEFT JOIN #__clm_saison AS c ON c.id = a.sid' . $where; $db->setQuery($query); $total = $db->loadResult(); jimport('joomla.html.pagination'); $pageNav = new JPagination($total, $limitstart, $limit); // get the subset (based on limits) of required records $query = 'SELECT a.*, c.name AS saison, b.Vereinname as verein, u.name AS editor, d.name as funktion' . ' ,u.lastvisitDate as date' . ' FROM #__clm_user AS a' . ' LEFT JOIN #__clm_saison AS c ON c.id = a.sid' . ' LEFT JOIN #__users AS u ON u.id = a.jid' . ' LEFT JOIN #__clm_dwz_vereine AS b ON a.zps = b.ZPS AND a.sid = b.sid' . ' LEFT JOIN #__clm_vereine AS e ON e.zps = a.zps AND e.sid = a.sid' . ' LEFT JOIN #__clm_usertype AS d ON d.user_clm = a.user_clm' . $where . $orderby; $db->setQuery($query, $pageNav->limitstart, $pageNav->limit); $rows = $db->loadObjectList(); if ($db->getErrorNum()) { echo $db->stderr(); return false; } // Statusfilter $lists['state'] = JHTML::_('grid.state', $filter_state); // Saisonfilter $sql = 'SELECT id, name FROM #__clm_saison WHERE archiv =0'; $db->setQuery($sql); $saisonlist[] = JHTML::_('select.option', '0', JText::_('USERS_SAISON'), 'id', 'name'); $saisonlist = array_merge($saisonlist, $db->loadObjectList()); $lists['sid'] = JHTML::_('select.genericlist', $saisonlist, 'filter_sid', 'class="inputbox" size="1" onchange="document.adminForm.submit();"', 'id', 'name', intval($filter_sid)); // Vereinefilter laden require_once JPATH_COMPONENT . DS . 'controllers' . DS . 'filter_vereine.php'; $vereinlist = CLMFilterVerein::vereine_filter(0); $lists['vid'] = JHTML::_('select.genericlist', $vereinlist, 'filter_vid', 'class="inputbox" size="1" onchange="document.adminForm.submit();"', 'zps', 'name', $filter_vid); // Funktionsliste $sql = 'SELECT user_clm as usertype, name FROM #__clm_usertype ORDER BY ID ASC'; $db->setQuery($sql); $usertypelist[] = JHTML::_('select.option', '0', JText::_('USERS_BENUTZER_DD'), 'usertype', 'name'); $usertypelist = array_merge($usertypelist, $db->loadObjectList()); $lists['usertype'] = JHTML::_('select.genericlist', $usertypelist, 'filter_usertype', 'class="inputbox" size="1" onchange="document.adminForm.submit();"', 'usertype', 'name', intval($filter_usertype)); // Ordering $lists['order_Dir'] = $filter_order_Dir; $lists['order'] = $filter_order; // Suchefilter $lists['search'] = $search; require_once JPATH_COMPONENT . DS . 'views' . DS . 'users.php'; CLMViewUsers::users($rows, $lists, $pageNav, $option); }