diff options
author | ivan <ivan> | 2006-08-25 19:30:52 +0000 |
---|---|---|
committer | ivan <ivan> | 2006-08-25 19:30:52 +0000 |
commit | 7517eaa583bcb213b62dfd0c11288aa50a350e8a (patch) | |
tree | a1a123dfb5a048348775e237f64ea25f6417bf2f | |
parent | b19266599870172bda0921df60020f0440854750 (diff) |
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
-rw-r--r-- | FS/FS/Misc.pm | 10 | ||||
-rw-r--r-- | FS/FS/Record.pm | 2 | ||||
-rw-r--r-- | httemplate/edit/cust_main/select-country.html | 36 | ||||
-rw-r--r-- | httemplate/edit/cust_main/select-county.html | 37 | ||||
-rw-r--r-- | 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> + |