lms-notify-sms

#!/usr/bin/perl -Tw
#
#  LMS version 1.11-git
#
#  Copyright (C) 2001-2012 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$

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

$ENV{'PATH'}='/sbin:/usr/sbin:/usr/local/sbin:/bin:/usr/bin:/usr/local/bin';

my $_version = '1.11-cvs';

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-sms, version $_version
(C) 2001-2012 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-sms, version $_version
(C) 2001-2012 LMS Developers

EOF
	exit 0;
}

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

if(!$quiet)
{
	print STDOUT "lms-notify-sms, version $_version\n";
	print STDOUT "(C) Copyright 2001-2012 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-sms', 'limit') || 0;
my $debtors_file = $ini->val('notify-sms', 'debtors_template');
my $debtors_subject = $ini->val('notify-sms', 'debtors_subject') || 'Debtors notification';
# new debit note notify
my $notes_file = $ini->val('notify-sms', 'notes_template');
my $notes_subject = $ini->val('notify-sms', 'notes_subject') || 'New debit note notification';
# new invoice notify
my $invoices_file = $ini->val('notify-sms', 'invoices_template');
my $invoices_subject = $ini->val('notify-sms', 'invoices_subject') || 'New invoice notification';
# deadline notify
my $deadline_file = $ini->val('notify-sms', 'deadline_template');
my $deadline_subject = $ini->val('notify-sms', 'deadline_subject') || 'Invoice deadline notification';

my $script_service = $ini->val('notify-sms', 'service');

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

# ------------------------------------------------------------------------------
# CONFIGURATION
# ------------------------------------------------------------------------------
my %cfg;
$cfg{'prefix'} = $ini->val('sms', 'prefix') || 48;
$cfg{'service'} = $ini->val('sms', 'service') || '';
$cfg{'debug_sms'} = $ini->val('sms', 'debug_sms') || '';

my $query = $dbase->prepare("SELECT var, value FROM uiconfig WHERE section='sms' AND disabled=0");
$query->execute();
while(my $row = $query->fetchrow_hashref())
{
	$cfg{$row->{'var'}} = $row->{'value'};
}
$query->finish();

if ($cfg{'prefix'} ne '') {
    $cfg{'prefix'} = sprintf("%d", $cfg{'prefix'});
}

if ($script_service) {
	$cfg{'service'} = $script_service;
}

# ------------------------------------------------------------------------------
# FUNCTIONS
# ------------------------------------------------------------------------------
my %equiv = (
	"\x{017C}" => 'z', "\x{0105}" => 'a', "\x{015B}" => 's', 
	"\x{0107}" => 'c', "\x{0119}" => 'e', "\x{017A}" => 'z',
	"\x{0144}" => 'n', "\x{00F3}" => 'o', "\x{0142}" => 'l',
	"\x{017B}" => 'Z', "\x{0104}" => 'A', "\x{015A}" => 'S',
	"\x{0106}" => 'C', "\x{0118}" => 'E', "\x{0179}" => 'Z',
	"\x{0143}" => 'N', "\x{00D3}" => 'O', "\x{0141}" => 'L',
	# see http://www.perlmonks.org/?node_id=563765 for more
);

sub asciize
{
	my $str = shift;
	$str =~ s/([^\0-\x7f])/exists($equiv{$1})?$equiv{$1}:"?"/eg;
	return $str;	
}

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();
	}
