package Support::Implementation::BasePublisherPayeeTransaction;
# 5/19/11 - Initial release.
# 5/23/11 - Corrected publisher logic per additional test cases in FB14013
# 2/1/12 - Made transaction date optional.
# 5/19/14 - Enabled Excel 2007.
# 2/13/15 - Corrected regex in _normalizeDate
#
use strict;
use warnings;

use diagnostics;

#use AutoLoader 'AUTOLOAD';

use lib '/app/tools/common/lib';
use lib '/app/tools/rps/lib';

use IO::File;
use Data::Dumper;
use Date::Calc qw(Add_Delta_Days Days_in_Month);

use Spreadsheet::ParseExcel;

use Common::Assert;
use Common::UTF8;
use Common::RSApp;
use Common::Consts;
use Common::CurrencyFormat;
use Common::Util qw( clean trimspaces);

#use RPS::DB::Item::Publisher;
#use RPS::DB::Item::PublisherAccount;
#use RPS::DB::Item::FinanceAccount;
#use RPS::DB::Item::PendingTransaction;

use Support::Implementation::ExcelReader;
use Support::Implementation::Excel2007Reader;
use Support::Implementation::TabDelimitedReader;

use RPS::DB::Item::Publisher;
use RPS::DB::Item::PublisherAccount;
use RPS::DB::Item::CAPublisher;
use RPS::DB::Item::CAPublisherAccount;
use RPS::DB::Item::FinanceAccount;
use RPS::DB::Item::PendingTransaction;

use base 'Support::Implementation::Template';

use constant kQuiet  => 0;
use constant kNormal => 1;
use constant kDebug  => 3;
my $gReportLevel = kNormal;

use constant kOriginUndefined     => 0;
use constant kOriginUnitedStates  => 1;
use constant kOriginCanada        => 2;
use constant kOriginUK            => 3;

binmode STDOUT, ":utf8";

#-----------------------------------------------------------------------
# templateHeader maps column names to their default column number.
# The actual column number-header name map is stored in columnMap.
# Note(s):
# 1) Column names are case sensitive
# 2) Spaces are ignored; you must manually remove spaces from the names
#    stored in templateHeader
#-----------------------------------------------------------------------
my %gTemplateHeader; # must overload

#--------------------------------------------------------------
# Maps column #'s to a unique key (headername).  The reverse of
# templateHeader, but with actual column #.
#--------------------------------------------------------------
my %gColumnMap = ();


#----------------------------------
# excount keeps track of exceptions
#----------------------------------
my %gExCount;

#--------------------------------------
# gCount keeps track of entities created
#--------------------------------------
my %gCount;

my $gDefaultPayorID;
my %gPayorMap = (); # maps names to ID

my $gPublisherMap; # maps publisher id to name

my $clientID;
my $execMode;

my $dbo;
my $dbh;
my $cdbo;

sub new {
   my ($class, %args) = @_;
   my $self = bless {}, $class;
   return $self->_init(%args);
}


sub _init {
   my( $self, %args ) = @_;

   report("PublisherTemplate::_init -- args = ". Dumper(\%args));

   $self->SUPER::_init(%args);

   $execMode = $self->exec_mode if ( $self->exec_mode );

   return $self;
}

sub parseHeader {
   my $self = shift;
}

#----------------------------------------------
# Read-in all of the template data into memory.
#----------------------------------------------
sub loadMemory {
    my $self = shift;

    $clientID = $self->client_id;
    my $app = Common::RSApp->new(clientID => $clientID);

    $self->{dbo}  = Common::RSApp::GetClientDB();
    $self->{dbh}  = $self->{dbo}->DBH;
    $self->{cdbo} = Common::RSApp::GetCommonDB();

    my $fileName = $self->name;

    $gPublisherMap = $self->_getPublisherMap();


    if( $self->isExcel2003( $fileName ) ) {
        print("BasePublisherPayeeTransaction::loadMemory -- loading Excel2k3 $fileName into memory\n");

        # Read in the header
        my %data;
        my $reader = Support::Implementation::ExcelReader->new(
            filename => $fileName,
            data => \%data,
            header => \%gTemplateHeader,
            columnmap => \%gColumnMap
        );
        $reader->scanExcelFile;

        # Try and parse it...
        $self->_processData(\%data);

    } elsif( $self->isExcel2007( $fileName ) ) {
        print("BasePublisherPayeeTransaction::loadMemory -- loading Excel2k7 $fileName into memory\n");

        # Read in the header
        my %data;
        my $reader = Support::Implementation::Excel2007Reader->new(
            filename => $fileName,
            data => \%data,
            header => \%gTemplateHeader,
            columnmap => \%gColumnMap
        );
        $reader->scanExcelFile;

        # Try and parse it...
        $self->_processData(\%data);
    } elsif( $self->isTabDelimited( $fileName ) ) {
        report("BasePublisherPayeeTransaction::loadMemory -- processing tab-delimited file");

        my %data;
        my $reader = Support::Implementation::TabDelimitedReader->new(
            filename => $fileName,
            data => \%data,
            header => \%gTemplateHeader,
            columnmap => \%gColumnMap
        );

        $reader->scanTabbedFile;
        $self->_processData(\%data);
    }
}


sub _getPublisherMap {
    die("BasePublisherPayeeTransaction::_initPublisherMap must be overloaded!");
}

sub _getPublisherIDColumnName {
    die("BasePublisherPayeeTransaction::_getPublisherIDColumnName must be overloaded!");
}

sub _getPublisherColumnName {
    die("BasePublisherPayeeTransaction::_getPublisherColumnName must be overloaded!");
}

sub _getAdminColumnName {
    die("BasePublisherPayeeTransaction::_getAdminColumnName must be overloaded!");
}

sub _getAgentColumnName {
    die("BasePublisherPayeeTransaction::_getAgentColumnName must be overloaded!");
}

sub _getPublisherName {
    my($self, $id) = @_;
    die("BasePublisherPayeeTransaction::_getPublisherName must be overloaded!");
}

sub _findPublisher {
    my($self, $args) = @_;
    die("BasePublisherPayeeTransaction::_getPublisherName must be overloaded!");
}

sub _getPublisherID {
    my($self, $publisherObject) = @_;
    die("BasePublisherPayeeTransaction::_getPublisherID must be overloaded!");
}

sub _getPublisherByID {
    my($self, $id) = @_;
    die("BasePublisherPayeeTransaction::_getPublisherByID must be overloaded!");
}

sub _getPublisherAdminID {
    my($self, $id) = @_;
    die("BasePublisherPayeeTransaction::_getPublisherAdminID must be overloaded!");
}

sub _getPublisherAgentID {
    my($self, $id) = @_;
    die("BasePublisherPayeeTransaction::_getPublisherAgentID must be overloaded!");
}

sub _getPublisherClientAccountID {
    my($self, $id) = @_;
    die("BasePublisherPayeeTransaction::_getPublisherClientAccountID must be overloaded!");
}

sub _getTransactionCurrencyCode {
    die("BasePublisherPayeeTransaction::_getTransactionCurrencyCode must be overloaded!");
}

sub _createPublisherAccount {
    die("BasePublisherPayeeTransaction::_createPublisherAccount must be overloaded!");
}

sub _getPublisherString {
    die("BasePublisherPayeeTransaction::_getPublisherString must be overloaded!");
}

