From 0704f3f641deb525399521368f1e6ba9ff5b71cd Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Wed, 6 Mar 2013 15:04:14 -0800 Subject: [PATCH] customer location columns in more reports, #940 --- FS/FS/UI/Web.pm | 10 +++++++--- httemplate/search/elements/cust_main_dayranges.html | 3 ++- httemplate/search/elements/cust_pay_or_refund.html | 2 +- 3 files changed, 10 insertions(+), 5 deletions(-) diff --git a/FS/FS/UI/Web.pm b/FS/FS/UI/Web.pm index 91d2034dd..d6f183d8e 100644 --- a/FS/FS/UI/Web.pm +++ b/FS/FS/UI/Web.pm @@ -378,6 +378,9 @@ Otherwise, this function will assume the field is named "custnum". If the argument isn't present at all, the join will just say "USING (custnum)", which might work. +As a special case, if TABLE is 'cust_main', only the joins to cust_location +will be returned. + LOCATION_TABLE is an optional table name to use for joining ship_location, in case your query also includes package information and you want the "service address" columns to reflect package addresses. @@ -392,11 +395,12 @@ sub join_cust_main { ($location_table, $locationnum) = split(/\./, $location_table); $locationnum ||= 'locationnum'; - my $sql = ' LEFT JOIN cust_main '; + my $sql = ''; if ( $cust_table ) { - $sql .= "ON (cust_main.custnum = $cust_table.$custnum)"; + $sql = " LEFT JOIN cust_main ON (cust_main.custnum = $cust_table.$custnum)" + unless $cust_table eq 'cust_main'; } else { - $sql .= "USING (custnum)"; + $sql = " LEFT JOIN cust_main USING (custnum)"; } if ( !@cust_fields or grep /^bill_/, @cust_fields ) { diff --git a/httemplate/search/elements/cust_main_dayranges.html b/httemplate/search/elements/cust_main_dayranges.html index eb7566494..c9c71f274 100644 --- a/httemplate/search/elements/cust_main_dayranges.html +++ b/httemplate/search/elements/cust_main_dayranges.html @@ -172,10 +172,11 @@ my $count_sql = "select count(*) from cust_main $where"; my $sql_query = { 'table' => 'cust_main', + 'addl_from' => FS::UI::Web::join_cust_main('cust_main'), 'hashref' => {}, 'select' => join(',', #'cust_main.*', - 'custnum', + 'cust_main.custnum', $range_cols, $packages_cols, FS::UI::Web::cust_sql_fields(), diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index b9da7ef06..3dc54d936 100755 --- a/httemplate/search/elements/cust_pay_or_refund.html +++ b/httemplate/search/elements/cust_pay_or_refund.html @@ -424,7 +424,7 @@ if ( $cgi->param('magic') ) { #here is the agent virtualization push @search, $curuser->agentnums_sql; - my $addl_from = ' LEFT JOIN cust_main USING ( custnum ) '; + my $addl_from = FS::UI::Web::join_cust_main('cust_pay'); my $group_by = ''; if ( $cgi->param('tax_names') ) { -- 2.11.0