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
}
}
function entrylist()
{
checkPerm('view');
require_once _base_ . '/lib/lib.table.php';
require_once _base_ . '/lib/lib.form.php';
require_once $GLOBALS['where_lms'] . '/lib/lib.coursepath.php';
require_once $GLOBALS['where_lms'] . '/lib/lib.course.php';
$lang =& DoceboLanguage::createInstance('catalogue', 'lms');
$out =& $GLOBALS['page'];
$id_cat = Get::req('id', DOTY_INT, 0);
$cat_name = getCatalogueName($id_cat);
$mod_perm = checkPerm('mod', true);
$title_area = array('index.php?modname=catalogue&op=catlist' => $lang->def('_CATALOGUE'), $cat_name);
$tb_entry = new Table();
$query_entry = "\r\n\tSELECT idEntry, type_of_entry\r\n\tFROM " . $GLOBALS['prefix_lms'] . "_catalogue_entry\r\n\tWHERE idCatalogue = '" . $id_cat . "'";
$re_entry = sql_query($query_entry);
$courses = array();
$coursepath = array();
while (list($id, $t_o_entry) = sql_fetch_row($re_entry)) {
if ($t_o_entry == 'course') {
$courses[$id] = $id;
} else {
$coursepath[$id] = $id;
}
}
$coursepath_man = new CoursePath_Manager();
$coursespath_name =& $coursepath_man->getNames($coursepath);
$course_name =& getCoursesInfo($courses);
$cont_h = array($lang->def('_CODE'), $lang->def('_NAME'), $lang->def('_TYPE'));
$type_h = array('', '', '');
if ($mod_perm) {
$type_h[] = 'image';
$cont_h[] = '<img src="' . getPathImage('fw') . 'standard/delete.png" alt="' . $lang->def('_DEL') . '" />';
}
$tb_entry->setColsStyle($type_h);
$tb_entry->addHead($cont_h);
$all_courses = true;
if (Docebo::user()->getUserLevelId() != ADMIN_GROUP_GODADMIN) {
$all_courses = false;
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$admin_courses = $adminManager->getAdminCourse(Docebo::user()->getIdST());
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(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') {
$all_courses = true;
}
} 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 (is_array($course_name)) {
foreach ($course_name as $course) {
$cont = array($course['code'], $course['name'], $lang->def('_COURSE'));
if ($mod_perm) {
if (isset($admin_courses['course'][$course['id']]) || $all_courses) {
$cont[] = '<a href="index.php?modname=catalogue&op=delentry&id_cat=' . $id_cat . '&type=course&id_entry=' . $course['id'] . '" ' . 'title="' . $lang->def('_REMOVE_ENTRY') . ' : ' . strip_tags($course['name']) . '">' . '<img src="' . getPathImage('fw') . 'standard/delete.png" ' . 'alt="' . $lang->def('_DEL') . ' : ' . strip_tags($course['name']) . '" /></a>';
} else {
$cont[] = '';
}
}
$tb_entry->addBody($cont);
}
}
if (is_array($coursespath_name)) {
while (list($id, $coursepath) = each($coursespath_name)) {
$cont = array($coursepath, $lang->def('_COURSEPATH'));
// #bugfix 1141
$tmp_code = $coursepath_man->getCoursepathInfo($id);
$path_code = $tmp_code['path_code'];
array_unshift($cont, $path_code);
// end bugfix
if ($mod_perm) {
if (isset($admin_courses['coursepath'][$id]) || $all_courses) {
$cont[] = '<a href="index.php?modname=catalogue&op=delentry&id_cat=' . $id_cat . '&type=coursepath&id_entry=' . $id . '" ' . 'title="' . $lang->def('_REMOVE_ENTRY') . ' : ' . strip_tags($coursepath) . '">' . '<img src="' . getPathImage('fw') . 'standard/delete.png" ' . 'alt="' . $lang->def('_DEL') . ' : ' . strip_tags($coursepath) . '" /></a>';
} else {
$cont[] = '';
}
}
$tb_entry->addBody($cont);
}
}
$select_entry = array('course' => $lang->def('_COURSE'), 'coursepath' => $lang->def('_COURSEPATH'));
if ($mod_perm) {
$tb_entry->addActionAdd('<a href="index.php?modname=catalogue&op=import&id_cat=' . $id_cat . '&load=1" class="ico-wt-sprite subs_import" ' . ' title="' . Lang::t('_IMPORT', 'catalogue') . '"><span>' . Lang::t('_IMPORT', 'catalogue') . '</span></a>');
}
switch (Get::req('result', DOTY_ALPHANUM, '')) {
case "ok":
UIFeedback::info(Lang::t('_OPERATION_SUCCESSFUL', 'standard'));
break;
case "err":
UIFeedback::error(Lang::t('_OPERATION_ERROR', 'standard'));
break;
}
$out->add(getTitleArea($title_area, 'catalogue') . '<div class="std_block">' . getBackUi('index.php?modname=catalogue&op=catlist', $lang->def('_BACK')) . $tb_entry->getTable() . getBackUi('index.php?modname=catalogue&op=catlist', $lang->def('_BACK')) . '</div>', 'content');
if ($mod_perm) {
require_once _base_ . '/lib/lib.dialog.php';
setupHrefDialogBox('a[href*=delentry]');
}
}
function _get_coursecategories_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'];
$categories = $ref['columns_filter']['selected_categories'];
$cols =& $ref['columns_filter']['showed_columns'];
if (!$sel_all && count($selection) <= 0) {
cout('<p>' . $this->lang->def('_EMPTY_SELECTION') . '</p>');
return;
}
$acl = new DoceboACLManager();
$acl->include_suspended = true;
$html = '';
//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->getAllUsersFromIdst($admin_tree);
$admin_users = array_unique($admin_users);
}
//course categories names
$res = sql_query("SELECT * FROM " . $lms . "_category ");
$categories_names = array();
$categories_limit = array();
while ($row = mysql_fetch_assoc($res)) {
$categories_names[$row['idCategory']] = $row['path'] != '/root/' ? end(explode("/", $row['path'])) : Lang::t('_CATEGORY', 'admin_course_management', 'lms');
// Lang::t('_ROOT'));
$categories_paths[$row['idCategory']] = $row['path'] != '/root/' ? substr($row['path'], 5, strlen($row['path']) - 5) : Lang::t('_CATEGORY', 'admin_course_management');
// Lang::t('_ROOT'));
$categories_limit[$row['idCategory']] = array($row['iLeft'], $row['iRight']);
}
$user_courses = false;
if (Docebo::user()->getUserLevelId() != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {
// if the usre is a subadmin with only few course assigned
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$admin_courses = $adminManager->getAdminCourse(Docebo::user()->getIdST());
if (isset($admin_courses['course'][0])) {
$user_course = false;
} 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') {
$user_course = false;
}
$user_courses = $admin_courses['course'];
} 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);
$user_courses = $admin_courses['course'];
}
}
//create table
switch ($sel_type) {
case 'users':
//table data
$data = array();
$head1 = array('');
$head2 = array($this->lang->def('_USER'));
$totals = array();
foreach ($categories as $idcat) {
$index = (int) str_replace("d", "", $idcat);
$head1[] = array('colspan' => 2, 'value' => $categories_paths[$index]);
$head2[] = $this->lang->def('_COMPLETED');
$head2[] = $this->lang->def('incomplete');
$is_descendant = strpos($idcat, "d");
if ($is_descendant === false) {
$condition = " AND cat.idCategory=" . $index . " ";
} else {
list($left, $right) = $categories_limit[$index];
//sql_fetch_row( sql_query("SELECT iLeft, iRight FROM ".$lms."_category WHERE idCAtegory=".$index) );
$condition = " AND cat.iLeft >= " . $left . " AND cat.iRight <= " . $right . " ";
}
//resolve user selection
if ($sel_all) {
$selection = $acl->getAllUsersIdst();
} else {
$selection = $acl->getAllUsersFromIdst($selection);
}
//resolve group and orgchart selection
$query = "SELECT cu.idUser, cat.idCategory, c.idCourse, c.code, cu.status " . " FROM " . $lms . "_course as c JOIN " . $lms . "_category as cat JOIN " . $lms . "_courseuser as cu " . " ON (c.idCourse=cu.idCourse AND c.idCategory=cat.idCategory) " . " WHERE " . ($sel_all ? " 1 " : " cu.idUser IN (" . implode(",", $selection) . ") ") . $condition . ($user_courses != false ? " AND c.idCourse IN ( '" . implode("','", $user_courses) . "' ) " : '');
$res = sql_query($query);
$temp = array();
$total_1 = 0;
$total_2 = 0;
while ($row = mysql_fetch_assoc($res)) {
$iduser = $row['idUser'];
if (!isset($temp[$iduser])) {
$temp[$iduser] = array('completed' => 0, 'not_completed' => 0);
}
switch ($row['status']) {
case 0:
case 1:
$temp[$iduser]['not_completed']++;
$total_2++;
break;
case 2:
$temp[$iduser]['completed']++;
$total_1++;
break;
}
}
$totals[] = $total_1;
$totals[] = $total_2;
$data[$index] = $temp;
//unset($temp); //free memory
}
$buffer = new ReportTablePrinter($type, true);
$buffer->openTable('', '');
$buffer->openHeader();
$buffer->addHeader($head1);
$buffer->addHeader($head2);
$buffer->closeHeader();
//retrieve usernames
$usernames = array();
$res = sql_query("SELECT idst, userid FROM " . $fw . "_user WHERE idst IN (" . implode(",", $selection) . ")");
while (list($idst, $userid) = sql_fetch_row($res)) {
$usernames[$idst] = $acl->relativeId($userid);
}
//user cycle
$buffer->openBody();
foreach ($selection as $user) {
$line = array();
$line[] = isset($usernames[$user]) ? $usernames[$user] : '';
foreach ($categories as $idcat) {
if ($idcat != '') {
$index = (int) str_replace("d", "", $idcat);
if (isset($data[$index][$user])) {
$line[] = $data[$index][$user]['completed'];
$line[] = $data[$index][$user]['not_completed'];
} else {
$line[] = '0';
$line[] = '0';
}
}
}
$buffer->addLine($line);
}
$buffer->closeBody();
//set totals
$foot = array('');
foreach ($totals as $total) {
$foot[] = $total;
}
$buffer->setFoot($foot);
//unset($data); //free memory
$buffer->closeTable();
$html .= $buffer->get();
break;
//-----------------------------------------
//-----------------------------------------
case 'groups':
//table data
$data = array();
//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'";
$query = "SELECT * FROM " . $fw . "_group WHERE groupid LIKE '/oc\\_%' OR groupid LIKE '/ocd\\_%' OR hidden = 'false' ";
$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]] : '';
}
}
}
//solve groups user
$solved_groups = array();
$subgroups_list = array();
foreach ($selection as $group) {
$temp = $acl->getGroupGDescendants($group);
$temp[] = $group;
foreach ($temp as $idst_subgroup) {
$solved_groups[$idst_subgroup] = $group;
}
$subgroups_list = array_merge($subgroups_list, $temp);
}
$tot_size = 2;
$totals = array();
$head1 = array(array('colspan' => 2, 'value' => $this->lang->def('_GROUPS')));
$head2 = array($this->lang->def('_NAME'), $this->lang->def('_TOTAL'));
foreach ($categories as $idcat) {
$index = (int) str_replace("d", "", $idcat);
$head1[] = array('colspan' => 2, 'value' => $categories_paths[$index]);
$head2[] = $this->lang->def('_COMPLETED');
$head2[] = $this->lang->def('incomplete');
$is_descendant = strpos($idcat, "d");
$condition = '';
if ($is_descendant === false) {
$condition = " AND cat.idCategory=" . $index . " ";
} else {
list($left, $right) = $categories_limit[$index];
//sql_fetch_row( sql_query("SELECT iLeft, iRight FROM ".$lms."_category WHERE idCAtegory=".$index) );
$condition = " AND cat.iLeft >= " . $left . " AND cat.iRight <= " . $right . " ";
}
$query = "SELECT gm.idst as idGroup, cu.idUser, cat.idCategory, c.idCourse, c.code, cu.status " . " FROM " . $lms . "_course as c JOIN " . $lms . "_category as cat JOIN " . $lms . "_courseuser as cu JOIN " . $fw . "_group_members as gm " . " ON (c.idCourse=cu.idCourse AND c.idCategory=cat.idCategory AND cu.idUser=gm.idstMember) " . " WHERE " . ($sel_all ? " 1 " : " gm.idst IN (" . implode(",", $subgroups_list) . ") ") . $condition . ($user_courses != false ? " AND c.idCourse IN ( '" . implode("','", $user_courses) . "' ) " : '');
$res = sql_query($query);
$temp = array();
$total_1 = 0;
$total_2 = 0;
while ($row = mysql_fetch_assoc($res)) {
$id_group = $solved_groups[$row['idGroup']];
if (!isset($temp[$id_group])) {
$temp[$id_group] = array('completed' => 0, 'not_completed' => 0);
}
switch ($row['status']) {
case 0:
case 1:
$temp[$id_group]['not_completed']++;
$total_2++;
break;
case 2:
$temp[$id_group]['completed']++;
$total_1++;
break;
}
}
$totals[] = $total_1;
$totals[] = $total_2;
$data[$index] = $temp;
//unset($temp); //free memory
}
$buffer = new ReportTablePrinter($type, true);
$buffer->openTable('', '');
$buffer->openHeader();
$buffer->addHeader($head1);
$buffer->addHeader($head2);
$buffer->closeHeader();
$tot_users = 0;
$buffer->openBody();
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;
foreach ($categories as $idcat) {
if ($idcat != '') {
$index = (int) str_replace("d", "", $idcat);
if (isset($data[$index][$group_id])) {
$line[] = $data[$index][$group_id]['completed'];
$line[] = $data[$index][$group_id]['not_completed'];
} else {
$line[] = '0';
$line[] = '0';
}
}
}
$buffer->addLine($line);
}
$buffer->closeBody();
//totals ...
$foot = array('', $tot_users);
foreach ($totals as $total) {
$foot[] = $total;
}
$buffer->setFoot($foot);
$buffer->closeTable();
$html .= $buffer->get();
break;
}
//end switch
$GLOBALS['page']->add($html, 'content');
}
/**
* 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 getClassrooms($start_index = false, $results = false, $sort = false, $dir = false, $categories = false, $filter_text = false, $filter_waiting = false)
{
require_once _lms_ . '/lib/lib.date.php';
$date_man = new DateManager();
$status_list = array(CST_PREPARATION => Lang::t('_CST_PREPARATION', 'course'), CST_AVAILABLE => Lang::t('_CST_AVAILABLE', 'course'), CST_EFFECTIVE => Lang::t('_CST_CONFIRMED', 'course'), CST_CONCLUDED => Lang::t('_CST_CONCLUDED', 'course'), CST_CANCELLED => Lang::t('_CST_CANCELLED', 'course'));
$query = "SELECT c.idCourse, c.code, c.name, c.status, COUNT(cd.id_date) as classroom_number " . " FROM %lms_course as c LEFT JOIN %lms_course_date as cd ON c.idCourse=cd.id_course " . " WHERE c.course_type = 'classroom' ";
//if ($categories) {
if (!is_array($categories)) {
$query .= " AND c.idCategory = " . (int) $categories . " ";
} else {
$query .= " AND c.idCategory IN (" . implode(',', $categories) . ") ";
}
//}
if ($filter_text) {
if (is_string($filter_text)) {
$query .= " AND (c.code LIKE '%" . $filter_text . "%' " . " OR c.name LIKE '%" . $filter_text . "%' " . " OR c.description LIKE '%" . $filter_text . "%') ";
}
}
if ($filter_waiting) {
$query .= " AND c.idCourse IN" . " (" . " SELECT idCOurse" . " FROM %lms_courseuser" . " WHERE waiting = 1" . " )";
}
$userlevelid = Docebo::user()->getUserLevelId();
$is_subadmin = false;
$all_courses = false;
if ($userlevelid != ADMIN_GROUP_GODADMIN) {
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$admin_courses = $adminManager->getAdminCourse(Docebo::user()->getIdST());
$all_courses = false;
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(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') {
$all_courses = true;
}
} 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);
}
$is_subadmin = true;
}
$query .= $is_subadmin && !$all_courses ? !empty($admin_courses['course']) ? ' AND c.idCourse IN (' . implode(',', $admin_courses['course']) . ')' : ' AND c.idCourse = 0' : '';
$query .= " GROUP BY c.idCourse ";
switch ($sort) {
case 'code':
$query .= " ORDER BY c.code " . $dir;
break;
case 'name':
$query .= " ORDER BY c.name " . $dir;
break;
}
$start_index === false ? '' : ($query .= " LIMIT " . $start_index . ", " . $results);
$result = sql_query($query);
$res = array();
while (list($id_course, $code, $name, $status, $classroom_number) = sql_fetch_row($result)) {
//$classroom_number = $date_man->getCourseDateNumber($id_course);
$res[] = array('id_course' => $id_course, 'code' => $code, 'name' => $name, 'status' => $status_list[$status], 'classroom_number' => $classroom_number);
}
return $res;
}
public function getAdminCourseResolved($idst)
{
$admin_list = $this->getAdminCourse($idst);
if (!isset($admin_list['course'][0])) {
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
$user_catalogue = $cat_man->getUserAllCatalogueId($idst);
if (count($user_catalogue) > 0) {
$arr_courses = array();
foreach ($user_catalogue as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat);
$arr_courses = array_merge($arr_courses, $catalogue_course);
}
foreach ($arr_courses as $id_course) {
$admin_list['course'][$id_course] = $id_course;
}
}
}
return $admin_list;
}
public function loadCourse($start_index, $results, $sort, $dir, $filter = false)
{
$userlevelid = Docebo::user()->getUserLevelId();
if ($userlevelid != ADMIN_GROUP_GODADMIN) {
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$acl_man =& Docebo::user()->getAclManager();
$admin_courses = $adminManager->getAdminCourse(Docebo::user()->getIdST());
$admin_tree = $adminManager->getAdminTree(Docebo::user()->getIdST());
$admin_users = $acl_man->getAllUsersFromIdst($admin_tree);
}
$query = "SELECT c.*, COUNT(cu.idUser) as subscriptions, SUM(cu.waiting) as pending" . " FROM %lms_course AS c" . " LEFT JOIN %lms_courseuser AS cu ON c.idCourse = cu.idCourse" . ($userlevelid != ADMIN_GROUP_GODADMIN ? !empty($admin_users) ? " AND cu.idUser IN (" . implode(',', $admin_users) . ")" : " AND cu.idUser IN (0)" : '') . " WHERE c.course_type <> 'assessment'";
if ($filter) {
if (isset($filter['id_category'])) {
if (isset($filter['descendants']) && $filter['descendants']) {
$query .= " AND c.idCategory IN (" . implode(',', $this->getCategoryDescendants($filter['id_category'])) . ")";
} else {
$query .= " AND c.idCategory = " . (int) $filter['id_category'];
}
}
if (isset($filter['text']) && $filter['text'] !== '') {
$query .= " AND( c.name LIKE '%" . $filter['text'] . "%'" . " OR c.code LIKE '%" . $filter['text'] . "%')";
}
if (isset($filter['waiting']) && $filter['waiting']) {
$query_course = "SELECT idCourse" . " FROM %lms_courseuser" . " WHERE waiting = 1";
$result = sql_query($query_course);
$id_course_filter = array(0 => 0);
while (list($id_course_tmp) = sql_fetch_row($result)) {
$id_course_filter[$id_course_tmp] = $id_course_tmp;
}
$query .= " AND c.idCourse IN (" . implode(',', $id_course_filter) . ")";
}
if (isset($filter['classroom']) && $filter['classroom']) {
$query .= " AND course_type = 'classroom'";
}
}
if ($userlevelid != ADMIN_GROUP_GODADMIN) {
$all_courses = false;
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(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') {
$all_courses = true;
}
} 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 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);
}
}
$admin_courses['course'] = array_merge($admin_courses['course'], $array_courses);
}
if (!$all_courses) {
if (empty($admin_courses['course'])) {
$query .= " AND 0 ";
} else {
$query .= " AND c.idCourse IN (" . implode(',', $admin_courses['course']) . ") ";
}
}
}
$query .= " GROUP BY c.idCourse" . " ORDER BY " . $sort . " " . $dir;
if ((int) $results > 0) {
$query .= " LIMIT " . (int) $start_index . ", " . (int) $results;
}
return sql_query($query);
}
public function getCourseList($type = '', $page = 1)
{
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
$user_catalogue = $cat_man->getUserAllCatalogueId(Docebo::user()->getIdSt());
$filter = '';
switch ($type) {
case 'elearning':
$filter = " AND course_type = '" . $type . "'";
break;
case 'classroom':
$filter = " AND course_type = '" . $type . "'";
break;
case 'edition':
$filter = " AND course_edition = 1";
break;
case 'new':
$filter = " AND create_date >= '" . date('Y-m-d', mktime(0, 0, 0, date('m'), (int) date('d') - 7, date('Y'))) . "'";
break;
case 'catalogue':
$id_catalogue = Get::req('id_cata', DOTY_INT, '0');
$catalogue_course =& $cat_man->getCatalogueCourse($id_catalogue);
$filter = " AND idCourse IN (" . implode(',', $catalogue_course) . ")";
break;
default:
break;
}
$filter .= " AND show_rules = 0";
$login_link = '<a href="index.php">' . Lang::t('_LOG_IN', 'login') . '</a>';
$signin_link = '<a href="index.php?modname=login&op=register">' . Lang::t('_SIGN_IN', 'login') . '</a>';
require_once _lib_ . '/lib.usermanager.php';
$option = new UserManagerOption();
$register_type = $option->getOption('register_type');
$limit = ($page - 1) * Get::sett('visuItem');
$id_cat = Get::req('id_cat', DOTY_INT, 0);
$query = "SELECT *" . " FROM %lms_course" . " WHERE status NOT IN (" . CST_PREPARATION . ", " . CST_CONCLUDED . ", " . CST_CANCELLED . ")" . " AND course_type <> 'assessment'" . " AND (" . " date_end = '0000-00-00'" . " OR date_end > '" . date('Y-m-d') . "'" . " )" . $filter . ($id_cat > 0 ? " AND idCategory = " . (int) $id_cat : '') . " ORDER BY name" . " LIMIT " . $limit . ", " . Get::sett('visuItem');
$result = sql_query($query);
$html = '';
$path_course = $GLOBALS['where_files_relative'] . '/appLms/' . Get::sett('pathcourse') . '/';
while ($row = sql_fetch_assoc($result)) {
$action = '';
if ($row['course_type'] === 'classroom') {
$additional_info = '';
$classrooms = $this->classroom_man->getCourseDate($row['idCourse'], false);
if (count($classrooms) > 0) {
$action = '<div class="catalog_action" style="top:5px;" id="action_' . $row['idCourse'] . '">' . '<a href="javascript:;" onclick="courseSelection(\'' . $row['idCourse'] . '\', \'0\')" title="' . Lang::t('_SHOW_EDITIONS', 'catalogue') . '"><p class="can_subscribe">' . Lang::t('_SHOW_EDITIONS', 'catalogue') . '</p></a>' . '</div>';
}
} elseif ($row['course_edition'] == 1) {
$additional_info = '';
$editions = $this->edition_man->getEditionAvailableForCourse(Docebo::user()->getIdSt(), $row['idCourse']);
if (count($editions) > 0) {
$action = '<div class="catalog_action" style="top:5px;" id="action_' . $row['idCourse'] . '">' . '<a href="javascript:;" onclick="courseSelection(\'' . $row['idCourse'] . '\', \'0\')" title="' . Lang::t('_SHOW_EDITIONS', 'catalogue') . '"><p class="can_subscribe">' . Lang::t('_SHOW_EDITIONS', 'catalogue') . '</p></a>' . '</div>';
}
} else {
// standard elearning course without editions
$query = "SELECT COUNT(*)" . " FROM %lms_courseuser" . " WHERE idCourse = '" . $row['idCourse'] . "'";
list($enrolled) = sql_fetch_row(sql_query($query));
$row['enrolled'] = $enrolled;
$row['create_date'] = Format::date($row['create_date'], 'date');
$additional_info = '<p class="course_support_info">' . Lang::t('_COURSE_INTRO', 'course', array('[course_type]' => $row['course_type'], '[create_date]' => $row['create_date'], '[enrolled]' => $row['enrolled'], '[course_status]' => Lang::t($this->cstatus[$row['status']], 'course'))) . '</p>';
$action = '<div class="catalog_action" style="top:5px;" id="action_' . $row['idCourse'] . '">' . '<p class="can_subscribe">' . str_replace(array('[login]', '[signin]'), array($login_link, $signin_link), $register_type === 'self' || $register_type === 'self_optin' || $register_type === 'moderate' ? Lang::t('_REGISTER_FOR_COURSE', 'login') : Lang::t('_REGISTER_FOR_COURSE_NO_REG', 'login')) . '</p>' . '</div>';
}
$html .= '<div class="dash-course">' . ($row['use_logo_in_courselist'] && $row['img_course'] ? '<div class="logo_container"><img class="clogo" src="' . $path_course . $row['img_course'] . '" alt="' . Util::purge($row['name']) . '" /></div>' : '') . ($row['use_logo_in_courselist'] && !$row['img_course'] ? '<div class="logo_container"><img class="clogo cnologo" src="' . Get::tmpl_path() . 'images/course/course_nologo.png' . '" alt="' . Util::purge($row['name']) . '" /></div>' : '') . '<div class="info_container">' . '<h2>' . ($row['lang_code'] != 'none' ? Get::img('language/' . strtolower($row['lang_code']) . '.png', $row['lang_code']) : '') . $row['name'] . '</h2>' . $additional_info . '<p class="course_support_info">' . $row['description'] . '</p>' . '<p class="course_support_info">' . ($row['course_demo'] ? '<a href="index.php?r=homecatalogue/downloadDemoMaterial&course_id=' . $row['idCourse'] . '" class="ico-wt-sprite subs_download"><span>' . Lang::t('_COURSE_DEMO', 'course') . '</span></a>' : '') . '</p>' . '<p class="course_support_info">' . ($row['code'] ? '<i style="font-size:.88em">[' . $row['code'] . ']</i>' : '') . '</p>' . $action . '<div class="nofloat"></div>' . '</div>' . '</div>';
}
if (sql_num_rows($result) <= 0) {
$html = '<p>' . Lang::t('_NO_CONTENT', 'standard') . '</p>';
}
return $html;
}
public function getTotalCourseNumber($type = '')
{
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
$user_catalogue = $cat_man->getUserAllCatalogueId(Docebo::user()->getIdSt());
switch ($type) {
case 'elearning':
$filter = " AND course_type = '" . $type . "'";
if (count($user_catalogue) > 0) {
$courses = array();
foreach ($user_catalogue as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat);
$courses = array_merge($courses, $catalogue_course);
}
$filter .= " AND idCourse IN (" . implode(',', $courses) . ")";
}
break;
case 'classroom':
$filter = " AND course_type = '" . $type . "'";
if (count($user_catalogue) > 0) {
$courses = array();
foreach ($user_catalogue as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat);
$courses = array_merge($courses, $catalogue_course);
}
$filter .= " AND idCourse IN (" . implode(',', $courses) . ")";
}
break;
case 'edition':
$filter = " AND course_edition = 1";
if (count($user_catalogue) > 0) {
$courses = array();
foreach ($user_catalogue as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat);
$courses = array_merge($courses, $catalogue_course);
}
$filter .= " AND idCourse IN (" . implode(',', $courses) . ")";
}
break;
case 'new':
$filter = " AND create_date >= '" . date('Y-m-d', mktime(0, 0, 0, date('m'), (int) date('d') - 7, date('Y'))) . "'";
if (count($user_catalogue) > 0) {
$courses = array();
foreach ($user_catalogue as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat);
$courses = array_merge($courses, $catalogue_course);
}
$filter .= " AND idCourse IN (" . implode(',', $courses) . ")";
}
break;
case 'catalogue':
$id_catalogue = Get::req('id_cata', DOTY_INT, '0');
$catalogue_course =& $cat_man->getCatalogueCourse($id_catalogue);
$filter = " AND idCourse IN (" . implode(',', $catalogue_course) . ")";
break;
default:
$filter = '';
if (count($user_catalogue) > 0) {
$courses = array();
foreach ($user_catalogue as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat);
$courses = array_merge($courses, $catalogue_course);
}
$filter .= " AND idCourse IN (" . implode(',', $courses) . ")";
}
break;
}
if (count($user_catalogue) == 0 && Get::sett('on_catalogue_empty', 'off') == 'off') {
$filter = " AND 0 ";
//query won't return any results with this setting
}
$id_cat = Get::req('id_cat', DOTY_INT, 0);
$query = "SELECT COUNT(*)" . " FROM %lms_course" . " WHERE status NOT IN (" . CST_PREPARATION . ", " . CST_CONCLUDED . ", " . CST_CANCELLED . ")" . " AND course_type <> 'assessment'" . " AND (" . " date_begin = '0000-00-00'" . " OR date_begin > '" . date('Y-m-d') . "'" . " )" . $filter . ($id_cat > 0 ? " AND idCategory = " . (int) $id_cat : '') . " ORDER BY name";
list($res) = sql_fetch_row(sql_query($query));
return $res;
}
/**
* Calculate all pending request of unsubscription to be moderated
* @return boolean
*/
public function countPendingUnsubscribeRequests()
{
$output = 0;
$ulevel = Docebo::user()->getUserLevelId();
$id_admin = Docebo::user()->getIdSt();
$filter = FALSE;
$admin_query_course = "";
$admin_query_editions = "";
if ($ulevel != ADMIN_GROUP_GODADMIN) {
require_once _base_ . '/lib/lib.preference.php';
$preference = new AdminPreference();
$view = $preference->getAdminCourse($id_admin);
$all_courses = false;
if (isset($view['course'][0])) {
$all_courses = true;
} elseif (isset($view['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) {
$view['course'][$id_course] = $id_course;
}
}
} elseif (Get::sett('on_catalogue_empty', 'off') == 'on') {
$all_courses = true;
}
} else {
$array_courses = array();
$array_courses = array_merge($array_courses, $view['course']);
if (!empty($view['coursepath'])) {
require_once _lms_ . '/lib/lib.coursepath.php';
$path_man = new CoursePath_Manager();
$coursepath_course =& $path_man->getAllCourses($view['coursepath']);
$array_courses = array_merge($array_courses, $coursepath_course);
}
if (!empty($view['catalogue'])) {
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
foreach ($view['catalogue'] as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);
$array_courses = array_merge($array_courses, $catalogue_course);
}
}
$view['course'] = array_merge($view['course'], $array_courses);
}
$filter = $view['course'];
$admin_query_course = $preference->getAdminUsersQuery($id_admin, 'idUser');
$admin_query_editions = $preference->getAdminUsersQuery($id_admin, 't1.id_user');
}
// -- Count for normal courses:
$query = "SELECT COUNT(*) FROM %lms_courseuser WHERE requesting_unsubscribe = 1";
$query .= " AND course_edition=0 AND course_type='elearning'";
if ($filter !== FALSE) {
if (empty($filter)) {
return 0;
}
//no courses to check --> no requests for sure
if (!$all_courses) {
//we haven't assigned "all courses" to the admin
$query .= " AND idCourse IN (" . implode(",", $filter) . ")";
}
}
if ($admin_query_course) {
$query .= " AND " . $admin_query_course;
}
$res = $this->db->query($query);
if ($res) {
list($tot) = $this->db->fetch_row($res);
$output = (int) $tot;
}
// -- Count for editions:
$query = "SELECT COUNT(*) FROM %lms_course_editions_user as t1,\r\n\t\t\t%lms_course_edition as t2 WHERE t1.requesting_unsubscribe = 1 AND\r\n\t\t\tt1.id_edition=t2.idCourseEdition ";
if ($filter !== FALSE) {
if (empty($filter)) {
return 0;
}
//no courses to check --> no requests for sure
if (!isset($filter[0]) || $filter[0] != 0) {
//we haven't assigned "all courses" to the admin
$query .= " AND t2.idCourse IN (" . implode(",", $filter) . ")";
}
}
if ($admin_query_editions) {
$query .= " AND " . $admin_query_editions;
}
$res = $this->db->query($query);
if ($res) {
list($tot) = $this->db->fetch_row($res);
$output = (int) $tot;
}
// -- Count for classrooms:
$query = "SELECT COUNT(*) FROM %lms_course_date_user as t1,\r\n\t\t\t%lms_course_date as t2 WHERE t1.requesting_unsubscribe = 1 AND\r\n\t\t\tt1.id_date=t2.id_date ";
if ($filter !== FALSE) {
if (empty($filter)) {
return 0;
}
//no courses to check --> no requests for sure
if (!isset($filter[0]) || $filter[0] != 0) {
//we haven't assigned "all courses" to the admin
$query .= " AND t2.id_course IN (" . implode(",", $filter) . ")";
}
}
if ($admin_query_editions) {
$query .= " AND " . $admin_query_editions;
}
$res = $this->db->query($query);
if ($res) {
list($tot) = $this->db->fetch_row($res);
$output = (int) $tot;
}
return $output > 0 ? $output : false;
}
protected function _getUnsubscribeSelectAll()
{
$filter_text = Get::req('filter_text', DOTY_STRING, "");
$filter_course = Get::req('filter_course', DOTY_INT, 0);
$courses_filter = FALSE;
$ulevel = Docebo::user()->user_level;
if ($ulevel != ADMIN_GROUP_GODADMIN) {
require_once _base_ . '/lib/lib.preference.php';
$preference = new AdminPreference();
$view = $preference->getAdminCourse(Docebo::user()->idst);
$all_courses = false;
if (isset($view['course'][0])) {
$all_courses = true;
} elseif (isset($view['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);
$courses = array_merge($courses, $catalogue_course);
}
foreach ($courses as $id_course) {
if ($id_course != 0) {
$view['course'][$id_course] = $id_course;
}
}
} elseif (Get::sett('on_catalogue_empty', 'off') == 'on') {
$all_courses = true;
}
} else {
$array_courses = array();
$array_courses = array_merge($array_courses, $view['course']);
if (!empty($view['coursepath'])) {
require_once _lms_ . '/lib/lib.coursepath.php';
$path_man = new Catalogue_Manager();
$coursepath_course =& $path_man->getAllCourses($view['coursepath']);
$array_courses = array_merge($array_courses, $coursepath_course);
}
if (!empty($view['catalogue'])) {
require_once _lms_ . '/lib/lib.catalogue.php';
$cat_man = new Catalogue_Manager();
foreach ($view['catalogue'] as $id_cat) {
$catalogue_course =& $cat_man->getCatalogueCourse($id_cat, true);
$array_courses = array_merge($array_courses, $catalogue_course);
}
}
$view['course'] = array_merge($view['course'], $array_courses);
}
if (!$all_courses) {
$courses_filter = $view['course'];
}
}
if ($filter_course > 0) {
if ($courses_filter === FALSE) {
$courses_filter = (int) $filter_course;
} else {
if (!in_array($filter_course, $courses_filter)) {
$courses_filter = array();
} else {
$courses_filter = (int) $filter_course;
}
}
}
$filter = array();
if ($filter_text != "") {
$filter['text'] = $filter_text;
}
if (is_array($courses_filter)) {
$filter['course'] = $courses_filter;
}
$output = $this->model->getUnsubscribeRequestsAll($filter);
echo $this->json->encode($output);
}
function pathelem()
{
checkPerm('view');
require_once _base_ . '/lib/lib.table.php';
require_once $GLOBALS['where_lms'] . '/lib/lib.course.php';
require_once $GLOBALS['where_lms'] . '/lib/lib.coursepath.php';
$lang =& DoceboLanguage::createInstance('coursepath', 'lms');
$id_path = importVar('id_path', true, 0);
$mod_perm = checkPerm('mod', true);
$path_man = new CoursePath_Manager();
$course_man = new Man_Course();
$path = $path_man->getCoursepathInfo($id_path);
// retriving id of the courses in this path
$slots = $path_man->getPathSlot($id_path);
$courses = $path_man->getPathElem($id_path);
// retrive all i need about courses name
if (isset($courses['course_list'])) {
$course_info = $course_man->getAllCourses(false, 'all', $courses['course_list']);
} else {
$course_info = array();
}
$area_title = array('index.php?modname=coursepath&op=pathlist' => $lang->def('_COURSEPATH'), $path['path_name']);
$GLOBALS['page']->add(getTitleArea($area_title, 'coursepath') . '<div class="std_block">' . getBackUi('index.php?modname=coursepath&op=pathlist', $lang->def('_BACK')), 'content');
if (isset($_GET['result'])) {
switch ($_GET['result']) {
case "ok":
$GLOBALS['page']->add(getResultUi($lang->def('_OPERATION_SUCCESSFUL')), 'content');
break;
case "err":
$GLOBALS['page']->add(getErrorUi($lang->def('_OPERATION_FAILURE')), 'content');
break;
}
}
$tb_path = new Table(0, $lang->def('_COURSE_PATH_COURSES_CAPTION'), $lang->def('_COURSE_PATH_COURSES_CAPTION'));
$cont_h = array($lang->def('_CODE'), $lang->def('_COURSE_NAME'), $lang->def('_PREREQUISITES'));
$type_h = array('coursepath_code', 'coursepath_name', '', 'image');
if ($mod_perm) {
$cont_h[] = Get::img('standard/down.png', Lang::t('_DOWN', 'coursepath'));
$type_h[] = 'image';
$cont_h[] = Get::img('standard/up.png', Lang::t('_UP', 'coursepath'));
$type_h[] = 'image';
$cont_h[] = Get::img('standard/moderate.png', Lang::t('_MOD', 'coursepath'));
$type_h[] = 'image';
$cont_h[] = '<img src="' . getPathImage() . 'standard/delete.png" alt="' . $lang->def('_DEL') . '" />';
$type_h[] = 'image';
}
$tb_path->setColsStyle($type_h);
$tb_path->addHead($cont_h);
$slot_number = 0;
foreach ($slots as $id_slot => $slot_info) {
$tb_path->emptyBody();
$tb_path->emptyFoot();
$i = 0;
if (!isset($courses[$id_slot])) {
$num_course = 0;
} else {
$num_course = count($courses[$id_slot]);
$all_courses = true;
if (Docebo::user()->getUserLevelId() != ADMIN_GROUP_GODADMIN) {
$all_courses = false;
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$admin_courses = $adminManager->getAdminCourse(Docebo::user()->getIdST());
$all_course = false;
if (isset($admin_courses['course'][0])) {
$all_course = 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') {
$all_courses = true;
}
} 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 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);
}
}
$admin_courses['course'] = array_merge($admin_courses['course'], $array_courses);
}
}
while (list($id_item, $prerequisites) = each($courses[$id_slot])) {
$cont = array($course_info[$id_item]['code'], $course_info[$id_item]['name']);
if ($prerequisites != '') {
$cont[] = coursePathSubstPrer($prerequisites, $course_info);
} else {
$cont[] = '';
}
if ($mod_perm) {
if (in_array($id_item, $admin_courses['course']) || $all_courses) {
if ($i != $num_course - 1) {
$cont[] = '<a href="index.php?modname=coursepath&op=downelem&id_path=' . $id_path . '&id_course=' . $id_item . '&id_slot=' . $id_slot . '" ' . 'title="' . $lang->def('_MOVE_DOWN') . ' : ' . $course_info[$id_item]['name'] . '">' . Get::img('standard/down.png', Lang::t('_DOWN', 'coursepath')) . '</a>';
} else {
$cont[] = '';
}
if ($i != 0) {
$cont[] = '<a href="index.php?modname=coursepath&op=upelem&id_path=' . $id_path . '&id_course=' . $id_item . '&id_slot=' . $id_slot . '" ' . 'title="' . $lang->def('_MOVE_UP') . ' : ' . $course_info[$id_item]['name'] . '">' . Get::img('standard/up.png', Lang::t('_UP', 'coursepath')) . '</a>';
} else {
$cont[] = '';
}
$cont[] = '<a href="index.php?modname=coursepath&op=modprerequisites&id_path=' . $id_path . '&id_course=' . $id_item . '&id_slot=' . $id_slot . '" ' . 'title="' . $lang->def('_MOD') . ' : ' . $course_info[$id_item]['name'] . '">' . Get::img('standard/moderate.png', Lang::t('_MOD', 'coursepath') . ' : ' . $course_info[$id_item]['name']) . '</a>';
$cont[] = '<a href="index.php?modname=coursepath&op=delcoursepath&id_path=' . $id_path . '&id_course=' . $id_item . '&id_slot=' . $id_slot . '" ' . 'title="' . $lang->def('_DEL') . ' : ' . $course_info[$id_item]['name'] . '">' . '<img src="' . getPathImage() . 'standard/delete.png" alt="' . $lang->def('_DEL') . ' : ' . $course_info[$id_item]['name'] . '" /></a>';
} else {
$cont[] = '';
$cont[] = '';
$cont[] = '';
$cont[] = '';
}
}
$tb_path->addBody($cont);
$i++;
}
}
// add link
if ($mod_perm) {
$tb_path->addActionAdd('<a class="ico-wt-sprite subs_import" href="index.php?modname=coursepath&op=importcourse&load=1&id_path=' . $id_path . '&id_slot=' . $id_slot . '" ' . 'title="' . $lang->def('_IMPORT_COURSE') . '">' . '<span>' . $lang->def('_IMPORT_COURSE') . '</span>' . '</a>');
}
$GLOBALS['page']->add($tb_path->getTable() . '<br />', 'content');
$slot_number++;
}
/*
$GLOBALS['page']->add(
'<a href="index.php?modname=coursepath&op=modslot&id_path='.$id_path.'" '
.'title="'.$lang->def('_NEW_SLOT_TITLE').'">'
.'<img src="'.getPathimage().'standard/add.png" alt="'.$lang->def('_ADD').'" />'
.$lang->def('_NEW_SLOT')
.'</a>'
,'content');
*/
$GLOBALS['page']->add(getBackUi('index.php?modname=coursepath&op=pathlist', $lang->def('_BACK')) . '</div>', 'content');
}
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;
}