Response Code	Authorization Code	Address Verification Status	Transaction ID	Submit Date/Time	Card Number	Expiration Date	Invoice Number	Invoice Description	Total Amount	Method	Action Code	Customer ID	Customer First Name	Customer Last Name	Company	Address	City	State	ZIP	Country	Phone	Fax	Email	Ship-To First Name	Ship-To Last Name	Ship-To Company	Ship-To Address	Ship-To City	Ship-To State	Ship-To ZIP	Ship-To Country	L2 - Tax	L2 - Duty	L2 - Freight	L2 - Tax Exempt	L2 - Purchase Order Number	Routing Number	Bank Account Number
1	151984	Z	708323005	10-Nov-2004 10:18:14 AM CST	XXXX2009	XXXX	2004111001	test of Amex	0.00	A	VOID		brian d	foy		5250 N. Broadway Suite 157	Chicago	IL	60640	USA												0.00	0.00	0.00	No			

##</code><code>##

Transaction ID,Transaction Status,Settlement Amount,Settlement Currency,Settlement Date/Time,Authorization Amount,Authorization Currency,Submit Date/Time,Authorization Code,Reference Transaction ID,Transaction Type,Address Verification Status,Card Code Status,Fraudscreen Applied,Recurring Billing Transaction,Partial Capture Status,Card Number,Expiration Date,Bank Account Number,Routing Number,Total Amount,Currency,Invoice Number,Invoice Description,Customer First Name,Customer Last Name,Company,Address,City,State,ZIP,Country,Phone,Fax,Email,Customer ID,Ship-To First Name,Ship-To Last Name,Ship-To Company,Ship-To Address,Ship-To City,Ship-To State,Ship-To ZIP,Ship-To Country,L2 - Tax,L2 - Freight,L2 - Duty,L2 - Tax Exempt,L2 - Purchase Order Number,CAVV Results Code,Reserved1,Reserved2,Reserved3,Reserved4,Reserved5,Reserved6,Reserved7,Reserved8,Reserved9,Reserved10,Reserved11,Reserved12,Reserved13,Reserved14,Reserved15,Reserved16,Reserved17,Reserved18,Reserved19,Reserved20
708323005,Voided,0.00,USD,10-Nov-2004 07:01:18 PM CST,0.00,USD,10-Nov-2004 10:18:14 AM CST,151984,0,Authorization Only,Z - Street Address: No Match -- First 5 Digits of Zip: Match,,Not Applicable,N,Not Applicable,XXXX2009,XXXX,,,0.00,USD,2004111001,test of Amex,brian d,foy,,5250 N. Broadway Suite 157,Chicago,IL,60640,USA,,,,,,,,,,,,,0.00,0.00,0.00,No,

##</code><code>##

Date            M       V       A  |    Total  |     M+V         A
-------------------------------------------------------------------
2004-12-10  16.00   32.00          |    48.00  |   48.00            

2004-12-12          16.00          |    16.00  |   16.00            

2004-12-13          32.00   16.00  |    48.00  |   32.00     16.00

2004-12-14  16.00                  |    16.00  |   16.00            

##</code><code>##

#!/usr/bin/perl

use File::Spec;

my $Desktop = "/Users/brian/Desktop";

@ARGV = map {
	File::Spec->catfile( $Desktop, $_ );
	} qw(
		Download20041217-040705.txt
		Download20041217-034041.txt
		);
		
my $commas = load_comma_file( $ARGV[0] );
choose( $commas, 'Transaction Status' => 'Settled Successfully' );

my $tabs   = load_tab_file(   $ARGV[1] );

merge( $tabs, $commas );

my @fields = (
	'Settlement Amount',
	'Method',
	'Settlement Date/Time',
	);	
prune( $commas, @fields );

munge_dates( $commas );

my $dates = invert( $commas );

report_totals( $dates );

# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
sub merge
	{
	my( $tabs, $commas ) = @_;

	foreach my $key ( keys %$commas )
		{
		$commas->{$key}{'Method'} = $tabs->{$key}{'Method'}
		}
	}
	
sub load_tab_file     { load_file( $_[0], \&read_tab_record,   3 ) }
sub load_comma_file   { load_file( $_[0], \&read_comma_record, 0 ) }
	
sub read_comma_record { read_record( $_[0], qr/,/  ) }
sub read_tab_record   { read_record( $_[0], qr/\t/ ) }

sub trim              { $_[0] =~ s/^\s*|\s*$//g; $_[0] };

sub read_record       { [ map { trim($_) } split m/$_[1]/,  $_[0] ] }

sub choose
	{
	my $hash = shift;
	my( $field, $value ) = @_;
	
	foreach ( keys %$hash )
		{
		my $subhash = $hash->{$_};
		
		delete $hash->{$_} unless $subhash->{$field} eq $value;
		}
	
	}
	
sub munge_dates
	{
	my $hash = shift;
	
	foreach ( keys %$hash )
		{
		my $date = 
			( split /\s+/, $hash->{$_}{'Settlement Date/Time'} )[0];
			
		my( $day, $month, $year ) = split /-/, $date;
		
		$month = month( $month );
		
		delete $hash->{$_}{'Settlement Date/Time'};
		$hash->{$_}{'Settlement Date'} = sprintf "%4d-%02d-%02d",
			$year, $month, $day;
		}

	}
	
sub month
	{
	my %hash = qw( Oct 10 Nov 11 Dec 12 Jan 1 );
	
	$hash{ $_[0] };
	}
	
sub prune
	{
	my $hash   = shift;
	
	my %fields = map { $_, 1 } @_;
	
	foreach ( keys %$hash )
		{
		my $subhash = $hash->{$_};
		foreach ( keys %$subhash )
			{
			delete $subhash->{$_} unless exists $fields{$_};
			}
		}
	
	}
	
sub load_file
	{
	my( $file, $reader, $pivot ) = @_;
	
	open my $fh, $file or die "Could not open file: $file\n$!\n";
	
	my $columns = $reader->( scalar <$fh> );
	
	my $hash = {};
	
	while( <$fh> )
		{
		my $fields = $reader->( $_ );
		
		my $trans_id = $fields->[$pivot];
		
		$hash->{ $trans_id } ||= {};
		
		@{ $hash->{ $trans_id } }{ @$columns } = @$fields;
		}
		
	return $hash;	
	}

sub invert
	{
	my $hash = shift;
	
	my $dates = {};
	
	foreach ( keys %$hash )
		{
		my $date = $hash->{$_}{'Settlement Date'};
		$dates->{$date} ||= [];
		
		push @{ $dates->{$date} }, [ 
			@{ $hash->{$_} }{ 'Method',  'Settlement Amount' }
			]; 
		}
		
	$dates;
	}
	
sub report_totals
	{
	my $hash = shift;

	print "\n";
	
	printf "%-10s   %7s  %7s  %7s  |  %7s  | %7s     %7s\n" .
	       "-" x 75 . "\n",
	       "Date", "M",  "V",  "A",  "Total",   "M+V",   "A";
	
	foreach my $date ( sort keys %$hash )
		{
		my %total;
		
		foreach my $t ( @{ $hash->{$date} } )
			{
			$total{ $t->[0] } += $t->[1];
			}
			
		printf "%10s   %7s  %7s  %7s  |  %7s  | %7s     %7s\n\n",
			$date,
			map { my $s=sprintf "%.2f", $_;  $s eq '0.00' ? '' : $s }
				@total{ qw( M V A ) }, 
				$total{M} + $total{V} + $total{A},
				$total{M} + $total{V}, 
				$total{A};
		}
		
	}