improve speed in customer search, #13364

[freeside.git] / httemplate / search / cust_bill_pkg_discount.html

diff --git a/httemplate/search/cust_bill_pkg_discount.html b/httemplate/search/cust_bill_pkg_discount.html
index 088b291..bb8038a 100644 (file)
--- a/httemplate/search/cust_bill_pkg_discount.html
+++ b/httemplate/search/cust_bill_pkg_discount.html
@@ -26,6 +26,14 @@
                    sub { time2str('%b %d %Y', shift->_date ) },
                    \&FS::UI::Web::cust_fields,
                  ],
+                 'sort_fields' => [
+                   '',
+                   'amount',
+                   'months',
+                   'pkg',
+                   'invnum',
+                   '_date',
+                 ],
                  'links'       => [
                    #'',
                    '', #link to customer discount???
@@ -85,6 +93,11 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   push @where, "cust_main.agentnum = $1";
 }
 
+#usernum
+if ( $cgi->param('usernum') =~ /^(\d+)$/ ) {
+  push @where, "cust_pkg_discount.usernum = $1";
+}
+
 # #classnum
 # # not specified: all classes
 # # 0: empty class
@@ -110,18 +123,25 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
 
 my $count_query = "SELECT COUNT(*), SUM(amount)";
 
-my $join_cust =  '      JOIN cust_bill_pkg USING ( billpkgnum )
-                        JOIN cust_bill USING ( invnum ) 
-                   LEFT JOIN cust_main USING ( custnum ) ';
+my $join_cust_pkg_discount =
+  'LEFT JOIN cust_pkg_discount USING (pkgdiscountnum)';
+
+my $join_cust =
+  '      JOIN cust_bill_pkg USING ( billpkgnum )
+         JOIN cust_bill USING ( invnum ) 
+    LEFT JOIN cust_main USING ( custnum ) ';
 
-my $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum )
-                 LEFT JOIN part_pkg USING ( pkgpart ) ';
-                 #LEFT JOIN part_pkg AS override
-                 #  ON pkgpart_override = override.pkgpart ';
+my $join_pkg =
+  ' LEFT JOIN cust_pkg ON ( cust_bill_pkg.pkgnum = cust_pkg.pkgnum )
+  LEFT JOIN part_pkg USING ( pkgpart ) ';
+  #LEFT JOIN part_pkg AS override
+  #  ON pkgpart_override = override.pkgpart ';
 
 my $where = ' WHERE '. join(' AND ', @where);
 
-$count_query .= " FROM cust_bill_pkg_discount $join_cust $join_pkg $where";
+$count_query .=
+  " FROM cust_bill_pkg_discount $join_cust_pkg_discount $join_cust $join_pkg ".
+  $where;
 
 my @select = (
                'cust_bill_pkg_discount.*',
@@ -135,7 +155,7 @@ push @select, 'cust_main.custnum',
 
 my $query = {
   'table'     => 'cust_bill_pkg_discount',
-  'addl_from' => "$join_cust $join_pkg",
+  'addl_from' => "$join_cust_pkg_discount $join_cust $join_pkg",
   'hashref'   => {},
   'select'    => join(', ', @select ),
   'extra_sql' => $where,