#!/usr/bin/perl -w
use strict;
use warnings;
use Data::Dumper;
use Getopt::Long;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;                                # die on errors...

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');

# open Excel file
my $Book = $Excel->Workbooks->Open("C:/Audio/perl/Project/Voice.xls");

my $Sheet = $Book->Worksheets('Feedback') ||
    die( "No such worksheet gendata" );

my $row_start_tmp = 0;
my $row_end_tmp = 0;
my $eur_column = 0;
my $gom_column = 0;
my $nam_column = 0;
my $column_add = 0;
my @gm_out;
my $my_row = 0;
my $my_column = 0;
my $feedback_value = 0;
my $eur_gom_total = 0;
my $gom_nam_total = 0;
my $eur_nam_total = 0;
my $feedback_ID = 0;

#-------------------------------------------------------------------------------
# TR file name
my $fl = "FB_CHECK.csv";

	$row_start_tmp = 2;
	$row_end_tmp = 695;
	$eur_column = 26;
	$gom_column = 120;

	my $i = 0;
	open(FH, ">$fl") or die $!;	  # open the excel sheet for write
	$feedback_ID = 3;


	foreach my $row ($row_start_tmp..$row_end_tmp)
	{
		foreach my $col ($eur_column..$eur_column)
		{
	  		# skip empty cells
#	  		next unless defined $Sheet->Cells($row,$column_value)->{'Value'}; 

			$my_row = ($Sheet->Cells($row,$eur_column)->{'Value'});
			$my_row =~ s/(^\s+|\s+$)//g;
			$my_column = ($Sheet->Cells($row,$gom_column)->{'Value'});
			$my_column =~ s/(^\s+|\s+$)//g;
			$feedback_value = ($Sheet->Cells($row,$feedback_ID)->{'Value'});
			$feedback_value =~ s/(^\s+|\s+$)//g;
			
			if ($my_row ne $my_column)
			{
				printf " %s At ($row, $col) the row is %s and the column is %s for EUR & NAM \n", $feedback_value, $my_row,$my_column;
				push @gm_out, [ $feedback_value, $my_row,$my_column,0 ];
			}
		}
	}
    printf FH "\n";
	$eur_gom_total = @gm_out;
    printf FH "The Total mismatches between EUR & GOM is $eur_gom_total \n";

    printf FH "\n";
   	printf FH "Below results for EUR & GOM \n";
	printf FH "\n";
	printf FH "Row value, EUR English Display Data, GOM English Display Data, NAM English Display Data \n";

	my $out_size = @gm_out;
	for($i=0;$i<$out_size;$i++)
	{
	    print FH "$gm_out[$i][0],$gm_out[$i][1],$gm_out[$i][2],$gm_out[$i][3] \n";
	}