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

##</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
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)"
												
		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 
2	1	RACK-40U-60	40U COMMON RACK - 4 PDP	EA	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 
4	1	NS80-CS	NS80 CONTROL STATION FACTORY  PRIMARY	EA	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 
6	1	NS80-DME0	NS80 ENCLOSURE FACTORY	EA	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 
8	2	NS80CDM1-4C	NS80 XB65: 4GB4GB MIM;6CU+2OP GIGE+1X10 GIGE FAC	EA	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 
10	90	NS-4G10-300	300GB 10K 520BPS 5/12V 4GB FC 16MB -R	EA	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 
12	1	PW40U-60-US	RACK-40U-60 PWR CORD US	EA	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 
14	2	MODEM-US	UNITED STATES MODEM	EA	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 
												
			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 
2	1	RACK-40U-60	40U COMMON RACK - 4 PDP	EA	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 
4	1	NS80-CS	NS80 CONTROL STATION FACTORY  PRIMARY	EA	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 
6	1	NS80-DME0	NS80 ENCLOSURE FACTORY	EA	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 
8	1	V-NS807315K	VAULT PACK NS80 73GB 15K 4GB DRIVES QTY 5	EA	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 
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 
11	1	PW40U-60-US	RACK-40U-60 PWR CORD US	EA	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 
13	2	MODEM-US	UNITED STATES MODEM	EA	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 
												
			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 
2	1	RACK-40U-60	40U COMMON RACK - 4 PDP	EA	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 
4	1	NS80-CS	NS80 CONTROL STATION FACTORY  PRIMARY	EA	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 
6	1	NS80-DME0	NS80 ENCLOSURE FACTORY	EA	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 
8	1	V-NS807315K	VAULT PACK NS80 73GB 15K 4GB DRIVES QTY 5	EA	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 
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 
11	1	PW40U-60-US	RACK-40U-60 PWR CORD US	EA	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 
13	2	MODEM-US	UNITED STATES MODEM	EA	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 
												
			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.