public function searchAction() { require_once 'models/table/OptionList.php'; //facilities list $criteria = array(); list($criteria, $location_tier, $location_id) = $this->getLocationCriteriaValues($criteria); $criteria['facility_name'] = $this->getSanParam('facility_name'); $criteria['type_id'] = $this->getSanParam('type_id'); $criteria['sponsor_id'] = $this->getSanParam('sponsor_id'); $criteria['outputType'] = $this->getSanParam('outputType'); $criteria['go'] = $this->getSanParam('go'); if ($criteria['go']) { $db = Zend_Db_Table_Abstract::getDefaultAdapter(); $num_locs = $this->setting('num_location_tiers'); list($field_name, $location_sub_query) = Location::subquery($num_locs, $location_tier, $location_id, true); $sql = 'SELECT facility_sponsor_option.facility_sponsor_phrase, facility.location_id, facility_type_option.facility_type_phrase, facility.facility_name, facility.id , ' . implode(',', $field_name) . ' FROM facility LEFT JOIN (' . $location_sub_query . ') as l ON facility.location_id = l.id LEFT OUTER JOIN facility_sponsor_option ON facility.sponsor_option_id = facility_sponsor_option.id LEFT OUTER JOIN facility_type_option ON facility.type_option_id = facility_type_option.id '; $where = array(); $where[] = ' facility.is_deleted = 0 '; $locationWhere = $this->getLocationCriteriaWhereClause($criteria, '', ''); if ($locationWhere) { $where[] = $locationWhere; } if ($criteria['type_id'] or $criteria['type_id'] === '0') { $where[] = ' type_option_id = "' . $criteria['type_id'] . '"'; } if ($criteria['sponsor_id'] or $criteria['sponsor_id'] === '0') { $where[] = ' sponsor_option_id = ' . $criteria['sponsor_id'] . ' '; } if ($criteria['facility_name']) { $where[] = " facility_name = '" . mysql_escape_string($criteria['facility_name']) . "'"; } if ($where) { $sql .= ' WHERE ' . implode(' AND ', $where); } $sql .= " ORDER BY " . " facility_name ASC "; $rowArray = $db->fetchAll($sql); if ($criteria['outputType']) { $this->sendData($rowArray); } $this->viewAssignEscaped('results', $rowArray); $this->view->assign('count', count($rowArray)); } $this->view->assign('criteria', $criteria); //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 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); }