X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_bill_pkg.pm;h=131f969a5d8fae31d2aaf32455ff912da19ef976;hb=b2bc0890549ea23dfb1673191e4bc7e14d8d7740;hp=7240db8595512083a4eee38268fd3969da3c806c;hpb=d3d9a2c8bbbec1318d14214ee3e6a6556e6fe6db;p=freeside.git

diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm
index 7240db859..131f969a5 100644
--- a/FS/FS/cust_bill_pkg.pm
+++ b/FS/FS/cust_bill_pkg.pm
@@ -713,9 +713,10 @@ appropriate FS::cust_bill_pkg_display objects.
 
 Options are passed as a list of name/value pairs.  Options are:
 
-part_pkg: FS::part_pkg object from the 
+part_pkg: FS::part_pkg object from this line item's package.
 
-real_pkgpart: if this line item comes from a bundled package, the pkgpart of the owning package.  Otherwise the same as the part_pkg's pkgpart above.
+real_pkgpart: if this line item comes from a bundled package, the pkgpart 
+of the owning package.  Otherwise the same as the part_pkg's pkgpart above.
 
 =cut
 
@@ -726,13 +727,19 @@ sub set_display {
 
   my $conf = new FS::Conf;
 
+  # whether to break this down into setup/recur/usage
   my $separate = $conf->exists('separate_usage');
+
   my $usage_mandate =            $part_pkg->option('usage_mandate', 'Hush!')
                     || $cust_pkg->part_pkg->option('usage_mandate', 'Hush!');
 
   # or use the category from $opt{'part_pkg'} if its not bundled?
   my $categoryname = $cust_pkg->part_pkg->categoryname;
 
+  # if we don't have to separate setup/recur/usage, or put this in a 
+  # package-specific section, or display a usage summary, then don't 
+  # even create one of these.  The item will just display in the unnamed
+  # section as a single line plus details.
   return $self->set('display', [])
     unless $separate || $categoryname || $usage_mandate;
   
@@ -740,34 +747,46 @@ sub set_display {
 
   my %hash = ( 'section' => $categoryname );
 
+  # whether to put usage details in a separate section, and if so, which one
   my $usage_section =            $part_pkg->option('usage_section', 'Hush!')
                     || $cust_pkg->part_pkg->option('usage_section', 'Hush!');
 
+  # whether to show a usage summary line (total usage charges, no details)
   my $summary =            $part_pkg->option('summarize_usage', 'Hush!')
               || $cust_pkg->part_pkg->option('summarize_usage', 'Hush!');
 
   if ( $separate ) {
+    # create lines for setup and (non-usage) recur, in the main section
     push @display, new FS::cust_bill_pkg_display { type => 'S', %hash };
     push @display, new FS::cust_bill_pkg_display { type => 'R', %hash };
   } else {
+    # display everything in a single line
     push @display, new FS::cust_bill_pkg_display
                      { type => '',
                        %hash,
+                       # and if usage_mandate is enabled, hide details
+                       # (this only works on multisection invoices...)
                        ( ( $usage_mandate ) ? ( 'summary' => 'Y' ) : () ),
                      };
   }
 
   if ($separate && $usage_section && $summary) {
+    # create a line for the usage summary in the main section
     push @display, new FS::cust_bill_pkg_display { type    => 'U',
                                                    summary => 'Y',
                                                    %hash,
                                                  };
   }
+
   if ($usage_mandate || ($usage_section && $summary) ) {
     $hash{post_total} = 'Y';
   }
 
   if ($separate || $usage_mandate) {
+    # show call details for this line item in the usage section.
+    # if usage_mandate is on, this will display below the section subtotal.
+    # this also happens if usage is in a separate section and there's a 
+    # summary in the main section, though I'm not sure why.
     $hash{section} = $usage_section if $usage_section;
     push @display, new FS::cust_bill_pkg_display { type => 'U', %hash };
   }
@@ -778,8 +797,9 @@ sub set_display {
 
 =item disintegrate
 
-Returns a list of cust_bill_pkg objects each with no more than a single class
-(including setup or recur) of charge.
+Returns a hash: keys are "setup", "recur" or usage classnum, values are
+FS::cust_bill_pkg objects, each with no more than a single class (setup or
+recur) of charge.
 
 =cut
 
@@ -1058,6 +1078,128 @@ sub _X_show_zero {
   $self->cust_pkg->_X_show_zero($what);
 }
 
+=item credited [ BEFORE, AFTER, OPTIONS ]
+
+Returns the sum of credits applied to this item.  Arguments are the same as
+owed_sql/paid_sql/credited_sql.
+
+=cut
+
+sub credited {
+  my $self = shift;
+  $self->scalar_sql('SELECT '. $self->credited_sql(@_).' FROM cust_bill_pkg WHERE billpkgnum = ?', $self->billpkgnum);
+}
+
+=back
+
+=head1 CLASS METHODS
+
+=over 4
+
+=item usage_sql
+
+Returns an SQL expression for the total usage charges in details on
+an item.
+
+=cut
+
+my $usage_sql =
+  '(SELECT COALESCE(SUM(cust_bill_pkg_detail.amount),0) 
+    FROM cust_bill_pkg_detail 
+    WHERE cust_bill_pkg_detail.billpkgnum = cust_bill_pkg.billpkgnum)';
+
+sub usage_sql { $usage_sql }
+
+# this makes owed_sql, etc. much more concise
+sub charged_sql {
+  my ($class, $start, $end, %opt) = @_;
+  my $charged = 
+    $opt{setuprecur} =~ /^s/ ? 'cust_bill_pkg.setup' :
+    $opt{setuprecur} =~ /^r/ ? 'cust_bill_pkg.recur' :
+    'cust_bill_pkg.setup + cust_bill_pkg.recur';
+
+  if ($opt{no_usage} and $charged =~ /recur/) { 
+    $charged = "$charged - $usage_sql"
+  }
+
+  $charged;
+}
+
+
+=item owed_sql [ BEFORE, AFTER, OPTIONS ]
+
+Returns an SQL expression for the amount owed.  BEFORE and AFTER specify
+a date window.  OPTIONS may include 'no_usage' (excludes usage charges)
+and 'setuprecur' (set to "setup" or "recur" to limit to one or the other).
+
+=cut
+
+sub owed_sql {
+  my $class = shift;
+  '(' . $class->charged_sql(@_) . 
+  ' - ' . $class->paid_sql(@_) .
+  ' - ' . $class->credited_sql(@_) . ')'
+}
+
+=item paid_sql [ BEFORE, AFTER, OPTIONS ]
+
+Returns an SQL expression for the sum of payments applied to this item.
+
+=cut
+
+sub paid_sql {
+  my ($class, $start, $end, %opt) = @_;
+  my $s = $start ? "AND cust_bill_pay._date <= $start" : '';
+  my $e = $end   ? "AND cust_bill_pay._date >  $end"   : '';
+  my $setuprecur = 
+    $opt{setuprecur} =~ /^s/ ? 'setup' :
+    $opt{setuprecur} =~ /^r/ ? 'recur' :
+    '';
+  $setuprecur &&= "AND setuprecur = '$setuprecur'";
+
+  my $paid = "( SELECT COALESCE(SUM(cust_bill_pay_pkg.amount),0)
+     FROM cust_bill_pay_pkg JOIN cust_bill_pay USING (billpaynum)
+     WHERE cust_bill_pay_pkg.billpkgnum = cust_bill_pkg.billpkgnum
+           $s $e $setuprecur )";
+
+  if ( $opt{no_usage} ) {
+    # cap the amount paid at the sum of non-usage charges, 
+    # minus the amount credited against non-usage charges
+    "LEAST($paid, ". 
+      $class->charged_sql($start, $end, %opt) . ' - ' .
+      $class->credited_sql($start, $end, %opt).')';
+  }
+  else {
+    $paid;
+  }
+
+}
+
+sub credited_sql {
+  my ($class, $start, $end, %opt) = @_;
+  my $s = $start ? "AND cust_credit_bill._date <= $start" : '';
+  my $e = $end   ? "AND cust_credit_bill._date >  $end"   : '';
+  my $setuprecur = 
+    $opt{setuprecur} =~ /^s/ ? 'setup' :
+    $opt{setuprecur} =~ /^r/ ? 'recur' :
+    '';
+  $setuprecur &&= "AND setuprecur = '$setuprecur'";
+
+  my $credited = "( SELECT COALESCE(SUM(cust_credit_bill_pkg.amount),0)
+     FROM cust_credit_bill_pkg JOIN cust_credit_bill USING (creditbillnum)
+     WHERE cust_credit_bill_pkg.billpkgnum = cust_bill_pkg.billpkgnum
+           $s $e $setuprecur )";
+
+  if ( $opt{no_usage} ) {
+    # cap the amount credited at the sum of non-usage charges
+    "LEAST($credited, ". $class->charged_sql($start, $end, %opt).')';
+  }
+  else {
+    $credited;
+  }
+
+}
+
 =back
 
 =head1 BUGS