package Support::Implementation::ArtistPayeeTemplate;
# 3/12/10 - When checking for duplicate payees, we look
# at the payee name and the client account id.
# 5/4/10 - Added logic to set currency code for client
# 1/13/11 - Made payor name case-insensitive
# 10/24/12 - Updated to reflect current artist_payee schema
#  (specifically, the street_address_2 and street_address_3 fields
#   that supplement the street_address field).
# 1/28/13 - Moved payor check so that we catch payor-issues before
#   we try to create a new payee; allow 0 min payment to be setup.
# 9/3/13 - Strip newlines in payor name
# 2/5/14 - Enabled Excel 2007 support
# 3/7/15 - Cleaned up payee status logic
# 7/7/15 - Added 'on hold' synonym
# 2/24/16 - Remove leading/trailing spaces on some fields
#  - Make payee searches by name only.
# 7/11/16 - Updated error handling.
# 10/14/16 - Allow 'onhold' status
# 12/2/16 - Add summary info to STDERR
# 11/6/16 - Added support for 'statement-distribution' (payee portal)
# 1/5/18 - Bug fixes related to payee portal testing.
# 3/13/18 - Added include for RPS::Portal::SendInvite
# 1/29/19 - Use clean_name for duplicate checking during 1st pass
#
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::CurrencyFormat;
use Common::Util qw( clean clean_name_catalog clean_name trimspaces);

use RPS::DB::Item::ArtistPayee;
use RPS::DB::Item::ArtistPayeeAccount;
use RPS::DB::Item::FinanceAccount;
use RPS::DB::Item::PendingTransaction;
use RPS::Portal::SendInvite;

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

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

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

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 = (
   "artist-payee-name" => 0,  # A
   "payee" => 0,  # A  ALIAS
   "client-account-no" => 1,  # B
   "status"            => 2,  # C
   "address-1"         => 3,  # D
   "address-2"         => 4,  # E
   "address-3"         => 5,  # F
   "address-4"         => 6,  # G
   "address-5"         => 7,  # H
   "city"              => 8,  # I
   "state-province"    => 9,  # J
   "zip-postal"        => 10, # K
   "country"           => 11, # L
   "e-mail"            => 12, # M
   "email"             => 12, # M  ALIAS
   "phone"             => 13, # N
   "fax"               => 14, # O
   "tax-id"            => 15, # P
   "comments"          => 16, # Q
   "statement-distribution" => 17, # R
   "payor-name"        => 18, # S
   "minimum-payment"   => 19, # T
   "opening-balance"   => 20, # U
);

#--------------------------------------------------------------
# Maps column #'s to a unique key (headername).  The reverse of
# templateHeader, but with actual column #.
#--------------------------------------------------------------
my %gColumnMap = ();

#-------------------------------------------
# Maps status codes to their internal values
#-------------------------------------------
my %gStatusMap = (
   "I" => 0,
   "A" => 1,
   "H" => 2,
);

#--------------------------------------------------------
# gHeaderDisplayed is a flag that we set if we've already
# displayed the template header during an exceptions dump
#--------------------------------------------------------
my $gHeaderDisplayed;

my $clientID;
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("ArtistPayeeTemplate::_init -- args = ". Dumper(\%args));

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

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

   return $self;
}

sub parseHeader {
   my $self = shift;
}

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

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

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

   my $fileName = $self->name;

   if( $self->isExcel2003( $fileName ) ) {
      print("ArtistPayeeTemplate::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("ArtistPayeeTemplate::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("ArtistPayeeTemplate::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 = (
      payee_exists => 0,
      unknown_country => 0,
   );

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

   #------------------------
   # Setup payor information
   #------------------------
   my $gDefaultPayorID;
   my %gPayorMap = (); # maps names to ID
   my $sql = "SELECT payor_id,name,is_default FROM payor";
   my $sth = $dbo->DoCmd($sql);
   while( my($id,$name,$isDefault) = $sth->fetchrow_array() ) {
      $gPayorMap{lc $name} = $id;
      if ( $isDefault ) {
         $gDefaultPayorID = $id;
      }
   }
   if ( not defined $gDefaultPayorID ) {
      die("No default payor setup for client");
   }

   #---------------------------------
   # Get the client's currency format
   #---------------------------------
   $sql = "SELECT country_code FROM client WHERE client_id=$clientID";
   $sth = $cdbo->DoCmd($sql);
   my($cCode) = $sth->fetchrow_array();
   my $currencyFormat = new Common::CurrencyFormat( countryCode => $cCode );
   my $denomination = $currencyFormat->currencyCode();

   if ( !$denomination ) {
      die("ERROR: Unable to find currency denomination for client($clientID)");
   }


   my $labelContact;
   my $labelName;
   my @inviteList; # list of payeeIDs that require a portal invite
   #----------------------------------------------------------
   # Get the label contact info (used for online distribution)
   # The columns with the info haven't been fully deployed during
   # payee portal development, so we do a little check to ensure
   # that they exist before trying to read data from them.
   #----------------------------------------------------------
   $sql = "SELECT * FROM information_schema.COLUMNS "
      . "WHERE TABLE_SCHEMA='RSCOMMON' "
      . "AND TABLE_NAME='client' "
      . "AND (COLUMN_NAME='email' OR COLUMN_NAME='display_name') "
      ;
   $sth = $cdbo->DoCmd($sql);
   if( $sth->rows != 2 ) {
      die("ERROR: RSCOMMON.client table is missing 'email' and 'display_name' columns !!!");
   }
   else {
      $sql = "SELECT email, display_name FROM client WHERE client_id=$clientID";
      $sth = $cdbo->DoCmd($sql);
      ($labelContact, $labelName) = $sth->fetchrow_array();
   }


   #-----------------------------------------
   # First pass -- check for duplicate payees
   #-----------------------------------------

   # duplicatePayeeMap -- if a payee name + client account no appears in
   # here, then the payee appears more than once in the template
   my %duplicatePayeeMap;

   my %seenPayee;
   foreach my $row (@$rows) {
      my $_seenPayee = $row->{'artist-payee-name'};
      $_seenPayee =~ s/\./_/g;

      my $seenKey = join("\t", clean_name( $_seenPayee ),
         _nullCheck( $row->{'client-account-no'} ) );
      if ( exists $seenPayee{$seenKey} ) {
         $duplicatePayeeMap{$seenKey}  = 1;
      }
      $seenPayee{$seenKey} = 1;
   }

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

      #-----------------------------------
      # Get all of the template variables.
      #-----------------------------------
      my $rowid           = $row->{'rowid'};
      my $payeeName       = $row->{'artist-payee-name'} || $row->{'payee'};
      my $clientAccountNo = $row->{"client-account-no"};
      my $status          = $row->{'status'};
      my $address1        = $row->{'address-1'};
      my $address2        = $row->{'address-2'};
      my $address3        = $row->{'address-3'};
      my $address4        = $row->{'address-4'};
      my $address5        = $row->{'address-5'};
      my $city            = $row->{'city'};
      my $stateProvince   = $row->{'state-province'};
      my $zipPostal       = $row->{'zip-postal'};
      my $country         = $row->{'country'};
      my $email           = $row->{'e-mail'} || $row->{'email'};
      my $phone           = $row->{'phone'};
      my $fax             = $row->{'fax'};
      my $taxid           = $row->{'tax-id'};
      my $comments        = $row->{'comments'};
      my $distribution    = $row->{'statement-distribution'};
      my $payorName       = $row->{'payor-name'};
      my $minPayment      = $row->{'minimum-payment'};
      my $balance         = $row->{'opening-balance'};

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

      #-----------------
      # Skip empty lines
      #-----------------
      if( ( !defined $payeeName || '' eq $payeeName ) &&
          ( !defined $payorName || '' eq $payorName ) ) {
         report("## Row $rowid - no payee or payor specifed .. skipping line !!!");
         #die("STOP!"); # XXX
         next;
      }

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

      # Clean-up fields as needed
      #
      $payeeName =~ s/^\s*//g if( defined $payeeName );
      $payeeName =~ s/\s*$//g if( defined $payeeName );
      $city =~ s/^\s*//g if( defined $city );
      $city =~ s/\s*$//g if( defined $city );
      $stateProvince =~ s/^\s*//g if( defined $stateProvince );
      $stateProvince =~ s/\s*$//g if( defined $stateProvince );
      $zipPostal =~ s/^\s*//g if( defined $zipPostal );
      $zipPostal =~ s/\s*$//g if( defined $zipPostal );


      my %args = ();
      if ( defined $payeeName ) { $args{name} = $payeeName; }
      if ( defined $clientAccountNo ) { $args{client_account_id} = $clientAccountNo; }

#      $args{status} = $gStatusMap{ uc substr($status, 0, 1) } if ( defined $status );

      $status =~ s/\s*//g if( $status );
      my $_status;

      # NOTE: the payee will default to active if no status is defined
      #
      if( defined $status && '' ne $status )
      {
         if( $status =~ /^i/i )
         {
            $_status = 0; # inactive
         }
         elsif( $status =~ /^a/i )
         {
            $_status = 1; # active
         }
         elsif( $status =~ /^h/i || $status =~ /^on[- ]?hold$/i )
         {
            $_status = 2; # on-hold
         }
         else
         {
            _appendString( $errorCode, "Unknown status '$status'");
            ++$excount{unknown_status};
         }
         $args{status} = $_status if( defined $_status );
      }

      # The template currently has five address lines, however internally we
      # only support three.  To accomodate this, the first two lines are stored
      # in their own location, and lines three through five are stored in the
      # third location. -ES 10/24/12
      #
      if ( defined $address1 ) { $args{street_address}   = $address1; }
      if ( defined $address2 ) { $args{street_address_2} = $address2; }

      my $streetAddress;
      if ( defined $address3 ) { $streetAddress = $address3; }
      if ( defined $address4 ) { $streetAddress = (defined $streetAddress) ? "$streetAddress\n$address4" : "$address4"; }
      if ( defined $address5 ) { $streetAddress = (defined $streetAddress) ? "$streetAddress\n$address5" : "$address5"; }

      if ( defined $streetAddress ) { $args{street_address_3} = $streetAddress; }


      if ( defined $city ) { $args{city} = $city; }
      if ( defined $stateProvince ) { $args{state_province} = $stateProvince; }
      if ( defined $zipPostal ) { $args{postal_code} = $zipPostal; }

      if ( defined $country ) {
         $country =~ s/^\s*//g;
         $country =~ s/\s*$//g;
         if ( $country =~ m/^[a-z][a-z]$/i ) {
            $args{country_code} = $country;
         } elsif( '' eq $country ) {
            $args{country_code} = "US"; # DEFAULT
         } else {
            $country = lc $country;
            # common mis-spellings
            $country =~ s/brasil/brazil/g;
            $country =~ s/austalia/australia/g;
            my $countryCode = $Common::Consts::COUNTRY_CODE{ $country };

            if ( !$countryCode ) {
               _appendString( $errorCode, "Unknown country");
               ++$excount{unknown_country};
            } else {
               $args{country_code} = $Common::Consts::COUNTRY_CODE{ $country };
            }
         }
      }
      if ( defined $email ) { $args{email} = $email; }
      if ( defined $phone ) { $args{phone_number} = $phone; }
      if ( defined $fax ) { $args{fax_number} = $fax; }
      if ( defined $taxid ) { $args{tax_id} = $taxid; }
      #if ( defined $comments ) { $args{comments} = $comments; }
      $args{comments} = (defined $comments) ? $comments : " ";

      #------------------------------------------------------
      # Check the statement distribution status, if any
      #------------------------------------------------------
      if ( defined $distribution ) {
         if ( '' eq $distribution || $distribution =~ /^m(?:anual)?/i ) {
            # nothing to do here
         } elsif( $distribution =~ /^o(?:nline)?/i ) {
            $args{distribution} = 1;  # enable distribution
            if( !defined $email || '' eq $email ) {
               #_appendString( $errorCode, "Email address required for online distribution");
               _appendString( $errorCode, "A valid email address is required");
               ++$excount{email_required_for_distribution};
            }

            if( !defined $labelContact || '' eq $labelContact ) {
               _appendString( $errorCode, "A Payee Portal Label Contact must exist");
               ++$excount{label_contact_required_for_distribution};
            }

         } else {
            _appendString( $errorCode, "Invalid distribution method");
            ++$excount{invalid_distribution_method};
         }

      }

      #------------------------------------------------------
      # Does the payee appear more than once in the template?
      #------------------------------------------------------
#      my $cleanName = clean_name_catalog($payeeName);

      my $_seenPayee = $payeeName;
      $_seenPayee =~ s/\./_/g;

      my $seenKey = join("\t", clean_name($_seenPayee),
         _nullCheck( $clientAccountNo ) );

      if ( $duplicatePayeeMap{$seenKey} ) {
         _appendString( $errorCode, "Duplicate payee");
         ++$excount{duplicate_payee};
      }

      #---------------------------------------------------------------------------
      # If the payor was specified, check it to make sure it's valid
      #---------------------------------------------------------------------------
      my $payorID;
      if( $payorName )
      {
         $payorName =~ s/\s*$//;
         $payorName =~ s/^\s*//;
         $payorName =~ s/(\r|\n)//g;
         $row->{'payor-name'} = $payorName; # ensure that the corrected name is output
         if ( not defined $payorName ) {
            $payorID = $gDefaultPayorID;
         } else {
            $payorID = $gPayorMap{lc $payorName};
         }
         if ( not defined $payorID ) {
#            # This should be caught during template validation
#            die("  Shouldn't get here -- payorID undefined for payor '$payorName'");
            _appendString( $errorCode, "Invalid payor");
            ++$excount{invalid_payor};
            report("WARNING: payor '$payorName' is not valid");
         }
         else
         {
            report("INFO: found payor '$payorName', id = $payorID ");
         }
      }


#      #------------------------------------------------------------------
#      # If there are any errors at this point, stop processing this payee
#      #------------------------------------------------------------------
#      if ( $errorCode ) {   # XXX - move this further down
#         $row->{'error-code'} = $errorCode;
#         next;
#      }

      my $payeeID;

      # If a client account number wasn't specified, we need to check
      # if the name appears more than once in the client's DB
      #
      if( !defined $clientAccountNo or '' eq $clientAccountNo )
      {
         my $sql = "SELECT COUNT(*) FROM artist_payee WHERE name = " .  $dbo->DBQuote($payeeName);
         my $sth = $dbo->DoCmd($sql);
         my($nMatches) = $sth->fetchrow_array();
         if( $nMatches > 1 )
         {
            report("Multiple payees found with name '$payeeName'");
            _appendString( $errorCode, "Non-unique payee name in DB");
            ++$excount{non_unique_payee_name_db};
         }
      }

      # To determine if a payee exists, just search on its name
      my %searchArgs = (
         name => $payeeName,
      );

# Make payee searches by name only. -ES 2/24/16
#      $searchArgs{client_account_id} = $clientAccountNo
#         if ( defined $clientAccountNo );
      # Re-enabling client account search (if specified in template) -ES 5/17/16
      $searchArgs{client_account_id} = $clientAccountNo
         if ( defined $clientAccountNo );


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


      #my $artistPayeeObj = RPS::DB::Item::ArtistPayee->Lookup(%args);
      my $artistPayeeObj = RPS::DB::Item::ArtistPayee->Lookup(%searchArgs);
      if ( not defined $artistPayeeObj ) {
         if ( $execMode ) {
            $artistPayeeObj = RPS::DB::Item::ArtistPayee->Create(%args);
            $artistPayeeObj->save();
            $payeeID = $artistPayeeObj->artist_payee_id;
            report("Created artist_payee $payeeID : ".Dumper(\%args));
            ++$count{new_payees};
            $row->{'rs-payee-id'} = $payeeID;
         } else {
            report("Non-exec mode, skipped artist_payee: ".Dumper(\%args));
            $row->{'rs-payee-id'} = 0;
         }

         if( defined $distribution && $distribution =~ /^o(?:nline)?/i ) {
            my $_payeeID = $row->{'rs-payee-id'};
            if( $_payeeID > 0 ) {
               report("Queuing payeeID $_payeeID for invite email");
               push @inviteList, $_payeeID;
            } else {
               report("PayeeID not set, skipping queuing of invite email");
            }
         }

      } else {
         $payeeID = $artistPayeeObj->artist_payee_id;
         report("EXISTS: artist_payee '$payeeName' (id=$payeeID)");
         _appendString( $errorCode, "Payee exists");
         ++$excount{payee_exists};
      }

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

      if ( not defined $payeeID ) {
         next;
      }

#      #---------------------------------------------------------------------------
#      # Optional: Setup artist_payee_account, finance_account, pending_transaction
#      #---------------------------------------------------------------------------
#      my $payorID;
#      $payorName =~ s/\s*$//;
#      $payorName =~ s/^\s*//;
#      if ( not defined $payorName ) {
#         $payorID = $gDefaultPayorID;
#      } else {
#         $payorID = $gPayorMap{lc $payorName};
#      }
#      if ( not defined $payorID ) {
#         # This should be caught during template validation
#         die("  Shouldn't get here -- payorID undefined for payor '$payorName'");
#      }


      #----------------------------------------------------------------------
      # If we have a min payment or opening balance, create a finance account
      # if it doesn't already exist.
      #----------------------------------------------------------------------
      my $financeAccountID;

      if( (defined $minPayment) ||
          ($minPayment && '' ne $minPayment) ||
          ($balance && '' ne $balance) ) {

         #--------------------------
         # Setup the finance account
         #--------------------------
         my %faArgs = (
            description => "account for artist payee $payeeID payor $payorID",
            type_code => RPS::DB::Item::FinanceAccount::kAccountTypeHoldover,
            currency_code => $denomination, # 5/4/10
         );
         my $faObj = RPS::DB::Item::FinanceAccount->Lookup(%faArgs);
         if ( not defined $faObj ) {
            if ( $execMode ) {
               $faObj = RPS::DB::Item::FinanceAccount->Create(%faArgs);
               $faObj->save();
               $financeAccountID = $faObj->finance_account_id;
               report("   Created finance_account $financeAccountID : ".Dumper(\%faArgs));
            } else {
               report("   Non-exec mode, skipped finance_account for payee $payeeID, payor $payorID");
            }
         } else {
            $financeAccountID = $faObj->finance_account_id;
            report("EXISTS: finance_account $financeAccountID");
         }

         if ( not defined $financeAccountID ) {
            report("   No financeAccountID, skipping ArtistPayeeAccount");
            next;
         }

      }

      #---------------------------------------------------
      # The balance may or may not have 'extra' characters
      #---------------------------------------------------
      if ( $balance ) {
         $balance =~ s/\$//;
         $balance =~ s/,//;
         if ( $balance =~ m/\(.*\)/ ) {
            $balance =~ s/\(//;
            $balance =~ s/\)//;
            $balance *= -1;
         }
      }

      #----------------------------------------------------
      # If there's a balance, setup the pending transaction
      #----------------------------------------------------
      if ( $balance and '' ne $balance) {

         #------------------------------
         # Setup the pending transaction
         #------------------------------
         my %ptArgs = (
            finance_account_id => $financeAccountID,
            amount => $balance,
            memo => "opening balance",
            type_code => RPS::DB::Item::PendingTransaction::kTypeAdjustment,
            currency_code => $denomination, # 5/4/10
         );
         my $ptID;
         my $ptObj = RPS::DB::Item::PendingTransaction->Lookup(%ptArgs);
         if ( not defined $ptObj ) {
            if ( $execMode ) {
               $ptObj = RPS::DB::Item::PendingTransaction->Create(%ptArgs);
               $ptObj->save();
               $ptID = $ptObj->pending_transaction_id;
               report("   Created pending_transaction $ptID : ".Dumper(\%ptArgs));
            } else {
               report("   Non-exec mode, skipping pending_transaction");
            }
         } else {
            $ptID = $ptObj->pending_transaction_id;
            report("EXISTS: pending_transaction $ptID");
         }

      }

      #-------------------------------
      # Setup the artist payee account
      #-------------------------------
      if ( $financeAccountID && (
             (defined $minPayment) ||
             ($minPayment && '' ne $minPayment) ||
             ($balance && '' ne $balance)) )
      {
         my %args = (
            artist_payee_id => $payeeID,
            payor_id => $payorID,
            finance_account_id => $financeAccountID,
         );
         if ( defined $minPayment ) {
            $minPayment =~ s/\$//;
            $minPayment =~ s/,//;
            $args{min_payment} = $minPayment;
         }
         my $apObj = RPS::DB::Item::ArtistPayeeAccount->Lookup(%args);
         if ( not defined $apObj ) {
            if ( $execMode ) {
               $apObj = RPS::DB::Item::ArtistPayeeAccount->Create(%args);
               $apObj->save();
               report("   Created artist_payee_account : ".Dumper(\%args));
            } else {
               report("   Non-exec mode, skipping artist_payee_account for payee $payeeID, finance_account $financeAccountID");
            }
         } else {
            report("EXISTS: artist_payee_account exists for payee $payeeID, finance_account $financeAccountID");
         }
      }


      #------------------------------------------------------------
      # If we have any errors at this point then set the errorCode.
      #------------------------------------------------------------
      $row->{'error-code'} = $errorCode;
   }#row loop


   if( (scalar @inviteList) > 0 )
   {
      report("#### Sending out portal invites ...");
      foreach my $payeeID (@inviteList) {
          report("   Sending invite to payee $payeeID ...");
          RPS::Portal::SendInvite->_sendInvite( $payeeID, $labelName, $labelContact );
      }
   }


   #------------------------------------------------------------
   # 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 #####");
   print STDERR "##### S U M M A R Y #####\n";
   my $totalExceptions = 0;
   my $totalRows = (scalar @$rows);

   foreach my $c (keys %excount) {
      my $v = $excount{$c};
      printf("%30s %6d\n", $c, $v);
      printf(STDERR "%30s %6d\n", $c, $v);
      $totalExceptions += $v;
   }
   printf("%30s %s\n", " ", "-------" );
   printf(STDERR "%30s %s\n", " ", "-------" );
   printf("%30s %6d\n", "Total Exceptions", $totalExceptions );
   printf(STDERR "%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);
      printf(STDERR "%30s %d\n", $c, $v);
   }

   print STDERR ">>>\n";
   if (!$execMode)
   {
       print STDERR ">>> Test complete.  Run 'make import' to commit changes\n";
   }
   else
   {
       print STDERR ">>> Import complete.  Attach exceptions report to FogBugz case.\n";
   }
   print STDERR ">>>\n\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 $payeeID = $row->{'rs-payee-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 $msg = (defined $payeeID) ? $payeeID : "NOT_FOUND";
      my $importStatus = ($errorCode) ? "__FAIL__" : "payee($msg)";

      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 _nullCheck {
   my($a) = @_;
   return('') if (!$a);
   return $a;
}

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

1;