attendance.lib.php 52 KB

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