From 7517eaa583bcb213b62dfd0c11288aa50a350e8a Mon Sep 17 00:00:00 2001
From: ivan <ivan>
Date: Fri, 25 Aug 2006 19:30:52 +0000
Subject: [PATCH] use GROUP BY instead of DISTINCT ON in the state and country
 queries for better cross-database compatibility, based on a preliminary patch
 from Jason Thomas

---
 FS/FS/Misc.pm                                 | 10 +++++---
 FS/FS/Record.pm                               |  2 +-
 httemplate/edit/cust_main/select-country.html | 36 ++++++++++++++------------
 httemplate/edit/cust_main/select-county.html  | 37 ++++++++++++---------------
 httemplate/edit/cust_main/select-state.html   | 23 ++++++++---------
 5 files changed, 54 insertions(+), 54 deletions(-)

diff --git a/FS/FS/Misc.pm b/FS/FS/Misc.pm
index 101a2d4e0..06fa51f54 100644
--- a/FS/FS/Misc.pm
+++ b/FS/FS/Misc.pm
@@ -359,10 +359,12 @@ sub states_hash {
 #     sort
      map { s/[\n\r]//g; $_; }
      map { $_->state; }
-     qsearch( 'cust_main_county',
-              { 'country' => $country },
-              'DISTINCT ON ( state ) *',
-            )
+     qsearch({ 
+               'select'    => 'state',
+               'table'     => 'cust_main_county',
+               'hashref'   => { 'country' => $country },
+               'extra_sql' => 'GROUP BY state',
+            })
   ;
 
   #it could throw a fatal "Invalid country code" error (for example "AX")
diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm
index a551bb8cc..cc7f551e9 100644
--- a/FS/FS/Record.pm
+++ b/FS/FS/Record.pm
@@ -388,7 +388,7 @@ sub qsearch {
   my %result;
   tie %result, "Tie::IxHash";
   my @stuff = @{ $sth->fetchall_arrayref( {} ) };
-  if($pkey) {
+  if ( $pkey && $stuff[0]->{$pkey} ) {
     %result = map { $_->{$pkey}, $_ } @stuff;
   } else {
     @result{@stuff} = @stuff;
diff --git a/httemplate/edit/cust_main/select-country.html b/httemplate/edit/cust_main/select-country.html
index 5467f26e9..137f61975 100644
--- a/httemplate/edit/cust_main/select-country.html
+++ b/httemplate/edit/cust_main/select-country.html
@@ -1,15 +1,3 @@
-%
-%
-%  my %opt = @_;
-%  foreach my $opt (qw( county state country prefix onchange disabled )) {
-%    $opt{$_} = '' unless exists($opt{$_}) && defined($opt{$_});
-%  }
-%
-%  my $conf = new FS::Conf;
-%  my $countrydefault = $conf->config('countrydefault') || 'US';
-%
-%
-
 
 <% include('/elements/xmlhttp.html',
               'url'  => $p.'misc/states.cgi',
@@ -57,18 +45,32 @@
 </SCRIPT>
 
 <SELECT NAME="<% $opt{'prefix'} %>country" onChange="<% $opt{'prefix'} %>country_changed(this); <% $opt{'onchange'} %>" <% $opt{'disabled'} %>>
+
 % foreach my $country (
 %     sort {    ($b eq $countrydefault) <=> ($a eq $countrydefault)
 %            or code2country($a) cmp code2country($b) }
 %     map { $_->country }
-%     qsearch( 'cust_main_county',{}, 'DISTINCT ON ( country ) *', )
-%   ) {
-%
-
+%     qsearch({
+%               'select'    => 'country',
+%               'table'     => 'cust_main_county',
+%               'hashref'   => {},
+%               'extra_sql' => 'GROUP BY country',
+%            })
+% ) {
 
   <OPTION VALUE="<% $country %>"<% $country eq $opt{'country'} ? ' SELECTED' : '' %>><% code2country($country). " ($country)" %>
-% } 
 
+% } 
 
 </SELECT>
 
+<%init>
+my %opt = @_;
+foreach my $opt (qw( county state country prefix onchange disabled )) {
+  $opt{$_} = '' unless exists($opt{$_}) && defined($opt{$_});
+}
+
+my $conf = new FS::Conf;
+my $countrydefault = $conf->config('countrydefault') || 'US';
+</%init>
+
diff --git a/httemplate/edit/cust_main/select-county.html b/httemplate/edit/cust_main/select-county.html
index 70939fca5..c9b64bf4f 100644
--- a/httemplate/edit/cust_main/select-county.html
+++ b/httemplate/edit/cust_main/select-county.html
@@ -1,25 +1,10 @@
-%
-%
-%  my %opt = @_;
-%  foreach my $opt (qw( county state country prefix onchange disabled )) {
-%    $opt{$_} = '' unless exists($opt{$_}) && defined($opt{$_});
-%  }
-%
-%  my $sql = "SELECT COUNT(*) FROM cust_main_county".
-%            " WHERE county IS NOT NULL AND county != ''";
-%  my $sth = dbh->prepare($sql) or die dbh->errstr;
-%  $sth->execute or die $sth->errstr;
-%  my $countyflag = $sth->fetchrow_arrayref->[0];
-%
-%
 % if ( $countyflag ) { 
 
-
   <% include('/elements/xmlhttp.html',
                 'url'  => $p.'misc/counties.cgi',
                 'subs' => [ $opt{'prefix'}. 'get_counties' ],
              )
-%>
+  %>
   
   <SCRIPT TYPE="text/javascript">
   
@@ -69,15 +54,14 @@
 %                                     'country' => $opt{'country'},
 %                                   }
 %              )
-%     ) {
-%  
-
+% ) {
 
     <OPTION VALUE="<% $county %>"<% $county eq $opt{'county'} ? ' SELECTED' : '' %>><% $county %>
-% } 
 
+% } 
 
   </SELECT>
+
 % } else { 
 
 
@@ -87,5 +71,18 @@
   </SCRIPT>
 
   <INPUT TYPE="hidden" NAME="<% $opt{'prefix'} %>county" VALUE="<% $opt{'county'} %>">
+
 % } 
 
+<%init>
+my %opt = @_;
+foreach my $opt (qw( county state country prefix onchange disabled )) {
+  $opt{$_} = '' unless exists($opt{$_}) && defined($opt{$_});
+}
+
+my $sql = "SELECT COUNT(*) FROM cust_main_county".
+          " WHERE county IS NOT NULL AND county != ''";
+my $sth = dbh->prepare($sql) or die dbh->errstr;
+$sth->execute or die $sth->errstr;
+my $countyflag = $sth->fetchrow_arrayref->[0];
+</%init>
diff --git a/httemplate/edit/cust_main/select-state.html b/httemplate/edit/cust_main/select-state.html
index a7e56e78f..87546e5e3 100644
--- a/httemplate/edit/cust_main/select-state.html
+++ b/httemplate/edit/cust_main/select-state.html
@@ -1,21 +1,20 @@
-%
-%
-%  my %opt = @_;
-%  foreach my $opt (qw( county state country prefix onchange disabled )) {
-%    $opt{$_} = '' unless exists($opt{$_}) && defined($opt{$_});
-%  }
-%
-%
-
-
 <SELECT NAME="<% $opt{'prefix'} %>state" onChange="<% $opt{'prefix'} %>state_changed(this); <% $opt{'onchange'} %>" <% $opt{'disabled'} %>>
-% tie my %states, 'Tie::IxHash', states_hash( $opt{'country'} ); 
-% foreach my $state ( keys %states ) { 
 
+% foreach my $state ( keys %states ) { 
 
   <OPTION VALUE="<% $state %>"<% $state eq $opt{'state'} ? ' SELECTED' : '' %>><% $states{$state} || '(n/a)' %>
+
 % } 
 
 
 </SELECT>
 
+<%init>
+my %opt = @_;
+foreach my $opt (qw( county state country prefix onchange disabled )) {
+  $opt{$_} = '' unless exists($opt{$_}) && defined($opt{$_});
+}
+
+tie my %states, 'Tie::IxHash', states_hash( $opt{'country'} ); 
+</%init>
+
-- 
2.11.0