#!/usr/bin/perl

use strict;
use Getopt::Std;
use Spreadsheet::ParseExcel;
use Unicode::String qw(ucs2 latin1);
use Date::Calc qw(Add_Delta_YMD);

use lib '/app/tools/common/lib';
use Common::Util qw(normalize_date trimquotes);

use constant SEP => "\t";

$| = 1;

my %opt;
getopts( 'o:c', \%opt );
my $xls = $ARGV[0] || usage();
die "can't open $xls\n" unless ( -r $xls );

my $slx = reverse $xls;
my ($fileName) = $slx =~ m/^slx\.(.+?)(\/|$)/i;
$fileName = reverse $fileName;

my $outDir = $opt{o} || $ENV{PWD};
$outDir =~ s/^\.$/$ENV{PWD}/;
$outDir =~ s/\/$//;

print "\nopening $xls...\n";
my $bookObj = Spreadsheet::ParseExcel::Workbook->Parse($xls);
my ( $row, $sheet );
my @sheets = _get_sheet_list( $bookObj->{Worksheet}, $opt{c} );

if ( scalar @sheets == 1 ) {
    my $sheet = $bookObj->{Worksheet}->[ $sheets[0] ];
    print "\nprocessing '$sheet->{Name}'\n";
    my $outFile = sprintf( "%s/%s.tsv", $outDir, $fileName );
    _dumpSheet( $sheet, $outFile );
    print "  saved $outFile\n";
} else {
    foreach my $sheetNum (@sheets) {
        my $sheet     = $bookObj->{Worksheet}->[$sheetNum];
        my $sheetName = $sheet->{Name};
        print "\nprocessing '$sheetName'\n";
        $sheetName =~ s/\s+/_/g;
        my $outFile = sprintf( "%s/%s.tsv", $outDir, $sheetName );
        _dumpSheet( $sheet, $outFile );
        print "  saved $outFile\n";
    }
}

print "\ndone\n";

#/////////////////////////////////////////////////
sub _dumpSheet {
    my ( $sheet, $output ) = @_;

    open( TXT, ">$output" ) || die "can't open $output for output: $!\n";

    for ( my $row = $sheet->{MinRow} ; $row <= $sheet->{MaxRow} ; $row++ ) {
        my @cols;
        foreach my $cell ( @{ $sheet->{Cells}[$row] } ) {
            unless ( ref $cell )    # put an empty string in this cell
            {
                push( @cols, '' );
                next;
            }

            my $value = $cell->{Val};

            if ( lc( $cell->{Code} ) eq 'ucs2' ) {
                my $ucs2_val = ucs2($value);
                $value = $ucs2_val->latin1;
            }

            if ( lc( $cell->{Type} ) eq 'date' ) {
                $value = Spreadsheet::ParseExcel::Utility::ExcelFmt( "yyyy-mm-dd", $value );
                if ( $bookObj->{Flg1904} )    # need to add 4 yrs
                {
                    my ( $y, $m, $d ) = split /-/, $value;
                    $value = Common::Util::normalize_date( join( '-', Add_Delta_YMD( $y, $m, $d, 4, 0, 1 ) ) );
                }
            } else    # raw value without formatting
            {
                $value = Common::Util::trimquotes( _cleanup($value) );
            }

            push @cols, $value;
        }

        print TXT join( SEP, @cols );
        print TXT "\n";
    }
    close(TXT);
}

sub _get_sheet_list {
    my $sheets = shift;
    my $prompt = shift;

    if ($prompt) {
        print "\nsheet names:\n";
        my $i = 1;
        map { printf( "%d. '%s' (%d rows)\n", $i++, $_->{Name}, $_->{MaxRow} ) } @$sheets;
        print "\nenter comma separated list of sheet nums to dump or 0 for all: ";
        my $list = <STDIN>;
        chomp $list;

        return _get_all_sheets($sheets) unless $list;
        my @sheetList = split /,\s*/, $list;
        map { $_-- } @sheetList;
        return @sheetList;
    }

    return _get_all_sheets($sheets);
}

sub _get_all_sheets {
    my $sheets    = shift;
    my @sheetList = ();
    my $i         = 0;

    map {
        push( @sheetList, $i ) if ( defined $_->{MaxRow} );
        $i++;
    } @$sheets;

    return @sheetList;
}

sub _cleanup {
    my @data = @_;

    for (@data) {
        s/[\n\r\t]+/ /g;    # strip tabs and linebreaks
        s/^\s+//;
        s/\s+$//;
    }

    return @data;
}

sub usage {
    die <<EofUSAGE;
usage: $0 [options] path/to/excel/file
  [-o path/to/output/dir]
  [-c (choose sheets names to dump)]
EofUSAGE
}