#!/usr/local/bin/perl -wT
#
# 95.cgi - calculate the 95th percentile from the RTG database
# Copyright (C) 2006 - Anthony Tonns
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
#
#
# initially written
# ATonns Mon Aug  7 11:52:36 EDT 2006
#

use strict;
use CGI qw/-debug :standard :html3 :cgi-lib/;
use CGI::Carp qw(fatalsToBrowser);
use DBI;
use Time::HiRes qw(gettimeofday tv_interval);
use Time::Local;

### customizable variables ###
my $config = '/usr/local/rtg/etc/rtg.conf';
my $relative_image_directory = "/rtg";
my $rtg_cgi_location = "rtg.cgi";
my @colors = ( "#FFFFCC", "#94D6E7" );

### static variables ###
my $VERSION = "0.7.4";

### CODE ###
sub DEBUG { 0; }

# baseline timestamp
my $t0 = [gettimeofday];

# start CGI output
print header;

# get the database connection properties from rtg.conf
my %dbconfig;
my $conf;
open($conf,$config) || die "cannot open config file '$config':";
while(<$conf>) {
	my $line = $_;
	chomp $line;
	next if $line =~ /^#/;
	next if $line =~ /^$/;
	my ($key,$val) = split(/\s+/,$line,2);
	if ( $key =~ /^DB_/ ) {
		$dbconfig{$key} = $val;
	}
}
close $conf;

# connect to the database
my $dbh = DBI->connect(
	"DBI:mysql:database=$dbconfig{DB_Database};host=$dbconfig{DB_Host}",
	"$dbconfig{DB_User}",
	"$dbconfig{DB_Pass}",
	{'RaiseError' => 1}
);
my $statement;

# parse the CGI variables
my %p = Vars;

# set the page title
my $title;
$title .= "RTG: ";
if ( $p{customer} ) {
	$title .= $p{customer};
}

# start the HTML
print start_html(-title=>$title,-background=>"$relative_image_directory/rtgback.png");
print a({-href=>"http://rtg.sourceforge.net/"},img {-src=>"$relative_image_directory/rtg.png",-border=>0}),p,hr;
print "Report: 95th percentile", br;

# function: compute_stats
# args: Ref to AoA from "selectall_arrayref" from the statement:
#
# SELECT counter, UNIX_TIMESTAMP(dtime) as unixtime, dtime
# FROM ifInOctets_$rids[$iid]
# WHERE $range AND id=$iid ORDER BY dtime
#
# return value: Ref to hash with computed statistics
#
sub compute_stats {
	my ($ary_ref) = $_[0];
	my %data = (
		'avg' => 0,
		'max' => 0,
		'ninetyfifth' => 0,
		'count' => 0,
		'ignore' => 0,
	);
	my @rates;
	my $avgrate = 0;
	my $last = 0;

	foreach my $row (@{$ary_ref}) {
		my $counter = $row->[0];
		my $now = $row->[1];
		my $dtime = $row->[2];
		# is the data out of order for some reason?
		if ( $last > $now || $now - $last <= 0 || $counter < 0 ) {
			print "ERROR: bad samples counter $counter now $now dtime $dtime\n";
			next;
		}
		# can't calculate a rate on the 1st measurement
		if ( $last == 0 ) {
			$last = $now;
			next;
		}
		# count this row
		$data{count}++;
		# calculate the current rate
		my $period = $now - $last;
		my $bits = $counter * 8;
		my $nowrate = $bits / $period;
		# store it
		push(@rates,$nowrate);
		# sum all rates for averaging later
		$avgrate += $nowrate;
		# is it the max rate?
		$data{max} = $data{max} > $nowrate ? $data{max} : $nowrate;
		# prep for next interation
		$last = $now;
	}

	# lop off 95% of the rows
	my $real = $data{count} * 0.05;
	# get an integer value as an index
	my $int = int($real);
	# round up, if required
	$int += $real - $int >= 0.5 ? 1 : 0;
	# this is the number of rows ignored
	$data{ignore} = $int;
	# reverse sort all the rates, so the highest ones are at the beginning
	my @srates = reverse sort {$a <=> $b} @rates;
	# index in to get the 95% rate
	$data{ninetyfifth} = $srates[$int];
	# calculate the average rate if there are measurements
	if ( $data{count} != 0 ) {
		$data{avg} = $avgrate / $data{count};
	}
	else {
		$data{avg} = 0;
	}
	return \%data;
}

# start the form
print start_form("GET");

# did they fill out (most of) the form?
if ( $p{customer} && $p{smonth} && $p{emonth} ) {

	# back-calculate unix time give the form input
	my $bt = timelocal(0,$p{smin},$p{shour},$p{sday},$p{smonth}-1,$p{syear}-1900);
	my $et = timelocal(0,$p{emin},$p{ehour},$p{eday},$p{emonth}-1,$p{eyear}-1900);
	# pretty-print the form input
	my $bt_str = localtime($bt);
	my $et_str = localtime($et);
	# the time range for use in the SQL statements below
	my $range="dtime>FROM_UNIXTIME($bt) AND dtime<=FROM_UNIXTIME($et) ";

	# the report basics
	print "Customer: $p{customer}",br;
	print "Period: $bt_str - $et_str",p;

	# SQL query to find all interfaces/routers that have this customer
	$statement = "";
	$statement .= qq!SELECT id, rid, name, description !;
	$statement .= qq!FROM interface !;
	$statement .= qq!WHERE description LIKE "%$p{customer}%"!;

	# store this data locally for iteration,
	# as we'll have to re-use the $dbh a lot in a moment
	my %data;
	my $ary_ref = $dbh->selectall_arrayref($statement);
	foreach my $row (@{$ary_ref}) {
		my $iid = $row->[0];
		$data{$iid}{rid} = $row->[1];
		$data{$iid}{name} = $row->[2];
		$data{$iid}{desc} = $row->[3];
		print "<!-- iid $row->[0] rid $row->[1] name $row->[2] desc $row->[3] -->\n" if DEBUG;
	}

	# a prepared SQL query to get the router "name"
	$statement = "";
	$statement .= qq!SELECT name !;
	$statement .= qq!FROM router !;
	$statement .= qq!WHERE rid=?!;
	my $router_sth = $dbh->prepare($statement);

	# now iterate over every interface found for this customer
	my @rows;
	my $curr_color = 0;
	foreach my $iid (sort keys %data) {

		# get the router name for this interface
		my $rid = $data{$iid}{rid};
		$router_sth->execute($rid);
		my ($router) = $router_sth->fetchrow_array;
		print "<!-- iid $iid rid $rid router $router -->\n" if DEBUG;

		# get the input statistics for this interface
		my $in_table = "ifInOctets_$rid";
		$statement = "";
		$statement .= qq!SELECT counter, UNIX_TIMESTAMP(dtime) as unixtime, dtime !;
		$statement .= qq!FROM $in_table !;
		$statement .= qq!WHERE $range !;
		$statement .= qq! AND id=? !;
		$statement .= qq! ORDER BY dtime !;
		my $input_sth = $dbh->prepare($statement);
		$input_sth->execute($iid);
		my $in_ref = $input_sth->fetchall_arrayref;
		my $in_data = compute_stats($in_ref);

		# get the output statistics for this interface
		my $out_table = "ifOutOctets_$rid";
		$statement = "";
		$statement .= qq!SELECT counter, UNIX_TIMESTAMP(dtime) as unixtime, dtime !;
		$statement .= qq!FROM $out_table !;
		$statement .= qq!WHERE $range !;
		$statement .= qq! AND id=? !;
		$statement .= qq! ORDER BY dtime !;
		my $output_sth = $dbh->prepare($statement);
		$output_sth->execute($iid);
		my $out_ref = $output_sth->fetchall_arrayref;
		my $out_data = compute_stats($out_ref);

		# get the color of this row
		my $color = $colors[$curr_color];
		$curr_color = $curr_color ? 0 : 1;

		# create a link to the RTG graph for this interface
		my $rtg_link = "$rtg_cgi_location?";
		$rtg_link .= "rid=$rid&";
		$rtg_link .= "iid=$iid&";
		$rtg_link .= "smonth=$p{smonth}&";
		$rtg_link .= "sday=$p{sday}&";
		$rtg_link .= "syear=$p{syear}&";
		$rtg_link .= "shour=$p{shour}&";
		$rtg_link .= "smin=$p{smin}&";
		$rtg_link .= "emonth=$p{emonth}&";
		$rtg_link .= "eday=$p{eday}&";
		$rtg_link .= "eyear=$p{eyear}&";
		$rtg_link .= "ehour=$p{ehour}&";
		$rtg_link .= "emin=$p{emin}&";
		$rtg_link .= "nth=95&";
		$rtg_link .= "percentile=on&";

		# setup the row in the table for this interface
		my $row =
			td({-bgcolor=>$color},a({-href=>"$rtg_link"},$data{$iid}{name})).
			td({-bgcolor=>$color},$data{$iid}{desc}).
			td({-bgcolor=>$color},$router).
			td({-bgcolor=>$color,-align=>"right"},sprintf("%.2f",$in_data->{avg}/1000000)).
			td({-bgcolor=>$color,-align=>"right"},sprintf("%.2f",$out_data->{avg}/1000000)).
			td({-bgcolor=>$color,-align=>"right"},sprintf("%.2f",$in_data->{max}/1000000)).
			td({-bgcolor=>$color,-align=>"right"},sprintf("%.2f",$out_data->{max}/1000000)).
			td({-bgcolor=>$color,-align=>"right"},sprintf("%.2f",$in_data->{ninetyfifth}/1000000)).
			td({-bgcolor=>$color,-align=>"right"},sprintf("%.2f",$out_data->{ninetyfifth}/1000000));
		# if debug was checked, add the debug data
		if ( $p{debug} ) {
			$row .=
				td({-bgcolor=>$color,-align=>"right"},$in_data->{count}).
				td({-bgcolor=>$color,-align=>"right"},$out_data->{count}).
				td({-bgcolor=>$color,-align=>"right"},$in_data->{ignore}).
				td({-bgcolor=>$color,-align=>"right"},$out_data->{ignore});
		}
		# store the row for output later
		push(@rows,$row);
	}

	# generate the top row of headers for the table (column categories)
	my @top_headers = (
		th( {-colspan=>3},"Interface"),
		th( {-colspan=>2},"Current Rate"),
		th( {-colspan=>2},"Max Rate"),
		th( {-colspan=>2},"95th \% Rate"),
	);
	# add the debug columns if necessary
	if ( $p{debug} ) {
		push(@top_headers,
			th( {-colspan=>2},"Samples"),
			th( {-colspan=>2},"Ignore Top"),
		);
	}
	# generate the bottom row of headers for the table (column details)
	my @bottom_headers = (
		th("Name"), th("Description"), th("Router"),
		th("In (Mpbs)"), th("Out (Mpbs)"),
		th("In"), th("Out"),
		th("In"), th("Out"),
	);
	# add the debug columns if necessary
	if ( $p{debug} ) {
		push(@bottom_headers,
			th("In"), th("Out"),
			th("In"), th("Out"),
		);
	}

	# print all the data in an HTML table
	print table({-border=>1},
		Tr({-bgcolor=>"#E0E0E0"},@top_headers),
		Tr({-bgcolor=>"#E0E0E0"},@bottom_headers),
		Tr(\@rows),
	);

}

