package Support::Implementation::LicenseBalance;
#
# 11/13/19 -- Initial release
# 12/20/19 -- Fixed execMode issue with createFinanceAccount; added count for new entities
#
# Open Issues:
#  
# Closed Issues:
#  Q: What currency code to we use for the balance transactions?  USD?
#  A: Use the client's base currency code.
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::Client;
use Common::Date;
use Common::Util qw( clean_name_catalog trimspaces normalize_isrc);

#use RPS::License::BaseTrackLicense;
#use RPS::License::US::PublicDomain;
#use RPS::License::US::TrackLicense;
#use RPS::License::US::RingtoneLicense;
#use RPS::License::US::TrackLicenseWithLiquidationSchedule;

#use RPS::DB::Item::Album;
#use RPS::DB::Item::Track;
#use RPS::DB::Item::Master;
use RPS::DB::Item::TrackLicense;
use RPS::DB::Item::Publisher;
#use RPS::DB::Item::Payor;
#use RPS::DB::Item::Region;
#use RPS::DB::Item::RegionCountryMap;
#use RPS::DB::Item::Product;
#use RPS::DB::Item::ProductType;
use RPS::DB::Item::PendingTransaction;
#use RPS::Finance::PendingTransaction;
#use RPS::Finance::Account;

use RPS::DB::Item::RegionCountryMap;


use RPS::DB::Item::FinanceAccount;
use RPS::DB::Item::PublisherTrackCrossedLicenseAccount;

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

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

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


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 = (
    "album-title"             => 0, # A
    "album-title-version"     => 1, # B
    "catalog-no"              => 2, # C
    "album-artist"            => 3, # D
    "label-name"              => 4, # E
    "track-title"             => 5, # F
    "track-number"            => 6, # G
    "track-artist"            => 7, # H
    "track-minutes"           => 8, # I
    "track-seconds"           => 9, # J
    "isrc"                    => 10, # K
    "license-type"            => 11, # L
    "product-type"            => 12, # M
    "region"                  => 13, # N
    "share"                   => 14, # O
    "payor"                   => 15, # P
    "publisher"               => 16, # Q
    "rs-publisher-id"         => 17, # R
    "client-account-no"       => 18, # S
    "admin"                   => 19, # T
    "agent"                   => 20, # U
    "publisher-direct"        => 21, # V
    "issuer-license-id"       => 22, # W
    "crossed"                 => 23, # X
    "status"                  => 24, # Y
    "mechanical-exempt"       => 25, # Z
    "date-sent"               => 26, # AA
    "date-received"           => 27, # AB
    "date-issued"             => 28, # AC
    "term-start"              => 29, # AD
    "term-end"                => 30, # AE
    "contract-title"          => 31, # AF
    "track-cap"               => 32, # AG
    "rate-type"               => 33, # AH
    "rate-percent"            => 34, # AI
    "rate-basis"              => 35, # AJ
    "lock-date"               => 36, # AK
    "penny-rate"              => 37, # AL
    "reserve-percent"         => 38, # AM
    "digital-reserves"        => 39, # AN
    "reserve-period-1"        => 40, # AO
    "reserve-period-2"        => 41, # AP
    "reserve-period-3"        => 42, # AQ
    "reserve-period-4"        => 43, # AR
    "reserve-period-5"        => 44, # AS
    "reserve-period-6"        => 45, # AT
    "reserve-period-7"        => 46, # AU
    "reserve-period-8"        => 47, # AV
    "percent-of-sales"        => 48, # AW
    "free-goods"              => 49, # AX
    "misc-deduction"          => 50, # AY
    "comments"                => 51, # AZ
    "license-date-created"    => 52, # BA
    "license-created-by"      => 53, # BB
    "license-date-modified"   => 54, # BC
    "license-modified-by"     => 55, # BD

    "rs-license-id"           => 56, # BE
    "Import to Current License Balance"   => 57, # BF
    "Current License Balance Memo"        => 58, # BG
    "Import to Crossed License Balance"   => 59, # BH
    "Crossed License Balance Memo"        => 60, # BI

);

