Html::computeGenericDateTimeSearch PHP Method

    static function computeGenericDateTimeSearch($val, $force_day = false, $specifictime = '')
    {
        if (empty($specifictime)) {
            $specifictime = strtotime($_SESSION["glpi_currenttime"]);
        }
        $format_use = "Y-m-d H:i:s";
        if ($force_day) {
            $format_use = "Y-m-d";
        }
        // Parsing relative date
        switch ($val) {
            case 'NOW':
                return date($format_use, $specifictime);
            case 'TODAY':
                return date("Y-m-d", $specifictime);
        }
        // Search on begin of month / year
        if (strstr($val, 'BEGIN')) {
            $hour = 0;
            $minute = 0;
            $second = 0;
            $month = date("n", $specifictime);
            $day = 1;
            $year = date("Y", $specifictime);
            switch ($val) {
                case "BEGINYEAR":
                    $month = 1;
                    break;
                case "BEGINMONTH":
                    break;
            }
            return date($format_use, mktime($hour, $minute, $second, $month, $day, $year));
        }
        // Search on Last monday, sunday...
        if (strstr($val, 'LAST')) {
            $lastday = str_replace("LAST", "LAST ", $val);
            $hour = 0;
            $minute = 0;
            $second = 0;
            $month = date("n", strtotime($lastday));
            $day = date("j", strtotime($lastday));
            $year = date("Y", strtotime($lastday));
            return date($format_use, mktime($hour, $minute, $second, $month, $day, $year));
        }
        // Search on +- x days, hours...
        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", $specifictime);
                $minute = date("i", $specifictime);
                $second = 0;
                $month = date("n", $specifictime);
                $day = date("j", $specifictime);
                $year = date("Y", $specifictime);
                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":
                        $format_use = "Y-m-d H:i:s";
                        $hour += $nb;
                        break;
                }
                return date($format_use, mktime($hour, $minute, $second, $month, $day, $year));
            }
        }
        return $val;
    }

 /**
  * 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("/\\&lt;/", "/\\&gt;/");
             $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("/\\&lt;/", "/\\&gt;/");
                 $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("/\\&lt;/", "/\\&gt;/");
                 $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);
 }