PHP Location::getAll方法代码示例

 public function tutoreditAction()
 {
     if (isset($_POST['addpeople'])) {
         # ADDING PERSON RECORD
         $peopleadd = new Peopleadd();
         $tutorid = $peopleadd->Peopleadd($_POST);
         /*
         			# ADDING ADDRESS RECORD
         			$query = "INSERT INTO addresses SET
         				address1 = '" . addslashes($_POST[address1]) . "',
         				address2 = '" . addslashes($_POST[address2]) . "',
         				city = '" . addslashes($_POST[zip]) . "',
         				postalcode = '" . addslashes($_POST[address1]) . "',
         				id_addresstype = '" . addslashes(1) . "',
         				id_geog1 = '" . addslashes($_POST[province_id] ? $_POST[province_id] : 0) . "',
         				id_geog2 = '" . addslashes($_POST[district_id] ? $_POST[district_id] : 0) . "',
         				id_geog3 = '" . addslashes($_POST[region_c_id] ? $_POST[region_c_id] : 0) . "'";
         die($query);
         			$db = $this->dbfunc()->query($query);
         			$id = $db->lastInsertId();
         			# ADDING ADDRESS RECORD
         			$query = "INSERT INTO link_tutor_addresses SET
         				id_address = '" . addslashes($id) . "',
         				id_tutor = '" . addslashes($tutorid) . "'";
         die($query);
         			$db = $this->dbfunc()->query($query);
         			$id = $db->lastInsertId();
         			# LINKING TO TUTOR RECORD
         			$db = $this->dbfunc(); 		 
         			$linkrec = array(
         				'id_address'		=>	$id,
         				'id_tutor'			=>	$tutorid,
         			);
         			$rowArray = $db->insert("link_tutor_addresses",$linkrec); 		
         			$id = $db->lastInsertId();
         */
         $this->_redirect(Settings::$COUNTRY_BASE_URL . "/tutoredit/tutoredit/id/" . $tutorid);
     }
     if (isset($_POST['update'])) {
         $tutoredit = new Tutoredit();
         $updateperson = $tutoredit->UpdatePerson($_POST);
         $updateperson = $tutoredit->UpdateTutor($_POST);
         # STORE LANGUAGES ON EDIT
         #			$updateperson=$tutoredit->UpdatePermanentAddress($_POST);
         #			$studentupdate = new Tutoredit();
         #			$update = $studentupdate->UpdatePerson($_POST);						# STORING PERSON RECORD
         #			$update = $studentupdate->UpdateStudent($_POST);					# STORING STUDENT RECORD
         #			$update = $studentupdate->UpdateStudentCohort($_POST);				# STORING COHORT LINK
         #			$update = $studentupdate->UpdatePermanentAddress($_POST);			# STORING PERMANENT ADDRESS
         $tutorid = $_POST['id'];
         $this->view->assign('newid', $tutorid);
     }
     $request = $this->getRequest();
     $tutorid = $request->getParam('id');
     $this->view->assign('id', $tutorid);
     $this->viewAssignEscaped('locations', Location::getAll());
     $Tutoredit = new Tutoredit();
     $details = $Tutoredit->EditTutor($tutorid);
     #print_r ($details);
     $date = $details['person'][0]['birthdate'];
     $dob = date("d-m-Y", strtotime($date));
     $this->view->assign('id', $tutorid);
     $this->view->assign('selid', $details['person'][0]['title_option_id']);
     $this->view->assign('firstname', $details['person'][0]['first_name']);
     $this->view->assign('middlename', $details['person'][0]['middle_name']);
     $this->view->assign('lastname', $details['person'][0]['last_name']);
     $this->view->assign('nationalid', $details['person'][0]['national_id']);
     $this->view->assign('gender', $details['person'][0]['gender']);
     $this->view->assign('dob', $dob);
     $helper = new Helper();
     $this->view->assign('lookupnationalities', $helper->getNationalities());
     $this->view->assign('lookupfacilities', $helper->getNationalities());
     $this->view->assign('lookupdegrees', $helper->getDegrees());
     $this->view->assign('tutorlanguages', $helper->getLanguages());
     $known = array();
     $tl = $helper->getTutorLanguages($details['tutor'][0]['id']);
     foreach ($tl as $_tl) {
         $known[] = $_tl['id_language'];
     }
     $this->view->assign('knownlanguages', $known);
     $this->view->assign('lookuptutortypes', $helper->getTutorTypes());
     $tt = array();
     $types = $helper->getLinkedTutorTypes($details['tutor'][0]['id']);
     foreach ($types as $type) {
         $tt[] = $type['id'];
     }
     $this->view->assign('tutortypes', $tt);
     $this->view->assign('students', $helper->getTutorStudents($tutorid));
     $this->view->assign('classes', $helper->getTutorClasses($tutorid));
     # GET ALL STUDENTS WHERE ADVISOR = THIS ID
     # GET ALL COURSES LINKED TO THIS ID
     /*
     		$this->view->assign('localgeo1',$details['student'][0]['geog1']);
     		$this->view->assign('localgeo2',$details['student'][0]['geog2']);
     		$this->view->assign('localgeo3',$details['student'][0]['geog3']);
     		$this->view->assign('address1',$details['person'][0]['home_address_1']);
     		$this->view->assign('address2',$details['person'][0]['home_address_2']);
     		$this->view->assign('city',$details['person'][0]['city']);
     		$this->view->assign('zip',$details['person'][0]['home_postal_code']);
     		$this->view->assign('enroll',$details['person'][0]['home_address_2']);
//.........这里部分代码省略.........

 public function peopleaddAction()
 {
     //locations
     if (count($_POST) > 0) {
         if (isset($_POST['addpeople'])) {
             $peopleadd = new PeopleAdd();
             # TRIGGERS ADDING PERSON
             $tutorid = $peopleadd->addTutor($_POST);
             switch ($_POST['type']) {
                 case "tutor":
                     $this->_redirect(Settings::$COUNTRY_BASE_URL . "/tutoredit/tutoredit/id/" . $tutorid);
                     break;
             }
         }
         exit;
     }
     $this->viewAssignEscaped('locations', Location::getAll());
     $this->view->assign('action', '../studentedit/studentedit/');
     $this->view->assign('title', $this->view->translation['Application Name']);
     $persontitle = new Peopleadd();
     $result = $persontitle->Peopletitle($fetchtitle);
     $this->view->assign('fetchtitle', $result);
     // FOr Facility
     $faclilityttitle = new Peopleadd();
     $result = $faclilityttitle->PeopleFacility($fetchfacility);
     $this->view->assign('fetchfacility', $result);
     $citylist = new Peopleadd();
     $result = $citylist->PeopleCity($citylist);
     $this->view->assign('fetchcity', $result);
     # CREATING HELPER
     $helper = new Helper();
     $this->view->assign('institutions', $helper->getInstitutions(false));
 }

 public function personsAction()
 {
     try {
         require_once 'models/table/Person.php';
         $personTable = new Person();
         $select = $personTable->select()->from('person', array('*'))->setIntegrityCheck(false);
         $rowRay = $personTable->fetchAll($select);
         $rowRay = @$rowRay->toArray();
         //sort by id
         $sorted = array();
         foreach ($rowRay as $row) {
             unset($row['suffix_option_id']);
             unset($row['title_option_id']);
             $sorted[$row['id']] = $row;
         }
         /*
         $sorted = $personTable->_fill_lookup($sorted, 'location_city', 'home_city_id', 'city_name');
         $sorted = $personTable->_fill_lookup($sorted, 'location_district', 'home_district_id', 'district_name');
         $sorted = $personTable->_fill_lookup($sorted, 'location_province', 'home_province_id', 'province_name');
         */
         $locations = Location::getAll();
         foreach ($sorted as $id => $row) {
             $city_info = Location::getCityInfo($row['home_location_id'], $this->setting('num_location_tiers'), $locations);
             if (count($city_info)) {
                 if ($city_info[0]) {
                     $sorted[$id]['city_name'] = $city_info[0];
                 }
                 if ($city_info[1]) {
                     $sorted[$id]['province_name'] = $locations[$city_info[1]]['name'];
                 }
                 if ($city_info[2]) {
                     $sorted[$id]['district_name'] = $locations[$city_info[2]]['name'];
                 }
                 if ($city_info[3]) {
                     $sorted[$id]['region_c_name'] = $locations[$city_info[3]]['name'];
                 }
                 if ($city_info[4]) {
                     $sorted[$id]['region_d_name'] = $locations[$city_info[4]]['name'];
                 }
                 if ($city_info[5]) {
                     $sorted[$id]['region_e_name'] = $locations[$city_info[5]]['name'];
                 }
                 if ($city_info[6]) {
                     $sorted[$id]['region_f_name'] = $locations[$city_info[6]]['name'];
                 }
                 if ($city_info[7]) {
                     $sorted[$id]['region_g_name'] = $locations[$city_info[7]]['name'];
                 }
                 if ($city_info[8]) {
                     $sorted[$id]['region_h_name'] = $locations[$city_info[8]]['name'];
                 }
                 if ($city_info[9]) {
                     $sorted[$id]['region_i_name'] = $locations[$city_info[9]]['name'];
                 }
             }
             unset($sorted[$id]['home_location_id']);
         }
         $sorted = $personTable->_fill_lookup($sorted, 'person_qualification_option', 'primary_qualification_option_id', 'qualification_phrase');
         $sorted = $personTable->_fill_lookup($sorted, 'person_primary_responsibility_option', 'primary_responsibility_option_id', 'responsibility_phrase');
         $sorted = $personTable->_fill_lookup($sorted, 'person_secondary_responsibility_option', 'secondary_responsibility_option_id', 'responsibility_phrase');
         $sorted = $personTable->_fill_lookup($sorted, 'person_custom_1_option', 'person_custom_1_option_id', 'custom1_phrase');
         $sorted = $personTable->_fill_lookup($sorted, 'person_custom_2_option', 'person_custom_2_option_id', 'custom2_phrase');
         $sorted = $personTable->_fill_lookup($sorted, 'facility', 'facility_id', 'facility_name');
         //fill participants
         $select = $personTable->select()->from('person', array('id'))->setIntegrityCheck(false)->join(array('pt' => 'person_to_training'), "pt.person_id = person.id", array('training_id'))->join(array('t' => 'training'), "pt.training_id = t.id", array())->join(array('tt' => 'training_title_option'), "t.training_title_option_id = tt.id", array('training_title_phrase'));
         $rows = $personTable->fetchAll($select);
         foreach ($rows as $row) {
             $pid = $row->id;
             $ra = $row->toArray();
             unset($ra['id']);
             $sorted[$pid]['courses'][] = $ra;
         }
         //fill trainers
         $select = $personTable->select()->from('trainer', array('person_id'))->setIntegrityCheck(false)->join(array('pt' => 'training_to_trainer'), "pt.trainer_id = trainer.person_id", array('training_id'))->join(array('t' => 'training'), "pt.training_id = t.id", array())->join(array('tt' => 'training_title_option'), "t.training_title_option_id = tt.id", array('training_title_phrase'));
         $rows = $personTable->fetchAll($select);
         foreach ($rows as $row) {
             $pid = $row->person_id;
             $ra = $row->toArray();
             unset($ra['person_id']);
             $sorted[$pid]['trained'][] = $ra;
         }
         if ($this->getSanParam('outputType') == 'csv') {
             $this->sendData($this->reportHeaders(false, $sorted));
         }
         $this->view->assign('data', $sorted);
     } catch (Exception $e) {
         echo $e->getMessage() . "<br>" . PHP_EOL;
     }
 }

 function getSelect()
 {
     $results = Location::getAll("city");
     foreach ($results as $row) {
         $temp[] = array("value" => $row['id'], "label" => stripslashes($row['title']));
     }
     return $temp;
 }

