update customer list and zip code report to use cust_location, #940

7 files changed, 48 insertions, 59 deletions

diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index b213bbcbe..2d347e140 100644
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -457,6 +457,8 @@ HASHREF.  Valid parameters are
 
 =item address
 
+=item zip
+
 =item refnum
 
 =item cancelled_pkgs
@@ -516,6 +518,7 @@ sub search {
     'usernum'       => '',
     'status'        => '',
     'address'       => '',
+    'zip'           => '',
     'paydate_year'  => '',
     'invoice_terms' => '',
     'custbatch'     => '',
@@ -578,6 +581,18 @@ sub search {
     )";
   }
 
+  ##
+  # zipcode
+  ##
+  if ( $params->{'zip'} =~ /\S/ ) {
+    my $zip = dbh->quote($params->{'zip'} . '%');
+    push @where, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.zip LIKE $zip
+    )";
+  }
+
   ###
   # refnum
   ###
diff --git a/httemplate/search/cust_main-zip.html b/httemplate/search/cust_main-zip.html
index c317dc36f..08800d431 100644
--- a/httemplate/search/cust_main-zip.html
+++ b/httemplate/search/cust_main-zip.html
@@ -4,8 +4,8 @@
                  'query'       => $sql_query,
                  'count_query' => $count_sql,
                  'header'      => [ 'Zip code', 'Customers', ],
-                 #'fields'      => [ 'zip', 'num_cust', ],
-                 #'links'       => [ '', sub { 'somewhere'; }  ],
+                 'fields'      => [ 0, 1 ],
+                 'links'       => [ '', $link  ],
              )
 %>
 <%init>
@@ -63,48 +63,36 @@ sub strip_plus4 {
   END";
 }
 
