1 | <?php |
||
2 | /* For licensing terms, see /license.txt */ |
||
3 | |||
4 | use ChamiloSession as Session; |
||
5 | use CpChart\Cache as pCache; |
||
6 | use CpChart\Data as pData; |
||
7 | use CpChart\Image as pImage; |
||
8 | |||
9 | /** |
||
10 | * Class MySpace. |
||
11 | */ |
||
12 | class MySpace |
||
13 | { |
||
14 | /** |
||
15 | * Get admin actions. |
||
16 | * |
||
17 | * @return string |
||
18 | */ |
||
19 | public static function getAdminActions() |
||
20 | { |
||
21 | $actions = [ |
||
22 | [ |
||
23 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=coaches', |
||
24 | 'content' => get_lang('DisplayCoaches'), |
||
25 | ], |
||
26 | [ |
||
27 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=user', |
||
28 | 'content' => get_lang('DisplayUserOverview'), |
||
29 | ], |
||
30 | [ |
||
31 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=session', |
||
32 | 'content' => get_lang('DisplaySessionOverview'), |
||
33 | ], |
||
34 | [ |
||
35 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=course', |
||
36 | 'content' => get_lang('DisplayCourseOverview'), |
||
37 | ], |
||
38 | [ |
||
39 | 'url' => api_get_path(WEB_CODE_PATH).'tracking/question_course_report.php?view=admin', |
||
40 | 'content' => get_lang('LPQuestionListResults'), |
||
41 | ], |
||
42 | [ |
||
43 | 'url' => api_get_path(WEB_CODE_PATH).'tracking/course_session_report.php?view=admin', |
||
44 | 'content' => get_lang('LPExerciseResultsBySession'), |
||
45 | ], |
||
46 | [ |
||
47 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=accessoverview', |
||
48 | 'content' => get_lang('DisplayAccessOverview').' ('.get_lang('Beta').')', |
||
49 | ], |
||
50 | [ |
||
51 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/exercise_category_report.php', |
||
52 | 'content' => get_lang('ExerciseCategoryAllSessionsReport'), |
||
53 | ], |
||
54 | [ |
||
55 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/survey_report.php', |
||
56 | 'content' => get_lang('SurveysReport'), |
||
57 | ], |
||
58 | [ |
||
59 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/tc_report.php', |
||
60 | 'content' => get_lang('TCReport'), |
||
61 | ], |
||
62 | [ |
||
63 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/ti_report.php', |
||
64 | 'content' => get_lang('TIReport'), |
||
65 | ], |
||
66 | [ |
||
67 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/question_stats_global.php', |
||
68 | 'content' => get_lang('QuestionStats'), |
||
69 | ], |
||
70 | [ |
||
71 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/question_stats_global_detail.php', |
||
72 | 'content' => get_lang('ExerciseAttemptStatsReport'), |
||
73 | ], |
||
74 | ]; |
||
75 | |||
76 | $field = new ExtraField('user'); |
||
77 | $companyField = $field->get_handler_field_info_by_field_variable('company'); |
||
78 | if (!empty($companyField)) { |
||
79 | $actions[] = [ |
||
80 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=company', |
||
81 | 'content' => get_lang('UserByEntityReport'), |
||
82 | ]; |
||
83 | } |
||
84 | $field = new ExtraField('lp'); |
||
85 | $authorsField = $field->get_handler_field_info_by_field_variable('authors'); |
||
86 | if (!empty($authorsField)) { |
||
87 | $actions[] = [ |
||
88 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=learningPath', |
||
89 | 'content' => get_lang('LpByAuthor'), |
||
90 | ]; |
||
91 | } |
||
92 | $field = new ExtraField('lp_item'); |
||
93 | $authorsItemField = $field->get_handler_field_info_by_field_variable('authorlpitem'); |
||
94 | if (!empty($authorsItemField)) { |
||
95 | $actions[] = [ |
||
96 | 'url' => api_get_path(WEB_CODE_PATH).'mySpace/admin_view.php?display=learningPathByItem', |
||
97 | 'content' => get_lang('LearningPathItemByAuthor'), |
||
98 | ]; |
||
99 | } |
||
100 | |||
101 | return Display::actions($actions, null); |
||
102 | } |
||
103 | |||
104 | /** |
||
105 | * @return string |
||
106 | */ |
||
107 | public static function getTopMenu() |
||
108 | { |
||
109 | $menuItems = []; |
||
110 | $menuItems[] = Display::url( |
||
111 | Display::return_icon( |
||
112 | 'statistics.png', |
||
113 | get_lang('MyStats'), |
||
114 | '', |
||
115 | ICON_SIZE_MEDIUM |
||
116 | ), |
||
117 | api_get_path(WEB_CODE_PATH)."auth/my_progress.php" |
||
118 | ); |
||
119 | $menuItems[] = Display::url( |
||
120 | Display::return_icon( |
||
121 | 'teacher.png', |
||
122 | get_lang('TeacherInterface'), |
||
123 | [], |
||
124 | 32 |
||
125 | ), |
||
126 | api_get_path(WEB_CODE_PATH).'mySpace/?view=teacher' |
||
127 | ); |
||
128 | $menuItems[] = Display::url( |
||
129 | Display::return_icon( |
||
130 | 'star_na.png', |
||
131 | get_lang('AdminInterface'), |
||
132 | [], |
||
133 | 32 |
||
134 | ), |
||
135 | '#' |
||
136 | ); |
||
137 | $menuItems[] = Display::url( |
||
138 | Display::return_icon('quiz.png', get_lang('ExamTracking'), [], 32), |
||
139 | api_get_path(WEB_CODE_PATH).'tracking/exams.php' |
||
140 | ); |
||
141 | $menu = ''; |
||
142 | foreach ($menuItems as $item) { |
||
143 | $menu .= $item; |
||
144 | } |
||
145 | $menu .= '<br />'; |
||
146 | |||
147 | return $menu; |
||
148 | } |
||
149 | |||
150 | /** |
||
151 | * This function serves exporting data in CSV format. |
||
152 | * |
||
153 | * @param array $header the header labels |
||
154 | * @param array $data the data array |
||
155 | * @param string $file_name the name of the file which contains exported data |
||
156 | * |
||
157 | * @return string mixed Returns a message (string) if an error occurred |
||
158 | */ |
||
159 | public function export_csv($header, $data, $file_name = 'export.csv') |
||
160 | { |
||
161 | $archive_path = api_get_path(SYS_ARCHIVE_PATH); |
||
162 | $archive_url = api_get_path(WEB_CODE_PATH).'course_info/download.php?archive_path=&archive='; |
||
163 | $message = ''; |
||
164 | if (!$open = fopen($archive_path.$file_name, 'w+')) { |
||
165 | $message = get_lang('noOpen'); |
||
166 | } else { |
||
167 | $info = ''; |
||
168 | |||
169 | foreach ($header as $value) { |
||
170 | $info .= $value.';'; |
||
171 | } |
||
172 | $info .= "\r\n"; |
||
173 | |||
174 | foreach ($data as $row) { |
||
175 | foreach ($row as $value) { |
||
176 | $info .= $value.';'; |
||
177 | } |
||
178 | $info .= "\r\n"; |
||
179 | } |
||
180 | |||
181 | fwrite($open, $info); |
||
182 | fclose($open); |
||
183 | @chmod($file_name, api_get_permissions_for_new_files()); |
||
184 | |||
185 | header("Location:".$archive_url.$file_name); |
||
186 | exit; |
||
187 | } |
||
188 | |||
189 | return $message; |
||
190 | } |
||
191 | |||
192 | /** |
||
193 | * Gets the connections to a course as an array of login and logout time. |
||
194 | * |
||
195 | * @param int $userId User id |
||
196 | * @param array $courseInfo |
||
197 | * @param int $sessionId Session id (optional, default = 0) |
||
198 | * |
||
199 | * @return array Connections |
||
200 | */ |
||
201 | public static function get_connections_to_course( |
||
202 | $userId, |
||
203 | $courseInfo, |
||
204 | $sessionId = 0 |
||
205 | ) { |
||
206 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS); |
||
207 | |||
208 | // protect data |
||
209 | $userId = (int) $userId; |
||
210 | $courseId = (int) $courseInfo['real_id']; |
||
211 | $sessionId = (int) $sessionId; |
||
212 | $sessionCondition = api_get_session_condition($sessionId); |
||
213 | |||
214 | $sql = 'SELECT login_course_date, logout_course_date |
||
215 | FROM '.$table.' |
||
216 | WHERE |
||
217 | user_id = '.$userId.' AND |
||
218 | c_id = '.$courseId.' |
||
219 | '.$sessionCondition.' |
||
220 | ORDER BY login_course_date ASC'; |
||
221 | $rs = Database::query($sql); |
||
222 | $connections = []; |
||
223 | |||
224 | while ($row = Database::fetch_array($rs)) { |
||
225 | $connections[] = [ |
||
226 | 'login' => $row['login_course_date'], |
||
227 | 'logout' => $row['logout_course_date'], |
||
228 | ]; |
||
229 | } |
||
230 | |||
231 | return $connections; |
||
232 | } |
||
233 | |||
234 | /** |
||
235 | * @param $user_id |
||
236 | * @param $course_list |
||
237 | * @param int $session_id |
||
238 | * |
||
239 | * @return array|bool |
||
240 | */ |
||
241 | public static function get_connections_from_course_list( |
||
242 | $user_id, |
||
243 | $course_list, |
||
244 | $session_id = 0 |
||
245 | ) { |
||
246 | // Database table definitions |
||
247 | $tbl_track_course = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS); |
||
248 | if (empty($course_list)) { |
||
249 | return false; |
||
250 | } |
||
251 | |||
252 | // protect data |
||
253 | $user_id = (int) $user_id; |
||
254 | $session_id = (int) $session_id; |
||
255 | $new_course_list = []; |
||
256 | foreach ($course_list as $course_item) { |
||
257 | $courseInfo = api_get_course_info($course_item['code']); |
||
258 | if ($courseInfo) { |
||
259 | $courseId = $courseInfo['real_id']; |
||
260 | $new_course_list[] = '"'.$courseId.'"'; |
||
261 | } |
||
262 | } |
||
263 | $course_list = implode(', ', $new_course_list); |
||
264 | |||
265 | if (empty($course_list)) { |
||
266 | return false; |
||
267 | } |
||
268 | $sql = 'SELECT login_course_date, logout_course_date, c_id |
||
269 | FROM '.$tbl_track_course.' |
||
270 | WHERE |
||
271 | user_id = '.$user_id.' AND |
||
272 | c_id IN ('.$course_list.') AND |
||
273 | session_id = '.$session_id.' |
||
274 | ORDER BY login_course_date ASC'; |
||
275 | $rs = Database::query($sql); |
||
276 | $connections = []; |
||
277 | |||
278 | while ($row = Database::fetch_array($rs)) { |
||
279 | $timestamp_login_date = api_strtotime($row['login_course_date'], 'UTC'); |
||
280 | $timestamp_logout_date = api_strtotime($row['logout_course_date'], 'UTC'); |
||
281 | $connections[] = [ |
||
282 | 'login' => $timestamp_login_date, |
||
283 | 'logout' => $timestamp_logout_date, |
||
284 | 'c_id' => $row['c_id'], |
||
285 | ]; |
||
286 | } |
||
287 | |||
288 | return $connections; |
||
289 | } |
||
290 | |||
291 | /** |
||
292 | * Creates a small table in the last column of the table with the user overview. |
||
293 | * |
||
294 | * @param int $user_id the id of the user |
||
295 | * |
||
296 | * @return array List course |
||
297 | */ |
||
298 | public static function returnCourseTracking($user_id) |
||
299 | { |
||
300 | $user_id = (int) $user_id; |
||
301 | |||
302 | if (empty($user_id)) { |
||
303 | return []; |
||
304 | } |
||
305 | |||
306 | $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER); |
||
307 | // getting all the courses of the user |
||
308 | $sql = "SELECT * FROM $tbl_course_user |
||
309 | WHERE |
||
310 | user_id = $user_id AND |
||
311 | relation_type <> ".COURSE_RELATION_TYPE_RRHH; |
||
312 | $result = Database::query($sql); |
||
313 | |||
314 | $list = []; |
||
315 | |||
316 | while ($row = Database::fetch_array($result)) { |
||
317 | $courseInfo = api_get_course_info_by_id($row['c_id']); |
||
318 | $courseId = $courseInfo['real_id']; |
||
319 | $courseCode = $courseInfo['code']; |
||
320 | |||
321 | if (empty($courseInfo)) { |
||
322 | continue; |
||
323 | } |
||
324 | |||
325 | $avg_score = Tracking::get_avg_student_score($user_id, $courseCode); |
||
326 | if (is_numeric($avg_score)) { |
||
327 | $avg_score = round($avg_score, 2); |
||
328 | } else { |
||
329 | $avg_score = '-'; |
||
330 | } |
||
331 | |||
332 | // Student exercises results (obtained score, maximum score, number of exercises answered, score percentage) |
||
333 | $exercisesResults = self::exercises_results($user_id, $courseCode); |
||
334 | |||
335 | $resultToString = ''; |
||
336 | if (!is_null($exercisesResults['percentage'])) { |
||
337 | $resultToString = $exercisesResults['score_obtained'].'/'.$exercisesResults['score_possible'].' ( '.$exercisesResults['percentage'].'% )'; |
||
338 | } |
||
339 | |||
340 | $item = [ |
||
341 | 'code' => $courseInfo['code'], |
||
342 | 'real_id' => $courseInfo['real_id'], |
||
343 | 'title' => $courseInfo['title'], |
||
344 | 'category' => $courseInfo['categoryName'], |
||
345 | 'image_small' => $courseInfo['course_image'], |
||
346 | 'image_large' => $courseInfo['course_image_large'], |
||
347 | 'time_spent' => api_time_to_hms(Tracking::get_time_spent_on_the_course($user_id, $courseId)), |
||
348 | 'student_progress' => round(Tracking::get_avg_student_progress($user_id, $courseCode)), |
||
349 | 'student_score' => $avg_score, |
||
350 | 'student_message' => Tracking::count_student_messages($user_id, $courseCode), |
||
351 | 'student_assignments' => Tracking::count_student_assignments($user_id, $courseCode), |
||
352 | 'student_exercises' => $resultToString, |
||
353 | 'questions_answered' => $exercisesResults['questions_answered'], |
||
354 | 'last_connection' => Tracking::get_last_connection_date_on_the_course($user_id, $courseInfo), |
||
355 | ]; |
||
356 | $list[] = $item; |
||
357 | } |
||
358 | |||
359 | return $list; |
||
360 | } |
||
361 | |||
362 | /** |
||
363 | * Display a sortable table that contains an overview off all the |
||
364 | * reporting progress of all users and all courses the user is subscribed to. |
||
365 | * |
||
366 | * @author Patrick Cool <[email protected]>, Ghent University, Belgium |
||
367 | * Alex Aragon <[email protected]>, BeezNest, Perú |
||
368 | * |
||
369 | * @version Chamilo 1.11.8 |
||
370 | * |
||
371 | * @since April 2019 |
||
372 | */ |
||
373 | public static function returnTrackingUserOverviewFilter($user_id) |
||
374 | { |
||
375 | $tpl = new Template('', false, false, false, false, false, false); |
||
376 | $userInfo = api_get_user_info($user_id); |
||
377 | |||
378 | $avatar = UserManager::getUserPicture($user_id, USER_IMAGE_SIZE_SMALL); |
||
379 | $user = [ |
||
380 | 'id' => $user_id, |
||
381 | 'code_user' => $userInfo['official_code'], |
||
382 | 'complete_name' => $userInfo['complete_name'], |
||
383 | 'username' => $userInfo['username'], |
||
384 | 'course' => self::returnCourseTracking($user_id), |
||
385 | 'avatar' => $avatar, |
||
386 | ]; |
||
387 | |||
388 | $tpl->assign('item', $user); |
||
389 | $templateName = $tpl->get_template('my_space/partials/tracking_user_overview.tpl'); |
||
390 | $content = $tpl->fetch($templateName); |
||
391 | |||
392 | return $content; |
||
393 | } |
||
394 | |||
395 | /** |
||
396 | * Display a sortable table that contains an overview off all the |
||
397 | * reporting progress of all users and all courses the user is subscribed to. |
||
398 | * |
||
399 | * @author Patrick Cool <[email protected]>, Ghent University, Belgium |
||
400 | * Alex Aragon <[email protected]>, BeezNest, Perú |
||
401 | * |
||
402 | * @version Chamilo 1.11.8 |
||
403 | * |
||
404 | * @since October 2008, Update April 2019 |
||
405 | */ |
||
406 | public static function display_tracking_user_overview() |
||
407 | { |
||
408 | self::display_user_overview_export_options(); |
||
409 | |||
410 | $params = ['view' => 'admin', 'display' => 'user']; |
||
411 | $table = new SortableTable( |
||
412 | 'tracking_user_overview', |
||
413 | ['MySpace', 'get_number_of_users_tracking_overview'], |
||
414 | ['MySpace', 'get_user_data_tracking_overview'], |
||
415 | 0, |
||
416 | 20, |
||
417 | 'ASC', |
||
418 | null, [ |
||
419 | 'class' => 'table table-transparent', |
||
420 | ] |
||
421 | ); |
||
422 | $table->additional_parameters = $params; |
||
423 | |||
424 | $table->set_column_filter(0, ['MySpace', 'returnTrackingUserOverviewFilter']); |
||
425 | $tableContent = $table->return_table(); |
||
426 | $tpl = new Template('', false, false, false, false, false, false); |
||
427 | $tpl->assign('table', $tableContent); |
||
428 | $templateName = $tpl->get_template('my_space/user_summary.tpl'); |
||
429 | $tpl->display($templateName); |
||
430 | } |
||
431 | |||
432 | /** |
||
433 | * @param $export_csv |
||
434 | */ |
||
435 | public static function display_tracking_coach_overview($export_csv) |
||
436 | { |
||
437 | if ($export_csv) { |
||
438 | $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT); |
||
439 | } else { |
||
440 | $is_western_name_order = api_is_western_name_order(); |
||
441 | } |
||
442 | $sort_by_first_name = api_sort_by_first_name(); |
||
443 | |||
444 | if (isset($_GET['tracking_list_coaches_column'])) { |
||
445 | $tracking_column = (int) $_GET['tracking_list_coaches_column']; |
||
446 | } else { |
||
447 | $tracking_column = ($is_western_name_order xor $sort_by_first_name) ? 1 : 0; |
||
448 | } |
||
449 | |||
450 | $tracking_direction = (isset($_GET['tracking_list_coaches_direction']) && in_array(strtoupper($_GET['tracking_list_coaches_direction']), ['ASC', 'DESC', 'ASCENDING', 'DESCENDING', '0', '1'])) ? $_GET['tracking_list_coaches_direction'] : 'DESC'; |
||
451 | // Prepare array for column order - when impossible, use some of user names. |
||
452 | if ($is_western_name_order) { |
||
453 | $order = [ |
||
454 | 0 => 'firstname', |
||
455 | 1 => 'lastname', |
||
456 | 2 => $sort_by_first_name ? 'firstname' : 'lastname', |
||
457 | 3 => 'login_date', |
||
458 | 4 => $sort_by_first_name ? 'firstname' : 'lastname', |
||
459 | 5 => $sort_by_first_name ? 'firstname' : 'lastname', |
||
460 | ]; |
||
461 | } else { |
||
462 | $order = [ |
||
463 | 0 => 'lastname', |
||
464 | 1 => 'firstname', |
||
465 | 2 => $sort_by_first_name ? 'firstname' : 'lastname', |
||
466 | 3 => 'login_date', |
||
467 | 4 => $sort_by_first_name ? 'firstname' : 'lastname', |
||
468 | 5 => $sort_by_first_name ? 'firstname' : 'lastname', |
||
469 | ]; |
||
470 | } |
||
471 | $table = new SortableTable( |
||
472 | 'tracking_list_coaches_myspace', |
||
473 | ['MySpace', 'count_coaches'], |
||
474 | null, |
||
475 | ($is_western_name_order xor $sort_by_first_name) ? 1 : 0 |
||
476 | ); |
||
477 | $parameters['view'] = 'admin'; |
||
478 | $table->set_additional_parameters($parameters); |
||
479 | if ($is_western_name_order) { |
||
480 | $table->set_header(0, get_lang('FirstName'), true); |
||
481 | $table->set_header(1, get_lang('LastName'), true); |
||
482 | } else { |
||
483 | $table->set_header(0, get_lang('LastName'), true); |
||
484 | $table->set_header(1, get_lang('FirstName'), true); |
||
485 | } |
||
486 | $table->set_header(2, get_lang('TimeSpentOnThePlatform'), false); |
||
487 | $table->set_header(3, get_lang('LastConnexion'), false); |
||
488 | $table->set_header(4, get_lang('NbStudents'), false); |
||
489 | $table->set_header(5, get_lang('CountCours'), false); |
||
490 | $table->set_header(6, get_lang('NumberOfSessions'), false); |
||
491 | $table->set_header(7, get_lang('Sessions'), false); |
||
492 | |||
493 | if ($is_western_name_order) { |
||
494 | $csv_header[] = [ |
||
495 | get_lang('FirstName'), |
||
496 | get_lang('LastName'), |
||
497 | get_lang('TimeSpentOnThePlatform'), |
||
498 | get_lang('LastConnexion'), |
||
499 | get_lang('NbStudents'), |
||
500 | get_lang('CountCours'), |
||
501 | get_lang('NumberOfSessions'), |
||
502 | ]; |
||
503 | } else { |
||
504 | $csv_header[] = [ |
||
505 | get_lang('LastName'), |
||
506 | get_lang('FirstName'), |
||
507 | get_lang('TimeSpentOnThePlatform'), |
||
508 | get_lang('LastConnexion'), |
||
509 | get_lang('NbStudents'), |
||
510 | get_lang('CountCours'), |
||
511 | get_lang('NumberOfSessions'), |
||
512 | ]; |
||
513 | } |
||
514 | |||
515 | $tbl_track_login = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); |
||
516 | $tbl_user = Database::get_main_table(TABLE_MAIN_USER); |
||
517 | $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); |
||
518 | $tbl_sessions = Database::get_main_table(TABLE_MAIN_SESSION); |
||
519 | |||
520 | $sqlCoachs = "SELECT DISTINCT |
||
521 | scu.user_id as id_coach, |
||
522 | u.id as user_id, |
||
523 | lastname, |
||
524 | firstname, |
||
525 | MAX(login_date) as login_date |
||
526 | FROM $tbl_user u, $tbl_session_course_user scu, $tbl_track_login |
||
527 | WHERE |
||
528 | scu.user_id = u.id AND scu.status=2 AND login_user_id=u.id |
||
529 | GROUP BY user_id "; |
||
530 | |||
531 | if (api_is_multiple_url_enabled()) { |
||
532 | $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION); |
||
533 | $access_url_id = api_get_current_access_url_id(); |
||
534 | if ($access_url_id != -1) { |
||
535 | $sqlCoachs = "SELECT DISTINCT |
||
536 | scu.user_id as id_coach, |
||
537 | u.id as user_id, |
||
538 | lastname, |
||
539 | firstname, |
||
540 | MAX(login_date) as login_date |
||
541 | FROM $tbl_user u, |
||
542 | $tbl_session_course_user scu, |
||
543 | $tbl_track_login , |
||
544 | $tbl_session_rel_access_url session_rel_url |
||
545 | WHERE |
||
546 | scu.user_id = u.id AND |
||
547 | scu.status = 2 AND |
||
548 | login_user_id = u.id AND |
||
549 | access_url_id = $access_url_id AND |
||
550 | session_rel_url.session_id = scu.session_id |
||
551 | GROUP BY u.id"; |
||
552 | } |
||
553 | } |
||
554 | if (!empty($order[$tracking_column])) { |
||
555 | $sqlCoachs .= " ORDER BY `".$order[$tracking_column]."` ".$tracking_direction; |
||
556 | } |
||
557 | |||
558 | $result_coaches = Database::query($sqlCoachs); |
||
559 | $global_coaches = []; |
||
560 | while ($coach = Database::fetch_array($result_coaches)) { |
||
561 | $global_coaches[$coach['user_id']] = $coach; |
||
562 | } |
||
563 | |||
564 | $sql_session_coach = "SELECT session.id_coach, u.id as user_id, lastname, firstname, MAX(login_date) as login_date |
||
565 | FROM $tbl_user u , $tbl_sessions as session, $tbl_track_login |
||
566 | WHERE id_coach = u.id AND login_user_id = u.id |
||
567 | GROUP BY u.id |
||
568 | ORDER BY login_date $tracking_direction"; |
||
569 | |||
570 | if (api_is_multiple_url_enabled()) { |
||
571 | $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION); |
||
572 | $access_url_id = api_get_current_access_url_id(); |
||
573 | if ($access_url_id != -1) { |
||
574 | $sql_session_coach = "SELECT session.id_coach, u.id as user_id, lastname, firstname, MAX(login_date) as login_date |
||
575 | FROM $tbl_user u , $tbl_sessions as session, $tbl_track_login , $tbl_session_rel_access_url as session_rel_url |
||
576 | WHERE |
||
577 | id_coach = u.id AND |
||
578 | login_user_id = u.id AND |
||
579 | access_url_id = $access_url_id AND |
||
580 | session_rel_url.session_id = session.id |
||
581 | GROUP BY u.id |
||
582 | ORDER BY login_date $tracking_direction"; |
||
583 | } |
||
584 | } |
||
585 | |||
586 | $result_sessions_coach = Database::query($sql_session_coach); |
||
587 | //$total_no_coaches += Database::num_rows($result_sessions_coach); |
||
588 | while ($coach = Database::fetch_array($result_sessions_coach)) { |
||
589 | $global_coaches[$coach['user_id']] = $coach; |
||
590 | } |
||
591 | |||
592 | $all_datas = []; |
||
593 | foreach ($global_coaches as $id_coach => $coaches) { |
||
594 | $time_on_platform = api_time_to_hms( |
||
595 | Tracking::get_time_spent_on_the_platform($coaches['user_id']) |
||
596 | ); |
||
597 | $last_connection = Tracking::get_last_connection_date( |
||
598 | $coaches['user_id'] |
||
599 | ); |
||
600 | $nb_students = count( |
||
601 | Tracking::get_student_followed_by_coach($coaches['user_id']) |
||
602 | ); |
||
603 | $nb_courses = count( |
||
604 | Tracking::get_courses_followed_by_coach($coaches['user_id']) |
||
605 | ); |
||
606 | $nb_sessions = count( |
||
607 | Tracking::get_sessions_coached_by_user($coaches['user_id']) |
||
608 | ); |
||
609 | |||
610 | $table_row = []; |
||
611 | if ($is_western_name_order) { |
||
612 | $table_row[] = $coaches['firstname']; |
||
613 | $table_row[] = $coaches['lastname']; |
||
614 | } else { |
||
615 | $table_row[] = $coaches['lastname']; |
||
616 | $table_row[] = $coaches['firstname']; |
||
617 | } |
||
618 | $table_row[] = $time_on_platform; |
||
619 | $table_row[] = $last_connection; |
||
620 | $table_row[] = $nb_students; |
||
621 | $table_row[] = $nb_courses; |
||
622 | $table_row[] = $nb_sessions; |
||
623 | $table_row[] = '<a href="session.php?id_coach='.$coaches['user_id'].'"> |
||
624 | '.Display::return_icon('2rightarrow.png', get_lang('Details')).' |
||
625 | </a>'; |
||
626 | $all_datas[] = $table_row; |
||
627 | |||
628 | if ($is_western_name_order) { |
||
629 | $csv_content[] = [ |
||
630 | api_html_entity_decode($coaches['firstname'], ENT_QUOTES), |
||
631 | api_html_entity_decode($coaches['lastname'], ENT_QUOTES), |
||
632 | $time_on_platform, |
||
633 | $last_connection, |
||
634 | $nb_students, |
||
635 | $nb_courses, |
||
636 | $nb_sessions, |
||
637 | ]; |
||
638 | } else { |
||
639 | $csv_content[] = [ |
||
640 | api_html_entity_decode($coaches['lastname'], ENT_QUOTES), |
||
641 | api_html_entity_decode($coaches['firstname'], ENT_QUOTES), |
||
642 | $time_on_platform, |
||
643 | $last_connection, |
||
644 | $nb_students, |
||
645 | $nb_courses, |
||
646 | $nb_sessions, |
||
647 | ]; |
||
648 | } |
||
649 | } |
||
650 | |||
651 | if ($tracking_column != 3) { |
||
652 | if ($tracking_direction == 'DESC') { |
||
653 | usort($all_datas, ['MySpace', 'rsort_users']); |
||
654 | } else { |
||
655 | usort($all_datas, ['MySpace', 'sort_users']); |
||
656 | } |
||
657 | } |
||
658 | |||
659 | if ($export_csv && $tracking_column != 3) { |
||
660 | usort($csv_content, 'sort_users'); |
||
661 | } |
||
662 | if ($export_csv) { |
||
663 | $csv_content = array_merge($csv_header, $csv_content); |
||
664 | } |
||
665 | |||
666 | foreach ($all_datas as $row) { |
||
667 | $table->addRow($row, 'align="right"'); |
||
668 | } |
||
669 | $table->display(); |
||
670 | } |
||
671 | |||
672 | /** |
||
673 | * @return mixed |
||
674 | */ |
||
675 | public static function count_coaches() |
||
676 | { |
||
677 | global $total_no_coaches; |
||
678 | |||
679 | return $total_no_coaches; |
||
680 | } |
||
681 | |||
682 | public static function sort_users($a, $b) |
||
683 | { |
||
684 | $tracking = Session::read('tracking_column'); |
||
685 | |||
686 | return api_strcmp( |
||
687 | trim(api_strtolower($a[$tracking])), |
||
688 | trim(api_strtolower($b[$tracking])) |
||
689 | ); |
||
690 | } |
||
691 | |||
692 | public static function rsort_users($a, $b) |
||
693 | { |
||
694 | $tracking = Session::read('tracking_column'); |
||
695 | |||
696 | return api_strcmp( |
||
697 | trim(api_strtolower($b[$tracking])), |
||
698 | trim(api_strtolower($a[$tracking])) |
||
699 | ); |
||
700 | } |
||
701 | |||
702 | /** |
||
703 | * Display a sortable table that contains an overview off all the progress of the user in a session. |
||
704 | * |
||
705 | * @deprecated ? |
||
706 | * |
||
707 | * @author César Perales <[email protected]>, Beeznest Team |
||
708 | */ |
||
709 | public static function display_tracking_lp_progress_overview( |
||
710 | $sessionId = '', |
||
711 | $courseId = '', |
||
712 | $date_from, |
||
713 | $date_to |
||
714 | ) { |
||
715 | $course = api_get_course_info_by_id($courseId); |
||
716 | /** |
||
717 | * Column name |
||
718 | * The order is important you need to check the $column variable in the model.ajax.php file. |
||
719 | */ |
||
720 | $columns = [ |
||
721 | get_lang('Username'), |
||
722 | get_lang('FirstName'), |
||
723 | get_lang('LastName'), |
||
724 | ]; |
||
725 | //add lessons of course |
||
726 | $lessons = LearnpathList::get_course_lessons($course['code'], $sessionId); |
||
727 | |||
728 | //create columns array |
||
729 | foreach ($lessons as $lesson_id => $lesson) { |
||
730 | $columns[] = $lesson['name']; |
||
731 | } |
||
732 | |||
733 | $columns[] = get_lang('Total'); |
||
734 | |||
735 | /** |
||
736 | * Column config. |
||
737 | */ |
||
738 | $column_model = [ |
||
739 | [ |
||
740 | 'name' => 'username', |
||
741 | 'index' => 'username', |
||
742 | 'align' => 'left', |
||
743 | 'search' => 'true', |
||
744 | 'wrap_cell' => "true", |
||
745 | ], |
||
746 | [ |
||
747 | 'name' => 'firstname', |
||
748 | 'index' => 'firstname', |
||
749 | 'align' => 'left', |
||
750 | 'search' => 'true', |
||
751 | ], |
||
752 | [ |
||
753 | 'name' => 'lastname', |
||
754 | 'index' => 'lastname', |
||
755 | 'align' => 'left', |
||
756 | 'search' => 'true', |
||
757 | ], |
||
758 | ]; |
||
759 | |||
760 | // Get dinamic column names |
||
761 | foreach ($lessons as $lesson_id => $lesson) { |
||
762 | $column_model[] = [ |
||
763 | 'name' => $lesson['id'], |
||
764 | 'index' => $lesson['id'], |
||
765 | 'align' => 'left', |
||
766 | 'search' => 'true', |
||
767 | ]; |
||
768 | } |
||
769 | |||
770 | $column_model[] = [ |
||
771 | 'name' => 'total', |
||
772 | 'index' => 'total', |
||
773 | 'align' => 'left', |
||
774 | 'search' => 'true', |
||
775 | ]; |
||
776 | |||
777 | $action_links = ''; |
||
778 | // jqgrid will use this URL to do the selects |
||
779 | $url = api_get_path(WEB_AJAX_PATH).'model.ajax.php?a=get_session_lp_progress&session_id='.$sessionId.'&course_id='.$courseId.'&date_to='.$date_to.'&date_from='.$date_from; |
||
780 | |||
781 | // Table Id |
||
782 | $tableId = 'lpProgress'; |
||
783 | |||
784 | // Autowidth |
||
785 | $extra_params['autowidth'] = 'true'; |
||
786 | |||
787 | // height auto |
||
788 | $extra_params['height'] = 'auto'; |
||
789 | |||
790 | $table = Display::grid_js( |
||
791 | $tableId, |
||
792 | $url, |
||
793 | $columns, |
||
794 | $column_model, |
||
795 | $extra_params, |
||
796 | [], |
||
797 | $action_links, |
||
798 | true |
||
799 | ); |
||
800 | |||
801 | $return = '<script>$(function() {'.$table. |
||
802 | 'jQuery("#'.$tableId.'").jqGrid("navGrid","#'.$tableId.'_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true}); |
||
803 | jQuery("#'.$tableId.'").jqGrid("navButtonAdd","#'.$tableId.'_pager",{ |
||
804 | caption:"", |
||
805 | title:"'.get_lang('ExportExcel').'", |
||
806 | onClickButton : function () { |
||
807 | jQuery("#'.$tableId.'").jqGrid("excelExport",{"url":"'.$url.'&export_format=xls"}); |
||
808 | } |
||
809 | }); |
||
810 | });</script>'; |
||
811 | $return .= Display::grid_html($tableId); |
||
812 | |||
813 | return $return; |
||
814 | } |
||
815 | |||
816 | /** |
||
817 | * Display a sortable table that contains an overview off all the progress of the user in a session. |
||
818 | * |
||
819 | * @param int $sessionId The session ID |
||
820 | * @param int $courseId The course ID |
||
821 | * @param int $exerciseId The quiz ID |
||
822 | * @param $date_from |
||
823 | * @param $date_to |
||
824 | * |
||
825 | * @return string HTML array of results formatted for gridJS |
||
826 | * |
||
827 | * @deprecated ? |
||
828 | * |
||
829 | * @author César Perales <[email protected]>, Beeznest Team |
||
830 | */ |
||
831 | public static function display_tracking_exercise_progress_overview( |
||
832 | $sessionId = 0, |
||
833 | $courseId = 0, |
||
834 | $exerciseId = 0, |
||
835 | $date_from = null, |
||
836 | $date_to = null |
||
837 | ) { |
||
838 | $date_from = Security::remove_XSS($date_from); |
||
839 | $date_to = Security::remove_XSS($date_to); |
||
840 | /** |
||
841 | * Column names |
||
842 | * The column order is important. Check $column variable in the main/inc/ajax/model.ajax.php file. |
||
843 | */ |
||
844 | $columns = [ |
||
845 | get_lang('Session'), |
||
846 | get_lang('ExerciseId'), |
||
847 | get_lang('ExerciseName'), |
||
848 | get_lang('Username'), |
||
849 | get_lang('LastName'), |
||
850 | get_lang('FirstName'), |
||
851 | get_lang('Time'), |
||
852 | get_lang('QuestionId'), |
||
853 | get_lang('QuestionTitle'), |
||
854 | get_lang('WorkDescription'), |
||
855 | get_lang('Answer'), |
||
856 | get_lang('Correct'), |
||
857 | ]; |
||
858 | |||
859 | /** |
||
860 | * Column config. |
||
861 | */ |
||
862 | $column_model = [ |
||
863 | ['name' => 'session', 'index' => 'session', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"], |
||
864 | ['name' => 'exercise_id', 'index' => 'exercise_id', 'align' => 'left', 'search' => 'true'], |
||
865 | ['name' => 'quiz_title', 'index' => 'quiz_title', 'align' => 'left', 'search' => 'true'], |
||
866 | ['name' => 'username', 'index' => 'username', 'align' => 'left', 'search' => 'true'], |
||
867 | ['name' => 'lastname', 'index' => 'lastname', 'align' => 'left', 'search' => 'true'], |
||
868 | ['name' => 'firstname', 'index' => 'firstname', 'align' => 'left', 'search' => 'true'], |
||
869 | ['name' => 'time', 'index' => 'time', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"], |
||
870 | ['name' => 'question_id', 'index' => 'question_id', 'align' => 'left', 'search' => 'true'], |
||
871 | ['name' => 'question', 'index' => 'question', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"], |
||
872 | ['name' => 'description', 'index' => 'description', 'align' => 'left', 'width' => '550', 'search' => 'true', 'wrap_cell' => "true"], |
||
873 | ['name' => 'answer', 'index' => 'answer', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"], |
||
874 | ['name' => 'correct', 'index' => 'correct', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"], |
||
875 | ]; |
||
876 | //get dynamic column names |
||
877 | |||
878 | // jqgrid will use this URL to do the selects |
||
879 | $url = api_get_path(WEB_AJAX_PATH).'model.ajax.php?a=get_exercise_progress&session_id='.$sessionId.'&course_id='.$courseId.'&exercise_id='.$exerciseId.'&date_to='.$date_to.'&date_from='.$date_from; |
||
880 | |||
881 | // Autowidth |
||
882 | $extra_params['autowidth'] = 'true'; |
||
883 | |||
884 | // height auto |
||
885 | $extra_params['height'] = 'auto'; |
||
886 | |||
887 | $tableId = 'exerciseProgressOverview'; |
||
888 | $table = Display::grid_js( |
||
889 | $tableId, |
||
890 | $url, |
||
891 | $columns, |
||
892 | $column_model, |
||
893 | $extra_params, |
||
894 | [], |
||
895 | '', |
||
896 | true |
||
897 | ); |
||
898 | |||
899 | $return = '<script>$(function() {'.$table. |
||
900 | 'jQuery("#'.$tableId.'").jqGrid("navGrid","#'.$tableId.'_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true}); |
||
901 | jQuery("#'.$tableId.'").jqGrid("navButtonAdd","#'.$tableId.'_pager",{ |
||
902 | caption:"", |
||
903 | title:"'.get_lang('ExportExcel').'", |
||
904 | onClickButton : function () { |
||
905 | jQuery("#'.$tableId.'").jqGrid("excelExport",{"url":"'.$url.'&export_format=xls"}); |
||
906 | } |
||
907 | }); |
||
908 | });</script>'; |
||
909 | $return .= Display::grid_html($tableId); |
||
910 | |||
911 | return $return; |
||
912 | } |
||
913 | |||
914 | /** |
||
915 | * Displays a form with all the additionally defined user fields of the profile |
||
916 | * and give you the opportunity to include these in the CSV export. |
||
917 | * |
||
918 | * @author Patrick Cool <[email protected]>, Ghent University, Belgium |
||
919 | * |
||
920 | * @version 1.8.6 |
||
921 | * |
||
922 | * @since November 2008 |
||
923 | */ |
||
924 | public static function display_user_overview_export_options() |
||
925 | { |
||
926 | $message = ''; |
||
927 | $defaults = []; |
||
928 | // include the user manager and formvalidator library |
||
929 | if (isset($_GET['export']) && 'options' == $_GET['export']) { |
||
930 | // get all the defined extra fields |
||
931 | $extrafields = UserManager::get_extra_fields( |
||
932 | 0, |
||
933 | 50, |
||
934 | 5, |
||
935 | 'ASC', |
||
936 | false, |
||
937 | 1 |
||
938 | ); |
||
939 | |||
940 | // creating the form with all the defined extra fields |
||
941 | $form = new FormValidator( |
||
942 | 'exportextrafields', |
||
943 | 'post', |
||
944 | api_get_self()."?view=".Security::remove_XSS($_GET['view']).'&display='.Security::remove_XSS($_GET['display']).'&export='.Security::remove_XSS($_GET['export']) |
||
945 | ); |
||
946 | |||
947 | if (is_array($extrafields) && count($extrafields) > 0) { |
||
948 | foreach ($extrafields as $key => $extra) { |
||
949 | $form->addElement('checkbox', 'extra_export_field'.$extra[0], '', $extra[3]); |
||
950 | } |
||
951 | $form->addButtonSave(get_lang('Ok'), 'submit'); |
||
952 | |||
953 | // setting the default values for the form that contains all the extra fields |
||
954 | $exportFields = Session::read('additional_export_fields'); |
||
955 | if (is_array($exportFields)) { |
||
956 | foreach ($exportFields as $key => $value) { |
||
957 | $defaults['extra_export_field'.$value] = 1; |
||
958 | } |
||
959 | } |
||
960 | $form->setDefaults($defaults); |
||
961 | } else { |
||
962 | $form->addElement('html', Display::return_message(get_lang('ThereAreNotExtrafieldsAvailable'), 'warning')); |
||
963 | } |
||
964 | |||
965 | if ($form->validate()) { |
||
966 | // exporting the form values |
||
967 | $values = $form->exportValues(); |
||
968 | |||
969 | // re-initialising the session that contains the additional fields that need to be exported |
||
970 | Session::write('additional_export_fields', []); |
||
971 | |||
972 | // adding the fields that are checked to the session |
||
973 | $message = ''; |
||
974 | $additionalExportFields = []; |
||
975 | foreach ($values as $field_ids => $value) { |
||
976 | if ($value == 1 && strstr($field_ids, 'extra_export_field')) { |
||
977 | $additionalExportFields[] = str_replace('extra_export_field', '', $field_ids); |
||
978 | } |
||
979 | } |
||
980 | Session::write('additional_export_fields', $additionalExportFields); |
||
981 | |||
982 | // adding the fields that will be also exported to a message string |
||
983 | $additionalExportFields = Session::read('additional_export_fields'); |
||
984 | if (is_array($additionalExportFields)) { |
||
985 | foreach ($additionalExportFields as $key => $extra_field_export) { |
||
986 | $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>'; |
||
987 | } |
||
988 | } |
||
989 | |||
990 | // Displaying a feedback message |
||
991 | if (!empty($additionalExportFields)) { |
||
992 | echo Display::return_message( |
||
993 | get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', |
||
994 | 'confirm', |
||
995 | false |
||
996 | ); |
||
997 | } else { |
||
998 | echo Display::return_message( |
||
999 | get_lang('NoAdditionalFieldsWillBeExported'), |
||
1000 | 'confirm', |
||
1001 | false |
||
1002 | ); |
||
1003 | } |
||
1004 | } else { |
||
1005 | $form->display(); |
||
1006 | } |
||
1007 | } else { |
||
1008 | $additionalExportFields = Session::read('additional_export_fields'); |
||
1009 | if (!empty($additionalExportFields)) { |
||
1010 | // get all the defined extra fields |
||
1011 | $extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC'); |
||
1012 | |||
1013 | foreach ($additionalExportFields as $key => $extra_field_export) { |
||
1014 | $message .= '<li>'.$extrafields[$extra_field_export][3].'</li>'; |
||
1015 | } |
||
1016 | |||
1017 | echo Display::return_message( |
||
1018 | get_lang('FollowingFieldsWillAlsoBeExported').': <br /><ul>'.$message.'</ul>', |
||
1019 | 'normal', |
||
1020 | false |
||
1021 | ); |
||
1022 | } |
||
1023 | } |
||
1024 | } |
||
1025 | |||
1026 | /** |
||
1027 | * Export to cvs a list of users who were enrolled in the lessons. |
||
1028 | * It is necessary that in the extra field, a company is defined. |
||
1029 | * |
||
1030 | * @param string|null $startDate |
||
1031 | * @param string|null $endDate |
||
1032 | * |
||
1033 | * @return array |
||
1034 | */ |
||
1035 | public static function exportCompanyResumeCsv($startDate, $endDate) |
||
1036 | { |
||
1037 | $companys = self::getCompanyLearnpathSubscription($startDate, $endDate); |
||
1038 | $csv_content = []; |
||
1039 | // Printing table |
||
1040 | $total = 0; |
||
1041 | $displayText = get_lang('Company'); |
||
1042 | // the first line of the csv file with the column headers |
||
1043 | $csv_row = []; |
||
1044 | $csv_row[] = $displayText; |
||
1045 | |||
1046 | $csv_row[] = get_lang('CountOfSubscribedUsers'); |
||
1047 | $csv_content[] = $csv_row; |
||
1048 | |||
1049 | foreach ($companys as $entity => $student) { |
||
1050 | $csv_row = []; |
||
1051 | // user official code |
||
1052 | $csv_row[] = $entity; |
||
1053 | $csv_row[] = count($student); |
||
1054 | $total += count($student); |
||
1055 | $csv_content[] = $csv_row; |
||
1056 | } |
||
1057 | |||
1058 | $csv_row = []; |
||
1059 | // user official code |
||
1060 | $csv_row[] = get_lang('GeneralTotal'); |
||
1061 | $csv_row[] = $total; |
||
1062 | $csv_content[] = $csv_row; |
||
1063 | Export::arrayToCsv($csv_content, 'reporting_company_resume'); |
||
1064 | exit; |
||
1065 | } |
||
1066 | |||
1067 | /** |
||
1068 | * Generates a structure to show the links or names for the authors by lesson report. |
||
1069 | * |
||
1070 | * @param array $students |
||
1071 | * @param array $studentRegistered |
||
1072 | * @param $lpCourseCode |
||
1073 | */ |
||
1074 | public static function getStudentDataToReportByLp($students = [], $studentRegistered = [], $lpCourseCode) |
||
1075 | { |
||
1076 | $data = []; |
||
1077 | $totalStudents = 0; |
||
1078 | $data['csv'] = ''; |
||
1079 | $data['html'] = ''; |
||
1080 | $icon = Display::return_icon('statistics.png', get_lang('Stats')); |
||
1081 | foreach ($students as $student) { |
||
1082 | $lpSessionId = isset($student['session_id']) ? (int) $student['session_id'] : 0; |
||
1083 | $studentId = (int) $student['id']; |
||
1084 | if (!isset($studentRegistered[$studentId][$lpSessionId])) { |
||
1085 | $url = api_get_path(WEB_CODE_PATH)."mySpace/myStudents.php?details=true&student=$studentId"; |
||
1086 | if (0 != $lpSessionId) { |
||
1087 | $url .= "&id_session=$lpSessionId"; |
||
1088 | } |
||
1089 | $url .= "&course=$lpCourseCode"; |
||
1090 | $reportLink = Display::url( |
||
1091 | $icon, |
||
1092 | $url |
||
1093 | ); |
||
1094 | $studentName = $student['complete_name']."(".$student['company'].")"; |
||
1095 | $studentRegistered[$studentId][$lpSessionId] = $student; |
||
1096 | $data['csv'] .= $studentName.' / '; |
||
1097 | $data['html'] .= "$reportLink <strong>$studentName</strong><br>"; |
||
1098 | $totalStudents++; |
||
1099 | } |
||
1100 | } |
||
1101 | $data['student_registered'] = $studentRegistered; |
||
1102 | $data['total_students'] = $totalStudents; |
||
1103 | |||
1104 | return $data; |
||
1105 | } |
||
1106 | |||
1107 | /** |
||
1108 | * * Generates a structure to show the names for the authors by lesson report by item. |
||
1109 | * |
||
1110 | * @param array $students |
||
1111 | * @param array $studentProcessed |
||
1112 | * @param string $typeReport |
||
1113 | * @param false $csv |
||
1114 | */ |
||
1115 | public static function getStudentDataToReportByLpItem($students = [], $studentProcessed = [], $typeReport = '', $csv = false) |
||
1116 | { |
||
1117 | $totalStudent = count($students); |
||
1118 | $sessionIcon = Display::return_icon( |
||
1119 | 'admin_star.png', |
||
1120 | get_lang('StudentInSessionCourse'), |
||
1121 | [], |
||
1122 | ICON_SIZE_MEDIUM |
||
1123 | ); |
||
1124 | $classIcon = Display::return_icon( |
||
1125 | 'group_summary.png', |
||
1126 | get_lang('UsersInsideClass'), |
||
1127 | '', |
||
1128 | ICON_SIZE_MEDIUM |
||
1129 | ); |
||
1130 | /* use 'for' to performance */ |
||
1131 | for ($i = 0; $i < $totalStudent; $i++) { |
||
1132 | $student = $students[$i]; |
||
1133 | $studentId = $student['id']; |
||
1134 | $lpItemIdStudent = $student['lp_item_id']; |
||
1135 | $sessionId = isset($student['session_id']) ? (int) $student['session_id'] : 0; |
||
1136 | $studentName = $student['complete_name']; |
||
1137 | $studentCompany = $student['company']; |
||
1138 | $studentName = "$studentName($studentCompany)"; |
||
1139 | $type = isset($student['type']) ? $student['type'] : null; |
||
1140 | $icon = null; |
||
1141 | if (0 != $sessionId) { |
||
1142 | $icon = $sessionIcon; |
||
1143 | } |
||
1144 | if ('class' == $typeReport) { |
||
1145 | $icon = $classIcon; |
||
1146 | } |
||
1147 | $studentString = "$icon $studentName"; |
||
1148 | if (0 != $sessionId) { |
||
1149 | $studentString = "<strong>$studentString</strong>"; |
||
1150 | } |
||
1151 | if ($csv == false) { |
||
1152 | $studentProcessed[$lpItemIdStudent][$type][$studentId] = $studentString.'<br>'; |
||
1153 | } else { |
||
1154 | $studentProcessed[$lpItemIdStudent][$type][$studentId] = "$studentName / "; |
||
1155 | } |
||
1156 | } |
||
1157 | |||
1158 | return $studentProcessed; |
||
1159 | } |
||
1160 | |||
1161 | /** |
||
1162 | * Displays a list as a table of users who were enrolled in the lessons. |
||
1163 | * It is necessary that in the extra field, a company is defined. |
||
1164 | * |
||
1165 | * @param string|null $startDate |
||
1166 | * @param string|null $endDate |
||
1167 | */ |
||
1168 | public static function displayResumeCompany( |
||
1169 | $startDate = null, |
||
1170 | $endDate = null |
||
1171 | ) { |
||
1172 | $companys = self::getCompanyLearnpathSubscription($startDate, $endDate); |
||
1173 | $tableHtml = ''; |
||
1174 | // Printing table |
||
1175 | $total = 0; |
||
1176 | $table = "<div class='table-responsive'><table class='table table-hover table-striped table-bordered data_table'>"; |
||
1177 | |||
1178 | $displayText = get_lang('Company'); |
||
1179 | $table .= "<thead><tr><th class='th-header'>$displayText</th><th class='th-header'> ".get_lang('CountOfSubscribedUsers')." </th></tr></thead><tbody>"; |
||
1180 | |||
1181 | foreach ($companys as $entity => $student) { |
||
1182 | $table .= "<tr><td>$entity</td><td>".count($student)."</td></tr>"; |
||
1183 | $total += count($student); |
||
1184 | } |
||
1185 | $table .= "<tr><td>".get_lang('GeneralTotal')."</td><td>$total</td></tr>"; |
||
1186 | $table .= '</tbody></table></div>'; |
||
1187 | |||
1188 | if (!empty($startDate) or !empty($endDate)) { |
||
1189 | $tableHtml = $table; |
||
1190 | } |
||
1191 | |||
1192 | $form = new FormValidator('searchDate', 'get'); |
||
1193 | $form->addHidden('display', 'company'); |
||
1194 | $today = new DateTime(); |
||
1195 | if (empty($startDate)) { |
||
1196 | $startDate = api_get_local_time($today->modify('first day of this month')->format('Y-m-d')); |
||
1197 | } |
||
1198 | if (empty($endDate)) { |
||
1199 | $endDate = api_get_local_time($today->modify('last day of this month')->format('Y-m-d')); |
||
1200 | } |
||
1201 | $form->addDatePicker( |
||
1202 | 'startDate', |
||
1203 | get_lang('DateStart'), |
||
1204 | [ |
||
1205 | 'value' => $startDate, |
||
1206 | ]); |
||
1207 | $form->addDatePicker( |
||
1208 | 'endDate', |
||
1209 | get_lang('DateEnd'), |
||
1210 | [ |
||
1211 | 'value' => $endDate, |
||
1212 | ]); |
||
1213 | $form->addButtonSearch(get_lang('Search')); |
||
1214 | if (count($companys) != 0) { |
||
1215 | //$form->addButtonSave(get_lang('Ok'), 'export'); |
||
1216 | $form |
||
1217 | ->addButton( |
||
1218 | 'export_csv', |
||
1219 | get_lang('ExportAsCSV'), |
||
1220 | 'check', |
||
1221 | 'primary', |
||
1222 | null, |
||
1223 | null, |
||
1224 | [ |
||
1225 | ] |
||
1226 | ); |
||
1227 | } |
||
1228 | |||
1229 | $tableContent = $form->returnForm(); |
||
1230 | $tableContent .= $tableHtml; |
||
1231 | // $tableContent .= $table->return_table(); |
||
1232 | |||
1233 | $tpl = new Template('', false, false, false, false, false, false); |
||
1234 | $tpl->assign('table', $tableContent); |
||
1235 | $templateName = $tpl->get_template('my_space/course_summary.tpl'); |
||
1236 | $tpl->display($templateName); |
||
1237 | } |
||
1238 | |||
1239 | /** |
||
1240 | * Displays a list as a table of teachers who are set authors by a extra_field authors. |
||
1241 | * |
||
1242 | * @param string|null $startDate |
||
1243 | * @param string|null $endDate |
||
1244 | * @param bool $csv |
||
1245 | */ |
||
1246 | public static function displayResumeLP( |
||
1247 | $startDate = null, |
||
1248 | $endDate = null, |
||
1249 | $csv = false |
||
1250 | ) { |
||
1251 | $tblExtraField = Database::get_main_table(TABLE_EXTRA_FIELD); |
||
1252 | $tblCourse = Database::get_main_table(TABLE_MAIN_COURSE); |
||
1253 | $tblExtraFieldValue = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES); |
||
1254 | $tblLpItem = Database::get_course_table(TABLE_LP_ITEM); |
||
1255 | $tblLp = Database::get_course_table(TABLE_LP_MAIN); |
||
1256 | $tblAccessUrlCourse = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); |
||
1257 | $accessUrlFilter = ''; |
||
1258 | if (api_is_multiple_url_enabled()) { |
||
1259 | $urlId = api_get_current_access_url_id(); |
||
1260 | $accessUrlFilter = " INNER JOIN $tblAccessUrlCourse aurc |
||
1261 | ON (c.id = aurc.c_id AND aurc.access_url_id = $urlId)"; |
||
1262 | } |
||
1263 | $query = " |
||
1264 | SELECT DISTINCT |
||
1265 | lp.name, |
||
1266 | lpi.title, |
||
1267 | lp.id as lp_id, |
||
1268 | lpi.id AS lp_item_id, |
||
1269 | REPLACE (efv.value, ';', ',') AS users_id, |
||
1270 | c.title AS course_title, |
||
1271 | c.code AS course_code |
||
1272 | FROM $tblExtraFieldValue AS efv |
||
1273 | INNER JOIN $tblExtraField AS ef |
||
1274 | ON ( |
||
1275 | efv.field_id = ef.id AND |
||
1276 | ef.variable = 'authorlpitem' AND |
||
1277 | efv.value != '' |
||
1278 | ) |
||
1279 | INNER JOIN $tblLpItem AS lpi |
||
1280 | ON (efv.item_id = lpi.iid) |
||
1281 | INNER JOIN $tblLp AS lp |
||
1282 | ON (lpi.lp_id = lp.iid AND lpi.c_id = lp.c_id) |
||
1283 | INNER JOIN $tblCourse AS c |
||
1284 | ON (lp.c_id = c.id) |
||
1285 | $accessUrlFilter"; |
||
1286 | $queryResult = Database::query($query); |
||
1287 | $dataTeachers = Database::store_result($queryResult, 'ASSOC'); |
||
1288 | $totalData = count($dataTeachers); |
||
1289 | $lpItems = []; |
||
1290 | $teachers = []; |
||
1291 | $users = []; |
||
1292 | $learningPaths = []; |
||
1293 | $csvContent = []; |
||
1294 | $htmlData = ''; |
||
1295 | /* use 'for' to performance */ |
||
1296 | for ($i = 0; $i < $totalData; $i++) { |
||
1297 | $row = $dataTeachers[$i]; |
||
1298 | $lpId = $row['lp_id']; |
||
1299 | $lpItems[] = $lpId; |
||
1300 | $authorData = $row['users_id']; |
||
1301 | $learningPaths[$lpId] = $row; |
||
1302 | if (strpos($authorData, ",") === false) { |
||
1303 | if (!isset($users[$authorData])) { |
||
1304 | $users[$authorData] = api_get_user_info($authorData); |
||
1305 | } |
||
1306 | $teachers[$authorData][$lpId] = $users[$authorData]; |
||
1307 | $learningPaths[$lpId]['teachers'][$authorData] = $users[$authorData]; |
||
1308 | } else { |
||
1309 | $items = explode(',', $authorData); |
||
1310 | $totalItems = count($items); |
||
1311 | for ($j = 0; $j < $totalItems; $j++) { |
||
1312 | $authorData = $items[$j]; |
||
1313 | if (!isset($users[$authorData])) { |
||
1314 | $users[$authorData] = api_get_user_info($authorData); |
||
1315 | } |
||
1316 | $teachers[$authorData][$lpId] = $users[$authorData]; |
||
1317 | $learningPaths[$lpId]['teachers'][$authorData] = $users[$authorData]; |
||
1318 | } |
||
1319 | } |
||
1320 | } |
||
1321 | $lpItems = array_unique($lpItems); |
||
1322 | $whereInLp = implode(',', $lpItems); |
||
1323 | if (count($lpItems) != 0) { |
||
1324 | $registeredUsers = self::getCompanyLearnpathSubscription( |
||
1325 | $startDate, |
||
1326 | $endDate, |
||
1327 | $whereInLp |
||
1328 | ); |
||
1329 | foreach ($registeredUsers as $students) { |
||
1330 | $totalStudents = count($students); |
||
1331 | /* use 'for' to performance */ |
||
1332 | for ($i = 0; $i < $totalStudents; $i++) { |
||
1333 | $user = $students[$i]; |
||
1334 | $lpId = $user['lp_item']; |
||
1335 | $studentId = $user['id']; |
||
1336 | $learningPaths[$lpId]['courseStudent'][$studentId] = $user; |
||
1337 | } |
||
1338 | } |
||
1339 | $registeredUsersBySession = self::getSessionAddUserCourseFromTrackDefault( |
||
1340 | $startDate, |
||
1341 | $endDate, |
||
1342 | $whereInLp |
||
1343 | ); |
||
1344 | foreach ($registeredUsersBySession as $lpId => $student) { |
||
1345 | $totalStudents = count($student); |
||
1346 | /* use 'for' to performance */ |
||
1347 | for ($i = 0; $i < $totalStudents; $i++) { |
||
1348 | $user = $student[$i]; |
||
1349 | $lpId = $user['lp']; |
||
1350 | $studentId = $user['id']; |
||
1351 | $learningPaths[$lpId]['sessionStudent'][$studentId] = $user; |
||
1352 | $learningPaths[$lpId]['sessionStudent'][$studentId]['session_id'] = $user; |
||
1353 | } |
||
1354 | } |
||
1355 | $registeredUsersGroup = self::getCompanyLearnpathSubscription( |
||
1356 | $startDate, |
||
1357 | $endDate, |
||
1358 | $whereInLp, |
||
1359 | true |
||
1360 | ); |
||
1361 | foreach ($registeredUsersGroup as $student) { |
||
1362 | $totalStudents = count($student); |
||
1363 | /* use 'for' to performance */ |
||
1364 | for ($i = 0; $i < $totalStudents; $i++) { |
||
1365 | $user = $student[$i]; |
||
1366 | $lpId = $user['lp_item']; |
||
1367 | $studentId = $user['id']; |
||
1368 | $learningPaths[$lpId]['courseStudentGroup'][$studentId] = $user; |
||
1369 | } |
||
1370 | } |
||
1371 | |||
1372 | $index = 0; |
||
1373 | $iconAdd = Display::return_icon('add.png', get_lang('ShowOrHide'), '', ICON_SIZE_SMALL); |
||
1374 | $iconRemove = Display::return_icon('error.png', get_lang('ShowOrHide'), '', ICON_SIZE_SMALL); |
||
1375 | $htmlData = "<div class='table-responsive'> |
||
1376 | <table class='table table-hover table-striped table-bordered data_table'> |
||
1377 | <thead> |
||
1378 | <tr> |
||
1379 | <th class='th-header'>".get_lang('Author')."</th> |
||
1380 | <th class='th-header'>".get_lang('LearningPathList')."</th> |
||
1381 | <th class='th-header'>".get_lang('CountOfSubscribedUsers')."</th> |
||
1382 | <th class='th-header'>".get_lang('StudentList')."</th> |
||
1383 | </tr> |
||
1384 | </thead> |
||
1385 | <tbody>"; |
||
1386 | $lastTeacher = ''; |
||
1387 | /* csv */ |
||
1388 | $csv_row = []; |
||
1389 | $csv_row[] = get_lang('Author'); |
||
1390 | $csv_row[] = get_lang('LearningPathList'); |
||
1391 | $csv_row[] = get_lang('CountOfSubscribedUsers'); |
||
1392 | $csv_row[] = get_lang('StudentList'); |
||
1393 | $csvContent[] = $csv_row; |
||
1394 | $studentsName = ''; |
||
1395 | /* csv */ |
||
1396 | foreach ($teachers as $authorLId => $teacher) { |
||
1397 | $totalStudents = 0; |
||
1398 | foreach ($teacher as $lpId => $teacherData) { |
||
1399 | $lpSessionId = 0; |
||
1400 | $lpData = $learningPaths[$lpId]; |
||
1401 | $printTeacherName = ($lastTeacher != $teacherData['complete_name']) ? $teacherData['complete_name'] : ''; |
||
1402 | $htmlData .= "<tr><td>$printTeacherName</td>"; |
||
1403 | $hiddenField = 'student_show_'.$index; |
||
1404 | $hiddenFieldLink = 'student_show_'.$index.'_'; |
||
1405 | $lpCourseCode = $lpData['course_code']; |
||
1406 | $lpName = $lpData['name']; |
||
1407 | $courseStudent = isset($lpData['courseStudent']) ? $lpData['courseStudent'] : []; |
||
1408 | $courseStudentGroup = isset($lpData['courseStudentGroup']) ? $lpData['courseStudentGroup'] : []; |
||
1409 | $sessionStudent = isset($lpData['sessionStudent']) ? $lpData['sessionStudent'] : []; |
||
1410 | $htmlData .= "<td>$lpName</td><td>".count($courseStudent)." ( ".count($sessionStudent)." )</td><td>"; |
||
1411 | $csv_row = []; |
||
1412 | $csv_row[] = $printTeacherName; |
||
1413 | $csv_row[] = $lpName; |
||
1414 | $csv_row[] = count($courseStudent).' ( '.count($sessionStudent)." )"; |
||
1415 | if (!empty($courseStudent) |
||
1416 | || !empty($courseStudentGroup) |
||
1417 | || !empty($sessionStudent) |
||
1418 | ) { |
||
1419 | $htmlData .= "<a href='#!' id='$hiddenFieldLink' onclick='showHideStudent(\"$hiddenField\")'> |
||
1420 | <div class='icon_add'>$iconAdd</div> |
||
1421 | <div class='icon_remove hidden'>$iconRemove</div> |
||
1422 | </a> |
||
1423 | <div id='$hiddenField' class='hidden'>"; |
||
1424 | $studentRegistered = []; |
||
1425 | |||
1426 | $tempArray = self::getStudentDataToReportByLp($courseStudent, $studentRegistered, $lpCourseCode); |
||
1427 | $studentsName .= $tempArray['csv']; |
||
1428 | $htmlData .= $tempArray['html']; |
||
1429 | $studentRegistered = $tempArray['student_registered']; |
||
1430 | $totalStudents += $tempArray['total_students']; |
||
1431 | |||
1432 | $tempArray = self::getStudentDataToReportByLp($sessionStudent, $studentRegistered, $lpCourseCode); |
||
1433 | $studentsName .= $tempArray['csv']; |
||
1434 | $htmlData .= $tempArray['html']; |
||
1435 | $studentRegistered = $tempArray['student_registered']; |
||
1436 | $totalStudents += $tempArray['total_students']; |
||
1437 | |||
1438 | $tempArray = self::getStudentDataToReportByLp($courseStudentGroup, $studentRegistered, $lpCourseCode); |
||
1439 | $studentsName .= $tempArray['csv']; |
||
1440 | $htmlData .= $tempArray['html']; |
||
1441 | $studentRegistered = $tempArray['student_registered']; |
||
1442 | $totalStudents += $tempArray['total_students']; |
||
1443 | |||
1444 | $htmlData .= "</div>"; |
||
1445 | } |
||
1446 | $htmlData .= "</td></tr>"; |
||
1447 | $index++; |
||
1448 | $csv_row[] = trim($studentsName, ' / '); |
||
1449 | $studentsName = ''; |
||
1450 | $csvContent[] = $csv_row; |
||
1451 | $lastTeacher = $teacherData['complete_name']; |
||
1452 | } |
||
1453 | $htmlData .= "<tr> |
||
1454 | <td></td> |
||
1455 | <td><strong>".get_lang('LearnpathsTotal')." ".count($teacher)." </strong></td> |
||
1456 | <td><strong>$totalStudents</strong></td> |
||
1457 | <td></td> |
||
1458 | </tr>"; |
||
1459 | } |
||
1460 | $htmlData .= "</tbody> |
||
1461 | </table> |
||
1462 | </div>"; |
||
1463 | } |
||
1464 | if (false == $csv) { |
||
1465 | $form = new FormValidator('searchDate', 'get'); |
||
1466 | $form->addHidden('display', 'learningPath'); |
||
1467 | $today = new DateTime(); |
||
1468 | if (empty($startDate)) { |
||
1469 | $startDate = $today->modify('first day of this month')->format('Y-m-d'); |
||
1470 | } |
||
1471 | if (empty($endDate)) { |
||
1472 | $endDate = $today->modify('last day of this month')->format('Y-m-d'); |
||
1473 | } |
||
1474 | $form->addDatePicker( |
||
1475 | 'startDate', |
||
1476 | get_lang('DateStart'), |
||
1477 | [ |
||
1478 | 'value' => $startDate, |
||
1479 | ]); |
||
1480 | $form->addDatePicker( |
||
1481 | 'endDate', |
||
1482 | get_lang('DateEnd'), |
||
1483 | [ |
||
1484 | 'value' => $endDate, |
||
1485 | ]); |
||
1486 | $form->addButtonSearch(get_lang('Search')); |
||
1487 | if (0 != count($csvContent)) { |
||
1488 | $form |
||
1489 | ->addButton( |
||
1490 | 'export_csv', |
||
1491 | get_lang('ExportAsCSV'), |
||
1492 | 'check', |
||
1493 | 'primary', |
||
1494 | null, |
||
1495 | null, |
||
1496 | [ |
||
1497 | ] |
||
1498 | ); |
||
1499 | } |
||
1500 | $tableContent = $form->returnForm(); |
||
1501 | if (!empty($startDate) || !empty($endDate)) { |
||
1502 | $tableContent .= $htmlData; |
||
1503 | } |
||
1504 | $tpl = new Template('', false, false, false, false, false, false); |
||
1505 | $tpl->assign('table', $tableContent); |
||
1506 | $templateName = $tpl->get_template('my_space/course_summary.tpl'); |
||
1507 | $tpl->display($templateName); |
||
1508 | } else { |
||
1509 | if (count($csvContent) != 0) { |
||
1510 | Export::arrayToCsv($csvContent, 'reporting_lp_by_authors'); |
||
1511 | } |
||
1512 | } |
||
1513 | } |
||
1514 | |||
1515 | /** |
||
1516 | * Displays a list as a table of teachers who are set authors of lp's item by a extra_field authors. |
||
1517 | */ |
||
1518 | public static function displayResumeLpByItem(string $startDate = null, string $endDate = null, bool $csv = false) |
||
1519 | { |
||
1520 | $tableHtml = ''; |
||
1521 | $table = ''; |
||
1522 | $tblExtraField = Database::get_main_table(TABLE_EXTRA_FIELD); |
||
1523 | $tblExtraFieldValue = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES); |
||
1524 | $tblLpItem = Database::get_course_table(TABLE_LP_ITEM); |
||
1525 | $tblLp = Database::get_course_table(TABLE_LP_MAIN); |
||
1526 | $tblAccessUrlCourse = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); |
||
1527 | $accessUrlFilter = ''; |
||
1528 | if (api_is_multiple_url_enabled()) { |
||
1529 | $urlId = api_get_current_access_url_id(); |
||
1530 | $accessUrlFilter = " INNER JOIN $tblAccessUrlCourse aurc |
||
1531 | ON (lp.c_id = aurc.c_id AND aurc.access_url_id = $urlId)"; |
||
1532 | } |
||
1533 | $index = 0; |
||
1534 | $cLpItems = []; |
||
1535 | $cLpItemsAuthor = []; |
||
1536 | $authorArray = []; |
||
1537 | $studentArray = []; |
||
1538 | $whereInLp = []; |
||
1539 | $dataSet = []; |
||
1540 | /** Get lp items only with authors */ |
||
1541 | $sql = " SELECT |
||
1542 | efv.item_id AS lp_item_id, |
||
1543 | efv.value AS author |
||
1544 | FROM $tblExtraFieldValue AS efv |
||
1545 | INNER JOIN $tblExtraField AS ef |
||
1546 | ON ( |
||
1547 | ef.variable = 'authorlpitem' AND |
||
1548 | efv.field_id = ef.id AND |
||
1549 | efv.value != '' |
||
1550 | ) |
||
1551 | ORDER BY efv.item_id "; |
||
1552 | $queryResult = Database::query($sql); |
||
1553 | $data = Database::store_result($queryResult, 'ASSOC'); |
||
1554 | $totalData = count($data); |
||
1555 | /* use 'for' to performance */ |
||
1556 | for ($i = 0; $i < $totalData; $i++) { |
||
1557 | $cLpItemsAuthor[$data[$i]['lp_item_id']] = $data[$i]['author']; |
||
1558 | } |
||
1559 | /** Get lp items only with price */ |
||
1560 | $sql = " SELECT |
||
1561 | lp.iid AS lp_id, |
||
1562 | lp.name AS lp_name, |
||
1563 | efv.item_id AS lp_item_id, |
||
1564 | lpi.title AS title, |
||
1565 | efv.value AS price |
||
1566 | FROM $tblExtraFieldValue AS efv |
||
1567 | INNER JOIN $tblExtraField AS ef |
||
1568 | ON ( |
||
1569 | ef.variable = 'price' AND |
||
1570 | efv.field_id = ef.id AND |
||
1571 | efv.value > 0 |
||
1572 | ) |
||
1573 | INNER JOIN $tblLpItem AS lpi |
||
1574 | ON (lpi.iid = efv.item_id) |
||
1575 | INNER JOIN $tblLp AS lp |
||
1576 | ON (lpi.lp_id = lp.iid AND lpi.c_id = lp.c_id) |
||
1577 | $accessUrlFilter"; |
||
1578 | $queryResult = Database::query($sql); |
||
1579 | $data = Database::store_result($queryResult, 'ASSOC'); |
||
1580 | $totalData = count($data); |
||
1581 | /* use 'for' to performance */ |
||
1582 | for ($i = 0; $i < $totalData; $i++) { |
||
1583 | $item = $data[$i]; |
||
1584 | $lpItemId = (int) $item['lp_item_id']; |
||
1585 | $whereInLp[] = $item['lp_id']; |
||
1586 | $author = isset($cLpItemsAuthor[$lpItemId]) ? $cLpItemsAuthor[$lpItemId] : null; |
||
1587 | $item['author'] = $author; |
||
1588 | if (!empty($author)) { |
||
1589 | $cLpItems[count($cLpItems)] = $item; |
||
1590 | } |
||
1591 | } |
||
1592 | $totalLpItems = count($cLpItems); |
||
1593 | $tableNoData = "<div class='table-responsive'> |
||
1594 | <table class='table table-hover table-striped table-bordered data_table'> |
||
1595 | <thead> |
||
1596 | <tr> |
||
1597 | <th class='th-header'>".get_lang('NoDataAvailable').'</th> |
||
1598 | </tr> |
||
1599 | </thead> |
||
1600 | </tbody> |
||
1601 | </tbody> |
||
1602 | </table> |
||
1603 | </div>'; |
||
1604 | if (0 == $totalLpItems) { |
||
1605 | $tableHtml = $tableNoData; |
||
1606 | } elseif (0 == count($whereInLp)) { |
||
1607 | $tableHtml = $tableNoData; |
||
1608 | } else { |
||
1609 | $whereInLp = array_unique($whereInLp); |
||
1610 | $whereInLp = implode(',', $whereInLp); |
||
1611 | $registeredUsersBySession = self::getSessionAddUserCourseFromTrackDefault( |
||
1612 | $startDate, |
||
1613 | $endDate, |
||
1614 | $whereInLp |
||
1615 | ); |
||
1616 | $registeredUsersInCourse = self::getUserSubscribedInCourseByDateAndLp($startDate, $endDate, $whereInLp); |
||
1617 | $registeredUsersInLp = self::getCompanyLearnpathSubscription( |
||
1618 | $startDate, |
||
1619 | $endDate, |
||
1620 | $whereInLp |
||
1621 | ); |
||
1622 | $registeredGroupsInLp = self::getCompanyLearnpathSubscription( |
||
1623 | $startDate, |
||
1624 | $endDate, |
||
1625 | $whereInLp, |
||
1626 | true |
||
1627 | ); |
||
1628 | /* use 'for' to performance */ |
||
1629 | for ($i = 0; $i < $totalLpItems; $i++) { |
||
1630 | $lpItem = $cLpItems[$i]; |
||
1631 | $lpItemId = $lpItem['lp_item_id']; |
||
1632 | $author = str_replace(';', ',', $lpItem['author']); |
||
1633 | $tempArrayAuthor = explode(',', $author); |
||
1634 | $byCourse = $registeredUsersInLp[$lpItemId] ?? []; |
||
1635 | $byCourseGroups = $registeredGroupsInLp[$lpItemId] ?? []; |
||
1636 | $bySession = $registeredUsersBySession[$lpItemId] ?? []; |
||
1637 | $byUserInCourse = $registeredUsersInCourse[$lpItemId] ?? []; |
||
1638 | if (is_array($tempArrayAuthor)) { |
||
1639 | $totalAuthors = count($tempArrayAuthor); |
||
1640 | for ($j = 0; $j < $totalAuthors; $j++) { |
||
1641 | if (!isset($authorArray[$tempArrayAuthor[$j]])) { |
||
1642 | $authorArray[$tempArrayAuthor[$j]] = api_get_user_info($tempArrayAuthor[$j]); |
||
1643 | } |
||
1644 | $dataSet[$tempArrayAuthor[$j]][$lpItemId] = [ |
||
1645 | 'course' => $byCourse, |
||
1646 | 'courseGroups' => $byCourseGroups, |
||
1647 | 'session' => $bySession, |
||
1648 | 'lp_item' => $lpItem, |
||
1649 | 'course_user' => $byUserInCourse, |
||
1650 | ]; |
||
1651 | } |
||
1652 | } else { |
||
1653 | if (!isset($authorArray[$author])) { |
||
1654 | $authorArray[$author] = api_get_user_info($author); |
||
1655 | } |
||
1656 | $dataSet[$author][$lpItemId] = [ |
||
1657 | 'course' => $byCourse, |
||
1658 | 'courseGroups' => $byCourseGroups, |
||
1659 | 'session' => $bySession, |
||
1660 | 'lp_item' => $lpItem, |
||
1661 | 'course_user' => $byUserInCourse, |
||
1662 | ]; |
||
1663 | } |
||
1664 | } |
||
1665 | } |
||
1666 | if ($csv == false) { |
||
1667 | if (empty($tableHtml)) { |
||
1668 | $table .= "<div class='table-responsive'> |
||
1669 | <table class='table table-hover table-striped table-bordered data_table'> |
||
1670 | <thead> |
||
1671 | <tr> |
||
1672 | <th class='th-header'>".get_lang('Author')."</th> |
||
1673 | <th class='th-header'>".get_lang('ContentList')."</th> |
||
1674 | <th class='th-header'>".get_lang('Tariff')."</th> |
||
1675 | <th class='th-header'>".get_lang('CountOfSubscribedUsers')."</th> |
||
1676 | <th class='th-header'>".get_lang('ToInvoice')."</th> |
||
1677 | <th class='th-header'>".get_lang('StudentList')."</th> |
||
1678 | </tr> |
||
1679 | </thead> |
||
1680 | <tbody>"; |
||
1681 | //Icon Constant |
||
1682 | $iconAdd = Display::return_icon('add.png', get_lang('ShowOrHide'), '', ICON_SIZE_SMALL); |
||
1683 | $iconRemove = Display::return_icon('error.png', get_lang('ShowOrHide'), '', ICON_SIZE_SMALL); |
||
1684 | |||
1685 | $lastAuthor = ''; |
||
1686 | $total = 0; |
||
1687 | foreach ($dataSet as $authorId => $lpItems) { |
||
1688 | $authorTemp = $authorArray[$authorId]; |
||
1689 | $totalSudent = 0; |
||
1690 | foreach ($lpItems as $lpItem) { |
||
1691 | $totalStudents = 0; |
||
1692 | $itemLp = $lpItem['lp_item']; |
||
1693 | $title = $itemLp['title']; |
||
1694 | $price = $itemLp['price']; |
||
1695 | $byCourse = $lpItem['course']; |
||
1696 | $byCourseGroups = $lpItem['courseGroups']; |
||
1697 | $bySession = $lpItem['session']; |
||
1698 | $byUserInCourse = $lpItem['course_user']; |
||
1699 | $hide = "class='author_$authorId hidden' "; |
||
1700 | $tableTemp = ''; |
||
1701 | if ($lastAuthor != $authorTemp) { |
||
1702 | $table .= "<tr><td>".$authorTemp['complete_name']."</td>"; |
||
1703 | } else { |
||
1704 | $table .= "<tr $hide ><td></td>"; |
||
1705 | } |
||
1706 | $table .= "<td>$title</td><td>$price</td>"; |
||
1707 | $studentRegister = count($byCourse); |
||
1708 | $studentGroupsRegister = count($byCourseGroups); |
||
1709 | $studentRegisterBySession = count($bySession); |
||
1710 | $usersInCourseCount = count($byUserInCourse); |
||
1711 | |||
1712 | $hiddenField = 'student_show_'.$index; |
||
1713 | $hiddenFieldLink = 'student_show_'.$index.'_'; |
||
1714 | if (0 != $studentRegister || |
||
1715 | 0 != $studentRegisterBySession || |
||
1716 | 0 != $studentGroupsRegister || |
||
1717 | 0 != $usersInCourseCount |
||
1718 | ) { |
||
1719 | $tableTemp .= "<td> |
||
1720 | <a href='#!' id='$hiddenFieldLink' onclick='showHideStudent(\"$hiddenField\")'> |
||
1721 | <div class='icon_add'>$iconAdd</div> |
||
1722 | <div class='icon_remove hidden'>$iconRemove</div> |
||
1723 | </a> |
||
1724 | <div id='$hiddenField' class='hidden'>"; |
||
1725 | $studentProcessed = []; |
||
1726 | /* Student by course*/ |
||
1727 | $studentProcessed = self::getStudentDataToReportByLpItem($byCourse, $studentProcessed); |
||
1728 | /* Student by Class*/ |
||
1729 | $studentProcessed = self::getStudentDataToReportByLpItem($byCourseGroups, $studentProcessed, 'class'); |
||
1730 | /* Student by sessions*/ |
||
1731 | $studentProcessed = self::getStudentDataToReportByLpItem($bySession, $studentProcessed); |
||
1732 | // Students in course*/ |
||
1733 | $studentProcessed = self::getStudentDataToReportByLpItem($byUserInCourse, $studentProcessed); |
||
1734 | $index++; |
||
1735 | foreach ($studentProcessed as $lpItemId => $item) { |
||
1736 | foreach ($item as $type => $student) { |
||
1737 | foreach ($student as $userId => $text) { |
||
1738 | if ('LearnpathSubscription' == $type) { |
||
1739 | $tableTemp .= $text; |
||
1740 | $totalStudents++; |
||
1741 | } else { |
||
1742 | if (!isset($studentProcessed[$lpItemId]['LearnpathSubscription'])) { |
||
1743 | $tableTemp .= $text; |
||
1744 | $totalStudents++; |
||
1745 | } |
||
1746 | } |
||
1747 | } |
||
1748 | } |
||
1749 | } |
||
1750 | $tableTemp .= "</div></td>"; |
||
1751 | } else { |
||
1752 | $tableTemp .= "<td></td>"; |
||
1753 | } |
||
1754 | $table .= "<td>$totalStudents</td>"; |
||
1755 | $invoicing = ($totalStudents * $price); |
||
1756 | $table .= "<td>$invoicing</td>"; |
||
1757 | $total += $invoicing; |
||
1758 | $totalSudent += $totalStudents; |
||
1759 | $table .= $tableTemp."</tr>"; |
||
1760 | $lastAuthor = $authorTemp; |
||
1761 | } |
||
1762 | $hiddenFieldLink = 'student__show_'.$index.'_'; |
||
1763 | $index++; |
||
1764 | $table .= "<tr> |
||
1765 | <th class='th-header'></th> |
||
1766 | <th class='th-header'> |
||
1767 | <a href='#!' id='$hiddenFieldLink' onclick='ShowMoreAuthor(\"$authorId\")'> |
||
1768 | <div class='icon_add_author_$authorId'>$iconAdd</div> |
||
1769 | <div class='icon_remove_author_$authorId hidden'>$iconRemove</div> |
||
1770 | </a> |
||
1771 | </th> |
||
1772 | <th class='th-header'></th> |
||
1773 | <th class='th-header'>$totalSudent</th> |
||
1774 | <th class='th-header'>$total</th> |
||
1775 | <th class='th-header'></tr>"; |
||
1776 | $total = 0; |
||
1777 | } |
||
1778 | $table .= "</tbody></table></div>"; |
||
1779 | $tableHtml = $table; |
||
1780 | } |
||
1781 | |||
1782 | $form = new FormValidator('searchDate', 'get'); |
||
1783 | $form->addHidden('display', 'learningPathByItem'); |
||
1784 | $today = new DateTime(); |
||
1785 | if (empty($startDate)) { |
||
1786 | $startDate = $today->modify('first day of this month')->format('Y-m-d'); |
||
1787 | } |
||
1788 | if (empty($endDate)) { |
||
1789 | $endDate = $today->modify('last day of this month')->format('Y-m-d'); |
||
1790 | } |
||
1791 | $form->addDatePicker( |
||
1792 | 'startDate', |
||
1793 | get_lang('DateStart'), |
||
1794 | [ |
||
1795 | 'value' => $startDate, |
||
1796 | ] |
||
1797 | ); |
||
1798 | $form->addDatePicker( |
||
1799 | 'endDate', |
||
1800 | get_lang('DateEnd'), |
||
1801 | [ |
||
1802 | 'value' => $endDate, |
||
1803 | ] |
||
1804 | ); |
||
1805 | $form->addButtonSearch(get_lang('Search')); |
||
1806 | |||
1807 | if (count($dataSet) != 0) { |
||
1808 | $form->addButton( |
||
1809 | 'export_csv', |
||
1810 | get_lang('ExportAsCSV'), |
||
1811 | 'check', |
||
1812 | 'primary', |
||
1813 | null, |
||
1814 | null, |
||
1815 | [ |
||
1816 | ] |
||
1817 | ); |
||
1818 | } |
||
1819 | $tableContent = $form->returnForm(); |
||
1820 | $tableContent .= $tableHtml; |
||
1821 | $tpl = new Template('', false, false, false, false, false, false); |
||
1822 | $tpl->assign('table', $tableContent); |
||
1823 | $templateName = $tpl->get_template('my_space/course_summary.tpl'); |
||
1824 | $tpl->display($templateName); |
||
1825 | } else { |
||
1826 | $csv_content = []; |
||
1827 | $csv_row = []; |
||
1828 | $csv_row[] = get_lang('Author'); |
||
1829 | $csv_row[] = get_lang('ContentList'); |
||
1830 | $csv_row[] = get_lang('Tariff'); |
||
1831 | $csv_row[] = get_lang('CountOfSubscribedUsers'); |
||
1832 | $csv_row[] = get_lang('ToInvoice'); |
||
1833 | $csv_row[] = get_lang('StudentList'); |
||
1834 | $csv_content[] = $csv_row; |
||
1835 | $total = 0; |
||
1836 | foreach ($dataSet as $authorId => $lpItems) { |
||
1837 | $authorTemp = $authorArray[$authorId]; |
||
1838 | $totalSudent = 0; |
||
1839 | foreach ($lpItems as $lpItem) { |
||
1840 | $totalStudents = 0; |
||
1841 | $itemLp = $lpItem['lp_item']; |
||
1842 | $itemLpId = $itemLp['lp_item_id']; |
||
1843 | $title = $itemLp['title']; |
||
1844 | $price = $itemLp['price']; |
||
1845 | $byCourse = $lpItem['course']; |
||
1846 | $bySession = $lpItem['session']; |
||
1847 | $byCourseGroups = $lpItem['courseGroups']; |
||
1848 | $byUserInCourse = $lpItem['course_user']; |
||
1849 | |||
1850 | $csv_row = []; |
||
1851 | $csv_row[] = $authorTemp['complete_name']; |
||
1852 | $csv_row[] = $title; |
||
1853 | $csv_row[] = $price; |
||
1854 | |||
1855 | $studentRegister = count($byCourse); |
||
1856 | $studentRegisterBySession = count($bySession); |
||
1857 | $studentGroupsRegister = count($byCourseGroups); |
||
1858 | $usersInCourseCount = count($byUserInCourse); |
||
1859 | |||
1860 | $studentsName = ''; |
||
1861 | if (0 != $studentRegister || |
||
1862 | 0 != $studentRegisterBySession || |
||
1863 | 0 != $studentGroupsRegister || |
||
1864 | 0 != $usersInCourseCount |
||
1865 | ) { |
||
1866 | $studentProcessed = []; |
||
1867 | /* Student by course*/ |
||
1868 | $studentProcessed = self::getStudentDataToReportByLpItem($byCourse, $studentProcessed, '', true); |
||
1869 | /* Student by Class*/ |
||
1870 | $studentProcessed = self::getStudentDataToReportByLpItem($byCourseGroups, $studentProcessed, 'class', true); |
||
1871 | /* Student by sessions*/ |
||
1872 | $studentProcessed = self::getStudentDataToReportByLpItem($bySession, $studentProcessed, '', true); |
||
1873 | // Students in course*/ |
||
1874 | $studentProcessed = self::getStudentDataToReportByLpItem($byUserInCourse, $studentProcessed, '', true); |
||
1875 | |||
1876 | $index++; |
||
1877 | foreach ($studentProcessed as $lpItemId => $item) { |
||
1878 | foreach ($item as $type => $student) { |
||
1879 | foreach ($student as $userId => $text) { |
||
1880 | if ('LearnpathSubscription' == $type) { |
||
1881 | $studentsName .= $text; |
||
1882 | $totalStudents++; |
||
1883 | } else { |
||
1884 | if (!isset($studentProcessed[$lpItemId]['LearnpathSubscription'])) { |
||
1885 | $studentsName .= $text; |
||
1886 | $totalStudents++; |
||
1887 | } |
||
1888 | } |
||
1889 | } |
||
1890 | } |
||
1891 | } |
||
1892 | } |
||
1893 | $csv_row[] = $totalStudents; |
||
1894 | $csv_row[] = $price * $totalStudents; |
||
1895 | $csv_row[] = trim($studentsName, " / "); |
||
1896 | $csv_content[] = $csv_row; |
||
1897 | } |
||
1898 | } |
||
1899 | Export::arrayToCsv($csv_content, 'reporting_lp_by_authors'); |
||
1900 | } |
||
1901 | } |
||
1902 | |||
1903 | public static function getSessionAddUserCourseFromTrackDefault( |
||
1904 | $startDate = null, |
||
1905 | $endDate = null, |
||
1906 | $whereInLp = null |
||
1907 | ) { |
||
1908 | $whereInLp = Database::escape_string($whereInLp); |
||
1909 | $data = []; |
||
1910 | $tblTrackDefault = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT); |
||
1911 | $tblSessionRelCourseUser = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); |
||
1912 | $tblLp = Database::get_course_table(TABLE_LP_MAIN); |
||
1913 | $tblLpItem = Database::get_course_table(TABLE_LP_ITEM); |
||
1914 | $tblUser = Database::get_main_table(TABLE_MAIN_USER); |
||
1915 | $tblAccessUrlUser = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
1916 | $accessUrlFilter = ''; |
||
1917 | if (api_is_multiple_url_enabled()) { |
||
1918 | $urlId = api_get_current_access_url_id(); |
||
1919 | $accessUrlFilter = " INNER JOIN $tblAccessUrlUser auru |
||
1920 | ON (u.id = auru.user_id AND auru.access_url_id = $urlId)"; |
||
1921 | } |
||
1922 | |||
1923 | if (!empty($startDate)) { |
||
1924 | $startDate = new DateTime($startDate); |
||
1925 | } else { |
||
1926 | $startDate = new DateTime(); |
||
1927 | } |
||
1928 | if (!empty($endDate)) { |
||
1929 | $endDate = new DateTime($endDate); |
||
1930 | } else { |
||
1931 | $endDate = new DateTime(); |
||
1932 | } |
||
1933 | if (!empty($startDate) and !empty($endDate)) { |
||
1934 | if ($startDate > $endDate) { |
||
1935 | $dateTemp = $endDate; |
||
1936 | $endDate = $startDate; |
||
1937 | $startDate = $dateTemp; |
||
1938 | unset($dateTemp); |
||
1939 | } |
||
1940 | } |
||
1941 | $startDate = api_get_utc_datetime($startDate->setTime(0, 0, 0)->format('Y-m-d H:i:s')); |
||
1942 | $endDate = api_get_utc_datetime($endDate->setTime(0, 0, 0)->format('Y-m-d H:i:s')); |
||
1943 | $extra = ''; |
||
1944 | if (!empty($whereInLp)) { |
||
1945 | $extra = " AND lpi.lp_id in ($whereInLp) "; |
||
1946 | } |
||
1947 | |||
1948 | $sql = "SELECT DISTINCT |
||
1949 | lp.iid AS lp, |
||
1950 | lpi.iid AS lp_item, |
||
1951 | lpi.iid AS lp_item_id, |
||
1952 | td.default_value AS id, |
||
1953 | srcu.session_id AS session_id, |
||
1954 | u.username AS username, |
||
1955 | td.default_date AS default_date, |
||
1956 | td.default_event_type AS type, |
||
1957 | u.firstname as firstname, |
||
1958 | u.lastname as lastname |
||
1959 | FROM $tblTrackDefault AS td |
||
1960 | INNER JOIN $tblSessionRelCourseUser AS srcu |
||
1961 | ON (td.default_value = srcu.user_id AND td.c_id = srcu.c_id) |
||
1962 | INNER JOIN $tblLp AS lp |
||
1963 | ON (lp.c_id = srcu.c_id) |
||
1964 | INNER JOIN $tblLpItem AS lpi |
||
1965 | ON ( |
||
1966 | lpi.c_id = srcu.c_id AND |
||
1967 | lp.id = lpi.lp_id AND |
||
1968 | lpi.c_id = lp.c_id |
||
1969 | ) |
||
1970 | INNER JOIN $tblUser AS u |
||
1971 | ON (u.id = srcu.user_id) |
||
1972 | $accessUrlFilter |
||
1973 | WHERE |
||
1974 | td.default_event_type = 'session_add_user_course' AND |
||
1975 | td.default_date >= '$startDate' AND |
||
1976 | td.default_date <= '$endDate' |
||
1977 | $extra |
||
1978 | ORDER BY td.default_value "; |
||
1979 | $queryResult = Database::query($sql); |
||
1980 | $dataTrack = Database::store_result($queryResult, 'ASSOC'); |
||
1981 | foreach ($dataTrack as $item) { |
||
1982 | $item['complete_name'] = api_get_person_name($item['firstname'], $item['lastname']); |
||
1983 | $item['company'] = self::getCompanyOfUser($item['id']); |
||
1984 | $data[$item['lp_item_id']][] = $item; |
||
1985 | } |
||
1986 | |||
1987 | return $data; |
||
1988 | } |
||
1989 | |||
1990 | public static function getUserSubscribedInCourseByDateAndLp( |
||
1991 | $startDate = null, |
||
1992 | $endDate = null, |
||
1993 | $whereInLp = null |
||
1994 | ): array { |
||
1995 | $whereInLp = Database::escape_string($whereInLp); |
||
1996 | $tblTrackDefault = Database::get_main_table(TABLE_STATISTIC_TRACK_E_DEFAULT); |
||
1997 | $tblCourseRelUser = Database::get_main_table(TABLE_MAIN_COURSE_USER); |
||
1998 | $tblLp = Database::get_course_table(TABLE_LP_MAIN); |
||
1999 | $tblLpItem = Database::get_course_table(TABLE_LP_ITEM); |
||
2000 | $tblUser = Database::get_main_table(TABLE_MAIN_USER); |
||
2001 | $tblAccessUrlUser = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
2002 | $accessUrlFilter = ''; |
||
2003 | |||
2004 | if (api_is_multiple_url_enabled()) { |
||
2005 | $urlId = api_get_current_access_url_id(); |
||
2006 | $accessUrlFilter = " INNER JOIN $tblAccessUrlUser auru |
||
2007 | ON (u.id = auru.user_id AND auru.access_url_id = $urlId)"; |
||
2008 | } |
||
2009 | |||
2010 | $startDate = !empty($startDate) ? new DateTime($startDate) : new DateTime(); |
||
2011 | $endDate = !empty($endDate) ? new DateTime($endDate) : new DateTime(); |
||
2012 | |||
2013 | $startDate = api_get_utc_datetime($startDate->setTime(0, 0)->format('Y-m-d H:i:s')); |
||
2014 | $endDate = api_get_utc_datetime($endDate->setTime(0, 0)->format('Y-m-d H:i:s')); |
||
2015 | |||
2016 | $extra = ''; |
||
2017 | |||
2018 | if (!empty($whereInLp)) { |
||
2019 | $extra = " AND lpi.lp_id in ($whereInLp) "; |
||
2020 | } |
||
2021 | |||
2022 | $sql = "SELECT DISTINCT |
||
2023 | lp.iid AS lp, |
||
2024 | lpi.iid AS lp_item, |
||
2025 | lpi.iid AS lp_item_id, |
||
2026 | u.id AS id, |
||
2027 | u.username AS username, |
||
2028 | td.default_date AS default_date, |
||
2029 | td.default_event_type AS type, |
||
2030 | u.firstname as firstname, |
||
2031 | u.lastname as lastname |
||
2032 | FROM $tblTrackDefault AS td |
||
2033 | INNER JOIN $tblCourseRelUser AS cru ON td.c_id = cru.c_id |
||
2034 | INNER JOIN $tblLp AS lp ON lp.c_id = cru.c_id |
||
2035 | INNER JOIN $tblLpItem AS lpi |
||
2036 | ON (lpi.c_id = cru.c_id AND lp.id = lpi.lp_id AND lpi.c_id = lp.c_id) |
||
2037 | INNER JOIN $tblUser AS u ON u.id = cru.user_id |
||
2038 | $accessUrlFilter |
||
2039 | WHERE |
||
2040 | td.default_event_type = '".LOG_SUBSCRIBE_USER_TO_COURSE."' |
||
2041 | AND td.default_date >= '$startDate' |
||
2042 | AND td.default_date <= '$endDate' |
||
2043 | AND td.default_value LIKE CONCAT('%s:2:\\\\\\\\\\\"id\\\\\\\\\";i:', cru.user_id, ';%') |
||
2044 | $extra |
||
2045 | ORDER BY u.id"; |
||
2046 | |||
2047 | $result = Database::query($sql); |
||
2048 | |||
2049 | $data = []; |
||
2050 | |||
2051 | while ($item = Database::fetch_assoc($result)) { |
||
2052 | $item['complete_name'] = api_get_person_name($item['firstname'], $item['lastname']); |
||
2053 | $item['company'] = self::getCompanyOfUser($item['id']); |
||
2054 | |||
2055 | $data[$item['lp_item_id']][] = $item; |
||
2056 | } |
||
2057 | |||
2058 | return $data; |
||
2059 | } |
||
2060 | |||
2061 | /** |
||
2062 | * Display a sortable table that contains an overview of all the reporting progress of all courses. |
||
2063 | */ |
||
2064 | public static function display_tracking_course_overview() |
||
2065 | { |
||
2066 | $params = ['view' => 'admin', 'display' => 'courseoverview']; |
||
2067 | $table = new SortableTable( |
||
2068 | 'tracking_session_overview', |
||
2069 | ['MySpace', 'get_total_number_courses'], |
||
2070 | ['MySpace', 'get_course_data_tracking_overview'], |
||
2071 | 1, |
||
2072 | 20, |
||
2073 | 'ASC', |
||
2074 | null, [ |
||
2075 | 'class' => 'table table-transparent', |
||
2076 | ] |
||
2077 | ); |
||
2078 | $table->additional_parameters = $params; |
||
2079 | $table->set_column_filter(0, ['MySpace', 'course_tracking_filter']); |
||
2080 | $tableContent = $table->return_table(); |
||
2081 | |||
2082 | $tpl = new Template('', false, false, false, false, false, false); |
||
2083 | $tpl->assign('table', $tableContent); |
||
2084 | $templateName = $tpl->get_template('my_space/course_summary.tpl'); |
||
2085 | $tpl->display($templateName); |
||
2086 | } |
||
2087 | |||
2088 | /** |
||
2089 | * Get the total number of courses. |
||
2090 | * |
||
2091 | * @return int Total number of courses |
||
2092 | */ |
||
2093 | public static function get_total_number_courses() |
||
2094 | { |
||
2095 | return CourseManager::count_courses(api_get_current_access_url_id()); |
||
2096 | } |
||
2097 | |||
2098 | /** |
||
2099 | * Get data for the courses. |
||
2100 | * |
||
2101 | * @param int $from Inferior limit |
||
2102 | * @param int $numberItems Number of items to select |
||
2103 | * @param string $column Column to order on |
||
2104 | * @param string $direction Order direction |
||
2105 | * |
||
2106 | * @return array Results |
||
2107 | */ |
||
2108 | public static function get_course_data_tracking_overview( |
||
2109 | $from, |
||
2110 | $numberItems, |
||
2111 | $column, |
||
2112 | $direction |
||
2113 | ) { |
||
2114 | switch ($column) { |
||
2115 | default: |
||
2116 | case 1: |
||
2117 | $column = 'title'; |
||
2118 | break; |
||
2119 | } |
||
2120 | |||
2121 | $courses = CourseManager::get_courses_list( |
||
2122 | $from, |
||
2123 | $numberItems, |
||
2124 | $column, |
||
2125 | $direction, |
||
2126 | -1, |
||
2127 | '', |
||
2128 | api_get_current_access_url_id() |
||
2129 | ); |
||
2130 | |||
2131 | $list = []; |
||
2132 | foreach ($courses as $course) { |
||
2133 | $list[] = [ |
||
2134 | '0' => $course['code'], |
||
2135 | 'col0' => $course['code'], |
||
2136 | ]; |
||
2137 | } |
||
2138 | |||
2139 | return $list; |
||
2140 | } |
||
2141 | |||
2142 | /** |
||
2143 | * Fills in course reporting data. |
||
2144 | * |
||
2145 | * @param int course code |
||
2146 | * @param array $url_params additional url parameters |
||
2147 | * @param array $row the row information (the other columns) |
||
2148 | * |
||
2149 | * @return string html code |
||
2150 | */ |
||
2151 | public static function course_tracking_filter($course_code, $url_params, $row) |
||
2152 | { |
||
2153 | $course_code = $row[0]; |
||
2154 | $courseInfo = api_get_course_info($course_code); |
||
2155 | $courseId = $courseInfo['real_id']; |
||
2156 | |||
2157 | $tpl = new Template('', false, false, false, false, false, false); |
||
2158 | $data = null; |
||
2159 | |||
2160 | // database table definition |
||
2161 | $tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER); |
||
2162 | $tbl_user = Database::get_main_table(TABLE_MAIN_USER); |
||
2163 | |||
2164 | // getting all the courses of the user |
||
2165 | $sql = "SELECT * |
||
2166 | FROM $tbl_user AS u |
||
2167 | INNER JOIN $tbl_course_rel_user AS cu |
||
2168 | ON cu.user_id = u.user_id |
||
2169 | WHERE cu.c_id = '".$courseId."'"; |
||
2170 | $result = Database::query($sql); |
||
2171 | $time_spent = 0; |
||
2172 | $progress = 0; |
||
2173 | $nb_progress_lp = 0; |
||
2174 | $score = 0; |
||
2175 | $nb_score_lp = 0; |
||
2176 | $nb_messages = 0; |
||
2177 | $nb_assignments = 0; |
||
2178 | $last_login_date = false; |
||
2179 | $total_score_obtained = 0; |
||
2180 | $total_score_possible = 0; |
||
2181 | $total_questions_answered = 0; |
||
2182 | while ($row = Database::fetch_object($result)) { |
||
2183 | // get time spent in the course and session |
||
2184 | $time_spent += Tracking::get_time_spent_on_the_course( |
||
2185 | $row->user_id, |
||
2186 | $courseInfo['real_id'] |
||
2187 | ); |
||
2188 | $progress_tmp = Tracking::get_avg_student_progress( |
||
2189 | $row->user_id, |
||
2190 | $course_code, |
||
2191 | [], |
||
2192 | null, |
||
2193 | true |
||
2194 | ); |
||
2195 | |||
2196 | if ($progress_tmp) { |
||
2197 | $progress += $progress_tmp[0]; |
||
2198 | $nb_progress_lp += $progress_tmp[1]; |
||
2199 | } |
||
2200 | $score_tmp = Tracking::get_avg_student_score( |
||
2201 | $row->user_id, |
||
2202 | $course_code, |
||
2203 | [], |
||
2204 | null, |
||
2205 | true |
||
2206 | ); |
||
2207 | if (is_array($score_tmp)) { |
||
2208 | $score += $score_tmp[0]; |
||
2209 | $nb_score_lp += $score_tmp[1]; |
||
2210 | } |
||
2211 | $nb_messages += Tracking::count_student_messages( |
||
2212 | $row->user_id, |
||
2213 | $course_code |
||
2214 | ); |
||
2215 | $nb_assignments += Tracking::count_student_assignments( |
||
2216 | $row->user_id, |
||
2217 | $course_code |
||
2218 | ); |
||
2219 | $last_login_date_tmp = Tracking::get_last_connection_date_on_the_course( |
||
2220 | $row->user_id, |
||
2221 | $courseInfo, |
||
2222 | null, |
||
2223 | false |
||
2224 | ); |
||
2225 | if ($last_login_date_tmp != false && |
||
2226 | $last_login_date == false |
||
2227 | ) { // TODO: To be cleaned |
||
2228 | $last_login_date = $last_login_date_tmp; |
||
2229 | } elseif ($last_login_date_tmp != false && $last_login_date != false) { |
||
2230 | // TODO: Repeated previous condition. To be cleaned. |
||
2231 | // Find the max and assign it to first_login_date |
||
2232 | if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) { |
||
2233 | $last_login_date = $last_login_date_tmp; |
||
2234 | } |
||
2235 | } |
||
2236 | |||
2237 | $exercise_results_tmp = self::exercises_results($row->user_id, $course_code); |
||
2238 | $total_score_obtained += $exercise_results_tmp['score_obtained']; |
||
2239 | $total_score_possible += $exercise_results_tmp['score_possible']; |
||
2240 | $total_questions_answered += $exercise_results_tmp['questions_answered']; |
||
2241 | } |
||
2242 | if ($nb_progress_lp > 0) { |
||
2243 | $avg_progress = round($progress / $nb_progress_lp, 2); |
||
2244 | } else { |
||
2245 | $avg_progress = 0; |
||
2246 | } |
||
2247 | if ($nb_score_lp > 0) { |
||
2248 | $avg_score = round($score / $nb_score_lp, 2); |
||
2249 | } else { |
||
2250 | $avg_score = '-'; |
||
2251 | } |
||
2252 | if ($last_login_date) { |
||
2253 | $last_login_date = api_convert_and_format_date( |
||
2254 | $last_login_date, |
||
2255 | DATE_FORMAT_SHORT, |
||
2256 | date_default_timezone_get() |
||
2257 | ); |
||
2258 | } else { |
||
2259 | $last_login_date = '-'; |
||
2260 | } |
||
2261 | if ($total_score_possible > 0) { |
||
2262 | $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2); |
||
2263 | } else { |
||
2264 | $total_score_percentage = 0; |
||
2265 | } |
||
2266 | if ($total_score_percentage > 0) { |
||
2267 | $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)'; |
||
2268 | } else { |
||
2269 | $total_score = '-'; |
||
2270 | } |
||
2271 | |||
2272 | $data = [ |
||
2273 | 'course_code' => $course_code, |
||
2274 | 'id' => $courseId, |
||
2275 | 'image' => $courseInfo['course_image_large'], |
||
2276 | 'image_small' => $courseInfo['course_image'], |
||
2277 | 'title' => $courseInfo['title'], |
||
2278 | 'url' => $courseInfo['course_public_url'], |
||
2279 | 'category' => $courseInfo['categoryName'], |
||
2280 | 'time_spent' => api_time_to_hms($time_spent), |
||
2281 | 'avg_progress' => $avg_progress, |
||
2282 | 'avg_score' => $avg_score, |
||
2283 | 'number_message' => $nb_messages, |
||
2284 | 'number_assignments' => $nb_assignments, |
||
2285 | 'total_score' => $total_score, |
||
2286 | 'questions_answered' => $total_questions_answered, |
||
2287 | 'last_login' => $last_login_date, |
||
2288 | ]; |
||
2289 | |||
2290 | $tpl->assign('data', $data); |
||
2291 | $layout = $tpl->get_template('my_space/partials/tracking_course_overview.tpl'); |
||
2292 | $content = $tpl->fetch($layout); |
||
2293 | |||
2294 | return $content; |
||
2295 | } |
||
2296 | |||
2297 | /** |
||
2298 | * This function exports the table that we see in display_tracking_course_overview(). |
||
2299 | */ |
||
2300 | public static function export_tracking_course_overview() |
||
2301 | { |
||
2302 | // database table definition |
||
2303 | $tbl_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER); |
||
2304 | $tbl_user = Database::get_main_table(TABLE_MAIN_USER); |
||
2305 | |||
2306 | // the values of the sortable table |
||
2307 | if ($_GET['tracking_course_overview_page_nr']) { |
||
2308 | $from = $_GET['tracking_course_overview_page_nr']; |
||
2309 | } else { |
||
2310 | $from = 0; |
||
2311 | } |
||
2312 | if ($_GET['tracking_course_overview_column']) { |
||
2313 | $orderby = $_GET['tracking_course_overview_column']; |
||
2314 | } else { |
||
2315 | $orderby = 0; |
||
2316 | } |
||
2317 | |||
2318 | if ($_GET['tracking_course_overview_direction']) { |
||
2319 | $direction = $_GET['tracking_course_overview_direction']; |
||
2320 | } else { |
||
2321 | $direction = 'ASC'; |
||
2322 | } |
||
2323 | |||
2324 | $course_data = self::get_course_data_tracking_overview( |
||
2325 | $from, |
||
2326 | 1000, |
||
2327 | $orderby, |
||
2328 | $direction |
||
2329 | ); |
||
2330 | |||
2331 | $csv_content = []; |
||
2332 | |||
2333 | // the first line of the csv file with the column headers |
||
2334 | $csv_row = []; |
||
2335 | $csv_row[] = get_lang('Course'); |
||
2336 | $csv_row[] = get_lang('AvgTimeSpentInTheCourse'); |
||
2337 | $csv_row[] = get_lang('AvgStudentsProgress'); |
||
2338 | $csv_row[] = get_lang('AvgCourseScore'); |
||
2339 | $csv_row[] = get_lang('TotalNumberOfMessages'); |
||
2340 | $csv_row[] = get_lang('TotalNumberOfAssignments'); |
||
2341 | $csv_row[] = get_lang('TotalExercisesScoreObtained'); |
||
2342 | $csv_row[] = get_lang('TotalExercisesScorePossible'); |
||
2343 | $csv_row[] = get_lang('TotalExercisesAnswered'); |
||
2344 | $csv_row[] = get_lang('TotalExercisesScorePercentage'); |
||
2345 | $csv_row[] = get_lang('LatestLogin'); |
||
2346 | $csv_content[] = $csv_row; |
||
2347 | |||
2348 | // the other lines (the data) |
||
2349 | foreach ($course_data as $key => $course) { |
||
2350 | $course_code = $course[0]; |
||
2351 | $courseInfo = api_get_course_info($course_code); |
||
2352 | $course_title = $courseInfo['title']; |
||
2353 | $courseId = $courseInfo['real_id']; |
||
2354 | |||
2355 | $csv_row = []; |
||
2356 | $csv_row[] = $course_title; |
||
2357 | |||
2358 | // getting all the courses of the session |
||
2359 | $sql = "SELECT * |
||
2360 | FROM $tbl_user AS u |
||
2361 | INNER JOIN $tbl_course_rel_user AS cu |
||
2362 | ON cu.user_id = u.user_id |
||
2363 | WHERE cu.c_id = '".$courseId."'"; |
||
2364 | $result = Database::query($sql); |
||
2365 | $time_spent = 0; |
||
2366 | $progress = 0; |
||
2367 | $nb_progress_lp = 0; |
||
2368 | $score = 0; |
||
2369 | $nb_score_lp = 0; |
||
2370 | $nb_messages = 0; |
||
2371 | $nb_assignments = 0; |
||
2372 | $last_login_date = false; |
||
2373 | $total_score_obtained = 0; |
||
2374 | $total_score_possible = 0; |
||
2375 | $total_questions_answered = 0; |
||
2376 | while ($row = Database::fetch_object($result)) { |
||
2377 | // get time spent in the course and session |
||
2378 | $time_spent += Tracking::get_time_spent_on_the_course( |
||
2379 | $row->user_id, |
||
2380 | $courseId |
||
2381 | ); |
||
2382 | $progress_tmp = Tracking::get_avg_student_progress( |
||
2383 | $row->user_id, |
||
2384 | $course_code, |
||
2385 | [], |
||
2386 | null, |
||
2387 | true |
||
2388 | ); |
||
2389 | $progress += $progress_tmp[0]; |
||
2390 | $nb_progress_lp += $progress_tmp[1]; |
||
2391 | $score_tmp = Tracking::get_avg_student_score( |
||
2392 | $row->user_id, |
||
2393 | $course_code, |
||
2394 | [], |
||
2395 | null, |
||
2396 | true |
||
2397 | ); |
||
2398 | if (is_array($score_tmp)) { |
||
2399 | $score += $score_tmp[0]; |
||
2400 | $nb_score_lp += $score_tmp[1]; |
||
2401 | } |
||
2402 | $nb_messages += Tracking::count_student_messages( |
||
2403 | $row->user_id, |
||
2404 | $course_code |
||
2405 | ); |
||
2406 | $nb_assignments += Tracking::count_student_assignments( |
||
2407 | $row->user_id, |
||
2408 | $course_code |
||
2409 | ); |
||
2410 | |||
2411 | $last_login_date_tmp = Tracking::get_last_connection_date_on_the_course( |
||
2412 | $row->user_id, |
||
2413 | $courseInfo, |
||
2414 | null, |
||
2415 | false |
||
2416 | ); |
||
2417 | if ($last_login_date_tmp != false && $last_login_date == false) { |
||
2418 | // TODO: To be cleaned. |
||
2419 | $last_login_date = $last_login_date_tmp; |
||
2420 | } elseif ($last_login_date_tmp != false && $last_login_date == false) { |
||
2421 | // TODO: Repeated previous condition. To be cleaned. |
||
2422 | // Find the max and assign it to first_login_date |
||
2423 | if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) { |
||
2424 | $last_login_date = $last_login_date_tmp; |
||
2425 | } |
||
2426 | } |
||
2427 | |||
2428 | $exercise_results_tmp = self::exercises_results($row->user_id, $course_code); |
||
2429 | $total_score_obtained += $exercise_results_tmp['score_obtained']; |
||
2430 | $total_score_possible += $exercise_results_tmp['score_possible']; |
||
2431 | $total_questions_answered += $exercise_results_tmp['questions_answered']; |
||
2432 | } |
||
2433 | if ($nb_progress_lp > 0) { |
||
2434 | $avg_progress = round($progress / $nb_progress_lp, 2); |
||
2435 | } else { |
||
2436 | $avg_progress = 0; |
||
2437 | } |
||
2438 | if ($nb_score_lp > 0) { |
||
2439 | $avg_score = round($score / $nb_score_lp, 2); |
||
2440 | } else { |
||
2441 | $avg_score = '-'; |
||
2442 | } |
||
2443 | if ($last_login_date) { |
||
2444 | $last_login_date = api_convert_and_format_date( |
||
2445 | $last_login_date, |
||
2446 | DATE_FORMAT_SHORT, |
||
2447 | date_default_timezone_get() |
||
2448 | ); |
||
2449 | } else { |
||
2450 | $last_login_date = '-'; |
||
2451 | } |
||
2452 | if ($total_score_possible > 0) { |
||
2453 | $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2); |
||
2454 | } else { |
||
2455 | $total_score_percentage = 0; |
||
2456 | } |
||
2457 | // time spent in the course |
||
2458 | $csv_row[] = api_time_to_hms($time_spent); |
||
2459 | // student progress in course |
||
2460 | $csv_row[] = $avg_progress; |
||
2461 | // student score |
||
2462 | $csv_row[] = $avg_score; |
||
2463 | // student messages |
||
2464 | $csv_row[] = $nb_messages; |
||
2465 | // student assignments |
||
2466 | $csv_row[] = $nb_assignments; |
||
2467 | // student exercises results (obtained score, maximum score, number of exercises answered, score percentage) |
||
2468 | $csv_row[] = $total_score_obtained; |
||
2469 | $csv_row[] = $total_score_possible; |
||
2470 | $csv_row[] = $total_questions_answered; |
||
2471 | $csv_row[] = $total_score_percentage; |
||
2472 | // last connection |
||
2473 | $csv_row[] = $last_login_date; |
||
2474 | $csv_content[] = $csv_row; |
||
2475 | } |
||
2476 | Export::arrayToCsv($csv_content, 'reporting_course_overview'); |
||
2477 | exit; |
||
2478 | } |
||
2479 | |||
2480 | /** |
||
2481 | * Display a sortable table that contains an overview of all the reporting |
||
2482 | * progress of all sessions and all courses the user is subscribed to. |
||
2483 | * |
||
2484 | * @author Guillaume Viguier <[email protected]> |
||
2485 | */ |
||
2486 | public static function display_tracking_session_overview() |
||
2487 | { |
||
2488 | $head = '<table style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">'; |
||
2489 | $head .= '<tr>'; |
||
2490 | $head .= '<th width="155px" style="border-left:0;border-bottom:0"><span>'.get_lang('Course').'</span></th>'; |
||
2491 | $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgTimeSpentInTheCourse'), 6, true).'</span></th>'; |
||
2492 | $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgStudentsProgress'), 6, true).'</span></th>'; |
||
2493 | $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('AvgCourseScore'), 6, true).'</span></th>'; |
||
2494 | $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfMessages'), 6, true).'</span></th>'; |
||
2495 | $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalNumberOfAssignments'), 6, true).'</span></th>'; |
||
2496 | $head .= '<th width="105px" style="border-bottom:0"><span>'.get_lang('TotalExercisesScoreObtained').'</span></th>'; |
||
2497 | $head .= '<th style="padding:0;border-bottom:0"><span>'.cut(get_lang('TotalExercisesAnswered'), 6, true).'</span></th>'; |
||
2498 | $head .= '<th style="padding:0;border-bottom:0;border-right:0;"><span>'.get_lang('LatestLogin').'</span></th>'; |
||
2499 | $head .= '</tr></table>'; |
||
2500 | |||
2501 | $params = ['view' => 'admin', 'display' => 'sessionoverview']; |
||
2502 | $table = new SortableTable( |
||
2503 | 'tracking_session_overview', |
||
2504 | ['MySpace', 'get_total_number_sessions'], |
||
2505 | ['MySpace', 'get_session_data_tracking_overview'], |
||
2506 | 1 |
||
2507 | ); |
||
2508 | $table->additional_parameters = $params; |
||
2509 | |||
2510 | $table->set_header(0, '', false, null, ['style' => 'display: none']); |
||
2511 | $table->set_header( |
||
2512 | 1, |
||
2513 | get_lang('Session'), |
||
2514 | true, |
||
2515 | ['style' => 'font-size:8pt'], |
||
2516 | ['style' => 'font-size:8pt'] |
||
2517 | ); |
||
2518 | $table->set_header( |
||
2519 | 2, |
||
2520 | $head, |
||
2521 | false, |
||
2522 | ['style' => 'width:90%;border:0;padding:0;font-size:7.5pt;'], |
||
2523 | ['style' => 'width:90%;padding:0;font-size:7.5pt;'] |
||
2524 | ); |
||
2525 | $table->set_column_filter(2, ['MySpace', 'session_tracking_filter']); |
||
2526 | $table->display(); |
||
2527 | } |
||
2528 | |||
2529 | /** |
||
2530 | * Get the total number of sessions. |
||
2531 | * |
||
2532 | * @return int Total number of sessions |
||
2533 | */ |
||
2534 | public static function get_total_number_sessions() |
||
2535 | { |
||
2536 | return SessionManager::count_sessions(api_get_current_access_url_id()); |
||
2537 | } |
||
2538 | |||
2539 | /** |
||
2540 | * Get data for the sessions. |
||
2541 | * |
||
2542 | * @param int $from Inferior limit |
||
2543 | * @param int $numberItems Number of items to select |
||
2544 | * @param string $column Column to order on |
||
2545 | * @param string $direction Order direction |
||
2546 | * |
||
2547 | * @return array Results |
||
2548 | */ |
||
2549 | public static function get_session_data_tracking_overview( |
||
2550 | $from, |
||
2551 | $numberItems, |
||
2552 | $column, |
||
2553 | $direction |
||
2554 | ) { |
||
2555 | $from = (int) $from; |
||
2556 | $numberItems = (int) $numberItems; |
||
2557 | $direction = Database::escape_string($direction); |
||
2558 | $columnName = 'name'; |
||
2559 | if ($column === 1) { |
||
2560 | $columnName = 'id'; |
||
2561 | } |
||
2562 | |||
2563 | $options = [ |
||
2564 | 'order' => " $columnName $direction", |
||
2565 | 'limit' => " $from,$numberItems", |
||
2566 | ]; |
||
2567 | $sessions = SessionManager::formatSessionsAdminForGrid($options); |
||
2568 | $list = []; |
||
2569 | foreach ($sessions as $session) { |
||
2570 | $list[] = [ |
||
2571 | '0' => $session['id'], |
||
2572 | 'col0' => $session['id'], |
||
2573 | '1' => strip_tags($session['name']), |
||
2574 | 'col1' => strip_tags($session['name']), |
||
2575 | ]; |
||
2576 | } |
||
2577 | |||
2578 | return $list; |
||
2579 | } |
||
2580 | |||
2581 | /** |
||
2582 | * Fills in session reporting data. |
||
2583 | * |
||
2584 | * @param int $session_id the id of the user |
||
2585 | * @param array $url_params additonal url parameters |
||
2586 | * @param array $row the row information (the other columns) |
||
2587 | * |
||
2588 | * @return string html code |
||
2589 | */ |
||
2590 | public static function session_tracking_filter($session_id, $url_params, $row) |
||
2591 | { |
||
2592 | $session_id = $row[0]; |
||
2593 | // the table header |
||
2594 | $return = '<table class="table table-hover table-striped data_table" style="width: 100%;border:0;padding:0;border-collapse:collapse;table-layout: fixed">'; |
||
2595 | |||
2596 | // database table definition |
||
2597 | $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE); |
||
2598 | $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE); |
||
2599 | $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); |
||
2600 | $tbl_user = Database::get_main_table(TABLE_MAIN_USER); |
||
2601 | |||
2602 | // getting all the courses of the user |
||
2603 | $sql = "SELECT * FROM $tbl_course AS c |
||
2604 | INNER JOIN $tbl_session_rel_course AS sc |
||
2605 | ON sc.c_id = c.id |
||
2606 | WHERE sc.session_id = '".$session_id."'"; |
||
2607 | $result = Database::query($sql); |
||
2608 | while ($row = Database::fetch_object($result)) { |
||
2609 | $courseId = $row->c_id; |
||
2610 | $courseInfo = api_get_course_info_by_id($courseId); |
||
2611 | $return .= '<tr>'; |
||
2612 | // course code |
||
2613 | $return .= ' <td width="157px" >'.$row->title.'</td>'; |
||
2614 | // get the users in the course |
||
2615 | $sql = "SELECT u.user_id |
||
2616 | FROM $tbl_user AS u |
||
2617 | INNER JOIN $tbl_session_rel_course_rel_user AS scu |
||
2618 | ON u.user_id = scu.user_id |
||
2619 | WHERE scu.session_id = '".$session_id."' AND scu.c_id = '".$courseId."'"; |
||
2620 | $result_users = Database::query($sql); |
||
2621 | $time_spent = 0; |
||
2622 | $progress = 0; |
||
2623 | $nb_progress_lp = 0; |
||
2624 | $score = 0; |
||
2625 | $nb_score_lp = 0; |
||
2626 | $nb_messages = 0; |
||
2627 | $nb_assignments = 0; |
||
2628 | $last_login_date = false; |
||
2629 | $total_score_obtained = 0; |
||
2630 | $total_score_possible = 0; |
||
2631 | $total_questions_answered = 0; |
||
2632 | while ($row_user = Database::fetch_object($result_users)) { |
||
2633 | // get time spent in the course and session |
||
2634 | $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $courseId, $session_id); |
||
2635 | $progress_tmp = Tracking::get_avg_student_progress($row_user->user_id, $row->code, [], $session_id, true); |
||
2636 | $progress += $progress_tmp[0]; |
||
2637 | $nb_progress_lp += $progress_tmp[1]; |
||
2638 | $score_tmp = Tracking::get_avg_student_score($row_user->user_id, $row->code, [], $session_id, true); |
||
2639 | if (is_array($score_tmp)) { |
||
2640 | $score += $score_tmp[0]; |
||
2641 | $nb_score_lp += $score_tmp[1]; |
||
2642 | } |
||
2643 | $nb_messages += Tracking::count_student_messages($row_user->user_id, $row->code, $session_id); |
||
2644 | $nb_assignments += Tracking::count_student_assignments($row_user->user_id, $row->code, $session_id); |
||
2645 | $last_login_date_tmp = Tracking::get_last_connection_date_on_the_course( |
||
2646 | $row_user->user_id, |
||
2647 | $courseInfo, |
||
2648 | $session_id, |
||
2649 | false |
||
2650 | ); |
||
2651 | if ($last_login_date_tmp != false && $last_login_date == false) { |
||
2652 | // TODO: To be cleaned. |
||
2653 | $last_login_date = $last_login_date_tmp; |
||
2654 | } elseif ($last_login_date_tmp != false && $last_login_date != false) { |
||
2655 | // TODO: Repeated previous condition! To be cleaned. |
||
2656 | // Find the max and assign it to first_login_date |
||
2657 | if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) { |
||
2658 | $last_login_date = $last_login_date_tmp; |
||
2659 | } |
||
2660 | } |
||
2661 | |||
2662 | $exercise_results_tmp = self::exercises_results($row_user->user_id, $row->code, $session_id); |
||
2663 | $total_score_obtained += $exercise_results_tmp['score_obtained']; |
||
2664 | $total_score_possible += $exercise_results_tmp['score_possible']; |
||
2665 | $total_questions_answered += $exercise_results_tmp['questions_answered']; |
||
2666 | } |
||
2667 | if ($nb_progress_lp > 0) { |
||
2668 | $avg_progress = round($progress / $nb_progress_lp, 2); |
||
2669 | } else { |
||
2670 | $avg_progress = 0; |
||
2671 | } |
||
2672 | if ($nb_score_lp > 0) { |
||
2673 | $avg_score = round($score / $nb_score_lp, 2); |
||
2674 | } else { |
||
2675 | $avg_score = '-'; |
||
2676 | } |
||
2677 | if ($last_login_date) { |
||
2678 | $last_login_date = api_convert_and_format_date( |
||
2679 | $last_login_date, |
||
2680 | DATE_FORMAT_SHORT, |
||
2681 | date_default_timezone_get() |
||
2682 | ); |
||
2683 | } else { |
||
2684 | $last_login_date = '-'; |
||
2685 | } |
||
2686 | if ($total_score_possible > 0) { |
||
2687 | $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2); |
||
2688 | } else { |
||
2689 | $total_score_percentage = 0; |
||
2690 | } |
||
2691 | if ($total_score_percentage > 0) { |
||
2692 | $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)'; |
||
2693 | } else { |
||
2694 | $total_score = '-'; |
||
2695 | } |
||
2696 | // time spent in the course |
||
2697 | $return .= ' <td><div>'.api_time_to_hms($time_spent).'</div></td>'; |
||
2698 | // student progress in course |
||
2699 | $return .= ' <td><div>'.$avg_progress.'</div></td>'; |
||
2700 | // student score |
||
2701 | $return .= ' <td><div>'.$avg_score.'</div></td>'; |
||
2702 | // student messages |
||
2703 | $return .= ' <td><div>'.$nb_messages.'</div></td>'; |
||
2704 | // student assignments |
||
2705 | $return .= ' <td><div>'.$nb_assignments.'</div></td>'; |
||
2706 | // student exercises results (obtained score, maximum score, number of exercises answered, score percentage) |
||
2707 | $return .= '<td width="105px;">'.$total_score.'</td>'; |
||
2708 | $return .= '<td>'.$total_questions_answered.'</td>'; |
||
2709 | // last connection |
||
2710 | $return .= ' <td><div>'.$last_login_date.'</div></td>'; |
||
2711 | $return .= '<tr>'; |
||
2712 | } |
||
2713 | $return .= '</table>'; |
||
2714 | |||
2715 | return $return; |
||
2716 | } |
||
2717 | |||
2718 | /** |
||
2719 | * This function exports the table that we see in display_tracking_session_overview(). |
||
2720 | */ |
||
2721 | public static function export_tracking_session_overview() |
||
2722 | { |
||
2723 | // database table definition |
||
2724 | $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE); |
||
2725 | $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE); |
||
2726 | $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); |
||
2727 | $tbl_user = Database::get_main_table(TABLE_MAIN_USER); |
||
2728 | |||
2729 | // the values of the sortable table |
||
2730 | $from = 0; |
||
2731 | if ($_GET['tracking_session_overview_page_nr']) { |
||
2732 | $from = $_GET['tracking_session_overview_page_nr']; |
||
2733 | } |
||
2734 | |||
2735 | $orderby = 0; |
||
2736 | if ($_GET['tracking_session_overview_column']) { |
||
2737 | $orderby = $_GET['tracking_session_overview_column']; |
||
2738 | } |
||
2739 | |||
2740 | $direction = 'ASC'; |
||
2741 | if ($_GET['tracking_session_overview_direction']) { |
||
2742 | $direction = $_GET['tracking_session_overview_direction']; |
||
2743 | } |
||
2744 | |||
2745 | $session_data = self::get_session_data_tracking_overview($from, 1000, $orderby, $direction); |
||
2746 | |||
2747 | $csv_content = []; |
||
2748 | |||
2749 | // the first line of the csv file with the column headers |
||
2750 | $csv_row = []; |
||
2751 | $csv_row[] = get_lang('Session'); |
||
2752 | $csv_row[] = get_lang('Course'); |
||
2753 | $csv_row[] = get_lang('AvgTimeSpentInTheCourse'); |
||
2754 | $csv_row[] = get_lang('AvgStudentsProgress'); |
||
2755 | $csv_row[] = get_lang('AvgCourseScore'); |
||
2756 | $csv_row[] = get_lang('TotalNumberOfMessages'); |
||
2757 | $csv_row[] = get_lang('TotalNumberOfAssignments'); |
||
2758 | $csv_row[] = get_lang('TotalExercisesScoreObtained'); |
||
2759 | $csv_row[] = get_lang('TotalExercisesScorePossible'); |
||
2760 | $csv_row[] = get_lang('TotalExercisesAnswered'); |
||
2761 | $csv_row[] = get_lang('TotalExercisesScorePercentage'); |
||
2762 | $csv_row[] = get_lang('LatestLogin'); |
||
2763 | $csv_content[] = $csv_row; |
||
2764 | |||
2765 | // the other lines (the data) |
||
2766 | foreach ($session_data as $key => $session) { |
||
2767 | $session_id = $session[0]; |
||
2768 | $session_title = $session[1]; |
||
2769 | |||
2770 | // getting all the courses of the session |
||
2771 | $sql = "SELECT * FROM $tbl_course AS c |
||
2772 | INNER JOIN $tbl_session_rel_course AS sc |
||
2773 | ON sc.c_id = c.id |
||
2774 | WHERE sc.session_id = '".$session_id."';"; |
||
2775 | $result = Database::query($sql); |
||
2776 | while ($row = Database::fetch_object($result)) { |
||
2777 | $courseId = $row->c_id; |
||
2778 | $courseInfo = api_get_course_info_by_id($courseId); |
||
2779 | $csv_row = []; |
||
2780 | $csv_row[] = $session_title; |
||
2781 | $csv_row[] = $row->title; |
||
2782 | // get the users in the course |
||
2783 | $sql = "SELECT scu.user_id |
||
2784 | FROM $tbl_user AS u |
||
2785 | INNER JOIN $tbl_session_rel_course_rel_user AS scu |
||
2786 | ON u.user_id = scu.user_id |
||
2787 | WHERE scu.session_id = '".$session_id."' AND scu.c_id = '".$courseId."'"; |
||
2788 | $result_users = Database::query($sql); |
||
2789 | $time_spent = 0; |
||
2790 | $progress = 0; |
||
2791 | $nb_progress_lp = 0; |
||
2792 | $score = 0; |
||
2793 | $nb_score_lp = 0; |
||
2794 | $nb_messages = 0; |
||
2795 | $nb_assignments = 0; |
||
2796 | $last_login_date = false; |
||
2797 | $total_score_obtained = 0; |
||
2798 | $total_score_possible = 0; |
||
2799 | $total_questions_answered = 0; |
||
2800 | while ($row_user = Database::fetch_object($result_users)) { |
||
2801 | // get time spent in the course and session |
||
2802 | $time_spent += Tracking::get_time_spent_on_the_course($row_user->user_id, $courseId, $session_id); |
||
2803 | $progress_tmp = Tracking::get_avg_student_progress( |
||
2804 | $row_user->user_id, |
||
2805 | $row->code, |
||
2806 | [], |
||
2807 | $session_id, |
||
2808 | true |
||
2809 | ); |
||
2810 | $progress += $progress_tmp[0]; |
||
2811 | $nb_progress_lp += $progress_tmp[1]; |
||
2812 | $score_tmp = Tracking::get_avg_student_score( |
||
2813 | $row_user->user_id, |
||
2814 | $row->code, |
||
2815 | [], |
||
2816 | $session_id, |
||
2817 | true |
||
2818 | ); |
||
2819 | if (is_array($score_tmp)) { |
||
2820 | $score += $score_tmp[0]; |
||
2821 | $nb_score_lp += $score_tmp[1]; |
||
2822 | } |
||
2823 | $nb_messages += Tracking::count_student_messages( |
||
2824 | $row_user->user_id, |
||
2825 | $row->code, |
||
2826 | $session_id |
||
2827 | ); |
||
2828 | |||
2829 | $nb_assignments += Tracking::count_student_assignments( |
||
2830 | $row_user->user_id, |
||
2831 | $row->code, |
||
2832 | $session_id |
||
2833 | ); |
||
2834 | |||
2835 | $last_login_date_tmp = Tracking:: get_last_connection_date_on_the_course( |
||
2836 | $row_user->user_id, |
||
2837 | $courseInfo, |
||
2838 | $session_id, |
||
2839 | false |
||
2840 | ); |
||
2841 | if ($last_login_date_tmp != false && $last_login_date == false) { |
||
2842 | // TODO: To be cleaned. |
||
2843 | $last_login_date = $last_login_date_tmp; |
||
2844 | } elseif ($last_login_date_tmp != false && $last_login_date == false) { |
||
2845 | // TODO: Repeated previous condition. To be cleaned. |
||
2846 | // Find the max and assign it to first_login_date |
||
2847 | if (strtotime($last_login_date_tmp) > strtotime($last_login_date)) { |
||
2848 | $last_login_date = $last_login_date_tmp; |
||
2849 | } |
||
2850 | } |
||
2851 | |||
2852 | $exercise_results_tmp = self::exercises_results($row_user->user_id, $row->code, $session_id); |
||
2853 | $total_score_obtained += $exercise_results_tmp['score_obtained']; |
||
2854 | $total_score_possible += $exercise_results_tmp['score_possible']; |
||
2855 | $total_questions_answered += $exercise_results_tmp['questions_answered']; |
||
2856 | } |
||
2857 | if ($nb_progress_lp > 0) { |
||
2858 | $avg_progress = round($progress / $nb_progress_lp, 2); |
||
2859 | } else { |
||
2860 | $avg_progress = 0; |
||
2861 | } |
||
2862 | if ($nb_score_lp > 0) { |
||
2863 | $avg_score = round($score / $nb_score_lp, 2); |
||
2864 | } else { |
||
2865 | $avg_score = '-'; |
||
2866 | } |
||
2867 | if ($last_login_date) { |
||
2868 | $last_login_date = api_convert_and_format_date( |
||
2869 | $last_login_date, |
||
2870 | DATE_FORMAT_SHORT, |
||
2871 | date_default_timezone_get() |
||
2872 | ); |
||
2873 | } else { |
||
2874 | $last_login_date = '-'; |
||
2875 | } |
||
2876 | if ($total_score_possible > 0) { |
||
2877 | $total_score_percentage = round($total_score_obtained / $total_score_possible * 100, 2); |
||
2878 | } else { |
||
2879 | $total_score_percentage = 0; |
||
2880 | } |
||
2881 | if ($total_score_percentage > 0) { |
||
2882 | $total_score = $total_score_obtained.'/'.$total_score_possible.' ('.$total_score_percentage.' %)'; |
||
2883 | } else { |
||
2884 | $total_score = '-'; |
||
2885 | } |
||
2886 | // time spent in the course |
||
2887 | $csv_row[] = api_time_to_hms($time_spent); |
||
2888 | // student progress in course |
||
2889 | $csv_row[] = $avg_progress; |
||
2890 | // student score |
||
2891 | $csv_row[] = $avg_score; |
||
2892 | // student messages |
||
2893 | $csv_row[] = $nb_messages; |
||
2894 | // student assignments |
||
2895 | $csv_row[] = $nb_assignments; |
||
2896 | // student exercises results (obtained score, maximum score, number of exercises answered, score percentage) |
||
2897 | $csv_row[] = $total_score_obtained; |
||
2898 | $csv_row[] = $total_score_possible; |
||
2899 | $csv_row[] = $total_questions_answered; |
||
2900 | $csv_row[] = $total_score_percentage; |
||
2901 | // last connection |
||
2902 | $csv_row[] = $last_login_date; |
||
2903 | $csv_content[] = $csv_row; |
||
2904 | } |
||
2905 | } |
||
2906 | Export::arrayToCsv($csv_content, 'reporting_session_overview'); |
||
2907 | exit; |
||
2908 | } |
||
2909 | |||
2910 | /** |
||
2911 | * Get general information about the exercise performance of the user |
||
2912 | * the total obtained score (all the score on all the questions) |
||
2913 | * the maximum score that could be obtained |
||
2914 | * the number of questions answered |
||
2915 | * the success percentage. |
||
2916 | * |
||
2917 | * @param int $user_id the id of the user |
||
2918 | * @param string $course_code the course code |
||
2919 | * @param int $session_id |
||
2920 | * |
||
2921 | * @return array |
||
2922 | * |
||
2923 | * @author Patrick Cool <[email protected]>, Ghent University, Belgium |
||
2924 | * |
||
2925 | * @version Dokeos 1.8.6 |
||
2926 | * |
||
2927 | * @since November 2008 |
||
2928 | */ |
||
2929 | public static function exercises_results($user_id, $course_code, $session_id = 0) |
||
2930 | { |
||
2931 | $user_id = (int) $user_id; |
||
2932 | $courseId = api_get_course_int_id($course_code); |
||
2933 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES); |
||
2934 | |||
2935 | $sql = "SELECT exe_result, exe_weighting |
||
2936 | FROM $table |
||
2937 | WHERE |
||
2938 | c_id = $courseId AND |
||
2939 | exe_user_id = $user_id"; |
||
2940 | |||
2941 | $session_id = (int) $session_id; |
||
2942 | if (!empty($session_id)) { |
||
2943 | $sql .= " AND session_id = '".$session_id."' "; |
||
2944 | } |
||
2945 | $result = Database::query($sql); |
||
2946 | $score_obtained = 0; |
||
2947 | $score_possible = 0; |
||
2948 | $questions_answered = 0; |
||
2949 | while ($row = Database::fetch_array($result)) { |
||
2950 | $score_obtained += $row['exe_result']; |
||
2951 | $score_possible += $row['exe_weighting']; |
||
2952 | $questions_answered++; |
||
2953 | } |
||
2954 | |||
2955 | $percentage = null; |
||
2956 | if ($score_possible != 0) { |
||
2957 | $percentage = round(($score_obtained / $score_possible * 100), 2); |
||
2958 | } |
||
2959 | |||
2960 | return [ |
||
2961 | 'score_obtained' => $score_obtained, |
||
2962 | 'score_possible' => $score_possible, |
||
2963 | 'questions_answered' => $questions_answered, |
||
2964 | 'percentage' => $percentage, |
||
2965 | ]; |
||
2966 | } |
||
2967 | |||
2968 | /** |
||
2969 | * This function exports the table that we see in display_tracking_user_overview(). |
||
2970 | * |
||
2971 | * @author Patrick Cool <[email protected]>, Ghent University, Belgium |
||
2972 | * |
||
2973 | * @version Dokeos 1.8.6 |
||
2974 | * |
||
2975 | * @since October 2008 |
||
2976 | */ |
||
2977 | public static function export_tracking_user_overview() |
||
2978 | { |
||
2979 | // database table definitions |
||
2980 | $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER); |
||
2981 | $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT); |
||
2982 | |||
2983 | // the values of the sortable table |
||
2984 | if ($_GET['tracking_user_overview_page_nr']) { |
||
2985 | $from = $_GET['tracking_user_overview_page_nr']; |
||
2986 | } else { |
||
2987 | $from = 0; |
||
2988 | } |
||
2989 | if ($_GET['tracking_user_overview_column']) { |
||
2990 | $orderby = $_GET['tracking_user_overview_column']; |
||
2991 | } else { |
||
2992 | $orderby = 0; |
||
2993 | } |
||
2994 | if ($is_western_name_order != api_is_western_name_order() && ($orderby == 1 || $orderby == 2)) { |
||
2995 | // Swapping the sorting column if name order for export is different than the common name order. |
||
2996 | $orderby = 3 - $orderby; |
||
2997 | } |
||
2998 | if ($_GET['tracking_user_overview_direction']) { |
||
2999 | $direction = $_GET['tracking_user_overview_direction']; |
||
3000 | } else { |
||
3001 | $direction = 'ASC'; |
||
3002 | } |
||
3003 | |||
3004 | $user_data = self::get_user_data_tracking_overview( |
||
3005 | $from, |
||
3006 | 1000, |
||
3007 | $orderby, |
||
3008 | $direction |
||
3009 | ); |
||
3010 | |||
3011 | // the first line of the csv file with the column headers |
||
3012 | $csv_row = []; |
||
3013 | $csv_row[] = get_lang('OfficialCode'); |
||
3014 | if ($is_western_name_order) { |
||
3015 | $csv_row[] = get_lang('FirstName'); |
||
3016 | $csv_row[] = get_lang('LastName'); |
||
3017 | } else { |
||
3018 | $csv_row[] = get_lang('LastName'); |
||
3019 | $csv_row[] = get_lang('FirstName'); |
||
3020 | } |
||
3021 | $csv_row[] = get_lang('LoginName'); |
||
3022 | $csv_row[] = get_lang('CourseCode'); |
||
3023 | |||
3024 | // the additional user defined fields (only those that were selected to be exported) |
||
3025 | $fields = UserManager::get_extra_fields(0, 50, 5, 'ASC'); |
||
3026 | |||
3027 | $additionalExportFields = Session::read('additional_export_fields'); |
||
3028 | |||
3029 | if (is_array($additionalExportFields)) { |
||
3030 | foreach ($additionalExportFields as $key => $extra_field_export) { |
||
3031 | $csv_row[] = $fields[$extra_field_export][3]; |
||
3032 | $field_names_to_be_exported[] = 'extra_'.$fields[$extra_field_export][1]; |
||
3033 | } |
||
3034 | } |
||
3035 | $csv_row[] = get_lang('AvgTimeSpentInTheCourse', ''); |
||
3036 | $csv_row[] = get_lang('AvgStudentsProgress', ''); |
||
3037 | $csv_row[] = get_lang('AvgCourseScore', ''); |
||
3038 | $csv_row[] = get_lang('AvgExercisesScore', ''); |
||
3039 | $csv_row[] = get_lang('AvgMessages', ''); |
||
3040 | $csv_row[] = get_lang('AvgAssignments', ''); |
||
3041 | $csv_row[] = get_lang('TotalExercisesScoreObtained', ''); |
||
3042 | $csv_row[] = get_lang('TotalExercisesScorePossible', ''); |
||
3043 | $csv_row[] = get_lang('TotalExercisesAnswered', ''); |
||
3044 | $csv_row[] = get_lang('TotalExercisesScorePercentage', ''); |
||
3045 | $csv_row[] = get_lang('FirstLogin', ''); |
||
3046 | $csv_row[] = get_lang('LatestLogin', ''); |
||
3047 | $csv_content[] = $csv_row; |
||
3048 | |||
3049 | // the other lines (the data) |
||
3050 | foreach ($user_data as $key => $user) { |
||
3051 | // getting all the courses of the user |
||
3052 | $sql = "SELECT * FROM $tbl_course_user |
||
3053 | WHERE user_id = '".intval($user[4])."' AND relation_type<>".COURSE_RELATION_TYPE_RRHH." "; |
||
3054 | $result = Database::query($sql); |
||
3055 | while ($row = Database::fetch_row($result)) { |
||
3056 | $courseInfo = api_get_course_info($row['course_code']); |
||
3057 | $courseId = $courseInfo['real_id']; |
||
3058 | |||
3059 | $csv_row = []; |
||
3060 | // user official code |
||
3061 | $csv_row[] = $user[0]; |
||
3062 | // user first|last name |
||
3063 | $csv_row[] = $user[1]; |
||
3064 | // user last|first name |
||
3065 | $csv_row[] = $user[2]; |
||
3066 | // user login name |
||
3067 | $csv_row[] = $user[3]; |
||
3068 | // course code |
||
3069 | $csv_row[] = $row[0]; |
||
3070 | // the additional defined user fields |
||
3071 | $extra_fields = self::get_user_overview_export_extra_fields($user[4]); |
||
3072 | |||
3073 | if (is_array($field_names_to_be_exported)) { |
||
3074 | foreach ($field_names_to_be_exported as $key => $extra_field_export) { |
||
3075 | $csv_row[] = $extra_fields[$extra_field_export]; |
||
3076 | } |
||
3077 | } |
||
3078 | // time spent in the course |
||
3079 | $csv_row[] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user[4], $courseId)); |
||
3080 | // student progress in course |
||
3081 | $csv_row[] = round(Tracking::get_avg_student_progress($user[4], $row[0]), 2); |
||
3082 | // student score |
||
3083 | $csv_row[] = round(Tracking::get_avg_student_score($user[4], $row[0]), 2); |
||
3084 | // student tes score |
||
3085 | $csv_row[] = round(Tracking::get_avg_student_exercise_score($user[4], $row[0]), 2); |
||
3086 | // student messages |
||
3087 | $csv_row[] = Tracking::count_student_messages($user[4], $row[0]); |
||
3088 | // student assignments |
||
3089 | $csv_row[] = Tracking::count_student_assignments($user[4], $row[0]); |
||
3090 | // student exercises results |
||
3091 | $exercises_results = self::exercises_results($user[4], $row[0]); |
||
3092 | $csv_row[] = $exercises_results['score_obtained']; |
||
3093 | $csv_row[] = $exercises_results['score_possible']; |
||
3094 | $csv_row[] = $exercises_results['questions_answered']; |
||
3095 | $csv_row[] = $exercises_results['percentage']; |
||
3096 | // first connection |
||
3097 | $csv_row[] = Tracking::get_first_connection_date_on_the_course($user[4], $courseId); |
||
3098 | // last connection |
||
3099 | $csv_row[] = strip_tags(Tracking::get_last_connection_date_on_the_course($user[4], $courseInfo)); |
||
3100 | |||
3101 | $csv_content[] = $csv_row; |
||
3102 | } |
||
3103 | } |
||
3104 | Export::arrayToCsv($csv_content, 'reporting_user_overview'); |
||
3105 | exit; |
||
3106 | } |
||
3107 | |||
3108 | /** |
||
3109 | * Get data for courses list in sortable with pagination. |
||
3110 | * |
||
3111 | * @return array |
||
3112 | */ |
||
3113 | public static function get_course_data($from, $number_of_items, $column, $direction) |
||
3114 | { |
||
3115 | global $courses, $csv_content, $charset, $session_id; |
||
3116 | |||
3117 | // definition database tables |
||
3118 | $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE); |
||
3119 | $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER); |
||
3120 | $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); |
||
3121 | |||
3122 | $course_data = []; |
||
3123 | $courses_code = array_keys($courses); |
||
3124 | |||
3125 | foreach ($courses_code as &$code) { |
||
3126 | $code = "'$code'"; |
||
3127 | } |
||
3128 | |||
3129 | // get all courses with limit |
||
3130 | $sql = "SELECT course.code as col1, course.title as col2 |
||
3131 | FROM $tbl_course course |
||
3132 | WHERE course.code IN (".implode(',', $courses_code).")"; |
||
3133 | |||
3134 | if (!in_array($direction, ['ASC', 'DESC'])) { |
||
3135 | $direction = 'ASC'; |
||
3136 | } |
||
3137 | |||
3138 | $column = (int) $column; |
||
3139 | $from = (int) $from; |
||
3140 | $number_of_items = (int) $number_of_items; |
||
3141 | |||
3142 | $sql .= " ORDER BY col$column $direction "; |
||
3143 | $sql .= " LIMIT $from,$number_of_items"; |
||
3144 | |||
3145 | $res = Database::query($sql); |
||
3146 | while ($row_course = Database::fetch_row($res)) { |
||
3147 | $course_code = $row_course[0]; |
||
3148 | $courseInfo = api_get_course_info($course_code); |
||
3149 | $courseId = $courseInfo['real_id']; |
||
3150 | $avg_assignments_in_course = $avg_messages_in_course = $nb_students_in_course = $avg_progress_in_course = $avg_score_in_course = $avg_time_spent_in_course = $avg_score_in_exercise = 0; |
||
3151 | |||
3152 | // students directly subscribed to the course |
||
3153 | if (empty($session_id)) { |
||
3154 | $sql = "SELECT user_id |
||
3155 | FROM $tbl_course_user as course_rel_user |
||
3156 | WHERE |
||
3157 | course_rel_user.status='5' AND |
||
3158 | course_rel_user.c_id = '$courseId'"; |
||
3159 | } else { |
||
3160 | $sql = "SELECT user_id FROM $tbl_session_course_user srcu |
||
3161 | WHERE |
||
3162 | c_id = '$courseId' AND |
||
3163 | session_id = '$session_id' AND |
||
3164 | status<>2"; |
||
3165 | } |
||
3166 | $rs = Database::query($sql); |
||
3167 | $users = []; |
||
3168 | while ($row = Database::fetch_array($rs)) { |
||
3169 | $users[] = $row['user_id']; |
||
3170 | } |
||
3171 | |||
3172 | if (count($users) > 0) { |
||
3173 | $nb_students_in_course = count($users); |
||
3174 | $avg_assignments_in_course = Tracking::count_student_assignments($users, $course_code, $session_id); |
||
3175 | $avg_messages_in_course = Tracking::count_student_messages($users, $course_code, $session_id); |
||
3176 | $avg_progress_in_course = Tracking::get_avg_student_progress($users, $course_code, [], $session_id); |
||
3177 | $avg_score_in_course = Tracking::get_avg_student_score($users, $course_code, [], $session_id); |
||
3178 | $avg_score_in_exercise = Tracking::get_avg_student_exercise_score($users, $course_code, 0, $session_id); |
||
3179 | $avg_time_spent_in_course = Tracking::get_time_spent_on_the_course( |
||
3180 | $users, |
||
3181 | $courseInfo['real_id'], |
||
3182 | $session_id |
||
3183 | ); |
||
3184 | |||
3185 | $avg_progress_in_course = round($avg_progress_in_course / $nb_students_in_course, 2); |
||
3186 | if (is_numeric($avg_score_in_course)) { |
||
3187 | $avg_score_in_course = round($avg_score_in_course / $nb_students_in_course, 2); |
||
3188 | } |
||
3189 | $avg_time_spent_in_course = api_time_to_hms($avg_time_spent_in_course / $nb_students_in_course); |
||
3190 | } else { |
||
3191 | $avg_time_spent_in_course = null; |
||
3192 | $avg_progress_in_course = null; |
||
3193 | $avg_score_in_course = null; |
||
3194 | $avg_score_in_exercise = null; |
||
3195 | $avg_messages_in_course = null; |
||
3196 | $avg_assignments_in_course = null; |
||
3197 | } |
||
3198 | $table_row = []; |
||
3199 | $table_row[] = $row_course[1]; |
||
3200 | $table_row[] = $nb_students_in_course; |
||
3201 | $table_row[] = $avg_time_spent_in_course; |
||
3202 | $table_row[] = is_null($avg_progress_in_course) ? '' : $avg_progress_in_course.'%'; |
||
3203 | $table_row[] = is_null($avg_score_in_course) ? '' : $avg_score_in_course.'%'; |
||
3204 | $table_row[] = is_null($avg_score_in_exercise) ? '' : $avg_score_in_exercise.'%'; |
||
3205 | $table_row[] = $avg_messages_in_course; |
||
3206 | $table_row[] = $avg_assignments_in_course; |
||
3207 | |||
3208 | //set the "from" value to know if I access the Reporting by the chamilo tab or the course link |
||
3209 | $table_row[] = '<center><a href="../../tracking/courseLog.php?cidReq='.$course_code.'&from=myspace&id_session='.$session_id.'"> |
||
3210 | '.Display::return_icon('2rightarrow.png', get_lang('Details')).' |
||
3211 | </a> |
||
3212 | </center>'; |
||
3213 | |||
3214 | $scoreInCourse = null; |
||
3215 | if (null !== $avg_score_in_course) { |
||
3216 | if (is_numeric($avg_score_in_course)) { |
||
3217 | $scoreInCourse = $avg_score_in_course.'%'; |
||
3218 | } else { |
||
3219 | $scoreInCourse = $avg_score_in_course; |
||
3220 | } |
||
3221 | } |
||
3222 | |||
3223 | $csv_content[] = [ |
||
3224 | api_html_entity_decode($row_course[1], ENT_QUOTES, $charset), |
||
3225 | $nb_students_in_course, |
||
3226 | $avg_time_spent_in_course, |
||
3227 | is_null($avg_progress_in_course) ? null : $avg_progress_in_course.'%', |
||
3228 | $scoreInCourse, |
||
3229 | is_null($avg_score_in_exercise) ? null : $avg_score_in_exercise.'%', |
||
3230 | $avg_messages_in_course, |
||
3231 | $avg_assignments_in_course, |
||
3232 | ]; |
||
3233 | $course_data[] = $table_row; |
||
3234 | } |
||
3235 | |||
3236 | return $course_data; |
||
3237 | } |
||
3238 | |||
3239 | /** |
||
3240 | * Get the number of users of the platform. |
||
3241 | * |
||
3242 | * @return int |
||
3243 | */ |
||
3244 | public static function get_number_of_users_tracking_overview() |
||
3245 | { |
||
3246 | return UserManager::get_number_of_users(0, api_get_current_access_url_id()); |
||
3247 | } |
||
3248 | |||
3249 | /** |
||
3250 | * Get all the data for the sortable table of the reporting progress of |
||
3251 | * all users and all the courses the user is subscribed to. |
||
3252 | * |
||
3253 | * @param int $from |
||
3254 | * @param int $numberItems |
||
3255 | * @param int $column |
||
3256 | * @param string $direction |
||
3257 | * |
||
3258 | * @return array |
||
3259 | */ |
||
3260 | public static function get_user_data_tracking_overview($from, $numberItems, $column, $direction) |
||
3261 | { |
||
3262 | $isWestern = api_is_western_name_order(); |
||
3263 | |||
3264 | switch ($column) { |
||
3265 | case '0': |
||
3266 | $column = $isWestern ? 'firstname' : 'lastname'; |
||
3267 | break; |
||
3268 | } |
||
3269 | |||
3270 | $order = [ |
||
3271 | " `$column` $direction", |
||
3272 | ]; |
||
3273 | $userList = UserManager::get_user_list([], $order, $from, $numberItems); |
||
3274 | $return = []; |
||
3275 | foreach ($userList as $user) { |
||
3276 | $return[] = [ |
||
3277 | '0' => $user['user_id'], |
||
3278 | 'col0' => $user['user_id'], |
||
3279 | ]; |
||
3280 | } |
||
3281 | |||
3282 | return $return; |
||
3283 | } |
||
3284 | |||
3285 | /** |
||
3286 | * Get all information that the user with user_id = $user_data has |
||
3287 | * entered in the additionally defined profile fields. |
||
3288 | * |
||
3289 | * @param int $user_id the id of the user |
||
3290 | * |
||
3291 | * @return array |
||
3292 | * |
||
3293 | * @author Patrick Cool <[email protected]>, Ghent University, Belgium |
||
3294 | * |
||
3295 | * @version Dokeos 1.8.6 |
||
3296 | * |
||
3297 | * @since November 2008 |
||
3298 | */ |
||
3299 | public static function get_user_overview_export_extra_fields($user_id) |
||
3300 | { |
||
3301 | // include the user manager |
||
3302 | $data = UserManager::get_extra_user_data($user_id, true); |
||
3303 | |||
3304 | return $data; |
||
3305 | } |
||
3306 | |||
3307 | /** |
||
3308 | * Checks if a username exist in the DB otherwise it create a "double" |
||
3309 | * i.e. if we look into for jmontoya but the user's name already exist we create the user jmontoya2 |
||
3310 | * the return array will be array(username=>'jmontoya', sufix='2'). |
||
3311 | * |
||
3312 | * @param string firstname |
||
3313 | * @param string lastname |
||
3314 | * @param string username |
||
3315 | * |
||
3316 | * @return array with the username, the sufix |
||
3317 | * |
||
3318 | * @author Julio Montoya |
||
3319 | */ |
||
3320 | public static function make_username($firstname, $lastname, $username, $language = null, $encoding = null) |
||
3321 | { |
||
3322 | // if username exist |
||
3323 | if (!UserManager::is_username_available($username) || empty($username)) { |
||
3324 | $i = 0; |
||
3325 | while (1) { |
||
3326 | if ($i == 0) { |
||
3327 | $sufix = ''; |
||
3328 | } else { |
||
3329 | $sufix = $i; |
||
3330 | } |
||
3331 | $desired_username = UserManager::create_username( |
||
3332 | $firstname, |
||
3333 | $lastname |
||
3334 | ); |
||
3335 | if (UserManager::is_username_available($desired_username.$sufix)) { |
||
3336 | break; |
||
3337 | } else { |
||
3338 | $i++; |
||
3339 | } |
||
3340 | } |
||
3341 | $username_array = ['username' => $desired_username, 'sufix' => $sufix]; |
||
3342 | |||
3343 | return $username_array; |
||
3344 | } else { |
||
3345 | $username_array = ['username' => $username, 'sufix' => '']; |
||
3346 | |||
3347 | return $username_array; |
||
3348 | } |
||
3349 | } |
||
3350 | |||
3351 | /** |
||
3352 | * Checks if there are repeted users in a given array. |
||
3353 | * |
||
3354 | * @param array $usernames list of the usernames in the uploaded file |
||
3355 | * @param array $user_array $user_array['username'] and $user_array['sufix'] |
||
3356 | * where suffix is the number part in a login i.e -> jmontoya2 |
||
3357 | * |
||
3358 | * @return array with the $usernames array and the $user_array array |
||
3359 | * |
||
3360 | * @author Julio Montoya |
||
3361 | */ |
||
3362 | public static function check_user_in_array($usernames, $user_array) |
||
3363 | { |
||
3364 | $user_list = array_keys($usernames); |
||
3365 | $username = $user_array['username'].$user_array['sufix']; |
||
3366 | |||
3367 | if (in_array($username, $user_list)) { |
||
3368 | $user_array['sufix'] += $usernames[$username]; |
||
3369 | $usernames[$username]++; |
||
3370 | } else { |
||
3371 | $usernames[$username] = 1; |
||
3372 | } |
||
3373 | $result_array = [$usernames, $user_array]; |
||
3374 | |||
3375 | return $result_array; |
||
3376 | } |
||
3377 | |||
3378 | /** |
||
3379 | * Checks whether a username has been already subscribed in a session. |
||
3380 | * |
||
3381 | * @param string $username a given username |
||
3382 | * @param array $course_list the array with the course list id |
||
3383 | * @param int $id_session the session id |
||
3384 | * |
||
3385 | * @return int 0 if the user is not subscribed otherwise it returns the user_id of the given username |
||
3386 | * |
||
3387 | * @author Julio Montoya |
||
3388 | */ |
||
3389 | public static function user_available_in_session($username, $course_list, $id_session) |
||
3390 | { |
||
3391 | $table_user = Database::get_main_table(TABLE_MAIN_USER); |
||
3392 | $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); |
||
3393 | $id_session = (int) $id_session; |
||
3394 | $username = Database::escape_string($username); |
||
3395 | foreach ($course_list as $courseId) { |
||
3396 | $courseId = (int) $courseId; |
||
3397 | $sql = " SELECT u.user_id FROM $tbl_session_rel_course_rel_user rel |
||
3398 | INNER JOIN $table_user u |
||
3399 | ON (rel.user_id = u.user_id) |
||
3400 | WHERE |
||
3401 | rel.session_id='$id_session' AND |
||
3402 | u.status='5' AND |
||
3403 | u.username ='$username' AND |
||
3404 | rel.c_id='$courseId'"; |
||
3405 | $rs = Database::query($sql); |
||
3406 | if (Database::num_rows($rs) > 0) { |
||
3407 | return Database::result($rs, 0, 0); |
||
3408 | } |
||
3409 | } |
||
3410 | |||
3411 | return 0; |
||
3412 | } |
||
3413 | |||
3414 | /** |
||
3415 | * This function checks whether some users in the uploaded file |
||
3416 | * repeated and creates unique usernames if necesary. |
||
3417 | * A case: Within the file there is an user repeted twice (Julio Montoya / Julio Montoya) |
||
3418 | * and the username fields are empty. |
||
3419 | * Then, this function would create unique usernames based on the first and the last name. |
||
3420 | * Two users wiould be created - jmontoya and jmontoya2. |
||
3421 | * Of course, if in the database there is a user with the name jmontoya, |
||
3422 | * the newly created two users registered would be jmontoya2 and jmontoya3. |
||
3423 | * |
||
3424 | * @param $users list of users |
||
3425 | * |
||
3426 | * @return array |
||
3427 | * |
||
3428 | * @author Julio Montoya Armas |
||
3429 | */ |
||
3430 | public static function check_all_usernames($users, $course_list, $id_session) |
||
3431 | { |
||
3432 | $table_user = Database::get_main_table(TABLE_MAIN_USER); |
||
3433 | $usernames = []; |
||
3434 | $new_users = []; |
||
3435 | foreach ($users as $index => $user) { |
||
3436 | $desired_username = []; |
||
3437 | if (empty($user['UserName'])) { |
||
3438 | $desired_username = self::make_username($user['FirstName'], $user['LastName'], ''); |
||
3439 | $pre_username = $desired_username['username'].$desired_username['sufix']; |
||
3440 | $user['UserName'] = $pre_username; |
||
3441 | $user['create'] = '1'; |
||
3442 | } else { |
||
3443 | if (UserManager::is_username_available($user['UserName'])) { |
||
3444 | $desired_username = self::make_username($user['FirstName'], $user['LastName'], $user['UserName']); |
||
3445 | $user['UserName'] = $desired_username['username'].$desired_username['sufix']; |
||
3446 | $user['create'] = '1'; |
||
3447 | } else { |
||
3448 | $is_session_avail = self::user_available_in_session($user['UserName'], $course_list, $id_session); |
||
3449 | if (0 == $is_session_avail) { |
||
3450 | $user_name = $user['UserName']; |
||
3451 | $sql_select = "SELECT user_id FROM $table_user WHERE username ='$user_name' "; |
||
3452 | $rs = Database::query($sql_select); |
||
3453 | $user['create'] = Database::result($rs, 0, 0); |
||
3454 | } else { |
||
3455 | $user['create'] = $is_session_avail; |
||
3456 | } |
||
3457 | } |
||
3458 | } |
||
3459 | // Usernames is the current list of users in the file. |
||
3460 | $result_array = self::check_user_in_array($usernames, $desired_username); |
||
3461 | $usernames = $result_array[0]; |
||
3462 | $desired_username = $result_array[1]; |
||
3463 | $user['UserName'] = $desired_username['username'].$desired_username['sufix']; |
||
3464 | $new_users[] = $user; |
||
3465 | } |
||
3466 | |||
3467 | return $new_users; |
||
3468 | } |
||
3469 | |||
3470 | /** |
||
3471 | * This functions checks whether there are users that are already |
||
3472 | * registered in the DB by different creator than the current coach. |
||
3473 | * |
||
3474 | * @param array $users |
||
3475 | * |
||
3476 | * @return array |
||
3477 | * |
||
3478 | * @author Julio Montoya Armas |
||
3479 | */ |
||
3480 | public static function get_user_creator($users) |
||
3481 | { |
||
3482 | $errors = []; |
||
3483 | $table_user = Database::get_main_table(TABLE_MAIN_USER); |
||
3484 | foreach ($users as $index => $user) { |
||
3485 | $username = Database::escape_string($user['UserName']); |
||
3486 | $sql = "SELECT creator_id FROM $table_user WHERE username='$username' "; |
||
3487 | |||
3488 | $rs = Database::query($sql); |
||
3489 | $creator_id = Database::result($rs, 0, 0); |
||
3490 | // check if we are the creators or not |
||
3491 | if ($creator_id != '') { |
||
3492 | if ($creator_id != api_get_user_id()) { |
||
3493 | $user['error'] = get_lang('UserAlreadyRegisteredByOtherCreator'); |
||
3494 | $errors[] = $user; |
||
3495 | } |
||
3496 | } |
||
3497 | } |
||
3498 | |||
3499 | return $errors; |
||
3500 | } |
||
3501 | |||
3502 | /** |
||
3503 | * Validates imported data. |
||
3504 | * |
||
3505 | * @param array $users list of users |
||
3506 | */ |
||
3507 | public static function validate_data($users, $id_session = null) |
||
3508 | { |
||
3509 | $errors = []; |
||
3510 | $new_users = []; |
||
3511 | foreach ($users as $index => $user) { |
||
3512 | // 1. Check whether mandatory fields are set. |
||
3513 | $mandatory_fields = ['LastName', 'FirstName']; |
||
3514 | if (api_get_setting('registration', 'email') == 'true') { |
||
3515 | $mandatory_fields[] = 'Email'; |
||
3516 | } |
||
3517 | |||
3518 | foreach ($mandatory_fields as $key => $field) { |
||
3519 | if (!isset($user[$field]) || strlen($user[$field]) == 0) { |
||
3520 | $user['error'] = get_lang($field.'Mandatory'); |
||
3521 | $errors[] = $user; |
||
3522 | } |
||
3523 | } |
||
3524 | // 2. Check whether the username is too long. |
||
3525 | if (UserManager::is_username_too_long($user['UserName'])) { |
||
3526 | $user['error'] = get_lang('UserNameTooLong'); |
||
3527 | $errors[] = $user; |
||
3528 | } |
||
3529 | |||
3530 | $user['UserName'] = trim($user['UserName']); |
||
3531 | |||
3532 | if (empty($user['UserName'])) { |
||
3533 | $user['UserName'] = UserManager::create_username($user['FirstName'], $user['LastName']); |
||
3534 | } |
||
3535 | $new_users[] = $user; |
||
3536 | } |
||
3537 | $results = ['errors' => $errors, 'users' => $new_users]; |
||
3538 | |||
3539 | return $results; |
||
3540 | } |
||
3541 | |||
3542 | /** |
||
3543 | * Adds missing user-information (which isn't required, like password, etc). |
||
3544 | */ |
||
3545 | public static function complete_missing_data($user) |
||
3546 | { |
||
3547 | // 1. Generate a password if it is necessary. |
||
3548 | if (!isset($user['Password']) || strlen($user['Password']) == 0) { |
||
3549 | $user['Password'] = api_generate_password(); |
||
3550 | } |
||
3551 | |||
3552 | return $user; |
||
3553 | } |
||
3554 | |||
3555 | /** |
||
3556 | * Saves imported data. |
||
3557 | */ |
||
3558 | public static function save_data($users, $course_list, $id_session) |
||
3559 | { |
||
3560 | $id_session = (int) $id_session; |
||
3561 | $sendMail = $_POST['sendMail'] ? 1 : 0; |
||
3562 | |||
3563 | // Adding users to the platform. |
||
3564 | $new_users = []; |
||
3565 | foreach ($users as $index => $user) { |
||
3566 | $user = self::complete_missing_data($user); |
||
3567 | // coach only will registered users |
||
3568 | $default_status = STUDENT; |
||
3569 | if ($user['create'] == COURSEMANAGER) { |
||
3570 | $user['id'] = UserManager:: create_user( |
||
3571 | $user['FirstName'], |
||
3572 | $user['LastName'], |
||
3573 | $default_status, |
||
3574 | $user['Email'], |
||
3575 | $user['UserName'], |
||
3576 | $user['Password'], |
||
3577 | $user['OfficialCode'], |
||
3578 | api_get_setting('PlatformLanguage'), |
||
3579 | $user['PhoneNumber'], |
||
3580 | '' |
||
3581 | ); |
||
3582 | $user['added_at_platform'] = 1; |
||
3583 | } else { |
||
3584 | $user['id'] = $user['create']; |
||
3585 | $user['added_at_platform'] = 0; |
||
3586 | } |
||
3587 | $new_users[] = $user; |
||
3588 | } |
||
3589 | // Update user list. |
||
3590 | $users = $new_users; |
||
3591 | |||
3592 | // Inserting users. |
||
3593 | SessionManager::insertUsersInCourses( |
||
3594 | array_column($users, 'id'), |
||
3595 | $course_list, |
||
3596 | $id_session |
||
3597 | ); |
||
3598 | |||
3599 | array_walk( |
||
3600 | $users, |
||
3601 | function (array &$user) { |
||
3602 | $user['added_at_session'] = 1; |
||
3603 | } |
||
3604 | ); |
||
3605 | |||
3606 | $registered_users = get_lang('FileImported').'<br /> Import file results : <br />'; |
||
3607 | // Sending emails. |
||
3608 | $addedto = ''; |
||
3609 | if ($sendMail) { |
||
3610 | foreach ($users as $index => $user) { |
||
3611 | $emailsubject = '['.api_get_setting('siteName').'] '.get_lang('YourReg').' '.api_get_setting('siteName'); |
||
3612 | $emailbody = get_lang('Dear').' '. |
||
3613 | api_get_person_name($user['FirstName'], $user['LastName']).",\n\n". |
||
3614 | get_lang('YouAreReg')." ".api_get_setting('siteName')." ".get_lang('WithTheFollowingSettings')."\n\n". |
||
3615 | get_lang('Username')." : $user[UserName]\n". |
||
3616 | get_lang('Pass')." : $user[Password]\n\n". |
||
3617 | get_lang('Address')." ".api_get_setting('siteName')." ".get_lang('Is')." : ".api_get_path(WEB_PATH)." \n\n". |
||
3618 | get_lang('Problem')."\n\n". |
||
3619 | get_lang('SignatureFormula').",\n\n". |
||
3620 | api_get_person_name(api_get_setting('administratorName'), api_get_setting('administratorSurname'))."\n". |
||
3621 | get_lang('Manager')." ".api_get_setting('siteName')."\nT. ". |
||
3622 | api_get_setting('administratorTelephone')."\n".get_lang('Email')." : ".api_get_setting('emailAdministrator'); |
||
3623 | |||
3624 | MessageManager::send_message_simple($user['id'], $emailsubject, $emailbody); |
||
3625 | |||
3626 | $userInfo = api_get_user_info($user['id']); |
||
3627 | |||
3628 | if (($user['added_at_platform'] == 1 && $user['added_at_session'] == 1) || $user['added_at_session'] == 1) { |
||
3629 | if ($user['added_at_platform'] == 1) { |
||
3630 | $addedto = get_lang('UserCreatedPlatform'); |
||
3631 | } else { |
||
3632 | $addedto = ' '; |
||
3633 | } |
||
3634 | |||
3635 | if ($user['added_at_session'] == 1) { |
||
3636 | $addedto .= get_lang('UserInSession'); |
||
3637 | } |
||
3638 | } else { |
||
3639 | $addedto = get_lang('UserNotAdded'); |
||
3640 | } |
||
3641 | |||
3642 | $registered_users .= UserManager::getUserProfileLink($userInfo).' - '.$addedto.'<br />'; |
||
3643 | } |
||
3644 | } else { |
||
3645 | foreach ($users as $index => $user) { |
||
3646 | $userInfo = api_get_user_info($user['id']); |
||
3647 | if (($user['added_at_platform'] == 1 && $user['added_at_session'] == 1) || $user['added_at_session'] == 1) { |
||
3648 | if ($user['added_at_platform'] == 1) { |
||
3649 | $addedto = get_lang('UserCreatedPlatform'); |
||
3650 | } else { |
||
3651 | $addedto = ' '; |
||
3652 | } |
||
3653 | |||
3654 | if ($user['added_at_session'] == 1) { |
||
3655 | $addedto .= ' '.get_lang('UserInSession'); |
||
3656 | } |
||
3657 | } else { |
||
3658 | $addedto = get_lang('UserNotAdded'); |
||
3659 | } |
||
3660 | $registered_users .= "<a href=\"../user/userInfo.php?uInfo=".$user['id']."\">". |
||
3661 | Security::remove_XSS($userInfo['complete_user_name'])."</a> - ".$addedto.'<br />'; |
||
3662 | } |
||
3663 | } |
||
3664 | Display::addFlash(Display::return_message($registered_users, 'normal', false)); |
||
3665 | header('Location: course.php?id_session='.$id_session); |
||
3666 | exit; |
||
3667 | } |
||
3668 | |||
3669 | /** |
||
3670 | * Reads CSV-file. |
||
3671 | * |
||
3672 | * @param string $file Path to the CSV-file |
||
3673 | * |
||
3674 | * @return array All userinformation read from the file |
||
3675 | */ |
||
3676 | public function parse_csv_data($file) |
||
3677 | { |
||
3678 | $users = Import::csvToArray($file); |
||
3679 | foreach ($users as $index => $user) { |
||
3680 | if (isset($user['Courses'])) { |
||
3681 | $user['Courses'] = explode('|', trim($user['Courses'])); |
||
3682 | } |
||
3683 | $users[$index] = $user; |
||
3684 | } |
||
3685 | |||
3686 | return $users; |
||
3687 | } |
||
3688 | |||
3689 | /** |
||
3690 | * Reads XML-file. |
||
3691 | * |
||
3692 | * @param string $file Path to the XML-file |
||
3693 | * |
||
3694 | * @return array All userinformation read from the file |
||
3695 | */ |
||
3696 | public static function parse_xml_data($file) |
||
3697 | { |
||
3698 | $crawler = Import::xml($file); |
||
3699 | $crawler = $crawler->filter('Contacts > Contact '); |
||
3700 | $array = []; |
||
3701 | foreach ($crawler as $domElement) { |
||
3702 | $row = []; |
||
3703 | foreach ($domElement->childNodes as $node) { |
||
3704 | if ($node->nodeName != '#text') { |
||
3705 | $row[$node->nodeName] = $node->nodeValue; |
||
3706 | } |
||
3707 | } |
||
3708 | if (!empty($row)) { |
||
3709 | $array[] = $row; |
||
3710 | } |
||
3711 | } |
||
3712 | |||
3713 | return $array; |
||
3714 | } |
||
3715 | |||
3716 | /** |
||
3717 | * @param int $courseId |
||
3718 | * @param int $sessionId |
||
3719 | * @param int $studentId |
||
3720 | */ |
||
3721 | public static function displayTrackingAccessOverView( |
||
3722 | $courseId, |
||
3723 | $sessionId, |
||
3724 | $studentId, |
||
3725 | $perPage = 20, |
||
3726 | $dates = null |
||
3727 | ) { |
||
3728 | $courseId = (int) $courseId; |
||
3729 | $sessionId = (int) $sessionId; |
||
3730 | $studentId = (int) $studentId; |
||
3731 | |||
3732 | $courseList = []; |
||
3733 | $sessionList = []; |
||
3734 | $studentList = []; |
||
3735 | |||
3736 | if (!empty($courseId)) { |
||
3737 | $course = api_get_course_entity($courseId); |
||
3738 | if ($course) { |
||
3739 | $courseList[$course->getId()] = $course->getTitle(); |
||
3740 | } |
||
3741 | } |
||
3742 | |||
3743 | if (!empty($sessionId)) { |
||
3744 | $session = api_get_session_entity($sessionId); |
||
3745 | if ($session) { |
||
3746 | $sessionList[$session->getId()] = $session->getName(); |
||
3747 | } |
||
3748 | } |
||
3749 | |||
3750 | if (!empty($studentId)) { |
||
3751 | $student = api_get_user_entity($studentId); |
||
3752 | if ($student) { |
||
3753 | $studentList[$student->getId()] = UserManager::formatUserFullName($student); |
||
3754 | } |
||
3755 | } |
||
3756 | |||
3757 | $form = new FormValidator('access_overview', 'GET'); |
||
3758 | $form->addElement( |
||
3759 | 'select_ajax', |
||
3760 | 'course_id', |
||
3761 | get_lang('SearchCourse'), |
||
3762 | $courseList, |
||
3763 | [ |
||
3764 | 'url' => api_get_path(WEB_AJAX_PATH).'course.ajax.php?'.http_build_query( |
||
3765 | [ |
||
3766 | 'a' => 'search_course_by_session_all', |
||
3767 | 'session_id' => $sessionId, |
||
3768 | 'course_id' => $courseId, |
||
3769 | ] |
||
3770 | ), |
||
3771 | ] |
||
3772 | ); |
||
3773 | |||
3774 | $form->addElement( |
||
3775 | 'select_ajax', |
||
3776 | 'session_id', |
||
3777 | get_lang('SearchSession'), |
||
3778 | $sessionList, |
||
3779 | [ |
||
3780 | 'url_function' => " |
||
3781 | function () { |
||
3782 | var params = $.param({ |
||
3783 | a: 'search_session_by_course', |
||
3784 | course_id: $('#access_overview_course_id').val() || 0 |
||
3785 | }); |
||
3786 | |||
3787 | return '".api_get_path(WEB_AJAX_PATH)."session.ajax.php?' + params; |
||
3788 | } |
||
3789 | ", |
||
3790 | ] |
||
3791 | ); |
||
3792 | |||
3793 | $form->addSelect( |
||
3794 | 'profile', |
||
3795 | get_lang('Profile'), |
||
3796 | [ |
||
3797 | '' => get_lang('Select'), |
||
3798 | STUDENT => get_lang('Student'), |
||
3799 | COURSEMANAGER => get_lang('CourseManager'), |
||
3800 | DRH => get_lang('Drh'), |
||
3801 | ], |
||
3802 | ['id' => 'profile'] |
||
3803 | ); |
||
3804 | |||
3805 | $form->addElement( |
||
3806 | 'select_ajax', |
||
3807 | 'student_id', |
||
3808 | get_lang('SearchUsers'), |
||
3809 | $studentList, |
||
3810 | [ |
||
3811 | 'placeholder' => get_lang('All'), |
||
3812 | 'url_function' => " |
||
3813 | function () { |
||
3814 | var params = $.param({ |
||
3815 | a: 'search_user_by_course', |
||
3816 | session_id: $('#access_overview_session_id').val(), |
||
3817 | course_id: $('#access_overview_course_id').val() |
||
3818 | }); |
||
3819 | |||
3820 | return '".api_get_path(WEB_AJAX_PATH)."course.ajax.php?' + params; |
||
3821 | } |
||
3822 | ", |
||
3823 | ] |
||
3824 | ); |
||
3825 | |||
3826 | $form->addDateRangePicker( |
||
3827 | 'date', |
||
3828 | get_lang('DateRange'), |
||
3829 | true, |
||
3830 | [ |
||
3831 | 'id' => 'date_range', |
||
3832 | 'format' => 'YYYY-MM-DD HH:mm', |
||
3833 | 'timePicker' => 'true', |
||
3834 | //'validate_format' => 'Y-m-d', |
||
3835 | ] |
||
3836 | ); |
||
3837 | |||
3838 | $form->addHidden('display', 'accessoverview'); |
||
3839 | $form->addRule('course_id', get_lang('Required'), 'required'); |
||
3840 | $form->addRule('profile', get_lang('Required'), 'required'); |
||
3841 | $form->addButton('submit', get_lang('Generate'), 'gear', 'primary'); |
||
3842 | |||
3843 | $table = null; |
||
3844 | if (!empty($dates)) { |
||
3845 | //if ($form->validate()) { |
||
3846 | $table = new SortableTable( |
||
3847 | 'tracking_access_overview', |
||
3848 | ['MySpace', 'getNumberOfTrackAccessOverview'], |
||
3849 | ['MySpace', 'getUserDataAccessTrackingOverview'], |
||
3850 | 0, |
||
3851 | $perPage |
||
3852 | ); |
||
3853 | $table->set_additional_parameters( |
||
3854 | [ |
||
3855 | 'course_id' => $courseId, |
||
3856 | 'session_id' => $sessionId, |
||
3857 | 'student_id' => $studentId, |
||
3858 | 'date' => $dates, |
||
3859 | 'tracking_access_overview_per_page' => $perPage, |
||
3860 | 'display' => 'accessoverview', |
||
3861 | ] |
||
3862 | ); |
||
3863 | $table->set_header(0, get_lang('LoginDate'), true); |
||
3864 | $table->set_header(1, get_lang('Username'), true); |
||
3865 | if (api_is_western_name_order()) { |
||
3866 | $table->set_header(2, get_lang('FirstName'), true); |
||
3867 | $table->set_header(3, get_lang('LastName'), true); |
||
3868 | } else { |
||
3869 | $table->set_header(2, get_lang('LastName'), true); |
||
3870 | $table->set_header(3, get_lang('FirstName'), true); |
||
3871 | } |
||
3872 | //$table->set_header(4, get_lang('Clicks'), false); |
||
3873 | $table->set_header(4, get_lang('IP'), false); |
||
3874 | $table->set_header(5, get_lang('TimeLoggedIn'), false); |
||
3875 | } |
||
3876 | |||
3877 | $template = new Template( |
||
3878 | null, |
||
3879 | false, |
||
3880 | false, |
||
3881 | false, |
||
3882 | false, |
||
3883 | false, |
||
3884 | false |
||
3885 | ); |
||
3886 | $template->assign('form', $form->returnForm()); |
||
3887 | $template->assign('table', $table ? $table->return_table() : null); |
||
3888 | |||
3889 | echo $template->fetch( |
||
3890 | $template->get_template('my_space/accessoverview.tpl') |
||
3891 | ); |
||
3892 | } |
||
3893 | |||
3894 | /** |
||
3895 | * @return int |
||
3896 | */ |
||
3897 | public static function getNumberOfTrackAccessOverview() |
||
3898 | { |
||
3899 | $user = Database::get_main_table(TABLE_MAIN_USER); |
||
3900 | $course = Database::get_main_table(TABLE_MAIN_COURSE); |
||
3901 | $trackCourseAccess = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS); |
||
3902 | |||
3903 | $sql = "SELECT COUNT(course_access_id) count |
||
3904 | FROM $trackCourseAccess a |
||
3905 | INNER JOIN $user u |
||
3906 | ON a.user_id = u.id |
||
3907 | INNER JOIN $course c |
||
3908 | ON a.c_id = c.id |
||
3909 | "; |
||
3910 | $sql = self::getDataAccessTrackingFilters($sql); |
||
3911 | |||
3912 | $result = Database::query($sql); |
||
3913 | $row = Database::fetch_assoc($result); |
||
3914 | |||
3915 | if ($row) { |
||
3916 | return $row['count']; |
||
3917 | } |
||
3918 | |||
3919 | return 0; |
||
3920 | } |
||
3921 | |||
3922 | /** |
||
3923 | * @param $from |
||
3924 | * @param $numberItems |
||
3925 | * @param $column |
||
3926 | * @param $orderDirection |
||
3927 | * |
||
3928 | * @return array |
||
3929 | */ |
||
3930 | public static function getUserDataAccessTrackingOverview( |
||
3931 | $from, |
||
3932 | $numberItems, |
||
3933 | $column, |
||
3934 | $orderDirection |
||
3935 | ) { |
||
3936 | $from = (int) $from; |
||
3937 | $numberItems = (int) $numberItems; |
||
3938 | $column = (int) $column; |
||
3939 | $orderDirection = Database::escape_string($orderDirection); |
||
3940 | $orderDirection = !in_array(strtolower(trim($orderDirection)), ['asc', 'desc']) ? 'asc' : $orderDirection; |
||
3941 | |||
3942 | $user = Database::get_main_table(TABLE_MAIN_USER); |
||
3943 | $course = Database::get_main_table(TABLE_MAIN_COURSE); |
||
3944 | $track_e_login = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); |
||
3945 | $trackCourseAccess = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS); |
||
3946 | |||
3947 | global $export_csv; |
||
3948 | $is_western_name_order = api_is_western_name_order(); |
||
3949 | if ($export_csv) { |
||
3950 | $is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT); |
||
3951 | } |
||
3952 | |||
3953 | //TODO add course name |
||
3954 | $sql = "SELECT |
||
3955 | a.login_course_date as col0, |
||
3956 | u.username as col1, |
||
3957 | ".( |
||
3958 | $is_western_name_order ? " |
||
3959 | u.firstname AS col2, |
||
3960 | u.lastname AS col3, |
||
3961 | " : " |
||
3962 | u.lastname AS col2, |
||
3963 | u.firstname AS col3, |
||
3964 | " |
||
3965 | )." |
||
3966 | a.login_course_date, |
||
3967 | a.logout_course_date, |
||
3968 | c.title, |
||
3969 | c.code, |
||
3970 | u.id as user_id, |
||
3971 | user_ip |
||
3972 | FROM $trackCourseAccess a |
||
3973 | INNER JOIN $user u |
||
3974 | ON a.user_id = u.id |
||
3975 | INNER JOIN $course c |
||
3976 | ON a.c_id = c.id |
||
3977 | WHERE 1=1 "; |
||
3978 | |||
3979 | $sql = self::getDataAccessTrackingFilters($sql); |
||
3980 | |||
3981 | $sql .= " ORDER BY col$column $orderDirection "; |
||
3982 | $sql .= " LIMIT $from, $numberItems"; |
||
3983 | |||
3984 | $result = Database::query($sql); |
||
3985 | |||
3986 | $data = []; |
||
3987 | while ($user = Database::fetch_assoc($result)) { |
||
3988 | $data[] = $user; |
||
3989 | } |
||
3990 | |||
3991 | $return = []; |
||
3992 | //TODO: Dont use numeric index |
||
3993 | foreach ($data as $key => $info) { |
||
3994 | $return[] = [ |
||
3995 | api_get_local_time($info['login_course_date']), |
||
3996 | $info['col1'], |
||
3997 | $info['col2'], |
||
3998 | $info['col3'], |
||
3999 | $info['user_ip'], |
||
4000 | gmdate('H:i:s', strtotime($info['logout_course_date']) - strtotime($info['login_course_date'])), |
||
4001 | ]; |
||
4002 | } |
||
4003 | |||
4004 | return $return; |
||
4005 | } |
||
4006 | |||
4007 | /** |
||
4008 | * Gets the connections to a course as an array of login and logout time. |
||
4009 | * |
||
4010 | * @param int $user_id |
||
4011 | * @param array $course_info |
||
4012 | * @param int $sessionId |
||
4013 | * @param string $start_date |
||
4014 | * @param string $end_date |
||
4015 | * @param bool $addUserIp |
||
4016 | * |
||
4017 | * @author Jorge Frisancho Jibaja |
||
4018 | * @author Julio Montoya <[email protected]> fixing the function |
||
4019 | * |
||
4020 | * @version OCT-22- 2010 |
||
4021 | * |
||
4022 | * @return array |
||
4023 | */ |
||
4024 | public static function get_connections_to_course_by_date( |
||
4025 | $user_id, |
||
4026 | $course_info, |
||
4027 | $sessionId, |
||
4028 | $start_date, |
||
4029 | $end_date, |
||
4030 | $addUserIp = false |
||
4031 | ) { |
||
4032 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS); |
||
4033 | $user_id = (int) $user_id; |
||
4034 | $connections = []; |
||
4035 | if (!empty($course_info)) { |
||
4036 | $courseId = (int) $course_info['real_id']; |
||
4037 | $end_date = self::add_day_to($end_date); |
||
4038 | |||
4039 | $start_date = Database::escape_string($start_date); |
||
4040 | $end_date = Database::escape_string($end_date); |
||
4041 | $sessionCondition = api_get_session_condition($sessionId); |
||
4042 | $sql = "SELECT |
||
4043 | login_course_date, |
||
4044 | logout_course_date, |
||
4045 | TIMESTAMPDIFF(SECOND, login_course_date, logout_course_date) duration, |
||
4046 | user_ip |
||
4047 | FROM $table |
||
4048 | WHERE |
||
4049 | user_id = $user_id AND |
||
4050 | c_id = $courseId AND |
||
4051 | login_course_date BETWEEN '$start_date' AND '$end_date' AND |
||
4052 | logout_course_date BETWEEN '$start_date' AND '$end_date' |
||
4053 | $sessionCondition |
||
4054 | ORDER BY login_course_date ASC"; |
||
4055 | $rs = Database::query($sql); |
||
4056 | |||
4057 | while ($row = Database::fetch_array($rs)) { |
||
4058 | $item = [ |
||
4059 | 'login' => $row['login_course_date'], |
||
4060 | 'logout' => $row['logout_course_date'], |
||
4061 | 'duration' => $row['duration'], |
||
4062 | ]; |
||
4063 | if ($addUserIp) { |
||
4064 | $item['user_ip'] = $row['user_ip']; |
||
4065 | } |
||
4066 | $connections[] = $item; |
||
4067 | } |
||
4068 | } |
||
4069 | |||
4070 | return $connections; |
||
4071 | } |
||
4072 | |||
4073 | /** |
||
4074 | * @param int $user_id |
||
4075 | * @param array $course_info |
||
4076 | * @param int $sessionId |
||
4077 | * @param null $start_date |
||
4078 | * @param null $end_date |
||
0 ignored issues
–
show
Documentation
Bug
introduced
by
Loading history...
|
|||
4079 | * |
||
4080 | * @return array |
||
4081 | */ |
||
4082 | public static function getStats($user_id, $course_info, $sessionId, $start_date = null, $end_date = null) |
||
4083 | { |
||
4084 | $table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS); |
||
4085 | $result = []; |
||
4086 | if (!empty($course_info)) { |
||
4087 | $stringStartDate = ''; |
||
4088 | $stringEndDate = ''; |
||
4089 | if ($start_date != null && $end_date != null) { |
||
4090 | $end_date = self::add_day_to($end_date); |
||
4091 | |||
4092 | $start_date = Database::escape_string($start_date); |
||
4093 | $end_date = Database::escape_string($end_date); |
||
4094 | |||
4095 | $stringStartDate = "AND login_course_date BETWEEN '$start_date' AND '$end_date'"; |
||
4096 | $stringEndDate = "AND logout_course_date BETWEEN '$start_date' AND '$end_date'"; |
||
4097 | } |
||
4098 | $user_id = (int) $user_id; |
||
4099 | $courseId = (int) $course_info['real_id']; |
||
4100 | $sessionCondition = api_get_session_condition($sessionId); |
||
4101 | $sql = "SELECT |
||
4102 | SEC_TO_TIME(AVG(time_to_sec(timediff(logout_course_date,login_course_date)))) as avrg, |
||
4103 | SEC_TO_TIME(SUM(time_to_sec(timediff(logout_course_date,login_course_date)))) as total, |
||
4104 | count(user_id) as times |
||
4105 | FROM $table |
||
4106 | WHERE |
||
4107 | user_id = $user_id AND |
||
4108 | c_id = $courseId $stringStartDate $stringEndDate |
||
4109 | $sessionCondition |
||
4110 | ORDER BY login_course_date ASC"; |
||
4111 | |||
4112 | $rs = Database::query($sql); |
||
4113 | if ($row = Database::fetch_array($rs)) { |
||
4114 | $foo_avg = $row['avrg']; |
||
4115 | $foo_total = $row['total']; |
||
4116 | $foo_times = $row['times']; |
||
4117 | $result = [ |
||
4118 | 'avg' => $foo_avg, |
||
4119 | 'total' => $foo_total, |
||
4120 | 'times' => $foo_times, |
||
4121 | ]; |
||
4122 | } |
||
4123 | } |
||
4124 | |||
4125 | return $result; |
||
4126 | } |
||
4127 | |||
4128 | public static function add_day_to($end_date) |
||
4129 | { |
||
4130 | $foo_date = strtotime($end_date); |
||
4131 | $foo_date = strtotime(' +1 day', $foo_date); |
||
4132 | $foo_date = date('Y-m-d', $foo_date); |
||
4133 | |||
4134 | return $foo_date; |
||
4135 | } |
||
4136 | |||
4137 | /** |
||
4138 | * This function draw the graphic to be displayed on the user view as an image. |
||
4139 | * |
||
4140 | * @param array $sql_result |
||
4141 | * @param string $start_date |
||
4142 | * @param string $end_date |
||
4143 | * @param string $type |
||
4144 | * |
||
4145 | * @author Jorge Frisancho Jibaja |
||
4146 | * |
||
4147 | * @version OCT-22- 2010 |
||
4148 | * |
||
4149 | * @return string |
||
4150 | */ |
||
4151 | public static function grapher($sql_result, $start_date, $end_date, $type = '') |
||
4152 | { |
||
4153 | if (empty($start_date)) { |
||
4154 | $start_date = ''; |
||
4155 | } |
||
4156 | if (empty($end_date)) { |
||
4157 | $end_date = ''; |
||
4158 | } |
||
4159 | if ('' == $type) { |
||
4160 | $type = 'day'; |
||
4161 | } |
||
4162 | $main_year = $main_month_year = $main_day = []; |
||
4163 | |||
4164 | $period = new DatePeriod( |
||
4165 | new DateTime($start_date), |
||
4166 | new DateInterval('P1D'), |
||
4167 | new DateTime($end_date) |
||
4168 | ); |
||
4169 | |||
4170 | foreach ($period as $date) { |
||
4171 | $main_day[$date->format('d-m-Y')] = 0; |
||
4172 | } |
||
4173 | |||
4174 | $period = new DatePeriod( |
||
4175 | new DateTime($start_date), |
||
4176 | new DateInterval('P1M'), |
||
4177 | new DateTime($end_date) |
||
4178 | ); |
||
4179 | |||
4180 | foreach ($period as $date) { |
||
4181 | $main_month_year[$date->format('m-Y')] = 0; |
||
4182 | } |
||
4183 | |||
4184 | $i = 0; |
||
4185 | if (is_array($sql_result) && count($sql_result) > 0) { |
||
4186 | foreach ($sql_result as $key => $data) { |
||
4187 | $login = api_strtotime($data['login']); |
||
4188 | $logout = api_strtotime($data['logout']); |
||
4189 | //creating the main array |
||
4190 | if (isset($main_month_year[date('m-Y', $login)])) { |
||
4191 | $main_month_year[date('m-Y', $login)] += (float) ($logout - $login) / 60; |
||
4192 | } |
||
4193 | if (isset($main_day[date('d-m-Y', $login)])) { |
||
4194 | $main_day[date('d-m-Y', $login)] += (float) ($logout - $login) / 60; |
||
4195 | } |
||
4196 | if ($i > 500) { |
||
4197 | break; |
||
4198 | } |
||
4199 | $i++; |
||
4200 | } |
||
4201 | switch ($type) { |
||
4202 | case 'day': |
||
4203 | $main_date = $main_day; |
||
4204 | break; |
||
4205 | case 'month': |
||
4206 | $main_date = $main_month_year; |
||
4207 | break; |
||
4208 | case 'year': |
||
4209 | $main_date = $main_year; |
||
4210 | break; |
||
4211 | } |
||
4212 | |||
4213 | $labels = array_keys($main_date); |
||
4214 | if (1 == count($main_date)) { |
||
4215 | $labels = $labels[0]; |
||
4216 | $main_date = $main_date[$labels]; |
||
4217 | } |
||
4218 | |||
4219 | /* Create and populate the pData object */ |
||
4220 | $myData = new pData(); |
||
4221 | $myData->addPoints($main_date, 'Serie1'); |
||
4222 | if (count($main_date) != 1) { |
||
4223 | $myData->addPoints($labels, 'Labels'); |
||
4224 | $myData->setSerieDescription('Labels', 'Months'); |
||
4225 | $myData->setAbscissa('Labels'); |
||
4226 | } |
||
4227 | $myData->setSerieWeight('Serie1', 1); |
||
4228 | $myData->setSerieDescription('Serie1', get_lang('MyResults')); |
||
4229 | $myData->setAxisName(0, get_lang('Minutes')); |
||
4230 | $myData->loadPalette(api_get_path(SYS_CODE_PATH).'palettes/pchart/default.color', true); |
||
4231 | |||
4232 | // Cache definition |
||
4233 | $cachePath = api_get_path(SYS_ARCHIVE_PATH); |
||
4234 | $myCache = new pCache(['CacheFolder' => substr($cachePath, 0, strlen($cachePath) - 1)]); |
||
4235 | $chartHash = $myCache->getHash($myData); |
||
4236 | |||
4237 | if ($myCache->isInCache($chartHash)) { |
||
4238 | //if we already created the img |
||
4239 | $imgPath = api_get_path(SYS_ARCHIVE_PATH).$chartHash; |
||
4240 | $myCache->saveFromCache($chartHash, $imgPath); |
||
4241 | $imgPath = api_get_path(WEB_ARCHIVE_PATH).$chartHash; |
||
4242 | } else { |
||
4243 | /* Define width, height and angle */ |
||
4244 | $mainWidth = 760; |
||
4245 | $mainHeight = 230; |
||
4246 | $angle = 50; |
||
4247 | |||
4248 | /* Create the pChart object */ |
||
4249 | $myPicture = new pImage($mainWidth, $mainHeight, $myData); |
||
4250 | |||
4251 | /* Turn of Antialiasing */ |
||
4252 | $myPicture->Antialias = false; |
||
4253 | /* Draw the background */ |
||
4254 | $settings = ["R" => 255, "G" => 255, "B" => 255]; |
||
4255 | $myPicture->drawFilledRectangle(0, 0, $mainWidth, $mainHeight, $settings); |
||
4256 | |||
4257 | /* Add a border to the picture */ |
||
4258 | $myPicture->drawRectangle( |
||
4259 | 0, |
||
4260 | 0, |
||
4261 | $mainWidth - 1, |
||
4262 | $mainHeight - 1, |
||
4263 | ["R" => 0, "G" => 0, "B" => 0] |
||
4264 | ); |
||
4265 | |||
4266 | /* Set the default font */ |
||
4267 | $myPicture->setFontProperties( |
||
4268 | [ |
||
4269 | "FontName" => api_get_path(SYS_FONTS_PATH).'opensans/OpenSans-Regular.ttf', |
||
4270 | "FontSize" => 10, ] |
||
4271 | ); |
||
4272 | /* Write the chart title */ |
||
4273 | $myPicture->drawText( |
||
4274 | $mainWidth / 2, |
||
4275 | 30, |
||
4276 | get_lang('TimeSpentInTheCourse'), |
||
4277 | [ |
||
4278 | "FontSize" => 12, |
||
4279 | "Align" => TEXT_ALIGN_BOTTOMMIDDLE, |
||
4280 | ] |
||
4281 | ); |
||
4282 | |||
4283 | /* Set the default font */ |
||
4284 | $myPicture->setFontProperties( |
||
4285 | [ |
||
4286 | "FontName" => api_get_path(SYS_FONTS_PATH).'opensans/OpenSans-Regular.ttf', |
||
4287 | "FontSize" => 8, |
||
4288 | ] |
||
4289 | ); |
||
4290 | |||
4291 | /* Define the chart area */ |
||
4292 | $myPicture->setGraphArea(50, 40, $mainWidth - 40, $mainHeight - 80); |
||
4293 | |||
4294 | /* Draw the scale */ |
||
4295 | $scaleSettings = [ |
||
4296 | 'XMargin' => 10, |
||
4297 | 'YMargin' => 10, |
||
4298 | 'Floating' => true, |
||
4299 | 'GridR' => 200, |
||
4300 | 'GridG' => 200, |
||
4301 | 'GridB' => 200, |
||
4302 | 'DrawSubTicks' => true, |
||
4303 | 'CycleBackground' => true, |
||
4304 | 'LabelRotation' => $angle, |
||
4305 | 'Mode' => SCALE_MODE_ADDALL_START0, |
||
4306 | ]; |
||
4307 | $myPicture->drawScale($scaleSettings); |
||
4308 | |||
4309 | /* Turn on Antialiasing */ |
||
4310 | $myPicture->Antialias = true; |
||
4311 | |||
4312 | /* Enable shadow computing */ |
||
4313 | $myPicture->setShadow( |
||
4314 | true, |
||
4315 | [ |
||
4316 | "X" => 1, |
||
4317 | "Y" => 1, |
||
4318 | "R" => 0, |
||
4319 | "G" => 0, |
||
4320 | "B" => 0, |
||
4321 | "Alpha" => 10, |
||
4322 | ] |
||
4323 | ); |
||
4324 | |||
4325 | /* Draw the line chart */ |
||
4326 | $myPicture->setFontProperties( |
||
4327 | [ |
||
4328 | "FontName" => api_get_path(SYS_FONTS_PATH).'opensans/OpenSans-Regular.ttf', |
||
4329 | "FontSize" => 10, |
||
4330 | ] |
||
4331 | ); |
||
4332 | $myPicture->drawSplineChart(); |
||
4333 | $myPicture->drawPlotChart( |
||
4334 | [ |
||
4335 | "DisplayValues" => true, |
||
4336 | "PlotBorder" => true, |
||
4337 | "BorderSize" => 1, |
||
4338 | "Surrounding" => -60, |
||
4339 | "BorderAlpha" => 80, |
||
4340 | ] |
||
4341 | ); |
||
4342 | |||
4343 | /* Do NOT Write the chart legend */ |
||
4344 | |||
4345 | /* Write and save into cache */ |
||
4346 | $myCache->writeToCache($chartHash, $myPicture); |
||
4347 | $imgPath = api_get_path(SYS_ARCHIVE_PATH).$chartHash; |
||
4348 | $myCache->saveFromCache($chartHash, $imgPath); |
||
4349 | $imgPath = api_get_path(WEB_ARCHIVE_PATH).$chartHash; |
||
4350 | } |
||
4351 | |||
4352 | return '<img src="'.$imgPath.'">'; |
||
4353 | } else { |
||
4354 | return api_convert_encoding( |
||
4355 | '<div id="messages" class="warning-message">'.get_lang('GraphicNotAvailable').'</div>', |
||
4356 | 'UTF-8' |
||
4357 | ); |
||
4358 | } |
||
4359 | } |
||
4360 | |||
4361 | /* |
||
4362 | * Gets the company name of a user based on the extra field 'company'. |
||
4363 | * |
||
4364 | * @param int $userId |
||
4365 | * |
||
4366 | * @return string |
||
4367 | */ |
||
4368 | public static function getCompanyOfUser($userId = 0) |
||
4369 | { |
||
4370 | $userId = (int) $userId; |
||
4371 | if (0 != $userId) { |
||
4372 | $tblExtraFieldValue = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES); |
||
4373 | $tblExtraField = Database::get_main_table(TABLE_EXTRA_FIELD); |
||
4374 | $sql = "SELECT |
||
4375 | extra_field_value.item_id AS userId, |
||
4376 | extra_field_value.value AS company |
||
4377 | FROM $tblExtraFieldValue AS extra_field_value |
||
4378 | INNER JOIN $tblExtraField AS extra_field |
||
4379 | ON ( |
||
4380 | extra_field_value.field_id = extra_field.id AND |
||
4381 | extra_field.variable = 'company' |
||
4382 | ) |
||
4383 | WHERE |
||
4384 | extra_field_value.value != '' AND |
||
4385 | extra_field_value.item_id = $userId "; |
||
4386 | $queryResult = Database::query($sql); |
||
4387 | $data = Database::store_result($queryResult, 'ASSOC'); |
||
4388 | $totalData = count($data); |
||
4389 | /* use 'for' to performance */ |
||
4390 | for ($i = 0; $i < $totalData; $i++) { |
||
4391 | $row = $data[$i]; |
||
4392 | if (isset($row['company']) && !empty($row['company'])) { |
||
4393 | return $row['company']; |
||
4394 | } |
||
4395 | } |
||
4396 | } |
||
4397 | |||
4398 | return get_lang('NoEntity'); |
||
4399 | } |
||
4400 | |||
4401 | /** |
||
4402 | * Gets a list of users who were enrolled in the lessons. |
||
4403 | * It is necessary that in the extra field, a company is defined. |
||
4404 | * |
||
4405 | * if lpId is different to 0, this search by lp id too |
||
4406 | * |
||
4407 | * Variable $withGroups determines the consultation of the enrollment in groups. The group in total will be taken |
||
4408 | * |
||
4409 | * @param string|null $startDate |
||
4410 | * @param string|null $endDate |
||
4411 | * @param int $lpId |
||
4412 | * @param bool $withGroups |
||
4413 | * |
||
4414 | * @return array |
||
4415 | */ |
||
4416 | protected static function getCompanyLearnpathSubscription( |
||
4417 | $startDate = null, |
||
4418 | $endDate = null, |
||
4419 | $whereInLp = null, |
||
4420 | $withGroups = false |
||
4421 | ) { |
||
4422 | $whereInLp = Database::escape_string($whereInLp); |
||
4423 | $tblItemProperty = Database::get_course_table(TABLE_ITEM_PROPERTY); |
||
4424 | $tblLp = Database::get_course_table(TABLE_LP_MAIN); |
||
4425 | $tblLpItem = Database::get_course_table(TABLE_LP_ITEM); |
||
4426 | $tblGroupUser = Database::get_course_table(TABLE_GROUP_USER); |
||
4427 | $tblUser = Database::get_main_table(TABLE_MAIN_USER); |
||
4428 | $tblAccessUrlUser = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); |
||
4429 | $accessUrlFilter = ''; |
||
4430 | if (api_is_multiple_url_enabled()) { |
||
4431 | $urlId = api_get_current_access_url_id(); |
||
4432 | $accessUrlFilter = " INNER JOIN $tblAccessUrlUser auru |
||
4433 | ON (u.id = auru.user_id AND auru.access_url_id = $urlId)"; |
||
4434 | } |
||
4435 | $whereCondition = ''; |
||
4436 | |||
4437 | //Validating dates |
||
4438 | if (!empty($startDate)) { |
||
4439 | $startDate = new DateTime($startDate); |
||
4440 | } |
||
4441 | if (!empty($endDate)) { |
||
4442 | $endDate = new DateTime($endDate); |
||
4443 | } |
||
4444 | if (!empty($startDate) && !empty($endDate)) { |
||
4445 | if ($startDate > $endDate) { |
||
4446 | $dateTemp = $endDate; |
||
4447 | $endDate = $startDate; |
||
4448 | $startDate = $dateTemp; |
||
4449 | unset($dateTemp); |
||
4450 | } |
||
4451 | } |
||
4452 | |||
4453 | // Settings condition and parametter GET to right date |
||
4454 | if (!empty($startDate)) { |
||
4455 | $startDate = api_get_utc_datetime($startDate->setTime(0, 0, 0)->format('Y-m-d H:i:s')); |
||
4456 | $_GET['startDate'] = $startDate; |
||
4457 | $whereCondition .= " AND ip.lastedit_date >= '$startDate' "; |
||
4458 | } |
||
4459 | if (!empty($endDate)) { |
||
4460 | $endDate = api_get_utc_datetime($endDate->setTime(23, 59, 59)->format('Y-m-d H:i:s')); |
||
4461 | $_GET['endDate'] = $endDate; |
||
4462 | $whereCondition .= " AND ip.lastedit_date <= '$endDate' "; |
||
4463 | } |
||
4464 | if (!empty($whereInLp)) { |
||
4465 | $whereCondition .= " AND ip.ref in ($whereInLp) "; |
||
4466 | } |
||
4467 | $datas = []; |
||
4468 | if (!empty($startDate) or !empty($endDate)) { |
||
4469 | $query = " |
||
4470 | SELECT DISTINCT |
||
4471 | ip.ref AS lp_item, |
||
4472 | lpi.iid AS lp_item_id, |
||
4473 | ip.session_id AS session_id, |
||
4474 | ip.lastedit_type AS type, |
||
4475 | u.username AS username, |
||
4476 | ip.lastedit_date AS lastedit_date, |
||
4477 | ip.to_user_id AS id, |
||
4478 | u.firstname as firstname, |
||
4479 | u.lastname as lastname |
||
4480 | FROM $tblItemProperty AS ip |
||
4481 | INNER JOIN $tblUser AS u |
||
4482 | ON (u.id = ip.to_user_id) |
||
4483 | INNER JOIN $tblLp AS lp |
||
4484 | ON (lp.iid = ip.ref AND lp.c_id = ip.c_id) |
||
4485 | INNER JOIN $tblLpItem AS lpi |
||
4486 | ON (lp.id = lpi.lp_id AND lp.c_id = lpi.c_id) |
||
4487 | $accessUrlFilter |
||
4488 | WHERE |
||
4489 | ip.lastedit_type = 'LearnpathSubscription' "; |
||
4490 | if (strlen($whereCondition) > 2) { |
||
4491 | $query .= $whereCondition; |
||
4492 | } |
||
4493 | if ($withGroups) { |
||
4494 | $query = " |
||
4495 | SELECT DISTINCT |
||
4496 | ip.ref AS lp_item, |
||
4497 | lpi.iid AS lp_item_id, |
||
4498 | ip.session_id AS session_id, |
||
4499 | ip.lastedit_type AS type, |
||
4500 | ip.lastedit_date AS lastedit_date, |
||
4501 | ip.to_group_id AS group_id, |
||
4502 | ug.user_id AS id, |
||
4503 | u.firstname as firstname, |
||
4504 | u.lastname as lastname |
||
4505 | FROM |
||
4506 | $tblItemProperty AS ip |
||
4507 | INNER JOIN $tblGroupUser AS ug |
||
4508 | ON (ug.group_id = ip.to_group_id AND ip.c_id = ug.c_id) |
||
4509 | INNER JOIN $tblUser AS u |
||
4510 | ON (u.id = ug.user_id) |
||
4511 | INNER JOIN $tblLp AS lp |
||
4512 | ON (lp.iid = ip.ref AND ug.c_id = lp.c_id) |
||
4513 | INNER JOIN $tblLpItem AS lpi |
||
4514 | ON (lp.id = lpi.lp_id AND lp.c_id = lpi.c_id) |
||
4515 | $accessUrlFilter |
||
4516 | WHERE |
||
4517 | ip.lastedit_type = 'LearnpathSubscription' AND |
||
4518 | ip.to_group_id != 0 "; |
||
4519 | if (strlen($whereCondition) > 2) { |
||
4520 | $query .= $whereCondition; |
||
4521 | } |
||
4522 | } |
||
4523 | $query .= ' ORDER BY ip.ref, ip.session_id '; |
||
4524 | $queryResult = Database::query($query); |
||
4525 | $data = Database::store_result($queryResult, 'ASSOC'); |
||
4526 | $totalData = count($data); |
||
4527 | /* use 'for' to performance */ |
||
4528 | for ($i = 0; $i < $totalData; $i++) { |
||
4529 | $row = $data[$i]; |
||
4530 | $row['complete_name'] = api_get_person_name($row['firstname'], $row['lastname']); |
||
4531 | $row['company'] = self::getCompanyOfUser($row['id']); |
||
4532 | $datas[$row['lp_item_id']][] = $row; |
||
4533 | } |
||
4534 | } |
||
4535 | |||
4536 | return $datas; |
||
4537 | } |
||
4538 | |||
4539 | private static function getDataAccessTrackingFilters($sql) |
||
4540 | { |
||
4541 | if (isset($_GET['course_id']) && !empty($_GET['course_id'])) { |
||
4542 | $courseId = (int) $_GET['course_id']; |
||
4543 | $sql .= " AND c.id = ".$courseId; |
||
4544 | } |
||
4545 | |||
4546 | if (isset($_GET['session_id']) && !empty($_GET['session_id'])) { |
||
4547 | $sessionId = (int) $_GET['session_id']; |
||
4548 | $sql .= " AND a.session_id = ".$sessionId; |
||
4549 | } |
||
4550 | |||
4551 | if (isset($_GET['student_id']) && !empty($_GET['student_id'])) { |
||
4552 | $userId = (int) $_GET['student_id']; |
||
4553 | $sql .= " AND u.user_id = ".$userId; |
||
4554 | } |
||
4555 | |||
4556 | $sql .= " AND u.status <> ".ANONYMOUS; |
||
4557 | |||
4558 | if (isset($_GET['date']) && !empty($_GET['date'])) { |
||
4559 | $dateRangePicker = new DateRangePicker('date', '', ['timePicker' => 'true']); |
||
4560 | $dates = $dateRangePicker->parseDateRange($_GET['date']); |
||
4561 | if (isset($dates['start']) && !empty($dates['start'])) { |
||
4562 | $dates['start'] = Database::escape_string(api_get_utc_datetime($dates['start'])); |
||
4563 | $sql .= " AND login_course_date >= '".$dates['start']."'"; |
||
4564 | } |
||
4565 | if (isset($dates['end']) && !empty($dates['end'])) { |
||
4566 | $dates['end'] = Database::escape_string(api_get_utc_datetime($dates['end'])); |
||
4567 | $sql .= " AND logout_course_date <= '".$dates['end']."'"; |
||
4568 | } |
||
4569 | } |
||
4570 | |||
4571 | return $sql; |
||
4572 | } |
||
4573 | } |
||
4574 |