From: Ivan Kohler <ivan@freeside.biz>
Date: Tue, 18 Jul 2017 03:21:07 +0000 (-0700)
Subject: search for payments by customer location, RT#76691
X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=d10a3e73a1c3e46f2c784d2aa4e7b3e621c336e2

search for payments by customer location, RT#76691
---

diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html
index 187fc8357..99f9ba0eb 100755
--- a/httemplate/search/elements/cust_pay_or_refund.html
+++ b/httemplate/search/elements/cust_pay_or_refund.html
@@ -464,7 +464,7 @@ if ( $cgi->param('magic') ) {
 
   #check for customer tags
   my @tags;
-  foreach my $p (keys $cgi->Vars) {
+  foreach my $p ($cgi->param) {
     if ($p =~ /^tagnum(\d+)/ && $1) {
       $addl_from .= " LEFT JOIN cust_tag ON (cust_tag.custnum = cust_pay.custnum)" unless @tags;
       push @tags, $1;
@@ -498,6 +498,113 @@ if ( $cgi->param('magic') ) {
     }
   }
 
+  #customer location... total false laziness w/cust_main/Search.pm
+
+  my $current = '';
+  unless ( $cgi->param('location_history') ) {
+    $current = '
+      AND (    cust_location.locationnum IN ( cust_main.bill_locationnum,
+                                              cust_main.ship_locationnum
+                                            )
+            OR cust_location.locationnum IN (
+                 SELECT locationnum FROM cust_pkg
+                  WHERE cust_pkg.custnum = cust_main.custnum
+                    AND locationnum IS NOT NULL
+                    AND '. FS::cust_pkg->ncancelled_recurring_sql.'
+               )
+          )';
+  }
+
+
+  ##
+  # address
+  ##
+  if ( $cgi->param('address') ) {
+    my @values = $cgi->param('address');
+    my @orwhere;
+    foreach (grep /\S/, @values) {
+      my $address = dbh->quote('%'. lc($_). '%');
+      push @orwhere,
+        "LOWER(cust_location.address1) LIKE $address",
+        "LOWER(cust_location.address2) LIKE $address";
+    }
+    if (@orwhere) {
+      push @search, "EXISTS(
+        SELECT 1 FROM cust_location 
+        WHERE cust_location.custnum = cust_main.custnum
+          AND (".join(' OR ',@orwhere).")
+          $current
+        )";
+    }
+  }
+
+  ##
+  # city
+  ##
+  if ( $cgi->param('city') =~ /\S/ ) {
+    my $city = dbh->quote($cgi->param('city'));
+    push @search, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.city = $city
+        $current
+    )";
+  }
+
+  ##
+  # county
+  ##
+  if ( $cgi->param('county') =~ /\S/ ) {
+    my $county = dbh->quote($cgi->param('county'));
+    push @search, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.county = $county
+        $current
+    )";
+  }
+
+  ##
+  # state
+  ##
+  if ( $cgi->param('state') =~ /\S/ ) {
+    my $state = dbh->quote($cgi->param('state'));
+    push @search, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.state = $state
+        $current
+    )";
+  }
+
+  ##
+  # zipcode
+  ##
+  if ( $cgi->param('zip') =~ /\S/ ) {
+    my $zip = dbh->quote($cgi->param('zip') . '%');
+    push @search, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.zip LIKE $zip
+        $current
+    )";
+  }
+
+  ##
+  # country
+  ##
+  if ( $cgi->param('country') =~ /^(\w\w)$/ ) {
+    my $country = uc($1);
+    push @search, "EXISTS(
+      SELECT 1 FROM cust_location
+      WHERE cust_location.custnum = cust_main.custnum
+        AND cust_location.country = '$country'
+        $current
+    )";
+  }
+
+  #end of false laziness w/cust_main/Search.pm
+
   my $search = ' WHERE '. join(' AND ', @search);
 
   $count_query = "SELECT COUNT(*), SUM($table.$amount_field) ";