#-----------------------------------------------------------------------
# gOptionalColumns -- these columns are optional (e.g., they can be left
# off the template)
#-----------------------------------------------------------------------
my %gOptionalColumns = (
    "album-title"             => 0, # A
    "album-title-version"     => 1, # B
    "catalog-no"              => 2, # C
    "album-artist"            => 3, # D
    "label-name"              => 4, # E
    "track-title"             => 5, # F
    "track-number"            => 6, # G
    "track-artist"            => 7, # H
    "track-minutes"           => 8, # I
    "track-seconds"           => 9, # J
    "isrc"                    => 10, # K
    "license-type"            => 11, # L
    "product-type"            => 12, # M
    "region"                  => 13, # N
    "share"                   => 14, # O
    "payor"                   => 15, # P
    "publisher"               => 16, # Q
    "rs-publisher-id"         => 17, # R
    "client-account-no"       => 18, # S
    "admin"                   => 19, # T
    "agent"                   => 20, # U
    "publisher-direct"        => 21, # V
    "issuer-license-id"       => 22, # W
    "crossed"                 => 23, # X
    "status"                  => 24, # Y
    "mechanical-exempt"       => 25, # Z
    "date-sent"               => 26, # AA
    "date-received"           => 27, # AB
    "date-issued"             => 28, # AC
    "term-start"              => 29, # AD
    "term-end"                => 30, # AE
    "contract-title"          => 31, # AF
    "track-cap"               => 32, # AG
    "rate-type"               => 33, # AH
    "rate-percent"            => 34, # AI
    "rate-basis"              => 35, # AJ
    "lock-date"               => 36, # AK
    "penny-rate"              => 37, # AL
    "reserve-percent"         => 38, # AM
    "digital-reserves"        => 39, # AN
    "reserve-period-1"        => 40, # AO
    "reserve-period-2"        => 41, # AP
    "reserve-period-3"        => 42, # AQ
    "reserve-period-4"        => 43, # AR
    "reserve-period-5"        => 44, # AS
    "reserve-period-6"        => 45, # AT
    "reserve-period-7"        => 46, # AU
    "reserve-period-8"        => 47, # AV
    "percent-of-sales"        => 48, # AW
    "free-goods"              => 49, # AX
    "misc-deduction"          => 50, # AY
    "comments"                => 51, # AZ
    "license-date-created"    => 52, # BA
    "license-created-by"      => 53, # BB
    "license-date-modified"   => 54, # BC
    "license-modified-by"     => 55, # BD
);

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

#--------------------------------------------------------
# gHeaderDisplayed is a flag that we set if we've already
# displayed the template header during an exceptions dump
#--------------------------------------------------------
my $gHeaderDisplayed;

# Reference to SearchUtil object
#my $gSearchObj;

#--------------------------------------------------------------------------------------
# gStatRateMap - contains US stat rates (used if we're liquidating historical reserves)
#--------------------------------------------------------------------------------------
my %gStatRateMap = ();

#-------------------------------------
# gDuration - cache of track durations
#-------------------------------------
my %gDuration = ();

my $clientID;
my $execMode;

