customer location columns in more reports, #940

diff --git a/FS/FS/UI/Web.pm b/FS/FS/UI/Web.pm
index 91d2034..d6f183d 100644 (file)
--- 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.
 
 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.
 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';
 
   ($location_table, $locationnum) = split(/\./, $location_table);
   $locationnum ||= 'locationnum';
 

-  my $sql = ' LEFT JOIN cust_main ';
+  my $sql = '';

   if ( $cust_table ) {
   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 {
   } else {

-    $sql .= "USING (custnum)";
+    $sql = " LEFT JOIN cust_main USING (custnum)";

   }
 
   if ( !@cust_fields or grep /^bill_/, @cust_fields ) {
   }
 
   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 eb75664..c9c71f2 100644 (file)
--- 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',
 
 my $sql_query = {
   'table'     => 'cust_main',

+  'addl_from' => FS::UI::Web::join_cust_main('cust_main'),

   'hashref'   => {},
   'select'    => join(',',
                    #'cust_main.*',
   'hashref'   => {},
   'select'    => join(',',
                    #'cust_main.*',

-                   'custnum',
+                   'cust_main.custnum',

                    $range_cols,
                    $packages_cols,
                    FS::UI::Web::cust_sql_fields(),
                    $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 b9da7ef..3dc54d9 100755 (executable)
--- 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;
 
   #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') ) {
   my $group_by = '';
 
   if ( $cgi->param('tax_names') ) {