1: <?php
  2: /**
  3:  * XOOPS Criteria parser for database query
  4:  *
  5:  * You may not change or alter any portion of this comment or credits
  6:  * of supporting developers from this source code or any supporting source code
  7:  * which is considered copyrighted (c) material of the original comment or credit authors.
  8:  * This program is distributed in the hope that it will be useful,
  9:  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 10:  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 11:  *
 12:  * @copyright       (c) 2000-2016 XOOPS Project (www.xoops.org)
 13:  * @license             GNU GPL 2 (http://www.gnu.org/licenses/gpl-2.0.html)
 14:  * @package             kernel
 15:  * @subpackage          database
 16:  * @since               2.0.0
 17:  * @author              Kazumi Ono <onokazu@xoops.org>
 18:  * @author              Nathan Dial
 19:  * @author              Taiwen Jiang <phppp@users.sourceforge.net>
 20:  */
 21: defined('XOOPS_ROOT_PATH') || exit('Restricted access');
 22: 
 23: /**
 24:  * A criteria (grammar?) for a database query.
 25:  *
 26:  * Abstract base class should never be instantiated directly.
 27:  *
 28:  * @abstract
 29:  */
 30: class CriteriaElement
 31: {
 32:     /**
 33:      * Sort order
 34:      *
 35:      * @var string
 36:      */
 37:     public $order = 'ASC';
 38: 
 39:     /**
 40:      *
 41:      * @var string
 42:      */
 43:     public $sort = '';
 44: 
 45:     /**
 46:      * Number of records to retrieve
 47:      *
 48:      * @var int
 49:      */
 50:     public $limit = 0;
 51: 
 52:     /**
 53:      * Offset of first record
 54:      *
 55:      * @var int
 56:      */
 57:     public $start = 0;
 58: 
 59:     /**
 60:      *
 61:      * @var string
 62:      */
 63:     public $groupby = '';
 64: 
 65:     /**
 66:      * Constructor
 67:      */
 68:     public function __construct()
 69:     {
 70:     }
 71: 
 72:     /**
 73:      * Render the criteria element
 74:      * @return string
 75:      */
 76:     public function render()
 77:     {
 78:     }
 79: 
 80:     /**
 81:      *
 82:      * @param string $sort
 83:      */
 84:     public function setSort($sort)
 85:     {
 86:         $this->sort = $sort;
 87:     }
 88: 
 89:     /**
 90:      *
 91:      * @return string
 92:      */
 93:     public function getSort()
 94:     {
 95:         return $this->sort;
 96:     }
 97: 
 98:     /**
 99:      *
100:      * @param string $order
101:      */
102:     public function setOrder($order)
103:     {
104:         if ('DESC' === strtoupper($order)) {
105:             $this->order = 'DESC';
106:         }
107:     }
108: 
109:     /**
110:      *
111:      * @return string
112:      */
113:     public function getOrder()
114:     {
115:         return $this->order;
116:     }
117: 
118:     /**
119:      *
120:      * @param int $limit
121:      */
122:     public function setLimit($limit = 0)
123:     {
124:         $this->limit = (int)$limit;
125:     }
126: 
127:     /**
128:      *
129:      * @return int
130:      */
131:     public function getLimit()
132:     {
133:         return $this->limit;
134:     }
135: 
136:     /**
137:      *
138:      * @param int $start
139:      */
140:     public function setStart($start = 0)
141:     {
142:         $this->start = (int)$start;
143:     }
144: 
145:     /**
146:      *
147:      * @return int
148:      */
149:     public function getStart()
150:     {
151:         return $this->start;
152:     }
153: 
154:     /**
155:      *
156:      * @param string $group
157:      */
158:     public function setGroupBy($group)
159:     {
160:         $this->groupby = $group;
161:     }
162: 
163:     /**
164:      *
165:      * @return string
166:      */
167:     public function getGroupby()
168:     {
169:         return $this->groupby ? " GROUP BY {$this->groupby}" : '';
170:     }
171:     /**
172:      * *#@-
173:      */
174: }
175: 
176: /**
177:  * Collection of multiple {@link CriteriaElement}s
178:  *
179:  */
180: class CriteriaCompo extends CriteriaElement
181: {
182:     /**
183:      * The elements of the collection
184:      *
185:      * @var array Array of {@link CriteriaElement} objects
186:      */
187:     public $criteriaElements = array();
188: 
189:     /**
190:      * Conditions
191:      *
192:      * @var array
193:      */
194:     public $conditions = array();
195: 
196:     /**
197:      * Constructor
198:      *
199:      * @param CriteriaElement|null $ele
200:      * @param string $condition
201:      */
202:     public function __construct(CriteriaElement $ele = null, $condition = 'AND')
203:     {
204:         if (isset($ele)) {
205:             $this->add($ele, $condition);
206:         }
207:     }
208: 
209:     /**
210:      * Add an element
211:      *
212:      * @param CriteriaElement|object $criteriaElement
213:      * @param string                 $condition
214:      * @return object reference to this collection
215:      */
216:     public function &add(CriteriaElement $criteriaElement, $condition = 'AND')
217:     {
218:         if (is_object($criteriaElement)) {
219:             $this->criteriaElements[] =& $criteriaElement;
220:             $this->conditions[]       = $condition;
221:         }
222: 
223:         return $this;
224:     }
225: 
226:     /**
227:      * Make the criteria into a query string
228:      *
229:      * @return string
230:      */
231:     public function render()
232:     {
233:         $ret   = '';
234:         $count = count($this->criteriaElements);
235:         if ($count > 0) {
236:             $render_string = $this->criteriaElements[0]->render();
237:             for ($i = 1; $i < $count; ++$i) {
238:                 if (!$render = $this->criteriaElements[$i]->render()) {
239:                     continue;
240:                 }
241:                 $render_string .= (empty($render_string) ? '' : ' ' . $this->conditions[$i] . ' ') . $render;
242:             }
243:             $ret = empty($render_string) ? '' : "({$render_string})";
244:         }
245: 
246:         return $ret;
247:     }
248: 
249:     /**
250:      * Make the criteria into a SQL "WHERE" clause
251:      *
252:      * @return string
253:      */
254:     public function renderWhere()
255:     {
256:         $ret = $this->render();
257:         $ret = ($ret != '') ? 'WHERE ' . $ret : $ret;
258: 
259:         return $ret;
260:     }
261: 
262:     /**
263:      * Generate an LDAP filter from criteria
264:      *
265:      * @return string
266:      * @author Nathan Dial ndial@trillion21.com
267:      */
268:     public function renderLdap()
269:     {
270:         $retval = '';
271:         $count  = count($this->criteriaElements);
272:         if ($count > 0) {
273:             $retval = $this->criteriaElements[0]->renderLdap();
274:             for ($i = 1; $i < $count; ++$i) {
275:                 $cond   = strtoupper($this->conditions[$i]);
276:                 $op     = ($cond === 'OR') ? '|' : '&';
277:                 $retval = "({$op}{$retval}" . $this->criteriaElements[$i]->renderLdap() . ')';
278:             }
279:         }
280: 
281:         return $retval;
282:     }
283: }
284: 
285: /**
286:  * A single criteria
287:  *
288:  */
289: class Criteria extends CriteriaElement
290: {
291:     /**
292:      *
293:      * @var string
294:      */
295:     public $prefix;
296:     public $function;
297:     public $column;
298:     public $operator;
299:     public $value;
300: 
301:     /**
302:      * Constructor
303:      *
304:      * @param string $column
305:      * @param string $value
306:      * @param string $operator
307:      * @param string $prefix
308:      * @param string $function
309:      */
310:     public function __construct($column, $value = '', $operator = '=', $prefix = '', $function = '')
311:     {
312:         $this->prefix   = $prefix;
313:         $this->function = $function;
314:         $this->column   = $column;
315:         $this->value    = $value;
316:         $this->operator = $operator;
317:     }
318: 
319:     /**
320:      * Make a sql condition string
321:      *
322:      * @return string
323:      */
324:     public function render()
325:     {
326:         $clause = (!empty($this->prefix) ? "{$this->prefix}." : '') . $this->column;
327:         if (!empty($this->function)) {
328:             $clause = sprintf($this->function, $clause);
329:         }
330:         if (in_array(strtoupper($this->operator), array('IS NULL', 'IS NOT NULL'))) {
331:             $clause .= ' ' . $this->operator;
332:         } else {
333:             if ('' === ($value = trim($this->value))) {
334:                 return '';
335:             }
336:             if (!in_array(strtoupper($this->operator), array('IN', 'NOT IN'))) {
337:                 if ((substr($value, 0, 1) !== '`') && (substr($value, -1) !== '`')) {
338:                     $value = "'{$value}'";
339:                 } elseif (!preg_match('/^[a-zA-Z0-9_\.\-`]*$/', $value)) {
340:                     $value = '``';
341:                 }
342:             }
343:             $clause .= " {$this->operator} {$value}";
344:         }
345: 
346:         return $clause;
347:     }
348: 
349:     /**
350:      * Generate an LDAP filter from criteria
351:      *
352:      * @return string
353:      * @author Nathan Dial ndial@trillion21.com, improved by Pierre-Eric MENUET pemen@sourceforge.net
354:      */
355:     public function renderLdap()
356:     {
357:         if ($this->operator === '>') {
358:             $this->operator = '>=';
359:         }
360:         if ($this->operator === '<') {
361:             $this->operator = '<=';
362:         }
363: 
364:         if ($this->operator === '!=' || $this->operator === '<>') {
365:             $operator = '=';
366:             $clause   = '(!(' . $this->column . $operator . $this->value . '))';
367:         } else {
368:             if ($this->operator === 'IN') {
369:                 $newvalue = str_replace(array('(', ')'), '', $this->value);
370:                 $tab      = explode(',', $newvalue);
371:                 foreach ($tab as $uid) {
372:                     $clause .= "({$this->column}={$uid})";
373:                 }
374:                 $clause = '(|' . $clause . ')';
375:             } else {
376:                 $clause = '(' . $this->column . $this->operator . $this->value . ')';
377:             }
378:         }
379: 
380:         return $clause;
381:     }
382: 
383:     /**
384:      * Make a SQL "WHERE" clause
385:      *
386:      * @return string
387:      */
388:     public function renderWhere()
389:     {
390:         $cond = $this->render();
391: 
392:         return empty($cond) ? '' : "WHERE {$cond}";
393:     }
394: }
395: