/**
* Test generalized_filter_custom_field_multiselect_values.
*/
public function test_customfieldmultiselectvalues()
{
global $CFG;
if (!file_exists($CFG->dirroot . '/local/elisreports/php_report_base.php')) {
$this->markTestSkipped('Test requires local_elisreports code');
}
$this->load_csv_data();
$uniqid = 'test_CustomFieldMultiselectValues';
$alias = 'test_CustomFieldMultiselectValues';
$name = 'test_CustomFieldMultiselectValues';
$label = 'test_CustomFieldMultiselectValues';
$adv = false;
$field = null;
$opts = array('block_instance' => '');
$filter = new test_generalized_filter_custom_field_multiselect_values($uniqid, $alias, $name, $label, $adv, $field, $opts);
$frm = new test_moodleform_filterstest();
$mform = $frm->get_mform();
// Test context in setupForm.
$filter->setupForm($mform);
$elements = $frm->get_elements();
$this->assertNotEmpty($elements);
$multiselectfound = false;
foreach ($elements as $ele) {
if (is_a($ele, 'elis_custom_field_multiselect')) {
$this->assertNotEmpty($ele->_options['contextlevel']);
$multiselectfound = true;
}
}
$this->assertNotEmpty($multiselectfound);
// Test context in get_label.
$filter->get_label(array('value' => '100'));
// Text context in check_for_custom_fields().
$filter = new generalized_filter_custom_field_multiselect_values($uniqid, $alias, $name, $label, $adv, $field, $opts);
$filter->check_for_custom_fields('course');
}
/**
* 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;
$columns = array();
$columns[] = new table_report_column('crs.name', get_string('column_course', $this->lang_file), 'csscourse', 'left', true);
$columns[] = new table_report_column('cls.idnumber', get_string('column_class_id', $this->lang_file), 'cssclass', '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('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("\n 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\n ON d.contextid = ctxt.id AND d.fieldid = {$custom_course_id}\n WHERE 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, 'csscustom_course_field', 'left', true);
}
}
// completion elements completed/total
$columns[] = new table_report_horizontal_bar_column('(SELECT COUNT(*) FROM {' . coursecompletion::TABLE . '} comp
JOIN {' . pmclass::TABLE . '} cls2
ON cls2.courseid = comp.courseid
JOIN {' . student::TABLE . '} stu
ON stu.classid = cls2.id
JOIN {' . student_grade::TABLE . '} clsgr
ON clsgr.classid = cls2.id
AND clsgr.userid = stu.userid
AND clsgr.locked = 1
AND clsgr.grade >= comp.completion_grade
AND clsgr.completionid = comp.id
WHERE cls2.id = cls.id
AND stu.userid = crlmuser.id
) AS stucompletedprogress', get_string('bar_column_progress', $this->lang_file), 'progress_bar', '(SELECT COUNT(*) FROM {' . coursecompletion::TABLE . '} comp
JOIN {' . pmclass::TABLE . '} cls2
ON cls2.courseid = comp.courseid
WHERE cls2.id = cls.id
) AS numprogress', 'center', '$p');
$columns[] = new table_report_column('0 AS completedprogress', get_string('column_progress', $this->lang_file), 'cssprogress', 'center', true);
$columns[] = new table_report_column('cls.startdate', get_string('column_start_date', $this->lang_file), 'cssstart_date', 'center', true);
$columns[] = new table_report_column('cls.enddate', get_string('column_end_date', $this->lang_file), 'cssend_date', 'center', true);
$optional_columns_ppt = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'optional_columns_preposttest', $this->filter);
$optional_columns_los = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'optional_columns_los', $this->filter);
$optional_columns_totscore = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'optional_columns_totalscore', $this->filter);
$this->preposttest_columns = false;
$this->los_columns = false;
$this->totalscore_column = false;
if (!empty($optional_columns_ppt) && !empty($optional_columns_ppt['0']['value'])) {
$columns[] = new table_report_column('pretest.score AS pretestscore', get_string('column_pretest_score', $this->lang_file), 'csspretest_score', 'center', true);
$columns[] = new table_report_column('posttest.score AS posttestscore', get_string('column_posttest_score', $this->lang_file), 'cssposttest_score', 'center', true);
$this->preposttest_columns = true;
}
if (!empty($optional_columns_los) && !empty($optional_columns_los['0']['value'])) {
$max_los_sql = 'SELECT courseid, COUNT(\'x\') AS count FROM {' . coursecompletion::TABLE . '} GROUP BY courseid ORDER BY count DESC';
$max_los = $DB->get_records_sql($max_los_sql, null, 0, 1);
$max_los = empty($max_los) ? 0 : current($max_los)->count;
for ($i = 1; $i <= $max_los; ++$i) {
$columns[] = new table_report_column("'" . addslashes(get_string('na', $this->lang_file)) . "' AS lo{$i}", get_string('column_los_prefix', $this->lang_file) . "{$i}", 'csslos_columns', 'center', false);
}
$this->los_columns = true;
}
if (!empty($optional_columns_totscore) && !empty($optional_columns_totscore['0']['value'])) {
$columns[] = new table_report_column('enrol.grade AS elisgrade', get_string('column_totalscore', $this->lang_file), 'csstotal_score', 'center', true);
$this->totalscore_column = true;
}
// discussion posts
$columns[] = new table_report_column('(SELECT COUNT(*) FROM {forum_discussions} disc
JOIN {forum_posts} post
ON post.discussion = disc.id
WHERE disc.course = clsmdl.moodlecourseid
AND post.userid = user.id
) AS numposts', get_string('column_discussion_posts', $this->lang_file), 'cssdiscussion_posts', 'center', true);
//create an IN clause identifying modules that are considered resources
//todo: use get_in_or_equal
$modules = $this->get_resource_modules();
$in = "IN ('" . implode("', '", $modules) . "')";
// resources accessed
$columns[] = new table_report_column("(SELECT COUNT(*) FROM {log} log\n WHERE log.module {$in}\n AND log.action = 'view'\n AND log.userid = user.id\n AND log.course = clsmdl.moodlecourseid\n ) AS numresources", get_string('column_resources_accessed', $this->lang_file), 'cssresources_accessed', 'center', true);
return $columns;
}
$multi_filter->update_field_list($action, $fieldid, $fieldname, $fieldidlist, $fieldnamelist, $scheduled);
// Set custom field id and name arrays to the updated id and name lists
$fieldidlist = $multi_filter->_fieldidlist;
$fieldnamelist = $multi_filter->_fieldnamelist;
// We need to serialize fieldidlist and fieldnamelist and fieldname for all form elements
$serialized_fieldidlist = '';
$serialized_fieldnamelist = '';
$serialized_fieldname = '';
if (isset($fieldidlist) && !empty($fieldidlist)) {
// Reindex first
$fieldidlist = array_merge($fieldidlist);
// Get custom field names if we have a list of custom ids and no names
if ((!isset($fieldnamelist) || empty($fieldnamelist)) && !($scheduled && $action == 'init')) {
//todo: change this to work in a static context
$options = array('fieldids' => array(), 'block_instance' => array());
$multi_filter_values = new generalized_filter_custom_field_multiselect_values('bogus', 'bogus', 'bogus', 'bogus', false, 'bogus', $options, base64_encode(serialize($fieldidlist)), $fieldnamelist);
$multi_filter_values->get_names();
$fieldidlist = $multi_filter_values->_fieldidlist;
$fieldnamelist = $multi_filter_values->_fieldnamelist;
}
$serialized_fieldidlist = base64_encode(serialize($fieldidlist));
}
//Retrieve fieldname list
if (isset($fieldnamelist)) {
$fieldnamelist = array_merge($fieldnamelist);
$serialized_fieldnamelist = base64_encode(serialize($fieldnamelist));
}
//Retrieve fieldname
if (isset($fieldname)) {
$serialized_fieldname = base64_encode(serialize($fieldname));
}
/**
* 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;
}
