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