1<?php
2	/**
3	* SQL Generator OPERANDS & TYPES - help to create criterias for common queries
4	* @author Edgar Antonio Luna Diaz <eald@co.com.mx>
5	* @author Alejadro Borges
6	* @author Jonathan Alberto Rivera Gomez
7	* @copyright Copyright (C) 2003,2004 Free Software Foundation, Inc. http://www.fsf.org/
8	* @license http://www.fsf.org/licenses/gpl.html GNU General Public License
9	* @package phpgwapi
10	* @subpackage database
11	* @version $Id: class.sql.inc.php 21045 2010-03-25 22:41:38Z Caeies $
12	* @internal Development of this application was funded by http://www.sogrp.com
13	* @link http://www.sogrp.com/
14	*/
15
16	if (empty($GLOBALS['phpgw_info']['server']['db_type']))
17	{
18		$GLOBALS['phpgw_info']['server']['db_type'] = 'mysql';
19	}
20
21	/**
22	* Include concrete database class
23	*/
24	include(PHPGW_API_INC.'/class.sql_'.$GLOBALS['phpgw_info']['server']['db_type'].'.inc.php');
25
26
27	/**
28	* SQL Generator OPERANDS & TYPES - help to create criterias for common queries
29	*
30	* This class provide common methods to set, mantain, an retrive the queries
31	* to use in a query (for the where clause).
32	* @package phpgwapi
33	* @subpackage database
34	* @abstract
35	*/
36	class sql_
37	{
38		function sql_()
39		{
40		}
41
42		/*************************************************************\
43		* Usefull low level functions to create queries logically   *
44		\*************************************************************/
45
46		/**
47		* Genarete equal criteria for sql
48		*
49		* @param string $left The left operand of the staement
50		* @param string $right The right operand of the statement
51		* @return string with an equal criteria formated.
52		*/
53		function equal($field, $value)
54		{
55			return $field.' = '.$value;
56		}
57
58		/**
59		* Generate a critieria for non equal comparission for sql.
60		*
61		* @param string $left Left operand.
62		* @param string $right Right operand.
63		* @return string with criteria.
64		*/
65		function not_equal($field, $value)
66		{
67			return $field.' <> '.$value;
68		}
69
70		/**
71		* Generate greater than criteria for sql
72		*
73		* @param string $left The left operand of the staement
74  		* @param string $right The right operand of the statement
75		* @return string with an greater than criteria formated.
76		*/
77		function greater($field, $value)
78		{
79			return $field.' > '.$value;
80		}
81
82		/**
83		* Generate less than criteria for sql (in string)
84		*
85		* @param string $left The left operand of the staement
86		* @param string $right The right operand of the statement
87		* @return string with an less than criteria formated.
88		*/
89		function less($field, $value)
90		{
91			return $field.' < '.$value;
92		}
93
94		/**
95		* Generate greater-equal than criteria for sql
96		*
97		* @param string $left The left operand of the staement
98  		* @param string $right The right operand of the statement
99		* @return string with an greater-equal than criteria formated.
100		*/
101		function greater_equal($field, $value)
102		{
103			return $field.' >= '.$value;
104		}
105
106		/**
107		* Generate less-equal than criteria for sql (in string)
108		*
109		* @param string $left The left operand of the staement
110		* @param string $right The right operand of the statement
111		* @return string with an less-equal than criteria formated.
112		*/
113		function less_equal($field, $value)
114		{
115			return $field.' <= '.$value;
116		}
117
118		/**
119		* Generate a criteria for search in the content of a field a value for sql.
120		*
121		* @param string $field For search in.
122		* @param string $value That will search.
123		* @return string that use LIKE to search in field.
124		*/
125		function has($field, $value)
126		{
127			return sql_criteria::upper($field).' LIKE '."'%$value%'";
128		}
129
130		/**
131		* Generate a criteria to search in the beginning of a field for sql.
132		*
133		* @param string $field For search in.
134		* @param string $value That will search.
135		* @return string that use LIKE to search in field.
136		*/
137		function begin_with($field, $value)
138		{
139			return sql_criteria::upper($field).' LIKE '."'$value%'";
140		}
141
142		/**
143		* Generate a critieria to search in the end of a field for sql.
144		*
145		* @param string $field For search in.
146		* @param string $value That will search.
147		* @return string that use LIKE to search in field.
148		*/
149		function end_with($field, $value)
150		{
151			return sql_criteria::upper($field).' LIKE '."'%$value'";
152		}
153
154		/**
155		* Generate an AND conjuction for sql criterias.
156		*
157		* Always return with brackets. I have more confidence in DBMS speed than the code that I will need to analize it in php.
158		* @param string $left Left operand.
159		* @param string $right Right operand.
160		* @return string with (right) and (left)
161		*/
162		function and_($left, $right)
163		{
164			return '('.$left.' AND '.$right.')';
165		}
166
167		/**
168		* Generate an OR conjuction for sql criterias.
169		*
170		* @param string $left Left operand.
171		* @param string $right Right operand.
172		* @return string with (right) or (left)
173		*/
174		function or_($left, $right)
175		{
176			return ' ('.$left.' OR '.$right.') ';
177		}
178
179		/**
180		* Generate a is null critieria for sql.
181		*
182		* @param string $data A field.
183		* @return string with criteria.
184		*/
185		function is_null($data)
186		{
187			return $data.' IS NULL';
188		}
189
190		/**
191		* Generate a is not null critieria for sql.
192		*
193		* @param string $data A field.
194		* @return string with criteria.
195		*/
196		function not_null($data)
197		{
198			return $data.' IS NOT NULL';
199		}
200
201		function upper($value)
202		{
203			return 'UPPER('.$value.')';
204		}
205
206		function lower($value)
207		{
208			return 'LOWER('.$value.')';
209		}
210
211		/**
212		* Generate a IN sql operator
213		*
214		* @param string $field String with the field which you can filter.
215		* @param string $values Array with posible values
216		* @return string with criteria.
217		*/
218		function in($field, $values, $type='integer')
219		{
220			// This must be changed by anything
221			if(count($values) > 1)
222			{
223				if($type != 'integer' && $type != '')
224				{
225					return str_replace(',\'', '\',\'', $field.' IN (\''.implode(",'",$values)."')");
226				}
227				else
228				{
229					return $field.' IN ('.implode(",", $values) .')';
230				}
231			}
232			else
233			{
234				$type = $type ? $type : 'integer';
235				if (is_array($values))
236				{
237					return sql::equal($field, sql::$type(current($values)));
238				}
239				else
240				{
241					return sql::equal($field, sql::$type($values));
242				}
243			}
244		}
245
246		/**
247		* Act like a lisp and(one, two, three,...infinity) adding clause with and.
248		*
249		* All and's are in same level, (without parenethesis).
250		* @param string $and Array with the list of operators for and.
251		* @return string with many and conjuntions at same level.
252		*/
253		function append_and($clause)
254		{
255			if(is_array($clause))
256			{
257				$value = array_shift($clause);
258				$return_value = $value;
259				foreach($clause as $element)
260				{
261					$return_value .= empty($element)?'':' AND '.$element;
262				}
263				return '('. $return_value .')';
264			}
265		}
266
267		/**
268		* Append many criterias with `or' conjuntion
269		*
270		* @param string $and Array with the list of operators for or.
271		* @return string with many or conjuntions at same level.
272		* @see append_and
273		*/
274		function append_or($clause)
275		{
276			if(is_array($clause))
277			{
278				$value = array_shift($clause);
279				$return_value = $value;
280				foreach($clause as $element)
281				{
282					$return_value .= empty($element)?'':' OR '.$element;
283				}
284				return '('. $return_value.')';
285			}
286		}
287		/*************************************************************\
288		* SQL Standard Types                                         *
289		\*************************************************************/
290
291		/**
292		* @param str string the value that will be casted for sql type
293		* @return string ready for using for a value with CHARACTER sql type
294		*/
295		function string($str)
296		{
297			$str = db::db_addslashes($str);
298			return "'$str'";
299		}
300
301		function character($str)
302		{
303			return sql::string($str);
304		}
305
306		/**
307		* @param integer string the value that will be casted for sql type
308		* @return string ready for using for a value with INTEGER sql type
309		*/
310		function integer($integer)
311		{
312			return intval($integer);
313		}
314
315		/**
316		* Generate a string with date
317		*/
318		function date_($date, $format=False)
319		{
320			switch(gettype($date))
321			{
322			case 'integer':
323				return sql::int_date2str($date, $format);
324			default:
325				return sql::str_date2int($date, $format);
326			}
327		}
328
329		/**
330		* return a string with time
331		*/
332		function time_($time, $format=False)
333		{
334			switch(gettype($time))
335			{
336			case 'integer':
337				return sql::int_time2str($time, $format);
338			default:
339				return sql::str_time2int($time, $format);
340			}
341		}
342
343		/*************************************************************\
344		* Data types conversion                                      *
345		\*************************************************************/
346
347		function int_date2str($int, $format=False)
348		{
349			$format = $format ? $format : $GLOBALS['phpgw_info']['user']['preferences']['common']['dateformat'];
350			return date($format, intval($int));
351		}
352
353		function int_time2str($int, $format=False)
354		{
355			$format = $format ? $format : $GLOBALS['phpgw_info']['user']['preferences']['common']['timeformat'];
356			return date($format, intval($int));
357		}
358		//note this is not 100% reliable, but close enough
359		function str_date2int($date, $format=False)
360		{
361			$format = $format ? $format : $GLOBALS['phpgw_info']['user']['preferences']['common']['dateformat'];
362			return date($format, intval(strtotime($date)));
363		}
364
365		function str_time2int($time)
366		{
367			return intval(sql::str_date2int($time));
368		}
369
370		/*************************************************************\
371		* Constants                                                   *
372		\*************************************************************/
373
374		/**
375		* Return a NULL value
376		*/
377		function null()
378		{
379			return ' NULL ';
380		}
381		/*************************************************************\
382		* Functions                                                   *
383		\*************************************************************/
384		/**
385		* Return the function that concatenate fields on $elements
386		*
387		* @param array $elements array with the elemnts that want to concatenate
388		* @return string with $elements concatenated
389		*/
390		function concat($elements)
391		{
392		}
393
394		/**
395		* Return the function that concatenate fields, when any returned value<br />
396		* is null, it changet it for empty string.
397		*
398		* @param array $elements array with the elemnts that want to concatenate
399		* @return string with $elements concatenated
400		*/
401		function concat_null($elements)
402		{
403		}
404
405		/**
406		* This function change to empty string, a NULL value for select.
407		*
408		* When data retrieved from database is NULL it allow change it to empty<br />
409		* string. use it in SELECT development.
410		* @param string $value Field or expresion to make safe.
411		*/
412		function safe_null($value)
413		{
414			if(empty($value) || !is_array($value))
415			{
416				return array();
417			}
418			foreach($value as $data)
419			{
420				$return_value[] = '(CASE WHEN '.$data.' IS NULL THEN \'\' ELSE '.$data.' END)';
421			}
422			return $return_value;
423		}
424	}
425?>
426