function _get_games_query($type = 'html', $report_data = NULL, $other = '')
{
require_once dirname(__FILE__) . '/report_tableprinter.php';
if ($report_data == NULL) {
$ref =& $_SESSION['report_tempdata'];
} else {
$ref =& $report_data;
}
$_ERR_NOUSER = Lang::t('_EMPTY_SELECTION', 'report');
$_ERR_NOCOMP = Lang::t('_EMPTY_SELECTION', 'report');
$_ERR_NODATA = Lang::t('_NO_CONTENT', 'report');
require_once _lms_ . '/lib/lib.report.php';
$lang_type = _getLOtranslations();
$sel_all = $ref['rows_filter']['select_all'];
$arr_selected_users = $ref['rows_filter']['selection'];
//list of users selected in the filter (users, groups and org.branches)
$comp_all = $ref['columns_filter']['all_games'];
$arr_selected_comp = $ref['columns_filter']['comp_selection'];
//list of communications selected in the filter
$start_date = substr($ref['columns_filter']['comp_start_date'], 0, 10);
$end_date = substr($ref['columns_filter']['comp_end_date'], 0, 10);
//check and validate time period dates
if (!preg_match('/^(\\d{4})\\D?(0[1-9]|1[0-2])\\D?([12]\\d|0[1-9]|3[01])$/', $start_date) || $start_date == '0000-00-00') {
$start_date = '';
}
if (!preg_match('/^(\\d{4})\\D?(0[1-9]|1[0-2])\\D?([12]\\d|0[1-9]|3[01])$/', $end_date) || $end_date == '0000-00-00') {
$end_date = '';
}
if ($start_date != '') {
$start_date .= ' 00:00:00';
}
if ($end_date != '') {
$end_date .= ' 23:59:59';
}
if ($start_date != '' && $end_date != '') {
if ($start_date > $end_date) {
//invalid time period
$start_date = '';
$end_date = '';
}
}
//instantiate acl manager
$acl_man = new DoceboACLManager();
$acl_man->include_suspended = true;
//extract user idst from selection
if ($sel_all) {
$arr_selected_users = $acl_man->getAllUsersIdst();
} else {
$arr_selected_users = $acl_man->getAllUsersFromIdst($arr_selected_users);
}
//admin users filter
$userlevelid = Docebo::user()->getUserLevelId();
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());
$admin_users = $acl_man->getAllUsersFromIdst($admin_tree);
$admin_users = array_unique($admin_users);
//filter users selection by admin visible users
$arr_selected_users = array_intersect($arr_selected_users, $admin_users);
//free some memory
unset($admin_tree);
unset($admin_users);
unset($adminManager);
}
//Has the "All games" options been selected ?
if ($comp_all) {
$query = "SELECT id_game FROM %lms_games";
$res = $this->db->query($query);
$arr_selected_comp = array();
while (list($id_game) = $this->db->fetch_row($res)) {
$arr_selected_comp[] = $id_game;
}
}
//check selected users ...
//$arr_selected_users = array(); //list of users selected in the filter (users, groups and org.branches)
if ($arr_selected_users <= 0) {
cout('<p>' . $_ERR_NOUSER . '</p>');
return;
}
//$arr_selected_comp = array(); //list of communications selected in the filter
if ($arr_selected_comp <= 0) {
cout('<p>' . $_ERR_NOCOMP . '</p>');
return;
}
$arr_comp = array();
//array $id_comm => list of generic idst
foreach ($arr_selected_comp as $id_game) {
$arr_comp[$id_game] = array();
}
//if no users have been assigned to the games, than display as 0 - 0
$arr_idst = array();
//flat list of idst
$query = "SELECT * FROM %lms_games_access WHERE id_comp IN (" . implode(",", $arr_selected_comp) . ")";
$res = $this->db->query($query);
while (list($id_game, $idst) = $this->db->fetch_row($res)) {
$arr_idst[] = $idst;
$arr_comp[$id_game][] = $idst;
}
if (count($arr_idst) <= 0) {
cout('<p>' . $_ERR_NOUSER . '</p>');
return;
}
$arr_groups = array();
//flat list of group idst
$query = "SELECT idst FROM %adm_group WHERE idst IN (" . implode(",", $arr_idst) . ")";
$res = $this->db->query($query);
while (list($idst) = $this->db->fetch_row($res)) {
$arr_groups[] = $idst;
}
//if any group selected, then extract users and create an array [id_group][id_user]
$arr_idst_users_flat = array();
$arr_members = array();
//array $idst group => list of idst
if (count($arr_groups) > 0) {
$query = "SELECT idst, idstMember FROM %adm_group_members WHERE " . " idst IN (" . implode(",", $arr_groups) . ")" . " AND idstMember IN (" . implode(",", $arr_selected_users) . ")";
$res = $this->db->query($query);
while (list($idst, $idstMember) = $this->db->fetch_row($res)) {
$arr_members[$idst][] = $idstMember;
$arr_idst_users_flat[] = $idstMember;
}
}
//set an array with all users idst ($_all)
$diff = array_diff($arr_selected_users, $arr_groups);
$_all_users = array_merge($arr_idst_users_flat, $diff);
unset($diff);
if (count($_all_users) <= 0) {
cout('<p>' . $_ERR_NOUSER . '</p>');
return;
}
//users have been extracted by group, now calculate report's rows ----------
//get games info data and put it in an array by id_game => {info}
$arr_comp_data = array();
$query = "SELECT * FROM %lms_games WHERE id_game IN (" . implode(",", $arr_selected_comp) . ")";
$res = $this->db->query($query);
while ($obj = $this->db->fetch_obj($res)) {
$arr_comp_data[$obj->id_game] = array('title' => $obj->title, 'type_of' => $obj->type_of, 'start_date' => $obj->start_date, 'end_date' => $obj->end_date);
}
//which selected communication has been seen by selected users?
$arr_viewed = array();
$query = "SELECT idReference, COUNT(idUser) as count " . " FROM %lms_games_track WHERE status IN ('completed', 'passed') " . " AND idUser IN (" . implode(",", $_all_users) . ") " . " AND idReference IN (" . implode(",", $arr_selected_comp) . ") " . ($start_date != '' ? " AND dateAttempt >= '" . $start_date . "' " : "") . ($end_date != '' ? " AND dateAttempt <= '" . $end_date . "' " : "") . " GROUP BY idReference";
$res = $this->db->query($query);
while ($obj = $this->db->fetch_obj($res)) {
$arr_viewed[$obj->idReference] = $obj->count;
}
//calculate average values, no conditions on the status
$arr_average = array();
$query = "SELECT idReference, AVG(current_score) as average_current_score, " . " AVG(max_score) as average_max_score, AVG(num_attempts) as average_num_attempts " . " FROM %lms_games_track WHERE idUser IN (" . implode(",", $_all_users) . ") " . " AND idReference IN (" . implode(",", $arr_selected_comp) . ") " . ($start_date != '' ? " AND dateAttempt >= '" . $start_date . "' " : "") . ($end_date != '' ? " AND dateAttempt <= '" . $end_date . "' " : "") . " GROUP BY idReference";
$res = $this->db->query($query);
while ($obj = $this->db->fetch_obj($res)) {
$arr_average[$obj->idReference] = $obj;
}
/*
//user details buffer
$acl_man = Docebo::user()->getAclManager();
$user_details = array();
$query = "SELECT idst, userid FROM %adm_user WHERE idst IN (".implode(",", $_all_users).")";
$res = $this->db->query($query);
while ($obj = $this->db->fetch_obj($res)) {
$user_details[$obj->idst] = $acl_man($obj->userid);
}
*/
//set table properties and buffer
$head = array(Lang::t('_GAMES_TITLE', 'report'), Lang::t('_GAMES_TYPE', 'report'), Lang::t('_FROM', 'standard'), Lang::t('_TO', 'standard'), Lang::t('_GAMES_ATTEMPTED', 'report'), Lang::t('_GAMES_TOTAL', 'report'), Lang::t('_GAMES_PERCENT', 'report'), Lang::t('_GAMES_AVG_SCORE', 'report'), Lang::t('_GAMES_AVG_MAX_SCORE', 'report'), Lang::t('_GAMES_AVG_NUM_ATTEMPTS', 'report'));
$buffer = new ReportTablePrinter();
$buffer->openTable('', '');
$buffer->openHeader();
$buffer->addHeader($head);
$buffer->closeHeader();
$buffer->openBody();
//rows cycle
foreach ($arr_comp as $id_game => $comp_id_list) {
//calculate total assigned users for every communication
$count = 0;
foreach ($comp_id_list as $idst) {
if (array_key_exists($idst, $arr_members)) {
foreach ($arr_members[$idst] as $idst_user) {
$count++;
}
} else {
$count++;
}
}
//line (one per communication)
$line = array();
$type_of = $arr_comp_data[$id_game]['type_of'];
$completed_by = isset($arr_viewed[$id_game]) ? $arr_viewed[$id_game] : 0;
$line[] = $arr_comp_data[$id_game]['title'];
$line[] = isset($lang_type[$type_of]) ? $lang_type[$type_of] : '';
$line[] = Format::date($arr_comp_data[$id_game]['start_date'], 'date');
$line[] = Format::date($arr_comp_data[$id_game]['end_date'], 'date');
$line[] = $completed_by;
$line[] = $count;
$line[] = number_format(($count > 0 ? $completed_by / $count : 0) * 100, 2, ',', '') . ' %';
$avg1 = isset($arr_average[$id_game]) ? $arr_average[$id_game]->average_current_score : '';
$avg2 = isset($arr_average[$id_game]) ? $arr_average[$id_game]->average_max_score : '';
$avg3 = isset($arr_average[$id_game]) ? $arr_average[$id_game]->average_num_attempts : '';
$line[] = number_format($avg1, 2, ',', '.');
$line[] = number_format($avg2, 2, ',', '.');
$line[] = number_format($avg3, 2, ',', '.');
$buffer->addLine($line);
}
$buffer->closeBody();
$buffer->closeTable();
cout($buffer->get());
}
function _get_games_query($type = 'html', $report_data = NULL, $other = '')
{
require_once dirname(__FILE__) . '/report_tableprinter.php';
if ($report_data == NULL) {
$ref =& $_SESSION['report_tempdata'];
} else {
$ref =& $report_data;
}
$_ERR_NOUSER = Lang::t('_EMPTY_SELECTION', 'report');
$_ERR_NOCOMP = Lang::t('_EMPTY_SELECTION', 'report');
$_ERR_NODATA = Lang::t('_NO_CONTENT', 'report');
//LO object types translations
require_once _lms_ . '/lib/lib.report.php';
$lang_type = _getLOtranslations();
$sel_all = $ref['rows_filter']['all_users'];
$arr_selected_users = $ref['rows_filter']['users'];
//list of users selected in the filter (users, groups and org.branches)
$comp_all = isset($ref['columns_filter']['all_games']) ? $ref['columns_filter']['all_games'] : false;
$arr_selected_comp = isset($ref['columns_filter']['comp_selection']) ? $ref['columns_filter']['comp_selection'] : array();
//list of communications selected in the filter
$start_date = isset($ref['columns_filter']['comp_start_date']) ? substr($ref['columns_filter']['comp_start_date'], 0, 10) : '';
$end_date = isset($ref['columns_filter']['comp_end_date']) ? substr($ref['columns_filter']['comp_end_date'], 0, 10) : '';
//check and validate time period dates
if (!preg_match('/^(\\d{4})\\D?(0[1-9]|1[0-2])\\D?([12]\\d|0[1-9]|3[01])$/', $start_date) || $start_date == '0000-00-00') {
$start_date = '';
}
if (!preg_match('/^(\\d{4})\\D?(0[1-9]|1[0-2])\\D?([12]\\d|0[1-9]|3[01])$/', $end_date) || $end_date == '0000-00-00') {
$end_date = '';
}
if ($start_date != '') {
$start_date .= ' 00:00:00';
}
if ($end_date != '') {
$end_date .= ' 23:59:59';
}
if ($start_date != '' && $end_date != '') {
if ($start_date > $end_date) {
//invalid time period
$start_date = '';
$end_date = '';
}
}
//other checkings and validations
if (!$sel_all && count($selection) <= 0) {
cout('<p>' . $_ERR_NOUSER . '</p>');
return;
}
$acl_man = new DoceboACLManager();
$acl_man->include_suspended = true;
//extract user idst from selection
if ($sel_all) {
$arr_selected_users = $acl_man->getAllUsersIdst();
} else {
$arr_selected_users = $acl_man->getAllUsersFromIdst($arr_selected_users);
}
if ($comp_all) {
$query = "SELECT id_game FROM %lms_games";
$res = $this->db->query($query);
$arr_selected_comp = array();
while (list($id_game) = $this->db->fetch_row($res)) {
$arr_selected_comp[] = $id_game;
}
}
//admin users filter
$userlevelid = Docebo::user()->getUserLevelId();
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());
$admin_users = $acl_man->getAllUsersFromIdst($admin_tree);
$admin_users = array_unique($admin_users);
//filter users selection by admin visible users
$arr_selected_users = array_intersect($arr_selected_users, $admin_users);
//free some memory
unset($admin_tree);
unset($admin_users);
unset($adminManager);
}
//check selected users ...
if (count($arr_selected_users) <= 0) {
//message: no users selected
cout('<p>' . $_ERR_NOUSER . '</p>');
return;
}
//check selected communications ...
if (count($arr_selected_comp) <= 0) {
//message: no communications selected
cout('<p>' . $_ERR_NOCOMP . '</p>');
return;
}
//set table properties and buffer
$head = array(Lang::t('_GAMES_TITLE', 'report'), Lang::t('_GAMES_TYPE', 'report'), Lang::t('_FROM', 'report'), Lang::t('_TO', 'report'), Lang::t('_USER', 'report'), Lang::t('_GAMES_ATTEMPTED', 'report'), Lang::t('_GAMES_ATTEMPT_DATE', 'report'), Lang::t('_GAMES_FIRST_ATTEMPT_DATE', 'report'), Lang::t('_GAMES_CURRENT_SCORE', 'report'), Lang::t('_GAMES_MAX_SCORE', 'report'), Lang::t('_GAMES_NUM_ATTEMPTS', 'report'));
if ($this->use_mail) {
$head[] = array('style' => 'img-cell', 'value' => $this->_loadEmailIcon());
}
$buffer = new ReportTablePrinter();
$buffer->openTable('', '');
$buffer->openHeader();
$buffer->addHeader($head);
$buffer->closeHeader();
$buffer->openBody();
//rows cycle
//which selected communication has been seen by selected users?
$_YES = Lang::t('_YES', 'standard');
$_NO = Lang::t('_NO', 'standard');
$arr_viewed = array();
$query = "SELECT ct.idReference, c.title, c.type_of, c.start_date, c.end_date, ct.status, " . " ct.dateAttempt, ct.firstAttempt, ct.idUser, u.userid, u.firstname, u.lastname, " . " ct.current_score, ct.max_score, ct.num_attempts " . " FROM (%lms_games_track as ct " . " JOIN %lms_games as c ON (ct.idReference=c.id_game)) " . " JOIN %adm_user as u ON (ct.idUser=u.idst) " . " WHERE ct.idUser IN (" . implode(",", $arr_selected_users) . ") " . " AND c.id_game IN (" . implode(",", $arr_selected_comp) . ") " . ($start_date != '' ? " AND ct.dateAttempt >= '" . $start_date . "' " : "") . ($end_date != '' ? " AND ct.dateAttempt <= '" . $end_date . "' " : "") . " ORDER BY c.title, u.userid";
$res = $this->db->query($query);
if ($this->db->num_rows($res) <= 0) {
cout('<p>' . $_ERR_NODATA . '</p>');
return;
}
while ($obj = $this->db->fetch_obj($res)) {
$line = array();
$line[] = $obj->title;
$line[] = isset($lang_type[$obj->type_of]) ? $lang_type[$obj->type_of] : '';
$line[] = Format::date($obj->start_date, "date");
$line[] = Format::date($obj->end_date, "date");
$line[] = $acl_man->relativeId($obj->userid);
$line[] = $obj->status == 'completed' || $obj->status == 'passed' ? $_YES : $_NO;
$line[] = Format::date($obj->dateAttempt, "datetime");
$line[] = Format::date($obj->firstAttempt, "datetime");
$line[] = $obj->current_score;
$line[] = $obj->max_score;
$line[] = $obj->num_attempts;
if ($this->use_mail) {
$line[] = '<div class="align_center">' . Form::getInputCheckbox('mail_' . $obj->idUser, 'mail_recipients[]', $obj->idUser, isset($_POST['select_all']), '') . '</div>';
}
$buffer->addLine($line);
}
$buffer->closeBody();
$buffer->closeTable();
cout($buffer->get());
$this->_loadEmailActions();
}
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;
}
