package Support::Implementation::CALicenseTemplate_v2;
#
# 11/13/09 -- Added support for All Digital (ALL-D) and DT product types.
# 3/12/10 -- Changed 'D' to 'DA' in _getProductTypeID()
# 4/8/10 -- Added 'royaltyshare-track-id' support.
# 7/26/10 -- Added support for Ringtone licenses per FB 12173.
# 8/17/10 -- Added code to remove trailing space from track.title_clean
#      for EGR track searches.
# 11/3/10 -- Added validation check for fractional share
# 11/15/10 -- Removed fractional share validation check (these are ok!)
# 11/16/10 -- "All Digital" and "All Physical" are not valid product types.
# 11/29/10 -- Added share range check.
# 1/12/11 -- Added missing rateTypeID check
# 1/20/11 -- Added special handling for track lookups for VP (FB13163)
#  -- Added code to deal with Excel dates
# 2/15/11 -- Added check for "rate too small".
# 2/18/11 -- Added LP5 config to list of product types.
#  -- Changed existing license messaging for PD licenses
# 4/4/11 -- Added reserve total check
# 6/23/11 -- Added RT alias for Ring product type
# 6/29/11 -- Added rate% check
# 9/20/11 -- Updated ringtone validation
# 11/1/11 -- Additional RS fixes
# 11/4/11 -- Fixed percentOfSales logic found while testing 14736
# 12/20/11 -- Added lower bound check for rate percentage (a too small
#   value caused a run to die (Case 15279)).
# 2/23/12 -- Cleaned up productType with trailing spaces
# 3/23/12 -- Cleaned up rateBasis check
# 5/17/12 -- Fixed bug in findTrack (TODO: clean this function up)
# 8/30/12 -- Look for album title using both the title and/or title_clean
#   this is to accomodate clients whose clean_name isn't correct.
# 9/19/12 -- Removed upper bound check on rate percentage
# 9/20/12 -- Use clean_name_catalog() instead of clean()
# 1/2/13 -- Added validation logic to detect region errors
# 1/15/13 -- Updated validation for licenses w/ term data
# 1/16/13 -- Adjusted track search criteria in _findTrack
# 8/12/13 -- Added check for malformed share (FB608; shares had two decimal points)
# 1/27/14 -- Added Excel 2007 support
# 2/19/14 -- Added check for balance formatting; debug code for balance logic
# 10/28/14 -- Added additional date format MM-DD-YY
# 4/2/15 -- Updated license balance logic; updated reserve error checking
# 7/20/15 -- Accept additional rate type of 'S' for RING licenses
# 7/22/15 -- Accept additional rate type of 'F' for RING licenses
# 8/27/15 -- Skip empty lines
# 5/9/16 -- Store normalized dates back in the row hash
# 5/11/16 -- Update stat rate parsing to prevent ringtone rates on non-ringtone licenses
# 9/12/16 -- Skip valid region check if region is not defined; output track mech exempt msg
#   in both exec and non-exec mode
# 9/14/16 -- Error out if the product type is 'all' anything (we need to tie reserves
#   to a specific product config)
# 11/3/16 -- Allow 'all' product type if no reserves are being released.
# 11/8/16 -- Treat 'All Products' as 'All'
# 12/1/16 -- Deal with Y2K dates
# 1/4/17 -- Accept v1-style headers
# 3/28/17 -- Deal with M/D/YYYY dates
# 5/17/17 -- Adjusted findAlbum to find exact title and title_clean
# 8/15/17 -- Check for shares < 0.1 or > 9999
# 11/1/17 -- Updated regex for all digital or all physical products (_getProductTypeID)
# 7/19/18 -- Strip out NBSP from dates and issuer licenseID
# 8/6/18 -- Added YYYY-MM-DD to normalizeDate
# 8/17/18 -- Added check for duplicate lines within the template; need to allow only 1
#  instance to import.  Any subsequent duplicates should exception out.
# 8/31/18 -- Remove CRLF from publisher name, if present
# 9/10/18 -- Removed DA/DT/RING from ALL/ALL-P dupe check (rule 4)
#  * Updated _normalizeDate to return if date can't be normalized (it is the
#  caller's responsibility to check if the date format is valid).
# 12/12/18 -- Made _findTrack less chatty
# 5/6/19 -- Allow mechanical exempt even if licenses are attached (this makes it
#  consistent with UI behavior).
# 5/23/19 -- Normalize ISRC
# 10/3/19 -- Cleaned up reserve/product error checking (no ALL-P w/ reserves)
# 10/16/19 -- Added region to the duplicate line detection logic (RSD-4655)
# 11/15/19 -- Fixed LP/CD regex (added beginning/end anchors).
#
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::Util qw( clean clean_name_catalog trimspaces normalize_isrc); # XXX Adding 'clean' for debugging

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::DB::Item::FinanceAccount;
use RPS::Finance::Account;

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

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;

use constant kAlbumNotFound       => 100;
use constant kTrackNotFound       => 101;
use constant kPublisherNotFound   => 102;
use constant kNonUniqueAlbumName  => 103;
use constant kDuplicateTrackTitle => 104;
use constant kNonUniqueCatalogNumber  => 105;
use constant kProductTypeNotFound => 99999;

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-name"                  => 0,  # A
   "catalog-#"                   => 1,  # B
   "track-name"                  => 2,  # C
   "isrc"                        => 3,  # D
   "track-mech-exempt"           => 4,  # E
   "public-domain"               => 5,  # F
   "product-type"                => 6,  # G
   "region"                      => 7,  # H
   "share-%"                     => 8,  # I
   "payor"                       => 9,  # J
   "royaltyshare-publisher-id#"  => 10, # K
   "publisher"                   => 11, # L
   "publisher-direct"            => 12, # M
   "issuer-license-id"           => 13, # N
   "status"                      => 14, # O
   "crossed"                     => 15, # P
   "license-date-sent"           => 16, # Q
   "license-date-received"       => 17, # R
   "license-date-issued"         => 18, # S
   "license-term-start"          => 19, # T
   "license-term-end"            => 20, # U
   "rate-type"                   => 21, # V
   "rate-%"                      => 22, # W
   "rate-basis"                  => 23, # X
   "lock-date"                   => 24, # Y
   "penny-rate"                  => 25, # Z
   "reserve-percent"             => 26, # AA
   "p1"                          => 27, # AB
   "p2"                          => 28, # AC
   "p3"                          => 29, # AD
   "p4"                          => 30, # AE
   "p5"                          => 31, # AF
   "p6"                          => 32, # AG
   "p7"                          => 33, # AH
   "p8"                          => 34, # AI

#   "digital-reserves"            => 35, # AJ
   "percent-of-sales"            => 36, # AK
   #"packaging" => 36,                  #
   "free-goods"                  => 37, # AL
   "misc-deduction"              => 38, # AM
   "comments"                    => 39, # AN
   "license-opening-balance"     => 40, # AO

#   "liquidated-units-sales-date" => 41, # AP
#   "p0-liquidated-units"         => 42, # AQ
#   "p1-liquidated-units"         => 43, # AR
#   "p2-liquidated-units"         => 44, # AS
#   "p3-liquidated-units"         => 45, # AT
#   "p4-liquidated-units"         => 46, # AU
#   "p5-liquidated-units"         => 45, # AV
#   "p6-liquidated-units"         => 45, # AW
#   "p7-liquidated-units"         => 45, # AX



   # optional - not required
   "royaltyshare-track-id"       => 46, # AY
   "royaltyshare-album-id"       => 47, # AZ
);

#-----------------------------------------------------------------------
# gOptionalColumns -- these columns are optional (e.g., they can be left
# off the template)
#-----------------------------------------------------------------------
my %gOptionalColumns = (
   "royaltyshare-track-id" => 1,
   "royaltyshare-album-id" => 1,
   "packaging" => 1,

   "digital-reserves"            => 35, # AJ
   "liquidated-units-sales-date" => 41, # AP
   "p0-liquidated-units"         => 42, # AQ
   "p1-liquidated-units"         => 43, # AR
   "p2-liquidated-units"         => 44, # AS
   "p3-liquidated-units"         => 45, # AT
   "p4-liquidated-units"         => 46, # AU
   "p5-liquidated-units"         => 45, # AV
   "p6-liquidated-units"         => 45, # AW
   "p7-liquidated-units"         => 45, # AX
);

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

# XXX TODO Create method for stat rate hash:
# XXX TODO    $gStatRateMap = $self->_getStatRateMap();
   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 = (
      new_licenses => 0,
   );

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

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

      #-----------------------------------
      # Get all of the template variables.
      #-----------------------------------
      my $rowid               = $row->{rowid};
      my $albumName           = $row->{'album-name'};
      my $catalogNumber       = $row->{'catalog-#'};
      my $trackName           = $row->{'track-name'};
      my $isrc                = $row->{'isrc'};
      my $trackMechExempt     = $row->{'track-mech-exempt'};
      my $publicDomain        = $row->{'public-domain'};
      my $productType         = $row->{'product-type'};
      my $region              = $row->{'region'};
      my $share               = $row->{'share-%'};
      my $payor               = $row->{'payor'};
      my $rsPublisherID       = $row->{'royaltyshare-publisher-id#'};
      my $publisherName       = $row->{'publisher'};
      my $publisherDirect     = $row->{'publisher-direct'};
      my $issuerLicenseID     = $row->{'issuer-license-id'};
      my $status              = $row->{'status'};
      my $crossed             = $row->{'crossed'};
      my $licenseDateSent     = $row->{'license-date-sent'};
      my $licenseDateReceived = $row->{'license-date-received'};
      my $licenseDateIssued   = $row->{'license-date-issued'};
      my $licenseTermStart    = $row->{'license-term-start'};
      my $licenseTermEnd      = $row->{'license-term-end'};
      my $rateType            = $row->{'rate-type'};
      my $ratePercentage      = $row->{'rate-%'};
      my $rateBasis           = $row->{'rate-basis'};
      my $lockDate            = $row->{'lock-date'};
      my $pennyRate           = $row->{'penny-rate'};
      my $reservePercent      = $row->{'reserve-percent'};
      my $p1                  = $row->{'p1'};
      my $p2                  = $row->{'p2'};
      my $p3                  = $row->{'p3'};
      my $p4                  = $row->{'p4'};
      my $p5                  = $row->{'p5'};
      my $p6                  = $row->{'p6'};
      my $p7                  = $row->{'p7'};
      my $p8                  = $row->{'p8'};

      my $digitalReserves     = $row->{'digital-reserves'};

      my $percentOfSales      = $row->{'percent-of-sales'};
      my $packaging           = $row->{'packaging'}; # XXX OBE ???
      my $freeGoods           = $row->{'free-goods'};
      my $miscDeduction       = $row->{'misc-deduction'};
      my $comments            = $row->{'comments'};
      my $balance             = $row->{'license-opening-balance'};

      my $rsTrackID           = $row->{'royaltyshare-track-id'};
      my $rsAlbumID           = $row->{'royaltyshare-album-id'};

      # New columns to allow liquidation of historical reserves TODO
      #
      my $r_salesDate         = $row->{'liquidated-units-sales-date'};
      my $r_p0_units          = $row->{'p0-liquidated-units'} || 0;
      my $r_p1_units          = $row->{'p1-liquidated-units'} || 0;
      my $r_p2_units          = $row->{'p2-liquidated-units'} || 0;
      my $r_p3_units          = $row->{'p3-liquidated-units'} || 0;
      my $r_p4_units          = $row->{'p4-liquidated-units'} || 0;
      my $r_p5_units          = $row->{'p5-liquidated-units'} || 0;
      my $r_p6_units          = $row->{'p6-liquidated-units'} || 0;
      my $r_p7_units          = $row->{'p7-liquidated-units'} || 0;


      # Check for obsolete column names
      my $altRSTrackID        = $row->{'rs-track-id'};
      die("ERROR: found column named 'rs-track-id', expecting 'royaltyshare-track-id'") if ( $altRSTrackID );

      report("#### row($rowid) ".Dumper(\%$row));
report("   D: r0($r_p0_units) r1($r_p1_units) r2($r_p2_units) r3($r_p3_units) "
   . "r4($r_p4_units) r5($r_p5_units) r6($r_p6_units) r7($r_p7_units)"); # XXX

      if( $publisherName ) {
         if ( $publisherName =~ /\r\n/ ) { # CR
             #$publisherName =~ s/\015//g;
             $publisherName =~ s/\r\n//g;
             $row->{'publisher'} = $publisherName;
         }
      }

      $publisherName =~ s/\s*$// if ( $publisherName );
      $publisherName =~ s/^\s*// if ( $publisherName );
      $albumName =~ s/\s*$// if ( $albumName );
      $albumName =~ s/^\s*// if ( $albumName );
      $isrc =~ s/\s*$// if ( $isrc );
      $isrc =~ s/^\s*// if ( $isrc );
      $isrc = normalize_isrc($isrc) if ( $isrc );
      $catalogNumber =~ s/\s*$// if ( $catalogNumber );
      $catalogNumber =~ s/^\s*// if ( $catalogNumber );
      $productType   =~ s/\s*$// if ( $productType   );
      $productType   =~ s/^\s*// if ( $productType   );

      $rateBasis           =~ s/^\s*// if ( $rateBasis       );
      $lockDate            =~ s/^\s*// if ( $lockDate        );
      $pennyRate           =~ s/^\s*// if ( $pennyRate       );
      $ratePercentage      =~ s/^\s*// if ( $ratePercentage  );
      $freeGoods           =~ s/^\s*// if ( $freeGoods       );
      $licenseDateSent     =~ s/\s*// if ( $licenseDateSent );
      $licenseDateReceived =~ s/\s*// if ( $licenseDateReceived );
      $licenseDateIssued   =~ s/\s*// if ( $licenseDateIssued );
      $licenseTermStart    =~ s/\s*// if ( $licenseTermStart );
      $licenseTermEnd      =~ s/\s*// if ( $licenseTermEnd );
      $r_salesDate         =~ s/\s*// if ( $r_salesDate );

      $issuerLicenseID     =~ s/\x{a0}*// if ( $issuerLicenseID ); # NBSP

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

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


      #----------------------------------------
      # Variables used with historical reserves
      #----------------------------------------
      my $productID;
      my $lookupDate; # used for stat rate lookups
      my $statRate=0;
      my $statRateID;
      my $effectiveRate;
      my @liquidationBuckets;


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

