From e4b9a44c8ff1e2e4acc2e20d067785765319db2a Mon Sep 17 00:00:00 2001
From: ivan <ivan>
Date: Thu, 6 Nov 2003 13:39:27 +0000
Subject: [PATCH] "current receivables" -> A/R Aging summary

---
 httemplate/index.html                    |   4 +-
 httemplate/search/report_receivables.cgi | 192 +++++++++++++++++++++--
 2 files changed, 183 insertions(+), 13 deletions(-)

diff --git a/httemplate/index.html b/httemplate/index.html
index c0b235c87..27f34b43b 100644
--- a/httemplate/index.html
+++ b/httemplate/index.html
@@ -69,9 +69,9 @@
               <LI>all invoices (<A HREF="search/cust_bill.cgi?invnum">by invoice number</A>) (<A HREF="search/cust_bill.cgi?date">by date</A>) (<A HREF="search/cust_bill.cgi?custnum">by customer number</A>)
             </UL>
       <A HREF="search/report_cust_pay.html">Payment report (by type and/or date range)</A>
-      <BR><BR>Financial reports
+      <BR><BR><A HREF="search/report_receivables.cgi">Accounts Receivable Aging Summary</A>
+      <BR><BR>(old) Financial reports (being rewritten)
             <UL>
-              <LI> <A HREF="search/report_receivables.cgi">current receivables</A>
               <LI> <A HREF="search/report_tax.html">tax reports</A>
               <LI> <A HREF="search/report_cc.html">credit card receipts</A>
               <LI> <A HREF="search/report_credit.html">credit memos</A>
diff --git a/httemplate/search/report_receivables.cgi b/httemplate/search/report_receivables.cgi
index 04a41366f..241ca581c 100755
--- a/httemplate/search/report_receivables.cgi
+++ b/httemplate/search/report_receivables.cgi
@@ -1,20 +1,190 @@
 <!-- mason kludge -->
 <%
+  my $sql = <<END;
 
-#my $user = getotaker;
-my $user = $FS::UID::user; #dumb 1.4 8-char workaround
+select *,
 
-print header('Current Receivables Report Results');
+       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_bill_pay
+                             where cust_bill.invnum = cust_bill_pay.invnum )
+                           ,0
+                         )
 
-open (REPORT, "freeside-receivables-report -v $user |");
+                     )
+           from cust_bill
+           where cust_bill._date > extract(epoch from now())-2592000
+             and cust_main.custnum = cust_bill.custnum
+         )
+         ,0
+       ) as owed_0_30,
 
-print '<PRE>';
-while(<REPORT>) {
-  print $_;
-}
-print '</PRE>';
+       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_bill_pay
+                             where cust_bill.invnum = cust_bill_pay.invnum )
+                           ,0
+                         )
 
-print '</BODY></HTML>';
+                     )
+           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
+         )
+         ,0
+       ) 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_bill_pay
+                             where cust_bill.invnum = cust_bill_pay.invnum )
+                           ,0
+                         )
+
+                     )
+           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
+         )
+         ,0
+       ) 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_bill_pay
+                             where cust_bill.invnum = cust_bill_pay.invnum )
+                           ,0
+                         )
+
+                     )
+           from cust_bill
+           where cust_bill._date <= extract(epoch from now())-7776000
+             and cust_main.custnum = cust_bill.custnum
+         )
+         ,0
+       ) 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_bill_pay
+                             where cust_bill.invnum = cust_bill_pay.invnum )
+                           ,0
+                         )
+
+                     )
+           from cust_bill
+           where cust_main.custnum = cust_bill.custnum
+         )
+         ,0
+       ) as owed_total
+
+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_bill_pay
+                               where cust_bill.invnum = cust_bill_pay.invnum )
+                             ,0
+                           )
+  
+                       )
+             from cust_bill
+             where cust_main.custnum = cust_bill.custnum
+           )
+           ,0
+         )
 
+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 $sth = dbh->prepare($sql) or die dbh->errstr;
+  $sth->execute or die $sth->errstr;
+
+%>
+<%= header('Accounts Receivable Aging Summary', menubar( 'Main Menu'=>$p, ) ) %>
+<%= $totals_table %>
+<%= table() %>
+  <TR>
+    <TH>Customer</TH>
+    <TH>0-30</TH>
+    <TH>30-60</TH>
+    <TH>60-90</TH>
+    <TH>90+</TH>
+    <TH>Total</TH>
+  </TR>
+<% while ( my $row = $sth->fetchrow_hashref() ) { %>
+  <TR>
+    <TD><%= $row->{'custnum'} %>:
+        <%= $row->{'company'} ? $row->{'company'}. ' (' : '' %>
+        <%= $row->{'last'}. ', '. $row->{'first'} %>
+        <%= $row->{'company'} ? ')' : '' %>
+    </TD>
+    <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_0_30'} ) %></TD>
+    <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_30_60'} ) %></TD>
+    <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_60_90'} ) %></TD>
+    <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %></TD>
+    <TD ALIGN="right"><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></TD>
+  </TR>
+<% } %>
+</TABLE>
+<%= $totals_table %>
+</BODY>
+</HTML>
-- 
2.20.1