use warnings;
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
use Cwd;

my @diskNames = GetDiskNames();
# create a new excel object
my $file = "iostat.xls";
unlink $file if (-e $file);
my $excel = new Win32::OLE("Excel.Application", \&QuitExcel) or 
die "could not create Microsoft Excel Application $! \n";
if ($excel)
{
  $excel->{Visible} = 1;
  $excel->{SheetsInNewWorkBook} = 2;
  my $workbook = $excel->WorkBooks->Add() 
    or die "could not create workbook in the excel sheet \n";  
  # create two worksheets in it one for sps data one for bps data;

  # get the bps data for each sheet
  
  my $bpsWorkSheet = $workbook->Worksheets(1);
  $workbook->Worksheets($bpsWorkSheet);
  $bpsWorkSheet->{Name} = "BPS";  
  $bpsWorkSheet->Range("A1")->{Value} = "Bytes Read Per Second";
  
  my $col = "A";  
  my $row;
  foreach(@diskNames)
  {
    $row = 2;
    $bpsWorkSheet->Range("$col$row")->{Value} = $_;
    # create a new column in the excel work sheet for this disk and dump the data in that column
    my @sps = GetColumnForDisk($_, 2);  
    foreach(@sps)
    {
      $bpsWorkSheet->Range($col.++$row)->{Value} = $_;
    }
    $col++;
  }  
  PlotGraph($bpsWorkSheet, $col, $row, "Bytes Per Second Versus Time", "Time", "Bytes Per Second");
  
  my $spsWorkSheet = $workbook->Worksheets(2);
  $workbook->Worksheets($spsWorkSheet);
  $spsWorkSheet->{Name} = "SPS";  
  $spsWorkSheet->Range("A1")->{Value} = "Seeks Per Second";
  
  $col = "A";    
  foreach(@diskNames)
  {
    $row = 2;
    $spsWorkSheet->Range("$col$row")->{Value} = $_;
    # create a new column in the excel work sheet for this disk and dump the data in that column
    my @sps = GetColumnForDisk($_, 3);  
    foreach(@sps)
    {
      $spsWorkSheet->Range($col.++$row)->{Value} = $_;
    }
    $col++;
  }  
  PlotGraph($spsWorkSheet, $col, $row, "Seeks Per Second Versus Time", "Time", "Seeks Per Second");
  
  #save close the excel object
  $workbook->SaveAs(cwd() . "/iostat.xls");  
}

sub GetColumnForDisk
{  
  my $diskName = shift;
  my $columnNumber = shift;
  my @col;  
  open FILE, "iostat.txt";
  my @lines = <FILE>;  
  foreach my $line (@lines) 
  {
    if (defined($line) && length($line) > 0)
    {
      $line =~ m/\s*(\S+)\s*(\S+)\s*(\S+)\s*(\S+)\s*/;
      if (defined($1) && defined($2) && defined($3))
      {           
        if ($diskName eq $1)
        { 
          if ($columnNumber == 2)
          {
            push @col, $2 if (defined($2));          
          }
          elsif ($columnNumber == 3)
          {
            push @col, $3 if (defined($3));          
          }
        }
      }
    }
  }
  
  close FILE;  
  return @col;
}


sub GetDiskNames
{
  open FILE, "iostat.txt";
  my @lines = <FILE>;
  my @diskNames;
  foreach my $line (@lines) 
  {    
    $line =~ m/\s*(\S+)\s*(\S+)\s*(\S+)\s*(\S+)\s*/;    
    if (defined($1))
    {
      my $diskName = $1;
      if ($diskName =~ m/device/)
      {
        next;
      }
      my $diskExists = CheckElementExists(\@diskNames, $diskName);    
      if ($diskExists == 0)
      {
        push @diskNames, $diskName;
      }
    }
  }
  close FILE;
  return @diskNames;
}

sub CheckElementExists
{
  my ($diskList, $diskName) = @_;    
  my $retVal = 0;  
  foreach(@$diskList)
  {
    if ($diskName eq $_)
    {
      $retVal = 1;
    }
  }
  return $retVal;
}

sub QuitExcel
{
  my $comobject = $_[0];
  my $name = $comobject->{Name};
  print "Quitting $name \n";
  $comobject->Quit();
}

sub PlotGraph
{ 
  my $worksheet = shift;
  my $col = shift;
  my $row = shift;
  my $chartTitle = shift;
  my $xAxisTitle = shift;
  my $yAxisTitle = shift;
  my $MRange = $worksheet->Range("A3:$col$row");
  # insert chart in the bps sheet 
  my $chart = $excel->Charts->Add;
  $chart->{Name} = $worksheet->{Name} . " Chart";
  $chart->{ChartType} = xlLine;
  $chart->SetSourceData({Source=>$MRange, PlotBy=>xlColumns});
  $chart->{HasTitle}=1;
  $chart->ChartTitle->{Text} = $chartTitle;
  $chart->Axes(xlCategory, xlPrimary)->{HasTitle} = 1;
  $chart->Axes(xlCategory, xlPrimary)->AxisTitle->{Text} = $xAxisTitle;
  $chart->Axes(xlValue, xlPrimary)->{HasTitle} = 1;
  $chart->Axes(xlValue, xlPrimary)->AxisTitle->{Text} = $yAxisTitle;  
}