my $dbo;

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


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

   report("LicenseTemplate::_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.  Once it's been
# read, try to parse it and create track licenses.
#--------------------------------------------------------------
sub loadMemory {
   my $self = shift;

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

   $dbo = Common::RSApp::GetClientDB();

   #$gSearchObj = Support::Implementation::SearchUtil->new(
   #   clientID => $clientID,
   #);

   my $cdbo = Common::RSApp::GetCommonDB();
   #---------------------------
   # Fill the stat_rate map
   # NB: '1' ==> US Rates
   #---------------------------
   my $sql = "SELECT * FROM stat_rate "
      . "WHERE stat_rate_type_id = 1 "
      . "ORDER BY date_effective";
   my $sth = $cdbo->DoCmd($sql);
   while( my $row = $sth->fetchrow_hashref() ) {
      my $id = $row->{stat_rate_id};
      $gStatRateMap{$id} = $row;
   }

   my $fileName = $self->name;

   if( $self->isExcel2003( $fileName ) ) {
      print("LicenseTemplate::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;

      die("Unable to import license template") if (! _validateHeader() );

      #die("loadMemory: ". Dumper(\%gColumnMap) );

      # Try and parse it...
      _processData(\%data);

   } elsif( $self->isExcel2007( $fileName ) ) {
      print("LicenseTemplate::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;

      die("Unable to import license template") if (! _validateHeader() );

      #die("loadMemory: ". Dumper(\%gColumnMap) );

      # Try and parse it...
      _processData(\%data);

   } elsif( $self->isTabDelimited( $fileName ) ) {
      report("LicenseTemplate::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);
   }
}

#-----------------------------------------------------------------------
# _validateHeader checks for any missing columns required by the license
# template (we allow 'extra' columns, but the all of the core template
# columns must be present in the header.
# Returns 1 if the header is valid, undef otherwise
#-----------------------------------------------------------------------
sub _validateHeader {
   my $hasErrors;

   #----------------------------------------------------------------------------
   # actualColumns will hold the list of header columns that we actually read in
   #----------------------------------------------------------------------------
   my %actualColumns;
   foreach my $k (keys %gColumnMap) {
      my $columnName = $gColumnMap{$k};
      ++$actualColumns{$columnName};
   }

   foreach my $col (keys %gTemplateHeader) {

      next if ( exists $gOptionalColumns{$col} );

      if ( not exists $actualColumns{$col} ) {
         report("LicenseTemplate:_validateHeader: Missing column '$col'");
         $hasErrors = 1;
      }
   }

   return ($hasErrors) ? undef : 1;


}#_validateHeader

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

    #my @errorList = ();

    my $numNewLicenses = 0;
    my $numExceptions = 0;
    my $numTrackExceptions = 0;
    my $numAlbumExceptions = 0;
    my $numLockDateExceptions = 0;
    my $numPublisherNotFoundExceptions = 0;
    my $numPublisherMismatchExceptions = 0;
    my $numRateTypeExceptions = 0;
    my $numPayorExceptions = 0;
    my $numProductTypeExceptions = 0;
    my $numRateBasisExceptions = 0;
    my $numRegionExceptions = 0;
    my $numZeroShareExceptions = 0;


    #----------------------------------
    # excount keeps track of exceptions
    #----------------------------------
    my %excount = (
        license_exists          => 0,
        track_not_found         => 0,
        album_not_found         => 0,
        zero_share              => 0,
        share_warning           => 0,
        publisher_name_mismatch => 0,
        publisher_not_found     => 0,
        bad_publisherid         => 0,
        payor_not_found         => 0,
        missing_rate_type       => 0,
        missing_product_type    => 0,
        missing_rate_basis      => 0,
        missing_lock_date       => 0,
        region_not_found        => 0,
        bad_date_format         => 0,
        invalid_digital_region  => 0,
        mechanical_exempt       => 0,
        isrc_too_long           => 0,
        isrc_too_short          => 0,
        missing_publisher_name  => 0,
    );

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

    my %gExCount;

    # simple track info cache
    my %trackInfo;

    # seenLicense is used for dupe checking within the template.  Template lines
    # must have a unique album, track and publisher.
    #
    my %seenLicense;

    my $currencyCode   = Common::Client::Current()->Locale()->currencyFormat()->currencyCode();
    print "D: ### client currentCode($currencyCode) ...\n"; # XXX

    #-----------------------------
    # Loop over each row (license)
    #-----------------------------
    foreach my $row (@$rows) {

        #-----------------------------------
        # Get all of the template variables.
        #-----------------------------------
        my $rowid               = $row->{rowid};

        my $rsLicenseID         = $row->{'rs-license-id'};
        my $currentBalance      = $row->{'Import to Current License Balance'} || 0;
        my $currentBalanceMemo  = $row->{'Current License Balance Memo'}      || '';
        my $crossedBalance      = $row->{'Import to Crossed License Balance'} || 0;
        my $crossedBalanceMemo  = $row->{'Crossed License Balance Memo'}      || '';


        report("#### [$rowid] licenseID($rsLicenseID) current($currentBalance) currentMemo($currentBalanceMemo) "
            . "crossed($crossedBalance) crossedMemo($crossedBalanceMemo)"); # XXX
#        report("#### row($rowid) ".Dumper(\%$row));


        #------------------------------------------------
        # errorCode will hold zero or more error messages
        #------------------------------------------------
        my $errorCode;

        my $warningCode;

        my $license;
        my $publisherID;
        my $payorID;
        my $trackID;

        if ( !$rsLicenseID || $rsLicenseID !~ /^(\d+)$/ ) {
            report("DEBUG: missing licenseID");
            ++$gExCount{missing_licenseID};
            _appendString( $errorCode, "RS licenseID required");
        } else {
            $license = RPS::DB::Item::TrackLicense->Lookup( track_license_id => $rsLicenseID );

            if ( !$license ) {
                #die("INVALID LICENSEID($rsLicenseID)");

                report("DEBUG: invalid licenseID $rsLicenseID");
                ++$gExCount{invalid_licenseid};
                _appendString( $errorCode, "Invalid licenseID");
            } else {
                $publisherID = $license->publisher_id;
                $payorID     = $license->payor_id;
                $trackID     = $license->track_id;
            }
        }


        if ( !$crossedBalance && !$currentBalance ) {
            report("   WARNING: No crossed($crossedBalance) or current($currentBalance) balance, skipping $rowid\n");
            ++$gExCount{no_balances};
            _appendString( $errorCode, "No balances");
        }

        # skip row if any errors
        if ( $errorCode ) {
            $row->{'error-code'} = $errorCode;
            next;
        }


        #####################
        #
        #  Crossed Balance
        #
        #####################
        #
        if ( $crossedBalance ) {
            report("   >> Processing crossedBalance($crossedBalance) memo($crossedBalanceMemo)");
            #-----------------------------------------------------
            # Look for the publisher_track_crossed_license_account
            #-----------------------------------------------------
            my $crossedAccountID;
            my $financeAccountID;
            my $sql = "SELECT publisher_track_crossed_license_account_id, finance_account_id "
                . "FROM publisher_track_crossed_license_account "
                . "WHERE publisher_id=$publisherID AND track_id=$trackID AND payor_id=$payorID";
            my $sth = $dbo->DoCmd($sql);
            if ( $sth->rows == 0 )
            {
                #print("TODO: Row $rowid: No publisher_track_crossed_license_account found : sql = $sql\n");
            }
            elsif( $sth->rows > 1 )
            {
                die("ERROR: Too many publisher_track_crossed_license_account(s) found for publisher($publisherID) track($trackID) payor($payorID) !!!");
            }
            else
            {
                ($crossedAccountID, $financeAccountID) = $sth->fetchrow_array();
                report("   Using finance_account_id $financeAccountID from publisher_track_crossed_license_account_id $crossedAccountID");

                # sanity check - make sure the currency codes match
                my $financeAccount = RPS::DB::Item::FinanceAccount->Lookup( finance_account_id => $financeAccountID );
                my $fCurrencyCode = $financeAccount->currency_code;
                if ( $fCurrencyCode ne $currencyCode ) {
                    die("ERROR: finance_account_id $financeAccountID currency code '$fCurrencyCode' doesn't match client base currency '$currencyCode' !!!");
                }
            }
      

            #-----------------------------------------------------------------------------
            #
            # Create the publisher_track_crossed_license (PTCL) account; it's dependent on
            # finance_account so we create that first before creating the PTCL account.
            #
            #-----------------------------------------------------------------------------
            if ( !$financeAccountID )
            {
                #
                # ..first create the finance account
                #
                my $desc   = "account for crossed licenses, publisher $publisherID, track $trackID, payor $payorID";
                my %faArgs = (
                    type_code     => RPS::DB::Item::FinanceAccount::kAccountTypeAdvance,
                    currency_code => $currencyCode,
                    description   => $desc,
                    exec_mode     => $execMode,
                );

                # note: an ID won't be returned if exec mode isn't set
                #

                if ( $execMode )
                {
                    $financeAccountID = _createFinanceAccount( %faArgs );
                    ++$count{finance_account};

#                    report("   Created finance_account $financeAccountID : ".Dumper(\%faArgs));

                    #
                    # ..now create the publisher_track_crossed_license_account
                    #
                    my %pargs = (
                        track_id           => $trackID,
                        payor_id           => $payorID,
                        publisher_id       => $publisherID,
                        finance_account_id => $financeAccountID,
                    );
                    my $o = RPS::DB::Item::PublisherTrackCrossedLicenseAccount->Create( %pargs );
                    $o->save;
                    my $id = $o->publisher_track_crossed_license_account_id;
                    report("   Created publisher_track_crossed_license_account $id : ".Dumper(\%pargs));
                    ++$count{publisher_track_crossed_license_account};
                }
                else
                {
                   report("   Non-exec mode, skipped finance_account and publisher_transaction_crossed_license_account");
                }
            }

            #
            # ..and create the pending transaction
            #
            my %ptArgs = (
                amount             => $crossedBalance,
                finance_account_id => $financeAccountID,
                currency_code      => $currencyCode,
                type_code          => RPS::DB::Item::PendingTransaction::kTypeAdjustment,
                memo               => $crossedBalanceMemo,
                #transaction_date   => Common::Date->today(),
                exec_mode          => $execMode,
                #check_number =>
            );
            my $id = _createPendingTransaction( %ptArgs );
            ++$count{pending_transaction} if ( $execMode );

        } # crossedBalance


        #####################
        #
        #  Current Balance
        #
        #####################

        if ( $currentBalance ) {

            report("   >> Processing currentBalance($currentBalance) memo($currentBalanceMemo)");

            # Does license have a finance account?
            #
            my ($financeAccountID) = $license->finance_account_id;

            if ( !$financeAccountID ) {
                print "   No finance_account_id tied to license ...\n";

                my %faArgs = (
                    type_code     => RPS::DB::Item::FinanceAccount::kAccountTypeHoldover,
                    currency_code => $currencyCode,
                    description   => "account for track license $rsLicenseID",
                );
                $faArgs{exec_mode} = $execMode if ( $execMode );

                if ( $execMode ) {

                    $financeAccountID = _createFinanceAccount( %faArgs );
#                    print "     Created finance_account_id $financeAccountID : " . Dumper(\%faArgs) . "\n";
                    ++$count{finance_account};

                    # Update the license
                    #
                    $license->finance_account_id($financeAccountID);
                    $license->save;
                    print "     Updated license $rsLicenseID with finance_account_id $financeAccountID\n";

                } else {
                    print "     Non-exec mode: Skipped creation of finance_account_id $financeAccountID : " . Dumper(\%faArgs) . "\n";
                }
            } else {
                print "     License $rsLicenseID is Using finance_account_id $financeAccountID\n";

                # sanity check - make sure the currency codes match
                my $financeAccount = RPS::DB::Item::FinanceAccount->Lookup( finance_account_id => $financeAccountID );
                my $fCurrencyCode = $financeAccount->currency_code;
                if ( $fCurrencyCode ne $currencyCode ) {
                    die("ERROR: finance_account_id $financeAccountID currency code '$fCurrencyCode' doesn't match client base currency '$currencyCode' !!!");
                }
            }

            if ( $financeAccountID ) {

                # Create the pending transaction
                #
                my %ptArgs = (
                    amount             => $currentBalance,
                    finance_account_id => $financeAccountID,
                    currency_code      => $currencyCode,
                    type_code          => RPS::DB::Item::PendingTransaction::kTypeAdjustment,
                    memo               => $currentBalanceMemo,
                    #transaction_date   => Common::Date->today(),
                    exec_mode          => $execMode,
                    #check_number =>
                );
                my $id = _createPendingTransaction( %ptArgs );
                ++$count{pending_transaction} if ( $execMode );

            } else {
                print "  No finance_account_id, skipping pending_transaction\n";
            }

        } # current balance


        #-----------------
        # Skip empty lines
        #-----------------
        if( '' eq $rsLicenseID && '' eq $currentBalance && '' eq $crossedBalance )
        {
            report("## Row $rowid is blank ... skipping");
            next;
        }



    }# row loop

    #------------------------------------------------------------
    # Dump out the errors
    #------------------------------------------------------------
    _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 %d\n", $c, $v);
        $totalExceptions += $v;
    }

    printf("%30s %s\n", " ", "-------" );
    printf("%30s %6d\n", "Total Exceptions", $totalExceptions );
    printf("%30s %6d\n", "Total Rows", $totalRows );
    report(" ");

    if (!$execMode)
    {
        print STDERR ">>> Test complete.  Run 'make import' to commit changes\n";
    }
    else
    {
        report("sanity check: total licenses created = $numNewLicenses");
        print STDERR ">>> Import complete.  Attach exceptions report to FogBugz case.\n";
    }
    print STDERR ">>>\n\n";
    
}#_processData


sub _createFinanceAccount {
    my(%a) = @_;
    my $typeCode     = $a{type_code};
    my $currencyCode = $a{currency_code};
    my $description  = $a{description};
    my $execMode     = $a{exec_mode};
    assert($typeCode);
    assert($currencyCode);
    assert($description);

    my $financeAccountID;

    my %args = (
        currency_code => $currencyCode,
        type_code     => $typeCode,
        description   => $description,
    );


    if ( $execMode ) {
        my $o = RPS::DB::Item::FinanceAccount->Create( %args );
        $o->save();
        $financeAccountID = $o->finance_account_id;
        report("_createFinanceAccount: Created finance_acccount $financeAccountID "
            . Dumper(\%args) );
    } else {
        report("_createFinanceAccount: Non-exec: Skipped finance_acccount "
            . Dumper(\%args) );
    }
    return $financeAccountID;
}

sub _createPendingTransaction {
    my(%a) = @_;

    my $financeAccountID = $a{finance_account_id};
    my $amount           = $a{amount};
    my $typeCode         = $a{type_code};
    my $currencyCode     = $a{currency_code};
    my $memo             = $a{memo};
    #my $transactionDate  = $a{transaction_date};   # Per CB, leave this blank
    my $checkNumber      = $a{check_number};
    my $execMode         = $a{exec_mode};

    # NB: check number not supported here (yet)

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

    my $pendingTransactionID;

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

    if ( $execMode ) {

        my $o = RPS::DB::Item::PendingTransaction->Create( %args );
        $o->save();
        $pendingTransactionID = $o->pending_transaction_id;
        report("_createPendingTransaction: Created pending_transaction $pendingTransactionID "
            . Dumper(\%args) );
    } else {
        report("     Non-exec mode, skipped _createPendingTransaction : " . Dumper(\%args) );
    }
    return $pendingTransactionID;

}#_createPendingTransaction


#--------------------------------------------------------------------------
# _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
# licenseID will be stored in the import status column ("licenseID(###)"),
# 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};
      next if ( !$v ); # strip blank columns

      #----------------------------------------------------------
      # 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("EXCEPTION:\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 $warningCode = $row->{'warning-code'};
      my $licenseID = $row->{'rs-license-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};
         next if ( !$v ); # strip blank columns
         my $val = ($row->{$v}) ? $row->{$v} : '';

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

         push @obuf, $val;
      }

      my $importStatus;
      my $ecString;

      if ( $errorCode ) {
         $importStatus = "__FAIL__";
         $ecString = ($errorCode) ? $errorCode : '';
      } else {
         $importStatus = "license($licenseID)";
         $ecString = ($warningCode) ? $warningCode : '';
      }

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

      report($zzbuf);
   }
} # _showExceptions

