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.
177 my ($dbh, $tags, $and_or, $excluding, $operator, $value, $offset,$length,$orderby,$desc_or_asc,$sqlstring, $extratables) = @_;
178 # build the sql request. She will look like :
180 # from marc_subfield_table as m1, marc_subfield_table as m2
181 # where m1.bibid=m2.bibid and
182 # (m1.subfieldvalue like "Des%" and m2.subfieldvalue like "27%")
184 # last minute stripping out of stuff
185 # doesn't work @$value =~ s/\'/ /;
186 # @$value = map { $_ =~ s/\'/ /g } @$value;
188 # "Normal" statements
189 my @normal_tags = ();
190 my @normal_and_or = ();
191 my @normal_operator = ();
192 my @normal_value = ();
193 # Extracts the NOT statements from the list of statements
196 my @not_operator = ();
199 $orderby = "biblio.title" unless $orderby;
200 $desc_or_asc = "ASC" unless $desc_or_asc;
201 #last minute stripping out of ' and ,
202 # paul : quoting, it's done a few lines lated.
203 # foreach $_ (@$value) {
208 # the item.notforloan contains an integer. Every value <>0 means "book unavailable for loan".
209 # but each library can have it's own table of meaning for each value. Get them
210 # 1st search if there is a list of authorised values connected to items.notforloan
211 my $sth = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.notforloan"');
213 my %notforloanstatus;
214 my ($authorised_valuecode) = $sth->fetchrow;
215 if ($authorised_valuecode) {
216 $sth = $dbh->prepare("select authorised_value,lib from authorised_values where category=?");
217 $sth->execute($authorised_valuecode);
218 while (my ($authorised_value,$lib) = $sth->fetchrow) {
219 $notforloanstatus{$authorised_value} = $lib?$lib:$authorised_value;
222 for(my $i = 0 ; $i <= $#{$value} ; $i++)
225 @$value[$i] =~ s/\*/%/g;
226 # remove % at the beginning
227 @$value[$i] =~ s/^%//g;
228 @$value[$i] =~ s/(\.|\?|\:|\!|\'|,|\-|\"|\(|\)|\[|\]|\{|\}|\/)/ /g if @$operator[$i] eq "contains";
229 if(@$excluding[$i]) # NOT statements
232 if(@$operator[$i] eq "contains")
234 foreach my $word (split(/ /, @$value[$i])) # if operator is contains, splits the words in separate requests
236 # remove the "%" for small word (3 letters. (note : the >4 is due to the % at the end)
237 # warn "word : $word";
238 $word =~ s/%//g unless length($word)>4;
239 unless (C4::Context->stopwords->{uc($word)} or length($word)==1) { #it's NOT a stopword => use it. Otherwise, ignore
240 push @not_tags, @$tags[$i];
241 push @not_and_or, "or"; # as request is negated, finds "foo" or "bar" if final request is NOT "foo" and "bar"
242 push @not_operator, @$operator[$i];
243 push @not_value, $word;
249 push @not_tags, @$tags[$i];
250 push @not_and_or, "or"; # as request is negated, finds "foo" or "bar" if final request is NOT "foo" and "bar"
251 push @not_operator, @$operator[$i];
252 push @not_value, @$value[$i];
255 else # NORMAL statements
257 if(@$operator[$i] eq "contains") # if operator is contains, splits the words in separate requests
259 foreach my $word (split(/ /, @$value[$i]))
261 # remove the "%" for small word (3 letters. (note : the >4 is due to the % at the end)
262 # warn "word : $word";
263 $word =~ s/%//g unless length($word)>4;
264 unless (C4::Context->stopwords->{uc($word)} or length($word)==1) { #it's NOT a stopword => use it. Otherwise, ignore
265 push @normal_tags, @$tags[$i];
266 push @normal_and_or, "and"; # assumes "foo" and "bar" if "foo bar" is entered
267 push @normal_operator, @$operator[$i];
268 push @normal_value, $word;
274 push @normal_tags, @$tags[$i];
275 push @normal_and_or, @$and_or[$i];
276 push @normal_operator, @$operator[$i];
277 push @normal_value, @$value[$i];
282 # Finds the basic results without the NOT requests
283 my ($sql_tables, $sql_where1, $sql_where2) = create_request($dbh,\@normal_tags, \@normal_and_or, \@normal_operator, \@normal_value);
284 $sql_where1 .= $sqlstring;
285 $sql_tables .= $extratables;
286 $sql_where1 .= "and TO_DAYS( NOW( ) ) - TO_DAYS( biblio.timestamp ) <30" if $orderby =~ "biblio.timestamp";
289 $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");
290 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";
292 $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");
293 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";
297 my $subtitle; # Added by JF for Subtitles
299 # Processes the NOT if any and there are results
300 my ($not_sql_tables, $not_sql_where1, $not_sql_where2);
302 if( ($sth->rows) && $any_not ) # some results to tune up and some NOT statements
304 ($not_sql_tables, $not_sql_where1, $not_sql_where2) = create_request($dbh,\@not_tags, \@not_and_or, \@not_operator, \@not_value);
308 while (my ($bibid) = $sth->fetchrow) {
309 push @tmpresult,$bibid;
312 warn "NOT : select distinct m1.bibid from $not_sql_tables where $not_sql_where2 and ($not_sql_where1)";
313 if ($not_sql_where2) {
314 $sth_not = $dbh->prepare("select distinct m1.bibid from $not_sql_tables where $not_sql_where2 and ($not_sql_where1)");
316 $sth_not = $dbh->prepare("select distinct m1.bibid from $not_sql_tables where $not_sql_where1");
323 while(my $bibid = $sth_not->fetchrow()) {
324 $not_bibids{$bibid} = 1; # populates the hashtable with the bibids matching the NOT statement
327 foreach my $bibid (@tmpresult)
329 if(!$not_bibids{$bibid})
331 push @result, $bibid;
337 else # no NOT statements
339 while (my ($bibid) = $sth->fetchrow) {
344 # we have bibid list. Now, loads title and author from [offset] to [offset]+[length]
345 my $counter = $offset;
346 # HINT : biblionumber as bn is important. The hash is fills biblionumber with items.biblionumber.
347 # so if you dont' has an item, you get a not nice empty value.
348 $sth = $dbh->prepare("SELECT biblio.biblionumber as bn,biblioitems.*,biblio.*, marc_biblio.bibid,itemtypes.notforloan,itemtypes.description
349 FROM biblio, marc_biblio
350 LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
351 LEFT JOIN itemtypes on itemtypes.itemtype=biblioitems.itemtype
352 WHERE biblio.biblionumber = marc_biblio.biblionumber AND bibid = ?");
353 my $sth_subtitle = $dbh->prepare("SELECT subtitle FROM bibliosubtitle WHERE biblionumber=?"); # Added BY JF for Subtitles
354 my @finalresult = ();
358 my ($oldbibid, $oldauthor, $oldtitle);
360 if (C4::Context->preference('hidelostitem')) {
361 $sth_itemCN = $dbh->prepare("select items.* from items where biblionumber=? and (itemlost = 0 or itemlost is NULL)");
363 $sth_itemCN = $dbh->prepare("select items.* from items where biblionumber=?");
365 my $sth_issue = $dbh->prepare("select date_due,returndate from issues where itemnumber=?");
366 # parse all biblios between start & end.
367 while (($counter <= $#result) && ($counter <= ($offset + $length))) {
368 # search & parse all items & note itemcallnumber
369 $sth->execute($result[$counter]);
371 my $line = $sth->fetchrow_hashref;
372 my $biblionumber=$line->{bn};
373 # Return subtitles first ADDED BY JF
374 $sth_subtitle->execute($biblionumber);
375 my $subtitle_here.= $sth_subtitle->fetchrow." ";
377 $subtitle = $subtitle_here;
378 # warn "Here's the Biblionumber ".$biblionumber;
379 # warn "and here's the subtitle: ".$subtitle_here;
383 # $continue=0 unless $line->{bn};
384 # my $lastitemnumber;
385 $sth_itemCN->execute($biblionumber);
387 my $notforloan=1; # to see if there is at least 1 item that can be issued
388 while (my $item = $sth_itemCN->fetchrow_hashref) {
389 # parse the result, putting holdingbranch & itemcallnumber in separate array
390 # then all other fields in the main array
392 # search if item is on loan
394 $sth_issue->execute($item->{itemnumber});
395 while (my $loan = $sth_issue->fetchrow_hashref) {
396 if ($loan->{date_due} and !$loan->{returndate}) {
397 $date_due = $loan->{date_due};
402 $lineCN{holdingbranch} = $item->{holdingbranch};
403 $lineCN{itemcallnumber} = $item->{itemcallnumber};
404 $lineCN{location} = $item->{location};
405 $lineCN{date_due} = format_date($date_due);
406 $lineCN{notforloan} = $notforloanstatus{$line->{notforloan}} if ($line->{notforloan}); # setting not forloan if itemtype is not for loan
407 $lineCN{notforloan} = $notforloanstatus{$item->{notforloan}} if ($item->{notforloan}); # setting not forloan it this item is not for loan
408 $notforloan=0 unless ($item->{notforloan} or $item->{wthdrawn} or $item->{itemlost});
409 push @CNresults,\%lineCN;
412 # save the biblio in the final array, with item and item issue status
415 $newline{totitem} = $totalitems;
416 # if $totalitems == 0, check if it's being ordered.
417 if ($totalitems == 0) {
418 my $sth = $dbh->prepare("select count(*) from aqorders where biblionumber=? and datecancellationprinted is NULL");
419 $sth->execute($biblionumber);
420 my ($ordered) = $sth->fetchrow;
421 $newline{onorder} = 1 if $ordered;
423 $newline{biblionumber} = $biblionumber;
424 $newline{norequests} = 0;
425 $newline{norequests} = 1 if ($line->{notforloan}); # itemtype not issuable
426 $newline{norequests} = 1 if (!$line->{notforloan} && $notforloan); # itemtype issuable but all items not issuable for instance
427 $newline{subtitle} = $subtitle; # put the subtitle in ADDED BY JF
429 my @CNresults2= @CNresults;
430 $newline{CN} = \@CNresults2;
431 $newline{'even'} = 1 if $#finalresult % 2 == 0;
432 $newline{'odd'} = 1 if $#finalresult % 2 == 1;
433 $newline{'timestamp'} = format_date($newline{timestamp});
435 push @finalresult, \%newline;
439 my $nbresults = $#result+1;
440 return (\@finalresult, $nbresults);
443 # Creates the SQL Request
446 my ($dbh,$tags, $and_or, $operator, $value) = @_;
448 my $sql_tables; # will contain marc_subfield_table as m1,...
449 my $sql_where1; # will contain the "true" where
450 my $sql_where2 = "("; # will contain m1.bibid=m2.bibid
451 my $nb_active=0; # will contain the number of "active" entries. an entry is active if a value is provided.
452 my $nb_table=1; # will contain the number of table. ++ on each entry EXCEPT when an OR is provided.
454 my $maxloop=8; # the maximum number of words to avoid a too complex search.
455 $maxloop = @$value if @$value<$maxloop;
457 for(my $i=0; $i<=$maxloop;$i++) {
461 if (@$operator[$i] eq "start") {
462 $sql_tables .= "marc_subfield_table as m$nb_table,";
463 $sql_where1 .= "(m1.subfieldvalue like ".$dbh->quote("@$value[$i]%");
465 $sql_where1 .=" and concat(m1.tag,m1.subfieldcode) in (@$tags[$i])";
468 } elsif (@$operator[$i] eq "contains") {
469 $sql_tables .= "marc_word as m$nb_table,";
470 $sql_where1 .= "(m1.word like ".$dbh->quote("@$value[$i]");
472 $sql_where1 .=" and m1.tagsubfield in (@$tags[$i])";
476 $sql_tables .= "marc_subfield_table as m$nb_table,";
477 $sql_where1 .= "(m1.subfieldvalue @$operator[$i] ".$dbh->quote("@$value[$i]");
479 $sql_where1 .=" and concat(m1.tag,m1.subfieldcode) in (@$tags[$i])";
484 if (@$operator[$i] eq "start") {
486 $sql_tables .= "marc_subfield_table as m$nb_table,";
487 $sql_where1 .= "@$and_or[$i] (m$nb_table.subfieldvalue like ".$dbh->quote("@$value[$i]%");
489 $sql_where1 .=" and concat(m$nb_table.tag,m$nb_table.subfieldcode) in (@$tags[$i])";
492 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
493 } elsif (@$operator[$i] eq "contains") {
494 if (@$and_or[$i] eq 'and') {
496 $sql_tables .= "marc_word as m$nb_table,";
497 $sql_where1 .= "@$and_or[$i] (m$nb_table.word like ".$dbh->quote("@$value[$i]");
499 $sql_where1 .=" and m$nb_table.tagsubfield in(@$tags[$i])";
502 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
504 $sql_where1 .= "@$and_or[$i] (m$nb_table.word like ".$dbh->quote("@$value[$i]");
506 $sql_where1 .=" and m$nb_table.tagsubfield in (@$tags[$i])";
509 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
513 $sql_tables .= "marc_subfield_table as m$nb_table,";
514 $sql_where1 .= "@$and_or[$i] (m$nb_table.subfieldvalue @$operator[$i] ".$dbh->quote(@$value[$i]);
516 $sql_where1 .=" and concat(m$nb_table.tag,m$nb_table.subfieldcode) in (@$tags[$i])";
518 $sql_where2 .= "m1.bibid=m$nb_table.bibid and ";
525 if($sql_where2 ne "(") # some datas added to sql_where2, processing
527 $sql_where2 = substr($sql_where2, 0, (length($sql_where2)-5)); # deletes the trailing ' and '
530 else # no sql_where2 statement, deleting '('
534 chop $sql_tables; # deletes the trailing ','
535 return ($sql_tables, $sql_where1, $sql_where2);
539 my ($dbh, $bibid, $marcflavour) = @_;
540 my ($mintag, $maxtag);
541 if ($marcflavour eq "MARC21") {
544 } else { # assume unimarc if not marc21
549 my $sth=$dbh->prepare("SELECT subfieldvalue,tag FROM marc_subfield_table WHERE bibid=? AND tag BETWEEN ? AND ? ORDER BY tagorder");
551 $sth->execute($bibid,$mintag,$maxtag);
558 while (my $data=$sth->fetchrow_arrayref) {
559 my $value=$data->[0];
560 my $thistag=$data->[1];
564 if ($thistag ne $tag && $note ne "") {
565 $marcnote = {marcnote => $note,};
566 push @marcnotes, $marcnote;
570 if ($note ne $value) {
571 $note = $note." ".$value;
576 $marcnote = {marcnote => $note};
577 push @marcnotes, $marcnote; #load last tag into array
583 my $marcnotesarray=\@marcnotes;
584 return $marcnotesarray;
588 sub getMARCsubjects {
589 my ($dbh, $bibid, $marcflavour) = @_;
590 my ($mintag, $maxtag);
591 if ($marcflavour eq "MARC21") {
594 } else { # assume unimarc if not marc21
598 my $sth=$dbh->prepare("SELECT subfieldvalue,subfieldcode FROM marc_subfield_table WHERE bibid=? AND tag BETWEEN ? AND ? ORDER BY tagorder");
600 $sth->execute($bibid,$mintag,$maxtag);
607 while (my $data=$sth->fetchrow_arrayref) {
608 my $value = $data->[0];
609 my $subfield = $data->[1];
610 if ($subfield eq "a" && $value ne $subjct) {
611 $marcsubjct = {MARCSUBJCT => $value,};
612 push @marcsubjcts, $marcsubjct;
620 my $marcsubjctsarray=\@marcsubjcts;
621 return $marcsubjctsarray;
622 } #end getMARCsubjects
624 END { } # module clean-up code here (global destructor)
633 Koha Developement team <info@koha.org>