X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main-zip.html;h=e22d3ea2e8ae1213c2d983d50daa36cba3aa6dea;hb=8572f2347f879d414a60e8e0dc111fa927d66c14;hp=c317dc36f22ea0f93f191ccc65af1332504e8093;hpb=c2aca3624124622e018f9cc1e02be0d0e93d3aa0;p=freeside.git diff --git a/httemplate/search/cust_main-zip.html b/httemplate/search/cust_main-zip.html index c317dc36f..e22d3ea2e 100644 --- a/httemplate/search/cust_main-zip.html +++ b/httemplate/search/cust_main-zip.html @@ -1,13 +1,11 @@ -<% include( 'elements/search.html', +<& elements/search.html, 'title' => 'Zip code Search Results', 'name' => 'zip codes', 'query' => $sql_query, 'count_query' => $count_sql, 'header' => [ 'Zip code', 'Customers', ], - #'fields' => [ 'zip', 'num_cust', ], - #'links' => [ '', sub { 'somewhere'; } ], - ) -%> + 'links' => [ '', $link ], +&> <%init> die "access denied" @@ -63,48 +61,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] } ];