/** * 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 }