/**
* 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
*/
static function export_complete_report_xls($survey_data, $filename, $user_id = 0)
{
$spreadsheet = new PHPExcel();
$spreadsheet->setActiveSheetIndex(0);
$worksheet = $spreadsheet->getActiveSheet();
$line = 0;
$column = 1;
// Skip the first column (row titles)
// Show extra fields blank space (enough for extra fields on next line)
//if (!empty($_REQUEST['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);
for ($i = 0; $i < $num; $i++) {
$worksheet->SetCellValueByColumnAndRow($line, $column, '');
$column++;
}
$display_extra_user_fields = true;
//}
// 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);
$course_id = api_get_course_int_id();
// First line (questions)
$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\t\t\t\tLEFT JOIN {$table_survey_question_option} options\n ON 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\t\t\t\t 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') {
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($row['survey_question']), ENT_QUOTES));
$column++;
} else {
for ($ii = 0; $ii < $row['number_of_options']; $ii++) {
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($row['survey_question']), ENT_QUOTES));
$column++;
}
}
}
}
}
$line++;
$column = 1;
// Show extra field values
if ($display_extra_user_fields) {
// Show the fields names for user fields
foreach ($extra_user_fields as &$field) {
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($field[3]), ENT_QUOTES));
$column++;
}
}
// Getting all the questions and options (second line)
$sql = "SELECT\n survey_question.question_id, survey_question.survey_id, survey_question.survey_question, survey_question.display, survey_question.sort, survey_question.type,\n survey_question_option.question_option_id, survey_question_option.option_text, 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 survey_question.question_id = survey_question_option.question_id AND survey_question_option.c_id = {$course_id}\n\t\t\t\tWHERE survey_question.survey_id = '" . intval($_GET['survey_id']) . "' AND\n\t\t\t\tsurvey_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') {
$worksheet->SetCellValueByColumnAndRow($line, $column, 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'];
$column++;
}
}
}
// Getting all the answers of the users
$line++;
$column = 0;
$old_user = '';
$answers_of_user = array();
$sql = "SELECT * FROM {$table_survey_answer}\n WHERE c_id = {$course_id} AND survey_id='" . intval($_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_xls($survey_data, $possible_answers, $answers_of_user, $old_user, true);
foreach ($return as $elem) {
$worksheet->SetCellValueByColumnAndRow($line, $column, $elem);
$column++;
}
$answers_of_user = array();
$line++;
$column = 0;
}
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'];
}
$return = SurveyUtil::export_complete_report_row_xls($survey_data, $possible_answers, $answers_of_user, $old_user, true);
// this is to display the last user
foreach ($return as $elem) {
$worksheet->SetCellValueByColumnAndRow($line, $column, $elem);
$column++;
}
$file = api_get_path(SYS_ARCHIVE_PATH) . api_replace_dangerous_char($filename);
$writer = new PHPExcel_Writer_Excel2007($spreadsheet);
$writer->save($file);
DocumentManager::file_send_for_download($file, true, $filename);
return null;
}
/**
* 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
*/
static function export_complete_report_xls($survey_data, $filename, $user_id = 0)
{
require_once api_get_path(LIBRARY_PATH).'pear/Spreadsheet_Excel_Writer/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$workbook ->setTempDir(api_get_path(SYS_ARCHIVE_PATH));
$workbook->send($filename);
$workbook->setVersion(8); // BIFF8
$worksheet =& $workbook->addWorksheet('Report 1');
$worksheet->setInputEncoding(api_get_system_encoding());
$line = 0;
$column = 1; // Skip the first column (row titles)
// Show extra fields blank space (enough for extra fields on next line)
$display_extra_user_fields = false;
//if (!empty($_REQUEST['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);
for ($i = 0; $i < $num; $i++) {
$worksheet->write($line, $column, '');
$column++;
}
$display_extra_user_fields = true;
//}
// 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);
$course_id = api_get_course_int_id();
// First line (questions)
$sql = "SELECT questions.question_id, questions.type, questions.survey_question, count(options.question_option_id) as number_of_options
FROM $table_survey_question questions
LEFT JOIN $table_survey_question_option options
ON questions.question_id = options.question_id AND options.c_id = $course_id
WHERE
questions.survey_id = '".Database::escape_string($_GET['survey_id'])."' AND
questions.c_id = $course_id
GROUP BY questions.question_id
ORDER 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') {
$worksheet->write($line,$column,api_html_entity_decode(strip_tags($row['survey_question']), ENT_QUOTES));
$column ++;
} else {
for ($ii = 0; $ii < $row['number_of_options']; $ii ++) {
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($row['survey_question']), ENT_QUOTES));
$column ++;
}
}
}
}
}
$line++;
$column = 1;
// Show extra field values
if ($display_extra_user_fields) {
// Show the fields names for user fields
foreach ($extra_user_fields as & $field) {
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($field[3]), ENT_QUOTES));
$column++;
}
}
// Getting all the questions and options (second line)
$sql = "SELECT survey_question.question_id, survey_question.survey_id, survey_question.survey_question, survey_question.display, survey_question.sort, survey_question.type,
survey_question_option.question_option_id, survey_question_option.option_text, survey_question_option.sort as option_sort
FROM $table_survey_question survey_question
LEFT JOIN $table_survey_question_option survey_question_option
ON survey_question.question_id = survey_question_option.question_id AND survey_question_option.c_id = $course_id
WHERE survey_question.survey_id = '".Database::escape_string($_GET['survey_id'])."' AND
survey_question.c_id = $course_id
ORDER 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') {
$worksheet->write($line,$column,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'];
$column++;
}
}
}
// Getting all the answers of the users
$line ++;
$column = 0;
$old_user = '';
$answers_of_user = array();
$sql = "SELECT * FROM $table_survey_answer 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_xls($survey_data, $possible_answers, $answers_of_user, $old_user, true);
foreach ($return as $elem) {
$worksheet->write($line, $column, $elem);
$column++;
}
$answers_of_user = array();
$line++;
$column = 0;
}
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'];
}
$return = SurveyUtil::export_complete_report_row_xls($survey_data, $possible_answers, $answers_of_user, $old_user, true); // this is to display the last user
foreach ($return as $elem) {
$worksheet->write($line, $column, $elem);
$column++;
}
$workbook->close();
return null;
}
