/**
* This functions displays the complete reporting
* @return string HTML code
* @todo open questions are not in the complete report yet.
* @author Patrick Cool <*****@*****.**>, Ghent University
* @version February 2007
*/
public static function display_complete_report($survey_data)
{
// 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);
// 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 '<a class="survey_export_link" href="javascript: void(0);" onclick="document.form1a.submit();">
' . Display::return_icon('export_csv.png', get_lang('ExportAsCSV'), '', ICON_SIZE_MEDIUM) . '</a>';
echo '<a class="survey_export_link" href="javascript: void(0);" onclick="document.form1b.submit();">
' . Display::return_icon('export_excel.png', get_lang('ExportAsXLS'), '', ICON_SIZE_MEDIUM) . '</a>';
echo '</div>';
// The form
echo '<form id="form1a" name="form1a" method="post" action="' . api_get_self() . '?action=' . Security::remove_XSS($_GET['action']) . '&survey_id=' . intval($_GET['survey_id']) . '&' . api_get_cidreq() . '">';
echo '<input type="hidden" name="export_report" value="export_report">';
echo '<input type="hidden" name="export_format" value="csv">';
echo '</form>';
echo '<form id="form1b" name="form1b" method="post" action="' . api_get_self() . '?action=' . Security::remove_XSS($_GET['action']) . '&survey_id=' . intval($_GET['survey_id']) . '&' . api_get_cidreq() . '">';
echo '<input type="hidden" name="export_report" value="export_report">';
echo '<input type="hidden" name="export_format" value="xls">';
echo '</form>';
echo '<form id="form2" name="form2" method="post" action="' . api_get_self() . '?action=' . Security::remove_XSS($_GET['action']) . '&survey_id=' . intval($_GET['survey_id']) . '&' . api_get_cidreq() . '">';
// The table
echo '<br /><table class="data_table" border="1">';
// Getting the number of options per question
echo ' <tr>';
echo ' <th>';
if (isset($_POST['submit_question_filter']) && $_POST['submit_question_filter'] || isset($_POST['export_report']) && $_POST['export_report']) {
echo '<button class="cancel" type="submit" name="reset_question_filter" value="' . get_lang('ResetQuestionFilter') . '">' . get_lang('ResetQuestionFilter') . '</button>';
}
echo '<button class="save" type="submit" name="submit_question_filter" value="' . get_lang('SubmitQuestionFilter') . '">' . get_lang('SubmitQuestionFilter') . '</button>';
echo '</th>';
$display_extra_user_fields = false;
if (!(isset($_POST['submit_question_filter']) && $_POST['submit_question_filter'] || isset($_POST['export_report']) && $_POST['export_report']) || !empty($_POST['fields_filter'])) {
// Show user fields section with a big th colspan that spans over all fields
$extra_user_fields = UserManager::get_extra_fields(0, 0, 5, 'ASC', false, true);
$num = count($extra_user_fields);
if ($num > 0) {
echo '<th ' . ($num > 0 ? ' colspan="' . $num . '"' : '') . '>';
echo '<label><input type="checkbox" name="fields_filter" value="1" checked="checked"/> ';
echo get_lang('UserFields');
echo '</label>';
echo '</th>';
$display_extra_user_fields = true;
}
}
$course_id = api_get_course_int_id();
// Get all the questions ordered by the "sort" column
// <hub> modify the query to display open questions too
// $sql = "SELECT q.question_id, q.type, q.survey_question, count(o.question_option_id) as number_of_options
// FROM $table_survey_question q LEFT JOIN $table_survey_question_option o
// ON q.question_id = o.question_id
// WHERE q.question_id = o.question_id
// AND q.survey_id = '".Database::escape_string($_GET['survey_id'])."'
// GROUP BY q.question_id
// ORDER BY q.sort ASC";
$sql = "SELECT q.question_id, q.type, q.survey_question, count(o.question_option_id) as number_of_options\n\t\t\t\tFROM {$table_survey_question} q LEFT JOIN {$table_survey_question_option} o\n\t\t\t\tON q.question_id = o.question_id\n\t\t\t\tWHERE q.survey_id = '" . Database::escape_string($_GET['survey_id']) . "' AND\n\t\t\t\tq.c_id = {$course_id} AND\n\t\t\t\to.c_id = {$course_id}\n\t\t\t\tGROUP BY q.question_id\n\t\t\t\tORDER BY q.sort ASC";
// </hub>
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
// We show the questions if
// 1. there is no question filter and the export button has not been clicked
// 2. there is a quesiton filter but the question is selected for display
//if (!($_POST['submit_question_filter'] || $_POST['export_report']) || in_array($row['question_id'], $_POST['questions_filter'])) {
if (!(isset($_POST['submit_question_filter']) && $_POST['submit_question_filter']) || is_array($_POST['questions_filter']) && in_array($row['question_id'], $_POST['questions_filter'])) {
// We do not show comment and pagebreak question types
if ($row['type'] != 'comment' && $row['type'] != 'pagebreak') {
echo ' <th';
// <hub> modified tst to include percentage
if ($row['number_of_options'] > 0 && $row['type'] != 'percentage') {
// </hub>
echo ' colspan="' . $row['number_of_options'] . '"';
}
echo '>';
echo '<label><input type="checkbox" name="questions_filter[]" value="' . $row['question_id'] . '" checked="checked"/> ';
echo $row['survey_question'];
echo '</label>';
echo '</th>';
}
// No column at all if it's not a question
}
$questions[$row['question_id']] = $row;
}
echo ' </tr>';
// Getting all the questions and options
echo ' <tr>';
echo ' <th> </th>';
// the user column
if (!(isset($_POST['submit_question_filter']) && $_POST['submit_question_filter'] || isset($_POST['export_report']) && $_POST['export_report']) || !empty($_POST['fields_filter'])) {
//show the fields names for user fields
foreach ($extra_user_fields as &$field) {
echo '<th>' . $field[3] . '</th>';
}
}
// cells with option (none for open question)
$sql = "SELECT \tsq.question_id, sq.survey_id,\n\t\t\t\t\t\tsq.survey_question, sq.display,\n\t\t\t\t\t\tsq.sort, sq.type, sqo.question_option_id,\n\t\t\t\t\t\tsqo.option_text, sqo.sort as option_sort\n\t\t\t\tFROM {$table_survey_question} sq\n\t\t\t\tLEFT JOIN {$table_survey_question_option} sqo\n\t\t\t\tON sq.question_id = sqo.question_id\n\t\t\t\tWHERE\n\t\t\t\t sq.survey_id = '" . Database::escape_string($_GET['survey_id']) . "' AND\n sq.c_id = {$course_id} AND\n sqo.c_id = {$course_id}\n\t\t\t\tORDER BY sq.sort ASC, sqo.sort ASC";
$result = Database::query($sql);
$display_percentage_header = 1;
// in order to display only once the cell option (and not 100 times)
while ($row = Database::fetch_array($result)) {
// We show the options if
// 1. there is no question filter and the export button has not been clicked
// 2. there is a question filter but the question is selected for display
//if (!($_POST['submit_question_filter'] || $_POST['export_report']) || in_array($row['question_id'], $_POST['questions_filter'])) {
if (!(isset($_POST['submit_question_filter']) && $_POST['submit_question_filter']) || is_array($_POST['questions_filter']) && in_array($row['question_id'], $_POST['questions_filter'])) {
// <hub> modif 05-05-2010
// we do not show comment and pagebreak question types
if ($row['type'] == 'open') {
echo '<th> - </th>';
$possible_answers[$row['question_id']][$row['question_option_id']] = $row['question_option_id'];
$display_percentage_header = 1;
} else {
if ($row['type'] == 'percentage' && $display_percentage_header) {
echo '<th> % </th>';
$possible_answers[$row['question_id']][$row['question_option_id']] = $row['question_option_id'];
$display_percentage_header = 0;
} else {
if ($row['type'] == 'percentage') {
$possible_answers[$row['question_id']][$row['question_option_id']] = $row['question_option_id'];
} else {
if ($row['type'] != 'comment' and $row['type'] != 'pagebreak' and $row['type'] != 'percentage') {
echo '<th>';
echo $row['option_text'];
echo '</th>';
$possible_answers[$row['question_id']][$row['question_option_id']] = $row['question_option_id'];
$display_percentage_header = 1;
}
}
}
}
//no column at all if the question was not a question
// </hub>
}
}
echo ' </tr>';
// Getting all the answers of the users
$old_user = '';
$answers_of_user = array();
$sql = "SELECT * FROM {$table_survey_answer}\n WHERE\n c_id = {$course_id} AND\n survey_id='" . intval($_GET['survey_id']) . "'\n ORDER BY user ASC";
$result = Database::query($sql);
$i = 1;
while ($row = Database::fetch_array($result)) {
if ($old_user != $row['user'] && $old_user != '') {
$userParam = $old_user;
if ($survey_data['anonymous'] != 0) {
$userParam = $i;
$i++;
}
SurveyUtil::display_complete_report_row($survey_data, $possible_answers, $answers_of_user, $userParam, $questions, $display_extra_user_fields);
$answers_of_user = array();
}
if (isset($questions[$row['question_id']]) && $questions[$row['question_id']]['type'] != 'open') {
$answers_of_user[$row['question_id']][$row['option_id']] = $row;
} else {
$answers_of_user[$row['question_id']][0] = $row;
}
$old_user = $row['user'];
}
$userParam = $old_user;
if ($survey_data['anonymous'] != 0) {
$userParam = $i;
$i++;
}
SurveyUtil::display_complete_report_row($survey_data, $possible_answers, $answers_of_user, $userParam, $questions, $display_extra_user_fields);
// This is to display the last user
echo '</table>';
echo '</form>';
}