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,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,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,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,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,biblioitems.*,biblio.*, 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);
354 if (C4::Context->preference('hidelostitem')) {
355 $sth_itemCN = $dbh->prepare("select items.* from items where biblionumber=? and (itemlost = 0 or itemlost is NULL)");
357 $sth_itemCN = $dbh->prepare("select items.* from items where biblionumber=?");
359 my $sth_issue = $dbh->prepare("select date_due,returndate from issues where itemnumber=?");
360 # parse all biblios between start & end.
361 while (($counter <= $#result) && ($counter <= ($offset + $length))) {
362 # search & parse all items & note itemcallnumber
363 $sth->execute($result[$counter]);
365 my $line = $sth->fetchrow_hashref;
366 my $biblionumber=$line->{bn};
367 # Return subtitles first ADDED BY JF
368 $sth_subtitle->execute($biblionumber);
369 my $subtitle_here.= $sth_subtitle->fetchrow." ";
371 $subtitle = $subtitle_here;
372 # warn "Here's the Biblionumber ".$biblionumber;
373 # warn "and here's the subtitle: ".$subtitle_here;
377 # $continue=0 unless $line->{bn};
378 # my $lastitemnumber;
379 $sth_itemCN->execute($biblionumber);
381 my $notforloan=1; # to see if there is at least 1 item that can be issued
382 while (my $item = $sth_itemCN->fetchrow_hashref) {
383 # parse the result, putting holdingbranch & itemcallnumber in separate array
384 # then all other fields in the main array
386 # search if item is on loan
388 $sth_issue->execute($item->{itemnumber});
389 while (my $loan = $sth_issue->fetchrow_hashref) {
390 if ($loan->{date_due} and !$loan->{returndate}) {
391 $date_due = $loan->{date_due};
396 $lineCN{holdingbranch} = $item->{holdingbranch};
397 $lineCN{itemcallnumber} = $item->{itemcallnumber};
398 $lineCN{location} = $item->{location};
399 $lineCN{date_due} = format_date($date_due);
400 $lineCN{notforloan} = $notforloanstatus{$line->{notforloan}} if ($line->{notforloan}); # setting not forloan if itemtype is not for loan
401 $lineCN{notforloan} = $notforloanstatus{$item->{notforloan}} if ($item->{notforloan}); # setting not forloan it this item is not for loan
402 $notforloan=0 unless ($item->{notforloan} or $item->{wthdrawn} or $item->{itemlost});
403 push @CNresults,\%lineCN;
406 # save the biblio in the final array, with item and item issue status
409 $newline{totitem} = $totalitems;
410 # if $totalitems == 0, check if it's being ordered.
411 if ($totalitems == 0) {
412 my $sth = $dbh->prepare("select count(*) from aqorders where biblionumber=? and datecancellationprinted is NULL");
413 $sth->execute($biblionumber);
414 my ($ordered) = $sth->fetchrow;
415 $newline{onorder} = 1 if $ordered;
417 $newline{biblionumber} = $biblionumber;
418 $newline{norequests} = 0;
419 $newline{norequests} = 1 if ($line->{notforloan}); # itemtype not issuable
420 $newline{norequests} = 1 if (!$line->{notforloan} && $notforloan); # itemtype issuable but all items not issuable for instance
421 $newline{subtitle} = $subtitle; # put the subtitle in ADDED BY JF
423 my @CNresults2= @CNresults;
424 $newline{CN} = \@CNresults2;
425 $newline{'even'} = 1 if $#finalresult % 2 == 0;
426 $newline{'odd'} = 1 if $#finalresult % 2 == 1;
427 $newline{'timestamp'} = format_date($newline{timestamp});
429 push @finalresult, \%newline;
433 my $nbresults = $#result+1;
434 return (\@finalresult, $nbresults);
437 # Creates the SQL Request
440 my ($dbh,$tags, $and_or, $operator, $value) = @_;
442 my $sql_tables; # will contain marc_subfield_table as m1,...
443 my $sql_where1; # will contain the "true" where
444 my $sql_where2 = "("; # will contain m1.bibid=m2.bibid
445 my $nb_active=0; # will contain the number of "active" entries. an entry is active if a value is provided.
446 my $nb_table=1; # will contain the number of table. ++ on each entry EXCEPT when an OR is provided.
448 my $maxloop=8; # the maximum number of words to avoid a too complex search.
449 $maxloop = @$value if @$value<$maxloop;
451 for(my $i=0; $i<=$maxloop;$i++) {
455 if (@$operator[$i] eq "start") {
456 $sql_tables .= "marc_subfield_table as m$nb_table,";
457 $sql_where1 .= "(m1.subfieldvalue like ".$dbh->quote("@$value[$i]%");
459 $sql_where1 .=" and concat(m1.tag,m1.subfieldcode) in (@$tags[$i])";
462 } elsif (@$operator[$i] eq "contains") {
463 $sql_tables .= "marc_word as m$nb_table,";
464 $sql_where1 .= "(m1.word like ".$dbh->quote("@$value[$i]");
466 $sql_where1 .=" and m1.tagsubfield in (@$tags[$i])";
470 $sql_tables .= "marc_subfield_table as m$nb_table,";
471 $sql_where1 .= "(m1.subfieldvalue @$operator[$i] ".$dbh->quote("@$value[$i]");
473 $sql_where1 .=" and concat(m1.tag,m1.subfieldcode) in (@$tags[$i])";
478 if (@$operator[$i] eq "start") {
480 $sql_tables .= "marc_subfield_table as m$nb_table,";
481 $sql_where1 .= "@$and_or[$i] (m$nb_table.subfieldvalue like ".$dbh->quote("@$value[$i]%");
483 $sql_where1 .=" and concat(m$nb_table.tag,m$nb_table.subfieldcode) in (@$tags[$i])";
486 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
487 } elsif (@$operator[$i] eq "contains") {
488 if (@$and_or[$i] eq 'and') {
490 $sql_tables .= "marc_word as m$nb_table,";
491 $sql_where1 .= "@$and_or[$i] (m$nb_table.word like ".$dbh->quote("@$value[$i]");
493 $sql_where1 .=" and m$nb_table.tagsubfield in(@$tags[$i])";
496 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
498 $sql_where1 .= "@$and_or[$i] (m$nb_table.word like ".$dbh->quote("@$value[$i]");
500 $sql_where1 .=" and m$nb_table.tagsubfield in (@$tags[$i])";
503 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
507 $sql_tables .= "marc_subfield_table as m$nb_table,";
508 $sql_where1 .= "@$and_or[$i] (m$nb_table.subfieldvalue @$operator[$i] ".$dbh->quote(@$value[$i]);
510 $sql_where1 .=" and concat(m$nb_table.tag,m$nb_table.subfieldcode) in (@$tags[$i])";
512 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
519 if($sql_where2 ne "(") # some datas added to sql_where2, processing
521 $sql_where2 = substr($sql_where2, 0, (length($sql_where2)-5)); # deletes the trailing ' and '
524 else # no sql_where2 statement, deleting '('
528 chop $sql_tables; # deletes the trailing ','
529 return ($sql_tables, $sql_where1, $sql_where2);
533 my ($dbh, $bibid, $marcflavour) = @_;
534 my ($mintag, $maxtag);
535 if ($marcflavour eq "MARC21") {
538 } else { # assume unimarc if not marc21
543 my $sth=$dbh->prepare("SELECT subfieldvalue,tag FROM marc_subfield_table WHERE bibid=? AND tag BETWEEN ? AND ? ORDER BY tagorder");
545 $sth->execute($bibid,$mintag,$maxtag);
552 while (my $data=$sth->fetchrow_arrayref) {
553 my $value=$data->[0];
554 my $thistag=$data->[1];
558 if ($thistag ne $tag && $note ne "") {
559 $marcnote = {marcnote => $note,};
560 push @marcnotes, $marcnote;
564 if ($note ne $value) {
565 $note = $note." ".$value;
570 $marcnote = {marcnote => $note};
571 push @marcnotes, $marcnote; #load last tag into array
577 my $marcnotesarray=\@marcnotes;
578 return $marcnotesarray;
582 sub getMARCsubjects {
583 my ($dbh, $bibid, $marcflavour) = @_;
584 my ($mintag, $maxtag);
585 if ($marcflavour eq "MARC21") {
588 } else { # assume unimarc if not marc21
592 my $sth=$dbh->prepare("SELECT subfieldvalue,subfieldcode FROM marc_subfield_table WHERE bibid=? AND tag BETWEEN ? AND ? ORDER BY tagorder");
594 $sth->execute($bibid,$mintag,$maxtag);
601 while (my $data=$sth->fetchrow_arrayref) {
602 my $value = $data->[0];
603 my $subfield = $data->[1];
604 if ($subfield eq "a" && $value ne $subjct) {
605 $marcsubjct = {MARCSUBJCT => $value,};
606 push @marcsubjcts, $marcsubjct;
614 my $marcsubjctsarray=\@marcsubjcts;
615 return $marcsubjctsarray;
616 } #end getMARCsubjects
618 END { } # module clean-up code here (global destructor)
627 Koha Developement team <info@koha.org>