model.ajax.php 29 KB

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