#!/usr/bin/perl

#############################################################################
# oSpam - Provides a server wide anti-spam solution for qmail + vmailmgr    #
#         based on ifspam, perl and SpamAssassin - <http://omail.omnis.ch>  #
#                                                                           #
# Copyright (C) 2003 Olivier Müller <om@omnis.ch>                           #
#                                                                           #
# $Id: $                                                                    #
# $Source: $                                                                #
#                                                                           #
# 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.           #
#############################################################################


use DBI;
use strict;

#################################################

# SETUP:

my $debug = 1;  

# emails:

my $debug_bcc_mail = '';  # keep empty after testing
my $admin_mail = '';  # for error notices
my $mail_sender = 'oSpam system <ospam@your-domain-here.ext>';

# database:

my $db_username = "nospam";
my $db_password = "*********";
my $db_hostname = "localhost";
my $db_database = "nospam";

my $tb_userpref = "userpref";	# SA sql table
my $tb_dotqmail = "dotqmail";	# ospam data

# file & cmd path:

my $cmd_preline = "/var/qmail/bin/preline";
my $cmd_spamc = "/usr/bin/spamc";
my $cmd_vdeliver = "/usr/local/bin/vdeliver";
my $cmd_filepipe = "/usr/local/nospam/filepipe";
my $cmd_ifspamh = "/usr/local/nospam/ifspamh";
my $spamc_options = "-f";
my $cmd_md5sum = "/usr/bin/md5sum";
my $cmd_chown = "/bin/chown";
my $cmd_chmod = "/bin/chmod";
my $cmd_sendmail = "/usr/sbin/sendmail";
my $sendmail_opt = "-oem -oi -t";

# qmail setup: 

my $cfg_virtualdomains = "/var/qmail/control/virtualdomains";
my $cfg_rcpthosts = "/var/qmail/control/rcpthosts";
my $dot_qmail_prefix = ".qmail-"; 

# internal values

my $version = 1; 		# integer
my $internal_version = 1000; 	# incrementing this number will force re-generation of all .qmail-files



#################################################

if (!-f $cmd_preline) { die "preline missing"; }
if (!-f $cmd_spamc) { die "spamc missing"; }
if (!-f $cmd_vdeliver) { die "vdeliver missing"; }
if (!-f $cmd_filepipe) { die "filepipe missing"; }
if (!-f $cmd_ifspamh) { die "ifspamh missing"; }
if (!-f $cmd_md5sum) { die "md5sum missing"; }
if (!-f $cmd_chown) { die "chown missing"; }
if (!-f $cmd_chmod) { die "chmod missing"; }
if (!-f $cmd_sendmail) { die "sendmail missing"; }
if (!-f $cfg_virtualdomains) { die "virtualdomains missing"; }

#################################################

# dotqmail file templates:

my $t_scan_only = <<TMPL1;
|$cmd_preline -d $cmd_spamc $spamc_options -u ___USERADDR___ | $cmd_filepipe $cmd_vdeliver
TMPL1

my $t_scan_and_fwdonly = <<TMPL2;
|$cmd_ifspamh ___FWDADDR___ ___USERADDR___
|$cmd_preline -d $cmd_spamc $spamc_options -u ___USERADDR___ | $cmd_filepipe $cmd_vdeliver
TMPL2

my $t_scan_and_fwd_and_deliver = <<TMPL3;
|$cmd_preline -d $cmd_spamc $spamc_options -u ___USERADDR___ | $cmd_filepipe $cmd_vdeliver
|$cmd_ifspamh ___FWDADDR___ ___USERADDR___
TMPL3

my $t_scan_and_trash = <<TMPL4;
|$cmd_ifspamh _trash_ ___USERADDR___
|$cmd_preline -d $cmd_spamc $spamc_options -u ___USERADDR___ | $cmd_filepipe $cmd_vdeliver
TMPL4


#################################################

my @added = ();
my @removed = ();
my @changed = ();
my @notchanged = ();

my %added_data = ();
my %removed_data = ();
my %changed_data = ();
my %notchanged_data = ();

my ($i, $query, $result, @datarow, $data, $email, $domain, $homedir, $file, $md5sum, $md5sum_check, $username, $log, $dotfile);
my ($spam_fwd, $spam_enabled, $spam_trash, $template, $spam_ts, $history, $mail_subject, $mail_body, $username_encoded);
my ($error, $msg, $active);

