#!/usr/bin/perl
#########################################################################
# This guy automates the process of entering daily traffic		#
# into an Excel file.							#
# It expects the statistics to be found in /stats in the below format.	#
#									#
# for AOL, in file /stats/aol: YYYY-MM-DD-aol 123,456			#
# for MSN, in file /stats/msn: YYYY-MM-DD-msn 123,456			#
# for WWW, in file /stats/www: YYYY-MM-DD-www 123,456			#
#									#
# Val Polyakov vpolyakov@katrillion.com August 22, 2002			#
#########################################################################

use Spreadsheet::ParseExcel::SaveParser;


my $weekday = (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)[(localtime)[6]-1] ;
my $dayofmonth = (localtime)[3] - 1;
my $month = (January, February, March, April, May, June, July, August, September, October, November, December)[(localtime)[4]];
my $date = (localtime)[3] - 1; 
my $column = (localtime)[6] + 1;
my $row =  2 + $dayofmonth;
my $excelrow = $row + 1;
my $aolstats;
my $msnstats;
my $wwwstats;
my $aolfile = "/stats/aol";
my $msnfile = "/stats/msn";
my $wwwfile = "/stats/www";

#########################
# Get the stats
#########################
open(AOLFILE, $aolfile) or die "Couldn't open AOL stats: $!\n";
$aolstats = <AOLFILE>;
if ($aolstats =~ /\d{4}-\d{2}-\d{2}-aol\s+(.*)/) {
  $aolstats = $1; 
}
open(MSNFILE, $msnfile) or die "Couldn't open MSN stats: $!\n";
$msnstats = <MSNFILE>;
if ($msnstats =~ /\d{4}-\d{2}-\d{2}-msn\s+(.*)/) {
 $msnstats = $1;
}
open(WWWFILE, $wwwfile) or die "Couldn't open WWW stats: $!\n";
$wwwstats = <WWWFILE>;
if ($wwwstats =~ /\d{4}-\d{2}-\d{2}-www\s+(.*)/) {
 $wwwstats = $1;
}

################################
# Get rid of the comma in stats
################################
if ($aolstats =~ /(\d+),(\d+)/) {
 $aolstats =  $1 . $2;
}
if ($msnstats =~ /(\d+),(\d+)/) {
 $msnstats = $1 . $2;
}
if ($wwwstats =~ /(\d+),(\d+)/) {
  $wwwstats = $1 . $2;
}

#######################
# open the excel file
#######################

my $Excel = new Spreadsheet::ParseExcel::SaveParser;
my $workbook = $Excel->Parse("test.xls");
#########################################
# Workbook name         Workbook number #
#########################################
# Daily Traffic 2nd Quarter   0		#
# Daily Traffic 3rd Quarter   1		#
# Daily Traffic 4th Quarter   2		#
# 1st Quarter 2002	      3		#
# 2nd Quarter 2002	      4		#
# 3rd Quarter 2002	      5		#
# 4th Quarter 2002	      6		#
#########################################

if ($month =~ /January|February|March/) {
  if ($month =~ /January/) { $row = 2 + $dayofmonth; }
  if ($month =~ /February/) { $row = 35 + $dayofmonth; }
  if ($month =~ /March/) { $row = 66 + $dayofmonth; }
  $workbook->AddCell(3, $row, 0, "$month $date");
  $workbook->AddCell(3, $row, 1, $weekday);
  $workbook->AddCell(3, $row, 2, $aolstats);
  $workbook->AddCell(3, $row, 3, $msnstats);
  $workbook->AddCell(3, $row, 4, $wwwstats);
  $workbook->AddCell(3, $row, 6, "=SUM(C$excelrow:F$excelrow)"   );
  $Excel->SaveAs($workbook, 'test.xls');
}
elsif ($month =~ /April|May|June/) {
 if ($month =~ /April/) { $row = 2 + $dayofmonth; }
 if ($month =~ /May/) { $row = 35 + $dayofmonth; }
 if ($month =~ /June/) { $row = 67 + $dayofmonth; }
 $workbook->AddCell(4, $row, 0, "$month $date");
 $workbook->AddCell(4, $row, 1, $weekday);
 $workbook->AddCell(4, $row, 2, $aolstats);
 $workbook->AddCell(4, $row, 3, $msnstats);
 $workbook->AddCell(4, $row, 4, $wwwstats);
 $workbook->AddCell(4, $row, 6, "=SUM(C$excelrow:F$excelrow)"   );
 $Excel->SaveAs($workbook, 'test.xls');
}
elsif ($month =~ /July|August|September/) {
if ($month =~ /July/) { $row = 2 + $dayofmonth; }
if ($month =~ /August/) { $row = 35 + $dayofmonth; }
if ($month =~ /September/) { $row = 69 + $dayofmonth; } 
 $workbook->AddCell(5, $row, 0, "$month $date");
 $workbook->AddCell(5, $row, 1, $weekday);
 $workbook->AddCell(5, $row, 2, $aolstats);
 $workbook->AddCell(5, $row, 3, $msnstats);
 $workbook->AddCell(5, $row, 4, $wwwstats);
 $workbook->AddCell(5, $row, 6, "=SUM(C$excelrow:F$excelrow)"   );
 $Excel->SaveAs($workbook, 'test.xls');
}
elsif ($month =~ /October|November|December/) {
 if ($month =~ /October/) { $row = 2 + $dayofmonth; }
 if ($month =~ /November/) { $row = 35 + $dayofmonth; }
 if ($month =~ /December/) { $row = 67 + $dayofmonth; }
 $workbook->AddCell(6, $row, 0, "$month $date");
 $workbook->AddCell(6, $row, 1, $weekday);
 $workbook->AddCell(6, $row, 2, $aolstats);
 $workbook->AddCell(6, $row, 3, $msnstats);
 $workbook->AddCell(6, $row, 4, $wwwstats);
 $workbook->AddCell(6, $row, 6, "=SUM(C$excelrow:F$excelrow)"   );
 $Excel->SaveAs($workbook, 'test.xls');
}