/** * 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();