#!/usr/local/bin/perl 

use List::MoreUtils qw( pairwise );
use Spreadsheet::WriteExcel;
use Excel::Writer::XLSX;

my $date = `date +%d%b%Y` ;
chomp($date);

my $FileName = "Weekly_Status_${date}.xlsx";
my $workbook = Excel::Writer::XLSX->new($FileName);

  my $worksheet2 = $workbook->add_worksheet("PtCorner_Summary_$date");
  $worksheet2->set_row('0',20);
  $worksheet2->set_column('A:B',18);
  $worksheet2->set_column('B:Z',10);
  $worksheet2->freeze_panes('3','4');

  my @ptsliceorder = &getSubDirs("timing/rpts"); 
  #the above will get list of dirs (func_ss_dir1, func_ss_dir2, func_ss_dir3, func_ss_di4, func_ff_dir1, func_ff_dir2, func_ff_dir3, func_ff_dir4, func_ff_dir5, func_ff_dir6, func_ff_dir7, func_ff_dir8); 
  #here i get order of @ptsliceorder random, need to sort it to get exactly as above.
  my @sliceorder = (sr2r, hr2r, si2r, sr2o, hi2r, hr2o);
  my %sliceIn;
  foreach $ptOrder (@ptsliceorder) {
    foreach $slice (@sliceorder) {
      if ($slice =~ m/hold/i) {
        $sliceIn{$ptOrder}{$slice} = "wns ths hfep";
      } else { $sliceIn{$ptOrder}{$slice} = "wns tns fep";}
    }
  }
  ##2nd Sheet Header Section
  ## Header section
  $flag  = 1 ;
  $row = 0;
  $col = 0;
  $nRow = $row   + 1 ;
  
  $worksheet2->write($row,$col++,  "PtCorner Wise Summary $date",       $yellow    );
  $worksheet2->write_blank($row,$col++,           $yellow    );
  
  $col = 0;
  $nRow = $row   + 1 ;
  $worksheet2->write($nRow,$col++,  "Block",       $reg    );
  $worksheet2->write($nRow,$col++,  "Owner",      $reg    );

  #I got stuck here in between #### lines.
######################################################
foreach $slice (@ptsliceorder) {
  foreach $order (@sliceorder) {
    if ($flag == 1) { 
      $flag  =  0 ;
      $color = $orange ;
    } else {
      $flag  =  1 ;
      $color = $yellow ;
    }
    @pt = keys %{$sliceIn};
    @ptsect = keys %{$sliceIn{$slice}};
    @sect = split (" ", $sliceIn{$slice}{$order});
    #print "my pt @pt, my ptsect @ptsect, my scet @sect\n";
    $nCol = ($#ptsect+1)*($#sect+1)+1;
    $ncol = $#sect + 1 ;
    $nRow = $row   + 1 ;
    $nrow = $nRow  + 1 ;
    $ptcnt = -1 ;
    $cnt  = -1 ;
    $ptcol = $col;
 
    $worksheet2->write($row,$ptcol,"$slice", $color);
    $worksheet2->write($nRow,$ptcol++,"$ptsect[$ptcnt++]", $reg);
    while ($nCol > 1) {
      $nCol = $nCol - 1 ;
      $worksheet2->write_blank($row,$ptcol,           $color    );
      $worksheet2->write($nRow,$ptcol++,  "$ptsect[$ptcnt++]",         $color    );
    }
    $worksheet2->write($nrow,$col++,"$sect[$cnt++]", $reg);
    while ($ncol > 1) {
      $ncol = $ncol - 1 ;
      $worksheet2->write_blank($nRow,$col,           $color    );
      $worksheet2->write($nrow,$col++,"$sect[$cnt++]", $reg);
    }
  }
}

foreach $ptOrder (@ptsliceorder) {
  foreach $order (@sliceorder) {
    $target = $order ;
    #printf "  Switch processing ... $target :: @sliceorder :: $ptOrder\n";
################################################################################
    switch ($target) {
          case /\bsr2r\b/   {
				 if ( $stage !~ m/tim/ ) {
				   $sr2rwns =  "NA"; $sr2rtns = "NA"; $sr2rvep = "NA"; 
				 } elsif ( $stage =~ m/tim/ ) {
				   
				   print "my rpt => $rpt\n";
				   $rpt = "timing/rpts/$ptOrder/${ptOrder}.slack_metrics.gz";
				   if ( ! -e $rpt ) { $sr2rwns =  "NA"; $sr2rtns = "NA"; $sr2rvep = "NA"; } else {
				     @Sr2r = getPtTiming($rpt,"clktoclk","Setup");
				     $sr2rwns = $Sr2r[0]; $sr2rtns = $Sr2r[1]; $sr2rvep = $Sr2r[2];
			       }
				 }
			         $worksheet2->write($tileCnt,$col++,  $sr2rwns,    $reg     );
			         $worksheet2->write($tileCnt,$col++,  $sr2rtns,    $reg     );
			         $worksheet2->write($tileCnt,$col++,  $sr2rvep,    $reg     );
				 }
	      etc...
    }
  }
}
$rowStart = 4;
$rowEnd   = 2 ;
$col      = 2;

$col--; $nMinusCol  = &columnMap($col--) ;
$autoFilCol = "$colText"."3";
$worksheet2->autofilter("A3:$autoFilCol");
$worksheet2->set_column("$colText:$colText",80);

$lastCel    = "$nMinusCol"."$rowEnd";
$worksheet2->conditional_formatting("A4:$lastCel", { type => 'text', criteria => 'ends with', value => 'NA', format => $coral });
$workbook->close();

sub getSubDirs {
  my $path = $_[0];
  opendir(DIR, $path) or die $!;
  while (my $file = readdir(DIR)) {
    # Use a regular expression to ignore files beginning with a period
    next if ($file =~ m/^\./);
    # A file test to check that it is a directory
    # Use -f to test for a file
    next unless (-d "$path/$file");
    #print "$file\n";
    push @sub_dirs, $file;
  }
  closedir(DIR);
  return @sub_dirs;
}

##</code><code>##

                                func_ss_dir1																		func_ss_dir1
      sr2r           hr2r   	   si2r 	   	 sr2o    	   hi2r         hr2o	  	 sr2r         hr2r   	   si2r 	   	 sr2o    	   hi2r         hr2o
  wns tns fep	 wns tns fep   wns tns fep    wns tns fep  wns tns fep  wns tns fep  wns tns fep   wns tns fep   wns tns fep    wns tns fep  wns tns fep  wns tns fep
  -1   -2  2     -1   -2  2    -1   -2  2     -1   -2  2   -1   -2  2   -1   -2  2   -1   -2  2    -1   -2  2    -1   -2  2     -1   -2  2   -1   -2  2   -1   -2  2