attendance.lib.php 79 KB

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