# 9/18/13 - Fixed DT product lookups
#
package Support::Implementation::CALicenseReservesTemplate;
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::CATrackLicense;
#use RPS::License::ReserveLiquidation;

use RPS::DB::Item::CALicenseReserve;
use RPS::DB::Item::Album;
use RPS::DB::Item::Track;
use RPS::DB::Item::Master;
use RPS::DB::Item::CATrackLicense;
use RPS::DB::Item::CAPublisher;
use RPS::DB::Item::Payor;
use RPS::DB::Item::Region;
use RPS::DB::Item::Product;
use RPS::DB::Item::ProductType;
#use RPS::DB::Item::PendingTransaction;
#use RPS::Finance::PendingTransaction;
#use RPS::DB::Item::FinanceAccount;
#use RPS::Finance::Account;

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

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-name"                 => 0,  # A
   "catalog-#"                  => 1,  # B
   "track-name"                 => 2,  # C
   "isrc"                       => 3,  # D
   "royaltyshare-publisher-id#" => 4,  # E
   "publisher"                  => 5,  # F
   "royaltyshare-license-id#"   => 6,  # G
   "license-config"             => 7,  # H
   "product-type"               => 8,  # I
   "region"                     => 9,  # J
   "sales-date",                => 10, # K
   "p0"                         => 11, # L
   "p1"                         => 12, # M
   "p2"                         => 13, # N
   "p3"                         => 14, # O
   "p4"                         => 15, # P
   "p5"                         => 16, # Q
   "p6"                         => 17, # R
   "p7"                         => 18, # S
#   "total"                      => 19, # T
);

#--------------------------------------------------------------
# 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 = (
   new_ca_license_reserve => 0,
);

#---------------------------------------------------------
# gStatRateMap contains the RSCOMMON.stat_rate information
#---------------------------------------------------------
my %gStatRateMap = ();

my $clientID;
my $execMode;

my $dbo;
my $dbh;

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


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

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

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

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

   return $self;
}

sub parseHeader {
   my $self = shift;
}

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

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

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

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

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

   my $fileName = $self->name;

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

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

      # Try and parse it...
      _processData(\%data);
   } elsif( $self->isTabDelimited( $fileName ) ) {
      report("CALicenseReservesTemplate::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,
      bad_license                => 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,
   );

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

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

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

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

      #-----------------------------------
      # Get all of the template variables.
      #-----------------------------------
      my $rowid               = $row->{rowid};
      my $albumName           = $row->{'album-name'};                 # A
      my $catalogNumber       = $row->{'catalog-#'};                  # B
      my $rsAlbumID           = $row->{'royaltyshare-album-id#'};     # C
      my $trackName           = $row->{'track-name'};                 # D
      my $isrc                = $row->{'isrc'};                       # E
      my $rsTrackID           = $row->{'royaltyshare-track-id#'};     # F

      my $rsPublisherID       = $row->{'royaltyshare-publisher-id#'}; # G
      my $publisherName       = $row->{'publisher'};                  # H
      my $rsLicenseID         = $row->{'royaltyshare-license-id#'};   # I
      my $licenseConfig       = $row->{'license-product-type'};       # J - was "license-config"
      my $productType         = $row->{'reserve-configuration'};      # K - was "product-type"
      my $region              = $row->{'region'};                     # L
      my $dateReservesTaken   = $row->{'sales-date'};                 # M
      my $p0                  = $row->{'p0'};                         # N
      my $p1                  = $row->{'p1'};                         # O
      my $p2                  = $row->{'p2'};                         # P
      my $p3                  = $row->{'p3'};                         # Q
      my $p4                  = $row->{'p4'};                         # R
      my $p5                  = $row->{'p5'};                         # S
      my $p6                  = $row->{'p6'};                         # T
      my $p7                  = $row->{'p7'};                         # U
#      my $total               = $row->{'total'};

      #-------------------------------------------------------
      # Check for obsolete columns -- error if these are found
      #-------------------------------------------------------
      my $obeLicenseConfig       = $row->{'license-config'}; # OBE
      my $obeProductType         = $row->{'product-type'};   # OBE

      die("ERROR: Column \"license-config\" is obsolete") if ( $obeLicenseConfig );
      die("ERROR: Column \"product-type\" is obsolete")   if ( $obeProductType );

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

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


      my $publisherID;
      my $regionID;

      my $productTypeID;
      my $licenseConfigID; # can be a product_type, or ALL or ALL-P

      my $licenseID;
      my $licenseRateType;
      my $pennyRate;
      my $licenseRatePercentage;
      my $licenseShare;
      my $licenseRateBasis;
      my $licenseLockDate;
      my $trackID;
      my $albumID;
      my $productID;

      #-----------------------------------
      # Check for missing template columns
      #-----------------------------------
      die("ERROR: column \"sales-date\" not found or contains no date!\n") if ( !$dateReservesTaken );

      die("ERROR: column \"p0\" not found! (should have p0 through p7)\n") if ( ! exists $row->{'p0'} );

      #----------------------------------------------------
      # NOTE: if the licenseID is defined, should we still
      # check things like region, license config, publisher
      # and track?
      #----------------------------------------------------

      #-----------------------------------------
      # 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 license region valid?
      #-----------------------------
      $regionID = _findRegion( name => $region );
      if ( !$regionID ) {
         _appendString( $errorCode, "Region not found");
         ++$excount{region_not_found};
      }

      #-----------------------------------------------------------------------
      # Is the product type valid?.  Note: the product type must be a tangible
      # product (e.g., not ALL or ALL-P or ALL-D).
      #-----------------------------------------------------------------------
      if ( !$productType ) {
         _appendString( $errorCode, "Missing product type");
         ++$excount{missing_product_type};
      } else {

         $productTypeID = _getProductTypeID( product_type => lc $productType );
         if ( !$productTypeID ||
              kProductTypeNotFound == $productTypeID ||
              RPS::DB::Item::ProductType::kAllPhysicalProducts == $productTypeID ||
              254 == $productTypeID )
         {
            _appendString( $errorCode, "Invalid product type");
            ++$excount{invalid_product_type};
         }
      }

      #-----------------------------
      # Is the license config valid?
      #-----------------------------
      if ( !$licenseConfig ) {
         #----------------------------------------------------------------
         # If the license config wasn't specified, but there was a valid
         # product type then use the product config as the license config.
         #----------------------------------------------------------------
         if ( !$productType ) {
            _appendString( $errorCode, "Missing license config");
            ++$excount{missing_license_config};
         } else {
            if ( $productTypeID && kProductTypeNotFound != $productTypeID ) {
               $licenseConfig = $productType;
            }
         }
      }
      
      if ( $licenseConfig ) {

         $licenseConfigID = _getProductTypeID( product_type => lc $licenseConfig );

         if ( $licenseConfigID && kProductTypeNotFound == $productTypeID ) {
            _appendString( $errorCode, "Invalid license config");
            ++$excount{invalid_license_config};
         }
      }

      #------------------------------------------------
      # Validate the publisher information if specified
      #------------------------------------------------
      my $actualName;
      if ( $rsPublisherID ) {
         my $pObj = RPS::DB::Item::CAPublisher->Lookup( ca_publisher_id => $rsPublisherID );
         if (!$pObj) {
            report("EXCEPTION: specified RS publisherID '$rsPublisherID' is invalid!");
            _appendString( $errorCode, "Invalid CAPublisherID");
            ++$excount{ca_publisherid_invalid};
         } else {
            $actualName = $pObj->publisher_name;
            if ( $publisherName && '' ne $publisherName && $actualName ne $publisherName ) {
               report("EXCEPTION: name on specified RS publisherID doesn't match template name!");
               _appendString( $errorCode, "PublisherID name mismatch");
               ++$excount{publisher_name_mismatch};
            }
            $publisherID = $rsPublisherID;
         }
      } else {
         $publisherID = _findPublisher(
            publisher_name => $publisherName
         );
      }
      if ( ! $publisherID ) {
         report("_EXCEPTION: publisher '$publisherName' not found");
         _appendString( $errorCode, "Publisher not found");
         ++$excount{publisher_not_found};
      }

      if ( $rsLicenseID ) {
         #----------------------------------------------------------------
         # If license information (aside from the licenseID) was specified,
         # make sure it's consistent with what's on the license.
         #----------------------------------------------------------------
         my $tlObj = RPS::DB::Item::CATrackLicense->Lookup(
            ca_track_license_id => $rsLicenseID
         );
         if ( !$tlObj ) {
            _appendString( $errorCode, "Invalid licenseID");
            ++$excount{bad_license};
         } else {
            my $err;

            my $tlPublisherID = $tlObj->ca_publisher_id;
            if ( $publisherID && $tlPublisherID != $publisherID ) {
               _appendString( $errorCode, "License publisher mismatch");
               ++$excount{license_publisher_mismatch};
               $err=1;
            }

            $trackID = $tlObj->track_id;

            if ( !$err ) {
               $licenseID             = $rsLicenseID;
               $licenseRateType       = $tlObj->rate_type;
               $pennyRate             = $tlObj->penny_rate;
               $licenseRatePercentage = $tlObj->rate_percentage;
               $licenseShare          = $tlObj->share;
               $licenseRateBasis      = $tlObj->rate_basis;
               $licenseLockDate       = $tlObj->lock_date;
            }
         }

      } else {
         #---------------
         # Find the track
         #---------------
         my $errFlag;
         ($trackID,$errFlag) = _findTrack(
            catalog_number => $catalogNumber,
            album_name => $albumName,
            track_name => $trackName,
            isrc => $isrc,
         );
         if ( $trackID ) {
            report("   Found track $trackID");
         } else {
            # TODO - need to store error information
            report("_EXCEPTION: Track not found, errFlag($errFlag)");
            $row->{'error_track_name'} = "Track '$trackName' not found";
            push @errorList, $row;
            if ( $errFlag == kTrackNotFound ) {
               _appendString( $errorCode, "Track not found");
               ++$excount{track_not_found};
            }

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

# 12/10/09 - the publisher was handled above.  don't repeat here.
#         #-------------------
#         # find the publisher
#         #-------------------
#         my $actualName;
#         if ( $rsPublisherID ) {
#            my $pObj = RPS::DB::Item::CAPublisher->Lookup( ca_publisher_id => $rsPublisherID );
#            $actualName = $pObj->publisher_name;
#            if ( $publisherName && '' ne $publisherName && $actualName ne $publisherName ) {
#               die("EXCEPTION: name on specified RS publisherID doesn't match template name '$publisherName'!");
#               _appendString( $errorCode, "PublisherID name mismatch");
#               ++$excount{publisher_name_mismatch};
#            }
#            $publisherID = $rsPublisherID;
#         } else {
#            $publisherID = _findPublisher(
#               publisher_name => $publisherName
#            );
#         }
#         if ( ! $publisherID ) {
#            report("_EXCEPTION: publisher '$publisherName' not found");
#            _appendString( $errorCode, "Publisher not found");
#            ++$excount{publisher_not_found};
#         }

      }

      #-------------------------------------------------------
      # If we have a trackID and a valid productTypeID, try to
      # find the product.
      #-------------------------------------------------------
      if ( $trackID && (kProductTypeNotFound != $productTypeID) ) {
         #----------------
         # Get the albumID
         #-----------------
         $albumID = _getTrackAlbumID($trackID) if ( $trackID );

         #-----------------
         # Find the product
         #-----------------
         my $assetID;

         if( $productTypeID == RPS::DB::Item::Product::kProductTypeDigitalTrack )
         {
             $assetID = $trackID;
         }
         else
         {
             $assetID = $albumID;
         }

         my $pObj = RPS::DB::Item::Product->Lookup(
            asset_id => $assetID,
            product_type_id => $productTypeID,
         );
         if ( !$pObj ) {
            report("DEBUG: no product found for assetID($albumID), productType($productTypeID)");
            _appendString( $errorCode, "No product found");
            ++$excount{product_not_found};
         } else {
            $productID = $pObj->product_id;
         }

         #----------------------------------------------------------------------
         # Find the track license using the track, publisher, region and product
         # type (license config).  Note: if we don't have a productID, then
         # skip looking for the license because we're won't be able to import
         # the reserve.
         #----------------------------------------------------------------------
         if ( $publisherID && $regionID && $licenseConfigID && $productID ) {
            my %tlArgs = (
               track_id => $trackID,
               ca_publisher_id => $publisherID,
               region_id => $regionID,
               product_type_id => $licenseConfigID,
            );

            my $tlObj = _findTrackLicense( %tlArgs );

            if ( $tlObj ) {
               $licenseID             = $tlObj->ca_track_license_id;
               $licenseRateType       = $tlObj->rate_type;
               $pennyRate             = $tlObj->penny_rate;
               $licenseRatePercentage = $tlObj->rate_percentage;
               $licenseShare          = $tlObj->share;
               $licenseRateBasis      = $tlObj->rate_basis;
               $licenseLockDate       = $tlObj->lock_date;
               report("   Found license $licenseID");

               # Store the information in the data row.
               $row->{'royaltyshare-license-id#'} = $tlObj->ca_track_license_id;
               $row->{'license-config'} = _printProductType($tlObj->product_type_id);

            } else {
               report("   WARNING: License not found : ". Dumper(\%tlArgs));
               _appendString( $errorCode, "License not found");
               ++$excount{license_not_found};
            }
         }

      }

      
      #----------------------------------------------
      # At this point, the row is either good or bad.
      #----------------------------------------------
      if ( $errorCode ) {
         $row->{'error-code'} = $errorCode;
         next;
      }

      #----------------------------------------------
      # Create the reserve(s) if there were no errors
      #----------------------------------------------
      my $statRate;
      my $effRate;
      my $year;

      my $duration = _getDuration( $trackID );

      my $lookupDate;
      if ( RPS::DB::Item::CATrackLicense::kRateBasisSale == $licenseRateBasis ) {
         $lookupDate = _normalizeDate($dateReservesTaken);
      } elsif ( RPS::DB::Item::CATrackLicense::kRateBasisLock == $licenseRateBasis  ) {
         assert($licenseLockDate);
         $lookupDate = _normalizeDate($licenseLockDate);
      } else { 
         assert("unknown rate basis");
      }

      my ( $statRateID, $rate, $minRate ) = _getStatRateID( $lookupDate );

      if ( RPS::DB::Item::CATrackLicense::kRateTypeFull == $licenseRateType )  {

         #------------------------------------------------------------
         # Sanity check -- if we're processing a full-stat license and
         # no duration is available, exception out because we won't be
         # able to calculate the effective rate for the reserve.
         #------------------------------------------------------------
         if ( !$duration || $duration == 0 ) {
            report("WARNING: license $licenseID is a full-stat license, "
               ."however trackID $trackID has no duration.");
            _appendString( $errorCode, "No track duration");
            ++$excount{no_track_duration};
            $row->{'error-code'} = $errorCode;
            next;
         }

         if ( $duration > 300 ) { # five minutes
            $statRate = $duration * $minRate;
         } else {
            $statRate = $rate;
         }
      } elsif ( RPS::DB::Item::CATrackLicense::kRateTypeMinimum == $licenseRateType )  {
         $statRate = $rate;
      } else {
         $statRate = $pennyRate;
      }

#      #--------------------------------
#      # Determine the stat rate indexes
#      #--------------------------------
#      my $lookupDate;
#      if ( RPS::DB::Item::CATrackLicense::kRateBasisSale == $licenseRateBasis ) {
#         #$year = _getYear($dateReservesTaken);
#         $lookupDate = $dateReservesTaken;
#      } elsif ( RPS::DB::Item::CATrackLicense::kRateBasisLock == $licenseRateBasis  ) {
#         assert($licenseLockDate);
#         #$year = _getYear($licenseLockDate);
#         $lookupDate = $licenseLockDate;
#      } else { 
#         assert("unknown rate basis");
#      }

      #my ($statRateID, $zzRate, $zzMinRate) = _getCAStatRateID($lookupDate);
      #my ($statRateID, $zzRate, $zzMinRate) = _getStatRateID($lookupDate);

      #-------------------------------------------------------------------
      # Calculate the effective rate that the reserves will be released at
      #-------------------------------------------------------------------
      if ( RPS::DB::Item::CATrackLicense::kRateTypePenny != $licenseRateType )  {
          $effRate = $statRate * ($licenseRatePercentage/100) * ($licenseShare/100);
      } else {
          $effRate = $statRate;
      }

      assert( $effRate > 0 );

      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=0;
      my $per=1;
      foreach my $units (@buckets) {
         if ( $units ) {
            my $reserveID = _createReserve(
               license_id     => $licenseID,
               product_id     => $productID,
               effective_rate => $effRate,
               units          => $buckets[$per - 1],
               period         => $per,
               stat_rate_id   => $statRateID,
               rate_basis     => $licenseRateBasis,
            );
            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};
      push @header, $v;
   }
   report("EXCEPTION:\t".join("\t", @header, "Error Code", "import-status"));

   #--------------------------------------
   # Now dump out the rows that had errors
   #--------------------------------------
   foreach my $row (@$rows) {
      my $rowid = $row->{rowid};
      my $errorCode = $row->{'error-code'};
      my $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} : '';
         push @obuf, $val;
      }

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

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

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

      report($zzbuf);
   }
}

