/**
* Method to build the query for the tags
*
* @access private
* @return string
*/
public function _buildQuery($browsingMode = false)
{
// Get the WHERE and ORDER BY clauses for the query
$where = $this->_buildQueryWhere($browsingMode);
$orderby = $this->_buildQueryOrderBy();
$db = EB::db();
if ($browsingMode) {
$query = array();
$query[] = 'SELECT a.*, b.`content_id` AS `featured` FROM ' . $db->qn('#__users') . ' AS a ';
$query[] = 'LEFT JOIN ' . $db->qn('#__easyblog_featured') . ' AS b ';
$query[] = 'ON a.`id` = b.`content_id` AND b.`type`=' . $db->Quote('blogger');
$query[] = $where;
$query[] = $orderby;
$query = implode(' ', $query);
} else {
$aclQuery = EB::AclHelper()->genIsbloggerSQL();
$query = 'select count( p.id ) as `totalPost`, COUNT( DISTINCT(g.content_id) ) as `featured`,';
$query .= ' a.*';
$query .= ' from `#__users` as a';
$query .= ' left join `#__easyblog_post` as p on a.`id` = p.`created_by`';
$query .= ' and `p`.`published` = ' . $db->Quote(EASYBLOG_POST_PUBLISHED);
$query .= ' and p.`state` = ' . $db->Quote(EASYBLOG_POST_NORMAL);
$query .= ' left join `#__easyblog_featured` AS `g` ON a.`id`= g.`content_id` AND g.`type`= ' . $db->Quote('blogger');
$query .= ' where (' . $aclQuery . ')';
$query .= $where;
$query .= ' group by a.`id`';
$query .= $orderby;
}
return $query;
}
/**
* Method to build the query for the tags
*
* @access private
* @return string
*/
public function _buildQuery($bloggerOnly = false)
{
// Get the WHERE and ORDER BY clauses for the query
$where = $this->_buildQueryWhere();
$orderby = $this->_buildQueryOrderBy();
$db = EB::db();
if ($bloggerOnly) {
$aclQuery = EB::AclHelper()->genIsbloggerSQL();
$query = 'select a.* FROM `#__users` AS `a`';
$query .= $where;
$query .= $where ? ' and (' : ' where (';
$query .= $aclQuery . ')';
} else {
$query = 'SELECT * FROM ' . $db->nameQuote('#__users');
$query .= $where;
}
$query .= $orderby;
return $query;
}
/**
* Retrieves a list of bloggers from the site
*
* @since 5.0
* @access public
* @param string
* @return
*/
public function getBloggers($sort = 'latest', $limit = 0, $filter = 'showallblogger', $search = '', $inclusion = array(), $exclusion = array(), $featuredOnly = '')
{
$db = EB::db();
$config = EB::config();
$nameDisplayFormat = $config->get('layout_nameformat');
$limit = $limit == 0 ? $this->getState('limit') : $limit;
$limitstart = $this->input->get('limitstart', $this->getState('limitstart'), 'int');
$limitSQL = ' LIMIT ' . $limitstart . ',' . $limit;
$excludedQuery = '';
$excluded = $config->get('layout_exclude_bloggers');
// check if there is exclusion from the backend settings OR from the parameter
if (!empty($excluded) || !empty($exclusion)) {
$tmp = explode(',', $excluded);
if (!empty($excluded) && !empty($exclusion)) {
$tmp = array_merge($tmp, $exclusion);
}
$values = array();
foreach ($tmp as $id) {
$values[] = $db->Quote($id);
}
$excludedQuery = ' AND a.`id` NOT IN (' . implode(',', $values) . ')';
}
//inclusion blogger
$includedQuery = '';
if (!empty($inclusion)) {
$values = array();
foreach ($inclusion as $id) {
$values[] = $db->Quote($id);
}
$includedQuery = ' AND a.id IN (' . implode(',', $values) . ')';
}
$searchQuery = '';
if (!empty($search)) {
$searchQuery .= ' AND ';
switch ($nameDisplayFormat) {
case 'name':
$searchQuery .= '`name` LIKE ' . $db->Quote('%' . $search . '%');
break;
case 'username':
$searchQuery .= '`username` LIKE ' . $db->Quote('%' . $search . '%');
break;
default:
$searchQuery .= '`nickname` LIKE ' . $db->Quote('%' . $search . '%');
break;
}
}
$aclQuery = EB::AclHelper()->genIsbloggerSQL();
// $query = 'select count( p.id ) as `totalPost`, MAX(p.`created`) as `latestPostDate`, COUNT( DISTINCT(g.content_id) ) as `featured`,';
$query = 'select SQL_CALC_FOUND_ROWS count( p.id ) as `totalPost`, MAX(p.`created`) as `latestPostDate`, COUNT( DISTINCT(g.content_id) ) as `featured`,';
$query .= ' a.`id`, b.`nickname`, a.`name`, a.`username`, a.`registerDate`, a.`lastvisitDate`, b.`permalink`';
$query .= ' from `#__users` as a';
$query .= ' left join `#__easyblog_post` as p on a.`id` = p.`created_by`';
$query .= ' inner JOIN `#__easyblog_users` AS `b` ON p.`created_by` = b.`id`';
$query .= ' left join `#__easyblog_featured` AS `g` ON a.`id`= g.`content_id` AND g.`type`= ' . $db->Quote('blogger');
$query .= ' where (' . $aclQuery . ')';
$query .= ' and `p`.`published` = ' . $db->Quote(EASYBLOG_POST_PUBLISHED);
$query .= ' and p.`state` = ' . $db->Quote(EASYBLOG_POST_NORMAL);
if ($featuredOnly) {
$query .= ' and a.`id` = g.`content_id`';
}
$query .= $excludedQuery;
$query .= $includedQuery;
$query .= $searchQuery;
$query .= ' group by a.`id`';
if ($filter == 'showbloggerwithpost') {
$query .= ' having (count(p.id) > 0)';
}
switch ($sort) {
case 'featured':
$query .= ' ORDER BY `featured` DESC';
break;
case 'latestpost':
$query .= ' ORDER BY `latestPostDate` DESC';
break;
case 'latest':
$query .= ' ORDER BY a.`registerDate` DESC';
break;
case 'postcount':
$query .= ' ORDER BY `totalPost` DESC';
break;
case 'active':
$query .= ' ORDER BY a.`lastvisitDate` DESC';
break;
case 'alphabet':
if ($nameDisplayFormat == 'name') {
$query .= ' ORDER BY a.`name` ASC';
} else {
if ($nameDisplayFormat == 'username') {
$query .= ' ORDER BY a.`username` ASC';
} else {
$query .= ' ORDER BY b.`nickname` ASC';
}
}
break;
default:
break;
}
$query .= $limitSQL;
$db->setQuery($query);
$results = $db->loadObjectList();
// now execute found_row() to get the number of records found.
$cntQuery = 'select FOUND_ROWS()';
$db->setQuery($cntQuery);
$this->_total = $db->loadResult();
if (empty($this->_pagination)) {
jimport('joomla.html.pagination');
$this->_pagination = EB::pagination($this->_total, $limitstart, $limit);
}
return $results;
}
/**
* Retrieves the total number of authors
*
* @since 4.0
* @access public
* @param string
* @return
*/
public function getTotalAuthors()
{
$db = EB::db();
$aclQuery = EB::AclHelper()->genIsbloggerSQL();
$query = 'select count(1)';
$query .= ' from `#__users` as a';
$query .= ' where a.`block` = ' . $db->Quote('0');
$query .= ' and (' . $aclQuery . ')';
$db->setQuery($query);
$result = $db->loadResult();
return $result;
}
