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                  'fields'      => [
 144                                     'custnum',
 145                                     'name',
 146                                     sub {
 147                                           my $row = shift;
 148                                           my $status = 'Cancelled';
 149                                           my $statuscol = 'FF0000';
 150                                           if ( $row->uncancelled_pkgs ) {
 151                                             $status = 'Suspended';
 152                                             $statuscol = 'FF9900';
 153                                             if ( $row->active_pkgs ) {
 154                                               $status = 'Active';
 155                                               $statuscol = '00CC00';
 156                                             }
 157                                           }
 158                                           $status;
 159                                         },
 160                                     #sub { ucfirst(shift->status) },
 161                                     sub { sprintf( $money_char.'%.2f',
 162                                                    shift->get('owed_0_30') ) },
 163                                     sub { sprintf( $money_char.'%.2f',
 164                                                    shift->get('owed_30_60') ) },
 165                                     sub { sprintf( $money_char.'%.2f',
 166                                                    shift->get('owed_60_90') ) },
 167                                     sub { sprintf( $money_char.'%.2f',
 168                                                    shift->get('owed_90_pl') ) },
 169                                     sub { sprintf( $money_char.'%.2f',
 170                                                    shift->get('owed_total') ) },
 171                                   ],
 172                  'links'       => [
 173                                     [ "${p}view/cust_main.cgi?", 'custnum' ],
 174                                     [ "${p}view/cust_main.cgi?", 'custnum' ],
 175                                     '',
 176                                     #'',
 177                                     '',
 178                                     '',
 179                                     '',
 180                                     '',
 181                                     '',
 182                                   ],
 183                  #'align'       => 'rlccrrrrr',
 184                  'align'       => 'rlcrrrrr',
 185                  #'size'        => [ '', '', '-1', '-1', '', '', '', '',  '', ],
 186                  #'style'       => [ '', '',  'b',  'b', '', '', '', '', 'b', ],
 187                  'size'        => [ '', '', '-1', '', '', '', '',  '', ],
 188                  'style'       => [ '', '',  'b', '', '', '', '', 'b', ],
 189                  'color'       => [
 190                                     '',
 191                                     '',
 192                                     sub {
 193                                           my $row = shift;
 194                                           my $status = 'Cancelled';
 195                                           my $statuscol = 'FF0000';
 196                                           if ( $row->uncancelled_pkgs ) {
 197                                             $status = 'Suspended';
 198                                             $statuscol = 'FF9900';
 199                                             if ( $row->active_pkgs ) {
 200                                               $status = 'Active';
 201                                               $statuscol = '00CC00';
 202                                             }
 203                                           }
 204                                            $statuscol;
 205                                         },
 206                                     #sub { shift->statuscolor; },
 207                                     '',
 208                                     '',
 209                                     '',
 210                                     '',
 211                                     '',
 212                                   ],
 213
 214              )
 215 %>
 216