# connect to the database

my $db = DBI->connect("DBI:mysql:$db_database:$db_hostname","$db_username","$db_password")
	or die "mysql connection failed";


# get list of new/changed/deleted spam accounts

$query = $db->prepare("select $tb_userpref.username as username from $tb_userpref left join $tb_dotqmail on $tb_userpref.username=$tb_dotqmail.username where $tb_dotqmail.username IS NULL AND preference LIKE 'spam_enabled' order by username");
$query->execute or die "sql query error";
$i = 0;

print "ADDED: " if $debug;
while($data = $query->fetchrow_hashref) {
	$added[$i++] = $$data{"username"};
	print $$data{"username"} . " " if $debug;
}

$query = $db->prepare("select $tb_dotqmail.username as username,md5sum,filename,active,error,lastprefchange from $tb_dotqmail left join $tb_userpref on $tb_userpref.username=$tb_dotqmail.username where $tb_userpref.username IS NULL AND $tb_dotqmail.error NOT LIKE '1' order by username");
$query->execute or die "sql query error";
$i = 0;

print ".\nREMOVED: " if $debug;
while($data = $query->fetchrow_hashref) {
	$removed[$i++] = $$data{"username"};
	print $$data{"username"} . " " if $debug;

	$removed_data{($$data{"username"})} = { 
		'md5sum' => $$data{"md5sum"},
		'filename' => $$data{"filename"},
		'lastprefchange' => $$data{"lastprefchange"},
		'active' => $$data{"active"},
		'error' => $$data{"error"}
	};
}


$query = $db->prepare("select $tb_userpref.username as username,$tb_dotqmail.md5sum as md5sum,$tb_dotqmail.filename as filename,$tb_dotqmail.lastprefchange as lastprefchange,$tb_dotqmail.active as active,$tb_dotqmail.error as error from $tb_userpref left join $tb_dotqmail on $tb_userpref.username=$tb_dotqmail.username WHERE preference LIKE 'spam_enabled' AND $tb_userpref.ts NOT LIKE $tb_dotqmail.lastprefchange AND $tb_dotqmail.error NOT LIKE '1' ORDER BY username");
$query->execute or die "sql query error";
$i = 0;

print ".\nCHANGED: " if $debug;
while($data = $query->fetchrow_hashref) {
	$changed[$i++] = $$data{"username"};
	print $$data{"username"} . " " if $debug;

	$changed_data{($$data{"username"})} = { 
		'md5sum' => $$data{"md5sum"},
		'filename' => $$data{"filename"},
		'lastprefchange' => $$data{"lastprefchange"},
		'active' => $$data{"active"},
		'error' => $$data{"error"}
	};
}


$query = $db->prepare("select $tb_userpref.username as username,$tb_dotqmail.md5sum as md5sum,$tb_dotqmail.filename as filename,$tb_dotqmail.lastprefchange as lastprefchange,$tb_dotqmail.active as active,$tb_dotqmail.error as error from $tb_userpref left join $tb_dotqmail on $tb_userpref.username=$tb_dotqmail.username where  preference LIKE 'spam_enabled' AND $tb_userpref.ts LIKE $tb_dotqmail.lastprefchange AND $tb_dotqmail.error NOT LIKE '1' ORDER BY username");
$query->execute or die "sql query error";
$i = 0;

print ".\nNOTCHANGED: " if $debug;
while($data = $query->fetchrow_hashref) {
	$notchanged[$i++] = $$data{"username"};
	print $$data{"username"} . " " if $debug;

	$notchanged_data{($$data{"username"})} = { 
		'md5sum' => $$data{"md5sum"},
		'filename' => $$data{"filename"},
		'lastprefchange' => $$data{"lastprefchange"},
		'active' => $$data{"active"},
		'error' => $$data{"error"}
	};
}

print ".\n\n" if $debug;


#################################################

my %virtualdomains_uid = ();
my %virtualdomains_homedir = ();
my $domain = "";
my $uid = "";

# load domains list -> to fetch home directory based on mail domain

open(VDOM, $cfg_virtualdomains) || die("Error: cannot open $cfg_virtualdomains");
while (my $line = <VDOM>) {
  chomp $line;
  ($domain,$uid) = split(/:/, $line);
  $virtualdomains_uid{$domain} = $uid;
  $virtualdomains_homedir{$domain} = ((getpwnam($uid))[7]) ;
}
close(VDOM);


