From 739d18b193ef10c6a238759af994f4abd53b4f8a Mon Sep 17 00:00:00 2001
From: Ivan Kohler <ivan@freeside.biz>
Date: Mon, 5 May 2014 18:19:41 -0700
Subject: [PATCH] commission reporting optimizations, RT#28964

---
 FS/FS/sales.pm                          | 27 ++++++++++++++++++---------
 httemplate/search/sales_commission.html | 31 ++++++++++++++++++++++---------
 2 files changed, 40 insertions(+), 18 deletions(-)

diff --git a/FS/FS/sales.pm b/FS/FS/sales.pm
index bdeaf1b68..f1b406da7 100644
--- a/FS/FS/sales.pm
+++ b/FS/FS/sales.pm
@@ -205,28 +205,37 @@ sub cust_bill_pkg {
   qsearch( $self->cust_bill_pkg_search(@_) )
 }
 
-sub cust_credit {
+sub cust_credit_search {
   my( $self, $sdate, $edate, %search ) = @_;
 
   $search{'hashref'}->{'commission_salesnum'} = $self->salesnum;
 
+  my @where = ();
+  push @where, "cust_credit._date >= $sdate" if $sdate;
+  push @where, "cust_credit._date  < $edate" if $edate;
+
   my $classnum_sql = '';
   if ( exists($search{'commission_classnum'}) ) {
     my $classnum = delete($search{'commission_classnum'});
-    $classnum_sql = " AND part_pkg.classnum ". ( $classnum ? " = $classnum"
-                                                           : " IS NULL "    );
+    push @where, 'part_pkg.classnum '. ( $classnum ? " = $classnum"
+                                                   : " IS NULL "    );
 
     $search{'addl_from'} .=
       ' LEFT JOIN cust_pkg ON ( commission_pkgnum = cust_pkg.pkgnum ) '.
       ' LEFT JOIN part_pkg USING ( pkgpart ) ';
   }
 
-  qsearch({ 'table'     => 'cust_credit',
-            'extra_sql' => " AND cust_credit._date >= $sdate ".
-                           " AND cust_credit._date  < $edate ".
-                           $classnum_sql,
-            %search,
-         });
+  my $extra_sql = "AND ".join(' AND ', map {"( $_ )"} @where);
+
+  { 'table'     => 'cust_credit',
+    'extra_sql' => $extra_sql,
+    %search,
+  };
+}
+
+sub cust_credit {
+  my $self = shift;
+  qsearch( $self->cust_credit_search(@_) )
 }
 
 =back
diff --git a/httemplate/search/sales_commission.html b/httemplate/search/sales_commission.html
index 57b6cdcd5..710461c40 100644
--- a/httemplate/search/sales_commission.html
+++ b/httemplate/search/sales_commission.html
@@ -66,17 +66,20 @@ my $sales_sub_maker = sub {
   sub {
     my $sales = shift;
 
-    #efficiency improvement: ask the db for a sum instead of all the records
-    my $total = 0;
-    my @cust_bill_pkg = $sales->cust_bill_pkg(
+    my $search = $sales->cust_bill_pkg_search(
       $beginning,
       $ending,
       'cust_main_sales' => $cust_main_sales,
       'paid' => $paid,
     );
-    $total += $_->get($field) foreach @cust_bill_pkg;
+    die 'cust_bill_pkg_search hashref not yet handled' if $search->{hashref};
 
-    $money_char. sprintf('%.2f', $total);
+    my $total = FS::Record->scalar_sql(
+      "SELECT SUM(cust_bill_pkg.$field) FROM cust_bill_pkg ". #$search->{table}
+      $search->{addl_from}. ' '. $search->{extra_sql}
+    );
+
+    return $money_char. sprintf('%.2f', $total);
   };
 };
 
@@ -84,11 +87,21 @@ my $commission_sub = sub {
   my $sales = shift;
 
   #efficiency improvement: ask the db for a sum instead of all the records
-  my $total_credit = 0;
-  my @cust_credit  = $sales->cust_credit( $beginning, $ending );
-  $total_credit += $_->amount foreach @cust_credit;
+  #my $total_credit = 0;
+  #my @cust_credit  = $sales->cust_credit( $beginning, $ending );
+  #$total_credit += $_->amount foreach @cust_credit;
+
+  my $search = $sales->cust_credit_search( $beginning, $ending );
+
+  my $sql =
+    "SELECT SUM(cust_credit.amount) FROM cust_credit ". #$search->{table}
+    $search->{addl_from}. ' '.
+    ' WHERE commission_salesnum = ? '. #$search->{hashref}
+    $search->{extra_sql};
+
+  my $total = FS::Record->scalar_sql($sql, $sales->salesnum);
 
-  $money_char. sprintf('%.2f', $total_credit);
+  $money_char. sprintf('%.2f', $total);
 };
 
 my $commission_link = [ 'cust_credit.html?'.
-- 
2.11.0