Computing: Website and Database Programming

Website visits analysis.


5. Total visits per month charts.

5.1. What to display?
 
Aim: Creating graphical displays (charts) of the total visits per month on the site (home page), on its sections (Computer Basics, Lazarus/Free Pascal Programming, Website and Database Applications, Homeless Life in Luxembourg) and the homeless subsections. Approach: Reading the totals for this month and each of the sections considered from the database, and creating a webpage with the chart, dynamically displaying the monthly values.
5.2. What to do?
 
To do:
  • Create template HTML files with my standard site header and footer (to be read and written by the Perl scripts creating the webpages).
  • Make sure that the Perl "Chart" module is installed.
  • Create a Perl module (stats.pm), containing some general functions to be used by the charting scripts.
  • Create scripts that output a webpage (with my standard site headers and footers) displaying an image whose source will be the dynamic chart wanted.
  • Create scripts reading the monthly counts from the database and use them to create a dynamic chart to be used as source for a website image.
Code preview for chart creating Perl scripts:
  1. Read the monthly totals for the sections considered from the database.
  2. Rearrange the data read in order to get the dataset arrays to be used by the "Chart" module.
  3. Create the chart as dynamic JPG image.
5.3. Template HTML files.
 
These are the header and footer, as I use them on all the pages of my site. The download file (cf. Perl code below) contains 2 rudimentary HTML files that may be used as templates read by the scripts. If you have your own website, replace their content with your site's header and footer HTML).
My webpage header code (/sitestats/site_header.template.html).
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
        <title>In the street with my laptop</title>
        <link rel="stylesheet" type="text/css" href="/main.css"/>
        <meta name="description" content="Personal website of a homeless guy, living with his laptop in the street in Luxembourg"/>
        <meta name="keywords" content="homeless, street, laptop, Luxembourg, programming, Lazarus, FreePascal, website, database, MySQL, Perl"/>
    </head>
    <body>
        <div class="container">
            <div class="header">
                <h1 class="title">In the street with my laptop</h1>
                <table align="center" border="0" width="96%">
                    <tr>
                        <td align="center"><a href="/index.php">Home</a></td>
                        <td width="20px"> </td>
                        <td align="center"><a href="/news.html">News</a></td>
                        <td width="20px"> </td>
                        <td align="center"><a href="/sitemap.html">Sitemap</a></td>
                        <td width="30%"> </td>
                        <td align="center"><a href="/aboutme.html">About me</a></td>
                        <td width="20px"> </td>
                        <td align="center"><a href="/aboutsite.html">About this site</a></td>
                        <td width="20px"> </td>
                        <td align="center"><a href="/disclaimer.html">Disclaimer</a></td>
                        <td width="20px"> </td>
                        <td align="center"><a href="mailto:info@streetinfo.lu">Contact</a></td>
                    </tr>
                </table>
            </div>
            <div class="content">
My webpage footer code (/sitestats/site_footer.template.html).
        </div>
        <div class="clearfloat">
        </div>
        </div>
    </body>
</html>
5.4. Perl "monthly totals" charting scripts.
 
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;
#
# Please change the value of the $stats_dir variable to the directory where your header and footer templates are located!
#

my $stats_dir = 'C:\Programs\Apache24\htdocs\sitestats';
#
# webpage_header
#
# Print standard Web page header and create new CGI object
# Arguments: ---
# Return: new CGI object
#

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;
}
#
# site_header
#
# Print common header for your website pages
# Arguments: page title
# Return: new CGI object
# Note: the subroutine uses site_header.template.html as template input file
#

sub site_header {
    my ($title) = @_;
    my $cgi = webpage_header();
    # Read template HTML file
    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);
    # Print template content
    foreach my $line (@lines) {
        chomp($line);
        print "$line\n";
    }
    # Print the page title
    print ' <h2>' . $title . '</h2><br/>', "\n";
}
#
# site_footer
#
# Print common footer for your website pages
# Arguments: ---
# Return: ---
# Note: the subroutine uses site_footer.template.html as template input file
#

sub site_footer {
    # Read template HTML file
    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);
    # Print template content
    foreach my $line (@lines) {
        chomp($line);
        print "$line\n";
    }
}
#
# db_dbconnect
#
# Connect to "statistics" database
# Arguments: ---
# Return: "statistics" database handle
#

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;
}
#
# db_dbdisconnect
#
# Disconnect from "statistics" database
# Arguments: "statistics" database handle
# Return: ---
#

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"."

#
# Display total site visits per month
#

use strict; use warnings;
use stats;
# Print site header
my $cgi = site_header('Total visits per month.');
# Print totals per month as a chart
print ' <img src="total_hits_chart.pl" alt="?" title="Total visits per month" border="1">', '<br/><br/>', "\n";
# Add a link for more detailed visists chart
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";
# Print site footer
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"."

#
# Create total site visits per month chart
#

use strict; use warnings;
use stats;
use Chart::LinesPoints;
# Connect to "statistics" database
my $dbh = db_dbconnect();
# Read total homepage visits per month from database
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();
# Create arrays to be used as chart labels (x-axis) and first dataset (y-axis)
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);
}
# Read sum of website section visits per month from database
$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();
# Create array to be used as second dataset (y-axis)
my @counts2 = ();
foreach my $row (@$all) {
    my ($year, $month, $count) = @$row;
    push(@counts2, $count);
}
db_dbdisconnect($dbh);
# Get maximum count in order to manually set a maximum y-value (nicer grah)
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;
    }
}
# Create a linespoints chart and output it as "dynamic CGI image"
my $yticks = 11;   # a tick for all 10, 20, 50, 100, 500 or 1000 visits
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"."

