mysql has no DISTINCT ON either, sigh

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

diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm
index 45648f1..07aa367 100644 (file)
--- a/FS/FS/cust_bill.pm
+++ b/FS/FS/cust_bill.pm
@@ -2716,18 +2716,33 @@ sub re_X {
   push @where, "cust_bill._date < ". (time-86400*$param{'days'})
     if $param{'days'};
 
-  my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
+  if ( $param{'newest_percust'} ) {
 
-  my $addl_from = 'left join cust_main using ( custnum )';
+    #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
+    #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
+
+    my @newest_where = map { s/\bcust_bill\./newest_cust_bill./g; }
+                           grep ! /^cust_main./, @where;
+    my $newest_where = scalar(@newest_where)
+                         ? ' AND '. join(' AND ', @newest_where)
+                        : '';
+
+    push @where, "cust_bill._date = (
+      SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill
+        WHERE newest_cust_bill.custnum = cust_bill.custnum
+          $newest_where
+    )";
 
-  if ( $param{'newest_percust'} ) {
-    $distinct = 'DISTINCT ON ( cust_bill.custnum )';
-    $orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
   }
+
+  my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
+
+  my $addl_from = 'left join cust_main using ( custnum )';
      
   my @cust_bill = qsearch( 'cust_bill',
                            {},
-                           "$distinct cust_bill.*",
+                           #"$distinct cust_bill.*",
+                           "cust_bill.*",
                            $extra_sql,
                            '',
                            $addl_from