list($searchsql, $params) = local_search_get_keyword_where_clause($keywords, $fields, SQL_PARAMS_NAMED);
if (empty($CFG->audiencevisibility)) {
$search_info->sql = "
FROM
{prog} p
WHERE
{$searchsql}
AND visible = 1
";
} else {
$visibilitysql = '';
$visibilityparams = array();
$canmanagevisibility = has_capability('local/coursecatalog:manageaudiencevisibility', context_system::instance());
if (!$canmanagevisibility) {
list($visibilitysql, $visibilityparams) = local_cohort_get_visible_learning_sql('p', 'id', COHORT_ASSN_ITEMTYPE_PROGRAM);
}
$search_info->sql = "
FROM
{prog} p
{$visibilitysql}
WHERE
{$searchsql}
";
$params = array_merge($params, $visibilityparams);
}
$search_info->order = " ORDER BY p.sortorder ASC";
$search_info->params = $params;
break;
/**
* Get the number of visible items in or below the selected categories
*
* This function counts the number of items within a set of categories, only including
* items that are visible to the user.
*
* By default returns the course count, but will work for programs, certifications too.
*
* We need to jump through some hoops to do this efficiently:
*
* - To avoid having to do it recursively it relies on the context
* path to find courses within a category
*
* - To avoid having to check capabilities for every item it only
* checks hidden courses, and only if user isn't a siteadmin
*
* @param integer|array $categoryids ID or IDs of the category/categories to fetch
* @param boolean $viewtype - type of item to count: course,program,certification
*
* @return integer|array Associative array, where keys are the sub-category IDs and value is the count.
* If $categoryids is a single integer, just returns the count as an integer
*/
function local_get_category_item_count($categoryids, $viewtype = 'course') {
global $CFG, $USER, $DB;
require_once($CFG->dirroot . '/cohort/lib.php');
list($insql, $params) = $DB->get_in_or_equal(is_array($categoryids) ? $categoryids : array($categoryids));
if (!$categories = $DB->get_records_select('course_categories', "id $insql", $params)) {
return array();
}
// What items are we counting, courses, programs, or certifications?
switch ($viewtype) {
case 'course':
$itemcap = 'moodle/course:viewhiddencourses';
$itemtable = "{course}";
$itemcontext = CONTEXT_COURSE;
$itemalias = 'c';
$extrawhere = '';
break;
case 'program':
$itemcap = 'local/program:viewhiddenprograms';
$itemtable = "{prog}";
$itemcontext = CONTEXT_PROGRAM;
$itemalias = 'p';
$extrawhere = " AND certifid IS NULL";
break;
case 'certification':
$itemcap = 'local/certification:viewhiddencertifications';
$itemtable = "{prog}";
$itemcontext = CONTEXT_PROGRAM;
$itemalias = 'p';
$extrawhere = " AND certifid IS NOT NULL";
break;
default:
print_error('invalid viewtype');
}
list($insql, $inparams) = $DB->get_in_or_equal(array_keys($categories), SQL_PARAMS_NAMED);
$sql = "SELECT instanceid, path
FROM {context}
WHERE contextlevel = :contextlvl
AND instanceid {$insql}
ORDER BY depth DESC";
$params = array('contextlvl' => CONTEXT_COURSECAT);
$params = array_merge($params, $inparams);
$contextpaths = $DB->get_records_sql_menu($sql, $params);
// Builds a WHERE snippet that matches any items inside the sub-category.
// This won't match the category itself (because of the trailing slash),
// But that's okay as we're only interested in the items inside.
$contextwhere = array(); $contextparams = array();
foreach ($contextpaths as $path) {
$paramalias = 'cxt_' . rand(1, 10000);
$contextwhere[] = $DB->sql_like('cx.path', ":{$paramalias}");
$contextparams[$paramalias] = $path . '/%';
}
// Add audience visibility setting.
$visibilitysql = '';
$visibilityparams = array();
$canmanagevisibility = has_capability('local/coursecatalog:manageaudiencevisibility', context_system::instance());
if (!empty($CFG->audiencevisibility) && !$canmanagevisibility) {
list($visibilitysql, $visibilityparams) = local_cohort_get_visible_learning_sql($itemalias, 'id', $itemcontext);
}
$sql = "SELECT {$itemalias}.id as itemid, {$itemalias}.visible, {$itemalias}.visible, cx.path
FROM {context} cx
JOIN {$itemtable} {$itemalias}
ON {$itemalias}.id = cx.instanceid AND contextlevel = :itemcontext
{$visibilitysql}
WHERE (" . implode(' OR ', $contextwhere) . ")" . $extrawhere;
$params = array('itemcontext' => $itemcontext);
$params = array_merge($params, $contextparams);
$params = array_merge($params, $visibilityparams);
// Get all items inside all the categories.
if (!$items = $DB->get_records_sql($sql, $params)) {
// Sub-categories are all empty.
if (is_array($categoryids)) {
return array();
} else {
return 0;
}
}
$results = array();
foreach ($items as $item) {
if (empty($CFG->audiencevisibility)) {
// Check individual permission.
// Get contextobj - use a switch in case this gets even more complicated in future with a third type.
switch ($itemcontext) {
case CONTEXT_COURSE:
$contextobj = context_course::instance($item->itemid);
break;
case CONTEXT_PROGRAM:
$contextobj = context_program::instance($item->itemid);
break;
}
if (!$item->visible && !has_capability($itemcap, $contextobj)) {
continue;
}
}
// We need to check if programs are available to students.
if ($viewtype == 'program' && !is_siteadmin($USER->id)) {
$program = new program($item->itemid);
if (!$program->is_accessible()) {
continue;
}
}
// Now we need to figure out which sub-category each item is a member of.
foreach ($contextpaths as $categoryid => $contextpath) {
// It's a member if the beginning of the contextpath's match.
if (substr($item->path, 0, strlen($contextpath.'/')) ==
$contextpath.'/') {
if (array_key_exists($categoryid, $results)) {
$results[$categoryid]++;
} else {
$results[$categoryid] = 1;
}
break;
}
}
}
if (empty($results)) {
return 0;
} else if (is_array($categoryids)) {
return $results;
} else {
return current($results);
}
}
