/** * Build a query to find all the retailers to display on web according filter * * @param array $params Contains parameters that allow to build a query. * @param int $retailerId Only required if we want to fetch data for a single retailer. * @param int $langId The language id. * * @return array */ public function getRetailersDataByCities(array $params, $retailerId = null, $langId = null) { if (is_null($langId)) { $langId = Zend_Registry::get('languageID'); } $filters = array('countryId' => array('table' => $this->countriesTable, 'joinOn' => $this->countriesTable . '.CI_CountryID = ' . $this->countryId, 'fields' => array('countryName' => $this->countriesTable . '.CI_Name'), 'langFilter' => ' AND ' . $this->countriesTable . '.CI_LanguageID = ' . $langId), 'stateId' => array('table' => $this->statesTable, 'joinOn' => $this->stateId . ' = SI_StateID', 'fields' => array('stateName' => $this->statesTable . '.SI_Name'), 'langFilter' => ' AND ' . $this->statesTable . '.SI_LanguageID = ' . $langId), 'cityId' => array('table' => $this->citiesTable, 'joinOn' => $this->cityId . ' = ' . $this->citiesTable . '.C_ID', 'fields' => array('cityName' => $this->citiesTable . '.C_Name'), 'langFilter' => ' AND ' . $this->citiesTable . '.CI_LanguageID = ' . $langId)); $select = $this->getAll($langId, false); $select->join($this->_table, 'MP_GenericProfileMemberID=R_GenericProfileId', array('RetailerId' => 'MP_GenericProfileMemberID', 'CompanyName' => 'MP_CompanyName')); $select->join($this->adresseTable, 'AI_AddressId=R_AddressId AND AI_LanguageID = ' . $langId, array('Name' => 'AI_Name', 'AddressId' => 'AI_AddressId', 'FirstAddress' => 'AI_FirstAddress', 'SecondAddress' => 'AI_SecondAddress', 'FirstTel' => 'AI_FirstTel', 'FirstExt' => 'AI_FirstExt', 'SecondTel' => 'AI_SecondTel', 'SecondExt' => 'AI_SecondExt', 'Website' => 'AI_WebSite')); $select->join($this->adresseTableData, 'AI_AddressId=A_AddressId AND AI_LanguageID = ' . $langId, array('ZipCode' => 'A_ZipCode', 'Fax' => 'A_Fax', 'Email' => 'A_Email')); $tmpData = $filters[$params['field']]; switch ($params['field']) { case 'countryId': $hasStates = Cible_FunctionsGeneral::getStateByCode($params['value'], null, $langId); if (count($hasStates) > 0) { $select->joinLeft($this->statesTable, $this->stateId . ' = SI_StateID' . $filters['stateId']['langFilter'], array('stateName' => $this->statesTable . '.SI_Name'))->joinLeft($this->citiesTable, $this->cityId . ' = ' . $this->citiesTable . '.C_ID', array('cityName' => $this->citiesTable . '.C_Name')); } $select->joinLeft($tmpData['table'], $tmpData['joinOn'] . $tmpData['langFilter'], $tmpData['fields'])->distinct(); break; case 'stateId': $select->joinLeft($this->citiesTable, $this->cityId . ' = ' . $this->citiesTable . '.C_ID', array('cityName' => $this->citiesTable . '.C_Name'))->joinLeft($tmpData['table'], $tmpData['joinOn'] . $tmpData['langFilter'], $tmpData['fields'])->distinct(); break; case 'cityId': $select->joinLeft($this->statesTable, $this->stateId . ' = SI_StateID' . $filters['stateId']['langFilter'], array('stateName' => $this->statesTable . '.SI_Name'))->joinLeft($tmpData['table'], $tmpData['joinOn'], $tmpData['fields'])->distinct(); break; default: break; } $select->where('MP_Status = 2')->where('R_Status = 2')->where($this->{$params}['field'] . ' = ?', $params['value'])->order($this->_orderField); if ($retailerId) { $select->where('MP_GenericProfileMemberID = ?', $retailerId); } $retailers = $this->_db->fetchAll($select); return $retailers; }
public function ajaxStatesAction() { $filter = 0; if ($this->_isXmlHttpRequest) { $this->getHelper('viewRenderer')->setNoRender(); $countryId = $this->_getParam('countryId'); $languageId = $this->_getParam('langId'); $filter = $this->_getParam('filter'); $states = Cible_FunctionsGeneral::getStateByCode($countryId, null, $languageId); if (is_array($states)) { foreach ($states as $id => $data) { $statesData[$id]['id'] = $data['id']; $statesData[$id]['name'] = $data['name']; } } echo json_encode($statesData); } }