X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_bill_pkg.cgi;h=77901de870a460135da164c9f2bbec5f9679a792;hb=7b125e587a4d1ee0aca692e23ea7897f671855ae;hp=115597a9443d5e6ed57ea5aa2dce63258ee8c703;hpb=59f7223cb3bdbc409cd40c6fd12529f9d45ade8e;p=freeside.git

diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index 115597a94..77901de87 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -3,15 +3,24 @@
                  'name'        => 'line items',
                  'query'       => $query,
                  'count_query' => $count_query,
-                 'count_addl'  => [ $money_char. '%.2f total', ],
+                 'count_addl'  => [ $money_char. '%.2f total',
+                                    $unearned ? ( $money_char. '%.2f unearned revenue' ) : (),
+                                  ],
                  'header'      => [
                    #'#',
                    'Description',
-                   'Setup charge',
+                   ( $unearned
+                     ? ( 'Unearned', 'Owed', 'Payment date' )
+                     : ( 'Setup charge' )
+                   ),
                    ( $use_usage eq 'usage'
                      ? 'Usage charge'
                      : 'Recurring charge'
                    ),
+                   ( $unearned
+                     ? ( 'Charge start', 'Charge end' )
+                     : ()
+                   ),
                    'Invoice',
                    'Date',
                    FS::UI::Web::cust_header(),
@@ -24,7 +33,26 @@
                        },
                    #strikethrough or "N/A ($amount)" or something these when
                    # they're not applicable to pkg_tax search
-                   sub { sprintf($money_char.'%.2f', shift->setup ) },
+                   sub { my $cust_bill_pkg = shift;
+                         if ( $unearned ) {
+                           my $period =
+                             $cust_bill_pkg->edate - $cust_bill_pkg->sdate;
+                           my $elapsed = $unearned - $cust_bill_pkg->sdate;
+                           $elapsed = 0 if $elapsed < 0;
+
+                           my $remaining = 1 - $elapsed/$period;
+
+                           sprintf($money_char. '%.2f',
+                             $remaining * $cust_bill_pkg->recur );
+
+                         } else {
+                           sprintf($money_char.'%.2f', $cust_bill_pkg->setup );
+                         }
+                       },
+                   ( $unearned
+                     ? ( $owed_sub, $payment_date_sub, )
+                     : ()
+                   ),
                    sub { my $row = shift;
                          my $value = 0;
                          if ( $use_usage eq 'recurring' ) {
@@ -36,6 +64,12 @@
                          }
                          sprintf($money_char.'%.2f', $value );
                        },
+                   ( $unearned
+                     ? ( sub { time2str('%b %d %Y', shift->sdate ) },
+                         sub { time2str('%b %d %Y', shift->edate ) },
+                       )
+                     : ()
+                   ),
                    'invnum',
                    sub { time2str('%b %d %Y', shift->_date ) },
                    \&FS::UI::Web::cust_fields,
@@ -44,7 +78,9 @@
                    #'',
                    '',
                    '',
+                   ( $unearned ? ( '', '' ) : () ),
                    '',
+                   ( $unearned ? ( '', '' ) : () ),
                    $ilink,
                    $ilink,
                    ( map { $_ ne 'Cust. Status' ? $clink : '' }
@@ -52,12 +88,19 @@
                    ),
                  ],
                  #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
-                 'align' => 'lrrrc'.FS::UI::Web::cust_aligns(),
+                 'align' => 'lr'.
+                            ( $unearned ? 'rc' : '' ).
+                            'r'.
+                            ( $unearned ? 'cc' : '' ).
+                            'rc'.
+                            FS::UI::Web::cust_aligns(),
                  'color' => [ 
                               #'',
                               '',
                               '',
+                              ( $unearned ? ( '', '' ) : () ),
                               '',
+                              ( $unearned ? ( '', '' ) : () ),
                               '',
                               '',
                               FS::UI::Web::cust_colors(),
@@ -66,7 +109,9 @@
                               #'',
                               '',
                               '',
+                              ( $unearned ? ( '', '' ) : () ),
                               '',
+                              ( $unearned ? ( '', '' ) : () ),
                               '',
                               '',
                               FS::UI::Web::cust_styles(),
@@ -82,6 +127,8 @@ die "access denied"
 
 my $conf = new FS::Conf;
 
+my $unearned = '';
+
 #here is the agent virtualization
 my $agentnums_sql =
   $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
@@ -284,6 +331,19 @@ if ( $cgi->param('out') ) {
                    keys %ph
     );
 
+} elsif ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) {
+
+  $unearned = $1;
+
+  push @where, "cust_bill_pkg.sdate < $unearned",
+               "cust_bill_pkg.edate > $unearned",
+               "cust_bill_pkg.recur != 0",
+               "part_pkg.freq != '0'",
+               "part_pkg.freq != '1'",
+               "part_pkg.freq NOT LIKE '%h'",
+               "part_pkg.freq NOT LIKE '%d'",
+               "part_pkg.freq NOT LIKE '%w'";
+
 }
 
 if ( $cgi->param('itemdesc') ) {
@@ -401,10 +461,31 @@ if ( $cgi->param('pkg_tax') ) {
     $count_query .= "SUM(setup + recur - usage)";
   } elsif ( $use_usage eq 'usage' ) {
     $count_query .= "SUM(usage)";
+  } elsif ( $unearned ) {
+    $count_query .= "SUM(cust_bill_pkg.recur)";
   } else {
     $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)";
   }
 
+  if ( $unearned ) {
+
+    #false laziness w/report_prepaid_income.cgi
+
+    my $float = 'REAL'; #'DOUBLE PRECISION';
+
+    my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS $float)";
+    my $elapsed = "(CASE WHEN cust_bill_pkg.sdate > $unearned
+                     THEN 0
+                     ELSE ($unearned - cust_bill_pkg.sdate)
+                   END)";
+    #my $elapsed = "CAST($unearned - cust_bill_pkg.sdate AS $float)";
+
+    my $remaining = "(1 - $elapsed/$period)";
+
+    $count_query .= ", SUM($remaining * cust_bill_pkg.recur)";
+
+  }
+
 }
 
 my $join_cust =  '      JOIN cust_bill USING ( invnum ) 
@@ -473,11 +554,13 @@ if ($use_usage) {
   $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
 }
 
-my @select = (
-               'cust_bill_pkg.*',
-               'cust_bill._date',
-             );
-push @select, 'part_pkg.pkg' unless $cgi->param('istax');
+my @select = ( 'cust_bill_pkg.*',
+               'cust_bill._date', );
+
+push @select, 'part_pkg.pkg',
+              'part_pkg.freq',
+  unless $cgi->param('istax');
+
 push @select, 'cust_main.custnum',
               FS::UI::Web::cust_sql_fields();
 
@@ -496,4 +579,17 @@ my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 my $conf = new FS::Conf;
 my $money_char = $conf->config('money_char') || '$';
 
+my $owed_sub = sub {
+  $money_char. shift->owed_recur; #_recur :/
+};
+
+my $payment_date_sub = sub {
+  #my $cust_bill_pkg = shift;
+  my @cust_pay = sort { $a->_date <=> $b->_date }
+                      map $_->cust_bill_pay->cust_pay,
+                          shift->cust_bill_pay_pkg('recur') #recur :/
+    or return '';
+  time2str('%b %d %Y', $cust_pay[-1]->_date );
+};
+
 </%init>