model.ajax.php 32 KB

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