function plugin_projet_addSelect($type, $ID, $num) { $searchopt =& Search::getOptions($type); $table = $searchopt[$ID]["table"]; $field = $searchopt[$ID]["field"]; $addtable = ""; $NAME = "ITEM"; $complexjoin = ''; if (isset($searchopt[$ID]['joinparams'])) { $complexjoin = Search::computeComplexJoinID($searchopt[$ID]['joinparams']); } if (($table != getTableForItemType($type) || !empty($complexjoin)) && $searchopt[$ID]["linkfield"] != getForeignKeyFieldForTable($table)) { $addtable .= "_" . $searchopt[$ID]["linkfield"]; } if (!empty($complexjoin)) { $addtable .= "_" . $complexjoin; } // Example of standard Select clause but use it ONLY for specific Select // No need of the function if you do not have specific cases switch ($type) { case 'PluginProjetProjet': switch ($table . "." . $field) { case "glpi_plugin_projet_projetstates.name": return "`" . $table . "`.`" . $field . "` AS ITEM_{$num}, `" . $table . "`.`id` AS ITEM_" . $num . "_2, "; break; case "glpi_plugin_projet_projets_projets.plugin_projet_projets_id_1": return " GROUP_CONCAT(`{$table}{$addtable}`.`plugin_projet_projets_id_1` SEPARATOR '\$\$\$\$')\n AS " . $NAME . "_{$num},\n GROUP_CONCAT(`{$table}{$addtable}`.`plugin_projet_projets_id_2` SEPARATOR '\$\$\$\$')\n AS " . $NAME . "_" . $num . "_2, "; break; } return ""; break; case 'PluginProjetProjetState': switch ($table . "." . $field) { case "glpi_plugin_projet_projetstates.color": return "`" . $table . "`.`" . $field . "` AS ITEM_{$num}, `" . $table . "`.`id` AS ITEM_" . $num . "_2, "; break; } return ""; break; case 'PluginProjetTask': switch ($table . "." . $field) { case "glpi_plugin_projet_tasks_tasks.plugin_projet_tasks_id_1": return " GROUP_CONCAT(`{$table}{$addtable}`.`plugin_projet_tasks_id_1` SEPARATOR '\$\$\$\$')\n AS " . $NAME . "_{$num},\n GROUP_CONCAT(`{$table}{$addtable}`.`plugin_projet_tasks_id_2` SEPARATOR '\$\$\$\$')\n AS " . $NAME . "_" . $num . "_2, "; break; case "glpi_contacts.name": return "`" . $table . "`.`id` AS ITEM_{$num}, `" . $table . "`.`name` AS contacts_name,\n `" . $table . "`.`firstname` AS contacts_firstname, "; break; case "glpi_plugin_projet_taskstates.name": return "`" . $table . "`.`" . $field . "` AS ITEM_{$num}, `" . $table . "`.`id` AS ITEM_" . $num . "_2, "; break; } return ""; break; case 'PluginProjetTaskState': switch ($table . "." . $field) { case "glpi_plugin_projet_taskstates.color": return "`" . $table . "`.`" . $field . "` AS ITEM_{$num}, `" . $table . "`.`id` AS ITEM_" . $num . "_2, "; break; } return ""; break; } 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); }
/** * Generic Function to add ORDER BY to a request * *@param $itemtype ID of the device type *@param $ID field to add *@param $order order define *@param $key item number * *@return select string * **/ static function addOrderBy($itemtype, $ID, $order, $key = 0) { global $CFG_GLPI; // Security test for order if ($order != "ASC") { $order = "DESC"; } $searchopt =& Search::getOptions($itemtype); $table = $searchopt[$ID]["table"]; $field = $searchopt[$ID]["field"]; $addtable = ''; if ($table != getTableForItemType($itemtype) && $searchopt[$ID]["linkfield"] != getForeignKeyFieldForTable($table)) { $addtable .= "_" . $searchopt[$ID]["linkfield"]; } if (isset($searchopt[$ID]['joinparams'])) { $complexjoin = Search::computeComplexJoinID($searchopt[$ID]['joinparams']); if (!empty($complexjoin)) { $addtable .= "_" . $complexjoin; } } if (isset($CFG_GLPI["union_search_type"][$itemtype])) { return " ORDER BY ITEM_{$key} {$order} "; } return " ORDER BY {$table}.{$field} {$order} "; }