##############

#
# handle new accounts
#

print "ADDED:\n" if $debug;

foreach $email (@added) {

	$username = "";
	$domain = "";
	$homedir = "";
	$dotfile = "";

	$spam_trash = 0;
	$spam_fwd = "";
	$spam_enabled = 0;
	$spam_ts = 0;
	$md5sum = "";

	if ($email =~ /^(.*)@(.*)$/) {
		$username = $1;
		$username_encoded = $1;
		$username_encoded =~ s/\./\:/g;  
		$domain = $2;
	} else {
		next;
	}

	$uid = $virtualdomains_uid{$domain}; 
	$homedir = $virtualdomains_homedir{$domain}; 
	$file = $homedir . "/" . $dot_qmail_prefix . $username_encoded;

	if (!$homedir || !$uid) { next; }

	print "$email -> $file in $homedir [$uid]\n" if $debug;

	# retrieve SA infos

	$query = $db->prepare("SELECT value,ts FROM $tb_userpref WHERE preference LIKE 'spam_enabled' AND username LIKE '$email'");
	$query->execute or die "sql query error";
	if (@datarow = $query->fetchrow_array) {
		$spam_enabled = $datarow[0];
		$spam_ts = $datarow[1];
	}

	$query = $db->prepare("SELECT value FROM $tb_userpref WHERE preference LIKE 'spam_trash' AND username LIKE '$email'");
	$query->execute or die "sql query error";
	if (@datarow = $query->fetchrow_array) {
		$spam_trash = $datarow[0];
	}

	$query = $db->prepare("SELECT value FROM $tb_userpref WHERE preference LIKE 'spam_fwd' AND username LIKE '$email'");
	$query->execute or die "sql query error";
	if (@datarow = $query->fetchrow_array) {
		$spam_fwd = $datarow[0];
	}

	print "spam_enabled: $spam_enabled - spam_trash = $spam_trash - spam_fwd = $spam_fwd\n" if $debug;

	# create the dot qmail file

	$error = 0;
	
	if ($spam_enabled && !(-f $file)) {  # only create a file if user really turned the system on

		$template = "# =================================================================================\n";
		$template .= "# $file generated on " . localtime() . "\n";
		$template .= "# by oSpam version $version.$internal_version for <$email> [$uid]\n";
		$template .= "# ___ DO NOT EDIT ___      -== http://ospam.omnis.ch/ ==- \n";
		$template .= "# \n";

		if ($spam_trash && $spam_fwd eq "") { $template .= $t_scan_and_trash; } 
		elsif ($spam_trash && $spam_fwd ne "") { $template .= $t_scan_and_fwdonly; }
		elsif (!$spam_trash && $spam_fwd eq "") { $template .= $t_scan_only; }
		elsif (!$spam_trash && $spam_fwd ne "") { $template .= $t_scan_and_fwd_and_deliver; }
		else { $template .= $t_scan_only; }   # shouldn't happen, but we all know Murphy :)		

		$template .= "# \n";
		$template .= "# =================================================================================\n";
	
		# parse template

		$template =~ s/___USERADDR___/$email/sg;
		$template =~ s/___FWDADDR___/$spam_fwd/sg;

		print "---[dotqmail]----\n$template---[/dotqmail]----\n" if $debug;		

		if (open("DOTQMAIL", ">$file")) {
			print DOTQMAIL $template;
			close (DOTQMAIL);
			system($cmd_chown, $uid, $file);
			system($cmd_chmod, "0600", $file);
			$md5sum = substr(`$cmd_md5sum $file`,0,32);

			print "file created: $file - md5sum = $md5sum\n" if $debug;
			
			# update db

			$history = "Created and activated on " . localtime(); 			
			$query = "INSERT INTO $tb_dotqmail (username,filename,lastprefchange,md5sum,active,history) ";
			$query .= "VALUES ('$email','$file','$spam_ts','$md5sum','1','$history')";
			$result = $db->do($query) or do { print "\nERR: $DBI::errstr\n\n"; };

			# send mail confirmation

                        open(SENDMAIL, "| $cmd_sendmail $sendmail_opt 1>&2") || die "Can't run $cmd_sendmail!";
			print SENDMAIL "To: $email\n";
			print SENDMAIL "From: $mail_sender\n";
			print SENDMAIL "Bcc: $debug_bcc_mail\n" if $debug_bcc_mail;
                        print SENDMAIL "Subject: [oSpam] spam system activated!\n";
                        print SENDMAIL "\n";
                        print SENDMAIL "Selected settings:\n";
                        print SENDMAIL "- mails are scanned against spam\n" if $spam_enabled;
                        print SENDMAIL "- spams are not delivered to your account\n" if $spam_trash;
                        print SENDMAIL "- spams will be delivered 'tagged' to your account\n" if !$spam_trash;
                        print SENDMAIL "- spams are forwarded to another account: $spam_fwd\n" if $spam_fwd;
                        print SENDMAIL "\n";
                        print SENDMAIL "Thanks for trying oSpam :)\n";
                        print SENDMAIL "--sysadmin\n";
                        print SENDMAIL "\n";
			close(SENDMAIL);

		} else {

			$error = 1;
			$history = "Error on creation " . localtime(); 			
			$msg = "Couldn't create the $file file: permission problems?\n";
		}
		
	} elsif ($spam_enabled && (-f $file)) { 
			
		$error = 1;
		$history = "Error on creation: .qmail- file already exists " . localtime();

	} else {

		# spam_enabled = 0, but we still update our DB for consistancy.

		$history = "Creating inactive account on " . localtime(); 			
		$query = "INSERT INTO $tb_dotqmail (username,filename,lastprefchange,md5sum,active,error,history) ";
		$query .= "VALUES ('$email','$file','$spam_ts','','0','0','$history')";
		$result = $db->do($query) or do { print "\nERR: $DBI::errstr\n\n"; };
	}

	
	
	if ($error) {
		$query = "INSERT INTO $tb_dotqmail (username,filename,lastprefchange,md5sum,active,error,history) ";
		$query .= "VALUES ('$email','$file','$spam_ts','','0','1','$history')";
		$result = $db->do($query) or do { print "\nERR: $DBI::errstr\n\n"; };
		
		open(SENDMAIL, "| $cmd_sendmail $sendmail_opt 1>&2") || die "Can't run $cmd_sendmail!";
		print SENDMAIL "To: $email\n";
		print SENDMAIL "From: $mail_sender\n";
		print SENDMAIL "Bcc: $debug_bcc_mail\n" if $debug_bcc_mail;
		print SENDMAIL "Subject: [oSpam] spam system: activation failed!\n";
		print SENDMAIL "\n";
		print SENDMAIL "$msg\n";
		print SENDMAIL "\n";
		print SENDMAIL "Please contact your mail server administrator.\n";
		print SENDMAIL "\n";
		print SENDMAIL "Selected settings:\n";
		print SENDMAIL "- mails are scanned against spam\n" if $spam_enabled;
		print SENDMAIL "- spams are not delivered to your account\n" if $spam_trash;
		print SENDMAIL "- spams will be delivered 'tagged' to your account\n" if !$spam_trash;
		print SENDMAIL "- spams are forwarded to another account: $spam_fwd\n" if $spam_fwd;
		print SENDMAIL "\n";
		print SENDMAIL "Thanks for trying oSpam :)\n";
		print SENDMAIL "--sysadmin\n";
		print SENDMAIL "\n";
		close(SENDMAIL);

	}

	print "\n\n" if $debug;

}

