/** * Appends additional data to query parameters based on existence of theme priority field * * @param string $cluster_id_field The field to join on for the cluster id * @param string $select The current select clause * @param string $join The current join clause */ function cluster_display_priority_append_sort_data($cluster_id_field, &$select, &$join) { global $CURMAN; //make sure we can get the field we need for ordering if ($theme_priority_field = new field(field::get_for_context_level_with_name('cluster', CLUSTER_DISPLAY_PRIORITY_FIELD)) and $contextlevel = context_level_base::get_custom_context_level('cluster', 'block_curr_admin')) { $field_data_table = $CURMAN->db->prefix_table($theme_priority_field->data_table()); //use this for easier naming in terms of sorting $select .= ', field_data.data AS priority '; $join .= "LEFT JOIN ({$CURMAN->db->prefix_table('context')} context\n JOIN {$field_data_table} field_data\n ON field_data.contextid = context.id\n AND field_data.fieldid = {$theme_priority_field->id})\n\n ON context.contextlevel = {$contextlevel}\n AND context.instanceid = {$cluster_id_field} "; } }
/** * Appends additional data to query parameters based on existence of theme priority field * * @param string $cluster_id_field The field to join on for the cluster id * @param string $select The current select clause * @param string $join The current join clause */ function userset_display_priority_append_sort_data($userset_id_field, &$select, &$join) { global $DB; //make sure we can get the field we need for ordering if ($theme_priority_field = new field(field::get_for_context_level_with_name(CONTEXT_ELIS_USERSET, USERSET_DISPLAY_PRIORITY_FIELD))) { $field_data_table = $theme_priority_field->data_table(); //use this for easier naming in terms of sorting $select .= ', field_data.data AS priority '; $join .= ' LEFT JOIN ({context} context JOIN {' . $field_data_table . '} field_data ON field_data.contextid = context.id AND field_data.fieldid = ' . $theme_priority_field->id . ') ON context.contextlevel = ' . CONTEXT_ELIS_USERSET . ' AND context.instanceid = ' . $userset_id_field . ' '; } }
/** * Return the maximum test score SQL statement * * @param string $field_shortname field short name to be used in get_field request * * @return string The appropriate SQL statement */ function get_max_test_score_sql($field_shortname) { global $CURMAN, $CFG; $course_context_level = context_level_base::get_custom_context_level('course', 'block_curr_admin'); if ($field_id = get_field('crlm_field', 'id', 'shortname', $field_shortname)) { $field = new field($field_id); $data_table = $CURMAN->db->prefix_table($field->data_table()); $sql = "SELECT MAX(clsgrd.grade) AS score,\n class.id AS classid,\n clsgrd.userid AS userid\n FROM {$data_table} d\n JOIN {$CURMAN->db->prefix_table('context')} ctxt\n ON d.contextid = ctxt.id\n AND ctxt.contextlevel = {$course_context_level}\n JOIN {$CURMAN->db->prefix_table(CRSCOMPTABLE)} comp\n ON d.data = comp.idnumber\n JOIN {$CURMAN->db->prefix_table(CLSTABLE)} class\n ON class.courseid = ctxt.instanceid\n JOIN {$CURMAN->db->prefix_table(GRDTABLE)} clsgrd\n ON clsgrd.classid = class.id\n AND clsgrd.locked = 1\n AND clsgrd.completionid = comp.id\n WHERE d.fieldid = {$field_id}\n GROUP BY class.id, clsgrd.userid\n "; } else { $sql = "SELECT NULL AS score, NULL AS classid, NULL as userid FROM {$CFG->prefix}user"; } return $sql; }
/** * Returns an SQL fragment needed to connect a context entity table to its * CM custom fields data in the CM system * * @param string $contextlevel Shortname of the context level we are looking for fields * related to * @param array $fieldids List of ids of field records we are displaying * @param string $instancefield Database field representing the context id * @return string The appropriate SQL fragment */ function get_custom_field_sql($fieldids, $instancefields) { global $CURMAN; $contexts = $CURMAN->db->get_records_list('crlm_field_contextlevel', 'fieldid', implode(',', $fieldids), 'contextlevel'); $levels = context_level_base::get_all_context_levels(); $contextlevel = ''; $fragment = array(); if (!empty($contexts)) { //add a join for each profile field foreach ($contexts as $context) { if ($contextlevel != $context->contextlevel) { $contextlevel = $context->contextlevel; $contextname = $levels[$contextlevel]->get_component_string('', ''); $instancefield = $instancefields[$contextname]; //have one ot more profile field we're joining, so join the context table at the top level $context_level = context_level_base::get_custom_context_level($contextlevel, 'block_curr_admin'); $fragment[] .= " LEFT JOIN {$CURMAN->db->prefix_table('context')} {$contextname}context\n ON {$instancefield} = {$contextname}context.instanceid\n AND {$contextname}context.contextlevel = {$contextlevel}"; } $field = new field($context->fieldid); $identifier = "customfielddata_{$context->fieldid}"; $fragment[] .= " LEFT JOIN {$CURMAN->db->prefix_table($field->data_table())} {$identifier}" . " ON {$contextname}context.id = {$identifier}.contextid" . " AND {$identifier}.fieldid = {$context->fieldid}"; } } return implode("\n", $fragment); }
/** * Upgrade old ELIS tables. * * @param int $oldversion The old ELIS version. * @return bool Success/Failure. */ function local_elisprogram_upgrade_old_tables($oldversion) { global $DB, $CFG; $dbman = $DB->get_manager(); $result = true; if ($result && $oldversion < 2013031400) { // ELIS-8066: remove blank/empty menu options from custom field menu/checkbox and defaults using them. $customfields = $DB->get_recordset('local_eliscore_field', null, '', 'id'); foreach ($customfields as $id => $unused) { $field = new field($id); $field->load(); if (isset($field->owners['manual'])) { $manual = new field_owner($field->owners['manual']); $control = $manual->param_control; $options = $manual->param_options; if (!empty($options) && empty($manual->param_options_source) && ($control == 'menu' || $control == 'checkbox')) { $options = str_replace("\r", '', $options); // Strip CRs. $options = preg_replace("/\n+/", "\n", $options); $manual->param_options = rtrim($options, "\n"); $manual->save(); // Remove any empty defaults. $DB->delete_records_select($field->data_table(), "contextid IS NULL AND fieldid = ? AND data = ''", array($id)); } } } upgrade_plugin_savepoint($result, 2013031400, 'elis', 'program'); } // ELIS-7780: remove deprecated capabilites. if ($result && $oldversion < 2013041900) { $capstodelete = array('elis/program:viewgroupreports', 'elis/program:viewreports'); list($inorequal, $params) = $DB->get_in_or_equal($capstodelete); $where = "capability {$inorequal}"; $DB->delete_records_select('role_capabilities', $where, $params); $where = "name {$inorequal}"; $DB->delete_records_select('capabilities', $where, $params); upgrade_plugin_savepoint($result, 2013041900, 'elis', 'program'); } // Remove any duplicate user track records before attempting to apply an index. pm_fix_duplicate_usertrack_records('crlm_user_track'); if ($result && $oldversion < 2013042900) { // Add indexes to {crlm_user_track} table. $table = new xmldb_table('crlm_user_track'); if ($dbman->table_exists($table)) { // Array of indexes to drop. $dropindexes = array(new xmldb_index('any_userid_ix', XMLDB_INDEX_UNIQUE, array('userid')), new xmldb_index('any_trackid_ix', XMLDB_INDEX_UNIQUE, array('trackid')), new xmldb_index('any_userid_trackid_ix', XMLDB_INDEX_NOTUNIQUE, array('userid', 'trackid'))); foreach ($dropindexes as $index) { // Drop unwanted indexes if they exist. if ($dbman->index_exists($table, $index)) { $dbman->drop_index($table, $index); } } // Array of indexes to create. $createindexes = array(new xmldb_index('userid_ix', XMLDB_INDEX_NOTUNIQUE, array('userid')), new xmldb_index('trackid_ix', XMLDB_INDEX_NOTUNIQUE, array('trackid')), new xmldb_index('userid_trackid_ix', XMLDB_INDEX_UNIQUE, array('userid', 'trackid'))); foreach ($createindexes as $index) { // Create desired indexes as required. if (!$dbman->index_exists($table, $index)) { $dbman->add_index($table, $index); } } } upgrade_plugin_savepoint($result, 2013042900, 'elis', 'program'); } if ($result && $oldversion < 2013051500) { // Change results engine action min/max fields from integer to float. $table = new xmldb_table('crlm_results_action'); if ($dbman->table_exists($table)) { $field = new xmldb_field('minimum', XMLDB_TYPE_NUMBER, '10,5', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, 'actiontype'); $dbman->change_field_type($table, $field); $field = new xmldb_field('maximum', XMLDB_TYPE_NUMBER, '10,5', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null, 'minimum'); $dbman->change_field_type($table, $field); } upgrade_plugin_savepoint($result, 2013051500, 'elis', 'program'); } if ($result && $oldversion < 2013051502) { // Define table crlm_certificate_settings to be created // Conditionally launch create table for crlm_certificate_settings. $table = new xmldb_table('crlm_certificate_settings'); if (!$dbman->table_exists($table)) { // Adding fields to table crlm_certificate_settings. $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); $table->add_field('entity_id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null); $table->add_field('entity_type', XMLDB_TYPE_CHAR, '9', null, XMLDB_NOTNULL, null, null); $table->add_field('cert_border', XMLDB_TYPE_CHAR, '200', null, XMLDB_NOTNULL, null, null); $table->add_field('cert_seal', XMLDB_TYPE_CHAR, '200', null, XMLDB_NOTNULL, null, null); $table->add_field('cert_template', XMLDB_TYPE_CHAR, '200', null, XMLDB_NOTNULL, null, null); $table->add_field('disable', XMLDB_TYPE_INTEGER, '1', null, XMLDB_NOTNULL, null, '1'); $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); $table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); // Adding keys to table crlm_certificate_settings. $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); // Adding indexes to table crlm_certificate_settings. $table->add_index('ent_id_type_ix', XMLDB_INDEX_UNIQUE, array('entity_id', 'entity_type')); $dbman->create_table($table); } // Define table crlm_certificate_issued to be created. $table = new xmldb_table('crlm_certificate_issued'); // Conditionally launch create table for crlm_certificate_issued. if (!$dbman->table_exists($table)) { // Adding fields to table crlm_certificate_issued. $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); $table->add_field('cm_userid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null); $table->add_field('cert_setting_id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null); $table->add_field('cert_code', XMLDB_TYPE_CHAR, '40', null, XMLDB_NOTNULL, null, null); $table->add_field('timeissued', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); $table->add_field('timecreated', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0'); // Adding keys to table crlm_certificate_issued. $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); // Adding indexes to table crlm_certificate_issued. $table->add_index('cert_code_ix', XMLDB_INDEX_UNIQUE, array('cert_code')); $table->add_index('cm_userid_ix', XMLDB_INDEX_NOTUNIQUE, array('cm_userid')); $dbman->create_table($table); } // ELIS savepoint reached. upgrade_plugin_savepoint($result, 2013051502, 'elis', 'program'); } // ELIS-8528: remove orphaned LOs. if ($result && $oldversion < 2013051503) { $where = 'NOT EXISTS (SELECT \'x\' FROM {crlm_course} cc WHERE cc.id = {crlm_course_completion}.courseid)'; $DB->delete_records_select('crlm_course_completion', $where); upgrade_plugin_savepoint($result, 2013051503, 'elis', 'program'); } if ($result && $oldversion < 2013082100) { // Change password field length to 255. $table = new xmldb_table('crlm_user'); $field = new xmldb_field('password', XMLDB_TYPE_CHAR, '255', null, null, null, null, 'username'); $dbman->change_field_precision($table, $field); upgrade_plugin_savepoint($result, 2013082100, 'elis', 'program'); } if ($result && $oldversion < 2013082101) { require_once elis::file('eliscore/fields/manual/custom_fields.php'); require_once elis::file('eliscore/fields/moodleprofile/custom_fields.php'); $fieldcat = field_category::ensure_exists_for_contextlevel(get_string('moodlefields', 'local_elisprogram'), CONTEXT_ELIS_USER); $sql = 'SELECT m.id FROM {user_info_field} m WHERE NOT EXISTS( SELECT * FROM {local_eliscore_field} e WHERE e.shortname = m.shortname )'; $fieldstosync = $DB->get_records_sql($sql); foreach ($fieldstosync as $mfield) { // Create field. $efield = field::make_from_moodle_field($mfield->id, $fieldcat, pm_moodle_profile::sync_from_moodle); // Sync profile field information from Moodle into ELIS. sync_profile_field_from_moodle($efield); } upgrade_plugin_savepoint($result, 2013082101, 'elis', 'program'); } if ($result && $oldversion < 2013082103) { // ELIS-8441 & ELIS-8569: Fix Program timetocomplete & frequency defaults of '0y'. $table = new xmldb_table('crlm_curriculum'); if ($dbman->table_exists($table)) { $field = new xmldb_field('timetocomplete', XMLDB_TYPE_CHAR, '64', null, XMLDB_NOTNULL, null, null, 'timemodified'); $dbman->change_field_default($table, $field); $field = new xmldb_field('frequency', XMLDB_TYPE_CHAR, '64', null, XMLDB_NOTNULL, null, null, 'timetocomplete'); $dbman->change_field_default($table, $field); $sql = 'UPDATE {crlm_curriculum} SET timetocomplete = "" WHERE timetocomplete = "0y"'; $DB->execute($sql); $sql = 'UPDATE {crlm_curriculum} SET frequency = "" WHERE frequency = "0y"'; $DB->execute($sql); } upgrade_plugin_savepoint($result, 2013082103, 'elis', 'program'); } if ($result && $oldversion < 2013082104) { require_once dirname(__FILE__) . '/../../../lib/lib.php'; pm_set_config('notify_addedtowaitlist_user', 1); pm_set_config('notify_enroledfromwaitlist_user', 1); pm_set_config('notify_incompletecourse_user', 1); upgrade_plugin_savepoint($result, 2013082104, 'elis', 'program'); } return $result; }
/** * Specifies the data representing the average test score per user * * @param string $field_shortname field short name to be used in get_field request * * @return numeric The calculated value */ function get_average_test_score($field_shortname) { global $CURMAN; $result = get_string('na', 'rlreport_course_usage_summary'); //Get the course context $course_context_level = context_level_base::get_custom_context_level('course', 'block_curr_admin'); //Get the field id of the field shortname to use in the data table if ($field_id = get_field('crlm_field', 'id', 'shortname', $field_shortname)) { $field = new field($field_id); $data_table = $CURMAN->db->prefix_table($field->data_table()); //main query $sql = "SELECT AVG(clsgrd.grade) AS score\n FROM {$data_table} d\n JOIN {$CURMAN->db->prefix_table('context')} ctxt\n ON d.contextid = ctxt.id\n AND ctxt.contextlevel = {$course_context_level}\n JOIN {$CURMAN->db->prefix_table(CRSCOMPTABLE)} comp\n ON d.data = comp.idnumber\n JOIN {$CURMAN->db->prefix_table(CLSTABLE)} class\n ON class.courseid = ctxt.instanceid\n JOIN {$CURMAN->db->prefix_table(CLSGRTABLE)} clsgrd\n ON clsgrd.classid = class.id\n AND clsgrd.locked = 1\n AND clsgrd.completionid = comp.id\n JOIN {$CURMAN->db->prefix_table(STUTABLE)} enrol\n ON enrol.classid = class.id\n AND enrol.userid = clsgrd.userid"; if (empty($CURMAN->config->legacy_show_inactive_users)) { $sql .= " JOIN {$CURMAN->db->prefix_table(USRTABLE)} usr ON usr.id = enrol.userid"; } //get permissions sql bit if ($this->need_permissions()) { $permissions_filter = ' AND ' . $this->get_permissions(); } else { $permissions_filter = ''; } if ($this->filter_statement || $this->need_permissions()) { $sql .= " WHERE EXISTS (\n SELECT *\n FROM {$CURMAN->db->prefix_table(STUTABLE)} enrol2\n JOIN {$CURMAN->db->prefix_table(CLSTABLE)} class\n ON class.id = enrol2.classid\n JOIN {$CURMAN->db->prefix_table(CURCRSTABLE)} curcrs\n ON curcrs.courseid = class.courseid\n JOIN {$CURMAN->db->prefix_table(CURASSTABLE)} curass\n ON curcrs.curriculumid = curass.curriculumid\n AND curass.userid = enrol2.userid\n WHERE enrol.id = enrol2.id\n AND d.fieldid = {$field_id}\n {$this->filter_statement}\n {$permissions_filter}\n )"; } else { $sql .= " WHERE d.fieldid = {$field_id}"; } if (empty($CURMAN->config->legacy_show_inactive_users)) { $sql .= ' AND usr.inactive = 0'; } $avg_crs_grd = 0; if ($avg_crs_grd_record = get_record_sql($sql)) { $avg_crs_grd = $avg_crs_grd_record->score; } } //Format as a percentage return round($avg_crs_grd) . '%'; }
function cluster_count_records($namesearch = '', $alpha = '', $extrafilters = array()) { global $CURMAN; $select = array(); $LIKE = $CURMAN->db->sql_compare(); if (!empty($namesearch)) { $namesearch = trim($namesearch); $select[] = "(name {$LIKE} '%{$namesearch}%')"; } if ($alpha) { $select[] = "(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 = "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('id', 'cluster'); //extend the basic context filter by potentially enabling access to parent clusters $select[] = "({$context_filter} OR {$sql_condition})"; } if (isset($extrafilters['parent'])) { $select[] = "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)); $select[] = "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})"; } $select = implode(' AND ', $select); return $CURMAN->db->count_records_select(CLSTTABLE, $select); }
/** * Specifies the data representing the average test score per user * * @param string $field_shortname field short name to be used in get_field request * * @return numeric The calculated value */ function get_average_test_score($field_shortname) { global $DB; $params = array(); $result = get_string('na', 'rlreport_course_usage_summary'); $avg_crs_grd = 0; //Get the field id of the field shortname to use in the data table if ($field_id = $DB->get_field(field::TABLE, 'id', array('shortname' => $field_shortname))) { $field = new field($field_id); $data_table = $field->data_table(); //main query $sql = 'SELECT AVG(clsgrd.grade) AS score FROM {' . $data_table . "} d\n JOIN {context} ctxt\n ON d.contextid = ctxt.id\n AND ctxt.contextlevel = " . CONTEXT_ELIS_COURSE . "\n JOIN {" . coursecompletion::TABLE . '} comp ON d.data = comp.idnumber JOIN {' . pmclass::TABLE . '} class ON class.courseid = ctxt.instanceid JOIN {' . student_grade::TABLE . '} clsgrd ON clsgrd.classid = class.id AND clsgrd.locked = 1 AND clsgrd.completionid = comp.id JOIN {' . student::TABLE . '} enrol ON enrol.classid = class.id AND enrol.userid = clsgrd.userid '; if (empty(elis::$config->local_elisprogram->legacy_show_inactive_users)) { $sql .= 'JOIN {' . user::TABLE . '} usr ON usr.id = enrol.userid '; } //get permissions sql bit if ($this->need_permissions()) { $permissions_filter = 'AND ' . $this->get_permissions($params); } else { $permissions_filter = ''; } if ($this->filter_statement || $this->need_permissions()) { $sql .= ' WHERE EXISTS (SELECT * FROM {' . student::TABLE . '} enrol2 JOIN {' . pmclass::TABLE . '} class ON class.id = enrol2.classid JOIN {' . curriculumcourse::TABLE . '} curcrs ON curcrs.courseid = class.courseid JOIN {' . curriculumstudent::TABLE . "} curass\n ON curcrs.curriculumid = curass.curriculumid\n AND curass.userid = enrol2.userid\n WHERE enrol.id = enrol2.id\n AND d.fieldid = {$field_id}\n {$this->filter_statement}\n {$permissions_filter}\n ) "; } else { $sql .= "\n WHERE d.fieldid = {$field_id}\n "; } if (empty(elis::$config->local_elisprogram->legacy_show_inactive_users)) { $sql .= 'AND usr.inactive = 0'; } if ($avg_crs_grd_record = $DB->get_record_sql($sql, $params)) { $avg_crs_grd = $avg_crs_grd_record->score; } } //Format as a percentage return round($avg_crs_grd) . '%'; }
function validation($data, $files) { global $CFG, $CURMAN; $errors = parent::validation($data, $files); // Use a default for 'id' if we're doing an add if (!$data['id']) { $data['id'] = 0; } if (!empty($data['username'])) { if (!$this->check_unique(USRTABLE, 'username', $data['username'], $data['id'])) { $errors['username_group'] = get_string('badusername', 'block_curr_admin'); } } else { if (!$data['id'] && empty($data['id_same_user'])) { $errors['username_group'] = get_string('required'); } } if (!empty($data['idnumber'])) { if (!$this->check_unique(USRTABLE, 'idnumber', $data['idnumber'], $data['id'])) { $errors['idnumber'] = get_string('badidnumber', 'block_curr_admin'); } } // Validate the supplied email addresses as best we can... if (!empty($data['email'])) { if (!$this->check_unique(USRTABLE, 'email', $data['email'], $data['id'])) { $errors['email'] = get_string('emailexists'); } } if (!empty($data['email2'])) { if (!$this->check_unique(USRTABLE, 'email', $data['email2'], $data['id'])) { $errors['email2'] = get_string('emailexists'); } } if (!empty($data['contactemail'])) { if (!$this->check_unique(USRTABLE, 'email', $data['contactemail'], $data['id'])) { $errors['contactemail'] = get_string('emailexists'); } } // validate custom profile fields $fields = field::get_for_context_level(context_level_base::get_custom_context_level('user', 'block_curr_admin')); $fields = $fields ? $fields : array(); if ($data['id']) { $context = get_context_instance(context_level_base::get_custom_context_level('user', 'block_curr_admin'), $data['id']); $contextid = $context->id; } else { $contextid = 0; } foreach ($fields as $field) { $field = new field($field); $key = "field_{$field->shortname}"; if ($field->multivalued) { $manual = new field_owner($field->owners['manual']); $fielddata = isset($data[$key]) ? $data[$key] : array(); if ($manual->param_required) { if (empty($fielddata)) { $errors[$key] = get_string('required'); } else { if (!empty($manual->param_options)) { $options = explode("\n", $manual->param_options); array_walk($options, 'trim_cr'); // TBD: defined below foreach ($fielddata as $entry) { if (!in_array($entry, $options)) { $errors[$key] = get_string('required'); break; } } } } } if (!isset($errors[$key]) && $field->forceunique && $contextid) { $where = "contextid != {$contextid} AND fieldid = {$field->id}"; if ($recs = get_records_select($field->data_table(), $where, 'contextid, data')) { $curcontext = -1; $vals = null; foreach ($recs as $rec) { if ($curcontext != $rec->contextid) { if (!empty($vals)) { $adif = array_diff($vals, $fielddata); if (empty($adif)) { $errors[$key] = get_string('valuealreadyused'); // TBD^^^ "[These/This combination of] values already uesd!" $vals = null; break; } } $curcontext = $rec->contextid; $vals = array(); } $vals[] = $rec->data; } if (!empty($vals)) { $adif = array_diff($vals, $fielddata); if (empty($adif)) { $errors[$key] = get_string('valuealreadyused'); // TBD^^^ "[These/This combination of] values already uesd!" } } } } } else { if ($field->forceunique) { // NON-MULTIVALUED case $fielddata = $CURMAN->db->get_record($field->data_table(), 'fieldid', $field->id, 'data', $data[$key]); //print_object($fielddata); if ($fielddata && $fielddata->contextid != $contextid) { $errors[$key] = get_string('valuealreadyused'); } } } } return $errors; }
/** * Returns an SQL fragment needed to connect a context entity table to its * CM custom fields data in the CM system * * @param string $contextlevel Shortname of the context level we are looking for fields * related to * @param array $fieldids List of ids of field records we are displaying * @param string $instancefield Database field representing the context id * @return string The appropriate SQL fragment */ function get_custom_field_sql($fieldids, $instancefields) { global $DB; $where = $DB->get_in_or_equal($fieldids); $contexts = $DB->get_records_select(field_contextlevel::TABLE, 'fieldid ' . $where[0], $where[1]); $fragment = array(); /* *** debug *** ob_start(); var_dump($instancefields); $tmp = ob_get_contents(); ob_end_clean(); error_log("UCCR::get_custom_field_sql(fieldids, instancefields = {$tmp})"); */ // Get the legacy context names mapped to the context level values $contextlevelnames = array_flip(\local_eliscore\context\helper::get_legacy_levels()); $contextlevel = ''; $contextname = ''; if (!empty($contexts)) { //add a join for each profile field foreach ($contexts as $context) { $ctxname = $contextlevelnames[$context->contextlevel]; if (!in_array($ctxname, array_keys($instancefields))) { // Not a context level we care about continue; } if ($contextlevel != $context->contextlevel) { $contextlevel = $context->contextlevel; $contextname = $ctxname . '_'; // TBD list($instancefield, $_as, $_var) = explode(' ', $instancefields[$ctxname]); // have one or more profile field we're joining, so join the context table at the top level $fragment[$ctxname] = " LEFT JOIN {context} {$contextname}context\n ON {$instancefield} = {$contextname}context.instanceid\n AND {$contextname}context.contextlevel = {$contextlevel}"; } $field = new field($context->fieldid); $identifier = "customfielddata_{$context->fieldid}"; $fragment[] = ' LEFT JOIN {' . $field->data_table() . "} {$identifier}\n ON {$contextname}context.id = {$identifier}.contextid\n AND {$identifier}.fieldid = {$context->fieldid}"; } } $result = implode("\n", $fragment); // error_log("UCCR::get_custom_field_sql() => {$result}"); return $result; }
/** * Return the maximum test score SQL statement * * @param string $field_shortname field short name to be used in get_field request * @uses $DB * @return string The appropriate SQL statement */ function get_max_test_score_sql($field_shortname) { global $DB; if ($field_id = $DB->get_field(field::TABLE, 'id', array('shortname' => $field_shortname))) { $field = new field($field_id); $data_table = $field->data_table(); $sql = 'SELECT MAX(clsgrd.grade) AS score, class.id AS classid, clsgrd.userid AS userid FROM {' . $data_table . "} d\n JOIN {context} ctxt\n ON d.contextid = ctxt.id\n AND ctxt.contextlevel = " . CONTEXT_ELIS_COURSE . "\n JOIN {" . coursecompletion::TABLE . '} comp ON d.data = comp.idnumber JOIN {' . pmclass::TABLE . '} class ON class.courseid = ctxt.instanceid JOIN {' . student_grade::TABLE . "} clsgrd\n ON clsgrd.classid = class.id\n AND clsgrd.locked = 1\n AND clsgrd.completionid = comp.id\n WHERE d.fieldid = {$field_id}\n GROUP BY class.id, clsgrd.userid\n "; } else { $sql = "SELECT NULL AS score, NULL AS classid, NULL as userid\n FROM {user}"; } return $sql; // TBD: array ??? }
/** * Method that specifies the report's columns * (specifies various fields involving user info, clusters, class enrolment, and module information) * * @uses $DB * @return table_report_column array The list of report columns */ function get_columns() { global $DB; //add custom fields here, first the Course name, then custom fields, then progress and % students passing $columns = array(); $columns[] = new table_report_column('crs.name', get_string('column_course', 'rlreport_course_progress_summary'), 'course', 'left', true); $filter_params = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'field' . $this->get_report_shortname(), $this->filter); $filter_params = $filter_params[0]['value']; $filter_params = $filter_params ? explode(',', $filter_params) : array(); // Loop through these additional parameters - new columns, will have to eventually pass the table etc... if (isset($filter_params) && is_array($filter_params)) { // Working with custom course fields - get all course fields $fields = field::get_for_context_level(CONTEXT_ELIS_COURSE)->to_array(); foreach ($filter_params as $custom_course_id) { $custom_course_field = new field($custom_course_id); // Obtain custom field default values IFF set if (($default_value = $custom_course_field->get_default()) !== false) { // save in array { record_field => default_value } $this->field_default['custom_data_' . $custom_course_id] = $default_value; } //Find matching course field $course_field_title = $fields[$custom_course_id]->name; //Now, create a join statement for each custom course field and add it to the sql query $data_table = $custom_course_field->data_table(); //field used to identify course id in custom field subquery $course_id_field = "ctxt_instanceid_{$custom_course_id}"; //make sure the user can view fields for the current course $view_field_capability = generalized_filter_custom_field_multiselect_values::field_capability($custom_course_field->owners); $view_field_contexts = get_contexts_by_capability_for_user('course', $view_field_capability, $this->userid); //$view_field_filter = $view_field_contexts->sql_filter_for_context_level('ctxt.instanceid', 'course'); $filter_obj = $view_field_contexts->get_filter('ctxt.instanceid', 'course'); $filter_sql = $filter_obj->get_sql(false, 'ctxt', SQL_PARAMS_NAMED); $view_field_filter = 'TRUE'; $params = array(); if (isset($filter_sql['where'])) { $view_field_filter = $filter_sql['where']; $params = $filter_sql['where_parameters']; } // Create a custom join to be used later for the completed sql query $this->custom_joins[] = array(" LEFT JOIN (SELECT d.data as custom_data_{$custom_course_id}, ctxt.instanceid as ctxt_instanceid_{$custom_course_id}\n FROM {context} ctxt\n JOIN {" . $data_table . "} d ON d.contextid = ctxt.id\n AND d.fieldid = {$custom_course_id}\n WHERE\n ctxt.contextlevel = " . CONTEXT_ELIS_COURSE . "\n AND {$view_field_filter}) custom_{$custom_course_id}\n ON cls.courseid = custom_{$custom_course_id}.{$course_id_field}", $params); $columns[] = new table_report_column('custom_' . $custom_course_id . '.custom_data_' . $custom_course_id, $fields[$custom_course_id]->name, 'custom_course_field', 'left'); } } //add progress bar and students passing $columns[] = new table_report_horizontal_bar_column('COUNT(DISTINCT clsgr.id) AS stucompletedprogress', get_string('bar_column_progress', 'rlreport_course_progress_summary'), 'progress_bar', 'COUNT(DISTINCT ' . $DB->sql_concat('comp.id', "'_'", 'enrol.id') . ') AS numprogress', 'center', '$e'); $columns[] = new table_report_column('SUM(CASE WHEN enrol.completestatusid = 2 THEN 1 ELSE 0 END) AS studentspassing', get_string('column_percent_passing', 'rlreport_course_progress_summary'), 'percent_passing', 'left'); return $columns; }
/** * Gets the custom field data for a specified context and field. If a * field value is not set, the default value will be given. */ static function get_for_context_and_field($context, $field) { global $CURMAN; if (is_string($field)) { $field = addslashes($field); $field = new field("shortname = '{$field}'\n AND id IN (SELECT fctx.fieldid\n FROM {$CURMAN->db->prefix_table(FIELDCONTEXTTABLE)} fctx\n WHERE fctx.contextlevel = {$context->contextlevel})"); } $result = NULL; if ($context) { $result = $CURMAN->db->get_records_select($field->data_table(), "contextid = {$context->id} AND fieldid = {$field->id}"); } if (empty($result)) { $result = $CURMAN->db->get_records_select($field->data_table(), "contextid IS NULL AND fieldid = {$field->id}"); } return $result; }
/** * Set up local tracking of whether or not a custom field is multivalued. * * @param int $fieldid The id of the appropriate ELIS custom user field * @param int $multivalued 1 if the field is multivalued, otherwise 0 * * @return int The multivalue status flag, as calculated and stored for the provided field */ protected function init_multivalue_status_for_field($fieldid, $multivalued) { global $CFG, $DB; require_once $CFG->dirroot . '/local/eliscore/lib/setup.php'; require_once elis::lib('data/customfield.class.php'); if (isset($this->customfield_multivaluestatus[$fieldid])) { return $this->customfield_multivaluestatus[$fieldid]; } // Determine if multi-valued data exists for this custom field, whether the field currently supports it or not. $field = new field($fieldid); $data_table = $field->data_table(); $sql = "SELECT 'x'\n FROM {" . $data_table . "} data1\n WHERE EXISTS (\n SELECT 'x'\n FROM {" . $data_table . "} data2\n WHERE data1.contextid = data2.contextid\n AND data1.contextid IS NOT NULL\n AND data1.fieldid = data2.fieldid\n AND data1.id != data2.id\n AND data1.fieldid = ?\n )"; $params = array($fieldid); $multivalue_data_exists = $DB->record_exists_sql($sql, $params); if ($multivalue_data_exists) { // One or more contexts have multiple values assigned for this field. if ($multivalued) { // Field currently supports multi-values. $this->customfield_multivaluestatus[$fieldid] = static::MULTIVALUE_ENABLED; } else { // Field no longer supports multi-values. $this->customfield_multivaluestatus[$fieldid] = static::MULTIVALUE_HISTORICAL; } } else { // Basic single value case. $this->customfield_multivaluestatus[$fieldid] = static::MULTIVALUE_NONE; } return $this->customfield_multivaluestatus[$fieldid]; }
/** * Sets the custom field data for a specified context and field. * * @param object $context the context to set the data for * @param field $field the field object to set the data for * @param mixed $data a single value or an array depending on whether * $field is multivalued or not * @param string $plugin * @return boolean whether or not the data was modified */ public static function set_for_context_and_field($context, field $field, $data) { global $DB; $data = $field->cast_data($data); // ELIS-3829 if ($context) { $contextid = $context->id; } else { $contextid = null; } $data_table = $field->data_table(); // FIXME: check exclude, unique, etc if ($field->multivalued) { if (!is_array($data)) { $data = array($data); } // find what data already exists (excluding default value if we have a context, including if we don't) $include_default = is_null($contextid) ? true : false; $records = self::get_for_context_and_field($context, $field, $include_default); $records = $records ? $records : array(); $todelete = array(); $existing = array(); foreach ($records as $rec) { $val = $field->cast_to_type($rec->data); if (in_array($val, $data)) { $existing[] = $val; } else { $todelete[] = $rec; } } // delete obsolete data foreach ($todelete as $rec) { $rec->delete(); } // add new data $toadd = array_diff($data, $existing); foreach ($toadd as $value) { $fielddatatype = "field_data_{$field->data_type()}"; $rec = new $fielddatatype(); $rec->contextid = $contextid; $rec->fieldid = $field->id; $rec->data = $value; $rec->save(); } return !empty($toadd) || !empty($todelete); } else { if ($rec = $DB->get_record($data_table, array('contextid' => $contextid, 'fieldid' => $field->id))) { // $fielddata = new field_data($rec, $field->data_type()); $fielddatatype = "field_data_{$field->data_type()}"; $fielddata = new $fielddatatype($rec); if ($data === null) { $fielddata->delete(); return true; } if ($fielddata->data == $data) { return false; } $fielddata->contextid = $contextid; // needed, or else NULL becomes 0 $fielddata->data = $data; $fielddata->save(); return true; } else { if ($data !== null) { $fielddatatype = "field_data_{$field->data_type()}"; $rec = new $fielddatatype(); $rec->contextid = $contextid; $rec->fieldid = $field->id; $rec->data = $data; $rec->save(); return true; } } } }
/** * Method that specifies the report's columns * (specifies various fields involving user info, clusters, class enrolment, and module information) * * @return table_report_column array The list of report columns */ function get_columns() { global $CURMAN, $SESSION; //add custom fields here, first the Course name, then custom fields, then progress and % students passing $columns = array(); $columns[] = new table_report_column('crs.name', get_string('column_course', 'rlreport_course_progress_summary'), 'course', 'left', true); $filter_params = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'field' . $this->get_report_shortname(), $this->filter); // Unserialize value of filter params to get field ids array $filter_params = @unserialize(base64_decode($filter_params[0]['value'])); // Loop through these additional parameters - new columns, will have to eventually pass the table etc... if (isset($filter_params) && is_array($filter_params)) { // Working with custom course fields - get all course fields $context = context_level_base::get_custom_context_level('course', 'block_curr_admin'); $fields = field::get_for_context_level($context); foreach ($filter_params as $custom_course_id) { $custom_course_field = new field($custom_course_id); // Obtain custom field default values IFF set if (($default_value = $custom_course_field->get_default()) !== false) { // save in array { record_field => default_value } $this->field_default['custom_data_' . $custom_course_id] = $default_value; } //Find matching course field $course_field_title = $fields[$custom_course_id]->name; //Now, create a join statement for each custom course field and add it to the sql query $data_table = $CURMAN->db->prefix_table($custom_course_field->data_table()); //field used to identify course id in custom field subquery $course_id_field = "ctxt_instanceid_{$custom_course_id}"; //make sure the user can view fields for the current course $view_field_capability = block_php_report_field_capability($custom_course_field->owners); $view_field_contexts = get_contexts_by_capability_for_user('course', $view_field_capability, $this->userid); $view_field_filter = $view_field_contexts->sql_filter_for_context_level('ctxt.instanceid', 'course'); // Create a custom join to be used later for the completed sql query $this->custom_joins[] = " LEFT JOIN (SELECT d.data as custom_data_{$custom_course_id}, ctxt.instanceid as ctxt_instanceid_{$custom_course_id}\n FROM {$CURMAN->db->prefix_table('context')} ctxt\n JOIN {$data_table} d ON d.contextid = ctxt.id\n AND d.fieldid = {$custom_course_id}\n WHERE\n ctxt.contextlevel = {$context}\n AND {$view_field_filter}) custom_{$custom_course_id}\n ON cls.courseid = custom_{$custom_course_id}.{$course_id_field}"; $columns[] = new table_report_column('custom_' . $custom_course_id . '.custom_data_' . $custom_course_id, $fields[$custom_course_id]->name, 'custom_course_field', 'left'); } } //add progress bar and students passing $columns[] = new table_report_horizontal_bar_column('COUNT(DISTINCT clsgr.id) AS stucompletedprogress', get_string('bar_column_progress', 'rlreport_course_progress_summary'), 'progress_bar', 'COUNT(DISTINCT ' . sql_concat('comp.id', "'_'", 'enrol.id') . ') AS numprogress', 'center', '$e'); $columns[] = new table_report_column('SUM(CASE WHEN enrol.completestatusid=2 THEN 1 ELSE 0 END) AS studentspassing', get_string('column_percent_passing', 'rlreport_course_progress_summary'), 'percent_passing', 'left'); return $columns; }