#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseExcel::Simple;
use DBI;

#  Open directory and get list of all Excel files
opendir(DIR , '/www/cgi-bin/data');
my @files = grep { /\.xls$/ } readdir(DIR);
closedir(DIR);

#  File to store all parsed Excel data
my $storeFile = '/www/cgi-bin/data/store.dat';

open(STORE,'>',"$storeFile") || die("Cannot Open File");

#  DB Column Headings
my @header;

foreach my $file ( @files ) {

  my $xls = Spreadsheet::ParseExcel::Simple->read('/www/cgi-bin/data/' . $file);

  foreach my $sheet ($xls->sheets) {
     while ($sheet->has_data) {
       my @data = $sheet->next_row;

           #  If first column of a row is 'Manu', use this as the header
           if ($data[0] eq 'Manu') { @header = ( @data[0..26], 'compressedColumnName') }

           #  If first column of a row is 'Dell', skip it.
           if ($data[0] ne 'Dell') { next }

           #  Save first 27 elements of the list.
           my @info = @data[0..26];

           #  Delete the first 27 elements of the list.
           splice @data,0,27;

           #  Join all remaining list elements into a string
           my $remaining = join(':', @data);

           #  Join them together and delimit list items with a '^'
           my @out = ( @info, $remaining );
           my $out = join('^', @out);

           #  Save it to the temp STORE file.
           print STORE "$out\n";
     }
  }
}

close STORE;

my $database = "db";
my $db_server = "localhost";
my $user = "user";
my $password = "pass";

#  Connect to database
my $dbh = DBI->connect("DBI:mysql:$database:$db_server",$user,$password);

$dbh->do("DROP TABLE `table`");


#  Format DB Headers, strip out all characters except letters.
my @newlist;
foreach my $head (@header) {
  $head =~ s/[^a-zA-Z]//g;
  push(@newlist, $head);
}

my $SQL = "CREATE TABLE `table` (".
     join( " varchar(255),", @newlist ) . " varchar(255))";

$dbh->do($SQL);

$SQL = "LOAD DATA LOCAL INFILE '$storeFile' INTO TABLE `table` FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n' ";

$dbh->do($SQL);


print "Excel Injected " . scalar localtime() . "\n";

my $excelLog = '/www/cgi-bin/tvw/excel.log';

open(LOG,'>',"$excelLog") || die("Cannot Open File");
print LOG "<font color=\"lime\"><b>Updated " . scalar localtime() . "</b></font>\n";
close LOG;

exit;