/**
* Gets a curriculum listing with specific sort and other filters as a recordset.
*
* @param string $sort Field to sort on.
* @param string $dir Direction of sort.
* @param int $startrec Record number to start at.
* @param int $perpage Number of records per page.
* @param string $namesearch Search string for curriculum name.
* @param string $alpha Start initial of curriculum name filter.
* @param array $contexts Contexts to search (in the form return by
* @param int $userid The id of the user we are assigning to curricula
*
* @return recordset Returned recordset.
*/
function curriculum_get_listing_recordset($sort = 'name', $dir = 'ASC', $startrec = 0, $perpage = 0, $namesearch = '', $alpha = '', $contexts = null, $userid = 0)
{
global $USER, $CURMAN;
$LIKE = $CURMAN->db->sql_compare();
$select = 'SELECT cur.*, (SELECT COUNT(*) FROM ' . $CURMAN->db->prefix_table(CURCRSTABLE) . ' WHERE curriculumid = cur.id ) as courses ';
$tables = 'FROM ' . $CURMAN->db->prefix_table(CURTABLE) . ' cur ';
$join = '';
$on = '';
$where = array("cur.iscustom = '0'");
if ($contexts !== null && !empty($namesearch)) {
$namesearch = trim($namesearch);
$where[] = "(name {$LIKE} '%{$namesearch}%')";
}
if ($alpha) {
$where[] = "(name {$LIKE} '{$alpha}%')";
}
if ($contexts !== null) {
$where[] = $contexts->sql_filter_for_context_level('cur.id', 'curriculum');
}
if (!empty($userid)) {
//get the context for the "indirect" capability
$context = cm_context_set::for_user_with_capability('cluster', 'block/curr_admin:curriculum:enrol_cluster_user', $USER->id);
$clusters = cluster_get_user_clusters($userid);
$allowed_clusters = $context->get_allowed_instances($clusters, 'cluster', 'clusterid');
$curriculum_context = cm_context_set::for_user_with_capability('curriculum', 'block/curr_admin:curriculum:enrol', $USER->id);
$curriculum_filter = $curriculum_context->sql_filter_for_context_level('cur.id', 'curriculum');
if (empty($allowed_clusters)) {
$where[] = $curriculum_filter;
} else {
$allowed_clusters_list = implode(',', $allowed_clusters);
//this allows both the indirect capability and the direct curriculum filter to work
$where[] = "(\n cur.id IN (\n SELECT clstcur.curriculumid\n FROM {$CURMAN->db->prefix_table(CLSTCURTABLE)} clstcur\n WHERE clstcur.clusterid IN ({$allowed_clusters_list})\n )\n OR\n {$curriculum_filter}\n )";
}
}
if (!empty($where)) {
$where = 'WHERE ' . implode(' AND ', $where) . ' ';
} else {
$where = '';
}
if ($sort) {
$sort = 'ORDER BY ' . $sort . ' ' . $dir . ' ';
}
if (!empty($perpage)) {
if ($CURMAN->db->_dbconnection->databaseType == 'postgres7') {
$limit = 'LIMIT ' . $perpage . ' OFFSET ' . $startrec;
} else {
$limit = 'LIMIT ' . $startrec . ', ' . $perpage;
}
} else {
$limit = '';
}
$sql = $select . $tables . $join . $on . $where . $sort . $limit;
return get_recordset_sql($sql);
}
/**
* Contructor.
*
* @param $userdata int/object/array The data id of a data record or data elements to load manually.
*
*/
function user($userdata = false)
{
parent::datarecord();
$this->set_table(USRTABLE);
$this->add_property('id', 'int');
$this->add_property('idnumber', 'string', true);
$this->add_property('username', 'string', true);
$this->add_property('password', 'string', true);
$this->add_property('firstname', 'string', true);
$this->add_property('lastname', 'string', true);
$this->add_property('mi', 'string');
$this->add_property('email', 'string', true);
$this->add_property('email2', 'string');
$this->add_property('address', 'string');
$this->add_property('address2', 'string');
$this->add_property('city', 'string');
$this->add_property('state', 'string');
$this->add_property('country', 'string', true);
$this->add_property('phone', 'string');
$this->add_property('phone2', 'string');
$this->add_property('fax', 'string');
$this->add_property('postalcode', 'string');
$this->add_property('birthdate', 'string');
$this->add_property('gender', 'string');
$this->add_property('language', 'string');
$this->add_property('transfercredits', 'string');
$this->add_property('comments', 'string');
$this->add_property('notes', 'string');
$this->add_property('timecreated', 'int');
$this->add_property('timeapproved', 'int');
$this->add_property('timemodified', 'int');
$this->add_property('inactive', 'int');
if (is_numeric($userdata) || is_string($userdata)) {
$this->data_load_record($userdata);
} else {
if (is_array($userdata)) {
$this->data_load_array($userdata);
} else {
if (is_object($userdata)) {
$this->data_load_array(get_object_vars($userdata));
}
}
}
if (!empty($this->id)) {
/// Load any other data we may want that is associated with the id number...
if ($clusters = cluster_get_user_clusters($this->id)) {
$this->load_cluster_info($clusters);
}
// custom fields
$level = context_level_base::get_custom_context_level('user', 'block_curr_admin');
if ($level) {
$fielddata = field_data::get_for_context(get_context_instance($level, $this->id));
$fielddata = $fielddata ? $fielddata : array();
foreach ($fielddata as $name => $value) {
$this->{"field_{$name}"} = $value;
}
}
}
// TODO: move this to accessors (set or get) so that birthdate and birthday/month/year are always in sync
if (isset($this->birthdate)) {
$birthdateparts = explode('/', $this->birthdate);
if (!empty($birthdateparts[1])) {
$this->birthday = $birthdateparts[2];
$this->birthmonth = $birthdateparts[1];
$this->birthyear = $birthdateparts[0];
} else {
$this->birthday = 0;
$this->birthmonth = 0;
$this->birthyear = 0;
}
}
}
/**
* Gets a track listing with specific sort and other filters.
*
* @param string $sort Field to sort on
* @param string $dir Direction of sort
* @param int $startrec Record number to start at
* @param int $perpage Number of records per page
* @param string $namesearch Search string for curriculum name
* @param string $alpha Start initial of curriculum name filter
* @param int $curriculumid Necessary associated curriculum
* @param int $clusterid Necessary associated cluster
* @param pm_context_set $contexts Contexts to provide permissions filtering, of null if none
* @param int $userid The id of the user we are assigning to tracks
*
* @return object array Returned records
*/
function track_get_listing($sort = 'name', $dir = 'ASC', $startrec = 0, $perpage = 0, $namesearch = '', $alpha = '', $curriculumid = 0, $parentclusterid = 0, $contexts = null, $userid = 0)
{
global $USER, $DB;
$params = array();
$NAMESEARCH_LIKE = $DB->sql_like('trk.name', ':search_namesearch', FALSE);
$ALPHA_LIKE = $DB->sql_like('trk.name', ':search_alpha', FALSE);
$select = 'SELECT trk.*, cur.name AS parcur, (SELECT COUNT(*) ' . 'FROM {' . trackassignment::TABLE . '} ' . "WHERE trackid = trk.id ) as class ";
$tables = 'FROM {' . track::TABLE . '} trk ' . 'JOIN {' . curriculum::TABLE . '} cur ON trk.curid = cur.id ';
$join = '';
$on = '';
$where = array('trk.defaulttrack = 0');
if (!empty($namesearch)) {
$namesearch = trim($namesearch);
$where[] = $NAMESEARCH_LIKE;
$params['search_namesearch'] = "%{$namesearch}%";
}
if ($alpha) {
//$where[] = "(trk.name $LIKE '$alpha%')";
$where[] = $ALPHA_LIKE;
$params['search_alpha'] = "{$alpha}%";
}
if ($curriculumid) {
$where[] = "(trk.curid = :curid)";
$params['curid'] = $curriculumid;
}
if ($parentclusterid) {
$where[] = "(trk.id IN (SELECT trackid FROM {" . clustertrack::TABLE . "}\n WHERE clusterid = :parentclusterid))";
$params['parentclusterid'] = $parentclusterid;
}
if ($contexts !== null) {
$filter_object = $contexts->get_filter('id', 'track');
$filter_sql = $filter_object->get_sql(false, 'trk', SQL_PARAMS_NAMED);
if (isset($filter_sql['where'])) {
$where[] = $filter_sql['where'];
$params = array_merge($params, $filter_sql['where_parameters']);
}
}
if (!empty($userid)) {
//get the context for the "indirect" capability
$context = pm_context_set::for_user_with_capability('cluster', 'local/elisprogram:track_enrol_userset_user', $USER->id);
$allowed_clusters = array();
$clusters = cluster_get_user_clusters($userid);
$allowed_clusters = $context->get_allowed_instances($clusters, 'cluster', 'clusterid');
$curriculum_context = pm_context_set::for_user_with_capability('cluster', 'local/elisprogram:track_enrol', $USER->id);
$curriculum_filter_object = $curriculum_context->get_filter('id', 'track');
$curriculum_filter = $curriculum_filter_object->get_sql(false, 'trk');
if (isset($curriculum_filter['where'])) {
if (count($allowed_clusters) != 0) {
$where[] = $curriculum_filter['where'];
$params = array_merge($params, $filter_sql['where_parameters']);
} else {
//this allows both the indirect capability and the direct track filter to work
$allowed_clusters_list = implode(',', $allowed_clusters);
$where[] = "(\n trk.id IN (\n SELECT clsttrk.trackid\n FROM {" . clustertrack::TABLE . "} clsttrk\n WHERE clsttrk.clusterid IN (:allowed_clusters)\n )\n OR\n {$curriculum_filter['where']}\n )";
$params['allowed_clusters'] = $allowed_clusters_list;
}
}
}
if (!empty($where)) {
$where = 'WHERE ' . implode(' AND ', $where) . ' ';
} else {
$where = '';
}
if ($sort) {
$sort = 'ORDER BY ' . $sort . ' ' . $dir . ' ';
}
$sql = $select . $tables . $join . $on . $where . $sort;
return $DB->get_records_sql($sql, $params, $startrec, $perpage);
}
/**
* Gets a cluster listing with specific sort and other filters.
*
* @param string $sort Field to sort on.
* @param string $dir Direction of sort.
* @param int $startrec Record number to start at.
* @param int $perpage Number of records per page.
* @param string $namesearch Search string for cluster name.
* @param string $descsearch Search string for cluster description.
* @param string $alpha Start initial of cluster name filter.
* @param int $userid User who you are assigning clusters to
* @return object array Returned records.
*/
function cluster_get_listing($sort = 'name', $dir = 'ASC', $startrec = 0, $perpage = 0, $namesearch = '', $alpha = '', $extrafilters = array(), $userid = 0)
{
global $USER, $CURMAN;
//require plugin code if enabled
$display_priority_enabled = in_array('cluster_display_priority', get_list_of_plugins('curriculum/plugins'));
if ($display_priority_enabled) {
require_once CURMAN_DIRLOCATION . '/plugins/cluster_display_priority/lib.php';
}
$LIKE = $CURMAN->db->sql_compare();
$select = 'SELECT clst.* ';
$tables = "FROM {$CURMAN->db->prefix_table(CLSTTABLE)} clst ";
$join = '';
$where_conditions = array();
if (!empty($namesearch)) {
$namesearch = trim($namesearch);
$where_conditions[] = "(name {$LIKE} '%{$namesearch}%') ";
}
if ($alpha) {
$where_conditions[] = "(name {$LIKE} '{$alpha}%') ";
}
if (!empty($extrafilters['contexts'])) {
/*
* Start of cluster hierarchy extension
*/
$sql_condition = '0=1';
if (cluster::all_clusters_viewable()) {
//user has capability at system level so allow access to any cluster
$sql_condition = '0=0';
} else {
//user does not have capability at system level, so filter
$viewable_clusters = cluster::get_viewable_clusters();
if (empty($viewable_clusters)) {
//user has no access to any clusters, so do not allow additional access
$sql_condition = '0=1';
} else {
//user has additional access to some set of clusters, so "enable" this access
$cluster_context_level = context_level_base::get_custom_context_level('cluster', 'block_curr_admin');
//use the context path to find parent clusters
$like = sql_ilike();
$parent_path = sql_concat('parent_context.path', "'/%'");
$cluster_filter = implode(',', $viewable_clusters);
$sql_condition = "clst.id IN (\n SELECT parent_context.instanceid\n FROM {$CURMAN->db->prefix_table('context')} parent_context\n JOIN {$CURMAN->db->prefix_table('context')} child_context\n ON child_context.path {$like} {$parent_path}\n AND parent_context.contextlevel = {$cluster_context_level}\n AND child_context.contextlevel = {$cluster_context_level}\n AND child_context.instanceid IN ({$cluster_filter})\n )";
}
}
/*
* End of cluster hierarchy extension
*/
$context_filter = $extrafilters['contexts']->sql_filter_for_context_level('clst.id', 'cluster');
//extend the basic context filter by potentially enabling access to parent clusters
$where_conditions[] = "({$context_filter} OR {$sql_condition})";
}
if (isset($extrafilters['parent'])) {
$where_conditions[] = "parent={$extrafilters['parent']}";
}
if (isset($extrafilters['classification'])) {
require_once CURMAN_DIRLOCATION . '/plugins/cluster_classification/lib.php';
$contextlevel = context_level_base::get_custom_context_level('cluster', 'block_curr_admin');
$field = new field(field::get_for_context_level_with_name($contextlevel, CLUSTER_CLASSIFICATION_FIELD));
$where_conditions[] = "clst.id IN (SELECT ctx.instanceid\n FROM {$CURMAN->db->prefix_table('context')} ctx\n JOIN (SELECT ctx.id AS contextid, IFNULL(fdata.data, fdefault.data) AS data\n FROM {$CURMAN->db->prefix_table('context')} ctx\n LEFT JOIN {$CURMAN->db->prefix_table($field->data_table())} fdata ON fdata.contextid = ctx.id AND fdata.fieldid = {$field->id}\n LEFT JOIN {$CURMAN->db->prefix_table($field->data_table())} fdefault ON fdefault.contextid IS NULL AND fdefault.fieldid = {$field->id}) fdata ON fdata.data = '{$extrafilters['classification']}' AND fdata.contextid = ctx.id\n WHERE ctx.contextlevel = {$contextlevel})";
}
if (!empty($userid)) {
//get the context for the "indirect" capability
$context = cm_context_set::for_user_with_capability('cluster', 'block/curr_admin:cluster:enrol_cluster_user', $USER->id);
$clusters = cluster_get_user_clusters($userid);
$allowed_clusters = $context->get_allowed_instances($clusters, 'cluster', 'clusterid');
$curriculum_context = cm_context_set::for_user_with_capability('cluster', 'block/curr_admin:cluster:enrol', $USER->id);
$curriculum_filter = $curriculum_context->sql_filter_for_context_level('clst.id', 'cluster');
if (empty($allowed_clusters)) {
$where_conditions[] = $curriculum_filter;
} else {
$allowed_clusters_list = implode(',', $allowed_clusters);
$cluster_context_level = context_level_base::get_custom_context_level('cluster', 'block_curr_admin');
$path = sql_concat('parentctxt.path', "'/%'");
$like = sql_ilike();
//this allows both the indirect capability and the direct curriculum filter to work
$where_conditions[] = "(\n (\n clst.id IN (\n SELECT childctxt.instanceid\n FROM\n {$CURMAN->db->prefix_table(CLSTTABLE)} clst\n JOIN {$CURMAN->db->prefix_table('context')} parentctxt\n ON clst.id = parentctxt.instanceid\n AND parentctxt.contextlevel = {$cluster_context_level}\n JOIN {$CURMAN->db->prefix_table('context')} childctxt\n ON childctxt.path {$like} {$path}\n AND childctxt.contextlevel = {$cluster_context_level}\n )\n )\n OR\n (\n {$curriculum_filter}\n )\n )";
}
}
//handle empty sort case
if (empty($sort)) {
$sort = 'name';
$dir = 'ASC';
}
//get the fields we are sorting
$sort_fields = explode(',', $sort);
//convert the fields into clauses
$sort_clauses = array();
foreach ($sort_fields as $key => $value) {
$new_value = trim($value);
if ($display_priority_enabled && $new_value == 'priority') {
$priority_key = $key;
$sort_clauses[$key] = $new_value . ' DESC';
} else {
$sort_clauses[$key] = $new_value . ' ' . $dir;
}
}
//determine if we are handling the priority field for ordering
if ($display_priority_enabled && in_array('priority', $sort_fields)) {
cluster_display_priority_append_sort_data('clst.id', $select, $join);
}
$where = '';
if (!empty($where_conditions)) {
$where = 'WHERE ' . implode(' AND ', $where_conditions) . ' ';
}
if (isset($priority_key) && !context_level_base::get_custom_context_level('cluster', 'block_curr_admin')) {
unset($sort_clauses[$priority_key]);
}
$sort_clause = 'ORDER BY ' . implode($sort_clauses, ', ') . ' ';
//paging
if (!empty($perpage)) {
if ($CURMAN->db->_dbconnection->databaseType == 'postgres7') {
$limit = 'LIMIT ' . $perpage . ' OFFSET ' . $startrec;
} else {
$limit = 'LIMIT ' . $startrec . ', ' . $perpage;
}
} else {
$limit = '';
}
$sql = $select . $tables . $join . $where . $sort_clause . $limit;
return $CURMAN->db->get_records_sql($sql);
}
/**
* Get standard permission filters for a user - element available table.
*
* This takes into account the local/elisprogram:[element]_enrol, and elis:program/[element]_enrol_userset_user permissions.
*
* @param string $elementtype The type of element we're associating to. I.e. program, track, class, userset.
* @param string $elementidsfromclusterids An SQL query to get ids for associated $elementtype from a list of clusters. Use
* the placeholder {clusterids} to include the $DB->get_in_or_equal call for the
* cluster ids.
* @return array An array consisting of an array of additional filters as 0, and parameters as 1
*/
protected function get_filter_sql_permissions_userelement_available($elementtype, $elementidsfromclusterids)
{
global $USER, $DB;
$elementtype2ctxlevel = array('program' => 'curriculum', 'track' => 'track', 'class' => 'pmclass', 'userset' => 'cluster');
if (!isset($elementtype2ctxlevel[$elementtype])) {
throw new Exception('Bad element type specified for get_filter_sql_permissions_userelement_available');
}
$enrolperm = 'local/elisprogram:' . $elementtype . '_enrol';
$usersetenrolperm = 'local/elisprogram:' . $elementtype . '_enrol_userset_user';
$ctxlevel = $elementtype2ctxlevel[$elementtype];
$additionalfilters = array();
$additionalparams = array();
// Get filter for contexts/elements where user has $enrolperm permission.
$elementenrolctxs = pm_context_set::for_user_with_capability($ctxlevel, $enrolperm, $USER->id);
$elementenrolctxsfilterobject = $elementenrolctxs->get_filter('id', $ctxlevel);
$elementenrolfilter = $elementenrolctxsfilterobject->get_sql(false, 'element', SQL_PARAMS_QM);
// Get elements that are associated with a userset where:
// - $this->userid is a member of the userset.
// - $USER has $usersetenrolperm permission on the userset.
$elementenrolusersetuserctxs = pm_context_set::for_user_with_capability('cluster', $usersetenrolperm, $USER->id);
$assigneeclusters = cluster_get_user_clusters($this->userid);
$clusters = array();
foreach ($assigneeclusters as $assigneecluster) {
$subsets = static::get_userset_subsets($assigneecluster->clusterid, true);
$clusters = array_merge($clusters, $subsets);
}
$allowedclusters = $elementenrolusersetuserctxs->get_allowed_instances($clusters, 'cluster', 'clusterid');
// Create the final filters.
if (isset($elementenrolfilter['where'])) {
if (empty($allowedclusters)) {
// If there's no $usersetenrolperm clusters to worry about, just use elements where assigner
// has $enrolperm perms.
$additionalfilters[] = $elementenrolfilter['where'];
$additionalparams = array_merge($additionalparams, $elementenrolfilter['where_parameters']);
} else {
// If we do have $usersetenrolperm clusters to worry about, we add a filter to require element ids to be either
// elements associated with the clusters where that permission exists, or contexts where the assigner has
// the $enrolperm permissions.
list($allowclusterswhere, $allowclustersparams) = $DB->get_in_or_equal($allowedclusters);
$elementidsfromclusterids = str_replace('{clusterids}', $allowclusterswhere, $elementidsfromclusterids);
$additionalfilters[] = '(element.id IN (' . $elementidsfromclusterids . ') OR ' . $elementenrolfilter['where'] . ')';
$additionalparams = array_merge($additionalparams, $allowclustersparams, $elementenrolfilter['where_parameters']);
}
}
return array($additionalfilters, $additionalparams);
}
/**
* Gets a track listing with specific sort and other filters.
*
* @param string $sort Field to sort on
* @param string $dir Direction of sort
* @param int $startrec Record number to start at
* @param int $perpage Number of records per page
* @param string $namesearch Search string for curriculum name
* @param string $alpha Start initial of curriculum name filter
* @param int $curriculumid Necessary associated curriculum
* @param int $clusterid Necessary associated cluster
* @param cm_context_set $contexts Contexts to provide permissions filtering, of null if none
* @param int $userid The id of the user we are assigning to tracks
*
* @return object array Returned records
*/
function track_get_listing($sort = 'name', $dir = 'ASC', $startrec = 0, $perpage = 0, $namesearch = '', $alpha = '', $curriculumid = 0, $parentclusterid = 0, $contexts = null, $userid = 0)
{
global $USER, $CURMAN;
$LIKE = $CURMAN->db->sql_compare();
$select = 'SELECT trk.*, cur.name AS parcur, (SELECT COUNT(*) FROM ' . $CURMAN->db->prefix_table(TRACKCLASSTABLE) . ' WHERE trackid = trk.id ) as class ';
$tables = 'FROM ' . $CURMAN->db->prefix_table(TRACKTABLE) . ' trk JOIN ' . $CURMAN->db->prefix_table(CURTABLE) . ' cur ON trk.curid = cur.id ';
$join = '';
$on = ' ';
$where = array('trk.defaulttrack = 0');
if (!empty($namesearch)) {
$namesearch = trim($namesearch);
$where[] = "(trk.name {$LIKE} '%{$namesearch}%')";
}
if ($alpha) {
$where[] = "(trk.name {$LIKE} '{$alpha}%')";
}
if ($curriculumid) {
$where[] = "(trk.curid = {$curriculumid})";
}
if ($parentclusterid) {
$where[] = "(trk.id IN (SELECT trackid FROM {$CURMAN->db->prefix_table(CLSTTRKTABLE)}\n WHERE clusterid = {$parentclusterid}))";
}
if ($contexts !== null) {
$where[] = $contexts->sql_filter_for_context_level('trk.id', 'track');
}
if (!empty($userid)) {
//get the context for the "indirect" capability
$context = cm_context_set::for_user_with_capability('cluster', 'block/curr_admin:track:enrol_cluster_user', $USER->id);
$allowed_clusters = array();
$clusters = cluster_get_user_clusters($userid);
$allowed_clusters = $context->get_allowed_instances($clusters, 'cluster', 'clusterid');
$curriculum_context = cm_context_set::for_user_with_capability('cluster', 'block/curr_admin:track:enrol', $USER->id);
$curriculum_filter = $curriculum_context->sql_filter_for_context_level('trk.id', 'track');
if (empty($allowed_clusters)) {
$where[] = $curriculum_filter;
} else {
//this allows both the indirect capability and the direct track filter to work
$allowed_clusters_list = implode(',', $allowed_clusters);
$where[] = "(\n trk.id IN (\n SELECT clsttrk.trackid\n FROM {$CURMAN->db->prefix_table(CLSTTRKTABLE)} clsttrk\n WHERE clsttrk.clusterid IN ({$allowed_clusters_list})\n )\n OR\n {$curriculum_filter}\n )";
}
}
$where = 'WHERE ' . implode(' AND ', $where) . ' ';
if ($sort) {
$sort = 'ORDER BY ' . $sort . ' ' . $dir . ' ';
}
if (!empty($perpage)) {
if ($CURMAN->db->_dbconnection->databaseType == 'postgres7') {
$limit = 'LIMIT ' . $perpage . ' OFFSET ' . $startrec;
} else {
$limit = 'LIMIT ' . $startrec . ', ' . $perpage;
}
} else {
$limit = '';
}
$sql = $select . $tables . $join . $on . $where . $sort . $limit;
return $CURMAN->db->get_records_sql($sql);
}
/**
* Gets a curriculum listing with specific sort and other filters as a recordset.
*
* @param string $sort Field to sort on.
* @param string $dir Direction of sort.
* @param int $startrec Record number to start at.
* @param int $perpage Number of records per page.
* @param string $namesearch Search string for curriculum name.
* @param string $alpha Start initial of curriculum name filter.
* @param array $contexts Contexts to search (in the form return by
* @param int $userid The id of the user we are assigning to curricula
* @uses $CFG
* @uses $DB
* @uses $USER
* @return recordset Returned recordset.
*/
function curriculum_get_listing_recordset($sort = 'name', $dir = 'ASC', $startrec = 0, $perpage = 0, $namesearch = '', $alpha = '', $contexts = null, $userid = 0)
{
global $CFG, $DB, $USER;
require_once $CFG->dirroot . '/local/elisprogram/lib/data/curriculum.class.php';
require_once $CFG->dirroot . '/local/elisprogram/lib/data/curriculumcourse.class.php';
require_once $CFG->dirroot . '/local/elisprogram/lib/data/clustercurriculum.class.php';
$select = 'SELECT cur.*, (SELECT COUNT(*) FROM {' . curriculumcourse::TABLE . '} WHERE curriculumid = cur.id ) as courses ';
$tables = 'FROM {' . curriculum::TABLE . '} cur ';
$join = '';
$on = '';
$params = array();
$where = array("cur.iscustom = '0'");
if ($contexts !== null && !empty($namesearch)) {
$where[] = '(' . $DB->sql_like('name', ':like_param', false) . ')';
$namesearch = trim($namesearch);
$params['like_param'] = "%{$namesearch}%";
}
if ($alpha) {
$where[] = '(' . $DB->sql_like('name', ':starts_with', false) . ')';
$params['starts_with'] = "{$alpha}%";
}
if ($contexts !== null) {
$filter_object = $contexts->get_filter('id', 'curriculum');
$filter_sql = $filter_object->get_sql(false, 'cur');
if (isset($filter_sql['where'])) {
$where[] = $filter_sql['where'];
$params = array_merge($params, $filter_sql['where_parameters']);
}
}
if (!empty($userid)) {
//get the context for the "indirect" capability
$context = pm_context_set::for_user_with_capability('cluster', 'local/elisprogram:program_enrol_userset_user', $USER->id);
$clusters = cluster_get_user_clusters($userid);
$allowed_clusters = $context->get_allowed_instances($clusters, 'cluster', 'clusterid');
$curriculum_context = pm_context_set::for_user_with_capability('curriculum', 'local/elisprogram:program_enrol', $USER->id);
$filter_object = $curriculum_context->get_filter('id', 'curriculum');
$filter_sql = $filter_object->get_sql(false, 'cur');
if (isset($filter_sql['where'])) {
$curriculum_filter = $filter_sql['where'];
$curriculum_params = $filter_sql['where_parameters'];
}
if (empty($allowed_clusters)) {
if (!empty($curriculum_filter)) {
$where[] = $curriculum_filter;
if (!empty($curriculum_params)) {
$params = array_merge($params, $curriculum_params);
}
}
} else {
$allowed_clusters_list = implode(',', $allowed_clusters);
//this allows both the indirect capability and the direct curriculum filter to work
$cluster_where = '(
cur.id IN (
SELECT clstcur.curriculumid
FROM {' . clustercurriculum::TABLE . '} clstcur
WHERE clstcur.clusterid IN (' . $allowed_clusters_list . ')
)';
if (!empty($curriculum_filter)) {
$cluster_where .= "OR\n {$curriculum_filter}\n )";
if (!empty($curriculum_params)) {
$params = array_merge($params, $curriculum_params);
}
}
$where[] = $cluster_where;
}
}
if (!empty($where)) {
$where = 'WHERE ' . implode(' AND ', $where) . ' ';
} else {
$where = '';
}
if ($sort) {
$sort = 'ORDER BY ' . $sort . ' ' . $dir . ' ';
}
$sql = $select . $tables . $join . $on . $where . $sort;
return $DB->get_recordset_sql($sql, $params, $startrec, $perpage);
}
/**
* Gets a cluster listing with specific sort and other filters.
*
* @param string $sort Field to sort on.
* @param string $dir Direction of sort.
* @param int $startrec Record number to start at.
* @param int $perpage Number of records per page.
* @param string $namesearch Search string for cluster name.
* @param string $descsearch Search string for cluster description.
* @param string $alpha Start initial of cluster name filter.
* @param int $userid User who you are assigning clusters to
* @return object array Returned records.
*/
function cluster_get_listing($sort = 'name', $dir = 'ASC', $startrec = 0, $perpage = 0, $namesearch = '', $alpha = '', $extrafilters = array(), $userid = 0)
{
global $USER, $DB;
//require plugin code if enabled
$plugins = get_plugin_list('elisprogram');
$display_priority_enabled = isset($plugins['usetdisppriority']);
if ($display_priority_enabled) {
require_once elis::plugin_file('elisprogram_usetdisppriority', 'lib.php');
$priority_field = field::get_for_context_level_with_name(CONTEXT_ELIS_USERSET, USERSET_DISPLAY_PRIORITY_FIELD);
if (empty($priority_field->id)) {
$display_priority_enabled = false;
}
}
$select = 'SELECT clst.* ';
$tables = 'FROM {' . userset::TABLE . '} clst';
$join = '';
$filters = array();
if (!empty($namesearch)) {
$namesearch = trim($namesearch);
$filters[] = new field_filter('name', "%{$namesearch}%", field_filter::LIKE);
}
if ($alpha) {
$filters[] = new field_filter('name', "{$alpha}%", field_filter::LIKE);
}
if (!empty($extrafilters['contexts'])) {
/*
* Start of cluster hierarchy extension
*/
$sql_condition = new select_filter('FALSE');
if (userset::all_clusters_viewable()) {
//user has capability at system level so allow access to any cluster
$sql_condition = new select_filter('TRUE');
} else {
//user does not have capability at system level, so filter
$viewable_clusters = userset::get_viewable_clusters();
if (empty($viewable_clusters)) {
//user has no access to any clusters, so do not allow additional access
$sql_condition = new select_filter('FALSE');
} else {
//user has additional access to some set of clusters, so "enable" this access
//use the context path to find parent clusters
$path = $DB->sql_concat('parent_context.path', "'/%'");
list($IN, $inparams) = $DB->get_in_or_equal($viewable_clusters);
$sql_condition = new select_filter("clst.id IN (SELECT parent_context.instanceid\n FROM {context} parent_context\n JOIN {context} child_context\n ON child_context.path LIKE {$path}\n AND parent_context.contextlevel = " . CONTEXT_ELIS_USERSET . "\n AND child_context.contextlevel = " . CONTEXT_ELIS_USERSET . "\n AND child_context.instanceid {$IN}\n )", $inparams);
}
}
/*
* End of cluster hierarchy extension
*/
$context_filter = $extrafilters['contexts']->get_filter('id', 'cluster');
//extend the basic context filter by potentially enabling access to parent clusters
$filters[] = new OR_filter(array($context_filter, $sql_condition));
}
if (isset($extrafilters['parent'])) {
$filters[] = new field_filter('parent', $extrafilters['parent']);
}
if (isset($extrafilters['classification'])) {
require_once elispm::file('plugins/usetclassify/lib.php');
$field = new field(field::get_for_context_level_with_name(CONTEXT_ELIS_USERSET, USERSET_CLASSIFICATION_FIELD));
$filters[] = new elis_field_filter($field, 'id', CONTEXT_ELIS_USERSET, $extrafilters['classification']);
}
if (!empty($userid)) {
//get the context for the "indirect" capability
$context = pm_context_set::for_user_with_capability('cluster', 'local/elisprogram:userset_enrol_userset_user', $USER->id);
$clusters = cluster_get_user_clusters($userid);
$allowed_clusters = $context->get_allowed_instances($clusters, 'cluster', 'clusterid');
$curriculum_context = pm_context_set::for_user_with_capability('cluster', 'local/elisprogram:userset_enrol', $USER->id);
$curriculum_filter = $curriculum_context->get_filter('id');
if (empty($allowed_clusters)) {
$filters[] = $curriculum_filter;
} else {
$allowed_clusters_list = implode(',', $allowed_clusters);
$path = $DB->sql_concat('parentctxt.path', "'/%'");
//this allows both the indirect capability and the direct curriculum filter to work
$subcluster_filter = new select_filter("clst.id IN (SELECT childctxt.instanceid\n FROM {" . userset::TABLE . "} clst\n JOIN {context} parentctxt\n ON clst.id = parentctxt.instanceid\n AND parentctxt.contextlevel = " . CONTEXT_ELIS_USERSET . "\n JOIN {context} childctxt\n ON childctxt.path LIKE {$path}\n AND childctxt.contextlevel = " . CONTEXT_ELIS_USERSET . "\n WHERE parentctxt.instanceid IN ({$allowed_clusters_list}))");
$filters[] = new OR_filter(array($subcluster_filter, $curriculum_filter));
}
}
//handle empty sort case
if (empty($sort)) {
$sort = 'name';
$dir = 'ASC';
}
//get the fields we are sorting
$sort_fields = explode(',', $sort);
//convert the fields into clauses
$sort_clauses = array();
foreach ($sort_fields as $field) {
$field = trim($field);
if ($field == 'priority') {
if ($display_priority_enabled) {
$sort_clauses[] = $field . ' DESC';
}
} else {
$sort_clauses[] = $field . ' ' . $dir;
}
}
if (empty($sort_clauses)) {
$sort_clauses = array('name ASC');
}
//determine if we are handling the priority field for ordering
if ($display_priority_enabled && in_array('priority', $sort_fields)) {
userset_display_priority_append_sort_data('clst.id', $select, $join);
}
$filter = new AND_filter($filters);
$filtersql = $filter->get_sql(true, 'clst');
$params = array();
$where = '';
if (isset($filtersql['join'])) {
$join .= ' JOIN ' . $filtersql['join'];
$params = array_merge($params, $filtersql['join_parameters']);
}
if (isset($filtersql['where'])) {
$where = ' WHERE ' . $filtersql['where'];
$params = array_merge($params, $filtersql['where_parameters']);
}
$sort_clause = ' ORDER BY ' . implode($sort_clauses, ', ') . ' ';
$sql = $select . $tables . $join . $where . $sort_clause;
$recordset = $DB->get_recordset_sql($sql, $params, $startrec, $perpage);
return new data_collection($recordset, 'userset', null, array(), true);
}
