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_Sales
 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:  * Shipping report resource model
 30:  *
 31:  * @category    Mage
 32:  * @package     Mage_Sales
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: class Mage_Sales_Model_Resource_Report_Shipping extends Mage_Sales_Model_Resource_Report_Abstract
 36: {
 37:     /**
 38:      * Model initialization
 39:      *
 40:      */
 41:     protected function _construct()
 42:     {
 43:         $this->_setResource('sales');
 44:     }
 45: 
 46:     /**
 47:      * Aggregate Shipping data
 48:      *
 49:      * @param mixed $from
 50:      * @param mixed $to
 51:      * @return Mage_Sales_Model_Resource_Report_Shipping
 52:      */
 53:     public function aggregate($from = null, $to = null)
 54:     {
 55:         // convert input dates to UTC to be comparable with DATETIME fields in DB
 56:         $from = $this->_dateToUtc($from);
 57:         $to   = $this->_dateToUtc($to);
 58: 
 59:         $this->_checkDates($from, $to);
 60:         $this->_aggregateByOrderCreatedAt($from, $to);
 61:         $this->_aggregateByShippingCreatedAt($from, $to);
 62:         $this->_setFlagData(Mage_Reports_Model_Flag::REPORT_SHIPPING_FLAG_CODE);
 63:         return $this;
 64:     }
 65: 
 66:     /**
 67:      * Aggregate shipping report by order create_at as period
 68:      *
 69:      * @param mixed $from
 70:      * @param mixed $to
 71:      * @return Mage_Sales_Model_Resource_Report_Shipping
 72:      */
 73:     protected function _aggregateByOrderCreatedAt($from, $to)
 74:     {
 75:         $table       = $this->getTable('sales/shipping_aggregated_order');
 76:         $sourceTable = $this->getTable('sales/order');
 77:         $adapter     = $this->_getWriteAdapter();
 78:         $adapter->beginTransaction();
 79: 
 80:         try {
 81:             if ($from !== null || $to !== null) {
 82:                 $subSelect = $this->_getTableDateRangeSelect($sourceTable, 'created_at', 'updated_at', $from, $to);
 83:             } else {
 84:                 $subSelect = null;
 85:             }
 86: 
 87:             $this->_clearTableByDateRange($table, $from, $to, $subSelect);
 88:             // convert dates from UTC to current admin timezone
 89:             $periodExpr = $adapter->getDatePartSql(
 90:                 $this->getStoreTZOffsetQuery($sourceTable, 'created_at', $from, $to)
 91:             );
 92:             $ifnullBaseShippingCanceled = $adapter->getIfNullSql('base_shipping_canceled', 0);
 93:             $ifnullBaseShippingRefunded = $adapter->getIfNullSql('base_shipping_refunded', 0);
 94:             $columns = array(
 95:                 'period'                => $periodExpr,
 96:                 'store_id'              => 'store_id',
 97:                 'order_status'          => 'status',
 98:                 'shipping_description'  => 'shipping_description',
 99:                 'orders_count'          => new Zend_Db_Expr('COUNT(entity_id)'),
100:                 'total_shipping'        => new Zend_Db_Expr(
101:                     "SUM((base_shipping_amount - {$ifnullBaseShippingCanceled}) * base_to_global_rate)"),
102:                 'total_shipping_actual' => new Zend_Db_Expr(
103:                     "SUM((base_shipping_invoiced - {$ifnullBaseShippingRefunded}) * base_to_global_rate)"),
104:             );
105: 
106:             $select = $adapter->select();
107:             $select->from($sourceTable, $columns)
108:                  ->where('state NOT IN (?)', array(
109:                     Mage_Sales_Model_Order::STATE_PENDING_PAYMENT,
110:                     Mage_Sales_Model_Order::STATE_NEW
111:                 ))
112:                 ->where('is_virtual = 0');
113: 
114:             if ($subSelect !== null) {
115:                 $select->having($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
116:             }
117: 
118:             $select->group(array(
119:                 $periodExpr,
120:                 'store_id',
121:                 'status',
122:                 'shipping_description'
123:             ));
124: 
125:             $select->having('orders_count > 0');
126: 
127:             $helper        = Mage::getResourceHelper('core');
128:             $insertQuery   = $helper->getInsertFromSelectUsingAnalytic($select, $table, array_keys($columns));
129:             $adapter->query($insertQuery);
130: 
131:             $select->reset();
132: 
133:             $columns = array(
134:                 'period'                => 'period',
135:                 'store_id'              => new Zend_Db_Expr(Mage_Core_Model_App::ADMIN_STORE_ID),
136:                 'order_status'          => 'order_status',
137:                 'shipping_description'  => 'shipping_description',
138:                 'orders_count'          => new Zend_Db_Expr('SUM(orders_count)'),
139:                 'total_shipping'        => new Zend_Db_Expr('SUM(total_shipping)'),
140:                 'total_shipping_actual' => new Zend_Db_Expr('SUM(total_shipping_actual)'),
141:             );
142: 
143:             $select
144:                 ->from($table, $columns)
145:                 ->where('store_id != ?', 0);
146: 
147:             if ($subSelect !== null) {
148:                 $select->where($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
149:             }
150: 
151:             $select->group(array(
152:                 'period',
153:                 'order_status',
154:                 'shipping_description'
155:             ));
156: 
157:             $insertQuery = $helper->getInsertFromSelectUsingAnalytic($select, $table, array_keys($columns));
158:             $adapter->query($insertQuery);
159:         } catch (Exception $e) {
160:             $adapter->rollBack();
161:             throw $e;
162:         }
163: 
164:         $adapter->commit();
165:         return $this;
166:     }
167: 
168:     /**
169:      * Aggregate shipping report by shipment create_at as period
170:      *
171:      * @param mixed $from
172:      * @param mixed $to
173:      * @return Mage_Sales_Model_Resource_Report_Shipping
174:      */
175:     protected function _aggregateByShippingCreatedAt($from, $to)
176:     {
177:         $table       = $this->getTable('sales/shipping_aggregated');
178:         $sourceTable = $this->getTable('sales/invoice');
179:         $orderTable  = $this->getTable('sales/order');
180:         $adapter     = $this->_getWriteAdapter();
181:         $adapter->beginTransaction();
182: 
183:         try {
184:             if ($from !== null || $to !== null) {
185:                 $subSelect = $this->_getTableDateRangeRelatedSelect(
186:                     $sourceTable, $orderTable, array('order_id'=>'entity_id'),
187:                     'created_at', 'updated_at', $from, $to
188:                 );
189:             } else {
190:                 $subSelect = null;
191:             }
192: 
193:             $this->_clearTableByDateRange($table, $from, $to, $subSelect);
194:             // convert dates from UTC to current admin timezone
195:             $periodExpr = $adapter->getDatePartSql(
196:                 $this->getStoreTZOffsetQuery(
197:                     array('source_table' => $sourceTable),
198:                     'source_table.created_at', $from, $to
199:                 )
200:             );
201:             $ifnullBaseShippingCanceled = $adapter->getIfNullSql('order_table.base_shipping_canceled', 0);
202:             $ifnullBaseShippingRefunded = $adapter->getIfNullSql('order_table.base_shipping_refunded', 0);
203:             $columns = array(
204:                 'period'                => $periodExpr,
205:                 'store_id'              => 'order_table.store_id',
206:                 'order_status'          => 'order_table.status',
207:                 'shipping_description'  => 'order_table.shipping_description',
208:                 'orders_count'          => new Zend_Db_Expr('COUNT(order_table.entity_id)'),
209:                 'total_shipping'        => new Zend_Db_Expr('SUM((order_table.base_shipping_amount - '
210:                     . "{$ifnullBaseShippingCanceled}) * order_table.base_to_global_rate)"),
211:                 'total_shipping_actual' => new Zend_Db_Expr('SUM((order_table.base_shipping_invoiced - '
212:                     . "{$ifnullBaseShippingRefunded}) * order_table.base_to_global_rate)"),
213:             );
214: 
215:             $select = $adapter->select();
216:             $select->from(array('source_table' => $sourceTable), $columns)
217:                 ->joinInner(
218:                     array('order_table' => $orderTable),
219:                     $adapter->quoteInto(
220:                         'source_table.order_id = order_table.entity_id AND order_table.state != ?',
221:                         Mage_Sales_Model_Order::STATE_CANCELED),
222:                     array()
223:                 )
224:                 ->useStraightJoin();
225: 
226:             $filterSubSelect = $adapter->select()
227:                 ->from(array('filter_source_table' => $sourceTable), 'MIN(filter_source_table.entity_id)')
228:                 ->where('filter_source_table.order_id = source_table.order_id');
229: 
230:             if ($subSelect !== null) {
231:                 $select->having($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
232:             }
233: 
234:             $select->where('source_table.entity_id = (?)', new Zend_Db_Expr($filterSubSelect));
235:             unset($filterSubSelect);
236: 
237:             $select->group(array(
238:                 $periodExpr,
239:                 'order_table.store_id',
240:                 'order_table.status',
241:                 'order_table.shipping_description'
242:             ));
243: 
244:             $helper        = Mage::getResourceHelper('core');
245:             $insertQuery   = $helper->getInsertFromSelectUsingAnalytic($select, $table, array_keys($columns));
246:             $adapter->query($insertQuery);
247: 
248:             $select->reset();
249: 
250:             $columns = array(
251:                 'period'                => 'period',
252:                 'store_id'              => new Zend_Db_Expr(Mage_Core_Model_App::ADMIN_STORE_ID),
253:                 'order_status'          => 'order_status',
254:                 'shipping_description'  => 'shipping_description',
255:                 'orders_count'          => new Zend_Db_Expr('SUM(orders_count)'),
256:                 'total_shipping'        => new Zend_Db_Expr('SUM(total_shipping)'),
257:                 'total_shipping_actual' => new Zend_Db_Expr('SUM(total_shipping_actual)'),
258:             );
259: 
260:             $select
261:                 ->from($table, $columns)
262:                 ->where('store_id != ?', 0);
263: 
264:             if ($subSelect !== null) {
265:                 $select->where($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
266:             }
267: 
268:             $select->group(array(
269:                 'period',
270:                 'order_status',
271:                 'shipping_description'
272:             ));
273:             $insertQuery = $helper->getInsertFromSelectUsingAnalytic($select, $table, array_keys($columns));
274:             $adapter->query($insertQuery);
275:         } catch (Exception $e) {
276:             $adapter->rollBack();
277:             throw $e;
278:         }
279: 
280:         $adapter->commit();
281:         return $this;
282:     }
283: }
284: