+ 'links' => [ '', $link ],
+&>
+<%init>
+
+die "access denied"
+ unless $FS::CurrentUser::CurrentUser->access_right('List zip codes');
+
+# XXX link to customers
+
+my @where = ();
+
+# select status
+
+if ( $cgi->param('status') =~ /^(prospect|uncancel|active|susp|cancel)$/ ) {
+ my $method = $1.'_sql';
+ push @where, FS::cust_main->$method();
+}
+
+# select agent
+# XXX this needs to be virtualized by agent too (like lots of stuff)
+
+my $agentnum = '';
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+ $agentnum = $1;
+ push @where, "cust_main.agentnum = $agentnum";
+}
+
+# select svcdb
+
+if ( $cgi->param('svcdb') =~ /^(\w+)$/ ) {
+ my $svcdb = $1;
+ push @where, "EXISTS( SELECT 1 FROM $svcdb LEFT JOIN cust_svc USING ( svcnum )
+ LEFT JOIN cust_pkg USING ( pkgnum )
+ WHERE cust_pkg.custnum = cust_main.custnum
+ )";
+}
+
+my $where = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
+
+# bill zip vs ship zip
+
+sub fieldorempty {
+ my $field = shift;
+ "CASE WHEN $field IS NULL THEN '' ELSE $field END";
+}
+
+sub strip_plus4 {
+ my $field = shift;
+ "CASE WHEN $field is NULL
+ THEN ''
+ ELSE CASE WHEN $field LIKE '_____-____'
+ THEN SUBSTRING($field FROM 1 FOR 5)
+ ELSE $field
+ END
+ 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 {
+ $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, $zip ASC
+ ";
+
+my $count_sql =
+ "SELECT COUNT(DISTINCT cust_location.zip)
+ FROM cust_main $join $where";
+
+my $link = [ $p.'search/cust_main.html?zip=',
+ sub { $_[0]->[0] } ];
+
+</%init>