package Support::Implementation::ArtistPayeeTransactionTemplate;
# 8/30/10: Per FB10234, multiply amount by -1 if transaction type
#  is advance or payment.
# 9/27/10: Updated date handling to allow YYYY-MM-DD dates.
# 12/22/10: Modified for new template format (FB12914)
# 7/14/11: Removed trailing spaces from artist_payee.name from DB
# 10/5/11: Added code to deal with Excel dates
# 12/18/12: Updated normalizeDate
# 1/28/13: Remove whitespace from front/end of payor name
# 9/3/13: Removed newline from payor name
# 2/5/14: Added Excel2007 support
# 2/14/14: Updated date logic
# 10/14/15: Skip line if no payee or payor in template
# 8/26/16: Removed trailing spaces from transactionType
# 10/3/16: Clean-up
# 12/2/16: Send post import (or test) output to STDERR
# 8/25/17: Disabled duplicate template row check; also
#   disabled duplicate pending transaction check (FB20129).
# 7/11/18: Updated date processing; clean up $amount if
#   it contains an embedded return.
# 9/9/19: Cleaned up payee name (found bad chars in RSD-4477).
# 5/11/20: Use Common::Util::normalize_date
# 8/10/20: Use _normalizeDate
#
use strict;
use warnings;

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 );

use Support::Implementation::ExcelReader;
use Support::Implementation::Excel2007Reader;
use Support::Implementation::TabDelimitedReader;
use Support::Implementation::Util qw( _normalizeDate );

use RPS::DB::Item::ArtistPayee;
use RPS::DB::Item::ArtistPayeeAccount;
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 = (
   "*Artist Payee Name"   => 0,  # A
   "Client Account #",    => 1,  # B
   "RS Artist Payee ID"    => 2,  # C
   "*Payor Name"         => 3,  # D
   "Client Payor #",      => 4,  # E
   "RS Payor ID"          => 5,  # F
   "*Type of Transaction" => 6,  # G
   "*Amount"            => 7,  # H
   "*Transaction Date"   => 8,  # I
   "Check #"             => 9,  # J
   "Memo"               => 10, # K
);

