function export($takings, $exam)
{
global $wpdb;
$newline = watupro_define_newline();
$rows = array();
// add all first names and last names to match them when exporting
$uids = array(0);
foreach ($takings as $taking) {
if (!empty($taking->user_id)) {
$uids[] = $taking->user_id;
}
}
$uids = array_unique($uids);
$first_names = $wpdb->get_results("SELECT meta_value, user_id FROM {$wpdb->usermeta}\n\t\t\t\tWHERE meta_key = 'first_name' AND user_id IN (" . implode(",", $uids) . ")");
$last_names = $wpdb->get_results("SELECT meta_value, user_id FROM {$wpdb->usermeta}\n\t\t\t\tWHERE meta_key = 'last_name' AND user_id IN (" . implode(",", $uids) . ")");
foreach ($takings as $cnt => $taking) {
foreach ($first_names as $first_name) {
if ($first_name->user_id == $taking->user_id) {
$takings[$cnt]->first_name = $first_name->meta_value;
}
}
foreach ($last_names as $last_name) {
if ($last_name->user_id == $taking->user_id) {
$takings[$cnt]->last_name = $last_name->meta_value;
}
}
}
// end adding first / last names to takings
if (empty($_GET['details'])) {
$rows[] = __('First name', 'watupro') . "\t" . __('Last name', 'watupro') . "\t" . __('Username and details', 'watupro') . "\t" . __('Email', 'watupro') . "\t" . __('IP', 'watupro') . "\t" . __('Date', 'watupro') . "\t" . __('Points', 'watupro') . "\t" . __('Percent correct', 'watupro') . "\t" . __('Grade', 'watupro');
} else {
// exports with questions and answers
$questions = $wpdb->get_results($wpdb->prepare("SELECT * FROM " . WATUPRO_QUESTIONS . "\n\t\t\t\t\tWHERE exam_id=%d ORDER BY ID", $exam->ID));
$titlerow = __('First name', 'watupro') . "\t" . __('Last name', 'watupro') . "\t" . __('Username', 'watupro') . "\t" . __('Email', 'watupro') . "\t" . __('IP', 'watupro') . "\t" . __('Date', 'watupro') . "\t";
foreach ($questions as $question) {
// strip tags and remove semicolon to protect the CSV sanity
$question_txt = strip_tags(str_replace("\t", " ", $question->question));
$question_txt = str_replace("\n", " ", $question_txt);
$question_txt = str_replace("\r", " ", $question_txt);
$question_txt = stripslashes($question_txt);
$titlerow .= $question_txt . "\t";
}
$titlerow .= __('Points', 'watupro') . "\t" . __('% Correct', 'watupro') . "\t" . __('Grade', 'watupro');
$rows[] = $titlerow;
// we also have to get full details so they can be matched below
$details = $wpdb->get_results($wpdb->prepare("SELECT * FROM " . WATUPRO_STUDENT_ANSWERS . "\n\t\t\t\t\t\tWHERE exam_id=%d", $exam->ID));
}
foreach ($takings as $taking) {
$taking_email = $taking->user_id ? $taking->user_email : $taking->email;
$taking_name = $taking->user_id ? $taking->display_name : (empty($taking->name) ? "N/A" : stripslashes($taking->name));
// add contact data if any
if (!empty($taking->contact_data)) {
$taking_name .= ' - ' . $taking->contact_data;
}
$row = (!empty($taking->first_name) ? $taking->first_name : "N/A") . "\t" . (!empty($taking->last_name) ? $taking->last_name : "N/A") . "\t" . $taking_name . "\t" . ($taking_email ? $taking_email : "N/A") . "\t" . $taking->ip . "\t" . date(get_option('date_format'), strtotime($taking->date)) . "\t";
if (!empty($_GET['details'])) {
foreach ($questions as $question) {
$answer = $feedback = "";
foreach ($details as $detail) {
if ($detail->taking_id == $taking->ID and $detail->question_id == $question->ID) {
// handle matrix better
if ($question->answer_type == 'matrix') {
$detail->answer = str_replace('</td><td>', ' = ', $detail->answer);
$detail->answer = str_replace('</tr><tr>', '; ', $detail->answer);
}
$answer = strip_tags(str_replace("\t", " ", $detail->answer));
$answer = str_replace("\n", " ", $answer);
$answer = str_replace("\r", " ", $answer);
$answer = stripslashes($answer);
// question accepts user feedback?
if ($question->accept_feedback and !empty($detail->feedback)) {
$feedback = strip_tags(str_replace("\t", " ", $detail->feedback));
$feedback = str_replace("\n", " ", $feedback);
$feedback = str_replace("\r", " ", $feedback);
$feedback = stripslashes($feedback);
$answer .= "; " . stripslashes($question->feedback_label) . " " . $feedback;
}
// end if accepts feedback
}
// end if detail matches taking and question
}
// end foreach answer
$row .= $answer . "\t";
}
// end foreach question
}
$taking_result = strip_tags(str_replace("\t", " ", $taking->result));
$taking_result = str_replace("\n", " ", $taking_result);
$taking_result = str_replace("\r", " ", $taking_result);
$taking_result = stripslashes($taking_result);
$row .= $taking->points . "\t" . $taking->percent_correct . "\t" . $taking_result;
$rows[] = $row;
}
$csv = implode($newline, $rows);
// credit to http://yoast.com/wordpress/users-to-csv/
$now = gmdate('D, d M Y H:i:s') . ' GMT';
header('Content-Type: ' . watupro_get_mime_type());
header('Expires: ' . $now);
header('Content-Disposition: attachment; filename="quiz-' . $exam->ID . '.csv"');
header('Pragma: no-cache');
echo $csv;
exit;
}
function watupro_export_questions()
{
global $wpdb;
$newline = watupro_define_newline();
// select questions
$questions = $wpdb->get_results($wpdb->prepare("SELECT tQ.*, tC.name as category \n\t\tFROM " . WATUPRO_QUESTIONS . " tQ LEFT JOIN " . WATUPRO_QCATS . " tC ON tC.ID=tQ.cat_id \n\t\tWHERE tQ.exam_id=%d ORDER BY tQ.sort_order, tQ.ID", $_GET['exam_id']), ARRAY_A);
$qids = array(0);
foreach ($questions as $question) {
$qids[] = $question['ID'];
}
$qid_sql = implode(",", $qids);
// select all answers in the exam
$answers = $wpdb->get_results("SELECT * FROM " . WATUPRO_ANSWERS . " WHERE question_id IN ({$qid_sql})");
// match answers to questions
foreach ($questions as $cnt => $question) {
$questions[$cnt]['answers'] = array();
foreach ($answers as $answer) {
if ($answer->question_id == $question['ID']) {
$questions[$cnt]['answers'][] = $answer;
}
}
}
// run last query to define the max number of answers
$num_ans = $wpdb->get_row("SELECT COUNT(ID) as num_answers FROM " . WATUPRO_ANSWERS . " WHERE question_id IN ({$qid_sql})\n\t\t\tGROUP BY question_id ORDER BY num_answers DESC");
$rows = array();
if (empty($_GET['copy'])) {
$titlerow = "Question ID\tQuestion\tAnswer Type\tOrder\tCategory\tExplanation/Feedback\tRequired?\tCorrect answer condition\tFill the gap/sorting points\tSorting Answers\tMax selections\tIs Inactive?\tIs Survey?\tElaborate answer feedback";
for ($i = 1; $i <= $num_ans->num_answers; $i++) {
$titlerow .= "\tAnswer ID\tAnswer\tPoints";
}
} else {
$titlerow = "Question\tAnswer Type\tOrder\tCategory\tExplanation/Feedback\tRequired?\tCorrect answer condition\tFill the gap/sorting points\tSorting Answers\tMax selections\tIs Inactive?\tIs survey?\tElaborate answer feedback";
// non-legacy export
if (empty($GET['legacy'])) {
$titlerow .= "\tOpen end mode\ttags\tOpen-end question display style\tExclude from showing on the final screen? (0 or 1)\tHints\tDisplay in compact format? (0 or 1)\tRound the points to the closest decimal? (0 or 1)\tIs this an important question? (0 or 100)";
}
if (empty($GET['legacy'])) {
for ($i = 1; $i <= $num_ans->num_answers; $i++) {
$titlerow .= "\tAnswer\tIs Correct?\tPoints";
}
} else {
for ($i = 1; $i <= $num_ans->num_answers; $i++) {
$titlerow .= "\tAnswer\tPoints";
}
}
}
$rows[] = $titlerow;
foreach ($questions as $question) {
// replace tabulators and quotes to avoid issues with excel
$question['question'] = str_replace("\t", " ", $question['question']);
$question['question'] = str_replace('"', "'", $question['question']);
$question['question'] = nl2br($question['question']);
$question['explain_answer'] = str_replace("\t", " ", $question['explain_answer']);
$question['explain_answer'] = str_replace('"', "'", $question['explain_answer']);
$question['explain_answer'] = nl2br($question['explain_answer']);
$question['explain_answer'] = str_replace("\n", "", $question['explain_answer']);
$question['explain_answer'] = str_replace("\r", "", $question['explain_answer']);
$question['hints'] = str_replace("\t", " ", $question['hints']);
$question['hints'] = str_replace('"', "'", $question['hints']);
$question['hints'] = nl2br($question['hints']);
$question['sorting_answers'] = str_replace('"', "'", $question['sorting_answers']);
$question['sorting_answers'] = str_replace("\n", "|||", $question['sorting_answers']);
$question['sorting_answers'] = str_replace("\r", "|||", $question['sorting_answers']);
// handle true/false questions
if ($question['answer_type'] == 'radio' and $question['truefalse']) {
$question['answer_type'] = "true/false";
}
$row = "";
if (empty($_GET['copy'])) {
$row .= $question['ID'] . "\t";
}
$row .= '"' . stripslashes($question['question']) . '"' . "\t" . $question['answer_type'] . "\t" . $question['sort_order'] . "\t" . $question['category'] . "\t" . '"' . stripslashes($question['explain_answer']) . '"' . "\t" . $question['is_required'] . "\t" . $question['correct_condition'] . "\t" . $question['correct_gap_points'] . "/" . $question['incorrect_gap_points'] . "\t" . '"' . stripslashes($question['sorting_answers']) . '"' . "\t" . $question['max_selections'] . "\t" . $question['is_inactive'] . "\t" . $question['is_survey'] . "\t" . $question['elaborate_explanation'];
// new export - adds the new fields
if (empty($_GET['legacy'])) {
$row .= "\t" . $question['open_end_mode'] . "\t" . '"' . $question['tags'] . '"' . "\t" . $question['open_end_display'] . "\t" . $question['exclude_on_final_screen'] . "\t" . '"' . $question['hints'] . '"' . "\t" . $question['compact_format'] . "\t" . $question['round_points'] . "\t" . $question['importance'];
}
foreach ($question['answers'] as $answer) {
if (empty($_GET['copy'])) {
$row .= "\t" . $answer->ID;
}
$row .= "\t" . '"' . stripslashes($answer->answer) . '"' . "\t" . $answer->correct . "\t" . $answer->point;
}
$row = str_replace("\n", "", $row);
$row = str_replace("\r", "", $row);
$rows[] = $row;
}
$csv = implode($newline, $rows);
// credit to http://yoast.com/wordpress/users-to-csv/
$now = gmdate('D, d M Y H:i:s') . ' GMT';
if (empty($_GET['copy'])) {
$filename = 'exam-' . $_GET['exam_id'] . '-questions-edit.csv';
} else {
$filename = 'exam-' . $_GET['exam_id'] . '-questions.csv';
}
header('Content-Type: ' . watupro_get_mime_type());
header('Expires: ' . $now);
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Pragma: no-cache');
echo $csv;
exit;
}
static function tests($report_user_id, $has_tabs = true)
{
// details about taken exams
global $wpdb;
// select all taken exams along with exam data
$sql = "SELECT COUNT(tA.ID) as cnt_answers, tT.*, tE.name as name, tT.exam_id as exam_id,\n\t\t\ttE.published_odd as published_odd, tE.published_odd_url as published_odd_url \n\t\t FROM " . WATUPRO_TAKEN_EXAMS . " tT, " . WATUPRO_EXAMS . " tE, " . WATUPRO_STUDENT_ANSWERS . " tA, " . WATUPRO_QUESTIONS . " tQ \n\t\t\tWHERE tT.user_id=%d AND tT.in_progress=0 AND tT.exam_id=tE.ID AND tA.taking_id = tT.id\t\n\t\t\tAND tA.question_id=tQ.ID AND tQ.is_survey=0\n\t\t\tGROUP BY tT.ID ORDER BY tT.ID DESC";
$exams = $wpdb->get_results($wpdb->prepare($sql, $report_user_id));
$posts = $wpdb->get_results("SELECT * FROM {$wpdb->posts} \n\t\tWHERE post_content LIKE '%[watupro %]%' \n\t\tAND post_status='publish'\n\t\tORDER BY post_date DESC");
// match posts to exams
foreach ($exams as $cnt => $exam) {
$exams[$cnt]->time_spent = self::time_spent($exam);
foreach ($posts as $post) {
if (stristr($post->post_content, "[WATUPRO " . $exam->exam_id . "]")) {
$exams[$cnt]->post = $post;
break;
}
}
}
if (!empty($_GET['export'])) {
$newline = watupro_define_newline();
$rows = array();
$rows[] = __('Quiz name', 'watupro') . "\t" . __('Time spent', 'watupro') . "\t" . __('Problems attempted', 'watupro') . "\t" . __('Grade', 'watupro') . "\t" . __('Points', 'watupro') . "\t" . __('Percent correct', 'watupro');
foreach ($exams as $exam) {
$result = stripslashes($exam->result);
$result = str_replace(array("\t", "\r", "\n"), array(" ", " ", " "), $result);
$rows[] = stripslashes($exam->name) . "\t" . self::time_spent_human($exam->time_spent) . "\t" . $exam->cnt_answers . "\t" . $result . "\t" . $exam->points . "\t" . $exam->percent_correct;
}
$csv = implode($newline, $rows);
$now = gmdate('D, d M Y H:i:s') . ' GMT';
header('Content-Type: ' . watupro_get_mime_type());
header('Expires: ' . $now);
header('Content-Disposition: attachment; filename="user-' . $report_user_id . '.csv"');
header('Pragma: no-cache');
echo $csv;
exit;
}
wp_enqueue_script('thickbox', null, array('jquery'));
wp_enqueue_style('thickbox.css', '/' . WPINC . '/js/thickbox/thickbox.css', null, '1.0');
if (@file_exists(get_stylesheet_directory() . '/watupro/reports/tests.php')) {
require get_stylesheet_directory() . '/watupro/reports/tests.php';
} else {
require WATUPRO_PATH . "/modules/reports/views/tests.php";
}
}
static function all_answers($in_shortcode = false)
{
global $wpdb;
$ob = empty($_GET['ob']) ? "tA.ID" : $_GET['ob'];
$dir = empty($_GET['dir']) ? "DESC" : $_GET['dir'];
if (!in_array($dir, array("ASC", "DESC"))) {
$dir = "DESC";
}
$odir = $dir == 'ASC' ? 'DESC' : 'ASC';
$offset = empty($_GET['offset']) ? 0 : intval($_GET['offset']);
$date_format = get_option('date_format');
$page_limit = 20;
$limit_sql = empty($_GET['export']) ? "LIMIT {$offset}, {$page_limit}" : "";
// select exam
$exam = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . WATUPRO_EXAMS . " WHERE ID=%d", $_GET['exam_id']));
// select question
$question = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . WATUPRO_QUESTIONS . " WHERE ID=%d", $_GET['id']));
// select all user answers joined, ordered and paginated
$answers = $wpdb->get_results($wpdb->prepare("SELECT SQL_CALC_FOUND_ROWS tA.*, tT.date as date, tT.ip as ip, \n\t\tCONCAT(tT.email, tU.user_email) as email, tU.display_name as display_name\n\t\tFROM " . WATUPRO_STUDENT_ANSWERS . " tA JOIN " . WATUPRO_TAKEN_EXAMS . " tT ON tA.taking_id = tT.ID\n\t\tLEFT JOIN {$wpdb->users} tU ON tU.ID = tA.user_id\n\t\tWHERE tA.exam_id = %d AND tA.question_id = %d AND tT.in_progress = 0\n\t\tORDER BY {$ob} {$dir} {$limit_sql}", $exam->ID, $question->ID));
$count = $wpdb->get_var("SELECT FOUND_ROWS()");
if (!empty($_GET['export'])) {
$newline = watupro_define_newline();
$rows = array();
$titlerow = __('User name', 'watupro') . "\t" . __('Email', 'watupro') . "\t" . __('IP', 'watupro') . "\t" . __('Date', 'watupro') . "\t" . __('Answer', 'watupro') . "\t" . __('Points', 'watupro') . "\t" . __('Is Correct?', 'watupro');
if (watupro_intel()) {
$titlerow .= "\t" . __('Teacher comments', 'watupro');
}
if (!empty($exam->question_hints) and !empty($question->hints)) {
$titlerow .= "\t" . __('Hints used', 'watupro');
}
$rows[] = $titlerow;
foreach ($answers as $answer) {
// cleanup
$answer->answer = str_replace("\t", " ", $answer->answer);
$answer->answer = str_replace('"', "'", $answer->answer);
$answer->teacher_comments = str_replace("\t", " ", $answer->teacher_comments);
$answer->teacher_comments = str_replace('"', "'", $answer->teacher_comments);
$answer->hints_used = str_replace("\t", " ", $answer->hints_used);
$answer->hints_used = str_replace('"', "'", $answer->hints_used);
$answer->hints_used = str_replace("</div>", "; ", $answer->hints_used);
$answer->hints_used = strip_tags($answer->hints_used);
$row = "";
$row .= $answer->user_id ? $answer->display_name : __("N/A", 'watupro');
$row .= "\t" . ($answer->email ? $answer->email : __("N/A", 'watupro'));
$row .= "\t" . $answer->ip;
$row .= "\t" . date($date_format, strtotime($answer->date));
$row .= "\t" . $answer->answer;
$row .= "\t" . $answer->points;
$row .= "\t" . ($answer->is_correct ? __('Yes', 'watupro') : __('No', 'watupro'));
if (watupro_intel()) {
$row .= "\t" . $answer->teacher_comments;
}
if (!empty($exam->question_hints) and !empty($question->hints)) {
$row .= "\t" . ($answer->num_hints_used ? sprintf(__('%d hints used:', 'watupro'), $answer->num_hints_used) . " " . trim($answer->hints_used) : __('No hints used', 'watupro'));
}
// remove new lines
$row = str_replace("\n", " ", $row);
$row = str_replace("\r", " ", $row);
$row = stripslashes($row);
$rows[] = $row;
}
$csv = implode($newline, $rows);
$now = gmdate('D, d M Y H:i:s') . ' GMT';
header('Content-Type: ' . watupro_get_mime_type());
header('Expires: ' . $now);
header('Content-Disposition: attachment; filename="exam-' . $exam->ID . '-question-' . $question->ID . '.csv"');
header('Pragma: no-cache');
echo $csv;
exit;
}
if (@file_exists(get_stylesheet_directory() . '/watupro/reports/all-question-answers.html.php')) {
require get_stylesheet_directory() . '/watupro/reports/all-question-answers.html.php';
} else {
require WATUPRO_PATH . "/modules/reports/views/all-question-answers.html.php";
}
}