#!/usr/bin/perl -w
use strict;
use Text::CSV_XS;
use DBI;
use Data::Dumper;

sub fix_value
{
#   3.123.321,12 PLN
    $_ = shift;
    s/\.//g;
    s/,/./;
    s/ [A-Z]$//;
    return $_;
}

sub fix_date
{
#    03/07/2006
    $_ = shift;
    s!(\d\d)/(\d\d)/(\d\d\d\d)!$3-$2-$1!;
    return $_;
}
my $inifile = "credentials.ini";



my %cfg = ();
open(F, "<", $inifile) or die "cannot open $inifile ($!)\n";
while (<F>)
{
    chomp;

    next if /^#/; # comments
    next if /^\s*$/; # empty

    my ($n, $v) = split(/\s*=\s*/, $_, 2);
    $cfg{$n} = $v;
}
close(F);


my $debug = 0;

my @hdr_ror = (
"data operacji",
"data księgowania",
"opis operacji",
"id / nazwa",
"tytul platnosci",
"wplywy",
"wydatki",
"saldo po operacji",
);

my @hdr_visa = (
"data księgowania",
"data operacji",
"opis transakcji",
"kwota",
"prowizja",
"razem",
);

my $xfile = $ARGV[0];
exit unless $xfile;



my $dbh=DBI->connect(
    "DBI:mysql:database=$cfg{db_db};host=$cfg{db_host}",
    $cfg{db_user},
    $cfg{db_pass},
    {
        PrintError => 1,
        RaiseError => 1,
        AutoCommit => 1,
    }
    );

my $l = $dbh->prepare("SET NAMES latin2");
$l->execute();
$l = $dbh->prepare("SET CHARACTER SET latin2");
$l->execute();
$l = $dbh->prepare("SET COLLATION_CONNECTION='latin2_general_ci'");
$l->execute();




print "Update($xfile)\n" if $debug;


open(A, "<${xfile}.csv");
$_ = <A>;
if (!/^data /)
{
	print "ERROR - not a KB24 CSV file:\n";
	print `cat ${xfile}.csv`;
	exit(1);
}

my $visa_mode = 0;
$visa_mode = 1 if /opis transakcji/;

my $stmt =
    $visa_mode ?
    "select count(*) as c, md5(?) as h from visa where hasz=md5(?)" :
    "select count(*) as c, md5(?) as h from ror  where hasz=md5(?)";

my $sth = $dbh->prepare($stmt) or die "Couldn't prepare statement: " . $DBI::err." - ".$DBI::errstr;

my $new = 0;
my @nl; # new lines
my %todayh = ();
while (<A>)
{
    chomp;
    next if (/^\s*$/); #empty
    next if (/^data operacji/); #hdr

    tr/\xA5\x8C\x8F\xB9\x9C\x9F/\xA1\xA6\xAC\xB1\xB6\xBC/; # fix pliterki

    tr/\241\306\312\243\321\323\246\257\254/ACELNOSZZ/; # kill'em
    tr/\261\346\352\263\361\363\266\277\274/acelnoszz/;


    $sth->execute($_, $_);
    my $cnth = $sth->fetchrow_hashref;

    $todayh{$cnth->{h}}++;
    my $todaycnt = defined $todayh{$cnth->{h}} ? $todayh{$cnth->{h}} : 0;

    if ($cnth->{c} < $todaycnt) # we have new entry
    {
        $new = 1;
        push(@nl, $_);
    }

}
close(A);


my $addror = $dbh->prepare("
insert into ror
(data_op, data_ks, opis, identyfikator, tytul, kwota, saldo, ts_ksiegowania, hasz) values
(?,       ?,       ?,    ?,             ?,     ?,     ?,     now(),          md5(?))
")
or die "Couldn't prepare statement: " . $DBI::err." - ".$DBI::errstr;

my $addvisa = $dbh->prepare("
insert into visa
(data_op, data_ks, tytul, kwota, prowizja, ts_ksiegowania, hasz) values
(?,       ?,       ?,     ?,     ?,        now(),          md5(?))
")
or die "Couldn't prepare statement: " . $DBI::err." - ".$DBI::errstr;

my $csv = Text::CSV_XS->new();
if ($new)
{
    for (@nl)
    {
        my $theline = $_;
        my $ret = $csv->parse($_);
        if (!$ret)
        {
            printf STDERR "CSV Error: %s\n", $csv->error_input();
            next;
        }

        my @c = $csv->fields();

        if ($visa_mode) # CC mode
        {

            $addvisa->execute(fix_date($c[1]),fix_date($c[0]),$c[2],fix_value($c[3]), fix_value($c[4]), $theline);
            my $subj = "$c[5] ($c[2])";

            my $i = 0;
            for ($i = 0; $i < 6; $i++)
            {
                printf(STDERR "%20s : %s\n", $hdr_visa[$i], $c[$i]);
            }
            printf(STDERR "\n\n");

            if ($cfg{email})
            {
                open(M, "|mail -s '[KB24 Visa] $subj' $cfg{email}") or die("Cannot send email: $!");

                my $i = 0;
                for ($i = 0; $i < 6; $i++)
                {
                    printf(M "%20s : %s\n", $hdr_visa[$i], $c[$i]);
                }
                printf(M "\n\n");
                close(M);
            }

            if ($cfg{sms})
            {
                open(M, "|mail -s 'KB24-V' $cfg{sms}") or die("Cannot send sms: $!");
                print M "$subj\n\n";
                close(M);
            }
        }
        else # ror mode
        {
            # skip limit operations
            next if ($c[2] =~ m/(Uznanie rku-przelew z rku zabezp|Przelew z rachunku na zabezpieczeni)/);

            my $v = fix_value($c[5] ? "$c[5]" : "-$c[6]");
            $addror->execute(fix_date($c[0]),fix_date($c[1]),$c[2],$c[3], $c[4], $v, fix_value($c[7]), $theline);

            my $subj = ($c[5] ? "+$c[5]" : "-$c[6]")." ($c[3] $c[4])";

            my $i = 0;
            for ($i = 0; $i < 8; $i++)
            {
                printf(STDERR "%20s : %s\n", $hdr_ror[$i], $c[$i]);
            }
            printf(STDERR "\n\n");

            if ($cfg{email})
            {
                open(M, "|mail -s '[KB24 Ror] $subj' $cfg{email}")  or die("Cannot send email: $!");

                my $i = 0;
                for ($i = 0; $i < 8; $i++)
                {
                    printf(M "%20s : %s\n", $hdr_ror[$i], $c[$i]) or die("Cannot send sms: $!");
                }
                printf(M "\n\n");
                close(M);
            }

            if ($cfg{sms})
            {
                open(M, "|mail -s 'KB24-R' $cfg{sms}");
                print M "$subj\n\n";
                close(M);
            }

        }



    }

}