#
# Display total sections visits per month
#

use strict; use warnings;
use stats;
# Print site header
my $cgi = site_header('Sections visits per month.');
# Print totals per section as a chart
print ' <img src="sections_hits_chart.pl" alt="?" title="Sections visits per month" border="1">', '<br/><br/>', "\n";
# Add a link to the homeless subsections visits chart
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";
# Print site footer
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"."

#
# Create total sections visits per month chart
#

use strict; use warnings;
use stats;
use Chart::LinesPoints;
# Connect to "statistics" database
my $dbh = db_dbconnect();
# Read total sections visits per month from database
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);
# Create array to be used as chart labels (x-axis) and hash to be used as datasets (y-axis)
my @months = (); my %counts = (); my $sx = -1; my $oldsection = '';
foreach my $row (@$all) {
    my ($sectionid, $section, $year, $month, $count) = @$row;
    unless ($sectionid eq 'C1') {   # exclude "luxcours" as development actually stopped
        if ((substr($sectionid, 0, 1) ne 'H') or ($sectionid eq 'H0')) {   # do not consider SDF subsections
            # Add month to array (unless it already is)
            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);
            }
            # Add monthly section count to hash
            if ($sectionid ne $oldsection) {
                # New section
                $sx++;   # using numeric index as hashes aren't sorted
                $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;
#
# Create a linespoints chart and output it as "dynamic CGI image"
#

my $chart = Chart::LinesPoints->new(1490,500);
# Months array elements as x-axis values
$chart->add_dataset(@months);
# Section totals arrays as y-axis values
for (my $i = 0; $i <= $sx; $i++) {
    # Create section totals array
    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);
        }
    }
    # Add section totals array to the chart
    $chart->add_dataset(@sectioncounts);
}
# Create legend array
my @legend = ();
for (my $i = 0; $i <= $sx; $i++) {
    my $label = sprintf('%-80s', $counts{$i}{'section'});
    push(@legend, $label);
}
# Create chart settings hash
my %colors = (
    'background' => 'seashell',
    'dataset0' => 'blue',
    'dataset1' => 'green',
    'dataset2' => 'orange'
);
if ($sx == 3) {   # actual situation: "Biology computing" section not under development
    $colors{'dataset3'} = 'black';
}
else {   # future situation: taking "Biology computing" section into account
    $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);
# Output the dynamic JPG image
$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"."

#
# Display homeless subsections visits per month
#

use strict; use warnings;
use stats;
# Print site header
my $cgi = site_header('Homeless subsections visits per month.');
# Print totals per homeless subsection as a chart
print ' <img src="homeless_hits_chart.pl" alt="?" title="Homeless subsections visits per month" border="1">', '<br/><br/>', "\n";
# Print site footer
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"."

#
# Create homeless subsections visits per month chart
#

use strict; use warnings;
use stats;
use Chart::StackedBars;
use Encode qw( encode_utf8 );
# Connect to "statistics" database
my $dbh = db_dbconnect();
# Read homless subsections visits per month from database
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);
# Create array to be used as chart labels (x-axis) and hash to be used as datasets (y-axis)
my @months = (); my %counts = (); my $sx = -1; my $oldsection = '';
foreach my $row (@$all) {
    my ($sectionid, $section, $year, $month, $count) = @$row;
    unless ($sectionid eq 'H0') {   # do not consider SDF section main page
        # Add month to array (unless it already is)
        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);
        }
        # Add monthly subsection count to hash
        $section = Encode::encode_utf8($section);
        $section =~ s/é/e/g; $section =~ s/è/e/g;   # "Chart" seems not understand UTF-8 characters (?)
        if ($sectionid ne $oldsection) {
            # New section
            $sx++;   # using numeric index as hashes aren't sorted
            $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;
#
# Create a stackedbars chart and output it as "dynamic CGI image"
#

my $chart = Chart::StackedBars->new(1490,500);
# Months array elements as x-axis values
$chart->add_dataset(@months);
# Subsection totals arrays as y-axis values
for (my $i = 0; $i <= $sx; $i++) {
    # Create subsection totals array
    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);
        }
    }
    # Add subsection totals array to the chart
    $chart->add_dataset(@sectioncounts);
}
# Create legend array
my @legend = ();
for (my $i = 0; $i <= $sx; $i++) {
    my $label = sprintf('%-80s', $counts{$i}{'section'});
    push(@legend, $label);
}
# Creating chart settings hash
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);
# Output the dynamic JPG image
$chart->cgi_jpeg();
5.5. Running the scripts.
 
Here's the output of the monthly site visits chart script, executed end January 2019 on my local Apache server by entering "localhost/cgi-bin/sitestats/total_hits.pl" in the address bar of my web browser. The header and footer used are the template HTML files described above.
Monthly site visits chart
The output of the monthly site sections visits chart script (local machine, same day); URL: "localhost/cgi-bin/sitestats/sections_hits.pl".
Monthly site sections visits chart
And the output of the monthly visits chart for the "Homeless Life in Luxembourg" subsections script; URL: "localhost/cgi-bin/sitestats/homeless_hits.pl".
Monthly 'homeless' subsections visits chart