#----------------------------------------------------------------------------
# Look for a track license with the specified configuration.  If a specific
# configuration can't be matched, look for an 'all' or 'all-physical' license.
#----------------------------------------------------------------------------
sub _findTrackLicense {
   my(%args) = @_;
   my $trackID       = $args{track_id};
   my $publisherID   = $args{ca_publisher_id};
   my $regionID      = $args{region_id};
   my $productTypeID = $args{product_type_id};
   assert($trackID);
   assert($publisherID);
   assert($regionID);
   assert($productTypeID);

   my %tlArgs = (
      track_id        => $trackID,
      ca_publisher_id => $publisherID,
      region_id       => $regionID,
      product_type_id => $productTypeID,
   );
   my $tlObj = RPS::DB::Item::CATrackLicense->Lookup( %tlArgs );

   if ( !$tlObj ) {
      #-------------------------------------------------------------------
      # Didn't find product-specific license; is there an 'all' or 'all-p'
      # license?
      #-------------------------------------------------------------------
      my $sql = "SELECT ca_track_license_id FROM ca_track_license "
         . "WHERE track_id=$trackID "
         . "AND ca_publisher_id=$publisherID "
         . "AND region_id=$regionID "
         . "AND (product_type_id IS NULL OR product_type_id = 255)";
      my $sth = $dbo->DoCmd($sql);
      die("Too many licenses found: sql =\n$sql") if ( $sth->rows > 1 );

      if ( $sth->rows == 1 ) {
         my($id) = $sth->fetchrow_array();
         $tlObj = RPS::DB::Item::CATrackLicense->Lookup( ca_track_license_id => $id );
      }
   }

   return $tlObj;
}

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

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

   my $licenseID  = $args{license_id};
   my $productID  = $args{product_id};
   my $effRate    = $args{effective_rate};
   my $period     = $args{period};
   my $statRateID = $args{stat_rate_id};
   my $rateBasis  = $args{rate_basis};
   assert($licenseID);
   assert($productID);
   assert($effRate);
   assert(defined $period);
   assert($statRateID);
   assert($rateBasis);

   my %rargs = (
      ca_track_license_id        => $licenseID,
      product_id                 => $productID,
      original_statement_item_id => 0,
      units                      => $units,
      effective_rate             => $effRate,
      periods_remaining          => $period,
      sale_stat_rate_id          => $statRateID,
   );
   $rargs{issue_stat_rate_id} = $statRateID
      if ( RPS::DB::Item::CATrackLicense::kRateBasisLock == $rateBasis );

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

   report("   _createReserves: effRate($effRate) licenseID($licenseID) "
      ."productID($productID) units($units) period($period) statRateID($statRateID)"
   );
   return $reserveID;

}# _createReserve

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

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

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

   $t_date =~ s/-//g;

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

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

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

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

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

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

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

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

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

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

   my $t_stat_rate_id;
   my $rate;
   my $minRate;

   my($yr,$mon,$dy) = split("-", $t_date);

   if ( !$mon || '' eq $mon ) {
      die("_getCAStatRateID: can't get month, date = '$t_date'\n");
   }
   if ( !$dy || '' eq $dy ) {
      die("_getCAStatRateID: can't get day, date = '$t_date'\n");
   }

   my $t_year = $yr;

   # The first 3 entries in ca_stat_rate start on dates other than January 1;
   # TODO: Change the date lookup logic in this routine; we should be reading
   # in the information from RSCOMMON.ca_stat_rate instead of hardcoding the
   # date info here.

   #if ( $t_year eq '1988' or $t_year eq '1989' ) {
   #   $t_stat_rate_id = 2;  # see RSCOMMON.ca_stat_rate for more info
   #} elsif ( $t_year eq '1990' or $t_year eq '1991' ) {
   #   $t_stat_rate_id = 3;
   #} elsif ( $t_year eq '1992' or $t_year eq '1993' ) {
   #}
   if ( $t_year eq '1992' or $t_year eq '1993' ) {
      $rate           = 0.0625;
      $minRate        = 0.0125;
      $t_stat_rate_id = 4;

   } elsif ( $t_year eq '1994' or $t_year eq '1995' ) {
      $rate           = 0.0647;
      $minRate        = 0.0129;
      $t_stat_rate_id = 5;

   } elsif ( $t_year eq '1996' or $t_year eq '1997' ) {
      $rate           = 0.0660;
      $minRate        = 0.0132;
      $t_stat_rate_id = 6;

   } elsif ( $t_year eq '1998' or $t_year eq '1999' ) {
      $rate           = 0.0710;
      $minRate        = 0.0142;
      $t_stat_rate_id = 7;

   } elsif ( $t_year eq '2000' or $t_year eq '2001' ) {
      $rate           = 0.0740;
      $minRate        = 0.0148;
      $t_stat_rate_id = 8;

   } elsif ( $t_year eq '2002' or $t_year eq '2003' or
             $t_year eq '2004' or $t_year eq '2005' or
             $t_year eq '2006' or
             ($t_year eq '2007' && $mon < 7) ) {
      $rate           = 0.0770;
      $minRate        = 0.0154;
      $t_stat_rate_id = 9;

   } elsif ( ($t_year eq '2007' && $mon >= 7) or
             $t_year eq '2008' or $t_year eq '2009' ) {
      $rate           = 0.0810;
      $minRate        = 0.0162;
      $t_stat_rate_id = 10;

   } elsif ( $t_year eq '2010' ) {
      $rate           = 0.0830;
      $minRate        = 0.0166;
      $t_stat_rate_id = 11;

   } else {
      report("   _getStatRateID: Unexpected date format, expected 'YYYY-MM-DD', got '$t_date'");
      die("   _getStatRateID: Encountered unknown year '$t_year'\n");
      #$t_stat_rate_id = 16;
   }
   return ($t_stat_rate_id, $rate, $minRate);
}#_getCAStatRateID

#------------------------------------------------
# _getDuration -- returns the duration in seconds
#------------------------------------------------
sub _getDuration {
   my($trackID) = @_;
   my $d;
   my $sql = qq(
      SELECT m.duration
      FROM track t JOIN master m USING(master_id)
      WHERE t.track_id=$trackID
   );
   my $sth = $dbo->DoCmd($sql);
   ($d) = $sth->fetchrow_array();
   return $d;
}


#------------------------------------------------------------
# _getCARoyaltyRate -- returns the royalty rate and minute rate
#  for a given date
# TODO: Get this information from RSCOMMON.ca_stat_rate
#------------------------------------------------------------
#sub _getCARoyaltyRate {
#   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.0770;
#      $minRate = 0.0154;
#   } elsif( $year >= 2000 ) {
#      $rate    = 0.0740;
#      $minRate = 0.0148;
#   } elsif( $year >= 1998 ) {
#      $rate    = 0.0710;
#      $minRate = 0.0142;
#   } elsif( $year >= 1996 ) {
#      $rate    = 0.0660;
#      $minRate = 0.0132;
#   } else {
#      die("_getCARoyaltyRate: ERROR -- reserves from date $dateTaken ???");
#   }
#
#   return($rate, $minRate);
#}# _getCARoyaltyRate

