diff options
| -rwxr-xr-x | httemplate/search/elements/cust_pay_or_refund.html | 109 | ||||
| -rw-r--r-- | httemplate/search/elements/options_cust_location.html | 58 | ||||
| -rw-r--r-- | httemplate/search/elements/report_cust_pay_or_refund.html | 2 | ||||
| -rwxr-xr-x | httemplate/search/report_cust_main.html | 58 |
4 files changed, 169 insertions, 58 deletions
diff --git a/httemplate/search/elements/cust_pay_or_refund.html b/httemplate/search/elements/cust_pay_or_refund.html index 187fc83..99f9ba0 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 0000000..28ad34a --- /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 728a12f..bff470a 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 69bbe85..c458bb4 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> |