#!/usr/bin/perl -Tw
#
#  iLMS version 26.03.15
#
#  Copyright (C) 2012-2015 iLMS 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$
#
# changelog :
# 19.11.02
# dodano zapis info o bilansie klienta przed wystawieniem faktury w bazie danych -> documents.balance_info_before

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 = '26.03.15';

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) 2012-2026 iLMS 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) 2012-2026 iLMS Developers

EOF
	exit 0;
}

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

if(!$quiet)
{
	print STDOUT "lms-payments, version $_version\n";
	print STDOUT "(C) 2012-2018 iLMS 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 $comment = $ini->val('payments', 'comment') || '%tariff za okres %period';
my $s_comment = $ini->val('payments', 'settlement_comment') || $comment;
my $suspension_description = $ini->val('payments', 'suspension_description') || '';
my $check_invoices = $ini->val('payments', 'check_invoices') || 0;
my $calculate_per_month = $ini->val('payments','calculate_per_month') || 0; # naliczaj za miesiąc -1>poprzenid(z dołu) 0>bieżący 1>następny (z góry)

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;

use constant TARIFF_INTERNET	=> 1;
use constant TARIFF_HOSTING	=> 2;
use constant TARIFF_SERVICE	=> 3;
use constant TARIFF_PHONE	=> 4;
use constant TARIFF_TV		=> 5;
use constant TARIFF_OTHER	=> -1;

my %tarifftypes;
$tarifftypes{+TARIFF_INTERNET}	= $ini->val('tarifftypes', 'internet') || 'internet',
$tarifftypes{+TARIFF_HOSTING}	= $ini->val('tarifftypes', 'hosting') || 'hosting',
$tarifftypes{+TARIFF_SERVICE}	= $ini->val('tarifftypes', 'service') || 'service',
$tarifftypes{+TARIFF_PHONE}	= $ini->val('tarifftypes', 'phone') || 'phone',
$tarifftypes{+TARIFF_TV}	= $ini->val('tarifftypes', 'tv') || 'tv',
$tarifftypes{+TARIFF_OTHER}	= $ini->val('tarifftypes', 'other') || 'other',

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());

sub localtime3()
{
    return localtime(timelocal(0,0,0,$dom,$month -1 +($calculate_per_month),$year));
}

if ($calculate_per_month) {

    $month = int strftime("%m",localtime3());
    $dom = int strftime("%d",localtime3());
    $year = strftime("%Y",localtime3());
    $weekday = strftime("%u",localtime3());
    $yearday = strftime("%j",localtime3());
    
}

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

sub period_format($$$) {
	my $period = shift;
	my $tip = shift;
	my $format = shift;

	if ($tip eq "begin") {
		return strftime($format, 0, 0, 12, $dom, $month - 1, $year - 1900);
	};

	$_ = $period;
	/@{[DAY]}/ && do {
		return strftime($format, 0, 0, 12, $dom, $month - 1, $year - 1900);
	};

	/@{[WEEK]}/ && do {
		return strftime($format, 0, 0, 12, $dom + 6, $month - 1, $year - 1900);
	};

	/@{[MONTH]}/ && do {
		return strftime($format, 0, 0, 12, $dom - 1, $month, $year - 1900);
	};

	/@{[QUARTER]}/ && do {
		return strftime($format, 0, 0, 12, $dom - 1, $q_month, $q_year - 1900);
	};

	/@{[HALFYEAR]}/ && do {
		return strftime($format, 0, 0, 12, $dom - 1, $y_month - 1, $y_year - 1900);
	};

	/@{[YEAR]}/ && do {
		return strftime($format, 0, 0, 12, $dom - 1, $month - 1, $year - 1900 + 1);
	};

	/@{[DISPOSABLE]}/ && do {
		return strftime($format, 0, 0, 12, $dom, $month - 1, $year - 1900);
	};

	return "<unknown>";
}

