model.ajax.php 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. //@todo this could be integrated in the inc/lib/model.lib.php + try to clean this file
  4. $language_file = array('admin','exercice');
  5. require_once '../global.inc.php';
  6. $libpath = api_get_path(LIBRARY_PATH);
  7. // 1. Setting variables needed by jqgrid
  8. $action = $_GET['a'];
  9. $page = intval($_REQUEST['page']); //page
  10. $limit = intval($_REQUEST['rows']); //quantity of rows
  11. $sidx = $_REQUEST['sidx']; //index (field) to filter
  12. $sord = $_REQUEST['sord']; //asc or desc
  13. if (!in_array($sord, array('asc','desc'))) {
  14. $sord = 'desc';
  15. }
  16. if (!in_array($action, array(
  17. 'get_exercise_results',
  18. 'get_hotpotatoes_exercise_results',
  19. 'get_work_user_list',
  20. 'get_timelines',
  21. 'get_user_skill_ranking',
  22. 'get_usergroups_teacher'))
  23. ) {
  24. api_protect_admin_script(true);
  25. }
  26. //Search features
  27. $ops = array (
  28. 'eq' => '=', //equal
  29. 'ne' => '<>', //not equal
  30. 'lt' => '<', //less than
  31. 'le' => '<=', //less than or equal
  32. 'gt' => '>', //greater than
  33. 'ge' => '>=', //greater than or equal
  34. 'bw' => 'LIKE', //begins with
  35. 'bn' => 'NOT LIKE', //doesn't begin with
  36. 'in' => 'LIKE', //is in
  37. 'ni' => 'NOT LIKE', //is not in
  38. 'ew' => 'LIKE', //ends with
  39. 'en' => 'NOT LIKE', //doesn't end with
  40. 'cn' => 'LIKE', //contains
  41. 'nc' => 'NOT LIKE' //doesn't contain
  42. );
  43. //@todo move this in the display_class or somewhere else
  44. function get_where_clause($col, $oper, $val) {
  45. global $ops;
  46. if (empty($col)){
  47. return '';
  48. }
  49. if($oper == 'bw' || $oper == 'bn') $val .= '%';
  50. if($oper == 'ew' || $oper == 'en' ) $val = '%'.$val;
  51. if($oper == 'cn' || $oper == 'nc' || $oper == 'in' || $oper == 'ni') $val = '%'.$val.'%';
  52. $val = Database::escape_string($val);
  53. return " $col {$ops[$oper]} '$val' ";
  54. }
  55. $where_condition = ""; //if there is no search request sent by jqgrid, $where should be empty
  56. $search_field = isset($_REQUEST['searchField']) ? $_REQUEST['searchField'] : false;
  57. $search_oper = isset($_REQUEST['searchOper']) ? $_REQUEST['searchOper'] : false;
  58. $search_string = isset($_REQUEST['searchString']) ? $_REQUEST['searchString'] : false;
  59. if ($_REQUEST['_search'] == 'true') {
  60. $where_condition = ' 1 = 1 ';
  61. $where_condition_in_form = get_where_clause($search_field, $search_oper, $search_string);
  62. if (!empty($where_condition_in_form)) {
  63. $where_condition .= ' AND '.$where_condition_in_form;
  64. }
  65. $filters = isset($_REQUEST['filters']) ? json_decode($_REQUEST['filters']) : false;
  66. if (!empty($filters)) {
  67. $where_condition .= ' AND ( ';
  68. $counter = 0;
  69. foreach ($filters->rules as $key=>$rule) {
  70. $where_condition .= get_where_clause($rule->field,$rule->op, $rule->data);
  71. if ($counter < count($filters->rules) -1) {
  72. $where_condition .= $filters->groupOp;
  73. }
  74. $counter++;
  75. }
  76. $where_condition .= ' ) ';
  77. }
  78. }
  79. //var_dump($where_condition);
  80. // get index row - i.e. user click to sort $sord = $_GET['sord'];
  81. // get the direction
  82. if (!$sidx) $sidx = 1;
  83. //2. Selecting the count FIRST
  84. //@todo rework this
  85. switch ($action) {
  86. case 'get_user_skill_ranking':
  87. $skill = new Skill();
  88. $count = $skill->get_user_list_skill_ranking_count();
  89. break;
  90. case 'get_work_user_list':
  91. require_once api_get_path(SYS_CODE_PATH).'work/work.lib.php';
  92. $work_id = $_REQUEST['work_id'];
  93. $count = get_count_work($work_id);
  94. break;
  95. case 'get_exercise_results':
  96. require_once api_get_path(SYS_CODE_PATH).'exercice/exercise.lib.php';
  97. $exercise_id = $_REQUEST['exerciseId'];
  98. if (isset($_GET['filter_by_user']) && !empty($_GET['filter_by_user'])) {
  99. $filter_user = intval($_GET['filter_by_user']);
  100. if ($where_condition == "") {
  101. $where_condition .= " te.exe_user_id = '$filter_user'" ;
  102. } else {
  103. $where_condition .= " AND te.exe_user_id = '$filter_user'";
  104. }
  105. }
  106. $count = get_count_exam_results($exercise_id, $where_condition);
  107. break;
  108. case 'get_hotpotatoes_exercise_results':
  109. require_once api_get_path(SYS_CODE_PATH).'exercice/exercise.lib.php';
  110. $hotpot_path = $_REQUEST['path'];
  111. $count = get_count_exam_hotpotatoes_results($hotpot_path);
  112. break;
  113. case 'get_sessions':
  114. $count = SessionManager::get_count_admin();
  115. break;
  116. case 'get_timelines':
  117. require_once $libpath.'timeline.lib.php';
  118. $obj = new Timeline();
  119. $count = $obj->get_count();
  120. break;
  121. case 'get_gradebooks':
  122. require_once $libpath.'gradebook.lib.php';
  123. $obj = new Gradebook();
  124. $count = $obj->get_count();
  125. break;
  126. case 'get_event_email_template':
  127. $obj = new EventEmailTemplate();
  128. $count = $obj->get_count();
  129. break;
  130. case 'get_careers':
  131. $obj = new Career();
  132. $count = $obj->get_count();
  133. break;
  134. case 'get_promotions':
  135. $obj = new Promotion();
  136. $count = $obj->get_count();
  137. break;
  138. case 'get_grade_models':
  139. $obj = new GradeModel();
  140. $count = $obj->get_count();
  141. break;
  142. case 'get_usergroups':
  143. $obj = new UserGroup();
  144. $count = $obj->get_count();
  145. break;
  146. case 'get_usergroups_teacher':
  147. $obj = new UserGroup();
  148. $type = isset($_REQUEST['type']) ? $_REQUEST['type'] : 'registered';
  149. $course_id = api_get_course_int_id();
  150. if ($type == 'registered') {
  151. $count = $obj->get_usergroup_by_course_with_data_count($course_id);
  152. } else {
  153. $count = $obj->get_count();
  154. }
  155. break;
  156. default:
  157. exit;
  158. }
  159. //3. Calculating first, end, etc
  160. $total_pages = 0;
  161. if ($count > 0) {
  162. if (!empty($limit)) {
  163. $total_pages = ceil($count/$limit);
  164. }
  165. }
  166. if ($page > $total_pages) {
  167. $page = $total_pages;
  168. }
  169. $start = $limit * $page - $limit;
  170. if ($start < 0 ) {
  171. $start = 0;
  172. }
  173. //4. Deleting an element if the user wants to
  174. if ($_REQUEST['oper'] == 'del') {
  175. $obj->delete($_REQUEST['id']);
  176. }
  177. $is_allowedToEdit = api_is_allowed_to_edit(null,true) || api_is_allowed_to_edit(true) || api_is_drh();
  178. //5. Querying the DB for the elements
  179. $columns = array();
  180. switch ($action) {
  181. case 'get_user_skill_ranking':
  182. $columns = array('photo', 'firstname', 'lastname', 'skills_acquired', 'currently_learning', 'rank');
  183. $result = $skill->get_user_list_skill_ranking($start, $limit, $sidx, $sord, $where_condition);
  184. $result = msort($result, 'skills_acquired', 'asc');
  185. $skills_in_course = array();
  186. if (!empty($result)) {
  187. //$counter = 1;
  188. foreach ($result as &$item) {
  189. $user_info = api_get_user_info($item['user_id']);
  190. $personal_course_list = UserManager::get_personal_session_course_list($item['user_id']);
  191. $count_skill_by_course = array();
  192. foreach ($personal_course_list as $course_item) {
  193. if (!isset($skills_in_course[$course_item['code']])) {
  194. $count_skill_by_course[$course_item['code']] = $skill->get_count_skills_by_course($course_item['code']);
  195. $skills_in_course[$course_item['code']] = $count_skill_by_course[$course_item['code']];
  196. } else {
  197. $count_skill_by_course[$course_item['code']] = $skills_in_course[$course_item['code']];
  198. }
  199. }
  200. $item['photo'] = Display::img($user_info['avatar_small']);
  201. $item['currently_learning'] = !empty($count_skill_by_course) ? array_sum($count_skill_by_course) : 0;
  202. }
  203. }
  204. break;
  205. case 'get_work_user_list':
  206. if (isset($_GET['type']) && $_GET['type'] == 'simple') {
  207. $columns = array('type', 'firstname', 'lastname', 'username', 'title', 'qualification', 'sent_date', 'qualificator_id', 'actions');
  208. } else {
  209. $columns = array('type', 'firstname', 'lastname', 'username', 'title', 'sent_date', 'actions');
  210. }
  211. $result = get_work_user_list($start, $limit, $sidx, $sord, $work_id, $where_condition);
  212. break;
  213. case 'get_exercise_results':
  214. $course = api_get_course_info();
  215. //used inside get_exam_results_data()
  216. $documentPath = api_get_path(SYS_COURSE_PATH) . $course['path'] . "/document";
  217. if ($is_allowedToEdit) {
  218. $columns = array('firstname', 'lastname', 'username', 'group_name', 'exe_duration', 'start_date', 'exe_date', 'score', 'status', 'actions');
  219. } else {
  220. //$columns = array('exe_duration', 'start_date', 'exe_date', 'score', 'status', 'actions');
  221. }
  222. $result = get_exam_results_data($start, $limit, $sidx, $sord, $exercise_id, $where_condition);
  223. break;
  224. case 'get_hotpotatoes_exercise_results':
  225. $course = api_get_course_info();
  226. //used inside get_exam_results_data()
  227. $documentPath = api_get_path(SYS_COURSE_PATH) . $course['path'] . "/document";
  228. $columns = array('firstname', 'lastname', 'username', 'group_name', 'exe_date', 'score', 'actions');
  229. $result = get_exam_results_hotpotatoes_data($start, $limit, $sidx, $sord, $hotpot_path, $where_condition); //get_exam_results_data($start, $limit, $sidx, $sord, $exercise_id, $where_condition);
  230. break;
  231. case 'get_sessions':
  232. $columns = array('name', 'nbr_courses', 'nbr_users', 'category_name', 'date_start','date_end', 'coach_name', 'session_active', 'visibility');
  233. $result = SessionManager::get_sessions_admin(array('where'=> $where_condition, 'order'=>"$sidx $sord", 'limit'=> "$start , $limit"));
  234. break;
  235. case 'get_timelines':
  236. $columns = array('headline', 'actions');
  237. //$columns = array('headline', 'type', 'start_date', 'end_date', 'text', 'media', 'media_credit', 'media_caption', 'title_slide', 'parent_id');
  238. if(!in_array($sidx, $columns)) {
  239. $sidx = 'headline';
  240. }
  241. $course_id = api_get_course_int_id();
  242. $result = Database::select('*', $obj->table, array('where' => array('parent_id = ? AND c_id = ?' => array('0', $course_id)), 'order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  243. $new_result = array();
  244. foreach ($result as $item) {
  245. if (!$item['status']) {
  246. $item['name'] = '<font style="color:#AAA">'.$item['name'].'</font>';
  247. }
  248. $item['headline'] = Display::url($item['headline'], api_get_path(WEB_CODE_PATH).'timeline/view.php?id='.$item['id']);
  249. $item['actions'] = Display::url(Display::return_icon('add.png', get_lang('AddItems')), api_get_path(WEB_CODE_PATH).'timeline/?action=add_item&parent_id='.$item['id']);
  250. $item['actions'] .= Display::url(Display::return_icon('edit.png', get_lang('Edit')), api_get_path(WEB_CODE_PATH).'timeline/?action=edit&id='.$item['id']);
  251. $item['actions'] .= Display::url(Display::return_icon('delete.png', get_lang('Delete')), api_get_path(WEB_CODE_PATH).'timeline/?action=delete&id='.$item['id']);
  252. $new_result[] = $item;
  253. }
  254. $result = $new_result;
  255. break;
  256. case 'get_gradebooks':
  257. $columns = array('name', 'certificates','skills', 'actions', 'has_certificates');
  258. if (!in_array($sidx, $columns)) {
  259. $sidx = 'name';
  260. }
  261. $result = Database::select('*', $obj->table, array('order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  262. $new_result = array();
  263. foreach($result as $item) {
  264. if ($item['parent_id'] != 0) {
  265. continue;
  266. }
  267. $skills = $obj->get_skills_by_gradebook($item['id']);
  268. //Fixes bug when gradebook doesn't have names
  269. if (empty($item['name'])) {
  270. $item['name'] = $item['course_code'];
  271. } else {
  272. //$item['name'] = $item['name'].' ['.$item['course_code'].']';
  273. }
  274. $item['name'] = Display::url($item['name'], api_get_path(WEB_CODE_PATH).'gradebook/index.php?id_session=0&cidReq='.$item['course_code']);
  275. if (!empty($item['certif_min_score']) && !empty($item['document_id'])) {
  276. $item['certificates'] = Display::return_icon('accept.png', get_lang('WithCertificate'), array(), ICON_SIZE_SMALL);
  277. $item['has_certificates'] = '1';
  278. } else {
  279. $item['certificates'] = Display::return_icon('warning.png', get_lang('NoCertificate'), array(), ICON_SIZE_SMALL);
  280. $item['has_certificates'] = '0';
  281. }
  282. if (!empty($skills)) {
  283. foreach($skills as $skill) {
  284. $item['skills'] .= Display::span($skill['name'], array('class' => 'label_tag skill'));
  285. }
  286. }
  287. $new_result[] = $item;
  288. }
  289. $result = $new_result;
  290. break;
  291. case 'get_event_email_template':
  292. $columns = array('subject', 'event_type_name', 'language_id', 'activated', 'actions');
  293. if(!in_array($sidx, $columns)) {
  294. $sidx = 'subject';
  295. }
  296. $result = Database::select('*', $obj->table, array('order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  297. $new_result = array();
  298. foreach ($result as $item) {
  299. $language_info = api_get_language_info($item['language_id']);
  300. $item['language_id'] = $language_info['english_name'];
  301. $item['actions'] = Display::url(Display::return_icon('edit.png', get_lang('Edit')), api_get_path(WEB_CODE_PATH).'admin/event_type.php?action=edit&event_type_name='.$item['event_type_name']);
  302. $item['actions'] .= Display::url(Display::return_icon('delete.png', get_lang('Delete')), api_get_path(WEB_CODE_PATH).'admin/event_controller.php?action=delete&id='.$item['id']);
  303. /*if (!$item['status']) {
  304. $item['name'] = '<font style="color:#AAA">'.$item['subject'].'</font>';
  305. }*/
  306. $new_result[] = $item;
  307. }
  308. $result = $new_result;
  309. break;
  310. case 'get_careers':
  311. $columns = array('name', 'description', 'actions');
  312. if(!in_array($sidx, $columns)) {
  313. $sidx = 'name';
  314. }
  315. $result = Database::select('*', $obj->table, array('order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  316. $new_result = array();
  317. foreach ($result as $item) {
  318. if (!$item['status']) {
  319. $item['name'] = '<font style="color:#AAA">'.$item['name'].'</font>';
  320. }
  321. $new_result[] = $item;
  322. }
  323. $result = $new_result;
  324. break;
  325. case 'get_promotions':
  326. $columns = array('name', 'career', 'description', 'actions');
  327. if(!in_array($sidx, $columns)) {
  328. $sidx = 'name';
  329. }
  330. $result = Database::select('p.id,p.name, p.description, c.name as career, p.status', "$obj->table p LEFT JOIN ".Database::get_main_table(TABLE_CAREER)." c ON c.id = p.career_id ", array('order' =>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  331. $new_result = array();
  332. foreach($result as $item) {
  333. if (!$item['status']) {
  334. $item['name'] = '<font style="color:#AAA">'.$item['name'].'</font>';
  335. }
  336. $new_result[] = $item;
  337. }
  338. $result = $new_result;
  339. break;
  340. case 'get_grade_models':
  341. $columns = array('name', 'description', 'actions');
  342. if (!in_array($sidx, $columns)) {
  343. $sidx = 'name';
  344. }
  345. $result = Database::select('*', "$obj->table ", array('order' =>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  346. $new_result = array();
  347. foreach($result as $item) {
  348. $new_result[] = $item;
  349. }
  350. $result = $new_result;
  351. break;
  352. case 'get_usergroups':
  353. $columns = array('name', 'users', 'courses','sessions','actions');
  354. $result = Database::select('*', $obj->table, array('order'=>"name $sord", 'LIMIT'=> "$start , $limit"));
  355. $new_result = array();
  356. if (!empty($result)) {
  357. foreach ($result as $group) {
  358. $group['sessions'] = count($obj->get_sessions_by_usergroup($group['id']));
  359. $group['courses'] = count($obj->get_courses_by_usergroup($group['id']));
  360. $group['users'] = count($obj->get_users_by_usergroup($group['id']));
  361. $new_result[] = $group;
  362. }
  363. $result = $new_result;
  364. }
  365. $columns = array('name', 'users', 'courses','sessions');
  366. if(!in_array($sidx, $columns)) {
  367. $sidx = 'name';
  368. }
  369. //Multidimensional sort
  370. msort($result, $sidx);
  371. break;
  372. case 'get_usergroups_teacher':
  373. $columns = array('name', 'users', 'actions');
  374. $options = array('order'=>"name $sord", 'LIMIT'=> "$start , $limit");
  375. switch ($type) {
  376. case 'not_registered':
  377. $options['course_id'] = $course_id;
  378. $result = $obj->get_usergroup_not_in_course($options);
  379. break;
  380. case 'registered':
  381. $options['where'] = array(" usergroup.course_id = ? " => $course_id);
  382. $result = $obj->get_usergroup_in_course($options);
  383. break;
  384. }
  385. $new_result = array();
  386. if (!empty($result)) {
  387. foreach ($result as $group) {
  388. $group['users'] = count($obj->get_users_by_usergroup($group['id']));
  389. if ($obj->usergroup_was_added_in_course($group['id'], $course_id)) {
  390. $url = 'class.php?action=remove_class_from_course&id='.$group['id'];
  391. $icon = Display::return_icon('delete.png', get_lang('Remove'));
  392. } else {
  393. $url = 'class.php?action=add_class_to_course&id='.$group['id'];
  394. $icon = Display::return_icon('add.png', get_lang('Add'));
  395. }
  396. $group['actions'] = Display::url($icon, $url);
  397. $new_result[] = $group;
  398. }
  399. $result = $new_result;
  400. }
  401. if (!in_array($sidx, $columns)) {
  402. $sidx = 'name';
  403. }
  404. //Multidimensional sort
  405. msort($result, $sidx);
  406. break;
  407. default:
  408. exit;
  409. }
  410. //var_dump($result);
  411. $allowed_actions = array('get_careers',
  412. 'get_promotions',
  413. 'get_usergroups',
  414. 'get_usergroups_teacher',
  415. 'get_gradebooks',
  416. 'get_sessions',
  417. 'get_exercise_results',
  418. 'get_hotpotatoes_exercise_results',
  419. 'get_work_user_list',
  420. 'get_timelines',
  421. 'get_grade_models',
  422. 'get_event_email_template',
  423. 'get_user_skill_ranking');
  424. //5. Creating an obj to return a json
  425. if (in_array($action, $allowed_actions)) {
  426. $response = new stdClass();
  427. $response->page = $page;
  428. $response->total = $total_pages;
  429. $response->records = $count;
  430. $i=0;
  431. if (!empty($result)) {
  432. foreach ($result as $row) {
  433. //print_r($row);
  434. // if results tab give not id, set id to $i otherwise id="null" for all <tr> of the jqgrid - ref #4235
  435. if ($row['id'] == "") {
  436. $response->rows[$i]['id']=$i;
  437. } else {
  438. $response->rows[$i]['id']=$row['id'];
  439. }
  440. $array = array();
  441. foreach ($columns as $col) {
  442. $array[] = $row[$col];
  443. }
  444. $response->rows[$i]['cell']=$array;
  445. $i++;
  446. }
  447. }
  448. echo json_encode($response);
  449. }
  450. exit;