#!/usr/bin/perl
use strict;
use warnings;
use CGI ':all';
use DBI;

my $db = 'pmstats.db';
my $dbh = DBI->connect("dbi:SQLite:dbname=$db") or die $DBI::errstr;

All_Nodes_by_DOW( $dbh );
All_Root_Nodes_by_DOW( $dbh );
All_Sub_Nodes_by_DOW( $dbh );
All_Nodes_by_Hour( $dbh );
Root_Nodes_by_Hour( $dbh );
Sub_Nodes_by_Hour( $dbh );
Holiday( $dbh, 'christmas.html', 'CHRISTMAS DAY' );
Main();

sub All_Nodes_by_DOW {
    my ($dbh, $file) = @_;
    $file ||= 'all_nodes_by_dow.html';
    open ( HTML , '>' , $file ) or die "Unable to open $file for writing : $!";
    select HTML;
    $| = 1;

    my $sth = $dbh->prepare("SELECT COUNT(*) FROM nodes WHERE dow = ?");
    my @total;
    for my $dow ( 1..7 ) {
        $sth->execute($dow) or die $dbh->errstr;
        my @day_total = $sth->fetchrow_array;
        push @total , $day_total[0];
    }

    my @average;
    $sth = $dbh->prepare(
        "select count(*) from nodes where dow = ? group by year,month,day"
    );
    for my $dow ( 1..7 ) {
        $sth->execute($dow) or die $dbh->errstr;
        my $num_days = $sth->fetchall_arrayref;
        push @average , int ( $total[ $dow - 1 ] / @$num_days );
    }

    my $sum_total;
    $sum_total += $_ for @total;
    my @percentage;
    for ( @total ) {
        push @percentage , sprintf("%.2f" , $_ * 100 / $sum_total);
    }

    print 
        start_html( -title => 'All Nodes by Day of Week', -bgcolor => "#ffffcc" ),
        div( { -align => "center" },
            a({ href=>"Main.html"}, "Main Menu" ),
            p(h1( 'All Nodes by Day of Week' ) ),
            table(
                {
                -bgcolor     => "#000000",
                -border      => "0",
                -cellpadding => "2",
                -cellspacing => "1",
                },
                Tr( { -style => "background-color:#CCCCCC" },
                    th( [ qw(DAY MON TUE WED THU FRI SAT SUN) ] ),
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                        td( 'TOTAL' ),
                        map { td( $_ ) } @total
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                        td( 'AVERAGE' ),
                        map { td( $_ ) } @average
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                        td( 'PERCENT' ),
                        map { td( $_ ) } @percentage
                ),
            ),
        ),
        end_html;
}

sub All_Root_Nodes_by_DOW {
    my ($dbh, $file) = @_;
    $file ||= 'all_root_nodes_by_dow.html';
    open ( HTML , '>' , $file ) or die "Unable to open $file for writing : $!";
    select HTML;
    $| = 1;

    my $sth = $dbh->prepare("SELECT COUNT(*) FROM nodes WHERE dow = ? AND root = ''");
    my @total;
    for my $dow ( 1..7 ) {
        $sth->execute($dow) or die $dbh->errstr;
        my @day_total = $sth->fetchrow_array;
        push @total , $day_total[0];
    }

    my @average;
    $sth = $dbh->prepare(
        "select count(*) from nodes where dow = ? AND root = '' group by year,month,day"
    );
    for my $dow ( 1..7 ) {
        $sth->execute($dow) or die $dbh->errstr;
        my $num_days = $sth->fetchall_arrayref;
        push @average , int ( $total[ $dow - 1 ] / @$num_days );
    }

    my $sum_total;
    $sum_total += $_ for @total;
    my @percentage;
    for ( @total ) {
        push @percentage , sprintf("%.2f" , $_ * 100 / $sum_total);
    }

    print 
        start_html( -title => 'Root Nodes by Day of Week', -bgcolor => "#ffffcc" ),
        div( { -align => "center" },
            a({ href=>"Main.html"}, "Main Menu" ),
            p(h1( 'Root Nodes by Day of Week' ) ),
            table(
                {
                -bgcolor     => "#000000",
                -border      => "0",
                -cellpadding => "2",
                -cellspacing => "1",
                },
                Tr( { -style => "background-color:#CCCCCC" },
                    th( [ qw(DAY MON TUE WED THU FRI SAT SUN) ] ),
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                        td( 'TOTAL' ),
                        map { td( $_ ) } @total
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                        td( 'AVERAGE' ),
                        map { td( $_ ) } @average
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                        td( 'PERCENT' ),
                        map { td( $_ ) } @percentage
                ),
            ),
        ),
        end_html;
}