-my( $zip, $czip);
-if ( $cgi->param('column') eq 'ship_zip' ) {
-
-  my $casewhen_noship =
-    "CASE WHEN ( ship_last IS NULL OR ship_last = '' ) THEN ";
-
-  $czip = "$casewhen_noship zip ELSE ship_zip END";
-
-  if ( $cgi->param('ignore_plus4') ) {
-    $zip = $casewhen_noship. strip_plus4('zip').
-                   " ELSE ". strip_plus4('ship_zip'). ' END';
-
-  } else {
-    $zip = $casewhen_noship. fieldorempty('zip').
-                   " ELSE ". fieldorempty('ship_zip'). ' END';
-  }
+$cgi->param('column') =~ /^(bill|ship)$/;
+my $location = $1 || 'bill';
+$location .= '_locationnum';
 
+my $zip;
+if ( $cgi->param('ignore_plus4') ) {
+  $zip = strip_plus4('cust_location.zip');
 } else {
-
-  $czip = 'zip';
-
-  if ( $cgi->param('ignore_plus4') ) {
-    $zip = strip_plus4('zip');
-  } else {
-    $zip = fieldorempty('zip');
-  }
-
+  $zip = fieldorempty('cust_location.zip');
 }
 
 # construct the queries and send 'em off
 
+my $join = "JOIN cust_location ON (cust_main.$location = cust_location.locationnum)";
+
 my $sql_query = 
   "SELECT $zip AS zipcode,
           COUNT(*) AS num_cust
      FROM cust_main
+     $join
      $where
      GROUP BY zipcode
-     ORDER BY num_cust DESC
+     ORDER BY num_cust DESC, $zip ASC
   ";
 
-my $count_sql = "select count(distinct $czip) from cust_main $where";
+my $count_sql = 
+  "SELECT COUNT(DISTINCT cust_location.zip)
+    FROM cust_main $join $where";
 
-# XXX should link...
+my $link = [ $p.'search/cust_main.html?zip=', 
+             sub { $_[0]->[0] } ];
 
 </%init>
diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi
index 859ef04e6..a81958e20 100755
--- a/httemplate/search/cust_main.cgi
+++ b/httemplate/search/cust_main.cgi
@@ -81,13 +81,8 @@
       <TR>
         <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('#') |h %></TH>
         <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('Status') |h %></TH>
-        <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('(bill) name') |h %></TH>
-        <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('company') |h %></TH>
-
-%if ( defined dbdef->table('cust_main')->column('ship_last') ) {
-      <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('(service) name') |h %></TH>
-      <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('company') |h %></TH>
-%}
+        <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('Name') |h %></TH>
+        <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('Company') |h %></TH>
 
 %foreach my $addl_header ( @addl_headers ) {
     <TH CLASS="grid" BGCOLOR="#cccccc"><% $addl_header %></TH>
@@ -172,25 +167,6 @@
         <% $pcompany %>
       </TD>
 
-%    if ( defined dbdef->table('cust_main')->column('ship_last') ) {
-%      my($ship_last,$ship_first,$ship_company)=(
-%        $cust_main->ship_last || $cust_main->getfield('last'),
-%        $cust_main->ship_last ? $cust_main->ship_first : $cust_main->first,
-%        $cust_main->ship_last ? $cust_main->ship_company : $cust_main->company,
-%      );
-%      my $pship_company = $ship_company
-%        ? qq!<A HREF="$view"><FONT SIZE=-1>$ship_company</FONT></A>!
-%        : '<FONT SIZE=-1>&nbsp;</FONT>';
-%      
-
-      <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan %>>
-        <A HREF="<% $view %>"><FONT SIZE=-1><% "$ship_last, $ship_first" %></FONT></A>
-      </TD>
-      <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan %>>
-        <% $pship_company %></A>
-      </TD>
-% }
-%
 %    foreach my $addl_col ( @addl_cols ) { 
 % if ( $addl_col eq 'tickets' ) { 
 % if ( @custom_priorities ) { 
diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html
index f75b45197..fa79b4dfb 100755
--- a/httemplate/search/cust_main.html
+++ b/httemplate/search/cust_main.html
@@ -41,7 +41,7 @@ my %search_hash = ();
 
 #scalars
 my @scalars = qw (
-  agentnum status address paydate_year paydate_month invoice_terms
+  agentnum status address zip paydate_year paydate_month invoice_terms
   no_censustract with_geocode custbatch usernum
   cancelled_pkgs
   cust_fields flattened_pkgs
diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html
index 53167c26e..c27771494 100644
--- a/httemplate/search/elements/search-html.html
+++ b/httemplate/search/elements/search-html.html
@@ -337,6 +337,11 @@
 %                       map {
 %                             if ( ref($_) eq 'CODE' ) {
 %                               &{$_}($row);
+%                             } elsif ( ref($row) eq 'ARRAY' and 
+%                                       $_ =~ /^\d+$/ ) {
+%                             # for the 'straight SQL' case: specify fields
+%                             # by position
+%                               $row->[$_];
 %                             } else {
 %                               $row->$_();
 %                             }
diff --git a/httemplate/search/report_cust_main-zip.html b/httemplate/search/report_cust_main-zip.html
index 00cb9ed2c..8bad332a9 100644
--- a/httemplate/search/report_cust_main-zip.html
+++ b/httemplate/search/report_cust_main-zip.html
@@ -8,8 +8,8 @@
         <TD ALIGN="right">Billing or service zip</TD>
         <TD>
           <SELECT NAME="column">
-            <OPTION VALUE="zip">Billing zip
-            <OPTION VALUE="ship_zip">Service zip
+            <OPTION VALUE="bill">Billing zip
+            <OPTION VALUE="ship">Service zip
           </SELECT>
         </TD>
       </TR>
diff --git a/httemplate/search/report_cust_main.html b/httemplate/search/report_cust_main.html
index 526bce26c..a2c6f1c4c 100755
--- a/httemplate/search/report_cust_main.html
+++ b/httemplate/search/report_cust_main.html
@@ -35,6 +35,11 @@
       <TD ALIGN="right" VALIGN="center"><% mt('Address') |h %></TD>
       <TD><INPUT TYPE="text" NAME="address" SIZE=54></TD>
     </TR>
+    
+    <TR>
+      <TD ALIGN="right" VALIGN="center"><% mt('Zip') |h %></TD>
+      <TD><INPUT TYPE="text" NAME="zip" SIZE=12></TD>
+    </TR>
 
     <TR>
         <TD ALIGN="right" VALIGN="center"><% mt('Signup date') |h %></TD>