[lms] Re: poczatek okresu rozliczeniowego.

11 Sty 2005

      Jaroslaw Dziubek napisał(a):
...
[Monday, 10 January 2005], KubaTyszko napisał(a):
Ale fakture wystawic mozesz wczesniej (pierwszego?). IMHO komplikujesz
sobie zycie. Skoro placa _do_ 10. za _BIEZACY_ miesiac to od 1. do 10.
maja saldo ujemne (ktore jest dozwolone), od 10. do 20. (w Twoim
przypadku) saldo ujemne, ktore jest dopuszczalne ;-) i od 20. maja saldo
ujemne, ale nie maja netu ;-)
Tak, tylko jak wytlumaczyc uzyszkodnikom ze maja dozwolone saldo ujemne gdy
spojza w swoj bilans przed 10 ??? A i admina bedzie troszke draznic jak 
bedzie
juz pierwszego na  minusie...
Dla bardzo chcacych jednak zmienic wpisy w bazie przesylam pliczek 
lms-payments
z przerobionym wpisem w linijce 148 - $txts{1} = "Abonament miesięczny...
- dodana -9 i 5
Efekt koncowy:
Abonent w podgladzie bilansu widzi ze ma oplacac okres rozliczeniowy od 
1 do 30.
Naliczanie nastepuje normalnie 10 kazdego miesiaca...
ps. sorki ze dwa posty ale zapomnialem zalaczyc pliczek...
-- 
pozdrawiam;
andzio

#!/usr/bin/perl -Tw
#
# LMS version 1.3-cvs
#
#  (C) 2001-2004 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.34 2004/08/19 20:19:00 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);

my $_version = '1.3-cvs';

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-2004 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=YYYYMMDD		override system date

EOF
	exit 0;
}

if($version)
{
	print STDERR <<EOF;
lms-payments, version $_version
(C) 2001-2004 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-2004 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;

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 $deadline = $ini->val('payments', 'deadline') || '14';
my $paytype = $ini->val('payments', 'paytype') || 'PRZELEW';
my $payday = sprintf("%d",strftime("%e",localtime()));

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;
}

