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]'); } }
/** * 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; }
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'); }
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; }
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 _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; }