package Support::Implementation::ArtistReservesTemplate;
# 8/31/10 - Changed price_level lookup to RSCOMMON.
#   Changed source lookup to use contract_term_source
#   Added support for new rate types.
# 9/29/16 - Fixed regionID logic; Fixed price level logic
use strict;
use warnings;

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

use IO::File;
use Data::Dumper;
use Date::Calc;

use Spreadsheet::ParseExcel;

use Common::Assert;
use Common::UTF8;
use Common::RSApp;

#use RPS::License::TrackLicense;
#use RPS::License::ReserveLiquidation;

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::Product;
use RPS::DB::Item::ProductType;
#use RPS::DB::Item::IncomeSource;
use RPS::DB::Item::ContractTermSource;
use RPS::DB::Item::AlbumContract;
use RPS::DB::Item::TrackContract;
use RPS::DB::Item::NewArtistContract;
use RPS::DB::Item::NewArtistContractTerm;
use RPS::DB::Item::ContractRateType;
use RPS::DB::Item::ArtistContractTermReserve;
#use RPS::DB::Item::PendingTransaction;
#use RPS::Finance::PendingTransaction;
#use RPS::DB::Item::FinanceAccount;
#use RPS::Finance::Account;

use Support::Implementation::ExcelReader;
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 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-title"         => 0,  # A
   "catalog-number"      => 1,  # B
   "contract-title"      => 2,  # C
   "rs-contract-number"  => 3,  # D
   "income-source"       => 4,  # E
   "channel"             => 5,  # F
   "price-level"         => 6,  # G
   "region"              => 7,  # H
   "p0"                  => 8,  # I
   "p1"                  => 9,  # J
   "p2"                  => 10, # K
   "p3"                  => 11, # L
   "p4"                  => 12, # M
   "p5"                  => 13, # N
   "p6"                  => 14, # O
   "p7"                  => 15, # P
);

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

#--------------------------------------
# count keeps track of entities created
#--------------------------------------
my %gCount = (
   license_reserve => 0,
);

#-----------------------------
# Stores income source mapping
#-----------------------------
my %gIncomeSourceMap;

#-----------------------
# Stores channel mapping
#-----------------------
my %gChannelMap;

#---------------------------
# Stores price level mapping
#---------------------------
my %gPriceLevelMap;

#---------------------
# Stores product types
#---------------------
my %gProductTypeMap;


my $clientID;
my $execMode;

# Client-database connnection
my $dbo;
my $dbh;

# RSCOMMON connnection
my $cdbo;

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


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

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

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

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

   return $self;
}

sub parseHeader {
   my $self = shift;
}

# Reference to SearchUtil object
my $gSearchObj;

