AutorÃa | Ultima modificación | Ver Log |
<?phpfunction local_cesa_reportes_get_week_days(){$data = new \stdClass();$data->min = 0;$data->max = 0;$data->days = [];$dt = new \DateTime();for($i = 0; $i < 7; $i++){if($i > 0) {$dt->sub(new \DateInterval('P1D'));}$dt->setTime(23, 59, 59);$max = $dt->getTimestamp();if(!$data->max) {$data->max = $max;}$dt->setTime(0, 0, 0);$min = $dt->getTimestamp();$item = new \stdClass();$item->min = $min;$item->max = $max;$item->day = $dt->format('d/m/Y');array_push($data->days, $item);}$data->min = $min;return $data;}/**** @param int $courseid* @param int $userid* @return int*/function local_cesa_reportes_get_timecompleted_for_course_and_userid($courseid, $userid){global $DB;$sql = 'SELECT COALESCE(timecompleted, 0) AS timecompleted ' .' FROM {course_completions} ' .' WHERE COURSE = :courseid AND userid = :userid ';return $DB->get_field_sql($sql, ['courseid' => $courseid, 'userid' => $userid]);}/**** @param int $courseid* @param int $userid* @param int $from* @param int $to* @return int*/function local_cesa_reportes_get_timecompleted_for_course_and_userid_range($courseid, $userid, $from, $to){global $DB;$sql = 'SELECT COALESCE(timecompleted, 0) AS timecompleted ' .' FROM {course_completions} ' .' WHERE COURSE = :courseid AND userid = :userid ' .' AND COALESCE(timecompleted, 0) >= :from AND COALESCE(timecompleted, 0) <= :to';return $DB->get_field_sql($sql, ['courseid' => $courseid, 'userid' => $userid, 'from' => $from, 'to' => $to]);}/**** @param int $from* @param int $to* @return int[]*/function local_cesa_reportes_get_course_with_activites_range($from, $to){global $DB;$sql = 'SELECT DISTINCT(course) AS course FROM {course_modules} ' .' WHERE visible = 1 AND completion > 0 AND deletioninprogress = 0 and added >= :from ' .' AND added <= :to';$courses = [];$records = $DB->get_records_sql($sql, ['from' => $from, 'to' => $to]);foreach($records as $record){array_push($courses, $record->course);}return $courses;}/**** @param int $courseid* @param int $userid* @return int*/function local_cesa_reportes_get_enroll_for_course_and_userid($courseid, $userid){global $DB;$sql = 'SELECT ue.timecreated FROM {user_enrolments} AS ue, {enrol} AS e ' .' WHERE ue.enrolid = e.id AND ue.userid = :userid AND e.courseid = :courseid LIMIT 1' ;$timestart = $DB->get_field_sql($sql, ['courseid' => $courseid, 'userid' => $userid]);return $timestart;}/**** @param int $courseid* @param int $from* @param int $to* @return array*/function local_cesa_reportes_get_activities_assigned_range($courseid, $from, $to){global $DB;$sql = 'SELECT id FROM {course_modules} ' .' WHERE course = :courseid AND visible = 1 AND completion > 0 AND deletioninprogress = 0 ' .' AND added >= :from AND added <= :to';$ids = [];$records = $DB->get_records_sql($sql, ['courseid' => $courseid, 'from' => $from, 'to' => $to ]);foreach($records as $record){array_push($ids, $record->id);}return $ids;}/**** @param int $courseid* @return array*/function local_cesa_reportes_get_activities_assigned($courseid){global $DB;$sql = 'SELECT id FROM {course_modules} ' .' WHERE course = :courseid AND visible = 1 AND completion > 0 AND deletioninprogress = 0 ' ;$ids = [];$records = $DB->get_records_sql($sql, ['courseid' => $courseid ]);foreach($records as $record){array_push($ids, $record->id);}return $ids;}/**** @param boolean $filter_by_company_active* @param int $start* @param int $end* @param array $filter_userids* @return array*/function local_cesa_reportes_get_last_ten_user_activities($filter_by_company_active, $filter_userids, $start, $end){global $DB;$filterUser = '';if($filter_by_company_active) {$filter_userids = empty($filter_userids) ? [-1] : $filter_userids;$filterUser .= ' AND u.id IN (' . implode(',', $filter_userids) . ')';}$sql = <<<EOTSELECT l.userid, l.timecreated, u.firstname, u.lastname, u.email, l.courseid, c.fullname as courseFROM {logstore_standard_log} as l, {user} as u, {course} as cWHEREl.userid > 1AND l.userid = u.idAND l.courseid = c.id$filterUserANDl.timecreated >= :startANDl.timecreated <= :endANDl.eventname IN ('\\\\core\\\\event\\\\course_completed','\\\\core\\\\event\\\\course_viewed','\\\\core\\\\event\\\\course_module_viewed','\\\\mod_book\\\\event\\\\course_module_viewed','\\\\mod_choice\\\\event\\\\course_module_viewed','\\\\mod_customcert\\\\event\\\\course_module_viewed','\\\\mod_data\\\\event\\\\course_module_viewed','\\\\mod_feedback\\\\event\\\\course_module_viewed','\\\\mod_folder\\\\event\\\\course_module_viewed','\\\\mod_forum\\\\event\\\\course_module_viewed','\\\\mod_h5pactivity\\\\event\\\\course_module_viewed','\\\\mod_hvp\\\\event\\\\course_module_viewed','\\\\mod_lesson\\\\event\\\\course_module_viewed','\\\\mod_lti\\\\event\\\\course_module_viewed','\\\\mod_page\\\\event\\\\course_module_viewed','\\\\mod_questionnaire\\\\event\\\\course_module_viewed','\\\\mod_quiz\\\\event\\\\course_module_viewed','\\\\mod_resource\\\\event\\\\course_module_viewed','\\\\mod_scorm\\\\event\\\\course_module_viewed','\\\\mod_subcourse\\\\event\\\\course_module_viewed','\\\\mod_survey\\\\event\\\\course_module_viewed','\\\\mod_url\\\\event\\\\course_module_viewed','\\\\mod_wiki\\\\event\\\\course_module_viewed','\\\\mod_workshop\\\\event\\\\course_module_viewed','\\\\mod_zoom\\\\event\\\\course_module_viewed')GROUP BY l.useridORDER BYl.timecreated DESC LIMIT 10;EOT;$items = [];$records = $DB->get_records_sql($sql, ['start' => $start, 'end' => $end]);foreach($records as $record){$record->timecreated = date('d/m/Y h:i a', $record->timecreated);array_push($items, (array) $record);}return $items;}/**** @param boolean $filter_by_company_active* @param int $start* @param int $end* @param array $filter_userids* @return array*/function local_cesa_reportes_get_top_ten_user_activities($filter_by_company_active, $filter_userids, $start, $end){global $DB;$filterUser = '';if($filter_by_company_active) {$filter_userids = empty($filter_userids) ? [-1] : $filter_userids;$filterUser .= ' AND u.id IN (' . implode(',', $filter_userids) . ')';}$sql = <<<EOTSELECT l.userid, COUNT(*) AS activities, u.firstname, u.lastname, u.emailFROM {logstore_standard_log} AS l, {user} AS uWHEREl.userid = u.idANDl.userid > 1 $filterUserANDl.timecreated >= $startANDl.timecreated <= $endANDl.eventname IN ('\\\\core\\\\event\\\\course_completed','\\\\core\\\\event\\\\course_viewed','\\\\core\\\\event\\\\course_module_viewed','\\\\mod_book\\\\event\\\\course_module_viewed','\\\\mod_choice\\\\event\\\\course_module_viewed','\\\\mod_customcert\\\\event\\\\course_module_viewed','\\\\mod_data\\\\event\\\\course_module_viewed','\\\\mod_feedback\\\\event\\\\course_module_viewed','\\\\mod_folder\\\\event\\\\course_module_viewed','\\\\mod_forum\\\\event\\\\course_module_viewed','\\\\mod_h5pactivity\\\\event\\\\course_module_viewed','\\\\mod_hvp\\\\event\\\\course_module_viewed','\\\\mod_lesson\\\\event\\\\course_module_viewed','\\\\mod_lti\\\\event\\\\course_module_viewed','\\\\mod_page\\\\event\\\\course_module_viewed','\\\\mod_questionnaire\\\\event\\\\course_module_viewed','\\\\mod_quiz\\\\event\\\\course_module_viewed','\\\\mod_resource\\\\event\\\\course_module_viewed','\\\\mod_scorm\\\\event\\\\course_module_viewed','\\\\mod_subcourse\\\\event\\\\course_module_viewed','\\\\mod_survey\\\\event\\\\course_module_viewed','\\\\mod_url\\\\event\\\\course_module_viewed','\\\\mod_wiki\\\\event\\\\course_module_viewed','\\\\mod_workshop\\\\event\\\\course_module_viewed','\\\\mod_zoom\\\\event\\\\course_module_viewed')GROUP BY l.useridORDER BY activities DESC LIMIT 10;EOT;$items = [];$records = $DB->get_records_sql($sql, ['start' => $start, 'end' => $end]);foreach($records as $record){array_push($items, (array) $record);}return $items;}/**** @param boolean $filter_by_company_active* @param int $start* @param int $end* @param array $filter_userids* @return int*/function local_cesa_reportes_get_count_activities($filter_by_company_active, $filter_userids, $start, $end){global $DB;$filterUser = '';if($filter_by_company_active) {$filter_userids = empty($filter_userids) ? [-1] : $filter_userids;$filterUser .= ' AND l.userid IN (' . implode(',', $filter_userids) . ')';}$sql = <<<EOTSELECT COUNT(*) FROM {logstore_standard_log} AS lWHEREl.userid > 1 $filterUserANDl.timecreated >= $startANDl.timecreated <= $endANDl.eventname IN ('\\\\core\\\\event\\\\course_completed','\\\\core\\\\event\\\\course_viewed','\\\\core\\\\event\\\\course_module_viewed','\\\\mod_book\\\\event\\\\course_module_viewed','\\\\mod_choice\\\\event\\\\course_module_viewed','\\\\mod_customcert\\\\event\\\\course_module_viewed','\\\\mod_data\\\\event\\\\course_module_viewed','\\\\mod_feedback\\\\event\\\\course_module_viewed','\\\\mod_folder\\\\event\\\\course_module_viewed','\\\\mod_forum\\\\event\\\\course_module_viewed','\\\\mod_h5pactivity\\\\event\\\\course_module_viewed','\\\\mod_hvp\\\\event\\\\course_module_viewed','\\\\mod_lesson\\\\event\\\\course_module_viewed','\\\\mod_lti\\\\event\\\\course_module_viewed','\\\\mod_page\\\\event\\\\course_module_viewed','\\\\mod_questionnaire\\\\event\\\\course_module_viewed','\\\\mod_quiz\\\\event\\\\course_module_viewed','\\\\mod_resource\\\\event\\\\course_module_viewed','\\\\mod_scorm\\\\event\\\\course_module_viewed','\\\\mod_subcourse\\\\event\\\\course_module_viewed','\\\\mod_survey\\\\event\\\\course_module_viewed','\\\\mod_url\\\\event\\\\course_module_viewed','\\\\mod_wiki\\\\event\\\\course_module_viewed','\\\\mod_workshop\\\\event\\\\course_module_viewed','\\\\mod_zoom\\\\event\\\\course_module_viewed')EOT;return $DB->get_field_sql($sql);}/**** @param boolean $filter_by_company_active* @param int $start* @param int $end* @param array $filter_userids* @return int*/function local_cesa_reportes_get_count_modules_completion($filter_by_company_active, $filter_userids, $start, $end){global $DB;$sql = 'SELECT count(*) AS completion ' .' FROM {course_modules_completion} AS cmc ' .' WHERE cmc.completionstate IN (1,2) '.' AND cmc.timemodified >= :start AND cmc.timemodified <= :end ';if($filter_by_company_active) {$filter_userids = empty($filter_userids) ? [-1] : $filter_userids;$sql .= ' AND cmc.userid IN (' . implode(',', $filter_userids) . ')';}return $DB->get_field_sql($sql, ['start' => $start, 'end' => $end]);}/**** @param boolean $filter_by_company_active* @param int $start* @param int $end* @param array $filter_userids* @return array*/function local_cesa_reportes_get_top_ten_user_modules_completion($filter_by_company_active, $filter_userids, $start, $end){global $DB;$sql = 'SELECT cmc.userid, count(*) AS completion, u.firstname, u.lastname, u.email ' .' FROM {course_modules_completion} AS cmc, {user} AS u ' .' WHERE cmc.userid = u.id AND cmc.completionstate IN (1,2) '.' AND cmc.timemodified >= :start AND cmc.timemodified <= :end ';if($filter_by_company_active) {$filter_userids = empty($filter_userids) ? [-1] : $filter_userids;$sql .= ' AND u.id IN (' . implode(',', $filter_userids) . ')';}$sql .= ' GROUP BY cmc.userid ' .' ORDER BY completion DESC ' .' LIMIT 10 ';$items = [];$records = $DB->get_records_sql($sql, ['start' => $start, 'end' => $end]);foreach($records as $record){array_push($items, (array) $record);}return $items;}/**** @param boolean $filter_by_company_active* @param int $start* @param int $end* @param array $filter_userids* @return array*/function local_cesa_reportes_get_last_ten_user_modules_completion($filter_by_company_active, $filter_userids, $start, $end){global $DB;/*SELECT c.fullname FROM mdl_course_modules_completion as cmc, mdl_course_modules as cm, mdl_course as cwhere cm.id = cmc.coursemoduleid and cm.course = c.id ;*/$sql = 'SELECT cmc.userid, cmc.timemodified, u.firstname, u.lastname, u.email, cm.course as courseid, c.fullname as course ' .' FROM {course_modules_completion} AS cmc, {user} AS u, {course_modules} AS cm, {course} AS c ' .' WHERE cmc.userid = u.id AND completionstate IN (1,2) '.' AND cm.id = cmc.coursemoduleid and cm.course = c.id ' .' AND cmc.timemodified >= :start AND cmc.timemodified <= :end ';if($filter_by_company_active) {$filter_userids = empty($filter_userids) ? [-1] : $filter_userids;$sql .= ' AND u.id IN (' . implode(',', $filter_userids) . ')';}$sql .= ' GROUP BY cmc.userid ' .' ORDER BY cmc.timemodified DESC LIMIT 10 ';$items = [];$records = $DB->get_records_sql($sql, ['start' => $start, 'end' => $end]);foreach($records as $record){$record->timemodified = date('d/m/Y h:i a', $record->timemodified);array_push($items, (array) $record);}return $items;}/**** @param int $courseid* @param int $userid* @param int $from* @param int $to* @return array*/function local_cesa_reportes_get_activities_completed_range($courseid, $userid, $from, $to){global $DB;$sql = 'SELECT cm_i.id, cmc_i.timemodified FROM {course_modules} AS cm_i, ' .' {course_modules_completion} AS cmc_i WHERE cmc_i.coursemoduleid = cm_i.id '.' AND cm_i.visible = 1 AND cmc_i.completionstate IN (1, 2) ' .' AND cm_i.deletioninprogress = 0 AND cm_i.course = :courseid AND cmc_i.userid = :userid ' .' AND added >= :from AND added <= :to ' .' GROUP BY cm_i.id ';$data = [];$records = $DB->get_records_sql($sql, ['courseid' => $courseid, 'userid' => $userid, 'from' => $from, 'to' => $to ]);foreach($records as $record){$data[$record->id] = $record->timemodified;}return $data;}/**** @param int $courseid* @param int $userid* @return array*/function local_cesa_reportes_get_activities_completed($courseid, $userid){global $DB;$sql = 'SELECT cm_i.id, cmc_i.timemodified FROM {course_modules} AS cm_i, ' .' {course_modules_completion} AS cmc_i WHERE cmc_i.coursemoduleid = cm_i.id '.' AND cm_i.visible = 1 AND cmc_i.completionstate IN (1, 2) ' .' AND cm_i.deletioninprogress = 0 AND cm_i.course = :courseid AND cmc_i.userid = :userid ' .' GROUP BY cm_i.id ';$data = [];$records = $DB->get_records_sql($sql, ['courseid' => $courseid, 'userid' => $userid ]);foreach($records as $record){$data[$record->id] = $record->timemodified;}return $data;}/**** @param int $courseid* @return int*/function local_cesa_reportes_get_num_activities_assigned($courseid){global $DB;$sql = 'SELECT COUNT(id) AS modules FROM {course_modules} ' .' WHERE course = :courseid AND visible = 1 AND completion > 0 AND deletioninprogress = 0 ' ;return $DB->get_field_sql($sql, ['courseid' => $courseid ]);}/**** @param int $courseid* @param int $userid* @return int*/function local_cesa_reportes_get_num_activities_completed($courseid, $userid){global $DB;$sql = 'SELECT COUNT(DISTINCT(cmc_i.id)) AS completed FROM {course_modules} cm_i, {course_modules_completion} cmc_i ' .' WHERE cmc_i.coursemoduleid = cm_i.id AND cm_i.visible = 1 AND cmc_i.completionstate IN (1, 2) ' .' AND cm_i.deletioninprogress = 0 AND cm_i.course = :courseid AND cmc_i.userid = :userid ';return $DB->get_field_sql($sql, ['courseid' => $courseid, 'userid' => $userid ]);}/**** @param int $courseid* @param int $from* @param int $to* @return int*/function local_cesa_reportes_get_num_activities_assigned_range($courseid, $from, $to){global $DB;$sql = 'SELECT COUNT(id) AS modules FROM {course_modules} ' .' WHERE course = :courseid AND visible = 1 AND completion > 0 AND deletioninprogress = 0 ' .' AND added >= :from AND added <= :to ';return $DB->get_field_sql($sql, ['courseid' => $courseid, 'from' => $from, 'to' => $to ]);}/**** @param int $courseid* @param int $userid* @param int $from* @param int $to* @return int*/function local_cesa_reportes_get_num_activities_completed_range($courseid, $userid, $from, $to){global $DB;$sql = 'SELECT COUNT(DISTINCT(cmc_i.id)) AS completed FROM {course_modules} cm_i, {course_modules_completion} cmc_i ' .' WHERE cmc_i.coursemoduleid = cm_i.id AND cm_i.visible = 1 AND cmc_i.completionstate IN (1, 2) ' .' AND cm_i.deletioninprogress = 0 AND cm_i.course = :courseid AND cmc_i.userid = :userid ' .' AND cmc_i.timemodified >= :from AND cmc_i.timemodified <= :to';return $DB->get_field_sql($sql, ['courseid' => $courseid, 'userid' => $userid, 'from' => $from, 'to' => $to ]);}/**** @param int $courseid* @param int $userid* @param int $from* @param int $to* @return \stdClass*/function local_cesa_reportes_get_min_and_max_date_events_range($courseid, $userid, $from, $to){global $DB;$sql = <<<EOTselectuserid,min(timecreated) as min,max(timecreated) as maxfrom {logstore_standard_log}whereuserid = :useridandcourseid = :courseidandeventname in ('\\\\core\\\\event\\\\course_completed','\\\\core\\\\event\\\\course_viewed','\\\\core\\\\event\\\\course_module_viewed','\\\\mod_book\\\\event\\\\course_module_viewed','\\\\mod_choice\\\\event\\\\course_module_viewed','\\\\mod_customcert\\\\event\\\\course_module_viewed','\\\\mod_data\\\\event\\\\course_module_viewed','\\\\mod_feedback\\\\event\\\\course_module_viewed','\\\\mod_folder\\\\event\\\\course_module_viewed','\\\\mod_forum\\\\event\\\\course_module_viewed','\\\\mod_h5pactivity\\\\event\\\\course_module_viewed','\\\\mod_hvp\\\\event\\\\course_module_viewed','\\\\mod_lesson\\\\event\\\\course_module_viewed','\\\\mod_lti\\\\event\\\\course_module_viewed','\\\\mod_page\\\\event\\\\course_module_viewed','\\\\mod_questionnaire\\\\event\\\\course_module_viewed','\\\\mod_quiz\\\\event\\\\course_module_viewed','\\\\mod_resource\\\\event\\\\course_module_viewed','\\\\mod_scorm\\\\event\\\\course_module_viewed','\\\\mod_subcourse\\\\event\\\\course_module_viewed','\\\\mod_survey\\\\event\\\\course_module_viewed','\\\\mod_url\\\\event\\\\course_module_viewed','\\\\mod_wiki\\\\event\\\\course_module_viewed','\\\\mod_workshop\\\\event\\\\course_module_viewed','\\\\mod_zoom\\\\event\\\\course_module_viewed')andtimecreated >= :fromandtimecreated <= :togroup by useridEOT;$data = new \stdClass();$data->min = '';$data->max = '';$sql = trim($sql);$record = $DB->get_record_sql($sql, ['courseid' => $courseid, 'userid' => $userid, 'from' => $from, 'to' => $to]);if($record) {$data->min = $record->min;$data->max = $record->max;}return $data;}/**** @param int $courseid* @param int $userid* @return \stdClass*/function local_cesa_reportes_get_min_and_max_date_events($courseid, $userid){global $DB;$sql = <<<EOTselectuserid,min(timecreated) as min,max(timecreated) as maxfrom {logstore_standard_log}whereuserid = :useridandcourseid = :courseidandeventname in ('\\\\core\\\\event\\\\course_completed','\\\\core\\\\event\\\\course_viewed','\\\\core\\\\event\\\\course_module_viewed','\\\\mod_book\\\\event\\\\course_module_viewed','\\\\mod_choice\\\\event\\\\course_module_viewed','\\\\mod_customcert\\\\event\\\\course_module_viewed','\\\\mod_data\\\\event\\\\course_module_viewed','\\\\mod_feedback\\\\event\\\\course_module_viewed','\\\\mod_folder\\\\event\\\\course_module_viewed','\\\\mod_forum\\\\event\\\\course_module_viewed','\\\\mod_h5pactivity\\\\event\\\\course_module_viewed','\\\\mod_hvp\\\\event\\\\course_module_viewed','\\\\mod_lesson\\\\event\\\\course_module_viewed','\\\\mod_lti\\\\event\\\\course_module_viewed','\\\\mod_page\\\\event\\\\course_module_viewed','\\\\mod_questionnaire\\\\event\\\\course_module_viewed','\\\\mod_quiz\\\\event\\\\course_module_viewed','\\\\mod_resource\\\\event\\\\course_module_viewed','\\\\mod_scorm\\\\event\\\\course_module_viewed','\\\\mod_subcourse\\\\event\\\\course_module_viewed','\\\\mod_survey\\\\event\\\\course_module_viewed','\\\\mod_url\\\\event\\\\course_module_viewed','\\\\mod_wiki\\\\event\\\\course_module_viewed','\\\\mod_workshop\\\\event\\\\course_module_viewed','\\\\mod_zoom\\\\event\\\\course_module_viewed')group by useridEOT;$data = new \stdClass();$data->min = '';$data->max = '';$sql = trim($sql);$record = $DB->get_record_sql($sql, ['courseid' => $courseid, 'userid' => $userid]);if($record) {$data->min = $record->min;$data->max = $record->max;}return $data;}/**** @param int $userid* @return array*/function local_cesa_reportes_get_user_extra_field_by_userid($userid){global $DB;$sql = 'SELECT f.id, f.name AS field, d.data AS value FROM {user_info_data} AS d, {user_info_field} AS f ' .' WHERE d.fieldid = f.id AND userid = :userid ORDER BY name' ;return $DB->get_records_sql($sql, ['userid' => $userid]);}/**** @return array*/function local_cesa_reportes_get_user_extra_field_names(){global $DB;$sql = 'SELECT id, name FROM {user_info_field} ORDER BY name' ;return $DB->get_records_sql($sql);}/**** @param boolean $filter_by_company_active* @param array $filter_userids* @param $filter_courseids* @param boolean $include_modules* @param int $page* @return array*/function local_cesa_reportes_get_data_for_report_or_excel($filter_by_company_active, $filter_userids, $filter_courseids, $include_modules, $page = 0){global $DB;$sql = ' SELECT u.id AS id, u.firstname, u.lastname, u.email FROM {user} AS u WHERE u.id > 1 AND u.deleted = 0 ' .' AND u.suspended = 0 ';if($filter_by_company_active && $filter_userids) {$filter_userids = empty($filter_userids) ? [-1] : $filter_userids;$sql .= ' AND u.id IN (' . implode(',', $filter_userids) . ')';}$sql .= ' ORDER BY lastname ASC, firstname ASC ';$users = $DB->get_records_sql($sql);$max_users = count($users);$max_pages = (int) ($max_users / 100);if($max_users > ($max_pages * 100) ) {$max_pages++;}if($page > 0) {if($page > $max_pages) {$page = $max_pages;}if($page == 1) {$users = array_slice($users, 0, 100);} else if($page == $max_pages) {$offset = ( $page - 1) * 100;$users = array_slice($users, $offset);} else {$offset = ( $page - 1) * 100;$users = array_slice($users, $offset, 100);}}/*echo 'page2 = ' . $page . PHP_EOL;echo 'max_pages = ' . $max_pages . PHP_EOL;echo 'max_users = ' . $max_users . PHP_EOL;echo 'current_users = ' . count($users) . PHP_EOL;echo '</pre>';exit;*/$items = [];$categories = [];$courses = [];$num_activities_assigned_for_courses = [];$modules_for_course = [];foreach($users as $user){$mycourses = enrol_get_users_courses($user->id);if(is_array($mycourses)) {usort($mycourses, function($a, $b) { return strcasecmp($a->fullname,$b->fullname); });}$item = new \stdClass();$item->userid = $user->id;$item->firstname = $user->firstname;$item->lastname = $user->lastname;$item->email = $user->email;$item->extras = local_cesa_reportes_get_user_extra_field_by_userid($user->id);$item->courses = [];foreach($mycourses as $val){if($filter_by_company_active && $filter_courseids) {if(!in_array($val->id, $filter_courseids)) {continue;}}if(isset($courses[ $val->id ])) {$course = $courses[$val->id];} else {$course = get_course($val->id);$courses[ $val->id ] = $course;}if(isset($num_activities_assigned_for_courses[$course->id])) {$num_activities_assigned = $num_activities_assigned_for_courses[$course->id];} else {$num_activities_assigned = local_cesa_reportes_get_num_activities_assigned($course->id);$num_activities_assigned_for_courses[$course->id] = $num_activities_assigned;}if(isset($categories[ $course->category ])) {$category = $categories[ $course->category ];} else {$category = $DB->get_field('course_categories', 'name', ['id' => $course->category]);$categories[ $course->category ] = $category;}$item_course = new \stdClass();$item_course->courseid = $course->id;$item_course->name = $course->fullname;$item_course->category = $category;$item_course->percentage = 0;$item_course->activities_assigned = $num_activities_assigned;$item_course->activities_completed = local_cesa_reportes_get_num_activities_completed($course->id, $user->id);if($item_course->activities_assigned > 0 && $item_course->activities_completed > 0) {$item_course->percentage = ( $item_course->activities_completed * 100 ) / $item_course->activities_assigned;}if($include_modules) {$activities_completed = local_cesa_reportes_get_activities_completed($course->id, $user->id);if(isset($modules_for_course[ $course->id ])) {$modules = $modules_for_course[ $course->id ];} else {$modules = [];$records = get_fast_modinfo($val->id)->get_cms();foreach($records as $record){$modules[ $record->id ] = $record->get_formatted_name();}$modules_for_course[ $course->id ] = $modules;}$item_course->activities = [];$ids = local_cesa_reportes_get_activities_assigned($course->id);foreach($ids as $id){if(isset($activities_completed[ $id ])) {$timecompleted = $activities_completed[$id];} else {$timecompleted = 0;}$activity = new \stdClass();$activity->module = $modules[$id];$activity->timecompleted = $timecompleted;array_push($item_course->activities, $activity);}}$item_course->timeenrolled = local_cesa_reportes_get_enroll_for_course_and_userid($course->id, $user->id);$item_course->timecompleted = local_cesa_reportes_get_timecompleted_for_course_and_userid($course->id, $user->id);$min_and_max_date_events = local_cesa_reportes_get_min_and_max_date_events($course->id, $user->id);$item_course->time_activities_first = $min_and_max_date_events->min;$item_course->time_activities_last = $min_and_max_date_events->max;/*$completion = new \completion_info($course);if ($completion->is_enabled()) {$item_course->completion_is_enabled = true;$item_course->percentage = \core_completion\progress::get_course_progress_percentage($course, $user->id);$item_course->percentage = !$item_course->percentage ? 0 : $item_course->percentage;} else {$item_course->completion_is_enabled = false;}*/array_push($item->courses, $item_course);}array_push($items, $item);}return ['pagination' => ['page' => $page,'max_users' => $max_users,'max_pages' => $max_pages],'items' => $items] ;}/**** @param boolean $filter_by_company_active* @param array $filter_userids* @param array $filter_courseids* @param string $filter_userkeyword* @param string $filter_from* @param string $filter_to,* @param boolean $include_modules* @param int $page* @return array*/function local_cesa_reportes_get_data_for_report_or_excel_custom($filter_by_company_active, $filter_userids, $filter_courseids, $filter_userkeyword, $filter_from, $filter_to, $include_modules, $page = 0){global $DB;$sql = ' SELECT u.id AS id, u.firstname, u.lastname, u.email FROM {user} AS u WHERE u.id > 1 AND u.deleted = 0 ' .' AND u.suspended = 0 ';if($filter_userkeyword) {$sql .= " AND ( u.firstname like '%$filter_userkeyword%' OR u.lastname like '%$filter_userkeyword%' OR u.email like '%$filter_userkeyword%' ) " ;}if($filter_by_company_active && $filter_userids) {$filter_userids = empty($filter_userids) ? [-1] : $filter_userids;$sql .= ' AND u.id IN (' . implode(',', $filter_userids) . ')';}$sql .= ' ORDER BY lastname ASC, firstname ASC ';$users = $DB->get_records_sql($sql);$max_users = count($users);$max_pages = (int) ($max_users / 100);if($max_users > ($max_pages * 100) ) {$max_pages++;}if($page > 0) {if($page > $max_pages) {$page = $max_pages;}if($page == 1) {$users = array_slice($users, 0, 100);} else if($page == $max_pages) {$offset = ( $page - 1) * 100;$users = array_slice($users, $offset);} else {$offset = ( $page - 1) * 100;$users = array_slice($users, $offset, 100);}}/*echo 'page2 = ' . $page . PHP_EOL;echo 'max_pages = ' . $max_pages . PHP_EOL;echo 'max_users = ' . $max_users . PHP_EOL;echo 'current_users = ' . count($users) . PHP_EOL;echo '</pre>';exit;*/$items = [];$categories = [];$courses = [];$num_activities_assigned_for_range = [];$num_activities_assigned_for_course = [];$modules_for_course = [];foreach($users as $user){$mycourses = enrol_get_users_courses($user->id);if(is_array($mycourses)) {usort($mycourses, function($a, $b) { return strcasecmp($a->fullname,$b->fullname); });}$item = new \stdClass();$item->userid = $user->id;$item->firstname = $user->firstname;$item->lastname = $user->lastname;$item->email = $user->email;$item->extras = local_cesa_reportes_get_user_extra_field_by_userid($user->id);$item->courses = [];foreach($mycourses as $val){if($filter_by_company_active && $filter_courseids) {if(!in_array($val->id, $filter_courseids)) {continue;}}if(isset($courses[ $val->id ])) {$course = $courses[$val->id];} else {$course = get_course($val->id);$courses[ $val->id ] = $course;}if(isset($num_activities_assigned_for_range[$course->id])) {$num_activities_assigned = $num_activities_assigned_for_range[$course->id];} else {$num_activities_assigned = local_cesa_reportes_get_num_activities_assigned_range($course->id, $filter_from, $filter_to);$num_activities_assigned_for_range[$course->id] = $num_activities_assigned;}if(isset($num_activities_assigned_for_course[$course->id])) {$num_activities_assigned_course = $num_activities_assigned_for_course[$course->id];} else {$num_activities_assigned_course = local_cesa_reportes_get_num_activities_assigned($course->id);$num_activities_assigned_for_course[$course->id] = $num_activities_assigned;}if(isset($categories[ $course->category ])) {$category = $categories[ $course->category ];} else {$category = $DB->get_field('course_categories', 'name', ['id' => $course->category]);$categories[ $course->category ] = $category;}$item_course = new \stdClass();$item_course->courseid = $course->id;$item_course->name = $course->fullname;$item_course->category = $category;$item_course->percentage = 0;$item_course->activities_assigned = $num_activities_assigned;$item_course->activities_completed = local_cesa_reportes_get_num_activities_completed_range($course->id, $user->id, $filter_from, $filter_to);if($item_course->activities_assigned > 0 && $item_course->activities_completed > 0) {$item_course->percentage = ( $item_course->activities_completed * 100 ) / $item_course->activities_assigned;}$item_course->percentage_course = 0;$item_course->activities_assigned_course = $num_activities_assigned_course;$item_course->activities_completed_course = local_cesa_reportes_get_num_activities_completed($course->id, $user->id);if($item_course->activities_assigned_course > 0 && $item_course->activities_completed_course > 0) {$item_course->percentage_course = ( $item_course->activities_completed_course * 100 ) / $item_course->activities_assigned_course;}if($include_modules) {$activities_completed = local_cesa_reportes_get_activities_completed_range($course->id, $user->id, $filter_from, $filter_to);if(isset($modules_for_course[ $course->id ])) {$modules = $modules_for_course[ $course->id ];} else {$modules = [];$records = get_fast_modinfo($val->id)->get_cms();foreach($records as $record){$modules[ $record->id ] = $record->get_formatted_name();}$modules_for_course[ $course->id ] = $modules;}$item_course->activities = [];$ids = local_cesa_reportes_get_activities_assigned_range($course->id, $filter_from, $filter_to);foreach($ids as $id){if(isset($activities_completed[ $id ])) {$timecompleted = $activities_completed[$id];} else {$timecompleted = 0;}$activity = new \stdClass();$activity->module = $modules[$id];$activity->timecompleted = $timecompleted;array_push($item_course->activities, $activity);}}$item_course->timeenrolled = local_cesa_reportes_get_enroll_for_course_and_userid($course->id, $user->id);$item_course->timecompleted = local_cesa_reportes_get_timecompleted_for_course_and_userid_range($course->id, $user->id, $filter_from, $filter_to);$item_course->timecompleted_course = local_cesa_reportes_get_timecompleted_for_course_and_userid($course->id, $user->id);$min_and_max_date_events = local_cesa_reportes_get_min_and_max_date_events_range($course->id, $user->id, $filter_from, $filter_to);$item_course->time_activities_first = $min_and_max_date_events->min;$item_course->time_activities_last = $min_and_max_date_events->max;$min_and_max_date_events = local_cesa_reportes_get_min_and_max_date_events($course->id, $user->id);$item_course->time_activities_first_course = $min_and_max_date_events->min;$item_course->time_activities_last_course = $min_and_max_date_events->max;/*$completion = new \completion_info($course);if ($completion->is_enabled()) {$item_course->completion_is_enabled = true;$item_course->percentage = \core_completion\progress::get_course_progress_percentage($course, $user->id);$item_course->percentage = !$item_course->percentage ? 0 : $item_course->percentage;} else {$item_course->completion_is_enabled = false;}*/array_push($item->courses, $item_course);}array_push($items, $item);}return ['pagination' => ['page' => $page,'max_users' => $max_users,'max_pages' => $max_pages],'items' => $items] ;}