##############

#
# handle deleted accounts (= accounts who are not in userpref table anymore: occurs on mail address deletion)
#

print "REMOVED:\n" if $debug;

foreach $email (@removed) {

	$file = $removed_data{$email}->{'filename'};
	$md5sum = $removed_data{$email}->{'md5sum'};

	if ($removed_data{$email}->{'active'}) {	

		print "$email -> $file: " if $debug;
		$error = 0;

		if (-f $file) { 
			# check if md5sum maches
			$md5sum_check = substr(`$cmd_md5sum $file`,0,32);
	
			if ($md5sum eq $md5sum_check) { 
				print "md5sum matches!  ($md5sum)\n" if $debug;
			} else {
				print "md5sum differnce: DB $md5sum =! FILE $md5sum_check)\n" if $debug;
				$msg = "active account md5 mismatch: differnce: DB $md5sum =! FILE $md5sum_check for $email\n";
				$log .= $msg;
				$error = 1;
			}
		} else {
			$msg = "active account: file $file deleted?! $email\n";
			$log .= $msg;
		}

		if ($error) {
	
			$query = $db->prepare("SELECT history FROM $tb_dotqmail WHERE username LIKE '$email'");
			$query->execute or die "sql query error";
			if (@datarow = $query->fetchrow_array) {
				$history = $datarow[0];
			}

			$history .= "\nError: removed, " . $msg;
			$query = "UPDATE $tb_dotqmail SET history='$history',error='1',active='0' WHERE username LIKE '$email'";
			$result = $db->do($query) or do { print "\nERR: $DBI::errstr\n\n"; };

		} else {

			unlink($file);
			$query = "DELETE FROM $tb_dotqmail WHERE username LIKE '$email'";
			$result = $db->do($query) or do { print "\nERR: $DBI::errstr\n\n"; };
		}
	}
}


##############

#
# handle notchanged accounts
#

# -> check if file still there, md5, timestamp

print "NOT CHANGED:\n" if $debug;

foreach $email (@notchanged) {

	$file = $notchanged_data{$email}->{'filename'};
	$md5sum = $notchanged_data{$email}->{'md5sum'};

	if ($notchanged_data{$email}->{'active'}) {	

		print "$email -> $file: " if $debug;

		$error = 0;

		if (-f $file) { 
			# check if md5sum maches
			$md5sum_check = substr(`$cmd_md5sum $file`,0,32);
	
			if ($md5sum eq $md5sum_check) { 
				print "md5sum matches!  ($md5sum)\n" if $debug;
			} else {
				print "md5sum differnce: DB $md5sum =! FILE $md5sum_check)\n" if $debug;
				$msg = "active account md5 mismatch: differnce: DB $md5sum =! FILE $md5sum_check for $email\n";
				$log .= $msg;
				$error = 1;
			}
		} else {
			$msg = "active account: file $file deleted?! $email\n";
			$log .= $msg;
		}

		if ($error) {
	
			$query = $db->prepare("SELECT history FROM $tb_dotqmail WHERE username LIKE '$email'");
			$query->execute or die "sql query error";
			if (@datarow = $query->fetchrow_array) {
				$history = $datarow[0];
			}

			$history .= "\nError: not_changed, " . $msg;
			$query = "UPDATE $tb_dotqmail SET history='$history',error='1',active='0' WHERE username LIKE '$email'";
			$result = $db->do($query) or do { print "\nERR: $DBI::errstr\n\n"; };
		}
	}
}

##############

#
# handle changed accounts
#

# -> check if file still there, md5, timestamp

print "CHANGED:\n" if $debug;

