attendance.lib.php 54 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357
  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. * @package chamilo.attendance
  12. */
  13. class Attendance
  14. {
  15. private $session_id;
  16. private $course_id;
  17. private $date_time;
  18. private $name;
  19. private $description;
  20. private $attendance_qualify_title;
  21. private $attendance_weight;
  22. private $course_int_id;
  23. // constants
  24. const DONE_ATTENDANCE_LOG_TYPE = 'done_attendance_sheet';
  25. const UPDATED_ATTENDANCE_LOG_TYPE = 'updated_attendance_sheet';
  26. const LOCKED_ATTENDANCE_LOG_TYPE = 'locked_attendance_sheet';
  27. public function __construct() {
  28. //$this->course_int_id = api_get_course_int_id();
  29. }
  30. /**
  31. * Get the total number of attendance inside current course and current session
  32. * @see SortableTable#get_total_number_of_items()
  33. */
  34. function get_number_of_attendances() {
  35. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  36. $session_id = api_get_session_id();
  37. $condition_session = api_get_session_condition($session_id);
  38. $course_id = api_get_course_int_id();
  39. $sql = "SELECT COUNT(att.id) AS total_number_of_items FROM $tbl_attendance att
  40. WHERE c_id = {$course_id} AND att.active = 1 $condition_session ";
  41. $res = Database::query($sql);
  42. $res = Database::query($sql);
  43. $obj = Database::fetch_object($res);
  44. return $obj->total_number_of_items;
  45. }
  46. /**
  47. * Get attendance list only the id, name and attendance_qualify_max fields
  48. * @param string course db name (optional)
  49. * @param int session id (optional)
  50. * @return array attendances list
  51. */
  52. function get_attendances_list($course_id = '', $session_id = null) {
  53. // Initializing database table and variables
  54. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  55. $data = array();
  56. if (empty($course_id)) {
  57. $course_id = api_get_course_int_id();
  58. } else {
  59. $course_id = intval($course_id);
  60. }
  61. $session_id = isset($session_id)?intval($session_id):api_get_session_id();
  62. $condition_session = api_get_session_condition($session_id);
  63. // Get attendance data
  64. $sql = "SELECT id, name, attendance_qualify_max FROM $tbl_attendance
  65. WHERE c_id = {$course_id} AND active = 1 $condition_session ";
  66. $rs = Database::query($sql);
  67. if (Database::num_rows($rs) > 0) {
  68. while ($row = Database::fetch_array($rs,'ASSOC')) {
  69. $data[$row['id']] = $row;
  70. }
  71. }
  72. return $data;
  73. }
  74. /**
  75. * Get the attendaces to display on the current page (fill the sortable-table)
  76. * @param int offset of first user to recover
  77. * @param int Number of users to get
  78. * @param int Column to sort on
  79. * @param string Order (ASC,DESC)
  80. * @see SortableTable#get_table_data($from)
  81. */
  82. function get_attendance_data($from, $number_of_items, $column, $direction) {
  83. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  84. $course_id = api_get_course_int_id();
  85. $session_id = api_get_session_id();
  86. $condition_session = api_get_session_condition($session_id);
  87. $column = intval($column);
  88. $from = intval($from);
  89. $number_of_items = intval($number_of_items);
  90. if (!in_array($direction, array('ASC','DESC'))) {
  91. $direction = 'ASC';
  92. }
  93. $active_plus = 'att.active = 1';
  94. if (api_is_platform_admin()) { $active_plus = ' 1=1 ';}
  95. $sql = "SELECT
  96. att.id AS col0,
  97. att.name AS col1,
  98. att.description AS col2,
  99. att.attendance_qualify_max AS col3,
  100. att.locked AS col4,
  101. att.active AS col5,
  102. att.session_id
  103. FROM $tbl_attendance att
  104. WHERE c_id = $course_id AND $active_plus $condition_session
  105. ORDER BY col$column $direction LIMIT $from,$number_of_items ";
  106. $res = Database::query($sql);
  107. $attendances = array ();
  108. $param_gradebook = '';
  109. if (isset($_SESSION['gradebook'])) {
  110. $param_gradebook = '&gradebook='.$_SESSION['gradebook'];
  111. }
  112. $user_info = api_get_user_info();
  113. while ($attendance = Database::fetch_row($res)) {
  114. $student_param = '';
  115. if (api_is_drh() && ($_GET['student_id'])) {
  116. $student_param = '&student_id='.Security::remove_XSS($_GET['student_id']);
  117. }
  118. $session_star = '';
  119. if (api_get_session_id() == $attendance[6]) {
  120. $session_star = api_get_session_image(api_get_session_id(), $user_info['status']);
  121. }
  122. if ($attendance[5] == 1) {
  123. $attendance[1] = '<a href="index.php?'.api_get_cidreq().'&action=attendance_sheet_list&attendance_id='.$attendance[0].$param_gradebook.$student_param.'">'.$attendance[1].'</a>'.$session_star;
  124. } else {
  125. $attendance[1] = '<a href="index.php?'.api_get_cidreq().'&action=attendance_sheet_list&attendance_id='.$attendance[0].$param_gradebook.$student_param.'"><del>'.$attendance[1].'</del></a>'.$session_star;
  126. }
  127. $attendance[3] = '<center>'.$attendance[3].'</center>';
  128. if (api_is_allowed_to_edit(null, true)) {
  129. $actions = '';
  130. $actions .= '<center>';
  131. if (api_is_platform_admin()) {
  132. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_edit&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('edit.png',get_lang('Edit'), array(), 22).'</a>&nbsp;';
  133. if ($attendance[5] == 1) {
  134. $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'), array(), 22).'</a>';
  135. } else {
  136. $actions .= '<a onclick="javascript:if(!confirm(\''.get_lang('AreYouSureToRestore').'\')) return false;" href="index.php?'.api_get_cidreq().'&action=attendance_restore&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('invisible.png',get_lang('Restore'), array(), 22).'</a>';
  137. $attendance[2] = '<del>'.$attendance[2].'</del>';
  138. }
  139. } else {
  140. $is_locked_attendance = self::is_locked_attendance($attendance[0]);
  141. if ($is_locked_attendance) {
  142. $actions .= Display::return_icon('edit_na.gif',get_lang('Edit')).'&nbsp;';
  143. $actions .= Display::return_icon('delete_na.gif',get_lang('Delete'));
  144. } else {
  145. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_edit&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('edit.png',get_lang('Edit'), array(), 22).'</a>&nbsp;';
  146. $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'), array(), 22).'</a>';
  147. }
  148. }
  149. // display lock/unlock icon
  150. $is_done_all_calendar = self::is_all_attendance_calendar_done($attendance[0]);
  151. if ($is_done_all_calendar) {
  152. $locked = $attendance[4];
  153. if ($locked == 0) {
  154. if (api_is_platform_admin()) {
  155. $message_alert = get_lang('AreYouSureToLockTheAttendance');
  156. } else {
  157. $message_alert = get_lang('UnlockMessageInformation');
  158. }
  159. $actions .= '&nbsp;<a onclick="javascript:if(!confirm(\''.$message_alert.'\')) return false;" href="index.php?'.api_get_cidreq().'&action=lock_attendance&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('unlock.png',get_lang('LockAttendance')).'</a>';
  160. } else {
  161. if (api_is_platform_admin()) {
  162. $actions .= '&nbsp;<a onclick="javascript:if(!confirm(\''.get_lang('AreYouSureToUnlockTheAttendance').'\')) return false;" href="index.php?'.api_get_cidreq().'&action=unlock_attendance&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('locked.png',get_lang('UnlockAttendance')).'</a>';
  163. } else {
  164. $actions .= '&nbsp;'.Display::return_icon('locked_na.png',get_lang('LockedAttendance'));
  165. }
  166. }
  167. }
  168. $actions .= '</center>';
  169. $attendances[] = array($attendance[0], $attendance[1], $attendance[2], $attendance[3],$actions);
  170. } else {
  171. $attendance[0] = '&nbsp;';
  172. $attendances[] = array($attendance[0], $attendance[1], $attendance[2], $attendance[3]);
  173. }
  174. }
  175. return $attendances;
  176. }
  177. /**
  178. * Get the attendaces by id to display on the current page
  179. * @param int attendance id
  180. * @return array attendance data
  181. */
  182. public function get_attendance_by_id($attendance_id) {
  183. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  184. $attendance_id = intval($attendance_id);
  185. $course_id = api_get_course_int_id();
  186. $attendance_data = array();
  187. $sql = "SELECT * FROM $tbl_attendance WHERE c_id = $course_id AND id = '$attendance_id'";
  188. $res = Database::query($sql);
  189. if (Database::num_rows($res) > 0) {
  190. while ($row = Database::fetch_array($res)) {
  191. $attendance_data = $row;
  192. }
  193. }
  194. return $attendance_data;
  195. }
  196. /**
  197. * add attendaces inside table
  198. * @param bool true for adding link in gradebook or false otherwise (optional)
  199. * @return int last attendance id
  200. */
  201. public function attendance_add($link_to_gradebook = false) {
  202. global $_course;
  203. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  204. $table_link = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  205. $session_id = api_get_session_id();
  206. $user_id = api_get_user_id();
  207. $course_code = api_get_course_id();
  208. $course_id = api_get_course_int_id();
  209. $title_gradebook= Database::escape_string($this->attendance_qualify_title);
  210. $value_calification = 0;
  211. $weight_calification = floatval($this->attendance_weight);
  212. $sql = "INSERT INTO $tbl_attendance SET
  213. c_id = $course_id,
  214. name ='".Database::escape_string($this->name)."',
  215. description = '".Database::escape_string($this->description)."',
  216. attendance_qualify_title = '$title_gradebook',
  217. attendance_weight = '$weight_calification',
  218. session_id = '$session_id'";
  219. Database::query($sql);
  220. $affected_rows = Database::affected_rows();
  221. $last_id = 0;
  222. if (!empty($affected_rows)) {
  223. // save inside item property table
  224. $last_id = Database::insert_id();
  225. api_item_property_update($_course, TOOL_ATTENDANCE, $last_id,"AttendanceAdded", $user_id);
  226. }
  227. // add link to gradebook
  228. if ($link_to_gradebook) {
  229. $description = '';
  230. $link_id=is_resource_in_course_gradebook($course_code,7,$last_id,$session_id);
  231. if (!$link_id) {
  232. add_resource_to_course_gradebook($course_code, 7, $last_id, $title_gradebook,$weight_calification,$value_calification,$description,time(),1,$session_id);
  233. } else {
  234. Database::query('UPDATE '.$table_link.' SET weight='.$weight_calification.' WHERE id='.$link_id.'');
  235. }
  236. }
  237. return $last_id;
  238. }
  239. /**
  240. * edit attendaces inside table
  241. * @param int attendance id
  242. * @param bool true for adding link in gradebook or false otherwise (optional)
  243. * @return int last id
  244. */
  245. public function attendance_edit($attendance_id, $link_to_gradebook = false) {
  246. global $_course;
  247. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  248. $table_link = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  249. $session_id = api_get_session_id();
  250. $user_id = api_get_user_id();
  251. $attendance_id = intval($attendance_id);
  252. $course_code = api_get_course_id();
  253. $course_id = api_get_course_int_id();
  254. $title_gradebook = Database::escape_string($this->attendance_qualify_title);
  255. $value_calification = 0;
  256. $weight_calification = floatval($this->attendance_weight);
  257. $sql = "UPDATE $tbl_attendance
  258. SET name ='".Database::escape_string($this->name)."',
  259. description = '".Database::escape_string($this->description)."',
  260. attendance_qualify_title = '".$title_gradebook."',
  261. attendance_weight = '".$weight_calification."'
  262. WHERE c_id = $course_id AND id = '$attendance_id'";
  263. Database::query($sql);
  264. if (!empty($affected_rows)) {
  265. // update row item property table
  266. $last_id = Database::insert_id();
  267. api_item_property_update($_course, TOOL_ATTENDANCE, $attendance_id,"AttendanceUpdated", $user_id);
  268. }
  269. // add link to gradebook
  270. if ($link_to_gradebook) {
  271. $description = '';
  272. $link_id=is_resource_in_course_gradebook($course_code,7,$attendance_id,$session_id);
  273. if (!$link_id) {
  274. add_resource_to_course_gradebook($course_code, 7, $attendance_id, $title_gradebook,$weight_calification,$value_calification,$description,time(),1,$session_id);
  275. } else {
  276. Database::query('UPDATE '.$table_link.' SET weight='.$weight_calification.' WHERE id='.$link_id.'');
  277. }
  278. }
  279. $last_id = $attendance_id;
  280. return $last_id;
  281. }
  282. /**
  283. * Restore attendance
  284. * @param int|array one or many attendances id
  285. * @return int affected rows
  286. */
  287. public function attendance_restore($attendance_id) {
  288. global $_course;
  289. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  290. $user_id = api_get_user_id();
  291. $course_id = api_get_course_int_id();
  292. if (is_array($attendance_id)) {
  293. foreach ($attendance_id as $id) {
  294. $id = intval($id);
  295. $sql = "UPDATE $tbl_attendance SET active = 1 WHERE c_id = $course_id AND id = '$id'";
  296. Database::query($sql);
  297. $affected_rows = Database::affected_rows();
  298. if (!empty($affected_rows)) {
  299. // update row item property table
  300. api_item_property_update($_course, TOOL_ATTENDANCE, $id,"restore", $user_id);
  301. }
  302. }
  303. } else {
  304. $attendance_id = intval($attendance_id);
  305. $sql = "UPDATE $tbl_attendance SET active = 1 WHERE c_id = $course_id AND id = '$attendance_id'";
  306. Database::query($sql);
  307. $affected_rows = Database::affected_rows();
  308. if (!empty($affected_rows)) {
  309. // update row item property table
  310. api_item_property_update($_course, TOOL_ATTENDANCE, $attendance_id,"restore", $user_id);
  311. }
  312. }
  313. return $affected_rows;
  314. }
  315. /**
  316. * delete attendaces
  317. * @param int|array one or many attendances id
  318. * @return int affected rows
  319. */
  320. public function attendance_delete($attendance_id) {
  321. global $_course;
  322. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  323. $user_id = api_get_user_id();
  324. $course_id = api_get_course_int_id();
  325. if (is_array($attendance_id)) {
  326. foreach ($attendance_id as $id) {
  327. $id = intval($id);
  328. $sql = "UPDATE $tbl_attendance SET active = 0 WHERE c_id = $course_id AND id = '$id'";
  329. Database::query($sql);
  330. $affected_rows = Database::affected_rows();
  331. if (!empty($affected_rows)) {
  332. // update row item property table
  333. api_item_property_update($_course, TOOL_ATTENDANCE, $id,"delete", $user_id);
  334. }
  335. }
  336. } else {
  337. $attendance_id = intval($attendance_id);
  338. $sql = "UPDATE $tbl_attendance SET active = 0 WHERE c_id = $course_id AND id = '$attendance_id'";
  339. Database::query($sql);
  340. $affected_rows = Database::affected_rows();
  341. if (!empty($affected_rows)) {
  342. // update row item property table
  343. api_item_property_update($_course, TOOL_ATTENDANCE, $attendance_id,"delete", $user_id);
  344. }
  345. }
  346. return $affected_rows;
  347. }
  348. /**
  349. * Lock or unlock an attendance
  350. * @param int attendance id
  351. * @param bool True to lock or false otherwise
  352. */
  353. public function lock_attendance($attendance_id, $lock = true) {
  354. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  355. $course_id = api_get_course_int_id();
  356. $attendance_id = intval($attendance_id);
  357. $locked = ($lock)?1:0;
  358. $upd = "UPDATE $tbl_attendance SET locked = $locked WHERE c_id = $course_id AND id = $attendance_id";
  359. Database::query($upd);
  360. $affected_rows = Database::affected_rows();
  361. if ($affected_rows && $lock) {
  362. //save attendance sheet log
  363. $lastedit_date = Date('Y-m-d H:i:s');
  364. $lastedit_type = self::LOCKED_ATTENDANCE_LOG_TYPE;
  365. $lastedit_user_id = api_get_user_id();
  366. $save_attendance_log = $this->save_attendance_sheet_log($attendance_id, $lastedit_date, $lastedit_type, $lastedit_user_id);
  367. }
  368. return $affected_rows;
  369. }
  370. /**
  371. * Get registered users inside current course
  372. * @param int attendance id for showing attendance result field (optional)
  373. * @return array users data
  374. */
  375. public function get_users_rel_course($attendance_id = 0) {
  376. $current_session_id = api_get_session_id();
  377. $current_course_id = api_get_course_id();
  378. if (!empty($current_session_id)) {
  379. $a_course_users = CourseManager :: get_user_list_from_course_code($current_course_id, true, $current_session_id,'','lastname');
  380. } else {
  381. $a_course_users = CourseManager :: get_user_list_from_course_code($current_course_id, false, 0, '','lastname');
  382. }
  383. // get registered users inside current course
  384. $a_users = array();
  385. foreach ($a_course_users as $key =>$user_data) {
  386. $value = array();
  387. $uid = $user_data['user_id'];
  388. $status = $user_data['status'];
  389. $user_status_in_session = null;
  390. $user_status_in_course = null;
  391. if (api_get_session_id()) {
  392. $user_status_in_session = SessionManager::get_user_status_in_session($uid, $current_course_id, $current_session_id);
  393. } else {
  394. $user_status_in_course = CourseManager::get_user_in_course_status($uid, $current_course_id);
  395. }
  396. //Not taking into account DRH or COURSEMANAGER
  397. if ($uid <= 1 || $status == DRH || $user_status_in_course == COURSEMANAGER || $user_status_in_session == 2) continue;
  398. if (!empty($attendance_id)) {
  399. $user_faults = $this->get_faults_of_user($uid, $attendance_id);
  400. $value['attendance_result'] = $user_faults['faults'].'/'.$user_faults['total'].' ('.$user_faults['faults_porcent'].'%)';
  401. $value['result_color_bar'] = $user_faults['color_bar'];
  402. }
  403. // user's picture
  404. $image_path = UserManager::get_user_picture_path_by_id($uid, 'web', false);
  405. $user_profile = UserManager::get_picture_user($uid, $image_path['file'], 22, USER_IMAGE_SIZE_SMALL, ' width="22" height="22" ');
  406. if (!empty($image_path['file'])) {
  407. $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>';
  408. } else {
  409. $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>';
  410. }
  411. $value['photo'] = $photo;
  412. $value['firstname'] = $user_data['firstname'];
  413. $value['lastname'] = $user_data['lastname'];
  414. $value['username'] = $user_data['username']; // hub
  415. $value['user_id'] = $uid;
  416. //Sending only 5 items in the array instead of 60
  417. $a_users[$key] = $value;
  418. }
  419. return $a_users;
  420. }
  421. /**
  422. * add attendaces sheet inside table
  423. * @param int attendance calendar id
  424. * @param array present users during current class
  425. * @param int attendance id
  426. * @return int affected rows
  427. */
  428. public function attendance_sheet_add($calendar_id,$users_present,$attendance_id) {
  429. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  430. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  431. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  432. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  433. $calendar_id = intval($calendar_id);
  434. $attendance_id = intval($attendance_id);
  435. $users = $this->get_users_rel_course();
  436. $course_id = api_get_course_int_id();
  437. $user_ids = array_keys($users);
  438. $users_absent = array_diff($user_ids,$users_present);
  439. $affected_rows = 0;
  440. // get last edit type
  441. $calendar_data = $this->get_attendance_calendar_by_id($calendar_id);
  442. $lastedit_type = self::DONE_ATTENDANCE_LOG_TYPE;
  443. if ($calendar_data['done_attendance']) {
  444. $lastedit_type = self::UPDATED_ATTENDANCE_LOG_TYPE;
  445. }
  446. // save users present in class
  447. foreach ($users_present as $user_present) {
  448. $uid = intval($user_present);
  449. // check if user already was registered with the $calendar_id
  450. $sql = "SELECT user_id FROM $tbl_attendance_sheet WHERE c_id = $course_id AND user_id='$uid' AND attendance_calendar_id = '$calendar_id'";
  451. $rs = Database::query($sql);
  452. if (Database::num_rows($rs) == 0) {
  453. $sql = "INSERT INTO $tbl_attendance_sheet SET
  454. c_id = $course_id,
  455. user_id = '$uid',
  456. attendance_calendar_id = '$calendar_id',
  457. presence = 1";
  458. Database::query($sql);
  459. $affected_rows += Database::affected_rows();
  460. } else {
  461. $sql = "UPDATE $tbl_attendance_sheet SET presence = 1 WHERE c_id = $course_id AND user_id ='$uid' AND attendance_calendar_id = '$calendar_id'";
  462. Database::query($sql);
  463. $affected_rows += Database::affected_rows();
  464. }
  465. }
  466. // save users absent in class
  467. foreach ($users_absent as $user_absent) {
  468. $uid = intval($user_absent);
  469. // check if user already was registered with the $calendar_id
  470. $sql = "SELECT user_id FROM $tbl_attendance_sheet WHERE c_id = $course_id AND user_id='$uid' AND attendance_calendar_id = '$calendar_id'";
  471. $rs = Database::query($sql);
  472. if (Database::num_rows($rs) == 0) {
  473. $sql = "INSERT INTO $tbl_attendance_sheet SET
  474. c_id = $course_id,
  475. user_id ='$uid',
  476. attendance_calendar_id = '$calendar_id',
  477. presence = 0";
  478. Database::query($sql);
  479. $affected_rows += Database::affected_rows();
  480. } else {
  481. $sql = "UPDATE $tbl_attendance_sheet SET presence = 0 WHERE c_id = $course_id AND user_id ='$uid' AND attendance_calendar_id = '$calendar_id'";
  482. Database::query($sql);
  483. $affected_rows += Database::affected_rows();
  484. }
  485. }
  486. // update done_attendance inside attendance calendar table
  487. $sql = "UPDATE $tbl_attendance_calendar SET done_attendance = 1 WHERE c_id = $course_id AND id = '$calendar_id'";
  488. Database::query($sql);
  489. // save users' results
  490. $this->update_users_results($user_ids, $attendance_id);
  491. if ($affected_rows) {
  492. //save attendance sheet log
  493. $lastedit_date = Date('Y-m-d H:i:s');
  494. $lastedit_user_id = api_get_user_id();
  495. $calendar_date_value = $calendar_data['date_time'];
  496. $save_attendance_log = $this->save_attendance_sheet_log($attendance_id, $lastedit_date, $lastedit_type, $lastedit_user_id, $calendar_date_value);
  497. }
  498. return $affected_rows;
  499. }
  500. /**
  501. * update users' attendance results
  502. * @param array registered users inside current course
  503. * @param int attendance id
  504. * @return void
  505. */
  506. public function update_users_results($user_ids, $attendance_id) {
  507. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  508. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  509. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  510. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  511. $course_id = api_get_course_int_id();
  512. $attendance_id = intval($attendance_id);
  513. // fill results about presence of students
  514. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  515. $calendar_ids = array();
  516. // get all dates from calendar by current attendance
  517. foreach ($attendance_calendar as $cal) {
  518. $calendar_ids[] = $cal['id'];
  519. }
  520. // get count of presences by users inside current attendance and save like results
  521. $count_presences = 0;
  522. if (count($user_ids) > 0) {
  523. foreach ($user_ids as $uid) {
  524. $count_presences = 0;
  525. if (count($calendar_ids) > 0) {
  526. $sql = "SELECT count(presence) as count_presences FROM $tbl_attendance_sheet
  527. WHERE c_id = $course_id AND user_id = '$uid' AND attendance_calendar_id IN(".implode(',',$calendar_ids).") AND presence = 1";
  528. $rs_count = Database::query($sql);
  529. $row_count = Database::fetch_array($rs_count);
  530. $count_presences = $row_count['count_presences'];
  531. }
  532. // save results
  533. $sql = "SELECT id FROM $tbl_attendance_result WHERE c_id = $course_id AND user_id='$uid' AND attendance_id='$attendance_id'";
  534. $rs_check_result = Database::query($sql);
  535. if (Database::num_rows($rs_check_result) > 0) {
  536. // update result
  537. $sql = "UPDATE $tbl_attendance_result SET
  538. score='$count_presences'
  539. WHERE c_id = $course_id AND user_id='$uid' AND attendance_id='$attendance_id'";
  540. Database::query($sql);
  541. } else {
  542. // insert new result
  543. $sql = "INSERT INTO $tbl_attendance_result SET
  544. c_id = $course_id ,
  545. user_id = '$uid',
  546. attendance_id = '$attendance_id',
  547. score = '$count_presences'";
  548. Database::query($sql);
  549. }
  550. }
  551. }
  552. // update attendance qualify max
  553. $count_done_calendar = $this->get_done_attendance_calendar($attendance_id);
  554. $sql = "UPDATE $tbl_attendance SET attendance_qualify_max='$count_done_calendar' WHERE c_id = $course_id AND id = '$attendance_id'";
  555. Database::query($sql);
  556. }
  557. /**
  558. * update attendance_sheet_log table, is used as history of an attendance sheet
  559. * @param int Attendance id
  560. * @param string Last edit datetime
  561. * @param string Event type ('locked_attendance', 'done_attendance_sheet' ...)
  562. * @param int Last edit user id
  563. * @param string Calendar datetime value (optional, when event type is 'done_attendance_sheet')
  564. * @return int Affected rows
  565. */
  566. public function save_attendance_sheet_log($attendance_id, $lastedit_date, $lastedit_type, $lastedit_user_id, $calendar_date_value = null) {
  567. $course_id = api_get_course_int_id();
  568. // define table
  569. $tbl_attendance_sheet_log = Database::get_course_table(TABLE_ATTENDANCE_SHEET_LOG);
  570. // protect data
  571. $attendance_id = intval($attendance_id);
  572. $lastedit_date = Database::escape_string($lastedit_date);
  573. $lastedit_type = Database::escape_string($lastedit_type);
  574. $lastedit_user_id = intval($lastedit_user_id);
  575. if (isset($calendar_date_value)) {
  576. $calendar_date_value = Database::escape_string($calendar_date_value);
  577. } else {
  578. $calendar_date_value = '';
  579. }
  580. // save data
  581. $ins = "INSERT INTO $tbl_attendance_sheet_log(c_id, attendance_id, lastedit_date, lastedit_type, lastedit_user_id, calendar_date_value)
  582. VALUES($course_id, $attendance_id, '$lastedit_date', '$lastedit_type', $lastedit_user_id, '$calendar_date_value')";
  583. Database::query($ins);
  584. return Database::affected_rows();
  585. }
  586. /**
  587. * Get number of done attendances inside current sheet
  588. * @param int attendance id
  589. * @return int number of done attendances
  590. */
  591. public function get_done_attendance_calendar($attendance_id) {
  592. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  593. $attendance_id = intval($attendance_id);
  594. $course_id = api_get_course_int_id();
  595. $sql = "SELECT count(done_attendance) as count FROM $tbl_attendance_calendar WHERE c_id = {$course_id} AND attendance_id = '$attendance_id' AND done_attendance=1";
  596. $rs = Database::query($sql);
  597. $row = Database::fetch_array($rs);
  598. $count = $row['count'];
  599. return $count;
  600. }
  601. /**
  602. * Get results of faults (absents) by user
  603. * @param int user id
  604. * @param int attendance id
  605. * @return array results containing number of faults, total done attendance, porcent of faults and color depend on result (red, orange)
  606. */
  607. public function get_faults_of_user($user_id, $attendance_id) {
  608. // initializing database table and variables
  609. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  610. $user_id = intval($user_id);
  611. $attendance_id = intval($attendance_id);
  612. $results = array();
  613. $attendance_data = $this->get_attendance_by_id($attendance_id);
  614. $calendar_count = $this->get_number_of_attendance_calendar($attendance_id);
  615. $total_done_attendance = $attendance_data['attendance_qualify_max'];
  616. $attendance_user_score = $this->get_user_score($user_id, $attendance_id);
  617. //This is the main change of the BT#1381
  618. //$total_done_attendance = $calendar_count;
  619. // calculate results
  620. $faults = $total_done_attendance - $attendance_user_score;
  621. $faults = $faults > 0 ? $faults:0;
  622. $faults_porcent = $calendar_count > 0 ?round(($faults*100)/$calendar_count,0):0;
  623. $results['faults'] = $faults;
  624. $results['total'] = $calendar_count;
  625. $results['faults_porcent'] = $faults_porcent;
  626. $color_bar = '';
  627. if ($faults_porcent > 25 ) {
  628. $color_bar = '#F11';
  629. } else if ($faults_porcent > 10) {
  630. $color_bar = '#F90';
  631. }
  632. $results['color_bar'] = $color_bar;
  633. return $results;
  634. }
  635. /**
  636. * Get results of faults average for all courses by user
  637. * @param int user id
  638. * @return array results containing number of faults, total done attendance, porcent of faults and color depend on result (red, orange)
  639. */
  640. public function get_faults_average_inside_courses($user_id) {
  641. // get all courses of current user
  642. $courses = CourseManager::get_courses_list_by_user_id($user_id, true);
  643. $user_id = intval($user_id);
  644. $results = array();
  645. $total_faults = $total_weight = $porcent = 0;
  646. foreach ($courses as $course) {
  647. //$course_code = $course['code'];
  648. //$course_info = api_get_course_info($course_code);
  649. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  650. $attendances_by_course = $this->get_attendances_list($course['real_id']);
  651. foreach ($attendances_by_course as $attendance) {
  652. // get total faults and total weight
  653. $total_done_attendance = $attendance['attendance_qualify_max'];
  654. $sql = "SELECT score FROM $tbl_attendance_result WHERE user_id=$user_id AND attendance_id=".$attendance['id'];
  655. $rs = Database::query($sql);
  656. $score = 0;
  657. if (Database::num_rows($rs) > 0) {
  658. $row = Database::fetch_array($rs);
  659. $score = $row['score'];
  660. }
  661. $faults = $total_done_attendance-$score;
  662. $faults = $faults > 0 ? $faults:0;
  663. $total_faults += $faults;
  664. $total_weight += $total_done_attendance;
  665. }
  666. }
  667. $porcent = $total_weight > 0 ?round(($total_faults*100)/$total_weight,0):0;
  668. $results['faults'] = $total_faults;
  669. $results['total'] = $total_weight;
  670. $results['porcent'] = $porcent;
  671. return $results;
  672. }
  673. /**
  674. * Get results of faults average by course
  675. * @param int user id
  676. * @param int Session id (optional)
  677. * @return array results containing number of faults, total done attendance, porcent of faults and color depend on result (red, orange)
  678. */
  679. public function get_faults_average_by_course($user_id, $course_code, $session_id = null) {
  680. // Database tables and variables
  681. $course_info = api_get_course_info($course_code);
  682. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  683. $user_id = intval($user_id);
  684. $results = array();
  685. $total_faults = $total_weight = $porcent = 0;
  686. $attendances_by_course = $this->get_attendances_list($course_info['real_id'], $session_id);
  687. foreach ($attendances_by_course as $attendance) {
  688. // Get total faults and total weight
  689. $total_done_attendance = $attendance['attendance_qualify_max'];
  690. $sql = "SELECT score FROM $tbl_attendance_result WHERE c_id = {$course_info['real_id']} AND user_id=$user_id AND attendance_id=".$attendance['id'];
  691. $rs = Database::query($sql);
  692. $score = 0;
  693. if (Database::num_rows($rs) > 0) {
  694. $row = Database::fetch_array($rs);
  695. $score = $row['score'];
  696. }
  697. $faults = $total_done_attendance-$score;
  698. $faults = $faults > 0 ? $faults:0;
  699. $total_faults += $faults;
  700. $total_weight += $total_done_attendance;
  701. }
  702. $porcent = $total_weight > 0 ?round(($total_faults*100)/$total_weight,0):0;
  703. $results['faults'] = $total_faults;
  704. $results['total'] = $total_weight;
  705. $results['porcent'] = $porcent;
  706. return $results;
  707. }
  708. /**
  709. * Get registered users' attendance sheet inside current course
  710. * @param int attendance id
  711. * @param int user id for showing data for only one user (optional)
  712. * @return array users attendance sheet data
  713. */
  714. public function get_users_attendance_sheet($attendance_id, $user_id = 0) {
  715. global $dateTimeFormatLong;
  716. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  717. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  718. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  719. $calendar_ids = array();
  720. // get all dates from calendar by current attendance
  721. foreach ($attendance_calendar as $cal) {
  722. $calendar_ids[] = $cal['id'];
  723. }
  724. $course_id = api_get_course_int_id();
  725. $data = array();
  726. if (empty($user_id)) {
  727. // get all registered users inside current course
  728. $users = $this->get_users_rel_course();
  729. $user_ids = array_keys($users);
  730. if (count($calendar_ids) > 0 && count($user_ids) > 0) {
  731. foreach ($user_ids as $uid) {
  732. $sql = "SELECT * FROM $tbl_attendance_sheet
  733. WHERE c_id = $course_id AND user_id = '$uid' AND attendance_calendar_id IN(".implode(',',$calendar_ids).") ";
  734. $res = Database::query($sql);
  735. if (Database::num_rows($res) > 0) {
  736. while ($row = Database::fetch_array($res)) {
  737. $data[$uid][$row['attendance_calendar_id']]['presence'] = $row['presence'];
  738. }
  739. }
  740. }
  741. }
  742. } else {
  743. // get attendance for current user
  744. $user_id = intval($user_id);
  745. if (count($calendar_ids) > 0) {
  746. $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
  747. WHERE att.c_id = $course_id AND
  748. cal.c_id = $course_id AND
  749. att.user_id = '$user_id' AND
  750. att.attendance_calendar_id IN(".implode(',',$calendar_ids).") ";
  751. $res = Database::query($sql);
  752. if (Database::num_rows($res) > 0) {
  753. while ($row = Database::fetch_array($res)) {
  754. $row['date_time'] = api_convert_and_format_date($row['date_time'], null, date_default_timezone_get());
  755. $data[$user_id][] = $row;
  756. }
  757. }
  758. }
  759. }
  760. return $data;
  761. }
  762. /**
  763. * Get next attendance calendar without presences (done attendances)
  764. * @param int attendance id
  765. * @return int attendance calendar id
  766. */
  767. public function get_next_attendance_calendar_id($attendance_id) {
  768. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  769. $attendance_id = intval($attendance_id);
  770. $course_id = api_get_course_int_id();
  771. $sql = "SELECT id FROM $tbl_attendance_calendar
  772. WHERE c_id = $course_id AND attendance_id = '$attendance_id' AND done_attendance = 0 ORDER BY date_time limit 1";
  773. $rs = Database::query($sql);
  774. $next_calendar_id = 0;
  775. if (Database::num_rows($rs) > 0) {
  776. $row = Database::fetch_array($rs);
  777. $next_calendar_id = $row['id'];
  778. }
  779. return $next_calendar_id;
  780. }
  781. /**
  782. * Get next attendance calendar datetime without presences (done attendances)
  783. * @param int attendance id
  784. * @return int UNIX time format datetime
  785. */
  786. public function get_next_attendance_calendar_datetime($attendance_id) {
  787. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  788. $course_id = api_get_course_int_id();
  789. $attendance_id = intval($attendance_id);
  790. $sql = "SELECT id, date_time FROM $tbl_attendance_calendar WHERE c_id = $course_id AND attendance_id = '$attendance_id' AND done_attendance = 0 ORDER BY date_time limit 1";
  791. $rs = Database::query($sql);
  792. $next_calendar_datetime = 0;
  793. if (Database::num_rows($rs) > 0) {
  794. $row = Database::fetch_array($rs);
  795. $next_calendar_datetime = api_get_local_time($row['date_time']);
  796. }
  797. return $next_calendar_datetime;
  798. }
  799. /**
  800. * Get user' score from current attendance
  801. * @param int user id
  802. * @param int attendance id
  803. * @return int score
  804. */
  805. public function get_user_score($user_id, $attendance_id) {
  806. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  807. $user_id = intval($user_id);
  808. $attendance_id = intval($attendance_id);
  809. $course_id = api_get_course_int_id();
  810. $sql = "SELECT score FROM $tbl_attendance_result WHERE c_id = $course_id AND user_id='$user_id' AND attendance_id='$attendance_id'";
  811. $rs = Database::query($sql);
  812. $score = 0;
  813. if (Database::num_rows($rs) > 0) {
  814. $row = Database::fetch_array($rs);
  815. $score = $row['score'];
  816. }
  817. return $score;
  818. }
  819. /**
  820. * Get attendance calendar data by id
  821. * @param int attendance calendar id
  822. * @return array attendance calendar data
  823. */
  824. public function get_attendance_calendar_by_id($calendar_id) {
  825. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  826. $calendar_id = intval($calendar_id);
  827. $course_id = api_get_course_int_id();
  828. $sql = "SELECT * FROM $tbl_attendance_calendar WHERE c_id = $course_id AND id = '$calendar_id' ";
  829. $rs = Database::query($sql);
  830. $data = array();
  831. if (Database::num_rows($rs) > 0) {
  832. while ($row = Database::fetch_array($rs)) {
  833. $row['date_time'] = api_get_local_time($row['date_time']);
  834. $data = $row;
  835. }
  836. }
  837. return $data;
  838. }
  839. /**
  840. * Get all attendance calendar data inside current attendance
  841. * @param int attendance id
  842. * @return array attendance calendar data
  843. */
  844. public function get_attendance_calendar($attendance_id, $type = 'all', $calendar_id = null) {
  845. global $dateFormatShort, $timeNoSecFormat;
  846. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  847. $attendance_id = intval($attendance_id);
  848. $course_id = api_get_course_int_id();
  849. $sql = "SELECT * FROM $tbl_attendance_calendar WHERE c_id = $course_id AND attendance_id = '$attendance_id' ";
  850. $filter_where = '';
  851. if (!in_array($type, array('today', 'all', 'all_done', 'all_not_done','calendar_id'))) {
  852. $type = 'all';
  853. }
  854. switch ($type) {
  855. case 'calendar_id':
  856. $calendar_id = intval($calendar_id);
  857. if (!empty($calendar_id)) {
  858. $sql.= " AND id = $calendar_id";
  859. }
  860. break;
  861. case 'today':
  862. //$sql .= ' AND DATE_FORMAT(date_time,"%d-%m-%Y") = DATE_FORMAT("'.api_get_utc_datetime().'", "%d-%m-%Y" )';
  863. break;
  864. case 'all_done':
  865. $sql .= " AND done_attendance = 1 ";
  866. break;
  867. case 'all_not_done':
  868. $sql .= " AND done_attendance = 0 ";
  869. break;
  870. case 'all':
  871. default:
  872. break;
  873. }
  874. $sql .= " ORDER BY date_time ";
  875. $rs = Database::query($sql);
  876. $data = array();
  877. if (Database::num_rows($rs) > 0) {
  878. while ($row = Database::fetch_array($rs,'ASSOC')) {
  879. $row['db_date_time'] = $row['date_time'];
  880. $row['date_time'] = api_get_local_time($row['date_time']);
  881. $row['date'] = api_format_date($row['date_time'], DATE_FORMAT_SHORT);
  882. $row['time'] = api_format_date($row['date_time'], TIME_NO_SEC_FORMAT);
  883. if ($type == 'today') {
  884. if (date('d-m-Y', api_strtotime($row['date_time'])) == date('d-m-Y', api_strtotime(api_get_local_time()))) {
  885. $data[] = $row;
  886. }
  887. } else {
  888. $data[] = $row;
  889. }
  890. }
  891. }
  892. return $data;
  893. }
  894. /**
  895. * Get number of attendance calendar inside current attendance
  896. * @param int attendance id
  897. * @return int number of dates in attendance calendar
  898. */
  899. public function get_number_of_attendance_calendar($attendance_id) {
  900. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  901. $attendance_id = intval($attendance_id);
  902. $course_id = api_get_course_int_id();
  903. $sql = "SELECT count(id) FROM $tbl_attendance_calendar WHERE c_id = $course_id AND attendance_id = '$attendance_id'";
  904. $rs = Database::query($sql);
  905. $row = Database::fetch_row($rs);
  906. $count = $row[0];
  907. return $count;
  908. }
  909. /**
  910. * Get count dates inside attendance calendar by attendance id
  911. * @param int attendance id
  912. * @return int count of dates
  913. */
  914. public function get_count_dates_inside_attendance_calendar($attendance_id) {
  915. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  916. $attendance_id = intval($attendance_id);
  917. $course_id = api_get_course_int_id();
  918. $sql = "SELECT count(id) FROM $tbl_attendance_calendar WHERE c_id = $course_id AND attendance_id = '$attendance_id'";
  919. $rs = Database::query($sql);
  920. $count = 0;
  921. if (Database::num_rows($rs) > 0) {
  922. $row = Database::fetch_row($rs);
  923. $count = $row[0];
  924. }
  925. return $count;
  926. }
  927. /**
  928. * check if all calendar of an attendance is done
  929. * @param int attendance id
  930. * @return bool True if all calendar is done, otherwise false
  931. */
  932. public function is_all_attendance_calendar_done($attendance_id) {
  933. $attendance_id = intval($attendance_id);
  934. $done_calendar = self::get_done_attendance_calendar($attendance_id);
  935. $count_dates_in_calendar = self::get_count_dates_inside_attendance_calendar($attendance_id);
  936. $number_of_dates = self::get_number_of_attendance_calendar($attendance_id);
  937. $result = false;
  938. if ($number_of_dates && (intval($count_dates_in_calendar) == intval($done_calendar))) {
  939. $result = true;
  940. }
  941. return $result;
  942. }
  943. /**
  944. * check if an attendance is locked
  945. * @param int attendance id
  946. * @param bool
  947. */
  948. public function is_locked_attendance($attendance_id) {
  949. $attendance_id = intval($attendance_id);
  950. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  951. $course_id = api_get_course_int_id();
  952. $sql = "SELECT id FROM $tbl_attendance WHERE c_id = $course_id AND id = $attendance_id AND locked = 1";
  953. $rs = Database::query($sql);
  954. $result = false;
  955. if (Database::num_rows($rs) > 0) {
  956. $result = true;
  957. }
  958. return $result;
  959. }
  960. /**
  961. * Add new datetime inside attendance calendar table
  962. * @param int attendance id
  963. * @return int affected rows
  964. */
  965. public function attendance_calendar_add($attendance_id) {
  966. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  967. $affected_rows = 0;
  968. $attendance_id = intval($attendance_id);
  969. $course_id = api_get_course_int_id();
  970. // check if datetime already exists inside the table
  971. $sql = "SELECT id FROM $tbl_attendance_calendar
  972. WHERE c_id = $course_id AND date_time='".Database::escape_string($this->date_time)."' AND attendance_id = '$attendance_id'";
  973. $rs = Database::query($sql);
  974. if (Database::num_rows($rs) == 0) {
  975. $sql = "INSERT INTO $tbl_attendance_calendar SET
  976. c_id = $course_id,
  977. date_time = '".Database::escape_string($this->date_time)."',
  978. attendance_id = '$attendance_id'";
  979. Database::query($sql);
  980. $affected_rows = Database::affected_rows();
  981. }
  982. // update locked attendance
  983. $is_all_calendar_done = self::is_all_attendance_calendar_done($attendance_id);
  984. if (!$is_all_calendar_done) {
  985. $unlock = self::lock_attendance($attendance_id, false);
  986. } else {
  987. $unlock = self::lock_attendance($attendance_id);
  988. }
  989. return $affected_rows;
  990. }
  991. /**
  992. * save repeated date inside attendance calendar table
  993. * @param int attendance id
  994. * @param int start date in tms
  995. * @param int end date in tms
  996. * @param string repeat type daily, weekly, monthlyByDate
  997. */
  998. public function attendance_repeat_calendar_add($attendance_id, $start_date, $end_date, $repeat_type) {
  999. $attendance_id = intval($attendance_id);
  1000. // save start date
  1001. $datetimezone = api_get_utc_datetime($start_date);
  1002. $this->set_date_time($datetimezone);
  1003. $res = $this->attendance_calendar_add($attendance_id);
  1004. //86400 = 24 hours in seconds
  1005. //604800 = 1 week in seconds
  1006. // Saves repeated dates
  1007. switch($repeat_type) {
  1008. case 'daily':
  1009. $j = 1;
  1010. for ($i = $start_date + 86400; ($i <= $end_date); $i += 86400) {
  1011. //$datetimezone = api_get_utc_date_add(api_get_utc_datetime($start_date), 0 , 0, $j); //to support europe timezones
  1012. $datetimezone = api_get_utc_datetime($i);
  1013. $this->set_date_time($datetimezone);
  1014. $res = $this->attendance_calendar_add($attendance_id);
  1015. $j++;
  1016. }
  1017. break;
  1018. exit;
  1019. case 'weekly':
  1020. $j = 1;
  1021. for ($i = $start_date + 604800; ($i <= $end_date); $i += 604800) {
  1022. $datetimezone = api_get_utc_datetime($i);
  1023. //$datetimezone = api_get_utc_date_add(api_get_utc_datetime($start_date), 0 , 0, $j*7); //to support europe timezones
  1024. $this->set_date_time($datetimezone);
  1025. $res = $this->attendance_calendar_add($attendance_id);
  1026. $j++;
  1027. }
  1028. break;
  1029. case 'monthlyByDate':
  1030. $j = 1;
  1031. //@todo fix bug with february
  1032. for ($i = $start_date + 2419200; ($i <= $end_date); $i += 2419200) {
  1033. $datetimezone = api_get_utc_datetime($i);
  1034. //$datetimezone = api_get_utc_date_add(api_get_utc_datetime($start_date), 0 , $j); //to support europe timezones
  1035. $this->set_date_time($datetimezone);
  1036. $res = $this->attendance_calendar_add($attendance_id);
  1037. $j++;
  1038. }
  1039. //$next_start = $this->add_month($start_date);
  1040. /*
  1041. $next_start = api_strtotime(api_get_utc_date_add(api_get_utc_datetime($start_date), 0 , 1),'UTC');
  1042. error_log('22-->$start_date '.$start_date);
  1043. error_log('$next_start '.$next_start);
  1044. error_log('$end_date '.$end_date);
  1045. while($next_start <= $end_date) {
  1046. //$datetime = date('Y-m-d H:i:s', $next_start);
  1047. $datetimezone = api_get_utc_datetime($next_start);
  1048. error_log('$datetimezone n loop '.$datetimezone);
  1049. $this->set_date_time($datetimezone);
  1050. $res = $this->attendance_calendar_add($attendance_id);
  1051. //$next_start = $this->add_month($next_start);
  1052. $next_start = api_strtotime(api_get_utc_date_add(api_get_utc_datetime($next_start), 0 , 1),'UTC');
  1053. error_log('$next_start in loop '.$next_start);
  1054. }*/
  1055. break;
  1056. }
  1057. }
  1058. /**
  1059. * Adds x months to a UNIX timestamp
  1060. * @param int The timestamp
  1061. * @param int The number of years to add
  1062. * @return int The new timestamp
  1063. */
  1064. private function add_month($timestamp, $num=1) {
  1065. $values = api_get_utc_datetime($timestamp);
  1066. $values = str_replace(array(':','-',' '), '/', $values);
  1067. list($y, $m, $d, $h, $n, $s) = split('/',$values);
  1068. if($m+$num>12) {
  1069. $y += floor($num/12);
  1070. $m += $num%12;
  1071. } else {
  1072. $m += $num;
  1073. }
  1074. //date_default_timezone_set('UTC');
  1075. // return mktime($h, $n, $s, $m, $d, $y);
  1076. $result = api_strtotime($y.'-'.$m.'-'.$d.' '.$h.':'.$n.':'.$s, 'UTC');
  1077. if (!empty($result)) {
  1078. return $result;
  1079. }
  1080. return false;
  1081. }
  1082. /**
  1083. * edit a datetime inside attendance calendar table
  1084. * @param int attendance calendar id
  1085. * @param int attendance id
  1086. * @return int affected rows
  1087. */
  1088. public function attendance_calendar_edit($calendar_id, $attendance_id) {
  1089. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1090. $affected_rows = 0;
  1091. $attendance_id = intval($attendance_id);
  1092. $course_id = api_get_course_int_id();
  1093. // check if datetime already exists inside the table
  1094. $sql = "SELECT id FROM $tbl_attendance_calendar
  1095. WHERE c_id = $course_id AND date_time = '".Database::escape_string($this->date_time)."' AND attendance_id = '$attendance_id'";
  1096. $rs = Database::query($sql);
  1097. if (Database::num_rows($rs) == 0) {
  1098. $sql = "UPDATE $tbl_attendance_calendar SET date_time='".Database::escape_string($this->date_time)."' WHERE c_id = $course_id AND id = '".intval($calendar_id)."'";
  1099. Database::query($sql);
  1100. $affected_rows = Database::affected_rows();
  1101. }
  1102. // update locked attendance
  1103. $is_all_calendar_done = self::is_all_attendance_calendar_done($attendance_id);
  1104. if (!$is_all_calendar_done) {
  1105. $unlock = self::lock_attendance($attendance_id, false);
  1106. } else {
  1107. $unlock = self::lock_attendance($attendance_id);
  1108. }
  1109. return $affected_rows;
  1110. }
  1111. /**
  1112. * delete a datetime from attendance calendar table
  1113. * @param int attendance calendar id
  1114. * @param int attendance id
  1115. * @param bool true for removing all calendar inside current attendance, false for removing by calendar id
  1116. * @return int affected rows
  1117. */
  1118. public function attendance_calendar_delete($calendar_id, $attendance_id , $all_delete = false) {
  1119. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1120. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  1121. $session_id = api_get_session_id();
  1122. $attendance_id = intval($attendance_id);
  1123. // get all registered users inside current course
  1124. $users = $this->get_users_rel_course();
  1125. $user_ids = array_keys($users);
  1126. $course_id = api_get_course_int_id();
  1127. if ($all_delete) {
  1128. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  1129. $calendar_ids = array();
  1130. // get all dates from calendar by current attendance
  1131. if (!empty($attendance_calendar)) {
  1132. foreach ($attendance_calendar as $cal) {
  1133. // delete all data from attendance sheet
  1134. $sql = "DELETE FROM $tbl_attendance_sheet WHERE c_id = $course_id AND attendance_calendar_id = '".intval($cal['id'])."'";
  1135. Database::query($sql);
  1136. // delete data from attendance calendar
  1137. $sql = "DELETE FROM $tbl_attendance_calendar WHERE c_id = $course_id AND id = '".intval($cal['id'])."'";
  1138. Database::query($sql);
  1139. }
  1140. }
  1141. } else {
  1142. // delete just one row from attendance sheet by the calendar id
  1143. $sql = "DELETE FROM $tbl_attendance_sheet WHERE c_id = $course_id AND attendance_calendar_id = '".intval($calendar_id)."'";
  1144. Database::query($sql);
  1145. // delete data from attendance calendar
  1146. $sql = "DELETE FROM $tbl_attendance_calendar WHERE c_id = $course_id AND id = '".intval($calendar_id)."'";
  1147. Database::query($sql);
  1148. }
  1149. $affected_rows = Database::affected_rows();
  1150. // update users' results
  1151. $this->update_users_results($user_ids, $attendance_id);
  1152. return $affected_rows;
  1153. }
  1154. /**
  1155. * buid a string datetime from array
  1156. * @param array array containing data e.g: $array('Y'=>'2010', 'F' => '02', 'd' => '10', 'H' => '12', 'i' => '30')
  1157. * @return string date and time e.g: '2010-02-10 12:30:00'
  1158. */
  1159. public function build_datetime_from_array($array) {
  1160. $year = '0000';
  1161. $month = $day = $hours = $minutes = $seconds = '00';
  1162. if (isset($array['Y']) && (isset($array['F']) || isset($array['M'])) && isset($array['d']) && isset($array['H']) && isset($array['i'])) {
  1163. $year = $array['Y'];
  1164. $month = isset($array['F'])?$array['F']:$array['M'];
  1165. if (intval($month) < 10 ) $month = '0'.$month;
  1166. $day = $array['d'];
  1167. if (intval($day) < 10 ) $day = '0'.$day;
  1168. $hours = $array['H'];
  1169. if (intval($hours) < 10 ) $hours = '0'.$hours;
  1170. $minutes = $array['i'];
  1171. if (intval($minutes) < 10 ) $minutes = '0'.$minutes;
  1172. }
  1173. if (checkdate($month,$day,$year)) {
  1174. $datetime = $year.'-'.$month.'-'.$day.' '.$hours.':'.$minutes.':'.$seconds;
  1175. }
  1176. return $datetime;
  1177. }
  1178. /** Setters for fields of attendances tables **/
  1179. public function set_session_id($session_id) {
  1180. $this->session_id = $session_id;
  1181. }
  1182. public function set_course_id($course_id) {
  1183. $this->course_id = $course_id;
  1184. }
  1185. public function set_date_time($datetime) {
  1186. $this->date_time = $datetime;
  1187. }
  1188. public function set_name($name) {
  1189. $this->name = $name;
  1190. }
  1191. public function set_description($description) {
  1192. $this->description = $description;
  1193. }
  1194. public function set_attendance_qualify_title($attendance_qualify_title) {
  1195. $this->attendance_qualify_title = $attendance_qualify_title;
  1196. }
  1197. public function set_attendance_weight($attendance_weight) {
  1198. $this->attendance_weight = $attendance_weight;
  1199. }
  1200. /** Getters for fields of attendances tables **/
  1201. public function get_session_id() {
  1202. return $this->session_id;
  1203. }
  1204. public function get_course_id() {
  1205. return $this->course_id;
  1206. }
  1207. public function get_date_time() {
  1208. return $this->date_time;
  1209. }
  1210. public function get_name($name) {
  1211. return $this->name;
  1212. }
  1213. public function get_description($description) {
  1214. return $this->description;
  1215. }
  1216. public function get_attendance_qualify_title($attendance_qualify_title) {
  1217. return $this->attendance_qualify_title;
  1218. }
  1219. public function get_attendance_weight($attendance_weight) {
  1220. return $this->attendance_weight;
  1221. }
  1222. }