Create SQL search condition
| static public makeTextCriteria ( $field, $val, $not = false, $link = 'AND' ) : search | ||
| $field | name (should be ` protected) | |
| $val | string value to search | |
| $not | boolean is a negative search ? (false by default) | |
| $link | with previous criteria (default 'AND') | |
| return | search | SQL string |
function getSqlCriteriasRestriction($link = 'AND')
{
$param = $this->getParameterValue();
if ($param) {
return Search::makeTextCriteria($this->getSqlField(), $param, false, $link);
}
return '';
}
/**
* Generic Function to add where to a request
*
* @param $link link string
* @param $nott is it a negative serach ?
* @param $itemtype item type
* @param $ID ID of the item to search
* @param $searchtype searchtype used (equals or contains)
* @param $val item num in the request
* @param $meta is a meta search (meta=2 in search.class.php)
*
* @return select string
**/
static function addWhere($link, $nott, $itemtype, $ID, $searchtype, $val, $meta = 0)
{
$searchopt =& Search::getOptions($itemtype);
$table = $searchopt[$ID]["table"];
$field = $searchopt[$ID]["field"];
$inittable = $table;
$addtable = '';
/*if ($table != getTableForItemType($itemtype)
&& $searchopt[$ID]["linkfield"] != getForeignKeyFieldForTable($table)) {
$addtable = "_".$searchopt[$ID]["linkfield"];
$table .= $addtable;
}*/
if (isset($searchopt[$ID]['joinparams'])) {
$complexjoin = Search::computeComplexJoinID($searchopt[$ID]['joinparams']);
if (!empty($complexjoin)) {
$table .= "_" . $complexjoin;
}
}
if ($meta && getTableForItemType($itemtype) != $table) {
$table .= "_" . $itemtype;
}
// Hack to allow search by ID on every sub-table
if (preg_match('/^\\$\\$\\$\\$([0-9]+)$/', $val, $regs)) {
return $link . " (`{$table}`.`id` " . ($nott ? "<>" : "=") . $regs[1] . " " . ($regs[1] == 0 ? " OR `{$table}`.`id` IS NULL" : '') . ") ";
}
// Preparse value
if (isset($searchopt[$ID]["datatype"])) {
switch ($searchopt[$ID]["datatype"]) {
case "datetime":
case "date":
case "date_delay":
$format_use = "Y-m-d";
if ($searchopt[$ID]["datatype"] == 'datetime') {
$format_use = "Y-m-d H:i:s";
}
// Parsing relative date
if ($val == 'NOW') {
$val = date($format_use);
}
if (preg_match("/^(-?)(\\d+)(\\w+)\$/", $val, $matches)) {
if (in_array($matches[3], array('YEAR', 'MONTH', 'WEEK', 'DAY', 'HOUR'))) {
$nb = intval($matches[2]);
if ($matches[1] == '-') {
$nb = -$nb;
}
// Use it to have a clean delay computation (MONTH / YEAR have not always the same duration)
$hour = date("H");
$minute = date("i");
$second = 0;
$month = date("n");
$day = date("j");
$year = date("Y");
switch ($matches[3]) {
case "YEAR":
$year += $nb;
break;
case "MONTH":
$month += $nb;
break;
case "WEEK":
$day += 7 * $nb;
break;
case "DAY":
$day += $nb;
break;
case "HOUR":
$hour += $nb;
break;
}
$val = date($format_use, mktime($hour, $minute, $second, $month, $day, $year));
}
}
break;
}
}
switch ($searchtype) {
case "contains":
$SEARCH = Search::makeTextSearch($val, $nott);
break;
case "equals":
if ($nott) {
$SEARCH = " <> '{$val}'";
} else {
$SEARCH = " = '{$val}'";
}
break;
case "notequals":
if ($nott) {
$SEARCH = " = '{$val}'";
} else {
$SEARCH = " <> '{$val}'";
}
break;
}
// Plugin can override core definition for its type
if ($plug = isPluginItemType($itemtype)) {
$function = 'plugin_' . $plug['plugin'] . '_addWhere';
if (function_exists($function)) {
$out = $function($link, $nott, $itemtype, $ID, $val);
if (!empty($out)) {
return $out;
}
}
}
switch ($inittable . "." . $field) {
// case "glpi_users_validation.name" :
case "glpi_users.name":
if ($itemtype == 'User') {
// glpi_users case / not link table
if (in_array($searchtype, array('equals', 'notequals'))) {
return " {$link} `{$table}`.`id`" . $SEARCH;
}
return Search::makeTextCriteria("`{$table}`.`{$field}`", $val, $nott, $link);
}
if ($_SESSION["glpinames_format"] == FIRSTNAME_BEFORE) {
$name1 = 'firstname';
$name2 = 'realname';
} else {
$name1 = 'realname';
$name2 = 'firstname';
}
if (in_array($searchtype, array('equals', 'notequals'))) {
return " {$link} (`{$table}`.`id`" . $SEARCH . ($val == 0 ? " OR `{$table}`.`id` IS NULL" : '') . ') ';
}
return $link . " (`{$table}`.`{$name1}` {$SEARCH}\n OR `{$table}`.`{$name2}` {$SEARCH}\n OR CONCAT(`{$table}`.`{$name1}`, ' ',\n `{$table}`.`{$name2}`) {$SEARCH}" . Search::makeTextCriteria("`{$table}`.`{$field}`", $val, $nott, 'OR') . ") ";
case "glpi_groups.name":
$linkfield = "";
if (in_array($searchtype, array('equals', 'notequals'))) {
return " {$link} (`{$table}`.`id`" . $SEARCH . ($val == 0 ? " OR `{$table}`.`id` IS NULL" : '') . ') ';
}
return Search::makeTextCriteria("`{$table}`.`{$field}`", $val, $nott, $link);
}
//// Default cases
// Link with plugin tables
if (preg_match("/^glpi_plugin_([a-z0-9]+)/", $inittable, $matches)) {
if (count($matches) == 2) {
$plug = $matches[1];
$function = 'plugin_' . $plug . '_addWhere';
if (function_exists($function)) {
$out = $function($link, $nott, $itemtype, $ID, $val);
if (!empty($out)) {
return $out;
}
}
}
}
$tocompute = "`{$table}`.`{$field}`";
if (isset($searchopt[$ID]["computation"])) {
$tocompute = $searchopt[$ID]["computation"];
$tocompute = str_replace("TABLE", "`{$table}`", $tocompute);
}
// Preformat items
if (isset($searchopt[$ID]["datatype"])) {
switch ($searchopt[$ID]["datatype"]) {
case "itemtypename":
if (in_array($searchtype, array('equals', 'notequals'))) {
return " {$link} (`{$table}`.`{$field}`" . $SEARCH . ') ';
}
case "datetime":
case "date":
case "date_delay":
if ($searchopt[$ID]["datatype"] == 'datetime') {
// Specific search for datetime
if (in_array($searchtype, array('equals', 'notequals'))) {
$val = preg_replace("/:00\$/", '', $val);
$val = '^' . $val;
if ($searchtype == 'notequals') {
$nott = !$nott;
}
return Search::makeTextCriteria("`{$table}`.`{$field}`", $val, $nott, $link);
}
}
if ($searchtype == 'lessthan') {
$val = '<' . $val;
}
if ($searchtype == 'morethan') {
$val = '>' . $val;
}
if ($searchtype) {
$date_computation = $tocompute;
}
$search_unit = ' MONTH ';
if (isset($searchopt[$ID]['searchunit'])) {
$search_unit = $searchopt[$ID]['searchunit'];
}
if ($searchopt[$ID]["datatype"] == "date_delay") {
$delay_unit = ' MONTH ';
if (isset($searchopt[$ID]['delayunit'])) {
$delay_unit = $searchopt[$ID]['delayunit'];
}
$date_computation = "ADDDATE(`{$table}`." . $searchopt[$ID]["datafields"][1] . ",\n INTERVAL `{$table}`." . $searchopt[$ID]["datafields"][2] . "\n {$delay_unit})";
}
if (in_array($searchtype, array('equals', 'notequals'))) {
return " {$link} ({$date_computation} " . $SEARCH . ') ';
}
$search = array("/\\</", "/\\>/");
$replace = array("<", ">");
$val = preg_replace($search, $replace, $val);
if (preg_match("/^\\s*([<>=]+)(.*)/", $val, $regs)) {
if (is_numeric($regs[2])) {
return $link . " {$date_computation} " . $regs[1] . "\n ADDDATE(NOW(), INTERVAL " . $regs[2] . " {$search_unit}) ";
}
// ELSE Reformat date if needed
$regs[2] = preg_replace('@(\\d{1,2})(-|/)(\\d{1,2})(-|/)(\\d{4})@', '\\5-\\3-\\1', $regs[2]);
if (preg_match('/[0-9]{2,4}-[0-9]{1,2}-[0-9]{1,2}/', $regs[2])) {
return $link . " {$date_computation} " . $regs[1] . " '" . $regs[2] . "'";
}
return "";
}
// ELSE standard search
// Date format modification if needed
$val = preg_replace('@(\\d{1,2})(-|/)(\\d{1,2})(-|/)(\\d{4})@', '\\5-\\3-\\1', $val);
return Search::makeTextCriteria($date_computation, $val, $nott, $link);
case "right":
if ($val == 'NULL' || $val == 'null') {
return $link . " {$tocompute} IS " . ($nott ? 'NOT' : '') . " NULL ";
}
return $link . " {$tocompute} = '{$val}' ";
case "bool":
if (!is_numeric($val)) {
if (strcasecmp($val, __('No')) == 0) {
$val = 0;
} else {
if (strcasecmp($val, __('Yes')) == 0) {
$val = 1;
}
}
}
// No break here : use number comparaison case
// No break here : use number comparaison case
case "number":
case "decimal":
case "timestamp":
$search = array("/\\</", "/\\>/");
$replace = array("<", ">");
$val = preg_replace($search, $replace, $val);
if (preg_match("/([<>])([=]*)[[:space:]]*([0-9]+)/", $val, $regs)) {
if ($nott) {
if ($regs[1] == '<') {
$regs[1] = '>';
} else {
$regs[1] = '<';
}
}
$regs[1] .= $regs[2];
return $link . " ({$tocompute} " . $regs[1] . " " . $regs[3] . ") ";
}
if (is_numeric($val)) {
if (isset($searchopt[$ID]["width"])) {
$ADD = "";
if ($nott && $val != 'NULL' && $val != 'null') {
$ADD = " OR {$tocompute} IS NULL";
}
if ($nott) {
return $link . " ({$tocompute} < " . (intval($val) - $searchopt[$ID]["width"]) . "\n OR {$tocompute} > " . (intval($val) + $searchopt[$ID]["width"]) . "\n {$ADD}) ";
}
return $link . " (({$tocompute} >= " . (intval($val) - $searchopt[$ID]["width"]) . "\n AND {$tocompute} <= " . (intval($val) + $searchopt[$ID]["width"]) . ")\n {$ADD}) ";
}
if (!$nott) {
return " {$link} ({$tocompute} = " . intval($val) . ") ";
}
return " {$link} ({$tocompute} <> " . intval($val) . ") ";
}
break;
}
}
// Default case
if (in_array($searchtype, array('equals', 'notequals'))) {
$out = " {$link} (`{$table}`.`id`" . $SEARCH;
if ($searchtype == 'notequals') {
$nott = !$nott;
}
// Add NULL if $val = 0 and not negative search
if (!$nott && $val == 0) {
$out .= " OR `{$table}`.`id` IS NULL";
}
$out .= ')';
return $out;
}
return Search::makeTextCriteria($tocompute, $val, $nott, $link);
}
