Date fields and 'not set' values


#1

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 {

#2

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