#------------------------------------------------------------
# _getRoyaltyRate -- returns the royalty rate and minute rate
#  for a given date
# TODO: Get this information from RSCOMMON.stat_rate
#------------------------------------------------------------
#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: ERROR -- reserves from date $dateTaken ???");
#   }
#
#   return($rate, $minRate);
#}# _getRoyaltyRate

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 = 100;
      }
   }
   return ($albumID, $errflag);
}# _findAlbum

sub _findPublisher {
   my(%args) = @_;
   my $publisherID;
   my $pObj = RPS::DB::Item::CAPublisher->Lookup(
      publisher_name => $args{publisher_name},
   );
   # TODO: Should check for duplicate publisher names..
   if ( $pObj ) {
      $publisherID = $pObj->ca_publisher_id;
   }
   return $publisherID;
}

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

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

   # hack -- need an external way of transforming
   # region names
   $regionName = "United States" if ( $args{name} eq 'DOMESTIC' );

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

sub _printProductType {
   my($id) = @_;
   my %types = (
      RPS::DB::Item::ProductType::kAllPhysicalProducts => 'ALL-P',
      RPS::DB::Item::Product::kProductTypeLP => 'LP',
      RPS::DB::Item::Product::kProductTypeCD => 'CD',
      RPS::DB::Item::Product::kProductTypeDigital => 'D',
      RPS::DB::Item::Product::kProductTypeVHS => 'VHS',
      RPS::DB::Item::Product::kProductTypeCass => 'CASS',
      RPS::DB::Item::Product::kProductTypeEP => 'EP',
      RPS::DB::Item::Product::kProductTypeDVD => 'DVD',
      RPS::DB::Item::Product::kProductTypeCDSingle => 'CD5',
      RPS::DB::Item::Product::kProductTypeCassSingle => 'CAS5',
      RPS::DB::Item::Product::kProductTypeDVDCDSet => 'DVDCD',
      RPS::DB::Item::Product::kProductTypeDblCD => 'CD2',
   );
   if (!$types{$id}) {
      return 'ALL';
   } else {
      return $types{$id};
   }
}

