#!/usr/bin/perl

#Perl script to print a Deal Sheet Report.

use strict;
use warnings;
use DBI;
use DBD::mysql;
use Spreadsheet::WriteExcel;

# DBI configuration variables
my $platform = "mysql";
my $database = "smdb";
my $host = "Sinn";
my $port = "3306";
my $tablename = "site";
my $user = "user";
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";


#global variables
our $quote="1001981933";
our $version="1";
our $row;
our $sth;
our $dedate;
our $de;
our $op;
our $customer;
our $dealn;
our $cco;
our $ebv;
our $fcost;
our $fmarg;
our $bookad;
our $tir;
our $swmaint;
our $hwmaint;
our $svsums;
our $svcost;
our $svmarg;
our $floor;
our $aref;



my $workbook  = Spreadsheet::WriteExcel->new('/lebensraum/Transfer/perl/BPW/pq_dealsheet_summary.xls');
die "Problems creating new Excel file: $!" unless defined $workbook;

#Create new spreadsheet report
my $worksheet = $workbook->add_worksheet();

	$worksheet->set_column(0, 0,  11); # Column  A   width set to 11
	$worksheet->set_column(1, 1,  11); # Column  B   width set to 11
	$worksheet->set_column(2, 2,  6); # Column  C   width set to 6
	$worksheet->set_column(3, 3,  11); # Column  D   width set to 11
	$worksheet->set_column(4, 4,  11); # Column  E   width set to 11
	$worksheet->set_column(5, 5,  22); # Column  F   width set to 22
	$worksheet->set_column(6, 6,  35); # Column  G   width set to 35
	$worksheet->set_column(7, 7,  50); # Column  H   width set to 50
	$worksheet->set_column(8, 8,  14); # Column  I   width set to 14
	$worksheet->set_column(9, 9,  14); # Column  J   width set to 14
	$worksheet->set_column(10, 10,  14); # Column  K   width set to 14
	$worksheet->set_column(11, 11,  14); # Column  L   width set to 14
	$worksheet->set_column(12, 12,  14); # Column  M   width set to 14
	$worksheet->set_column(13, 13,  14); # Column  N   width set to 14
	$worksheet->set_column(14, 14,  14); # Column  O   width set to 14
	$worksheet->set_column(15, 15,  14); # Column  P   width set to 14
	$worksheet->set_column(16, 16,  14); # Column  Q   width set to 14
	$worksheet->set_column(17, 17,  14); # Column  R   width set to 14
	$worksheet->set_column(18, 18,  14); # Column  S   width set to 14
	$worksheet->set_column(19, 19,  20); # Column  S   width set to 20


	my $header = $workbook->add_format(
           bg_color => 43, # index for pale yellow
           center_across  => 1, #center
           size  => 12, #set font size to 12
           bold  => 1, #set bold on
                      );

	my $headerb = $workbook->add_format(
           bg_color => 51, # index for orange
           center_across  => 1,
           size  => 12,
           bold  => 1,
                      );

	my $alignc = $workbook->add_format(); #simple center only format
	$alignc->set_align('center');
	$alignc->set_align('vcenter');

	my $alignl = $workbook->add_format(); #simple align left format
	$alignl->set_align('left');
	$alignl->set_align('vcenter');

	my $alignld = $workbook->add_format(); #align left & format as $
	$alignld->set_num_format('$#,##0');
	$alignld->set_align('left');
	$alignld->set_align('vcenter');

	my $aligncd = $workbook->add_format(); #align center and format for date
	$aligncd->set_num_format('mm/dd/yyyy');
	$aligncd->set_align('center');
	$aligncd->set_align('vcenter');

	my $alignPerc = $workbook->add_format(); #align center and format for percent
	$alignPerc->set_num_format('0.00%');
	$alignPerc->set_align('center');
	$alignPerc->set_align('vcenter');

	

	my $yel = $workbook->add_format(
           bg_color => 43, # index for pale yellow 
                      );

	my $ora = $workbook->add_format(
           bg_color => 51, # index for orange
                      );
	
	my $margformula= $worksheet->store_formula('=1-K/J'); #formula experiment

	$worksheet->write_blank(0, 0, $ora);#format row 0 orange
	$worksheet->write_blank(0, 1, $ora);
	$worksheet->write_blank(0, 2, $ora);
	$worksheet->write_blank(0, 3, $ora);
	$worksheet->write_blank(0, 4, $ora);
	$worksheet->write_blank(0, 5, $ora);
	$worksheet->write_string(0, 6, "Deal Sheet Summary",$headerb);
	$worksheet->write_blank(0, 7, $ora);
	$worksheet->write_blank(0, 8, $ora);
	$worksheet->write_blank(0, 9, $ora);
	$worksheet->write_blank(0, 10, $ora);
	$worksheet->write_blank(0, 11, $ora);
	$worksheet->write_blank(0, 12, $ora);
	$worksheet->write_blank(0, 13, $ora);
	$worksheet->write_blank(0, 14, $ora);
	$worksheet->write_blank(0, 15, $ora);
	$worksheet->write_blank(0, 16, $ora);
	$worksheet->write_blank(0, 17, $ora);
	$worksheet->write_blank(0, 18, $ora);
	$worksheet->write_blank(0, 19, $ora);
	$worksheet->write_string(0, 6, "Deal Sheet Summary",$headerb); #define column headers
	$worksheet->write_string(1, 0, "DE Date",$header);
	$worksheet->write_string(1, 1, "DE #",$header);
	$worksheet->write_string(1, 2, "Ver",$header);
	$worksheet->write_string(1, 3, "OP #",$header);
	$worksheet->write_string(1, 4, "SO #",$header);
	$worksheet->write_string(1, 5, "Rep",$header);
	$worksheet->write_string(1, 6, "Customer",$header);
	$worksheet->write_string(1, 7, "Deal Name",$header);
	$worksheet->write_string(1, 8, "Sell",$header);
	$worksheet->write_string(1, 9, "Book Value",$header);
	$worksheet->write_string(1, 10, "Field Cost",$header);
	$worksheet->write_string(1, 12, "Field Margin",$header);
	$worksheet->write_string(1, 11, "Book Adjust",$header);
	$worksheet->write_string(1, 13, "Trade In",$header);
	$worksheet->write_string(1, 14, "SW Maint",$header);
	$worksheet->write_string(1, 15, "HW Maint",$header);
	$worksheet->write_string(1, 16, "Services Sell",$header);
	$worksheet->write_string(1, 17, "Services Cost",$header);
	$worksheet->write_string(1, 18, "Services Margin",$header);
	$worksheet->write_string(1, 19, "Floor",$header);

