package Support::Implementation::PublisherPayeeTemplate;
# 5/4/10 - Added logic to set currency code for client
# 1/11/11 - Corrected error logic to prevent publisher with
#    exceptiopns from being created.
# 6/16/11 - Changed gPayorMap to use lowercase keys
# 3/7/12 - Added text field cleanup
# 10/24/12 - Updated to reflect current publisher schema
#  (specifically, the street_address_2 and street_address_3 fields
#   that supplement the street_address field).
# 6/7/13 - Updated to accept human-readable type specification.
# 6/10/15 - Relaxed duplicate checking; aside from the publisher name
#  and client accountID, we'll also consider the agent/admin information.
# 3/28/16 - Force currency code for US publisher payees
# 9/12/16 - Allow "agency" or "agent" in "type" column instead of "3".
# 2/14/17 - Don't 'die' if agent not found and we're in non-exec mode
# 5/16/17 - Convert NBSP to regular spaces; improve missing agent/admin
#  detection when run in non-exec mode.
# 2/4/20 - Added affiliate support (RSD-5048). Cleaned up status validation
# 7/1/20 - Add exception if agent not found
#
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 trimspaces escape_mysql_regexp );

use RPS::DB::Item::Publisher;
use RPS::DB::Item::PublisherAccount;
use RPS::DB::Item::FinanceAccount;
use RPS::DB::Item::PendingTransaction;
use RPS::DB::Item::Affiliate;

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

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

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

use constant kPublisherTypeStandard => 1;
use constant kPublisherTypeAdmin    => 2;
use constant kPublisherTypeAgent    => 3;

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

#--------------------------------------------------------------
# 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,
   "I" => 0,
   "inactive" => 0,
   "a" => 1,
   "A" => 1,
   "active" => 1,
   "h" => 2,
   "H" => 2,
   "hold" => 2,
);

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

#--------------------------
# Keep track of blank lines
#--------------------------
my %gBlankPublisherMap = ();

#-------------------------------------
# Counters for entities that we create
#-------------------------------------
my $gNewPublisherCount = 0;
my $gExistingPublisherCount = 0;
my $gNewPublisherAccount = 0;
my $gNewPendingTransaction = 0;
my $gNewFinanceAccount = 0;

#----------------------------------
# excount keeps track of exceptions
#----------------------------------
my %gExCount;

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

#my $gHfaName;
#my $gHfaID;

#----------------------------------------
# gAgentMap maps agent names to their IDs
#----------------------------------------
my %gAgentMap = ();

my $gDefaultPayorID;
my %gPayorMap = (); # maps names to ID

my %gMissingAgentMap;
my %gMissingAdminMap;
my %gPendingAgentMap;
my %gPendingAdminMap;

my $clientID;
my $execMode;

my $dbo;
my $cdbo;
my $dbh;

