<% my( $count_query, $sql_query ); if ( $cgi->param('begin') || $cgi->param('end') || $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 = 'ORDER BY cust_bill._date'; if ( $cgi->param('begin') =~ /^(\d+)$/ ) { push @where, "cust_bill._date >= $1", } if ( $cgi->param('end') =~ /^(\d+)$/ ) { push @where, "cust_bill._date < $1", } my($query) = $cgi->keywords; 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; } 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' => '*', }; } my $link = [ "${p}view/cust_bill.cgi?", 'invnum', ]; my $clink = sub { my $cust_bill = shift; my $cust_main = $cust_bill->cust_main; $cust_main ? [ "${p}view/cust_main.cgi?", 'custnum' ] : ''; }; %> <%= include( 'elements/search.html', 'title' => 'Invoice Search Results', 'name' => 'invoices', 'query' => $sql_query, 'count_query' => $count_query, 'count_addl' => [ '$%.2f total invoiced', '$%.2f total outstanding balance', ], 'redirect' => $link, 'header' => [ 'Invoice #', qw(Balance Amount Date), 'Contact name', 'Company' ], 'fields' => [ 'invnum', sub { sprintf('$%.2f', shift->get('owed') ) }, sub { sprintf('$%.2f', shift->charged ) }, sub { time2str('%b %d %Y', shift->_date ) }, sub { my $cust_bill = shift; my $cust_main = $cust_bill->cust_main; $cust_main ? $cust_main->get('last'). ', '. $cust_main->first : "WARNING: can't find cust_main.custnum ". $cust_bill->custnum. ' (cust_bill.invnum '. $cust_bill->invnum. ')'; }, sub { my $cust_main = shift->cust_main; $cust_main ? $cust_main->company : ''; }, ], 'links' => [ $link, $link, $link, $link, $clink, $clink, ], ) %>