package Support::Implementation::LabelPayeeTransactionTemplate;
# 3/18/11: Fixed duplicated payee message per FB13583
# 4/11/11: Adjusted payee name logic; template can have "*Label Payee Name"
#  or "*Payee Name".  Importer will error out if neither is present.
# 6/14/11: Removed whitespace from amount (noticed in FB14153).
# 7/12/11: Added support for dates in the form MM/DD/YY
# 8/31/11: Added exception for duplication pending transaction
# 1/09/13: Allow '*Transaction Date' or 'Transaction Date'.
# 2/7/14: Added Excel 2007 support
# 3/7/14: Check for blank lines
# 3/20/14: Added Excel date support (TODO: move common routines elsewhere...)
# 7/18/14: Remove newlines from payee name (seen during testing of FB5484)
# 8/19/14: Remove trailing spaces from transaction type (seen during testing FB5997)
# 9/26/14: Continue processing if payee name is blank
# 4/27/17: Transaction date is optional.
# 5/11/17: Remove NBSP from amount.
# 6/12/17: Updated date normalization logic
# 1/6/20: Removed leading/trailing spaces from payee name
#
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;

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 Support::Implementation::ExcelReader;
use Support::Implementation::Excel2007Reader;
use Support::Implementation::TabDelimitedReader;

use RPS::DB::Item::LabelPayee;
use RPS::DB::Item::LabelPayeeAccount;
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;

