=item address
+=item zip
+
=item refnum
=item cancelled_pkgs
'usernum' => '',
'status' => '',
'address' => '',
+ 'zip' => '',
'paydate_year' => '',
'invoice_terms' => '',
'custbatch' => '',
)";
}
+ ##
+ # 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
###
'query' => $sql_query,
'count_query' => $count_sql,
'header' => [ 'Zip code', 'Customers', ],
- #'fields' => [ 'zip', 'num_cust', ],
- #'links' => [ '', sub { 'somewhere'; } ],
+ 'fields' => [ 0, 1 ],
+ 'links' => [ '', $link ],
)
%>
<%init>
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] } ];
</%init>
<TR>
<TH CLASS="grid" BGCOLOR="#cccccc"><% mt('#') |h %></TH>
<TH CLASS="grid" BGCOLOR="#cccccc"><% mt('Status') |h %></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('(bill) name') |h %></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('company') |h %></TH>
-
-%if ( defined dbdef->table('cust_main')->column('ship_last') ) {
- <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('(service) name') |h %></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('company') |h %></TH>
-%}
+ <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('Name') |h %></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('Company') |h %></TH>
%foreach my $addl_header ( @addl_headers ) {
<TH CLASS="grid" BGCOLOR="#cccccc"><% $addl_header %></TH>
<% $pcompany %>
</TD>
-% 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!<A HREF="$view"><FONT SIZE=-1>$ship_company</FONT></A>!
-% : '<FONT SIZE=-1> </FONT>';
-%
-
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan %>>
- <A HREF="<% $view %>"><FONT SIZE=-1><% "$ship_last, $ship_first" %></FONT></A>
- </TD>
- <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan %>>
- <% $pship_company %></A>
- </TD>
-% }
-%
% foreach my $addl_col ( @addl_cols ) {
% if ( $addl_col eq 'tickets' ) {
% if ( @custom_priorities ) {
#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
% 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->$_();
% }
<TD ALIGN="right">Billing or service zip</TD>
<TD>
<SELECT NAME="column">
- <OPTION VALUE="zip">Billing zip
- <OPTION VALUE="ship_zip">Service zip
+ <OPTION VALUE="bill">Billing zip
+ <OPTION VALUE="ship">Service zip
</SELECT>
</TD>
</TR>
<TD ALIGN="right" VALIGN="center"><% mt('Address') |h %></TD>
<TD><INPUT TYPE="text" NAME="address" SIZE=54></TD>
</TR>
+
+ <TR>
+ <TD ALIGN="right" VALIGN="center"><% mt('Zip') |h %></TD>
+ <TD><INPUT TYPE="text" NAME="zip" SIZE=12></TD>
+ </TR>
<TR>
<TD ALIGN="right" VALIGN="center"><% mt('Signup date') |h %></TD>