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'); require_once _lms_ . '/lib/lib.report.php'; $lang_type = _getLOtranslations(); $sel_all = $ref['rows_filter']['select_all']; $arr_selected_users = $ref['rows_filter']['selection']; //list of users selected in the filter (users, groups and org.branches) $comp_all = $ref['columns_filter']['all_games']; $arr_selected_comp = $ref['columns_filter']['comp_selection']; //list of communications selected in the filter $start_date = substr($ref['columns_filter']['comp_start_date'], 0, 10); $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 = ''; } } //instantiate acl manager $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); } //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); } //Has the "All games" options been selected ? 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; } } //check selected users ... //$arr_selected_users = array(); //list of users selected in the filter (users, groups and org.branches) if ($arr_selected_users <= 0) { cout('<p>' . $_ERR_NOUSER . '</p>'); return; } //$arr_selected_comp = array(); //list of communications selected in the filter if ($arr_selected_comp <= 0) { cout('<p>' . $_ERR_NOCOMP . '</p>'); return; } $arr_comp = array(); //array $id_comm => list of generic idst foreach ($arr_selected_comp as $id_game) { $arr_comp[$id_game] = array(); } //if no users have been assigned to the games, than display as 0 - 0 $arr_idst = array(); //flat list of idst $query = "SELECT * FROM %lms_games_access WHERE id_comp IN (" . implode(",", $arr_selected_comp) . ")"; $res = $this->db->query($query); while (list($id_game, $idst) = $this->db->fetch_row($res)) { $arr_idst[] = $idst; $arr_comp[$id_game][] = $idst; } if (count($arr_idst) <= 0) { cout('<p>' . $_ERR_NOUSER . '</p>'); return; } $arr_groups = array(); //flat list of group idst $query = "SELECT idst FROM %adm_group WHERE idst IN (" . implode(",", $arr_idst) . ")"; $res = $this->db->query($query); while (list($idst) = $this->db->fetch_row($res)) { $arr_groups[] = $idst; } //if any group selected, then extract users and create an array [id_group][id_user] $arr_idst_users_flat = array(); $arr_members = array(); //array $idst group => list of idst if (count($arr_groups) > 0) { $query = "SELECT idst, idstMember FROM %adm_group_members WHERE " . " idst IN (" . implode(",", $arr_groups) . ")" . " AND idstMember IN (" . implode(",", $arr_selected_users) . ")"; $res = $this->db->query($query); while (list($idst, $idstMember) = $this->db->fetch_row($res)) { $arr_members[$idst][] = $idstMember; $arr_idst_users_flat[] = $idstMember; } } //set an array with all users idst ($_all) $diff = array_diff($arr_selected_users, $arr_groups); $_all_users = array_merge($arr_idst_users_flat, $diff); unset($diff); if (count($_all_users) <= 0) { cout('<p>' . $_ERR_NOUSER . '</p>'); return; } //users have been extracted by group, now calculate report's rows ---------- //get games info data and put it in an array by id_game => {info} $arr_comp_data = array(); $query = "SELECT * FROM %lms_games WHERE id_game IN (" . implode(",", $arr_selected_comp) . ")"; $res = $this->db->query($query); while ($obj = $this->db->fetch_obj($res)) { $arr_comp_data[$obj->id_game] = array('title' => $obj->title, 'type_of' => $obj->type_of, 'start_date' => $obj->start_date, 'end_date' => $obj->end_date); } //which selected communication has been seen by selected users? $arr_viewed = array(); $query = "SELECT idReference, COUNT(idUser) as count " . " FROM %lms_games_track WHERE status IN ('completed', 'passed') " . " AND idUser IN (" . implode(",", $_all_users) . ") " . " AND idReference IN (" . implode(",", $arr_selected_comp) . ") " . ($start_date != '' ? " AND dateAttempt >= '" . $start_date . "' " : "") . ($end_date != '' ? " AND dateAttempt <= '" . $end_date . "' " : "") . " GROUP BY idReference"; $res = $this->db->query($query); while ($obj = $this->db->fetch_obj($res)) { $arr_viewed[$obj->idReference] = $obj->count; } //calculate average values, no conditions on the status $arr_average = array(); $query = "SELECT idReference, AVG(current_score) as average_current_score, " . " AVG(max_score) as average_max_score, AVG(num_attempts) as average_num_attempts " . " FROM %lms_games_track WHERE idUser IN (" . implode(",", $_all_users) . ") " . " AND idReference IN (" . implode(",", $arr_selected_comp) . ") " . ($start_date != '' ? " AND dateAttempt >= '" . $start_date . "' " : "") . ($end_date != '' ? " AND dateAttempt <= '" . $end_date . "' " : "") . " GROUP BY idReference"; $res = $this->db->query($query); while ($obj = $this->db->fetch_obj($res)) { $arr_average[$obj->idReference] = $obj; } /* //user details buffer $acl_man = Docebo::user()->getAclManager(); $user_details = array(); $query = "SELECT idst, userid FROM %adm_user WHERE idst IN (".implode(",", $_all_users).")"; $res = $this->db->query($query); while ($obj = $this->db->fetch_obj($res)) { $user_details[$obj->idst] = $acl_man($obj->userid); } */ //set table properties and buffer $head = array(Lang::t('_GAMES_TITLE', 'report'), Lang::t('_GAMES_TYPE', 'report'), Lang::t('_FROM', 'standard'), Lang::t('_TO', 'standard'), Lang::t('_GAMES_ATTEMPTED', 'report'), Lang::t('_GAMES_TOTAL', 'report'), Lang::t('_GAMES_PERCENT', 'report'), Lang::t('_GAMES_AVG_SCORE', 'report'), Lang::t('_GAMES_AVG_MAX_SCORE', 'report'), Lang::t('_GAMES_AVG_NUM_ATTEMPTS', 'report')); $buffer = new ReportTablePrinter(); $buffer->openTable('', ''); $buffer->openHeader(); $buffer->addHeader($head); $buffer->closeHeader(); $buffer->openBody(); //rows cycle foreach ($arr_comp as $id_game => $comp_id_list) { //calculate total assigned users for every communication $count = 0; foreach ($comp_id_list as $idst) { if (array_key_exists($idst, $arr_members)) { foreach ($arr_members[$idst] as $idst_user) { $count++; } } else { $count++; } } //line (one per communication) $line = array(); $type_of = $arr_comp_data[$id_game]['type_of']; $completed_by = isset($arr_viewed[$id_game]) ? $arr_viewed[$id_game] : 0; $line[] = $arr_comp_data[$id_game]['title']; $line[] = isset($lang_type[$type_of]) ? $lang_type[$type_of] : ''; $line[] = Format::date($arr_comp_data[$id_game]['start_date'], 'date'); $line[] = Format::date($arr_comp_data[$id_game]['end_date'], 'date'); $line[] = $completed_by; $line[] = $count; $line[] = number_format(($count > 0 ? $completed_by / $count : 0) * 100, 2, ',', '') . ' %'; $avg1 = isset($arr_average[$id_game]) ? $arr_average[$id_game]->average_current_score : ''; $avg2 = isset($arr_average[$id_game]) ? $arr_average[$id_game]->average_max_score : ''; $avg3 = isset($arr_average[$id_game]) ? $arr_average[$id_game]->average_num_attempts : ''; $line[] = number_format($avg1, 2, ',', '.'); $line[] = number_format($avg2, 2, ',', '.'); $line[] = number_format($avg3, 2, ',', '.'); $buffer->addLine($line); } $buffer->closeBody(); $buffer->closeTable(); cout($buffer->get()); }
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 _printTable_course($type, $course, $stats, $filter_cols) { require_once _lms_ . '/admin/modules/report/report_tableprinter.php'; $buffer = new ReportTablePrinter($type); $output = ''; $lang =& DoceboLanguage::createInstance('report', 'framework'); $glang =& DoceboLanguage::createInstance('admin_course_managment', 'lms'); $query = "SELECT idCategory, path" . " FROM " . $GLOBALS['prefix_lms'] . "_category"; $result = sql_query($query); $array_category = array(0 => $lang->def('_NONE')); while (list($id_cat, $name_cat) = sql_fetch_row($result)) { $array_category[$id_cat] = substr($name_cat, 5, strlen($name_cat) - 5); } $array_status = array(CST_PREPARATION => $lang->def('_CST_PREPARATION', 'admin_course_managment', 'lms'), CST_AVAILABLE => $glang->def('_CST_AVAILABLE'), CST_EFFECTIVE => $glang->def('_CST_CONFIRMED'), CST_CONCLUDED => $glang->def('_CST_CONCLUDED'), CST_CANCELLED => $glang->def('_CST_CANCELLED')); $colspan_course = 0; if (in_array('_CODE_COURSE', $filter_cols)) { $colspan_course++; } if (in_array('_NAME_COURSE', $filter_cols)) { $colspan_course++; } if (in_array('_COURSE_CATEGORY', $filter_cols)) { $colspan_course++; } if (in_array('_COURSESTATUS', $filter_cols)) { $colspan_course++; } if (in_array('_PUBLICATION_DATE', $filter_cols)) { $colspan_course++; } $colspan_stats = 0; if (in_array('_HIGH_VOTE', $filter_cols)) { $colspan_stats++; } if (in_array('_LESS_VOTE', $filter_cols)) { $colspan_stats++; } if (in_array('_MEDIUM_VOTE', $filter_cols)) { $colspan_stats++; } $th1 = array(array('colspan' => $colspan_course, 'value' => $lang->def('_COURSE')), array('colspan' => $colspan_stats, 'value' => $lang->def('_DOC_STATS'))); $th2 = array(); if (in_array('_CODE_COURSE', $filter_cols)) { $th2[] = $glang->def('_COURSE_CODE'); } if (in_array('_NAME_COURSE', $filter_cols)) { $th2[] = $glang->def('_COURSE_NAME'); } if (in_array('_COURSE_CATEGORY', $filter_cols)) { $th2[] = $glang->def('_CATEGORY'); } if (in_array('_COURSESTATUS', $filter_cols)) { $th2[] = $glang->def('_STATUS'); } if (in_array('_PUBLICATION_DATE', $filter_cols)) { $th2[] = $glang->def('_CREATION_DATE'); } if (in_array('_HIGH_VOTE', $filter_cols)) { $th2[] = $lang->def('_MAX_SCORE'); } if (in_array('_LESS_VOTE', $filter_cols)) { $th2[] = $lang->def('_MIN_SCORE'); } if (in_array('_MEDIUM_VOTE', $filter_cols)) { $th2[] = $lang->def('_SCORE'); } $buffer->openTable($lang->def('_COURSE_CAPTION'), $lang->def('_COURSE_SUMMAMRY_MANAGMENT')); $buffer->openHeader(); $buffer->addHeader($th1); $buffer->addHeader($th2); $buffer->closeHeader(); $buffer->openBody(); foreach ($course as $course_info) { $trow = array(); if (in_array('_CODE_COURSE', $filter_cols)) { $trow[] = addslashes($course_info['code']); } if (in_array('_NAME_COURSE', $filter_cols)) { $trow[] = addslashes($course_info['name']); } if (in_array('_COURSE_CATEGORY', $filter_cols)) { $trow[] = $array_category[$course_info['idCategory']]; } if (in_array('_COURSESTATUS', $filter_cols)) { $trow[] = isset($array_status[$course_info['status']]) ? $array_status[$course_info['status']] : ''; } if (in_array('_PUBLICATION_DATE', $filter_cols)) { $trow[] = Format::date($course_info['create_date'], 'datetime'); } if (in_array('_HIGH_VOTE', $filter_cols)) { $trow[] = isset($stats[$course_info['id_quest']]) ? (string) $stats[$course_info['id_quest']]['max_value'] : '-'; } if (in_array('_LESS_VOTE', $filter_cols)) { $trow[] = isset($stats[$course_info['id_quest']]) ? (string) $stats[$course_info['id_quest']]['min_value'] : '-'; } if (in_array('_MEDIUM_VOTE', $filter_cols)) { $trow[] = isset($stats[$course_info['id_quest']]) ? (string) $stats[$course_info['id_quest']]['everage_value'] : '-'; } $buffer->addLine($trow); } $buffer->closeTable(); $buffer->closeBody(); return $buffer->get(); }