/**
* @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;
}
