#!/usr/bin/perl
use strict;
use warnings;
use Time::Piece;
#for testing
my $dest = "C:/Perl-Script/Network Faults/";
# current date/time
my $t = localtime;
my $date = $t->wdayname.' '.$t->dmy('/');
my $time = $t->hms;
# input data
my %hdata = build_hash($dest."RoomName.txt");
my $href = build_sqlData();
my @lrooms=();
foreach my $room (sort keys %{$href}){
#print "room is $room\n";
#print "status is ${$href}{$room}\n";
push @lrooms, $room;
#print "num is @lrooms\n";
}
# output html
open OUT, ">". $dest."ICT Report.html" or die "$!";
print OUT qq(
Bulletin
NewYork Time
Report Date $date $time
);
foreach my $key (sort keys %hdata) {
my $h4 = $key;
$h4 =~ s/[\"\$]//g;
print OUT qq!
$h4
\n!; #how to change width of table here????
my @arrays = @{$hdata{$key}};
for my $i (1..$#arrays) {
print OUT '
';
my @col = split(/,/,$arrays[$i]);
for my $j (0..$#col) {
$col[$j] =~ s/\"Period\"/\"Class\"/g;
$col[$j] =~ s/"//g;
my $bg = "bgcolor=#e0e0e0";
#print @lrooms;
if ($col[$j] ~~ @lrooms) {
$bg = (${$href}{$col[$j]} =~ /Pending/) ? "bgcolor=#FF0000" :
(${$href}{$col[$j]} =~ /InProgress/) ? "bgcolor=#FFFF00" :
"";
}
print OUT "
$col[$j]
"; # how to set the fixed width of cells ???
};
print OUT "
";
};
print OUT "
\n";
}
print OUT "";
close OUT;
#-------------------------------------
# This subroutine split text file to
# a hash of arrays for data processing
# -------------------------------------
sub build_hash{
my $infile = shift;
my %data;
my $last ='';
open my $ifh,'<',$infile or die "$!";
while (my $line=<$ifh>) {
chomp $line;
if ( substr($line,0,2) eq '"$'
|| substr($line,0,1) eq '$') {
$last = $line;
}
push @{$data{$last}},$line;
}
close $ifh;
return %data;
}
#--------------------------------------------
# This subroutine extracts data from sql server
# to an array of arrays for data processing
# -------------------------------------------
sub build_sqlData{
use Data::Dumper;
use DBI;
# establish the connection
my $dbh = DBI->connect('dbi:ODBC:myDSN','user','pass',
{ RaiseError => 1,
PrintError => 1 } )
or die "Could not connect to database: $DBI::errstr";
# sql query statement
my $sql =<< "SQL";
SELECT RTRIM(location),RTRIM(CompletionStatus)
FROM ewNetworkFaults
WHERE Type like '%Room Audio Visual%'
AND CompletionStatus not like '%Completed%'
SQL
my $table = $dbh->selectall_arrayref($sql);
print Dumper $table;
my $last_index = $#${table};
my $count = scalar @$table;
print "
records = $count
last_index = $last_index\n";
my %hdata;
for my $row (@$table) {
my $location = $row->[0];
my $status = $row->[1];
$hdata{$location} = $status;
}
#print Dumper \%hdata;
return \%hdata;
}