/**
* Build an SQL query to load the list data.
*
* @return JQuery
*/
protected function _getListQuery()
{
// Create a new query object.
$query = new JQuery();
// Select the required fields from the table.
$query->select($this->getState('list.select', 'a.*'));
$query->from('`#__viewlevels` AS a');
// Add the level in the tree.
$query->group('a.id');
// Filter the items over the search string if set.
$search = $this->getState('filter.search');
if (!empty($search)) {
if (stripos($search, 'id:') === 0) {
$query->where('a.id = ' . (int) substr($search, 3));
} else {
$search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%');
$query->where('a.title LIKE ' . $search);
}
}
$query->group('a.id');
// Add the list ordering clause.
$query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.lft')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC')));
//echo nl2br(str_replace('#__','jos_',$query));
return $query;
}
/**
* Build an SQL query to load the list data.
*
* @return JQuery
*/
protected function _getListQuery()
{
// Create a new query object.
$query = new JQuery();
// Select the required fields from the table.
$query->select($this->getState('list.select', 'a.*'));
$query->from('`#__usergroups` AS a');
// Add the level in the tree.
$query->select('COUNT(DISTINCT c2.id) AS level');
$query->join('LEFT OUTER', '`#__usergroups` AS c2 ON a.lft > c2.lft AND a.rgt < c2.rgt');
$query->group('a.id');
// Count the objects in the user group.
$query->select('COUNT(DISTINCT map.user_id) AS user_count');
$query->join('LEFT', '`#__user_usergroup_map` AS map ON map.group_id = a.id');
$query->group('a.id');
// Filter the comments over the search string if set.
$search = $this->getState('filter.search');
if (!empty($search)) {
if (stripos($search, 'id:') === 0) {
$query->where('a.id = ' . (int) substr($search, 3));
} else {
$search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%');
$query->where('a.title LIKE ' . $search);
}
}
// Add the list ordering clause.
$query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.lft')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC')));
//echo nl2br(str_replace('#__','jos_',$query));
return $query;
}
/**
* Method to build an SQL query to load the list data.
*
* @access protected
* @return string An SQL query
* @since 1.0
*/
function _getListQuery()
{
$query = new JQuery();
// Select the required fields from the table.
$query->select($this->getState('list.select', 'a.*'));
$query->from('`#__social_threads` AS a');
// Join on the comments table.
$query->select('COUNT(c.id) AS comment_count');
$query->leftJoin('#__social_comments AS c ON c.thread_id = a.id');
// Join on the ratings table.
$query->select('pscore_count');
$query->leftJoin('#__social_ratings AS r ON r.thread_id = a.id');
$query->group('a.id');
// Filter the items over the context if set.
if ($context = $this->getState('filter.context')) {
$query->where('a.context = ' . $this->_db->Quote($context));
}
// Filter by search string.
if ($search = $this->getState('filter.search')) {
if (stripos($search, 'id:') === 0) {
$query->where('a.id = ' . (int) substr($search, 3));
} else {
$search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%');
$query->where('a.page_title LIKE ' . $search);
}
}
// Add the list ordering clause.
$query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.id') . ' ' . $this->getState('list.direction', 'asc')));
//echo nl2br(str_replace('#__','jos_',$query));
return $query;
}
/**
* Method to get a list of options for a list input.
*
* @return array An array of JHtml options.
*/
protected function _getOptions()
{
$db =& JFactory::getDbo();
$query = new JQuery();
$query->select('a.id AS value, a.title AS text, a.level');
$query->from('#__categories AS a');
$query->join('LEFT', '`#__categories` AS b ON a.lft > b.lft AND a.rgt < b.rgt');
// Filter by the type
if ($extension = $this->_form->getValue('extension')) {
$query->where('(a.extension = ' . $db->quote($extension) . ' OR a.parent_id = 0)');
}
// Prevent parenting to children of this item.
if ($id = $this->_form->getValue('id')) {
$query->join('LEFT', '`#__categories` AS p ON p.id = ' . (int) $id);
$query->where('NOT(a.lft >= p.lft AND a.rgt <= p.rgt)');
}
$query->group('a.id');
$query->order('a.lft ASC');
// Get the options.
$db->setQuery($query);
$options = $db->loadObjectList();
// Check for a database error.
if ($db->getErrorNum()) {
JError::raiseWarning(500, $db->getErrorMsg());
}
// Pad the option text with spaces using depth level as a multiplier.
for ($i = 0, $n = count($options); $i < $n; $i++) {
$options[$i]->text = str_repeat('- ', $options[$i]->level) . $options[$i]->text;
}
$options = array_merge(parent::_getOptions(), $options);
return $options;
}
/**
* Build an SQL query to load the list data.
*
* @return JQuery
*/
protected function _getListQuery()
{
// Create a new query object.
$query = new JQuery();
// Select the required fields from the table.
$query->select($this->getState('list.select', 'a.id, a.template, a.title, a.home, a.client_id'));
$query->from('`#__template_styles` AS a');
// Join on menus.
$query->select('COUNT(m.template_style_id) AS assigned');
$query->leftjoin('#__menu AS m ON m.template_style_id = a.id');
$query->group('a.id');
// Filter by template.
if ($template = $this->getState('filter.template')) {
$query->where('a.template = ' . $this->_db->quote($template));
}
// Filter by client.
$clientId = $this->getState('filter.client_id');
if (is_numeric($clientId)) {
$query->where('a.client_id = ' . (int) $clientId);
}
// Filter by search in title
$search = $this->getState('filter.search');
if (!empty($search)) {
if (stripos($search, 'id:') === 0) {
$query->where('a.id = ' . (int) substr($search, 3));
} else {
$search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%');
$query->where('a.template LIKE ' . $search . ' OR a.title LIKE ' . $search);
}
}
// Add the list ordering clause.
$query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.name')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC')));
//echo nl2br(str_replace('#__','jos_',$query));
return $query;
}
function getList(&$params)
{
//get database
$db =& JFactory::getDbo();
$query = new JQuery();
$query->select('MONTH(created) AS created_month, created, id, title, YEAR(created) AS created_year');
$query->from('#__content');
$query->where('state = -1 AND checked_out = 0');
$query->group('created_year DESC, created_month DESC');
// $query = 'SELECT MONTH(created) AS created_month, created, id, title, YEAR(created) AS created_year' .
// ' FROM #__content' .
// ' WHERE (state = -1 AND checked_out = 0)' .
// ' GROUP BY created_year DESC, created_month DESC';
$db->setQuery($query, 0, intval($params->get('count')));
$rows = $db->loadObjectList();
$menu =& JSite::getMenu();
$item = $menu->getItems('link', 'index.php?option=com_content&view=archive', true);
$itemid = isset($item) ? '&Itemid=' . $item->id : '';
$i = 0;
$lists = array();
foreach ($rows as $row) {
$date =& JFactory::getDate($row->created);
$created_month = $date->toFormat("%m");
$month_name = $date->toFormat("%B");
$created_year = $date->toFormat("%Y");
$lists[$i]->link = JRoute::_('index.php?option=com_content&view=archive&year=' . $created_year . '&month=' . $created_month . $itemid);
$lists[$i]->text = $month_name . ', ' . $created_year;
$i++;
}
return $lists;
}
/**
* Get a list of the available menus.
*
* @return array An array of the available menus (from the menu types table).
*/
public static function getMenus()
{
$db =& JFactory::getDbo();
$query = new JQuery();
$query->select('a.*, SUM(b.home) AS home');
$query->from('#__menu_types AS a');
$query->leftJoin('#__menu AS b ON b.menutype = a.menutype');
$query->group('a.id');
$db->setQuery($query);
$result = $db->loadObjectList();
return $result;
}
/**
* Build an SQL query to load the list data.
*
* @return JQuery
*/
protected function _getListQuery()
{
// Create a new query object.
$query = new JQuery();
// Select the required fields from the table.
$query->select($this->getState('list.select', 'a.*'));
$query->from('`#__users` AS a');
// Join over the group mapping table.
$query->select('COUNT(map.group_id) AS group_count');
$query->join('LEFT', '#__user_usergroup_map AS map ON map.user_id = a.id');
$query->group('a.id');
// Join over the user groups table.
$query->select('GROUP_CONCAT(g2.title SEPARATOR ' . $this->_db->Quote("\n") . ') AS group_names');
$query->join('LEFT', '#__usergroups AS g2 ON g2.id = map.group_id');
// If the model is set to check item state, add to the query.
$state = $this->getState('filter.state');
if (is_numeric($state)) {
$query->where('a.block = ' . (int) $state);
}
// If the model is set to check the activated state, add to the query.
$active = $this->getState('filter.active');
if (is_numeric($active)) {
if ($active == '0') {
$query->where('a.activation = ""');
} else {
if ($active == '1') {
$query->where('LENGTH(a.activation) = 32');
}
}
}
// Filter the items over the group id if set.
if ($groupId = $this->getState('filter.group_id')) {
$query->join('LEFT', '#__user_usergroup_map AS map2 ON map2.user_id = a.id');
$query->where('map2.group_id = ' . (int) $groupId);
}
// Filter the items over the search string if set.
if ($this->getState('filter.search') !== '') {
// Escape the search token.
$token = $this->_db->Quote('%' . $this->_db->getEscaped($this->getState('filter.search')) . '%');
// Compile the different search clauses.
$searches = array();
$searches[] = 'a.name LIKE ' . $token;
$searches[] = 'a.username LIKE ' . $token;
$searches[] = 'a.email LIKE ' . $token;
// Add the clauses to the query.
$query->where('(' . implode(' OR ', $searches) . ')');
}
// Add the list ordering clause.
$query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.name')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC')));
//echo nl2br(str_replace('#__','jos_',$query));
return $query;
}
function getOnlineUserNames()
{
$db =& JFactory::getDbo();
$result = null;
$query = new JQuery();
$query->select('a.username, a.time, a.userid, a.usertype, a.client_id');
$query->from('#__session AS a');
$query->where('a.userid != 0');
$query->group('a.userid');
$db->setQuery($query);
$result = $db->loadObjectList();
if ($db->getErrorNum()) {
JError::raiseWarning(500, $db->stderr());
}
return $result;
}
/**
* Method to build an SQL query to load the list data.
*
* @return string An SQL query
*/
protected function _getListQuery()
{
// Create a new query object.
$query = new JQuery();
// Select all fields from the table.
$query->select($this->getState('list.select', 'a.*'));
$query->from('`#__menu_types` AS a');
// Self join to find the number of published menu items in the menu.
$query->select('COUNT(DISTINCT m1.id) AS count_published');
$query->join('LEFT', '`#__menu` AS m1 ON m1.menutype = a.menutype AND m1.published = 1');
// Self join to find the number of unpublished menu items in the menu.
$query->select('COUNT(DISTINCT m2.id) AS count_unpublished');
$query->join('LEFT', '`#__menu` AS m2 ON m2.menutype = a.menutype AND m2.published = 0');
// Self join to find the number of trashed menu items in the menu.
$query->select('COUNT(DISTINCT m3.id) AS count_trashed');
$query->join('LEFT', '`#__menu` AS m3 ON m3.menutype = a.menutype AND m3.published = -2');
$query->group('a.id');
// Add the list ordering clause.
$query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.id')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC')));
//echo nl2br(str_replace('#__','jos_',$query->toString())).'<hr/>';
return $query;
}
/**
* method to give information about categories
*/
function &getCategories()
{
if (!isset($this->_categories)) {
$query = new JQuery();
$query->select('MAX(ordering) as `max`');
$query->select('catid');
$query->from('#__banners');
$query->where('state>=0');
$query->group('catid');
$this->_db->setQuery((string) $query);
$this->_categories = $this->_db->loadObjectList('catid');
}
return $this->_categories;
}
/**
* Get a list of menu links for one or all menus.
*
* @param string An option menu to filter the list on, otherwise all menu links are returned as a grouped array.
* @param int An optional parent ID to pivot results around.
* @param int An optional mode. If parent ID is set and mode=2, the parent and children are excluded from the list.
* @param array An optional array of states
*/
public static function getMenuLinks($menuType = null, $parentId = 0, $mode = 0, $published = array())
{
$db = JFactory::getDbo();
$query = new JQuery();
$query->select('a.id AS value, a.title AS text, a.level, a.menutype, a.type');
$query->from('#__menu AS a');
$query->join('LEFT', '`#__menu` AS b ON a.lft > b.lft AND a.rgt < b.rgt');
// Filter by the type
if ($menuType) {
$query->where('(a.menutype = ' . $db->quote($menuType) . ' OR a.parent_id = 0)');
}
if ($parentId) {
if ($mode == 2) {
// Prevent the parent and children from showing.
$query->join('LEFT', '`#__menu` AS p ON p.id = ' . (int) $parentId);
$query->where('(a.lft <= p.lft OR a.rgt >= p.rgt)');
}
}
if (!empty($published)) {
if (is_array($published)) {
$published = '(' . implode(',', $published) . ')';
}
$query->where('a.published IN ' . $published);
}
$query->group('a.id');
$query->order('a.lft ASC');
// Get the options.
$db->setQuery($query);
$links = $db->loadObjectList();
// Check for a database error.
if ($error = $db->getErrorMsg()) {
JError::raiseWarning(500, $error);
return false;
}
// Pad the option text with spaces using depth level as a multiplier.
foreach ($links as &$link) {
$link->text = str_repeat('- ', $link->level) . $link->text;
}
if (empty($menuType)) {
// If the menutype is empty, group the items by menutype.
$query = new JQuery();
$query->select('*');
$query->from('#__menu_types');
$query->where('menutype <> ' . $db->quote(''));
$query->order('title, menutype');
$db->setQuery($query);
$menuTypes = $db->loadObjectList();
// Check for a database error.
if ($error = $db->getErrorMsg()) {
JError::raiseWarning(500, $error);
return false;
}
// Create a reverse lookup and aggregate the links.
$rlu = array();
foreach ($menuTypes as &$type) {
$rlu[$type->menutype] =& $type;
$type->links = array();
}
// Loop through the list of menu links.
foreach ($links as &$link) {
if (isset($rlu[$link->menutype])) {
$rlu[$link->menutype]->links[] =& $link;
// Cleanup garbage.
unset($link->menutype);
}
}
return $menuTypes;
} else {
return $links;
}
}
/**
* Gets a list of the asset groups as an array of JHtml compatible options.
*
* @param array $config An array of options for the options
*
* @return mixed An array or false if an error occurs
*/
public static function assetgroups($config = array())
{
if (empty(JHtmlAccess::$asset_groups)) {
$db =& JFactory::getDbo();
$query = new JQuery();
$query->select('a.id AS value, a.title AS text');
$query->from('#__viewlevels AS a');
$query->group('a.id');
$query->order('a.ordering ASC');
$db->setQuery($query);
JHtmlAccess::$asset_groups = $db->loadObjectList();
// Check for a database error.
if ($db->getErrorNum()) {
JError::raiseNotice(500, $db->getErrorMsg());
return false;
}
}
return JHtmlAccess::$asset_groups;
}
/**
* Build an SQL query to load the list data.
*
* @return JQuery
*/
protected function _getListQuery()
{
// Create a new query object.
$query = new JQuery();
// Select the required fields from the table.
$query->select($this->getState('list.select', 'a.id AS id,' . 'a.name AS name,' . 'a.contact AS contact,' . 'a.checked_out AS checked_out,' . 'a.checked_out_time AS checked_out_time, ' . 'a.state AS state,' . 'a.metakey AS metakey,' . 'a.purchase_type as purchase_type'));
$query->from('`#__banner_clients` AS a');
// Join over the banners for counting
$query->select('COUNT(b.id) as nbanners');
$query->join('LEFT', '#__banners AS b ON a.id = b.cid');
// Join over the users for the checked out user.
$query->select('uc.name AS editor');
$query->join('LEFT', '#__users AS uc ON uc.id=a.checked_out');
// Filter by published state
$published = $this->getState('filter.state');
if (is_numeric($published)) {
$query->where('a.state = ' . (int) $published);
} else {
if ($published === '') {
$query->where('(a.state IN (0, 1))');
}
}
$query->group('a.id');
// Filter by search in title
$search = $this->getState('filter.search');
if (!empty($search)) {
if (stripos($search, 'id:') === 0) {
$query->where('a.id = ' . (int) substr($search, 3));
} else {
$search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%');
$query->where('a.name LIKE ' . $search);
}
}
// Add the list ordering clause.
$query->order($this->_db->getEscaped($this->getState('list.ordering', 'ordering')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC')));
//echo nl2br(str_replace('#__','jos_',$query));
return $query;
}
/**
* Generic check for whether dependancies exist for this object in the database schema
*
* Can be overloaded/supplemented by the child class
*
* @deprecated
* @param mixed An optional primary key value check the row for. If not
* set the instance property value is used.
* @param array An optional array to compiles standard joins formatted like:
* [label => 'Label', name => 'table name' , idfield => 'field', joinfield => 'field']
* @return boolean True on success.
* @since 1.0
* @link http://docs.joomla.org/JTable/canDelete
*/
public function canDelete($pk = null, $joins = null)
{
// Initialise variables.
$k = $this->_tbl_key;
$pk = is_null($pk) ? $this->{$k} : $pk;
// If no primary key is given, return false.
if ($pk === null) {
return false;
}
if (is_array($joins)) {
// Get a query object.
jimport('joomla.database.query');
$query = new JQuery();
// Setup the basic query.
$query->select('`' . $this->_tbl_key . '`');
$query->from('`' . $this->_tbl . '`');
$query->where('`' . $this->_tbl_key . '` = ' . $this->_db->quote($this->{$k}));
$query->group('`' . $this->_tbl_key . '`');
// For each join add the select and join clauses to the query object.
foreach ($joins as $table) {
$query->select('COUNT(DISTINCT ' . $table['idfield'] . ') AS ' . $table['idfield']);
$query->join('LEFT', $table['name'] . ' ON ' . $table['joinfield'] . ' = ' . $k);
}
// Get the row object from the query.
$this->_db->setQuery((string) $query, 0, 1);
$row = $this->_db->loadObject();
// Check for a database error.
if ($this->_db->getErrorNum()) {
$this->setError($this->_db->getErrorMsg());
return false;
}
$msg = array();
$i = 0;
foreach ($joins as $table) {
$k = $table['idfield'] . $i;
if ($obj->{$k}) {
$msg[] = JText::_($table['label']);
}
$i++;
}
if (count($msg)) {
$this->setError("noDeleteRecord" . ": " . implode(', ', $msg));
return false;
} else {
return true;
}
}
return true;
}
/**
* Build an SQL query to load the list data.
*
* @return JQuery
*/
protected function _getListQuery()
{
// Create a new query object.
$query = new JQuery();
// Select the required fields from the table.
$query->select($this->getState('list.select', 'a.id, a.title, a.position, a.module, ' . 'a.checked_out, a.checked_out_time, a.published, a.access, a.ordering, a.language'));
$query->from('`#__modules` AS a');
// Join over the users for the checked out user.
$query->select('uc.name AS editor');
$query->join('LEFT', '#__users AS uc ON uc.id=a.checked_out');
// Join over the asset groups.
$query->select('ag.title AS access_level');
$query->join('LEFT', '#__viewlevels AS ag ON ag.id = a.access');
// Join over the module menus
$query->select('MIN(mm.menuid) AS pages');
$query->join('LEFT', '#__modules_menu AS mm ON mm.moduleid = a.id');
$query->group('a.id');
// Filter by access level.
if ($access = $this->getState('filter.access')) {
$query->where('a.access = ' . (int) $access);
}
// Filter by published state
$state = $this->getState('filter.state');
if (is_numeric($state)) {
$query->where('a.published = ' . (int) $state);
} else {
if ($state === '') {
$query->where('(a.published IN (0, 1))');
}
}
// Filter by position
$position = $this->getState('filter.position');
if ($position) {
$query->where('a.position = ' . $this->_db->Quote($position));
}
// Filter by module
$module = $this->getState('filter.module');
if ($module) {
$query->where('a.module = ' . $this->_db->Quote($module));
}
// Filter by client.
$clientId = $this->getState('filter.client_id');
if (is_numeric($clientId)) {
$query->where('a.client_id = ' . (int) $clientId);
}
// Filter by search in title
$search = $this->getState('filter.search');
if (!empty($search)) {
if (stripos($search, 'id:') === 0) {
$query->where('a.id = ' . (int) substr($search, 3));
} else {
$search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%');
$query->where('a.title LIKE ' . $search);
}
}
// Add the list ordering clause.
$query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.ordering')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC')));
//echo nl2br(str_replace('#__','jos_',$query));
return $query;
}
