#!/usr/bin/perl

#Perl script to extract site data from Synergy and insert into mysql smdb DATABASE bom TABLE

#mysql> desc bom;
#+---------+--------------+------+-----+---------+-------+
#| Field   | Type         | Null | Key | Default | Extra |
#+---------+--------------+------+-----+---------+-------+
#| indate  | date         | YES  |     | NULL    |       |
#| dedate  | date         | YES  |     | NULL    |       |
#| de      | int(11)      | NO   | MUL | NULL    |       |
#| op      | int(11)      | NO   | MUL | NULL    |       |
#| so      | int(11)      | YES  | MUL | NULL    |       |
#| serial  | char(20)     | YES  |     | NULL    |       |
#| line    | int(11)      | YES  |     | NULL    |       |
#| qty     | int(11)      | YES  |     | NULL    |       |
#| model   | char(30)     | YES  | MUL | NULL    |       |
#| mdesc   | char(50)     | YES  |     | NULL    |       |
#| unit    | char(5)      | YES  |     | EA      |       |
#| listp   | int(11)      | YES  |     | NULL    |       |
#| discp   | int(11)      | YES  |     | NULL    |       |
#| deal    | int(11)      | YES  |     | NULL    |       |
#| repfl   | int(11)      | YES  |     | NULL    |       |
#| dmfl    | int(11)      | YES  |     | NULL    |       |
#| cost    | int(11)      | YES  |     | NULL    |       |
#| marg    | int(11)      | YES  |     | NULL    |       |
#| maint   | int(11)      | YES  |     | NULL    |       |
#| comment | varchar(125) | YES  |     | NULL    |       |
#| ucost   | int(11)      | YES  |     | NULL    |       |
#| dealn   | char(12)     | YES  |     | NULL    |       |
#| bu      | char(20)     | YES  |     | NULL    |       |
#| upgrade | char(1)      | YES  |     | NULL    |       |
#+---------+--------------+------+-----+---------+-------+





use strict;
use warnings;
use DBI;
use DBD::mysql;
use Spreadsheet::ParseExcel;
use Time::JulianDay;

# DBI configuration variables
my $platform = "mysql";
my $database = "smdb";
my $host = "localhost";
my $port = "3306";
my $tablename = "site";
my $user = "root";
my $pw = "password";

#Data source name
my $dsn = "dbi:mysql:smdb:localhost:3306";

# PERL DBI connection handle
my $dbh = DBI->connect($dsn, $user, $pw)or die "Unable to connect: $DBI::errstr\n";

#xls file to be parsed (You'll need to add your path information here based on where you put the attached spreadsheet.)
my $file = "1001894004.xls";

#global variables

our $row;
our $de;
our @de;
our $de0;
our $version;
our $of;
our $den;
our $den2;
our $year;
our $month;
our $day;
our $so;
our $op;
our $serial;
our $line;
our $qty;
our $model;
our $mdesc;
our $unit;
our $listp;
our $discp;
our $deal;
our $repfl;
our $dmfl;
our $cost;
our $marg;
our $maint;
our $bu;
our $comment;
our $dealn;
our $upgrade;
our $dedate;
our $dedate0;
our $dedate1;

my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($file)or die "Unable to open $file\n";

#locate columns in the spreadsheet from which we want to extract data
my $sheet =$workbook->{Worksheet}[0];

#iterate through spreadsheet rows and extract site.siteid, site.name, site.address, site.city, site.state, site.zip

	$de0 = $sheet->{Cells}[4][10]->{Val};
	@de = split / /, $de0;
 	($de, $version, $den, $of, $den2) = @de;

	 if ($sheet->{Cells}[7][11]->{Val} eq "") {$so=0} else {$so = $sheet->{Cells}[7][11]->{Val};}
	 if ($sheet->{Cells}[11][12]->{Val} eq "") {$op=0} else {$op = $sheet->{Cells}[11][12]->{Val};}
	 $dedate = $sheet->{Cells}[6][10]->{Val};

#my problem area starts here

foreach my $row ($sheet->{MinRow}+18 .. $sheet->{MaxRow}) {	

	if ( $sheet->{Cells}[$row][2]->{Val} = "SYSTEM" )  { #I am trying to step through each row until I encounter the string "SYSTEM" in the 3rd cell (identified as column 2 in the module). It looks like I get a hit because the output indicates that the rest of the iterations are taking place.

	$comment = $sheet->{Cells}[$row-2][2]->{Val}; #however, the $comment variable is getting feed with the string "SYSTEM" instead of the longer string in the cell two rows above. I am attempting to pull that value with my $row-2 pointer. That approach has worked well in other scripts so I am not sure why it is failing here.
	
	if ( $sheet->{Cells}[$row][0]->{Val} =~ /(^\d+)/ ) {
	
	 $line = $sheet->{Cells}[$row][0]->{Val};
	 $qty = $sheet->{Cells}[$row][1]->{Val};

	 $model = $sheet->{Cells}[$row][2]->{Val}; # another strange outcome is the $model variable is also getting filled with "SYSTEM" instead of the value in the current row. That worked properly until I added the if "SYSTEM" statement.

	 $mdesc = $sheet->{Cells}[$row][3]->{Val};
	 $unit = $sheet->{Cells}[$row][4]->{Val};
	 $listp = $sheet->{Cells}[$row][5]->{Val};
	 $discp = $sheet->{Cells}[$row][6]->{Val};
	 $deal = $sheet->{Cells}[$row][7]->{Val};
	 $repfl = $sheet->{Cells}[$row][8]->{Val};
	 $dmfl = $sheet->{Cells}[$row][9]->{Val};
	 $cost = $sheet->{Cells}[$row][10]->{Val};
	 $marg = $sheet->{Cells}[$row][11]->{Val};
	 $maint = $sheet->{Cells}[$row][12]->{Val};
	 if ($sheet->{Cells}[$row][16]->{Val} eq "") {$upgrade=0} else {$upgrade = $sheet->{Cells}[$row][16]->{Val};}
	 if ($sheet->{Cells}[$row][17]->{Val} eq "") {$bu=0} else {$bu = $sheet->{Cells}[$row][17]->{Val};}
     if ($sheet->{Cells}[$row][18]->{Val} eq "") {$dealn=0} else {$dealn= $sheet->{Cells}[$row][18]->{Val};}
	
	 $dedate0 = $dedate + julian_day(1900, 1, 0);
	 ($year, $month, $day) = inverse_julian_day($dedate0);
	 $dedate1 = join '-', $year,$month,$day;	
	
#$dbh->do("insert into bom		  				
#(indate,dedate,de,op,so,serial,line,qty,model,mdesc,unit,listp,discp,deal,repfl,dmfl,cost,marg,maint,bu,comment,dealn,upgrade) 
#	values
#(CURDATE(),\'$dedate1\',$de,$op,$so,\'$serial\',$line,$qty,\'$model\',\'$mdesc\',\'$unit\',$listp,$discp,$deal,$repfl,$dmfl,$cost,$marg,$maint,\'$bu\',\'$comment\',\'$dealn\',$upgrade)");

#print captured output
	print "DE: $de\n";
	print "DEDATE: $dedate1\n";
	print "OP# $op\n";
	print "SO# $so\n";
	
	print "0 LINE# $line\n";
	print "1 QTY: $qty\n";
	print "2 MODEL: $model\n";
	print "3 DESC: $mdesc\n";
	print "4 UNIT: $unit\n";
	print "5 LIST PRICE: $listp\n";
	print "6 DISCOUNT: $discp\n";
	print "7 SELL PRICE: $deal\n";
	print "8 REP FLOOR: $repfl\n";
	print "9 DM FLOOR: $dmfl\n";
	print "10 COST: $cost\n";
	print "11 MARGIN: $marg\n";
	print "12 MAINTENANCE: $maint\n";
	print "13 COMMENT: $comment\n";
	print "14 SERIAL# $serial\n";
	print "15 BU: $bu\n";
	print "16 UPGRADE? $upgrade\n";
	print "17 DEAL# $dealn\n";
	print "+++++++++++++++++++++++++++++++++++\n";
	}{next;}
	}
}
exit;