attendance.lib.php 54 KB

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