//.........这里部分代码省略.........
             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);
 }

 public function searchAction()
 {
     if (!$this->hasACL('edit_employee')) {
         $this->doNoAccessError();
     }
     $criteria = $this->getAllParams();
     if ($criteria['go']) {
         // process search
         $where = array();
         list($a, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
         list($locationFlds, $locationsubquery) = Location::subquery($this->setting('num_location_tiers'), $location_tier, $location_id);
         $sql = "SELECT DISTINCT\r\n\t\t\t\t\tpartner.id,partner.partner,partner.location_id," . implode(',', $locationFlds) . "\r\n\t\t\t\t\t,GROUP_CONCAT(funderopt.funder_phrase) as funder\r\n\t\t\t\t\t,GROUP_CONCAT(funders.funder_end_date) as funding_end_date\r\n\t\t\t\t\t,GROUP_CONCAT(subp.partner) as subpartners\r\n\t\t\t\t\tFROM partner LEFT JOIN ({$locationsubquery}) as l  ON l.id = partner.location_id\r\n\t\t\t\t\tLEFT JOIN partner_to_funder funders         ON partner.id = funders.partner_id\r\n\t\t\t\t\tLEFT JOIN partner_funder_option funderopt   ON funders.partner_funder_option_id = funderopt.id\r\n\t\t\t\t\tLEFT JOIN partner_to_subpartner subpartners ON subpartners.partner_id = partner.id\r\n\t\t\t\t\tLEFT JOIN partner subp                      ON subp.id = subpartners.subpartner_id ";
         // restricted access?? only show partners by organizers that we have the ACL to view
         #$org_allowed_ids = allowed_org_access_full_list($this); // doesnt have acl 'training_organizer_option_all'
         #if($org_allowed_ids)
         #	$where[] = " partner.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[] = " partner.organizer_option_id in ($site_orgs) ";
         $locationWhere = $this->getLocationCriteriaWhereClause($criteria, '', '');
         if ($locationWhere) {
             $where[] = $locationWhere;
         }
         if ($criteria['subpartner_id']) {
             $where[] = 'subpartners.subpartner_id = ' . $criteria['subpartner_id'];
         }
         if ($criteria['partner_id']) {
             $where[] = 'partner.id = ' . $criteria['partner_id'];
         }
         if ($criteria['start_date']) {
             $where[] = 'funder_end_date >= \'' . $this->_date_to_sql($criteria['start_date']) . ' 00:00:00\'';
         }
         if ($criteria['end_date']) {
             $where[] = 'funder_end_date <= \'' . $this->_date_to_sql($criteria['end_date']) . ' 23:59:59\'';
         }
         if (count($where)) {
             $sql .= ' WHERE ' . implode(' AND ', $where);
         }
         $sql .= ' GROUP BY partner.id ';
         $db = $this->dbfunc();
         $rows = $db->fetchAll($sql);
         $this->viewAssignEscaped('results', $rows);
         $this->view->assign('count', count($rows));
     }
     // assign form drop downs
     $this->view->assign('status', $status);
     $this->viewAssignEscaped('criteria', $criteria);
     $this->viewAssignEscaped('locations', Location::getAll());
     $this->view->assign('partners', DropDown::generateHtml('partner', 'partner', $criteria['partner_id'], false, $this->view->viewonly, false));
     $this->view->assign('subpartners', DropDown::generateHtml('partner', 'partner', $criteria['subpartner_id'], false, $this->view->viewonly, false, true, array('name' => 'subpartner_id'), true));
 }

 public function clearLocationRenders()
 {
     $query = "SELECT `id`, `name`\n\t\t\t\t\tFROM Location\n\t\t\t\t\tWHERE id > 1 AND nefub_id IS NOT NULL";
     $result = Database::query($query);
     $locations = Location::getAll();
     foreach ($locations as $location) {
         if ($location->nefub_id) {
             $url = HOST . '/locatie/' . $location->nefub_id . '?clear=1';
             self::put("Maak pagina voor locatie " . $location->name);
             get_headers($url);
         }
     }
 }

 public function partnersAction()
 {
     require_once 'models/table/Helper.php';
     require_once 'views/helpers/FormHelper.php';
     require_once 'views/helpers/DropDown.php';
     require_once 'views/helpers/Location.php';
     require_once 'views/helpers/CheckBoxes.php';
     require_once 'views/helpers/TrainingViewHelper.php';
     $criteria = $this->getAllParams();
     if ($criteria['go']) {
         $where = array();
         $criteria['last_selected_rgn'] = regionFiltersGetLastID('', $criteria);
         list($a, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
         list($locationFlds, $locationsubquery) = Location::subquery($this->setting('num_location_tiers'), $location_tier, $location_id);
         $sql = "SELECT\r\n\t\t\t\t\tpartner.*,\r\n\t\t\t\t\tpartner.id,partner.partner,partner.location_id," . implode(',', $locationFlds) . "\r\n\t\t\t\t\t,GROUP_CONCAT(distinct facility.facility_name) as facilities\r\n\t\t\t\t\t,CASE WHEN annual_cost REGEXP '[^!0-9,\\.][0-9\\.,]+' THEN SUBSTRING(annual_cost, 2) ELSE annual_cost END AS 'annual_cost_to_compare'\r\n\t\t\t\t\t,COUNT(e.id) AS pcnt\r\n\t\t\t\t\tFROM partner LEFT JOIN ({$locationsubquery}) as l ON l.id = partner.location_id\r\n\t\t\t\t\tLEFT JOIN partner_to_funder funders ON partner.id = funders.partner_id\r\n\t\t\t\t\tLEFT JOIN partner_funder_option funderopt ON funders.partner_funder_option_id = funderopt.id\r\n\t\t\t\t\tLEFT JOIN partner_to_subpartner subpartners ON subpartners.partner_id = partner.id\r\n\t\t\t\t\tLEFT JOIN employee e on e.partner_id = partner.id\r\n\t\t\t\t\tLEFT JOIN facility ON e.site_id = facility.id\r\n\t\t\t\t\t";
         if ($criteria['facility_type_option_id']) {
             $sql .= " LEFT JOIN facility_type_option fto ON fto.id = facility.type_option_id ";
         }
         // restricted access?? only show partners by organizers that we have the ACL to view
         #$org_allowed_ids = allowed_org_access_full_list($this); // doesnt have acl 'training_organizer_option_all'
         #if ($org_allowed_ids)                             $where[] = "partner.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[] = "partner.organizer_option_id in ({$site_orgs})";
         }
         // criteria
         if ($criteria['partner_id']) {
             $where[] = 'partner.id = ' . $criteria['partner_id'];
         }
         if ($criteria['last_selected_rgn']) {
             $where[] = 'province_name is not null';
         }
         // bugfix - location subquery is not working like a inner join or where, not sure why
         if ($criteria['facilityInput']) {
             $where[] = 'facility.id = ' . $criteria['facilityInput'];
         }
         if ($criteria['facility_type_option_id']) {
             $where[] = 'facility.type_option_id = ' . $criteria['facility_type_option_id'];
         }
         if ($criteria['employee_qualification_option_id']) {
             $where[] = 'employee_qualification_option_id = ' . $criteria['employee_qualification_option_id'];
         }
         if ($criteria['employee_category_option_id']) {
             $where[] = 'employee_category_option_id = ' . $criteria['employee_category_option_id'];
         }
         if ($criteria['hours_min']) {
             $where[] = 'e.funded_hours_per_week >=' . $criteria['hours_min'];
         }
         if ($criteria['hours_max']) {
             $where[] = 'e.funded_hours_per_week <=' . $criteria['hours_min'];
         }
         if ($criteria['cost_min']) {
             $where[] = 'e.annual_cost_to_compare >=' . $criteria['cost_min'];
         }
         if ($criteria['cost_max']) {
             $where[] = 'e.annual_cost_to_compare <=' . $criteria['cost_max'];
         }
         if ($criteria['employee_role_option_id']) {
             $where[] = 'funder_end_date >= \'' . $this->_date_to_sql($criteria['start_date']) . ' 00:00:00\'';
         }
         if ($criteria['partner_importance_option_id']) {
             $where[] = 'partner_importance_option_id = ' . $criteria['partner_importance_option_id'];
         }
         if ($criteria['start_date']) {
             $where[] = 'funder_end_date >= \'' . $this->_date_to_sql($criteria['start_date']) . ' 00:00:00\'';
         }
         if ($criteria['end_date']) {
             $where[] = 'funder_end_date <= \'' . $this->_date_to_sql($criteria['end_date']) . ' 23:59:59\'';
         }
         if ($criteria['employee_transition_option_id']) {
             $where[] = 'employee_transition_option_id = ' . $criteria['employee_transition_option_id'];
         }
         if ($criteria['transition_confirmed']) {
             $where[] = 'transition_confirmed = 1';
         }
         if (count($where)) {
             $sql .= ' WHERE ' . implode(' AND ', $where);
         }
         $sql .= ' GROUP BY partner.id ';
         $db = $this->dbfunc();
         $rowArray = $db->fetchAll($sql);
         $this->viewAssignEscaped('results', $rowArray);
         $this->view->assign('count', count($rowArray));
         if ($criteria['outputType']) {
             $this->sendData($this->reportHeaders(false, $rowArray));
         }
     }
     // assign form drop downs
     $this->view->assign('status', $status);
     $this->view->assign('criteria', $criteria);
     $this->view->assign('pageTitle', t('Reports'));
     $this->viewAssignEscaped('locations', Location::getAll());
     $this->view->assign('partners', DropDown::generateHtml('partner', 'partner', $criteria['partner_id'], false, $this->view->viewonly, false));
     //table, col, selected_value
     $this->view->assign('subpartners', DropDown::generateHtml('partner', 'partner', $criteria['subpartner_id'], false, $this->view->viewonly, false, true, array('name' => 'subpartner_id'), true));
     $this->view->assign('importance', DropDown::generateHtml('partner_importance_option', 'importance_phrase', $criteria['partner_importance_option_id'], false, $this->view->viewonly, false));
     $this->view->assign('transitions', DropDown::generateHtml('employee_transition_option', 'transition_phrase', $criteria['employee_transition_option_id'], false, $this->view->viewonly, false));
     $this->view->assign('incomingPartners', DropDown::generateHtml('partner', 'partner', $criteria['incoming_partner'], false, $this->view->viewonly, false, true, array('name' => 'incoming_partner'), true));
//.........这里部分代码省略.........

 function addlocationAction()
 {
     require_once 'views/helpers/DropDown.php';
     //locations
     $this->viewAssignEscaped('locations', Location::getAll());
 }

 /**
  * Training Roster
  */
 public function rosterAction()
 {
     $training_id = $this->_getParam('id');
     $this->view->assign('url', Settings::$COUNTRY_BASE_URL . "/training/roster/id/{$training_id}");
     $tableObj = new Training();
     $rowRay = $tableObj->getTrainingInfo($training_id);
     // calculate end date
     switch ($rowRay['training_length_interval']) {
         case 'week':
             $days = $rowRay['training_length_value'] * 7;
             break;
         case 'day':
             $days = $rowRay['training_length_value'] - 1;
             // start day counts as a day?
             break;
         default:
             $days = false;
             break;
     }
     if ($days) {
         $rowRay['training_end_date'] = strtotime("+{$days} day", strtotime($rowRay['training_start_date']));
         $rowRay['training_end_date'] = date('Y-m-d', $rowRay['training_end_date']);
     } else {
         $rowRay['training_end_date'] = $rowRay['training_start_date'];
     }
     $rowRay['duration'] = $rowRay['training_length_value'] . ' ' . $rowRay['training_length_interval'] . ($rowRay['training_length_value'] == 1 ? "" : "s");
     $this->viewAssignEscaped('row', $rowRay);
     // trainer/person tables
     require_once 'views/helpers/EditTableHelper.php';
     /* Trainers */
     $trainers = TrainingToTrainer::getTrainers($training_id)->toArray();
     $trainerFields = array('last_name' => $this->tr('Last Name'), 'first_name' => $this->tr('First Name'), 'duration_days' => t('Days'));
     $colStatic = array_keys($trainerFields);
     // all
     $editLinkInfo = array('disabled' => 1);
     // no edit/remove links
     $html = EditTableHelper::generateHtmlTraining('Trainer', $trainers, $trainerFields, $colStatic, array(), $editLinkInfo);
     $this->view->assign('tableTrainers', $html);
     /* Participants */
     $persons = PersonToTraining::getParticipants($training_id)->toArray();
     $personsFields = array('last_name' => $this->tr('Last Name'), 'first_name' => $this->tr('First Name'));
     if ($this->setting('module_attendance_enabled')) {
         if (strtotime($rowRay['training_start_date']) < time()) {
             $personsFields = array_merge($personsFields, array('duration_days' => t('Days')));
             // already had class(es) - show the days attended
         }
         $personsFields['award_phrase'] = $this->tr('Complete');
     }
     $personsFields = array_merge($personsFields, array('birthdate' => t('Date of Birth'), 'facility_name' => t('Facility')));
     if ($this->setting('display_viewing_location')) {
         $personsFields['location_phrase'] = $this->tr('Viewing Location');
     }
     if ($this->setting('display_budget_code')) {
         $personsFields['budget_code_phrase'] = $this->tr('Budget Code');
     }
     //if ($this->setting ( 'display_region_b' ))
     $personsFields['location_name'] = t('Location');
     //add location
     $locations = Location::getAll();
     foreach ($persons as $pid => $person) {
         $region_ids = Location::getCityInfo($person['location_id'], $this->setting('num_location_tiers'));
         $ordered_l = array($region_ids['cityname']);
         foreach ($region_ids as $key => $value) {
             if (!empty($value) && isset($locations[$value]['name'])) {
                 $ordered_l[] = $locations[$value]['name'];
             } else {
                 break;
             }
         }
         $persons[$pid]['location_name'] = implode(', ', $ordered_l);
     }
     $colStatic = array_keys($personsFields);
     // all
     $editLinkInfo = array('disabled' => 1);
     // no edit/remove links
     $html = EditTableHelper::generateHtmlTraining('Persons', $persons, $personsFields, $colStatic, array(), $editLinkInfo);
     $this->view->assign('tablePersons', $html);
     if ($this->_getParam('outputType') && $this->_getParam('trainers')) {
         $this->sendData($trainers);
     }
     if ($this->_getParam('outputType') && $this->_getParam('persons')) {
         $this->sendData($persons);
     }
 }

 public function facilitiesNewFacilitiesAction()
 {
     require_once 'views/helpers/Location.php';
     $db = Zend_Db_Table_Abstract::getDefaultAdapter();
     $criteria = array();
     list($criteria, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
     $num_locs = $this->setting('num_location_tiers');
     list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id);
     $rows = $db->fetchAll("\r\n\t\t\tselect loc.*,facility.*,types.facility_type_phrase,sponsors.facility_sponsor_phrase,facility.id as id\r\n\t\t\tfrom facility\r\n\t\t\tleft join ({$location_sub_query})    as loc   on loc.id = location_id\r\n\t\t\tleft join facility_type_option     as types on types.id = type_option_id\r\n\t\t\tleft join facility_sponsor_option  as sponsors on sponsors.id = sponsor_option_id\r\n\t\t\twhere facility.approved is null and facility.is_deleted = 0\r\n\t\t\torder by facility_name");
     $go = $this->getSanParam('go');
     if ($go) {
         require_once 'FacilityController.php';
         $c = new FacilityController($this->getRequest(), $this->getResponse());
         $c->searchAction();
     }
     // fill form dropdowns
     $this->viewAssignEscaped('primary_results', $rows);
     // facility name
     $nameArray = OptionList::suggestionListValues('facility', 'facility_name', false, false, false);
     $this->viewAssignEscaped('facility_names', $nameArray);
     // locations
     $this->viewAssignEscaped('locations', Location::getAll());
     // 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);
 }

 public function institutionsearchAction()
 {
     $ins = new Institution();
     $criteria = $this->sanitize($_POST);
     $inssearch = $ins->InstitutionSearch($criteria);
     $this->view->assign('institute', $inssearch);
     $this->view->assign('geo1', $criteria['geo1']);
     $this->view->assign('geo2', $criteria['geo2']);
     $this->view->assign('geo3', $criteria['geo3']);
     $this->view->assign('geo4', $criteria['geo4']);
     $this->view->assign('geo5', $criteria['geo5']);
     $this->view->assign('geo6', $criteria['geo6']);
     $this->view->assign('geo7', $criteria['geo7']);
     $this->view->assign('geo8', $criteria['geo8']);
     $this->view->assign('geo9', $criteria['geo9']);
     # CREATING HELPER
     $helper = new Helper();
     # GETTING INSTITUTION TYPES
     $types = $helper->getInstitutionTypes();
     $this->view->assign('institutiontypes', $types);
     # GETTING LOCATIONS
     $this->viewAssignEscaped('locations', Location::getAll());
     # GETTING SPONSORS
     $this->view->assign('sponsors', $helper->getSponsors());
 }

 private function _attach_locations($rowArray)
 {
     if ($rowArray) {
         $num_tiers = $this->setting('num_location_tiers');
         $locations = Location::getAll();
         foreach ($rowArray as $id => $row) {
             $region_ids = Location::getCityInfo($row['location_id'], $num_tiers);
             //todo rUserious? ::getcityinfo is such an expensive call(I think), it loads the entire Location table every call... todo investigate
             $rowArray[$id]['province_name'] = $locations[$region_ids['province_id']]['name'];
             $rowArray[$id]['district_name'] = $locations[$region_ids['district_id']]['name'];
             $rowArray[$id]['region_c_name'] = $locations[$region_ids['region_c_id']]['name'];
             $rowArray[$id]['region_d_name'] = $locations[$region_ids['region_d_id']]['name'];
             $rowArray[$id]['region_e_name'] = $locations[$region_ids['region_e_id']]['name'];
             $rowArray[$id]['region_f_name'] = $locations[$region_ids['region_f_id']]['name'];
             $rowArray[$id]['region_g_name'] = $locations[$region_ids['region_g_id']]['name'];
             $rowArray[$id]['region_h_name'] = $locations[$region_ids['region_h_id']]['name'];
             $rowArray[$id]['region_i_name'] = $locations[$region_ids['region_i_id']]['name'];
         }
     }
     return $rowArray;
 }

 public function searchAction()
 {
     if (!$this->hasACL('edit_employee')) {
         $this->doNoAccessError();
     }
     $criteria = $this->getAllParams();
     if ($criteria['go']) {
         // process search
         $where = array();
         list($a, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria);
         list($locationFlds, $locationsubquery) = Location::subquery($this->setting('num_location_tiers'), $location_tier, $location_id, true);
         $sql = "SELECT DISTINCT\r\n\t\t\t\t\temployee.*, " . implode(',', $locationFlds) . "\r\n\t\t\t\t\t,CONCAT(supervisor.first_name, CONCAT(' ', supervisor.last_name)) as supervisor,\r\n\t\t\t\t\tqual.qualification_phrase as staff_cadre,\r\n\t\t\t\t\tsite.facility_name,\r\n\t\t\t\t\tcategory.category_phrase as staff_category\r\n\t\t\t\t\tFROM employee LEFT JOIN ({$locationsubquery}) as l ON l.id = employee.location_id\r\n\t\t\t\t\tLEFT JOIN employee supervisor ON supervisor.id = employee.supervisor_id\r\n\t\t\t\t\tLEFT JOIN facility site ON site.id = employee.site_id\r\n\t\t\t\t\tLEFT JOIN employee_qualification_option qual ON qual.id = employee.employee_qualification_option_id\r\n\t\t\t\t\tLEFT JOIN employee_category_option category on category.id = employee.employee_category_option_id\r\n\t\t\t\t\tLEFT JOIN partner ON partner.id = employee.partner_id\r\n\t\t\t\t\t";
         #if ($criteria['partner_id']) $sql    .= ' INNER JOIN partner_to_subpartner subp ON partner.id = ' . $criteria['partner_id'];
         // restricted access?? only show partners by organizers that we have the ACL to view
         #$org_allowed_ids = allowed_org_access_full_list($this); // doesnt have acl 'training_organizer_option_all'
         #if($org_allowed_ids)
         #	$where[] = " partner.organizer_option_id in ($org_allowed_ids) ";
         $locationWhere = $this->getLocationCriteriaWhereClause($criteria, '', '');
         if ($locationWhere) {
             $where[] = $locationWhere;
         }
         if ($criteria['first_name']) {
             $where[] = "employee.first_name   = '{$criteria['first_name']}'";
         }
         if ($criteria['last_name']) {
             $where[] = "employee.last_name    = '{$criteria['last_name']}'";
         }
         if ($criteria['partner_id']) {
             $where[] = 'employee.partner_id   = ' . $criteria['partner_id'];
         }
         //todo
         if ($criteria['facilityInput']) {
             $where[] = 'employee.site_id      = ' . $criteria['facilityInput'];
         }
         if ($criteria['employee_qualification_option_id']) {
             $where[] = 'employee.employee_qualification_option_id    = ' . $criteria['employee_qualification_option_id'];
         }
         if ($criteria['category_option_id']) {
             $where[] = 'employee.staff_category_id = ' . $criteria['category_option_id'];
         }
         if (count($where)) {
             $sql .= ' WHERE ' . implode(' AND ', $where);
         }
         $db = $this->dbfunc();
         $rows = $db->fetchAll($sql);
         $this->viewAssignEscaped('results', $rows);
         $this->viewAssignEscaped('count', count($rows));
     }
     // assign form drop downs
     $helper = new Helper();
     $this->view->assign('status', $status);
     $this->viewAssignEscaped('criteria', $criteria);
     $this->viewAssignEscaped('locations', Location::getAll());
     $this->view->assign('partners', DropDown::generateHtml('partner', 'partner', $criteria['partner_id'], false, $this->view->viewonly, false));
     $this->view->assign('subpartners', DropDown::generateHtml('partner', 'partner', $criteria['partner_id'], false, $this->view->viewonly, false, false, array('name' => 'subpartner_id'), true));
     $this->view->assign('cadres', DropDown::generateHtml('employee_qualification_option', 'qualification_phrase', $criteria['employee_qualification_option_id'], false, $this->view->viewonly, false));
     $this->viewAssignEscaped('sites', $helper->getFacilities());
     $this->view->assign('categories', DropDown::generateHtml('employee_category_option', 'category_phrase', $criteria['employee_category_option_id'], false, $this->view->viewonly, false));
 }

    protected function showLocations()
    {
        switch ($this->mode) {
            case 'bewerken':
                $oLocation = Location::getByNefubId($this->editId);
                $this->editObject($oLocation, '/bewerken.tpl');
                break;
            default:
                $query = 'SELECT DISTINCT(`city`)
								FROM `Location`
								ORDER BY `city` ASC';
                $cityRows = Database::select_rows_by_query($query);
                $cities = array();
                foreach ($cityRows as $cityRow) {
                    $name = $cityRow['city'];
                    if (!is_array($cities[$name])) {
                        $cities[$name] = array();
                    }
                    $cities[$name] = Location::getAll(array('city' => $name), 'name');
                }
                $this->assign('cities', $cities);
                $this->template = '/locaties.tpl';
                $this->showOutput();
        }
    }