# 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);
my $prev_period = strftime("%m/%Y", 0, 0, 12, 1, $month - 2, $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;

use constant DEFAULT_NUMBER_TEMPLATE => '%N/LMS/%Y';

sub docnumber($$$) {
	my $number = shift;
	my $template = shift;
	my $time = shift;

	$number = $number ? $number : 1;
	$template = length($template) ? $template : DEFAULT_NUMBER_TEMPLATE;
	$time = $time ? $time : strftime("%s", localtime2());

	# main document number
	$template =~ s/%(\d*)N/sprintf("%0$1d", $number)/eg;

	# time conversion specifiers
	return strftime($template, localtime($time));
}

sub getCustomerBalance($$) {
    
    my $customerid = shift;
    my $cdate = shift;
    
    my $bdbq = $dbase->prepare("SELECT SUM(value) AS balance FROM cash WHERE customerid=? AND time < ?;");
    $bdbq->execute($customerid,$cdate);
    my $brow = $bdbq->fetchrow_hashref();
    
    return $brow->{'balance'};
    
}


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 %numbertemplates;
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.dateto, a.pdiscount, a.vdiscount, 
	a.invoice, t.description AS description, a.id AS assignmentid,
	c.divisionid, c.paytype, c.account, c.account_primary, 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.type ELSE -1 END) as tarifftype,
	(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 t.gtu ELSE l.gtu END) AS gtu,
	(CASE a.liabilityid WHEN 0 THEN t.tax_procedure ELSE l.tax_procedure END) AS tax_procedure,
	(CASE a.liabilityid WHEN 0 
		THEN ROUND(((((100 - a.pdiscount) * t.value) / 100) - a.vdiscount) *
			(CASE a.suspended WHEN 0
				THEN 1.0
				ELSE $suspension_percentage / 100
			END), 2)
		ELSE ROUND(((((100 - a.pdiscount) * l.value) / 100) - a.vdiscount) *
			(CASE a.suspended WHEN 0
				THEN 1.0
				ELSE $suspension_percentage / 100
			END), 2)
	END) AS value,
	(CASE
	    WHEN ( t.splitpayment = 1 AND a.splitpayment = '-1' AND c.splitpayment = '-1' AND (c.type = 1 OR c.type = 2) ) THEN 1 
	    WHEN ( l.splitpayment = 1 AND a.splitpayment = '-1' AND c.splitpayment = '-1' AND (c.type = 1 OR c.type = 2) ) THEN 1 
	    WHEN ( (a.splitpayment = 1 OR a.splitpayment = '-1') AND c.splitpayment = '-1' AND (c.type = 1 OR c.type = 2) ) THEN 1 
	    WHEN ( (c.type = 1 OR c.type = 2) AND c.splitpayment = 1 ) THEN 1 
	    ELSE 0 END
	) AS splitpayment 
	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.invoice = 1 OR a.invoice = 0) 
		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();

