#!/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-payments,v 1.132 2011/04/01 11:46:52 alec Exp $

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

my $_version = '1.11.13 Dira';

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

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

if($help)
{
	print STDERR <<EOF;
lms-payments, 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;
-f, --fakedate=YYYY/MM/DD	override system date

EOF
	exit 0;
}

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

EOF
	exit 0;
}

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

if(!$quiet)
{
	print STDOUT "lms-payments, 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 $customergroups = $ini->val('payments', 'customergroups') || '';
my $deadline = $ini->val('payments', 'deadline') || '14';
my $paytype = $ini->val('payments', 'paytype') || 2; # TRANSFER
my $sdate_next = $ini->val('payments', 'saledate_next_month') || 0;
my $comment = $ini->val('payments', 'comment') || 'Tariff %tariff subscription for period %period';
my $s_comment = $ini->val('payments', 'settlement_comment') || $comment;
my $suspension_description = $ini->val('payments', 'suspension_description') || '';

my $suspension_percentage = $ini->val('finances', 'suspension_percentage') || '0';

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

sub is_leap_year($)
{
	my $year = shift;
        return 0 if $year % 4;
        return 1 if $year % 100;
	return 0 if $year % 400;
	return 1;
}

use constant HALFYEAR	=> 7;
use constant CONTINUOUS	=> 6;
use constant YEAR	=> 5;
use constant QUARTER	=> 4;
use constant MONTH	=> 3;
use constant WEEK	=> 2;
use constant DAY	=> 1;
use constant DISPOSABLE	=> 0;

my @customergroupslist = split ' ', $customergroups;

my $currtime = strftime("%s",localtime2());
my $month = int strftime("%m",localtime2());
my $dom = int strftime("%d",localtime2());
my $year = strftime("%Y",localtime2());
my $weekday = strftime("%u",localtime2());
my $yearday = strftime("%j",localtime2());

# leap year fix
if(is_leap_year($year) && $yearday > 31+28) {
	$yearday -= 1;
}

my $today;
if($fakedate) {
	$today = $currtime;
} else {
	$today = timelocal(0,0,0,$dom,$month-1,$year);
}

my $quarter;
if($month==1 || $month==4 || $month==7 || $month==10) {
	$quarter = $dom;
} elsif ($month==2 || $month==5 || $month==8 || $month==11) {
	$quarter = $dom + 100;
} else {
	$quarter = $dom + 200;
}

my $halfyear;
if($month > 6) {
       $halfyear = $dom + ($month - 7) * 100;
} else {
       $halfyear = $dom + ($month - 1) * 100;
}

my $q_month = $month + 2;
my $q_year  = $year;
my $y_month = $month + 5;
my $y_year  = $year;
if ($q_month > 12) {
    $q_month -= 12;
    $q_year += 1;
}
if ($y_month > 12) {
    $y_month -= 12;
    $y_year += 1;
}

my %txts;
$txts{+DAY} = strftime("%Y/%m/%d", 0, 0, 12, $dom, $month - 1, $year - 1900);
$txts{+WEEK} = strftime("%Y/%m/%d", 0, 0, 12, $dom, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 12, $dom + 6, $month - 1, $year - 1900);
$txts{+MONTH} = strftime("%Y/%m/%d", 0, 0, 12, $dom, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 12, $dom - 1, $month, $year - 1900);
$txts{+QUARTER} = strftime("%Y/%m/%d", 0, 0, 12, $dom, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 12, $dom - 1, $q_month, $q_year - 1900);
$txts{+HALFYEAR} = strftime("%Y/%m/%d", 0, 0, 12, $dom, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 12, $dom - 1, $y_month, $y_year - 1900);
$txts{+YEAR} = strftime("%Y/%m/%d", 0, 0, 12, $dom, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 12, $dom - 1, $month - 1, $year - 1900 + 1);
$txts{+DISPOSABLE} = strftime("%Y/%m/%d", 0, 0, 12, $dom, $month - 1, $year - 1900);

# special case, ie. you have 01.01.2005-01.31.2005 on invoice, but invoice/
# assignment is made not January, the 1st:

my $current_month = strftime("%Y/%m/%d", 0, 0, 12, 1, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 12, 0, $month, $year - 1900);
my $current_period = strftime("%m/%Y", 0, 0, 12, 1, $month - 1, $year - 1900);
my $next_period = strftime("%m/%Y", 0, 0, 12, 1, $month, $year - 1900);

# sale date setting
my $saledate = $currtime;
if ($sdate_next) {
    $saledate = strftime("%s", 0, 0, 12, 1, $month, $year - 1900);
}

# calculate start and end of numbering period
sub get_period($)
{
	my $period = shift || YEAR;
	my $start = 0;
	my $end = 0;

	for($period)
	{
		/@{[DAY]}/ && do {
			$start = strftime("%s", 0, 0, 0, $dom, $month - 1, $year - 1900);
			$end = strftime("%s", 0, 0, 0, $dom + 1, $month - 1, $year - 1900);
			last;
		};
		/@{[WEEK]}/ && do {
			my $startweek = $dom - $weekday + 1;
			$start = strftime("%s", 0, 0, 0, $startweek, $month - 1, $year - 1900);
			$end = strftime("%s", 0, 0, 0, $startweek + 7, $month - 1, $year - 1900);
			last;
		};
		/@{[MONTH]}/ && do {
			$start = strftime("%s", 0, 0, 0, 1, $month - 1, $year - 1900);
			$end = strftime("%s", 0, 0, 0, 1, $month, $year - 1900);
			last;
		};
		/@{[QUARTER]}/ && do {
			my $startmonth;
			if($month<=3) {
				$startmonth = 1;
			} elsif ($month<=6) {
				$startmonth = 4;
			} elsif ($month<=9) {
				$startmonth = 7;
			} else {
				$startmonth = 10;
			}
			$start = strftime("%s", 0, 0, 0, 1, $startmonth - 1, $year - 1900);
			$end = strftime("%s", 0, 0, 0, 1, $startmonth - 1 + 3, $year - 1900);
			last;
		};
		/@{[HALFYEAR]}/ && do {
			my $startmonth;
		    	if($month<=6) {
				$startmonth = 1;
			} else {
				$startmonth = 7;
			}
			$start = strftime("%s", 0, 0, 0, 1, $startmonth - 1, $year - 1900);
			$end = strftime("%s", 0, 0, 0, 1, $startmonth - 1 + 6, $year - 1900);
			last;
		};
		/@{[CONTINUOUS]}/ && do {
			$start = strftime("%s", 0, 0, 0, 1, 0, 70);
			$end = strftime("%s", 0, 0, 0, $dom + 1, $month - 1, $year - 1900);
			last;
		};
		# /@{[YEAR]}/ && do 
		{
			$start = strftime("%s", 0, 0, 0, 1, 0, $year - 1900);
			$end = strftime("%s", 0, 0, 0, 1, 0, $year + 1 - 1900);
			last;
		};
	}
	return ('start' => $start, 'end' => $end);
}

my $dbq;
my $row;

if(! $suspension_percentage)
{
	$dbq = $dbase->prepare("SELECT value FROM uiconfig WHERE var = 'suspension_percentage' AND section = 'finances'");
	$dbq->execute();
	if($row = $dbq->fetchrow_hashref())
	{
		$suspension_percentage = $row->{'value'} || 0;
	}
}

my %periods;
my %plans;
my %numbers;
my %invoices;
my %paytypes;
my %numberplans;
my $icdbq;
my $icrow;
my $cdbq;
my $suspended = 0;
my $skipped = 0;
my $tested = 0;
my $itemid;

# get IDs and periods of default invoices numbering plans
$dbq = $dbase->prepare("SELECT n.id, n.period,
        COALESCE(a.divisionid, 0) AS divid, isdefault
	FROM numberplans n
	LEFT JOIN numberplanassignments a ON (a.planid = n.id)
	WHERE doctype = 1");
$dbq->execute();
while(my $row = $dbq->fetchrow_hashref())
{
    if ($row->{'isdefault'}) {
	    $plans{$row->{'divid'}} = $row->{'id'};
    }
   	$periods{$row->{'id'}} = $row->{'period'} || YEAR;
}

# let's go, fetch *ALL* assignments in given day
$dbq = $dbase->prepare("SELECT a.tariffid, a.liabilityid, a.customerid,
    a.period, a.at, a.suspended, a.settlement, a.datefrom, a.discount,
	a.invoice, t.description AS description, a.id AS assignmentid,
	c.divisionid, c.paytype, a.paytype AS a_paytype, a.numberplanid,
	d.inv_paytype AS d_paytype, t.period AS t_period,
	(CASE a.liabilityid WHEN 0 THEN t.name ELSE l.name END) AS name,
	(CASE a.liabilityid WHEN 0 THEN t.taxid ELSE l.taxid END) AS taxid,
	(CASE a.liabilityid WHEN 0 THEN t.prodid ELSE l.prodid END) AS prodid,
	(CASE a.liabilityid WHEN 0 
		THEN (CASE a.suspended WHEN 0 
			THEN ROUND(CASE a.discount WHEN 0 THEN t.value
				ELSE ((100-a.discount)*t.value)/100 END, 2)
			ELSE ROUND(ROUND(CASE a.discount WHEN 0 THEN t.value
				ELSE ((100-a.discount)*t.value)/100 END, 2) * $suspension_percentage/100, 2)
			END)
		ELSE
		    (CASE a.suspended WHEN 0
			THEN ROUND(CASE a.discount WHEN 0 THEN l.value
				ELSE ((100-a.discount)*l.value)/100 END, 2)
			ELSE ROUND(ROUND(CASE a.discount WHEN 0 THEN l.value
				ELSE ((100-a.discount)*l.value)/100 END, 2) * $suspension_percentage/100, 2)
			END)
		END) AS value
	FROM assignments a
	JOIN customers c ON (a.customerid = c.id)
	LEFT JOIN tariffs t ON (a.tariffid = t.id)
	LEFT JOIN liabilities l ON (a.liabilityid = l.id)
	LEFT JOIN divisions d ON (d.id = c.divisionid)
	WHERE c.status = 3
		AND ((a.period = ".DISPOSABLE." AND at = $today)
		    OR ((a.period = ".DAY."
			    OR (a.period = ".WEEK." AND at = $weekday)
    			OR (a.period = ".MONTH." AND at = $dom)
	    		OR (a.period = ".QUARTER." AND at = $quarter)
		    	OR (a.period = ".HALFYEAR." AND at = $halfyear)
			    OR (a.period = ".YEAR." AND at = $yearday))
		        AND (a.datefrom <= $currtime OR a.datefrom = 0)
		        AND (a.dateto > $currtime OR a.dateto = 0)))
	ORDER BY a.customerid, a.invoice, a.paytype, a.numberplanid, value DESC");
$dbq->execute();

while(my $assign = $dbq->fetchrow_hashref())
{
	my $uid = $assign->{'customerid'};
	my $divid = $assign->{'divisionid'} || 0;

	if (not defined $assign->{'value'}) { next; };

	if ($assign->{'value'} == 0) { next; };

	# checking groups membership
	if ($customergroups)
	{
		if ($skipped != $uid && $tested != $uid)
		{
			my $found = 0;
	    		my $sdbq = $dbase->prepare("SELECT UPPER(name) AS name FROM customerassignments, customergroups WHERE customergroups.id = customergroupid AND customerid = $uid GROUP BY name");
		        $sdbq->execute();

			while (my $srow = $sdbq->fetchrow_hashref())
			{
				foreach my $group (@customergroupslist)
				{
					if ($srow->{'name'} eq uc($group))
					{
						$found = 1;
						last;
					}
				}
				if ($found) { last; }
			}

			if ($found)
			{
				$tested = $uid;
			}
			else
			{
				$skipped = $uid;
				next;
			}
		}
		elsif ($skipped == $uid)
		{
			next;
		}
	}

	# check, if all customer liabilities are suspended
	if ($suspended != $uid)
	{
		my $xdbq = $dbase->prepare("SELECT 1 FROM assignments, customers WHERE customerid = customers.id AND tariffid = 0 AND liabilityid = 0 AND (datefrom <= $currtime OR datefrom = 0) AND (dateto > $currtime OR dateto = 0) AND customerid = $uid");
		$xdbq->execute();
		if(my $xrow = $xdbq->fetchrow_hashref())
		{
			$suspended = $uid;
			if (!$assign->{'suspended'})
			{
				$assign->{'value'} = $assign->{'value'} * $suspension_percentage / 100;
			}
		}
		$xdbq->finish();
	} else {
		if (!$assign->{'suspended'})
		{
			$assign->{'value'} = $assign->{'value'} * $suspension_percentage / 100;
		}
	}

	my $desc = $comment;
	if ($assign->{'liabilityid'})
	{
		$desc = $assign->{'name'}; # you can use variables in names of tariffless liabilities
	}
	$desc =~ s/\%tariff/$assign->{'name'}/g;
	$desc =~ s/\%desc/$assign->{'description'}/g;
	$desc =~ s/\%period/$txts{$assign->{'period'}}/g;
	$desc =~ s/\%current_month/$current_month/g;
	$desc =~ s/\%current_period/$current_period/g;
	$desc =~ s/\%next_period/$next_period/g;

	if ($suspension_percentage && ($assign->{'suspended'} || $suspended == $uid))
	{
		$desc .= " ".$suspension_description;
	}

	$invoices{$uid} = 0 if not defined $invoices{$uid};
	$paytypes{$uid} = 0 if not defined $paytypes{$uid};
	$numberplans{$uid} = 0 if not defined $numberplans{$uid};

	if($assign->{'value'} != 0)
	{
	    my $val = $assign->{'value'};
        # calculate assignment value according to tariff's period
        if ($assign->{'t_period'} && $assign->{'period'} != DISPOSABLE
            && $assign->{'t_period'} != $assign->{'period'}
        ) {
            if ($assign->{'t_period'} == YEAR) {
                $val = $val / 12.0;
            } elsif ($assign->{'t_period'} == HALFYEAR) {
                $val = $val / 6.0;
            } elsif ($assign->{'t_period'} == QUARTER) {
                $val = $val / 3.0;
            }

            if ($assign->{'period'} == YEAR) {
                $val = $val * 12.0;
            } elsif ($assign->{'period'} == HALFYEAR) {
                $val = $val * 6.0;
            } elsif ($assign->{'period'} == QUARTER) {
                $val = $val * 3.0;
            } elsif ($assign->{'period'} == WEEK) {
                $val = $val / 4.0;
            } elsif ($assign->{'period'} == DAY) {
                $val = $val / 30.0;
            }

        }

        $val = sprintf("%.2f", $val);

		if($assign->{'invoice'})
		{
		    my ($inv_paytype, $numberplan, $plan);

		    if ($assign->{'a_paytype'}) {
		        $inv_paytype = $assign->{'a_paytype'};
		    } elsif ($assign->{'paytype'}) {
		        $inv_paytype = $assign->{'paytype'};
		    } elsif ($assign->{'d_paytype'}) {
		        $inv_paytype = $assign->{'d_paytype'};
		    } else {
		        $inv_paytype = $paytype;
            }

		    if ($assign->{'numberplanid'}) {
		        $plan = $assign->{'numberplanid'};
		    } else {
			    $plan = $plans{$divid} || 0;
            }

			if($invoices{$uid} eq 0 || $paytypes{$uid} != $inv_paytype || $numberplans{$uid} != $plan)
			{
				if(!$numbers{$plan})
				{
					my %period = get_period($periods{$plan});

					my $dbqn = $dbase->prepare("SELECT MAX(number) AS number FROM documents
						WHERE cdate >= ? AND cdate < ? AND type = 1 AND numberplanid = ?");
					$dbqn->execute($period{'start'}, $period{'end'}, $plan);

					$row = $dbqn->fetchrow_hashref();
					$numbers{$plan} = $row->{'number'} || 0;
					$dbqn->finish();
				}

				$itemid = 0;
				$numbers{$plan}++;

				my $udbq = $dbase->prepare("SELECT lastname, name, address, city, zip, ssn, ten, countryid, divisionid, paytime
						FROM customers WHERE id = ?");
				$udbq->execute($uid);
				my $urow = $udbq->fetchrow_hashref();
				my $paytime = $urow->{'paytime'};
				if($paytime eq -1) { $paytime = $deadline; }

				my $idbq = $dbase->prepare("INSERT INTO documents (number, numberplanid, type, countryid, divisionid,
						customerid, name, address, zip, city, ten, ssn, cdate, sdate, paytime, paytype)
						VALUES (?, ?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
				$idbq->execute($numbers{$plan}, $plan, $urow->{'countryid'}, $urow->{'divisionid'}, $uid,
						$urow->{'lastname'}.' '.$urow->{'name'}, $urow->{'address'}, $urow->{'zip'},
						$urow->{'city'}, $urow->{'ten'}, $urow->{'ssn'}, $currtime, $saledate, $paytime, $inv_paytype);

				$idbq = $dbase->prepare("SELECT id FROM documents
						WHERE cdate = ? AND number = ? AND type = 1 AND customerid = ?");
				$idbq->execute($currtime, $numbers{$plan}, $uid);
				my $irow = $idbq->fetchrow_hashref();

				$invoices{$uid} = $irow->{'id'};
				$paytypes{$uid} = $inv_paytype;
				$numberplans{$uid} = $plan;

				$udbq->finish();
				$idbq->finish();
			}

			$icdbq = $dbase->prepare("SELECT itemid FROM invoicecontents
					WHERE tariffid=$assign->{'tariffid'} AND value=$val
					AND docid=$invoices{$uid} AND description=? AND discount=$assign->{'discount'}");
			$icdbq->execute($desc);

			if($icrow = $icdbq->fetchrow_hashref())
			{
				my $tmp_itemid = $icrow->{'itemid'};

				$icdbq = $dbase->prepare("UPDATE invoicecontents SET count=count+1
						WHERE tariffid=$assign->{'tariffid'} AND docid=$invoices{$uid}
						AND description=? AND discount=$assign->{'discount'}");
				$icdbq->execute($desc);

				$cdbq = $dbase->prepare("UPDATE cash SET value = value+($val*-1)
						WHERE docid = $invoices{$uid} AND itemid = $tmp_itemid");
				$cdbq->execute();
			}
			else
			{
				$itemid++;

				$icdbq = $dbase->prepare("INSERT INTO invoicecontents (docid, value, taxid, prodid,
						content, count, description, tariffid, itemid, discount)
						VALUES ($invoices{$uid}, $val, $assign->{'taxid'}, '$assign->{'prodid'}',
						'szt.', 1, ?, $assign->{'tariffid'}, $itemid, $assign->{'discount'})");
				$icdbq->execute($desc);

				$cdbq = $dbase->prepare("INSERT INTO cash (time, value, taxid, customerid, comment, docid, itemid)
						VALUES ($currtime, $val * -1, $assign->{'taxid'}, $uid, ?, $invoices{$uid}, $itemid)");
				$cdbq->execute($desc);
			}

			$icdbq->finish();
		}
		else
		{
			$cdbq = $dbase->prepare("INSERT INTO cash (time, value, taxid, customerid, comment)
					VALUES ($currtime, $val * -1, $assign->{'taxid'}, $uid, ?)");
			$cdbq->execute($desc);
		}

		print STDERR "CID:$uid\tVAL:$val\tDESC:$desc\n" if not $quiet;

		# settlements accounting
		if ($assign->{'settlement'} && $assign->{'datefrom'})
		{
			my $alldays = 1;
			my $diffdays = sprintf("%d", ($today - $assign->{'datefrom'})/86400);
			my $period = strftime("%Y/%m/%d", 0, 0, 0, $dom - $diffdays, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 0, $dom - 1, $month - 1, $year - 1900);

			for ($assign->{'period'})
	                {
			        # there are no disposable or daily liabilities with settlement
				/@{[WEEK]}/ && do {
					$alldays = 7; last;
				};
				/@{[MONTH]}/ && do {
					$alldays = 30; last;
				};
				/@{[QUARTER]}/ && do {
					$alldays = 90; last;
				};
				/@{[HALFYEAR]}/ && do {
					$alldays = 182; last;
				};
				/@{[YEAR]}/ && do {
					$alldays = 365;	last;
				};
			}

			my $value = sprintf("%.2f", $diffdays * $val/$alldays);

			#print "value: ".$val." diffdays: $diffdays alldays: $alldays settl_value: $value\n";

			my $sdesc = $s_comment;
			$sdesc =~ s/\%tariff/$assign->{'name'}/g;
			$sdesc =~ s/\%desc/$assign->{'description'}/g;
			$sdesc =~ s/\%period/$period/g;
			$sdesc =~ s/\%current_month/$current_month/g;

	    		if($assign->{'invoice'})
		    	{
				$icdbq = $dbase->prepare("SELECT itemid FROM invoicecontents
						WHERE tariffid=? AND value=? AND docid=? AND description=?");
				$icdbq->execute($assign->{'tariffid'}, $value, $invoices{$uid}, $sdesc);

				if($icrow = $icdbq->fetchrow_hashref())
				{
					my $tmp_itemid = $icrow->{'itemid'};

					$icdbq = $dbase->prepare("UPDATE invoicecontents SET count=count+1
							WHERE tariffid=? AND docid=? AND description=?");
					$icdbq->execute($assign->{'tariffid'}, $invoices{$uid}, $sdesc);

					$cdbq = $dbase->prepare("UPDATE cash SET value = value+($value*-1)
							WHERE docid = ? AND itemid = ?");
					$cdbq->execute($invoices{$uid}, $tmp_itemid);
				}
				else
				{
					$itemid++;

					$icdbq = $dbase->prepare("INSERT INTO invoicecontents (docid, value, taxid, prodid,
							content, count, description, tariffid, itemid, discount)
							VALUES ($invoices{$uid}, $value, $assign->{'taxid'}, '$assign->{'prodid'}',
							'szt.', 1, ?, $assign->{'tariffid'}, $itemid, $assign->{'discount'})");
					$icdbq->execute($sdesc);

					$cdbq = $dbase->prepare("INSERT INTO cash (time, value, taxid, customerid, comment, docid, itemid)
							VALUES ($currtime, $value * -1, $assign->{'taxid'}, $uid, ?, $invoices{$uid}, $itemid)");
					$cdbq->execute($sdesc);
				}
				$icdbq->finish();
			}
			else
			{
				$cdbq = $dbase->prepare("INSERT INTO cash (time, value, taxid, customerid, comment)
						VALUES ($currtime, $value * -1, $assign->{'taxid'}, $uid, ?)");
				$cdbq->execute($sdesc);
				$cdbq->finish();
			}

			print STDERR "CID:$uid\tVAL:$value\tDESC:$sdesc\n" if not $quiet;

			#remove settlement flag
			$dbase->do("UPDATE assignments SET settlement = 0 WHERE id = $assign->{'assignmentid'}");
		}
		$cdbq->finish();
	}
}
$dbq->finish();

# solid payments
$dbq = $dbase->prepare("SELECT * FROM payments WHERE value <> 0
		AND (period = ".DAY."
			OR (period = ".WEEK." AND at=$weekday)
			OR (period = ".MONTH." AND at=$dom)
			OR (period = ".QUARTER." AND at = $quarter)
			OR (period = ".HALFYEAR." AND at = $halfyear) 
			OR (period = ".YEAR." AND at = $yearday))");
$dbq->execute();

while(my $assign = $dbq->fetchrow_hashref())
{
	my $sdbq = $dbase->prepare("INSERT INTO cash (time, type, value, customerid, comment)
			VALUES ($currtime, 1, $assign->{'value'} * -1, 0, ?)");
	$sdbq->execute($assign->{'name'}.'/'.$assign->{'creditor'});
	print STDERR "CID:0\tVAL:$assign->{'value'}\tDESC:$assign->{'name'}/$assign->{'creditor'}\n" if not $quiet;
}
$dbq->finish();

# delete old assignments
$dbase->do("DELETE FROM liabilities WHERE id IN (
    SELECT liabilityid FROM assignments
    WHERE dateto < $utsfmt - 86400 * 30 AND dateto != 0 AND at < $today - 86400 * 30
	    AND liabilityid != 0)");
$dbase->do("DELETE FROM assignments
    WHERE dateto < $utsfmt - 86400 * 30 AND dateto != 0 AND at < $today - 86400 * 30");

$dbase->disconnect();
