X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=rt%2Flib%2FRT%2FTickets_Overlay.pm;h=0d482cd04e1ea5e06819c3c10a871edfc57a30e5;hb=6587f6ba7d047ddc1686c080090afe7d53365bd4;hp=5a7e020566adfa7f0a3b94e272f155e448e9cd65;hpb=09aa4ca78df448f73e440a25a024e44eaf00c827;p=freeside.git diff --git a/rt/lib/RT/Tickets_Overlay.pm b/rt/lib/RT/Tickets_Overlay.pm index 5a7e02056..0d482cd04 100644 --- a/rt/lib/RT/Tickets_Overlay.pm +++ b/rt/lib/RT/Tickets_Overlay.pm @@ -145,9 +145,17 @@ our %FIELD_METADATA = ( WatcherGroup => [ 'MEMBERSHIPFIELD', ], #loc_left_pair HasAttribute => [ 'HASATTRIBUTE', 1 ], HasNoAttribute => [ 'HASATTRIBUTE', 0 ], - Agentnum => [ 'FREESIDEFIELD', ], - Classnum => [ 'FREESIDEFIELD', ], - Tagnum => [ 'FREESIDEFIELD', 'cust_tag' ], + #freeside + Customer => [ 'FREESIDEFIELD' => 'Customer' ], + Service => [ 'FREESIDEFIELD' => 'Service' ], + WillResolve => [ 'DATE' => 'WillResolve', ], #loc_left_pair +); + +our %SEARCHABLE_SUBFIELDS = ( + User => [qw( + EmailAddress Name RealName Nickname Organization Address1 Address2 + WorkPhone HomePhone MobilePhone PagerPhone id + )], ); # Mapping of Field Type to Function @@ -548,7 +556,7 @@ sub _DateFieldLimit { $date->SetToNow; $date->SetToStart('month', Timezone => 'server'); $daystart = $date->ISO; - $date->AddMonth; + $date->AddMonth(Timezone => 'server'); $dayend = $date->ISO; } elsif ( lc($value) eq 'last month' ) { @@ -837,6 +845,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 @@ -1235,7 +1250,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; @@ -1456,7 +1471,7 @@ sub _CustomFieldLimit { %rest ); } - elsif ( $cf->Type eq 'Date' ) { + elsif ( $cfid and $cf->Type eq 'Date' ) { $self->_DateFieldLimit( 'Content', $op, @@ -1767,42 +1782,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 - if ( $subkey eq 'Number' ) { - my ($linkalias, $custnum_sql) = $self->JoinToCustLinks; - push @res, { %$row, - ALIAS => '', - FIELD => $custnum_sql, - }; + # OrderBy(FIELD => expression) doesn't work, it has to be + # an actual field, so we have to do the join even if sorting + # by custnum + my $custalias = $self->JoinToCustomer; + my $cust_field = lc($subkey); + if ( !$cust_field or $cust_field eq 'number' ) { + $cust_field = 'custnum'; } - 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 }; + elsif ( $cust_field eq 'name' ) { + $cust_field = "COALESCE( $custalias.company, + $custalias.last || ', ' || $custalias.first + )"; } + else { # order by cust_main fields directly: 'Customer.agentnum' + $cust_field = $subkey; + } + push @res, { %$row, ALIAS => $custalias, FIELD => $cust_field }; + + } elsif ( $field eq 'Service' ) { + + my $svcalias = $self->JoinToService; + my $svc_field = lc($subkey); + if ( !$svc_field or $svc_field eq 'number' ) { + $svc_field = 'svcnum'; + } + push @res, { %$row, ALIAS => $svcalias, FIELD => $svc_field }; } #Freeside @@ -1822,26 +1847,31 @@ sub JoinToCustLinks { # 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', - ); + # only join once for each RT::Tickets object + my $linkalias = $self->{cust_main_linkalias}; + if (!$linkalias) { + $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/', - ); + $self->SUPER::Limit( + LEFTJOIN => $linkalias, + FIELD => 'Type', + OPERATOR => '=', + VALUE => 'MemberOf', + ); + $self->SUPER::Limit( + LEFTJOIN => $linkalias, + FIELD => 'Target', + OPERATOR => 'STARTSWITH', + VALUE => 'freeside://freeside/cust_main/', + ); + $self->{cust_main_linkalias} = $linkalias; + } my $custnum_sql = "CAST(SUBSTR($linkalias.Target,31) AS "; if ( RT->Config->Get('DatabaseType') eq 'mysql' ) { $custnum_sql .= 'SIGNED INTEGER)'; @@ -1855,7 +1885,8 @@ sub JoinToCustLinks { sub JoinToCustomer { my $self = shift; my ($linkalias, $custnum_sql) = $self->JoinToCustLinks; - + # don't reuse this join, though--negative queries need + # independent joins my $custalias = $self->Join( TYPE => 'LEFT', EXPRESSION => $custnum_sql, @@ -1865,9 +1896,79 @@ sub JoinToCustomer { return $custalias; } +sub JoinToSvcLinks { + my $self = shift; + my $linkalias = $self->{cust_svc_linkalias}; + if (!$linkalias) { + $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_svc/', + ); + $self->{cust_svc_linkalias} = $linkalias; + } + my $svcnum_sql = "CAST(SUBSTR($linkalias.Target,30) AS "; + if ( RT->Config->Get('DatabaseType') eq 'mysql' ) { + $svcnum_sql .= 'SIGNED INTEGER)'; + } + else { + $svcnum_sql .= 'INTEGER)'; + } + return ($linkalias, $svcnum_sql); +} + +sub JoinToService { + my $self = shift; + my ($linkalias, $svcnum_sql) = $self->JoinToSvcLinks; + $self->Join( + TYPE => 'LEFT', + EXPRESSION => $svcnum_sql, + TABLE2 => 'cust_svc', + FIELD2 => 'svcnum', + ); +} + +# This creates an alternate left join path to cust_main via cust_svc. +# _FreesideFieldLimit needs to add this as a separate, independent join +# and include all tickets that have a matching cust_main record via +# either path. +sub JoinToCustomerViaService { + my $self = shift; + my $svcalias = $self->JoinToService; + my $cust_pkg = $self->Join( + TYPE => 'LEFT', + ALIAS1 => $svcalias, + FIELD1 => 'pkgnum', + TABLE2 => 'cust_pkg', + FIELD2 => 'pkgnum', + ); + my $cust_main = $self->Join( + TYPE => 'LEFT', + ALIAS1 => $cust_pkg, + FIELD1 => 'custnum', + TABLE2 => 'cust_main', + FIELD2 => 'custnum', + ); + $cust_main; +} + 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 @@ -1877,32 +1978,71 @@ sub _FreesideFieldLimit { $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, - ); + my (@alias, $table2, $subfield, $pkey); + if ( $field eq 'Customer' ) { + push @alias, $self->JoinToCustomer; + push @alias, $self->JoinToCustomerViaService; + $pkey = 'custnum'; + } + elsif ( $field eq 'Service' ) { + push @alias, $self->JoinToService; + $pkey = 'svcnum'; + } + else { + die "malformed Freeside query: $field"; + } + + $subfield = $rest{SUBKEY} || $pkey; + my $table2; + # compound subkey: separate into table name and field in that table + # (must be linked by custnum) + $subfield = lc($subfield); + ($table2, $subfield) = ($1, $2) if $subfield =~ /^(\w+)?\.(\w+)$/; + $subfield = $pkey if $subfield eq 'number'; + + # if it's compound, create a join from cust_main or cust_svc to that + # table, using custnum or svcnum, and Limit on that table instead. + foreach my $a (@alias) { + if ( $table2 ) { + $a = $self->Join( + TYPE => 'LEFT', + ALIAS1 => $a, + FIELD1 => $pkey, + TABLE2 => $table2, + FIELD2 => $pkey, + ); + } + + # do the actual Limit + $self->SUPER::Limit( + LEFTJOIN => $a, + FIELD => $subfield, + OPERATOR => $op, + VALUE => $value, + ENTRYAGGREGATOR => 'AND', + # no SUBCLAUSE needed, limits on different aliases across left joins + # are inherently independent + ); + + # then, since it's a left join, exclude tickets for which there is now + # no matching record in the table we just limited on. (Or where there + # is a matching record, if $is_negative.) + # For a cust_main query (where there are two different aliases), this + # will produce a subclause: "cust_main_1.custnum IS NOT NULL OR + # cust_main_2.custnum IS NOT NULL" (or "IS NULL AND..." for a negative + # query). + $self->_SQLLimit( + %rest, + ALIAS => $a, + FIELD => $pkey, + OPERATOR => $is_negative ? 'IS' : 'IS NOT', + VALUE => 'NULL', + QUOTEVALUE => 0, + ENTRYAGGREGATOR => $is_negative ? 'AND' : 'OR', + SUBCLAUSE => 'fs_limit', + ); + } } #Freeside @@ -3300,9 +3440,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); @@ -3377,9 +3517,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.