#--------------------------------------------------------------
# Read-in all of the template data into memory.  Once it's been
# read, try to parse it and
#--------------------------------------------------------------
sub loadMemory {
   my $self = shift;

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

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

   $cdbo = Common::RSApp::GetCommonDB();

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

   # Fill the income source map
   #my $sql = "SELECT name, income_source_id "
   #   . "FROM income_source";

   my $sql = "SELECT i.name, cts.contract_term_source_id "
      ."FROM income_source i "
      ."JOIN contract_term_income_source ctis USING(income_source_id) "
      ."JOIN contract_term_source cts USING(contract_term_source_id) "
      ."ORDER BY i.name, cts.contract_term_source_id DESC "
      #."WHERE cts.contract_term_source_id NOT IN (1,2,3) "
      ;

   my $sth = $cdbo->DoCmd($sql);
   while( my($name,$id) = $sth->fetchrow_array() ) {

      if ( exists $gIncomeSourceMap{lc $name} ) {
         $gIncomeSourceMap{lc $name} .= ",$id";
         
      } else {
         $gIncomeSourceMap{lc $name} = $id;
      }
   }

   # Fill the channel map
   $sql = "SELECT name, channel_id FROM channel";
   $sth = $dbo->DoCmd($sql);
   while( my($name,$id) = $sth->fetchrow_array() ) {
      $gChannelMap{lc $name} = $id;
   }
   $gChannelMap{all} = 0;

   # Fill the price level map
   $sql = "SELECT name, price_level_id FROM price_level";
   $sth = $cdbo->DoCmd($sql); # RSCOMMON
   while( my($name,$id) = $sth->fetchrow_array() ) {
      $gPriceLevelMap{lc $name} = $id;
   }
   $gPriceLevelMap{all} = 0;

   # Fill the product type map
   $sql = "SELECT description, product_type_id FROM product_type";
   $sth = $cdbo->DoCmd($sql); # RSCOMMON
   while( my($name,$id) = $sth->fetchrow_array() ) {
      $gProductTypeMap{lc $name} = $id;
   }

   # Fill the price level map
   my $fileName = $self->name;

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

   if( $self->isExcel2003( $fileName ) ) {
      print("ArtistReservesTemplate::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;
      #_scanExcelFile( $fileName, \%data, \%gTemplateHeader, \%gColumnMap );

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

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

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

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

   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 = (
      region_not_found           => 0,
      invalid_product_type       => 0,
      missing_product_type       => 0,
      invalid_licenseid          => 0,
      license_not_found          => 0,
      license_publisher_mismatch => 0,
      track_not_found            => 0,
      album_not_found            => 0,
      publisher_name_mismatch    => 0,
      product_not_found          => 0,
      no_track_duration          => 0,
   );

   #-----------------------------
   # Loop over each row (license)
   #-----------------------------

   my $rowCount=0;
   foreach my $row (@$rows) {

#      next if ( $counter > 100 );
      ++$rowCount;

      #-------------------------------------------
      # Get all of the template variables.
      # 9/23/09: Modified to 1.0.2 specification.
      #-------------------------------------------
      my $rowid               = $row->{rowid};

      my $albumName           = $row->{'album-title'};        # A
      my $catalogNumber       = $row->{'catalog-number'};     # B
      my $contractTitle       = $row->{'contract-title'};     # C
      my $rsContractID        = $row->{'rs-contract-number'}; # D
      my $incomeSource        = $row->{'income-source'};      # E
      my $channel             = $row->{'channel'};            # F
      my $priceLevel          = $row->{'price-level'};        # G
      my $region              = $row->{'region'};             # H

      my $p0                  = $row->{'p0'};                 # I
      my $p1                  = $row->{'p1'};                 # J
      my $p2                  = $row->{'p2'};                 # K
      my $p3                  = $row->{'p3'};                 # L
      my $p4                  = $row->{'p4'};                 # M
      my $p5                  = $row->{'p5'};                 # N
      my $p6                  = $row->{'p6'};                 # O
      my $p7                  = $row->{'p7'};                 # P

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

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

      my $regionID;
      my $incomeSourceID;
      my $channelID;
      my $priceLevelID;
      my $contractID;
      my $termID;

      my $albumID;
      my $productID;
      my $_err;

      #-----------------------------------------
      # Make sure we have something to liquidate
      #-----------------------------------------
      if ( (!$p0 || '' eq $p0) &&
           (!$p1 || '' eq $p1) &&
           (!$p2 || '' eq $p2) &&
           (!$p3 || '' eq $p3) &&
           (!$p4 || '' eq $p4) &&
           (!$p5 || '' eq $p5) &&
           (!$p6 || '' eq $p6) &&
           (!$p7 || '' eq $p7) ) {
         _appendString( $errorCode, "Nothing to liquidate");
         ++$excount{nothing_to_liquidate};
      }

      #-------------------------------------------
      # Make sure that the units are whole numbers
      #-------------------------------------------
      if ( ($p0 && $p0 =~ /\./ ) ||
           ($p1 && $p1 =~ /\./ ) ||
           ($p2 && $p2 =~ /\./ ) ||
           ($p3 && $p3 =~ /\./ ) ||
           ($p4 && $p4 =~ /\./ ) ||
           ($p5 && $p5 =~ /\./ ) ||
           ($p6 && $p6 =~ /\./ ) ||
           ($p7 && $p7 =~ /\./ ) ) {
         _appendString( $errorCode, "Fractional units not allowed");
         ++$excount{fractional_units_not_allowed};
      }

      #---------------------
      # Is the region valid?
      #---------------------
      $regionID = _findRegion( name => $region );
      if ( ! defined $regionID ) {   # regionID can be 0 or higher
         _appendString( $errorCode, "Region not found");
         ++$excount{region_not_found};
      }

      #----------------------------
      # Is the income source valid?
      #----------------------------
      if ( !$incomeSource ) {
         _appendString( $errorCode, "Missing income source");
         ++$excount{missing_income_source};
      } else {

         $incomeSourceID = _getIncomeSourceID( lc $incomeSource );
         if ( !$incomeSourceID ) {
            _appendString( $errorCode, "Invalid income source");
            ++$excount{invalid_income_source};
         }
      }

      if ( !$incomeSourceID ) { # DEBUG
         report("### Invalid income source '$incomeSource' not found");
         foreach my $k (keys %gIncomeSourceMap) {
            my $v = $gIncomeSourceMap{$k};
            report("   name($k)  val($v)");
         }
      }

      #----------------------
      # Is the channel valid?
      #----------------------
      $channelID = _getChannelID( lc $channel );
      if ( ! defined $channelID ) {
         _appendString( $errorCode, "Invalid channel");
         ++$excount{invalid_channel};
      }

      #--------------------------
      # Is the price level valid?
      #--------------------------
      $priceLevelID = _getPriceLevelID( lc $priceLevel );
      if ( ! defined $priceLevelID ) {
         _appendString( $errorCode, "Invalid price level");
         ++$excount{invalid_price_level};
      }

      #--------------------
      # Is the album valid?
      #--------------------
      #($albumID, $_err) = _findAlbum( 
      ($albumID, $_err) = $gSearchObj->findAlbum( 
         album_name => $albumName,
         catalog_number => $catalogNumber,
      );
      if ( $_err && $_err == kAlbumNotFound ) {


         report("_EXCEPTION: album(". _printNull($albumName) .") "
            . "cat#(". _printNull($catalogNumber) .") not found");

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

         # for grins and giggles, let's try to find some alternatives...

         if ( $catalogNumber ) {
            my $sql = "SELECT album_id,title,catalog_number "
               . "FROM album "
               . "WHERE catalog_number LIKE '%" . $catalogNumber . "%'";
            my $sth = $dbo->DoCmd($sql);
            if ( $sth->rows() > 0 ) {
               while( my($albumID,$title,$catno) = $sth->fetchrow_array() ) {
                  report("   _EXCEPTION: not_found: album(" . _printNull($albumName) . ") "
                     . "cat#(". _printNull($catalogNumber) . ") "
                     . " ---> albumID($albumID) title($title) cat#($catno)");

                  report(join("\t",
                     "POSSIBLE_MATCH:",
                     _printNull($albumName),
                     _printNull($catalogNumber),
                     _printNull($albumID),
                     _printNull($title),
                     _printNull($catno)
                  ));
               }
            } else {

               report("   _EXCEPTION: not_found: album(" . _printNull($albumName) . ") "
                  . "cat#(". _printNull($catalogNumber) . ")");

            }
         }
      }

      #----------------------------
      # Is there a product defined?
      #----------------------------
      my $productTypeID;
      #$productTypeID = _getProductTypeID( $incomeSourceID );
      $productTypeID = _getProductTypeID( lc $incomeSource );

      if ( !$productTypeID ) {
         _appendString( $errorCode, "Unable to map income source");
         ++$excount{unable_to_map_income_source};

         report("ERROR: Unable to map income source, productTypeMap = ". Dumper(\%gProductTypeMap) . "\n");

      } else {

         if ( $productTypeID == 3 ) { # DA
            _appendString( $errorCode, "Digital product detected");
            ++$excount{digital_product_detected};
         } else {
            if ( $albumID ) {
               $productID = _getProductID(
                  album_id => $albumID,
                  product_type_id => $productTypeID,
               );
            }

            if ( !$productID ) {
               _appendString( $errorCode, "No product found");
               ++$excount{no_product_found};
            }
         }

      }

      #-------------------------
      # Does the contract exist?
      #-------------------------
      if ( $rsContractID ) {
         my $o = RPS::DB::Item::NewArtistContract->Lookup(
            artist_contract_id => $rsContractID,
         );
         if ( !$o ) {
            _appendString( $errorCode, "Invalid contractID");
            ++$excount{invalid_contract_id};
         } else {
            $contractID = $o->artist_contract_id;
         }
      } else {
         if ( !$contractTitle ) {
            _appendString( $errorCode, "Blank contract title");
            ++$excount{blank_contract_title};
         } else {
            $contractID = _findContract( title => $contractTitle );
         }

         if ( !$contractID ) {

            report("   _EXCEPTION: contract_not_found: title('$contractTitle')");

            _appendString( $errorCode, "Contract not found");
            ++$excount{contract_not_found};
         }
      }

      report("  DEBUG: contractID($contractID)") if ( $contractID );

      #------------------------------
      # Does the contract term exist?
      #------------------------------
      my $termObj;
      if ( $contractID && defined $incomeSourceID && defined $channelID &&
           defined $regionID && defined $priceLevelID ) {

         my @sourceIDs = split(",", $incomeSourceID);

         foreach my $srcID (@sourceIDs) {

            next if( $termID );

            my %args = (
               artist_contract_id => $contractID,

               #income_source_id   => $incomeSourceID,
               #contract_term_source_id   => $incomeSourceID,
               contract_term_source_id   => $srcID,

               channel_id         => $channelID,
               region_id          => $regionID,
               price_level_id     => $priceLevelID,
            );
            $termObj = RPS::DB::Item::NewArtistContractTerm->Lookup( %args );
            if ( !$termObj ) {
               report("   XXX DEBUG: Term not found : ". Dumper(\%args));
               #report("   DEBUG: Term not found : ". Dumper(\%args));
               #_appendString( $errorCode, "Term not found");
               #++$excount{term_not_found};
            } else {
               $termID = $termObj->artist_contract_term_id;
            }

# TODO: Need to check for 'all' channel ?

         }

         if ( !$termID ) {
            #report("   DEBUG: Term not found : ". Dumper(\%args));
            report("   DEBUG: Term not found !!!");
report("DEBUG: ". Dumper(\%gIncomeSourceMap) ); # XXX YYY
            _appendString( $errorCode, "Term not found");
            ++$excount{term_not_found};
         }





      } else {
         _appendString( $errorCode, "Unable to lookup term");
         ++$excount{unable_to_lookup_term};
      }
      #report("  DEBUG: termID($termID)") if ( $termID );

      #------------------------------------------------------
      # Is the contract attached at the album or track level?
      #------------------------------------------------------

      my $trackContractID; # set only if track-level contract
      my $trackProration;  # ditto
      my $trackID;         # ibid

      my $albumContractID;
      if ( $albumID && $contractID ) {
         $albumContractID = _findAlbumContractID(
            album_id => $albumID,
            contract_id => $contractID,
         );
      }

      if ( $albumContractID ) {
         report("   contractID($contractID) is attached to albumID($albumID) "
            . "via albumContractID($albumContractID)");
      } else {

         if ( $albumID && $trackID && $contractID ) {
            ($trackContractID,$trackProration,$trackID) = _findTrackContractID(
               album_id => $albumID,
               contract_id => $contractID,
            );

            if ( !$trackContractID ) {
               report("   WARNING: Contract '$contractTitle' "
                  . "(id=". _printNull($contractID)  .") "
                  . "isn't attached to albumID $albumID or its tracks");
               _appendString( $errorCode, "Not attached to album or track");
               ++$excount{not_attached_to_album_or_track};
            }
         } else {
            report("   DEBUG: no album and/or contract -- skipping track_contract lookup");
         }
      }


      report( "  INFO: contractID(".  _printNull($contractID) .") "
         . "termID(". _printNull($termID) .") "
         . "incomeSourceID(" . _printNull($incomeSourceID) . ") "
         . "channelID(" . _printNull($channelID) . ") "
         . "priceLevelID(" . _printNull($priceLevelID) . ") "
         . "regionID(" . _printNull($regionID) . ") "
         . "acID(" . _printNull($albumContractID) . ") "
         . "tcID(" . _printNull($trackContractID) . ") "
         . "trackID(" . _printNull($trackID) . ") "
         . "prorate(" . _printNull($trackProration) . ") "
      );

      #----------------------------------------------
      #
      # At this point, the row is either good or bad.
      #
      #----------------------------------------------
      if ( $errorCode ) {
         report("   WARNING: skipping row($rowid) due to error(s): $errorCode");
         $row->{'error-code'} = $errorCode;
         next;
      }

      #---------------------------------------------------
      # If the rate_type is % of retail or % of wholesale,
      # lookup the price
      #---------------------------------------------------
      my $price;
      my $rateTypeID = $termObj->contract_rate_type_id;
      if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRetail ||
           $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentWholesale ||
           $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentRetail ||
           $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentDocumentWholesale ) {
         $price = _getProductPrice( $productID, $rateTypeID );
      } elsif( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypeFixed ) {
         $price = 1;
      } elsif( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRevenue ) {
         $price = 0;  # price isn't used; default it to 0
      } else {
         _appendString( $errorCode, "Invalid contract rate type");
         ++$excount{invalid_contract_rate_type};
         report("   Unexpected contractRateTypeID($rateTypeID)");
      }


      #---------------------------------------------------------------------------
      # Calculate the effective rate
      # TODO: verify - check if the reserve's effectiveRate is actually used,
      # or if the true effectiveRate is calculated during the run as rate * price.
      #---------------------------------------------------------------------------
      my $effectiveRate = $termObj->rate;
      $effectiveRate /= $trackProration if ( $trackProration );



      my @buckets;
      push @buckets, $p0;
      push @buckets, $p1;
      push @buckets, $p2;
      push @buckets, $p3;
      push @buckets, $p4;
      push @buckets, $p5;
      push @buckets, $p6;
      push @buckets, $p7;

      #------------------------------------------------------
      # reserveList will contain a list of reserveIDs created
      #------------------------------------------------------
      my @reserveList;
      my $per=1;
      foreach my $units (@buckets) {
         if ( $units ) {
            my %args = (
               artist_contract_term_id => $termID,
               product_id              => $productID,
               region_id               => $regionID,
               channel_id              => $channelID,
               periods_remaining       => $per,
               units                   => $buckets[$per - 1],
               effective_rate          => $effectiveRate,
               price_level_id          => $priceLevelID,
               income_source_id        => $incomeSourceID,
            );
            $args{price} = $price if ( $price );
            $args{album_id} = $albumID if ( $albumID );
            $args{track_id} = $trackID if ( $trackID );

            my $reserveID = _createReserve( %args );

            push @reserveList, $reserveID if ( $reserveID );

         }
         ++$per;
      }
      my $zzReserveList = join(",", @reserveList);
      $row->{'rs-reserve-id'} = $zzReserveList if ( $zzReserveList );


   }# row loop

   report("_processData: ". @errorList . " row(s) had errors");

   #------------------------------------------------------------
   # Validation: Do we have at least one of the periods defined?
   #------------------------------------------------------------

   _showExceptions( $rows );  # XXX XXX XXX

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

   foreach my $c (keys %excount) {
      my $v = $excount{$c};
      printf("%30s %6d\n", $c, $v);
      $totalExceptions += $v;
   }
   printf("%30s %s\n", " ", "-------" );
   printf("%30s %6d\n", "Total Exceptions", $totalExceptions );
   printf("%30s %6d\n", "Total Rows", $totalRows );
   report(" ");
   
   foreach my $c (keys %gCount) {
      my $v = $gCount{$c};
      printf("%30s %d\n", $c, $v);
   }
   report("total rows = $rowCount");
}#_processData

#--------------------------------------------------------------------------
# _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.
#--------------------------------------------------------------------------
sub _showExceptions {
   my ( $rows ) = @_;

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

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

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

   #--------------------------------------
   # Now dump out the rows that had errors
   #--------------------------------------
   foreach my $row (@$rows) {
      my $rowid = $row->{rowid};
      my $errorCode = $row->{'error-code'};
      my $reserveID = $row->{'rs-reserve-id'} || '---';

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

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

         push @obuf, $val;
      }

      my $importStatus = ($errorCode) ? "__FAIL__" : "reserve($reserveID)";

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

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

      report($zzbuf);
   }
}# _showExceptions

