1: <?php
  2: /**
  3:  * Magento
  4:  *
  5:  * NOTICE OF LICENSE
  6:  *
  7:  * This source file is subject to the Open Software License (OSL 3.0)
  8:  * that is bundled with this package in the file LICENSE.txt.
  9:  * It is also available through the world-wide-web at this URL:
 10:  * http://opensource.org/licenses/osl-3.0.php
 11:  * If you did not receive a copy of the license and are unable to
 12:  * obtain it through the world-wide-web, please send an email
 13:  * to license@magentocommerce.com so we can send you a copy immediately.
 14:  *
 15:  * DISCLAIMER
 16:  *
 17:  * Do not edit or add to this file if you wish to upgrade Magento to newer
 18:  * versions in the future. If you wish to customize Magento for your
 19:  * needs please refer to http://www.magentocommerce.com for more information.
 20:  *
 21:  * @category    Mage
 22:  * @package     Mage_Shipping
 23:  * @copyright   Copyright (c) 2012 Magento Inc. (http://www.magentocommerce.com)
 24:  * @license     http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 25:  */
 26: 
 27: /**
 28:  * Shipping table rates
 29:  *
 30:  * @category   Mage
 31:  * @package    Mage_Shipping
 32:  * @author     Magento Core Team <core@magentocommerce.com>
 33:  */
 34: class Mage_Shipping_Model_Resource_Carrier_Tablerate extends Mage_Core_Model_Resource_Db_Abstract
 35: {
 36:     /**
 37:      * Import table rates website ID
 38:      *
 39:      * @var int
 40:      */
 41:     protected $_importWebsiteId     = 0;
 42: 
 43:     /**
 44:      * Errors in import process
 45:      *
 46:      * @var array
 47:      */
 48:     protected $_importErrors        = array();
 49: 
 50:     /**
 51:      * Count of imported table rates
 52:      *
 53:      * @var int
 54:      */
 55:     protected $_importedRows        = 0;
 56: 
 57:     /**
 58:      * Array of unique table rate keys to protect from duplicates
 59:      *
 60:      * @var array
 61:      */
 62:     protected $_importUniqueHash    = array();
 63: 
 64:     /**
 65:      * Array of countries keyed by iso2 code
 66:      *
 67:      * @var array
 68:      */
 69:     protected $_importIso2Countries;
 70: 
 71:     /**
 72:      * Array of countries keyed by iso3 code
 73:      *
 74:      * @var array
 75:      */
 76:     protected $_importIso3Countries;
 77: 
 78:     /**
 79:      * Associative array of countries and regions
 80:      * [country_id][region_code] = region_id
 81:      *
 82:      * @var array
 83:      */
 84:     protected $_importRegions;
 85: 
 86:     /**
 87:      * Import Table Rate condition name
 88:      *
 89:      * @var string
 90:      */
 91:     protected $_importConditionName;
 92: 
 93:     /**
 94:      * Array of condition full names
 95:      *
 96:      * @var array
 97:      */
 98:     protected $_conditionFullNames  = array();
 99: 
100:     /**
101:      * Define main table and id field name
102:      *
103:      * @return void
104:      */
105:     protected function _construct()
106:     {
107:         $this->_init('shipping/tablerate', 'pk');
108:     }
109: 
110:     /**
111:      * Return table rate array or false by rate request
112:      *
113:      * @param Mage_Shipping_Model_Rate_Request $request
114:      * @return array|boolean
115:      */
116:     public function getRate(Mage_Shipping_Model_Rate_Request $request)
117:     {
118:         $adapter = $this->_getReadAdapter();
119:         $bind = array(
120:             ':website_id' => (int) $request->getWebsiteId(),
121:             ':country_id' => $request->getDestCountryId(),
122:             ':region_id' => (int) $request->getDestRegionId(),
123:             ':postcode' => $request->getDestPostcode()
124:         );
125:         $select = $adapter->select()
126:             ->from($this->getMainTable())
127:             ->where('website_id = :website_id')
128:             ->order(array('dest_country_id DESC', 'dest_region_id DESC', 'dest_zip DESC'))
129:             ->limit(1);
130: 
131:         // Render destination condition
132:         $orWhere = '(' . implode(') OR (', array(
133:             "dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = :postcode",
134:             "dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = ''",
135: 
136:             // Handle asterix in dest_zip field
137:             "dest_country_id = :country_id AND dest_region_id = :region_id AND dest_zip = '*'",
138:             "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = '*'",
139:             "dest_country_id = '0' AND dest_region_id = :region_id AND dest_zip = '*'",
140:             "dest_country_id = '0' AND dest_region_id = 0 AND dest_zip = '*'",
141: 
142:             "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = ''",
143:             "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = :postcode",
144:             "dest_country_id = :country_id AND dest_region_id = 0 AND dest_zip = '*'",
145:         )) . ')';
146:         $select->where($orWhere);
147: 
148:         // Render condition by condition name
149:         if (is_array($request->getConditionName())) {
150:             $orWhere = array();
151:             $i = 0;
152:             foreach ($request->getConditionName() as $conditionName) {
153:                 $bindNameKey  = sprintf(':condition_name_%d', $i);
154:                 $bindValueKey = sprintf(':condition_value_%d', $i);
155:                 $orWhere[] = "(condition_name = {$bindNameKey} AND condition_value <= {$bindValueKey})";
156:                 $bind[$bindNameKey] = $conditionName;
157:                 $bind[$bindValueKey] = $request->getData($conditionName);
158:                 $i++;
159:             }
160: 
161:             if ($orWhere) {
162:                 $select->where(implode(' OR ', $orWhere));
163:             }
164:         } else {
165:             $bind[':condition_name']  = $request->getConditionName();
166:             $bind[':condition_value'] = $request->getData($request->getConditionName());
167: 
168:             $select->where('condition_name = :condition_name');
169:             $select->where('condition_value <= :condition_value');
170:         }
171: 
172:         $result = $adapter->fetchRow($select, $bind);
173:         // Normalize destination zip code
174:         if ($result && $result['dest_zip'] == '*') {
175:             $result['dest_zip'] = '';
176:         }
177:         return $result;
178:     }
179: 
180:     /**
181:      * Upload table rate file and import data from it
182:      *
183:      * @param Varien_Object $object
184:      * @throws Mage_Core_Exception
185:      * @return Mage_Shipping_Model_Resource_Carrier_Tablerate
186:      */
187:     public function uploadAndImport(Varien_Object $object)
188:     {
189:         if (empty($_FILES['groups']['tmp_name']['tablerate']['fields']['import']['value'])) {
190:             return $this;
191:         }
192: 
193:         $csvFile = $_FILES['groups']['tmp_name']['tablerate']['fields']['import']['value'];
194:         $website = Mage::app()->getWebsite($object->getScopeId());
195: 
196:         $this->_importWebsiteId     = (int)$website->getId();
197:         $this->_importUniqueHash    = array();
198:         $this->_importErrors        = array();
199:         $this->_importedRows        = 0;
200: 
201:         $io     = new Varien_Io_File();
202:         $info   = pathinfo($csvFile);
203:         $io->open(array('path' => $info['dirname']));
204:         $io->streamOpen($info['basename'], 'r');
205: 
206:         // check and skip headers
207:         $headers = $io->streamReadCsv();
208:         if ($headers === false || count($headers) < 5) {
209:             $io->streamClose();
210:             Mage::throwException(Mage::helper('shipping')->__('Invalid Table Rates File Format'));
211:         }
212: 
213:         if ($object->getData('groups/tablerate/fields/condition_name/inherit') == '1') {
214:             $conditionName = (string)Mage::getConfig()->getNode('default/carriers/tablerate/condition_name');
215:         } else {
216:             $conditionName = $object->getData('groups/tablerate/fields/condition_name/value');
217:         }
218:         $this->_importConditionName = $conditionName;
219: 
220:         $adapter = $this->_getWriteAdapter();
221:         $adapter->beginTransaction();
222: 
223:         try {
224:             $rowNumber  = 1;
225:             $importData = array();
226: 
227:             $this->_loadDirectoryCountries();
228:             $this->_loadDirectoryRegions();
229: 
230:             // delete old data by website and condition name
231:             $condition = array(
232:                 'website_id = ?'     => $this->_importWebsiteId,
233:                 'condition_name = ?' => $this->_importConditionName
234:             );
235:             $adapter->delete($this->getMainTable(), $condition);
236: 
237:             while (false !== ($csvLine = $io->streamReadCsv())) {
238:                 $rowNumber ++;
239: 
240:                 if (empty($csvLine)) {
241:                     continue;
242:                 }
243: 
244:                 $row = $this->_getImportRow($csvLine, $rowNumber);
245:                 if ($row !== false) {
246:                     $importData[] = $row;
247:                 }
248: 
249:                 if (count($importData) == 5000) {
250:                     $this->_saveImportData($importData);
251:                     $importData = array();
252:                 }
253:             }
254:             $this->_saveImportData($importData);
255:             $io->streamClose();
256:         } catch (Mage_Core_Exception $e) {
257:             $adapter->rollback();
258:             $io->streamClose();
259:             Mage::throwException($e->getMessage());
260:         } catch (Exception $e) {
261:             $adapter->rollback();
262:             $io->streamClose();
263:             Mage::logException($e);
264:             Mage::throwException(Mage::helper('shipping')->__('An error occurred while import table rates.'));
265:         }
266: 
267:         $adapter->commit();
268: 
269:         if ($this->_importErrors) {
270:             $error = Mage::helper('shipping')->__('File has not been imported. See the following list of errors: %s', implode(" \n", $this->_importErrors));
271:             Mage::throwException($error);
272:         }
273: 
274:         return $this;
275:     }
276: 
277:     /**
278:      * Load directory countries
279:      *
280:      * @return Mage_Shipping_Model_Resource_Carrier_Tablerate
281:      */
282:     protected function _loadDirectoryCountries()
283:     {
284:         if (!is_null($this->_importIso2Countries) && !is_null($this->_importIso3Countries)) {
285:             return $this;
286:         }
287: 
288:         $this->_importIso2Countries = array();
289:         $this->_importIso3Countries = array();
290: 
291:         /** @var $collection Mage_Directory_Model_Resource_Country_Collection */
292:         $collection = Mage::getResourceModel('directory/country_collection');
293:         foreach ($collection->getData() as $row) {
294:             $this->_importIso2Countries[$row['iso2_code']] = $row['country_id'];
295:             $this->_importIso3Countries[$row['iso3_code']] = $row['country_id'];
296:         }
297: 
298:         return $this;
299:     }
300: 
301:     /**
302:      * Load directory regions
303:      *
304:      * @return Mage_Shipping_Model_Resource_Carrier_Tablerate
305:      */
306:     protected function _loadDirectoryRegions()
307:     {
308:         if (!is_null($this->_importRegions)) {
309:             return $this;
310:         }
311: 
312:         $this->_importRegions = array();
313: 
314:         /** @var $collection Mage_Directory_Model_Resource_Region_Collection */
315:         $collection = Mage::getResourceModel('directory/region_collection');
316:         foreach ($collection->getData() as $row) {
317:             $this->_importRegions[$row['country_id']][$row['code']] = (int)$row['region_id'];
318:         }
319: 
320:         return $this;
321:     }
322: 
323:     /**
324:      * Return import condition full name by condition name code
325:      *
326:      * @param string $conditionName
327:      * @return string
328:      */
329:     protected function _getConditionFullName($conditionName)
330:     {
331:         if (!isset($this->_conditionFullNames[$conditionName])) {
332:             $name = Mage::getSingleton('shipping/carrier_tablerate')->getCode('condition_name_short', $conditionName);
333:             $this->_conditionFullNames[$conditionName] = $name;
334:         }
335: 
336:         return $this->_conditionFullNames[$conditionName];
337:     }
338: 
339:     /**
340:      * Validate row for import and return table rate array or false
341:      * Error will be add to _importErrors array
342:      *
343:      * @param array $row
344:      * @param int $rowNumber
345:      * @return array|false
346:      */
347:     protected function _getImportRow($row, $rowNumber = 0)
348:     {
349:         // validate row
350:         if (count($row) < 5) {
351:             $this->_importErrors[] = Mage::helper('shipping')->__('Invalid Table Rates format in the Row #%s', $rowNumber);
352:             return false;
353:         }
354: 
355:         // strip whitespace from the beginning and end of each row
356:         foreach ($row as $k => $v) {
357:             $row[$k] = trim($v);
358:         }
359: 
360:         // validate country
361:         if (isset($this->_importIso2Countries[$row[0]])) {
362:             $countryId = $this->_importIso2Countries[$row[0]];
363:         } elseif (isset($this->_importIso3Countries[$row[0]])) {
364:             $countryId = $this->_importIso3Countries[$row[0]];
365:         } elseif ($row[0] == '*' || $row[0] == '') {
366:             $countryId = '0';
367:         } else {
368:             $this->_importErrors[] = Mage::helper('shipping')->__('Invalid Country "%s" in the Row #%s.', $row[0], $rowNumber);
369:             return false;
370:         }
371: 
372:         // validate region
373:         if ($countryId != '0' && isset($this->_importRegions[$countryId][$row[1]])) {
374:             $regionId = $this->_importRegions[$countryId][$row[1]];
375:         } elseif ($row[1] == '*' || $row[1] == '') {
376:             $regionId = 0;
377:         } else {
378:             $this->_importErrors[] = Mage::helper('shipping')->__('Invalid Region/State "%s" in the Row #%s.', $row[1], $rowNumber);
379:             return false;
380:         }
381: 
382:         // detect zip code
383:         if ($row[2] == '*' || $row[2] == '') {
384:             $zipCode = '*';
385:         } else {
386:             $zipCode = $row[2];
387:         }
388: 
389:         // validate condition value
390:         $value = $this->_parseDecimalValue($row[3]);
391:         if ($value === false) {
392:             $this->_importErrors[] = Mage::helper('shipping')->__('Invalid %s "%s" in the Row #%s.', $this->_getConditionFullName($this->_importConditionName), $row[3], $rowNumber);
393:             return false;
394:         }
395: 
396:         // validate price
397:         $price = $this->_parseDecimalValue($row[4]);
398:         if ($price === false) {
399:             $this->_importErrors[] = Mage::helper('shipping')->__('Invalid Shipping Price "%s" in the Row #%s.', $row[4], $rowNumber);
400:             return false;
401:         }
402: 
403:         // protect from duplicate
404:         $hash = sprintf("%s-%d-%s-%F", $countryId, $regionId, $zipCode, $value);
405:         if (isset($this->_importUniqueHash[$hash])) {
406:             $this->_importErrors[] = Mage::helper('shipping')->__('Duplicate Row #%s (Country "%s", Region/State "%s", Zip "%s" and Value "%s").', $rowNumber, $row[0], $row[1], $zipCode, $value);
407:             return false;
408:         }
409:         $this->_importUniqueHash[$hash] = true;
410: 
411:         return array(
412:             $this->_importWebsiteId,    // website_id
413:             $countryId,                 // dest_country_id
414:             $regionId,                  // dest_region_id,
415:             $zipCode,                   // dest_zip
416:             $this->_importConditionName,// condition_name,
417:             $value,                     // condition_value
418:             $price                      // price
419:         );
420:     }
421: 
422:     /**
423:      * Save import data batch
424:      *
425:      * @param array $data
426:      * @return Mage_Shipping_Model_Resource_Carrier_Tablerate
427:      */
428:     protected function _saveImportData(array $data)
429:     {
430:         if (!empty($data)) {
431:             $columns = array('website_id', 'dest_country_id', 'dest_region_id', 'dest_zip',
432:                 'condition_name', 'condition_value', 'price');
433:             $this->_getWriteAdapter()->insertArray($this->getMainTable(), $columns, $data);
434:             $this->_importedRows += count($data);
435:         }
436: 
437:         return $this;
438:     }
439: 
440:     /**
441:      * Parse and validate positive decimal value
442:      * Return false if value is not decimal or is not positive
443:      *
444:      * @param string $value
445:      * @return bool|float
446:      */
447:     protected function _parseDecimalValue($value)
448:     {
449:         if (!is_numeric($value)) {
450:             return false;
451:         }
452:         $value = (float)sprintf('%.4F', $value);
453:         if ($value < 0.0000) {
454:             return false;
455:         }
456:         return $value;
457:     }
458: 
459:     /**
460:      * Parse and validate positive decimal value
461:      *
462:      * @see self::_parseDecimalValue()
463:      * @deprecated since 1.4.1.0
464:      * @param string $value
465:      * @return bool|float
466:      */
467:     protected function _isPositiveDecimalNumber($value)
468:     {
469:         return $this->_parseDecimalValue($value);
470:     }
471: }
472: