X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=rt%2Flib%2FRT%2FTickets_Overlay.pm;h=876f1084e41f974f9e12f4219781f1d166323251;hb=5418c73e5a15b5f030de732b6558f4c8a316220a;hp=8faf3f0b4c76a86579c4c90895cd3049cd09018b;hpb=cbc36fe3a65f4713239611a0dffd8afa0de3c46e;p=freeside.git diff --git a/rt/lib/RT/Tickets_Overlay.pm b/rt/lib/RT/Tickets_Overlay.pm index 8faf3f0b4..876f1084e 100644 --- a/rt/lib/RT/Tickets_Overlay.pm +++ b/rt/lib/RT/Tickets_Overlay.pm @@ -1,40 +1,40 @@ # BEGIN BPS TAGGED BLOCK {{{ -# +# # COPYRIGHT: -# -# This software is Copyright (c) 1996-2009 Best Practical Solutions, LLC -# -# +# +# This software is Copyright (c) 1996-2011 Best Practical Solutions, LLC +# +# # (Except where explicitly superseded by other copyright notices) -# -# +# +# # LICENSE: -# +# # This work is made available to you under the terms of Version 2 of # the GNU General Public License. A copy of that license should have # been provided with this software, but in any event can be snarfed # from www.gnu.org. -# +# # This work is distributed in the hope that it will be useful, but # WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU # General Public License for more details. -# +# # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA # 02110-1301 or visit their web page on the internet at # http://www.gnu.org/licenses/old-licenses/gpl-2.0.html. -# -# +# +# # CONTRIBUTION SUBMISSION POLICY: -# +# # (The following paragraph is not intended to limit the rights granted # to you to modify and distribute this software under the terms of # the GNU General Public License and is only of importance to you if # you choose to contribute your changes and enhancements to the # community by submitting them to Best Practical Solutions, LLC.) -# +# # By intentionally submitting any modifications, corrections or # derivatives to this work, or any other work intended for use with # Request Tracker, to Best Practical Solutions, LLC, you confirm that @@ -43,7 +43,7 @@ # royalty-free, perpetual, license to use, copy, create derivative # works based on those contributions, and sublicense and distribute # those contributions and any derivatives thereof. -# +# # END BPS TAGGED BLOCK }}} # Major Changes: @@ -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,16 @@ our %FIELD_METADATA = ( WatcherGroup => [ 'MEMBERSHIPFIELD', ], #loc_left_pair HasAttribute => [ 'HASATTRIBUTE', 1 ], HasNoAttribute => [ 'HASATTRIBUTE', 0 ], + Agentnum => [ 'FREESIDEFIELD', ], + Classnum => [ 'FREESIDEFIELD', ], + Tagnum => [ 'FREESIDEFIELD', 'cust_tag' ], +); + +our %SEARCHABLE_SUBFIELDS = ( + User => [qw( + EmailAddress Name RealName Nickname Organization Address1 Address2 + WorkPhone HomePhone MobilePhone PagerPhone id + )], ); # Mapping of Field Type to Function @@ -161,8 +170,8 @@ our %dispatch = ( WATCHERFIELD => \&_WatcherLimit, MEMBERSHIPFIELD => \&_WatcherMembershipLimit, CUSTOMFIELD => \&_CustomFieldLimit, - DATECUSTOMFIELD => \&_DateCustomFieldLimit, HASATTRIBUTE => \&_HasAttributeLimit, + FREESIDEFIELD => \&_FreesideFieldLimit, ); our %can_bundle = ();# WATCHERFIELD => "yes", ); @@ -521,6 +530,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 +546,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(Timezone => 'server'); + $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 +595,7 @@ sub _DateLimit { } else { $sb->_SQLLimit( - FIELD => $meta->[1], + FIELD => $field, OPERATOR => $op, VALUE => $date->ISO, @rest, @@ -806,6 +844,13 @@ sub _WatcherLimit { my $type = $meta->[1] || ''; my $class = $meta->[2] || 'Ticket'; + # Bail if the subfield is not allowed + if ( $rest{SUBKEY} + and not grep { $_ eq $rest{SUBKEY} } @{$SEARCHABLE_SUBFIELDS{'User'}}) + { + die "Invalid watcher subfield: '$rest{SUBKEY}'"; + } + # Owner was ENUM field, so "Owner = 'xxx'" allowed user to # search by id and Name at the same time, this is workaround # to preserve backward compatibility @@ -1204,7 +1249,7 @@ Try and turn a CF descriptor into (cfid, cfname) object pair. sub _CustomFieldDecipher { my ($self, $string) = @_; - my ($queue, $field, $column) = ($string =~ /^(?:(.+?)\.)?{(.+)}(?:\.(.+))?$/); + my ($queue, $field, $column) = ($string =~ /^(?:(.+?)\.)?{(.+)}(?:\.(Content|LargeContent))?$/); $field ||= ($string =~ /^{(.*?)}$/)[0] || $string; my $cf; @@ -1342,101 +1387,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 +1470,15 @@ sub _CustomFieldLimit { %rest ); } + elsif ( $cfid and $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 +1656,6 @@ sub _HasAttributeLimit { ); } - # End Helper Functions # End of SQL Stuff ------------------------------------------------- @@ -1724,7 +1682,6 @@ sub OrderByCols { next; } if ( $row->{FIELD} !~ /\./ ) { - my $meta = $self->FIELDS->{ $row->{FIELD} }; unless ( $meta ) { push @res, $row; @@ -1824,63 +1781,52 @@ sub OrderByCols { foreach my $uid ( $self->CurrentUser->Id, $RT::Nobody->Id ) { if ( RT->Config->Get('DatabaseType') eq 'Oracle' ) { my $f = ($row->{'ALIAS'} || 'main') .'.Owner'; - push @res, { %$row, ALIAS => '', FIELD => "CASE WHEN $f=$uid THEN 1 ELSE 0 END", ORDER => $order } ; + push @res, { + %$row, + FIELD => undef, + ALIAS => '', + FUNCTION => "CASE WHEN $f=$uid THEN 1 ELSE 0 END", + ORDER => $order + }; } else { - push @res, { %$row, FIELD => "Owner=$uid", ORDER => $order } ; + push @res, { + %$row, + FIELD => undef, + FUNCTION => "Owner=$uid", + ORDER => $order + }; } } 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 ( $subkey eq 'Number' ) { - + my ($linkalias, $custnum_sql) = $self->JoinToCustLinks; push @res, { %$row, - ALIAS => $linkalias, - FIELD => "CAST(SUBSTR(Target,31) AS INTEGER)", - #ORDER => ($row->{ORDER} || 'ASC') - }; - - } elsif ( $subkey eq 'Name' ) { - - my $custalias = $self->Join( - TYPE => 'LEFT', - #ALIAS1 => $linkalias, - #FIELD1 => 'CAST(SUBSTR(Target,31) AS INTEGER)', - EXPRESSION => "CAST(SUBSTR($linkalias.Target,31) AS INTEGER)", - TABLE2 => 'cust_main', - FIELD2 => 'custnum', - - ); - - my $field = "COALESCE( $custalias.company, - $custalias.last || ', ' || $custalias.first - )"; - - push @res, { %$row, ALIAS => '', FIELD => $field }; - + ALIAS => '', + FIELD => $custnum_sql, + }; + } + else { + my $custalias = $self->JoinToCustomer; + my $field; + if ( $subkey eq 'Name' ) { + $field = "COALESCE( $custalias.company, + $custalias.last || ', ' || $custalias.first + )"; + } + elsif ( $subkey eq 'Class' ) { + $field = "$custalias.classnum"; + } + elsif ( $subkey eq 'Agent' ) { + $field = "$custalias.agentnum"; + } + else { + # no other cases exist yet, but for obviousness: + $field = $subkey; + } + push @res, { %$row, ALIAS => '', FIELD => $field }; } } #Freeside @@ -1892,55 +1838,102 @@ sub OrderByCols { return $self->SUPER::OrderByCols(@res); } -# }}} +#Freeside -#this duplicates/ovverrides the DBIx::SearchBuilder version.. -# we need to fix the "handle FUNCTION(FIELD)" stuff and this is much easier -# than patching SB -# but does this have other terrible ramifications? maybe a flag to trigger -# this specific case? -sub _OrderClause { +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', + ); - return '' unless $self->{'order_by'}; - - my $clause = ''; - foreach my $row ( @{$self->{'order_by'}} ) { - - my %rowhash = ( ALIAS => 'main', - FIELD => undef, - ORDER => 'ASC', - %$row - ); - if ($rowhash{'ORDER'} && $rowhash{'ORDER'} =~ /^des/i) { - $rowhash{'ORDER'} = "DESC"; - } - else { - $rowhash{'ORDER'} = "ASC"; - } - $rowhash{'ALIAS'} = 'main' unless defined $rowhash{'ALIAS'}; + $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); +} - if ( defined $rowhash{'ALIAS'} and - $rowhash{'FIELD'} and - $rowhash{'ORDER'} ) { +sub JoinToCustomer { + my $self = shift; + my ($linkalias, $custnum_sql) = $self->JoinToCustLinks; - if ( length $rowhash{'ALIAS'} && $rowhash{'FIELD'} =~ /^((\w+\()+)(.*\)+)$/ ) { - # handle 'FUNCTION(FIELD)' formatted fields - $rowhash{'FIELD'} = $1. $rowhash{'ALIAS'}. '.'. $3; - $rowhash{'ALIAS'} = ''; - } + my $custalias = $self->Join( + TYPE => 'LEFT', + EXPRESSION => $custnum_sql, + TABLE2 => 'cust_main', + FIELD2 => 'custnum', + ); + return $custalias; +} - $clause .= ($clause ? ", " : " "); - $clause .= $rowhash{'ALIAS'} . "." if length $rowhash{'ALIAS'}; - $clause .= $rowhash{'FIELD'} . " "; - $clause .= $rowhash{'ORDER'}; - } +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', + ); } - $clause = " ORDER BY$clause " if $clause; - return $clause; + $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 # {{{ sub Limit @@ -2865,11 +2858,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'} @@ -3337,9 +3325,9 @@ is a description of the purpose of that TicketRestriction sub DescribeRestrictions { my $self = shift; - my ( $row, %listing ); + my %listing; - foreach $row ( keys %{ $self->{'TicketRestrictions'} } ) { + foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) { $listing{$row} = $self->{'TicketRestrictions'}{$row}{'DESCRIPTION'}; } return (%listing); @@ -3414,9 +3402,8 @@ sub DeleteRestriction { sub _RestrictionsToClauses { my $self = shift; - my $row; my %clause; - foreach $row ( keys %{ $self->{'TicketRestrictions'} } ) { + foreach my $row ( keys %{ $self->{'TicketRestrictions'} } ) { my $restriction = $self->{'TicketRestrictions'}{$row}; # We need to reimplement the subclause aggregation that SearchBuilder does.