httemplate/search/report_receivables.cgi

   1 <!-- mason kludge -->
   2 <%
   3
   4   my $charged = <<END;
   5   sum( charged
   6        - coalesce(
   7            ( select sum(amount) from cust_bill_pay
   8              where cust_bill.invnum = cust_bill_pay.invnum )
   9            ,0
  10          )
  11        - coalesce(
  12            ( select sum(amount) from cust_credit_bill
  13              where cust_bill.invnum = cust_credit_bill.invnum )
  14            ,0
  15          )
  16
  17      )
  18 END
  19
  20   my $owed_cols = <<END;
  21        coalesce(
  22          ( select $charged from cust_bill
  23            where cust_bill._date > extract(epoch from now())-2592000
  24              and cust_main.custnum = cust_bill.custnum
  25          )
  26          ,0
  27        ) as owed_0_30,
  28
  29        coalesce(
  30          ( select $charged from cust_bill
  31            where cust_bill._date >  extract(epoch from now())-5184000
  32              and cust_bill._date <= extract(epoch from now())-2592000
  33              and cust_main.custnum = cust_bill.custnum
  34          )
  35          ,0
  36        ) as owed_30_60,
  37
  38        coalesce(
  39          ( select $charged from cust_bill
  40            where cust_bill._date >  extract(epoch from now())-7776000
  41              and cust_bill._date <= extract(epoch from now())-5184000
  42              and cust_main.custnum = cust_bill.custnum
  43          )
  44          ,0
  45        ) as owed_60_90,
  46
  47        coalesce(
  48          ( select $charged from cust_bill
  49            where cust_bill._date <= extract(epoch from now())-7776000
  50              and cust_main.custnum = cust_bill.custnum
  51          )
  52          ,0
  53        ) as owed_90_plus,
  54
  55        coalesce(
  56          ( select $charged from cust_bill
  57            where cust_main.custnum = cust_bill.custnum
  58          )
  59          ,0
  60        ) as owed_total
  61 END
  62
  63   my $recurring = <<END;
  64         0 < ( select freq from part_pkg
  65                 where cust_pkg.pkgpart = part_pkg.pkgpart )
  66 END
  67
  68   my $packages_cols = <<END;
  69
  70        ( select count(*) from cust_pkg
  71            where cust_main.custnum = cust_pkg.custnum
  72              and $recurring
  73              and ( cancel = 0 or cancel is null )
  74        ) as uncancelled_pkgs,
  75
  76        ( select count(*) from cust_pkg
  77            where cust_main.custnum = cust_pkg.custnum
  78              and $recurring
  79              and ( cancel = 0 or cancel is null )
  80              and ( susp = 0 or susp is null )
  81        ) as active_pkgs
  82
  83 END
  84
  85   my $sql = <<END;
  86
  87 select *, $owed_cols, $packages_cols from cust_main
  88 where 0 <
  89   coalesce(
  90            ( select $charged from cust_bill
  91              where cust_main.custnum = cust_bill.custnum
  92            )
  93            ,0
  94          )
  95
  96 order by coalesce(lower(company), ''), lower(last)
  97
  98 END
  99
 100   my $total_sql = "select $owed_cols";
 101
 102   my $sth = dbh->prepare($sql) or die dbh->errstr;
 103   $sth->execute or die $sth->errstr;
 104
 105   my $total_sth = dbh->prepare($total_sql) or die dbh->errstr;
 106   $total_sth->execute or die $total_sth->errstr;
 107
 108 %>
 109 <%= header('Accounts Receivable Aging Summary', menubar( 'Main Menu'=>$p, ) ) %>
 110 <%= table() %>
 111   <TR>
 112     <TH>Customer</TH>
 113     <TH>Status</TH>
 114     <TH>0-30</TH>
 115     <TH>30-60</TH>
 116     <TH>60-90</TH>
 117     <TH>90+</TH>
 118     <TH>Total</TH>
 119   </TR>
 120 <% while ( my $row = $sth->fetchrow_hashref() ) {
 121      my $status = 'Cancelled';
 122      my $statuscol = 'FF0000';
 123      if ( $row->{uncancelled_pkgs} ) {
 124        $status = 'Suspended';
 125        $statuscol = 'FF9900';
 126        if ( $row->{active_pkgs} ) {
 127          $status = 'Active';
 128          $statuscol = '00CC00';
 129        }
 130      }
 131 %>
 132   <TR>
 133     <TD><A HREF="<%= $p %>view/cust_main.cgi?<%= $row->{'custnum'} %>"><%= $row->{'custnum'} %>:
 134         <%= $row->{'company'} ? $row->{'company'}. ' (' : '' %><%= $row->{'last'}. ', '. $row->{'first'} %><%= $row->{'company'} ? ')' : '' %></A>
 135     </TD>
 136     <TD><B><FONT SIZE=-1 COLOR="#<%= $statuscol %>"><%= $status %></FONT></B></TD>
 137     <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_0_30'} ) %></TD>
 138     <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_30_60'} ) %></TD>
 139     <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_60_90'} ) %></TD>
 140     <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %></TD>
 141     <TD ALIGN="right"><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></TD>
 142   </TR>
 143 <% } %>
 144 <% my $row = $total_sth->fetchrow_hashref(); %>
 145   <TR>
 146     <TD COLSPAN=6>&nbsp;</TD>
 147   </TR>
 148   <TR>
 149     <TD COLSPAN=2><I>Total</I></TD>
 150     <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_0_30'} ) %></TD>
 151     <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_30_60'} ) %></TD>
 152     <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_60_90'} ) %></TD>
 153     <TD ALIGN="right"><I>$<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %></TD>
 154     <TD ALIGN="right"><I><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></I></TD>
 155   </TR>
 156 </TABLE>
 157 </BODY>
 158 </HTML>