sub _getProductTypeID {
   my(%args) = @_;
   my $cfg = $args{product_type};
report("DEBUG: _getProductTypeID -- cfg($cfg)");
   #return RPS::DB::Item::ProductType::kAllProducts if ( $cfg =~ /^all$/i );
   return undef if ( $cfg =~ /^all$/i );
   return RPS::DB::Item::ProductType::kAllPhysicalProducts if ( ($cfg =~ /^allp$/i) || ($cfg =~ /^all-p$/i) );
   return RPS::DB::Item::Product::kProductTypeLP if ( $cfg =~ /^lp$/i );
   return RPS::DB::Item::Product::kProductTypeCD if ( $cfg =~ /^cd$/i );
   return RPS::DB::Item::Product::kProductTypeDigital if ( $cfg =~ /^d$/i );
   return RPS::DB::Item::Product::kProductTypeVHS if ( $cfg =~ /^vhs$/i );
   return RPS::DB::Item::Product::kProductTypeCass if ( $cfg =~ /^cass$/i );
   return RPS::DB::Item::Product::kProductTypeEP if ( $cfg =~ /^ep$/i );
   return RPS::DB::Item::Product::kProductTypeDVD if ( $cfg =~ /^dvd$/i );
   return RPS::DB::Item::Product::kProductTypeCDSingle if ( $cfg =~ /^cd5$/i );
   return RPS::DB::Item::Product::kProductTypeCassSingle if ( $cfg =~ /^cas5$/i );
   return RPS::DB::Item::Product::kProductTypeDVDCDSet if ( $cfg =~ /^dvdcd$/i );
   return RPS::DB::Item::Product::kProductTypeDblCD if ( $cfg =~ /^cd2$/i );
   return kProductTypeNotFound;
}

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

   if ( $dstr =~ m/^\d\d\d\d\d$/ ) {
      my $_dt = Spreadsheet::ParseExcel::Utility::ExcelFmt( "yyyy-mm-dd", $dstr );
      report("_normalizeDate:  Converted msft date $dstr to $_dt");
      $dstr = $_dt;
   }
   return $dstr;

}

