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_Tax
 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: /**
 29:  * Tax Calculation Resource Model
 30:  *
 31:  * @category    Mage
 32:  * @package     Mage_Tax
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: class Mage_Tax_Model_Resource_Calculation extends Mage_Core_Model_Resource_Db_Abstract
 36: {
 37:     /**
 38:      * Rates cache
 39:      *
 40:      * @var unknown
 41:      */
 42:     protected $_ratesCache              = array();
 43: 
 44:     /**
 45:      * Primery key auto increment flag
 46:      *
 47:      * @var bool
 48:      */
 49:     protected $_isPkAutoIncrement    = false;
 50: 
 51:     /**
 52:      * Resource initialization
 53:      *
 54:      */
 55:     protected function _construct()
 56:     {
 57:         $this->_setMainTable('tax/tax_calculation');
 58:     }
 59: 
 60:     /**
 61:      * Delete calculation settings by rule id
 62:      *
 63:      * @param int $ruleId
 64:      * @return Mage_Tax_Model_Resource_Calculation
 65:      */
 66:     public function deleteByRuleId($ruleId)
 67:     {
 68:         $conn = $this->_getWriteAdapter();
 69:         $where = $conn->quoteInto('tax_calculation_rule_id = ?', (int)$ruleId);
 70:         $conn->delete($this->getMainTable(), $where);
 71: 
 72:         return $this;
 73:     }
 74: 
 75:     /**
 76:      * Retreive distinct calculation
 77:      *
 78:      * @param  string $field
 79:      * @param  int $ruleId
 80:      * @return array
 81:      */
 82:     public function getDistinct($field, $ruleId)
 83:     {
 84:         $select = $this->_getReadAdapter()->select();
 85:         $select->from($this->getMainTable(), $field)
 86:             ->where('tax_calculation_rule_id = ?', (int)$ruleId);
 87: 
 88:         return $this->_getReadAdapter()->fetchCol($select);
 89:     }
 90: 
 91:     /**
 92:      * Get tax rate information: calculation process data and tax rate
 93:      *
 94:      * @param Varien_Object $request
 95:      * @return array
 96:      */
 97:     public function getRateInfo($request)
 98:     {
 99:         $rates = $this->_getRates($request);
100:         return array(
101:             'process'   => $this->getCalculationProcess($request, $rates),
102:             'value'     => $this->_calculateRate($rates)
103:         );
104:     }
105: 
106:     /**
107:      * Get tax rate for specific tax rate request
108:      *
109:      * @param Varien_Object $request
110:      * @return int
111:      */
112:     public function getRate($request)
113:     {
114:         return $this->_calculateRate($this->_getRates($request));
115:     }
116: 
117:     /**
118:      * Retrieve Calculation Process
119:      *
120:      * @param Varien_Object $request
121:      * @param array $rates
122:      * @return array
123:      */
124:     public function getCalculationProcess($request, $rates = null)
125:     {
126:         if (is_null($rates)) {
127:             $rates = $this->_getRates($request);
128:         }
129: 
130:         $result = array();
131:         $row = array();
132:         $ids = array();
133:         $currentRate = 0;
134:         $totalPercent = 0;
135:         $countedRates = count($rates);
136:         for ($i = 0; $i < $countedRates; $i++) {
137:             $rate = $rates[$i];
138:             $value = (isset($rate['value']) ? $rate['value'] : $rate['percent'])*1;
139: 
140:             $oneRate = array(
141:                 'code'=>$rate['code'],
142:                 'title'=>$rate['title'],
143:                 'percent'=>$value,
144:                 'position'=>$rate['position'],
145:                 'priority'=>$rate['priority'],
146:             );
147:             if (isset($rate['tax_calculation_rule_id'])) {
148:                 $oneRate['rule_id'] = $rate['tax_calculation_rule_id'];
149:             }
150: 
151:             if (isset($rate['hidden'])) {
152:                 $row['hidden'] = $rate['hidden'];
153:             }
154: 
155:             if (isset($rate['amount'])) {
156:                 $row['amount'] = $rate['amount'];
157:             }
158: 
159:             if (isset($rate['base_amount'])) {
160:                 $row['base_amount'] = $rate['base_amount'];
161:             }
162:             if (isset($rate['base_real_amount'])) {
163:                 $row['base_real_amount'] = $rate['base_real_amount'];
164:             }
165:             $row['rates'][] = $oneRate;
166: 
167:             if (isset($rates[$i+1]['tax_calculation_rule_id'])) {
168:                 $rule = $rate['tax_calculation_rule_id'];
169:             }
170:             $priority = $rate['priority'];
171:             $ids[] = $rate['code'];
172: 
173:             if (isset($rates[$i+1]['tax_calculation_rule_id'])) {
174:                 while(isset($rates[$i+1]) && $rates[$i+1]['tax_calculation_rule_id'] == $rule) {
175:                     $i++;
176:                 }
177:             }
178: 
179:             $currentRate += $value;
180: 
181:             if (!isset($rates[$i+1]) || $rates[$i+1]['priority'] != $priority
182:                 || (isset($rates[$i+1]['process']) && $rates[$i+1]['process'] != $rate['process'])
183:             ) {
184:                 $row['percent'] = (100+$totalPercent)*($currentRate/100);
185:                 $row['id'] = implode($ids);
186:                 $result[] = $row;
187:                 $row = array();
188:                 $ids = array();
189: 
190:                 $totalPercent += (100+$totalPercent)*($currentRate/100);
191:                 $currentRate = 0;
192:             }
193:         }
194: 
195:         return $result;
196:     }
197: 
198:     /**
199:      * Create search templates for postcode
200:      *
201:      * @param string $postcode
202:      * @return array  $strArr
203:      */
204:     protected function _createSearchPostCodeTemplates($postcode)
205:     {
206:         $len = Mage::helper('tax')->getPostCodeSubStringLength();
207:         $strlen = strlen($postcode);
208:         if ($strlen > $len) {
209:             $postcode = substr($postcode, 0, $len);
210:             $strlen = $len;
211:         }
212: 
213:         $strArr = array($postcode, $postcode . '*');
214:         if ($strlen > 1) {
215:             for ($i = 1; $i < $strlen; $i++) {
216:                 $strArr[] = sprintf('%s*', substr($postcode, 0, - $i));
217:             }
218:         }
219: 
220:         return $strArr;
221:     }
222: 
223:     /**
224:      * Returns tax rates for request - either pereforms SELECT from DB, or returns already cached result
225:      * Notice that productClassId due to optimization can be array of ids
226:      *
227:      * @param Varien_Object $request
228:      * @return array
229:      */
230:     protected function _getRates($request)
231:     {
232:         // Extract params that influence our SELECT statement and use them to create cache key
233:         $storeId = Mage::app()->getStore($request->getStore())->getId();
234:         $customerClassId = $request->getCustomerClassId();
235:         $countryId = $request->getCountryId();
236:         $regionId = $request->getRegionId();
237:         $postcode = $request->getPostcode();
238: 
239:         // Process productClassId as it can be array or usual value. Form best key for cache.
240:         $productClassId = $request->getProductClassId();
241:         $ids = is_array($productClassId) ? $productClassId : array($productClassId);
242:         foreach ($ids as $key => $val) {
243:             $ids[$key] = (int) $val; // Make it integer for equal cache keys even in case of null/false/0 values
244:         }
245:         $ids = array_unique($ids);
246:         sort($ids);
247:         $productClassKey = implode(',', $ids);
248: 
249:         // Form cache key and either get data from cache or from DB
250:         $cacheKey = implode('|', array($storeId, $customerClassId, $productClassKey, $countryId, $regionId, $postcode));
251: 
252:         if (!isset($this->_ratesCache[$cacheKey])) {
253:             // Make SELECT and get data
254:             $select = $this->_getReadAdapter()->select();
255:             $select
256:                 ->from(array('main_table' => $this->getMainTable()),
257:                 array(  'tax_calculation_rate_id',
258:                         'tax_calculation_rule_id',
259:                         'customer_tax_class_id',
260:                         'product_tax_class_id'
261:                     )
262:                 )
263:                 ->where('customer_tax_class_id = ?', (int)$customerClassId);
264:             if ($productClassId) {
265:                 $select->where('product_tax_class_id IN (?)', $productClassId);
266:             }
267:             $ifnullTitleValue = $this->_getReadAdapter()->getCheckSql(
268:                 'title_table.value IS NULL',
269:                 'rate.code',
270:                 'title_table.value'
271:             );
272:             $ruleTableAliasName = $this->_getReadAdapter()->quoteIdentifier('rule.tax_calculation_rule_id');
273:             $select
274:                 ->join(
275:                     array('rule' => $this->getTable('tax/tax_calculation_rule')),
276:                     $ruleTableAliasName . ' = main_table.tax_calculation_rule_id',
277:                     array('rule.priority', 'rule.position'))
278:                 ->join(
279:                     array('rate'=>$this->getTable('tax/tax_calculation_rate')),
280:                     'rate.tax_calculation_rate_id = main_table.tax_calculation_rate_id',
281:                     array(
282:                         'value' => 'rate.rate',
283:                         'rate.tax_country_id',
284:                         'rate.tax_region_id',
285:                         'rate.tax_postcode',
286:                         'rate.tax_calculation_rate_id',
287:                         'rate.code'
288:                 ))
289:                 ->joinLeft(
290:                     array('title_table' => $this->getTable('tax/tax_calculation_rate_title')),
291:                    "rate.tax_calculation_rate_id = title_table.tax_calculation_rate_id "
292:                    . "AND title_table.store_id = '{$storeId}'",
293:                     array('title' => $ifnullTitleValue))
294:                 ->where('rate.tax_country_id = ?', $countryId)
295:                 ->where("rate.tax_region_id IN(?)", array(0, (int)$regionId));
296:             $postcodeIsNumeric = is_numeric($postcode);
297:             $postcodeIsRange = is_string($postcode) && preg_match('/^(.+)-(.+)$/', $postcode, $matches);
298:             if ($postcodeIsRange) {
299:                 $zipFrom = $matches[1];
300:                 $zipTo = $matches[2];
301:             }
302: 
303:             if ($postcodeIsNumeric || $postcodeIsRange) {
304:                 $selectClone = clone $select;
305:                 $selectClone->where('rate.zip_is_range IS NOT NULL');
306:             }
307:             $select->where('rate.zip_is_range IS NULL');
308: 
309:             if ($postcode != '*' || $postcodeIsRange) {
310:                 $select
311:                     ->where("rate.tax_postcode IS NULL OR rate.tax_postcode IN('*', '', ?)",
312:                         $postcodeIsRange ? $postcode : $this->_createSearchPostCodeTemplates($postcode));
313:                 if ($postcodeIsNumeric) {
314:                     $selectClone
315:                         ->where('? BETWEEN rate.zip_from AND rate.zip_to', $postcode);
316:                 } else if ($postcodeIsRange) {
317:                     $selectClone->where('rate.zip_from >= ?', $zipFrom)
318:                         ->where('rate.zip_to <= ?', $zipTo);
319:                 }
320:             }
321: 
322:             /**
323:              * @see ZF-7592 issue http://framework.zend.com/issues/browse/ZF-7592
324:              */
325:             if ($postcodeIsNumeric || $postcodeIsRange) {
326:                 $select = $this->_getReadAdapter()->select()->union(
327:                     array(
328:                         '(' . $select . ')',
329:                         '(' . $selectClone . ')'
330:                     )
331:                 );
332:             }
333: 
334:             $select->order('priority ' . Varien_Db_Select::SQL_ASC)
335:                    ->order('tax_calculation_rule_id ' . Varien_Db_Select::SQL_ASC)
336:                    ->order('tax_country_id ' . Varien_Db_Select::SQL_DESC)
337:                    ->order('tax_region_id ' . Varien_Db_Select::SQL_DESC)
338:                    ->order('tax_postcode ' . Varien_Db_Select::SQL_DESC)
339:                    ->order('value ' . Varien_Db_Select::SQL_DESC);
340: 
341:             $this->_ratesCache[$cacheKey] = $this->_getReadAdapter()->fetchAll($select);
342:         }
343: 
344:         return $this->_ratesCache[$cacheKey];
345:     }
346: 
347:     /**
348:      * Calculate rate
349:      *
350:      * @param array $rates
351:      * @return int
352:      */
353:     protected function _calculateRate($rates)
354:     {
355:         $result      = 0;
356:         $currentRate = 0;
357:         $countedRates = count($rates);
358:         for ($i = 0; $i < $countedRates; $i++) {
359:             $rate       = $rates[$i];
360:             $rule       = $rate['tax_calculation_rule_id'];
361:             $value      = $rate['value'];
362:             $priority   = $rate['priority'];
363: 
364:             while(isset($rates[$i+1]) && $rates[$i+1]['tax_calculation_rule_id'] == $rule) {
365:                 $i++;
366:             }
367: 
368:             $currentRate += $value;
369: 
370:             if (!isset($rates[$i+1]) || $rates[$i+1]['priority'] != $priority) {
371:                 $result += (100+$result)*($currentRate/100);
372:                 $currentRate = 0;
373:             }
374:         }
375: 
376:         return $result;
377:     }
378: 
379:     /**
380:      * Retrieve rate ids
381:      *
382:      * @param Varien_Object $request
383:      * @return array
384:      */
385:     public function getRateIds($request)
386:     {
387:         $result = array();
388:         $rates  = $this->_getRates($request);
389:         $countedRates = count($rates);
390:         for ($i = 0; $i < $countedRates; $i++) {
391:             $rate = $rates[$i];
392:             $rule = $rate['tax_calculation_rule_id'];
393:             $result[] = $rate['tax_calculation_rate_id'];
394:             while(isset($rates[$i+1]) && $rates[$i+1]['tax_calculation_rule_id'] == $rule) {
395:                 $i++;
396:             }
397:         }
398: 
399:         return $result;
400:     }
401: 
402:     /**
403:      * Retrieve rates by customer tax class
404:      *
405:      * @param int $customerTaxClassId
406:      * @param int $productTaxClassId
407:      * @return array
408:      */
409:     public function getRatesByCustomerTaxClass($customerTaxClass, $productTaxClass = null)
410:     {
411:         $adapter = $this->_getReadAdapter();
412:         $customerTaxClassId = (int)$customerTaxClass;
413:         $calcJoinConditions = array(
414:             'calc_table.tax_calculation_rate_id = main_table.tax_calculation_rate_id',
415:             $adapter->quoteInto('calc_table.customer_tax_class_id = ?', $customerTaxClassId),
416: 
417:         );
418:         if ($productTaxClass !== null) {
419:             $productTaxClassId = (int)$productTaxClass;
420:             $calcJoinConditions[] = $adapter->quoteInto('calc_table.product_tax_class_id = ?', $productTaxClassId);
421:         }
422: 
423:         $selectCSP = $adapter->select();
424:         $selectCSP
425:             ->from(
426:                 array('main_table' => $this->getTable('tax/tax_calculation_rate')),
427:                 array('country' => 'tax_country_id', 'region_id' => 'tax_region_id', 'postcode' => 'tax_postcode'))
428:             ->joinInner(
429:                 array('calc_table' => $this->getTable('tax/tax_calculation')),
430:                 implode(' AND ', $calcJoinConditions),
431:                 array('product_class' => 'calc_table.product_tax_class_id'))
432:             ->joinLeft(
433:                 array('state_table' => $this->getTable('directory/country_region')),
434:                 'state_table.region_id = main_table.tax_region_id',
435:                 array('region_code' => 'state_table.code'))
436:             ->distinct(true);
437: 
438:         $CSP = $adapter->fetchAll($selectCSP);
439: 
440:         $result = array();
441:         foreach ($CSP as $one) {
442:             $request = new Varien_Object();
443:             $request->setCountryId($one['country'])
444:                 ->setRegionId($one['region_id'])
445:                 ->setPostcode($one['postcode'])
446:                 ->setCustomerClassId($customerTaxClassId)
447:                 ->setProductClassId($one['product_class']);
448: 
449:             $rate = $this->getRate($request);
450:             if ($rate) {
451:                 $row = array(
452:                     'value'         => $rate/100,
453:                     'country'       => $one['country'],
454:                     'state'         => $one['region_code'],
455:                     'postcode'      => $one['postcode'],
456:                     'product_class' => $one['product_class'],
457:                 );
458: 
459:                 $result[] = $row;
460:             }
461:         }
462: 
463:         return $result;
464:     }
465: }
466: