#!/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;

##</code><code>##

,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,,,,Prepared By:,rep,,Quote Name:,IT NAS Refresh Celerras NS80 Only Configs,,,,
,,,,,,Quote Status:,Draft,,Quote #:,1001894004 Version 17 of 17,,,,
,,,,,,Operating Unit:,,,Approved Date:,,,,,
,,,,,,Catalyst Order Type:,,,System Date:,05-Feb-2009,,,,
, ,,,,,GSA Deal?,,,Contract:,,,,,
,,,,,,Customer Selling Relationship:,,,,,,,,
,,,,,,,,,,,,,,
Sold To:,,,Bill To:,Ship To:,,,Install:,,,,Oppty name:, IT SJ Celerra Refresh,,
Customer,,,Customer,Customer,,,,,,,Oppty #:,0,LINKED,
0,,,0,0,,,,,,,Customer Agreement:,,,
,,,,,,,,,,,PO #:,,,
,,,,,,,,,,,Enduser PO#:,,,
,,,,,,,,,,,,,,
Line #,QTY,Product ID,Description,Units,"Total List Price
(USD)",Discount,"Total Deal Price
(USD)","REP Floor
(USD)","DM Floor
(USD)","Cost
(USD)",Margin,"Maint LP Annual
(USD)",,Floor Break
,,,,,,,,,,,,,,
,,IT SJC-12 NAS Refesh NS80 12TB Mirrored Usable (sjc12-0324/1853/3402),,,,,,,,,,,,
,,,,,,,,,,,,,,
,,SYSTEM,PSI FOR NS80,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
1,1,NS80CDME65,NS80 INT ENCL WITH 2 XB65 FACTORY,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
2,1,RACK-40U-60,40U COMMON RACK - 4 PDP,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
3,7,NS-4PDAE,NS INT EXP DAE,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
4,1,NS80-CS,NS80 CONTROL STATION FACTORY  PRIMARY,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
5,1,NS80-CS2,NS80 CONTROL STATION FACTORY  2ND,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
6,1,NS80-DME0,NS80 ENCLOSURE FACTORY,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
7,1,NS80-AUX,NS80 ARRAY RACK REQ,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
8,2,NS80CDM1-4C,NS80 XB65: 4GB4GB MIM;6CU+2OP GIGE+1X10 GIGE FAC,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
9,1,V-NS807315K,VAULT PACK NS80 73GB 15K 4GB DRIVES QTY 5,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
10,90,NS-4G10-300,300GB 10K 520BPS 5/12V 4GB FC 16MB -R,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
11,4,NS-4G10-300HS,300GB 10K 520BPS 5/12V 4GB FC 16MB -R HS,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
12,1,PW40U-60-US,RACK-40U-60 PWR CORD US,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
13,1,NS80-DCD,DOC & CD: NS80,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
14,2,MODEM-US,UNITED STATES MODEM,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
,,,,,,,,,,,,,,
,,,Hardware Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
1,3,NS80C-CIFS-L,CELERRA NS80C CIFS LIC,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,0,0 
,,,,,,,,,,,,,,
,,,Software Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,Configuration Sub-total,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,Hardware Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,Software Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,Services Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,,,
,,,,,,,,,,,,,,
,,,Maintenance and Warranty Upgrade Sub-total,,$ 0 ,0%,$ 0 ,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,Configuration Total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,IT SJC-K NAS Refresh CCO NS80 1 of 3 3TB Mirrored Usable (sjck-nsg2268),,,,,,,,,,,,
,,,,,,,,,,,,,,
,,SYSTEM,PSI FOR NS80,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
1,1,NS80CDME65,NS80 INT ENCL WITH 2 XB65 FACTORY,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
2,1,RACK-40U-60,40U COMMON RACK - 4 PDP,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
3,2,NS-4PDAE,NS INT EXP DAE,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
4,1,NS80-CS,NS80 CONTROL STATION FACTORY  PRIMARY,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
5,1,NS80-CS2,NS80 CONTROL STATION FACTORY  2ND,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
6,1,NS80-DME0,NS80 ENCLOSURE FACTORY,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
7,1,NS80-AUX,NS80 ARRAY RACK REQ,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
8,1,V-NS807315K,VAULT PACK NS80 73GB 15K 4GB DRIVES QTY 5,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
9,24,NS-4G10-300,300GB 10K 520BPS 5/12V 4GB FC 16MB -R,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
10,1,NS-4G10-300HS,300GB 10K 520BPS 5/12V 4GB FC 16MB -R HS,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
11,1,PW40U-60-US,RACK-40U-60 PWR CORD US,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
12,1,NS80-DCD,DOC & CD: NS80,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
13,2,MODEM-US,UNITED STATES MODEM,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
,,,,,,,,,,,,,,
,,,Hardware Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
1,1,NS80C-CIFS-L,CELERRA NS80C CIFS LIC,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
,,,,,,,,,,,,,,
,,,Software Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,Configuration Sub-total,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,Hardware Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,Software Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,Services Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,,,
,,,,,,,,,,,,,,
,,,Maintenance and Warranty Upgrade Sub-total,,$ 0 ,0%,$ 0 ,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,Configuration Total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,IT SJC-K NAS Refresh CCO NS80 2 of 3 3TB Mirrored Usable (sjck-nsg1735),,,,,,,,,,,,
,,,,,,,,,,,,,,
,,SYSTEM,PSI FOR NS80,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
1,1,NS80CDME65,NS80 INT ENCL WITH 2 XB65 FACTORY,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
2,1,RACK-40U-60,40U COMMON RACK - 4 PDP,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
3,2,NS-4PDAE,NS INT EXP DAE,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
4,1,NS80-CS,NS80 CONTROL STATION FACTORY  PRIMARY,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
5,1,NS80-CS2,NS80 CONTROL STATION FACTORY  2ND,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
6,1,NS80-DME0,NS80 ENCLOSURE FACTORY,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
7,1,NS80-AUX,NS80 ARRAY RACK REQ,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
8,1,V-NS807315K,VAULT PACK NS80 73GB 15K 4GB DRIVES QTY 5,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
9,24,NS-4G10-300,300GB 10K 520BPS 5/12V 4GB FC 16MB -R,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
10,1,NS-4G10-300HS,300GB 10K 520BPS 5/12V 4GB FC 16MB -R HS,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
11,1,PW40U-60-US,RACK-40U-60 PWR CORD US,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
12,1,NS80-DCD,DOC & CD: NS80,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
13,2,MODEM-US,UNITED STATES MODEM,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
,,,,,,,,,,,,,,
,,,Hardware Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
1,1,NS80C-CIFS-L,CELERRA NS80C CIFS LIC,EA,0 ,0%,0 ,0 ,0 ,0 ,0.0%,0 ,,0 
,,,,,,,,,,,,,,
,,,Software Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,Configuration Sub-total,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,Hardware Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,Software Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,Services Sub-total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,,,
,,,,,,,,,,,,,,
,,,Maintenance and Warranty Upgrade Sub-total,,$ 0 ,0%,$ 0 ,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,Configuration Total,,$ 0 ,0%,$ 0 ,$ 0 ,$ 0 ,$ 0 ,0.0%,$ 0 ,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
Note:,,,,,,,,,,,,,,
"Booking Value includes Professional Services, the MISC model, and booking redirects.",,,,,,,,,,,,,,
Software Content Percent calculation is ((SW/(HW+SW))*100.,,,,,,,,,,,,,,
"Gross Cost and Gross Margin calculations include Professional Services, but exclude Maintenance and the MISC model.",,,,,,,,,,,,,,
"Field Product Cost and Field Product Margin calculations exclude Professional Services, Maintenance and the MISC model.",,,,,,,,,,,,,,
,,,,,,,,,,,,,,