summaryrefslogtreecommitdiff
path: root/httemplate
diff options
context:
space:
mode:
authorivan <ivan>2003-11-06 14:45:59 +0000
committerivan <ivan>2003-11-06 14:45:59 +0000
commit9d3055def8da004cc1b7c7f9b1ce88da3eca3bba (patch)
tree4d86f9bda1c7853e6b4302c106739c3550677f83 /httemplate
parent5a77da3b812de3d218edc3faba3aefc777e5ca71 (diff)
add totals & simplify expressions
Diffstat (limited to 'httemplate')
-rwxr-xr-xhttemplate/search/report_receivables.cgi147
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>&nbsp;</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>