#!/usr/bin/perl
# $Id: parse.pl 945 2013-04-13 17:12:32Z webb $

use strict;
use warnings;

#
# main()
#

use DBI;
use Getopt::Std;
use YAML qw//;

#sub CONFIG_FILE()    { '/www/wactech/lonestar/import.yaml' }
sub CONFIG_FILE()    { '/home/webb/public_html/lonestar/trunk/import.yaml' }

main();
exit 0;

sub usage {
  print "usage: $0 [-f CONFIG] file [...]\n";
  exit 1;
}

sub main {
  getopts( 'f:', \my %opts )
    or usage;

  usage
    unless @ARGV;

  $opts{f} = CONFIG_FILE
    unless defined $opts{f};

  my $config = YAML::LoadFile( $opts{f} );

  for my $file ( @ARGV ) {
    Lonestar::Parser->parse(
      { file => $file, %{ $config->{parser} } },
      Lonestar::DB->new( $config->{database} ),
      Lonestar::Progress->new( $config->{progress} ),
    );
  }
}


#
# Lonestar::Parser
#
BEGIN {
  package Lonestar::Parser;

  use POSIX qw/ strftime /;
  use Spreadsheet::ParseExcel;
  use Spreadsheet::ParseExcel::Utility qw/ int2col ExcelLocaltime /;

  sub parse {
    my ( $class, $config, $db, $progress ) = @_;

    my $workbook = Lonestar::Workbook->new;

    {
      my $excel = Spreadsheet::ParseExcel->new(
        CellHandler => sub {
          my ( $excel, $sheet, $i_row, $i_col, $cell ) = @_;

          my $index = _worksheet_index( $config->{offset}, $sheet );
          return
            unless defined $index;

          my ( $row, $col ) = ( $i_row + 1, scalar int2col( $i_col ) );

          $progress->soft_update( $index + 1 );

          _parse_cell( $workbook->worksheet( $index ), $row, $col, $cell );
        },
        NotSetCell  => 1,
      );

      $excel->Parse( $config->{file} );
    }

    $db->connect;

    $progress->hard_slots( scalar @{ $workbook->worksheets } );

    # convert owners skip array to hash for O(1) lookup
    $config->{skip}{owners} = { map {; $_ => 1 } @{ $config->{skip}{owners} || [ ] } };

    for my $index ( 0 .. $#{ $workbook->worksheets } ) {
      $progress->hard_update( $index + 1 );

      my $sheet = $workbook->worksheets->[$index];

      next
        if exists $config->{skip}{owners}{ $sheet->{owner} };

      $sheet->{pricing}{cows}{8}  = $sheet->{pricing}{cows}{0};
      $sheet->{pricing}{bulls}{8} = $sheet->{pricing}{bulls}{0};

      _store_worksheet( $index, $sheet, $db )
    }

    $db->disconnect;

    $progress->update( 100 );
  }

  sub _worksheet_index { my ( $offset, $sheet ) = @_; $sheet >= $offset ? $sheet - $offset : undef; }

  sub _parse_cell {
    my ( $worksheet, $row, $col, $cell ) = @_;

    # parse sheet info
    if( $col eq 'B' ) {
      {
        # check for static data
        my %row_map = (
          1  => 'date',
          2  => 'owner',
          3  => 'origin',
          29 => 'receiver_no',
        );

        $worksheet->{ $row_map{$row} } = $cell->{Val}, return
          if exists $row_map{$row};
      }

      {
        # check for cow pricing data
        my %row_map = (
          9  => 0,
          10 => 2,
          11 => 3,
          12 => 4,
        );

        if( exists $row_map{$row} ) {
          $worksheet->{pricing}{cows}{ $row_map{$row} }
            = length $cell->{Val} ? $cell->{Val} / 100 : 0;
          return;
        }
      }

      {
        # check for bull pricing data
        my %row_map = (
          13 => 0,
          14 => 1,
          15 => 2,
          16 => 3,
        );

        if( exists $row_map{$row} ) {
          $worksheet->{pricing}{bulls}{ $row_map{$row} }
            = length $cell->{Val} ? $cell->{Val} / 100 : 0;
          return;
        }
      }
    }

    # parse pricing adjustment info
    elsif( $col eq 'BR' ) {
      {
        # check for cow pricing adjustments
        my %row_map = (
          11 => 'dairy',
          12 => 'l500',
          13 => 'l400',
          14 => 'l350',
          15 => 'l300',
          16 => 'l250',
          22 => 'shelly',
          23 => 'premium',
        );

        $worksheet->{pricing}{adjustments}{cows}{ $row_map{$row} } = $cell->{Val}, return
          if exists $row_map{$row};
      }

      {
        # check for bull pricing adjustments
        my %row_map = (
          35 => 'dairy',
          36 => 'l1000',
          37 => 'l800',
          38 => 'l600',
          42 => 'shelly',
        );

        $worksheet->{pricing}{adjustments}{bulls}{ $row_map{$row} } = $cell->{Val}, return
          if exists $row_map{$row};
      }
    }

    # parse livestock info
    elsif( $row >= 4 && $row <= 116 ) {
      my %col_map = (
        E  => { type => 'cows',  attr => 'holstein' },
        F  => { type => 'cows',  attr => 'regrade' },
        G  => { type => 'cows',  attr => 'weight' },
        AM => { type => 'bulls', attr => 'holstein' },
        AN => { type => 'bulls', attr => 'regrade' },
        AO => { type => 'bulls', attr => 'weight' },
      );

      my $slot = $row - 4;

      # normalize cell value to stripped value or ''
      my $value = defined $cell->{Val} ? $cell->{Val} : '';
      if( $value ne '' ) {
        # strip leading/trailing whitespace
        s/^\s+//, s/\s+\z//
          for $value;
      }

      if( exists $col_map{$col} ) {
        my ( $type, $attr ) = ( @{ $col_map{$col} }{qw/ type attr /} );

        # use existing value or assign to 0 if not present
        $worksheet->{$type}[$slot]{$attr}
          = $value ne '' ? $value : 0;
      }
      elsif( $col eq 'AK' ) {
        my %lot_map = (
          C => 'cows',
          B => 'bulls',
        );

        for my $prefix ( keys %lot_map ) {
          my $type = $lot_map{$prefix};

          $worksheet->{$type}[$slot]{lot_number}
            = sprintf '%s%03d', $prefix, $value;
        }
      }
    }

  }

  sub _store_worksheet {
    my ( $index, $sheet, $db ) = @_;

    $sheet->{pricing}{cows} = { }
      unless defined $sheet->{pricing}{cows};
    $sheet->{pricing}{bulls} = { }
      unless defined $sheet->{pricing}{bulls};

    # skip worksheets with no pricing information
    return
      unless grep $_, values %{ $sheet->{pricing}{cows} }, values %{ $sheet->{pricing}{bulls} };

    # reformat date
    $sheet->{date} = do {
      if( $sheet->{date} =~ m#^(\d+)\D(\d+)\D(\d+)\z# ) {
        my ( $m, $d, $y ) = ( $1, $2, $3 );
        $y = "20$y"
          if $y =~ /^\d{2}\z/;
        sprintf '%04d-%02d-%02d', $y, $m, $d;
      }
      else {
        strftime '%Y-%m-%d', ExcelLocaltime( $sheet->{date} );
      }
    };

    # strip off city
    $sheet->{origin} =~ s/^.*,\s*//;

    # process cows
    for my $cow ( @{ $sheet->{cows} } ) {
      next
        unless $cow->{weight} > 0;

      # adjust to net weight
      # Per phonecall w/ Dawn on 8/1/12, no need to deduct 15 lbs anymore.
      #$cow->{weight} -= 15;

      # figure base price
      my $price = $sheet->{pricing}{cows}{ $cow->{regrade} };

      # adjust price according to weight
         if( $cow->{weight} >= 600 ) {
        # premium beef (no regrade)
        $price += $sheet->{pricing}{adjustments}{cows}{premium}
          if $cow->{regrade} == 0;
      }
      elsif( $cow->{weight} <  250 ) {
        $price -= $sheet->{pricing}{adjustments}{cows}{l250};
      }
      elsif( $cow->{weight} <  300 ) {
        $price -= $sheet->{pricing}{adjustments}{cows}{l300};
      }
      elsif( $cow->{weight} <  350 ) {
        $price -= $sheet->{pricing}{adjustments}{cows}{l350};
      }
      elsif( $cow->{weight} <  400 ) {
        $price -= $sheet->{pricing}{adjustments}{cows}{l400};
      }
      elsif( $cow->{weight} <  500 ) {
        $price -= $sheet->{pricing}{adjustments}{cows}{l500};
      }

      # adjust price for dairy
      $price -= $sheet->{pricing}{adjustments}{cows}{dairy}
        if $cow->{holstein};

      # adjust price for shellys
      $price -= $sheet->{pricing}{adjustments}{cows}{shelly}
        if $cow->{regrade} eq '8';

      # figure class code and classification
      my ( $class_code, $classification );

      $class_code = $cow->{holstein} ? 6 : 8;

      if( $cow->{regrade} eq '2' || $cow->{regrade} eq '3' ) {
        $classification = 7;
      }
      elsif( $cow->{regrade} eq '4' ) {
        $classification = 8;
      }
      else {
        $classification = 6;
      }

      # insert into the DB
      $db->store_cattle(
        $index + 1,
        @$sheet{qw/ date receiver_no owner origin /},
        $price,
        @$cow{qw/ lot_number holstein regrade weight /},
        $class_code,
        $classification,
      );
    }

    # process bulls
    for my $bull ( @{ $sheet->{bulls} } ) {
      next
        unless $bull->{weight} > 0;

      # adjust to net weight
      # Per phonecall w/ Dawn on 8/1/12, no need to deduct 15 lbs anymore.
      #$bull->{weight} -= 15;

      # figure base price
      my $price = $sheet->{pricing}{bulls}{ $bull->{regrade} };

      # adjust price according to weight
         if( $bull->{weight} <  600 ) {
        $price -= $sheet->{pricing}{adjustments}{bulls}{l600};
      }
      elsif( $bull->{weight} <  800 ) {
        $price -= $sheet->{pricing}{adjustments}{bulls}{l800};
      }
      elsif( $bull->{weight} <  1000 ) {
        $price -= $sheet->{pricing}{adjustments}{bulls}{l1000};
      }

      # adjust price for dairy
      $price -= $sheet->{pricing}{adjustments}{bulls}{dairy}
        if $bull->{holstein};

      # adjust price for shellys
      $price -= $sheet->{pricing}{adjustments}{bulls}{shelly}
        if $bull->{regrade} eq '8';

      # figure class code and classification
      my ( $class_code, $classification ) = ( 9, 9 );

      # insert into the DB
      $db->store_cattle(
        $index + 1,
        @$sheet{qw/ date receiver_no owner origin /},
        $price,
        @$bull{qw/ lot_number holstein regrade weight /},
        $class_code,
        $classification,
      );
    }

  }
}


#
# Lonestar::Workbook
#
BEGIN {
  package Lonestar::Workbook;

  use base qw( Class::Accessor::Faster );

  __PACKAGE__->mk_accessors(qw/ worksheets /);

  sub new { $_[0]->SUPER::new({ worksheets => [ ] }) }

  sub worksheet {
    my ( $self, $index ) = @_;

    $self->worksheets->[$index] = { }
      unless defined $self->worksheets->[$index];

    return $self->worksheets->[$index];
  }
}


#
# Lonestar::DB
#
BEGIN {
  package Lonestar::DB;

  use base qw( Class::Accessor::Faster );

  __PACKAGE__->mk_accessors(qw/ _dbr _store /);
  __PACKAGE__->mk_accessors(qw/ dsn username password /);

  sub new { my $class = shift; $class->SUPER::new({ %{ $_[0] }, _store => { } }) }

  sub connect {
    my ( $self ) = @_;

    my $dbr = DBI->connect(
      $self->dsn, $self->username, $self->password,
      {
        AutoCommit => 0,
        RaiseError => 1,
        PrintError => 0,
      }
    );

    my $sql = do {
      my @fields = qw/
        worksheet
        kill_date
        usda_lot
        owner
        state
        cwt
        lot_number
        holstein
        regrade
        hot_wt
        class_code
        classification
      /;

        q/INSERT INTO lonestar_cattle (/
      . join( ', ', @fields )
      . q/) VALUES (/
      . join( ', ', ('?') x @fields )
      . q/)/
    };

    $self->_store->{cattle} = $dbr->prepare( $sql );

    $self->_dbr( $dbr );
  }

  sub disconnect {
    my ( $self ) = @_;

    $self->_dbr->commit;
    $self->_dbr->disconnect;
  }

  sub store_cattle {
    my ( $self, @args ) = @_;
    $self->_store->{cattle}->execute( @args );
  }
}


#
# Lonestar::Progress
#
BEGIN {
  package Lonestar::Progress;

  use base qw( Class::Accessor::Faster );

  __PACKAGE__->mk_accessors(qw/ _last _soft_last /);
  __PACKAGE__->mk_accessors(qw/ file hard_slots soft_slots soft_limit /);

  sub new { my $class = shift; $class->SUPER::new({ %{ $_[0] }, _soft_last => -1 }) }

  sub hard_limit { my ( $self ) = @_; 100 - $self->soft_limit }

  sub update {
    my ( $self, $percentage ) = @_;

    eval {
      open my $fh, '>', $self->file
        or die $self->file, ": open(): $!";

      print { $fh } "$percentage\n";

      close $fh
        or die $self->file, ": close(): $!";
    }

    # ignore errors
  }

  sub _percentage {
    my ( $self, $type, $current ) = @_;

    my $slots = do { my $method = "${type}_slots"; $self->$method };
    my $limit = do { my $method = "${type}_limit"; $self->$method };

    my $percentage = $current < $slots
      ? int( ( $current / $slots ) * $limit )
      : $limit
    ;

    $percentage = $limit - 1
      if $percentage >= $limit;

    return $percentage;
  }

  sub soft_update {
    my ( $self, $current ) = @_;

    return
      if $current == $self->_soft_last;
    $self->_soft_last( $current );

    $self->update( $self->_percentage( 'soft', $current ) );
  }

  sub hard_update {
    my ( $self, $current ) = @_;

    $self->update( $self->_percentage( 'hard', $current ) + $self->soft_limit );
  }
}