public function getCoursePathSubscriptionsList($id_path, $filter = false) { require_once _lms_ . '/lib/lib.coursepath.php'; $cman = new CoursePath_Manager(); $courses = $cman->getAllCourses(array($id_path)); if (empty($courses)) { //... } $query = "SELECT DISTINCT s.idUser " . " FROM (%lms_courseuser as s JOIN %lms_coursepath_user as p " . " ON (s.idUser = p.idUser)) " . " JOIN %adm_user as u " . " ON (s.idUser = u.idst) " . " WHERE p.id_path = " . (int) $id_path . " AND s.idCourse IN (" . implode(",", array_values($courses)) . ") "; //." GROUP BY s.idUser"; if (is_array($filter)) { if (isset($filter['text']) && $filter['text'] != "") { $query .= " AND (u.userid LIKE '%" . $filter['text'] . "%' OR u.firstname LIKE '%" . $filter['text'] . "%' OR u.lastname LIKE '%" . $filter['text'] . "%') "; } $arr_idst = array(); if (isset($filter['orgchart']) && $filter['orgchart'] > 0) { $umodel = new UsermanagementAdm(); $use_desc = isset($filter['descendants']) && $filter['descendants']; $ulist = $umodel->getFolderUsers($filter['orgchart'], $use_desc); if (!empty($ulist)) { $arr_idst = $ulist; } unset($ulist); } if (!empty($arr_idst)) { $conditions[] = " AND u.idst IN (" . implode(",", $arr_idst) . ") "; } if (isset($filter['date_valid']) && strlen($filter['date_valid']) >= 10) { $query .= " AND (s.date_begin_validity <= '" . $filter['date_valid'] . "' OR s.date_begin_validity IS NULL OR s.date_begin_validity='0000-00-00 00:00:00') "; $query .= " AND (s.date_expire_validity >= '" . $filter['date_valid'] . "' OR s.date_expire_validity IS NULL OR s.date_expire_validity='0000-00-00 00:00:00') "; } if (isset($filter['show'])) { //validate values switch ($filter['show']) { case 0: //all //no condition to check ... break; case 1: //expired $query .= " AND (s.date_expire_validity IS NOT NULL AND s.date_expire_validity < NOW())"; break; case 2: //not expired with expiring date $query .= " AND (s.date_expire_validity IS NOT NULL AND s.date_expire_validity > NOW())"; break; case 3: //not expired without expiring date $query .= " AND (s.date_expire IS NULL OR s.date_expire='' OR s.date_expire='0000-00-00 00:00:00') "; break; default: //all ... break; } } } if (Docebo::user()->getUserLevelId() != ADMIN_GROUP_GODADMIN) { require_once _base_ . '/lib/lib.preference.php'; $acl_man = new DoceboACLManager(); $adminManager = new AdminPreference(); $admin_tree = $adminManager->getAdminTree(getLogUserId()); $admin_users = $acl_man->getAllUsersFromSelection($admin_tree); $query .= " AND s.idUser IN (" . implode(',', $admin_users) . ")"; } $output = array(); $res = sql_query($query); if ($res) { while (list($idst) = sql_fetch_row($res)) { $output[] = $idst; } } return $output; }
function _get_LO_query($type = 'html', $report_data = NULL, $other = '') { require_once "report_tableprinter.php"; function is_showed($which, $data) { if (isset($data['columns_filter'])) { return in_array($which, $data['columns_filter']['showed_columns']); } else { return false; } } if ($report_data == NULL) { $ref =& $_SESSION['report_tempdata']; } else { $ref =& $report_data; } $_rows =& $ref['rows_filter']; $_cols =& $ref['columns_filter']; $acl_man = new DoceboACLManager(); $acl_man->include_suspended = TRUE; $all_users =& $_rows['all_users']; //select root & descendants from orgchart instead $all_courses =& $_cols['all_courses']; $courses =& $_cols['selected_courses']; $types =& $_cols['lo_types']; $milestones =& $_cols['lo_milestones']; $showed =& $_cols['showed_columns']; $customcols =& $_cols['custom_fields']; $order_by = isset($_cols['order_by']) ? $_cols['order_by'] : 'userid'; $order_dir = isset($_cols['order_dir']) ? $_cols['order_dir'] : 'asc'; $suspended = isset($_cols['show_suspended']) ? (bool) $_cols['show_suspended'] : false; if ($all_users) { $users =& $acl_man->getAllUsersIdst(); } else { $users =& $acl_man->getAllUsersFromSelection($_rows['users']); } $temptypes = array(); foreach ($types as $val) { $temptypes[] = "'" . $val . "'"; } $tempmilestones = array(); foreach ($milestones as $val) { switch ($val) { case _MILESTONE_NONE: $tempmilestones[] = "''"; $tempmilestones[] = "'-'"; break; case _MILESTONE_START: $tempmilestones[] = "'start'"; break; case _MILESTONE_END: $tempmilestones[] = "'end'"; break; } } $colspans = array('user' => 0, 'course' => 0, 'lo' => 0); foreach ($this->LO_columns as $val) { if ($val['select']) { if (in_array($val['key'], $showed)) { switch ($val['key']) { case 'user_name': $colspans[$val['group']] += 2; break; default: $colspans[$val['group']]++; break; } } } else { if ($val['key'] == '_CUSTOM_FIELDS_') { //do nothing ... } else { $colspans[$val['group']]++; } } } //custom user fields require_once _adm_ . '/lib/lib.field.php'; $fman = new FieldList(); $field_values = array(); $temp_head2 = array(); foreach ($customcols as $val) { if ($val['selected']) { $colspans['user']++; $temp_head2[] = $val['label']; $field_values[$val['id']] = $fman->fieldValue((int) $val['id'], $users); } } $lang = $this->lang; $head1 = array(); $head1[] = array('colspan' => $colspans['user'], 'value' => $lang->def('_USER')); //_TH_USER $head1[] = array('colspan' => $colspans['course'], 'value' => $lang->def('_COURSE')); //_TH_COURSE $head1[] = array('colspan' => $colspans['lo'], 'value' => $lang->def('_LEARNING_OBJECTS')); if ($this->use_mail) { $head1[] = array('style' => 'img-cell', 'value' => $this->_loadEmailIcon()); } $head2 = array(); foreach ($this->LO_columns as $val) { if ($val['select']) { if (in_array($val['key'], $showed)) { switch ($val['key']) { //manages exceptions through switch case 'user_name': $head2[] = Lang::t('_LASTNAME', 'standard'); $head2[] = Lang::t('_FIRSTNAME', 'standard'); break; default: $head2[] = $val['label']; break; } } } else { if ($val['key'] == '_CUSTOM_FIELDS_') { foreach ($temp_head2 as $tval) { $head2[] = $tval; } } else { $head2[] = $val['label']; //label } } } if ($this->use_mail) { $head2[] = ''; } //'<img src="'.getPathImage().'standard/email.gif"/>';//''; //header for checkbox $buffer = new ReportTablePrinter($type); $buffer->openTable('', ''); $buffer->openHeader(); $buffer->addHeader($head1); $buffer->addHeader($head2); $buffer->closeHeader(); //retrieve LOs from courses $score_arr = array('test' => array(), 'scorm' => array()); //retrieve test score $query = "SELECT t1.idOrg, t2.idUser, t1.idCourse, t2.score, t2.bonus_score, t2.score_status " . " FROM %lms_organization AS t1 " . " JOIN %lms_testtrack AS t2 ON ( t1.objectType = 'test' " . " AND t1.idOrg = t2.idReference ), %adm_user as t3 " . "WHERE t3.idst=t2.idUser " . ($suspended ? "" : "AND t3.valid=1 ") . (!$all_courses ? " AND t1.idCourse IN (" . implode(',', $courses) . ") " : "") . (!$all_users ? " AND t2.idUser IN (" . implode(',', $users) . ") " : "") . (count($tempmilestones) > 0 ? " AND t1.milestone IN (" . implode(',', $tempmilestones) . ") " : ""); $res = sql_query($query); while ($row = mysql_fetch_assoc($res)) { $score_arr['test'][$row['idOrg']][$row['idUser']] = $row['score'] + $row['bonus_score']; } //retrievescorm score $query = "SELECT t1.idOrg, t2.idUser, t1.idCourse, t2.score_raw, t2.score_min, t2.score_max " . " FROM %lms_organization AS t1 " . " JOIN %lms_scorm_tracking AS t2 ON ( t1.objectType = 'scormorg' " . " AND t1.idOrg = t2.idReference ), %adm_user as t3 " . "WHERE t3.idst=t2.idUser " . ($suspended ? "" : "AND t3.valid=1 ") . (!$all_courses ? " AND t1.idCourse IN (" . implode(',', $courses) . ") " : "") . (!$all_users ? " AND t2.idUser IN (" . implode(',', $users) . ") " : "") . (count($tempmilestones) > 0 ? " AND t1.milestone IN (" . implode(',', $tempmilestones) . ") " : ""); $res = sql_query($query); while ($row = mysql_fetch_assoc($res)) { $score_arr['scorm'][$row['idOrg']][$row['idUser']] = $row['score_raw']; } $buffer->openBody(); //retrieve LO types translations $LO_types = $this->getLOTypesTranslations(); //retrieve LO's data $_dir = "ASC"; switch ($order_dir) { case "desc": $_dir = "DESC"; break; } $query_order_by = "t0.userid " . $_dir . ", t1.title " . $_dir; switch ($order_by) { case 'firstname': $query_order_by = "t0.firstname " . $_dir . ", t0.lastname, " . $_dir . ", t0.userid " . $_dir . ", t1.title " . $_dir; break; case 'lastname': $query_order_by = "t0.lastname " . $_dir . ", t0.firstname, " . $_dir . ", t0.userid " . $_dir . ", t1.title " . $_dir; break; case 'email': $query_order_by = "t0.email " . $_dir . ", t0.userid " . $_dir . ", t1.title " . $_dir; break; case 'course_code': $query_order_by = "t3.code " . $_dir . ", t3.name " . $_dir . ", t0.userid " . $_dir . ", t1.title " . $_dir; break; case 'course_name': $query_order_by = "t3.name " . $_dir . ", t3.code " . $_dir . ", t0.userid " . $_dir . ", t1.title " . $_dir; break; case 'object_title': $query_order_by = "t1.title " . $_dir . ", t0.userid " . $_dir; break; case 'object_type': $query_order_by = "t1.objectType " . $_dir . ", t1.title " . $_dir . ", t0.userid " . $_dir; break; case 'first_attempt': $query_order_by = "t2.firstAttempt"; break; case 'last_attempt': $query_order_by = "t2.dateAttempt"; break; } $query = "SELECT t0.idst as user_st, t0.userid, t0.firstname, t0.lastname, t0.email, t0.valid, " . " t1.idOrg, t1.objectType, t1.title, t1.idResource, t1.milestone, " . " t3.idCourse, t3.code, t3.name, t3.status as course_status, " . " t2.firstAttempt, t2.dateAttempt, t2.status " . " FROM %adm_user as t0, " . " %lms_organization as t1, " . " %lms_commontrack as t2, " . " %lms_course as t3 " . " WHERE " . " t0.idst=t2.idUser AND t1.idOrg=t2.idReference AND t1.idCourse=t3.idCourse " . ($suspended ? "" : "AND t0.valid=1 ") . (!$all_courses ? " AND t1.idCourse IN (" . implode(',', $courses) . ") " : "") . (count($temptypes) > 0 ? " AND t2.objectType IN (" . implode(',', $temptypes) . ") " : "") . (!$all_users ? " AND t2.idUser IN (" . implode(',', $users) . ") " : "") . (count($tempmilestones) > 0 ? "AND t1.milestone IN (" . implode(',', $tempmilestones) . ")" : "") . " ORDER BY " . $query_order_by; $res = sql_query($query); while ($row = mysql_fetch_assoc($res)) { $temp = array(); foreach ($this->LO_columns as $val) { switch ($val['key']) { case 'userid': $temp[] = $acl_man->relativeId($row['userid']); break; case 'user_name': if (in_array($val['key'], $showed)) { $temp[] = $row['lastname']; $temp[] = $row['firstname']; } break; case 'email': if (in_array($val['key'], $showed)) { $temp[] = $row['email']; } break; case 'suspended': if (in_array($val['key'], $showed)) { $temp[] = $row['valid'] > 0 ? Lang::t('_NO', 'standard') : Lang::t('_YES', 'standard'); } break; case '_CUSTOM_FIELDS_': foreach ($customcols as $field) { if ($field['selected']) { if (isset($field_values[$field['id']][$row['user_st']])) { $temp[] = $field_values[$field['id']][$row['user_st']]; } else { $temp[] = ''; } } } break; case 'course_code': $temp[] = $row['code']; break; case 'course_name': if (in_array($val['key'], $showed)) { $temp[] = $row['name']; } break; case 'course_status': if (in_array($val['key'], $showed)) { $temp[] = $this->_convertStatusCourse($row['course_status']); } break; case 'lo_type': if (in_array($val['key'], $showed)) { $temp[] = isset($LO_types[$row['objectType']]) ? $LO_types[$row['objectType']] : ""; } break; case 'lo_name': if (in_array($val['key'], $showed)) { $temp[] = $row['title']; } break; case 'lo_milestone': if (in_array($val['key'], $showed)) { $temp[] = $row['milestone']; } break; case 'firstAttempt': if (in_array($val['key'], $showed)) { $temp[] = $this->_convertDate($row['firstAttempt']); } break; case 'lastAttempt': if (in_array($val['key'], $showed)) { $temp[] = $this->_convertDate($row['dateAttempt']); } break; case 'lo_status': if (in_array($val['key'], $showed)) { $temp[] = Lang::t($row['status'], 'storage'); } break; case 'lo_score': if (in_array($val['key'], $showed)) { switch ($row['objectType']) { case 'test': if (isset($score_arr['test'][$row['idOrg']][$row['user_st']])) { $score_val = $score_arr['test'][$row['idOrg']][$row['user_st']]; } else { $score_val = '0'; } $temp[] = $score_val; break; case 'scormorg': if (isset($score_arr['scorm'][$row['idOrg']][$row['user_st']])) { $score_val = $score_arr['scorm'][$row['idOrg']][$row['user_st']]; } else { $score_val = '0'; } $temp[] = $score_val; break; default: $temp[] = ''; break; } } break; default: if (in_array($val['key'], $showed)) { $temp[] = ''; } break; } } //end switch - end for if ($this->use_mail) { $temp[] = '<div class="align_center">' . Form::getInputCheckbox('mail_' . $row['user_st'], 'mail_recipients[]', $row['user_st'], isset($_POST['select_all']), '') . '</div>'; } $buffer->addLine($temp); } $buffer->closeBody(); $buffer->closeTable(); $output = $buffer->get(); if ($this->use_mail) { $output .= $this->_loadEmailActions(); } 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; }