#!/usr/bin/perl -Tw

use strict;
use DBI;
use Config::IniFiles;
use Getopt::Long;
use vars qw($configfile $fakedate $test);
use POSIX qw(strftime);
use POSIX qw(mktime);
use Time::Local;
use warnings;
use Text::CSV;
use Text::Iconv;


use locale;
use POSIX qw(locale_h);
use POSIX qw(setlocale);
setlocale(LC_ALL,"pl_PL.iso88592");
setlocale (&POSIX::LC_ALL,"pl");

my $file = 'report-sysberg.csv';

my %options = (
	"--config-file|C=s"	=>	\$configfile,
	"--fakedate|f=s"	=>	\$fakedate,
	"--test|q"		=>	\$test,
);

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

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

if(! -r $configfile)
{
	print STDERR "Fatal error: Unable to read configuration file $configfile, 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 = sprintf("%d",strftime("%m",localtime2()));
#my $day = strftime("%e",localtime2());
my $day = 1;

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

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

my $csv = Text::CSV->new({ binary => 1, sep_char => ';'} );

open (CSV, "<", $file) or die $!;

my $import_ok=0;
my $import_fail=0;
my $import_empty_fraction=0;
my $import_brutto=0;
my $import_bm_ok=0;
my $import_bm_brutto=0;

while (<CSV>) {
    my $line = $_;
    if ($csv->parse($line)) {
	my @columns = $csv->fields();
	# sprawdzenie czy numer klienta jest liczbą
	if ($columns[0] =~ m/^\d+$/ ) {
	    # czy frakcja jest pusta i równa zeru
	    if ($columns[5] eq "0.000000" && $columns[4] eq "") {
		print "Frakcja bez nazwy za zerową kwotę\n";
		$import_empty_fraction++;
	    }
	    # czy opłata jest za telefony firmowe
	    elsif ($columns[0] eq "7865") {
		print "Telefon firmowy - $columns[4] za kwotę $columns[5]\n";
		$import_bm_brutto += $columns[5];
		$import_bm_ok++;
	    }
	    else {
		# sprawdzenie czy jest klient o takim id
		my $checkcustomer = $dbase->prepare("SELECT id FROM customers WHERE id = $columns[0] AND status = 3");
		$checkcustomer->execute();
		my $row = $checkcustomer->fetchrow_hashref();
		if ($row) {
		    $columns[2] =~ m/(\d{2})(\d{2})(\d{4})$/;
		    my $start = "$3/$2/$1";
		    $columns[3] =~ m/(\d{2})(\d{2})(\d{4})$/;
		    my $end = "$3/$2/$1";
		    my $wartosc = sprintf "%.2f", $columns[5];
		    my $nazwa = "$columns[4] z numeru $columns[1] za okres $start - $end ($columns[8] poł., $columns[7]s)";
		    if(!$test) {
			my $liadb = $dbase->prepare("INSERT INTO liabilities (name, value, taxid) VALUES (?, ?, ?)");
			$liadb->execute($nazwa, $columns[5], 2 ) or die "Can't execute SQL\n";
			$liadb = $dbase->prepare("SELECT id FROM liabilities WHERE name = '$nazwa' AND value = '$columns[5]' LIMIT 1");
			$liadb->execute() or die "Can't execute SQL\n";
			my $row = $liadb->fetchrow_hashref();
			my $liabid = $row->{'id'};
			$liadb = $dbase->prepare("INSERT INTO assignments (liabilityid, customerid, at, datefrom, dateto, invoice) VALUES (?, ?, $daystart, $daystart, $dayend, ?)");
			$liadb->execute($liabid, $columns[0], 1);
			$liadb->finish();
		    }
		    print "$nazwa za kwotę $wartosc\n";
		    $import_ok++;
		    $import_brutto +=$columns[5];
		}
		else {
		    print "Użytkownika numer $columns[0] nie ma w bazie\n";
		    $import_fail++;
		}
		$checkcustomer->finish();
	    }
	}
	else {
	    print "Nieprawidłowy numer klienta: $columns[0]\n";
	    $import_fail++;
	}
    }
    else {
#	my $err = $csv->error_input;
	print "Failed to parse line: \n";
    }
}
print "\nZaimportowano $import_ok zobowiązań na kwotę $import_brutto pln\n";
print "Nie powiodło się $import_fail zobowiązań\n";
print "Było $import_empty_fraction pustych frakcji za zerową kwotę\n";
print "Było $import_bm_ok frakcji dla firmy za kwotę $import_bm_brutto brutto\n";
close CSV;
$dbase->disconnect();