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_Reports
 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:  * Reports Mysql resource helper model
 30:  *
 31:  * @category    Mage
 32:  * @package     Mage_Reports
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: class Mage_Reports_Model_Resource_Helper_Mysql4 extends Mage_Core_Model_Resource_Helper_Mysql4
 36:     implements Mage_Reports_Model_Resource_Helper_Interface
 37: {
 38: 
 39:     /**
 40:      * Merge Index data
 41:      *
 42:      * @param string $mainTable
 43:      * @param array $data
 44:      * @return string
 45:      */
 46:     public function mergeVisitorProductIndex($mainTable, $data, $matchFields)
 47:     {
 48:         $result = $this->_getWriteAdapter()->insertOnDuplicate($mainTable, $data, array_keys($data));
 49:         return $result;
 50:     }
 51: 
 52:     /**
 53:      * Update rating position
 54:      *
 55:      * @param string $type day|month|year
 56:      * @param string $column
 57:      * @param string $mainTable
 58:      * @param string $aggregationTable
 59:      * @return Mage_Core_Model_Resource_Helper_Mysql4
 60:      */
 61:     public function updateReportRatingPos($type, $column, $mainTable, $aggregationTable) {
 62:         $adapter         = $this->_getWriteAdapter();
 63:         $periodSubSelect = $adapter->select();
 64:         $ratingSubSelect = $adapter->select();
 65:         $ratingSelect    = $adapter->select();
 66: 
 67:         switch ($type) {
 68:             case 'year':
 69:                 $periodCol = $adapter->getDateFormatSql('t.period', '%Y-01-01');
 70:                 break;
 71:             case 'month':
 72:                 $periodCol = $adapter->getDateFormatSql('t.period', '%Y-%m-01');
 73:                 break;
 74:             default:
 75:                 $periodCol = 't.period';
 76:                 break;
 77:         }
 78: 
 79:         $columns = array(
 80:             'period'        => 't.period',
 81:             'store_id'      => 't.store_id',
 82:             'product_id'    => 't.product_id',
 83:             'product_name'  => 't.product_name',
 84:             'product_price' => 't.product_price',
 85:         );
 86: 
 87:         if ($type == 'day') {
 88:             $columns['id'] = 't.id';  // to speed-up insert on duplicate key update
 89:         }
 90: 
 91:         $cols = array_keys($columns);
 92:         $cols['total_qty'] = new Zend_Db_Expr('SUM(t.' . $column . ')');
 93:         $periodSubSelect->from(array('t' => $mainTable), $cols)
 94:             ->group(array('t.store_id', $periodCol, 't.product_id'))
 95:             ->order(array('t.store_id', $periodCol, 'total_qty DESC'));
 96: 
 97:         $cols = $columns;
 98:         $cols[$column] = 't.total_qty';
 99:         $cols['rating_pos']  = new Zend_Db_Expr(
100:             "(@pos := IF(t.`store_id` <> @prevStoreId OR {$periodCol} <> @prevPeriod, 1, @pos+1))");
101:         $cols['prevStoreId'] = new Zend_Db_Expr('(@prevStoreId := t.`store_id`)');
102:         $cols['prevPeriod']  = new Zend_Db_Expr("(@prevPeriod := {$periodCol})");
103:         $ratingSubSelect->from($periodSubSelect, $cols);
104: 
105:         $cols               = $columns;
106:         $cols['period']     = $periodCol;
107:         $cols[$column]      = 't.' . $column;
108:         $cols['rating_pos'] = 't.rating_pos';
109:         $ratingSelect->from($ratingSubSelect, $cols);
110: 
111:         $sql = $ratingSelect->insertFromSelect($aggregationTable, array_keys($cols));
112:         $adapter->query("SET @pos = 0, @prevStoreId = -1, @prevPeriod = '0000-00-00'");
113: 
114:         $adapter->query($sql);
115: 
116:         return $this;
117:     }
118: 
119: }
120: