X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fcust_bill%2FSearch.pm;h=ee5da3be87f7023d8619a4dbdf71adab132024e2;hb=bb7e827141c9ed68f30765c9ca2ddcd1d760ad2d;hp=2a67529c19620e0f08ee27ac9743e9c4d44852fc;hpb=7b40e0e045368d350529d78208be0e6124f94b5a;p=freeside.git

diff --git a/FS/FS/cust_bill/Search.pm b/FS/FS/cust_bill/Search.pm
index 2a67529c1..ee5da3be8 100644
--- a/FS/FS/cust_bill/Search.pm
+++ b/FS/FS/cust_bill/Search.pm
@@ -6,18 +6,22 @@ use FS::UI::Web;
 use FS::Record qw( qsearchs dbh );
 use FS::cust_main;
 use FS::access_user;
+use FS::Conf;
+use charnames ':full';
                                                                                 
 =item search HASHREF                                                            
                                                                                 
 (Class method)                                                                  
                                                                                 
-Returns a qsearch hash expression to search for parameters specified in HASHREF.
-In addition to all parameters accepted by search_sql_where, the following
-additional parameters valid:
+Returns a qsearch hash expression to search for parameters specified in
+HASHREF.  In addition to all parameters accepted by search_sql_where, the
+following additional parameters valid:
 
 =over 4                                                                         
 
-=item newest_percust
+=item newest_percust - only show the most recent invoice for each customer
+
+=item invoiced - show the invoiced amount (excluding discounts) instead of gross sales
 
 =back
 
