1<?php
2/**
3 * CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
4 * Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
5 *
6 * Licensed under The MIT License
7 * For full copyright and license information, please see the LICENSE.txt
8 * Redistributions of files must retain the above copyright notice.
9 *
10 * @copyright     Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
11 * @link          https://cakephp.org CakePHP(tm) Project
12 * @since         3.0.0
13 * @license       https://opensource.org/licenses/mit-license.php MIT License
14 */
15namespace Cake\Database;
16
17use Cake\Database\Expression\FunctionExpression;
18
19/**
20 * Contains methods related to generating FunctionExpression objects
21 * with most commonly used SQL functions.
22 * This acts as a factory for FunctionExpression objects.
23 */
24class FunctionsBuilder
25{
26    /**
27     * Returns a new instance of a FunctionExpression. This is used for generating
28     * arbitrary function calls in the final SQL string.
29     *
30     * @param string $name the name of the SQL function to constructed
31     * @param array $params list of params to be passed to the function
32     * @param array $types list of types for each function param
33     * @param string $return The return type of the function expression
34     * @return \Cake\Database\Expression\FunctionExpression
35     */
36    protected function _build($name, $params = [], $types = [], $return = 'string')
37    {
38        return new FunctionExpression($name, $params, $types, $return);
39    }
40
41    /**
42     * Helper function to build a function expression that only takes one literal
43     * argument.
44     *
45     * @param string $name name of the function to build
46     * @param string|\Cake\Database\ExpressionInterface $expression the function argument
47     * @param array $types list of types to bind to the arguments
48     * @param string $return The return type for the function
49     * @return \Cake\Database\Expression\FunctionExpression
50     */
51    protected function _literalArgumentFunction($name, $expression, $types = [], $return = 'string')
52    {
53        if (!is_string($expression)) {
54            $expression = [$expression];
55        } else {
56            $expression = [$expression => 'literal'];
57        }
58
59        return $this->_build($name, $expression, $types, $return);
60    }
61
62    /**
63     * Returns a FunctionExpression representing a call to SQL RAND function.
64     *
65     * @return \Cake\Database\Expression\FunctionExpression
66     */
67    public function rand()
68    {
69        return $this->_build('RAND', [], [], 'float');
70    }
71
72    /**
73     * Returns a FunctionExpression representing a call to SQL SUM function.
74     *
75     * @param string|\Cake\Database\ExpressionInterface $expression the function argument
76     * @param array $types list of types to bind to the arguments
77     * @return \Cake\Database\Expression\FunctionExpression
78     */
79    public function sum($expression, $types = [])
80    {
81        $returnType = 'float';
82        if (current($types) === 'integer') {
83            $returnType = 'integer';
84        }
85
86        return $this->_literalArgumentFunction('SUM', $expression, $types, $returnType);
87    }
88
89    /**
90     * Returns a FunctionExpression representing a call to SQL AVG function.
91     *
92     * @param string|\Cake\Database\ExpressionInterface $expression the function argument
93     * @param array $types list of types to bind to the arguments
94     * @return \Cake\Database\Expression\FunctionExpression
95     */
96    public function avg($expression, $types = [])
97    {
98        return $this->_literalArgumentFunction('AVG', $expression, $types, 'float');
99    }
100
101    /**
102     * Returns a FunctionExpression representing a call to SQL MAX function.
103     *
104     * @param string|\Cake\Database\ExpressionInterface $expression the function argument
105     * @param array $types list of types to bind to the arguments
106     * @return \Cake\Database\Expression\FunctionExpression
107     */
108    public function max($expression, $types = [])
109    {
110        return $this->_literalArgumentFunction('MAX', $expression, $types, current($types) ?: 'string');
111    }
112
113    /**
114     * Returns a FunctionExpression representing a call to SQL MIN function.
115     *
116     * @param string|\Cake\Database\ExpressionInterface $expression the function argument
117     * @param array $types list of types to bind to the arguments
118     * @return \Cake\Database\Expression\FunctionExpression
119     */
120    public function min($expression, $types = [])
121    {
122        return $this->_literalArgumentFunction('MIN', $expression, $types, current($types) ?: 'string');
123    }
124
125    /**
126     * Returns a FunctionExpression representing a call to SQL COUNT function.
127     *
128     * @param string|\Cake\Database\ExpressionInterface $expression the function argument
129     * @param array $types list of types to bind to the arguments
130     * @return \Cake\Database\Expression\FunctionExpression
131     */
132    public function count($expression, $types = [])
133    {
134        return $this->_literalArgumentFunction('COUNT', $expression, $types, 'integer');
135    }
136
137    /**
138     * Returns a FunctionExpression representing a string concatenation
139     *
140     * @param array $args List of strings or expressions to concatenate
141     * @param array $types list of types to bind to the arguments
142     * @return \Cake\Database\Expression\FunctionExpression
143     */
144    public function concat($args, $types = [])
145    {
146        return $this->_build('CONCAT', $args, $types, 'string');
147    }
148
149    /**
150     * Returns a FunctionExpression representing a call to SQL COALESCE function.
151     *
152     * @param array $args List of expressions to evaluate as function parameters
153     * @param array $types list of types to bind to the arguments
154     * @return \Cake\Database\Expression\FunctionExpression
155     */
156    public function coalesce($args, $types = [])
157    {
158        return $this->_build('COALESCE', $args, $types, current($types) ?: 'string');
159    }
160
161    /**
162     * Returns a FunctionExpression representing a call to SQL CAST function.
163     *
164     * @param string|\Cake\Database\ExpressionInterface $field Field or expression to cast.
165     * @param string $type The target data type
166     * @return \Cake\Database\Expression\FunctionExpression
167     */
168    public function cast($field, $type = '')
169    {
170        if (is_array($field)) {
171            deprecationWarning(
172                'Build cast function by FunctionsBuilder::cast(array $args) is deprecated. ' .
173                'Use FunctionsBuilder::cast($field, string $type) instead.'
174            );
175
176            return $this->_build('CAST', $field);
177        }
178
179        $expression = $this->_literalArgumentFunction('CAST', $field);
180        $expression->setConjunction(' AS')->add([$type => 'literal']);
181
182        return $expression;
183    }
184
185    /**
186     * Returns a FunctionExpression representing the difference in days between
187     * two dates.
188     *
189     * @param array $args List of expressions to obtain the difference in days.
190     * @param array $types list of types to bind to the arguments
191     * @return \Cake\Database\Expression\FunctionExpression
192     */
193    public function dateDiff($args, $types = [])
194    {
195        return $this->_build('DATEDIFF', $args, $types, 'integer');
196    }
197
198    /**
199     * Returns the specified date part from the SQL expression.
200     *
201     * @param string $part Part of the date to return.
202     * @param string|\Cake\Database\ExpressionInterface $expression Expression to obtain the date part from.
203     * @param array $types list of types to bind to the arguments
204     * @return \Cake\Database\Expression\FunctionExpression
205     */
206    public function datePart($part, $expression, $types = [])
207    {
208        return $this->extract($part, $expression, $types);
209    }
210
211    /**
212     * Returns the specified date part from the SQL expression.
213     *
214     * @param string $part Part of the date to return.
215     * @param string|\Cake\Database\ExpressionInterface $expression Expression to obtain the date part from.
216     * @param array $types list of types to bind to the arguments
217     * @return \Cake\Database\Expression\FunctionExpression
218     */
219    public function extract($part, $expression, $types = [])
220    {
221        $expression = $this->_literalArgumentFunction('EXTRACT', $expression, $types, 'integer');
222        $expression->setConjunction(' FROM')->add([$part => 'literal'], [], true);
223
224        return $expression;
225    }
226
227    /**
228     * Add the time unit to the date expression
229     *
230     * @param string|\Cake\Database\ExpressionInterface $expression Expression to obtain the date part from.
231     * @param string|int $value Value to be added. Use negative to subtract.
232     * @param string $unit Unit of the value e.g. hour or day.
233     * @param array $types list of types to bind to the arguments
234     * @return \Cake\Database\Expression\FunctionExpression
235     */
236    public function dateAdd($expression, $value, $unit, $types = [])
237    {
238        if (!is_numeric($value)) {
239            $value = 0;
240        }
241        $interval = $value . ' ' . $unit;
242        $expression = $this->_literalArgumentFunction('DATE_ADD', $expression, $types, 'datetime');
243        $expression->setConjunction(', INTERVAL')->add([$interval => 'literal']);
244
245        return $expression;
246    }
247
248    /**
249     * Returns a FunctionExpression representing a call to SQL WEEKDAY function.
250     * 1 - Sunday, 2 - Monday, 3 - Tuesday...
251     *
252     * @param string|\Cake\Database\ExpressionInterface $expression the function argument
253     * @param array $types list of types to bind to the arguments
254     * @return \Cake\Database\Expression\FunctionExpression
255     */
256    public function dayOfWeek($expression, $types = [])
257    {
258        return $this->_literalArgumentFunction('DAYOFWEEK', $expression, $types, 'integer');
259    }
260
261    /**
262     * Returns a FunctionExpression representing a call to SQL WEEKDAY function.
263     * 1 - Sunday, 2 - Monday, 3 - Tuesday...
264     *
265     * @param string|\Cake\Database\ExpressionInterface $expression the function argument
266     * @param array $types list of types to bind to the arguments
267     * @return \Cake\Database\Expression\FunctionExpression
268     */
269    public function weekday($expression, $types = [])
270    {
271        return $this->dayOfWeek($expression, $types);
272    }
273
274    /**
275     * Returns a FunctionExpression representing a call that will return the current
276     * date and time. By default it returns both date and time, but you can also
277     * make it generate only the date or only the time.
278     *
279     * @param string $type (datetime|date|time)
280     * @return \Cake\Database\Expression\FunctionExpression
281     */
282    public function now($type = 'datetime')
283    {
284        if ($type === 'datetime') {
285            return $this->_build('NOW')->setReturnType('datetime');
286        }
287        if ($type === 'date') {
288            return $this->_build('CURRENT_DATE')->setReturnType('date');
289        }
290        if ($type === 'time') {
291            return $this->_build('CURRENT_TIME')->setReturnType('time');
292        }
293    }
294
295    /**
296     * Magic method dispatcher to create custom SQL function calls
297     *
298     * @param string $name the SQL function name to construct
299     * @param array $args list with up to 3 arguments, first one being an array with
300     * parameters for the SQL function, the second one a list of types to bind to those
301     * params, and the third one the return type of the function
302     * @return \Cake\Database\Expression\FunctionExpression
303     */
304    public function __call($name, $args)
305    {
306        switch (count($args)) {
307            case 0:
308                return $this->_build($name);
309            case 1:
310                return $this->_build($name, $args[0]);
311            case 2:
312                return $this->_build($name, $args[0], $args[1]);
313            default:
314                return $this->_build($name, $args[0], $args[1], $args[2]);
315        }
316    }
317}
318