/**
* 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
*/
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 = survey_manager::get_questions($_GET['survey_id']);
// Actions bar
echo '<div class="actions">';
echo '<a href="reporting.php?survey_id=' . Security::remove_XSS($_GET['survey_id']) . '">' . 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=' . Security::remove_XSS($_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="' . Security::remove_XSS($_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['xaxis']) && $_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 = survey_manager::get_question($_GET['xaxis']);
}
// Getting all the information of the y axis
if (isset($_GET['yaxis']) && is_numeric($_GET['yaxis'])) {
$question_y = survey_manager::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
echo '<table border="1" class="data_table">';
// The header
echo ' <tr>';
for ($ii = 0; $ii <= count($question_x['answers']); $ii++) {
if ($ii == 0) {
echo ' <th> </th>';
} else {
if ($question_x['type'] == 'score') {
for ($x = 1; $x <= $question_x['maximum_score']; $x++) {
echo ' <th>' . $question_x['answers'][$ii - 1] . '<br />' . $x . '</th>';
}
$x = '';
} else {
echo ' <th>' . $question_x['answers'][$ii - 1] . '</th>';
}
}
}
echo ' </tr>';
// The main part
for ($ij = 0; $ij < count($question_y['answers']); $ij++) {
// 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++) {
echo ' <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) {
echo ' <th>' . $question_y['answers'][$ij] . ' ' . $y . '</th>';
break;
} else {
echo ' <td align="center">';
echo SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij], $x, $y);
echo '</td>';
}
}
} else {
if ($ii == 0) {
echo ' <th>' . $question_y['answers'][$ij] . ' ' . $y . '</th>';
} else {
echo ' <td align="center">';
echo SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij], 0, $y);
echo '</td>';
}
}
}
echo ' </tr>';
}
} else {
echo ' <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) {
echo ' <th>' . $question_y['answers'][$ij] . '</th>';
break;
} else {
echo ' <td align="center">';
echo SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij], $x, 0);
echo '</td>';
}
}
} else {
if ($ii == 0) {
echo ' <th>' . $question_y['answers'][$ij] . '</th>';
} else {
echo ' <td align="center">';
echo SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij]);
echo '</td>';
}
}
}
echo ' </tr>';
}
}
echo '</table>';
}
}
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 = survey_manager::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, 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 course_code = '%s' AND s.status <> 2 AND id_session = %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'];
}
/* $where_survey = '';
if (!empty($date_to) && !empty($date_from)) {
$where_survey = sprintf(" AND s.avail_from >= '%s'
AND s.avail_till <= '%s'", $date_from, $date_to);
} */
$sql = "SELECT u.user_id, u.lastname, u.firstname, u.username, u.email, s.course_code
FROM $session_course_user s
INNER JOIN $user u ON u.user_id = s.id_user
$where $order $limit";
$sql_query = sprintf($sql, Database::escape_string($course['code']), $sessionId);
$rs = Database::query($sql_query);
while ($user = Database::fetch_array($rs)) {
$users[$user['user_id']] = $user;
}
//Get survey questions
$questions = survey_manager::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
FROM $c_survey_answer sa
INNER JOIN $c_survey_question sq
ON sq.question_id = sa.question_id
LEFT JOIN $c_survey_question_option sqo
ON
sqo.c_id = sa.c_id AND
sqo.question_id = sq.question_id AND
sqo.question_option_id = sa.option_id AND
sqo.survey_id = sq.survey_id
WHERE
sa.survey_id = %d AND
sa.c_id = %d AND
sa.user = %d
"; //. $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;
}
/**
* 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 = survey_manager::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;
}
