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

[Jesse Vincent]

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
> 

--