courseTime.reports.php 1.6 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647
  1. <?php
  2. /*
  3. $reports_template['courseTime'] = array(
  4. 'description' => 'Time spent by students in each courses',
  5. 'getSQL' => 'reports_template_courseTime_getSQL',
  6. 'wizard' =>
  7. '
  8. <span id="courseTime" class="step">
  9. <span class="font_normal_07em_black">This test does not need any particular settings</span><br />
  10. <input type="hidden" class="link" value="format" />
  11. </span>
  12. ');*/
  13. function reports_template_courseTime_getSQL()
  14. {
  15. // fetch columns
  16. $result = array();
  17. $query = 'select r.id as kid, c.title as course '.
  18. 'from '.
  19. Database::get_main_table(TABLE_MAIN_REPORTS_KEYS).' r, '.
  20. Database::get_main_table(TABLE_MAIN_COURSE).' c '.
  21. 'where r.course_id=c.id and r.tool_id is null and r.child_id is null'.
  22. ' order by c.title';
  23. $columns = Database::query($query);
  24. if (Database::num_rows($columns) == 0) {
  25. die('<b>'.get_lang('no data found: '.$query).'</b>');
  26. }
  27. $columns = Database::store_result($columns);
  28. // fetch data
  29. $query = 'select u.lastname Name, u.firstname Firstname';
  30. foreach ($columns as $key => $column) {
  31. $query .= ', sec_to_time(k'.$key.'.report_time) as `'.
  32. $column['course'].'` ';
  33. }
  34. $query .= ' from '.Database::get_main_table(TABLE_MAIN_USER).' u ';
  35. foreach ($columns as $key => $column) // fixme sessions
  36. {
  37. $query .= 'left outer join '.
  38. Database::get_main_table(TABLE_MAIN_REPORTS_VALUES).
  39. ' k'.$key.
  40. ' on k'.$key.'.key_id = '.$column['kid'].
  41. ' and k'.$key.'.user_id = u.user_id ';
  42. }
  43. return $query;
  44. }