ISSP lookup for OIB and JMBAG, WIP

diff --git a/intranet/cgi-bin/rfid-uid/borrower.pl b/intranet/cgi-bin/rfid-uid/borrower.pl
new file mode 100755 (executable)
index 0000000..a4ba19c
--- /dev/null
+++ b/intranet/cgi-bin/rfid-uid/borrower.pl
@@ -0,0 +1,140 @@
+#!/usr/bin/perl
+
+# Copyright Dobrica Pavlinusic 2024
+#
+# This file is part of Koha.
+#
+# Koha is free software; you can redistribute it and/or modify it under the
+# terms of the GNU General Public License as published by the Free Software
+# Foundation; either version 2 of the License, or (at your option) any later
+# version.
+#
+# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+# A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along
+# with Koha; if not, write to the Free Software Foundation, Inc.,
+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
+
+# designed to work with https://github.com/martinpaljak/nfc4pc
+# java -jar build/libs/nfc4pc-230711-next.jar -d -c -u https://lib.fer.hr:8443/cgi-bin/koha/rfid-uid/borrower.pl
+
+# perl -I/srv/koha_ffzg/ ./borrower.pl 'uid=0492188A741590'
+
+use strict;
+use warnings;
+
+use CGI;
+use C4::Context;
+use LWP::UserAgent qw();
+use Data::Dump qw(dump); # FIXME
+use JSON;
+
+my $json;
+
+my $cert_password;
+{
+       open(my $fh, '<', '/home/dpavlin/certifikat_za_pristup_apiv2/lozinka.txt');
+       local $/ = undef;
+       $cert_password = <$fh>;
+       close($fh);
+}
+
+my $query = new CGI;
+print "Content-type: text/plain; charset=utf-8\r\n\r\n";
+
+my $dbh = C4::Context->dbh;
+
+my @where;
+my @execute;
+
+if ( my $val = $query->param('uid') ) {
+       $json->{param}->{RFID_SID} = uc $val;
+       push @where, "( code = 'RFID_SID' and attribute = ? )";
+       push @execute, $val;
+}
+
+sub generate_sql {
+       my @where = @_;
+my $sql = qq{
+select
+       distinct
+       b.borrowernumber, firstname, surname, email, userid, cardnumber
+from borrower_attributes ba
+join borrowers b on b.borrowernumber = ba.borrowernumber
+where (} . join(') or (', @where) . qq{)};
+
+       return $sql;
+}
+
+my $sql = generate_sql @where;
+warn "# sql $sql\n";
+
+my $sth = $dbh->prepare( $sql );
+$sth->execute( @execute );
+
+$json->{rows} = $sth->rows;
+
+if ( $sth->rows < 1 ) {
+       if ( exists $json->{param}->{RFID_SID} ) {
+               my $sid = $json->{param}->{RFID_SID};
+               my $l = length($sid);
+               if ( $l == 19 or $l == 14 or $l = 8 ) {
+                       warn "ISSP $sid\n";
+
+                       my $ua = LWP::UserAgent->new (
+                       ssl_opts => {
+                         SSL_cert_file => '/home/dpavlin/certifikat_za_pristup_apiv2/certifikat.pfx',
+                         SSL_passwd_cb => sub { $cert_password },
+                       }
+                       );
+                       my $response = $ua->get ("https://isspapi.issp.srce.hr/Kartice/oibjmbag/$sid", Accept => "application/json");
+
+                       if ($response->is_success) {
+                               my $json_text = $response->decoded_content;
+                               my $h = decode_json $json_text;
+
+                               $json->{issp} = $h;
+
+                               @where = ();
+                               @execute = ();
+
+                               foreach my $f ( keys %$h ) {
+                                       my $name = uc($f);
+                                       push @where, "( code = '$name' and attribute = ? )";
+                                       push @execute, $h->{$f};
+                               }
+                               my $sql = generate_sql @where;
+                               warn "ISSP sql: $sql ",dump(@execute);
+                               my $sth = $dbh->prepare( $sql );
+                               $sth->execute( @execute );
+                               if ( $sth->rows == 1 ) {
+                                       $json->{borrower} = $sth->fetchrow_hashref;
+                                       warn "borrower = ",dump( $json->{borrower} );
+                                       my $sth2 = $dbh->prepare(qq{ insert into borrower_attributes (borrowernumber, code, attribute) values (?,?,?) });
+                                       $sth2->execute( $json->{borrower}->{borrowernumber}, 'RFID_SID', $sid);
+                                       warn "ISSP updated $json->{borrower}->{borrowernumber} $sid";
+                               } else {
+                                       warn "ISSP $sid not found in koha $json_text";
+                                       $json->{error} = "borrower not found";
+                               }
+                       } else {
+                               warn "ERROR ISSP ", $response->status_line;
+                               $json->{error} = "borrower not found";
+                       }
+               } else {
+                       $json->{error} = "borrower not found";
+               }
+       } else {
+               $json->{error} = "borrower not found";
+       }
+} elsif ( $sth->rows > 1 ) {
+       $json->{error} = "more than one borrower found";
+} else {
+       $json->{borrower} = $sth->fetchrow_hashref;
+}
+
+$json = encode_json( $json );
+
+print $json;