attendance.lib.php 36 KB

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