This commit was manufactured by cvs2svn to create tag 'freeside_2_1_1'.

[freeside.git] / httemplate / search / cust_bill_pkg.cgi

diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index fd8b04d..98a1da9 100644 (file)
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -3,28 +3,56 @@
                  '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(),
                  ],
                  'fields'      => [
-                   'billpkgnum',
+                   #'billpkgnum',
                    sub { $_[0]->pkgnum > 0
                            ? $_[0]->get('pkg')      # possibly use override.pkg
                            : $_[0]->get('itemdesc') # but i think this correct
                        },
                    #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,36 +64,54 @@
                          }
                          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,
                  ],
                  'links'       => [
+                   #'',
                    '',
                    '',
+                   ( $unearned ? ( '', '' ) : () ),
                    '',
-                   '',
+                   ( $unearned ? ( '', '' ) : () ),
                    $ilink,
                    $ilink,
                    ( map { $_ ne 'Cust. Status' ? $clink : '' }
                          FS::UI::Web::cust_header()
                    ),
                  ],
-                 'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
+                 #'align' => 'rlrrrc'.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(),
                             ],
                  'style' => [ 
+                              #'',
                               '',
                               '',
+                              ( $unearned ? ( '', '' ) : () ),
                               '',
-                              '',
+                              ( $unearned ? ( '', '' ) : () ),
                               '',
                               '',
                               FS::UI::Web::cust_styles(),
@@ -74,11 +120,15 @@
 %>
 <%init>
 
+#LOTS of false laziness below w/cust_credit_bill_pkg.cgi
+
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
 
 my $conf = new FS::Conf;
 
+my $unearned = '';
+
 #here is the agent virtualization
 my $agentnums_sql =
   $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
@@ -89,9 +139,6 @@ my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 push @where, "_date >= $beginning",
              "_date <= $ending";
 
-push @where , " payby != 'COMP' "
-  unless $cgi->param('include_comp_cust');
-
 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   push @where, "cust_main.agentnum = $1";
 }
@@ -281,6 +328,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') ) {
@@ -398,10 +458,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 ) 
@@ -469,13 +550,14 @@ if ($use_usage) {
 } else {
   $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
 }
-warn "count_query is $count_query\n";
 
-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();
 
@@ -494,4 +576,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>