5 include( 'elements/cust_pay_or_refund.html',
7 'amount_field' => 'paid',
8 'name_singular' => 'payment',
12 include( 'elements/cust_pay_or_refund.html',
14 'amount_field' => 'refund',
15 'name_singular' => 'refund',
16 'name_verb' => 'refunded',
20 <% include( 'search.html',
22 'name_singular' => $name_singular,
23 'query' => $sql_query,
24 'count_query' => $count_query,
25 'count_addl' => [ '$%.2f total '.$opt{name_verb}, ],
26 'header' => [ "\u$name_singular",
30 FS::UI::Web::cust_header(),
33 'payby_payinfo_pretty',
34 sub { sprintf('$%.2f', shift->$amount_field() ) },
35 sub { time2str('%b %d %Y', shift->_date ) },
36 sub { my $o = shift->otaker;
37 $o = 'auto billing' if $o eq 'fs_daily';
38 $o = 'customer self-service' if $o eq 'fs_selfservice';
41 \&FS::UI::Web::cust_fields,
44 'align' => 'rrrc'.FS::UI::Web::cust_aligns(),
50 ( map { $_ ne 'Cust. Status' ? $cust_link : '' }
51 FS::UI::Web::cust_header()
59 FS::UI::Web::cust_colors(),
66 FS::UI::Web::cust_styles(),
75 unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
77 my $thing = $opt{'thing'};
78 my $amount_field = $opt{'amount_field'};
79 my $name_singular = $opt{'name_singular'};
81 my $title = "\u$name_singular Search Results";
82 my( $count_query, $sql_query );
83 if ( $cgi->param('magic') ) {
87 if ( $cgi->param('magic') eq '_date' ) {
90 if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) {
91 push @search, "agentnum = $1"; # $search{'agentnum'} = $1;
92 my $agent = qsearchs('agent', { 'agentnum' => $1 } );
93 die "unknown agentnum $1" unless $agent;
94 $title = $agent->agent. " $title";
97 if ( $cgi->param('payby') ) {
98 $cgi->param('payby') =~
99 /^(CARD|CHEK|BILL|PREP|CASH|WEST|MCRD)(-(VisaMC|Amex|Discover|Maestro))?$/
100 or die "illegal payby ". $cgi->param('payby');
101 push @search, "cust_$thing.payby = '$1'";
107 if ( $cardtype eq 'VisaMC' ) {
108 #avoid posix regexes for portability
110 " ( ( substring(cust_$thing.payinfo from 1 for 1) = '4' ".
111 " AND substring(cust_$thing.payinfo from 1 for 4) != '4936' ".
112 " AND substring(cust_$thing.payinfo from 1 for 6) ".
113 " NOT SIMILAR TO '49030[2-9]' ".
114 " AND substring(cust_$thing.payinfo from 1 for 6) ".
115 " NOT SIMILAR TO '49033[5-9]' ".
116 " AND substring(cust_$thing.payinfo from 1 for 6) ".
117 " NOT SIMILAR TO '49110[1-2]' ".
118 " AND substring(cust_$thing.payinfo from 1 for 6) ".
119 " NOT SIMILAR TO '49117[4-9]' ".
120 " AND substring(cust_$thing.payinfo from 1 for 6) ".
121 " NOT SIMILAR TO '49118[1-2]' ".
123 " OR substring(cust_$thing.payinfo from 1 for 2) = '51' ".
124 " OR substring(cust_$thing.payinfo from 1 for 2) = '52' ".
125 " OR substring(cust_$thing.payinfo from 1 for 2) = '53' ".
126 " OR substring(cust_$thing.payinfo from 1 for 2) = '54' ".
127 " OR substring(cust_$thing.payinfo from 1 for 2) = '54' ".
128 " OR substring(cust_$thing.payinfo from 1 for 2) = '55' ".
129 " OR substring(cust_$thing.payinfo from 1 for 2) = '36' ". #Diner's int'l processed as Visa/MC inside US
131 } elsif ( $cardtype eq 'Amex' ) {
133 " ( substring(cust_$thing.payinfo from 1 for 2 ) = '34' ".
134 " OR substring(cust_$thing.payinfo from 1 for 2 ) = '37' ".
136 } elsif ( $cardtype eq 'Discover' ) {
138 " ( substring(cust_$thing.payinfo from 1 for 4 ) = '6011' ".
139 " OR substring(cust_$thing.payinfo from 1 for 2 ) = '65' ".
140 " OR substring(cust_$thing.payinfo from 1 for 3 ) = '622' ". #China Union Pay processed as Discover outside CN
142 } elsif ( $cardtype eq 'Maestro' ) {
144 " ( substring(cust_$thing.payinfo from 1 for 2 ) = '63' ".
145 " OR substring(cust_$thing.payinfo from 1 for 2 ) = '67' ".
146 " OR substring(cust_$thing.payinfo from 1 for 6 ) = '564182' ".
147 " OR substring(cust_$thing.payinfo from 1 for 4 ) = '4936' ".
148 " OR substring(cust_$thing.payinfo from 1 for 6 ) ".
149 " SIMILAR TO '49030[2-9]' ".
150 " OR substring(cust_$thing.payinfo from 1 for 6 ) ".
151 " SIMILAR TO '49033[5-9]' ".
152 " OR substring(cust_$thing.payinfo from 1 for 6 ) ".
153 " SIMILAR TO '49110[1-2]' ".
154 " OR substring(cust_$thing.payinfo from 1 for 6 ) ".
155 " SIMILAR TO '49117[4-9]' ".
156 " OR substring(cust_$thing.payinfo from 1 for 6 ) ".
157 " SIMILAR TO '49118[1-2]' ".
160 die "unknown card type $cardtype";
163 my $masksearch = $search;
164 $masksearch =~ s/cust_$thing\.payinfo/cust_$thing.paymask/gi;
167 "( $search OR ( cust_$thing.paymask IS NOT NULL AND $masksearch ) )";
172 if ( $cgi->param('payinfo') ) {
173 $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/
174 or die "illegal payinfo ". $cgi->param('payinfo');
175 push @search, "cust_$thing.payinfo = '$1'";
178 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
179 push @search, "_date >= $beginning ",
182 push @search, FS::UI::Web::parse_lt_gt($cgi, $amount_field );
186 } elsif ( $cgi->param('magic') eq 'paybatch' ) {
188 $cgi->param('paybatch') =~ /^([\w\/\:\-\.]+)$/
189 or die "illegal paybatch: ". $cgi->param('paybatch');
191 push @search, "paybatch = '$1'";
193 $orderby = "LOWER(company || ' ' || last || ' ' || first )";
196 die "unknown search magic: ". $cgi->param('magic');
199 #here is the agent virtualization
200 push @search, $FS::CurrentUser::CurrentUser->agentnums_sql;
202 my $search = ' WHERE '. join(' AND ', @search);
204 $count_query = "SELECT COUNT(*), SUM($amount_field) ".
205 "FROM cust_$thing LEFT JOIN cust_main USING ( custnum )".
209 'table' => "cust_$thing",
210 'select' => join(', ',
212 'cust_main.custnum as cust_main_custnum',
213 FS::UI::Web::cust_sql_fields(),
216 'extra_sql' => "$search ORDER BY $orderby",
217 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )',
222 #hmm... is this still used?
224 $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ or die "illegal payinfo";
227 $cgi->param('payby') =~ /^(\w+)$/ or die "illegal payby";
230 $count_query = "SELECT COUNT(*), SUM($amount_field) FROM cust_$thing".
231 " WHERE payinfo = '$payinfo' AND payby = '$payby'".
232 " AND ". $FS::CurrentUser::CurrentUser->agentnums_sql;
235 'table' => "cust_$thing",
236 'hashref' => { 'payinfo' => $payinfo,
238 'extra_sql' => $FS::CurrentUser::CurrentUser->agentnums_sql.
245 if ( $FS::CurrentUser::CurrentUser->access_right('View invoices') #XXX for now
246 || $FS::CurrentUser::CurrentUser->access_right('View customer payments') ){
247 $link = [ "${p}view/cust_$thing.html?${thing}num=", $thing.'num' ]
250 my $cust_link = sub {
251 my $cust_thing = shift;
252 $cust_thing->cust_main_custnum
253 ? [ "${p}view/cust_main.cgi?", 'custnum' ]