model.ajax.php 29 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703
  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', 'gradebook', 'tracking');
  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 (strpos(strtolower($sidx), 'asc') !== false) {
  14. $sidx = str_replace(array('asc', ','), '', $sidx);
  15. $sord = 'asc';
  16. }
  17. if (strpos(strtolower($sidx), 'desc') !== false) {
  18. $sidx = str_replace(array('desc', ','), '', $sidx);
  19. $sord = 'desc';
  20. }
  21. if (!in_array($sord, array('asc','desc'))) {
  22. $sord = 'desc';
  23. }
  24. if (!in_array($action, array(
  25. 'get_exercise_results',
  26. 'get_hotpotatoes_exercise_results',
  27. 'get_work_user_list',
  28. 'get_timelines',
  29. 'get_user_skill_ranking',
  30. 'get_usergroups_teacher'))
  31. ) {
  32. api_protect_admin_script(true);
  33. }
  34. //Search features
  35. $ops = array (
  36. 'eq' => '=', //equal
  37. 'ne' => '<>', //not equal
  38. 'lt' => '<', //less than
  39. 'le' => '<=', //less than or equal
  40. 'gt' => '>', //greater than
  41. 'ge' => '>=', //greater than or equal
  42. 'bw' => 'LIKE', //begins with
  43. 'bn' => 'NOT LIKE', //doesn't begin with
  44. 'in' => 'LIKE', //is in
  45. 'ni' => 'NOT LIKE', //is not in
  46. 'ew' => 'LIKE', //ends with
  47. 'en' => 'NOT LIKE', //doesn't end with
  48. 'cn' => 'LIKE', //contains
  49. 'nc' => 'NOT LIKE' //doesn't contain
  50. );
  51. //@todo move this in the display_class or somewhere else
  52. function get_where_clause($col, $oper, $val) {
  53. global $ops;
  54. if (empty($col)){
  55. return '';
  56. }
  57. if($oper == 'bw' || $oper == 'bn') $val .= '%';
  58. if($oper == 'ew' || $oper == 'en' ) $val = '%'.$val;
  59. if($oper == 'cn' || $oper == 'nc' || $oper == 'in' || $oper == 'ni') $val = '%'.$val.'%';
  60. $val = Database::escape_string($val);
  61. return " $col {$ops[$oper]} '$val' ";
  62. }
  63. $where_condition = ""; //if there is no search request sent by jqgrid, $where should be empty
  64. $operation = isset($_REQUEST['oper']) ? $_REQUEST['oper'] : false;
  65. $export_format = isset($_REQUEST['export_format']) ? $_REQUEST['export_format'] : 'csv';
  66. $search_field = isset($_REQUEST['searchField']) ? $_REQUEST['searchField'] : false;
  67. $search_oper = isset($_REQUEST['searchOper']) ? $_REQUEST['searchOper'] : false;
  68. $search_string = isset($_REQUEST['searchString']) ? $_REQUEST['searchString'] : false;
  69. $extra_fields = array();
  70. if ($_REQUEST['_search'] == 'true') {
  71. $where_condition = ' 1 = 1 ';
  72. $where_condition_in_form = get_where_clause($search_field, $search_oper, $search_string);
  73. if (!empty($where_condition_in_form)) {
  74. $where_condition .= ' AND '.$where_condition_in_form;
  75. }
  76. $filters = isset($_REQUEST['filters']) ? json_decode($_REQUEST['filters']) : false;
  77. //var_dump($filters);
  78. //for now
  79. $extra_field = new ExtraField('session');
  80. if (!empty($filters)) {
  81. //Getting double select if exists
  82. $double_select = array();
  83. foreach ($filters->rules as $rule) {
  84. if (strpos($rule->field, '_second') === false) {
  85. } else {
  86. $my_field = str_replace('_second', '', $rule->field);
  87. $double_select[$my_field] = $rule->data;
  88. }
  89. }
  90. $condition_array = array();
  91. foreach ($filters->rules as $rule) {
  92. if (strpos($rule->field, 'extra_') === false) {
  93. //normal fields
  94. $field = $rule->field;
  95. if (!empty($rule->data)) {
  96. $condition_array[] = get_where_clause($field, $rule->op, $rule->data);
  97. }
  98. } else {
  99. //Extra fields
  100. //normal
  101. if (strpos($rule->field, '_second') === false) {
  102. //No _second
  103. $original_field = str_replace('extra_', '', $rule->field);
  104. $field_option = $extra_field->get_handler_field_info_by_field_variable($original_field);
  105. if ($field_option['field_type'] == ExtraField::FIELD_TYPE_DOUBLE_SELECT) {
  106. if (isset($double_select[$rule->field])) {
  107. $data = explode('#', $rule->data);
  108. //var_dump($data);
  109. $rule->data = $data[1].'::'.$double_select[$rule->field];
  110. } else {
  111. // only was sent 1 select
  112. $data = explode('#', $rule->data);
  113. $rule->data = $data[1];
  114. }
  115. if (!empty($rule->data)) {
  116. $condition_array[] = ' ('.get_where_clause($rule->field, $rule->op, $rule->data).') ';
  117. $extra_fields[] = array('field' => $rule->field, 'id' => $field_option['id']);
  118. }
  119. } else {
  120. if (!empty($rule->data)) {
  121. $condition_array[] = ' ('.get_where_clause($rule->field, $rule->op, $rule->data).') ';
  122. $extra_fields[] = array('field' => $rule->field, 'id' => $field_option['id']);
  123. }
  124. }
  125. } else {
  126. $my_field = str_replace('_second', '', $rule->field);
  127. $original_field = str_replace('extra_', '', $my_field);
  128. $field_option = $extra_field->get_handler_field_info_by_field_variable($original_field);
  129. $extra_fields[] = array('field' => $rule->field, 'id' => $field_option['id']);
  130. }
  131. }
  132. }
  133. if (!empty($condition_array)) {
  134. $where_condition .= ' AND ( ';
  135. //var_dump($condition_array);
  136. $where_condition .= implode($filters->groupOp, $condition_array);
  137. $where_condition .= ' ) ';
  138. }
  139. }
  140. }
  141. // get index row - i.e. user click to sort $sord = $_GET['sord'];
  142. // get the direction
  143. if (!$sidx) $sidx = 1;
  144. //2. Selecting the count FIRST
  145. //@todo rework this
  146. switch ($action) {
  147. case 'get_group_reporting':
  148. $course_id = isset($_REQUEST['course_id']) ? $_REQUEST['course_id'] : null;
  149. $group_id = isset($_REQUEST['gidReq']) ? $_REQUEST['gidReq'] : null;
  150. $count = Tracking::get_group_reporting($course_id, $group_id, 'count');
  151. break;
  152. case 'get_user_course_report_resumed':
  153. $count = CourseManager::get_count_user_list_from_course_code(true, 'ruc');
  154. break;
  155. case 'get_user_course_report':
  156. $count = CourseManager::get_count_user_list_from_course_code(false);
  157. break;
  158. case 'get_course_exercise_medias':
  159. $course_id = api_get_course_int_id();
  160. $count = Question::get_count_course_medias($course_id);
  161. break;
  162. case 'get_user_skill_ranking':
  163. $skill = new Skill();
  164. $count = $skill->get_user_list_skill_ranking_count();
  165. break;
  166. case 'get_work_user_list':
  167. require_once api_get_path(SYS_CODE_PATH).'work/work.lib.php';
  168. $work_id = $_REQUEST['work_id'];
  169. $count = get_count_work($work_id);
  170. break;
  171. case 'get_exercise_results':
  172. require_once api_get_path(SYS_CODE_PATH).'exercice/exercise.lib.php';
  173. $exercise_id = $_REQUEST['exerciseId'];
  174. if (isset($_GET['filter_by_user']) && !empty($_GET['filter_by_user'])) {
  175. $filter_user = intval($_GET['filter_by_user']);
  176. if ($where_condition == "") {
  177. $where_condition .= " te.exe_user_id = '$filter_user'" ;
  178. } else {
  179. $where_condition .= " AND te.exe_user_id = '$filter_user'";
  180. }
  181. }
  182. $count = get_count_exam_results($exercise_id, $where_condition);
  183. break;
  184. case 'get_hotpotatoes_exercise_results':
  185. require_once api_get_path(SYS_CODE_PATH).'exercice/exercise.lib.php';
  186. $hotpot_path = $_REQUEST['path'];
  187. $count = get_count_exam_hotpotatoes_results($hotpot_path);
  188. break;
  189. case 'get_sessions':
  190. $list_type = isset($_REQUEST['list_type']) ? $_REQUEST['list_type'] : 'simple';
  191. if ($list_type == 'simple') {
  192. $count = SessionManager::get_sessions_admin(array('where'=> $where_condition, 'extra' => $extra_fields), true);
  193. } else {
  194. $count = SessionManager::get_count_admin_complete(array('where'=> $where_condition, 'extra' => $extra_fields));
  195. }
  196. break;
  197. case 'get_extra_fields':
  198. $type = $_REQUEST['type'];
  199. $obj = new ExtraField($type);
  200. $count = $obj->get_count();
  201. break;
  202. case 'get_extra_field_options':
  203. $type = $_REQUEST['type'];
  204. $field_id = $_REQUEST['field_id'];
  205. $obj = new ExtraFieldOption($type);
  206. $count = $obj->get_count_by_field_id($field_id);
  207. break;
  208. case 'get_timelines':
  209. require_once $libpath.'timeline.lib.php';
  210. $obj = new Timeline();
  211. $count = $obj->get_count();
  212. break;
  213. case 'get_gradebooks':
  214. require_once $libpath.'gradebook.lib.php';
  215. $obj = new Gradebook();
  216. $count = $obj->get_count();
  217. break;
  218. case 'get_event_email_template':
  219. $obj = new EventEmailTemplate();
  220. $count = $obj->get_count();
  221. break;
  222. case 'get_careers':
  223. $obj = new Career();
  224. $count = $obj->get_count();
  225. break;
  226. case 'get_promotions':
  227. $obj = new Promotion();
  228. $count = $obj->get_count();
  229. break;
  230. case 'get_grade_models':
  231. $obj = new GradeModel();
  232. $count = $obj->get_count();
  233. break;
  234. case 'get_usergroups':
  235. $obj = new UserGroup();
  236. $count = $obj->get_count();
  237. break;
  238. case 'get_usergroups_teacher':
  239. $obj = new UserGroup();
  240. $type = isset($_REQUEST['type']) ? $_REQUEST['type'] : 'registered';
  241. $course_id = api_get_course_int_id();
  242. if ($type == 'registered') {
  243. $count = $obj->get_usergroup_by_course_with_data_count($course_id);
  244. } else {
  245. $count = $obj->get_count();
  246. }
  247. break;
  248. default:
  249. exit;
  250. }
  251. //3. Calculating first, end, etc
  252. $total_pages = 0;
  253. if ($count > 0) {
  254. if (!empty($limit)) {
  255. $total_pages = ceil($count/$limit);
  256. }
  257. }
  258. if ($page > $total_pages) {
  259. $page = $total_pages;
  260. }
  261. $start = $limit * $page - $limit;
  262. if ($start < 0 ) {
  263. $start = 0;
  264. }
  265. //4. Deleting an element if the user wants to
  266. if (isset($_REQUEST['oper']) && $_REQUEST['oper'] == 'del') {
  267. $obj->delete($_REQUEST['id']);
  268. }
  269. $is_allowedToEdit = api_is_allowed_to_edit(null,true) || api_is_allowed_to_edit(true) || api_is_drh();
  270. //5. Querying the DB for the elements
  271. $columns = array();
  272. switch ($action) {
  273. case 'get_group_reporting':
  274. $columns = array('name', 'time', 'progress', 'score', 'works', 'messages', 'actions');
  275. $result = Tracking::get_group_reporting($course_id, $group_id, 'all', $start, $limit, $sidx, $sord, $where_condition);
  276. break;
  277. case 'get_course_exercise_medias':
  278. $columns = array('question');
  279. $result = Question::get_course_medias($course_id, $start, $limit, $sidx, $sord, $where_condition);
  280. break;
  281. case 'get_user_course_report_resumed':
  282. $columns = array('extra_ruc', 'training_hours', 'count_users', 'count_users_registered', 'average_hours_per_user', 'count_certificates');
  283. $column_names = array(get_lang('Company'), get_lang('TrainingHoursAccumulated'), get_lang('CountOfSubscriptions'), get_lang('CountOfUsers'), get_lang('AverageHoursPerStudent'), get_lang('CountCertificates'));
  284. $result = CourseManager::get_user_list_from_course_code(null, null, "LIMIT $start, $limit", " $sidx $sord", null, null, true, true, 'ruc');
  285. $new_result = array();
  286. if (!empty($result)) {
  287. foreach ($result as $row) {
  288. $row['training_hours'] = api_time_to_hms($row['training_hours']);
  289. $row['average_hours_per_user'] = api_time_to_hms($row['average_hours_per_user']);
  290. $new_result[] = $row;
  291. }
  292. $result = $new_result;
  293. }
  294. break;
  295. case 'get_user_course_report':
  296. $columns = array('course', 'user', 'time', 'certificate', 'score', 'progress');
  297. $column_names = array(get_lang('Course'), get_lang('User'), get_lang('ManHours'), get_lang('Approved'), get_lang('Score'), get_lang('NotesObtained'));
  298. $extra_fields = UserManager::get_extra_fields(0, 100, null, null, true, true);
  299. if (!empty($extra_fields)) {
  300. foreach($extra_fields as $extra) {
  301. $columns[] = $extra['1'];
  302. $column_names[] = $extra['3'];
  303. }
  304. }
  305. $result = CourseManager::get_user_list_from_course_code(null, null, "LIMIT $start, $limit", " $sidx $sord", null, null, true);
  306. break;
  307. case 'get_user_skill_ranking':
  308. $columns = array('photo', 'firstname', 'lastname', 'skills_acquired', 'currently_learning', 'rank');
  309. $result = $skill->get_user_list_skill_ranking($start, $limit, $sidx, $sord, $where_condition);
  310. $result = msort($result, 'skills_acquired', 'asc');
  311. $skills_in_course = array();
  312. if (!empty($result)) {
  313. //$counter = 1;
  314. foreach ($result as &$item) {
  315. $user_info = api_get_user_info($item['user_id']);
  316. $personal_course_list = UserManager::get_personal_session_course_list($item['user_id']);
  317. $count_skill_by_course = array();
  318. foreach ($personal_course_list as $course_item) {
  319. if (!isset($skills_in_course[$course_item['code']])) {
  320. $count_skill_by_course[$course_item['code']] = $skill->get_count_skills_by_course($course_item['code']);
  321. $skills_in_course[$course_item['code']] = $count_skill_by_course[$course_item['code']];
  322. } else {
  323. $count_skill_by_course[$course_item['code']] = $skills_in_course[$course_item['code']];
  324. }
  325. }
  326. $item['photo'] = Display::img($user_info['avatar_small']);
  327. $item['currently_learning'] = !empty($count_skill_by_course) ? array_sum($count_skill_by_course) : 0;
  328. }
  329. }
  330. break;
  331. case 'get_work_user_list':
  332. if (isset($_GET['type']) && $_GET['type'] == 'simple') {
  333. $columns = array('type', 'firstname', 'lastname', 'username', 'title', 'qualification', 'sent_date', 'qualificator_id', 'actions');
  334. } else {
  335. $columns = array('type', 'firstname', 'lastname', 'username', 'title', 'sent_date', 'actions');
  336. }
  337. $result = get_work_user_list($start, $limit, $sidx, $sord, $work_id, $where_condition);
  338. break;
  339. case 'get_exercise_results':
  340. $course = api_get_course_info();
  341. //used inside get_exam_results_data()
  342. $documentPath = api_get_path(SYS_COURSE_PATH) . $course['path'] . "/document";
  343. if ($is_allowedToEdit) {
  344. $columns = array('firstname', 'lastname', 'username', 'group_name', 'exe_duration', 'start_date', 'exe_date', 'score', 'status', 'lp', 'actions');
  345. } else {
  346. //$columns = array('exe_duration', 'start_date', 'exe_date', 'score', 'status', 'actions');
  347. }
  348. $result = get_exam_results_data($start, $limit, $sidx, $sord, $exercise_id, $where_condition);
  349. break;
  350. case 'get_hotpotatoes_exercise_results':
  351. $course = api_get_course_info();
  352. //used inside get_exam_results_data()
  353. $documentPath = api_get_path(SYS_COURSE_PATH) . $course['path'] . "/document";
  354. $columns = array('firstname', 'lastname', 'username', 'group_name', 'exe_date', 'score', 'actions');
  355. $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);
  356. break;
  357. case 'get_sessions':
  358. $session_columns = SessionManager::get_session_columns($list_type);
  359. $columns = $session_columns['simple_column_name'];
  360. if ($list_type == 'simple') {
  361. $result = SessionManager::get_sessions_admin(array('where'=> $where_condition, 'order'=>"$sidx $sord", 'extra' => $extra_fields, 'limit'=> "$start , $limit"), false);
  362. } else {
  363. $result = SessionManager::get_sessions_admin_complete(array('where'=> $where_condition, 'order'=>"$sidx $sord", 'extra' => $extra_fields, 'limit'=> "$start , $limit"));
  364. }
  365. break;
  366. case 'get_timelines':
  367. $columns = array('headline', 'actions');
  368. //$columns = array('headline', 'type', 'start_date', 'end_date', 'text', 'media', 'media_credit', 'media_caption', 'title_slide', 'parent_id');
  369. if(!in_array($sidx, $columns)) {
  370. $sidx = 'headline';
  371. }
  372. $course_id = api_get_course_int_id();
  373. $result = Database::select('*', $obj->table, array('where' => array('parent_id = ? AND c_id = ?' => array('0', $course_id)), 'order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  374. $new_result = array();
  375. foreach ($result as $item) {
  376. if (!$item['status']) {
  377. $item['name'] = '<font style="color:#AAA">'.$item['name'].'</font>';
  378. }
  379. $item['headline'] = Display::url($item['headline'], api_get_path(WEB_CODE_PATH).'timeline/view.php?id='.$item['id']);
  380. $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']);
  381. $item['actions'] .= Display::url(Display::return_icon('edit.png', get_lang('Edit')), api_get_path(WEB_CODE_PATH).'timeline/?action=edit&id='.$item['id']);
  382. $item['actions'] .= Display::url(Display::return_icon('delete.png', get_lang('Delete')), api_get_path(WEB_CODE_PATH).'timeline/?action=delete&id='.$item['id']);
  383. $new_result[] = $item;
  384. }
  385. $result = $new_result;
  386. break;
  387. case 'get_gradebooks':
  388. $columns = array('name', 'certificates','skills', 'actions', 'has_certificates');
  389. if (!in_array($sidx, $columns)) {
  390. $sidx = 'name';
  391. }
  392. $result = Database::select('*', $obj->table, array('order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  393. $new_result = array();
  394. foreach($result as $item) {
  395. if ($item['parent_id'] != 0) {
  396. continue;
  397. }
  398. $skills = $obj->get_skills_by_gradebook($item['id']);
  399. //Fixes bug when gradebook doesn't have names
  400. if (empty($item['name'])) {
  401. $item['name'] = $item['course_code'];
  402. } else {
  403. //$item['name'] = $item['name'].' ['.$item['course_code'].']';
  404. }
  405. $item['name'] = Display::url($item['name'], api_get_path(WEB_CODE_PATH).'gradebook/index.php?id_session=0&cidReq='.$item['course_code']);
  406. if (!empty($item['certif_min_score']) && !empty($item['document_id'])) {
  407. $item['certificates'] = Display::return_icon('accept.png', get_lang('WithCertificate'), array(), ICON_SIZE_SMALL);
  408. $item['has_certificates'] = '1';
  409. } else {
  410. $item['certificates'] = Display::return_icon('warning.png', get_lang('NoCertificate'), array(), ICON_SIZE_SMALL);
  411. $item['has_certificates'] = '0';
  412. }
  413. if (!empty($skills)) {
  414. foreach($skills as $skill) {
  415. $item['skills'] .= Display::span($skill['name'], array('class' => 'label_tag skill'));
  416. }
  417. }
  418. $new_result[] = $item;
  419. }
  420. $result = $new_result;
  421. break;
  422. case 'get_event_email_template':
  423. $columns = array('subject', 'event_type_name', 'language_id', 'activated', 'actions');
  424. if(!in_array($sidx, $columns)) {
  425. $sidx = 'subject';
  426. }
  427. $result = Database::select('*', $obj->table, array('order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  428. $new_result = array();
  429. foreach ($result as $item) {
  430. $language_info = api_get_language_info($item['language_id']);
  431. $item['language_id'] = $language_info['english_name'];
  432. $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']);
  433. $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']);
  434. /*if (!$item['status']) {
  435. $item['name'] = '<font style="color:#AAA">'.$item['subject'].'</font>';
  436. }*/
  437. $new_result[] = $item;
  438. }
  439. $result = $new_result;
  440. break;
  441. case 'get_careers':
  442. $columns = array('name', 'description', 'actions');
  443. if(!in_array($sidx, $columns)) {
  444. $sidx = 'name';
  445. }
  446. $result = Database::select('*', $obj->table, array('order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  447. $new_result = array();
  448. foreach ($result as $item) {
  449. if (!$item['status']) {
  450. $item['name'] = '<font style="color:#AAA">'.$item['name'].'</font>';
  451. }
  452. $new_result[] = $item;
  453. }
  454. $result = $new_result;
  455. break;
  456. case 'get_promotions':
  457. $columns = array('name', 'career', 'description', 'actions');
  458. if(!in_array($sidx, $columns)) {
  459. $sidx = 'name';
  460. }
  461. $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"));
  462. $new_result = array();
  463. foreach($result as $item) {
  464. if (!$item['status']) {
  465. $item['name'] = '<font style="color:#AAA">'.$item['name'].'</font>';
  466. }
  467. $new_result[] = $item;
  468. }
  469. $result = $new_result;
  470. break;
  471. case 'get_grade_models':
  472. $columns = array('name', 'description', 'actions');
  473. if (!in_array($sidx, $columns)) {
  474. $sidx = 'name';
  475. }
  476. $result = Database::select('*', "$obj->table ", array('order' =>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  477. $new_result = array();
  478. foreach($result as $item) {
  479. $new_result[] = $item;
  480. }
  481. $result = $new_result;
  482. break;
  483. case 'get_usergroups':
  484. $columns = array('name', 'users', 'courses', 'sessions', 'actions');
  485. $result = Database::select('*', $obj->table, array('order'=>"name $sord", 'LIMIT'=> "$start , $limit"));
  486. $new_result = array();
  487. if (!empty($result)) {
  488. foreach ($result as $group) {
  489. $group['sessions'] = count($obj->get_sessions_by_usergroup($group['id']));
  490. $group['courses'] = count($obj->get_courses_by_usergroup($group['id']));
  491. $group['users'] = count($obj->get_users_by_usergroup($group['id']));
  492. $new_result[] = $group;
  493. }
  494. $result = $new_result;
  495. }
  496. $columns = array('name', 'users', 'courses','sessions');
  497. if(!in_array($sidx, $columns)) {
  498. $sidx = 'name';
  499. }
  500. //Multidimensional sort
  501. msort($result, $sidx);
  502. break;
  503. case 'get_extra_fields':
  504. $obj = new ExtraField($type);
  505. $columns = array('field_display_text', 'field_variable', 'field_type', 'field_changeable', 'field_visible', 'field_filter', 'field_order');
  506. $result = Database::select('*', $obj->table, array('order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  507. $new_result = array();
  508. if (!empty($result)) {
  509. foreach ($result as $item) {
  510. $item['field_type'] = $obj->get_field_type_by_id($item['field_type']);
  511. $item['field_changeable'] = $item['field_changeable'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
  512. $item['field_visible'] = $item['field_visible'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
  513. $item['field_filter'] = $item['field_filter'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
  514. $new_result[] = $item;
  515. }
  516. $result = $new_result;
  517. }
  518. break;
  519. case 'get_extra_field_options':
  520. $obj = new ExtraFieldOption($type);
  521. $columns = array('option_display_text', 'option_value', 'option_order');
  522. $result = Database::select('*', $obj->table, array('where' => array("field_id = ? " => $field_id),'order'=>"$sidx $sord", 'LIMIT'=> "$start , $limit"));
  523. /*$new_result = array();
  524. if (!empty($result)) {
  525. foreach ($result as $item) {
  526. $item['field_type'] = $obj->get_field_type_by_id($item['field_type']);
  527. $item['field_changeable'] = $item['field_changeable'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
  528. $item['field_visible'] = $item['field_visible'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
  529. $item['field_filter'] = $item['field_filter'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
  530. $new_result[] = $item;
  531. }
  532. $result = $new_result;
  533. }*/
  534. break;
  535. case 'get_usergroups_teacher':
  536. $columns = array('name', 'users', 'actions');
  537. $options = array('order'=>"name $sord", 'LIMIT'=> "$start , $limit");
  538. $options['course_id'] = $course_id;
  539. switch ($type) {
  540. case 'not_registered':
  541. $options['where'] = array(" (course_id IS NULL OR course_id != ?) " => $course_id);
  542. $result = $obj->get_usergroup_not_in_course($options);
  543. break;
  544. case 'registered':
  545. $options['where'] = array(" usergroup.course_id = ? " => $course_id);
  546. $result = $obj->get_usergroup_in_course($options);
  547. break;
  548. }
  549. $new_result = array();
  550. if (!empty($result)) {
  551. foreach ($result as $group) {
  552. $group['users'] = count($obj->get_users_by_usergroup($group['id']));
  553. if ($obj->usergroup_was_added_in_course($group['id'], $course_id)) {
  554. $url = 'class.php?action=remove_class_from_course&id='.$group['id'];
  555. $icon = Display::return_icon('delete.png', get_lang('Remove'));
  556. } else {
  557. $url = 'class.php?action=add_class_to_course&id='.$group['id'];
  558. $icon = Display::return_icon('add.png', get_lang('Add'));
  559. }
  560. $group['actions'] = Display::url($icon, $url);
  561. $new_result[] = $group;
  562. }
  563. $result = $new_result;
  564. }
  565. if(!in_array($sidx, $columns)) {
  566. $sidx = 'name';
  567. }
  568. //Multidimensional sort
  569. msort($result, $sidx);
  570. break;
  571. default:
  572. exit;
  573. }
  574. $allowed_actions = array(
  575. 'get_careers',
  576. 'get_promotions',
  577. 'get_usergroups',
  578. 'get_usergroups_teacher',
  579. 'get_gradebooks',
  580. 'get_sessions',
  581. 'get_exercise_results',
  582. 'get_hotpotatoes_exercise_results',
  583. 'get_work_user_list',
  584. 'get_timelines',
  585. 'get_grade_models',
  586. 'get_event_email_template',
  587. 'get_user_skill_ranking',
  588. 'get_extra_fields',
  589. 'get_extra_field_options',
  590. 'get_course_exercise_medias',
  591. 'get_user_course_report',
  592. 'get_user_course_report_resumed',
  593. 'get_group_reporting'
  594. );
  595. //5. Creating an obj to return a json
  596. if (in_array($action, $allowed_actions)) {
  597. $response = new stdClass();
  598. $response->page = $page;
  599. $response->total = $total_pages;
  600. $response->records = $count;
  601. if ($operation && $operation == 'excel') {
  602. $j = 1;
  603. require_once api_get_path(LIBRARY_PATH).'export.lib.inc.php';
  604. $array = array();
  605. if (empty($column_names)) {
  606. $column_names = $columns;
  607. }
  608. //Headers
  609. foreach ($column_names as $col) {
  610. $array[0][] = $col;
  611. }
  612. foreach ($result as $row) {
  613. foreach ($columns as $col) {
  614. $array[$j][] = strip_tags($row[$col]);
  615. }
  616. $j++;
  617. }
  618. switch ($export_format) {
  619. case 'xls':
  620. Export::export_table_xls($array, 'company_report');
  621. break;
  622. case 'csv':
  623. default:
  624. Export::export_table_csv($array, 'company_report');
  625. break;
  626. }
  627. exit;
  628. }
  629. $i = 0;
  630. if (!empty($result)) {
  631. foreach ($result as $row) {
  632. //print_r($row);
  633. // if results tab give not id, set id to $i otherwise id="null" for all <tr> of the jqgrid - ref #4235
  634. if ($row['id'] == "") {
  635. $response->rows[$i]['id']=$i;
  636. } else {
  637. $response->rows[$i]['id']=$row['id'];
  638. }
  639. $array = array();
  640. foreach ($columns as $col) {
  641. $array[] = isset($row[$col]) ? $row[$col] : null;
  642. }
  643. $response->rows[$i]['cell']=$array;
  644. $i++;
  645. }
  646. }
  647. echo json_encode($response);
  648. }
  649. exit;