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 $agentnum = '';
  95   if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
  96     $agentnum = $1;
  97     $where .= " AND agentnum = '$agentnum' ";
  98   }
  99
 100   my $count_sql = "select count(*) from cust_main $where";
 101
 102   my $sql_query = {
 103     'table'     => 'cust_main',
 104     'hashref'   => {},
 105     'select'    => "*, $owed_cols, $packages_cols",
 106     'extra_sql' => "$where order by coalesce(lower(company), ''), lower(last)",
 107   };
 108
 109   if ( $agentnum ) {
 110     $owed_cols =~
 111       s/cust_bill\.custnum/cust_bill.custnum AND cust_main.agentnum = '$agentnum'/g;
 112   }
 113   my $total_sql = "select $owed_cols";
 114   my $total_sth = dbh->prepare($total_sql) or die dbh->errstr;
 115   $total_sth->execute or die $total_sth->errstr;
 116   my $row = $total_sth->fetchrow_hashref();
 117
 118   my $conf = new FS::Conf;
 119   my $money_char = $conf->config('money_char') || '$';
 120
 121 %><%= include( 'elements/search.html',
 122                  'title'       => 'Accounts Receivable Aging Summary',
 123                  'name'        => 'customers',
 124                  'query'       => $sql_query,
 125                  'count_query' => $count_sql,
 126                  'header'      => [
 127                                     '#',
 128                                     'Customer',
 129                                     'Status', # (me)',
 130                                     #'Status', # (cust_main)',
 131                                     '0-30',
 132                                     '30-60',
 133                                     '60-90',
 134                                     '90+',
 135                                     'Total',
 136                                   ],
 137                  'footer'      => [
 138                                     '',
 139                                     'Total',
 140                                     '',
 141                                     #'',
 142                                     sprintf( $money_char.'%.2f',
 143                                              $row->{'owed_0_30'} ),
 144                                     sprintf( $money_char.'%.2f',
 145                                              $row->{'owed_30_60'} ),
 146                                     sprintf( $money_char.'%.2f',
 147                                              $row->{'owed_60_90'} ),
 148                                     sprintf( $money_char.'%.2f',
 149                                              $row->{'owed_90_pl'} ),
 150                                     sprintf( '<b>'. $money_char.'%.2f'. '</b>',
 151                                              $row->{'owed_total'} ),
 152                                   ],
 153                  'fields'      => [
 154                                     'custnum',
 155                                     'name',
 156                                     sub {
 157                                           my $row = shift;
 158                                           my $status = 'Cancelled';
 159                                           my $statuscol = 'FF0000';
 160                                           if ( $row->uncancelled_pkgs ) {
 161                                             $status = 'Suspended';
 162                                             $statuscol = 'FF9900';
 163                                             if ( $row->active_pkgs ) {
 164                                               $status = 'Active';
 165                                               $statuscol = '00CC00';
 166                                             }
 167                                           }
 168                                           $status;
 169                                         },
 170                                     #sub { ucfirst(shift->status) },
 171                                     sub { sprintf( $money_char.'%.2f',
 172                                                    shift->get('owed_0_30') ) },
 173                                     sub { sprintf( $money_char.'%.2f',
 174                                                    shift->get('owed_30_60') ) },
 175                                     sub { sprintf( $money_char.'%.2f',
 176                                                    shift->get('owed_60_90') ) },
 177                                     sub { sprintf( $money_char.'%.2f',
 178                                                    shift->get('owed_90_pl') ) },
 179                                     sub { sprintf( $money_char.'%.2f',
 180                                                    shift->get('owed_total') ) },
 181                                   ],
 182                  'links'       => [
 183                                     [ "${p}view/cust_main.cgi?", 'custnum' ],
 184                                     [ "${p}view/cust_main.cgi?", 'custnum' ],
 185                                     '',
 186                                     #'',
 187                                     '',
 188                                     '',
 189                                     '',
 190                                     '',
 191                                     '',
 192                                   ],
 193                  #'align'       => 'rlccrrrrr',
 194                  'align'       => 'rlcrrrrr',
 195                  #'size'        => [ '', '', '-1', '-1', '', '', '', '',  '', ],
 196                  #'style'       => [ '', '',  'b',  'b', '', '', '', '', 'b', ],
 197                  'size'        => [ '', '', '-1', '', '', '', '',  '', ],
 198                  'style'       => [ '', '',  'b', '', '', '', '', 'b', ],
 199                  'color'       => [
 200                                     '',
 201                                     '',
 202                                     sub {
 203                                           my $row = shift;
 204                                           my $status = 'Cancelled';
 205                                           my $statuscol = 'FF0000';
 206                                           if ( $row->uncancelled_pkgs ) {
 207                                             $status = 'Suspended';
 208                                             $statuscol = 'FF9900';
 209                                             if ( $row->active_pkgs ) {
 210                                               $status = 'Active';
 211                                               $statuscol = '00CC00';
 212                                             }
 213                                           }
 214                                            $statuscol;
 215                                         },
 216                                     #sub { shift->statuscolor; },
 217                                     '',
 218                                     '',
 219                                     '',
 220                                     '',
 221                                     '',
 222                                   ],
 223
 224              )
 225 %>
 226