From 1add633372bdca3cc7163c2ce48363fed3984437 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Fri, 24 Jul 2015 18:19:56 -0700 Subject: [PATCH] automate RBC payment batch transfer, #35228 --- FS/FS/Conf.pm | 7 ++ FS/FS/pay_batch.pm | 58 ++++++++++------ FS/bin/freeside-rbc-download | 160 +++++++++++++++++++++++++++++++++++++++++++ FS/bin/freeside-rbc-upload | 115 +++++++++++++++++++++++++++++++ 4 files changed, 319 insertions(+), 21 deletions(-) create mode 100755 FS/bin/freeside-rbc-download create mode 100755 FS/bin/freeside-rbc-upload diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm index 4e1736be3..c93608266 100644 --- a/FS/FS/Conf.pm +++ b/FS/FS/Conf.pm @@ -3856,6 +3856,13 @@ and customer address. Include units.', }, { + 'key' => 'batchconfig-RBC-login', + 'section' => 'billing', + 'description' => 'FTPS login for uploading Royal Bank of Canada batches. Two lines: 1. username, 2. password. If not supplied, batches can still be created but not automatically uploaded.', + 'type' => 'textarea', + }, + + { 'key' => 'batchconfig-td_eft1464', 'section' => 'billing', 'description' => 'Configuration for TD Bank EFT1464 batching, seven lines: 1. Originator ID, 2. Datacenter Code, 3. Short name, 4. Long name, 5. Returned payment branch number, 6. Returned payment account, 7. Transaction code.', diff --git a/FS/FS/pay_batch.pm b/FS/FS/pay_batch.pm index a7628f6e0..df969a00f 100644 --- a/FS/FS/pay_batch.pm +++ b/FS/FS/pay_batch.pm @@ -209,7 +209,9 @@ foreach my $INC (@INC) { =item import_results OPTION => VALUE, ... -Import batch results. +Import batch results. Can be called as an instance method, if you want to +automatically adjust status on a specific batch, or a class method, if you +don't know which batch(es) the results apply to. Options are: @@ -280,6 +282,8 @@ sub import_results { my $declined_condition = $info->{'declined'}; my $close_condition = $info->{'close_condition'}; + my %target_batches; # batches that had at least one payment updated + my $csv = new Text::CSV_XS; local $SIG{HUP} = 'IGNORE'; @@ -293,13 +297,17 @@ sub import_results { local $FS::UID::AutoCommit = 0; my $dbh = dbh; - my $reself = $self->select_for_update; + if ( ref($self) ) { + # if called on a specific pay_batch, check the status of that batch + # before continuing + my $reself = $self->select_for_update; - if ( $reself->status ne 'I' - and !$conf->exists('batch-manual_approval') ) { - $dbh->rollback if $oldAutoCommit; - return "batchnum ". $self->batchnum. "no longer in transit"; - } + if ( $reself->status ne 'I' + and !$conf->exists('batch-manual_approval') ) { + $dbh->rollback if $oldAutoCommit; + return "batchnum ". $self->batchnum. "no longer in transit"; + } + } # otherwise we can't enforce this constraint. sorry. my $total = 0; my $line; @@ -345,6 +353,7 @@ sub import_results { push @all_values, \@values; } elsif ($filetype eq 'variable') { + # no longer used my @values = ( eval { $parse->($self, $line) } ); if( $@ ) { $dbh->rollback if $oldAutoCommit; @@ -404,6 +413,9 @@ sub import_results { unless ( $cust_pay_batch ) { return "unknown paybatchnum $hash{'paybatchnum'}\n"; } + # remember that we've touched this batch + $target_batches{ $cust_pay_batch->batchnum } = 1; + my $custnum = $cust_pay_batch->custnum, my $payby = $cust_pay_batch->payby, @@ -443,21 +455,25 @@ sub import_results { } # foreach (@all_values) - my $close = 1; - if ( defined($close_condition) ) { - # Allow the module to decide whether to close the batch. - # $close_condition can also die() to abort the whole import. - $close = eval { $close_condition->($self) }; - if ( $@ ) { - $dbh->rollback; - die $@; + # decide whether to close batches that had payments posted + foreach my $batchnum (keys %target_batches) { + my $pay_batch = FS::pay_batch->by_key($batchnum); + my $close = 1; + if ( defined($close_condition) ) { + # Allow the module to decide whether to close the batch. + # $close_condition can also die() to abort the whole import. + $close = eval { $close_condition->($pay_batch) }; + if ( $@ ) { + $dbh->rollback; + die $@; + } } - } - if ( $close ) { - my $error = $self->set_status('R'); - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return $error; + if ( $close ) { + my $error = $pay_batch->set_status('R'); + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return $error; + } } } diff --git a/FS/bin/freeside-rbc-download b/FS/bin/freeside-rbc-download new file mode 100755 index 000000000..376b839e1 --- /dev/null +++ b/FS/bin/freeside-rbc-download @@ -0,0 +1,160 @@ +#!/usr/bin/perl + +use strict; +use Getopt::Std; +use Date::Format qw(time2str); +use File::Temp qw(tempdir); #0.19 for ->newdir() interface, not in 5.10.0 +use Net::FTPSSL; +use FS::UID qw(adminsuidsetup dbh); +use FS::Record qw(qsearch qsearchs); +use FS::pay_batch; +use FS::Conf; + +use vars qw( $opt_v $opt_a $opt_f ); +getopts('va:f:'); + +#$Net::SFTP::Foreign::debug = -1; +sub usage { " + Usage: + freeside-rbc-download [ -v ] [ -a archivedir ] [ -f filename ] user\n +" } + +sub debug { + print STDERR $_[0] if $opt_v; +} + +my $user = shift or die &usage; +adminsuidsetup $user; + +$FS::UID::AutoCommit = 0; +my $dbh = dbh; + +if ( $opt_a ) { + die "no such directory: $opt_a\n" + unless -d $opt_a; + die "archive directory $opt_a is not writable by the freeside user\n" + unless -w $opt_a; +} + +my $tmpdir = tempdir( CLEANUP => 1 ); #DIR=>somewhere? + +my $conf = new FS::Conf; +my ($username, $password) = $conf->config('batchconfig-RBC-login'); +$username and $password + or die "RBC FTP login not configured. Enter your username and password in 'batchconfig-rbc-login'.\n"; + +my $host = 'ftpssl.rbc.com'; +debug "Connecting to $username\@$host...\n"; + +my $ftp = Net::FTPSSL->new($host, + Timeout => 30, + Debug => ($opt_v ? 1 : 0), + Croak => 1, # rely on auto-rollback when dbh closes + ); +$ftp->login($username, $password); + +# directory layout: +# ~/ # upload to here +# ~/inbound +# ~/inbound/valid # batches move here while being processed +# ~/outbound +# ~/outbound/XXXX # string of four characters; results arrive here + +$ftp->cwd('outbound'); +for my $dir ( $ftp->nlst ) { + debug "Entering outbound/$dir\n"; + $ftp->cwd($dir); + FILE: for my $filename ( $ftp->nlst ) { + debug "$filename..."; + # filenames look like "RPT9999X.111". + # 9999 is the four-digit report type + # X is "P" for production or "T" for test + # 111 is the sequential file number + if ( $opt_f ) { + if ( $filename ne $opt_f ) { + debug "is not the requested file.\n"; + next FILE; + } + # -f can be used to download/process any file, even one that doesn't fit + # the naming rule (e.g. those that are already downloaded). + } elsif ( $filename =~ /^RPT(\d{4})[PT]\.\d{3}$/ ) { + # fallthrough; don't currently reject files based on RPT type, because + # our parser should be able to figure it out + } else { + debug "skipped.\n"; + next FILE; + } + + debug "downloading.\n"; + $ftp->get($filename, "$tmpdir/$filename"); + + #copy to archive dir + if ( $opt_a ) { + debug "Copying to archive dir $opt_a\n"; + system 'cp', "$tmpdir/$filename", $opt_a; + warn "failed to copy $tmpdir/$filename to $opt_a: $!\n" if $!; + } + + debug "Processing batch..."; + open(my $fh, '<', "$tmpdir/$filename") + or die "couldn't read temp file: $!\n"; + + my $error = FS::pay_batch->import_results( + filehandle => $fh, + format => 'RBC', + ); + + if ( $error ) { + die "Processing $filename failed:\n$error\n\n"; + } + + debug "done.\n"; + } # FILE + $ftp->cdup(); +} # $dir + +debug "Finished.\n"; +dbh->commit; +exit(0); + +=head1 NAME + +freeside-rbc-download - Retrieve payment batch responses from RBC. + +=head1 SYNOPSIS + + freeside-rbc-download [ -v ] [ -f filename ] [ -a archivedir ] user + +=head1 DESCRIPTION + +Command line tool to download payment batch responses from the Royal Bank of +Canada ACH service. These files are fixed-width data files containing some +combination of valid, returned, or reversed payment records. + +By default, the script will download any files with names like "RPT9999X.111" +where 9999 is a four-digit document type code (like "0900", all records), X is +the letter "P" for production or "T" for test mode, and 111 is a counter +incremented with each new response file. After the files are downloaded, RBC's +server will automatically rename them with the suffix '.downloaded%FTPS' to +avoid double-processing them. + + +-v: Be verbose. + +-f filename: Download a file with a specific name, instead of all files +matching the pattern. This can be used to reprocess a specific file. + +-a directory: Archive the files in the specified directory. + +user: freeside username + +=head1 BUGS + +=head1 SEE ALSO + +L + +=cut + +1; + diff --git a/FS/bin/freeside-rbc-upload b/FS/bin/freeside-rbc-upload new file mode 100755 index 000000000..52501028c --- /dev/null +++ b/FS/bin/freeside-rbc-upload @@ -0,0 +1,115 @@ +#!/usr/bin/perl + +use strict; +use Getopt::Std; +use DateTime; +use Net::FTPSSL; +use File::Temp qw(tempdir); +use File::Slurp 'write_file'; +use FS::UID qw(adminsuidsetup dbh); +use FS::Record qw(qsearch qsearchs); +use FS::pay_batch; +use FS::Conf; + +use vars qw( $opt_a $opt_v $opt_p ); +getopts('avp:'); + +sub usage { " + Usage: + freeside-rbc-upload [ -v ] user batchnum + freeside-rbc-upload -a [ -p payby ] [ -v ] user\n +" } + +sub debug { + print STDERR $_[0] if $opt_v; +} + +my $user = shift or die &usage; +adminsuidsetup $user; + +my @batches; + +# copied from freeside-paymentech-upload, obviously +if($opt_a) { + my %criteria = (status => 'O'); + $criteria{'payby'} = uc($opt_p) if $opt_p; + @batches = qsearch('pay_batch', \%criteria); + die "No open batches found".($opt_p ? " of type '$opt_p'" : '').".\n" + if !@batches; +} +else { + my $batchnum = shift; + die &usage if !$batchnum; + @batches = qsearchs('pay_batch', { batchnum => $batchnum } ); + die "Can't find payment batch '$batchnum'\n" if !@batches; +} + +my $conf = new FS::Conf; +my ($username, $password) = $conf->config('batchconfig-RBC-login'); + +$username and $password + or die "RBC FTP login not configured. Enter your username and password in 'batchconfig-rbc-login'.\n"; + +my $host = 'ftpssl.rbc.com'; +debug "Connecting to $username\@$host...\n"; + +my $date = DateTime->now->strftime('%Y%m%d'); + +my $ftp = Net::FTPSSL->new($host, + Timeout => 30, + Debug => ($opt_v ? 1 : 0), + Croak => 1, # rely on auto-rollback when dbh closes + ); +$ftp->login($username, $password); + +my $tmpdir = tempdir( CLEANUP => 1 ); + +foreach my $pay_batch (@batches) { + my $batchnum = $pay_batch->batchnum; + my $filename = $date . '.' . sprintf('%06d', $batchnum); + debug "Exporting batch $batchnum to $filename\n"; + + my $text = $pay_batch->export_batch(format => 'RBC'); + write_file("$tmpdir/$filename", $text); + + debug "Uploading $filename..."; + $ftp->put("$tmpdir/$filename", $filename); + debug "done.\n"; +} + +debug "Finished.\n"; + +=head1 NAME + +freeside-rbc-upload - Transmit a payment batch to RBC via FTP/TLS. + +=head1 SYNOPSIS + + freeside-rbc-upload [ -a [ -p PAYBY ] ] [ -v ] user batchnum + +=head1 DESCRIPTION + +Command line tool to upload a payment batch to the Royal Bank of Canada +ACH service. Use L to retrieve the response file. +Options: + +-a: Send all open batches, instead of specifying a batchnum. + +-p PAYBY: With -a, limit to batches of that payment type, e.g. -p CARD. + +-v: Be verbose. + +user: freeside username + +batchnum: pay_batch primary key + +=head1 BUGS + +=head1 SEE ALSO + +L + +=cut + +1; + -- 2.11.0