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