optimize invoice rendering with lots of CDRs, RT#15155

3 files changed, 99 insertions, 56 deletions

diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm
index 4dc41f091..77c571b8c 100644
--- a/FS/FS/cust_bill.pm
+++ b/FS/FS/cust_bill.pm
@@ -3034,7 +3034,7 @@ sub print_generic {
     $options{'section'} = $section if $multisection;
     $options{'format'} = $format;
     $options{'escape_function'} = $escape_function;
-    $options{'format_function'} = sub { () } unless $unsquelched;
+    $options{'no_usage'} = 1 unless $unsquelched;
     $options{'unsquelched'} = $unsquelched;
     $options{'summary_page'} = $summarypage;
     $options{'skip_usage'} =
@@ -4762,6 +4762,7 @@ format: the invoice format.
 
 escape_function: the function used to escape strings.
 
+DEPRECATED? (expensive, mostly unused?)
 format_function: the function used to format CDRs.
 
 section: a hashref containing 'description'; if this is present, 
@@ -4790,6 +4791,7 @@ sub _items_cust_bill_pkg {
   my $format = $opt{format} || '';
   my $escape_function = $opt{escape_function} || sub { shift };
   my $format_function = $opt{format_function} || '';
+  my $no_usage = $opt{no_usage} || '';
   my $unsquelched = $opt{unsquelched} || ''; #unused
   my $section = $opt{section}->{description} if $opt{section};
   my $summary_page = $opt{summary_page} || ''; #unused
@@ -4846,6 +4848,7 @@ sub _items_cust_bill_pkg {
       my %details_opt = ( 'format'          => $format,
                           'escape_function' => $escape_function,
                           'format_function' => $format_function,
+                          'no_usage'        => $opt{'no_usage'},
                         );
 
       if ( $cust_bill_pkg->pkgnum > 0 ) {
@@ -5003,7 +5006,7 @@ sub _items_cust_bill_pkg {
 
             #instead of omitting details entirely in this case (unwanted side
             # effects), just omit CDRs
-            $details_opt{'format_function'} = sub { () }
+            $details_opt{'no_usage'} = 1
               if $type && $type eq 'R';
 
             push @d, $cust_bill_pkg->details(%details_opt);
diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm
index 9cc6e7cab..2c157150d 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;
@@ -444,61 +445,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 { $_->[0] eq 'C'
+            ? &{$format_sub}(      $_->[1] )
+            : &{$escape_function}( $_->[1] );
+        }
+      @{ $sth->fetchall_arrayref };
+
+  }
+
 }
 
 =item details_header [ OPTION => VALUE ... ]
@@ -514,8 +554,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 = 
@@ -825,10 +863,10 @@ usage.
 
 sub usage {
   my( $self, $classnum ) = @_;
-  my $sum = 0;
 
   if ( $self->get('details') ) {
 
+    my $sum = 0;
     foreach my $value (
       map { ref($_) eq 'HASH'
               ? $_->{'amount'}
diff --git a/FS/FS/cust_bill_pkg_detail.pm b/FS/FS/cust_bill_pkg_detail.pm
index eb0ae9677..46f6e170d 100644
--- a/FS/FS/cust_bill_pkg_detail.pm
+++ b/FS/FS/cust_bill_pkg_detail.pm
@@ -163,11 +163,13 @@ for tabular appearance in those environments if possible.
 If I<escape_function> is set then the format is processed by this
 function before being returned.
 
+DEPRECATED? (mostly unused, expensive)
 If I<format_function> is set then the detail is handed to this callback
 for processing.
 
 =cut
 
+#totally false laziness w/cust_bill_pkg->detail
 sub formatted {
   my ( $self, %opt ) = @_;
   my $format = $opt{format} || '';