reports.lib.php 8.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216
  1. <?php
  2. require_once '../inc/global.inc.php';
  3. define ('REPORTS_PROGRESS_COMPLETED', 1);
  4. $reports_modules = array();
  5. $reports_enabled_modules = array('quiz','course','scorm');
  6. $reports_enabled_templates = array('exercicesMultiCourses', 'courseTime');
  7. // load templates
  8. function reports_loadTemplates() {
  9. global $reports_enabled_templates, $reports_template;
  10. foreach ($reports_enabled_templates as $t)
  11. require_once 'templates/'.$t.'.reports.php';
  12. }
  13. // clear all reporting data
  14. function reports_clearAll() {
  15. /*
  16. Database::query('DELETE FROM '.Database::get_main_table(TABLE_MAIN_REPORTS_KEYS));
  17. Database::query('DELETE FROM '.Database::get_main_table(TABLE_MAIN_REPORTS_VALUES));
  18. */
  19. Database::query('DROP TABLE '.Database::get_main_table(TABLE_MAIN_REPORTS_KEYS));
  20. Database::query('DROP TABLE '.Database::get_main_table(TABLE_MAIN_REPORTS_VALUES));
  21. Database::query('
  22. CREATE TABLE '.Database::get_main_table(TABLE_MAIN_REPORTS_KEYS).' (
  23. id int unsigned NOT NULL AUTO_INCREMENT primary key,
  24. course_id int unsigned DEFAULT NULL,
  25. tool_id int unsigned DEFAULT NULL,
  26. child_id int unsigned DEFAULT NULL,
  27. child_name varchar(64) DEFAULT NULL,
  28. subchild_id int unsigned DEFAULT NULL,
  29. subchild_name varchar(64) DEFAULT NULL,
  30. subsubchild_id int unsigned DEFAULT NULL,
  31. subsubchild_name varchar(64) DEFAULT NULL,
  32. link varchar(256) DEFAULT NULL)');
  33. Database::query('
  34. CREATE TABLE '.Database::get_main_table(TABLE_MAIN_REPORTS_VALUES).' (
  35. key_id int unsigned NOT NULL,
  36. user_id int unsigned NOT NULL,
  37. session_id int unsigned NOT NULL,
  38. attempt int NOT NULL,
  39. score decimal(5,3) DEFAULT NULL,
  40. progress int DEFAULT NULL,
  41. report_time int DEFAULT NULL,
  42. ts datetime DEFAULT NULL)');
  43. }
  44. function reports_addDBKeys() {
  45. // cannot had this primary key here due to mysql restrction on auto_increment
  46. // Database::query('alter table '.Database::get_main_table(TABLE_MAIN_REPORTS_KEYS).
  47. // ' primary key(id)');
  48. Database::query('alter ignore table '.
  49. Database::get_main_table(TABLE_MAIN_REPORTS_KEYS).
  50. ' add index(course_id)');
  51. Database::query('alter table '.
  52. Database::get_main_table(TABLE_MAIN_REPORTS_KEYS).
  53. ' add index(course_id,tool_id,child_id,subchild_id,subsubchild_id)');
  54. Database::query('alter ignore table '.
  55. Database::get_main_table(TABLE_MAIN_REPORTS_VALUES).
  56. ' add index(user_id)');
  57. Database::query('alter ignore table '.
  58. Database::get_main_table(TABLE_MAIN_REPORTS_VALUES).
  59. ' add primary key(key_id,user_id,session_id,attempt)');
  60. }
  61. // build all reporting data
  62. function reports_build() {
  63. global $reports_enabled_modules, $reports_modules;
  64. // include + init
  65. foreach($reports_enabled_modules as $module) {
  66. require_once('modules/'.$module.'.php');
  67. $initFunc = 'reports_modules_'.$module.'_init';
  68. $initFunc();
  69. }
  70. // init For Each Courses
  71. foreach($reports_enabled_modules as $module) {
  72. $initFuncFEC = 'reports_modules_'.$module.'_init_forEachCourses';
  73. foreach(CourseManager::get_courses_list() as $course)
  74. $initFuncFEC($course['code'], $course['id'],
  75. $course['db_name']);
  76. }
  77. // fetch data
  78. foreach($reports_enabled_modules as $module)
  79. foreach ($reports_modules[$module] as $keys)
  80. reports_automaticAdd($keys['keys_query'],
  81. $keys['values_query_function']);
  82. }
  83. // add a key and returns his id
  84. // field are not checked for insertion since this function is for internal
  85. // use only
  86. function reports_addKey($course_id, $tool_id,
  87. $child_id, $child_name,
  88. $subchild_id, $subchild_name,
  89. $subsubchild_id, $subsubchild_name,
  90. $link) {
  91. Database::query('INSERT into '.
  92. Database::get_main_table(TABLE_MAIN_REPORTS_KEYS).
  93. ' (id, course_id, tool_id, child_id, child_name, '.
  94. 'subchild_id, subchild_name, subsubchild_id, subsubchild_name,'.
  95. 'link ) values (null, '.
  96. ($course_id == '' ? 'NULL' :$course_id).', '.
  97. ($tool_id == '' ? 'NULL' :$tool_id).', '.
  98. ($child_id == '' ? 'NULL' :$child_id).', '.
  99. ($child_name == '' ? 'NULL' :"'$child_name'").', '.
  100. ($subchild_id == '' ? 'NULL' :$subchild_id).', '.
  101. ($subchild_name == '' ? 'NULL' : "'$subchild_name'").', '.
  102. ($subsubchild_id == '' ? 'NULL' : $subsubchild_id).', '.
  103. ($subsubchild_name == '' ? 'NULL' : "'$subsubchild_name'").', '.
  104. ($link == '' ? 'NULL' : "'$link'").')');
  105. return Database::insert_id();
  106. }
  107. // add a value
  108. function reports_addValue($key, $session, $uid, $attempt, $score,
  109. $progress, $time, $ts) {
  110. Database::query('INSERT into '.
  111. Database::get_main_table(TABLE_MAIN_REPORTS_VALUES).
  112. ' (key_id, user_id, session_id, attempt, score, '.
  113. 'progress, report_time) values ('.$key.', '.
  114. $uid.', '.
  115. // -1 instead of null because of primary key limitation with null column
  116. ($session == '' ? '-1' : $session).', '.
  117. ($attempt == '' ? '-1' : $attempt).', '.
  118. ($score == '' ? 'NULL' : $score).', '.
  119. ($progress == '' ? 'NULL' : $progress).', '.
  120. ($time == '' ? 'NULL' : $time).', '.
  121. ($ts == '' ? 'NULL' : $ts).')');
  122. }
  123. // add a value using a sub query warning take care about the order of the fields
  124. function reports_addValueQuery($query) {
  125. Database::query('INSERT into '.
  126. Database::get_main_table(TABLE_MAIN_REPORTS_VALUES).
  127. ' (key_id, user_id, session_id, attempt, score, '.
  128. 'progress, report_time, ts) ('.$query.')');
  129. }
  130. // return tools ID (parametre is a constant from main_api
  131. function reports_getToolId($tool) {
  132. $tools = array_flip(api_get_tools_lists());
  133. if (array_key_exists($tool, $tools))
  134. return $tools[$tool];
  135. else
  136. return null;
  137. }
  138. // return a sql clause returning triplet of (course, $session, $uid) the
  139. // current user is authorized to reed
  140. function reports_getVisibilitySQL () {
  141. return "select cru.user_id, c.id, null from course c, course_rel_user cru where cru.course_code = c.code";
  142. // fixme sessions
  143. }
  144. // this function execute keys_query (SQL statement)
  145. // each rows may returns following fields course_id, tool_id, child_id,
  146. // child_name, subchild_id, subchild_name, subsubchild_id, subsubchild_name,
  147. // link
  148. // row may contains other fields.
  149. // rows are parsed using fetch_assoc
  150. // assoc array are then given to values_query_function
  151. // this function must return a assoc array
  152. // return["type"] should be either 'static' or 'sql'
  153. // return["static"] should contains an array of assoc array which may
  154. // includes following headers: session, user_id, attempt, score progress and
  155. // time.
  156. // return["sql"] (when type==sql) an sql query returning the same fields.
  157. // this sql stateuement MUST include a field key_id with the value given
  158. // to the function as parametre. This statement will be passed to
  159. // reports_addValueQuery
  160. function reports_automaticAdd($keys_query, $values_query_function) {
  161. $keys_result = Database::query($keys_query);
  162. if (!$keys_query) {
  163. echo 'folowwing keys_query failed: '.$keys_query."\n";
  164. return;
  165. }
  166. $num = Database::num_rows($keys_result);
  167. for ($i = 0; $i < $num; $i++) {
  168. $keys = Database::fetch_assoc($keys_result);
  169. $key_id = reports_addKey(
  170. array_key_exists('course_id', $keys) ? $keys['course_id'] : '',
  171. array_key_exists('tool_id', $keys) ? $keys['tool_id'] : '',
  172. array_key_exists('child_id', $keys) ? $keys['child_id'] : '',
  173. array_key_exists('child_name', $keys) ? $keys['child_name'] : '',
  174. array_key_exists('subchild_id', $keys) ? $keys['subchild_id'] : '',
  175. array_key_exists('subchild_name', $keys) ? $keys['subchild_name'] : '',
  176. array_key_exists('subsubchild_id', $keys) ? $keys['subsubchild_id'] : '',
  177. array_key_exists('subsubchild_name', $keys) ? $keys['subsubchild_name'] : '',
  178. array_key_exists('link', $keys) ? $keys['link'] : '');
  179. $values = $values_query_function($keys, $key_id);
  180. if ($values['type'] == 'static')
  181. for ($j = 0; $j<sizeof($values['static']); $j++)
  182. reports_addValue($key_id,
  183. array_key_exists('session', $values['static'][$j]) ? $values['static'][$j]['session'] : '',
  184. array_key_exists('user_id', $values['static'][$j]) ? $values['static'][$j]['user_id'] : '',
  185. array_key_exists('attempt', $values['static'][$j]) ? $values['static'][$j]['attempt'] : '',
  186. array_key_exists('score', $values['static'][$j]) ? $values['static'][$j]['score'] : '',
  187. array_key_exists('progress', $values['static'][$j]) ? $values['static'][$j]['progress'] : '',
  188. array_key_exists('report_time', $values['static'][$j]) ? $values['static'][$j]['report_time'] : '',
  189. array_key_exists('ts', $values['static'][$j]) ? $values['static'][$j]['ts'] : '');
  190. else
  191. reports_addValueQuery($values['sql']);
  192. }
  193. }