# otherwise, the form wasn't filled out, so print it
else {
	# use "now" as the time to fill-in the form values by default
	my @now = localtime(time);
	print "Customer Name: " . textfield(-name=>"customer");
	print table({-border=>0},
		Tr( [
			td( [	"From:",
				textfield(	-name=>'smonth',
						-size=>3, -maxlength=>2,
						-default=> $now[4] + 1 ),
				textfield(	-name=>'sday',
						-size=>3, -maxlength=>2,
						-default=> $now[3] ),
				textfield(	-name=>'syear',
						-size=>5, -maxlength=>4,
						-default=> $now[5] + 1900),
				textfield(	-name=>'shour',
						-size=>3, -maxlength=>2,
						-default=> "00" ),
				textfield(	-name=>'smin',
						-size=>3, -maxlength=>2,
						-default=> "00" ),
			] ),
			td( [	"To:",
				textfield(	-name=>'emonth',
						-size=>3, -maxlength=>2,
						-default=> $now[4] + 1 ),
				textfield(	-name=>'eday',
						-size=>3, -maxlength=>2,
						-default=> $now[3] ),
				textfield(	-name=>'eyear',
						-size=>5, -maxlength=>4,
						-default=> $now[5] + 1900),
				textfield(	-name=>'ehour',
						-size=>3, -maxlength=>2,
						-default=> "23" ),
				textfield(	-name=>'emin',
						-size=>3, -maxlength=>2,
						-default=> "59" ),
			] ),
		] ),
	);
	print "Debug: ",checkbox(-name=>'debug',-label=>''),br,br;
	print submit(-value=>'Ok'), br;
}

# clean up form
print end_form;
print br, br, hr;
print a({-href=>"http://rtg.sourceforge.net/"},'RTG') . "Version $VERSION";

# clean up database handle, incase this gets mod_perl'd in the future
$dbh->disconnect;

# final timestamp
my $elapsed = tv_interval ( $t0, [gettimeofday]);
# print out as HTML comment
print "\n<!-- time elapsed: $elapsed seconds -->\n";

# clean up HTML
print end_html;