From b594ce910540fae472cfa1fb132f658d4846c638 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Sat, 27 Apr 2013 13:22:24 -0700 Subject: allow service smart_search to find unlinked svc_* records, related to #22385 --- FS/FS/cust_svc.pm | 20 +++++++++++--------- 1 file changed, 11 insertions(+), 9 deletions(-) (limited to 'FS/FS/cust_svc.pm') diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm index 1653840..cab80a8 100644 --- a/FS/FS/cust_svc.pm +++ b/FS/FS/cust_svc.pm @@ -863,32 +863,34 @@ sub smart_search_param { my @or = map { my $table = $_; my $search_sql = "FS::$table"->search_sql($string); - " ( svcdb = '$table' - AND 0 < ( SELECT COUNT(*) FROM $table - WHERE $table.svcnum = cust_svc.svcnum - AND $search_sql - ) - ) "; + + "SELECT $table.svcnum AS svcnum, '$table' AS svcdb ". + "FROM $table WHERE $search_sql"; } FS::part_svc->svc_tables; if ( $string =~ /^(\d+)$/ ) { - unshift @or, " ( agent_svcid IS NOT NULL AND agent_svcid = $1 ) "; + unshift @or, "SELECT cust_svc.svcnum, NULL FROM cust_svc WHERE agent_svcid = $1"; } - my @extra_sql = ' ( '. join(' OR ', @or). ' ) '; + my $addl_from = " RIGHT JOIN (\n" . join("\nUNION\n", @or) . "\n) AS svc_all ". + " ON (svc_all.svcnum = cust_svc.svcnum) "; + + my @extra_sql; push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( 'null_right' => 'View/link unlinked services' ); my $extra_sql = ' WHERE '.join(' AND ', @extra_sql); #for agentnum - my $addl_from = ' LEFT JOIN cust_pkg USING ( pkgnum )'. + $addl_from .= ' LEFT JOIN cust_pkg USING ( pkgnum )'. FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'). ' LEFT JOIN part_svc USING ( svcpart )'; ( 'table' => 'cust_svc', + 'select' => 'svc_all.svcnum AS svcnum, '. + 'COALESCE(svc_all.svcdb, part_svc.svcdb) AS svcdb', 'addl_from' => $addl_from, 'hashref' => {}, 'extra_sql' => $extra_sql, -- cgit v1.1