#--------------------------------
# Create an artist reserve entry.
#--------------------------------
sub _createReserve {
   my (%args) = @_;
   my $reserveID;

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

   my $termID     = $args{artist_contract_term_id};
   my $productID  = $args{product_id};
   my $regionID   = $args{region_id};
   my $channelID  = $args{channel_id};

   my $period     = $args{periods_remaining};
   my $priceLevel = $args{price_level_id};
   my $effRate    = $args{effective_rate};
   my $sourceID   = $args{income_source_id};
   my $price      = $args{price}; # optional

   my $albumID    = $args{album_id};
   my $trackID    = $args{track_id}; # optional

   assert($termID);
   assert($productID);
   assert(defined $regionID);   # may be 0 or higher
   assert(defined $channelID);  # may be 0 or higher
   assert($effRate);
   assert(defined $period);  # 0 through 7
   assert($sourceID);
   assert($albumID);

   my %rargs = (
      artist_contract_term_id    => $termID,
      product_id                 => $productID,
      region_id                  => $regionID,
      channel_id                 => $channelID,
      original_statement_item_id => 0,
      periods_remaining          => $period,
      units                      => $units,
      price_level_id             => $priceLevel,
      effective_rate             => $effRate,
      income_source_id           => $sourceID,
      album_id                   => $albumID,
   );
   $rargs{price} = $price if ( $price );

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

   report("   _createReserves: effRate($effRate) termID($termID) "
      . "productID($productID) units($units) period($period) "
      . "srcID($sourceID) ch($channelID) pLvl($priceLevel) "
      . "price(" . _printNull($price) . ")"
   );
   return $reserveID;

}# _createReserve

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

   my $t_stat_rate_id;

   my $t_year = $t_date;
   if ( $t_year eq '1996' or $t_year eq '1997' ) {
      $t_stat_rate_id = 11;  # see RSCOMMON.stat_rate for more info
   } elsif ( $t_year eq '1998' or $t_year eq '1999' ) {
      $t_stat_rate_id = 12;
   } elsif ( $t_year eq '2000' or $t_year eq '2001' ) {
      $t_stat_rate_id = 13;
   } elsif ( $t_year eq '2002' or $t_year eq '2003' ) {
      $t_stat_rate_id = 14;
   } elsif ( $t_year eq '2004' or $t_year eq '2005' ) {
      $t_stat_rate_id = 15;
   } elsif ( $t_year eq '2006' or $t_year eq '2007' or $t_year eq '2008' ) {
      $t_stat_rate_id = 16;
   } else {
      report("   _getStatRateID: Encountered unknown year '$t_year', ".
             "defaulting to max rate");
      $t_stat_rate_id = 16;
   }
   return $t_stat_rate_id;
}

