X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main-zip.html;fp=httemplate%2Fsearch%2Fcust_main-zip.html;h=08800d431762939af4b7c128919b59f77f7e2497;hb=f578e57b593adb9254b2e465c73116a0e0e617b5;hp=c317dc36f22ea0f93f191ccc65af1332504e8093;hpb=7b6434395ecdbf8013309d22564b146736d6e927;p=freeside.git

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>