'Result of each test per student',
'getSQL' => 'reports_template_exercicesMultiCourses_getSQL',
'wizard' =>
'
Result of each test per student
');
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(''.get_lang('no data found').'');
$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;
}