#------------------------------------------------------------
# _getRoyaltyRate -- returns the royalty rate and minute rate
#  for a given date
#------------------------------------------------------------
sub _getRoyaltyRate {
   my($dateTaken) = @_;
   my $rate;
   my $minRate;
   assert($dateTaken);

   my $year = _getYear($dateTaken);
   #if ( $dateTaken =~ m/^(\d\d\d\d)/ ) { $year = $1; }
   #if ( $dateTaken =~ m/(\d\d\d\d)$/ ) { $year = $1; }
   die("invalid date '$dateTaken'") if ( !$year );

   if ( $year >= 2006 ) {
      $rate    = 0.0910;
      $minRate = 0.0175;
   } elsif( $year >= 2004 ) {
      $rate    = 0.0850;
      $minRate = 0.0165;
   } elsif( $year >= 2002 ) {
      $rate    = 0.0800;
      $minRate = 0.0155;
   } elsif( $year >= 2000 ) {
      $rate    = 0.0755;
      $minRate = 0.0145;
   } else {
      die("_getRoyaltyRate: WTF -- reserves from $dateTaken ???");
   }

   return($rate, $minRate);
}

sub _getYear {
   my($dt) = @_;
   my $year;
   if ( $dt =~ m/^(\d\d\d\d)/ ) { $year = $1; }
   if ( $dt =~ m/(\d\d\d\d)$/ ) { $year = $1; }
   return $year;
}

#-------------------------------------------------------------
# _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) = @_;
   #report("_dumpError: received ". ref $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 = $v->_getValue() || '';  # _getValue doesn't return 0?
         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/ ) {
         #printf("%s   %20s  %20s %s\n", $leader, $k, " ", $typeOf);
         my $pArray = $v->getPendingTransactionArray();
         my $pType = ref $pArray;
         $hasError = $v->_hasError();
         my $errFlag = $hasError ? "*** ERROR ***" : '>> OK <<';

         #printf("%s   %20s  %20s %s pTYPE=%s\n", $leader, $k, " ", $typeOf, $pType );

         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 <<';
            #printf("%s   %20s  %20s %s %s\n", $leader, $amt, $typeCode, $pType, $errFlag );
            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();
         #printf(" accountID(%d)\n", $financeAccountID );
         _dumpError( $leader . "      ",\%$v );

      } elsif( (ref $v) =~ /RPS::License::ReserveLiquidationList/ ) {
         #my $reserveLiquidationList = $v->{ReserveLiquidationList};
         #my $reserveArray = $reserveLiquidationList->getList();
         my $reserveArray = $v->getList();
            
         #printf("%s   %20s  %20s %s\n%20s", $leader, $k, " ", $typeOf);
         printf("%s   %20s  %s\n%20s", $leader, $k, "@", $typeOf);
         for( my $i=0; $i<8; $i++ ) {
         #   if ( $reserveArray->[$i]->Percent() ) {
               printf("%s [%d] %d,", $leader, $i, $reserveArray->[$i]->Percent() );
         #   }
         }
         print"\n";
      } else {
         #printf("DEBUG: leader($leader)\n");
         #printf("DEBUG: k($k)\n");
         #printf("DEBUG: v($v)\n");
         #printf("DEBUG: typeOf($typeOf)\n");
         my $errFlag = '';
#         if( defined $v ) {
#            $hasError = $v->_hasError();
#            $errFlag = $hasError ? "*** ERROR ***" : '>> OK <<';
#         }

         $v = "---" if ( !$v );
         printf("%s   %20s  %20s %s %s\n", $leader, $k, $v, $typeOf, $errFlag);
      }
   }
}# _dumpError

sub _dumpError2 {
   my($leader, $hash) = @_;
   #-------------------------------------------------------
   # 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 %$hash ) {
      report("_dumpError2: examining k($k)");
      my $v = $hash->{$k};
      my $typeOf = ref $v;

      my $hasError = '';

      if ( (ref $v) =~ /Common::FormObject::Scalar/ ||
           (ref $v) eq "Common::FormObject::DateTime" ) {
         _reportError($k, $v );
      }
   }

}# _dumpError2

sub _getTrackAlbumID {
   my($trackID) = @_;
   my $tObj = RPS::DB::Item::Track->Lookup( track_id => $trackID );
   return $tObj->album_id;
}

sub _findTrack {
   my(%args) = @_;
   my $trackID;
   my ($albumID,$errflag) = _findAlbum(%args);
   if ( ! $albumID ) {
      report("   _findTrack: album not found, skipping track");
      $errflag = kAlbumNotFound;
   } else {
      my $trackName = $args{track_name};
      $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 $tColl = RPS::DB::Item::Track->GetTracksByAlbumID($albumID);
      while( $tColl->hasNext() ) {
         my $trackObj = $tColl->next();
         if ( $isrc ) {
            my $masterID = $trackObj->master_id;
            my $mObj = RPS::DB::Item::Master->Lookup( master_id => $masterID );
            my $zzIsrc = $mObj->isrc;
            if ( $isrc eq $zzIsrc ) {
               $trackID = $trackObj->track_id;
               last;
            }
         } else {
            if ( (lc $trackName) eq (lc $trackObj->title) ) {
               $trackID = $trackObj->track_id;
               last;
            }
         }
      }
      $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 && $catNo ) {
#      report("   _findAlbum: looking for CAT#($catNo) title($albumName)");
#      my $aObj = RPS::DB::Item::Album->Lookup(
#         title => $albumName,
#         catalog_number => $catNo,
#      );
#      if ( $aObj ) {
#         $albumID = $aObj->album_id;
#         report("   _findAlbum: found albumID($albumID)");
#      } else {
#         report("   _findAlbum: album not found");
#         $errflag = kAlbumNotFound;
#      }
#   }
#   return ($albumID, $errflag);
#}# _findAlbum

sub _findAlbumContractID {
   my(%args) = @_;
   my $albumID    = $args{album_id};
   my $contractID = $args{contract_id};
   assert($albumID);
   assert($contractID);

   my $albumContractID;
   my $o = RPS::DB::Item::AlbumContract->Lookup(
      album_id => $albumID,
      artist_contract_id => $contractID,
   );
   $albumContractID = $o->album_contract_id if ( $o );
   return $albumContractID;
}# _albumContractID

sub _findTrackContractID {
   my(%args) = @_;
   my $albumID = $args{album_id};
   my $contractID = $args{contract_td};
   assert($albumID);
   assert($contractID);

   my $trackContractID;
   my $proration;
   my $sql = "SELECT track_id FROM track WHERE album_id=$albumID";
   my $sth = $dbo->DoCmd($sql);
   while( my($trackID) = $sth->fetchrow_array() ) {
      my $o = RPS::DB::Item::TrackContract->Lookup(
         track_id => $trackID,
         artist_contract_id => $contractID,
      );
      if ( $o ) {
         $trackContractID = $o->track_contract_id;
         $proration = $o->prorate_track_count;
         next;
      }
   }
   return ($trackContractID, $proration);
}# _albumContractID



#-------------------------------------------------------------------
# _getIncomeSourceID - returns the income source ID of the specified
# income source, or undef if the income source is invalid
#-------------------------------------------------------------------
sub _getIncomeSourceID {
   my($s) = @_;
   return $gIncomeSourceMap{$s};
}# _getIncomeSourceID

#--------------------------------------------------------
# _getChannelID - returns the channel ID of the specified
# channel, or undef if the channel is invalid
#--------------------------------------------------------
sub _getChannelID {
   my($s) = @_;
   return $gChannelMap{$s};
}# _getChannelID

#---------------------------------------------------------------
# _getPriceLevelID - returns the price level ID of the specified
# price level, or undef if the price level is invalid
#---------------------------------------------------------------
sub _getPriceLevelID {
   my($s) = @_;
   return $gPriceLevelMap{lc $s};
}# _getPriceLevelID

#-----------------------------------------------------------------------
# _getProductTypeID - maps RSCOMMON.income_source IDs to product_type_id
# (see RSCOMMON.product_type for ID values)
#-----------------------------------------------------------------------
sub _getProductTypeID {
   my($srcID) = @_;
   assert($srcID);
   return $gProductTypeMap{$srcID};
}# _getProductTypeID

#---------------------------------
# _getProductID - find the product
#---------------------------------
sub _getProductID {
   my(%args) = @_;
   my $productID;
   my $albumID       = $args{album_id};
   my $productTypeID = $args{product_type_id};
   assert($albumID);
   assert($productTypeID);
   my $p = RPS::DB::Item::Product->Lookup(
      asset_id => $albumID,
      product_type_id => $productTypeID,
   );
   $productID = $p->product_id if ( $p );
   return $productID;
}# _getProductID

#-------------------------------------------------------------
# _findRegion - returns the region ID of the specified region,
# or undef if the region is invalid
#-------------------------------------------------------------
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;
}# _findRegion

#--------------------------------------------------------------
# _findContract - look for a contract and return the contractID
# if found, undef otherwise
#--------------------------------------------------------------
sub _findContract {
   my(%args) = @_;
   my $contractID;
   my $title = $args{title};

   my %sArgs;
   $sArgs{title} = $title if ( $title );

   if ( (keys %sArgs) > 0 ) {
      my $o = RPS::DB::Item::NewArtistContract->Lookup( %sArgs );
      $contractID = $o->artist_contract_id if ( $o );
   }
   return $contractID;
}# _findContract

#-----------------------------------------------
# _getProductPrice - determine a product's price
#-----------------------------------------------
sub _getProductPrice {
   my ( $productID, $rateTypeID ) = @_;
   my $price;
   my $sql;
   if ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentRetail ) {
      $sql = "SELECT p.retail FROM product_price pp ".
             "LEFT JOIN price p ON p.price_id = pp.price_id ".
             "WHERE pp.product_id = $productID";
   } elsif ( $rateTypeID == RPS::DB::Item::ContractRateType::kRateTypePercentWholesale ) {
      $sql = "SELECT p.wholesale FROM product_price pp ".
             "LEFT JOIN price p ON p.price_id = pp.price_id ".
             "WHERE pp.product_id = $productID";
   }
   if ( $sql ) {
      report("      _getProductPrice: SQL = $sql");
      my $sth = $dbo->DoCmd($sql);
      ($price) = $sth->fetchrow_array();
   }
   return $price;
} #_getProductPrice

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

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

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

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

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

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

sub _printNull {
   my($v) = @_;
   return $v ? $v : "NULL";
   #return (! defined $v) ? '---' : $v;
}

1;