/**
* Quite similar to display_complete_report(), returns an HTML string
* that can be used in a csv file
* @todo consider merging this function with display_complete_report
* @return string The contents of a csv file
* @author Patrick Cool <*****@*****.**>, Ghent University
* @version February 2007
*/
public static function export_complete_report($survey_data, $user_id = 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);
// The first column
$return = ';';
// Show extra fields blank space (enough for extra fields on next line)
$extra_user_fields = UserManager::get_extra_fields(0, 0, 5, 'ASC', false, true);
$num = count($extra_user_fields);
$return .= str_repeat(';', $num);
$course_id = api_get_course_int_id();
$sql = "SELECT\n questions.question_id,\n questions.type,\n questions.survey_question,\n count(options.question_option_id) as number_of_options\n\t\t\t\tFROM {$table_survey_question} questions\n LEFT JOIN {$table_survey_question_option} options\n\t\t\t\tON questions.question_id = options.question_id AND options.c_id = {$course_id}\n\t\t\t\tWHERE\n\t\t\t\t questions.survey_id = '" . intval($_GET['survey_id']) . "' AND\n questions.c_id = {$course_id}\n\t\t\t\tGROUP BY questions.question_id\n\t\t\t\tORDER BY questions.sort ASC";
$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'] || 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') {
if ($row['number_of_options'] == 0 && $row['type'] == 'open') {
$return .= str_replace("\r\n", ' ', api_html_entity_decode(strip_tags($row['survey_question']), ENT_QUOTES)) . ';';
} else {
for ($ii = 0; $ii < $row['number_of_options']; $ii++) {
$return .= str_replace("\r\n", ' ', api_html_entity_decode(strip_tags($row['survey_question']), ENT_QUOTES)) . ';';
}
}
}
}
}
$return .= "\n";
// Getting all the questions and options
$return .= ';';
// Show the fields names for user fields
if (!empty($extra_user_fields)) {
foreach ($extra_user_fields as &$field) {
$return .= '"' . str_replace("\r\n", ' ', api_html_entity_decode(strip_tags($field[3]), ENT_QUOTES)) . '";';
}
}
$sql = "SELECT\n\t\t survey_question.question_id,\n\t\t survey_question.survey_id,\n\t\t survey_question.survey_question,\n\t\t survey_question.display,\n\t\t survey_question.sort,\n\t\t survey_question.type,\n survey_question_option.question_option_id,\n survey_question_option.option_text,\n survey_question_option.sort as option_sort\n\t\t\t\tFROM {$table_survey_question} survey_question\n\t\t\t\tLEFT JOIN {$table_survey_question_option} survey_question_option\n\t\t\t\tON\n\t\t\t\t survey_question.question_id = survey_question_option.question_id AND\n\t\t\t\t survey_question_option.c_id = {$course_id}\n\t\t\t\tWHERE\n\t\t\t\t survey_question.survey_id = '" . intval($_GET['survey_id']) . "' AND\n\t\t\t\t survey_question.c_id = {$course_id}\n\t\t\t\tORDER BY survey_question.sort ASC, survey_question_option.sort ASC";
$result = Database::query($sql);
$possible_answers = array();
$possible_answers_type = array();
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 quesiton filter but the question is selected for display
if (!$_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') {
$row['option_text'] = str_replace(array("\r", "\n"), array('', ''), $row['option_text']);
$return .= api_html_entity_decode(strip_tags($row['option_text']), ENT_QUOTES) . ';';
$possible_answers[$row['question_id']][$row['question_option_id']] = $row['question_option_id'];
$possible_answers_type[$row['question_id']] = $row['type'];
}
}
}
$return .= "\n";
// Getting all the answers of the users
$old_user = '';
$answers_of_user = array();
$sql = "SELECT * FROM {$table_survey_answer}\n\t\t WHERE c_id = {$course_id} AND survey_id='" . Database::escape_string($_GET['survey_id']) . "'";
if ($user_id != 0) {
$sql .= "AND user='******' ";
}
$sql .= "ORDER BY user ASC";
$open_question_iterator = 1;
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
if ($old_user != $row['user'] && $old_user != '') {
$return .= SurveyUtil::export_complete_report_row($survey_data, $possible_answers, $answers_of_user, $old_user, true);
$answers_of_user = array();
}
if ($possible_answers_type[$row['question_id']] == 'open') {
$temp_id = 'open' . $open_question_iterator;
$answers_of_user[$row['question_id']][$temp_id] = $row;
$open_question_iterator++;
} else {
$answers_of_user[$row['question_id']][$row['option_id']] = $row;
}
$old_user = $row['user'];
}
// This is to display the last user
$return .= SurveyUtil::export_complete_report_row($survey_data, $possible_answers, $answers_of_user, $old_user, true);
return $return;
}
