package Orchard::ETL::Extract::SpreadSheet;

use strict;
use warnings;

use lib '/var/app/orchard/collab/jgetter/perllib';

use base 'Class::Accessor';

#__PACKAGE__->mk_accessors(qw/csv data fh header named_cols col_names total_lines lineno/);
__PACKAGE__->mk_accessors(qw/workbook header named_cols col_names/);

use FileHandle;
use Spreadsheet::Read;
use Orchard::NamedColumns;

sub new {
    my $class = shift;
    my $file  = shift;
    my $head  = shift || 1;
    my $self  = { 'workbook' => Spreadsheet::Read->new($file), 'header' => $head };
    bless $self, $class;
    $self;
}

sub getrows {
    my $self = shift;
    my $snum = shift || 1;

    ## get worksheet by sheet number
    my $worksheet = $self->workbook->sheet($snum);

    my $header;
    map {
        $_ =~ s/\s+/_/g;
        $_ =~ s/\W+//g;
        push @{$header}, lc($_)
    } $worksheet->row( $self->header );

    ## set the named cols i.e. A=>FirstName, B=>LastName or whatever
    $self->named_cols( Orchard::NamedColumns::letter_cols($header) );
    $self->col_names( Orchard::NamedColumns::col_letters($header) );

    my $rows;
    for ( my $i = ( $self->header + 1 ) ; $i <= $worksheet->maxrow ; $i++ ) {
        my $row = [ $worksheet->row($i) ];
        my $href;
        for ( my $j = 0 ; $j < scalar @{$header} ; $j++ ) {
            $href->{ $header->[$j] } = $row->[$j];
        }
        push @{$rows}, $href;
    }
    $rows;
}

sub getcol {
    my $self = shift;
    my $row  = shift;
    my $col  = shift;
    my $key  = $self->named_cols->{ uc($col) };
    ( defined $key and defined $row->{$key} and $row->{$key} ne '' ) ? $row->{$key} : undef;
}

sub getkey {
    my $self = shift;
    my $row  = shift;
    my $key  = shift;
    ( defined $row->{$key} and $row->{$key} ne '' ) ? $row->{$key} : undef;
}

sub getletter {
    my $self  = shift;
    my $text  = shift;
    my ($col) = map { $self->col_names->{$_} } grep { $_ =~ m/$text/i } keys %{ $self->col_names };
    $col;
}

sub getstr {
    my $self = shift;
    my $row  = shift;
    my @cols;
    foreach my $col ( ( 'A' ... 'Z', 'AA' ... 'ZZ' ) ) {
        next unless exists $self->named_cols->{$col};
        my $col = defined $self->getcol( $row, $col ) ? $self->getcol( $row, $col ) : '';
        push @cols, $col;
    }
    $self->csv->combine(@cols);
    $self->csv->string;

}

1;