package Support::Implementation::ProductPriceTemplate;
#-----------------------------------------------------------------------
# This importer allows the bulk setting of product prices for a given
# price level.  The template specifies prices via the price name, hence
# the prices must be already be in the system.
#
# This importer may modify the product and product_price tables.
#
# 6/29/10 - Changed price_level reference to RSCOMMON
# 6/30/10 - Adjusted product_price lookup to include price_id
# 1/4/13 - Handle price names missing a trailing 0
# 2/26/13 - Made price_level name case-insensitive
# 11/26/14 - Corrected ProductPrice lookup logic
# 1/16/15 - Added Excel 2007 support
# 9/28/16 - Check for invalid price entries before processing line.
# 9/29/16 - Corrected invalid price entry validation
# 8/3/17 - Remove leading/trailing spaces from price names.  Fixed
#          $albumID assignment if none specified in template.
# 10/25/18 - Added support for digital pricing (RSD-2651).  Also appended
#  price to price names so that we can match the values shown on the UI.
# 6/6/19 - Cleaned up error reporting.
#-----------------------------------------------------------------------
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 Common::Consts;
use Common::Util qw( clean trimspaces);

use RPS::DB::Item::Price;
use RPS::DB::Item::ProductPrice;
use RPS::DB::Item::Product;

use Support::Implementation::SearchUtil;
use Support::Implementation::ExcelReader;
use Support::Implementation::Excel2007Reader;
use Support::Implementation::TabDelimitedReader;
use Support::Implementation::ImplementationUtil qw( report appendString checkMissingColumn printNull );

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

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

binmode STDOUT, ":utf8";

#-----------------------------------------------------------------------
# templateHeader maps column names to their default column number.
# The actual column number-header name map is stored in columnMap.
# Note(s):
# 1) Column names are case sensitive
# 2) Spaces are ignored; you must manually remove spaces from the names
#    stored in templateHeader
#-----------------------------------------------------------------------
my %gTemplateHeader = (
   "album-id"                  => 0,  # A
   "album-title"               => 1,  # B
   "album-artist"              => 2,  # C
   "label-name"                => 3,  # D
   "catalog-number"            => 4,  # E
#   "client-album-id"           => 5,  # F
   "release-date"              => 6,  # G

   "cd-upc"                    => 7,  # H
   "cd-full-price-name"        => 8,  # I
   "cd-midline-price-name"     => 9,  # J
   "cd-budget-price-name"      => 10, # K
   "cd-default-price-point"     => 11, # L

   "vinyl-upc"                 => 12, # M
   "vinyl-full-price-name"     => 13, # N
   "vinyl-midline-price-name"  => 14, # O
   "vinyl-budget-price-name"   => 15, # P
   "vinyl-default-price-point"  => 16, # Q

   "dvd-upc"                   => 17, # R
   "dvd-full-price-name"       => 18, # S
   "dvd-midline-price-name"    => 19, # T
   "dvd-budget-price-name"     => 20, # U
   "dvd-default-price-point"    => 21, # V

   "dvdcd-upc"                 => 22, # W
   "dvdcd-full-price-name"     => 23, # X
   "dvdcd-midline-price-name"  => 24, # Y
   "dvdcd-budget-price-name"   => 25, # Z
   "dvdcd-default-price-point"  => 26, # AA

   "digital-upc"               => 27, # AA
   "digital-album-price-name"  => 28, # AB
   "digital-track-price-name"  => 29, # AC

);

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

#-----------------------------------------------
# gPriceMap is a map of price names to their IDs
#-----------------------------------------------
my %gPriceMap;

#----------------------------------------------------------
# gPriceLevelMap is a map of price level names to their IDs
#----------------------------------------------------------
my %gPriceLevelMap;

#--------------------------------------
# count keeps track of entities created
#--------------------------------------
my %count;

my $gClientID;
my $execMode;

my $dbo;
my $cdbo;

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


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

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

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

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

   return $self;
}

sub parseHeader {
   my $self = shift;
}

# Reference to SearchUtil object
my $gSearchObj;

