/** * 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); }