#!/usr/bin/perl -Tw

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

my $_version = '1.5-cvs';

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

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

if($help)
{
	print STDERR <<EOF;
lms-ostrzegaj, version $_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;

EOF
	exit 0;
}

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

EOF
	exit 0;
}

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

if(!$quiet)
{
	print STDOUT "lms-ostrzegaj, version $_version\n";
	print STDOUT "(C) 2001-2005 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 $message = $ini->val('ostrzegaj', 'message') || 'Przypominamy o terminowym oplacaniu abonamentu.<BR>';

my $dbase;
my $utsfmt;

if($dbtype eq "mysql")
{
	$dbase = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost","$dbuser","$dbpasswd", { RaiseError => 1 });
	$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))";
}
elsif($dbtype eq "sqlite")
{
	$dbase = DBI->connect("DBI:SQLite:dbname=$dbname;host=$dbhost","$dbuser","$dbpasswd", { RaiseError => 1 });
	$utsfmt = "strftime('%s','now')";	
}
else
{
	print STDERR "Fatal error: unsupported database type: $dbtype, exiting.\n";
	exit 1;
}

my $now_date = strftime "%Y/%m/%d", localtime;
$message =~ s/%now/$now_date/g;

my $dbq = $dbase->prepare("DROP TABLE IF EXISTS tmp_customers");
$dbq->execute();
$dbq = $dbase->prepare("CREATE TABLE tmp_customers (id INTEGER, balance DOUBLE, covenant DOUBLE)");
$dbq->execute();
$dbq->finish();

$dbq = $dbase->prepare("INSERT INTO tmp_customers SELECT customers.id AS id, COALESCE(SUM(cash.value), 0.00)/(CASE COUNT(DISTINCT nodes.id) WHEN 0 THEN 1 ELSE COUNT(DISTINCT nodes.id) END) AS balance, 0 FROM customers LEFT JOIN cash ON (customers.id=cash.customerid) LEFT JOIN nodes ON (customers.id=ownerid) WHERE deleted = 0 AND nodes.access = 1 GROUP BY customers.id");

#	SELECT customers.id AS id, COALESCE(SUM((type * -2 + 7) * cash.value), 0.00)/(CASE COUNT(DISTINCT nodes.id) WHEN 0 THEN 1 ELSE COUNT(DISTINCT nodes.id) END) AS balance, 0 FROM customers LEFT JOIN cash ON (customers.id=cash.customerid AND (type = 3 OR type = 4)) LEFT JOIN nodes ON (customers.id=ownerid) WHERE deleted = 0 AND nodes.access = 1 GROUP BY customers.id");
$dbq->execute();
$dbq->finish();

$dbq = $dbase->prepare("SELECT customerid AS id,sum((100-discount)/100*tariffs.value) AS covenant FROM assignments LEFT JOIN tariffs ON (assignments.tariffid=tariffs.id) WHERE NOT suspended GROUP BY customerid");
$dbq->execute();
while (my $row = $dbq->fetchrow_hashref())
{
	my $dbq1 = $dbase->prepare("UPDATE tmp_customers SET covenant=$row->{'covenant'} WHERE id=$row->{'id'}");	
	$dbq1->execute;
}
$dbq = $dbase->prepare("delete from tmp_customers where -balance<2*covenant OR covenant=0");
$dbq->execute();
$dbq->finish();
$dbq = $dbase->prepare("select customerid AS id FROM customerassignments LEFT JOIN customergroups ON customerassignments.customergroupid=customergroups.id WHERE customergroups.name='SILENT'");
$dbq->execute();
while (my $row=$dbq->fetchrow_hashref())
{
	my $sdbq = $dbase->prepare("DELETE FROM tmp_customers WHERE id=$row->{'id'}");
	$sdbq->execute();
}
$dbq->finish();
$dbq = $dbase->prepare("SELECT CONCAT(UPPER(customers.lastname),' ',customers.name) AS customername,tmp_customers.id AS id,tmp_customers.covenant,tmp_customers.balance FROM tmp_customers LEFT JOIN customers ON tmp_customers.id=customers.id");
$dbq->execute();
while (my $row=$dbq->fetchrow_hashref())
{
	if(!$quiet)
	{
		print STDOUT "$row->{'customername'}\t($row->{'balance'})\t$row->{'covenant'}\n";
	}
	my $sdbq = $dbase->prepare("UPDATE customers SET message='$message' WHERE id=$row->{'id'}");
	$sdbq->execute();
	$sdbq->finish();
	$sdbq = $dbase->prepare("UPDATE nodes SET warning=1 WHERE ownerid='$row->{'id'}'");
	$sdbq->execute();
	$sdbq->finish();
}
$dbq->finish();

$dbq = $dbase->prepare("DELETE FROM timestamps WHERE tablename = 'nodes' OR tablename = '_global'");
$dbq->execute();
$dbq->finish();
$dbq = $dbase->prepare("INSERT INTO timestamps (tablename,time) VALUES ('nodes',$utsfmt)");
$dbq->execute();
$dbq->finish();
$dbq = $dbase->prepare("INSERT INTO timestamps (tablename,time) VALUES ('_global',$utsfmt)");
$dbq->execute();
$dbq->finish();
$dbase->disconnect();