attendance.lib.php 58 KB

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