#!/usr/bin/perl -w
#
# lms-invoice-notify.pl 0.3 by Sylwester Zdanowski 22.08.2012

use DBI;
use Config::IniFiles;
use Getopt::Long;
use vars qw($configfile $help $version $debug);
use POSIX qw(strftime);
use LWP::UserAgent;
use warnings; 
use Time::Local;
use Switch;

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

my $_version = 'lms-invoice-notify.pl 0.3';

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

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

if($help)
{
	print STDERR <<EOF;
 $_version

-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.
-f, --fakedate=YYYY/MM/DD   override system date;

EOF
	exit 0;
}

if($version)
{
	print STDERR <<EOF;
 $_version
Sylwester Zdanowski

EOF
	exit 0;
}

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

if(!$quiet)
{
	print STDOUT " $_version\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 $service = $ini->val('sms', 'service') || '';
my $smsuser = $ini->val('sms', 'user') || '';
my $smspassword = $ini->val('sms', 'password') || '';
my $smssender = $ini->val('sms', 'sender') || '';
my $smsprefix = $ini->val('sms', 'smsprefix') || '';
my $excludecomment = $ini->val('sms', 'excludecomment') || '';

my $smstemplate = $ini->val('sms', 'fvtemplate') || '';
my $customergroups = $ini->val('sms', 'fvcustomergroups') || '';

my $prefix = $ini->val('sms', 'prefix') || '';

my $history = $ini->val('sms', 'history') || '0';
my $history_user= $ini->val('sms', 'historyuser') || '';
my $history_subject = $ini->val('sms', 'historysubject') || 'SMS - FV ';


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

if(!$smsuser || !$smspassword || !$service)
{
	print STDERR "Fatal error: sms service/user/password unset! Can't continue, exiting.\n";
	exit 1;
}


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 localtime2()
{
	if($fakedate)
	{
		my @fakedate = split(/\//, $fakedate);
		return localtime(timelocal(0,0,0,$fakedate[2],$fakedate[1]-1,$fakedate[0]));
	}
	else
	{
		return localtime();
	}
}

my $month =  int strftime("%m",localtime2());
my $day = int strftime("%d",localtime2());
my $year = strftime("%Y",localtime2());
my $daystart = strftime("%s", 0, 0, 0, $day, $month - 1, $year - 1900);
my $dayend = strftime("%s", 59, 59, 23, $day, $month - 1, $year - 1900);

my $groupwhere = '';
my $groupjoin = '';
if($customergroups)
{
	$customergroups = "UPPER('$customergroups')";
	$customergroups =~ s/[ \t]+/\'\),UPPER\(\'/g;
	$groupwhere = " AND UPPER(customergroups.name) IN ($customergroups)";  
	$groupjoin = "LEFT JOIN customerassignments ON (d.customerid = customerassignments.customerid) 
			LEFT JOIN customergroups ON (customerassignments.customergroupid = customergroups.id) ";
}

my $comment="";
if($excludecomment)
{
	$comment=' AND c.name NOT LIKE "'.$excludecomment.'" ';
}

$dbq = $dbase->prepare("SELECT d.id, d.number, d.cdate, d.name, d.customerid, n.template,c.phone
			FROM documents d 
			LEFT JOIN customercontacts c ON (c.customerid = d.customerid) 
			LEFT JOIN numberplans n ON (n.id = d.numberplanid)
			$groupjoin
			WHERE d.type = 1  
				AND d.cdate >= $daystart AND d.cdate <= $dayend 
				AND c.phone REGEXP ('($prefix)[0-9]{3}-|[0-9]{3}-|[0-9]{3}')
				$comment
				$groupwhere 
			GROUP BY d.customerid");

$dbq->execute();

open(FILE, "$smstemplate");
my @template = <FILE>;
close FILE;

my $ua = LWP::UserAgent->new;

while(my $row = $dbq->fetchrow_hashref())
{
	my $invoice_number = $row->{'template'} || '%N/LMS/%Y';
	$invoice_number =~ s/%(\d*)N/sprintf"%0${1}d",$row->{'number'}/e;
	$invoice_number = strftime($invoice_number, localtime($row->{'cdate'}));

	my $phone = $row->{'phone'};
	$phone =~ s/-//g;
	my $sms_txt='';

	foreach my $line (@template)
	{
		if ($line =~ /\%invoice_nr\%/) {
			$line =~ s/\%invoice_nr\%/$invoice_number/g;
		}
		$sms_txt.=$line;
	}

	my $body='';
	my $status=1;
	my $response='';
	switch ($service)
	{
		case 'serwersms' 
		{
			$response = $ua->get('https://api1.serwersms.pl/zdalnie/index.php?login='.$smsuser.'&haslo='.$smspassword.'&test='.$debug.'&akcja=wyslij_sms&numer='.$phone.'&wiadomosc='.$sms_txt.'&nadawca='.$smssender);
		}
		case 'smscenter'
		{ 
			if(!($phone=~/^$smsprefix/))
			{ 
				$phone=$smsprefix.$phone;
			}
		$response = $ua->get('http://api.statsms.net/send.php?user='.$smsuser.'&pass='.$smspassword.'&type=sms&number='.$phone.'&text='.$sms_txt.'&from='.$smssender);
			
		}
	}

	if ($response->is_success) 
	{
		$body=$response->decoded_content;
		$status=2;
	}
	else
	{
		print STDERR "Connection error";
	}

	if(!$quiet)
	{
		print STDOUT "\n--------------------------------------------------------------\n";
		print STDOUT $row->{'id'}." ".$row->{'name'}." ".$row->{'phone'}." ".$invoice_number."\n";
		print STDOUT $body;
		print STDOUT $sms_txt."\n";
	}	

	if(!$debug && $history)
	{
		my $currtime = strftime("%s",localtime2());

		my $message_dbq = $dbase->prepare("INSERT INTO messages (type, cdate, subject, body, userid, sender) VALUES ('2','$currtime', '$history_subject','$body', '$history_user', '$smssender')");
		$message_dbq->execute();
		my $last_id = $dbase->{insertid};

		$message_dbq = $dbase->prepare("INSERT INTO messageitems (messageid, customerid,destination,lastdate, status) VALUES ('$last_id', '$row->{'customerid'}', '$row->{'phone'}','$currtime','$status')");
		$message_dbq->execute();
	}
}

$dbq->finish();
$dbase->disconnect();