# Moved to around line 1466 (just before the pass/fail check)
#      #----------------
#      # Check for duplicate template entries
#      # TODO: We may need to move this further down, and base the key on the underlying RPS IDs
#      # E.g.,  albumID-catno-trackID-isrc-publisherID-productTypeID
#      # This way we can better detect duplicates (particulary with the product type ID; 
#      # e.g. "ALL-D" would technically be different than "ALLD" or "All Digital").
#      #----------------
#      my $_aname = (defined $albumName)     ? (lc $albumName)     : '';
#      my $_catno = (defined $catalogNumber) ? (lc $catalogNumber) : '';
#      my $_tname = (defined $trackName)     ? (lc $trackName)     : '';
#      my $_isrc  = (defined $isrc)          ? (lc $isrc)          : '';
#      my $_pname = (defined $publisherName) ? (lc $publisherName) : '';
#      my $_ptype = (defined $productType)   ? (lc $productType)   : '';
#
#      my $licenseKey = join("\t", lc $_aname, lc $_catno, lc $_tname, lc $_isrc, lc $_pname, lc $_ptype );
#      if( exists $seenLicense{$licenseKey} ) {
#         my $_rowid = $seenLicense{$licenseKey};
#         report("DUPE_EXCEPTION:  row $rowid is a duplicate of row $_rowid -- skipping ...");
#         _appendString( $errorCode, "Duplicate line");
#         ++$excount{duplicate_line};
#      } else {
#          $seenLicense{$licenseKey} = $rowid;
#      }

      #---------------
      # Find the track
      #---------------
      my $trackID;
      my $errFlag;

      if ( $rsTrackID ) {
         # TODO: Check the track title?
         $trackID = $rsTrackID;
      } else {

         # TODO: Add rsAlbumID support to _findTrack
         ($trackID,$errFlag) = _findTrack(
            catalog_number => $catalogNumber,
            album_name => $albumName,
            track_name => $trackName,
            isrc => $isrc,
         );
      }

      if ( $trackID ) {
         report("Found track $trackID");

         my $tObj = RPS::DB::Item::Track->Lookup( track_id => $trackID );
         $trackInfo{$trackID}{albumID} = $tObj->album_id;

         #-------------------------------------------------------------
         # If license is marked track mechanical exempt, then the track
         # will be set to not pay or accrue mechanical royalties, and
         # no licenses will be created or attached.
         #-------------------------------------------------------------
         if ( $trackMechExempt && $trackMechExempt =~ /^y/i ) {
            if ( 0 == $tObj->mechanical_exempt ) {

               # (OBE) RSD-2202: Can't mark a track exempt if it currently has a license

               # 5/6/19: The UI allows this for inactive/active licenses, so we'll
               # allow the track to be set to mechanical exempt no matter what.
               #
               my $o = RPS::DB::Item::TrackLicense->Lookup( track_id => $trackID );

               if ( $o ) {

                  report("Warning: track $trackID has license(s), setting to mechanical_exempt");

               #   report("Debug: unable to set track $trackID to mechanical_exempt; license attached");
               #   _appendString( $errorCode, "License Attached");
               #   ++$excount{mechanical_exempt_license_attached};
               #} else {
               #   if ( $execMode ) {
               #      $tObj->mechanical_exempt(1);
               #      $tObj->save();
               #   }
               #   report("Debug: set track $trackID to mechanical_exempt");
               #   _appendString( $errorCode, "Mechanical Exempt");
               #   ++$excount{mechanical_exempt};

               }

               if ( $execMode ) {
                  $tObj->mechanical_exempt(1);
                  $tObj->save();
               }
               report("Debug: set track $trackID to mechanical_exempt");
               _appendString( $errorCode, "Mechanical Exempt");
               ++$excount{mechanical_exempt};

            } else {
               report("Debug: track $trackID already mechanical_exempt");
               _appendString( $errorCode, "Already Mechanical Exempt");
               ++$excount{already_mechanical_exempt};
            }
         }

      } else {

         report("_EXCEPTION: Track not found, errFlag($errFlag)");
         #push @errorList, $row;

         if ( $isrc ) {
            if ( length($isrc) > 12 ) {
               _appendString( $errorCode, "ISRC too long");
               ++$excount{isrc_too_long};
            }
            if ( length($isrc)>0 && length($isrc) < 12 ) {
               _appendString( $errorCode, "ISRC too short");
               ++$excount{isrc_too_short};
            }
         }

         if ( $errFlag == kTrackNotFound ) {
            _appendString( $errorCode, "Track not found");
            ++$excount{track_not_found};
         }

         if ( $errFlag == kAlbumNotFound ) {
            _appendString( $errorCode, "Album not found");
            ++$excount{album_not_found};
         }

         if ( $errFlag == kDuplicateTrackTitle ) {
            _appendString( $errorCode, "Duplicate track title");
            ++$excount{duplicate_track_title};
         }

      }


      #-------------------------------------------------------------------------
      # A note about error handling...
      #
      # The TrackLicense object can have errors associated with the form
      # fields; this is to allow errors to be propagated back to the user via
      # the UI.  From an importer standpoint, it would be nice to have this
      # same ability with a cell on a given row.  In the past, a single column
      # (named "error-code") would contain a brief message indicating why a 
      # given row could not be imported.
      #
      # The metadata importer does something pretty cool -- it can generate
      # a spreadsheet of the rows containing errors, and then insert an
      # Excel comment on the cell.  Thus, cells with errors can be readily
      # identified -- they'll have commments and may have cell coloring to
      # indicate error severity.  The comment itself will indicate why the cell
      # is causing an error.
      #
      # To enable such functionality in the License importer, we'll need a way
      # to treat each cell as an object with an 'error' property.  For now, this
      # is TBD and we'll use the "error-code" column.
      #-------------------------------------------------------------------------

      my $publisherID;
      my $payorID;
      my $regionID;
      my $rateTypeID;
      my $productTypeID;
      my $rateBasisID;
      my $inactiveID;
      my $crossedID;
      my $publisherDirectID;

      my $isRingtone; # set for ringtone licenses

      my $reserveError; # set if we detect anything that would hold up historical reserves
                        # if reserves can't be imported, don't import the license

      #--------------------------------------------------------------------------
      # We need to make sure that publicDomain is really set (i.e. is set to "Y")
      #--------------------------------------------------------------------------
      #$publicDomain = "" if ( $publicDomain =~ /n/i );
      #$publicDomain = "" if ( $publicDomain && $publicDomain !~ /y/i );
      $publicDomain = undef if ( $publicDomain && $publicDomain !~ /y/i );

      if ( !$publicDomain && 
           ( !$trackMechExempt || ($trackMechExempt && $trackMechExempt !~ m/y/i )) )
      {
         my $e = (defined $trackID) ? $trackID : 'NOT_FOUND';
         report("   FYI: Non-PD license and trackID $e is not mech exempt."); # XXX

         #---------------------------------------------------------
         # Check for zero share -- the TrackLicense validate method
         # isn't picking this up properly
         #---------------------------------------------------------
         if ( !$share or $share == 0 ) {
            _appendString( $errorCode, "Zero share");
            ++$excount{zero_share};
         }

         #----------------------------------
         # Make sure the share is an integer
         #----------------------------------
#         if ( $share && ! _isInteger($share) ) {
#            _appendString( $errorCode, "Share must be integer");
#            ++$excount{invalid_share};
#         }

         #-----------------------------
         # Make sure the share is range
         #-----------------------------
         if ( $share && ( $share < 0.10 || $share > 9999 ) ) {
            print "SHARE_EXCEPTION: Share must be between 0.1 and 9999\n"; # ES 8/15/17
            _appendString( $errorCode, "Share must be between 0.1 and 9999"); # ES 8/15/17
            ++$excount{invalid_share};
         }

         #------------------------------------------
         # Make sure the share is properly formatted
         #------------------------------------------
         if ( $share && ( $share !~ /^\d*[\.]?\d*$/ ) ) {
            print "SHARE_EXCEPTION: Invalid share format ($share)\n";
            _appendString( $errorCode, "Invalid share format");
            ++$excount{invalid_share_format};
         }

         #-------------------
         # find the publisher
         #-------------------
         my $actualName;
         if ( $rsPublisherID ) {

            if ( $rsPublisherID !~ /^\d+$/ || $rsPublisherID == 0 ) {
               _appendString( $errorCode, "Bad publisherID");
               ++$excount{bad_publisherid};

            } else {

               my $e;
               my $pObj = RPS::DB::Item::Publisher->Lookup( publisher_id => $rsPublisherID );

               if ( !$pObj ) {
                  report("_EXCEPTION: publisherID($rsPublisherID) is invalid!");
                  _appendString( $errorCode, "PublisherID invalid");
                  ++$excount{publisherid_invalid};
                  ++$e;
               } else {
                  $actualName = $pObj->publisher_name;
               }

               #----------------------------------------------------------
               # _if_ a publisher name was specified, make sure it matches
               #----------------------------------------------------------
               if ( $publisherName && $actualName && ( (lc $actualName) ne (lc $publisherName) ) ) {
                  report("_EXCEPTION: name_mismatch! id($rsPublisherID) "
                     . "template($publisherName) --> actual($actualName)");
                  _appendString( $errorCode, "PublisherID name mismatch");
                  ++$excount{publisher_name_mismatch};
                  ++$e;
               }

               $publisherID = $rsPublisherID if ( !$e );

            }
         } else {

            if ( !$publisherName ) {
               _appendString( $errorCode, "Missing publisher name");
               ++$excount{missing_publisher_name};
            } else {
               $publisherID = _findPublisher(
                  publisher_name => $publisherName
               );
            }
         }

         if ( (!$publisherID) or 0 == $publisherID ) {
            if ( $publisherName ) {
               report("_EXCEPTION: publisher '$publisherName' not found");
            } else {
               report("_EXCEPTION: publisher not defined");
            }
            _appendString( $errorCode, "Publisher not found");
            ++$excount{publisher_not_found};
         }

         #---------------------------------------------------------------------
         # Get the payor.  Note: from a TrackLicense perspective, if the
         # payor is blank then the default payor (a payor with is_default set)
         # will be used.  However, the license template (1.0) specifies that
         # a payor must be specified in the template and that it must be valid.
         #---------------------------------------------------------------------
         $payorID = _findPayor( payor_name => $payor ) if ( $payor );
         if ( ! $payorID ) {
            report("_EXCEPTION: payor '$payor' not found");
            _appendString( $errorCode, "Payor not found");
            ++$excount{payor_not_found};
            if ( $clientID == 149 ) { # XXX 149 = DAYWIND
               $payorID = 1; # Force payor to default
            }
         }

         #---------------
         # Get the region
         #---------------
         if ( $region && '' ne $region ) {
            $regionID = _findRegion( name => $region );
            report(">>> Region '$region' --> regionID $regionID"); # XXX
            if ( !defined $regionID ) {
               #--------------------------------------------------------------
               # NB: the UI will default to the region with
               # "is_default_mechanical_license_region".  The license template
               # however, requires a region to be defined.
               #--------------------------------------------------------------
               _appendString( $errorCode, "Region not found");
               ++$excount{region_not_found};
            }
         } else {
            _appendString( $errorCode, "Region missing");
            ++$excount{region_missing};
         }
      
         #-------------------
         # Get the rateTypeID
         #-------------------
         $rateTypeID = _getRateType( rate_type => $rateType );
         if ( !$rateTypeID ) {
            _appendString( $errorCode, "Missing rate type");
            ++$excount{missing_rate_type};
         } else {

            #-----------------------------------------------------------
            # For full (us ring) or min stat licenses, a rate percentage
            # is required.
            # NB: Full stat and US Ring Rate licenses share the same
            # rate type ID (kRateTypeFull). -ES 9/21/11
            #-----------------------------------------------------------
            if ( $rateTypeID == RPS::DB::Item::TrackLicense::kRateTypeFull or
                 $rateTypeID == RPS::DB::Item::TrackLicense::kRateTypeMinimum )
            {
              
               if ( !$ratePercentage )
               {
                  _appendString( $errorCode, "Missing rate percentage");
                  ++$excount{missing_rate_percentage};
               }
               else
               {
                  #-----------------------------------------------------------
                  # If the rate percentage is too small, the effective rate
                  # may end up (due to rounding) as zero during a mechanical
                  # run, which in turn will cause the run to die.
                  # To prevent this, we reject any rate percentages that are
                  # less than 1 (or greater than 100).  If the former is
                  # actually being used by the client -- perhaps as a means
                  # to prevent paying out on a license -- then client services
                  # should engage the client to figure out how to best
                  # accomodate their needs.
                  #-----------------------------------------------------------

                  #if ( $ratePercentage <= 1 || $ratePercentage > 100 )
                  if ( $ratePercentage <= 1 )
                  {
                     _appendString( $errorCode, "Invalid rate percentage");
                     ++$excount{invalid_rate_percentage};
                  }
               }
            }

            #----------------------------------------------------
            # For penny rate licenses, you must have a penny rate
            #----------------------------------------------------
            if ( $rateTypeID == RPS::DB::Item::TrackLicense::kRateTypePenny ) {
               if ( !$pennyRate ) {
                  _appendString( $errorCode, "Missing penny rate");
                  ++$excount{missing_penny_rate};
               } else {
                  if ( _numberTooSmall($pennyRate) ) {
                     _appendString( $errorCode, "Penny rate too small");
                     ++$excount{penny_rate_too_small};
                  }
               }

               # Normally, you only enter a rate percentage for stat rate licenses.
               # For penny rate licenses we default this to 100 (desc track_license)
               # when the license is created.  By manually setting it here we avoid
               # waiting till the license is actually created.  This also lets us
               # calculate the effective rate is we're going to be creating historical
               # reserves to attach to this license. -ES 7/16/14
               #
               $ratePercentage = 100 if ( !$ratePercentage );
            }
         }

         #----------------------
         # Get the productTypeID
         #----------------------
         $productTypeID = _getProductTypeID( product_type => $productType );


         if ( $productType ) {
            if ( $productTypeID && kProductTypeNotFound == $productTypeID ) {
               _appendString( $errorCode, "Unrecognized product type");
               ++$excount{unrecognized_product_type};
            }
         } else {
            _appendString( $errorCode, "Missing product type");
            ++$excount{missing_product_type};
         }

         #---------------------------------------------------------------
         # 9/14/16: Check If the product type is 'all' anything.
         # We use the product type to find a specific product, thus we
         # need something tangible.
         # 11/3/16: This is only an issue if we're releasing anything.
         #---------------------------------------------------------------
# 10/3/19 - moving this below to where we try to setup historical reserves
#         if( $productType =~ /all/i &&
#             ($r_p0_units || $r_p1_units || $r_p2_units || $r_p3_units ||
#              $r_p4_units || $r_p5_units || $r_p6_units || $r_p7_units))
#         {
#            _appendString( $errorCode, "Specific product type required for reserves");
#            ++$excount{specific_product_type_required};
#         }

         #---------------------------------------------------------------
         # Sanity check -- if the license is ALL or D, then make sure the
         # region is US or CA only
         #---------------------------------------------------------------
# 9/14/16: not a valid check since we're dealing with reserves
#         if ( !$productTypeID ||
#              RPS::DB::Item::Product::kProductTypeDigital == $productTypeID ) {
#            if ( $regionID && ! _validDigitalRegion($regionID) ) {
#               _appendString( $errorCode, "Invalid digital region");
#               ++$excount{invalid_digital_region};
#            }
#         }

         #-------------------
         # Get the rate basis
         # NOTE: Rate basis isn't valid for fixed-rate licenses.
         #-------------------
         if ( $rateTypeID && $rateTypeID != RPS::DB::Item::TrackLicense::kRateTypePenny ) {

            $rateBasisID = _getRateBasis( rate_basis => $rateBasis );
            if ( !$rateBasisID ) {
               $rateBasisID = RPS::DB::Item::TrackLicense::kRateBasisSale;
               report("D[$rowid]: defaulting stat rate license to sale based");
#               _appendString( $errorCode, "Unknown rate basis");
#               ++$excount{unknown_rate_basis};
            }

         } else {
            if ( $rateBasis ) {
               # exception if the user specified a rate basis for a fixed-rate license
               _appendString( $errorCode, "Rate basis not allowed on fixed-rate license");
               ++$excount{fixed_rate_license_with_rate_basis};
            }
            else
            {
               report("D[$rowid]: defaulting penny rate license to sale based");
               $rateBasisID = RPS::DB::Item::TrackLicense::kRateBasisSale;
            }
         }

         #------------------------------------------------------------
         # Make sure we have a lock date for lock-date based licenses,
         # and that the lock date is valid.
         #------------------------------------------------------------
         if ( $rateBasisID and RPS::DB::Item::TrackLicense::kRateBasisLock == $rateBasisID and !$lockDate ) {
            _appendString( $errorCode, "Missing Lock Date");
            ++$excount{missing_lock_date};
         }
         if ( $lockDate  ) {

            #if ( $lockDate =~ m/^(\d+)$/ ) {
            #   my $_lockDate = numericToDate( $lockDate );
            #   report("  Converted msft date $lockDate to $_lockDate");
            #   $lockDate = $_lockDate;
            #}
            $lockDate = _normalizeDate($lockDate);

            if ( ! _isValidDateFmt($lockDate)) {
               _appendString( $errorCode, "Bad lock date format");
               ++$excount{bad_date_format};
            } else {
               $row->{'lock-date'} = $lockDate;
            }
         }
#report("DEBUG: line $rowid, rateTypeID($rateTypeID) pennyRate($pennyRate) ratePercentage($ratePercentage) rateBasisID($rateBasisID)");
         # Can't have a lock date with a fixed-rate license (2/28/11)
#         if( $rateBasisID && $rateBasisID == RPS::DB::Item::TrackLicense::kRateBasisLock &&
#             $rateTypeID && $rateTypeID == RPS::DB::Item::TrackLicense::kRateTypePenny ) {
#            _appendString( $errorCode, "Lock date not allowed on fixed-rate license");
#            ++$excount{lock_date_penny_rate_license};
#         }

         #-------------------------
         # Get the publisher direct
         #-------------------------
         $publisherDirectID = _getPublisherDirect( publisher_direct => $publisherDirect );

         #------------------------
         # Get the inactive status
         #------------------------
         $inactiveID = _getStatusID( inactive => $status );

         #-----------------
         # Get the cross ID
         #-----------------
         $crossedID = _getCrossedID( crossed => $crossed );


         #------------------------------------#
         #                                    #
         #  Special validation for ringtones  #
         #                                    #
         #------------------------------------#

         if ( $productTypeID &&
              RPS::DB::Item::Product::kProductTypeRingtone == $productTypeID ) {

            $isRingtone = 1;

            #-----------------------------------------
            # Make sure rate type is 'R' or 'P' or 'S'/'F'
            #
            # NB: We check the raw value vs the rateTypeID because US Ring Rate
            # and Full Stat share the same rateTypeID, however only the former
            # is valid with ringtone products. -ES 9/20/11
            #
            #
            # Relaxed to include 's' 'f' (full statutory), in addition to fixed ('p')
            # and US ringtone ('r'). -ES 7/20/15
            #-----------------------------------------
            if ( $rateType !~ /^(p|r|s|f)/i ) {
               _appendString( $errorCode, "Invalid ringtone rate type");
               ++$excount{invalid_ringtone_rate_type};
            }

            #-------------------------------------
            # Ringtones are valid only in US or CA
            #-------------------------------------
            report("WARNING: Missing regionID for region '$region' (errorCode = '$errorCode')") if ( !defined $regionID );
            if ( defined $regionID && ! _validDigitalRegion($regionID) ) {
               _appendString( $errorCode, "Invalid ringtone region");
               ++$excount{invalid_ringtone_region};
            }

            #-----------------------------------
            # Ringtone licenses can't be crossed
            #-----------------------------------
            if ( $crossedID ) {
               _appendString( $errorCode, "Can't cross ringtone license");
               ++$excount{cant_cross_ringtone};
            }

            #----------------------------------
            # Reserves not allowed on ringtones
            #----------------------------------
            if ( $reservePercent || $p1 || $p2 || $p3 || $p4 ||
                 $p5 || $p6 || $p7 || $p8 ) {
               _appendString( $errorCode, "No reserves on ringtones");
               ++$excount{no_reserves_on_ringtones};
            }

            #---------------------------------
            # Ringtones can't be public domain
            #---------------------------------
            if ( $publicDomain )
            {
               _appendString( $errorCode, "Ringtone can't be public domain");
               ++$excount{no_pd_on_ringtones};
            }

            #-------------------------------
            # Ringtones can't hava a balance
            #-------------------------------
            if ( $balance )
            {
               _appendString( $errorCode, "Ringtone license balance not supported");
               ++$excount{no_balance_on_ringtones};
            }

         }#ringtone validation
         else
         {
            if ( defined $rateType && $rateType =~ /^r$/i ) {
               _appendString( $errorCode, "Can't use ringtone rate type on non-ringtone license");
               ++$excount{ringtone_rate_on_non_ring_license};
            }
         }


         #--------------------------------------------------
         # Make sure that any dates are in the proper format
         #--------------------------------------------------
         if ( $licenseDateSent  ) {
            $licenseDateSent = _normalizeDate($licenseDateSent);
            if ( ! _isValidDateFmt($licenseDateSent)) {
               _appendString( $errorCode, "Bad date sent format");
               ++$excount{bad_date_format};
            } else {
               $row->{'license-date-sent'} = $licenseDateSent;
            }
         }

         if ( $licenseDateReceived  ) {
            $licenseDateReceived = _normalizeDate($licenseDateReceived);
            if ( ! _isValidDateFmt($licenseDateReceived)) {
               _appendString( $errorCode, "Bad date received format");
               ++$excount{bad_date_format};
            } else {
               $row->{'license-date-received'} = $licenseDateReceived;
            }
         }

         if ( $licenseDateIssued  ) {
            $licenseDateIssued = _normalizeDate($licenseDateIssued);
            if ( ! _isValidDateFmt($licenseDateIssued)) {
               _appendString( $errorCode, "Bad date issued format");
               ++$excount{bad_date_format};
            } else {
               $row->{'license-date-issued'} = $licenseDateIssued;
            }
         }

         if ( $licenseTermStart  ) {
            $licenseTermStart = _normalizeDate($licenseTermStart);
            if ( ! _isValidDateFmt($licenseTermStart)) {
               _appendString( $errorCode, "Bad term start format");
               ++$excount{bad_date_format};
            } else {
               $row->{'license-term-start'} = $licenseTermStart;
            }
         }

         if ( $licenseTermEnd  ) {
            $licenseTermEnd = _normalizeDate($licenseTermEnd);
            if ( ! _isValidDateFmt($licenseTermEnd)) {
               _appendString( $errorCode, "Bad term end format");
               ++$excount{bad_date_format};
            } else {
               $row->{'license-term-end'} = $licenseTermEnd;
            }
         }

         #----------------------------------
         # If a reserve percentage is specified then a liquidation
         # schedule is required.
         #----------------------------------
         if ( $reservePercent && !$p1 && !$p2 && !$p3 && !$p4 &&
              !$p5 && !$p6 && !$p7 && !$p8 ) {

            # For ringtone licenses, the liquidation schedule is N/A so
            # suppress the error.  FB14736 11/4/11
            #
            if ( !$isRingtone )
            {
               _appendString( $errorCode, "Liquidation Schedule cannot be blank");
               ++$excount{blank_liquidation_schedule};
            }
         }

         #-------------------------------------------------------
         # Make sure the sum of the liquidation schedule is valid
         #-------------------------------------------------------
         my $_scheduleTotal = 0;
         $_scheduleTotal += $p1 if ( $p1 );
         $_scheduleTotal += $p2 if ( $p2 );
         $_scheduleTotal += $p3 if ( $p3 );
         $_scheduleTotal += $p4 if ( $p4 );
         $_scheduleTotal += $p5 if ( $p5 );
         $_scheduleTotal += $p6 if ( $p6 );
         $_scheduleTotal += $p7 if ( $p7 );
         $_scheduleTotal += $p8 if ( $p8 );
         if ( $_scheduleTotal > 100 )
         {
            _appendString( $errorCode, "Total amount liquidated cannot exceed 100");
            ++$excount{liquidation_schedule_exceeded};
         }


         #-------------------------------------------------------
         # If digital reserves is specified, make sure it's valid
         #-------------------------------------------------------
         if ( $digitalReserves && ($digitalReserves !~ /^(y|n)$/i ) )
         {
            _appendString( $errorCode, "Digital reserves must be Y or N");
            ++$excount{invalid_digital_reserves};
         }
report("  DEBUG: digitalReserves($digitalReserves)") if( $digitalReserves ); # XXX

         #----------------------------------------------------
         # Validate liquidated units sales date, if specified.
         #----------------------------------------------------
         if ( $r_salesDate  ) {
            $r_salesDate = _normalizeDate($r_salesDate);
            if ( ! _isValidDateFmt($r_salesDate)) {
               _appendString( $errorCode, "Bad liquidation sale date");
               ++$excount{bad_liquidation_date_format};
            }
         }

         #-------------------------------------------------------
         # Units to liquidate, if present, must be whole numbers
         #-------------------------------------------------------
         if ( ($r_p0_units && $r_p0_units =~ /\./ ) ||
              ($r_p1_units && $r_p1_units =~ /\./ ) ||
              ($r_p2_units && $r_p2_units =~ /\./ ) ||
              ($r_p3_units && $r_p3_units =~ /\./ ) ||
              ($r_p4_units && $r_p4_units =~ /\./ ) ||
              ($r_p5_units && $r_p5_units =~ /\./ ) ||
              ($r_p6_units && $r_p6_units =~ /\./ ) ||
              ($r_p7_units && $r_p7_units =~ /\./ ) )
         {
            _appendString( $errorCode, "Can't liquidate fractional units");
            ++$excount{fractional_units_not_allowed};
         }


         #-------------------------------------------------------------------------
         # If we're liquidating reserves, we need to make sure that we have a valid
         # physical product to work with.
         #-------------------------------------------------------------------------

         if( $trackID && ($r_p0_units || $r_p1_units || $r_p2_units || $r_p3_units || $r_p4_units || $r_p5_units || $r_p6_units || $r_p7_units ))
         {

            if ( $productTypeID ) {

               if ( $productTypeID == RPS::DB::Item::ProductType::kAllPhysicalProducts ) {
                  # Can't specify reserves with 'all products'; need a specific product type

                  print "Need specific physical product for reserves\n";
                  _appendString( $errorCode, "Need specific physical product for reserves");
                  ++$excount{allp_with_reserves};

               } else {

                  # Using the track, find the target album-level product that we'll attach the reserve to.
                  #

                  if( RPS::DB::Item::Product::kProductTypeCD         == $productTypeID ||
                      RPS::DB::Item::Product::kProductTypeDVD        == $productTypeID ||
                      RPS::DB::Item::Product::kProductTypeCDSingle   == $productTypeID ||
                      RPS::DB::Item::Product::kProductTypeCassSingle == $productTypeID ||
                      RPS::DB::Item::Product::kProductTypeLP         == $productTypeID )
                  {
                     my $tObj = RPS::DB::Item::Track->Lookup( track_id => $trackID );
                     my $assetID = $tObj->album_id;

                     my $prod = RPS::DB::Item::Product->Lookup( asset_id => $assetID, product_type_id => $productTypeID );
                     if( !$prod )
                     {
                        _appendString( $errorCode, "No $productType product found");
                        ++$excount{missing_product};
                     }
                     else
                     {
                        $productID = $prod->product_id;
                     }
                  }
                  else
                  {
                     #die("LicenseTemplate_v2.pm: Trying to specify reserves for track $trackID with "
                     #    . "non-physical productTypeID $productTypeID"); # XXX
                     print "Non-physical reserves not allowed\n";
                     _appendString( $errorCode, "Non-physical reserves not allowed");
                     ++$excount{nonphysical_reserves};
                  }
               }
            }
            else {
               _appendString( $errorCode, "All product type not allowed with reserves");
               ++$excount{all_with_reserves};
            }


            # Grr... if historical reserves are present on the license line, then we
            # must check a bunch of other stuff before creating the license.

            my $duration = _getDuration($trackID);

            # For stat rate licenses, figure out which date we'll use for determining stat rates.
            #
            #   For a sale rate basis license we'll use the liquidated sales date, otherwise we'll
            #   use the lock date
            #
            # Note: for penny rate licenses, we'll use the supplied penny rate as the rate
            #
            if( $rateTypeID && RPS::DB::Item::TrackLicense::kRateTypePenny != $rateTypeID )
            {
                if ( $rateBasisID and RPS::DB::Item::TrackLicense::kRateBasisLock == $rateBasisID )
                {
                   $lookupDate = $lockDate;
                   print STDERR "D[$rowid]: lock date license, setting lookupDate($lookupDate) to lockDate($lockDate)\n"; # XXX
                   die("Row $rowid: Missing lockdate for historical reserves!!!") if( !$lookupDate ); # XXX
                }
                else
                {
                   if( !$r_salesDate || '' eq $r_salesDate )
                   {
#                      print STDERR "D[$rowid]: sale-based license, liquidation sale date required but not found!!!\n"; # XXX
                      _appendString( $errorCode, "Liquidation sale date required for stat rate license");
                      ++$excount{missing_sale_date};
                      ++$reserveError;
                   }
                   else
                   {
                      $lookupDate = $r_salesDate;
                      print STDERR "D[$rowid]: stat rate license, setting lookupDate($lookupDate) to salesDate($r_salesDate)\n"; # XXX
                   }
                }
            }


            # Determine the rate information based on the lookup date
            #
#            my ( $_statRateID, $rate, $minRate ) = _getStatRateID( $lookupDate );
            my ( $_statRateID, $rate, $minRate );
            ( $_statRateID, $rate, $minRate ) = _getStatRateID( $lookupDate ) if( $lookupDate && '' ne $lookupDate );
# This will cause a problem if we actually try to create a reserve (e.g., make sure you're
# catching sale date errors!!) XXX
#
            $statRateID = $_statRateID;


            if ( $rateTypeID && $rateTypeID == RPS::DB::Item::TrackLicense::kRateTypeFull )
            {
               #------------------------------------------------------------
               # Sanity check -- if we're processing a full-stat license and
               # no duration is available, exception out because we won't be
               # able to calculate the effective rate for the reserve.
               #------------------------------------------------------------
               if ( !$duration || $duration == 0 ) {
# XXX
#                  report("WARNING: license $licenseID is a full-stat license, "
#                     ."however trackID $trackID has no duration.");
                  report("WARNING: Full-stat license with track that has no duration !!!");
                  _appendString( $errorCode, "No track duration");
                  ++$excount{no_track_duration};
                  ++$reserveError;
#                  $row->{'error-code'} = $errorCode;
#                  next;
               }
               elsif ( $duration > 300 ) # five minutes
               { # five minutes
                  $statRate = $duration * $minRate;
               }
               else
               {
                  $statRate = $rate;
               }

            }
            elsif( RPS::DB::Item::TrackLicense::kRateTypeMinimum == $rateTypeID )
            {
               $statRate = $rate;
            }
            else
            {
               $statRate = $pennyRate;
            }

            #--------------------------------------------------------------------------------
            # Calculate and validate the effective rate that the reserves will be released at
            #--------------------------------------------------------------------------------
            $effectiveRate = $statRate * ($ratePercentage/100) * ($share/100);

            if( !$reserveError )
            {
               push @liquidationBuckets, $r_p0_units;
               push @liquidationBuckets, $r_p1_units;
               push @liquidationBuckets, $r_p2_units;
               push @liquidationBuckets, $r_p3_units;
               push @liquidationBuckets, $r_p4_units;
               push @liquidationBuckets, $r_p5_units;
               push @liquidationBuckets, $r_p6_units;
               push @liquidationBuckets, $r_p7_units;
            }


         }


      }# non-PD specific fields
      else
      {
         my $exempt = $trackMechExempt || "";
         my $tid    = $trackID || "NO_TRACKID";
         #report("   FYI: PD license or trackID $trackID is mech exempt: publicDomain($publicDomain) mechExempt($trackMechExempt) "); # XXX
         my $_pd = (defined $publicDomain) ? $publicDomain : '';
         report("   FYI: PD license or trackID $tid is mech exempt: publicDomain($_pd) mechExempt($exempt) "); # XXX
      }

      #-------------------------------------------------------
      # For PD licenses, the share can't be zero otherwise the
      # form validator will complain.
      #-------------------------------------------------------
      if ( $publicDomain ) {
         if ( !$share || $share == 0 ) {
            _appendString( $errorCode, "Zero PD share");
            ++$excount{zero_pd_share};
            #die("zero PD share");
         }
      }

### ZZZ XXX YOU ARE HERE
      #----------------
      # Check for duplicate template entries
      #
      # The license key is based on the underlying RPS IDs if we can find them, otherwise we'll use
      # whatever was specified in the template (this applies to album name, track name and
      # publisher).  We're just trying to detect the case where the template contains duplicated
      # licenses due to grouping.  If a license already exists in RPS, you'll get the "track already
      # licensed for this region" message during an import.
      #----------------
      
      # These variables are only used for the duplication detection logic
      my $_publisherName = (defined $publisherName) ? $publisherName : ''; # NB: only PD licenses should have a blank pname
      my $_albumID = (defined $trackID) ?  $trackInfo{$trackID}{albumID} : lc $albumName;
      my $_catno   = (defined $catalogNumber) ? (lc $catalogNumber) : '';
      my $_trackID = (defined $trackID)       ? $trackID            : lc $trackName;
      my $_isrc    = (defined $isrc)          ? (lc $isrc)          : '';
      my $_pubID   = (defined $publisherID)   ? $publisherID        : lc $_publisherName;
      my $_ptypeID = (defined $productTypeID) ? $productTypeID      : 99999;  # Placeholder for ALL

      # 10/16/19 - Adding region to the duplicate line check (RSD-4655)
      my $_regionID = (defined $regionID)     ? $regionID           : lc $region;

      my $licenseKey = join("\t", $_albumID, $_catno, $_trackID, lc $_isrc, $_pubID, $_ptypeID, $_regionID ); # RSD-4655

      # Rules for group duplication detection (see RSD-2202)
      # 1) ALL (not w/ any individual config, digital or physical, after that - that would be a dup)
      # 2) ALLP/ALL-P (not w/ any physical individual configs after that - that would be a dup)
      # 3) ALLD/ALL-D (not w/ any digital individual configs after that - that would be a dup)
      #
      # 4) Any individual config (not w/ ALL - that would be a dup)
      # 5) Any physical individual config (not w/ ALLP/ALL-P - that would be a dup)
      # 6) Any digital individual config (not w/ ALLD/ALL-D - that would be a dup)
      # NOTE: we're checking for dupes against lines that we've seen in the template.
      # We do not check against the database here.  Also, rule 4 is covered by rules
      # 5 and 6 (see below).
      #

