Merge branch 'master' of git.freeside.biz:/home/git/freeside

31 files changed, 158 insertions, 81 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
diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html
index 3c0530e4f..88cdaf5ab 100755
--- a/httemplate/search/cust_bill.html
+++ b/httemplate/search/cust_bill.html
@@ -62,7 +62,7 @@
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('List invoices');
 
-my $join_cust_main = 'LEFT JOIN cust_main USING ( custnum )';
+my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill');
 #here is the agent virtualization
 my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql;
 
@@ -198,7 +198,6 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) {
   };
 
 }
-
 my $link  = [ "${p}view/cust_bill.cgi?", 'invnum', ];
 my $clink = sub {
   my $cust_bill = shift;
diff --git a/httemplate/search/cust_bill_event.cgi b/httemplate/search/cust_bill_event.cgi
index 90c89139c..00f7cd564 100644
--- a/httemplate/search/cust_bill_event.cgi
+++ b/httemplate/search/cust_bill_event.cgi
@@ -100,7 +100,7 @@ my $where = 'WHERE '. FS::cust_bill_event->search_sql_where( \%search );
 
 my $join = 'LEFT JOIN part_bill_event USING ( eventpart ) '.
            'LEFT JOIN cust_bill       USING ( invnum    ) '.
-           'LEFT JOIN cust_main       USING ( custnum   ) ';
+           FS::UI::Web::join_cust_main('cust_bill');
 
 my $sql_query = {
   'table'     => 'cust_bill_event',
diff --git a/httemplate/search/cust_bill_pay.html b/httemplate/search/cust_bill_pay.html
index 79de74985..871227aa4 100644
--- a/httemplate/search/cust_bill_pay.html
+++ b/httemplate/search/cust_bill_pay.html
@@ -117,8 +117,8 @@ my $where = 'WHERE '. join(' AND ', @search);
 #
 my $count_query = 'SELECT COUNT(*), SUM(amount)
                    FROM cust_bill_pay
-                     LEFT JOIN cust_bill USING ( invnum  )
-                     LEFT JOIN cust_main USING ( custnum ) '.
+                     LEFT JOIN cust_bill USING ( invnum  ) '.
+                     FS::UI::Web::join_cust_main('cust_bill') .
                   $where;
 
 my $sql_query   = {
@@ -137,8 +137,8 @@ my $sql_query   = {
   'hashref'   => {},
   'extra_sql' => $where,
   'addl_from' => 'LEFT JOIN cust_bill   USING ( invnum  )
-                  LEFT JOIN cust_pay    USING ( paynum )
-                  LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum )',
+                  LEFT JOIN cust_pay    USING ( paynum ) '.
+                  FS::UI::Web::join_cust_main('cust_bill')
 };
 
 my $cust_bill_link = sub {
diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index 1e67e9320..6a9f8775e 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -222,9 +222,9 @@ if ( $conf->exists('enable_taxclasses') ) {
 
 # valid in both the tax and non-tax cases
 my $join_cust = 
-  " LEFT JOIN cust_bill USING (invnum)
-    LEFT JOIN cust_main USING (custnum)
-  ";
+  " LEFT JOIN cust_bill USING (invnum)".
+  # use cust_pkg.locationnum if it exists
+  FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
 
 #agent virtualization
 my $agentnums_sql =
@@ -647,7 +647,7 @@ $where &&= "WHERE $where";
 
 my $query = {
   'table'     => 'cust_bill_pkg',
-  'addl_from' => "$join_cust $join_pkg",
+  'addl_from' => "$join_pkg $join_cust",
   'hashref'   => {},
   'select'    => join(",\n", @select ),
   'extra_sql' => $where,
@@ -656,7 +656,7 @@ my $query = {
 
 my $count_query =
   'SELECT ' . join(',', @total) .
-  " FROM cust_bill_pkg $join_cust $join_pkg
+  " FROM cust_bill_pkg $join_pkg $join_cust
   $where";
 
 @peritem_desc = map {emt($_)} @peritem_desc;
diff --git a/httemplate/search/cust_bill_pkg_discount.html b/httemplate/search/cust_bill_pkg_discount.html
index bb8038a44..4225e91e1 100644
--- a/httemplate/search/cust_bill_pkg_discount.html
+++ b/httemplate/search/cust_bill_pkg_discount.html
@@ -127,12 +127,12 @@ my $join_cust_pkg_discount =
   'LEFT JOIN cust_pkg_discount USING (pkgdiscountnum)';
 
 my $join_cust =
-  '      JOIN cust_bill_pkg USING ( billpkgnum )
-         JOIN cust_bill USING ( invnum ) 
-    LEFT JOIN cust_main USING ( custnum ) ';
+  '         JOIN cust_bill USING ( invnum ) '.
+  FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
 
 my $join_pkg =
-  ' LEFT JOIN cust_pkg ON ( cust_bill_pkg.pkgnum = cust_pkg.pkgnum )
+  '    JOIN cust_bill_pkg USING ( billpkgnum )
+  LEFT JOIN cust_pkg ON ( cust_bill_pkg.pkgnum = cust_pkg.pkgnum )
   LEFT JOIN part_pkg USING ( pkgpart ) ';
   #LEFT JOIN part_pkg AS override
   #  ON pkgpart_override = override.pkgpart ';
@@ -140,7 +140,7 @@ my $join_pkg =
 my $where = ' WHERE '. join(' AND ', @where);
 
 $count_query .=
-  " FROM cust_bill_pkg_discount $join_cust_pkg_discount $join_cust $join_pkg ".
+  " FROM cust_bill_pkg_discount $join_cust_pkg_discount $join_pkg $join_cust ".
   $where;
 
 my @select = (
@@ -155,7 +155,7 @@ push @select, 'cust_main.custnum',
 
 my $query = {
   'table'     => 'cust_bill_pkg_discount',
-  'addl_from' => "$join_cust_pkg_discount $join_cust $join_pkg",
+  'addl_from' => "$join_cust_pkg_discount $join_pkg $join_cust",
   'hashref'   => {},
   'select'    => join(', ', @select ),
   'extra_sql' => $where,
diff --git a/httemplate/search/cust_credit.html b/httemplate/search/cust_credit.html
index f5d8fa19f..d1f41df00 100755
--- a/httemplate/search/cust_credit.html
+++ b/httemplate/search/cust_credit.html
@@ -137,7 +137,7 @@ my $where = 'WHERE '. join(' AND ', @search);
 
 my $count_query = 'SELECT COUNT(*), SUM(amount) ';
 $count_query .= ', SUM(' . FS::cust_credit->unapplied_sql . ') ' if $unapplied;
-$count_query .= 'FROM cust_credit LEFT JOIN cust_main USING ( custnum ) '.
+$count_query .= 'FROM cust_credit'. FS::UI::Web::join_cust_main('cust_credit').
                   $where;
 
 my @count_addl = ( $money_char.'%.2f total credited (gross)' );
@@ -148,7 +148,7 @@ my $sql_query   = {
   'select'    => join(', ',@select),
   'hashref'   => {},
   'extra_sql' => $where,
-  'addl_from' => 'LEFT JOIN cust_main USING ( custnum )',
+  'addl_from' => FS::UI::Web::join_cust_main('cust_credit')
 };
 
 </%init>
diff --git a/httemplate/search/cust_credit_bill.html b/httemplate/search/cust_credit_bill.html
index 9fd6a987a..7e11ee676 100644
--- a/httemplate/search/cust_credit_bill.html
+++ b/httemplate/search/cust_credit_bill.html
@@ -103,8 +103,8 @@ my $where = 'WHERE '. join(' AND ', @search);
 #
 my $count_query = 'SELECT COUNT(*), SUM(amount)
                    FROM cust_credit_bill
-                     LEFT JOIN cust_bill USING ( invnum  )
-                     LEFT JOIN cust_main USING ( custnum ) '.
+                     LEFT JOIN cust_bill USING ( invnum  ) '.
+                  FS::UI::Web::join_cust_main('cust_bill') .
                   $where;
 
 my $sql_query   = {
@@ -121,8 +121,8 @@ my $sql_query   = {
   'hashref'   => {},
   'extra_sql' => $where,
   'addl_from' => 'LEFT JOIN cust_bill   USING ( invnum  )
-                  LEFT JOIN cust_credit USING ( crednum )
-                  LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum )',
+                  LEFT JOIN cust_credit USING ( crednum )'.
+                  FS::UI::Web::join_cust_main('cust_bill')
 };
 
 my $cust_bill_link = sub {
diff --git a/httemplate/search/cust_credit_bill_pkg.html b/httemplate/search/cust_credit_bill_pkg.html
index 8b21f371c..26da106e2 100644
--- a/httemplate/search/cust_credit_bill_pkg.html
+++ b/httemplate/search/cust_credit_bill_pkg.html
@@ -383,8 +383,8 @@ my $count_query = "SELECT COUNT(DISTINCT creditbillpkgnum),
                           SUM(cust_credit_bill_pkg.amount)";
 
 my $join_cust =
-  '      JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum ) 
-    LEFT JOIN cust_main ON ( cust_bill.custnum = cust_main.custnum ) ';
+  '      JOIN cust_bill ON ( cust_bill_pkg.invnum = cust_bill.invnum )'.
+  FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
 
 
 my $join_pkg;
diff --git a/httemplate/search/cust_credit_refund.html b/httemplate/search/cust_credit_refund.html
index 75138e99d..5d89ebe67 100644
--- a/httemplate/search/cust_credit_refund.html
+++ b/httemplate/search/cust_credit_refund.html
@@ -103,8 +103,8 @@ my $where = 'WHERE '. join(' AND ', @search);
 #
 my $count_query = 'SELECT COUNT(*), SUM(cust_credit_refund.amount)
                    FROM cust_credit_refund
-                     LEFT JOIN cust_credit USING ( crednum )
-                     LEFT JOIN cust_main   USING ( custnum ) '.
+                     LEFT JOIN cust_credit USING ( crednum ) '.
+                  FS::UI::Web::join_cust_main('cust_credit') .
                   $where;
 
 my $sql_query   = {
@@ -121,8 +121,8 @@ my $sql_query   = {
   'hashref'   => {},
   'extra_sql' => $where,
   'addl_from' => 'LEFT JOIN cust_credit USING ( crednum   )
-                  LEFT JOIN cust_refund USING ( refundnum )
-                  LEFT JOIN cust_main ON ( cust_credit.custnum = cust_main.custnum )',
+                  LEFT JOIN cust_refund USING ( refundnum )'.
+                  FS::UI::Web::join_cust_main('cust_credit')
 };
 
 #my $cust_credit_link = sub {
diff --git a/httemplate/search/cust_event.html b/httemplate/search/cust_event.html
index deb34b9e5..bd6ba6974 100644
--- a/httemplate/search/cust_event.html
+++ b/httemplate/search/cust_event.html
@@ -175,7 +175,13 @@ $search{'ending'}    = $ending;
 
 my $where = ' WHERE '. FS::cust_event->search_sql_where( \%search );
 
-my $join = FS::cust_event->join_sql();
+my $join = FS::cust_event->join_sql() .
+  'LEFT JOIN cust_location bill_location '.
+  'ON (cust_main.bill_locationnum = bill_location.locationnum) '.
+  'LEFT JOIN cust_location ship_location '.
+  'ON (cust_main.ship_locationnum = ship_location.locationnum)';
+  # warning: does not show the true service address for package events.
+  # the query to do that would be painfully slow.
 
 my $sql_query = {
   'table'     => 'cust_event',
diff --git a/httemplate/search/cust_pkg_discount.html b/httemplate/search/cust_pkg_discount.html
index d70c3116f..bd28da87d 100644
--- a/httemplate/search/cust_pkg_discount.html
+++ b/httemplate/search/cust_pkg_discount.html
@@ -92,8 +92,8 @@ my $count_query = "SELECT COUNT(*), SUM(amount)";
 
 my $join = ' LEFT JOIN discount  USING ( discountnum )
              LEFT JOIN cust_pkg  USING ( pkgnum )
-             LEFT JOIN part_pkg  USING ( pkgpart )
-             LEFT JOIN cust_main USING ( custnum ) ';
+             LEFT JOIN part_pkg  USING ( pkgpart ) '.
+             FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
 
 my $where = ' WHERE '. join(' AND ', @where);
 
diff --git a/httemplate/search/cust_svc.html b/httemplate/search/cust_svc.html
index 2adcbd76f..e2a83b7de 100644
--- a/httemplate/search/cust_svc.html
+++ b/httemplate/search/cust_svc.html
@@ -62,7 +62,7 @@ if ( length( $cgi->param('search_svc') ) ) {
 
   my $addl_from = ' 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 @extra_sql = ();
 
diff --git a/httemplate/search/cust_tax_exempt.cgi b/httemplate/search/cust_tax_exempt.cgi
index 3704b208a..f075d1926 100644
--- a/httemplate/search/cust_tax_exempt.cgi
+++ b/httemplate/search/cust_tax_exempt.cgi
@@ -50,9 +50,7 @@
 %>
 <%init>
 
-my $join_cust = "
-    LEFT JOIN cust_main USING ( custnum )
-";
+my $join_cust = FS::UI::Web::join_cust_main('cust_tax_exempt');
 
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('View customer tax exemptions');
diff --git a/httemplate/search/cust_tax_exempt_pkg.cgi b/httemplate/search/cust_tax_exempt_pkg.cgi
index 1b767f846..7e1a239b4 100644
--- a/httemplate/search/cust_tax_exempt_pkg.cgi
+++ b/httemplate/search/cust_tax_exempt_pkg.cgi
@@ -82,9 +82,8 @@
 <%once>
 
 my $join_cust = "
-    JOIN cust_bill USING ( invnum )
-    LEFT JOIN cust_main USING ( custnum )
-";
+    JOIN cust_bill USING ( invnum )" .
+    FS::UI::Web::join_cust_main('cust_bill', 'cust_pkg');
 
 my $join_pkg = "
     LEFT JOIN cust_pkg USING ( pkgnum )
@@ -93,8 +92,8 @@ my $join_pkg = "
 
 my $join = "
     JOIN cust_bill_pkg USING ( billpkgnum )
-    $join_cust
     $join_pkg
+    $join_cust
 ";
 
 </%once>
diff --git a/httemplate/search/inventory_item.html b/httemplate/search/inventory_item.html
index 086c8e92d..64722ff41 100644
--- a/httemplate/search/inventory_item.html
+++ b/httemplate/search/inventory_item.html
@@ -157,7 +157,7 @@ my $link_cust = sub {
 my $addl_from = ' 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 $areboxes = 0;
 
 my $sub_checkbox = sub {
diff --git a/httemplate/search/phone_avail.html b/httemplate/search/phone_avail.html
index 1335379ae..312c84ea9 100644
--- a/httemplate/search/phone_avail.html
+++ b/httemplate/search/phone_avail.html
@@ -125,9 +125,11 @@ my $search = scalar(@search)
 my $addl_from = ' 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 $count_query = "SELECT COUNT(*) FROM phone_avail $search"; #$addl_from?
+# All of these relationships are left joined in the many-to-one direction,
+# so including $addl_from won't affect the count.  Logic!
 
 my $hashref = {};
 $hashref->{'ordernum'} = $1 if $cgi->param('ordernum') =~ /^(\d+)$/;
diff --git a/httemplate/search/quotation.html b/httemplate/search/quotation.html
index 259c85c22..fbc35bea1 100755
--- a/httemplate/search/quotation.html
+++ b/httemplate/search/quotation.html
@@ -72,7 +72,7 @@ die "access denied"
   unless $curuser->access_right('List quotations');
 
 my $join_prospect_main = 'LEFT JOIN prospect_main USING ( prospectnum )';
-my $join_cust_main = 'LEFT JOIN cust_main ON ( quotation.custnum = cust_main.custnum )';
+my $join_cust_main = FS::UI::Web::join_cust_main('quotation');
 
 #here is the agent virtualization
 my $agentnums_sql = ' (    '. $curuser->agentnums_sql( table=>'prospect_main' ).
diff --git a/httemplate/search/svc_dish.cgi b/httemplate/search/svc_dish.cgi
index 94da03537..a2e56d24d 100755
--- a/httemplate/search/svc_dish.cgi
+++ b/httemplate/search/svc_dish.cgi
@@ -61,7 +61,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 my $addl_from = ' 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');
 
 #here is the agent virtualization
 push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql(
diff --git a/httemplate/search/svc_domain.cgi b/httemplate/search/svc_domain.cgi
index 9827b8d38..508d88931 100755
--- a/httemplate/search/svc_domain.cgi
+++ b/httemplate/search/svc_domain.cgi
@@ -66,7 +66,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 my $addl_from = ' 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');
 
 #here is the agent virtualization
 push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( 
diff --git a/httemplate/search/svc_external.cgi b/httemplate/search/svc_external.cgi
index cb51d44fd..7d57697b1 100755
--- a/httemplate/search/svc_external.cgi
+++ b/httemplate/search/svc_external.cgi
@@ -90,7 +90,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 my $addl_from = ' 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');
 
 #here is the agent virtualization
 push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql(
diff --git a/httemplate/search/svc_forward.cgi b/httemplate/search/svc_forward.cgi
index f17f131ab..eda790a0a 100755
--- a/httemplate/search/svc_forward.cgi
+++ b/httemplate/search/svc_forward.cgi
@@ -67,7 +67,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 my $addl_from = ' 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');
 
 #here is the agent virtualization
 push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( 
diff --git a/httemplate/search/svc_hardware.cgi b/httemplate/search/svc_hardware.cgi
index ec09be82b..085d7500a 100644
--- a/httemplate/search/svc_hardware.cgi
+++ b/httemplate/search/svc_hardware.cgi
@@ -44,8 +44,8 @@ die "access denied"
 my $addl_from = '
  LEFT JOIN cust_svc  USING ( svcnum  )
  LEFT JOIN part_svc  USING ( svcpart )
- LEFT JOIN cust_pkg  USING ( pkgnum  )
- LEFT JOIN cust_main USING ( custnum )
+ LEFT JOIN cust_pkg  USING ( pkgnum  )'.
+ FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg').'
  LEFT JOIN hardware_type USING ( typenum )';
 
 my @extra_sql;
diff --git a/httemplate/search/svc_www.cgi b/httemplate/search/svc_www.cgi
index adc31c88a..5132f9470 100755
--- a/httemplate/search/svc_www.cgi
+++ b/httemplate/search/svc_www.cgi
@@ -73,7 +73,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 my $addl_from = ' 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');
 
 #here is the agent virtualization
 push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql(
diff --git a/httemplate/search/unearned_detail.html b/httemplate/search/unearned_detail.html
index f61de052e..425aa5a4e 100644
--- a/httemplate/search/unearned_detail.html
+++ b/httemplate/search/unearned_detail.html
@@ -210,8 +210,8 @@ push @select, '(edate - 82799) AS before_edate';
 #usage always excluded
 
 # always 'nottax', not 'istax'
-$join_cust =  '        JOIN cust_bill USING ( invnum )
-                  LEFT JOIN cust_main USING ( custnum ) ';
+$join_cust =  '        JOIN cust_bill USING ( invnum ) '.
+                  FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
 
 $join_pkg .=  ' LEFT JOIN cust_pkg USING ( pkgnum )
                 LEFT JOIN part_pkg USING ( pkgpart )
@@ -222,7 +222,7 @@ my $where = ' WHERE '. join(' AND ', @where);
 
 my $count_query = "SELECT COUNT(DISTINCT billpkgnum), 
   SUM( $unearned_base ), SUM( $unearned_sql )
-  FROM cust_bill_pkg $join_cust $join_pkg $where";
+  FROM cust_bill_pkg $join_pkg $join_cust $where";
 
 push @select, 'part_pkg.pkg',
               'part_pkg.freq',
@@ -231,7 +231,7 @@ push @select, 'part_pkg.pkg',
 
 my $query = {
   'table'     => 'cust_bill_pkg',
-  'addl_from' => "$join_cust $join_pkg",
+  'addl_from' => "$join_pkg $join_cust",
   'hashref'   => {},
   'select'    => join(",\n", @select ),
   'extra_sql' => $where,
diff --git a/httemplate/search/unprovisioned_services.html b/httemplate/search/unprovisioned_services.html
index f85e4fb19..89d5f2d66 100644
--- a/httemplate/search/unprovisioned_services.html
+++ b/httemplate/search/unprovisioned_services.html
@@ -74,7 +74,8 @@ my $search = " where cust_pkg.cancel is null and pkg_svc.quantity > 0 and "
 	    . " cust_svc.pkgnum = cust_pkg.pkgnum and "
 	    . " cust_svc.svcpart = pkg_svc.svcpart) $svcpart_limit";
 
-my $addl_from = " join pkg_svc using (pkgpart) join cust_main using (custnum) ";
+my $addl_from = " join pkg_svc using (pkgpart) ".
+            FS::UI::Web::join_cust_main('cust_pkg', 'cust_pkg');
 
 # this was very painful to derive but it appears correct
 #select cust_pkg.custnum,cust_pkg.pkgpart,cust_pkg.pkgnum, pkg_svc.svcpart from cust_pkg join