foreach $email (@changed) {

	$file = $changed_data{$email}->{'filename'};
	$md5sum = $changed_data{$email}->{'md5sum'};
	$active = $changed_data{$email}->{'active'};

	print "$email -> $file: " if $debug;

	if ($active) {	

		$error = 0;

		if (-f $file) { 
			# check if md5sum maches
			$md5sum_check = substr(`$cmd_md5sum $file`,0,32);
	
			if ($md5sum eq $md5sum_check) { 
				print "md5sum matches!  ($md5sum)\n" if $debug;
			} else {
				print "md5sum differnce: DB $md5sum =! FILE $md5sum_check)\n" if $debug;
				$msg = "active account md5 mismatch: differnce: DB $md5sum =! FILE $md5sum_check for $email\n";
				$log .= $msg;
				$error = 1;
			}
		} else {
			$msg = "active account: file $file deleted?! $email\n";
			$log .= $msg;
		}

		if ($error) {
	
			$query = $db->prepare("SELECT history FROM $tb_dotqmail WHERE username LIKE '$email'");
			$query->execute or die "sql query error";
			if (@datarow = $query->fetchrow_array) {
				$history = $datarow[0];
			}

			$history .= "\nError: changed, " . $msg;
			$query = "UPDATE $tb_dotqmail SET history='$history',error='1',active='0' WHERE username LIKE '$email'";
			$result = $db->do($query) or do { print "\nERR: $DBI::errstr\n\n"; };

		} 

	}
	
	if (!$error) {

			# no error, let's update the file

			$spam_trash = 0;
			$spam_fwd = "";
			$spam_enabled = 0;
			$spam_ts = 0;
			$md5sum = "";

			if ($email =~ /^(.*)@(.*)$/) {
				$username = $1;
				$username_encoded = $1;
				$username_encoded =~ s/\./\:/g;  
				$domain = $2;
			} else {
				next;
			}

			$uid = $virtualdomains_uid{$domain}; 
			$homedir = $virtualdomains_homedir{$domain}; 
			$file = $homedir . "/" . $dot_qmail_prefix . $username_encoded;

			print "$email -> $file in $homedir [$uid]\n" if $debug;

			# retrieve SA infos
	
			$query = $db->prepare("SELECT value,ts FROM $tb_userpref WHERE preference LIKE 'spam_enabled' AND username LIKE '$email'");
			$query->execute or die "sql query error";
			if (@datarow = $query->fetchrow_array) {
				$spam_enabled = $datarow[0];
				$spam_ts = $datarow[1];
			}

			$query = $db->prepare("SELECT value FROM $tb_userpref WHERE preference LIKE 'spam_trash' AND username LIKE '$email'");
			$query->execute or die "sql query error";
			if (@datarow = $query->fetchrow_array) {
				$spam_trash = $datarow[0];
			}

			$query = $db->prepare("SELECT value FROM $tb_userpref WHERE preference LIKE 'spam_fwd' AND username LIKE '$email'");
			$query->execute or die "sql query error";
			if (@datarow = $query->fetchrow_array) {
				$spam_fwd = $datarow[0];
			}

			print "spam_enabled: $spam_enabled - spam_trash = $spam_trash - spam_fwd = $spam_fwd\n" if $debug;

			# re-create the dot qmail file
			
			if ($spam_enabled) {  # only create a file if user really turned the system on

				$template = "# =================================================================================\n";
				$template .= "# $file generated on " . localtime() . "\n";
				$template .= "# by oSpam version $version.$internal_version for <$email> [$uid]\n";
				$template .= "# ___ DO NOT EDIT ___      -== http://ospam.omnis.ch/ ==- \n";
				$template .= "# \n";

				if ($spam_trash && $spam_fwd eq "") { $template .= $t_scan_and_trash; } 
				elsif ($spam_trash && $spam_fwd ne "") { $template .= $t_scan_and_fwdonly; }
				elsif (!$spam_trash && $spam_fwd eq "") { $template .= $t_scan_only; }
				elsif (!$spam_trash && $spam_fwd ne "") { $template .= $t_scan_and_fwd_and_deliver; }
				else { $template .= $t_scan_only; }   # shouldn't happen, but we all know Murphy :)		

				$template .= "# \n";
				$template .= "# =================================================================================\n";
	
				# parse template

				$template =~ s/___USERADDR___/$email/sg;
				$template =~ s/___FWDADDR___/$spam_fwd/sg;

				print "---[dotqmail]----\n$template---[/dotqmail]----\n" if $debug;		

				if (open("DOTQMAIL", ">$file")) {
					print DOTQMAIL $template;
					close (DOTQMAIL);
					system($cmd_chown, $uid, $file);
					system($cmd_chmod, "0600", $file);
					$md5sum = substr(`$cmd_md5sum $file`,0,32);

					print "file updated: $file - md5sum = $md5sum\n" if $debug;
			
					# update db

					$query = $db->prepare("SELECT history FROM $tb_dotqmail WHERE username LIKE '$email'");
					$query->execute or die "sql query error";
					if (@datarow = $query->fetchrow_array) {
						$history = $datarow[0];
					}

	
					$history .= "\nSettings updated on " . localtime(); 

					$query = "UPDATE $tb_dotqmail SET md5sum='$md5sum',history='$history',filename='$file',lastprefchange='$spam_ts',error='0',active='1' WHERE username LIKE '$email'";
					$result = $db->do($query) or do { print "\nERR: $DBI::errstr\n\n"; };

					# send mail confirmation

		                        open(SENDMAIL, "| $cmd_sendmail $sendmail_opt 1>&2") || die "Can't run $cmd_sendmail!";
					print SENDMAIL "To: $email\n";
					print SENDMAIL "From: $mail_sender\n";
					print SENDMAIL "Bcc: $debug_bcc_mail\n" if $debug_bcc_mail;
                        		print SENDMAIL "Subject: [oSpam] spam system updated!\n";
                        		print SENDMAIL "\n";
                        		print SENDMAIL "Selected settings:\n";
                        		print SENDMAIL "- mails are scanned against spam\n" if $spam_enabled;
                        		print SENDMAIL "- spams are not delivered to your account\n" if $spam_trash;
                        		print SENDMAIL "- spams will be delivered 'tagged' to your account\n" if !$spam_trash;
                        		print SENDMAIL "- spams are forwarded to another account: $spam_fwd\n" if $spam_fwd;
                        		print SENDMAIL "\n";
                        		print SENDMAIL "Thanks for trying oSpam :)\n";
                        		print SENDMAIL "--sysadmin\n";
                        		print SENDMAIL "\n";
					close(SENDMAIL);

				} else {



					$query = $db->prepare("SELECT history FROM $tb_dotqmail WHERE username LIKE '$email'");
					$query->execute or die "sql query error";
					if (@datarow = $query->fetchrow_array) {
						$history = $datarow[0];
					}

					$history .= "\nsettings update FAILED on " . localtime(); 

					$query = "UPDATE $tb_dotqmail SET history='$history',error='1',active='0' WHERE username LIKE '$email'";
					$result = $db->do($query) or do { print "\nERR: $DBI::errstr\n\n"; };

		                        open(SENDMAIL, "| $cmd_sendmail $sendmail_opt 1>&2") || die "Can't run $cmd_sendmail!";
					print SENDMAIL "To: $email\n";
					print SENDMAIL "From: $mail_sender\n";
					print SENDMAIL "Bcc: $debug_bcc_mail\n" if $debug_bcc_mail;
                        		print SENDMAIL "Subject: [oSpam] spam system: activation failed!\n";
                        		print SENDMAIL "\n";
                        		print SENDMAIL "-> couldn't create the $file file!\n";
                        		print SENDMAIL "(maybe a file or directory permissions problem ?)\n";
                        		print SENDMAIL "\n";
                        		print SENDMAIL "\n";
                        		print SENDMAIL "Selected settings:\n";
                        		print SENDMAIL "- mails are scanned against spam\n" if $spam_enabled;
                        		print SENDMAIL "- spams are not delivered to your account\n" if $spam_trash;
                        		print SENDMAIL "- spams will be delivered 'tagged' to your account\n" if !$spam_trash;
                        		print SENDMAIL "- spams are forwarded to another account: $spam_fwd\n" if $spam_fwd;
                        		print SENDMAIL "\n";
                        		print SENDMAIL "Thanks for trying oSpam :)\n";
                        		print SENDMAIL "--sysadmin\n";
                        		print SENDMAIL "\n";
					close(SENDMAIL);

				}

			} else {

				# not enables: remove the file!

				unlink($file);
				print "removed $file for $email - turned off SA\n" if $debug;

				# update db

				$query = $db->prepare("SELECT history FROM $tb_dotqmail WHERE username LIKE '$email'");
				$query->execute or die "sql query error";
				if (@datarow = $query->fetchrow_array) {
					$history = $datarow[0];
				}

	
				$history .= "\noSpam stopped (removed .qmail file) on " . localtime(); 

				$query = "UPDATE $tb_dotqmail SET md5sum='0',history='$history',filename='$file',lastprefchange='$spam_ts',error='0',active='0' WHERE username LIKE '$email'";
				$result = $db->do($query) or do { print "\nERR: $DBI::errstr\n\n"; };

				# send mail confirmation

		                open(SENDMAIL, "| $cmd_sendmail $sendmail_opt 1>&2") || die "Can't run $cmd_sendmail!";
				print SENDMAIL "To: $email\n";
				print SENDMAIL "From: $mail_sender\n";
				print SENDMAIL "Bcc: $debug_bcc_mail\n" if $debug_bcc_mail;
                       		print SENDMAIL "Subject: [oSpam] spam system stopped!\n";
                       		print SENDMAIL "\n";
                       		print SENDMAIL "From now all mails are going to be delivered without\n";
                       		print SENDMAIL "going through the anti-spam system. \n";
                       		print SENDMAIL "\n";
                       		print SENDMAIL "Thanks for trying oSpam :)\n";
                       		print SENDMAIL "--sysadmin\n";
                       		print SENDMAIL "\n";
				close(SENDMAIL);
		}

	}
}

print "LOG: \n" . $log . "\n\n";