#-------------------------------------------------------------------
#
# _processData is where the real work is done.  It takes the generic
# information stored in the supplied array of hashes and decodes it.
#
#-------------------------------------------------------------------
sub _processData {
    my($self, $data) = @_;
    my $rows = $data->{rows};

    #--------------------------------------
    # count keeps track of entities created
    #--------------------------------------
    %gCount = (
        publisher           => 0,
        publisher_account   => 0,
        finance_account     => 0,
        pending_transaction => 0,
    );

    #------------------------
    # Setup payor information
    #------------------------
    my $sql = "SELECT payor_id,name,is_default FROM payor";
    my $sth = $self->{dbo}->DoCmd($sql);
    while( my($id,$name,$isDefault) = $sth->fetchrow_array() )
    {
        $gPayorMap{$id} = lc $name;
        $gDefaultPayorID = $id if ( $isDefault );
    }

    die("No default payor setup for client") if ( not defined $gDefaultPayorID );

    #---------------------------------
    # Get the client's currency format
    #---------------------------------
    $sql = "SELECT country_code FROM client WHERE client_id=$clientID";
    $sth = $self->{cdbo}->DoCmd($sql);
    my($cCode) = $sth->fetchrow_array();
    my $currencyFormat = new Common::CurrencyFormat( countryCode => $cCode );
    my $denomination = $currencyFormat->currencyCode();

    die("ERROR: Unable to find currency denomination for client($clientID)") if ( !$denomination );

    #my %publisherMap = $self->_getPublisherMap();


    foreach my $row (@$rows)
    {
        #======================================================================
        # Read in the column information.
        #
        # IMPORTANT: Except for 'rowid', the hash names _MUST_ match the column
        # names in the template.
        #======================================================================
        my $rowid           = $row->{'rowid'};
        my $origin          = $row->{'RoyaltyShare Publisher Origin Name'};

        my $clientAccountID = $row->{'Client Account #'};
        my $clientPayorID   = $row->{'Client Payor #'};

        my $publisherID     = $row->{ $self->_getPublisherIDColumnName() };

        my $publisherName   = $row->{ $self->_getPublisherColumnName() };

        my $adminName       = $row->{ $self->_getAdminColumnName() };
        my $agentName       = $row->{ $self->_getAgentColumnName() };

        my $payorID         = $row->{'RS Payor ID'};

        my $payorName       = $row->{'*Payor Name'};

        my $transactionType = $row->{'*Type of Transaction'};

        my $amount          = $row->{'*Amount'};
        my $transactionDate = $row->{'Transaction Date'} || $row->{'*Transaction Date'};
        my $checkNumber     = $row->{'Check #'};
        my $memo            = $row->{'Memo'};

        #my $oldPublisherName   = $row->{'Publisher Name'};  # OBE

        my $errorCode;
        report("#### row($rowid): " . Dumper(\%$row));

        $clientAccountID = trimspaces( $clientAccountID ) if ( $clientAccountID );
        $clientPayorID   = trimspaces( $clientPayorID   ) if ( $clientPayorID   );
        $publisherID     = trimspaces( $publisherID     ) if ( $publisherID     );
        $publisherName   = trimspaces( $publisherName   ) if ( $publisherName   );
        $adminName       = trimspaces( $adminName       ) if ( $adminName       );
        $agentName       = trimspaces( $agentName       ) if ( $agentName       );
        $payorID         = trimspaces( $payorID         ) if ( $payorID         );
        $payorName       = trimspaces( $payorName       ) if ( $payorName       );
        $transactionType = trimspaces( $transactionType ) if ( $transactionType );
        $amount          = trimspaces( $amount          ) if ( $amount          );
        $transactionDate = trimspaces( $transactionDate ) if ( $transactionDate );
        $checkNumber     = trimspaces( $checkNumber     ) if ( $checkNumber     );
        $memo            = trimspaces( $memo            ) if ( $memo            );


        #if ( $oldPublisherName ) {
        #   die("Old template detected - please update per FB12915");
        #}

        if ( !$publisherName && !$payorName && !$transactionType ) {
            report("WARNING: line $rowid is blank");
            next;
        }


        #-------------------------------------------------------------
        # If a valid administrator or agent is in the template,
        # then adminID will be the publisher ID of the admin or agent.
        #-------------------------------------------------------------
        my $adminID;

        #---------------------------------------------------------------
        # If an admin was specified, then we must a) find the admin, and
        # b) make sure the publisher is actually using the admin
        #---------------------------------------------------------------
        if ( $adminName )
        {
            my $c = $self->_findPublisher(
               name     => $adminName,
               is_admin => 1,
            );

            my $numFound = $c->size();

            if ( $numFound == 0 )
            {
                _appendString( $errorCode, "Admin not found");
                ++$gExCount{admin_not_found};
            }
            elsif( $numFound > 1 )
            {
                report("ERROR: adminName($adminName) not unique!");
                _appendString( $errorCode, "Admin is not unique");
                ++$gExCount{admin_not_unique};
            }
            else
            {
                my $p = $c->next();
                $adminID = $self->_getPublisherID($p);
                report("DEBUG: Found adminID($adminID) name($adminName)");
            }
        }

        #----------------------------
        # Find the agent if specified
        #----------------------------
        if ( $agentName )
        {
            my $c = $self->_findPublisher(
               name      => $agentName,
               is_agency => 1,
            );

            my $numFound = $c->size();

            if ( $numFound == 0 )
            {
                _appendString( $errorCode, "Agent not found");
                ++$gExCount{agent_not_found};
            }
            elsif( $numFound > 1 )
            {
                report("ERROR: agentName($adminName) not unique!");
                _appendString( $errorCode, "Agent is not unique");
                ++$gExCount{agent_not_unique};
            }
            else
            {
                my $p = $c->next();

                #-------------------------------------------------------------
                # IMPORTANT: If both an admin and agent is specified, the
                # transaction is applied to the agent.  Thus, setting 'adminID'
                # here will override the adminID found if an admin was found
                # above.
                #-------------------------------------------------------------
                $adminID = $self->_getPublisherID($p);
                report("DEBUG: Found agentID($adminID) name($agentName)");
            }
        }

        #---------------------------------------------------------------
        # Once we've found a publisher, store the actual agent/admin IDs
        # This will make is easier to validate the publisher.
        #---------------------------------------------------------------
        my $actualAgentID;
        my $actualAdminID;
        my $actualClientAccountID;

        #----------------------------------------------------------------
        # Find the publisher first using the publisherID (if specified)
        # or by searching based on the publisher name and client account
        # id (if specified).  Note that we don't check for an admin/agent
        # relation (yet).
        #----------------------------------------------------------------
        if ( $publisherID )
        {
            #-------------------------------------------------------
            # If the publisherID was specified in the template, make
            # sure its valid
            #-------------------------------------------------------
            my $realName = $gPublisherMap->{ $publisherID };

            if ( $realName )
            {
                if ( (lc $realName) ne (lc $publisherName) )
                {
                    report("ERROR: Publisher name mismatch: '$publisherName' doesn't match "
                        . "'$realName'");
                    _appendString( $errorCode, "Bad RS publisher ID");
                    ++$gExCount{bad_rs_publisher_id};
                }
            }
            else
            {
                _appendString( $errorCode, "PublisherID not found");
                ++$gExCount{publisher_id_not_found};
            }

            my $pObj = $self->_getPublisherByID( $publisherID );
die("ERROR: _getPublisherByID didn't return object for publisherID($publisherID)?!") if ( !$pObj );

            $actualAgentID         = $self->_getPublisherAgentID( $pObj );
            $actualAdminID         = $self->_getPublisherAdminID( $pObj );
            $actualClientAccountID = $self->_getPublisherClientAccountID( $pObj );

            if ( $clientAccountID && $actualClientAccountID &&
                 ($clientAccountID ne $actualClientAccountID) )
            {
                report("ERROR: Publisher clientAccountID mismatch: read '$clientAccountID', expected "
                    . "'". ($actualClientAccountID || '')  . "'");
                _appendString( $errorCode, "Bad client account #");
                ++$gExCount{bad_client_account_id};
            }

        }
        else
        {
            #-----------------------------------------------------------------
            # The publisher name wasn't specified; try to locate it using name
            # from the template and if provided, the publisher client account
            # id from the template.
            #-----------------------------------------------------------------

            if ( $publisherName )
            {
                my %searchArgs = (
                   name              => $publisherName,
                   client_account_id => $clientAccountID,
                );

                my $c = $self->_findPublisher( %searchArgs );

                my $numFound = $c->size();

                if ( $numFound == 0 )
                {
                    report("ERROR: publisherName($publisherName) not found!");
                    _appendString( $errorCode, "Publisher not found");
                    ++$gExCount{publisher_not_found};


#                    if ( $agentName || $adminName )
#                    {
#                        # TODO: This will have to suffice for now, but maybe this
#                        # can be changed to something like 'publisher not found' or
#                        # 'publisher is not configured to use admin/agent'.
#                        #
#                        _appendString( $errorCode, "Publisher with admin/agent not found");
#                        ++$gExCount{publisher_admin_agent_not_found};
#                    }
#                    else
#                    {
#                        _appendString( $errorCode, "Publisher not found");
#                        ++$gExCount{publisher_not_found};
#                    }
                }
                elsif( $numFound > 1 )
                {
                    report("ERROR: publisherName($publisherName) not unique!");
                    _appendString( $errorCode, "Publisher is not unique");
                    ++$gExCount{publisher_not_unique};
                }
                else
                {
                    my $p = $c->next();
                    $publisherID           = $self->_getPublisherID($p);
                    $actualAgentID         = $self->_getPublisherAgentID( $p );
                    $actualAdminID         = $self->_getPublisherAdminID( $p );
                    $actualClientAccountID = $self->_getPublisherClientAccountID( $p );
                    report("DEBUG: Found publisherID($publisherID) name($publisherName)");
                }

            }
            else
            {
                report("ERROR: publisherName missing!");
                _appendString( $errorCode, "Missing publisher name");
                ++$gExCount{missing_publisher_name};
            }

        }

        #--------------------------------------------------------------------
        # If the publisher was found, check for any admin/agent relationships
        #--------------------------------------------------------------------
        if ( $publisherID && ($agentName || $adminName) )
        {
            my %searchArgs = (
               publisher_id => $publisherID,
            );
            if ( $agentName )
            {
                $searchArgs{agent_id} = $adminID;
            }
            else
            {
                $searchArgs{admin_id} = $adminID;
            }

            my $c = $self->_findPublisher( %searchArgs );

            my $numFound = $c->size();

            if ( $numFound == 0 )
            {
                if ( $agentName )
                {
                    report( "ERROR: Publisher $publisherID with specified agent $adminID not found");
                    _appendString( $errorCode, "Publisher with agent not found");
                    ++$gExCount{publisher_agent_not_found};
                }
                else
                {
                    report( "ERROR: Publisher $publisherID with specified admin $adminID not found");
                    _appendString( $errorCode, "Publisher with admin not found");
                    ++$gExCount{publisher_admin_not_found};
                }
            }
        }

        #-------------------------------------------------------------------------
        # Check for 3-tier relationship.  If publisher is setup for both an admin
        # and agent, then you cannot enter a transaction against the admin.  So,
        # if the template only has an admin entered but the publisher has an agent
        # then we have an error.
        #-------------------------------------------------------------------------
        if ( $publisherID && $actualAgentID && $adminName && !$agentName )
        {
            _appendString( $errorCode, "Publisher admin specified but agent expected");
            ++$gExCount{publisher_admin_but_no_agent};
        }

        #-------------------
        # Validate the payor
        #-------------------
        if ( $payorID )
        {
            if ( exists $gPayorMap{$payorID} )
            {
                if ( $payorName && ( lc $payorName ne $gPayorMap{$payorID} ) )
                {
                    report("ERROR: Payor name mismatch: '$payorName' doesn't match "
                        . "'" . $gPayorMap{$payorID} . "'");
                    _appendString( $errorCode, "Bad RS payor ID");
                    ++$gExCount{bad_rs_payor_id};
                }
                else
                {
                    report("   INFO: PayorID($payorID) specified and it matches DB info");
                }
            }
            else
            {
                report("ERROR: payorID $payorID is invalid!");
                _appendString( $errorCode, "Bad RS payorID");
                ++$gExCount{bad_rs_payor_id};
            }
        }
        elsif ($payorName)
        {
            # Find the payor
            $payorID = $self->_findPayor(
                name            => $payorName,
                client_payor_id => $clientPayorID
            );

            #die("ERROR: line $rowid: unable to find payor payorName($payorName)") if ( !$payorID );

            if ( !$payorID )
            {
                report("ERROR: payor $payorName not found!");
                _appendString( $errorCode, "Payor not found");
                ++$gExCount{payor_not_found};
            }
        }
        else
        {
            report("ERROR: payorName missing!");
            _appendString( $errorCode, "Missing payor name");
            ++$gExCount{missing_payor_name};
        }

        if ( $payorID )
        {
            report("DEBUG: payorName($payorName) --> payorID($payorID)");
        }
        else
        {
            report("DEBUG: payorName($payorName) not found");
        }

        #------------------------------
        # Validate the transaction type
        #------------------------------
        my $_transactionType = _getTransactionType($transactionType);
        if ( !$transactionType )
        {
            report("ERROR: Missing transaction type");
            _appendString( $errorCode, "Missing transaction type");
            ++$gExCount{missing_transaction_type};
        }
        elsif ( !$_transactionType )
        {
            report("DEBUG: BasePublisherPayeeTransaction:_processData: Illegal transaction type '$transactionType'");
            report("ERROR: Illegal transaction type");
            _appendString( $errorCode, "Illegal transaction type");
            ++$gExCount{illegal_transaction_type};
        }

        #----------------------------
        # Make sure we have an amount
        #----------------------------
        if ( !$amount )
        {
            report("ERROR: Missing amount");
            _appendString( $errorCode, "Missing amount");
            ++$gExCount{missing_amount};
        }

        $amount = _normalizeAmount( $amount );

        if ( $amount && $amount == 0 )
        {
            report("ERROR: Zero amount");
            _appendString( $errorCode, "Zero amount");
            ++$gExCount{missing_amount};
        }

        if ( $amount and $_transactionType and (
            $_transactionType == RPS::DB::Item::PendingTransaction::kTypeAdvance or
            $_transactionType == RPS::DB::Item::PendingTransaction::kTypePayment ) )
        {
            if ( $amount < 0 )
            {
                if ( $_transactionType == RPS::DB::Item::PendingTransaction::kTypeAdvance )
                {
                    report("ERROR: Negative advance not allowed");
                    _appendString( $errorCode, "Negative advance not allowed");
                    ++$gExCount{negative_advance_not_allowed};
                }
                elsif( $_transactionType == RPS::DB::Item::PendingTransaction::kTypePayment )
                {
                    report("ERROR: Negative payment not allowed");
                    _appendString( $errorCode, "Negative payment not allowed");
                    ++$gExCount{negative_payment_not_allowed};
                }
            }
            else
            {
                #------------------------------------------------------------------------
                # Per FB12968/FB11818, if the transaction type is advance or payment then
                # we need to multiply the balance by -1 to get the correct figure in the
                # system.
                #------------------------------------------------------------------------
                $amount *= -1;
            }
        }

        #------------------------------------------
        # The date _must_ be in the form mm/dd/yyyy
        #------------------------------------------
        my $convertedDate;
        if ( $transactionDate )
        {
            #if ( $transactionDate !~ '(\d*)/(\d*)/(\d\d\d\d)')
            if ( ! _validDate($transactionDate) )
            {
                report("ERROR: Bad transaction date format '$transactionDate'");
                _appendString( $errorCode, "Bad transaction date, expected mm/dd/yyyy");
                ++$gExCount{bad_transaction_date};
            }
            else
            {
                $convertedDate = _normalizeDate($transactionDate);
                if ( exists $row->{'Transaction Date'} ) {
                    $row->{'Transaction Date'} = $convertedDate;
                } elsif ( exists $row->{'*Transaction Date'} ) {
                    $row->{'*Transaction Date'} = $convertedDate;
                } else {
                    die("ERROR: unable to store normalized transaction date !!!");
                }
            }
        }
        #else
        #{
        #    report("ERROR: Missing transaction date");
        #    _appendString( $errorCode, "Missing transaction date");
        #    ++$gExCount{missing_transaction_date};
        #}


        #=======================================
        # If there are any errors, we're done...
        #=======================================
        if ( $errorCode )
        {
            $row->{'error-code'} = $errorCode;
            #die("_processData: STOP - ERROR: <<$errorCode>>"); # XXX - TEST
            next;
        }


        #------------------------------------
        # Otherwise create the transaction !!
        #------------------------------------
        my %args = (
            #origin           => $_origin,
            #origin           => kOriginUnitedStates,

            publisher_id     => $publisherID,
            payor_id         => $payorID,
            transaction_type => $_transactionType,
            amount           => $amount,
            currency_code    => $self->_getTransactionCurrencyCode(),
        );

        # optional arguments
        $args{transaction_date} = $convertedDate if ( $convertedDate );
        $args{check_number}     = $checkNumber if ( $checkNumber );
        $args{memo}             = $memo if ( $memo );
        $args{admin_id}         = $adminID if ( $adminID );

report("BasePublisherPayeeTransaction: Calling _creatPublisherTransaction: ".
   Dumper(\%args));

        $self->_createPublisherTransaction( %args );

#die("_processData: STOP");
    }# bottom of row loop

   #------------------------------------------------------------
   # Dump out the errors
   # TODO: Need to properly propagate the errors back to the user
   #------------------------------------------------------------
   _showExceptions( $rows );

   #-----------------------
   # Show import statistics
   #-----------------------
   report("##### S U M M A R Y #####");
   my $totalRows = (scalar @$rows);

   my $totalExceptions = 0;
   foreach my $c (keys %gExCount) {
      my $v = $gExCount{$c};
      printf("%30s %6d\n", $c, $v);
      $totalExceptions += $v;
   }
   printf("%30s %s\n", " ", "-------" );
   printf("%30s %6d\n", "Total Exceptions", $totalExceptions );
   printf("%30s %6d\n", "Total Rows", $totalRows );
   report(" ");

   foreach my $c (keys %gCount) {
      my $v = $gCount{$c};
      printf("%30s %d\n", $c, $v);
   }

}#_processData

sub _normalizeAmount {
   my($amount) = @_;

   if ( $amount ) {
      $amount =~ s/\$//;
      $amount =~ s/,//;
      $amount =~ s/^\s*//;
      $amount =~ s/\s*$//;
   }
   return $amount;
}

sub _findPayor {
    my($self, %args) = @_;

    my $name          = $args{name};
    my $clientPayorID = $args{client_payor_id};
    assert($name);

    my $payorID;
    my $sql = "SELECT payor_id FROM payor WHERE name=? ";

    my $sth;
    if ( $clientPayorID )
    {
        $sql .= "AND client_payor_id=? ";
        $sth = $self->{dbh}->prepare($sql);
        $sth->execute($name, $clientPayorID);
    }
    else
    {
        $sth = $self->{dbh}->prepare($sql);
        $sth->execute($name);
    }

    my $found;
    while( my($id) = $sth->fetchrow_array() )
    {
        die("ERROR: _findPayor: payor '$name' not unique") if ( $found );

        $payorID = $id;
        $found = 1;
    }
    return $payorID;
}

#-------------------------------------------------------------
# _getTransactionType: convert a transaction type string to an
# actual RPS value.
#-------------------------------------------------------------
sub _getTransactionType {
   my($t) = @_;
   my $retval;
   if ( lc $t eq "adjustment" ) {
      $retval = RPS::DB::Item::PendingTransaction::kTypeAdjustment;
      #return RPS::DB::Item::PendingTransaction::kTypeAdjustment;
   } elsif ( lc $t eq "advance" ) {
      $retval = RPS::DB::Item::PendingTransaction::kTypeAdvance;
   } elsif ( lc $t eq "payment" ) {
      $retval = RPS::DB::Item::PendingTransaction::kTypePayment;
   }# else {
   #   die("_getTransactionType: illegal transaction type '$t'\n");
   #}
   return $retval;
}