#--------------------------------------------------------------
# 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 $clientID;
my $execMode;

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

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);

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

   my $fileName = $self->name;

   if( $self->isExcel2003( $fileName ) ) {
      print("ArtistPayeeTransactionTemplate::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...
      _processData(\%data);

   } elsif( $self->isExcel2007( $fileName ) ) {
      print("ArtistPayeeTransactionTemplate::loadMemory -- loading Excel2k3 $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...
      _processData(\%data);
   } elsif( $self->isTabDelimited( $fileName ) ) {
      report("ArtistPayeeTransactionTemplate::loadMemory -- processing tab-delimited file");

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

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

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

   #----------------------------------------
   # Reset the exception and entity counters
   #----------------------------------------
   %gExCount = ();
   %gCount = ();

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

      $gPayorMap{$id} = lc $name;
      if ( $isDefault ) {
         $gDefaultPayorID = $id;
      }
   }
   if ( not defined $gDefaultPayorID ) {
      die("No default payor setup for client");
   }

   #---------------------------------------
   # Pre-load the payee map with payee info
   #---------------------------------------
   my %gArtistPayeeMap = ();
   my %gArtistPayeeCleanMap = ();

   $sql = "SELECT artist_payee_id, name FROM artist_payee";
   $sth = $dbo->DoCmd($sql);
   while( my($id,$name) = $sth->fetchrow_array() ) {
      $name =~ s/\s*$//;
      $gArtistPayeeMap{$id} = lc $name;

      my $_cleanName = clean($name);
      $_cleanName =~ s/_*$//; # in case there's garbage at the end of the name

      $gArtistPayeeCleanMap{$id} = $_cleanName;
   }

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

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

   my %seenMap = ();
   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 $payeeID         = $row->{'*RoyaltyShare Artist Payee ID'};
      #my $payeeName       = $row->{'Artist Payee Name'};
      #my $payorID         = $row->{'*RoyaltyShare Payor ID'};
      #my $payorName       = $row->{'Payor Name'};
      #my $transactionType = $row->{'*Type of Transaction'};
      #my $amount          = $row->{'*Amount'};
      #my $transactionDate = $row->{'Transaction Date'};
      #my $checkNumber     = $row->{'Check #'};
      #my $memo            = $row->{'Memo'};

      my $payeeName       = $row->{'*Artist Payee Name'};
      my $clientAccountID = $row->{'Client Account #'} || ""; # NEW
      my $payeeID         = $row->{'RS Artist Payee ID'};
      my $payorName       = $row->{'*Payor Name'};
      my $clientPayorID   = $row->{'Client Payor #'} || ""; # NEW
      my $payorID         = $row->{'RS Payor ID'};
      my $transactionType = $row->{'*Type of Transaction'};
      my $amount          = $row->{'*Amount'};
      my $transactionDate = $row->{'*Transaction Date'};
      my $checkNumber     = $row->{'Check #'} || "";
      my $memo            = $row->{'Memo'} || "";
      my $errorCode;

      if( !$payeeName && !$payorName )
      {
         report("#### row($rowid): Line $rowid is blank -- skipping !!!!\n"); # XXX
         next;
      }

      # Remove trailing spaces
      $transactionDate =~ s/\s*$//g if ( $transactionDate );
      $transactionType =~ s/\s*$//g if ( $transactionType );

      $clientAccountID =~ s/\t//g if ( $clientAccountID );

      #$payeeName =~ s/\s*$//g if ( $payeeName and $clientID != 182 ); # XXX 5/27 - hack for MOS
      #$payeeName =~ s/\x{200b}//g if ( $payeeName );
      if ( $payeeName ) {
          $payeeName =~ s/\s*$//g if ( $clientID != 182 ); # XXX 5/27 - hack for MOS
          $payeeName =~ s/\x{200b}//g;
          $row->{'*Artist Payee Name'} = $payeeName;
      }


      if ( $payorName )
      {
         $payorName =~ s/\s*$//g;
         $payorName =~ s/^\s*//g;
         $payorName =~ s/(\r|\n)//g;
         $row->{'*Payor Name'} = $payorName; # ensure that the corrected name is output
      }

      $memo      =~ s/\s*$//g if ( $memo );
      $amount =~ s/(\r|\n)//g if ( $amount );

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


      #----------------------------
      # Make sure we have an origin
      #----------------------------
#      my $_origin = _getOrigin( $origin );
#      if ( !$origin) {
#         _appendString( $errorCode, "Missing origin");
#         ++$gExCount{missing_origin};
#      } elsif ( $_origin eq kOriginUndefined ) {
#         _appendString( $errorCode, "Invalid origin");
#         ++$gExCount{invalid_origin};
#      }

      #--------------------------------------------------------------------
      # Validate the payeeID.  If the payee name is specified, then we'll
      # compare it to what's in RPS to make sure they match.  If the name
      # is not specified, then we just ckeck to see if the payeeID exists.
      #
      # Note: unlike publisher payees, artist payees don't have the notion
      # of 'origin'.
      #--------------------------------------------------------------------

# 9/28/10 -- Make the client fill the template in properly..
#      if ( $clientID == 182 ) {
#         # MOS is using the payee.client_account_id, not the RS artistPayeeID...
#         my $sql = "SELECT artist_payee_id FROM artist_payee "
#            . "WHERE client_account_id='$payeeID'";
#         my $sth = $dbo->DoCmd($sql);
#         if ( $sth->rows == 0 )
#         {
#            die("MOS: Unable to find artistPayeeID for payee clientAccountID($payeeID)");
#
#         }
#         elsif ( $sth->rows > 0 )
#         {
#            die("MOS: Non-unique payee clientAccountID($payeeID)");
#         }
#         else
#         {
#            my($_payeeID) = $sth->fetchrow_array();
#            report("  MOS: mapped payeeID($payeeID) to rsPayeeID($_payeeID)");
#            $payeeID = $_payeeID;
#         }
#
#      }
      #------------------------------------
      # Per FB12914, payee name is required
      #------------------------------------
      if ( !$payeeName ) {
         report("_EXCEPTION: PAYEE_NAME_MISSING");
         _appendString( $errorCode, "Payee name cannot be blank");
         ++$gExCount{payee_name_missing};
      }

      #------------------------------------
      # Per FB12914, payor name is required
      #------------------------------------
      if ( !$payorName ) {
         report("_EXCEPTION: PAYOR_NAME_MISSING");
         _appendString( $errorCode, "Payor name cannot be blank");
         ++$gExCount{payor_name_missing};
      }

      if ( $payeeID ) {

         if ( exists $gArtistPayeeMap{$payeeID} ) {
            if ( $payeeName && ( lc $payeeName ne $gArtistPayeeMap{$payeeID} )  ) {

               if ( clean($payeeName) ne $gArtistPayeeCleanMap{$payeeID} ) {

                  report("  PAYEE_VALIDATE: Payee name mismatch: '$payeeName' doesn't match "
                     . "'" . $gArtistPayeeMap{$payeeID} . "' and "
                     . "'" . clean($payeeName) ."' doesn't match '". $gArtistPayeeCleanMap{$payeeID} . "'"
                  );
                  _appendString( $errorCode, "Payee name mismatch");
                  ++$gExCount{payee_name_mismatch};
               }

            }
         } else {
            report("  PAYEE_VALIDATE: PayeeID not found");
            _appendString( $errorCode, "PayeeID not found");
            ++$gExCount{payee_id_not_found};
         }
# payeeID is now optional per FB12914
#      } else {
#         report("  PAYEE_VALIDATE: Missing payeeID");
#         _appendString( $errorCode, "Missing PayeeID");
#         ++$gExCount{missing_payee_id};
      }
      else
      {
         my $sql = "SELECT artist_payee_id FROM artist_payee "
            . "WHERE name = ? ";
         my $sth;
         if ( $clientAccountID ) {
            report("  D: searching for payee '$payeeName' with clientAccountID '$clientAccountID'"); # XXX
            $sql .= "AND client_account_id = ? "; # what about client_payee_id ???
            $sth = $dbh->prepare($sql);
            $sth->execute($payeeName, $clientAccountID);
         } else {
            report("  D: searching for payee '$payeeName' by name only"); # XXX
            $sth = $dbh->prepare($sql);
            $sth->execute($payeeName);
         }
         if ( $sth->rows == 0 ) {
            report("  PAYEE_VALIDATE: payee '$payeeName' not found");
            _appendString( $errorCode, "Payee not found");
            ++$gExCount{payee_name_not_found};
         } elsif ( $sth->rows > 1 ) {
            report("  PAYEE_VALIDATE: Duplicate payee '$payeeName' detected");
            _appendString( $errorCode, "Payee not found");
            ++$gExCount{duplicate_payee_name};
         } else {
            ($payeeID) = $sth->fetchrow_array();
            report("   Found payee '$payeeName' (id=$payeeID)");
         }

      }

      #-------------------
      # Validate the payor
      #-------------------
      if ( $payorID ) {
         if ( exists $gPayorMap{$payorID} ) {
            if ( $payorName && ( lc $payorName ne $gPayorMap{$payorID} ) ) {
               report("  PAYOR_VALIDATE: Payor name mismatch: '$payorName' doesn't match "
                  . "'" . $gPayorMap{$payorID} . "'");
               _appendString( $errorCode, "Payor name mismatch");
               ++$gExCount{payor_name_mismatch};
            }
         } else {
            report("  PAYOR_VALIDATE: PayorID not found");
            _appendString( $errorCode, "PayorID not found");
            ++$gExCount{payorid_not_found};
         }
# Per FB12914, payorID is now optional
#      } else {
#report("  PAYOR_VALIDATE: Missing payorID");
#         _appendString( $errorCode, "Missing payorID");
#         ++$gExCount{missing_payorid};
      }
      else
      {
         my $sql = "SELECT payor_id FROM payor "
            . "WHERE name = ? ";
         my $sth;
         if ( $clientPayorID ) {
            $sql .= "AND client_payor_id = ? "; # what about payor.client_account_id ???
            $sth = $dbh->prepare($sql);
            $sth->execute($payorName, $clientPayorID);
         } else {
            $sth = $dbh->prepare($sql);
            $sth->execute($payorName);
         }
         if ( $sth->rows == 0 ) {
            report("  PAYOR_VALIDATE: payor '$payorName' not found");
            _appendString( $errorCode, "Payor not found");
            ++$gExCount{payor_name_not_found};
         } elsif ( $sth->rows > 1 ) {
            report("  PAYOR_VALIDATE: Duplicate payor '$payorName' detected");
            _appendString( $errorCode, "Payor not found");
            ++$gExCount{duplicate_payor_name};
         } else {
            ($payorID) = $sth->fetchrow_array();
            report("   Found payor '$payorName' (id=$payorID)");
         }

      }


      #------------------------------
      # Validate the transaction type
      #------------------------------
      my $_transactionType = _getTransactionType($transactionType);
      if ( !$transactionType ) {
report("  TRANSACTION_TYPE_VALIDATE: Missing transaction type");
         _appendString( $errorCode, "Missing transaction type");
         ++$gExCount{missing_transaction_type};
      } elsif ( !$_transactionType ) {
report("  TRANSACTION_TYPE_VALIDATE: Transaction type doesn't exist");
         _appendString( $errorCode, "Transaction type doesn't exist");
         ++$gExCount{transaction_type_doesnt_exist};
      }

      #----------------------------
      # Make sure we have an amount
      #----------------------------
      if ( !$amount ) {
report("  AMOUNT_VALIDATE: Amount not found");
         _appendString( $errorCode, "Missing amount");
         ++$gExCount{missing_amount};
      } else {
         if ( $amount !~ m/^-/ and $amount !~ m/^\d/ ) {
report("  AMOUNT_VALIDATE: Invalid Amount");
            _appendString( $errorCode, "Invalid amount format");
            ++$gExCount{invalid_amount_format};
         }
      }

      if ( $amount and $_transactionType and (
           $_transactionType == RPS::DB::Item::PendingTransaction::kTypeAdvance or
           $_transactionType == RPS::DB::Item::PendingTransaction::kTypePayment ) ) {

         # RSD-6458 Only positive amounts allowed for advance or payment.
         #
         if ( $amount < 0 ) {
            _appendString( $errorCode, "Payment/advance must be positive");
            ++$gExCount{negative_payment_advance};
         }

         #------------------------------------------------------------------------
         # Per FB10234/FB12914, 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;
      }
      
      #----------------------------------------------
      # TODO: Need a better way to deal with dates...
      #----------------------------------------------
      my $convertedDate;

      if ( $transactionDate ) {

         $convertedDate = _normalizeDate($transactionDate);
         if ( !$convertedDate ) {
            report("  DATE_VALIDATE: Date format invalid '$transactionDate'");
            _appendString( $errorCode, "Date format invalid");
            ++$gExCount{bad_transaction_date};
         } else {
             report("  DATE_VALIDATE: Converting date '$transactionDate' --> '$convertedDate'");
         }
      } else {
         report("  DATE_VALIDATE: No date found");
         _appendString( $errorCode, "Transaction date cannot be blank");
         ++$gExCount{missing_transaction_date};
      }

      # Have we seen this line already?
      #
      my $seenKey = join("\t",
         $payeeName,
         $clientAccountID,
         $payeeID,
         $payorName,
         $clientPayorID,
         $payorID,
         $transactionType,
         $amount || '',
         $transactionDate || '',
         $checkNumber,
         $memo
      );
#      report("[$rowid] seenKey=$seenKey"); # XXX
# XXX Disabling the duplicate row check; if it's in the template assume it's intentional ES 8/25/17 (FB20129)
#      if( exists $seenMap{$seenKey} )
#      {
#         _appendString( $errorCode, "Duplicate of row ". $seenMap{$seenKey} );
#         ++$gExCount{duplicate_template_transaction};
#      }
#      else
#      {
#          $seenMap{$seenKey} = $rowid;
#      }
      

      #=======================================
      # If there are any errors, we're done...
      #=======================================
      if ( $errorCode ) {
         $row->{'error-code'} = $errorCode;
         next;
      }


      #------------------------------------
      # Otherwise create the transaction !!
      #------------------------------------
      my %args = (
         artist_payee_id  => $payeeID,
         payor_id         => $payorID,
         transaction_type => $_transactionType,
         amount           => $amount,
         denomination     => $denomination,
      );

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

      _createArtistPayeeTransaction( %args );
   }

   #------------------------------------------------------------
   # 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 #####");
   print STDERR "##### S U M M A R Y #####\n";
   my $totalExceptions = 0;
   my $totalRows = (scalar @$rows);
   foreach my $c (keys %gExCount) {
      my $v = $gExCount{$c};
      printf("%30s %6d\n", $c, $v);
      printf(STDERR "%30s %6d\n", $c, $v);
      $totalExceptions += $v;
   }
   printf("%30s %s\n", " ", "-------" );
   printf(STDERR "%30s %s\n", " ", "-------" );
   printf("%30s %6d\n", "Total Exceptions", $totalExceptions );
   printf(STDERR "%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);
      printf(STDERR "%30s %d\n", $c, $v);
   }

   print STDERR ">>>\n";
   if (!$execMode)
   {
       print STDERR ">>> Test complete.  Run 'make import' to commit changes\n";
   }
   else
   {
       print STDERR ">>> Import complete.  Attach exceptions report to FogBugz case.\n";
   }
   print STDERR ">>>\n\n";

}#_processData

#-------------------------------------------------------------
# _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;
}

#
# _createTransaction
#
sub _createArtistPayeeTransaction {
   my( %args ) = @_;
   
   my $artistPayeeID    = $args{artist_payee_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 $denomination     = $args{denomination};

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

   assert($artistPayeeID);
   assert($payorID);
   assert($transactionType);
   assert($amount);
   assert($denomination);

   my $financeAccountID;
   #my $currencyCode = 'USD';  # Big assumption...
   my $currencyCode = $denomination;

   #------------------------
   # Payee Account arguments
   #------------------------
   my %paArgs = (
      payor_id => $payorID,
      artist_payee_id => $artistPayeeID,
   );

   my $paObj = RPS::DB::Item::ArtistPayeeAccount->Lookup( %paArgs );

   #---------------------------------------------------------
   # Find the finance account.  Create if it doesn't exist.
   #---------------------------------------------------------
   my $sql = "SELECT finance_account_id FROM artist_payee_account "
      . "WHERE artist_payee_id = $artistPayeeID AND payor_id = $payorID";
   my $sth = $dbo->DoCmd($sql);
   ($financeAccountID) = $sth->fetchrow_array();


   if ( !$financeAccountID ) {
      #--------------------------------------------------------------
      # payee account doesn't have a finance_account_id (or payee
      # account doesn't exist).  Before we do anything with the payee
      # account, we'll need to create a finance account.
      #--------------------------------------------------------------

      #--------------------------
      # Setup the finance account
      #--------------------------
      my %faArgs = (
         description => "account for artist payee $artistPayeeID payor $payorID",
         type_code => RPS::DB::Item::FinanceAccount::kAccountTypeHoldover,
         currency_code => $currencyCode,
      );
      my $faObj = RPS::DB::Item::FinanceAccount->Lookup(%faArgs);

      if ( !$faObj ) {
         if ( $execMode ) {
            $faObj = RPS::DB::Item::FinanceAccount->Create(%faArgs);
            $faObj->save();
            $financeAccountID = $faObj->finance_account_id;
            report("   Created finance_account $financeAccountID : ".Dumper(\%faArgs));
            ++$gCount{finance_account};


            #-------------------------------------------------------
            # If the payee account exists (but didn't have a finance
            # account), go ahead and update it here.
            #-------------------------------------------------------
            if ( $paObj ) {
               $paObj->finance_account_id($financeAccountID);
               $paObj->save();
               report("   Updated payee account with finance_account_id $financeAccountID");
            } else {
               # Create a new payee account
               $paArgs{finance_account_id} = $financeAccountID;

               $paObj = RPS::DB::Item::ArtistPayeeAccount->Create( %paArgs );
               $paObj->save();

               report("   Created artist payee account ". Dumper(\%paArgs));
               ++$gCount{artist_payee_account};

            }

         } else {
            report("   Non-exec mode, skipped finance_account for payee $artistPayeeID, payor $payorID");
         }
      } else {
         die("createTransaction -- illegal state?!  Found financeAccount when I shouldn't have: ".
            Dumper(\%faArgs) . "\n");
         #$financeAccountID = $faObj->finance_account_id;
         #report("EXISTS: finance_account $financeAccountID");
      }

   } else {
      # The payee account must exist if we're able to get a finance_account_id
      #($financeAccountID) = $sth->fetchrow_array();
      report("   Found existing financeAccountID($financeAccountID)");
   }

   if ( !$financeAccountID ) {
      report("   createArtistPayeeTransaction -- no financeAccountID, skipping payee account & transaction");
      return;
   }

   #-------------------------------
   # Setup the pending transaction.
   #-------------------------------

   $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          => $transactionType,
   );
   $ptArgs{memo}             = $memo if ( $memo );
   $ptArgs{transaction_date} = $transactionDate if ( $transactionDate );
   $ptArgs{check_number}     = $checkNumber if ( $checkNumber );

   my $ptID;
# XXX Disabling the duplicate pending transaction check; if it's in the template assume it's intentional ES 8/25/17 (FB20129)
#   my $ptObj = RPS::DB::Item::PendingTransaction->Lookup(%ptArgs);
#   if ( not defined $ptObj ) {
#      if ( $execMode ) {
#         $ptObj = RPS::DB::Item::PendingTransaction->Create(%ptArgs);
#         $ptObj->save();
#         $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));
#      }
#   } else {
#      $ptID = $ptObj->pending_transaction_id;
#      report("EXISTS: pending_transaction $ptID");
#   }

   if ( $execMode ) {
      my $ptObj = RPS::DB::Item::PendingTransaction->Create(%ptArgs);
      $ptObj->save();
      $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;

} #_createArtistPayeeTransaction

#--------------------------------------------------------------------------
# _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};

      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 $importStatus = ($errorCode) ? "__FAIL__" : "payee($payeeID)";

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

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

      report($zzbuf);
   }
}#_showExceptions

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 numericToDate
{
   my $self = shift;
   my( $ndays ) = @_;

   # Excel date is (supposedly) days since 01 Jan 1900, but have to subtract 2
   # from that number because (1) 0/1 index issue, and (2) MS wants to pretend
   # that 1900 was a leap year.
   #my @date = Add_Delta_Days(1900, 1, 1, $ndays - 2);
   my @date = Add_Delta_Days(1900, 1, 1, $ndays);
   return wantarray ? @date : sprintf("%d-%02d-%02d", @date);
}

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

1;