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:  * Invoice 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_Invoiced 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 Invoiced data
 48:      *
 49:      * @param mixed $from
 50:      * @param mixed $to
 51:      * @return Mage_Sales_Model_Resource_Report_Invoiced
 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->_aggregateByInvoiceCreatedAt($from, $to);
 62: 
 63:         $this->_setFlagData(Mage_Reports_Model_Flag::REPORT_INVOICE_FLAG_CODE);
 64:         return $this;
 65:     }
 66: 
 67:     /**
 68:      * Aggregate Invoiced data by invoice created_at as period
 69:      *
 70:      * @param mixed $from
 71:      * @param mixed $to
 72:      * @return Mage_Sales_Model_Resource_Report_Invoiced
 73:      */
 74:     protected function _aggregateByInvoiceCreatedAt($from, $to)
 75:     {
 76:         $table       = $this->getTable('sales/invoiced_aggregated');
 77:         $sourceTable = $this->getTable('sales/invoice');
 78:         $orderTable  = $this->getTable('sales/order');
 79:         $helper      = Mage::getResourceHelper('core');
 80:         $adapter     = $this->_getWriteAdapter();
 81: 
 82:         $adapter->beginTransaction();
 83: 
 84:         try {
 85:             if ($from !== null || $to !== null) {
 86:                 $subSelect = $this->_getTableDateRangeRelatedSelect(
 87:                     $sourceTable, $orderTable, array('order_id'=>'entity_id'),
 88:                     'created_at', 'updated_at', $from, $to
 89:                 );
 90:             } else {
 91:                 $subSelect = null;
 92:             }
 93: 
 94:             $this->_clearTableByDateRange($table, $from, $to, $subSelect);
 95:             // convert dates from UTC to current admin timezone
 96:             $periodExpr = $adapter->getDatePartSql(
 97:                 $this->getStoreTZOffsetQuery(
 98:                     array('source_table' => $sourceTable),
 99:                     'source_table.created_at', $from, $to
100:                 )
101:             );
102:             $columns = array(
103:                 // convert dates from UTC to current admin timezone
104:                 'period'                => $periodExpr,
105:                 'store_id'              => 'order_table.store_id',
106:                 'order_status'          => 'order_table.status',
107:                 'orders_count'          => new Zend_Db_Expr('COUNT(order_table.entity_id)'),
108:                 'orders_invoiced'       => new Zend_Db_Expr('COUNT(order_table.entity_id)'),
109:                 'invoiced'              => new Zend_Db_Expr('SUM(order_table.base_total_invoiced'
110:                     . ' * order_table.base_to_global_rate)'),
111:                 'invoiced_captured'     => new Zend_Db_Expr('SUM(order_table.base_total_paid'
112:                     . ' * order_table.base_to_global_rate)'),
113:                 'invoiced_not_captured' => new Zend_Db_Expr(
114:                     'SUM((order_table.base_total_invoiced - order_table.base_total_paid)'
115:                     . ' * order_table.base_to_global_rate)')
116:             );
117: 
118:             $select = $adapter->select();
119:             $select->from(array('source_table' => $sourceTable), $columns)
120:                 ->joinInner(
121:                     array('order_table' => $orderTable),
122:                     $adapter->quoteInto(
123:                         'source_table.order_id = order_table.entity_id AND order_table.state <> ?',
124:                         Mage_Sales_Model_Order::STATE_CANCELED),
125:                     array()
126:                 );
127: 
128:             $filterSubSelect = $adapter->select();
129:             $filterSubSelect->from(array('filter_source_table' => $sourceTable), 'MAX(filter_source_table.entity_id)')
130:                 ->where('filter_source_table.order_id = source_table.order_id');
131: 
132:             if ($subSelect !== null) {
133:                 $select->having($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
134:             }
135: 
136:             $select->where('source_table.entity_id = (?)', new Zend_Db_Expr($filterSubSelect));
137:             unset($filterSubSelect);
138: 
139:             $select->group(array(
140:                 $periodExpr,
141:                 'order_table.store_id',
142:                 'order_table.status'
143:             ));
144: 
145:             $select->having('orders_count > 0');
146:             $insertQuery = $helper->getInsertFromSelectUsingAnalytic($select, $table, array_keys($columns));
147:             $adapter->query($insertQuery);
148:             $select->reset();
149: 
150:             $columns = array(
151:                 'period'                => 'period',
152:                 'store_id'              => new Zend_Db_Expr(Mage_Core_Model_App::ADMIN_STORE_ID),
153:                 'order_status'          => 'order_status',
154:                 'orders_count'          => new Zend_Db_Expr('SUM(orders_count)'),
155:                 'orders_invoiced'       => new Zend_Db_Expr('SUM(orders_invoiced)'),
156:                 'invoiced'              => new Zend_Db_Expr('SUM(invoiced)'),
157:                 'invoiced_captured'     => new Zend_Db_Expr('SUM(invoiced_captured)'),
158:                 'invoiced_not_captured' => new Zend_Db_Expr('SUM(invoiced_not_captured)')
159:             );
160: 
161:             $select
162:                 ->from($table, $columns)
163:                 ->where('store_id <> ?', 0);
164: 
165:             if ($subSelect !== null) {
166:                 $select->where($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
167:             }
168: 
169:             $select->group(array(
170:                 'period',
171:                 'order_status'
172:             ));
173: 
174:             $insertQuery = $helper->getInsertFromSelectUsingAnalytic($select, $table, array_keys($columns));
175:             $adapter->query($insertQuery);
176:             $adapter->commit();
177:         } catch (Exception $e) {
178:             $adapter->rollBack();
179:             throw $e;
180:         }
181: 
182:         return $this;
183:     }
184: 
185:     /**
186:      * Aggregate Invoiced data by order created_at as period
187:      *
188:      * @param mixed $from
189:      * @param mixed $to
190:      * @return Mage_Sales_Model_Resource_Report_Invoiced
191:      */
192:     protected function _aggregateByOrderCreatedAt($from, $to)
193:     {
194:         $table       = $this->getTable('sales/invoiced_aggregated_order');
195:         $sourceTable = $this->getTable('sales/order');
196:         $adapter     = $this->_getWriteAdapter();
197: 
198: 
199:             if ($from !== null || $to !== null) {
200:                 $subSelect = $this->_getTableDateRangeSelect($sourceTable, 'created_at', 'updated_at', $from, $to);
201:             } else {
202:                 $subSelect = null;
203:             }
204: 
205:             $this->_clearTableByDateRange($table, $from, $to, $subSelect);
206:             // convert dates from UTC to current admin timezone
207:             $periodExpr = $adapter->getDatePartSql(
208:                 $this->getStoreTZOffsetQuery(
209:                     $sourceTable, 'created_at', $from, $to
210:                 )
211:             );
212: 
213:             $columns = array(
214:                 'period'                => $periodExpr,
215:                 'store_id'              => 'store_id',
216:                 'order_status'          => 'status',
217:                 'orders_count'          => new Zend_Db_Expr('COUNT(base_total_invoiced)'),
218:                 'orders_invoiced'       => new Zend_Db_Expr(
219:                     sprintf('SUM(%s)',
220:                         $adapter->getCheckSql('base_total_invoiced > 0', 1, 0)
221:                     )
222:                 ),
223:                 'invoiced'              => new Zend_Db_Expr(
224:                     sprintf('SUM(%s * %s)',
225:                         $adapter->getIfNullSql('base_total_invoiced',0),
226:                         $adapter->getIfNullSql('base_to_global_rate',0)
227:                     )
228:                 ),
229:                 'invoiced_captured'     => new Zend_Db_Expr(
230:                     sprintf('SUM(%s * %s)',
231:                         $adapter->getIfNullSql('base_total_paid',0),
232:                         $adapter->getIfNullSql('base_to_global_rate',0)
233:                     )
234:                 ),
235:                 'invoiced_not_captured' => new Zend_Db_Expr(
236:                     sprintf('SUM((%s - %s) * %s)',
237:                         $adapter->getIfNullSql('base_total_invoiced',0),
238:                         $adapter->getIfNullSql('base_total_paid',0),
239:                         $adapter->getIfNullSql('base_to_global_rate',0)
240:                     )
241:                 )
242: 
243:             );
244: 
245:             $select = $adapter->select();
246:             $select->from($sourceTable, $columns)
247:                 ->where('state <> ?', Mage_Sales_Model_Order::STATE_CANCELED);
248: 
249:             if ($subSelect !== null) {
250:                 $select->having($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
251:             }
252: 
253:             $select->group(array(
254:                 $periodExpr,
255:                 'store_id',
256:                 'status'
257:             ));
258: 
259:             $select->having('orders_count > 0');
260: 
261:             $helper      = Mage::getResourceHelper('core');
262:             $insertQuery = $helper->getInsertFromSelectUsingAnalytic($select, $table, array_keys($columns));
263:             $adapter->query($insertQuery);
264:             $select->reset();
265: 
266:             $columns = array(
267:                 'period'                => 'period',
268:                 'store_id'              => new Zend_Db_Expr(Mage_Core_Model_App::ADMIN_STORE_ID),
269:                 'order_status'          => 'order_status',
270:                 'orders_count'          => new Zend_Db_Expr('SUM(orders_count)'),
271:                 'orders_invoiced'       => new Zend_Db_Expr('SUM(orders_invoiced)'),
272:                 'invoiced'              => new Zend_Db_Expr('SUM(invoiced)'),
273:                 'invoiced_captured'     => new Zend_Db_Expr('SUM(invoiced_captured)'),
274:                 'invoiced_not_captured' => new Zend_Db_Expr('SUM(invoiced_not_captured)')
275:             );
276: 
277:             $select->from($table, $columns)
278:                 ->where('store_id <> ?', 0);
279: 
280:             if ($subSelect !== null) {
281:                 $select->where($this->_makeConditionFromDateRangeSelect($subSelect, 'period'));
282:             }
283: 
284:             $select->group(array(
285:                 'period',
286:                 'order_status'
287:             ));
288: 
289:             $helper      = Mage::getResourceHelper('core');
290:             $insertQuery = $helper->getInsertFromSelectUsingAnalytic($select, $table, array_keys($columns));
291:             $adapter->query($insertQuery);
292: 
293: 
294:         return $this;
295:     }
296: }
297: