attendance.lib.php 79 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032203320342035203620372038203920402041204220432044204520462047204820492050205120522053205420552056205720582059206020612062206320642065206620672068206920702071207220732074207520762077207820792080208120822083208420852086208720882089209020912092209320942095209620972098209921002101210221032104210521062107210821092110211121122113211421152116211721182119212021212122212321242125212621272128212921302131213221332134213521362137213821392140214121422143214421452146214721482149215021512152215321542155215621572158215921602161216221632164216521662167216821692170217121722173217421752176217721782179218021812182218321842185218621872188218921902191219221932194219521962197219821992200220122022203220422052206220722082209221022112212221322142215221622172218221922202221222222232224222522262227222822292230223122322233223422352236223722382239224022412242224322442245224622472248224922502251225222532254225522562257225822592260
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This file contains class used like library, provides functions for attendance tool.
  5. * It's also used like model to attendance_controller (MVC pattern).
  6. *
  7. * @author Christian Fasanando <christian1827@gmail.com>
  8. * @author Julio Montoya <gugli100@gmail.com> improvements
  9. *
  10. * @package chamilo.attendance
  11. */
  12. class Attendance
  13. {
  14. // constants
  15. const DONE_ATTENDANCE_LOG_TYPE = 'done_attendance_sheet';
  16. const UPDATED_ATTENDANCE_LOG_TYPE = 'updated_attendance_sheet';
  17. const LOCKED_ATTENDANCE_LOG_TYPE = 'locked_attendance_sheet';
  18. public $category_id;
  19. private $session_id;
  20. private $course_id;
  21. private $date_time;
  22. private $name;
  23. private $description;
  24. private $attendance_qualify_title;
  25. private $attendance_weight;
  26. private $course_int_id;
  27. /**
  28. * Constructor.
  29. */
  30. public function __construct()
  31. {
  32. //$this->course_int_id = api_get_course_int_id();
  33. }
  34. /**
  35. * Get the total number of attendance inside current course and current session.
  36. *
  37. * @param int $active
  38. *
  39. * @return int
  40. *
  41. * @see SortableTable#get_total_number_of_items()
  42. */
  43. public static function getNumberOfAttendances()
  44. {
  45. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  46. $session_id = api_get_session_id();
  47. $condition_session = api_get_session_condition($session_id);
  48. $course_id = api_get_course_int_id();
  49. $active_plus = '';
  50. if ((isset($_GET['isStudentView']) && $_GET['isStudentView'] == 'true') ||
  51. !api_is_allowed_to_edit(null, true)
  52. ) {
  53. $active_plus = ' AND att.active = 1';
  54. }
  55. $sql = "SELECT COUNT(att.id) AS total_number_of_items
  56. FROM $tbl_attendance att
  57. WHERE
  58. c_id = $course_id AND
  59. active <> 2 $active_plus $condition_session ";
  60. /*$active = (int) $active;
  61. if ($active === 1 || $active === 0) {
  62. $sql .= "AND att.active = $active";
  63. }*/
  64. $res = Database::query($sql);
  65. $obj = Database::fetch_object($res);
  66. return $obj->total_number_of_items;
  67. }
  68. /**
  69. * Get attendance list only the id, name and attendance_qualify_max fields.
  70. *
  71. * @param int $course_id course db name (optional)
  72. * @param int $session_id session id (optional)
  73. *
  74. * @return array attendances list
  75. */
  76. public function get_attendances_list($course_id = 0, $session_id = 0)
  77. {
  78. $table = Database::get_course_table(TABLE_ATTENDANCE);
  79. $course_id = (int) $course_id;
  80. if (empty($course_id)) {
  81. $course_id = api_get_course_int_id();
  82. }
  83. $session_id = !empty($session_id) ? (int) $session_id : api_get_session_id();
  84. $condition_session = api_get_session_condition($session_id);
  85. // Get attendance data
  86. $sql = "SELECT id, name, attendance_qualify_max
  87. FROM $table
  88. WHERE c_id = $course_id AND active = 1 $condition_session ";
  89. $result = Database::query($sql);
  90. $data = [];
  91. if (Database::num_rows($result) > 0) {
  92. while ($row = Database::fetch_array($result, 'ASSOC')) {
  93. $data[$row['id']] = $row;
  94. }
  95. }
  96. return $data;
  97. }
  98. /**
  99. * Get the attendances to display on the current page (fill the sortable-table).
  100. *
  101. * @param int offset of first user to recover
  102. * @param int Number of users to get
  103. * @param int Column to sort on
  104. * @param string Order (ASC,DESC)
  105. *
  106. * @see SortableTable#get_table_data($from)
  107. *
  108. * @return array
  109. */
  110. public static function get_attendance_data(
  111. $from,
  112. $number_of_items,
  113. $column,
  114. $direction
  115. ) {
  116. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  117. $course_id = api_get_course_int_id();
  118. $session_id = api_get_session_id();
  119. $condition_session = api_get_session_condition($session_id);
  120. $column = (int) $column;
  121. $from = (int) $from;
  122. $number_of_items = (int) $number_of_items;
  123. if (!in_array($direction, ['ASC', 'DESC'])) {
  124. $direction = 'ASC';
  125. }
  126. $active_plus = '';
  127. if ((isset($_GET['isStudentView']) && $_GET['isStudentView'] == 'true') ||
  128. !api_is_allowed_to_edit(null, true)
  129. ) {
  130. $active_plus = ' AND att.active = 1';
  131. }
  132. $sql = "SELECT
  133. att.id AS col0,
  134. att.name AS col1,
  135. att.description AS col2,
  136. att.attendance_qualify_max AS col3,
  137. att.locked AS col4,
  138. att.active AS col5,
  139. att.session_id
  140. FROM $tbl_attendance att
  141. WHERE
  142. att.active <> 2 AND
  143. c_id = $course_id $active_plus $condition_session
  144. ORDER BY col$column $direction
  145. LIMIT $from,$number_of_items ";
  146. $res = Database::query($sql);
  147. $attendances = [];
  148. $user_info = api_get_user_info();
  149. $allowDelete = api_get_setting('allow_delete_attendance');
  150. $student_param = '';
  151. $studentRequestId = isset($_GET['student_id']) ? (int) $_GET['student_id'] : 0;
  152. if (api_is_drh() && !empty($studentRequestId)) {
  153. $student_param = '&student_id='.$studentRequestId;
  154. }
  155. while ($attendance = Database::fetch_row($res)) {
  156. $session_star = '';
  157. if (api_get_session_id() == $attendance[6]) {
  158. $session_star = api_get_session_image(api_get_session_id(), $user_info['status']);
  159. }
  160. if ($attendance[5] == 1) {
  161. $isDrhOfCourse = CourseManager::isUserSubscribedInCourseAsDrh(
  162. api_get_user_id(),
  163. api_get_course_info()
  164. ) || api_is_drh();
  165. if (api_is_allowed_to_edit(null, true) || $isDrhOfCourse) {
  166. // Link to edit
  167. $attendance[1] = '<a href="index.php?'.api_get_cidreq().'&action=attendance_sheet_list&attendance_id='.$attendance[0].$student_param.'">'.$attendance[1].'</a>'.$session_star;
  168. } else {
  169. // Link to view
  170. $attendance[1] = '<a href="index.php?'.api_get_cidreq().'&action=attendance_sheet_list_no_edit&attendance_id='.$attendance[0].$student_param.'">'.$attendance[1].'</a>'.$session_star;
  171. }
  172. } else {
  173. $attendance[1] = '<a class="muted" href="index.php?'.api_get_cidreq().'&action=attendance_sheet_list&attendance_id='.$attendance[0].$student_param.'">'.$attendance[1].'</a>'.$session_star;
  174. }
  175. if ($attendance[5] == 1) {
  176. $attendance[3] = '<center>'.$attendance[3].'</center>';
  177. } else {
  178. $attendance[3] = '<center><span class="muted">'.$attendance[3].'</span></center>';
  179. }
  180. $attendance[3] = '<center>'.$attendance[3].'</center>';
  181. if (api_is_allowed_to_edit(null, true)) {
  182. $actions = '';
  183. $actions .= '<center>';
  184. if (api_is_platform_admin()) {
  185. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_edit&attendance_id='.$attendance[0].'">'.
  186. Display::return_icon('edit.png', get_lang('Edit'), [], ICON_SIZE_SMALL).'</a>&nbsp;';
  187. // Visible
  188. if ($attendance[5] == 1) {
  189. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_set_invisible&attendance_id='.$attendance[0].'">'.
  190. Display::return_icon('visible.png', get_lang('Hide'), [], ICON_SIZE_SMALL).'</a>';
  191. } else {
  192. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_set_visible&attendance_id='.$attendance[0].'">'.
  193. Display::return_icon('invisible.png', get_lang('Show'), [], ICON_SIZE_SMALL).'</a>';
  194. $attendance[2] = '<span class="muted">'.$attendance[2].'</span>';
  195. }
  196. if ($allowDelete === 'true') {
  197. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_delete&attendance_id='.$attendance[0].'">'.
  198. Display::return_icon('delete.png', get_lang('Delete'), [], ICON_SIZE_SMALL).'</a>';
  199. }
  200. } else {
  201. $is_locked_attendance = self::is_locked_attendance($attendance[0]);
  202. if ($is_locked_attendance) {
  203. $actions .= Display::return_icon('edit_na.png', get_lang('Edit')).'&nbsp;';
  204. $actions .= Display::return_icon('visible.png', get_lang('Hide'));
  205. } else {
  206. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_edit&attendance_id='.$attendance[0].'">'.
  207. Display::return_icon('edit.png', get_lang('Edit'), [], ICON_SIZE_SMALL).'</a>&nbsp;';
  208. if ($attendance[5] == 1) {
  209. $actions .= ' <a href="index.php?'.api_get_cidreq().'&action=attendance_set_invisible&attendance_id='.$attendance[0].'">'.
  210. Display::return_icon('visible.png', get_lang('Hide'), [], ICON_SIZE_SMALL).'</a>';
  211. } else {
  212. $actions .= ' <a href="index.php?'.api_get_cidreq().'&action=attendance_set_visible&attendance_id='.$attendance[0].'">'.
  213. Display::return_icon('invisible.png', get_lang('Show'), [], ICON_SIZE_SMALL).'</a>';
  214. $attendance[2] = '<span class="muted">'.$attendance[2].'</span>';
  215. }
  216. if ($allowDelete === 'true') {
  217. $actions .= ' <a href="index.php?'.api_get_cidreq().'&action=attendance_delete&attendance_id='.$attendance[0].'">'.
  218. Display::return_icon('delete.png', get_lang('Delete'), [], ICON_SIZE_SMALL).'</a>';
  219. }
  220. }
  221. }
  222. // display lock/unlock icon
  223. $is_done_all_calendar = self::is_all_attendance_calendar_done($attendance[0]);
  224. if ($is_done_all_calendar) {
  225. $locked = $attendance[4];
  226. if ($locked == 0) {
  227. if (api_is_platform_admin()) {
  228. $message_alert = get_lang('Are you sure you want to lock the attendance?');
  229. } else {
  230. $message_alert = get_lang('The attendance is not locked, which means your teacher is still able to modify it.');
  231. }
  232. $actions .= '&nbsp;<a onclick="javascript:if(!confirm(\''.$message_alert.'\')) return false;" href="index.php?'.api_get_cidreq().'&action=lock_attendance&attendance_id='.$attendance[0].'">'.
  233. Display::return_icon('unlock.png', get_lang('Lock attendance')).'</a>';
  234. } else {
  235. if (api_is_platform_admin()) {
  236. $actions .= '&nbsp;<a onclick="javascript:if(!confirm(\''.get_lang('Are you sure you want to unlock the attendance?').'\')) return false;" href="index.php?'.api_get_cidreq().'&action=unlock_attendance&attendance_id='.$attendance[0].'">'.
  237. Display::return_icon('locked.png', get_lang('Unlock attendance')).'</a>';
  238. } else {
  239. $actions .= '&nbsp;'.Display::return_icon('locked_na.png', get_lang('Locked attendance'));
  240. }
  241. }
  242. }
  243. $actions .= '</center>';
  244. $attendances[] = [
  245. $attendance[0],
  246. $attendance[1],
  247. $attendance[2],
  248. $attendance[3],
  249. $actions,
  250. ];
  251. } else {
  252. $attendance[0] = '&nbsp;';
  253. $attendances[] = [
  254. $attendance[0],
  255. $attendance[1],
  256. $attendance[2],
  257. $attendance[3],
  258. ];
  259. }
  260. }
  261. return $attendances;
  262. }
  263. /**
  264. * Get the attendances by id to display on the current page.
  265. *
  266. * @param int $attendanceId
  267. *
  268. * @return array attendance data
  269. */
  270. public function get_attendance_by_id($attendanceId)
  271. {
  272. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  273. $attendanceId = (int) $attendanceId;
  274. $course_id = api_get_course_int_id();
  275. $attendance_data = [];
  276. $sql = "SELECT * FROM $tbl_attendance
  277. WHERE c_id = $course_id AND id = '$attendanceId'";
  278. $res = Database::query($sql);
  279. if (Database::num_rows($res) > 0) {
  280. while ($row = Database::fetch_array($res)) {
  281. $attendance_data = $row;
  282. }
  283. }
  284. return $attendance_data;
  285. }
  286. /**
  287. * Add attendances sheet inside table. This is the *list of* dates, not
  288. * a specific date in itself.
  289. *
  290. * @param bool true for adding link in gradebook or false otherwise (optional)
  291. *
  292. * @return int last attendance id
  293. */
  294. public function attendance_add($link_to_gradebook = false)
  295. {
  296. $_course = api_get_course_info();
  297. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  298. $table_link = Database::get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  299. $session_id = api_get_session_id();
  300. $user_id = api_get_user_id();
  301. $course_code = $_course['code'];
  302. $course_id = $_course['real_id'];
  303. $title_gradebook = $this->attendance_qualify_title;
  304. $value_calification = 0;
  305. $weight_calification = api_float_val($this->attendance_weight);
  306. $params = [
  307. 'c_id' => $course_id,
  308. 'name' => $this->name,
  309. 'description' => $this->description,
  310. 'attendance_qualify_title' => $title_gradebook,
  311. 'attendance_weight' => $weight_calification,
  312. 'session_id' => $session_id,
  313. 'active' => 1,
  314. 'attendance_qualify_max' => 0,
  315. 'locked' => 0,
  316. ];
  317. $last_id = Database::insert($tbl_attendance, $params);
  318. if (!empty($last_id)) {
  319. $sql = "UPDATE $tbl_attendance SET id = iid WHERE iid = $last_id";
  320. Database::query($sql);
  321. api_item_property_update(
  322. $_course,
  323. TOOL_ATTENDANCE,
  324. $last_id,
  325. "AttendanceAdded",
  326. $user_id
  327. );
  328. }
  329. // add link to gradebook
  330. if ($link_to_gradebook && !empty($this->category_id)) {
  331. $description = '';
  332. $link_info = GradebookUtils::isResourceInCourseGradebook(
  333. $course_code,
  334. 7,
  335. $last_id,
  336. $session_id
  337. );
  338. $link_id = $link_info['id'];
  339. if (!$link_info) {
  340. GradebookUtils::add_resource_to_course_gradebook(
  341. $this->category_id,
  342. $course_code,
  343. 7,
  344. $last_id,
  345. $title_gradebook,
  346. $weight_calification,
  347. $value_calification,
  348. $description,
  349. 1,
  350. $session_id
  351. );
  352. } else {
  353. Database::query('UPDATE '.$table_link.' SET weight='.$weight_calification.' WHERE id='.$link_id.'');
  354. }
  355. }
  356. return $last_id;
  357. }
  358. /**
  359. * edit attendances inside table.
  360. *
  361. * @param int attendance id
  362. * @param bool true for adding link in gradebook or false otherwise (optional)
  363. *
  364. * @return int last id
  365. */
  366. public function attendance_edit($attendanceId, $link_to_gradebook = false)
  367. {
  368. $_course = api_get_course_info();
  369. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  370. $table_link = Database::get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  371. $session_id = api_get_session_id();
  372. $user_id = api_get_user_id();
  373. $attendanceId = (int) $attendanceId;
  374. $course_code = $_course['code'];
  375. $course_id = $_course['real_id'];
  376. $title_gradebook = $this->attendance_qualify_title;
  377. $value_calification = 0;
  378. $weight_calification = api_float_val($this->attendance_weight);
  379. if (!empty($attendanceId)) {
  380. $params = [
  381. 'name' => $this->name,
  382. 'description' => $this->description,
  383. 'attendance_qualify_title' => $title_gradebook,
  384. 'attendance_weight' => $weight_calification,
  385. ];
  386. Database::update(
  387. $tbl_attendance,
  388. $params,
  389. ['c_id = ? AND id = ?' => [$course_id, $attendanceId]]
  390. );
  391. api_item_property_update(
  392. $_course,
  393. TOOL_ATTENDANCE,
  394. $attendanceId,
  395. "AttendanceUpdated",
  396. $user_id
  397. );
  398. // add link to gradebook
  399. if ($link_to_gradebook && !empty($this->category_id)) {
  400. $description = '';
  401. $link_info = GradebookUtils::isResourceInCourseGradebook(
  402. $course_code,
  403. 7,
  404. $attendanceId,
  405. $session_id
  406. );
  407. if (!$link_info) {
  408. GradebookUtils::add_resource_to_course_gradebook(
  409. $this->category_id,
  410. $course_code,
  411. 7,
  412. $attendanceId,
  413. $title_gradebook,
  414. $weight_calification,
  415. $value_calification,
  416. $description,
  417. 1,
  418. $session_id
  419. );
  420. } else {
  421. Database::query('UPDATE '.$table_link.' SET weight='.$weight_calification.' WHERE id='.$link_info['id'].'');
  422. }
  423. }
  424. return $attendanceId;
  425. }
  426. return null;
  427. }
  428. /**
  429. * Restore attendance.
  430. *
  431. * @param int|array one or many attendances id
  432. *
  433. * @return int affected rows
  434. */
  435. public function attendance_restore($attendanceId)
  436. {
  437. $_course = api_get_course_info();
  438. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  439. $user_id = api_get_user_id();
  440. $course_id = $_course['real_id'];
  441. if (is_array($attendanceId)) {
  442. foreach ($attendanceId as $id) {
  443. $id = (int) $id;
  444. $sql = "UPDATE $tbl_attendance SET active = 1
  445. WHERE c_id = $course_id AND id = '$id'";
  446. $result = Database::query($sql);
  447. $affected_rows = Database::affected_rows($result);
  448. if (!empty($affected_rows)) {
  449. // update row item property table
  450. api_item_property_update(
  451. $_course,
  452. TOOL_ATTENDANCE,
  453. $id,
  454. 'restore',
  455. $user_id
  456. );
  457. }
  458. }
  459. } else {
  460. $attendanceId = (int) $attendanceId;
  461. $sql = "UPDATE $tbl_attendance SET active = 1
  462. WHERE c_id = $course_id AND id = '$attendanceId'";
  463. $result = Database::query($sql);
  464. $affected_rows = Database::affected_rows($result);
  465. if (!empty($affected_rows)) {
  466. // update row item property table
  467. api_item_property_update(
  468. $_course,
  469. TOOL_ATTENDANCE,
  470. $attendanceId,
  471. 'restore',
  472. $user_id
  473. );
  474. }
  475. }
  476. return $affected_rows;
  477. }
  478. /**
  479. * Delete attendances.
  480. *
  481. * @param int|array $attendanceId one or many attendances id
  482. *
  483. * @return int affected rows
  484. */
  485. public function attendance_delete($attendanceId)
  486. {
  487. $_course = api_get_course_info();
  488. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  489. $user_id = api_get_user_id();
  490. $course_id = $_course['real_id'];
  491. if (is_array($attendanceId)) {
  492. foreach ($attendanceId as $id) {
  493. $id = intval($id);
  494. $sql = "UPDATE $tbl_attendance SET active = 2
  495. WHERE c_id = $course_id AND id = '$id'";
  496. $result = Database::query($sql);
  497. $affected_rows = Database::affected_rows($result);
  498. if (!empty($affected_rows)) {
  499. // update row item property table
  500. api_item_property_update(
  501. $_course,
  502. TOOL_ATTENDANCE,
  503. $id,
  504. "delete",
  505. $user_id
  506. );
  507. }
  508. }
  509. } else {
  510. $attendanceId = intval($attendanceId);
  511. $sql = "UPDATE $tbl_attendance SET active = 2
  512. WHERE c_id = $course_id AND id = '$attendanceId'";
  513. $result = Database::query($sql);
  514. $affected_rows = Database::affected_rows($result);
  515. if (!empty($affected_rows)) {
  516. // update row item property table
  517. api_item_property_update(
  518. $_course,
  519. TOOL_ATTENDANCE,
  520. $attendanceId,
  521. "delete",
  522. $user_id
  523. );
  524. }
  525. }
  526. return $affected_rows;
  527. }
  528. /**
  529. * Changes visibility.
  530. *
  531. * @param int|array $attendanceId one or many attendances id
  532. * @param int $status
  533. *
  534. * @return int affected rows
  535. */
  536. public function changeVisibility($attendanceId, $status = 1)
  537. {
  538. $_course = api_get_course_info();
  539. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  540. $user_id = api_get_user_id();
  541. $course_id = $_course['real_id'];
  542. $status = (int) $status;
  543. $action = 'visible';
  544. if ($status == 0) {
  545. $action = 'invisible';
  546. }
  547. if (is_array($attendanceId)) {
  548. foreach ($attendanceId as $id) {
  549. $id = (int) $id;
  550. $sql = "UPDATE $tbl_attendance SET active = $status
  551. WHERE c_id = $course_id AND id = '$id'";
  552. $result = Database::query($sql);
  553. $affected_rows = Database::affected_rows($result);
  554. if (!empty($affected_rows)) {
  555. // update row item property table
  556. api_item_property_update(
  557. $_course,
  558. TOOL_ATTENDANCE,
  559. $id,
  560. $action,
  561. $user_id
  562. );
  563. }
  564. }
  565. } else {
  566. $attendanceId = (int) $attendanceId;
  567. $sql = "UPDATE $tbl_attendance SET active = $status
  568. WHERE c_id = $course_id AND id = '$attendanceId'";
  569. $result = Database::query($sql);
  570. $affected_rows = Database::affected_rows($result);
  571. if (!empty($affected_rows)) {
  572. // update row item property table
  573. api_item_property_update(
  574. $_course,
  575. TOOL_ATTENDANCE,
  576. $attendanceId,
  577. $action,
  578. $user_id
  579. );
  580. }
  581. }
  582. return $affected_rows;
  583. }
  584. /**
  585. * Lock or unlock an attendance.
  586. *
  587. * @param int attendance id
  588. * @param bool True to lock or false otherwise
  589. *
  590. * @return int
  591. */
  592. public function lock_attendance($attendanceId, $lock = true)
  593. {
  594. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  595. $course_id = api_get_course_int_id();
  596. $attendanceId = (int) $attendanceId;
  597. $locked = $lock ? 1 : 0;
  598. $upd = "UPDATE $tbl_attendance SET locked = $locked
  599. WHERE c_id = $course_id AND id = $attendanceId";
  600. $result = Database::query($upd);
  601. $affected_rows = Database::affected_rows($result);
  602. if ($affected_rows && $lock) {
  603. // Save attendance sheet log
  604. $this->saveAttendanceSheetLog(
  605. $attendanceId,
  606. api_get_utc_datetime(),
  607. self::LOCKED_ATTENDANCE_LOG_TYPE,
  608. api_get_user_id()
  609. );
  610. }
  611. return $affected_rows;
  612. }
  613. /**
  614. * Get registered users inside current course.
  615. *
  616. * @param int $attendanceId attendance id for showing attendance result field (optional)
  617. * @param int $groupId
  618. *
  619. * @return array users data
  620. */
  621. public function get_users_rel_course($attendanceId = 0, $groupId = 0)
  622. {
  623. $current_session_id = api_get_session_id();
  624. $current_course_id = api_get_course_id();
  625. $currentCourseIntId = api_get_course_int_id();
  626. $studentInGroup = [];
  627. if (!empty($current_session_id)) {
  628. $a_course_users = CourseManager:: get_user_list_from_course_code(
  629. $current_course_id,
  630. $current_session_id,
  631. '',
  632. 'lastname'
  633. );
  634. } else {
  635. $a_course_users = CourseManager:: get_user_list_from_course_code(
  636. $current_course_id,
  637. 0,
  638. '',
  639. 'lastname'
  640. );
  641. }
  642. if (!empty($groupId)) {
  643. $groupInfo = GroupManager::get_group_properties($groupId);
  644. $students = GroupManager::getStudents($groupInfo['iid']);
  645. if (!empty($students)) {
  646. foreach ($students as $student) {
  647. $studentInGroup[$student['user_id']] = true;
  648. }
  649. }
  650. }
  651. // get registered users inside current course
  652. $a_users = [];
  653. foreach ($a_course_users as $key => $user_data) {
  654. $value = [];
  655. $uid = $user_data['user_id'];
  656. $userInfo = api_get_user_info($uid);
  657. $status = $user_data['status'];
  658. if (!empty($groupId)) {
  659. if (!isset($studentInGroup[$uid])) {
  660. continue;
  661. }
  662. }
  663. $user_status_in_session = null;
  664. $user_status_in_course = null;
  665. if (api_get_session_id()) {
  666. $user_status_in_session = SessionManager::get_user_status_in_course_session(
  667. $uid,
  668. $currentCourseIntId,
  669. $current_session_id
  670. );
  671. } else {
  672. $user_status_in_course = CourseManager::getUserInCourseStatus(
  673. $uid,
  674. $currentCourseIntId
  675. );
  676. }
  677. // Not taking into account DRH or COURSEMANAGER
  678. if ($uid <= 1 ||
  679. $status == DRH ||
  680. $user_status_in_course == COURSEMANAGER ||
  681. $user_status_in_session == 2
  682. ) {
  683. continue;
  684. }
  685. if (!empty($attendanceId)) {
  686. $user_faults = $this->get_faults_of_user(
  687. $uid,
  688. $attendanceId,
  689. $groupId
  690. );
  691. $value['attendance_result'] = $user_faults['faults'].'/'.$user_faults['total'].' ('.$user_faults['faults_porcent'].'%)';
  692. $value['result_color_bar'] = $user_faults['color_bar'];
  693. }
  694. $photo = Display::img(
  695. $userInfo['avatar_small'],
  696. $userInfo['complete_name'],
  697. [],
  698. false
  699. );
  700. $value['photo'] = $photo;
  701. $value['firstname'] = $user_data['firstname'];
  702. $value['lastname'] = $user_data['lastname'];
  703. $value['username'] = $user_data['username'];
  704. $value['user_id'] = $uid;
  705. // Sending only 5 items in the array instead of 60
  706. $a_users[$key] = $value;
  707. }
  708. return $a_users;
  709. }
  710. /**
  711. * add attendances sheet inside table.
  712. *
  713. * @param int $calendar_id attendance calendar id
  714. * @param array $users_present present users during current class
  715. * @param int $attendanceId
  716. *
  717. * @return int affected rows
  718. */
  719. public function attendance_sheet_add($calendar_id, $users_present, $attendanceId)
  720. {
  721. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  722. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  723. $calendar_id = (int) $calendar_id;
  724. $attendanceId = (int) $attendanceId;
  725. $users = $this->get_users_rel_course();
  726. $course_id = api_get_course_int_id();
  727. $user_ids = array_keys($users);
  728. $users_absent = array_diff($user_ids, $users_present);
  729. $affected_rows = 0;
  730. // get last edit type
  731. $calendar_data = $this->get_attendance_calendar_by_id($calendar_id);
  732. $lastedit_type = self::DONE_ATTENDANCE_LOG_TYPE;
  733. if ($calendar_data['done_attendance']) {
  734. $lastedit_type = self::UPDATED_ATTENDANCE_LOG_TYPE;
  735. }
  736. // save users present in class
  737. foreach ($users_present as $user_present) {
  738. $uid = (int) $user_present;
  739. // check if user already was registered with the $calendar_id
  740. $sql = "SELECT user_id FROM $tbl_attendance_sheet
  741. WHERE c_id = $course_id AND user_id='$uid' AND attendance_calendar_id = '$calendar_id'";
  742. $rs = Database::query($sql);
  743. if (Database::num_rows($rs) == 0) {
  744. $sql = "INSERT INTO $tbl_attendance_sheet SET
  745. c_id = $course_id,
  746. user_id = '$uid',
  747. attendance_calendar_id = '$calendar_id',
  748. presence = 1";
  749. $result = Database::query($sql);
  750. $affected_rows += Database::affected_rows($result);
  751. } else {
  752. $sql = "UPDATE $tbl_attendance_sheet SET presence = 1
  753. WHERE
  754. c_id = $course_id AND
  755. user_id ='$uid' AND
  756. attendance_calendar_id = '$calendar_id'
  757. ";
  758. $result = Database::query($sql);
  759. $affected_rows += Database::affected_rows($result);
  760. }
  761. }
  762. // save users absent in class
  763. foreach ($users_absent as $user_absent) {
  764. $uid = intval($user_absent);
  765. // check if user already was registered with the $calendar_id
  766. $sql = "SELECT user_id FROM $tbl_attendance_sheet
  767. WHERE c_id = $course_id AND user_id='$uid' AND attendance_calendar_id = '$calendar_id'";
  768. $rs = Database::query($sql);
  769. if (Database::num_rows($rs) == 0) {
  770. $sql = "INSERT INTO $tbl_attendance_sheet SET
  771. c_id = $course_id,
  772. user_id ='$uid',
  773. attendance_calendar_id = '$calendar_id',
  774. presence = 0";
  775. $result = Database::query($sql);
  776. Database::insert_id();
  777. $affected_rows += Database::affected_rows($result);
  778. } else {
  779. $sql = "UPDATE $tbl_attendance_sheet SET presence = 0
  780. WHERE
  781. c_id = $course_id AND
  782. user_id ='$uid' AND
  783. attendance_calendar_id = '$calendar_id'";
  784. $result = Database::query($sql);
  785. $affected_rows += Database::affected_rows($result);
  786. }
  787. }
  788. // update done_attendance inside attendance calendar table
  789. $sql = "UPDATE $tbl_attendance_calendar SET done_attendance = 1
  790. WHERE c_id = $course_id AND id = '$calendar_id'";
  791. Database::query($sql);
  792. // save users' results
  793. $this->updateUsersResults($user_ids, $attendanceId);
  794. if ($affected_rows) {
  795. //save attendance sheet log
  796. $this->saveAttendanceSheetLog(
  797. $attendanceId,
  798. api_get_utc_datetime(),
  799. $lastedit_type,
  800. api_get_user_id(),
  801. $calendar_data['date_time']
  802. );
  803. }
  804. return $affected_rows;
  805. }
  806. /**
  807. * update users' attendance results.
  808. *
  809. * @param array $user_ids registered users inside current course
  810. * @param int $attendanceId
  811. */
  812. public function updateUsersResults($user_ids, $attendanceId)
  813. {
  814. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  815. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  816. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  817. $course_id = api_get_course_int_id();
  818. $attendanceId = intval($attendanceId);
  819. // fill results about presence of students
  820. $attendance_calendar = $this->get_attendance_calendar(
  821. $attendanceId,
  822. 'all',
  823. null,
  824. null,
  825. true
  826. );
  827. $calendar_ids = [];
  828. // get all dates from calendar by current attendance
  829. foreach ($attendance_calendar as $cal) {
  830. $calendar_ids[] = $cal['id'];
  831. }
  832. // get count of presences by users inside current attendance and save like results
  833. if (count($user_ids) > 0) {
  834. foreach ($user_ids as $uid) {
  835. $uid = (int) $uid;
  836. $count_presences = 0;
  837. if (count($calendar_ids) > 0) {
  838. $sql = "SELECT count(presence) as count_presences
  839. FROM $tbl_attendance_sheet
  840. WHERE
  841. c_id = $course_id AND
  842. user_id = '$uid' AND
  843. attendance_calendar_id IN (".implode(',', $calendar_ids).") AND
  844. presence = 1";
  845. $rs_count = Database::query($sql);
  846. $row_count = Database::fetch_array($rs_count);
  847. $count_presences = $row_count['count_presences'];
  848. }
  849. // save results
  850. $sql = "SELECT id FROM $tbl_attendance_result
  851. WHERE
  852. c_id = $course_id AND
  853. user_id = '$uid' AND
  854. attendance_id = '$attendanceId' ";
  855. $rs_check_result = Database::query($sql);
  856. if (Database::num_rows($rs_check_result) > 0) {
  857. // update result
  858. $sql = "UPDATE $tbl_attendance_result SET
  859. score = '$count_presences'
  860. WHERE
  861. c_id = $course_id AND
  862. user_id='$uid' AND
  863. attendance_id='$attendanceId'";
  864. Database::query($sql);
  865. } else {
  866. // insert new result
  867. $sql = "INSERT INTO $tbl_attendance_result SET
  868. c_id = $course_id ,
  869. user_id = '$uid',
  870. attendance_id = '$attendanceId',
  871. score = '$count_presences'";
  872. Database::query($sql);
  873. $insertId = Database::insert_id();
  874. if ($insertId) {
  875. $sql = "UPDATE $tbl_attendance_result SET id = iid WHERE iid = $insertId";
  876. Database::query($sql);
  877. }
  878. }
  879. }
  880. }
  881. // update attendance qualify max
  882. $count_done_calendar = self::get_done_attendance_calendar($attendanceId);
  883. $sql = "UPDATE $tbl_attendance SET
  884. attendance_qualify_max = '$count_done_calendar'
  885. WHERE c_id = $course_id AND id = '$attendanceId'";
  886. Database::query($sql);
  887. }
  888. /**
  889. * update attendance_sheet_log table, is used as history of an attendance sheet.
  890. *
  891. * @param int Attendance id
  892. * @param string Last edit datetime
  893. * @param string Event type ('locked_attendance', 'done_attendance_sheet' ...)
  894. * @param int Last edit user id
  895. * @param string Calendar datetime value (optional, when event type is 'done_attendance_sheet')
  896. *
  897. * @return int Affected rows
  898. */
  899. public function saveAttendanceSheetLog(
  900. $attendanceId,
  901. $lastedit_date,
  902. $lastedit_type,
  903. $lastedit_user_id,
  904. $calendar_date_value = null
  905. ) {
  906. $course_id = api_get_course_int_id();
  907. // define table
  908. $tbl_attendance_sheet_log = Database::get_course_table(TABLE_ATTENDANCE_SHEET_LOG);
  909. // protect data
  910. $attendanceId = intval($attendanceId);
  911. $lastedit_user_id = intval($lastedit_user_id);
  912. if (isset($calendar_date_value)) {
  913. $calendar_date_value = $calendar_date_value;
  914. } else {
  915. $calendar_date_value = '';
  916. }
  917. // save data
  918. $params = [
  919. 'c_id' => $course_id,
  920. 'attendance_id' => $attendanceId,
  921. 'lastedit_date' => $lastedit_date,
  922. 'lastedit_type' => $lastedit_type,
  923. 'lastedit_user_id' => $lastedit_user_id,
  924. 'calendar_date_value' => $calendar_date_value,
  925. ];
  926. $insertId = Database::insert($tbl_attendance_sheet_log, $params);
  927. if ($insertId) {
  928. $sql = "UPDATE $tbl_attendance_sheet_log SET id = iid WHERE iid = $insertId";
  929. Database::query($sql);
  930. }
  931. return $insertId;
  932. }
  933. /**
  934. * Get number of done attendances inside current sheet.
  935. *
  936. * @param int attendance id
  937. *
  938. * @return int number of done attendances
  939. */
  940. public static function get_done_attendance_calendar($attendanceId)
  941. {
  942. $table = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  943. $attendanceId = intval($attendanceId);
  944. $course_id = api_get_course_int_id();
  945. $sql = "SELECT count(done_attendance) as count
  946. FROM $table
  947. WHERE
  948. c_id = $course_id AND
  949. attendance_id = '$attendanceId' AND
  950. done_attendance = 1
  951. ";
  952. $rs = Database::query($sql);
  953. $row = Database::fetch_array($rs);
  954. $count = $row['count'];
  955. return $count;
  956. }
  957. /**
  958. * Get results of faults (absents) by user.
  959. *
  960. * @param int $user_id
  961. * @param int $attendanceId
  962. * @param int $groupId
  963. *
  964. * @return array results containing number of faults, total done attendance,
  965. * percent of faults and color depend on result (red, orange)
  966. */
  967. public function get_faults_of_user($user_id, $attendanceId, $groupId = null)
  968. {
  969. $user_id = intval($user_id);
  970. $attendanceId = intval($attendanceId);
  971. $results = [];
  972. $calendar_count = self::get_number_of_attendance_calendar(
  973. $attendanceId,
  974. $groupId,
  975. null,
  976. $user_id
  977. );
  978. // $total_done_attendance = $attendance_data['attendance_qualify_max'];
  979. $total_done_attendance = self::get_number_of_attendance_calendar(
  980. $attendanceId,
  981. $groupId,
  982. true,
  983. $user_id
  984. );
  985. $attendance_user_score = $this->get_user_score(
  986. $user_id,
  987. $attendanceId,
  988. $groupId
  989. );
  990. //This is the main change of the BT#1381
  991. //$total_done_attendance = $calendar_count;
  992. // calculate results
  993. $faults = $total_done_attendance - $attendance_user_score;
  994. if (empty($calendar_count)) {
  995. $faults = 0;
  996. }
  997. $faults = $faults > 0 ? $faults : 0;
  998. $faults_porcent = $calendar_count > 0 ? round(($faults * 100) / $calendar_count, 0) : 0;
  999. $results['faults'] = $faults;
  1000. $results['total'] = $calendar_count;
  1001. $results['faults_porcent'] = $faults_porcent;
  1002. $color_bar = '';
  1003. if ($faults_porcent > 25) {
  1004. $color_bar = '#f28989';
  1005. } elseif ($faults_porcent > 10) {
  1006. $color_bar = '#F90';
  1007. }
  1008. $results['color_bar'] = $color_bar;
  1009. return $results;
  1010. }
  1011. /**
  1012. * Get results of faults average for all courses by user.
  1013. *
  1014. * @param int $user_id
  1015. *
  1016. * @return array results containing number of faults, total done attendance,
  1017. * percentage of faults and color depend on result (red, orange)
  1018. */
  1019. public function get_faults_average_inside_courses($user_id)
  1020. {
  1021. // get all courses of current user
  1022. $courses = CourseManager::get_courses_list_by_user_id($user_id, true);
  1023. $user_id = intval($user_id);
  1024. $results = [];
  1025. $total_faults = $total_weight = $porcent = 0;
  1026. foreach ($courses as $course) {
  1027. //$course_code = $course['code'];
  1028. //$course_info = api_get_course_info($course_code);
  1029. $course_id = $course['real_id'];
  1030. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  1031. $attendances_by_course = $this->get_attendances_list($course_id);
  1032. foreach ($attendances_by_course as $attendance) {
  1033. // get total faults and total weight
  1034. $total_done_attendance = $attendance['attendance_qualify_max'];
  1035. $sql = "SELECT score
  1036. FROM $tbl_attendance_result
  1037. WHERE
  1038. c_id = $course_id AND
  1039. user_id = $user_id AND
  1040. attendance_id = ".$attendance['id'];
  1041. $rs = Database::query($sql);
  1042. $score = 0;
  1043. if (Database::num_rows($rs) > 0) {
  1044. $row = Database::fetch_array($rs);
  1045. $score = $row['score'];
  1046. }
  1047. $faults = $total_done_attendance - $score;
  1048. $faults = $faults > 0 ? $faults : 0;
  1049. $total_faults += $faults;
  1050. $total_weight += $total_done_attendance;
  1051. }
  1052. }
  1053. $porcent = $total_weight > 0 ? round(($total_faults * 100) / $total_weight, 0) : 0;
  1054. $results['faults'] = $total_faults;
  1055. $results['total'] = $total_weight;
  1056. $results['porcent'] = $porcent;
  1057. return $results;
  1058. }
  1059. /**
  1060. * Get results of faults average by course.
  1061. *
  1062. * @param int $user_id
  1063. * @param string $course_code
  1064. * @param int Session id (optional)
  1065. *
  1066. * @return array results containing number of faults,
  1067. * total done attendance, porcent of faults and color depend on result (red, orange)
  1068. */
  1069. public function get_faults_average_by_course(
  1070. $user_id,
  1071. $course_code,
  1072. $session_id = null
  1073. ) {
  1074. // Database tables and variables
  1075. $course_info = api_get_course_info($course_code);
  1076. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  1077. $user_id = intval($user_id);
  1078. $results = [];
  1079. $total_faults = $total_weight = $porcent = 0;
  1080. $attendances_by_course = $this->get_attendances_list(
  1081. $course_info['real_id'],
  1082. $session_id
  1083. );
  1084. foreach ($attendances_by_course as $attendance) {
  1085. // Get total faults and total weight
  1086. $total_done_attendance = $attendance['attendance_qualify_max'];
  1087. $sql = "SELECT score FROM $tbl_attendance_result
  1088. WHERE
  1089. c_id = {$course_info['real_id']} AND
  1090. user_id = $user_id AND
  1091. attendance_id=".$attendance['id'];
  1092. $rs = Database::query($sql);
  1093. $score = 0;
  1094. if (Database::num_rows($rs) > 0) {
  1095. $row = Database::fetch_array($rs);
  1096. $score = $row['score'];
  1097. }
  1098. $faults = $total_done_attendance - $score;
  1099. $faults = $faults > 0 ? $faults : 0;
  1100. $total_faults += $faults;
  1101. $total_weight += $total_done_attendance;
  1102. }
  1103. $porcent = $total_weight > 0 ? round(($total_faults * 100) / $total_weight, 0) : 0;
  1104. $results['faults'] = $total_faults;
  1105. $results['total'] = $total_weight;
  1106. $results['porcent'] = $porcent;
  1107. return $results;
  1108. }
  1109. /**
  1110. * Get registered users' attendance sheet inside current course.
  1111. *
  1112. * @param int $attendanceId
  1113. * @param int $user_id for showing data for only one user (optional)
  1114. * @param int $groupId
  1115. *
  1116. * @return array users attendance sheet data
  1117. */
  1118. public function get_users_attendance_sheet(
  1119. $attendanceId,
  1120. $user_id = 0,
  1121. $groupId = 0
  1122. ) {
  1123. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  1124. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1125. $attendance_calendar = $this->get_attendance_calendar(
  1126. $attendanceId,
  1127. 'all',
  1128. null,
  1129. $groupId
  1130. );
  1131. $calendar_ids = [];
  1132. // get all dates from calendar by current attendance
  1133. foreach ($attendance_calendar as $cal) {
  1134. $calendar_ids[] = $cal['id'];
  1135. }
  1136. $course_id = api_get_course_int_id();
  1137. $data = [];
  1138. if (empty($user_id)) {
  1139. // get all registered users inside current course
  1140. $users = $this->get_users_rel_course();
  1141. $user_ids = array_keys($users);
  1142. if (count($calendar_ids) > 0 && count($user_ids) > 0) {
  1143. foreach ($user_ids as $uid) {
  1144. $uid = (int) $uid;
  1145. $sql = "SELECT * FROM $tbl_attendance_sheet
  1146. WHERE
  1147. c_id = $course_id AND
  1148. user_id = '$uid' AND
  1149. attendance_calendar_id IN(".implode(',', $calendar_ids).")
  1150. ";
  1151. $res = Database::query($sql);
  1152. if (Database::num_rows($res) > 0) {
  1153. while ($row = Database::fetch_array($res)) {
  1154. $data[$uid][$row['attendance_calendar_id']]['presence'] = $row['presence'];
  1155. }
  1156. }
  1157. }
  1158. }
  1159. } else {
  1160. // Get attendance for current user
  1161. $user_id = (int) $user_id;
  1162. if (count($calendar_ids) > 0) {
  1163. $sql = "SELECT cal.date_time, att.presence
  1164. FROM $tbl_attendance_sheet att
  1165. INNER JOIN $tbl_attendance_calendar cal
  1166. ON cal.id = att.attendance_calendar_id
  1167. WHERE
  1168. att.c_id = $course_id AND
  1169. cal.c_id = $course_id AND
  1170. att.user_id = '$user_id' AND
  1171. att.attendance_calendar_id IN (".implode(',', $calendar_ids).")
  1172. ORDER BY date_time";
  1173. $res = Database::query($sql);
  1174. if (Database::num_rows($res) > 0) {
  1175. while ($row = Database::fetch_array($res)) {
  1176. $row['date_time'] = api_convert_and_format_date($row['date_time'], null, date_default_timezone_get());
  1177. $data[$user_id][] = $row;
  1178. }
  1179. }
  1180. }
  1181. }
  1182. return $data;
  1183. }
  1184. /**
  1185. * Get next attendance calendar without presences (done attendances).
  1186. *
  1187. * @param int attendance id
  1188. *
  1189. * @return int attendance calendar id
  1190. */
  1191. public function get_next_attendance_calendar_id($attendanceId)
  1192. {
  1193. $table = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1194. $attendanceId = (int) $attendanceId;
  1195. $course_id = api_get_course_int_id();
  1196. $sql = "SELECT id FROM $table
  1197. WHERE
  1198. c_id = $course_id AND
  1199. attendance_id = '$attendanceId' AND
  1200. done_attendance = 0
  1201. ORDER BY date_time
  1202. LIMIT 1";
  1203. $rs = Database::query($sql);
  1204. $next_calendar_id = 0;
  1205. if (Database::num_rows($rs) > 0) {
  1206. $row = Database::fetch_array($rs);
  1207. $next_calendar_id = $row['id'];
  1208. }
  1209. return $next_calendar_id;
  1210. }
  1211. /**
  1212. * Get next attendance calendar datetime without presences (done attendances).
  1213. *
  1214. * @param int attendance id
  1215. *
  1216. * @return int UNIX time format datetime
  1217. */
  1218. public function getNextAttendanceCalendarDatetime($attendanceId)
  1219. {
  1220. $table = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1221. $course_id = api_get_course_int_id();
  1222. $attendanceId = (int) $attendanceId;
  1223. $sql = "SELECT id, date_time FROM $table
  1224. WHERE
  1225. c_id = $course_id AND
  1226. attendance_id = '$attendanceId' AND
  1227. done_attendance = 0
  1228. ORDER BY date_time
  1229. LIMIT 1";
  1230. $rs = Database::query($sql);
  1231. $next_calendar_datetime = 0;
  1232. if (Database::num_rows($rs) > 0) {
  1233. $row = Database::fetch_array($rs);
  1234. $next_calendar_datetime = api_get_local_time($row['date_time']);
  1235. }
  1236. return $next_calendar_datetime;
  1237. }
  1238. /**
  1239. * Get user's score from current attendance.
  1240. *
  1241. * @param int $user_id
  1242. * @param int $attendanceId
  1243. * @param int $groupId
  1244. *
  1245. * @return int score
  1246. */
  1247. public function get_user_score($user_id, $attendanceId, $groupId = 0)
  1248. {
  1249. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  1250. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  1251. $tbl_attendance_cal_rel_group = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR_REL_GROUP);
  1252. $tbl_attendance_cal = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1253. $user_id = intval($user_id);
  1254. $attendanceId = intval($attendanceId);
  1255. $groupId = (int) $groupId;
  1256. $course_id = api_get_course_int_id();
  1257. if (empty($groupId)) {
  1258. $sql = "SELECT score FROM $tbl_attendance_result
  1259. WHERE
  1260. c_id = $course_id AND
  1261. user_id='$user_id' AND
  1262. attendance_id='$attendanceId'";
  1263. } else {
  1264. $sql = "SELECT count(presence) as score FROM $tbl_attendance_sheet
  1265. WHERE
  1266. c_id = $course_id AND
  1267. user_id='$user_id' AND
  1268. presence = 1 AND
  1269. attendance_calendar_id IN (
  1270. SELECT calendar_id FROM $tbl_attendance_cal_rel_group crg
  1271. INNER JOIN $tbl_attendance_cal c
  1272. ON (crg.calendar_id = c.id)
  1273. WHERE
  1274. crg.c_id = $course_id AND
  1275. crg.group_id = $groupId AND
  1276. c.attendance_id = $attendanceId
  1277. )
  1278. ";
  1279. }
  1280. $rs = Database::query($sql);
  1281. $score = 0;
  1282. if (Database::num_rows($rs) > 0) {
  1283. $row = Database::fetch_array($rs);
  1284. $score = $row['score'];
  1285. }
  1286. return $score;
  1287. }
  1288. /**
  1289. * Get attendance calendar data by id.
  1290. *
  1291. * @param int attendance calendar id
  1292. *
  1293. * @return array attendance calendar data
  1294. */
  1295. public function get_attendance_calendar_by_id($calendar_id)
  1296. {
  1297. $table = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1298. $calendar_id = intval($calendar_id);
  1299. $course_id = api_get_course_int_id();
  1300. $sql = "SELECT * FROM $table
  1301. WHERE c_id = $course_id AND id = '$calendar_id' ";
  1302. $rs = Database::query($sql);
  1303. $data = [];
  1304. if (Database::num_rows($rs) > 0) {
  1305. while ($row = Database::fetch_array($rs)) {
  1306. $row['date_time'] = api_get_local_time($row['date_time']);
  1307. $data = $row;
  1308. }
  1309. }
  1310. return $data;
  1311. }
  1312. /**
  1313. * Get all attendance calendar data inside current attendance.
  1314. *
  1315. * @param int $attendanceId
  1316. * @param string $type
  1317. * @param int $calendar_id
  1318. * @param int $groupId
  1319. * @param bool $showAll = false show group calendar items or not
  1320. *
  1321. * @return array attendance calendar data
  1322. */
  1323. public function get_attendance_calendar(
  1324. $attendanceId,
  1325. $type = 'all',
  1326. $calendar_id = null,
  1327. $groupId = 0,
  1328. $showAll = false
  1329. ) {
  1330. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1331. $tbl_acrg = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR_REL_GROUP);
  1332. $attendanceId = intval($attendanceId);
  1333. $course_id = api_get_course_int_id();
  1334. if ($showAll) {
  1335. $sql = "SELECT * FROM $tbl_attendance_calendar c
  1336. WHERE c_id = $course_id AND attendance_id = '$attendanceId'";
  1337. } else {
  1338. $sql = "SELECT * FROM $tbl_attendance_calendar c
  1339. WHERE
  1340. c_id = $course_id AND
  1341. attendance_id = '$attendanceId' AND
  1342. id NOT IN (
  1343. SELECT calendar_id FROM $tbl_acrg
  1344. WHERE c_id = $course_id AND group_id != 0 AND group_id IS NOT NULL
  1345. )
  1346. ";
  1347. }
  1348. if (!empty($groupId)) {
  1349. $groupId = intval($groupId);
  1350. $sql = "SELECT c.* FROM $tbl_attendance_calendar c
  1351. INNER JOIN $tbl_acrg g
  1352. ON c.c_id = g.c_id AND c.id = g.calendar_id
  1353. WHERE
  1354. c.c_id = $course_id AND
  1355. g.group_id = '$groupId' AND
  1356. c.attendance_id = '$attendanceId'
  1357. ";
  1358. }
  1359. if (!in_array($type, ['today', 'all', 'all_done', 'all_not_done', 'calendar_id'])) {
  1360. $type = 'all';
  1361. }
  1362. switch ($type) {
  1363. case 'calendar_id':
  1364. $calendar_id = intval($calendar_id);
  1365. if (!empty($calendar_id)) {
  1366. $sql .= " AND c.id = $calendar_id";
  1367. }
  1368. break;
  1369. case 'today':
  1370. //$sql .= ' AND DATE_FORMAT(date_time,"%d-%m-%Y") = DATE_FORMAT("'.api_get_utc_datetime().'", "%d-%m-%Y" )';
  1371. break;
  1372. case 'all_done':
  1373. $sql .= " AND done_attendance = 1 ";
  1374. break;
  1375. case 'all_not_done':
  1376. $sql .= " AND done_attendance = 0 ";
  1377. break;
  1378. case 'all':
  1379. default:
  1380. break;
  1381. }
  1382. $sql .= " ORDER BY date_time ";
  1383. $rs = Database::query($sql);
  1384. $data = [];
  1385. if (Database::num_rows($rs) > 0) {
  1386. while ($row = Database::fetch_array($rs, 'ASSOC')) {
  1387. $row['db_date_time'] = $row['date_time'];
  1388. $row['date_time'] = api_get_local_time($row['date_time']);
  1389. $row['date'] = api_format_date($row['date_time'], DATE_FORMAT_SHORT);
  1390. $row['time'] = api_format_date($row['date_time'], TIME_NO_SEC_FORMAT);
  1391. $row['groups'] = $this->getGroupListByAttendanceCalendar($row['id'], $course_id);
  1392. if ($type == 'today') {
  1393. if (date('d-m-Y', api_strtotime($row['date_time'], 'UTC')) == date('d-m-Y', time())) {
  1394. $data[] = $row;
  1395. }
  1396. } else {
  1397. $data[] = $row;
  1398. }
  1399. }
  1400. }
  1401. return $data;
  1402. }
  1403. /**
  1404. * Get number of attendance calendar inside current attendance.
  1405. *
  1406. * @param int $attendanceId
  1407. * @param int $groupId
  1408. * @param $done_attendance
  1409. * @param int $userId
  1410. *
  1411. * @return int number of dates in attendance calendar
  1412. */
  1413. public static function get_number_of_attendance_calendar(
  1414. $attendanceId,
  1415. $groupId = 0,
  1416. $done_attendance = null,
  1417. $userId = 0
  1418. ) {
  1419. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1420. $calendarRelGroup = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR_REL_GROUP);
  1421. $tbl_groupRelUser = Database::get_course_table(TABLE_GROUP_USER);
  1422. $attendanceId = intval($attendanceId);
  1423. $groupId = intval($groupId);
  1424. $course_id = api_get_course_int_id();
  1425. $where_attendance = '';
  1426. if ($done_attendance) {
  1427. $where_attendance = ' done_attendance = 1 AND ';
  1428. }
  1429. if (empty($userId)) {
  1430. if (empty($groupId)) {
  1431. $sql = "SELECT count(a.id)
  1432. FROM $tbl_attendance_calendar a
  1433. WHERE
  1434. c_id = $course_id AND
  1435. $where_attendance
  1436. attendance_id = '$attendanceId' AND
  1437. id NOT IN (
  1438. SELECT calendar_id FROM $calendarRelGroup
  1439. WHERE
  1440. c_id = $course_id AND
  1441. group_id != 0 AND
  1442. group_id IS NOT NULL
  1443. )
  1444. ";
  1445. } else {
  1446. $sql = "SELECT count(a.id)
  1447. FROM $tbl_attendance_calendar a
  1448. INNER JOIN $calendarRelGroup g
  1449. ON (a.id = g.calendar_id AND a.c_id = g.c_id)
  1450. WHERE
  1451. a.c_id = $course_id AND
  1452. $where_attendance
  1453. attendance_id = '$attendanceId' AND
  1454. group_id = $groupId
  1455. ";
  1456. }
  1457. } else {
  1458. if (empty($groupId)) {
  1459. $sql = "SELECT count(a.id)
  1460. FROM $tbl_attendance_calendar a
  1461. WHERE
  1462. c_id = $course_id AND
  1463. $where_attendance
  1464. attendance_id = '$attendanceId' AND
  1465. id NOT IN (
  1466. SELECT calendar_id FROM $calendarRelGroup
  1467. WHERE
  1468. c_id = $course_id AND
  1469. group_id != 0 AND
  1470. group_id IS NOT NULL AND
  1471. group_id NOT IN (
  1472. SELECT group_id
  1473. FROM $tbl_groupRelUser
  1474. WHERE user_id = $userId
  1475. )
  1476. )
  1477. ";
  1478. } else {
  1479. $sql = "SELECT count(a.id)
  1480. FROM $tbl_attendance_calendar a
  1481. INNER JOIN $calendarRelGroup g
  1482. ON (a.id = g.calendar_id AND a.c_id = g.c_id)
  1483. WHERE
  1484. a.c_id = $course_id AND
  1485. $where_attendance
  1486. attendance_id = '$attendanceId' AND
  1487. group_id = $groupId
  1488. ";
  1489. }
  1490. }
  1491. $rs = Database::query($sql);
  1492. $row = Database::fetch_row($rs);
  1493. $count = $row[0];
  1494. return $count;
  1495. }
  1496. /**
  1497. * Get count dates inside attendance calendar by attendance id.
  1498. *
  1499. * @param int $attendanceId
  1500. *
  1501. * @return int count of dates
  1502. */
  1503. public static function get_count_dates_inside_attendance_calendar($attendanceId)
  1504. {
  1505. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1506. $attendanceId = intval($attendanceId);
  1507. $course_id = api_get_course_int_id();
  1508. $sql = "SELECT count(id) FROM $tbl_attendance_calendar
  1509. WHERE
  1510. c_id = $course_id AND
  1511. attendance_id = '$attendanceId'";
  1512. $rs = Database::query($sql);
  1513. $count = 0;
  1514. if (Database::num_rows($rs) > 0) {
  1515. $row = Database::fetch_row($rs);
  1516. $count = $row[0];
  1517. }
  1518. return $count;
  1519. }
  1520. /**
  1521. * check if all calendar of an attendance is done.
  1522. *
  1523. * @param int $attendanceId
  1524. *
  1525. * @return bool True if all calendar is done, otherwise false
  1526. */
  1527. public static function is_all_attendance_calendar_done($attendanceId)
  1528. {
  1529. $attendanceId = (int) $attendanceId;
  1530. $done_calendar = self::get_done_attendance_calendar($attendanceId);
  1531. $count_dates_in_calendar = self::get_count_dates_inside_attendance_calendar($attendanceId);
  1532. $number_of_dates = self::get_number_of_attendance_calendar($attendanceId);
  1533. $result = false;
  1534. if ($number_of_dates && intval($count_dates_in_calendar) == intval($done_calendar)) {
  1535. $result = true;
  1536. }
  1537. return $result;
  1538. }
  1539. /**
  1540. * check if an attendance is locked.
  1541. *
  1542. * @param int $attendanceId
  1543. * @param bool
  1544. *
  1545. * @return bool
  1546. */
  1547. public static function is_locked_attendance($attendanceId)
  1548. {
  1549. // Use gradebook lock
  1550. $result = api_resource_is_locked_by_gradebook($attendanceId, LINK_ATTENDANCE);
  1551. return $result;
  1552. }
  1553. /**
  1554. * Add new datetime inside attendance calendar table.
  1555. *
  1556. * @param int $attendanceId
  1557. * @param array $groupList
  1558. *
  1559. * @return int affected rows
  1560. */
  1561. public function attendance_calendar_add($attendanceId, $groupList = [])
  1562. {
  1563. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1564. $affected_rows = 0;
  1565. $attendanceId = intval($attendanceId);
  1566. $course_id = api_get_course_int_id();
  1567. // check if datetime already exists inside the table
  1568. /*$sql = "SELECT id FROM $tbl_attendance_calendar
  1569. WHERE
  1570. c_id = $course_id AND
  1571. date_time='".Database::escape_string($this->date_time)."' AND
  1572. attendance_id = '$attendanceId'";
  1573. $rs = Database::query($sql);
  1574. if (Database::num_rows($rs) == 0) {*/
  1575. $params = [
  1576. 'c_id' => $course_id,
  1577. 'date_time' => $this->date_time,
  1578. 'attendance_id' => $attendanceId,
  1579. 'done_attendance' => 0,
  1580. ];
  1581. $id = Database::insert($tbl_attendance_calendar, $params);
  1582. if ($id) {
  1583. $sql = "UPDATE $tbl_attendance_calendar SET id = iid WHERE iid = $id";
  1584. Database::query($sql);
  1585. $affected_rows++;
  1586. }
  1587. $this->addAttendanceCalendarToGroup($id, $course_id, $groupList);
  1588. //}
  1589. // update locked attendance
  1590. $is_all_calendar_done = self::is_all_attendance_calendar_done($attendanceId);
  1591. if (!$is_all_calendar_done) {
  1592. self::lock_attendance($attendanceId, false);
  1593. } else {
  1594. self::lock_attendance($attendanceId);
  1595. }
  1596. return $affected_rows;
  1597. }
  1598. /**
  1599. * @param int $calendarId
  1600. * @param int $courseId
  1601. * @param array $groupList
  1602. *
  1603. * @return bool
  1604. */
  1605. public function addAttendanceCalendarToGroup($calendarId, $courseId, $groupList)
  1606. {
  1607. if (empty($groupList)) {
  1608. return false;
  1609. }
  1610. $table = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR_REL_GROUP);
  1611. foreach ($groupList as $groupId) {
  1612. if (empty($groupId)) {
  1613. continue;
  1614. }
  1615. $result = $this->getAttendanceCalendarGroup(
  1616. $calendarId,
  1617. $courseId,
  1618. $groupId
  1619. );
  1620. if (empty($result)) {
  1621. $params = [
  1622. 'calendar_id' => $calendarId,
  1623. 'c_id' => $courseId,
  1624. 'group_id' => $groupId,
  1625. ];
  1626. Database::insert($table, $params);
  1627. }
  1628. }
  1629. return true;
  1630. }
  1631. /**
  1632. * @param int $calendarId
  1633. * @param int $courseId
  1634. *
  1635. * @return array
  1636. */
  1637. public function getGroupListByAttendanceCalendar($calendarId, $courseId)
  1638. {
  1639. $table = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR_REL_GROUP);
  1640. return Database::select(
  1641. '*',
  1642. $table,
  1643. [
  1644. 'where' => [
  1645. 'calendar_id = ? AND c_id = ?' => [$calendarId, $courseId],
  1646. ],
  1647. ]
  1648. );
  1649. }
  1650. /**
  1651. * @param int $calendarId
  1652. * @param int $courseId
  1653. * @param int $groupId
  1654. *
  1655. * @return array
  1656. */
  1657. public function getAttendanceCalendarGroup($calendarId, $courseId, $groupId)
  1658. {
  1659. $table = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR_REL_GROUP);
  1660. return Database::select(
  1661. '*',
  1662. $table,
  1663. [
  1664. 'where' => [
  1665. 'calendar_id = ? AND c_id = ? AND group_id = ?' => [$calendarId, $courseId, $groupId],
  1666. ],
  1667. ]
  1668. );
  1669. }
  1670. /**
  1671. * @param int $calendarId
  1672. * @param int $courseId
  1673. */
  1674. public function deleteAttendanceCalendarGroup($calendarId, $courseId)
  1675. {
  1676. $table = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR_REL_GROUP);
  1677. Database::delete(
  1678. $table,
  1679. [
  1680. 'calendar_id = ? AND c_id = ?' => [$calendarId, $courseId],
  1681. ]
  1682. );
  1683. }
  1684. /**
  1685. * save repeated date inside attendance calendar table.
  1686. *
  1687. * @param int $attendanceId
  1688. * @param int $start_date start date in tms
  1689. * @param int $end_date end date in tms
  1690. * @param string $repeat_type daily, weekly, monthlyByDate
  1691. * @param array $groupList
  1692. */
  1693. public function attendance_repeat_calendar_add(
  1694. $attendanceId,
  1695. $start_date,
  1696. $end_date,
  1697. $repeat_type,
  1698. $groupList = []
  1699. ) {
  1700. $attendanceId = intval($attendanceId);
  1701. // save start date
  1702. $datetimezone = api_get_utc_datetime($start_date);
  1703. $this->set_date_time($datetimezone);
  1704. $this->attendance_calendar_add($attendanceId, $groupList);
  1705. // 86400 = 24 hours in seconds
  1706. // 604800 = 1 week in seconds
  1707. // Saves repeated dates
  1708. switch ($repeat_type) {
  1709. case 'daily':
  1710. $j = 1;
  1711. for ($i = $start_date + 86400; ($i <= $end_date); $i += 86400) {
  1712. $datetimezone = api_get_utc_datetime($i);
  1713. $this->set_date_time($datetimezone);
  1714. $this->attendance_calendar_add($attendanceId, $groupList);
  1715. $j++;
  1716. }
  1717. break;
  1718. case 'weekly':
  1719. $j = 1;
  1720. for ($i = $start_date + 604800; ($i <= $end_date); $i += 604800) {
  1721. $datetimezone = api_get_utc_datetime($i);
  1722. $this->set_date_time($datetimezone);
  1723. $this->attendance_calendar_add($attendanceId, $groupList);
  1724. $j++;
  1725. }
  1726. break;
  1727. case 'monthlyByDate':
  1728. $j = 1;
  1729. //@todo fix bug with february
  1730. for ($i = $start_date + 2419200; ($i <= $end_date); $i += 2419200) {
  1731. $datetimezone = api_get_utc_datetime($i);
  1732. $this->set_date_time($datetimezone);
  1733. $this->attendance_calendar_add($attendanceId, $groupList);
  1734. $j++;
  1735. }
  1736. break;
  1737. }
  1738. }
  1739. /**
  1740. * edit a datetime inside attendance calendar table.
  1741. *
  1742. * @param int attendance calendar id
  1743. * @param int attendance id
  1744. *
  1745. * @return int affected rows
  1746. */
  1747. public function attendance_calendar_edit($calendar_id, $attendanceId)
  1748. {
  1749. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1750. $affected_rows = 0;
  1751. $attendanceId = intval($attendanceId);
  1752. $course_id = api_get_course_int_id();
  1753. // check if datetime already exists inside the table
  1754. $sql = "SELECT id FROM $tbl_attendance_calendar
  1755. WHERE
  1756. c_id = $course_id AND
  1757. date_time = '".Database::escape_string($this->date_time)."' AND
  1758. attendance_id = '$attendanceId'";
  1759. $rs = Database::query($sql);
  1760. if (Database::num_rows($rs) == 0) {
  1761. $sql = "UPDATE $tbl_attendance_calendar
  1762. SET date_time='".Database::escape_string($this->date_time)."'
  1763. WHERE c_id = $course_id AND id = '".intval($calendar_id)."'";
  1764. Database::query($sql);
  1765. }
  1766. // update locked attendance
  1767. $is_all_calendar_done = self::is_all_attendance_calendar_done($attendanceId);
  1768. if (!$is_all_calendar_done) {
  1769. self::lock_attendance($attendanceId, false);
  1770. } else {
  1771. self::lock_attendance($attendanceId);
  1772. }
  1773. return $affected_rows;
  1774. }
  1775. /**
  1776. * delete a datetime from attendance calendar table.
  1777. *
  1778. * @param int attendance calendar id
  1779. * @param int attendance id
  1780. * @param bool true for removing all calendar inside current attendance, false for removing by calendar id
  1781. *
  1782. * @return int affected rows
  1783. */
  1784. public function attendance_calendar_delete(
  1785. $calendar_id,
  1786. $attendanceId,
  1787. $all_delete = false
  1788. ) {
  1789. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1790. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  1791. $attendanceId = intval($attendanceId);
  1792. $calendar_id = (int) $calendar_id;
  1793. // get all registered users inside current course
  1794. $users = $this->get_users_rel_course();
  1795. $user_ids = array_keys($users);
  1796. $course_id = api_get_course_int_id();
  1797. $affected_rows = 0;
  1798. if ($all_delete) {
  1799. $attendance_calendar = $this->get_attendance_calendar($attendanceId);
  1800. // get all dates from calendar by current attendance
  1801. if (!empty($attendance_calendar)) {
  1802. foreach ($attendance_calendar as $cal) {
  1803. // delete all data from attendance sheet
  1804. $sql = "DELETE FROM $tbl_attendance_sheet
  1805. WHERE c_id = $course_id AND attendance_calendar_id = '".intval($cal['id'])."'";
  1806. Database::query($sql);
  1807. // delete data from attendance calendar
  1808. $sql = "DELETE FROM $tbl_attendance_calendar
  1809. WHERE c_id = $course_id AND id = '".intval($cal['id'])."'";
  1810. Database::query($sql);
  1811. $this->deleteAttendanceCalendarGroup($cal['id'], $course_id);
  1812. $affected_rows++;
  1813. }
  1814. }
  1815. } else {
  1816. // delete just one row from attendance sheet by the calendar id
  1817. $sql = "DELETE FROM $tbl_attendance_sheet
  1818. WHERE c_id = $course_id AND attendance_calendar_id = '".$calendar_id."'";
  1819. Database::query($sql);
  1820. // delete data from attendance calendar
  1821. $sql = "DELETE FROM $tbl_attendance_calendar
  1822. WHERE c_id = $course_id AND id = '".$calendar_id."'";
  1823. Database::query($sql);
  1824. $this->deleteAttendanceCalendarGroup($calendar_id, $course_id);
  1825. $affected_rows++;
  1826. }
  1827. // update users' results
  1828. $this->updateUsersResults($user_ids, $attendanceId);
  1829. return $affected_rows;
  1830. }
  1831. public function set_session_id($sessionId)
  1832. {
  1833. $this->session_id = $sessionId;
  1834. }
  1835. public function set_course_id($course_id)
  1836. {
  1837. $this->course_id = $course_id;
  1838. }
  1839. public function set_date_time($datetime)
  1840. {
  1841. $this->date_time = $datetime;
  1842. }
  1843. public function set_name($name)
  1844. {
  1845. $this->name = $name;
  1846. }
  1847. public function set_description($description)
  1848. {
  1849. $this->description = $description;
  1850. }
  1851. public function set_attendance_qualify_title($attendance_qualify_title)
  1852. {
  1853. $this->attendance_qualify_title = $attendance_qualify_title;
  1854. }
  1855. public function set_attendance_weight($attendance_weight)
  1856. {
  1857. $this->attendance_weight = $attendance_weight;
  1858. }
  1859. /** Getters for fields of attendances tables */
  1860. public function get_session_id()
  1861. {
  1862. return $this->session_id;
  1863. }
  1864. public function get_course_id()
  1865. {
  1866. return $this->course_id;
  1867. }
  1868. public function get_date_time()
  1869. {
  1870. return $this->date_time;
  1871. }
  1872. public function get_name()
  1873. {
  1874. return $this->name;
  1875. }
  1876. public function get_description()
  1877. {
  1878. return $this->description;
  1879. }
  1880. public function get_attendance_qualify_title()
  1881. {
  1882. return $this->attendance_qualify_title;
  1883. }
  1884. public function get_attendance_weight()
  1885. {
  1886. return $this->attendance_weight;
  1887. }
  1888. /**
  1889. * @param string $startDate in UTC time
  1890. * @param string $endDate in UTC time
  1891. *
  1892. * @return array
  1893. */
  1894. public function getAttendanceLogin($startDate, $endDate)
  1895. {
  1896. if (empty($startDate) ||
  1897. $startDate == '0000-00-00' ||
  1898. $startDate == '0000-00-00 00:00:00' ||
  1899. empty($endDate) ||
  1900. $endDate == '0000-00-00' ||
  1901. $endDate == '0000-00-00 00:00:00'
  1902. ) {
  1903. return false;
  1904. }
  1905. $sessionId = api_get_session_id();
  1906. $courseCode = api_get_course_id();
  1907. $courseId = api_get_course_int_id();
  1908. if (!empty($sessionId)) {
  1909. $users = CourseManager::get_user_list_from_course_code(
  1910. $courseCode,
  1911. $sessionId,
  1912. '',
  1913. 'lastname',
  1914. 0
  1915. );
  1916. } else {
  1917. $users = CourseManager::get_user_list_from_course_code(
  1918. $courseCode,
  1919. 0,
  1920. '',
  1921. 'lastname',
  1922. STUDENT
  1923. );
  1924. }
  1925. $dateTimeStartOriginal = new DateTime($startDate);
  1926. $dateTimeStart = new DateTime($startDate);
  1927. $dateTimeEnd = new DateTime($endDate);
  1928. $interval = $dateTimeStart->diff($dateTimeEnd);
  1929. $days = intval($interval->format('%a'));
  1930. $dateList = [$dateTimeStart->format('Y-m-d')];
  1931. $headers = [
  1932. get_lang('User'),
  1933. $dateTimeStart->format('Y-m-d'),
  1934. ];
  1935. for ($i = 0; $i < $days; $i++) {
  1936. $dateTimeStart = $dateTimeStart->add(new DateInterval('P1D'));
  1937. $date = $dateTimeStart->format('Y-m-d');
  1938. $dateList[] = $date;
  1939. $headers[] = $date;
  1940. }
  1941. $accessData = CourseManager::getCourseAccessPerCourseAndSession(
  1942. $courseId,
  1943. $sessionId,
  1944. $dateTimeStartOriginal->format('Y-m-d H:i:s'),
  1945. $dateTimeEnd->format('Y-m-d H:i:s')
  1946. );
  1947. $results = [];
  1948. if (!empty($accessData)) {
  1949. foreach ($accessData as $data) {
  1950. $onlyDate = substr($data['login_course_date'], 0, 10);
  1951. $results[$data['user_id']][$onlyDate] = true;
  1952. }
  1953. }
  1954. return [
  1955. 'users' => $users,
  1956. 'dateList' => $dateList,
  1957. 'headers' => $headers,
  1958. 'results' => $results,
  1959. ];
  1960. }
  1961. /**
  1962. * @param string $startDate in UTC time
  1963. * @param string $endDate in UTC time
  1964. *
  1965. * @return string
  1966. */
  1967. public function getAttendanceLoginTable($startDate, $endDate)
  1968. {
  1969. $data = $this->getAttendanceLogin($startDate, $endDate);
  1970. if (!$data) {
  1971. return null;
  1972. }
  1973. $headers = $data['headers'];
  1974. $dateList = $data['dateList'];
  1975. $users = $data['users'];
  1976. $results = $data['results'];
  1977. $table = new HTML_Table(['class' => 'data_table']);
  1978. $row = 0;
  1979. $column = 0;
  1980. foreach ($headers as $header) {
  1981. $table->setHeaderContents($row, $column, $header);
  1982. $column++;
  1983. }
  1984. $row = 1;
  1985. foreach ($users as $user) {
  1986. $table->setCellContents(
  1987. $row,
  1988. 0,
  1989. $user['lastname'].' '.$user['firstname'].' ('.$user['username'].')'
  1990. );
  1991. $row++;
  1992. }
  1993. $column = 1;
  1994. $row = 1;
  1995. foreach ($users as $user) {
  1996. foreach ($dateList as $date) {
  1997. $status = null;
  1998. if (isset($results[$user['user_id']]) &&
  1999. isset($results[$user['user_id']][$date])
  2000. ) {
  2001. $status = 'X';
  2002. }
  2003. $table->setCellContents($row, $column, $status);
  2004. $column++;
  2005. }
  2006. $row++;
  2007. $column = 1;
  2008. }
  2009. return $table->toHtml();
  2010. }
  2011. /**
  2012. * @param string $startDate in UTC time
  2013. * @param string $endDate in UTC time
  2014. *
  2015. * @return string
  2016. */
  2017. public function exportAttendanceLogin($startDate, $endDate)
  2018. {
  2019. $data = $this->getAttendanceLogin($startDate, $endDate);
  2020. if (!$data) {
  2021. return null;
  2022. }
  2023. $users = $data['users'];
  2024. $results = $data['results'];
  2025. $table = new HTML_Table(['class' => 'data_table']);
  2026. $table->setHeaderContents(0, 0, get_lang('User'));
  2027. $table->setHeaderContents(0, 1, get_lang('Date'));
  2028. $row = 1;
  2029. foreach ($users as $user) {
  2030. $table->setCellContents(
  2031. $row,
  2032. 0,
  2033. $user['lastname'].' '.$user['firstname'].' ('.$user['username'].')'
  2034. );
  2035. $row++;
  2036. }
  2037. $table->setColAttributes(0, ['style' => 'width:28%']);
  2038. $row = 1;
  2039. foreach ($users as $user) {
  2040. if (isset($results[$user['user_id']]) &&
  2041. !empty($results[$user['user_id']])
  2042. ) {
  2043. $dates = implode(', ', array_keys($results[$user['user_id']]));
  2044. $table->setCellContents($row, 1, $dates);
  2045. }
  2046. $row++;
  2047. }
  2048. $tableToString = $table->toHtml();
  2049. $params = [
  2050. 'filename' => get_lang('Attendance').'_'.api_get_utc_datetime(),
  2051. 'pdf_title' => get_lang('Attendance'),
  2052. 'course_code' => api_get_course_id(),
  2053. 'show_real_course_teachers' => true,
  2054. ];
  2055. $pdf = new PDF('A4', null, $params);
  2056. $pdf->html_to_pdf_with_template($tableToString);
  2057. }
  2058. }