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:  * Abstract report aggregate resource model
 30:  *
 31:  * @category    Mage
 32:  * @package     Mage_Reports
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: abstract class Mage_Reports_Model_Resource_Report_Abstract extends Mage_Core_Model_Resource_Db_Abstract
 36: {
 37:     /**
 38:      * Flag object
 39:      *
 40:      * @var Mage_Reports_Model_Flag
 41:      */
 42:     protected $_flag     = null;
 43: 
 44:     /**
 45:      * Retrive flag object
 46:      *
 47:      * @return Mage_Reports_Model_Flag
 48:      */
 49:     protected function _getFlag()
 50:     {
 51:         if ($this->_flag === null) {
 52:             $this->_flag = Mage::getModel('reports/flag');
 53:         }
 54:         return $this->_flag;
 55:     }
 56: 
 57:     /**
 58:      * Saves flag
 59:      *
 60:      * @param string $code
 61:      * @param mixed $value
 62:      * @return Mage_Reports_Model_Resource_Report_Abstract
 63:      */
 64:     protected function _setFlagData($code, $value = null)
 65:     {
 66:         $this->_getFlag()
 67:             ->setReportFlagCode($code)
 68:             ->unsetData()
 69:             ->loadSelf();
 70: 
 71:         if ($value !== null) {
 72:             $this->_getFlag()->setFlagData($value);
 73:         }
 74: 
 75:         $time = Varien_Date::toTimestamp(true);
 76:         // touch last_update
 77:         $this->_getFlag()->setLastUpdate($this->formatDate($time));
 78: 
 79:         $this->_getFlag()->save();
 80: 
 81:         return $this;
 82:     }
 83: 
 84:     /**
 85:      * Retrieve flag data
 86:      *
 87:      * @param string $code
 88:      * @return mixed
 89:      */
 90:     protected function _getFlagData($code)
 91:     {
 92:         $this->_getFlag()
 93:             ->setReportFlagCode($code)
 94:             ->unsetData()
 95:             ->loadSelf();
 96: 
 97:         return $this->_getFlag()->getFlagData();
 98:     }
 99: 
100:     /**
101:      * Trancate table
102:      *
103:      * @param string $table
104:      * @return Mage_Reports_Model_Resource_Report_Abstract
105:      */
106:     protected function _truncateTable($table)
107:     {
108:         if ($this->_getWriteAdapter()->getTransactionLevel() > 0) {
109:             $this->_getWriteAdapter()->delete($table);
110:         } else {
111:             $this->_getWriteAdapter()->truncateTable($table);
112:         }
113:         return $this;
114:     }
115: 
116:     /**
117:      * Clear report table by specified date range.
118:      * If specified source table parameters,
119:      * condition will be generated by source table subselect.
120:      *
121:      * @param string $table
122:      * @param string|null $from
123:      * @param string|null $to
124:      * @param Zend_Db_Select|string|null $subSelect
125:      * @param unknown_type $doNotUseTruncate
126:      * @return Mage_Reports_Model_Resource_Report_Abstract
127:      */
128:     protected function _clearTableByDateRange($table, $from = null, $to = null, $subSelect = null,
129:         $doNotUseTruncate = false)
130:     {
131:         if ($from === null && $to === null && !$doNotUseTruncate) {
132:             $this->_truncateTable($table);
133:             return $this;
134:         }
135: 
136:         if ($subSelect !== null) {
137:             $deleteCondition = $this->_makeConditionFromDateRangeSelect($subSelect, 'period');
138:         } else {
139:             $condition = array();
140:             if ($from !== null) {
141:                 $condition[] = $this->_getWriteAdapter()->quoteInto('period >= ?', $from);
142:             }
143: 
144:             if ($to !== null) {
145:                 $condition[] = $this->_getWriteAdapter()->quoteInto('period <= ?', $to);
146:             }
147:             $deleteCondition = implode(' AND ', $condition);
148:         }
149:         $this->_getWriteAdapter()->delete($table, $deleteCondition);
150:         return $this;
151:     }
152: 
153:     /**
154:      * Generate table date range select
155:      *
156:      * @param string $table
157:      * @param string $column
158:      * @param string $whereColumn
159:      * @param string|null $from
160:      * @param string|null $to
161:      * @param array $additionalWhere
162:      * @param unknown_type $alias
163:      * @return Varien_Db_Select
164:      */
165:     protected function _getTableDateRangeSelect($table, $column, $whereColumn, $from = null, $to = null,
166:         $additionalWhere = array(), $alias = 'date_range_table')
167:     {
168:         $adapter = $this->_getReadAdapter();
169:         $select  = $adapter->select()
170:             ->from(
171:                 array($alias => $table),
172:                 $adapter->getDatePartSql(
173:                     $this->getStoreTZOffsetQuery(array($alias => $table), $alias . '.' . $column, $from, $to)
174:                 )
175:             )
176:             ->distinct(true);
177: 
178:         if ($from !== null) {
179:            $select->where($alias . '.' . $whereColumn . ' >= ?', $from);
180:         }
181: 
182:         if ($to !== null) {
183:            $select->where($alias . '.' . $whereColumn . ' <= ?', $to);
184:         }
185: 
186:         if (!empty($additionalWhere)) {
187:             foreach ($additionalWhere as $condition) {
188:                 if (is_array($condition) && count($condition) == 2) {
189:                    $condition = $adapter->quoteInto($condition[0], $condition[1]);
190:                 } elseif (is_array($condition)) { // Invalid condition
191:                    continue;
192:                 }
193:                 $condition = str_replace('{{table}}', $adapter->quoteIdentifier($alias), $condition);
194:                 $select->where($condition);
195:             }
196:         }
197: 
198:         return $select;
199:     }
200: 
201:     /**
202:      * Make condition for using in where section
203:      * from select statement with single date column
204:      *
205:      * @result string|false
206:      *
207:      * @param Varien_Db_Select $select
208:      * @param string $periodColumn
209:      * @return unknown
210:      */
211:     protected function _makeConditionFromDateRangeSelect($select, $periodColumn)
212:     {
213:         static $selectResultCache = array();
214:         $cacheKey = (string)$select;
215: 
216:         if (!array_key_exists($cacheKey, $selectResultCache)) {
217:             try {
218:                 $selectResult = array();
219:                 $query = $this->_getReadAdapter()->query($select);
220:                 while ($date = $query->fetchColumn()) {
221:                     $selectResult[] = $date;
222:                 }
223:             } catch (Exception $e) {
224:                 $selectResult = false;
225:             }
226:             $selectResultCache[$cacheKey] = $selectResult;
227:         } else {
228:             $selectResult = $selectResultCache[$cacheKey];
229:         }
230:         if ($selectResult === false) {
231:             return false;
232:         }
233: 
234:         $whereCondition = array();
235:         $adapter = $this->_getReadAdapter();
236:         foreach ($selectResult as $date) {
237:             $date = substr($date, 0, 10); // to fix differences in oracle
238:             $whereCondition[] = $adapter->prepareSqlCondition($periodColumn, array('like' => $date));
239:         }
240:         $whereCondition = implode(' OR ', $whereCondition);
241:         if ($whereCondition == '') {
242:             $whereCondition = '1=0';  // FALSE condition!
243:         }
244: 
245:         return $whereCondition;
246:     }
247: 
248:     /**
249:      * Generate table date range select
250:      *
251:      * @param string $table
252:      * @param string $relatedTable
253:      * @param array $joinCondition
254:      * @param string $column
255:      * @param string $whereColumn
256:      * @param string|null $from
257:      * @param string|null $to
258:      * @param array $additionalWhere
259:      * @param unknown_type $alias
260:      * @param unknown_type $relatedAlias
261:      * @return Varien_Db_Select
262:      */
263:     protected function _getTableDateRangeRelatedSelect($table, $relatedTable, $joinCondition, $column, $whereColumn,
264:         $from = null, $to = null, $additionalWhere = array(), $alias = 'date_range_table',
265:         $relatedAlias = 'related_date_range_table')
266:     {
267:         $adapter = $this->_getReadAdapter();
268:         $joinConditionSql = array();
269: 
270:         foreach ($joinCondition as $fkField => $pkField) {
271:             $joinConditionSql[] = sprintf('%s.%s = %s.%s', $alias, $fkField, $relatedAlias, $pkField);
272:         }
273: 
274:         $select = $adapter->select()
275:             ->from(
276:                 array($alias => $table),
277:                 $adapter->getDatePartSql(
278:                     $adapter->quoteIdentifier($alias . '.' . $column)
279:                 )
280:             )
281:             ->joinInner(
282:                 array($relatedAlias => $relatedTable),
283:                 implode(' AND ', $joinConditionSql),
284:                 array()
285:             )
286:             ->distinct(true);
287: 
288:         if ($from !== null) {
289:            $select->where($relatedAlias . '.' . $whereColumn . ' >= ?', $from);
290:         }
291: 
292:         if ($to !== null) {
293:            $select->where($relatedAlias . '.' . $whereColumn . ' <= ?', $to);
294:         }
295: 
296:         if (!empty($additionalWhere)) {
297:             foreach ($additionalWhere as $condition) {
298:                 if (is_array($condition) && count($condition) == 2) {
299:                    $condition = $adapter->quoteInto($condition[0], $condition[1]);
300:                 } elseif (is_array($condition)) { // Invalid condition
301:                    continue;
302:                 }
303:                 $condition = str_replace(
304:                     array('{{table}}', '{{related_table}}'),
305:                     array(
306:                         $adapter->quoteIdentifier($alias),
307:                         $adapter->quoteIdentifier($relatedAlias)
308:                     ),
309:                     $condition
310:                 );
311:                 $select->where($condition);
312:             }
313:         }
314: 
315:         return $select;
316:     }
317: 
318:     /**
319:      * Check range dates and transforms it to strings
320:      *
321:      * @param mixed $from
322:      * @param mixed $to
323:      * @return Mage_Reports_Model_Resource_Report_Abstract
324:      */
325:     protected function _checkDates(&$from, &$to)
326:     {
327:         if ($from !== null) {
328:             $from = $this->formatDate($from);
329:         }
330: 
331:         if ($to !== null) {
332:             $to = $this->formatDate($to);
333:         }
334: 
335:         return $this;
336:     }
337: 
338:     /**
339:     * Retrieve query for attribute with timezone conversion
340:     *
341:     * @param string|array $table
342:     * @param string $column
343:     * @param mixed $from
344:     * @param mixed $to
345:     * @param int|string|Mage_Core_Model_Store|null $store
346:     * @return string
347:     */
348:     public function getStoreTZOffsetQuery($table, $column, $from = null, $to = null, $store = null)
349:     {
350:         $column = $this->_getWriteAdapter()->quoteIdentifier($column);
351: 
352:         if (is_null($from)) {
353:             $selectOldest = $this->_getWriteAdapter()->select()
354:                 ->from(
355:                     $table,
356:                     array("MIN($column)")
357:                 );
358:             $from = $this->_getWriteAdapter()->fetchOne($selectOldest);
359:         }
360: 
361:         $periods = $this->_getTZOffsetTransitions(
362:             Mage::app()->getLocale()->storeDate($store)->toString(Zend_Date::TIMEZONE_NAME), $from, $to
363:         );
364:         if (empty($periods)) {
365:             return $column;
366:         }
367: 
368:         $query = "";
369:         $periodsCount = count($periods);
370: 
371:         $i = 0;
372:         foreach ($periods as $offset => $timestamps) {
373:             $subParts = array();
374:             foreach ($timestamps as $ts) {
375:                 $subParts[] = "($column between {$ts['from']} and {$ts['to']})";
376:             }
377: 
378:             $then = $this->_getWriteAdapter()
379:                 ->getDateAddSql($column, $offset, Varien_Db_Adapter_Interface::INTERVAL_SECOND);
380: 
381:             $query .= (++$i == $periodsCount) ? $then : "CASE WHEN " . join(" OR ", $subParts) . " THEN $then ELSE ";
382:         }
383: 
384:         return $query . str_repeat('END ', count($periods) - 1);
385:     }
386: 
387:     /**
388:      * Retrieve transitions for offsets of given timezone
389:      *
390:      * @param string $timezone
391:      * @param mixed $from
392:      * @param mixed $to
393:      * @return array
394:      */
395:     protected function _getTZOffsetTransitions($timezone, $from = null, $to = null)
396:     {
397:         $tzTransitions = array();
398:         try {
399:             if (!empty($from)) {
400:                 $from = new Zend_Date($from, Varien_Date::DATETIME_INTERNAL_FORMAT);
401:                 $from = $from->getTimestamp();
402:             }
403: 
404:             $to = new Zend_Date($to, Varien_Date::DATETIME_INTERNAL_FORMAT);
405:             $nextPeriod = $this->_getWriteAdapter()->formatDate($to->toString(Varien_Date::DATETIME_INTERNAL_FORMAT));
406:             $to = $to->getTimestamp();
407: 
408:             $dtz = new DateTimeZone($timezone);
409:             $transitions = $dtz->getTransitions();
410:             $dateTimeObject = new Zend_Date('c');
411: 
412:             for ($i = count($transitions) - 1; $i >= 0; $i--) {
413:                 $tr = $transitions[$i];
414:                 if ($tr['ts'] > $to) {
415:                     continue;
416:                 }
417: 
418:                 $dateTimeObject->set($tr['time']);
419:                 $tr['time'] = $this->_getWriteAdapter()
420:                     ->formatDate($dateTimeObject->toString(Varien_Date::DATETIME_INTERNAL_FORMAT));
421:                 $tzTransitions[$tr['offset']][] = array('from' => $tr['time'], 'to' => $nextPeriod);
422: 
423:                 if (!empty($from) && $tr['ts'] < $from) {
424:                     break;
425:                 }
426:                 $nextPeriod = $tr['time'];
427:             }
428:         } catch (Exception $e) {
429:             Mage::logException($e);
430:         }
431: 
432:         return $tzTransitions;
433:     }
434: 
435: 
436:     /**
437:      * Retrieve store timezone offset from UTC in the form acceptable by SQL's CONVERT_TZ()
438:      *
439:      * @param unknown_type $store
440:      * @return string
441:      */
442:     protected function _getStoreTimezoneUtcOffset($store = null)
443:     {
444:         return Mage::app()->getLocale()->storeDate($store)->toString(Zend_Date::GMT_DIFF_SEP);
445:     }
446: 
447:     /**
448:      * Retrieve date in UTC timezone
449:      *
450:      * @param unknown_type $date
451:      * @return Zend_Date|null
452:      */
453:     protected function _dateToUtc($date)
454:     {
455:         if ($date === null) {
456:             return null;
457:         }
458:         $dateUtc = new Zend_Date($date);
459:         $dateUtc->setTimezone('Etc/UTC');
460:         return $dateUtc;
461:     }
462: }
463: