'group_column' => 'payby',
'group_label' => 'payby_name',
+ 'amount_field' => $amount_field,
'subtotal' => { $opt{amount_field} => "sum($amount_field)" },
'subtotal_row' => [ 'Subtotal',
sub { sprintf($money, $_[0]->$amount_field) },
],
'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 @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, 'paycardtype';
+ 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') ) {
-
- $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 = "$table.payby = '$1'";
-
- if ( $3 ) {
-
- my $cardtype = $3;
-
- 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' ) {
-
- #avoid posix regexes for portability
- $search =
- " ( ( substring($table.payinfo from 1 for 1) = '4' ".
- " AND substring($table.payinfo from 1 for 4) != '4936' ".
- " AND substring($table.payinfo from 1 for 6) ".
- " NOT $similar_to '49030[2-9]' ".
- " AND substring($table.payinfo from 1 for 6) ".
- " NOT $similar_to '49033[5-9]' ".
- " AND substring($table.payinfo from 1 for 6) ".
- " NOT $similar_to '49110[1-2]' ".
- " AND substring($table.payinfo from 1 for 6) ".
- " NOT $similar_to '49117[4-9]' ".
- " AND substring($table.payinfo from 1 for 6) ".
- " NOT $similar_to '49118[1-2]' ".
- " )".
- " OR substring($table.payinfo from 1 for 2) = '51' ".
- " OR substring($table.payinfo from 1 for 2) = '52' ".
- " OR substring($table.payinfo from 1 for 2) = '53' ".
- " OR substring($table.payinfo from 1 for 2) = '54' ".
- " OR substring($table.payinfo from 1 for 2) = '54' ".
- " OR substring($table.payinfo from 1 for 2) = '55' ".
-# " OR substring($table.payinfo from 1 for 2) = '36' ". #Diner's int'l was processed as Visa/MC inside US, now Discover
- " ) ";
-
- } elsif ( $cardtype eq 'Amex' ) {
-
- $search =
- " ( substring($table.payinfo from 1 for 2 ) = '34' ".
- " OR substring($table.payinfo from 1 for 2 ) = '37' ".
- " ) ";
-
- } elsif ( $cardtype eq 'Discover' ) {
-
- my $country = $conf->config('countrydefault') || 'US';
-
- $search =
- " ( substring($table.payinfo from 1 for 4 ) = '6011' ".
- " OR substring($table.payinfo from 1 for 2 ) = '65' ".
- " OR substring($table.payinfo from 1 for 3 ) = '300' ".
- " OR substring($table.payinfo from 1 for 3 ) = '301' ".
- " OR substring($table.payinfo from 1 for 3 ) = '302' ".
- " OR substring($table.payinfo from 1 for 3 ) = '303' ".
- " OR substring($table.payinfo from 1 for 3 ) = '304' ".
- " OR substring($table.payinfo from 1 for 3 ) = '305' ".
- " OR substring($table.payinfo from 1 for 4 ) = '3095' ".
- " OR substring($table.payinfo from 1 for 2 ) = '36' ".
- " OR substring($table.payinfo from 1 for 2 ) = '38' ".
- " OR substring($table.payinfo from 1 for 2 ) = '39' ".
- " OR substring($table.payinfo from 1 for 3 ) = '644' ".
- " OR substring($table.payinfo from 1 for 3 ) = '645' ".
- " OR substring($table.payinfo from 1 for 3 ) = '646' ".
- " OR substring($table.payinfo from 1 for 3 ) = '647' ".
- " OR substring($table.payinfo from 1 for 3 ) = '648' ".
- " OR substring($table.payinfo from 1 for 3 ) = '649' ".
- ( $country =~ /^(US|CA)$/
- ?" OR substring($table.payinfo from 1 for 4 ) = '3528' ". # JCB cards in the 3528-3589 range identified as Discover inside US/CA
- " OR substring($table.payinfo from 1 for 4 ) = '3529' ".
- " OR substring($table.payinfo from 1 for 3 ) = '353' ".
- " OR substring($table.payinfo from 1 for 3 ) = '354' ".
- " OR substring($table.payinfo from 1 for 3 ) = '355' ".
- " OR substring($table.payinfo from 1 for 3 ) = '356' ".
- " OR substring($table.payinfo from 1 for 3 ) = '357' ".
- " OR substring($table.payinfo from 1 for 3 ) = '358' "
- :""
- ).
- " OR substring($table.payinfo from 1 for 3 ) = '622' ". #China Union Pay processed as Discover outside CN
- " ) ";
-
- } elsif ( $cardtype eq 'Maestro' ) {
-
- $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' ".
- " OR substring($table.payinfo from 1 for 4 ) = '4936' ".
- " OR substring($table.payinfo from 1 for 6 ) ".
- " $similar_to '49030[2-9]' ".
- " OR substring($table.payinfo from 1 for 6 ) ".
- " $similar_to '49033[5-9]' ".
- " OR substring($table.payinfo from 1 for 6 ) ".
- " $similar_to '49110[1-2]' ".
- " OR substring($table.payinfo from 1 for 6 ) ".
- " $similar_to '49117[4-9]' ".
- " OR substring($table.payinfo from 1 for 6 ) ".
- " $similar_to '49118[1-2]' ".
- " ) ";
-
- } elsif ( $cardtype eq 'Tokenized' ) {
-
- $search = " substring($table.payinfo from 1 for 2 ) = '99' ";
-
- } else {
- die "unknown card type $cardtype";
- }
-
- my $masksearch = $search;
- $masksearch =~ s/$table\.payinfo/$table.paymask/gi;
-
- $payby_search = "( $payby_search AND ( $search OR ( $table.paymask IS NOT NULL AND $masksearch ) ) )";
+ foreach my $payby_string ( $cgi->param('payby') ) {
+
+ my $payby_search;
+
+ 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 ( $subtype ) {
+
+ my $in_cardtype = $cardtype_of{$subtype}
+ or die "unknown card type $subtype";
+ $payby_search .= " AND $table.paycardtype IN($in_cardtype)";
}
}
+ 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