package Support::Implementation::Excel2007Reader;
#-----------------------------------------------------
#  ExcelReader - provides access to Excel spreadsheets
# 2/17/11: Added check for missing 'Val' key
# 3/1/16: Use formatted value for numeric cells
# 3/22/16: Numeric cell adjustment; use unformatted but add 0
#   to lose "extra" precision
# 5/24/16 - Don't clean up non-numeric formatted cells
# 1/5/17 - Set 'missingmap' attribute to hash of any missing columns
#-----------------------------------------------------
use strict;
use warnings;

use IO::File;
use Data::Dumper;

#use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::UTF8;

#use ExcelReader;

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

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

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

sub FixXML {
    my $parm = $_[0];
    $parm =~ s/&/&/g;
    $parm =~ s/>/>/g;
    $parm =~ s/&lt;/</g;
    $parm =~ s/&quot;/"/g;
    $parm =~ s/&apos;/'/g;
    $parm =~ s/&#xA;/\n/g;
    $parm =~ s/&#xa;/\n/g;
    $parm =~ s/&#xD;/\r/g;
    $parm =~ s/&#xd;/\r/g;
    $parm =~ s/&#x9;/\t/g;
    return($parm);
}

#------------------------------------------------------------------------
# _scanExcelFile will build a generic array of hashes; each row from the
# template will be stored as a hash, using the header name as the key.
# The set of rows will be returned as an array.
# Arguments:
#   fileName - the name of the Excel file to read (_must_ be Excel 2k3)
#   data - the hash which will contain the data
#   templateHeader - the template header hash
#   columnMap - will map each column (by name) to the actual column #
# Any columns not found in 'templateHeader' will be returned in the 'missingmap' attribute
#------------------------------------------------------------------------
sub scanExcelFile {
   my $self = shift;
   my $fileName = $self->{filename};
   my $data = $self->{data};
   my $templateHeader = $self->{header};
   my $columnMap = $self->{columnmap};

   assert($fileName);
   assert($data);
   assert($templateHeader);

print STDERR "DEBUG: Excel2007Reader.scanExcelFile - creating oExcel object ...\n";
   my $oExcel = Spreadsheet::XLSX->new( $fileName );
   #my $oBook = $oExcel->Parse($fileName);
   #die("FATAL ERROR: Unable to open '$fileName'") if ( !$oBook );
   die("FATAL ERROR: Unable to open '$fileName'") if ( !$oExcel );

print STDERR "DEBUG: Excel2007Reader.scanExcelFile...\n";

   #my $numSheets = $oBook->{SheetCount};
   #my $workSheet = $oBook->{Worksheet}[0];

   my $numSheets = $oExcel->{SheetCount};
   my $workSheet = $oExcel->{Worksheet}[0];

   #-----------------------------------
   # The first row _must_ be the header
   #-----------------------------------

   #----------------------------------------------------------------
   # As columns are found, they'll be removed from 'columnsNotFound'
   #----------------------------------------------------------------
   my %columnsNotFound = %$templateHeader;

   my $foundColumns=0;
   my $maxCol = $workSheet->{MaxCol}; # absolute number of columns
   for(my $col = $workSheet->{MinCol};
       defined $maxCol && $col <= $maxCol;
       $col++) {
      my $cellObj = $workSheet->{Cells}[0][$col];
#      report("ExcelReader: col($col)");

      if ( !$cellObj ) {
         report("  Excel2007Reader -- row(0),col($col) is empty");
         next;
      }
      if ( $cellObj && ( ! defined ($cellObj->Value) ) ) { # shouldn't happen..
         assert("undefined cellObj->Value (col=$col)");
      } else {
         die("cellObj not defined") if ( !$cellObj );
         report("cellObj->Value not defined, col=$col, maxCol=$maxCol") if ( !$cellObj->Value );
         #next; # No need to process this column... it has nothing
      }

      #-----------------------------------------------------------
      # In some cases, Excel may report more columns than expected.
      # These 'extra' columns should be considered blanks and not
      # processed.
      #-----------------------------------------------------------
      if ( $cellObj && $cellObj->Value ) {

         my $cellValue = $cellObj->Value; # TODO - trimSpaces?
         my $origCellValue = $cellObj->Value; # header name direct from template
# 10/9/19 - TEST: remove leading/trailing spaces and force column name to lowercase
#         $origCellValue =~ s/^\s+//g;
#         $origCellValue =~ s/\s+$//g;
#         $origCellValue = lc $origCellValue;
#         print "D: Excel2007Reader::scanExcelFile - origCellValue($origCellValue)\n"; # XXX
         $cellValue =~ s/ //g;  # remember, no spaces in header names...

         if ( '' eq $cellValue ) { # shouldn't happen..
            assert("undefined cellValue (col=$col)");
         }

#         report("Looking for column($cellValue)");
         if ( defined $templateHeader->{"$cellValue"} ) {
            my $expectedColumn = $templateHeader->{"$cellValue"};
            $foundColumns++;
            delete $columnsNotFound{"$cellValue"};
#            if ( $expectedColumn != $col ) {
#               report("   WARNING: column '$origCellValue', expected $expectedColumn, got $col");
#            } else {
#               report("   OK: column '$origCellValue' is in column $col");
#            }

            #------------------------------------------------------------------
            # Store the actual column # associated with the column header.
            # We'll use this information when it comes time to parse the 'data'
            # hash.
            #------------------------------------------------------------------
            $columnMap->{$col} = $cellValue;

         } else {
            if ( '' eq $origCellValue ) {
            #if ( !$origCellValue ) {
               report("   Excel2007Reader Warning: empty header cell in col $col, skipping...");
               next;
            } else {
               report("   Excel2007Reader Warning: Did not find column '$origCellValue' (col=$col) "
                  . "in templateHeader");
               #---------------------------------------
               # Store the new column in the header map
               #---------------------------------------
               $templateHeader->{"$origCellValue"} = $col;
               $columnMap->{$col} = $origCellValue;
            }
         }

      }
   }#column loop (header)

   if ( $foundColumns < (keys %{$templateHeader}) ) {
      report("Excel2007Reader: This template is missing the following columns:");
      $self->{missingmap} = \%columnsNotFound;
      foreach my $k (keys %columnsNotFound) {
         my $v = $columnsNotFound{$k};
         #report("DEBUG: key($k)   v($v)");
         report("  $k");
      }
      #die("\n###\n### Import halted: please correct the header before continuing\n###"); XXX
   }

   #----------------------
   # Process the data rows
   #----------------------
   for( my $row = $workSheet->{MinRow} + 1;
        $row <= $workSheet->{MaxRow};
        $row++ ) {

      my $dataRow = {};
      $dataRow->{rowid} = $row+1; # rowid is the row# that the user sees

      #-------------------------------------------------------
      # Check if the row is blank; if so, don't store anything
      #-------------------------------------------------------
      my $rowHasData;
      for( my $col = $workSheet->{MinCol};
         defined $maxCol && $col <= ($maxCol);
         $col++ ) {
         my $cellObj = $workSheet->{Cells}[$row][$col];
         #$rowHasData=1 if ( $cellObj && "" ne $cellObj->{Val} );
         $rowHasData=1 if ( $cellObj && (defined $cellObj->{Val}) && "" ne $cellObj->{Val} );
      }

      next if ( !$rowHasData );

      for( my $col = $workSheet->{MinCol}; $col <= $maxCol; $col++ ) {
         my $cellObj = $workSheet->{Cells}[$row][$col];
         my $cellValue;
         my $encoding;
         #if ( defined $cellObj and '' ne $cellObj->{Val} ) {
         if ( (defined $cellObj) && (defined $cellObj->{Val}) && ('' ne $cellObj->{Val}) ) {
            $encoding = (defined $cellObj->{Code}) ? $cellObj->{Code} : "?";

            $cellValue = $cellObj->{Val}; # use raw cell value ** DEPRECATED
            #$cellValue = $cellObj->unformatted(); # use raw cell value

            my $formattedValue = $cellObj->Value(); # formatted value

            $cellValue = FixXML($cellValue);
#print STDERR "row($row) col($col): cellValue($cellValue) formatted($formattedValue)\n"; # XXX
            #-------------------------------------------
            # Some fields need to be handled differently
            #-------------------------------------------
            my $type = $cellObj->{Type};

print "row($row) col($col) type($type): cellValue($cellValue) formatted($formattedValue) uformat(". $cellObj->unformatted() .")\n"; # XXX
#            if ( $formattedValue =~ m/\%$/ ) {
#               #-------------------------------------------------------------
#               # Percentages are special; if we don't use the formatted value
#               # then cellValue will be a number from 0..1 instead of 0..100
#               #-------------------------------------------------------------
#               $cellValue = $formattedValue;
#               $cellValue =~ s/\%$//; # remove the percent sign
#
#            } elsif( $formattedValue =~ m/^\$/ ) {
#               $cellValue = $formattedValue;
#               $cellValue =~ s/^\$//; # remove the dollar sign
#            }
#            elsif ( "Numeric" eq $type ) {
#               #$cellValue = $formattedValue;
#               $cellValue = $cellObj->unformatted() + 0; # XXX  Remove 'extra' precision  3/22/16 -ES
#            }

            if ( "Numeric" eq $type ) {
               if ( $formattedValue =~ m/\%$/ ) {
                  #-------------------------------------------------------------
                  # Percentages are special; if we don't use the formatted value
                  # then cellValue will be a number from 0..1 instead of 0..100
                  #-------------------------------------------------------------
                  $cellValue = $formattedValue;
                  $cellValue =~ s/\%$//; # remove the percent sign
                  $cellValue += 0;

# Treat numeric cells with dollar sign like any other numeric cell
#               } elsif( $formattedValue =~ m/^\s*\$/ ) {
#                  $cellValue = $formattedValue;
#                  $cellValue =~ s/^\s*\$//; # remove the dollar sign
#                  $cellValue =~ s/\s//g; # .. any any whitespace
               } else {
                  #$cellValue = $formattedValue;
                  $cellValue = $cellObj->unformatted() + 0; # XXX  Remove 'extra' precision  3/22/16 -ES
#print "D: stored cellValue: $cellValue\n"; # XXX
               }
            }
            elsif( 'Text' eq $type ) {
               ; # just use cell value as-is - 7/5/16
            }
            else {
               # Use the formatted value - 5/24/16
               $cellValue = $formattedValue;

            }

            if ( "Date" eq $type ) {
               #-------------------------------------------------------------
               # The raw value is not usable; use the formatted value instead
               #-------------------------------------------------------------
               $cellValue = $formattedValue;
            }

            $cellValue =~ s/(\r|\n)//g if ( $cellValue );  # remove embedded returns from all fields

            my $excelRow = $row + 1;
            my $excelCol = $col + 1;

            #die("UCS2 found in row $excelRow, col $excelCol") if ( 'ucs2' eq (lc $encoding) );
            if ( 'ucs2' eq (lc $encoding) ) {
               report("WARNING: UCS2 found in row $excelRow, col $excelCol") if ( 'ucs2' eq (lc $encoding) );

               #$cellValue = Common::UTF8::Encode($cellValue, 'UCS2');
               $cellValue = Common::UTF8::Encode($cellValue, 
                  Common::UTF8::kEncodingUCS2
               );
            } else {
               $cellValue = Common::UTF8::Encode($cellValue);
            }
#            report("DEBUG: ($row, $col) VALUE($cellValue) formattedValue($formattedValue) "
#               . "encoding($encoding) type($type)");

#            $cellValue = Common::UTF8::Encode($cellValue);

         } else {
            #die("no cellObj at ($row,$col) ?");

         }

         my $key = $columnMap->{$col};

         #------------------------------------------------------
         # If there are empty columns (e.g. $key is undef), then
         # dont' store any data for that column.
         #------------------------------------------------------
#die("null key at row($row) col($col)?") if ( ! $key );
         $dataRow->{$key} = $cellValue if ( $key );

      }# column loop

      #---------------
      # Store the data
      #---------------
      push @{$data->{rows}}, $dataRow;

   }# row loop

}#_scanExcelFile

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

1;