first stab at BoM download

14 files changed, 298 insertions, 21 deletions

diff --git a/FS/FS.pm b/FS/FS.pm
index f41245e..d8999ca 100644
--- a/FS/FS.pm
+++ b/FS/FS.pm
@@ -142,7 +142,9 @@ L<FS::cust_credit_bill> - Credit application to invoice class
 
 L<FS::cust_pay_refund> - Refund application to payment class
 
-L<FS::cust_pay_batch> - Credit card transaction queue class
+L<FS::pay_batch> - Credit card transaction queue class
+
+L<FS::cust_pay_batch> - Credit card transaction member queue class
 
 L<FS::prepay_credit> - Prepaid "calling card" credit class.
 
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index e811856..17d541e 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -538,10 +538,21 @@ sub tables_hashref {
       'index' => [ [ 'paynum' ], [ 'invnum' ] ],
     },
 
+    'pay_batch' => { #batches of payments to an external processor
+      'columns' => [
+        'batchnum',   'serial',    '',   '', '', '', 
+        'status',  'char', 'NULL',     1, '', '', 
+      ],
+      'primary_key' => 'batchnum',
+      'unique' => [],
+      'index' => [],
+    },
+
     'cust_pay_batch' => { #what's this used for again?  list of customers
                           #in current CARD batch? (necessarily CARD?)
       'columns' => [
         'paybatchnum',   'serial',    '',   '', '', '', 
+        'batchnum',   'int',    '',   '', '', '', 
         'invnum',   'int',    '',   '', '', '', 
         'custnum',   'int',    '',   '', '', '', 
         'last',     'varchar', '',     $char_d, '', '', 
@@ -553,7 +564,7 @@ sub tables_hashref {
         'zip',      'varchar', 'NULL', 10, '', '', 
         'country',  'char', '',     2, '', '', 
         #        'trancode', 'int', '', '', '', ''
-        'cardnum',  'varchar', '',     16, '', '', 
+        'payinfo',  'varchar', '',     512, '', '', 
         #'exp',      @date_type, '', ''
         'exp',      'varchar', '',     11, '', '', 
         'payname',  'varchar', 'NULL', $char_d, '', '', 
@@ -561,7 +572,7 @@ sub tables_hashref {
       ],
       'primary_key' => 'paybatchnum',
       'unique' => [],
-      'index' => [ ['invnum'], ['custnum'] ],
+      'index' => [ ['batchnum'], ['invnum'], ['custnum'] ],
     },
 
     'cust_pkg' => {
diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm
index bcae4d6..c2a39af 100644
--- a/FS/FS/cust_bill.pm
+++ b/FS/FS/cust_bill.pm
@@ -13,7 +13,7 @@ use HTML::Entities;
 use Locale::Country;
 use FS::UID qw( datasrc );
 use FS::Misc qw( send_email send_fax );
-use FS::Record qw( qsearch qsearchs );
+use FS::Record qw( qsearch qsearchs dbh );
 use FS::cust_main_Mixin;
 use FS::cust_main;
 use FS::cust_bill_pkg;
@@ -1282,8 +1282,22 @@ L<FS::cust_pay_batch>).
 sub batch_card {
   my $self = shift;
   my $cust_main = $self->cust_main;
+  my $oldAutoCommit = $FS::UID::AutoCommit;
+  local $FS::UID::AutoCommit = 0;
+  my $dbh = dbh;
+
+  my $pay_batch = qsearchs('pay_batch'=> '');
+
+  unless ($pay_batch) {
+    $pay_batch = new FS::pay_batch;
+    my $error = $pay_batch->insert;
+    if ( $error ) {
+      die "error creating new batch: $error\n";
+    }
+  }
 
   my $cust_pay_batch = new FS::cust_pay_batch ( {
+    'batchnum' => $pay_batch->getfield('batchnum'),
     'invnum'   => $self->getfield('invnum'),
     'custnum'  => $cust_main->getfield('custnum'),
     'last'     => $cust_main->getfield('last'),
@@ -1294,7 +1308,7 @@ sub batch_card {
     'state'    => $cust_main->getfield('state'),
     'zip'      => $cust_main->getfield('zip'),
     'country'  => $cust_main->getfield('country'),
-    'cardnum'  => $cust_main->payinfo,
+    'payinfo'  => $cust_main->payinfo,
     'exp'      => $cust_main->getfield('paydate'),
     'payname'  => $cust_main->getfield('payname'),
     'amount'   => $self->owed,
@@ -1302,6 +1316,8 @@ sub batch_card {
   my $error = $cust_pay_batch->insert;
   die $error if $error;
 
+  $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+
   '';
 }
 
diff --git a/FS/FS/cust_pay_batch.pm b/FS/FS/cust_pay_batch.pm
index 25b796b..117d725 100644
--- a/FS/FS/cust_pay_batch.pm
+++ b/FS/FS/cust_pay_batch.pm
@@ -37,7 +37,7 @@ following fields are currently supported:
 
 =item paybatchnum - primary key (automatically assigned)
 
-=item cardnum
+=item payinfo
 
 =item exp - card expiration 
 
@@ -119,7 +119,7 @@ sub check {
   my $error = 
       $self->ut_numbern('paybatchnum')
     || $self->ut_numbern('trancode') #depriciated
-    || $self->ut_number('cardnum') 
+    || $self->ut_number('payinfo') 
     || $self->ut_money('amount')
     || $self->ut_number('invnum')
     || $self->ut_number('custnum')
@@ -137,14 +137,19 @@ sub check {
   $self->first =~ /^([\w \,\.\-\']+)$/ or return "Illegal first name";
   $self->first($1);
 
-  my $cardnum = $self->cardnum;
-  $cardnum =~ s/\D//g;
-  $cardnum =~ /^(\d{13,16})$/
-    or return "Illegal credit card number";
-  $cardnum = $1;
-  $self->cardnum($cardnum);
-  validate($cardnum) or return "Illegal credit card number";
-  return "Unknown card type" if cardtype($cardnum) eq "Unknown";
+  # FIXME
+  # there is no point in false laziness here
+  # we will effectively set "check_payinfo to 0"
+  # we can change that when we finish the refactor
+  
+  #my $cardnum = $self->cardnum;
+  #$cardnum =~ s/\D//g;
+  #$cardnum =~ /^(\d{13,16})$/
+  #  or return "Illegal credit card number";
+  #$cardnum = $1;
+  #$self->cardnum($cardnum);
+  #validate($cardnum) or return "Illegal credit card number";
+  #return "Unknown card type" if cardtype($cardnum) eq "Unknown";
 
   if ( $self->exp eq '' ) {
     return "Expiration date required"; #unless 
@@ -305,6 +310,21 @@ sub import_results {
   local $FS::UID::AutoCommit = 0;
   my $dbh = dbh;
 
+  my $pay_batch = qsearchs('pay_batch',{'batchnum'=> $paybatch});
+  unless ($pay_batch && $pay_batch->status eq 'I') {
+    $dbh->rollback if $oldAutoCommit;
+    return "batch $paybatch is not in transit";
+  };
+
+  my %batchhash = $pay_batch->hash;
+  $batchhash{'status'} = 'R';   # Resolved
+  my $newbatch = new FS::pay_batch ( \%batchhash );
+  my $error = $newbatch->replace($paybatch);
+  if ( $error ) {
+    $dbh->rollback if $oldAutoCommit;
+    return $error
+  }
+
   my $total = 0;
   my $line;
   while ( defined($line=<$fh>) ) {
diff --git a/FS/FS/pay_batch.pm b/FS/FS/pay_batch.pm
new file mode 100644
index 0000000..192c5df
--- /dev/null
+++ b/FS/FS/pay_batch.pm
@@ -0,0 +1,121 @@
+package FS::pay_batch;
+
+use strict;
+use vars qw( @ISA );
+use FS::Record qw( qsearch qsearchs );
+
+@ISA = qw(FS::Record);
+
+=head1 NAME
+
+FS::pay_batch - Object methods for pay_batch records
+
+=head1 SYNOPSIS
+
+  use FS::pay_batch;
+
+  $record = new FS::pay_batch \%hash;
+  $record = new FS::pay_batch { 'column' => 'value' };
+
+  $error = $record->insert;
+
+  $error = $new_record->replace($old_record);
+
+  $error = $record->delete;
+
+  $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::pay_batch object represents an example.  FS::pay_batch inherits from
+FS::Record.  The following fields are currently supported:
+
+=over 4
+
+=item batchnum - primary key
+
+=item status - 
+
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item new HASHREF
+
+Creates a new example.  To add the example to the database, see L<"insert">.
+
+Note that this stores the hash reference, not a distinct copy of the hash it
+points to.  You can ask the object for a copy with the I<hash> method.
+
+=cut
+
+# the new method can be inherited from FS::Record, if a table method is defined
+
+sub table { 'pay_batch'; }
+
+=item insert
+
+Adds this record to the database.  If there is an error, returns the error,
+otherwise returns false.
+
+=cut
+
+# the insert method can be inherited from FS::Record
+
+=item delete
+
+Delete this record from the database.
+
+=cut
+
+# the delete method can be inherited from FS::Record
+
+=item replace OLD_RECORD
+
+Replaces the OLD_RECORD with this one in the database.  If there is an error,
+returns the error, otherwise returns false.
+
+=cut
+
+# the replace method can be inherited from FS::Record
+
+=item check
+
+Checks all fields to make sure this is a valid example.  If there is
+an error, returns the error, otherwise returns false.  Called by the insert
+and replace methods.
+
+=cut
+
+# the check method should currently be supplied - FS::Record contains some
+# data checking routines
+
+sub check {
+  my $self = shift;
+
+  my $error = 
+    $self->ut_numbern('batchnum')
+    || $self->ut_enum('status', [ '', 'I', 'R' ])
+  ;
+  return $error if $error;
+
+  $self->SUPER::check;
+}
+
+=back
+
+=head1 BUGS
+
+The author forgot to customize this manpage.
+
+=head1 SEE ALSO
+
+L<FS::Record>, schema.html from the base documentation.
+
+=cut
+
+1;
+
diff --git a/FS/MANIFEST b/FS/MANIFEST
index bd810a8..4e71e72 100644
--- a/FS/MANIFEST
+++ b/FS/MANIFEST
@@ -340,3 +340,9 @@ t/access_groupagent.t
 FS/access_right.pm
 t/access_right.t
 FS/m2m_Common.pm
+FS/pay_batch.pm
+t/pay_batch.t
+FS/pay_batch.pm
+t/pay_batch.t
+FS/pay_batch.pm
+t/pay_batch.t
diff --git a/FS/t/pay_batch.t b/FS/t/pay_batch.t
new file mode 100644
index 0000000..c43133d
--- /dev/null
+++ b/FS/t/pay_batch.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::pay_batch;
+$loaded=1;
+print "ok 1\n";
diff --git a/README.1.5.7.lastbit b/README.1.5.7.lastbit
index ad29e4c..872d30a 100644
--- a/README.1.5.7.lastbit
+++ b/README.1.5.7.lastbit
@@ -62,3 +62,8 @@ ALTER TABLE virtual_field ALTER vfieldnum SET NOT NULL;
 ALTER TABLE virtual_field ADD PRIMARY KEY (vfieldnum);
 -- ALTER TABLE h_virtual_field ADD vfieldnum int;
 
+ALTER TABLE cust_pay_batch RENAME COLUMN cardnum TO payinfo;
+ALTER TABLE cust_pay_batch ALTER COLUMN payinfo varchar(512) NULL;
+ALTER TABLE h_cust_pay_batch RENAME COLUMN cardnum TO payinfo;
+ALTER TABLE h_cust_pay_batch ALTER COLUMN payinfo varchar(512) NULL;
+
diff --git a/README.1.5.8 b/README.1.5.8
index 6ad19b0..4335a56 100644
--- a/README.1.5.8
+++ b/README.1.5.8
@@ -8,6 +8,15 @@ install DBIx::DBSchema 0.27 (or later)
 install HTML::Widgets:SelectLayers 0.05 (or later)
 install Business::CreditCard 0.28 (or later)
 
+ALTER TABLE cust_pay_batch ADD COLUMN batchnum int;
+ALTER TABLE cust_pay_batch ALTER COLUMN batchnum SET NOT NULL;
+ALTER TABLE cust_pay_batch ADD COLUMN payinfo varchar(512);
+UPDATE cust_pay_batch SET payinfo = cardnum;
+ALTER TABLE cust_pay_batch DROP COLUMN cardnum;
+ALTER TABLE h_cust_pay_batch ADD COLUMN payinfo varchar(512);
+UPDATE h_cust_pay_batch SET payinfo = cardnum;
+ALTER TABLE h_cust_pay_batch DROP COLUMN cardnum;
+
 make install-perl-modules
 run "freeside-upgrade username" to uprade your database schema
 
diff --git a/htetc/handler.pl b/htetc/handler.pl
index cbe2dd3..d400a55 100644
--- a/htetc/handler.pl
+++ b/htetc/handler.pl
@@ -149,6 +149,7 @@ sub handler
       use FS::part_svc;
       use FS::part_svc_router;
       use FS::part_virtual_field;
+      use FS::pay_batch;
       use FS::pkg_svc;
       use FS::port;
       use FS::queue qw(joblisting);
diff --git a/httemplate/browse/cust_pay_batch.cgi b/httemplate/browse/cust_pay_batch.cgi
index 0f05ecb..6ee983a 100755
--- a/httemplate/browse/cust_pay_batch.cgi
+++ b/httemplate/browse/cust_pay_batch.cgi
@@ -3,7 +3,9 @@
 
 <FORM ACTION="<%=$p%>misc/download-batch.cgi" METHOD="POST">
 Download batch in format <SELECT NAME="format">
+<OPTION VALUE="">Default batch mode</OPTION>
 <OPTION VALUE="csv-td_canada_trust-merchant_pc_batch">CSV file for TD Canada Trust Merchant PC Batch</OPTION>
+<OPTION VALUE="BoM">Bank of Montreal ECA results</OPTION>
 </SELECT><INPUT TYPE="submit" VALUE="Download"></FORM>
 <BR><BR>
 
@@ -11,7 +13,9 @@ Download batch in format <SELECT NAME="format">
 Upload results<BR>
 Filename <INPUT TYPE="file" NAME="batch_results"><BR>
 Format <SELECT NAME="format">
+<OPTION VALUE="">Default batch mode</OPTION>
 <OPTION VALUE="csv-td_canada_trust-merchant_pc_batch">CSV results from TD Canada Trust Merchant PC Batch</OPTION>
+<OPTION VALUE="BoM">Bank of Montreal ECA results</OPTION>
 </SELECT><BR>
 <INPUT TYPE="submit" VALUE="Upload"></FORM>
 <BR>
@@ -47,7 +51,7 @@ $<%= sprintf("%.2f", $total) %> total in pending batch<BR>
 foreach my $cust_pay_batch ( sort { $a->paybatchnum <=> $b->paybatchnum }
                              qsearch('cust_pay_batch', {} )
 ) {
-  my $cardnum = $cust_pay_batch->cardnum;
+  my $cardnum = $cust_pay_batch->payinfo;
   #$cardnum =~ s/.{4}$/xxxx/;
   $cardnum = 'x'x(length($cardnum)-4). substr($cardnum,(length($cardnum)-4));
 
diff --git a/httemplate/docs/schema.html b/httemplate/docs/schema.html
index cdb59a2..d9e35ef 100644
--- a/httemplate/docs/schema.html
+++ b/httemplate/docs/schema.html
@@ -199,10 +199,16 @@
         <li>amount
         <li>_date
       </ul>
-    <li><a name="cust_pay_batch" href="man/FS/cust_pay_batch.html">cust_pay_batch</a> - Pending batch
+    <li><a name="pay_batch" href="man/FS/pay_batch.html">pay_batch</a> - Pending batch
+      <ul>
+        <li>batchnum
+        <li>status
+      </ul>
+    <li><a name="cust_pay_batch" href="man/FS/cust_pay_batch.html">cust_pay_batch</a> - Pending batch members
       <ul>
         <li>paybatchnum
-        <li>cardnum
+        <li>batchnum
+        <li>payinfo - account number
         <li>exp - card expiration
         <li>amount
         <li>invnum - <a href="#cust_bill">invoice</a>
diff --git a/httemplate/docs/upgrade10.html b/httemplate/docs/upgrade10.html
index 7cd1d8e..2a4b0d9 100644
--- a/httemplate/docs/upgrade10.html
+++ b/httemplate/docs/upgrade10.html
@@ -46,6 +46,14 @@ DROP INDEX cust_bill_pkg1;
 <pre>
 ALTER TABLE cust_main ALTER COLUMN payinfo varchar(512) NULL;
 ALTER TABLE h_cust_main ALTER COLUMN payinfo varchar(512) NULL;
+ALTER TABLE cust_pay_batch ADD COLUMN batchnum int NOT NULL;
+ALTER TABLE cust_pay_batch ALTER COLUMN batchnum SET NOT NULL;
+ALTER TABLE cust_pay_batch ADD COLUMN payinfo varchar(512) NULL;
+UPDATE cust_pay_batch SET payinfo = cardnum;
+ALTER TABLE cust_pay_batch DROP COLUMN cardnum;
+ALTER TABLE h_cust_pay_batch ADD COLUMN payinfo varchar(512) NULL;
+UPDATE h_cust_pay_batch SET payinfo = cardnum;
+ALTER TABLE h_cust_pay_batch DROP COLUMN cardnum;
 </pre>
 On older Pg versions that don't support altering columns directly, you will need to dump the database, edit the schema definitions in the dump file, and reload.
 
diff --git a/httemplate/misc/download-batch.cgi b/httemplate/misc/download-batch.cgi
index 306ef5d..5a98f1d 100644
--- a/httemplate/misc/download-batch.cgi
+++ b/httemplate/misc/download-batch.cgi
@@ -1,16 +1,79 @@
 <%
 
+my $conf=new FS::Conf;
+
 #http_header('Content-Type' => 'text/comma-separated-values' ); #IE chokes
 http_header('Content-Type' => 'text/plain' );
 
+#need default
+my $formatconfig = "batchconfig".$cgi->param('format');
+
+die "No batch configuration exists.\n$formatconfig\n" unless $conf->exists($formatconfig);
+my $format = $conf->config($formatconfig);
+
+my $oldAutoCommit = $FS::UID::AutoCommit;
+local $FS::UID::AutoCommit = 0;
+my $dbh = dbh;
+
+my $pay_batch = qsearchs('pay_batch', {'status'=>''} );
+die "No pending batch. \n" unless $pay_batch;
+
+my %batchhash = $pay_batch->hash;
+$batchhash{'status'} = 'I';
+my $new = new FS::pay_batch \%batchhash;
+my $error = $new->replace($pay_batch);
+die "error updating batch status: $error\n" if $error;
+
+my $batchtotal=0;
+my $batchcount=0;
+
+my (@date)=localtime();
+my $jdate = sprintf("%03d", $date[5] % 100).sprintf("%03d", $date[7]);
+
+if ($format eq "BoM") {
+  my($reformat,$origid,$datacenter,$typecode,$shortname,$longname,$mybank,$myacct) = $conf->config('batchconfig');
+  printf "A%10s%04u%06u%05u%54s\n",$origid,$pay_batch->batchnum,$jdate,$datacenter,"";
+  printf "XD%03u%06u%-15s%-30s%09u%-12s   \n",$typecode,$jdate,$shortname,$longname,$mybank,$myacct;
+}elsif ($format eq "CSV file for TD Canada Trust Merchant PC Batch"){
+  1;
+}else{
+  die "Unknown format for batch in batchconfig. \n";
+}
+
+
 for my $cust_pay_batch ( sort { $a->paybatchnum <=> $b->paybatchnum }
-                              qsearch('cust_pay_batch', {} )
+                           qsearch('cust_pay_batch',
+			      {'batchnum'=>$pay_batch->batchnum} )
 ) {
 
 $cust_pay_batch->exp =~ /^\d{2}(\d{2})[\/\-](\d+)[\/\-]\d+$/;
 my( $mon, $y ) = ( $2, $1 );
 $mon = "0$mon" if $mon < 10;
 my $exp = "$mon$y";
+$batchcount++;
+$batchtotal += $cust_pay_batch->amount;
+
+if ($format eq "BoM") {
+  my( $account, $aba ) = split( '@', $cust_pay_batch->payinfo );
+  printf "D%010u%09u%-12s%-29s%-19s\n",$cust_pay_batch->amount*100,$aba,$account,$cust_pay_batch->payname,$cust_pay_batch->invnum;
+}elsif ($format eq "CSV file for TD Canada Trust Merchant PC Batch"){
+%>,,,,<%= $cust_pay_batch->payinfo %>,<%= $exp %>,<%= $cust_pay_batch->amount %>,<%= $cust_pay_batch->paybatchnum %>
+<% }else{
+   die "I'm already dead, but you did not know that.\n";
+}
+
+}
+
+if ($format eq "BoM") {
+  printf "YD%08u%014u%56s\n",$batchcount,$batchtotal*100,"";
+  printf "Z%014u%05u%014u%05u%41s\n",$batchtotal*100,$batchcount,"0","0","";
+}elsif ($format eq "CSV file for TD Canada Trust Merchant PC Batch"){
+  1;
+} else{
+  die "I'm already dead (again), but you did not know that.\n";
+}
+
+$dbh->commit or die $dbh->errstr if $oldAutoCommit;
+
+%>
 
-%>,,,,<%= $cust_pay_batch->cardnum %>,<%= $exp %>,<%= $cust_pay_batch->amount %>,<%= $cust_pay_batch->paybatchnum %>
-<% } %>