httemplate/search/cust_pay_batch.cgi

   1 %
   2 %
   3 %my ($count_query, $sql_query, $batchnum);
   4 %my $hashref = {};
   5 %my @search = ();
   6 %my $orderby = 'paybatchnum';
   7 %
   8 %if ( $cgi->param('batchnum') && $cgi->param('batchnum') =~ /^(\d+)$/ ) {
   9 %  push @search, "batchnum = $1";
  10 %  my $pay_batch = qsearchs('pay_batch', { 'batchnum' => $1 } );
  11 %  die "Batch $1 not found!" unless $pay_batch;
  12 %  $batchnum = $pay_batch->batchnum;
  13 %}
  14 %
  15 %if ( $cgi->param('payby')  ) {
  16 %  $cgi->param('payby') =~ /^(CARD|CHEK)$/
  17 %    or die "illegal payby " . $cgi->param('payby');
  18 %
  19 %  push @search, "cust_pay_batch.payby = '$1'";
  20 %}
  21 %
  22 %if ( not $cgi->param('dcln') ) {
  23 %  push @search, "cpb.status IS DISTINCT FROM 'Approved'";
  24 %}
  25 %
  26 %my ($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
  27 %unless ($batchnum){
  28 %  push @search, "pay_batch.upload >= $beginning" if ($beginning);
  29 %  push @search, "pay_batch.upload <= $ending" if ($ending < 4294967295);#2^32-1
  30 %  $orderby = "pay_batch.download,paybatchnum";
  31 %}
  32 %
  33 %push @search, $FS::CurrentUser::CurrentUser->agentnums_sql;
  34 %my $search = ' WHERE ' . join(' AND ', @search);
  35 %
  36 %$count_query = 'SELECT COUNT(*) FROM cust_pay_batch AS cpb ' .
  37 %                  'LEFT JOIN cust_main USING ( custnum ) ' .
  38 %                  'LEFT JOIN pay_batch USING ( batchnum )' .
  39 %                 $search;
  40 %
  41 %#grr
  42 %$sql_query = "SELECT paybatchnum,invnum,custnum,cpb.last,cpb.first," .
  43 %             "cpb.payname,cpb.payinfo,cpb.exp,amount,cpb.status " .
  44 %            "FROM cust_pay_batch AS cpb " .
  45 %             'LEFT JOIN cust_main USING ( custnum ) ' .
  46 %             'LEFT JOIN pay_batch USING ( batchnum ) ' .
  47 %             "$search ORDER BY $orderby";
  48 %
  49 %my $html_init = <<EOF;
  50 %<FORM ACTION="$p/misc/download-batch.cgi" METHOD="POST">
  51 %Download batch in format <SELECT NAME="format">
  52 %<OPTION VALUE="">Default batch mode</OPTION>
  53 %<OPTION VALUE="csv-td_canada_trust-merchant_pc_batch">CSV file for TD Canada Trust Merchant PC Batch</OPTION>
  54 %<OPTION VALUE="PAP">80 byte file for TD Canada Trust PAP Batch</OPTION>
  55 %<OPTION VALUE="BoM">Bank of Montreal ECA batch</OPTION>
  56 %</SELECT><INPUT TYPE="hidden" NAME="batchnum" VALUE="$batchnum"><INPUT TYPE="submit" VALUE="Download"></FORM>
  57 %<BR><BR>
  58 %
  59 %<FORM ACTION="$p/misc/upload-batch.cgi" METHOD="POST" ENCTYPE="multipart/form-data">
  60 %Upload results<BR>
  61 %Filename <INPUT TYPE="file" NAME="batch_results"><BR>
  62 %Format <SELECT NAME="format">
  63 %<OPTION VALUE="">Default batch mode</OPTION>
  64 %<OPTION VALUE="csv-td_canada_trust-merchant_pc_batch">CSV results from TD Canada Trust Merchant PC Batch</OPTION>
  65 %<OPTION VALUE="PAP">264 byte results for TD Canada Trust PAP Batch</OPTION>
  66 %<OPTION VALUE="BoM">Bank of Montreal ECA results</OPTION>
  67 %</SELECT><BR>
  68 %<INPUT TYPE="submit" VALUE="Upload"></FORM>
  69 %<BR>
  70 %EOF
  71 %
  72 %if ($batchnum) {
  73 %  my $sth = dbh->prepare($count_query) or die dbh->errstr. "doing $count_query";
  74 %  $sth->execute or die "Error executing \"$count_query\": ". $sth->errstr;
  75 %  my $cards = $sth->fetchrow_arrayref->[0];
  76 %
  77 %  my $st = "SELECT SUM(amount) from cust_pay_batch WHERE batchnum=". $batchnum;
  78 %  $sth = dbh->prepare($st) or die dbh->errstr. "doing $st";
  79 %  $sth->execute or die "Error executing \"$st\": ". $sth->errstr;
  80 %  my $total = $sth->fetchrow_arrayref->[0];
  81 %
  82 %  $html_init .= "$cards credit card payments batched<BR>\$" .
  83 %                sprintf("%.2f", $total) ." total in batch<BR>";
  84 %}
  85 %
  86 %
  87 <% include('elements/search.html',
  88               'title'       => 'Batch payment details',
  89               'name'        => 'batch details',
  90               'menubar'     => ['Main Menu'  => $p,],
  91               'query'       => $sql_query,
  92               'count_query' => $count_query,
  93               'html_init'   => $batchnum ? $html_init : '',
  94               'header'      => [ '#',
  95                                  'Inv #',
  96                                  'Customer',
  97                                  'Customer',
  98                                  'Card Name',
  99                                  'Card',
 100                                  'Exp',
 101                                  'Amount',
 102                                  'Status',
 103                                ],
 104               'fields'      => [ sub {
 105                                    shift->[0];
 106                                  },
 107                                  sub {
 108                                    shift->[1];
 109                                  },
 110                                  sub {
 111                                    shift->[2];
 112                                  },
 113                                  sub {
 114                                    my $cpb = shift;
 115                                    $cpb->[3] . ', ' . $cpb->[4];
 116                                  },
 117                                  sub {
 118                                    shift->[5];
 119                                  },
 120                                  sub {
 121                                    my $cardnum = shift->[6];
 122                                    'x'x(length($cardnum)-4). substr($cardnum,(length($cardnum)-4));
 123                                  },
 124                                  sub {
 125                                    shift->[7] =~ /^\d{2}(\d{2})[\/\-](\d+)[\/\-]\d+$/;
 126                                    my( $mon, $year ) = ( $2, $1 );
 127                                    $mon = "0$mon" if $mon < 10;
 128                                    "$mon/$year";
 129                                  },
 130                                  sub {
 131                                    shift->[8];
 132                                  },
 133                                  sub {
 134                                    shift->[9];
 135                                  },
 136                                ],
 137               'align'       => 'lllllllrl',
 138               'links'       => [ ['', sub{'#';}],
 139                                  ["${p}view/cust_bill.cgi?", sub{shift->[1];},],
 140                                  ["${p}view/cust_main.cgi?", sub{shift->[2];},],
 141                                  ["${p}view/cust_main.cgi?", sub{shift->[2];},],
 142                                ],
 143       )
 144 %>
 145