C4/Search.pm

   1 package C4::Search;
   2
   3 # Copyright 2000-2002 Katipo Communications
   4 #
   5 # This file is part of Koha.
   6 #
   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
  10 # version.
  11 #
  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.
  15 #
  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
  19
  20 use strict;
  21 require Exporter;
  22 use DBI;
  23 use C4::Context;
  24 use C4::Reserves2;
  25         # FIXME - C4::Search uses C4::Reserves2, which uses C4::Search.
  26         # So Perl complains that all of the functions here get redefined.
  27 use C4::Date;
  28 use C4::Biblio;
  29
  30 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
  31
  32 # set the version for version checking
  33 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
  34           shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
  35
  36 =head1 NAME
  37
  38 C4::Search - Functions for searching the Koha catalog and other databases
  39
  40 =head1 SYNOPSIS
  41
  42   use C4::Search;
  43
  44   my ($count, @results) = catalogsearch($env, $type, $search, $num, $offset);
  45
  46 =head1 DESCRIPTION
  47
  48 This module provides the searching facilities for the Koha catalog and
  49 other databases.
  50
  51 C<&catalogsearch> is a front end to all the other searches. Depending
  52 on what is passed to it, it calls the appropriate search function.
  53
  54 =head1 FUNCTIONS
  55
  56 =over 2
  57
  58 =cut
  59
  60 @ISA = qw(Exporter);
  61 @EXPORT = qw(
  62         &catalogsearch &KeywordSearch &CatSearch &subsearch
  63 );
  64 # make all your functions, whether exported or not;
  65
  66 =item catalogsearch
  67
  68   ($count, @results) = &catalogsearch($env, $type, $search, $num, $offset);
  69
  70 This is primarily a front-end to other, more specialized catalog
  71 search functions: if C<$search-E<gt>{itemnumber}> or
  72 C<$search-E<gt>{isbn}> is given, C<&catalogsearch> uses a precise
  73 C<&CatSearch>. If $search->{subject} is given, it runs a subject
  74 C<&CatSearch>. If C<$search-E<gt>{keyword}> is given, it runs a
  75 C<&KeywordSearch>. Otherwise, it runs a loose C<&CatSearch>.
  76
  77 If C<$env-E<gt>{itemcount}> is 1, then C<&catalogsearch> also counts
  78 the items for each result, and adds several keys:
  79
  80 =over 4
  81
  82 =item C<itemcount>
  83
  84 The total number of copies of this book.
  85
  86 =item C<locationhash>
  87
  88 This is a reference-to-hash; the keys are the names of branches where
  89 this book may be found, and the values are the number of copies at
  90 that branch.
  91
  92 =item C<location>
  93
  94 A descriptive string saying where the book is located, and how many
  95 copies there are, if greater than 1.
  96
  97 =item C<subject2>
  98
  99 The book's subject, with spaces replaced with C<%20>, presumably for
 100 HTML.
 101
 102 =back
 103
 104 =cut
 105 #'
 106 sub catalogsearch {
 107         my ($env,$type,$search,$num,$offset)=@_;
 108         my $dbh = C4::Context->dbh;
 109         #  foreach my $key (%$search){
 110         #    $search->{$key}=$dbh->quote($search->{$key});
 111         #  }
 112         my ($count,@results);
 113         if ($search->{'itemnumber'} ne '' || $search->{'isbn'} ne ''){
 114                 print STDERR "Doing a precise search\n";
 115                 ($count,@results)=CatSearch($env,'precise',$search,$num,$offset);
 116         } elsif ($search->{'subject'} ne ''){
 117                 ($count,@results)=CatSearch($env,'subject',$search,$num,$offset);
 118         } elsif ($search->{'keyword'} ne ''){
 119                 ($count,@results)=&KeywordSearch($env,'keyword',$search,$num,$offset);
 120         } else {
 121                 ($count,@results)=CatSearch($env,'loose',$search,$num,$offset);
 122
 123         }
 124         if ($env->{itemcount} eq '1') {
 125                 foreach my $data (@results){
 126                         my ($counts) = itemcount2($env, $data->{'biblionumber'}, 'intra');
 127                         my $subject2=$data->{'subject'};
 128                         $subject2=~ s/ /%20/g;
 129                         $data->{'itemcount'}=$counts->{'total'};
 130                         my $totalitemcounts=0;
 131                         foreach my $key (keys %$counts){
 132                                 if ($key ne 'total'){   # FIXME - Should ignore 'order', too.
 133                                         #$data->{'location'}.="$key $counts->{$key} ";
 134                                         $totalitemcounts+=$counts->{$key};
 135                                         $data->{'locationhash'}->{$key}=$counts->{$key};
 136                                 }
 137                         }
 138                         my $locationtext='';
 139                         my $locationtextonly='';
 140                         my $notavailabletext='';
 141                         foreach (sort keys %{$data->{'locationhash'}}) {
 142                                 if ($_ eq 'notavailable') {
 143                                         $notavailabletext="Not available";
 144                                         my $c=$data->{'locationhash'}->{$_};
 145                                         $data->{'not-available-p'}=$totalitemcounts;
 146                                         if ($totalitemcounts>1) {
 147                                         $notavailabletext.=" ($c)";
 148                                         $data->{'not-available-plural-p'}=1;
 149                                         }
 150                                 } else {
 151                                         $locationtext.="$_";
 152                                         my $c=$data->{'locationhash'}->{$_};
 153                                         if ($_ eq 'Item Lost') {
 154                                         $data->{'lost-p'}=$totalitemcounts;
 155                                         $data->{'lost-plural-p'}=1
 156                                                         if $totalitemcounts > 1;
 157                                         } elsif ($_ eq 'Withdrawn') {
 158                                         $data->{'withdrawn-p'}=$totalitemcounts;
 159                                         $data->{'withdrawn-plural-p'}=1
 160                                                         if $totalitemcounts > 1;
 161                                         } elsif ($_ eq 'On Loan') {
 162                                         $data->{'on-loan-p'}=$totalitemcounts;
 163                                         $data->{'on-loan-plural-p'}=1
 164                                                         if $totalitemcounts > 1;
 165                                         } else {
 166                                         $locationtextonly.=$_;
 167                                         $locationtextonly.=" ($c), "
 168                                                         if $totalitemcounts>1;
 169                                         }
 170                                         if ($totalitemcounts>1) {
 171                                         $locationtext.=" ($c), ";
 172                                         }
 173                                 }
 174                         }
 175                         if ($notavailabletext) {
 176                                 $locationtext.=$notavailabletext;
 177                         } else {
 178                                 $locationtext=~s/, $//;
 179                         }
 180                         $data->{'location'}=$locationtext;
 181                         $data->{'location-only'}=$locationtextonly;
 182                         $data->{'subject2'}=$subject2;
 183                         $data->{'use-location-flags-p'}=1; # XXX
 184                 }
 185         }
 186         return ($count,@results);
 187 }
 188
 189 =item KeywordSearch
 190
 191   $search = { "keyword" => "One or more keywords",
 192               "class"   => "VID|CD",    # Limit search to fiction and CDs
 193               "dewey"   => "813",
 194          };
 195   ($count, @results) = &KeywordSearch($env, $type, $search, $num, $offset);
 196
 197 C<&KeywordSearch> searches the catalog by keyword: given a string
 198 (C<$search-E<gt>{"keyword"}> consisting of a space-separated list of
 199 keywords, it looks for books that contain any of those keywords in any
 200 of a number of places.
 201
 202 C<&KeywordSearch> looks for keywords in the book title (and subtitle),
 203 series name, notes (both C<biblio.notes> and C<biblioitems.notes>),
 204 and subjects.
 205
 206 C<$search-E<gt>{"class"}> can be set to a C<|> (pipe)-separated list of
 207 item class codes (e.g., "F" for fiction, "JNF" for junior nonfiction,
 208 etc.). In this case, the search will be restricted to just those
 209 classes.
 210
 211 If C<$search-E<gt>{"class"}> is not specified, you may specify
 212 C<$search-E<gt>{"dewey"}>. This will restrict the search to that
 213 particular Dewey Decimal Classification category. Setting
 214 C<$search-E<gt>{"dewey"}> to "513" will return books about arithmetic,
 215 whereas setting it to "5" will return all books with Dewey code 5I<xx>
 216 (Science and Mathematics).
 217
 218 C<$env> and C<$type> are ignored.
 219
 220 C<$offset> and C<$num> specify the subset of results to return.
 221 C<$num> specifies the number of results to return, and C<$offset> is
 222 the number of the first result. Thus, setting C<$offset> to 100 and
 223 C<$num> to 5 will return results 100 through 104 inclusive.
 224
 225 =cut
 226 #'
 227 sub KeywordSearch {
 228   my ($env,$type,$search,$num,$offset)=@_;
 229   my $dbh = C4::Context->dbh;
 230   $search->{'keyword'}=~ s/ +$//;
 231   my @key=split(' ',$search->{'keyword'});
 232                 # FIXME - Naive users might enter comma-separated
 233                 # words, e.g., "training, animal". Ought to cope with
 234                 # this.
 235   my $count=@key;
 236   my $i=1;
 237   my %biblionumbers;            # Set of biblionumbers returned by the
 238                                 # various searches.
 239
 240   # FIXME - Ought to filter the stopwords out of the list of keywords.
 241   #     @key = map { !defined($stopwords{$_}) } @key;
 242
 243   # FIXME - The way this code is currently set up, it looks for all of
 244   # the keywords first in (title, notes, seriestitle), then in the
 245   # subtitle, then in the subject. Thus, if you look for keywords
 246   # "science fiction", this search won't find a book with
 247   #     title    = "How to write fiction"
 248   #     subtitle = "A science-based approach"
 249   # Is this the desired effect? If not, then the first SQL query
 250   # should look in the biblio, subtitle, and subject tables all at
 251   # once. The way the first query is built can accomodate this easily.
 252
 253   # Look for keywords in table 'biblio'.
 254
 255   # Build an SQL query that finds each of the keywords in any of the
 256   # title, biblio.notes, or seriestitle. To do this, we'll build up an
 257   # array of clauses, one for each keyword.
 258   my $query;                    # The SQL query
 259   my @clauses = ();             # The search clauses
 260   my @bind = ();                # The term bindings
 261
 262   $query = <<EOT;               # Beginning of the query
 263         SELECT  biblionumber
 264         FROM    biblio
 265         WHERE
 266 EOT
 267   foreach my $keyword (@key)
 268   {
 269     my @subclauses = ();        # Subclauses, one for each field we're
 270                                 # searching on
 271
 272     # For each field we're searching on, create a subclause that'll
 273     # match the current keyword in the current field.
 274     foreach my $field (qw(title notes seriestitle author))
 275     {
 276       push @subclauses,
 277         "$field LIKE ? OR $field LIKE ?";
 278           push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
 279     }
 280     # (Yes, this could have been done as
 281     #   @subclauses = map {...} qw(field1 field2 ...)
 282     # )but I think this way is more readable.
 283
 284     # Construct the current clause by joining the subclauses.
 285     push @clauses, "(" . join(")\n\tOR (", @subclauses) . ")";
 286   }
 287   # Now join all of the clauses together and append to the query.
 288   $query .= "(" . join(")\nAND (", @clauses) . ")";
 289
 290   # FIXME - Perhaps use $sth->bind_columns() ? Documented as the most
 291   # efficient way to fetch data.
 292   my $sth=$dbh->prepare($query);
 293   $sth->execute(@bind);
 294   while (my @res = $sth->fetchrow_array) {
 295     for (@res)
 296     {
 297         $biblionumbers{$_} = 1;         # Add these results to the set
 298     }
 299   }
 300   $sth->finish;
 301
 302   # Now look for keywords in the 'bibliosubtitle' table.
 303
 304   # Again, we build a list of clauses from the keywords.
 305   @clauses = ();
 306   @bind = ();
 307   $query = "SELECT biblionumber FROM bibliosubtitle WHERE ";
 308   foreach my $keyword (@key)
 309   {
 310     push @clauses,
 311         "subtitle LIKE ? OR subtitle like ?";
 312         push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
 313   }
 314   $query .= "(" . join(") AND (", @clauses) . ")";
 315
 316   $sth=$dbh->prepare($query);
 317   $sth->execute(@bind);
 318   while (my @res = $sth->fetchrow_array) {
 319     for (@res)
 320     {
 321         $biblionumbers{$_} = 1;         # Add these results to the set
 322     }
 323   }
 324   $sth->finish;
 325
 326   # Look for the keywords in the notes for individual items
 327   # ('biblioitems.notes')
 328
 329   # Again, we build a list of clauses from the keywords.
 330   @clauses = ();
 331   @bind = ();
 332   $query = "SELECT biblionumber FROM biblioitems WHERE ";
 333   foreach my $keyword (@key)
 334   {
 335     push @clauses,
 336         "notes LIKE ? OR notes like ?";
 337         push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
 338   }
 339   $query .= "(" . join(") AND (", @clauses) . ")";
 340
 341   $sth=$dbh->prepare($query);
 342   $sth->execute(@bind);
 343   while (my @res = $sth->fetchrow_array) {
 344     for (@res)
 345     {
 346         $biblionumbers{$_} = 1;         # Add these results to the set
 347     }
 348   }
 349   $sth->finish;
 350
 351   # Look for keywords in the 'bibliosubject' table.
 352
 353   # FIXME - The other queries look for words in the desired field that
 354   # begin with the individual keywords the user entered. This one
 355   # searches for the literal string the user entered. Is this the
 356   # desired effect?
 357   # Note in particular that spaces are retained: if the user typed
 358   #     science  fiction
 359   # (with two spaces), this won't find the subject "science fiction"
 360   # (one space). Likewise, a search for "%" will return absolutely
 361   # everything.
 362   # If this isn't the desired effect, see the previous searches for
 363   # how to do it.
 364
 365   $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
 366   like ? group by biblionumber");
 367   $sth->execute("%$search->{'keyword'}%");
 368
 369   while (my @res = $sth->fetchrow_array) {
 370     for (@res)
 371     {
 372         $biblionumbers{$_} = 1;         # Add these results to the set
 373     }
 374   }
 375   $sth->finish;
 376
 377   my $i2=0;
 378   my $i3=0;
 379   my $i4=0;
 380
 381   my @res2;
 382   my @res = keys %biblionumbers;
 383   $count=@res;
 384
 385   $i=0;
 386 #  print "count $count";
 387   if ($search->{'class'} ne ''){
 388     while ($i2 <$count){
 389       my $query="select * from biblio,biblioitems where
 390       biblio.biblionumber=? and
 391       biblio.biblionumber=biblioitems.biblionumber ";
 392       my @bind = ($res[$i2]);
 393       if ($search->{'class'} ne ''){    # FIXME - Redundant
 394       my @temp=split(/\|/,$search->{'class'});
 395       my $count=@temp;
 396       $query.= "and ( itemtype=?";
 397       push(@bind,$temp[0]);
 398       for (my $i=1;$i<$count;$i++){
 399         $query.=" or itemtype=?";
 400         push(@bind,$temp[$i]);
 401       }
 402       $query.=")";
 403       }
 404        my $sth=$dbh->prepare($query);
 405        #    print $query;
 406        $sth->execute(@bind);
 407        if (my $data2=$sth->fetchrow_hashref){
 408          my $dewey= $data2->{'dewey'};
 409          my $subclass=$data2->{'subclass'};
 410          # FIXME - This next bit is bogus, because it assumes that the
 411          # Dewey code is a floating-point number. It isn't. It's
 412          # actually a string that mainly consists of numbers. In
 413          # particular, "4" is not a valid Dewey code, although "004"
 414          # is ("Data processing; Computer science"). Likewise, zeros
 415          # after the decimal are significant ("575" is not the same as
 416          # "575.0"; the latter is more specific). And "000" is a
 417          # perfectly good Dewey code ("General works; computer
 418          # science") and should not be interpreted to mean "this
 419          # database entry does not have a Dewey code". That's what
 420          # NULL is for.
 421          $dewey=~s/\.*0*$//;
 422          ($dewey == 0) && ($dewey='');
 423          ($dewey) && ($dewey.=" $subclass") ;
 424           $sth->finish;
 425           my $end=$offset +$num;
 426           if ($i4 <= $offset){
 427             $i4++;
 428           }
 429 #         print $i4;
 430           if ($i4 <=$end && $i4 > $offset){
 431             $data2->{'dewey'}=$dewey;
 432             $res2[$i3]=$data2;
 433
 434 #           $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
 435             $i3++;
 436             $i4++;
 437 #           print "in here $i3<br>";
 438           } else {
 439 #           print $end;
 440           }
 441           $i++;
 442         }
 443      $i2++;
 444      }
 445      $count=$i;
 446
 447    } else {
 448   # $search->{'class'} was not specified
 449
 450   # FIXME - This is bogus: it makes a separate query for each
 451   # biblioitem, and returns results in apparently random order. It'd
 452   # be much better to combine all of the previous queries into one big
 453   # one (building it up a little at a time, of course), and have that
 454   # big query select all of the desired fields, instead of just
 455   # 'biblionumber'.
 456
 457   while ($i2 < $num && $i2 < $count){
 458     my $query="select * from biblio,biblioitems where
 459     biblio.biblionumber=? and
 460     biblio.biblionumber=biblioitems.biblionumber ";
 461     my @bind=($res[$i2+$offset]);
 462
 463     if ($search->{'dewey'} ne ''){
 464       $query.= "and (dewey like ?)";
 465       push(@bind,"$search->{'dewey'}%");
 466     }
 467
 468     my $sth=$dbh->prepare($query);
 469 #    print $query;
 470     $sth->execute(@bind);
 471     if (my $data2=$sth->fetchrow_hashref){
 472         my $dewey= $data2->{'dewey'};
 473         my $subclass=$data2->{'subclass'};
 474         $dewey=~s/\.*0*$//;
 475         ($dewey == 0) && ($dewey='');
 476         ($dewey) && ($dewey.=" $subclass") ;
 477         $sth->finish;
 478         $data2->{'dewey'}=$dewey;
 479
 480         $res2[$i]=$data2;
 481 #       $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
 482         $i++;
 483     }
 484     $i2++;
 485
 486   }
 487   }
 488
 489   #$count=$i;
 490   return($count,@res2);
 491 }
 492
 493 =item CatSearch
 494
 495   ($count, @results) = &CatSearch($env, $type, $search, $num, $offset);
 496
 497 C<&CatSearch> searches the Koha catalog. It returns a list whose first
 498 element is the number of returned results, and whose subsequent
 499 elements are the results themselves.
 500
 501 Each returned element is a reference-to-hash. Most of the keys are
 502 simply the fields from the C<biblio> table in the Koha database, but
 503 the following keys may also be present:
 504
 505 =over 4
 506
 507 =item C<illustrator>
 508
 509 The book's illustrator.
 510
 511 =item C<publisher>
 512
 513 The publisher.
 514
 515 =back
 516
 517 C<$env> is ignored.
 518
 519 C<$type> may be C<subject>, C<loose>, or C<precise>. This controls the
 520 high-level behavior of C<&CatSearch>, as described below.
 521
 522 In many cases, the description below says that a certain field in the
 523 database must match the search string. In these cases, it means that
 524 the beginning of some word in the field must match the search string.
 525 Thus, an author search for "sm" will return books whose author is
 526 "John Smith" or "Mike Smalls", but not "Paul Grossman", since the "sm"
 527 does not occur at the beginning of a word.
 528
 529 Note that within each search mode, the criteria are and-ed together.
 530 That is, if you perform a loose search on the author "Jerome" and the
 531 title "Boat", the search will only return books by Jerome containing
 532 "Boat" in the title.
 533
 534 It is not possible to cross modes, e.g., set the author to "Asimov"
 535 and the subject to "Math" in hopes of finding books on math by Asimov.
 536
 537 =head2 Loose search
 538
 539 If C<$type> is set to C<loose>, the following search criteria may be
 540 used:
 541
 542 =over 4
 543
 544 =item C<$search-E<gt>{author}>
 545
 546 The search string is a space-separated list of words. Each word must
 547 match either the C<author> or C<additionalauthors> field.
 548
 549 =item C<$search-E<gt>{title}>
 550
 551 Each word in the search string must match the book title. If no author
 552 is specified, the book subtitle will also be searched.
 553
 554 =item C<$search-E<gt>{abstract}>
 555
 556 Searches for the given search string in the book's abstract.
 557
 558 =item C<$search-E<gt>{'date-before'}>
 559
 560 Searches for books whose copyright date matches the search string.
 561 That is, setting C<$search-E<gt>{'date-before'}> to "1985" will find
 562 books written in 1985, and setting it to "198" will find books written
 563 between 1980 and 1989.
 564
 565 =item C<$search-E<gt>{title}>
 566
 567 Searches by title are also affected by the value of
 568 C<$search-E<gt>{"ttype"}>; if it is set to C<exact>, then the book
 569 title, (one of) the series titleZ<>(s), or (one of) the unititleZ<>(s) must
 570 match the search string exactly (the subtitle is not searched).
 571
 572 If C<$search-E<gt>{"ttype"}> is set to anything other than C<exact>,
 573 each word in the search string must match the title, subtitle,
 574 unititle, or series title.
 575
 576 =item C<$search-E<gt>{class}>
 577
 578 Restricts the search to certain item classes. The value of
 579 C<$search-E<gt>{"class"}> is a | (pipe)-separated list of item types.
 580 Thus, setting it to "F" restricts the search to fiction, and setting
 581 it to "CD|CAS" will only look in compact disks and cassettes.
 582
 583 =item C<$search-E<gt>{dewey}>
 584
 585 Searches for books whose Dewey Decimal Classification code matches the
 586 search string. That is, setting C<$search-E<gt>{"dewey"}> to "5" will
 587 search for all books in 5I<xx> (Science and mathematics), setting it
 588 to "54" will search for all books in 54I<x> (Chemistry), and setting
 589 it to "546" will search for books on inorganic chemistry.
 590
 591 =item C<$search-E<gt>{publisher}>
 592
 593 Searches for books whose publisher contains the search string (unlike
 594 other search criteria, C<$search-E<gt>{publisher}> is a string, not a
 595 set of words.
 596
 597 =back
 598
 599 =head2 Subject search
 600
 601 If C<$type> is set to C<subject>, the following search criterion may
 602 be used:
 603
 604 =over 4
 605
 606 =item C<$search-E<gt>{subject}>
 607
 608 The search string is a space-separated list of words, each of which
 609 must match the book's subject.
 610
 611 Special case: if C<$search-E<gt>{subject}> is set to C<nz>,
 612 C<&CatSearch> will search for books whose subject is "New Zealand".
 613 However, setting C<$search-E<gt>{subject}> to C<"nz football"> will
 614 search for books on "nz" and "football", not books on "New Zealand"
 615 and "football".
 616
 617 =back
 618
 619 =head2 Precise search
 620
 621 If C<$type> is set to C<precise>, the following search criteria may be
 622 used:
 623
 624 =over 4
 625
 626 =item C<$search-E<gt>{item}>
 627
 628 Searches for books whose barcode exactly matches the search string.
 629
 630 =item C<$search-E<gt>{isbn}>
 631
 632 Searches for books whose ISBN exactly matches the search string.
 633
 634 =back
 635
 636 For a loose search, if an author was specified, the results are
 637 ordered by author and title. If no author was specified, the results
 638 are ordered by title.
 639
 640 For other (non-loose) searches, if a subject was specified, the
 641 results are ordered alphabetically by subject.
 642
 643 In all other cases (e.g., loose search by keyword), the results are
 644 not ordered.
 645
 646 =cut
 647 #'
 648 sub CatSearch  {
 649         my ($env,$type,$search,$num,$offset)=@_;
 650         my $dbh = C4::Context->dbh;
 651         my $query = '';
 652         my @bind = ();
 653         my @results;
 654
 655         my $title = lc($search->{'title'});
 656
 657         if ($type eq 'loose') {
 658                 if ($search->{'author'} ne ''){
 659                         my @key=split(' ',$search->{'author'});
 660                         my $count=@key;
 661                         my $i=1;
 662                         $query="select *,biblio.author,biblio.biblionumber from
 663                                                         biblio
 664                                                         left join additionalauthors
 665                                                         on additionalauthors.biblionumber =biblio.biblionumber
 666                                                         where
 667                                                         ((biblio.author like ? or biblio.author like ? or
 668                                                         additionalauthors.author like ? or additionalauthors.author
 669                                                         like ?
 670                                                                 )";
 671                         @bind=("$key[0]%","% $key[0]%","$key[0]%","% $key[0]%");
 672                         while ($i < $count){
 673                                         $query .= " and (
 674                                                                         biblio.author like ? or biblio.author like ? or
 675                                                                         additionalauthors.author like ? or additionalauthors.author like ?
 676                                                                         )";
 677                                         push(@bind,"$key[$i]%","% $key[$i]%","$key[$i]%","% $key[$i]%");
 678                                 $i++;
 679                         }
 680                         $query .= ")";
 681                         if ($search->{'title'} ne ''){
 682                                 my @key=split(' ',$search->{'title'});
 683                                 my $count=@key;
 684                                 my $i=0;
 685                                 $query.= " and (((title like ? or title like ?)";
 686                                 push(@bind,"$key[0]%","% $key[0]%");
 687                                 while ($i<$count){
 688                                         $query .= " and (title like ? or title like ?)";
 689                                         push(@bind,"$key[$i]%","% $key[$i]%");
 690                                         $i++;
 691                                 }
 692                                 $query.=") or ((seriestitle like ? or seriestitle like ?)";
 693                                 push(@bind,"$key[0]%","% $key[0]%");
 694                                 for ($i=1;$i<$count;$i++){
 695                                         $query.=" and (seriestitle like ? or seriestitle like ?)";
 696                                         push(@bind,"$key[$i]%","% $key[$i]%");
 697                                         }
 698                                 $query.=") or ((unititle like ? or unititle like ?)";
 699                                 push(@bind,"$key[0]%","% $key[0]%");
 700                                 for ($i=1;$i<$count;$i++){
 701                                         $query.=" and (unititle like ? or unititle like ?)";
 702                                         push(@bind,"$key[$i]%","% $key[$i]%");
 703                                         }
 704                                 $query .= "))";
 705                         }
 706                         if ($search->{'abstract'} ne ''){
 707                                 $query.= " and (abstract like ?)";
 708                                 push(@bind,"%$search->{'abstract'}%");
 709                         }
 710                         if ($search->{'date-before'} ne ''){
 711                                 $query.= " and (copyrightdate like ?)";
 712                                 push(@bind,"%$search->{'date-before'}%");
 713                         }
 714                         $query.=" group by biblio.biblionumber";
 715                 } else {
 716                         if ($search->{'title'} ne '') {
 717                                 if ($search->{'ttype'} eq 'exact'){
 718                                         $query="select * from biblio
 719                                         where
 720                                         (biblio.title=? or (biblio.unititle = ?
 721                                         or biblio.unititle like ? or
 722                                         biblio.unititle like ? or
 723                                         biblio.unititle like ?) or
 724                                         (biblio.seriestitle = ? or
 725                                         biblio.seriestitle like ? or
 726                                         biblio.seriestitle like ? or
 727                                         biblio.seriestitle like ?)
 728                                         )";
 729                                         @bind=($search->{'title'},$search->{'title'},"$search->{'title'} |%","%| $search->{'title'} |%","%| $search->{'title'}",$search->{'title'},"$search->{'title'} |%","%| $search->{'title'} |%","%| $search->{'title'}");
 730                                 } else {
 731                                         my @key=split(' ',$search->{'title'});
 732                                         my $count=@key;
 733                                         my $i=1;
 734                                         $query="select biblio.biblionumber,author,title,unititle,notes,abstract,serial,seriestitle,copyrightdate,timestamp,subtitle from biblio
 735                                         left join bibliosubtitle on
 736                                         biblio.biblionumber=bibliosubtitle.biblionumber
 737                                         where
 738                                         (((title like ? or title like ?)";
 739                                         @bind=("$key[0]%","% $key[0]%");
 740                                         while ($i<$count){
 741                                                 $query .= " and (title like ? or title like ?)";
 742                                                 push(@bind,"$key[$i]%","% $key[$i]%");
 743                                                 $i++;
 744                                         }
 745                                         $query.=") or ((subtitle like ? or subtitle like ?)";
 746                                         push(@bind,"$key[0]%","% $key[0]%");
 747                                         for ($i=1;$i<$count;$i++){
 748                                                 $query.=" and (subtitle like ? or subtitle like ?)";
 749                                                 push(@bind,"$key[$i]%","% $key[$i]%");
 750                                         }
 751                                         $query.=") or ((seriestitle like ? or seriestitle like ?)";
 752                                         push(@bind,"$key[0]%","% $key[0]%");
 753                                         for ($i=1;$i<$count;$i++){
 754                                                 $query.=" and (seriestitle like ? or seriestitle like ?)";
 755                                                 push(@bind,"$key[$i]%","% $key[$i]%");
 756                                         }
 757                                         $query.=") or ((unititle like ? or unititle like ?)";
 758                                         push(@bind,"$key[0]%","% $key[0]%");
 759                                         for ($i=1;$i<$count;$i++){
 760                                                 $query.=" and (unititle like ? or unititle like ?)";
 761                                                 push(@bind,"$key[$i]%","% $key[$i]%");
 762                                         }
 763                                         $query .= "))";
 764                                 }
 765                                 if ($search->{'abstract'} ne ''){
 766                                         $query.= " and (abstract like ?)";
 767                                         push(@bind,"%$search->{'abstract'}%");
 768                                 }
 769                                 if ($search->{'date-before'} ne ''){
 770                                         $query.= " and (copyrightdate like ?)";
 771                                         push(@bind,"%$search->{'date-before'}%");
 772                                 }
 773                         } elsif ($search->{'class'} ne ''){
 774                                 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
 775                                 my @temp=split(/\|/,$search->{'class'});
 776                                 my $count=@temp;
 777                                 $query.= " and ( itemtype= ?)";
 778                                 @bind=($temp[0]);
 779                                 for (my $i=1;$i<$count;$i++){
 780                                         $query.=" or itemtype=?";
 781                                         push(@bind,$temp[$i]);
 782                                 }
 783                                 $query.=")";
 784                                 if ($search->{'illustrator'} ne ''){
 785                                         $query.=" and illus like ?";
 786                                         push(@bind,"%".$search->{'illustrator'}."%");
 787                                 }
 788                                 if ($search->{'dewey'} ne ''){
 789                                         $query.=" and biblioitems.dewey like ?";
 790                                         push(@bind,"$search->{'dewey'}%");
 791                                 }
 792                         } elsif ($search->{'dewey'} ne ''){
 793                                 $query="select * from biblioitems,biblio
 794                                 where biblio.biblionumber=biblioitems.biblionumber
 795                                 and biblioitems.dewey like ?";
 796                                 @bind=("$search->{'dewey'}%");
 797                         } elsif ($search->{'illustrator'} ne '') {
 798                                         $query="select * from biblioitems,biblio
 799                                 where biblio.biblionumber=biblioitems.biblionumber
 800                                 and biblioitems.illus like ?";
 801                                         @bind=("%".$search->{'illustrator'}."%");
 802                         } elsif ($search->{'publisher'} ne ''){
 803                                 $query = "Select * from biblio,biblioitems where biblio.biblionumber
 804                                 =biblioitems.biblionumber and (publishercode like ?)";
 805                                 @bind=("%$search->{'publisher'}%");
 806                         } elsif ($search->{'abstract'} ne ''){
 807                                 $query = "Select * from biblio where abstract like ?";
 808                                 @bind=("%$search->{'abstract'}%");
 809                         } elsif ($search->{'date-before'} ne ''){
 810                                 $query = "Select * from biblio where copyrightdate like ?";
 811                                 @bind=("%$search->{'date-before'}%");
 812                         }
 813                         $query .=" group by biblio.biblionumber";
 814                 }
 815         }
 816         if ($type eq 'subject'){
 817                 my @key=split(' ',$search->{'subject'});
 818                 my $count=@key;
 819                 my $i=1;
 820                 $query="select * from bibliosubject, biblioitems where
 821 (bibliosubject.biblionumber = biblioitems.biblionumber) and ( subject like ? or subject like ? or subject like ?)";
 822                 @bind=("$key[0]%","% $key[0]%","%($key[0])%");
 823                 while ($i<$count){
 824                         $query.=" and (subject like ? or subject like ? or subject like ?)";
 825                         push(@bind,"$key[$i]%","% $key[$i]%","%($key[$i])%");
 826                         $i++;
 827                 }
 828
 829                 # FIXME - Wouldn't it be better to fix the database so that if a
 830                 # book has a subject "NZ", then it also gets added the subject
 831                 # "New Zealand"?
 832                 # This can also be generalized by adding a table of subject
 833                 # synonyms to the database: just declare "NZ" to be a synonym for
 834                 # "New Zealand", "SF" a synonym for both "Science fiction" and
 835                 # "Fantastic fiction", etc.
 836
 837                 if (lc($search->{'subject'}) eq 'nz'){
 838                         $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
 839                         or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
 840                 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
 841                         $query=~ s/ nz/ NEW ZEALAND/ig;
 842                         $query=~ s/nz /NEW ZEALAND /ig;
 843                         $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
 844                 }
 845         }
 846         if ($type eq 'precise'){
 847                 if ($search->{'itemnumber'} ne ''){
 848                         $query="select * from items,biblio ";
 849                         my $search2=uc $search->{'itemnumber'};
 850                         $query=$query." where
 851                         items.biblionumber=biblio.biblionumber
 852                         and barcode=?";
 853                         @bind=($search2);
 854                                         # FIXME - .= <<EOT;
 855                 }
 856                 if ($search->{'isbn'} ne ''){
 857                         my $search2=uc $search->{'isbn'};
 858                         my $sth1=$dbh->prepare("select * from biblioitems where isbn=?");
 859                         $sth1->execute($search2);
 860                         my $i2=0;
 861                         while (my $data=$sth1->fetchrow_hashref) {
 862                                 my $sth=$dbh->prepare("select * from biblioitems,biblio where
 863                                         biblio.biblionumber = ?
 864                                         and biblioitems.biblionumber = biblio.biblionumber");
 865                                 $sth->execute($data->{'biblionumber'});
 866                                 # FIXME - There's already a $data in this scope.
 867                                 my $data=$sth->fetchrow_hashref;
 868                                 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
 869                                 # FIXME - The following assumes that the Dewey code is a
 870                                 # floating-point number. It isn't: it's a string.
 871                                 $dewey=~s/\.*0*$//;
 872                                 ($dewey == 0) && ($dewey='');
 873                                 ($dewey) && ($dewey.=" $subclass");
 874                                 $data->{'dewey'}=$dewey;
 875                                 $results[$i2]=$data;
 876                         #           $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
 877                                 $i2++;
 878                                 $sth->finish;
 879                         }
 880                         $sth1->finish;
 881                 }
 882         }
 883         if ($type ne 'precise' && $type ne 'subject'){
 884                 if ($search->{'author'} ne ''){
 885                         $query .= " order by biblio.author,title";
 886                 } else {
 887                         $query .= " order by title";
 888                 }
 889         } else {
 890                 if ($type eq 'subject'){
 891                         $query .= " group by subject ";
 892                 }
 893         }
 894         my $sth=$dbh->prepare($query);
 895         $sth->execute(@bind);
 896         my $count=1;
 897         my $i=0;
 898         my $limit= $num+$offset;
 899         while (my $data=$sth->fetchrow_hashref){
 900                 my $query="select classification,dewey,subclass,publishercode from biblioitems where biblionumber=?";
 901                 my @bind=($data->{'biblionumber'});
 902                 if ($search->{'class'} ne ''){
 903                         my @temp=split(/\|/,$search->{'class'});
 904                         my $count=@temp;
 905                         $query.= " and ( itemtype= ?";
 906                         push(@bind,$temp[0]);
 907                         for (my $i=1;$i<$count;$i++){
 908                         $query.=" or itemtype=?";
 909                         push(@bind,$temp[$i]);
 910                         }
 911                         $query.=")";
 912                 }
 913                 if ($search->{'dewey'} ne ''){
 914                         $query.=" and dewey=? ";
 915                         push(@bind,$search->{'dewey'});
 916                 }
 917                 if ($search->{'illustrator'} ne ''){
 918                         $query.=" and illus like ?";
 919                         push(@bind,"%$search->{'illustrator'}%");
 920                 }
 921                 if ($search->{'publisher'} ne ''){
 922                         $query.= " and (publishercode like ?)";
 923                         push(@bind,"%$search->{'publisher'}%");
 924                 }
 925                 my $sti=$dbh->prepare($query);
 926                 $sti->execute(@bind);
 927                 my $classification;
 928                 my $dewey;
 929                 my $subclass;
 930                 my $true=0;
 931                 my $publishercode;
 932                 my $bibitemdata;
 933                 if ($bibitemdata = $sti->fetchrow_hashref()){
 934                         $true=1;
 935                         $classification=$bibitemdata->{'classification'};
 936                         $dewey=$bibitemdata->{'dewey'};
 937                         $subclass=$bibitemdata->{'subclass'};
 938                         $publishercode=$bibitemdata->{'publishercode'};
 939                 }
 940                 #  print STDERR "$dewey $subclass $publishercode\n";
 941                 # FIXME - The Dewey code is a string, not a number.
 942                 $dewey=~s/\.*0*$//;
 943                 ($dewey == 0) && ($dewey='');
 944                 ($dewey) && ($dewey.=" $subclass");
 945                 $data->{'classification'}=$classification;
 946                 $data->{'dewey'}=$dewey;
 947                 $data->{'publishercode'}=$publishercode;
 948                 $sti->finish;
 949                 if ($true == 1){
 950                         if ($count > $offset && $count <= $limit){
 951                                 $results[$i]=$data;
 952                                 $i++;
 953                         }
 954                         $count++;
 955                 }
 956         }
 957         $sth->finish;
 958         $count--;
 959         return($count,@results);
 960 }
 961
 962 =item subsearch
 963
 964   @results = &subsearch($env, $subject);
 965
 966 Searches for books that have a subject that exactly matches
 967 C<$subject>.
 968
 969 C<&subsearch> returns an array of results. Each element of this array
 970 is a string, containing the book's title, author, and biblionumber,
 971 separated by tabs.
 972
 973 C<$env> is ignored.
 974
 975 =cut
 976 #'
 977 sub subsearch {
 978   my ($env,$subject)=@_;
 979   my $dbh = C4::Context->dbh;
 980   my $sth=$dbh->prepare("Select * from biblio,bibliosubject where
 981   biblio.biblionumber=bibliosubject.biblionumber and
 982   bibliosubject.subject=? group by biblio.biblionumber
 983   order by biblio.title");
 984   $sth->execute($subject);
 985   my $i=0;
 986   my @results;
 987   while (my $data=$sth->fetchrow_hashref){
 988     push @results, $data;
 989     $i++;
 990   }
 991   $sth->finish;
 992   return(@results);
 993 }
 994
 995 END { }       # module clean-up code here (global destructor)
 996
 997 1;
 998 __END__
 999
1000 =back
1001
1002 =head1 AUTHOR
1003
1004 Koha Developement team <info@koha.org>
1005
1006 =cut