search by state (and county, country), RT#30312

5 files changed, 84 insertions, 5 deletions

diff --git a/FS/FS/Misc.pm b/FS/FS/Misc.pm
index 380f895..f597058 100644
--- a/FS/FS/Misc.pm
+++ b/FS/FS/Misc.pm
@@ -548,7 +548,7 @@ sub states_hash {
 
   #it could throw a fatal "Invalid country code" error (for example "AX")
   my $subcountry = eval { new Locale::SubCountry($country) }
-    or return ( '', '(n/a)' );
+    or return (); # ( '', '(n/a)' );
 
   #"i see your schwartz is as big as mine!"
   map  { ( $_->[0] => $_->[1] ) }
diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index 046e959..0c00477 100644
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -727,6 +727,30 @@ sub search {
   }
 
   ##
+  # county
+  ##
+  if ( $params->{'county'} =~ /\S/ ) {
+    my $county = dbh->quote($params->{'county'});
+    push @where, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.county = $county
+    )";
+  }
+
+  ##
+  # state
+  ##
+  if ( $params->{'state'} =~ /\S/ ) {
+    my $state = dbh->quote($params->{'state'});
+    push @where, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.state = $state
+    )";
+  }
+
+  ##
   # zipcode
   ##
   if ( $params->{'zip'} =~ /\S/ ) {
@@ -738,6 +762,18 @@ sub search {
     )";
   }
 
+  ##
+  # country
+  ##
+  if ( $params->{'country'} =~ /^(\w\w)$/ ) {
+    my $country = uc($1);
+    push @where, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.country = '$country'
+    )";
+  }
+
   ###
   # refnum
   ###
diff --git a/httemplate/elements/select-country.html b/httemplate/elements/select-country.html
index e5656dc..724afe3 100644
--- a/httemplate/elements/select-country.html
+++ b/httemplate/elements/select-country.html
@@ -2,7 +2,7 @@
 
 Example:
 
-  include( '/elements/select-country.html',
+  <& /elements/select-country.html,
     #recommended
     country    => $current_country,
 
@@ -14,9 +14,15 @@ Example:
     empty_label   => 'all', #label for empty option
     disable_stateupdate => 0, #bool - disabled update of the select-state.html
     style         => [ 'attribute:value', 'another:value' ],
-  );
+
+    state_disable_empty => 1, #defaults to 1, disable the state empty option
+    state_empty_label   => 'all', #label for state empty option
+  &>
 
 </%doc>
+% #maybe this makes more sense in select-state.html?
+% # (county update is in select-county... and we wouldn't have to pass "state_"
+% #  options)
 % unless ( $opt{'disable_stateupdate'} ) {
 
   <% include('/elements/xmlhttp.html',
@@ -45,10 +51,14 @@ Example:
   
         // add the new states
         var statesArray = eval('(' + states + ')' );
+%       unless ( $opt{'disable_empty'} ) {
+          statesArray.unshift('', '');
+%       }
+
         for ( var s = 0; s < statesArray.length; s=s+2 ) {
             var stateLabel = statesArray[s+1];
             if ( stateLabel == "" )
-                stateLabel = '(n/a)';
+                stateLabel = <% $opt{state_empty_label} || '(n/a)' |js_string %>;
             opt(what.form.<% $pre %>state, statesArray[s], stateLabel);
         }
   
diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html
index c0292aa..a307463 100755
--- a/httemplate/search/cust_main.html
+++ b/httemplate/search/cust_main.html
@@ -41,7 +41,8 @@ my %search_hash = ();
 
 #scalars
 my @scalars = qw (
-  agentnum salesnum status address zip paydate_year paydate_month invoice_terms
+  agentnum salesnum status address county state zip country
+  paydate_year paydate_month invoice_terms
   no_censustract with_geocode with_email POST no_POST
   custbatch usernum
   cancelled_pkgs
diff --git a/httemplate/search/report_cust_main.html b/httemplate/search/report_cust_main.html
index 4309fbb..16a8122 100755
--- a/httemplate/search/report_cust_main.html
+++ b/httemplate/search/report_cust_main.html
@@ -43,6 +43,27 @@
       <TD ALIGN="right" VALIGN="center"><% mt('Address') |h %></TD>
       <TD><INPUT TYPE="text" NAME="address" SIZE=54></TD>
     </TR>
+
+    <TR>
+      <TD ALIGN="right" VALIGN="center"><% mt('County') |h %></TD>
+      <TD>
+        <& /elements/select-county.html,
+             disable_empty => 0,
+             empty_label   => '(all)',
+        &>
+      </TD>
+    </TR>
+    
+
+    <TR>
+      <TD ALIGN="right" VALIGN="center"><% mt('State') |h %></TD>
+      <TD>
+        <& /elements/select-state.html,
+             disable_empty => 0,
+             empty_label   => '(all)',
+        &>
+      </TD>
+    </TR>
     
     <TR>
       <TD ALIGN="right" VALIGN="center"><% mt('Zip') |h %></TD>
@@ -50,6 +71,17 @@
     </TR>
 
     <TR>
+      <TD ALIGN="right" VALIGN="center"><% mt('Country') |h %></TD>
+      <TD>
+        <& /elements/select-country.html,
+             disable_empty       => 0,
+             state_disable_empty => 0,
+             state_empty_label   => '(all)',
+        &>
+      </TD>
+    </TR>
+
+    <TR>
         <TD ALIGN="right" VALIGN="center"><% mt('Signup date') |h %></TD>
         <TD>
         <TABLE>