#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Spreadsheet::ParseExcel::Simple;
my $yesno;
my $statement;

my $dir = '/home/ubuntu/sipdownloads/';
my $dbh = DBI->connect("dbi:mysql:lightspeed", "lightspeed", "webstore")
    or die $DBI::errstr;

my $sth = $dbh->prepare("truncate table siptemp");
$sth->execute();


    opendir(DIR, $dir) or die $!;
    while (my $file = readdir(DIR)) {

        # We only want files
        next unless (-f "$dir/$file");

        # Use a regular expression to find excell files
        next unless ($file =~ m/\.xls$/);

        print "$dir$file\n";

	my $xls = Spreadsheet::ParseExcel::Simple->read("$dir$file");
		foreach my $sheet ($xls->sheets) {
			my $counter = 0;
			while ($sheet->has_data) {
				my @data = $sheet->next_row;

				if ($counter < 7) {
					$counter++;
				} elsif ($counter == 8){
					$counter++;
				} else {
					s/,/./g for @data;
					s/\n/ /g for @data;
					s/ USD//g for @data;
					if ($data[12] eq 'Yes') {$yesno =1;} else {$yesno =0;}
					my $statement = "insert into siptemp (article, description, price, cost, cost5, instock) values (\'$data[0]\',\'$data[2]\',\'$data[4]\',\'$data[6]\',\'$data[8]\',\'$yesno\')";
					$sth = $dbh->prepare("$statement");
					$sth->execute();
					$counter++;
					}
				}
			}
    }

$sth = $dbh->prepare("truncate table sip");
$sth->execute();
$sth = $dbh->prepare("insert into sip select distinct * from siptemp");
$sth->execute();

$dbh->disconnect
    or warn "Disconnection failed: $DBI::errstr\n";

closedir(DIR);

 exit 0;