12345678910111213141516171819202122232425262728293031323334353637383940414243444546 |
- <?php
- function reports_template_courseTime_getSQL()
- {
-
- $result = array();
- $query = 'select r.id as kid, c.title as course ' .
- '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 is null and r.child_id is null' .
- ' order by c.title';
- $columns = Database::query($query);
- if (Database::num_rows($columns) == 0) {
- die('<b>' . get_lang('no data found: ' . $query) . '</b>');
- }
- $columns = Database::store_result($columns);
-
- $query = 'select u.lastname Name, u.firstname Firstname';
- foreach ($columns as $key => $column) {
- $query .= ', sec_to_time(k' . $key . '.report_time) as `' .
- $column['course'] . '` ';
- }
- $query .= ' from ' . Database::get_main_table(TABLE_MAIN_USER) . ' u ';
- foreach ($columns as $key => $column)
- {
- $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 ';
- }
- return $query;
- }
|