make customer location columns available in various reports, #940

6 files changed, 97 insertions, 25 deletions

diff --git a/FS/FS/UI/Web.pm b/FS/FS/UI/Web.pm
index c2ea0a61c..91d2034dd 100644
--- a/FS/FS/UI/Web.pm
+++ b/FS/FS/UI/Web.pm
@@ -235,20 +235,20 @@ sub cust_header {
     '(service) Name'           => 'ship_contact',
     '(bill) Company'           => 'company',
     '(service) Company'        => 'ship_company',
-    'Address 1'                => 'address1',
-    'Address 2'                => 'address2',
-    'City'                     => 'city',
-    'State'                    => 'state',
-    'Zip'                      => 'zip',
+    'Address 1'                => 'bill_address1',
+    'Address 2'                => 'bill_address2',
+    'City'                     => 'bill_city',
+    'State'                    => 'bill_state',
+    'Zip'                      => 'bill_zip',
     'Country'                  => 'country_full',
     'Day phone'                => 'daytime', # XXX should use msgcat, but how?
     'Night phone'              => 'night',   # XXX should use msgcat, but how?
     'Fax number'               => 'fax',
-    '(bill) Address 1'         => 'address1',
-    '(bill) Address 2'         => 'address2',
-    '(bill) City'              => 'city',
-    '(bill) State'             => 'state',
-    '(bill) Zip'               => 'zip',
+    '(bill) Address 1'         => 'bill_address1',
+    '(bill) Address 2'         => 'bill_address2',
+    '(bill) City'              => 'bill_city',
+    '(bill) State'             => 'bill_state',
+    '(bill) Zip'               => 'bill_zip',
     '(bill) Country'           => 'country_full',
     '(bill) Day phone'         => 'daytime', # XXX should use msgcat, but how?
     '(bill) Night phone'       => 'night',   # XXX should use msgcat, but how?
@@ -335,17 +335,21 @@ setting is supplied, the <B>cust-fields</B> configuration value.
 sub cust_sql_fields {
 
   my @fields = qw( last first company );
-  push @fields, map "ship_$_", @fields;
-  push @fields, 'country';
+#  push @fields, map "ship_$_", @fields;
 
   cust_header(@_);
   #inefficientish, but tiny lists and only run once per page
 
-  my @add_fields = qw( address1 address2 city state zip daytime night fax );
-  push @fields,
-    grep { my $field = $_; grep { $_ eq $field } @cust_fields }
-         ( @add_fields, ( map "ship_$_", @add_fields ), 'payby' );
-
+  my @location_fields;
+  foreach my $field (qw( address1 address2 city state zip )) {
+    foreach my $pre ('bill_','ship_') {
+      if ( grep { $_ eq $pre.$field } @cust_fields ) {
+        push @location_fields, $pre.'location.'.$field.' AS '.$pre.$field;
+      }
+    }
+  }
+  
+  push @fields, 'payby' if grep { $_ eq 'payby'} @cust_fields;
   push @fields, 'agent_custid';
 
   my @extra_fields = ();
@@ -353,7 +357,67 @@ sub cust_sql_fields {
     push @extra_fields, FS::cust_main->balance_sql . " AS current_balance";
   }
 
-  map("cust_main.$_", @fields), @extra_fields;
+  map("cust_main.$_", @fields), @location_fields, @extra_fields;
+}
+
+=item join_cust_main [ TABLE[.CUSTNUM] ] [ LOCATION_TABLE[.LOCATIONNUM] ]
+
+Returns an SQL join phrase for the FROM clause so that the fields listed
+in L<cust_sql_fields> will be available.  Currently joins to cust_main 
+itself, as well as cust_location (under the aliases 'bill_location' and
+'ship_location') if address fields are needed.  L<cust_header()> should have
+been called already.
+
+All of these will be left joins; if you want to exclude rows with no linked
+cust_main record (or bill_location/ship_location), you can do so in the 
+WHERE clause.
+
+TABLE is the table containing the custnum field.  If CUSTNUM (a field name
+in that table) is specified, that field will be joined to cust_main.custnum.
+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.
+
+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.
+
+=cut
+
+sub join_cust_main {
+  my ($cust_table, $location_table) = @_;
+  my ($custnum, $locationnum);
+  ($cust_table, $custnum) = split(/\./, $cust_table);
+  $custnum ||= 'custnum';
+  ($location_table, $locationnum) = split(/\./, $location_table);
+  $locationnum ||= 'locationnum';
+
+  my $sql = ' LEFT JOIN cust_main ';
+  if ( $cust_table ) {
+    $sql .= "ON (cust_main.custnum = $cust_table.$custnum)";
+  } else {
+    $sql .= "USING (custnum)";
+  }
+
+  if ( !@cust_fields or grep /^bill_/, @cust_fields ) {
+
+    $sql .= ' LEFT JOIN cust_location bill_location'.
+            ' ON (bill_location.locationnum = cust_main.bill_locationnum)';
+
+  }
+
+  if ( !@cust_fields or grep /^ship_/, @cust_fields ) {
+
+    if (!$location_table) {
+      $location_table = 'cust_main';
+      $locationnum = 'ship_locationnum';
+    }
+
+    $sql .= ' LEFT JOIN cust_location ship_location'.
+            " ON (ship_location.locationnum = $location_table.$locationnum) ";
+  }
+
+  $sql;
 }
 
 =item cust_fields OBJECT [ CUST_FIELDS_VALUE ]
diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index 1047890c3..349f3e348 100644
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -814,6 +814,12 @@ sub search {
   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 
   my $addl_from = '';
+  # always make address fields available in results
+  for my $pre ('bill_', 'ship_') {
+    $addl_from .= 
+      'LEFT JOIN cust_location AS '.$pre.'location '.
+      'ON (cust_main.'.$pre.'locationnum = '.$pre.'location.locationnum) ';
+  }
 
   my $count_query = "SELECT COUNT(*) FROM cust_main $extra_sql";
 
diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm
index 6d85a11f2..c2077c533 100644
--- a/FS/FS/cust_pkg.pm
+++ b/FS/FS/cust_pkg.pm
@@ -603,7 +603,9 @@ replace methods.
 sub check {
   my $self = shift;
 
-  $self->locationnum('') if !$self->locationnum || $self->locationnum == -1;
+  if ( !$self->locationnum or $self->locationnum == -1 ) {
+    $self->set('locationnum', $self->cust_main->ship_locationnum);
+  }
 
   my $error = 
     $self->ut_numbern('pkgnum')
@@ -3817,10 +3819,10 @@ sub search {
 
   my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
 
-  my $addl_from = 'LEFT JOIN cust_main USING ( custnum  ) '.
-                  'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
+  my $addl_from = 'LEFT JOIN part_pkg  USING ( pkgpart  ) '.
                   'LEFT JOIN pkg_class ON ( part_pkg.classnum = pkg_class.classnum ) '.
-                  'LEFT JOIN cust_location USING ( locationnum ) ';
+                  'LEFT JOIN cust_location USING ( locationnum ) '.
+                  FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
 
   my $select;
   my $count_query;
diff --git a/FS/FS/svc_Common.pm b/FS/FS/svc_Common.pm
index 21710226a..f7f0b4094 100644
--- a/FS/FS/svc_Common.pm
+++ b/FS/FS/svc_Common.pm
@@ -1324,7 +1324,7 @@ sub search {
     'LEFT JOIN cust_svc  USING ( svcnum  )',
     'LEFT JOIN part_svc  USING ( svcpart )',
     'LEFT JOIN cust_pkg  USING ( pkgnum  )',
-    'LEFT JOIN cust_main USING ( custnum )',
+    FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'),
   );
 
   my @where = ();
diff --git a/FS/FS/svc_acct.pm b/FS/FS/svc_acct.pm
index bd71b0ba1..39f969f09 100644
--- a/FS/FS/svc_acct.pm
+++ b/FS/FS/svc_acct.pm
@@ -2829,7 +2829,7 @@ sub search {
     ' LEFT JOIN cust_svc  USING ( svcnum  ) ',
     ' LEFT JOIN part_svc  USING ( svcpart ) ',
     ' LEFT JOIN cust_pkg  USING ( pkgnum  ) ',
-    ' LEFT JOIN cust_main USING ( custnum ) ',
+    FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg')
   );
 
   my @where = ();
diff --git a/FS/FS/svc_broadband.pm b/FS/FS/svc_broadband.pm
index af8135304..1a3adf414 100755
--- a/FS/FS/svc_broadband.pm
+++ b/FS/FS/svc_broadband.pm
@@ -182,7 +182,7 @@ sub search {
     'LEFT JOIN cust_svc  USING ( svcnum  )',
     'LEFT JOIN part_svc  USING ( svcpart )',
     'LEFT JOIN cust_pkg  USING ( pkgnum  )',
-    'LEFT JOIN cust_main USING ( custnum )',
+    FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg'),
   );
 
   # based on FS::svc_acct::search, probably the most mature of the bunch