add totals & simplify expressions

diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi
index 1fd7d03..d066516 100755 (executable)
--- 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>