protected function _fetchQuestions($evaluation_id = false) { $id = $evaluation_id ? $evaluation_id : $this->getSanParam('id'); if ($id) { $ev = new Evaluation(); $source_row = $ev->findOrCreate($id); if ($source_row->id) { $title = $source_row->title; $question_rows = $ev->fetchAllQuestions($id); $qtext = array(); $qtype = array(); $qid = array(); foreach ($question_rows as $qr) { $qtext[] = $qr->question_text; $qtype[] = $qr->question_type; $qid[] = $qr->id; } $answers = $ev->fetchCustomAnswers($id); if (!is_array($answers) || empty($answers)) { $answers = array(); } } } else { $this->_redirect('error'); } return array($title, $qtext, $qtype, $qid, $answers); }
public function rosterAction() { #ini_set('max_execution_time','120'); // these are now exceeded globally #ini_set('memory_limit', '256M'); $criteria['training_organizer_id'] = $this->getSanParam('training_organizer_id'); $criteria['training_title_id'] = $this->_getParam('training_title_id'); $criteria['is_extended'] = $is_extended = $this->getSanParam('is_extended'); $criteria['add_additional'] = $add_additional = $this->getSanParam('add_additional'); $criteria['go'] = $this->getSanParam('go'); //find the first date in the database $db = Zend_Db_Table_Abstract::getDefaultAdapter(); $sql = "SELECT MIN(training_start_date) as \"start\" FROM training WHERE is_deleted = 0"; $rowArray = $db->fetchAll($sql); $start_default = $rowArray[0]['start']; $parts = explode('-', $start_default); $criteria['start-year'] = $parts[0]; $criteria['start-month'] = $parts[1]; $criteria['start-day'] = $parts[2]; if ($this->getSanParam('start-year')) { $criteria['start-year'] = $this->getSanParam('start-year'); } if ($this->getSanParam('start-month')) { $criteria['start-month'] = $this->getSanParam('start-month'); } if ($this->getSanParam('start-day')) { $criteria['start-day'] = $this->getSanParam('start-day'); } if ($this->view->mode == 'search') { $sql = "SELECT MAX(training_start_date) as \"start\" FROM training "; $rowArray = $db->fetchAll($sql); $end_default = $rowArray[0]['start']; $parts = explode('-', $end_default); $criteria['end-year'] = $parts[0]; $criteria['end-month'] = $parts[1]; $criteria['end-day'] = $parts[2]; } else { $criteria['end-year'] = date('Y'); $criteria['end-month'] = date('m'); $criteria['end-day'] = date('d'); } if ($this->getSanParam('end-year')) { $criteria['end-year'] = $this->getSanParam('end-year'); } if ($this->getSanParam('end-month')) { $criteria['end-month'] = $this->getSanParam('end-month'); } if ($this->getSanParam('end-day')) { $criteria['end-day'] = $this->getSanParam('end-day'); } if ($criteria['go']) { try { // select trainings $sql = "SELECT id FROM training "; $where = "WHERE is_deleted=0"; // where // restricted access?? only show trainings we have the ACL to view require_once 'views/helpers/TrainingViewHelper.php'; $org_allowed_ids = allowed_organizer_access($this); if ($org_allowed_ids) { // doesnt have acl 'training_organizer_option_all' $org_allowed_ids = implode(',', $org_allowed_ids); $where .= " AND training_organizer_option_id in ({$org_allowed_ids}) "; } // restricted access?? only show organizers that belong to this site if its a multi org site $site_orgs = allowed_organizer_in_this_site($this); // for sites to host multiple training organizers on one domain $where .= $site_orgs ? " AND training_organizer_option_id in ({$site_orgs}) " : ""; if ($criteria['training_organizer_id'][0]) { $where .= " AND training_organizer_option_id in (" . implode(',', $criteria['training_organizer_id']) . ")"; } if ($criteria['training_title_id'][0]) { $where .= " AND training_title_option_id in (" . implode(',', $criteria['training_title_id']) . ")"; } if (intval($criteria['start-year'])) { if (strlen($where)) { $where .= ' AND '; } $startDate = $criteria['start-year'] . '-' . $criteria['start-month'] . '-' . $criteria['start-day']; $where .= ' training_start_date >= \'' . $startDate . '\' '; } if (intval($criteria['end-year'])) { if (strlen($where)) { $where .= ' AND '; } $endDate = $criteria['end-year'] . '-' . $criteria['end-month'] . '-' . $criteria['end-day']; $where .= ' training_start_date <= \'' . $endDate . '\' '; } $sql .= $where; $rowArray = $db->fetchAll($sql); // now we have trainings, lets get participants trainers and evaluations require_once 'models/table/Training.php'; require_once 'models/table/TrainingToTrainer.php'; require_once 'models/table/PersonToTraining.php'; require_once 'models/table/Evaluation.php'; $tableObj = new Training(); $output = array(); echo '<html><body>'; if (!$rowArray) { echo 'No trainings found.'; } $locations = Location::getAll(); $answers = $db->fetchAll('SELECT * FROM evaluation_question_response'); $responselist = $db->fetchAll('SELECT *,evaluation_response.id as evaluation_response_id FROM evaluation_response LEFT JOIN evaluation_to_training ett ON ett.id = evaluation_response.evaluation_to_training_id ORDER BY training_id ASC'); // response list is basically a hash of training_id, evaluation_to_training_id, evaluation_id, evaluation_response.id, and trainer_person_id, cool! $questionz = array(); // loop through trainings foreach ($rowArray as $row) { if (!isset($row['id']) || empty($row['id'])) { continue; } $rowRay = @$tableObj->getTrainingInfo($row['id']); // calculate end date switch ($rowRay['training_length_interval']) { case 'week': $days = $rowRay['training_length_value'] * 7; break; case 'day': $days = $rowRay['training_length_value'] - 1; // start day counts as a day? break; default: $days = false; break; } if ($days) { $rowRay['training_end_date'] = strtotime("+{$days} day", strtotime($rowRay['training_start_date'])); $rowRay['training_end_date'] = date('Y-m-d', $rowRay['training_end_date']); } else { $rowRay['training_end_date'] = $rowRay['training_start_date']; } $rowRay['duration'] = $rowRay['training_length_value'] . ' ' . $rowRay['training_length_interval'] . ($rowRay['training_length_value'] == 1 ? "" : "s"); if (!$rowRay['training_title']) { $rowRay['training_title'] = t('Unknown'); } echo "\r\n\t\t\t\t<p>\r\n\t\t\t\t<strong>" . t('Training') . ' ' . t('ID') . ":</strong> {$rowRay['id']}<br />\r\n\t\t\t\t<strong>" . t('Training') . ' ' . t('Name') . ":</strong> {$rowRay['training_title']}<br />\r\n\t\t\t\t<strong>" . t('Training Center') . ":</strong> {$rowRay['training_location_name']}<br />\r\n\t\t\t\t<strong>" . t('Dates') . ":</strong> {$rowRay['training_start_date']}" . ($rowRay['training_start_date'] != $rowRay['training_end_date'] ? ' - ' . $rowRay['training_end_date'] : '') . "<br />\r\n\t\t\t\t<strong>" . t('Course Length') . ":</strong> {$rowRay['duration']}<br />\r\n\t\t\t\t<strong>" . t('Training') . ' ' . t('Topic') . ":</strong> {$rowRay['training_topic_phrase']}<br />\r\n\t\t\t\t<strong>" . t('Training') . ' ' . t('Level') . ":</strong> {$rowRay['training_level_phrase']}<br />\r\n\t\t\t\t" . ($rowRay['training_got_curriculum_phrase'] ? "<strong>" . $this->tr('GOT Curriculum') . "</strong>: {$rowRay['training_got_curriculum_phrase']}<br />" : '') . "\r\n\t\t\t\t" . ($rowRay['got_comments'] ? "<strong>" . $this->tr('GOT Comment') . "</strong>: {$rowRay['got_comments']}<br />" : '') . "\r\n\t\t\t\t" . ($rowRay['comments'] ? "<strong>" . $this->tr('Comments') . "</strong>: {$rowRay['comments']}<br />" : "") . "\r\n\t\t\t\t" . ($rowRay['pepfar'] ? "<strong>" . $this->tr('PEPFAR Category') . ":</strong> {$rowRay['pepfar']}<br />" : "") . "\r\n\t\t\t\t" . ($rowRay['objectives'] ? "<strong>" . $this->tr('Course Objectives') . ":</strong> " . nl2br($rowRay['objectives']) : '') . "\r\n\t\t\t\t</p>\r\n\t\t\t\t"; // evaluations $question_lookup = array(); // questions needed by attached evaluations foreach ($responselist as $responserow) { if ($responserow['training_id'] > $row['id']) { break; } // speed up, its sorted if ($responserow['training_id'] != $row['id']) { continue; } // found a valid training/repsonse combo, lets attach the answers and questions to the training results for EZness if (!isset($row['questions'])) { $row['questions'] = array(); } // get ans foreach ($answers as $key => $value) { if ($value['evaluation_response_id'] == $responserow['evaluation_response_id']) { if (!isset($row['answers'])) { $row['answers'][$responserow['evaluation_id']][$responserow['evaluation_response_id']] = array(''); } // training['answers'][response_id][question_id] => answer $row['answers'][$responserow['evaluation_id']][$responserow['evaluation_response_id']][$value['evaluation_question_id']] = $value['value_text'] ? $value['value_text'] : $value['value_int']; } } // get q $question_lookup[] = $responserow['evaluation_id']; } // get all questions (usually a larger table than responses) foreach (array_unique($question_lookup) as $eval_id) { if (!trim($eval_id)) { continue; } if (!isset($questionz[$eval_id])) { // fetch once $questionz[$eval_id] = @Evaluation::fetchAllQuestions($eval_id)->toArray(); } } // evals now in rowRay['answers'], questions in $questionz //end evaluations /* Trainers */ $trainers = @TrainingToTrainer::getTrainers($row['id'])->toArray(); if ($trainers) { echo ' <table border="1" style="border-collapse:collapse;" cellpadding="3"> <caption style="text-align:left;"><em>' . t('Course') . ' ' . t('Trainers') . '</em></caption> <tr> <th>' . $this->tr('Last Name') . '</th> <th>' . $this->tr('First Name') . '</th> <th>' . t('Days') . '</th> </tr> '; foreach ($trainers as $tRow) { echo "\r\n\t\t\t\t\t<tr>\r\n\t\t\t\t\t<td>{$tRow['last_name']}</td>\r\n\t\t\t\t\t<td>{$tRow['first_name']}</td>\r\n\t\t\t\t\t<td>{$tRow['duration_days']}</td>\r\n\t\t\t\t\t</tr>\r\n\t\t\t\t\t"; } echo '</table><br>'; } $persons = @PersonToTraining::getParticipants($row['id'])->toArray(); echo ' <table border="1" style="border-collapse:collapse;" cellpadding="3"> <caption style="text-align:left;"><em>' . t('Course Participants') . '</em></caption> <tr>'; $headers = array(); $headers[] = $this->tr('Last Name'); if ($this->setting('display_middle_name')) { $headers[] = $this->tr('Middle Name'); } $headers[] = $this->tr('First Name'); $headers[] = t('Date of Birth'); $headers[] = $this->tr('Facility'); if ($add_additional) { $headers[] = $this->tr('Region A (Province)'); if ($this->setting('display_region_b')) { $headers[] = $this->tr('Region B (Health District)'); } if ($this->setting('display_region_c')) { $headers[] = $this->tr('Region C (Local Region)'); } if ($this->setting('display_region_d')) { $headers[] = $this->tr('Region D'); } if ($this->setting('display_region_e')) { $headers[] = $this->tr('Region E'); } if ($this->setting('display_region_f')) { $headers[] = $this->tr('Region F'); } if ($this->setting('display_region_g')) { $headers[] = $this->tr('Region G'); } if ($this->setting('display_region_h')) { $headers[] = $this->tr('Region H'); } if ($this->setting('display_region_i')) { $headers[] = $this->tr('Region I'); } $headers[] = t('Primary Qualification'); $headers[] = t('Secondary Qualification'); } if ($this->setting('module_attendance_enabled')) { $headers[] = t('Days Attended'); $headers[] = t('Complete'); } if ($is_extended) { $headers[] = t('Pre-Test'); $headers[] = t('Post-Test'); $headers[] = t('Change in Score'); } /* Participants */ // map each score-other to a hash $scores = array(); $scoreOtherHeaders = array(); foreach ($persons as $r) { if (!$r['person_id']) { continue; } $keys = explode(',', $r['score_other_k']); $values = explode(',', $r['score_other_v']); foreach ($keys as $i => $k) { $k = trim($k); if ($k) { $scores[$r['person_id']][$k] = $values[$i]; $scoreOtherHeaders[] = $k; } } } $scoreOtherHeaders = array_unique($scoreOtherHeaders); foreach ($scoreOtherHeaders as $h) { $headers[] = $h; } echo '<th>' . implode('</th><th>', $headers); echo '</th></tr>'; /* Participants */ foreach ($persons as $r) { if (is_numeric($r['score_percent_change'])) { // add percent if ($r['score_percent_change'] > 0) { $r['score_percent_change'] = "+" . $r['score_percent_change']; } $r['score_percent_change'] = "{$r['score_percent_change']}%"; } $r['score_change'] = ''; if ($r['score_post']) { $r['score_change'] = $r['score_post'] - $r['score_pre']; } echo "<tr><td>"; $body_fields = array(); $body_fields[] = $r['last_name']; if ($this->setting('display_middle_name')) { $body_fields[] = $r['middle_name']; } $body_fields[] = $r['first_name']; $body_fields[] = $r['birthdate']; $body_fields[] = $r['facility_name']; if ($add_additional) { $region_ids = Location::getCityInfo($r['location_id'], $this->setting('num_location_tiers'), $locations); $region_ids = Location::cityInfotoHash($region_ids); $body_fields[] = $locations[$region_ids['province_id']]['name']; if ($this->setting('display_region_b')) { $body_fields[] = $locations[$region_ids['district_id']]['name']; } if ($this->setting('display_region_c')) { $body_fields[] = $locations[$region_ids['region_c_id']]['name']; } if ($this->setting('display_region_d')) { $body_fields[] = $locations[$region_ids['region_d_id']]['name']; } if ($this->setting('display_region_e')) { $body_fields[] = $locations[$region_ids['region_e_id']]['name']; } if ($this->setting('display_region_f')) { $body_fields[] = $locations[$region_ids['region_f_id']]['name']; } if ($this->setting('display_region_g')) { $body_fields[] = $locations[$region_ids['region_g_id']]['name']; } if ($this->setting('display_region_h')) { $body_fields[] = $locations[$region_ids['region_h_id']]['name']; } if ($this->setting('display_region_i')) { $body_fields[] = $locations[$region_ids['region_i_id']]['name']; } if (!$r['primary_qualification'] or $r['primary_qualification'] == 'unknown') { $body_fields[] = $r['qualification']; $body_fields[] = ''; } else { $body_fields[] = $r['primary_qualification']; $body_fields[] = $r['qualification']; } // $body_fields[] = $r['primary_responsibility']; // $body_fields[] = $r['secondary_responsibility']; } if ($this->setting('module_attendance_enabled')) { $body_fields[] = $r['duration_days']; $body_fields[] = $r['award_id'] ? $r['award_id'] : ''; } if ($is_extended) { $body_fields[] = $r['score_pre']; $body_fields[] = $r['score_post']; $body_fields[] = $r['score_change']; //custom scores $pid = $r['person_id']; foreach ($scoreOtherHeaders as $h) { $body_fields[] = $scores[$pid][$h] ? $scores[$pid][$h] : ' '; // TODO should show a ' ' on empty not space, TrainSMART standard } } echo implode('</td><td>', $body_fields); echo "</td></tr>"; } echo '</table><br>'; // evaluations if ($row['answers']) { echo ' <table border="1" style="border-collapse:collapse;" cellpadding="3"> <caption style="text-align:left;"><em>' . t('Evaluations') . '</em></caption>'; $qnames = array(); $answer = array(); $qids = array(); $last_eval_id = 0; foreach ($row['answers'] as $eval_id => $evalresponse) { $hdr_txt = ""; if ($eval_id != $last_eval_id) { // print header row foreach ($questionz as $evaluationid => $qArray) { if ($eval_id != $evaluationid) { continue; } foreach ($qArray as $q) { $ex = ''; $qids[] = $q['id']; if ($q['question_type'] == 'Likert3' || $q['question_type'] == 'Likert3NA') { $ex = " (1-3)"; } if ($q['question_type'] == 'Likert' || $q['question_type'] == 'LikertNA') { $ex = " (1-5)"; } $hdr_txt .= '<th>' . $q['question_text'] . $ex . '</th>'; } break; } if ($hdr_txt) { echo "<tr>{$hdr_txt}</tr>"; } } foreach ($evalresponse as $reponseid => $answerrow) { // attempt has build evalation question list if (isset($answerrow[0]) && !$answerrow[0]) { unset($answerrow[0]); } // bugfix, one of my array() inits is wrong. TODO // pad results (missing answers wont line up in html table otherwise) foreach ($qids as $qid) { if (!isset($answerrow[$qid])) { $answerrow[$qid] = ' '; } // TODO should show a '-', TrainSMART standard } ksort($answerrow); // due to filling in missing answers above, need to resort here echo '<tr><td>' . implode('</td><td>', $answerrow) . '</td></tr>'; } } echo '</table><br>'; } // done echo '<br><hr size="1">'; } echo '</html></body>'; foreach ($output as $key => $value) { echo utf8_decode($value); echo PHP_EOL; } exit; } catch (Exception $e) { echo $e->getMessage() . '<br>' . PHP_EOL; die; } } //form drop downs $organizersArray = OptionList::suggestionList('training_organizer_option', 'training_organizer_phrase', false, false, false); $this->viewAssignEscaped('organizers', $organizersArray); $titlesArray = OptionList::suggestionList('training_title_option', 'training_title_phrase', false, false, false); $this->viewAssignEscaped('titles', $titlesArray); $this->view->assign('criteria', $criteria); }