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_Core
 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:  * Resource helper class for MySql Varien DB Adapter
 29:  *
 30:  * @category    Mage
 31:  * @package     Mage_Core
 32:  * @author      Magento Core Team <core@magentocommerce.com>
 33:  */
 34: class Mage_Core_Model_Resource_Helper_Mysql4 extends Mage_Core_Model_Resource_Helper_Abstract
 35: {
 36:     /**
 37:      * Returns expresion for field unification
 38:      *
 39:      * @param string $field
 40:      * @return Zend_Db_Expr
 41:      */
 42:     public function castField($field)
 43:     {
 44:         return $field;
 45:     }
 46:     /**
 47:      * Returns analytic expression for database column
 48:      *
 49:      * @param string $column
 50:      * @param string $groupAliasName OPTIONAL
 51:      * @param string $orderBy OPTIONAL
 52:      * @return Zend_Db_Expr
 53:      */
 54:     public function prepareColumn($column, $groupAliasName = null, $orderBy = null)
 55:     {
 56:         return new Zend_Db_Expr((string)$column);
 57:     }
 58: 
 59:     /**
 60:      * Returns select query with analytic functions
 61:      *
 62:      * @param Varien_Db_Select $select
 63:      * @return string
 64:      */
 65:     public function getQueryUsingAnalyticFunction(Varien_Db_Select $select)
 66:     {
 67:         return $select->assemble();
 68:     }
 69: 
 70:     /**
 71:      *
 72:      * Returns Insert From Select On Duplicate query with analytic functions
 73:      *
 74:      * @param Varien_Db_Select $select
 75:      * @param string $table
 76:      * @param array $table
 77:      * @return string
 78:      */
 79:     public function getInsertFromSelectUsingAnalytic(Varien_Db_Select $select, $table, $fields)
 80:     {
 81:         return $select->insertFromSelect($table, $fields);
 82:     }
 83: 
 84:     /**
 85:      * Correct limitation of queries with UNION
 86:      * No need to do additional actions on MySQL
 87:      *
 88:      * @param Varien_Db_Select $select
 89:      * @return Varien_Db_Select
 90:      */
 91:     public function limitUnion($select)
 92:     {
 93:         return $select;
 94:     }
 95: 
 96:     /**
 97:      * Returns array of quoted orders with direction
 98:      *
 99:      * @param Varien_Db_Select $select
100:      * @param bool $autoReset
101:      * @return array
102:      */
103:     protected function _prepareOrder(Varien_Db_Select $select, $autoReset = false)
104:     {
105:         $selectOrders = $select->getPart(Zend_Db_Select::ORDER);
106:         if (!$selectOrders) {
107:             return array();
108:         }
109: 
110:         $orders = array();
111:         foreach ($selectOrders as $term) {
112:             if (is_array($term)) {
113:                 if (!is_numeric($term[0])) {
114:                     $orders[]   = sprintf('%s %s', $this->_getReadAdapter()->quoteIdentifier($term[0], true), $term[1]);
115:                 }
116:             } else {
117:                 if (!is_numeric($term)) {
118:                     $orders[] = $this->_getReadAdapter()->quoteIdentifier($term, true);
119:                 }
120:             }
121:         }
122: 
123:         if ($autoReset) {
124:             $select->reset(Zend_Db_Select::ORDER);
125:         }
126: 
127:         return $orders;
128:     }
129: 
130:     /**
131:      * Truncate alias name from field.
132:      *
133:      * Result string depends from second optional argument $reverse
134:      * which can be true if you need the first part of the field.
135:      * Field can be with 'dot' delimiter.
136:      *
137:      * @param string $field
138:      * @param bool   $reverse OPTIONAL
139:      * @return string
140:      */
141:     protected function _truncateAliasName($field, $reverse = false)
142:     {
143:         $string = $field;
144:         if (!is_numeric($field) && (strpos($field, '.') !== false)) {
145:             $size  = strpos($field, '.');
146:             if ($reverse) {
147:                 $string = substr($field, 0, $size);
148:             } else {
149:                 $string = substr($field, $size + 1);
150:             }
151:         }
152: 
153:         return $string;
154:     }
155: 
156:     /**
157:      * Returns quoted group by fields
158:      *
159:      * @param Varien_Db_Select $select
160:      * @param bool $autoReset
161:      * @return array
162:      */
163:     protected function _prepareGroup(Varien_Db_Select $select, $autoReset = false)
164:     {
165:         $selectGroups = $select->getPart(Zend_Db_Select::GROUP);
166:         if (!$selectGroups) {
167:             return array();
168:         }
169: 
170:         $groups = array();
171:         foreach ($selectGroups as $term) {
172:             $groups[] = $this->_getReadAdapter()->quoteIdentifier($term, true);
173:         }
174: 
175:         if ($autoReset) {
176:             $select->reset(Zend_Db_Select::GROUP);
177:         }
178: 
179:         return $groups;
180:     }
181: 
182:     /**
183:      * Prepare and returns having array
184:      *
185:      * @param Varien_Db_Select $select
186:      * @param bool $autoReset
187:      * @return array
188:      * @throws Zend_Db_Exception
189:      */
190:     protected function _prepareHaving(Varien_Db_Select $select, $autoReset = false)
191:     {
192:         $selectHavings = $select->getPart(Zend_Db_Select::HAVING);
193:         if (!$selectHavings) {
194:             return array();
195:         }
196: 
197:         $havings = array();
198:         $columns = $select->getPart(Zend_Db_Select::COLUMNS);
199:         foreach ($columns as $columnEntry) {
200:             $correlationName = (string)$columnEntry[1];
201:             $column          = $columnEntry[2];
202:             foreach ($selectHavings as $having) {
203:                 /**
204:                  * Looking for column expression in the having clause
205:                  */
206:                 if (strpos($having, $correlationName) !== false) {
207:                     if (is_string($column)) {
208:                         /**
209:                          * Replace column expression to column alias in having clause
210:                          */
211:                         $havings[] = str_replace($correlationName, $column, $having);
212:                     } else {
213:                         throw new Zend_Db_Exception(sprintf("Can't prepare expression without column alias: '%s'", $correlationName));
214:                     }
215:                 }
216:             }
217:         }
218: 
219:         if ($autoReset) {
220:             $select->reset(Zend_Db_Select::HAVING);
221:         }
222: 
223:         return $havings;
224:     }
225: 
226:     /**
227:      *
228:      * @param string $query
229:      * @param int $limitCount
230:      * @param int $limitOffset
231:      * @param array $columnList
232:      * @return string
233:      */
234:     protected function _assembleLimit($query, $limitCount, $limitOffset, $columnList = array())
235:     {
236:         if ($limitCount !== null) {
237:               $limitCount = intval($limitCount);
238:             if ($limitCount <= 0) {
239: //                throw new Exception("LIMIT argument count={$limitCount} is not valid");
240:             }
241: 
242:             $limitOffset = intval($limitOffset);
243:             if ($limitOffset < 0) {
244: //                throw new Exception("LIMIT argument offset={$limitOffset} is not valid");
245:             }
246: 
247:             if ($limitOffset + $limitCount != $limitOffset + 1) {
248:                 $columns = array();
249:                 foreach ($columnList as $columnEntry) {
250:                     $columns[] = $columnEntry[2] ? $columnEntry[2] : $columnEntry[1];
251:                 }
252: 
253:                 $query = sprintf('%s LIMIT %s, %s', $query, $limitCount, $limitOffset);
254:             }
255:         }
256: 
257:         return $query;
258:     }
259: 
260:     /**
261:      * Prepare select column list
262:      *
263:      * @param Varien_Db_Select $select
264:      * @param $groupByCondition OPTIONAL
265:      * @return array
266:      * @throws Zend_Db_Exception
267:      */
268:     public function prepareColumnsList(Varien_Db_Select $select, $groupByCondition = null)
269:     {
270:         if (!count($select->getPart(Zend_Db_Select::FROM))) {
271:             return $select->getPart(Zend_Db_Select::COLUMNS);
272:         }
273: 
274:         $columns          = $select->getPart(Zend_Db_Select::COLUMNS);
275:         $tables           = $select->getPart(Zend_Db_Select::FROM);
276:         $preparedColumns  = array();
277: 
278:         foreach ($columns as $columnEntry) {
279:             list($correlationName, $column, $alias) = $columnEntry;
280:             if ($column instanceof Zend_Db_Expr) {
281:                 if ($alias !== null) {
282:                     if (preg_match('/(^|[^a-zA-Z_])^(SELECT)?(SUM|MIN|MAX|AVG|COUNT)\s*\(/i', $column, $matches)) {
283:                         $column = $this->prepareColumn($column, $groupByCondition);
284:                     }
285:                     $preparedColumns[strtoupper($alias)] = array(null, $column, $alias);
286:                 } else {
287:                     throw new Zend_Db_Exception("Can't prepare expression without alias");
288:                 }
289:             } else {
290:                 if ($column == Zend_Db_Select::SQL_WILDCARD) {
291:                     if ($tables[$correlationName]['tableName'] instanceof Zend_Db_Expr) {
292:                         throw new Zend_Db_Exception("Can't prepare expression when tableName is instance of Zend_Db_Expr");
293:                     }
294:                     $tableColumns = $this->_getReadAdapter()->describeTable($tables[$correlationName]['tableName']);
295:                     foreach(array_keys($tableColumns) as $col) {
296:                         $preparedColumns[strtoupper($col)] = array($correlationName, $col, null);
297:                     }
298:                 } else {
299:                     $columnKey = is_null($alias) ? $column : $alias;
300:                     $preparedColumns[strtoupper($columnKey)] = array($correlationName, $column, $alias);
301:                 }
302:             }
303:         }
304: 
305: //        $select->reset(Zend_Db_Select::COLUMNS);
306: //        $select->setPart(Zend_Db_Select::COLUMNS, array_values($preparedColumns));
307: 
308:         return $preparedColumns;
309:     }
310: 
311:     /**
312:      * Add prepared column group_concat expression
313:      *
314:      * @param Varien_Db_Select $select
315:      * @param string $fieldAlias Field alias which will be added with column group_concat expression
316:      * @param string $fields
317:      * @param string $groupConcatDelimiter
318:      * @param string $fieldsDelimiter
319:      * @param string $additionalWhere
320:      * @return Varien_Db_Select
321:      */
322:     public function addGroupConcatColumn($select, $fieldAlias, $fields, $groupConcatDelimiter = ',', $fieldsDelimiter = '', $additionalWhere = '')
323:     {
324:         if (is_array($fields)) {
325:             $fieldExpr = $this->_getReadAdapter()->getConcatSql($fields, $fieldsDelimiter);
326:         } else {
327:             $fieldExpr = $fields;
328:         }
329:         if ($additionalWhere) {
330:             $fieldExpr = $this->_getReadAdapter()->getCheckSql($additionalWhere, $fieldExpr, "''");
331:         }
332:         $separator = '';
333:         if ($groupConcatDelimiter) {
334:             $separator = sprintf(" SEPARATOR '%s'",  $groupConcatDelimiter);
335:         }
336: 
337:         $select->columns(array($fieldAlias => new Zend_Db_Expr(sprintf('GROUP_CONCAT(%s%s)', $fieldExpr, $separator))));
338: 
339:         return $select;
340:     }
341: 
342:     /**
343:      * Returns expression of days passed from $startDate to $endDate
344:      *
345:      * @param  string|Zend_Db_Expr $startDate
346:      * @param  string|Zend_Db_Expr $endDate
347:      * @return Zend_Db_Expr
348:      */
349:     public function getDateDiff($startDate, $endDate)
350:     {
351:         $dateDiff = '(TO_DAYS(' . $endDate . ') - TO_DAYS(' . $startDate . '))';
352:         return new Zend_Db_Expr($dateDiff);
353:     }
354: 
355:     /**
356:      * Escapes and quotes LIKE value.
357:      * Stating escape symbol in expression is not required, because we use standard MySQL escape symbol.
358:      * For options and escaping see escapeLikeValue().
359:      *
360:      * @param string $value
361:      * @param array $options
362:      * @return Zend_Db_Expr
363:      *
364:      * @see escapeLikeValue()
365:      */
366:     public function addLikeEscape($value, $options = array())
367:     {
368:         $value = $this->escapeLikeValue($value, $options);
369:         return new Zend_Db_Expr($this->_getReadAdapter()->quote($value));
370:     }
371: }
372: