1: <?php
  2: /*
  3:  You may not change or alter any portion of this comment or credits
  4:  of supporting developers from this source code or any supporting source code
  5:  which is considered copyrighted (c) material of the original comment or credit authors.
  6: 
  7:  This program is distributed in the hope that it will be useful,
  8:  but WITHOUT ANY WARRANTY; without even the implied warranty of
  9:  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 10: */
 11: 
 12: namespace Xoops\Core\Kernel;
 13: 
 14: use Doctrine\DBAL\Query\QueryBuilder;
 15: 
 16: /**
 17:  * A single criteria for database query
 18:  *
 19:  * @category  Xoops\Core\Kernel\Criteria
 20:  * @package   Xoops\Core\Kernel
 21:  * @author    Kazumi Ono <onokazu@xoops.org>
 22:  * @author    Nathan Dial <ndial@trillion21.com>
 23:  * @author    Taiwen Jiang <phppp@users.sourceforge.net>
 24:  * @copyright 2000-2013 XOOPS Project (http://xoops.org)
 25:  * @license   GNU GPL 2 or later (http://www.gnu.org/licenses/gpl-2.0.html)
 26:  * @link      http://xoops.org
 27:  * @since     2.0.0
 28:  */
 29: class Criteria extends CriteriaElement
 30: {
 31:     /**
 32:      * @var string
 33:      */
 34:     public $prefix;
 35: 
 36:     /**
 37:      * @var string
 38:      */
 39:     public $function;
 40: 
 41:     /**
 42:      * @var string
 43:      */
 44:     public $column;
 45: 
 46:     /**
 47:      * @var string
 48:      */
 49:     public $operator;
 50: 
 51:     /**
 52:      * @var mixed
 53:      */
 54:     public $value;
 55: 
 56:     /**
 57:      * Constructor
 58:      *
 59:      * @param string $column   column criteria applies to
 60:      * @param string $value    value to compare to column
 61:      * @param string $operator operator to apply to column
 62:      * @param string $prefix   prefix to append to column
 63:      * @param string $function sprintf string taking one string argument applied to column
 64:      */
 65:     public function __construct($column, $value = '', $operator = '=', $prefix = '', $function = '')
 66:     {
 67:         $this->prefix = $prefix;
 68:         $this->function = $function;
 69:         $this->column = $column;
 70:         $this->value = $value;
 71:         $this->operator = $operator;
 72:     }
 73: 
 74:     /**
 75:      * Make a sql condition string
 76:      *
 77:      * @return string
 78:      */
 79:     public function render()
 80:     {
 81:         $clause = (!empty($this->prefix) ? "{$this->prefix}." : "") . $this->column;
 82:         if (!empty($this->function)) {
 83:             $clause = sprintf($this->function, $clause);
 84:         }
 85:         if (in_array(strtoupper($this->operator), array('IS NULL', 'IS NOT NULL'))) {
 86:             $clause .= ' ' . $this->operator;
 87:         } else {
 88:             if ('' === ($value = trim($this->value))) {
 89:                 return '';
 90:             }
 91:             if (!in_array(strtoupper($this->operator), array('IN', 'NOT IN'))) {
 92:                 if ((substr($value, 0, 1) !== '`') && (substr($value, -1) !== '`')) {
 93:                     $value = "'{$value}'";
 94:                 } else {
 95:                     if (!preg_match('/^[a-zA-Z0-9_\.\-`]*$/', $value)) {
 96:                         $value = '``';
 97:                     }
 98:                 }
 99:             }
100:             $clause .= " {$this->operator} {$value}";
101:         }
102: 
103:         return $clause;
104:     }
105: 
106:     /**
107:      * Generate an LDAP filter from criteria
108:      *
109:      * @return string
110:      * @author Nathan Dial ndial@trillion21.com, improved by Pierre-Eric MENUET pemen@sourceforge.net
111:      */
112:     public function renderLdap()
113:     {
114:         $clause = '';
115:         if ($this->operator === '>') {
116:             $this->operator = '>=';
117:         }
118:         if ($this->operator === '<') {
119:             $this->operator = '<=';
120:         }
121: 
122:         if ($this->operator === '!=' || $this->operator === '<>') {
123:             $operator = '=';
124:             $clause = "(!(" . $this->column . $operator . $this->value . "))";
125:         } else {
126:             if ($this->operator === 'IN') {
127:                 $newvalue = str_replace(array('(', ')'), '', $this->value);
128:                 $tab = explode(',', $newvalue);
129:                 foreach ($tab as $uid) {
130:                     $clause .= "({$this->column}={$uid})";
131:                 }
132:                 $clause = '(|' . $clause . ')';
133:             } else {
134:                 $clause = "(" . $this->column . ' ' . $this->operator . ' ' . $this->value . ")";
135:             }
136:         }
137:         return $clause;
138:     }
139: 
140:     /**
141:      * Make a SQL "WHERE" clause
142:      *
143:      * @return string
144:      */
145:     public function renderWhere()
146:     {
147:         $cond = $this->render();
148:         return empty($cond) ? '' : "WHERE {$cond}";
149:     }
150: 
151:     /**
152:      * Render criteria as Doctrine QueryBuilder instructions
153:      *
154:      * @param QueryBuilder $qb        query builder instance
155:      * @param string       $whereMode how does this fit in the passed in QueryBuilder?
156:      *                                '' = as where,'and'= as andWhere, 'or' = as orWhere
157:      *
158:      * @return QueryBuilder query builder instance
159:      */
160:     public function renderQb(QueryBuilder $qb = null, $whereMode = '')
161:     {
162:         if ($qb==null) { // initialize query builder if not passed in
163:             $qb = \Xoops::getInstance()->db()->createXoopsQueryBuilder();
164:             $whereMode = ''; // first entry in new instance must be where
165:         }
166:         $expr = $this->buildExpressionQb($qb);
167: 
168:         switch (strtolower($whereMode)) {
169:             case 'and':
170:                 $qb->andWhere($expr);
171:                 break;
172:             case 'or':
173:                 $qb->orWhere($expr);
174:                 break;
175:             case '':
176:                 $qb->where($expr);
177:                 break;
178:         }
179: 
180:         if ($this->limit!=0 || $this->start!=0) {
181:             $qb->setFirstResult($this->start)
182:                 ->setMaxResults($this->limit);
183:         }
184: 
185:         if (!empty($this->groupBy)) {
186:             $qb->groupBy($this->groupBy);
187:         }
188: 
189:         if (!empty($this->sort)) {
190:             $qb->orderBy($this->sort, $this->order);
191:         }
192: 
193:         return $qb;
194:     }
195: 
196:     /**
197:      * Build an expression to be included in a Doctrine QueryBuilder instance.
198:      *
199:      * This method will build an expression, adding any parameters to the query,
200:      * but the caller is responsible for adding the expression to the query, for
201:      * example as where() parameter. This allows the caller to handle all context,
202:      * such as parenthetical groupings.
203:      *
204:      * @param QueryBuilder $qb query builder instance
205:      *
206:      * @return string expression
207:      */
208:     public function buildExpressionQb(QueryBuilder $qb)
209:     {
210:         $eb = $qb->expr();
211: 
212:         $column = (empty($this->prefix) ? "" : $this->prefix.'.') . $this->column;
213: 
214:         // this should be done using portability functions
215:         if (!empty($this->function)) {
216:             $column = sprintf($this->function, $column);
217:         }
218: 
219:         $value=trim($this->value);
220: 
221:         $operator = strtolower($this->operator);
222:         $expr = '';
223: 
224:         // handle special case of value
225:         if (in_array($operator, array('is null', 'is not null', 'in', 'not in'))) {
226:             switch ($operator) {
227:                 case 'is null':
228:                     $expr = $eb->isNull($column);
229:                     break;
230:                 case 'is not null':
231:                     $expr = $eb->isNotNull($column);
232:                     break;
233:                 case 'in':
234:                     if (!empty($value) && $value!=='()') {
235:                         $expr = $column . ' IN ' . $value;
236:                     } else {
237:                         // odd case of a null set - this won't match anything
238:                         $expr = $eb->neq($column, $column);
239:                     }
240:                     break;
241:                 case 'not in':
242:                     if (!empty($value) && $value!=='()') {
243:                         $expr = $column . ' NOT IN ' . $value;
244:                     }
245:                     break;
246:             }
247:         } elseif (!empty($column)) { // no value is a nop (bug: this should be a valid value)
248:             $columnValue = $qb->createNamedParameter($value);
249:             switch ($operator) {
250:                 case '=':
251:                 case 'eq':
252:                     $expr = $eb->eq($column, $columnValue);
253:                     break;
254:                 case '!=':
255:                 case '<>':
256:                 case 'neq':
257:                     $expr = $eb->neq($column, $columnValue);
258:                     break;
259:                 case '<':
260:                 case 'lt':
261:                     $expr = $eb->lt($column, $columnValue);
262:                     break;
263:                 case '<=':
264:                 case 'lte':
265:                     $expr = $eb->lte($column, $columnValue);
266:                     break;
267:                 case '>':
268:                 case 'gt':
269:                     $expr = $eb->gt($column, $columnValue);
270:                     break;
271:                 case '>=':
272:                 case 'gte':
273:                     $expr = $eb->gte($column, $columnValue);
274:                     break;
275:                 case 'like':
276:                     $expr = $eb->like($column, $columnValue);
277:                     break;
278:                 case 'not like':
279:                     $expr = $eb->notLike($column, $columnValue);
280:                     break;
281:                 default:
282:                     $expr = $eb->comparison($column, strtoupper($operator), $columnValue);
283:                     break;
284:             }
285:         } else {
286:             $expr = '(1)';
287:         }
288:         return $expr;
289:     }
290: }
291: