package Support::Implementation::LiquidationAdjustmentTemplate;
#-------------------------------------------------------------
# Starting with r1, this template supports the adjustment of
# artist reserves _only_.
#
# Also, we only allow the redistribution of current period reserves
# such that they release in one or more following periods.
#
# Forcing or accelerating the release of reserves to the current
# period is not yet implemented.  (3/22/12)
#
# 4/23/10:
#  - Updated all album, track and contract title comparisons
#    to be case-insensitive. -ES
#  - Changed price_level reference to RSCOMMON.
# 3/21/12:
#  - Updated term lookup to use contract_term_source
# 8/21/12:
#  - Cleaned up _validateProductType
# 9/4/14:
#  - Added 'all' and 'all physical' to _validateContractTermSource.  This
#    is to allow for terms that changed from a specific physical contract
#    term source to a more generic one after reserves have been held.
# 8/28/18:
#  - Added XLSX support
#-------------------------------------------------------------
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 List::Util qw(sum);

use Spreadsheet::ParseExcel;

#use Support::Implementation::ImplementationUtil qw(_findTrack _findAlbum report
#   appendString kAlbumNotFound kNonUniqueAlbumName kTrackNotFound);
use Support::Implementation::ImplementationUtil qw( report appendString checkMissingColumn printNull );

use Common::Util qw(clean);

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

use RPS::DB::Item::NewArtistContract;
use RPS::DB::Item::NewArtistContractTerm;
use RPS::DB::Item::IncomeSource;
use RPS::DB::Item::ContractRateType;
use RPS::DB::Item::ReserveLiquidation;

use RPS::DB::Item::LicenseReserve;
use RPS::DB::Item::CALicenseReserve;
use RPS::DB::Item::ArtistContractTermReserve;

use RPS::DB::Item::Album;
use RPS::DB::Item::Track;
use RPS::DB::Item::AlbumContract;
use RPS::DB::Item::TrackContract;

use RPS::DB::Item::ArtistPayee;
use RPS::DB::Item::Payor;
use RPS::DB::Item::Region;

use Support::Implementation::ExcelReader;
use Support::Implementation::TabDelimitedReader;

#use Support::Implementation::SearchUtil qw ( report );
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 kDuplicateTerm       => 1;
use constant kDefaultTermNotLast  => 2;

binmode STDOUT, ":utf8";


#use constant kClassAttributes => qw( name exec_mode client_id );

#-----------------------------------------------------------------------
# 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.
# 
# TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO
#
# Should modify logic to reject template if the column names don't
# match _exactly_.  See line 78 of ExcelReader.pm (scanExcelFile); this
# is where spaces are removed from the column name.
#-----------------------------------------------------------------------
my %gTemplateHeader = (
   "current-period"  => 0,  # A
   "payor"           => 1,  # B
   "rs-contract-id"  => 2,  # C
   "contract-title"  => 3,  # D
   "rs-album-id"     => 4,  # E
   "album-name"      => 5,  # F
   "rs-track-id"     => 6,  # G
   "track-name"      => 7,  # H
   "source"          => 8,  # I
   "current"         => 9,  # J
   "P1"              => 10, # K
   "P2"              => 11, # L
   "P3"              => 12, # M
   "P4"              => 13, # N
   "P5"              => 14, # O
   "P6"              => 15, # P
   "P7"              => 16, # Q
   "P8"              => 17, # R
);

#--------------------------------------------------------------
# Maps column #'s to a unique key (headername).  The reverse of
# templateHeader, but with actual column #.
#--------------------------------------------------------------
my %gColumnMap = ();
sub parseHeader {
   my $self = shift;
}

#my $app; # app singleton
my $dbo;
my $dbh;
my $cdbo;
my $clientID;
my $execMode;

my %gRegionMap;
#my %gIncomeSourceMap;
my %gChannelMap;
my %gPriceLevelMap;
my %gContractRateTypeMap;
my %gContractTermSourceMap;

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

   $self->{dbo} = $dbo;

   return $self->_init(%args);
}

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

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

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

   my $clientID = $self->client_id;

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

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

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

   #------------------------------
   # Build a list of valid regions
   #------------------------------
   my $sql = "SELECT region_id, name FROM region";
   my $sth = $dbo->DoCmd($sql);
   while( my($id,$name) = $sth->fetchrow_array() ) {
      $gRegionMap{ lc $name } = $id;
   }

   #-------------------------------------
   # Build a list of valid income sources
   #-------------------------------------
   #$sql = "SELECT income_source_id, format, name FROM income_source";
   #$sth = $cdbo->DoCmd($sql);
   #while( my($id,$format,$name) = $sth->fetchrow_array() ) {
   #   my $val = join("\t",$format,$id);
   #   $gIncomeSourceMap{ lc $name } = $val;
   #}
   $sql = "SELECT contract_term_source_id, name FROM contract_term_source";
   $sth = $cdbo->DoCmd($sql);
   while( my($id,$name) = $sth->fetchrow_array() ) {
      $gContractTermSourceMap{ lc $name } = $id;
   }
   # Setup aliases
   $gContractTermSourceMap{ 'all products'} = 1;

   $gContractTermSourceMap{ 'all-p'} = 2;
   $gContractTermSourceMap{ 'all physical products'} = 2;

   $gContractTermSourceMap{ 'all-d'} = 3;
   $gContractTermSourceMap{ 'all digital products'} = 3;


   #-------------------------------
   # Build a list of valid channels
   #-------------------------------
   $sql = "SELECT channel_id, name FROM channel";
   $sth = $dbo->DoCmd($sql);
   while( my($id,$name) = $sth->fetchrow_array() ) {
      $gChannelMap{ lc $name } = $id;
   }
   $gChannelMap{all} = 0;

   #-----------------------------------
   # Build a list of valid price levels
   #-----------------------------------
   $sql = "SELECT price_level_id, name FROM price_level";
   $sth = $cdbo->DoCmd($sql);
   while( my($id,$name) = $sth->fetchrow_array() ) {
      $gPriceLevelMap{ lc $name } = $id;
      $gPriceLevelMap{album} = $id if ( $name =~ m/^album/ );
      $gPriceLevelMap{track} = $id if ( $name =~ m/^track/ );
   }
   $gPriceLevelMap{all} = 0;

   #------------------------------------------
   # Build a list of valid contract rate types
   #------------------------------------------
   $sql = "SELECT contract_rate_type_id, name FROM contract_rate_type";
   $sth = $dbo->DoCmd($sql);
   while( my($id,$name) = $sth->fetchrow_array() ) {
      $gContractRateTypeMap{ lc $name } = $id;

      # store synonyms that are used by the template specification 1.1
      $gContractRateTypeMap{ retail } = $id if ( $name =~ m/retail/i );
      $gContractRateTypeMap{ wholesale } = $id if ( $name =~ m/wholesale/i );
      $gContractRateTypeMap{ revenue } = $id if ( $name =~ m/revenue/i );
      $gContractRateTypeMap{ "net revenue" } = $id if ( $name =~ m/revenue/i );
      $gContractRateTypeMap{ fixed } = $id if ( $name =~ m/fixed/i );
   }

   return $self;
}

sub getHeader {
   my $self = shift;
   #shift->{gTemplateHeader};
   return %gTemplateHeader;
}

sub getColumnMap {
   my $self = shift;
   return %gColumnMap;
}