diff --git a/httemplate/search/elements/options_cust_location.html b/httemplate/search/elements/options_cust_location.html
new file mode 100644
index 000000000..28ad34a7e
--- /dev/null
+++ b/httemplate/search/elements/options_cust_location.html
@@ -0,0 +1,58 @@
+
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('Address') |h %></TH>
+      <TD><INPUT TYPE="text" NAME="address" SIZE=54></TD>
+    </TR>
+
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('City') |h %></TH>
+      <TD>
+        <& /elements/city.html,
+             disable_empty => 0,
+             empty_label   => '(all)',
+             disable_text  => 1,
+        &>
+      </TD>
+    </TR>
+
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('County') |h %></TH>
+      <TD>
+        <& /elements/select-county.html,
+             disable_empty => 0,
+             empty_label   => '(all)',
+        &>
+      </TD>
+    </TR>
+    
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('State') |h %></TH>
+      <TD>
+        <& /elements/select-state.html,
+             disable_empty => 0,
+             empty_label   => '(all)',
+        &>
+      </TD>
+    </TR>
+    
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('Zip') |h %></TH>
+      <TD><INPUT TYPE="text" NAME="zip" SIZE=12></TD>
+    </TR>
+
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('Country') |h %></TH>
+      <TD>
+        <& /elements/select-country.html,
+             disable_empty       => 0,
+             state_disable_empty => 0,
+             state_empty_label   => '(all)',
+        &>
+      </TD>
+    </TR>
+
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('Search historical addresses') %></TH>
+      <TD><INPUT TYPE="checkbox" NAME="location_history" VALUE="1"></TD>
+
+
diff --git a/httemplate/search/elements/report_cust_pay_or_refund.html b/httemplate/search/elements/report_cust_pay_or_refund.html
index 728a12f23..bff470a69 100644
--- a/httemplate/search/elements/report_cust_pay_or_refund.html
+++ b/httemplate/search/elements/report_cust_pay_or_refund.html
@@ -134,6 +134,8 @@ Examples:
        'pre_options'  => [ 0 => emt('(none)') ],
   &>
 
+  <& options_cust_location.html &>
+
 </TABLE>
 
 % if ( $table eq 'cust_pay' ) { 
diff --git a/httemplate/search/report_cust_main.html b/httemplate/search/report_cust_main.html
index 69bbe8538..c458bb4af 100755
--- a/httemplate/search/report_cust_main.html
+++ b/httemplate/search/report_cust_main.html
@@ -144,63 +144,7 @@
 
   <FONT CLASS="fsinnerbox-title"><% emt('Location search options') %></FONT>
   <TABLE CLASS="fsinnerbox">
-
-    <TR>
-      <TH ALIGN="right" VALIGN="center"><% mt('Address') |h %></TH>
-      <TD><INPUT TYPE="text" NAME="address" SIZE=54></TD>
-    </TR>
-
-    <TR>
-      <TH ALIGN="right" VALIGN="center"><% mt('City') |h %></TH>
-      <TD>
-        <& /elements/city.html,
-             disable_empty => 0,
-             empty_label   => '(all)',
-             disable_text  => 1,
-        &>
-      </TD>
-    </TR>
-
-    <TR>
-      <TH ALIGN="right" VALIGN="center"><% mt('County') |h %></TH>
-      <TD>
-        <& /elements/select-county.html,
-             disable_empty => 0,
-             empty_label   => '(all)',
-        &>
-      </TD>
-    </TR>
-    
-    <TR>
-      <TH ALIGN="right" VALIGN="center"><% mt('State') |h %></TH>
-      <TD>
-        <& /elements/select-state.html,
-             disable_empty => 0,
-             empty_label   => '(all)',
-        &>
-      </TD>
-    </TR>
-    
-    <TR>
-      <TH ALIGN="right" VALIGN="center"><% mt('Zip') |h %></TH>
-      <TD><INPUT TYPE="text" NAME="zip" SIZE=12></TD>
-    </TR>
-
-    <TR>
-      <TH ALIGN="right" VALIGN="center"><% mt('Country') |h %></TH>
-      <TD>
-        <& /elements/select-country.html,
-             disable_empty       => 0,
-             state_disable_empty => 0,
-             state_empty_label   => '(all)',
-        &>
-      </TD>
-    </TR>
-
-    <TR>
-      <TH ALIGN="right" VALIGN="center"><% mt('Search historical addresses') %></TH>
-      <TD><INPUT TYPE="checkbox" NAME="location_history" VALUE="1"></TD>
-
+    <& elements/options_cust_location.html &>
   </TABLE>
   <BR>