/**
* This function displays the report by question.
*
* It displays a table with all the options of the question and the number of users who have answered positively on the option.
* The number of users who answered positive on a given option is expressed in an absolute number, in a percentage of the total
* and graphically using bars
* By clicking on the absolute number you get a list with the persons who have answered this.
* You can then click on the name of the person and you will then go to the report by user where you see all the
* answers of that user.
*
* @param array All the survey data
* @return string html code that displays the report by question
* @todo allow switching between horizontal and vertical.
* @todo multiple response: percentage are probably not OK
* @todo the question and option text have to be shortened and should expand when the user clicks on it.
* @todo the pagebreak and comment question types should not be shown => removed from $survey_data before
* @author Patrick Cool <*****@*****.**>, Ghent University
* @version February 2007 - Updated March 2008
*/
public static function display_question_report($survey_data)
{
$singlePage = isset($_GET['single_page']) ? intval($_GET['single_page']) : 0;
$course_id = api_get_course_int_id();
// Database table definitions
$table_survey_question = Database::get_course_table(TABLE_SURVEY_QUESTION);
$table_survey_question_option = Database::get_course_table(TABLE_SURVEY_QUESTION_OPTION);
$table_survey_answer = Database::get_course_table(TABLE_SURVEY_ANSWER);
// Determining the offset of the sql statement (the n-th question of the survey)
$offset = !isset($_GET['question']) ? 0 : intval($_GET['question']);
$currentQuestion = isset($_GET['question']) ? intval($_GET['question']) : 0;
$questions = array();
$surveyId = intval($_GET['survey_id']);
$action = Security::remove_XSS($_GET['action']);
echo '<div class="actions">';
echo '<a href="' . api_get_path(WEB_CODE_PATH) . 'survey/reporting.php?survey_id=' . $surveyId . '&' . api_get_cidreq() . '">' . Display::return_icon('back.png', get_lang('BackTo') . ' ' . get_lang('ReportingOverview'), '', ICON_SIZE_MEDIUM) . '</a>';
echo '</div>';
if ($survey_data['number_of_questions'] > 0) {
$limitStatement = null;
if (!$singlePage) {
echo '<div id="question_report_questionnumbers" class="pagination">';
/* echo '<ul><li class="disabled"><a href="#">'.get_lang('Question').'</a></li>'; */
if ($currentQuestion != 0) {
echo '<li><a href="' . api_get_path(WEB_CODE_PATH) . 'survey/reporting.php?action=' . $action . '&' . api_get_cidreq() . '&survey_id=' . $surveyId . '&question=' . ($offset - 1) . '">' . get_lang('PreviousQuestion') . '</a></li>';
}
for ($i = 1; $i <= $survey_data['number_of_questions']; $i++) {
if ($offset != $i - 1) {
echo '<li><a href="' . api_get_path(WEB_CODE_PATH) . 'survey/reporting.php?action=' . $action . '&' . api_get_cidreq() . '&survey_id=' . $surveyId . '&question=' . ($i - 1) . '">' . $i . '</a></li>';
} else {
echo '<li class="disabled"s><a href="#">' . $i . '</a></li>';
}
/*if ($i < $survey_data['number_of_questions']) {
echo ' | ';
}*/
}
if ($currentQuestion < $survey_data['number_of_questions'] - 1) {
echo '<li><a href="' . api_get_path(WEB_CODE_PATH) . 'survey/reporting.php?action=' . $action . '&' . api_get_cidreq() . '&survey_id=' . $surveyId . '&question=' . ($offset + 1) . '">' . get_lang('NextQuestion') . '</li></a>';
}
echo '</ul>';
echo '</div>';
$limitStatement = " LIMIT {$offset}, 1";
}
// Getting the question information
$sql = "SELECT * FROM {$table_survey_question}\n\t\t\t WHERE\n\t\t\t c_id = {$course_id} AND\n survey_id='" . Database::escape_string($_GET['survey_id']) . "' AND\n type<>'pagebreak' AND type<>'comment'\n ORDER BY sort ASC\n {$limitStatement}";
$result = Database::query($sql);
//$question = Database::fetch_array($result);
while ($row = Database::fetch_array($result)) {
$questions[$row['question_id']] = $row;
}
// Navigate through the questions (next and previous)
/*if ($currentQuestion != 0 ) {
echo '<a href="'.api_get_path(WEB_CODE_PATH).'survey/reporting.php?action='.Security::remove_XSS($_GET['action']).'&'.api_get_cidreq().'&survey_id='.Security::remove_XSS($_GET['survey_id']).'&question='.Security::remove_XSS($offset-1).'">'.
Display::return_icon('action_prev.png', get_lang('PreviousQuestion'), array('align' => 'middle')).' '.get_lang('PreviousQuestion').'</a> ';
} else {
echo Display::return_icon('action_prev.png', get_lang('PreviousQuestion'), array('align' => 'middle')).' '.get_lang('PreviousQuestion').' ';
}
echo ' | ';
if ($currentQuestion < ($survey_data['number_of_questions'] - 1)) {
echo '<a href="'.api_get_path(WEB_CODE_PATH).'survey/reporting.php?action='.Security::remove_XSS($_GET['action']).'&'.api_get_cidreq().'&survey_id='.Security::remove_XSS($_GET['survey_id']).'&question='.Security::remove_XSS($offset+1).'">'.get_lang('NextQuestion').' '.Display::return_icon('action_next.png', get_lang('NextQuestion'), array('align' => 'middle')).'</a>';
} else {
echo get_lang('NextQuestion').' '.Display::return_icon('action_next.png', get_lang('NextQuestion'), array('align' => 'middle'));
}*/
}
foreach ($questions as $question) {
$chartData = array();
$options = array();
echo '<div class="title-question">';
echo strip_tags(isset($question['survey_question']) ? $question['survey_question'] : null);
echo '</div>';
if ($question['type'] == 'score') {
/** @todo This function should return the options as this is needed further in the code */
$options = SurveyUtil::display_question_report_score($survey_data, $question, $offset);
} elseif ($question['type'] == 'open') {
/** @todo Also get the user who has answered this */
$sql = "SELECT * FROM {$table_survey_answer}\n WHERE\n c_id = {$course_id} AND\n survey_id='" . intval($_GET['survey_id']) . "' AND\n question_id = '" . intval($question['question_id']) . "'";
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
echo $row['option_id'] . '<hr noshade="noshade" size="1" />';
}
} else {
// Getting the options ORDER BY sort ASC
$sql = "SELECT * FROM {$table_survey_question_option}\n WHERE\n c_id = {$course_id} AND\n survey_id='" . intval($_GET['survey_id']) . "'\n AND question_id = '" . intval($question['question_id']) . "'\n ORDER BY sort ASC";
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
$options[$row['question_option_id']] = $row;
}
// Getting the answers
$sql = "SELECT *, count(answer_id) as total FROM {$table_survey_answer}\n WHERE\n c_id = {$course_id} AND\n survey_id='" . intval($_GET['survey_id']) . "'\n AND question_id = '" . intval($question['question_id']) . "'\n GROUP BY option_id, value";
$result = Database::query($sql);
$number_of_answers = array();
$data = array();
while ($row = Database::fetch_array($result)) {
if (!isset($number_of_answers[$row['question_id']])) {
$number_of_answers[$row['question_id']] = 0;
}
$number_of_answers[$row['question_id']] += $row['total'];
$data[$row['option_id']] = $row;
}
foreach ($options as $option) {
$optionText = strip_tags($option['option_text']);
$optionText = html_entity_decode($optionText);
$votes = isset($data[$option['question_option_id']]['total']) ? $data[$option['question_option_id']]['total'] : '0';
array_push($chartData, array('option' => $optionText, 'votes' => $votes));
}
$chartContainerId = 'chartContainer' . $question['question_id'];
echo '<div id="' . $chartContainerId . '" class="col-md-12">';
echo self::drawChart($chartData, false, $chartContainerId);
// displaying the table: headers
echo '<table class="display-survey table">';
echo ' <tr>';
echo ' <th> </th>';
echo ' <th>' . get_lang('AbsoluteTotal') . '</th>';
echo ' <th>' . get_lang('Percentage') . '</th>';
echo ' <th>' . get_lang('VisualRepresentation') . '</th>';
echo ' <tr>';
// Displaying the table: the content
if (is_array($options)) {
foreach ($options as $key => &$value) {
$absolute_number = null;
if (isset($data[$value['question_option_id']])) {
$absolute_number = $data[$value['question_option_id']]['total'];
}
if ($question['type'] == 'percentage' && empty($absolute_number)) {
continue;
}
if ($number_of_answers[$option['question_id']] == 0) {
$answers_number = 0;
} else {
$answers_number = $absolute_number / $number_of_answers[$option['question_id']] * 100;
}
echo ' <tr>';
echo ' <td class="center">' . $value['option_text'] . '</td>';
echo ' <td class="center">';
if ($absolute_number != 0) {
echo '<a href="' . api_get_path(WEB_CODE_PATH) . 'survey/reporting.php?action=' . $action . '&survey_id=' . $surveyId . '&question=' . $offset . '&viewoption=' . $value['question_option_id'] . '">' . $absolute_number . '</a>';
} else {
echo '0';
}
echo ' </td>';
echo ' <td class="center">' . round($answers_number, 2) . ' %</td>';
echo ' <td class="center">';
$size = $answers_number * 2;
if ($size > 0) {
echo '<div style="border:1px solid #264269; background-color:#aecaf4; height:10px; width:' . $size . 'px"> </div>';
} else {
echo '<div style="text-align: left;">' . get_lang("NoDataAvailable") . '</div>';
}
echo ' </td>';
echo ' </tr>';
}
}
// displaying the table: footer (totals)
echo ' <tr>';
echo ' <td class="total"><b>' . get_lang('Total') . '</b></td>';
echo ' <td class="total"><b>' . ($number_of_answers[$option['question_id']] == 0 ? '0' : $number_of_answers[$option['question_id']]) . '</b></td>';
echo ' <td class="total"> </td>';
echo ' <td class="total"> </td>';
echo ' </tr>';
echo '</table>';
echo '</div>';
}
}
if (isset($_GET['viewoption'])) {
echo '<div class="answered-people">';
echo '<h4>' . get_lang('PeopleWhoAnswered') . ': ' . strip_tags($options[Security::remove_XSS($_GET['viewoption'])]['option_text']) . '</h4>';
if (is_numeric($_GET['value'])) {
$sql_restriction = "AND value='" . Database::escape_string($_GET['value']) . "'";
}
$sql = "SELECT user FROM {$table_survey_answer}\n WHERE\n c_id = {$course_id} AND\n option_id = '" . Database::escape_string($_GET['viewoption']) . "'\n {$sql_restriction}";
$result = Database::query($sql);
echo '<ul>';
while ($row = Database::fetch_array($result)) {
$user_info = api_get_user_info($row['user']);
echo '<li><a href="' . api_get_path(WEB_CODE_PATH) . 'survey/reporting.php?action=userreport&survey_id=' . $surveyId . '&user='******'user'] . '">' . $user_info['complete_name'] . '</a></li>';
}
echo '</ul>';
echo '</div>';
}
}
/**
* This function displays the report by question.
*
* It displays a table with all the options of the question and the number of users who have answered positively on the option.
* The number of users who answered positive on a given option is expressed in an absolute number, in a percentage of the total
* and graphically using bars
* By clicking on the absolute number you get a list with the persons who have answered this.
* You can then click on the name of the person and you will then go to the report by user where you see all the
* answers of that user.
*
* @param array All the survey data
* @return string html code that displays the report by question
* @todo allow switching between horizontal and vertical.
* @todo multiple response: percentage are probably not OK
* @todo the question and option text have to be shortened and should expand when the user clicks on it.
* @todo the pagebreak and comment question types should not be shown => removed from $survey_data before
* @author Patrick Cool <*****@*****.**>, Ghent University
* @version February 2007 - Updated March 2008
*/
static function display_question_report($survey_data)
{
$course_id = api_get_course_int_id();
$data = array();
$number_of_answers = 0;
// Database table definitions
$table_survey_question = Database::get_course_table(TABLE_SURVEY_QUESTION);
$table_survey_question_option = Database::get_course_table(TABLE_SURVEY_QUESTION_OPTION);
$table_survey_answer = Database::get_course_table(TABLE_SURVEY_ANSWER);
// Determining the offset of the sql statement (the n-th question of the survey)
if (!isset($_GET['question'])) {
$offset = 0;
} else {
$offset = Database::escape_string($_GET['question']);
}
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>';
if ($survey_data['number_of_questions'] > 0) {
echo '<div id="question_report_questionnumbers">' . get_lang('GoToQuestion') . ': ';
for ($i = 1; $i <= $survey_data['number_of_questions']; $i++) {
if ($offset != $i - 1) {
echo '<a href="reporting.php?action=questionreport&survey_id=' . Security::remove_XSS($_GET['survey_id']) . '&question=' . ($i - 1) . '">' . $i . '</a>';
} else {
echo $i;
}
if ($i < $survey_data['number_of_questions']) {
echo ' | ';
}
}
echo '</div>';
// Getting the question information
$sql = "SELECT * FROM {$table_survey_question} WHERE c_id = {$course_id} AND survey_id='" . Database::escape_string($_GET['survey_id']) . "' AND type<>'pagebreak' AND type<>'comment' ORDER BY sort ASC LIMIT " . $offset . ",1";
$result = Database::query($sql);
$question = Database::fetch_array($result);
// Navigate through the questions (next and previous)
if ($_GET['question'] != 0) {
echo '<a href="reporting.php?action=' . Security::remove_XSS($_GET['action']) . '&survey_id=' . $_GET['survey_id'] . '&question=' . Security::remove_XSS($offset - 1) . '">' . Display::return_icon('action_prev.png', get_lang('PreviousQuestion'), array('align' => 'middle')) . ' ' . get_lang('PreviousQuestion') . '</a> ';
} else {
echo Display::return_icon('action_prev.png', get_lang('PreviousQuestion'), array('align' => 'middle')) . ' ' . get_lang('PreviousQuestion') . ' ';
}
echo ' | ';
if ($_GET['question'] < $survey_data['number_of_questions'] - 1) {
echo '<a href="reporting.php?action=' . Security::remove_XSS($_GET['action']) . '&survey_id=' . Security::remove_XSS($_GET['survey_id']) . '&question=' . Security::remove_XSS($offset + 1) . '">' . get_lang('NextQuestion') . ' ' . Display::return_icon('action_next.png', get_lang('NextQuestion'), array('align' => 'middle')) . '</a>';
} else {
echo get_lang('NextQuestion') . ' ' . Display::return_icon('action_next.png', get_lang('NextQuestion'), array('align' => 'middle'));
}
}
echo $question['survey_question'];
if ($question['type'] == 'score') {
/** @todo This function should return the options as this is needed further in the code */
$options = SurveyUtil::display_question_report_score($survey_data, $question, $offset);
} elseif ($question['type'] == 'open') {
/** @todo Also get the user who has answered this */
$sql = "SELECT * FROM {$table_survey_answer}\n WHERE c_id = {$course_id} AND\n survey_id='" . Database::escape_string($_GET['survey_id']) . "' AND\n question_id = '" . Database::escape_string($question['question_id']) . "'";
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
echo $row['option_id'] . '<hr noshade="noshade" size="1" />';
}
} else {
// Getting the options
$sql = "SELECT * FROM {$table_survey_question_option}\n\t\t\t\t\t\tWHERE c_id = {$course_id} AND survey_id='" . Database::escape_string($_GET['survey_id']) . "'\n\t\t\t\t\t\tAND question_id = '" . Database::escape_string($question['question_id']) . "'\n\t\t\t\t\t\tORDER BY sort ASC";
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
$options[$row['question_option_id']] = $row;
}
// Getting the answers
$sql = "SELECT *, count(answer_id) as total FROM {$table_survey_answer}\n\t\t\t\t\t\tWHERE c_id = {$course_id} AND\n\t\t\t\t\t\t survey_id='" . Database::escape_string($_GET['survey_id']) . "' AND\n\t\t\t\t\t\t question_id = '" . Database::escape_string($question['question_id']) . "'\n\t\t\t\t\t\tGROUP BY option_id, value";
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
$number_of_answers += $row['total'];
$data[$row['option_id']] = $row;
}
// displaying the table: headers
echo '<table class="data_table">';
echo ' <tr>';
echo ' <th> </th>';
echo ' <th>' . get_lang('AbsoluteTotal') . '</th>';
echo ' <th>' . get_lang('Percentage') . '</th>';
echo ' <th>' . get_lang('VisualRepresentation') . '</th>';
echo ' <tr>';
// Displaying the table: the content
if (is_array($options)) {
foreach ($options as $key => &$value) {
$absolute_number = $data[$value['question_option_id']]['total'];
if ($question['type'] == 'percentage' && empty($absolute_number)) {
continue;
}
if ($number_of_answers == 0) {
$answers_number = 0;
} else {
$answers_number = $absolute_number / $number_of_answers * 100;
}
echo ' <tr>';
echo ' <td>' . $value['option_text'] . '</td>';
echo ' <td align="right"><a href="reporting.php?action=' . Security::remove_XSS($_GET['action']) . '&survey_id=' . Security::remove_XSS($_GET['survey_id']) . '&question=' . Security::remove_XSS($offset) . '&viewoption=' . $value['question_option_id'] . '">' . $absolute_number . '</a></td>';
echo ' <td align="right">' . round($answers_number, 2) . ' %</td>';
echo ' <td align="right">';
$size = $answers_number * 2;
if ($size > 0) {
echo '<div style="border:1px solid #264269; background-color:#aecaf4; height:10px; width:' . $size . 'px"> </div>';
}
echo ' </td>';
echo ' </tr>';
}
}
// displaying the table: footer (totals)
echo ' <tr>';
echo ' <td style="border-top:1px solid black;"><b>' . get_lang('Total') . '</b></td>';
echo ' <td style="border-top:1px solid black;" align="right"><b>' . ($number_of_answers == 0 ? '0' : $number_of_answers) . '</b></td>';
echo ' <td style="border-top:1px solid black;"> </td>';
echo ' <td style="border-top:1px solid black;"> </td>';
echo ' </tr>';
echo '</table>';
}
if (isset($_GET['viewoption'])) {
echo get_lang('PeopleWhoAnswered') . ': ' . $options[Security::remove_XSS($_GET['viewoption'])]['option_text'] . '<br />';
if (is_numeric($_GET['value'])) {
$sql_restriction = "AND value='" . Database::escape_string($_GET['value']) . "'";
}
$sql = "SELECT user FROM {$table_survey_answer} WHERE c_id = {$course_id} AND option_id = '" . Database::escape_string($_GET['viewoption']) . "' {$sql_restriction}";
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
$user_info = api_get_user_info($row['user']);
echo '<a href="reporting.php?action=userreport&survey_id=' . Security::remove_XSS($_GET['survey_id']) . '&user='******'user'] . '">' . $user_info['complete_name'] . '</a><br />';
}
}
}
