static function addSelect($itemtype, $ID, $num, $meta = 0, $meta_type = 0)
{
global $CFG_GLPI;
$searchopt =& self::getOptions($itemtype);
$table = $searchopt[$ID]["table"];
$field = $searchopt[$ID]["field"];
$addtable = "";
$addtable2 = "";
$NAME = "ITEM";
$complexjoin = '';
if (isset($searchopt[$ID]['joinparams'])) {
$complexjoin = self::computeComplexJoinID($searchopt[$ID]['joinparams']);
}
if (($table != getTableForItemType($itemtype) && (!isset($CFG_GLPI["union_search_type"][$itemtype]) || $CFG_GLPI["union_search_type"][$itemtype] != $table) || !empty($complexjoin)) && $searchopt[$ID]["linkfield"] != getForeignKeyFieldForTable($table)) {
$addtable .= "_" . $searchopt[$ID]["linkfield"];
}
if (!empty($complexjoin)) {
$addtable .= "_" . $complexjoin;
$addtable2 .= "_" . $complexjoin;
}
if ($meta) {
// $NAME = "META";
if (getTableForItemType($meta_type) != $table) {
$addtable .= "_" . $meta_type;
$addtable2 .= "_" . $meta_type;
}
}
// Plugin can override core definition for its type
if ($plug = isPluginItemType($itemtype)) {
$function = 'plugin_' . $plug['plugin'] . '_addSelect';
if (function_exists($function)) {
$out = $function($itemtype, $ID, $num);
if (!empty($out)) {
return $out;
}
}
}
$tocompute = "`{$table}{$addtable}`.`{$field}`";
$tocomputeid = "`{$table}{$addtable}`.`id`";
$tocomputetrans = "IFNULL(`{$table}" . $addtable . "_trans`.`value`,'" . self::NULLVALUE . "') ";
$ADDITONALFIELDS = '';
if (isset($searchopt[$ID]["additionalfields"]) && count($searchopt[$ID]["additionalfields"])) {
foreach ($searchopt[$ID]["additionalfields"] as $key) {
if ($meta || isset($searchopt[$ID]["forcegroupby"]) && $searchopt[$ID]["forcegroupby"]) {
$ADDITONALFIELDS .= " IFNULL(GROUP_CONCAT(DISTINCT CONCAT(IFNULL(`{$table}{$addtable}`.`{$key}`,\n '" . self::NULLVALUE . "'),\n '" . self::SHORTSEP . "', {$tocomputeid}) SEPARATOR '" . self::LONGSEP . "'), '" . self::NULLVALUE . self::SHORTSEP . "')\n AS `" . $NAME . "_" . $num . "_{$key}`, ";
} else {
$ADDITONALFIELDS .= "`{$table}{$addtable}`.`{$key}` AS `" . $NAME . "_" . $num . "_{$key}`, ";
}
}
}
// Virtual display no select : only get additional fields
if (strpos($field, '_virtual') === 0) {
return $ADDITONALFIELDS;
}
switch ($table . "." . $field) {
case "glpi_users.name":
if ($itemtype != 'User') {
if (isset($searchopt[$ID]["forcegroupby"]) && $searchopt[$ID]["forcegroupby"]) {
$addaltemail = "";
if (($itemtype == 'Ticket' || $itemtype == 'Problem') && isset($searchopt[$ID]['joinparams']['beforejoin']['table']) && ($searchopt[$ID]['joinparams']['beforejoin']['table'] == 'glpi_tickets_users' || $searchopt[$ID]['joinparams']['beforejoin']['table'] == 'glpi_problems_users' || $searchopt[$ID]['joinparams']['beforejoin']['table'] == 'glpi_changes_users')) {
// For tickets_users
$ticket_user_table = $searchopt[$ID]['joinparams']['beforejoin']['table'] . "_" . self::computeComplexJoinID($searchopt[$ID]['joinparams']['beforejoin']['joinparams']);
$addaltemail = "GROUP_CONCAT(DISTINCT CONCAT(`{$ticket_user_table}`.`users_id`, ' ',\n `{$ticket_user_table}`.`alternative_email`)\n SEPARATOR '" . self::LONGSEP . "') AS `" . $NAME . "_" . $num . "_2`, ";
}
return " GROUP_CONCAT(DISTINCT `{$table}{$addtable}`.`id` SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "`,\n {$addaltemail}\n {$ADDITONALFIELDS}";
}
return " `{$table}{$addtable}`.`{$field}` AS `" . $NAME . "_{$num}`,\n `{$table}{$addtable}`.`realname` AS `" . $NAME . "_" . $num . "_realname`,\n `{$table}{$addtable}`.`id` AS `" . $NAME . "_" . $num . "_id`,\n `{$table}{$addtable}`.`firstname` AS `" . $NAME . "_" . $num . "_firstname`,\n {$ADDITONALFIELDS}";
}
break;
case "glpi_softwarelicenses.number":
if ($meta) {
return " FLOOR(SUM(`{$table}{$addtable2}`.`{$field}`)\n * COUNT(DISTINCT `{$table}{$addtable2}`.`id`)\n / COUNT(`{$table}{$addtable2}`.`id`)) AS `" . $NAME . "_" . $num . "`,\n MIN(`{$table}{$addtable2}`.`{$field}`) AS `" . $NAME . "_" . $num . "_min`,\n {$ADDITONALFIELDS}";
} else {
return " FLOOR(SUM(`{$table}{$addtable}`.`{$field}`)\n * COUNT(DISTINCT `{$table}{$addtable}`.`id`)\n / COUNT(`{$table}{$addtable}`.`id`)) AS `" . $NAME . "_" . $num . "`,\n MIN(`{$table}{$addtable}`.`{$field}`) AS `" . $NAME . "_" . $num . "_min`,\n {$ADDITONALFIELDS}";
}
case "glpi_profiles.name":
if ($itemtype == 'User' && $ID == 20) {
$addtable2 = '';
if ($meta) {
$addtable2 = "_" . $meta_type;
}
return " GROUP_CONCAT(`{$table}{$addtable}`.`{$field}` SEPARATOR '" . self::LONGSEP . "') AS `" . $NAME . "_{$num}`,\n GROUP_CONCAT(`glpi_profiles_users{$addtable2}`.`entities_id` SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "_entities_id`,\n GROUP_CONCAT(`glpi_profiles_users{$addtable2}`.`is_recursive` SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "_is_recursive`,\n GROUP_CONCAT(`glpi_profiles_users{$addtable2}`.`is_dynamic` SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "_is_dynamic`,\n {$ADDITONALFIELDS}";
}
break;
case "glpi_entities.completename":
if ($itemtype == 'User' && $ID == 80) {
$addtable2 = '';
if ($meta) {
$addtable2 = "_" . $meta_type;
}
return " GROUP_CONCAT(`{$table}{$addtable}`.`completename` SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_{$num}`,\n GROUP_CONCAT(`glpi_profiles_users{$addtable2}`.`profiles_id` SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "_profiles_id`,\n GROUP_CONCAT(`glpi_profiles_users{$addtable2}`.`is_recursive` SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "_is_recursive`,\n GROUP_CONCAT(`glpi_profiles_users{$addtable2}`.`is_dynamic` SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "_is_dynamic`,\n {$ADDITONALFIELDS}";
}
break;
case "glpi_auth_tables.name":
$user_searchopt = self::getOptions('User');
return " `glpi_users`.`authtype` AS `" . $NAME . "_" . $num . "`,\n `glpi_users`.`auths_id` AS `" . $NAME . "_" . $num . "_auths_id`,\n `glpi_authldaps" . $addtable . "_" . self::computeComplexJoinID($user_searchopt[30]['joinparams']) . "`.`{$field}`\n AS `" . $NAME . "_" . $num . "_ldapname`,\n `glpi_authmails" . $addtable . "_" . self::computeComplexJoinID($user_searchopt[31]['joinparams']) . "`.`{$field}`\n AS `" . $NAME . "_" . $num . "_mailname`,\n {$ADDITONALFIELDS}";
case "glpi_softwarelicenses.name":
case "glpi_softwareversions.name":
if ($meta) {
return " GROUP_CONCAT(DISTINCT CONCAT(`glpi_softwares`.`name`, ' - ',\n `{$table}{$addtable2}`.`{$field}`, '" . self::SHORTSEP . "',\n `{$table}{$addtable2}`.`id`) SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "`,\n {$ADDITONALFIELDS}";
}
break;
case "glpi_softwarelicenses.serial":
case "glpi_softwarelicenses.otherserial":
case "glpi_softwarelicenses.comment":
case "glpi_softwareversions.comment":
if ($meta) {
return " GROUP_CONCAT(DISTINCT CONCAT(`glpi_softwares`.`name`, ' - ',\n `{$table}{$addtable2}`.`{$field}`,'" . self::SHORTSEP . "',\n `{$table}{$addtable2}`.`id`) SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "`,\n {$ADDITONALFIELDS}";
}
return " GROUP_CONCAT(DISTINCT CONCAT(`{$table}{$addtable}`.`name`, ' - ',\n `{$table}{$addtable}`.`{$field}`, '" . self::SHORTSEP . "',\n `{$table}{$addtable}`.`id`) SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "`,\n {$ADDITONALFIELDS}";
case "glpi_states.name":
if ($meta && $meta_type == 'Software') {
return " GROUP_CONCAT(DISTINCT CONCAT(`glpi_softwares`.`name`, ' - ',\n `glpi_softwareversions{$addtable}`.`name`, ' - ',\n `{$table}{$addtable2}`.`{$field}`, '" . self::SHORTSEP . "',\n `{$table}{$addtable2}`.`id`) SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "`,\n {$ADDITONALFIELDS}";
} else {
if ($itemtype == 'Software') {
return " GROUP_CONCAT(DISTINCT CONCAT(`glpi_softwareversions`.`name`, ' - ',\n `{$table}{$addtable}`.`{$field}`,'" . self::SHORTSEP . "',\n `{$table}{$addtable}`.`id`) SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "`,\n {$ADDITONALFIELDS}";
}
}
break;
}
//// Default cases
// Link with plugin tables
if (preg_match("/^glpi_plugin_([a-z0-9]+)/", $table, $matches)) {
if (count($matches) == 2) {
$plug = $matches[1];
$function = 'plugin_' . $plug . '_addSelect';
if (function_exists($function)) {
$out = $function($itemtype, $ID, $num);
if (!empty($out)) {
return $out;
}
}
}
}
if (isset($searchopt[$ID]["computation"])) {
$tocompute = $searchopt[$ID]["computation"];
$tocompute = str_replace("TABLE", "`{$table}{$addtable}`", $tocompute);
}
// Preformat items
if (isset($searchopt[$ID]["datatype"])) {
switch ($searchopt[$ID]["datatype"]) {
case "count":
return " COUNT(DISTINCT `{$table}{$addtable}`.`{$field}`) AS `" . $NAME . "_" . $num . "`,\n {$ADDITONALFIELDS}";
case "date_delay":
$interval = "MONTH";
if (isset($searchopt[$ID]['delayunit'])) {
$interval = $searchopt[$ID]['delayunit'];
}
$add_minus = '';
if (isset($searchopt[$ID]["datafields"][3])) {
$add_minus = "-`{$table}{$addtable}`.`" . $searchopt[$ID]["datafields"][3] . "`";
}
if ($meta || isset($searchopt[$ID]["forcegroupby"]) && $searchopt[$ID]["forcegroupby"]) {
return " GROUP_CONCAT(DISTINCT ADDDATE(`{$table}{$addtable}`.`" . $searchopt[$ID]["datafields"][1] . "`,\n INTERVAL (`{$table}{$addtable}`.`" . $searchopt[$ID]["datafields"][2] . "` {$add_minus}) {$interval})\n SEPARATOR '" . self::LONGSEP . "') AS `" . $NAME . "_{$num}`,\n {$ADDITONALFIELDS}";
}
return "ADDDATE(`{$table}{$addtable}`.`" . $searchopt[$ID]["datafields"][1] . "`,\n INTERVAL (`{$table}{$addtable}`.`" . $searchopt[$ID]["datafields"][2] . "` {$add_minus}) {$interval}) AS `" . $NAME . "_{$num}`,\n {$ADDITONALFIELDS}";
case "itemlink":
if ($meta || isset($searchopt[$ID]["forcegroupby"]) && $searchopt[$ID]["forcegroupby"]) {
return " GROUP_CONCAT(DISTINCT CONCAT({$tocompute}, '" . self::SHORTSEP . "' ,\n `{$table}{$addtable}`.`id`)\n SEPARATOR '" . self::LONGSEP . "') AS `" . $NAME . "_{$num}`,\n {$ADDITONALFIELDS}";
}
$TRANS = '';
if (Session::haveTranslations(getItemTypeForTable($table), $field)) {
$TRANS = "GROUP_CONCAT(DISTINCT CONCAT(IFNULL({$tocomputetrans}, '" . self::NULLVALUE . "'),\n '" . self::SHORTSEP . "',{$tocomputeid})\n SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "_trans`, ";
}
return " {$tocompute} AS `" . $NAME . "_{$num}`,\n `{$table}{$addtable}`.`id` AS `" . $NAME . "_" . $num . "_id`,\n {$TRANS}\n {$ADDITONALFIELDS}";
}
}
// Default case
if ($meta || isset($searchopt[$ID]["forcegroupby"]) && $searchopt[$ID]["forcegroupby"] && (!isset($searchopt[$ID]["computation"]) || isset($searchopt[$ID]["computationgroupby"]) && $searchopt[$ID]["computationgroupby"])) {
// Not specific computation
$TRANS = '';
if (Session::haveTranslations(getItemTypeForTable($table), $field)) {
$TRANS = "GROUP_CONCAT(DISTINCT CONCAT(IFNULL({$tocomputetrans}, '" . self::NULLVALUE . "'),\n '" . self::SHORTSEP . "',{$tocomputeid}) SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_" . $num . "_trans`, ";
}
return " GROUP_CONCAT(DISTINCT CONCAT(IFNULL({$tocompute}, '" . self::NULLVALUE . "'),\n '" . self::SHORTSEP . "',{$tocomputeid}) SEPARATOR '" . self::LONGSEP . "')\n AS `" . $NAME . "_{$num}`,\n {$TRANS}\n {$ADDITONALFIELDS}";
}
$TRANS = '';
if (Session::haveTranslations(getItemTypeForTable($table), $field)) {
$TRANS = $tocomputetrans . " AS `" . $NAME . "_" . $num . "_trans`, ";
}
return "{$tocompute} AS `" . $NAME . "_{$num}`, {$TRANS} {$ADDITONALFIELDS}";
}