#------------------------------------------------------------
# _getOrigin: map the origin string into an internal constant
#------------------------------------------------------------
sub _getOrigin {
   my($o) = @_;
   my $retval = kOriginUndefined;
   $retval = kOriginUnitedStates if ( lc $o eq 'united states' );
   $retval = kOriginCanada if ( lc $o eq 'canada' );
   #$retval = kOriginUK if ( lc $o eq 'uk' );
   return $retval;
}

sub _createFinanceAccount {
    my( $self, %args ) = @_;

    my $description  = $args{description};
    my $typeCode     = $args{type_code};
    my $currencyCode = $args{currency_code};

    #-------------------------
    # Create a finance account
    #-------------------------
    my %faArgs = (
        description   => $description,
        type_code     => $typeCode,
        currency_code => $currencyCode,
    );
    my $faObj = RPS::DB::Item::FinanceAccount->Lookup(%faArgs);

    my $financeAccountID;

    if ( !$faObj )
    {
        if ( $self->exec_mode )
        {
            $faObj = RPS::DB::Item::FinanceAccount->Create(%faArgs);
            $faObj->save();
            $financeAccountID = $faObj->finance_account_id;
            report("   Created finance_account $financeAccountID : ".Dumper(\%faArgs));
            ++$gCount{finance_account};
        }
        else
        {
            report("   Non-exec mode, skipped finance_account, desc($description)");
        }
    }
    else
    {
        # Finance account exists...
        $financeAccountID = $faObj->finance_account_id;
    }

    return $faObj;
}#_createFinanceAccount

