/** * @return array */ public function getBindings() { $bind = [':website_id' => (int) $this->request->getWebsiteId(), ':country_id' => $this->request->getDestCountryId(), ':region_id' => (int) $this->request->getDestRegionId(), ':postcode' => $this->request->getDestPostcode()]; // Render condition by condition name if (is_array($this->request->getConditionName())) { $i = 0; foreach ($this->request->getConditionName() as $conditionName) { $bindNameKey = sprintf(':condition_name_%d', $i); $bindValueKey = sprintf(':condition_value_%d', $i); $bind[$bindNameKey] = $conditionName; $bind[$bindValueKey] = $this->request->getData($conditionName); $i++; } } else { $bind[':condition_name'] = $this->request->getConditionName(); $bind[':condition_value'] = $this->request->getData($this->request->getConditionName()); } return $bind; }
public function getNewRate(\Magento\Quote\Model\Quote\Address\RateRequest $request, $zipRangeSet = 0) { $connection = $this->getConnection(); $postcode = $request->getDestPostcode(); $bind = [':website_id' => (int) $request->getWebsiteId(), ':country_id' => $request->getDestCountryId(), ':region_id' => (int) $request->getDestRegionId(), ':postcode' => (string) $request->getDestPostcode(), ':city' => (string) $request->getDestCity()]; 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 = $connection->quoteInto(" AND ? LIKE dest_zip )", $postcode); } for ($j = 0; $j < 10; $j++) { $select = $connection->select()->from($this->getMainTable())->where($connection->quoteInto("website_id = ?", $bind[':website_id']))->order(['dest_country_id DESC', 'dest_region_id DESC', 'dest_zip DESC']); switch ($j) { case 0: $select->where($connection->quoteInto(" (dest_country_id=? ", $bind[':country_id']) . $connection->quoteInto(" AND dest_region_id=? ", $bind[':region_id']) . $connection->quoteInto(" AND STRCMP(LOWER(dest_city),LOWER(?)) = 0 ", $bind[':city']) . $zipSearchString); break; case 1: $select->where($connection->quoteInto(" (dest_country_id=? ", $bind[':country_id']) . $connection->quoteInto(" AND dest_region_id=? AND dest_city=''", $bind[':region_id']) . $zipSearchString); break; case 2: $select->where($connection->quoteInto(" (dest_country_id=? ", $request->getDestCountryId()) . $connection->quoteInto(" AND dest_region_id=? ", $request->getDestRegionId()) . $connection->quoteInto(" AND STRCMP(LOWER(dest_city),LOWER(?)) = 0 AND dest_zip='')", $bind[':city'])); break; case 3: $select->where($connection->quoteInto(" (dest_country_id=? ", $bind[':country_id']) . $connection->quoteInto(" AND STRCMP(LOWER(dest_city),LOWER(?)) = 0 AND dest_region_id='0'", $bind[':city']) . $zipSearchString); break; case 4: $select->where($connection->quoteInto(" (dest_country_id=? ", $bind[':country_id']) . $connection->quoteInto(" AND STRCMP(LOWER(dest_city),LOWER(?)) = 0 AND dest_region_id='0' AND dest_zip='') ", $bind[':city'])); break; case 5: $select->where($connection->quoteInto(" (dest_country_id=? AND dest_region_id='0' AND dest_city='' ", $bind[':country_id']) . $zipSearchString); break; case 6: $select->where($connection->quoteInto(" (dest_country_id=? ", $bind[':country_id']) . $connection->quoteInto(" AND dest_region_id=? AND dest_city='' AND dest_zip='') ", $bind[':region_id'])); break; case 7: $select->where($connection->quoteInto(" (dest_country_id=? AND dest_region_id='0' AND dest_city='' AND dest_zip='') ", $bind[':country_id'])); break; case 8: $select->where(" (dest_country_id='0' AND dest_region_id='0'" . $zipSearchString); break; case 9: $select->where(" (dest_country_id='0' AND dest_region_id='0' AND dest_zip='')"); break; } if (is_array($request->getMRConditionName())) { $i = 0; foreach ($request->getMRConditionName() as $conditionName) { if ($i == 0) { $select->where('condition_name=?', $conditionName); } else { $select->orWhere('condition_name=?', $conditionName); } $select->where('condition_from_value<=?', $request->getData($conditionName)); $i++; } } else { $select->where('condition_name=?', $request->getMRConditionName()); $select->where('condition_from_value<=?', $request->getData($request->getMRConditionName())); $select->where('condition_to_value>=?', $request->getData($request->getMRConditionName())); } $newdata = array(); $row = $connection->fetchAll($select, $bind); if (!empty($row)) { // have found a result or found nothing and at end of list! foreach ($row as $data) { $newdata[] = $data; } break; } } return $newdata; }
/** * Generation Shipment Details Node according to origin region * * @param \Magento\Shipping\Model\Simplexml\Element $xml * @param RateRequest $rawRequest * @param string $originRegion * @return void * @SuppressWarnings(PHPMD.CyclomaticComplexity) * @SuppressWarnings(PHPMD.NPathComplexity) */ protected function _shipmentDetails($xml, $rawRequest, $originRegion = '') { $nodeShipmentDetails = $xml->addChild('ShipmentDetails', '', ''); $nodeShipmentDetails->addChild('NumberOfPieces', count($rawRequest->getPackages())); if ($originRegion) { $nodeShipmentDetails->addChild('CurrencyCode', $this->_storeManager->getWebsite($this->_request->getWebsiteId())->getBaseCurrencyCode()); } $nodePieces = $nodeShipmentDetails->addChild('Pieces', '', ''); /* * Package type * EE (DHL Express Envelope), OD (Other DHL Packaging), CP (Custom Packaging) * DC (Document), DM (Domestic), ED (Express Document), FR (Freight) * BD (Jumbo Document), BP (Jumbo Parcel), JD (Jumbo Junior Document) * JP (Jumbo Junior Parcel), PA (Parcel), DF (DHL Flyer) */ $i = 0; foreach ($rawRequest->getPackages() as $package) { $nodePiece = $nodePieces->addChild('Piece', '', ''); $packageType = 'EE'; if ($package['params']['container'] == self::DHL_CONTENT_TYPE_NON_DOC) { $packageType = 'CP'; } $nodePiece->addChild('PieceID', ++$i); $nodePiece->addChild('PackageType', $packageType); $nodePiece->addChild('Weight', round($package['params']['weight'], 1)); $params = $package['params']; if ($params['width'] && $params['length'] && $params['height']) { if (!$originRegion) { $nodePiece->addChild('Width', round($params['width'])); $nodePiece->addChild('Height', round($params['height'])); $nodePiece->addChild('Depth', round($params['length'])); } else { $nodePiece->addChild('Depth', round($params['length'])); $nodePiece->addChild('Width', round($params['width'])); $nodePiece->addChild('Height', round($params['height'])); } } $content = []; foreach ($package['items'] as $item) { $content[] = $item['name']; } $nodePiece->addChild('PieceContents', substr(implode(',', $content), 0, 34)); } if (!$originRegion) { $nodeShipmentDetails->addChild('Weight', round($rawRequest->getPackageWeight(), 1)); $nodeShipmentDetails->addChild('WeightUnit', substr($this->_getWeightUnit(), 0, 1)); $nodeShipmentDetails->addChild('GlobalProductCode', $rawRequest->getShippingMethod()); $nodeShipmentDetails->addChild('LocalProductCode', $rawRequest->getShippingMethod()); $nodeShipmentDetails->addChild('Date', $this->_coreDate->date('Y-m-d')); $nodeShipmentDetails->addChild('Contents', 'DHL Parcel'); /** * The DoorTo Element defines the type of delivery service that applies to the shipment. * The valid values are DD (Door to Door), DA (Door to Airport) , AA and DC (Door to * Door non-compliant) */ $nodeShipmentDetails->addChild('DoorTo', 'DD'); $nodeShipmentDetails->addChild('DimensionUnit', substr($this->_getDimensionUnit(), 0, 1)); if ($package['params']['container'] == self::DHL_CONTENT_TYPE_NON_DOC) { $packageType = 'CP'; } $nodeShipmentDetails->addChild('PackageType', $packageType); if ($this->getConfigData('content_type') == self::DHL_CONTENT_TYPE_NON_DOC) { $nodeShipmentDetails->addChild('IsDutiable', 'Y'); } $nodeShipmentDetails->addChild('CurrencyCode', $this->_storeManager->getWebsite($this->_request->getWebsiteId())->getBaseCurrencyCode()); } else { if ($package['params']['container'] == self::DHL_CONTENT_TYPE_NON_DOC) { $packageType = 'CP'; } $nodeShipmentDetails->addChild('PackageType', $packageType); $nodeShipmentDetails->addChild('Weight', $rawRequest->getPackageWeight()); $nodeShipmentDetails->addChild('DimensionUnit', substr($this->_getDimensionUnit(), 0, 1)); $nodeShipmentDetails->addChild('WeightUnit', substr($this->_getWeightUnit(), 0, 1)); $nodeShipmentDetails->addChild('GlobalProductCode', $rawRequest->getShippingMethod()); $nodeShipmentDetails->addChild('LocalProductCode', $rawRequest->getShippingMethod()); /** * The DoorTo Element defines the type of delivery service that applies to the shipment. * The valid values are DD (Door to Door), DA (Door to Airport) , AA and DC (Door to * Door non-compliant) */ $nodeShipmentDetails->addChild('DoorTo', 'DD'); $nodeShipmentDetails->addChild('Date', $this->_coreDate->date('Y-m-d')); $nodeShipmentDetails->addChild('Contents', 'DHL Parcel TEST'); } }
/** * 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; }
/** * Return table rate array or false by rate request * * @param \Magento\Quote\Model\Quote\Address\RateRequest $request * @return array|bool */ public function getRate(\Magento\Quote\Model\Quote\Address\RateRequest $request) { $connection = $this->getConnection(); $bind = [':website_id' => (int) $request->getWebsiteId(), ':country_id' => $request->getDestCountryId(), ':region_id' => (int) $request->getDestRegionId(), ':postcode' => $request->getDestPostcode()]; $select = $connection->select()->from($this->getMainTable())->where('website_id = :website_id')->order(['dest_country_id DESC', 'dest_region_id DESC', 'dest_zip DESC'])->limit(1); // Render destination condition $orWhere = '(' . implode(') OR (', ["dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = :postcode", "dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = ''", "dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = '*'", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = '*'", "dest_country_id = '0' AND dest_region_id = :region_id AND dest_zip = '*'", "dest_country_id = '0' AND dest_region_id = 0 AND dest_zip = '*'", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = ''", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = :postcode", "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = '*'"]) . ')'; $select->where($orWhere); // Render condition by condition name if (is_array($request->getConditionName())) { $orWhere = []; $i = 0; foreach ($request->getConditionName() as $conditionName) { $bindNameKey = sprintf(':condition_name_%d', $i); $bindValueKey = sprintf(':condition_value_%d', $i); $orWhere[] = "(condition_name = {$bindNameKey} AND condition_value <= {$bindValueKey})"; $bind[$bindNameKey] = $conditionName; $bind[$bindValueKey] = $request->getData($conditionName); $i++; } if ($orWhere) { $select->where(implode(' OR ', $orWhere)); } } else { $bind[':condition_name'] = $request->getConditionName(); $bind[':condition_value'] = $request->getData($request->getConditionName()); $select->where('condition_name = :condition_name'); $select->where('condition_value <= :condition_value'); } $result = $connection->fetchRow($select, $bind); // Normalize destination zip code if ($result && $result['dest_zip'] == '*') { $result['dest_zip'] = ''; } return $result; }