/** * @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; }
/** * @param RateRequest $request * @return \Magento\Shipping\Model\Rate\Result|bool */ public function collectRates(RateRequest $request) { if (!$this->getConfigFlag('active')) { return false; } #print_r(get_class_methods($request)); $info = array('city' => $request->getDestCity(), 'company' => $request->getDestCompany(), 'country_id' => $request->getDestCountryId(), 'firstname' => $request->getDestFirstname(), 'lastname' => $request->getDestLastname(), 'postcode' => $request->getDestPostcode(), 'region' => $request->getDestRegion(), 'region_code' => $request->getDestRegionCode(), 'region_id' => $request->getDestRegionId(), 'street' => $request->getDestStreet(), 'telphone' => $request->getDestTelphone()); #$this->_logger->addDebug('$info'); $this->_logger->log(100, print_r($info, true)); $quoteArr = null; foreach ($request->getAllItems() as $item) { $quoteArr['item_id'] = $item->getitem_id(); $quoteArr['quote_id'] = $item->getquote_id(); $quoteArr['product_id'] = $item->getproduct_id(); $quoteArr['parent_item_id'] = $item->getparent_item_id(); $quoteArr['is_virtual'] = $item->getis_virtual(); $quoteArr['sku'] = $item->getsku(); $quoteArr['name'] = $item->getname(); $quoteArr['qty'] = $item->getqty(); $quoteArr['weight'] = $item->getweight(); $quoteArr['price'] = $item->getprice(); $quoteArr['base_price'] = $item->getbase_price(); $quoteArr['custom_price'] = $item->getcustom_price(); $quoteArr['discount_percent'] = $item->getdiscount_percent(); $quoteArr['discount_amount'] = $item->getdiscount_amount(); } #$this->_logger->addDebug('测试'); $this->_logger->log(100, print_r($quoteArr, true)); /** @var \Magento\Shipping\Model\Rate\Result $result */ $result = $this->_rateResultFactory->create(); $shippingPrice = $this->getConfigData('price'); $method = $this->_rateMethodFactory->create(); $method->setCarrier($this->_code); $method->setCarrierTitle($this->getConfigData('title')); $method->setMethod($this->_code); $method->setMethodTitle($this->getConfigData('name')); $method->setPrice($shippingPrice); $method->setCost($shippingPrice); $result->append($method); return $result; }
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; }
/** * 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; }