diff options
Diffstat (limited to 'httemplate')
| -rwxr-xr-x | httemplate/search/report_receivables.cgi | 147 |
1 files changed, 41 insertions, 106 deletions
diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi index 1fd7d03c7..d0665164c 100755 --- a/httemplate/search/report_receivables.cgi +++ b/httemplate/search/report_receivables.cgi @@ -1,24 +1,25 @@ <!-- mason kludge --> <% - my $sql = <<END; -select *, + my $charged = <<END; + sum( charged + - coalesce( + ( select sum(amount) from cust_bill_pay + where cust_bill.invnum = cust_bill_pay.invnum ) + ,0 + ) + - coalesce( + ( select sum(amount) from cust_credit_bill + where cust_bill.invnum = cust_credit_bill.invnum ) + ,0 + ) - coalesce( - ( select sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_credit_bill - where cust_bill.invnum = cust_credit_bill.invnum ) - ,0 - ) + ) +END - ) - from cust_bill + my $owed_cols = <<END; + coalesce( + ( select $charged from cust_bill where cust_bill._date > extract(epoch from now())-2592000 and cust_main.custnum = cust_bill.custnum ) @@ -26,20 +27,7 @@ select *, ) as owed_0_30, coalesce( - ( select sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_credit_bill - where cust_bill.invnum = cust_credit_bill.invnum ) - ,0 - ) - - ) - from cust_bill + ( select $charged from cust_bill where cust_bill._date > extract(epoch from now())-5184000 and cust_bill._date <= extract(epoch from now())-2592000 and cust_main.custnum = cust_bill.custnum @@ -48,20 +36,7 @@ select *, ) as owed_30_60, coalesce( - ( select sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_credit_bill - where cust_bill.invnum = cust_credit_bill.invnum ) - ,0 - ) - - ) - from cust_bill + ( select $charged from cust_bill where cust_bill._date > extract(epoch from now())-7776000 and cust_bill._date <= extract(epoch from now())-5184000 and cust_main.custnum = cust_bill.custnum @@ -70,20 +45,7 @@ select *, ) as owed_60_90, coalesce( - ( select sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_credit_bill - where cust_bill.invnum = cust_credit_bill.invnum ) - ,0 - ) - - ) - from cust_bill + ( select $charged from cust_bill where cust_bill._date <= extract(epoch from now())-7776000 and cust_main.custnum = cust_bill.custnum ) @@ -91,43 +53,19 @@ select *, ) as owed_90_plus, coalesce( - ( select sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_credit_bill - where cust_bill.invnum = cust_credit_bill.invnum ) - ,0 - ) - - ) - from cust_bill + ( select $charged from cust_bill where cust_main.custnum = cust_bill.custnum ) ,0 ) as owed_total +END -from cust_main + my $sql = <<END; +select *, $owed_cols from cust_main where 0 < coalesce( - ( select sum( charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_credit_bill - where cust_bill.invnum = cust_credit_bill.invnum) - ,0 - ) - - ) - from cust_bill + ( select $charged from cust_bill where cust_main.custnum = cust_bill.custnum ) ,0 @@ -137,30 +75,16 @@ order by company, last END - - #order by! - - #the grep (and the sort ) should be pushed down to SQL - #my @cust_main = sort { $a->company cmp $b->company - # || $a->last cmp $b->last } - # grep { $_->balance } - # qsearch('cust_main', {} ); - - my $totals_table = table(). '<TR><TH>Total</TH>'. - '<TD><i>0-30</i></TD>'. - '<TD><i>30-60</i></TD>'. - '<TD><i>60-90</i></TD>'. - '<TD><i>90+</i></TD>'. - '<TD><i>total</i></TD>'. - '</TABLE>'; - $totals_table = ''; + my $total_sql = "select $owed_cols"; my $sth = dbh->prepare($sql) or die dbh->errstr; $sth->execute or die $sth->errstr; + my $total_sth = dbh->prepare($total_sql) or die dbh->errstr; + $total_sth->execute or die $total_sth->errstr; + %> <%= header('Accounts Receivable Aging Summary', menubar( 'Main Menu'=>$p, ) ) %> -<%= $totals_table %> <%= table() %> <TR> <TH>Customer</TH> @@ -184,7 +108,18 @@ END <TD ALIGN="right"><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></TD> </TR> <% } %> +<% my $row = $total_sth->fetchrow_hashref(); %> + <TR> + <TD COLSPAN=6> </TD> + </TR> + <TR> + <TD><I>Total</I></TD> + <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_0_30'} ) %></TD> + <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_30_60'} ) %></TD> + <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_60_90'} ) %></TD> + <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %></TD> + <TD ALIGN="right"><I><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></I></TD> + </TR> </TABLE> -<%= $totals_table %> </BODY> </HTML> |