attendance.lib.php 78 KB

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