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   my $align = join('', map { /#/ ? 'r' : 'l' } FS::UI::Web::cust_header() ).
 122              'crrrrr';
 123
 124 %><%= include( 'elements/search.html',
 125                  'title'       => 'Accounts Receivable Aging Summary',
 126                  'name'        => 'customers',
 127                  'query'       => $sql_query,
 128                  'count_query' => $count_sql,
 129                  'header'      => [
 130                                     FS::UI::Web::cust_header(),
 131                                     'Status', # (me)',
 132                                     #'Status', # (cust_main)',
 133                                     '0-30',
 134                                     '30-60',
 135                                     '60-90',
 136                                     '90+',
 137                                     'Total',
 138                                   ],
 139                  'footer'      => [
 140                                     'Total',
 141                                     ( map '',
 142                                           ( 1 ..
 143                                             scalar(FS::UI::Web::cust_header()-1)
 144                                           )
 145                                     ),
 146                                     '',
 147                                     #'',
 148                                     sprintf( $money_char.'%.2f',
 149                                              $row->{'owed_0_30'} ),
 150                                     sprintf( $money_char.'%.2f',
 151                                              $row->{'owed_30_60'} ),
 152                                     sprintf( $money_char.'%.2f',
 153                                              $row->{'owed_60_90'} ),
 154                                     sprintf( $money_char.'%.2f',
 155                                              $row->{'owed_90_pl'} ),
 156                                     sprintf( '<b>'. $money_char.'%.2f'. '</b>',
 157                                              $row->{'owed_total'} ),
 158                                   ],
 159                  'fields'      => [
 160                                     \&FS::UI::Web::cust_fields,
 161                                     sub {
 162                                           my $row = shift;
 163                                           my $status = 'Cancelled';
 164                                           my $statuscol = 'FF0000';
 165                                           if ( $row->uncancelled_pkgs ) {
 166                                             $status = 'Suspended';
 167                                             $statuscol = 'FF9900';
 168                                             if ( $row->active_pkgs ) {
 169                                               $status = 'Active';
 170                                               $statuscol = '00CC00';
 171                                             }
 172                                           }
 173                                           $status;
 174                                         },
 175                                     #sub { ucfirst(shift->status) },
 176                                     sub { sprintf( $money_char.'%.2f',
 177                                                    shift->get('owed_0_30') ) },
 178                                     sub { sprintf( $money_char.'%.2f',
 179                                                    shift->get('owed_30_60') ) },
 180                                     sub { sprintf( $money_char.'%.2f',
 181                                                    shift->get('owed_60_90') ) },
 182                                     sub { sprintf( $money_char.'%.2f',
 183                                                    shift->get('owed_90_pl') ) },
 184                                     sub { sprintf( $money_char.'%.2f',
 185                                                    shift->get('owed_total') ) },
 186                                   ],
 187                  'links'       => [
 188                                     [ "${p}view/cust_main.cgi?", 'custnum' ],
 189                                     [ "${p}view/cust_main.cgi?", 'custnum' ],
 190                                     '',
 191                                     #'',
 192                                     '',
 193                                     '',
 194                                     '',
 195                                     '',
 196                                     '',
 197                                   ],
 198                  #'align'       => 'rlccrrrrr',
 199                  'align'       => $align,
 200                  #'size'        => [ '', '', '-1', '-1', '', '', '', '',  '', ],
 201                  #'style'       => [ '', '',  'b',  'b', '', '', '', '', 'b', ],
 202                  'size'        => [ ( map '', FS::UI::Web::cust_header() ),
 203                                     '-1', '', '', '', '',  '', ],
 204                  'style'       => [ ( map '', FS::UI::Web::cust_header() ),
 205                                     'b', '', '', '', '', 'b', ],
 206                  'color'       => [
 207                                     ( map '', FS::UI::Web::cust_header() ),
 208                                     sub {
 209                                           my $row = shift;
 210                                           my $status = 'Cancelled';
 211                                           my $statuscol = 'FF0000';
 212                                           if ( $row->uncancelled_pkgs ) {
 213                                             $status = 'Suspended';
 214                                             $statuscol = 'FF9900';
 215                                             if ( $row->active_pkgs ) {
 216                                               $status = 'Active';
 217                                               $statuscol = '00CC00';
 218                                             }
 219                                           }
 220                                            $statuscol;
 221                                         },
 222                                     #sub { shift->statuscolor; },
 223                                     '',
 224                                     '',
 225                                     '',
 226                                     '',
 227                                     '',
 228                                   ],
 229
 230              )
 231 %>