/**
* 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, $CFG;
$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());
// 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, 'csscustom_course_field', 'left', true);
}
}
// completion elements completed/total
$columns[] = new table_report_horizontal_bar_column("(SELECT COUNT(*)\n FROM {$CURMAN->db->prefix_table(CRSCOMPTABLE)} comp\n JOIN {$CURMAN->db->prefix_table(CLSTABLE)} cls2\n ON cls2.courseid = comp.courseid\n JOIN {$CURMAN->db->prefix_table(STUTABLE)} stu\n ON stu.classid = cls2.id\n JOIN {$CURMAN->db->prefix_table(GRDTABLE)} clsgr\n ON clsgr.classid = cls2.id\n AND clsgr.userid = stu.userid\n AND clsgr.locked = 1\n AND clsgr.grade >= comp.completion_grade\n AND clsgr.completionid = comp.id\n WHERE cls2.id = cls.id\n AND stu.userid = crlmuser.id\n ) AS stucompletedprogress", get_string('bar_column_progress', $this->lang_file), 'progress_bar', "(SELECT COUNT(*)\n FROM {$CURMAN->db->prefix_table(CRSCOMPTABLE)} comp\n JOIN {$CURMAN->db->prefix_table(CLSTABLE)} cls2\n ON cls2.courseid = comp.courseid\n WHERE cls2.id = cls.id\n ) 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);
$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);
// discussion posts
$columns[] = new table_report_column("(SELECT COUNT(*)\n FROM {$CFG->prefix}forum_discussions disc\n JOIN {$CFG->prefix}forum_posts post\n ON post.discussion = disc.id\n WHERE disc.course = clsmdl.moodlecourseid\n AND post.userid = user.id\n ) AS numposts", get_string('column_discussion_posts', $this->lang_file), 'cssdiscussion_posts', 'center', true);
// resources accessed
$columns[] = new table_report_column("(SELECT COUNT(*)\n FROM {$CFG->prefix}log log\n JOIN {$CFG->prefix}resource rsc\n ON rsc.id = log.info\n WHERE log.module = 'resource'\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;
}
/**
* Specifies whether a course-level custom field is accessible to the
* current user in at least once course context
*
* @param array $owners shortname-indexed collection of all field owners
*
* @return boolean true if accessible, otherwise false
*/
function block_php_report_field_accessible($owners)
{
global $USER, $CFG;
require_once $CFG->dirroot . '/curriculum/lib/contexts.php';
if ($view_capability = block_php_report_field_capability($owners)) {
//make sure the user has the view capability in some course
$contexts = get_contexts_by_capability_for_user('course', $view_capability, $USER->id);
return !$contexts->is_empty();
} else {
//data error
return false;
}
}
/**
* 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;
}
