[Rt-devel] Date fields and 'not set' values

[Michal Kouril]

Hi,

First patch is to allow 'Not set' or 'NULL' entry into the date fields.

The second patch is connected with the 'not set' search on date  
fields. I tried to setup a query through TicketSQL to compare date  
fields against "not set" and it seemed the functionality was not  
there. The patch will allow for queries such as Due = 'not set' or  
Due != 'not set' to query for "not set" in the date fields.

If there is any other way to do the above please let me know.

Thanks,

Michal

--- rt-3.8.2/lib/RT/Interface/Web.pm	2009-01-06 20:15:34.000000000 -0500
+++ /opt/rt3/lib/RT/Interface/Web.pm	2009-05-23 13:25:33.000000000 -0400
@@ -1414,7 +1414,12 @@
              and ( $DateObj->Unix != $Ticket->$obj()->Unix() ) )
          {
              my $method = "Set$field";
-            my ( $code, $msg ) = $Ticket->$method( $DateObj->ISO );
+            if ( $ARGSRef->{ $field . '_Date' } =~ /^NULL$/i ||
+                 $ARGSRef->{ $field . '_Date' } =~ /^not set$/i ) {
+                ( $code, $msg ) = $Ticket->$method( '0000-00-00  
00:00:00' );
+            } else {
+                ( $code, $msg ) = $Ticket->$method( $DateObj->ISO );
+            }
              push @results, "$msg";
          }
      }



--- rt-3.8.2/lib/RT/Tickets_Overlay.pm	2009-01-06 20:15:33.000000000  
-0500
+++ /opt/rt3/lib/RT/Tickets_Overlay.pm	2009-05-23 13:53:19.000000000  
-0400
@@ -505,7 +505,7 @@
      my ( $sb, $field, $op, $value, @rest ) = @_;

      die "Invalid Date Op: $op"
-        unless $op =~ /^(=|>|<|>=|<=)$/;
+        unless $op =~ /^(=|>|<|>=|<=|!=)$/;

      my $meta = $FIELD_METADATA{$field};
      die "Incorrect Meta Data for $field"
@@ -516,33 +516,111 @@

      if ( $op eq "=" ) {

-        # if we're specifying =, that means we want everything on a
-        # particular single day.  in the database, we need to check  
for >
-        # and < the edges of that day.
+        if ( $value =~ /^NULL$/i ||
+             $value =~ /^not set$/i ) {

-        $date->SetToMidnight( Timezone => 'server' );
-        my $daystart = $date->ISO;
-        $date->AddDay;
-        my $dayend = $date->ISO;
+            $sb->_OpenParen;

-        $sb->_OpenParen;
+            $sb->_SQLLimit(
+                FIELD    => $meta->[1],
+                OPERATOR => "is",
+                VALUE    => "NULL",
+                @rest,
+            );

-        $sb->_SQLLimit(
-            FIELD    => $meta->[1],
-            OPERATOR => ">=",
-            VALUE    => $daystart,
-            @rest,
-        );
+            $sb->_SQLLimit(
+                FIELD    => $meta->[1],
+                OPERATOR => "=",
+                VALUE    => "0000-00-00 00:00:00",
+                @rest,
+                ENTRYAGGREGATOR => 'OR',
+            );

-        $sb->_SQLLimit(
-            FIELD    => $meta->[1],
-            OPERATOR => "<=",
-            VALUE    => $dayend,
-            @rest,
-            ENTRYAGGREGATOR => 'AND',
-        );
+            $sb->_CloseParen;
+        } else {

-        $sb->_CloseParen;
+            # if we're specifying =, that means we want everything on a
+            # particular single day.  in the database, we need to  
check for >
+            # and < the edges of that day.
+
+            $date->SetToMidnight( Timezone => 'server' );
+            my $daystart = $date->ISO;
+            $date->AddDay;
+            my $dayend = $date->ISO;
+
+            $sb->_OpenParen;
+
+            $sb->_SQLLimit(
+                FIELD    => $meta->[1],
+                OPERATOR => ">=",
+                VALUE    => $daystart,
+                @rest,
+            );
+
+            $sb->_SQLLimit(
+                FIELD    => $meta->[1],
+                OPERATOR => "<=",
+                VALUE    => $dayend,
+                @rest,
+                ENTRYAGGREGATOR => 'AND',
+            );
+
+            $sb->_CloseParen;
+        }
+
+    } elsif ( $op eq "!=" ) {
+
+        if ( $value =~ /^NULL$/i ||
+             $value =~ /^not set$/i ) {
+
+            $sb->_OpenParen;
+
+            $sb->_SQLLimit(
+                FIELD    => $meta->[1],
+                OPERATOR => "is not",
+                VALUE    => "NULL",
+                @rest,
+            );
+
+            $sb->_SQLLimit(
+                FIELD    => $meta->[1],
+                OPERATOR => "!=",
+                VALUE    => "0000-00-00 00:00:00",
+                @rest,
+                ENTRYAGGREGATOR => 'AND',
+            );
+
+            $sb->_CloseParen;
+        } else {
+
+            # if we're specifying =, that means we want everything on a
+            # particular single day.  in the database, we need to  
check for >
+            # and < the edges of that day.
+
+            $date->SetToMidnight( Timezone => 'server' );
+            my $daystart = $date->ISO;
+            $date->AddDay;
+            my $dayend = $date->ISO;
+
+            $sb->_OpenParen;
+
+            $sb->_SQLLimit(
+                FIELD    => $meta->[1],
+                OPERATOR => "<",
+                VALUE    => $daystart,
+                @rest,
+            );
+
+            $sb->_SQLLimit(
+                FIELD    => $meta->[1],
+                OPERATOR => ">",
+                VALUE    => $dayend,
+                @rest,
+                ENTRYAGGREGATOR => 'AND',
+            );
+
+            $sb->_CloseParen;
+        }

      }
      else {