#-------------------------------------------------------------
# _dumpError -- go through all of the TrackLicense properties
# and display the current value (if any), underlying datatype,
# and error information
#-------------------------------------------------------------
#sub _dumpError {
#   my($leader, $license) = @_;
#
#   #-------------------------------------------------------
#   # The following will show _all_ properties (not just the
#   # usual track_license info)
#   #-------------------------------------------------------
#   foreach my $k (sort { $a cmp $b } keys %$license ) {
#
#      my $v = $license->{$k};
#      my $typeOf = ref $v;
#
#      my $hasError = '';
#      if ( (ref $v) =~ /Common::FormObject::Scalar/ ||
#           ref $v eq "Common::FormObject::DateTime" ) {
#
#         my $sv = (defined $v->_getValue()) ? $v->_getValue() : '__undef__';
#         $hasError = $v->_hasError();
#         my $errFlag = $hasError ? "*** ERROR ***" : '';
#         printf("%s   %20s  %20s %s %s\n", $leader, $k, $sv, $typeOf, $errFlag);
#
#      } elsif( (ref $v) =~ /RPS::Finance::PendingTransactionList/ ) {
#
#         my $pArray = $v->getPendingTransactionArray();
#         my $pType = ref $pArray;
#         $hasError = $v->_hasError();
#         my $errFlag = $hasError ? "*** ERROR ***" : '>> OK <<';
#
#         printf("%s %s %s\n", $leader, $typeOf, $errFlag );
#         foreach my $pt (@{$pArray}) {
#            my $amt = $pt->Amount();
#            my $typeCode = $pt->TypeCode();
#            $pType = ref $pt;
#            $hasError = $pt->_hasError();
#            my $errFlag = $hasError ? "*** ERROR ***" : ' >> OK <<';
#            report("$leader    $pType: amount($amt) typeCode($typeCode) $errFlag");
#         }
#
#
#
#      } elsif( (ref $v) =~ /RPS::Finance::Account/ ) {
#         $hasError = $v->_hasError();
#         my $errFlag = $hasError ? "*** ERROR ***" : '>> OK <<';
#
#         printf("%s   %20s  %20s %s %s\n", $leader, $k, " ", $typeOf, $errFlag);
#         my $financeAccountID = $v->AccountID();
#
#         _dumpError( $leader . "      ",\%$v );
#
#      } elsif( (ref $v) =~ /RPS::License::ReserveLiquidationList/ ) {
#
#         my $reserveArray = $v->getList();
#            
#         printf("%s   %20s  %s\n%20s", $leader, $k, "@", $typeOf);
#         for( my $i=0; $i<8; $i++ ) {
#            printf("%s [%d] %d,", $leader, $i, $reserveArray->[$i]->Percent() );
#         }
#         print"\n";
#      } else {
#         my $errFlag = '';
#
#         $v = "---" if ( !$v );
#         printf("%s   %20s  %20s %s %s\n", $leader, $k, $v, $typeOf, $errFlag);
#      }
#   }
#}# _dumpError

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

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

   my $cur = $str;
   my $prefix = "$cur; $v";
   $_[0] = ($str) ? $prefix : $v;
}# _appendString

