attendance.lib.php 39 KB

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