model.ajax.php 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357
  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'))) {
  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. $where_condition_in_form = get_where_clause($search_field, $search_oper, $search_string);
  55. if (!empty($where_condition_in_form)) {
  56. $where_condition .= ' AND '.$where_condition_in_form;
  57. }
  58. $filters = isset($_REQUEST['filters']) ? json_decode($_REQUEST['filters']) : false;
  59. if (!empty($filters)) {
  60. $where_condition .= ' AND ( ';
  61. $counter = 0;
  62. foreach ($filters->rules as $key=>$rule) {
  63. $where_condition .= get_where_clause($rule->field,$rule->op, $rule->data);
  64. if ($counter < count($filters->rules) -1) {
  65. $where_condition .= $filters->groupOp;
  66. }
  67. $counter++;
  68. }
  69. $where_condition .= ' ) ';
  70. }
  71. }
  72. //var_dump($where_condition);
  73. // get index row - i.e. user click to sort $sord = $_GET['sord'];
  74. // get the direction
  75. if (!$sidx) $sidx = 1;
  76. //2. Selecting the count FIRST
  77. //@todo rework this
  78. switch ($action) {
  79. case 'get_work_user_list':
  80. require_once api_get_path(SYS_CODE_PATH).'work/work.lib.php';
  81. $work_id = $_REQUEST['work_id'];
  82. $count = get_count_work($work_id);
  83. break;
  84. case 'get_exercise_results':
  85. require_once api_get_path(SYS_CODE_PATH).'exercice/exercise.lib.php';
  86. require_once $libpath.'groupmanager.lib.php';
  87. $exercise_id = $_REQUEST['exerciseId'];
  88. $count = get_count_exam_results($exercise_id);
  89. break;
  90. case 'get_sessions':
  91. require_once $libpath.'sessionmanager.lib.php';
  92. $count = SessionManager::get_count_admin();
  93. break;
  94. case 'get_timelines':
  95. require_once $libpath.'timeline.lib.php';
  96. $obj = new Timeline();
  97. $count = $obj->get_count();
  98. break;
  99. case 'get_gradebooks':
  100. require_once $libpath.'gradebook.lib.php';
  101. $obj = new Gradebook();
  102. $count = $obj->get_count();
  103. break;
  104. case 'get_careers':
  105. require_once $libpath.'career.lib.php';
  106. $obj = new Career();
  107. $count = $obj->get_count();
  108. break;
  109. case 'get_promotions':
  110. require_once $libpath.'promotion.lib.php';
  111. $obj = new Promotion();
  112. $count = $obj->get_count();
  113. break;
  114. case 'get_grade_models':
  115. require_once $libpath.'grade_model.lib.php';
  116. $obj = new GradeModel();
  117. $count = $obj->get_count();
  118. break;
  119. case 'get_usergroups':
  120. require_once $libpath.'usergroup.lib.php';
  121. $obj = new UserGroup();
  122. $count = $obj->get_count();
  123. break;
  124. default:
  125. exit;
  126. }
  127. //3. Calculating first, end, etc
  128. $total_pages = 0;
  129. if ($count > 0) {
  130. if (!empty($limit)) {
  131. $total_pages = ceil($count/$limit);
  132. }
  133. }
  134. if ($page > $total_pages) {
  135. $page = $total_pages;
  136. }
  137. $start = $limit * $page - $limit;
  138. if ($start < 0 ) {
  139. $start = 0;
  140. }
  141. //4. Deleting an element if the user wants to
  142. if ($_REQUEST['oper'] == 'del') {
  143. $obj->delete($_REQUEST['id']);
  144. }
  145. $is_allowedToEdit = api_is_allowed_to_edit(null,true);
  146. $is_tutor = api_is_allowed_to_edit(true);
  147. //5. Querying the DB for the elements
  148. $columns = array();
  149. switch ($action) {
  150. case 'get_work_user_list':
  151. if (isset($_GET['type']) && $_GET['type'] == 'simple') {
  152. $columns = array('type', 'firstname', 'lastname', 'username', 'qualification', 'sent_date', 'qualificator_id', 'actions');
  153. } else {
  154. $columns = array('type', 'firstname', 'lastname', 'username', 'sent_date', 'actions');
  155. }
  156. $result = get_work_user_list($start, $limit, $sidx, $sord, $work_id, $where_condition);
  157. break;
  158. case 'get_exercise_results':
  159. $course = api_get_course_info();
  160. //used inside get_exam_results_data()
  161. $documentPath = api_get_path(SYS_COURSE_PATH) . $course['path'] . "/document";
  162. if ($is_allowedToEdit || $is_tutor) {
  163. $columns = array('firstname', 'lastname', 'username', 'group_name', 'exe_duration', 'start_date', 'exe_date', 'score','status','actions');
  164. } else {
  165. $columns = array('exe_duration', 'start_date', 'exe_date', 'score', 'status', 'actions');
  166. }
  167. $result = get_exam_results_data($start, $limit, $sidx, $sord, $exercise_id, $where_condition);
  168. break;
  169. case 'get_sessions':
  170. $columns = array('name', 'nbr_courses','category_name', 'date_start','date_end', 'coach_name', 'session_active', 'visibility');
  171. $result = SessionManager::get_sessions_admin(array('where'=> $where_condition, 'order'=>"$sidx $sord", 'limit'=> "$start , $limit"));
  172. break;
  173. case 'get_timelines':
  174. $columns = array('headline', 'actions');
  175. //$columns = array('headline', 'type', 'start_date', 'end_date', 'text', 'media', 'media_credit', 'media_caption', 'title_slide', 'parent_id');
  176. if(!in_array($sidx, $columns)) {
  177. $sidx = 'headline';
  178. }
  179. $course_id = api_get_course_int_id();
  180. $result = Database::select('*', $obj->table, array('where' => array('parent_id = ? AND c_id = ?' => array('0', $course_id)), 'order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  181. $new_result = array();
  182. foreach ($result as $item) {
  183. if (!$item['status']) {
  184. $item['name'] = '<font style="color:#AAA">'.$item['name'].'</font>';
  185. }
  186. $item['headline'] = Display::url($item['headline'], api_get_path(WEB_CODE_PATH).'timeline/view.php?id='.$item['id']);
  187. $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']);
  188. $item['actions'] .= Display::url(Display::return_icon('edit.png', get_lang('Edit')), api_get_path(WEB_CODE_PATH).'timeline/?action=edit&id='.$item['id']);
  189. $item['actions'] .= Display::url(Display::return_icon('delete.png', get_lang('Delete')), api_get_path(WEB_CODE_PATH).'timeline/?action=delete&id='.$item['id']);
  190. $new_result[] = $item;
  191. }
  192. $result = $new_result;
  193. break;
  194. case 'get_gradebooks':
  195. $columns = array('name', 'certificates','skills', 'actions', 'has_certificates');
  196. if (!in_array($sidx, $columns)) {
  197. $sidx = 'name';
  198. }
  199. $result = Database::select('*', $obj->table, array('order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  200. $new_result = array();
  201. foreach($result as $item) {
  202. if ($item['parent_id'] != 0) {
  203. continue;
  204. }
  205. $skills = $obj->get_skills_by_gradebook($item['id']);
  206. //Fixes bug when gradebook doesn't have names
  207. if (empty($item['name'])) {
  208. $item['name'] = $item['course_code'];
  209. } else {
  210. //$item['name'] = $item['name'].' ['.$item['course_code'].']';
  211. }
  212. $item['name'] = Display::url($item['name'], api_get_path(WEB_CODE_PATH).'gradebook/index.php?id_session=0&cidReq='.$item['course_code']);
  213. if (!empty($item['certif_min_score']) && !empty($item['document_id'])) {
  214. $item['certificates'] = Display::return_icon('accept.png', get_lang('WithCertificate'), array(), ICON_SIZE_SMALL);
  215. $item['has_certificates'] = '1';
  216. } else {
  217. $item['certificates'] = Display::return_icon('warning.png', get_lang('NoCertificate'), array(), ICON_SIZE_SMALL);
  218. $item['has_certificates'] = '0';
  219. }
  220. if (!empty($skills)) {
  221. foreach($skills as $skill) {
  222. $item['skills'] .= Display::span($skill['name'], array('class' => 'label_tag skill'));
  223. }
  224. }
  225. $new_result[] = $item;
  226. }
  227. $result = $new_result;
  228. break;
  229. case 'get_careers':
  230. $columns = array('name', 'description', 'actions');
  231. if(!in_array($sidx, $columns)) {
  232. $sidx = 'name';
  233. }
  234. $result = Database::select('*', $obj->table, array('order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  235. $new_result = array();
  236. foreach ($result as $item) {
  237. if (!$item['status']) {
  238. $item['name'] = '<font style="color:#AAA">'.$item['name'].'</font>';
  239. }
  240. $new_result[] = $item;
  241. }
  242. $result = $new_result;
  243. break;
  244. case 'get_promotions':
  245. $columns = array('name', 'career', 'description', 'actions');
  246. if(!in_array($sidx, $columns)) {
  247. $sidx = 'name';
  248. }
  249. $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"));
  250. $new_result = array();
  251. foreach($result as $item) {
  252. if (!$item['status']) {
  253. $item['name'] = '<font style="color:#AAA">'.$item['name'].'</font>';
  254. }
  255. $new_result[] = $item;
  256. }
  257. $result = $new_result;
  258. break;
  259. case 'get_grade_models':
  260. $columns = array('name', 'description', 'actions');
  261. if(!in_array($sidx, $columns)) {
  262. $sidx = 'name';
  263. }
  264. $result = Database::select('*', "$obj->table ", array('order' =>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  265. $new_result = array();
  266. foreach($result as $item) {
  267. $new_result[] = $item;
  268. }
  269. $result = $new_result;
  270. break;
  271. case 'get_usergroups':
  272. $columns = array('name', 'users', 'courses','sessions','actions');
  273. $result = Database::select('*', $obj->table, array('order'=>"name $sord", 'LIMIT'=> "$start , $limit"));
  274. $new_result = array();
  275. if (!empty($result)) {
  276. foreach ($result as $group) {
  277. $group['sessions'] = count($obj->get_sessions_by_usergroup($group['id']));
  278. $group['courses'] = count($obj->get_courses_by_usergroup($group['id']));
  279. $group['users'] = count($obj->get_users_by_usergroup($group['id']));
  280. $new_result[] = $group;
  281. }
  282. $result = $new_result;
  283. }
  284. $columns = array('name', 'users', 'courses','sessions');
  285. if(!in_array($sidx, $columns)) {
  286. $sidx = 'name';
  287. }
  288. //Multidimensional sort
  289. msort($result, $sidx);
  290. break;
  291. default:
  292. exit;
  293. }
  294. //var_dump($result);
  295. $allowed_actions = array('get_careers', 'get_promotions', 'get_usergroups', 'get_gradebooks',
  296. 'get_sessions', 'get_exercise_results', 'get_work_user_list', 'get_timelines', 'get_grade_models');
  297. //5. Creating an obj to return a json
  298. if (in_array($action, $allowed_actions)) {
  299. $response = new stdClass();
  300. $response->page = $page;
  301. $response->total = $total_pages;
  302. $response->records = $count;
  303. $i=0;
  304. if (!empty($result)) {
  305. foreach ($result as $row) {
  306. //print_r($row);
  307. // if results tab give not id, set id to $i otherwise id="null" for all <tr> of the jqgrid - ref #4235
  308. if ($row['id'] == "") {
  309. $response->rows[$i]['id']=$i;
  310. } else {
  311. $response->rows[$i]['id']=$row['id'];
  312. }
  313. $array = array();
  314. foreach ($columns as $col) {
  315. $array[] = $row[$col];
  316. }
  317. $response->rows[$i]['cell']=$array;
  318. $i++;
  319. }
  320. }
  321. echo json_encode($response);
  322. }
  323. exit;