use constant kDuplicateTransaction => 1;
use constant kNoFinanceAccount     => 2;

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 = (
   "*Label Payee Name"    => 0,  # A
   "Client Account #"     => 1,  # B
   "RS Label 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("LabelPayeeTransactionTemplate::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("LabelPayeeTransactionTemplate::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...
      _processData(\%data);
   } elsif( $self->isTabDelimited( $fileName ) ) {
      report("LabelPayeeTransactionTemplate::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{$name} = $id;
      $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 %gLabelPayeeMap = ();

   $sql = "SELECT label_payee_id, name FROM label_payee";
   $sth = $dbo->DoCmd($sql);
   while( my($id,$name) = $sth->fetchrow_array() ) {
      $gLabelPayeeMap{$id} = lc $name;
   }

   #---------------------------------
   # 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)");
   }

   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 $payeeName       = $row->{'*Label Payee Name'};    # A
      my $clientAccountID = $row->{'Client Account #'};     # B
      my $payeeID         = $row->{'RS Label Payee ID'};    # C
      my $payorName       = $row->{'*Payor Name'};          # D
      my $clientPayorID   = $row->{'Client Payor #'};       # E
      my $payorID         = $row->{'RS Payor ID'};          # F
      my $transactionType = $row->{'*Type of Transaction'}; # G
      my $amount          = $row->{'*Amount'};              # H
      my $transactionDate = $row->{'*Transaction Date'};    # I
      my $checkNumber     = $row->{'Check #'};              # J
      my $memo            = $row->{'Memo'};                 # K

      my $errorCode;

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


      $payorName =~ s/\s*$//g if ( $payorName );
      $memo      =~ s/\s*$//g if ( $memo );
      $amount    =~ s/^\s*//g if ( $amount );
      $amount    =~ s/\s*$//g if ( $amount );

      if( $amount =~ /\x{a0}/ )
      {
	 $amount =~ s/\x{a0}//g; # remove NBSP
         $row->{'*Amount'} = $amount;
      }

      $transactionType =~ s/\s*$//g if ( $transactionType );

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


      # Check for blank line
      if( !$payeeName && !$row->{'*Payee Name'} && !$payorName )
      {
          print STDERR "Skipping line $rowid\n";
          next;
      }

      # Check for alternate payee name column. -ES 4/11/11
      if ( !$payeeName ) {
         $payeeName = $row->{'*Payee Name'};
         #die("Unable to find '*Payee Name' column") if ( !$payeeName );
      }

      # Remove any embedded newlines and/or leading/trailing spaces from payee name
      #
      $payeeName =~ s/\n//g if ( $payeeName );
      $payeeName =~ s/\r//g if ( $payeeName );

      $payeeName =~ s/\s*$//g if ( $payeeName );
      $payeeName =~ s/^\s*//g if ( $payeeName );

      # Check for alternate transaction date column. -ES 1/09/13
      if ( !$transactionDate ) {
         $transactionDate = $row->{'Transaction Date'};
         #die("Unable to find '*Transaction Date' column") if ( !$transactionDate );
         report("Warning: no transaction date found");
      }

      # Apparently, both MOS (clientID 182) and Virtual (clientID 3) have
      # label payees with trailing spaces. We normally strip trailing spaces
      # from the template, unless its one of these special clients..
      #
      if ( $clientID != 3 and $clientID != 182 ) {
         $payeeName =~ s/\s*$//g if ( $payeeName );
      }

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

      #------------------------------------
      # Per FB12916, payee name is required
      #------------------------------------
      if ( !$payeeName ) {
         report("_EXCEPTION: PAYEE_NAME_MISSING");
         _appendString( $errorCode, "Payee name cannot be blank");
         ++$gExCount{payee_name_missing};
      }

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

      #--------------------------------------------------------------------
      # 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, label payees don't have the notion
      # of 'origin'.
      #--------------------------------------------------------------------
      if ( $payeeID ) {
         if ( exists $gLabelPayeeMap{$payeeID} ) {
            if ( $payeeName && ( lc $payeeName ne $gLabelPayeeMap{$payeeID} )  ) {
               report("  PAYEE_VALIDATE: Payee name mismatch: '$payeeName' doesn't match "
                  . "'" . $gLabelPayeeMap{$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 optional per FB12916
#      } else {
#report("  PAYEE_VALIDATE: Missing payeeID");
#         _appendString( $errorCode, "Missing PayeeID");
#         ++$gExCount{missing_payee_id};
      }
      else
      {
         my $sql = "SELECT label_payee_id FROM label_payee "
            . "WHERE name = ? ";
         my $sth;
         if ( $clientAccountID ) {
            $sql .= "AND client_account_id = ? "; # what about client_payee_id ???
            $sth = $dbh->prepare($sql);
            $sth->execute($payeeName, $clientAccountID);
         } else {
            $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, "Duplicate payee name");
            ++$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 FB12916 the payorID is now optional
#      } else {
#         report("  PAYOR_VALIDATE: Missing payorID");
#         _appendString( $errorCode, "Missing payorID");
#         ++$gExCount{missing_payorid};
      }
      elsif( $payorName )
      {
         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)");
         }

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

      #------------------------------
      # 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 and that it's valid
      #------------------------------------------------
      if ( !$amount ) {
          report("  AMOUNT_VALIDATE: Amount not found");
          _appendString( $errorCode, "Missing amount");
          ++$gExCount{missing_amount};
      } else {
          if ( ( $amount !~ /^-/ && $amount !~ /^\d*\.?\d*$/ ) || abs($amount) < .01 ) {
              report("  AMOUNT_VALIDATE: Invalid Amount");
              _appendString( $errorCode, "Invalid amount format");
              ++$gExCount{invalid_amount_format};
          } else {
              if ( $_transactionType && (
                   $_transactionType == RPS::DB::Item::PendingTransaction::kTypeAdvance ||
                   $_transactionType == RPS::DB::Item::PendingTransaction::kTypePayment ) ) {

                  if ( $amount < 0 ) {
                      _appendString( $errorCode, "Amount must be positive for this transaction type");
                      ++$gExCount{amount_must_be_positive};
                  } else {
                      #------------------------------------------------------------------------
                      # Per FB12916, 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{1,2})/(\d{1,2})/(\d\d\d\d)'  ) &&
              ($transactionDate !~ '(\d\d)/(\d\d)/(\d\d)'      ) &&
              ($transactionDate !~ '(\d{1,2})-(\d{1,2})-(\d\d)') &&
              ($transactionDate !~ '(\d\d\d\d)-(\d\d)-(\d\d)'  ) &&
              ($transactionDate !~ '(\d{5})'                   ) ) {
report("  DATE_VALIDATE: Date format invalid '$transactionDate'");
            _appendString( $errorCode, "Date format invalid");
            ++$gExCount{bad_transaction_date};
         } else {
            $convertedDate = _normalizeDate($transactionDate);
report("  DATE_VALIDATE: Converting date '$transactionDate' --> '$convertedDate'");
         }

      } else {
report("  DATE_VALIDATE: No date found");
      }

      # If we converted the date, use this for any error reporting
      #
      $row->{'*Transaction Date'} = $convertedDate if( $convertedDate );


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


      #------------------------------------
      # Otherwise create the transaction !!
      #------------------------------------
      my %args = (
         label_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 );

      my $st = _createLabelPayeeTransaction( %args );

      if ( $st == kDuplicateTransaction )
      {
         _appendString( $errorCode, "Duplicate transaction");
         ++$gExCount{duplicate_transaction};
      }
      elsif ( $st == kNoFinanceAccount ) # this shouldn't happen in exec mode
      {
         _appendString( $errorCode, "No finance account");
         ++$gExCount{no_finance_account};
      }

      $row->{'error-code'} = $errorCode if ( $errorCode );
   }

   #------------------------------------------------------------
   # 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 $totalExceptions = 0;
   my $totalRows = (scalar @$rows);

   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

#-------------------------------------------------------------
# _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 _createLabelPayeeTransaction {
   my( %args ) = @_;

   my $labelPayeeID     = $args{label_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};

   my $status = 0;

   # 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($labelPayeeID);
   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,
      label_payee_id => $labelPayeeID,
   );

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

   #---------------------------------------------------------
   # Find the finance account.  Create if it doesn't exist.
   #---------------------------------------------------------
   my $sql = "SELECT finance_account_id FROM label_payee_account "
      . "WHERE label_payee_id = $labelPayeeID "
      . "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 label payee $labelPayeeID 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::LabelPayeeAccount->Create( %paArgs );
               $paObj->save();

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

            }

         } else {
            report("   Non-exec mode, skipped finance_account for payee $labelPayeeID, 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("   createLabelPayeeTransaction -- no financeAccountID, skipping payee account & transaction");
      return kNoFinanceAccount;
   }

   #-------------------------------
   # 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;
   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");
      $status = kDuplicateTransaction;
   }


   return $status;

} #_createLabelPayeeTransaction

#--------------------------------------------------------------------------
# _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->{'payee-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 $importStatus = ($errorCode) ? "__FAIL__" : "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
#----------------------------------------------------------------------
sub _normalizeDate {
   my($dt) = @_;

   my($month,$day,$year);

   if ( $dt =~ '^(\d{1,2})/(\d{1,2})/(\d\d\d\d)$' )  # MM/DD/YYYY
   {
      ($month,$day,$year) = split("/",$dt);
#      print "_normalizeDate: D0: m($month) d($day) y($year)\n"; # XXX
   }
   elsif ( $dt =~ '^(\d\d)/(\d\d)/(\d\d)$' )
   {
      ($month,$day,$year) = split("/",$dt);
      if ( $year > 80 )
      {
         $year += 1900;
      }
      else
      {
         $year += 2000;
      }
#      print "_normalizeDate: D1: m($month) d($day) y($year)\n"; # XXX
   }
   elsif ( $dt =~ '^(\d{1,2})-(\d{1,2})-(\d\d)$' )
   {
      ($month,$day,$year) = split("-",$dt);
      if ( $year > 80 )
      {
         $year += 1900;
      }
      else
      {
         $year += 2000;
      }
#      print "_normalizeDate: D2: m($month) d($day) y($year)\n"; # XXX
   }
   elsif( $dt =~ '(\d\d\d\d)-(\d\d)-(\d\d)' )
   {
      ($year,$month,$day) = split("-",$dt);
#      print "_normalizeDate: D3: m($month) d($day) y($year)\n"; # XXX
   }
   elsif ( $dt =~ /^\d{5}$/ )
   {
      my $_dt = Spreadsheet::ParseExcel::Utility::ExcelFmt( "yyyy-mm-dd", $dt );
      report("_normalizeDate:  Converted msft date $dt to $_dt");
      ($year,$month,$day) = split("-", $_dt);
   }
   assert($year);
   assert($month);
   assert($day);
   return sprintf("%04d-%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 report {
   my($text, $level) = @_;
   $level = kNormal unless $level;
   if ( $level <= $gReportLevel ) {
      print $text . "\n";
   }
}

1;