2 my $title = 'Payment Search Results';
3 my( $count_query, $sql_query );
4 if ( $cgi->param('magic') && $cgi->param('magic') eq '_date' ) {
9 if ( $cgi->param('agentnum') && $cgi->param('agentnum') =~ /^(\d+)$/ ) {
10 push @search, "agentnum = $1"; # $search{'agentnum'} = $1;
11 my $agent = qsearchs('agent', { 'agentnum' => $1 } );
12 die "unknown agentnum $1" unless $agent;
13 $title = $agent->agent. " $title";
16 if ( $cgi->param('payby') ) {
17 $cgi->param('payby') =~ /^(CARD|CHEK|BILL)(-(VisaMC|Amex|Discover))?$/
18 or die "illegal payby ". $cgi->param('payby');
19 $search{'payby'} = $1;
21 if ( $3 eq 'VisaMC' ) {
22 #avoid posix regexes for portability
23 push @search, " ( substring(payinfo from 1 for 1) = '4' ".
24 " OR substring(payinfo from 1 for 2) = '51' ".
25 " OR substring(payinfo from 1 for 2) = '52' ".
26 " OR substring(payinfo from 1 for 2) = '53' ".
27 " OR substring(payinfo from 1 for 2) = '54' ".
28 " OR substring(payinfo from 1 for 2) = '54' ".
29 " OR substring(payinfo from 1 for 2) = '55' ".
31 } elsif ( $3 eq 'Amex' ) {
32 push @search, " ( substring(payinfo from 1 for 2 ) = '34' ".
33 " OR substring(payinfo from 1 for 2 ) = '37' ".
35 } elsif ( $3 eq 'Discover' ) {
36 push @search, " substring(payinfo from 1 for 4 ) = '6011' ";
38 die "unknown card type $3";
43 #false laziness with cust_pkg.cgi
44 if ( $cgi->param('beginning')
45 && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) {
46 my $beginning = str2time($1);
47 push @search, "_date >= $beginning ";
49 if ( $cgi->param('ending')
50 && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) {
51 my $ending = str2time($1) + 86399;
52 push @search, " _date <= $ending ";
54 if ( $cgi->param('begin')
55 && $cgi->param('begin') =~ /^(\d+)$/ ) {
56 push @search, "_date >= $1 ";
58 if ( $cgi->param('end')
59 && $cgi->param('end') =~ /^(\d+)$/ ) {
60 push @search, " _date < $1 ";
65 $search = ( scalar(keys %search) ? ' AND ' : ' WHERE ' ).
66 join(' AND ', @search);
69 my $hsearch = join(' AND ', map { "$_ = '$search{$_}'" } keys %search );
70 $count_query = "SELECT COUNT(*), SUM(paid) ".
71 "FROM cust_pay LEFT JOIN cust_main USING ( custnum )".
72 ( $hsearch ? " WHERE $hsearch " : '' ).
75 warn join('-', keys %search);
77 'table' => 'cust_pay',
78 'hashref' => \%search,
79 'extra_sql' => "$search ORDER BY _date",
80 'addl_from' => 'LEFT JOIN cust_main USING ( custnum )',
85 $cgi->param('payinfo') =~ /^\s*(\d+)\s*$/ or die "illegal payinfo";
88 $cgi->param('payby') =~ /^(\w+)$/ or die "illegal payby";
91 $count_query = "SELECT COUNT(*), SUM(paid) FROM cust_pay ".
92 "WHERE payinfo = '$payinfo' AND payby = '$payby'";
95 'table' => 'cust_pay',
96 'hashref' => { 'payinfo' => $payinfo,
98 'extra_sql' => "ORDER BY _date",
103 my $link = [ "${p}view/cust_main.cgi?", 'custnum' ];
106 <%= include( 'elements/search.html',
108 'name' => 'payments',
109 'query' => $sql_query,
110 'count_query' => $count_query,
111 'count_addl' => [ '$%.2f total paid', ],
113 [ qw(Payment Amount Date), 'Cust #', 'Contact name',
117 my $cust_pay = shift;
118 if ( $cust_pay->payby eq 'CARD' ) {
119 'Card #'. $cust_pay->payinfo_masked;
120 } elsif ( $cust_pay->payby eq 'CHEK' ) {
121 'E-check acct#'. $cust_pay->payinfo;
122 } elsif ( $cust_pay->payby eq 'BILL' ) {
123 'Check #'. $cust_pay->payinfo;
125 $cust_pay->payby. ' '. $cust_pay->payinfo;
128 sub { sprintf('$%.2f', shift->paid ) },
129 sub { time2str('%b %d %Y', shift->_date ) },
131 sub { $_[0]->get('last'). ', '. $_[0]->first; },