| |
|
You can view the code by opening the tabs below. Or click the following link to download
all you need to install this appliation. Download file not yet available!
|
| a. Charting scripts common functions (/cgi-bin/sitestats/stats.pm). |
#!C:/Programs/Strawberry/win64/perl/bin/perl.exe
use strict; use warnings;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use DBI;
my $stats_dir = 'C:\Programs\Apache24\htdocs\sitestats';
sub webpage_header {
my $cgi = new CGI;
print "Content-Type: text/html\n\n";
print '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"/>', "\n\n";
return $cgi;
}
sub site_header {
my ($title) = @_;
my $cgi = webpage_header();
my $template = "$stats_dir\\site_header.template.html";
open(my $input, "<", $template)
or die "Can't open template file $template: $!";
my @lines = <$input>;
close($input);
foreach my $line (@lines) {
chomp($line);
print "$line\n";
}
print ' <h2>' . $title . '</h2><br/>', "\n";
}
sub site_footer {
my $template = "$stats_dir\\site_footer.template.html";
open(my $input, "<", $template)
or die "Can't open template file $template: $!";
my @lines = <$input>;
close($input);
foreach my $line (@lines) {
chomp($line);
print "$line\n";
}
}
sub db_dbconnect {
my $database='statistics'; my $username = 'stats'; my $passwd = 'password';
my $dbh = DBI->connect("dbi:mysql:database=$database", $username, $passwd, { AutoCommit => 1, RaiseError => 1, mysql_enable_utf8 => 1 })
or die "Failed to connect to database: $DBI::errstr";
return $dbh;
}
sub db_dbdisconnect {
my ($dbh) = @_;
$dbh->disconnect();
}
1;
|
|
The shebang at the beginning of the script points to the Perl interpreter to be used (in my case the 64bit version of Strawberry Perl
installed on my Windows 10) and has to be adapted to your actual development environment!
|
|
| b. Total site visits per month (/cgi-bin/sitestats/total_hits.pl and /cgi-bin/sitestats/total_hits_chart.pl). |
|
The script total_hits.pl creates a webpage including the common website header and footer, an image, whose source is set to the total_hits_chart.pl
script (that contains all the database and charting related code for the graph wanted and that returns a dynamic JPG image that will then be displayed
here) and a link to the monthly sections visits chart.
|
#!C:/Programs/Strawberry/win64/perl/bin/perl.exe -I"."
use strict; use warnings;
use stats;
my $cgi = site_header('Total visits per month.');
print ' <img src="total_hits_chart.pl" alt="?" title="Total visits per month" border="1">', '<br/><br/>', "\n";
print ' <table width="100%">', "\n";
print ' <tr><td align="right">', '<a href="/cgi-bin/sitestats/sections_hits.pl">Website sections details</a>', '</td></tr>', "\n";
print ' </table>', "\n";
site_footer();
|
|
The script total_hits_chart.pl creates a linespoints chart of the total monthly visits on my site (homepage) and displays this chart as a dynamic
JPG image, that may be catched on a webpage by setting the "src" attribute of an "img" tag to the URL of the script; in my actual application, the
image is catched and displayed on the website, cretaed by total_hits.pl (described above).
|
#!C:/Programs/Strawberry/win64/perl/bin/perl.exe -I"."
use strict; use warnings;
use stats;
use Chart::LinesPoints;
my $dbh = db_dbconnect();
my $sql = "SELECT YEAR(access_date) AS _year, MONTH(access_date) AS _month, SUM(access_count) AS _hits FROM site_hits\n";
$sql .= " WHERE web_site = 'AL'\n";
$sql .= "GROUP BY _year, _month\n";
$sql .= "ORDER BY _year, _month";
my $sth = $dbh->prepare($sql); $sth->execute();
my $all = $sth->fetchall_arrayref();
$sth->finish();
my @months = (); my @counts1 = ();
foreach my $row (@$all) {
my ($year, $month, $count) = @$row;
if ($month < 10) {
$month = '0' . $month;
}
$month .= '/' . substr($year, 2, 2);
push(@months, $month);
push(@counts1, $count);
}
$sql = "SELECT YEAR(access_date) AS _year, MONTH(access_date) AS _month, SUM(access_count) AS _hits FROM site_hits\n";
$sql .= " WHERE (web_site = 'CB') OR (web_site = 'CL') OR (web_site = 'CW') OR (web_site = 'H0') OR (web_site = 'B0')\n";
$sql .= "GROUP BY _year, _month\n";
$sql .= "ORDER BY _year, _month;";
$sth = $dbh->prepare($sql); $sth->execute();
$all = $sth->fetchall_arrayref();
$sth->finish();
my @counts2 = ();
foreach my $row (@$all) {
my ($year, $month, $count) = @$row;
push(@counts2, $count);
}
db_dbdisconnect($dbh);
my $max = $counts1[0];
for (my $i=1; $i < scalar @counts1; $i++) {
if ($counts1[$i] > $max) {
$max = $counts1[$i];
}
}
for (my $i=1; $i < scalar @counts2; $i++) {
if ($counts2[$i] > $max) {
$max = $counts2[$i];
}
}
my $ymax = 100;
if ($max > 1000) {
if ($max > 5000) {
$ymax = 10000;
}
else {
$ymax = 5000;
}
}
elsif ($max > 100) {
if ($max > 500) {
$ymax = 1000;
}
elsif ($max > 200) {
$ymax = 500;
}
else {
$ymax = 200;
}
}
my $yticks = 11;
my $chart = Chart::LinesPoints->new(1490,500);
$chart->add_dataset(@months);
$chart->add_dataset(@counts1);
$chart->add_dataset(@counts2);
my %hash = (
'y_label' => 'Visits',
'x_label' => 'Month',
'max_val' => $ymax,
'min_y_ticks' => $yticks,
'max_y_ticks' => $yticks,
'include_zero' => 'true',
'precision' => 0,
'grey_background' => 'false',
'colors' => { 'background' => 'seashell', 'dataset0' => 'red1', 'dataset1' => 'red3' },
'legend' => 'bottom',
'legend_labels' => ['Total homepage visits ', 'Total sections visits'],
'grid_lines' => 'true'
);
$chart->set( %hash);
$chart->cgi_jpeg();
|
|
The shebang at the beginning of the script points to the Perl interpreter to be used (in my case the 64bit version of Strawberry Perl
installed on my Windows 10) and has to be adapted to your actual development environment! Please note the presence of the argument -I"." passed to the Perl interpreter. This is because I stored the stats.pm module together with the scripts in
/cgi-bin/sitestats/ and Strawberry Perl does not automatically include the current directory (".") in the path where to search for modules.
|
|
| c. Total sections visits per month (/cgi-bin/sitestats/section_hits.pl and /cgi-bin/sitestats/sections_hits_chart.pl). |
|
The script section_hits.pl creates a webpage including the common website header and footer, an image, whose source is set to the section_hits_chart.pl
script (that contains all the database and charting related code for the graph wanted and that returns a dynamic JPG image that will then be displayed
here) and a link to the details of the monthly homeless subsections visits chart.
|
#!C:/Programs/Strawberry/win64/perl/bin/perl.exe -I"."
use strict; use warnings;
use stats;
my $cgi = site_header('Sections visits per month.');
print ' <img src="sections_hits_chart.pl" alt="?" title="Sections visits per month" border="1">', '<br/><br/>', "\n";
print ' <table width="100%">', "\n";
print ' <tr><td align="right">', '<a href="/cgi-bin/sitestats/homeless_hits.pl">Homeless subsections details</a>', '</td></tr>', "\n";
print ' </table>', "\n";
site_footer();
|
|
The script sections_hits_chart.pl creates a linespoints chart of the monthly visits on the main sections of my site and displays this chart as a dynamic
JPG image, that may be catched on a webpage by setting the "src" attribute of an "img" tag to the URL of the script; in my actual application, the
image is catched and displayed on the website, cretaed by section_hits.pl (described above).
|
#!C:/Programs/Strawberry/win64/perl/bin/perl.exe -I"."
use strict; use warnings;
use stats;
use Chart::LinesPoints;
my $dbh = db_dbconnect();
my $sql = "SELECT web_site AS _sectionid, site_description AS _section, YEAR(access_date) AS _year, MONTH(access_date) AS _month, SUM(access_count) AS _hits FROM site_hits, web_sites\n";
$sql .= " WHERE web_site <> 'AL'\n";
$sql .= " AND web_site = site_id\n";
$sql .= "GROUP BY _sectionid, _year, _month\n";
$sql .= "ORDER BY site_seqnum, _year, _month";
my $sth = $dbh->prepare($sql); $sth->execute();
my $all = $sth->fetchall_arrayref();
$sth->finish();
db_dbdisconnect($dbh);
my @months = (); my %counts = (); my $sx = -1; my $oldsection = '';
foreach my $row (@$all) {
my ($sectionid, $section, $year, $month, $count) = @$row;
unless ($sectionid eq 'C1') {
if ((substr($sectionid, 0, 1) ne 'H') or ($sectionid eq 'H0')) {
if ($month < 10) {
$month = '0' . $month;
}
$month .= '/' . substr($year, 2, 2);
my $newmonth = 1;
for (my $i = 0; $i < scalar @months; $i++) {
if ($months[$i] eq $month) {
$newmonth = 0;
}
}
if ($newmonth) {
push(@months, $month);
}
if ($sectionid ne $oldsection) {
$sx++;
$counts{$sx}{'section'} = $section;
$oldsection = $sectionid;
}
$counts{$sx}{$month} = $count;
}
}
}
@months = sort { substr($a, 3, 2) . substr($a, 0, 2) cmp substr($b, 3, 2) . substr($b, 0, 2) } @months;
my $chart = Chart::LinesPoints->new(1490,500);
$chart->add_dataset(@months);
for (my $i = 0; $i <= $sx; $i++) {
my @sectioncounts = ();
for (my $j = 0; $j < scalar @months; $j++) {
if (exists($counts{$i}{$months[$j]})) {
push(@sectioncounts, $counts{$i}{$months[$j]});
}
else {
push(@sectioncounts, 0);
}
}
$chart->add_dataset(@sectioncounts);
}
my @legend = ();
for (my $i = 0; $i <= $sx; $i++) {
my $label = sprintf('%-80s', $counts{$i}{'section'});
push(@legend, $label);
}
my %colors = (
'background' => 'seashell',
'dataset0' => 'blue',
'dataset1' => 'green',
'dataset2' => 'orange'
);
if ($sx == 3) {
$colors{'dataset3'} = 'black';
}
else {
$colors{'dataset3'} = 'magenta';
$colors{'dataset4'} = 'black';
}
my %hash = (
'y_label' => 'Visits',
'x_label' => 'Month',
'include_zero' => 'true',
'precision' => 0,
'grey_background' => 'false',
'colors' => \%colors,
'legend' => 'bottom',
'legend_labels' => \@legend,
'grid_lines' => 'true'
);
$chart->set( %hash);
$chart->cgi_jpeg();
|
|
| d. Total homeless subsections visits per month (/cgi-bin/sitestats/homeless_hits.pl and /cgi-bin/sitestats/homeless_hits_chart.pl). |
|
The script homeless_hits.pl creates a webpage including the common website header and footer, an image, whose source is set to the homeless_hits_chart.pl
script (that contains all the database and charting related code for the graph wanted and that returns a dynamic JPG image that will then be displayed
here).
|
#!C:/Programs/Strawberry/win64/perl/bin/perl.exe -I"."
use strict; use warnings;
use stats;
my $cgi = site_header('Homeless subsections visits per month.');
print ' <img src="homeless_hits_chart.pl" alt="?" title="Homeless subsections visits per month" border="1">', '<br/><br/>', "\n";
site_footer();
|
|
The script homeless_hits_chart.pl creates a stackedbars chart of the monthly visits on the subsections of my "Homeless Life in Luxembourg" site and
displays this chart as a dynamic JPG image, that may be catched on a webpage by setting the "src" attribute of an "img" tag to the URL of the script; in
my actual application, the image is catched and displayed on the website, cretaed by homeless_hits.pl (described above).
|
#!C:/Programs/Strawberry/win64/perl/bin/perl.exe -I"."
use strict; use warnings;
use stats;
use Chart::StackedBars;
use Encode qw( encode_utf8 );
my $dbh = db_dbconnect();
my $sql = "SELECT web_site AS _sectionid, site_description AS _section, YEAR(access_date) AS _year, MONTH(access_date) AS _month, SUM(access_count) AS _hits FROM site_hits, web_sites\n";
$sql .= " WHERE LEFT(web_site, 1) = 'H'\n";
$sql .= " AND web_site = site_id\n";
$sql .= " GROUP BY _sectionid, _year, _month\n";
$sql .= " ORDER BY site_seqnum, _year, _month";
my $sth = $dbh->prepare($sql); $sth->execute();
my $all = $sth->fetchall_arrayref();
$sth->finish();
db_dbdisconnect($dbh);
my @months = (); my %counts = (); my $sx = -1; my $oldsection = '';
foreach my $row (@$all) {
my ($sectionid, $section, $year, $month, $count) = @$row;
unless ($sectionid eq 'H0') {
if ($month < 10) {
$month = '0' . $month;
}
$month .= '/' . substr($year, 2, 2);
my $newmonth = 1;
for (my $i = 0; $i < scalar @months; $i++) {
if ($months[$i] eq $month) {
$newmonth = 0;
}
}
if ($newmonth) {
push(@months, $month);
}
$section = Encode::encode_utf8($section);
$section =~ s/é/e/g; $section =~ s/è/e/g;
if ($sectionid ne $oldsection) {
$sx++;
$counts{$sx}{'section'} = $section;
$oldsection = $sectionid;
}
$counts{$sx}{$month} = $count;
}
}
@months = sort { substr($a, 3, 2) . substr($a, 0, 2) cmp substr($b, 3, 2) . substr($b, 0, 2) } @months;
my $chart = Chart::StackedBars->new(1490,500);
$chart->add_dataset(@months);
for (my $i = 0; $i <= $sx; $i++) {
my @sectioncounts = ();
for (my $j = 0; $j < scalar @months; $j++) {
if (exists($counts{$i}{$months[$j]})) {
push(@sectioncounts, $counts{$i}{$months[$j]});
}
else {
push(@sectioncounts, 0);
}
}
$chart->add_dataset(@sectioncounts);
}
my @legend = ();
for (my $i = 0; $i <= $sx; $i++) {
my $label = sprintf('%-80s', $counts{$i}{'section'});
push(@legend, $label);
}
my %hash = (
'y_label' => 'Visits',
'x_label' => 'Month',
'include_zero' => 'true',
'precision' => 0,
'grey_background' => 'false',
'colors' => { 'background' => 'seashell', 'dataset0' => 'SteelBlue3', 'dataset1' => 'BlueViolet', 'dataset2' => 'DeepPink1', 'dataset3' => 'OliveDrab3', 'dataset4' => 'chocolate3'},
'legend' => 'bottom',
'legend_labels' => \@legend,
'grid_lines' => 'true'
);
$chart->set( %hash);
$chart->cgi_jpeg();
|
|
|
|
