httemplate/search/report_receivables.cgi

   1 <!-- mason kludge -->
   2 <%
   3   my $sql = <<END;
   4
   5 select *,
   6
   7        coalesce(
   8          ( select sum( charged
   9                        - coalesce(
  10                            ( select sum(amount) from cust_bill_pay
  11                              where cust_bill.invnum = cust_bill_pay.invnum )
  12                            ,0
  13                          )
  14                        - coalesce(
  15                            ( select sum(amount) from cust_credit_bill
  16                              where cust_bill.invnum = cust_credit_bill.invnum )
  17                            ,0
  18                          )
  19
  20                      )
  21            from cust_bill
  22            where cust_bill._date > extract(epoch from now())-2592000
  23              and cust_main.custnum = cust_bill.custnum
  24          )
  25          ,0
  26        ) as owed_0_30,
  27
  28        coalesce(
  29          ( select sum( charged
  30                        - coalesce(
  31                            ( select sum(amount) from cust_bill_pay
  32                              where cust_bill.invnum = cust_bill_pay.invnum )
  33                            ,0
  34                          )
  35                        - coalesce(
  36                            ( select sum(amount) from cust_credit_bill
  37                              where cust_bill.invnum = cust_credit_bill.invnum )
  38                            ,0
  39                          )
  40
  41                      )
  42            from cust_bill
  43            where cust_bill._date >  extract(epoch from now())-5184000
  44              and cust_bill._date <= extract(epoch from now())-2592000
  45              and cust_main.custnum = cust_bill.custnum
  46          )
  47          ,0
  48        ) as owed_30_60,
  49
  50        coalesce(
  51          ( select sum( charged
  52                        - coalesce(
  53                            ( select sum(amount) from cust_bill_pay
  54                              where cust_bill.invnum = cust_bill_pay.invnum )
  55                            ,0
  56                          )
  57                        - coalesce(
  58                            ( select sum(amount) from cust_credit_bill
  59                              where cust_bill.invnum = cust_credit_bill.invnum )
  60                            ,0
  61                          )
  62
  63                      )
  64            from cust_bill
  65            where cust_bill._date >  extract(epoch from now())-7776000
  66              and cust_bill._date <= extract(epoch from now())-5184000
  67              and cust_main.custnum = cust_bill.custnum
  68          )
  69          ,0
  70        ) as owed_60_90,
  71
  72        coalesce(
  73          ( select sum( charged
  74                        - coalesce(
  75                            ( select sum(amount) from cust_bill_pay
  76                              where cust_bill.invnum = cust_bill_pay.invnum )
  77                            ,0
  78                          )
  79                        - coalesce(
  80                            ( select sum(amount) from cust_credit_bill
  81                              where cust_bill.invnum = cust_credit_bill.invnum )
  82                            ,0
  83                          )
  84
  85                      )
  86            from cust_bill
  87            where cust_bill._date <= extract(epoch from now())-7776000
  88              and cust_main.custnum = cust_bill.custnum
  89          )
  90          ,0
  91        ) as owed_90_plus,
  92
  93        coalesce(
  94          ( select sum( charged
  95                        - coalesce(
  96                            ( select sum(amount) from cust_bill_pay
  97                              where cust_bill.invnum = cust_bill_pay.invnum )
  98                            ,0
  99                          )
 100                        - coalesce(
 101                            ( select sum(amount) from cust_credit_bill
 102                              where cust_bill.invnum = cust_credit_bill.invnum )
 103                            ,0
 104                          )
 105
 106                      )
 107            from cust_bill
 108            where cust_main.custnum = cust_bill.custnum
 109          )
 110          ,0
 111        ) as owed_total
 112
 113 from cust_main
 114
 115 where 0 <
 116   coalesce(
 117            ( select sum( charged
 118                          - coalesce(
 119                              ( select sum(amount) from cust_bill_pay
 120                                where cust_bill.invnum = cust_bill_pay.invnum )
 121                              ,0
 122                            )
 123                          - coalesce(
 124                              ( select sum(amount) from cust_credit_bill
 125                                where cust_bill.invnum = cust_credit_bill.invnum)
 126                              ,0
 127                            )
 128
 129                        )
 130              from cust_bill
 131              where cust_main.custnum = cust_bill.custnum
 132            )
 133            ,0
 134          )
 135
 136 order by company, last
 137
 138 END
 139
 140
 141   #order by!
 142
 143   #the grep (and the sort ) should be pushed down to SQL
 144   #my @cust_main = sort {    $a->company cmp $b->company
 145   #                       || $a->last    cmp $b->last    }
 146   #                grep { $_->balance }
 147   #                qsearch('cust_main', {} );
 148
 149   my $totals_table = table(). '<TR><TH>Total</TH>'.
 150                              '<TD><i>0-30</i></TD>'.
 151                              '<TD><i>30-60</i></TD>'.
 152                              '<TD><i>60-90</i></TD>'.
 153                              '<TD><i>90+</i></TD>'.
 154                              '<TD><i>total</i></TD>'.
 155                              '</TABLE>';
 156   $totals_table = '';
 157
 158   my $sth = dbh->prepare($sql) or die dbh->errstr;
 159   $sth->execute or die $sth->errstr;
 160
 161 %>
 162 <%= header('Accounts Receivable Aging Summary', menubar( 'Main Menu'=>$p, ) ) %>
 163 <%= $totals_table %>
 164 <%= table() %>
 165   <TR>
 166     <TH>Customer</TH>
 167     <TH>0-30</TH>
 168     <TH>30-60</TH>
 169     <TH>60-90</TH>
 170     <TH>90+</TH>
 171     <TH>Total</TH>
 172   </TR>
 173 <% while ( my $row = $sth->fetchrow_hashref() ) { %>
 174   <TR>
 175     <TD><A HREF="<%= $p %>view/cust_main.cgi?<%= $row->{'custnum'} %>">
 176         <%= $row->{'company'} ? $row->{'company'}. ' (' : '' %>
 177         <%= $row->{'last'}. ', '. $row->{'first'} %>
 178         <%= $row->{'company'} ? ')' : '' %></A>
 179     </TD>
 180     <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_0_30'} ) %></TD>
 181     <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_30_60'} ) %></TD>
 182     <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_60_90'} ) %></TD>
 183     <TD ALIGN="right">$<%= sprintf("%.2f", $row->{'owed_90_plus'} ) %></TD>
 184     <TD ALIGN="right"><B>$<%= sprintf("%.2f", $row->{'owed_total'} ) %></B></TD>
 185   </TR>
 186 <% } %>
 187 </TABLE>
 188 <%= $totals_table %>
 189 </BODY>
 190 </HTML>