From a941bc2ebde67e4bb0be5d6516cf1b09c092e8a1 Mon Sep 17 00:00:00 2001
From: Ivan Kohler <ivan@freeside.biz>
Date: Mon, 17 Jul 2017 20:21:27 -0700
Subject: [PATCH] search for payments by customer location, RT#76691

---
 httemplate/search/elements/cust_pay_or_refund.html | 107 +++++++++++++++++++++
 .../search/elements/options_cust_location.html     |  58 +++++++++++
 .../search/elements/report_cust_pay_or_refund.html |   2 +
 3 files changed, 167 insertions(+)
 create mode 100644 httemplate/search/elements/options_cust_location.html

diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html
index 0eefc101a..82b786e57 100755
--- a/httemplate/search/elements/cust_pay_or_refund.html
+++ b/httemplate/search/elements/cust_pay_or_refund.html
@@ -612,6 +612,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 e05f1af73..fc0db63b4 100644
--- a/httemplate/search/elements/report_cust_pay_or_refund.html
+++ b/httemplate/search/elements/report_cust_pay_or_refund.html
@@ -144,6 +144,8 @@ Examples:
        'pre_options'  => [ 0 => emt('(none)') ],
   &>
 
+  <& options_cust_location.html &>
+
 </TABLE>
 
 % if ( $table eq 'cust_pay' ) { 
-- 
2.11.0