],
'show_combined' => 1,
&>
+<%shared>
+# canonicalize the payby subtype string to an SQL-quoted list
+my %cardtype_of = (
+ 'VisaMC' => q['VISA card', 'MasterCard'],
+ 'Amex' => q['American Express card'],
+ 'Discover' => q['Discover card'],
+ 'Maestro' => q['Switch', 'Solo', 'Laser'],
+ 'Tokenized' => q['Tokenized'],
+);
+</%shared>
<%init>
my %opt = @_;
my $money = ($conf->config('money_char') || '$') . '%.2f';
die "access denied"
- unless $curuser->access_right('Financial reports');
+ unless $curuser->access_right('Basic payment and refund reports');
my $table = $opt{'table'} || 'cust_'.$opt{'thing'};
$title = 'Unapplied ' if $unapplied;
$title .= "\u$name_singular Search Results";
-my $link = '';
-if ( ( $curuser->access_right('View invoices') #remove in 2.5 (2.7?)
- || ($curuser->access_right('View payments') && $table =~ /^cust_pay/)
- || ($curuser->access_right('View refunds') && $table eq 'cust_refund')
- )
- && ! $opt{'disable_link'}
- )
-{
-
- my $key;
- my $q = '';
- if ( $table eq 'cust_pay_void' ) {
- $key = 'paynum';
- $q .= 'void=1;';
- } elsif ( $table eq /^cust_(\w+)$/ ) {
- $key = $1.'num';
- }
-
- if ( $key ) {
- $q .= "$key=";
- $link = [ "${p}view/$table.html?$q", $key ]
- }
-}
+###NOT USED???
+#my $link = '';
+#if ( ( $curuser->access_right('View invoices') #remove in 2.5 (2.7?)
+# || ($curuser->access_right('View payments') && $table =~ /^cust_pay/)
+# || ($curuser->access_right('View refunds') && $table eq 'cust_refund')
+# )
+# && ! $opt{'disable_link'}
+# )
+#{
+#
+# my $key;
+# my $q = '';
+# if ( $table eq 'cust_pay_void' ) {
+# $key = 'paynum';
+# $q .= 'void=1;';
+# } elsif ( $table eq /^cust_(\w+)$/ ) {
+# $key = $1.'num';
+# }
+#
+# if ( $key ) {
+# $q .= "$key=";
+# $link = [ "${p}view/$table.html?$q", $key ]
+# }
+#}
my $cust_link = sub {
my $cust_thing = shift;
push @sort_fields, @{ $opt{'pre_fields'} };
}
-my $sub_receipt = sub {
+my $sub_receipt = $opt{'disable_link'} ? '' : sub {
my $obj = shift;
my $objnum = $obj->primary_key . '=' . $obj->get($obj->primary_key);
+ my $table = $obj->table;
+ my $void = '';
+ if ($table eq 'cust_pay_void') {
+ $table = 'cust_pay';
+ $void = ';void=1';
+ }
include('/elements/popup_link_onclick.html',
- 'action' => $p.'view/cust_pay.html?link=popup;'.$objnum,
+ 'action' => $p.'view/'.$table.'.html?link=popup;'.$objnum.$void,
'actionlabel' => emt('Payment Receipt'),
);
};
push @link_onclicks, $sub_receipt, '';
push @sort_fields, 'paysort', $amount_field;
+# 4.x, to remain functional while the upgrade is running...
+my $sub_guess_cardtype = sub {
+ my $row = shift;
+ $row->paycardtype || (
+ ($row->payby eq 'CARD' && $row->paymask !~ /N\/A/)
+ ? cardtype($row->paymask)
+ : ''
+ )
+};
+
if ($opt{'show_card_type'}) {
push @header, emt('Card Type');
$align .= 'r';
push @links, '';
- push @fields, sub {
- (($_[0]->payby eq 'CARD') && ($_[0]->paymask !~ /N\/A/)) ? cardtype($_[0]->paymask) : ''
- };
- push @sort_fields, '';
+ push @fields, $sub_guess_cardtype;
+ # worst case, paycardtype isn't filled in yet and sorting by that column
+ # does nothing.
+ push @sort_fields, 'paycardtype';
}
if ( $unapplied ) {
push @fields, sub { time2str('%b %d %Y', shift->_date ) };
push @sort_fields, '_date';
-if ($opt{'show_order_number'}) {
+if ($cgi->param('show_order_number')) {
push @header, emt('Order Number');
$align .= 'r';
push @links, '';
if ( $cgi->param('payby') ) {
my @all_payby_search = ();
- foreach my $payby ( $cgi->param('payby') ) {
+ foreach my $payby_string ( $cgi->param('payby') ) {
- $payby =~
- /^(CARD|CHEK|BILL|CASH|PPAL|APPL|ANRD|PREP|WIRE|WEST|IDTP|EDI|MCRD|MCHK)(-(VisaMC|Amex|Discover|Maestro|Tokenized))?$/
- or die "illegal payby $payby";
+ my $payby_search;
- my $payby_search = "$table.payby = '$1'";
+ my ($payby, $subtype) = split('-', $payby_string);
+ # make sure it exists and is a transaction type
+ if ( FS::payby->payment_payby2longname($payby) ) {
+ $payby_search = "$table.payby = " . dbh->quote($payby);
+ } else {
+ die "illegal payby $payby_string";
+ }
- if ( $3 ) {
+ if ( $subtype ) {
- my $cardtype = $3;
+ my $in_cardtype = $cardtype_of{$subtype}
+ or die "unknown card type $subtype";
+ # will complete this phrase after alt_search
+ $payby_search .= " AND ( $table.paycardtype IN($in_cardtype)";
+ # 4.x transitional, to avoid breaking things while we upgrade
my $similar_to = dbh->{Driver}->{Name} =~ /^mysql/i
? 'REGEXP' #doesn't behave exactly the same, but
#should work for our patterns
: 'SIMILAR TO';
- my $search;
- if ( $cardtype eq 'VisaMC' ) {
+ my $alt_search;
+ if ( $subtype eq 'VisaMC' ) {
#avoid posix regexes for portability
- $search =
+ $alt_search =
# Visa
" ( ( substring($table.payinfo from 1 for 1) = '4' ".
# is not Switch
" OR substring($table.payinfo from 1 for 3) = '2[2-7]x' ".
" ) ";
- } elsif ( $cardtype eq 'Amex' ) {
+ } elsif ( $subtype eq 'Amex' ) {
- $search =
+ $alt_search =
" ( substring($table.payinfo from 1 for 2 ) = '34' ".
" OR substring($table.payinfo from 1 for 2 ) = '37' ".
" ) ";
- } elsif ( $cardtype eq 'Discover' ) {
+ } elsif ( $subtype eq 'Discover' ) {
my $country = $conf->config('countrydefault') || 'US';
- $search =
+ $alt_search =
" ( substring($table.payinfo from 1 for 4 ) = '6011' ".
" OR substring($table.payinfo from 1 for 3 ) = '60x' ".
" OR substring($table.payinfo from 1 for 2 ) = '65' ".
" ) ";
- } elsif ( $cardtype eq 'Maestro' ) {
+ } elsif ( $subtype eq 'Maestro' ) {
- $search =
+ $alt_search =
" ( substring($table.payinfo from 1 for 2 ) = '63' ".
" OR substring($table.payinfo from 1 for 2 ) = '67' ".
" OR substring($table.payinfo from 1 for 6 ) = '564182' ".
" $similar_to '49118[1-2]' ".
" ) ";
- } elsif ( $cardtype eq 'Tokenized' ) {
+ } elsif ( $subtype eq 'Tokenized' ) {
- $search = " substring($table.payinfo from 1 for 2 ) = '99' ";
+ $alt_search = " substring($table.payinfo from 1 for 2 ) = '99' ";
- } else {
- die "unknown card type $cardtype";
+ } else { # shouldn't happen if there's a $subtype
+
+ $alt_search = 'TRUE';
+
}
- my $masksearch = $search;
- $masksearch =~ s/$table\.payinfo/$table.paymask/gi;
+ # alt_search is already paren'd if it contains OR.
+ # now make sure it works if they're encrypted.
+ my $masksearch = $alt_search;
+ $masksearch =~ s/$table.payinfo/$table.paymask/g;
+ $alt_search = "( ($table.paymask IS NOT NULL AND $masksearch)
+ OR $alt_search )";
- $payby_search = "( $payby_search AND ( $search OR ( $table.paymask IS NOT NULL AND $masksearch ) ) )";
+ # close paren here
+ $payby_search .= " OR ( $table.paycardtype IS NULL AND $alt_search ) )";
- }
+ } # if $subtype
push @all_payby_search, $payby_search;
}
+ if ( $cgi->param('paymask') ) {
+ $cgi->param('paymask') =~ /^\s*(\d+)\s*$/
+ or die "illegal paymask ". $cgi->param('paymask');
+ my $regexp = regexp_sql();
+ push @search, "$table.paymask $regexp '$1\$'";
+ }
+
if ( $cgi->param('payinfo') ) {
$cgi->param('payinfo') =~ /^\s*(\d+)\s*$/
or die "illegal payinfo ". $cgi->param('payinfo');
}
}
+ #check for customer tags
+ my @tags;
+ 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;
+ }
+ }
+ if (@tags) {
+ my $tags = join(',',@tags);
+ push @search, "cust_tag.tagnum in ($tags)";
+ }
+
if ( $cgi->param('tax_names') ) {
if ( dbh->{Driver}->{Name} =~ /^Pg/i ) {
}
}
+ #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) ";
projects / freeside.git / blobdiff