my $splitpayment = 0;
my $sumvalue = 0;

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; };
	
	if ($assign->{'splitpayment'} == 1) { $splitpayment = 1; }

	# 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 $dateto = $assign->{'dateto'}; #pobieram datę zakończenia taryfy
        my $days2end = sprintf("%d", ($assign->{'dateto'} - $today)/86400 +1); #liczę ile dni zostało do końca taryfy w danym miesiącu
        my $curmonth = strftime("%Y%m",localtime($today)); #pobieram bierzący miesiąc
        my $lasttariffmonth = strftime("%Y%m",localtime($dateto)); #pobieram ostatni miesiac taryfy
        my $periodnqf = strftime("%Y/%m/%d", 0, 0, 0, 1, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 0, $days2end , $month - 1 , $year - 1900); # opis niepełnego okresu dla miesięcznej taryfy
        my $desc4nqf = (($curmonth eq $lasttariffmonth) && ($assign ->{period} == 3)) ? ($periodnqf): ($txts{$assign->{'period'}}); #warunek dla opisu 

	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/\%type/$tarifftypes{$assign->{'tarifftype'}}/g;
        $desc =~ s/\%period_(begin|end)\(\s*["'](.*?)["']\s*\)/&period_format($assign->{'period'}, $1, $2)/ge;
	$desc =~ s/\%period/$desc4nqf/g;
	$desc =~ s/\%current_month/$current_month/g;
	$desc =~ s/\%current_period/$current_period/g;
	$desc =~ s/\%next_period/$next_period/g;
	$desc =~ s/\%prev_period/$prev_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;
			}

		}

		my $val_tarrif = (($curmonth eq $lasttariffmonth) && ($assign ->{period} == 3)  && ($days2end <=30)) ? (sprintf("%.2f", ($val/30 *$days2end))) : (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();
					
					$dbqn = $dbase->prepare("SELECT template FROM numberplans WHERE id = ?");
					$dbqn->execute($plan);

					$row = $dbqn->fetchrow_hashref();
					$numbertemplates{$plan} = $row->{'template'};
					$dbqn->finish();
				}

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

				my $udbq = $dbase->prepare("SELECT lastname, name, type, address, city, zip, ssn, ten, countryid, divisionid, paytime, post_name, post_address, post_zip, post_city,
						invoice_name, invoice_lastname, invoice_address, invoice_zip, invoice_city, invoice_countryid, invoice_ten, invoice_ssn, account, account_primary, 
						recipient_lastname, recipient_name, recipient_address, recipient_zip, recipient_city , print_balance_info, templatefile, invoice_footer , jpk_v7_tp, 
						recipient_ten, recipient_ssn, jst 
						FROM customers WHERE id = ?");
				$udbq->execute($uid);
				my $urow = $udbq->fetchrow_hashref();

				my $name = $urow->{'lastname'};
				if ($urow->{'name'} ne '') {
				     $name = $urow->{'lastname'}.'<br>'.$urow->{'name'};
				}
				my $address = $urow->{'address'};
				my $zip = $urow->{'zip'};
				my $city = $urow->{'city'};
				my $countryid = $urow->{'countryid'};
				my $type = $urow->{'type'};
				my $ten = '';
				my $ssn = '';
				my $bankaccount = '';
				
				if (!$urow->{'post_name'}) { $urow->{'post_name'} = ''; }
				if (!$urow->{'post_address'}) { $urow->{'post_address'} = ''; }
				if (!$urow->{'post_zip'}) { $urow->{'post_zip'} = ''; }
				if (!$urow->{'post_city'}) { $urow->{'post_city'} = ''; }
				if (!$urow->{'invoice_name'}) { $urow->{'invoice_name'} = ''; }
				if (!$urow->{'invoice_lastname'}) { $urow->{'invoice_lastname'} = ''; }
				if (!$urow->{'invoice_address'}) { $urow->{'invoice_address'} = ''; }
				if (!$urow->{'invoice_zip'}) { $urow->{'invoice_zip'} = ''; }
				if (!$urow->{'invoice_city'}) { $urow->{'invoice_city'} = ''; }
				if (!$urow->{'invoice_footer'}) { $urow->{'invoice_footer'} = ''; }
				if ($urow->{'account'} && $urow->{'account_primary'}) {
				    $bankaccount = $urow->{'account'};
				}
#				if (!$urow->{'recipient_ten'}) { $urow->{'recipient_ten'} = ''; }
#				if (!$urow->{'recipient_ssn'}) { $urow->{'recipient_ssn'} = ''; }
				if (!$urow->{'jst'}) { $urow->{'jst'} = 0; }
				
				if ($urow->{'countryid'}) {
				    $countryid = $urow->{'countryid'};
				} else {
				    $countryid = 0;
				}

				if ($type == 0) 
				{
				    $ssn = $urow->{'ssn'};
				} else {
				    $ten = $urow->{'ten'};
				}


				if (($urow->{'invoice_name'} ne '') && ($urow->{'invoice_address'} ne '') && ($urow->{'invoice_zip'} ne '') && ($urow->{'invoice_city'} ne '')) 
				{
				    $name = $urow->{'invoice_name'};
				    if ($urow->{'invoice_lastname'} ne '') {
					$name = $urow->{'invoice_name'}.'<br>'.$urow->{'invoice_lastname'};
				    }
				    $address = $urow->{'invoice_address'};
				    $zip = $urow->{'invoice_zip'};
				    $city = $urow->{'invoice_city'};
				    if ($urow->{'countryid'}) {
					$countryid = $urow->{'invoice_countryid'};
				    } else {
					$countryid = 0;
				    }
				    $type = $urow->{'type'};
				    $ten = '';
				    $ssn = '';

				    if ($type == 0)
				    {
				        $ssn = $urow->{'invoice_ssn'};
				    } else {
				        $ten = $urow->{'invoice_ten'};
				    }

				}

				
				
				my $ddbq = $dbase->prepare("SELECT shortname, name, address, city, zip, countryid, ten, regon, account, inv_header, inv_footer, inv_author, inv_cplace, urllogofile 
						FROM divisions WHERE id = ?");
				$ddbq -> execute($urow->{'divisionid'});
				my $drow = $ddbq->fetchrow_hashref();
				
				
				my $paytime = $urow->{'paytime'};
				if($paytime eq -1) { $paytime = $deadline; }
				my $fullnumber = docnumber($numbers{$plan}, $numbertemplates{$plan}, $currtime);
				
				# sprawdzamy który nr konta użyć
				if (!$bankaccount) {
				    $bankaccount = $drow->{'account'};
				}
				#template version
				my $dttiv = $dbase->prepare("SELECT value FROM uiconfig WHERE section=? AND var=?;");
				$dttiv->execute('invoices','template_version');
				my $ttiv = $dttiv->fetchrow_hashref();
				if ( !$ttiv->{'value'} ) { $ttiv->{'value'} = '4'; }
				$ttiv->{'value'} = '4';
				
				#template type
				my $dttyp = $dbase->prepare("SELECT value FROM uiconfig WHERE section=? AND var=?;");
				$dttyp->execute('invoices','type');
				my $ttyp = $dttyp->fetchrow_hashref();
				if ( !$ttyp->{'value'} ) { $ttyp->{'value'} = 'pdf'; }
				$ttyp->{'value'} = 'pdf';
				
				#template file
				my $dtfile = $dbase->prepare("SELECT value FROM uiconfig WHERE section=? AND var=?;");
				$dtfile->execute('invoices','template_file');
				my $tfile = $dtfile->fetchrow_hashref();
				if ( !$tfile->{'value'} ) { $tfile->{'value'} = "FT-0100-NEW"; }
				if ($urow->{'templatefile'} ne '-1') {
				    $tfile->{'value'} = $urow->{'templatefile'};
				}
				
				#template sdateview
				my $dtsdv = $dbase->prepare("SELECT value FROM uiconfig WHERE section=? AND var=?;");
				$dtsdv->execute('invoices','sdateview');
				my $tsdv = $dtsdv->fetchrow_hashref();
				if ( !$tsdv->{'value'} ) { $tsdv->{'value'} = '0'; }
				
				my $dbalanceinfo = $dbase->prepare("SELECT value FROM uiconfig WHERE section=? AND var=?;");
				$dbalanceinfo->execute('invoices','print_balance_info');
				my $balanceinfo = $dbalanceinfo->fetchrow_hashref();
				if (!defined $balanceinfo->{'value'}) { $balanceinfo->{'value'} = '1'; }
				if ($urow->{'print_balance_info'} ne '-1') {
				    $balanceinfo->{'value'} = $urow->{'print_balance_info'};
				}
				
				#template logo file
				my $urllogofile;
				if ( ($drow->{'urllogofile'} // '') ne '') {
				    $urllogofile = $drow->{'urllogofile'};
				} else {
				    my $dtlogo = $dbase->prepare("SELECT value FROM uiconfig WHERE section=? AND var=?;");
				    $dtlogo->execute('invoices','urllogofile');
				    my $tlogo = $dtlogo->fetchrow_hashref();
				    $urllogofile = $tlogo->{'value'} || '';
				}
				
				my $balance = getCustomerBalance($uid,$currtime);
				if (!$balance) {
				    $balance = '0.00';
				}
				
				if ($type == 0) {
				    $splitpayment = 0;
				}
				
				my $idbq = $dbase->prepare("INSERT INTO documents (number, numberplanid, type, countryid, divisionid,
						customerid, name, address, zip, city, ten, cdate, sdate, paytime, paytype,
						div_name, div_address, div_city, div_zip, div_countryid, div_ten, div_regon, div_account, 
						div_inv_header, div_inv_footer, div_inv_author, div_inv_cplace, div_shortname, fullnumber,
						version, templatetype, templatefile, sdateview, urllogofile, post_name, post_address, post_zip, post_city,print_balance_info,reversion,
						recipient_lastname, recipient_name, recipient_address, recipient_zip, recipient_city,balance_info_before,splitpayment,jpk_v7_tp,
						recipient_ten, recipient_ssn, jst)
						VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
						?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
				
				$idbq->execute($numbers{$plan}, $plan, $assign->{'invoice'}, $countryid, $urow->{'divisionid'}, $uid,
						$name, $address, $zip, $city, $ten, $currtime, $saledate, $paytime, $inv_paytype,
						$drow->{'name'}, $drow->{'address'}, $drow->{'city'}, $drow->{'zip'}, $drow->{'countryid'},
						$drow->{'ten'}, $drow->{'regon'}, 
						$bankaccount, 
						$drow->{'inv_header'}, 
						($urow->{'invoice_footer'} ? $urow->{'invoice_footer'} : $drow->{'inv_footer'}),
						$drow->{'inv_author'}, $drow->{'inv_cplace'}, $drow->{'shortname'}, $fullnumber,$ttiv->{'value'},
						$ttyp->{'value'}, $tfile->{'value'},$tsdv->{'value'}, $urllogofile,
						$urow->{'post_name'}, $urow->{'post_address'}, $urow->{'post_zip'}, $urow->{'post_city'}, $balanceinfo->{'value'}, 5,
						$urow->{'recipient_lastname'}, 
						$urow->{'recipient_name'}, 
						$urow->{'recipient_address'}, 
						$urow->{'recipient_zip'}, 
						$urow->{'recipient_city'},
						$balance,
						$splitpayment,
						$urow->{'jpk_v7_tp'},
						$urow->{'recipient_ten'},
						$urow->{'recipient_ssn'},
						$urow->{'jst'}
						);

				$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_tarrif
					AND docid=$invoices{$uid} AND description=? AND pdiscount=$assign->{'pdiscount'}
					AND vdiscount=$assign->{'vdiscount'}");
			$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 pdiscount=$assign->{'pdiscount'} AND vdiscount=$assign->{'vdiscount'}");
				$icdbq->execute($desc);

				$cdbq = $dbase->prepare("UPDATE cash SET value = value+($val_tarrif*-1)
						WHERE docid = $invoices{$uid} AND itemid = $tmp_itemid");
				$cdbq->execute();
				
				if ($assign->{'splitpayment'}) {
				    my $splittmp = $dbase->prepare("UPDATE documents SET splitpayment=1 WHERE id=$invoices{$uid}");
				    $splittmp->execute();
				}
			}
			else
			{
				$itemid++;

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

				$cdbq = $dbase->prepare("INSERT INTO cash (time, value, taxid, customerid, comment, docid, itemid)
						VALUES ($currtime, $val_tarrif * -1, $assign->{'taxid'}, $uid, ?, $invoices{$uid}, $itemid)");
				$cdbq->execute($desc);
				
				if ($assign->{'splitpayment'}) {
				    my $splittmp = $dbase->prepare("UPDATE documents SET splitpayment=1 WHERE id=$invoices{$uid}");
				    $splittmp->execute();
				}
			}

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

		print STDERR "CID:$uid\tVAL:$val_tarrif\tDESC:$desc\n" if not $quiet;
                 if (($assign->{'period'} == 3 )&& (strftime("%j",localtime($today)) == (strftime("%j",localtime($assign->{'datefrom'}))))) {my $dsa = $dbase->prepare("UPDATE assignments SET settlement = 0 WHERE id = $assign->{'assignmentid'}"); $dsa->execute(); $assign->{'settlement'} = 0 ;print STDERR "usunięto wyrównanie, ponieważ data naliczenia i data uruchomienia skryptu są takie same\n" if not $quiet;}
                 if ($assign->{'period'} == 0) {my $dsa = $dbase->prepare("UPDATE assignments SET settlement = 0 WHERE id = $assign->{'assignmentid'}"); $dsa->execute(); $assign->{'settlement'} = 0 ;print STDERR "usunięto wyrównanie, ponieważ taryfa jest jednorazowa\n" if not $quiet;}
                 
		# settlements accounting
                $val = sprintf("%.2f", $val);
		if ($assign->{'settlement'} && $assign->{'datefrom'})
		{
			my $alldays = 1;
        		my $diffdays = sprintf("%d", 31 - strftime("%d", localtime($assign->{'datefrom'})));
			my $period = strftime("%Y/%m/%d", localtime($assign->{'datefrom'}))." - ".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", $val/$alldays * $diffdays);
            my $vdiscountnqf  = sprintf("%.2f", $assign->{'vdiscount'}/$alldays * $diffdays);

			#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/\%type/$tarifftypes{$assign->{'tarifftype'}}/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, pdiscount, vdiscount)
							VALUES ($invoices{$uid}, $value, $assign->{'taxid'}, '$assign->{'prodid'}',
							'szt.', 1, ?, $assign->{'tariffid'}, $itemid, $assign->{'pdiscount'}, $vdiscountnqf)");
					$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();

my $splitcheck = $dbase->prepare("SELECT d.id AS docid, c.id AS cid, c.type AS customertype , SUM(i.value) AS value 
	FROM documents d 
	JOIN customers c ON (c.id = d.customerid) 
	LEFT JOIN invoicecontents i ON (i.docid = d.id) 
	WHERE d.cdate = $currtime 
	GROUP BY d.id 
	;");
$splitcheck->execute();

while (my $split = $splitcheck->fetchrow_hashref()) {
    
    if ($split->{'customertype'} > 0) {
	if ($split->{'value'} >= 15000) {
	    print STDERR "CID:$split->{'cid'}\tDOC:$split->{'docid'}\tVAL : $split->{'value'}\toznaczenie : mechanizm podzielonej platnosci\n";
	    my $usplit = $dbase->prepare("UPDATE documents SET splitpayment=1 WHERE id=?;");
	    $usplit->execute($split->{'docid'});
	    $usplit->finish()
	}
	
    } else {
	my $usplit = $dbase->prepare("UPDATE documents SET splitpayment=0 WHERE id=?;");
	    $usplit->execute($split->{'docid'});
	    $usplit->finish();
    }
    
}

$splitcheck->finish();

# check invoices (autoclose invoices)
if ($check_invoices)
{
	my $dbq = $dbase->prepare("UPDATE documents SET closed = 1
		WHERE customerid IN (
			SELECT customerid
			FROM cash WHERE time <= $currtime
			GROUP BY customerid
			HAVING SUM(value) >= 0
		)
		AND type IN (1, 3, 5)
		AND cdate <= $currtime
		AND closed = 0");
	$dbq->execute();
	$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:\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();