sub getDbo {
   my $self = shift;
   return $dbo;
}

# Reference to SearchUtil object
my $gSearchObj;

my $gClientID;

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

   my $clientID = $self->client_id;
   $gClientID = $clientID;
   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,
   );

   my $fileName = $self->name;

   if( $self->isExcel2003( $fileName ) ) {
      print("LiquidationAdjustmentTemplate::loadMemory -- loading Excel2k3 $fileName into memory\n");

      #--------------------------
      # Read in the 1st worksheet
      #--------------------------
      my %data;
      my $reader = Support::Implementation::ExcelReader->new(
         filename => $fileName,
         data => \%data,
         header => \%gTemplateHeader,
         columnmap => \%gColumnMap,
         tab => 1,
      );
      $reader->scanExcelFile();

      #-----------------
      # process the data
      #-----------------
      _processData(\%data);

   } elsif( $self->isExcel2007( $fileName ) ) {
      print("LiquidationAdjustmentTemplate::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 ) ) {
      #die("Whoa.... I don't know how to do this with tab-delimited files!!!\n");
      report("LiquidationAdjustmentTemplate::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 contract
# data.
#-------------------------------------------------------------------
sub _processData {

   my($data) = @_;
   my $rows = $data->{rows};

   #-----------------------------------------------------------------------
   # Hash of all contracts seen.  We'll use this hash to detect if template
   # lines aren't grouped together properly.
   #-----------------------------------------------------------------------
   my %seenContractMap = ();

   # TBD
   my %excount = (
      contract_not_found  => 0,
      album_not_found     => 0,
      tracK_not_found     => 0,
   );

   #--------------------------------------
   # rowcount keeps track of line counters
   #--------------------------------------
   my %rowcount = (
      rows_failed => 0,
      total       => 0,
   );

   #----------------------------------------------------------
   # entities: if we create any RPS entities, we'll keep track
   # of the totals in this hash
   #----------------------------------------------------------
   my %entities = (
      license_reserve              => 0,
      ca_license_reserve           => 0,
      artist_contract_term_reserve => 0,
   );

   #================================================================
   # We'll use the following variables to keep track of the contract
   # whose terms we are processing.
   #================================================================

   # Keep track of the number of contracts processed
   my $contractCounter = 0;

   #--------------------------------------------------------------------
   # errorCode will hold one or more error messages for the current line
   #--------------------------------------------------------------------
   my $errorCode;

   use constant kColumnCurrentPeriod    => 'current-period';
   use constant kColumnPayor            => 'payor';
   use constant kColumnRSContractID     => 'rs-contract-id';
   use constant kColumnContractTitle    => 'contract-title';
   use constant kColumnRSAlbumID        => 'rs-album-id';
   use constant kColumnAlbumName        => 'album-name';
   use constant kColumnRSTrackID        => 'rs-track-id';
   use constant kColumnTrackName        => 'track-name';
   use constant kColumnSource           => 'source';
   use constant kColumnCurrent          => 'current';
   use constant kColumnP1               => 'P1';
   use constant kColumnP2               => 'P2';
   use constant kColumnP3               => 'P3';
   use constant kColumnP4               => 'P4';
   use constant kColumnP5               => 'P5';
   use constant kColumnP6               => 'P6';
   use constant kColumnP7               => 'P7';
   use constant kColumnP8               => 'P8';

   my @requiredColumns;
   push @requiredColumns, kColumnCurrentPeriod;  # A
   push @requiredColumns, kColumnPayor;          # B
   push @requiredColumns, kColumnRSContractID;   # C
   push @requiredColumns, kColumnContractTitle;  # D
   push @requiredColumns, kColumnRSAlbumID;      # E
   push @requiredColumns, kColumnAlbumName;      # F
   push @requiredColumns, kColumnRSTrackID;      # G
   push @requiredColumns, kColumnTrackName;      # H
   push @requiredColumns, kColumnSource;         # I
   push @requiredColumns, kColumnCurrent;        # J
   push @requiredColumns, kColumnP1;             # K
   push @requiredColumns, kColumnP2;             # L
   push @requiredColumns, kColumnP3;             # M
   push @requiredColumns, kColumnP4;             # N
   push @requiredColumns, kColumnP5;             # O
   push @requiredColumns, kColumnP6;             # P
   push @requiredColumns, kColumnP7;             # Q
   push @requiredColumns, kColumnP8;             # R

   #--------------------------
   # Process each row in order
   #--------------------------
   foreach my $row (@$rows) {
#
      #die("STOP\n") if ( $contractCounter > 5 );

      #-------------------------------------------------------
      # Get all of the template variables.
      # Note: the keys listed are defined in %gTemplateHeader.
      #-------------------------------------------------------
      my $rowid             = $row->{rowid};

      my $currentPeriod  = $row->{ kColumnCurrentPeriod() };
      my $payor          = $row->{ kColumnPayor()         };
      my $rsContractID   = $row->{ kColumnRSContractID()  };
      my $contractTitle  = $row->{ kColumnContractTitle() };
      my $rsAlbumID      = $row->{ kColumnRSAlbumID()     };
      my $albumName      = $row->{ kColumnAlbumName()     };
      my $rsTrackID      = $row->{ kColumnRSTrackID()     };
      my $trackName      = $row->{ kColumnTrackName()     };
      my $source         = $row->{ kColumnSource()        };
      my $current        = $row->{ kColumnCurrent()       };
      my $p1             = $row->{ kColumnP1()            };
      my $p2             = $row->{ kColumnP2()            };
      my $p3             = $row->{ kColumnP3()            };
      my $p4             = $row->{ kColumnP4()            };
      my $p5             = $row->{ kColumnP5()            };
      my $p6             = $row->{ kColumnP6()            };
      my $p7             = $row->{ kColumnP7()            };
      my $p8             = $row->{ kColumnP8()            };

      my $schedStr = join("-",
         "p1(" . printNull($p1) . ")",
         "p2(" . printNull($p2) . ")",
         "p3(" . printNull($p3) . ")",
         "p4(" . printNull($p4) . ")",
         "p5(" . printNull($p5) . ")",
         "p6(" . printNull($p6) . ")",
         "p7(" . printNull($p7) . ")",
         "p8(" . printNull($p8) . ")"
      );


      report("#### [$rowid]  "
         . "currentPeriod(" . printNull($currentPeriod) . ") "
         . "payor("         . printNull($payor)         . ") "
         . "contractID("    . printNull($rsContractID)  . ") "
         . "contractTitle(" . printNull($contractTitle) . ") "
         . "albumID("       . printNull($rsAlbumID)     . ") "
         . "albumName("     . printNull($albumName)     . ") "
         . "trackID("       . printNull($rsTrackID)     . ") "
         . "trackName("     . printNull($trackName)     . ") "
         . "source("        . printNull($source)        . ") "
         . "current("       . printNull($current)       . ") "
         . "$schedStr\n" . Dumper( \%$row ));

      undef $errorCode;

      ++$rowcount{total};

      my $schedule = join("-",
         ($p1 || 0),
         ($p2 || 0),
         ($p3 || 0),
         ($p4 || 0),
         ($p5 || 0),
         ($p6 || 0),
         ($p7 || 0),
         ($p8 || 0),
      );

      my $liquidateNow;

      if ( $current eq '100' && $schedule eq '0-0-0-0-0-0-0-0' )
      {
          report("   ### Detected movement of all reserves to current period!!!");
          $liquidateNow = 1;
      }


      #-----------------------------------
      # Make sure all columns are in place
      #-----------------------------------
      my $s;
      foreach my $c (@requiredColumns) {
         $s = checkMissingColumn( $c, $row, \%excount );
         appendString( $errorCode, $s ) if ( $s );
      }

      if ( $errorCode ) {
         die("ERROR: template is missing the following column(s):\n$errorCode\n");
      }

      $payor =~ s/^\s*//;
      $payor =~ s/\s*$//;
      $contractTitle =~ s/^\s*//;
      $contractTitle =~ s/\s*$//;
      $albumName =~ s/^\s*//;
      $albumName =~ s/\s*$//;
      $trackName =~ s/^\s*// if ( $trackName );
      $trackName =~ s/\s*$// if ( $trackName );
      $source =~ s/^\s*//;
      $source =~ s/\s*$//;

      #----------------------------------------------------------------
      # 'maxBucket' - holds the highest bucket index with schedule info
      # defined.
      #----------------------------------------------------------------
      my $maxBucket;

      #--------------------------------------------------------------------------
      # Make sure the schedule totals 100%.  If it doesn't, then we're doing more
      # than adjusting reserves.
      #--------------------------------------------------------------------------
      my $schedTot=0;
      my $bi=0;
      foreach my $v (split("-",$schedule)) {
         $schedTot += $v;
         $maxBucket = $bi if ( $v > 0 );
         ++$bi;
      }
      $schedTot += $current;
      if ( $schedTot != 100 ) {
         report("ERROR: schedule($schedule) not 100%");
         appendString( $errorCode, "Incomplete schedule");
         ++$excount{incomplete_schedule};
      }


      #----------------------------------------------------------------
      #
      # Is there a contract defined?  The RPS contractID is required.
      # The contract title, if specified, must match the actual title
      # within RPS.
      #
      #----------------------------------------------------------------
      my $contractID; # set to RPS contractID
      if ( $rsContractID && '' ne $rsContractID ) {
         my $sql = "SELECT title "
            . "FROM new_artist_contract "
            . "WHERE artist_contract_id = $rsContractID";
         my $sth = $dbo->DoCmd($sql);
         if ( $sth->rows() == 0 ) {
            report("ERROR: contractID $rsContractID not found!");
            appendString( $errorCode, "Invalid contractID");
            ++$excount{invalid_contractid};
         } else {

            my ($zzTitle) = $sth->fetchrow_array();

            if ( $contractTitle && '' ne $contractTitle ) {
               if ( lc $contractTitle ne lc $zzTitle ) {
                  report("ERROR: contractID $rsContractID title mismatch: '$zzTitle' != '$contractTitle'");
                  appendString( $errorCode, "Contract title mismatch");
                  ++$excount{invalid_contract_title_mismatch};
               } else {
                  $contractID = $rsContractID;
               }
            } else {
               $contractID = $rsContractID;
            }
          
         }

      }


      #----------------------------------------------------------------
      #
      # Is there an album defined?  The RPS albumID is required ONLY if
      # we're adjusting album-level liquidations.  If the RPS albumID
      # is present, the album name (if specified) must match the name
      # within RPS.
      #
      #----------------------------------------------------------------
      my $albumID; # set to RPS albumID, if found

      if ( $rsAlbumID && '' ne $rsAlbumID ) {
         my $sql = "SELECT title "
            . "FROM album "
            . "WHERE album_id = $rsAlbumID";
         my $sth = $dbo->DoCmd($sql);
         if ( $sth->rows() == 0 ) {
            report("ERROR: albumID $rsAlbumID not found!");
            appendString( $errorCode, "Invalid albumID");
            ++$excount{invalid_albumid};
         } else {

            my ($zzTitle) = $sth->fetchrow_array();

            if ( $albumName && '' ne $albumName ) {
               if ( lc $albumName ne lc $zzTitle ) {
                  report("ERROR: albumID $rsAlbumID title mismatch: '$zzTitle' != '$albumName'");
                  appendString( $errorCode, "Album name mismatch");
                  ++$excount{invalid_albumname_mismatch};
               } else {
                  $albumID = $rsAlbumID;
               }
            } else {
               $albumID = $rsAlbumID;
            }
          
         }

      }

      #--------------------------------------------------------------
      #
      # Is there a track defined?  The RPS trackID is required ONLY
      # we're adjusting track-level liquidations.  If the RPS trackID
      # is present, the track name (if specified) must match the name
      # within RPS.
      #
      #--------------------------------------------------------------
      my $trackID; # set to RPS trackID, if found

      if ( $rsTrackID && '' ne $rsTrackID ) {
         my $sql = "SELECT title "
            . "FROM track "
            . "WHERE track_id = $rsTrackID";
         my $sth = $dbo->DoCmd($sql);
         if ( $sth->rows() == 0 ) {
            report("ERROR: trackID $rsTrackID not found!");
            appendString( $errorCode, "Invalid trackID");
            ++$excount{invalid_trackid};
         } else {

            my ($zzTitle) = $sth->fetchrow_array();

            if ( $trackName && '' ne $trackName ) {
               if ( lc $trackName ne lc $zzTitle ) {
                  report("ERROR: trackID $rsTrackID title mismatch: '$zzTitle' != '$trackName'");
                  appendString( $errorCode, "Track name mismatch");
                  ++$excount{invalid_trackname_mismatch};
               } else {
                  $trackID = $rsTrackID;
               }
            } else {
               $trackID = $rsTrackID;
            }
          
         }

      }

#
# TODO: Check if both album and track are defined.  Exception if so.
# The reason they're mutually exclusive is that if this line is for
# track-level liquidation, then the track _must_ be identified via a
# track identifier.
#

      #----------------------------------------------------------------------
      # Get the source.  This information will be used to find the product(s)
      # for which reserves will be adjusted.  It will also be used to find
      # the track licenses or artist contract terms when adjusting publisher
      # and artist payee reserves, respectively.
      #----------------------------------------------------------------------


      #--------------------------------------------------------------------
      # The human-readable source field from the template is used in two
      # ways:
      # 1) We use it to help identify the contract term(s)
      # contract_term_source_id that is associated with each contract term.
      # 'sourceList' will be used to identify the contract term.
      #--------------------------------------------------------------------

      my @sourceList = _validateContractTermSource($source);

      if ( !@sourceList ) {
         die("Found no source IDs for source($source)");
      }


      #-------------------------------------------------------------------
      # For looking up products, we need to map the 'source' column to one
      # or more product_type entries.
      #-------------------------------------------------------------------
      my @productTypes;
      @productTypes = _validateProductType($source);
      if ( !@productTypes ) {
         die("Found no productTypeIDs for source($source)");
      }

      report("  DEBUG: sourceList ". join(", ", @sourceList) );
      report("  DEBUG: productTypes ". join(", ", @productTypes) );

      #----------------------------------------------------------------
      #
      # If neither contract  nor album nor track was specified, then
      # there is nothing to adjust.
      #
      #----------------------------------------------------------------
      if ( (($rsContractID && '' eq $rsContractID) || !$rsContractID) &&
           (($rsTrackID && '' eq $rsTrackID) || !$rsTrackID) &&
           (($trackName && '' eq $trackName) || !$trackName) &&
           (($rsAlbumID && '' eq $rsAlbumID) || !$rsAlbumID) &&
           (($albumName && '' eq $albumName) || !$albumName) ) {

         report("ERROR: Neither contract or album/track specified");
         appendString( $errorCode, "No contract or album/track specified");
         ++$excount{Missing_contract_album_track};
      }

      #-------------------
      #
      # Validate the payor
      #
      #-------------------
      my $payorID;
      if ( $payor && '' ne $payor ) {
         my $o = RPS::DB::Item::Payor->Lookup( name => $payor );
         if ( !$o ) {

            report("ERROR: Payor '$payor' not found");
            appendString( $errorCode, "Payor not found");
            ++$excount{payor_not_found};

         } else {

            $payorID = $o->payor_id;
            report("  DEBUG: found payor $payor (payorID = $payorID)");

         }
      }

      #-----------------------------------------#
      #                                         #
      # Stop processing if there are any errors #
      #                                         #
      #-----------------------------------------#
      if ( $errorCode && '' ne $errorCode ) {
         $row->{'error-code'} = $errorCode if ( $errorCode && '' ne $errorCode );
         ++$rowcount{rows_failed};
         next;
      }


      #
      #
      #
      # Adjust reserves about here...
      #
      #
      #

      # There are two activities we can do with artist reserves.
      #
      #
      # We can take reserves scheduled to be released in the current period and
      # adjust them so that they are spread out over following periods.
      #
      # or
      #
      # We can take reserves scheduled to be released in following periods and
      # move them up such that they release in the current period.
      #


      #-----------------------------------------------------------------------
      # Create the list of product(s) whose reserves will be modified.
      #
      #*** Artist Reserves:
      #
      # For a given contract, the products we're interested in are the ones
      # that are attached (via an artist_contract_term_reserve) to the
      # specified income source.
      #
      # Really all we need to find this is the productID.
      #
      #-----------------------------------------------------------------------

      # 'products' - if an albumID was defined, then this will contain a list
      # of relevant products (we use the source to determine exactly which
      # products types to look for.
      my @products;

      if ( $albumID ) {
         report("   Searching for products using albumID($albumID)");
         my $sql = "SELECT product_id FROM product "
            . "WHERE asset_id=$albumID "
            . "AND product_type_id IN (". join(",",@productTypes) . ")" ;
         my $sth = $dbo->DoCmd($sql);
         while( my($productID) = $sth->fetchrow_array() ) {
            push @products, $productID;
         }
      } elsif( $trackID ) {
         report("   Searching for products using trackID($trackID)");
         my $sql = "SELECT product_id FROM product p "
            . "JOIN album a ON a.album_id=p.asset_id "
            . "JOIN track t ON t.album_id=a.album_id "
            . "WHERE p.asset_id=a.album_id "
            . "AND p.product_type_id IN (". join(",",@productTypes) . ") "
            . "AND t.track_id=$trackID ";
         my $sth = $dbo->DoCmd($sql);
         while( my($productID) = $sth->fetchrow_array() ) {
            push @products, $productID;
         }
      }
      report("  DEBUG: found product(s): " . join(", ",@products) );

      #----------------------------------------------------------------------
      # 'reservesUpdated' set if _any_ reserves are found matching the payor,
      # payee and product criteria.  This is used to detect the case where
      # no matching reserves were found.
      #----------------------------------------------------------------------
      my $reservesFound;

      #----------------------------------------------------------------------
      # Determine the contract terms to be examined for reserves.
      # Basically, gather all the terms with the relevant contract term source
      # id(s), and then check each term to see it is related to a product (as
      # stored in @products) via an artist reserve.  If so, then modify the
      # reserve.
      #----------------------------------------------------------------------
      my $sql = "SELECT c.artist_contract_id, c.title, ct.artist_contract_term_id "
         . "FROM new_artist_contract c "
         . "JOIN new_artist_contract_term ct USING(artist_contract_id) ";

      if ( lc $source eq 'all products' )
      {
         # We need to search for terms with specific incomeSourceIDs as well
         # as terms with a NULL incomeSourceID (meaning 'all products')

         die("TODO");  # should work now that we're using _validateContractTermSource above
                       # leaving 'die' here until this is fully tested though

         #$sql .= "WHERE ( ct.income_source_id IN ( ". join(",",@sourceList) .") "
         #        . " || ct.income_source_id IS NULL ) "; 
      }
      else
      {
         $sql .= "WHERE ct.contract_term_source_id IN ( ". join(",",@sourceList) .") ";

      }

      $sql .= "AND c.artist_contract_id=$contractID ";
      $sql .= "AND c.payor_id=$payorID ";


      #---------------------------------------------------------------
      # Get all the contracts and terms associated with the payor and
      # (optionally) a payee.
      #---------------------------------------------------------------
      my $sth = $dbo->DoCmd($sql);
if( $sth->rows == 0 )
{
    print "####\n#### WARNING: No contract/terms found: $sql\n";
}
      while(my($contractID, $title, $termID) = $sth->fetchrow_array() ) {

         my $hdrShown;
         my $msg = "   ### Examining contract $contractID title($title) termID($termID)";

print ">>> $msg\n";
         # Check if the contract is attached to the album or any of its
         # tracks.  If the latter, and the user didn't specify any trackIDs,
         # then modify only those reserves related to the tracks that are
         # currently attached to the contract.
         my $sql2 = "SELECT album_contract_id FROM album_contract "
            . "WHERE artist_contract_id=$contractID "
            . "AND album_id=$albumID";
         my $sth2 = $dbo->DoCmd($sql2);

         # If trackContractMap has any entries, then the contract is not
         # attached to the album.
         my %trackContractMap;
         if ( $sth2->rows == 0 ) {
            my $sql2 = "SELECT track_id, track_contract_id FROM track_contract "
               . "WHERE artist_contract_id=$contractID "
               . "AND track_id IN "
               . "  (SELECT track_id FROM track WHERE album_id=$albumID)";
            my $sth2 = $dbo->DoCmd($sql2);
            while( my($zzTrackID, $tcID) = $sth2->fetchrow_array() ) {
               $trackContractMap{$zzTrackID} = $tcID;
            }
         }

         if ( !$liquidateNow )
         {
            $reservesFound = _liquidateReservesLater( {
               term_id => $termID,
               album_id => $albumID,
               products => @products,
               header_shown => $hdrShown,
               msg => $msg,
               current => $current,
               max_bucket => $maxBucket,
               track_contract_map => \%trackContractMap,
            } );
         }
         else
         {
print ">>> DEBUG: products: ". join(", ",@products) . "\n"; # XXX
            my $found = _liquidateReservesNow( {
               term_id => $termID,
               album_id => $albumID,
               products => \@products,
#               header_shown => $hdrShown,
#               msg => $msg,
               current => $current,
               max_bucket => $maxBucket,
#               track_contract_map => \%trackContractMap,
            } );
            $reservesFound = 1 if( $found );

         }
#         #------------------------------------------------------------
#         #
#         # Check if there are any reserves attached to the term.  If
#         # an album (product) was specified, then limit the search to
#         # reserves related to the product.  Otherwise, grab all
#         # reserves (well, those with periods_remaining=1).
#         #
#         #------------------------------------------------------------
#
#         $sql2 = "SELECT r.artist_contract_term_reserve_id, "
#            . "r.units, r.revenue_based, r.revenue, r.periods_remaining, "
#            . "r.product_id, r.album_id, r.track_id "
#            . "FROM artist_contract_term_reserve r "
#            . "WHERE r.artist_contract_term_id=$termID "
#            . "AND r.periods_remaining=1 ";
#         $sql2 .= "AND r.product_id IN (" .  join(",",@products) . ")" if ( scalar @products > 0 );
#
#         $sth2 = $dbo->DoCmd($sql2);
#         while( my($reserveID, $units, $revenueBased, $revenue, $per,
#                   $productID, $zzAlbumID, $zzTrackID ) = $sth2->fetchrow_array() ) {
#            if( !$hdrShown ) {
#               report($msg);
#               $hdrShown = 1;
#            }
#
#            # Sanity check -- make sure that the albumID on the reserve
#            # matches the albumID we're expecting
#            die("albumID($albumID) doesn't match reserve $reserveID albumID($zzAlbumID)")
#               if ( $albumID != $zzAlbumID );
#
#            die("trackID($trackID) doesn't match reserve $reserveID trackID($zzTrackID)")
#               if ( $trackID && $trackID != $zzTrackID );
#
#            # If the user didn't specify a trackID and the contract is not attached
#            # to the album, we need to make sure that we only adjust reserves that
#            # apply to the tracks currently attached to the contract.
#            if ( !$trackID && $zzTrackID != 0 ) {
#               if ( not exists $trackContractMap{$zzTrackID} ) {
#                  report("         WARNING: Skipping reserves for trackID($zzTrackID), "
#                     . "not attached to contractID(12)");
#                  next;
#               }
#            }
#            $reservesFound = 1;
#
#            # This is where the fun happens..
#
#            my $rObj = RPS::DB::Item::ArtistContractTermReserve->Lookup(
#               artist_contract_term_reserve_id => $reserveID
#            );
#
#            #---------------------------------------------------------
#            # Split the current reserves and spread them out according
#            # to the schedule defined in the template.  We don't touch
#            # the reserves for subsequent runs; if the client wants to
#            # adjust those then they can submit another request after
#            # this run.
#            #---------------------------------------------------------
#            my @buckets; # will hold revenue or units to distribute...
#            my $bi=0;    # bucket index
#
#
#
#            if ( !$revenueBased && $units == 1 ) {
#
#               # If this is a unit-based reserve and there's only one unit,
#               # then there's nothing to split...
#               report("         reserve($reserveID) has $units unit -- can't split");
#               next;
#            }
#
#            #------------------------------------------------------------
#            # 'startingAmount' is the amount that will be released in the
#            # next run unless we do something about it...
#            #------------------------------------------------------------
#            my $startingAmount = ($revenueBased) ? $revenue: $units;
#
#            #-------------------------------------------------------------
#            # 'balance' holds the amount that we need to spread out over
#            # one or more periods.  Typically this will be some percentage
#            # ('current') of the starting amount.
#            #-------------------------------------------------------------
#            my $balance;
#            if ( $current == 0 ) {
#               # We're postponing _all_ reserves for this run
#               $balance = $startingAmount;
#
#            } else {
#               if ( $revenueBased ) {
#                  $balance = $startingAmount * $current/100;
#               } else {
#                  $balance = round($startingAmount * $current/100);
#               }
#            }
#
#            #------------------------------------------------------
#            # 'newCurrentAmount' this will be the new value for the
#            # existing reserve entry that is to be released now.
#            #------------------------------------------------------
#            my $newCurrentAmount = $startingAmount - $balance;
#
#            #--------------------------------------------------------------
#            # 'total2Release' is the total number of units that will be
#            # released over time.  The sum of 'newCurrentAmount' and
#            # 'total2Release' MUST be equal to 'startingAmount', otherwise
#            # we're actually losing reserves instead of adjusting their
#            # release.
#            #--------------------------------------------------------------
#            my $total2Release = $balance;
#
#            report("      reserve($reserveID) productID($productID) "
#               . "units($units) revenue($revenue) PCT($current) per($per) "
#               . "newAmount($newCurrentAmount) total2Release($total2Release) "
#               . "balance($balance)");
#
#            my @schedList = split("-",$schedule);
#
#            #--------------------------------------------------------------
#            # The following loop basically determines how much (units or
#            # revenue) to release in each period as defined by the schedule
#            # in the template.  We liquidate relative to 'total2Release'.
#            #--------------------------------------------------------------
#            foreach my $percent (@schedList) {
#
#               if ( $percent > 0 && $balance > 0 ) {
#                  my $amount; # amount to release
#
#                  if ( $total2Release == 1 &&  0 == $revenueBased ) {
#                     $amount = 1;
#                     report("ALPHA: total2Release($total2Release) balance($balance) revenueBased($revenueBased)");
#                  } else {
#                     report("BRAVO: total2Release($total2Release) balance($balance) revenueBased($revenueBased)");
#                     if ( !$revenueBased ) {
#                        # can't have partial units, so round up
#                        #$amount = round( ($units * $percent)/100 );
#                        $amount = round( ($total2Release * $percent)/100 );
#                        if ( $amount == 0 ) {
#                           $amount = 1;
#                        }
#                     } else {
#                        $amount = $total2Release * ($percent/100);
#                     }
#                  }
#
#                  if ( ($balance - $amount)  < 0 ) {
#
#                     $amount = $balance;
#                     $balance = 0;
#
#                  } else {
#
#                     #--------------------------------------------------
#                     # If this is the last bucket to liquidate anything,
#                     # then add-in the balance so that it goes away too.
#                     #--------------------------------------------------
#                     if ( $bi == $maxBucket ) {
#                        $amount = $balance;
#                     } else {
#                        $balance -= $amount;
#                     }
#                  }
#                  #$balance = 0 if ( $balance < 0 );
#
#                  $buckets[$bi] = $amount;
#                  report("        [$bi] $percent\% = $amount (balance=$balance)");
#               }
#
#               $bi++;
#            }# liquidation calculation loop
#
#            report("        ------------");
#
#            #------------------------------------------------------------
#            #
#            # Sanity check: make sure the total amount to be released (as
#            # defined in the 'buckets' list) is the same as the amount we
#            # actually expect to see released.
#            #
#            #------------------------------------------------------------
#
#            my $total=0;
#            foreach my $b (@buckets) {
#               $total += $b;
#            }
#            if ( $total != $total2Release ) {
#               # shouldn't happen if we correctly checked the schedule total
#               # near the beginning of processData
#               die("        ERROR: total($total) != total2Release($total2Release)");
#            }
#
#            # ok, lets get the revised reserves into the system
#
#            #---------------------------------------------------------------
#            #
#            # First, update the existing reserve (the one whose amount needs
#            # to be spread out over time).
#            #
#            #---------------------------------------------------------------
#
#            if ( $execMode ) {
#               if ( $revenueBased ) {
#                  $rObj->revenue($newCurrentAmount);
#                  $rObj->save();
#                  report("         Changed reserve $reserveID from revenue "
#                     . "$startingAmount to $newCurrentAmount ($current\%)");
#               } else {
#                  $rObj->units($newCurrentAmount);
#                  $rObj->save();
#                  report("         Changed reserve $reserveID from units "
#                     . "$startingAmount to $newCurrentAmount ($current\%)");
#               }
#               ++$entities{artist_contract_term_reserve_UPDATE};
#               report("");
#            } else {
#               report("         Non-exec mode: skipped changing reserve $reserveID from "
#                  . "$startingAmount to $newCurrentAmount ($current\%)");
#            }
#
#
#            #------------------------------------------------------------
#            #
#            # Next, we'll create new reserve buckets with the rest of the
#            # reserves...
#            #
#            # Note: any new reserves we create will start with periods
#            # remaining=2 (e.g., the run after the current run).
#            #
#            #------------------------------------------------------------
#            my $period=2;
#            foreach my $b (@buckets) {
#               #$total += $b;
#               #report("         b($b) subtotal($total)");
#
#               next if ( $b == 0 );
#
#               my %args = (
#                  artist_contract_term_id    => $rObj->artist_contract_term_id,
#                  income_source_id           => $rObj->income_source_id,
#                  region_id                  => $rObj->region_id,
#                  channel_id                 => $rObj->channel_id,
#                  price_level_id             => $rObj->price_level_id,
#                  original_statement_item_id => $rObj->original_statement_item_id,
#                  product_id                 => $rObj->product_id,
#                  product_format_id          => $rObj->product_format_id,
#                  #units => $b;
#                  #revenue => $b;
#                  price                      => $rObj->price,
#                  revenue_based              => $rObj->revenue_based,
#                  effective_rate             => $rObj->effective_rate,
#                  periods_remaining          => $period,
#                  #liquidated_run_id => $rObj->liquidated_run_id,
#                  album_id                   => $rObj->album_id,
#                  track_id                   => $rObj->track_id,
#               );
#               if ( $revenueBased ) {
#                  $args{revenue} = $b;
#               } else {
#                  $args{units} = $b;
#               }
#
#               if ( $execMode ) {
#                  my $o = RPS::DB::Item::ArtistContractTermReserve->Create( %args );
#                  $o->save();
#                  my $id = $o->artist_contract_term_reserve_id;
#                  report("         Created artist_contract_term_reserve $id based on $reserveID : ".
#                     Dumper(\%args));
#                  ++$entities{artist_contract_term_reserve};
#               } else {
#                  report("         Non-exec mode, skipped artist_contract_term_reserve creation: ".
#                     Dumper(\%args));
#               }
#
#               $period++;
#            }
#
#         }# reserve loop


      }# contract term loop


      if ( !$reservesFound ) {
         report("ERROR: No reserves found");
         appendString( $errorCode, "Reserves not found");
         ++$excount{reserves_not_found};
      }


      #----------------------------------------------------------------------
      # If any errors occurred during the album/track creation, log them here
      #----------------------------------------------------------------------
      if ( $errorCode && '' ne $errorCode ) {
         $row->{'error-code'} = $errorCode if ( $errorCode && '' ne $errorCode );
         ++$rowcount{rows_failed};
      }

      
      ++$contractCounter;
   }# template row loop

   report("DONE reading template rows...");

   _showExceptions( $rows );

   #-----------------------
   # Show import statistics
   #-----------------------
   report("##### S U M M A R Y #####");
   report("Entities Created:");
   foreach my $c (keys %entities) {
      my $v = $entities{$c};
      printf("%30s %6d\n", $c, $v);
   }
   report("Exceptions:");
   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(" ");

   report("Row Processing Summary:");
   foreach my $c (sort{ $a cmp $b } keys %rowcount) {
      my $v = $rowcount{$c};
      printf("%30s %6d\n", $c, $v);
   }

}#_processData

sub _liquidateReservesNow {
   my( $args ) = @_;
   report("#### _liquidateReservesNow: args = ". Dumper(\%$args) );
   my $termID = $args->{term_id};

   my $_products = $args->{products};
   my @products = @$_products;

#   my $albumID  = $args->{album_id};
#   my $trackID  = $args->{track_id};
#
#   my $msg  = $args->{msg};
#   my $current  = $args->{current};
#   my $schedule = $args->{schedule};
#   my $maxBucket = $args->{max_bucket};
#   my %trackContractMap = %{$args->{track_contract_map}};

   my $reservesFound;
#

   # Find all reserves that are scheduled to be released in subsequent periods
   my $sql = "SELECT r.artist_contract_term_reserve_id, "
      . "  r.units, "
      . "  r.price, "
      . "  r.effective_rate, "
      . "  r.revenue_based, "
      . "  r.periods_remaining "
      . "FROM artist_contract_term_reserve r "
      . "INNER JOIN artist_royalty_income_item ii ON "
      . "  ( ii.artist_royalty_income_item_id = r.original_statement_item_id ) "
      . "INNER JOIN artist_royalty_album ra ON "
      . "  ( ra.artist_royalty_album_id = ii.artist_royalty_album_id ) "
      . "INNER JOIN artist_royalty_statement rs ON "
      . "  ( rs.artist_royalty_statement_id = ra.artist_royalty_statement_id ) "
      . "INNER JOIN artist_royalty_run run ON "
      . "  ( run.artist_royalty_run_id = rs.artist_royalty_run_id ) "
      . "WHERE r.artist_contract_term_id=$termID "
      . "AND run.status IN (2,5) "
      . "AND r.product_id IN (". join(",",@products) . ") "
      . "AND r.periods_remaining > 0 "
      . "ORDER BY r.original_statement_item_id, r.periods_remaining "
      ;
   my $sth = $dbo->DoCmd($sql);

   my $totalUnits = 0;
   my $totalRevenue = 0;
   while( my($reserveID, $units, $price, $effRate, $revenueBased, $period) = $sth->fetchrow_array() )
   {
      next if ( $period == 1 );

      report("   #### Examining reserve $reserveID u($units) p($price) effRate($effRate) revBased($revenueBased)");
      $totalUnits += $units;
      $totalRevenue += ($units * $price * ($effRate/100));

      $reservesFound = 1;

      # Change the reserves to release in current period
      my $o = RPS::DB::Item::ArtistContractTermReserve->Lookup(
         artist_contract_term_reserve_id => $reserveID,
      );
      assert($o);

      report("UNDO:\tUPDATE artist_contract_term_reserve SET periods_remaining=$period "
         . "WHERE artist_contract_term_reserve_id=$reserveID");

      if ( $execMode )
      {
         $o->periods_remaining(1);
         $o->save();

         report("   >> Updated reserveID($reserveID) to release in period 1");
      }
      else
      {
         report("   >> Non-exec mode: Skipped update of reserveID($reserveID) (termID $termID) to release in period 1 instead of period $period");
      }
   }
   report("_liquidateReservesNow: termID($termID) products(". join(",",@products) . ") units($totalUnits) rev($totalRevenue)");
  

   #die("_liquidateReservesNow: termID($termID) products(". join(",",@products) . ") units($totalUnits) rev($totalRevenue) : sql = $sql");
   return $reservesFound;

}# _liquidateReservesNow

sub _liquidateReservesLater {

   my($args) = @_;
   my $termID   = $args->{term_id};
   my @products = $args->{products};
   my $albumID  = $args->{album_id};
   my $trackID  = $args->{track_id};
   my $hdrShown  = $args->{header_shown};
   my $msg  = $args->{msg};
   my $current  = $args->{current};
   my $schedule = $args->{schedule};
   my $maxBucket = $args->{max_bucket};
   my %trackContractMap = %{$args->{track_contract_map}};

   my $reservesFound;

   die("_liquidateReservesLater: termID($termID)");

   #------------------------------------------------------------
   #
   # Check if there are any reserves attached to the term.  If
   # an album (product) was specified, then limit the search to
   # reserves related to the product.  Otherwise, grab all
   # reserves (well, those with periods_remaining=1).
   #
   #------------------------------------------------------------

   my $sql2 = "SELECT r.artist_contract_term_reserve_id, "
      . "r.units, r.revenue_based, r.revenue, r.periods_remaining, "
      . "r.product_id, r.album_id, r.track_id "
      . "FROM artist_contract_term_reserve r "
      . "WHERE r.artist_contract_term_id=$termID "
      . "AND r.periods_remaining=1 ";
   $sql2 .= "AND r.product_id IN (" .  join(",",@products) . ")" if ( scalar @products > 0 );

   my $sth2 = $dbo->DoCmd($sql2);
   while( my($reserveID, $units, $revenueBased, $revenue, $per,
             $productID, $zzAlbumID, $zzTrackID ) = $sth2->fetchrow_array() ) {
      if( !$hdrShown ) {
         report($msg);
         $hdrShown = 1;
      }

      # Sanity check -- make sure that the albumID on the reserve
      # matches the albumID we're expecting
      die("albumID($albumID) doesn't match reserve $reserveID albumID($zzAlbumID)")
         if ( $albumID != $zzAlbumID );

      die("trackID($trackID) doesn't match reserve $reserveID trackID($zzTrackID)")
         if ( $trackID && $trackID != $zzTrackID );

      # If the user didn't specify a trackID and the contract is not attached
      # to the album, we need to make sure that we only adjust reserves that
      # apply to the tracks currently attached to the contract.
      if ( !$trackID && $zzTrackID != 0 ) {
         if ( not exists $trackContractMap{$zzTrackID} ) {
            report("         WARNING: Skipping reserves for trackID($zzTrackID), "
               . "not attached to contractID(12)");
            next;
         }
      }
      $reservesFound = 1;

      # This is where the fun happens..

      my $rObj = RPS::DB::Item::ArtistContractTermReserve->Lookup(
         artist_contract_term_reserve_id => $reserveID
      );

      #---------------------------------------------------------
      # Split the current reserves and spread them out according
      # to the schedule defined in the template.  We don't touch
      # the reserves for subsequent runs; if the client wants to
      # adjust those then they can submit another request after
      # this run.
      #---------------------------------------------------------
      my @buckets; # will hold revenue or units to distribute...
      my $bi=0;    # bucket index



      if ( !$revenueBased && $units == 1 ) {

         # If this is a unit-based reserve and there's only one unit,
         # then there's nothing to split...
         report("         reserve($reserveID) has $units unit -- can't split");
         next;
      }

      #------------------------------------------------------------
      # 'startingAmount' is the amount that will be released in the
      # next run unless we do something about it...
      #------------------------------------------------------------
      my $startingAmount = ($revenueBased) ? $revenue: $units;

      #-------------------------------------------------------------
      # 'balance' holds the amount that we need to spread out over
      # one or more periods.  Typically this will be some percentage
      # ('current') of the starting amount.
      #-------------------------------------------------------------
      my $balance;
      if ( $current == 0 ) {
         # We're postponing _all_ reserves for this run
         $balance = $startingAmount;

      } else {
         if ( $revenueBased ) {
            $balance = $startingAmount * $current/100;
         } else {
            $balance = round($startingAmount * $current/100);
         }
      }

      #------------------------------------------------------
      # 'newCurrentAmount' this will be the new value for the
      # existing reserve entry that is to be released now.
      #------------------------------------------------------
      my $newCurrentAmount = $startingAmount - $balance;

      #--------------------------------------------------------------
      # 'total2Release' is the total number of units that will be
      # released over time.  The sum of 'newCurrentAmount' and
      # 'total2Release' MUST be equal to 'startingAmount', otherwise
      # we're actually losing reserves instead of adjusting their
      # release.
      #--------------------------------------------------------------
      my $total2Release = $balance;

      report("      reserve($reserveID) productID($productID) "
         . "units($units) revenue($revenue) PCT($current) per($per) "
         . "newAmount($newCurrentAmount) total2Release($total2Release) "
         . "balance($balance)");

      my @schedList = split("-",$schedule);

      #--------------------------------------------------------------
      # The following loop basically determines how much (units or
      # revenue) to release in each period as defined by the schedule
      # in the template.  We liquidate relative to 'total2Release'.
      #--------------------------------------------------------------
      foreach my $percent (@schedList) {

         if ( $percent > 0 && $balance > 0 ) {
            my $amount; # amount to release

            if ( $total2Release == 1 &&  0 == $revenueBased ) {
               $amount = 1;
               report("ALPHA: total2Release($total2Release) balance($balance) revenueBased($revenueBased)");
            } else {
               report("BRAVO: total2Release($total2Release) balance($balance) revenueBased($revenueBased)");
               if ( !$revenueBased ) {
                  # can't have partial units, so round up
                  #$amount = round( ($units * $percent)/100 );
                  $amount = round( ($total2Release * $percent)/100 );
                  if ( $amount == 0 ) {
                     $amount = 1;
                  }
               } else {
                  $amount = $total2Release * ($percent/100);
               }
            }

            if ( ($balance - $amount)  < 0 ) {

               $amount = $balance;
               $balance = 0;

            } else {

               #--------------------------------------------------
               # If this is the last bucket to liquidate anything,
               # then add-in the balance so that it goes away too.
               #--------------------------------------------------
               if ( $bi == $maxBucket ) {
                  $amount = $balance;
               } else {
                  $balance -= $amount;
               }
            }
            #$balance = 0 if ( $balance < 0 );

            $buckets[$bi] = $amount;
            report("        [$bi] $percent\% = $amount (balance=$balance)");
         }

         $bi++;
      }# liquidation calculation loop

      report("        ------------");

      #------------------------------------------------------------
      #
      # Sanity check: make sure the total amount to be released (as
      # defined in the 'buckets' list) is the same as the amount we
      # actually expect to see released.
      #
      #------------------------------------------------------------

      my $total=0;
      foreach my $b (@buckets) {
         $total += $b;
      }
      if ( $total != $total2Release ) {
         # shouldn't happen if we correctly checked the schedule total
         # near the beginning of processData
         die("        ERROR: total($total) != total2Release($total2Release)");
      }

      # ok, lets get the revised reserves into the system

      #---------------------------------------------------------------
      #
      # First, update the existing reserve (the one whose amount needs
      # to be spread out over time).
      #
      #---------------------------------------------------------------

      if ( $execMode ) {
         if ( $revenueBased ) {
            $rObj->revenue($newCurrentAmount);
            $rObj->save();
            report("         Changed reserve $reserveID from revenue "
               . "$startingAmount to $newCurrentAmount ($current\%)");
         } else {
            $rObj->units($newCurrentAmount);
            $rObj->save();
            report("         Changed reserve $reserveID from units "
               . "$startingAmount to $newCurrentAmount ($current\%)");
         }
#         ++$entities{artist_contract_term_reserve_UPDATE}; XXX
         report("");
      } else {
         report("         Non-exec mode: skipped changing reserve $reserveID from "
            . "$startingAmount to $newCurrentAmount ($current\%)");
      }


      #------------------------------------------------------------
      #
      # Next, we'll create new reserve buckets with the rest of the
      # reserves...
      #
      # Note: any new reserves we create will start with periods
      # remaining=2 (e.g., the run after the current run).
      #
      #------------------------------------------------------------
      my $period=2;
      foreach my $b (@buckets) {
         #$total += $b;
         #report("         b($b) subtotal($total)");

         next if ( $b == 0 );

         my %args = (
            artist_contract_term_id    => $rObj->artist_contract_term_id,
            income_source_id           => $rObj->income_source_id,
            region_id                  => $rObj->region_id,
            channel_id                 => $rObj->channel_id,
            price_level_id             => $rObj->price_level_id,
            original_statement_item_id => $rObj->original_statement_item_id,
            product_id                 => $rObj->product_id,
            product_format_id          => $rObj->product_format_id,
            #units => $b;
            #revenue => $b;
            price                      => $rObj->price,
            revenue_based              => $rObj->revenue_based,
            effective_rate             => $rObj->effective_rate,
            periods_remaining          => $period,
            #liquidated_run_id => $rObj->liquidated_run_id,
            album_id                   => $rObj->album_id,
            track_id                   => $rObj->track_id,
         );
         if ( $revenueBased ) {
            $args{revenue} = $b;
         } else {
            $args{units} = $b;
         }

         if ( $execMode ) {
            my $o = RPS::DB::Item::ArtistContractTermReserve->Create( %args );
            $o->save();
            my $id = $o->artist_contract_term_reserve_id;
            report("         Created artist_contract_term_reserve $id based on $reserveID : ".
               Dumper(\%args));
#            ++$entities{artist_contract_term_reserve}; XXX
         } else {
            report("         Non-exec mode, skipped artist_contract_term_reserve creation: ".
               Dumper(\%args));
         }

         $period++;
      }

   }# reserve loop

   return $reservesFound;

}#_liquidateLater

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("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 $warningCode = $row->{'warning-code'};

      my $albumContractID = $row->{'rs-albumcontract-id'};
      my $trackContractID = $row->{'rs-trackcontract-id'};
      #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 = "___XXX___";
         $importStatus = "albumcontract($albumContractID)" if ( $albumContractID );
         $importStatus = "trackcontract($trackContractID)" if ( $trackContractID );
         $ecString = ($warningCode) ? $warningCode : '';
      }

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

#------------------------------------------------------------
# _validateSource will determine if the supplied source is
# a valid template value.  If it is, then a ID value will be
# returned.  The ID value will either be an income_source_id
# or product_type_id, depending on the supplied royalty type.
#------------------------------------------------------------
sub OBE_validateIncomeSource {
   my ($src) = @_;
   my @idlist;
  
   report("  DEBUG:_validateIncomeSource: src = $src");
   return if ( !$src || '' eq $src );

   $src = lc $src;
   push @idlist, 1  if ( $src eq 'cd'    || $src eq 'all products' || $src eq 'all physical products');
   push @idlist, 2  if ( $src eq 'lp'    || $src eq 'lp' || $src eq 'all products' || $src eq 'all physical products');
   push @idlist, 3  if ( $src eq 'cass'  || $src eq 'cass5' || $src eq 'all products' || $src eq 'all physical products');
   push @idlist, 15 if ( $src eq 'cd5'   || $src eq 'all products' || $src eq 'all physical products');
   push @idlist, 22 if ( $src eq 'dvd'   || $src eq 'all products' || $src eq 'all physical products');
   push @idlist, 14 if ( $src eq 'dvdcd' || $src eq 'all products' || $src eq 'all physical products');
   push @idlist, 23 if ( $src eq 'vhs'   || $src eq 'all products' || $src eq 'all physical products');

   # should we even bother adding in the digital IDs?
   push @idlist, 4 if ( $src eq 'da' || $src eq 'all products' || $src eq 'all digital products');
   push @idlist, 5 if ( $src eq 'dt' || $src eq 'all products' || $src eq 'all digital products');
   push @idlist, 6 if ( $src eq 'ds' || $src eq 'all products' || $src eq 'all digital products');
   push @idlist, 7 if ( $src eq 'dteth' || $src eq 'all products' || $src eq 'all digital products');

   return @idlist;
}# _validateIncomeSource

#-----------------------------------------------------------------
# _validateContractTermSource - return the contract_term_source_id
# value(s) associated with the specified source.
# These are _not_ the same as income_source_id's.  Also, we only
# care about physical sources since we're dealing with reserves.
#-----------------------------------------------------------------
sub _validateContractTermSource {
   my ($src) = @_;
   my @idlist;
  
   report("  DEBUG:_validateContractTermSource: src = $src");
   return if ( !$src || '' eq $src );

   $src = lc $src;
   push @idlist, 6  if ( $src eq 'cd'    || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 7  if ( $src eq 'lp'    || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 8  if ( $src eq 'cas'   || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 9  if ( $src eq 'dvdcd' || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 10 if ( $src eq 'cd5'   || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 11 if ( $src eq 'cd2'   || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 12 if ( $src eq 'dvd'   || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 13 if ( $src eq 'vhs'   || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 14 if ( $src eq 'lp5'   || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 28 if ( $src eq 'cas5'  || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 29 if ( $src eq 'bd'    || $src eq 'all products' || $src =~ /all physical/ );

   # should we even bother adding in the digital IDs?
#   push @idlist, 4 if ( $src eq 'da' || $src eq 'all products' || $src eq 'all digital products');
#   push @idlist, 5 if ( $src eq 'dt' || $src eq 'all products' || $src eq 'all digital products');
#   push @idlist, 6 if ( $src eq 'ds' || $src eq 'all products' || $src eq 'all digital products');
#   push @idlist, 7 if ( $src eq 'dteth' || $src eq 'all products' || $src eq 'all digital products');

   # include All Physical (=2) if a specific source type was specified
   #
   push @idlist, 2 if( $src !~ /all (products|physical)/i );

   return @idlist;
}# _validateIncomeSource

sub _validateProductType {
   my ($src) = @_;
   my @idlist;
   return if ( !$src || '' eq $src );
   $src = lc $src;
   push @idlist, 1  if ( $src eq 'lp'    || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 2  if ( $src eq 'cd'    || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 5  if ( $src eq 'vhs'   || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 6  if ( $src eq 'cass'  || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 7  if ( $src eq 'ep'    || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 9  if ( $src eq 'dvd'   || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 11 if ( $src eq 'cd5'   || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 12 if ( $src eq 'cas5'  || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 13 if ( $src eq 'dvdcd' || $src eq 'all products' || $src =~ /all physical/ );
   push @idlist, 14 if ( $src eq 'cd2'   || $src eq 'all products' || $src =~ /all physical/ );

   push @idlist, 3 if ( $src eq 'da' || $src eq 'all products' || $src =~ /all digital/ );
   push @idlist, 4 if ( $src eq 'dt' || $src eq 'all products' || $src =~ /all digital/ );
   return @idlist;
}# _validateProductType

sub round {
   my($number) = @_;
   return int($number + .5 * ($number <=> 0));
}

#sub _xappendString {
#   my($str,$v) = @_;
#
#   if ( $str ) {
#      my $cur = $str;
#      my $newstring = "$cur; $v";
#      $_[0] = $newstring;
#   } else {
#      $_[0] = $v;
#   }
#}# _xappendString
#
#sub report {
#   my($text, $level) = @_;
#   $level = kNormal unless $level;
#   if ( $level <= $gReportLevel ) {
#      print $text . "\n";
#   }
#}

1;