@@ -26,7 +30,8 @@ additional parameters valid:
 sub search {
   my( $class, $params ) = @_;
 
-  my( $count_query, $count_addl ) = ( '', '' );
+  my $count_query = '';
+  my @count_addl;
 
   #some false laziness w/cust_bill::re_X
 
@@ -38,41 +43,92 @@ sub search {
 
   my $join_cust_main = FS::UI::Web::join_cust_main('cust_bill');
 
+  # get discounted, credited, and paid amounts here, for use in report
+  #
+  # Testing shows that this is by far the most efficient way to do the 
+  # joins. In particular it's almost 100x faster to join to an aggregate
+  # query than to put the subquery in a select expression. It also makes
+  # it more convenient to do arithmetic between columns, use them as sort
+  # keys, etc.
+  #
+  # Each ends with a RIGHT JOIN cust_bill so that it includes all invnums,
+  # even if they have no discounts/credits/payments; the total amount is then
+  # coalesced to zero.
+  my $join = "$join_cust_main
+  JOIN (
+    SELECT COALESCE(SUM(cust_bill_pkg_discount.amount), 0) AS discounted,
+      invnum
+      FROM cust_bill_pkg_discount
+        JOIN cust_bill_pkg USING (billpkgnum)
+        RIGHT JOIN cust_bill USING (invnum)
+      GROUP BY invnum
+    ) AS _discount USING (invnum)
+  JOIN (
+    SELECT COALESCE(SUM(cust_credit_bill.amount), 0) AS credited, invnum
+      FROM cust_credit_bill
+        RIGHT JOIN cust_bill USING (invnum)
+      GROUP BY invnum
+    ) AS _credit USING (invnum)
+  JOIN (
+    SELECT COALESCE(SUM(cust_bill_pay.amount), 0) AS paid, invnum
+      FROM cust_bill_pay
+        RIGHT JOIN cust_bill USING (invnum)
+      GROUP BY invnum
+    ) AS _pay USING (invnum)
+  ";
+
   unless ( $count_query ) {
-    $count_query = 'SELECT COUNT(*), '. join(', ',
-                     map "SUM($_)",
-                         ( 'charged',
-                           FS::cust_bill->net_sql,
-                           FS::cust_bill->owed_sql,
-                         )
-                   );
-    $count_addl = [ '$%.2f invoiced (gross)',
-                    '$%.2f invoiced (net)',
-                    '$%.2f outstanding balance',
-                  ];
+
+    my $money = (FS::Conf->new->config('money_char') || '$') . '%.2f';
+
+    my @sums = ( 'credited',                  # credits
+                 'charged - credited',        # net sales
+                 'charged - credited - paid', # balance due
+               );
+
+    @count_addl = ( "\N{MINUS SIGN} $money credited",
+                    "= $money net sales",
+                    "$money outstanding balance",
+                  );
+
+    if ( $params->{'invoiced'} ) {
+
+      unshift @sums, 'charged';
+      unshift @count_addl, "$money invoiced";
+
+    } else {
+
+      unshift @sums, 'charged + discounted', 'discounted';
+      unshift @count_addl, "$money gross sales",
+                           "\N{MINUS SIGN} $money discounted";
+
+    }
+
+    $count_query = 'SELECT COUNT(*), '. join(', ', map "SUM($_)", @sums);
   }
-  $count_query .=  " FROM cust_bill $join_cust_main $extra_sql";
+  $count_query .=  " FROM cust_bill $join $extra_sql";
 
   #$sql_query =
   +{
     'table'     => 'cust_bill',
-    'addl_from' => $join_cust_main,
+    'addl_from' => $join,
     'hashref'   => {},
     'select'    => join(', ',
                      'cust_bill.*',
                      #( map "cust_main.$_", qw(custnum last first company) ),
                      'cust_main.custnum as cust_main_custnum',
                      FS::UI::Web::cust_sql_fields(),
-                     #$class->owed_sql. ' AS owed',
-                     #$class->net_sql.  ' AS net',
-                     FS::cust_bill->owed_sql. ' AS owed',
-                     FS::cust_bill->net_sql.  ' AS net',
+                     '(charged + discounted) as gross',
+                     'discounted',
+                     'credited',
+                     '(charged - credited) as net',
+                     '(charged - credited - paid) as owed',
                    ),
     'extra_sql' => $extra_sql,
     'order_by'  => 'ORDER BY '. ( $params->{'order_by'} || 'cust_bill._date' ),
 
     'count_query' => $count_query,
-    'count_addl'  => $count_addl,
+    'count_addl'  => \@count_addl,
   };
 
 }
@@ -176,16 +232,10 @@ sub search_sql_where {
                      ? @{ $param->{'cust_classnum'} }
                      :  ( $param->{'cust_classnum'} );
 
-    @classnum = grep /^(\d*)$/, @classnum;
+    @classnum = grep /^(\d+)$/, @classnum;
 
     if ( @classnum ) {
-      push @search, '( '. join(' OR ', map {
-                                             $_ ? "cust_main.classnum = $_"
-                                                : "cust_main.classnum IS NULL"
-                                           }
-                                           @classnum
-                              ).
-                    ' )';
+      push @search, 'COALESCE(cust_main.classnum, 0) IN ('.join(',', @classnum).')';
     }
 
   }
@@ -206,6 +256,7 @@ sub search_sql_where {
     push @search, "cust_bill.invnum <= $1";
   }
 
+  # these are from parse_lt_gt, and should already be sanitized
   #charged
   if ( $param->{charged} ) {
     my @charged = ref($param->{charged})
@@ -216,21 +267,22 @@ sub search_sql_where {
                       @charged;
   }
 
-  my $owed_sql = FS::cust_bill->owed_sql;
+  #my $owed_sql = FS::cust_bill->owed_sql;
+  my $owed_sql = '(cust_bill.charged - credited - paid)';
+  my $net_sql = '(cust_bill.charged - credited)';
 
   #owed
   if ( $param->{owed} ) {
     my @owed = ref($param->{owed})
                  ? @{ $param->{owed} }
                  : ($param->{owed});
-    push @search, map { s/^owed/$owed_sql/; $_; }
-                      @owed;
+    push @search, map { s/^owed/$owed_sql/; $_ } @owed;
   }
 
   #open/net flags
   push @search, "0 != $owed_sql"
     if $param->{'open'};
-  push @search, '0 != '. FS::cust_bill->net_sql
+  push @search, "0 != $net_sql"
     if $param->{'net'};
 
   #days