#      print "Row: $rowid:   licenseKey = $licenseKey\n"; # XXX
      if( exists $seenLicense{$licenseKey} ) {
         my $_rowid = $seenLicense{$licenseKey};
         report("DUPE_EXCEPTION:  row $rowid is a duplicate of row $_rowid -- skipping ...");
         _appendString( $errorCode, "Duplicate line");
         ++$excount{duplicate_line};

      } elsif( $_ptypeID == 99999 ) {
#         print "Dupe Rule 1: Checking for 'ALL' dupes : ". Dumper(\%seenLicense) . "\n"; # XXX
         # 1) ALL - check if we've seen any config in the template
         #
         # E.g., "we're looking at ALL, have we seen anything non-ALL?"
         if( keys %seenLicense > 0 ) {
            foreach my $config (
               RPS::DB::Item::ProductType::kAllDigitalProducts,
               RPS::DB::Item::ProductType::kAllPhysicalProducts,
               RPS::DB::Item::Product::kProductTypeLP,
               RPS::DB::Item::Product::kProductTypeCD,
               RPS::DB::Item::Product::kProductTypeDigital,
               RPS::DB::Item::Product::kProductTypeDigitalTrack,
               RPS::DB::Item::Product::kProductTypeVHS,
               RPS::DB::Item::Product::kProductTypeCass,
               RPS::DB::Item::Product::kProductTypeEP,
               RPS::DB::Item::Product::kProductTypeDVD,
               RPS::DB::Item::Product::kProductTypeCDSingle,
               RPS::DB::Item::Product::kProductTypeCassSingle,
               RPS::DB::Item::Product::kProductTypeDVDCDSet,
               RPS::DB::Item::Product::kProductTypeDblCD,
               RPS::DB::Item::Product::kProductTypeRingtone,
               RPS::DB::Item::Product::kProductTypeLP5 ) {

               my $licenseKey = join("\t", $_albumID, $_catno, $_trackID, lc $_isrc, $_pubID, $config );

               if( exists $seenLicense{$licenseKey} ) { # dupe
                  my $_rowid = $seenLicense{$licenseKey};
                  report("DUPE_EXCEPTION:  row $rowid is a duplicate of row $_rowid -- skipping ...");
                  _appendString( $errorCode, "Duplicate line");
                  ++$excount{duplicate_line};
                  last;
               }
            }
         }
         $seenLicense{$licenseKey} = $rowid;


      } elsif( $_ptypeID == RPS::DB::Item::ProductType::kAllPhysicalProducts ) {

#         print "Dupe Rule 2: Checking for 'ALLP' dupes : ". Dumper(\%seenLicense) . "\n"; # XXX
         # 2) ALLP/ALL-P (not w/ any physical individual configs after that - that would be a dup)
         #
         # E.g., "we're looking at ALL-P, have we seen ALL or any individual physical config?"
         foreach my $config (
            99999, # ALL
            RPS::DB::Item::ProductType::kAllPhysicalProducts,
            RPS::DB::Item::Product::kProductTypeLP,
            RPS::DB::Item::Product::kProductTypeCD,
            RPS::DB::Item::Product::kProductTypeVHS,
            RPS::DB::Item::Product::kProductTypeCass,
            RPS::DB::Item::Product::kProductTypeEP,
            RPS::DB::Item::Product::kProductTypeDVD,
            RPS::DB::Item::Product::kProductTypeCDSingle,
            RPS::DB::Item::Product::kProductTypeCassSingle,
            RPS::DB::Item::Product::kProductTypeDVDCDSet,
            RPS::DB::Item::Product::kProductTypeDblCD,
            RPS::DB::Item::Product::kProductTypeLP5 ) {

            my $licenseKey = join("\t", $_albumID, $_catno, $_trackID, lc $_isrc, $_pubID, $config );

            if( exists $seenLicense{$licenseKey} ) { # dupe
               my $_rowid = $seenLicense{$licenseKey};
               report("DUPE_EXCEPTION:  row $rowid is a duplicate of row $_rowid -- skipping ...");
               _appendString( $errorCode, "Duplicate line");
               ++$excount{duplicate_line};
               last;
            }
         }
         $seenLicense{$licenseKey} = $rowid;

       } elsif( $_ptypeID == RPS::DB::Item::ProductType::kAllDigitalProducts ) {

#         print "Dupe Rule 3: Checking for 'ALLD' dupes : ". Dumper(\%seenLicense) . "\n"; # XXX
         # 3) ALLD/ALL-D (not w/ any digital individual configs after that - that would be a dup)
         #
         # E.g., "we're looking at ALL-D, have we seen ALL or any individual digital config?"
         foreach my $config (
            99999, # ALL
            RPS::DB::Item::Product::kProductTypeDigital,
            RPS::DB::Item::Product::kProductTypeDigitalTrack ) {

            my $licenseKey = join("\t", $_albumID, $_catno, $_trackID, lc $_isrc, $_pubID, $config );

            if( exists $seenLicense{$licenseKey} ) { # dupe
               my $_rowid = $seenLicense{$licenseKey};
               report("DUPE_EXCEPTION:  row $rowid is a duplicate of row $_rowid -- skipping ...");
               _appendString( $errorCode, "Duplicate line");
               ++$excount{duplicate_line};
               last;
            }
         }
          $seenLicense{$licenseKey} = $rowid;



      } elsif( $_ptypeID == RPS::DB::Item::Product::kProductTypeLP  ||
               $_ptypeID == RPS::DB::Item::Product::kProductTypeCD  ||
               #$_ptypeID == RPS::DB::Item::Product::kProductTypeDigital  ||
               #$_ptypeID == RPS::DB::Item::Product::kProductTypeDigitalTrack  ||
               $_ptypeID == RPS::DB::Item::Product::kProductTypeVHS  ||
               $_ptypeID == RPS::DB::Item::Product::kProductTypeCass  ||
               $_ptypeID == RPS::DB::Item::Product::kProductTypeEP  ||
               $_ptypeID == RPS::DB::Item::Product::kProductTypeDVD  ||
               $_ptypeID == RPS::DB::Item::Product::kProductTypeCDSingle  ||
               $_ptypeID == RPS::DB::Item::Product::kProductTypeCassSingle  ||
               $_ptypeID == RPS::DB::Item::Product::kProductTypeDVDCDSet  ||
               $_ptypeID == RPS::DB::Item::Product::kProductTypeDblCD  ||
               #$_ptypeID == RPS::DB::Item::Product::kProductTypeRingtone  ||
               $_ptypeID == RPS::DB::Item::Product::kProductTypeLP5 ) {

#         print "Dupe Rule 4: Checking for 'ALLD/ALLP/Individual' dupes with ALL: ". Dumper(\%seenLicense) . "\n"; # XXX
         # 4) Any physical individual config (not w/ ALL or ALLP - that would be a dup)
         #
         # E.g., "we're looking at a physical individual config, have we seen ALL or ALLP?"
         foreach my $config (
            99999,
            RPS::DB::Item::ProductType::kAllPhysicalProducts ) {

            my $licenseKey = join("\t", $_albumID, $_catno, $_trackID, lc $_isrc, $_pubID, $config );

            if( exists $seenLicense{$licenseKey} ) { # dupe
               my $_rowid = $seenLicense{$licenseKey};
               report("DUPE_EXCEPTION:  row $rowid is a duplicate of row $_rowid -- skipping ...");
               _appendString( $errorCode, "Duplicate line");
               ++$excount{duplicate_line};
               last;
             }
          }
          $seenLicense{$licenseKey} = $rowid;


      } elsif( $_ptypeID == RPS::DB::Item::Product::kProductTypeDigital  ||
               $_ptypeID == RPS::DB::Item::Product::kProductTypeDigitalTrack ) {
#         print "Dupe Rule 6: Checking for 'digital individual' dupes with ALL/ALL-D: ". Dumper(\%seenLicense) . "\n"; # XXX
         # 6) Any digital individual config (not w/ ALLD/ALL-D or ALL - that would be a dup)
         #
         # E.g., "we're looking at a digital individual config, have we seen ALL or ALLD?"
         #
         foreach my $config (
            99999,
            RPS::DB::Item::ProductType::kAllDigitalProducts ) {

            my $licenseKey = join("\t", $_albumID, $_catno, $_trackID, lc $_isrc, $_pubID, $config );

            if( exists $seenLicense{$licenseKey} ) { # dupe
               my $_rowid = $seenLicense{$licenseKey};
               report("DUPE_EXCEPTION:  row $rowid is a duplicate of row $_rowid -- skipping ...");
               _appendString( $errorCode, "Duplicate line");
               ++$excount{duplicate_line};
               last;
             }
          }
          $seenLicense{$licenseKey} = $rowid;
# 




      } else {
          $seenLicense{$licenseKey} = $rowid;
      }


      #------------------------------------------------------------
      # If we have any errors at this point then set the errorCode.
      #------------------------------------------------------------
      $row->{'error-code'} = $errorCode;




      #-------------------------------------------------------------------
      # Are there any exceptions that the form validation won't pickup?
      # If so, stop processing this license.
      #
      # * If we don't have a trackID then we're done (the form validator
      #   requires a trackID)
      # * If this is a regular publishing license and we don't have a
      #   publisherID then we're done.
      # * If this is a regular publishing license and we don't have a
      #   share then we're done.
      # * If this is a regular publishing license and we don't have a
      #   valid product type then we're done.
      # * If this is a regular publishing license and we're trying to import
      #   historical reserves, but there's a reserve issue.
      #-------------------------------------------------------------------

      if ( !$trackID || (!$publisherID && (!$publicDomain)) ||
           (!$publicDomain && (!$share or $share == 0) )  ||
#           (!$publicDomain && ($share && ( $share < 0.01 || $share > 9999 ) )) ||
           (!$publicDomain && ($share && ( $share < 0.1 || $share > 9999 ) )) ||
           (!$publicDomain && !$regionID )  ||
           (!$publicDomain && !$payorID )  ||
           (!$publicDomain && !$rateTypeID )  ||
           (!$publicDomain && $rateTypeID && RPS::DB::Item::TrackLicense::kRateTypePenny == $rateTypeID && !$pennyRate ) ||
           (!$publicDomain && $productTypeID && ($productTypeID == kProductTypeNotFound ) ) ||
           (!$publicDomain && $reserveError)
           )
      {
         report("WARNING: Error(s) found on line $rowid : $errorCode");
         next;
      };

      if ( $publicDomain && (!$share || $share == 0) )
      {
         report("WARNING: PD license with zero share.. skipping: $errorCode");
         next;
      }

      if ( !$publicDomain && $reservePercent && !$p1 && !$p2 && !$p3 && !$p4 &&
           !$p5 && !$p6 && !$p7 && !$p8 )
      {
         report("WARNING: Skipping line $rowid with missing liquidation schedule");
         next;
      }

      if ( $errorCode )
      {
         report("WARNING: uncaught error condition exists! errorCode: $errorCode");
         next;
      }

      #===========================================
      # Ok, go ahead and create the license object
      #===========================================

      #----------------------------------------------------------
      # Check if the license already exists.
      #----------------------------------------------------------
      my %tlArgs = (
         track_id => $trackID
      );

      #
      # The region from the template may refer to multiple regions
      # as defined by region_country_map; thus when checking for
      # existing licenses we need to check for licenses on countries
      # defined by region_country_map. -ES 8/13/10
      #

      if ( $publicDomain )
      {
         $tlArgs{type} = RPS::DB::Item::TrackLicense::kPublicDomain;
      }
      else
      {

         if ( $isRingtone )
         {
            $tlArgs{type} = RPS::DB::Item::TrackLicense::kRingtone;
         }
         else
         {
            $tlArgs{type} = RPS::DB::Item::TrackLicense::kPublishingLicense;
         }
         $tlArgs{publisher_id} = $publisherID;
         $tlArgs{region_id} = $regionID;
         $tlArgs{product_type_id} = $productTypeID if ( $productTypeID );
         #$tlArgs{payor_id} = $payorID;
      }


      # Removing this validation check... -ES 1/15/13
      #