sub _createTransaction {
    my( $self, %args ) = @_;

    my $amount           = $args{amount};
    my $financeAccountID = $args{finance_account_id};
    my $currencyCode     = $args{currency_code};
    #my $typeCode         = $args{type_code};
    my $transactionType  = $args{transaction_type};

    assert($amount);
    assert($financeAccountID);
    assert($currencyCode);
    #assert($typeCode);
    assert($transactionType);

    my $memo            = $args{memo};
    my $transactionDate = $args{transaction_date};
    my $checkNumber     = $args{check_number};

    $amount =~ s/\$//;
    $amount =~ s/,//;
    if ( $amount =~ m/\(.*\)/ ) {
       $amount =~ s/\(//;
       $amount =~ s/\)//;
       $amount *= -1;
    }

    my %ptArgs = (
        finance_account_id => $financeAccountID,
        amount             => $amount,
        currency_code      => $currencyCode,
        #type_code          => $typeCode,
        type_code          => $transactionType,
    );
    $ptArgs{memo}             = $memo if ( $memo );
    $ptArgs{transaction_date} = $transactionDate if ( $transactionDate );
    $ptArgs{check_number}     = $checkNumber if ( $checkNumber );

    # Note: the following will allow duplicate transactions to be created.
    #
    my $ptObj;
    if ( $self->exec_mode ) {
        $ptObj = RPS::DB::Item::PendingTransaction->Create(%ptArgs);
        $ptObj->save();
        my $ptID = $ptObj->pending_transaction_id;
        report("   Created pending_transaction $ptID : ".Dumper(\%ptArgs));
        ++$gCount{pending_transaction};
    } else {
        report("   Non-exec mode, skipping pending_transaction :" . Dumper(\%ptArgs));
    }

    return $ptObj;

}#_createTransaction

