package Support::Implementation::ExcelReader;
#-----------------------------------------------------
#  ExcelReader - provides access to Excel spreadsheets
# 2/17/11: Added check for missing 'Val' key
# 1/15/13: Suppressed UCS2 warning.
# 11/25/14: Don't strip leading dollar signs if present
#   (this was causing issues with fields where we want
#   the leading dollar sign preserved - case 7572)
# 4/26/17: Fixed percent-sign handling (was noticed in case 18534,
#   where titles with a trailing percent were getting munged).
#-----------------------------------------------------
use strict;
use warnings;

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

use Spreadsheet::ParseExcel;

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

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

   my $oExcel = new Spreadsheet::ParseExcel;
   my $oBook = $oExcel->Parse($fileName);
   die("FATAL ERROR: Unable to open '$fileName'") if ( !$oBook );

   my $numSheets = $oBook->{SheetCount};
   my $workSheet = $oBook->{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("  ExcelReader -- 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
         $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("   ExcelReader Warning: empty header cell in col $col, skipping...");
               next;
            } else {
               report("   ExcelReader 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("ExcelReader: This template is missing the following columns:");
      foreach my $k (keys %columnsNotFound) {
         my $v = $columnsNotFound{$k};
         #report("DEBUG: key($k)   v($v)");
         report("  $k");
      }
   }

   #----------------------
   # 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 && (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) && (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

            #-------------------------------------------
            # Some fields need to be handled differently
            #-------------------------------------------
            my $type = $cellObj->{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
#            }

#            elsif( $formattedValue =~ m/^\$/ )
#            {
#               $cellValue = $formattedValue;
#               $cellValue =~ s/^\$//; # remove the dollar sign
#            }

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

            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;

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

               $cellValue = Common::UTF8::Encode($cellValue, 
                  Common::UTF8::kEncodingUCS2
               );
            } else {
               $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.
         #------------------------------------------------------

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