/** * 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; }