The tab-separated format has everything I need save for one thing: the transaction status. It lists every attempted charge, whether it was an authorization, decline, void, or something else. It doesn't tell me the status of the transaction. I can't create totals from that because I can't tell which ones went through and which ones were declined or voided. Here's a sample line from the tab-separated file.

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 N
+ame    Company    Address    City    State    ZIP    Country    Phone
+    Fax    Email    Ship-To First Name    Ship-To Last Name    Ship-T
+o 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    XXXX20
+09    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
[download]

The comma-separated format knows the transaction status, but it doesn't know the charge type: MasterCard, American Express, or Visa. It boggles me that the reports are missing these fields, but they are. Here's a sample line:

Transaction ID,Transaction Status,Settlement Amount,Settlement Currenc
+y,Settlement Date/Time,Authorization Amount,Authorization Currency,Su
+bmit Date/Time,Authorization Code,Reference Transaction ID,Transactio
+n Type,Address Verification Status,Card Code Status,Fraudscreen Appli
+ed,Recurring Billing Transaction,Partial Capture Status,Card Number,E
+xpiration Date,Bank Account Number,Routing Number,Total Amount,Curren
+cy,Invoice Number,Invoice Description,Customer First Name,Customer La
+st Name,Company,Address,City,State,ZIP,Country,Phone,Fax,Email,Custom
+er ID,Ship-To First Name,Ship-To Last Name,Ship-To Company,Ship-To Ad
+dress,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,CA
+VV Results Code,Reserved1,Reserved2,Reserved3,Reserved4,Reserved5,Res
+erved6,Reserved7,Reserved8,Reserved9,Reserved10,Reserved11,Reserved12
+,Reserved13,Reserved14,Reserved15,Reserved16,Reserved17,Reserved18,Re
+served19,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 Applic
+able,XXXX2009,XXXX,,,0.00,USD,2004111001,test of Amex,brian d,foy,,52
+50 N. Broadway Suite 157,Chicago,IL,60640,USA,,,,,,,,,,,,,0.00,0.00,0
+.00,No,
[download]

They both have a transaction ID (although in different columns, so load_file() is a bit ugly), and I can join the records on that. After reading in each file (nothing fancy yet, but as I have to use this more, there will probably be some sort of LWP thing to download them) and loading them into a big hash of hashes, I select from the comma-separated records only the ones that have been settled successfully. The choose() routine simply deletes any keys whose sub-key "Transaction Status" is not "Settled Successfully". The merge() routine joins all of the second level hash keys based on the transaction ID, which both formats share. Having done that and knowing I don't need most of the information from the files, I use prune() to delete any of the sub-keys not in @fields. I don't really need to do this, but I had an easier time looking at Data::Dumper output this way. If I were handling huge files, I would have done this sooner, but I have only several hundred records and the script runs fast enough for me to not care right now.

Once I have the records I want, I turn the date-time groups into a date format I want to use. All the records settled in a particular batch have the same date-time group (that looks like 17-Nov-2004 02:19:53 AM CST), so I really don't need the time. In munge_dates() I just throw away the time and turn the month into a number ( 2004-11-17 ) for easy string sorting.

Now I have this big data structure sitting around. I have an entry for every successful credit card settlement, and each settlement knows all of its data. I don't care about settlements though: I want to know daily totals. I need to turn the data structure inside out so I key on the date instead of the transaction ID, and that's what invert() does. The invert() function also throws away extra data, since I only want to know how much I charged and which type of card I charged. I end up with a hash of arrays, and in each array is an amount-card pair.

The report_totals() turns that hash into a table, and I actually think the table is pretty slick. I wanted to use a format for this, but lexical varialbes carried the day. I started with a printf format to put numbers in columns, put sometimes that would make a 0.00. That just clutters the report, so I changed all the format specifiers to strings and made the string an empty one if the total came out as the string 0.00. Now I just see the important numbers. The first three number columns are totals by card type (which I like to see but don't really matter for this problem), the column between the pipes is the total money I'm due for that day, and the last two are the deposit amounts I should see in my bank statement. I should see a separate deposit for each amount in the last two columns.

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
[download]

I print that report, put it next to my printed bank statement, and start matching settlements and deposits. Badda bing badda boom, once done there are some settlements without check marks next to them. They owe me a bunch of money and I know the dates they missed. That's one of the reasons I think the reports aren't that helpful, even though I could just attribute it to incompetence: if you can't easily see what they owe you, you can't easily request your money. Oh well.

#!/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};
        }
        
    }
[download]