# invoices, debit notes
	if ($data =~ /\%invoice/) {
		$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_sms
{
	my $phone = shift;
	my $sms = shift;
	my $msgid = shift;

	$phone =~ s/[^0-9]//g;
        $phone =~ s/^0+//;

	my $p = substr($phone, 0, 2);
	if ($p != $cfg{'prefix'}) {
		$phone = $cfg{'prefix'} . $phone;
	}

	if ($cfg{'service'} eq 'gnokii') {
		system("echo $sms | gnokii --sendsms $phone");
		return 2; # MSG_SENT
	} elsif ($cfg{'service'} eq 'smstools') {
		my $dir = $cfg{'smstools_outdir'} || '/var/spool/sms/outgoing';

		if(! (-e $dir)) {
                        return 'SMSTools outgoing directory not exists ('.$dir.')!';
                }
		if(! (-w $dir)) {
                        return 'Unable to write to SMSTools outgoing directory ('.$dir.')!';
		}
                my $filename = $dir.'/lms-'.$msgid.'-'.$phone;
		$sms = asciize(decode('utf-8', $sms));

		open(SMSFILE, ">$filename") or return 'Unable to create file '.$filename.'!';
		printf(SMSFILE "To: %s\n\n%s", $phone, $sms);
		close(SMSFILE);

		return 1; #MSG_NEW
	} else {
		return 'Unknown SMS service!';
	}
}

sub send_message
{
	my ($msgid, $cid, $phone, $data) = @_;

	my $dbqi = $dbase->prepare("INSERT INTO messageitems
		(messageid, customerid, destination, status)
		VALUES (?, ?, ?, ?)");
	$dbqi->execute($msgid, $cid, $phone, 1);

	my $result = send_sms($phone, $data, $msgid);
	my $time = mktime(localtime());

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

	if ($result =~ /[^0-9]/) {
		$dbqi->execute(3, $time, $result, $msgid, $cid);
	} elsif ($result == 2) { # MSG_SENT
		$dbqi->execute($result, $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 (2, ?, ?, ?)");
	$dbqi->execute($time, $subject, $template);
	$dbqi = $dbase->prepare("SELECT id FROM messages
			WHERE cdate = ? AND type = 2 AND subject = ?");
	$dbqi->execute($time, $subject);
	my $msgid = ($dbqi->fetchrow_array)[0];
	$dbqi->finish();

	return $msgid;
}

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

	# @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, x.phone
		FROM customers c
		JOIN cash ON (c.id = cash.customerid)
		JOIN (SELECT MIN(phone) AS phone, customerid
		        FROM customercontacts
			WHERE (type & 1) = 1
			GROUP BY customerid
		) x ON (x.customerid = c.id)
		GROUP BY c.id, c.pin, c.lastname, c.name, x.phone
		HAVING SUM(value) < $limit");
	$dbq->execute();

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

	while (my $row = $dbq->fetchrow_hashref())
	{
		$row->{'phone'} = $cfg{'debug_sms'} || $row->{'phone'};
		$row->{'phone'} =~ s/[^0-9]//g;

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

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

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

	my $dbq = $dbase->prepare("SELECT d.id AS docid, c.id, c.pin, d.name,
		d.number, n.template, d.cdate, d.paytime, x.phone,
		COALESCE(ca.balance, 0) AS balance, v.value
		FROM documents d
		JOIN customers c ON (c.id = d.customerid)
		JOIN (SELECT MIN(phone) AS phone, customerid
		        FROM customercontacts
			WHERE (type & 1) = 1
			GROUP BY customerid
		) x ON (x.customerid = 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();

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

	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'}));
			
		$row->{'phone'} = $cfg{'debug_sms'} || $row->{'phone'};
		$row->{'phone'} =~ s/[^0-9]//g;

		if(!$quiet)
		{
			printf STDOUT "[new invoice] %s (%04d) %s: %s\n",
				$row->{'name'}, $row->{'id'}, $row->{'doc_number'}, $row->{'phone'};
		}
			
		if(!$debug)
		{
			send_message($msgid, $row->{'id'}, $row->{'phone'}, parse_data($template, $row));
		}
	}
	$dbq->finish();
}

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

	my $dbq = $dbase->prepare("SELECT d.id AS docid, c.id, c.pin, d.name,
		d.number, n.template, d.cdate, d.paytime, x.phone,
		COALESCE(ca.balance, 0) AS balance, v.value
		FROM documents d
		JOIN customers c ON (c.id = d.customerid)
		JOIN (SELECT MIN(phone) AS phone, customerid
		        FROM customercontacts
			WHERE (type & 1) = 1
			GROUP BY customerid
		) x ON (x.customerid = 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.closed = 0 AND ca.balance < 0
		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())
	{
		$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'}));
			
		$row->{'phone'} = $cfg{'debug_sms'} || $row->{'phone'};
		$row->{'phone'} =~ s/[^0-9]//g;

		if(!$quiet)
		{
			printf STDOUT "[deadline] %s (%04d) %s: %s\n",
				$row->{'name'}, $row->{'id'}, $row->{'doc_number'}, $row->{'phone'};
		}
			
		if(!$debug)
		{
			send_message($msgid, $row->{'id'}, $row->{'phone'}, parse_data($template, $row));
		}
	}
	$dbq->finish();
}

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

	my $dbq = $dbase->prepare("SELECT d.id AS docid, c.id, c.pin, d.name,
		d.number, n.template, d.cdate, x.phone,
		COALESCE(ca.balance, 0) AS balance, v.value
		FROM documents d
		JOIN customers c ON (c.id = d.customerid)
		JOIN (SELECT MIN(phone) AS phone, customerid
		        FROM customercontacts
			WHERE (type & 1) = 1
			GROUP BY customerid
		) x ON (x.customerid = 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();

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

	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'}));
			
		$row->{'phone'} = $cfg{'debug_sms'} || $row->{'phone'};
		$row->{'phone'} =~ s/[^0-9]//g;

		if(!$quiet)
		{
			printf STDOUT "[new debit note] %s (%04d) %s: %s\n",
				$row->{'name'}, $row->{'id'}, $row->{'doc_number'}, $row->{'phone'};
		}
			
		if(!$debug)
		{
			send_message($msgid, $row->{'id'}, $row->{'phone'}, parse_data($template, $row));
		}
	}
	$dbq->finish();
}

$dbase->disconnect();