function get_header_entries()
{
$header_array = array();
$show_after = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'showdr_sck', $this->filter);
$date_range_after = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'showdr_sdt', $this->filter);
$show_before = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'showdr_eck', $this->filter);
$date_range_before = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'showdr_edt', $this->filter);
// Add "after dates if available
if (!empty($show_after[0]['value'])) {
$after = !empty($date_range_after[0]['value']) ? $this->userdate($date_range_after[0]['value'], get_string('strftimedaydate')) : get_string('anytime', $this->lang_file);
} else {
$after = get_string('anytime', $this->lang_file);
}
// Add "after dates if available
if (!empty($show_before[0]['value'])) {
$before = !empty($date_range_before[0]['value']) ? $this->userdate($date_range_before[0]['value'], get_string('strftimedaydate')) : get_string('anytime', $this->lang_file);
} else {
$before = get_string('anytime', $this->lang_file);
}
// If both dates are the same, only show one
if ($before == $after) {
$before = '';
$splitter = '';
} else {
$splitter = ' - ';
}
$header_obj = new stdClass();
$header_obj->label = get_string('header_date_range', $this->lang_file) . ': ';
$header_obj->value = $after . $splitter . $before;
$header_obj->css_identifier = '';
$header_array[] = $header_obj;
return $header_array;
}
/**
* Constructs an appropriate order by clause for the main query
*
* @return string The appropriate order by clause
*/
function get_order_by_clause()
{
//always want to start by sorting on user info
$result = " ORDER BY lastname, firstname, userid";
//determine whether we're showing the curriculum name
$filters = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'filter-summarycolumns', $this->filter);
$cols = $filters[0]['value'];
if ($cols['cur_name']) {
//also sort on curriculum name if displayed
$result .= ", curid IS NULL, name";
}
return $result;
}
/**
* Retrieves start and end settings from active filter (if exists)
* and populates class properties: startdate and enddate
*
* @uses none
* @param none
* @return none
*/
function get_filter_values()
{
$start_enabled = php_report_filtering_get_active_filter_values($this->get_report_shortname(), nonstarter_report::datefilterid . '_sck', $this->filter);
$start = 0;
if (!empty($start_enabled) && is_array($start_enabled) && !empty($start_enabled[0]['value'])) {
$start = php_report_filtering_get_active_filter_values($this->get_report_shortname(), nonstarter_report::datefilterid . '_sdt', $this->filter);
}
$end_enabled = php_report_filtering_get_active_filter_values($this->get_report_shortname(), nonstarter_report::datefilterid . '_eck', $this->filter);
$end = 0;
if (!empty($end_enabled) && is_array($end_enabled) && !empty($end_enabled[0]['value'])) {
$end = php_report_filtering_get_active_filter_values($this->get_report_shortname(), nonstarter_report::datefilterid . '_edt', $this->filter);
}
$this->startdate = !empty($start) && is_array($start) ? $start[0]['value'] : 0;
$this->enddate = !empty($end) && is_array($end) ? $end[0]['value'] : 0;
//$this->err_dump($start, '$datefilter(2)_sdt');
//$this->err_dump($end, '$datefilter(2)_edt');
//error_log("nonstarter::get_filter_values() ... startdate={$this->startdate} enddate={$this->enddate}");
// Fetch filter values for the user profile filter
$this->up_headers = $this->upfilter->get_set_filter_values($this->get_report_shortname(), $this->filter, $this->_fields);
}
/**
* Retrieves start and end settings from active filter (if exists)
* and populates class properties: startdate and enddate
*
* @uses none
* @param none
* @return none
*/
function get_filter_date_values()
{
$start_enabled = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'enrol' . '_sck');
$start = !empty($start_enabled) && is_array($start_enabled) && !empty($start_enabled[0]['value']) ? php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'enrol' . '_sdt') : 0;
$end_enabled = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'enrol' . '_eck');
$end = !empty($end_enabled) && is_array($end_enabled) && !empty($end_enabled[0]['value']) ? php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'enrol' . '_edt') : 0;
$this->startdate = !empty($start) && is_array($start) ? $start[0]['value'] : 0;
$this->enddate = !empty($end) && is_array($end) ? $end[0]['value'] : 0;
}
/**
* Get Date Filter Values
* Retrieves start and end settings from active filter (if exists) and return: startdate and enddate.
*
* @param string $reportshortname Shortname of the report.
* @param object $filter The filter object.
* @param string $uniqueid The unqiue filter name
* @return string|bool Either the start/end date string value, or false if dates are empty.
*/
public function get_date_filter_values($reportshortname, $filter, $uniqueid)
{
$startenabled = php_report_filtering_get_active_filter_values($reportshortname, $uniqueid . '_sck', $filter);
$start = 0;
if (!empty($startenabled) && is_array($startenabled) && !empty($startenabled[0]['value'])) {
$start = php_report_filtering_get_active_filter_values($reportshortname, $uniqueid . '_sdt', $filter);
}
$endenabled = php_report_filtering_get_active_filter_values($reportshortname, $uniqueid . '_eck', $filter);
$end = 0;
if (!empty($endenabled) && is_array($endenabled) && !empty($endenabled[0]['value'])) {
$end = php_report_filtering_get_active_filter_values($reportshortname, $uniqueid . '_edt', $filter);
}
$startdate = !empty($start) && is_array($start) ? $start[0]['value'] : 0;
$enddate = !empty($end) && is_array($end) ? $end[0]['value'] : 0;
$sdate = userdate($startdate, get_string('date_format', $this->languagefile));
$edate = !empty($enddate) ? userdate($enddate, get_string('date_format', $this->languagefile)) : get_string('present', $this->languagefile);
if (empty($startdate) && empty($enddate)) {
// Don't return a value if neither date is selected.
return false;
} else {
return "{$sdate} - {$edate}";
}
}
function get_header_entries($export_format)
{
global $CFG, $USER;
$header_array = array();
// Add a course/class name if available
$classid = 0;
$cls_setting = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'classid', $this->filter);
$cmclass = null;
if (!empty($cls_setting[0]['value'])) {
$classid = $cls_setting[0]['value'];
$cmclass = new pmclass($classid);
// Course name
$header_obj = new stdClass();
$header_obj->label = get_string('header_course', $this->lang_file) . ':';
$header_obj->value = $cmclass->course->name;
$header_obj->css_identifier = '';
$header_array[] = $header_obj;
// Class name
$header_obj = new stdClass();
$header_obj->label = get_string('header_class', $this->lang_file) . ':';
$header_obj->value = $cmclass->idnumber;
$header_obj->css_identifier = '';
$header_array[] = $header_obj;
}
// If we are displaying a class, show date range and instructors
if (!empty($classid)) {
// Add dates if available
if (!empty($cmclass)) {
$cmclass = $cmclass->to_object();
// TBD: no date data w/o?!?!
//error_log("class_roster::get_header_entries() dates: {$startdate} ~ {$enddate}");
// Add start date if available
if (!empty($cmclass->startdate)) {
$header_obj = new stdClass();
$header_obj->label = get_string('header_start_date', $this->lang_file) . ':';
$header_obj->value = $this->pmclassdate($cmclass, 'start');
$header_obj->css_identifier = '';
$header_array[] = $header_obj;
}
// Add end date if available
if (!empty($cmclass->enddate)) {
$header_obj = new stdClass();
$header_obj->label = get_string('header_end_date', $this->lang_file) . ':';
$header_obj->value = $this->pmclassdate($cmclass, 'end');
$header_obj->css_identifier = '';
$header_array[] = $header_obj;
}
}
// Add instructor names
$instructor = new instructor(array('userid' => $USER->id, 'classid' => $classid));
$instructor_records = $instructor->get_instructors($classid);
if (!empty($instructor_records)) {
$instructors = '';
foreach ($instructor_records as $record) {
$userpage = new userpage(array('id' => $record->id, 'action' => 'view'));
$instructors .= '<span class="external_report_link"><a href="' . $userpage->url . '">' . php_report::fullname($record) . '</a></span><br />';
}
$header_obj = new stdClass();
$header_obj->label = get_string('header_instructors', $this->lang_file) . ':';
$header_obj->value = $instructors == '' ? 'Not Available' : $instructors;
$header_obj->css_identifier = '';
$header_array[] = $header_obj;
}
}
return $header_array;
}
/**
* Specifies an SQL statement that will retrieve users and their cluster assignment info, class enrolments,
* and resource info
*
* @param array $columns The list of columns automatically calculated
* by get_select_columns()
* @return string The report's main sql statement
*/
function get_report_sql($columns)
{
global $CFG, $CURMAN, $USER;
$cm_user_id = cm_get_crlmuserid($USER->id);
$filter_array = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'userid', $this->filter);
$filter_user_id = isset($filter_array[0]['value']) ? $filter_array[0]['value'] : 0;
if ($filter_user_id == $cm_user_id && $this->execution_mode == php_report::EXECUTION_MODE_INTERACTIVE) {
// always allow the user to see their own report but not necessarily schedule it
$permissions_filter = 'TRUE';
} else {
// obtain all course contexts where this user can view reports
$contexts = get_contexts_by_capability_for_user('user', $this->access_capability, $this->userid);
$permissions_filter = $contexts->sql_filter_for_context_level('usr.id', 'user');
}
// Figure out the number of completed credits for the curriculum
$numcomplete_subquery = "SELECT sum(innerclsenr.credits)\n FROM {$CURMAN->db->prefix_table(STUTABLE)} innerclsenr\n JOIN {$CURMAN->db->prefix_table(CLSTABLE)} innercls ON innercls.id = innerclsenr.classid\n JOIN {$CURMAN->db->prefix_table(CRSTABLE)} innercrs ON innercls.courseid = innercrs.id\n JOIN {$CURMAN->db->prefix_table(CURCRSTABLE)} innercurcrs\n ON innercurcrs.courseid = innercrs.id\n WHERE innerclsenr.userid = usr.id\n AND innercurcrs.curriculumid = cur.id\n AND innerclsenr.completestatusid = " . STUSTATUS_PASSED . "\n ";
// Main query
$sql = "SELECT {$columns},\n cur.id IS NULL AS isnull,\n crs.name AS course_name,\n clsenr.credits AS credits,\n clsenr.grade AS grade,\n clsenr.completetime AS date_completed,\n clsenr.completestatusid AS status,\n curass.timeexpired AS expires,\n usr.firstname AS firstname,\n usr.lastname AS lastname,\n cur.reqcredits AS reqcnt,\n ({$numcomplete_subquery}) AS acqcnt\n FROM {$CURMAN->db->prefix_table(CRSTABLE)} crs\n JOIN {$CURMAN->db->prefix_table(CLSTABLE)} cls\n ON cls.courseid=crs.id\n JOIN {$CURMAN->db->prefix_table(STUTABLE)} clsenr\n ON clsenr.classid=cls.id\n JOIN {$CURMAN->db->prefix_table(USRTABLE)} usr\n ON usr.id = clsenr.userid\n LEFT JOIN ({$CURMAN->db->prefix_table(CURASSTABLE)} curass\n JOIN {$CURMAN->db->prefix_table(CURTABLE)} cur\n ON cur.id = curass.curriculumid\n JOIN {$CURMAN->db->prefix_table(CURCRSTABLE)} curcrs\n ON curcrs.curriculumid = cur.id)\n ON curass.userid = usr.id\n AND curcrs.courseid = crs.id\n LEFT JOIN {$CURMAN->db->prefix_table(CRSCOMPTABLE)} crscomp\n ON crscomp.courseid = crs.id\n LEFT JOIN {$CURMAN->db->prefix_table(GRDTABLE)} grd\n ON grd.classid = cls.id\n AND grd.userid = usr.id\n AND grd.completionid = crscomp.id\n AND grd.locked = 1\n WHERE {$permissions_filter}\n ";
return $sql;
}
/**
* Overload method to initialize report groupings
* used here to also init report data!
*
* @return parent::initialize_groupings();
*/
function initialize_groupings()
{
$show_array = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'sccts', $this->filter);
if (isset($show_array[0]['value'])) {
$this->show_time_spent = $show_array[0]['value'] == 1 ? true : false;
}
$show_array = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'sccg', $this->filter);
if (isset($show_array[0]['value'])) {
$this->show_total_grade = $show_array[0]['value'] == 1 ? true : false;
}
return parent::initialize_groupings();
}
/**
* Constructs an appropriate order by clause for the main query
*
* @return string The appropriate order by clause
*/
function get_order_by_clause()
{
//determine whether we're showing the curriculum name
$filters = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'filter-detailcolumns', $this->filter);
$cols = $filters[0]['value'];
if ($cols['cur_name']) {
//showing curriuclumname, so sort by curriculum, course, class
return ' ORDER BY curid IS NULL, curriculumname, coursename, classidnumber';
}
//not showing curriculum name, so just sort by course, class
return ' ORDER BY coursename, classidnumber';
}
/**
* Constructs an appropriate order by clause for the main query
*
* @return string The appropriate order by clause
*/
function get_order_by_clause()
{
// always want to start by sorting on user info
$result = array();
foreach ($this->get_custom_instance_fields() as $key => $val) {
list($entity, $_as, $dbvar) = explode(" ", $val);
if ($entity == 'user') {
$result[] = 'lastname';
$result[] = 'firstname';
}
$result[] = $dbvar;
}
// determine whether we're showing the curriculum name
$filters = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'filter-summarycolumns', $this->filter);
$cols = $filters[0]['value'];
if ($cols['cur_name']) {
// also sort on curriculum name if displayed
$result[] = "curid IS NULL";
$result[] = "name";
}
return ' ORDER BY ' . implode(', ', $result);
}
/**
* Specifies an SQL statement that will retrieve users and their cluster assignment info, class enrolments,
* and resource info
*
* @param array $columns The list of columns automatically calculated
* by get_select_columns()
* @return array The report's main sql statement with optional params
*/
function get_report_sql($columns)
{
global $CFG, $USER;
$params = array();
$permissions_filter = 'TRUE';
$cm_user_id = cm_get_crlmuserid($USER->id);
$filter_array = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'filterautoc', $this->filter);
$filter_user_id = isset($filter_array[0]['value']) ? $filter_array[0]['value'] : -1;
// ELIS-4699: so not == to invalid cm/pm userid
// ELIS-3993 -- Do not display any results if no user ID was supplied by the filter
if ($filter_user_id == -1) {
$permissions_filter = ' FALSE';
} else {
if ($filter_user_id != $cm_user_id || $this->execution_mode != php_report::EXECUTION_MODE_INTERACTIVE) {
// obtain all course contexts where this user can view reports
$contexts = get_contexts_by_capability_for_user('user', $this->access_capability, $this->userid);
//$permissions_filter = $contexts->sql_filter_for_context_level('crlmuser.id', 'user');
$filter_obj = $contexts->get_filter('id', 'user');
$filter_sql = $filter_obj->get_sql(false, 'crlmuser', SQL_PARAMS_NAMED);
if (isset($filter_sql['where'])) {
if ($filter_sql['where'] == 'FALSE') {
// This user does not have permission to view the requested data
$this->nopermission = true;
$permissions_filter = ' FALSE';
} else {
$permissions_filter = $filter_sql['where'];
$params = $filter_sql['where_parameters'];
}
}
}
}
//tracks progress used by this user
$total_progress_subquery = 'SELECT cls.id AS classid, stu.userid as userid, COUNT(*) AS numprogress, COUNT(clsgr.id) AS stucomplete
FROM {' . coursecompletion::TABLE . '} comp
JOIN {' . pmclass::TABLE . '} cls
ON cls.courseid = comp.courseid
LEFT JOIN {' . student::TABLE . '} stu
ON stu.classid = cls.id
LEFT JOIN {' . student_grade::TABLE . '} clsgr
ON clsgr.classid = cls.id
AND clsgr.userid = stu.userid
AND clsgr.locked = 1
AND clsgr.grade >= comp.completion_grade
AND clsgr.completionid = comp.id
GROUP BY cls.id, stu.userid
';
if ($this->preposttest_columns) {
//gets the pretest score for this user
$pretest_query = $this->get_max_test_score_sql('_elis_course_pretest');
//gets the posttest score for this user
$posttest_query = $this->get_max_test_score_sql('_elis_course_posttest');
}
//main query
$sql = "SELECT {$columns}, crs.id AS courseid,\n cls.starttimehour AS starttimehour,\n cls.starttimeminute AS starttimeminute,\n cls.endtimehour AS endtimehour,\n cls.endtimeminute AS endtimeminute,\n cls.id AS classid,\n crlmuser.id AS userid, crlmuser.firstname, crlmuser.lastname\n FROM {" . pmclass::TABLE . '} cls
JOIN {' . student::TABLE . '} enrol
ON enrol.classid = cls.id
JOIN {' . user::TABLE . '} crlmuser
ON crlmuser.id = enrol.userid
JOIN {user} user
ON user.idnumber = crlmuser.idnumber
LEFT JOIN {' . classmoodlecourse::TABLE . '} clsmdl
ON clsmdl.classid = cls.id
LEFT JOIN {' . course::TABLE . '} crs
ON crs.id = cls.courseid ';
if ($this->preposttest_columns) {
$sql .= "\n LEFT JOIN ({$pretest_query}) pretest\n ON pretest.classid = cls.id\n AND pretest.userid = crlmuser.id\n LEFT JOIN ({$posttest_query}) posttest\n ON posttest.classid = cls.id\n AND posttest.userid = crlmuser.id\n ";
}
// add custom field joins if they exist
if (!empty($this->custom_joins)) {
foreach ($this->custom_joins as $custom_join) {
$sql .= $custom_join[0];
$params += $custom_join[1];
}
}
$sql .= "\n WHERE {$permissions_filter}";
return array($sql, $params);
}
/**
* Specifies header summary data
* representing curricula, date range, cluster and number of courses in report
*
* @uses $DB
* @param $export_format The desired export format for the headers
* @return array A mapping of display names to values
*/
function get_header_entries($export_format)
{
global $DB;
//need to get start_date and end_date from report interface
$cluster_display = '';
$curricula_display = '';
$course_count = $this->numrecs;
if ($selected_cluster = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'cluster', $this->filter)) {
$count = 0;
// Check for NOT - cluster_op == 2
$cluster_op = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'cluster_op', $this->filter);
if ($cluster_op['0']['value'] == '2') {
$cluster_display .= get_string('header_not', 'rlreport_course_progress_summary');
}
foreach ($selected_cluster as $cluster) {
if ($cluster_new = $DB->get_record(userset::TABLE, array('id' => $cluster['value']))) {
if ($count > 0) {
$cluster_display .= ' AND ';
}
$count++;
if (!empty($cluster_new->display)) {
$cluster_display .= $cluster_new->display;
} else {
$cluster_display .= $cluster_new->name;
}
}
}
} else {
$cluster_display = get_string('header_all', 'rlreport_course_progress_summary');
}
if ($selected_curricula = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'curr', $this->filter)) {
$count = 0;
foreach ($selected_curricula as $curricula) {
if ($curricula['value'] == '0') {
$curricula_display = get_string('header_all_assigned', 'rlreport_course_progress_summary');
} else {
if ($curricula['value'] == 'null') {
$curricula_display = get_string('filter_non_program', 'rlreport_course_progress_summary');
} else {
if ($curricula = $DB->get_record(curriculum::TABLE, array('id' => $curricula['value']))) {
if ($count > 0) {
$curricula_display .= ' AND ';
}
$count++;
$curricula_display .= $curricula->name;
}
}
}
}
} else {
$curricula_display = get_string('header_all', 'rlreport_course_progress_summary');
}
// Get date filter parameters req'd for header title
$this->get_datefilter_values();
$sdate = !empty($this->startdate) ? $this->userdate($this->startdate, get_string('date_format', 'rlreport_course_progress_summary')) : get_string('present', 'rlreport_course_progress_summary');
$edate = !empty($this->enddate) ? $this->userdate($this->enddate, get_string('date_format', 'rlreport_course_progress_summary')) : get_string('present', 'rlreport_course_progress_summary');
if (empty($this->startdate) && empty($this->enddate)) {
$date_range_display = get_string('header_all', 'rlreport_course_progress_summary');
} else {
$date_range_display = "{$sdate} - {$edate}";
}
return array(new php_report_header_entry(get_string('header_curricula', 'rlreport_course_progress_summary'), $curricula_display, 'curricula'), new php_report_header_entry(get_string('header_date_range', 'rlreport_course_progress_summary'), $date_range_display, 'date'), new php_report_header_entry(get_string('header_organization', 'rlreport_course_progress_summary'), $cluster_display, 'organization'), new php_report_header_entry(get_string('header_course_count', 'rlreport_course_progress_summary'), $course_count, 'course_count'));
}
/**
* Specifies an SQL statement that will retrieve users and their cluster assignment info, class enrolments,
* and resource info
*
* @param array $columns The list of columns automatically calculated
* by get_select_columns()
* @return array The report's main sql statement with optional params
*/
function get_report_sql($columns)
{
global $USER;
$cm_user_id = cm_get_crlmuserid($USER->id);
$filter_array = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'userid', $this->filter);
// ELIS-4699: so not == to invalid cm/pm userid
$filter_user_id = isset($filter_array[0]['value']) ? $filter_array[0]['value'] : -1;
$params = array('p_completestatus' => STUSTATUS_PASSED);
$permissions_filter = 'TRUE ';
// ELIS-3993 -- Do not display any results if no user ID was supplied by the filter
if ($filter_user_id == -1) {
$permissions_filter = ' FALSE';
} else {
if ($filter_user_id != $cm_user_id || $this->execution_mode != php_report::EXECUTION_MODE_INTERACTIVE) {
// obtain all course contexts where this user can view reports
$contexts = get_contexts_by_capability_for_user('user', $this->access_capability, $this->userid);
$filter_obj = $contexts->get_filter('id', 'user');
$filter_sql = $filter_obj->get_sql(false, 'usr', SQL_PARAMS_NAMED);
if (isset($filter_sql['where'])) {
if ($filter_sql['where'] == 'FALSE') {
// This user does not have permission to view the requested data
$this->nopermission = true;
$permissions_filter = 'FALSE';
} else {
$permissions_filter = $filter_sql['where'];
$params += $filter_sql['where_parameters'];
}
}
}
}
// Figure out the number of completed credits for the curriculum
$numcomplete_subquery = "SELECT sum(innerclsenr.credits)\n FROM {" . student::TABLE . "} innerclsenr\n JOIN {" . pmclass::TABLE . "} innercls ON innercls.id = innerclsenr.classid\n JOIN {" . course::TABLE . "} innercrs ON innercls.courseid = innercrs.id\n JOIN {" . curriculumcourse::TABLE . "} innercurcrs\n ON innercurcrs.courseid = innercrs.id\n WHERE innerclsenr.userid = usr.id\n AND innercurcrs.curriculumid = cur.id\n AND innerclsenr.completestatusid = :p_completestatus";
// Main query
$sql = "SELECT {$columns},\n cur.id IS NULL AS isnull,\n crs.name AS course_name,\n clsenr.credits AS credits,\n clsenr.grade AS grade,\n clsenr.completetime AS date_completed,\n clsenr.completestatusid AS status,\n curass.timeexpired AS expires,\n usr.firstname AS firstname,\n usr.lastname AS lastname,\n cur.reqcredits AS reqcnt,\n ({$numcomplete_subquery}) AS acqcnt\n FROM {" . course::TABLE . "} crs\n JOIN {" . pmclass::TABLE . "} cls\n ON cls.courseid=crs.id\n JOIN {" . student::TABLE . "} clsenr\n ON clsenr.classid=cls.id\n JOIN {" . user::TABLE . "} usr\n ON usr.id = clsenr.userid\n LEFT JOIN ({" . curriculumstudent::TABLE . "} curass\n JOIN {" . curriculum::TABLE . "} cur\n ON cur.id = curass.curriculumid\n JOIN {" . curriculumcourse::TABLE . "} curcrs\n ON curcrs.curriculumid = cur.id)\n ON curass.userid = usr.id\n AND curcrs.courseid = crs.id\n LEFT JOIN {" . coursecompletion::TABLE . "} crscomp\n ON crscomp.courseid = crs.id\n LEFT JOIN {" . GRDTABLE . "} grd\n ON grd.classid = cls.id\n AND grd.userid = usr.id\n AND grd.completionid = crscomp.id\n AND grd.locked = 1\n WHERE {$permissions_filter}\n ";
return array($sql, $params);
}
/**
* Specifies the report title
*
* @param $export_format The desired export format for the headers
* @return array - header entires
*/
function get_header_entries($export_format)
{
$header_objs = array();
if ($export_format == php_report::$EXPORT_FORMAT_CSV || $export_format == php_report::$EXPORT_FORMAT_EXCEL) {
// Get courseclass filter values for class id
$filter = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'class', $this->filter);
if (!empty($filter) && is_array($filter) && !empty($filter[0]['value'])) {
$pmclass = new pmclass($filter[0]['value']);
$header_obj = new stdClass();
$header_obj->label = get_string('header_course', $this->lang_file);
$class_obj = new stdClass();
$class_obj->name = $pmclass->course->name;
$class_obj->idnumber = $pmclass->course->idnumber;
$header_obj->value = get_string('header_crs_value', $this->lang_file, $class_obj);
$header_obj->css_identifier = '';
$header_objs[] = $header_obj;
$header_obj = new stdClass();
$header_obj->label = get_string('header_class', $this->lang_file);
$header_obj->value = $pmclass->idnumber;
$header_obj->css_identifier = '';
$header_objs[] = $header_obj;
}
}
return $header_objs;
}
/**
* Specifies an SQL statement that will retrieve users and their cluster assignment info, class enrolments,
* and resource info
*
* @param array $columns The list of columns automatically calculated
* by get_select_columns()
* @return string The report's main sql statement
*/
function get_report_sql($columns)
{
global $CFG, $CURMAN, $USER;
require_once CURMAN_DIRLOCATION . '/lib/student.class.php';
$incomplete_status = STUSTATUS_NOTCOMPLETE;
$cm_user_id = cm_get_crlmuserid($USER->id);
$filter_array = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'userid', $this->filter);
$filter_user_id = isset($filter_array[0]['value']) ? $filter_array[0]['value'] : 0;
if ($filter_user_id == $cm_user_id && $this->execution_mode == php_report::EXECUTION_MODE_INTERACTIVE) {
// always allow the user to see their own report but not necessarily schedule it
$permissions_filter = 'TRUE';
} else {
// obtain all course contexts where this user can view reports
$contexts = get_contexts_by_capability_for_user('user', $this->access_capability, $this->userid);
$permissions_filter = $contexts->sql_filter_for_context_level('crlmuser.id', 'user');
}
//tracks progress used by this user
$total_progress_subquery = "SELECT cls.id AS classid, stu.userid as userid, COUNT(*) AS numprogress, COUNT(clsgr.id) AS stucomplete\n FROM {$CURMAN->db->prefix_table(CRSCOMPTABLE)} comp\n JOIN {$CURMAN->db->prefix_table(CLSTABLE)} cls\n ON cls.courseid = comp.courseid\n LEFT JOIN {$CURMAN->db->prefix_table(STUTABLE)} stu\n ON stu.classid = cls.id\n LEFT JOIN {$CURMAN->db->prefix_table(GRDTABLE)} clsgr\n ON clsgr.classid = cls.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 GROUP BY cls.id, stu.userid\n ";
//gets the pretest score for this user
$pretest_query = $this->get_max_test_score_sql('_elis_course_pretest');
//gets the posttest score for this user
$posttest_query = $this->get_max_test_score_sql('_elis_course_posttest');
//main query
$sql = "SELECT {$columns},\n crs.id AS courseid,\n cls.starttimehour AS starttimehour,\n cls.starttimeminute AS starttimeminute,\n cls.endtimehour AS endtimehour,\n cls.endtimeminute AS endtimeminute\n FROM {$CURMAN->db->prefix_table(CLSTABLE)} cls\n JOIN {$CURMAN->db->prefix_table(STUTABLE)} enrol\n ON enrol.classid = cls.id\n JOIN {$CURMAN->db->prefix_table(USRTABLE)} crlmuser\n ON crlmuser.id = enrol.userid\n JOIN {$CFG->prefix}user user\n ON user.idnumber = crlmuser.idnumber\n JOIN {$CURMAN->db->prefix_table(CLSMOODLETABLE)} clsmdl\n ON clsmdl.classid = cls.id\n LEFT JOIN {$CURMAN->db->prefix_table(CRSTABLE)} crs\n ON crs.id = cls.courseid\n LEFT JOIN ({$pretest_query}) pretest\n ON pretest.classid = cls.id\n AND pretest.userid = crlmuser.id\n LEFT JOIN ({$posttest_query}) posttest\n ON posttest.classid = cls.id\n AND posttest.userid = crlmuser.id";
// add custom field joins if they exist
if (isset($this->custom_joins) && is_array($this->custom_joins)) {
foreach ($this->custom_joins as $custom_join) {
$sql .= $custom_join;
}
}
$sql .= " WHERE {$permissions_filter}";
return $sql;
}
/**
* Method that specifies fields to group the results by (header displayed when these fields change)
*
* @uses $DB
* @return array List of objects containing grouping id, field names, display labels and sort order
*/
function get_grouping_fields()
{
global $DB;
//field that is used to compare one record from the next
$compare_field = $DB->sql_concat('user.lastname', "'_'", 'user.firstname', "'_'", 'user.id');
//field used to order for groupings
$order_field = $DB->sql_concat('lastname', "'_'", 'firstname', "'_'", 'userid');
$cluster_label = get_string('grouping_cluster', 'rlreport_course_completion_by_cluster');
$cluster_grouping = new table_report_grouping('cluster', 'cluster.id', $cluster_label, 'ASC', array('cluster.name'), 'above', 'path');
$user_grouping_fields = array('user.idnumber AS useridnumber', 'user.firstname');
$user_grouping = new table_report_grouping('groupuseridnumber', $compare_field, '', 'ASC', $user_grouping_fields, 'below', $order_field);
//these groupings will always be used
$result = array($cluster_grouping, $user_grouping);
//determine whether or not we should use the curriculum grouping
$preferences = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'columns_curriculum', $this->filter);
$show_curriculum = true;
if (isset($preferences['0']['value'])) {
$show_curriculum = $preferences['0']['value'];
}
if ($show_curriculum) {
$curriculum_label = get_string('grouping_curriculum', 'rlreport_course_completion_by_cluster');
$result[] = new table_report_grouping('groupcurriculumid', 'curriculum.id', $curriculum_label, 'ASC', array(), 'below', 'curriculumname, curriculumid');
}
return $result;
}
/**
* Specifies header summary data
* representing curricula, date range, cluster and number of courses in report
*
* @return array A mapping of display names to values
*/
function get_header_entries()
{
//need to get start_date and end_date from report interface
$cluster_display = '';
$curricula_display = '';
$course_count = $this->numrecs;
if ($selected_cluster = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'cluster')) {
$count = 0;
foreach ($selected_cluster as $cluster) {
if ($cluster_new = get_record(CLSTTABLE, 'id', $cluster['value'])) {
if ($count > 0) {
$cluster_display .= ' AND ';
}
$count++;
if (!empty($cluster_new->display)) {
$cluster_display .= $cluster_new->display;
} else {
$cluster_display .= $cluster_new->name;
}
}
}
} else {
$cluster_display = get_string('header_all', 'rlreport_course_progress_summary');
}
if ($selected_curricula = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'curr')) {
//print_object($selected_curricula);
$count = 0;
foreach ($selected_curricula as $curricula) {
if ($curricula = get_record('crlm_curriculum', 'id', $curricula['value'])) {
if ($count > 0) {
$curricula_display .= ' AND ';
}
$count++;
$curricula_display .= $curricula->name;
}
}
} else {
$curricula_display = get_string('header_all', 'rlreport_course_progress_summary');
}
// Get date filter parameters req'd for header title
$sdate = !empty($this->startdate) ? $this->userdate($this->startdate, get_string('date_format', 'rlreport_resource_usage')) : get_string('present', 'rlreport_course_progress_summary');
$edate = !empty($this->enddate) ? $this->userdate($this->enddate, get_string('date_format', 'rlreport_resource_usage')) : get_string('present', 'rlreport_course_progress_summary');
if (empty($this->startdate) && empty($this->enddate)) {
$date_range_display = get_string('header_all', 'rlreport_course_progress_summary');
} else {
$date_range_display = "{$sdate} - {$edate}";
}
$header_entries = array();
$curricula_header = $header_entries[] = new php_report_header_entry(get_string('header_curricula', 'rlreport_course_progress_summary'), $curricula_display, 'curricula');
$header_entries[] = new php_report_header_entry(get_string('header_date_range', 'rlreport_course_progress_summary'), $date_range_display, 'date_range');
$header_entries[] = new php_report_header_entry(get_string('header_organization', 'rlreport_course_progress_summary'), $cluster_display, 'organization');
$header_entries[] = new php_report_header_entry(get_string('header_course_count', 'rlreport_course_progress_summary'), $course_count, 'course_count');
return $header_entries;
}
/**
* Retrieves start and end settings from active filter (if exists)
* and populates class properties: startdate and enddate
*
* @uses none
* @param none
* @return none
*/
function get_filter_values()
{
$start_enabled = php_report_filtering_get_active_filter_values($this->get_report_shortname(), sitewide_time_summary_report::datefilterid . '_sck', $this->filter);
$start = 0;
if (!empty($start_enabled) && is_array($start_enabled) && !empty($start_enabled[0]['value'])) {
$start = php_report_filtering_get_active_filter_values($this->get_report_shortname(), sitewide_time_summary_report::datefilterid . '_sdt', $this->filter);
}
$end_enabled = php_report_filtering_get_active_filter_values($this->get_report_shortname(), sitewide_time_summary_report::datefilterid . '_eck', $this->filter);
$end = 0;
if (!empty($end_enabled) && is_array($end_enabled) && !empty($end_enabled[0]['value'])) {
$end = php_report_filtering_get_active_filter_values($this->get_report_shortname(), sitewide_time_summary_report::datefilterid . '_edt', $this->filter);
}
$this->startdate = !empty($start) && is_array($start) ? $start[0]['value'] : 0;
$this->enddate = !empty($end) && is_array($end) ? $end[0]['value'] : 0;
//$this->err_dump($datefilter, 'get_filter_values(); $datefilter');
//error_log("sitewide_time_summary::get_filter_values() ... startdate={$this->startdate} enddate={$this->enddate}");
// Get segment filter settings
$segfilter = php_report_filtering_get_active_filter_values($this->get_report_shortname(), sitewide_time_summary_report::segfilterid, $this->filter);
//$this->err_dump($segfilter, '$segfilter');
$this->segment = sitewide_time_summary_report::defaultseg;
if (!empty($segfilter) && is_array($segfilter)) {
$this->segment = $segfilter[0]['value'];
}
}
function get_header_entries()
{
global $CFG, $CURMAN;
$header_array = array();
// Add a course/class name if available
$classid = 0;
$cls_setting = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'classid', $this->filter);
if (!empty($cls_setting[0]['value'])) {
$classid = $cls_setting[0]['value'];
$cmclass = new cmclass($classid);
// Course name
$header_obj = new stdClass();
$header_obj->label = get_string('header_course', $this->lang_file) . ':';
$header_obj->value = $cmclass->course->name;
$header_obj->css_identifier = '';
$header_array[] = $header_obj;
// Class name
$header_obj = new stdClass();
$header_obj->label = get_string('header_class', $this->lang_file) . ':';
$header_obj->value = $cmclass->idnumber;
$header_obj->css_identifier = '';
$header_array[] = $header_obj;
}
// If we are displaying a class, show date range and instructors
if (!empty($classid)) {
// Add dates if available
if (!empty($cmclass)) {
$startdate = $cmclass->startdate;
$enddate = $cmclass->enddate;
// Add start date if available
if (!empty($startdate)) {
$header_obj = new stdClass();
$header_obj->label = get_string('header_start_date', $this->lang_file) . ':';
$header_obj->value = $this->userdate($startdate, get_string('strftimedaydate'));
$header_obj->css_identifier = '';
$header_array[] = $header_obj;
}
// Add end date if available
if (!empty($enddate)) {
$header_obj = new stdClass();
$header_obj->label = get_string('header_end_date', $this->lang_file) . ':';
$header_obj->value = $this->userdate($enddate, get_string('strftimedaydate'));
$header_obj->css_identifier = '';
$header_array[] = $header_obj;
}
}
// Add instructor names
$instructor_records = instructor::get_instructors($classid);
if (!empty($instructor_records)) {
$instructors = '';
foreach ($instructor_records as $record) {
$userpage = new usermanagementpage(array('id' => $record->id, 'action' => 'view'));
$instructors .= '<span class="external_report_link"><a href="' . $userpage->get_url() . '">' . fullname($record) . '</a></span><br />';
}
$header_obj = new stdClass();
$header_obj->label = get_string('header_instructors', $this->lang_file) . ':';
$header_obj->value = $instructors == '' ? 'Not Available' : $instructors;
$header_obj->css_identifier = '';
$header_array[] = $header_obj;
}
}
return $header_array;
}
