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