/**
* execute the connection to source
*/
function connect()
{
$this->lang = DoceboLanguage::createInstance('ru_report');
// perform the query for data retriving
$course_man = new Man_Course();
$this->acl_man = new DoceboACLManager();
$p_dr = new PeopleDataRetriever($GLOBALS['dbConn'], $GLOBALS['prefix_fw']);
$re_people = $p_dr->getAllRowsIdst();
$this->_query_result = false;
$this->_readed_end = false;
$this->row_index = 0;
$user_selected = array();
if (!$re_people) {
$this->_readed_end = true;
return TRUE;
}
// find some information
$query_course_user = "******" . $GLOBALS['prefix_lms'] . "_courseuser AS cu \r\n\t\t\tJOIN " . $GLOBALS['prefix_lms'] . "_course AS c \r\n\t\tWHERE cu.idCourse = c.idCourse \r\n\t\t\tAND cu.idUser IN ( " . implode(',', $user_selected) . " ) \r\n\t\t\tAND cu.status = '" . _CUS_END . "' ";
$this->_query_result = sql_query($query_course_user);
if (!$this->_query_result) {
$this->last_error = mysql_error();
return FALSE;
}
return TRUE;
}
function get_next_row()
{
if ($this->data === false) {
$pdr = new PeopleDataRetriever($GLOBALS['dbConn'], $GLOBALS['prefix_fw']);
if ($this->tree != 0) {
//list($this->tree_desc) = $this->tree_view->tdb->getDescendantsSTFromST(array($this->tree));
$pdr->setGroupFilter($this->tree, true);
}
$this->data = $pdr->getRows();
}
$row = sql_fetch_row($this->data);
//print_r($row);
if ($row == false) {
$this->eof = true;
return false;
}
$export = array();
//find user field value
foreach ($this->simplecols as $field_id => $name) {
if (is_numeric($field_id)) {
$pluto = $this->fl->fieldValue((int) $field_id, array($row[0]));
list(, $export[]) = each($pluto);
} else {
switch ($field_id) {
case "userid":
$export[] = substr($row[1], 1);
break;
case "firstname":
$export[] = $row[2];
break;
case "lastname":
$export[] = $row[3];
break;
case "pass":
$export[] = '';
break;
case "email":
$export[] = $row[4];
break;
case "signature":
$export[] = $row[6];
break;
}
}
}
$this->index++;
return $export;
}
/**
* various stats and data retrieving to display in the dashboard
*
* @param boolean $stats_required
* @param boolean $arr_users
* @return array
*/
public function getUsersStats($stats_required = false, $arr_users = false)
{
$aclManager = Docebo::user()->getACLManager();
$users = array();
if ($stats_required == false || empty($stats_required) || !is_array($stats_required)) {
$stats_required = array('all', 'suspended', 'register_today', 'register_yesterday', 'register_7d', 'now_online', 'inactive_30d', 'waiting', 'superadmin', 'admin', 'public_admin');
}
$stats_required = array_flip($stats_required);
$data = new PeopleDataRetriever($GLOBALS['dbConn'], $GLOBALS['prefix_fw']);
if (!empty($this->users_filter)) {
$data->setUserFilter($this->users_filter);
}
if (isset($stats_required['all'])) {
$users['all'] = $data->getTotalRows();
}
if (isset($stats_required['suspended'])) {
$data->addFieldFilter('valid', 0);
$data->addFieldFilter('userid', 'Anonymous', '<>');
//or idst <> Docebo::user()->getAnonymousId() ...
$users['suspended'] = $data->getTotalRows();
}
if (isset($stats_required['register_today'])) {
$data->resetFieldFilter();
$data->addFieldFilter('register_date', date("Y-m-d") . ' 00:00:00', '>');
$users['register_today'] = $data->getTotalRows();
}
if (isset($stats_required['register_yesterday'])) {
$data->resetFieldFilter();
$yesterday = date("Y-m-d", time() - 86400);
$data->addFieldFilter('register_date', $yesterday . ' 00:00:00', '>');
$data->addFieldFilter('register_date', $yesterday . ' 23:59:59', '<');
$users['register_yesterday'] = $data->getTotalRows();
}
if (isset($stats_required['register_7d'])) {
$data->resetFieldFilter();
$sevendaysago = date("Y-m-d", time() - 7 * 86400);
$data->addFieldFilter('register_date', $sevendaysago . ' 00:00:00', '>');
$users['register_7d'] = $data->getTotalRows();
}
if (isset($stats_required['now_online'])) {
$data->resetFieldFilter();
$data->addFieldFilter('lastenter', date("Y-m-d H:i:s", time() - REFRESH_LAST_ENTER), '>');
$users['now_online'] = $data->getTotalRows();
if ($arr_users !== false && is_array($arr_users) && count($arr_users) > 0) {
$data->setUserFilter($arr_users);
$users['now_online_filtered'] = $data->getTotalRows();
} else {
$users['now_online_filtered'] = 0;
}
}
if (isset($stats_required['inactive_30d'])) {
$data->resetFieldFilter();
$data->addFieldFilter('lastenter', date("Y-m-d", time() - 30 * 86400) . ' 00:00:00', '<');
$data->addFieldFilter('userid', 'Anonymous', '<>');
//or idst <> Docebo::user()->getAnonymousId() ...
$users['inactive_30d'] = $data->getTotalRows();
}
if (isset($stats_required['waiting'])) {
$users['waiting'] = $aclManager->getTempUserNumber();
}
if (isset($stats_required['superadmin'])) {
$idst_sadmin = $aclManager->getGroupST(ADMIN_GROUP_GODADMIN);
$users['superadmin'] = $aclManager->getGroupUMembersNumber($idst_sadmin);
}
if (isset($stats_required['admin'])) {
$idst_admin = $aclManager->getGroupST(ADMIN_GROUP_ADMIN);
$users['admin'] = $aclManager->getGroupUMembersNumber($idst_admin);
}
if (isset($stats_required['public_admin'])) {
$idst_padmin = $aclManager->getGroupST(ADMIN_GROUP_PUBLICADMIN);
$users['public_admin'] = $aclManager->getGroupUMembersNumber($idst_padmin);
}
return $users;
}
/**
* execute the connection to source
**/
function connect()
{
$this->lang = DoceboLanguage::createInstance('rg_report');
// perform the query for data retriving
$course_man = new Man_Course();
$this->acl_man = new DoceboACLManager();
$p_dr = new PeopleDataRetriever($GLOBALS['dbConn'], $GLOBALS['prefix_fw']);
$re_people = $p_dr->getAllRowsIdst();
$this->_readed_end = false;
$this->row_index = 0;
$user_selected = array();
if (!$re_people) {
$this->_readed_end = true;
return TRUE;
}
while (list($idst) = sql_fetch_row($re_people)) {
$user_selected[$idst] = $idst;
}
$this->users_info = $this->acl_man->getUsers($user_selected);
$this->id_courses = $course_man->getAllCourses();
$this->num_iscr = array();
$this->num_nobegin = array();
$this->num_itinere = array();
$this->num_end = array();
$this->time_in_course = array();
$effective_user = array();
$query_course_user = "******" . $GLOBALS['prefix_lms'] . "_courseuser AS cu \r\n\t\tWHERE idUser IN ( " . implode(',', $user_selected) . " ) ";
$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($this->num_iscr[$id_c])) {
++$this->num_iscr[$id_c];
} else {
$this->num_iscr[$id_c] = 1;
}
if ($fisrt_access === NULL) {
//never enter
if (isset($this->num_nobegin[$id_c])) {
++$this->num_nobegin[$id_c];
} else {
$this->num_nobegin[$id_c] = 1;
}
} elseif ($date_complete === NULL) {
//enter
if (isset($this->num_itinere[$id_c])) {
++$this->num_itinere[$id_c];
} else {
$this->num_itinere[$id_c] = 1;
}
} else {
//complete
if (isset($this->num_end[$id_c])) {
++$this->num_end[$id_c];
} else {
$this->num_end[$id_c] = 1;
}
}
$effective_user[] = $id_u;
}
if (!empty($effective_user)) {
$query_time = "\r\n\t\t\tSELECT idCourse, SUM(UNIX_TIMESTAMP(lastTime) - UNIX_TIMESTAMP(enterTime)) \r\n\t\t\tFROM " . $GLOBALS['prefix_lms'] . "_tracksession \r\n\t\t\tWHERE idUser IN ( " . implode(',', $effective_user) . " )\r\n\t\t\tGROUP BY idCourse ";
$re_time = sql_query($query_time);
while (list($id_c, $time_num) = sql_fetch_row($re_time)) {
$this->time_in_course[$id_c] = $time_num;
}
}
return TRUE;
}
function &getAllUserIdst()
{
$p_dr = new PeopleDataRetriever($GLOBALS['dbConn'], $GLOBALS['prefix_fw']);
$userlevelid = Docebo::user()->getUserLevelId();
if ($userlevelid != ADMIN_GROUP_GODADMIN) {
require_once _base_ . '/lib/lib.preference.php';
$adminManager = new AdminPreference();
$p_dr->intersectGroupFilter($adminManager->getAdminTree(Docebo::user()->getIdSt()));
}
$re_people = $p_dr->getAllRowsIdst();
$user_selected = array();
if (!$re_people) {
return $user_selected;
}
while (list($idst) = sql_fetch_row($re_people)) {
$user_selected[$idst] = $idst;
}
return $user_selected;
}