sub All_Sub_Nodes_by_DOW {
    my ($dbh, $file) = @_;
    $file ||= 'all_sub_nodes_by_dow.html';
    open ( HTML , '>' , $file ) or die "Unable to open $file for writing : $!";
    select HTML;
    $| = 1;

    my $sth = $dbh->prepare("SELECT COUNT(*) FROM nodes WHERE dow = ? AND root != ''");
    my @total;
    for my $dow ( 1..7 ) {
        $sth->execute($dow) or die $dbh->errstr;
        my @day_total = $sth->fetchrow_array;
        push @total , $day_total[0];
    }

    my @average;
    $sth = $dbh->prepare(
        "select count(*) from nodes where dow = ? AND root != '' group by year,month,day"
    );
    for my $dow ( 1..7 ) {
        $sth->execute($dow) or die $dbh->errstr;
        my $num_days = $sth->fetchall_arrayref;
        push @average , int ( $total[ $dow - 1 ] / @$num_days );
    }

    my $sum_total;
    $sum_total += $_ for @total;
    my @percentage;
    for ( @total ) {
        push @percentage , sprintf("%.2f" , $_ * 100 / $sum_total);
    }

    print 
        start_html( -title => 'Sub Nodes by Day of Week', -bgcolor => "#ffffcc" ),
        div( { -align => "center" },
            a({ href=>"Main.html"}, "Main Menu" ),
            p(h1( 'Sub Nodes by Day of Week' ) ),
            table(
                {
                -bgcolor     => "#000000",
                -border      => "0",
                -cellpadding => "2",
                -cellspacing => "1",
                },
                Tr( { -style => "background-color:#CCCCCC" },
                    th( [ qw(DAY MON TUE WED THU FRI SAT SUN) ] ),
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                        td( 'TOTAL' ),
                        map { td( $_ ) } @total
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                        td( 'AVERAGE' ),
                        map { td( $_ ) } @average
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                        td( 'PERCENT' ),
                        map { td( $_ ) } @percentage
                ),
            ),
        ),
        end_html;
}

sub All_Nodes_by_Hour {
    my ($dbh, $file) = @_;
    $file ||= 'all_nodes_by_hour.html';
    open ( HTML , '>' , $file ) or die "Unable to open $file for writing : $!";
    select HTML;
    $| = 1;

    my $sth = $dbh->prepare("SELECT COUNT(*) FROM nodes WHERE hour = ?");
    my @total;
    for my $hour ( map { sprintf "%.2d" , $_ } 0..23 ) {
        $sth->execute($hour) or die $dbh->errstr;
        my @day_total = $sth->fetchrow_array;
        push @total , $day_total[0];
    }

    my @average;
    $sth = $dbh->prepare(
        "select count(*) from nodes where hour = ? group by year,month,day,hour"
    );
    for my $hour ( map { sprintf "%.2d" , $_ } 0..23 ) {
        $sth->execute($hour) or die $dbh->errstr;
        my $num_days = $sth->fetchall_arrayref;
        push @average , int ( $total[ $hour ] / @$num_days );
    }

    my $sum_total;
    $sum_total += $_ for @total;
    my @percentage;
    for ( @total ) {
        push @percentage , sprintf("%.2f" , $_ * 100 / $sum_total);
    }

    print 
        start_html( -title => 'All Nodes by Hour', -bgcolor => "#ffffcc" ),
        div( { -align => "center" },
            a({ href=>"Main.html"}, "Main Menu" ),
            p(h1( 'All Nodes by Hour' ) ),
            table(
                {
                -bgcolor     => "#000000",
                -border      => "0",
                -cellpadding => "2",
                -cellspacing => "1",
                },
                Tr( { -style => "background-color:#CCCCCC" },
                    th( [ qw(HOUR TOTAL AVERAGE PERCENT) ] ),
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                    [
                        map {td([
                            sprintf( "%.2d", $_ ),
                            $total[$_],
                            $average[$_],
                            $percentage[$_],
                            ]),
                        } 0 .. 23
                    ]
                ),
            ),
        ),
        end_html;
}