#select columns from mysql quote table
$sth= $dbh->prepare("select dedate,de,den,op,so,rep,customer,dealn,cco,ebv,fcost,fmarg,bookad,tir,swmaint,hwmaint,svsums,svcost,svmarg,floor
from quote");		  				

$sth->execute();

#capture output into array and variablize the results 
 while ( my $aref = $sth->fetchrow_arrayref )
{
 my ($dedate,$de,$den,$op,$so,$rep,$customer,$dealn,$cco,$ebv,$fcost,$fmarg,$bookad,$tir,$swmaint,$hwmaint,$svsums,$svcost,$svmarg,$floor) = @$aref;

#write data to spreadsheet row by row 
	$worksheet->write_date_time($row+2, 0, $dedate,$aligncd);
	$worksheet->write_number($row+2, 1, $de,$alignc);
	$worksheet->write_number($row+2, 2, $den,$alignc);
	$worksheet->write_number($row+2, 3, $op,$alignc);
	$worksheet->write_number($row+2, 4, $so,$alignc);
	$worksheet->write_string($row+2, 5, $rep,$alignl);
	$worksheet->write_string($row+2, 6, $customer,$alignl);
	$worksheet->write_string($row+2, 7, $dealn,$alignl);
	$worksheet->write_number($row+2, 8, $cco,$alignld);
 	$worksheet->write_number($row+2, 9, $ebv,$alignld);
	$worksheet->write_number($row+2, 10, $fcost,$alignld);
	$worksheet->write_formula($row+2, 12, '=1-(K.($row+2)/J.($row+2))',$alignPerc); #the code I am having trouble with
	$worksheet->write_number($row+2, 11, $bookad,$alignld);
	$worksheet->write_number($row+2, 13, $tir,$alignld);
	$worksheet->write_number($row+2, 14, $swmaint,$alignld);
	$worksheet->write_number($row+2, 15, $hwmaint,$alignld);
	$worksheet->write_number($row+2, 16, $svsums,$alignld);
	$worksheet->write_number($row+2, 17, $svcost,$alignld);
	$worksheet->write_number($row+2, 18, $svmarg,$alignPerc);
	$worksheet->write_string($row+2, 19, $floor,$alignc);
	$row++;
} 

$workbook->close();

exit;