1 <& elements/search.html,
2 'title' => 'Zip code Search Results',
5 'count_query' => $count_sql,
6 'header' => [ 'Zip code', 'Customers', ],
7 'links' => [ '', $link ],
12 unless $FS::CurrentUser::CurrentUser->access_right('List zip codes');
14 # XXX link to customers
20 if ( $cgi->param('status') =~ /^(prospect|uncancel|active|susp|cancel)$/ ) {
21 my $method = $1.'_sql';
22 push @where, FS::cust_main->$method();
26 # XXX this needs to be virtualized by agent too (like lots of stuff)
29 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
31 push @where, "cust_main.agentnum = $agentnum";
36 if ( $cgi->param('svcdb') =~ /^(\w+)$/ ) {
38 push @where, "EXISTS( SELECT 1 FROM $svcdb LEFT JOIN cust_svc USING ( svcnum )
39 LEFT JOIN cust_pkg USING ( pkgnum )
40 WHERE cust_pkg.custnum = cust_main.custnum
44 my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
46 # bill zip vs ship zip
50 "CASE WHEN $field IS NULL THEN '' ELSE $field END";
55 "CASE WHEN $field is NULL
57 ELSE CASE WHEN $field LIKE '_____-____'
58 THEN SUBSTRING($field FROM 1 FOR 5)
64 $cgi->param('column') =~ /^(bill|ship)$/;
65 my $location = $1 || 'bill';
66 $location .= '_locationnum';
69 if ( $cgi->param('ignore_plus4') ) {
70 $zip = strip_plus4('cust_location.zip');
72 $zip = fieldorempty('cust_location.zip');
75 # construct the queries and send 'em off
77 my $join = "JOIN cust_location ON (cust_main.$location = cust_location.locationnum)";
80 "SELECT $zip AS zipcode,
86 ORDER BY num_cust DESC, $zip ASC
90 "SELECT COUNT(DISTINCT cust_location.zip)
91 FROM cust_main $join $where";
93 my $link = [ $p.'search/cust_main.html?zip=',