protected function getValue() { $app = JFactory::getApplication(); if (isset($this->doc->cat_ids) && isset($this->doc->cat_titles) && !is_null($this->doc->cat_ids) && !is_null($this->doc->cat_titles)) { $categories = array(); $catIdArr = explode(",", $this->doc->cat_ids); $catTitleArr = explode("|||", $this->doc->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, dxref.main"); $query->from("#__judownload_categories AS c"); $query->join("", "#__judownload_documents_xref AS dxref ON (c.id = dxref.cat_id)"); $query->where("dxref.doc_id = " . $this->doc_id); if ($app->isSite()) { $categoryIdArrayCanAccess = JUDownloadFrontHelperCategory::getAccessibleCategoryIds(); if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) { $query->where('c.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')'); } else { $query->where('c.id IN("")'); } } $query->order("dxref.main DESC, dxref.ordering ASC"); $db->setQuery($query); $categories = $db->loadObjectList(); $value = $categories; } return $value; }
public static function isEmptyCat($catId) { $categoryIdArrayCanAccess = JUDownloadFrontHelperCategory::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('#__judownload_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('#__judownload_documents AS d'); $query->where('dx.cat_id = ' . $catId); $query->where('d.approved = 1'); $query->where('d.published = 1'); $query->where('(d.publish_up = ' . $db->quote($nullDate) . ' OR d.publish_up <= ' . $db->quote($nowDate) . ')'); $query->where('(d.publish_down = ' . $db->quote($nullDate) . ' OR d.publish_down >= ' . $db->quote($nowDate) . ')'); if ($user->get('guest')) { $query->where('d.access IN (' . $levelString . ')'); } else { $query->where('(d.access IN (' . $levelString . ') OR (d.created_by = ' . $user->id . '))'); } $query->join('INNER', '#__judownload_documents_xref AS dx ON d.id = dx.doc_id'); $query->where('dx.cat_id IN (' . implode(",", $categoryIdArrayCanAccess) . ')'); $app = JFactory::getApplication(); $tagLanguage = JFactory::getLanguage()->getTag(); if ($app->getLanguageFilter()) { $query->where('d.language IN (' . $db->quote($tagLanguage) . ',' . $db->quote('*') . ')'); } $query->group('d.id'); $db->setQuery($query); $totalDocuments = $db->loadResult(); if (!$totalSubCats && !$totalDocuments) { return true; } else { return false; } }
public function getTotalDocumentsAssignTag($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('#__judownload_documents AS d'); $query->join('', '#__judownload_documents_xref AS dxmain ON d.id = dxmain.doc_id AND dxmain.main = 1'); $query->join('', '#__judownload_categories AS cmain ON cmain.id = dxmain.cat_id'); $categoryIdArrayCanAccess = JUDownloadFrontHelperCategory::getAccessibleCategoryIds(); if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) { $query->where('cmain.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')'); } else { $query->where('cmain.id IN("")'); } $query->where('d.approved = 1'); $query->where('d.published = 1'); $query->where('(d.publish_up = ' . $db->quote($nullDate) . ' OR d.publish_up <= ' . $db->quote($nowDate) . ')'); $query->where('(d.publish_down = ' . $db->quote($nullDate) . ' OR d.publish_down >= ' . $db->quote($nowDate) . ')'); if ($user->get('guest')) { $query->where('d.access IN (' . $levelsStr . ')'); } else { $query->where('(d.access IN (' . $levelsStr . ') OR (d.created_by = ' . $user->id . '))'); } $app = JFactory::getApplication(); $languageTag = JFactory::getLanguage()->getTag(); if ($app->getLanguageFilter()) { $query->where('d.language IN (' . $db->quote($languageTag) . ',' . $db->quote('*') . ',' . $db->quote('') . ')'); } $query->join('', '#__judownload_tags_xref AS tag_xref ON tag_xref.doc_id = d.id'); $query->join('', '#__judownload_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 function getRelatedDocuments($documentId) { $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'); $maxRelatedDocs = (int) $params->get('max_related_documents', 12); $db = $this->getDbo(); $query = $db->getQuery(true); $query->select('d.*, cmain.id AS cat_id'); $query->from('#__judownload_documents AS d'); $query->join('', '#__judownload_documents_xref AS dxmain ON d.id = dxmain.doc_id AND dxmain.main = 1'); $query->join('', '#__judownload_categories AS cmain ON cmain.id = dxmain.cat_id'); $categoryIdArrayCanAccess = JUDownloadFrontHelperCategory::getAccessibleCategoryIds(); if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) { $query->where('cmain.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')'); } else { $query->where('cmain.id IN("")'); } $query->join('', '#__judownload_documents_relations AS drel ON d.id = drel.doc_id_related'); $query->where('drel.doc_id =' . $documentId); $query->where('d.id !=' . $documentId); $query->where('d.approved = 1'); $query->where('d.published = 1'); $query->where('d.publish_up <= ' . $db->quote($nowDate)); $query->where('(d.publish_down = ' . $db->quote($nullDate) . ' OR d.publish_down > ' . $db->quote($nowDate) . ')'); if ($user->get('guest')) { $query->where('d.access IN (' . $levelsStr . ')'); } else { $query->where('(d.access IN (' . $levelsStr . ') OR (d.created_by = ' . $user->id . '))'); } if ($app->getLanguageFilter()) { $query->where('d.language IN (' . $db->quote($lang->getTag()) . ',' . $db->quote('*') . $db->quote('') . ')'); } $order = $params->get('related_documents_ordering', 'drel.ordering'); $dir = $params->get('related_documents_direction', 'ASC'); $query->order($order . ' ' . $dir); if ($maxRelatedDocs > 0) { $db->setQuery($query, 0, $maxRelatedDocs); } else { $db->setQuery($query); } return $db->loadObjectList(); }
protected function getListQuery() { $db = $this->getDbo(); $date = JFactory::getDate(); $now = $date->toSql(); $nullDate = $db->getNullDate(); $query = $db->getQuery(true); $query->select('d.id, d.title, d.alias, d.created, d.access'); $query->from('#__judownload_documents AS d'); $query->join('', '#__judownload_documents_xref AS dxref ON dxref.doc_id = d.id AND dxref.main = 1'); $query->select('c.title AS category_title'); $query->join('', '#__judownload_categories AS c ON c.id = dxref.cat_id'); $categoryIdArrayCanAccess = JUDownloadFrontHelperCategory::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 = d.access'); $access = $this->getState('filter.access'); if ($access) { $query->where('d.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('d.featured = ' . (int) $featured); } $licenseid = $this->getState('filter.licenseid'); if ($licenseid) { $query->SELECT('l.title AS license_title'); $query->JOIN('LEFT', '#__judownload_licenses AS l ON l.id = d.license_id'); $query->where('l.id = ' . $db->quote($licenseid)); } $query->where('d.approved = 1'); $query->where('d.published = 1'); $query->where('d.publish_up <= ' . $db->quote($now)); $query->where('(d.publish_down = ' . $db->quote($nullDate) . ' OR d.publish_down > ' . $db->quote($now) . ')'); $search = $this->getState('filter.search'); if (!empty($search)) { if (stripos($search, 'id:') === 0) { $query->where('d.id = ' . (int) substr($search, 3)); } else { $search = $db->Quote('%' . $db->getEscaped($search, true) . '%'); $query->where('d.title LIKE ' . $search); } } $orderCol = $this->state->get('list.ordering'); $orderDirn = $this->state->get('list.direction'); if ($orderCol) { $query->order($orderCol . ' ' . $orderDirn); } return $query; }
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('#__judownload_categories AS c'); $query->join('INNER', '#__judownload_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 = JUDownloadFrontHelperCategory::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); $showTotalChildDocs = $params->get('show_total_docs_of_relcat', 0); $nestedCategories = null; if ($showTotalChildDocs || $showTotalSubCats) { $nestedCategories = JUDownloadFrontHelperCategory::getCategoriesRecursive($category->id, true, true, true, false, false, true); if ($showTotalChildDocs) { $category->total_documents = JUDownloadFrontHelperCategory::getTotalDocumentsInCategory($category->id, $nestedCategories); } if ($showTotalSubCats) { $category->total_nested_categories = JUDownloadFrontHelperCategory::getTotalSubCategoriesInCategory($category->id, $nestedCategories); } } $registry = new JRegistry; $registry->loadString($category->images); $category->images = $registry->toObject(); $category->link = JRoute::_(JUDownloadHelperRoute::getCategoryRoute($category->id)); if (!$showEmptyCategory) { if (is_null($nestedCategories)) { $nestedCategories = JUDownloadFrontHelperCategory::getCategoriesRecursive($category->id, true, true, true, false, false, true); } if (!isset($category->total_nested_categories)) { $category->total_nested_categories = JUDownloadFrontHelperCategory::getTotalSubCategoriesInCategory($category->id, $nestedCategories); } if (!isset($category->total_documents)) { $category->total_documents = JUDownloadFrontHelperCategory::getTotalDocumentsInCategory($category->id, $nestedCategories); } if (($category->total_nested_categories > 0) || ($category->total_documents > 0)) { $categoriesAfter[] = $category; } } else { $categoriesAfter[] = $category; } } $this->cache[$storeId] = $categoriesAfter; } return $this->cache[$storeId]; }
public static function optimizeListDocumentQuery(&$query, $massSelect = false) { $user = JFactory::getUser(); $query->select('cmain.id AS cat_id'); $query->join('', '#__judownload_documents_xref AS dxmain ON d.id = dxmain.doc_id AND dxmain.main = 1'); $query->join('', '#__judownload_categories AS cmain ON cmain.id = dxmain.cat_id'); $categoryIdArrayCanAccess = JUDownloadFrontHelperCategory::getAccessibleCategoryIds(); if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) { $query->where('cmain.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')'); } else { $query->where('cmain.id IN("")'); } if ($massSelect) { $query->select('(SELECT COUNT(*) FROM #__judownload_files AS f WHERE f.doc_id = d.id AND f.published = 1) AS total_files'); $commentsField = new JUDownloadFieldCore_comments(); if ($commentsField->canView(array("view" => "list"))) { $isModerator = JUDownloadFrontHelperModerator::isModerator(); if (!$isModerator && !$user->authorise('core.admin', 'com_judownload')) { $params = JUDownloadHelper::getParams(); $negative_vote_comment = $params->get('negative_vote_comment'); if (is_numeric($negative_vote_comment) && $negative_vote_comment > 0) { $query->select('(SELECT COUNT(*) FROM #__judownload_comments AS cm WHERE cm.doc_id = d.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 #__judownload_comments AS cm WHERE cm.doc_id = d.id AND cm.approved = 1 AND cm.published = 1 AND cm.level = 1) AS total_comments'); } } } $subscriptionsField = new JUDownloadFieldCore_subscriptions(); if ($subscriptionsField->canView(array("view" => "list"))) { $query->select('(SELECT COUNT(*) FROM #__judownload_subscriptions AS sub WHERE sub.item_id = d.id AND sub.type = "document" AND sub.published = 1) AS total_subscriptions'); } $reportsField = new JUDownloadFieldCore_reports(); if ($reportsField->canView(array("view" => "list"))) { $query->select('(SELECT COUNT(*) FROM #__judownload_reports AS r WHERE r.item_id = d.id AND r.type = "document") AS total_reports'); } $categoriesField = new JUDownloadFieldCore_categories(); if ($categoriesField->canView(array("view" => "list"))) { $query->select('(SELECT GROUP_CONCAT(catids.id ORDER BY dx_catids.main DESC, dx_catids.ordering ASC SEPARATOR ",") FROM (#__judownload_categories AS catids JOIN #__judownload_documents_xref AS dx_catids ON catids.id = dx_catids.cat_id) WHERE d.id = dx_catids.doc_id GROUP BY d.id) AS cat_ids'); $query->select('(SELECT GROUP_CONCAT(cattitles.title ORDER BY dx_cattitles.main DESC, dx_cattitles.ordering ASC SEPARATOR "|||") FROM (#__judownload_categories AS cattitles JOIN #__judownload_documents_xref AS dx_cattitles ON cattitles.id = dx_cattitles.cat_id) WHERE d.id = dx_cattitles.doc_id GROUP BY d.id) AS cat_titles'); } $tagsField = new JUDownloadFieldCore_tags(); if ($tagsField->canView(array("view" => "list"))) { $query->select('IFNULL ((SELECT GROUP_CONCAT(tagids.id ORDER BY tx_tagids.ordering ASC SEPARATOR ",") FROM (#__judownload_tags AS tagids JOIN #__judownload_tags_xref AS tx_tagids ON tagids.id = tx_tagids.tag_id) WHERE d.id = tx_tagids.doc_id GROUP BY d.id), "") AS tag_ids'); $query->select('IFNULL ((SELECT GROUP_CONCAT(tagtitles.title ORDER BY tx_tagtitles.ordering ASC SEPARATOR "|||") FROM (#__judownload_tags AS tagtitles JOIN #__judownload_tags_xref AS tx_tagtitles ON tagtitles.id = tx_tagtitles.tag_id) WHERE d.id = tx_tagtitles.doc_id GROUP BY d.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('#__judownload_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 . ') OR field.who_can_download_can_access = 1)'); $view = $app->input->get('view', ''); if ($view == 'category' || $view == 'tree') { $cat_id = $app->input->getInt('id', 0); $category = JUDownloadHelper::getCategoryById($cat_id); if (is_object($category)) { $fieldQuery->where('field.group_id = ' . $category->fieldgroup_id); } } else { $fieldQuery->join('', '#__judownload_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('', '#__judownload_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', '#__judownload_fields_values AS fields_values_' . $field->id . ' ON fields_values_' . $field->id . '.doc_id = d.id AND fields_values_' . $field->id . '.field_id = ' . $field->id); } } }
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($catId); $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 = JUDownloadFrontHelperCategory::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_judownload.category.' . (int) $item->id; $candoCreate = $user->authorise('judl.category.create', $assetName); if (!$candoCreate) { $disable = true; } } elseif ($checkCreatedPermission == "document") { $assetName = 'com_judownload.category.' . (int) $item->id; $candoCreate = $user->authorise('judl.document.create', $assetName); if (!$candoCreate) { $disable = true; } } } if ($item->published != 1 && !$checkPublished) { $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 getTotalDocumentsInCategory($categoryId, $nestedCategoryIds = null) { if (is_null($nestedCategoryIds)) { $nestedCategories = JUDownloadFrontHelperCategory::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('#__judownload_documents AS d'); $query->join('', '#__judownload_documents_xref AS dx ON d.id = dx.doc_id'); $query->join('', '#__judownload_categories AS c ON c.id = dx.cat_id'); if (is_array($nestedCategoryIds) && count($nestedCategoryIds) > 0) { $query->where('(c.id IN (' . implode(",", $nestedCategoryIds) . '))'); } else { $query->where('(c.id IN (""))'); } $query->join('', '#__judownload_documents_xref AS dxmain ON d.id = dxmain.doc_id AND dxmain.main = 1'); $query->join('', '#__judownload_categories AS cmain ON cmain.id = dxmain.cat_id'); $categoryIdArrayCanAccess = JUDownloadFrontHelperCategory::getAccessibleCategoryIds(); if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) { $query->where('cmain.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')'); } else { $query->where('cmain.id IN("")'); } $query->where('d.approved = 1'); $query->where('d.published = 1'); $query->where('(d.publish_up = ' . $db->quote($nullDate) . ' OR d.publish_up <= ' . $db->quote($nowDate) . ')'); $query->where('(d.publish_down = ' . $db->quote($nullDate) . ' OR d.publish_down >= ' . $db->quote($nowDate) . ')'); if ($user->get('guest')) { $query->where('d.access IN (' . $levelsStr . ')'); } else { $query->where('(d.access IN (' . $levelsStr . ') OR d.created_by = ' . $user->id . ')'); } $app = JFactory::getApplication(); $tagLanguage = JFactory::getLanguage()->getTag(); if ($app->getLanguageFilter()) { $query->where('d.language IN (' . $db->quote($tagLanguage) . ',' . $db->quote('*') . ',' . $db->quote('') . ')'); } $db->setQuery($query); self::$cache[$storeId] = $db->loadResult(); } return self::$cache[$storeId]; }
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('d.*'); $query->from('#__judownload_documents AS d'); $query->join('', '#__judownload_documents_xref AS dxref ON dxref.doc_id = d.id AND dxref.main = 1'); $query->select('c.title AS category_title'); $query->join('', '#__judownload_categories AS c ON dxref.cat_id = c.id'); $categoryIdArrayCanAccess = JUDownloadFrontHelperCategory::getAccessibleCategoryIds(); if (is_array($categoryIdArrayCanAccess) && count($categoryIdArrayCanAccess) > 0) { $query->where('c.id IN(' . implode(",", $categoryIdArrayCanAccess) . ')'); } else { $query->where('c.id IN("")'); } $query->select('l.title AS license_title'); $query->join('LEFT', '#__judownload_licenses AS l ON l.id = d.license_id'); $query->select('ua.name AS created_by'); $query->join('LEFT', '#__users AS ua ON ua.id = d.created_by'); $query->select('vl.title AS access_level'); $query->join('LEFT', '#__viewlevels AS vl ON vl.id = d.access'); $query->where('d.approved = 1'); $query->where('d.published = 1'); $query->where('(d.publish_up = ' . $db->quote($nullDate) . ' OR d.publish_up <= ' . $db->quote($nowDate) . ')'); $query->where('(d.publish_down = ' . $db->quote($nullDate) . ' OR d.publish_down >= ' . $db->quote($nowDate) . ')'); if ($user->get('guest')) { $query->where('d.access IN (' . $levelsStr . ')'); } else { $query->where('(d.access IN (' . $levelsStr . ') OR (d.created_by = ' . $user->id . '))'); } $catid = $this->getState('filter.catid'); if ($catid) { $query->where('c.id = ' . $db->quote($catid)); } $licenseid = $this->getState('filter.licenseid'); if ($licenseid) { $query->where('l.id = ' . $db->quote($licenseid)); } $access = $this->getState('filter.access'); if ($access) { $query->where('d.access = ' . (int) $access); } $published = $this->getState('filter.published', ''); if ($published !== '') { $query->where('d.published = ' . (int) $published); } $featured = $this->getState('filter.featured', ''); if ($featured !== '') { $query->where('d.featured = ' . (int) $featured); } if (!empty($search)) { if (stripos($search, 'id:') === 0) { $query->where('d.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('d.created_by = 0 OR d.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('d.title LIKE ' . $search); } } $orderingAllow = array('d.id', 'd.title', 'd.author', 'c.title', 'l.title', 'd.featured', 'd.published', 'd.created'); if (in_array($listOrder, $orderingAllow)) { if ($listOrder == 'c.title' || $listOrder == 'l.title') { $query->order($listOrder . " " . $listDirn . ', d.title'); } else { $query->order($listOrder . ' ' . $listDirn); } } return $query; }