Paypal to Freeside batch payments

From PrgmrWiki

Freeside can import payments in batches by uploading a csv file, but it can only take customer numbers for who the payment is from instead of invoice numbers. Here is a perl script that takes the csv file from paypal and looks up the customer number and prints a csv file for to upload to freeside. I took the basic structure of this script from http://perldoc.perl.org/perlfaq5.html#How-can-I-read-in-an-entire-file-all-at-once%3f

#!/usr/bin/perl
use strict;
use warnings;

use DBI;

my ($file, @fields, $transactiontype, $itemnum, $custnum, $amount, $transactionid, $itemtitle, $name, $fromemail, $date);
$file=$ARGV[0];

my $dbh = DBI->connect("DBI:Pg:dbname=freeside;", "freeside", '', {'RaiseError' => 1});
my $fetchcustnum=$dbh->prepare("SELECT custnum FROM cust_bill WHERE invnum=?;");

open (INPUT, $file)     || die "can't open $file: $!";
while (<INPUT>) {
        chomp;
        @fields=split(/"[,\t]"/);
        $date=$fields[0];
        $transactiontype=$fields[4];
        $itemnum=$fields[16];
        $amount=$fields[7];
        $transactionid=$fields[12];
        $itemtitle=$fields[15];
        $name=$fields[3];
        $fromemail=$fields[10];

        if ($transactiontype=~/Payment Received$/) {
                if ($itemtitle=~/pr[mg]{2}r Xen VPS invoice/) {

                $fetchcustnum->execute($itemnum);
                $custnum=$fetchcustnum->fetchrow_array;

                print "$custnum, ,$amount,pp#$transactionid\n";
                }
                else { print STDERR "$amount $transactionid payment without an invoice from $name $fromemail on $date\n"; }
        }


        # do something with $_
        }
close(INPUT)            || die "can't close $file: $!";