#!/home/devcurpl/biff/local/bin/perl
######################################################################
# BiffSocko
# cbbdat.pl
#
# This program gets data from the metquest database.  We exclude 
# Monday, Saturday, and Sunday data.  See the SQL Statement for 
# details.  Additionally, the program gets the peak times for each day
# and reports only on those
#
# NOTE: this is for 60 minute buckets .. you can change this to 
# 15 minute buckets by alterating the SQL stmt to read _15min where
# it currently says _60min
#
# Format : cbbxls.pl
# date $tasktime $sparetime $queuesize $counter
######################################################################
use DBI;
use Spreadsheet::WriteExcel;


#########################################################
# creat EXCEL worksheet objects and print headers
#########################################################
my $workbook  = Spreadsheet::WriteExcel->new("demo01.xls");
my $worksheet = $workbook->add_worksheet("cbb weekly data");
my $worksheet_prd = $workbook->add_worksheet("PRD");
my $worksheet_uat = $workbook->add_worksheet("UAT");

########################################################
# Chart Stuff isn't working yet
########################################################
#my $chart_worksheet = $workbook->add_worksheet();
#my $chart = $workbook->add_chart_ext('chart01.bin', 'Chart1');
#$chart_worksheet->store_formula('=Sheet1!A1');

my $grformat = $workbook->add_format(bg_color=>'lime');
    $grformat->set_bold();
    #$grformat->set_bg_color('#CCFFCC');
    $grformat->set_align('center');

my $yellowformat = $workbook->add_format(bg_color=>'yellow');
    $yellowformat->set_bold();
    #$grformat->set_bg_color('#CCFFCC');
    $yellowformat->set_align('center');


my $orgformat = $workbook->add_format(bg_color=>'orange');
    $orgformat->set_bold();
    #$orgformat->set_bg_color('#FF9900');
    $orgformat->set_align('center');



########################################################
# Print out PRD data to the worksheet_prd
########################################################
$worksheet_prd->write('A1',"PRD");
@row = ("old","180","1","=C2/C3","=D2*B2");
$worksheet_prd->write("A2", \@row); 
@row = ("new","156","1.85");
$worksheet_prd->write("A3", \@row); 
@row = ("Total","=B3+(B2*D2)");
$worksheet_prd->write("A5", \@row); 
@row = ("demand","230","133");
$worksheet_prd->write("A8", \@row); 
@row = ("1-month","","11.4");
$worksheet_prd->write("A9", \@row); 
@row = ("6-month","","68.5");
$worksheet_prd->write("A10", \@row); 

@row = ("Month","Demand","Headroom","Total");
$worksheet_prd->write("B12", \@row, $orgformat); 
@row = ("","","","Demand");
$worksheet_prd->write("B13", \@row, $orgformat); 

@col = 
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
$worksheet_prd->write('B14', [\@col],$grformat);
@col = 
("=C8","143.971944444444","155.398434444444","166.824924444444","178.251414444444","189.677904444444","201.104394444444","212.530884444444","223.957374444444","235.383864444444","246.810354444444","258.236844444444");
$worksheet_prd->write('C14', [\@col]);
@col = 
("=C10","=D14","=D15","=D16","=D17","=D18","=D19","=5*C9","=4*C9","=3*C9","=2*C9","=C9");
$worksheet_prd->write('D14', [\@col], $yellowformat);
@col = 
("=C14+D14","=C15+D15","=C16+D16","=C17+D17","=C18+D18","=C19+D19","=C20+D20","=C21+D21","=C22+D22","=C23+D23","=C24+D24","=C25+D25");
$worksheet_prd->write('E14', [\@col]);

########################################################
# Print out UAT data to the worksheet_uat
########################################################
$worksheet_uat->write('A1',"UAT");
@row = ("old","180","1","=C2/C3","=D2*B2");
$worksheet_uat->write("A2", \@row);
@row = ("new","156","1.85");
$worksheet_uat->write("A3", \@row);
@row = ("Total","=B3+(B2*D2)");
$worksheet_uat->write("A5", \@row);
@row = ("demand","230","133");
$worksheet_uat->write("A8", \@row);
@row = ("1-month","","11.4");
$worksheet_uat->write("A9", \@row);
@row = ("6-month","","68.5");
$worksheet_uat->write("A10", \@row);

@row = ("Month","Demand","Headroom","Total");
$worksheet_uat->write("B12", \@row, $orgformat); 
@row = ("","","","Demand");
$worksheet_uat->write("B13", \@row, $orgformat); 

