Account Suspended
Account Suspended
This Account has been suspended.
Contact your hosting provider for more information.
 All Data Structures Functions Variables Pages
SQLOperatorUtil.php
1 <?php
2  /*********************************************************************************
3  * Zurmo is a customer relationship management program developed by
4  * Zurmo, Inc. Copyright (C) 2017 Zurmo Inc.
5  *
6  * Zurmo is free software; you can redistribute it and/or modify it under
7  * the terms of the GNU Affero General Public License version 3 as published by the
8  * Free Software Foundation with the addition of the following permission added
9  * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
10  * IN WHICH THE COPYRIGHT IS OWNED BY ZURMO, ZURMO DISCLAIMS THE WARRANTY
11  * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
12  *
13  * Zurmo is distributed in the hope that it will be useful, but WITHOUT
14  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
15  * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
16  * details.
17  *
18  * You should have received a copy of the GNU Affero General Public License along with
19  * this program; if not, see http://www.gnu.org/licenses or write to the Free
20  * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
21  * 02110-1301 USA.
22  *
23  * You can contact Zurmo, Inc. with a mailing address at 27 North Wacker Drive
24  * Suite 370 Chicago, IL 60606. or at email address contact@zurmo.com.
25  *
26  * The interactive user interfaces in original and modified versions
27  * of this program must display Appropriate Legal Notices, as required under
28  * Section 5 of the GNU Affero General Public License version 3.
29  *
30  * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
31  * these Appropriate Legal Notices must retain the display of the Zurmo
32  * logo and Zurmo copyright notice. If the display of the logo is not reasonably
33  * feasible for technical reasons, the Appropriate Legal Notices must display the words
34  * "Copyright Zurmo Inc. 2017. All rights reserved".
35  ********************************************************************************/
36 
42  {
47  public static function isValidOperatorTypeByValue($operatorType, $value)
48  {
49  if (is_string($value))
50  {
51  return in_array($operatorType, array('startsWith', 'endsWith', 'equals', 'doesNotEqual', 'contains',
52  'doesNotContains', 'doesNotStartsWith', 'doesNotEndsWith',
53  'lessThan', 'greaterThan', 'greaterThanOrEqualTo',
54  'lessThanOrEqualTo'));
55  }
56  elseif (is_array($value))
57  {
58  return in_array($operatorType, array('oneOf'));
59  }
60  elseif ($value !== null)
61  {
62  return in_array($operatorType, array('greaterThan', 'lessThan', 'equals', 'doesNotEqual',
63  'greaterThanOrEqualTo', 'lessThanOrEqualTo'));
64  }
65  elseif ($value === null)
66  {
67  return in_array($operatorType, array('isNull', 'isNotNull', 'isEmpty', 'isNotEmpty'));
68  }
69  return false;
70  }
71 
77  public static function getOperatorByType($operatorType)
78  {
79  assert('is_string($operatorType)');
80  $validOperator = true;
81  if (YII_DEBUG)
82  {
83  $validOperator = SQLOperatorUtil::isValidOperatorType($operatorType);
84  }
85  if ($validOperator)
86  {
87  switch ($operatorType)
88  {
89  case 'startsWith' :
90  return 'like';
91 
92  case 'doesNotStartsWith' :
93  return 'not like';
94 
95  case 'endsWith' :
96  return 'like';
97 
98  case 'doesNotEndsWith' :
99  return 'not like';
100 
101  case 'contains' :
102  return 'like';
103 
104  case 'doesNotContains' :
105  return 'not like';
106 
107  case 'equals' :
108  return '=';
109 
110  case 'doesNotEqual' :
111  return '!=';
112 
113  case 'greaterThan' :
114  return '>';
115 
116  case 'lessThan' :
117  return '<';
118 
119  case 'greaterThanOrEqualTo' :
120  return '>=';
121 
122  case 'lessThanOrEqualTo' :
123  return '<=';
124 
125  default :
126  throw new NotSupportedException('Unsupported operator type: ' . $operatorType);
127  }
128  }
129  }
130 
134  public static function resolveValueLeftSideLikePartByOperatorType($operatorType)
135  {
136  assert('is_string($operatorType)');
137  $validOperator = true;
138  if (YII_DEBUG)
139  {
140  $validOperator = SQLOperatorUtil::isValidOperatorType($operatorType);
141  }
142  if ($validOperator && in_array($operatorType, array('endsWith', 'doesNotEndsWith', 'contains', 'doesNotContains')))
143  {
144  return '%';
145  }
146  }
147 
151  public static function resolveValueRightSideLikePartByOperatorType($operatorType)
152  {
153  assert('is_string($operatorType)');
154  $validOperator = true;
155  if (YII_DEBUG)
156  {
157  $validOperator = SQLOperatorUtil::isValidOperatorType($operatorType);
158  }
159  if ($validOperator && in_array($operatorType, array('startsWith', 'doesNotStartsWith', 'contains', 'doesNotContains')))
160  {
161  return '%';
162  }
163  }
164 
165  public static function resolveOperatorAndValueForOneOf($operatorType, $values, $ignoreStringToLower = false)
166  {
167  assert('$operatorType == "oneOf"');
168  assert('is_array($values) && count($values) > 0');
169  $inPart = null;
170  foreach ($values as $theValue)
171  {
172  if ($inPart != null)
173  {
174  $inPart .= ','; // Not Coding Standard
175  }
176  if (is_string($theValue))
177  {
178  if ($ignoreStringToLower)
179  {
180  $inPart .= "'" . DatabaseCompatibilityUtil::escape($theValue) . "'";
181  }
182  else
183  {
184  $inPart .= "'" . DatabaseCompatibilityUtil::escape($theValue) . "'";
185  }
186  }
187  elseif (is_numeric($theValue))
188  {
189  $inPart .= $theValue;
190  }
191  elseif (is_bool($theValue))
192  {
193  if (!$theValue)
194  {
195  $theValue = 0;
196  }
197  $inPart .= $theValue;
198  }
199  else
200  {
201  throw new NotSupportedException();
202  }
203  }
204  return 'IN(' . $inPart . ')';
205  }
206 
207  public static function resolveOperatorAndValueForNullOrEmpty($operatorType)
208  {
209  assert('in_array($operatorType, array("isNull", "isNotNull", "isEmpty", "isNotEmpty"))');
210  if ($operatorType == 'isNull')
211  {
212  return 'IS NULL'; // Not Coding Standard
213  }
214  elseif ($operatorType == 'isNotNull')
215  {
216  return 'IS NOT NULL'; // Not Coding Standard
217  }
218  elseif ($operatorType == 'isEmpty')
219  {
220  return "= ''";
221  }
222  else
223  {
224  return "!= ''";
225  }
226  }
227 
231  protected static function isValidOperatorType($type)
232  {
233  if (in_array($type, array(
234  'startsWith',
235  'doesNotStartsWith',
236  'endsWith',
237  'doesNotEndsWith',
238  'contains',
239  'doesNotContains',
240  'equals',
241  'doesNotEqual',
242  'greaterThanOrEqualTo',
243  'lessThanOrEqualTo',
244  'greaterThan',
245  'lessThan',
246  'oneOf',
247  'isNull',
248  'isNotNull',
249  'isEmpty',
250  'isNotEmpty')))
251  {
252  return true;
253  }
254  return false;
255  }
256 
257  public static function doesOperatorTypeAllowNullValues($type)
258  {
259  assert('is_string($type)');
260  if (in_array($type, array(
261  'isNull',
262  'isNotNull',
263  'isEmpty',
264  'isNotEmpty')))
265  {
266  return true;
267  }
268  return false;
269  }
270 
277  public static function resolveValidationForATemplateSqlStatementAndReturnErrorMessage($structure, $clauseCount)
278  {
279  assert('is_string($structure)');
280  assert('is_int($clauseCount)');
281  $formula = strtolower($structure);
282  if (!self::validateParenthesis($formula) )
283  {
284  $errorContent = Zurmo::t('Core', 'Please fix your parenthesis.');
285  }
286  else
287  {
288  $arguments = preg_split("/or|and/", $formula);
289  foreach ($arguments as $argument)
290  {
291  if (preg_match("/\(\s*not\s*\)/", $argument))
292  {
293  $errorContent = Zurmo::t('Core', 'Please fix your parenthesis around the {not} operator.',
294  array('{not}' => 'not'));
295  }
296  $argument = str_replace("(", "", $argument);
297  $argument = str_replace(")", "", $argument);
298  $argument = trim(str_replace('not', '', $argument));
299  if (intval($argument) === 0)
300  {
301  $errorContent = Zurmo::t('Core', 'Please fix conditions.');
302  }
303  elseif (!is_numeric($argument))
304  {
305  $errorContent = Zurmo::t('Core', 'Please, only use one of the operators: {operators}.',
306  array('{operators}' => 'not, and, or'));
307  }
308  elseif (!(intval($argument) <= $clauseCount) ||
309  !(intval($argument) > 0) ||
310  !(preg_match("/\./", $argument) === 0) )
311  {
312  $errorContent = Zurmo::t('Core', 'Please use only integers less than {max}.',
313  array('{max}' => $clauseCount + 1));
314  }
315  }
316  }
317  if (isset($errorContent))
318  {
319  return Zurmo::t('Core', 'The structure is invalid. {error}', array('{error}' => $errorContent));
320  }
321  }
322 
326  public static function validateParenthesis($formula)
327  {
328  $val = 0;
329  for ($i = 0; $i <= strlen($formula); $i++)
330  {
331  $char = substr($formula, $i, 1);
332  if ($char === "(")
333  {
334  $val += 1;
335  }
336  elseif ($char === ")")
337  {
338  $val -= 1;
339  }
340  if ($val < 0)
341  {
342  return false;
343  }
344  }
345  if ($val !== 0)
346  {
347  return false;
348  }
349  else
350  {
351  return true;
352  }
353  }
354  }
355 ?>
static resolveValidationForATemplateSqlStatementAndReturnErrorMessage($structure, $clauseCount)
static validateParenthesis($formula)
static getOperatorByType($operatorType)
static isValidOperatorTypeByValue($operatorType, $value)
static resolveValueLeftSideLikePartByOperatorType($operatorType)
static isValidOperatorType($type)
static resolveValueRightSideLikePartByOperatorType($operatorType)
Generated on Mon Jul 6 2020 07:10:30
Account Suspended
Account Suspended
This Account has been suspended.
Contact your hosting provider for more information.