public function getTotalStudentsSubscribed($id_course, $id_edition, $filter = "") { $subscribed = $this->getEditionSubscribed($id_edition); if (count($subscribed) == 0) { return 0; } $query = "SELECT COUNT(*)" . " FROM " . $this->courseuser_table . " AS s" . " JOIN " . $this->user_table . " AS u ON s.idUser = u.idst" . " WHERE s.idCourse = " . (int) $id_course . " AND u.idst IN (" . implode(', ', $subscribed) . ")" . " AND s.level = 3"; 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; } } } list($res) = sql_fetch_row(sql_query($query)); return $res; }
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; }
public function getCourseSubscribedUserIdst($id_course, $no_flat = false, $filter = '') { if (is_numeric($id_course)) { $arr = array($id_course); } elseif (is_array($id_course) && count($id_course) > 0) { $arr =& $id_course; } else { return false; } $query = "SELECT s.idUser, s.idCourse " . " FROM " . $this->subscribe_table . " AS s " . " JOIN " . $this->user_table . " AS u ON u.idst = s.idUser " . " WHERE s.idCourse IN (" . implode(",", $arr) . ") "; if (Docebo::user()->getUserLevelId() != ADMIN_GROUP_GODADMIN) { require_once _base_ . '/lib/lib.preference.php'; $adminManager = new AdminPreference(); $query .= " AND " . $adminManager->getAdminUsersQuery(Docebo::user()->getIdSt(), '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') "; } } $result = $this->db->query($query); $res = array(); while (list($id_user, $id_course) = $this->db->fetch_row($result)) { if ($no_flat) { $res[$id_course][$id_user] = $id_user; } else { $res[$id_user] = (int) $id_user; } } if (!$no_flat) { $res = array_unique($res); } return $res; }
public function getCourseStatsTotal($id_course, $filter) { $query = "SELECT COUNT(*) " . " FROM " . $this->tables['courseuser'] . " as cu " . " JOIN " . $this->tables['user'] . " as u " . " ON (cu.idUser = u.idst AND cu.idCourse=" . (int) $id_course . ") "; if (is_array($filter)) { $condition = array(); if (isset($filter['text']) && $filter['text'] != "") { $conditions[] = " (u.userid LIKE '%" . $filter['text'] . "%' OR u.firstname LIKE '%" . $filter['text'] . "%' OR u.lastname LIKE '%" . $filter['text'] . "%') "; } if (isset($filter['selection']) && $filter['selection'] > 0) { switch ($filter['selection']) { //begin only case 1: $conditions[] = " cu.status = 0 "; break; //itinere only //itinere only case 2: $conditions[] = " cu.status = 1 "; break; //complete only //complete only case 3: $conditions[] = " cu.status = 2 "; break; } } $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 (isset($filter['groups']) && $filter['groups'] > 0) { $gmodel = new GroupmanagementAdm(); $ulist = $gmodel->getGroupAllUsers($filter['groups']); if (!empty($ulist)) { if (!empty($arr_idst)) { $arr_idst = array_merge($arr_idst, $ulist); } else { $arr_idst = $ulist; } } } if (!empty($arr_idst)) { $conditions[] = " u.idst IN (" . implode(",", $arr_idst) . ") "; } if (!empty($conditions)) { $query .= " WHERE " . implode(" AND ", $conditions) . " "; } } $res = $this->db->query($query); if ($res) { list($count) = $this->db->fetch_row($res); } else { $count = false; } return $count; }