#!/usr/bin/perl
# Copyright 2012 TMA
# License: GNU GPL version 2
use strict;
use warnings;
use utf8;
my $status = 'endmail';
my ($fio,$konto,$castka,$VS,$SS,$zprava,$protiucet,$date);
my ($header, $body);
my $mul;
sub vreset() {
($header,$body) = ('','');
($fio,$konto,$castka,$VS,$SS,$zprava,$protiucet,$date) = undef;
}
sub process($) {
$status = $_[0];
no warnings;
$date =~ s/ +\d+:\d+:\d+ +[+-]\d{4}$//o;
$date =~ s/^..., *(\d+) (...|\d+) (\d\d\d\d).*/$3-$2-$1/o;
$date =~ s/Jan/01/o;
$date =~ s/Feb/02/o;
$date =~ s/Mar/03/o;
$date =~ s/Apr/04/o;
$date =~ s/May/05/o;
$date =~ s/Jun/06/o;
$date =~ s/Jul/07/o;
$date =~ s/Aug/08/o;
$date =~ s/Sep/09/o;
$date =~ s/Oct/10/o;
$date =~ s/Nov/11/o;
$date =~ s/Dec/12/o;
$date =~ s/ /0/o;
$date =~ s/-(\d)-/-0$1-/o;
$date =~ s/-(\d)$/-0$1/o;
print "-- $ARGV $.\n";
print "insert into ucetni_data (konto, castka, datum, VS, SS, protiucet, zprava, comment) values
($konto, $castka", commanullq($date),
commanull($VS), commanull($SS), commanullq($protiucet),
commanullq($zprava),commanullq($header."\n\n".$body),");\n";
}
sub def_or_empty($) { defined$_[0]?$_[0]:'' }
sub quot($) {
my ($x) = @_;
# ' needs to be doubled
$x =~ s/'/''/go;
# replace suspicious characters by .
$x =~ s{[^][\na-zA-Z0-9 !@#$%^&*()_=+;:"|<>/?,.~`{}'-]}{.}go; # '
"'$x'"
}
sub commanull_($&) {
my ($x, $q) = (@_);
#print STDERR "[[$x]]\n";
no warnings;
(length $x) ? ", ". $q->($x) : ", NULL"
}
sub commanull($) { commanull_($_[0],sub {$_[0]}) }
sub commanullq($) { commanull_($_[0],\") }
sub decimal($) {
my ($x) = @_;
$x =~ y/,/./;
$x =~ s/ //g;
$x
}
my %action = (
endmail => sub {
#print "$status: $_\n";
/^From / and do {{ $status = 'header'; vreset }}
},
header => sub {
$header .= $_."\n";
#print "$status: $_\n";
/^From: automat(?:\@| at )fio\.cz/ and $fio = 1;
/^$/ and $status = ($fio ? 'body' : 'endmail');
/^Date: (.*)/ and $date = $1;
#print "$status(",def_or_empty($fio),"): $_\n";
},
body => sub {
/^From / or $body .= $_."\n";
#print "$status: $_\n";
/^P..jem na kont.: (\d+)/ and ($konto, $mul) = ($1,1);
/^V.daj na kont.: (\d+)/ and ($konto, $mul) = ($1,-1);
/^..stka: ([0-9 ]+[,.]\d+)?/ and $castka = $mul * decimal($1);
/^VS: (\d+)/ and $VS = $1;
/^SS: (\d*)/ and $SS = $1;
/^Zpr.va p..jemci: (.*)/ and $zprava = $1;
/^Proti..et: ([0-9\/-]+)/ and $protiucet = $1;
/_____/ and process('endmail');
/^From / and (process('header'),vreset);
#/^P[?ř][?í]jem na kont[?ě]: (\d+)/ and $konto = $1;
#/^[?Č][?á]stka: (\d+)[,.](\d+)?/ and $castka = $1 + $2/100;
#/^VS: (\d+)/ and $VS = $1;
#/^SS: (\d*)/ and $SS = $1;
#/^Zpr[?á]va p[?ř][?í]jemci: (.*)/ and $zprava = $1;
#/^Proti..et: ([0-9\/-]+)/ and $protiucet = $1;
#/_____/ and process;
}
);
my ($drop,$create) = (1,1);
my $SEP = ';';
print <<EOF if $drop;
drop table if exists ucetni_data;
EOF
print <<EOF if $create;
CREATE TABLE IF NOT EXISTS ucetni_data (
`id` int(11) NOT NULL AUTO_INCREMENT,
`konto` decimal(30,0) NOT NULL,
`castka` decimal(30,2) NOT NULL,
`datum` date NOT NULL,
`vs` int(11) DEFAULT NULL,
`ss` int(11) DEFAULT NULL,
`protiucet` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`zprava` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`comment` varchar(2000) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=563 ;
EOF
while (<>) {
chomp;
$action{$status}->();
}
process('') if $status eq 'body';