1 <% include( 'elements/search.html',
2 'title' => 'Zip code Search Results',
5 'count_query' => $count_sql,
6 'header' => [ 'Zip code', 'Customers', ],
8 'links' => [ '', $link ],
14 unless $FS::CurrentUser::CurrentUser->access_right('List zip codes');
16 # XXX link to customers
22 if ( $cgi->param('status') =~ /^(prospect|uncancel|active|susp|cancel)$/ ) {
23 my $method = $1.'_sql';
24 push @where, FS::cust_main->$method();
28 # XXX this needs to be virtualized by agent too (like lots of stuff)
31 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
33 push @where, "cust_main.agentnum = $agentnum";
38 if ( $cgi->param('svcdb') =~ /^(\w+)$/ ) {
40 push @where, "EXISTS( SELECT 1 FROM $svcdb LEFT JOIN cust_svc USING ( svcnum )
41 LEFT JOIN cust_pkg USING ( pkgnum )
42 WHERE cust_pkg.custnum = cust_main.custnum
46 my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
48 # bill zip vs ship zip
52 "CASE WHEN $field IS NULL THEN '' ELSE $field END";
57 "CASE WHEN $field is NULL
59 ELSE CASE WHEN $field LIKE '_____-____'
60 THEN SUBSTRING($field FROM 1 FOR 5)
66 $cgi->param('column') =~ /^(bill|ship)$/;
67 my $location = $1 || 'bill';
68 $location .= '_locationnum';
71 if ( $cgi->param('ignore_plus4') ) {
72 $zip = strip_plus4('cust_location.zip');
74 $zip = fieldorempty('cust_location.zip');
77 # construct the queries and send 'em off
79 my $join = "JOIN cust_location ON (cust_main.$location = cust_location.locationnum)";
82 "SELECT $zip AS zipcode,
88 ORDER BY num_cust DESC, $zip ASC
92 "SELECT COUNT(DISTINCT cust_location.zip)
93 FROM cust_main $join $where";
95 my $link = [ $p.'search/cust_main.html?zip=',