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: * 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 mixed $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 mixed $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 mixed $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 mixed $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 mixed $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 mixed $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 the difference in days between
163: * two dates.
164: *
165: * @param array $args List of expressions to obtain the difference in days.
166: * @param array $types list of types to bind to the arguments
167: * @return \Cake\Database\Expression\FunctionExpression
168: */
169: public function dateDiff($args, $types = [])
170: {
171: return $this->_build('DATEDIFF', $args, $types, 'integer');
172: }
173:
174: /**
175: * Returns the specified date part from the SQL expression.
176: *
177: * @param string $part Part of the date to return.
178: * @param string $expression Expression to obtain the date part from.
179: * @param array $types list of types to bind to the arguments
180: * @return \Cake\Database\Expression\FunctionExpression
181: */
182: public function datePart($part, $expression, $types = [])
183: {
184: return $this->extract($part, $expression);
185: }
186:
187: /**
188: * Returns the specified date part from the SQL expression.
189: *
190: * @param string $part Part of the date to return.
191: * @param string $expression Expression to obtain the date part from.
192: * @param array $types list of types to bind to the arguments
193: * @return \Cake\Database\Expression\FunctionExpression
194: */
195: public function extract($part, $expression, $types = [])
196: {
197: $expression = $this->_literalArgumentFunction('EXTRACT', $expression, $types, 'integer');
198: $expression->setConjunction(' FROM')->add([$part => 'literal'], [], true);
199:
200: return $expression;
201: }
202:
203: /**
204: * Add the time unit to the date expression
205: *
206: * @param string $expression Expression to obtain the date part from.
207: * @param string $value Value to be added. Use negative to subtract.
208: * @param string $unit Unit of the value e.g. hour or day.
209: * @param array $types list of types to bind to the arguments
210: * @return \Cake\Database\Expression\FunctionExpression
211: */
212: public function dateAdd($expression, $value, $unit, $types = [])
213: {
214: if (!is_numeric($value)) {
215: $value = 0;
216: }
217: $interval = $value . ' ' . $unit;
218: $expression = $this->_literalArgumentFunction('DATE_ADD', $expression, $types, 'datetime');
219: $expression->setConjunction(', INTERVAL')->add([$interval => 'literal']);
220:
221: return $expression;
222: }
223:
224: /**
225: * Returns a FunctionExpression representing a call to SQL WEEKDAY function.
226: * 1 - Sunday, 2 - Monday, 3 - Tuesday...
227: *
228: * @param mixed $expression the function argument
229: * @param array $types list of types to bind to the arguments
230: * @return \Cake\Database\Expression\FunctionExpression
231: */
232: public function dayOfWeek($expression, $types = [])
233: {
234: return $this->_literalArgumentFunction('DAYOFWEEK', $expression, $types, 'integer');
235: }
236:
237: /**
238: * Returns a FunctionExpression representing a call to SQL WEEKDAY function.
239: * 1 - Sunday, 2 - Monday, 3 - Tuesday...
240: *
241: * @param mixed $expression the function argument
242: * @param array $types list of types to bind to the arguments
243: * @return \Cake\Database\Expression\FunctionExpression
244: */
245: public function weekday($expression, $types = [])
246: {
247: return $this->dayOfWeek($expression, $types);
248: }
249:
250: /**
251: * Returns a FunctionExpression representing a call that will return the current
252: * date and time. By default it returns both date and time, but you can also
253: * make it generate only the date or only the time.
254: *
255: * @param string $type (datetime|date|time)
256: * @return \Cake\Database\Expression\FunctionExpression
257: */
258: public function now($type = 'datetime')
259: {
260: if ($type === 'datetime') {
261: return $this->_build('NOW')->setReturnType('datetime');
262: }
263: if ($type === 'date') {
264: return $this->_build('CURRENT_DATE')->setReturnType('date');
265: }
266: if ($type === 'time') {
267: return $this->_build('CURRENT_TIME')->setReturnType('time');
268: }
269: }
270:
271: /**
272: * Magic method dispatcher to create custom SQL function calls
273: *
274: * @param string $name the SQL function name to construct
275: * @param array $args list with up to 3 arguments, first one being an array with
276: * parameters for the SQL function, the second one a list of types to bind to those
277: * params, and the third one the return type of the function
278: * @return \Cake\Database\Expression\FunctionExpression
279: */
280: public function __call($name, $args)
281: {
282: switch (count($args)) {
283: case 0:
284: return $this->_build($name);
285: case 1:
286: return $this->_build($name, $args[0]);
287: case 2:
288: return $this->_build($name, $args[0], $args[1]);
289: default:
290: return $this->_build($name, $args[0], $args[1], $args[2]);
291: }
292: }
293: }
294: