#!/usr/bin/perl 

use lib 'SPE/blib/lib';

use strict;
use warnings;
#===========================================================
#  DECLARATIONS
#===========================================================
use Data::Dumper;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Text::ParseWords;

#===========================================================
#  VARIABLES
#===========================================================

my $dir = 'M:\Business Process Reengineering\Taxonomy\Reviewed Content\Group D';

my $column_max = 26;

#===========================================================
#  SUBROUTINES
#===========================================================
sub trim {
    $_[0] =~ s/\s+$//;
    $_[0] =~ s/^\s+//;
}

sub parse_excel {

  my %by_agency;

  my $excel_file = shift;
  my $acronyms = shift;

  -e $excel_file or 
    die "Must provide valid XLS file! $excel_file, $!\n";

  # create a ParseExcel object
  my $excel_obj = Spreadsheet::ParseExcel->new();

  warn "Parsing $excel_file (may take a long time)";
    
  my $workbook = $excel_obj->Parse($excel_file);
    
  # we need to get each worksheet from the workbook in turn

  my @worksheet = @{$workbook->{Worksheet}};

  my $worksheet_i;
  my $header;
    
  for my $worksheet ( @{$workbook->{Worksheet}} ) {
	
    # {Cells}[row][col]
	
    my $last_col = $worksheet->{MaxCol};
    my $last_row = $worksheet->{MaxRow};

    warn "Last row: $last_row";
    warn "Last col: $last_col";

    $header = [ map { $_->Value } @{$worksheet->{Cells}[0]} ] ;

    for my $row ( 1 .. $last_row ) {

      my $row = $worksheet->{Cells}[$row];

      ref($row->[2]) and (my $V = $row->[2]->Value) or next;

#      print "row: " . $V;
#      print "\n";

      trim $V;

      my $key = $acronyms -> { $V } ;

      if ($key) {
	  push @ { $by_agency{$key} } ,  [ map { $_->Value }  @$row ] ;
      } else {
	  print qq(no key found for "$V"\n);
      }

    }
    last; # only process one worksheet
  }

  ($header, \%by_agency);
  
}

sub read_acronyms {

    my $f = "$dir\\..\\A-B-C-D Acronyms.csv";

    open F, $f or die "couldnt open $f: $!";

    <F>;

    my %ret;

    while (<F>) {

	my @data = quotewords("\t", 0, $_);
	for (@data) {
	    trim ($_);
	} 

	$ret{$data[1]} = $data[0];

    }

    \%ret;

}

sub write_excel {

    my ($header, $agency, $acronyms) = @_;

    warn 'write_excel';

    for my $acronym (keys %$agency) {

	my @agency_rows = @ { $agency->{$acronym} } ;

	my $w = "Organization_$acronym.xls";

	# Create a new workbook called simple.xls and add a worksheet
	my $workbook  = Spreadsheet::WriteExcel->new($w);
	my $worksheet = $workbook->addworksheet();

	# The general syntax is write($row, $column, $token). Note that row and
	# column are zero indexed

	my $i;

	# Write some text
	
	$worksheet->write(0, $i++,  $_) for @$header;


	my ($row, $col) = (1, 0);
	
	for my $agency_row (@agency_rows) {

	    warn "agency_row: " . Dumper($agency_row);
	    warn "witdth of row: " . scalar @$agency_row;

	    for (@$agency_row) {

		warn "$worksheet->write($row, $col,  $_)";
		$worksheet->write($row, $col,  $_);
		warn "$worksheet->write($row, $col,  $_) WRITTEN";

		++$col;
	    }

	    ++$row;

	}

	return; # why write all the excel files when one is failing...

    }

}


#===========================================================
sub usage {
    
    my ( $tool ) = $0 =~ m,([^\/]+$),;
			    
print <<HERE;
 ------------------------------------------------------------
USAGE:
			    
    $tool 
	    
   Takes an Excel file, parses it into plain text delimited 
   fields and rows, and sends the results to STDOUT. The default 
   field and record delimiters are "\\t" and "\\n" if neither 
   is given.
------------------------------------------------------------
HERE
    exit 0;
}
#===========================================================

#===========================================================
#  PROGRAM PROPER
#===========================================================

    sub main {

	my $f   = "$dir/Organization_GroupD-no-CR-SMALL.xls";
	
	my $acronyms = read_acronyms;
#    print Dumper $acronyms;
	my ($header, $agency) = parse_excel($f, $acronyms, @_);
#    print Dumper $agency;
#    print Dumper $header;
	write_excel($header, $agency, $acronyms);
	0;
    }

exit main(@ARGV);