#!/usr/bin/perl -w

use strict;
use DBI;

# Configure the following settings
my $from = 'eduroam-statistics@domain.tld';
my @send_to = ('name.surname@some.domain', 'aaa-podpora@arnes.si');
my @my_domains = ("domain.tld", "domain2.tld");
my $subject = "Eduroam usage statistics for " . join (",",@my_domains);
my $sql_filter = '';
#my $sql_filter = 'AND `Called-Station-Id` LIKE "____.____.____"';
my $sql_username = 'root';
#my $sql_username = 'statuser';
my $sql_pass = undef;
#my $sql_pass = 'hidden_password';

#########################################################################################################
my $start_date;
my @mysql_lines;
my $dbh;
my $data = "";
my $domain_regex = "";
my $tmp;

$ENV{PATH}="";
$ENV{BASH_ENV}="";

#########################################################################################################
sub mysql_connect {
        $dbh = DBI->connect("DBI:mysql::localhost", $sql_username, $sql_pass, { PrintError => 0 });
        if(!defined($dbh)) {
                print "ERROR: connect to MySQL database failed!";
                exit(-1);
        }
}
#########################################################################################################
sub mysql_do {
        my $rows;
	my $tmp = $_[0];

        return if($_[0] =~ m/^\s*$/);
        return if($_[0] =~ m/^#/);
        $tmp =~ s/;$//;
        $rows = $dbh->do("$tmp");
        if(!defined $rows) {
                print "ERROR: Unable to execute SQL command: $tmp";
		exit(-1);
        }
}
#########################################################################################################
sub mysql_disconnect {
        $dbh->disconnect;
}
#########################################################################################################
sub mysql_run {
	my $sth = $dbh->prepare($_[0]);
	my $row = $sth->execute();

	if (defined $row) {
		my @row_array;
		my @output;
		while (@row_array = $sth->fetchrow_array) {
			$row_array[1] = "" if(!defined $row_array[1]);
			push @output, "$row_array[0]\t$row_array[1]";
		}
		return @output;
	}
	return undef;
}
#########################################################################################################

# ---------------------------------------------------------------------------
# MAIN ()

# Silly config check.
foreach $tmp (@my_domains) {
	if($tmp eq "domain.tld") {
		print "You should configure the script before running it. The domain name is wrong.\n";
		exit(-1);
	}
}

# Set-up regular expression matching for the SQL queries.
if($#my_domains == 0) {
	$domain_regex = $my_domains[0] . "\$";
} else {
	$domain_regex = "(";
	foreach $tmp (@my_domains) {
		$domain_regex .= "$tmp\|";
	}
	$domain_regex = substr($domain_regex,0,-1) . ")\$";
}

# start date is 12 months ago.
my ( $sec, $min, $hr, $mday, $month, $year, $wday, $yday, $isdst ) = localtime(time);
$year += 1900;
$month += 1;
# substract 12 months
$year--; 
$start_date = "$year-$month-01"; # We start from the first day.

&mysql_connect();
&mysql_do("use radius");

# Process Network logins per month (All, Home):
$data .= "Network logins per month - All:\n";
$data .= "=============================================\n";
@mysql_lines = &mysql_run("select SUBSTRING(`Timestamp Start`,1,7) as Month, count(`User-Name`) as Network_Logins from ACCOUNTING where `Acct-Session-Time` > 60 $sql_filter group by Month;");
$data .= join("\n", @mysql_lines);
$data .= "\n\n";

$data .= "Network logins per month - Home institution:\n";
$data .= "=============================================\n";
@mysql_lines = &mysql_run("select SUBSTRING(`Timestamp Start`,1,7) as Month, count(`User-Name`) as Network_Logins from ACCOUNTING where `User-Name` REGEXP \"$domain_regex\" and `Acct-Session-Time` > 60 $sql_filter group by Month;");
$data .= join("\n", @mysql_lines);
$data .= "\n\n";

$data .= "User logins per month - All:\n";
$data .= "=============================================\n";
@mysql_lines = &mysql_run("select SUBSTRING(`Timestamp Start`,1,7) as Month, count(distinct `User-Name`) as User_Logins from ACCOUNTING where 1 $sql_filter group by Month;");
$data .= join("\n", @mysql_lines);
$data .= "\n\n";

$data .= "User logins per month - Home institution:\n";
$data .= "=============================================\n";
@mysql_lines = &mysql_run("select SUBSTRING(`Timestamp Start`,1,7) as Month, count(distinct `User-Name`) as User_Logins from ACCOUNTING where `User-Name` REGEXP \"$domain_regex\" $sql_filter group by Month;");
$data .= join("\n", @mysql_lines);
$data .= "\n\n";

$data .= "Monthly Network usage - Avarage Session Time:\n";
$data .= "(Only sessions with session_time > 60 sec are counted)\n";
$data .= "=============================================\n";
@mysql_lines = &mysql_run("select SUBSTRING(`Timestamp Start`,1,7) as Month, AVG(`Acct-Session-Time`) as Avarage_Session_Time from ACCOUNTING where `Acct-Session-Time` > 60 $sql_filter group by Month;");
$data .= join("\n", @mysql_lines);
$data .= "\n\n";

$data .= "Monthly Network usage - Avarage Input Octets:\n";
$data .= "(Only sessions with session_time > 60 sec are counted)\n";
$data .= "=============================================\n";
@mysql_lines = &mysql_run("select SUBSTRING(`Timestamp Start`,1,7) as Month, AVG(`Acct-Input-Octets`) as Avarage_Input_Octets from ACCOUNTING where `Acct-Session-Time` > 60 $sql_filter group by Month;");
$data .= join("\n", @mysql_lines);
$data .= "\n\n";

$data .= "Monthly Network usage - Avarage Output Octets:\n";
$data .= "(Only sessions with session_time > 60 sec are counted)\n";
$data .= "=============================================\n";
@mysql_lines = &mysql_run("select SUBSTRING(`Timestamp Start`,1,7) as Month, AVG(`Acct-Output-Octets`) as Avarage_Output_Octets from ACCOUNTING where `Acct-Session-Time` > 60 $sql_filter group by Month;");
$data .= join("\n", @mysql_lines);
$data .= "\n\n";

$data .= "Monthly Network usage - Avarage Input Packets:\n";
$data .= "(Only sessions with session_time > 60 sec are counted)\n";
$data .= "=============================================\n";
@mysql_lines = &mysql_run("select SUBSTRING(`Timestamp Start`,1,7) as Month, AVG(`Acct-Input-Packets`) as Avarage_Input_Packets from ACCOUNTING where `Acct-Session-Time` > 60 $sql_filter group by Month;");
$data .= join("\n", @mysql_lines);
$data .= "\n\n";

$data .= "Monthly Network usage - Avarage Output Packets:\n";
$data .= "(Only sessions with session_time > 60 sec are counted)\n";
$data .= "=============================================\n";
@mysql_lines = &mysql_run("select SUBSTRING(`Timestamp Start`,1,7) as Month, AVG(`Acct-Output-Packets`) as Avarage_Output_Packets from ACCOUNTING where `Acct-Session-Time` > 60 $sql_filter group by Month;");
$data .= join("\n", @mysql_lines);
$data .= "\n\n";

$data .= "How many usernames from Home Institution were first encountered.\n";
$data .= "=============================================\n";
&mysql_do("create table Stat_01 (User_Name varchar(100), First_Timestamp datetime);");
&mysql_do("insert into Stat_01 select `User-Name`, min(`Timestamp Start`) as Month from ACCOUNTING WHERE `User-Name` REGEXP \"$domain_regex\" $sql_filter group by `User-Name`;");
@mysql_lines = &mysql_run("select SUBSTRING(First_Timestamp,1,7) as Month, count(User_Name) from Stat_01 group by Month;");
&mysql_do("drop table Stat_01;");
$data .= join("\n", @mysql_lines);
$data .= "\n\n";

$data .= "How many different users logged in\n";
$data .= "(in last 12 months - from $start_date to now)\n";
$data .= "=============================================\n";
@mysql_lines = &mysql_run("select count(distinct `User-Name`) from ACCOUNTING where `Timestamp Start` > \"$start_date\" $sql_filter;");
$data .= join("\n", @mysql_lines);
$data .= "\n\n";

print "Sending mail \n\tFrom: $from \n\tTo: " . join(", ", @send_to) . " \n\tSubject: $subject\n\n";

open(SENDMAIL, "| /usr/sbin/sendmail -t") || die "Unable to open sendmail";
print SENDMAIL "To: " . join(" ,", @send_to) . "\n";
print SENDMAIL<<end
From: $from
Reply-To: $from
Subject: $subject

end
;
print SENDMAIL $data;
close SENDMAIL;

print "Mail sent.\n"; 

&mysql_disconnect();

exit 0;