Category:
Author/Contact Info Val Polyakov vpolyakov@katrillion.com
Description: This script will automate the entering of daily traffic stastics (and its very easy to mod it to enter pretty much whatever you'd like). Docs on what it currently expects to be found and where are in the comments, on the top of the code. I do realize that the code could be much cleaner, however it's time for me to move on to the next project.. So if you'd like to help me out a bit and delete unneeded crap out of there (i have a few var's I don't need for example, and replace all those nested if's with something.. that'd be nice. But, it works fine as it is. Have fun! 
#!/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,Saturda
+y)[(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');
}
Replies are listed 'Best First'.
Re: Auto-dailies
by thraxil (Prior) on Aug 22, 2002 at 14:55 UTC

    these lines:

    if ($aolstats =~ /(\d+),(\d+)/) {
 $aolstats =  $1 . $2;
}
if ($msnstats =~ /(\d+),(\d+)/) {
 $msnstats = $1 . $2;
}
if ($wwwstats =~ /(\d+),(\d+)/) {
  $wwwstats = $1 . $2;
}

    [download]

    could be simplified as:

    $aolstats =~ s/(\d+),(\d+)/$1$2/;
$msnstats =~ s/(\d+),(\d+)/$1$2/;
$wwwstats =~ s/(\d+),(\d+)/$1$2/;

    [download]

    or even:

    s/(\d+),(\d+)/$1$2/ for ($aolstats,$msnstats,$wwwstats);

    [download]

    and of course, if you are dealing with numbers like 1,000,000, you'll want to add a 'g' flag to the substitution.

    anders pearson

[reply]
[d/l]
[select]
      if you are dealing with numbers like 1,000,000, you'll want to add a 'g' flag to the substitution.
      Something like: 
      s/(\d+),(\d+)/$1$2/g for ($aolstats,$msnstats,$wwwstats);

      [download]
      perhaps? Actually that wouldn't work because the two regexes overlap... You're trying to take '1,234,567' and squeeze the '234' grouping into $2 for the first match, and $1 for the next one.

      Since the comment in the original code reads: "Get rid of the comma in stats", I would write it like this: 

      tr/,//d for $aolstats, $msnstats, $wwwstats;

      [download]

      -Blake

[reply]
[d/l]
[select]

Back to Code Catacombs