/**
* Generic Function to add where to a request
*
* @param $link link string
* @param $nott is it a negative search ?
* @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) (default 0)
*
* @return select string
**/
static function addWhere($link, $nott, $itemtype, $ID, $searchtype, $val, $meta = 0)
{
$searchopt =& self::getOptions($itemtype);
$table = $searchopt[$ID]["table"];
$field = $searchopt[$ID]["field"];
$inittable = $table;
$addtable = '';
if ($table != 'asset_types' && $table != getTableForItemType($itemtype) && $searchopt[$ID]["linkfield"] != getForeignKeyFieldForTable($table)) {
$addtable = "_" . $searchopt[$ID]["linkfield"];
$table .= $addtable;
}
if (isset($searchopt[$ID]['joinparams'])) {
$complexjoin = self::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":
$force_day = true;
if ($searchopt[$ID]["datatype"] == 'datetime') {
$force_day = false;
}
if (strstr($val, 'BEGIN') || strstr($val, 'LAST')) {
$force_day = true;
}
$val = Html::computeGenericDateTimeSearch($val, $force_day);
break;
}
}
switch ($searchtype) {
case "contains":
$SEARCH = self::makeTextSearch($val, $nott);
break;
case "equals":
if ($nott) {
$SEARCH = " <> '{$val}'";
} else {
$SEARCH = " = '{$val}'";
}
break;
case "notequals":
if ($nott) {
$SEARCH = " = '{$val}'";
} else {
$SEARCH = " <> '{$val}'";
}
break;
case "under":
if ($nott) {
$SEARCH = " NOT IN ('" . implode("','", getSonsOf($inittable, $val)) . "')";
} else {
$SEARCH = " IN ('" . implode("','", getSonsOf($inittable, $val)) . "')";
}
break;
case "notunder":
if ($nott) {
$SEARCH = " IN ('" . implode("','", getSonsOf($inittable, $val)) . "')";
} else {
$SEARCH = " NOT IN ('" . implode("','", getSonsOf($inittable, $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, $searchtype);
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 self::makeTextCriteria("`{$table}`.`{$field}`", $val, $nott, $link);
}
if ($_SESSION["glpinames_format"] == User::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" : '') . ') ';
}
$toadd = '';
$tmplink = 'OR';
if ($nott) {
$tmplink = 'AND';
}
if ($itemtype == 'Ticket' || $itemtype == 'Problem') {
if (isset($searchopt[$ID]["joinparams"]["beforejoin"]["table"]) && isset($searchopt[$ID]["joinparams"]["beforejoin"]["joinparams"]) && ($searchopt[$ID]["joinparams"]["beforejoin"]["table"] == 'glpi_tickets_users' || $searchopt[$ID]["joinparams"]["beforejoin"]["table"] == 'glpi_problems_users' || $searchopt[$ID]["joinparams"]["beforejoin"]["table"] == 'glpi_changes_users')) {
$bj = $searchopt[$ID]["joinparams"]["beforejoin"];
$linktable = $bj['table'] . '_' . self::computeComplexJoinID($bj['joinparams']);
//$toadd = "`$linktable`.`alternative_email` $SEARCH $tmplink ";
$toadd = self::makeTextCriteria("`{$linktable}`.`alternative_email`", $val, $nott, $tmplink);
}
}
$toadd2 = '';
if ($nott && $val != 'NULL' && $val != 'null') {
$toadd2 = " OR `{$table}`.`{$field}` IS NULL";
}
return $link . " (((`{$table}`.`{$name1}` {$SEARCH}\n {$tmplink} `{$table}`.`{$name2}` {$SEARCH}\n {$tmplink} `{$table}`.`{$field}` {$SEARCH}\n {$tmplink} CONCAT(`{$table}`.`{$name1}`, ' ', `{$table}`.`{$name2}`) {$SEARCH} )\n {$toadd2}) {$toadd})";
case "glpi_groups.completename":
if ($val == 'mygroups') {
switch ($searchtype) {
case 'equals':
return " {$link} (`{$table}`.`id` IN ('" . implode("','", $_SESSION['glpigroups']) . "')) ";
case 'notequals':
return " {$link} (`{$table}`.`id` NOT IN ('" . implode("','", $_SESSION['glpigroups']) . "')) ";
case 'under':
$groups = $_SESSION['glpigroups'];
foreach ($_SESSION['glpigroups'] as $g) {
$groups += getSonsOf($inittable, $g);
}
$groups = array_unique($groups);
return " {$link} (`{$table}`.`id` IN ('" . implode("','", $groups) . "')) ";
case 'notunder':
$groups = $_SESSION['glpigroups'];
foreach ($_SESSION['glpigroups'] as $g) {
$groups += getSonsOf($inittable, $g);
}
$groups = array_unique($groups);
return " {$link} (`{$table}`.`id` NOT IN ('" . implode("','", $groups) . "')) ";
}
}
break;
case "glpi_auth_tables.name":
$user_searchopt = self::getOptions('User');
$tmplink = 'OR';
if ($nott) {
$tmplink = 'AND';
}
return $link . " (`glpi_authmails" . $addtable . "_" . self::computeComplexJoinID($user_searchopt[31]['joinparams']) . "`.`name`\n {$SEARCH}\n {$tmplink} `glpi_authldaps" . $addtable . "_" . self::computeComplexJoinID($user_searchopt[30]['joinparams']) . "`.`name`\n {$SEARCH} ) ";
case "glpi_ipaddresses.name":
$search = array("/\\</", "/\\>/");
$replace = array("<", ">");
$val = preg_replace($search, $replace, $val);
if (preg_match("/^\\s*([<>])([=]*)[[:space:]]*([0-9\\.]+)/", $val, $regs)) {
if ($nott) {
if ($regs[1] == '<') {
$regs[1] = '>';
} else {
$regs[1] = '<';
}
}
$regs[1] .= $regs[2];
return $link . " (INET_ATON(`{$table}`.`{$field}`) " . $regs[1] . " INET_ATON('" . $regs[3] . "')) ";
}
break;
case "glpi_tickets.status":
case "glpi_problems.status":
case "glpi_changes.status":
if ($val == 'all') {
return "";
}
$tocheck = array();
if ($item = getItemForItemtype($itemtype)) {
switch ($val) {
case 'process':
$tocheck = $item->getProcessStatusArray();
break;
case 'notclosed':
$tocheck = $item->getAllStatusArray();
foreach ($item->getClosedStatusArray() as $status) {
if (isset($tocheck[$status])) {
unset($tocheck[$status]);
}
}
$tocheck = array_keys($tocheck);
break;
case 'old':
$tocheck = array_merge($item->getSolvedStatusArray(), $item->getClosedStatusArray());
break;
case 'notold':
$tocheck = $item->getAllStatusArray();
foreach ($item->getSolvedStatusArray() as $status) {
if (isset($tocheck[$status])) {
unset($tocheck[$status]);
}
}
foreach ($item->getClosedStatusArray() as $status) {
if (isset($tocheck[$status])) {
unset($tocheck[$status]);
}
}
$tocheck = array_keys($tocheck);
break;
}
}
if (count($tocheck) == 0) {
$statuses = $item->getAllStatusArray();
if (isset($statuses[$val])) {
$tocheck = array($val);
}
}
if (count($tocheck)) {
if ($nott) {
return $link . " `{$table}`.`{$field}` NOT IN ('" . implode("','", $tocheck) . "')";
}
return $link . " `{$table}`.`{$field}` IN ('" . implode("','", $tocheck) . "')";
}
break;
case "glpi_tickets_tickets.tickets_id_1":
$tmplink = 'OR';
$compare = '=';
if ($nott) {
$tmplink = 'AND';
$compare = '<>';
}
$toadd2 = '';
if ($nott && $val != 'NULL' && $val != 'null') {
$toadd2 = " OR `{$table}`.`{$field}` IS NULL";
}
return $link . " (((`{$table}`.`tickets_id_1` {$compare} '{$val}'\n {$tmplink} `{$table}`.`tickets_id_2` {$compare} '{$val}')\n AND `glpi_tickets`.`id` <> '{$val}')\n {$toadd2})";
case "glpi_tickets.priority":
case "glpi_tickets.impact":
case "glpi_tickets.urgency":
case "glpi_problems.priority":
case "glpi_problems.impact":
case "glpi_problems.urgency":
case "glpi_changes.priority":
case "glpi_changes.impact":
case "glpi_changes.urgency":
case "glpi_projects.priority":
if (is_numeric($val)) {
if ($val > 0) {
return $link . " `{$table}`.`{$field}` = '{$val}'";
}
if ($val < 0) {
return $link . " `{$table}`.`{$field}` >= '" . abs($val) . "'";
}
// Show all
return $link . " `{$table}`.`{$field}` >= '0' ";
}
return "";
case "glpi_tickets.global_validation":
case "glpi_ticketvalidations.status":
if ($val == 'all') {
return "";
}
$tocheck = array();
switch ($val) {
case 'can':
$tocheck = CommonITILValidation::getCanValidationStatusArray();
break;
case 'all':
$tocheck = CommonITILValidation::getAllValidationStatusArray();
break;
}
if (count($tocheck) == 0) {
$tocheck = array($val);
}
if (count($tocheck)) {
if ($nott) {
return $link . " `{$table}`.`{$field}` NOT IN ('" . implode("','", $tocheck) . "')";
}
return $link . " `{$table}`.`{$field}` IN ('" . implode("','", $tocheck) . "')";
}
break;
}
//// 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, $searchtype);
if (!empty($out)) {
return $out;
}
}
}
}
$tocompute = "`{$table}`.`{$field}`";
$tocomputetrans = "`" . $table . "_" . $field . "_trans`.`value`";
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 . ') ';
}
break;
case "itemlink":
if (in_array($searchtype, array('equals', 'notequals', 'under', 'notunder'))) {
return " {$link} (`{$table}`.`id`" . $SEARCH . ') ';
}
break;
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 self::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'];
}
$add_minus = '';
if (isset($searchopt[$ID]["datafields"][3])) {
$add_minus = "-`{$table}`.`" . $searchopt[$ID]["datafields"][3] . "`";
}
$date_computation = "ADDDATE(`{$table}`." . $searchopt[$ID]["datafields"][1] . ",\n INTERVAL (`{$table}`." . $searchopt[$ID]["datafields"][2] . "\n {$add_minus})\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 self::makeTextCriteria($date_computation, $val, $nott, $link);
case "right":
if ($searchtype == 'notequals') {
$nott = !$nott;
}
return $link . ($nott ? ' NOT' : '') . " ({$tocompute} & '{$val}') ";
case "bool":
if (!is_numeric($val)) {
if (strcasecmp($val, __('No')) == 0) {
$val = 0;
} else {
if (strcasecmp($val, __('Yes')) == 0) {
$val = 1;
}
}
}
if ($searchtype == 'notequals') {
$nott = !$nott;
}
// No break here : use number comparaison case
// No break here : use number comparaison case
case "count":
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', 'under', 'notunder'))) {
if ((!isset($searchopt[$ID]['searchequalsonfield']) || !$searchopt[$ID]['searchequalsonfield']) && ($table != getTableForItemType($itemtype) || $itemtype == 'AllAssets')) {
$out = " {$link} (`{$table}`.`id`" . $SEARCH;
} else {
$out = " {$link} (`{$table}`.`{$field}`" . $SEARCH;
}
if ($searchtype == 'notequals') {
$nott = !$nott;
}
// Add NULL if $val = 0 and not negative search
// Or negative search on real value
if (!$nott && $val == 0 || $nott && $val != 0) {
$out .= " OR `{$table}`.`id` IS NULL";
}
$out .= ')';
return $out;
}
$transitemtype = getItemTypeForTable($inittable);
if (Session::haveTranslations($transitemtype, $field)) {
return " {$link} (" . self::makeTextCriteria($tocompute, $val, $nott, '') . "\n OR " . self::makeTextCriteria($tocomputetrans, $val, $nott, '') . ")";
}
return self::makeTextCriteria($tocompute, $val, $nott, $link);
}