if($fakedate)
{
	my @fakedate = split(//, $fakedate);
	print @fakedate[0..1];
	die;
}

my $month = sprintf("%d",strftime("%m",localtime()));
my $day = strftime("%e",localtime());
my $year = strftime("%Y",localtime());
my $weekday = strftime("%u",localtime());
my $yearday = strftime("%j",localtime());
my $quarter;
if($month==1 || $month==4 || $month==7 || $month==10) {
	$quarter = $day;
} elsif ($month==2 || $month==5 || $month==8 || $month==11) {
	$quarter = $day + 100;
} else {
	$quarter = $day + 200;
}
my %txts;

$txts{0} = "Abonament tygodniowy \"%\" (za okres ".strftime("%Y/%m/%d", 0, 0, 12, $day, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 12, $day + 7, $month - 1, $year - 1900).")";
$txts{1} = "Abonament miesiêczny \"%\" (za okres ".strftime("%Y/%m/%d", 0, 0, 12, $day - 9, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 12, $day - 10, $month, $year - 1900).")";
$txts{2} = "Abonament kwartalny \"%\" (za okres ".strftime("%Y/%m/%d", 0, 0, 12, $day, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 12, $day - 1, $month + 2, $year - 1900).")";
$txts{3} = "Abonament roczny \"%\" (za okres ".strftime("%Y/%m/%d", 0, 0, 12, $day, $month - 1, $year - 1900)." - ".strftime("%Y/%m/%d", 0, 0, 12, $day - 1, $month - 1, $year - 1900 + 1).")";

my $yearstart = strftime("%s", 0, 0, 0, 1, 0, $year - 1900);
my $yearend = strftime("%s", 59, 59, 23, 31, 11, $year - 1900);

my $dbq = $dbase->prepare("SELECT MAX(number) AS number FROM invoices WHERE cdate >= $yearstart AND cdate <= $yearend");
$dbq->execute();
my $row = $dbq->fetchrow_hashref();
my $number = $row->{'number'} || 0;
my %gotinv;
my $time = strftime("%s",localtime());
my $icdbq;
my $suspended = 0;

# dobra, query wybiera *WSZYSTKIE* mo¿liwe assigmenty na dany dzieñ.

$dbq = $dbase->prepare("SELECT assignments.id AS id, tariffid, userid, period, at, value, taxvalue, pkwiu, uprate, downrate, tariffs.name AS name, invoice FROM assignments, tariffs, users WHERE tariffs.id = tariffid AND userid = users.id AND status = 3 AND deleted = 0 AND suspended = 0 AND ((period = 0 AND at = $weekday) OR (period = 1 AND at = $day) OR (period = 2 AND at = $quarter) OR (period = 3 AND at = $yearday)) AND (datefrom <= $utsfmt OR datefrom = 0) AND (dateto > $utsfmt OR dateto = 0) ORDER BY userid, value ASC");
$dbq->execute();

print "UID:\tKwota:\tZa:\n";

while(my $assign = $dbq->fetchrow_hashref())
{
	my $uid = $assign->{'userid'};

	# sprawdzamy, czy wszystkie p³atno¶ci usera nie s± zawieszone
	if($suspended != $uid)
	{
		my $xdbq = $dbase->prepare("SELECT 1 FROM assignments, users WHERE userid = users.id AND tariffid = 0 AND (datefrom <= $utsfmt OR datefrom = 0) AND (dateto > $utsfmt OR dateto = 0) AND userid = $uid");
		$xdbq->execute();
		if(my $xrow = $xdbq->fetchrow_hashref())
		{
			$suspended = $uid;
			next;
		}
	} else {
		next;
	}

	my $txt = $txts{$assign->{'period'}};
	$txt =~ s/\%/$assign->{'name'}/g;
	$gotinv{$uid} = 0 if not defined $gotinv{$uid};
	if($assign->{'value'} != 0)
	{
		if($assign->{'invoice'})
		{
			if($gotinv{$uid} eq 0)
			{
				$number++;
				my $udbq = $dbase->prepare("SELECT * FROM users WHERE id=$uid");
				$udbq->execute();
				my $urow = $udbq->fetchrow_hashref();
				my $idbq = $dbase->prepare("INSERT INTO invoices (number, customerid, name, address, zip, city, phone, nip, pesel, cdate, paytime, paytype, finished) VALUES ($number, $uid, '$urow->{'lastname'} $urow->{'name'}', '$urow->{'address'}', '$urow->{'zip'}', '$urow->{'city'}', '$urow->{'phone1'}', '$urow->{'nip'}', '$urow->{'pesel'}', $time, $deadline, '$paytype', 1)");
				$idbq->execute();
				$idbq = $dbase->prepare("SELECT id FROM invoices WHERE number=$number AND cdate=$time AND finished=1 AND customerid=$uid");
				$idbq->execute();
				my $irow = $idbq->fetchrow_hashref();
				$gotinv{$uid} = $irow->{'id'};
			}

			my $icdbq = $dbase->prepare("SELECT * FROM invoicecontents WHERE tariffid=$assign->{'tariffid'} AND invoiceid=$gotinv{$uid} AND description='$txt'");
			$icdbq->execute();
			if(my $icrow = $icdbq->fetchrow_hashref())
			{
				$icdbq = $dbase->prepare("UPDATE invoicecontents SET count=count+1 WHERE tariffid=$assign->{'tariffid'} AND invoiceid=$gotinv{$uid} AND description='$txt'");
				$icdbq->execute();
			}
			else
			{
				if ($assign->{'taxvalue'})
				{
					$icdbq = $dbase->prepare("INSERT INTO invoicecontents (invoiceid, value, taxvalue, pkwiu, content, count, description, tariffid) VALUES ($gotinv{$uid}, $assign->{'value'}, $assign->{'taxvalue'}, '$assign->{'pkwiu'}', 'szt', 1, '$txt', $assign->{'tariffid'})");
				}
				else
				{
					$icdbq = $dbase->prepare("INSERT INTO invoicecontents (invoiceid, value, taxvalue, pkwiu, content, count, description, tariffid) VALUES ($gotinv{$uid}, $assign->{'value'}, NULL, '$assign->{'pkwiu'}', 'szt', 1, '$txt', $assign->{'tariffid'})");
				}
				$icdbq->execute();
			}
		}
		my $sdbq;
		if ($assign->{'taxvalue'})
		{
			$sdbq = $dbase->prepare("INSERT INTO cash (time, adminid, type, value, taxvalue, userid, comment, invoiceid) VALUES ($time, 0, 4, $assign->{'value'}, $assign->{'taxvalue'}, $uid, '$txt', $gotinv{$uid})");
		}
		else
		{
			$sdbq = $dbase->prepare("INSERT INTO cash (time, adminid, type, value, taxvalue, userid, comment, invoiceid) VALUES ($time, 0, 4, $assign->{'value'}, NULL, $uid, '$txt', $gotinv{$uid})");
		}
		$sdbq->execute();
		print "$uid\t$assign->{'value'}\t$txt\n";
	}
}	

# op³aty sta³e
$dbq = $dbase->prepare("SELECT * FROM payments WHERE value <> 0 AND ((period = 0 AND at=$weekday) OR (period = 1 AND at=$day) OR (period = 2 AND at = $quarter) OR (period = 3 AND at = $yearday))");
$dbq->execute();

while(my $assign = $dbq->fetchrow_hashref())
{
	my $sdbq = $dbase->prepare("INSERT INTO cash (time, adminid, type, value, userid, comment, invoiceid) VALUES ($time, 0, 2, $assign->{'value'}, 0, '$assign->{'name'}/$assign->{'creditor'}', 0)");
	$sdbq->execute();
	print "0\t$assign->{'value'}\t$assign->{'name'}/$assign->{'creditor'}\n";
}

$dbq = $dbase->prepare("DELETE FROM timestamps WHERE tablename = 'cash' OR tablename = '_global'");
$dbq->execute();
$dbq = $dbase->prepare("INSERT INTO timestamps (tablename,time) VALUES ('cash',$utsfmt)");
$dbq->execute();
$dbq = $dbase->prepare("INSERT INTO timestamps (tablename,time) VALUES ('_global',$utsfmt)");
$dbq->execute();
$dbq = $dbase->prepare("DELETE FROM assignments WHERE dateto < $utsfmt - 86400 * 30 AND dateto != 0");
$dbq->execute();
$dbase->disconnect();

[lms] Re: poczatek okresu rozliczeniowego.

Andrzej Banach