From a3d554c00d8c2b2a647a823ace0b87d2edecf16c Mon Sep 17 00:00:00 2001 From: Marion Durand Date: Thu, 9 Dec 2021 10:23:13 +0100 Subject: [PATCH] Bug 18618 : Many small refacto and improvement * change duration before refresh and improve the use of $temp variable * improve SQL request for extracting pairs fix condition create pair with old_issues use placeholders * improve SQL request for getting suggestion add the possibility to look in metadata for the id * execute the script only if mana is activated * optimize imbricate foreach loop * add a max number of simultaneous issues * remove unused option --- misc/cronjobs/mana_send_pairs.pl | 182 ++++++++++++++++++------------- opac/svc/mana/getSuggestion | 71 ++++++++++-- 2 files changed, 166 insertions(+), 87 deletions(-) diff --git a/misc/cronjobs/mana_send_pairs.pl b/misc/cronjobs/mana_send_pairs.pl index f54f7d0c9b..e821e00c37 100644 --- a/misc/cronjobs/mana_send_pairs.pl +++ b/misc/cronjobs/mana_send_pairs.pl @@ -12,96 +12,122 @@ use HTTP::Request; use Koha::SharedContent; my $help = 0; -my $man = 0; -my $verbose = 0; -my $date=DateTime->now()->strftime("%F"); - -my $securitytoken; -my $MANA_IP; GetOptions( 'help|?' => \$help, - 'man' => \$man, - 'v' => \$verbose, - 'date|d:s' => \$date, - 'security_token|s:s' => \$securitytoken, - 'mana_ip|i:s' => \$MANA_IP ); -$securitytoken = C4::Context->preference('ManaToken'); -my $dbh = C4::Context->dbh; -my $query = q{ -SELECT - ExtractValue(biblio_metadata1.metadata, '//datafield[@tag="010"]/subfield[@code="a"]') as isbn1, - ExtractValue(biblio_metadata2.metadata, '//datafield[@tag="010"]/subfield[@code="a"]') as isbn2, - ExtractValue(biblio_metadata1.metadata, '//datafield[@tag="011"]/subfield[@code="a"]') as issn1, - ExtractValue(biblio_metadata2.metadata, '//datafield[@tag="011"]/subfield[@code="a"]') as issn2, - ExtractValue(biblio_metadata1.metadata, '//datafield[@tag="013"]/subfield[@code="a"]') as ismn1, - ExtractValue(biblio_metadata2.metadata, '//datafield[@tag="013"]/subfield[@code="a"]') as ismn2, - ExtractValue(biblio_metadata1.metadata, '//datafield[@tag="015"]/subfield[@code="a"]') as isrn1, - ExtractValue(biblio_metadata2.metadata, '//datafield[@tag="015"]/subfield[@code="a"]') as isrn2, - ExtractValue(biblio_metadata1.metadata, '//datafield[@tag="016"]/subfield[@code="a"]') as isrc1, - ExtractValue(biblio_metadata2.metadata, '//datafield[@tag="016"]/subfield[@code="a"]') as isrc2, - ExtractValue(biblio_metadata1.metadata, '//datafield[@tag="072"]/subfield[@code="a"]') as upc1, - ExtractValue(biblio_metadata2.metadata, '//datafield[@tag="072"]/subfield[@code="a"]') as upc2, - ExtractValue(biblio_metadata1.metadata, '//datafield[@tag="073"]/subfield[@code="a"]') as ean1, - ExtractValue(biblio_metadata2.metadata, '//datafield[@tag="073"]/subfield[@code="a"]') as ean2 -FROM - issues AS issues1 -LEFT JOIN - issues AS issues2 ON issues1.borrowernumber=issues2.borrowernumber -LEFT JOIN - items AS items1 ON issues1.itemnumber=items1.itemnumber -LEFT JOIN - biblio_metadata AS biblio_metadata1 ON items1.biblionumber=biblio_metadata1.biblionumber -LEFT JOIN - items AS items2 ON issues2.itemnumber=items2.itemnumber -LEFT JOIN - biblio_metadata AS biblio_metadata2 ON items2.biblionumber=biblio_metadata2.biblionumber -WHERE - issues1.issuedate LIKE '}.$date. q{%' AND biblio_metadata1.biblionumber>biblio_metadata2.biblionumber AND biblio_metadata1.format='marcxml' AND biblio_metadata2.format='marcxml' -ORDER BY - issues1.borrowernumber,biblio_metadata1.biblionumber +if ($help){ + print "This script is used to send reading pairs to Mana KB.\nReading pairs are automatically extracted from Koha database.\n"; + exit; }; -my $sth = $dbh->prepare( $query ); -$sth->execute; - -my $row; -my @reading_pairs; - -#for each reading pair, processes the id pairs -while ( $row = $sth->fetchrow_hashref ){ -$row->{isbn1} = C4::Koha::NormalizeISBN({ isbn => $row->{isbn1}, format => 'ISBN-13', strip_hyphens => 1 }); -$row->{isbn2} = C4::Koha::NormalizeISBN({ isbn => $row->{isbn2}, format => 'ISBN-13', strip_hyphens => 1 }); - my $currentpair; - foreach my $key1 ( qw ( isbn issn ismn isrn ean upc isrc ) ){ - foreach my $key2 ( qw ( isbn issn ismn isrn ean upc isrc ) ){ - if ( $row->{ $key1."1" } and $row->{ $key2."2" } ){ - $currentpair->{ idtype1 } = $key1; - $currentpair->{ documentid1 } = $row->{ $key1."1" }; - $currentpair->{ idtype2 } = $key2; - $currentpair->{ documentid2 } = $row->{ $key2."2" }; - push @reading_pairs, $currentpair; +#if mana is activated +if (C4::Context->preference("Mana") == 1){ + #choose the field to select depending on marc flavour + my $date=DateTime->now()->strftime("%F")."%"; + + #define the max number of simultaneous issues for a single borrower + #if a sigle borrower have more issues than this, no pairs will be created for this borrower + #It is used to exlude huge borrower such as libraries or instution (because the issues won't have any connection) + my $current_issues_max = 50; + + my $dbh = C4::Context->dbh; + my $query_part1 = " + SELECT + issues1.borrowernumber as borrowernumber, + biblio_metadata1.biblionumber as biblionumber1, + biblio_metadata2.biblionumber as biblionumber2, + ExtractValue(biblio_metadata1.metadata, '//datafield[\@tag=\"010\"]/subfield[\@code=\"a\"]') as isbn1, + ExtractValue(biblio_metadata2.metadata, '//datafield[\@tag=\"010\"]/subfield[\@code=\"a\"]') as isbn2, + ExtractValue(biblio_metadata1.metadata, '//datafield[\@tag=\"011\"]/subfield[\@code=\"a\"]') as issn1, + ExtractValue(biblio_metadata2.metadata, '//datafield[\@tag=\"011\"]/subfield[\@code=\"a\"]') as issn2, + ExtractValue(biblio_metadata1.metadata, '//datafield[\@tag=\"013\"]/subfield[\@code=\"a\"]') as ismn1, + ExtractValue(biblio_metadata2.metadata, '//datafield[\@tag=\"013\"]/subfield[\@code=\"a\"]') as ismn2, + ExtractValue(biblio_metadata1.metadata, '//datafield[\@tag=\"015\"]/subfield[\@code=\"a\"]') as isrn1, + ExtractValue(biblio_metadata2.metadata, '//datafield[\@tag=\"015\"]/subfield[\@code=\"a\"]') as isrn2, + ExtractValue(biblio_metadata1.metadata, '//datafield[\@tag=\"016\"]/subfield[\@code=\"a\"]') as isrc1, + ExtractValue(biblio_metadata2.metadata, '//datafield[\@tag=\"016\"]/subfield[\@code=\"a\"]') as isrc2, + ExtractValue(biblio_metadata1.metadata, '//datafield[\@tag=\"072\"]/subfield[\@code=\"a\"]') as upc1, + ExtractValue(biblio_metadata2.metadata, '//datafield[\@tag=\"072\"]/subfield[\@code=\"a\"]') as upc2, + ExtractValue(biblio_metadata1.metadata, '//datafield[\@tag=\"073\"]/subfield[\@code=\"a\"]') as ean1, + ExtractValue(biblio_metadata2.metadata, '//datafield[\@tag=\"073\"]/subfield[\@code=\"a\"]') as ean2 + FROM + issues AS issues1 + JOIN + (SELECT borrowernumber FROM issues GROUP BY borrowernumber HAVING COUNT(*) < $current_issues_max) AS number ON number.borrowernumber=issues1.borrowernumber + JOIN + issues AS issues2 ON issues1.borrowernumber=issues2.borrowernumber + JOIN + items AS items1 ON issues1.itemnumber=items1.itemnumber + JOIN + biblio_metadata AS biblio_metadata1 ON items1.biblionumber=biblio_metadata1.biblionumber + JOIN + items AS items2 ON issues2.itemnumber=items2.itemnumber + JOIN + biblio_metadata AS biblio_metadata2 ON items2.biblionumber=biblio_metadata2.biblionumber + WHERE + issues1.issuedate LIKE ? + AND items1.biblionumber != items2.biblionumber + AND (DATE(issues1.issuedate) != DATE(issues2.issuedate) OR items1.biblionumber < items2.biblionumber) + AND biblio_metadata1.format='marcxml' + AND biblio_metadata2.format='marcxml' + "; + my $query_part2 = $query_part1; + $query_part2 =~ s/issues AS issues2/old_issues AS issues2/; + + # query_part1 match issues of the day with current issues + # query-part2 match issues of the day with old issues + my $query = $query_part1."UNION ALL".$query_part2; + + my $sth = $dbh->prepare( $query ); + $sth->execute($date, $date); + + my $row; + my @reading_pairs; + + #for each reading pair, processes the id pairs + while ( $row = $sth->fetchrow_hashref ){ + #if necessary, normalize the ids + $row->{isbn1} = C4::Koha::NormalizeISBN({ isbns => $row->{isbn1}, format => 'ISBN-13', strip_hyphens => 1 }) if $row->{isbn1}; + $row->{isbn2} = C4::Koha::NormalizeISBN({ isbns => $row->{isbn2}, format => 'ISBN-13', strip_hyphens => 1 }) if $row->{isbn2}; + + # try to make pairs between every id possible + foreach my $key1 ( qw ( isbn issn ismn isrn ean upc isrc ) ){ + if ( $row->{ $key1."1" }){ + foreach my $key2 ( qw ( isbn issn ismn isrn ean upc isrc ) ){ + # if both selected ids exists + if ( $row->{ $key2."2" } ){ + # make pairs between every ids of the given type + my $currentpair; + $currentpair->{ idtype1 } = $key1; + $currentpair->{ documentid1 } = $row->{$key1."1"}; + $currentpair->{ idtype2 } = $key2; + $currentpair->{ documentid2 } = $row->{$key2."2"}; + push @reading_pairs, $currentpair; + } + } } } } -} -my $content; + my $content; -#informations for mana -$MANA_IP=C4::Context->config("mana_config"); -$content->{ securitytoken } = $securitytoken; -$content->{ reading_pairs } = \@reading_pairs; + #informations for mana + my $MANA_IP=C4::Context->config("mana_config"); -my $url = "$MANA_IP/bulk/reading_pair.json"; -my $request = HTTP::Request->new( POST => $url ); + my $securitytoken = C4::Context->preference('ManaToken'); + $content->{ securitytoken } = $securitytoken; + $content->{ reading_pairs } = \@reading_pairs; -my $json = to_json( $content, { utf8 => 1 } ); -#print $json; + my $url = "$MANA_IP/bulk/reading_pair.json"; + my $request = HTTP::Request->new( POST => $url ); -#send to mana -$request->content($json); -my $result = Koha::SharedContent::process_request( $request ); -print Data::Dumper::Dumper( $result ); + my $json = to_json( $content, { utf8 => 1 } ); + #print $json; + + #send to mana + $request->content($json); + my $result = Koha::SharedContent::process_request( $request ); + #print Data::Dumper::Dumper( $result ); +} diff --git a/opac/svc/mana/getSuggestion b/opac/svc/mana/getSuggestion index 1ef8172197..d11bc5bd18 100755 --- a/opac/svc/mana/getSuggestion +++ b/opac/svc/mana/getSuggestion @@ -37,7 +37,7 @@ use DateTime; use DateTime::Format::MySQL; use DateTime::Duration; -use constant DURATION_BEFORE_REFRESH => DateTime::Duration->new( months => 3 ); +use constant DURATION_BEFORE_REFRESH => DateTime::Duration->new( weeks => 1 ); use C4::Output qw( output_with_http_headers ); my $input = CGI->new; binmode STDOUT, ":encoding(UTF-8)"; @@ -60,17 +60,17 @@ my $local_suggestions; my $now; my $timestamp; -my $temp; +my $duration; #check it doesn't already have the informations localy stored eval { $local_suggestions = Koha::Reading_suggestions->find( $biblionumber )->unblessed(); $now = DateTime->now(); $timestamp = DateTime::Format::MySQL->parse_timestamp( $local_suggestions->{timestamp} ); - $temp = $now - $timestamp; + $duration = $now - $timestamp; }; my @biblios; my @biblios_blessed; -if ( $local_suggestions and DateTime::Duration->compare( ($now - $timestamp), DURATION_BEFORE_REFRESH ) == -1 ){ +if ( $local_suggestions and DateTime::Duration->compare( $duration, DURATION_BEFORE_REFRESH ) == -1 ){ delete $local_suggestions->{timestamp}; delete $local_suggestions->{biblionumber}; foreach my $key ( sort keys %{ $local_suggestions }){ @@ -84,20 +84,73 @@ else{ my $idtype; my $documentid; my $notnormalizeddocumentid; - if ( $biblioitem->isbn ){ + my $found = 0; + + if ( $biblioitem && $biblioitem->isbn ){ $idtype= "isbn"; $documentid= C4::Koha::NormalizeISBN({ isbn => $biblioitem->isbn, format => 'ISBN-13', strip_hyphens => 1 }); + if ($documentids) { $found = 1; }; } - elsif ( $biblioitem->issn ){ + elsif ( $biblioitem && $biblioitem->issn ){ $idtype= "issn"; $documentid= $biblioitem->issn; + if ($documentids) { $found = 1; }; } - elsif ( $biblioitem->ean ){ + elsif ( $biblioitem && $biblioitem->ean ){ $idtype= "ean"; $documentid= $biblioitem->ean; + if ($documentids) { $found = 1; }; } - else{ - die "error: no propper identifier"; + if ($found == 0){ + my $dbh = C4::Context->dbh; + my $query = " + SELECT + ExtractValue(metadata, '//datafield[\@tag=\"010\"]/subfield[\@code=\"a\"]') as isbn, + ExtractValue(metadata, '//datafield[\@tag=\"011\"]/subfield[\@code=\"a\"]') as issn, + ExtractValue(metadata, '//datafield[\@tag=\"013\"]/subfield[\@code=\"a\"]') as ismn, + ExtractValue(metadata, '//datafield[\@tag=\"015\"]/subfield[\@code=\"a\"]') as isrn, + ExtractValue(metadata, '//datafield[\@tag=\"016\"]/subfield[\@code=\"a\"]') as isrc, + ExtractValue(metadata, '//datafield[\@tag=\"072\"]/subfield[\@code=\"a\"]') as upc, + ExtractValue(metadata, '//datafield[\@tag=\"073\"]/subfield[\@code=\"a\"]') as ean + FROM biblio_metadata + WHERE biblionumber = ? + AND format=\"marcxml\" + "; + my $sth = $dbh->prepare( $query ); + $sth->execute($biblionumber); + my $row = $sth->fetchrow_hashref; + + if ($row->{isbn}){ + $idtype = "isbn"; + $documentid = C4::Koha::NormalizeISBN({ isbn => $row->{isbn}, format => 'ISBN-13', strip_hyphens => 1 }); + } + elsif ($row->{issn}){ + $idtype = "issn"; + $documentid = $row->{issn}; + } + elsif ($row->{ismn}){ + $idtype = "ismn"; + $documentid = $row->{ismn}; + } + elsif ($row->{isrn}){ + $idtype = "isrn"; + $documentid = $row->{isrn}; + } + elsif ($row->{isrc}){ + $idtype = "isrc"; + $documentid = $row->{isrc}; + } + elsif ($row->{upc}){ + $idtype = "upc"; + $documentid = $row->{upc}; + } + elsif ($row->{ean}){ + $idtype = "ean"; + $documentid = $row->{ean}; + } + else{ + die "error: no propper identifier"; + } } $notnormalizeddocumentid = $documentid; my $offset = 1; -- 2.17.1