#!/usr/bin/perl

use Net::SMTP;
use DBI;
use POSIX qw(strftime);

# pull in prefs
require "/private/admin/acct/requires/prefs.pl";

# Connect to database server
my $dbh = DBI->connect($prefs{'connect_string'},
		$prefs{'sql_user'},
		$prefs{'sql_pass'},
		{ AutoCommit => 1 })
	or die("$DBI::errstr\n");


# Threshold percentage for warnings
my $threshold = 80;

# From address on quota emails
my $from = "consulting\@oregonstate.edu";

# Date to put on overquota messages
my $date = strftime("%a, %d %b %Y %H:%M:%S %z", localtime);

# Message for warning
my $msg_warn = <<ECHO
To: %to%
Subject: NEARING MAIL QUOTA

Your ONID mailbox (%to%) is above the recommended
limit of 80% and is nearing your mail quota.

You are currently using %usage% MB (%percent%%) of your %quota% MB mail quota.
You will not be able to receive new mail if you exceed your mail quota.

You need to allow enough space so that you can continue to receive
new e-mail.  If you typically receive large attachments you should
be sure to allow space for several of them.

To make more space available, delete any messages that you no
longer need.  Frequently, messages are left in the Trash folder
and the Sent Items or sent-mail folder.  Those folders are good
places to look for messages that can be deleted.

If you need help, please contact IS Computer Consulting at
http://tss.oregonstate.edu/consulting/ or (541) 737-3474.

-- ONID Support
ECHO
;

# Message for overquota
my $msg_over = <<ECHO
Date: %date%
From: %from%
To: %to%
Subject: OVER MAIL QUOTA

Your ONID mailbox (%to%) has exceeded your mail quota.

You are currently using %usage% MB (%percent%%) of your %quota% MB mail quota.
You can no longer receive new mail in your ONID mailbox.

To make more space available, delete any messages that you no
longer need.  Frequently, messages are left in the Trash folder
and the Sent Items or sent-mail folder.  Those folders are good
places to look for messages that can be deleted.

If you need help, please contact IS Computer Consulting at
http://tss.oregonstate.edu/consulting/ or (541) 737-3474.

--ONID Support
ECHO
;

# Message to sysadmins
my $msg_sys = <<ECHO
To: support\@onid.orst.edu
Subject: Mail Quota Report

Mail Quota Report

ECHO
;

# Fetch quota report at given threshold
my ($quota, $percent, $usage, $username);
my %overquota = ();
my %warning = ();
open(IN, "ssh root\@cyrus-be1.onid.oregonstate.edu /private/tools/report_over_quota.pl $threshold |");
while(<IN>) {
	if (/(\d+)\s+(\d+)\s+(\d+)\s+user\.(\w+)/) {
		$quota = $1;
		$percent = $2;
		$usage = $3;
		$username = $4;

		# Convert to MB
		$quota = int($quota / 1024);
		$usage = int($usage / 1024);

		if ($percent >= 100) {
			$overquota{$username} = "$quota:$percent:$usage";
		}
		else {
			$warning{$username} = "$quota:$percent:$usage";
		}
	}
}
close(IN);


# Send out warning messages
my ($tmpmsg, $to);
$msg_sys .= "Emails sent to users over $threshold%:\n\n";
foreach $username (sort keys %warning) {
	$tmpmsg = $msg_warn;
	($quota,$percent,$usage) = split(/:/, $warning{$username});
	$to = "$username\@onid.orst.edu";

	# Build sysadmin message
	$msg_sys .= sprintf("%-8s   %d / %d MB (%d%%)\n", $username, $usage, $quota, $percent);

	# Substitute values into message
	$tmpmsg =~ s/%to%/$to/g;
	$tmpmsg =~ s/%quota%/$quota/g;
	$tmpmsg =~ s/%percent%/$percent/g;
	$tmpmsg =~ s/%usage%/$usage/g;

	$loop = 0;
	while ($loop < 3) {
		$smtp = Net::SMTP->new('mail.oregonstate.edu');
		last if ($smtp);
		$loop++;
	}
	if (! $smtp) {
		die("Unable to send mail to mail.oregonstate.edu, exiting");
	}

	$smtp->mail($from);
	$smtp->to($to);
	$smtp->data($tmpmsg);
	$smtp->dataend();
}


# Send out overquota message
my ($count, $sth);
$msg_sys .= "\nUsers over quota (100%):\n\n";
foreach $username (sort keys %overquota) {
	# Build sysadmin message
	($quota,$percent,$usage) = split(/:/, $overquota{$username});
	$msg_sys .= sprintf("%-8s   %d / %d MB (%d%%)\n", $username, $usage, $quota, $percent);

	# Check if we've already sent the overquota message to this user
	$sth = $dbh->prepare("SELECT COUNT(*) FROM overquota WHERE username = ?");
	$sth->execute($username);
	($count) = $sth->fetchrow_array;
	if ($count > 0) {
		# Don't send a message
		next;
	}

	# Add to overquota table
	$sth = $dbh->prepare("INSERT INTO overquota (username, overtime, quotatype)
			values (?, now(), 'mail')");
	$sth->execute($username);

	# And build a message to send
	$tmpmsg = $msg_over;
	$to = "$username\@onid.orst.edu";

	# Substitute values into message
	$tmpmsg =~ s/%to%/$to/g;
	$tmpmsg =~ s/%quota%/$quota/g;
	$tmpmsg =~ s/%percent%/$percent/g;
	$tmpmsg =~ s/%usage%/$usage/g;
	$tmpmsg =~ s/%from%/$from/g;
	$tmpmsg =~ s/%date%/$date/g;

	open(OUT, "| ssh root\@cyrus-be1.onid.oregonstate.edu /usr/local/cyrus/bin/deliver -q -r consulting\@oregonstate.edu $username");
	print OUT $tmpmsg;
	close(OUT);
}

# Sent out sysadmin message
$loop = 0;
while ($loop < 3) {
	$smtp = Net::SMTP->new('mail.oregonstate.edu');
	last if ($smtp);
	$loop++;
}
if (! $smtp) {
	die("Unable to send mail to mail.oregonstate.edu, exiting");
}

$smtp->mail("root\@onid.orst.edu");
$smtp->to("support\@onid.orst.edu");
$smtp->data($msg_sys);
$smtp->dataend();

# Clear users out of overquota table that are no longer overquota
$sth = $dbh->prepare("SELECT username FROM overquota");
$sth->execute();
while(($username) = $sth->fetchrow_array) {
	if (! defined($overquota{$username})) {
		$dbh->do("DELETE FROM overquota WHERE username = '$username'");
	}
}

$dbh->disconnect;