From 33ab2526f326e3d38621b4ac4f468787a5377d64 Mon Sep 17 00:00:00 2001 From: tonnesen Date: Fri, 5 Oct 2001 20:13:20 +0000 Subject: [PATCH] Changed search interface to allow searches on Illustrator. This is apparently a common search in elementary school libraries. --- C4/Catalogue.pm | 258 +++++++++++++++++++++++++++++++++----------- C4/Search.pm | 181 ++++++++++++++++--------------- marc/marcschema.sql | 31 +++--- search.pl | 44 +++++--- 4 files changed, 338 insertions(+), 176 deletions(-) diff --git a/C4/Catalogue.pm b/C4/Catalogue.pm index 1bfc54959c..49db2c186b 100644 --- a/C4/Catalogue.pm +++ b/C4/Catalogue.pm @@ -175,6 +175,24 @@ sub updateBiblio { my $sth=$dbh->prepare("select * from biblio where biblionumber=$biblionumber"); $sth->execute; my $origbiblio=$sth->fetchrow_hashref; + $sth=$dbh->prepare("select subtitle from bibliosubtitle where biblionumber=$biblionumber"): + $sth->execute; + my ($subtitle)=$sth->fetchrow; + $origbiblio->{'subtitle'}=$subtitle; + $sth=$dbh->prepare("select author from additionalauthors where biblionumber=$biblionumber"); + $sth->execute; + my $origadditionalauthors; + while (my ($author) = $sth->fetchrow) { + push (@{$origbiblio->{'additionalauthors'}}, $author); + $origadditionalauthors->{$subject}=1; + } + $sth=$dbh->prepare("select subject from bibliosubjects where biblionumber=$biblionumber"); + $sth->execute; + my $origsubjects; + while (my ($subject) = $sth->fetchrow) { + push (@{$origbiblio->{'subjects'}}, $subject); + $origsubjects->{$subject}=1; + } # Obtain a list of MARC Record_ID's that are tied to this biblio @@ -187,80 +205,126 @@ sub updateBiblio { + my $Record_ID=''; if ($biblio->{'author'} ne $origbiblio->{'author'}) { my $q_author=$dbh->quote($biblio->{'author'}); - logchange('kohadb', 'biblio', 'author', $origbiblio->{'author'}, $biblio->{'author'}); + logchange('kohadb', 'change', 'biblio', 'author', $origbiblio->{'author'}, $biblio->{'author'}); my $sti=$dbh->prepare("update biblio set author=$q_author where biblionumber=$biblio->{'biblionumber'}"); $sti->execute; - logchange('marc', '100', 'a', $origbiblio->{'author'}, $biblio->{'author'}); - foreach (@marcrecords) { - changeSubfield($_, '100', 'a', $origbiblio->{'author'}, $biblio->{'author'}); + foreach $Record_ID (@marcrecords) { + logchange('marc', 'change', $Record_ID, '100', 'a', $origbiblio->{'author'}, $biblio->{'author'}); + changeSubfield($Record_ID, '100', 'a', $origbiblio->{'author'}, $biblio->{'author'}); } } if ($biblio->{'title'} ne $origbiblio->{'title'}) { my $q_title=$dbh->quote($biblio->{'title'}); - logchange('kohadb', 'biblio', 'title', $origbiblio->{'title'}, $biblio->{'title'}); + logchange('kohadb', 'change', 'biblio', 'title', $origbiblio->{'title'}, $biblio->{'title'}); my $sti=$dbh->prepare("update biblio set title=$q_title where biblionumber=$biblio->{'biblionumber'}"); $sti->execute; - logchange('marc', '245', 'a', $origbiblio->{'title'}, $biblio->{'title'}); - foreach (@marcrecords) { - changeSubfield($_, '245', 'a', $origbiblio->{'title'}, $biblio->{'title'}); + foreach $Record_ID (@marcrecords) { + logchange('marc', 'change', $Record_ID, '245', 'a', $origbiblio->{'title'}, $biblio->{'title'}); + changeSubfield($Record_ID, '245', 'a', $origbiblio->{'title'}, $biblio->{'title'}); + } + } + if ($biblio->{'subtitle'} ne $origbiblio->{'subtitle'}) { + my $q_subtitle=$dbh->quote($biblio->{'subtitle'}); + logchange('kohadb', 'change', 'biblio', 'subtitle', $origbiblio->{'subtitle'}, $biblio->{'subtitle'}); + my $sti=$dbh->prepare("update bibliosubtitle set subtitle=$q_subtitle where biblionumber=$biblio->{'biblionumber'}"); + $sti->execute; + foreach $Record_ID (@marcrecords) { + logchange('marc', 'change', $Record_ID, '245', 'b', $origbiblio->{'subtitle'}, $biblio->{'subtitle'}); + changeSubfield($Record_ID, '245', 'b', $origbiblio->{'subtitle'}, $biblio->{'subtitle'}); } } if ($biblio->{'unititle'} ne $origbiblio->{'unititle'}) { my $q_unititle=$dbh->quote($biblio->{'unititle'}); - logchange('kohadb', 'biblio', 'unititle', $origbiblio->{'unititle'}, $biblio->{'unititle'}); + logchange('kohadb', 'change', 'biblio', 'unititle', $origbiblio->{'unititle'}, $biblio->{'unititle'}); my $sti=$dbh->prepare("update biblio set unititle=$q_unititle where biblionumber=$biblio->{'biblionumber'}"); $sti->execute; } if ($biblio->{'notes'} ne $origbiblio->{'notes'}) { my $q_notes=$dbh->quote($biblio->{'notes'}); - logchange('kohadb', 'biblio', 'notes', $origbiblio->{'notes'}, $biblio->{'notes'}); + logchange('kohadb', 'change', 'biblio', 'notes', $origbiblio->{'notes'}, $biblio->{'notes'}); my $sti=$dbh->prepare("update biblio set notes=$q_notes where biblionumber=$biblio->{'biblionumber'}"); $sti->execute; - logchange('marc', '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'}); - foreach (@marcrecords) { - changeSubfield($_, '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'}); + foreach $Record_ID (@marcrecords) { + logchange('marc', 'change', $Record_ID, '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'}); + changeSubfield($Record_ID, '500', 'a', $origbiblio->{'notes'}, $biblio->{'notes'}); } } if ($biblio->{'serial'} ne $origbiblio->{'serial'}) { my $q_serial=$dbh->quote($biblio->{'serial'}); - logchange('kohadb', 'biblio', 'serial', $origbiblio->{'serial'}, $biblio->{'serial'}); + logchange('kohadb', 'change', 'biblio', 'serial', $origbiblio->{'serial'}, $biblio->{'serial'}); my $sti=$dbh->prepare("update biblio set serial=$q_serial where biblionumber=$biblio->{'biblionumber'}"); $sti->execute; } if ($biblio->{'seriestitle'} ne $origbiblio->{'seriestitle'}) { my $q_seriestitle=$dbh->quote($biblio->{'seriestitle'}); - logchange('kohadb', 'biblio', 'seriestitle', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'}); + logchange('kohadb', 'change', 'biblio', 'seriestitle', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'}); my $sti=$dbh->prepare("update biblio set seriestitle=$q_seriestitle where biblionumber=$biblio->{'biblionumber'}"); $sti->execute; - logchange('marc', '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'}); - foreach (@marcrecords) { - changeSubfield($_, '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'}); + foreach $Record_ID (@marcrecords) { + logchange('marc', 'change', $Record_ID, '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'}); + changeSubfield($Record_ID, '440', 'a', $origbiblio->{'seriestitle'}, $biblio->{'seriestitle'}); } } if ($biblio->{'copyrightdate'} ne $origbiblio->{'copyrightdate'}) { my $q_copyrightdate=$dbh->quote($biblio->{'copyrightdate'}); - logchange('kohadb', 'biblio', 'copyrightdate', $origbiblio->{'copyrightdate'}, $biblio->{'copyrightdate'}); + logchange('kohadb', 'change', 'biblio', 'copyrightdate', $origbiblio->{'copyrightdate'}, $biblio->{'copyrightdate'}); my $sti=$dbh->prepare("update biblio set copyrightdate=$q_copyrightdate where biblionumber=$biblio->{'biblionumber'}"); $sti->execute; - logchange('marc', '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}"); - foreach (@marcrecords) { - changeSubfield($_, '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}"); + foreach $Record_ID (@marcrecords) { + logchange('marc', 'change', $Record_ID, '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}"); + changeSubfield($Record_ID, '260', 'c', "c$origbiblio->{'notes'}", "c$biblio->{'notes'}"); } } + +# Check for subject heading changes + + my $newsubject=''; + foreach $newsubject (@{$biblio->{'subject'}}) { + $subjects->{$newsubject}=1; + if ($origsubjects->{$newsubject}) { + $subjects->{$newsubject}=2; + } else { + my $q_newsubject=$dbh->quote($newsubject); + my $sth=$dbh->prepare("insert into bibliosubjects (subject,biblionumber) values ($q_newsubject, $biblionumber)"); + $sth->execute; + logchange('kohadb', 'add', 'biblio', 'subject', $newsubject); + my $subfields; + $subfields->{1}->{'Subfield_Mark'}='a'; + $subfields->{1}->{'Subfield_Value'}=$newsubject; + my $tag='650'; + my $Record_ID; + foreach $Record_ID (@marcrecords) { + addTag($env, $Record_ID, $tag, ' ', ' ', $subfields); + logchange('marc', 'add', $Record_ID, '650', 'a', $newsubject); + } + } + } + foreach $origsubject (keys %$origsubjects) { + if ($subjects->{$origsubject} == 1) { + my $q_origsubject=$dbh->quote($origsubject); + logchange('kohadb', 'delete', 'biblio', '$biblionumber', 'subject', $origsubject); + my $sth=$dbh->prepare("delete from bibliosubjects where biblionumber=$biblionumber and subject=$q_origsubject"); + $sth->execute; + } + } + } sub logchange { # Subroutine to log changes to databases my $database=shift; if ($database eq 'kohadb') { + my $type=shift; my $section=shift; my $item=shift; my $original=shift; my $new=shift; print "KOHA: $section $item $original $new\n"; } elsif ($database eq 'marc') { + my $type=shift; my $tag=shift; my $mark=shift; my $subfield_ID=shift; @@ -596,114 +660,114 @@ sub updateBiblioItem { $sth->execute; my ($Record_ID) = $sth->fetchrow; if ($biblioitem->{'biblionumber'} ne $obi->{'biblionumber'}) { - logchange('kohadb', 'biblioitems', 'biblionumber', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'}); + logchange('kohadb', 'change', 'biblioitems', 'biblionumber', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'}); my $sth=$dbh->prepare("update biblioitems set biblionumber=$biblioitem->{'biblionumber'} where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '090', 'c', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'}); + logchange('marc', 'change', $Record_ID, '090', 'c', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'}); changeSubfield($Record_ID, '090', 'c', $obi->{'biblionumber'}, $biblioitem->{'biblionumber'}); } if ($biblioitem->{'volume'} ne $obi->{'volume'}) { - logchange('kohadb', 'biblioitems', 'volume', $obi->{'volume'}, $biblioitem->{'volume'}); + logchange('kohadb', 'change', 'biblioitems', 'volume', $obi->{'volume'}, $biblioitem->{'volume'}); my $q_volume=$dbh->quote($biblioitem->{'volume'}); my $sth=$dbh->prepare("update biblioitems set volume=$q_volume where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '440', 'v', $obi->{'volume'}, $biblioitem->{'volume'}); + logchange('marc', 'change', $Record_ID, '440', 'v', $obi->{'volume'}, $biblioitem->{'volume'}); changeSubfield($Record_ID, '440', 'v', $obi->{'volume'}, $biblioitem->{'volume'}); } if ($biblioitem->{'number'} ne $obi->{'number'}) { - logchange('kohadb', 'biblioitems', 'number', $obi->{'number'}, $biblioitem->{'number'}); + logchange('kohadb', 'change', 'biblioitems', 'number', $obi->{'number'}, $biblioitem->{'number'}); my $q_number=$dbh->quote($biblioitem->{'number'}); my $sth=$dbh->prepare("update biblioitems set number=$q_number where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '440', 'v', $obi->{'number'}, $biblioitem->{'number'}); + logchange('marc', 'change', $Record_ID, '440', 'v', $obi->{'number'}, $biblioitem->{'number'}); changeSubfield($Record_ID, '440', 'v', $obi->{'number'}, $biblioitem->{'number'}); } if ($biblioitem->{'itemtype'} ne $obi->{'itemtype'}) { - logchange('kohadb', 'biblioitems', 'itemtype', $obi->{'itemtype'}, $biblioitem->{'itemtype'}); + logchange('kohadb', 'change', 'biblioitems', 'itemtype', $obi->{'itemtype'}, $biblioitem->{'itemtype'}); my $q_itemtype=$dbh->quote($biblioitem->{'itemtype'}); my $sth=$dbh->prepare("update biblioitems set itemtype=$q_itemtype where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '090', 'a', $obi->{'itemtype'}, $biblioitem->{'itemtype'}); + logchange('marc', 'change', $Record_ID, '090', 'a', $obi->{'itemtype'}, $biblioitem->{'itemtype'}); changeSubfield($Record_ID, '090', 'a', $obi->{'itemtype'}, $biblioitem->{'itemtype'}); } if ($biblioitem->{'isbn'} ne $obi->{'isbn'}) { - logchange('kohadb', 'biblioitems', 'isbn', $obi->{'isbn'}, $biblioitem->{'isbn'}); + logchange('kohadb', 'change', 'biblioitems', 'isbn', $obi->{'isbn'}, $biblioitem->{'isbn'}); my $q_isbn=$dbh->quote($biblioitem->{'isbn'}); my $sth=$dbh->prepare("update biblioitems set isbn=$q_isbn where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '020', 'a', $obi->{'isbn'}, $biblioitem->{'isbn'}); + logchange('marc', 'change', $Record_ID, '020', 'a', $obi->{'isbn'}, $biblioitem->{'isbn'}); changeSubfield($Record_ID, '020', 'a', $obi->{'isbn'}, $biblioitem->{'isbn'}); } if ($biblioitem->{'issn'} ne $obi->{'issn'}) { - logchange('kohadb', 'biblioitems', 'issn', $obi->{'issn'}, $biblioitem->{'issn'}); + logchange('kohadb', 'change', 'biblioitems', 'issn', $obi->{'issn'}, $biblioitem->{'issn'}); my $q_issn=$dbh->quote($biblioitem->{'issn'}); my $sth=$dbh->prepare("update biblioitems set issn=$q_issn where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '022', 'a', $obi->{'issn'}, $biblioitem->{'issn'}); + logchange('marc', 'change', $Record_ID, '022', 'a', $obi->{'issn'}, $biblioitem->{'issn'}); changeSubfield($Record_ID, '022', 'a', $obi->{'issn'}, $biblioitem->{'issn'}); } if ($biblioitem->{'dewey'} ne $obi->{'dewey'}) { - logchange('kohadb', 'biblioitems', 'dewey', $obi->{'dewey'}, $biblioitem->{'dewey'}); + logchange('kohadb', 'change', 'biblioitems', 'dewey', $obi->{'dewey'}, $biblioitem->{'dewey'}); my $sth=$dbh->prepare("update biblioitems set dewey=$biblioitem->{'dewey'} where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '082', 'a', $obi->{'dewey'}, $biblioitem->{'dewey'}); + logchange('marc', 'change', $Record_ID, '082', 'a', $obi->{'dewey'}, $biblioitem->{'dewey'}); changeSubfield($Record_ID, '082', 'a', $obi->{'dewey'}, $biblioitem->{'dewey'}); } if ($biblioitem->{'subclass'} ne $obi->{'subclass'}) { - logchange('kohadb', 'biblioitems', 'subclass', $obi->{'subclass'}, $biblioitem->{'subclass'}); + logchange('kohadb', 'change', 'biblioitems', 'subclass', $obi->{'subclass'}, $biblioitem->{'subclass'}); my $q_subclass=$dbh->quote($biblioitem->{'subclass'}); my $sth=$dbh->prepare("update biblioitems set subclass=$q_subclass where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '090', 'b', $obi->{'subclass'}, $biblioitem->{'subclass'}); + logchange('marc', 'change', $Record_ID, '090', 'b', $obi->{'subclass'}, $biblioitem->{'subclass'}); changeSubfield($Record_ID, '090', 'b', $obi->{'subclass'}, $biblioitem->{'subclass'}); } if ($biblioitem->{'place'} ne $obi->{'place'}) { - logchange('kohadb', 'biblioitems', 'place', $obi->{'place'}, $biblioitem->{'place'}); + logchange('kohadb', 'change', 'biblioitems', 'place', $obi->{'place'}, $biblioitem->{'place'}); my $q_place=$dbh->quote($biblioitem->{'place'}); my $sth=$dbh->prepare("update biblioitems set place=$q_place where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '260', 'a', $obi->{'place'}, $biblioitem->{'place'}); + logchange('marc', 'change', $Record_ID, '260', 'a', $obi->{'place'}, $biblioitem->{'place'}); changeSubfield($Record_ID, '260', 'a', $obi->{'place'}, $biblioitem->{'place'}); } if ($biblioitem->{'publishercode'} ne $obi->{'publishercode'}) { - logchange('kohadb', 'biblioitems', 'publishercode', $obi->{'publishercode'}, $biblioitem->{'publishercode'}); + logchange('kohadb', 'change', 'biblioitems', 'publishercode', $obi->{'publishercode'}, $biblioitem->{'publishercode'}); my $q_publishercode=$dbh->quote($biblioitem->{'publishercode'}); my $sth=$dbh->prepare("update biblioitems set publishercode=$q_publishercode where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '260', 'b', $obi->{'publishercode'}, $biblioitem->{'publishercode'}); + logchange('marc', 'change', $Record_ID, '260', 'b', $obi->{'publishercode'}, $biblioitem->{'publishercode'}); changeSubfield($Record_ID, '260', 'b', $obi->{'publishercode'}, $biblioitem->{'publishercode'}); } if ($biblioitem->{'publicationyear'} ne $obi->{'publicationyear'}) { - logchange('kohadb', 'biblioitems', 'publicationyear', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'}); + logchange('kohadb', 'change', 'biblioitems', 'publicationyear', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'}); my $q_publicationyear=$dbh->quote($biblioitem->{'publicationyear'}); my $sth=$dbh->prepare("update biblioitems set publicationyear=$q_publicationyear where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '260', 'c', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'}); + logchange('marc', 'change', $Record_ID, '260', 'c', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'}); changeSubfield($Record_ID, '260', 'c', $obi->{'publicationyear'}, $biblioitem->{'publicationyear'}); } if ($biblioitem->{'illus'} ne $obi->{'illus'}) { - logchange('kohadb', 'biblioitems', 'illus', $obi->{'illus'}, $biblioitem->{'illus'}); + logchange('kohadb', 'change', 'biblioitems', 'illus', $obi->{'illus'}, $biblioitem->{'illus'}); my $q_illus=$dbh->quote($biblioitem->{'illus'}); my $sth=$dbh->prepare("update biblioitems set illus=$q_illus where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '700', 'a', $obi->{'illus'}, $biblioitem->{'illus'}); + logchange('marc', 'change', $Record_ID, '700', 'a', $obi->{'illus'}, $biblioitem->{'illus'}); changeSubfield($Record_ID, '700', 'a', $obi->{'illus'}, $biblioitem->{'illus'}); } if ($biblioitem->{'pages'} ne $obi->{'pages'}) { - logchange('kohadb', 'biblioitems', 'pages', $obi->{'pages'}, $biblioitem->{'pages'}); + logchange('kohadb', 'change', 'biblioitems', 'pages', $obi->{'pages'}, $biblioitem->{'pages'}); my $q_pages=$dbh->quote($biblioitem->{'pages'}); my $sth=$dbh->prepare("update biblioitems set pages=$q_pages where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '300', 'a', $obi->{'pages'}, $biblioitem->{'pages'}); + logchange('marc', 'change', $Record_ID, '300', 'a', $obi->{'pages'}, $biblioitem->{'pages'}); changeSubfield($Record_ID, '300', 'a', $obi->{'pages'}, $biblioitem->{'pages'}); } if ($biblioitem->{'size'} ne $obi->{'size'}) { - logchange('kohadb', 'biblioitems', 'size', $obi->{'size'}, $biblioitem->{'size'}); + logchange('kohadb', 'change', 'biblioitems', 'size', $obi->{'size'}, $biblioitem->{'size'}); my $q_size=$dbh->quote($biblioitem->{'size'}); my $sth=$dbh->prepare("update biblioitems set size=$q_size where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '300', 'c', $obi->{'size'}, $biblioitem->{'size'}); + logchange('marc', 'change', $Record_ID, '300', 'c', $obi->{'size'}, $biblioitem->{'size'}); changeSubfield($Record_ID, '300', 'c', $obi->{'size'}, $biblioitem->{'size'}); } if ($biblioitem->{'notes'} ne $obi->{'notes'}) { - logchange('kohadb', 'biblioitems', 'notes', $obi->{'notes'}, $biblioitem->{'notes'}); + logchange('kohadb', 'change', 'biblioitems', 'notes', $obi->{'notes'}, $biblioitem->{'notes'}); my $q_notes=$dbh->quote($biblioitem->{'notes'}); my $sth=$dbh->prepare("update biblioitems set notes=$q_notes where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '500', 'a', $obi->{'notes'}, $biblioitem->{'notes'}); + logchange('marc', 'change', $Record_ID, '500', 'a', $obi->{'notes'}, $biblioitem->{'notes'}); changeSubfield($Record_ID, '500', 'a', $obi->{'notes'}, $biblioitem->{'notes'}); } if ($biblioitem->{'lccn'} ne $obi->{'lccn'}) { - logchange('kohadb', 'biblioitems', 'lccn', $obi->{'lccn'}, $biblioitem->{'lccn'}); + logchange('kohadb', 'change', 'biblioitems', 'lccn', $obi->{'lccn'}, $biblioitem->{'lccn'}); my $q_lccn=$dbh->quote($biblioitem->{'lccn'}); my $sth=$dbh->prepare("update biblioitems set lccn=$q_lccn where biblioitemnumber=$biblioitemnumber"); - logchange('marc', '010', 'c', $obi->{'lccn'}, $biblioitem->{'lccn'}); - changeSubfield($Record_ID, '010', 'c', $obi->{'lccn'}, $biblioitem->{'lccn'}); + logchange('marc', 'change', $Record_ID, '010', 'a', $obi->{'lccn'}, $biblioitem->{'lccn'}); + changeSubfield($Record_ID, '010', 'a', $obi->{'lccn'}, $biblioitem->{'lccn'}); } } @@ -847,44 +911,108 @@ sub updateItem { my ($Subfield852_ID) = $sth->fetchrow; if ($item->{'barcode'} ne $olditem->{'barcode'}) { - logchange('kohadb', 'items', 'barcode', $olditem->{'barcode'}, $item->{'barcode'}); + logchange('kohadb', 'change', 'items', 'barcode', $olditem->{'barcode'}, $item->{'barcode'}); my $q_barcode=$dbh->quote($item->{'barcode'}); my $sth=$dbh->prepare("update items set barcode=$q_barcode where itemnumber=$itemnumber"); $sth->execute; my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'p', $olditem->{'barcode'}, $item->{'barcode'}, $Subfield876_ID); - logchange('marc', '876', 'p', $Subfield_Key, $olditem->{'barcode'}, $item->{'barcode'}); + logchange('marc', 'change', $Record_ID, '876', 'p', $Subfield_Key, $olditem->{'barcode'}, $item->{'barcode'}); } if ($item->{'booksellerid'} ne $olditem->{'booksellerid'}) { - logchange('kohadb', 'items', 'booksellerid', $olditem->{'booksellerid'}, $item->{'booksellerid'}); + logchange('kohadb', 'change', 'items', 'booksellerid', $olditem->{'booksellerid'}, $item->{'booksellerid'}); my $q_booksellerid=$dbh->quote($item->{'booksellerid'}); my $sth=$dbh->prepare("update items set booksellerid=$q_booksellerid where itemnumber=$itemnumber"); $sth->execute; my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'e', $olditem->{'booksellerid'}, $item->{'booksellerid'}, $Subfield876_ID); - logchange('marc', '876', 'e', $Subfield_Key, $olditem->{'booksellerid'}, $item->{'booksellerid'}); + logchange('marc', 'change', $Record_ID, '876', 'e', $Subfield_Key, $olditem->{'booksellerid'}, $item->{'booksellerid'}); } if ($item->{'dateaccessioned'} ne $olditem->{'dateaccessioned'}) { - logchange('kohadb', 'items', 'dateaccessioned', $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'}); + logchange('kohadb', 'change', 'items', 'dateaccessioned', $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'}); my $q_dateaccessioned=$dbh->quote($item->{'dateaccessioned'}); my $sth=$dbh->prepare("update items set dateaccessioned=$q_dateaccessioned where itemnumber=$itemnumber"); $sth->execute; my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'd', $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'}, $Subfield876_ID); - logchange('marc', '876', 'd', $Subfield_Key, $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'}); + logchange('marc', 'change', $Record_ID, '876', 'd', $Subfield_Key, $olditem->{'dateaccessioned'}, $item->{'dateaccessioned'}); } if ($item->{'homebranch'} ne $olditem->{'homebranch'}) { - logchange('kohadb', 'items', 'homebranch', $olditem->{'homebranch'}, $item->{'homebranch'}); + logchange('kohadb', 'change', 'items', 'homebranch', $olditem->{'homebranch'}, $item->{'homebranch'}); my $q_homebranch=$dbh->quote($item->{'homebranch'}); my $sth=$dbh->prepare("update items set homebranch=$q_homebranch where itemnumber=$itemnumber"); $sth->execute; my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'b', $olditem->{'homebranch'}, $item->{'homebranch'}, $Subfield876_ID); - logchange('marc', '876', 'b', $Subfield_Key, $olditem->{'homebranch'}, $item->{'homebranch'}); + logchange('marc', 'change', $Record_ID, '876', 'b', $Subfield_Key, $olditem->{'homebranch'}, $item->{'homebranch'}); } if ($item->{'holdingbranch'} ne $olditem->{'holdingbranch'}) { - logchange('kohadb', 'items', 'holdingbranch', $olditem->{'holdingbranch'}, $item->{'holdingbranch'}); + logchange('kohadb', 'change', 'items', 'holdingbranch', $olditem->{'holdingbranch'}, $item->{'holdingbranch'}); my $q_holdingbranch=$dbh->quote($item->{'holdingbranch'}); my $sth=$dbh->prepare("update items set holdingbranch=$q_holdingbranch where itemnumber=$itemnumber"); $sth->execute; my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'l', $olditem->{'holdingbranch'}, $item->{'holdingbranch'}, $Subfield876_ID); - logchange('marc', '876', 'l', $Subfield_Key, $olditem->{'holdingbranch'}, $item->{'holdingbranch'}); + logchange('marc', 'change', $Record_ID, '876', 'l', $Subfield_Key, $olditem->{'holdingbranch'}, $item->{'holdingbranch'}); + } + if ($item->{'price'} ne $olditem->{'price'}) { + logchange('kohadb', 'change', 'items', 'price', $olditem->{'price'}, $item->{'price'}); + my $q_price=$dbh->quote($item->{'price'}); + my $sth=$dbh->prepare("update items set price=$q_price where itemnumber=$itemnumber"); + $sth->execute; + my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'c', $olditem->{'price'}, $item->{'price'}, $Subfield876_ID); + logchange('marc', 'change', $Record_ID, '876', 'c', $Subfield_Key, $olditem->{'price'}, $item->{'price'}); + } + if ($item->{'itemnotes'} ne $olditem->{'itemnotes'}) { + logchange('kohadb', 'change', 'items', 'itemnotes', $olditem->{'itemnotes'}, $item->{'itemnotes'}); + my $q_itemnotes=$dbh->quote($item->{'itemnotes'}); + my $sth=$dbh->prepare("update items set itemnotes=$q_itemnotes where itemnumber=$itemnumber"); + $sth->execute; + my ($Subfield_ID, $Subfield_Key) = changeSubfield($Record_ID, '876', 'c', $olditem->{'itemnotes'}, $item->{'itemnotes'}, $Subfield876_ID); + logchange('marc', 'change', $Record_ID, '876', 'c', $Subfield_Key, $olditem->{'itemnotes'}, $item->{'itemnotes'}); + } + if ($item->{'notforloan'} ne $olditem->{'notforloan'}) { + logchange('kohadb', 'change', 'items', 'notforloan', $olditem->{'notforloan'}, $item->{'notforloan'}); + my $sth=$dbh->prepare("update items set notforloan=$notforloan where itemnumber=$itemnumber"); + $sth->execute; + if ($item->{'notforloan'}) { + my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Not for loan', $Subfield876_ID); + logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Not for loan'); + } else { + my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Not for loan', $Subfield876_ID); + logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Not for loan'); + } + } + if ($item->{'itemlost'} ne $olditem->{'itemlost'}) { + logchange('kohadb', 'change', 'items', 'itemlost', $olditem->{'itemlost'}, $item->{'itemlost'}); + my $sth=$dbh->prepare("update items set itemlost=$itemlost where itemnumber=$itemnumber"); + $sth->execute; + if ($item->{'itemlost'}) { + my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Item lost', $Subfield876_ID); + logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Item lost'); + } else { + my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Item lost', $Subfield876_ID); + logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Item lost'); + } + } + if ($item->{'wthdrawn'} ne $olditem->{'wthdrawn'}) { + logchange('kohadb', 'change', 'items', 'wthdrawn', $olditem->{'wthdrawn'}, $item->{'wthdrawn'}); + my $sth=$dbh->prepare("update items set wthdrawn=$wthdrawn where itemnumber=$itemnumber"); + $sth->execute; + if ($item->{'wthdrawn'}) { + my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Withdrawn', $Subfield876_ID); + logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Withdrawn'); + } else { + my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Withdrawn', $Subfield876_ID); + logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Withdrawn'); + } + } + if ($item->{'restricted'} ne $olditem->{'restricted'}) { + logchange('kohadb', 'change', 'items', 'restricted', $olditem->{'restricted'}, $item->{'restricted'}); + my $sth=$dbh->prepare("update items set restricted=$restricted where itemnumber=$itemnumber"); + $sth->execute; + if ($item->{'restricted'}) { + my ($Subfield_ID, $Subfield_Key) = addSubfield($Record_ID, '876', 'h', 'Restricted', $Subfield876_ID); + logchange('marc', 'add', $Record_ID, '876', 'h', $Subfield_Key, 'Restricted'); + } else { + my ($Subfield_ID, $Subfield_Key) = deleteSubfield($Record_ID, '876', 'h', 'Restricted', $Subfield876_ID); + logchange('marc', 'delete', $Record_ID, '876', 'h', $Subfield_Key, 'Restricted'); + } } } diff --git a/C4/Search.pm b/C4/Search.pm index 33bf7dd100..6a52090bd5 100755 --- a/C4/Search.pm +++ b/C4/Search.pm @@ -19,8 +19,7 @@ $VERSION = 0.01; &itemdata &bibdata &GetItems &borrdata &getacctlist &itemnodata &itemcount &OpacSearch &borrdata2 &NewBorrowerNumber &bibitemdata &borrissues &getboracctrecord &ItemType &itemissues &FrontSearch &subject &subtitle -&addauthor &bibitems &barcodes &findguarantees &allissues &systemprefs -&findguarantor); +&addauthor &bibitems &barcodes &findguarantees &allissues &systemprefs); %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ], # your exported package globals go here, @@ -73,22 +72,18 @@ sub findguarantees{ return($i,\@dat); } -sub findguarantor{ - my ($bornum)=@_; - my $dbh=C4Connect; - my $query="select guarantor from borrowers where - borrowernumber='$bornum'"; - my $sth=$dbh->prepare($query); - $sth->execute; - my $data=$sth->fetchrow_hashref; - $sth->finish; - $query="Select * from borrowers where borrowernumber='$data->{'guarantor'}'"; - $sth=$dbh->prepare($query); - $sth->execute; - $data=$sth->fetchrow_hashref; - $sth->finish; - $dbh->disconnect; - return($data); + +sub systemprefs { + my %systemprefs; + my $dbh=C4Connect; + my $sth=$dbh->prepare("select variable,value from systempreferences"); + $sth->execute; + while (my ($variable,$value)=$sth->fetchrow) { + $systemprefs{$variable}=$value; + } + $sth->finish; + $dbh->disconnect; + return(%systemprefs); } sub NewBorrowerNumber { @@ -143,7 +138,14 @@ sub OpacSearch { $sth->execute; $i=0; while (my $data=$sth->fetchrow_hashref){ - $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}"; + my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}"); + $sti->execute; + my ($dewey, $subclass) = $sti->fetchrow; + $dewey=~s/0*$//; + ($dewey == 0) && ($dewey=''); + ($dewey) && ($dewey.=" $subclass"); + $sti->finish; + $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$dewey"; $i++; } $sth->finish; @@ -177,7 +179,14 @@ sub FrontSearch { $sth->execute; $i=0; while (my $data=$sth->fetchrow_hashref){ - $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}"; + my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}"); + $sti->execute; + my ($dewey, $subclass) = $sti->fetchrow; + $dewey=~s/\.*0*$//; + ($dewey == 0) && ($dewey=''); + ($dewey) && ($dewey.=" $subclass"); + $sti->finish; + $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey"; # print $results[$i]; $i++; } @@ -233,12 +242,9 @@ sub KeywordSearch { my $count=@key; my $i=1; my @results; - my $query ="Select * from biblio - left join bibliosubtitle on - bibliosubtitle.biblionumber=biblio.biblionumber - left join biblioitems on - biblioitems.biblionumber=biblio.biblionumber - where + my $query ="Select * from biblio,bibliosubtitle,biblioitems where + biblio.biblionumber=bibliosubtitle.biblionumber and + biblioitems.biblionumber=biblio.biblionumber and (((title like '$key[0]%' or title like '% $key[0]%')"; while ($i < $count){ $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')"; @@ -292,7 +298,14 @@ sub KeywordSearch { $sth->execute; $i=0; while (my $data=$sth->fetchrow_hashref){ - $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}"; + my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}"); + $sti->execute; + my ($dewey, $subclass) = $sti->fetchrow; + $dewey=~s/\.*0*$//; + ($dewey == 0) && ($dewey=''); + ($dewey) && ($dewey.=" $subclass"); + $sti->finish; + $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey"; # print $results[$i]; $i++; } @@ -326,7 +339,7 @@ sub KeywordSearch { @results=sort @results; my @res; my $count=@results; - $i=1; + $i=0; if ($count > 0){ $res[0]=$results[0]; } @@ -357,7 +370,8 @@ sub CatSearch { my $query = ''; my @results; $search->{'title'}=~ s/'/\\'/g; - $search->{'author'}=~ s/'/\\'/g; + $search->{'author'}=~ s/'/\\'/g; + $search->{'illustrator'}=~ s/'/\\'/g; my $title = lc($search->{'title'}); if ($type eq 'loose') { @@ -366,11 +380,11 @@ sub CatSearch { my $count=@key; my $i=1; $query="select *,biblio.author,biblio.biblionumber from - biblio + biblioitems,biblio left join additionalauthors on additionalauthors.biblionumber =biblio.biblionumber - left join biblioitems on biblioitems.biblionumber=biblio.biblionumber - where + where biblioitems.biblionumber=biblio.biblionumber + and ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or additionalauthors.author like '$key[0]%' or additionalauthors.author like '% $key[0]%' @@ -398,10 +412,13 @@ sub CatSearch { if ($search->{'dewey'} ne ''){ $query.=" and dewey='$search->{'dewey'}' "; } + if ($search->{'illustrator'} ne ''){ + $query.=" and illus like '%".$search->{'illustrator'}."%' "; + } $query.=" group by biblio.biblionumber"; } else { - if ($search->{'title'} ne ''){ + if ($search->{'title'} ne '') { if ($search->{'ttype'} eq 'exact'){ $query="select * from biblio where @@ -418,11 +435,10 @@ sub CatSearch { my @key=split(' ',$search->{'title'}); my $count=@key; my $i=1; - $query="select * from biblio - left join bibliosubtitle on - bibliosubtitle.biblionumber=biblio.biblionumber - left join biblioitems on biblioitems.biblionumber=biblio.biblionumber + $query="select * from biblio,bibliosubtitle,biblioitems where + (biblio.biblionumber=bibliosubtitle.biblionumber and + biblioitems.biblionumber=biblio.biblionumber) and (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')"; while ($i<$count){ $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')"; @@ -453,6 +469,9 @@ sub CatSearch { if ($search->{'dewey'} ne ''){ $query.=" and dewey='$search->{'dewey'}' "; } + if ($search->{'illustrator'} ne ''){ + $query.=" and illus like '%".$search->{'illustrator'}."%' "; + } } } elsif ($search->{'class'} ne ''){ $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber"; @@ -463,14 +482,22 @@ sub CatSearch { $query.=" or itemtype='$temp[$i]'"; } $query.=")"; + if ($search->{'illustrator'} ne ''){ + $query.=" and illus like '%".$search->{'illustrator'}."%' "; + } } elsif ($search->{'dewey'} ne ''){ $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber and biblioitems.dewey like '$search->{'dewey'}%'"; + } elsif ($search->{'illustrator'} ne '') { + if ($search->{'illustrator'} ne ''){ + $query="select * from biblioitems,biblio + where biblio.biblionumber=biblioitems.biblionumber + and biblioitems.illus like '%".$search->{'illustrator'}."%'"; + } } $query .=" group by biblio.biblionumber"; } - } if ($type eq 'subject'){ my @key=split(' ',$search->{'subject'}); @@ -515,7 +542,11 @@ sub CatSearch { my $sth=$dbh->prepare($query); $sth->execute; my $data=$sth->fetchrow_hashref; - $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$data->{'isbn'}\t$data->{'itemtype'}"; + my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'}); + $dewey=~s/\.*0*$//; + ($dewey == 0) && ($dewey=''); + ($dewey) && ($dewey.=" $subclass"); + $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}"; $i2++; $sth->finish; } @@ -534,20 +565,26 @@ if ($type ne 'precise' && $type ne 'subject'){ $query=$query." order by subject"; } } -#print $query; my $sth=$dbh->prepare($query); $sth->execute; my $count=1; my $i=0; my $limit= $num+$offset; while (my $data=$sth->fetchrow_hashref){ + my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}"); + $sti->execute; + my ($dewey, $subclass) = $sti->fetchrow; + $dewey=~s/\.*0*$//; + ($dewey == 0) && ($dewey=''); + ($dewey) && ($dewey.=" $subclass"); + $sti->finish; if ($count > $offset && $count <= $limit){ if ($type ne 'subject' && $type ne 'precise'){ - $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}"; + $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}"; } elsif ($search->{'isbn'} ne '' || $search->{'item'} ne ''){ - $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}"; + $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}"; } else { - $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}"; + $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}"; } $i++; } @@ -597,7 +634,7 @@ sub ItemInfo { and biblio.biblionumber='$biblionumber' and branches.branchcode= items.holdingbranch "; # print $type; - if ($type eq 'opac'){ + if ($type ne 'intra'){ $query.=" and (items.itemlost<>1 or items.itemlost is NULL) and (wthdrawn <> 1 or wthdrawn is NULL)"; } @@ -736,8 +773,7 @@ sub bibdata { sub bibitemdata { my ($bibitem)=@_; my $dbh=C4Connect; - my $query="Select *, biblioitems.notes as bnotes - from biblio,biblioitems,itemtypes where biblio.biblionumber= + my $query="Select * from biblio,biblioitems,itemtypes where biblio.biblionumber= biblioitems.biblionumber and biblioitemnumber=$bibitem and biblioitems.itemtype=itemtypes.itemtype"; # print $query; @@ -807,29 +843,23 @@ sub itemissues { my $dbh=C4Connect; my $query="Select * from items where items.biblioitemnumber='$bibitem'"; -# print $query; my $sth=$dbh->prepare($query) || die $dbh->errstr; $sth->execute || die $sth->errstr; my $i=0; my @results; - while (my $data=$sth->fetchrow_hashref){ + while (my $data=$sth->fetchrow_hashref) { my $query2="select * from issues,borrowers where itemnumber=$data->{'itemnumber'} and returndate is NULL and issues.borrowernumber=borrowers.borrowernumber"; my $sth2=$dbh->prepare($query2); $sth2->execute; - if (my $data2=$sth2->fetchrow_hashref){ + if (my $data2=$sth2->fetchrow_hashref) { $data->{'date_due'}=$data2->{'date_due'}; $data->{'card'}=$data2->{'cardnumber'}; } else { - if ($data->{'wthdrawn'} eq '1'){ + if ($data->{'wthdrawn'} eq '1') { $data->{'date_due'}='Cancelled'; } else { -# my ($rescount,$reserves)=FindReserves($biblio,''); -# if ($rescount >0){# -# $data->{'date_due'}='Request'; -# } else { $data->{'date_due'}='Available'; -# } } } $sth2->finish; @@ -837,7 +867,6 @@ sub itemissues { and issues.borrowernumber=borrowers.borrowernumber order by date_due desc"; my $sth2=$dbh->prepare($query2) || die $dbh->errstr; -# print $query2; $sth2->execute || die $sth2->errstr; for (my $i2=0;$i2<2;$i2++){ if (my $data2=$sth2->fetchrow_hashref){ @@ -948,18 +977,13 @@ by date_due"; } sub allissues { - my ($bornum,$order,$limit)=@_; + my ($bornum)=@_; my $dbh=C4Connect; my $query; - $query="Select * from issues,biblio,items,biblioitems - where borrowernumber='$bornum' and - items.biblioitemnumber=biblioitems.biblioitemnumber and - items.itemnumber=issues.itemnumber and - items.biblionumber=biblio.biblionumber"; - $query.=" order by $order"; - if ($limit !=0){ - $query.=" limit $limit"; - } + $query="Select * from issues,biblio,items where borrowernumber='$bornum' and +items.itemnumber=issues.itemnumber and +items.biblionumber=biblio.biblionumber order +by date_due"; #print $query; my $sth=$dbh->prepare($query); $sth->execute; @@ -1065,9 +1089,9 @@ sub itemcount { my $dbh=C4Connect; my $query="Select * from items where biblionumber=$bibnum "; - if ($type eq 'opac'){ - $query.=" and (itemlost =0 or itemlost is NULL) and - (wthdrawn = 0 or wthdrawn is NULL)"; + if ($type ne 'intra'){ + $query.=" and (itemlost <>1 or itemlost is NULL) and + (wthdrawn <> 1 or wthdrawn is NULL)"; } my $sth=$dbh->prepare($query); # print $query; @@ -1101,7 +1125,7 @@ sub itemcount { if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){ $scount++; } - if ($data->{'itemlost'} eq '1' || $data->{'itemlost'} eq '2'){ + if ($data->{'itemlost'} eq '1'){ $lostcount++; } if ($data->{'holdingbranch'} eq 'FM'){ @@ -1114,8 +1138,7 @@ sub itemcount { $sth2->finish; } # if ($count == 0){ - my $query2="Select * from aqorders where biblionumber=$bibnum and - datecancellationprinted is NULL"; + my $query2="Select * from aqorders where biblionumber=$bibnum"; my $sth2=$dbh->prepare($query2); $sth2->execute; if (my $data=$sth2->fetchrow_hashref){ @@ -1178,20 +1201,6 @@ sub barcodes{ return(@barcodes); } - -sub systemprefs { - my %systemprefs; - my $dbh=C4Connect; - my $sth=$dbh->prepare("select variable,value from systempreferences"); - $sth->execute; - while (my ($variable,$value)=$sth->fetchrow) { - $systemprefs{$variable}=$value; - } - $sth->finish; - $dbh->disconnect; - return(%systemprefs); -} - END { } # module clean-up code here (global destructor) diff --git a/marc/marcschema.sql b/marc/marcschema.sql index ff0641eb27..7e0dfcab24 100644 --- a/marc/marcschema.sql +++ b/marc/marcschema.sql @@ -1,8 +1,9 @@ create table Resource_Table (Resource_ID INT UNSIGNED NOT NULL AUTO_INCREMENT, -Date_Added TIMESTAMP, Date_Modified TIMESTAMP, Record_ID INT UNSIGNED, +Date_Added TIMESTAMP, Date_Modified TIMESTAMP, Record_ID INT UNSIGNED NOT NULL, Statement_ID INT UNSIGNED, Scope_ID INT UNSIGNED, Container_ID INT UNSIGNED, -primary key(Resource_ID)); +primary key(Resource_ID), +key(Record_ID)); create table Schema_Table (Schema_ID INT UNSIGNED NOT NULL AUTO_INCREMENT, Date_Added TIMESTAMP, Date_Modified TIMESTAMP, @@ -39,20 +40,23 @@ primary key(Scope_ID)); create table Bib_Table (Bib_ID INT UNSIGNED NOT NULL AUTO_INCREMENT, Record_ID INT UNSIGNED NOT NULL, -Date_Added TIMESTAMP, Date_Modified TIMESTAMP, Control_ID INT UNSIGNED, -Tag_0XX_ID INT UNSIGNED, Tag_1XX_ID INT UNSIGNED, Tag_2XX_ID INT -UNSIGNED, Tag_3XX_ID INT UNSIGNED, Tag_4XX_ID INT UNSIGNED, Tag_5XX_ID -INT UNSIGNED, Tag_6XX_ID INT UNSIGNED, Tag_7XX_ID INT UNSIGNED, -Tag_8XX_ID INT UNSIGNED, Tag_9XX_ID INT UNSIGNED, Storage_ID INT -UNSIGNED, Holdings_ID INT UNSIGNED, -KEY ID_Index(Record_ID), primary key(Bib_ID)); +Date_Added TIMESTAMP, Date_Modified TIMESTAMP, Control_ID INT UNSIGNED NOT NULL, +Tag_0XX_ID INT UNSIGNED NOT NULL, Tag_1XX_ID INT UNSIGNED NOT NULL, Tag_2XX_ID INT +UNSIGNED NOT NULL, Tag_3XX_ID INT UNSIGNED NOT NULL, Tag_4XX_ID INT UNSIGNED NOT NULL, Tag_5XX_ID +INT UNSIGNED NOT NULL, Tag_6XX_ID INT UNSIGNED NOT NULL, Tag_7XX_ID INT UNSIGNED NOT NULL, +Tag_8XX_ID INT UNSIGNED NOT NULL, Tag_9XX_ID INT UNSIGNED NOT NULL, Storage_ID INT +UNSIGNED NOT NULL, Holdings_ID INT UNSIGNED NOT NULL, +KEY ID_Index(Record_ID), primary key(Bib_ID), +key (TAG_0XX_ID), key (TAG_1XX_ID), key (TAG_2XX_ID), key (TAG_3XX_ID), key (TAG_4XX_ID), key (TAG_5XX_ID), key (TAG_6XX_ID), key (TAG_7XX_ID), key (TAG_8XX_ID), key (TAG_9XX_ID), key (Storage_ID), key (Holdings_ID)); create table 0XX_Tag_Table (Tag_Key INT UNSIGNED NOT NULL AUTO_INCREMENT, Tag_ID INT UNSIGNED NOT NULL, Indicator1 CHAR(1) NOT NULL, Indicator2 CHAR(1) NOT NULL, -Tag CHAR(3) NOT NULL, Subfield_ID INT UNSIGNED, Authority_ID INT UNSIGNED, -Link_Flag ENUM('Y','N','B'), Storage_ID INT UNSIGNED, +Tag CHAR(3) NOT NULL, Subfield_ID INT UNSIGNED NOT NULL, Authority_ID INT UNSIGNED, +Link_Flag ENUM('Y','N','B'), Storage_ID INT UNSIGNED NOT NULL, KEY ID_Index(Tag_ID), +key (Subfield_ID), +key (Storage_ID), KEY Tag_Index (Tag(3)), primary key(Tag_Key)); create table 0XX_Subfield_Table @@ -66,9 +70,10 @@ KEY Subfield_Index (Subfield_Value(255)), primary key(Subfield_Key)); create table 1XX_Tag_Table (Tag_Key INT UNSIGNED NOT NULL AUTO_INCREMENT, Tag_ID INT UNSIGNED NOT NULL, Indicator1 CHAR(1) NOT NULL, Indicator2 CHAR(1) NOT NULL, -Tag CHAR(3) NOT NULL, Subfield_ID INT UNSIGNED, Authority_ID INT UNSIGNED, -Link_Flag ENUM('Y','N','B'), Storage_ID INT UNSIGNED, +Tag CHAR(3) NOT NULL, Subfield_ID INT UNSIGNED NOT NULL, Authority_ID INT UNSIGNED, +Link_Flag ENUM('Y','N','B'), Storage_ID INT UNSIGNED NOT NULL, KEY ID_Index(Tag_ID), +key (Subfield_ID), key (Storage_ID), KEY Tag_Index (Tag(3)), primary key(Tag_Key)); create table 1XX_Subfield_Table diff --git a/search.pl b/search.pl index f62100b2b6..4ca5e15bcf 100755 --- a/search.pl +++ b/search.pl @@ -2,9 +2,8 @@ #script to provide intranet (librarian) advanced search facility #modified 9/11/1999 by chris@katipo.co.nz #adding an extra comment to play with CVS (Si, 19/11/99) -#modified 29/12/99 by chris@katipo.co.nz to be usable by opac as well +#modified 29/12/99 by chris@katipo.co.nz to be usavle by opac as well #modified by chris 10/11/00 to fix dewey search -#modified by chris@katipo.co.nz 3/2/01 to fix glitch with " in titles use strict; use C4::Search; @@ -41,9 +40,10 @@ $search{'title'}=$title; my $keyword=validate($input->param('keyword')); $search{'keyword'}=$keyword; $search{'front'}=validate($input->param('front')); - my $author=validate($input->param('author')); $search{'author'}=$author; +my $illustrator=validate($input->param('illustrator')); +$search{'illustrator'}=$illustrator; my $subject=validate($input->param('subject')); $search{'subject'}=$subject; my $itemnumber=validate($input->param('item')); @@ -87,10 +87,9 @@ if ($itemnumber ne '' || $isbn ne ''){ # print "hey"; ($count,@results)=&KeywordSearch(\$blah,'intra',\%search,$num,$offset); } elsif ($search{'front'} ne '') { - $search{'keyword'}=$search{'front'}; - ($count,@results)&KeywordSearch(\$blah,'intra',\%search,$num,$offset); + ($count,@results)&FrontSearch(\$blah,'intra',\%search,$num,$offset); # print "hey"; - }elsif ($title ne '' || $author ne '' || $dewey ne '' || $class ne '') { + }elsif ($title ne '' || $author ne '' || $illustrator ne '' || $dewey ne '' || $class ne '') { ($count,@results)=&CatSearch(\$blah,'loose',\%search,$num,$offset); # print "hey"; } @@ -111,12 +110,16 @@ print mktablehdr; if ($type ne 'opac'){ if ($subject ne ''){ print mktablerow(1,$main,'SUBJECT','/images/background-mem.gif'); + } elsif ($illustrator ne '') { + print mktablerow(7,$main,'TITLE','AUTHOR', 'ILLUSTRATOR', bold('©'),'COUNT',bold('LOCATION'),'','/images/background-mem.gif'); } else { print mktablerow(6,$main,'TITLE','AUTHOR',bold('©'),'COUNT',bold('LOCATION'),'','/images/background-mem.gif'); } } else { if ($subject ne ''){ print mktablerow(6,$main,'SUBJECT','   ','   '); + } elsif ($illustrator ne '') { + print mktablerow(7,$main,'TITLE','AUTHOR','ILLUSTRATOR', bold('©'),'COUNT',bold('BRANCH'),''); } else { print mktablerow(6,$main,'TITLE','AUTHOR',bold('©'),'COUNT',bold('BRANCH'),''); } @@ -137,7 +140,6 @@ while ($i < $count2){ $stuff[1]=~ s/\`/\\\'/g; my $title2=$stuff[1]; $title2=~ s/ /%20/g; - $title2=~ s/\W//g; if ($subject eq ''){ # print $stuff[0]; $stuff[1]=mklink("/cgi-bin/koha/detail.pl?type=$type&bib=$stuff[2]&title=$title2",$stuff[1]); @@ -149,6 +151,8 @@ while ($i < $count2){ $word=~ s/\,/\,%20/g; $word=~ s/\n//g; my $url="/cgi-bin/koha/search.pl?author=$word&type=$type"; + $stuff[7]=$stuff[5]; + $stuff[5]=''; $stuff[0]=mklink($url,$stuff[0]); my ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount)=itemcount($env,$stuff[2],$type); $stuff[4]=$count; @@ -221,19 +225,35 @@ while ($i < $count2){ } if ($colour == 1){ - print mktablerow(6,$secondary,$stuff[1],$stuff[0],$stuff[3],$stuff[4],$stuff[5],$stuff[6]); + if ($illustrator) { + print mktablerow(7,$secondary,$stuff[1],$stuff[0],$stuff[7],$stuff[3],$stuff[4],$stuff[5],$stuff[6]); + } else { + print mktablerow(6,$secondary,$stuff[1],$stuff[0],$stuff[3],$stuff[4],$stuff[5],$stuff[6]); + } $colour=0; - } else{ - print mktablerow(6,'white',$stuff[1],$stuff[0],$stuff[3],$stuff[4],$stuff[5],$stuff[6]); + } else { + if ($illustrator) { + print mktablerow(7,'white',$stuff[1],$stuff[0],$stuff[7],$stuff[3],$stuff[4],$stuff[5],$stuff[6]); + } else { + print mktablerow(6,'white',$stuff[1],$stuff[0],$stuff[3],$stuff[4],$stuff[5],$stuff[6]); + } $colour=1; } $i++; } $offset=$num+$offset; if ($type ne 'opac'){ - print mktablerow(6,$main,'   ','   ','  ','  ','','','/images/background-mem.gif'); + if ($illustrator) { + print mktablerow(7,$main,'   ','   ','  ','  ','','','','/images/background-mem.gif'); + } else { + print mktablerow(6,$main,'   ','   ','  ','  ','','','/images/background-mem.gif'); + } } else { - print mktablerow(6,$main,'   ','   ','  ','   ','',''); + if ($illustrator) { + print mktablerow(7,$main,'   ','   ','  ','   ','', '',''); + } else { + print mktablerow(6,$main,'   ','   ','  ','   ','',''); + } } print mktableft(); my $search; -- 2.39.5