1 package C4::SearchMarc;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
28 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
30 # set the version for version checking
35 C4::Search - Functions for searching the Koha MARC catalog
39 This module provides the searching facilities for the Koha MARC catalog
44 @EXPORT = qw(&catalogsearch &findseealso &findsuggestion &getMARCnotes &getMARCsubjects);
46 =head1 findsuggestion($dbh,$values);
48 =head2 $dbh is a link to the DB handler.
51 my $dbh =C4::Context->dbh;
53 =head2 $values is a word
55 Searches words with the same soundex, ordered by frequency of use.
56 Useful to suggest other searches to the users.
61 my ($dbh,$values) = @_;
62 my $sth = $dbh->prepare("SELECT count( * ) AS total, word FROM marc_word WHERE sndx_word = soundex( ? ) AND word <> ? GROUP BY word ORDER BY total DESC");
64 for(my $i = 0 ; $i <= $#{$values} ; $i++) {
65 if (length(@$values[$i]) >=5) {
66 $sth->execute(@$values[$i],@$values[$i]);
69 while ((my ($count,$word) = $sth->fetchrow) and $resfound <=10) {
70 push @results, "@$values[$i]|$word|$count";
71 # $results{@$values[$i]} = \@resline;
79 =head1 findseealso($dbh,$fields);
81 =head2 $dbh is a link to the DB handler.
84 my $dbh =C4::Context->dbh;
86 =head2 $fields is a reference to the fields array
88 This function modify the @$fields array and add related fields to search on.
93 my ($dbh, $fields) = @_;
94 my $tagslib = MARCgettagslib ($dbh,1);
95 for (my $i=0;$i<=$#{$fields};$i++) {
96 my ($tag) =substr(@$fields[$i],1,3);
97 my ($subfield) =substr(@$fields[$i],4,1);
98 @$fields[$i].=','.$tagslib->{$tag}->{$subfield}->{seealso} if ($tagslib->{$tag}->{$subfield}->{seealso});
102 =head1 my ($count, @results) = catalogsearch($dbh, $tags, $and_or, $excluding, $operator, $value, $offset,$length,$orderby,$sqlstring);
104 =head2 $dbh is a link to the DB handler.
107 my $dbh =C4::Context->dbh;
109 $tags,$and_or, $excluding, $operator, $value are references to array
113 contains the list of tags+subfields (for example : $@tags[0] = '200a')
114 A field can be a list of fields : '200f','700a','700b','701a','701b'
120 contains a list of strings containing and or or. The 1st value is useless.
124 contains 0 or 1. If 1, then the request is negated.
128 contains contains,=,start,>,>=,<,<= the = and start work on the complete subfield. The contains operator works on every word in the subfield.
131 contains home, search home anywhere.
132 = home, search a string being home.
136 contains the value to search
137 If it contains a * or a %, then the search is partial.
139 =head2 $offset and $length
141 returns $length results, beginning at $offset
145 define the field used to order the request. Any field in the biblio/biblioitem tables can be used. DESC is possible too
147 (for example title, title DESC,...)
151 optional argument containing an sql string to be used in the 'where' statement. see usage in opac-search.pl.
155 returns an array containing hashes. The hash contains all biblio & biblioitems fields and a reference to an item hash. The "item hash contains one line for each callnumber & the number of items related to the callnumber.
159 =head2 my $marcnotesarray = &getMARCnotes($dbh,$bibid,$marcflavour);
161 Returns a reference to an array containing all the notes stored in the MARC database for the given bibid.
162 $marcflavour ("MARC21" or "UNIMARC") determines which tags are used for retrieving subjects.
164 =head2 my $marcsubjctsarray = &getMARCsubjects($dbh,$bibid,$marcflavour);
166 Returns a reference to an array containing all the subjects stored in the MARC database for the given bibid.
167 $marcflavour ("MARC21" or "UNIMARC") determines which tags are used for retrieving subjects.
172 my ($dbh, $tags, $and_or, $excluding, $operator, $value, $offset,$length,$orderby,$desc_or_asc,$sqlstring) = @_;
173 # build the sql request. She will look like :
175 # from marc_subfield_table as m1, marc_subfield_table as m2
176 # where m1.bibid=m2.bibid and
177 # (m1.subfieldvalue like "Des%" and m2.subfieldvalue like "27%")
179 # last minute stripping out of stuff
180 # doesn't work @$value =~ s/\'/ /;
181 # @$value = map { $_ =~ s/\'/ /g } @$value;
183 # "Normal" statements
184 my @normal_tags = ();
185 my @normal_and_or = ();
186 my @normal_operator = ();
187 my @normal_value = ();
188 # Extracts the NOT statements from the list of statements
191 my @not_operator = ();
194 $orderby = "biblio.title" unless $orderby;
195 $desc_or_asc = "ASC" unless $desc_or_asc;
196 #last minute stripping out of ' and ,
197 # paul : quoting, it's done a few lines lated.
198 # foreach $_ (@$value) {
203 # the item.notforloan contains an integer. Every value <>0 means "book unavailable for loan".
204 # but each library can have it's own table of meaning for each value. Get them
205 # 1st search if there is a list of authorised values connected to items.notforloan
206 my $sth = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.notforloan"');
208 my %notforloanstatus;
209 my ($authorised_valuecode) = $sth->fetchrow;
210 if ($authorised_valuecode) {
211 $sth = $dbh->prepare("select authorised_value,lib from authorised_values where category=?");
212 $sth->execute($authorised_valuecode);
213 while (my ($authorised_value,$lib) = $sth->fetchrow) {
214 $notforloanstatus{$authorised_value} = $lib?$lib:$authorised_value;
217 for(my $i = 0 ; $i <= $#{$value} ; $i++)
220 @$value[$i] =~ s/\*/%/g;
221 # remove % at the beginning
222 @$value[$i] =~ s/^%//g;
223 @$value[$i] =~ s/(\.|\?|\:|\!|\'|,|\-|\"|\(|\)|\[|\]|\{|\}|\/)/ /g if @$operator[$i] eq "contains";
224 if(@$excluding[$i]) # NOT statements
227 if(@$operator[$i] eq "contains")
229 foreach my $word (split(/ /, @$value[$i])) # if operator is contains, splits the words in separate requests
231 # remove the "%" for small word (3 letters. (note : the >4 is due to the % at the end)
232 # warn "word : $word";
233 $word =~ s/%//g unless length($word)>4;
234 unless (C4::Context->stopwords->{uc($word)} or length($word)==1) { #it's NOT a stopword => use it. Otherwise, ignore
235 push @not_tags, @$tags[$i];
236 push @not_and_or, "or"; # as request is negated, finds "foo" or "bar" if final request is NOT "foo" and "bar"
237 push @not_operator, @$operator[$i];
238 push @not_value, $word;
244 push @not_tags, @$tags[$i];
245 push @not_and_or, "or"; # as request is negated, finds "foo" or "bar" if final request is NOT "foo" and "bar"
246 push @not_operator, @$operator[$i];
247 push @not_value, @$value[$i];
250 else # NORMAL statements
252 if(@$operator[$i] eq "contains") # if operator is contains, splits the words in separate requests
254 foreach my $word (split(/ /, @$value[$i]))
256 # remove the "%" for small word (3 letters. (note : the >4 is due to the % at the end)
257 # warn "word : $word";
258 $word =~ s/%//g unless length($word)>4;
259 unless (C4::Context->stopwords->{uc($word)} or length($word)==1) { #it's NOT a stopword => use it. Otherwise, ignore
260 push @normal_tags, @$tags[$i];
261 push @normal_and_or, "and"; # assumes "foo" and "bar" if "foo bar" is entered
262 push @normal_operator, @$operator[$i];
263 push @normal_value, $word;
269 push @normal_tags, @$tags[$i];
270 push @normal_and_or, @$and_or[$i];
271 push @normal_operator, @$operator[$i];
272 push @normal_value, @$value[$i];
277 # Finds the basic results without the NOT requests
278 my ($sql_tables, $sql_where1, $sql_where2) = create_request($dbh,\@normal_tags, \@normal_and_or, \@normal_operator, \@normal_value);
279 $sql_where1 .=" ". $sqlstring;
280 $sql_where1 .= "and TO_DAYS( NOW( ) ) - TO_DAYS( biblio.timestamp ) <30" if $orderby =~ "biblio.timestamp";
283 $sth = $dbh->prepare("select distinct m1.bibid from biblio,biblioitems,items,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where2 and ($sql_where1) order by $orderby $desc_or_asc");
284 warn "Q2 : select distinct m1.bibid from biblio,biblioitems,items,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where2 and ($sql_where1) order by $orderby $desc_or_asc term is @$value";
286 $sth = $dbh->prepare("select distinct m1.bibid from biblio,biblioitems,items,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where1 order by $orderby $desc_or_asc");
287 warn "Q : select distinct m1.bibid from biblio,biblioitems,items,marc_biblio,$sql_tables where biblio.biblionumber=marc_biblio.biblionumber and biblio.biblionumber=biblioitems.biblionumber and m1.bibid=marc_biblio.bibid and $sql_where1 order by $orderby $desc_or_asc";
291 my $subtitle; # Added by JF for Subtitles
293 # Processes the NOT if any and there are results
294 my ($not_sql_tables, $not_sql_where1, $not_sql_where2);
296 if( ($sth->rows) && $any_not ) # some results to tune up and some NOT statements
298 ($not_sql_tables, $not_sql_where1, $not_sql_where2) = create_request($dbh,\@not_tags, \@not_and_or, \@not_operator, \@not_value);
302 while (my ($bibid) = $sth->fetchrow) {
303 push @tmpresult,$bibid;
306 warn "NOT : select distinct m1.bibid from $not_sql_tables where $not_sql_where2 and ($not_sql_where1)";
307 if ($not_sql_where2) {
308 $sth_not = $dbh->prepare("select distinct m1.bibid from $not_sql_tables where $not_sql_where2 and ($not_sql_where1)");
310 $sth_not = $dbh->prepare("select distinct m1.bibid from $not_sql_tables where $not_sql_where1");
317 while(my $bibid = $sth_not->fetchrow()) {
318 $not_bibids{$bibid} = 1; # populates the hashtable with the bibids matching the NOT statement
321 foreach my $bibid (@tmpresult)
323 if(!$not_bibids{$bibid})
325 push @result, $bibid;
331 else # no NOT statements
333 while (my ($bibid) = $sth->fetchrow) {
338 # we have bibid list. Now, loads title and author from [offset] to [offset]+[length]
339 my $counter = $offset;
340 # HINT : biblionumber as bn is important. The hash is fills biblionumber with items.biblionumber.
341 # so if you dont' has an item, you get a not nice empty value.
342 $sth = $dbh->prepare("SELECT biblio.biblionumber as bn,biblio.*, biblioitems.*,marc_biblio.bibid,itemtypes.notforloan,itemtypes.description
343 FROM biblio, marc_biblio
344 LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
345 LEFT JOIN itemtypes on itemtypes.itemtype=biblioitems.itemtype
346 WHERE biblio.biblionumber = marc_biblio.biblionumber AND bibid = ?");
347 my $sth_subtitle = $dbh->prepare("SELECT subtitle FROM bibliosubtitle WHERE biblionumber=?"); # Added BY JF for Subtitles
348 my @finalresult = ();
352 my ($oldbibid, $oldauthor, $oldtitle);
353 my $sth_itemCN = $dbh->prepare("select items.* from items where biblionumber=?");
354 my $sth_issue = $dbh->prepare("select date_due,returndate from issues where itemnumber=?");
355 # parse all biblios between start & end.
356 while (($counter <= $#result) && ($counter <= ($offset + $length))) {
357 # search & parse all items & note itemcallnumber
358 $sth->execute($result[$counter]);
360 my $line = $sth->fetchrow_hashref;
361 my $biblionumber=$line->{bn};
362 # Return subtitles first ADDED BY JF
363 $sth_subtitle->execute($biblionumber);
364 my $subtitle_here.= $sth_subtitle->fetchrow." ";
366 $subtitle = $subtitle_here;
367 # warn "Here's the Biblionumber ".$biblionumber;
368 # warn "and here's the subtitle: ".$subtitle_here;
372 # $continue=0 unless $line->{bn};
373 # my $lastitemnumber;
374 $sth_itemCN->execute($biblionumber);
376 my $notforloan=1; # to see if there is at least 1 item that can be issued
377 while (my $item = $sth_itemCN->fetchrow_hashref) {
378 # parse the result, putting holdingbranch & itemcallnumber in separate array
379 # then all other fields in the main array
381 # search if item is on loan
383 $sth_issue->execute($item->{itemnumber});
384 while (my $loan = $sth_issue->fetchrow_hashref) {
385 if ($loan->{date_due} and !$loan->{returndate}) {
386 $date_due = $loan->{date_due};
391 $lineCN{holdingbranch} = $item->{holdingbranch};
392 $lineCN{itemcallnumber} = $item->{itemcallnumber};
393 $lineCN{location} = $item->{location};
394 $lineCN{date_due} = format_date($date_due);
395 $lineCN{notforloan} = $notforloanstatus{$line->{notforloan}} if ($line->{notforloan}); # setting not forloan if itemtype is not for loan
396 $lineCN{notforloan} = $notforloanstatus{$item->{notforloan}} if ($item->{notforloan}); # setting not forloan it this item is not for loan
397 $notforloan=0 unless ($item->{notforloan} or $item->{wthdrawn} or $item->{itemlost});
398 push @CNresults,\%lineCN;
401 # save the biblio in the final array, with item and item issue status
404 $newline{totitem} = $totalitems;
405 # if $totalitems == 0, check if it's being ordered.
406 if ($totalitems == 0) {
407 my $sth = $dbh->prepare("select count(*) from aqorders where biblionumber=? and datecancellationprinted is NULL");
408 $sth->execute($biblionumber);
409 my ($ordered) = $sth->fetchrow;
410 $newline{onorder} = 1 if $ordered;
412 $newline{biblionumber} = $biblionumber;
413 $newline{norequests} = 0;
414 $newline{norequests} = 1 if ($line->{notforloan}); # itemtype not issuable
415 $newline{norequests} = 1 if (!$line->{notforloan} && $notforloan); # itemtype issuable but all items not issuable for instance
416 $newline{subtitle} = $subtitle; # put the subtitle in ADDED BY JF
418 my @CNresults2= @CNresults;
419 $newline{CN} = \@CNresults2;
420 $newline{'even'} = 1 if $#finalresult % 2 == 0;
421 $newline{'odd'} = 1 if $#finalresult % 2 == 1;
422 $newline{'timestamp'} = format_date($newline{timestamp});
424 push @finalresult, \%newline;
428 my $nbresults = $#result+1;
429 return (\@finalresult, $nbresults);
432 # Creates the SQL Request
435 my ($dbh,$tags, $and_or, $operator, $value) = @_;
437 my $sql_tables; # will contain marc_subfield_table as m1,...
438 my $sql_where1; # will contain the "true" where
439 my $sql_where2 = "("; # will contain m1.bibid=m2.bibid
440 my $nb_active=0; # will contain the number of "active" entries. an entry is active if a value is provided.
441 my $nb_table=1; # will contain the number of table. ++ on each entry EXCEPT when an OR is provided.
443 my $maxloop=8; # the maximum number of words to avoid a too complex search.
444 $maxloop = @$value if @$value<$maxloop;
446 for(my $i=0; $i<=$maxloop;$i++) {
450 if (@$operator[$i] eq "start") {
451 $sql_tables .= "marc_subfield_table as m$nb_table,";
452 $sql_where1 .= "(m1.subfieldvalue like ".$dbh->quote("@$value[$i]%");
454 $sql_where1 .=" and concat(m1.tag,m1.subfieldcode) in (@$tags[$i])";
457 } elsif (@$operator[$i] eq "contains") {
458 $sql_tables .= "marc_word as m$nb_table,";
459 $sql_where1 .= "(m1.word like ".$dbh->quote("@$value[$i]");
461 $sql_where1 .=" and m1.tagsubfield in (@$tags[$i])";
465 $sql_tables .= "marc_subfield_table as m$nb_table,";
466 $sql_where1 .= "(m1.subfieldvalue @$operator[$i] ".$dbh->quote("@$value[$i]");
468 $sql_where1 .=" and concat(m1.tag,m1.subfieldcode) in (@$tags[$i])";
473 if (@$operator[$i] eq "start") {
475 $sql_tables .= "marc_subfield_table as m$nb_table,";
476 $sql_where1 .= "@$and_or[$i] (m$nb_table.subfieldvalue like ".$dbh->quote("@$value[$i]%");
478 $sql_where1 .=" and concat(m$nb_table.tag,m$nb_table.subfieldcode) in (@$tags[$i])";
481 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
482 } elsif (@$operator[$i] eq "contains") {
483 if (@$and_or[$i] eq 'and') {
485 $sql_tables .= "marc_word as m$nb_table,";
486 $sql_where1 .= "@$and_or[$i] (m$nb_table.word like ".$dbh->quote("@$value[$i]");
488 $sql_where1 .=" and m$nb_table.tagsubfield in(@$tags[$i])";
491 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
493 $sql_where1 .= "@$and_or[$i] (m$nb_table.word like ".$dbh->quote("@$value[$i]");
495 $sql_where1 .=" and m$nb_table.tagsubfield in (@$tags[$i])";
498 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
502 $sql_tables .= "marc_subfield_table as m$nb_table,";
503 $sql_where1 .= "@$and_or[$i] (m$nb_table.subfieldvalue @$operator[$i] ".$dbh->quote(@$value[$i]);
505 $sql_where1 .=" and concat(m$nb_table.tag,m$nb_table.subfieldcode) in (@$tags[$i])";
507 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
514 if($sql_where2 ne "(") # some datas added to sql_where2, processing
516 $sql_where2 = substr($sql_where2, 0, (length($sql_where2)-5)); # deletes the trailing ' and '
519 else # no sql_where2 statement, deleting '('
523 chop $sql_tables; # deletes the trailing ','
524 return ($sql_tables, $sql_where1, $sql_where2);
528 my ($dbh, $bibid, $marcflavour) = @_;
529 my ($mintag, $maxtag);
530 if ($marcflavour eq "MARC21") {
533 } else { # assume unimarc if not marc21
538 my $sth=$dbh->prepare("SELECT subfieldvalue,tag FROM marc_subfield_table WHERE bibid=? AND tag BETWEEN ? AND ? ORDER BY tagorder");
540 $sth->execute($bibid,$mintag,$maxtag);
547 while (my $data=$sth->fetchrow_arrayref) {
548 my $value=$data->[0];
549 my $thistag=$data->[1];
553 if ($thistag ne $tag && $note ne "") {
554 $marcnote = {marcnote => $note,};
555 push @marcnotes, $marcnote;
559 if ($note ne $value) {
560 $note = $note." ".$value;
565 $marcnote = {marcnote => $note};
566 push @marcnotes, $marcnote; #load last tag into array
572 my $marcnotesarray=\@marcnotes;
573 return $marcnotesarray;
577 sub getMARCsubjects {
578 my ($dbh, $bibid, $marcflavour) = @_;
579 my ($mintag, $maxtag);
580 if ($marcflavour eq "MARC21") {
583 } else { # assume unimarc if not marc21
587 my $sth=$dbh->prepare("SELECT subfieldvalue,subfieldcode FROM marc_subfield_table WHERE bibid=? AND tag BETWEEN ? AND ? ORDER BY tagorder");
589 $sth->execute($bibid,$mintag,$maxtag);
596 while (my $data=$sth->fetchrow_arrayref) {
597 my $value = $data->[0];
598 my $subfield = $data->[1];
599 if ($subfield eq "a" && $value ne $subjct) {
600 $marcsubjct = {MARCSUBJCT => $value,};
601 push @marcsubjcts, $marcsubjct;
609 my $marcsubjctsarray=\@marcsubjcts;
610 return $marcsubjctsarray;
611 } #end getMARCsubjects
613 END { } # module clean-up code here (global destructor)
622 Koha Developement team <info@koha.org>