function _get_games_query($type = 'html', $report_data = NULL, $other = '')
{
require_once dirname(__FILE__) . '/report_tableprinter.php';
if ($report_data == NULL) {
$ref =& $_SESSION['report_tempdata'];
} else {
$ref =& $report_data;
}
$_ERR_NOUSER = Lang::t('_EMPTY_SELECTION', 'report');
$_ERR_NOCOMP = Lang::t('_EMPTY_SELECTION', 'report');
$_ERR_NODATA = Lang::t('_NO_CONTENT', 'report');
//LO object types translations
require_once _lms_ . '/lib/lib.report.php';
$lang_type = _getLOtranslations();
$sel_all = $ref['rows_filter']['all_users'];
$arr_selected_users = $ref['rows_filter']['users'];
//list of users selected in the filter (users, groups and org.branches)
$comp_all = isset($ref['columns_filter']['all_games']) ? $ref['columns_filter']['all_games'] : false;
$arr_selected_comp = isset($ref['columns_filter']['comp_selection']) ? $ref['columns_filter']['comp_selection'] : array();
//list of communications selected in the filter
$start_date = isset($ref['columns_filter']['comp_start_date']) ? substr($ref['columns_filter']['comp_start_date'], 0, 10) : '';
$end_date = isset($ref['columns_filter']['comp_end_date']) ? substr($ref['columns_filter']['comp_end_date'], 0, 10) : '';
//check and validate time period dates
if (!preg_match('/^(\\d{4})\\D?(0[1-9]|1[0-2])\\D?([12]\\d|0[1-9]|3[01])$/', $start_date) || $start_date == '0000-00-00') {
$start_date = '';
}
if (!preg_match('/^(\\d{4})\\D?(0[1-9]|1[0-2])\\D?([12]\\d|0[1-9]|3[01])$/', $end_date) || $end_date == '0000-00-00') {
$end_date = '';
}
if ($start_date != '') {
$start_date .= ' 00:00:00';
}
if ($end_date != '') {
$end_date .= ' 23:59:59';
}
if ($start_date != '' && $end_date != '') {
if ($start_date > $end_date) {
//invalid time period
$start_date = '';
$end_date = '';
}
}
//other checkings and validations
if (!$sel_all && count($selection) <= 0) {
cout('<p>' . $_ERR_NOUSER . '</p>');
return;
}
$acl_man = new DoceboACLManager();
$acl_man->include_suspended = true;
//extract user idst from selection
if ($sel_all) {
$arr_selected_users = $acl_man->getAllUsersIdst();
} else {
$arr_selected_users = $acl_man->getAllUsersFromIdst($arr_selected_users);
}
if ($comp_all) {
$query = "SELECT id_game FROM %lms_games";
$res = $this->db->query($query);
$arr_selected_comp = array();
while (list($id_game) = $this->db->fetch_row($res)) {
$arr_selected_comp[] = $id_game;
}
}
//admin users filter
$userlevelid = Docebo::user()->getUserLevelId();
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());
$admin_users = $acl_man->getAllUsersFromIdst($admin_tree);
$admin_users = array_unique($admin_users);
//filter users selection by admin visible users
$arr_selected_users = array_intersect($arr_selected_users, $admin_users);
//free some memory
unset($admin_tree);
unset($admin_users);
unset($adminManager);
}
//check selected users ...
if (count($arr_selected_users) <= 0) {
//message: no users selected
cout('<p>' . $_ERR_NOUSER . '</p>');
return;
}
//check selected communications ...
if (count($arr_selected_comp) <= 0) {
//message: no communications selected
cout('<p>' . $_ERR_NOCOMP . '</p>');
return;
}
//set table properties and buffer
$head = array(Lang::t('_GAMES_TITLE', 'report'), Lang::t('_GAMES_TYPE', 'report'), Lang::t('_FROM', 'report'), Lang::t('_TO', 'report'), Lang::t('_USER', 'report'), Lang::t('_GAMES_ATTEMPTED', 'report'), Lang::t('_GAMES_ATTEMPT_DATE', 'report'), Lang::t('_GAMES_FIRST_ATTEMPT_DATE', 'report'), Lang::t('_GAMES_CURRENT_SCORE', 'report'), Lang::t('_GAMES_MAX_SCORE', 'report'), Lang::t('_GAMES_NUM_ATTEMPTS', 'report'));
if ($this->use_mail) {
$head[] = array('style' => 'img-cell', 'value' => $this->_loadEmailIcon());
}
$buffer = new ReportTablePrinter();
$buffer->openTable('', '');
$buffer->openHeader();
$buffer->addHeader($head);
$buffer->closeHeader();
$buffer->openBody();
//rows cycle
//which selected communication has been seen by selected users?
$_YES = Lang::t('_YES', 'standard');
$_NO = Lang::t('_NO', 'standard');
$arr_viewed = array();
$query = "SELECT ct.idReference, c.title, c.type_of, c.start_date, c.end_date, ct.status, " . " ct.dateAttempt, ct.firstAttempt, ct.idUser, u.userid, u.firstname, u.lastname, " . " ct.current_score, ct.max_score, ct.num_attempts " . " FROM (%lms_games_track as ct " . " JOIN %lms_games as c ON (ct.idReference=c.id_game)) " . " JOIN %adm_user as u ON (ct.idUser=u.idst) " . " WHERE ct.idUser IN (" . implode(",", $arr_selected_users) . ") " . " AND c.id_game IN (" . implode(",", $arr_selected_comp) . ") " . ($start_date != '' ? " AND ct.dateAttempt >= '" . $start_date . "' " : "") . ($end_date != '' ? " AND ct.dateAttempt <= '" . $end_date . "' " : "") . " ORDER BY c.title, u.userid";
$res = $this->db->query($query);
if ($this->db->num_rows($res) <= 0) {
cout('<p>' . $_ERR_NODATA . '</p>');
return;
}
while ($obj = $this->db->fetch_obj($res)) {
$line = array();
$line[] = $obj->title;
$line[] = isset($lang_type[$obj->type_of]) ? $lang_type[$obj->type_of] : '';
$line[] = Format::date($obj->start_date, "date");
$line[] = Format::date($obj->end_date, "date");
$line[] = $acl_man->relativeId($obj->userid);
$line[] = $obj->status == 'completed' || $obj->status == 'passed' ? $_YES : $_NO;
$line[] = Format::date($obj->dateAttempt, "datetime");
$line[] = Format::date($obj->firstAttempt, "datetime");
$line[] = $obj->current_score;
$line[] = $obj->max_score;
$line[] = $obj->num_attempts;
if ($this->use_mail) {
$line[] = '<div class="align_center">' . Form::getInputCheckbox('mail_' . $obj->idUser, 'mail_recipients[]', $obj->idUser, isset($_POST['select_all']), '') . '</div>';
}
$buffer->addLine($line);
}
$buffer->closeBody();
$buffer->closeTable();
cout($buffer->get());
$this->_loadEmailActions();
}
function required()
{
require_once _base_ . '/lib/lib.form.php';
require_once _base_ . '/lib/lib.table.php';
require_once _adm_ . '/lib/lib.publicadminmanager.php';
require_once _lms_ . '/lib/lib.course.php';
require_once _lms_ . '/lib/lib.date.php';
require_once _lms_ . '/lib/lib.competences.php';
$db = DbConn::getInstance();
$lang =& DoceboLanguage::CreateInstance('public_coursepanel', 'lms');
$sel_competence = Get::req('sel_competence_req', DOTY_INT, false);
$sel_course = Get::req('sel_course_req', DOTY_INT, false);
$back_url = "index.php?modname=public_coursepanel&op=coursepanel&sel_competence=" . (int) $sel_competence . "&sel_course=" . (int) $sel_course;
$back_ui = getBackUi($back_url, $lang->def('_BACK'));
$sel_comp = Get::req('required_selector', DOTY_INT, 0);
if ($sel_comp) {
$id_pa = getLogUserId();
if (!$id_pa) {
//...
}
$acl_man = new DoceboACLManager();
$admin_manager = new PublicAdminManager();
$array_users = array();
$idst_associated = $admin_manager->getAdminTree($id_pa);
$array_users =& $acl_man->getAllUsersFromIdst($idst_associated);
$array_users = array_unique($array_users);
if (empty($array_users)) {
//error: no users to deal with
cout($back_ui . $lang->def('_NO_USERS') . $back_ui, 'content');
return;
}
cout(getTitleArea($lang->def('_REQUIRED_USERS'), 'coursepanel') . '<div class="std_block">', 'content');
$table = new Table();
$head_labels = array($lang->def('_USERNAME'), $lang->def('_NAME'));
$head_style = array('', '', 'align_center', 'align_center');
$table->addHead($head_labels, $head_style);
//$date = date ("Y-m-d H:i:s", strtotime("+1 months"));
//$now = date("Y-m-d H:i:s");
$man_comp = new Competences_Manager();
$comp_data = $man_comp->GetCompetence($sel_comp);
$already = array();
$query = "SELECT id_user, score_init, score_got FROM %lms_competence_user WHERE id_competence='" . (int) $sel_comp . "'";
$res = $db->query($query);
while (list($id_user, $score_init, $score_got) = $db->fetch_row($res)) {
if ($comp_data['type'] == 'score') {
//if the competence assignment exists in DB, but the total score is 0, then it's considered as non-assigned
if ((int) $score_init + (int) $score_got > 0) {
$already[] = $id_user;
}
} else {
$already[] = $id_user;
}
}
$already = array_unique($already);
$array_users = array_diff($array_users, $already);
//get required competences not got from users
$query = "";
if ($comp_data['type'] == 'score') {
$query = "SELECT u.idst, u.userid, u.lastname, u.firstname FROM %lms_competence_required as cr " . " JOIN %adm_user as u ON (cr.idst = u.idst)" . " WHERE cr.idst IN (" . implode(",", $array_users) . ") AND cr.id_competence=" . (int) $sel_comp . " ORDER BY u.userid";
} else {
$query = "SELECT u.idst, u.userid, u.lastname, u.firstname FROM %lms_competence_required as cr " . " JOIN %adm_user as u ON (cr.idst = u.idst) " . " WHERE cr.idst IN (" . implode(",", $array_users) . ") AND cr.id_competence=" . (int) $sel_comp . " ORDER BY u.userid";
}
$res = $db->query($query);
if (sql_num_rows($res) > 0) {
while (list($idst, $userid, $lastname, $firstname) = $db->fetch_row($res)) {
$line = array();
$line[] = $acl_man->relativeId($userid);
$line[] = $lastname . " " . $firstname;
$table->addBody($line);
}
//cout(getTitleArea($lang->def('_EXPIRING_USERS'), 'coursepanel').'<div class="std_block">', 'content');
cout($lang->def('_COMPETENCE') . ': <b>' . $comp_data['name'] . '</b>', 'content');
cout($back_ui . $table->getTable() . $back_ui, 'content');
} else {
cout($back_ui . $lang->def('_NO_REQUIRED_USER') . $back_ui, 'content');
}
} else {
//error, no competence selected
cout($back_ui . $lang->def('_NO_COMPETENCE_SELECTED') . $back_ui, 'content');
return;
}
}
function _get_time_query($type = 'html', $report_data = NULL, $other = '')
{
require_once $GLOBALS['where_lms'] . '/lib/lib.course.php';
require_once dirname(__FILE__) . '/report_tableprinter.php';
if ($report_data == NULL) {
$ref =& $_SESSION['report_tempdata'];
} else {
$ref =& $report_data;
}
$fw = $GLOBALS['prefix_fw'];
$lms = $GLOBALS['prefix_lms'];
$sel_all = $ref['rows_filter']['select_all'];
$sel_type = $ref['rows_filter']['selection_type'];
$selection = $ref['rows_filter']['selection'];
$timetype = $ref['columns_filter']['timetype'];
$years =& $ref['columns_filter']['years'];
$months =& $ref['columns_filter']['months'];
if (!$sel_all && count($selection) <= 0) {
cout('<p>' . $this->lang->def('_EMPTY_SELECTION') . '</p>');
return;
}
$acl = new DoceboACLManager();
$acl->include_suspended = true;
//admin users filter
$userlevelid = Docebo::user()->getUserLevelId();
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());
$admin_users = $acl_man->getAllUsersFromIdst($admin_tree);
$admin_users = array_unique($admin_users);
}
$html = '';
$times = array();
switch ($timetype) {
case 'years':
$now = date('Y');
for ($i = $now - $years + 1; $i <= $now; $i++) {
$times[] = $i;
}
break;
case 'months':
//...
break;
}
switch ($sel_type) {
case 'users':
$data = array();
$users_list = $sel_all ? $acl->getAllUsersIdst() : $acl->getAllUsersFromIdst($selection);
$users_list = array_unique($users_list);
if ($userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {
$users_list = array_intersect($users_list, $admin_users);
}
$query = "SELECT idUser, YEAR(date_complete) as yearComplete " . " FROM " . $lms . "_courseuser " . " WHERE status=2 " . ($userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous() ? " AND idUser IN (" . implode(",", $users_list) . ") " : "");
$res = sql_query($query);
while ($row = mysql_fetch_assoc($res)) {
//$data[ $row['idUser'] ][ $row['yearComplete'] ] = $row['complete'];
$idUser = $row['idUser'];
$year = $row['yearComplete'];
if (!isset($data[$idUser][$year])) {
$data[$idUser][$year] = 0;
}
$data[$idUser][$year]++;
}
$usernames = array();
$query = "SELECT idst, userid FROM " . $fw . "_user WHERE idst IN (" . implode(",", $users_list) . ")";
$res = sql_query($query);
while ($row = mysql_fetch_assoc($res)) {
$usernames[$row['idst']] = $acl->relativeId($row['userid']);
}
//draw table
$buffer = new ReportTablePrinter($type, true);
$buffer->openTable('', '');
$head = array($this->lang->def('_USER'));
foreach ($times as $time) {
$head[] = $time;
}
$head[] = $this->lang->def('_TOTAL');
$buffer->openHeader();
$buffer->addHeader($head);
$buffer->closeHeader();
$tot_total = 0;
$buffer->openBody();
foreach ($users_list as $user) {
if (!isset($usernames[$user])) {
break;
}
$line = array();
$line_total = 0;
$line[] = $usernames[$user];
foreach ($times as $time) {
//years or months
switch ($timetype) {
case 'years':
if (isset($data[$user][$time])) {
$line[] = $data[$user][$time];
$line_total += $data[$user][$time];
} else {
$line[] = '0';
}
break;
case 'months':
//$year = ...
//$month = ...
//$line[] = (isset($data[$group][$year][$month]) ? $data[$group][$year][$month] : '0'); break;
}
}
$line[] = $line_total;
$tot_total += $line_total;
$buffer->addLine($line);
}
$buffer->closeBody();
//totals
$foot = array('');
foreach ($times as $time) {
$temp = 0;
foreach ($users_list as $user) {
if (isset($data[$user][$time])) {
$temp += $data[$user][$time];
}
}
$foot[] = $temp;
}
$foot[] = $tot_total;
$buffer->setFoot($foot);
$buffer->closeTable();
$html .= $buffer->get();
break;
//--------------------
//--------------------
case 'groups':
//retrieve all labels
$orgchart_labels = array();
$query = "SELECT * FROM " . $fw . "_org_chart WHERE lang_code='" . getLanguage() . "'";
$res = sql_query($query);
while ($row = mysql_fetch_assoc($res)) {
$orgchart_labels[$row['id_dir']] = $row['translation'];
}
$labels = array();
//$query = "SELECT * FROM ".$fw."_group WHERE (hidden='false' OR groupid LIKE '/oc_%' OR groupid LIKE '/ocd_%') AND type='free'";
$query = "SELECT * FROM " . $fw . "_group WHERE groupid LIKE '/oc\\_%' OR groupid LIKE '/ocd\\_%' OR hidden = 'false' ";
$res = sql_query($query);
while ($row = mysql_fetch_assoc($res)) {
if ($row['hidden'] == 'false') {
$labels[$row['idst']] = $acl->relativeId($row['groupid']);
} else {
$temp = explode("_", $row['groupid']);
//echo '<div>'.print_r($temp,true).'</div>';
if ($temp[0] == '/oc') {
$labels[$row['idst']] = $temp[1] != 0 ? $orgchart_labels[$temp[1]] : '';
} elseif ($temp[0] == '/ocd') {
$labels[$row['idst']] = $temp[1] != 0 ? $orgchart_labels[$temp[1]] : '';
}
}
}
//solve groups user
$solved_groups = array();
$subgroups_list = array();
foreach ($selection as $group) {
$temp = $acl->getGroupGDescendants($group);
$temp[] = $group;
foreach ($temp as $idst_subgroup) {
$solved_groups[$idst_subgroup] = $group;
}
$subgroups_list = array_merge($subgroups_list, $temp);
}
$query = "SELECT gm.idst as idGroup, YEAR(cu.date_complete) as yearComplete, MONTH(cu.date_complete) as monthComplete " . " FROM " . $lms . "_courseuser as cu JOIN " . $fw . "_group_members as gm ON (cu.idUser=gm.idstMember) " . " WHERE status=2 AND gm.idst IN (" . implode(",", $subgroups_list) . ")";
$data = array();
$res = sql_query($query);
while ($row = mysql_fetch_assoc($res)) {
$idGroup = $solved_groups[$row['idGroup']];
$year = $row['yearComplete'];
$month = $row['monthComplete'];
switch ($timetype) {
case 'years':
if (!isset($data[$idGroup][$year])) {
$data[$idGroup][$year] = 0;
}
$data[$idGroup][$year]++;
break;
case 'months':
if (!isset($data[$idGroup][$year][$month])) {
$data[$idGroup][$year][$month] = 0;
}
$data[$idGroup][$year][$month]++;
break;
}
//end switch
}
//draw table
$buffer = new ReportTablePrinter($type, true);
$buffer->openTable('', '');
$head = array($this->lang->def('_GROUPS'), $this->lang->def('_USERS'));
foreach ($times as $time) {
$head[] = $time;
}
$head[] = $this->lang->def('_TOTAL');
$buffer->openHeader();
$buffer->addHeader($head);
$buffer->closeHeader();
$tot_users = 0;
$tot_total = 0;
$buffer->openBody();
foreach ($selection as $group) {
$group_users = $acl->getGroupAllUser($group);
if ($userlevelid != ADMIN_GROUP_GODADMIN && !Docebo::user()->isAnonymous()) {
$group_users = array_intersect($group_users, $admin_users);
}
$users_num = count($group_users);
$line = array();
$line_total = 0;
$line[] = $labels[$group];
$line[] = $users_num;
foreach ($times as $time) {
//years or months
switch ($timetype) {
case 'years':
if (isset($data[$group][$time])) {
$line[] = $data[$group][$time];
$line_total += $data[$group][$time];
} else {
$line[] = '0';
}
break;
case 'months':
//$year = ...
//$month = ...
//$line[] = (isset($data[$group][$year][$month]) ? $data[$group][$year][$month] : '0'); break;
}
}
$line[] = $line_total;
$tot_users += $users_num;
$tot_total += $line_total;
$buffer->addLine($line);
}
$buffer->closeBody();
//totals
$foot = array('', $tot_users);
foreach ($times as $time) {
$temp = 0;
foreach ($selection as $group) {
if (isset($data[$group][$time])) {
$temp += $data[$group][$time];
}
}
$foot[] = $temp;
}
$foot[] = $tot_total;
$buffer->setFoot($foot);
$buffer->closeTable();
$html .= $buffer->get();
break;
}
//end switch
cout($html);
}
function formatter_userid(&$column, &$record, &$data, $args = false)
{
$acl = new DoceboACLManager();
$output = $acl->relativeId($data);
return $output;
}
