#!/usr/bin/perl -w

use strict;
use DBI;
use Data::Dump qw/dump/;
use Algorithm::CheckDigits;
use Text::Unaccent;
use DateTime;

my $ferweb_csv = 'report_62836_1026_Aktivni_studenti.csv';
my $dt = DateTime->now()->add( years => 1 );
$dt->set( month => 10, day => 31 ); # next year, 31.10.
my $new_dateexpiry = $dt->ymd;

warn "# new_dateexpiry: $new_dateexpiry\n";

die "$ferweb_csv : $!" unless -r $ferweb_csv;

my $c = DBI->connect("dbi:CSV:","","", { RaiseError => 1, f_ext => '.csv', f_encoding => 'utf-8', csv_sep_char => ';' }) || die $dbi::errstr;
my $k = DBI->connect("dbi:mysql:database=koha_fer","","", { RaiseError => 1, AutoCommit => 0, mysql_enable_utf8 => 1 }) || die $DBI::errstr;

my $ferweb_table = $ferweb_csv;
$ferweb_table =~ s/\.csv//i;

my $s = $c->prepare(qq{
select * from $ferweb_table
});
$s->execute;

my $user_exists = $k->prepare(qq{
select * from borrowers where cardnumber = ?
});

my $insert_user = $k->prepare(qq{
insert into borrowers 
(cardnumber, categorycode, userid, firstname, surname, branchcode, email, dateexpiry)
values
(?,'S',?,?,?,'SRE',?,'2021-12-13')
});

my $update_dateexpiry = $k->prepare(qq{
update borrowers
set dateexpiry = ?
where cardnumber = ?
});

my $stat;

while( my $row = $s->fetchrow_hashref ) {

#	warn "# row = ",dump($row),$/;

	$user_exists->execute( 'S' . $row->{jmbag} );

	if ( $user_exists->rows ) {
		my $user = $user_exists->fetchrow_hashref;
#		warn "# koha borrower = ",dump($user);
		print "UPD ";
		$stat->{updated}++;
		$update_dateexpiry->execute( $new_dateexpiry, 'S' . $row->{jmbag} );
	} elsif ( ! $row->{nick} ) {
		warn "NEW (without nick) ",dump($row);
		$stat->{new_no_nick}++;
		print "NC? ";
	} else {
		warn "NEW: ",dump($row);
		$insert_user->execute( 'S' . $row->{jmbag}, $row->{nick}, $row->{ime}, $row->{prezime}, $row->{email} );
		$stat->{new}++;
		print "NEW ";
	}

	print join(" "
		, $row->{jmbag}
		, $row->{nick}
		, $row->{email}
	),$/;

}

$k->commit;

warn "# stat ", dump( $stat ),$/;
