optimize customer list, RT#20173

[freeside.git] / FS / FS / part_pkg.pm

diff --git a/FS/FS/part_pkg.pm b/FS/FS/part_pkg.pm
index 97bce45..9ed623f 100644 (file)
--- a/FS/FS/part_pkg.pm
+++ b/FS/FS/part_pkg.pm
@@ -4,7 +4,9 @@ use base qw( FS::part_pkg::API
            );
 
 use strict;
-use vars qw( %plans $DEBUG $setup_hack $skip_pkg_svc_hack );
+use vars qw( %plans $DEBUG $setup_hack $skip_pkg_svc_hack
+             $cache_enabled %cache_link %cache_pkg_svc
+           );
 use Carp qw(carp cluck confess);
 use Scalar::Util qw( blessed );
 use DateTime;
@@ -33,9 +35,14 @@ use FS::part_pkg_currency;
 use FS::part_svc_link;
 
 $DEBUG = 0;
+
 $setup_hack = 0;
 $skip_pkg_svc_hack = 0;
 
+$cache_enabled = 0;
+%cache_link = ();
+%cache_pkg_svc = ();
+
 =head1 NAME
 
 FS::part_pkg - Object methods for part_pkg objects
@@ -1104,19 +1111,19 @@ definition.
 sub pkg_svc {
   my $self = shift;
 
+  return @{ $cache_pkg_svc{$self->pkgpart} }
+    if $cache_enabled && $cache_pkg_svc{$self->pkgpart};
+
 #  #sort { $b->primary cmp $a->primary } 
 #    grep { $_->quantity }
 #      qsearch( 'pkg_svc', { 'pkgpart' => $self->pkgpart } );
 
   my $opt = ref($_[0]) ? $_[0] : { @_ };
-  my %pkg_svc = map  { $_->svcpart => $_ }
-                grep { $_->quantity }
-                qsearch( 'pkg_svc', { 'pkgpart' => $self->pkgpart } );
+  my %pkg_svc = map  { $_->svcpart => $_ } $self->_pkg_svc;
 
   unless ( $opt->{disable_linked} ) {
     foreach my $dst_pkg ( map $_->dst_pkg, $self->svc_part_pkg_link ) {
-      my @pkg_svc = grep { $_->quantity }
-                    qsearch( 'pkg_svc', { pkgpart=>$dst_pkg->pkgpart } );
+      my @pkg_svc = $dst_pkg->_pkg_svc;
       foreach my $pkg_svc ( @pkg_svc ) {
         if ( $pkg_svc{$pkg_svc->svcpart} ) {
           my $quantity = $pkg_svc{$pkg_svc->svcpart}->quantity;
@@ -1128,10 +1135,25 @@ sub pkg_svc {
     }
   }
 
-  values(%pkg_svc);
+  my @pkg_svc = values(%pkg_svc);
+
+  $cache_pkg_svc{$self->pkgpart} = \@pkg_svc if $cache_enabled;
+
+  @pkg_svc;
 
 }
 
+sub _pkg_svc {
+  my $self = shift;
+  grep { $_->quantity }
+    qsearch({
+      'select'    => 'pkg_svc.*, part_svc.*',
+      'table'     => 'pkg_svc',
+      'addl_from' => 'LEFT JOIN part_svc USING ( svcpart )',
+      'hashref'   => { 'pkgpart' => $self->pkgpart },
+    });
+}
+
 =item svcpart [ SVCDB ]
 
 Returns the svcpart of the primary service definition (see L<FS::part_svc>)
@@ -1402,12 +1424,10 @@ sub option {
   my( $self, $opt, $ornull ) = @_;
 
   #cache: was pulled up in the original part_pkg query
-  if ( $opt =~ /^(setup|recur)_fee$/ && defined($self->hashref->{"_$opt"}) ) {
-    return $self->hashref->{"_$opt"};
-  }
+  return $self->hashref->{"_opt_$opt"}
+    if exists $self->hashref->{"_opt_$opt"};
 
-  cluck "$self -> option: searching for $opt"
-    if $DEBUG;
+  cluck "$self -> option: searching for $opt" if $DEBUG;
   my $part_pkg_option =
     qsearchs('part_pkg_option', {
       pkgpart    => $self->pkgpart,
@@ -1541,14 +1561,25 @@ sub supp_part_pkg_link {
 
 sub _part_pkg_link {
   my( $self, $type ) = @_;
-  qsearch({ table    => 'part_pkg_link',
-            hashref  => { 'src_pkgpart' => $self->pkgpart,
-                          'link_type'   => $type,
-                          #protection against infinite recursive links
-                          'dst_pkgpart' => { op=>'!=', value=> $self->pkgpart },
-                        },
-            order_by => "ORDER BY hidden",
-         });
+
+  return @{ $cache_link{$type}->{$self->pkgpart} }
+    if $cache_enabled && $cache_link{$type}->{$self->pkgpart};
+
+  cluck $type.'_part_pkg_link called' if $DEBUG;
+
+  my @ppl = 
+    qsearch({ table    => 'part_pkg_link',
+              hashref  => { src_pkgpart => $self->pkgpart,
+                            link_type   => $type,
+                            #protection against infinite recursive links
+                            dst_pkgpart => { op=>'!=', value=> $self->pkgpart },
+                          },
+              order_by => "ORDER BY hidden",
+           });
+
+  $cache_link{$type}->{$self->pkgpart} = \@ppl if $cache_enabled;
+
+  return @ppl;
 }
 
 sub self_and_bill_linked {
@@ -2308,6 +2339,26 @@ sub _pkgs_sql {
 
 }
 
+=item join_options_sql
+
+Returns an SQL fragment for JOINing the part_pkg_option records for this
+package's setup_fee and recur_fee (as setup_option and recur_option,
+respectively).  Useful for optimization.
+
+=cut
+
+sub join_options_sql {
+  #my $class = shift;
+  "
+    LEFT JOIN part_pkg_option AS setup_option
+      ON (     part_pkg.pkgpart = setup_option.pkgpart
+           AND setup_option.optionname = 'setup_fee' )
+    LEFT JOIN part_pkg_option AS recur_option
+      ON (     part_pkg.pkgpart = recur_option.pkgpart
+           AND recur_option.optionname = 'recur_fee' )
+  ";
+}
+
 =back
 
 =head1 SUBROUTINES