httemplate/search/cust_pay_batch.cgi

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