#!/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-messages,v 1.4 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);

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-messages, 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 write anything.

EOF
	exit 0;
}

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

EOF
	exit 0;
}

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

if(!$quiet)
{
	print STDOUT "lms-notify-messages, 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';

# debtors notify
my $limit = $ini->val('notify-messages', 'limit') || 0;
my $debtors_file = $ini->val('notify-messages', 'debtors_template');
# new debit note notify
my $notes_file = $ini->val('notify-messages', 'notes_template');
# new invoice notify
my $invoices_file = $ini->val('notify-messages', 'invoices_template');
# deadline notify
my $deadline_file = $ini->val('notify-messages', 'deadline_template');

if($debtors_file && ! -r $debtors_file)
{
	print STDERR "Fatal error: unable to read message template file $debtors_file, exiting!\n";
	exit 1;
}
if($invoices_file && ! -r $invoices_file)
{
	print STDERR "Fatal error: unable to read message template file $invoices_file, exiting!\n";
	exit 1;
}
if($notes_file && ! -r $notes_file)
{
	print STDERR "Fatal error: unable to read message template file $notes_file, exiting!\n";
	exit 1;
}
if($deadline_file && ! -r $deadline_file)
{
	print STDERR "Fatal error: unable to read message template file $deadline_file, 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 });
	$dbase->do("SET CLIENT_ENCODING TO 'UNICODE'");
	$utsfmt = "EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))";
}
else
{
	print STDERR "Fatal error: unsupported database type: $dbtype, exiting.\n";
	exit 1;
}

# ------------------------------------------------------------------------------
# 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;
}

my $add_msg = $dbase->prepare('UPDATE customers SET message = ? WHERE id = ?');
my $set_warn = $dbase->prepare('UPDATE nodes SET warning = 1 WHERE ownerid = ?');

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

	my $dbq = $dbase->prepare("SELECT c.id, c.pin, c.lastname, c.name,
			SUM(value) AS balance
		FROM customers c
		JOIN cash ON (c.id = cash.customerid)
		WHERE c.deleted = 0
		GROUP BY c.id, c.pin, c.lastname, c.name
		HAVING SUM(value) < $limit");
	$dbq->execute();

	while (my $row = $dbq->fetchrow_hashref())
	{
		my $name = $row->{'name'}.' '.$row->{'lastname'};
		my $txt = parse_data($template, $row);

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

		if(!$debug && $txt)
		{
			$add_msg->execute($txt, $row->{'id'});
			$set_warn->execute($row->{'id'});
		}
	}
	$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,
		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
		");
	$dbq->execute();

	while (my $row = $dbq->fetchrow_hashref())
	{
		$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'}));

		my $txt = parse_data($template, $row);
			
		if(!$quiet)
		{
			printf STDOUT "[new invoice] %s (%04d) %s\n",
				$row->{'name'}, $row->{'id'}, $row->{'doc_number'};
		}
			
		if(!$debug && $txt)
		{
			$add_msg->execute($txt, $row->{'id'});
			$set_warn->execute($row->{'id'});
		}
	}
	$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,
		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 d.cdate > $utsfmt - ((d.paytime + 1) * 86400)
			AND d.cdate < $utsfmt - (d.paytime * 86400)
		");
	$dbq->execute();

	while (my $row = $dbq->fetchrow_hashref())
	{
		$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'}));
			
		my $txt = parse_data($template, $row);

		if(!$quiet)
		{
			printf STDOUT "[deadline] %s (%04d) %s\n",
				$row->{'name'}, $row->{'id'}, $row->{'doc_number'};
		}
			
		if(!$debug && $txt)
		{
			$add_msg->execute($txt, $row->{'id'});
			$set_warn->execute($row->{'id'});
		}
	}
	$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,
		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
		");
	$dbq->execute();

	while (my $row = $dbq->fetchrow_hashref())
	{
		$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'}));
			
		my $txt = parse_data($template, $row);

		if(!$quiet)
		{
			printf STDOUT "[new debit note] %s (%04d) %s\n",
				$row->{'name'}, $row->{'id'}, $row->{'doc_number'};
		}
			
		if(!$debug && $txt)
		{
			$add_msg->execute($txt, $row->{'id'});
			$set_warn->execute($row->{'id'});
		}
	}
	$dbq->finish();
}

$add_msg->finish();
$set_warn->finish();
$dbase->disconnect();
