in reply to Re^2: how to save data to new array after retrieving from sql server
in thread how to save data to new array after retrieving from sql server

where am i wrong

$#{$table} should be either $#${table} or just $#$table

$table is an arrayref so these 

  my $location = $table[i]->[0]; # missing $ on i
  my $room = $table[i]->[1];

[download]
should be 
  my $location = $table->[$i][0];
  my $room     = $table->[$i][1];

[download]

and push is for building an array not a hash, so this

push $hdata{$location}=$room;

should be

$hdata{$location} = $room;

or alternatively

#!/usr/bin/perl
use strict;
use Data::Dump 'pp';
my $table = [
 ['Wellbeing Office', 'Pending'],
 ['Library','Pending'], ['Y219','InProgress'],
 ['B201','InProgress'], ['B108','InProgress'],
 ['LAB1','InProgress'], ['C303','InProgress'],
];

my $last_index = $#${table};
my $count = scalar @$table;
print "
records    = $count 
last index = $last_index\n";

#my %hdata = ();
#for my $i (0..$last_index){
#  my $location = $table->[$i][0];
#  my $room     = $table->[$i][1];
#  $hdata{$location} = $room;    
#}
#pp \%hdata;

my %hdata = ();
for my $row (@$table){
  my $location = $row->[0];
  my $status   = $row->[1];
  $hdata{$location} = $status;    
}
pp \%hdata;

[download]
poj

Replies are listed 'Best First'.
Re^4: how to save data to new array after retrieving from sql server
by mhoang (Acolyte) on Aug 02, 2017 at 02:50 UTC

    Hello All, I follow all your advice and finally I got the HTML page that I want with the cell change color based on condition, now I do not want table width = 100% and I want fix size for all the cells I am stucked. When I changed the table width to auto and cells to some px all the headings just jumps. any idea please? 

    #!/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(
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
  "http://www.w3.org/TR/html4/loose.dtd"><html>
<head>
   <title>Bulletin</title>
    <style type="text/css">
     h4   { color: #0; font-size: 1em; }  
   </style>     

    <meta name="viewport" content="width=device-width, initial-scale=1
+">
    <link rel="stylesheet" type='text/css' media='screen' href="/Perl-
+Script/Extra/w3.css"> 
    
</head>
<body>
    <div class="w3-container w3-blue">
    <h4>NewYork Time</h4>
    <h4>Report Date $date  $time</h4>
    </div>
    <div class="w3-control-bar"></div>

);

foreach my $key (sort keys %hdata) {
        
      my $h4 = $key;
    $h4 =~ s/[\"\$]//g;
    print OUT qq!<h4>$h4</h4>
    <table border="1" width="100%" style="border-color:#e0e0e0" cellpa
+dding="5" align="left">\n!; #how to change width of table here????
                
    my @arrays = @{$hdata{$key}}; 
    for my $i (1..$#arrays) {
      print OUT '<tr>';
      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=#FF00
+00" :  
                     (${$href}{$col[$j]} =~ /InProgress/) ? "bgcolor=#
+FFFF00" :
                  "";        
         }

        print OUT "<td $bg align=left>$col[$j]</td>"; # how to set the
+ fixed width of cells ???
      };
      print OUT "</tr>";      
    };
    print OUT "</table>&nbsp;<br>\n";
}
print OUT "</body></html>";
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;
}

    [download]
    Nirvana is Now or Never
[reply]
[d/l]

      Remove the width and alignment from the table tag. Add cellspacing="0" if you want to change the double line borders to single. 

      print OUT qq!<h4>$h4</h4>
    <table border="1" style="border-color:#e0e0e0" cellpadding="5" cel
+lspacing="0" >\n!;

      [download]
      poj
[reply]
[d/l]
[select]

        Thanks Poj I finally got everything adjusted properly.I like to thank all the monks here for the great support.

         code
        Nirvana is Here and Now or Never
[reply]
[d/l]

In Section Seekers of Perl Wisdom