/**
* Insert new training location and return id if not found. Return id if found.
*/
public static function insertIfNotFound($name, $location_id)
{
$tableObj = new TrainingLocation();
$whereSql = "training_location_name = ? AND location_id = {$location_id}";
$select = $tableObj->select()->from(array('tl' => $tableObj->_name), 'id')->where($whereSql, $name);
$row = $tableObj->fetchRow($select);
if ($row) {
return $row->id;
} else {
// insert
$data['training_location_name'] = $name;
$data['location_id'] = $location_id;
return $tableObj->insert($data);
}
}
public function facilityReport()
{
require_once 'models/table/TrainingLocation.php';
$criteria = array();
//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');
}
list($criteria, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
$criteria['training_title_option_id'] = $this->getSanParam('training_title_option_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_pepfar_id'] = $this->getSanParam('training_pepfar_id');
$criteria['training_topic_id'] = $this->getSanParam('training_topic_id');
$criteria['training_level_id'] = $this->getSanParam('training_level_id');
$criteria['facility_type_id'] = $this->getSanParam('facility_type_id');
$criteria['facility_sponsor_id'] = $this->getSanParam('facility_sponsor_id');
$criteria['facilityInput'] = $this->getSanParam('facilityInput');
$criteria['is_tot'] = $this->getSanParam('is_tot');
$criteria['go'] = $this->getSanParam('go');
$criteria['doCount'] = $this->view->mode == 'count';
$criteria['showProvince'] = ($this->getSanParam('showProvince') or $criteria['doCount'] and ($criteria['province_id'] or $criteria['province_id'] === '0'));
$criteria['showDistrict'] = ($this->getSanParam('showDistrict') or $criteria['doCount'] and ($criteria['district_id'] or $criteria['district_id'] === '0'));
/*$criteria ['showCadre'] = ($this->getSanParam ( 'showCadre' ) or ($criteria ['doCount'] and ($criteria ['cadre'] or $criteria ['cadre'] === '0')));*/
$criteria['showRegionC'] = ($this->getSanParam('showRegionC') or $criteria['doCount'] and ($criteria['region_c_id'] or $criteria['region_c_id'] === '0'));
$criteria['showTrainingTitle'] = ($this->getSanParam('showTrainingTitle') or $criteria['doCount'] and ($criteria['training_title_option_id'] or $criteria['training_title_option_id'] === '0' or $criteria['training_title_id']));
$criteria['showLocation'] = ($this->getSanParam('showLocation') or $criteria['doCount'] and $criteria['training_location_id']);
$criteria['showOrganizer'] = ($this->getSanParam('showOrganizer') or $criteria['doCount'] and ($criteria['training_organizer_id'] or $criteria['training_organizer_id'] === '0'));
$criteria['showPepfar'] = ($this->getSanParam('showPepfar') or $criteria['doCount'] and ($criteria['training_pepfar_id'] or $criteria['training_pepfar_id'] === '0'));
$criteria['showTopic'] = ($this->getSanParam('showTopic') or $criteria['doCount'] and ($criteria['training_topic_id'] or $criteria['training_topic_id'] === '0'));
$criteria['showLevel'] = ($this->getSanParam('showLevel') or $criteria['doCount'] and $criteria['training_level_id']);
$criteria['showType'] = ($this->getSanParam('showType') or $criteria['doCount'] and ($criteria['facility_type_id'] or $criteria['facility_type_id'] === '0'));
$criteria['showSponsor'] = ($this->getSanParam('showSponsor') or $criteria['doCount'] and $criteria['facility_sponsor_id']);
$criteria['showFacility'] = true;
//($this->getSanParam('showFacility') OR ($criteria['doCount'] and $criteria['facility_name']));
$criteria['showTot'] = ($this->getSanParam('showTot') or ($criteria['doCount'] and $criteria['is_tot'] !== '' or $criteria['is_tot'] === '0'));
if ($criteria['go']) {
$sql = 'SELECT ';
if ($criteria['doCount']) {
$sql .= ' COUNT(pt.person_id) as "cnt", pt.facility_name ';
} else {
$sql .= ' DISTINCT pt.id as "id", pt.facility_name, pt.training_start_date ';
}
if ($criteria['showFacility']) {
$sql .= ', pt.facility_name ';
}
if ($criteria['showTrainingTitle'] or $this->view->mode == 'search') {
$sql .= ', pt.training_title ';
}
if ($criteria['showDistrict']) {
$sql .= ', pt.district_name, pt.district_id ';
}
if ($criteria['showCadre']) {
$sql .= ', cad.id, cad.cadrename ';
}
if ($criteria['showProvince']) {
$sql .= ', pt.province_name, pt.province_id ';
}
if ($criteria['showRegionC']) {
$sql .= ', pt.region_c_name, pt.region_c_id ';
}
if ($criteria['showLocation']) {
$sql .= ', pt.training_location_name ';
}
if ($criteria['showOrganizer']) {
$sql .= ', torg.training_organizer_phrase ';
}
if ($criteria['showLevel']) {
$sql .= ', tlev.training_level_phrase ';
}
if ($criteria['showType']) {
$sql .= ', fto.facility_type_phrase ';
}
if ($criteria['showSponsor']) {
$sql .= ', fso.facility_sponsor_phrase ';
}
if ($criteria['showPepfar']) {
if ($criteria['doCount']) {
$sql .= ', tpep.pepfar_category_phrase ';
} else {
$sql .= ', GROUP_CONCAT(DISTINCT tpep.pepfar_category_phrase) as "pepfar_category_phrase" ';
}
}
if ($criteria['showTopic']) {
if ($criteria['doCount']) {
$sql .= ', ttopic.training_topic_phrase ';
} else {
$sql .= ', GROUP_CONCAT(DISTINCT ttopic.training_topic_phrase ORDER BY training_topic_phrase) AS "training_topic_phrase" ';
}
}
if ($criteria['showTot']) {
//$sql .= ', pt.is_tot ';
$sql .= ", IF(pt.is_tot,'" . t('Yes') . "','" . t('No') . "') AS is_tot";
}
//JOIN with the participants to get facility
$num_locs = $this->setting('num_location_tiers');
list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id, true);
if ($criteria['doCount']) {
$sql .= ' FROM (SELECT training.*, fac.person_id as "person_id", fac.facility_id as "facility_id", fac.type_option_id, fac.sponsor_option_id, fac.facility_name as "facility_name" , tto.training_title_phrase AS training_title,training_location.training_location_name, l.' . implode(', l.', $field_name) . ' FROM training ' . ' JOIN training_title_option tto ON (`training`.training_title_option_id = tto.id)' . ' JOIN training_location ON training.training_location_id = training_location.id ' . ' JOIN (SELECT person_id, facility_name, facility_id, location_id, type_option_id, sponsor_option_id,training_id FROM person JOIN person_to_training ON person_to_training.person_id = person.id ' . ' JOIN facility as f ON person.facility_id = f.id) as fac ON training.id = fac.training_id JOIN (' . $location_sub_query . ') as l ON fac.location_id = l.id WHERE training.is_deleted=0) as pt ';
} else {
$sql .= ' FROM (SELECT training.*, fac.facility_id as "facility_id", fac.type_option_id, fac.sponsor_option_id ,fac.facility_name as "facility_name" , tto.training_title_phrase AS training_title,training_location.training_location_name, l.' . implode(', l.', $field_name) . ' FROM training ' . ' JOIN training_title_option tto ON (`training`.training_title_option_id = tto.id) ' . ' JOIN training_location ON training.training_location_id = training_location.id ' . ' JOIN (SELECT DISTINCT facility_name, facility_id, location_id, training_id, type_option_id, sponsor_option_id FROM person JOIN person_to_training ON person_to_training.person_id = person.id ' . ' JOIN facility as f ON person.facility_id = f.id) as fac ON training.id = fac.training_id JOIN (' . $location_sub_query . ') as l ON fac.location_id = l.id WHERE training.is_deleted=0) as pt ';
}
if ($criteria['showOrganizer']) {
$sql .= ' JOIN training_organizer_option as torg ON torg.id = pt.training_organizer_option_id ';
}
if ($criteria['showLevel']) {
$sql .= ' JOIN training_level_option as tlev ON tlev.id = pt.training_level_option_id ';
}
if ($criteria['showType']) {
$sql .= ' JOIN facility_type_option as fto ON fto.id = pt.type_option_id ';
}
/*if ($criteria ['showCadre']) {
$sql .= ' Inner JOIN cadres as cad';
}*/
if ($criteria['showSponsor']) {
$sql .= ' JOIN facility_sponsor_option as fso ON fso.id = pt.sponsor_option_id ';
}
if ($criteria['showPepfar']) {
$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']) {
//$sql .= ' LEFT JOIN training_topic_option as ttopic ON ttopic.id = ttopic.training_topic_option_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 ';
}
$where = array(' pt.is_deleted=0 ');
if ($criteria['training_title_option_id'] or $criteria['training_title_option_id'] === '0') {
$where[] = ' pt.training_title_option_id = ' . $criteria['training_title_option_id'];
}
if ($criteria['training_title_id'] or $criteria['training_title_id'] === '0') {
$where[] = ' pt.training_title_option_id = ' . $criteria['training_title_id'];
}
if ($criteria['facilityInput']) {
$where[] = ' pt.facility_id = \'' . $criteria['facilityInput'] . '\'';
}
if ($criteria['training_location_id']) {
$where[] = ' pt.training_location_id = \'' . $criteria['training_location_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_topic_id'] or $criteria['training_topic_id'] === '0') {
$where[] = ' ttopic.training_topic_option_id = \'' . $criteria['training_topic_id'] . '\'';
}
if ($criteria['facility_type_id'] or $criteria['facility_type_id'] === '0') {
$where[] = ' pt.type_option_id = \'' . $criteria['facility_type_id'] . '\'';
}
if ($criteria['facility_sponsor_id'] or $criteria['facility_sponsor_id'] === '0') {
$where[] = ' pt.sponsor_option_id = \'' . $criteria['facility_sponsor_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 (intval($criteria['end-year']) and $criteria['start-year']) {
$startDate = $criteria['start-year'] . '-' . $criteria['start-month'] . '-' . $criteria['start-day'];
$endDate = $criteria['end-year'] . '-' . $criteria['end-month'] . '-' . $criteria['end-day'];
$where[] = ' training_start_date >= \'' . $startDate . '\' AND training_start_date <= \'' . $endDate . '\' ';
}
if ($criteria['is_tot'] or $criteria['is_tot'] === '0') {
$where[] = ' pt.is_tot = ' . $criteria['is_tot'];
}
if ($where) {
$sql .= ' WHERE ' . implode(' AND ', $where);
}
if ($criteria['doCount']) {
$groupBy = array();
if ($criteria['showFacility']) {
$groupBy[] = ' pt.facility_id';
}
if ($criteria['showTrainingTitle']) {
$groupBy[] = ' pt.training_title_option_id';
}
if ($criteria['showProvince']) {
$groupBy[] = ' pt.province_id';
}
if ($criteria['showDistrict']) {
$groupBy[] = ' pt.district_id';
}
if ($criteria['showCadre']) {
$groupBy[] = ' pt.id';
}
if ($criteria['showRegionC']) {
$groupBy[] = ' pt.region_c_id';
}
if ($criteria['showLocation']) {
$groupBy[] = ' pt.training_location_id';
}
if ($criteria['showOrganizer']) {
$groupBy[] = ' pt.training_organizer_option_id';
}
if ($criteria['showTopic']) {
$groupBy[] = ' ttopic.training_topic_option_id';
}
if ($criteria['showLevel']) {
$groupBy[] = ' pt.training_level_option_id';
}
if ($criteria['showPepfar']) {
$groupBy[] = ' tpep.training_pepfar_categories_option_id';
}
if ($criteria['showType']) {
$groupBy[] = ' pt.type_option_id';
}
if ($criteria['showSponsor']) {
$groupBy[] = ' pt.sponsor_option_id';
}
if ($criteria['showTot']) {
$groupBy[] = ' pt.is_tot';
}
if ($groupBy) {
$groupBy = ' GROUP BY ' . implode(', ', $groupBy);
}
$sql .= $groupBy;
} else {
if ($criteria['showPepfar'] || $criteria['showTopic']) {
$sql .= ' GROUP BY pt.id';
}
}
$rowArray = $db->fetchAll($sql . ' ORDER BY facility_name ASC ');
if ($criteria['doCount']) {
$count = 0;
foreach ($rowArray as $row) {
$count += $row['cnt'];
}
} else {
$count = count($rowArray);
}
if ($this->_getParam('outputType')) {
$this->sendData($this->reportHeaders(false, $rowArray));
}
} else {
$count = 0;
$rowArray = array();
}
$criteria['go'] = $this->getSanParam('go');
//not sure why we are getting multiple PEPFARS
foreach ($rowArray as $key => $row) {
if (isset($row['pepfar_category_phrase'])) {
$rowArray[$key]['pepfar_category_phrase'] = implode(',', array_unique(explode(',', $row['pepfar_category_phrase'])));
}
}
$this->viewAssignEscaped('results', $rowArray);
$this->view->assign('count', $count);
$this->view->assign('criteria', $criteria);
//facilities list
$fArray = OptionList::suggestionList('facility', array('facility_name', 'id'), false, 9999);
$facilitiesArray = array();
foreach ($fArray as $key => $val) {
if ($val['id'] != 0) {
$facilitiesArray[] = $val;
}
}
$this->viewAssignEscaped('facilities', $facilitiesArray);
//locations
$locations = Location::getAll();
$this->viewAssignEscaped('locations', $locations);
//facility types
$typesArray = OptionList::suggestionList('facility_type_option', 'facility_type_phrase', false, false);
$this->viewAssignEscaped('facility_types', $typesArray);
//sponsor types
$sponsorsArray = OptionList::suggestionList('facility_sponsor_option', 'facility_sponsor_phrase', false, false);
$this->viewAssignEscaped('facility_sponsors', $sponsorsArray);
//course
//$courseArray = Course::suggestionList(false,10000);
//$this->viewAssignEscaped('courses',$courseArray);
//location
// 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);
//facilities list
$rowArray = OptionList::suggestionList('facility', array('facility_name', 'id'), false, 9999);
$facilitiesArray = array();
foreach ($rowArray as $key => $val) {
if ($val['id'] != 0) {
$facilitiesArray[] = $val;
}
}
$this->viewAssignEscaped('facilities', $facilitiesArray);
}
function viewlocationAction()
{
if (!$this->hasACL('edit_course')) {
$this->view->assign('viewonly', 'disabled="disabled"');
}
require_once 'models/table/TrainingLocation.php';
$this->view->assign('id', $this->_getParam('id'));
if ($this->_getParam('id')) {
require_once 'views/helpers/DropDown.php';
$rowLocation = TrainingLocation::selectLocation($this->_getParam('id'))->toArray();
//locations
list($cname, $prov, $dist, $regc) = Location::getCityInfo($rowLocation['location_id'], $this->setting('num_location_tiers'));
$rowLocation['city_name'] = $cname;
$rowLocation['region_c_id'] = $regc;
$rowLocation['district_id'] = $dist;
$rowLocation['province_id'] = $prov;
$this->viewAssignEscaped('rowLocation', $rowLocation);
//see if it is referenced anywhere
$this->view->assign('okToDelete', !TrainingLocation::isReferenced($this->_getParam('id')));
}
// location drop-down
$locations = TrainingLocation::selectAllLocations($this->setting('num_location_tiers'));
$this->viewAssignEscaped('tlocations', $locations);
}
/**
* New training location
*/
public function locationAddAction()
{
require_once 'models/table/TrainingLocation.php';
require_once 'models/table/Location.php';
$request = $this->getRequest();
$validateOnly = $request->isXmlHttpRequest();
if ($validateOnly) {
$this->setNoRenderer();
}
if ($request->isPost()) {
$tableObj = new TrainingLocation();
$location = $this->_getParam('training_location_name');
list($location_params, $location_tier, $location_id) = $this->getLocationCriteriaValues(array());
//validate
$status = ValidationContainer::instance();
$districtText = $this->tr('Region B (Health District)');
$provinceText = $this->tr('Region A (Province)');
$localRegionText = $this->tr('Region C (Local Region)');
$regionDText = $this->tr('Region D');
$regionEText = $this->tr('Region E');
$regionFText = $this->tr('Region F');
$regionGText = $this->tr('Region G');
$regionHText = $this->tr('Region H');
$regionIText = $this->tr('Region I');
$status->checkRequired($this, 'province_id', $provinceText);
if ($this->setting('display_region_b')) {
$status->checkRequired($this, 'district_id', $districtText);
}
if ($this->setting('display_region_c')) {
$status->checkRequired($this, 'region_c_id', $localRegionText);
}
if ($this->setting('display_region_d')) {
$status->checkRequired($this, 'region_d_id', $regionDText);
}
if ($this->setting('display_region_e')) {
$status->checkRequired($this, 'region_e_id', $regionEText);
}
if ($this->setting('display_region_f')) {
$status->checkRequired($this, 'region_f_id', $regionFText);
}
if ($this->setting('display_region_g')) {
$status->checkRequired($this, 'region_g_id', $regionGText);
}
if ($this->setting('display_region_h')) {
$status->checkRequired($this, 'region_h_id', $regionHText);
}
if ($this->setting('display_region_i')) {
$status->checkRequired($this, 'region_i_id', $regionIText);
}
//$status->checkRequired ( $this, 'city', t ( "City is required." ) );
$city_id = false;
if ($this->getSanParam('city') && !$this->getSanParam('is_new_city')) {
$city_id = Location::verifyHierarchy($location_params['city'], $location_params['city_parent_id'], $this->setting('num_location_tiers'));
if ($city_id === false) {
$status->addError('city', t("That city does not appear to be located in the chosen region. If you want to create a new city, check the new city box."));
}
}
// save
if (!$status->hasError()) {
$location_id = null;
if ($city_id === false && $this->getSanParam('is_new_city')) {
$location_id = Location::insertIfNotFound($location_params['city'], $location_params['city_parent_id'], $this->setting('num_location_tiers'));
if ($location_id === false) {
$status->addError('city', t('Could not save that city.'));
}
} else {
if ($city_id) {
$location_id = $city_id;
} else {
if ($this->setting('display_region_i')) {
$location_id = $this->getSanParam('region_i_id');
} else {
if ($this->setting('display_region_h')) {
$location_id = $this->getSanParam('region_h_id');
} else {
if ($this->setting('display_region_g')) {
$location_id = $this->getSanParam('region_g_id');
} else {
if ($this->setting('display_region_f')) {
$location_id = $this->getSanParam('region_f_id');
} else {
if ($this->setting('display_region_e')) {
$location_id = $this->getSanParam('region_e_id');
} else {
if ($this->setting('display_region_d')) {
$location_id = $this->getSanParam('region_d_id');
} else {
if ($this->setting('display_region_c')) {
$location_id = $this->getSanParam('region_c_id');
} else {
if ($this->setting('display_region_b')) {
$location_id = $this->getSanParam('district_id');
} else {
$location_id = $this->getSanParam('province_id');
}
}
}
}
}
}
}
}
}
if (strstr($location_id, '_')) {
$parts = explode('_', $location_id);
$location_id = $parts[count($parts) - 1];
}
}
if ($location_id) {
// update or insert?
if ($this->_getParam('update')) {
$data = array();
$data['location_id'] = $location_id;
$data['training_location_name'] = $location;
$tableObj = new TrainingLocation();
$tableObj->update($data, "id = " . $this->_getParam('update'));
$status->setStatusMessage(t('The') . ' ' . t('Training Center') . ' ' . t('has been updated.'));
$_SESSION['status'] = t('The') . ' ' . t('Training Center') . ' ' . t('has been updated.');
//refresh the page, so the picker dropdown is refreshed as well
$status->setRedirect('/facility/view-location/id/' . $this->_getParam('update'));
} else {
$id = TrainingLocation::insertIfNotFound($location, $location_id);
if ($this->_getParam('info') == 'extra') {
$status->setStatusMessage(t('The') . ' ' . t('Training Center') . ' ' . t('has been saved.'));
$_SESSION['status'] = t('The') . ' ' . t('Training Center') . ' ' . t('has been saved.');
$status->setRedirect('/facility/view-location/id/' . $id);
}
}
if ($this->_getParam('info') == 'extra') {
$this->sendData($status);
} else {
$this->sendData(array('location_id' => $id));
}
}
} else {
$status->setStatusMessage(t('The') . ' ' . t('Training Center') . ' ' . t('could not be saved.'));
$this->sendData($status);
}
}
}
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
}
/**
* Import a training location
*/
public function importLocationAction()
{
$this->view->assign('pageTitle', t('Import a training location'));
require_once 'models/table/Location.php';
require_once 'models/table/TrainingLocation.php';
// template redirect
if ($this->getSanParam('download')) {
return $this->importLocationTemplateAction();
}
if (!$this->hasACL('import_training_location')) {
$this->doNoAccessError();
}
//CSV STUFF
$filename = $_FILES['upload']['tmp_name'];
if ($filename) {
$trainingLocationObj = new TrainingLocation();
$errs = array();
while ($row = $this->_csv_get_row($filename)) {
$values = array();
if (!is_array($row)) {
continue;
}
// sanity?
if (!isset($cols)) {
// set headers (field names)
$cols = $row;
// first row is headers (field names)
continue;
}
$countValidFields = 0;
if (!empty($row)) {
// add
foreach ($row as $i => $v) {
// proccess each column
if (empty($v) && $v !== '0') {
continue;
}
if ($v == 'n/a') {
// has to be able to process values from a data export
$v = NULL;
}
$countValidFields++;
$delimiter = strpos($v, ',');
// is this field a comma seperated list too (or array)?
if ($delimiter && $v[$delimiter - 1] != '\\') {
// handle arrays as field values(Export), and comma seperated values(import manual entry), and strings or int
$values[$cols[$i]] = explode(',', $this->sanitize($v));
} else {
$values[$cols[$i]] = $this->sanitize($v);
}
}
}
// done now all fields are named and in $values['my_field']
if ($countValidFields) {
//validate
if (isset($values['uuid'])) {
unset($values['uuid']);
}
if (isset($values['id'])) {
unset($values['id']);
}
if (isset($values['is_deleted'])) {
unset($values['is_deleted']);
}
if (isset($values['created_by'])) {
unset($values['created_by']);
}
if (isset($values['modified_by'])) {
unset($values['modified_by']);
}
if (isset($values['timestamp_created'])) {
unset($values['timestamp_created']);
}
if (isset($values['timestamp_updated'])) {
unset($values['timestamp_updated']);
}
//required
if (empty($values['training_location_name'])) {
$errs[] = t('Error adding training location, training location name cannot be empty.');
}
//locations
$num_location_tiers = $this->setting('num_location_tiers');
$bSuccess = true;
$location_id = null;
if ($values['location_id']) {
$location_id = $values['location_id'];
}
$tier = 1;
if (!$location_id) {
for ($i = 0; $i <= $num_location_tiers; $i++) {
// insert/find locations
$r = 1 + $i;
// first location field in csv row // could use this too: $values[t('Region A (Province)')]
if (empty($row[$r]) || $bSuccess == false) {
continue;
}
$location_id = Location::insertIfNotFound($row[$r], $location_id, $tier);
if (!$location_id) {
$bSuccess = false;
break;
}
$tier++;
}
}
if (!$bSuccess || !$location_id) {
$errs[] = t('Error locating/creating region or city:') . ' ' . $row[$r] . ' ' . t('Training Location') . ': ' . $values['training_location_name'];
continue;
// couldnt save location
}
$values['location_id'] = $location_id;
//dupecheck
$dupe = new TrainingLocation();
$select = $dupe->select()->where('location_id =' . $location_id . ' and training_location_name = "' . $values['training_location_name'] . '"');
if ($dupe->fetchRow($select)) {
$errs[] = t('The training location could not be saved. A training location with this name already exists in that location.') . ' ' . t('training location') . ': ' . $values['training_location_name'];
$bSuccess = false;
}
if (!$bSuccess) {
continue;
}
//save
try {
$tableObj = $trainingLocationObj->createRow();
$tableObj->training_location_name = $values['training_location_name'];
$tableObj->location_id = $location_id;
$row_id = $tableObj->save();
} catch (Exception $e) {
$errored = 1;
$errs[] = nl2br($e->getMessage()) . ' ' . t('ERROR: The training location could not be saved.');
}
if (!$row_id) {
$errored = 1;
}
//sucess - done
}
//loop
}
// done processing rows
$_POST['redirect'] = null;
$status = ValidationContainer::instance();
if (empty($errored) && empty($errs)) {
$stat = t('Your changes have been saved.');
} else {
$stat = t('Error importing data. Some data may have been imported and some may not have.');
}
foreach ($errs as $errmsg) {
$stat .= '<br>' . 'Error: ' . htmlspecialchars($errmsg, ENT_QUOTES);
}
$status->setStatusMessage($stat);
$this->view->assign('status', $status);
}
// done with import
}