attendance.lib.php 60 KB

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