attendance.lib.php 53 KB

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