attendance.lib.php 79 KB

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