#----------------------------------------------------------------------
# _calculateEffectiveRate - copied straight from
#  /app/tools/rps/lib/RPS/Mechanical/Process/CreateStatement/Complex.pm
#
# Modified to change 'self->report' references to just 'report'.
#----------------------------------------------------------------------
#sub _calculateEffectiveRate
#{
#    my ($self, $issueStatRateID, $saleStatRateID, $rateBasis, $rateType, $pennyRate, $percentOfRate, $duration, $share, $lockDate, $trackLicense) = @_;
#
#    #$self->_report("_calculateEffectiveRate : issueStatRateID = $issueStatRateID, saleStatRateID = $saleStatRateID, rateBasis = $rateBasis, pennyRate = $pennyRate, percentOfRate = $percentOfRate, duration = $duration, share = $share", 3);
#    report("_calculateEffectiveRate : issueStatRateID = $issueStatRateID, saleStatRateID = $saleStatRateID, rateBasis = $rateBasis, pennyRate = $pennyRate, percentOfRate = $percentOfRate, duration = $duration, share = $share", 3);
#
#    # Calculate the effective rate per unit to pay.
#    # rate basis - either sale or release
#    # rate term - full, partial, or penny
#    #
#    my $appliedStatRateID;
#    my ($baseStatRate, $minuteRate);
#    my $effectiveRate;
#
#
#    if (RPS::License::RateType::kPenny == $rateType)
#    {
#        $effectiveRate = $pennyRate;
#        $appliedStatRateID = $saleStatRateID;
#        #$self->_report("penny rate : minimum, pennyRate = $pennyRate", 3);
#        report("penny rate : minimum, pennyRate = $pennyRate", 3);
#    }
#    else
#    {
#        if (RPS::License::RateBasis::kSale == $rateBasis)
#        {
#            #($baseStatRate, $minuteRate)= $self->_getStatRate($saleStatRateID);
#            ($baseStatRate, $minuteRate)= $self->_getStatRate($saleStatRateID); # XXX XXX XXX
#            #$self->_report("rate basis = sale, baseRate = $baseStatRate, minute rate = $minuteRate", 3);
#            report("rate basis = sale, baseRate = $baseStatRate, minute rate = $minuteRate", 3);
#            $appliedStatRateID = $saleStatRateID;
#        }
#        # !!! For this to convert to 'lock date', I'm going to _NEED_ to know the lock date. duh!
#        elsif (RPS::License::RateBasis::kLock == $rateBasis)
#        {
#            my $lockStatRateID;
#
#            if ($trackLicense->type == RPS::DB::Item::TrackLicense::kRingtone)
#            {
#                #$lockStatRateID = $self->_getRingStatRateID($lockDate);
#                $lockStatRateID = _getRingStatRateID($lockDate); # XXX XXX XXX
#            }
#            else
#            {
#                #$lockStatRateID = $self->_getStatRateID($lockDate);
#                $lockStatRateID = _getStatRateID($lockDate); # XXX XXX XXX
#            }
#
#
#            #($baseStatRate, $minuteRate)= $self->_getStatRate($lockStatRateID);
#            ($baseStatRate, $minuteRate)= _getStatRate($lockStatRateID); # XXX XXX XXX
#            #$self->_report("rate basis = lock, baseRate = $baseStatRate, minute rate = $minuteRate", 3);
#            report("rate basis = lock, baseRate = $baseStatRate, minute rate = $minuteRate", 3);
#            $appliedStatRateID = $lockStatRateID;
#        }
#        else
#        {
#            die "!!! Unknown rate basis: " . $rateBasis;
#        }
#
#        if (RPS::License::RateType::kFull == $rateType)
#        {
#            # Use the base stat rate for songs of 5 minutes or less.
#            # Use the minute rate for longer songs.
#            #
#            $effectiveRate = $baseStatRate;
#            if ($duration > 300)
#            {
#                my $minutes = ceil($duration / 60);
#                my $minBasedRate = ($minutes * $minuteRate);
#
#                #$self->_report("full rate. minutes = $minutes, duration = $duration, minute based rate = $minBasedRate", 3);
#                report("full rate. minutes = $minutes, duration = $duration, minute based rate = $minBasedRate", 3);
#                $effectiveRate = ($minBasedRate > $effectiveRate ? $minBasedRate : $effectiveRate);
#            }
#        }
#        elsif (RPS::License::RateType::kMinimum == $rateType)
#        {
#            $effectiveRate = $baseStatRate;
#            #$self->_report("min rate : minimum, baseStatRate = $baseStatRate", 3);
#            report("min rate : minimum, baseStatRate = $baseStatRate", 3);
#        }
#        else
#        {
#            die "!!! Unknown rate type: " . $rateType;
#        }
#    }
#
#
#    my $baseRate = $effectiveRate;
#
#    # 0 %  is not an option...
#    #
#    if ($percentOfRate > 0 && RPS::License::RateType::kPenny != $rateType)
#    {
#        $effectiveRate = $effectiveRate * ($percentOfRate / 100);
#    }
#    assert($effectiveRate > 0);
#
#
#    # Now that we have the effective rate, cut it according to the share percentage.
#    #
#    if (RPS::License::RateType::kPenny != $rateType)
#    {
#        $effectiveRate = $effectiveRate * ($share / 100);
#    }
#
#
#    # For my final trick - round these down to 4 places right of the decimal point.
#    #
#    $effectiveRate = Common::RSMath::round($effectiveRate, 4);
#    $baseRate = Common::RSMath::round($baseRate, 4);
#
#
#    #$self->_report("returning effective rate = $effectiveRate", 3);
#    report("returning effective rate = $effectiveRate", 3);
#    return ($effectiveRate, $baseRate, $appliedStatRateID);
#}#_calculateEffectiveRate

1;