attendance.lib.php 78 KB

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