#!/usr/bin/perl -Tw
#
#  LMS version 1.11.13 Dira
#
#  Copyright (C) 2001-2011 LMS Developers
#
#  Please, see the doc/AUTHORS for more information about authors!
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License Version 2 as
#  published by the Free Software Foundation.
#
#  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.
#
#  $Id: lms-notify,v 1.73 2011/01/18 08:11:58 alec Exp $

use strict;
use DBI;
use Config::IniFiles;
use Getopt::Long;
use vars qw($configfile $quiet $help $version $debug);
use POSIX qw(strftime mktime);
use MIME::QuotedPrint;
use Mail::Sender;


my $_version = '1.11.13 Dira';

my %options = (
	"--config-file|C=s"	=>	\$configfile,
	"--quiet|q"		=>	\$quiet,
	"--help|h"		=>	\$help,
	"--version|v"		=>	\$version,
	"--debug|d"             =>      \$debug
);

Getopt::Long::config("no_ignore_case");
GetOptions(%options);

if($help)
{
	print STDERR <<EOF;
lms-notify, version $_version
(C) 2001-2011 LMS Developers

-C, --config-file=/etc/lms/lms.ini	alternate config file (default: /etc/lms/lms.ini);
-h, --help			print this help and exit;
-v, --version			print version info and exit;
-q, --quiet			suppress any output, except errors;
-d, --debug                     do debugging, don't send anything.

EOF
	exit 0;
}

if($version)
{
	print STDERR <<EOF;
lms-notify, version $_version
(C) 2001-2011 LMS Developers

EOF
	exit 0;
}

if(!$configfile)
{
	$configfile = "/etc/lms/lms.ini";
}

if(!$quiet)
{
	print STDOUT "lms-notify, version $_version\n";
	print STDOUT "(C) 2001-2011 LMS Developers\n";
	print STDOUT "Using file $configfile as config.\n";
}

if(! -r $configfile)
{
	print STDERR "Fatal error: Unable to read configuration file $configfile, exiting.\n";
	exit 1;
}

my $ini = new Config::IniFiles -file => $configfile;
print @Config::IniFiles::errors;

my $dbtype = $ini->val('database', 'type') || 'mysql';
my $dbhost = $ini->val('database', 'host') || 'localhost';
my $dbuser = $ini->val('database', 'user') || 'root';
my $dbpasswd = $ini->val('database', 'password') || '';
my $dbname = $ini->val('database', 'database') || 'lms';

my $smtp_host = $ini->val('notify', 'smtp_host') || 'localhost';
my $smtp_user = $ini->val('notify', 'smtp_user') || '';
my $smtp_pass = $ini->val('notify', 'smtp_pass') || '';
my $smtp_auth = $ini->val('notify', 'smtp_auth') || ''; # 'LOGIN', 'PLAIN', 'CRAM-MD5', 'NTLM'
my $debugemail = $ini->val('notify', 'debug_email') || '';
my $mailfrom = $ini->val('notify', 'mailfrom') || '';
my $mailfname = $ini->val('notify', 'mailfname') || '';

# debtors notify
my $limit = $ini->val('notify', 'limit') || 0;
my $debtors_file = $ini->val('notify', 'debtors_template');
my $debtors_subject = $ini->val('notify', 'debtors_subject') || 'Debtors notification';
# new debit note notify
my $notes_file = $ini->val('notify', 'notes_template');
my $notes_subject = $ini->val('notify', 'notes_subject') || 'New debit note notification';
# new invoice notify
my $invoices_file = $ini->val('notify', 'invoices_template');
my $invoices_subject = $ini->val('notify', 'invoices_subject') || 'New invoice notification';
# deadline notify
my $deadline_file = $ini->val('notify', 'deadline_template');
my $deadline_subject = $ini->val('notify', 'deadline_subject') || 'Invoice deadline notification';

if(!$mailfrom)
{
	print STDERR "Fatal error: mailfrom unset! Can't continue, exiting.\n";
	exit 1;
}

