/**
* Specifies an SQL statement that will produce the required report
*
* @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)
{
$param_prefix = 'ccbcr_';
//special version of the select columns used in the non-curriculum case
$noncurriculum_columns = str_replace('curriculum.id', 'NULL', $columns);
//extra column needed for the curriculum-specific records
$extra_curriculum_columns = 'COUNT(course_completion.id) AS numtotal,
enrol.id AS enrolid,
class.id AS classid,
cluster.id AS clusterid,
cluster_context.path AS path,
curriculum.name AS curriculumname,
curriculum.id AS curriculumid,
user.id AS userid,
curriculum_assignment.completed,
enrol.completestatusid AS enrolstatus,
course.name AS coursename,
course.id AS courseid,
enrol.completetime AS enrolcompletetime,
curriculum_assignment.timecompleted AS curriculumcompletetime';
if (empty(elis::$config->local_elisprogram->legacy_show_inactive_users)) {
$inactive = ' AND user.inactive = 0';
} else {
$inactive = '';
}
//extra column needed for the non-curriculum-specific records
$extra_noncurriculum_columns = str_replace('curriculum.id', 'NULL', $extra_curriculum_columns);
$extra_noncurriculum_columns = str_replace('curriculum.name', 'NULL', $extra_noncurriculum_columns);
$params = array();
$param_cluster_context = $param_prefix . 'clust_context';
$params[$param_cluster_context . '1'] = CONTEXT_ELIS_USERSET;
$params[$param_cluster_context . '2'] = CONTEXT_ELIS_USERSET;
//starting point for both cases
$core_tables_fmt = '{' . user::TABLE . '} user
JOIN {' . clusterassignment::TABLE . '} user_cluster
ON user.id = user_cluster.userid
JOIN {' . userset::TABLE . "} cluster\n ON user_cluster.clusterid = cluster.id\n JOIN {context} cluster_context\n ON cluster.id = cluster_context.instanceid\n AND cluster_context.contextlevel = :{$param_cluster_context}%d";
//course completion info used in both cases
$completion_tables = 'LEFT JOIN {' . coursecompletion::TABLE . '} course_completion
ON course.id = course_completion.courseid
LEFT JOIN {' . student_grade::TABLE . '} class_graded
ON class.id = class_graded.classid
AND course_completion.id = class_graded.completionid
AND enrol.userid = class_graded.userid
AND class_graded.locked = 1
AND class_graded.grade >= course_completion.completion_grade';
//filters for each of two cases, put inside the query for performance reasons
//(use parent functionality because we are preventing filter application in the usual way)
$curriculum_filter = parent::get_filter_condition('WHERE');
$noncurriculum_filter = parent::get_filter_condition('AND');
//grouping for each of the two cases
$group_by = "GROUP BY user.id, enrol.id, cluster.id, course.id, curriculum.id";
$noncurriculum_group_by = "GROUP BY user.id, enrol.id, cluster.id, course.id";
// status of clustertree filter, drop-down menu
$usingdd = php_report_filtering_get_active_filter_values($this->get_report_shortname(), 'cluster_usingdropdown', $this->filter);
//check permissions
$permissions_filter = '';
$filter_params = array();
if ($usingdd === false || empty($usingdd[0]['value'])) {
//check permissions ONLY IF they selected dropdown: 'any value'
//TBD: IFF we can disable checkboxes for non-permitted tree clusters
// THEN we can remove the second if condition above:
// || empty($usingdd[0]['value'])
$contexts = get_contexts_by_capability_for_user('cluster', $this->access_capability, $this->userid);
//$permissions_filter = $contexts->sql_filter_for_context_level('clusterid', 'cluster');
$filter_obj = $contexts->get_filter('clusterid', 'cluster');
$filter_sql = $filter_obj->get_sql(false, null, SQL_PARAMS_NAMED);
if (isset($filter_sql['where'])) {
$permissions_filter = 'WHERE ' . $filter_sql['where'];
$filter_params = $filter_sql['where_parameters'];
}
}
$lastname = 'user.lastname';
if (stripos($columns, $lastname) === FALSE) {
$columns .= ", {$lastname}";
}
//the master query
$sql = "SELECT * FROM (\n SELECT DISTINCT {$columns},\n {$extra_curriculum_columns}\n FROM " . sprintf($core_tables_fmt, 1) . '
JOIN {' . curriculumstudent::TABLE . '} curriculum_assignment
ON user.id = curriculum_assignment.userid
JOIN {' . curriculum::TABLE . '} curriculum
ON curriculum_assignment.curriculumid = curriculum.id
JOIN {' . curriculumcourse::TABLE . '} curriculum_course
ON curriculum.id = curriculum_course.curriculumid
JOIN {' . course::TABLE . '} course
ON curriculum_course.courseid = course.id
LEFT JOIN ({' . pmclass::TABLE . '} class
JOIN {' . student::TABLE . "} enrol\n ON class.id = enrol.classid)\n ON curriculum_assignment.userid = enrol.userid\n AND course.id = class.courseid\n {$completion_tables}\n {$curriculum_filter[0]}\n {$inactive}\n {$group_by}\n\n UNION\n\n SELECT DISTINCT {$noncurriculum_columns},\n {$extra_noncurriculum_columns}\n FROM " . sprintf($core_tables_fmt, 2) . '
JOIN {' . student::TABLE . '} enrol
ON user.id = enrol.userid
JOIN {' . pmclass::TABLE . '} class
ON enrol.classid = class.id
JOIN {' . course::TABLE . '} course
ON class.courseid = course.id
LEFT JOIN ({' . curriculumcourse::TABLE . '} curriculum_course
JOIN {' . curriculumstudent::TABLE . "} curriculum_assignment\n ON curriculum_course.curriculumid = curriculum_assignment.curriculumid)\n ON course.id = curriculum_course.courseid\n AND enrol.userid = curriculum_assignment.userid\n {$completion_tables}\n\n WHERE curriculum_assignment.id IS NULL\n {$noncurriculum_filter[0]}\n {$inactive}\n {$noncurriculum_group_by}\n ) main_data\n {$permissions_filter}";
$params = array_merge($params, $curriculum_filter[1], $noncurriculum_filter[1], $filter_params);
return array($sql, $params);
}
/**
* Specifies an SQL statement that will produce the required report
*
* @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 $CURMAN;
$cluster_context_level = context_level_base::get_custom_context_level('cluster', 'block_curr_admin');
//special version of the select columns used in the non-curriculum case
$noncurriculum_columns = str_replace('curriculum.id', 'NULL', $columns);
//extra column needed for the curriculum-specific records
$extra_curriculum_columns = "user.lastname,\n COUNT(course_completion.id) AS numtotal,\n enrol.id AS enrolid,\n class.id AS classid,\n cluster.id AS clusterid,\n cluster_context.path AS path,\n curriculum.name AS curriculumname,\n curriculum.id AS curriculumid,\n user.id AS userid,\n curriculum_assignment.completed,\n enrol.completestatusid AS enrolstatus,\n course.name AS coursename,\n course.id AS courseid,\n enrol.completetime AS enrolcompletetime,\n curriculum_assignment.timecompleted AS curriculumcompletetime";
if (empty($CURMAN->config->legacy_show_inactive_users)) {
$inactive = ' AND user.inactive = 0';
} else {
$inactive = '';
}
//extra column needed for the non-curriculum-specific records
$extra_noncurriculum_columns = str_replace('curriculum.id', 'NULL', $extra_curriculum_columns);
$extra_noncurriculum_columns = str_replace('curriculum.name', 'NULL', $extra_noncurriculum_columns);
//starting point for both cases
$core_tables = "{$CURMAN->db->prefix_table(USRTABLE)} user\n JOIN {$CURMAN->db->prefix_table(CLSTUSERTABLE)} user_cluster\n ON user.id = user_cluster.userid\n JOIN {$CURMAN->db->prefix_table(CLSTTABLE)} cluster\n ON user_cluster.clusterid = cluster.id\n JOIN {$CURMAN->db->prefix_table('context')} cluster_context\n ON cluster.id = cluster_context.instanceid\n AND cluster_context.contextlevel = {$cluster_context_level}";
//course completion info used in both cases
$completion_tables = "LEFT JOIN {$CURMAN->db->prefix_table(CRSCOMPTABLE)} course_completion\n ON course.id = course_completion.courseid\n LEFT JOIN {$CURMAN->db->prefix_table(GRDTABLE)} class_graded\n ON class.id = class_graded.classid\n AND course_completion.id = class_graded.completionid\n AND enrol.userid = class_graded.userid\n AND class_graded.locked = 1\n AND class_graded.grade >= course_completion.completion_grade";
//filters for each of two cases, put inside the query for performance reasons
//(use parent functionality because we are preventing filter application in the usual way)
$curriculum_filter = parent::get_filter_condition('WHERE');
$noncurriculum_filter = parent::get_filter_condition('AND');
//grouping for each of the two cases
$group_by = "GROUP BY user.id, enrol.id, cluster.id, course.id, curriculum.id";
$noncurriculum_group_by = "GROUP BY user.id, enrol.id, cluster.id, course.id";
//check permissions
$contexts = get_contexts_by_capability_for_user('cluster', $this->access_capability, $this->userid);
$permissions_filter = $contexts->sql_filter_for_context_level('clusterid', 'cluster');
//the master query
$sql = "SELECT * FROM\n (\n SELECT {$columns},\n {$extra_curriculum_columns}\n FROM\n {$core_tables}\n\n JOIN {$CURMAN->db->prefix_table(CURASSTABLE)} curriculum_assignment\n ON user.id = curriculum_assignment.userid\n JOIN {$CURMAN->db->prefix_table(CURTABLE)} curriculum\n ON curriculum_assignment.curriculumid = curriculum.id\n JOIN {$CURMAN->db->prefix_table(CURCRSTABLE)} curriculum_course\n ON curriculum.id = curriculum_course.curriculumid\n JOIN {$CURMAN->db->prefix_table(CRSTABLE)} course\n ON curriculum_course.courseid = course.id\n LEFT JOIN ({$CURMAN->db->prefix_table(CLSTABLE)} class\n JOIN {$CURMAN->db->prefix_table(STUTABLE)} enrol\n ON class.id = enrol.classid)\n ON curriculum_assignment.userid = enrol.userid\n AND course.id = class.courseid\n\n {$completion_tables}\n\n {$curriculum_filter}\n {$inactive}\n\n {$group_by}\n\n UNION\n\n SELECT {$noncurriculum_columns},\n {$extra_noncurriculum_columns}\n FROM\n {$core_tables}\n\n JOIN {$CURMAN->db->prefix_table(STUTABLE)} enrol\n ON user.id = enrol.userid\n JOIN {$CURMAN->db->prefix_table(CLSTABLE)} class\n ON enrol.classid = class.id\n JOIN {$CURMAN->db->prefix_table(CRSTABLE)} course\n ON class.courseid = course.id\n LEFT JOIN ({$CURMAN->db->prefix_table(CURCRSTABLE)} curriculum_course\n JOIN {$CURMAN->db->prefix_table(CURASSTABLE)} curriculum_assignment\n ON curriculum_course.curriculumid = curriculum_assignment.curriculumid)\n ON course.id = curriculum_course.courseid\n AND enrol.userid = curriculum_assignment.userid\n\n {$completion_tables}\n\n WHERE curriculum_assignment.id IS NULL\n {$noncurriculum_filter}\n {$inactive}\n\n {$noncurriculum_group_by}\n ) main_data\n WHERE {$permissions_filter}";
return $sql;
}
