mysql has no DISTINCT ON either, sigh

5 files changed, 99 insertions, 29 deletions

diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm
index 45648f1..07aa367 100644
--- 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
diff --git a/FS/FS/h_Common.pm b/FS/FS/h_Common.pm
index d55da8c..d9846ee 100644
--- a/FS/FS/h_Common.pm
+++ b/FS/FS/h_Common.pm
@@ -30,9 +30,10 @@ inherit from.
 
 =item sql_h_search END_TIMESTAMP [ START_TIMESTAMP ] 
 
-Returns an a list consisting of the "SELECT" and "EXTRA_SQL" SQL fragments to
-search for the appropriate history records created before END_TIMESTAMP
-and (optionally) not cancelled before START_TIMESTAMP.
+Returns an a list consisting of the "SELECT", "EXTRA_SQL", SQL fragments, a
+placeholder for "CACHE_OBJ" and an "AS" SQL fragment, to search for the
+appropriate history records created before END_TIMESTAMP and (optionally) not
+cancelled before START_TIMESTAMP.
 
 =cut
 
@@ -48,25 +49,45 @@ sub sql_h_search {
     confess 'Called sql_h_search without END_TIMESTAMP';
   }
 
-  my $notcancelled = '';
+  my( $notcancelled, $notcancelled_mr ) = ( '', '' );
   if ( scalar(@_) && $_[0] ) {
-    $notcancelled = "AND 0 = ( SELECT COUNT(*) FROM $table as notdel
-                                WHERE notdel.$pkey = maintable.$pkey
-                                AND notdel.history_action = 'delete'
-                                AND notdel.history_date > maintable.history_date
-                                AND notdel.history_date <= $_[0]
-                             )";
+    $notcancelled =
+      "AND 0 = ( SELECT COUNT(*) FROM $table as notdel
+                   WHERE notdel.$pkey = maintable.$pkey
+                     AND notdel.history_action = 'delete'
+                     AND notdel.history_date > maintable.history_date
+                     AND notdel.history_date <= $_[0]
+               )";
+    $notcancelled_mr =
+      "AND 0 = ( SELECT COUNT(*) FROM $table as notdel_mr
+                   WHERE notdel_mr.$pkey = mostrecent.$pkey
+                     AND notdel_mr.history_action = 'delete'
+                     AND notdel_mr.history_date > mostrecent.history_date
+                     AND notdel_mr.history_date <= $_[0]
+               )";
   }
 
   (
-    "DISTINCT ON ( $pkey ) *",
+    #"DISTINCT ON ( $pkey ) *",
+    "*",
 
     "AND history_date <= $end
      AND (    history_action = 'insert'
            OR history_action = 'replace_new'
          )
      $notcancelled
-     ORDER BY $pkey ASC, history_date DESC",
+     AND history_date = ( SELECT MAX(mostrecent.history_date)
+                            FROM $table AS mostrecent
+                            WHERE mostrecent.$pkey = maintable.$pkey
+			      AND mostrecent.history_date <= $end
+			      AND (    mostrecent.history_action = 'insert'
+			            OR mostrecent.history_action = 'replace_new'
+				  )
+			      $notcancelled_mr
+                        )
+
+     ORDER BY $pkey ASC",
+     #ORDER BY $pkey ASC, history_date DESC",
 
      '',
 
diff --git a/httemplate/edit/rate.cgi b/httemplate/edit/rate.cgi
index 72a04c3..55fe319 100644
--- a/httemplate/edit/rate.cgi
+++ b/httemplate/edit/rate.cgi
@@ -58,17 +58,21 @@ Rate plan
   <TH><FONT SIZE=-1>Granularity</FONT></TH>
 </TR>
 % foreach my $rate_region (
-%     sort { lc($a->regionname) cmp lc($b->regionname) }
+%     #sort { lc($a->regionname) cmp lc($b->regionname) }
 %     qsearch({
-%               'select'    => 'DISTINCT ON ( regionnum ) rate_region.*',
 %               'table'     => 'rate_region',
 %               'hashref'   => {},
+%               'order_by'  => 'ORDER BY lc(regionname)',
+%
+%               #'select'    => 'DISTINCT ON ( regionnum ) rate_region.*',
+%               #...
 %               #'addl_from' => 'INNER JOIN rate_prefix USING ( regionnum )',
 %               #'extra_sql' => "WHERE countrycode != '1'",
 %
 %                              # 'ORDER BY regionname'
 %                              # ERROR: SELECT DISTINCT ON expressions must
 %                              #        match initial ORDER BY expressions
+%                              # also, DISTINCT ON is a Pg-ism
 %            })
 %   ) {
 %     my $n = $rate_region->regionnum;
diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html
index aa4b2f1..be7406e 100755
--- a/httemplate/search/cust_bill.html
+++ b/httemplate/search/cust_bill.html
@@ -59,7 +59,7 @@ my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql;
 
 my( $count_query, $sql_query );
 my $count_addl = '';
-my $distinct = '';
+#my $distinct = '';
 my($begin, $end) = ( '', '' );
 my $agentnum = '';
 my($open, $days) = ( '', '' );
@@ -141,18 +141,34 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) {
     push @where, "cust_bill._date < ". (time-86400*$days) if $days;
   }
 
-  #here is the agent virtualization
-  push @where, $agentnums_sql;
-  my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
-
   if ( $cgi->param('newest_percust') ) {
     $newest_percust = 1;
-    $distinct = 'DISTINCT ON ( cust_bill.custnum )';
-    $orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
+
+    #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
+    #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
     #$count_query = "SELECT 'N/A', 'N/A', 'N/A'"; #XXXXXXX fix
+    
+    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
+    )";
+
+
     $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'";
   }
 
+  #here is the agent virtualization
+  push @where, $agentnums_sql;
+
+  my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
+
   unless ( $count_query ) {
     $count_query = "SELECT COUNT(*), sum(charged), sum($owed)";
     $count_addl = [ '$%.2f total invoiced',
@@ -165,7 +181,8 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) {
     'table'     => 'cust_bill',
     'addl_from' => $join_cust_main,
     'hashref'   => {},
-    'select'    => "$distinct ". join(', ',
+    #'select'    => "$distinct ". join(', ',
+    'select'    => join(', ',
                      'cust_bill.*',
                      #( map "cust_main.$_", qw(custnum last first company) ),
                      'cust_main.custnum as cust_main_custnum',
diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index bb9c8f4..eddb3da 100755
--- a/httemplate/search/report_tax.cgi
+++ b/httemplate/search/report_tax.cgi
@@ -444,7 +444,20 @@ my %base_regions = ();
 foreach my $r (
   qsearch( 'cust_main_county',
            {},
-           "DISTINCT ON ( country, state, county, CASE WHEN taxname IS NULL THEN '' ELSE taxname END ) *",
+           "DISTINCT
+              country,
+              state,
+              county,
+              CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname,".
+
+	      #a little bit unsure of this part... test?
+	      #ah, it looks like it winds up being irrelevant as ->{'tax'} 
+	      # from $regions is not displayed when show_taxclasses is on
+	      ( $cgi->param('show_taxclasses')
+                  ? " CASE WHEN taxclass IS NULL THEN '' ELSE taxclass END "
+                  : " '' "
+       	      )." AS taxclass"
+           ,
            $gotcust
          )
 ) {