, Ghent University * @version January 2007 */ public static function remove_answer($user, $survey_id, $question_id, $course_id) { $course_id = intval($course_id); // table definition $table_survey_answer = Database::get_course_table(TABLE_SURVEY_ANSWER); $sql = "DELETE FROM $table_survey_answer WHERE c_id = $course_id AND user = '".Database::escape_string($user)."' AND survey_id = '".intval($survey_id)."' AND question_id = '".intval($question_id)."'"; Database::query($sql); } /** * This function stores an answer of a user on a question of a survey * * @param mixed The user id or email of the person who fills the survey * @param integer Survey id * @param integer Question id * @param integer Option id * @param string Option value * @param array $survey_data Survey data settings * @return bool False if insufficient data, true otherwise * * @author Patrick Cool , Ghent University * @version January 2007 */ public static function store_answer( $user, $survey_id, $question_id, $option_id, $option_value, $survey_data ) { // If the question_id is empty, don't store an answer if (empty($question_id)) { return false; } // Table definition $table_survey_answer = Database::get_course_table(TABLE_SURVEY_ANSWER); // Make the survey anonymous if ($survey_data['anonymous'] == 1) { if (!isset($_SESSION['surveyuser'])) { $user = md5($user.time()); $_SESSION['surveyuser'] = $user; } else { $user = $_SESSION['surveyuser']; } } $course_id = $survey_data['c_id']; $sql = "INSERT INTO $table_survey_answer (c_id, user, survey_id, question_id, option_id, value) VALUES ( $course_id, '".Database::escape_string($user)."', '".Database::escape_string($survey_id)."', '".Database::escape_string($question_id)."', '".Database::escape_string($option_id)."', '".Database::escape_string($option_value)."' )"; Database::query($sql); $insertId = Database::insert_id(); $sql = "UPDATE $table_survey_answer SET answer_id = $insertId WHERE iid = $insertId"; Database::query($sql); return true; } /** * This function checks the parameters that are used in this page * * @return string $people_filled The header, an error and the footer if any parameter fails, else it returns true * @author Patrick Cool , Ghent University * @version February 2007 */ public static function check_parameters($people_filled) { $error = false; // Getting the survey data $survey_data = SurveyManager::get_survey($_GET['survey_id']); // $_GET['survey_id'] has to be numeric if (!is_numeric($_GET['survey_id'])) { $error = get_lang('IllegalSurveyId'); } // $_GET['action'] $allowed_actions = array( 'overview', 'questionreport', 'userreport', 'comparativereport', 'completereport', 'deleteuserreport' ); if (isset($_GET['action']) && !in_array($_GET['action'], $allowed_actions)) { $error = get_lang('ActionNotAllowed'); } // User report if (isset($_GET['action']) && $_GET['action'] == 'userreport') { if ($survey_data['anonymous'] == 0) { foreach ($people_filled as $key => & $value) { $people_filled_userids[] = $value['invited_user']; } } else { $people_filled_userids = $people_filled; } if (isset($_GET['user']) && !in_array($_GET['user'], $people_filled_userids)) { $error = get_lang('UnknowUser'); } } // Question report if (isset($_GET['action']) && $_GET['action'] == 'questionreport') { if (isset($_GET['question']) && !is_numeric($_GET['question'])) { $error = get_lang('UnknowQuestion'); } } if ($error) { $tool_name = get_lang('Reporting'); Display::addFlash( Display::return_message( get_lang('Error').': '.$error, 'error', false ) ); Display::display_header($tool_name); Display::display_footer(); exit; } else { return true; } } /** * This function deals with the action handling * @param array $survey_data * @param array $people_filled * @return void * @author Patrick Cool , Ghent University * @version February 2007 */ public static function handle_reporting_actions($survey_data, $people_filled) { $action = isset($_GET['action']) ? $_GET['action'] : null; // Getting the number of question $temp_questions_data = SurveyManager::get_questions($_GET['survey_id']); // Sorting like they should be displayed and removing the non-answer question types (comment and pagebreak) $my_temp_questions_data = $temp_questions_data == null ? array() : $temp_questions_data; $questions_data = array(); foreach ($my_temp_questions_data as $key => & $value) { if ($value['type'] != 'comment' && $value['type'] != 'pagebreak') { $questions_data[$value['sort']] = $value; } } // Counting the number of questions that are relevant for the reporting $survey_data['number_of_questions'] = count($questions_data); if ($action == 'questionreport') { self::display_question_report($survey_data); } if ($action == 'userreport') { self::display_user_report($people_filled, $survey_data); } if ($action == 'comparativereport') { self::display_comparative_report(); } if ($action == 'completereport') { self::display_complete_report($survey_data); } if ($action == 'deleteuserreport') { self::delete_user_report($_GET['survey_id'], $_GET['user']); } } /** * This function deletes the report of an user who wants to retake the survey * @param integer $survey_id * @param integer $user_id * @return void * @author Christian Fasanando Flores * @version November 2008 */ public static function delete_user_report($survey_id, $user_id) { $table_survey_answer = Database::get_course_table(TABLE_SURVEY_ANSWER); $table_survey_invitation = Database::get_course_table(TABLE_SURVEY_INVITATION); $table_survey = Database::get_course_table(TABLE_SURVEY); $course_id = api_get_course_int_id(); $survey_id = (int) $survey_id; $user_id = Database::escape_string($user_id); if (!empty($survey_id) && !empty($user_id)) { // delete data from survey_answer by user_id and survey_id $sql = "DELETE FROM $table_survey_answer WHERE c_id = $course_id AND survey_id = '".$survey_id."' AND user = '".$user_id."'"; Database::query($sql); // update field answered from survey_invitation by user_id and survey_id $sql = "UPDATE $table_survey_invitation SET answered = '0' WHERE c_id = $course_id AND survey_code = ( SELECT code FROM $table_survey WHERE c_id = $course_id AND survey_id = '".$survey_id."' ) AND user = '".$user_id."'"; $result = Database::query($sql); } if ($result !== false) { $message = get_lang('SurveyUserAnswersHaveBeenRemovedSuccessfully').'
'. get_lang('GoBack').''; echo Display::return_message($message, 'confirmation', false); } } /** * This function displays the user report which is basically nothing more * than a one-page display of all the questions * of the survey that is filled with the answers of the person who filled the survey. * * @return string html code of the one-page survey with the answers of the selected user * @author Patrick Cool , Ghent University * @version February 2007 - Updated March 2008 */ public static function display_user_report($people_filled, $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); $surveyId = isset($_GET['survey_id']) ? (int) $_GET['survey_id'] : 0; // Actions bar echo ''; // Step 1: selection of the user echo ""; echo get_lang('SelectUserWhoFilledSurvey').'
'; echo ''; $course_id = api_get_course_int_id(); // Step 2: displaying the survey and the answer of the selected users if (isset($_GET['user'])) { echo Display::return_message( get_lang('AllQuestionsOnOnePage'), 'normal', false ); // Getting all the questions and options $sql = "SELECT survey_question.question_id, survey_question.survey_id, survey_question.survey_question, survey_question.display, survey_question.max_value, 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, survey_question_option.sort ASC"; $result = Database::query($sql); while ($row = Database::fetch_array($result, 'ASSOC')) { if ($row['type'] != 'pagebreak') { $questions[$row['sort']]['question_id'] = $row['question_id']; $questions[$row['sort']]['survey_id'] = $row['survey_id']; $questions[$row['sort']]['survey_question'] = $row['survey_question']; $questions[$row['sort']]['display'] = $row['display']; $questions[$row['sort']]['type'] = $row['type']; $questions[$row['sort']]['maximum_score'] = $row['max_value']; $questions[$row['sort']]['options'][$row['question_option_id']] = $row['option_text']; } } // Getting all the answers of the user $sql = "SELECT * FROM $table_survey_answer WHERE c_id = $course_id AND survey_id = '".intval($_GET['survey_id'])."' AND user = '".Database::escape_string($_GET['user'])."'"; $result = Database::query($sql); while ($row = Database::fetch_array($result, 'ASSOC')) { $answers[$row['question_id']][] = $row['option_id']; $all_answers[$row['question_id']][] = $row; } // Displaying all the questions foreach ($questions as & $question) { // If the question type is a scoring then we have to format the answers differently switch ($question['type']) { case 'score': $finalAnswer = array(); if (is_array($question) && is_array($all_answers)) { foreach ($all_answers[$question['question_id']] as $key => & $answer_array) { $finalAnswer[$answer_array['option_id']] = $answer_array['value']; } } break; case 'multipleresponse': $finalAnswer = isset($answers[$question['question_id']]) ? $answers[$question['question_id']] : ''; break; default: $finalAnswer = ''; if (isset($all_answers[$question['question_id']])) { $finalAnswer = $all_answers[$question['question_id']][0]['option_id']; } break; } $ch_type = 'ch_'.$question['type']; /** @var survey_question $display */ $display = new $ch_type; $url = api_get_self(); $form = new FormValidator('question', 'post', $url); $form->addHtml('
'); $form->addHtml($question['survey_question']); $display->render($form, $question, $finalAnswer); $form->addHtml('
'); $form->display(); } } } /** * 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 ''; if ($survey_data['number_of_questions'] > 0) { $limitStatement = null; if (!$singlePage) { echo ''; $limitStatement = " LIMIT $offset, 1"; } // 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 $limitStatement"; $result = Database::query($sql); //$question = Database::fetch_array($result); while ($row = Database::fetch_array($result)) { $questions[$row['question_id']] = $row; } } foreach ($questions as $question) { $chartData = array(); $options = array(); echo '
'; echo strip_tags(isset($question['survey_question']) ? $question['survey_question'] : null); echo '
'; if ($question['type'] == 'score') { /** @todo This function should return the options as this is needed further in the code */ $options = self::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 WHERE c_id = $course_id AND survey_id='" . intval($_GET['survey_id']) . "' AND question_id = '" . intval($question['question_id']) . "'"; $result = Database::query($sql); while ($row = Database::fetch_array($result, 'ASSOC')) { echo $row['option_id'] . '
'; } } else { // Getting the options ORDER BY sort ASC $sql = "SELECT * FROM $table_survey_question_option WHERE c_id = $course_id AND survey_id='" . intval($_GET['survey_id']) . "' AND question_id = '" . intval($question['question_id']) . "' ORDER BY sort ASC"; $result = Database::query($sql); while ($row = Database::fetch_array($result, 'ASSOC')) { $options[$row['question_option_id']] = $row; } // Getting the answers $sql = "SELECT *, count(answer_id) as total FROM $table_survey_answer WHERE c_id = $course_id AND survey_id='" . intval($_GET['survey_id']) . "' AND question_id = '" . intval($question['question_id']) . "' GROUP BY option_id, value"; $result = Database::query($sql); $number_of_answers = array(); $data = array(); while ($row = Database::fetch_array($result, 'ASSOC')) { 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 '
'; echo self::drawChart($chartData, false, $chartContainerId); // displaying the table: headers echo ''; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; // 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; } $number_of_answers[$option['question_id']] = isset($number_of_answers[$option['question_id']]) ? $number_of_answers[$option['question_id']] : 0; if ($number_of_answers[$option['question_id']] == 0) { $answers_number = 0; } else { $answers_number = $absolute_number / $number_of_answers[$option['question_id']] * 100; } echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; } } // displaying the table: footer (totals) echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; echo '
 ' . get_lang('AbsoluteTotal') . '' . get_lang('Percentage') . '' . get_lang('VisualRepresentation') . '
' . $value['option_text'] . ''; if ($absolute_number != 0) { echo '' . $absolute_number . ''; } else { echo '0'; } echo ' ' . round($answers_number, 2) . ' %'; $size = $answers_number * 2; if ($size > 0) { echo '
 
'; } else { echo '
' . get_lang("NoDataAvailable") . '
'; } echo '
' . get_lang('Total') . '' . ($number_of_answers[$option['question_id']] == 0 ? '0' : $number_of_answers[$option['question_id']]) . '  
'; echo '
'; } } if (isset($_GET['viewoption'])) { echo '
'; echo '

'.get_lang('PeopleWhoAnswered').': '.strip_tags($options[Security::remove_XSS($_GET['viewoption'])]['option_text']).'

'; 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); echo ''; echo '
'; } } /** * Display score data about a survey question * @param array Question info * @param integer The offset of results shown * @return void (direct output) */ public static function display_question_report_score($survey_data, $question, $offset) { // Database table definitions $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(); // Getting the options $sql = "SELECT * FROM $table_survey_question_option WHERE c_id = $course_id AND survey_id='".Database::escape_string($_GET['survey_id'])."' AND question_id = '".Database::escape_string($question['question_id'])."' 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 WHERE c_id = $course_id AND survey_id='".Database::escape_string($_GET['survey_id'])."' AND question_id = '".Database::escape_string($question['question_id'])."' GROUP BY option_id, value"; $result = Database::query($sql); $number_of_answers = 0; while ($row = Database::fetch_array($result)) { $number_of_answers += $row['total']; $data[$row['option_id']][$row['value']] = $row; } $chartData = array(); foreach ($options as $option) { $optionText = strip_tags($option['option_text']); $optionText = html_entity_decode($optionText); for ($i = 1; $i <= $question['max_value']; $i++) { $votes = $data[$option['question_option_id']][$i]['total']; if (empty($votes)) { $votes = '0'; } array_push( $chartData, array( 'serie' => $optionText, 'option' => $i, 'votes' => $votes ) ); } } echo '
'; echo self::drawChart($chartData, true); echo '
'; // Displaying the table: headers echo ''; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; // Displaying the table: the content foreach ($options as $key => & $value) { for ($i = 1; $i <= $question['max_value']; $i++) { $absolute_number = $data[$value['question_option_id']][$i]['total']; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; } } // Displaying the table: footer (totals) echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; echo ' '; echo '
 '.get_lang('Score').''.get_lang('AbsoluteTotal').''.get_lang('Percentage').''.get_lang('VisualRepresentation').'
'.$value['option_text'].''.$i.''.$absolute_number.''.round($absolute_number/$number_of_answers*100, 2).' %'; $size = ($absolute_number/$number_of_answers*100*2); if ($size > 0) { echo '
 
'; } echo '
'.get_lang('Total').' '.$number_of_answers.'  
'; } /** * 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); $surveyId = isset($_GET['survey_id']) ? intval($_GET['survey_id']) : 0; $action = isset($_GET['action']) ? Security::remove_XSS($_GET['action']) : ''; // Actions bar echo ''; // The form echo ''; echo ''; echo ''; echo ''; echo '
'; echo ''; echo ''; echo '
'; echo '
'; // The table echo '
'; // Getting the number of options per question echo ' '; echo ' '; $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 ''; $display_extra_user_fields = true; } } $course_id = api_get_course_int_id(); $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.survey_id = '".$surveyId."' AND q.c_id = $course_id AND o.c_id = $course_id GROUP BY q.question_id ORDER BY q.sort ASC"; $result = Database::query($sql); $questions = []; 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 (!(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 ' modified tst to include percentage if ($row['number_of_options'] > 0 && $row['type'] != 'percentage') { // echo ' colspan="'.$row['number_of_options'].'"'; } echo '>'; echo ''; echo ''; } // No column at all if it's not a question } $questions[$row['question_id']] = $row; } echo ' '; // Getting all the questions and options echo ' '; echo ' '; // 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 ''; } } // cells with option (none for open question) $sql = "SELECT sq.question_id, sq.survey_id, sq.survey_question, sq.display, sq.sort, sq.type, sqo.question_option_id, sqo.option_text, sqo.sort as option_sort FROM $table_survey_question sq LEFT JOIN $table_survey_question_option sqo ON sq.question_id = sqo.question_id WHERE sq.survey_id = '".$surveyId."' AND sq.c_id = $course_id AND sqo.c_id = $course_id ORDER BY sq.sort ASC, sqo.sort ASC"; $result = Database::query($sql); $display_percentage_header = 1; $possible_answers = []; // 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 (!(isset($_POST['submit_question_filter']) && $_POST['submit_question_filter']) || (is_array($_POST['questions_filter']) && in_array($row['question_id'], $_POST['questions_filter'])) ) { // modif 05-05-2010 // we do not show comment and pagebreak question types if ($row['type'] == 'open') { echo ''; $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 ''; $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' && $row['type'] <> 'pagebreak' && $row['type'] <> 'percentage') { echo ''; $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 // } } echo ' '; // Getting all the answers of the users $old_user = ''; $answers_of_user = array(); $sql = "SELECT * FROM $table_survey_answer WHERE c_id = $course_id AND survey_id='".$surveyId."' ORDER BY answer_id, 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++; } self::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++; } self::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 '
'; if ( (isset($_POST['submit_question_filter']) && $_POST['submit_question_filter']) || (isset($_POST['export_report']) && $_POST['export_report']) ) { echo ''; } echo ''; echo '0?' colspan="'.$num.'"':'').'>'; echo ''; echo '
 '.$field[3].' -  % '; echo $row['option_text']; echo '
'; echo '
'; } /** * This function displays a row (= a user and his/her answers) in the table of the complete report. * * @param array $survey_data * @param array Possible options * @param array User answers * @param mixed User ID or user details string * @param boolean Whether to show extra user fields or not * @author Patrick Cool , Ghent University * @version February 2007 - Updated March 2008 */ public static function display_complete_report_row( $survey_data, $possible_options, $answers_of_user, $user, $questions, $display_extra_user_fields = false ) { $user = Security::remove_XSS($user); echo ''; if ($survey_data['anonymous'] == 0) { if (intval($user) !== 0) { $userInfo = api_get_user_info($user); if (!empty($userInfo)) { $user_displayed = $userInfo['complete_name_with_username']; } else { $user_displayed = '-'; } echo ''. $user_displayed.''; // the user column } else { echo ''.$user.''; // the user column } } else { echo '' . get_lang('Anonymous') . ' ' . $user . ''; } if ($display_extra_user_fields) { // Show user fields data, if any, for this user $user_fields_values = UserManager::get_extra_user_data(intval($user), false, false, false, true); foreach ($user_fields_values as & $value) { echo ''.$value.''; } } if (is_array($possible_options)) { // modified to display open answers and percentage foreach ($possible_options as $question_id => & $possible_option) { if ($questions[$question_id]['type'] == 'open') { echo ''; echo $answers_of_user[$question_id]['0']['option_id']; echo ''; } else { foreach ($possible_option as $option_id => & $value) { if ($questions[$question_id]['type'] == 'percentage') { if (!empty($answers_of_user[$question_id][$option_id])) { echo ""; echo $answers_of_user[$question_id][$option_id]['value']; echo ""; } } else { echo ''; if (!empty($answers_of_user[$question_id][$option_id])) { if ($answers_of_user[$question_id][$option_id]['value'] != 0) { echo $answers_of_user[$question_id][$option_id]['value']; } else { echo 'v'; } } } // } } } } echo ''; } /** * 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 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 = '".intval($_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 (!(isset($_POST['submit_question_filter'])) || (isset($_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 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 = '".intval($_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 question filter but the question is selected for display if (!(isset($_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 WHERE c_id = $course_id AND survey_id='".Database::escape_string($_GET['survey_id'])."'"; if ($user_id != 0) { $sql .= "AND user='".Database::escape_string($user_id)."' "; } $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 .= self::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 .= self::export_complete_report_row( $survey_data, $possible_answers, $answers_of_user, $old_user, true ); return $return; } /** * Add a line to the csv file * * @param array Possible answers * @param array User's answers * @param mixed User ID or user details as string - Used as a string in the result string * @param boolean Whether to display user fields or not * @return string One line of the csv file * @author Patrick Cool , Ghent University * @version February 2007 */ public static function export_complete_report_row( $survey_data, $possible_options, $answers_of_user, $user, $display_extra_user_fields = false ) { $return = ''; if ($survey_data['anonymous'] == 0) { if (intval($user) !== 0) { $userInfo = api_get_user_info($user); if (!empty($userInfo)) { $user_displayed = $userInfo['complete_name_with_username']; } else { $user_displayed = '-'; } $return .= $user_displayed.';'; } else { $return .= $user.';'; } } else { $return .= '-;'; // The user column } if ($display_extra_user_fields) { // Show user fields data, if any, for this user $user_fields_values = UserManager::get_extra_user_data( $user, false, false, false, true ); foreach ($user_fields_values as & $value) { $return .= '"'.str_replace('"', '""', api_html_entity_decode(strip_tags($value), ENT_QUOTES)).'";'; } } if (is_array($possible_options)) { foreach ($possible_options as $question_id => $possible_option) { if (is_array($possible_option) && count($possible_option) > 0) { foreach ($possible_option as $option_id => & $value) { $my_answer_of_user = !isset($answers_of_user[$question_id]) || isset($answers_of_user[$question_id]) && $answers_of_user[$question_id] == null ? array() : $answers_of_user[$question_id]; $key = array_keys($my_answer_of_user); if (isset($key[0]) && substr($key[0], 0, 4) == 'open') { $return .= '"'. str_replace( '"', '""', api_html_entity_decode(strip_tags($answers_of_user[$question_id][$key[0]]['option_id']), ENT_QUOTES) ). '"'; } elseif (!empty($answers_of_user[$question_id][$option_id])) { //$return .= 'v'; if ($answers_of_user[$question_id][$option_id]['value'] != 0) { $return .= $answers_of_user[$question_id][$option_id]['value']; } else { $return .= 'v'; } } $return .= ';'; } } } } $return .= "\n"; return $return; } /** * 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_xls($survey_data, $filename, $user_id = 0) { $course_id = api_get_course_int_id(); $surveyId = isset($_GET['survey_id']) ? (int) $_GET['survey_id'] : 0; if (empty($course_id) || empty($surveyId)) { return false; } $spreadsheet = new PHPExcel(); $spreadsheet->setActiveSheetIndex(0); $worksheet = $spreadsheet->getActiveSheet(); $line = 1; $column = 1; // Skip the first column (row titles) // Show extra fields blank space (enough for extra fields on next line) // 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($column, $line, ''); $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); // 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 = $surveyId 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 (!(isset($_POST['submit_question_filter'])) || (isset($_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( $column, $line, api_html_entity_decode( strip_tags($row['survey_question']), ENT_QUOTES ) ); $column++; } else { for ($ii = 0; $ii < $row['number_of_options']; $ii ++) { $worksheet->setCellValueByColumnAndRow( $column, $line, 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( $column, $line, 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 = $surveyId 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 (!isset($_POST['submit_question_filter']) || (isset($_POST['questions_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( $column, $line, 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 = $surveyId"; if ($user_id != 0) { $sql .= " AND user='".intval($user_id)."' "; } $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 = self::export_complete_report_row_xls( $survey_data, $possible_answers, $answers_of_user, $old_user, true ); foreach ($return as $elem) { $worksheet->setCellValueByColumnAndRow($column, $line, $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 = self::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($column, $line, $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; } /** * Add a line to the csv file * * @param array Possible answers * @param array User's answers * @param mixed User ID or user details as string - Used as a string in the result string * @param boolean Whether to display user fields or not * @return string One line of the csv file */ public static function export_complete_report_row_xls( $survey_data, $possible_options, $answers_of_user, $user, $display_extra_user_fields = false ) { $return = array(); if ($survey_data['anonymous'] == 0) { if (intval($user) !== 0) { $userInfo = api_get_user_info($user); if ($userInfo) { $user_displayed = $userInfo['complete_name_with_username']; } else { $user_displayed = '-'; } $return[] = $user_displayed; } else { $return[] = $user; } } else { $return[] = '-'; // The user column } if ($display_extra_user_fields) { //show user fields data, if any, for this user $user_fields_values = UserManager::get_extra_user_data( intval($user), false, false, false, true ); foreach ($user_fields_values as $value) { $return[] = api_html_entity_decode(strip_tags($value), ENT_QUOTES); } } if (is_array($possible_options)) { foreach ($possible_options as $question_id => & $possible_option) { if (is_array($possible_option) && count($possible_option) > 0) { foreach ($possible_option as $option_id => & $value) { $my_answers_of_user = isset($answers_of_user[$question_id]) ? $answers_of_user[$question_id] : []; $key = array_keys($my_answers_of_user); if (isset($key[0]) && substr($key[0], 0, 4) == 'open') { $return[] = api_html_entity_decode(strip_tags($answers_of_user[$question_id][$key[0]]['option_id']), ENT_QUOTES); } elseif (!empty($answers_of_user[$question_id][$option_id])) { if ($answers_of_user[$question_id][$option_id]['value'] != 0) { $return[] = $answers_of_user[$question_id][$option_id]['value']; } else { $return[] = 'v'; } } else { $return[] = ''; } } } } } return $return; } /** * This function displays the comparative report which allows you to compare two questions * A comparative report creates a table where one question is on the x axis and a second question is on the y axis. * In the intersection is the number of people who have answerd positive on both options. * * @return string HTML code * * @author Patrick Cool , Ghent University * @version February 2007 */ public static function display_comparative_report() { // Allowed question types for comparative report $allowed_question_types = array( 'yesno', 'multiplechoice', 'multipleresponse', 'dropdown', 'percentage', 'score' ); $surveyId = isset($_GET['survey_id']) ? (int) $_GET['survey_id'] : 0; // Getting all the questions $questions = SurveyManager::get_questions($surveyId); // Actions bar echo ''; // Displaying an information message that only the questions with predefined answers can be used in a comparative report echo Display::return_message(get_lang('OnlyQuestionsWithPredefinedAnswers'), 'normal', false); $xAxis = isset($_GET['xaxis']) ? Security::remove_XSS($_GET['xaxis']) : ''; $yAxis = isset($_GET['yaxis']) ? Security::remove_XSS($_GET['yaxis']) : ''; $url = api_get_self().'?'.api_get_cidreq().'&action='.Security::remove_XSS($_GET['action']).'&survey_id='.$surveyId.'&xaxis='.$xAxis.'&y='.$yAxis; $form = new FormValidator('compare', 'get', $url); $form->addHidden('action', Security::remove_XSS($_GET['action'])); $form->addHidden('survey_id', $surveyId); $optionsX = ['----']; $optionsY = ['----']; $defaults = []; foreach ($questions as $key => & $question) { if (is_array($allowed_question_types)) { if (in_array($question['type'], $allowed_question_types)) { //echo '