attendance.lib.php 33 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This file contains class used like library, provides functions for attendance tool. It's also used like model to attendance_controller (MVC pattern)
  5. * @author Christian Fasanando <christian1827@gmail.com>
  6. * @package chamilo.attendance
  7. */
  8. /**
  9. * Attendance can be used to instanciate objects or as a library to manage attendances
  10. * @package chamilo.attendance
  11. */
  12. class Attendance
  13. {
  14. private $session_id;
  15. private $course_id;
  16. private $date_time;
  17. private $name;
  18. private $description;
  19. private $attendance_qualify_title;
  20. private $attendance_weight;
  21. public function __construct() {}
  22. /**
  23. * Get the total number of attendance inside current course and current session
  24. * @see SortableTable#get_total_number_of_items()
  25. */
  26. function get_number_of_attendances() {
  27. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  28. $session_id = api_get_session_id();
  29. $sql = "SELECT COUNT(att.id) AS total_number_of_items FROM $tbl_attendance att WHERE att.active = 1 = att.session_id = '$session_id' ";
  30. $res = Database::query($sql);
  31. $res = Database::query($sql);
  32. $obj = Database::fetch_object($res);
  33. return $obj->total_number_of_items;
  34. }
  35. /**
  36. * Get attendance list
  37. * @param string course code (optional)
  38. * @param int session id (optional)
  39. * @return array attendances list
  40. */
  41. function get_attendances_list($course_code = '', $session_id = null) {
  42. // initializing database table and variables
  43. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  44. $data = array();
  45. if (!empty($course_code)) {
  46. $course_info = api_get_course_info($course_code);
  47. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE, $course_info['dbName']);
  48. }
  49. $condition_session = '';
  50. if (isset($session_id)) {
  51. $session_id = intval($session_id);
  52. $condition_session = ' WHERE session_id = '.$session_id;
  53. }
  54. // get attendance data
  55. $sql = "SELECT * FROM $tbl_attendance $condition_session ";
  56. $rs = Database::query($sql);
  57. if (Database::num_rows($rs) > 0){
  58. while ($row = Database::fetch_array($rs)) {
  59. $data[$row['id']] = $row;
  60. }
  61. }
  62. return $data;
  63. }
  64. /**
  65. * Get the attendaces to display on the current page (fill the sortable-table)
  66. * @param int offset of first user to recover
  67. * @param int Number of users to get
  68. * @param int Column to sort on
  69. * @param string Order (ASC,DESC)
  70. * @see SortableTable#get_table_data($from)
  71. */
  72. function get_attendance_data($from, $number_of_items, $column, $direction) {
  73. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  74. $session_id = api_get_session_id();
  75. $column = intval($column);
  76. $from = intval($from);
  77. $number_of_items = intval($number_of_items);
  78. if (!in_array($direction, array('ASC','DESC'))) {
  79. $direction = 'ASC';
  80. }
  81. $sql = "SELECT
  82. att.id AS col0,
  83. att.name AS col1,
  84. att.description AS col2,
  85. att.attendance_qualify_max AS col3
  86. FROM $tbl_attendance att
  87. WHERE att.session_id = '$session_id' AND att.active = 1
  88. ORDER BY col$column $direction LIMIT $from,$number_of_items ";
  89. $res = Database::query($sql);
  90. $attendances = array ();
  91. $param_gradebook = '';
  92. if (isset($_SESSION['gradebook'])) {
  93. $param_gradebook = '&gradebook='.$_SESSION['gradebook'];
  94. }
  95. while ($attendance = Database::fetch_row($res)) {
  96. $student_param = '';
  97. if (api_is_drh() && ($_GET['student_id'])) {
  98. $student_param = '&student_id='.Security::remove_XSS($_GET['student_id']);
  99. }
  100. $attendance[1] = '<a href="index.php?'.api_get_cidreq().'&action=attendance_sheet_list&attendance_id='.$attendance[0].$param_gradebook.$student_param.'">'.$attendance[1].'</a>';
  101. $attendance[3] = '<center>'.$attendance[3].'</center>';
  102. if (api_is_allowed_to_edit(null, true)) {
  103. $actions = '';
  104. $actions .= '<center><a href="index.php?'.api_get_cidreq().'&action=attendance_edit&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('edit.gif',get_lang('Edit')).'</a>&nbsp;';
  105. $actions .= '<a onclick="javascript:if(!confirm(\''.get_lang('AreYouSureToDelete').'\')) return false;" href="index.php?'.api_get_cidreq().'&action=attendance_delete&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('delete.gif',get_lang('Delete')).'</a></center>';
  106. $attendances[] = array($attendance[0], $attendance[1], $attendance[2], $attendance[3],$actions);
  107. } else {
  108. $attendance[0] = '&nbsp;';
  109. $attendances[] = array($attendance[0], $attendance[1], $attendance[2], $attendance[3]);
  110. }
  111. }
  112. return $attendances;
  113. }
  114. /**
  115. * Get the attendaces by id to display on the current page
  116. * @param int attendance id
  117. * @return array attendance data
  118. */
  119. public function get_attendance_by_id($attendance_id) {
  120. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  121. $sesion_id = api_get_session_id();
  122. $attendance_id = intval($attendance_id);
  123. $attendance_data = array();
  124. $sql = "SELECT * FROM $tbl_attendance WHERE id = '$attendance_id'";
  125. $res = Database::query($sql);
  126. if (Database::num_rows($res) > 0) {
  127. while ($row = Database::fetch_array($res)) {
  128. $attendance_data = $row;
  129. }
  130. }
  131. return $attendance_data;
  132. }
  133. /**
  134. * add attendaces inside table
  135. * @param bool true for adding link in gradebook or false otherwise (optional)
  136. * @return int last attendance id
  137. */
  138. public function attendance_add($link_to_gradebook = false) {
  139. global $_course;
  140. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  141. $table_link = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  142. $session_id = api_get_session_id();
  143. $user_id = api_get_user_id();
  144. $course_code = api_get_course_id();
  145. $title_gradebook= Database::escape_string($this->attendance_qualify_title);
  146. $value_calification = 0;
  147. $weight_calification = floatval($this->attendance_weight);
  148. $sql = "INSERT INTO $tbl_attendance
  149. SET name ='".Database::escape_string($this->name)."',
  150. description = '".Database::escape_string($this->description)."',
  151. attendance_qualify_title = '$title_gradebook',
  152. attendance_weight = '$weight_calification',
  153. session_id = '$session_id'";
  154. Database::query($sql);
  155. $affected_rows = Database::affected_rows();
  156. $last_id = 0;
  157. if (!empty($affected_rows)) {
  158. // save inside item property table
  159. $last_id = Database::insert_id();
  160. api_item_property_update($_course, TOOL_ATTENDANCE, $last_id,"AttendanceAdded", $user_id);
  161. }
  162. // add link to gradebook
  163. if ($link_to_gradebook) {
  164. $description = '';
  165. $link_id=is_resource_in_course_gradebook($course_code,7,$last_id,$session_id);
  166. if ($link_id==false) {
  167. add_resource_to_course_gradebook($course_code, 7, $last_id, $title_gradebook,$weight_calification,$value_calification,$description,time(),1,$session_id);
  168. } else {
  169. Database::query('UPDATE '.$table_link.' SET weight='.$weight_calification.' WHERE id='.$link_id.'');
  170. }
  171. }
  172. return $last_id;
  173. }
  174. /**
  175. * edit attendaces inside table
  176. * @param int attendance id
  177. * @param bool true for adding link in gradebook or false otherwise (optional)
  178. * @return int affected rows
  179. */
  180. public function attendance_edit($attendance_id, $link_to_gradebook = false) {
  181. global $_course;
  182. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  183. $table_link = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  184. $session_id = api_get_session_id();
  185. $user_id = api_get_user_id();
  186. $attendance_id = intval($attendance_id);
  187. $course_code = api_get_course_id();
  188. $title_gradebook = Database::escape_string($this->attendance_qualify_title);
  189. $value_calification = 0;
  190. $weight_calification = floatval($this->attendance_weight);
  191. $sql = "UPDATE $tbl_attendance
  192. SET name ='".Database::escape_string($this->name)."',
  193. description = '".Database::escape_string($this->description)."',
  194. attendance_qualify_title = '".$title_gradebook."',
  195. attendance_weight = '".$weight_calification."'
  196. WHERE id = '$attendance_id'";
  197. Database::query($sql);
  198. $affected_rows = Database::affected_rows();
  199. if (!empty($affected_rows)) {
  200. // update row item property table
  201. $last_id = Database::insert_id();
  202. api_item_property_update($_course, TOOL_ATTENDANCE, $attendance_id,"AttendanceUpdated", $user_id);
  203. }
  204. // add link to gradebook
  205. if ($link_to_gradebook) {
  206. $description = '';
  207. $link_id=is_resource_in_course_gradebook($course_code,7,$attendance_id,$session_id);
  208. if ($link_id==false) {
  209. add_resource_to_course_gradebook($course_code, 7, $attendance_id, $title_gradebook,$weight_calification,$value_calification,$description,time(),1,$session_id);
  210. } else {
  211. Database::query('UPDATE '.$table_link.' SET weight='.$weight_calification.' WHERE id='.$link_id.'');
  212. }
  213. }
  214. return $affected_rows;
  215. }
  216. /**
  217. * delete attendaces
  218. * @param int attendance id
  219. * @return int affected rows
  220. */
  221. public function attendance_delete($attendance_id) {
  222. global $_course;
  223. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  224. $user_id = api_get_user_id();
  225. if (is_array($attendance_id)) {
  226. foreach ($attendance_id as $id) {
  227. $id = intval($id);
  228. $sql = "UPDATE $tbl_attendance SET active = 0 WHERE id = '$id'";
  229. Database::query($sql);
  230. $affected_rows = Database::affected_rows();
  231. if (!empty($affected_rows)) {
  232. // update row item property table
  233. api_item_property_update($_course, TOOL_ATTENDANCE, $id,"delete", $user_id);
  234. }
  235. }
  236. } else {
  237. $attendance_id = intval($attendance_id);
  238. $sql = "UPDATE $tbl_attendance SET active = 0 WHERE id = '$attendance_id'";
  239. Database::query($sql);
  240. $affected_rows = Database::affected_rows();
  241. if (!empty($affected_rows)) {
  242. // update row item property table
  243. api_item_property_update($_course, TOOL_ATTENDANCE, $attendance_id,"delete", $user_id);
  244. }
  245. }
  246. return $affected_rows;
  247. }
  248. /**
  249. * get registered users inside current course
  250. * @param int attendance id for showing attendance result field (optional)
  251. * @return array users data
  252. */
  253. public function get_users_rel_course($attendance_id = 0) {
  254. $current_session_id = api_get_session_id();
  255. $current_course_id = api_get_course_id();
  256. if (!empty($current_session_id)) {
  257. $a_course_users = CourseManager :: get_user_list_from_course_code($current_course_id, true, $current_session_id,'','lastname');
  258. } else {
  259. $a_course_users = CourseManager :: get_user_list_from_course_code($current_course_id, false, 0, '','lastname');
  260. }
  261. // get registered users inside current course
  262. $a_users = array();
  263. foreach ($a_course_users as $key => $value) {
  264. $uid = intval($value['user_id']);
  265. $status = $value['status'];
  266. $user_status_in_course = CourseManager::get_user_in_course_status($uid, $current_course_id);
  267. if ($uid <= 1 || $status == DRH || $user_status_in_course == COURSEMANAGER) continue;
  268. if (!empty($attendance_id)) {
  269. $attendance_id = intval($attendance_id);
  270. $user_faults = $this->get_faults_of_user($uid, $attendance_id);
  271. $value['attendance_result'] = $user_faults['faults'].'/'.$user_faults['total'].' ('.$user_faults['faults_porcent'].'%)';
  272. $value['result_color_bar'] = $user_faults['color_bar'];
  273. }
  274. // user's picture
  275. $image_path = UserManager::get_user_picture_path_by_id($uid, 'web', false);
  276. $user_profile = UserManager::get_picture_user($uid, $image_path['file'], 22, USER_IMAGE_SIZE_SMALL, ' width="22" height="22" ');
  277. if (!api_is_anonymous()) {
  278. if (!empty($image_path['file'])) {
  279. $photo = '<center><a class="thickbox" href="'.$image_path['dir'].$image_path['file'].'" ><img src="'.$user_profile['file'].'" '.$user_profile['style'].' alt="'.api_get_person_name($value['firstname'], $value['lastname']).'" title="'.api_get_person_name($value['firstname'], $value['lastname']).'" /></a></center>';
  280. } else {
  281. $photo = '<center><img src="'.$user_profile['file'].'" '.$user_profile['style'].' alt="'.api_get_person_name($value['firstname'], $value['lastname']).'" title="'.api_get_person_name($value['firstname'], $value['lastname']).'" /></center>';
  282. }
  283. } else {
  284. $photo = '<center><img src="'.$user_profile['file'].'" '.$user_profile['style'].' alt="'.api_get_person_name($value['firstname'], $value['lastname']).'" title="'.api_get_person_name($value['firstname'], $value['lastname']).'" /></center>';
  285. }
  286. $value['photo'] = $photo;
  287. $a_users[$key] = $value;
  288. }
  289. return $a_users;
  290. }
  291. /**
  292. * add attendaces sheet inside table
  293. * @param int attendance calendar id
  294. * @param array present users during current class
  295. * @param int attendance id
  296. * @return int affected rows
  297. */
  298. public function attendance_sheet_add($calendar_id,$users_present,$attendance_id) {
  299. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  300. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  301. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  302. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  303. $calendar_id = intval($calendar_id);
  304. $attendance_id = intval($attendance_id);
  305. $users = $this->get_users_rel_course();
  306. $user_ids = array_keys($users);
  307. $users_absent = array_diff($user_ids,$users_present);
  308. $affected_rows = 0;
  309. // save users present in class
  310. foreach ($users_present as $user_present) {
  311. $uid = intval($user_present);
  312. // check if user already was registered with the $calendar_id
  313. $sql = "SELECT user_id FROM $tbl_attendance_sheet WHERE user_id='$uid' AND attendance_calendar_id = '$calendar_id'";
  314. $rs = Database::query($sql);
  315. if (Database::num_rows($rs) == 0) {
  316. $sql = "INSERT INTO $tbl_attendance_sheet SET user_id ='$uid', attendance_calendar_id = '$calendar_id', presence = 1";
  317. Database::query($sql);
  318. $affected_rows = Database::affected_rows();
  319. } else {
  320. $sql = "UPDATE $tbl_attendance_sheet SET presence = 1 WHERE user_id ='$uid' AND attendance_calendar_id = '$calendar_id'";
  321. Database::query($sql);
  322. $affected_rows = Database::affected_rows();
  323. }
  324. }
  325. // save users absent in class
  326. foreach ($users_absent as $user_absent) {
  327. $uid = intval($user_absent);
  328. // check if user already was registered with the $calendar_id
  329. $sql = "SELECT user_id FROM $tbl_attendance_sheet WHERE user_id='$uid' AND attendance_calendar_id = '$calendar_id'";
  330. $rs = Database::query($sql);
  331. if (Database::num_rows($rs) == 0) {
  332. $sql = "INSERT INTO $tbl_attendance_sheet SET user_id ='$uid', attendance_calendar_id = '$calendar_id', presence = 0";
  333. Database::query($sql);
  334. $affected_rows = Database::affected_rows();
  335. } else {
  336. $sql = "UPDATE $tbl_attendance_sheet SET presence = 0 WHERE user_id ='$uid' AND attendance_calendar_id = '$calendar_id'";
  337. Database::query($sql);
  338. $affected_rows = Database::affected_rows();
  339. }
  340. }
  341. // update done_attendance inside attendance calendar table
  342. $sql = "UPDATE $tbl_attendance_calendar SET done_attendance = 1 WHERE id = '$calendar_id'";
  343. Database::query($sql);
  344. // save users' results
  345. $this->update_users_results($user_ids, $attendance_id);
  346. return $affected_rows;
  347. }
  348. /**
  349. * update users' attendance results
  350. * @param array registered users inside current course
  351. * @param int attendance id
  352. * @return void
  353. */
  354. public function update_users_results($user_ids, $attendance_id) {
  355. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  356. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  357. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  358. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  359. $attendance_id = intval($attendance_id);
  360. // fill results about presence of students
  361. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  362. $calendar_ids = array();
  363. // get all dates from calendar by current attendance
  364. foreach ($attendance_calendar as $cal) {
  365. $calendar_ids[] = $cal['id'];
  366. }
  367. // get count of presences by users inside current attendance and save like results
  368. $count_presences = 0;
  369. if (count($user_ids) > 0) {
  370. foreach ($user_ids as $uid) {
  371. $count_presences = 0;
  372. if (count($calendar_ids) > 0) {
  373. $sql = "SELECT count(presence) as count_presences FROM $tbl_attendance_sheet WHERE user_id = '$uid' AND attendance_calendar_id IN(".implode(',',$calendar_ids).") AND presence = 1";
  374. $rs_count = Database::query($sql);
  375. $row_count = Database::fetch_array($rs_count);
  376. $count_presences = $row_count['count_presences'];
  377. }
  378. // save results
  379. $sql = "SELECT id FROM $tbl_attendance_result WHERE user_id='$uid' AND attendance_id='$attendance_id'";
  380. $rs_check_result = Database::query($sql);
  381. if (Database::num_rows($rs_check_result) > 0) {
  382. // update result
  383. $sql = "UPDATE $tbl_attendance_result SET score='$count_presences' WHERE user_id='$uid' AND attendance_id='$attendance_id'";
  384. Database::query($sql);
  385. } else {
  386. // insert new result
  387. $sql = "INSERT INTO $tbl_attendance_result SET user_id='$uid', attendance_id = '$attendance_id', score='$count_presences'";
  388. Database::query($sql);
  389. }
  390. }
  391. }
  392. // update attendance qualify max
  393. $count_done_calendar = $this->get_done_attendance_calendar($attendance_id);
  394. $sql = "UPDATE $tbl_attendance SET attendance_qualify_max='$count_done_calendar' WHERE id = '$attendance_id'";
  395. Database::query($sql);
  396. }
  397. /**
  398. * Get number of done attendances inside current sheet
  399. * @param int attendance id
  400. * @return int number of done attendances
  401. */
  402. public function get_done_attendance_calendar($attendance_id) {
  403. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  404. $attendance_id = intval($attendance_id);
  405. $sql = "SELECT count(done_attendance) as count FROM $tbl_attendance_calendar WHERE attendance_id = '$attendance_id' AND done_attendance=1";
  406. $rs = Database::query($sql);
  407. $row = Database::fetch_array($rs);
  408. $count = $row['count'];
  409. return $count;
  410. }
  411. /**
  412. * Get results of faults (absents) by user
  413. * @param int user id
  414. * @param int attendance id
  415. * @return array results containing number of faults, total done attendance, porcent of faults and color depend on result (red, orange)
  416. */
  417. public function get_faults_of_user($user_id, $attendance_id) {
  418. // initializing database table and variables
  419. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  420. $user_id = intval($user_id);
  421. $attendance_id = intval($attendance_id);
  422. $results = array();
  423. $attendance_data = $this->get_attendance_by_id($attendance_id);
  424. $total_done_attendance = $attendance_data['attendance_qualify_max'];
  425. $attendance_user_score = $this->get_user_score($user_id, $attendance_id);
  426. // calculate results
  427. $faults = $total_done_attendance-$attendance_user_score;
  428. $faults = $faults > 0 ? $faults:0;
  429. $faults_porcent = $total_done_attendance > 0 ?round(($faults*100)/$total_done_attendance,0):0;
  430. $results['faults'] = $faults;
  431. $results['total'] = $total_done_attendance;
  432. $results['faults_porcent'] = $faults_porcent;
  433. $color_bar = '';
  434. if ($faults_porcent > 25 ) {
  435. $color_bar = '#F11';
  436. } else if ($faults_porcent > 10) {
  437. $color_bar = '#F90';
  438. }
  439. $results['color_bar'] = $color_bar;
  440. return $results;
  441. }
  442. /**
  443. * Get results of faults average for all courses by user
  444. * @param int user id
  445. * @return array results containing number of faults, total done attendance, porcent of faults and color depend on result (red, orange)
  446. */
  447. public function get_faults_average_inside_courses($user_id) {
  448. // get all courses of current user
  449. $courses = CourseManager::get_courses_list_by_user_id($user_id, true);
  450. $user_id = intval($user_id);
  451. $results = array();
  452. $total_faults = $total_weight = $porcent = 0;
  453. foreach ($courses as $course) {
  454. $course_code = $course['code'];
  455. $course_info = api_get_course_info($course_code);
  456. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT, $course_info['dbName']);
  457. $attendances_by_course = $this->get_attendances_list($course_code);
  458. foreach ($attendances_by_course as $attendance) {
  459. // get total faults and total weight
  460. $attendance_id = $attendance['id'];
  461. $total_done_attendance = $attendance['attendance_qualify_max'];
  462. $sql = "SELECT score FROM $tbl_attendance_result WHERE user_id='$user_id' AND attendance_id='$attendance_id'";
  463. $rs = Database::query($sql);
  464. $score = 0;
  465. if (Database::num_rows($rs) > 0) {
  466. $row = Database::fetch_array($rs);
  467. $score = $row['score'];
  468. }
  469. $faults = $total_done_attendance-$score;
  470. $faults = $faults > 0 ? $faults:0;
  471. $total_faults += $faults;
  472. $total_weight += $total_done_attendance;
  473. }
  474. }
  475. $porcent = $total_weight > 0 ?round(($total_faults*100)/$total_weight,0):0;
  476. $results['faults'] = $total_faults;
  477. $results['total'] = $total_weight;
  478. $results['porcent'] = $porcent;
  479. return $results;
  480. }
  481. /**
  482. * Get results of faults average by course
  483. * @param int user id
  484. * @param int Session id (optional)
  485. * @return array results containing number of faults, total done attendance, porcent of faults and color depend on result (red, orange)
  486. */
  487. public function get_faults_average_by_course($user_id, $course_code, $session_id = null) {
  488. // Database tables and variables
  489. $course_info = api_get_course_info($course_code);
  490. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT, $course_info['dbName']);
  491. $user_id = intval($user_id);
  492. $results = array();
  493. $total_faults = $total_weight = $porcent = 0;
  494. $attendances_by_course = $this->get_attendances_list($course_code, $session_id);
  495. foreach ($attendances_by_course as $attendance) {
  496. // get total faults and total weight
  497. $attendance_id = $attendance['id'];
  498. $total_done_attendance = $attendance['attendance_qualify_max'];
  499. $sql = "SELECT score FROM $tbl_attendance_result WHERE user_id='$user_id' AND attendance_id='$attendance_id'";
  500. $rs = Database::query($sql);
  501. $score = 0;
  502. if (Database::num_rows($rs) > 0) {
  503. $row = Database::fetch_array($rs);
  504. $score = $row['score'];
  505. }
  506. $faults = $total_done_attendance-$score;
  507. $faults = $faults > 0 ? $faults:0;
  508. $total_faults += $faults;
  509. $total_weight += $total_done_attendance;
  510. }
  511. $porcent = $total_weight > 0 ?round(($total_faults*100)/$total_weight,0):0;
  512. $results['faults'] = $total_faults;
  513. $results['total'] = $total_weight;
  514. $results['porcent'] = $porcent;
  515. return $results;
  516. }
  517. /**
  518. * Get registered users' attendance sheet inside current course
  519. * @param int attendance id
  520. * @param int user id for showing data for only one user (optional)
  521. * @return array users attendance sheet data
  522. */
  523. public function get_users_attendance_sheet($attendance_id, $user_id = 0) {
  524. global $dateTimeFormatLong;
  525. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  526. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  527. $attendance_id = intval($attendance_id);
  528. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  529. $calendar_ids = array();
  530. // get all dates from calendar by current attendance
  531. foreach ($attendance_calendar as $cal) {
  532. $calendar_ids[] = $cal['id'];
  533. }
  534. $data = array();
  535. if (empty($user_id)) {
  536. // get all registered users inside current course
  537. $users = $this->get_users_rel_course();
  538. $user_ids = array_keys($users);
  539. if (count($calendar_ids) > 0 && count($user_ids) > 0) {
  540. foreach ($user_ids as $uid) {
  541. $sql = "SELECT * FROM $tbl_attendance_sheet WHERE user_id = '$uid' AND attendance_calendar_id IN(".implode(',',$calendar_ids).") ";
  542. $res = Database::query($sql);
  543. if (Database::num_rows($res) > 0) {
  544. while ($row = Database::fetch_array($res)) {
  545. $data[$uid][$row['attendance_calendar_id']]['presence'] = $row['presence'];
  546. }
  547. }
  548. }
  549. }
  550. } else {
  551. // get attendance for current user
  552. $user_id = intval($user_id);
  553. if (count($calendar_ids) > 0) {
  554. $sql = "SELECT cal.date_time, att.presence FROM $tbl_attendance_sheet att INNER JOIN $tbl_attendance_calendar cal ON cal.id = att.attendance_calendar_id WHERE att.user_id = '$user_id' AND att.attendance_calendar_id IN(".implode(',',$calendar_ids).") ";
  555. $res = Database::query($sql);
  556. if (Database::num_rows($res) > 0) {
  557. while ($row = Database::fetch_array($res)) {
  558. $row['date_time'] = api_convert_and_format_date($row['date_time'], null, date_default_timezone_get());
  559. $data[$user_id][] = $row;
  560. }
  561. }
  562. }
  563. }
  564. return $data;
  565. }
  566. /**
  567. * Get next attendance calendar without presences (done attendances)
  568. * @param int attendance id
  569. * @return int attendance calendar id
  570. */
  571. public function get_next_attendance_calendar_id($attendance_id) {
  572. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  573. $attendance_id = intval($attendance_id);
  574. $sql = "SELECT id FROM $tbl_attendance_calendar WHERE attendance_id = '$attendance_id' AND done_attendance = 0 ORDER BY date_time limit 1";
  575. $rs = Database::query($sql);
  576. $next_calendar_id = 0;
  577. if (Database::num_rows($rs) > 0) {
  578. $row = Database::fetch_array($rs);
  579. $next_calendar_id = $row['id'];
  580. }
  581. return $next_calendar_id;
  582. }
  583. /**
  584. * Get user' score from current attendance
  585. * @param int user id
  586. * @param int attendance id
  587. * @return int score
  588. */
  589. public function get_user_score($user_id, $attendance_id) {
  590. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  591. $user_id = intval($user_id);
  592. $attendance_id = intval($attendance_id);
  593. $sql = "SELECT score FROM $tbl_attendance_result WHERE user_id='$user_id' AND attendance_id='$attendance_id'";
  594. $rs = Database::query($sql);
  595. $score = 0;
  596. if (Database::num_rows($rs) > 0) {
  597. $row = Database::fetch_array($rs);
  598. $score = $row['score'];
  599. }
  600. return $score;
  601. }
  602. /**
  603. * Get attendance calendar data by id
  604. * @param int attendance calendar id
  605. * @return array attendance calendar data
  606. */
  607. public function get_attendance_calendar_by_id($calendar_id) {
  608. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  609. $calendar_id = intval($calendar_id);
  610. $sql = "SELECT * FROM $tbl_attendance_calendar WHERE id = '$calendar_id' ";
  611. $rs = Database::query($sql);
  612. $data = array();
  613. if (Database::num_rows($rs) > 0) {
  614. while ($row = Database::fetch_array($rs)) {
  615. $row['date_time'] = api_get_local_time($row['date_time']);
  616. $data = $row;
  617. }
  618. }
  619. return $data;
  620. }
  621. /**
  622. * Get all attendance calendar data inside current attendance
  623. * @param int attendance id
  624. * @return array attendance calendar data
  625. */
  626. public function get_attendance_calendar($attendance_id) {
  627. global $dateFormatShort, $timeNoSecFormat;
  628. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  629. $attendance_id = intval($attendance_id);
  630. $sql = "SELECT * FROM $tbl_attendance_calendar WHERE attendance_id = '$attendance_id' ORDER BY date_time ";
  631. $rs = Database::query($sql);
  632. $data = array();
  633. if (Database::num_rows($rs) > 0) {
  634. while ($row = Database::fetch_array($rs)) {
  635. $row['date_time'] = api_get_local_time($row['date_time']);
  636. $row['date'] = api_format_date($row['date_time'], DATE_FORMAT_SHORT);
  637. $row['time'] = api_format_date($row['date_time'], TIME_NO_SEC_FORMAT);
  638. $data[] = $row;
  639. }
  640. }
  641. return $data;
  642. }
  643. /**
  644. * Add new datetime inside attendance calendar table
  645. * @param int attendance id
  646. * @return int affected rows
  647. */
  648. public function attendant_calendar_add($attendance_id) {
  649. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  650. $affected_rows = 0;
  651. $attendance_id = intval($attendance_id);
  652. // check if datetime already exists inside the table
  653. $sql = "SELECT id FROM $tbl_attendance_calendar WHERE date_time='".Database::escape_string($this->date_time)."' AND attendance_id = '$attendance_id'";
  654. $rs = Database::query($sql);
  655. if (Database::num_rows($rs) == 0) {
  656. $sql = "INSERT INTO $tbl_attendance_calendar SET date_time='".Database::escape_string($this->date_time)."', attendance_id = '$attendance_id'";
  657. Database::query($sql);
  658. $affected_rows = Database::affected_rows();
  659. }
  660. return $affected_rows;
  661. }
  662. /**
  663. * edit a datetime inside attendance calendar table
  664. * @param int attendance calendar id
  665. * @param int attendance id
  666. * @return int affected rows
  667. */
  668. public function attendant_calendar_edit($calendar_id, $attendance_id) {
  669. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  670. $affected_rows = 0;
  671. $attendance_id = intval($attendance_id);
  672. // check if datetime already exists inside the table
  673. $sql = "SELECT id FROM $tbl_attendance_calendar WHERE date_time = '".Database::escape_string($this->date_time)."' AND attendance_id = '$attendance_id'";
  674. $rs = Database::query($sql);
  675. if (Database::num_rows($rs) == 0) {
  676. $sql = "UPDATE $tbl_attendance_calendar SET date_time='".Database::escape_string($this->date_time)."' WHERE id = '".intval($calendar_id)."'";
  677. Database::query($sql);
  678. $affected_rows = Database::affected_rows();
  679. }
  680. return $affected_rows;
  681. }
  682. /**
  683. * delete a datetime from attendance calendar table
  684. * @param int attendance calendar id
  685. * @param int attendance id
  686. * @param bool true for removing all calendar inside current attendance, false for removing by calendar id
  687. * @return int affected rows
  688. */
  689. public function attendance_calendar_delete($calendar_id, $attendance_id , $all_delete = false) {
  690. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  691. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  692. $session_id = api_get_session_id();
  693. $attendance_id = intval($attendance_id);
  694. // get all registered users inside current course
  695. $users = $this->get_users_rel_course();
  696. $user_ids = array_keys($users);
  697. if ($all_delete) {
  698. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  699. $calendar_ids = array();
  700. // get all dates from calendar by current attendance
  701. if (!empty($attendance_calendar)) {
  702. foreach ($attendance_calendar as $cal) {
  703. // delete all data from attendance sheet
  704. $sql = "DELETE FROM $tbl_attendance_sheet WHERE attendance_calendar_id = '".intval($cal['id'])."'";
  705. Database::query($sql);
  706. // delete data from attendance calendar
  707. $sql = "DELETE FROM $tbl_attendance_calendar WHERE id = '".intval($cal['id'])."'";
  708. Database::query($sql);
  709. }
  710. }
  711. } else {
  712. // delete just one row from attendance sheet by the calendar id
  713. $sql = "DELETE FROM $tbl_attendance_sheet WHERE attendance_calendar_id = '".intval($calendar_id)."'";
  714. Database::query($sql);
  715. // delete data from attendance calendar
  716. $sql = "DELETE FROM $tbl_attendance_calendar WHERE id = '".intval($calendar_id)."'";
  717. Database::query($sql);
  718. }
  719. $affected_rows = Database::affected_rows();
  720. // update users' results
  721. $this->update_users_results($user_ids, $attendance_id);
  722. return $affected_rows;
  723. }
  724. /**
  725. * buid a string datetime from array
  726. * @param array array containing data e.g: $array('Y'=>'2010', 'F' => '02', 'd' => '10', 'H' => '12', 'i' => '30')
  727. * @return string date and time e.g: '2010-02-10 12:30:00'
  728. */
  729. public function build_datetime_from_array($array) {
  730. $year = '0000';
  731. $month = $day = $hours = $minutes = $seconds = '00';
  732. if (isset($array['Y']) && isset($array['F']) && isset($array['d']) && isset($array['H']) && isset($array['i'])) {
  733. $year = $array['Y'];
  734. $month = $array['F'];
  735. if (intval($month) < 10 ) $month = '0'.$month;
  736. $day = $array['d'];
  737. if (intval($day) < 10 ) $day = '0'.$day;
  738. $hours = $array['H'];
  739. if (intval($hours) < 10 ) $hours = '0'.$hours;
  740. $minutes = $array['i'];
  741. if (intval($minutes) < 10 ) $minutes = '0'.$minutes;
  742. }
  743. if (checkdate($month,$day,$year)) {
  744. $datetime = $year.'-'.$month.'-'.$day.' '.$hours.':'.$minutes.':'.$seconds;
  745. }
  746. return $datetime;
  747. }
  748. /** Setters for fields of attendances tables **/
  749. public function set_session_id($session_id) {
  750. $this->session_id = $session_id;
  751. }
  752. public function set_course_id($course_id) {
  753. $this->course_id = $course_id;
  754. }
  755. public function set_date_time($datetime) {
  756. $this->date_time = $datetime;
  757. }
  758. public function set_name($name) {
  759. $this->name = $name;
  760. }
  761. public function set_description($description) {
  762. $this->description = $description;
  763. }
  764. public function set_attendance_qualify_title($attendance_qualify_title) {
  765. $this->attendance_qualify_title = $attendance_qualify_title;
  766. }
  767. public function set_attendance_weight($attendance_weight) {
  768. $this->attendance_weight = $attendance_weight;
  769. }
  770. /** Getters for fields of attendances tables **/
  771. public function get_session_id() {
  772. return $this->session_id;
  773. }
  774. public function get_course_id() {
  775. return $this->course_id;
  776. }
  777. public function get_date_time() {
  778. return $this->date_time;
  779. }
  780. public function get_name($name) {
  781. return $this->name;
  782. }
  783. public function get_description($description) {
  784. return $this->description;
  785. }
  786. public function get_attendance_qualify_title($attendance_qualify_title) {
  787. return $this->attendance_qualify_title;
  788. }
  789. public function get_attendance_weight($attendance_weight) {
  790. return $this->attendance_weight;
  791. }
  792. }
  793. ?>