X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_bill_pkg.pm;h=4220d3c0631dec2746c19b0bd96480a5655a41bd;hb=f89471ac01284a6d73ebffd6eaebdde79d5f25dc;hp=2c79209c5d5aacace86b82cde857841e4032f19c;hpb=c7bf005860b761a55ca075df987fb3b5ade8c242;p=freeside.git

diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm
index 2c79209c5..4220d3c06 100644
--- a/FS/FS/cust_bill_pkg.pm
+++ b/FS/FS/cust_bill_pkg.pm
@@ -3,6 +3,7 @@ package FS::cust_bill_pkg;
 use strict;
 use vars qw( @ISA $DEBUG $me );
 use Carp;
+use Text::CSV_XS;
 use FS::Record qw( qsearch qsearchs dbdef dbh );
 use FS::cust_main_Mixin;
 use FS::cust_pkg;
@@ -18,6 +19,8 @@ use FS::cust_bill_pkg_tax_location;
 use FS::cust_bill_pkg_tax_rate_location;
 use FS::cust_tax_adjustment;
 
+use List::Util qw(sum);
+
 @ISA = qw( FS::cust_main_Mixin FS::Record );
 
 $DEBUG = 0;
@@ -146,18 +149,8 @@ sub insert {
 
   if ( $self->get('details') ) {
     foreach my $detail ( @{$self->get('details')} ) {
-      my $cust_bill_pkg_detail = new FS::cust_bill_pkg_detail {
-        'billpkgnum' => $self->billpkgnum,
-        'format'     => (ref($detail) ? $detail->[0] : '' ),
-        'detail'     => (ref($detail) ? $detail->[1] : $detail ),
-        'amount'     => (ref($detail) ? $detail->[2] : '' ),
-        'classnum'   => (ref($detail) ? $detail->[3] : '' ),
-        'phonenum'   => (ref($detail) ? $detail->[4] : '' ),
-        'accountcode' => (ref($detail) ? $detail->[5] : '' ),
-        'duration'   => (ref($detail) ? $detail->[6] : '' ),
-        'regionname' => (ref($detail) ? $detail->[7] : '' ),
-      };
-      $error = $cust_bill_pkg_detail->insert;
+      $detail->billpkgnum($self->billpkgnum);
+      $error = $detail->insert;
       if ( $error ) {
         $dbh->rollback if $oldAutoCommit;
         return "error inserting cust_bill_pkg_detail: $error";
@@ -338,6 +331,8 @@ sub check {
   ;
   return $error if $error;
 
+  $self->regularize_details;
+
   #if ( $self->pkgnum != 0 ) { #allow unchecked pkgnum 0 for tax! (add to part_pkg?)
   if ( $self->pkgnum > 0 ) { #allow -1 for non-pkg line items and 0 for tax (add to part_pkg?)
     return "Unknown pkgnum ". $self->pkgnum
@@ -350,6 +345,50 @@ sub check {
   $self->SUPER::check;
 }
 
+=item regularize_details
+
+Converts the contents of the 'details' pseudo-field to 
+L<FS::cust_bill_pkg_detail> objects, if they aren't already.
+
+=cut
+
+sub regularize_details {
+  my $self = shift;
+  if ( $self->get('details') ) {
+    foreach my $detail ( @{$self->get('details')} ) {
+      if ( ref($detail) ne 'FS::cust_bill_pkg_detail' ) {
+        # then turn it into one
+        my %hash = ();
+        if ( ! ref($detail) ) {
+          $hash{'detail'} = $detail;
+        }
+        elsif ( ref($detail) eq 'HASH' ) {
+          %hash = %$detail;
+        }
+        elsif ( ref($detail) eq 'ARRAY' ) {
+          carp "passing invoice details as arrays is deprecated";
+          #carp "this way sucks, use a hash"; #but more useful/friendly
+          $hash{'format'}      = $detail->[0];
+          $hash{'detail'}      = $detail->[1];
+          $hash{'amount'}      = $detail->[2];
+          $hash{'classnum'}    = $detail->[3];
+          $hash{'phonenum'}    = $detail->[4];
+          $hash{'accountcode'} = $detail->[5];
+          $hash{'startdate'}   = $detail->[6];
+          $hash{'duration'}    = $detail->[7];
+          $hash{'regionname'}  = $detail->[8];
+        }
+        else {
+          die "unknown detail type ". ref($detail);
+        }
+        $detail = new FS::cust_bill_pkg_detail \%hash;
+      }
+      $detail->billpkgnum($self->billpkgnum) if $self->billpkgnum;
+    }
+  }
+  return;
+}
+
 =item cust_pkg
 
 Returns the package (see L<FS::cust_pkg>) for this invoice line item.
@@ -432,61 +471,100 @@ to skip usage detail:
 
 sub details {
   my ( $self, %opt ) = @_;
-  my $format = $opt{format} || '';
   my $escape_function = $opt{escape_function} || sub { shift };
-  return () unless defined dbdef->table('cust_bill_pkg_detail');
 
-  eval "use Text::CSV_XS;";
-  die $@ if $@;
   my $csv = new Text::CSV_XS;
 
-  my $format_sub = sub { my $detail = shift;
-                         $csv->parse($detail) or return "can't parse $detail";
-                         join(' - ', map { &$escape_function($_) }
-                                     $csv->fields
-                             );
-                       };
-
-  $format_sub = sub { my $detail = shift;
-                      $csv->parse($detail) or return "can't parse $detail";
-                      join('</TD><TD>', map { &$escape_function($_) }
-                                        $csv->fields
-                          );
-                    }
-    if $format eq 'html';
-
-  $format_sub = sub { my $detail = shift;
-                      $csv->parse($detail) or return "can't parse $detail";
-                      #join(' & ', map { '\small{'. &$escape_function($_). '}' }
-                      #            $csv->fields );
-                      my $result = '';
-                      my $column = 1;
-                      foreach ($csv->fields) {
-                        $result .= ' & ' if $column > 1;
-                        if ($column > 6) {                     # KLUDGE ALERT!
-                          $result .= '\multicolumn{1}{l}{\scriptsize{'.
-                                     &$escape_function($_). '}}';
-                        }else{
-                          $result .= '\scriptsize{'.  &$escape_function($_). '}';
-                        }
-                        $column++;
-                      }
-                      $result;
-                    }
-    if $format eq 'latex';
-
-  $format_sub = $opt{format_function} if $opt{format_function};
-
-  map { ( $_->format eq 'C'
-          ? &{$format_sub}( $_->detail, $_ )
-          : &{$escape_function}( $_->detail )
-        )
-      }
-    qsearch ({ 'table'    => 'cust_bill_pkg_detail',
-               'hashref'  => { 'billpkgnum' => $self->billpkgnum },
-               'order_by' => 'ORDER BY detailnum',
-            });
-    #qsearch ( 'cust_bill_pkg_detail', { 'lineitemnum' => $self->lineitemnum });
+  if ( $opt{format_function} ) {
+
+    #this still expects to be passed a cust_bill_pkg_detail object as the
+    #second argument, which is expensive
+    carp "deprecated format_function passed to cust_bill_pkg->details";
+    my $format_sub = $opt{format_function} if $opt{format_function};
+
+    map { ( $_->format eq 'C'
+              ? &{$format_sub}( $_->detail, $_ )
+              : &{$escape_function}( $_->detail )
+          )
+        }
+      qsearch ({ 'table'    => 'cust_bill_pkg_detail',
+                 'hashref'  => { 'billpkgnum' => $self->billpkgnum },
+                 'order_by' => 'ORDER BY detailnum',
+              });
+
+  } elsif ( $opt{'no_usage'} ) {
+
+    my $sql = "SELECT detail FROM cust_bill_pkg_detail ".
+              "  WHERE billpkgnum = ". $self->billpkgnum.
+              "    AND ( format IS NULL OR format != 'C' ) ".
+              "  ORDER BY detailnum";
+    my $sth = dbh->prepare($sql) or die dbh->errstr;
+    $sth->execute or die $sth->errstr;
+
+    map &{$escape_function}( $_->[0] ), @{ $sth->fetchall_arrayref };
+
+  } else {
+
+    my $format_sub;
+    my $format = $opt{format} || '';
+    if ( $format eq 'html' ) {
+
+      $format_sub = sub { my $detail = shift;
+                          $csv->parse($detail) or return "can't parse $detail";
+                          join('</TD><TD>', map { &$escape_function($_) }
+                                            $csv->fields
+                              );
+                        };
+
+    } elsif ( $format eq 'latex' ) {
+
+      $format_sub = sub {
+        my $detail = shift;
+        $csv->parse($detail) or return "can't parse $detail";
+        #join(' & ', map { '\small{'. &$escape_function($_). '}' }
+        #            $csv->fields );
+        my $result = '';
+        my $column = 1;
+        foreach ($csv->fields) {
+          $result .= ' & ' if $column > 1;
+          if ($column > 6) {                     # KLUDGE ALERT!
+            $result .= '\multicolumn{1}{l}{\scriptsize{'.
+                       &$escape_function($_). '}}';
+          }else{
+            $result .= '\scriptsize{'.  &$escape_function($_). '}';
+          }
+          $column++;
+        }
+        $result;
+      };
+
+    } else {
+
+      $format_sub = sub { my $detail = shift;
+                          $csv->parse($detail) or return "can't parse $detail";
+                          join(' - ', map { &$escape_function($_) }
+                                      $csv->fields
+                              );
+                        };
+
+    }
+
+    my $sql = "SELECT format, detail FROM cust_bill_pkg_detail ".
+              "  WHERE billpkgnum = ". $self->billpkgnum.
+              "  ORDER BY detailnum";
+    my $sth = dbh->prepare($sql) or die dbh->errstr;
+    $sth->execute or die $sth->errstr;
+
+    #avoid the fetchall_arrayref and loop for less memory usage?
+
+    map { (defined($_->[0]) && $_->[0] eq 'C')
+            ? &{$format_sub}(      $_->[1] )
+            : &{$escape_function}( $_->[1] );
+        }
+      @{ $sth->fetchall_arrayref };
+
+  }
+
 }
 
 =item details_header [ OPTION => VALUE ... ]
@@ -502,8 +580,6 @@ sub details_header {
   my $self = shift;
   return '' unless defined dbdef->table('cust_bill_pkg_detail');
 
-  eval "use Text::CSV_XS;";
-  die $@ if $@;
   my $csv = new Text::CSV_XS;
 
   my @detail = 
@@ -656,6 +732,76 @@ sub unitrecur {
     : $self->getfield('unitrecur');
 }
 
+=item set_display OPTION => VALUE ...
+
+A helper method for I<insert>, populates the pseudo-field B<display> with
+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 
+
+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
+
+sub set_display {
+  my( $self, %opt ) = @_;
+  my $part_pkg = $opt{'part_pkg'};
+  my $cust_pkg = new FS::cust_pkg { pkgpart => $opt{real_pkgpart} };
+
+  my $conf = new FS::Conf;
+
+  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;
+
+  return $self->set('display', [])
+    unless $separate || $categoryname || $usage_mandate;
+  
+  my @display = ();
+
+  my %hash = ( 'section' => $categoryname );
+
+  my $usage_section =            $part_pkg->option('usage_section', 'Hush!')
+                    || $cust_pkg->part_pkg->option('usage_section', 'Hush!');
+
+  my $summary =            $part_pkg->option('summarize_usage', 'Hush!')
+              || $cust_pkg->part_pkg->option('summarize_usage', 'Hush!');
+
+  if ( $separate ) {
+    push @display, new FS::cust_bill_pkg_display { type => 'S', %hash };
+    push @display, new FS::cust_bill_pkg_display { type => 'R', %hash };
+  } else {
+    push @display, new FS::cust_bill_pkg_display
+                     { type => '',
+                       %hash,
+                       ( ( $usage_mandate ) ? ( 'summary' => 'Y' ) : () ),
+                     };
+  }
+
+  if ($separate && $usage_section && $summary) {
+    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) {
+    $hash{section} = $usage_section if $usage_section;
+    push @display, new FS::cust_bill_pkg_display { type => 'U', %hash };
+  }
+
+  $self->set('display', \@display);
+
+}
+
 =item disintegrate
 
 Returns a list of cust_bill_pkg objects each with no more than a single class
@@ -743,28 +889,29 @@ usage.
 
 sub usage {
   my( $self, $classnum ) = @_;
-  my $sum = 0;
-  my @values = ();
+  $self->regularize_details;
 
   if ( $self->get('details') ) {
 
-    @values = 
-      map { $_->[2] }
-      grep { ref($_) && ( defined($classnum) ? $_->[3] eq $classnum : 1 ) }
-      @{ $self->get('details') };
+    return sum( 0, 
+      map { $_->amount || 0 }
+      grep { !defined($classnum) or $classnum eq $_->classnum }
+      @{ $self->get('details') }
+    );
 
-  }else{
+  } else {
 
-    my $hashref = { 'billpkgnum' => $self->billpkgnum };
-    $hashref->{ 'classnum' } = $classnum if defined($classnum);
-    @values = map { $_->amount } qsearch('cust_bill_pkg_detail', $hashref);
+    my $sql = 'SELECT SUM(COALESCE(amount,0)) FROM cust_bill_pkg_detail '.
+              ' WHERE billpkgnum = '. $self->billpkgnum;
+    $sql .= " AND classnum = $classnum" if defined($classnum);
 
-  }
+    my $sth = dbh->prepare($sql) or die dbh->errstr;
+    $sth->execute or die $sth->errstr;
+
+    return $sth->fetchrow_arrayref->[0] || 0;
 
-  foreach ( @values ) {
-    $sum += $_ if $_;
   }
-  $sum;
+
 }
 
 =item usage_classes
@@ -776,16 +923,14 @@ details.
 
 sub usage_classes {
   my( $self ) = @_;
+  $self->regularize_details;
 
   if ( $self->get('details') ) {
 
-    my %seen = ();
-    foreach my $detail ( grep { ref($_) } @{$self->get('details')} ) {
-      $seen{ $detail->[3] } = 1;
-    }
+    my %seen = ( map { $_->classnum => 1 } @{ $self->get('details') } );
     keys %seen;
 
-  }else{
+  } else {
 
     map { $_->classnum }
         qsearch({ table   => 'cust_bill_pkg_detail',
@@ -810,8 +955,6 @@ sub cust_bill_pkg_display {
   my $default =
     new FS::cust_bill_pkg_display { billpkgnum =>$self->billpkgnum };
 
-  return ( $default ) unless defined dbdef->table('cust_bill_pkg_display');#hmmm
-
   my $type = $opt{type} if exists $opt{type};
   my @result;
 
@@ -898,12 +1041,124 @@ sub cust_bill_pkg_discount {
 
 =cut
 
-sub recur_show_zero {
-  my $self = shift;
+sub recur_show_zero { shift->_X_show_zero('recur'); }
+sub setup_show_zero { shift->_X_show_zero('setup'); }
+
+sub _X_show_zero {
+  my( $self, $what ) = @_;
+
+  return 0 unless $self->$what() == 0 && $self->pkgnum;
+
+  $self->cust_pkg->_X_show_zero($what);
+}
+
+=back
+
+=head1 CLASS METHODS
+
+=over 4
+
+=item usage_sql
+
+Returns an SQL expression for the total usage charges in details on
+an item.
 
-     $self->recur == 0
-  && $self->pkgnum
-  && $self->cust_pkg->part_pkg->recur_show_zero;
+=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;
+  }
 
 }