#sub _getError {
#   my($licensehash, $errormap) = @_;
#   #-------------------------------------------------------
#   # The supplied hash is assumed to be a hash of form
#   # elements.  Check each one for an error...
#   #-------------------------------------------------------
#   foreach my $k (sort { $a cmp $b } keys %$licensehash ) {
#
#      my $v = $licensehash->{$k};
#      my $typeOf = ref $v;
#
#      my $hasError = '';
#
#      if ( (ref $v) =~ /Common::FormObject::Scalar/ ||
#           (ref $v) eq "Common::FormObject::DateTime" ) {
#
#         if ( $v->_hasError() ) {
#            my %xmlParams = $v->getXMLParams();
#            my $msg = defined $xmlParams{emsg} ? $xmlParams{emsg} : "MSG_NOT_AVAILABLE";
#            my $e = $xmlParams{e};
#            $errormap->{$k} = $msg;
#         }
#      }
#   }
#
#}# _getError

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

   report("   _reportError: checking '$name' for errors");
   if ($obj && $obj->_hasError()) {

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

sub _numberTooSmall {
   my($n) = @_;
   #return if ( $n );
   return ($n && ($n < 0.0001)) ? 1 : 0;
}

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);
   return wantarray ? @date : sprintf("%d-%02d-%02d", @date);
}

1;