#!/usr/bin/perl

use strict;
use warnings;

use utf8;
use Encode;

use FindBin;
BEGIN { unshift @INC, "$FindBin::Bin/lib" }

use NCRB::FileStructure;
use NCRB::FileParser;

use DBI;
use Digest;#::MD5 qw/md5_hex/;

use Data::Dumper;

binmode STDOUT, ':utf8';

my $dbh = DBI->connect('dbi:Firebird:db=/srv/firebird/MAINBASE.FDB;ib_charset=WIN1251', 'sysdba', 'masterkey');
die $dbh->errstr if $dbh->errstr;
print "Database connected!\n";

sub trim($)
{
    my $string = shift;
    $string =~ s/^\s+//;
    $string =~ s/\s+$//;
    return $string;
}

sub genID($$) {
    my $prefix = shift;
    my $string = shift;
    my $crc = Digest->new('SHA-1');
    $crc->add($string);

    return "$prefix" . $crc->hexdigest; #md5_hex($string);
}

sub cnv($) {
    my $string = shift;
    return encode('cp1251', $string);
}

sub saveBlock {
    my $data = shift;

    for (my $strId=0; $strId<scalar(@$data); $strId++) {
        my $strType = shift @{$data->[$strId]};
        if ($strType eq 'P') {
            print "Saving string P...\n";
            print Dumper $data->[$strId];
            save_P($data->[$strId]);
            exit;
        } elsif ($strType eq 'C') {

        } elsif ($strType eq 'O') {

        } elsif ($strType eq 'M') {

        } elsif ($strType eq 'I') {

        }
    }
}

sub save_P {
    my $str = shift;
    $dbh->{AutoCommit} = 1;

#    my $sel_q = <<EOQ;
#
#    SELECT ID FROM REF_PATIENTS
#    WHERE (TITLE = ?)
#    OR (
#        FAM = ?
#        AND IM = ?
#        AND OT = ?
#    );
#
#EOQ

    my $ins_q = <<EOQ;

    INSERT INTO REF_PATIENTS(ID, CODE, TITLE, FAM, IM, OT, SEX, BIRTHDAY, SNILS)
    VALUES (gen_id(GEN_REF_PATIENTS_CODE,1),?,?,?,?,?,?,?,?)
    RETURNING ID;

EOQ

    my $sel_q = "SELECT ID FROM REF_PATIENTS"
        . " WHERE (TITLE = '$str->[2] $str->[3] $str->[4]')"
        . " OR ("
        . " FAM = '$str->[2]'"
        . " AND IM = '$str->[3]'"
        . " AND OT = '$str->[4]'"
        . ");"
    ;
    print "$sel_q\n";
    my $sth_s = $dbh->prepare(cnv($sel_q));
    #$sth_s->bind_param(1, cnv("$str->[2] $str->[3] $str->[4]"));
    #$sth_s->bind_param(2, cnv($str->[2]));
    #$sth_s->bind_param(3, cnv($str->[3]));
    #$sth_s->bind_param(4, cnv($str->[4]));
    $sth_s->execute or die "ERROR: Failed to find record! $!";

    print "Sex orig: $str->[5]\n";
    my $sex = ($str->[5] eq 'М' ? 'Муж.' :
        ($str->[5] eq 'Ж' ? 'Жен.' :
            undef
        )
    );
    print "Sex: $sex\n";

    if ($sth_s->rows == 0) {
        #not found
        print "Not found. Creating a new record.\n";
        #my $sth_i = $dbh->prepare($ins_q);
        #$sth_i->execute(
        #    cnv($str->[0]),
        #    cnv("$str->[2] $str->[3] $str->[4]"),
        #    cnv($str->[2]),
        #    cnv($str->[3]),
        #    cnv($str->[4]),
        #    cnv($sex),
        #    cnv($str->[6]),
        #    cnv($str->[8])
        #) or die 'ERROR!';
        #$dbh->commit;
        print "TITLE: $str->[2] $str->[3] $str->[4]\n";
        my $ins_q_1 = "INSERT INTO REF_PATIENTS(ID, CODE, TITLE, FAM, IM, OT, SEX, BIRTHDAY, SNILS) "
        . " VALUES("
        . "gen_id(GEN_REF_PATIENTS_CODE, 1),"
        . "'" . trim($str->[0]). "',"
        . "'" . "$str->[2] $str->[3] $str->[4]". "',"
        . "'" . $str->[2]. "',"
        . "'" . $str->[3]. "',"
        . "'" . $str->[4]. "',"
        . "'" . $sex. "',"
        . "'" . $str->[6]. "',"
        . "'" . $str->[8]. "'"
        . ")"
        . " RETURNING ID;"
        ;
        print "QUERY: $ins_q_1\n";
        my $sth_i = $dbh->prepare(cnv($ins_q_1));
        $sth_i->execute or die 'ERROR!';
        #$sth_i->commit;
        print "ID: " . $sth_i->fetchrow_arrayref->[0] . "\n\n";
        print "Done.\n";
    } elsif ($sth_s->rows > 1) {
        while (my $row = $sth_s->fetchrow_arrayref) {
            print Dumper $row;
        }
        die "ERROR! Found " . $sth_s->rows . " for\n $str\n\n";
    } elsif ($sth_s->rows == 1) {
        return $sth_s->fetchrow->arrayref->[0];
    } else {
        die "ERROR! Select returned code: " . $sth_s->rows . "\n";
    }
}

sub main {

    my $proto = NCRB::FileStructure->new->init;
    #$proto->addDataString('L', 'Характеристика учреждения', '1', 1, '8', 'Код Учреждения по ОКПО', 'L', 1, 'C', 8, 0);

    #print Dumper $proto;

    my $parser = NCRB::FileParser->new(file => '36825093.txt', proto => $proto);
    print "Parsing data...\n\n";
    $parser->parse;
    #print Dumper $parser->getData;

    #print Dumper $parser->getData->[0];
    #exit;

    for (my $blockId=0; $blockId < scalar (@{$parser->getData}); $blockId++) {
        saveBlock($parser->getData->[$blockId]);
        #for (my $stringId=0; $stringId < scalar (@{$parser->getData->[$blockId]}); $stringId++) {
        #    saveString($parser->getData->[$blockId]->[$stringId]
        #    my $strType = $parser->getData->[$blockId]->[$stringId]->[0];
        #    for (my $fieldId=0; $fieldId < scalar(@{$parser->getData->[$blockId]->[$stringId]}); $fieldId++) {
        #        #Encode::_utf8_off($parser->getData->[$a]->[$b]);
        #        print "".$parser->getData->[$blockId]->[$stringId]->[$fieldId]."#";
        #    }
        #    print "\n";
        #}
    }

}


main;