Date fields and 'not set' values

Michal_Kouril · May 23, 2009, 6:00pm

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 {
```

Jesse_Vincent · May 26, 2009, 1:10pm

I’ve bounced this into a ticket for review (probably by ruslan)On Sat, May 23, 2009 at 02:00:10PM -0400, Michal Kouril wrote:

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 {
```

List info: The rt-devel Archives