if($debtors_file && ! -r $debtors_file)
{
	print STDERR "Fatal error: unable to read mail template file $debtors_file, exiting!\n";
	exit 1;
}
if($invoices_file && ! -r $invoices_file)
{
	print STDERR "Fatal error: unable to read mail template file $invoices_file, exiting!\n";
	exit 1;
}
if($notes_file && ! -r $notes_file)
{
	print STDERR "Fatal error: unable to read mail template file $notes_file, exiting!\n";
	exit 1;
}
if($deadline_file && ! -r $deadline_file)
{
	print STDERR "Fatal error: unable to read mail template file $deadline_file, exiting!\n";
	exit 1;
}

if($smtp_auth && $smtp_auth !~ /LOGIN|PLAIN|CRAM-MD5|NTLM/i)
{
        print STDERR "Fatal error: smtp_auth setting not supported! Can't continue, exiting.\n";
        exit 1;
}

my $dbase;
my $utsfmt;

if($dbtype =~ /mysql/)
{
	$dbase = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost","$dbuser","$dbpasswd", { RaiseError => 1 });
	$dbase->do("SET NAMES utf8");
	$utsfmt = "UNIX_TIMESTAMP()";
}
elsif($dbtype eq "postgres")
{
	$dbase = DBI->connect("DBI:Pg:dbname=$dbname;host=$dbhost","$dbuser","$dbpasswd", { RaiseError => 1 });
	$utsfmt = "EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))";
}
else
{
	print STDERR "Fatal error: unsupported database type: $dbtype, exiting.\n";
	exit 1;
}

sub encode_header
{
	my $subject = shift;
	$subject = encode_qp($subject, '');
	$subject =~ s/ /_/g;
	# @TODO: split long lines
	return "=?UTF-8?Q?$subject?=";
}

my $from = $mailfrom;

if($mailfname)
{
        $mailfname = encode_header($mailfname);
	$from = "$mailfname <$from>";
}

$Mail::Sender::NO_X_MAILER = 1;
$Mail::Sender::SITE_HEADERS = "X-Mailer: lms-notify v.$_version";