@col = 
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
$worksheet_uat->write('B14', [\@col],$grformat);
@col = 
("=C8","143.971944444444","155.398434444444","166.824924444444","178.251414444444","189.677904444444","201.104394444444","212.530884444444","223.957374444444","235.383864444444","246.810354444444","258.236844444444");
$worksheet_uat->write('C14', [\@col]);
@col = 
("=C10","=D14","=D15","=D16","=D17","=D18","=D19","=5*C9","=4*C9","=3*C9","=2*C9","=C9");
$worksheet_uat->write('D14', [\@col], $yellowformat);
@col = 
("=C14+D14","=C15+D15","=C16+D16","=C17+D17","=C18+D18","=C19+D19","=C20+D20","=C21+D21","=C22+D22","=C23+D23","=C24+D24","=C25+D25");
$worksheet_uat->write('E14', [\@col]);


########################################################
# Print out the CBB Data to worksheet
########################################################
$worksheet->write('A1', 'DATE', $format);
$worksheet->write('B1', 'TASKTIME', $format);
$worksheet->write('C1', 'SPARETIME', $format);
$worksheet->write('D1', 'QUEUESIZE', $format);
$worksheet->write('E1', 'COUNTER', $format);
$worksheet->write('G1', 'CALC', $format);
$worksheet->write('H1', 'CALCNUM', $format);
$worksheet->write('J1', 'MONTH', $format);
$worksheet->write('K1', 'CPU', $format);

#########################################################
# set up DSQUERY for Sybase and connect to the database\
#########################################################
$ENV{'DSQUERY'}='BTH_SYT_MRX_1';
if(!($sybaseHandleDBI=DBI->connect("DBI:Sybase:database=metquest",INSTAL, 
INSTALL))){
        print STDERR "can't connect to database\n";
        exit(1);
}

#########################################################
# SQL
#########################################################
$sql="
 SELECT
    convert(datetime,tt.id_time_start) AS 'Date',
    convert(int,tt.id_value) AS 'TaskTime_El',
    convert(int,st.id_value) AS 'SpareTime_El',
    convert(int,qs.id_value) AS 'QueueSize_El'
  FROM metquest.dbo.KPI_DATA qs, metquest.dbo.KPI_DATA st, 
metquest.dbo.KPI_DATA tt
  WHERE (tt.id_param='CBB_TaskTime_El_60min')
    AND (st.id_param='CBB_SpareTime_El_60min')
    AND (qs.id_param='CBB_QueueSize_El_60min')
    AND (tt.id_time_start=st.id_time_start)
    AND (qs.id_time_start=st.id_time_start)
    AND (datename(weekday,tt.id_time_start) != 'Monday')
    AND (datename(weekday,tt.id_time_start) != 'Sunday')
    AND (datename(weekday,tt.id_time_start) != 'Saturday')
    AND ( tt.id_time_start > '1/9/2007')";

$selstmt=$sybaseHandleDBI->prepare($sql);
if(!($selstmt->execute())){
                 print STDERR "did not execute $sql\n";
                 exit(1);
}

#########################################################
# travrse the results of the SQL.  This section
# will basically look for peak elapsed tasktimes
#########################################################
$today="";
$maxday=0;
$flag=1;
# skip the first line of the spreadsheet - header line
$counter=2;
while(my @record = $selstmt->fetchrow()){
                 $date                           = $record[0];
                 $tasktime               =               $record[1];
                 $sparetime              =               $record[2];
                 $queuesize              =               $record[3];

                 @day=split(/\s+/,$date);
                 $dayOfMonth=$day[1];
 
                 if($dayOfMonth ne $today){
                                 $today=$dayOfMonth;
 
 #############################################
                                 # do this to keep from printing the 
                                 # first line
 #############################################
                                 if($flag==1){
                                                 $flag=0;
                                 }else{

                                                 $date           = 
$maxrecord[0];
                                         $tasktime       = $maxrecord[1];
                                         $sparetime      = $maxrecord[2];
                                         $queuesize      = $maxrecord[3];
                                                 $prtcount = $counter -1;
                                                 @row = ("$date", 
"$tasktime", "$sparetime", "$queuesize", "$prtcount");
 $worksheet->write("A$counter", \@row); # Write a row of data
                                                 $counter++;
                                                 $maxday=0;
                                 }
                 }else{
                                 if($maxday < $tasktime){
                                                 @maxrecord=@record;
                                                 $maxday = $tasktime
                                 }


                 }
}

###################################
# ok, the data for the spreadsheet
# has been updated, now it's time
# to do some calculations
###################################
@col = ("bucket 
interval","b","slope","CPUs/day","CPUs/week","CPUs/month","6-month","max 
capacity");
$worksheet->write('G2', [\@col]); 

@col = 
("3600","=B2","=SLOPE(B2:B$prtcount,E2:E$prtcount)","=H4/H2","=4*H5","=4.3*H6","=6*H7","=336*3600");
$worksheet->write('H2', [\@col]);

@col = 
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
$worksheet->write('J2', [\@col]); 

@col = 
("=H3/H2","=K2+H7","=K3+H7","=K4+H7","=K5+H7","=K6+H7","=K7+H7","=K8+H7","=K9+H7","=K10+H7","=K11+H7","=K12+H7");
$worksheet->write('K2', [\@col]); 

$workbook->close();
exit(0);