package Support::Implementation::ExpenseTemplate_NEW;
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::Expense;
use RPS::DB::Item::ExpenseName;
use RPS::DB::Item::ExpenseType;
#use RPS::DB::Item::AlbumContract;
use RPS::DB::Item::NewArtistContract;

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

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

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

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

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 = (
   "*Net Revenue / Expenses"  => 0,  # A
   "*Expense Type"            => 1,  # B
   "*Recoupable %"            => 2,  # C
   "*Amount %"                => 3,  # D
   "Memo"                     => 4,  # E
   "RS Contract ID"           => 5,  # F
   "Contract Title"           => 6,  # G
   "RS Album ID"              => 7,  # H
   "Catalog #"                => 8,  # I
   "Album Name"               => 9,  # J
   "RS Track ID"              => 10,  # K
   "ISRC"                     => 11,  # L
   "Track Title"              => 12,  # M
);

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

#-----------------------------------------------------------
# gContractMap maps contract IDs to their titles and payeeID
#-----------------------------------------------------------
my %gContractMap;

#----------------------------------------
# gPayeeMap maps payee IDs to their names
#----------------------------------------
my %gPayeeMap;

my $clientID;
my $execMode;

my $dbo;

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


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

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

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

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

   return $self;
}

sub parseHeader {
   my $self = shift;
}

#----------------------------------
# Contains map of trackIDs to ISRCs
#----------------------------------
my %gTrackISRCMap;

# Reference to SearchUtil object
my $gSearchObj;

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

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

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

   my %a = (
      clientID => $clientID
   );

   #$gSearchObj = new Support::Implementation::SearchUtil->new( %a );
   $gSearchObj = Support::Implementation::SearchUtil->new( %a );

   #------------------------
   # Fill the track-ISRC map
   #------------------------
   my $sql = "SELECT t.track_id, m.isrc "
      . "FROM track t JOIN master m USING(master_id) ";
   my $sth = $dbo->DoCmd($sql);
   while( my($id,$isrc) = $sth->fetchrow_array() ) {
      $gTrackISRCMap{$id} = $isrc;
   }


   #--------------------------------------
   # Fill the contract info and payee maps
   #--------------------------------------
   my $sql = "SELECT artist_contract_id, title, artist_payee_id "
      . "FROM new_artist_contract";
   my $sth = $dbo->DoCmd($sql);
   while( my($id,$title,$payeeID) = $sth->fetchrow_array() ) {
      $gContractMap{$id} = join("\t", $title, $payeeID );
   }

   my $sql = "SELECT artist_payee_id, name FROM artist_payee";
   my $sth = $dbo->DoCmd($sql);
   while( my($id,$name) = $sth->fetchrow_array() ) {
      $gPayeeMap{$id} = $name;
   }

   #return; # XXX XXX

   my $fileName = $self->name;

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

   #----------------------------------
   # excount keeps track of exceptions
   #----------------------------------
   my %excount = (
   );

   #--------------------------------------
   # rowcount keeps track of line counters
   #--------------------------------------
   my %rowcount = ();

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

   #my %gAlbumContractMap;
   #my $sql = "SELECT artist_contract_id,album_contract_id,album_id FROM album_contract";
   #my $sth = $dbo->DoCmd($sql);
   #while( my($contractID,$acID,$albumID) = $sth->fetchrow_array() ) {
   #   $gAlbumContractMap{ join("-",$contractID,$albumID) } = $acID;
   #}

   use constant kColumnNetRevenueExpenses => '*Net Revenue / Expenses';
   use constant kColumnExpenseType        => '*Expense Type';
   use constant kColumnRecoupablePercent  => '*Recoupable %';
   use constant kColumnAmount             => '*Amount';
   use constant kColumnMemo               => 'Memo';
   use constant kColumnRSContractID       => 'RS Contract ID';
   use constant kColumnContractTitle      => 'Contract Title';
   use constant kColumnArtistPayee        => 'Payee Name';
   use constant kColumnRSAlbumID          => 'RS Album ID';
   use constant kColumnCatalogNumber      => 'Catalog #';
   use constant kColumnAlbumName          => 'Album Name';
   use constant kColumnRSTrackID          => 'RS Track ID';
   use constant kColumnISRC               => 'ISRC';
   use constant kColumnTrackTitle         => 'Track Title';

   my @columns;
   push @columns, kColumnNetRevenueExpenses;
   push @columns, kColumnExpenseType;
   push @columns, kColumnRecoupablePercent;
   push @columns, kColumnAmount;
   push @columns, kColumnMemo;
   push @columns, kColumnRSContractID;
   push @columns, kColumnContractTitle;
   push @columns, kColumnArtistPayee;
   push @columns, kColumnRSAlbumID;
   push @columns, kColumnCatalogNumber;
   push @columns, kColumnAlbumName;
   push @columns, kColumnRSTrackID;
   push @columns, kColumnISRC;
   push @columns, kColumnTrackTitle;

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

      #-----------------------------------
      # Get all of the template variables.
      #-----------------------------------
      my $rowid           = $row->{'rowid'};

      my $category        = $row->{ kColumnNetRevenueExpenses() };
      my $expenseType     = $row->{ kColumnExpenseType()        };
      my $recoupablePct   = $row->{ kColumnRecoupablePercent()  };
      my $amount          = $row->{ kColumnAmount()             };
      my $memo            = $row->{ kColumnMemo()               };
      my $rsContractID    = $row->{ kColumnRSContractID()       };
      my $contractTitle   = $row->{ kColumnContractTitle()      };
      my $payeeName       = $row->{ kColumnArtistPayee()        };
      my $rsAlbumID       = $row->{ kColumnRSAlbumID()          };
      my $catalogNumber   = $row->{ kColumnCatalogNumber()      };
      my $albumName       = $row->{ kColumnAlbumName()          };
      my $rsTrackID       = $row->{ kColumnRSTrackID()          };
      my $isrc            = $row->{ kColumnISRC()               };
      my $trackTitle      = $row->{ kColumnTrackTitle()         };

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

      report("#### row($rowid) ".Dumper(\%$row));
      report("  DEBUG: category($category) expenseType($expenseType) recoupablePct($recoupablePct) amount($amount)");

      ++$rowcount{total};

      #--------------------------------------------------------
      # Make sure all columns are in place and if the column is
      # prefixed with an asterisk, that it actually has data in
      # it.
      #--------------------------------------------------------
      my $s;
      foreach my $c (@columns) {
         $s = _checkMissingColumn( $c, $row, \%excount );
         _appendString( $errorCode, $s ) if ( $s );
      }
      die("FATAL ERROR(S) DETECTED:\n$errorCode\n") if ( $errorCode );

      #-----------------------
      # Remove unneeded spaces
      #-----------------------
      $rsContractID =~ s/\s*//g if ( $rsContractID );
      $rsAlbumID =~ s/\s*//g if ( $rsAlbumID );
      $rsTrackID =~ s/\s*//g if ( $rsTrackID );

      $payeeName     =~ s/^\s*//g if ( $payeeName );
      $contractTitle =~ s/^\s*//g if ( $contractTitle );
      $catalogNumber =~ s/^\s*//g if ( $catalogNumber );
      $albumName     =~ s/^\s*//g if ( $albumName );
      $trackTitle    =~ s/^\s*//g if ( $trackTitle );
      $isrc          =~ s/^\s*//g if ( $isrc );

      # Clean-up
      $recoupablePct =~ s/%//;

      $amount =~ s/,//;
      $amount =~ s/\$//;
      if( $amount =~ /\(/ ) {
         $amount =~ s/\(//;
         $amount =~ s/\)//;
         $amount *= -1;
      }
 
      if ( $recoupablePct < 0 || $recoupablePct > 100 ) {
         _appendString( $errorCode, "Percent out of range" );
         ++$excount{percent_out_of_range};
      }

      if ( $amount == 0 ) {
         _appendString( $errorCode, "Zero amount" );
         ++$excount{zero_amount};
      }

      #===========================#
      #                           #
      #    Match Expense Info     #
      #                           #
      #===========================#

      #------------------------------
      # Validate the expense category
      #------------------------------
      my $preProcess;
      if ( (lc $category) eq 'net revenue' ) {
         $preProcess = 1; # 1 = net revenue deduction
      } elsif( (lc $category) eq 'expenses' ) {
         $preProcess = 0; # 0 = expense
      } else {
         _appendString( $errorCode, "Unknown expense category '$category'" );
         ++$excount{unknown_expense_category};
      }

      #-------------------------------------------------------------------
      # Check if the expense type is valid.  Note: the name of the expense
      # is actually stored in the expense_name table, not expense_type.
      #
      # We assume that the expense name has already been setup prior to
      # trying to import expenses.
      #-------------------------------------------------------------------
      my $expenseNameID;
      my $enObj = RPS::DB::Item::ExpenseName->Lookup( name => $expenseType );
      if ( not defined $enObj ) {
         #if ( defined $execMode ) {
         #   $enObj = RPS::DB::Item::ExpenseName->Create( name => $f_expenseType );
         #   $enObj->save();
         #   $expenseNameID = $enObj->expense_name_id;
         #   report("   Created expense_name $expenseNameID for name '$f_expenseType'");
         #   $newExpenseNameCount++;
         #} else {
         #   report("   Non-exec mode, skipping creation of expense_name");
         #   _generateException("EXPENSE_NAME_EXCEPTION", %inputLine);
         #   $expenseNameExceptionCount++;
         #   next;
         #}

         _appendString( $errorCode, "Expense type '$expenseType' not found" );
         ++$excount{expensetype_not_found};

      } else {
         $expenseNameID = $enObj->expense_name_id;
      }

      # TODO: Check recoupable %

      #=================================================#
      #                                                 #
      #                Match Artist Payee               #
      #                                                 #
      # Validate and match the artist payee information #
      #                                                 #
      #=================================================#
      my $artistPayeeID; # set if the payee is found
      if ( $payeeName && '' ne $payeeName ) {
         my $sql = "SELECT artist_payee_id, name FROM artist_payee "
            . "WHERE name=" . $dbo->DBQuote($payeeName);
         my $sth = $dbo->DoCmd($sql);
         if ( $sth->rows == 0 ) {
            _appendString( $errorCode, "Payee not found" );
            ++$excount{payee_not_found};
         } elsif( $sth->rows > 1 ) {

            if ( $rsContractID ) {
               #--------------------------------------------------------
               # If the RS contractID is specified in the template, then
               # see if the payeeID on the contract is one of the payees
               # returned using the name search.
               #--------------------------------------------------------
               my $sql2 = "SELECT artist_payee_id FROM new_artist_contract "
                  . "WHERE artist_contract_id=$rsContractID";
               my $sth2 = $dbo->DoCmd($sql2);
               my($zzPayeeID) = $sth2->fetchrow_array();
               while( my($id) = $sth->fetchrow_array() ) {
                  if ( $id == $zzPayeeID ) {
                     $artistPayeeID = $id;
                     next;
                  }
               }

               if ( !$artistPayeeID ) {
                  _appendString( $errorCode, "Payee not on contract" );
                  ++$excount{payee_not_on_contract};
               }
            } else {
               #-----------------------------------------------------------
               # If no RS contractID was specified and we have more than
               # one payee with the same name, then just throw a duplicate
               # payee exception.  I suppose we could try to find the
               # contract and then look for the payee, but let's just force
               # the end-user to fill-in a contractID if there is more than
               # one payee with a given name.
               #-----------------------------------------------------------
               _appendString( $errorCode, "Duplicate payee" );
               ++$excount{duplicate_payee};

            }

         } else {
            my($zzPayeeID, $name) = $sth->fetchrow_array();
            $artistPayeeID = $zzPayeeID;
            report("DEBUG:MATCH_ARTIST found payee($name) payeeID($artistPayeeID)");
         }
      } else {
         report("DEBUG:MATCH_ARTIST: no payee specified");
      }
      # MATCH ARTIST PAYEE

      #============================================================#
      #                                                            #
      #                       Match Album                          #
      #                                                            #
      # If an album ID was specified, make sure it's valid.        #
      # Otherwise look for the album using the title and catalog#. #
      #                                                            #
      #============================================================#
      my $albumID;

      if ( $rsAlbumID && ('' ne $rsAlbumID) ) {
         my $o = RPS::DB::Item::Album->Lookup( album_id => $rsAlbumID );
         if ( !$o ) {
            report("_EXCEPTION: albumID $rsAlbumID is invalid -- templateAlbumName($albumName)");

            _appendString( $errorCode, "Invalid albumID" );
            ++$excount{invalid_albumid};
         } else {
            # Make sure the catalog# and title match what's in the template
            if ( $albumName && ('' ne $albumName) && ($albumName ne $o->title) ) {

               report("_EXCEPTION: templateAlbumName($albumName) doesn't match albumName("
                  . $o->title . ")");

               _appendString( $errorCode, "Album name mismatch" );
               ++$excount{albumname_mismatch};
            }
            if ( $catalogNumber && ('' ne $catalogNumber) && ($catalogNumber ne $o->catalog_number) ) {
               _appendString( $errorCode, "Catalog number mismatch" );
               ++$excount{catalognumber_mismatch};
            }
            $albumID = $o->album_id;
         }
      } elsif( ($albumName && '' ne $albumName) || ($catalogNumber && '' ne $catalogNumber)) {

         report("DEBUG:MATCH_ALBUM: no rsTrackID specified, searching for album...");
         #-------------------------------------------------------------------
         # The albumID not specified.  Lookup the album information using the
         # title and catalog number
         #-------------------------------------------------------------------
         my ($zzAlbumID, $st) = $gSearchObj->findAlbum(
            album_name => $albumName,
            catalog_number => $catalogNumber,
         );
         if ( $st ) {

            if ( $st == Support::Implementation::SearchUtil::kAlbumNotFound ) {

               report("_EXCEPTION: album not found - title(". _printNull($albumName) .") "
                  ."cat#(". _printNull($catalogNumber) .")");
               _appendString( $errorCode, "Album not found" );
               ++$excount{album_not_found};

            } elsif ( $st && $st == Support::Implementation::SearchUtil::kNonUniqueAlbumName ) {

               report("_EXCEPTION: duplicate album($albumName) cat#($catalogNumber)");
               _appendString( $errorCode, "Duplicate album name" );
               ++$excount{duplicate_album};

            } elsif ( $st && $st == Support::Implementation::SearchUtil::kNonUniqueCatalogNumber ) {

               report("_EXCEPTION: duplicate album($albumName) cat#($catalogNumber)");
               _appendString( $errorCode, "Duplicate catalog number" );
               ++$excount{duplicate_catno};

            } else {
               die("ERROR: Unchecked status code '$st' while calling findAlbum()\n");
            }
         } else {
            report("DEBUG:MATCH_ALBUM: found album($zzAlbumID) using title(" ._printNull($albumName) .") "
               . "cat#(". _printNull($catalogNumber) . ")");
            $albumID = $zzAlbumID;
         }

      } else {
         report("DEBUG:MATCH_ALBUM: no rsTrackID, title or cat# specified ... ");
         
      }# MATCH ALBUM

      #===========================================================#
      #                                                           #
      #                       Match Track                         #
      #                                                           #
      # If track information is not specified, then expenses will #
      # be applied at the _album level_.                          #
      #                                                           #
      # If a track ID was specified, make sure it's valid.        #
      # Otherwise look for the track using the title and ISRC.    #
      #                                                           #
      #===========================================================#
      my $trackID;

      if ( $rsTrackID && ('' ne $rsTrackID) ) {
         my $o = RPS::DB::Item::Track->Lookup( track_id => $rsTrackID );
         if ( !$o ) {
            report("_EXCEPTION: trackID $rsTrackID is invalid -- templateTrackName($trackTitle)");

            _appendString( $errorCode, "Invalid trackID" );
            ++$excount{invalid_trackid};
         } else {

            my $errors; # set if there are _any_ track errors found

            #----------------------------------------------------------------
            # Make sure the track title and ISRC match what's in the template
            #----------------------------------------------------------------
            if ( $trackTitle && ('' ne $trackTitle) && ($trackTitle ne $o->title) ) {

               report("_EXCEPTION: templateTrackTitle($trackTitle) doesn't match trackTitle("
                  . $o->title . ")");

               _appendString( $errorCode, "Track title mismatch" );
               ++$excount{tracktitle_mismatch};
               ++$errors;
            }
            if ( $isrc && ('' ne $isrc) ) {
               if ( $isrc ne $gTrackISRCMap{$rsTrackID} ) {
                  report("_EXCEPTION: templateISRC($isrc) doesn't match trackISRC("
                     . $gTrackISRCMap{$rsTrackID} . ")");

                  _appendString( $errorCode, "Track ISRC mismatch" );
                  ++$excount{trackisrc_mismatch};
                  ++$errors;
               }
            }

            #--------------------------------------------------------------
            # Make sure the track's albumID matches any albumID information
            # derived from the album columns of the template
            #--------------------------------------------------------------
            if ( $albumID ) {
               my $zzAlbumID = $o->album_id;
               if ( $albumID != $zzAlbumID ) {

                  report("_EXCEPTION: rsTrackID($rsTrackID) is for "
                     . "albumID($zzAlbumID), but template albumID is $albumID");

                  _appendString( $errorCode, "Track albumID mismatch" );
                  ++$excount{track_albumid_mismatch};
                  ++$errors;
               }
            }


            $trackID = $rsTrackID if ( !$errors );
         }
      } else {

         #----------------------------------------------------------------
         # If we have a track title and/or isrc then try to find the track
         #----------------------------------------------------------------
         if ( ($trackTitle && '' ne $trackTitle) ||
              ($isrc && '' ne $isrc) ) {

            if ( !$albumID ) {
               report( "_EXCEPTION: found track information "
                  . "track(" . _printNull($trackTitle) . ") "
                  . "isrc(" . _printNull($isrc) . ") "
                  . "but no album specified");

               _appendString( $errorCode, "Track without album" );
               ++$excount{track_without_album};

            } else {
               my %args = (
                  album_id => $albumID,
               );
               $args{isrc}       = $isrc if ( $isrc );
               $args{track_name} = $trackTitle if ( $trackTitle );

               my($zzTrackID, $st) = $gSearchObj->findTrack( %args );

               # Did we find the track or is it exception time?
               if ( $st ) {
                  if ( $st == Support::Implementation::SearchUtil::kTrackNotFound ) {
                     report("_EXCEPTION: track(" . _printNull($trackTitle) . ") "
                        . "isrc(". _printNull($isrc) . ") not found");
                     _appendString( $errorCode, "Track not found" );
                     ++$excount{track_not_found};
                  } else {
                     die("ERROR: Unchecked status code '$st' while calling findTrack()\n");
                  }
               } else {
                  report("DEBUG:MATCH_TRACK: found track($zzTrackID) using "
                     . "title(" ._printNull($trackTitle) .") "
                     . "isrc(". _printNull($isrc) . ")");
                  $trackID = $zzTrackID;
               }

            }
         }


      }# MATCH TRACK

      if ( !$albumID && !$trackID ) {
         report("  ## Album and/or track not found -- skipping expense!!!");
         $row->{'error-code'} = $errorCode;
         ++$rowcount{rows_failed};
         next;
      }

      #/////////////////////////////////////////////////////////////
      #
      # Now that the album or track has been found, find the set of
      # contracts that we'll process.
      #
      #/////////////////////////////////////////////////////////////


      #=============================================#
      #                                             #
      #               Match Contract                #
      #                                             #
      # Validate and match the contract information #
      #                                             #
      #=============================================#

      #----------------------------------------------------------------
      # 'contracts' will hold a list of contractIDs to which expenses
      # will be attached.
      #
      # IMPORTANT NOTE: The only time there should be more than one
      # contractID in 'contracts' is if the contractID, contract title
      # and artist payee are all left blank.
      #----------------------------------------------------------------
      my @contracts;

      my $contractID;
      if ( $rsContractID && ('' ne $rsContractID) ) {

         # contractID specified
         my $o = RPS::DB::Item::NewArtistContract->Lookup(
            artist_contract_id => $rsContractID,
         );
         if ( !$o ) {
            _appendString( $errorCode, "Invalid contractID" );
            ++$excount{invalid_contractid};
         } else {

            my $err;

            if ( $artistPayeeID && $artistPayeeID != $o->artist_payee_id ) {
               _appendString( $errorCode, "Specified payee doesn't match payee on contract" );
               ++$excount{contract_payee_mismatch};
               $err=1;
            }

            if ( $contractTitle && $contractTitle ne $o->title ) {
               _appendString( $errorCode, "Specified contract title doesn't match title on contract" );
               ++$excount{contract_title_mismatch};
               $err=1;
            }

            $contractID = $o->artist_contract_id if ( !$err );

            #push @contracts, $contractID;

            #----------------------------------------------
            # Find the album and/or track-level attachments
            #----------------------------------------------
            if ( $trackID ) {
               my $sql = "SELECT track_contract_id FROM track_contract "
                  . "WHERE track_id=$trackID "
                  . "AND artist_contract_id=$contractID";
               my $sth = $dbo->DoCmd($sql);
               while( my($tcID) = $sth->fetchrow_array() ) {
                  my $key = join("-", $contractID, $tcID, 2);  # 2 = track contract
                  push @contracts, $key;
               }
            } elsif ( $albumID ) {
               my $sql = "SELECT album_contract_id FROM album_contract "
                  . "WHERE album_id=$albumID "
                  . "AND artist_contract_id=$contractID";
               my $sth = $dbo->DoCmd($sql);
               while( my($acID) = $sth->fetchrow_array() ) {
                  my $key = join("-", $contractID, $acID, 1);  # 1 = album contract
                  push @contracts, $key;
               }
            } else {
               die("DEBUG: specified contractID '$rsContractID' not attached!!!\n");
            }

         }

      } else {
         #------------------------------------------------------------------
         # contractID not specified.  If the title was specified, then we'll
         # limit the search to those contracts with the specified title.
         # If the artist payee was also specified, it will also be used to
         # filter the list of contracts to process.
         #
         #------------------------------------------------------------------

         if ( $contractTitle && '' ne $contractTitle ) {

            #------------------------------------------------------------
            # Look for the contract.  If an artistPayeeID is available,
            # use it to further identify the contract.  Note that you
            # may get a duplicate contract issue if the title appears
            # more than once, or if the same title/payeeID combination
            # appears more than once.  The latter condition is highly
            # unlikely, but since we don't enforce title/payee uniqueness
            # then it is something we should check for.
            #------------------------------------------------------------

            my $sql = "SELECT artist_contract_id FROM new_artist_contract "
                    . "WHERE title="
                    . $dbo->DBQuote($contractTitle);

            $sql .= " AND artist_payee_id=$artistPayeeID" if ($artistPayeeID);

            my $sth = $dbo->DoCmd($sql);
            if ( $sth->rows == 0 ) {
               report("_EXCEPTION: contract not found '$contractTitle' :\n$sql");
               _appendString( $errorCode, "Contract not found" );
               ++$excount{contract_not_found};
            } elsif( $sth->rows > 1 ) {

               if ( $artistPayeeID ) {

                  die("FATAL ERROR: found too many contracts using title and payee name!\n$sql\n");

               } else {

                  report("_EXCEPTION: contract title not unique '$contractTitle'");
                  #_appendString( $errorCode, "Duplicate contract" );
                  _appendString( $errorCode, "Duplicate contract title exists - payee name required" );
                  ++$excount{duplicate_contract_title};

               }
            } else {

               ($contractID) = $sth->fetchrow_array();
               #push @contracts, $contractID;

               #----------------------------------------------
               # Find the album and/or track-level attachments
               #----------------------------------------------
               if ( $trackID ) {
                  my $sql = "SELECT track_contract_id FROM track_contract "
                     . "WHERE track_id=$trackID "
                     . "AND artist_contract_id=$contractID";
                  my $sth = $dbo->DoCmd($sql);
                  while( my($tcID) = $sth->fetchrow_array() ) {
                     my $key = join("-", $contractID, $tcID, 2);  # 2 = track contract
                     push @contracts, $key;
                  }
               } elsif ( $albumID ) {
                  my $sql = "SELECT album_contract_id FROM album_contract "
                     . "WHERE album_id=$albumID "
                     . "AND artist_contract_id=$contractID";
                  my $sth = $dbo->DoCmd($sql);
                  while( my($acID) = $sth->fetchrow_array() ) {
                     my $key = join("-", $contractID, $acID, 1);  # 1 = album contract
                     push @contracts, $key;
                  }
               } else {
                  die("DEBUG2: specified contractID '$rsContractID' not attached!!!\n");
               }

            }

         } else {
            #----------------------------------------------------------------
            # If the RS contract ID and title are both blank, but the payee
            # is not then exception out.  Otherwise fill 'contracts' with all
            # contractIDs
            #
            # 12/11/09: If no contract information was found (which is
            # something very popular with KillRockStars), then we want to
            # limit the contracts to those associated with the album or
            # track.
            #----------------------------------------------------------------
            if ( $artistPayeeID ) {

               # payee specified (and found), but no contract info specified!
               _appendString( $errorCode, "Missing contract title" );
               ++$excount{missing_contract_title};

            } elsif ( !$payeeName || ($payeeName && '' eq $payeeName) ) {

               ## Ok, no contract info or payee.  Default to all contracts
               #my $sql = "SELECT artist_contract_id FROM new_artist_contract";
               #my $sth = $dbo->DoCmd($sql);
               #while( my($id) = $sth->fetchrow_array() ) {
               #   push @contracts, $id;
               #}

               #--------------------------------------------------------------
               # For KRS, they want to attach the expense to all album and/or
               # track contracts.  This may or may not be applicable to others
               # besides KRS (I mean, what if a client simply forgets to fill
               # in the contract title or payee information?), so the importer
               # just dies if anyone but KRS tries this sh*t.
               #--------------------------------------------------------------
               die("Wildcard contract mode detected for non-KRS client...\n")
                  if ( !$clientID );
#die("wildcard contract mode...  albumID($albumID) trackID($trackID)");
               #my $sql = "SELECT artist_contract_id FROM new_artist_contract";
               #my $sth = $dbo->DoCmd($sql);
               #while( my($id) = $sth->fetchrow_array() ) {
               #   push @contracts, $id;
               #}

               #----------------------------------------------
               # Find the album and/or track-level attachments
               #----------------------------------------------
               if ( $trackID ) {
                  my $sql = "SELECT track_contract_id, artist_contract_id FROM track_contract "
                     . "WHERE track_id=$trackID ";
                     #. "AND artist_contract_id=$contractID";
                  my $sth = $dbo->DoCmd($sql);
                  while( my($tcID,$contractID) = $sth->fetchrow_array() ) {
                     my $key = join("-", $contractID, $tcID, 2);  # 2 = track contract
                     push @contracts, $key;
                  }
               } elsif ( $albumID ) {
                  my $sql = "SELECT album_contract_id, artist_contract_id FROM album_contract "
                     . "WHERE album_id=$albumID ";
                     #. "AND artist_contract_id=$contractID";
                  my $sth = $dbo->DoCmd($sql);
                  while( my($acID,$contractID) = $sth->fetchrow_array() ) {
                     my $key = join("-", $contractID, $acID, 1);  # 1 = album contract
                     push @contracts, $key;
                  }
               } else {
                  die("DEBUG3: specified contractID '$rsContractID' not attached!!!\n");
               }

            } else {

               die("WTF? How'd you get here? payeeName($payeeName)\n");
            }

         }
      }# MATCH CONTRACT


      #------------------------------------------------------------------
      # If there are any errors at this point, stop processing this expense
      #------------------------------------------------------------------
      if ( $errorCode ) {
         $row->{'error-code'} = $errorCode;
         ++$rowcount{rows_failed};
         next;
      }

      # Sanity check -- we should have an albumID and/or trackID.
      if ( !$albumID && !$trackID ) {
         die("row($rowid): WTF -- neither albumID or trackID is set!?\n");
      }



      my $numContracts = scalar @contracts;
report("DEBUG: #### There are $numContracts contract(s)");
      if ( $numContracts > 1 ) {
         #die("   There are $numContracts contract(s) to process!!! "
         #   . join(",",@contracts)
         #);
         report("   DEBUG: MULTIPLE_CONTRACTS: There are $numContracts "
            . "contract(s) to process");
         if ( $clientID != 169 ) {
            die("   ***** MULTIPLE_CONTRACTS: We only do this for Kill Rock Stars!!! ");
         }
      }
      

      # Loop over all the contracts flagged for updating.  Unless the template
      # didn't have any contract info (e.g., rs contractID, title and payee were
      # all blank), 'contracts' will only have one contractID in it and
      # numContracts will be 1.
      #
      # To determine where the expense will actually attach, we need to consider
      # whether we're attaching at the album or track level.
      #
      # For a specified contract (e.g. @contracts == 1), then we'll be attaching
      # to a specific album or track, and 'parents' will just hold the information
      # for the album (or track) contract.
      #
      # For all contracts (@contracts > 1), then we'll be attaching to a specific
      # album or possibly to all track(s) that the contract is attached to.  To
      # handle the latter condition, we use the 'parents' list to keep track of
      # the entities for which expenses will be created.

      # 'parents' holds the parentID concatenated to the parent_type.
      my @parents;

      # When we're dealing with multiple contracts for a given row, we only
      # need to know if a row had any errors or not
      my $rowHasErrors;


      # Loop over all of the album and/or track contractID's found above.
      # Each element in the contract list is either an album contract or
      # a track contract.
      for my $key (@contracts) {

         my($contractID, $parentID, $parentType) = split("-",$key);

         report("   #### Processing contract($contractID) parentID($parentID) type($parentType)");

assert($contractID);
         push @parents, join("-", $parentID, $parentType);

#         if ( !$trackID ) {
#
#            #------------------------
#            # look for album contract
#            #------------------------
#
#            my $sql = "SELECT album_contract_id FROM album_contract "
#               . "WHERE artist_contract_id=$contractID "
#               . "AND album_id=$albumID";
#            my $sth = $dbo->DoCmd($sql);
#            if ( $sth->rows == 0 ) {
#
#               if ( $numContracts == 1 ) {
#
#                  report("_EXCEPTION: contract($contractID) is not attached to albumID($albumID)");
#                  _appendString( $errorCode, "Album not attached" );
#                  ++$excount{album_not_attached};
#
#               } else {
#                  # Look for track contract(s)
#                  my $sql = "SELECT track_contract_id FROM track_contract "
#                     . "WHERE artist_contract_id=$contractID "
#                     . "AND track_id IN "
#                     . "  (SELECT track_id FROM track WHERE album_id=$albumID)";
#                  my $sth = $dbo->DoCmd($sql);
#
#                  if ( $sth->rows == 0 ) {
#
#                     # XXX XXX XXX
#                     # Do _not_ log this as an exception unless you want to create
#                     # an BF exception field in the exception report!
#                     #report("   _EXCEPTION: contract($contractID) not attached "
#                     #   . "to album or track");
#                     #_appendString( $errorCode, "Not attached to album or track" );
#
#                     ++$excount{not_attached_to_album_or_track};
#
#                  } else {
#                     $parentType = 2; # track contract
#                     while( my($tcID) = $sth->fetchrow_array() ) {
#                        push @parents, join("-", $tcID, $parentType);
#
#                        # XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX
#                        #
#                        # if we are not to attach to all track contracts,
#                        # then you don't need this loop (just attach to the
#                        # first trackContractID you find) and the whole 'parents'
#                        # thing becomes OBE.
#                        #
#                        # XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX XXX
#                     }
#
#                  }
#
#               }
#
#            } elsif( $sth->rows == 1 ) {
#
#               ($parentID) = $sth->fetchrow_array();
#               $parentType = 1; # album contract
#
#               push @parents, join("-",$parentID,$parentType);
#
#            } else {
#
#               die("ERROR: contract($contractID) is attached multiple times to albumID($albumID)\n");
#
#            }
#
#         } else {
#
#            my $sql = "SELECT track_contract_id FROM track_contract "
#               . "WHERE artist_contract_id=$contractID "
#               . "AND track_id=$trackID";
#            my $sth = $dbo->DoCmd($sql);
#            if ( $sth->rows == 0 ) {
#               report("_EXCEPTION: contract($contractID) is not attached to trackID($trackID)");
#               _appendString( $errorCode, "Track not attached" );
#               ++$excount{track_not_attached};
#            } elsif( $sth->rows > 1 ) {
#               die("ERROR: contract($contractID) is attached multiple times to trackID($trackID)\n");
#            }
#            ($parentID) = $sth->fetchrow_array();
#            $parentType = 2; # track contract
#
#            push @parents, join("-",$parentID,$parentType);
#
#         }


         #-----------------------------------------------------------------
         #
         # For multi-contracts, we don't want to generate an error msg
         # for every contract issue otherwise the "Error Code" field
         # will be too large.  We don't want to ignore these errors
         # either.  'errSuppressed' will be set if we're in multi-contract
         # mode and the expense type isn't setup or if the recoupable
         # percent isn't setup.
         #
         #-----------------------------------------------------------------
         my $errSuppressed;

         #------------------------------------------------------------------------------
         # If this is a net revenue deduction, check if the contract has a default
         # term and that it's not zero percent.
         #------------------------------------------------------------------------------
         if ( $preProcess == 1 ) { # net rev
            my $sql = "SELECT artist_contract_term_id, rate "
               . "FROM new_artist_contract_term "
               . "WHERE artist_contract_id=$contractID "
               . "AND priority=0";
            my $sth = $dbo->DoCmd($sql);
            if ( $sth->rows == 0 ) {

               report("WARNING: netrev deduction but contract($contractID) has no_default_term "
                  . "numContracts = $numContracts");
               if ( $numContracts == 1 ) {
                  # XXX XXX XXX
                  #report("_EXCEPTION: netrev deduction but contract($contractID) has no default term");
                  #_appendString( $errorCode, "No default term" );
                  _appendString( $errorCode, "Default term cannot be 0% for net revenue expense" );
               }
               $errSuppressed = 1;
               ++$excount{no_default_term};

            } else {
               my ( $termID, $rate ) = $sth->fetchrow_array();
               if ( 0 == $rate ) {
                  if ( $numContracts == 1 ) {
                     # XXX XXX XXX
                     report("_EXCEPTION: netrev deduction but contract($contractID) has 0% default term");
                     #_appendString( $errorCode, "Zero percent default term" );
                     _appendString( $errorCode, "Default term cannot be 0% for net revenue expense" );
                  }
                  $errSuppressed = 1;
                  ++$excount{zero_percent_default_term};
               }
            }
         }


         #-----------------------------------------------------------------
         # Find the expense_type.  Search based on the contract and expense
         # name information.  If the expense type exists, double-check the
         # percentage on the existing expense type.  If they're different
         # then it's important to make sure that this was intentional.
         #-----------------------------------------------------------------
         my %etArgs = (
            artist_contract_id => $contractID,
            expense_name_id => $expenseNameID,
         );

         my $expenseTypeID;
         my $etObj = RPS::DB::Item::ExpenseType->Lookup(%etArgs);
         if ( not defined $etObj ) {

            #report(join("\t",
            #   "_EXCEPTION: missing expense type",
            #   $contractID,
            #   $expenseType,  # really expense_name.name
            #   $recoupablePct,
            #));

            my($contractTitle,$payeeID) = split("\t", $gContractMap{$contractID} );
            my $payeeName = $gPayeeMap{$payeeID};

            #report(join("\t",
            #   "LICENSE_INCOME_EXPENSE_TEMPLATE:",
            #   "contractTitle($contractTitle)",
            #   "contractID($contractID)",
            #   "payee($payeeName)",
            #   "payeeID(" . _printNull($artistPayeeID) . ")",
            #   "expenseType($expenseType)",  # really expense_name.name
            #   "recoupablePct($recoupablePct)",
            #));
            report("WARNING: expensetype_not_setup_on_contract");
            report(join("\t",
               "LICENSE_INCOME_EXPENSE_TEMPLATE:",
               $contractTitle,  # A - Contract Title
               $payeeName,      # B - Payee Name
               "",              # C - Licensing Income Type
               "",              # D - Licensing Income Net Revenue Rate %
               $expenseType,    # E - Recoupable Expense Type
               $recoupablePct,  # F - Recoupable Percentage
            ));

            $errSuppressed = 1;
            if ( $numContracts == 1 ) {
               # XXX XXX XXX
               _appendString( $errorCode, "Expense type not setup on contract" );
            }
            ++$excount{expensetype_not_setup_on_contract};

         } else {
            my $currentPercent = $etObj->percent;
            my $etID = $etObj->expense_type_id;

            # If the percentages don't match, then the expense type is not setup
            # on the contract.  This will prevent the importer from attaching the
            # expense to the contract.
            if ( $currentPercent != $recoupablePct ) {

               $errSuppressed = 1;
               report( "WARNING: recoupable_pct_not_setup_on_contract $contractID, "
                  . "expenseType($expenseType)");
               if ( $numContracts == 1 ) {
                  # XXX XXX XXX
                  _appendString( $errorCode, "Recoupable % is not setup on contract" );
               }
               ++$excount{recoupable_pct_not_setup_on_contract};

            } else {
            
               $expenseTypeID = $etObj->expense_type_id;
               report("   Using existing expense_type $expenseTypeID : ". Dumper(\%etArgs));
            }
         }



         # Ok, last chance to check for errors...
         if ( $errorCode || $errSuppressed ) {
            #$row->{'error-code'} = $errorCode;
            #++$rowcount{rows_failed};
            $rowHasErrors = 1;
            next;
         }

         my $numParents = scalar @parents;

         next if ( $numParents == 0 || !$parentType );
#die("DEBUG: targets: ". join(", ",@parents) ) if ( $parentType == 2 && (scalar @parents) > 0);

         # Create the expense.  Because of the possibility of having to attach
         # the expense to multiple trackContractIDs on the current contract
         # (see above), we have to do this 'parents' kludge...

#         for my $p (@parents) {
#
#            my($parentID,$parentType) = split("-", $p);

            my %expArgs = (
               expense_type_id => $expenseTypeID,
               parent_id       => $parentID,
               parent_type     => $parentType,
               #memo            => $memo,
               amount          => $amount,
               percent         => $recoupablePct,
               pre_process     => $preProcess,
               processed       => 0,
            );
            $expArgs{memo} = $memo if ( $memo );

            my $expObj = RPS::DB::Item::Expense->Lookup(%expArgs);
            my $expenseID;

            #-----------------------------------------------------------------------
            # Per FB8247, it's possible that the same expense may be defined
            # more than once in the template, which means we can't reject a
            # template line if an expense with matching data already exists.
            #
            # TODO: Maybe modify the lookup logic to take date_created into account?
            #-----------------------------------------------------------------------
            if ( $expObj ) {
               $expenseID = $expObj->expense_id;
               report("   Expense $expenseID exists, creating anyway");
            }

            if ( defined $execMode ) {
               $expObj = RPS::DB::Item::Expense->Create(%expArgs);
               $expObj->save();
               $expenseID = $expObj->expense_id;
               report("   Created expense $expenseID ".Dumper(\%expArgs));
               ++$count{expense};
            } else {
               report("   Non-exec mode, skipping expense creation : ".Dumper(\%expArgs));
            }

            $row->{'rs-expense-id'} = $expenseID; # why bother?   this is so broke...

#         }# parent loop

      }# contract loop

      if ( $rowHasErrors ) {
         $row->{'error-code'} = $errorCode;
         ++$rowcount{rows_failed};
      }
      ++$rowcount{rows_imported};


   }#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;
   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 );
   report(" ");
   
   foreach my $c (keys %count) {
      my $v = $count{$c};
      printf("%30s %d\n", $c, $v);
   }

   report(" ");
   foreach my $c (keys %rowcount) {
      my $v = $rowcount{$c};
      printf("%30s %d\n", $c, $v);
   }
}#_processData

#------------------------------------------------------------------
# _checkMissingColumn - checks if a column is missing or not.  Also
# ensures that a required columns (prefixed with an asterisk) has
# data in it.  A string containing error(s) is returned, otherwise
# undef is returned if column is OK.
#------------------------------------------------------------------
sub _checkMissingColumn {
   my($colName, $rows, $excount) = @_;
   my $errStr;
   if ( ! exists $rows->{$colName} ) {
      report("_checkMissingColumn -- column '$colName' is missing");
      _appendString($errStr, "Column '$colName' not found");

      my $colClean = clean($colName);
      my $key = "column_" . $colClean . "_not_found";
      ++$excount->{$key};
   }

   #------------------------------------------------------------------
   # If the column name starts with an asterisk then it can't be blank
   #------------------------------------------------------------------
   if ( $colName =~ /^\*/ ) {
      my $v = $rows->{$colName};
      # strip out leading spaces
      $v =~ s/^\s*//g;
      if ( '' eq $v ) {
         _appendString($errStr, "Column '$colName' can't be blank");
      }
   }
   return $errStr;

}# _checkMissingColumn


#--------------------------------------------------------------------------
# _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
# expenseID will be stored in the import status column ("expenseID(###)"),
# otherwise this column will contain the string "__FAIL__".
#--------------------------------------------------------------------------
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 $expenseID = $row->{'rs-expense-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__" : "expense($expenseID)";

      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 _printNull {
   my($s) = @_;
   return $s ? $s : "NULL";
}

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

1;