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