Date fields and 'not set' values

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 {

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: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel