#!/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
my $file = "/QAR1001894004v17.xls";

#global variables

our $row;
our $rw;
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};
# the new foreach loop that returns the last SYSTEM and $comment in the spreadsheet (not exactly what I was hoping for)
    
foreach my $row ($sheet->{MinRow}+18..$sheet->{MaxRow}){

    if ($sheet->{Cells}[$row][2]->{Val} eq "SYSTEM") {$comment = $sheet->{Cells}[$row-2][2]->{Val};}
    if ($sheet->{Cells}[$row][3]->{Val} eq "UPGRADE SOLUTION") {$upgrade=1} else {$upgrade=0};
}

foreach my $rw ($sheet->{MinRow}+18 .. $sheet->{MaxRow}) {    
    
    if ( $sheet->{Cells}[$rw][0]->{Val} =~ /(^\d+)/ ) {
    
     $line = $sheet->{Cells}[$rw][0]->{Val};
     $qty = $sheet->{Cells}[$rw][1]->{Val};
     $model = $sheet->{Cells}[$rw][2]->{Val};
     $mdesc = $sheet->{Cells}[$rw][3]->{Val};
     $unit = $sheet->{Cells}[$rw][4]->{Val};
     $listp = $sheet->{Cells}[$rw][5]->{Val};
     $discp = $sheet->{Cells}[$rw][6]->{Val};
     $deal = $sheet->{Cells}[$rw][7]->{Val};
     $repfl = $sheet->{Cells}[$rw][8]->{Val};
     $dmfl = $sheet->{Cells}[$rw][9]->{Val};
     $cost = $sheet->{Cells}[$rw][10]->{Val};
     $marg = $sheet->{Cells}[$rw][11]->{Val};
     $maint = $sheet->{Cells}[$rw][12]->{Val};
     if ($sheet->{Cells}[$rw][17]->{Val} eq "") {$bu=0} else {$bu = $sheet->{Cells}[$rw][17]->{Val};}
     if ($sheet->{Cells}[$rw][18]->{Val} eq "") {$dealn=0} else {$dealn= $sheet->{Cells}[$rw][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;