function getPagination()
{
// Lets load the content if it doesn't already exist
if (empty($this->_pagination)) {
jimport('joomla.html.pagination');
$this->_pagination = EasyBlogHelper::getPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit'));
}
return $this->_pagination;
}
function getCategories($sort = 'latest', $hideEmptyPost = true, $limit = 0, $inclusion = array())
{
$db = EasyBlogHelper::db();
//blog privacy setting
$my = JFactory::getUser();
$isBloggerMode = EasyBlogRouter::isBloggerMode();
$orderBy = '';
$limit = $limit == 0 ? $this->getState('limit') : $limit;
$limitstart = JRequest::getInt('limitstart', $this->getState('limitstart'));
$limitSQL = ' LIMIT ' . $limitstart . ',' . $limit;
$extra = '';
$andWhere = array();
$andWhere[] = ' a.`published` = 1';
$andWhere[] = ' a.`parent_id` = 0';
$accessibleCatsIds = EasyBlogHelper::getAccessibleCategories();
// Respect inclusion categories
if (!empty($inclusion)) {
if (!empty($accessibleCatsIds)) {
$accessibleCatsIdsArray = array();
foreach ($accessibleCatsIds as $row) {
$accessibleCatsIdsArray[] = $row->id;
}
$inclusion = array_intersect($inclusion, $accessibleCatsIdsArray);
}
$inclusionQuery = ' a.`id` IN(';
if (!is_array($inclusion)) {
$inclusion = array($inclusion);
}
$inclusion = array_values($inclusion);
for ($i = 0; $i < count($inclusion); $i++) {
$inclusionQuery .= $db->Quote($inclusion[$i]);
if (next($inclusion) !== false) {
$inclusionQuery .= ',';
}
}
$inclusionQuery .= ')';
$andWhere[] = $inclusionQuery;
$extra .= ' AND ' . $inclusionQuery;
} else {
if (!empty($accessibleCatsIds)) {
$catAccessQuery = ' a.`id` IN(';
if (!is_array($accessibleCatsIds)) {
$accessibleCatsIds = array($accessibleCatsIds);
}
for ($i = 0; $i < count($accessibleCatsIds); $i++) {
$catAccessQuery .= $db->Quote($accessibleCatsIds[$i]->id);
if (next($accessibleCatsIds) !== false) {
$catAccessQuery .= ',';
}
}
$catAccessQuery .= ')';
$andWhere[] = $catAccessQuery;
}
}
if ($isBloggerMode !== false) {
$andWhere[] = ' (b.`created_by` = ' . $db->Quote($isBloggerMode) . ' OR a.`created_by` = ' . $db->Quote($isBloggerMode) . ')';
}
if ($hideEmptyPost) {
$arrParentIds = $this->_getParentIdsWithPost($accessibleCatsIds);
if (!empty($arrParentIds)) {
$tmpParentId = implode(',', $arrParentIds);
$andWhere[] = ' a.`id` IN (' . $tmpParentId . ')';
} else {
// this mean no categories fond. just return the empty result.
if (empty($this->_pagination)) {
jimport('joomla.html.pagination');
$this->_pagination = EasyBlogHelper::getPagination(0, $limitstart, $limit);
}
// return array();
}
$this->_total = count($arrParentIds);
} else {
$extra = count($andWhere) ? ' WHERE ' . implode(' AND ', $andWhere) : '';
$query = 'SELECT a.`id` FROM ' . EasyBlogHelper::getHelper('SQL')->nameQuote('#__easyblog_category') . ' AS a';
$query .= ' LEFT JOIN ' . EasyBlogHelper::getHelper('SQL')->nameQuote('#__easyblog_post') . ' AS b';
$query .= ' ON a.`id` = b.`category_id`';
$query .= ' AND b.`published` = ' . $db->Quote('1');
if ($my->id == 0) {
$query .= ' AND b.`private` = ' . $db->Quote(BLOG_PRIVACY_PUBLIC);
}
$query .= $extra;
$query .= ' GROUP BY a.`id`';
$db->setQuery($query);
$result = $db->loadResultArray();
$this->_total = count($result);
if ($db->getErrorNum()) {
JError::raiseError(500, $db->stderr());
}
}
if (empty($this->_pagination)) {
jimport('joomla.html.pagination');
$this->_pagination = EasyBlogHelper::getPagination($this->_total, $limitstart, $limit);
}
$extra = count($andWhere) ? ' WHERE ' . implode(' AND ', $andWhere) : '';
$query = 'SELECT a.`id`, a.`title`, a.`alias`, a.`private`, COUNT(b.`id`) AS `cnt`';
$query .= ' FROM ' . EasyBlogHelper::getHelper('SQL')->nameQuote('#__easyblog_category') . ' AS `a`';
$query .= ' LEFT JOIN ' . EasyBlogHelper::getHelper('SQL')->nameQuote('#__easyblog_post') . ' AS b';
$query .= ' ON a.`id` = b.`category_id`';
$query .= ' AND b.`published` = ' . $db->Quote('1');
if ($my->id == 0) {
$query .= ' AND b.`private` = ' . $db->Quote(BLOG_PRIVACY_PUBLIC);
}
$query .= $extra;
$query .= ' GROUP BY a.`id`';
switch ($sort) {
case 'popular':
$orderBy = ' ORDER BY `cnt` DESC';
break;
case 'alphabet':
$orderBy = ' ORDER BY a.`title` ASC';
break;
case 'ordering':
$orderBy = ' ORDER BY a.`lft` ASC';
break;
case 'latest':
default:
$orderBy = ' ORDER BY a.`created` DESC';
break;
}
$query .= $orderBy;
$query .= $limitSQL;
$db->setQuery($query);
$result = $db->loadObjectList();
if ($db->getErrorNum()) {
JError::raiseError(500, $db->stderr());
}
return $result;
}
function getCompojoomComment($max = 0, $userId = 0, $sort = 'latest', $base = 'comment', $search = '', $published = 'all')
{
$db = EasyBlogHelper::db();
$queryPagination = false;
$queryLimit = '';
$queryOrder = ' ORDER BY a.`date` DESC';
$queryWhere = '';
switch ($sort) {
case 'latest':
default:
$queryOrder = ' ORDER BY a.`date` DESC';
break;
}
if (!empty($userId)) {
if ($base == 'comment') {
$queryWhere .= ' WHERE a.`userid` = ' . $db->Quote($userId);
} else {
$queryWhere .= ' WHERE b.`created_by` = ' . $db->Quote($userId);
}
}
switch ($published) {
case 'published':
$queryWhere .= ' AND a.`published` = ' . $db->Quote('1');
break;
case 'unpublished':
$queryWhere .= ' AND a.`published` = ' . $db->Quote('0');
break;
case 'moderate':
$queryWhere .= ' AND a.`published` = ' . $db->Quote('2');
break;
case 'all':
default:
break;
}
if (!empty($search)) {
$queryWhere .= !empty($queryWhere) ? ' AND' : ' WHERE';
$queryWhere .= ' a.`comment` LIKE ' . $db->Quote('%' . $search . '%');
}
$queryWhere .= ' AND a.`component` = ' . $db->quote('com_easyblog');
if ($max > 0) {
$queryLimit = ' LIMIT ' . $max;
} else {
$limit = $this->getState('limit');
$limitstart = $this->getState('limitstart');
$queryLimit = ' LIMIT ' . $limitstart . ',' . $limit;
$queryPagination = true;
}
if ($queryPagination) {
$query = 'SELECT COUNT(1)';
$query .= ' FROM `#__comment` AS a INNER JOIN `#__easyblog_post` AS b';
$query .= ' ON a.`contentid` = b.`id`';
$query .= $queryWhere;
$db->setQuery($query);
$this->_total = $db->loadResult();
jimport('joomla.html.pagination');
$this->_pagination = EasyBlogHelper::getPagination($this->_total, $limitstart, $limit);
}
$query = 'SELECT' . ' a.`id` AS `id`,' . ' a.`contentid` AS `post_id`,' . ' a.`comment` AS `comment`,' . ' a.`name` AS `name`,' . ' a.`title` AS `title`,' . ' a.`email` AS `email`,' . ' a.`website` AS `url`,' . ' a.`ip` AS `ip`,' . ' a.`userid` AS `created_by`,' . ' a.`date` AS `created`,' . ' a.`date` AS `modified`,' . ' a.`published` AS `published`,' . ' ' . $db->quote('0000-00-00 00:00:00') . ' AS `publish_up`,' . ' ' . $db->quote('0000-00-00 00:00:00') . ' AS `publish_down`,' . ' ' . $db->quote('0') . ' AS `ordering`,' . ' a.`voting_yes` AS `vote`,' . ' ' . $db->quote('0') . ' AS `hits`,' . ' ' . $db->quote('1') . ' AS `sent`,' . ' ' . $db->quote('0') . ' AS `parent_id`,' . ' ' . $db->quote('0') . ' AS `lft`,' . ' ' . $db->quote('0') . ' AS `rgt`,' . ' b.`created_by` AS `blog_owner`, b.`title` AS `blog_title`' . ' FROM `#__comment` AS a INNER JOIN `#__easyblog_post` AS b' . ' ON a.`contentid` = b.`id`' . $queryWhere . $queryOrder . $queryLimit;
$db->setQuery($query);
$result = $db->loadObjectList();
if ($db->getErrorNum() > 0) {
JError::raiseError($db->getErrorNum(), $db->getErrorMsg() . $db->stderr());
}
return $result;
}
function getTeamBlogRequest($userId = '', $useLimit = true)
{
$db = EasyBlogHelper::db();
$limit = $this->getState('limit');
$limitstart = $this->getState('limitstart');
// common query
$cquery = '';
if (!empty($userId)) {
$cquery .= ' inner join `#__easyblog_team_users` as b';
$cquery .= ' on a.`team_id` = b.`team_id`';
$cquery .= ' and b.`user_id` = ' . $db->Quote($userId);
$cquery .= ' and b.`isadmin` = ' . $db->Quote('1');
}
$cquery .= ' inner join `#__easyblog_team` as c on a.`team_id` = c.`id`';
$cquery .= ' where a.`ispending` = ' . $db->Quote('1');
$query = 'select count(1) from `#__easyblog_team_request` as a';
$query .= $cquery;
$db->setQuery($query);
$this->_total = $db->loadResult();
jimport('joomla.html.pagination');
$this->_pagination = EasyBlogHelper::getPagination($this->_total, $limitstart, $limit);
//actual query
$query = 'select a.*, c.`title` from `#__easyblog_team_request` as a';
$query .= $cquery;
$query .= ' order by a.`created`';
if ($useLimit) {
$query .= ' LIMIT ' . $limitstart . ',' . $limit;
}
$db->setQuery($query);
$result = $db->loadObjectList();
return $result;
}
function getPaginationByTagBlogs()
{
jimport('joomla.html.pagination');
$this->_pagination = EasyBlogHelper::getPagination($this->getTotalByTagBlogs(), $this->getState('limitstart'), $this->getState('limit'));
return $this->_pagination;
}
function getArchive($archiveYear, $archiveMonth, $archiveDay = '')
{
$db = EasyBlogHelper::db();
$my = JFactory::getUser();
$config = EasyBlogHelper::getConfig();
$isBloggerMode = EasyBlogRouter::isBloggerMode();
$excludeCats = array();
$teamBlogIds = '';
$queryExclude = '';
$queryInclude = '';
$modCid = JRequest::getVar('modCid', array());
//where
$queryWhere = ' WHERE a.`published` = 1';
$queryWhere .= ' AND a.`ispending` = 0';
//get teamblogs id.
$query = '';
if ($config->get('main_includeteamblogpost')) {
$teamBlogIds = EasyBlogHelper::getViewableTeamIds();
if (count($teamBlogIds) > 0) {
$teamBlogIds = implode(',', $teamBlogIds);
}
}
//var_dump($teamBlogIds);
$excludeCats = EasyBlogHelper::getPrivateCategories();
if (!empty($excludeCats)) {
$queryExclude .= ' AND a.`category_id` NOT IN (' . implode(',', $excludeCats) . ')';
}
if (!empty($modCid)) {
$queryInclude .= ' AND a.`category_id` IN (' . implode(',', $modCid) . ')';
}
//do not list out protected blog in rss
if (JRequest::getCmd('format', '') == 'feed') {
if ($config->get('main_password_protect', true)) {
$queryWhere .= ' AND a.`blogpassword`="" ';
}
}
//blog privacy setting
// @integrations: jomsocial privacy
$file = JPATH_ROOT . DIRECTORY_SEPARATOR . 'components' . DIRECTORY_SEPARATOR . 'com_community' . DIRECTORY_SEPARATOR . 'libraries' . DIRECTORY_SEPARATOR . 'core.php';
$easysocial = EasyBlogHelper::getHelper('EasySocial');
if ($config->get('integrations_easysocial_privacy') && $easysocial->exists() && !EasyBlogHelper::isSiteAdmin()) {
$esPrivacyQuery = $easysocial->buildPrivacyQuery('a');
$queryWhere .= $esPrivacyQuery;
} else {
if ($config->get('main_jomsocial_privacy') && JFile::exists($file) && !EasyBlogHelper::isSiteAdmin()) {
require_once $file;
$my = JFactory::getUser();
$jsFriends = CFactory::getModel('Friends');
$friends = $jsFriends->getFriendIds($my->id);
// Insert query here.
$queryWhere .= ' AND (';
$queryWhere .= ' (a.`private`= 0 ) OR';
$queryWhere .= ' ( (a.`private` = 20) AND (' . $db->Quote($my->id) . ' > 0 ) ) OR';
if (empty($friends)) {
$queryWhere .= ' ( (a.`private` = 30) AND ( 1 = 2 ) ) OR';
} else {
$queryWhere .= ' ( (a.`private` = 30) AND ( a.' . EasyBlogHelper::getHelper('SQL')->nameQuote('created_by') . ' IN (' . implode(',', $friends) . ') ) ) OR';
}
$queryWhere .= ' ( (a.`private` = 40) AND ( a.' . EasyBlogHelper::getHelper('SQL')->nameQuote('created_by') . '=' . $my->id . ') )';
$queryWhere .= ' )';
} else {
if ($my->id == 0) {
$queryWhere .= ' AND a.`private` = ' . $db->Quote(BLOG_PRIVACY_PUBLIC);
}
}
}
if ($config->get('main_includeteamblogpost') && !empty($teamBlogIds)) {
$queryWhere .= ' AND (u.team_id IN (' . $teamBlogIds . ') OR a.`issitewide` = ' . $db->Quote('1') . ')';
} else {
$queryWhere .= ' AND a.`issitewide` = ' . $db->Quote('1');
}
if (empty($archiveDay)) {
$fromDate = $archiveYear . '-' . $archiveMonth . '-01 00:00:00';
$toDate = $archiveYear . '-' . $archiveMonth . '-31 23:59:59';
} else {
$fromDate = $archiveYear . '-' . $archiveMonth . '-' . $archiveDay . ' 00:00:00';
$toDate = $archiveYear . '-' . $archiveMonth . '-' . $archiveDay . ' 23:59:59';
}
// When language filter is enabled, we need to detect the appropriate contents
if (EasyBlogHelper::getJoomlaVersion() >= '1.6') {
$filterLanguage = JFactory::getApplication()->getLanguageFilter();
if ($filterLanguage) {
$queryWhere .= ' AND (';
$queryWhere .= ' a.`language`=' . $db->Quote(JFactory::getLanguage()->getTag());
$queryWhere .= ' OR a.`language`=' . $db->Quote('');
$queryWhere .= ' )';
}
}
$tzoffset = EasyBlogDateHelper::getOffSet(true);
$queryWhere .= ' AND ( DATE_ADD(a.`created`, INTERVAL ' . $tzoffset . ' HOUR) >= ' . $db->Quote($fromDate) . ' AND DATE_ADD(a.`created`, INTERVAL ' . $tzoffset . ' HOUR) <= ' . $db->Quote($toDate) . ' ) ';
if ($isBloggerMode !== false) {
$queryWhere .= ' AND a.`created_by` = ' . $db->Quote($isBloggerMode);
}
//ordering
$queryOrder = ' ORDER BY a.`created` DESC';
//limit
$limit = $this->getState('limit');
$limitstart = $this->getState('limitstart');
$queryLimit = ' LIMIT ' . $limitstart . ',' . $limit;
//set pagination
$query = 'SELECT COUNT(1) FROM `#__easyblog_post` AS a';
$query .= ' LEFT JOIN `#__easyblog_category` AS b';
$query .= ' ON a.category_id = b.id';
if ($config->get('main_includeteamblogpost') && !empty($teamBlogIds)) {
$query .= ' LEFT JOIN `#__easyblog_team_post` AS u ON a.id = u.post_id';
}
$query .= $queryWhere;
$db->setQuery($query);
$this->_total = $db->loadResult();
jimport('joomla.html.pagination');
$this->_pagination = EasyBlogHelper::getPagination($this->_total, $limitstart, $limit);
//get archive
$query = 'SELECT a.*, b.`title` AS `category`';
if ($config->get('main_includeteamblogpost') && !empty($teamBlogIds)) {
$query .= ' ,u.team_id';
}
$query .= ' FROM `#__easyblog_post` AS a';
$query .= ' LEFT JOIN `#__easyblog_category` AS b';
$query .= ' ON a.category_id = b.id';
if ($config->get('main_includeteamblogpost') && !empty($teamBlogIds)) {
$query .= ' LEFT JOIN `#__easyblog_team_post` AS u ON a.id = u.post_id';
}
$query .= $queryWhere;
$query .= $queryExclude;
$query .= $queryInclude;
$query .= $queryOrder;
$query .= $queryLimit;
// echo $query . '<br><br>';
$db->setQuery($query);
if ($db->getErrorNum() > 0) {
JError::raiseError($db->getErrorNum(), $db->getErrorMsg() . $db->stderr());
}
$result = $db->loadObjectList();
return $result;
}
function getCategoriesHierarchy()
{
$db = EasyBlogHelper::db();
$limit = '10';
$limitstart = $this->getState('limitstart');
$search = JRequest::getVar('search', '');
$gid = EasyBlogHelper::getUserGids();
$gids = '';
if (count($gid) > 0) {
foreach ($gid as $id) {
$gids .= empty($gids) ? $db->Quote($id) : ',' . $db->Quote($id);
}
}
$query = 'SELECT a.*, ( SELECT COUNT(id) FROM `#__easyblog_category` WHERE `lft` < a.`lft` AND `rgt` > a.`rgt`) AS depth';
$query .= ' FROM `#__easyblog_category` AS a';
$query .= ' WHERE a.`published` = ' . $db->Quote('1');
if (!empty($search)) {
$query .= ' AND a.`title` LIKE ' . $db->Quote('%' . $search . '%');
}
$query .= ' and a.id not in (';
$query .= ' select id from `#__easyblog_category` as c';
$query .= ' where not exists (';
$query .= ' select b.category_id from `#__easyblog_category_acl` as b';
$query .= ' where b.category_id = c.id and b.`acl_id` = ' . $db->Quote(CATEGORY_ACL_ACTION_SELECT);
$query .= ' and b.type = ' . $db->Quote('group');
$query .= ' and b.content_id IN (' . $gids . ')';
$query .= ' )';
$query .= ' and c.`private` = ' . $db->Quote(CATEGORY_PRIVACY_ACL);
$query .= ')';
$query .= ' AND a.`parent_id` NOT IN (SELECT `id` FROM `#__easyblog_category` AS e WHERE e.`published` = ' . $db->Quote('0') . ' AND e.`parent_id` = ' . $db->Quote('0') . ' )';
$query .= ' ORDER BY a.`lft`';
$this->_total = $this->_getListCount($query);
jimport('joomla.html.pagination');
$this->_pagination = EasyBlogHelper::getPagination($this->_total, $limitstart, $limit);
$query .= ' LIMIT ' . $limitstart . ', ' . $limit;
//echo $query;
$db->setQuery($query);
$result = $db->loadObjectList();
return $result;
}
/**
* Method to retrieve blog posts based on the given tag id.
*
* @access public
* @param int $tagId The tag id.
* @return array $rows An array of blog objects.
*/
function getTaggedBlogs($tagId = 0, $limit = false, $includeCatIds = '')
{
if ($tagId == 0) {
return false;
}
$my = JFactory::getUser();
$db = EasyBlogHelper::db();
$config = EasyBlogHelper::getConfig();
if ($limit === false) {
if ($config->get('layout_listlength') == 0) {
$limit = $this->getState('limit');
} else {
$limit = $config->get('layout_listlength');
}
}
$limitstart = $this->getState('limitstart');
$isBloggerMode = EasyBlogRouter::isBloggerMode();
$queryExclude = '';
$excludeCats = array();
$isJSGrpPluginInstalled = false;
$isJSGrpPluginInstalled = JPluginHelper::isEnabled('system', 'groupeasyblog');
$isEventPluginInstalled = JPluginHelper::isEnabled('system', 'eventeasyblog');
$isJSInstalled = false;
// need to check if the site installed jomsocial.
if (JFile::exists(JPATH_ROOT . DIRECTORY_SEPARATOR . 'components' . DIRECTORY_SEPARATOR . 'com_community' . DIRECTORY_SEPARATOR . 'libraries' . DIRECTORY_SEPARATOR . 'core.php')) {
$isJSInstalled = true;
}
$includeJSGrp = $isJSGrpPluginInstalled && $isJSInstalled ? true : false;
$includeJSEvent = $isEventPluginInstalled && $isJSInstalled ? true : false;
$jsGrpPostIds = '';
$jsEventPostIds = '';
if ($includeJSEvent) {
$queryEvent = 'SELECT ' . EasyBlogHelper::getHelper('SQL')->nameQuote('post_id') . ' FROM';
$queryEvent .= ' ' . EasyBlogHelper::getHelper('SQL')->nameQuote('#__easyblog_external') . ' AS ' . EasyBlogHelper::getHelper('SQL')->nameQuote('a');
$queryEvent .= ' INNER JOIN' . EasyBlogHelper::getHelper('SQL')->nameQuote('#__community_events') . ' AS ' . EasyBlogHelper::getHelper('SQL')->nameQuote('b');
$queryEvent .= ' ON ' . EasyBlogHelper::getHelper('SQL')->nameQuote('a') . '.uid = ' . EasyBlogHelper::getHelper('SQL')->nameQuote('b') . '.id';
$queryEvent .= ' AND ' . EasyBlogHelper::getHelper('SQL')->nameQuote('a') . '.' . EasyBlogHelper::getHelper('SQL')->nameQuote('source') . '=' . $db->Quote('jomsocial.event');
$queryEvent .= ' WHERE ' . EasyBlogHelper::getHelper('SQL')->nameQuote('b') . '.' . EasyBlogHelper::getHelper('SQL')->nameQuote('permission') . '=' . $db->Quote(0);
$db->setQuery($queryEvent);
$jsEventPostIds = $db->loadResultArray();
if (!empty($excludeBlogs) && !empty($jsEventPostIds)) {
$jsEventPostIds = array_diff($jsEventPostIds, $excludeBlogs);
}
}
if ($includeJSGrp) {
$queryJSGrp = 'select `post_id` from `#__easyblog_external_groups` as exg inner join `#__community_groups` as jsg';
$queryJSGrp .= ' on exg.group_id = jsg.id ';
$queryJSGrp .= ' where jsg.`approvals` = 0';
$db->setQuery($queryJSGrp);
$jsGrpPostIds = $db->loadResultArray();
if (!empty($excludeBlogs) && !empty($jsGrpPostIds)) {
$jsGrpPostIds = array_diff($jsGrpPostIds, $excludeBlogs);
}
}
//get teamblogs id.
if ($config->get('main_includeteamblogpost')) {
$teamBlogIds = EasyBlogHelper::getViewableTeamIds();
if (count($teamBlogIds) > 0) {
$teamBlogIds = implode(',', $teamBlogIds);
}
}
// get all private categories id
$excludeCats = EasyBlogHelper::getPrivateCategories();
if (!empty($excludeCats)) {
$queryExclude .= ' AND b.`category_id` NOT IN (' . implode(',', $excludeCats) . ')';
}
$query = 'SELECT b.*, c.`title` as `category`';
if ($config->get('main_includeteamblogpost') && !empty($teamBlogIds)) {
$query .= ' ,u.`team_id`';
}
$query .= ' FROM ' . EasyBlogHelper::getHelper('SQL')->nameQuote('#__easyblog_post_tag') . ' AS a ';
$query .= ' INNER JOIN ' . EasyBlogHelper::getHelper('SQL')->nameQuote('#__easyblog_post') . ' AS b ';
$query .= ' ON a.post_id=b.id ';
$query .= ' LEFT JOIN `#__easyblog_category` AS c';
$query .= ' ON b.category_id = c.id';
if ($config->get('main_includeteamblogpost') && !empty($teamBlogIds)) {
$query .= ' LEFT JOIN `#__easyblog_team_post` AS u ON b.id = u.post_id';
}
$query .= ' WHERE a.tag_id = ' . $db->Quote($tagId);
$query .= ' AND b.`published` = ' . $db->Quote('1');
if (EasyBlogHelper::getJoomlaVersion() >= '1.6') {
// @rule: When language filter is enabled, we need to detect the appropriate contents
$filterLanguage = JFactory::getApplication()->getLanguageFilter();
if ($filterLanguage) {
$query .= ' AND (';
$query .= ' b.`language`=' . $db->Quote(JFactory::getLanguage()->getTag());
$query .= ' OR b.`language`=' . $db->Quote('');
$query .= ' OR b.`language`=' . $db->Quote('*');
$query .= ' )';
}
}
$file = JPATH_ROOT . DIRECTORY_SEPARATOR . 'components' . DIRECTORY_SEPARATOR . 'com_community' . DIRECTORY_SEPARATOR . 'libraries' . DIRECTORY_SEPARATOR . 'core.php';
//blog privacy setting
if ($config->get('main_jomsocial_privacy') && JFile::exists($file) && !EasyBlogHelper::isSiteAdmin()) {
require_once $file;
$jsFriends = CFactory::getModel('Friends');
$friends = $jsFriends->getFriendIds($my->id);
array_push($friends, $my->id);
// Insert query here.
$query .= ' AND (';
$query .= ' (b.`private`= 0 ) OR';
$query .= ' ( (b.`private` = 20) AND (' . $db->Quote($my->id) . ' > 0 ) ) OR';
if (empty($friends)) {
$query .= ' ( (b.`private` = 30) AND ( 1 = 2 ) ) OR';
} else {
$query .= ' ( (b.`private` = 30) AND ( b.' . EasyBlogHelper::getHelper('SQL')->nameQuote('created_by') . ' IN (' . implode(',', $friends) . ') ) ) OR';
}
$query .= ' ( (b.`private` = 40) AND ( b.' . EasyBlogHelper::getHelper('SQL')->nameQuote('created_by') . '=' . $my->id . ') )';
$query .= ' )';
} else {
if ($my->id == 0) {
$query .= ' AND b.`private` = ' . $db->Quote(BLOG_PRIVACY_PUBLIC);
}
}
if ($isBloggerMode !== false) {
$query .= ' AND b.`created_by` = ' . $db->Quote($isBloggerMode);
}
// if( !empty( $jsGrpPostIds ) )
// {
// $tmpIds = implode( ',', $jsGrpPostIds);
// $query .= ' AND ( b.id IN (' . $tmpIds . ') OR b.`issitewide` = ' . $db->Quote('1') . ')';
// }
$jsTmpIds = array();
if ($jsGrpPostIds) {
$jsTmpIds = array_merge($jsGrpPostIds, $jsTmpIds);
}
if ($jsEventPostIds) {
$jsTmpIds = array_merge($jsEventPostIds, $jsTmpIds);
}
if ($jsTmpIds) {
$jsTmpIds = array_unique($jsTmpIds);
$extmpIds = implode(',', $jsTmpIds);
$query .= ' AND ( b.id IN (' . $extmpIds . ') OR b.`issitewide` = ' . $db->Quote('1') . ')';
}
if ($config->get('main_includeteamblogpost') && (!empty($teamBlogIds) || !empty($jsTmpIds))) {
$query .= ' AND (';
if (!empty($teamBlogIds)) {
$query .= ' u.`team_id` IN (' . $teamBlogIds . ')';
}
if ($jsTmpIds) {
$extmpIds = implode(',', $jsTmpIds);
$query .= $teamBlogIds ? ' OR' : '';
$query .= ' b.`id` IN(' . $extmpIds . ')';
}
$query .= ' OR b.`issitewide`=' . $db->Quote(1);
$query .= ')';
} else {
$query .= ' AND b.`issitewide` = ' . $db->Quote('1');
}
$includeCats = array();
$includeCatIds = trim($includeCatIds);
if (!empty($includeCatIds)) {
$includeCats = explode(',', $includeCatIds);
if (!empty($excludeCats)) {
$includeCats = array_diff($includeCats, $excludeCats);
}
if (!empty($includeCats)) {
$query .= ' AND b.`category_id` IN (' . implode(',', $includeCats) . ')';
}
}
$query .= $queryExclude;
//$query .= ' ORDER BY `created` DESC';
$sort = $config->get('layout_postorder', 'latest');
$defaultSorting = $config->get('layout_postsort', 'desc');
switch ($sort) {
case 'latest':
$queryOrder = ' ORDER BY b.`created` ' . $defaultSorting;
break;
case 'published':
$queryOrder = ' ORDER BY b.`publish_up` ' . $defaultSorting;
break;
case 'popular':
$queryOrder = ' ORDER BY b.`hits` ' . $defaultSorting;
break;
case 'active':
$queryOrder = ' ORDER BY b.`publish_down` ' . $defaultSorting;
break;
case 'alphabet':
$queryOrder = ' ORDER BY b.`title` ' . $defaultSorting;
break;
case 'modified':
$queryOrder = ' ORDER BY b.`modified` ' . $defaultSorting;
break;
case 'random':
$queryOrder = ' ORDER BY RAND() ';
break;
default:
break;
}
$query .= $queryOrder;
//total tag's post sql
$totalQuery = 'SELECT COUNT(1) FROM (';
$totalQuery .= $query;
$totalQuery .= ') as x';
$query .= ' LIMIT ' . $limitstart . ',' . $limit;
$db->setQuery($query);
$rows = $db->loadObjectList();
$db->setQuery($totalQuery);
$db->loadResult();
$this->_total = $db->loadResult();
jimport('joomla.html.pagination');
$this->_pagination = EasyBlogHelper::getPagination($this->_total, $limitstart, $limit);
return $rows;
}
function getAllBloggers($sort = 'latest', $limit = 0, $filter = 'showallblogger', $search = '')
{
$db = EasyBlogHelper::db();
require_once EBLOG_HELPERS . DIRECTORY_SEPARATOR . 'helper.php';
$config = EasyBlogHelper::getConfig();
$nameDisplayFormat = $config->get('layout_nameformat');
$limit = $limit == 0 ? $this->getState('limit') : $limit;
$limitstart = $this->getState('limitstart');
$limitSQL = ' LIMIT ' . $limitstart . ',' . $limit;
//first let get the id for add_entry acl
$query = 'select `id` from `#__easyblog_acl` where `action` = ' . $db->Quote('add_entry ');
$db->setQuery($query);
$aclId = $db->loadResult();
$query = 'SELECT COUNT(1) FROM (';
$query .= ' (SELECT a.`id`, b.`nickname`, b.`avatar`, b.`description`,';
$query .= ' a.`name`, a.`username`, a.`registerDate`, a.`lastvisitDate` ';
$query .= ' FROM `#__users` AS `a`';
if (EasyBlogHelper::getJoomlaVersion() >= '1.6') {
$query .= ' INNER JOIN `#__user_usergroup_map` AS `d` ON a.`id` = d.`user_id`';
} else {
$query .= ' INNER JOIN `#__core_acl_aro` AS `c` ON a.`id` = c.`value`';
$query .= ' AND c.`section_value` = ' . $db->Quote('users');
$query .= ' INNER JOIN `#__core_acl_groups_aro_map` AS `d` ON c.`id` = d.`aro_id`';
}
$query .= ' INNER JOIN `#__easyblog_acl_group` AS `e` ON d.`group_id` = e.`content_id`';
$query .= ' LEFT JOIN `#__easyblog_users` AS `b` ON a.`id` = b.`id`';
$query .= ' LEFT JOIN `#__easyblog_post` AS `p` ON a.`id` = p.`created_by`';
$query .= ' AND `p`.' . EasyBlogHelper::getHelper('SQL')->nameQuote('published') . '=' . $db->Quote(POST_ID_PUBLISHED);
$query .= ' WHERE e.acl_id = ' . $db->Quote($aclId);
$query .= ' AND e.`type` = ' . $db->Quote('group') . ' AND e.`status` = 1';
$query .= ' GROUP BY a.`id`';
if ($filter == 'showbloggerwithpost') {
$query .= ' HAVING (COUNT(p.`id`) > 0)';
}
$query .= ' )';
$query .= ' UNION ';
$query .= ' (SELECT a1.`id`, b1.`nickname`, b1.`avatar`, b1.`description`,';
$query .= ' a1.`name`, a1.`username`, a1.`registerDate`, a1.`lastvisitDate` ';
$query .= ' FROM `#__users` AS `a1`';
$query .= ' INNER JOIN `#__easyblog_acl_group` AS `c1` ON a1.`id` = c1.`content_id`';
$query .= ' LEFT JOIN `#__easyblog_users` AS `b1` ON a1.`id` = b1.`id`';
$query .= ' LEFT JOIN `#__easyblog_post` AS `p1` ON a1.`id` = p1.`created_by`';
$query .= ' AND `p1`.' . EasyBlogHelper::getHelper('SQL')->nameQuote('published') . '=' . $db->Quote(POST_ID_PUBLISHED);
$query .= ' WHERE c1.acl_id = ' . $db->Quote($aclId);
$query .= ' AND c1.`type` = ' . $db->Quote('assigned') . ' AND c1.`status` = 1';
$query .= ' GROUP BY a1.`id`';
if ($filter == 'showbloggerwithpost') {
$query .= ' HAVING (COUNT(p1.`id`) > 0)';
}
$query .= ' )';
$query .= ' ) as x';
$excludedQuery = '';
$excluded = EasyBlogHelper::getConfig()->get('layout_exclude_bloggers');
if (!empty($excluded)) {
$tmp = explode(',', $excluded);
$values = array();
foreach ($tmp as $id) {
$values[] = $db->Quote($id);
}
$excludedQuery = ' WHERE x.`id` NOT IN(' . implode(',', $values) . ')';
}
$query .= $excludedQuery;
// Ensure that the count also respects the search
if (!empty($search)) {
switch ($nameDisplayFormat) {
case 'name':
$query .= 'AND x.`name` LIKE ' . $db->Quote('%' . $search . '%');
break;
case 'username':
$query .= 'AND x.`username` LIKE ' . $db->Quote('%' . $search . '%');
break;
default:
$query .= 'AND x.`nickname` LIKE ' . $db->Quote('%' . $search . '%');
break;
}
}
$db->setQuery($query);
$this->_total = $db->loadResult();
if ($db->getErrorNum()) {
var_dump($db->stderr());
exit;
JError::raiseError(500, $db->stderr());
}
if (empty($this->_pagination)) {
jimport('joomla.html.pagination');
$this->_pagination = EasyBlogHelper::getPagination($this->_total, $limitstart, $limit);
}
$query = 'SELECT x.* FROM (';
$query .= ' (SELECT a.`id`, b.`nickname`, b.`avatar`, b.`description`,';
$query .= ' a.`name`, a.`username`, a.`registerDate`, a.`lastvisitDate`,';
$query .= ' COUNT(p.`id`) as `totalPost`, MAX(p.`created`) as `latestPostDate`,';
$query .= ' COUNT( DISTINCT(g.content_id) ) as `featured`';
$query .= ' FROM `#__users` AS `a`';
$query .= ' LEFT JOIN `#__easyblog_users` AS `b` ON a.`id` = b.`id`';
if (EasyBlogHelper::getJoomlaVersion() >= '1.6') {
$query .= ' INNER JOIN `#__user_usergroup_map` AS `d` ON a.`id` = d.`user_id`';
} else {
$query .= ' INNER JOIN `#__core_acl_aro` AS `c` ON a.`id` = c.`value`';
$query .= ' AND c.`section_value` = ' . $db->Quote('users');
$query .= ' INNER JOIN `#__core_acl_groups_aro_map` AS `d` ON c.`id` = d.`aro_id`';
}
$query .= ' INNER JOIN `#__easyblog_acl_group` AS `e` ON d.`group_id` = e.`content_id`';
$query .= ' LEFT JOIN `#__easyblog_featured` AS `g` ON a.`id`= g.`content_id` AND g.`type`=' . $db->Quote('blogger');
$query .= ' LEFT JOIN `#__easyblog_post` AS `p` ON a.`id` = p.`created_by`';
$query .= ' AND `p`.' . EasyBlogHelper::getHelper('SQL')->nameQuote('published') . '=' . $db->Quote(POST_ID_PUBLISHED);
$query .= ' WHERE e.acl_id = ' . $db->Quote($aclId);
$query .= ' AND e.`type` = ' . $db->Quote('group') . ' AND e.`status` = 1';
$query .= ' GROUP BY a.`id`';
if ($filter == 'showbloggerwithpost') {
$query .= ' HAVING (COUNT(p.`id`) > 0)';
}
$query .= ' )';
$query .= ' UNION ';
$query .= ' (SELECT a1.`id`, b1.`nickname`, b1.`avatar`, b1.`description`,';
$query .= ' a1.`name`, a1.`username`, a1.`registerDate`, a1.`lastvisitDate`,';
$query .= ' COUNT(p1.`id`) as `totalPost`, MAX(p1.`created`) as `latestPostDate`,';
$query .= ' COUNT( DISTINCT(g1.`content_id`) ) as `featured`';
$query .= ' FROM `#__users` AS `a1`';
$query .= ' LEFT JOIN `#__easyblog_users` AS `b1` ON a1.`id` = b1.`id`';
$query .= ' INNER JOIN `#__easyblog_acl_group` AS `c1` ON a1.`id` = c1.`content_id`';
//$query .= ' AND c1.`type` = ' . $db->Quote('assigned') . ' AND c1.`status` = 1';
//$query .= ' INNER JOIN `#__easyblog_acl` as `d1` ON c1.`acl_id` = d1.`id`';
//$query .= ' AND d1.`action` = ' . $db->Quote('add_entry');
$query .= ' LEFT JOIN `#__easyblog_featured` AS `g1` ON a1.`id`= g1.`content_id` AND g1.`type`=' . $db->Quote('blogger');
$query .= ' LEFT JOIN `#__easyblog_post` AS `p1` ON a1.`id` = p1.`created_by`';
$query .= ' AND `p1`.' . EasyBlogHelper::getHelper('SQL')->nameQuote('published') . '=' . $db->Quote(POST_ID_PUBLISHED);
$query .= ' WHERE c1.acl_id = ' . $db->Quote($aclId);
$query .= ' AND c1.`type` = ' . $db->Quote('assigned') . ' AND c1.`status` = 1';
$query .= ' GROUP BY a1.`id`';
if ($filter == 'showbloggerwithpost') {
$query .= ' HAVING (COUNT(p1.`id`) > 0)';
}
$query .= ' )';
$query .= ' ) as x';
$query .= $excludedQuery;
if (!empty($search)) {
$query .= !empty($excludedQuery) ? ' AND ' : ' WHERE ';
switch ($nameDisplayFormat) {
case 'name':
$query .= 'x.`name` LIKE ' . $db->Quote('%' . $search . '%');
break;
case 'username':
$query .= 'x.`username` LIKE ' . $db->Quote('%' . $search . '%');
break;
default:
$query .= 'x.`nickname` LIKE ' . $db->Quote('%' . $search . '%');
break;
}
}
switch ($sort) {
case 'featured':
$query .= ' ORDER BY x.`featured` DESC';
break;
case 'latestpost':
$query .= ' ORDER BY x.`latestPostDate` DESC';
break;
case 'latest':
$query .= ' ORDER BY x.`registerDate` DESC';
break;
case 'active':
$query .= ' ORDER BY x.`lastvisitDate` DESC';
break;
case 'alphabet':
if ($nameDisplayFormat == 'name') {
$query .= ' ORDER BY x.`name` ASC';
} else {
if ($nameDisplayFormat == 'username') {
$query .= ' ORDER BY x.`username` ASC';
} else {
$query .= ' ORDER BY x.`nickname` ASC';
}
}
break;
default:
break;
}
$query .= $limitSQL;
$db->setQuery($query);
$result = $db->loadObjectList();
if ($db->getErrorNum()) {
JError::raiseError(500, $db->stderr());
}
return $result;
}
