protected function getListQuery()
{
$db = $this->getDbo();
$date = JFactory::getDate();
$now = $date->toSql();
$nullDate = $db->getNullDate();
$query = $db->getQuery(true);
$query->select('listing.id, listing.title, listing.alias, listing.created, listing.access');
$query->from('#__judirectory_listings AS listing');
$query->join('', '#__judirectory_listings_xref AS listingxref ON dxref.doc_id = listing.id AND dxref.main = 1');
$query->select('c.title AS category_title');
$query->join('', '#__judirectory_categories AS c ON c.id = dxref.cat_id');
$categoryIdArrayCanAccess = JUDirectoryFrontHelperPermission::getAccessibleCategoryIds();
if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) {
$query->where('c.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')');
} else {
$query->where('c.id IN("")');
}
$query->select('vl.title AS access_title');
$query->join('LEFT', '#__viewlevels AS vl ON vl.id = listing.access');
$access = $this->getState('filter.access');
if ($access) {
$query->where('listing.access = ' . (int) $access);
}
$categoryId = $this->getState('filter.catid');
if (is_numeric($categoryId)) {
$query->where('c.id = ' . (int) $categoryId);
}
$query->where('c.published = 1');
$query->where('c.publish_up <= ' . $db->quote($now));
$query->where('(c.publish_down = ' . $db->quote($nullDate) . ' OR c.publish_down > ' . $db->quote($now) . ')');
$featured = $this->getState('filter.featured', '');
if ($featured !== '') {
$query->where('listing.featured = ' . (int) $featured);
}
$query->where('listing.approved = 1');
$query->where('listing.published = 1');
$query->where('listing.publish_up <= ' . $db->quote($now));
$query->where('(listing.publish_down = ' . $db->quote($nullDate) . ' OR listing.publish_down > ' . $db->quote($now) . ')');
$search = $this->getState('filter.search');
if (!empty($search)) {
if (stripos($search, 'id:') === 0) {
$query->where('listing.id = ' . (int) substr($search, 3));
} else {
$search = $db->Quote('%' . $db->getEscaped($search, true) . '%');
$query->where('listing.title LIKE ' . $search);
}
}
$orderCol = $this->state->get('list.ordering');
$orderDirn = $this->state->get('list.direction');
if ($orderCol) {
$query->order($orderCol . ' ' . $orderDirn);
}
return $query;
}
protected function getValue()
{
$app = JFactory::getApplication();
// At the frontend, if we already select cats in getListQuery() => use it, first cat is main cat
if (isset($this->listing->cat_ids) && isset($this->listing->cat_titles) && !is_null($this->listing->cat_ids) && !is_null($this->listing->cat_titles)) {
$categories = array();
$catIdArr = explode(",", $this->listing->cat_ids);
$catTitleArr = explode("|||", $this->listing->cat_titles);
foreach ($catIdArr as $key => $catId) {
$category = new stdClass();
$category->id = $catIdArr[$key];
$category->title = $catTitleArr[$key];
if ($key == 0) {
$category->main = 1;
} else {
$category->main = 0;
}
$categories[] = $category;
}
$value = $categories;
} else {
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select("c.id, c.title, c.parent_id, listingxref.main");
$query->from("#__judirectory_categories AS c");
$query->join("", "#__judirectory_listings_xref AS listingxref ON (c.id = listingxref.cat_id)");
$query->where("listingxref.listing_id = " . $this->listing_id);
if ($app->isSite()) {
// Where in categoryIds can access at frontend
$categoryIdArrayCanAccess = JUDirectoryFrontHelperPermission::getAccessibleCategoryIds();
if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) {
$query->where('c.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')');
} else {
$query->where('c.id IN("")');
}
}
$query->order("listingxref.main DESC, listingxref.ordering ASC");
$db->setQuery($query);
$categories = $db->loadObjectList();
$value = $categories;
}
return $value;
}
public static function getCategoryOptions($catId = 1, $fetchSelf = true, $checkCreatedPermission = false, $checkPublished = false, $ignoredCatId = array(), $startLevel = 0, $separation = '|—')
{
$categoryTree = self::getCategoryTree($catId, $fetchSelf, $checkPublished);
if ($categoryTree) {
$app = JFactory::getApplication();
$user = JFactory::getUser();
$options = array();
$ignoredCatIdArr = array();
if ($ignoredCatId) {
foreach ($ignoredCatId as $cat_id) {
if (!in_array($cat_id, $ignoredCatIdArr)) {
$_categoryTree = self::getCategoryTree($cat_id, true);
foreach ($_categoryTree as $category) {
if (!in_array($category->id, $ignoredCatIdArr)) {
$ignoredCatIdArr[] = $category->id;
}
}
}
}
}
foreach ($categoryTree as $key => $item) {
if ($app->isSite()) {
$accessibleCategoryIds = JUDirectoryFrontHelperPermission::getAccessibleCategoryIds();
if (!is_array($accessibleCategoryIds)) {
$accessibleCategoryIds = array();
}
if (!in_array($item->id, $accessibleCategoryIds)) {
continue;
}
}
if ($ignoredCatIdArr && in_array($item->id, $ignoredCatIdArr)) {
continue;
}
$disable = false;
if ($checkCreatedPermission) {
if ($checkCreatedPermission == "category") {
$assetName = 'com_judirectory.category.' . (int) $item->id;
$candoCreate = $user->authorise('judir.category.create', $assetName);
if (!$candoCreate) {
$disable = true;
}
} elseif ($checkCreatedPermission == "listing") {
$assetName = 'com_judirectory.category.' . (int) $item->id;
$candoCreate = $user->authorise('judir.listing.create', $assetName);
if (!$candoCreate) {
$disable = true;
}
if ($item->id == 1 && !$disable) {
$params = JUDirectoryHelper::getParams();
if (!$params->get('allow_add_listing_to_root', 0)) {
$disable = true;
}
}
}
}
if ($item->published != 1) {
$item->title = "[" . $item->title . "]";
}
if ($key == 0) {
$firstLevel = $item->level - $startLevel;
}
$level = $item->level - $firstLevel;
$options[] = JHtml::_('select.option', $item->id, str_repeat($separation, $level) . $item->title, 'value', 'text', $disable);
}
}
return $options;
}
public static function optimizeListListingQuery(&$query, $massSelect = false)
{
$user = JFactory::getUser();
$query->select('cmain.id AS cat_id');
$query->join('', '#__judirectory_listings_xref AS listingxmain ON listing.id = listingxmain.listing_id AND listingxmain.main = 1');
$query->join('', '#__judirectory_categories AS cmain ON cmain.id = listingxmain.cat_id');
$categoryIdArrayCanAccess = JUDirectoryFrontHelperPermission::getAccessibleCategoryIds();
if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) {
$query->where('cmain.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')');
} else {
$query->where('cmain.id IN("")');
}
if ($massSelect) {
$commentsField = new JUDirectoryFieldCore_comments();
if ($commentsField->canView(array("view" => "list"))) {
$isModerator = JUDirectoryFrontHelperModerator::isModerator();
if (!$isModerator && !$user->authorise('core.admin', 'com_judirectory')) {
$params = JUDirectoryHelper::getParams();
$negative_vote_comment = $params->get('negative_vote_comment');
if (is_numeric($negative_vote_comment) && $negative_vote_comment > 0) {
$query->select('(SELECT COUNT(*) FROM #__judirectory_comments AS cm WHERE cm.listing_id = listing.id AND cm.approved = 1 AND cm.published = 1 AND cm.level = 1
AND (cm.total_votes - cm.helpful_votes) < ' . $negative_vote_comment . ' ) AS total_comments');
} else {
$query->select('(SELECT COUNT(*) FROM #__judirectory_comments AS cm WHERE cm.listing_id = listing.id AND cm.approved = 1 AND cm.published = 1 AND cm.level = 1) AS total_comments');
}
}
}
$subscriptionsField = new JUDirectoryFieldCore_subscriptions();
if ($subscriptionsField->canView(array("view" => "list"))) {
$query->select('(SELECT COUNT(*) FROM #__judirectory_subscriptions AS sub WHERE sub.item_id = listing.id AND sub.type = "listing" AND sub.published = 1) AS total_subscriptions');
}
$reportsField = new JUDirectoryFieldCore_reports();
if ($reportsField->canView(array("view" => "list"))) {
$query->select('(SELECT COUNT(*) FROM #__judirectory_reports AS r WHERE r.item_id = listing.id AND r.type = "listing") AS total_reports');
}
$categoriesField = new JUDirectoryFieldCore_categories();
if ($categoriesField->canView(array("view" => "list"))) {
$query->select('(SELECT GROUP_CONCAT(catids.id ORDER BY listingx_catids.main DESC, listingx_catids.ordering ASC SEPARATOR ",") FROM (#__judirectory_categories AS catids JOIN #__judirectory_listings_xref AS listingx_catids ON catids.id = listingx_catids.cat_id) WHERE listing.id = listingx_catids.listing_id GROUP BY listing.id) AS cat_ids');
$query->select('(SELECT GROUP_CONCAT(cattitles.title ORDER BY listingx_cattitles.main DESC, listingx_cattitles.ordering ASC SEPARATOR "|||") FROM (#__judirectory_categories AS cattitles JOIN #__judirectory_listings_xref AS listingx_cattitles ON cattitles.id = listingx_cattitles.cat_id) WHERE listing.id = listingx_cattitles.listing_id GROUP BY listing.id) AS cat_titles');
}
$tagsField = new JUDirectoryFieldCore_tags();
if ($tagsField->canView(array("view" => "list"))) {
$query->select('IFNULL ((SELECT GROUP_CONCAT(tagids.id ORDER BY tx_tagids.ordering ASC SEPARATOR ",") FROM (#__judirectory_tags AS tagids JOIN #__judirectory_tags_xref AS tx_tagids ON tagids.id = tx_tagids.tag_id) WHERE listing.id = tx_tagids.listing_id GROUP BY listing.id), "") AS tag_ids');
$query->select('IFNULL ((SELECT GROUP_CONCAT(tagtitles.title ORDER BY tx_tagtitles.ordering ASC SEPARATOR "|||") FROM (#__judirectory_tags AS tagtitles JOIN #__judirectory_tags_xref AS tx_tagtitles ON tagtitles.id = tx_tagtitles.tag_id) WHERE listing.id = tx_tagtitles.listing_id GROUP BY listing.id), "") AS tag_titles');
}
$app = JFactory::getApplication();
$accessLevel = implode(',', $user->getAuthorisedViewLevels());
$db = JFactory::getDbo();
$date = JFactory::getDate();
$nullDate = $db->quote($db->getNullDate());
$nowDate = $db->quote($date->toSql());
$fieldQuery = $db->getQuery(true);
$fieldQuery->select('field.id');
$fieldQuery->from('#__judirectory_fields AS field');
$fieldQuery->where('field.field_name = ""');
$fieldQuery->where('field.list_view = 1');
$fieldQuery->where('field.published = 1');
$fieldQuery->where('field.publish_up <= ' . $nowDate);
$fieldQuery->where('(field.publish_down = ' . $nullDate . ' OR field.publish_down > ' . $nowDate . ')');
$fieldQuery->where('field.access IN (' . $accessLevel . ')');
$view = $app->input->get('view', '');
if ($view == 'category' || $view == 'tree') {
$cat_id = $app->input->getInt('id', 0);
$category = JUDirectoryHelper::getCategoryById($cat_id);
if (is_object($category)) {
$fieldQuery->where('field.group_id = ' . $category->fieldgroup_id);
}
} else {
$fieldQuery->join('', '#__judirectory_categories AS c ON (field.group_id = c.fieldgroup_id OR field.group_id = 1)');
if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) {
$fieldQuery->where('c.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')');
} else {
$fieldQuery->where('c.id IN("")');
}
}
$fieldQuery->join('', '#__judirectory_fields_groups AS field_group ON field.group_id = field_group.id');
$fieldQuery->where('field_group.published = 1');
$fieldQuery->where('field_group.access IN (' . $accessLevel . ')');
$fieldQuery->group('field.id');
$db->setQuery($fieldQuery);
$fields = $db->loadObjectList();
foreach ($fields as $field) {
$query->select('IFNULL (fields_values_' . $field->id . '.value, "") AS field_values_' . $field->id);
$query->join('LEFT', '#__judirectory_fields_values AS fields_values_' . $field->id . ' ON fields_values_' . $field->id . '.listing_id = listing.id AND fields_values_' . $field->id . '.field_id = ' . $field->id);
}
}
}
protected function getListQuery()
{
$listOrder = $this->state->get('list.ordering');
$listDirn = $this->state->get('list.direction');
$search = $this->state->get('filter.search');
$user = JFactory::getUser();
$levels = $user->getAuthorisedViewLevels();
$levelsStr = implode(',', $levels);
$db = $this->getDBO();
$nullDate = $db->getNullDate();
$nowDate = JFactory::getDate()->toSql();
$query = $db->getQuery(true);
$query->select('listing.*');
$query->from('#__judirectory_listings AS listing');
$query->join('', '#__judirectory_listings_xref AS listingxref ON listingxref.listing_id = listing.id AND listingxref.main = 1');
$query->select('c.title AS category_title');
$query->join('', '#__judirectory_categories AS c ON listingxref.cat_id = c.id');
$categoryIdArrayCanAccess = JUDirectoryFrontHelperPermission::getAccessibleCategoryIds();
if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) {
$query->where('c.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')');
} else {
$query->where('c.id IN("")');
}
$query->select('ua.name AS created_by');
$query->join('LEFT', '#__users AS ua ON ua.id = listing.created_by');
$query->select('vl.title AS access_level');
$query->join('LEFT', '#__viewlevels AS vl ON vl.id = listing.access');
$query->where('listing.approved = 1');
$query->where('listing.published = 1');
$query->where('(listing.publish_up = ' . $db->quote($nullDate) . ' OR listing.publish_up <= ' . $db->quote($nowDate) . ')');
$query->where('(listing.publish_down = ' . $db->quote($nullDate) . ' OR listing.publish_down >= ' . $db->quote($nowDate) . ')');
if ($user->get('guest')) {
$query->where('listing.access IN (' . $levelsStr . ')');
} else {
$query->where('(listing.access IN (' . $levelsStr . ') OR (listing.created_by = ' . $user->id . '))');
}
$catid = $this->getState('filter.catid');
if ($catid) {
$query->where('c.id = ' . $db->quote($catid));
}
$access = $this->getState('filter.access');
if ($access) {
$query->where('listing.access = ' . (int) $access);
}
$published = $this->getState('filter.published', '');
if ($published !== '') {
$query->where('listing.published = ' . (int) $published);
}
$featured = $this->getState('filter.featured', '');
if ($featured !== '') {
$query->where('listing.featured = ' . (int) $featured);
}
if (!empty($search)) {
if (stripos($search, 'id:') === 0) {
$query->where('listing.id = ' . (int) substr($search, 3));
} elseif (stripos($search, 'author:') === 0) {
$search = substr($search, 7);
$search = $db->quote('%' . $db->escape($search, true) . '%');
$query->where('c.title LIKE ' . $search);
} elseif (stripos($search, 'created_by:') === 0) {
$search = substr($search, 11);
if (is_numeric($search)) {
$query->where('listing.created_by = 0 OR listing.created_by IS NULL');
} else {
$search = $db->Quote('%' . $db->escape($search, true) . '%');
$query->where('(ua.name LIKE ' . $search . ' OR ua.username LIKE ' . $search . ')');
}
} else {
$search = $db->Quote('%' . $db->escape($search, true) . '%');
$query->where('listing.title LIKE ' . $search);
}
}
$orderingAllow = array('listing.id', 'listing.title', 'listing.author', 'c.title', 'l.title', 'listing.featured', 'listing.published', 'listing.created');
if (in_array($listOrder, $orderingAllow)) {
if ($listOrder == 'c.title' || $listOrder == 'l.title') {
$query->order($listOrder . " " . $listDirn . ', listing.title');
} else {
$query->order($listOrder . ' ' . $listDirn);
}
}
return $query;
}
public function getTotalListingsAssignTag($tagId)
{
$user = JFactory::getUser();
$levels = $user->getAuthorisedViewLevels();
$levelsStr = implode(',', $levels);
$db = $this->getDbo();
$nullDate = $db->getNullDate();
$nowDate = JFactory::getDate()->toSql();
$query = $db->getQuery(true);
$query->select('COUNT(*)');
$query->from('#__judirectory_listings AS listing');
$query->join('', '#__judirectory_listings_xref AS listingxmain ON listing.id = listingxmain.listing_id AND listingxmain.main = 1');
$query->join('', '#__judirectory_categories AS cmain ON cmain.id = listingxmain.cat_id');
$categoryIdArrayCanAccess = JUDirectoryFrontHelperPermission::getAccessibleCategoryIds();
if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) {
$query->where('cmain.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')');
} else {
$query->where('cmain.id IN("")');
}
$query->where('listing.approved = 1');
$query->where('listing.published = 1');
$query->where('(listing.publish_up = ' . $db->quote($nullDate) . ' OR listing.publish_up <= ' . $db->quote($nowDate) . ')');
$query->where('(listing.publish_down = ' . $db->quote($nullDate) . ' OR listing.publish_down >= ' . $db->quote($nowDate) . ')');
if ($user->get('guest')) {
$query->where('listing.access IN (' . $levelsStr . ')');
} else {
$query->where('(listing.access IN (' . $levelsStr . ') OR (listing.created_by = ' . $user->id . '))');
}
$app = JFactory::getApplication();
$languageTag = JFactory::getLanguage()->getTag();
if ($app->getLanguageFilter()) {
$query->where('listing.language IN (' . $db->quote($languageTag) . ',' . $db->quote('*') . ',' . $db->quote('') . ')');
}
$query->join('', '#__judirectory_tags_xref AS tag_xref ON tag_xref.listing_id = listing.id');
$query->join('', '#__judirectory_tags AS tag ON tag.id = tag_xref.tag_id');
$query->where('tag.published = 1');
$query->where('(tag.publish_up = ' . $db->quote($nullDate) . ' OR tag.publish_up <= ' . $db->quote($nowDate) . ')');
$query->where('(tag.publish_down = ' . $db->quote($nullDate) . ' OR tag.publish_down >= ' . $db->quote($nowDate) . ')');
$query->where('tag.id =' . $tagId);
$db->setQuery($query);
return (int) $db->loadResult();
}
public static function isEmptyCat($catId)
{
$categoryIdArrayCanAccess = JUDirectoryFrontHelperPermission::getAccessibleCategoryIds();
if (empty($categoryIdArrayCanAccess)) {
return true;
}
$user = JFactory::getUser();
$levelsArray = $user->getAuthorisedViewLevels();
$levelString = implode(',', $levelsArray);
$db = JFactory::getDBO();
$nullDate = $db->getNullDate();
$nowDate = JFactory::getDate()->toSql();
$query = $db->getQuery(true);
$query->select('COUNT(*)');
$query->from('#__judirectory_categories AS c');
$query->where('c.parent_id = ' . $catId);
$query->where('c.published = 1');
$query->where('(c.publish_up = ' . $db->quote($nullDate) . ' OR c.publish_up <= ' . $db->quote($nowDate) . ')');
$query->where('(c.publish_down = ' . $db->quote($nullDate) . ' OR c.publish_down >= ' . $db->quote($nowDate) . ')');
$query->where('c.access IN (' . $levelString . ')');
$query->where('c.id IN (' . implode(",", $categoryIdArrayCanAccess) . ')');
$app = JFactory::getApplication();
$tagLanguage = JFactory::getLanguage()->getTag();
if ($app->getLanguageFilter()) {
$query->where('c.language IN (' . $db->quote($tagLanguage) . ',' . $db->quote('*') . ')');
}
$db->setQuery($query);
$totalSubCats = $db->loadResult();
$query = $db->getQuery(true);
$query->select('COUNT(*)');
$query->from('#__judirectory_listings AS listing');
$query->where('listingxref.cat_id = ' . $catId);
$query->where('listing.approved = 1');
$query->where('listing.published = 1');
$query->where('(listing.publish_up = ' . $db->quote($nullDate) . ' OR listing.publish_up <= ' . $db->quote($nowDate) . ')');
$query->where('(listing.publish_down = ' . $db->quote($nullDate) . ' OR listing.publish_down >= ' . $db->quote($nowDate) . ')');
if ($user->get('guest')) {
$query->where('listing.access IN (' . $levelString . ')');
} else {
$query->where('(listing.access IN (' . $levelString . ') OR (listing.created_by = ' . $user->id . '))');
}
$query->join('INNER', '#__judirectory_listings_xref AS listingxref ON listing.id = listingxref.listing_id');
$query->where('listingxref.cat_id IN (' . implode(",", $categoryIdArrayCanAccess) . ')');
$app = JFactory::getApplication();
$tagLanguage = JFactory::getLanguage()->getTag();
if ($app->getLanguageFilter()) {
$query->where('listing.language IN (' . $db->quote($tagLanguage) . ',' . $db->quote('*') . ')');
}
$query->group('listing.id');
$db->setQuery($query);
$totalListings = $db->loadResult();
if (!$totalSubCats && !$totalListings) {
return true;
} else {
return false;
}
}
public static function getTotalListingsInCategory($categoryId, $nestedCategoryIds = null)
{
if (is_null($nestedCategoryIds)) {
$nestedCategories = JUDirectoryFrontHelperCategory::getCategoriesRecursive($categoryId, true, true, true);
$nestedCategoryIds = array();
foreach ($nestedCategories as $nestedCategory) {
$nestedCategoryIds[] = $nestedCategory->id;
}
}
if (!is_array($nestedCategoryIds) || !count($nestedCategoryIds)) {
return 0;
}
$storeId = md5(__METHOD__ . "::" . serialize($nestedCategoryIds));
if (!isset(self::$cache[$storeId])) {
$user = JFactory::getUser();
$levels = $user->getAuthorisedViewLevels();
$levelsStr = implode(',', $levels);
$db = JFactory::getDbo();
$nullDate = $db->getNullDate();
$nowDate = JFactory::getDate()->toSql();
$query = $db->getQuery(true);
$query->select('COUNT(*)');
$query->from('#__judirectory_listings AS listing');
$query->join('', '#__judirectory_listings_xref AS listingxref ON listing.id = listingxref.listing_id');
$query->join('', '#__judirectory_categories AS c ON c.id = listingxref.cat_id');
if (is_array($nestedCategoryIds) && count($nestedCategoryIds) > 0) {
$query->where('(c.id IN (' . implode(",", $nestedCategoryIds) . '))');
} else {
$query->where('(c.id IN (""))');
}
$query->join('', '#__judirectory_listings_xref AS listingxmain ON listing.id = listingxmain.listing_id AND listingxmain.main = 1');
$query->join('', '#__judirectory_categories AS cmain ON cmain.id = listingxmain.cat_id');
$categoryIdArrayCanAccess = JUDirectoryFrontHelperPermission::getAccessibleCategoryIds();
if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) {
$query->where('cmain.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')');
} else {
$query->where('cmain.id IN("")');
}
$query->where('listing.approved = 1');
$query->where('listing.published = 1');
$query->where('(listing.publish_up = ' . $db->quote($nullDate) . ' OR listing.publish_up <= ' . $db->quote($nowDate) . ')');
$query->where('(listing.publish_down = ' . $db->quote($nullDate) . ' OR listing.publish_down >= ' . $db->quote($nowDate) . ')');
if ($user->get('guest')) {
$query->where('listing.access IN (' . $levelsStr . ')');
} else {
$query->where('(listing.access IN (' . $levelsStr . ') OR listing.created_by = ' . $user->id . ')');
}
$app = JFactory::getApplication();
$tagLanguage = JFactory::getLanguage()->getTag();
if ($app->getLanguageFilter()) {
$query->where('listing.language IN (' . $db->quote($tagLanguage) . ',' . $db->quote('*') . ',' . $db->quote('') . ')');
}
$db->setQuery($query);
self::$cache[$storeId] = $db->loadResult();
}
return self::$cache[$storeId];
}
public function getRelatedCategories($categoryId, $ordering = 'crel.ordering', $direction = 'ASC')
{
$storeId = md5(__METHOD__ . "::" . $categoryId . "::" . $ordering . "::" . $direction);
if (!isset($this->cache[$storeId])) {
$params = $this->getState('params');
$showEmptyCategory = $params->get('show_empty_related_category', 1);
$user = JFactory::getUser();
$levels = $user->getAuthorisedViewLevels();
$levelsStr = implode(',', $levels);
$db = JFactory::getDbo();
$nullDate = $db->getNullDate();
$nowDate = JFactory::getDate()->toSql();
$query = $db->getQuery(true);
$query->select('c.*');
$query->from('#__judirectory_categories AS c');
$query->join('INNER', '#__judirectory_categories_relations AS crel ON c.id=crel.cat_id_related');
$query->where('crel.cat_id =' . $categoryId);
$query->where('c.published = 1');
$query->where('(c.publish_up = ' . $db->quote($nullDate) . ' OR c.publish_up <= ' . $db->quote($nowDate) . ')');
$query->where('(c.publish_down = ' . $db->quote($nullDate) . ' OR c.publish_down >= ' . $db->quote($nowDate) . ')');
$query->where('c.access IN (' . $levelsStr . ')');
$categoryIdArrayCanAccess = JUDirectoryFrontHelperPermission::getAccessibleCategoryIds();
if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) {
$query->where('c.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')');
} else {
$query->where('c.id IN("")');
}
$app = JFactory::getApplication();
$tag = JFactory::getLanguage()->getTag();
if ($app->getLanguageFilter()) {
$query->where('c.language IN (' . $db->quote($tag) . ',' . $db->quote('*') . ',' . $db->quote('') . ')');
}
$query->order($ordering . ' ' . $direction);
$query->group('c.id');
$db->setQuery($query);
$categoriesBefore = $db->loadObjectList();
$categoriesAfter = array();
foreach ($categoriesBefore as $category) {
$showTotalSubCats = $params->get('show_total_subcats_of_relcat', 0);
$showTotalChildListings = $params->get('show_total_listings_of_relcat', 0);
$nestedCategories = null;
if ($showTotalChildListings || $showTotalSubCats) {
$nestedCategories = JUDirectoryFrontHelperCategory::getCategoriesRecursive($category->id, true, true, true, false, false, true);
if ($showTotalChildListings) {
$category->total_listings = JUDirectoryFrontHelperCategory::getTotalListingsInCategory($category->id, $nestedCategories);
}
if ($showTotalSubCats) {
$category->total_nested_categories = JUDirectoryFrontHelperCategory::getTotalSubCategoriesInCategory($category->id, $nestedCategories);
}
}
$registry = new JRegistry();
$registry->loadString($category->images);
$category->images = $registry->toObject();
$category->link = JRoute::_(JUDirectoryHelperRoute::getCategoryRoute($category->id));
if (!$showEmptyCategory) {
if (is_null($nestedCategories)) {
$nestedCategories = JUDirectoryFrontHelperCategory::getCategoriesRecursive($category->id, true, true, true, false, false, true);
}
if (!isset($category->total_nested_categories)) {
$category->total_nested_categories = JUDirectoryFrontHelperCategory::getTotalSubCategoriesInCategory($category->id, $nestedCategories);
}
if (!isset($category->total_listings)) {
$category->total_listings = JUDirectoryFrontHelperCategory::getTotalListingsInCategory($category->id, $nestedCategories);
}
if ($category->total_nested_categories > 0 || $category->total_listings > 0) {
$categoriesAfter[] = $category;
}
} else {
$categoriesAfter[] = $category;
}
}
$this->cache[$storeId] = $categoriesAfter;
}
return $this->cache[$storeId];
}
public function getRelatedListings($listingId)
{
$user = JFactory::getUser();
$levels = $user->getAuthorisedViewLevels();
$levelsStr = implode(',', $levels);
$db = JFactory::getDbo();
$nullDate = $db->getNullDate();
$nowDate = JFactory::getDate()->toSql();
$app = JFactory::getApplication();
$lang = JFactory::getLanguage();
$params = $this->getState('params');
$maxRelatedListings = (int) $params->get('max_related_listings', 12);
$db = $this->getDbo();
$query = $db->getQuery(true);
$query->select('listing.*, cmain.id AS cat_id');
$query->from('#__judirectory_listings AS listing');
$query->join('', '#__judirectory_listings_xref AS listingxmain ON listing.id = listingxmain.listing_id AND listingxmain.main = 1');
$query->join('', '#__judirectory_categories AS cmain ON cmain.id = listingxmain.cat_id');
$categoryIdArrayCanAccess = JUDirectoryFrontHelperPermission::getAccessibleCategoryIds();
if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) {
$query->where('cmain.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')');
} else {
$query->where('cmain.id IN("")');
}
$query->join('', '#__judirectory_listings_relations AS listingrel ON listing.id = listingrel.listing_id_related');
$query->where('listingrel.listing_id =' . $listingId);
$query->where('listing.id !=' . $listingId);
$query->where('listing.approved = 1');
$query->where('listing.published = 1');
$query->where('listing.publish_up <= ' . $db->quote($nowDate));
$query->where('(listing.publish_down = ' . $db->quote($nullDate) . ' OR listing.publish_down > ' . $db->quote($nowDate) . ')');
if ($user->get('guest')) {
$query->where('listing.access IN (' . $levelsStr . ')');
} else {
$query->where('(listing.access IN (' . $levelsStr . ') OR (listing.created_by = ' . $user->id . '))');
}
if ($app->getLanguageFilter()) {
$query->where('listing.language IN (' . $db->quote($lang->getTag()) . ',' . $db->quote('*') . $db->quote('') . ')');
}
$order = $params->get('related_listings_ordering', 'listingrel.ordering');
$dir = $params->get('related_listings_direction', 'ASC');
$query->order($order . ' ' . $dir);
if ($maxRelatedListings > 0) {
$db->setQuery($query, 0, $maxRelatedListings);
} else {
$db->setQuery($query);
}
return $db->loadObjectList();
}
