/**
* Save Assignments (for admin page)
*/
public static function save($table, $parent_table, $option_table, $parent_id, $option_ids)
{
if (!is_numeric($parent_id) || !is_array($option_ids) || empty($option_ids)) {
return;
}
$option_ids = array_unique($option_ids);
$tableObj = new MultiAssignList(array('name' => $table));
$tableObj->delete("{$parent_table}_id={$parent_id}", true);
foreach ($option_ids as $id) {
if ($id) {
$createRow = $tableObj->createRow();
$createRow->{"{$parent_table}_id"} = $parent_id;
$createRow->{"{$option_table}_id"} = $id;
$createRow->save();
}
}
}
public function json()
{
$rowArray = MultiAssignList::adminList($this->table, $this->parent_table, key($this->parent_field), $this->option_table, key($this->option_field));
foreach ($rowArray as $key => $row) {
$rowArray[$key]['edit'] = '<a href="' . $this->url . '/edit/' . $row["{$this->parent_table}_id"] . '#edit">edit</a> ' . '<a href="' . $this->url . '/delete/' . $row["{$this->parent_table}_id"] . '" onclick="return confirm(\'Are you sure you wish to unasign this?\')">delete</a>';
}
return $rowArray;
}
private function doAddEditView()
{
if (!$this->hasACL('edit_course')) {
$this->view->assign('viewonly', 'disabled="disabled"');
$this->view->assign('pageTitle', t('View') . ' ' . t('Training'));
}
// edittable ajax (remove/update/etc)
if ($this->_getParam('edittable')) {
$this->ajaxeditTable();
return;
}
require_once 'models/table/MultiOptionList.php';
require_once 'models/table/TrainingLocation.php';
require_once 'models/table/Location.php';
require_once 'models/table/System.php';
require_once 'views/helpers/EditTableHelper.php';
require_once 'views/helpers/DropDown.php';
require_once 'views/helpers/FileUpload.php';
// allow multiple pepfars?
if (!$this->setting('allow_multi_pepfar')) {
$this->NUM_PEPFAR = 1;
}
// get translation labels
// $this->view->assign('labels', Translation::getAll());
//validate
$status = ValidationContainer::instance();
$request = $this->getRequest();
$validateOnly = $request->isXmlHttpRequest();
$training_id = $this->getSanParam('id');
$is_new = $this->getSanParam('new') || !$training_id;
// new training -- use defaults
$this->view->assign('is_new', $is_new);
$trainingObj = new Training();
$row = $trainingObj->findOrCreate($training_id);
$rowRay = @$row->toArray();
//filter training orgs by user access
$allowIds = false;
if (!$this->hasACL('training_organizer_option_all')) {
$allowIds = array();
$user_id = $this->isLoggedIn();
$training_organizer_array = MultiOptionList::choicesList('user_to_organizer_access', 'user_id', $user_id, 'training_organizer_option', 'training_organizer_phrase', false, false);
foreach ($training_organizer_array as $orgOption) {
if ($orgOption['user_id']) {
$allowIds[] = $orgOption['id'];
}
}
}
if ($this->_getParam('action') != 'add' and !$this->hasACL('training_organizer_option_all') and (!$allowIds or array_search($rowRay['training_organizer_option_id'], $allowIds) === false)) {
$this->view->assign('viewonly', 'disabled="disabled"');
$this->view->assign('pageTitle', t('View') . ' ' . t('Training'));
}
if ($row->is_deleted) {
$this->_redirect('training/deleted');
return;
}
$courseRow = $trainingObj->getCourseInfo($training_id);
$rowRay['training_title'] = $courseRow ? $courseRow->training_title_phrase : '';
$rowRay['training_title_option_id'] = $courseRow ? $courseRow->training_title_option_id : 0;
// does not exist
if (!$row->id && $this->_getParam('action') != 'add') {
$this->_redirect('training/index');
}
if ($validateOnly) {
$this->setNoRenderer();
}
$age_opts = OptionList::suggestionList('age_range_option', array('id', 'age_range_phrase'), false, 100, false);
if ($request->isPost() && !$this->getSanParam('edittabledelete')) {
//$status->checkRequired($this, 'training_title_option_id',t('Training Name'));
//$status->checkRequired($this, 'training_category_and_title_option_id',t('Training Name'));
$status->checkRequired($this, 'training_length_value', t('Training') . ' ' . t('Length'));
$status->checkRequired($this, 'training_length_interval', t('Training') . ' ' . t('Interval'));
//$status->checkRequired($this, 'training_organizer_option_id',t('Training Organizer'));
//$status->checkRequired($this, 'training_level_option_id',t('Training Level'));
//$status->checkRequired($this, 'training_location_id',t('Training Location'));
//$status->checkRequired($this, 'training_topic_option_id','Training Topic');
// May be "0" value
if (!$this->getSanParam('training_length_value')) {
$status->addError('training_length_value', t('Training length is required.'));
}
// validate score averages values
if ($score = trim($this->getSanParam('pre'))) {
if (!is_numeric($score)) {
$status->addError('pre', $this->view->translation['Pre Test Score'] . ' ' . t('must be numeric.'));
} elseif ($score < 0 || $score > 100) {
$status->addError('pre', $this->view->translation['Pre Test Score'] . ' ' . t('must be between 1-100.'));
}
}
if ($score = trim($this->getSanParam('post'))) {
if (!is_numeric($score)) {
$status->addError('post', $this->view->translation['Post Test Score'] . ' ' . t('must be numeric.'));
} elseif ($score < 0 || $score > 100) {
$status->addError('post', $this->view->translation['Post Test Score'] . ' ' . t('must be between 1-100.'));
}
}
if ($this->getSanParam('start-year') == "" || $this->getSanParam('start-month') == "" || $this->getSanParam('start-day') == "") {
$status->addError('start-day', t('Start date is required.'));
}
$training_start_date = @$this->getSanParam('start-year') . '-' . @$this->getSanParam('start-month') . '-' . @$this->getSanParam('start-day');
if ($training_start_date !== '--' and $training_start_date !== '0000-00-00') {
$status->isValidDate($this, 'start-day', t('Training') . ' ' . t('start'), $training_start_date);
}
if ($this->setting('display_end_date')) {
$training_end_date = @$this->getSanParam('end-year') . '-' . @$this->getSanParam('end-month') . '-' . @$this->getSanParam('end-day');
if ($training_end_date !== '--' and $training_end_date !== '0000-00-00') {
$status->isValidDate($this, 'end-day', t('Training') . ' ' . t('end'), $training_end_date);
}
if ($training_end_date != '--') {
if (strtotime($training_end_date) < strtotime($training_start_date)) {
$status->addError('end-day', t('End date must be after start date.'));
}
}
}
$pepfarEnabled = @$this->setting('display_training_pepfar');
if ($training_id) {
$pepfarCount = 0;
$pepfar_array = $this->getSanParam('training_pepfar_categories_option_id');
if ($pepfar_array) {
foreach ($pepfar_array as $p) {
if ($p) {
$pepfarCount++;
}
}
}
// if (!$pepfarCount) {
// $status->addError('training_pepfar_categories_option',t('PEPFAR is required.'));
// }
// pepfar (multiple days)
if ($this->getSanParam('pepfar_days') && $pepfarEnabled) {
$pepfarTotal = 0;
foreach ($this->getSanParam('pepfar_days') as $key => $value) {
if (!is_numeric($value)) {
$value = ereg_replace("/^[.0-9]", "", $value);
}
//$daysRay [$pepfar_array[$key]] = $value; //set the days key to the pepfar id
$daysRay[$key] = $value;
//set the days key to the pepfar id
$pepfarTotal += $value;
if ($pepfarCount > 1 && !$value && $pepfarEnabled) {
$status->addError('training_pepfar_categories_option', t('Number of days is required.'));
}
if ($pepfarCount == $key + 1) {
break;
}
}
// calculate days
switch ($this->getSanParam('training_length_interval')) {
case 'week':
$days = $this->getSanParam('training_length_value') * 7;
break;
case 'day':
$days = $this->getSanParam('training_length_value');
// start day counts as a day?
break;
default:
$days = 0.5;
break;
}
// do days add up to match training length?
if ($days != $pepfarTotal && $pepfarCount > 1 && $pepfarEnabled) {
$status->addError('training_pepfar_categories_option', sprintf(t("Total") . ' ' . t('Training') . ' ' . t("length is %s, but PEPFAR category total is %d days. "), $days == 1 ? $days . ' ' . t('day') : $days . ' ' . t('days'), $pepfarTotal));
}
}
// custom fields
if ($this->getSanParam('custom1_phrase')) {
$tableCustom = new ITechTable(array('name' => 'training_custom_1_option'));
$row->training_custom_1_option_id = $tableCustom->insertUnique('custom1_phrase', $this->getSanParam('custom1_phrase'), true);
}
if ($this->getSanParam('custom2_phrase')) {
$tableCustom = new ITechTable(array('name' => 'training_custom_2_option'));
$row->training_custom_2_option_id = $tableCustom->insertUnique('custom2_phrase', $this->getSanParam('custom2_phrase'), true);
}
$custom3 = $this->getSanParam('custom3_phrase');
$row->custom_3 = $custom3 ? $custom3 : '';
$custom4 = $this->getSanParam('custom4_phrase');
$row->custom_4 = $custom4 ? $custom4 : '';
// checkbox
if (!$this->getSanParam('is_tot')) {
$row->is_tot = 0;
}
if (!$this->getSanParam('is_refresher')) {
$row->is_refresher = 0;
}
$training_refresher_option_id = $this->getSanParam('training_refresher_option_id');
if (!empty($training_refresher_option_id)) {
$row->is_refresher = 1;
}
}
if ($this->_getParam('action') == 'add' && $this->_countrySettings['module_unknown_participants_enabled'] && !$this->getSanParam('has_known_participants')) {
$row->has_known_participants = 0;
} else {
if ($this->_getParam('action') == 'add') {
$row->has_known_participants = 1;
}
}
//approve by default if the approvals modules is not enabled
if ($this->_getParam('action') == 'add' && $this->_countrySettings['module_approvals_enabled'] && !$this->hasACL('approve_trainings')) {
$row->is_approved = 0;
} else {
if ($this->_getParam('action') == 'add') {
$row->is_approved = 1;
}
}
// delete training
if ($this->getSanParam('specialAction') == 'delete') {
$partys = PersonToTraining::getParticipants($training_id)->toArray();
$tranys = TrainingToTrainer::getTrainers($training_id)->toArray();
if (!$partys && !$tranys) {
$row->is_deleted = 1;
$trainingObj->delete('id = ' . $row->id);
} else {
$status->setStatusMessage(t('This') . ' ' . t('Training') . ' ' . t('session could not be deleted. Some participants or trainers may still be attached.'));
}
}
if ($status->hasError() && !$row->is_deleted) {
$status->setStatusMessage(t('This') . ' ' . t('Training') . ' ' . t('session could not be saved.'));
} else {
$row = self::fillFromArray($row, $this->_getAllParams());
// format: categoryid_titleid
$ct_ids = $this->getSanParam('training_category_and_title_option_id');
// remove category id and underscore (unless dynamic title insert, which is numeric)
$training_title_option_id = !is_numeric($ct_ids) ? substr($ct_ids, strpos($ct_ids, '_') + 1) : $ct_ids;
$row->training_title_option_id = $training_title_option_id;
$row->training_start_date = @$this->getSanParam('start-year') . '-' . @$this->getSanParam('start-month') . '-' . @$this->getSanParam('start-day');
if ($this->setting('display_end_date')) {
$row->training_end_date = @$this->getSanParam('end-year') . '-' . @$this->getSanParam('end-month') . '-' . @$this->getSanParam('end-day');
}
// cannot be null ... set defaults
if (!$row->comments) {
$row->comments = '';
}
if (!$row->got_comments) {
$row->got_comments = '';
}
if (!$row->objectives) {
$row->objectives = '';
}
if (!$row->is_tot) {
$row->is_tot = 0;
}
if (!$row->is_refresher) {
$row->is_refresher = 0;
}
// update related tables
if ($training_id) {
// funding
$amount_extra_col = '';
$amount_extra_vals = array();
$amount_extra_col = 'funding_amount';
if ($this->getSanParam('funding_id')) {
foreach ($this->getSanParam('funding_id') as $funding_id) {
$amount_extra_vals[] = $this->getSanParam('funding_id_amount_' . $funding_id);
}
}
MultiOptionList::updateOptions('training_to_training_funding_option', 'training_funding_option', 'training_id', $training_id, 'training_funding_option_id', $this->getSanParam('funding_id'), $amount_extra_col, $amount_extra_vals);
// pepfar
if ($pepfarEnabled) {
MultiOptionList::updateOptions('training_to_training_pepfar_categories_option', 'training_pepfar_categories_option', 'training_id', $training_id, 'training_pepfar_categories_option_id', $this->getSanParam('training_pepfar_categories_option_id'), 'duration_days', isset($daysRay) ? $daysRay : false);
}
// method
if ($this->setting('display_training_method')) {
$row->training_method_option_id = $this->getSanParam('training_method_option_id');
}
// topics
if (!$this->setting('allow_multi_topic')) {
// drop-down -- set up faux checkbox array (since table schema uses multiple choices)
$_GET['topic_id'][] = $this->getSanParam('training_topic_option_id');
}
MultiOptionList::updateOptions('training_to_training_topic_option', 'training_topic_option', 'training_id', $training_id, 'training_topic_option_id', $this->getSanParam('topic_id'));
// refresher course (if dropdownlist)
if ($this->setting('multi_opt_refresher_course')) {
MultiOptionList::updateOptions('training_to_training_refresher_option', 'training_refresher_option', 'training_id', $training_id, 'training_refresher_option_id', $this->getSanParam('training_refresher_option_id'));
}
//Qualifications for unknown participants
if (!$row->has_known_participants) {
//check for duplicates
///oooh, compound key = qual + age
//DELETE EVERYTHING FOR THIS TRAINING
//START OVER
$quals = $this->getSanParam('person_qualification_option_id');
$quantities_na = $this->getSanParam('qualification_quantity_na');
$quantities_male = $this->getSanParam('qualification_quantity_male');
$quantities_female = $this->getSanParam('qualification_quantity_female');
$age_ranges = $this->getSanParam('age_range_option_id');
$qualPlusAgeArray = array();
//make array of qualifications + age range
$qualRows = OptionList::suggestionListHierarchical('person_qualification_option', 'qualification_phrase', false, false);
foreach ($qualRows as $qRow) {
foreach (array_keys($age_opts) as $age_opt) {
$qualPlusAgeArray[$qRow['id']][$age_opt] = array('na' => 0, 'male' => 0, 'female' => 0);
}
}
foreach ($quals as $ix => $item) {
if ($item) {
$qualPlusAgeArray[$quals[$ix]][$age_ranges[$ix]]['na'] = $qualPlusAgeArray[$quals[$ix]][$age_ranges[$ix]]['na'] + $quantities_na[$ix];
$qualPlusAgeArray[$quals[$ix]][$age_ranges[$ix]]['male'] = $qualPlusAgeArray[$quals[$ix]][$age_ranges[$ix]]['male'] + $quantities_male[$ix];
$qualPlusAgeArray[$quals[$ix]][$age_ranges[$ix]]['female'] = $qualPlusAgeArray[$quals[$ix]][$age_ranges[$ix]]['female'] + $quantities_female[$ix];
}
}
$deleteTable = new ITechTable(array('name' => 'training_to_person_qualification_option'));
$deleteTable->delete('training_id = ' . $training_id, true);
foreach ($qualPlusAgeArray as $qkey => $ageRay) {
foreach ($ageRay as $akey => $counts) {
if ($counts['na'] || $counts['male'] || $counts['female']) {
MultiOptionList::insertOption('training_to_person_qualification_option', 'training_id', $training_id, 'person_qualification_option_id', $qkey, array('age_range_option_id', 'person_count_na', 'person_count_male', 'person_count_female'), array('age_range_option_id' => $akey, 'person_count_na' => $counts['na'], 'person_count_male' => $counts['male'], 'person_count_female' => $counts['female']));
}
}
}
}
}
//mark approval status
$do_save_approval_history = false;
if ($this->setting('module_approvals_enabled')) {
if ($this->getSanParam('approval_status') == 'approved') {
$row->is_approved = 1;
if ($this->setting('allow_multi_approvers') && !$this->hasACL('master_approver')) {
$row->is_approved = 2;
// approved, but not approved by master approver, only that user can make this a 1 and have it display aproved!
}
$rowRay['is_approved'] = 1;
$do_save_approval_history = true;
} else {
if ($this->getSanParam('approval_status') == 'rejected') {
$row->is_approved = 0;
$rowRay['is_approved'] = 0;
if ($this->setting('allow_multi_approvers') && !$this->hasACL('master_approver')) {
$row->is_approved = 1;
// approved, but not approved by master approver, only that user can make this a 1 and have it display aproved!
$rowRay['is_approved'] = 1;
}
$do_save_approval_history = true;
}
}
if ($this->_getParam('action') == 'add' or !$this->hasACL('approve_trainings')) {
$do_save_approval_history = true;
}
}
if ($this->_getParam('action') == 'add') {
$do_save_approval_history = true;
}
$row->training_refresher_option_id = 0;
// refresher / bugfix - this col isnt used anymore
if ($row->save()) {
//save approval history
if ($this->getSanParam('approval_comments') || $do_save_approval_history) {
require_once 'models/table/TrainingApprovalHistory.php';
$history_table = new TrainingApprovalHistory();
$approval_status = $this->_countrySettings['module_approvals_enabled'] ? $this->getSanParam('approval_status') : 'approved';
if (!$this->hasACL('approve_trainings')) {
$approval_status = 'resubmitted';
}
$history_data = array('training_id' => $row->id, 'approval_status' => $approval_status, 'message' => $this->getSanParam('approval_comments'));
$history_table->insert($history_data);
}
// redirects
if ($this->_getParam('action') == 'add') {
$status->redirect = Settings::$COUNTRY_BASE_URL . '/training/edit/id/' . $row->id . '/new/1';
}
if ($this->_getParam('redirectUrl')) {
$status->redirect = $this->_getParam('redirectUrl');
}
// duplicate training
if ($this->getSanParam('specialAction') == 'duplicate') {
if ($this->hasACL('duplicate_training')) {
$dupId = $trainingObj->duplicateTraining($row->id);
$status->redirect = Settings::$COUNTRY_BASE_URL . '/training/edit/id/' . $dupId . '/msg/duplicate';
}
}
if (!$status->redirect) {
$status->setStatusMessage(t('This') . ' ' . t('Training') . ' ' . t('session has been saved.'));
}
} else {
error_log("Couldn't save training {$training_id}");
}
}
if ($validateOnly) {
$this->sendData($status);
} else {
$this->view->assign('status', $status);
}
}
//
// Init view
//
$this->view->assign('custom3_phrase', $row->custom_3);
$this->view->assign('custom4_phrase', $row->custom_4);
//split start date fields
if (!$row->training_start_date) {
$row->training_start_date = '--';
}
// empty
$parts = explode(' ', $row->training_start_date);
$parts = explode('-', $parts[0]);
$rowRay['start-year'] = $parts[0];
$rowRay['start-month'] = $parts[1];
$rowRay['start-day'] = $parts[2];
//split end date fields
if (!$row->training_end_date) {
$row->training_end_date = '--';
}
// empty
$parts = explode(' ', $row->training_end_date);
$parts = explode('-', $parts[0]);
$rowRay['end-year'] = $parts[0];
$rowRay['end-month'] = $parts[1];
$rowRay['end-day'] = $parts[2];
// Drop downs
//$this->view->assign('dropDownTitle', DropDown::generateHtml('training_title_option','training_title_phrase',$rowRay['training_title_option_id'],($this->hasACL('training_title_option_all')?'training/insert-table':false), $this->view->viewonly,false));
$this->view->assign('dropDownOrg', DropDown::generateHtml('training_organizer_option', 'training_organizer_phrase', $rowRay['training_organizer_option_id'], $this->hasACL('training_organizer_option_all') ? 'training/insert-table' : false, $this->view->viewonly, $this->view->viewonly ? false : $allowIds));
$this->view->assign('dropDownLevel', DropDown::generateHtml('training_level_option', 'training_level_phrase', $rowRay['training_level_option_id'], 'training/insert-table', $this->view->viewonly));
$this->view->assign('dropDownGotCir', DropDown::generateHtml('training_got_curriculum_option', 'training_got_curriculum_phrase', $rowRay['training_got_curriculum_option_id'], 'training/insert-table', $this->view->viewonly));
$this->view->assign('dropDownMethod', DropDown::generateHtml('training_method_option', 'training_method_phrase', $rowRay['training_method_option_id'], 'training/insert-table', $this->view->viewonly));
$this->view->assign('dropDownPrimaryLanguage', DropDown::generateHtml('trainer_language_option', 'language_phrase', $rowRay['training_primary_language_option_id'], false, $this->view->viewonly, false, false, array('name' => 'training_primary_language_option_id')));
$this->view->assign('dropDownSecondaryLanguage', DropDown::generateHtml('trainer_language_option', 'language_phrase', $rowRay['training_secondary_language_option_id'], false, $this->view->viewonly, false, false, array('name' => 'training_secondary_language_option_id')));
//$catTitleArray = OptionList::suggestionList('location_district',array('district_name','parent_province_id'),false,false);
$this->view->assign('age_options', $age_opts);
// training categories & titles
$categoryTitle = MultiAssignList::getOptions('training_title_option', 'training_title_phrase', 'training_category_option_to_training_title_option', 'training_category_option');
$this->view->assign('categoryTitle', $categoryTitle);
// add title link
if ($this->hasACL('training_title_option_all')) {
$this->view->assign('titleInsertLink', " <a href=\"#\" onclick=\"addToSelect('" . str_replace("'", "\\" . "'", t('Please enter your new')) . " " . strtolower($this->view->translation['Training'] . t('Name')) . ":', 'select_training_title_option', '" . Settings::$COUNTRY_BASE_URL . "/training/insert-table/table/training_title_option/column/training_title_phrase/outputType/json'); return false;\">" . t('Insert new') . "</a>");
}
//get assigned evaluation
$ev_id = null;
$ev_to_t_id = null;
if ($training_id) {
$evtableObj = new ITechTable(array('name' => 'evaluation_to_training'));
$evRow = $evtableObj->fetchRow($evtableObj->select(array('id', 'evaluation_id'))->where('training_id = ' . $training_id));
if ($evRow) {
$ev_id = $evRow->evaluation_id;
$ev_to_t_id = $evRow->id;
}
$this->view->assign('evaluation_id', $ev_id);
$this->view->assign('evaluation_to_training_id', $ev_to_t_id);
}
//Qualifications for unknown participants
if (!$row->has_known_participants) {
//count primary qualifications.
//add a dropdown for each
$tableObj = new ITechTable(array('name' => 'person_qualification_option'));
$qualRows = OptionList::suggestionListHierarchical('person_qualification_option', 'qualification_phrase', false, false);
//get values for this training
$selectedObj = new ITechTable(array('name' => 'training_to_person_qualification_option'));
$selectedRows = null;
if ($training_id) {
$selectedRows = $selectedObj->fetchAll($selectedObj->select(array('person_qualification_option_id', 'id', 'person_count_na', 'person_count_male', 'person_count_female', 'age_range_option_id'))->where('training_id = ' . $training_id));
$unknownQualDropDowns = array();
$qual_row_count = 0;
foreach ($selectedRows as $selectedRow) {
$selector = array();
$selector['select'] = $this->_generate_hierarchical('select_person_qualification_option_' . $qual_row_count, $qualRows, 'qualification_phrase', $selectedRow->person_qualification_option_id);
$selector['age_range_option_id'] = $selectedRow->age_range_option_id;
$selector['quantity_na'] = $selectedRow->person_count_na;
$selector['quantity_male'] = $selectedRow->person_count_male;
$selector['quantity_female'] = $selectedRow->person_count_female;
$unknownQualDropDowns[] = $selector;
$qual_row_count++;
}
$max_rows = count($qualRows) * 3;
//should be about 30
for ($i = $selectedRows->count(); $i < $max_rows; $i++) {
$selector = array();
$selector['select'] = $this->_generate_hierarchical('select_person_qualification_option_' . $qual_row_count, $qualRows, 'qualification_phrase', -1);
$selector['age_range_option_id'] = 1;
$selector['quantity_na'] = 0;
$selector['quantity_male'] = 0;
$selector['quantity_female'] = 0;
$unknownQualDropDowns[] = $selector;
$qual_row_count++;
}
$this->view->assign('unknownQualDropDowns', $unknownQualDropDowns);
}
}
// find category based on title
$catId = 0;
if ($courseRow && $courseRow->training_title_option_id) {
foreach ($categoryTitle as $r) {
if ($r['id'] == $courseRow->training_title_option_id && $r['training_category_option_id'] != 0) {
$catId = $r['training_category_option_id'];
break;
}
}
}
$this->view->assign('dropDownCategory', DropDown::generateHtml('training_category_option', 'training_category_phrase', $catId, false, $this->view->viewonly, false));
//echo '<pre>';
//print_r($catTitleArray); exit;
// $this->view->assign('dropDownProvince', DropDown::generateHtml('location_province','province_name',false,false,$this->view->viewonly));
// $this->view->assign('dropDownDistrict', DropDown::generateHtml('location_district','district_name',false,false,$this->view->viewonly));
$this->viewAssignEscaped('locations', Location::getAll());
// Topic drop-down
if ($training_id) {
if (!$this->setting('allow_multi_topic')) {
$training_topic_id = $trainingObj->getTrainingSingleTopic($training_id);
if ($is_new) {
$training_topic_id = false;
}
// use default
$this->view->assign('dropDownTopic', DropDown::generateHtml('training_topic_option', 'training_topic_phrase', $training_topic_id, 'training/insert-table', $this->view->viewonly));
} else {
// topic checkboxes
$topicArray = MultiOptionList::choicesList('training_to_training_topic_option', 'training_id', $training_id, 'training_topic_option', 'training_topic_phrase');
$this->view->assign('topicArray', $topicArray);
$this->view->assign('topicJsonUrl', Settings::$COUNTRY_BASE_URL . '/training/insert-table/table/training_topic_option/column/training_topic_phrase/outputType/json');
}
}
if ($this->hasACL('acl_editor_training_topic')) {
$this->view->assign("topicInsertLink", ' <a href="#" onclick="addCheckbox(\'' . t('Please enter the name your new topic item') . '\', \'topic_id\', \'topicContainer\', \'' . $this->view->topicJsonUrl . '\'); return false;">' . t('Insert New') . '</a>');
}
// get custom phrases (custom1_phrase, custom2_phrase)
if ($training_id) {
$rowRay = array_merge($rowRay, $trainingObj->getCustom($training_id)->toArray());
}
// location drop-down
$tlocations = TrainingLocation::selectAllLocations($this->setting('num_location_tiers'));
$this->viewAssignEscaped('tlocations', $tlocations);
if ($this->hasACL('edit_facility')) {
$this->view->assign("insertLocationLink", '<a href="#" onclick="return false;" id="show">' . t(str_replace(' ', ' ', t('Insert new'))) . '</a>');
}
// pepfar durations
$pepfarEnabled = @$this->setting('display_training_pepfar');
if ($training_id && $pepfarEnabled) {
$pepfarArray = MultiOptionList::choicesList('training_to_training_pepfar_categories_option', 'training_id', $training_id, 'training_pepfar_categories_option', 'pepfar_category_phrase', 'duration_days');
foreach ($pepfarArray as $item) {
if (isset($item['training_id']) && $item['training_id']) {
$pepfars[] = array('id' => $item['id'], 'duration' => $item['duration_days']);
}
}
}
// pepfar
$this->view->assign('NUM_PEPFAR', $this->NUM_PEPFAR);
// number of Pepfar drop-downs to display
for ($j = 0; $j < $this->NUM_PEPFAR; $j++) {
$pepfarid = isset($pepfars[$j]['id']) ? $pepfars[$j]['id'] : '';
if ($is_new) {
$pepfarid = false;
}
// use default
$pepfarHtml = DropDown::generateHtml('training_pepfar_categories_option', 'pepfar_category_phrase', $pepfarid, false, $this->view->viewonly, false, false, array(), $j == 0);
$pepfarHtml = str_replace('training_pepfar_categories_option_id', 'training_pepfar_categories_option_id[]', $pepfarHtml);
// use array
$dropDownPepfars[] = $pepfarHtml;
//$pepfarDurations[] = (isset($pepfars[$j]['duration']) && $pepfars[$j]['duration'] ? $pepfars[$j]['duration'] . ' day' . (($pepfars[$j]['duration'] <= 1) ? '' : 's') : '');
$pepfarDurations[] = isset($pepfars[$j]['duration']) && $pepfars[$j]['duration'] ? $pepfars[$j]['duration'] : '';
}
$this->view->assign('dropDownPepfars', $dropDownPepfars);
$this->view->assign('pepfarDurations', $pepfarDurations);
// checkboxes
$fundingArray = MultiOptionList::choicesList('training_to_training_funding_option', 'training_id', $training_id, 'training_funding_option', array('funding_phrase', 'is_default'));
if ($training_id) {
//lame to do another query, but it's easy
$tableObj = new ITechTable(array('name' => 'training_to_training_funding_option'));
$amountRows = $tableObj->fetchAll($tableObj->select(array('training_funding_option_id', 'id', 'funding_amount'))->where('training_id = ' . $training_id));
foreach ($amountRows as $amt_row) {
foreach ($fundingArray as $k => $funding_row) {
if ($funding_row['id'] == $amt_row->training_funding_option_id) {
$fundingArray[$k]['funding_amount'] = $amt_row->funding_amount;
}
}
}
}
$this->view->assign('fundingArray', $fundingArray);
if ($this->hasACL('acl_editor_funding')) {
$this->view->assign('fundingJsonUrl', Settings::$COUNTRY_BASE_URL . '/training/insert-table/table/training_funding_option/column/funding_phrase/outputType/json');
$this->view->assign("fundingInsertLink", ' <a href="#" onclick="addCheckbox(\'' . t('Please enter the name your new funding item:') . '\', \'funding_id\', \'fundingContainer\', \'' . $this->view->fundingJsonUrl . '\'); return false;">' . t('Insert New') . '</a>');
}
// refresher (if multi)
if ($training_id) {
if ($this->setting('multi_opt_refresher_course')) {
$training_refresher_id = $row->training_refresher_option_id;
if ($is_new) {
$training_refresher_id = false;
}
// use default
#$this->view->assign ( 'dropDownRefresher', DropDown::generateHtml ( 'training_refresher_option', 'refresher_phrase_option', $training_refresher_id, 'training/insert-table', $this->view->viewonly ) );
$this->view->assign('refresherArray', MultiOptionList::choicesList('training_to_training_refresher_option', 'training_id', $training_id, 'training_refresher_option', 'refresher_phrase_option', false, false));
if ($this->hasACL('acl_editor_refresher_course')) {
$this->view->assign('refresherJsonUrl', Settings::$COUNTRY_BASE_URL . '/training/insert-table/table/training_refresher_option/column/refresher_phrase_option/outputType/json');
$this->view->assign("refresherInsertLink", ' <a href="#" onclick="addCheckbox(\'' . t('Please enter the name your new refresher item') . '\', \'training_refresher_option_id\', \'refresherContainer\', \'' . $this->view->refresherJsonUrl . '\'); return false;">' . t('Insert New') . '</a>');
}
}
}
/****************************************************************************************************************
* Trainers */
if ($training_id) {
$trainers = TrainingToTrainer::getTrainers($training_id)->toArray();
} else {
$trainers = array();
}
if (!$this->setting('display_middle_name')) {
$trainerFields = array('first_name' => $this->tr('First Name'), 'last_name' => $this->tr('Last Name'));
$colStatic = array('first_name', 'last_name');
} else {
if ($this->setting('display_middle_name_last')) {
$trainerFields = array('first_name' => $this->tr('First Name'), 'last_name' => $this->tr('Last Name'), 'middle_name' => $this->tr('Middle Name'));
$colStatic = array('first_name', 'last_name', 'middle_name');
} else {
$trainerFields = array('first_name' => $this->tr('First Name'), 'middle_name' => $this->tr('Middle Name'), 'last_name' => $this->tr('Last Name'));
$colStatic = array('first_name', 'middle_name', 'last_name');
}
}
if ($this->view->viewonly) {
$editLinkInfo['disabled'] = 1;
$linkInfo = array();
$colStatic = array_keys($trainerFields);
} else {
$linkInfo = array('linkFields' => $colStatic, 'linkId' => 'trainer_id', 'linkUrl' => Settings::$COUNTRY_BASE_URL . '/person/edit/id/%trainer_id%');
$linkInfo['linkUrl'] = "javascript:submitThenRedirect('{$linkInfo['linkUrl']}/trainingredirect/{$training_id}');";
$editLinkInfo = array();
}
$html = EditTableHelper::generateHtmlTraining('Trainer', $trainers, $trainerFields, $colStatic, $linkInfo, $editLinkInfo);
$this->view->assign('tableTrainers', $html);
/****************************************************************************************************************
* Participants */
$locations = Location::getAll();
$customColDefs = array();
if ($training_id) {
$persons = PersonToTraining::getParticipants($training_id)->toArray();
foreach ($persons as $pid => $p) {
$region_ids = Location::getCityInfo($p['location_id'], $this->setting('num_location_tiers'));
// todo expensive call, getcityinfo loads all locations each time??
$persons[$pid]['province_name'] = $region_ids[1] ? $locations[$region_ids['1']]['name'] : 'unknown';
if ($region_ids[2]) {
$persons[$pid]['district_name'] = $locations[$region_ids[2]]['name'];
} else {
$persons[$pid]['district_name'] = 'unknown';
}
if ($region_ids[3]) {
$persons[$pid]['region_c_name'] = $locations[$region_ids[3]]['name'];
} else {
$persons[$pid]['region_c_name'] = 'unknown';
}
if ($region_ids[4]) {
$persons[$pid]['region_d_name'] = $locations[$region_ids[4]]['name'];
} else {
$persons[$pid]['region_d_name'] = 'unknown';
}
if ($region_ids[5]) {
$persons[$pid]['region_e_name'] = $locations[$region_ids[5]]['name'];
} else {
$persons[$pid]['region_e_name'] = 'unknown';
}
if ($region_ids[6]) {
$persons[$pid]['region_f_name'] = $locations[$region_ids[6]]['name'];
} else {
$persons[$pid]['region_f_name'] = 'unknown';
}
if ($region_ids[7]) {
$persons[$pid]['region_g_name'] = $locations[$region_ids[7]]['name'];
} else {
$persons[$pid]['region_g_name'] = 'unknown';
}
if ($region_ids[8]) {
$persons[$pid]['region_h_name'] = $locations[$region_ids[8]]['name'];
} else {
$persons[$pid]['region_h_name'] = 'unknown';
}
if ($region_ids[9]) {
$persons[$pid]['region_i_name'] = $locations[$region_ids[9]]['name'];
} else {
$persons[$pid]['region_i_name'] = 'unknown';
}
}
} else {
$persons = array();
}
if (!$this->setting('display_middle_name')) {
$personsFields = array('first_name' => $this->tr('First Name'), 'last_name' => $this->tr('Last Name'), 'birthdate' => t('Date of Birth'), 'facility_name' => t('Facility'));
} else {
if ($this->setting('display_middle_name_last')) {
$personsFields = array('first_name' => $this->tr('First Name'), 'last_name' => $this->tr('Last Name'), 'middle_name' => "..." . $this->tr('Middle Name'), 'birthdate' => t('Date of Birth'), 'facility_name' => t('Facility'));
} else {
$personsFields = array('first_name' => $this->tr('First Name'), 'middle_name' => "..." . $this->tr('Middle Name'), 'last_name' => $this->tr('Last Name'), 'birthdate' => t('Date of Birth'), 'facility_name' => t('Facility'));
}
}
if ($this->setting('module_attendance_enabled')) {
$personsFields['duration_days'] = $this->tr('Days Attended');
$personsFields['award_phrase'] = $this->tr('Complete');
$rowArray = OptionList::suggestionList('person_to_training_award_option', array('id', 'award_phrase'), false, 9999, false, false);
$elements = array(0 => array('text' => ' ', 'value' => 0));
foreach ($rowArray as $i => $tablerow) {
$elements[$i + 1]['text'] = $tablerow['award_phrase'];
$elements[$i + 1]['value'] = $tablerow['id'];
}
$elements = json_encode($elements);
// yui data table will enjoy spending time with a json encoded array
$customColDefs['award_phrase'] = "editor:'dropdown', editorOptions: {dropdownOptions: {$elements} }";
}
if ($this->setting('display_viewing_location')) {
$personsFields['location_phrase'] = $this->tr('Viewing Location');
$vLocDropDown = OptionList::suggestionList('person_to_training_viewing_loc_option', array('id', 'location_phrase'), false, 9999, false, false);
$elements = array(0 => array('text' => '', 'value' => 0));
foreach ($vLocDropDown as $i => $tablerow) {
$elements[$i + 1]['text'] = $tablerow['location_phrase'];
$elements[$i + 1]['value'] = $tablerow['id'];
}
$elements = json_encode($elements);
$customColDefs['location_phrase'] = "editor:'dropdown', editorOptions: {dropdownOptions: {$elements} }";
}
if ($this->setting('display_budget_code')) {
$personsFields['budget_code_phrase'] = $this->tr('Budget Code');
$budgetDropDown = OptionList::suggestionList('person_to_training_budget_option', array('id', 'budget_code_phrase'), false, 9999, false, false);
$elements = array(0 => array('text' => '', 'value' => 0));
foreach ($budgetDropDown as $i => $tablerow) {
$elements[$i + 1]['text'] = $tablerow['budget_code_phrase'];
$elements[$i + 1]['value'] = $tablerow['id'];
}
$elements = json_encode($elements);
$customColDefs['budget_code_phrase'] = "editor: 'dropdown' , editorOptions:{dropdownOptions: {$elements}} ";
}
if ($this->setting('display_region_i')) {
$personsFields['region_i_name'] = $this->tr('Region I');
} else {
if ($this->setting('display_region_h')) {
$personsFields['region_h_name'] = $this->tr('Region H');
} else {
if ($this->setting('display_region_g')) {
$personsFields['region_g_name'] = $this->tr('Region G');
} else {
if ($this->setting('display_region_f')) {
$personsFields['region_f_name'] = $this->tr('Region F');
} else {
if ($this->setting('display_region_e')) {
$personsFields['region_e_name'] = $this->tr('Region E');
} else {
if ($this->setting('display_region_d')) {
$personsFields['region_d_name'] = $this->tr('Region D');
} else {
if ($this->setting('display_region_c')) {
$personsFields['region_c_name'] = $this->tr('Region C (Local Region)');
} else {
if ($this->setting('display_region_b')) {
$personsFields['district_name'] = $this->tr('Region B (Health District)');
} else {
$personsFields['province_name'] = $this->tr('Region A (Province)');
}
}
}
}
}
}
}
}
$colStatic = array_keys($personsFields);
// static calumns (From field keys)
if ($this->setting('module_attendance_enabled') || $this->setting('display_viewing_location') || $this->setting('display_budget_code')) {
foreach ($colStatic as $i => $v) {
if ($v == 'duration_days' || $v == 'award_phrase' || $v == 'budget_code_phrase' || $v == 'location_phrase') {
unset($colStatic[$i]);
}
}
// remove 1 so we can edit the field
}
if ($this->view->viewonly) {
$editLinkInfo['disabled'] = 1;
$linkInfo = array();
} else {
$linkInfo = array('linkFields' => $colStatic, 'linkId' => 'person_id', 'linkUrl' => Settings::$COUNTRY_BASE_URL . '/person/edit/id/%person_id%');
$linkInfo['linkUrl'] = "javascript:submitThenRedirect('{$linkInfo['linkUrl']}/trainingredirect/{$training_id}');";
$editLinkInfo = array();
// add link next to "Remove"
if ($this->setting('display_training_pre_test')) {
$editLinkInfo[] = array('linkName' => t('Pre-Test'), 'linkId' => 'id', 'linkUrl' => "javascript:updateScore('Pre-Test', %id%, '" . Settings::$COUNTRY_BASE_URL . "/training/scores-update', '%score_pre%');");
// do not translate label/key
}
if ($this->setting('display_training_post_test')) {
$editLinkInfo[] = array('linkName' => t('Post-Test'), 'linkId' => 'id', 'linkUrl' => "javascript:updateScore('Post-Test', %id%, '" . Settings::$COUNTRY_BASE_URL . "/training/scores-update', '%score_post%');");
// do not translate label/key
}
$editLinkInfo[] = array('linkName' => t('Scores'), 'linkId' => 'id', 'linkUrl' => "javascript:submitThenRedirect('" . Settings::$COUNTRY_BASE_URL . "/training/scores/ptt_id/%id%');");
// old
//'linkUrl' => Settings::$COUNTRY_BASE_URL."/training/scores/training/$training_id/person/%person_id%",
//$editLinkInfo['linkUrl'] = "javascript:submitThenRedirect('{$editLinkInfo['linkUrl']}');";
}
$html = EditTableHelper::generateHtmlTraining('Persons', $persons, $personsFields, $colStatic, $linkInfo, $editLinkInfo, $customColDefs);
$this->view->assign('tablePersons', $html);
/****************************************************************************************************************/
/* Attached Files */
FileUpload::displayFiles($this, 'training', $row->id, !$this->view->viewonly);
//$this->view->assign('files', 'x' . FileUpload::displayFiles($this, 'training', $row->id));
// File upload form
if (!$this->view->viewonly) {
$this->view->assign('filesForm', FileUpload::displayUploadForm('training', $row->id, FileUpload::$FILETYPES));
}
/****************************************************************************************************************/
/* Approval status */
if ($this->setting('module_approvals_enabled')) {
$canApprove = $this->hasACL('master_approver') && $row->is_approved == 2 || $this->hasACL('approve_trainings') && !$row->is_approved;
$this->view->assign('can_approve', $canApprove);
if ($canApprove) {
$this->view->assign('approve_val', '');
} else {
$this->view->assign('approve_val', $row->is_approved);
}
// disable control
if (!$canApprove or !$this->hasACL('approve_trainings')) {
$this->view->assign('approve_disable_str', 'disabled');
} else {
$this->view->assign('approve_disable_str', '');
}
}
/****************************************************************************************************************/
/* Attached Files */
// mode
$this->view->assign('mode', $this->_getParam('action'));
switch ($this->_getParam('msg')) {
case 'duplicate':
$this->view->assign('msg', t('Training') . ' ' . t('session has been duplicated.<br>You can edit the duplicate session below.'));
break;
default:
break;
}
// edit variables
if ($this->_getParam('action') != 'add') {
//audit history
$creatorObj = new User();
$updaterObj = new User();
$creatorrow = $creatorObj->findOrCreate($row->created_by);
$rowRay['creator'] = $creatorrow->first_name . ' ' . $creatorrow->last_name;
$updaterrow = $updaterObj->findOrCreate($row->modified_by);
$rowRay['updater'] = $updaterrow->first_name . ' ' . $updaterrow->last_name;
}
if (empty($trainers) || empty($persons)) {
$this->view->assign('isIncomplete', true);
}
// default start date?
if ($this->getSanParam('start-date')) {
$parts = explode('/', $this->getSanParam('start-date'));
if (count($parts) == 3) {
$rowRay['start-day'] = $parts[0];
$rowRay['start-month'] = $parts[1];
$rowRay['start-year'] = $parts[2];
}
}
// row values
$this->view->assign('row', $rowRay);
}
public function evaluationsReportAction()
{
require_once 'models/table/Trainer.php';
require_once 'models/table/TrainingLocation.php';
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
//criteria
$criteria['showTrainer'] = $this->getSanParam('showTrainer');
$criteria['showCategory'] = $this->getSanParam('showCategory');
$criteria['showTitle'] = $this->getSanParam('showTitle');
$criteria['showLocation'] = $this->getSanParam('showLocation');
$criteria['showOrganizer'] = $this->getSanParam('showOrganizer');
$criteria['showMechanism'] = $this->getSanParam('showMechanism');
$criteria['showTopic'] = $this->getSanParam('showTopic');
$criteria['showLevel'] = $this->getSanParam('showLevel');
$criteria['showPepfar'] = $this->getSanParam('showPepfar');
$criteria['showMethod'] = $this->getSanParam('showMethod');
$criteria['showFunding'] = $this->getSanParam('showFunding');
$criteria['showTOT'] = $this->getSanParam('showTOT');
$criteria['showRefresher'] = $this->getSanParam('showRefresher');
$criteria['showGotCurric'] = $this->getSanParam('showGotCurric');
$criteria['showGotComment'] = $this->getSanParam('showGotComment');
$criteria['showLang1'] = $this->getSanParam('showLang1');
$criteria['showLang2'] = $this->getSanParam('showLang2');
$criteria['showCustom1'] = $this->getSanParam('showCustom1');
$criteria['showCustom2'] = $this->getSanParam('showCustom2');
$criteria['showCustom3'] = $this->getSanParam('showCustom3');
$criteria['showCustom4'] = $this->getSanParam('showCustom4');
$criteria['showCustom5'] = $this->getSanParam('showCustom5');
$criteria['showProvince'] = $this->getSanParam('showProvince');
$criteria['showDistrict'] = $this->getSanParam('showDistrict');
$criteria['showRegionC'] = $this->getSanParam('showRegionC');
$criteria['showRegionD'] = $this->getSanParam('showRegionD');
$criteria['showRegionE'] = $this->getSanParam('showRegionE');
$criteria['showRegionF'] = $this->getSanParam('showRegionF');
$criteria['showRegionG'] = $this->getSanParam('showRegionG');
$criteria['showRegionH'] = $this->getSanParam('showRegionH');
$criteria['showRegionI'] = $this->getSanParam('showRegionI');
$criteria['evaluation_id'] = $this->getSanParam('evaluation_id');
$criteria['trainer_id'] = $this->getSanParam('trainer_id');
$criteria['training_category_id'] = $this->getSanParam('training_category_id');
$criteria['training_title_id'] = $this->getSanParam('training_title_id');
$criteria['training_location_id'] = $this->getSanParam('training_location_id');
$criteria['training_organizer_id'] = $this->getSanParam('training_organizer_id');
$criteria['training_mechanism_id'] = $this->getSanParam('training_mechanism_id');
$criteria['training_topic_id'] = $this->getSanParam('training_topic_id');
$criteria['training_level_id'] = $this->getSanParam('training_level_id');
$criteria['training_pepfar_id'] = $this->getSanParam('training_pepfar_id');
$criteria['training_method_id'] = $this->getSanParam('training_method_id');
$criteria['training_funding_id'] = $this->getSanParam('training_funding_id');
$criteria['training_tot_id'] = $this->getSanParam('training_tot_id');
$criteria['training_refresher_id'] = $this->getSanParam('training_refresher_id');
$criteria['training_got_id'] = $this->getSanParam('training_got_id');
$criteria['training_gotcomment_id'] = $this->getSanParam('training_gotcomment_id');
$criteria['training_lang1_id'] = $this->getSanParam('training_lang1_id');
$criteria['training_lang2_id'] = $this->getSanParam('training_lang2_id');
$criteria['training_custom1_id'] = $this->getSanParam('training_custom1_id');
$criteria['training_custom2_id'] = $this->getSanParam('training_custom2_id');
$criteria['training_custom3_id'] = $this->getSanParam('training_custom3_id');
$criteria['training_custom4_id'] = $this->getSanParam('training_custom4_id');
$criteria['province_id'] = $this->getSanParam('province_id');
$criteria['district_id'] = $this->getSanParam('district_id');
$criteria['region_c_id'] = $this->getSanParam('region_c_id');
$criteria['region_d_id'] = $this->getSanParam('region_d_id');
$criteria['region_e_id'] = $this->getSanParam('region_e_id');
$criteria['region_f_id'] = $this->getSanParam('region_f_id');
$criteria['region_g_id'] = $this->getSanParam('region_g_id');
$criteria['region_h_id'] = $this->getSanParam('region_h_id');
$criteria['region_i_id'] = $this->getSanParam('region_i_id');
$criteria['startdate'] = $this->getSanParam('startdate');
$criteria['enddate'] = $this->getSanParam('enddate');
$criteria['has_response'] = $this->getSanParam('has_response');
$criteria['limit'] = $this->getSanParam('limit');
$criteria['go'] = $this->getSanParam('go');
if ($criteria['go']) {
// fields
$sql = 'SELECT pt.id as "id", ptc.pcnt, pt.training_start_date, pt.training_end_date, pt.has_known_participants ';
// training fields
$sql .= ',title, trainer_person_id, first_name, last_name, question_text, question_type, weight, value_text, value_int';
// evaluation fields
if ($criteria['showRegionI']) {
$sql .= ', pt.region_i_name ';
}
if ($criteria['showRegionH']) {
$sql .= ', pt.region_h_name ';
}
if ($criteria['showRegionG']) {
$sql .= ', pt.region_g_name ';
}
if ($criteria['showRegionF']) {
$sql .= ', pt.region_f_name ';
}
if ($criteria['showRegionE']) {
$sql .= ', pt.region_e_name ';
}
if ($criteria['showRegionD']) {
$sql .= ', pt.region_d_name ';
}
if ($criteria['showRegionC']) {
$sql .= ', pt.region_c_name ';
}
if ($criteria['showDistrict']) {
$sql .= ', pt.district_name ';
}
if ($criteria['showProvince']) {
$sql .= ', pt.province_name ';
}
if ($criteria['showLocation']) {
$sql .= ', pt.training_location_name ';
}
if ($criteria['showOrganizer']) {
$sql .= ', torg.training_organizer_phrase as training_organizer_phrase ';
}
if ($criteria['showMechanism'] && $this->setting('display_training_partner')) {
$sql .= ', organizer_partners.mechanism_id ';
}
if ($criteria['showLevel']) {
$sql .= ', tlev.training_level_phrase ';
}
if ($criteria['showCategory']) {
$sql .= ', tcat.training_category_phrase ';
}
if ($criteria['showTitle']) {
$sql .= ', training_title ';
}
if ($criteria['showPepfar'] || $criteria['training_pepfar_id'] || $criteria['training_pepfar_id'] === '0') {
$sql .= ', GROUP_CONCAT(DISTINCT tpep.pepfar_category_phrase) as "pepfar_category_phrase" ';
}
if ($criteria['showMethod']) {
$sql .= ', tmeth.training_method_phrase ';
}
if ($criteria['showTopic']) {
$sql .= ', GROUP_CONCAT(DISTINCT ttopic.training_topic_phrase ORDER BY training_topic_phrase) AS "training_topic_phrase" ';
}
if ($criteria['showTOT']) {
$sql .= ", IF(is_tot,'" . t('Yes') . "','" . t('No') . "') AS is_tot";
}
if ($criteria['showRefresher']) {
$sql .= ", IF(is_refresher,'" . t('Yes') . "','" . t('No') . "') AS is_refresher";
}
if ($criteria['showLang2']) {
$sql .= ', tlos.language_phrase as "secondary_language_phrase" ';
}
if ($criteria['showLang1']) {
$sql .= ', tlop.language_phrase as "primary_language_phrase" ';
}
if ($criteria['showGotComment']) {
$sql .= ", pt.got_comments";
}
if ($criteria['showGotCurric']) {
$sql .= ', tgotc.training_got_curriculum_phrase ';
}
if ($criteria['showFunding']) {
$sql .= ', GROUP_CONCAT(DISTINCT tfund.funding_phrase ORDER BY funding_phrase) as "funding_phrase" ';
}
if ($criteria['showCustom1']) {
$sql .= ', tqc.custom1_phrase ';
}
if ($criteria['showCustom2']) {
$sql .= ', tqc.custom2_phrase ';
}
if ($criteria['showCustom3']) {
$sql .= ', pt.custom_3';
}
if ($criteria['showCustom4']) {
$sql .= ', pt.custom_4';
}
if ($criteria['showCustom5']) {
$sql .= ', pt.custom_5';
}
list($dontcare, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
$num_location_tiers = $this->setting('num_location_tiers');
list($field_name, $location_sub_query) = Location::subquery($num_location_tiers, $location_tier, $location_id, true);
$sql .= ' FROM (SELECT training.*, tto.training_title_phrase AS training_title,training_location.training_location_name, ' . implode(',', $field_name) . ' FROM training ' . ' LEFT JOIN training_title_option tto ON (`training`.training_title_option_id = tto.id) ' . ' LEFT JOIN training_location ON training.training_location_id = training_location.id ' . ' LEFT JOIN (' . $location_sub_query . ') as l ON training_location.location_id = l.id ' . ' WHERE training.is_deleted=0) as pt ';
$sql .= ' LEFT JOIN (SELECT COUNT(id) as "pcnt",training_id FROM person_to_training GROUP BY training_id) as ptc ON ptc.training_id = pt.id ';
// joins
if ($criteria['trainer_id']) {
$sql .= ' LEFT JOIN training_to_trainer as t2t ON (t2t.training_id = pt.id AND t2t.trainer_id = ' . $criteria['trainer_id'] . ')';
}
if ($criteria['showOrganizer'] or $criteria['training_organizer_id'] || $criteria['showMechanism'] || $criteria['training_mechanism_id']) {
$sql .= ' JOIN training_organizer_option as torg ON torg.id = pt.training_organizer_option_id ';
}
if ($criteria['showMechanism'] || $criteria['training_mechanism_id'] && @$this->setting('display_training_partner')) {
$sql .= ' LEFT JOIN organizer_partners ON organizer_partners.organizer_id = torg.id';
}
if ($criteria['showLevel'] || $criteria['training_level_id']) {
$sql .= ' JOIN training_level_option as tlev ON tlev.id = pt.training_level_option_id ';
}
if ($criteria['showMethod'] || $criteria['training_method_id']) {
$sql .= ' JOIN training_method_option as tmeth ON tmeth.id = pt.training_method_option_id ';
}
if ($criteria['showPepfar'] || $criteria['training_pepfar_id'] || $criteria['training_pepfar_id'] === '0') {
$sql .= ' LEFT JOIN (SELECT training_id, ttpco.training_pepfar_categories_option_id, pepfar_category_phrase FROM training_to_training_pepfar_categories_option as ttpco JOIN training_pepfar_categories_option as tpco ON ttpco.training_pepfar_categories_option_id = tpco.id) as tpep ON tpep.training_id = pt.id ';
}
if ($criteria['showTopic'] || $criteria['training_topic_id']) {
$sql .= ' LEFT JOIN (SELECT training_id, ttto.training_topic_option_id, training_topic_phrase FROM training_to_training_topic_option as ttto JOIN training_topic_option as tto ON ttto.training_topic_option_id = tto.id) as ttopic ON ttopic.training_id = pt.id ';
}
if ($criteria['showLang1'] || $criteria['training_lang1_id']) {
$sql .= ' LEFT JOIN trainer_language_option as tlop ON tlop.id = pt.training_primary_language_option_id ';
}
if ($criteria['showLang2'] || $criteria['training_lang2_id']) {
$sql .= ' LEFT JOIN trainer_language_option as tlos ON tlos.id = pt.training_secondary_language_option_id ';
}
if ($criteria['showFunding'] || (intval($criteria['funding_min']) or intval($criteria['funding_max']))) {
$sql .= ' LEFT JOIN (SELECT training_id, ttfo.training_funding_option_id, funding_phrase, ttfo.funding_amount FROM training_to_training_funding_option as ttfo JOIN training_funding_option as tfo ON ttfo.training_funding_option_id = tfo.id) as tfund ON tfund.training_id = pt.id ';
}
if ($criteria['showGotCurric'] || $criteria['training_got_id']) {
$sql .= ' LEFT JOIN training_got_curriculum_option as tgotc ON tgotc.id = pt.training_got_curriculum_option_id';
}
if ($criteria['showCategory'] or !empty($criteria['training_category_id'])) {
$sql .= 'LEFT JOIN training_category_option_to_training_title_option tcotto ON (tcotto.training_title_option_id = pt.training_title_option_id)
LEFT JOIN training_category_option tcat ON (tcotto.training_category_option_id = tcat.id)';
}
if ($criteria['showCustom1'] || $criteria['training_custom1_id']) {
$sql .= ' LEFT JOIN training_custom_1_option as tqc ON pt.training_custom_1_option_id = tqc.id ';
}
if ($criteria['showCustom2'] || $criteria['training_custom2_id']) {
$sql .= ' LEFT JOIN training_custom_2_option as tqc2 ON pt.training_custom_2_option_id = tqc2.id ';
}
#if ( $criteria['showCustom3'] || $criteria ['custom_3_id'] )
#todo$sql .= ' LEFT JOIN training_custom_3_option as custom_3 ON pt.training_custom_3_option_id = tqc3.id ';
#if ( $criteria['showCustom4'] || $criteria ['custom_4_id'] )
#todo$sql .= ' LEFT JOIN training_custom_4_option as custom_4 ON pt.training_custom_4_option_id = tqc4.id ';
$sql .= ' RIGHT JOIN evaluation_to_training ON pt.id = evaluation_to_training.training_id
RIGHT JOIN evaluation ON evaluation_id = evaluation.id
RIGHT JOIN evaluation_response ON evaluation_to_training.id = evaluation_response.evaluation_to_training_id
RIGHT JOIN evaluation_question ON evaluation.id = evaluation_question.evaluation_id
RIGHT JOIN evaluation_question_response ON evaluation_response.id = evaluation_question_response.evaluation_response_id AND evaluation_question.id = evaluation_question_response.evaluation_question_id
LEFT JOIN person ON trainer_person_id = person.id ';
// where
$where = array(' pt.is_deleted=0 ');
// 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[] = " pt.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
if ($site_orgs) {
$where[] = " training_organizer_option_id in ({$site_orgs}) ";
}
if ($criteria['training_participants_type']) {
if ($criteria['training_participants_type'] == 'has_known_participants') {
$where[] = ' pt.has_known_participants = 1 ';
}
if ($criteria['training_participants_type'] == 'has_unknown_participants') {
$where[] = ' pt.has_known_participants = 0 ';
}
}
if ($criteria['evaluation_id']) {
$where[] = ' evaluation.id = ' . $criteria['evaluation_id'];
}
if ($criteria['trainer_id']) {
$where[] = ' trainer_person_id = ' . $criteria['trainer_id'];
}
if ($criteria['training_location_id']) {
$where[] = ' pt.training_location_id = \'' . $criteria['training_location_id'] . '\'';
}
if ($criteria['training_title_id'] or $criteria['training_title_id'] === '0') {
$where[] = ' pt.training_title_option_id = ' . $criteria['training_title_id'];
}
if ($criteria['training_organizer_id'] or $criteria['training_organizer_id'] === '0') {
$where[] = ' pt.training_organizer_option_id = \'' . $criteria['training_organizer_id'] . '\'';
}
if ($criteria['training_mechanism_id'] or $criteria['training_mechanism_id'] === '0' && $this->setting('display_training_partner')) {
$where[] = ' organizer_partners.mechanism_id = \'' . $criteria['training_mechanism_id'] . '\'';
}
if ($criteria['training_topic_id'] or $criteria['training_topic_id'] === '0') {
$where[] = ' ttopic.training_topic_option_id = \'' . $criteria['training_topic_id'] . '\'';
}
if ($criteria['training_level_id']) {
$where[] = ' pt.training_level_option_id = \'' . $criteria['training_level_id'] . '\'';
}
if ($criteria['training_pepfar_id'] or $criteria['training_pepfar_id'] === '0') {
$where[] = ' tpep.training_pepfar_categories_option_id = \'' . $criteria['training_pepfar_id'] . '\'';
}
if ($criteria['training_method_id'] or $criteria['training_method_id'] === '0') {
$where[] = ' tmeth.id = \'' . $criteria['training_method_id'] . '\'';
}
if ($criteria['training_lang1_id'] or $criteria['training_lang1_id'] === '0') {
$where[] = ' pt.training_primary_language_option_id = \'' . $criteria['training_lang1_id'] . '\'';
}
if ($criteria['training_lang2_id'] or $criteria['training_lang2_id'] === '0') {
$where[] = ' pt.training_secondary_language_option_id = \'' . $criteria['training_lang2_id'] . '\'';
}
if ($criteria['startdate']) {
$parts = explode('/', $criteria['startdate']);
$reformattedDate = implode('/', array(@$parts[1], @$parts[0], @$parts[2]));
// swap month and date (reverse them)
$startDate = @date('Y-m-d', @strtotime($reformattedDate));
$parts2 = explode('/', $criteria['enddate']);
$reformattedDate = implode('/', array(@$parts2[1], @$parts2[0], @$parts2[2]));
// swap month and date (reverse them)
$endDate = @date('Y-m-d', @strtotime($reformattedDate));
if (!empty($startDate) && !empty($endDate)) {
$where[] = ' training_start_date >= \'' . $startDate . '\' AND training_start_date <= \'' . $endDate . '\' ';
}
}
if (intval($criteria['is_tot'])) {
$where[] = ' is_tot = ' . $criteria['is_tot'];
}
// not used
if ($criteria['training_funding_id'] or $criteria['training_funding_id'] === '0') {
$where[] = ' tfund.training_funding_option_id = \'' . $criteria['training_funding_id'] . '\'';
}
if ($criteria['training_category_id'] or $criteria['training_category_id'] === '0') {
$where[] = ' tcat.id = \'' . $criteria['training_category_id'] . '\'';
}
if ($criteria['training_got_id'] or $criteria['training_got_id'] === '0') {
$where[] = ' tgotc.id = \'' . $criteria['training_got_id'] . '\'';
}
if ($criteria['training_custom1_id'] or $criteria['training_custom1_id'] === '0') {
$where[] = ' pt.training_custom_1_option_id = \'' . $criteria['training_custom1_id'] . '\'';
}
if ($criteria['training_custom2_id'] or $criteria['training_custom2_id'] === '0') {
$where[] = ' pt.training_custom_2_option_id = \'' . $criteria['training_custom2_id'] . '\'';
}
if ($criteria['training_custom3_id'] or $criteria['training_custom3_id'] === '0') {
$where[] = ' pt.custom_3 = \'' . $criteria['training_custom3_id'] . '\'';
}
if ($criteria['training_custom4_id'] or $criteria['training_custom4_id'] === '0') {
$where[] = ' pt.custom_4 = \'' . $criteria['training_custom4_id'] . '\'';
}
$where[] = ' evaluation.is_deleted = 0';
$where[] = ' evaluation_response.is_deleted = 0';
$where[] = ' evaluation_question.is_deleted = 0';
$where[] = ' evaluation_question_response.is_deleted = 0';
if ($criteria['has_response']) {
$where[] = ' evaluation_response.evaluation_to_training_id IS NOT NULL ';
}
// finish
if ($where) {
$sql .= ' WHERE ' . implode(' AND ', $where);
}
$sql .= ' GROUP BY evaluation_question_response.id';
$rowArray = $db->fetchAll($sql);
// end training lookup
// output csv if necessary
if ($this->_getParam('outputType')) {
$this->sendData($this->reportHeaders(false, $rowArray));
}
//done
}
// values for the view
$this->viewAssignEscaped('results', $rowArray);
$this->view->assign('count', count($rowArray));
$this->view->assign('criteria', $criteria);
//evaluations drop down
$evaluationsArray = OptionList::suggestionList('evaluation', 'title', false, false, false);
$this->viewAssignEscaped('evaluations', $evaluationsArray);
//trainers
$trainersArray = $db->fetchAll('select p.id,p.first_name,p.middle_name,p.last_name from trainer left join person p on p.id = person_id order by p.first_name asc');
foreach ($trainersArray as $i => $row) {
$trainersArray[$i]['fullname'] = $this->setting('display_middle_name_last') ? $row['first_name'] . ' ' . $row['last_name'] . ' ' . $row['middle_name'] : $row['first_name'] . ' ' . $row['middle_name'] . ' ' . $row['last_name'];
}
$this->viewAssignEscaped('trainers', $trainersArray);
//locations
$locations = Location::getAll();
$this->viewAssignEscaped('locations', $locations);
//course
$courseArray = TrainingTitleOption::suggestionList(false, 10000);
$this->viewAssignEscaped('courses', $courseArray);
//location drop-down
$tlocations = TrainingLocation::selectAllLocations($this->setting('num_location_tiers'));
$this->viewAssignEscaped('tlocations', $tlocations);
//organizers
$organizersArray = OptionList::suggestionList('training_organizer_option', 'training_organizer_phrase', false, false, false);
$this->viewAssignEscaped('organizers', $organizersArray);
//topics
$topicsArray = OptionList::suggestionList('training_topic_option', 'training_topic_phrase', false, false, false);
$this->viewAssignEscaped('topics', $topicsArray);
//levels
$levelArray = OptionList::suggestionList('training_level_option', 'training_level_phrase', false, false);
$this->viewAssignEscaped('levels', $levelArray);
//pepfar
$organizersArray = OptionList::suggestionList('training_pepfar_categories_option', 'pepfar_category_phrase', false, false, false);
$this->viewAssignEscaped('pepfars', $organizersArray);
//refresher
if ($this->setting('multi_opt_refresher_course')) {
$refresherArray = OptionList::suggestionList('training_refresher_option', 'refresher_phrase_option', false, false, false);
$this->viewAssignEscaped('refresher', $refresherArray);
}
//funding
$fundingArray = OptionList::suggestionList('training_funding_option', 'funding_phrase', false, false, false);
$this->viewAssignEscaped('funding', $fundingArray);
//category
$categoryArray = OptionList::suggestionList('training_category_option', 'training_category_phrase', false, false, false);
$this->viewAssignEscaped('category', $categoryArray);
//primary language
$langArray = OptionList::suggestionList('trainer_language_option', 'language_phrase', false, false, false);
$this->viewAssignEscaped('language', $langArray);
//category+titles
$categoryTitle = MultiAssignList::getOptions('training_title_option', 'training_title_phrase', 'training_category_option_to_training_title_option', 'training_category_option');
$this->view->assign('categoryTitle', $categoryTitle);
//training methods
$methodTitle = OptionList::suggestionList('training_method_option', 'training_method_phrase', false, false, false);
$this->view->assign('methods', $methodTitle);
//got curric
$gotCuriccArray = OptionList::suggestionList('training_got_curriculum_option', 'training_got_curriculum_phrase', false, false, false);
$this->viewAssignEscaped('gotcurric', $gotCuriccArray);
//mechanism (organizer_partners table)
$mechanismArray = array();
if ($this->setting('display_training_partner')) {
$mechanismArray = OptionList::suggestionList('organizer_partners', 'mechanism_id', false, false, false, "mechanism_id != ''");
}
$this->viewAssignEscaped('mechanisms', $mechanismArray);
//customfields
$customArray = OptionList::suggestionList('training_custom_1_option', 'custom1_phrase', false, false, false);
$this->viewAssignEscaped('custom1', $customArray);
$customArray2 = OptionList::suggestionList('training_custom_2_option', 'custom2_phrase', false, false, false);
$this->viewAssignEscaped('custom2', $customArray2);
$customArray3 = OptionList::suggestionList('training', 'custom_3', false, false, false, "custom_3 != ''");
$this->viewAssignEscaped('custom3', $customArray3);
$customArray4 = OptionList::suggestionList('training', 'custom_4', false, false, false, "custom_4 != ''");
$this->viewAssignEscaped('custom4', $customArray4);
$customArray5 = OptionList::suggestionList('training', 'custom_5', false, false, false, "custom_5 != ''");
$this->viewAssignEscaped('custom5', $customArray5);
#$createdByArray = $db->fetchAll("select id,CONCAT(first_name, CONCAT(' ', last_name)) as name from user where is_blocked = 0");
#$this->viewAssignEscaped ( 'createdBy', $createdByArray );
#// find category based on title
#$catId = NULL;
#if ($criteria ['training_category_id']) {
# foreach ( $categoryTitle as $r ) {
# if ($r ['id'] == $criteria ['training_category_id']) {
# $catId = $r ['training_category_option_id'];
# break;
# }
# }
#}
#$this->view->assign ( 'catId', $catId );
//done
}
