reports.lib.php 8.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217
  1. <?php
  2. require_once __DIR__.'/../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', 'courseArticulate');
  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. /*
  15. function reports_clearAll() {
  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. Database::query('DROP TABLE '.Database::get_main_table(TABLE_MAIN_REPORTS_KEYS));
  19. Database::query('DROP TABLE '.Database::get_main_table(TABLE_MAIN_REPORTS_VALUES));
  20. Database::query('
  21. CREATE TABLE '.Database::get_main_table(TABLE_MAIN_REPORTS_KEYS).' (
  22. id int unsigned NOT NULL AUTO_INCREMENT primary key,
  23. course_id int unsigned DEFAULT NULL,
  24. tool_id int unsigned DEFAULT NULL,
  25. child_id int unsigned DEFAULT NULL,
  26. child_name varchar(64) DEFAULT NULL,
  27. subchild_id int unsigned DEFAULT NULL,
  28. subchild_name varchar(64) DEFAULT NULL,
  29. subsubchild_id int unsigned DEFAULT NULL,
  30. subsubchild_name varchar(64) DEFAULT NULL,
  31. link varchar(256) DEFAULT NULL)');
  32. Database::query('
  33. CREATE TABLE '.Database::get_main_table(TABLE_MAIN_REPORTS_VALUES).' (
  34. key_id int unsigned NOT NULL,
  35. user_id int unsigned NOT NULL,
  36. session_id int unsigned NOT NULL,
  37. attempt int NOT NULL,
  38. score decimal(5,3) DEFAULT NULL,
  39. progress int DEFAULT NULL,
  40. report_time int DEFAULT NULL,
  41. ts datetime DEFAULT NULL)');
  42. }
  43. function reports_addDBKeys() {
  44. // cannot had this primary key here due to mysql restrction on auto_increment
  45. // Database::query('alter table '.Database::get_main_table(TABLE_MAIN_REPORTS_KEYS).
  46. // ' primary key(id)');
  47. Database::query('alter ignore table '.
  48. Database::get_main_table(TABLE_MAIN_REPORTS_KEYS).
  49. ' add index(course_id)');
  50. Database::query('alter table '.
  51. Database::get_main_table(TABLE_MAIN_REPORTS_KEYS).
  52. ' add index(course_id,tool_id,child_id,subchild_id,subsubchild_id)');
  53. Database::query('alter ignore table '.
  54. Database::get_main_table(TABLE_MAIN_REPORTS_VALUES).
  55. ' add index(user_id)');
  56. Database::query('alter ignore table '.
  57. Database::get_main_table(TABLE_MAIN_REPORTS_VALUES).
  58. ' add primary key(key_id,user_id,session_id,attempt)');
  59. }
  60. // build all reporting data
  61. function reports_build() {
  62. global $reports_enabled_modules, $reports_modules;
  63. // include + init
  64. foreach($reports_enabled_modules as $module) {
  65. require_once('modules/'.$module.'.php');
  66. $initFunc = 'reports_modules_'.$module.'_init';
  67. $initFunc();
  68. }
  69. // init For Each Courses
  70. foreach($reports_enabled_modules as $module) {
  71. $initFuncFEC = 'reports_modules_'.$module.'_init_forEachCourses';
  72. foreach(CourseManager::get_courses_list() as $course)
  73. $initFuncFEC($course['code'], $course['id'],
  74. $course['db_name']);
  75. }
  76. // fetch data
  77. foreach($reports_enabled_modules as $module)
  78. foreach ($reports_modules[$module] as $keys)
  79. reports_automaticAdd($keys['keys_query'],
  80. $keys['values_query_function']);
  81. }
  82. // add a key and returns his id
  83. // field are not checked for insertion since this function is for internal
  84. // use only
  85. function reports_addKey($course_id, $tool_id,
  86. $child_id, $child_name,
  87. $subchild_id, $subchild_name,
  88. $subsubchild_id, $subsubchild_name,
  89. $link) {
  90. Database::query('INSERT into '.
  91. Database::get_main_table(TABLE_MAIN_REPORTS_KEYS).
  92. ' (id, course_id, tool_id, child_id, child_name, '.
  93. 'subchild_id, subchild_name, subsubchild_id, subsubchild_name,'.
  94. 'link ) values (null, '.
  95. ($course_id == '' ? 'NULL' :$course_id).', '.
  96. ($tool_id == '' ? 'NULL' :$tool_id).', '.
  97. ($child_id == '' ? 'NULL' :$child_id).', '.
  98. ($child_name == '' ? 'NULL' :"'$child_name'").', '.
  99. ($subchild_id == '' ? 'NULL' :$subchild_id).', '.
  100. ($subchild_name == '' ? 'NULL' : "'$subchild_name'").', '.
  101. ($subsubchild_id == '' ? 'NULL' : $subsubchild_id).', '.
  102. ($subsubchild_name == '' ? 'NULL' : "'$subsubchild_name'").', '.
  103. ($link == '' ? 'NULL' : "'$link'").')');
  104. return Database::insert_id();
  105. }
  106. // add a value
  107. function reports_addValue($key, $session, $uid, $attempt, $score,
  108. $progress, $time, $ts) {
  109. Database::query('INSERT into '.
  110. Database::get_main_table(TABLE_MAIN_REPORTS_VALUES).
  111. ' (key_id, user_id, session_id, attempt, score, '.
  112. 'progress, report_time) values ('.$key.', '.
  113. $uid.', '.
  114. // -1 instead of null because of primary key limitation with null column
  115. ($session == '' ? '-1' : $session).', '.
  116. ($attempt == '' ? '-1' : $attempt).', '.
  117. ($score == '' ? 'NULL' : $score).', '.
  118. ($progress == '' ? 'NULL' : $progress).', '.
  119. ($time == '' ? 'NULL' : $time).', '.
  120. ($ts == '' ? 'NULL' : $ts).')');
  121. }
  122. // add a value using a sub query warning take care about the order of the fields
  123. function reports_addValueQuery($query) {
  124. Database::query('INSERT into '.
  125. Database::get_main_table(TABLE_MAIN_REPORTS_VALUES).
  126. ' (key_id, user_id, session_id, attempt, score, '.
  127. 'progress, report_time, ts) ('.$query.')');
  128. }
  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 from ".Database::get_main_table(TABLE_MAIN_COURSE_USER).' cru';
  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. /*
  161. function reports_automaticAdd($keys_query, $values_query_function) {
  162. $keys_result = Database::query($keys_query);
  163. if (!$keys_query) {
  164. echo 'folowwing keys_query failed: '.$keys_query."\n";
  165. return;
  166. }
  167. $num = Database::num_rows($keys_result);
  168. for ($i = 0; $i < $num; $i++) {
  169. $keys = Database::fetch_assoc($keys_result);
  170. $key_id = reports_addKey(
  171. array_key_exists('course_id', $keys) ? $keys['course_id'] : '',
  172. array_key_exists('tool_id', $keys) ? $keys['tool_id'] : '',
  173. array_key_exists('child_id', $keys) ? $keys['child_id'] : '',
  174. array_key_exists('child_name', $keys) ? $keys['child_name'] : '',
  175. array_key_exists('subchild_id', $keys) ? $keys['subchild_id'] : '',
  176. array_key_exists('subchild_name', $keys) ? $keys['subchild_name'] : '',
  177. array_key_exists('subsubchild_id', $keys) ? $keys['subsubchild_id'] : '',
  178. array_key_exists('subsubchild_name', $keys) ? $keys['subsubchild_name'] : '',
  179. array_key_exists('link', $keys) ? $keys['link'] : '');
  180. $values = $values_query_function($keys, $key_id);
  181. if ($values['type'] == 'static')
  182. for ($j = 0; $j<sizeof($values['static']); $j++)
  183. reports_addValue($key_id,
  184. array_key_exists('session', $values['static'][$j]) ? $values['static'][$j]['session'] : '',
  185. array_key_exists('user_id', $values['static'][$j]) ? $values['static'][$j]['user_id'] : '',
  186. array_key_exists('attempt', $values['static'][$j]) ? $values['static'][$j]['attempt'] : '',
  187. array_key_exists('score', $values['static'][$j]) ? $values['static'][$j]['score'] : '',
  188. array_key_exists('progress', $values['static'][$j]) ? $values['static'][$j]['progress'] : '',
  189. array_key_exists('report_time', $values['static'][$j]) ? $values['static'][$j]['report_time'] : '',
  190. array_key_exists('ts', $values['static'][$j]) ? $values['static'][$j]['ts'] : '');
  191. else
  192. reports_addValueQuery($values['sql']);
  193. }
  194. }*/