httemplate/search/pay_batch.cgi

   1 %
   2 %
   3 %my %statusmap = ('I'=>'In Transit', 'O'=>'Open', 'R'=>'Resolved');
   4 %my $hashref = {};
   5 %my $count_query = 'SELECT COUNT(*) FROM pay_batch';
   6 %
   7 %my($begin, $end) = ( '', '' );
   8 %
   9 %my @where;
  10 %if ( $cgi->param('beginning')
  11 %     && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) {
  12 %  $begin = str2time($1);
  13 %  push @where, "download >= $begin";
  14 %}
  15 %if ( $cgi->param('ending')
  16 %      && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) {
  17 %  $end = str2time($1) + 86399;
  18 %  push @where, "download < $end";
  19 %}
  20 %
  21 %my @status;
  22 %if ( $cgi->param('open') ) {
  23 %  push @status, "O";
  24 %}
  25 %
  26 %if ( $cgi->param('intransit') ) {
  27 %  push @status, "I";
  28 %}
  29 %
  30 %if ( $cgi->param('resolved') ) {
  31 %  push @status, "R";
  32 %}
  33 %
  34 %push @where,
  35 %     scalar(@status) ? q!(status='! . join(q!' OR status='!, @status) . q!')!
  36 %                     : q!status='X'!;  # kludgy, X is unused at present
  37 %
  38 %my $extra_sql = scalar(@where) ? 'WHERE ' . join(' AND ', @where) : '';
  39 %
  40 %
  41 <% include( 'elements/search.html',
  42                  'title'        => 'Credit Card Batches',
  43                  'menubar'      => [ 'Main Menu' => $p, ],
  44                  'name'         => 'batches',
  45                  'query'        => { 'table'     => 'pay_batch',
  46                                      'hashref'   => $hashref,
  47                                      'extra_sql' => "$extra_sql ORDER BY batchnum DESC",
  48                                    },
  49                  'count_query'  => "$count_query $extra_sql",
  50                  'header'       => [ 'Batch',
  51                                      'First Download',
  52                                      'Last Upload',
  53                                      'Item Count',
  54                                      'Amount',
  55                                      'Status',
  56                                    ],
  57                  'align'        => 'lllrrl',
  58                  'fields'       => [ 'batchnum',
  59                                      sub {
  60                                        my $_date = shift->download;
  61                                        $_date ? time2str("%a %b %e %T %Y", $_date) : ''
  62                                      },
  63                                      sub {
  64                                        my $_date = shift->upload;
  65                                        $_date ? time2str("%a %b %e %T %Y", $_date) : ''
  66                                      },
  67                                      sub {
  68                                        my $st = "SELECT COUNT(*) from cust_pay_batch WHERE batchnum=" . shift->batchnum;
  69                                        my $sth = dbh->prepare($st)
  70                                          or die dbh->errstr. "doing $st";
  71                                        $sth->execute
  72                                          or die "Error executing \"$st\": ". $sth->errstr;
  73                                        $sth->fetchrow_arrayref->[0];
  74                                      },
  75                                      sub {
  76                                        my $st = "SELECT SUM(amount) from cust_pay_batch WHERE batchnum=" . shift->batchnum;
  77                                        my $sth = dbh->prepare($st)
  78                                          or die dbh->errstr. "doing $st";
  79                                        $sth->execute
  80                                          or die "Error executing \"$st\": ". $sth->errstr;
  81                                        $sth->fetchrow_arrayref->[0];
  82                                      },
  83                                      sub {
  84                                        $statusmap{shift->status};
  85                                      },
  86                                    ],
  87                  'links'        => [ [ "${p}search/cust_pay_batch.cgi?batchnum=", 'batchnum',],
  88                                    ],
  89       )
  90
  91 %>
  92
  93