From f578e57b593adb9254b2e465c73116a0e0e617b5 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Sun, 29 Jul 2012 17:57:24 -0700 Subject: [PATCH] update customer list and zip code report to use cust_location, #940 --- FS/FS/cust_main/Search.pm | 15 +++++++++ httemplate/search/cust_main-zip.html | 48 +++++++++++------------------ httemplate/search/cust_main.cgi | 28 ++--------------- httemplate/search/cust_main.html | 2 +- httemplate/search/elements/search-html.html | 5 +++ httemplate/search/report_cust_main-zip.html | 4 +-- httemplate/search/report_cust_main.html | 5 +++ 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] } ]; 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 @@ <% mt('#') |h %> <% mt('Status') |h %> - <% mt('(bill) name') |h %> - <% mt('company') |h %> - -%if ( defined dbdef->table('cust_main')->column('ship_last') ) { - <% mt('(service) name') |h %> - <% mt('company') |h %> -%} + <% mt('Name') |h %> + <% mt('Company') |h %> %foreach my $addl_header ( @addl_headers ) { <% $addl_header %> @@ -172,25 +167,6 @@ <% $pcompany %> -% 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!$ship_company! -% : ' '; -% - - > - <% "$ship_last, $ship_first" %> - - > - <% $pship_company %> - -% } -% % 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 @@ Billing or service zip 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 @@ <% mt('Address') |h %> + + + <% mt('Zip') |h %> + + <% mt('Signup date') |h %> -- 2.11.0