/**
* Get a more complete report- a data dump for analysis
* @global type $CFG
* @param type $formID
* @return PHPExcel an Excel spreadsheet containing a detailed report of the results of an assessment session
*/
public function getFullReportAsExcel($session_ID)
{
$overviewXML = simplexml_load_string($this->getSummaryReportForExamInstance($session_ID));
// print("form ID is: $session_ID <br/>");
global $CFG;
// questions labels.
// alphabet array
$alphabetArr = array();
foreach (range('A', 'Z') as $i) {
$alphabetArr[] = $i;
}
// this extends the possible spreadsheet cells a bit.
foreach (range('A', 'Z') as $i) {
$alphabetArr[] = "A" . $i;
}
foreach (range('A', 'Z') as $i) {
$alphabetArr[] = "B" . $i;
}
foreach (range('A', 'Z') as $i) {
$alphabetArr[] = "C" . $i;
}
// There shouldn't be more than, say, 70
$j = 0;
// get student sessions
$sessionsXMLStr = "<data>";
try {
$conn = new PDO("mysql:host={$CFG->db};dbname={$CFG->schema}", $CFG->dbuser, $CFG->dbuserpass);
} catch (PDOException $e) {
die('<data><error>failed connecting to database</error><detail>' . $e->getMessage() . '</detail></data>');
}
$query = "SELECT a.ID, a.student_id, a.overall_rating, a.additional_rating, a.site_ID,\n (SELECT fname FROM students WHERE students.ID = a.student_id) as fname, \n (SELECT lname FROM students WHERE students.ID = a.student_id) as lname, \n (SELECT studentnum FROM students WHERE students.ID = a.student_id) as studentnum, a.form_id, a.start_timestamp\n from student_exam_sessions a inner join exam_instances b on a.form_ID = b.ID WHERE a.form_ID = :session_ID";
$stmt = $conn->prepare($query);
$stmt->bindValue(':session_ID', $session_ID, PDO::PARAM_INT);
$stmt->execute() or die('<data><error>getSummaryReportForExamInstance query failed</error><detail>' . $stmt->errorCode() . '</detail></data>');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$sessionsXMLStr .= "<session><id>{$row['ID']}</id><student_id>{$row['student_id']}</student_id><fname>{$row['fname']}</fname><lname>{$row['lname']}</lname><studentnum>{$row['studentnum']}</studentnum><siteid>{$row['site_ID']}</siteid><created_timedate>{$row['start_timestamp']}</created_timedate></session>";
}
$sessionsXMLStr .= "</data>";
// print($query);
$sessionsXML = simplexml_load_string($sessionsXMLStr);
// get question definitions for this session
$enumlib = new EnumLib();
$questionsXML = simplexml_load_string($enumlib->getQuestionsForSession($session_ID));
// create excel sheet
$phpexcelObj = new PHPExcel();
// make a new Excel sheet
$quantitativeWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Quantitative Cohort Outcomes');
$phpexcelObj->addSheet($quantitativeWorksheet, 0);
// set labels
$quantitativeWorksheet->getColumnDimension("A")->setWidth(200);
$quantitativeWorksheet->setCellValue('A1', "Quantitative Cohort Outcomes for: {$overviewXML->summary->description} {$overviewXML->summary->examdate}, n=" . count($sessionsXML->session));
$quantitativeWorksheet->getStyle('A1')->getFont()->setSize(16);
$quantitativeWorksheet->setCellValue('A2', "Item");
$quantitativeWorksheet->getStyle("A2")->getFont()->setBold(true);
// $quantitativeWorksheet->setCellValue('C2', "Percentage");
// $quantitativeWorksheet->getStyle("C2")->getFont()->setBold(true);
// $quantitativeWorksheet->getColumnDimension("C")->setAutoSize(true);
$questionscount = 0;
$questionsArr = array();
$k = 3;
//
foreach ($questionsXML->question as $question) {
//set all criteria labels
$quantitativeWorksheet->setCellValue("A{$k}", $question->text);
// $quantitativeWorksheet->getStyle("A$k")->getFont()->setBold(true);
$quantitativeWorksheet->getColumnDimension("A")->setWidth(150);
// get the criteria
$currentColumn = 1;
$criteriaDef = simplexml_load_string($enumlib->getCriteriaScaleItems($overviewXML->summary->scale_id));
foreach ($criteriaDef->item as $item) {
$quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn]}2", "n marked {$item->short_description}");
$quantitativeWorksheet->getStyle("{$alphabetArr[$currentColumn]}2")->getFont()->setBold(true);
$quantitativeWorksheet->getColumnDimension("{$alphabetArr[$currentColumn]}")->setAutoSize(true);
$quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn + 1]}2", "% marked {$item->short_description}");
$quantitativeWorksheet->getStyle("{$alphabetArr[$currentColumn + 1]}2")->getFont()->setBold(true);
$quantitativeWorksheet->getColumnDimension("{$alphabetArr[$currentColumn + 1]}")->setAutoSize(true);
$query = "SELECT COUNT(answer) as total FROM student_exam_sessions_responses WHERE question_ID = :id AND answer = :answer AND student_exam_sessions_responses.student_exam_session_ID IN(SELECT ID FROM student_exam_sessions WHERE student_exam_sessions.form_ID = :session_ID)";
$stmt = $conn->prepare($query);
$stmt->bindValue(':id', $question->id, PDO::PARAM_INT);
$stmt->bindValue(':answer', $item->value, PDO::PARAM_STR);
$stmt->bindValue(':session_ID', $session_ID, PDO::PARAM_INT);
$stmt->execute() or die('<data><error>select query failed query failed</error><detail>' . $stmt->errorCode() . '</detail></data>');
$resultArr = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn]}{$k}", $row['total']);
$quantitativeWorksheet->setCellValue("{$alphabetArr[$currentColumn + 1]}{$k}", round($row['total'] / count($sessionsXML->session), 2) * 100 . "%");
}
$currentColumn += 2;
$stmt->closeCursor();
}
$k++;
}
// qualitative data
$criteriaWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Criteria Comments');
$phpexcelObj->addSheet($criteriaWorksheet, 1);
// set labels
$criteriaWorksheet->getColumnDimension("A")->setWidth(150);
$criteriaWorksheet->setCellValue('A1', "Qualitative Comments on Criteria Requiring Comments: {$overviewXML->summary->description} {$overviewXML->summary->examdate}, n=" . count($sessionsXML->session));
$criteriaWorksheet->getStyle('A1')->getFont()->setSize(16);
$criteriaWorksheet->setCellValue('A2', "Item");
$criteriaWorksheet->getStyle("A2")->getFont()->setBold(true);
$criteriaWorksheet->setCellValue('B2', "Comments");
$criteriaWorksheet->getStyle("B2")->getFont()->setBold(true);
$criteriaWorksheet->getColumnDimension("B")->setAutoSize(true);
$k = 3;
//
foreach ($questionsXML->question as $question) {
//set all criteria labels
// add up questions here
//$currentrow = 4;
//print('num'.$studentexamsession->studentnum);
$sql = "SELECT COUNT(*) FROM student_exam_sessions_responses WHERE question_ID = :id AND answer = 0 AND student_exam_sessions_responses.student_exam_session_ID IN(SELECT ID FROM student_exam_sessions WHERE student_exam_sessions.form_ID = :session_ID)";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':id', $question->id, PDO::PARAM_INT);
$stmt->bindValue(':session_ID', $session_ID, PDO::PARAM_INT);
if ($stmt->execute()) {
if ($stmt->fetchColumn() > 0) {
$stmt->closeCursor();
$criteriaWorksheet->setCellValue("A{$k}", html_entity_decode($question->text, ENT_QUOTES, 'UTF-8'));
// $quantitativeWorksheet->getStyle("A$k")->getFont()->setBold(true);
$criteriaWorksheet->getColumnDimension("A")->setAutoSize(true);
$query = "SELECT comments FROM student_exam_sessions_responses WHERE question_ID = :id AND answer IN (SELECT value FROM assessment_criteria_scales_items WHERE assessment_criteria_scale_typeID = :criteriaid AND needs_comment ='true')";
$stmt = $conn->prepare($query);
$stmt->bindValue(':id', $question->id, PDO::PARAM_INT);
$stmt->bindValue(':criteriaid', $overviewXML->summary->scale_id, PDO::PARAM_STR);
$stmt->execute() or die('<data><error>select query failed query failed</error><detail>' . $stmt->errorCode() . '</detail></data>');
$resultArr = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$criteriaWorksheet->setCellValue("B{$k}", $row['comments']);
$k++;
}
$stmt->closeCursor();
}
}
}
// qualitative data
$overallCommentsWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Overall Comments');
$phpexcelObj->addSheet($overallCommentsWorksheet, 2);
// set labels
$overallCommentsWorksheet->getColumnDimension("A")->setWidth(200);
$overallCommentsWorksheet->setCellValue('A1', "Qualitative Overall Comments: {$overviewXML->summary->description} {$overviewXML->summary->examdate}, n=" . count($sessionsXML->session));
$overallCommentsWorksheet->getStyle('A1')->getFont()->setSize(16);
$overallCommentsWorksheet->setCellValue('A2', "Comments");
$overallCommentsWorksheet->getStyle("A2")->getFont()->setBold(true);
$k = 3;
//
foreach ($overviewXML->session as $session) {
if (strlen($session->comments) > 0) {
$overallCommentsWorksheet->setCellValue("A{$k}", html_entity_decode($session->comments, ENT_QUOTES, 'UTF-8'));
// $quantitativeWorksheet->getStyle("A$k")->getFont()->setBold(true);
$k++;
}
}
$rawWorksheet = new PHPExcel_Worksheet($phpexcelObj, 'Raw Scores Data Dump');
$phpexcelObj->addSheet($rawWorksheet, 3);
// assessment raw data
$rawWorksheet->setCellValue('A1', "Assessment Raw Data: {$overviewXML->summary->description} {$overviewXML->summary->examdate}");
$rawWorksheet->getStyle('A1')->getFont()->setSize(16);
$rawWorksheet->setCellValue('A2', "Student Number");
$rawWorksheet->getStyle('A2')->getFont()->setBold(true);
$rawWorksheet->setCellValue('B2', "Student Name");
$rawWorksheet->getStyle('B2')->getFont()->setBold(true);
$rawWorksheet->setCellValue('C2', "Site ID");
$rawWorksheet->getStyle('C2')->getFont()->setBold(true);
$rawWorksheet->setCellValue('D2', "Assessment Timestamp");
$rawWorksheet->getStyle('D2')->getFont()->setBold(true);
// $rawWorksheet->getColumnDimension('B')->setAutoSize(true);
// $rawWorksheet->getColumnDimension('C')->setAutoSize(true);
$questionscount = 0;
$questionsArr = array();
$k = 0;
// get all criteria labels
foreach ($questionsXML->question as $question) {
// $criterias = simplexml_load_string($enumlib->getCriteriaForQuestion($question->id, false));
// foreach ($criterias as $criteria) {
// print($criteria->text . '<br/>');
if ($question->type != 'label') {
$rawWorksheet->setCellValue($alphabetArr[$k] . '2', $question->text);
$rawWorksheet->getStyle($alphabetArr[$k] . '2')->getFont()->setBold(true);
$rawWorksheet->getColumnDimension($alphabetArr[$k])->setAutoSize(true);
$questionsArr[] = array("id" => (string) $question->id, "column" => $alphabetArr[$k]);
// get student results, loop to populate all answers to this criteria
// can probably do this better...
$questionscount++;
$k++;
}
// }
}
// print('questionsArr:<br/>');
// print_r($questionsArr);
// populate student details
$currentrow = 4;
foreach ($sessionsXML->session as $studentexamsession) {
//print('num'.$studentexamsession->studentnum);
$rawWorksheet->setCellValue('A' . $currentrow, $studentexamsession->studentnum);
$rawWorksheet->setCellValue('B' . $currentrow, $studentexamsession->fname . ' ' . $studentexamsession->lname);
$rawWorksheet->setCellValue('C' . $currentrow, $studentexamsession->siteid);
$rawWorksheet->setCellValue('D' . $currentrow, $studentexamsession->created_timedate);
$rawWorksheet->setCellValue('E' . $currentrow, $studentexamsession->created_timedate);
$query = "SELECT * FROM student_exam_sessions_responses WHERE student_exam_session_ID = :sessionid";
$stmt = $conn->prepare($query);
$stmt->bindValue(':sessionid', $studentexamsession->id, PDO::PARAM_INT);
$stmt->execute() or die('<data><error>>select query failed query failed</error><detail>' . $stmt->errorCode() . '</detail></data>');
$resultArr = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$resultArr[] = array("id" => $row['question_ID'], "answer" => $row['answer']);
}
for ($i = 0; $i < count($questionsArr); $i++) {
$rawWorksheet->setCellValue($questionsArr[$i]['column'] . $currentrow, $this->findAnswer($questionsArr[$i]['id'], $resultArr));
}
$currentrow++;
$stmt->closeCursor();
}
$phpexcelObj->setActiveSheetIndex(0);
return $phpexcelObj;
}
}
if ($authlib->user_has_capability($loggedinuserdata->userID, $authlib::roles_view_reports)) {
$menuStr .= "<li><div style='vertical-align: middle; display:table;'>" . "<div style='display:table-cell; vertical-align:middle'><input type='image' src='{$CFG->wwwroot}{$CFG->basedir}icons/dialog-question.png' BORDER='0' onclick='showHelp(\"{$stringlib->get_string('reports_help')}\"); return false;'/></div>" . "<div style='float:left;display:table-cell; vertical-align:middle'>";
$menuStr .= $patharr[$i - 1] == 'reports' ? "<span class='currentmenulocation'>{$stringlib->get_string('reports_help')}</span>" : "<a href='javascript:window.location.assign(\"{$CFG->wwwroot}{$CFG->basedir}reports/\")'>{$stringlib->get_string('reports_index_label')}</a>";
}
if ($authlib->user_has_capability($loggedinuserdata->userID, $authlib::roles_view_assessments)) {
$menuStr .= "<li><div style='vertical-align: middle; display:table;'>" . "<div style='display:table-cell; vertical-align:middle'><input type='image' src='{$CFG->wwwroot}{$CFG->basedir}icons/dialog-question.png' BORDER='0' onclick='showHelp(\"{$stringlib->get_string('osce_archive_help')}\"); return false;'/></div>" . "<div style='float:left;display:table-cell; vertical-align:middle'>";
$menuStr .= $patharr[$i - 1] == 'archive' ? "<span class='currentmenulocation'>{$stringlib->get_string('eosce_archive')}</span>" : "<a href='javascript:window.location.assign(\"{$CFG->wwwroot}{$CFG->basedir}archive/\")'>{$stringlib->get_string('eosce_archive')}</a>";
$menuStr .= "</div></div></li>";
}
$menuStr .= "</ul><li>";
}
$from = isset($_REQUEST['from']) ? $_REQUEST['from'] > 0 ? $_REQUEST['from'] : 0 : 0;
$increment = 10;
$instanceData = simplexml_load_string($enumlib->getExamInstanceOverviewByID($_REQUEST['id']));
$questiondata = simplexml_load_string($enumlib->getQuestionsForSession($_REQUEST['id']));
// is this locked?
$locked = strval($instanceData->instance[0]->finalised) == 'true';
// exam active?
$active = strval($instanceData->instance[0]->active) == 'true';
// is it in practice mode?
$practicing = strval($instanceData->instance[0]->practicing) == 'true';
// is the currently logged on user an admin or the owner?
$isowner = strval($loggedinuserdata->userID) == strval($instanceData->instance[0]->owner_id) || $authlib->user_has_capability($loggedinuserdata->userID, $authlib::roles_finalise_other_assessment);
$listTableStr = "<table id='questionstbl'><thead><tr><th style='width:50px;'>Reorder</th>\n <th>Edit</th>\n <th>{$stringlib->get_string('assessment_item_list_type')}<input type='image' src='{$CFG->wwwroot}{$CFG->basedir}/icons/dialog-question.png' BORDER='0' style='vertical-align: middle; display:table-cell' onclick='showHelp(\"{$stringlib->get_string('item_type_help_string')}\"); return false;'/></th><th>{$stringlib->get_string('assessment_item_list_text')}</th><th>{$stringlib->get_string('assessment_item_list_remove')}</th></tr></thead><tbody>";
foreach ($questiondata->question as $question) {
$listTableStr .= "<tr class='sortablerow' entryid='{$question->id}'><td class='draghandle'><img src='{$CFG->wwwroot}{$CFG->basedir}/icons/object-flip-vertical.png'/></td>\n <td class='editimg'>\n <input type='image' src='{$CFG->wwwroot}{$CFG->basedir}/icons/gtk-edit.png' BORDER='0' style='vertical-align: middle; display:table-cell' onclick='edit_question_popup(\"{$question->id}\"); return false;'/></td>\n <td>" . ($question->type == '1' ? 'Yes' : 'No') . "</td>\n <td>{$question->text}</td>\n \n ";
$listTableStr .= "<td class='deletehandle'><input type='image' src='{$CFG->wwwroot}{$CFG->basedir}/icons/gtk-cancel.png' BORDER='0' style='vertical-align: text-bottom;' onclick='delete_question({$question->id}); return false;'/></td></tr>";
}
$listTableStr .= "</tbody></table>";
$enumLib = new EnumLib();
