1 <& elements/search.html,
2 'title' => 'Zip code Search Results',
5 'count_query' => $count_sql,
6 'header' => [ 'Zip code', 'Customers', ],
8 'links' => [ '', $link ],
13 unless $FS::CurrentUser::CurrentUser->access_right('List zip codes');
15 # XXX link to customers
21 if ( $cgi->param('status') =~ /^(prospect|uncancel|active|susp|cancel)$/ ) {
22 my $method = $1.'_sql';
23 push @where, FS::cust_main->$method();
27 # XXX this needs to be virtualized by agent too (like lots of stuff)
30 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
32 push @where, "cust_main.agentnum = $agentnum";
37 if ( $cgi->param('svcdb') =~ /^(\w+)$/ ) {
39 push @where, "EXISTS( SELECT 1 FROM $svcdb LEFT JOIN cust_svc USING ( svcnum )
40 LEFT JOIN cust_pkg USING ( pkgnum )
41 WHERE cust_pkg.custnum = cust_main.custnum
45 my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
47 # bill zip vs ship zip
51 "CASE WHEN $field IS NULL THEN '' ELSE $field END";
56 "CASE WHEN $field is NULL
58 ELSE CASE WHEN $field LIKE '_____-____'
59 THEN SUBSTRING($field FROM 1 FOR 5)
65 $cgi->param('column') =~ /^(bill|ship)$/;
66 my $location = $1 || 'bill';
67 $location .= '_locationnum';
70 if ( $cgi->param('ignore_plus4') ) {
71 $zip = strip_plus4('cust_location.zip');
73 $zip = fieldorempty('cust_location.zip');
76 # construct the queries and send 'em off
78 my $join = "JOIN cust_location ON (cust_main.$location = cust_location.locationnum)";
81 "SELECT $zip AS zipcode,
87 ORDER BY num_cust DESC, $zip ASC
91 "SELECT COUNT(DISTINCT cust_location.zip)
92 FROM cust_main $join $where";
94 my $link = [ $p.'search/cust_main.html?zip=',