#
# _createPublisherTransaction
#
sub _createPublisherTransaction {
    my( $self, %args ) = @_;

    #my $origin           = $args{origin};
    my $publisherID      = $args{publisher_id};
    my $payorID          = $args{payor_id};
    my $transactionType  = $args{transaction_type};
    my $amount           = $args{amount};

    my $transactionDate  = $args{transaction_date};
    my $checkNumber      = $args{check_number};
    my $memo             = $args{memo};
    my $currencyCode     = $args{currency_code};

report("BasePublisherPayeeTransaction: _createPublisherTransaction: transactionDate(". printNull($transactionDate) . ")");
    # If an admin is specified, then we'll setup a pending transaction
    # through an indirect balance account.  Otherwise we'll use a normal
    # publisher account.
    #
    my $adminID          = $args{admin_id};  # this can be an admin _or_ agent publisher ID

    # Note: should we create a publisher account if it doesn't exist?
    # If so, what min payment do we use?
    my $minPayment = 0; # only for new publisher accounts

    #assert($origin);
    assert($publisherID);
    assert($payorID);
    assert($transactionType);
    assert($amount);

    my $paObj = $self->_createPublisherAccount(
        payor_id => $payorID,
        id       => $publisherID,
        admin_id => $adminID,
    );

    my $publisherString = $self->_getPublisherString();

    if ( $paObj )
    {
        my $financeAccountID = $paObj->finance_account_id;
        if ( !$financeAccountID )
        {
            my $description;
            if ( $adminID )
            {
               $description = "account for $publisherString $publisherID payor $payorID indirect publisher $adminID";
            }
            else
            {
               $description = "account for $publisherString $publisherID payor $payorID";
            }

            my $faObj = $self->_createFinanceAccount(
                description   => $description,
                type_code     => RPS::DB::Item::FinanceAccount::kAccountTypeHoldover,
                currency_code => $currencyCode,
            );
            $financeAccountID = $faObj->finance_account_id if ( $faObj );

            if ( $self->exec_mode && $financeAccountID )
            {
                $paObj->finance_account_id($financeAccountID);
                $paObj->save();
            }
        }

        #--------------------------------------------------------
        # If finance account ID is set then setup the transaction
        #--------------------------------------------------------
        if ( $financeAccountID )
        {
            my $ptObj = $self->_createTransaction(
                amount             => $amount,
                finance_account_id => $financeAccountID,
                currency_code      => $currencyCode,
                #type_code          => $typeCode,
                memo               => $memo,
                transaction_date   => $transactionDate,
                check_number       => $checkNumber,
                transaction_type   => $transactionType,
            );
            if ( $ptObj ) {
                my $id = $ptObj->pending_transaction_id;
                report("BasePublisherTransaction:_createPublisherTransaction: created pt $id");
            }
        }

    }


    #die("BasePublishertransaction:_createTransaction");

} #_createPublisherTransaction

