From db4d8679af26c301cb66f3f3da7f7cd7a3ae4854 Mon Sep 17 00:00:00 2001 From: mark Date: Fri, 22 Jul 2011 19:07:43 +0000 Subject: [PATCH] EFT Canada batch format and scripts, #13628 --- FS/FS/Conf.pm | 10 +- FS/FS/cust_pay_batch.pm | 18 ++- FS/FS/pay_batch/eft_canada.pm | 63 +++++++++ FS/bin/freeside-eftca-download | 155 +++++++++++++++++++++ FS/bin/freeside-eftca-upload | 122 ++++++++++++++++ httemplate/search/elements/cust_pay_batch_top.html | 1 + 6 files changed, 362 insertions(+), 7 deletions(-) create mode 100644 FS/FS/pay_batch/eft_canada.pm create mode 100755 FS/bin/freeside-eftca-download create mode 100755 FS/bin/freeside-eftca-upload diff --git a/FS/FS/Conf.pm b/FS/FS/Conf.pm index 9953ea54b..36d3bbdce 100644 --- a/FS/FS/Conf.pm +++ b/FS/FS/Conf.pm @@ -3122,7 +3122,8 @@ and customer address. Include units.', 'description' => 'Fixed (unchangeable) format for electronic check batches.', 'type' => 'select', 'select_enum' => [ 'csv-td_canada_trust-merchant_pc_batch', 'BoM', 'PAP', - 'paymentech', 'ach-spiritone', 'RBC', 'td_eft1464' + 'paymentech', 'ach-spiritone', 'RBC', 'td_eft1464', + 'eft_canada' ] }, @@ -3183,6 +3184,13 @@ and customer address. Include units.', }, { + 'key' => 'batchconfig-eft_canada', + 'section' => 'billing', + 'description' => 'Configuration for EFT Canada batching, four lines: 1. SFTP username, 2. SFTP password, 3. Transaction code, 4. Number of days to delay process date.', + 'type' => 'textarea', + }, + + { 'key' => 'payment_history-years', 'section' => 'UI', 'description' => 'Number of years of payment history to show by default. Currently defaults to 2.', diff --git a/FS/FS/cust_pay_batch.pm b/FS/FS/cust_pay_batch.pm index 171ec9fcf..f5e6a4bf1 100644 --- a/FS/FS/cust_pay_batch.pm +++ b/FS/FS/cust_pay_batch.pm @@ -300,26 +300,32 @@ sub approve { return; } -=item decline +=item decline [ REASON ] Decline this payment. This will replace the existing record with the same paybatchnum, set its status to 'Declined', and run collection events as appropriate. This should only be called from the batch import process. +REASON is a string description of the decline reason, defaulting to +'Returned payment'. + =cut sub decline { my $new = shift; - my $conf = new FS::Conf; + my $reason = shift || 'Returned payment'; + #my $conf = new FS::Conf; my $paybatchnum = $new->paybatchnum; my $old = qsearchs('cust_pay_batch', { paybatchnum => $paybatchnum }) or return "paybatchnum $paybatchnum not found"; if ( $old->status ) { # Handle the case where payments are rejected after the batch has been - # approved. Only if manual approval is enabled. - if ( $conf->exists('batch-manual_approval') - and lc($old->status) eq 'approved' ) { + # approved. FS::pay_batch::import_results won't allow results to be + # imported to a closed batch unless batch-manual_approval is enabled, + # so we don't check it here. +# if ( $conf->exists('batch-manual_approval') and + if ( lc($old->status) eq 'approved' ) { # Void the payment my $cust_pay = qsearchs('cust_pay', { custnum => $new->custnum, @@ -329,7 +335,7 @@ sub decline { # should never happen... return "failed to revoke paybatchnum $paybatchnum, payment not found"; } - $cust_pay->void('Returned payment'); + $cust_pay->void($reason); } else { # normal case: refuse to do anything diff --git a/FS/FS/pay_batch/eft_canada.pm b/FS/FS/pay_batch/eft_canada.pm new file mode 100644 index 000000000..0e4161034 --- /dev/null +++ b/FS/FS/pay_batch/eft_canada.pm @@ -0,0 +1,63 @@ +package FS::pay_batch::eft_canada; + +use strict; +use vars qw(@ISA %import_info %export_info $name); +use FS::Record 'qsearch'; +use FS::Conf; +use FS::cust_pay_batch; +use Date::Format 'time2str'; +use Time::Local 'timelocal'; + +my $conf; +my $origid; + +$name = 'eft_canada'; + +%import_info = ( filetype => 'NONE' ); # see FS/bin/freeside-eftca-download + +my ($trans_code, $process_date); + +%export_info = ( + init => sub { + my $conf = shift; + my @config = $conf->config('batchconfig-eft_canada'); + # SFTP login, password, trans code, delay time + my $process_delay; + ($trans_code, $process_delay) = @config[2,3]; + $process_delay ||= 1; # days + $process_date = time2str('%D', time + ($process_delay * 86400)); + }, + delimiter => '', # avoid blank lines for header/footer + # EFT Upload Specification for .CSV Files, Rev. 2.0 + # not a true CSV format--strings aren't quoted, so be careful + row => sub { + my ($cust_pay_batch, $pay_batch) = @_; + my @fields; + # company + empty or first + last + my $company = sprintf('%.64s', $cust_pay_batch->cust_main->company); + if ( $company ) { + push @fields, $company, '' + } + else { + push @fields, map { sprintf('%.64s', $_) } + $cust_pay_batch->first, $cust_pay_batch->last; + } + my ($account, $aba) = split('@', $cust_pay_batch->payinfo); + # standard format for Canadian bank ID + $aba =~ /^0(\d{3})(\d{5})$/ + or die "invalid routing number '$aba'\n"; + push @fields, sprintf('%05s', $2), + sprintf('%03s', $1), + sprintf('%012s', $account), + sprintf('%.02f', $cust_pay_batch->amount); + # DB = debit + push @fields, 'DB', $trans_code, $process_date; + push @fields, $cust_pay_batch->paybatchnum; # reference + # strip illegal characters that might occur in customer name + s/[,|']//g foreach @fields; # better substitution for these? + return join(',', @fields) . "\n"; + }, + +); + +1; diff --git a/FS/bin/freeside-eftca-download b/FS/bin/freeside-eftca-download new file mode 100755 index 000000000..3d717bcc6 --- /dev/null +++ b/FS/bin/freeside-eftca-download @@ -0,0 +1,155 @@ +#!/usr/bin/perl + +use strict; +use Getopt::Std; +use Date::Format qw(time2str); +use File::Temp qw(tempdir); +use Net::SFTP::Foreign; +use Expect; +use FS::UID qw(adminsuidsetup datasrc); +use FS::Record qw(qsearch qsearchs); +use FS::pay_batch; +use FS::cust_pay_batch; +use FS::Conf; + +use vars qw( $opt_v $opt_a ); +getopts('va:'); + +#$Net::SFTP::Foreign::debug = -1; +sub HELP_MESSAGE { " + Usage: + freeside-eftca-download [ -v ] [ -a archivedir ] user\n +" } + +my @fields = ( + 'tid', # transaction ID + 'paybatchnum', # reference field + 'returncode', # status code + 'returndate', + 'paid', # dollars and cents, with decimal + 'type', + 'first', + 'last', + 'account', + 'bank', + 'transit', +); + +my $user = shift or die &HELP_MESSAGE; +adminsuidsetup $user; + +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 = File::Temp->newdir(); +my $tmpdir = tempdir( CLEANUP => 1 ); #DIR=>somewhere? + +my $conf = new FS::Conf; +my @batchconf = $conf->config('batchconfig-eft_canada'); +# BIN, terminalID, merchantID, username, password +my $username = $batchconf[0] or die "no EFT Canada batch username configured\n"; +my $password = $batchconf[1] or die "no EFT Canada batch password configured\n"; + +my $host = 'ftp.eftcanada.com'; +print STDERR "Connecting to $username\@$host...\n" if $opt_v; + +my $sftp = Net::SFTP::Foreign->new( host => $host, + user => $username, + password => $password, + timeout => 30, + ); +die "failed to connect to '$username\@$host'\n(".$sftp->error.")\n" if $sftp->error; + +$sftp->setcwd('/Returns'); + +my $files = $sftp->ls('.', wanted => qr/^ReturnFile/, names_only => 1); +die "no response files found\n" if !@$files; + +FILE: foreach my $filename (@$files) { + print STDERR "Retrieving $filename\n" if $opt_v; + $sftp->get("$filename", "$tmpdir/$filename"); + if($sftp->error) { + warn "failed to download $filename\n"; + next FILE; + } + + #move to server archive dir + $sftp->rename("$filename", "Archive/$filename"); + if($sftp->error) { + warn "failed to archive $filename on server\n"; + } # process it anyway though + + #copy to local archive dir + if ( $opt_a ) { + print STDERR "Copying $tmpdir/$filename to archive dir $opt_a\n" + if $opt_v; + system 'cp', "$tmpdir/$filename", $opt_a; + warn "failed to copy $tmpdir/$filename to $opt_a: $@" if $@; + } + + open my $fh, "<$tmpdir/$filename"; + # Some duplication with FS::pay_batch::import_results, but we're really + # doing something different here. + my $csv = new Text::CSV_XS ( { quote_char => undef, sep_char => '|' } ); + my %hash; + while (my $line = <$fh>) { + next if $line =~ /^\s*$/; + $csv->parse($line) or do { + warn "can't parse $filename: ".$csv->error_input."\n"; + next FILE; #parsing errors = reading the wrong kind of file + }; + @hash{@fields} = $csv->fields(); + print STDERR "voiding paybatchnum#$hash{paybatchnum}\n" if $opt_v; + my $cpb = qsearchs('cust_pay_batch', + { paybatchnum => $hash{'paybatchnum'} }); + if ( !$cpb ) { + warn "can't find paybatchnum #$hash{paybatchnum} ($hash{first} $hash{last}, $hash{paid})\n"; + next; + } + my $error = $cpb->decline("Returned payment ($hash{returncode})"); + if ( $error ) { + warn "can't void paybatchnum #$hash{paybatchnum}: $error\n"; + } + } + close $fh; +} + +print STDERR "Finished!\n" if $opt_v; + +=head1 NAME + +freeside-eftca-download - Retrieve payment batch responses from EFT Canada. + +=head1 SYNOPSIS + + freeside-eftca-download [ -v ] [ -a archivedir ] user + +=head1 DESCRIPTION + +Command line tool to download returned payment reports from the EFT Canada +gateway and void the returned payments. Uses the login and password from +'batchconfig-eft_canada'. + +-v: Be verbose. + +-a directory: Archive response files in the provided directory. + +user: freeside username + +=head1 BUGS + +You need to manually SFTP to ftp.eftcanada.com from the freeside account +and accept their key before running this script. + +=head1 SEE ALSO + +L + +=cut + +1; + diff --git a/FS/bin/freeside-eftca-upload b/FS/bin/freeside-eftca-upload new file mode 100755 index 000000000..b50115365 --- /dev/null +++ b/FS/bin/freeside-eftca-upload @@ -0,0 +1,122 @@ +#!/usr/bin/perl + +use strict; +use Getopt::Std; +use Date::Format qw(time2str); +use File::Temp qw(tempdir); +use Net::SFTP::Foreign; +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 ); +getopts('av'); + +#$Net::SFTP::Foreign::debug = -1; + +sub HELP_MESSAGE { " + Usage: + freeside-eftca-upload [ -v ] user batchnum + freeside-eftca-upload -a [ -v ] user\n +" } + +my $user = shift or die &HELP_MESSAGE; +adminsuidsetup $user; + +my @batches; + +if($opt_a) { + @batches = qsearch('pay_batch', { 'status' => 'O', 'payby' => 'CHEK' }) + or die "No open batches found.\n"; +} +else { + my $batchnum = shift; + die &HELP_MESSAGE if !$batchnum; + @batches = qsearchs('pay_batch', { batchnum => $batchnum } ); + die "Can't find payment batch '$batchnum'\n" if !@batches; +} + +my $conf = new FS::Conf; +my @batchconf = $conf->config('batchconfig-eft_canada'); +my $username = $batchconf[0] or die "no EFT Canada batch username configured\n"; +my $password = $batchconf[1] or die "no EFT Canada batch password configured\n"; + +my $tmpdir = tempdir( CLEANUP => 1 ); #DIR=>somewhere? + +my @filenames; + +foreach my $pay_batch (@batches) { + my $batchnum = $pay_batch->batchnum; + my $filename = time2str('%Y%m%d', time) . '-' . sprintf('%06d.csv',$batchnum); + print STDERR "Exporting batch $batchnum to $filename...\n" if $opt_v; + my $text = $pay_batch->export_batch('eft_canada'); + open OUT, ">$tmpdir/$filename"; + print OUT $text; + close OUT; + push @filenames, $filename; +} + +my $host = 'ftp.eftcanada.com'; +print STDERR "Connecting to $username\@$host...\n" if $opt_v; + +my $sftp = Net::SFTP::Foreign->new( host => $host, + user => $username, + password => $password, + timeout => 30, + ); +die "failed to connect to '$username\@$host'\n(".$sftp->error.")\n" + if $sftp->error; + +foreach my $filename (@filenames) { + $sftp->put("$tmpdir/$filename", "$filename") + or die "failed to upload file (".$sftp->error.")\n"; +} + +$FS::UID::AutoCommit = 0; +foreach my $pay_batch (@batches) { + # Auto-approve and close the batch. Some false laziness with manual_approve. + my $batchnum = $pay_batch->batchnum; + my $error; + foreach my $cpb ( qsearch('cust_pay_batch', { 'batchnum' => $batchnum } ) ) { + $cpb->setfield('paid', $cpb->amount); + $error = $cpb->approve($batchnum); + last if $error; + } + $error ||= $pay_batch->set_status('R'); + die "error closing batch $batchnum: $error\n\n" if $error; +} +dbh->commit; + +print STDERR "Finished!\n" if $opt_v; + +=head1 NAME + +freeside-eftca-upload - Transmit a payment batch to EFT Canada via SFTP. + +=head1 SYNOPSIS + + freeside-paymentech-upload [ -a ] [ -v ] user batchnum + +=head1 DESCRIPTION + +Command line tool to upload a payment batch to the EFT Canada gateway. The +batch will be exported to a comma-delimited file and transmitted via SFTP. +Use L to retrieve the response file. + +-a: Send all open batches, instead of specifying a batchnum. + +-v: Be verbose. + +user: freeside username + +batchnum: pay_batch primary key + +=head1 SEE ALSO + +L + +=cut + +1; + diff --git a/httemplate/search/elements/cust_pay_batch_top.html b/httemplate/search/elements/cust_pay_batch_top.html index 96ed428b0..ce0ee9ed4 100644 --- a/httemplate/search/elements/cust_pay_batch_top.html +++ b/httemplate/search/elements/cust_pay_batch_top.html @@ -106,6 +106,7 @@ tie my %download_formats, 'Tie::IxHash', ( 'paymentech' => 'XML file for Chase Paymentech', 'RBC' => 'Royal Bank of Canada PDS batch', 'td_eft1464' => '1464 byte file for TD Commercial Banking EFT', +'eft_canada' => 'EFT Canada CSV batch', # insert new batch formats here ); -- 2.11.0