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