#!/usr/bin/perl

use warnings;
use strict;
$|++;
use DBI;
use Data::Dumper;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::Utility qw(xls2csv);
use File::Find;

my $dbh = DBI->connect('dbi:mysql:harryfox;host=172.16.1.233', 'root', '', { RaiseError => 1, AutoCommit => 1}) or die $DBI::errstr;

my @files;

find sub {
    return unless -f;
    return unless $_ !~ /^HFA/;
    return unless $_ =~ /^03.*xls$/;
    #print "$File::Find::name\n";
    push @files, $File::Find::name;
}, "/home/kevin/harryfox/royalties/jeannie";
                                                                                
#map { print $_, "\n"; put_in_db($_); } @files;

foreach ( @files ) {
	print $_, "\n";
	put_in_db($_);
}


sub put_in_db {

	my ($spreadsheet) = @_;
	$spreadsheet =~ /jeannie\/(.*)ITMSTS/;
	my $file = lc $1;
	if ( $file =~ /^9/ ) { 
		$file = '19' . $file;
	} else {
		$file = '20' . $file;
	}

	print "file is $file\n";
=cut
my $e = new Spreadsheet::ParseExcel;
my $oBook = $e->Parse("$spreadsheet");
my $maxRow;
for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) {
        my $oWkS = $oBook->{Worksheet}[$iSheet];
        $maxRow = $oWkS->{MaxRow};
}
$maxRow--;
$e = undef;
$oBook = undef;
=cut
#print "max row: $maxRow\n";
#my $get = "A1:F$maxRow";
my $get = "A1:F12900";
my $content = xls2csv($spreadsheet, $get);
                                                                                
#print $content;
                                                                                
my @lines = split/\n/, $content;

undef $content;

my $sth = $dbh->prepare_cached("insert into roy_itmsts$file (itemnum,itemdesc,itemdesc2,qty_sold,qty_retn,qty_net) VALUES(?,?,?,?,?,?)");

print "Loading...\n";
foreach ( @lines ) {
	my @tmp = split/\|/, $_;
	map { s/^\s+//g; s/\s+$//g; } @tmp;
	#print "$tmp[0] $tmp[1] $tmp[2] $tmp[3] $tmp[4] $tmp[5]\n";
	$sth->execute(@tmp[0..5]);

}
print "Finished Loading...\n";
$sth->finish();

undef @lines;
}