break;
case 'get_survey_overview':
$sessionId = 0;
if (!empty($_GET['session_id']) && !empty($_GET['course_id']) && !empty($_GET['survey_id'])) {
$sessionId = intval($_GET['session_id']);
$courseId = intval($_GET['course_id']);
$surveyId = intval($_GET['survey_id']);
$date_from = $_GET['date_from'];
$date_to = $_GET['date_to'];
//$course = api_get_course_info_by_id($courseId);
}
/**
* Add lessons of course
*/
$columns = array('username', 'firstname', 'lastname');
$questions = SurveyManager::get_questions($surveyId, $courseId);
foreach ($questions as $question_id => $question) {
$columns[] = $question_id;
}
$result = SessionManager::get_survey_overview($sessionId, $courseId, $surveyId, $date_from, $date_to, array('where' => $whereCondition, 'order' => "{$sidx} {$sord}", 'limit' => "{$start} , {$limit}"));
break;
case 'get_session_progress':
$columns = array('lastname', 'firstname', 'username', 'total', 'courses', 'lessons', 'exercises', 'forums', 'homeworks', 'wikis', 'surveys', 'lessons_total', 'lessons_done', 'lessons_left', 'lessons_progress', 'exercises_total', 'exercises_done', 'exercises_left', 'exercises_progress', 'forums_total', 'forums_done', 'forums_left', 'forums_progress', 'assignments_total', 'assignments_done', 'assignments_left', 'assignments_progress', 'wiki_total', 'wiki_revisions', 'wiki_read', 'wiki_unread', 'wiki_progress', 'surveys_total', 'surveys_done', 'surveys_left', 'surveys_progress');
$sessionId = 0;
if (!empty($_GET['course_id']) && !empty($_GET['session_id'])) {
$sessionId = intval($_GET['session_id']);
$courseId = intval($_GET['course_id']);
}
$result = SessionManager::get_session_progress($sessionId, $courseId, null, null, array('where' => $whereCondition, 'order' => "{$sidx} {$sord}", 'limit' => "{$start} , {$limit}"));
break;
case 'get_session_access_overview':
/**
* Gets the survey answers
* @param int session id
* @param int course id
* @param int survey id
* @param array options order and limit keys
* @todo fix the query
* @return array table with user name, lp name, progress
*/
public static function get_survey_overview($sessionId = 0, $courseId = 0, $surveyId = 0, $date_from, $date_to, $options)
{
//escaping vars
$sessionId = intval($sessionId);
$courseId = intval($courseId);
$surveyId = intval($surveyId);
$date_from = Database::escape_string($date_from);
$date_to = Database::escape_string($date_to);
//tables
$session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$user = Database::get_main_table(TABLE_MAIN_USER);
$tbl_course_lp_view = Database::get_course_table(TABLE_LP_VIEW);
$c_survey = Database::get_course_table(TABLE_SURVEY);
$c_survey_answer = Database::get_course_table(TABLE_SURVEY_ANSWER);
$c_survey_question = Database::get_course_table(TABLE_SURVEY_QUESTION);
$c_survey_question_option = Database::get_course_table(TABLE_SURVEY_QUESTION_OPTION);
$course = api_get_course_info_by_id($courseId);
$where = " WHERE c_id = '%s' AND s.status <> 2 AND session_id = %s";
$limit = null;
if (!empty($options['limit'])) {
$limit = " LIMIT " . $options['limit'];
}
if (!empty($options['where'])) {
$where .= ' ' . $options['where'];
}
$order = null;
if (!empty($options['order'])) {
$order = " ORDER BY " . $options['order'];
}
$sql = "SELECT u.user_id, u.lastname, u.firstname, u.username, u.email, s.c_id\n FROM {$session_course_user} s\n INNER JOIN {$user} u ON u.user_id = s.user_id\n {$where} {$order} {$limit}";
$sql_query = sprintf($sql, intval($course['real_id']), $sessionId);
$rs = Database::query($sql_query);
while ($user = Database::fetch_array($rs)) {
$users[$user['user_id']] = $user;
}
//Get survey questions
$questions = SurveyManager::get_questions($surveyId, $courseId);
//Survey is anonymous?
$result = Database::query(sprintf("SELECT anonymous FROM {$c_survey} WHERE survey_id = %d", $surveyId));
$row = Database::fetch_array($result);
$anonymous = $row['anonymous'] == 1 ? true : false;
$table = array();
foreach ($users as $user) {
$data = array('lastname' => $anonymous ? '***' : $user[1], 'firstname' => $anonymous ? '***' : $user[2], 'username' => $anonymous ? '***' : $user[3]);
//Get questions by user
$sql = "SELECT sa.question_id, sa.option_id, sqo.option_text, sq.type\n FROM {$c_survey_answer} sa\n INNER JOIN {$c_survey_question} sq\n ON sq.question_id = sa.question_id\n LEFT JOIN {$c_survey_question_option} sqo\n ON\n sqo.c_id = sa.c_id AND\n sqo.question_id = sq.question_id AND\n sqo.question_option_id = sa.option_id AND\n sqo.survey_id = sq.survey_id\n WHERE\n sa.survey_id = %d AND\n sa.c_id = %d AND\n sa.user = %d\n ";
//. $where_survey;
$sql_query = sprintf($sql, $surveyId, $courseId, $user['user_id']);
$result = Database::query($sql_query);
$user_questions = array();
while ($row = Database::fetch_array($result)) {
$user_questions[$row['question_id']] = $row;
}
//Match course lessons with user progress
foreach ($questions as $question_id => $question) {
$option_text = 'option_text';
if ($user_questions[$question_id]['type'] == 'open') {
$option_text = 'option_id';
}
$data[$question_id] = $user_questions[$question_id][$option_text];
}
$table[] = $data;
}
return $table;
}
/**
* This function displays the comparative report which allows you to compare two questions
* A comparative report creates a table where one question is on the x axis and a second question is on the y axis.
* In the intersection is the number of people who have answerd positive on both options.
*
* @return string HTML code
*
* @author Patrick Cool <*****@*****.**>, Ghent University
* @version February 2007
*/
public static function display_comparative_report()
{
// Allowed question types for comparative report
$allowed_question_types = array('yesno', 'multiplechoice', 'multipleresponse', 'dropdown', 'percentage', 'score');
// Getting all the questions
$questions = SurveyManager::get_questions($_GET['survey_id']);
// Actions bar
echo '<div class="actions">';
echo '<a href="' . api_get_path(WEB_CODE_PATH) . 'survey/reporting.php?survey_id=' . intval($_GET['survey_id']) . '&' . api_get_cidreq() . '">' . Display::return_icon('back.png', get_lang('BackTo') . ' ' . get_lang('ReportingOverview'), '', ICON_SIZE_MEDIUM) . '</a>';
echo '</div>';
// Displaying an information message that only the questions with predefined answers can be used in a comparative report
Display::display_normal_message(get_lang('OnlyQuestionsWithPredefinedAnswers'), false);
// The form for selecting the axis of the table
echo '<form id="form1" name="form1" method="get" action="' . api_get_self() . '?action=' . Security::remove_XSS($_GET['action']) . '&survey_id=' . intval($_GET['survey_id']) . '&xaxis=' . Security::remove_XSS($_GET['xaxis']) . '&y=' . Security::remove_XSS($_GET['yaxis']) . '">';
// Survey_id
echo '<input type="hidden" name="action" value="' . Security::remove_XSS($_GET['action']) . '"/>';
echo '<input type="hidden" name="survey_id" value="' . intval($_GET['survey_id']) . '"/>';
// X axis
echo get_lang('SelectXAxis') . ': ';
echo '<select name="xaxis">';
echo '<option value="">---</option>';
foreach ($questions as $key => &$question) {
if (is_array($allowed_question_types)) {
if (in_array($question['type'], $allowed_question_types)) {
echo '<option value="' . $question['question_id'] . '"';
if (isset($_GET['xaxis']) && $_GET['xaxis'] == $question['question_id']) {
echo ' selected="selected"';
}
echo '">' . api_substr(strip_tags($question['question']), 0, 50) . '</option>';
}
}
}
echo '</select><br /><br />';
// Y axis
echo get_lang('SelectYAxis') . ': ';
echo '<select name="yaxis">';
echo '<option value="">---</option>';
foreach ($questions as $key => &$question) {
if (in_array($question['type'], $allowed_question_types)) {
echo '<option value="' . $question['question_id'] . '"';
if (isset($_GET['yaxis']) && $_GET['yaxis'] == $question['question_id']) {
echo ' selected="selected"';
}
echo '">' . api_substr(strip_tags($question['question']), 0, 50) . '</option>';
}
}
echo '</select><br /><br />';
echo '<button class="save" type="submit" name="Submit" value="Submit">' . get_lang('CompareQuestions') . '</button>';
echo '</form>';
// Getting all the information of the x axis
if (isset($_GET['xaxis']) && is_numeric($_GET['xaxis'])) {
$question_x = SurveyManager::get_question($_GET['xaxis']);
}
// Getting all the information of the y axis
if (isset($_GET['yaxis']) && is_numeric($_GET['yaxis'])) {
$question_y = SurveyManager::get_question($_GET['yaxis']);
}
if (isset($_GET['xaxis']) && is_numeric($_GET['xaxis']) && isset($_GET['yaxis']) && is_numeric($_GET['yaxis'])) {
// Getting the answers of the two questions
$answers_x = SurveyUtil::get_answers_of_question_by_user($_GET['survey_id'], $_GET['xaxis']);
$answers_y = SurveyUtil::get_answers_of_question_by_user($_GET['survey_id'], $_GET['yaxis']);
// Displaying the table
$tableHtml = '<table border="1" class="data_table">';
$xOptions = array();
// The header
$tableHtml .= ' <tr>';
for ($ii = 0; $ii <= count($question_x['answers']); $ii++) {
if ($ii == 0) {
$tableHtml .= ' <th> </th>';
} else {
if ($question_x['type'] == 'score') {
for ($x = 1; $x <= $question_x['maximum_score']; $x++) {
$tableHtml .= ' <th>' . $question_x['answers'][$ii - 1] . '<br />' . $x . '</th>';
}
$x = '';
} else {
$tableHtml .= ' <th>' . $question_x['answers'][$ii - 1] . '</th>';
}
$optionText = strip_tags($question_x['answers'][$ii - 1]);
$optionText = html_entity_decode($optionText);
array_push($xOptions, trim($optionText));
}
}
$tableHtml .= ' </tr>';
$chartData = array();
// The main part
for ($ij = 0; $ij < count($question_y['answers']); $ij++) {
$currentYQuestion = strip_tags($question_y['answers'][$ij]);
$currentYQuestion = html_entity_decode($currentYQuestion);
// The Y axis is a scoring question type so we have more rows than the options (actually options * maximum score)
if ($question_y['type'] == 'score') {
for ($y = 1; $y <= $question_y['maximum_score']; $y++) {
$tableHtml .= ' <tr>';
for ($ii = 0; $ii <= count($question_x['answers']); $ii++) {
if ($question_x['type'] == 'score') {
for ($x = 1; $x <= $question_x['maximum_score']; $x++) {
if ($ii == 0) {
$tableHtml .= ' <th>' . $question_y['answers'][$ij] . ' ' . $y . '</th>';
break;
} else {
$tableHtml .= ' <td align="center">';
$votes = SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij], $x, $y);
$tableHtml .= $votes;
array_push($chartData, array('serie' => array($currentYQuestion, $xOptions[$ii - 1]), 'option' => $x, 'votes' => $votes));
$tableHtml .= '</td>';
}
}
} else {
if ($ii == 0) {
$tableHtml .= '<th>' . $question_y['answers'][$ij] . ' ' . $y . '</th>';
} else {
$tableHtml .= '<td align="center">';
$votes = SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij], 0, $y);
$tableHtml .= $votes;
array_push($chartData, array('serie' => array($currentYQuestion, $xOptions[$ii - 1]), 'option' => $y, 'votes' => $votes));
$tableHtml .= '</td>';
}
}
}
$tableHtml .= ' </tr>';
}
} else {
$tableHtml .= ' <tr>';
for ($ii = 0; $ii <= count($question_x['answers']); $ii++) {
if ($question_x['type'] == 'score') {
for ($x = 1; $x <= $question_x['maximum_score']; $x++) {
if ($ii == 0) {
$tableHtml .= ' <th>' . $question_y['answers'][$ij] . '</th>';
break;
} else {
$tableHtml .= ' <td align="center">';
$votes = SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij], $x, 0);
$tableHtml .= $votes;
array_push($chartData, array('serie' => array($currentYQuestion, $xOptions[$ii - 1]), 'option' => $x, 'votes' => $votes));
$tableHtml .= '</td>';
}
}
} else {
if ($ii == 0) {
$tableHtml .= ' <th>' . $question_y['answers'][$ij] . '</th>';
} else {
$tableHtml .= ' <td align="center">';
$votes = SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij]);
$tableHtml .= $votes;
array_push($chartData, array('serie' => $xOptions[$ii - 1], 'option' => $currentYQuestion, 'votes' => $votes));
$tableHtml .= '</td>';
}
}
}
$tableHtml .= ' </tr>';
}
}
$tableHtml .= '</table>';
echo '<div id="chartContainer" class="col-md-12">';
echo self::drawChart($chartData, true);
echo '</div>';
echo $tableHtml;
}
}
/**
* Display a sortable table that contains an overview off all the progress of the user in a session
* @author César Perales <*****@*****.**>, Beeznest Team
*/
function display_survey_overview($sessionId = 0, $courseId = 0, $surveyId = 0, $date_from, $date_to)
{
/**
* Column name
* The order is important you need to check the $column variable in the model.ajax.php file
*/
$columns = array(get_lang('Username'), get_lang('FirstName'), get_lang('LastName'));
//add lessons of course
$questions = SurveyManager::get_questions($surveyId, $courseId);
foreach ($questions as $question) {
$columns[] = $question['question'];
}
/**
* Column config
*/
$column_model = array(array('name' => 'username', 'index' => 'username', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"), array('name' => 'firstname', 'index' => 'firstname', 'align' => 'left', 'search' => 'true'), array('name' => 'lastname', 'index' => 'lastname', 'align' => 'left', 'search' => 'true'));
//get dinamic column names
foreach ($questions as $question_id => $question) {
$column_model[] = array('name' => $question_id, 'index' => $question_id, 'width' => '70', 'align' => 'left', 'search' => 'true');
}
$action_links = '';
// jqgrid will use this URL to do the selects
$url = api_get_path(WEB_AJAX_PATH) . 'model.ajax.php?a=get_survey_overview&session_id=' . $sessionId . '&course_id=' . $courseId . '&survey_id=' . $surveyId . '&date_to=' . $date_to . '&date_from=' . $date_from;
// Table Id
$tableId = 'lpProgress';
//Autowidth
$extra_params['autowidth'] = 'true';
// height auto
$extra_params['height'] = 'auto';
$table = Display::grid_js($tableId, $url, $columns, $column_model, $extra_params, array(), $action_links, true);
$return = '<script>$(function() {' . $table . 'jQuery("#' . $tableId . '").jqGrid("navGrid","#' . $tableId . '_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
jQuery("#' . $tableId . '").jqGrid("navButtonAdd","#' . $tableId . '_pager",{
caption:"",
title:"' . get_lang('ExportExcel') . '",
onClickButton : function () {
jQuery("#' . $tableId . '").jqGrid("excelExport",{"url":"' . $url . '&export_format=xls"});
}
});
});</script>';
$return .= Display::grid_html($tableId);
return $return;
}
