/** * Return table rate array or false by rate request * * @param \Magento\Quote\Model\Quote\Address\RateRequest $request * @param bool $zipRangeSet * @return array|bool */ public function getRate(\Magento\Quote\Model\Quote\Address\RateRequest $request, $zipRangeSet = false) { $adapter = $this->getConnection(); $shippingData = array(); $postcode = $request->getDestPostcode(); if ($zipRangeSet && is_numeric($postcode)) { # Want to search for postcodes within a range $zipSearchString = ' AND :postcode BETWEEN dest_zip AND dest_zip_to '; } else { $zipSearchString = " AND :postcode LIKE dest_zip "; } for ($j = 0; $j < 8; $j++) { $select = $adapter->select()->from($this->getMainTable())->where('website_id = :website_id')->order(['dest_country_id DESC', 'dest_region_id DESC', 'dest_zip DESC', 'condition_from_value DESC']); $zoneWhere = ''; $bind = array(); switch ($j) { case 0: // country, region, city, postcode $zoneWhere = "dest_country_id = :country_id AND dest_region_id = :region_id AND STRCMP(LOWER(dest_city),LOWER(:city))= 0 " . $zipSearchString; // TODO Add city $bind = [':country_id' => $request->getDestCountryId(), ':region_id' => (int) $request->getDestRegionId(), ':city' => $request->getDestCity(), ':postcode' => $request->getDestPostcode()]; break; case 1: // country, region, no city, postcode $zoneWhere = "dest_country_id = :country_id AND dest_region_id = :region_id AND dest_city='' " . $zipSearchString; $bind = [':country_id' => $request->getDestCountryId(), ':region_id' => (int) $request->getDestRegionId(), ':postcode' => $request->getDestPostcode()]; break; case 2: // country, state, city, no postcode $zoneWhere = "dest_country_id = :country_id AND dest_region_id = :region_id AND STRCMP(LOWER(dest_city),LOWER(:city))= 0 AND dest_zip ='*'"; // TODO Add city search $bind = [':country_id' => $request->getDestCountryId(), ':region_id' => (int) $request->getDestRegionId(), ':city' => $request->getDestCity()]; break; case 3: //country, city, no region, no postcode $zoneWhere = "dest_country_id = :country_id AND dest_region_id = '0' AND STRCMP(LOWER(dest_city),LOWER(:city))= 0 AND dest_zip ='*'"; // TODO Add city $bind = [':country_id' => $request->getDestCountryId(), ':city' => $request->getDestCity()]; break; case 4: // country, postcode $zoneWhere = "dest_country_id = :country_id AND dest_region_id = '0' AND dest_city ='*' " . $zipSearchString; $bind = [':country_id' => $request->getDestCountryId(), ':postcode' => $request->getDestPostcode()]; break; case 5: // country, region $zoneWhere = "dest_country_id = :country_id AND dest_region_id = :region_id AND dest_city ='*' AND dest_zip ='*'"; $bind = [':country_id' => $request->getDestCountryId(), ':region_id' => (int) $request->getDestRegionId()]; break; case 6: // country $zoneWhere = "dest_country_id = :country_id AND dest_region_id = '0' AND dest_city ='*' AND dest_zip ='*'"; $bind = [':country_id' => $request->getDestCountryId()]; break; case 7: // nothing $zoneWhere = "dest_country_id = '0' AND dest_region_id = '0' AND dest_city ='*' AND dest_zip ='*'"; break; } $select->where($zoneWhere); $bind[':website_id'] = (int) $request->getWebsiteId(); $bind[':condition_name'] = $request->getConditionMRName(); $bind[':condition_value'] = $request->getData($request->getConditionMRName()); $select->where('condition_name = :condition_name'); $select->where('condition_from_value < :condition_value'); $select->where('condition_to_value >= :condition_value'); $this->_logger->debug('SQL Select: ', $select->getPart('where')); $this->_logger->debug('Bindings: ', $bind); $results = $adapter->fetchAll($select, $bind); if (!empty($results)) { $this->_logger->debug('SQL Results: ', $results); foreach ($results as $data) { $shippingData[] = $data; } break; } } return $shippingData; }