From: mark Date: Sat, 20 Nov 2010 02:33:04 +0000 (+0000) Subject: search tickets on some customer fields, RT#9328 X-Git-Tag: TORRUS_1_0_9~79 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=14ddc73d2c72c357bc80e40aae0c3a7538110450 search tickets on some customer fields, RT#9328 --- diff --git a/rt/FREESIDE_MODIFIED b/rt/FREESIDE_MODIFIED index 9a86d2e88..070d574e0 100644 --- a/rt/FREESIDE_MODIFIED +++ b/rt/FREESIDE_MODIFIED @@ -21,7 +21,7 @@ lib/RT/Interface/Web_Vendor.pm lib/RT/Record.pm #and customfield date patch lib/RT/SearchBuilder.pm #need DBIx::SearchBuilder >= 1.36 for Pg 8.1+ lib/RT/Transaction_Overlay.pm -lib/RT/Tickets_Overlay.pm #customfield date patch +lib/RT/Tickets_Overlay.pm #customfield date patch #SearchCustomerFields lib/RT/Ticket_Overlay.pm lib/RT/Users_Overlay.pm lib/RT/Groups_Overlay.pm @@ -48,6 +48,9 @@ share/html/Elements/EditCustomFieldDate #customfield date patch (NEW) share/html/Elements/ShowLink_Checklist share/html/Elements/ShowUserVerbose share/html/Elements/Footer + share/html/Elements/SelectCustomerAgent #SearchCustomerFields + share/html/Elements/SelectCustomerClass #SearchCustomerFields + share/html/Elements/SelectCustomerTag #SearchCustomerFields html/Ticket/Create.html #XXX TODO share/html/Search/Build.html share/html/Search/Elements/BuildFormatString @@ -85,3 +88,6 @@ share/html/Callbacks/RTx-Checklist/* share/html/Callbacks/CheckMandatoryFields/* share/html/Callbacks/TimeToResolve/* + +share/html/Callbacks/SearchCustomerFields/* + diff --git a/rt/lib/RT/Tickets_Overlay.pm b/rt/lib/RT/Tickets_Overlay.pm index be5a0d5fb..61f2db033 100644 --- a/rt/lib/RT/Tickets_Overlay.pm +++ b/rt/lib/RT/Tickets_Overlay.pm @@ -146,6 +146,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 @@ -163,6 +166,7 @@ our %dispatch = ( CUSTOMFIELD => \&_CustomFieldLimit, DATECUSTOMFIELD => \&_DateCustomFieldLimit, HASATTRIBUTE => \&_HasAttributeLimit, + FREESIDEFIELD => \&_FreesideFieldLimit, ); our %can_bundle = ();# WATCHERFIELD => "yes", ); @@ -1697,7 +1701,6 @@ sub _HasAttributeLimit { ); } - # End Helper Functions # End of SQL Stuff ------------------------------------------------- @@ -1832,60 +1835,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 "; - if ( RT->Config->Get('DatabaseType') eq 'mysql' ) { - $custnum_sql .= 'SIGNED INTEGER)'; - } - else { - $custnum_sql .= '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 @@ -1897,6 +1866,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 diff --git a/rt/share/html/Callbacks/SearchCustomerFields/Search/Elements/PickBasics/Default b/rt/share/html/Callbacks/SearchCustomerFields/Search/Elements/PickBasics/Default new file mode 100644 index 000000000..abbafbcf1 --- /dev/null +++ b/rt/share/html/Callbacks/SearchCustomerFields/Search/Elements/PickBasics/Default @@ -0,0 +1,46 @@ +<%init> +push @$Conditions, + { + Name => 'Agentnum', + Field => 'Agent', + Op => { + Type => 'component', + Path => '/Elements/SelectBoolean', + Arguments => { TrueVal=> '=', FalseVal => '!=' }, + }, + Value => { + Type => 'component', + Path => '/Elements/SelectCustomerAgent', + }, + }, + { + Name => 'Classnum', + Field => 'Customer Class', + Op => { + Type => 'component', + Path => '/Elements/SelectBoolean', + Arguments => { TrueVal=> '=', FalseVal => '!=' }, + }, + Value => { + Type => 'component', + Path => '/Elements/SelectCustomerClass', + }, + }, + { + Name => 'Tagnum', + Field => 'Tag', + Op => { + Type => 'component', + Path => '/Elements/SelectBoolean', + Arguments => { TrueVal=> '=', FalseVal => '!=' }, + }, + Value => { + Type => 'component', + Path => '/Elements/SelectCustomerTag', + }, + }, +; + +<%ARGS> +$Conditions => [] + diff --git a/rt/share/html/Elements/SelectCustomerAgent b/rt/share/html/Elements/SelectCustomerAgent new file mode 100644 index 000000000..75a1fba63 --- /dev/null +++ b/rt/share/html/Elements/SelectCustomerAgent @@ -0,0 +1,17 @@ +% return if ($RT::URI::freeside::IntegrationType ne 'Internal'); + +<%init> +<%args> +$ShowNullOption => 1 +$Name => undef +$Default => 0 + diff --git a/rt/share/html/Elements/SelectCustomerClass b/rt/share/html/Elements/SelectCustomerClass new file mode 100644 index 000000000..1a03cba8f --- /dev/null +++ b/rt/share/html/Elements/SelectCustomerClass @@ -0,0 +1,17 @@ +% return if ($RT::URI::freeside::IntegrationType ne 'Internal'); + +<%init> +<%args> +$ShowNullOption => 1 +$Name => undef +$Default => 0 + diff --git a/rt/share/html/Elements/SelectCustomerTag b/rt/share/html/Elements/SelectCustomerTag new file mode 100644 index 000000000..862766966 --- /dev/null +++ b/rt/share/html/Elements/SelectCustomerTag @@ -0,0 +1,17 @@ +% return if ($RT::URI::freeside::IntegrationType ne 'Internal'); + +<%init> +<%args> +$ShowNullOption => 1 +$Name => undef +$Default => 0 +