attendance.lib.php 55 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509151015111512151315141515151615171518151915201521152215231524152515261527152815291530153115321533153415351536153715381539154015411542154315441545154615471548154915501551155215531554155515561557155815591560156115621563156415651566156715681569157015711572157315741575157615771578157915801581158215831584158515861587158815891590159115921593159415951596159715981599160016011602160316041605160616071608160916101611161216131614161516161617161816191620162116221623162416251626162716281629163016311632163316341635163616371638163916401641164216431644164516461647164816491650165116521653165416551656165716581659166016611662166316641665166616671668166916701671167216731674167516761677167816791680168116821683168416851686168716881689169016911692169316941695169616971698169917001701
  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 db name (optional)
  56. * @param int session id (optional)
  57. * @return array attendances list
  58. */
  59. public function get_attendances_list($course_id = '', $session_id = null)
  60. {
  61. // Initializing database table and variables
  62. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  63. $data = array();
  64. if (empty($course_id)) {
  65. $course_id = api_get_course_int_id();
  66. } else {
  67. $course_id = intval($course_id);
  68. }
  69. $session_id = isset($session_id)?intval($session_id):api_get_session_id();
  70. $condition_session = api_get_session_condition($session_id);
  71. // Get attendance data
  72. $sql = "SELECT id, name, attendance_qualify_max
  73. FROM $tbl_attendance
  74. WHERE c_id = $course_id AND active = 1 $condition_session ";
  75. $rs = Database::query($sql);
  76. if (Database::num_rows($rs) > 0) {
  77. while ($row = Database::fetch_array($rs,'ASSOC')) {
  78. $data[$row['id']] = $row;
  79. }
  80. }
  81. return $data;
  82. }
  83. /**
  84. * Get the attendaces to display on the current page (fill the sortable-table)
  85. * @param int offset of first user to recover
  86. * @param int Number of users to get
  87. * @param int Column to sort on
  88. * @param string Order (ASC,DESC)
  89. * @see SortableTable#get_table_data($from)
  90. */
  91. public static function get_attendance_data($from, $number_of_items, $column, $direction)
  92. {
  93. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  94. $course_id = api_get_course_int_id();
  95. $session_id = api_get_session_id();
  96. $condition_session = api_get_session_condition($session_id);
  97. $column = intval($column);
  98. $from = intval($from);
  99. $number_of_items = intval($number_of_items);
  100. if (!in_array($direction, array('ASC','DESC'))) {
  101. $direction = 'ASC';
  102. }
  103. $active_plus = '';
  104. if ((isset($_GET['isStudentView']) && $_GET['isStudentView'] == 'true') || !api_is_allowed_to_edit(null, true)) {
  105. $active_plus = 'AND att.active = 1';
  106. }
  107. $sql = "SELECT
  108. att.id AS col0,
  109. att.name AS col1,
  110. att.description AS col2,
  111. att.attendance_qualify_max AS col3,
  112. att.locked AS col4,
  113. att.active AS col5,
  114. att.session_id
  115. FROM $tbl_attendance att
  116. WHERE c_id = $course_id $active_plus $condition_session
  117. ORDER BY col$column $direction
  118. LIMIT $from,$number_of_items ";
  119. $res = Database::query($sql);
  120. $attendances = array ();
  121. $param_gradebook = '';
  122. if (isset($_SESSION['gradebook'])) {
  123. $param_gradebook = '&gradebook='.$_SESSION['gradebook'];
  124. }
  125. $user_info = api_get_user_info();
  126. while ($attendance = Database::fetch_row($res)) {
  127. $student_param = '';
  128. if (api_is_drh() && $_GET['student_id']) {
  129. $student_param = '&student_id='.Security::remove_XSS($_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].$param_gradebook.$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].$param_gradebook.$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].$param_gradebook.$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].$param_gradebook.'">'.Display::return_icon('edit.png', get_lang('Edit'), array(), ICON_SIZE_SMALL).'</a>&nbsp;';
  161. if ($attendance[5] == 1) {
  162. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_delete&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('visible.png', get_lang('Hide'), array(), ICON_SIZE_SMALL).'</a>';
  163. } else {
  164. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_restore&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('invisible.png', get_lang('Show'), array(), ICON_SIZE_SMALL).'</a>';
  165. $attendance[2] = '<span class="muted">'.$attendance[2].'</span>';
  166. }
  167. } else {
  168. $is_locked_attendance = self::is_locked_attendance($attendance[0]);
  169. if ($is_locked_attendance) {
  170. $actions .= Display::return_icon('edit_na.png', get_lang('Edit')).'&nbsp;';
  171. $actions .= Display::return_icon('visible.png', get_lang('Hide'));
  172. } else {
  173. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_edit&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('edit.png', get_lang('Edit'), array(), ICON_SIZE_SMALL).'</a>&nbsp;';
  174. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_delete&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('visible.png', get_lang('Hide'), array(), ICON_SIZE_SMALL).'</a>';
  175. }
  176. }
  177. // display lock/unlock icon
  178. $is_done_all_calendar = self::is_all_attendance_calendar_done($attendance[0]);
  179. if ($is_done_all_calendar) {
  180. $locked = $attendance[4];
  181. if ($locked == 0) {
  182. if (api_is_platform_admin()) {
  183. $message_alert = get_lang('AreYouSureToLockTheAttendance');
  184. } else {
  185. $message_alert = get_lang('UnlockMessageInformation');
  186. }
  187. $actions .= '&nbsp;<a onclick="javascript:if(!confirm(\''.$message_alert.'\')) return false;" href="index.php?'.api_get_cidreq().'&action=lock_attendance&attendance_id='.$attendance[0].$param_gradebook.'">'.Display::return_icon('unlock.png', get_lang('LockAttendance')).'</a>';
  188. } else {
  189. if (api_is_platform_admin()) {
  190. $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].$param_gradebook.'">'.Display::return_icon('locked.png', get_lang('UnlockAttendance')).'</a>';
  191. } else {
  192. $actions .= '&nbsp;'.Display::return_icon('locked_na.png', get_lang('LockedAttendance'));
  193. }
  194. }
  195. }
  196. $actions .= '</center>';
  197. $attendances[] = array($attendance[0], $attendance[1], $attendance[2], $attendance[3],$actions);
  198. } else {
  199. $attendance[0] = '&nbsp;';
  200. $attendances[] = array($attendance[0], $attendance[1], $attendance[2], $attendance[3]);
  201. }
  202. }
  203. return $attendances;
  204. }
  205. /**
  206. * Get the attendances by id to display on the current page
  207. * @param int $attendance_id
  208. * @return array attendance data
  209. */
  210. public function get_attendance_by_id($attendance_id)
  211. {
  212. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  213. $attendance_id = intval($attendance_id);
  214. $course_id = api_get_course_int_id();
  215. $attendance_data = array();
  216. $sql = "SELECT * FROM $tbl_attendance
  217. WHERE c_id = $course_id AND id = '$attendance_id'";
  218. $res = Database::query($sql);
  219. if (Database::num_rows($res) > 0) {
  220. while ($row = Database::fetch_array($res)) {
  221. $attendance_data = $row;
  222. }
  223. }
  224. return $attendance_data;
  225. }
  226. /**
  227. * Add attendances sheet inside table. This is the *list of* dates, not
  228. * a specific date in itself.
  229. * @param bool true for adding link in gradebook or false otherwise (optional)
  230. * @return int last attendance id
  231. */
  232. public function attendance_add($link_to_gradebook = false)
  233. {
  234. global $_course;
  235. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  236. $table_link = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  237. $session_id = api_get_session_id();
  238. $user_id = api_get_user_id();
  239. $course_code = api_get_course_id();
  240. $course_id = api_get_course_int_id();
  241. $title_gradebook= Database::escape_string($this->attendance_qualify_title);
  242. $value_calification = 0;
  243. $weight_calification = floatval($this->attendance_weight);
  244. $sql = "INSERT INTO $tbl_attendance SET
  245. c_id = $course_id,
  246. name ='".Database::escape_string($this->name)."',
  247. description = '".Database::escape_string($this->description)."',
  248. attendance_qualify_title = '$title_gradebook',
  249. attendance_weight = '$weight_calification',
  250. session_id = '$session_id'";
  251. Database::query($sql);
  252. $affected_rows = Database::affected_rows();
  253. $last_id = 0;
  254. if (!empty($affected_rows)) {
  255. // save inside item property table
  256. $last_id = Database::insert_id();
  257. api_item_property_update($_course, TOOL_ATTENDANCE, $last_id,"AttendanceAdded", $user_id);
  258. }
  259. // add link to gradebook
  260. if ($link_to_gradebook && !empty($this->category_id)) {
  261. $description = '';
  262. $link_info = is_resource_in_course_gradebook($course_code,7,$last_id,$session_id);
  263. $link_id = $link_info['id'];
  264. if (!$link_info) {
  265. add_resource_to_course_gradebook(
  266. $this->category_id,
  267. $course_code,
  268. 7,
  269. $last_id,
  270. $title_gradebook,
  271. $weight_calification,
  272. $value_calification,
  273. $description,
  274. 1,
  275. $session_id
  276. );
  277. } else {
  278. Database::query('UPDATE '.$table_link.' SET weight='.$weight_calification.' WHERE id='.$link_id.'');
  279. }
  280. }
  281. return $last_id;
  282. }
  283. /**
  284. * edit attendances inside table
  285. * @param int attendance id
  286. * @param bool true for adding link in gradebook or false otherwise (optional)
  287. * @return int last id
  288. */
  289. public function attendance_edit($attendance_id, $link_to_gradebook = false)
  290. {
  291. $_course = api_get_course_info();
  292. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  293. $table_link = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  294. $session_id = api_get_session_id();
  295. $user_id = api_get_user_id();
  296. $attendance_id = intval($attendance_id);
  297. $course_code = api_get_course_id();
  298. $course_id = api_get_course_int_id();
  299. $title_gradebook = Database::escape_string($this->attendance_qualify_title);
  300. $value_calification = 0;
  301. $weight_calification= floatval($this->attendance_weight);
  302. if (!empty($attendance_id)) {
  303. $sql = "UPDATE $tbl_attendance
  304. SET name ='".Database::escape_string($this->name)."',
  305. description = '".Database::escape_string($this->description)."',
  306. attendance_qualify_title = '".$title_gradebook."',
  307. attendance_weight = '".$weight_calification."'
  308. WHERE c_id = $course_id AND id = '$attendance_id'";
  309. Database::query($sql);
  310. api_item_property_update($_course, TOOL_ATTENDANCE, $attendance_id,"AttendanceUpdated", $user_id);
  311. // add link to gradebook
  312. if ($link_to_gradebook && !empty($this->category_id)) {
  313. $description = '';
  314. $link_id = is_resource_in_course_gradebook($course_code, 7, $attendance_id, $session_id);
  315. if (!$link_id) {
  316. add_resource_to_course_gradebook(
  317. $this->category_id,
  318. $course_code,
  319. 7,
  320. $attendance_id,
  321. $title_gradebook,
  322. $weight_calification,
  323. $value_calification,
  324. $description,
  325. 1,
  326. $session_id
  327. );
  328. } else {
  329. Database::query('UPDATE '.$table_link.' SET weight='.$weight_calification.' WHERE id='.$link_id.'');
  330. }
  331. }
  332. return $attendance_id;
  333. }
  334. return null;
  335. }
  336. /**
  337. * Restore attendance
  338. * @param int|array one or many attendances id
  339. * @return int affected rows
  340. */
  341. public function attendance_restore($attendance_id)
  342. {
  343. $_course = api_get_course_info();
  344. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  345. $user_id = api_get_user_id();
  346. $course_id = api_get_course_int_id();
  347. if (is_array($attendance_id)) {
  348. foreach ($attendance_id as $id) {
  349. $id = intval($id);
  350. $sql = "UPDATE $tbl_attendance SET active = 1
  351. WHERE c_id = $course_id AND id = '$id'";
  352. Database::query($sql);
  353. $affected_rows = Database::affected_rows();
  354. if (!empty($affected_rows)) {
  355. // update row item property table
  356. api_item_property_update($_course, TOOL_ATTENDANCE, $id,"restore", $user_id);
  357. }
  358. }
  359. } else {
  360. $attendance_id = intval($attendance_id);
  361. $sql = "UPDATE $tbl_attendance SET active = 1
  362. WHERE c_id = $course_id AND id = '$attendance_id'";
  363. Database::query($sql);
  364. $affected_rows = Database::affected_rows();
  365. if (!empty($affected_rows)) {
  366. // update row item property table
  367. api_item_property_update($_course, TOOL_ATTENDANCE, $attendance_id,"restore", $user_id);
  368. }
  369. }
  370. return $affected_rows;
  371. }
  372. /**
  373. * Delete attendances
  374. * @param int|array one or many attendances id
  375. * @return int affected rows
  376. */
  377. public function attendance_delete($attendance_id)
  378. {
  379. $_course = api_get_course_info();
  380. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  381. $user_id = api_get_user_id();
  382. $course_id = api_get_course_int_id();
  383. if (is_array($attendance_id)) {
  384. foreach ($attendance_id as $id) {
  385. $id = intval($id);
  386. $sql = "UPDATE $tbl_attendance SET active = 0
  387. WHERE c_id = $course_id AND id = '$id'";
  388. Database::query($sql);
  389. $affected_rows = Database::affected_rows();
  390. if (!empty($affected_rows)) {
  391. // update row item property table
  392. api_item_property_update($_course, TOOL_ATTENDANCE, $id,"delete", $user_id);
  393. }
  394. }
  395. } else {
  396. $attendance_id = intval($attendance_id);
  397. $sql = "UPDATE $tbl_attendance SET active = 0
  398. WHERE c_id = $course_id AND id = '$attendance_id'";
  399. Database::query($sql);
  400. $affected_rows = Database::affected_rows();
  401. if (!empty($affected_rows)) {
  402. // update row item property table
  403. api_item_property_update(
  404. $_course,
  405. TOOL_ATTENDANCE,
  406. $attendance_id,
  407. "delete",
  408. $user_id
  409. );
  410. }
  411. }
  412. return $affected_rows;
  413. }
  414. /**
  415. * Lock or unlock an attendance
  416. * @param int attendance id
  417. * @param bool True to lock or false otherwise
  418. */
  419. public function lock_attendance($attendance_id, $lock = true)
  420. {
  421. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  422. $course_id = api_get_course_int_id();
  423. $attendance_id = intval($attendance_id);
  424. $locked = ($lock)?1:0;
  425. $upd = "UPDATE $tbl_attendance SET locked = $locked
  426. WHERE c_id = $course_id AND id = $attendance_id";
  427. Database::query($upd);
  428. $affected_rows = Database::affected_rows();
  429. if ($affected_rows && $lock) {
  430. //save attendance sheet log
  431. $lastedit_date = Date('Y-m-d H:i:s');
  432. $lastedit_type = self::LOCKED_ATTENDANCE_LOG_TYPE;
  433. $lastedit_user_id = api_get_user_id();
  434. $this->save_attendance_sheet_log(
  435. $attendance_id,
  436. $lastedit_date,
  437. $lastedit_type,
  438. $lastedit_user_id
  439. );
  440. }
  441. return $affected_rows;
  442. }
  443. /**
  444. * Get registered users inside current course
  445. * @param int $attendance_id attendance id for showing attendance result field (optional)
  446. * @return array users data
  447. */
  448. public function get_users_rel_course($attendance_id = 0)
  449. {
  450. $current_session_id = api_get_session_id();
  451. $current_course_id = api_get_course_id();
  452. if (!empty($current_session_id)) {
  453. $a_course_users = CourseManager:: get_user_list_from_course_code(
  454. $current_course_id,
  455. $current_session_id,
  456. '',
  457. 'lastname'
  458. );
  459. } else {
  460. $a_course_users = CourseManager:: get_user_list_from_course_code(
  461. $current_course_id,
  462. 0,
  463. '',
  464. 'lastname'
  465. );
  466. }
  467. // get registered users inside current course
  468. $a_users = array();
  469. foreach ($a_course_users as $key =>$user_data) {
  470. $value = array();
  471. $uid = $user_data['user_id'];
  472. $status = $user_data['status'];
  473. $user_status_in_session = null;
  474. $user_status_in_course = null;
  475. if (api_get_session_id()) {
  476. $user_status_in_session = SessionManager::get_user_status_in_course_session(
  477. $uid,
  478. $current_course_id,
  479. $current_session_id
  480. );
  481. } else {
  482. $user_status_in_course = CourseManager::get_user_in_course_status(
  483. $uid,
  484. $current_course_id
  485. );
  486. }
  487. //Not taking into account DRH or COURSEMANAGER
  488. if ($uid <= 1 || $status == DRH || $user_status_in_course == COURSEMANAGER || $user_status_in_session == 2) continue;
  489. if (!empty($attendance_id)) {
  490. $user_faults = $this->get_faults_of_user($uid, $attendance_id);
  491. $value['attendance_result'] = $user_faults['faults'].'/'.$user_faults['total'].' ('.$user_faults['faults_porcent'].'%)';
  492. $value['result_color_bar'] = $user_faults['color_bar'];
  493. }
  494. // user's picture
  495. $image_path = UserManager::get_user_picture_path_by_id($uid, 'web', false);
  496. $user_profile = UserManager::get_picture_user($uid, $image_path['file'], 22, USER_IMAGE_SIZE_SMALL, ' width="22" height="22" ');
  497. if (!empty($image_path['file'])) {
  498. $photo = '<center><a class="thickbox" href="'.$image_path['dir'].$image_path['file'].'" ><img src="'.$user_profile['file'].'" '.$user_profile['style'].' alt="'.api_get_person_name($user_data['firstname'], $user_data['lastname']).'" title="'.api_get_person_name($user_data['firstname'], $user_data['lastname']).'" /></a></center>';
  499. } else {
  500. $photo = '<center><img src="'.$user_profile['file'].'" '.$user_profile['style'].' alt="'.api_get_person_name($user_data['firstname'], $user_data['lastname']).'" title="'.api_get_person_name($user_data['firstname'], $user_data['lastname']).'" /></center>';
  501. }
  502. $value['photo'] = $photo;
  503. $value['firstname'] = $user_data['firstname'];
  504. $value['lastname'] = $user_data['lastname'];
  505. $value['username'] = $user_data['username'];
  506. $value['user_id'] = $uid;
  507. //Sending only 5 items in the array instead of 60
  508. $a_users[$key] = $value;
  509. }
  510. return $a_users;
  511. }
  512. /**
  513. * add attendances sheet inside table
  514. * @param int $calendar_id attendance calendar id
  515. * @param array $users_present present users during current class
  516. * @param int $attendance_id
  517. * @return int affected rows
  518. */
  519. public function attendance_sheet_add($calendar_id, $users_present, $attendance_id)
  520. {
  521. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  522. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  523. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  524. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  525. $calendar_id = intval($calendar_id);
  526. $attendance_id = intval($attendance_id);
  527. $users = $this->get_users_rel_course();
  528. $course_id = api_get_course_int_id();
  529. $user_ids = array_keys($users);
  530. $users_absent = array_diff($user_ids,$users_present);
  531. $affected_rows = 0;
  532. // get last edit type
  533. $calendar_data = $this->get_attendance_calendar_by_id($calendar_id);
  534. $lastedit_type = self::DONE_ATTENDANCE_LOG_TYPE;
  535. if ($calendar_data['done_attendance']) {
  536. $lastedit_type = self::UPDATED_ATTENDANCE_LOG_TYPE;
  537. }
  538. // save users present in class
  539. foreach ($users_present as $user_present) {
  540. $uid = intval($user_present);
  541. // check if user already was registered with the $calendar_id
  542. $sql = "SELECT user_id FROM $tbl_attendance_sheet
  543. WHERE c_id = $course_id AND user_id='$uid' AND attendance_calendar_id = '$calendar_id'";
  544. $rs = Database::query($sql);
  545. if (Database::num_rows($rs) == 0) {
  546. $sql = "INSERT INTO $tbl_attendance_sheet SET
  547. c_id = $course_id,
  548. user_id = '$uid',
  549. attendance_calendar_id = '$calendar_id',
  550. presence = 1";
  551. Database::query($sql);
  552. $affected_rows += Database::affected_rows();
  553. } else {
  554. $sql = "UPDATE $tbl_attendance_sheet SET presence = 1
  555. WHERE c_id = $course_id AND user_id ='$uid' AND attendance_calendar_id = '$calendar_id'";
  556. Database::query($sql);
  557. $affected_rows += Database::affected_rows();
  558. }
  559. }
  560. // save users absent in class
  561. foreach ($users_absent as $user_absent) {
  562. $uid = intval($user_absent);
  563. // check if user already was registered with the $calendar_id
  564. $sql = "SELECT user_id FROM $tbl_attendance_sheet
  565. WHERE c_id = $course_id AND user_id='$uid' AND attendance_calendar_id = '$calendar_id'";
  566. $rs = Database::query($sql);
  567. if (Database::num_rows($rs) == 0) {
  568. $sql = "INSERT INTO $tbl_attendance_sheet SET
  569. c_id = $course_id,
  570. user_id ='$uid',
  571. attendance_calendar_id = '$calendar_id',
  572. presence = 0";
  573. Database::query($sql);
  574. $affected_rows += Database::affected_rows();
  575. } else {
  576. $sql = "UPDATE $tbl_attendance_sheet SET presence = 0
  577. WHERE c_id = $course_id AND user_id ='$uid' AND attendance_calendar_id = '$calendar_id'";
  578. Database::query($sql);
  579. $affected_rows += Database::affected_rows();
  580. }
  581. }
  582. // update done_attendance inside attendance calendar table
  583. $sql = "UPDATE $tbl_attendance_calendar SET done_attendance = 1
  584. WHERE c_id = $course_id AND id = '$calendar_id'";
  585. Database::query($sql);
  586. // save users' results
  587. $this->update_users_results($user_ids, $attendance_id);
  588. if ($affected_rows) {
  589. //save attendance sheet log
  590. $lastedit_date = Date('Y-m-d H:i:s');
  591. $lastedit_user_id = api_get_user_id();
  592. $calendar_date_value = $calendar_data['date_time'];
  593. $this->save_attendance_sheet_log(
  594. $attendance_id,
  595. $lastedit_date,
  596. $lastedit_type,
  597. $lastedit_user_id,
  598. $calendar_date_value
  599. );
  600. }
  601. return $affected_rows;
  602. }
  603. /**
  604. * update users' attendance results
  605. * @param array $user_ids registered users inside current course
  606. * @param int $attendance_id
  607. * @return void
  608. */
  609. public function update_users_results($user_ids, $attendance_id)
  610. {
  611. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  612. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  613. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  614. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  615. $course_id = api_get_course_int_id();
  616. $attendance_id = intval($attendance_id);
  617. // fill results about presence of students
  618. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  619. $calendar_ids = array();
  620. // get all dates from calendar by current attendance
  621. foreach ($attendance_calendar as $cal) {
  622. $calendar_ids[] = $cal['id'];
  623. }
  624. // get count of presences by users inside current attendance and save like results
  625. $count_presences = 0;
  626. if (count($user_ids) > 0) {
  627. foreach ($user_ids as $uid) {
  628. $count_presences = 0;
  629. if (count($calendar_ids) > 0) {
  630. $sql = "SELECT count(presence) as count_presences
  631. FROM $tbl_attendance_sheet
  632. WHERE
  633. c_id = $course_id AND
  634. user_id = '$uid' AND
  635. attendance_calendar_id IN(".implode(',',$calendar_ids).") AND
  636. presence = 1";
  637. $rs_count = Database::query($sql);
  638. $row_count = Database::fetch_array($rs_count);
  639. $count_presences = $row_count['count_presences'];
  640. }
  641. // save results
  642. $sql = "SELECT id FROM $tbl_attendance_result
  643. WHERE c_id = $course_id AND user_id='$uid' AND attendance_id='$attendance_id'";
  644. $rs_check_result = Database::query($sql);
  645. if (Database::num_rows($rs_check_result) > 0) {
  646. // update result
  647. $sql = "UPDATE $tbl_attendance_result SET
  648. score='$count_presences'
  649. WHERE c_id = $course_id AND user_id='$uid' AND attendance_id='$attendance_id'";
  650. Database::query($sql);
  651. } else {
  652. // insert new result
  653. $sql = "INSERT INTO $tbl_attendance_result SET
  654. c_id = $course_id ,
  655. user_id = '$uid',
  656. attendance_id = '$attendance_id',
  657. score = '$count_presences'";
  658. Database::query($sql);
  659. }
  660. }
  661. }
  662. // update attendance qualify max
  663. $count_done_calendar = self::get_done_attendance_calendar($attendance_id);
  664. $sql = "UPDATE $tbl_attendance SET attendance_qualify_max='$count_done_calendar'
  665. WHERE c_id = $course_id AND id = '$attendance_id'";
  666. Database::query($sql);
  667. }
  668. /**
  669. * update attendance_sheet_log table, is used as history of an attendance sheet
  670. * @param int Attendance id
  671. * @param string Last edit datetime
  672. * @param string Event type ('locked_attendance', 'done_attendance_sheet' ...)
  673. * @param int Last edit user id
  674. * @param string Calendar datetime value (optional, when event type is 'done_attendance_sheet')
  675. * @return int Affected rows
  676. */
  677. public function save_attendance_sheet_log($attendance_id, $lastedit_date, $lastedit_type, $lastedit_user_id, $calendar_date_value = null)
  678. {
  679. $course_id = api_get_course_int_id();
  680. // define table
  681. $tbl_attendance_sheet_log = Database::get_course_table(TABLE_ATTENDANCE_SHEET_LOG);
  682. // protect data
  683. $attendance_id = intval($attendance_id);
  684. $lastedit_date = Database::escape_string($lastedit_date);
  685. $lastedit_type = Database::escape_string($lastedit_type);
  686. $lastedit_user_id = intval($lastedit_user_id);
  687. if (isset($calendar_date_value)) {
  688. $calendar_date_value = Database::escape_string($calendar_date_value);
  689. } else {
  690. $calendar_date_value = '';
  691. }
  692. // save data
  693. $ins = "INSERT INTO $tbl_attendance_sheet_log (c_id, attendance_id, lastedit_date, lastedit_type, lastedit_user_id, calendar_date_value)
  694. VALUES ($course_id, $attendance_id, '$lastedit_date', '$lastedit_type', $lastedit_user_id, '$calendar_date_value')";
  695. Database::query($ins);
  696. return Database::affected_rows();
  697. }
  698. /**
  699. * Get number of done attendances inside current sheet
  700. * @param int attendance id
  701. * @return int number of done attendances
  702. */
  703. public static function get_done_attendance_calendar($attendance_id)
  704. {
  705. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  706. $attendance_id = intval($attendance_id);
  707. $course_id = api_get_course_int_id();
  708. $sql = "SELECT count(done_attendance) as count
  709. FROM $tbl_attendance_calendar
  710. WHERE c_id = $course_id AND attendance_id = '$attendance_id' AND done_attendance=1";
  711. $rs = Database::query($sql);
  712. $row = Database::fetch_array($rs);
  713. $count = $row['count'];
  714. return $count;
  715. }
  716. /**
  717. * Get results of faults (absents) by user
  718. * @param int user id
  719. * @param int attendance id
  720. * @return array results containing number of faults, total done attendance,
  721. * percent of faults and color depend on result (red, orange)
  722. */
  723. public function get_faults_of_user($user_id, $attendance_id)
  724. {
  725. // initializing database table and variables
  726. $user_id = intval($user_id);
  727. $attendance_id = intval($attendance_id);
  728. $results = array();
  729. $attendance_data = $this->get_attendance_by_id($attendance_id);
  730. $calendar_count = self::get_number_of_attendance_calendar($attendance_id);
  731. $total_done_attendance = $attendance_data['attendance_qualify_max'];
  732. $attendance_user_score = $this->get_user_score($user_id, $attendance_id);
  733. //This is the main change of the BT#1381
  734. //$total_done_attendance = $calendar_count;
  735. // calculate results
  736. $faults = $total_done_attendance - $attendance_user_score;
  737. $faults = $faults > 0 ? $faults:0;
  738. $faults_porcent = $calendar_count > 0 ?round(($faults*100)/$calendar_count,0):0;
  739. $results['faults'] = $faults;
  740. $results['total'] = $calendar_count;
  741. $results['faults_porcent'] = $faults_porcent;
  742. $color_bar = '';
  743. if ($faults_porcent > 25 ) {
  744. $color_bar = '#f28989';
  745. } else if ($faults_porcent > 10) {
  746. $color_bar = '#F90';
  747. }
  748. $results['color_bar'] = $color_bar;
  749. return $results;
  750. }
  751. /**
  752. * Get results of faults average for all courses by user
  753. * @param int $user_id
  754. * @return array results containing number of faults, total done attendance,
  755. * percentage of faults and color depend on result (red, orange)
  756. */
  757. public function get_faults_average_inside_courses($user_id)
  758. {
  759. // get all courses of current user
  760. $courses = CourseManager::get_courses_list_by_user_id($user_id, true);
  761. $user_id = intval($user_id);
  762. $results = array();
  763. $total_faults = $total_weight = $porcent = 0;
  764. foreach ($courses as $course) {
  765. //$course_code = $course['code'];
  766. //$course_info = api_get_course_info($course_code);
  767. $course_id = $course['real_id'];
  768. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  769. $attendances_by_course = $this->get_attendances_list($course_id);
  770. foreach ($attendances_by_course as $attendance) {
  771. // get total faults and total weight
  772. $total_done_attendance = $attendance['attendance_qualify_max'];
  773. $sql = "SELECT score
  774. FROM $tbl_attendance_result
  775. WHERE
  776. c_id = $course_id AND
  777. user_id = $user_id AND
  778. attendance_id = ".$attendance['id'];
  779. $rs = Database::query($sql);
  780. $score = 0;
  781. if (Database::num_rows($rs) > 0) {
  782. $row = Database::fetch_array($rs);
  783. $score = $row['score'];
  784. }
  785. $faults = $total_done_attendance-$score;
  786. $faults = $faults > 0 ? $faults:0;
  787. $total_faults += $faults;
  788. $total_weight += $total_done_attendance;
  789. }
  790. }
  791. $porcent = $total_weight > 0 ?round(($total_faults*100)/$total_weight,0):0;
  792. $results['faults'] = $total_faults;
  793. $results['total'] = $total_weight;
  794. $results['porcent'] = $porcent;
  795. return $results;
  796. }
  797. /**
  798. * Get results of faults average by course
  799. * @param int $user_id
  800. * @param string $course_code
  801. * @param int Session id (optional)
  802. * @return array results containing number of faults,
  803. * total done attendance, porcent of faults and color depend on result (red, orange)
  804. */
  805. public function get_faults_average_by_course($user_id, $course_code, $session_id = null)
  806. {
  807. // Database tables and variables
  808. $course_info = api_get_course_info($course_code);
  809. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  810. $user_id = intval($user_id);
  811. $results = array();
  812. $total_faults = $total_weight = $porcent = 0;
  813. $attendances_by_course = $this->get_attendances_list($course_info['real_id'], $session_id);
  814. foreach ($attendances_by_course as $attendance) {
  815. // Get total faults and total weight
  816. $total_done_attendance = $attendance['attendance_qualify_max'];
  817. $sql = "SELECT score FROM $tbl_attendance_result
  818. WHERE
  819. c_id = {$course_info['real_id']} AND
  820. user_id = $user_id AND
  821. attendance_id=".$attendance['id'];
  822. $rs = Database::query($sql);
  823. $score = 0;
  824. if (Database::num_rows($rs) > 0) {
  825. $row = Database::fetch_array($rs);
  826. $score = $row['score'];
  827. }
  828. $faults = $total_done_attendance-$score;
  829. $faults = $faults > 0 ? $faults:0;
  830. $total_faults += $faults;
  831. $total_weight += $total_done_attendance;
  832. }
  833. $porcent = $total_weight > 0 ?round(($total_faults*100)/$total_weight,0):0;
  834. $results['faults'] = $total_faults;
  835. $results['total'] = $total_weight;
  836. $results['porcent'] = $porcent;
  837. return $results;
  838. }
  839. /**
  840. * Get registered users' attendance sheet inside current course
  841. * @param int $attendance_id
  842. * @param int $user_id for showing data for only one user (optional)
  843. * @return array users attendance sheet data
  844. */
  845. public function get_users_attendance_sheet($attendance_id, $user_id = 0)
  846. {
  847. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  848. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  849. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  850. $calendar_ids = array();
  851. // get all dates from calendar by current attendance
  852. foreach ($attendance_calendar as $cal) {
  853. $calendar_ids[] = $cal['id'];
  854. }
  855. $course_id = api_get_course_int_id();
  856. $data = array();
  857. if (empty($user_id)) {
  858. // get all registered users inside current course
  859. $users = $this->get_users_rel_course();
  860. $user_ids = array_keys($users);
  861. if (count($calendar_ids) > 0 && count($user_ids) > 0) {
  862. foreach ($user_ids as $uid) {
  863. $sql = "SELECT * FROM $tbl_attendance_sheet
  864. WHERE
  865. c_id = $course_id AND
  866. user_id = '$uid' AND
  867. attendance_calendar_id IN(".implode(',',$calendar_ids).")
  868. ";
  869. $res = Database::query($sql);
  870. if (Database::num_rows($res) > 0) {
  871. while ($row = Database::fetch_array($res)) {
  872. $data[$uid][$row['attendance_calendar_id']]['presence'] = $row['presence'];
  873. }
  874. }
  875. }
  876. }
  877. } else {
  878. // Get attendance for current user
  879. $user_id = intval($user_id);
  880. if (count($calendar_ids) > 0) {
  881. $sql = "SELECT cal.date_time, att.presence
  882. FROM $tbl_attendance_sheet att
  883. INNER JOIN $tbl_attendance_calendar cal
  884. ON cal.id = att.attendance_calendar_id
  885. WHERE
  886. att.c_id = $course_id AND
  887. cal.c_id = $course_id AND
  888. att.user_id = '$user_id' AND
  889. att.attendance_calendar_id IN (".implode(',',$calendar_ids).")
  890. ORDER BY date_time";
  891. $res = Database::query($sql);
  892. if (Database::num_rows($res) > 0) {
  893. while ($row = Database::fetch_array($res)) {
  894. $row['date_time'] = api_convert_and_format_date($row['date_time'], null, date_default_timezone_get());
  895. $data[$user_id][] = $row;
  896. }
  897. }
  898. }
  899. }
  900. return $data;
  901. }
  902. /**
  903. * Get next attendance calendar without presences (done attendances)
  904. * @param int attendance id
  905. * @return int attendance calendar id
  906. */
  907. public function get_next_attendance_calendar_id($attendance_id)
  908. {
  909. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  910. $attendance_id = intval($attendance_id);
  911. $course_id = api_get_course_int_id();
  912. $sql = "SELECT id FROM $tbl_attendance_calendar
  913. WHERE
  914. c_id = $course_id AND
  915. attendance_id = '$attendance_id' AND
  916. done_attendance = 0
  917. ORDER BY date_time
  918. LIMIT 1";
  919. $rs = Database::query($sql);
  920. $next_calendar_id = 0;
  921. if (Database::num_rows($rs) > 0) {
  922. $row = Database::fetch_array($rs);
  923. $next_calendar_id = $row['id'];
  924. }
  925. return $next_calendar_id;
  926. }
  927. /**
  928. * Get next attendance calendar datetime without presences (done attendances)
  929. * @param int attendance id
  930. * @return int UNIX time format datetime
  931. */
  932. public function get_next_attendance_calendar_datetime($attendance_id)
  933. {
  934. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  935. $course_id = api_get_course_int_id();
  936. $attendance_id = intval($attendance_id);
  937. $sql = "SELECT id, date_time FROM $tbl_attendance_calendar
  938. WHERE
  939. c_id = $course_id AND
  940. attendance_id = '$attendance_id' AND
  941. done_attendance = 0
  942. ORDER BY date_time
  943. LIMIT 1";
  944. $rs = Database::query($sql);
  945. $next_calendar_datetime = 0;
  946. if (Database::num_rows($rs) > 0) {
  947. $row = Database::fetch_array($rs);
  948. $next_calendar_datetime = api_get_local_time($row['date_time']);
  949. }
  950. return $next_calendar_datetime;
  951. }
  952. /**
  953. * Get user' score from current attendance
  954. * @param int $user_id
  955. * @param int $attendance_id
  956. * @return int score
  957. */
  958. public function get_user_score($user_id, $attendance_id)
  959. {
  960. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  961. $user_id = intval($user_id);
  962. $attendance_id = intval($attendance_id);
  963. $course_id = api_get_course_int_id();
  964. $sql = "SELECT score FROM $tbl_attendance_result
  965. WHERE
  966. c_id = $course_id AND
  967. user_id='$user_id' AND
  968. attendance_id='$attendance_id'";
  969. $rs = Database::query($sql);
  970. $score = 0;
  971. if (Database::num_rows($rs) > 0) {
  972. $row = Database::fetch_array($rs);
  973. $score = $row['score'];
  974. }
  975. return $score;
  976. }
  977. /**
  978. * Get attendance calendar data by id
  979. * @param int attendance calendar id
  980. * @return array attendance calendar data
  981. */
  982. public function get_attendance_calendar_by_id($calendar_id)
  983. {
  984. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  985. $calendar_id = intval($calendar_id);
  986. $course_id = api_get_course_int_id();
  987. $sql = "SELECT * FROM $tbl_attendance_calendar
  988. WHERE c_id = $course_id AND id = '$calendar_id' ";
  989. $rs = Database::query($sql);
  990. $data = array();
  991. if (Database::num_rows($rs) > 0) {
  992. while ($row = Database::fetch_array($rs)) {
  993. $row['date_time'] = api_get_local_time($row['date_time']);
  994. $data = $row;
  995. }
  996. }
  997. return $data;
  998. }
  999. /**
  1000. * Get all attendance calendar data inside current attendance
  1001. * @param int attendance id
  1002. * @return array attendance calendar data
  1003. */
  1004. public function get_attendance_calendar($attendance_id, $type = 'all', $calendar_id = null)
  1005. {
  1006. global $dateFormatShort, $timeNoSecFormat;
  1007. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1008. $attendance_id = intval($attendance_id);
  1009. $course_id = api_get_course_int_id();
  1010. $sql = "SELECT * FROM $tbl_attendance_calendar
  1011. WHERE c_id = $course_id AND attendance_id = '$attendance_id' ";
  1012. if (!in_array($type, array('today', 'all', 'all_done', 'all_not_done','calendar_id'))) {
  1013. $type = 'all';
  1014. }
  1015. switch ($type) {
  1016. case 'calendar_id':
  1017. $calendar_id = intval($calendar_id);
  1018. if (!empty($calendar_id)) {
  1019. $sql.= " AND id = $calendar_id";
  1020. }
  1021. break;
  1022. case 'today':
  1023. //$sql .= ' AND DATE_FORMAT(date_time,"%d-%m-%Y") = DATE_FORMAT("'.api_get_utc_datetime().'", "%d-%m-%Y" )';
  1024. break;
  1025. case 'all_done':
  1026. $sql .= " AND done_attendance = 1 ";
  1027. break;
  1028. case 'all_not_done':
  1029. $sql .= " AND done_attendance = 0 ";
  1030. break;
  1031. case 'all':
  1032. default:
  1033. break;
  1034. }
  1035. $sql .= " ORDER BY date_time ";
  1036. $rs = Database::query($sql);
  1037. $data = array();
  1038. if (Database::num_rows($rs) > 0) {
  1039. while ($row = Database::fetch_array($rs,'ASSOC')) {
  1040. $row['db_date_time'] = $row['date_time'];
  1041. $row['date_time'] = api_get_local_time($row['date_time']);
  1042. $row['date'] = api_format_date($row['date_time'], DATE_FORMAT_SHORT);
  1043. $row['time'] = api_format_date($row['date_time'], TIME_NO_SEC_FORMAT);
  1044. if ($type == 'today') {
  1045. if (date('d-m-Y', api_strtotime($row['date_time'], 'UTC')) == date('d-m-Y', time())) {
  1046. $data[] = $row;
  1047. }
  1048. } else {
  1049. $data[] = $row;
  1050. }
  1051. }
  1052. }
  1053. return $data;
  1054. }
  1055. /**
  1056. * Get number of attendance calendar inside current attendance
  1057. * @param int $attendance_id
  1058. * @return int number of dates in attendance calendar
  1059. */
  1060. public static function get_number_of_attendance_calendar($attendance_id)
  1061. {
  1062. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1063. $attendance_id = intval($attendance_id);
  1064. $course_id = api_get_course_int_id();
  1065. $sql = "SELECT count(id) FROM $tbl_attendance_calendar
  1066. WHERE c_id = $course_id AND attendance_id = '$attendance_id'";
  1067. $rs = Database::query($sql);
  1068. $row = Database::fetch_row($rs);
  1069. $count = $row[0];
  1070. return $count;
  1071. }
  1072. /**
  1073. * Get count dates inside attendance calendar by attendance id
  1074. * @param int $attendance_id
  1075. * @return int count of dates
  1076. */
  1077. public static function get_count_dates_inside_attendance_calendar($attendance_id)
  1078. {
  1079. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1080. $attendance_id = intval($attendance_id);
  1081. $course_id = api_get_course_int_id();
  1082. $sql = "SELECT count(id) FROM $tbl_attendance_calendar
  1083. WHERE
  1084. c_id = $course_id AND
  1085. attendance_id = '$attendance_id'";
  1086. $rs = Database::query($sql);
  1087. $count = 0;
  1088. if (Database::num_rows($rs) > 0) {
  1089. $row = Database::fetch_row($rs);
  1090. $count = $row[0];
  1091. }
  1092. return $count;
  1093. }
  1094. /**
  1095. * check if all calendar of an attendance is done
  1096. * @param int $attendance_id
  1097. * @return bool True if all calendar is done, otherwise false
  1098. */
  1099. public static function is_all_attendance_calendar_done($attendance_id)
  1100. {
  1101. $attendance_id = intval($attendance_id);
  1102. $done_calendar = self::get_done_attendance_calendar($attendance_id);
  1103. $count_dates_in_calendar = self::get_count_dates_inside_attendance_calendar($attendance_id);
  1104. $number_of_dates = self::get_number_of_attendance_calendar($attendance_id);
  1105. $result = false;
  1106. if ($number_of_dates && (intval($count_dates_in_calendar) == intval($done_calendar))) {
  1107. $result = true;
  1108. }
  1109. return $result;
  1110. }
  1111. /**
  1112. * check if an attendance is locked
  1113. * @param int $attendance_id
  1114. * @param bool
  1115. */
  1116. public static function is_locked_attendance($attendance_id)
  1117. {
  1118. //use gradebook lock
  1119. $result = api_resource_is_locked_by_gradebook($attendance_id, LINK_ATTENDANCE);
  1120. return $result;
  1121. }
  1122. /**
  1123. * Add new datetime inside attendance calendar table
  1124. * @param int attendance id
  1125. * @return int affected rows
  1126. */
  1127. public function attendance_calendar_add($attendance_id)
  1128. {
  1129. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1130. $affected_rows = 0;
  1131. $attendance_id = intval($attendance_id);
  1132. $course_id = api_get_course_int_id();
  1133. // check if datetime already exists inside the table
  1134. $sql = "SELECT id FROM $tbl_attendance_calendar
  1135. WHERE
  1136. c_id = $course_id AND
  1137. date_time='".Database::escape_string($this->date_time)."' AND
  1138. attendance_id = '$attendance_id'";
  1139. $rs = Database::query($sql);
  1140. if (Database::num_rows($rs) == 0) {
  1141. $sql = "INSERT INTO $tbl_attendance_calendar SET
  1142. c_id = $course_id,
  1143. date_time = '".Database::escape_string($this->date_time)."',
  1144. attendance_id = '$attendance_id'";
  1145. Database::query($sql);
  1146. $affected_rows = Database::affected_rows();
  1147. }
  1148. // update locked attendance
  1149. $is_all_calendar_done = self::is_all_attendance_calendar_done($attendance_id);
  1150. if (!$is_all_calendar_done) {
  1151. self::lock_attendance($attendance_id, false);
  1152. } else {
  1153. self::lock_attendance($attendance_id);
  1154. }
  1155. return $affected_rows;
  1156. }
  1157. /**
  1158. * save repeated date inside attendance calendar table
  1159. * @param int $attendance_id
  1160. * @param int $start_date start date in tms
  1161. * @param int $end_date end date in tms
  1162. * @param string $repeat_type daily, weekly, monthlyByDate
  1163. */
  1164. public function attendance_repeat_calendar_add($attendance_id, $start_date, $end_date, $repeat_type)
  1165. {
  1166. $attendance_id = intval($attendance_id);
  1167. // save start date
  1168. $datetimezone = api_get_utc_datetime($start_date);
  1169. $this->set_date_time($datetimezone);
  1170. $res = $this->attendance_calendar_add($attendance_id);
  1171. //86400 = 24 hours in seconds
  1172. //604800 = 1 week in seconds
  1173. // Saves repeated dates
  1174. switch($repeat_type) {
  1175. case 'daily':
  1176. $j = 1;
  1177. for ($i = $start_date + 86400; ($i <= $end_date); $i += 86400) {
  1178. $datetimezone = api_get_utc_datetime($i);
  1179. $this->set_date_time($datetimezone);
  1180. $this->attendance_calendar_add($attendance_id);
  1181. $j++;
  1182. }
  1183. break;
  1184. case 'weekly':
  1185. $j = 1;
  1186. for ($i = $start_date + 604800; ($i <= $end_date); $i += 604800) {
  1187. $datetimezone = api_get_utc_datetime($i);
  1188. $this->set_date_time($datetimezone);
  1189. $this->attendance_calendar_add($attendance_id);
  1190. $j++;
  1191. }
  1192. break;
  1193. case 'monthlyByDate':
  1194. $j = 1;
  1195. //@todo fix bug with february
  1196. for ($i = $start_date + 2419200; ($i <= $end_date); $i += 2419200) {
  1197. $datetimezone = api_get_utc_datetime($i);
  1198. $this->set_date_time($datetimezone);
  1199. $this->attendance_calendar_add($attendance_id);
  1200. $j++;
  1201. }
  1202. break;
  1203. }
  1204. }
  1205. /**
  1206. * Adds x months to a UNIX timestamp
  1207. * @param int The timestamp
  1208. * @param int The number of years to add
  1209. * @return int The new timestamp
  1210. */
  1211. private function add_month($timestamp, $num=1)
  1212. {
  1213. $values = api_get_utc_datetime($timestamp);
  1214. $values = str_replace(array(':','-',' '), '/', $values);
  1215. list($y, $m, $d, $h, $n, $s) = split('/',$values);
  1216. if($m+$num>12) {
  1217. $y += floor($num/12);
  1218. $m += $num%12;
  1219. } else {
  1220. $m += $num;
  1221. }
  1222. //date_default_timezone_set('UTC');
  1223. // return mktime($h, $n, $s, $m, $d, $y);
  1224. $result = api_strtotime($y.'-'.$m.'-'.$d.' '.$h.':'.$n.':'.$s, 'UTC');
  1225. if (!empty($result)) {
  1226. return $result;
  1227. }
  1228. return false;
  1229. }
  1230. /**
  1231. * edit a datetime inside attendance calendar table
  1232. * @param int attendance calendar id
  1233. * @param int attendance id
  1234. * @return int affected rows
  1235. */
  1236. public function attendance_calendar_edit($calendar_id, $attendance_id)
  1237. {
  1238. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1239. $affected_rows = 0;
  1240. $attendance_id = intval($attendance_id);
  1241. $course_id = api_get_course_int_id();
  1242. // check if datetime already exists inside the table
  1243. $sql = "SELECT id FROM $tbl_attendance_calendar
  1244. WHERE
  1245. c_id = $course_id AND
  1246. date_time = '".Database::escape_string($this->date_time)."' AND
  1247. attendance_id = '$attendance_id'";
  1248. $rs = Database::query($sql);
  1249. if (Database::num_rows($rs) == 0) {
  1250. $sql = "UPDATE $tbl_attendance_calendar
  1251. SET date_time='".Database::escape_string($this->date_time)."'
  1252. WHERE c_id = $course_id AND id = '".intval($calendar_id)."'";
  1253. Database::query($sql);
  1254. $affected_rows = Database::affected_rows();
  1255. }
  1256. // update locked attendance
  1257. $is_all_calendar_done = self::is_all_attendance_calendar_done($attendance_id);
  1258. if (!$is_all_calendar_done) {
  1259. self::lock_attendance($attendance_id, false);
  1260. } else {
  1261. self::lock_attendance($attendance_id);
  1262. }
  1263. return $affected_rows;
  1264. }
  1265. /**
  1266. * delete a datetime from attendance calendar table
  1267. * @param int attendance calendar id
  1268. * @param int attendance id
  1269. * @param bool true for removing all calendar inside current attendance, false for removing by calendar id
  1270. * @return int affected rows
  1271. */
  1272. public function attendance_calendar_delete($calendar_id, $attendance_id , $all_delete = false)
  1273. {
  1274. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1275. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  1276. $attendance_id = intval($attendance_id);
  1277. // get all registered users inside current course
  1278. $users = $this->get_users_rel_course();
  1279. $user_ids = array_keys($users);
  1280. $course_id = api_get_course_int_id();
  1281. if ($all_delete) {
  1282. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  1283. // get all dates from calendar by current attendance
  1284. if (!empty($attendance_calendar)) {
  1285. foreach ($attendance_calendar as $cal) {
  1286. // delete all data from attendance sheet
  1287. $sql = "DELETE FROM $tbl_attendance_sheet
  1288. WHERE c_id = $course_id AND attendance_calendar_id = '".intval($cal['id'])."'";
  1289. Database::query($sql);
  1290. // delete data from attendance calendar
  1291. $sql = "DELETE FROM $tbl_attendance_calendar
  1292. WHERE c_id = $course_id AND id = '".intval($cal['id'])."'";
  1293. Database::query($sql);
  1294. }
  1295. }
  1296. } else {
  1297. // delete just one row from attendance sheet by the calendar id
  1298. $sql = "DELETE FROM $tbl_attendance_sheet
  1299. WHERE c_id = $course_id AND attendance_calendar_id = '".intval($calendar_id)."'";
  1300. Database::query($sql);
  1301. // delete data from attendance calendar
  1302. $sql = "DELETE FROM $tbl_attendance_calendar
  1303. WHERE c_id = $course_id AND id = '".intval($calendar_id)."'";
  1304. Database::query($sql);
  1305. }
  1306. $affected_rows = Database::affected_rows();
  1307. // update users' results
  1308. $this->update_users_results($user_ids, $attendance_id);
  1309. return $affected_rows;
  1310. }
  1311. /**
  1312. * buid a string datetime from array
  1313. * @param array array containing data e.g:
  1314. * $array('Y'=>'2010', 'F' => '02', 'd' => '10', 'H' => '12', 'i' => '30')
  1315. * @return string date and time e.g: '2010-02-10 12:30:00'
  1316. */
  1317. public function build_datetime_from_array($array)
  1318. {
  1319. return return_datetime_from_array($array);
  1320. }
  1321. /** Setters for fields of attendances tables **/
  1322. public function set_session_id($session_id)
  1323. {
  1324. $this->session_id = $session_id;
  1325. }
  1326. public function set_course_id($course_id)
  1327. {
  1328. $this->course_id = $course_id;
  1329. }
  1330. public function set_date_time($datetime)
  1331. {
  1332. $this->date_time = $datetime;
  1333. }
  1334. public function set_name($name)
  1335. {
  1336. $this->name = $name;
  1337. }
  1338. public function set_description($description)
  1339. {
  1340. $this->description = $description;
  1341. }
  1342. public function set_attendance_qualify_title($attendance_qualify_title)
  1343. {
  1344. $this->attendance_qualify_title = $attendance_qualify_title;
  1345. }
  1346. public function set_attendance_weight($attendance_weight)
  1347. {
  1348. $this->attendance_weight = $attendance_weight;
  1349. }
  1350. /** Getters for fields of attendances tables **/
  1351. public function get_session_id()
  1352. {
  1353. return $this->session_id;
  1354. }
  1355. public function get_course_id()
  1356. {
  1357. return $this->course_id;
  1358. }
  1359. public function get_date_time()
  1360. {
  1361. return $this->date_time;
  1362. }
  1363. public function get_name()
  1364. {
  1365. return $this->name;
  1366. }
  1367. public function get_description()
  1368. {
  1369. return $this->description;
  1370. }
  1371. public function get_attendance_qualify_title()
  1372. {
  1373. return $this->attendance_qualify_title;
  1374. }
  1375. public function get_attendance_weight()
  1376. {
  1377. return $this->attendance_weight;
  1378. }
  1379. /**
  1380. * @param string $startDate in UTC time
  1381. * @param string $endDate in UTC time
  1382. *
  1383. * @return array
  1384. */
  1385. public function getAttendanceLogin($startDate, $endDate)
  1386. {
  1387. if (empty($startDate) || $startDate == '0000-00-00' ||
  1388. empty($endDate) || $endDate == '0000-00-00'
  1389. ) {
  1390. return false;
  1391. }
  1392. $sessionId = api_get_session_id();
  1393. $courseCode = api_get_course_id();
  1394. if (!empty($sessionId)) {
  1395. $users = CourseManager:: get_user_list_from_course_code(
  1396. $courseCode,
  1397. $sessionId,
  1398. '',
  1399. 'lastname',
  1400. 0
  1401. );
  1402. } else {
  1403. $users = CourseManager:: get_user_list_from_course_code(
  1404. $courseCode,
  1405. 0,
  1406. '',
  1407. 'lastname',
  1408. STUDENT
  1409. );
  1410. }
  1411. $dateTimeStartOriginal = new DateTime($startDate);
  1412. $dateTimeStart = new DateTime($startDate);
  1413. $dateTimeEnd= new DateTime($endDate);
  1414. $interval = $dateTimeStart->diff($dateTimeEnd);
  1415. $days = intval($interval->format('%a'));
  1416. $dateList = array($dateTimeStart->format('Y-m-d'));
  1417. $headers = array(
  1418. get_lang('User'),
  1419. $dateTimeStart->format('Y-m-d')
  1420. );
  1421. for ($i = 0; $i < $days; $i++) {
  1422. $dateTimeStart = $dateTimeStart->add(new DateInterval('P1D'));
  1423. $date = $dateTimeStart->format('Y-m-d');
  1424. $dateList[] = $date;
  1425. $headers[] = $date;
  1426. }
  1427. $accessData = CourseManager::getCourseAccessPerCourseAndSession(
  1428. $courseCode,
  1429. $sessionId,
  1430. $dateTimeStartOriginal->format('Y-m-d H:i:s'),
  1431. $dateTimeEnd->format('Y-m-d H:i:s')
  1432. );
  1433. $results = array();
  1434. if (!empty($accessData)) {
  1435. foreach ($accessData as $data) {
  1436. $onlyDate = substr($data['login_course_date'], 0, 10);
  1437. $results[$data['user_id']][$onlyDate] = true;
  1438. }
  1439. }
  1440. return array(
  1441. 'users' => $users,
  1442. 'dateList' => $dateList,
  1443. 'headers' => $headers,
  1444. 'results' => $results
  1445. );
  1446. }
  1447. /**
  1448. * @param string $startDate in UTC time
  1449. * @param string $endDate in UTC time
  1450. *
  1451. * @return string
  1452. */
  1453. public function getAttendanceLoginTable($startDate, $endDate)
  1454. {
  1455. $data = $this->getAttendanceLogin($startDate, $endDate);
  1456. if (!$data) {
  1457. return null;
  1458. }
  1459. $headers = $data['headers'];
  1460. $dateList = $data['dateList'];
  1461. $users = $data['users'];
  1462. $results = $data['results'];
  1463. $table = new HTML_Table(array('class' => 'data_table'));
  1464. $row = 0;
  1465. $column = 0;
  1466. foreach ($headers as $header) {
  1467. $table->setHeaderContents($row, $column, $header);
  1468. $column++;
  1469. }
  1470. $row = 1;
  1471. foreach ($users as $user) {
  1472. $table->setCellContents(
  1473. $row,
  1474. 0,
  1475. $user['lastname'].' '.$user['firstname'].' ('.$user['username'].')'
  1476. );
  1477. $row ++;
  1478. }
  1479. $column = 1;
  1480. $row = 1;
  1481. foreach ($users as $user) {
  1482. foreach ($dateList as $date) {
  1483. $status = null;
  1484. if (isset($results[$user['user_id']]) &&
  1485. isset($results[$user['user_id']][$date])
  1486. ) {
  1487. $status = 'X';
  1488. }
  1489. $table->setCellContents($row, $column, $status);
  1490. $column++;
  1491. }
  1492. $row++;
  1493. $column = 1;
  1494. }
  1495. return $table->toHtml();
  1496. }
  1497. /**
  1498. * @param string $startDate in UTC time
  1499. * @param string $endDate in UTC time
  1500. *
  1501. * @return string
  1502. */
  1503. public function exportAttendanceLogin($startDate, $endDate)
  1504. {
  1505. $data = $this->getAttendanceLogin($startDate, $endDate);
  1506. if (!$data) {
  1507. return null;
  1508. }
  1509. $users = $data['users'];
  1510. $results = $data['results'];
  1511. $table = new HTML_Table(array('class' => 'data_table'));
  1512. $table->setHeaderContents(0, 0, get_lang('User'));
  1513. $table->setHeaderContents(0, 1, get_lang('Date'));
  1514. $row = 1;
  1515. foreach ($users as $user) {
  1516. $table->setCellContents(
  1517. $row,
  1518. 0,
  1519. $user['lastname'].' '.$user['firstname'].' ('.$user['username'].')'
  1520. );
  1521. $row++;
  1522. }
  1523. $table->setColAttributes(0, array('style' => 'width:28%'));
  1524. $row = 1;
  1525. foreach ($users as $user) {
  1526. if (isset($results[$user['user_id']]) &&
  1527. !empty($results[$user['user_id']])
  1528. ) {
  1529. $dates = implode(', ', array_keys($results[$user['user_id']]));
  1530. $table->setCellContents($row, 1, $dates);
  1531. }
  1532. $row++;
  1533. }
  1534. //$tableToString = null;
  1535. //$sessionInfo = api_get_session_info(api_get_session_id());
  1536. //if (!empty($sessionInfo)) {
  1537. /*$tableToString .= '<strong>'.get_lang('PeriodToDisplay').'</strong>: '.
  1538. sprintf(get_lang('FromDateXToDateY'), $startDate, $endDate);*/
  1539. //}
  1540. $tableToString = $table->toHtml();
  1541. $params = array(
  1542. 'filename' => get_lang('Attendance') . '_' . api_get_utc_datetime(),
  1543. 'pdf_title' => get_lang('Attendance'),
  1544. 'course_code' => api_get_course_id(),
  1545. 'show_real_course_teachers' => true
  1546. );
  1547. $pdf = new PDF('A4', null, $params);
  1548. $pdf->html_to_pdf_with_template($tableToString);
  1549. }
  1550. }