model.ajax.php 19 KB

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