sub Root_Nodes_by_Hour {
    my ($dbh, $file) = @_;
    $file ||= 'root_nodes_by_hour.html';
    open ( HTML , '>' , $file ) or die "Unable to open $file for writing : $!";
    select HTML;
    $| = 1;

    my $sth = $dbh->prepare("SELECT COUNT(*) FROM nodes WHERE hour = ? AND root = ''");
    my @total;
    for my $hour ( map { sprintf "%.2d" , $_ } 0..23 ) {
        $sth->execute($hour) or die $dbh->errstr;
        my @day_total = $sth->fetchrow_array;
        push @total , $day_total[0];
    }

    my @average;
    $sth = $dbh->prepare(
        "select count(*) from nodes where hour = ? AND root = '' group by year,month,day,hour"
    );
    for my $hour ( map { sprintf "%.2d" , $_ } 0..23 ) {
        $sth->execute($hour) or die $dbh->errstr;
        my $num_days = $sth->fetchall_arrayref;
        push @average , int ( $total[ $hour ] / @$num_days );
    }

    my $sum_total;
    $sum_total += $_ for @total;
    my @percentage;
    for ( @total ) {
        push @percentage , sprintf("%.2f" , $_ * 100 / $sum_total);
    }

    print 
        start_html( -title => 'Root Nodes by Hour', -bgcolor => "#ffffcc" ),
        div( { -align => "center" },
            a({ href=>"Main.html"}, "Main Menu" ),
            p(h1( 'Root Nodes by Hour' ) ),
            table(
                {
                -bgcolor     => "#000000",
                -border      => "0",
                -cellpadding => "2",
                -cellspacing => "1",
                },
                Tr( { -style => "background-color:#CCCCCC" },
                    th( [ qw(HOUR TOTAL AVERAGE PERCENT) ] ),
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                    [
                        map {td([
                            sprintf( "%.2d", $_ ),
                            $total[$_],
                            $average[$_],
                            $percentage[$_],
                            ]),
                        } 0 .. 23
                    ]
                ),
            ),
        ),
        end_html;
}

sub Sub_Nodes_by_Hour {
    my ($dbh, $file) = @_;
    $file ||= 'sub_nodes_by_hour.html';
    open ( HTML , '>' , $file ) or die "Unable to open $file for writing : $!";
    select HTML;
    $| = 1;

    my $sth = $dbh->prepare("SELECT COUNT(*) FROM nodes WHERE hour = ? AND root != ''");
    my @total;
    for my $hour ( map { sprintf "%.2d" , $_ } 0..23 ) {
        $sth->execute($hour) or die $dbh->errstr;
        my @day_total = $sth->fetchrow_array;
        push @total , $day_total[0];
    }

    my @average;
    $sth = $dbh->prepare(
        "select count(*) from nodes where hour = ? AND root != '' group by year,month,day,hour"
    );
    for my $hour ( map { sprintf "%.2d" , $_ } 0..23 ) {
        $sth->execute($hour) or die $dbh->errstr;
        my $num_days = $sth->fetchall_arrayref;
        push @average , int ( $total[ $hour ] / @$num_days );
    }

    my $sum_total;
    $sum_total += $_ for @total;
    my @percentage;
    for ( @total ) {
        push @percentage , sprintf("%.2f" , $_ * 100 / $sum_total);
    }

    print 
        start_html( -title => 'Sub Nodes by Hour', -bgcolor => "#ffffcc" ),
        div( { -align => "center" },
            a({ href=>"Main.html"}, "Main Menu" ),
            p(h1( 'Sub Nodes by Hour' ) ),
            table(
                {
                -bgcolor     => "#000000",
                -border      => "0",
                -cellpadding => "2",
                -cellspacing => "1",
                },
                Tr( { -style => "background-color:#CCCCCC" },
                    th( [ qw(HOUR TOTAL AVERAGE PERCENT) ] ),
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                    [
                        map {td([
                            sprintf( "%.2d", $_ ),
                            $total[$_],
                            $average[$_],
                            $percentage[$_],
                            ]),
                        } 0 .. 23
                    ]
                ),
            ),
        ),
        end_html;
}

sub Holiday {
    my ($dbh, $file, $holiday) = @_;
    $file ||= 'holiday.html';
    open ( HTML , '>' , $file ) or die "Unable to open $file for writing : $!";
    select HTML;
    $| = 1;

    my $sth = $dbh->prepare("SELECT COUNT(*) FROM nodes WHERE holiday = ?");
    $sth->execute($holiday) or die $dbh->errstr;
    my ($total) = $sth->fetchrow_array;

    $sth = $dbh->prepare("SELECT COUNT(*) FROM nodes WHERE holiday = ? AND root = ''");
    $sth->execute($holiday) or die $dbh->errstr;
    my ($total_root) = $sth->fetchrow_array;

    $sth = $dbh->prepare("SELECT COUNT(*) FROM nodes WHERE holiday = ? AND root != ''");
    $sth->execute($holiday) or die $dbh->errstr;
    my ($total_sub) = $sth->fetchrow_array;

    my @row;
    push @row , [ 'Overall', 'N/A' , $total , $total_root , $total_sub ];

    my $year_total_sth = $dbh->prepare(
        "select count(*) from nodes where year = ? AND holiday = ?"
    );
    my $year_root_sth = $dbh->prepare(
        "select count(*) from nodes where year = ? AND holiday = ? AND root = ''"
    );
    my $year_sub_sth = $dbh->prepare(
        "select count(*) from nodes where year = ? AND holiday = ? AND root != ''"
    );
    my $dow_sth = $dbh->prepare(
        "select dow from nodes where year = ? AND holiday = ?"
    );
    my @num2day = ( qw(N/A MON TUE WED THU FRI SAT SUN) );
    for my $year ( 1999 .. 2004 ) {
        $dow_sth->execute($year, $holiday) or die $dbh->errstr;
        my $dow_ref = $dow_sth->fetchall_arrayref;
	my $dow = $dow_ref->[0][0] ? $num2day[ $dow_ref->[0][0] ] : $num2day[0];
        $year_total_sth->execute($year, $holiday) or die $dbh->errstr;
        my @year_total = $year_total_sth->fetchrow_array;
        $year_root_sth->execute($year, $holiday) or die $dbh->errstr;
        my @year_root = $year_root_sth->fetchrow_array;
        $year_sub_sth->execute($year, $holiday) or die $dbh->errstr;
        my @year_sub = $year_sub_sth->fetchrow_array;
        push @row , [$year, $dow, $year_total[0], $year_root[0], $year_sub[0]];
    }


    print 
        start_html( -title => "Report for $holiday", -bgcolor => "#ffffcc" ),
        div( { -align => "center" },
            a({ href=>"Main.html"}, "Main Menu" ),
            p(h1( "Report for $holiday" ) ),
            table(
                {
                -bgcolor     => "#000000",
                -border      => "0",
                -cellpadding => "2",
                -cellspacing => "1",
                },
                Tr( { -style => "background-color:#CCCCCC" },
                    th( [ qw(YEAR DOW TOTAL ROOT SUB) ] ),
                ),
                Tr( { -style => "background-color:#CCCCCC" },
                    [
                        map {td([
                            $_->[0],
                            $_->[1],
                            $_->[2],
                            $_->[3],
                            $_->[4],
                            ]),
                        } @row
                    ]
                ),
            ),
        ),
        end_html;
}

sub Main {
    my $file = 'Main.html';
    open ( HTML , '>' , $file ) or die "Unable to open $file for writing : $!";
    select HTML;
    $| = 1;

    print 
        start_html( -title => 'MAIN MENU', -bgcolor => "#ffffcc" ),
        div( { -align => "center" },
            p(h1( 'MAIN MENU' ) ),
            p( a({ href=>"all_nodes_by_dow.html"}, "All Nodes by Day of Week" ) ),
            p( a({ href=>"all_root_nodes_by_dow.html"}, "All Root Nodes by Day of Week" ) ),
            p( a({ href=>"all_sub_nodes_by_dow.html"}, "All Sub Nodes by Day of Week" ) ),
            p( a({ href=>"all_nodes_by_hour.html"}, "All Nodes by Hour" ) ),
            p( a({ href=>"root_nodes_by_hour.html"}, "All Root Nodes by Hour" ) ),
            p( a({ href=>"sub_nodes_by_hour.html"}, "All Sub Nodes by Hour" ) ),
            p( a({ href=>"christmas.html"}, "Christmas Report" ) ),
        ),
        end_html;
}