X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=rt%2Flib%2FRT%2FTickets_Overlay.pm;h=16e98aec8253d37a1a5683219cc20279016663e9;hb=afc02cadca8e8033285bb213bcde620cb8fa4f99;hp=f2949ed7aad0aa92114d9258f24dd4238de6c400;hpb=5ef991c5001d0a9413d99cc76f152f3a7b71459b;p=freeside.git diff --git a/rt/lib/RT/Tickets_Overlay.pm b/rt/lib/RT/Tickets_Overlay.pm index f2949ed7a..16e98aec8 100644 --- a/rt/lib/RT/Tickets_Overlay.pm +++ b/rt/lib/RT/Tickets_Overlay.pm @@ -136,7 +136,6 @@ our %FIELD_METADATA = ( QueueAdminCc => [ 'WATCHERFIELD' => 'AdminCc' => 'Queue', ], #loc_left_pair QueueWatcher => [ 'WATCHERFIELD' => undef => 'Queue', ], #loc_left_pair CustomFieldValue => [ 'CUSTOMFIELD', ], #loc_left_pair - DateCustomFieldValue => [ 'DATECUSTOMFIELD', ], CustomField => [ 'CUSTOMFIELD', ], #loc_left_pair CF => [ 'CUSTOMFIELD', ], #loc_left_pair Updated => [ 'TRANSDATE', ], #loc_left_pair @@ -146,6 +145,9 @@ our %FIELD_METADATA = ( WatcherGroup => [ 'MEMBERSHIPFIELD', ], #loc_left_pair HasAttribute => [ 'HASATTRIBUTE', 1 ], HasNoAttribute => [ 'HASATTRIBUTE', 0 ], + Agentnum => [ 'FREESIDEFIELD', ], + Classnum => [ 'FREESIDEFIELD', ], + Tagnum => [ 'FREESIDEFIELD', 'cust_tag' ], ); # Mapping of Field Type to Function @@ -161,8 +163,8 @@ our %dispatch = ( WATCHERFIELD => \&_WatcherLimit, MEMBERSHIPFIELD => \&_WatcherMembershipLimit, CUSTOMFIELD => \&_CustomFieldLimit, - DATECUSTOMFIELD => \&_DateCustomFieldLimit, HASATTRIBUTE => \&_HasAttributeLimit, + FREESIDEFIELD => \&_FreesideFieldLimit, ); our %can_bundle = ();# WATCHERFIELD => "yes", ); @@ -521,6 +523,14 @@ sub _DateLimit { die "Incorrect Meta Data for $field" unless ( defined $meta->[1] ); + $sb->_DateFieldLimit( $meta->[1], $op, $value, @rest ); +} + +# Factor this out for use by custom fields + +sub _DateFieldLimit { + my ( $sb, $field, $op, $value, @rest ) = @_; + my $date = RT::Date->new( $sb->CurrentUser ); $date->Set( Format => 'unknown', Value => $value ); @@ -529,23 +539,44 @@ sub _DateLimit { # 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; + # + # Except if the value is 'this month' or 'last month', check + # > and < the edges of the month. + + my ($daystart, $dayend); + if ( lc($value) eq 'this month' ) { + $date->SetToNow; + $date->SetToStart('month', Timezone => 'server'); + $daystart = $date->ISO; + $date->AddMonth; + $dayend = $date->ISO; + } + elsif ( lc($value) eq 'last month' ) { + $date->SetToNow; + $date->SetToStart('month', Timezone => 'server'); + $dayend = $date->ISO; + $date->AddDays(-1); + $date->SetToStart('month', Timezone => 'server'); + $daystart = $date->ISO; + } + else { + $date->SetToMidnight( Timezone => 'server' ); + $daystart = $date->ISO; + $date->AddDay; + $dayend = $date->ISO; + } $sb->_OpenParen; $sb->_SQLLimit( - FIELD => $meta->[1], + FIELD => $field, OPERATOR => ">=", VALUE => $daystart, @rest, ); $sb->_SQLLimit( - FIELD => $meta->[1], + FIELD => $field, OPERATOR => "<", VALUE => $dayend, @rest, @@ -557,7 +588,7 @@ sub _DateLimit { } else { $sb->_SQLLimit( - FIELD => $meta->[1], + FIELD => $field, OPERATOR => $op, VALUE => $date->ISO, @rest, @@ -1342,101 +1373,6 @@ sub _CustomFieldJoin { return ($TicketCFs, $CFs); } -=head2 _DateCustomFieldLimit - -Limit based on CustomFields of type Date - -Meta Data: - none - -=cut - -sub _DateCustomFieldLimit { - my ( $self, $_field, $op, $value, %rest ) = @_; - - my $field = $rest{'SUBKEY'} || die "No field specified"; - - # For our sanity, we can only limit on one queue at a time - - my ($queue, $cfid, $column); - ($queue, $field, $cfid, $column) = $self->_CustomFieldDecipher( $field ); - -# If we're trying to find custom fields that don't match something, we -# want tickets where the custom field has no value at all. Note that -# we explicitly don't include the "IS NULL" case, since we would -# otherwise end up with a redundant clause. - - my $null_columns_ok; - if ( ( $op =~ /^NOT LIKE$/i ) or ( $op eq '!=' ) ) { - $null_columns_ok = 1; - } - - my $cfkey = $cfid ? $cfid : "$queue.$field"; - my ($TicketCFs, $CFs) = $self->_CustomFieldJoin( $cfkey, $cfid, $field ); - - $self->_OpenParen; - - if ( $CFs && !$cfid ) { - $self->SUPER::Limit( - ALIAS => $CFs, - FIELD => 'Name', - VALUE => $field, - ENTRYAGGREGATOR => 'AND', - ); - } - - $self->_OpenParen if $null_columns_ok; - - my $date = RT::Date->new( $self->CurrentUser ); - $date->Set( Format => 'unknown', Value => $value ); - - 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. - - $date->SetToMidnight( Timezone => 'server' ); - my $daystart = $date->ISO; - $date->AddDay; - my $dayend = $date->ISO; - - $self->_OpenParen; - - $self->_SQLLimit( - ALIAS => $TicketCFs, - FIELD => 'Content', - OPERATOR => ">=", - VALUE => $daystart, - %rest, - ); - - $self->_SQLLimit( - ALIAS => $TicketCFs, - FIELD => 'Content', - OPERATOR => "<=", - VALUE => $dayend, - %rest, - ENTRYAGGREGATOR => 'AND', - ); - - $self->_CloseParen; - - } - else { - $self->_SQLLimit( - ALIAS => $TicketCFs, - FIELD => 'Content', - OPERATOR => $op, - VALUE => $date->ISO, - %rest, - ); - } - - $self->_CloseParen; - -} - =head2 _CustomFieldLimit Limit based on CustomFields @@ -1520,6 +1456,15 @@ sub _CustomFieldLimit { %rest ); } + elsif ( $cf->Type eq 'Date' ) { + $self->_DateFieldLimit( + 'Content', + $op, + $value, + ALIAS => $TicketCFs, + %rest + ); + } elsif ( $op eq '=' || $op eq '!=' || $op eq '<>' ) { unless ( length( Encode::encode_utf8($value) ) > 255 ) { $self->_SQLLimit( @@ -1697,7 +1642,6 @@ sub _HasAttributeLimit { ); } - # End Helper Functions # End of SQL Stuff ------------------------------------------------- @@ -1832,54 +1776,26 @@ sub OrderByCols { push @res, { %$row, FIELD => "Priority", ORDER => $order } ; } elsif ( $field eq 'Customer' ) { #Freeside - - my $linkalias = $self->Join( - TYPE => 'LEFT', - ALIAS1 => 'main', - FIELD1 => 'id', - TABLE2 => 'Links', - FIELD2 => 'LocalBase' - ); - - $self->SUPER::Limit( - LEFTJOIN => $linkalias, - FIELD => 'Type', - OPERATOR => '=', - VALUE => 'MemberOf', - ); - $self->SUPER::Limit( - LEFTJOIN => $linkalias, - FIELD => 'Target', - OPERATOR => 'STARTSWITH', - VALUE => 'freeside://freeside/cust_main/', - ); - - #if there was a Links.RemoteTarget int, this bs wouldn't be necessary - my $custnum_sql = "CAST(SUBSTR($linkalias.Target,31) AS INTEGER)"; - if ( $subkey eq 'Number' ) { - + my ($linkalias, $custnum_sql) = $self->JoinToCustLinks; push @res, { %$row, ALIAS => '', FIELD => $custnum_sql, - }; - - } elsif ( $subkey eq 'Name' ) { - - my $custalias = $self->Join( - TYPE => 'LEFT', - EXPRESSION => $custnum_sql, - TABLE2 => 'cust_main', - FIELD2 => 'custnum', - - ); - - my $field = "COALESCE( $custalias.company, - $custalias.last || ', ' || $custalias.first - )"; - - push @res, { %$row, ALIAS => '', FIELD => $field }; - + }; + } + else { + my $custalias = $self->JoinToCustomer; + my $field; + if ( $subkey eq 'Name' ) { + $field = "COALESCE( $custalias.company, + $custalias.last || ', ' || $custalias.first + )"; + } + else { + # no other cases exist yet, but for obviousness: + $field = $subkey; + } + push @res, { %$row, ALIAS => '', FIELD => $field }; } } #Freeside @@ -1891,6 +1807,100 @@ sub OrderByCols { return $self->SUPER::OrderByCols(@res); } +#Freeside + +sub JoinToCustLinks { + # Set up join to links (id = localbase), + # limit link type to 'MemberOf', + # and target value to any Freeside custnum URI. + # Return the linkalias for further join/limit action, + # and an sql expression to retrieve the custnum. + my $self = shift; + my $linkalias = $self->Join( + TYPE => 'LEFT', + ALIAS1 => 'main', + FIELD1 => 'id', + TABLE2 => 'Links', + FIELD2 => 'LocalBase', + ); + + $self->SUPER::Limit( + LEFTJOIN => $linkalias, + FIELD => 'Type', + OPERATOR => '=', + VALUE => 'MemberOf', + ); + $self->SUPER::Limit( + LEFTJOIN => $linkalias, + FIELD => 'Target', + OPERATOR => 'STARTSWITH', + VALUE => 'freeside://freeside/cust_main/', + ); + my $custnum_sql = "CAST(SUBSTR($linkalias.Target,31) AS "; + if ( RT->Config->Get('DatabaseType') eq 'mysql' ) { + $custnum_sql .= 'SIGNED INTEGER)'; + } + else { + $custnum_sql .= 'INTEGER)'; + } + return ($linkalias, $custnum_sql); +} + +sub JoinToCustomer { + my $self = shift; + my ($linkalias, $custnum_sql) = $self->JoinToCustLinks; + + my $custalias = $self->Join( + TYPE => 'LEFT', + EXPRESSION => $custnum_sql, + TABLE2 => 'cust_main', + FIELD2 => 'custnum', + ); + return $custalias; +} + +sub _FreesideFieldLimit { + my ( $self, $field, $op, $value, %rest ) = @_; + my $alias = $self->JoinToCustomer; + my $is_negative = 0; + if ( $op eq '!=' || $op =~ /\bNOT\b/i ) { + # if the op is negative, do the join as though + # the op were positive, then accept only records + # where the right-side join key is null. + $is_negative = 1; + $op = '=' if $op eq '!='; + $op =~ s/\bNOT\b//; + } + my $meta = $FIELD_METADATA{$field}; + if ( $meta->[1] ) { + $alias = $self->Join( + TYPE => 'LEFT', + ALIAS1 => $alias, + FIELD1 => 'custnum', + TABLE2 => $meta->[1], + FIELD2 => 'custnum', + ); + } + + $self->SUPER::Limit( + LEFTJOIN => $alias, + FIELD => lc($field), + OPERATOR => $op, + VALUE => $value, + ENTRYAGGREGATOR => 'AND', + ); + $self->_SQLLimit( + %rest, + ALIAS => $alias, + FIELD => lc($field), + OPERATOR => $is_negative ? 'IS' : 'IS NOT', + VALUE => 'NULL', + QUOTEVALUE => 0, + ); +} + +#Freeside + # }}} # {{{ Limit the result set based on content @@ -2817,11 +2827,6 @@ sub LimitCustomField { $args{CUSTOMFIELD} = $CF->Id; } - # Handle special customfields types - if ($CF->Type eq 'Date') { - $args{FIELD} = 'DateCustomFieldValue'; - } - #If we are looking to compare with a null value. if ( $args{'OPERATOR'} =~ /^is$/i ) { $args{'DESCRIPTION'}