httemplate/search/report_receivables.cgi

   1 <%
   2
   3   my $charged = <<END;
   4   sum( charged
   5        - coalesce(
   6            ( select sum(amount) from cust_bill_pay
   7              where cust_bill.invnum = cust_bill_pay.invnum )
   8            ,0
   9          )
  10        - coalesce(
  11            ( select sum(amount) from cust_credit_bill
  12              where cust_bill.invnum = cust_credit_bill.invnum )
  13            ,0
  14          )
  15
  16      )
  17 END
  18
  19   my $owed_cols = <<END;
  20        coalesce(
  21          ( select $charged 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 $charged from cust_bill
  30            where cust_bill._date >  extract(epoch from now())-5184000
  31              and cust_bill._date <= extract(epoch from now())-2592000
  32              and cust_main.custnum = cust_bill.custnum
  33          )
  34          ,0
  35        ) as owed_30_60,
  36
  37        coalesce(
  38          ( select $charged from cust_bill
  39            where cust_bill._date >  extract(epoch from now())-7776000
  40              and cust_bill._date <= extract(epoch from now())-5184000
  41              and cust_main.custnum = cust_bill.custnum
  42          )
  43          ,0
  44        ) as owed_60_90,
  45
  46        coalesce(
  47          ( select $charged from cust_bill
  48            where cust_bill._date <= extract(epoch from now())-7776000
  49              and cust_main.custnum = cust_bill.custnum
  50          )
  51          ,0
  52        ) as owed_90_pl,
  53
  54        coalesce(
  55          ( select $charged from cust_bill
  56            where cust_main.custnum = cust_bill.custnum
  57          )
  58          ,0
  59        ) as owed_total
  60 END
  61
  62   my $recurring = <<END;
  63         '0' != ( select freq from part_pkg
  64                    where cust_pkg.pkgpart = part_pkg.pkgpart )
  65 END
  66
  67   my $packages_cols = <<END;
  68
  69        ( select count(*) from cust_pkg
  70            where cust_main.custnum = cust_pkg.custnum
  71              and $recurring
  72              and ( cancel = 0 or cancel is null )
  73        ) as uncancelled_pkgs,
  74
  75        ( select count(*) from cust_pkg
  76            where cust_main.custnum = cust_pkg.custnum
  77              and $recurring
  78              and ( cancel = 0 or cancel is null )
  79              and ( susp = 0 or susp is null )
  80        ) as active_pkgs
  81
  82 END
  83
  84   my $where = <<END;
  85 where 0 <
  86   coalesce(
  87            ( select $charged from cust_bill
  88              where cust_main.custnum = cust_bill.custnum
  89            )
  90            ,0
  91          )
  92 END
  93
  94   my $count_sql = "select count(*) from cust_main $where";
  95
  96   my $sql_query = {
  97     'table'     => 'cust_main',
  98     'hashref'   => {},
  99     'select'    => "*, $owed_cols, $packages_cols",
 100     'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)",
 101   };
 102
 103   my $total_sql = "select $owed_cols";
 104   my $total_sth = dbh->prepare($total_sql) or die dbh->errstr;
 105   $total_sth->execute or die $total_sth->errstr;
 106   my $row = $total_sth->fetchrow_hashref();
 107
 108   my $conf = new FS::Conf;
 109   my $money_char = $conf->config('money_char') || '$';
 110
 111 %><%= include( 'elements/search.html',
 112                  'title'       => 'Accounts Receivable Aging Summary',
 113                  'name'        => 'customers',
 114                  'query'       => $sql_query,
 115                  'count_query' => $count_sql,
 116                  'header'      => [
 117                                     '#',
 118                                     'Customer',
 119                                     'Status (me)',
 120                                     'Status (cust_main)',
 121                                     '0-30',
 122                                     '30-60',
 123                                     '60-90',
 124                                     '90+',
 125                                     'Total',
 126                                   ],
 127                  'footer'      => [
 128                                     '',
 129                                     'Total',
 130                                     '',
 131                                     '',
 132                                     sprintf( $money_char.'%.2f',
 133                                              $row->{'owed_0_30'} ),
 134                                     sprintf( $money_char.'%.2f',
 135                                              $row->{'owed_30_60'} ),
 136                                     sprintf( $money_char.'%.2f',
 137                                              $row->{'owed_60_90'} ),
 138                                     sprintf( $money_char.'%.2f',
 139                                              $row->{'owed_90_pl'} ),
 140                                     sprintf( '<b>'. $money_char.'%.2f'. '</b>',
 141                                              $row->{'owed_total'} ),
 142                                   ],
 143                  'align'       => 'rlccrrrrr',
 144                  'size'        => [ '', '', '-1', '-1', '', '', '', '',  '', ],
 145                  'style'       => [ '', '',  'b',  'b', '', '', '', '', 'b', ],
 146                  'color'       => [
 147                                     '',
 148                                     '',
 149                                     sub {
 150                                           my $row = shift;
 151                                           my $status = 'Cancelled';
 152                                           my $statuscol = 'FF0000';
 153                                           if ( $row->uncancelled_pkgs ) {
 154                                             $status = 'Suspended';
 155                                             $statuscol = 'FF9900';
 156                                             if ( $row->active_pkgs ) {
 157                                               $status = 'Active';
 158                                               $statuscol = '00CC00';
 159                                             }
 160                                           }
 161                                            $statuscol;
 162                                         },
 163                                     sub { shift->statuscolor; },
 164                                     '',
 165                                     '',
 166                                     '',
 167                                     '',
 168                                     '',
 169                                   ],
 170                  'fields'      => [
 171                                     'custnum',
 172                                     'name',
 173                                     sub {
 174                                           my $row = shift;
 175                                           my $status = 'Cancelled';
 176                                           my $statuscol = 'FF0000';
 177                                           if ( $row->uncancelled_pkgs ) {
 178                                             $status = 'Suspended';
 179                                             $statuscol = 'FF9900';
 180                                             if ( $row->active_pkgs ) {
 181                                               $status = 'Active';
 182                                               $statuscol = '00CC00';
 183                                             }
 184                                           }
 185                                           $status;
 186                                         },
 187                                     sub { ucfirst(shift->status) },
 188                                     sub { sprintf( $money_char.'%.2f',
 189                                                    shift->get('owed_0_30') ) },
 190                                     sub { sprintf( $money_char.'%.2f',
 191                                                    shift->get('owed_30_60') ) },
 192                                     sub { sprintf( $money_char.'%.2f',
 193                                                    shift->get('owed_60_90') ) },
 194                                     sub { sprintf( $money_char.'%.2f',
 195                                                    shift->get('owed_90_pl') ) },
 196                                     sub { sprintf( $money_char.'%.2f',
 197                                                    shift->get('owed_total') ) },
 198                                   ],
 199              )
 200 %>
 201