xmkouril at gmail
May 23, 2009, 11:00 AM
Views: 414
Permalink
|
|
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 {
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
|
Date fields and 'not set' values