#--------------------------------------------------------------------------
# _showExceptions was originally intended to _just_ show the template lines
# that exceptioned out.  It's been modified to output both exception and
# non-exception lines.  Exception message(s) will be placed in the error
# code column.  If a line is imported successfully, then the resulting
# payeeID will be stored in the import status column ("payeeID(###)"),
# otherwise this column will contain the string "__FAILED__".
#--------------------------------------------------------------------------
sub _showExceptions {
   my ( $rows ) = @_;

   #---------------------------
   # Build the exception header
   #---------------------------
   my @header;
   for( my $i = 0; $i < (keys %gColumnMap); $i++ ) {
      # Get the key at the specified column
      my $v = $gColumnMap{$i};

      #----------------------------------------------------------
      # Do _not_ push the "Error Code" or "import-status" columns
      # if they were in the original template.  We'll re-create
      # them on the fly.
      #----------------------------------------------------------
      next if ( ("Error Code" eq $v) || ("import-status") eq $v );

      push @header, $v;
   }

   report("STATUS:\t".join("\t", @header, "Error Code", "import-status"));

   #--------------------------------------
   # Now dump out the rows that had errors
   #--------------------------------------
   foreach my $row (@$rows) {
      my $rowid = $row->{rowid};
report("dumping row($rowid)");


#      my $payeeName = $row->{'publisher-name'};
#      next if ( !$payeeName );

      my $errorCode = $row->{'error-code'};
      my $payeeID = $row->{'rs-payee-id'};

      #----------------------------------------
      # Output the row data in the proper order
      #----------------------------------------
      my @obuf;
      for( my $i = 0; $i < (keys %gColumnMap); $i++ ) {
         # Get the key at the specified column
         my $v = $gColumnMap{$i};
         my $val = ($row->{$v}) ? $row->{$v} : '';

         #----------------------------------------------------------
         # Do _not_ push the "Error Code" or "import-status" columns
         # if they were in the original template.
         #----------------------------------------------------------
         next if ( ("Error Code" eq $v) || ("import-status") eq $v );

         push @obuf, $val;
      }

      $payeeID = "NULL" if ( !$payeeID );

      my $importStatus;
      if ( $errorCode ) {
         $importStatus = "__FAIL__";
      } else {
         $importStatus = "payee($payeeID)";
      }

      my $ecString = ($errorCode) ? $errorCode : '';

      my $zzbuf = join("\t", "STATUS:", @obuf, $ecString, $importStatus);

      report($zzbuf);
   }
}#_showExceptions

#----------------------------------------------------------------------
# _normalizeDate convert the template date into a MySQL-compatible date
# TODO: Should do this the same way the sales importers do it...
#----------------------------------------------------------------------
sub _validDate {
   my($dt) = @_;
   my $retval;
   if ( $dt =~ m/(\d+)[\/|-](\d+)[\/|-](\d{4})/  ||
        $dt =~ m/(\d{1,2})[\/|-](\d{1,2})[\/|-](\d{2})/  ||  # MM/DD/YYYY or MM/DD/YY
#        $dt =~ /^(\d{5})$/ || # Excel 5-digit string
        $dt =~ m/(\d{4})[\/|-](\d+)[\/|-](\d+)/ )
   {
       $retval = 1;
   }
   return $retval;
}

sub _normalizeDate {
   my($dt) = @_;

   my($month, $day, $year);
   if ( $dt =~ m/^(\d{1,2})[\/|-](\d{1,2})[\/|-](\d{4})/ ) # MM/DD/YYYY or MM-DD-YYYY
   {
       ($month, $day, $year) = ($1, $2, $3);
   }
   elsif ( $dt =~ m/^(\d{1,2})[\/|-](\d{1,2})[\/|-](\d{2})/ ) # MM/DD/YY or MM-DD-YY
   {
       ($month, $day, $year) = ($1, $2, $3);
       $year += 2000;
   }
   elsif( $dt =~ m/(\d{4})[\/|-](\d{1,2})[\/|-](\d{1,2})/ ) # YYYY/MM/DD or YYYY-MM-DD
   {
       ($month, $day, $year) = ($2, $3, $1);
#   }
#   elsif( $dt =~ /^(\d{5})$/ ) {
#       my $_dt = Spreadsheet::ParseExcel::Utility::ExcelFmt( "yyyy-mm-dd", $dt );
#       ($year, $month, $day) = split('-', $_dt);
   }
   return sprintf("%4d-%02d-%02d", $year, $month, $day);
}

#---------------------------------------------------
# _isValidDateFmt - return true if the supplied date
# is in the format YYYY-MM-DD, false otherwise
#---------------------------------------------------
sub _isValidDateFmt {
   my($dstr) = @_;
   my($yr,$mo,$dy) = split("-",$dstr);
   my $st = 1; # valid unless we detect otherwise

   $st = 0 if ( !$yr || !$mo || !$dy );
   $st = 0 if ( $mo && ( $mo !~ /^\d+$/ ));
   $st = 0 if ( $yr && ( $yr !~ /^\d+$/ ));
   $st = 0 if ( $dy && ( $dy !~ /^\d+$/ ));
   $st = 0 if ( length($yr) != 4 );
   return $st;
}

sub _appendString {
   my($str,$v) = @_;

   if ( $str ) {
      my $cur = $str;
      my $newstring = "$cur; $v";
      $_[0] = $newstring;
   } else {
      $_[0] = $v;
   }
   return;
}# _appendString

sub _reportError {
   my ($name, $obj) = @_;

   report("   _reportError: checking '$name' for errors");
   if ($obj && $obj->_hasError()) {
      #my ($e, $msg) = $obj->getError();
      #print "$name has an error: $e : $msg\n";

      my %xmlParams = $obj->getXMLParams();
      my $msg = defined $xmlParams{emsg} ? $xmlParams{emsg} : "MSG_NOT_AVAILABLE";
      my $e = $xmlParams{e};
      print "$name has an error:: $e :: $msg\n";
      return 1;
   }
   return undef;
}

sub printNull {
   my($s) = @_;
   return $s ? $s : "NULL";
}

sub report {
   my($text, $level) = @_;
   $level = kNormal unless $level;
   if ( $level <= $gReportLevel ) {
      print $text . "\n";
   }
}

1;