#----------------------------------------------
# Read-in all of the template data into memory.
#----------------------------------------------
sub loadMemory {
   my $self = shift;

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

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

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

   my $fileName = $self->name;

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

      # Try and parse it...
      _processData(\%data);
   } elsif( $self->isExcel2007( $fileName ) ) {
      print("PriceTemplate::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;

      # Try and parse it...
      _processData(\%data);
   } elsif( $self->isTabDelimited( $fileName ) ) {
      report("PriceTemplate::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.
#-------------------------------------------------------------------
my $gErr;  # use this for keeping track of errors found on a line

my $totalCount = 0;
my $errorCount = 0;
my %gBadPriceNames; # global hash of bad price names
my %gBadDefaultPriceNames; # global hash of bad default price names

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

   #----------------------------------
   # excount keeps track of exceptions
   #----------------------------------
   my %excount = (
      price_exists => 0,
   );

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

   #-------------------------
   # Initialize the data maps
   #-------------------------
   #my %gPriceMap = ();
   my $sql = "SELECT price_id, name, retail, wholesale FROM price";
   my $sth = $dbo->DoCmd($sql);
   while( my($priceID, $name, $retail, $wholesale) = $sth->fetchrow_array() ) {
      my $cleanName = Common::UTF8::Encode($name);
      $gPriceMap{$cleanName} = $priceID;

      # the UI is showing price.name concatenated with the retail price
      my $displayString = $name . ' (' . $retail . ')';
      $gPriceMap{$displayString} = $priceID;
   }

   #my %gPriceLevelMap = ();
   $sql = "SELECT price_level_id, name FROM price_level";
   $sth = $cdbo->DoCmd($sql);
   while( my($id,$name) = $sth->fetchrow_array() ) {
      $gPriceLevelMap{lc $name} = $id;
   }

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

      $totalCount++;

      #-----------------------------------
      # Get all of the template variables.
      #-----------------------------------
      my $rowid                   = $row->{'rowid'};
      my $albumID                 = $row->{'album-id'};                  # A
      my $albumName               = $row->{'album-title'};               # B
      my $albumArtist             = $row->{'album-artist'};              # C
      my $labelName               = $row->{'label-name'};                # D
      my $catalogNumber           = $row->{'catalog-number'};            # E
      $catalogNumber = $row->{'catalog-#'} if(!$catalogNumber); # check alternate
      my $releaseDate             = $row->{'release-date'};              # F

      my $cdUpc                   = $row->{'cd-upc'};                    # G
      my $cdFullPriceName         = $row->{'cd-full-price-name'};        # H
      my $cdMidlinePriceName      = $row->{'cd-midline-price-name'};     # I
      my $cdBudgetPriceName       = $row->{'cd-budget-price-name'};      # J
      my $cdDefaultPriceName      = $row->{'cd-default-price-point'};     # K

      my $vinylUpc                = $row->{'vinyl-upc'};                 # L
      my $vinylFullPriceName      = $row->{'vinyl-full-price-name'};     # M
      my $vinylMidlinePriceName   = $row->{'vinyl-midline-price-name'};  # N
      my $vinylBudgetPriceName    = $row->{'vinyl-budget-price-name'};   # O
      my $vinylDefaultPriceName   = $row->{'vinyl-default-price-point'};  # P

      my $dvdUpc                  = $row->{'dvd-upc'};                   # Q
      my $dvdFullPriceName        = $row->{'dvd-full-price-name'};       # R
      my $dvdMidlinePriceName     = $row->{'dvd-midline-price-name'};    # S
      my $dvdBudgetPriceName      = $row->{'dvd-budget-price-name'};     # T
      my $dvdDefaultPriceName     = $row->{'dvd-default-price-point'};    # U

      my $dvdcdUpc                = $row->{'dvdcd-upc'};                 # V
      my $dvdcdFullPriceName      = $row->{'dvdcd-full-price-name'};     # W
      my $dvdcdMidlinePriceName   = $row->{'dvdcd-midline-price-name'};  # X
      my $dvdcdBudgetPriceName    = $row->{'dvdcd-budget-price-name'};   # Y
      my $dvdcdDefaultPriceName   = $row->{'dvdcd-default-price-point'};  # Z

      my $digitalUpc              = $row->{'digital-upc'};               # AA
      my $digitalAlbumPriceName   = $row->{'digital-album-price-name'};  # AB
      my $digitalTrackPriceName   = $row->{'digital-track-price-name'};  # AC

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

      $cdFullPriceName = trimspaces($cdFullPriceName);
      $cdMidlinePriceName = trimspaces($cdMidlinePriceName);
      $cdBudgetPriceName = trimspaces($cdBudgetPriceName);
      $cdDefaultPriceName = trimspaces($cdDefaultPriceName);

      $vinylFullPriceName = trimspaces($vinylFullPriceName);
      $vinylMidlinePriceName = trimspaces($vinylMidlinePriceName);
      $vinylBudgetPriceName = trimspaces($vinylBudgetPriceName);
      $vinylDefaultPriceName = trimspaces($vinylDefaultPriceName);

      $dvdFullPriceName = trimspaces($dvdFullPriceName);
      $dvdMidlinePriceName = trimspaces($dvdMidlinePriceName);
      $dvdBudgetPriceName = trimspaces($dvdBudgetPriceName);
      $dvdDefaultPriceName = trimspaces($dvdDefaultPriceName);

      $dvdcdFullPriceName = trimspaces($dvdcdFullPriceName);
      $dvdcdMidlinePriceName = trimspaces($dvdcdMidlinePriceName);
      $dvdcdBudgetPriceName = trimspaces($dvdcdBudgetPriceName);
      $dvdcdDefaultPriceName = trimspaces($dvdcdDefaultPriceName);

      $digitalAlbumPriceName = trimspaces($digitalAlbumPriceName);
      $digitalTrackPriceName = trimspaces($digitalTrackPriceName);

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

      #my %args = ();
      #
      #$args{name}       = $priceName;


      #-----------------------------
      # Verify the album information
      #-----------------------------

      my %aArgs = (
         album_name => $albumName,
      );
      $aArgs{catalog_number} = $catalogNumber if ( $catalogNumber && '' ne $catalogNumber );

      my($_albumID, $errFlag) = $gSearchObj->findAlbum( %aArgs );

      if ( $errFlag ) {

         if ( !$albumID ) {

            #-----------------------------------------------------------------
            # If an RPS albumID was not specified in the template, try to find
            # the album using the album title and/or catalog number
            #-----------------------------------------------------------------
            report("   errFlag($errFlag) ***");
            if ( $errFlag == Support::Implementation::SearchUtil::kAlbumNotFound ) {
               report("ERROR: findAlbum returned album not found : ". Dumper(\%aArgs) );
               appendString( $errorCode, "Album not found");
               ++$excount{album_not_found};
            } elsif ( $errFlag == Support::Implementation::SearchUtil::kNonUniqueAlbumName ) {
               appendString( $errorCode, "Non-unique album name");
               ++$excount{nonunique_album_name};
            } else {
               die("ERROR: _findAlbum returned unknown errorFlag($errFlag)\n");
            }

         } else {

            # TODO: Need to verify that the title and/or catalog_number match
            # the album associated with the albumID from the template

         }

      } else {

         # if albumID was defined in template, make sure it matches the albumID
         # we found doing a name/catalog# lookup

         report("   albumID($_albumID)");

         if ( $albumID ) {
            if ( $albumID != $_albumID ) {
               appendString( $errorCode, "Specified albumID doesn't match album information");
               ++$excount{albumid_mismatch};
            }
         }
         else {
            $albumID = $_albumID;
         }

      }

      # Check for invalid price names.  These all need to be setup beforehand through the UI.
      # We create a hash of these since the same name may be used more than once on the line
      # and we only want to issue a single error message per name if needed.
      #
      my %badPriceNames; # bad names on this line

      $badPriceNames{$cdFullPriceName} = 1 if( $cdFullPriceName && ! _getPriceID($cdFullPriceName) );
      $badPriceNames{$cdMidlinePriceName} = 1 if( $cdMidlinePriceName && ! _getPriceID($cdMidlinePriceName) );
      $badPriceNames{$cdBudgetPriceName} = 1 if( $cdBudgetPriceName && ! _getPriceID($cdBudgetPriceName) );


      $badPriceNames{$vinylFullPriceName} = 1 if( $vinylFullPriceName && ! _getPriceID($vinylFullPriceName) );
      $badPriceNames{$vinylMidlinePriceName} = 1 if( $vinylMidlinePriceName && ! _getPriceID($vinylMidlinePriceName) );
      $badPriceNames{$vinylBudgetPriceName} = 1 if( $vinylBudgetPriceName && ! _getPriceID($vinylBudgetPriceName) );


      $badPriceNames{$dvdFullPriceName} = 1 if( $dvdFullPriceName && ! _getPriceID($dvdFullPriceName) );
      $badPriceNames{$dvdMidlinePriceName} = 1 if( $dvdMidlinePriceName && ! _getPriceID($dvdMidlinePriceName) );
      $badPriceNames{$dvdBudgetPriceName} = 1 if( $dvdBudgetPriceName && ! _getPriceID($dvdBudgetPriceName) );


      $badPriceNames{$dvdcdFullPriceName} = 1 if( $dvdcdFullPriceName && ! _getPriceID($dvdcdFullPriceName) );
      $badPriceNames{$dvdcdMidlinePriceName} = 1 if( $dvdcdMidlinePriceName && ! _getPriceID($dvdcdMidlinePriceName) );
      $badPriceNames{$dvdcdBudgetPriceName} = 1 if( $dvdcdBudgetPriceName && ! _getPriceID($dvdcdBudgetPriceName) );

      $badPriceNames{$digitalAlbumPriceName} = 1 if( $digitalAlbumPriceName && ! _getPriceID($digitalAlbumPriceName) );
      $badPriceNames{$digitalTrackPriceName} = 1 if( $digitalTrackPriceName && ! _getPriceID($digitalTrackPriceName) );

      if( (keys %badPriceNames) > 0 )
      {
          foreach my $name (keys %badPriceNames)
          {
              appendString( $errorCode, "Price name '$name' not found");
              ++$excount{bad_price_name};
              $gBadPriceNames{$name} = 1;  # keep track of these across all lines ...
          }
      }



      # The default names must match a price level
      #
      my %badDefaultPriceNames; # bad names on this line
      $badDefaultPriceNames{$cdDefaultPriceName} = 1 if( $cdDefaultPriceName && ! _getPriceLevelID($cdDefaultPriceName) );
      $badDefaultPriceNames{$vinylDefaultPriceName} = 1 if( $vinylDefaultPriceName && ! _getPriceLevelID($vinylDefaultPriceName) );
      $badDefaultPriceNames{$dvdDefaultPriceName} = 1 if( $dvdDefaultPriceName && ! _getPriceLevelID($dvdDefaultPriceName) );
      $badDefaultPriceNames{$dvdcdDefaultPriceName} = 1 if( $dvdcdDefaultPriceName && ! _getPriceLevelID($dvdcdDefaultPriceName) );


      if( (keys %badDefaultPriceNames) > 0 )
      {
          foreach my $name (keys %badDefaultPriceNames)
          {
              appendString( $errorCode, "Invalid default price");
              ++$excount{bad_default_price_name};
              $gBadDefaultPriceNames{$name} = 1;  # keep track of these across all lines ...
          }
      }

      # TODO: check for duplicate albums in template


      #=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
      # OPEN ISSUE(S):
      #
      # Do we allow products without prices?  Currently, if we have a priceless
      # product, then we generate an exception.
      #
      #=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

      #----------------------
      # Look for a CD product
      #----------------------
      my $cd_product = RPS::DB::Item::Product->Lookup(
         asset_id => $albumID,
         product_type_id => 2, # DVD
      );

      if ( !$cd_product ){
         if ( $cdFullPriceName || $cdMidlinePriceName || $cdBudgetPriceName ) {
            report("Missing CD product (albumID=$albumID) in row($rowid)");
            #appendString( $errorCode, "Missing CD product");
            #++$excount{missing_cd_product};
         }
      }


      #-------------------------
      # Look for a Vinyl product
      #-------------------------
      my $vinyl_product = RPS::DB::Item::Product->Lookup(
         asset_id => $albumID,
         product_type_id => 1, # LP
      );

      if ( !$vinyl_product ){
         if ( $vinylFullPriceName || $vinylMidlinePriceName || $vinylBudgetPriceName ) {
            report("Missing Vinyl product (albumID=$albumID) in row($rowid)");
            #appendString( $errorCode, "Missing Vinyl product");
            #++$excount{missing_vinyl_product};
         }
      }

      #-------------------------
      # Look for a DVD product
      #-------------------------
      my $dvd_product = RPS::DB::Item::Product->Lookup(
         asset_id => $albumID,
         product_type_id => 9, # DVD
      );

      if ( !$dvd_product ){
         if ( $dvdFullPriceName || $dvdMidlinePriceName || $dvdBudgetPriceName ) {
            report("Missing DVD product (albumID=$albumID) in row($rowid)");
            #appendString( $errorCode, "Missing DVD product");
            #++$excount{missing_dvd_product};
         }
      }

      #-------------------------
      # Look for a DVDCD product
      #-------------------------
      my $dvdcd_product = RPS::DB::Item::Product->Lookup(
         asset_id => $albumID,
         product_type_id => 13, # DVDCD
      );

      if ( !$dvdcd_product ){
         if ( $dvdcdFullPriceName || $dvdcdMidlinePriceName || $dvdcdBudgetPriceName ) {
            report("Missing DVDCD product (albumID=$albumID) in row($rowid)");
            #appendString( $errorCode, "Missing DVDCD product");
            #++$excount{missing_dvdcd_product};
         }
      }

      # Check for a Digital product
      # It's possible that an album has more than one DA product.  Let's error out if there
      # are multiple DA products with the same UPC.
      #
      my $digital_product;
      if ( $digitalUpc ) {
          my $sql = "SELECT product_id FROM product WHERE product_type_id=3 AND asset_id=$albumID";
          $sql .= " AND upc_ean='$digitalUpc'";
          my $sth = $dbo->DoCmd($sql);
          if ( $sth->rows > 1 ) {
             appendString( $errorCode, "Duplicate digital UPC detected");
             ++$excount{duplicate_digital_upc};
          } elsif ( $sth->rows == 1 ) {
             $digital_product = RPS::DB::Item::Product->Lookup(
                asset_id => $albumID,
                product_type_id => 3, # DA
             );
          }
      }

      #------------------------------
      # Check if no prices were found
      #------------------------------
      if ( !$cdFullPriceName && !$cdMidlinePriceName && !$cdBudgetPriceName &&
           !$vinylFullPriceName && !$vinylMidlinePriceName && !$vinylBudgetPriceName &&
           !$dvdFullPriceName && !$dvdMidlinePriceName && !$dvdBudgetPriceName &&
           !$dvdcdFullPriceName && !$dvdcdMidlinePriceName && !$dvdcdBudgetPriceName &&
           !$digitalAlbumPriceName && !$digitalTrackPriceName) {
         appendString( $errorCode, "Prices not defined");
         ++$excount{prices_not_defined};
      }

      #--------------------------------
      # Check if no products were found
      #--------------------------------
      if ( !$vinyl_product && !$cd_product && !$dvd_product && !$dvdcd_product && !$digital_product ){
         appendString( $errorCode, "No products found");
         ++$excount{no_products_found};
      }

      #------------------------------------------------------------------
      # If there are any errors at this point, stop processing this album
      #------------------------------------------------------------------
      if ( $errorCode ) {
         $row->{'error-code'} = $errorCode;
         report("    ERROR: Unable to process line: $errorCode\n");
         $errorCount++;
         next;
      }



      #------------------------------------------------------------
      # priceString - if we create any product_price entities, then
      # the product_price_id(s) will be added to this string.
      #------------------------------------------------------------
      my $priceString;
      my $ppID;

      if ( $cd_product ) {

         my $productID = $cd_product->product_id;
      
         $ppID = ($cdFullPriceName) ? _createProductPrice( 'CD', $productID, 'Full', $cdFullPriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         $ppID = ($cdMidlinePriceName ) ? _createProductPrice( 'CD', $productID, 'Mid', $cdMidlinePriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         $ppID = ($cdBudgetPriceName ) ? _createProductPrice( 'CD', $productID, 'Budget', $cdBudgetPriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         _setDefaultPriceLevel($productID, $cdDefaultPriceName) if ( $cdDefaultPriceName );
      }

      if ( $vinyl_product ) {

         my $productID = $vinyl_product->product_id;
      
         $ppID = ( $vinylFullPriceName ) ?  _createProductPrice( 'LP', $productID, 'Full', $vinylFullPriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         $ppID = ( $vinylMidlinePriceName ) ?  _createProductPrice( 'LP', $productID, 'Mid', $vinylMidlinePriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         $ppID = ( $vinylBudgetPriceName ) ?  _createProductPrice( 'LP', $productID, 'Budget', $vinylBudgetPriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         _setDefaultPriceLevel($productID, $vinylDefaultPriceName) if ( $vinylDefaultPriceName );
      }

      if ( $dvd_product ) {

         my $productID = $dvd_product->product_id;
      
         $ppID = ( $dvdFullPriceName ) ?  _createProductPrice( 'DVD', $productID, 'Full', $dvdFullPriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         $ppID = ( $dvdMidlinePriceName ) ?  _createProductPrice( 'DVD', $productID, 'Mid', $dvdMidlinePriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         $ppID = ( $dvdBudgetPriceName ) ?  _createProductPrice( 'DVD', $productID, 'Budget', $dvdBudgetPriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         _setDefaultPriceLevel($productID, $dvdDefaultPriceName) if ( $dvdDefaultPriceName );
      }

      if ( $dvdcd_product ) {

         my $productID = $dvdcd_product->product_id;
      
         $ppID = ( $dvdcdFullPriceName ) ?  _createProductPrice( 'DVDCD', $productID, 'Full', $dvdcdFullPriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         $ppID = ( $dvdcdMidlinePriceName ) ?  _createProductPrice( 'DVDCD', $productID, 'Mid', $dvdcdMidlinePriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         $ppID = ( $dvdcdBudgetPriceName ) ?  _createProductPrice( 'DVDCD', $productID, 'Budget', $dvdcdBudgetPriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);

         _setDefaultPriceLevel($productID, $dvdcdDefaultPriceName) if ( $dvdcdDefaultPriceName );
      }

      if ( $digital_product ) {
         my $productID = $digital_product->product_id;
      
         $ppID = ( $digitalAlbumPriceName ) ?  _createProductPrice( 'DA', $productID, 'Album download', $digitalAlbumPriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);
         $ppID = ( $digitalTrackPriceName ) ?  _createProductPrice( 'DT', $productID, 'Track download', $digitalTrackPriceName ) : undef;
         appendString( $priceString, $ppID) if ($ppID);
      }

      $row->{'rs-price-id'} = $priceString if ( $priceString );

      #-----------------------------------------------------------------------
      # If there are any errors, just tag then onto the line so the user knows
      # about it.  If we were running in exec mode, then it's possible we may
      # have created product prices yet had an error on the line.
      #-----------------------------------------------------------------------
      if ( $gErr ) {
         $row->{'error-code'} = $gErr;
         undef $gErr;
      }

   }#row loop



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

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

   if( (keys %gBadPriceNames) > 0 )
   {
       report(">>> The following price name(s) need to be configured:");
       foreach my $name (sort{ $a cmp $b } keys %gBadPriceNames) {
           report("   $name");
       }
       report("");
   }

   if( (keys %gBadDefaultPriceNames) > 0 )
   {
       report(">>> The following default price level(s) are invalid:");
       foreach my $name (sort{ $a cmp $b } keys %gBadDefaultPriceNames) {
           report("   $name");
       }
       report("");
   }

   report(">>> $errorCount out of $totalCount lines had errors\n");

}#_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.  Exception message(s) will be placed in the error
# code column.  If a line is imported successfully, then the resulting
# licenseID will be stored in the import status column ("licenseID(###)"),
# otherwise this column will contain the string "__FAILED__".
#--------------------------------------------------------------------------
sub _showExceptions {
   my ( $rows ) = @_;

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

      #----------------------------------------------------------
      # 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 $priceID = $row->{'rs-price-id'};
      my $priceID = (exists $row->{'rs-price-id'}) ?  $row->{'rs-price-id'} : "---";

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

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

         push @obuf, $val;
      }

      my $importStatus = ($errorCode) ? "__FAIL__" : "product_price($priceID)";

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

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

      report($zzbuf);
   }
}#_showExceptions

#---------------------------------------------------
# _isValidDateFmt - return true if the supplied date
# is in the format YYYY-MM-DD, false otherwise
#---------------------------------------------------
sub _isValidDateFmt {
   my($dstr) = @_;
   my($yr,$mo,$dy) = split("-",$dstr);
   my $st = 1; # valid unless we detect otherwise

   $st = 0 if ( !$yr || !$mo || !$dy );
   $st = 0 if ( $mo && ( $mo !~ /^\d+$/ ));
   $st = 0 if ( $yr && ( $yr !~ /^\d+$/ ));
   $st = 0 if ( $dy && ( $dy !~ /^\d+$/ ));
   $st = 0 if ( length($yr) != 4 );
   return $st;
}

#sub _appendString {
#   my($str,$v) = @_;
#
#   if ( $str ) {
#      my $cur = $str;
#      my $newstring = "$cur; $v";
#      $_[0] = $newstring;
#   } else {
#      $_[0] = $v;
#   }
#   return;
#die("str($str) v($v)") if ( !$str );
#
#   my $cur = $str;
#   my $prefix = "$cur; $v";
#   $_[0] = ($str) ? $prefix : $v;
#}# _appendString

sub _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 reportException {
   my $rowRef = shift;
   my $exceptionType = shift;

   my @row = @{$rowRef};

   report join("\t", "EXCEPTION:", @row, $exceptionType);
}

sub _printField {
   my ($label,$val) = @_;
   my $s = "";
   if ( $val && $label ) {
      $s = "$label($val) ";
   }
   return $s;
}

sub _getPriceID {
    my($priceName) = @_;
    assert($priceName);
    my $priceID = $gPriceMap{$priceName};

    if( !$priceID )
    {
print STDERR "D: _getPriceID: gPriceMap = ". Dumper(\%gPriceMap) . "\n"; # XXX
        # If we were unable to find the priceName (from the template), then
        # it's possible it's because Excel isn't returning what you think.
        # This can happen if the priceName looks like a number (instead of
        # an alphanumeric string), and the trailing zeroes are being removed.
        #
        if( $priceName =~ /^\d*\.\d$/ )  # Ex: 13.50 --> 13.5
        {
            $priceID = $gPriceMap{ $priceName . "0" };
            print STDERR "D: _getPriceID: Found alternate priceName(". $priceName ."0) --> priceID($priceID)\n" if( $priceID );
        }
        elsif( $priceName =~ /^\d*$/ )  # Ex: 11.00 --> 11
        {
            $priceID = $gPriceMap{ $priceName . ".00" };
            print STDERR "D: _getPriceID: Found alternate priceName(". $priceName .".00) --> priceID($priceID)\n" if( $priceID );
        }

        if( !$priceID )
        {
            report("ERROR: _getPriceID: No priceID found for price '$priceName'");
        }
    }
    return $priceID;
}

#----------------------------------------------------------------------      
# Name:        _createProductPrice
# Description: Creates a product price entry
# Arguments:
#   productType - a string describing the type of product; used for
#     printing a diagnostic message to stdout
#   productID - a valid RPS product_id
#   priceLevelName - The name of the price level to associate with the
#     product.  Must match price_level.name exactly.
#   priceName - The name of the price.  Must match price.name.
#
# If we find any errors, these will be added to the global var $gErr
#----------------------------------------------------------------------      
sub _createProductPrice {
   my ($productType, $productID, $priceLevelName, $priceName) = @_;

   my $productPriceID;

   my $priceLevelID = $gPriceLevelMap{ lc $priceLevelName };
   my $priceID      = _getPriceID($priceName);

   if( !$priceID )
   {
       # This error shouldn't occur now that we're checking for invalid price names
       # in our pre-scan process..
       #
       die("ERROR:_createProductPrice: No priceID found for price '$priceName' : "
           . Dumper(\%gPriceMap) . "\n") if ( !$priceID );
   }

#   my $priceID      = $gPriceMap{ $priceName };
#
#   if( !$priceID )
#   {
#      # If we were unable to find the priceName (from the template), then
#      # it's possible it's because Excel isn't returning what you think.
#      # This can happen if the priceName looks like a number (instead of
#      # an alphanumeric string), and the trailing zeroes are being removed.
#      #
#
#      if( $priceName =~ /^\d*\.\d$/ )  # Ex: 13.50 --> 13.5
#      {
#          $priceID = $gPriceMap{ $priceName . "0" };
#          print STDERR "Found alternate priceName(". $priceName ."0) --> priceID($priceID)\n" if( $priceID );
#      }
#      elsif( $priceName =~ /^\d*$/ )  # Ex: 11.00 --> 11
#      {
#          $priceID = $gPriceMap{ $priceName . ".00" };
#          print STDERR "Found alternate priceName(". $priceName .".00) --> priceID($priceID)\n" if( $priceID );
#      }
#
#      if( !$priceID )
#      {
#         die("ERROR:_createProductPrice: No priceID found for price '$priceName' : "
#            . Dumper(\%gPriceMap) . "\n") if ( !$priceID );
#      }
#   }

   my %args = (
      product_id     => $productID,
      price_level_id => $priceLevelID,
      #price_id       => $priceID,
   );

   #my $ppObj = RPS::DB::Item::ProductPrice->Lookup(
   #   product_id     => $productID,
   #   price_level_id => $priceLevelID,
   #);
   my $ppObj = RPS::DB::Item::ProductPrice->Lookup( %args );

   if ( $ppObj ) {
      $productPriceID = $ppObj->product_price_id;
      my $curPriceID     = $ppObj->price_id;
      report "Product price $productPriceID already exists for $productType product $productID";
      report("WARNING: price_id mismatch, found $curPriceID, expected $priceID")
        if ( $priceID != $curPriceID );

      my $e1 = "Product price already exists for $productType";

      $gErr = (defined $gErr) ? "$gErr; $e1" : $e1;

   } else {
      # product price doesn't exist, create it

      $args{price_id} = $priceID;
      if ( $execMode ) {
         my $ppObj = RPS::DB::Item::ProductPrice->Create( %args );
         $ppObj->save();
         $productPriceID = $ppObj->product_price_id;
         ++$count{product_price};
         report("   Created product_price $productPriceID : ". Dumper(\%args));
      } else {
         report("   Non-exec mode, skipped product_price : ". Dumper(\%args));
      }
   }
   return $productPriceID;
}# _createProductPrice

sub _getPriceLevelID {
   my ( $priceLevelName ) = @_;
   my $priceLevelID = $gPriceLevelMap{ lc $priceLevelName };
   return $priceLevelID;
}

#---------------------------------------------------------------------
# Name:        _setDefaultPriceLevel
# Description: sets the default price level for a product
# Arguments:
#   productID - a valid RPS product_id
#   priceLevelName - The name of the price level to associate with the
#     product.  Must match price_level.name exactly.
#---------------------------------------------------------------------
sub _setDefaultPriceLevel {
   my ( $productID, $priceLevelName ) = @_;
   assert($productID);
   assert($priceLevelName);

   my $pObj = RPS::DB::Item::Product->Lookup( product_id => $productID );

   my $priceLevelID = _getPriceLevelID($priceLevelName);

   die("   _setDefaultPriceLevel: Illegal price level '$priceLevelName'\n") if (!$priceLevelID);

   if ( $execMode ) {
      $pObj->default_price_level_id($priceLevelID);
      $pObj->save();
      report("   Set default price to '$priceLevelName' (id=$priceLevelID) "
         . "on productID $productID");
   } else {
      report("   Non-exec mode, skipped setting default price to "
         . "'$priceLevelName' (id=$priceLevelID) "
         . "on productID $productID");
   }
}# _setDefaultPriceLevel
1;