my $gDenomination; # Set to the client's currency code

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


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

   report("PublisherTemplate::_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();
   $dbh = $dbo->DBH;
   $cdbo = Common::RSApp::GetCommonDB();

   my $fileName = $self->name;

   if( $self->isExcel2003( $fileName ) ) {
      print("PublisherPayeeTemplate::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("PublisherPayeeTemplate::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("PublisherPayeeTemplate::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
   #----------------------------------
   %gExCount = (
      publisher_exists => 0,
      unknown_country  => 0, # XXX
      admin_not_found  => 0,
      invalid_type     => 0,
      missing_type     => 0,
      invalid_status   => 0,
      unexpected_admin => 0,
   );

   #--------------------------------------
   # count keeps track of entities created
   #--------------------------------------
   %gCount = (
      publisher           => 0,
      publisher_account   => 0,
      finance_account     => 0,
      pending_transaction => 0,
   );

   #------------------------
   # Setup payor information
   #------------------------
   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");
   }

   #----------------------------------------------------
   # Pre-load the gAgentMap with any pre-existing agents
   #----------------------------------------------------
   $sql = "SELECT publisher_id, publisher_name "
      . "FROM publisher WHERE is_agency=1";
   $sth = $dbo->DoCmd($sql);
   while( my($id,$name) = $sth->fetchrow_array() ) {
      $gAgentMap{lc $name} = $id;
   }

   #---------------------
   # Check if HFA exists.
   #---------------------
   my $hfaObj = RPS::DB::Item::Publisher->Lookup(
      is_agency => 1,
   );
   if( $hfaObj ) {
      #$gHfaID = $hfaObj->publisher_id;
      #$gHfaName = $hfaObj->publisher_name;
   } else {
      report("### WARNING: HFA not found");

      #---------------------------------------------------------------
      # Not having HFA in the system may not be a problem, _UNLESS_
      # a) one or more publishers in the template are using HFA, _and_
      # b) the template doesn't declare HFA (type=3)
      #---------------------------------------------------------------
      my $hfaInTemplate;
      my $hfaDependency;
      foreach my $row (@$rows) {
         my $agent = $row->{'agent'};
         my $type = $row->{'type'};
         $hfaInTemplate = 1 if ( $type && ($type eq "3" || $type =~ /^(agent|agency)$/i) );
         $hfaDependency = 1 if ( $agent );
      }
      if ( $hfaDependency && !$hfaInTemplate ) {
         my $msg = "\n".
            "#########################################################################\n".
            "# ERROR: HFA dependencies detected in template, but HFA does not exist. #\n".
            "# Please create HFA entry or add it to the template, and try again.     #\n".
            "#########################################################################\n".
            "\n";
         die($msg);
      }
   }

   #---------------------------------
   # 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 );
   $gDenomination = $currencyFormat->currencyCode();

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

   report("###############################################");
   report("####                                       ####");
   report("####   createPublishers - creating HFA     ####");
   report("####                                       ####");
   report("###############################################");
   _createPublishers(kPublisherTypeAgent, $data->{rows});

   report("###############################################");
   report("####                                       ####");
   report("####   createPublishers - creating admins  ####");
   report("####                                       ####");
   report("###############################################");
   _createPublishers(kPublisherTypeAdmin, $data->{rows});

   report("###################################################");
   report("####                                           ####");
   report("####   createPublishers - creating non-admins  ####");
   report("####                                           ####");
   report("###################################################");
   _createPublishers(kPublisherTypeStandard, $data->{rows});

   report("\nCreated $gNewPublisherCount publisher(s)");
   report("Created $gNewPublisherAccount publisher_account(s)");
   report("Created $gNewFinanceAccount finance_account(s)");
   report("Created $gNewPendingTransaction pending_transaction(s)");
   report("There were ". (keys %gBlankPublisherMap)
      . " blank line(s)");
   report("$gExistingPublisherCount publisher(s) already exist");


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

   if( keys %gMissingAdminMap )
   {
      foreach my $a (keys %gMissingAdminMap)
      {
          print "Missing admin '$a'\n";
      }
   }

   if( keys %gMissingAgentMap )
   {
      foreach my $a (keys %gMissingAgentMap)
      {
          print "Missing agent '$a'\n";
      }
   }
   my $totalRows = (scalar @$rows);

   my $totalExceptions = 0;
   foreach my $c (keys %gExCount) {
      my $v = $gExCount{$c};
      printf("%30s %6d\n", $c, $v);
      $totalExceptions += $v;
   }
   printf("%30s %s\n", " ", "-------" );
   printf("%30s %6d\n", "Total Exceptions", $totalExceptions );
   printf("%30s %6d\n", "Total Rows", $totalRows );
   report(" ");
   
   foreach my $c (keys %gCount) {
      my $v = $gCount{$c};
      printf("%30s %d\n", $c, $v);
   }

}#_processData

#----------------------------------------------------------------------------
# _createPublishers: This subroutine will process the has of publishers
# and depending on the 'mode', will do one of the following:
# mode = 1  Create standard publishers.  It does this by examining the hash
#           for publishers with type=1
# mode = 2  Create admin publishers.  It does this by examining the hash
#           for publishers with type=2
# mode = 3  Create agency (HFA).  It does this by examining the hash for
#           a publisher with type=3.
#
# To ensure best results, you should call this routine three times; the
# first time with mode=3 (create HFA), followed by mode=2 (create admin(s)),
# then followed by mode=1 (create standard publishers).
#----------------------------------------------------------------------------
sub _createPublishers {
   my ($mode,$publishers) = @_;
   if ( $mode != kPublisherTypeStandard and
        $mode != kPublisherTypeAdmin and
        $mode != kPublisherTypeAgent) {
      die("createPublishers: illegal mode $mode");
   }

   report("DEBUG: _createPublishers: " . scalar @$publishers);

   #------------------------------------------------------
   # We need to check if the template has duplicate lines.
   #------------------------------------------------------
   my %publisherRowMap = ();
   my %seenPublisherMap = ();
   my %duplicatePublisherMap = (); # if a rowid is in here, then it's a dupe
   foreach my $publisher (@$publishers) {
      my $rowid           = $publisher->{'rowid'};
      my $publisherName   = trimspaces $publisher->{'publisher-name'};
      #my $status          = lc $publisher->{'status'}; XXX not part of dupe check

      my $clientAccountNo = $publisher->{'client-account-no'};

      my $agent           = trimspaces $publisher->{'agent'};

      my $country         = (exists $publisher->{'country'} && defined $publisher->{country}) ? lc $publisher->{'country'} : undef;

      my $publisherType   = $publisher->{'type'};

      my $payorName       = trimspaces $publisher->{'payor-name'};
      my $minPayment      = $publisher->{'minimum-payment'};
      my $balance         = $publisher->{'opening-balance'};

      my $address1        = $publisher->{'address-1'},
      my $adminName       = trimspaces $publisher->{admin};

      #my $key = join( "\t", $publisherName, $publisherType, _nullCheck($agent), _nullCheck($adminName), _nullCheck($payorName),
      #   #$minPayment, $balance
      #);

      my $key = join( "\t", $publisherName, $publisherType, _nullCheck($clientAccountNo), _nullCheck($agent), _nullCheck($adminName), _nullCheck($payorName),
         #$minPayment, $balance
      );
      if ( exists $seenPublisherMap{$key} ) {

         my $orig = $seenPublisherMap{$key};
         $publisherRowMap{$key} = (exists $publisherRowMap{$key}) ?
            $publisherRowMap{$key} . ", $rowid" : "$orig, $rowid";

         $duplicatePublisherMap{$orig} = 1;
         $duplicatePublisherMap{$rowid} = 1;
         report("ROW($rowid) is a duplicate of row ". $seenPublisherMap{$key} . " key($key)");
      } else {
         $seenPublisherMap{$key} = $rowid;
      }
   }

   foreach my $key (keys %publisherRowMap) {
      my $rowids = $publisherRowMap{$key};
      report("DEBUG: key($key) found in row(s): $rowids");
   }

   #--------------------------------
   # Now process the template lines.
   #--------------------------------
   foreach my $publisher (@$publishers) {

      my $errmsg;

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

      my $rowid           = $publisher->{'rowid'};
      my $publisherName   = trimspaces $publisher->{'publisher-name'};
      $publisherName =~ s/\x{a0}/ /g;  # Chang NBSP to regular space
      $publisher->{'publisher-name'} = $publisherName;

      my $status          = ( exists $publisher->{status} && defined $publisher->{status} ) ? lc $publisher->{'status'} : undef;

      my $clientAccountNo = $publisher->{'client-account-no'};
      $clientAccountNo =~ s/\s*$// if ( $clientAccountNo );

      my $agent           = trimspaces $publisher->{'agent'};
      if( $agent ) {
         $agent =~ s/\x{a0}/ /g;  # Chang NBSP to regular space
         $publisher->{'agent'} = $agent;
      }

      my $country         = (exists $publisher->{'country'} && defined $publisher->{country}) ? $publisher->{'country'} : undef;
      $country =~ s/\s+$// if ( $country );
      $country = 'US' if ( !$country );

      my $publisherType   = $publisher->{'type'};

      my $payorName       = trimspaces $publisher->{'payor-name'};
      my $minPayment      = $publisher->{'minimum-payment'};
      my $balance         = $publisher->{'opening-balance'};

      my $address1        = trimspaces $publisher->{'address-1'};
      $address1 =~ s/\s+$//g if ( $address1 );

      my $address2        = $publisher->{'address-2'};
      $address2 =~ s/\s+$//g if ( $address2 );

      #$address2 =~ s/\0xa0*$//;
      my $address3        = trimspaces $publisher->{'address-3'};
      $address3 =~ s/\s+$//g if ( $address3 );

      my $address4        = trimspaces $publisher->{'address-4'};
      $address4 =~ s/\s+$//g if ( $address4 );

      my $address5        = trimspaces $publisher->{'address-5'};
      $address5 =~ s/\s+$//g if ( $address5 );

      my $affiliateName   = (exists $publisher->{affiliation} && defined $publisher->{affiliation}) ? $publisher->{affiliation} : undef;

die("row($rowid): address-1 has return!\n") if ( $address1 && $address1 =~ m/\n/ );
die("row($rowid): address-2 has return!\n") if ( $address2 && $address2 =~ m/\n/ );
die("row($rowid): address-3 has return!\n") if ( $address3 && $address3 =~ m/\n/ );
die("row($rowid): address-4 has return!\n") if ( $address4 && $address4 =~ m/\n/ );
die("row($rowid): address-5 has return!\n") if ( $address5 && $address5 =~ m/\n/ );

      $minPayment = 0 if ( !$minPayment );  # Default to zero if unspecified

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


      if ( !$publisherName or '' eq $publisherName ) {
         $gBlankPublisherMap{$rowid} = $rowid;
         next;
      }

      #---------------------------------------------------------------------
      # We're _supposed_ to only allow 1, 2 or 3 as the publisher type.  But
      # some folks like to spell out the intended type.  Convert the spelled
      # out version to its numeric equivalent, or throw an exception.
      #---------------------------------------------------------------------
      my $pType = $publisherType;
      if ( $pType && $pType !~ /^\d+$/ ) {
         $pType = _getPublisherType($publisherType);
         $publisher->{'type'} = $pType if ( $pType );
      }

      $publisherType = $pType;
      
      my %args;

      #---------------------------------------------------------------------
      # If we're creating admins and the current publisher type isn't admin
      # then skip this publisher.  If no publisher type is specified, then
      # this will result in an invalid_type exception.
      #---------------------------------------------------------------------
      if ( $publisherType ) {
         if ( $mode == kPublisherTypeAdmin and
              $publisherType != kPublisherTypeAdmin ) {
            next;
         }

         #---------------------------------------------------------------------
         # If we're creating standard publishers and the current publisher type
         # is admin (type=2), then skip this publisher.
         #---------------------------------------------------------------------
         if ( $mode == kPublisherTypeStandard and
              $publisherType != kPublisherTypeStandard ) {
            next;
         }

         if ( $mode == kPublisherTypeAgent and
              $publisherType != kPublisherTypeAgent ) {
            next;
         }
      }


      # Generate an output line for debugging...

      my $zzMinPayment = (!$minPayment) ? "NULL" : $minPayment;
      my $zzAgent = (!$agent) ? "NULL" : $agent;
      my $zzPType = (!$publisherType) ? "NULL" : $publisherType;
      #report("### Processing line $rowid, puname($publisherName), "
      #   . "mode($mode), type($zzPType), "
      #   . "agent($zzAgent) "
      #   . "hfaName(" . (($gHfaName)? $gHfaName : "UNDEF")  .") minPayment($zzMinPayment)");
      report("  ### Processing line $rowid, puname($publisherName), "
         . "mode($mode), type($zzPType), "
         . "agent($zzAgent) "
         . "minPayment($zzMinPayment)");

      if ( $duplicatePublisherMap{$rowid} ) {
         report("DEBUG: duplicate_publisher");
         ++$gExCount{duplicate_publisher};
         _appendString( $errorCode, "Duplicate publisher");
      }

      #-------------------------------------------
      # set default status if one wasn't specified
      #-------------------------------------------

      # default is active, unless otherwise directed
      $publisher->{status} = RPS::DB::Item::Publisher::kStatusActive;

      if ( defined $status && $status !~ /^(1|2|3)$/ ) {
         if ( $status =~ /^a(?:ctive)?/i ) {
            $publisher->{status} = RPS::DB::Item::Publisher::kStatusActive;
         }elsif ( $status =~ /^i(?:nactive)?/i ) {
            $publisher->{status} = RPS::DB::Item::Publisher::kStatusInactive;
         } elsif ( $status =~ /^h(?:old)?/i ) {
            $publisher->{status} = RPS::DB::Item::Publisher::kStatusOnHold;
         } else {
            ++$gExCount{invalid_status};
            _appendString( $errorCode, "Invalid status");
         }
      }

      # Validate payor
      my $payorID;
      if ( not defined $payorName or "" eq $payorName ) {
         $payorID = $gDefaultPayorID;
      } else {
         $payorID = $gPayorMap{lc $payorName};
      }
      if ( not defined $payorID ) {
         ++$gExCount{invalid_payor};
         _appendString( $errorCode, "Invalid Payor");
      }

      #------------------------
      # validate publisher type
      #------------------------
      if ( $publisherType ) {
         if ( kPublisherTypeStandard != $publisherType and
              kPublisherTypeAdmin != $publisherType and
              kPublisherTypeAgent != $publisherType ) {

            report("DEBUG: invalid_type($publisherType)");
            ++$gExCount{invalid_type};
            _appendString( $errorCode, "Invalid type");
         }
      } else {
         report("DEBUG: missing_type");
         ++$gExCount{missing_type};
         _appendString( $errorCode, "Missing type");
      }

      #-----------------------
      # Validate country code.
      #-----------------------
      my $countryCode;
      if ( $country && '' ne $country ) {
         if ( $country =~ m/^[a-z][a-z]$/i ) {
            $countryCode = $country;
         } else {
            $country = lc $country;
            # common mis-spellings (TODO: should externalize this...)
            $country =~ s/brasil/brazil/g;
            $country =~ s/austalia/australia/g;
            my $zzCountryCode = $Common::Consts::COUNTRY_CODE{ $country };
            report("Mapped country [$country] --> [$zzCountryCode]");

            if ( !$zzCountryCode ) {
               _appendString( $errorCode, "Unknown country");
               ++$gExCount{unknown_country};
            } else {
               $countryCode = uc $zzCountryCode;
            }
         }
      }

#      #---------------------------------------------------------
#      # If this row had any errors, don't process this line.
#      # Note: some additional exception detection is done below;
#      # if we find any further exceptions then those have to be
#      # manually stored in the 'error-code' field.
#      #---------------------------------------------------------
#      if ( $errorCode ) {
#         $publisher->{'error-code'} = $errorCode;
#         next;
#      }


      #my $streetAddress;
      #if ( defined $address1 ) { $streetAddress = $address1; }
      #if ( defined $address2 ) { $streetAddress = (defined $streetAddress) ? "$streetAddress\n$address2" : "$address2"; }
      #if ( defined $address3 ) { $streetAddress = (defined $streetAddress) ? "$streetAddress\n$address3" : "$address3"; }
      #if ( defined $address4 ) { $streetAddress = (defined $streetAddress) ? "$streetAddress\n$address4" : "$address4"; }
      #if ( defined $address5 ) { $streetAddress = (defined $streetAddress) ? "$streetAddress\n$address5" : "$address5"; }
      #$streetAddress = '' if (!$streetAddress);

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

      # Replace any non-breaking spaces (NBSP) with a normal space
      if ( $publisherName =~ m/\xA0/ ) {
         $publisherName =~ s/\xA0/ /g;  # non-breaking space
      }

      # The next line removes blank lines from the address
      #$address =~ s/^\n//g;

      %args = (
         publisher_name => $publisherName,
         #street_address => $streetAddress,
         city           => $publisher->{'city'},
         state_province => $publisher->{'state-province'},
         postal_code    => $publisher->{'zip-postal'},
         email          => $publisher->{'e-mail'},
         comments       => $publisher->{'comments'},
         tax_id         => $publisher->{'tax-id'},
         phone_number   => $publisher->{'phone'},
         fax_number     => $publisher->{'fax'},
         #status         => $gStatusMap{ $status },
         status         => $publisher->{status},
      );

      $args{street_address}   = ( defined $address1 )      ? $address1 : '';
      $args{street_address_2} = ( defined $address2 )      ? $address2 : '';
      $args{street_address_3} = ( defined $streetAddress ) ? $streetAddress : '';


      if ( $countryCode ) {
         $args{country_code} = $countryCode;
         $publisher->{'country'} = $countryCode;
      }

      if ( defined $publisher->{'client-account-no'} ) {
         $args{'client_account_id'} = $publisher->{'client-account-no'};
      }

      if ( not defined $publisher->{'comments'} ) {
         $args{'comments'} = '';
      }
      if ( not defined $publisher->{'e-mail'} ) {
         $args{'email'} = '';
      }
      if ( not defined $publisher->{'tax-id'} ) {
         $args{'tax_id'} = '';
      }

      if ( not defined $publisher->{'phone'} ) {
         $args{'phone_number'} = '';
      }
      if ( not defined $publisher->{'fax'} ) {
         $args{'fax_number'} = '';
      }

      if ( not defined $publisher->{'city'} ) {
         $args{'city'} = '';
      }
      if ( not defined $publisher->{'zip-postal'} ) {
         $args{'postal_code'} = '';
      }
      if ( not defined $publisher->{'state-province'} ) {
         $args{'state_province'} = '';
      }

      if ( $affiliateName ) { # XXX RSD-4048
         my $affiliate = RPS::DB::Item::Affiliate->Lookup( name => $affiliateName );
         if ( !$affiliate ) {
            die( "ERROR: affiliate '$affiliateName' not found - check RSCOMMON.affiliate !!!" ); # XXX
            #if ( $execMode ) {
            #   $affiliate = RPS::DB::Item::Affiliate->Create( name => $affiliateName );
            #   $affiliate->save;
            #   my $id = $affiliate->affiliate_id;
            #   $args{affiliate_id} = $id;
            #   report("Created affiliate $id  '$affiliateName'");
            #} else {
            #   report("Non-exec: Warning: affiliate '$affiliateName' not found");
            #}
         } else {
            print "D: found affiliate '$affiliateName' id=". $affiliate->affiliate_id . "\n"; # XXX
            $args{affiliate_id} = $affiliate->affiliate_id;
         }
      }

      #---------------------------------------------------------------
      # The following query is used to determine if a publisher exists
      # or not.  We'll augment the query with agent and/or admin info
      # if those are defined on the template line.
      #---------------------------------------------------------------
      my $searchSql = "SELECT publisher_id FROM publisher "
         . "WHERE publisher_name = ? ";
      #$searchSql .= "AND client_account_id='$clientAccountNo'" if ( $clientAccountNo );
      $searchSql .= "AND client_account_id = ? " if ( $clientAccountNo );

      # Validate the agent
      if ( $agent && ("" ne $agent) ) {

         my $zzAgentID = $gAgentMap{lc $agent};
         #die("ERROR: No agent publisher named '$agent'\n") if (!$zzAgentID);

         if (!$zzAgentID) {
            # XXX
            #report("ERROR: No agent publisher named '$agent':");
            #foreach my $a (keys %gAgentMap) {
            #   my $v = $gAgentMap{$a};
            #   report("   key($a)  val($v)");
            #}

            #$gMissingAgentMap{$agent} = $agent;

            #die("HALT\n") if( $execMode );
            # XXX

            if( exists $gPendingAgentMap{$agent} )
            {
               # not an error; the missing agent will be created when run in exec-mode
               report("Warning: agent publisher '$agent' hasn't been created yet");
            }
            else
            {
               report("ERROR: No agent publisher named '$agent':");
               foreach my $a (keys %gAgentMap) {
                  my $v = $gAgentMap{$a};
                  report("   gAgentMap: key($a)  val($v)");
               }

               foreach my $a (keys %gPendingAgentMap) {
                  my $v = $gPendingAgentMap{$a};
                  report("   gPendingAgentMap: key($a)  val($v)");
               }

               $gMissingAgentMap{$agent} = $agent;

               _appendString( $errorCode, "Agent not found");
               ++$gExCount{agent_not_found}; # 7/1/20
               $publisher->{'error-code'} = $errorCode;
#               die("HALT\n") if( $execMode );
               next;

            }
         }
         else
         {
            $args{agent_id} = $zzAgentID;
            $searchSql .= "AND agent_id=$zzAgentID ";
         }
      }
      else {
         $searchSql .= "AND agent_id IS NULL ";
      }

      $args{is_admin} = 1 if ( $publisherType == kPublisherTypeAdmin );

      $args{is_agency} = 1 if ( $publisherType == kPublisherTypeAgent );


      if($publisherType == kPublisherTypeAdmin)
      {
          $gPendingAdminMap{$publisherName} = $publisherName;
      }
      if($publisherType == kPublisherTypeAgent)
      {
          $gPendingAgentMap{$publisherName} = $publisherName;
      }

#      die("HFA already set?") if ( $publisherType == kPublisherTypeAgent and defined $gHfaID);

      #my $searchSql = "SELECT publisher_id FROM publisher "
      #   . "WHERE publisher_name = ? ";
      #$searchSql .= "AND client_account_id='$clientAccountNo'" if ( $clientAccountNo );

      my $adminName = trimspaces $publisher->{admin};

      #------------------------------------------------------
      # Per the 1.0 spec, the publisher type must be standard
      # in order for the 'admin' field to be applicable.
      #------------------------------------------------------
      if ( ($mode == kPublisherTypeAdmin) && $adminName && ('' ne $adminName) ) {
         _appendString( $errorCode, "Admin has admin");
         ++$gExCount{admin_has_admin};
         $publisher->{'error-code'} = $errorCode;
         next;
         #die("ERROR: Detected administrator ($publisherName) with an admin ($adminName)");
      }

#      if ( ($mode != kPublisherTypeAdmin) && $adminName && ('' ne $adminName) ) {
      if ( $mode != kPublisherTypeAdmin ) {

#         #-----------------------------
#         # Find the admin's publisherID
#         #-----------------------------
#         my $adminName = trimspaces $publisher->{admin};
#         report("   _createPublisher: admin($adminName)");

         # If we're not processing an admin publisher, check if the specified admin is
         # valid.  In the case of no admin specified, we'll add "admin IS NULL" to our
         # existing publisher check.  We're making a slight change to what we consider
         # duplicates.  You can have the same publisher name if the agent/admin relations
         # are different. -ES 6/10/15
         #
         if ( $adminName && '' ne $adminName) {
            #-----------------------------
            # Find the admin's publisherID
            #-----------------------------
            my $adminName = trimspaces $publisher->{admin};
            $adminName =~ s/\x{a0}/ /g; # Change NBSP to regular space
            $publisher->{admin} = $adminName;
            report("   _createPublisher: admin($adminName)");

            my $sql = qq(
               SELECT publisher_id FROM publisher
               WHERE publisher_name=?
               AND is_admin=1
            );

            my $sth = $dbh->prepare($sql);
            $sth->execute($adminName);

            if ( $sth->rows == 0 ) {

               #---------------------------------------------------------
               # If we get here, then we didn't create the required admin
               # during the initial pass through the publisher hash (or
               # the admin name for the publisher isn't correct).
               #---------------------------------------------------------
               #report("   Admin '$adminName' was not found");
               #$gMissingAdminMap{$adminName} = $adminName;

               #_appendString( $errorCode, "Admin not found");
               #++$gExCount{admin_not_found};


               if( exists $gPendingAdminMap{$adminName} )
               {
                  report("   Warning: Admin '$adminName' has not been created yet");
               }
               else
               {
                  report("   Admin '$adminName' was not found");
                  $gMissingAdminMap{$adminName} = $adminName;

                  _appendString( $errorCode, "Admin not found");
                  ++$gExCount{admin_not_found};

               }

               $publisher->{'error-code'} = $errorCode;
               next;
            }

            my $adminID = $sth->fetchrow_array();
            $args{admin_id} = $adminID;
            $searchSql .= "AND admin_id=$adminID ";
         } else {
         #   $args{agent_id} = $gHfaID;
         #   $searchSql .= "AND agent_id=$gHfaID ";
            $searchSql .= "AND admin_id IS NULL ";
         }
      }

      #--------------------------------------------------------------------
      # If we're searching for a duplicate admin, set searchSql accordingly
      #--------------------------------------------------------------------
      if ( $mode == kPublisherTypeAdmin ) {
         $searchSql .= "AND is_admin=1 ";
      }

      # You can have the same publisher name / client account ID as an agent
      # or an admin, so if we're trying to setup a standard publisher, we
      # check that the same publisher doesn't already exist as an agent or
      # admin (e.g., both the is_admin and is_agent fields are empty).
      #
      if ( $mode == kPublisherTypeStandard ) {
         $searchSql .= "AND is_admin=0 AND is_agency=0 ";
      }

      #----------------------
      # does publisher exist?
      #----------------------
      my $sth = $dbh->prepare($searchSql);
      #$sth->execute($publisher->{publisherName});
      report("DEBUG: publisherName($publisherName)");
      if ( $clientAccountNo ) {
         $sth->execute($publisherName,$clientAccountNo);
      } else {
         $sth->execute($publisherName);
      }
      my ($publisherID) = $sth->fetchrow_array();

report("D: mode($mode) searchSql = $searchSql"); # XXX XXX
      if ( $sth->rows >= 1 ) { # XXX XXX XXX
         report("_EXCEPTION: duplicate publisher name '$publisherName' --> publisherID($publisherID)");
         _appendString( $errorCode, "Duplicate publisher name");
         ++$gExCount{duplicate_publisher_name};
      }
      report("_EXCEPTION: publisher '$publisherName' "
         #. "(hash=". $publisher->{publisherName}
         . " has ". $sth->rows . " hit(s)");

      #my $cleanAddress = $streetAddress;
      #$cleanAddress =~ s/\n/\\n/g;

      #---------------------------------------------------------
      # If this row had any errors, don't process this line.
      # Note: some additional exception detection is done below;
      # if we find any further exceptions then those have to be
      # manually stored in the 'error-code' field.
      #---------------------------------------------------------
      if ( $errorCode ) {
         $publisher->{'error-code'} = $errorCode;
         next;
      }

      #-------------------------------------
      # create publisher if it doesn't exist
      #-------------------------------------
      my $pObj;
      if ( not defined $publisherID ) {
         if ( $execMode ) {
            $pObj = RPS::DB::Item::Publisher->Create( %args );
            $pObj->save();
            $publisherID = $pObj->publisher_id;
            report("####   Created publisher $publisherID : ".Dumper(\%args));
            ++$gCount{publisher};

            $publisher->{'rs-payee-id'} = $publisherID;

            if ( $mode == kPublisherTypeAgent ) {
               $gAgentMap{lc $publisherName} = $publisherID;
            }

         } else {
            report("   Non-exec mode, skipping publisher creation : " . Dumper(\%args) );
         }
      } else {
         report("   Warning: publisher $publisherID exists");
         $gExistingPublisherCount++;
         ++$gExCount{publisher_exists};
         _appendString( $errorCode, "Publisher exists");
      }

#      #---------------------------------------------------------
#      # If this row had any errors, don't process this line.
#      # Note: some additional exception detection is done below;
#      # if we find any further exceptions then those have to be
#      # manually stored in the 'error-code' field.
#      #---------------------------------------------------------
#      if ( $errorCode ) {
#         $publisher->{'error-code'} = $errorCode;
#         next;
#      }

      #---------------------------------------------------------------------------
      # Optional: Setup publisher_account, finance_account, pending_transaction
      #---------------------------------------------------------------------------

      my $financeAccountID;

      #-----------------------------------------------------------------------------
      # If min payment is defined, create a finance account and/or publisher_account
      # if neither exist.
      #-----------------------------------------------------------------------------

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

         #--------------------------
         # Setup the finance account
         #--------------------------
         my %faArgs = (
            description => "account for publisher $publisherID payor $payorID",
            type_code => RPS::DB::Item::FinanceAccount::kAccountTypeHoldover,
            currency_code => 'USD',  # 3/28/16
         );
         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));
               ++$gCount{finance_account};

            } else {
               report("   Non-exec mode, skipped finance_account for payee $publisherID, payor $payorID");
            }
         } else {
            $financeAccountID = $faObj->finance_account_id;
            report("EXISTS: finance_account $financeAccountID");
         }

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

      if ( $financeAccountID && $balance && $balance ne "") {
         #--------------------------------------------
         # Setup the pending transaction if amount > 0
         #--------------------------------------------
         $balance =~ s/\$//;
         $balance =~ s/,//;
         if ( $balance =~ m/\(.*\)/ ) {
            $balance =~ s/\(//;
            $balance =~ s/\)//;
            $balance *= -1;
         }

         my %ptArgs = (
            finance_account_id => $financeAccountID,
            amount => $balance,
            memo => "opening balance",
            type_code => RPS::DB::Item::PendingTransaction::kTypeAdjustment,
            currency_code => 'USD',
         );
         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));
               ++$gCount{pending_transaction};
            } else {
               report("   Non-exec mode, skipping pending_transaction");
            }
         } else {
            $ptID = $ptObj->pending_transaction_id;
            report("EXISTS: pending_transaction $ptID");
         }

      }

      #-------------------------------
      # Setup the publisher account
      #-------------------------------
      if ( $financeAccountID && (
           ($minPayment && '' ne $minPayment) ||
           ($balance && '' ne $balance  )) )
      {
         my %args = (
            publisher_id => $publisherID,
            payor_id => $payorID,
            finance_account_id => $financeAccountID,
         );
         if ( defined $minPayment ) {
            $minPayment =~ s/\$//;
            $minPayment =~ s/,//;
            $args{min_payment} = $minPayment;
         }
         my $apObj = RPS::DB::Item::PublisherAccount->Lookup(%args);
         if ( not defined $apObj ) {
            if ( $execMode ) {
               $apObj = RPS::DB::Item::PublisherAccount->Create(%args);
               $apObj->save();
               my $id = $apObj->publisher_account_id;
               report("   Created publisher_account $id : ".Dumper(\%args));
               ++$gCount{publisher_account};
            } else {
               report("   Non-exec mode, skipping publisher_account for publisher $publisherID, finance_account $financeAccountID");
            }
         } else {
            report("EXISTS: publisher_account exists for publisher $publisherID, finance_account $financeAccountID");
         }
      }

   }# publisher loop
             
}#_createPublishers

sub _getPublisherType {
   my($s) = @_;
   my $ptype;
   #$ptype = kPublisherTypeStandard if ( (lc $s) eq 'standard publisher');
   $ptype = kPublisherTypeStandard if ( $s =~ /standard/i );
   $ptype = kPublisherTypeAdmin if ( (lc $s) eq 'administrator');
   $ptype = kPublisherTypeAgent if ( $s =~ /(agent|agency)/i);
   #report("DEBUG: _getPublisherType: s($s) --> ($ptype)");
   return $ptype;
}#_getPublisherType

#--------------------------------------------------------------------------
# _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
# payeeID will be stored in the import status column ("payeeID(###)"),
# 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 $payeeName = $row->{'publisher-name'};
      next if ( !$payeeName );

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

      #$payeeID = "NULL" if ( !$payeeID && !$execMode );
      $payeeID = "NULL" if ( !$payeeID );

#die("no payeeID for '$payeeName' in execMode (errorCode=$errorCode)?") if ( !$payeeID );
      my $importStatus;
      if ( $errorCode ) {
         $importStatus = "__FAIL__";
      } else {
         $importStatus = "payee($payeeID)";
      }
      #my $importStatus = ($errorCode) ? "__FAIL__" : "payee($payeeID)";

      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;