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 optional argument containing extra tables to search. Used in conjunction with $sqlstring. See usage in opac-search.pl.
156 String... so ',items,issues,reserves' allows the items, issues and reserves tables to be used.in a where.
160 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.
164 =head2 my $marcnotesarray = &getMARCnotes($dbh,$bibid,$marcflavour);
166 Returns a reference to an array containing all the notes stored in the MARC database for the given bibid.
167 $marcflavour ("MARC21" or "UNIMARC") determines which tags are used for retrieving subjects.
169 =head2 my $marcsubjctsarray = &getMARCsubjects($dbh,$bibid,$marcflavour);
171 Returns a reference to an array containing all the subjects stored in the MARC database for the given bibid.
172 $marcflavour ("MARC21" or "UNIMARC") determines which tags are used for retrieving subjects.
175 =head2 my $marcurlsarray = &getMARCurls($dbh,$bibid,$marcflavour);
177 Returns a reference to an array containing all the URLS stored in the MARC database for the given bibid.
178 $marcflavour ("MARC21" or "UNIMARC") isn't used in this version because both flavours of MARC use the same subfield for URLS (but eventually when we get the lables working we'll need to change this.
182 my ($dbh, $tags, $and_or, $excluding, $operator, $value, $offset,$length,$orderby,$desc_or_asc,$sqlstring, $extratables) = @_;
183 # build the sql request. She will look like :
185 # from marc_subfield_table as m1, marc_subfield_table as m2
186 # where m1.bibid=m2.bibid and
187 # (m1.subfieldvalue like "Des%" and m2.subfieldvalue like "27%")
189 # last minute stripping out of stuff
190 # doesn't work @$value =~ s/\'/ /;
191 # @$value = map { $_ =~ s/\'/ /g } @$value;
193 # "Normal" statements
194 my @normal_tags = ();
195 my @normal_and_or = ();
196 my @normal_operator = ();
197 my @normal_value = ();
198 # Extracts the NOT statements from the list of statements
201 my @not_operator = ();
204 $orderby = "biblio.title" unless $orderby;
205 $desc_or_asc = "ASC" unless $desc_or_asc;
206 #last minute stripping out of ' and ,
207 # paul : quoting, it's done a few lines lated.
208 # foreach $_ (@$value) {
213 # the item.notforloan contains an integer. Every value <>0 means "book unavailable for loan".
214 # but each library can have it's own table of meaning for each value. Get them
215 # 1st search if there is a list of authorised values connected to items.notforloan
216 my $sth = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.notforloan"');
218 my %notforloanstatus;
219 my ($authorised_valuecode) = $sth->fetchrow;
220 if ($authorised_valuecode) {
221 $sth = $dbh->prepare("select authorised_value,lib from authorised_values where category=?");
222 $sth->execute($authorised_valuecode);
223 while (my ($authorised_value,$lib) = $sth->fetchrow) {
224 $notforloanstatus{$authorised_value} = $lib?$lib:$authorised_value;
227 for(my $i = 0 ; $i <= $#{$value} ; $i++)
230 @$value[$i] =~ s/\*/%/g;
231 # remove % at the beginning
232 @$value[$i] =~ s/^%//g;
233 @$value[$i] =~ s/(\.|\?|\:|\!|\'|,|\-|\"|\(|\)|\[|\]|\{|\}|\/)/ /g if @$operator[$i] eq "contains";
234 if(@$excluding[$i]) # NOT statements
237 if(@$operator[$i] eq "contains")
239 foreach my $word (split(/ /, @$value[$i])) # if operator is contains, splits the words in separate requests
241 # remove the "%" for small word (3 letters. (note : the >4 is due to the % at the end)
242 # warn "word : $word";
243 $word =~ s/%//g unless length($word)>4;
244 unless (C4::Context->stopwords->{uc($word)} or length($word)==1) { #it's NOT a stopword => use it. Otherwise, ignore
245 push @not_tags, @$tags[$i];
246 push @not_and_or, "or"; # as request is negated, finds "foo" or "bar" if final request is NOT "foo" and "bar"
247 push @not_operator, @$operator[$i];
248 push @not_value, $word;
254 push @not_tags, @$tags[$i];
255 push @not_and_or, "or"; # as request is negated, finds "foo" or "bar" if final request is NOT "foo" and "bar"
256 push @not_operator, @$operator[$i];
257 push @not_value, @$value[$i];
260 else # NORMAL statements
262 if(@$operator[$i] eq "contains") # if operator is contains, splits the words in separate requests
264 foreach my $word (split(/ /, @$value[$i]))
266 # remove the "%" for small word (3 letters. (note : the >4 is due to the % at the end)
267 # warn "word : $word";
268 $word =~ s/%//g unless length($word)>4;
269 unless (C4::Context->stopwords->{uc($word)} or length($word)==1) { #it's NOT a stopword => use it. Otherwise, ignore
270 push @normal_tags, @$tags[$i];
271 push @normal_and_or, "and"; # assumes "foo" and "bar" if "foo bar" is entered
272 push @normal_operator, @$operator[$i];
273 push @normal_value, $word;
279 push @normal_tags, @$tags[$i];
280 push @normal_and_or, @$and_or[$i];
281 push @normal_operator, @$operator[$i];
282 push @normal_value, @$value[$i];
287 # Finds the basic results without the NOT requests
288 my ($sql_tables, $sql_where1, $sql_where2) = create_request($dbh,\@normal_tags, \@normal_and_or, \@normal_operator, \@normal_value);
289 $sql_where1 .= $sqlstring;
290 $sql_tables .= $extratables;
291 $sql_where1 .= "and TO_DAYS( NOW( ) ) - TO_DAYS( biblio.timestamp ) <30" if $orderby =~ "biblio.timestamp";
294 $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");
295 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";
297 $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");
298 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";
302 my $subtitle; # Added by JF for Subtitles
304 # Processes the NOT if any and there are results
305 my ($not_sql_tables, $not_sql_where1, $not_sql_where2);
307 if( ($sth->rows) && $any_not ) # some results to tune up and some NOT statements
309 ($not_sql_tables, $not_sql_where1, $not_sql_where2) = create_request($dbh,\@not_tags, \@not_and_or, \@not_operator, \@not_value);
313 while (my ($bibid) = $sth->fetchrow) {
314 push @tmpresult,$bibid;
317 warn "NOT : select distinct m1.bibid from $not_sql_tables where $not_sql_where2 and ($not_sql_where1)";
318 if ($not_sql_where2) {
319 $sth_not = $dbh->prepare("select distinct m1.bibid from $not_sql_tables where $not_sql_where2 and ($not_sql_where1)");
321 $sth_not = $dbh->prepare("select distinct m1.bibid from $not_sql_tables where $not_sql_where1");
328 while(my $bibid = $sth_not->fetchrow()) {
329 $not_bibids{$bibid} = 1; # populates the hashtable with the bibids matching the NOT statement
332 foreach my $bibid (@tmpresult)
334 if(!$not_bibids{$bibid})
336 push @result, $bibid;
342 else # no NOT statements
344 while (my ($bibid) = $sth->fetchrow) {
349 # we have bibid list. Now, loads title and author from [offset] to [offset]+[length]
350 my $counter = $offset;
351 # HINT : biblionumber as bn is important. The hash is fills biblionumber with items.biblionumber.
352 # so if you dont' has an item, you get a not nice empty value.
353 $sth = $dbh->prepare("SELECT biblio.biblionumber as bn,biblioitems.*,biblio.*, marc_biblio.bibid,itemtypes.notforloan,itemtypes.description
354 FROM biblio, marc_biblio
355 LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
356 LEFT JOIN itemtypes on itemtypes.itemtype=biblioitems.itemtype
357 WHERE biblio.biblionumber = marc_biblio.biblionumber AND bibid = ?");
358 my $sth_subtitle = $dbh->prepare("SELECT subtitle FROM bibliosubtitle WHERE biblionumber=?"); # Added BY JF for Subtitles
359 my @finalresult = ();
363 my ($oldbibid, $oldauthor, $oldtitle);
365 if (C4::Context->preference('hidelostitem')) {
366 $sth_itemCN = $dbh->prepare("select items.* from items where biblionumber=? and (itemlost = 0 or itemlost is NULL)");
368 $sth_itemCN = $dbh->prepare("select items.* from items where biblionumber=?");
370 my $sth_issue = $dbh->prepare("select date_due,returndate from issues where itemnumber=?");
371 # parse all biblios between start & end.
372 while (($counter <= $#result) && ($counter <= ($offset + $length))) {
373 # search & parse all items & note itemcallnumber
374 $sth->execute($result[$counter]);
376 my $line = $sth->fetchrow_hashref;
377 my $biblionumber=$line->{bn};
378 # Return subtitles first ADDED BY JF
379 $sth_subtitle->execute($biblionumber);
380 my $subtitle_here.= $sth_subtitle->fetchrow." ";
382 $subtitle = $subtitle_here;
383 # warn "Here's the Biblionumber ".$biblionumber;
384 # warn "and here's the subtitle: ".$subtitle_here;
388 # $continue=0 unless $line->{bn};
389 # my $lastitemnumber;
390 $sth_itemCN->execute($biblionumber);
392 my $notforloan=1; # to see if there is at least 1 item that can be issued
393 while (my $item = $sth_itemCN->fetchrow_hashref) {
394 # parse the result, putting holdingbranch & itemcallnumber in separate array
395 # then all other fields in the main array
397 # search if item is on loan
399 $sth_issue->execute($item->{itemnumber});
400 while (my $loan = $sth_issue->fetchrow_hashref) {
401 if ($loan->{date_due} and !$loan->{returndate}) {
402 $date_due = $loan->{date_due};
407 $lineCN{holdingbranch} = $item->{holdingbranch};
408 $lineCN{itemcallnumber} = $item->{itemcallnumber};
409 $lineCN{location} = $item->{location};
410 $lineCN{date_due} = format_date($date_due);
411 $lineCN{notforloan} = $notforloanstatus{$line->{notforloan}} if ($line->{notforloan}); # setting not forloan if itemtype is not for loan
412 $lineCN{notforloan} = $notforloanstatus{$item->{notforloan}} if ($item->{notforloan}); # setting not forloan it this item is not for loan
413 $notforloan=0 unless ($item->{notforloan} or $item->{wthdrawn} or $item->{itemlost});
414 push @CNresults,\%lineCN;
417 # save the biblio in the final array, with item and item issue status
420 $newline{totitem} = $totalitems;
421 # if $totalitems == 0, check if it's being ordered.
422 if ($totalitems == 0) {
423 my $sth = $dbh->prepare("select count(*) from aqorders where biblionumber=? and datecancellationprinted is NULL");
424 $sth->execute($biblionumber);
425 my ($ordered) = $sth->fetchrow;
426 $newline{onorder} = 1 if $ordered;
428 $newline{biblionumber} = $biblionumber;
429 $newline{norequests} = 0;
430 $newline{norequests} = 1 if ($line->{notforloan}); # itemtype not issuable
431 $newline{norequests} = 1 if (!$line->{notforloan} && $notforloan); # itemtype issuable but all items not issuable for instance
432 $newline{subtitle} = $subtitle; # put the subtitle in ADDED BY JF
434 my @CNresults2= @CNresults;
435 $newline{CN} = \@CNresults2;
436 $newline{'even'} = 1 if $#finalresult % 2 == 0;
437 $newline{'odd'} = 1 if $#finalresult % 2 == 1;
438 $newline{'timestamp'} = format_date($newline{timestamp});
440 push @finalresult, \%newline;
444 my $nbresults = $#result+1;
445 return (\@finalresult, $nbresults);
448 # Creates the SQL Request
451 my ($dbh,$tags, $and_or, $operator, $value) = @_;
453 my $sql_tables; # will contain marc_subfield_table as m1,...
454 my $sql_where1; # will contain the "true" where
455 my $sql_where2 = "("; # will contain m1.bibid=m2.bibid
456 my $nb_active=0; # will contain the number of "active" entries. an entry is active if a value is provided.
457 my $nb_table=1; # will contain the number of table. ++ on each entry EXCEPT when an OR is provided.
459 my $maxloop=8; # the maximum number of words to avoid a too complex search.
460 $maxloop = @$value if @$value<$maxloop;
462 for(my $i=0; $i<=$maxloop;$i++) {
466 if (@$operator[$i] eq "start") {
467 $sql_tables .= "marc_subfield_table as m$nb_table,";
468 $sql_where1 .= "(m1.subfieldvalue like ".$dbh->quote("@$value[$i]%");
470 $sql_where1 .=" and concat(m1.tag,m1.subfieldcode) in (@$tags[$i])";
473 } elsif (@$operator[$i] eq "contains") {
474 $sql_tables .= "marc_word as m$nb_table,";
475 $sql_where1 .= "(m1.word like ".$dbh->quote("@$value[$i]");
477 $sql_where1 .=" and m1.tagsubfield in (@$tags[$i])";
481 $sql_tables .= "marc_subfield_table as m$nb_table,";
482 $sql_where1 .= "(m1.subfieldvalue @$operator[$i] ".$dbh->quote("@$value[$i]");
484 $sql_where1 .=" and concat(m1.tag,m1.subfieldcode) in (@$tags[$i])";
489 if (@$operator[$i] eq "start") {
491 $sql_tables .= "marc_subfield_table as m$nb_table,";
492 $sql_where1 .= "@$and_or[$i] (m$nb_table.subfieldvalue like ".$dbh->quote("@$value[$i]%");
494 $sql_where1 .=" and concat(m$nb_table.tag,m$nb_table.subfieldcode) in (@$tags[$i])";
497 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
498 } elsif (@$operator[$i] eq "contains") {
499 if (@$and_or[$i] eq 'and') {
501 $sql_tables .= "marc_word as m$nb_table,";
502 $sql_where1 .= "@$and_or[$i] (m$nb_table.word like ".$dbh->quote("@$value[$i]");
504 $sql_where1 .=" and m$nb_table.tagsubfield in(@$tags[$i])";
507 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
509 $sql_where1 .= "@$and_or[$i] (m$nb_table.word like ".$dbh->quote("@$value[$i]");
511 $sql_where1 .=" and m$nb_table.tagsubfield in (@$tags[$i])";
514 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
518 $sql_tables .= "marc_subfield_table as m$nb_table,";
519 $sql_where1 .= "@$and_or[$i] (m$nb_table.subfieldvalue @$operator[$i] ".$dbh->quote(@$value[$i]);
521 $sql_where1 .=" and concat(m$nb_table.tag,m$nb_table.subfieldcode) in (@$tags[$i])";
523 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
530 if($sql_where2 ne "(") # some datas added to sql_where2, processing
532 $sql_where2 = substr($sql_where2, 0, (length($sql_where2)-5)); # deletes the trailing ' and '
535 else # no sql_where2 statement, deleting '('
539 chop $sql_tables; # deletes the trailing ','
540 return ($sql_tables, $sql_where1, $sql_where2);
544 my ($dbh, $bibid, $marcflavour) = @_;
545 my ($mintag, $maxtag);
546 if ($marcflavour eq "MARC21") {
549 } else { # assume unimarc if not marc21
554 my $sth=$dbh->prepare("SELECT subfieldvalue,tag FROM marc_subfield_table WHERE bibid=? AND tag BETWEEN ? AND ? ORDER BY tagorder");
556 $sth->execute($bibid,$mintag,$maxtag);
563 while (my $data=$sth->fetchrow_arrayref) {
564 my $value=$data->[0];
565 my $thistag=$data->[1];
569 if ($thistag ne $tag && $note ne "") {
570 $marcnote = {marcnote => $note,};
571 push @marcnotes, $marcnote;
575 if ($note ne $value) {
576 $note = $note." ".$value;
581 $marcnote = {marcnote => $note};
582 push @marcnotes, $marcnote; #load last tag into array
587 my $marcnotesarray=\@marcnotes;
588 return $marcnotesarray;
592 sub getMARCsubjects {
593 my ($dbh, $bibid, $marcflavour) = @_;
594 my ($mintag, $maxtag);
595 if ($marcflavour eq "MARC21") {
598 } else { # assume unimarc if not marc21
602 my $sth=$dbh->prepare("SELECT subfieldvalue,subfieldcode FROM marc_subfield_table WHERE bibid=? AND tag BETWEEN ? AND ? ORDER BY tagorder");
604 $sth->execute($bibid,$mintag,$maxtag);
611 while (my $data=$sth->fetchrow_arrayref) {
612 my $value = $data->[0];
613 my $subfield = $data->[1];
614 if ($subfield eq "a" && $value ne $subjct) {
615 $marcsubjct = {MARCSUBJCT => $value,};
616 push @marcsubjcts, $marcsubjct;
623 my $marcsubjctsarray=\@marcsubjcts;
624 return $marcsubjctsarray;
625 } #end getMARCsubjects
627 END { } # module clean-up code here (global destructor)
636 Koha Developement team <info@koha.org>