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;
}