-<HTML>
- <HEAD>
- <TITLE>Invoice Search</TITLE>
- </HEAD>
- <BODY BGCOLOR="#e8e8e8">
- <FONT SIZE=7>
- Invoice Search
- </FONT>
- <BR><BR>
- <FORM ACTION="cust_bill.cgi" METHOD="post">
- Search for <B>invoice #</B>:
- <INPUT TYPE="text" NAME="invnum">
+<%
+ my( $count_query, $sql_query );
+ if ( $cgi->keywords ) {
+ my($query) = $cgi->keywords;
+ my $owed =
+ "charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
+ WHERE cust_bill_pay.invnum = cust_bill.invnum )
+ - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
+ WHERE cust_credit_bill.invnum = cust_bill.invnum )";
+ my @where;
+ my $orderby = '';
+ if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) {
+ my($open, $days, $field) = ($1, $2, $3);
+ $field = "_date" if $field eq 'date';
+ $orderby = "ORDER BY cust_bill.$field";
+ push @where, "0 != $owed" if $open;
+ push @where, "cust_bill._date < ". (time-86400*$days) if $days;
+ } else {
+ die "unknown query string $query";
+ }
+
+ my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
+
+ $count_query = "SELECT COUNT(*), sum(charged), sum($owed)
+ FROM cust_bill $extra_sql";
+
+ $sql_query = {
+ 'table' => 'cust_bill',
+ 'hashref' => {},
+ 'select' => "cust_bill.*, $owed as owed",
+ 'extra_sql' => "$extra_sql $orderby"
+ };
+
+ } else {
+ $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/;
+ $count_query = 'SELECT 1';
+ $sql_query = {
+ 'table' => 'cust_bill',
+ 'hashref' => { 'invnum' => $2 },
+ #'select' => '*',
+ };
+ }