public function __construct()
{
$this->db = DbConn::getInstance();
$this->users_filter = false;
$this->courses_filter = false;
$this->user_level = Docebo::user()->getUserLevelId();
if ($this->user_level != ADMIN_GROUP_GODADMIN) {
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$this->users_filter = $adminManager->getAdminUsers(Docebo::user()->getIdST());
$all_courses = false;
$array_courses = array();
$admin_courses = $adminManager->getAdminCourse(Docebo::user()->getIdST());
foreach ($admin_courses['course'] as $key => $id_course) {
if ($key > 0) {
$array_courses[$key] = $id_course;
}
}
if (isset($admin_courses['course'][0])) {
$all_courses = true;
} elseif (isset($admin_courses['course'][-1])) {
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
$user_catalogue = $cat_man->getUserAllCatalogueId(Docebo::user()->getIdSt());
if (count($user_catalogue) > 0) {
$courses = array();
foreach ($user_catalogue as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);
if (empty($courses)) {
$courses = $catalogue_course;
} else {
$courses = array_merge($courses, $catalogue_course);
}
}
foreach ($courses as $id_course) {
if ($id_course != 0) {
$array_courses[$id_course] = $id_course;
}
}
} elseif (Get::sett('on_catalogue_empty', 'off') == 'on') {
$all_courses = true;
}
} else {
if (!empty($admin_courses['coursepath'])) {
require_once _lms_ . '/lib/lib.coursepath.php';
$path_man = new CoursePath_Manager();
$coursepath_course =& $path_man->getAllCourses($admin_courses['coursepath']);
$array_courses = array_merge($array_courses, $coursepath_course);
}
if (!empty($admin_courses['catalogue'])) {
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
foreach ($admin_courses['catalogue'] as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);
$array_courses = array_merge($array_courses, $catalogue_course);
}
}
}
if (!$all_courses) {
$this->courses_filter = array_values($array_courses);
}
//if "$all_courses" is true, than leave "$this->courses_filter" as false
}
}
/**
* Return the output in the selected format for the report with the filters given
* @param string $type output type
* @param array $report_data a properly formatted list of rule to follow
* @param string $other
* @return string the properly formated report
*/
function _get_courses_query($type = 'html', $report_data = NULL, $other = '')
{
require_once $GLOBALS['where_lms'] . '/lib/lib.course.php';
$output = '';
$jump_url = '';
$org_chart_subdivision = 0;
// not implemented
$elem_selected = array();
$lang =& DoceboLanguage::createInstance('report', 'framework');
$acl_man = new DoceboACLManager();
$acl_man->include_suspended = TRUE;
$course_man = new Man_Course();
// read form _SESSION (XXX: change this) the report setting
$filter_userselection = !$report_data && isset($_SESSION['report_tempdata']['rows_filter']['users']) ? $_SESSION['report_tempdata']['rows_filter']['users'] : $report_data['rows_filter']['users'];
$filter_columns = !$report_data && $_SESSION['report_tempdata']['columns_filter'] ? $_SESSION['report_tempdata']['columns_filter'] : $report_data['columns_filter'];
if (!$report_data && isset($_SESSION['report_tempdata']['rows_filter']['all_users'])) {
$alluser = $_SESSION['report_tempdata']['rows_filter']['all_users'] ? 1 : 0;
} else {
$alluser = $report_data['rows_filter']['all_users'] ? 1 : 0;
}
// break filters into a more usable format
$filter_allcourses = $filter_columns['all_courses'];
$filter_courseselection =& $filter_columns['selected_courses'];
$order_by = isset($filter_columns['order_by']) ? $filter_columns['order_by'] : "userid";
$order_dir = isset($filter_columns['order_dir']) ? $filter_columns['order_dir'] : "asc";
$show_suspended = 'active_only';
if (isset($filter_columns['show_suspended']) && $filter_columns['show_suspended']) {
$show_suspended = 'all';
}
// retrive the user selected
if ($alluser > 0) {
// all the user selected (we can avoid this ? no we need to hide the suspended users)
$user_selected =& $acl_man->getAllUsersIdst();
} else {
// resolve the user selection
$user_selected =& $acl_man->getAllUsersFromSelection($filter_userselection);
}
//apply sub admin filters, if needed
$userlevelid = Docebo::user()->getUserLevelId();
if ($userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {
//filter users
$alluser = false;
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$admin_users = $adminManager->getAdminUsers(Docebo::user()->getIdST());
$admin_users = $acl_man->getAllUsersFromSelection($admin_users);
$user_selected = array_intersect($user_selected, $admin_users);
unset($admin_users);
//filter courses
$admin_allcourses = false;
$admin_courses = $adminManager->getAdminCourse(Docebo::user()->getIdST());
if (!$filter_allcourses) {
$rs = sql_query("SELECT idCourse FROM %lms_course");
$course_selected = array();
while (list($id_course) = sql_fetch_row($rs)) {
$course_selected[] = $id_course;
}
}
if (isset($admin_courses['course'][0])) {
//No filter
$admin_allcourses = true;
} elseif (isset($admin_courses['course'][-1])) {
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
$user_catalogue = $cat_man->getUserAllCatalogueId(Docebo::user()->getIdSt());
if (count($user_catalogue) > 0) {
$courses = array(0);
foreach ($user_catalogue as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);
$courses = array_merge($courses, $catalogue_course);
}
foreach ($courses as $id_course) {
if ($id_course != 0) {
$admin_courses['course'][$id_course] = $id_course;
}
}
} elseif (Get::sett('on_catalogue_empty', 'off') == 'on') {
//No filter
}
if (!empty($admin_courses['course'])) {
$rs = sql_query("SELECT idCourse FROM %lms_course");
$course_selected = array();
while (list($id_course) = sql_fetch_row($rs)) {
$course_selected[] = $id_course;
}
$_clist = array_values($admin_courses['course']);
$course_selected = array_intersect($course_selected, $_clist);
} else {
$course_selected = array();
}
} else {
$array_courses = array();
$array_courses = array_merge($array_courses, $admin_courses['course']);
if (!empty($admin_courses['coursepath'])) {
require_once _lms_ . '/lib/lib.coursepath.php';
$path_man = new Catalogue_Manager();
$coursepath_course =& $path_man->getAllCourses($admin_courses['coursepath']);
$array_courses = array_merge($array_courses, $coursepath_course);
}
if (!empty($admin_courses['catalogue'])) {
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
foreach ($admin_courses['catalogue'] as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);
$array_courses = array_merge($array_courses, $catalogue_course);
}
}
$admin_courses['course'] = array_merge($admin_courses['course'], $array_courses);
if (!empty($admin_courses['course'])) {
$rs = sql_query("SELECT idCourse FROM %lms_course");
$course_selected = array();
while (list($id_course) = sql_fetch_row($rs)) {
$course_selected[] = $id_course;
}
$_clist = array_values($admin_courses['course']);
$course_selected = array_intersect($course_selected, $_clist);
} else {
$course_selected = array();
}
}
unset($admin_courses);
if (!$filter_allcourses) {
$filter_courseselection = array_intersect($filter_courseselection, $course_selected);
} else {
$filter_courseselection = $course_selected;
}
if ($filter_allcourses && $admin_allcourses) {
$filter_allcourses = true;
} else {
$filter_allcourses = false;
}
}
$show_classrooms_editions = isset($filter_columns['show_classrooms_editions']) ? (bool) $filter_columns['show_classrooms_editions'] : false;
$classrooms_editions_info = array();
if ($show_classrooms_editions) {
//retrieve classrooms info
$query = "SELECT d.*, MIN(dd.date_begin) AS date_1, MAX(dd.date_end) AS date_2 " . " FROM %lms_course_date AS d JOIN %lms_course_date_day AS dd ON (d.id_date = dd.id_date) " . (!$filter_allcourses ? " AND d.id_course IN (" . implode(',', $filter_courseselection) . ") " : "") . " GROUP BY dd.id_date";
$res = sql_query($query);
while ($obj = sql_fetch_object($res)) {
$classrooms_editions_info['classrooms'][$obj->id_date] = $obj;
}
//retrieve editions info
//TO DO ...
}
// if we must subdived the users into the org_chart folders we must retrive some extra info
if ($org_chart_subdivision == 1) {
require_once $GLOBALS['where_framework'] . '/lib/lib.orgchart.php';
$org_man = new OrgChartManager();
if ($alluser == 1) {
$elem_selected = $org_man->getAllGroupIdFolder();
} else {
$elem_selected = $user_selected;
}
$org_name = $org_man->getFolderFormIdst($elem_selected);
if ($userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$admin_tree = $adminManager->getAdminTree(Docebo::user()->getIdST());
$org_name_temp = $org_name;
$org_name = array();
foreach ($org_name_temp as $id => $value) {
if (isset($admin_tree[$id])) {
$org_name[$id] = $value;
}
}
}
}
if (empty($user_selected)) {
cout($lang->def('_NULL_SELECTION'), 'content');
return;
}
// Retrive all the course
$id_courses = $course_man->getAllCourses();
if (empty($id_courses)) {
return $lang->def('_NULL_COURSE_SELECTION');
}
$re_category = sql_query("\r\n\t\tSELECT idCategory, path\r\n\t\tFROM " . $GLOBALS['prefix_lms'] . "_category");
$category_list = array(0 => $lang->def('_NONE'));
$category_path_list = array(0 => '/');
while (list($id_cat, $name_cat) = sql_fetch_row($re_category)) {
$category_list[$id_cat] = substr($name_cat, strrpos($name_cat, '/') + 1);
$category_path_list[$id_cat] = substr($name_cat, 5, strlen($name_cat) - 5);
//eliminates "/root"
}
$time_list = array();
$session_list = array();
$lastaccess_list = array();
$query = "\r\n\t\tSELECT idUser, idCourse, COUNT(*), SUM(UNIX_TIMESTAMP(lastTime) - UNIX_TIMESTAMP(enterTime)), MAX(lastTime)\r\n\t\tFROM " . $GLOBALS['prefix_lms'] . "_tracksession\r\n\t\tWHERE 1 " . ($alluser > 0 ? "" : "AND idUser IN ( " . implode(',', $user_selected) . " ) ") . ($filter_allcourses ? "" : "AND idCourse IN (" . implode(',', $filter_courseselection) . ") ");
//if($start_time != '') $query .= " AND enterTime >= '".$start_time."' ";
//if($end_time != '') $query .= " AND lastTime <= '".$end_time."' ";
$query .= "GROUP BY idUser, idCourse ";
$re_time = sql_query($query);
while (list($id_u, $id_c, $session_num, $time_num, $last_num) = sql_fetch_row($re_time)) {
$session_list[$id_u . '_' . $id_c] = $session_num;
$time_list[$id_u . '_' . $id_c] = $time_num;
$lastaccess_list[$id_u . '_' . $id_c] = $last_num;
}
//recover start and final score
require_once $GLOBALS['where_lms'] . '/lib/lib.orgchart.php';
$org_man = new OrganizationManagement(false);
$score_start = $org_man->getStartObjectScore($user_selected, array_keys($id_courses));
$score_final = $org_man->getFinalObjectScore($user_selected, array_keys($id_courses));
require_once $GLOBALS['where_lms'] . '/lib/lib.coursereport.php';
$rep_man = new CourseReportManager();
$score_course = $rep_man->getUserFinalScore($user_selected, array_keys($id_courses));
//set query suspended users condition
$query_show_suspended = "u.valid = 1";
//default condition
switch ($show_suspended) {
case "all":
$query_show_suspended = "1";
break;
case "suspended_only":
$query_show_suspended = "u.valid = 0";
break;
case "active_only":
$query_show_suspended = "u.valid = 1";
break;
}
//set query order by param
$_dir = "ASC";
switch ($order_dir) {
case "desc":
$_dir = "DESC";
break;
}
$query_order_by = "u.userid, c.code";
switch ($order_by) {
case 'firstname':
$query_order_by = "u.firstname " . $_dir . ", u.userid " . $_dir . ", c.code " . $_dir . "";
break;
case 'lastname':
$query_order_by = "u.lastname " . $_dir . ", u.userid " . $_dir . ", c.code " . $_dir . "";
break;
case 'email':
$query_order_by = "u.email " . $_dir . ", u.userid " . $_dir . ", c.code " . $_dir . "";
break;
case 'course_code':
$query_order_by = "c.code " . $_dir . ", u.userid " . $_dir . "";
break;
case 'course_name':
$query_order_by = "c.name " . $_dir . ", c.code " . $_dir . ", u.userid " . $_dir . "";
break;
case 'status':
$query_order_by = "cu.status " . $_dir . ", u.userid " . $_dir . ", c.code " . $_dir . "";
break;
case 'level':
$query_order_by = "cu.level " . $_dir . ", u.userid " . $_dir . ", c.code " . $_dir . "";
break;
case 'date_subscription':
$query_order_by = "cu.date_inscr " . $_dir . ", u.userid " . $_dir . ", c.code " . $_dir . "";
break;
case 'date_first_access':
$query_order_by = "cu.date_first_access " . $_dir . ", u.userid " . $_dir . ", c.code " . $_dir . "";
break;
case 'date_last_access':
$query_order_by = "cu.date_last_access " . $_dir . ", u.userid " . $_dir . ", c.code " . $_dir . "";
break;
case 'date_complete':
$query_order_by = "cu.date_complete " . $_dir . ", u.userid " . $_dir . ", c.code " . $_dir . "";
break;
}
if ($org_chart_subdivision == 0) {
// find some information
if ($show_classrooms_editions) {
$query_course_user = "******" . " cu.status, cu.date_inscr, cu.date_first_access, cu.date_complete, c.mediumTime, c.course_type, c.credits, " . " u.userid, u.firstname, u.lastname, u.email, u.register_date, u.valid, " . " d.id_date, du.date_subscription AS classroom_date_subscription, du.date_complete AS classroom_date_complete " . " FROM ( %lms_courseuser AS cu JOIN %lms_course AS c JOIN %adm_user as u " . " ON (cu.idCourse = c.idCourse AND cu.idUser = u.idst) ) " . " LEFT JOIN (%lms_course_date AS d JOIN %lms_course_date_user AS du ON (d.id_date = du.id_date)) " . " ON (d.id_course = cu.idCourse AND du.id_user = cu.idUser) " . " WHERE " . $query_show_suspended . " " . ($alluser > 0 ? "" : "AND cu.idUser IN ( " . implode(',', $user_selected) . " ) ") . ($filter_allcourses ? "" : "AND c.idCourse IN (" . implode(',', $filter_courseselection) . ") ") . " ORDER BY " . $query_order_by;
} else {
$query_course_user = "******" . " cu.status, cu.date_inscr, cu.date_first_access, cu.date_complete, c.mediumTime, c.course_type, c.credits, " . " u.userid, u.firstname, u.lastname, u.email, u.register_date, u.valid " . " FROM %lms_courseuser AS cu JOIN %lms_course AS c JOIN %adm_user as u " . " ON (cu.idCourse = c.idCourse AND cu.idUser = u.idst) " . " WHERE " . $query_show_suspended . " = 1 " . ($alluser > 0 ? "" : "AND cu.idUser IN ( " . implode(',', $user_selected) . " ) ") . ($filter_allcourses ? "" : "AND c.idCourse IN (" . implode(',', $filter_courseselection) . ") ") . " ORDER BY " . $query_order_by;
}
$output .= $this->_printTable_courses($type, $query_course_user, $category_list, $category_path_list, $session_list, $lastaccess_list, $time_list, $score_start, $score_final, $score_course, $user_selected, $filter_columns, $show_classrooms_editions, $classrooms_editions_info);
} else {
$date_now = Format::date(date("Y-m-d H:i:s"));
reset($org_name);
while (list($idst_group, $folder_name) = each($org_name)) {
if ($type == 'html') {
cout('<div class="datasummary">' . '<b>' . $lang->def('_FOLDER_NAME') . ' :</b> ' . $folder_name['name'] . ($folder_name['type_of_folder'] == ORG_CHART_WITH_DESCENDANTS ? ' (' . $lang->def('_WITH_DESCENDANTS') . ')' : '') . '<br />', 'content');
cout('<b>' . $lang->def('_CREATION_DATE') . ' :</b> ' . $date_now . '<br /></div>', 'content');
}
$group_user = $acl_man->getGroupAllUser($idst_group);
// find some information
if ($show_classrooms_editions) {
$query_course_user = "******" . " cu.status, cu.date_inscr, cu.date_first_access, cu.date_complete, c.mediumTime, c.course_type, c.credits, " . " u.userid, u.firstname, u.lastname, u.email, u.register_date, u.valid, " . " d.id_date, du.date_subscription AS classroom_date_subscription, du.date_complete AS classroom_date_complete " . " FROM ( %lms_courseuser AS cu JOIN %lms_course AS c JOIN %adm_user as u " . " ON (cu.idCourse = c.idCourse AND cu.idUser = u.idst) ) " . " LEFT JOIN (%lms_course_date AS d JOIN %lms_course_date_user AS du ON (d.id_date = du.id_date)) " . " ON (d.id_course = cu.idCourse AND du.id_user = cu.idUser) " . " WHERE " . $query_show_suspended . " " . (!empty($group_user) ? " AND cu.idUser IN ( " . implode(',', $group_user) . " ) " : " AND 0 ") . ($filter_allcourses ? "" : "AND c.idCourse IN (" . implode(',', $filter_courseselection) . ") ") . " ORDER BY " . $query_order_by;
} else {
$query_course_user = "******" . " cu.status, cu.date_inscr, cu.date_first_access, cu.date_complete, c.mediumTime, c.course_type, c.credits, " . " u.userid, u.firstname, u.lastname, u.email, u.register_date, u.valid " . " FROM %lms_courseuser AS cu JOIN %lms_course AS c JOIN %adm_user as u " . " ON (cu.idCourse = c.idCourse AND cu.idUser = u.idst) " . " WHERE " . $query_show_suspended . " = 1 " . (!empty($group_user) ? " AND cu.idUser IN ( " . implode(',', $group_user) . " ) " : " AND 0 ") . ($filter_allcourses ? "" : "AND c.idCourse IN (" . implode(',', $filter_courseselection) . ") ") . " ORDER BY " . $query_order_by;
}
$output .= $this->_printTable_courses($type, $query_course_user, $category_list, $category_path_list, $session_list, $lastaccess_list, $time_list, $score_start, $score_final, $score_course, $filter_userselection, $filter_columns, $show_classrooms_editions, $classrooms_editions_info);
}
}
return $output;
}
public function fastsubscribe()
{
if (!$this->permissions['subscribe_course']) {
$output = array('success' => false, 'message' => $this->_getMessage("no permission"));
echo $this->json->encode($output);
return;
} else {
if (!$this->checkAdminLimit()) {
$output = array('success' => false, 'message' => Lang::t('_SUBSCRIBE_LIMIT_REACHED', 'subscribe'));
echo $this->json->encode($output);
return;
}
}
$id_user = Get::req('idst', DOTY_INT, 0);
//user idst
$userid = Get::req('userid', DOTY_STRING, '');
//user username
$result = false;
if (Docebo::user()->getUserLevelId() != ADMIN_GROUP_GODADMIN) {
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$admin_users = $adminManager->getAdminUsers(Docebo::user()->getIdST());
$is_admin = true;
}
//adjust idst to subscribe
if ($id_user <= 0) {
$id_user = false;
if ($userid != '') {
$id_user = Docebo::aclm()->getUserST($userid);
}
}
if ($id_user <= 0) {
$output = array('success' => false, 'message' => $this->_getMessage("invalid user"));
echo $this->json->encode($output);
return;
}
if (isset($admin_users) && array_search($id_user, $admin_users) == false && Docebo::user()->getUserLevelId() != ADMIN_GROUP_GODADMIN) {
$output = array('success' => true);
echo $this->json->encode($output);
} elseif ($id_user) {
$level = 3;
//student level
$waiting = false;
$result = $this->model->subscribeUser($id_user, $level, $waiting);
if ($result) {
require_once _lms_ . '/lib/lib.course.php';
$docebo_course = new DoceboCourse($this->id_course);
$level_idst =& $docebo_course->getCourseLevel($this->model->getIdCourse());
if (count($level_idst) == 0 || $level_idst[1] == '') {
$level_idst =& $docebo_course->createCourseLevel($this->id_course);
}
//$this->acl_man->addToGroup($level_idst[$level], $id_user);
$this->_addToCourseGroup($level_idst[$level], $id_user);
// Save limit preference for admin
if (Docebo::user()->getUserLevelId() != ADMIN_GROUP_GODADMIN) {
$admin_pref = new AdminPreference();
$pref = $admin_pref->getAdminRules(Docebo::user()->getIdSt());
if ($pref['admin_rules.limit_course_subscribe'] == 'on') {
$user_pref = new UserPreferences(Docebo::user()->getIdSt());
$subscribed_count = $user_pref->getPreference('user_subscribed_count');
$user_pref->setPreference('user_subscribed_count', $subscribed_count + 1);
}
}
//check if we have selected send alert checkbox
$send_alert = Get::req('send_alert', DOTY_INT, 0) > 0;
if ($send_alert) {
require_once _base_ . '/lib/lib.eventmanager.php';
$uinfo = Docebo::aclm()->getUser($id_user, false);
$course_info = $docebo_course->getAllInfo();
$array_subst = array('[url]' => Get::sett('url'), '[course]' => $course_info['name'], '[medium_time]' => $course_info['mediumTime'], '[firstname]' => $uinfo[ACL_INFO_FIRSTNAME], '[lastname]' => $uinfo[ACL_INFO_LASTNAME], '[username]' => Docebo::aclm()->relativeId($uinfo[ACL_INFO_USERID]));
// message to user that is waiting
$msg_composer = new EventMessageComposer();
$msg_composer->setSubjectLangText('email', '_NEW_USER_SUBSCRIBED_SUBJECT', false);
$msg_composer->setBodyLangText('email', '_NEW_USER_SUBSCRIBED_TEXT', $array_subst);
$msg_composer->setBodyLangText('sms', '_NEW_USER_SUBSCRIBED_TEXT_SMS', $array_subst);
// send message to the user subscribed
$users = array($id_user);
createNewAlert('UserCourseInserted', 'subscribe', 'insert', '1', 'User subscribed', $users, $msg_composer, $send_alert, true);
}
}
$output = array('success' => $result);
echo $this->json->encode($output);
}
}
function _get_courses_query($type = 'html', $report_data = NULL, $other = '')
{
require_once $GLOBALS['where_lms'] . '/lib/lib.course.php';
require_once dirname(__FILE__) . '/report_tableprinter.php';
if ($report_data == NULL) {
$ref =& $_SESSION['report_tempdata'];
} else {
$ref =& $report_data;
}
$fw = $GLOBALS['prefix_fw'];
$lms = $GLOBALS['prefix_lms'];
$sel_all = $ref['rows_filter']['select_all'];
$sel_type = $ref['rows_filter']['selection_type'];
$selection = $ref['rows_filter']['selection'];
$all_courses = $ref['columns_filter']['all_courses'];
$courses = $ref['columns_filter']['selected_courses'];
$cols =& $ref['columns_filter']['showed_columns'];
$acl = new DoceboACLManager();
$html = '';
$man = new Man_Course();
$courses_codes = $man->getAllCourses();
if ($all_courses) {
$courses = array();
foreach ($courses_codes as $key => $val) {
$courses[] = $key;
}
}
/*
if(Docebo::user()->getUserLevelId() != ADMIN_GROUP_GODADMIN) {
// if the usre is a subadmin with only few course assigned
require_once(_base_.'/lib/lib.preference.php');
$adminManager = new AdminPreference();
$admin_tree = $adminManager->getAdminTree(Docebo::user()->getIdST());
$courses = array_intersect($courses, $admin_tree['courses']);
}
*/
$increment = 0;
if ($cols['completed']) {
$increment++;
}
if ($cols['initinere']) {
$increment++;
}
if ($cols['notstarted']) {
$increment++;
}
if ($cols['show_percentages']) {
$increment = $increment * 2;
}
/*
//admin users filter
$acl_man = Docebo::user()->getACLManager();
$userlevelid = Docebo::user()->getUserLevelId();
if ( $userlevelid != ADMIN_GROUP_GODADMIN ) {
require_once(_base_.'/lib/lib.preference.php');
$adminManager = new AdminPreference();
$admin_tree = $adminManager->getAdminTree(Docebo::user()->getIdST());
$admin_users = $acl_man->getAllUsersFromSelection($admin_tree);
$admin_users = array_unique($admin_users);
}*/
$userlevelid = Docebo::user()->getUserLevelId();
if ($userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {
//filter users
$alluser = false;
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$admin_users = $adminManager->getAdminUsers(Docebo::user()->getIdST());
//$user_selected = array_intersect($user_selected, $admin_users);
//unset($admin_users);
//filter courses
$admin_courses = $adminManager->getAdminCourse(Docebo::user()->getIdST());
if ($all_courses) {
$all_courses = false;
$rs = sql_query("SELECT idCourse FROM %lms_course");
$course_selected = array();
while (list($id_course) = sql_fetch_row($rs)) {
$course_selected[] = $id_course;
}
}
if (isset($admin_courses['course'][0])) {
//No filter
} elseif (isset($admin_courses['course'][-1])) {
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
$user_catalogue = $cat_man->getUserAllCatalogueId(Docebo::user()->getIdSt());
if (count($user_catalogue) > 0) {
$courses = array(0);
foreach ($user_catalogue as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);
$courses = array_merge($courses, $catalogue_course);
}
foreach ($courses as $id_course) {
if ($id_course != 0) {
$admin_courses['course'][$id_course] = $id_course;
}
}
} elseif (Get::sett('on_catalogue_empty', 'off') == 'on') {
//No filter
}
if (!empty($admin_courses['course'])) {
$_clist = array_values($admin_courses['course']);
$course_selected = array_intersect($course_selected, $_clist);
} else {
$course_selected = array();
}
} else {
$array_courses = array();
$array_courses = array_merge($array_courses, $admin_courses['course']);
if (!empty($admin_courses['coursepath'])) {
require_once _lms_ . '/lib/lib.coursepath.php';
$path_man = new Catalogue_Manager();
$coursepath_course =& $path_man->getAllCourses($admin_courses['coursepath']);
$array_courses = array_merge($array_courses, $coursepath_course);
}
if (!empty($admin_courses['catalogue'])) {
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
foreach ($admin_courses['catalogue'] as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);
$array_courses = array_merge($array_courses, $catalogue_course);
}
}
$admin_courses['course'] = array_merge($admin_courses['course'], $array_courses);
if (!empty($admin_courses['course'])) {
$_clist = array_values($admin_courses['course']);
$course_selected = array_intersect($course_selected, $_clist);
} else {
$course_selected = array();
}
}
unset($admin_courses);
}
switch ($sel_type) {
case 'groups':
//retrieve all labels
$orgchart_labels = array();
$query = "SELECT * FROM " . $fw . "_org_chart WHERE lang_code='" . getLanguage() . "'";
$res = sql_query($query);
while ($row = mysql_fetch_assoc($res)) {
$orgchart_labels[$row['id_dir']] = $row['translation'];
}
$labels = array();
$query = "SELECT * FROM " . $fw . "_group WHERE (hidden='false' OR groupid LIKE '/oc_%' OR groupid LIKE '/ocd_%') AND type='free'";
$res = sql_query($query);
while ($row = mysql_fetch_assoc($res)) {
if ($row['hidden'] == 'false') {
$labels[$row['idst']] = $acl->relativeId($row['groupid']);
} else {
$temp = explode("_", $row['groupid']);
//echo '<div>'.print_r($temp,true).'</div>';
if ($temp[0] == '/oc') {
$labels[$row['idst']] = $temp[1] != 0 ? $orgchart_labels[$temp[1]] : '';
} elseif ($temp[0] == '/ocd') {
$labels[$row['idst']] = $temp[1] != 0 ? $orgchart_labels[$temp[1]] : '';
}
}
}
$tot_size = 2;
$head1 = array(array('colspan' => 2, 'value' => $this->lang->def('_GROUPS')));
$head2 = array($this->lang->def('_NAME'), $this->lang->def('_TOTAL'));
foreach ($courses as $course) {
$head1[] = array('value' => ($courses_codes[$course]['code'] ? '[' . $courses_codes[$course]['code'] . '] ' : '') . $courses_codes[$course]['name'], 'colspan' => $increment);
if ($cols['completed']) {
$head2[] = $this->lang->def('_USER_STATUS_END');
}
if ($cols['completed'] && $cols['show_percentages']) {
$head2[] = _PERCENT_SIMBOL;
}
if ($cols['initinere']) {
$head2[] = $this->lang->def('_USER_STATUS_BEGIN');
}
if ($cols['initinere'] && $cols['show_percentages']) {
$head2[] = _PERCENT_SIMBOL;
}
if ($cols['notstarted']) {
$head2[] = $this->lang->def('_USER_STATUS_SUBS');
}
if ($cols['notstarted'] && $cols['show_percentages']) {
$head2[] = _PERCENT_SIMBOL;
}
$tot_size += $increment;
}
$buffer = new ReportTablePrinter($type, true);
$buffer->openTable('', '');
$buffer->openHeader();
$buffer->addHeader($head1);
$buffer->addHeader($head2);
$buffer->closeHeader();
$tot_users = 0;
$course_stats = array();
//for each group, retrieve label and user statistics
foreach ($selection as $dir_id => $group_id) {
$group_users = $acl->getGroupAllUser($group_id);
if ($userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {
$group_users = array_intersect($group_users, $admin_users);
}
$users_num = count($group_users);
$line = array();
$line[] = $labels[$group_id];
$line[] = $users_num;
$tot_users += $users_num;
if (count($group_users) > 0) {
$query = "SELECT cu.idUser, cu.idCourse, cu.status " . " FROM " . $lms . "_courseuser as cu, " . $lms . "_course as c, " . $fw . "_user as u " . " WHERE cu.idUser=u.idst AND cu.idCourse=c.idCourse " . " AND u.idst IN (" . implode(",", $group_users) . ") " . (!$all_courses ? " AND c.idCourse IN (" . implode(",", $courses) . " )" : "");
$res = sql_query($query);
//$tot_completed = 0;
while ($row = mysql_fetch_assoc($res)) {
if (!isset($course_stats[$row['idCourse']][$group_id])) {
$course_stats[$row['idCourse']][$group_id] = array('completed' => 0, 'initinere' => 0, 'notstarted' => 0, 'total' => 0);
}
switch ((int) $row['status']) {
case 2:
$course_stats[$row['idCourse']][$group_id]['completed']++;
break;
case 1:
$course_stats[$row['idCourse']][$group_id]['initinere']++;
break;
case 0:
$course_stats[$row['idCourse']][$group_id]['notstarted']++;
break;
}
$course_stats[$row['idCourse']][$group_id]['total']++;
}
foreach ($courses as $course) {
if (isset($course_stats[$course][$group_id])) {
if ($course_stats[$course][$group_id]['total'] == 0) {
$dividend = 1;
} else {
$dividend = $course_stats[$course][$group_id]['total'];
}
if ($cols['completed']) {
$line[] = $course_stats[$course][$group_id]['completed'];
}
if ($cols['completed'] && $cols['show_percentages']) {
$line[] = number_format(100.0 * $course_stats[$course][$group_id]['completed'] / $dividend, 2, ',', '') . _PERCENT_SIMBOL;
}
if ($cols['initinere']) {
$line[] = $course_stats[$course][$group_id]['initinere'];
}
if ($cols['initinere'] && $cols['show_percentages']) {
$line[] = number_format(100.0 * $course_stats[$course][$group_id]['initinere'] / $dividend, 2, ',', '') . _PERCENT_SIMBOL;
}
if ($cols['notstarted']) {
$line[] = $course_stats[$course][$group_id]['notstarted'];
}
if ($cols['notstarted'] && $cols['show_percentages']) {
$line[] = number_format(100.0 * $course_stats[$course][$group_id]['notstarted'] / $dividend, 2, ',', '') . _PERCENT_SIMBOL;
}
} else {
if ($cols['completed']) {
$line[] = '0';
}
if ($cols['completed'] && $cols['show_percentages']) {
$line[] = '0,00%';
}
if ($cols['initinere']) {
$line[] = '0';
}
if ($cols['initinere'] && $cols['show_percentages']) {
$line[] = '0,00%';
}
if ($cols['notstarted']) {
$line[] = '0';
}
if ($cols['notstarted'] && $cols['show_percentages']) {
$line[] = '0,00%';
}
}
}
//$line[] = $tot_completed;
} else {
foreach ($courses as $course) {
if ($cols['completed']) {
$line[] = '0';
}
if ($cols['completed'] && $cols['show_percentages']) {
$line[] = '0,00%';
}
if ($cols['initinere']) {
$line[] = '0';
}
if ($cols['initinere'] && $cols['show_percentages']) {
$line[] = '0,00%';
}
if ($cols['notstarted']) {
$line[] = '0';
}
if ($cols['notstarted'] && $cols['show_percentages']) {
$line[] = '0,00%';
}
}
}
$buffer->addLine($line);
}
$buffer->closeBody();
//echo '<pre>'.print_r($course_stats,true).'</pre>';
//calc totals
$foot = array('', $tot_users);
foreach ($courses as $course) {
$completed_total = 0;
$initinere_total = 0;
$notstarted_total = 0;
$total_total = 0;
foreach ($selection as $dir_id => $group_id) {
$completed_total += isset($course_stats[$course][$group_id]['completed']) ? $course_stats[$course][$group_id]['completed'] : 0;
$initinere_total += isset($course_stats[$course][$group_id]['initinere']) ? $course_stats[$course][$group_id]['initinere'] : 0;
$notstarted_total += isset($course_stats[$course][$group_id]['notstarted']) ? $course_stats[$course][$group_id]['notstarted'] : 0;
$total_total += isset($course_stats[$course][$group_id]['total']) ? $course_stats[$course][$group_id]['total'] : 0;
}
if ($cols['completed']) {
$foot[] = $completed_total;
}
if ($cols['completed'] && $cols['show_percentages']) {
$foot[] = $total_total != 0 ? number_format(100.0 * $completed_total / $total_total, 2, _DECIMAL_SEPARATOR, '') . _PERCENT_SIMBOL : '0' . _DECIMAL_SEPARATOR . '00' . _PERCENT_SIMBOL;
}
if ($cols['initinere']) {
$foot[] = $initinere_total;
}
if ($cols['initinere'] && $cols['show_percentages']) {
$foot[] = $total_total != 0 ? number_format(100.0 * $initinere_total / $total_total, 2, _DECIMAL_SEPARATOR, '') . _PERCENT_SIMBOL : '0' . _DECIMAL_SEPARATOR . '00' . _PERCENT_SIMBOL;
}
if ($cols['notstarted']) {
$foot[] = $notstarted_total;
}
if ($cols['notstarted'] && $cols['show_percentages']) {
$foot[] = $total_total != 0 ? number_format(100.0 * $notstarted_total / $total_total, 2, _DECIMAL_SEPARATOR, '') . _PERCENT_SIMBOL : '0' . _DECIMAL_SEPARATOR . '00' . _PERCENT_SIMBOL;
}
}
$buffer->setFoot($foot);
$buffer->closeTable();
$html .= $buffer->get();
break;
case 'users':
$temp = array();
// resolve the user selection
$users =& $acl->getAllUsersFromIdst($selection);
if ($userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {
$users = array_intersect($users, $admin_users);
}
if (count($users) <= 0) {
$html .= '<p>' . $this->lang->def('_EMPTY_SELECTION') . '</p>';
break;
}
$query = "SELECT cu.idUser, cu.idCourse, cu.status, u.userId, c.code, u.firstname, u.lastname " . " FROM ( " . $lms . "_courseuser as cu " . " JOIN " . $lms . "_course as c ON ( cu.idCourse = c.idCourse) ) " . " JOIN " . $fw . "_user as u ON (cu.idUser = u.idst) " . " WHERE 1 " . " AND cu.idCourse IN (" . implode(",", $courses) . ") " . ($sel_all ? "" : " AND idUser IN (" . implode(",", $users) . ")") . "";
$res = sql_query($query);
while ($row = mysql_fetch_array($res)) {
if (!isset($temp[$row['idUser']])) {
$temp[$row['idUser']] = array('username' => $acl->relativeId($row['userId']), 'fullname' => $row['lastname'] . ' ' . $row['firstname'], 'courses' => array());
}
$temp[$row['idUser']]['courses'][$row['idCourse']] = $row['status'];
}
//echo '<pre>';
//print_r($temp);
//draw table
$tot_size = 1;
$head2 = array($this->lang->def('_USERNAME'), $this->lang->def('_FULLNAME'));
$head1 = array(array('colspan' => 2, 'value' => $this->lang->def('_USER')));
foreach ($courses as $course) {
$head1[] = array('value' => ($courses_codes[$course]['code'] ? '[' . $courses_codes[$course]['code'] . '] ' : '') . $courses_codes[$course]['name'], 'colspan' => $increment);
if ($cols['completed']) {
$head2[] = $this->lang->def('_USER_STATUS_END');
}
if ($cols['completed'] && $cols['show_percentages']) {
$head2[] = _PERCENT_SIMBOL;
}
if ($cols['initinere']) {
$head2[] = $this->lang->def('_USER_STATUS_BEGIN');
}
if ($cols['initinere'] && $cols['show_percentages']) {
$head2[] = _PERCENT_SIMBOL;
}
if ($cols['notstarted']) {
$head2[] = $this->lang->def('_USER_STATUS_SUBS');
}
if ($cols['notstarted'] && $cols['show_percentages']) {
$head2[] = _PERCENT_SIMBOL;
}
$tot_size += $increment;
}
$buffer = new ReportTablePrinter($type, true);
$buffer->openTable('', '');
$buffer->openHeader();
$buffer->addHeader($head1);
$buffer->addHeader($head2);
$buffer->closeHeader();
$completed_total = array();
$initinere_total = array();
$notstarted_total = array();
$courses_total = array();
foreach ($courses as $course) {
$completed_total[$course] = 0;
$initinere_total[$course] = 0;
$notstarted_total[$course] = 0;
$courses_total[$course] = 0;
}
$buffer->openBody();
foreach ($temp as $id_user => $table_row) {
$line = array();
$line[] = $table_row['username'];
$line[] = $table_row['fullname'];
foreach ($courses as $course) {
if (isset($table_row['courses'][$course])) {
if ($cols['completed']) {
$line[] = $table_row['courses'][$course] == 2 ? 1 : 0;
}
if ($cols['completed'] && $cols['show_percentages']) {
$line[] = $table_row['courses'][$course] == 2 ? '100' . _DECIMAL_SEPARATOR . '00' . _PERCENT_SIMBOL : '0' . _PERCENT_SIMBOL;
}
if ($cols['initinere']) {
$line[] = $table_row['courses'][$course] == 1 ? 1 : 0;
}
if ($cols['initinere'] && $cols['show_percentages']) {
$line[] = $table_row['courses'][$course] == 1 ? '100' . _DECIMAL_SEPARATOR . '00' . _PERCENT_SIMBOL : '0' . _PERCENT_SIMBOL;
}
if ($cols['notstarted']) {
$line[] = $table_row['courses'][$course] == 0 ? 1 : 0;
}
if ($cols['notstarted'] && $cols['show_percentages']) {
$line[] = $table_row['courses'][$course] == 0 ? '100' . _DECIMAL_SEPARATOR . '00' . _PERCENT_SIMBOL : '0' . _PERCENT_SIMBOL;
}
switch ((int) $table_row['courses'][$course]) {
case 2:
if (isset($completed_total[$course])) {
$completed_total[$course] += 1;
} else {
$completed_course[$course] = 1;
}
break;
case 1:
if (isset($initinere_total[$course])) {
$initinere_total[$course] += 1;
} else {
$initinere_course[$course] = 1;
}
break;
case 0:
if (isset($notstarted_total[$course])) {
$notstarted_total[$course] += 1;
} else {
$notstarted_course[$course] = 1;
}
break;
}
if (isset($courses_total[$course])) {
$courses_total[$course] += 1;
} else {
$courses_total[$course] = 1;
}
} else {
if ($cols['completed']) {
$line[] = '0';
}
if ($cols['completed'] && $cols['show_percentages']) {
$line[] = '0' . _PERCENT_SIMBOL;
}
if ($cols['initinere']) {
$line[] = '0';
}
if ($cols['initinere'] && $cols['show_percentages']) {
$line[] = '0' . _PERCENT_SIMBOL;
}
if ($cols['notstarted']) {
$line[] = '0';
}
if ($cols['notstarted'] && $cols['show_percentages']) {
$line[] = '0' . _PERCENT_SIMBOL;
}
if (isset($courses_total[$course])) {
$courses_total[$course] += 1;
} else {
$courses_total[$course] = 1;
}
}
}
$buffer->addLine($line);
}
$buffer->closeBody();
$totals_line = array('', '');
foreach ($courses as $course) {
$completed_num = isset($completed_total[$course]) ? $completed_total[$course] : '0';
$initinere_num = isset($initinere_total[$course]) ? $initinere_total[$course] : '0';
$notstarted_num = isset($notstarted_total[$course]) ? $notstarted_total[$course] : '0';
$total_num = isset($courses_total[$course]) ? $courses_total[$course] : '0';
if ($cols['completed']) {
$totals_line[] = $completed_num;
}
if ($cols['completed'] && $cols['show_percentages']) {
$totals_line[] = $total_num != 0 ? number_format(100.0 * $completed_num / $total_num, 2, _DECIMAL_SEPARATOR, '') . _PERCENT_SIMBOL : '0' . _DECIMAL_SEPARATOR . '00' . _PERCENT_SIMBOL;
}
if ($cols['initinere']) {
$totals_line[] = $initinere_num;
}
if ($cols['initinere'] && $cols['show_percentages']) {
$totals_line[] = $total_num != 0 ? number_format(100.0 * $initinere_num / $total_num, 2, _DECIMAL_SEPARATOR, '') . _PERCENT_SIMBOL : '0' . _DECIMAL_SEPARATOR . '00' . _PERCENT_SIMBOL;
}
if ($cols['notstarted']) {
$totals_line[] = $notstarted_num;
}
if ($cols['notstarted'] && $cols['show_percentages']) {
$totals_line[] = $total_num != 0 ? number_format(100.0 * $notstarted_num / $total_num, 2, _DECIMAL_SEPARATOR, '') . _PERCENT_SIMBOL : '0' . _DECIMAL_SEPARATOR . '00' . _PERCENT_SIMBOL;
}
}
$buffer->setFoot($totals_line);
$buffer->closeTable();
$html .= $buffer->get();
break;
}
return $html;
}
function _get_users_query($type = 'html', $report_data = NULL, $other = '')
{
//$jump_url, $alluser, $org_chart_subdivision, $start_time, $end_time
if ($report_data == NULL) {
$ref =& $_SESSION['report_tempdata'];
} else {
$ref =& $report_data;
}
$all_courses = $ref['rows_filter']['all_courses'];
$course_selected =& $ref['rows_filter']['selected_courses'];
$time_range = $ref['columns_filter']['time_belt']['time_range'];
$start_time = $ref['columns_filter']['time_belt']['start_date'];
$end_time = $ref['columns_filter']['time_belt']['end_date'];
$org_chart_subdivision = $ref['columns_filter']['org_chart_subdivision'];
$filter_cols = $ref['columns_filter']['showed_cols'];
$show_percent = isset($ref['columns_filter']['show_percent']) ? (bool) $ref['columns_filter']['show_percent'] : true;
$show_suspended = isset($ref['columns_filter']['show_suspended']) ? (bool) $ref['columns_filter']['show_suspended'] : false;
$only_students = isset($ref['columns_filter']['only_students']) ? (bool) $ref['columns_filter']['only_students'] : false;
$show_assessment = isset($ref['columns_filter']['show_assessment']) ? (bool) $ref['columns_filter']['show_assessment'] : false;
$show_classrooms_editions = isset($ref['columns_filter']['show_classrooms_editions']) ? (bool) $ref['columns_filter']['show_classrooms_editions'] : false;
if ($time_range != 0) {
$start_time = date("Y-m-d H:i:s", time() - $time_range * 24 * 3600);
$end_time = date("Y-m-d H:i:s");
} else {
$start_time = $start_time;
$end_time = $end_time;
}
$alluser = $ref['columns_filter']['all_users'];
$output = '';
$lang =& DoceboLanguage::createInstance('course', 'framework');
require_once $GLOBALS['where_framework'] . '/lib/lib.directory.php';
require_once _base_ . '/lib/lib.userselector.php';
$acl_man = new DoceboACLManager();
$acl_man->include_suspended = TRUE;
$course_man = new Man_Course();
$user_level = Docebo::user()->getUserLevelId();
if ($alluser == 0) {
$user_selected =& $acl_man->getAllUsersFromSelection($ref['columns_filter']['users']);
} else {
$user_selected =& $acl_man->getAllUsersIdst();
}
//apply filters for sub-admins
if ($user_level != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {
//filter users
$alluser = 0;
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$admin_users = $adminManager->getAdminUsers(Docebo::user()->getIdST());
$admin_users = $acl_man->getAllUsersFromSelection($admin_users);
$user_selected = array_intersect($user_selected, $admin_users);
unset($admin_users);
//filter courses
$admin_courses = $adminManager->getAdminCourse(Docebo::user()->getIdST());
if ($all_courses) {
$all_courses = false;
$rs = sql_query("SELECT idCourse FROM %lms_course");
$course_selected = array();
while (list($id_course) = sql_fetch_row($rs)) {
$course_selected[] = $id_course;
}
}
if (isset($admin_courses['course'][0])) {
//No filter
} elseif (isset($admin_courses['course'][-1])) {
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
$user_catalogue = $cat_man->getUserAllCatalogueId(Docebo::user()->getIdSt());
if (count($user_catalogue) > 0) {
$courses = array(0);
foreach ($user_catalogue as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);
$courses = array_merge($courses, $catalogue_course);
}
foreach ($courses as $id_course) {
if ($id_course != 0) {
$admin_courses['course'][$id_course] = $id_course;
}
}
} elseif (Get::sett('on_catalogue_empty', 'off') == 'on') {
//No filter
}
if (!empty($admin_courses['course'])) {
$_clist = array_values($admin_courses['course']);
$course_selected = array_intersect($course_selected, $_clist);
} else {
$course_selected = array();
}
} else {
$array_courses = array();
$array_courses = array_merge($array_courses, $admin_courses['course']);
if (!empty($admin_courses['coursepath'])) {
require_once _lms_ . '/lib/lib.coursepath.php';
$path_man = new Catalogue_Manager();
$coursepath_course =& $path_man->getAllCourses($admin_courses['coursepath']);
$array_courses = array_merge($array_courses, $coursepath_course);
}
if (!empty($admin_courses['catalogue'])) {
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
foreach ($admin_courses['catalogue'] as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);
$array_courses = array_merge($array_courses, $catalogue_course);
}
}
$admin_courses['course'] = array_merge($admin_courses['course'], $array_courses);
if (!empty($admin_courses['course'])) {
$_clist = array_values($admin_courses['course']);
$course_selected = array_intersect($course_selected, $_clist);
} else {
$course_selected = array();
}
}
unset($admin_courses);
}
if ($org_chart_subdivision == 1) {
require_once $GLOBALS['where_framework'] . '/lib/lib.orgchart.php';
$org_man = new OrgChartManager();
if ($alluser == 1) {
$user_level = Docebo::user()->getUserLevelId();
if ($user_level != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {
$elem_selected = $user_selected;
} else {
$elem_selected = $org_man->getAllGroupIdFolder();
}
} else {
$elem_selected = $user_selected;
}
$org_name = $org_man->getFolderFormIdst($elem_selected);
if ($user_level != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$admin_tree = $adminManager->getAdminTree(Docebo::user()->getIdST());
$org_name_temp = $org_name;
$org_name = array();
foreach ($org_name_temp as $id => $value) {
if (isset($admin_tree[$id])) {
$org_name[$id] = $value;
}
}
}
} else {
$elem_selected = array();
}
if (empty($user_selected)) {
//no users to display
$GLOBALS['page']->add($lang->def('_NULL_SELECTION'), 'content');
return;
}
// Retrieve all the course
$id_courses = array();
if (!$show_classrooms_editions) {
$q_courses = "SELECT c.idCourse, c.code, c.name, c.description, c.course_type " . " FROM %lms_course As c " . ($show_assessment ? "" : " WHERE c.course_type <> 'assessment' ") . " ORDER BY c.code, c.name";
$r_courses = sql_query($q_courses);
while (list($id, $code, $name, $description, $course_type) = sql_fetch_row($r_courses)) {
$id_courses[$id] = array('id_course' => $id, 'code' => $code, 'name' => $name, 'description' => $description, 'course_type' => $course_type);
}
} else {
$q_courses = "SELECT c.idCourse, c.code, c.name, c.description, c.course_type, d.id_date " . " FROM %lms_course As c LEFT JOIN %lms_course_date AS d ON (c.idCourse = d.id_course) " . ($show_assessment ? "" : " WHERE c.course_type <> 'assessment' ") . " ORDER BY c.code, c.name, d.code, d.name";
$r_courses = sql_query($q_courses);
while (list($id, $code, $name, $description, $course_type, $id_date) = sql_fetch_row($r_courses)) {
$index = $course_type == 'classroom' ? $id . '_' . $id_date : $id;
$id_courses[$index] = array('id_course' => $id, 'code' => $code, 'name' => $name, 'description' => $description, 'course_type' => $course_type);
}
}
if (empty($id_courses)) {
//no courses on the platform
cout($lang->def('_NULL_COURSE_SELECTION'), 'content');
return;
}
$id_coursedates = array();
$date_now = Format::date(date("Y-m-d H:i:s"));
$classrooms_editions_info = array();
if ($show_classrooms_editions) {
//retrieve classrooms info
$query = "SELECT d.*, MIN(dd.date_begin) AS date_1, MAX(dd.date_end) AS date_2 " . " FROM %lms_course_date AS d JOIN %lms_course_date_day AS dd ON (d.id_date = dd.id_date) " . (!$all_courses ? " AND d.id_course IN (" . implode(',', $course_selected) . ") " : "") . " GROUP BY dd.id_date";
$res = sql_query($query);
while ($obj = sql_fetch_object($res)) {
$classrooms_editions_info['classrooms'][$obj->id_date] = $obj;
}
//retrieve editions info
//TO DO ...
}
if (!$show_classrooms_editions) {
if ($org_chart_subdivision == 0) {
$query_course_user = "******" . " FROM %lms_courseuser AS cu JOIN %adm_user AS u ON (cu.idUser = u.idst) " . " WHERE cu.idUser IN ( " . implode(',', $user_selected) . " ) " . ($all_courses ? "" : "AND cu.idCourse IN (" . implode(',', $course_selected) . ") ") . ($show_suspended ? "" : " AND u.valid = 1 ") . ($only_students ? " AND cu.level = 3 " : "");
if ($start_time != '' && $start_time != '0000-00-00') {
$query_course_user .= " AND cu.date_complete >= '" . $start_time . "' ";
}
if ($end_time != '' && $end_time != '0000-00-00') {
$query_course_user .= " AND cu.date_complete <= '" . $end_time . "'";
}
$num_iscr = array();
$num_nobegin = array();
$num_itinere = array();
$num_end = array();
$time_in_course = array();
$effective_user = array();
$re_course_user = sql_query($query_course_user);
while (list($id_u, $id_c, $fisrt_access, $date_complete, $status) = sql_fetch_row($re_course_user)) {
if (isset($num_iscr[$id_c])) {
++$num_iscr[$id_c];
} else {
$num_iscr[$id_c] = 1;
}
switch ($status) {
case _CUS_CONFIRMED:
break;
case _CUS_SUSPEND:
break;
case _CUS_SUBSCRIBED:
if (isset($num_nobegin[$id_c])) {
++$num_nobegin[$id_c];
} else {
$num_nobegin[$id_c] = 1;
}
break;
case _CUS_BEGIN:
if (isset($num_itinere[$id_c])) {
++$num_itinere[$id_c];
} else {
$num_itinere[$id_c] = 1;
}
break;
case _CUS_END:
if (isset($num_end[$id_c])) {
++$num_end[$id_c];
} else {
$num_end[$id_c] = 1;
}
break;
}
$effective_user[] = $id_u;
}
if (!empty($effective_user)) {
$query_time = "\r\n\t\t\t\t\t\tSELECT idCourse, SUM(UNIX_TIMESTAMP(lastTime) - UNIX_TIMESTAMP(enterTime))\r\n\t\t\t\t\t\tFROM %lms_tracksession\r\n\t\t\t\t\t\tWHERE idUser IN ( " . implode(',', $effective_user) . " ) ";
if ($start_time != '' && $start_time != '0000-00-00') {
$query_time .= " AND enterTime >= '" . $start_time . "' ";
}
if ($end_time != '' && $end_time != '0000-00-00') {
$query_time .= " AND enterTime <= '" . $end_time . "' ";
}
$query_time .= " GROUP BY idCourse ";
$re_time = sql_query($query_time);
while (list($id_c, $time_num) = sql_fetch_row($re_time)) {
$time_in_course[$id_c] = $time_num;
}
}
$output .= $this->_printTable_users($type, $acl_man, $id_courses, $num_iscr, $num_nobegin, $num_itinere, $num_end, $time_in_course, $filter_cols, $show_percent, $show_classrooms_editions, $classrooms_editions_info);
} else {
reset($org_name);
while (list($idst_group, $folder_name) = each($org_name)) {
if ($first) {
$first = FALSE;
} else {
$output .= '<br /><br /><br />';
}
$output .= '<div class="datasummary">' . '<b>' . $lang->def('_FOLDER_NAME') . ' :</b> ' . $folder_name['name'] . ($folder_name['type_of_folder'] == ORG_CHART_WITH_DESCENDANTS ? ' (' . $lang->def('_WITH_DESCENDANTS') . ')' : '') . '<br />';
if ($start_time != '' && $start_time != '0000-00-00' || $end_time != '' && $end_time != '0000-00-00') {
$output .= '<b>' . $lang->def('_TIME_BELT_2') . ' :</b> ' . ($start_time != '' && $start_time != '0000-00-00' ? ' <b>' . $lang->def('_START_TIME') . ' </b>' . Format::date($start_time, 'date') : '') . ($end_time != '' && $end_time != '0000-00-00' ? ' <b>' . $lang->def('_TO') . ' </b>' . Format::date($end_time, 'date') : '') . '<br />';
}
$group_user = $acl_man->getGroupAllUser($idst_group);
$query_course_user = "******" . " FROM %lms_courseuser AS cu JOIN %adm_user AS u ON (cu.idUser = u.idst) " . " WHERE cu.idUser IN ( " . implode(',', $group_user) . " ) " . ($all_courses ? "" : "AND cu.idCourse IN (" . implode(',', $course_selected) . ") ") . ($show_suspended ? "" : " AND u.valid = 1 ") . ($only_students ? " AND cu.level = 3 " : "");
if ($start_time != '' && $start_time != '0000-00-00') {
$query_course_user .= " AND cu.date_complete >= '" . $start_time . "' ";
}
if ($end_time != '' && $end_time != '0000-00-00') {
$query_course_user .= " AND cu.date_complete <= '" . $end_time . "' AND cu.level='3'";
}
$num_iscr = array();
$num_nobegin = array();
$num_itinere = array();
$num_end = array();
$time_in_course = array();
$effective_user = array();
$re_course_user = sql_query($query_course_user);
while (list($id_u, $id_c, $fisrt_access, $date_complete) = sql_fetch_row($re_course_user)) {
if (isset($num_iscr[$id_c])) {
++$num_iscr[$id_c];
} else {
$num_iscr[$id_c] = 1;
}
if ($fisrt_access === NULL) {
//never enter
if (isset($num_nobegin[$id_c])) {
++$num_nobegin[$id_c];
} else {
$num_nobegin[$id_c] = 1;
}
} elseif ($date_complete === NULL) {
//enter
if (isset($num_itinere[$id_c])) {
++$num_itinere[$id_c];
} else {
$num_itinere[$id_c] = 1;
}
} else {
//complete
if (isset($num_end[$id_c])) {
++$num_end[$id_c];
} else {
$num_end[$id_c] = 1;
}
}
$effective_user[] = $id_u;
}
if (!empty($group_user)) {
$query_time = "\r\n\t\t\t\t\t\tSELECT idCourse, SUM(UNIX_TIMESTAMP(lastTime) - UNIX_TIMESTAMP(enterTime))\r\n\t\t\t\t\t\tFROM %lms_tracksession\r\n\t\t\t\t\t\tWHERE idUser IN ( " . implode(',', $group_user) . " ) ";
if ($start_time != '' && $start_time != '0000-00-00') {
$query_time .= " AND enterTime >= '" . $start_time . "' ";
}
if ($end_time != '' && $end_time != '0000-00-00') {
$query_time .= " AND enterTime <= '" . $end_time . "' ";
}
$query_time .= " GROUP BY idCourse ";
$re_time = sql_query($query_time);
while (list($id_c, $time_num) = sql_fetch_row($re_time)) {
$time_in_course[$id_c] = $time_num;
}
}
reset($id_courses);
$output .= $this->_printTable_users($type, $acl_man, $id_courses, $num_iscr, $num_nobegin, $num_itinere, $num_end, $time_in_course, $filter_cols, $show_percent, $show_classrooms_editions, $classrooms_editions_info);
}
}
} else {
//check classrooms and editions
if ($org_chart_subdivision == 0) {
$query_course_user = "******" . " FROM (%lms_courseuser AS cu JOIN %lms_course AS c JOIN %adm_user AS u ON (cu.idCourse = c.idCourse AND cu.idUser = u.idst)) " . " LEFT JOIN (%lms_course_date AS d JOIN %lms_course_date_user AS du ON (du.id_date=d.id_date)) " . " ON (du.id_user = cu.idUser AND d.id_course = cu.idCourse) " . " WHERE cu.idUser IN ( " . implode(',', $user_selected) . " ) " . ($all_courses ? "" : "AND cu.idCourse IN (" . implode(',', $course_selected) . ")") . ($show_suspended ? "" : " AND u.valid = 1 ") . ($only_students ? " AND cu.level = 3 " : "");
if ($start_time != '' && $start_time != '0000-00-00') {
$query_course_user .= " AND cu.date_complete >= '" . $start_time . "' ";
}
if ($end_time != '' && $end_time != '0000-00-00') {
$query_course_user .= " AND cu.date_complete <= '" . $end_time . "'";
}
$num_iscr = array();
$num_nobegin = array();
$num_itinere = array();
$num_end = array();
$time_in_course = array();
$effective_user = array();
$re_course_user = sql_query($query_course_user);
while (list($id_u, $id_c, $fisrt_access, $date_complete, $status, $course_type, $id_date) = sql_fetch_row($re_course_user)) {
$index = $course_type == 'classroom' ? $id_c . '_' . $id_date : $id_c;
if (isset($num_iscr[$index])) {
++$num_iscr[$index];
} else {
$num_iscr[$index] = 1;
}
switch ($status) {
case _CUS_CONFIRMED:
break;
case _CUS_SUSPEND:
break;
case _CUS_SUBSCRIBED:
if (isset($num_nobegin[$index])) {
++$num_nobegin[$index];
} else {
$num_nobegin[$index] = 1;
}
break;
case _CUS_BEGIN:
if (isset($num_itinere[$index])) {
++$num_itinere[$index];
} else {
$num_itinere[$index] = 1;
}
break;
case _CUS_END:
if (isset($num_end[$index])) {
++$num_end[$index];
} else {
$num_end[$index] = 1;
}
break;
}
if (!in_array($id_u, $effective_user)) {
$effective_user[] = $id_u;
}
}
if (!empty($effective_user)) {
$query_time = "SELECT idCourse, SUM(UNIX_TIMESTAMP(lastTime) - UNIX_TIMESTAMP(enterTime)) " . " FROM %lms_tracksession WHERE idUser IN ( " . implode(',', $effective_user) . " ) ";
if ($start_time != '' && $start_time != '0000-00-00') {
$query_time .= " AND enterTime >= '" . $start_time . "' ";
}
if ($end_time != '' && $end_time != '0000-00-00') {
$query_time .= " AND enterTime <= '" . $end_time . "' ";
}
$query_time .= " GROUP BY idCourse ";
$re_time = sql_query($query_time);
while (list($id_c, $time_num) = sql_fetch_row($re_time)) {
$time_in_course[$id_c] = $time_num;
}
}
$output .= $this->_printTable_users($type, $acl_man, $id_courses, $num_iscr, $num_nobegin, $num_itinere, $num_end, $time_in_course, $filter_cols, $show_percent, $show_classrooms_editions, $classrooms_editions_info);
} else {
$first = true;
reset($org_name);
while (list($idst_group, $folder_name) = each($org_name)) {
if ($first) {
$first = FALSE;
} else {
$output .= '<br /><br /><br />';
}
$output .= '<div class="datasummary">' . '<b>' . $lang->def('_FOLDER_NAME') . ' :</b> ' . $folder_name['name'] . ($folder_name['type_of_folder'] == ORG_CHART_WITH_DESCENDANTS ? ' (' . $lang->def('_WITH_DESCENDANTS') . ')' : '') . '<br />';
if ($start_time != '' && $start_time != '0000-00-00' || $end_time != '' && $end_time != '0000-00-00') {
$output .= '<b>' . $lang->def('_TIME_BELT_2') . ' :</b> ' . ($start_time != '' && $start_time != '0000-00-00' ? ' <b>' . $lang->def('_START_TIME') . ' </b>' . Format::date($start_time, 'date') : '') . ($end_time != '' && $end_time != '0000-00-00' ? ' <b>' . $lang->def('_TO') . ' </b>' . Format::date($end_time, 'date') : '') . '<br />';
}
$group_user = $acl_man->getGroupAllUser($idst_group);
$query_course_user = "******" . " FROM (%lms_courseuser AS cu JOIN %lms_course AS c JOIN %adm_user AS u ON (cu.idCourse = c.idCourse AND cu.idUser = u.idst)) " . " LEFT JOIN (%lms_course_date AS d JOIN %lms_course_date_user AS du ON (du.id_date=d.id_date)) " . " ON (du.id_user = cu.idUser AND d.id_course = cu.idCourse) " . " WHERE cu.idUser IN ( " . implode(',', $group_user) . " ) " . ($all_courses ? "" : "AND cu.idCourse IN (" . implode(',', $course_selected) . ")") . ($show_suspended ? "" : " AND u.valid = 1 ") . ($only_students ? " AND cu.level = 3 " : "");
if ($start_time != '' && $start_time != '0000-00-00') {
$query_course_user .= " AND cu.date_complete >= '" . $start_time . "' ";
}
if ($end_time != '' && $end_time != '0000-00-00') {
$query_course_user .= " AND cu.date_complete <= '" . $end_time . "'";
}
$num_iscr = array();
$num_nobegin = array();
$num_itinere = array();
$num_end = array();
$time_in_course = array();
$effective_user = array();
//$re_course_user = sql_query($query_course_user);
//while(list($id_u, $id_c, $fisrt_access, $date_complete) = sql_fetch_row($re_course_user)) {
$re_course_user = sql_query($query_course_user);
while (list($id_u, $id_c, $fisrt_access, $date_complete, $status, $course_type, $id_date) = sql_fetch_row($re_course_user)) {
$index = $course_type == 'classroom' ? $id_c . '_' . $id_date : $id_c;
if (isset($num_iscr[$index])) {
++$num_iscr[$index];
} else {
$num_iscr[$index] = 1;
}
if ($fisrt_access === NULL) {
//never enter
if (isset($num_nobegin[$index])) {
++$num_nobegin[$index];
} else {
$num_nobegin[$index] = 1;
}
} elseif ($date_complete === NULL) {
//enter
if (isset($num_itinere[$index])) {
++$num_itinere[$index];
} else {
$num_itinere[$index] = 1;
}
} else {
//complete
if (isset($num_end[$index])) {
++$num_end[$index];
} else {
$num_end[$index] = 1;
}
}
if (!in_array($id_u, $effective_user)) {
$effective_user[] = $id_u;
}
}
if (!empty($group_user)) {
$query_time = "SELECT idCourse, SUM(UNIX_TIMESTAMP(lastTime) - UNIX_TIMESTAMP(enterTime)) " . " FROM %lms_tracksession WHERE idUser IN ( " . implode(',', $group_user) . " ) ";
if ($start_time != '' && $start_time != '0000-00-00') {
$query_time .= " AND enterTime >= '" . $start_time . "' ";
}
if ($end_time != '' && $end_time != '0000-00-00') {
$query_time .= " AND enterTime <= '" . $end_time . "' ";
}
$query_time .= " GROUP BY idCourse ";
$re_time = sql_query($query_time);
while (list($id_c, $time_num) = sql_fetch_row($re_time)) {
$time_in_course[$id_c] = $time_num;
}
}
reset($id_courses);
$output .= $this->_printTable_users($type, $acl_man, $id_courses, $num_iscr, $num_nobegin, $num_itinere, $num_end, $time_in_course, $filter_cols, $show_percent, $show_classrooms_editions, $classrooms_editions_info);
}
}
}
return $output;
}
