attendance.lib.php 60 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380138113821383138413851386138713881389139013911392139313941395139613971398139914001401140214031404140514061407140814091410141114121413141414151416141714181419142014211422142314241425142614271428142914301431143214331434143514361437143814391440144114421443144414451446144714481449145014511452145314541455145614571458145914601461146214631464146514661467146814691470147114721473147414751476147714781479148014811482148314841485148614871488148914901491149214931494149514961497149814991500150115021503150415051506150715081509
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This file contains class used like library, provides functions for attendance tool. It's also used like model to attendance_controller (MVC pattern)
  5. * @author Christian Fasanando <christian1827@gmail.com>
  6. * @author Julio Montoya <gugli100@gmail.com> improvements
  7. * @package chamilo.attendance
  8. */
  9. /**
  10. * Attendance can be used to instanciate objects or as a library to manage attendances
  11. * @package chamilo.attendance
  12. */
  13. class Attendance
  14. {
  15. private $session_id;
  16. private $course_id;
  17. private $date_time;
  18. private $name;
  19. private $description;
  20. private $attendance_qualify_title;
  21. private $attendance_weight;
  22. private $course_int_id;
  23. public $category_id;
  24. // constants
  25. const DONE_ATTENDANCE_LOG_TYPE = 'done_attendance_sheet';
  26. const UPDATED_ATTENDANCE_LOG_TYPE = 'updated_attendance_sheet';
  27. const LOCKED_ATTENDANCE_LOG_TYPE = 'locked_attendance_sheet';
  28. public function __construct() {
  29. //$this->course_int_id = api_get_course_int_id();
  30. }
  31. /**
  32. * Get the total number of attendance inside current course and current session
  33. * @see SortableTable#get_total_number_of_items()
  34. */
  35. static function get_number_of_attendances() {
  36. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  37. $session_id = api_get_session_id();
  38. $condition_session = api_get_session_condition($session_id);
  39. $course_id = api_get_course_int_id();
  40. $sql = "SELECT COUNT(att.id) AS total_number_of_items FROM $tbl_attendance att
  41. WHERE c_id = $course_id AND att.active = 1 $condition_session ";
  42. $res = Database::query($sql);
  43. $obj = Database::fetch_object($res);
  44. return $obj->total_number_of_items;
  45. }
  46. /**
  47. * Get attendance list only the id, name and attendance_qualify_max fields
  48. * @param string course db name (optional)
  49. * @param int session id (optional)
  50. * @return array attendances list
  51. */
  52. function get_attendances_list($course_id = '', $session_id = null) {
  53. // Initializing database table and variables
  54. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  55. $data = array();
  56. //@todo user seters/gettrs
  57. if (empty($course_id)) {
  58. $course_id = api_get_course_int_id();
  59. } else {
  60. $course_id = intval($course_id);
  61. }
  62. $session_id = isset($session_id) ? intval($session_id):api_get_session_id();
  63. $condition_session = api_get_session_condition($session_id);
  64. // Get attendance data
  65. $sql = "SELECT id, name, attendance_qualify_max
  66. FROM $tbl_attendance
  67. WHERE c_id = $course_id AND active = 1 $condition_session ";
  68. $rs = Database::query($sql);
  69. if (Database::num_rows($rs) > 0) {
  70. while ($row = Database::fetch_array($rs,'ASSOC')) {
  71. $data[$row['id']] = $row;
  72. }
  73. }
  74. return $data;
  75. }
  76. /**
  77. * Get the attendaces to display on the current page (fill the sortable-table)
  78. * @param int offset of first user to recover
  79. * @param int Number of users to get
  80. * @param int Column to sort on
  81. * @param string Order (ASC,DESC)
  82. * @see SortableTable#get_table_data($from)
  83. */
  84. static function get_attendance_data($from = 0, $number_of_items = 20, $column = 1, $direction = 'desc') {
  85. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  86. $course_id = api_get_course_int_id();
  87. $session_id = api_get_session_id();
  88. $condition_session = api_get_session_condition($session_id);
  89. $column = intval($column);
  90. $from = intval($from);
  91. $number_of_items = intval($number_of_items);
  92. if (!in_array($direction, array('ASC','DESC'))) {
  93. $direction = 'ASC';
  94. }
  95. $att = new Attendance();
  96. $att->set_session_id($session_id);
  97. $att->set_course_int_id($course_id);
  98. $active_plus = 'att.active = 1';
  99. if (api_is_platform_admin()) {
  100. $active_plus = ' 1 = 1 ';
  101. }
  102. $sql = "SELECT
  103. att.id,
  104. att.name,
  105. att.description,
  106. att.attendance_qualify_max,
  107. att.locked,
  108. att.active,
  109. att.session_id
  110. FROM $tbl_attendance att
  111. WHERE c_id = $course_id AND $active_plus $condition_session
  112. ORDER BY $column $direction
  113. LIMIT $from, $number_of_items ";
  114. $res = Database::query($sql);
  115. $attendances = array();
  116. $param_gradebook = '';
  117. if (isset($_SESSION['gradebook'])) {
  118. $param_gradebook = '&gradebook='.$_SESSION['gradebook'];
  119. }
  120. $user_info = api_get_user_info();
  121. while ($attendance = Database::fetch_array($res,'ASSOC')) {
  122. $id = $attendance['id'];
  123. $student_param = '';
  124. if (api_is_drh() && ($_GET['student_id'])) {
  125. $student_param = '&student_id='.Security::remove_XSS($_GET['student_id']);
  126. }
  127. $session_star = '';
  128. if (api_get_session_id() == $attendance['session_id']) {
  129. $session_star = api_get_session_image(api_get_session_id(), $user_info['status']);
  130. }
  131. if ($attendance['active'] == 0) {
  132. $attendance['name'] = "<del>".$attendance['name']."</del>";
  133. }
  134. if ($attendance['locked'] == 1) {
  135. if (api_is_allowed_to_edit(null, true)) {
  136. //Link to edit
  137. $attendance['name'] = '<a href="index.php?'.api_get_cidreq().'&action=attendance_sheet_list&attendance_id='.$id.$param_gradebook.$student_param.'">'.$attendance['name'].'</a>'.$session_star;
  138. } else {
  139. //Link to view
  140. $attendance['name'] = '<a href="index.php?'.api_get_cidreq().'&action=attendance_sheet_list_no_edit&attendance_id='.$id.$param_gradebook.$student_param.'">'.$attendance['name'].'</a>'.$session_star;
  141. }
  142. } else {
  143. $attendance['name'] = '<a href="index.php?'.api_get_cidreq().'&action=attendance_sheet_list&attendance_id='.$id.$param_gradebook.$student_param.'">'.$attendance['name'].'</a>'.$session_star;
  144. }
  145. //description
  146. $attendance['description'] = '<center>'.$attendance['description'].'</center>';
  147. if (api_is_allowed_to_edit(null, true)) {
  148. $actions = '';
  149. $actions .= '<center>';
  150. if (api_is_platform_admin()) {
  151. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_edit&attendance_id='.$id.$param_gradebook.'">'.
  152. Display::return_icon('edit.png',get_lang('Edit'), array(), ICON_SIZE_SMALL).'</a>&nbsp;';
  153. if ($attendance['locked'] == 0) {
  154. // $actions .= '<a onclick="javascript:if(!confirm(\''.get_lang('AreYouSureToDelete').'\')) return false;" href="index.php?'.api_get_cidreq().'&action=attendance_delete&attendance_id='.$id.$param_gradebook.'">'.Display::return_icon('delete.png',get_lang('Delete'), array(), ICON_SIZE_SMALL).'</a>';
  155. }
  156. if ($attendance['active'] == 1) {
  157. $actions .= '<a onclick="javascript:if(!confirm(\''.get_lang('AreYouSureToDelete').'\')) return false;" href="index.php?'.api_get_cidreq().'&action=attendance_delete&attendance_id='.$id.$param_gradebook.'">'.Display::return_icon('delete.png',get_lang('Delete'), array(), ICON_SIZE_SMALL).'</a>';
  158. } else {
  159. $actions .= '<a onclick="javascript:if(!confirm(\''.get_lang('AreYouSureToRestore').'\')) return false;" href="index.php?'.api_get_cidreq().'&action=attendance_restore&attendance_id='.$id.$param_gradebook.'">'.Display::return_icon('invisible.png',get_lang('Restore'), array(), ICON_SIZE_SMALL).'</a>';
  160. }
  161. } else {
  162. $is_locked_attendance = self::is_locked_attendance($attendance['id']);
  163. if ($is_locked_attendance) {
  164. $actions .= Display::return_icon('edit_na.png',get_lang('Edit')).'&nbsp;';
  165. $actions .= Display::return_icon('delete_na.png',get_lang('Delete'));
  166. } else {
  167. $actions .= '<a href="index.php?'.api_get_cidreq().'&action=attendance_edit&attendance_id='.$id.$param_gradebook.'">'.Display::return_icon('edit.png',get_lang('Edit'), array(), ICON_SIZE_SMALL).'</a>&nbsp;';
  168. $actions .= '<a onclick="javascript:if(!confirm(\''.get_lang('AreYouSureToDelete').'\')) return false;" href="index.php?'.api_get_cidreq().'&action=attendance_delete&attendance_id='.$id.$param_gradebook.'">'.Display::return_icon('delete.png',get_lang('Delete'), array(), ICON_SIZE_SMALL).'</a>';
  169. }
  170. }
  171. // display lock/unlock icon
  172. $is_done_all_calendar = $att->is_all_attendance_calendar_done($id);
  173. if ($is_done_all_calendar) {
  174. $locked = $attendance['attendance_qualify_max'];
  175. if ($locked == 0) {
  176. if (api_is_platform_admin()) {
  177. $message_alert = get_lang('AreYouSureToLockTheAttendance');
  178. } else {
  179. $message_alert = get_lang('UnlockMessageInformation');
  180. }
  181. $actions .= '&nbsp;<a onclick="javascript:if(!confirm(\''.$message_alert.'\')) return false;" href="index.php?'.api_get_cidreq().'&action=lock_attendance&attendance_id='.$id.$param_gradebook.'">'.
  182. Display::return_icon('unlock.png', get_lang('LockAttendance')).'</a>';
  183. } else {
  184. if (api_is_platform_admin()) {
  185. $actions .= '&nbsp;<a onclick="javascript:if(!confirm(\''.get_lang('AreYouSureToUnlockTheAttendance').'\')) return false;" href="index.php?'.api_get_cidreq().'&action=unlock_attendance&attendance_id='.$id.$param_gradebook.'">'.
  186. Display::return_icon('lock.png', get_lang('UnlockAttendance')).'</a>';
  187. } else {
  188. $actions .= '&nbsp;'.Display::return_icon('locked_na.png',get_lang('LockedAttendance'));
  189. }
  190. }
  191. }
  192. $actions .= '</center>';
  193. $attendance['actions'] = $actions;
  194. $attendances[] = $attendance;
  195. } else {
  196. $attendances[] = $attendance;
  197. }
  198. }
  199. return $attendances;
  200. }
  201. /**
  202. * Get the attendaces by id to display on the current page
  203. * @param int attendance id
  204. * @return array attendance data
  205. */
  206. public function get_attendance_by_id($attendance_id) {
  207. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  208. $attendance_id = intval($attendance_id);
  209. $course_id = $this->get_course_int_id();
  210. $attendance_data = array();
  211. $sql = "SELECT * FROM $tbl_attendance WHERE c_id = $course_id AND id = '$attendance_id'";
  212. $res = Database::query($sql);
  213. if (Database::num_rows($res) > 0) {
  214. while ($row = Database::fetch_array($res)) {
  215. $attendance_data = $row;
  216. }
  217. }
  218. return $attendance_data;
  219. }
  220. /**
  221. * Add attendaces sheet inside table. This is the *list of* dates, not
  222. * a specific date in itself.
  223. * @param bool true for adding link in gradebook or false otherwise (optional)
  224. * @return int last attendance id
  225. */
  226. public function attendance_add($link_to_gradebook = false, $user_id = null) {
  227. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  228. $table_link = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  229. if (empty($user_id)) {
  230. $user_id = api_get_user_id();
  231. } else {
  232. $user_id = intval($user_id);
  233. }
  234. $course_code = $this->get_course_id();
  235. $session_id = $this->get_session_id();
  236. $course_info = api_get_course_info($course_code);
  237. $course_id = $course_info['real_id'];
  238. $title_gradebook = Database::escape_string($this->attendance_qualify_title);
  239. $weight_calification = floatval($this->attendance_weight);
  240. $value_calification = 0;
  241. $sql = "INSERT INTO $tbl_attendance SET
  242. c_id = $course_id,
  243. name ='".Database::escape_string($this->name)."',
  244. description = '".Database::escape_string($this->description)."',
  245. attendance_qualify_title = '$title_gradebook',
  246. attendance_weight = '$weight_calification',
  247. session_id = '$session_id'";
  248. Database::query($sql);
  249. $affected_rows = Database::affected_rows();
  250. $last_id = 0;
  251. if (!empty($affected_rows)) {
  252. // save inside item property table
  253. $last_id = Database::insert_id();
  254. api_item_property_update($course_info, TOOL_ATTENDANCE, $last_id, "AttendanceAdded", $user_id);
  255. }
  256. // add link to gradebook
  257. if ($link_to_gradebook && !empty($this->category_id)) {
  258. $description = '';
  259. $link_info = is_resource_in_course_gradebook($course_code, 7, $last_id, $session_id);
  260. $link_id = $link_info['id'];
  261. if (!$link_info) {
  262. add_resource_to_course_gradebook($this->category_id, $course_code, 7, $last_id, $title_gradebook,$weight_calification,$value_calification,$description,1,$session_id);
  263. } else {
  264. Database::query('UPDATE '.$table_link.' SET weight='.$weight_calification.' WHERE id='.$link_id.'');
  265. }
  266. }
  267. return $last_id;
  268. }
  269. /**
  270. * edit attendaces inside table
  271. * @param int attendance id
  272. * @param bool true for adding link in gradebook or false otherwise (optional)
  273. * @return int last id
  274. */
  275. public function attendance_edit($attendance_id, $link_to_gradebook = false) {
  276. global $_course;
  277. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  278. $table_link = Database :: get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
  279. $session_id = api_get_session_id();
  280. $user_id = api_get_user_id();
  281. $attendance_id = intval($attendance_id);
  282. $course_code = $this->get_course_id();
  283. $course_id = $this->get_course_int_id();
  284. $title_gradebook = Database::escape_string($this->attendance_qualify_title);
  285. $value_calification = 0;
  286. $weight_calification= floatval($this->attendance_weight);
  287. if (!empty($attendance_id)) {
  288. $sql = "UPDATE $tbl_attendance
  289. SET name ='".Database::escape_string($this->name)."',
  290. description = '".Database::escape_string($this->description)."',
  291. attendance_qualify_title = '".$title_gradebook."',
  292. attendance_weight = '".$weight_calification."'
  293. WHERE c_id = $course_id AND id = '$attendance_id'";
  294. Database::query($sql);
  295. api_item_property_update($_course, TOOL_ATTENDANCE, $attendance_id,"AttendanceUpdated", $user_id);
  296. // add link to gradebook
  297. if ($link_to_gradebook && !empty($this->category_id)) {
  298. $description = '';
  299. $link_id = is_resource_in_course_gradebook($course_code, 7, $attendance_id, $session_id);
  300. if (!$link_id) {
  301. add_resource_to_course_gradebook($this->category_id, $course_code, 7, $attendance_id, $title_gradebook,$weight_calification,$value_calification,$description,1,$session_id);
  302. } else {
  303. Database::query('UPDATE '.$table_link.' SET weight='.$weight_calification.' WHERE id='.$link_id.'');
  304. }
  305. }
  306. return $attendance_id;
  307. }
  308. return null;
  309. }
  310. /**
  311. * Restore attendance
  312. * @param int|array one or many attendances id
  313. * @return int affected rows
  314. */
  315. public function attendance_restore($attendance_id) {
  316. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  317. $user_id = api_get_user_id();
  318. $course_id = $this->get_course_int_id();
  319. $course_info = api_get_course_info_by_id($course_id);
  320. if (is_array($attendance_id)) {
  321. foreach ($attendance_id as $id) {
  322. $id = intval($id);
  323. $sql = "UPDATE $tbl_attendance SET active = 1 WHERE c_id = $course_id AND id = '$id'";
  324. Database::query($sql);
  325. $affected_rows = Database::affected_rows();
  326. if (!empty($affected_rows)) {
  327. // update row item property table
  328. api_item_property_update($course_info, TOOL_ATTENDANCE, $id,"restore", $user_id);
  329. }
  330. }
  331. } else {
  332. $attendance_id = intval($attendance_id);
  333. $sql = "UPDATE $tbl_attendance SET active = 1 WHERE c_id = $course_id AND id = '$attendance_id'";
  334. Database::query($sql);
  335. $affected_rows = Database::affected_rows();
  336. if (!empty($affected_rows)) {
  337. // update row item property table
  338. api_item_property_update($course_info, TOOL_ATTENDANCE, $attendance_id,"restore", $user_id);
  339. }
  340. }
  341. return $affected_rows;
  342. }
  343. /**
  344. * delete attendaces
  345. * @param int|array one or many attendances id
  346. * @return int affected rows
  347. */
  348. public function attendance_delete($attendance_id) {
  349. $tbl_attendance = Database :: get_course_table(TABLE_ATTENDANCE);
  350. $user_id = api_get_user_id();
  351. $course_id = $this->get_course_int_id();
  352. $course_info = api_get_course_info_by_id($course_id);
  353. if (is_array($attendance_id)) {
  354. foreach ($attendance_id as $id) {
  355. $id = intval($id);
  356. $sql = "UPDATE $tbl_attendance SET active = 0 WHERE c_id = $course_id AND id = '$id'";
  357. Database::query($sql);
  358. $affected_rows = Database::affected_rows();
  359. if (!empty($affected_rows)) {
  360. // update row item property table
  361. api_item_property_update($course_info, TOOL_ATTENDANCE, $id,"delete", $user_id);
  362. }
  363. }
  364. } else {
  365. $attendance_id = intval($attendance_id);
  366. $sql = "UPDATE $tbl_attendance SET active = 0 WHERE c_id = $course_id AND id = '$attendance_id'";
  367. Database::query($sql);
  368. $affected_rows = Database::affected_rows();
  369. if (!empty($affected_rows)) {
  370. // update row item property table
  371. api_item_property_update($course_info, TOOL_ATTENDANCE, $attendance_id,"delete", $user_id);
  372. }
  373. }
  374. return $affected_rows;
  375. }
  376. /**
  377. * Lock or unlock an attendance
  378. * @param int attendance id
  379. * @param bool True to lock or false otherwise
  380. */
  381. public function lock_attendance($attendance_id, $lock = true) {
  382. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  383. $course_id = $this->get_course_int_id();
  384. $attendance_id = intval($attendance_id);
  385. $locked = ($lock)?1:0;
  386. $upd = "UPDATE $tbl_attendance SET locked = $locked WHERE c_id = $course_id AND id = $attendance_id";
  387. Database::query($upd);
  388. $affected_rows = Database::affected_rows();
  389. if ($affected_rows && $lock) {
  390. //save attendance sheet log
  391. $lastedit_type = self::LOCKED_ATTENDANCE_LOG_TYPE;
  392. $lastedit_user_id = api_get_user_id();
  393. $this->save_attendance_sheet_log($attendance_id, api_get_utc_datetime(), $lastedit_type, $lastedit_user_id);
  394. }
  395. return $affected_rows;
  396. }
  397. /**
  398. * Get registered users inside current course
  399. * @param int attendance id for showing attendance result field (optional)
  400. * @return array users data
  401. */
  402. public function get_users_rel_course($attendance_id = 0) {
  403. $current_course_id = $this->get_course_id();
  404. $current_session_id = $this->get_session_id();
  405. if (!empty($current_session_id)) {
  406. $a_course_users = CourseManager :: get_user_list_from_course_code($current_course_id, $current_session_id,'', 'lastname');
  407. } else {
  408. $a_course_users = CourseManager :: get_user_list_from_course_code($current_course_id, 0, '','lastname');
  409. }
  410. // get registered users inside current course
  411. $a_users = array();
  412. foreach ($a_course_users as $key =>$user_data) {
  413. $value = array();
  414. $uid = $user_data['user_id'];
  415. $status = $user_data['status'];
  416. $user_status_in_session = null;
  417. $user_status_in_course = null;
  418. if ($current_session_id) {
  419. $user_status_in_session = SessionManager::get_user_status_in_course_session($uid, $current_course_id, $current_session_id);
  420. } else {
  421. $user_status_in_course = CourseManager::get_user_in_course_status($uid, $current_course_id);
  422. }
  423. //Not taking into account DRH or COURSEMANAGER
  424. if ($uid <= 1 || $status == DRH || $user_status_in_course == COURSEMANAGER || $user_status_in_session == 2) continue;
  425. if (!empty($attendance_id)) {
  426. $user_faults = $this->get_faults_of_user($uid, $attendance_id);
  427. $value['attendance_result'] = $user_faults['faults'].'/'.$user_faults['total'].' ('.$user_faults['faults_porcent'].'%)';
  428. $value['result_color_bar'] = $user_faults['color_bar'];
  429. }
  430. // user's picture
  431. $image_path = UserManager::get_user_picture_path_by_id($uid, 'web', false);
  432. $user_profile = UserManager::get_picture_user($uid, $image_path['file'], 22, USER_IMAGE_SIZE_SMALL, ' width="22" height="22" ');
  433. if (!empty($image_path['file'])) {
  434. $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>';
  435. } else {
  436. $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>';
  437. }
  438. $value['photo'] = $photo;
  439. $value['firstname'] = $user_data['firstname'];
  440. $value['lastname'] = $user_data['lastname'];
  441. $value['username'] = $user_data['username'];
  442. $value['user_id'] = $uid;
  443. //Sending only 5 items in the array instead of 60
  444. $a_users[$key] = $value;
  445. }
  446. return $a_users;
  447. }
  448. public function attendance_sheet_disable($calendar_id, $user_id) {
  449. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  450. $course_id = $this->get_course_int_id();
  451. $user_id = intval($user_id);
  452. $calendar_id = intval($calendar_id);
  453. $sql = "UPDATE $tbl_attendance_sheet SET presence = ''
  454. WHERE c_id = $course_id AND user_id ='$user_id' AND attendance_calendar_id = '$calendar_id'";
  455. Database::query($sql);
  456. return true;
  457. }
  458. public function attendance_sheet_get_info($calendar_id, $user_id) {
  459. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  460. $course_id = $this->get_course_int_id();
  461. $user_id = intval($user_id);
  462. $calendar_id = intval($calendar_id);
  463. $sql = "SELECT * FROM $tbl_attendance_sheet
  464. WHERE c_id = $course_id AND user_id = '$user_id' AND attendance_calendar_id = '$calendar_id'";
  465. $result = Database::query($sql);
  466. if (Database::num_rows($result)) {
  467. return Database::store_result($result, 'ASSOC');
  468. }
  469. return false;
  470. }
  471. /**
  472. * Add attendaces sheet inside table
  473. * @param int attendance calendar id
  474. * @param array present users during current class (array with user_ids)
  475. * @param int attendance id
  476. * @return int affected rows
  477. */
  478. public function attendance_sheet_add($calendar_id, $user_results, $attendance_id, $save_user_absents = true, $done_attendance = true) {
  479. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  480. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  481. $calendar_id = intval($calendar_id);
  482. $attendance_id = intval($attendance_id);
  483. $users = $this->get_users_rel_course();
  484. $course_id = $this->get_course_int_id();
  485. $user_ids = array_keys($users);
  486. $users_absent = array_diff($user_ids, $user_results);
  487. $affected_rows = 0;
  488. // get last edit type
  489. $calendar_data = $this->get_attendance_calendar_by_id($calendar_id);
  490. $lastedit_type = self::DONE_ATTENDANCE_LOG_TYPE;
  491. if (isset($calendar_data['done_attendance']) && $calendar_data['done_attendance']) {
  492. $lastedit_type = self::UPDATED_ATTENDANCE_LOG_TYPE;
  493. }
  494. if (!empty($user_results)) {
  495. // Save users present in class
  496. foreach ($user_results as $uid => $presence) {
  497. $presence = intval($presence);
  498. // check if user already was registered with the $calendar_id
  499. $sql = "SELECT user_id FROM $tbl_attendance_sheet WHERE c_id = $course_id AND user_id = '$uid' AND attendance_calendar_id = '$calendar_id'";
  500. $rs = Database::query($sql);
  501. if (Database::num_rows($rs) == 0) {
  502. $sql = "INSERT INTO $tbl_attendance_sheet SET
  503. c_id = $course_id,
  504. user_id = '$uid',
  505. attendance_calendar_id = '$calendar_id',
  506. presence = $presence";
  507. Database::query($sql);
  508. $affected_rows += Database::affected_rows();
  509. } else {
  510. $sql = "UPDATE $tbl_attendance_sheet SET presence = $presence
  511. WHERE c_id = $course_id AND user_id ='$uid' AND attendance_calendar_id = '$calendar_id'";
  512. Database::query($sql);
  513. $affected_rows += Database::affected_rows();
  514. }
  515. }
  516. }
  517. /*
  518. // save users absent in class
  519. if ($save_user_absents) {
  520. foreach ($users_absent as $user_absent) {
  521. $uid = intval($user_absent);
  522. // check if user already was registered with the $calendar_id
  523. $sql = "SELECT user_id FROM $tbl_attendance_sheet WHERE c_id = $course_id AND user_id='$uid' AND attendance_calendar_id = '$calendar_id'";
  524. $rs = Database::query($sql);
  525. if (Database::num_rows($rs) == 0) {
  526. $sql = "INSERT INTO $tbl_attendance_sheet SET
  527. c_id = $course_id,
  528. user_id ='$uid',
  529. attendance_calendar_id = '$calendar_id',
  530. presence = 0";
  531. Database::query($sql);
  532. $affected_rows += Database::affected_rows();
  533. } else {
  534. $sql = "UPDATE $tbl_attendance_sheet SET presence = 0 WHERE c_id = $course_id AND user_id ='$uid' AND attendance_calendar_id = '$calendar_id'";
  535. Database::query($sql);
  536. $affected_rows += Database::affected_rows();
  537. }
  538. }
  539. }*/
  540. if ($done_attendance) {
  541. // update done_attendance inside attendance calendar table
  542. $sql = "UPDATE $tbl_attendance_calendar SET done_attendance = 1 WHERE c_id = $course_id AND id = '$calendar_id'";
  543. Database::query($sql);
  544. }
  545. // Save user's results
  546. $this->update_users_results($user_ids, $attendance_id);
  547. if ($affected_rows) {
  548. //save attendance sheet log
  549. $lastedit_user_id = api_get_user_id();
  550. $calendar_date_value = $calendar_data['date_time'];
  551. $this->save_attendance_sheet_log($attendance_id, api_get_utc_datetime(), $lastedit_type, $lastedit_user_id, $calendar_date_value);
  552. }
  553. return $affected_rows;
  554. }
  555. /**
  556. * Unstable version! Needs review! Use only for speed boosts!
  557. * Add attendaces sheet inside table, by group to reduce db stress
  558. * @param array Array of calendar_id, user_id, status, attendance_id, course_id, date, all assumed pre-filtered
  559. * @param bool Whether to save absent users
  560. * @param bool Whether to set done_attendance to 1 in db
  561. * @return int affected rows
  562. */
  563. public function attendance_sheet_group_add($list, $save_user_absents = true, $done_attendance = true) {
  564. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  565. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  566. $user_ids = array();
  567. $date_now = api_get_utc_datetime();
  568. //$calendar_date_value = $calendar_data['date_time'];
  569. $lastedit_user_id = api_get_user_id();
  570. $lastedit_type = self::DONE_ATTENDANCE_LOG_TYPE;
  571. $sql = "INSERT INTO $tbl_attendance_sheet (c_id, user_id, attendance_calendar_id, presence) VALUES ";
  572. foreach ($list as $row) {
  573. if ($row[0] != null) { //ignore placeholders
  574. $sql .= "(".intval($row[4]).','.intval($row[1]).','.intval($row[0]).','.$row[2].'),';
  575. $user_ids[] = intval($row[1]);
  576. }
  577. if ($done_attendance) {
  578. // update done_attendance inside attendance calendar table
  579. $sql2 = "UPDATE $tbl_attendance_calendar SET done_attendance = 1 WHERE c_id = ".intval($row[4])." AND id = '".intval($row[0])."'";
  580. $r2 = Database::query($sql2);
  581. }
  582. // Save user's results
  583. $this->update_users_results(array(intval($row[1])), $attendance_id);
  584. //save attendance sheet log
  585. $this->save_attendance_sheet_log(intval($row[3]), $date_now, $lastedit_type, $lastedit_user_id, $row[5]);
  586. }
  587. $sql = substr($sql,0,-1);
  588. //error_log($sql);
  589. $r = Database::query($sql);
  590. $users_absent = array_diff($user_ids, $user_results);
  591. $affected_rows = Database::affected_rows($r);
  592. return $affected_rows;
  593. }
  594. /**
  595. * update users' attendance results
  596. * @param array registered users inside current course
  597. * @param int attendance id
  598. * @return void
  599. */
  600. public function update_users_results($user_ids, $attendance_id) {
  601. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  602. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  603. $tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
  604. $course_id = $this->get_course_int_id();
  605. $attendance_id = intval($attendance_id);
  606. // fill results about presence of students
  607. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  608. $calendar_ids = array();
  609. // get all dates from calendar by current attendance
  610. foreach ($attendance_calendar as $cal) {
  611. $calendar_ids[] = $cal['id'];
  612. }
  613. //See BT#5419
  614. $status_that_score_point_to_users = self::get_status_that_score_point_to_users();
  615. $status_that_score_point_to_users = "'".implode("','", $status_that_score_point_to_users)."'";
  616. // Get count of presences by users inside current attendance and save like results
  617. $count_presences = 0;
  618. if (count($user_ids) > 0) {
  619. foreach ($user_ids as $uid) {
  620. $count_presences = 0;
  621. if (count($calendar_ids) > 0) {
  622. $sql = "SELECT count(presence) as count_presences
  623. FROM $tbl_attendance_sheet
  624. WHERE c_id = $course_id AND
  625. user_id = '$uid' AND
  626. attendance_calendar_id IN (".implode(',', $calendar_ids).") AND
  627. presence IN ($status_that_score_point_to_users)";
  628. $rs_count = Database::query($sql);
  629. $row_count = Database::fetch_array($rs_count);
  630. $count_presences = $row_count['count_presences'];
  631. }
  632. // Save results
  633. $sql = "SELECT id FROM $tbl_attendance_result WHERE c_id = $course_id AND user_id = '$uid' AND attendance_id='$attendance_id'";
  634. $rs_check_result = Database::query($sql);
  635. if (Database::num_rows($rs_check_result) > 0) {
  636. // update result
  637. $sql = "UPDATE $tbl_attendance_result SET
  638. score = '$count_presences'
  639. WHERE c_id = $course_id AND user_id='$uid' AND attendance_id='$attendance_id'";
  640. Database::query($sql);
  641. } else {
  642. // insert new result
  643. $sql = "INSERT INTO $tbl_attendance_result SET
  644. c_id = $course_id ,
  645. user_id = '$uid',
  646. attendance_id = '$attendance_id',
  647. score = '$count_presences'";
  648. Database::query($sql);
  649. }
  650. }
  651. }
  652. // update attendance qualify max
  653. $count_done_calendar = $this->get_done_attendance_calendar($attendance_id);
  654. $sql = "UPDATE $tbl_attendance SET attendance_qualify_max='$count_done_calendar' WHERE c_id = $course_id AND id = '$attendance_id'";
  655. Database::query($sql);
  656. }
  657. /**
  658. * update attendance_sheet_log table, is used as history of an attendance sheet
  659. * @param int Attendance id
  660. * @param string Last edit datetime
  661. * @param string Event type ('locked_attendance', 'done_attendance_sheet' ...)
  662. * @param int Last edit user id
  663. * @param string Calendar datetime value (optional, when event type is 'done_attendance_sheet')
  664. * @return int Affected rows
  665. */
  666. public function save_attendance_sheet_log($attendance_id, $lastedit_date, $lastedit_type, $lastedit_user_id, $calendar_date_value = null) {
  667. $course_id = $this->get_course_int_id();
  668. // define table
  669. $tbl_attendance_sheet_log = Database::get_course_table(TABLE_ATTENDANCE_SHEET_LOG);
  670. // protect data
  671. $attendance_id = intval($attendance_id);
  672. $lastedit_date = Database::escape_string($lastedit_date);
  673. $lastedit_type = Database::escape_string($lastedit_type);
  674. $lastedit_user_id = intval($lastedit_user_id);
  675. if (isset($calendar_date_value)) {
  676. $calendar_date_value = Database::escape_string($calendar_date_value);
  677. } else {
  678. $calendar_date_value = '';
  679. }
  680. // save data
  681. $ins = "INSERT INTO $tbl_attendance_sheet_log(c_id, attendance_id, lastedit_date, lastedit_type, lastedit_user_id, calendar_date_value)
  682. VALUES ($course_id, $attendance_id, '$lastedit_date', '$lastedit_type', $lastedit_user_id, '$calendar_date_value')";
  683. Database::query($ins);
  684. return Database::affected_rows();
  685. }
  686. /**
  687. * Get number of done attendances inside current sheet
  688. * @param int attendance id
  689. * @return int number of done attendances
  690. */
  691. public function get_done_attendance_calendar($attendance_id) {
  692. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  693. $attendance_id = intval($attendance_id);
  694. $course_id = $this->get_course_int_id();
  695. $sql = "SELECT count(done_attendance) as count
  696. FROM $tbl_attendance_calendar
  697. WHERE c_id = $course_id AND
  698. attendance_id = '$attendance_id' AND
  699. done_attendance = 1";
  700. $rs = Database::query($sql);
  701. $row = Database::fetch_array($rs);
  702. $count = $row['count'];
  703. return $count;
  704. }
  705. /**
  706. * Get results of faults (absents) by user
  707. * @param int user id
  708. * @param int attendance id
  709. * @return array results containing number of faults, total done attendance, porcent of faults and color depend on result (red, orange)
  710. */
  711. public function get_faults_of_user($user_id, $attendance_id) {
  712. $user_id = intval($user_id);
  713. $attendance_id = intval($attendance_id);
  714. $results = array();
  715. $attendance_data = $this->get_attendance_by_id($attendance_id);
  716. $calendar_count = $this->get_number_of_attendance_calendar($attendance_id);
  717. $total_done_attendance = $attendance_data['attendance_qualify_max'];
  718. $attendance_user_score = $this->get_user_score($user_id, $attendance_id);
  719. //This is the main change of the BT#1381
  720. //$total_done_attendance = $calendar_count;
  721. // calculate results
  722. $faults = $total_done_attendance - $attendance_user_score;
  723. $faults = $faults > 0 ? $faults:0;
  724. $faults_porcent = $calendar_count > 0 ?round(($faults*100)/$calendar_count,0):0;
  725. $results['faults'] = $faults;
  726. $results['total'] = $calendar_count;
  727. $results['faults_porcent'] = $faults_porcent;
  728. $color_bar = 'success';
  729. if ($faults_porcent > 25 ) {
  730. $color_bar = 'important';
  731. } elseif ($faults_porcent > 10) {
  732. $color_bar = 'warning';
  733. } elseif ($faults_porcent > 1) {
  734. $color_bar = 'info';
  735. }
  736. $results['color_bar'] = $color_bar;
  737. return $results;
  738. }
  739. /**
  740. * Get results of faults average for all courses by user
  741. * @param int user id
  742. * @return array results containing number of faults, total done attendance, porcent of faults and color depend on result (red, orange)
  743. */
  744. public function get_faults_average_inside_courses($user_id) {
  745. // get all courses of current user
  746. $courses = CourseManager::get_courses_list_by_user_id($user_id, true);
  747. $user_id = intval($user_id);
  748. $results = array();
  749. $total_faults = $total_weight = $porcent = 0;
  750. foreach ($courses as $course) {
  751. //$course_code = $course['code'];
  752. //$course_info = api_get_course_info($course_code);
  753. $course_id = $course['real_id'];
  754. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  755. $attendances_by_course = $this->get_attendances_list($course_id);
  756. foreach ($attendances_by_course as $attendance) {
  757. // get total faults and total weight
  758. $total_done_attendance = $attendance['attendance_qualify_max'];
  759. $sql = "SELECT score FROM $tbl_attendance_result
  760. WHERE c_id = $course_id AND user_id = $user_id AND attendance_id = ".$attendance['id'];
  761. $rs = Database::query($sql);
  762. $score = 0;
  763. if (Database::num_rows($rs) > 0) {
  764. $row = Database::fetch_array($rs);
  765. $score = $row['score'];
  766. }
  767. $faults = $total_done_attendance-$score;
  768. $faults = $faults > 0 ? $faults:0;
  769. $total_faults += $faults;
  770. $total_weight += $total_done_attendance;
  771. }
  772. }
  773. $porcent = $total_weight > 0 ?round(($total_faults*100)/$total_weight,0):0;
  774. $results['faults'] = $total_faults;
  775. $results['total'] = $total_weight;
  776. $results['porcent'] = $porcent;
  777. return $results;
  778. }
  779. /**
  780. * Get results of faults average by course
  781. * @param int user id
  782. * @param int Session id (optional)
  783. * @return array results containing number of faults, total done attendance, porcent of faults and color depend on result (red, orange)
  784. */
  785. public function get_faults_average_by_course($user_id, $course_code, $session_id = null) {
  786. // Database tables and variables
  787. $course_info = api_get_course_info($course_code);
  788. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  789. $user_id = intval($user_id);
  790. $results = array();
  791. $total_faults = $total_weight = $porcent = 0;
  792. $attendances_by_course = $this->get_attendances_list($course_info['real_id'], $session_id);
  793. foreach ($attendances_by_course as $attendance) {
  794. // Get total faults and total weight
  795. $total_done_attendance = $attendance['attendance_qualify_max'];
  796. $sql = "SELECT score FROM $tbl_attendance_result WHERE c_id = {$course_info['real_id']} AND user_id=$user_id AND attendance_id=".$attendance['id'];
  797. $rs = Database::query($sql);
  798. $score = 0;
  799. if (Database::num_rows($rs) > 0) {
  800. $row = Database::fetch_array($rs);
  801. $score = $row['score'];
  802. }
  803. $faults = $total_done_attendance-$score;
  804. $faults = $faults > 0 ? $faults:0;
  805. $total_faults += $faults;
  806. $total_weight += $total_done_attendance;
  807. }
  808. $porcent = $total_weight > 0 ?round(($total_faults*100)/$total_weight,0):0;
  809. $results['faults'] = $total_faults;
  810. $results['total'] = $total_weight;
  811. $results['porcent'] = $porcent;
  812. return $results;
  813. }
  814. /**
  815. * Get registered users' attendance sheet inside current course
  816. * @param int attendance id
  817. * @param int user id for showing data for only one user (optional)
  818. * @return array users attendance sheet data
  819. */
  820. public function get_users_attendance_sheet($attendance_id, $user_id = 0) {
  821. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  822. $tbl_attendance_calendar= Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  823. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  824. $calendar_ids = array();
  825. // get all dates from calendar by current attendance
  826. foreach ($attendance_calendar as $cal) {
  827. $calendar_ids[] = $cal['id'];
  828. }
  829. $course_id = $this->get_course_int_id();
  830. $data = array();
  831. if (empty($user_id)) {
  832. // get all registered users inside current course
  833. $users = $this->get_users_rel_course();
  834. $user_ids = array_keys($users);
  835. if (count($calendar_ids) > 0 && count($user_ids) > 0) {
  836. foreach ($user_ids as $uid) {
  837. $sql = "SELECT * FROM $tbl_attendance_sheet
  838. WHERE c_id = $course_id AND
  839. user_id = '$uid' AND
  840. attendance_calendar_id IN(".implode(',', $calendar_ids).") ";
  841. $res = Database::query($sql);
  842. if (Database::num_rows($res) > 0) {
  843. while ($row = Database::fetch_array($res)) {
  844. $data[$uid][$row['attendance_calendar_id']]['presence'] = $row['presence'];
  845. }
  846. }
  847. }
  848. }
  849. } else {
  850. // Get attendance for current user
  851. $user_id = intval($user_id);
  852. if (count($calendar_ids) > 0) {
  853. $sql = "SELECT cal.date_time, att.presence
  854. FROM $tbl_attendance_sheet att
  855. INNER JOIN $tbl_attendance_calendar cal
  856. ON cal.id = att.attendance_calendar_id
  857. WHERE att.c_id = $course_id AND
  858. cal.c_id = $course_id AND
  859. att.user_id = '$user_id' AND
  860. att.attendance_calendar_id IN(".implode(',',$calendar_ids).")
  861. ORDER BY date_time";
  862. $res = Database::query($sql);
  863. if (Database::num_rows($res) > 0) {
  864. while ($row = Database::fetch_array($res)) {
  865. $row['date_time'] = api_convert_and_format_date($row['date_time'], null, date_default_timezone_get());
  866. $data[$user_id][] = $row;
  867. }
  868. }
  869. }
  870. }
  871. return $data;
  872. }
  873. /**
  874. * Get next attendance calendar without presences (done attendances)
  875. * @param int attendance id
  876. * @return int attendance calendar id
  877. */
  878. public function get_next_attendance_calendar_id($attendance_id) {
  879. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  880. $attendance_id = intval($attendance_id);
  881. $course_id = $this->get_course_int_id();
  882. $sql = "SELECT id FROM $tbl_attendance_calendar
  883. WHERE c_id = $course_id AND attendance_id = '$attendance_id' AND done_attendance = 0 ORDER BY date_time limit 1";
  884. $rs = Database::query($sql);
  885. $next_calendar_id = 0;
  886. if (Database::num_rows($rs) > 0) {
  887. $row = Database::fetch_array($rs);
  888. $next_calendar_id = $row['id'];
  889. }
  890. return $next_calendar_id;
  891. }
  892. /**
  893. * Get next attendance calendar datetime without presences (done attendances)
  894. * @param int attendance id
  895. * @return int UNIX time format datetime
  896. */
  897. public function get_next_attendance_calendar_datetime($attendance_id) {
  898. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  899. $course_id = $this->get_course_int_id();
  900. $attendance_id = intval($attendance_id);
  901. $sql = "SELECT id, date_time FROM $tbl_attendance_calendar WHERE c_id = $course_id AND attendance_id = '$attendance_id' AND done_attendance = 0 ORDER BY date_time limit 1";
  902. $rs = Database::query($sql);
  903. $next_calendar_datetime = 0;
  904. if (Database::num_rows($rs) > 0) {
  905. $row = Database::fetch_array($rs);
  906. $next_calendar_datetime = api_get_local_time($row['date_time']);
  907. }
  908. return $next_calendar_datetime;
  909. }
  910. /**
  911. * Get user' score from current attendance
  912. * @param int user id
  913. * @param int attendance id
  914. * @return int score
  915. */
  916. public function get_user_score($user_id, $attendance_id) {
  917. $tbl_attendance_result = Database::get_course_table(TABLE_ATTENDANCE_RESULT);
  918. $user_id = intval($user_id);
  919. $attendance_id = intval($attendance_id);
  920. $course_id = $this->get_course_int_id();
  921. $sql = "SELECT score FROM $tbl_attendance_result
  922. WHERE c_id = $course_id AND user_id='$user_id' AND attendance_id='$attendance_id'";
  923. $rs = Database::query($sql);
  924. $score = 0;
  925. if (Database::num_rows($rs) > 0) {
  926. $row = Database::fetch_array($rs);
  927. $score = $row['score'];
  928. }
  929. return $score;
  930. }
  931. /**
  932. * Get attendance calendar data by id
  933. * @param int attendance calendar id
  934. * @return array attendance calendar data
  935. */
  936. public function get_attendance_calendar_by_id($calendar_id) {
  937. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  938. $calendar_id = intval($calendar_id);
  939. $course_id = $this->get_course_int_id();
  940. $sql = "SELECT * FROM $tbl_attendance_calendar WHERE c_id = $course_id AND id = '$calendar_id' ";
  941. $rs = Database::query($sql);
  942. $data = array();
  943. if (Database::num_rows($rs) > 0) {
  944. while ($row = Database::fetch_array($rs)) {
  945. $row['date_time'] = api_get_local_time($row['date_time']);
  946. $data = $row;
  947. }
  948. }
  949. return $data;
  950. }
  951. /**
  952. * Get all attendance calendar data inside current attendance
  953. * @param int attendance id
  954. * @return array attendance calendar data
  955. */
  956. public function get_attendance_calendar($attendance_id, $type = 'all', $calendar_id = null) {
  957. global $dateFormatShort, $timeNoSecFormat;
  958. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  959. $attendance_id = intval($attendance_id);
  960. $course_id = $this->get_course_int_id();
  961. $sql = "SELECT * FROM $tbl_attendance_calendar WHERE c_id = $course_id AND attendance_id = '$attendance_id' ";
  962. if (!in_array($type, array('today', 'all', 'all_done', 'all_not_done','calendar_id'))) {
  963. $type = 'all';
  964. }
  965. switch ($type) {
  966. case 'calendar_id':
  967. $calendar_id = intval($calendar_id);
  968. if (!empty($calendar_id)) {
  969. $sql.= " AND id = $calendar_id";
  970. }
  971. break;
  972. case 'today':
  973. //$sql .= ' AND DATE_FORMAT(date_time,"%d-%m-%Y") = DATE_FORMAT("'.api_get_utc_datetime().'", "%d-%m-%Y" )';
  974. break;
  975. case 'all_done':
  976. $sql .= " AND done_attendance = 1 ";
  977. break;
  978. case 'all_not_done':
  979. $sql .= " AND done_attendance = 0 ";
  980. break;
  981. case 'all':
  982. default:
  983. break;
  984. }
  985. $sql .= " ORDER BY date_time ";
  986. $rs = Database::query($sql);
  987. $data = array();
  988. if (Database::num_rows($rs) > 0) {
  989. while ($row = Database::fetch_array($rs,'ASSOC')) {
  990. $row['db_date_time'] = $row['date_time'];
  991. $row['date_time'] = api_get_local_time($row['date_time']);
  992. $row['date'] = api_format_date($row['date_time'], DATE_FORMAT_SHORT);
  993. $row['time'] = api_format_date($row['date_time'], TIME_NO_SEC_FORMAT);
  994. if ($type == 'today') {
  995. if (date('d-m-Y', api_strtotime($row['date_time'], 'UTC')) == date('d-m-Y', time())) {
  996. $data[] = $row;
  997. }
  998. } else {
  999. $data[] = $row;
  1000. }
  1001. }
  1002. }
  1003. return $data;
  1004. }
  1005. /**
  1006. * Get number of attendance calendar inside current attendance
  1007. * @param int attendance id
  1008. * @return int number of dates in attendance calendar
  1009. */
  1010. public function get_number_of_attendance_calendar($attendance_id) {
  1011. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1012. $attendance_id = intval($attendance_id);
  1013. $course_id = $this->get_course_int_id();
  1014. $sql = "SELECT count(id) FROM $tbl_attendance_calendar
  1015. WHERE c_id = $course_id AND attendance_id = '$attendance_id'";
  1016. $rs = Database::query($sql);
  1017. $row = Database::fetch_row($rs);
  1018. $count = $row[0];
  1019. return $count;
  1020. }
  1021. /**
  1022. * Get count dates inside attendance calendar by attendance id
  1023. * @param int attendance id
  1024. * @return int count of dates
  1025. */
  1026. public function get_count_dates_inside_attendance_calendar($attendance_id) {
  1027. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1028. $attendance_id = intval($attendance_id);
  1029. $course_id = $this->get_course_int_id();
  1030. $sql = "SELECT count(id) FROM $tbl_attendance_calendar
  1031. WHERE c_id = $course_id AND attendance_id = '$attendance_id'";
  1032. $rs = Database::query($sql);
  1033. $count = 0;
  1034. if (Database::num_rows($rs) > 0) {
  1035. $row = Database::fetch_row($rs);
  1036. $count = $row[0];
  1037. }
  1038. return $count;
  1039. }
  1040. /**
  1041. * check if all calendar of an attendance is done
  1042. * @param int attendance id
  1043. * @return bool True if all calendar is done, otherwise false
  1044. */
  1045. public function is_all_attendance_calendar_done($attendance_id) {
  1046. $attendance_id = intval($attendance_id);
  1047. $done_calendar = $this->get_done_attendance_calendar($attendance_id);
  1048. $count_dates_in_calendar = $this->get_count_dates_inside_attendance_calendar($attendance_id);
  1049. $number_of_dates = $this->get_number_of_attendance_calendar($attendance_id);
  1050. $result = false;
  1051. if ($number_of_dates && (intval($count_dates_in_calendar) == intval($done_calendar))) {
  1052. $result = true;
  1053. }
  1054. return $result;
  1055. }
  1056. /**
  1057. * check if an attendance is locked
  1058. * @param int attendance id
  1059. * @param bool
  1060. */
  1061. public static function is_locked_attendance($attendance_id) {
  1062. //use gradebook lock
  1063. $result = api_resource_is_locked_by_gradebook($attendance_id, LINK_ATTENDANCE);
  1064. return $result;
  1065. }
  1066. public function get_attendance_calendar_data_by_date($attendance_id, $date_time) {
  1067. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1068. $course_id = $this->get_course_int_id();
  1069. $attendance_id = intval($attendance_id);
  1070. // check if datetime already exists inside the table
  1071. $sql = "SELECT * FROM $tbl_attendance_calendar
  1072. WHERE c_id = $course_id AND
  1073. date_time = '".Database::escape_string($date_time)."' AND
  1074. attendance_id = '$attendance_id'";
  1075. $result = Database::query($sql);
  1076. if (Database::num_rows($result)) {
  1077. return Database::fetch_array($result, 'ASSOC');
  1078. } else {
  1079. return false;
  1080. }
  1081. }
  1082. /**
  1083. * Add new datetime inside attendance calendar table
  1084. * @param int attendance id
  1085. * @return int affected rows
  1086. */
  1087. public function attendance_calendar_add($attendance_id, $return_inserted_id = false) {
  1088. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1089. $affected_rows = 0;
  1090. $cal_id = null;
  1091. $attendance_id = intval($attendance_id);
  1092. $course_id = $this->get_course_int_id();
  1093. $calendar_info = $this->get_attendance_calendar_data_by_date($attendance_id, $this->date_time);
  1094. if ($calendar_info == false) {
  1095. $sql = "INSERT INTO $tbl_attendance_calendar SET
  1096. c_id = $course_id,
  1097. date_time = '".Database::escape_string($this->date_time)."',
  1098. attendance_id = '$attendance_id'";
  1099. Database::query($sql);
  1100. $cal_id = Database::get_last_insert_id();
  1101. $affected_rows = Database::affected_rows();
  1102. }
  1103. // update locked attendance
  1104. $is_all_calendar_done = $this->is_all_attendance_calendar_done($attendance_id);
  1105. if (!$is_all_calendar_done) {
  1106. $unlock = self::lock_attendance($attendance_id, false);
  1107. } else {
  1108. $unlock = self::lock_attendance($attendance_id);
  1109. }
  1110. if ($return_inserted_id) {
  1111. return $cal_id;
  1112. }
  1113. return $affected_rows;
  1114. }
  1115. /**
  1116. * save repeated date inside attendance calendar table
  1117. * @param int attendance id
  1118. * @param int start date in tms
  1119. * @param int end date in tms
  1120. * @param string repeat type daily, weekly, monthlyByDate
  1121. */
  1122. public function attendance_repeat_calendar_add($attendance_id, $start_date, $end_date, $repeat_type) {
  1123. $attendance_id = intval($attendance_id);
  1124. // save start date
  1125. $datetimezone = api_get_utc_datetime($start_date);
  1126. $this->set_date_time($datetimezone);
  1127. $res = $this->attendance_calendar_add($attendance_id);
  1128. //86400 = 24 hours in seconds
  1129. //604800 = 1 week in seconds
  1130. // Saves repeated dates
  1131. switch($repeat_type) {
  1132. case 'daily':
  1133. $j = 1;
  1134. for ($i = $start_date + 86400; ($i <= $end_date); $i += 86400) {
  1135. //$datetimezone = api_get_utc_date_add(api_get_utc_datetime($start_date), 0 , 0, $j); //to support europe timezones
  1136. $datetimezone = api_get_utc_datetime($i);
  1137. $this->set_date_time($datetimezone);
  1138. $res = $this->attendance_calendar_add($attendance_id);
  1139. $j++;
  1140. }
  1141. break;
  1142. exit;
  1143. case 'weekly':
  1144. $j = 1;
  1145. for ($i = $start_date + 604800; ($i <= $end_date); $i += 604800) {
  1146. $datetimezone = api_get_utc_datetime($i);
  1147. //$datetimezone = api_get_utc_date_add(api_get_utc_datetime($start_date), 0 , 0, $j*7); //to support europe timezones
  1148. $this->set_date_time($datetimezone);
  1149. $res = $this->attendance_calendar_add($attendance_id);
  1150. $j++;
  1151. }
  1152. break;
  1153. case 'monthlyByDate':
  1154. $j = 1;
  1155. //@todo fix bug with february
  1156. for ($i = $start_date + 2419200; ($i <= $end_date); $i += 2419200) {
  1157. $datetimezone = api_get_utc_datetime($i);
  1158. //$datetimezone = api_get_utc_date_add(api_get_utc_datetime($start_date), 0 , $j); //to support europe timezones
  1159. $this->set_date_time($datetimezone);
  1160. $res = $this->attendance_calendar_add($attendance_id);
  1161. $j++;
  1162. }
  1163. break;
  1164. }
  1165. }
  1166. /**
  1167. * Adds x months to a UNIX timestamp
  1168. * @param int The timestamp
  1169. * @param int The number of years to add
  1170. * @return int The new timestamp
  1171. */
  1172. private function add_month($timestamp, $num=1) {
  1173. $values = api_get_utc_datetime($timestamp);
  1174. $values = str_replace(array(':','-',' '), '/', $values);
  1175. list($y, $m, $d, $h, $n, $s) = split('/',$values);
  1176. if($m+$num>12) {
  1177. $y += floor($num/12);
  1178. $m += $num%12;
  1179. } else {
  1180. $m += $num;
  1181. }
  1182. //date_default_timezone_set('UTC');
  1183. // return mktime($h, $n, $s, $m, $d, $y);
  1184. $result = api_strtotime($y.'-'.$m.'-'.$d.' '.$h.':'.$n.':'.$s, 'UTC');
  1185. if (!empty($result)) {
  1186. return $result;
  1187. }
  1188. return false;
  1189. }
  1190. /**
  1191. * edit a datetime inside attendance calendar table
  1192. * @param int attendance calendar id
  1193. * @param int attendance id
  1194. * @return int affected rows
  1195. */
  1196. public function attendance_calendar_edit($calendar_id, $attendance_id) {
  1197. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1198. $affected_rows = 0;
  1199. $attendance_id = intval($attendance_id);
  1200. $course_id = api_get_course_int_id();
  1201. // check if datetime already exists inside the table
  1202. $sql = "SELECT id FROM $tbl_attendance_calendar
  1203. WHERE c_id = $course_id AND date_time = '".Database::escape_string($this->date_time)."' AND attendance_id = '$attendance_id'";
  1204. $rs = Database::query($sql);
  1205. if (Database::num_rows($rs) == 0) {
  1206. $sql = "UPDATE $tbl_attendance_calendar SET date_time='".Database::escape_string($this->date_time)."' WHERE c_id = $course_id AND id = '".intval($calendar_id)."'";
  1207. Database::query($sql);
  1208. $affected_rows = Database::affected_rows();
  1209. }
  1210. // update locked attendance
  1211. $is_all_calendar_done = self::is_all_attendance_calendar_done($attendance_id);
  1212. if (!$is_all_calendar_done) {
  1213. $unlock = self::lock_attendance($attendance_id, false);
  1214. } else {
  1215. $unlock = self::lock_attendance($attendance_id);
  1216. }
  1217. return $affected_rows;
  1218. }
  1219. /**
  1220. * delete a datetime from attendance calendar table
  1221. * @param int attendance calendar id
  1222. * @param int attendance id
  1223. * @param bool true for removing all calendar inside current attendance, false for removing by calendar id
  1224. * @return int affected rows
  1225. */
  1226. public function attendance_calendar_delete($calendar_id, $attendance_id , $all_delete = false) {
  1227. $tbl_attendance_calendar = Database::get_course_table(TABLE_ATTENDANCE_CALENDAR);
  1228. $tbl_attendance_sheet = Database::get_course_table(TABLE_ATTENDANCE_SHEET);
  1229. $session_id = api_get_session_id();
  1230. $attendance_id = intval($attendance_id);
  1231. // get all registered users inside current course
  1232. $users = $this->get_users_rel_course();
  1233. $user_ids = array_keys($users);
  1234. $course_id = api_get_course_int_id();
  1235. if ($all_delete) {
  1236. $attendance_calendar = $this->get_attendance_calendar($attendance_id);
  1237. $calendar_ids = array();
  1238. // get all dates from calendar by current attendance
  1239. if (!empty($attendance_calendar)) {
  1240. foreach ($attendance_calendar as $cal) {
  1241. // delete all data from attendance sheet
  1242. $sql = "DELETE FROM $tbl_attendance_sheet WHERE c_id = $course_id AND attendance_calendar_id = '".intval($cal['id'])."'";
  1243. Database::query($sql);
  1244. // delete data from attendance calendar
  1245. $sql = "DELETE FROM $tbl_attendance_calendar WHERE c_id = $course_id AND id = '".intval($cal['id'])."'";
  1246. Database::query($sql);
  1247. }
  1248. }
  1249. } else {
  1250. // delete just one row from attendance sheet by the calendar id
  1251. $sql = "DELETE FROM $tbl_attendance_sheet WHERE c_id = $course_id AND attendance_calendar_id = '".intval($calendar_id)."'";
  1252. Database::query($sql);
  1253. // delete data from attendance calendar
  1254. $sql = "DELETE FROM $tbl_attendance_calendar WHERE c_id = $course_id AND id = '".intval($calendar_id)."'";
  1255. Database::query($sql);
  1256. }
  1257. $affected_rows = Database::affected_rows();
  1258. // update users' results
  1259. $this->update_users_results($user_ids, $attendance_id);
  1260. return $affected_rows;
  1261. }
  1262. /**
  1263. * buid a string datetime from array
  1264. * @param array array containing data e.g: $array('Y'=>'2010', 'F' => '02', 'd' => '10', 'H' => '12', 'i' => '30')
  1265. * @return string date and time e.g: '2010-02-10 12:30:00'
  1266. */
  1267. public function build_datetime_from_array($array) {
  1268. return Text::return_datetime_from_array($array);
  1269. }
  1270. /** Setters for fields of attendances tables **/
  1271. public function set_session_id($session_id) {
  1272. $this->session_id = intval($session_id);
  1273. }
  1274. public function set_course_id($course_id) {
  1275. $this->course_id = Database::escape_string($course_id);
  1276. }
  1277. public function set_date_time($datetime) {
  1278. $this->date_time = $datetime;
  1279. }
  1280. public function set_name($name) {
  1281. $this->name = $name;
  1282. }
  1283. public function set_description($description) {
  1284. $this->description = $description;
  1285. }
  1286. public function set_attendance_qualify_title($attendance_qualify_title) {
  1287. $this->attendance_qualify_title = $attendance_qualify_title;
  1288. }
  1289. public function set_attendance_weight($attendance_weight) {
  1290. $this->attendance_weight = $attendance_weight;
  1291. }
  1292. /** Getters for fields of attendances tables **/
  1293. public function get_session_id() {
  1294. return isset($this->session_id) ? $this->session_id : api_get_session_id();
  1295. }
  1296. public function get_course_id() {
  1297. return isset($this->course_id) ? $this->course_id : api_get_course_id();
  1298. }
  1299. public function get_date_time() {
  1300. return $this->date_time;
  1301. }
  1302. public function get_name() {
  1303. return $this->name;
  1304. }
  1305. public function get_description() {
  1306. return $this->description;
  1307. }
  1308. public function get_attendance_qualify_title() {
  1309. return $this->attendance_qualify_title;
  1310. }
  1311. public function get_attendance_weight() {
  1312. return $this->attendance_weight;
  1313. }
  1314. public function get_course_int_id() {
  1315. return isset($this->course_int_id) ? $this->course_int_id : api_get_course_int_id();
  1316. }
  1317. public function set_course_int_id($course_id) {
  1318. $this->course_int_id = intval($course_id);
  1319. }
  1320. public function get_attendance_states() {
  1321. $attendance_states = array(
  1322. '1' => array('label' => get_lang('Present'), 'class' => 'btn-success') ,
  1323. '2' => array('label' => get_lang('Late'), 'class' => 'btn-info') ,
  1324. '3' => array('label' => get_lang('VeryLate'), 'class' => 'btn-warning') ,
  1325. '0' => array('label' => get_lang('Absent'), 'class' => 'btn-danger') ,
  1326. '4' => array('label' => get_lang('Default'), 'class' => 'btn') ,
  1327. );
  1328. return $attendance_states;
  1329. }
  1330. public function get_status_that_score_point_to_users() {
  1331. return array(
  1332. 1, //presence
  1333. 2, //late
  1334. 3, //very late
  1335. );
  1336. }
  1337. public function get_default_attendance_state() {
  1338. return 4;
  1339. }
  1340. public function get_attendance_state_button($state_id, $add_label = false, $extra_attributes = array()) {
  1341. $state_list = $this->get_attendance_states();
  1342. if (isset($state_list[$state_id])) {
  1343. $label = '&nbsp;';
  1344. if ($add_label) {
  1345. $label = $state_list[$state_id]['label'];
  1346. }
  1347. $default_class = isset($extra_attributes['class']) ? $extra_attributes['class'] : null;
  1348. $extra_attributes['class'] = $default_class." btn ".$state_list[$state_id]['class'];
  1349. return Display::url($label, '', $extra_attributes);
  1350. }
  1351. return null;
  1352. }
  1353. }