#      my $tlObj = RPS::DB::Item::TrackLicense->Lookup( %tlArgs );
#      if ( $tlObj )
#      {
#         my $licID = $tlObj->track_license_id;
#
#         report("   WARNING: license $licID exists: ". Dumper(\%tlArgs));
#
#         if ( $publicDomain )
#         {
#            _appendString( $errorCode, "PD License Exists");
#            ++$excount{pd_license_exists};
#         }
#         else
#         {
#            _appendString( $errorCode, "License Exists");
#            ++$excount{license_exists};
#         }
#         $row->{'error-code'} = $errorCode;
#         next;
#      }


      #--------------------------------------------------------------
      # Create a RPS::License::TrackLicense object so we can validate
      # the license information using the form validation logic
      #--------------------------------------------------------------
      my %licArgs = (
         _loadSubs => 1,
         trackID => $trackID,
      );
      if ( $publicDomain )
      {
         report("PD detected");
         $licArgs{publicDomain} = 1;

         report("PD debug: " . Dumper(\%licArgs));
      }
      #...........................................................
      # Note: the object will default to kPublishingLicense if the
      # publicDomain flag is omitted during instantiation
      #...........................................................

      my $newLicense;


      my $financeAccount; # set only if there's a balance

      my $validPendingTransaction = 0;     # true if PendingTransaction is valid
      my $validPendingTransactionList = 0; # true if PendingTransactionList is valid
      my $validFinanceAccount = 0;         # true if Account is valid
      my $validReserveLiquidation = 0;     # true if ReserveLiquidation is valid

      if ( ! $publicDomain )
      {

         if ( $isRingtone )
         {
            $newLicense = RPS::License::US::RingtoneLicense->new( %licArgs );
         }
         else
         {
            $newLicense = RPS::License::US::TrackLicenseWithLiquidationSchedule->new( %licArgs );
         }

         $newLicense->Inactive($inactiveID);

         $newLicense->PublisherID($publisherID) if $publisherID;
         $newLicense->Share($share) if $share;
         $newLicense->PayorID($payorID) if $payorID;
         $newLicense->RegionID($regionID) if $regionID;
         $newLicense->RateType($rateTypeID) if $rateTypeID;

         $newLicense->PennyRate($pennyRate)
            if (  RPS::DB::Item::TrackLicense::kRateTypePenny == $rateTypeID );

         $newLicense->RatePercentage($ratePercentage)
            if (  RPS::DB::Item::TrackLicense::kRateTypePenny != $rateTypeID && $ratePercentage);

         $newLicense->ProductTypeID($productTypeID);
         $newLicense->RateBasis($rateBasisID) if ( $rateBasisID );

         $newLicense->ReservePercentage($reservePercent) if ( $reservePercent );

         if ( $rateBasisID && $rateBasisID == RPS::DB::Item::TrackLicense::kRateBasisLock ) {
            $newLicense->LockDate($lockDate);
         }

         $newLicense->IssuerLicenseID($issuerLicenseID);
         $newLicense->Comments($comments);
         $newLicense->MiscDeduction($miscDeduction);
         $newLicense->FreeGoods($freeGoods) if ( $freeGoods );

         $newLicense->DateSent($licenseDateSent) if ($licenseDateSent );

         $newLicense->DateReceived($licenseDateReceived) if ($licenseDateReceived );

         $newLicense->DateIssued($licenseDateIssued) if ($licenseDateIssued );

         $newLicense->TermStart($licenseTermStart) if ($licenseTermStart );

         $newLicense->TermEnd($licenseTermEnd) if ($licenseTermEnd );

         $newLicense->CrossCollateralized($crossedID);
         $newLicense->PublisherDirect($publisherDirectID);

         $newLicense->PercentageOfSales($percentOfSales) if ( $percentOfSales );

         $newLicense->DigitalReservesEnabled(1) if ( $digitalReserves && $digitalReserves =~ /^y$/i );

         #-------------------------
         # Set the reserve schedule
         #-------------------------
         if ( !$isRingtone )
         {
            my $reserveLiquidationList = $newLicense->{ReserveLiquidationList};
            my $reserveArray = $reserveLiquidationList->getList();

            _setReserve(1,$p1,\%{$reserveArray->[0]} );
            _setReserve(2,$p2,\%{$reserveArray->[1]} );
            _setReserve(3,$p3,\%{$reserveArray->[2]} );
            _setReserve(4,$p4,\%{$reserveArray->[3]} );
            _setReserve(5,$p5,\%{$reserveArray->[4]} );
            _setReserve(6,$p6,\%{$reserveArray->[5]} );
            _setReserve(7,$p7,\%{$reserveArray->[6]} );
            _setReserve(8,$p8,\%{$reserveArray->[7]} );
            $validReserveLiquidation = $reserveLiquidationList->validate();
         }

         #-----------------------------------------------------------
         # If there's a balance, we need to setup the finance account
         #-----------------------------------------------------------
         if ( $balance )
         {
            $financeAccount = $newLicense->{FinanceAccount};
            $financeAccount->TypeCode(
               RPS::DB::Item::FinanceAccount::kAccountTypeHoldover
            );
            $financeAccount->CurrencyCode('USD');

            my $ptList = $financeAccount->{PendingTransactionList};

# TODO: hack... if PendingTransactionList isn't defined, let's create it here...
#
            if( not defined $ptList ) # XXX XXX XXX
            {
                report("PendingTransactionList _not_ defined ... creating it");
                $financeAccount->{PendingTransactionList} = RPS::Finance::PendingTransactionList->new(
                    accountID => $financeAccount->AccountID(),
                    startDate => $financeAccount->{_startDate},
                    endDate => $financeAccount->{_endDate},
                    showPendingPaymentsOnly => $financeAccount->{_showPendingPaymentsOnly},
                );
                $ptList = $financeAccount->{PendingTransactionList};
            }
die("PendingTransactionList _not_ defined".  Dumper( \%$financeAccount )) if( not defined $ptList ); # XXX XXX should not happen
#            my $ptArray = $ptList->getPendingTransactionArray();

# XXX In Case 3442, there was a balance (albeit malformed) that caused this
# XXX section of code to execute.  For some reason ptList came back empty
# XXX so we got an error when trying to invoke getPendingTransactionArray.
# XXX
# XXX  >>> The PendingTransactionList _should_ have a PendingTransactionArray
# XXX      tied to it.
report("DEBUG: financeAccount: ". Dumper( \%$financeAccount ) ); # XXX
#
# XXX ptArray is supposed to be defined within the finance account object (RPS::Finance::Account)
#   but for some bizarre reason it's not being set when the finance account is created.
#   So, we create the pending transaction but it never makes it onto the pending transaction list
#   (ptArray) because well, we're not getting the reference $financeAccount->{PendingTransactionList}
#   What we're trying to do is create a pending transaction and add it to the
#   finance account's pending transaction list...
#
#   If I'm understanding RPS::Finance::Account correctly, PendingTransactionList should be an
#   empty array for a new Account object.
#   UPDATE: loadSubs is 0, so Account isn't creating the PendingTransactionList (see Account->_initSubs).
#   UPDATE: TrackLicenseWithLiquidationSchedule.pm creates Account object w/ loadSubs => 0
#           This causes a fatal problem when we try to save the new license object:
#           When saving the license, the Account object itself is saved.  In Account->save, once an
#           finance_account_id is defined, it tries to apply the account id to all of the underlying
#           pending transactions:
#
#              if (! $accountID)
#              {
#                  $accountID = $dbObj->finance_account_id();
#                  $self->{PendingTransactionList}->assignAccountID($accountID, $dbObj->currency_code);
#              }
#
#              $self->{PendingTransactionList}->save();
#
#           !! assignAccountID dies because PendingTransactionList was never created in the first place.
#
#            my $ptArray = (defined $ptList) ? $ptList->getPendingTransactionArray() : [ ] ; # XXX - debug
            my $ptArray = $ptList->getPendingTransactionArray(); # XXX - TEST
            report("ptArray is empty") if ( not defined $ptArray->[0] );

            #..................................................................
            # Note: passing the args as shown below has no effect. The 'amount'
            # and 'type_code' must be set after instantiation.
            #
            # Meanwhile, the following "new" call will generate a warning in
            # RPS::Finance::PendingTransaction.pm due to type_code not being
            # set.  We manually set values immediately after instantiation so
            # it should all be good.
            #..................................................................
            my $ptObj = RPS::Finance::PendingTransaction->new(
               amount => $balance,
               type_code => RPS::DB::Item::PendingTransaction::kTypeAdjustment,
            );

            $ptObj->Amount($balance);
            $ptObj->TypeCode( RPS::DB::Item::PendingTransaction::kTypeAdjustment );
            $ptObj->CurrencyCode('USD');
            $ptObj->Memo("Opening Balance");
            push @{$ptArray}, $ptObj;

            #-----------------------------
            # Validate the finance objects
            #-----------------------------

            # TODO: if an object won't validate, we need to find the error
            # and then add it into the errorCode variable
            $validFinanceAccount = $financeAccount->validate();
            $validPendingTransactionList = $ptList->validate();
            $validPendingTransaction = $ptObj->validate();


            report("DEBUG: Checking if ptArray is defined...");
            if ( not defined $ptArray->[0] )
            {
               report("ptArray is empty");
            }
            else
            {
               my $num = @$ptArray;
               report("ptArray is not empty, has $num element(s) ");
               foreach my $p (@{$ptArray})
               {
                  die("   amount not defined? ". ref $p )
                     if ( not defined $p->Amount() ); # XXX
                  my $amt = $p->Amount();

                  report("   $amt");
               }
            }
         }
         else
         {
            #-----------------------------------------------------------
            # This license doesn't have a balance, but we need to set
            # the following validation flags so that we don't mistakenly
            # think the license isn't valid
            #-----------------------------------------------------------
            $validFinanceAccount = 1;
            $validPendingTransactionList = 1;
            $validPendingTransaction = 1;
         }

      }
      else
      {
         #...............................................................
         # This doesn't work -- the publicDomain flag _must_ be set when
         # creating the TrackLicense object in order for it to be flagged
         # as PD.  If we leave out the publicDomain flag at creation and
         # then try to use the "Type" modifier to set the license as PD,
         # the TrackLicense validator tries to use the non-PD validation
         # logic to examine the license and of course it fails since the
         # PD license is missing a bunch of required fields.
         #...............................................................
         # $newLicense->Type(RPS::DB::Item::TrackLicense::kPublicDomain);

         $newLicense = RPS::License::US::PublicDomain->new( %licArgs );

         # Set the share.  It must be defined and > 0.
         assert($share);
         $newLicense->Share($share);

         #----------------------------------------------------------------
         # Even though this is a PD license, we need to set the following
         # validation flags so that we don't mistakenly flag the license
         # as invalid
         #----------------------------------------------------------------
         $validFinanceAccount = 1;
         $validPendingTransactionList = 1;
         $validPendingTransaction = 1;
      }

      #--------------------------------------------------------------------
      # In order for the license to validate properly, any sub-objects must
      # also validate successfully.
      #--------------------------------------------------------------------
      
      report("DEBUG: ReserveLiquidation validation:  $validReserveLiquidation");

      report("DEBUG: finance validation:  account($validFinanceAccount) "
         . "ptList($validPendingTransactionList) pt($validPendingTransaction)");

      my $validLicense = $newLicense->validate();

      #-----------------------------------------------------------------------
      # If the license didn't validate, see if we can get a meaningful message
      # from the form element.
      # If the _only_ error is a share exception, then we'll let the license in
      # _if_ the share is over 100%.  Per CD, this is to accomodate licenses
      # where the client intentionally wants to license over a 100%.
      #-----------------------------------------------------------------------
      my %errorMap;
      _getError(\%$newLicense, \%errorMap);
      if ( (keys %errorMap) == 1 )
      {
         report("Share error: ".  $newLicense->Share() ) if (exists $errorMap{Share});
         if (exists $errorMap{Share} && $newLicense->Share() > 100 ) {
            $validLicense = 1; # overlook this error...
         }
         else # XXX
         { # XXX
            print "DEBUG: errorMap: ". Dumper(\%errorMap) . "\n"; # XXX 
         } # XXX
      }


      if ( $validLicense && $validFinanceAccount && $validPendingTransactionList &&
           $validPendingTransaction )
      {
         report("#### VALID ####");

         # sanity check - if we're going to be creating reserves make sure we have a product_id XXX ARCHIE
         if( $r_p0_units || $r_p1_units || $r_p2_units || $r_p3_units || $r_p4_units ||
             $r_p5_units || $r_p6_units || $r_p7_units ) {
             die("ERROR: liquidation units tected but product_id is NOT set !!!") if ( !$productID );
         }

         if ( $execMode )
         {

            #----------------------------------------------------------
            # Everything is valid, so go ahead and save off the objects
            #----------------------------------------------------------
            if ( $financeAccount )
            {
               $financeAccount->save(); # saves finance_account & pending_transaction
               my $financeAccountID = $financeAccount->AccountID();
               $newLicense->FinanceAccountID($financeAccountID);
            }
            
            $newLicense->save(); # saves track_license and reserve_liquidation
            ++$count{new_licenses};
            ++$numNewLicenses;

            my $trackLicenseID = $newLicense->TrackLicenseID();

            #-------------------------------------------------------------
            # If we let this license in with a share exception, flag it so
            # it's visible in the import report.
            #-------------------------------------------------------------
            if ( exists $errorMap{Share} )
            {
               _appendString( $warningCode, "Share warning");
               ++$excount{share_warning};
               $row->{'warning-code'} = $warningCode;
            }

            #----------------------------------------------------------------
            # The finance account (if any) and track license both know about
            # each other. 
            #
            # After creating the license with the financeAccountID info, we
            # need to update the financeAccount object with the license info.
            #----------------------------------------------------------------
            if ( $financeAccount ) {
               $financeAccount->Description("advance account for license $trackLicenseID");
               $financeAccount->save();
            }

            $row->{'rs-license-id'} = $trackLicenseID;
            report("   rowid($rowid): Created license $trackLicenseID");



# ZEEE
            # TODO: Setup reserves to liquidate
            # TODO: Setup reserves to liquidate
            # TODO: Setup reserves to liquidate
report("   D(after license create): r0($r_p0_units) r1($r_p1_units) r2($r_p2_units) r3($r_p3_units) "
   . "r4($r_p4_units) r5($r_p5_units) r6($r_p6_units) r7($r_p7_units), rateBasis($rateBasisID)"); # XXX
            if( $r_p0_units || $r_p1_units || $r_p2_units || $r_p3_units || $r_p4_units || $r_p5_units || $r_p6_units || $r_p7_units )
            {
               report("### Creating historical reserve entries for license $trackLicenseID..."); # XXX

               #------------------------------------------------------
               # reserveList will contain a list of reserveIDs created
               #------------------------------------------------------
               my @reserveList;
               my $per=1;
               foreach my $units (@liquidationBuckets) {
                  if ( $units ) {
                     my $reserveID = _createReserve(
                        license_id     => $trackLicenseID,
                        product_id     => $productID,
                        effective_rate => $effectiveRate,
                        units          => $liquidationBuckets[$per - 1],
                        period         => $per,
                        stat_rate_id   => $statRateID,
                        rate_basis     => $rateBasisID,
                     );
                     push @reserveList, $reserveID if ( $reserveID );

                  }
                  ++$per;
               }
               my $zzReserveList = join(",", @reserveList);
               report("    ### Created reserves: $zzReserveList"); # XXX

            }
            else
            {
               report("### No historical reserve data -- skipping"); # XXX
            }



         }
      }
      else
      {
         #-------------------------------------------
         # This whole section needs to be revamped...
         #-------------------------------------------
#         report("INVALID, regionID($regionID) pubID($publisherID) ptype($productTypeID): ". Dumper(\%errorMap) );
         report("INVALID, regionID(". ($regionID||'') . ") pubID(". ($publisherID||'') . ") ptype(". ($productTypeID||'') . "): ". Dumper(\%errorMap) );

         report("    validLicense($validLicense) validFinanceAccount($validFinanceAccount) "
            . "validPendingTransactionList($validPendingTransactionList) "
            . "validPendingTransaction($validPendingTransaction )");

         if ( $errorMap{RegionID} )
         {
#            report("INVALID, region error with license!");
#            _appendString( $errorCode, "Region error");
#            ++$excount{region_error};
#            $row->{'error-code'} = $errorCode;

            # If we get here, validate the license using the region logic
            # from RPS/License/BaseTrackLicense.pm.  This will allow us
            # to properly get the reason why the region isn't valid.
            # Note that we replace $self with $newLicense, and we skip
            # the form-related data.

# XXX Start copy from RPS/License/BaseTrackLicense.pm XXX

#            my $countryCodeMap = $self->_getCountryCodeMapForValidation();
            my $countryCodeMap = $newLicense->_getCountryCodeMapForValidation();

            # Make sure _our_ region doesn't have any of these countries in it.
            #
#            my $ccMapItems = RPS::DB::Item::RegionCountryMap->GetByRegionID($self->RegionID());
            my $ccMapItems = RPS::DB::Item::RegionCountryMap->GetByRegionID($newLicense->RegionID());
            while (my $mapItem = $ccMapItems->next())
            {
                if ($countryCodeMap->{$mapItem->country_code})
                {
                    print "DEBUG: TrackLicense::validate - this track, publisher, and product type is already licensed on country code " . $mapItem->country_code . "\n";
#                    $self->{RegionID}->setError(Common::FormObject::kErrFieldInvalid);
#                    $valid = 0;
                    _appendString( $errorCode, "Track, publisher, and product type already licensed on country code " . $mapItem->country_code);
                    ++$excount{region_error};
                    $row->{'error-code'} = $errorCode;
                    last;
                }
            }

# XXX End copy from RPS/License/BaseTrackLicense.pm XXX



         }
         elsif(exists $errorMap{Share} && ($newLicense->Share() < 0.1 or $newLicense->Share() > 9999 )) {
            _appendString( $errorCode, "Share must be between 0.1 and 9999");
            ++$excount{share_error};
            $row->{'error-code'} = $errorCode;
         }
         else
         {
            # If you get here, figure out what went wrong
            die("ERROR: RPS license validation failed but license importer didn't recognize it");
         }
         $row->{'error-code'} = $errorCode if($errorCode); # XXX XXX

      }

   }# row loop

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

   #-----------------------
   # Show import statistics
   #-----------------------
   report("##### S U M M A R Y #####");
   print STDERR "##### S U M M A R Y #####\n";
   my $totalExceptions = 0;
   my $totalRows = (scalar @$rows);

   foreach my $c (keys %excount) {
      my $v = $excount{$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 %count) {
      my $v = $count{$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
   {
       report("sanity check: total licenses created = $numNewLicenses");
       print STDERR ">>> Import complete.  Attach exceptions report to FogBugz case.\n";
   }
   print STDERR ">>>\n\n";
   
}#_processData

sub _getStatRateID {
   my ( $t_date ) = @_;
   assert($t_date);

   my %rateTypeMap = (
      1 => 'US',
      2 => 'CA',
      3 => 'RINGTONE',
   );

   my($actualStatRateID, $actualRate, $actualMinuteRate) = ("", "", "");

   $t_date =~ s/-//g if ( $t_date =~ m/-/ );
   $t_date =~ s/\///g if ( $t_date =~ m/\// );

   # Check if date is in the form YYYYMM.  Force it to YYYYMM01 if so.
   $t_date .= "01" if ( length($t_date) == 6 );

   # Check if date is in the form YYYY.  Force it to YYYY0101 if so.
   $t_date .= "0101" if ( length($t_date) == 4 );

report("DEBUG: _getStatRateID: t_date($t_date)");

   foreach my $statRateID (sort{$a <=> $b} keys %gStatRateMap) {
      my $row = $gStatRateMap{$statRateID};

      my $rateType   = $row->{stat_rate_type_id};
      my $effDate    = $row->{date_effective};
      my $rate       = $row->{rate};
      my $minRate    = $row->{minute_rate};
      my $sRateType  = $rateTypeMap{$rateType};

      my $dateInfo = "";
      $effDate =~ s/\-//g;

      if ( $t_date > $effDate ) {
         $actualStatRateID = $statRateID;
         $actualRate = $rate;
         $actualMinuteRate = $minRate;
         $dateInfo = "***"
      }

      report("  statRateID($statRateID) rate($rate) minRate($minRate) "
         . "dateEffective($effDate) type($sRateType) $dateInfo");
   }

   die("_getStatRateID: Unable to find stat_rate_id for date '$t_date'")
      if ( !$actualStatRateID );

   return ($actualStatRateID, $actualRate, $actualMinuteRate);
}

#------------------------------------------------
# _getDuration -- returns the duration in seconds
#------------------------------------------------
sub _getDuration {
   my($trackID) = @_;

   if( ! exists $gDuration{$trackID} )
   {
      my $d;
      my $sql = qq(
         SELECT m.duration
         FROM track t JOIN master m USING(master_id)
         WHERE t.track_id=$trackID
      );
      my $sth = $dbo->DoCmd($sql);
      ($d) = $sth->fetchrow_array();
      $gDuration{$trackID} = $d;
   }
   return $gDuration{$trackID};
}

#--------------------------------
# Create a license_reserve entry.
#--------------------------------
sub _createReserve {
   my (%args) = @_;
   my $reserveID;

   my $units      = $args{units};
   return if ( !$units  || $units == 0);

   my $licenseID  = $args{license_id};
   my $productID  = $args{product_id};
   my $effRate    = $args{effective_rate};
   my $period     = $args{period};
   my $statRateID = $args{stat_rate_id};
   my $rateBasis  = $args{rate_basis};
   assert($licenseID);
   assert($productID);
   assert($effRate);
   assert(defined $period);
   #assert($statRateID); Not required for penny rate
   assert($rateBasis);

   my %rargs = (
      track_license_id           => $licenseID,
      product_id                 => $productID,
      original_statement_item_id => 0,
      units                      => $units,
      effective_rate             => $effRate,
      periods_remaining          => $period,
      sale_stat_rate_id          => $statRateID,
   );

   #$rargs{issue_stat_rate_id} = $statRateID
   #   if ( RPS::DB::Item::TrackLicense::kRateBasisLock == $rateBasis );
   if ( RPS::DB::Item::TrackLicense::kRateBasisLock == $rateBasis )
   {
      # Note: lock date licenses won't be fixed rate, therefore they'd better have a statRateID
      #
      assert($statRateID);
      $rargs{issue_stat_rate_id} = $statRateID;
   }

   if ( $execMode ) {
      my $rObj = RPS::DB::Item::LicenseReserve->Create( %rargs );
      $rObj->save();
      $reserveID = $rObj->license_reserve_id;
      report("   Created license_reserve $reserveID : ".Dumper(\%rargs));
      #++$gCount{license_reserve}; XXX
   } else {
      report("   Non-exec mode, skipping reserve : ".Dumper(\%rargs));
   }

   report("   _createReserves: effRate($effRate) licenseID($licenseID) "
      ."productID($productID) units(". ($units || 0) . ") period($period) statRateID(". ((defined $statRateID) ? $statRateID : 'P') .")"
   );
   return $reserveID;

}# _createReserve

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

#-------------------------------------------------------------
# _setReserve -- defines a ReserveLiquidation item if there is
# any percentage to liquidate
#-------------------------------------------------------------
sub _setReserve {
   my($period,$percent,$a) = @_;
   $percent = 0 if ( !$percent );
   $a->Period($period);
   $a->Percent($percent);
}

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

#---------------------------------------------------
# _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 _normalizeDate {
   my($dstr) = @_;

   $dstr =~ s/\x{a0}//g;
   $dstr =~ s/\s//g;

   if ( $dstr =~ m/^(\d+)$/ ) {
      my $_dt = numericToDate( $dstr );
      report("_normalizeDate:  Converted msft date $dstr to $_dt");
      $dstr = $_dt;
   }
   elsif( $dstr =~ /^(\d{1,2})-(\d{1,2})-(\d{2})$/ ) # MM-DD-YY
   {
      my($m,$d,$y) = ($1,$2,$3);
      if ($3 =~ /[2-9]\d/) # Y2k kludge
      {
         $y += 1990;
         report("_normalizeDate:  Converted Y2K date $3 to $y");
      }
      else
      {
         $y += 2000;
      }
      $dstr = sprintf("%04d-%02d-%02d", $y, $m, $d);
   }
   elsif( $dstr =~ /^(\d{1,2})\/(\d{1,2})\/(\d{4})$/ ) # MM-DD-YYYY
   {
      my($m,$d,$y) = ($1,$2,$3);
      $dstr = sprintf("%04d-%02d-%02d", $y, $m, $d);
   }
   elsif( $dstr =~ /^(\d{1,2})\/(\d{1,2})\/(\d{2})$/ ) # MM-DD-YY
   {
      my($m,$d,$y) = ($1,$2,$3);
      $y += 2000;
      $dstr = sprintf("%04d-%02d-%02d", $y, $m, $d);
   }
   elsif( $dstr =~ /^(\d{4})-(\d{2})-(\d{2})$/ ) # YYYY-MM-DD
   {
      my($y,$m,$d) = ($1,$2,$3);
      $dstr = sprintf("%04d-%02d-%02d", $y, $m, $d);
   }
   else {
      #die "_normalizeDate: unable to process '$dstr'\n";
      report("ERROR: _normalizeDate: unable to normalize '$dstr'");
   }
   return $dstr;

}

sub _isInteger {
   my($v) = @_;
   return ( $v and $v =~ m/^\d*$/ ) ? 1 : undef;
}

sub _findTrack {
   my(%args) = @_;
   my $trackID;
   my ($albumID,$errflag) = _findAlbum(%args);
   if ( ! $albumID ) {
      my $albumName = $args{album_name} || "";
      my $catNo = $args{catalog_number} || "";
      report("   _findTrack: album($albumName) cat#($catNo) not found, skipping track");
      $errflag = kAlbumNotFound;
   } else {
      my $trackName = $args{track_name};
      $trackName =~ s/^\s+//; # remove leading spaces
      $trackName =~ s/\s+$//; # remove trailing spaces

      my $isrc = (defined $args{isrc}) ? $args{isrc} : "";
      report("   _findTrack: looking for track($trackName) isrc($isrc) on albumID($albumID)");


      my $duplicate; # set if track title is not unique
      if ( !$isrc ) {
         # No ISRC; see if track title is a duplicate
         my $cleanTitle = clean_name_catalog($trackName);
         my $sql = "SELECT track_id FROM track "
            . "WHERE album_id = $albumID AND "
            #. "( title = ". $dbo->DBQuote($trackName) . " OR title_clean LIKE '$cleanTitle%' ) ";
            . "( title = ". $dbo->DBQuote($trackName) . " OR title_clean = '$cleanTitle' ) ";
         my $sth = $dbo->DoCmd($sql);
         if ( $sth->rows > 1 ) {
            report("_findTrack:DEBUG: duplicate title detected: sql = $sql");
            $errflag = kDuplicateTrackTitle;
            $duplicate = 1;
         }
      }

      if ( ! $duplicate ) {
         my $tColl = RPS::DB::Item::Track->GetTracksByAlbumID($albumID);
         while( $tColl->hasNext() ) {
            my $trackObj = $tColl->next();

            if ( $isrc ) {
               $isrc = trimspaces($isrc);
               my $masterID = $trackObj->master_id;
               my $mObj = RPS::DB::Item::Master->Lookup( master_id => $masterID );
               my $zzIsrc = $mObj->isrc;
               #if ( '' ne $isrc && $isrc eq $zzIsrc )
#die("STOP: no ISRC for master_id $masterID !!!") if( !defined $zzIsrc ); # XXX

               my $t1       = lc clean_name_catalog($trackName);
               my $oldClean = lc clean($trackName);  # this is deprecated, but some old catalog used this
               my $t2       = lc $trackObj->title_clean;

               my $actualTitle = $trackObj->title;

               # kludge for New World
               #$t2 =~ s/__/_/g if ( $clientID == 138 );

               $t2 =~ s/^_//g if ( $clientID == 290 ); # fearless

               # kludge for Nettwerk; remove trailing "_" from clean title
               $t2 =~ s/_$// if ( $clientID == 51 );

               # kludge for EGR; remove trailing "_" from clean title
               $t2 =~ s/_$// if ( $clientID == 183 );

               # kludge for VP Records; remove trailing "_" from clean title (FB13163)
               #$t2 =~ s/_$// if ( $clientID == 306 );
               if ( $clientID == 306 )
               {
                  $t1       =~ s/_$//;
                  $oldClean =~ s/_$//;
                  $t2       =~ s/_$//;
               }

               if ( defined $zzIsrc ) {
                   report("_findTrack:2: comparing titles [$t1] to [$t2], isrc [$isrc] to [$zzIsrc]", kDebug );
               } else {
                   report("_findTrack:2: comparing titles [$t1] to [$t2], isrc [$isrc] to [--NOT DEFINED--] (master $masterID)", kDebug );
               }

               # The ISRC and title must match
               # If the RS track title matches but doesn't have an ISRC, then allow
               # the match.
               #
               if ( $isrc && $zzIsrc && '' ne $isrc && ($isrc eq $zzIsrc) &&
                   (($t1 eq $t2) || ($oldClean eq $t2) || ( lc $actualTitle eq lc $trackName )) )
               {
                  $trackID = $trackObj->track_id;
                  last;
               }

               # Compare the raw track titles
               if ( $isrc && $zzIsrc && '' ne $isrc && ($isrc eq $zzIsrc) &&
                  ( $trackName eq $trackObj->title ) )
               {
                  $trackID = $trackObj->track_id;
                  last;
               }
            } else {
               my $t1       = lc clean_name_catalog($trackName);
               my $oldClean = lc clean($trackName);  # this is deprecated, but some old catalog used this
               my $t2       = lc $trackObj->title_clean;
               my $t2NewClean = lc clean_name_catalog($trackObj->title_clean);

               my $_id = $trackObj->track_id;
               my $_dc = $trackObj->date_created;
#               report( "D0: _findTrack: comparing '$t1' to '$t2' id($_id) created($_dc) oldClean($oldClean)"); # XXX
#               report( "D0: _findTrack: comparing '$t1' to '$t2NewClean' id($_id) created($_dc)"); # XXX
#
               # kludge for New World
               $t2 =~ s/__/_/g if ( $clientID == 138 );

               $t2 =~ s/^_//g if ( $clientID == 290 ); # fearless

               # kludge for Nettwerk; remove trailing "_" from clean title
               $t2 =~ s/_$//;

               # kludge for VP Records; remove trailing "_" from clean title (FB13163)
               $t2 =~ s/_$// if ( $clientID == 306 );

               report("_findTrack: comparing [$t1] to [$t2]", kDebug );

               #if ( $t1 eq $t2 || $oldClean eq $t2 ) {
               if ( $t1 eq $t2 || $oldClean eq $t2 || $t1 eq $t2NewClean) {
                  $trackID = $trackObj->track_id;
                  last;
               }

               report( "  D: _findTrack: comparing '$trackName' to '". $trackObj->title ."' id($_id) created($_dc)"); # XXX
               # Compare the raw track titles
               if ( $trackName eq $trackObj->title ) {
                  $trackID = $trackObj->track_id;
                  last;
               }


               # XXX kludge for CMH (RSD-4793)
               if ( $trackName =~ /\.\.\./ ) { # check for three dots (ellipsis)
                  my $_trackName = $trackName;
                  $_trackName =~ s/\.\.\./\x{2026}/;  # horizontal ellipsis
                  report( "  D: _findTrack: comparing '$_trackName' to '". $trackObj->title ."' id($_id) created($_dc)"); # XXX
                  if ( $_trackName eq $trackObj->title ) {
                     $trackID = $trackObj->track_id;
                     last;
                  }
               }
               if ( $trackName =~ /'/ ) {  # check for apostrophe
                  my $_trackName = $trackName;
                  $_trackName =~ s/'/\x{2019}/;  # RIGHT SINGLE QUOTATION MARK
                  report( "  D: _findTrack: comparing '$_trackName' to '". $trackObj->title ."' id($_id) created($_dc)"); # XXX
                  if ( $_trackName eq $trackObj->title ) {
                     $trackID = $trackObj->track_id;
                     last;
                  }
               }
               if ( $trackName =~ /&/ ) { # check for ampersand
                  my $_trackName = $trackName;
                  $_trackName =~ s/&/and/;
                  report( "  D: _findTrack: comparing '$_trackName' to '". $trackObj->title ."' id($_id) created($_dc)"); # XXX
                  if ( $_trackName eq $trackObj->title ) {
                     $trackID = $trackObj->track_id;
                     last;
                  }
               }

            }
         }#track loop

         $errflag = kTrackNotFound if( !$trackID );
      }


   }
   return ($trackID, $errflag);
}# _findTrack

sub _findAlbum {
   my(%args) = @_;
   my $catNo = $args{catalog_number} || "";
   my $albumName = $args{album_name};
   my $errflag;
   my $albumID;
   if ( $albumName ) {
      my %a = (
         title => $albumName,
      );
      $a{catalog_number} = $catNo if ( $catNo );
      #report("   _findAlbum: looking for CAT#($catNo) title($albumName)");

      my $cleanAlbumTitle = clean_name_catalog($albumName);
      my $_catNo = (defined $catNo ) ? $catNo : '--NOT SPECIFIED--';
      report("   _findAlbum: looking for CAT#($_catNo) title($albumName) titleClean($cleanAlbumTitle)");

      #-------------------------------------------------------------------
      # Warning: if a title has a trailing space in the database, then the
      # clean version will have a trailing underscore.  We use LIKE to try
      # and prevent this from not finding the album.
      #-------------------------------------------------------------------

      #my $sql = "SELECT album.* FROM album WHERE title_clean LIKE '$cleanAlbumTitle%' ";

      #my $sql = "SELECT album.* FROM album WHERE "
      #   . "( title = ". $dbo->DBQuote($albumName) . " OR title_clean LIKE '$cleanAlbumTitle%' ) ";

      my $sql = "SELECT album.* FROM album WHERE "
         . "( title = ". $dbo->DBQuote($albumName) . " OR title_clean = '$cleanAlbumTitle' ) ";
      $sql .= " AND catalog_number = " . $dbo->DBQuote($catNo) if ( $catNo );

      my $albumColl = RPS::DB::Item::Album->GetAll( $sql );

      if ( $albumColl->size() == 0 ) {
         $errflag = kAlbumNotFound;
         report("   _findAlbum: album not found: title($albumName) cat#($_catNo)");
      } elsif ( $albumColl->size() > 1 ) {
         report("   _findAlbum: duplicate album detected: title($albumName) cat#($_catNo)");
         $errflag = kNonUniqueAlbumName;
      }

      if ( !$errflag ) {
         my $aObj = $albumColl->next();
         $albumID = $aObj->album_id;
         report("   _findAlbum: found albumID($albumID)");
      }
   }
   else
   {
      # no album name, maybe we can use the catalog number
      if( $catNo )
      {
         my $sql = "SELECT album.* FROM album WHERE catalog_number = ".  $dbo->DBQuote($catNo);
         my $albumColl = RPS::DB::Item::Album->GetAll( $sql );
         $errflag = kAlbumNotFound if ( $albumColl->size() == 0 );
         $errflag = kNonUniqueCatalogNumber if ( $albumColl->size() > 1 );
         if ( !$errflag ) {
            my $aObj = $albumColl->next();
            $albumID = $aObj->album_id;
            report("   _findAlbum: found albumID($albumID) using catalog#");
         }

      }
   }
   return ($albumID, $errflag);
}# _findAlbum

sub _findPublisher {
   my(%args) = @_;
   my $publisherID;
   my $name = $args{publisher_name};
   $name =~ s/\s*$//g;
   $name =~ s/^\s*//g;
   my $pObj = RPS::DB::Item::Publisher->Lookup(
      publisher_name => $name,
   );
   # TODO: Should check for duplicate publisher names..
   if ( $pObj ) {
      $publisherID = $pObj->publisher_id;
   }
   return $publisherID;
}

sub _findPayor {
   my(%args) = @_;
   my $payorID;
   my $pObj = RPS::DB::Item::Payor->Lookup(
      name => $args{payor_name},
   );
   if ( $pObj ) {
      $payorID = $pObj->payor_id;
   }
   return $payorID;
}

sub _findRegion {
   my(%args) = @_;
   my $regionID;
   my $regionName = $args{name};

   my $rObj = RPS::DB::Item::Region->Lookup(
      name => $regionName,
   );
   if ( $rObj ) {
      $regionID = $rObj->region_id;
   }
   return $regionID;
}

#--------------------------------------------------------
# For 'ALL' or 'D' licenses, the region must be US or CA.
# This routine checks the specified region and sees if it
# contains any other countries besides US and/or CA
#--------------------------------------------------------
sub _validDigitalRegion {
   my($regionID) = @_;
   my $valid = 1;
   my $coll = RPS::DB::Item::RegionCountryMap->GetByRegionID($regionID);
   while($valid && $coll->hasNext() ) {
      my $rcObj = $coll->next();
      my $cc = uc $rcObj->country_code;
      $valid = 0 if ( $cc ne 'US' && $cc ne 'CA' );
   }
   return $valid;

}
#-----------------------------------------------------------
# _getCrossedID -- returns a value that can be used to set a
# license's cross_collateralized field
#-----------------------------------------------------------
sub _getCrossedID {
   my(%args) = @_;
   my $crossed = $args{crossed};
   my $crossedID = 0; # no - default
   if ( $crossed && $crossed =~ /^y/i ) {
      $crossedID = 1; 
   }
   return $crossedID;
}

#----------------------------------------------------------
# _getStatusID -- returns a value that can be used to set a
# license's 'inactive' field
#----------------------------------------------------------
sub _getStatusID {
   my(%args) = @_;
   my $status = $args{inactive};
   my $statusID = 0; # active - default
   if ( $status && $status =~ /^i/i ) {
      $statusID = 1; 
   }
   return $statusID;
}

#----------------------------------------------------------
# _getPublisherDirect -- returns a value that can be used
# to set a license's 'publisher_direct' field.
#----------------------------------------------------------
sub _getPublisherDirect {
   my(%args) = @_;
   my $pdflag = $args{publisher_direct};
   my $statusID = 0; # default is "not publisher direct"
   if ( $pdflag && $pdflag =~ /^y/i ) {
      $statusID = 1;  # publisher direct
   }
   return $statusID;
}

#--------------------------------------------------------------
# _getRateBasis -- returns a rate basis identifier based on the
# supplied text string
#--------------------------------------------------------------
sub _getRateBasis {
   my(%args) = @_;
   my $rateBasis = $args{rate_basis};
   my $rateBasisID;
   if ( !$rateBasis || ($rateBasis && $rateBasis =~ /^S/i ) ) {
      $rateBasisID = RPS::DB::Item::TrackLicense::kRateBasisSale; # Default
   } elsif ( $rateBasis && $rateBasis =~ /^L/i ) {
      $rateBasisID = RPS::DB::Item::TrackLicense::kRateBasisLock;
   }
   return $rateBasisID;
}

#-----------------------------------------------------------------
# _getRateType -- returns an RPS rate type identifier based on the
# supplied rate type string.
# If a rate type isn't found, then an undefined rate type ID will
# be returned to the caller.
#-----------------------------------------------------------------
sub _getRateType {
   my(%args) =@_;
   my $rateType = $args{rate_type};
   my $rateTypeID = undef;

   # Note: the 1.0 spec defines S, M and P as valid rate types
   # I'm including F(ULL), M(IN), P(ENNY) for now.
   if ( $rateType && '' ne $rateType ) {
      if ( $rateType =~ /^p/i ) {

         $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypePenny;

      } elsif( ($rateType =~ /^s/i) || ($rateType =~ /^f/i) ||
               ($rateType =~ /ring/i) || ($rateType =~ /r/i ) ) {

         $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypeFull;

      } elsif( $rateType =~ /^m/i ) {

         $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypeMinimum;
      }
# XXX 12/1/16 -- If rate type isn't specified, we'll flag as error
#   } else {
#      $rateTypeID = RPS::DB::Item::TrackLicense::kRateTypeFull; # Default
   }

   return $rateTypeID;
}

sub _getProductTypeID {
   my(%args) = @_;
   my $cfg = $args{product_type};
   #return RPS::DB::Item::ProductType::kAllProducts if ( $cfg =~ /^all$/i );
   return kProductTypeNotFound if ( !$cfg );
   return undef if ( $cfg =~ /^all$/i || $cfg =~ /^all products$/i );

   return RPS::DB::Item::ProductType::kAllDigitalProducts if ( ($cfg =~ /^alld$/i) || ($cfg =~ /^all-d$/i) ||
      ($cfg =~ /^all digital$/i) || ($cfg =~ /^all(?:-|\s)digital(?: products)?$/i) ); # All digital

   return RPS::DB::Item::ProductType::kAllPhysicalProducts if ( ($cfg =~ /^allp$/i) ||
      ($cfg =~ /^all-p$/i) || ($cfg =~ /^all physical(?: products)?$/i ) );

   return RPS::DB::Item::Product::kProductTypeLP if ( $cfg =~ /^lp$/i );
   return RPS::DB::Item::Product::kProductTypeCD if ( $cfg =~ /^cd$/i );
   return RPS::DB::Item::Product::kProductTypeDigital if ( $cfg =~ /^da$/i || $cfg =~ /^digital album$/i );
   return RPS::DB::Item::Product::kProductTypeDigitalTrack if ( $cfg =~ /^dt$/i );
   return RPS::DB::Item::Product::kProductTypeVHS if ( $cfg =~ /^vhs$/i );
   return RPS::DB::Item::Product::kProductTypeCass if ( $cfg =~ /^cass$/i );
   return RPS::DB::Item::Product::kProductTypeEP if ( $cfg =~ /^ep$/i );
   return RPS::DB::Item::Product::kProductTypeDVD if ( $cfg =~ /^dvd$/i );
   return RPS::DB::Item::Product::kProductTypeCDSingle if ( $cfg =~ /^cd5$/i );
   return RPS::DB::Item::Product::kProductTypeCassSingle if ( $cfg =~ /^cas5$/i );
   return RPS::DB::Item::Product::kProductTypeDVDCDSet if ( $cfg =~ /^dvdcd$/i );
   return RPS::DB::Item::Product::kProductTypeDblCD if ( $cfg =~ /^cd2$/i );
   return RPS::DB::Item::Product::kProductTypeRingtone if ( $cfg =~ /^ring$/i || $cfg =~ /^rt$/i );
   return RPS::DB::Item::Product::kProductTypeLP5 if ( $cfg =~ /^lp5$/i );
   return kProductTypeNotFound;
}

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

}# _getError2

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;