# ------------------------------------------------------------------------------
# FUNCTIONS
# ------------------------------------------------------------------------------
sub parse_data
{
	my $data = shift;
	my $row = shift;

	if ($data =~ /\%b/) {
		my $amount = -$row->{'balance'};
		$data =~ s/\%b/$amount/g; 
	}
	if ($data =~ /\%date-y/) {
		my $year = strftime("%Y", localtime());
		$data =~ s/\%date-y/$year/g; 
	}
	if ($data =~ /\%date-m/) {
		my $month = strftime("%m", localtime());
		$data =~ s/\%date-m/$month/g;
	}
	if ($data =~ /\%date_month_name/) {
		my $month_name = strftime("%B", localtime());
		$data =~ s/\%date_month_name/$month_name/g;
	}
	if ($data =~ /\%B/) {
		$data =~ s/\%B/$row->{'balance'}/g;
	}
	if ($data =~ /\%saldo/) {
		$data =~ s/\%saldo/$row->{'balance'}/g;
	}
	if ($data =~ /\%pin/) {
		$data =~ s/\%pin/$row->{'pin'}/g;
	}
	if ($data =~ /\%cid/) {
		$data =~ s/\%cid/$row->{'id'}/g;
	}
	if ($data =~ /\%abonament/) {
		my $saldo_dbq = $dbase->prepare("SELECT SUM(value) AS value
			FROM assignments, tariffs
			WHERE tariffid = tariffs.id AND customerid = ?
				AND (datefrom <= $utsfmt OR datefrom = 0)
				AND (dateto > $utsfmt OR dateto = 0)
				AND ((datefrom < dateto) OR (datefrom = 0 AND datefrom = 0))");
		$saldo_dbq->execute($row->{'id'});
		my $ow_s = $saldo_dbq->fetchrow_hashref();
		$data =~ s/\%abonament/$ow_s->{'value'}/g;
		$saldo_dbq->finish();
	}
	if ($data =~ /\%last_10_in_a_table/ ) {
		my $last10_dbq = $dbase->prepare("SELECT comment, value, time FROM cash WHERE 
				customerid = $row->{'id'} ORDER BY time DESC LIMIT 10");
		# ok, now we are going to rise up system's load
		$last10_dbq->execute();
		my $l10 = "-----------+-----------+----------------------------------------------------\n";
		while (my $row_s = $last10_dbq->fetchrow_hashref())
		{
			my $op_time = strftime( "%Y/%m/%d", localtime($row_s->{'time'}));
			my $op_amount = sprintf("%9.2f", $row_s->{'value'});
			my $for_what = sprintf("%-52s",$row_s->{'comment'} || '');
		
			$l10 = $l10."$op_time | $op_amount | $for_what \n";
		}
		$last10_dbq->finish();
		$l10 = $l10."-----------+-----------+----------------------------------------------------\n";
		$data =~ s/\%last_10_in_a_table/$l10/;
	} 
# invoices, debit notes
	if ($data =~ /\%invoice/) { # for backward compat.
		$data =~ s/\%invoice/$row->{'doc_number'}/g;
	}
	if ($data =~ /\%number/) {
		$data =~ s/\%number/$row->{'doc_number'}/g;
	}
	if ($data =~ /\%value/) {
		$data =~ s/\%value/$row->{'value'}/g;
	}

	return $data;
}

sub send_mail
{
	my ($rmail, $rname, $subject, $body) = @_;
		
	(new Mail::Sender)->MailMsg ({
	        smtp => $smtp_host,
		from => $mailfrom,
		auth => $smtp_auth,
		authid => $smtp_user,
		authpwd => $smtp_pass,
		on_errors => undef,
#		debug_level => 4,
#		debug => './log.txt',
                subject => encode_header($subject),
		fake_from => $from,
		to => $rmail,
		fake_to => encode_header($rname).' <'.$rmail.'>',
		replyto => $mailfrom,
		ctype => 'text/plain',
    		encoding => 'quoted-printable',
		charset => 'UTF-8',
		msg => "$body\n",
	}) or return $Mail::Sender::Error;
	
	return 0;
}

sub send_message
{
	my ($msgid, $cid, $rmail, $rname, $subject, $body) = @_;

	my $dbqi = $dbase->prepare("INSERT INTO messageitems
		(messageid, customerid, destination, status)
		VALUES (?, ?, ?, ?)");
	$dbqi->execute($msgid, $cid, $rmail, 1);
	
	my $result = send_mail($rmail, $rname, $subject, $body);
	my $time = mktime(localtime());

	$dbqi = $dbase->prepare("UPDATE messageitems
		SET status = ?, lastdate = ?, error = ?
		WHERE messageid = ? AND customerid = ?");

	if ($result) {
		$dbqi->execute(3, $time, $result, $msgid, $cid);
	} else { # MSG_SENT
		$dbqi->execute(2, $time, undef, $msgid, $cid);
	}
	$dbqi->finish();
}

sub create_message
{
	my $subject = shift;
	my $template = shift;
	my $time = mktime(localtime());

	my $dbqi = $dbase->prepare("INSERT INTO messages
			(type, cdate, subject, body) VALUES (1, ?, ?, ?)");
	$dbqi->execute($time, $subject, $template);
	$dbqi = $dbase->prepare("SELECT id FROM messages
			WHERE cdate = ? AND type = 1 AND subject = ?");
	$dbqi->execute($time, $subject);
	my $msgid = ($dbqi->fetchrow_array)[0];
	$dbqi->finish();

	return $msgid;
}

# ------------------------------------------------------------------------
# ACTIONS
# ------------------------------------------------------------------------
# Debtors
if ($debtors_file)
{
	open(MAILFILE, "$debtors_file");
	my $template = join('', <MAILFILE>);
	close MAILFILE;

	# @TODO: check 'messages' table and don't send notifies to often
	my $dbq = $dbase->prepare("SELECT c.id, c.pin, c.lastname, c.name,
			SUM(value) AS balance, c.email
		FROM customers c
		JOIN cash ON (c.id = cash.customerid)
		WHERE c.deleted = 0 AND c.email != ''
		GROUP BY c.id, c.pin, c.lastname, c.name, c.email
		HAVING SUM(value) < $limit");
	$dbq->execute();

	my $msgid;
	if ($dbq->rows && !$debug) {
		$msgid = create_message($debtors_subject, $template);
	}

	while (my $row = $dbq->fetchrow_hashref())
	{
		my $recipient_name = $row->{'name'}.' '.$row->{'lastname'};
		my $recipient_mail = $debugemail || $row->{'email'};

		if(!$quiet)
		{
			printf STDOUT "[debt] %s (%04d): %s\n",
				$recipient_name, $row->{'id'}, $recipient_mail;
		}

		if(!$debug)
		{
			send_message($msgid, $row->{'id'}, $recipient_mail, $recipient_name,
				$debtors_subject, parse_data($template, $row));
		}
	}
	$dbq->finish();
}

# Invoices created up to 24 hours ago
if ($invoices_file)
{
	open(MAILFILE, "$invoices_file");
	my $template = join('', <MAILFILE>);
	close MAILFILE;

	my $dbq = $dbase->prepare("SELECT d.id AS docid, c.id, c.pin, d.name,
		d.number, n.template, d.cdate, d.paytime, c.email,
		COALESCE(ca.balance, 0) AS balance, v.value
		FROM documents d
		JOIN customers c ON (c.id = d.customerid)
		JOIN (SELECT SUM(value) * -1 AS value, docid
			FROM cash
			GROUP BY docid
		) v ON (v.docid = d.id)
		LEFT JOIN numberplans n ON (d.numberplanid = n.id)
		LEFT JOIN (SELECT SUM(value) AS balance, customerid
			FROM cash
			GROUP BY customerid
		) ca ON (ca.customerid = d.customerid)
		WHERE d.type = 1
	    		AND d.cdate > $utsfmt - 86400
			AND c.email != ''
		");
	$dbq->execute();

	my $msgid;
	if ($dbq->rows && !$debug) {
		$msgid = create_message($invoices_subject, $template);
	}

	while (my $row = $dbq->fetchrow_hashref())
	{
		my $recipient_name = $row->{'name'};
		my $recipient_mail = $debugemail || $row->{'email'};

		$row->{'doc_number'} = $row->{'template'} || '%N/LMS/%Y';
		$row->{'doc_number'} =~ s/%(\d*)N/sprintf"%0${1}d",$row->{'number'}/e;
		$row->{'doc_number'} = strftime($row->{'doc_number'}, localtime($row->{'cdate'}));
			
		if(!$quiet)
		{
			printf STDOUT "[new invoice] %s (%04d) %s: %s\n",
				$row->{'name'}, $row->{'id'}, $row->{'doc_number'}, $recipient_mail;
		}
			
		if(!$debug)
		{
			send_message($msgid, $row->{'id'}, $recipient_mail, $recipient_name,
				$invoices_subject, parse_data($template, $row));
		}
	}
	$dbq->finish();
}

# Invoices (not payed) up to 24 hours after deadline (cdate + paytime)
if ($deadline_file)
{
	open(MAILFILE, "$deadline_file");
	my $template = join('', <MAILFILE>);
	close MAILFILE;

	my $dbq = $dbase->prepare("SELECT d.id AS docid, c.id, c.pin, d.name,
		d.number, n.template, d.cdate, d.paytime, c.email,
		COALESCE(ca.balance, 0) AS balance, v.value
		FROM documents d
		JOIN (SELECT SUM(value) AS balance, customerid
			FROM cash
			GROUP BY customerid
			HAVING SUM(value) < 0
		) ca ON (ca.customerid = d.customerid)
		JOIN customers c ON (c.id = d.customerid)
		JOIN (SELECT SUM(value) * -1 AS value, docid
			FROM cash
			GROUP BY docid
		) v ON (v.docid = d.id)
		LEFT JOIN numberplans n ON (d.numberplanid = n.id)
		WHERE d.type = 1 AND d.closed = 0
			AND c.email != ''
			AND d.cdate > $utsfmt - ((d.paytime + 1) * 86400)
			AND d.cdate < $utsfmt - (d.paytime * 86400)
		");
	$dbq->execute();

	my $msgid;
	if ($dbq->rows && !$debug) {
		$msgid = create_message($deadline_subject, $template);
	}

	while (my $row = $dbq->fetchrow_hashref())
	{
		my $recipient_name = $row->{'name'};
		my $recipient_mail = $debugemail || $row->{'email'};

		$row->{'doc_number'} = $row->{'template'} || '%N/LMS/%Y';
		$row->{'doc_number'} =~ s/%(\d*)N/sprintf"%0${1}d",$row->{'number'}/e;
		$row->{'doc_number'} = strftime($row->{'doc_number'}, localtime($row->{'cdate'}));
			
		if(!$quiet)
		{
			printf STDOUT "[deadline] %s (%04d) %s: %s\n",
				$row->{'name'}, $row->{'id'}, $row->{'doc_number'}, $recipient_mail;
		}
			
		if(!$debug)
		{
			send_message($msgid, $row->{'id'}, $recipient_mail, $recipient_name,
				$deadline_subject, parse_data($template, $row));
		}
	}
	$dbq->finish();
}

# Debit notes created up to 24 hours ago
if ($notes_file)
{
	open(MAILFILE, "$notes_file");
	my $template = join('', <MAILFILE>);
	close MAILFILE;

	my $dbq = $dbase->prepare("SELECT d.id AS docid, c.id, c.pin, d.name,
		d.number, n.template, d.cdate, c.email,
		COALESCE(ca.balance, 0) AS balance, v.value
		FROM documents d
		JOIN customers c ON (c.id = d.customerid)
		JOIN (SELECT SUM(value) * -1 AS value, docid
			FROM cash
			GROUP BY docid
		) v ON (v.docid = d.id)
		LEFT JOIN numberplans n ON (d.numberplanid = n.id)
		LEFT JOIN (SELECT SUM(value) AS balance, customerid
			FROM cash
			GROUP BY customerid
		) ca ON (ca.customerid = d.customerid)
		WHERE d.type = 5
	    		AND d.cdate > $utsfmt - 86400
			AND c.email != ''
		");
	$dbq->execute();

	my $msgid;
	if ($dbq->rows && !$debug) {
		$msgid = create_message($notes_subject, $template);
	}

	while (my $row = $dbq->fetchrow_hashref())
	{
		my $recipient_name = $row->{'name'};
		my $recipient_mail = $debugemail || $row->{'email'};

		$row->{'doc_number'} = $row->{'template'} || '%N/LMS/%Y';
		$row->{'doc_number'} =~ s/%(\d*)N/sprintf"%0${1}d",$row->{'number'}/e;
		$row->{'doc_number'} = strftime($row->{'doc_number'}, localtime($row->{'cdate'}));
			
		if(!$quiet)
		{
			printf STDOUT "[new debit note] %s (%04d) %s: %s\n",
				$row->{'name'}, $row->{'id'}, $row->{'doc_number'}, $recipient_mail;
		}
			
		if(!$debug)
		{
			send_message($msgid, $row->{'id'}, $recipient_mail, $recipient_name,
				$notes_subject, parse_data($template, $row));
		}
	}
	$dbq->finish();
}

$dbase->disconnect();
