function reports_template_exercicesMultiCourses_getSQL()
{
// foreach quiz
$result = array();
$columns = Database::query('select r.id as kid, c.title as course, ' . 'r.child_name as test from ' . Database::get_main_table(TABLE_MAIN_REPORTS_KEYS) . ' r, ' . Database::get_main_table(TABLE_MAIN_COURSE) . ' c ' . 'where r.course_id=c.id and r.tool_id=' . reports_getToolId(TOOL_QUIZ) . ' order by r.course_id, r.child_name');
if (Database::num_rows($columns) == 0) {
die('<b>' . get_lang('no data found') . '</b>');
}
$query = 'select u.lastname Name, u.firstname Firstname';
$columns = Database::store_result($columns);
if ($_REQUEST['tattempt'] == 'min' || $_REQUEST['tattempt'] == 'max') {
$function = $_REQUEST['tattempt'];
} else {
$function = 'avg';
}
foreach ($columns as $key => $column) {
$query .= ', ' . $function . '(k' . $key . '.score) as `' . $column['course'] . ' - ' . $column['test'] . '` ';
}
$query .= ' from ' . Database::get_main_table(TABLE_MAIN_USER) . ' u ';
foreach ($columns as $key => $column) {
// fixme sessions
$query .= 'left outer join ' . Database::get_main_table(TABLE_MAIN_REPORTS_VALUES) . ' k' . $key . ' on k' . $key . '.key_id = ' . $column['kid'] . ' and k' . $key . '.user_id = u.user_id ';
}
$query .= ' group by ';
foreach ($columns as $key => $column) {
// grouping attempt
$query .= 'k' . $key . '.attempt, ';
}
$query = substr($query, 0, -2);
// removing last ', ';
return $query;
}
function reports_modules_scorm_init_forEachCourses($course_code, $course_id, $course_db)
{
global $reports_modules;
$reports_modules_scorm_toolid = reports_getToolId(TOOL_LEARNPATH);
// package level
array_push($reports_modules['scorm'], array('keys_query' => 'select ' . $course_id . ' as course_id, ' . $reports_modules_scorm_toolid . ' as tool_id, ' . 'lp.id as child_id, lp.name as child_name, ' . "'" . $course_db . "'" . ' as course_db from ' . Database::get_course_table(TABLE_LP_MAIN) . ' lp', 'values_query_function' => 'reports_modules_scorm_packageVal'));
// sco level
array_push($reports_modules['scorm'], array('keys_query' => 'select ' . $course_id . ' as course_id, ' . $reports_modules_scorm_toolid . ' as tool_id, ' . 'lp.id as child_id, lp.name as child_name, ' . 'lpi.id as subchild_id, lpi.title as subchild_name, ' . "'" . $course_db . "'" . ' as course_db from ' . Database::get_course_table(TABLE_LP_MAIN, $course_db) . ' lp,' . Database::get_course_table(TABLE_LP_ITEM, $course_db) . ' lpi where lp.id = lpi.lp_id', 'values_query_function' => 'reports_modules_scorm_scoVal'));
// objectives level
array_push($reports_modules['scorm'], array('keys_query' => 'select distinct ' . $course_id . ' as course_id, ' . $reports_modules_scorm_toolid . ' as tool_id, ' . 'lp.id as child_id, lp.name as child_name, ' . 'lpi.id as subchild_id, ' . 'lpi.title as subchild_name, ' . 'null as subsubchild_id, ' . 'lpivo.objective_id as subsubchild_name, ' . "'" . $course_db . "'" . ' as course_db from ' . Database::get_course_table(TABLE_LP_MAIN, $course_db) . ' lp,' . Database::get_course_table(TABLE_LP_ITEM, $course_db) . ' lpi, ' . Database::get_course_table(TABLE_LP_ITEM_VIEW, $course_db) . ' lpiv, ' . Database::get_course_table(TABLE_LP_IV_OBJECTIVE, $course_db) . ' lpivo ' . 'where lp.id = lpi.lp_id ' . 'and lpi.id = lpiv.lp_item_id ' . 'and lpiv.id = lpivo.lp_iv_id ', 'values_query_function' => 'reports_modules_scorm_objVal'));
}
function reports_modules_quiz_init_forEachCourses($course_code, $course_id, $course_db) {
global $reports_modules;
$reports_modules_quiz_toolid = reports_getToolId(TOOL_QUIZ);
array_push($reports_modules['quiz'],
array('keys_query' =>
'select '.$course_id.' as course_id, '.
$reports_modules_quiz_toolid.' as tool_id, '.
'q.id as child_id, q.title as child_name, '.
"'".$course_code."'".' as course_code from '.Database::get_course_table(TABLE_QUIZ_TEST).' q ',
'values_query_function' => 'reports_modules_quiz_quizVal'));
}
