From a4f140da55f861a003367520a399261492d512cd Mon Sep 17 00:00:00 2001 From: Matthias Meusburger Date: Mon, 16 Jan 2012 14:47:26 +0100 Subject: [PATCH] Bug 5337: EAN management : Adds ean for various searches - in various acquisition pages and serials home - in database : biblioitems.ean - adds ean and its mapping in default english bibliographic framework - adds ean mapping in default french bibliographic framework - ean search is not enabled for MARC21 The required mapping between the ean marc field and the biblioitems.ean database field will be automatically added on an existing unimarc installation. However, if you already have records with ean, you will have to run misc/batchRebuildBiblioTables.pl to populate biblioitems.ean --- C4/Acquisition.pm | 26 +++++++++++++------- C4/Biblio.pm | 9 ++++--- C4/Serials.pm | 20 ++++++++++++--- acqui/addorder.pl | 1 + acqui/histsearch.pl | 6 ++++- acqui/neworderempty.pl | 3 +++ acqui/parcel.pl | 10 +++++--- catalogue/detail.pl | 2 +- .../mysql/atomicupdate/02.00.060-adding-ean.pl | 10 ++++++++ .../mandatory/unimarc_framework_DEFAULT.sql | 2 ++ .../Obligatoire/framework_DEFAULT.sql | 4 +-- installer/data/mysql/kohastructure.sql | 2 ++ installer/data/mysql/updatedatabase.pl | 15 +++++++++++ .../prog/en/includes/serials-search.inc | 13 ++++++++-- .../prog/en/modules/acqui/histsearch.tt | 3 +++ .../prog/en/modules/acqui/neworderempty.tt | 11 +++++++++ .../intranet-tmpl/prog/en/modules/acqui/parcel.tt | 11 +++++++-- opac/opac-ISBDdetail.pl | 2 +- opac/opac-detail.pl | 6 ++--- serials/checkexpiration.pl | 1 + serials/claims.pl | 1 + serials/routing-preview.pl | 1 + serials/routing.pl | 2 ++ serials/serial-issues.pl | 1 + serials/serials-collection.pl | 1 + serials/serials-edit.pl | 2 ++ serials/serials-home.pl | 19 ++++++++------ serials/serials-recieve.pl | 1 + serials/subscription-add.pl | 2 ++ serials/subscription-detail.pl | 1 + serials/viewalerts.pl | 4 ++- 31 files changed, 152 insertions(+), 40 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/02.00.060-adding-ean.pl diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 4518d25..55b7a99 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -1264,7 +1264,7 @@ C<@results> is an array of references-to-hash with the following keys: sub SearchOrder { #### -------- SearchOrder------------------------------- - my ($ordernumber, $search, $supplierid, $basket) = @_; + my ( $ordernumber, $search, $ean, $supplierid, $basket ) = @_; my $dbh = C4::Context->dbh; my @args = (); @@ -1284,7 +1284,11 @@ sub SearchOrder { $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)"; push @args, ("%$search%","%$search%","%$search%"); } - if($supplierid){ + if ($ean) { + $query .= " AND biblioitems.ean = ?"; + push @args, $ean; + } + if ($supplierid) { $query .= "AND aqbasket.booksellerid = ?"; push @args, $supplierid; } @@ -1664,12 +1668,12 @@ sub GetHistory { my $title = $params{title}; my $author = $params{author}; my $isbn = $params{isbn}; + my $ean = $params{ean}; my $name = $params{name}; my $from_placed_on = $params{from_placed_on}; my $to_placed_on = $params{to_placed_on}; my $basket = $params{basket}; my $booksellerinvoicenumber = $params{booksellerinvoicenumber}; - my @order_loop; my $total_qty = 0; my $total_qtyreceived = 0; @@ -1681,12 +1685,13 @@ sub GetHistory { biblio.title, biblio.author, biblioitems.isbn, + biblioitems.ean, aqorders.basketno, - aqbasket.basketname, - aqbasket.basketgroupid, - aqbasketgroups.name as groupname, + aqbasket.basketname, + aqbasket.basketgroupid, + aqbasketgroups.name as groupname, aqbooksellers.name, - aqbasket.creationdate, + aqbasket.creationdate, aqorders.datereceived, aqorders.quantity, aqorders.quantityreceived, @@ -1697,7 +1702,7 @@ sub GetHistory { aqorders.biblionumber FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno - LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id + LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber"; @@ -1724,7 +1729,10 @@ sub GetHistory { $query .= " AND biblioitems.isbn LIKE ? "; push @query_params, "%$isbn%"; } - + if ( defined $ean and $ean ) { + $query .= " AND biblioitems.ean = ? "; + push @query_params, "$ean"; + } if ( $name ) { $query .= " AND aqbooksellers.name LIKE ? "; push @query_params, "%$name%"; diff --git a/C4/Biblio.pm b/C4/Biblio.pm index 5508cbe..7584716 100644 --- a/C4/Biblio.pm +++ b/C4/Biblio.pm @@ -3271,7 +3271,8 @@ sub _koha_modify_biblioitem_nonmarc { cn_item = ?, cn_suffix = ?, cn_sort = ?, - totalissues = ? + totalissues = ?, + ean = ? where biblioitemnumber = ? "; my $sth = $dbh->prepare($query); @@ -3283,6 +3284,7 @@ sub _koha_modify_biblioitem_nonmarc { $biblioitem->{'pages'}, $biblioitem->{'bnotes'}, $biblioitem->{'size'}, $biblioitem->{'place'}, $biblioitem->{'lccn'}, $biblioitem->{'url'}, $biblioitem->{'biblioitems.cn_source'}, $biblioitem->{'cn_class'}, $biblioitem->{'cn_item'}, $biblioitem->{'cn_suffix'}, $cn_sort, $biblioitem->{'totalissues'}, + $biblioitem->{'ean'}, $biblioitem->{'biblioitemnumber'} ); if ( $dbh->errstr ) { @@ -3334,7 +3336,8 @@ sub _koha_add_biblioitem { cn_item = ?, cn_suffix = ?, cn_sort = ?, - totalissues = ? + totalissues = ?, + ean = ? "; my $sth = $dbh->prepare($query); $sth->execute( @@ -3345,7 +3348,7 @@ sub _koha_add_biblioitem { $biblioitem->{'pages'}, $biblioitem->{'bnotes'}, $biblioitem->{'size'}, $biblioitem->{'place'}, $biblioitem->{'lccn'}, $biblioitem->{'marc'}, $biblioitem->{'url'}, $biblioitem->{'biblioitems.cn_source'}, $biblioitem->{'cn_class'}, $biblioitem->{'cn_item'}, $biblioitem->{'cn_suffix'}, $cn_sort, - $biblioitem->{'totalissues'} + $biblioitem->{'totalissues'}, $biblioitem->{'ean'} ); my $bibitemnum = $dbh->{'mysql_insertid'}; diff --git a/C4/Serials.pm b/C4/Serials.pm index 20ad706..177a5ee 100644 --- a/C4/Serials.pm +++ b/C4/Serials.pm @@ -545,15 +545,15 @@ sub GetFullSubscriptionsFromBiblionumber { =head2 GetSubscriptions -@results = GetSubscriptions($title,$ISSN,$biblionumber); -this function gets all subscriptions which have title like $title,ISSN like $ISSN and biblionumber like $biblionumber. +@results = GetSubscriptions($title,$ISSN,$ean,$biblionumber); +this function gets all subscriptions which have title like $title,ISSN like $ISSN,EAN like $ean and biblionumber like $biblionumber. return: a table of hashref. Each hash containt the subscription. =cut sub GetSubscriptions { - my ( $string, $issn, $biblionumber ) = @_; + my ( $string, $issn, $ean, $biblionumber ) = @_; #return unless $title or $ISSN or $biblionumber; my $dbh = C4::Context->dbh; @@ -597,6 +597,20 @@ sub GetSubscriptions { } $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))"; } + if ($ean) { + my @sqlstrings; + my @strings_to_search; + @strings_to_search = map { "$_" } split( / /, $ean ); + foreach my $index qw(biblioitems.ean) { + push @bind_params, @strings_to_search; + my $tmpstring = "OR $index = ? " x scalar(@strings_to_search); + $debug && warn "$tmpstring"; + $tmpstring =~ s/^OR //; + push @sqlstrings, $tmpstring; + } + $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "(" . join( ") OR (", @sqlstrings ) . ")"; + } + $sql .= "$sqlwhere ORDER BY title"; $debug and warn "GetSubscriptions query: $sql params : ", join( " ", @bind_params ); $sth = $dbh->prepare($sql); diff --git a/acqui/addorder.pl b/acqui/addorder.pl index 279084b..01febba 100755 --- a/acqui/addorder.pl +++ b/acqui/addorder.pl @@ -202,6 +202,7 @@ if ( $orderinfo->{quantity} ne '0' ) { "biblio.author" => $$orderinfo{author} ? $$orderinfo{author} : "", "biblio.seriestitle" => $$orderinfo{series} ? $$orderinfo{series} : "", "biblioitems.isbn" => $$orderinfo{isbn} ? $$orderinfo{isbn} : "", + "biblioitems.ean" => $$orderinfo{ean} ? $$orderinfo{ean} : "", "biblioitems.publishercode" => $$orderinfo{publishercode} ? $$orderinfo{publishercode} : "", "biblioitems.publicationyear" => $$orderinfo{publicationyear} ? $$orderinfo{publicationyear}: "", "biblio.copyrightdate" => $$orderinfo{publicationyear} ? $$orderinfo{publicationyear}: "", diff --git a/acqui/histsearch.pl b/acqui/histsearch.pl index 44921be..e55be5f 100755 --- a/acqui/histsearch.pl +++ b/acqui/histsearch.pl @@ -63,6 +63,7 @@ my $title = $input->param( 'title'); my $author = $input->param('author'); my $isbn = $input->param('isbn'); my $name = $input->param( 'name' ); +my $ean = $input->param('ean'); my $basket = $input->param( 'basket' ); my $booksellerinvoicenumber = $input->param( 'booksellerinvoicenumber' ); my $do_search = $input->param('do_search') || 0; @@ -101,6 +102,7 @@ if ($do_search) { title => $title, author => $author, isbn => $isbn, + ean => $ean, name => $name, from_placed_on => $from_iso, to_placed_on => $to_iso, @@ -120,7 +122,8 @@ $template->param( numresults => $order_loop ? scalar(@$order_loop) : undef, title => $title, author => $author, - isbn => $isbn, + isbn => $isbn, + ean => $ean, name => $name, basket => $basket, booksellerinvoicenumber => $booksellerinvoicenumber, @@ -129,6 +132,7 @@ $template->param( DHTMLcalendar_dateformat=> C4::Dates->DHTMLcalendar(), dateformat => C4::Dates->new()->format(), debug => $debug || $input->param('debug') || 0, + uc(C4::Context->preference("marcflavour")) => 1 ); output_html_with_http_headers $input, $cookie, $template->output; diff --git a/acqui/neworderempty.pl b/acqui/neworderempty.pl index c99863a..08693d1 100755 --- a/acqui/neworderempty.pl +++ b/acqui/neworderempty.pl @@ -372,6 +372,7 @@ $template->param( editionstatement => $data->{'editionstatement'}, budget_loop => $budget_loop, isbn => $data->{'isbn'}, + ean => $data->{'ean'}, seriestitle => $data->{'seriestitle'}, itemtypeloop => \@itemtypes, quantity => $data->{'quantity'}, @@ -390,6 +391,7 @@ $template->param( # CHECKME: gst-stuff needs verifing, mason. gstrate => $bookseller->{'gstrate'} // C4::Context->preference("gist") // 0, gstreg => $bookseller->{'gstreg'}, + (uc(C4::Context->preference("marcflavour"))) => 1 ); output_html_with_http_headers $input, $cookie, $template->output; @@ -521,6 +523,7 @@ sub Load_Duplicate { booksellerid => $basket->{'booksellerid'}, breedingid => $params->{'breedingid'}, duplicatetitle => $duplicatetitle, + (uc(C4::Context->preference("marcflavour"))) => 1 ); output_html_with_http_headers $input, $cookie, $template->output; diff --git a/acqui/parcel.pl b/acqui/parcel.pl index 5d0d164..bf14ff0 100755 --- a/acqui/parcel.pl +++ b/acqui/parcel.pl @@ -101,14 +101,15 @@ if($input->param('format') eq "json"){ }); my @datas; - my $search = $input->param('search') || ''; + my $search = $input->param('search') || ''; + my $ean = $input->param('ean') || ''; my $supplier = $input->param('booksellerid') || ''; my $basketno = $input->param('basketno') || ''; my $orderno = $input->param('orderno') || ''; - my $orders = SearchOrder($orderno, $search, $supplier, $basketno); - foreach my $order (@$orders){ - if($order->{quantityreceived} < $order->{quantity}){ + my $orders = SearchOrder($orderno, $search, $ean, $supplier, $basketno); + foreach my $order (@$orders) { + if ( $order->{quantityreceived} < $order->{quantity} ) { my $data = {}; $data->{basketno} = $order->{basketno}; @@ -322,6 +323,7 @@ $template->param( totalPqtyrcvd => $totalPqtyrcvd, totalPecost => sprintf("%.2f", $totalPecost), resultsperpage => $resultsperpage, + (uc(C4::Context->preference("marcflavour"))) => 1 ); output_html_with_http_headers $input, $cookie, $template->output; diff --git a/catalogue/detail.pl b/catalogue/detail.pl index de355fa..be4fd9a 100755 --- a/catalogue/detail.pl +++ b/catalogue/detail.pl @@ -142,7 +142,7 @@ my ( $holdcount, $holds ) = GetReservesFromBiblionumber($biblionumber,1); #coping with subscriptions my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber); -my @subscriptions = GetSubscriptions( $dat->{title}, $dat->{issn}, $biblionumber ); +my @subscriptions = GetSubscriptions( $dat->{title}, $dat->{issn}, undef, $biblionumber ); my @subs; foreach my $subscription (@subscriptions) { diff --git a/installer/data/mysql/atomicupdate/02.00.060-adding-ean.pl b/installer/data/mysql/atomicupdate/02.00.060-adding-ean.pl new file mode 100644 index 0000000..7e00da9 --- /dev/null +++ b/installer/data/mysql/atomicupdate/02.00.060-adding-ean.pl @@ -0,0 +1,10 @@ +#!/usr/bin/perl +use C4::Context; +my $dbh= C4::Context->dbh; +$dbh->do("ALTER TABLE `biblioitems` ADD `ean` VARCHAR( 13 ) NULL AFTER issn"); +$dbh->do("CREATE INDEX `ean` ON biblioitems (`ean`) "); +$dbh->do("ALTER TABLE `deletedbiblioitems` ADD `ean` VARCHAR( 13 ) NULL AFTER issn"); +$dbh->do("CREATE INDEX `ean` ON deletedbiblioitems (`ean`) "); +if (C4::Context->preference("marcflavour") eq 'UNIMARC') { + $dbh->do("UPDATE marc_subfield_structure SET kohafield='biblioitems.ean' WHERE tagfield='073' and tagsubfield='a'"); +} diff --git a/installer/data/mysql/en/marcflavour/unimarc/mandatory/unimarc_framework_DEFAULT.sql b/installer/data/mysql/en/marcflavour/unimarc/mandatory/unimarc_framework_DEFAULT.sql index a98267b..bce3862 100644 --- a/installer/data/mysql/en/marcflavour/unimarc/mandatory/unimarc_framework_DEFAULT.sql +++ b/installer/data/mysql/en/marcflavour/unimarc/mandatory/unimarc_framework_DEFAULT.sql @@ -166,6 +166,7 @@ INSERT INTO `marc_tag_structure` (`tagfield`, `liblibrarian`, `libopac`, `repeat ('022', 'Government Publication Number', 'Government Publication Number', 0, 0, '', ''), ('040', 'CODEN (Serials)', 'CODEN (Serials)', 0, 0, '', ''), ('071', 'Publisher\'s Number (Sound Recordings and Music)', '', 0, 0, '', ''), + ('073', 'EAN', 'EAN', 1, 0, '', ''), ('100', 'General Processing Data', 'General Processing Data', 1, 0, '', ''), ('101', 'Language of the Item', 'Language of the Item', 1, 0, '', ''), ('102', 'Country of Publication or Production', 'Country of Publication or Production', 0, 0, '', ''), @@ -356,6 +357,7 @@ INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian` ('040', 'z', 'Erroneous CODEN', 'Erroneous CODEN', 1, 0, '', 0, '', '', '', 0, -5, '', '', '', NULL), ('071', 'a', 'Publisher\'s Number (Sound Recordings and Music)', 'Publisher\'s Number (Sound Recordings and Music)', 0, 0, '', 0, '', '', '', 0, -5, '', '', '', NULL), ('071', 'b', 'Source', 'Source', 0, 0, '', 0, '', '', '', 0, -5, '', '', '', NULL), + ('073', 'a', 'EAN', 'EAN', 0, 0, 'biblioitems.ean', 0, '', '', '', 0, 0, '', '', '', NULL), ('100', 'a', 'General Processing Data', 'General Processing Data', 0, 0, '', 1, '', '', 'unimarc_field_100.pl', 0, 0, '', '', '', NULL), ('101', 'a', 'Language of the Text, Soundtrack, etc.', 'Language of the Text, Soundtrack, etc.', 1, 0, '', 1, 'LAN', '', '', 0, 0, '', '', '', NULL), ('101', 'b', 'Language of Intermediate Text when Item is Not Translated from Original', 'Language of Intermediate Text when Item is Not Translated from Original', 0, 0, '', 1, '', '', '', 0, -5, '', '', '', NULL), diff --git a/installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/framework_DEFAULT.sql b/installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/framework_DEFAULT.sql index 3e12a44..9b6cc7a 100644 --- a/installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/framework_DEFAULT.sql +++ b/installer/data/mysql/fr-FR/marcflavour/unimarc_complet/Obligatoire/framework_DEFAULT.sql @@ -318,7 +318,7 @@ INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian` ('072', 'c', 'Numéro additionnels suivant le code normalisé', '', 0, 0, '', 0, '', '', '', 0, 0, '', NULL, '', ''), ('072', 'd', 'Mention de disponibilité et/ou de prix', '', 0, 0, '', 0, '', '', '', 0, 0, '', NULL, '', ''), ('072', 'z', 'Numéro ou code erroné', '', 0, 0, '', 0, '', '', '', 0, 0, '', NULL, '', ''), -('073', 'a', 'Numéro', '', 0, 0, '', 0, '', '', '', NULL, 0, '', '', NULL, ''), +('073', 'a', 'Numéro', '', 0, 0, 'biblioitems.ean', 0, '', '', '', NULL, 0, '', '', NULL, ''), ('073', 'b', 'Qualificatif', '', 0, 0, '', 0, '', '', '', NULL, 0, '', '', NULL, ''), ('073', 'c', 'Numéros additionnels', '', 0, 0, '', 0, '', '', '', NULL, 0, '', '', NULL, ''), ('073', 'd', 'Prix et disponibilité', '', 0, 0, '', 0, '', '', '', NULL, 0, '', '', NULL, ''), @@ -2034,4 +2034,4 @@ INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian` ('995', 'u', 'note', '', 0, 0, 'items.itemnotes', 10, '', '', '', NULL, 0, '', NULL, NULL, ''); UPDATE `marc_subfield_structure` SET maxlength=24 WHERE tagfield='000'; -UPDATE `marc_subfield_structure` SET maxlength=36 WHERE tagfield='100'; \ No newline at end of file +UPDATE `marc_subfield_structure` SET maxlength=36 WHERE tagfield='100'; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 9a94e0f..983b787 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -154,6 +154,7 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c) `isbn` varchar(30) default NULL, -- ISBN (MARC21 020$a) `issn` varchar(9) default NULL, -- ISSN (MARC21 022$a) + `ean` varchar(13) default NULL, `publicationyear` text, `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b) `volumedate` date default NULL, @@ -625,6 +626,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c) `isbn` varchar(30) default NULL, -- ISBN (MARC21 020$a) `issn` varchar(9) default NULL, -- ISSN (MARC21 022$a) + `ean` varchar(13) default NULL, `publicationyear` text, `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b) `volumedate` date default NULL, diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 38aa70c..19600da 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5246,6 +5246,21 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } + + +$DBversion = "3.09.00.005"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do("ALTER TABLE `biblioitems` ADD `ean` VARCHAR( 13 ) NULL AFTER issn"); + $dbh->do("CREATE INDEX `ean` ON biblioitems (`ean`) "); + $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `ean` VARCHAR( 13 ) NULL AFTER issn"); + $dbh->do("CREATE INDEX `ean` ON deletedbiblioitems (`ean`) "); + if (C4::Context->preference("marcflavour") eq 'UNIMARC') { + $dbh->do("UPDATE marc_subfield_structure SET kohafield='biblioitems.ean' WHERE tagfield='073' and tagsubfield='a'"); + } + print "Upgrade to $DBversion done (Adding ean in biblioitems and deletedbiblioitems)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/serials-search.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/serials-search.inc index 546ab56..09a2999 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/serials-search.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/serials-search.inc @@ -3,8 +3,17 @@