koha-dev.rot13.org Git - koha_gimpoz/blob - C4/Search.pm

   1 package C4::Search;
   2
   3 # Copyright 2000-2002 Katipo Communications
   4 # New functions added 22-09-2005 Tumer Garip tgarip@neu.edu.tr
   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 use C4::Biblio;
  26 use C4::Koha;
  27 use Date::Calc;
  28 use MARC::File::XML;
  29 use MARC::File::USMARC;
  30 use MARC::Record;
  31
  32         # FIXME - C4::Search uses C4::Reserves2, which uses C4::Search.
  33         # So Perl complains that all of the functions here get redefined.
  34 use C4::Date;
  35
  36 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
  37
  38 # set the version for version checking
  39 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
  40           shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
  41
  42 =head1 NAME
  43
  44 C4::Search - Functions for searching the Koha catalog and other databases
  45
  46 =head1 SYNOPSIS
  47
  48   use C4::Search;
  49
  50   my ($count, @results) = catalogsearch($env, $type, $search, $num, $offset);
  51
  52 =head1 DESCRIPTION
  53
  54 This module provides the searching facilities for the Koha catalog and
  55 other databases.
  56
  57 C<&catalogsearch> is a front end to all the other searches. Depending
  58 on what is passed to it, it calls the appropriate search function.
  59
  60 =head1 FUNCTIONS
  61
  62 =over 2
  63
  64 =cut
  65
  66 @ISA = qw(Exporter);
  67 @EXPORT = qw(
  68
  69 &CatSearch &BornameSearch &ItemInfo &KeywordSearch &subsearch
  70 &itemdata &bibdata &GetItems &borrdata &itemnodata &itemcount
  71 &borrdata2 &borrdata3 &NewBorrowerNumber &bibitemdata &borrissues
  72 &getboracctrecord &ItemType &itemissues &subject &subtitle
  73 &addauthor &bibitems &barcodes &findguarantees &allissues
  74 &findseealso &findguarantor &getwebsites &getwebbiblioitems &itemcount2 &FindDuplicate
  75 &isbnsearch &getbranchname &getborrowercategory &getborrowercategoryinfo
  76
  77 &searchZOOM &catalogsearch &catalogsearch3 &CatSearch3 &catalogsearch4 &searchResults
  78
  79 &getRecords &buildQuery
  80
  81 &getMARCnotes &getMARCsubjects &getMARCurls);
  82 # make all your functions, whether exported or not;
  83
  84 =head1 findseealso($dbh,$fields);
  85
  86 =head2 $dbh is a link to the DB handler.
  87
  88 use C4::Context;
  89 my $dbh =C4::Context->dbh;
  90
  91 =head2 $fields is a reference to the fields array
  92
  93 This function modify the @$fields array and add related fields to search on.
  94
  95 =cut
  96
  97 sub findseealso {
  98     my ($dbh, $fields) = @_;
  99     my $tagslib = MARCgettagslib ($dbh,1);
 100     for (my $i=0;$i<=$#{$fields};$i++) {
 101         my ($tag) =substr(@$fields[$i],1,3);
 102         my ($subfield) =substr(@$fields[$i],4,1);
 103         @$fields[$i].=','.$tagslib->{$tag}->{$subfield}->{seealso} if ($tagslib->{$tag}->{$subfield}->{seealso});
 104     }
 105 }
 106
 107 =item findguarantees
 108
 109   ($num_children, $children_arrayref) = &findguarantees($parent_borrno);
 110   $child0_cardno = $children_arrayref->[0]{"cardnumber"};
 111   $child0_borrno = $children_arrayref->[0]{"borrowernumber"};
 112
 113 C<&findguarantees> takes a borrower number (e.g., that of a patron
 114 with children) and looks up the borrowers who are guaranteed by that
 115 borrower (i.e., the patron's children).
 116
 117 C<&findguarantees> returns two values: an integer giving the number of
 118 borrowers guaranteed by C<$parent_borrno>, and a reference to an array
 119 of references to hash, which gives the actual results.
 120
 121 =cut
 122 #'
 123 sub findguarantees{
 124   my ($bornum)=@_;
 125   my $dbh = C4::Context->dbh;
 126   my $sth=$dbh->prepare("select cardnumber,borrowernumber, firstname, surname from borrowers where guarantor=?");
 127   $sth->execute($bornum);
 128
 129   my @dat;
 130   while (my $data = $sth->fetchrow_hashref)
 131   {
 132     push @dat, $data;
 133   }
 134   $sth->finish;
 135   return (scalar(@dat), \@dat);
 136 }
 137
 138 =item findguarantor
 139
 140   $guarantor = &findguarantor($borrower_no);
 141   $guarantor_cardno = $guarantor->{"cardnumber"};
 142   $guarantor_surname = $guarantor->{"surname"};
 143   ...
 144
 145 C<&findguarantor> takes a borrower number (presumably that of a child
 146 patron), finds the guarantor for C<$borrower_no> (the child's parent),
 147 and returns the record for the guarantor.
 148
 149 C<&findguarantor> returns a reference-to-hash. Its keys are the fields
 150 from the C<borrowers> database table;
 151
 152 =cut
 153 #'
 154 sub findguarantor{
 155   my ($bornum)=@_;
 156   my $dbh = C4::Context->dbh;
 157   my $sth=$dbh->prepare("select guarantor from borrowers where borrowernumber=?");
 158   $sth->execute($bornum);
 159   my $data=$sth->fetchrow_hashref;
 160   $sth->finish;
 161   $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
 162   $sth->execute($data->{'guarantor'});
 163   $data=$sth->fetchrow_hashref;
 164   $sth->finish;
 165   return($data);
 166 }
 167
 168 =item NewBorrowerNumber
 169
 170   $num = &NewBorrowerNumber();
 171
 172 Allocates a new, unused borrower number, and returns it.
 173
 174 =cut
 175 #'
 176 # FIXME - This is identical to C4::Circulation::Borrower::NewBorrowerNumber.
 177 # Pick one and stick with it. Preferably use the other one. This function
 178 # doesn't belong in C4::Search.
 179 sub NewBorrowerNumber {
 180   my $dbh = C4::Context->dbh;
 181   my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
 182   $sth->execute;
 183   my $data=$sth->fetchrow_hashref;
 184   $sth->finish;
 185   $data->{'max(borrowernumber)'}++;
 186   return($data->{'max(borrowernumber)'});
 187 }
 188
 189 =item catalogsearch
 190
 191   ($count, @results) = &catalogsearch($env, $type, $search, $num, $offset);
 192
 193 This is primarily a front-end to other, more specialized catalog
 194 search functions: if C<$search-E<gt>{itemnumber}> or
 195 C<$search-E<gt>{isbn}> is given, C<&catalogsearch> uses a precise
 196 C<&CatSearch>. If $search->{subject} is given, it runs a subject
 197 C<&CatSearch>. If C<$search-E<gt>{keyword}> is given, it runs a
 198 C<&KeywordSearch>. Otherwise, it runs a loose C<&CatSearch>.
 199
 200 If C<$env-E<gt>{itemcount}> is 1, then C<&catalogsearch> also counts
 201 the items for each result, and adds several keys:
 202
 203 =over 4
 204
 205 =item C<itemcount>
 206
 207 The total number of copies of this book.
 208
 209 =item C<locationhash>
 210
 211 This is a reference-to-hash; the keys are the names of branches where
 212 this book may be found, and the values are the number of copies at
 213 that branch.
 214
 215 =item C<location>
 216
 217 A descriptive string saying where the book is located, and how many
 218 copies there are, if greater than 1.
 219
 220 =item C<subject2>
 221
 222 The book's subject, with spaces replaced with C<%20>, presumably for
 223 HTML.
 224
 225 =back
 226
 227 =cut
 228 #'
 229 sub catalogsearch {
 230     my ($dbh, $tags, $and_or, $excluding, $operator, $value, $offset,$length,$orderby,$desc_or_asc) = @_;
 231
 232     # used for the new API
 233     my ($search_or_scan,$type,$query,$num,$startfrom,$then_sort_by);
 234
 235     $search_or_scan = 'search';
 236     $then_sort_by = '';
 237     my $number_of_results = $length; # num of results to return
 238     $startfrom = $offset; # offset
 239     my $ccl_query;
 240     for (my $i = 0 ; $i <= $#{$value} ; $i++) {
 241         $ccl_query.= @$value[$i];
 242     }
 243     my ($error,$count,$facets,@results) = searchZOOM('search','ccl',$ccl_query,$number_of_results,$startfrom,$then_sort_by);
 244
 245     my @result = ();
 246     my $subtitle; # Added by JF for Subtitles
 247
 248     # find bibids from results
 249     #put them in @result
 250     foreach my $rec (@results) {
 251         my $record = MARC::Record->new_from_usmarc($rec);
 252         my $oldbiblio = MARCmarc2koha($dbh,$record,'');
 253         push @result, $oldbiblio->{'biblionumber'}; #FIXME bibid?
 254     }
 255     # we have bibid list. Now, loads title and author from [offset] to [offset]+[length]
 256     my $counter = $offset;
 257     # HINT : biblionumber as bn is important. The hash is fills biblionumber with items.biblionumber.
 258     # so if you dont' has an item, you get a not nice empty value.
 259     my $sth = $dbh->prepare("SELECT biblio.biblionumber as bn,biblioitems.*,biblio.*, itemtypes.notforloan,itemtypes.description
 260                             FROM biblio
 261                             LEFT JOIN biblioitems on biblio.biblionumber = biblioitems.biblionumber
 262                             LEFT JOIN itemtypes on itemtypes.itemtype=biblioitems.itemtype
 263                             WHERE biblio.biblionumber = ?"); #marc_biblio.biblionumber AND bibid = ?");
 264         my $sth_subtitle = $dbh->prepare("SELECT subtitle FROM bibliosubtitle WHERE biblionumber=?"); # Added BY JF for Subtitles
 265     my @finalresult = ();
 266     my @CNresults=();
 267     my $totalitems=0;
 268     my $oldline;
 269     my ($oldbibid, $oldauthor, $oldtitle);
 270     my $sth_itemCN;
 271     if (C4::Context->preference('hidelostitems')) {
 272         $sth_itemCN = $dbh->prepare("select items.* from items where biblionumber=? and (itemlost = 0 or itemlost is NULL) order by homebranch");
 273     } else {
 274         $sth_itemCN = $dbh->prepare("select items.* from items where biblionumber=? order by homebranch");
 275     }
 276     my $sth_issue = $dbh->prepare("select date_due,returndate from issues where itemnumber=?");
 277     # parse all biblios between start & end.
 278     #while (($counter <= $#result) && ($counter <= ($offset + $length))) { #FIXME, do all of them
 279     while ($counter <= $#result) {
 280         # search & parse all items & note itemcallnumber
 281         #warn $result[$counter];
 282         $sth->execute($result[$counter]);
 283         my $continue=1;
 284         my $line = $sth->fetchrow_hashref;
 285         my $biblionumber=$line->{bn};
 286         # Return subtitles first ADDED BY JF
 287                 $sth_subtitle->execute($biblionumber);
 288                 my $subtitle_here.= $sth_subtitle->fetchrow." ";
 289                 chop $subtitle_here;
 290                 $subtitle = $subtitle_here;
 291 #               warn "Here's the Biblionumber ".$biblionumber;
 292 #                warn "and here's the subtitle: ".$subtitle_here;
 293
 294         # /ADDED BY JF
 295
 296 #       $continue=0 unless $line->{bn};
 297 #       my $lastitemnumber;
 298         $sth_itemCN->execute($biblionumber);
 299         my @CNresults = ();
 300         my $notforloan=1; # to see if there is at least 1 item that can be issued
 301         while (my $item = $sth_itemCN->fetchrow_hashref) {
 302             # parse the result, putting holdingbranch & itemcallnumber in separate array
 303             # then all other fields in the main array
 304
 305             # search if item is on loan
 306             my $date_due;
 307             $sth_issue->execute($item->{itemnumber});
 308             while (my $loan = $sth_issue->fetchrow_hashref) {
 309                 if ($loan->{date_due} and !$loan->{returndate}) {
 310                     $date_due = $loan->{date_due};
 311                 }
 312             }
 313             # store this item
 314             my %lineCN;
 315             $lineCN{holdingbranch} = $item->{holdingbranch};
 316             $lineCN{itemcallnumber} = $item->{itemcallnumber};
 317             $lineCN{location} = $item->{location};
 318             $lineCN{date_due} = format_date($date_due);
 319             #$lineCN{notforloan} = $notforloanstatus{$line->{notforloan}} if ($line->{notforloan}); # setting not forloan if itemtype is not for loan
 320             #$lineCN{notforloan} = $notforloanstatus{$item->{notforloan}} if ($item->{notforloan}); # setting not forloan it this item is not for loan
 321             $notforloan=0 unless ($item->{notforloan} or $item->{wthdrawn} or $item->{itemlost});
 322             push @CNresults,\%lineCN;
 323             $totalitems++;
 324         }
 325         # save the biblio in the final array, with item and item issue status
 326         my %newline;
 327         %newline = %$line;
 328         $newline{totitem} = $totalitems;
 329         # if $totalitems == 0, check if it's being ordered.
 330         if ($totalitems == 0) {
 331             my $sth = $dbh->prepare("select count(*) from aqorders where biblionumber=? and datecancellationprinted is NULL");
 332             $sth->execute($biblionumber);
 333             my ($ordered) = $sth->fetchrow;
 334             $newline{onorder} = 1 if $ordered;
 335         }
 336         $newline{biblionumber} = $biblionumber;
 337         $newline{norequests} = 0;
 338         $newline{norequests} = 1 if ($line->{notforloan}); # itemtype not issuable
 339         $newline{norequests} = 1 if (!$line->{notforloan} && $notforloan); # itemtype issuable but all items not issuable for instance
 340                 $newline{subtitle} = $subtitle;  # put the subtitle in ADDED BY JF
 341
 342         my @CNresults2= @CNresults;
 343         $newline{CN} = \@CNresults2;
 344         $newline{'even'} = 1 if $#finalresult % 2 == 0;
 345         $newline{'odd'} = 1 if $#finalresult % 2 == 1;
 346         $newline{'timestamp'} = format_date($newline{timestamp});
 347         @CNresults = ();
 348         push @finalresult, \%newline;
 349         $totalitems=0;
 350         $counter++;
 351     }
 352     my $nbresults = $#result+1;
 353     return (\@finalresult, $nbresults);
 354 }
 355
 356
 357 sub add_html_bold_fields {
 358         my ($type, $data, $search) = @_;
 359
 360         my %reference = ('additionalauthors' => 'author',
 361                                         'publishercode' => 'publisher',
 362                                         'subtitle' => 'title'
 363                                         );
 364
 365         foreach my $key ('title', 'author', 'additionalauthors', 'publishercode', 'publicationyear', 'subject', 'subtitle') {
 366                 my $new_key;
 367                 if ($key eq 'additionalauthors') {
 368                         $new_key = 'additionalauthors';
 369                 } else {
 370                         $new_key = 'bold_' . $key;
 371                         $data->{$new_key} = $data->{$key};
 372                 }
 373
 374                 my $key1;
 375                 if ($reference{$key}) {
 376                         $key1 = $reference{$key};
 377                 } else {
 378                         $key1 = $key;
 379                 }
 380
 381                 my @keys;
 382                 my $i = 1;
 383                 if ($type eq 'keyword') {
 384                 my $newkey=$search->{'keyword'};
 385                 $newkey=~s /\++//g;
 386                         @keys = split " ", $newkey;
 387                 } else {
 388                         while ($search->{"field_value$i"}) {
 389                                 my $newkey=$search->{"field_value$i"};
 390                                 $newkey=~s /\++//g;
 391                                 push @keys, $newkey;
 392                                 $i++;
 393                         }
 394                 }
 395                 my $count = @keys;
 396                 for ($i = 0; $i < $count ; $i++) {
 397                         if ($key eq 'additionalauthors') {
 398                                 my $j = 0;
 399                                 foreach (@{$data->{$new_key}}) {
 400                                         if (!$data->{$new_key}->[$j]->{'bold_value'}) {
 401                                                 $data->{$new_key}->[$j]->{'bold_value'} = $data->{$new_key}->[$j]->{'value'};
 402                                         }
 403                                         if ( ($data->{$new_key}->[$j]->{'value'} =~ /($keys[$i])/i) && (lc($keys[$i]) ne 'b') ) {
 404                                                 my $word = $1;
 405                                                 $data->{$new_key}->[$j]->{'bold_value'} =~ s/$word/<b>$word<\/b>/;
 406                                         }
 407                                         $j++;
 408                                 }
 409                         } else {
 410                                 if (($data->{$new_key} =~ /($keys[$i])/i) && (lc($keys[$i]) ne 'b') ) {
 411                                         my $word = $1;
 412                                         $data->{$new_key} =~ s/$word/<b>$word<\/b>/;
 413                                 }
 414                         }
 415                 }
 416         }
 417
 418
 419 }
 420
 421 sub catalogsearch3 {
 422         my ($search,$num,$offset) = @_;
 423         my $dbh = C4::Context->dbh;
 424         my ($count,@results);
 425
 426         if ($search->{'itemnumber'} ne '' || $search->{'isbn'} ne ''|| $search->{'biblionumber'} ne ''){
 427                 ($count,@results) = CatSearch3('precise',$search,$num,$offset);
 428         } elsif ($search->{'keyword'} ne ''){
 429                 ($count,@results) = CatSearch3('keyword',$search,$num,$offset);
 430         } elsif ($search->{'recently_items'} ne '') {
 431                 ($count,@results) = CatSearch3('recently_items',$search,$num,$offset);
 432         } else {
 433                 ($count,@results) = CatSearch3('loose',$search,$num,$offset);
 434         }
 435
 436
 437         return ($count,@results);
 438 }
 439
 440 sub CatSearch3  {
 441
 442         my ($type,$search,$num,$offset)=@_;
 443         my $dbh = C4::Context->dbh;
 444         my $query = '';                 #to make the query statement
 445         my $count_query = '';   #to count total results
 446         my @params = ();                #to collect the params
 447         my @results;                    #to retrieve the results
 448
 449         # 1) do a search by barcode or isbn
 450         if ($type eq 'precise') {
 451
 452                         if ($search->{'itemnumber'} ne ''){
 453                         $query = "SELECT biblionumber FROM items WHERE (barcode = ?)";
 454                         push @params, $search->{'itemnumber'};
 455
 456                         } elsif ($search->{'isbn'} ne '') {
 457                         $query = "SELECT biblionumber FROM biblioitems WHERE (isbn like ?)";
 458                         push @params, $search->{'isbn'};
 459                         }else {
 460                         $query = "SELECT biblionumber FROM biblioitems WHERE (biblionumber = ?)";
 461                         push @params, $search->{'biblionumber'};
 462                         }
 463
 464                 #add branch condition
 465                 if ($search->{'branch'} ne '') {
 466                         $query.= " AND (  holdingbranch like ? ) ";
 467                         my $keys = $search->{'branch'};
 468                         push @params, $keys;
 469                 }
 470
 471         # 2) do a search by keyword
 472         } elsif ($type eq 'keyword') {
 473                 my $keys = $search->{'keyword'};
 474                 my @words = split / /, $keys;
 475
 476                 #parse the keywords
 477                 my $keyword;
 478                 if ($search->{'ttype'} eq 'exact') {
 479                         for (my $i = 0; $i < @words ;$i++) {
 480                                 if ($i + 1 == @words) {
 481                                         $words[$i] = '+' . $words[$i] . '*';
 482                                 } else {
 483                                         $words[$i] = '+' . $words[$i];
 484                                 }
 485                         }
 486                 } else {
 487                         for (my $i = 0; $i < @words ;$i++) {
 488                                 $words[$i] =  $words[$i] . '*';
 489                         }
 490                 }
 491                 $keyword = join " ", @words;
 492
 493                 #Builds the SQL
 494                 $query = "(SELECT DISTINCT B.biblionumber AS biblionumber ,( MATCH (title,seriestitle,unititle,B.author,subject,publishercode,itemcallnumber) AGAINST(? in BOOLEAN MODE) ) as Relevance
 495                                                 FROM biblio AS B
 496                                                 LEFT JOIN biblioitems AS BI ON (B.biblionumber = BI.biblionumber)
 497                                                 LEFT JOIN items AS I ON (BI.biblionumber = I.biblionumber)
 498                                                 LEFT JOIN additionalauthors AA1 ON (B.biblionumber = AA1.biblionumber)
 499                                                 LEFT JOIN bibliosubject AS BS1 ON (B.biblionumber = BS1.biblionumber)
 500                                                 LEFT JOIN bibliosubtitle AS BSU1 ON (B.biblionumber = BSU1.biblionumber)
 501                                         where   MATCH (title,seriestitle,unititle,B.author,subject,publishercode,itemcallnumber) AGAINST (? IN BOOLEAN MODE) ";
 502
 503                 push @params,$keyword;
 504                 push @params,$keyword;
 505                 #search by class
 506                 if ($search->{'class'} ne '') {
 507                         $query .= " AND ( itemtype = ? ) ";
 508                         push @params, $search->{'class'};
 509                 }
 510                 #search by branch
 511                 if ($search->{'branch'} ne '') {
 512                         $query .= " AND ( items.holdingbranch like ? ) ";
 513                         push @params, $search->{'branch'};
 514                 }
 515         if ($search->{'stack'} ne '') {
 516                         $query .= " AND ( items.stack = ?  ) ";
 517                         push @params, $search->{'stack'};
 518                 }
 519                 #search by publication year
 520                 if ($search->{'date_from'} ne '') {
 521                 $query .= " AND ( biblioitems.publicationyear >= ?) ";
 522                         push @params, $search->{'date_from'};
 523                 if ($search->{'date_to'} ne '') {
 524                                 $query .= " AND ( biblioitems.publicationyear <= ?) ";
 525                                 push @params, $search->{'date_to'};
 526
 527                         }
 528                 }
 529                 $query .= ")";
 530
 531
 532
 533
 534         # 3) search the items acquired recently (in the last $search->{'range'} days)
 535         } elsif ($type eq 'recently_items') {
 536                 my $keys;
 537                 if ($search->{'range'}) {
 538                         $keys = $search->{'range'};
 539                 } else {
 540                         $keys = 30;
 541                 }
 542                 $query = "SELECT B.biblionumber FROM biblio AS B
 543                                                         LEFT JOIN biblioitems AS BI ON (B.biblionumber = BI.biblionumber)
 544
 545                                                 WHERE
 546                                                         (TO_DAYS( NOW( ) ) - TO_DAYS( B.timestamp ))<?";
 547                 #search by class
 548                 push @params, $keys;
 549                 if ($search->{'class'} ne '') {
 550                         $query .= " AND ( BI.itemtype = ? ) ";
 551                         push @params, $search->{'class'};
 552                 }
 553                 $query.= " ORDER BY title ";
 554
 555         # 4) do a loose search
 556         } else {
 557
 558                         my ($condition1, $condition2, $condition3) = ('','','');
 559                         my $count_params = 0;
 560
 561
 562                         #search_field 1
 563                         if ($search->{'field_name1'} eq 'all') {
 564                                 $condition1.= " ( MATCH (title,seriestitle,unititle,B.author,subject,publishercode,itemcallnumber) AGAINST(? in BOOLEAN MODE) ) ";
 565
 566                                 $count_params = 1;
 567                         } elsif ($search->{'field_name1'} eq 'author') {
 568                                 $condition1.= " (  MATCH (B.author) AGAINST(? in BOOLEAN MODE)  ) ";
 569                                 $count_params = 1;
 570                         } elsif ($search->{'field_name1'} eq 'title') {
 571                                 $condition1.= " (  MATCH (title,seriestitle,unititle) AGAINST(? in BOOLEAN MODE ) ) ";
 572                                 $count_params = 1;
 573                         } elsif ($search->{'field_name1'} eq 'subject') {
 574                                 $condition1.= " ( ( MATCH (subject) AGAINST(? in BOOLEAN MODE) ) ) ";
 575                                 $count_params = 1;
 576                         } elsif ($search->{'field_name1'} eq 'publisher') {
 577                                 $condition1.= " ( MATCH (publishercode) AGAINST(? in BOOLEAN MODE )) ";
 578                                 $count_params = 1;
 579                         } elsif ($search->{'field_name1'} eq 'publicationyear') {
 580                                 $condition1.= " ( MATCH (publicationyear) AGAINST(? in BOOLEAN MODE )) ";
 581                                 $count_params = 1;
 582                         } elsif ($search->{'field_name1'} eq 'callno') {
 583                                 $condition1.= "  ( MATCH (itemcallnumber) AGAINST(? in BOOLEAN MODE ))  ";
 584                                 $count_params = 1;
 585                         }
 586
 587                                         if ($search->{'ttype1'}  eq 'exact') {
 588                                         push @params,"\"".$search->{'field_value1'}."\"";
 589                                         push @params, "\"".$search->{'field_value1'}."\"";
 590                                         } else {
 591                                         my $keys = $search->{'field_value1'};
 592                                         my @words = split / /, $keys;
 593                                         #parse the keywords
 594                                         my $keyword;
 595                                                 for (my $i = 0; $i < @words ;$i++) {
 596                                                 $words[$i] = '+'. $words[$i] . '*';
 597                                                 }
 598                                         $keyword = join " ", @words;
 599                                         push @params, $keyword;
 600                                         push @params, $keyword;
 601
 602                                         }
 603
 604                         $query = " SELECT DISTINCT B.biblionumber AS biblionumber ,$condition1 as Relevance
 605                                                 FROM biblio AS B
 606                                                 LEFT JOIN biblioitems AS BI ON (B.biblionumber = BI.biblionumber)
 607                                                 LEFT JOIN items AS I ON (BI.biblionumber = I.biblionumber)
 608                                                 LEFT JOIN additionalauthors AA1 ON (B.biblionumber = AA1.biblionumber)
 609                                                 LEFT JOIN bibliosubject AS BS1 ON (B.biblionumber = BS1.biblionumber)
 610                                                 LEFT JOIN bibliosubtitle AS BSU1 ON (B.biblionumber = BSU1.biblionumber) ";
 611
 612
 613                         #search_field 2
 614                         if ( ($search->{'field_value1'}) && ($search->{'field_value2'}) ) {
 615                         if ($search->{'field_name2'} eq 'all') {
 616                                 $condition2.= "  MATCH (title,seriestitle,unititle,B.author,subject,publishercode,itemcallnumber) AGAINST( ? in BOOLEAN MODE) ) ";
 617
 618                                 $count_params = 1;
 619                         } elsif ($search->{'field_name2'} eq 'author') {
 620                                 $condition2.= "  MATCH (B.author,AA1.author) AGAINST( ? in BOOLEAN MODE)  ) ";
 621                                 $count_params = 1;
 622                         } elsif ($search->{'field_name2'} eq 'title') {
 623                                 $condition2.= "   MATCH (title,seriestitle,unititle) AGAINST( ? in BOOLEAN MODE ) ) ";
 624                                 $count_params = 1;
 625                         } elsif ($search->{'field_name2'} eq 'subject') {
 626                                 $condition2.= "   MATCH (subject) AGAINST(? in BOOLEAN MODE) )  ";
 627                                 $count_params = 1;
 628                         } elsif ($search->{'field_name2'} eq 'publisher') {
 629                                 $condition2.= " MATCH (publishercode) AGAINST(? in BOOLEAN MODE )) ";
 630                                 $count_params = 1;
 631                         } elsif ($search->{'field_name2'} eq 'publicationyear') {
 632                                 $condition2.= "  MATCH (publicationyear) AGAINST(? in BOOLEAN MODE )) ";
 633                                 $count_params = 1;
 634                         } elsif ($search->{'field_name2'} eq 'callno') {
 635                                 $condition2.= "   MATCH (itemcallnumber) AGAINST(? in BOOLEAN MODE ))  ";
 636                                 $count_params = 1;
 637                         }
 638                                         if ($search->{'op1'} eq "not"){
 639                                         $search->{'op1'}="and (not ";
 640                                         }else{
 641                                         $search->{'op1'}.=" (";
 642                                         }
 643
 644                                         if ($search->{'ttype2'}  eq 'exact') {
 645                                         push @params, "\"".$search->{'field_value2'}."\"";
 646                                         } else {
 647                                         my $keys = $search->{'field_value2'};
 648                                         my @words = split / /, $keys;
 649                                         #parse the keywords
 650                                         my $keyword;
 651                                                 for (my $i = 0; $i < @words ;$i++) {
 652                                                 $words[$i] = "+". $words[$i] . '*';
 653                                                 }
 654                                         $keyword = join " ", @words;
 655                                         push @params, $keyword;
 656                                         }
 657
 658                         }
 659
 660                         #search_field 3
 661                         if ( ($search->{'field_value2'}) && ($search->{'field_value3'}) ) {
 662
 663                                 if ($search->{'field_name3'} eq 'all') {
 664                                 $condition3.= " MATCH (title,seriestitle,unititle,B.author,subject,publishercode,itemcallnumber) AGAINST(? in BOOLEAN MODE ) ) ";
 665
 666                                 $count_params = 1;
 667                         } elsif ($search->{'field_name3'} eq 'author') {
 668                                 $condition3.= "   MATCH (B.author,AA1.author) AGAINST(? in BOOLEAN MODE)  ) ";
 669                                 $count_params = 1;
 670                         } elsif ($search->{'field_name3'} eq 'title') {
 671                                 $condition3.= "   MATCH (title,seriestitle,unititle) AGAINST(? in BOOLEAN MODE) ) ";
 672                                 $count_params = 1;
 673                         } elsif ($search->{'field_name3'} eq 'subject') {
 674                                 $condition3.= "  MATCH (subject) AGAINST(? in BOOLEAN MODE ) )  ";
 675                                 $count_params = 1;
 676                         } elsif ($search->{'field_name3'} eq 'publisher') {
 677                                 $condition3.= "  MATCH (publishercode) AGAINST(? in BOOLEAN MODE )) ";
 678                                 $count_params = 1;
 679                         } elsif ($search->{'field_name3'} eq 'publicationyear') {
 680                                 $condition3.= "  MATCH (publicationyear) AGAINST(? in BOOLEAN MODE )) ";
 681                                 $count_params = 1;
 682                         } elsif ($search->{'field_name3'} eq 'callno') {
 683                                 $condition3.= "   MATCH (itemcallnumber) AGAINST(? in BOOLEAN MODE ))  ";
 684                                 $count_params = 1;
 685                         }
 686                                 if ($search->{'op2'} eq "not"){
 687                                         $search->{'op2'}="and (not ";
 688                                         }else{
 689                                         $search->{'op2'}.=" (";
 690                                         }
 691                                         if ($search->{'ttype3'}  eq 'exact') {
 692                                         push @params, "\"".$search->{'field_value3'}."\"";
 693                                 } else {
 694                                         my $keys = $search->{'field_value3'};
 695                                         my @words = split / /, $keys;
 696                                         #parse the keywords
 697                                         my $keyword;
 698
 699                                                 for (my $i = 0; $i < @words ;$i++) {
 700                                                 $words[$i] = "+". $words[$i] . '*';
 701                                                 }
 702                                         $keyword = join " ", @words;
 703                                         push @params, $keyword;
 704                                 }
 705                         }
 706
 707                         $query.= " WHERE ";
 708                         if (($condition1 ne '') && ($condition2 ne '') && ($condition3 ne '')) {
 709                                 if ($search->{'op1'} eq $search->{'op2'}) {
 710                                         $query.= " ( $condition1 $search->{'op1'} $condition2 $search->{'op2'} $condition3 ) ";
 711                                 } elsif ( $search->{'op1'} eq "and (" ) {
 712                                         $query.= " ( $condition1 $search->{'op1'} ( $condition2 $search->{'op2'} $condition3 ) ) ";
 713                                 } else {
 714                                         $query.= " ( ( $condition1 $search->{'op1'} $condition2 ) $search->{'op2'} $condition3 ) ";
 715                                 }
 716                         } elsif ( ($condition1 ne '') && ($condition2 ne '') ) {
 717                                 $query.= " ( $condition1 $search->{'op1'} $condition2 ) ";
 718                         } else {
 719                                 $query.= " ( $condition1 ) ";
 720                         }
 721
 722                         #search by class
 723                         if ($search->{'class'} ne ''){
 724                                 $query.= " AND ( itemtype = ? ) ";
 725                                 my $keys = $search->{'class'};
 726                                 push @params, $search->{'class'};
 727                         }
 728                         #search by branch
 729                         if ($search->{'branch'} ne '') {
 730                                 $query.= " AND   I.holdingbranch like ?  ";
 731                                 my $keys = $search->{'branch'};
 732                                 push @params, $keys, $keys;
 733                         }
 734                         #search by publication year
 735                         if ($search->{'date_from'} ne '') {
 736                                 $query .= " AND ( BI.publicationyear >= ?) ";
 737                                 push @params, $search->{'date_from'};
 738                                 if ($search->{'date_to'} ne '') {
 739                                                         $query .= " AND ( BI.publicationyear <= ?) ";
 740                                         push @params, $search->{'date_to'};
 741
 742                                 }
 743                         }
 744                         if ($search->{'order'} eq "1=1003 i<"){
 745                         $query.= " ORDER BY b.author ";
 746                         }elsif ($search->{'order'} ge "1=9 i<"){
 747                         $query.= " ORDER BY lcsort ";
 748                         }elsif ($search->{'order'} eq "1=4 i<"){
 749                         $query.= " ORDER BY title ";
 750                         }else{
 751                         $query.=" ORDER BY Relevance DESC";
 752                         }
 753         }
 754
 755 #warn "$query,@params,";
 756         $count_query = $query;
 757         warn "QUERY:".$count_query;
 758         #execute the query and returns just the results between $num and $num + $offset
 759         my $limit = $num + $offset;
 760         my $startfrom = $offset;
 761         my $sth = $dbh->prepare($query);
 762
 763         $sth->execute(@params);
 764
 765     my $i = 0;
 766 #Build brancnames hash
 767 #find branchname
 768 #get branch information.....
 769 my %branches;
 770                 my $bsth=$dbh->prepare("SELECT branchcode,branchname FROM branches");
 771                 $bsth->execute();
 772                 while (my $bdata=$bsth->fetchrow_hashref){
 773                         $branches{$bdata->{'branchcode'}}= $bdata->{'branchname'};
 774
 775                 }
 776
 777 #Building shelving hash
 778 my %shelves;
 779 #find shelvingname
 780 my $stackstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.stack"');
 781                 $stackstatus->execute;
 782
 783                 my ($authorised_valuecode) = $stackstatus->fetchrow;
 784                 if ($authorised_valuecode) {
 785                         $stackstatus = $dbh->prepare("select lib,authorised_value from authorised_values where category=? ");
 786                         $stackstatus->execute($authorised_valuecode);
 787
 788                         while (my $lib = $stackstatus->fetchrow_hashref){
 789                         $shelves{$lib->{'authorised_value'}} = $lib->{'lib'};
 790                         }
 791                 }
 792
 793 #search item field code
 794         my $sth3 =
 795           $dbh->prepare(
 796         "select tagfield from marc_subfield_structure where kohafield like 'items.itemnumber'"
 797         );
 798          $sth3->execute;
 799          my ($itemtag) = $sth3->fetchrow;
 800 ## find column names of items related to MARC
 801         my $sth2=$dbh->prepare("SHOW COLUMNS from items");
 802         $sth2->execute;
 803         my %subfieldstosearch;
 804         while ((my $column)=$sth2->fetchrow){
 805         my ($tagfield,$tagsubfield) = &MARCfind_marc_from_kohafield($dbh,"items.".$column,"");
 806         $subfieldstosearch{$column}=$tagsubfield;
 807         }
 808 my $toggle;
 809 my $even;
 810 #proccess just the results to show
 811         while (my( $data,$rel) = $sth->fetchrow)  {
 812                 if (($i >= $startfrom) && ($i < $limit)) {
 813
 814                 my $marcrecord=MARCgetbiblio($dbh,$data);
 815                 my $oldbiblio=MARCmarc2koha($dbh,$marcrecord,'');
 816
 817
 818                         &add_html_bold_fields($type, $oldbiblio, $search);
 819 if ($i % 2) {
 820                 $toggle="#ffffcc";
 821         } else {
 822                 $toggle="white";
 823         }
 824         $oldbiblio->{'toggle'}=$toggle;
 825
 826
 827
 828  my @fields = $marcrecord->field($itemtag);
 829 my @items;
 830  my $item;
 831 my %counts;
 832 $counts{'total'}=0;
 833
 834 #
 835 ##Loop for each item field
 836      foreach my $field (@fields) {
 837        foreach my $code ( keys %subfieldstosearch ) {
 838
 839 $item->{$code}=$field->subfield($subfieldstosearch{$code});
 840 }
 841
 842 my $status;
 843
 844 $item->{'branchname'}=$branches{$item->{'holdingbranch'}};
 845 $item->{'shelves'}=$shelves{$item->{stack}};
 846 $status="Lost" if ($item->{'itemlost'}>0);
 847 $status="Withdrawn" if ($item->{'wthdrawn'}>0) ;
 848 if ($search->{'from'} eq "intranet"){
 849 $search->{'avoidquerylog'}=1;
 850 $status="Due:".format_date($item->{'onloan'}) if ($item->{'onloan'}>0);
 851  $status = $item->{'holdingbranch'}."-".$item->{'stack'}."[".$item->{'itemcallnumber'}."]" unless defined $status;
 852 }else{
 853 $status="On Loan" if ($item->{'onloan'}>0);
 854    $status = $item->{'branchname'}."[".$item->{'shelves'}."]" unless defined $status;
 855 }
 856  $counts{$status}++;
 857 $counts{'total'}++;
 858 push @items,$item;
 859
 860         }
 861
 862                 my $norequests = 1;
 863                 my $noitems    = 1;
 864                 if (@items) {
 865                         $noitems = 0;
 866                         foreach my $itm (@items) {
 867                                 $norequests = 0 unless $itm->{'itemnotforloan'};
 868                         }
 869                 }
 870                 $oldbiblio->{'noitems'} = $noitems;
 871                 $oldbiblio->{'norequests'} = $norequests;
 872                 $oldbiblio->{'even'} = $even = not $even;
 873                 $oldbiblio->{'itemcount'} = $counts{'total'};
 874                 my $totalitemcounts = 0;
 875                 foreach my $key (keys %counts){
 876                         if ($key ne 'total'){
 877                                 $totalitemcounts+= $counts{$key};
 878                                 $oldbiblio->{'locationhash'}->{$key}=$counts{$key};
 879                         }
 880                 }
 881
 882                 my ($locationtext, $locationtextonly, $notavailabletext) = ('','','');
 883                 foreach (sort keys %{$oldbiblio->{'locationhash'}}) {
 884                         if ($_ eq 'notavailable') {
 885                                 $notavailabletext="Not available";
 886                                 my $c=$oldbiblio->{'locationhash'}->{$_};
 887                                 $oldbiblio->{'not-available-p'}=$c;
 888                         } else {
 889                                 $locationtext.="$_";
 890                                 my $c=$oldbiblio->{'locationhash'}->{$_};
 891                                 if ($_ eq 'Item Lost') {
 892                                         $oldbiblio->{'lost-p'} = $c;
 893                                 } elsif ($_ eq 'Withdrawn') {
 894                                         $oldbiblio->{'withdrawn-p'} = $c;
 895                                 } elsif ($_ eq 'On Loan') {
 896                                         $oldbiblio->{'on-loan-p'} = $c;
 897                                 } else {
 898                                         $locationtextonly.= $_;
 899                                         $locationtextonly.= " ($c)<br> " if $totalitemcounts > 1;
 900                                 }
 901                                 if ($totalitemcounts>1) {
 902                                         $locationtext.=" ($c)<br> ";
 903                                 }
 904                         }
 905                 }
 906                 if ($notavailabletext) {
 907                         $locationtext.= $notavailabletext;
 908                 } else {
 909                         $locationtext=~s/, $//;
 910                 }
 911                 $oldbiblio->{'location'} = $locationtext;
 912                 $oldbiblio->{'location-only'} = $locationtextonly;
 913                 $oldbiblio->{'use-location-flags-p'} = 1;
 914                         push @results, $oldbiblio;
 915
 916                 }
 917                 $i++;
 918         }
 919
 920         my $count = $i;
 921         unless ($search->{'avoidquerylog'}) {
 922                 add_query_line($type, $search, $count);}
 923         return($count,@results);
 924 }
 925
 926 sub catalogsearch4 {
 927         my ($search,$num,$offset) = @_;
 928         my ($count,@results);
 929
 930         if ($search->{'itemnumber'} ne '' || $search->{'isbn'} ne ''|| $search->{'biblionumber'} ne ''|| $search->{'authnumber'} ne ''){
 931                 ($count,@results) = CatSearch4('precise',$search,$num,$offset);
 932         } elsif ($search->{'cql'} ne ''){
 933                 if ($search->{'rpn'} ne '') {
 934                                 warn "RPN ON";
 935                                 ($count,@results) = CatSearch4('rpn',$search,$num,$offset);
 936                 } else {
 937                         warn "RPN".$search->{'rpn'};
 938                 ($count,@results) = CatSearch4('cql',$search,$num,$offset);
 939                 }
 940         } elsif ($search->{'keyword'} ne ''){
 941                 ($count,@results) = CatSearch4('keyword',$search,$num,$offset);
 942         } elsif ($search->{'recently_items'} ne '') {
 943                 ($count,@results) = CatSearch4('recently_items',$search,$num,$offset);
 944         } else {
 945                 ($count,@results) = CatSearch4('loose',$search,$num,$offset);
 946         }
 947         return ($count,@results);
 948 }
 949
 950 sub CatSearch4  {
 951
 952         my ($type,$search,$num,$offset)=@_;
 953         my $dbh = C4::Context->dbh;
 954         my $query = '';                 #to make the query statement
 955         my $count_query = '';   #to count total results
 956         my @params = ();                #to collect the params
 957         my @results;                    #to retrieve the results
 958         my $attr;
 959         my $attr2;
 960         my $attr3;
 961         my $numresults;
 962         my $marcdata;
 963         my $toggle;
 964         my $even=1;
 965         my $cql;
 966         my $rpn;
 967         my $cql_query;
 968         # 1) do a search by barcode or isbn
 969         if ($type eq 'cql') {
 970                 $cql=1;
 971                 $cql_query = $search->{'cql'};
 972                 while( my ($k, $v) = each %$search ) {
 973                         warn "key: $k, value: $v.\n";
 974                             }
 975                 warn "QUERY:".$query;
 976         }
 977         if ($type eq 'rpn') {
 978                 $rpn=1;
 979                 $cql=1;
 980                 $cql_query = $search->{'cql'}; #but it's really a rpn query FIXME
 981         }
 982         if ($type eq 'precise') {
 983
 984                 if ($search->{'itemnumber'} ne '') {
 985
 986                         $query = " \@attr 1=1028 ". $search->{'itemnumber'};
 987
 988
 989                 }elsif ($search->{'isbn'} ne ''){
 990                         $query = " \@attr 1=7 \@attr 4=1  \@attr 5=1 "."\"".$search->{'isbn'}."\"";
 991
 992                 }elsif ($search->{'biblionumber'} ne ''){
 993                         $query = " \@attr 1=1007  ".$search->{'biblionumber'};
 994
 995                 }elsif ($search->{'authnumber'} ne ''){
 996                                 my $n=0;
 997                                 my @ids=split / /,$search->{'authnumber'} ;
 998                                 foreach my  $id (@ids){
 999                                 $query .= "  \@attr GILS 1=2057  ".$id;
1000                                 $n++;
1001                                 }
1002                         if ($n>1){
1003                          $query= "\@or ".$query;
1004                         }
1005
1006                 }
1007                 #add branch condition
1008                 if ($search->{'branch'} ne '') {
1009                 $query= "\@and ".$query;
1010                         $query .= " \@attr 1=1033 \"".$search->{'branch'}."\"";
1011
1012                 }
1013         # 2) do a search by keyword
1014         }elsif ($type eq 'keyword') {
1015                  $search->{'keyword'}=~ s/(\\|\|)//g;;
1016
1017                 #parse the keywords
1018                 my $keyword;
1019
1020                 if ($search->{'ttype'} eq 'exact') {
1021                          $attr="\@attr 4=1  \@attr 5=1 \@attr 2=102 ";
1022                 } else {
1023                          $attr=" \@attr 4=6  \@attr 5=103 \@attr 2=102 ";
1024                 }
1025
1026
1027                 #Builds the query
1028                 $query = " \@attr 1=1016 ".$attr."\"".$search->{'keyword'}."\"";
1029
1030
1031                 #search by itemtypes
1032                 if ($search->{'class'} ne '') {
1033                         $query= "\@and ".$query;
1034                         $query .= " \@attr 1=1031  \"".$search->{'class'}."\"";
1035                         push @params, $search->{'class'};
1036                 }
1037                 #search by callnumber
1038                 if ($search->{'callno'} ne '') {
1039                         $query= "\@and ".$query;
1040                         $query .= " \@attr 1=20 \@attr 4=1  \@attr 5=1 \"".$search->{'callno'}."\"";
1041
1042                 }
1043                 #search by branch
1044                 if ($search->{'branch'} ne '') {
1045                         $query= "\@and ".$query;
1046                         $query .= " \@attr 1=1033 \"".$search->{'branch'}."\"";
1047
1048                 }
1049                 if ($search->{'stack'} ne '') {
1050                         $query= "\@and ".$query;
1051                         $query .= " \@attr 1=1019 \"".$search->{'stack'}."\"";
1052                         push @params, $search->{'stack'};
1053                 }
1054                 if ($search->{'date_from'} ne '') {
1055                 $query= "\@and ".$query;
1056                 $query .= " \@attr 1=30 \@attr 2=4 \@attr 4=4 ".$search->{'date_from'};
1057                         push @params, $search->{'date_from'};
1058                 }
1059                 if ($search->{'date_to'} ne '') {
1060                              $query= "\@and ".$query;
1061                 $query .= " \@attr 1=30 \@attr 2=2 \@attr 4=4 ".$search->{'date_to'};
1062                                 push @params, $search->{'date_to'};
1063
1064                         }
1065
1066 # 3) search the items acquired recently (in the last $search->{'range'} days)
1067         } elsif ($type eq 'recently_items') {
1068                 my $keys;
1069                 if ($search->{'range'}) {
1070                         $keys = $search->{'range'}*(-1);
1071                 } else {
1072                         $keys = -30;
1073                 }
1074         my @datearr = localtime();
1075         my $dateduef = (1900+$datearr[5])."-".sprintf ("%0.2d", ($datearr[4]+1))."-".$datearr[3];
1076
1077
1078         my ($year, $month, $day) = split /-/, $dateduef;
1079         ($year, $month, $day) = &Date::Calc::Add_Delta_Days($year, $month, $day, ($keys - 1));
1080         $dateduef = "$year-$month-$day";
1081                  $query .= " \@attr 1=32 \@attr 2=4 \@attr 4=5 ".$dateduef;
1082                 #search by class
1083                 push @params, $keys;
1084                 if ($search->{'class'} ne '') {
1085                 $query= "\@and ".$query;
1086                         $query .= " \@attr 1=1031 \"".$search->{'class'}."\"";
1087
1088                 }
1089
1090
1091
1092
1093         # 4) do a loose search
1094         } else {
1095
1096                         my ($condition1, $condition2, $condition3) = ('','','');
1097                         my $count_params = 0;
1098
1099                         if ($search->{'ttype1'} eq 'exact') {
1100                         $attr="\@attr 4=1   ";
1101                                 if ($search->{'atype1'} eq 'start'){
1102                                 $attr.=" \@attr 3=1 \@attr 6=3 \@attr 5=1 \@attr 2=102 ";
1103                                 }else{
1104                                 $attr.=" \@attr 5=1 \@attr 3=3 \@attr 6=1 \@attr 2=102 ";
1105                                 }
1106                         } else {
1107                          $attr=" \@attr 4=6  \@attr 5=103 ";
1108                         }
1109
1110                         #search_field 1
1111                         $search->{'field_value1'}=~ s/(\.|\?|\;|\=|\/|\\|\||\:|\!|,|\-|\"|\(|\)|\[|\]|\{|\}|\/)//g;
1112                         if ($search->{'field_name1'} eq 'all') {
1113                                 $condition1.= " \@attr 1=1016 ".$attr." \"".$search->{'field_value1'}."\" ";
1114
1115                         } elsif ($search->{'field_name1'} eq 'author') {
1116                                 $condition1.=" \@attr 1=1003 ".$attr." \"".$search->{'field_value1'}."\" ";
1117
1118                         } elsif ($search->{'field_name1'} eq 'title') {
1119                                 $condition1.= " \@attr 1=4 ".$attr." \"".$search->{'field_value1'}."\" ";
1120
1121                         } elsif ($search->{'field_name1'} eq 'subject') {
1122                                 $condition1.=" \@attr 1=21 ".$attr." \"".$search->{'field_value1'}."\" ";
1123                         } elsif ($search->{'field_name1'} eq 'series') {
1124                                                         $condition1.=" \@attr 1=5 ".$attr." \"".$search->{'field_value1'}."\" ";
1125
1126                         } elsif ($search->{'field_name1'} eq 'publisher') {
1127                                 $condition1.= " \@attr 1=1018 ".$attr." \"".$search->{'field_value1'}."\" ";
1128                         } elsif ($search->{'field_name1'} eq 'callno') {
1129                                 $condition1.= " \@attr 1=20 \@attr 3=2 ".$attr." \"".$search->{'field_value1'}."\" ";
1130                         }
1131                         $query = $condition1;
1132                         #search_field 2
1133                         if ($search->{'field_value2'}) {
1134                         $search->{'field_value2'}=~ s/(\.|\?|\;|\=|\/|\\|\||\:|\!|,|\-|\"|\(|\)|\[|\]|\{|\}|\/)//g;
1135                         if ($search->{'ttype2'} eq 'exact') {
1136
1137                                 $attr2="\@attr 4=1   ";
1138                                 if ($search->{'atype1'} eq 'start'){
1139                                 $attr.=" \@attr 3=1 \@attr 6=3 \@attr 5=1 \@attr 2=102 ";
1140                                 }else{
1141                                 $attr.=" \@attr 5=1 \@attr 3=3 \@attr 6=1 \@attr 2=102 ";
1142                                 }
1143                         } else {
1144                                  $attr2=" \@attr 4=6  \@attr 5=103 ";
1145                         }
1146
1147                                 if ($search->{'field_name2'} eq 'all') {
1148                                         if ($search->{'op1'} eq 'and') {
1149                                                 $query = " \@and ".$query;
1150                                                 $condition2.= " \@attr 1=1016 ".$attr2." \"".$search->{'field_value2'}."\" ";
1151
1152                                         } elsif ($search->{'op1'} eq 'or')  {
1153                                                 $query = " \@or ".$query;
1154                                                 $condition2.= " \@attr 1=1016 ".$attr2." \"".$search->{'field_value2'}."\" ";
1155                                         } else {
1156                                                 $query = " \@not ".$query;
1157                                                 $condition2.= " \@attr 1=1016 ".$attr2." \"".$search->{'field_value2'}."\" ";
1158
1159                                         }
1160                                 } elsif ($search->{'field_name2'} eq 'author') {
1161                                         if ($search->{'op1'} eq 'and') {
1162                                                 $query = " \@and ".$query;
1163                                                 $condition2.= " \@attr 1=1003 ".$attr2." \"".$search->{'field_value2'}."\" ";
1164
1165                                         } elsif ($search->{'op1'} eq 'or'){
1166                                                 $query = " \@or ".$query;
1167                                                 $condition2.= " \@attr 1=1003 ".$attr2." \"".$search->{'field_value2'}."\" ";
1168                                         } else {
1169                                                 $query = " \@not ".$query;
1170                                                 $condition2.= " \@attr 1=1003 ".$attr2." \"".$search->{'field_value2'}."\" ";
1171
1172                                         }
1173
1174                                 } elsif ($search->{'field_name2'} eq 'title') {
1175                                         if ($search->{'op1'} eq 'and') {
1176                                                 $query = " \@and ".$query;
1177                                                 $condition2.= " \@attr 1=4 ".$attr2." \"".$search->{'field_value2'}."\" ";
1178
1179                                         } elsif ($search->{'op1'} eq 'or'){
1180                                                 $query = " \@or ".$query;
1181                                                 $condition2.= " \@attr 1=4 ".$attr2." \"".$search->{'field_value2'}."\" ";
1182                                         } else {
1183                                                 $query = " \@not ".$query;
1184                                                 $condition2.= " \@attr 1=4 ".$attr2." \"".$search->{'field_value2'}."\" ";
1185                                         }
1186
1187                                 } elsif ($search->{'field_name2'} eq 'subject') {
1188                                         if ($search->{'op1'} eq 'and') {
1189                                                 $query = " \@and ".$query;
1190                                                 $condition2.= " \@attr 1=21 ".$attr2." \"".$search->{'field_value2'}."\" ";
1191
1192                                         } elsif ($search->{'op1'} eq 'or') {
1193                                                 $query = " \@or ".$query;
1194                                                 $condition2.= " \@attr 1=21 ".$attr2." \"".$search->{'field_value2'}."\" ";
1195                                         } else {
1196                                                 $query = " \@not ".$query;
1197                                                 $condition2.= " \@attr 1=21 ".$attr2." \"".$search->{'field_value2'}."\" ";
1198                                         }
1199                                 } elsif ($search->{'field_name2'} eq 'series') {
1200                                         if ($search->{'op1'} eq 'and') {
1201                                                 $query = " \@and ".$query;
1202                                                 $condition2.= " \@attr 1=5 ".$attr2." \"".$search->{'field_value2'}."\" ";
1203
1204                                         } elsif ($search->{'op1'} eq 'or') {
1205                                                 $query = " \@or ".$query;
1206                                                 $condition2.= " \@attr 1=5 ".$attr2." \"".$search->{'field_value2'}."\" ";
1207                                         } else {
1208                                                 $query = " \@not ".$query;
1209                                                 $condition2.= " \@attr 1=5 ".$attr2." \"".$search->{'field_value2'}."\" ";
1210                                         }
1211                                 } elsif ($search->{'field_name2'} eq 'callno') {
1212                                         if ($search->{'op1'} eq 'and') {
1213                                                 $query = " \@and ".$query;
1214                                                 $condition2.= " \@attr 1=20 \@attr 3=2 ".$attr2." \"".$search->{'field_value2'}."\" ";
1215
1216                                         } elsif ($search->{'op1'} eq 'or'){
1217                                                 $query = " \@or ".$query;
1218                                                 $condition2.= " \@attr 1=20 \@attr 3=2 ".$attr2." \"".$search->{'field_value2'}."\" ";
1219                                         } else {
1220                                                 $query = " \@not ".$query;
1221                                                 $condition2.= " \@attr 1=20 \@attr 3=2 ".$attr2." \"".$search->{'field_value2'}."\" ";
1222                                         }
1223                                 } elsif ($search->{'field_name2'} eq 'publisher') {
1224                                 $query = " \@and ".$query;
1225                                 $condition2.= " \@attr 1=1018 ".$attr2." \"".$search->{'field_value2'}."\" ";
1226                                 } elsif ($search->{'field_name2'} eq 'publicationyear') {
1227                                 $query = " \@and ".$query;
1228                                 $condition2.= " \@attr 1=30 ".$search->{'field_value2'};
1229                                 }
1230                                         $query .=$condition2;
1231
1232
1233                         }
1234
1235                         #search_field 3
1236                         if ($search->{'field_value3'}) {
1237                         $search->{'field_value3'}=~ s/(\.|\?|\;|\=|\/|\\|\||\:|\!|,|\-|\"|\(|\)|\[|\]|\{|\}|\/)//g;
1238                         if ($search->{'ttype3'} eq 'exact') {
1239                         $attr3="\@attr 4=1   ";
1240                                 if ($search->{'atype1'} eq 'start'){
1241                                 $attr.=" \@attr 3=1 \@attr 6=3 \@attr 5=1 \@attr 2=102 ";
1242                                 }else{
1243                                 $attr.=" \@attr 5=1 \@attr 3=3 \@attr 6=1 \@attr 2=102 ";
1244                                 }
1245                         } else {
1246                         $attr3=" \@attr 4=6  \@attr 5=103 ";
1247                         }
1248
1249                                 if ($search->{'field_name3'} eq 'all') {
1250                                         if ($search->{'op2'} eq 'and') {
1251                                                 $query = " \@and ".$query;
1252                                                 $condition3.= " \@attr 1=1016 ".$attr3." \"".$search->{'field_value3'}."\" ";
1253
1254                                         } elsif ($search->{'op2'} eq 'or') {
1255                                                 $query = " \@or ".$query;
1256                                                 $condition3.= " \@attr 1=1016 ".$attr3." \"".$search->{'field_value3'}."\" ";
1257                                         } else {
1258                                                 $query = " \@not ".$query;
1259                                                 $condition3.= " \@attr 1=1016 ".$attr3." \"".$search->{'field_value3'}."\" ";
1260                                         }
1261                                 } elsif ($search->{'field_name3'} eq 'author') {
1262                                         if ($search->{'op2'} eq 'and') {
1263                                                 $query = " \@and ".$query;
1264                                                 $condition3.= " \@attr 1=1003 ".$attr3." \"".$search->{'field_value3'}."\" ";
1265
1266                                         } elsif ($search->{'op2'} eq 'or') {
1267                                                 $query = " \@or ".$query;
1268                                                 $condition3.= " \@attr 1=1003 ".$attr3." \"".$search->{'field_value3'}."\" ";
1269                                         } else {
1270                                                 $query = " \@not ".$query;
1271                                                 $condition3.= " \@attr 1=1003 ".$attr3." \"".$search->{'field_value3'}."\" ";
1272                                         }
1273
1274                                 } elsif ($search->{'field_name3'} eq 'title') {
1275                                         if ($search->{'op2'} eq 'and') {
1276                                                 $query = " \@and ".$query;
1277                                                 $condition3.= " \@attr 1=4 ".$attr3." \"".$search->{'field_value3'}."\" ";
1278
1279                                         } elsif ($search->{'op2'} eq 'or') {
1280                                                 $query = " \@or ".$query;
1281                                                 $condition3.= " \@attr 1=4 ".$attr3." \"".$search->{'field_value3'}."\" ";
1282                                         } else {
1283                                                 $query = " \@not ".$query;
1284                                                 $condition3.= " \@attr 1=4 ".$attr3." \"".$search->{'field_value3'}."\" ";
1285                                         }
1286
1287                                 } elsif ($search->{'field_name3'} eq 'subject') {
1288                                         if ($search->{'op2'} eq 'and') {
1289                                                 $query = " \@and ".$query;
1290                                                 $condition3.= " \@attr 1=21 ".$attr3." \"".$search->{'field_value3'}."\" ";
1291
1292                                         } elsif ($search->{'op2'} eq 'or') {
1293                                                 $query = " \@or ".$query;
1294                                                 $condition3.= " \@attr 1=21 ".$attr3." \"".$search->{'field_value3'}."\" ";
1295                                         } else {
1296                                                 $query = " \@not ".$query;
1297                                                 $condition3.= " \@attr 1=21 ".$attr3." \"".$search->{'field_value3'}."\" ";
1298                                         }
1299                                 } elsif ($search->{'field_name3'} eq 'series') {
1300                                         if ($search->{'op2'} eq 'and') {
1301                                                 $query = " \@and ".$query;
1302                                                 $condition3.= " \@attr 1=5 ".$attr3." \"".$search->{'field_value3'}."\" ";
1303
1304                                         } elsif ($search->{'op2'} eq 'or') {
1305                                                 $query = " \@or ".$query;
1306                                                 $condition3.= " \@attr 1=5 ".$attr3." \"".$search->{'field_value3'}."\" ";
1307                                         } else {
1308                                                 $query = " \@not ".$query;
1309                                                 $condition3.= " \@attr 1=5 ".$attr3." \"".$search->{'field_value3'}."\" ";
1310                                         }
1311                                 } elsif ($search->{'field_name3'} eq 'callno') {
1312                                         if ($search->{'op2'} eq 'and') {
1313                                                 $query = " \@and ".$query;
1314                                                 $condition3.= " \@attr 1=20 \@attr 3=2 ".$attr3." \"".$search->{'field_value3'}."\" ";
1315
1316                                         } elsif ($search->{'op2'} eq 'or') {
1317                                                 $query = " \@or ".$query;
1318                                                 $condition3.= " \@attr 1=20 \@attr 3=2 ".$attr3." \"".$search->{'field_value3'}."\" ";
1319
1320                                         } else {
1321                                                 $query = " \@not ".$query;
1322                                                 $condition3.= " \@attr 1=20  \@attr 3=2 ".$attr3." \"".$search->{'field_value3'}."\" ";
1323                                         }
1324
1325
1326                                 } elsif ($search->{'field_name3'} eq 'publisher') {
1327                                 $query = " \@and ".$query;
1328                                 $condition3.= " \@attr 1=1018 ".$attr3." \"".$search->{'field_value3'}."\" ";
1329                                 } elsif ($search->{'field_name2'} eq 'publicationyear') {
1330                                 $query = " \@and ".$query;
1331                                 $condition3.= " \@attr 1=30 ".$search->{'field_value3'};
1332                                 }
1333                                         $query .=$condition3;
1334
1335
1336                         }
1337
1338
1339
1340                         #search by class
1341                 if ($search->{'class'} ne '') {
1342                         $query= "\@and ".$query;
1343                         $query .= " \@attr 1=1031 \"".$search->{'class'}."\"";
1344                         push @params, $search->{'class'};
1345                 }
1346                 #search by branch
1347                 if ($search->{'branch'} ne '') {
1348                 $query= "\@and ".$query;
1349                         $query .= " \@attr 1=1033 \"".$search->{'branch'}."\"";
1350 #
1351                 }
1352                 if ($search->{'stack'} ne '') {
1353                         $query= "\@and ".$query;
1354                         $query .= " \@attr 1=1019 \"".$search->{'stack'}."\"";
1355
1356                 }
1357                 if ($search->{'date_from'} ne '') {
1358                 $query= "\@and ".$query;
1359                 $query .= " \@attr 1=30 \@attr 2=4 \@attr 4=4 ".$search->{'date_from'};
1360                 }
1361                 if ($search->{'date_to'} ne '') {
1362                              $query= "\@and ".$query;
1363                 $query .= " \@attr 1=30 \@attr 2=2 \@attr 4=4 ".$search->{'date_to'};
1364
1365                         }
1366
1367         }
1368
1369         if ($cql) {
1370                 warn "STILL CQL";
1371                 $count_query = $cql_query;
1372                 $query=1;
1373         } else {
1374                 $count_query = $query;
1375         }
1376         warn "QUERY_AFTER".$count_query;
1377         if ($search->{'order'}) {
1378                 $query.=" ".$search->{'order'};
1379                 $query=" \@or \@or ".$query;
1380         }
1381 #warn $query;
1382         #execute the query and returns just the results between $num and $num + $offset
1383         my $limit = $num + $offset;
1384         my $startfrom = $offset;
1385 return unless $query; ##Somebody hit the search button with no query. Prevent a system crash
1386 my $oConnection=C4::Context->Zconn("biblioserver");
1387 if ($oConnection eq "error"){
1388   return("error",undef);
1389  }
1390 #$oConnection->option(preferredRecordSyntax => "XML");
1391 my $oResult;
1392 my $newq;
1393 if ($cql) {
1394         warn "CQLISH:".$cql_query;
1395         if ($rpn) {
1396                 $newq= new ZOOM::Query::PQF($cql_query);
1397         } else {
1398                 $newq = new ZOOM::Query::CQL($cql_query,$oConnection);
1399         }
1400 } else {
1401         $newq= new ZOOM::Query::PQF($query);
1402 }
1403 #my $order=$search->{'order'};
1404 #if ($order){
1405 #$newq->sortby("$order");
1406 #}
1407 eval {
1408 $oResult= $oConnection->search($newq);
1409 };
1410 if($@){
1411    return("error",undef);
1412  }
1413
1414
1415
1416  $numresults=$oResult->size() if  ($oResult);
1417
1418     my $i = 0;
1419
1420         #proccess just the results to show
1421         if ($numresults>0)  {
1422 #Build brancnames hash
1423 #find branchname
1424 #get branch information.....
1425 my %branches;
1426                 my $bsth=$dbh->prepare("SELECT branchcode,branchname FROM branches");
1427                 $bsth->execute();
1428                 while (my $bdata=$bsth->fetchrow_hashref){
1429                         $branches{$bdata->{'branchcode'}}= $bdata->{'branchname'};
1430
1431                 }
1432
1433 #Building shelving hash
1434 my %shelves;
1435 #find shelvingname
1436 my $stackstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.stack"');
1437                 $stackstatus->execute;
1438
1439                 my ($authorised_valuecode) = $stackstatus->fetchrow;
1440                 if ($authorised_valuecode) {
1441                         $stackstatus = $dbh->prepare("select lib,authorised_value from authorised_values where category=? ");
1442                         $stackstatus->execute($authorised_valuecode);
1443
1444                         while (my $lib = $stackstatus->fetchrow_hashref){
1445                         $shelves{$lib->{'authorised_value'}} = $lib->{'lib'};
1446                         }
1447                 }
1448
1449 #search item field code
1450         my $sth =
1451           $dbh->prepare(
1452 "select tagfield from marc_subfield_structure where kohafield like 'items.itemnumber'"
1453         );
1454  $sth->execute;
1455  my ($itemtag) = $sth->fetchrow;
1456 ## find column names of items related to MARC
1457 my $sth2=$dbh->prepare("SHOW COLUMNS from items");
1458         $sth2->execute;
1459 my %subfieldstosearch;
1460 while ((my $column)=$sth2->fetchrow){
1461 my ($tagfield,$tagsubfield) = &MARCfind_marc_from_kohafield($dbh,"items.".$column,"");
1462 $subfieldstosearch{$column}=$tagsubfield;
1463 }
1464
1465                 for ($i=$startfrom; $i<(($startfrom+$num<=$numresults) ? ($startfrom+$num):$numresults) ; $i++){
1466
1467                         my $rec=$oResult->record($i);
1468
1469                 $marcdata = $rec->raw();
1470                 my $marcrecord;
1471         $marcrecord = MARC::File::USMARC::decode($marcdata);
1472 #       $marcrecord=MARC::Record->new_from_xml( $marcdata,'UTF-8' );
1473 #       $marcrecord->encoding( 'UTF-8' );
1474         my $oldbiblio = MARCmarc2koha($dbh,$marcrecord,'');
1475
1476         &add_html_bold_fields($type,$oldbiblio,$search);
1477         if ($i % 2) {
1478                 $toggle="#ffffcc";
1479         } else {
1480                 $toggle="white";
1481         }
1482         $oldbiblio->{'toggle'}=$toggle;
1483
1484
1485
1486  my @fields = $marcrecord->field($itemtag);
1487 my @items;
1488  my $item;
1489 my %counts;
1490 $counts{'total'}=0;
1491
1492 #
1493 ##Loop for each item field
1494      foreach my $field (@fields) {
1495        foreach my $code ( keys %subfieldstosearch ) {
1496
1497 $item->{$code}=$field->subfield($subfieldstosearch{$code});
1498 }
1499
1500 my $status;
1501
1502 $item->{'branchname'}=$branches{$item->{'holdingbranch'}};
1503 $item->{'shelves'}=$shelves{$item->{stack}};
1504 $status="Lost" if ($item->{'itemlost'}>0);
1505 $status="Withdrawn" if ($item->{'wthdrawn'}>0);
1506 if ($search->{'from'} eq "intranet"){
1507 $search->{'avoidquerylog'}=1;
1508 $status="Due:".format_date($item->{'onloan'}) if ($item->{'onloan'}>0);
1509  $status = $item->{'holdingbranch'}."-".$item->{'stack'}."[".$item->{'itemcallnumber'}."]" unless defined $status;
1510 }else{
1511 $status="On Loan" if ($item->{'onloan'}>0);
1512    $status = $item->{'branchname'}."[".$item->{'shelves'}."]" unless defined $status;
1513 }
1514  $counts{$status}++;
1515 $counts{'total'}++;
1516 push @items,$item;
1517 #$oldbiblio->{'itemcount'}++;
1518         }
1519
1520                 my $norequests = 1;
1521                 my $noitems    = 1;
1522                 if (@items) {
1523                         $noitems = 0;
1524                         foreach my $itm (@items) {
1525                                 $norequests = 0 unless $itm->{'itemnotforloan'};
1526                         }
1527                 }
1528                 $oldbiblio->{'noitems'} = $noitems;
1529                 $oldbiblio->{'norequests'} = $norequests;
1530                 $oldbiblio->{'even'} = $even = not $even;
1531                 $oldbiblio->{'itemcount'} = $counts{'total'};
1532
1533                 my $totalitemcounts = 0;
1534                 foreach my $key (keys %counts){
1535                         if ($key ne 'total'){
1536                                 $totalitemcounts+= $counts{$key};
1537                                 $oldbiblio->{'locationhash'}->{$key}=$counts{$key};
1538                         }
1539                 }
1540
1541                 my ($locationtext, $locationtextonly, $notavailabletext) = ('','','');
1542                 foreach (sort keys %{$oldbiblio->{'locationhash'}}) {
1543                         if ($_ eq 'notavailable') {
1544                                 $notavailabletext="Not available";
1545                                 my $c=$oldbiblio->{'locationhash'}->{$_};
1546                                 $oldbiblio->{'not-available-p'}=$c;
1547                         } else {
1548                                 $locationtext.="$_";
1549                                 my $c=$oldbiblio->{'locationhash'}->{$_};
1550                                 if ($_ eq 'Item Lost') {
1551                                         $oldbiblio->{'lost-p'} = $c;
1552                                 } elsif ($_ eq 'Withdrawn') {
1553                                         $oldbiblio->{'withdrawn-p'} = $c;
1554                                 } elsif ($_ eq 'On Loan') {
1555                                         $oldbiblio->{'on-loan-p'} = $c;
1556                                 } else {
1557                                         $locationtextonly.= $_;
1558                                         $locationtextonly.= " ($c)<br> " if $totalitemcounts > 1;
1559                                 }
1560                                 if ($totalitemcounts>1) {
1561                                         $locationtext.=" ($c)<br> ";
1562                                 }
1563                         }
1564                 }
1565                 if ($notavailabletext) {
1566                         $locationtext.= $notavailabletext;
1567                 } else {
1568                         $locationtext=~s/, $//;
1569                 }
1570                 $oldbiblio->{'location'} = $locationtext;
1571                 $oldbiblio->{'location-only'} = $locationtextonly;
1572                 $oldbiblio->{'use-location-flags-p'} = 1;
1573
1574         push (@results, $oldbiblio);
1575
1576                 }
1577 #               $i++;
1578         }
1579 #$oConnection->destroy();
1580         my $count = $numresults;
1581
1582         unless ($search->{'avoidquerylog'}) {
1583                 add_query_line($type, $search, $count);}
1584         return($count,@results);
1585 }
1586
1587
1588 sub FindDuplicate {
1589         my ($record)=@_;
1590 my $dbh=C4::Context->dbh;
1591         my $result = MARCmarc2koha($dbh,$record,'');
1592         my $sth;
1593         my $query;
1594         my $search;
1595         my  $type;
1596         my ($biblionumber,$bibid,$title);
1597         # search duplicate on ISBN, easy and fast..
1598 $search->{'avoidquerylog'}=1;
1599         if ($result->{isbn}) {
1600         $type="precise";
1601 ###Temporary fix for ISBN
1602 my $isbn=$result->{isbn};
1603 $isbn=~ s/(\.|\?|\;|\=|\/|\\|\||\:|\!|\'|,|\-|\"|\(|\)|\[|\]|\{|\}|\/)//g;
1604                 $search->{'isbn'}=$isbn;
1605                         }else{
1606 $result->{title}=~s /\\//g;
1607 $result->{title}=~s /\"//g;
1608         $type="loose";
1609         $search->{'field_name1'}="title";
1610         $search->{'field_value1'}=$result->{title};
1611         $search->{'ttype1'}="exact";
1612         $search->{'atype1'}="start";
1613         }
1614         my ($total,@result)=CatSearch4($type,$search,1,0);
1615                 return $result[0]->{'biblionumber'}, $result[0]->{'biblionumber'},$result[0]->{'title'} if ($total);
1616
1617 }
1618 =item KeywordSearch
1619
1620   $search = { "keyword" => "One or more keywords",
1621               "class"   => "VID|CD",    # Limit search to fiction and CDs
1622               "dewey"   => "813",
1623          };
1624   ($count, @results) = &KeywordSearch($env, $type, $search, $num, $offset);
1625
1626 C<&KeywordSearch> searches the catalog by keyword: given a string
1627 (C<$search-E<gt>{"keyword"}> consisting of a space-separated list of
1628 keywords, it looks for books that contain any of those keywords in any
1629 of a number of places.
1630
1631 C<&KeywordSearch> looks for keywords in the book title (and subtitle),
1632 series name, notes (both C<biblio.notes> and C<biblioitems.notes>),
1633 and subjects.
1634
1635 C<$search-E<gt>{"class"}> can be set to a C<|> (pipe)-separated list of
1636 item class codes (e.g., "F" for fiction, "JNF" for junior nonfiction,
1637 etc.). In this case, the search will be restricted to just those
1638 classes.
1639
1640 If C<$search-E<gt>{"class"}> is not specified, you may specify
1641 C<$search-E<gt>{"dewey"}>. This will restrict the search to that
1642 particular Dewey Decimal Classification category. Setting
1643 C<$search-E<gt>{"dewey"}> to "513" will return books about arithmetic,
1644 whereas setting it to "5" will return all books with Dewey code 5I<xx>
1645 (Science and Mathematics).
1646
1647 C<$env> and C<$type> are ignored.
1648
1649 C<$offset> and C<$num> specify the subset of results to return.
1650 C<$num> specifies the number of results to return, and C<$offset> is
1651 the number of the first result. Thus, setting C<$offset> to 100 and
1652 C<$num> to 5 will return results 100 through 104 inclusive.
1653
1654 =cut
1655 #'
1656 sub KeywordSearch {
1657   my ($env,$type,$search,$num,$offset)=@_;
1658   my $dbh = C4::Context->dbh;
1659   $search->{'keyword'}=~ s/ +$//;
1660   my @key=split(' ',$search->{'keyword'});
1661                 # FIXME - Naive users might enter comma-separated
1662                 # words, e.g., "training, animal". Ought to cope with
1663                 # this.
1664   my $count=@key;
1665   my $i=1;
1666   my %biblionumbers;            # Set of biblionumbers returned by the
1667                                 # various searches.
1668
1669   # FIXME - Ought to filter the stopwords out of the list of keywords.
1670   #     @key = map { !defined($stopwords{$_}) } @key;
1671
1672   # FIXME - The way this code is currently set up, it looks for all of
1673   # the keywords first in (title, notes, seriestitle), then in the
1674   # subtitle, then in the subject. Thus, if you look for keywords
1675   # "science fiction", this search won't find a book with
1676   #     title    = "How to write fiction"
1677   #     subtitle = "A science-based approach"
1678   # Is this the desired effect? If not, then the first SQL query
1679   # should look in the biblio, subtitle, and subject tables all at
1680   # once. The way the first query is built can accomodate this easily.
1681
1682   # Look for keywords in table 'biblio'.
1683
1684   # Build an SQL query that finds each of the keywords in any of the
1685   # title, biblio.notes, or seriestitle. To do this, we'll build up an
1686   # array of clauses, one for each keyword.
1687   my $query;                    # The SQL query
1688   my @clauses = ();             # The search clauses
1689   my @bind = ();                # The term bindings
1690
1691   $query = <<EOT;               # Beginning of the query
1692         SELECT  biblionumber
1693         FROM    biblio
1694         WHERE
1695 EOT
1696   foreach my $keyword (@key)
1697   {
1698     my @subclauses = ();        # Subclauses, one for each field we're
1699                                 # searching on
1700
1701     # For each field we're searching on, create a subclause that'll
1702     # match the current keyword in the current field.
1703     foreach my $field (qw(title notes seriestitle author))
1704     {
1705       push @subclauses,
1706         "$field LIKE ? OR $field LIKE ?";
1707           push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
1708     }
1709     # (Yes, this could have been done as
1710     #   @subclauses = map {...} qw(field1 field2 ...)
1711     # )but I think this way is more readable.
1712
1713     # Construct the current clause by joining the subclauses.
1714     push @clauses, "(" . join(")\n\tOR (", @subclauses) . ")";
1715   }
1716   # Now join all of the clauses together and append to the query.
1717   $query .= "(" . join(")\nAND (", @clauses) . ")";
1718
1719   # FIXME - Perhaps use $sth->bind_columns() ? Documented as the most
1720   # efficient way to fetch data.
1721   my $sth=$dbh->prepare($query);
1722   $sth->execute(@bind);
1723   while (my @res = $sth->fetchrow_array) {
1724     for (@res)
1725     {
1726         $biblionumbers{$_} = 1;         # Add these results to the set
1727     }
1728   }
1729   $sth->finish;
1730
1731   # Now look for keywords in the 'bibliosubtitle' table.
1732
1733   # Again, we build a list of clauses from the keywords.
1734   @clauses = ();
1735   @bind = ();
1736   $query = "SELECT biblionumber FROM bibliosubtitle WHERE ";
1737   foreach my $keyword (@key)
1738   {
1739     push @clauses,
1740         "subtitle LIKE ? OR subtitle like ?";
1741         push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
1742   }
1743   $query .= "(" . join(") AND (", @clauses) . ")";
1744
1745   $sth=$dbh->prepare($query);
1746   $sth->execute(@bind);
1747   while (my @res = $sth->fetchrow_array) {
1748     for (@res)
1749     {
1750         $biblionumbers{$_} = 1;         # Add these results to the set
1751     }
1752   }
1753   $sth->finish;
1754
1755   # Look for the keywords in the notes for individual items
1756   # ('biblioitems.notes')
1757
1758   # Again, we build a list of clauses from the keywords.
1759   @clauses = ();
1760   @bind = ();
1761   $query = "SELECT biblionumber FROM biblioitems WHERE ";
1762   foreach my $keyword (@key)
1763   {
1764     push @clauses,
1765         "notes LIKE ? OR notes like ?";
1766         push(@bind,"\Q$keyword\E%","% \Q$keyword\E%");
1767   }
1768   $query .= "(" . join(") AND (", @clauses) . ")";
1769
1770   $sth=$dbh->prepare($query);
1771   $sth->execute(@bind);
1772   while (my @res = $sth->fetchrow_array) {
1773     for (@res)
1774     {
1775         $biblionumbers{$_} = 1;         # Add these results to the set
1776     }
1777   }
1778   $sth->finish;
1779
1780   # Look for keywords in the 'bibliosubject' table.
1781
1782   # FIXME - The other queries look for words in the desired field that
1783   # begin with the individual keywords the user entered. This one
1784   # searches for the literal string the user entered. Is this the
1785   # desired effect?
1786   # Note in particular that spaces are retained: if the user typed
1787   #     science  fiction
1788   # (with two spaces), this won't find the subject "science fiction"
1789   # (one space). Likewise, a search for "%" will return absolutely
1790   # everything.
1791   # If this isn't the desired effect, see the previous searches for
1792   # how to do it.
1793
1794   $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
1795   like ? group by biblionumber");
1796   $sth->execute("%$search->{'keyword'}%");
1797
1798   while (my @res = $sth->fetchrow_array) {
1799     for (@res)
1800     {
1801         $biblionumbers{$_} = 1;         # Add these results to the set
1802     }
1803   }
1804   $sth->finish;
1805
1806   my $i2=0;
1807   my $i3=0;
1808   my $i4=0;
1809
1810   my @res2;
1811   my @res = keys %biblionumbers;
1812   $count=@res;
1813
1814   $i=0;
1815 #  print "count $count";
1816   if ($search->{'class'} ne ''){
1817     while ($i2 <$count){
1818       my $query="select * from biblio,biblioitems where
1819       biblio.biblionumber=? and
1820       biblio.biblionumber=biblioitems.biblionumber ";
1821       my @bind = ($res[$i2]);
1822       if ($search->{'class'} ne ''){    # FIXME - Redundant
1823       my @temp=split(/\|/,$search->{'class'});
1824       my $count=@temp;
1825       $query.= "and ( itemtype=?";
1826       push(@bind,$temp[0]);
1827       for (my $i=1;$i<$count;$i++){
1828         $query.=" or itemtype=?";
1829         push(@bind,$temp[$i]);
1830       }
1831       $query.=")";
1832       }
1833        my $sth=$dbh->prepare($query);
1834        #    print $query;
1835        $sth->execute(@bind);
1836        if (my $data2=$sth->fetchrow_hashref){
1837          my $dewey= $data2->{'dewey'};
1838          my $subclass=$data2->{'subclass'};
1839          # FIXME - This next bit is bogus, because it assumes that the
1840          # Dewey code is a floating-point number. It isn't. It's
1841          # actually a string that mainly consists of numbers. In
1842          # particular, "4" is not a valid Dewey code, although "004"
1843          # is ("Data processing; Computer science"). Likewise, zeros
1844          # after the decimal are significant ("575" is not the same as
1845          # "575.0"; the latter is more specific). And "000" is a
1846          # perfectly good Dewey code ("General works; computer
1847          # science") and should not be interpreted to mean "this
1848          # database entry does not have a Dewey code". That's what
1849          # NULL is for.
1850          $dewey=~s/\.*0*$//;
1851          ($dewey == 0) && ($dewey='');
1852          ($dewey) && ($dewey.=" $subclass") ;
1853           $sth->finish;
1854           my $end=$offset +$num;
1855           if ($i4 <= $offset){
1856             $i4++;
1857           }
1858 #         print $i4;
1859           if ($i4 <=$end && $i4 > $offset){
1860             $data2->{'dewey'}=$dewey;
1861             $res2[$i3]=$data2;
1862
1863 #           $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
1864             $i3++;
1865             $i4++;
1866 #           print "in here $i3<br>";
1867           } else {
1868 #           print $end;
1869           }
1870           $i++;
1871         }
1872      $i2++;
1873      }
1874      $count=$i;
1875
1876    } else {
1877   # $search->{'class'} was not specified
1878
1879   # FIXME - This is bogus: it makes a separate query for each
1880   # biblioitem, and returns results in apparently random order. It'd
1881   # be much better to combine all of the previous queries into one big
1882   # one (building it up a little at a time, of course), and have that
1883   # big query select all of the desired fields, instead of just
1884   # 'biblionumber'.
1885
1886   while ($i2 < $num && $i2 < $count){
1887     my $query="select * from biblio,biblioitems where
1888     biblio.biblionumber=? and
1889     biblio.biblionumber=biblioitems.biblionumber ";
1890     my @bind=($res[$i2+$offset]);
1891
1892     if ($search->{'dewey'} ne ''){
1893       $query.= "and (dewey like ?)";
1894       push(@bind,"$search->{'dewey'}%");
1895     }
1896
1897     my $sth=$dbh->prepare($query);
1898 #    print $query;
1899     $sth->execute(@bind);
1900     if (my $data2=$sth->fetchrow_hashref){
1901         my $dewey= $data2->{'dewey'};
1902         my $subclass=$data2->{'subclass'};
1903         $dewey=~s/\.*0*$//;
1904         ($dewey == 0) && ($dewey='');
1905         ($dewey) && ($dewey.=" $subclass") ;
1906         $sth->finish;
1907         $data2->{'dewey'}=$dewey;
1908
1909         $res2[$i]=$data2;
1910 #       $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
1911         $i++;
1912     }
1913     $i2++;
1914
1915   }
1916   }
1917
1918   #$count=$i;
1919   return($count,@res2);
1920 }
1921
1922 sub KeywordSearch2 {
1923   my ($env,$type,$search,$num,$offset)=@_;
1924   my $dbh = C4::Context->dbh;
1925   $search->{'keyword'}=~ s/ +$//;
1926   my @key=split(' ',$search->{'keyword'});
1927   my $count=@key;
1928   my $i=1;
1929   my @results;
1930   my $query ="Select * from biblio,bibliosubtitle,biblioitems where
1931   biblio.biblionumber=biblioitems.biblionumber and
1932   biblio.biblionumber=bibliosubtitle.biblionumber and
1933   (((title like ? or title like ?)";
1934   my @bind=("$key[0]%","% $key[0]%");
1935   while ($i < $count){
1936     $query .= " and (title like ? or title like ?)";
1937     push(@bind,"$key[$i]%","% $key[$i]%");
1938     $i++;
1939   }
1940   $query.= ") or ((subtitle like ? or subtitle like ?)";
1941   push(@bind,"$key[0]%","% $key[0]%");
1942   for ($i=1;$i<$count;$i++){
1943     $query.= " and (subtitle like ? or subtitle like ?)";
1944     push(@bind,"$key[$i]%","% $key[$i]%");
1945   }
1946   $query.= ") or ((seriestitle like ? or seriestitle like ?)";
1947   push(@bind,"$key[0]%","% $key[0]%");
1948   for ($i=1;$i<$count;$i++){
1949     $query.=" and (seriestitle like ? or seriestitle like ?)";
1950     push(@bind,"$key[$i]%","% $key[$i]%");
1951   }
1952   $query.= ") or ((biblio.notes like ? or biblio.notes like ?)";
1953   push(@bind,"$key[0]%","% $key[0]%");
1954   for ($i=1;$i<$count;$i++){
1955     $query.=" and (biblio.notes like ? or biblio.notes like ?)";
1956     push(@bind,"$key[$i]%","% $key[$i]%");
1957   }
1958   $query.= ") or ((biblioitems.notes like ? or biblioitems.notes like ?)";
1959   push(@bind,"$key[0]%","% $key[0]%");
1960   for ($i=1;$i<$count;$i++){
1961     $query.=" and (biblioitems.notes like ? or biblioitems.notes like ?)";
1962     push(@bind,"$key[$i]%","% $key[$i]%");
1963   }
1964   if ($search->{'keyword'} =~ /new zealand/i){
1965     $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
1966     or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
1967     or author like '% nz %' or author like '% nz')"
1968   }
1969   if ($search->{'keyword'} eq  'nz' || $search->{'keyword'} eq 'NZ' ||
1970   $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
1971   $search->{'keyword'} =~ / nz/i){
1972     $query.= "or (title like 'new zealand%' or title like '% new zealand %'
1973     or title like '% new zealand' or subtitle like 'new zealand%' or
1974     subtitle like '% new zealand %'
1975     or subtitle like '% new zealand' or author like 'new zealand%'
1976     or author like '% new zealand %' or author like '% new zealand' or
1977     seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
1978     or seriestitle like '% new zealand')"
1979   }
1980   $query .= "))";
1981   if ($search->{'class'} ne ''){
1982     my @temp=split(/\|/,$search->{'class'});
1983     my $count=@temp;
1984     $query.= "and ( itemtype=?";
1985     push(@bind,"$temp[0]");
1986     for (my $i=1;$i<$count;$i++){
1987       $query.=" or itemtype=?";
1988       push(@bind,"$temp[$i]");
1989      }
1990   $query.=")";
1991   }
1992   if ($search->{'dewey'} ne ''){
1993     $query.= "and (dewey like '$search->{'dewey'}%') ";
1994   }
1995    $query.="group by biblio.biblionumber";
1996    #$query.=" order by author,title";
1997 #  print $query;
1998   my $sth=$dbh->prepare($query);
1999   $sth->execute(@bind);
2000   $i=0;
2001   while (my $data=$sth->fetchrow_hashref){
2002 #FIXME: rewrite to use ? before uncomment
2003 #    my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
2004 #    ");
2005 #    $sti->execute;
2006 #    my ($dewey, $subclass) = $sti->fetchrow;
2007     my $dewey=$data->{'dewey'};
2008     my $subclass=$data->{'subclass'};
2009     $dewey=~s/\.*0*$//;
2010     ($dewey == 0) && ($dewey='');
2011     ($dewey) && ($dewey.=" $subclass");
2012 #    $sti->finish;
2013     $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
2014 #      print $results[$i];
2015     $i++;
2016   }
2017   $sth->finish;
2018   $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
2019   like ? group by biblionumber");
2020   $sth->execute("%".$search->{'keyword'}."%");
2021   while (my $data=$sth->fetchrow_hashref){
2022     $query="Select * from biblio,biblioitems where
2023     biblio.biblionumber=? and
2024     biblio.biblionumber=biblioitems.biblionumber ";
2025     @bind=($data->{'biblionumber'});
2026     if ($search->{'class'} ne ''){
2027       my @temp=split(/\|/,$search->{'class'});
2028       my $count=@temp;
2029       $query.= " and ( itemtype=?";
2030       push(@bind,$temp[0]);
2031       for (my $i=1;$i<$count;$i++){
2032         $query.=" or itemtype=?";
2033         push(@bind,$temp[$i]);
2034       }
2035       $query.=")";
2036
2037     }
2038     if ($search->{'dewey'} ne ''){
2039       $query.= "and (dewey like ?)";
2040       push(@bind,"$search->{'dewey'}%");
2041     }
2042     my $sth2=$dbh->prepare($query);
2043     $sth2->execute(@bind);
2044 #    print $query;
2045     while (my $data2=$sth2->fetchrow_hashref){
2046       my $dewey= $data2->{'dewey'};
2047       my $subclass=$data2->{'subclass'};
2048       $dewey=~s/\.*0*$//;
2049       ($dewey == 0) && ($dewey='');
2050       ($dewey) && ($dewey.=" $subclass") ;
2051 #      $sti->finish;
2052        $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
2053 #      print $results[$i];
2054       $i++;
2055     }
2056     $sth2->finish;
2057   }
2058   my $i2=1;
2059   @results=sort @results;
2060   my @res;
2061   $count=@results;
2062   $i=1;
2063   if ($count > 0){
2064     $res[0]=$results[0];
2065   }
2066   while ($i2 < $count){
2067     if ($results[$i2] ne $res[$i-1]){
2068       $res[$i]=$results[$i2];
2069       $i++;
2070     }
2071     $i2++;
2072   }
2073   $i2=0;
2074   my @res2;
2075   $count=@res;
2076   while ($i2 < $num && $i2 < $count){
2077     $res2[$i2]=$res[$i2+$offset];
2078 #    print $res2[$i2];
2079     $i2++;
2080   }
2081   $sth->finish;
2082 #  $i--;
2083 #  $i++;
2084   return($i,@res2);
2085 }
2086
2087
2088 sub add_query_line {
2089
2090         my ($type,$search,$results)=@_;
2091         my $dbh = C4::Context->dbh;
2092         my $searchdesc = '';
2093         my $from;
2094         my $borrowernumber = $search->{'borrowernumber'};
2095         my $remote_IP = $search->{'remote_IP'};
2096         my $remote_URL= $search->{'remote_URL'};
2097         my $searchmode = '';
2098         my $searchlinkdesc = '';
2099
2100         if ($search->{'from'}) {
2101                 $from = $search->{'from'};
2102         } else {
2103                 $from = 'opac'
2104         }
2105
2106         if ($type eq 'keyword') {
2107                 $searchdesc = $search->{'keyword'};
2108                 if ($search->{'ttype'} eq 'exact') {
2109                         $searchmode = 'phrase';
2110                 } else {
2111                         $searchmode = 'any word';
2112                 }
2113                 $searchlinkdesc.= "search_type=keyword&keyword=$search->{'keyword'}&ttype=$search->{'ttype'}";
2114
2115         } elsif ($type eq 'precise') {
2116                 if ($search->{'itemnumber'}) {
2117                         $searchdesc = "barcode = $search->{'itemnumber'}";
2118                         $searchlinkdesc.= "search_type=precise&itemnumber=$search->{'itemnumber'}";
2119                 } else {
2120                         $searchdesc = "isbn = $search->{'itemnumber'}";
2121                         $searchlinkdesc.= "search_type=precise&itemnumber=$search->{'isbn'}";
2122                 }
2123
2124         } elsif ($type eq 'recently_items') {
2125                 $searchdesc = "$search->{'range'}";
2126                 $searchlinkdesc.= "recently_items=1&search=$search->{'range'}";
2127         } else {
2128                 $searchlinkdesc.= "search_type=loose";
2129                 if ( ($search->{"field_name1"}) && ($search->{"field_value1"}) ) {
2130                         if ($search->{"ttype1"} eq 'exact') {
2131                                 $searchmode.= ' starting with ';
2132                         } else {
2133                                 $searchmode.= ' containing ';
2134                         }
2135                         $searchdesc.= " | " . $search->{"field_name1"} . " = " . $search->{"field_value1"} . " | ";
2136                         $searchlinkdesc.= "&ttype=$search->{'ttype1'}&field_name1=$search->{'field_name1'}&field_value1=$search->{'field_value1'}";
2137                 }
2138
2139                 if ( ($search->{"field_name2"}) && ($search->{"field_value2"}) ) {
2140                         if ($search->{"ttype2"} eq 'exact') {
2141                                 $searchmode.= ' | starting with ';
2142                         } else {
2143                                 $searchmode.= ' | containing ';
2144                         }
2145                         $searchdesc.= uc($search->{"op1"});
2146                         $searchdesc.= " | " . $search->{"field_name2"} . " = " . $search->{"field_value2"} . " | ";
2147                         $searchlinkdesc.= "&op1=$search->{'op1'}&ttype=$search->{'ttype2'}&field_name2=$search->{'field_name2'}&field_value2=$search->{'field_value2'}";
2148                 }
2149
2150                 if ( ($search->{"field_name3"}) && ($search->{"field_value3"}) ) {
2151                         if ($search->{"ttype3"} eq 'exact') {
2152                                 $searchmode.= ' | starting with ';
2153                         } else {
2154                                 $searchmode.= ' | containing ';
2155                         }
2156                         $searchdesc.= uc($search->{"op2"});
2157                         $searchdesc.= " | " . $search->{"field_name3"} . " = " . $search->{"field_value3"} . " | ";
2158                         $searchlinkdesc.= "&op2=$search->{'op2'}&ttype=$search->{'ttype3'}&field_name3=$search->{'field_name3'}&field_value3=$search->{'field_value3'}";
2159                 }
2160         }
2161
2162         if ($search->{'branch'}) {
2163                 $searchdesc.= " AND branch = $search->{'branch'}";
2164                 $searchlinkdesc.= "&branch=$search->{'branch'}";
2165         }
2166         if ($search->{'class'}) {
2167                 $searchdesc.= " AND itemtype = $search->{'class'}";
2168                 $searchlinkdesc.= "&class=$search->{'class'}";
2169         }
2170
2171 #       my $sth = $dbh->prepare("INSERT INTO querys_log (searchtype, searchdesc, searchmode, borrowernumber, number_of_results, date, execute_from, remote_IP, linkdesc) VALUES (?,?,?,?,?,NOW(),?,?,?)");
2172 #       $sth->execute($type, $searchdesc, $searchmode, $borrowernumber, $results, $from, $remote_IP, $searchlinkdesc);
2173 #       $sth->finish;
2174 my $sth = $dbh->prepare("INSERT INTO phrase_log(phr_phrase,phr_resultcount,phr_ip,user,actual) VALUES(?,?,?,?,?)");
2175
2176
2177 $sth->execute($searchdesc,$results,$remote_IP,$borrowernumber,$remote_URL);
2178 $sth->finish;
2179
2180 }
2181
2182
2183 =item CatSearch
2184
2185   ($count, @results) = &CatSearch($env, $type, $search, $num, $offset);
2186
2187 C<&CatSearch> searches the Koha catalog. It returns a list whose first
2188 element is the number of returned results, and whose subsequent
2189 elements are the results themselves.
2190
2191 Each returned element is a reference-to-hash. Most of the keys are
2192 simply the fields from the C<biblio> table in the Koha database, but
2193 the following keys may also be present:
2194
2195 =over 4
2196
2197 =item C<illustrator>
2198
2199 The book's illustrator.
2200
2201 =item C<publisher>
2202
2203 The publisher.
2204
2205 =back
2206
2207 C<$env> is ignored.
2208
2209 C<$type> may be C<subject>, C<loose>, or C<precise>. This controls the
2210 high-level behavior of C<&CatSearch>, as described below.
2211
2212 In many cases, the description below says that a certain field in the
2213 database must match the search string. In these cases, it means that
2214 the beginning of some word in the field must match the search string.
2215 Thus, an author search for "sm" will return books whose author is
2216 "John Smith" or "Mike Smalls", but not "Paul Grossman", since the "sm"
2217 does not occur at the beginning of a word.
2218
2219 Note that within each search mode, the criteria are and-ed together.
2220 That is, if you perform a loose search on the author "Jerome" and the
2221 title "Boat", the search will only return books by Jerome containing
2222 "Boat" in the title.
2223
2224 It is not possible to cross modes, e.g., set the author to "Asimov"
2225 and the subject to "Math" in hopes of finding books on math by Asimov.
2226
2227 =head2 Loose search
2228
2229 If C<$type> is set to C<loose>, the following search criteria may be
2230 used:
2231
2232 =over 4
2233
2234 =item C<$search-E<gt>{author}>
2235
2236 The search string is a space-separated list of words. Each word must
2237 match either the C<author> or C<additionalauthors> field.
2238
2239 =item C<$search-E<gt>{title}>
2240
2241 Each word in the search string must match the book title. If no author
2242 is specified, the book subtitle will also be searched.
2243
2244 =item C<$search-E<gt>{abstract}>
2245
2246 Searches for the given search string in the book's abstract.
2247
2248 =item C<$search-E<gt>{'date-before'}>
2249
2250 Searches for books whose copyright date matches the search string.
2251 That is, setting C<$search-E<gt>{'date-before'}> to "1985" will find
2252 books written in 1985, and setting it to "198" will find books written
2253 between 1980 and 1989.
2254
2255 =item C<$search-E<gt>{title}>
2256
2257 Searches by title are also affected by the value of
2258 C<$search-E<gt>{"ttype"}>; if it is set to C<exact>, then the book
2259 title, (one of) the series titleZ<>(s), or (one of) the unititleZ<>(s) must
2260 match the search string exactly (the subtitle is not searched).
2261
2262 If C<$search-E<gt>{"ttype"}> is set to anything other than C<exact>,
2263 each word in the search string must match the title, subtitle,
2264 unititle, or series title.
2265
2266 =item C<$search-E<gt>{class}>
2267
2268 Restricts the search to certain item classes. The value of
2269 C<$search-E<gt>{"class"}> is a | (pipe)-separated list of item types.
2270 Thus, setting it to "F" restricts the search to fiction, and setting
2271 it to "CD|CAS" will only look in compact disks and cassettes.
2272
2273 =item C<$search-E<gt>{dewey}>
2274
2275 Searches for books whose Dewey Decimal Classification code matches the
2276 search string. That is, setting C<$search-E<gt>{"dewey"}> to "5" will
2277 search for all books in 5I<xx> (Science and mathematics), setting it
2278 to "54" will search for all books in 54I<x> (Chemistry), and setting
2279 it to "546" will search for books on inorganic chemistry.
2280
2281 =item C<$search-E<gt>{publisher}>
2282
2283 Searches for books whose publisher contains the search string (unlike
2284 other search criteria, C<$search-E<gt>{publisher}> is a string, not a
2285 set of words.
2286
2287 =back
2288
2289 =head2 Subject search
2290
2291 If C<$type> is set to C<subject>, the following search criterion may
2292 be used:
2293
2294 =over 4
2295
2296 =item C<$search-E<gt>{subject}>
2297
2298 The search string is a space-separated list of words, each of which
2299 must match the book's subject.
2300
2301 Special case: if C<$search-E<gt>{subject}> is set to C<nz>,
2302 C<&CatSearch> will search for books whose subject is "New Zealand".
2303 However, setting C<$search-E<gt>{subject}> to C<"nz football"> will
2304 search for books on "nz" and "football", not books on "New Zealand"
2305 and "football".
2306
2307 =back
2308
2309 =head2 Precise search
2310
2311 If C<$type> is set to C<precise>, the following search criteria may be
2312 used:
2313
2314 =over 4
2315
2316 =item C<$search-E<gt>{item}>
2317
2318 Searches for books whose barcode exactly matches the search string.
2319
2320 =item C<$search-E<gt>{isbn}>
2321
2322 Searches for books whose ISBN exactly matches the search string.
2323
2324 =back
2325
2326 For a loose search, if an author was specified, the results are
2327 ordered by author and title. If no author was specified, the results
2328 are ordered by title.
2329
2330 For other (non-loose) searches, if a subject was specified, the
2331 results are ordered alphabetically by subject.
2332
2333 In all other cases (e.g., loose search by keyword), the results are
2334 not ordered.
2335
2336 =cut
2337 #'
2338 sub CatSearch  {
2339         my ($env,$type,$search,$num,$offset)=@_;
2340         my $dbh = C4::Context->dbh;
2341         my $query = '';
2342         my @bind = ();
2343         my @results;
2344
2345         my $title = lc($search->{'title'});
2346
2347         if ($type eq 'loose') {
2348                 if ($search->{'author'} ne ''){
2349                         my @key=split(' ',$search->{'author'});
2350                         my $count=@key;
2351                         my $i=1;
2352                         $query="select *,biblio.author,biblio.biblionumber from
2353                                                         biblio
2354                                                         left join additionalauthors
2355                                                         on additionalauthors.biblionumber =biblio.biblionumber
2356                                                         where
2357                                                         ((biblio.author like ? or biblio.author like ? or
2358                                                         additionalauthors.author like ? or additionalauthors.author
2359                                                         like ?
2360                                                                 )";
2361                         @bind=("$key[0]%","% $key[0]%","$key[0]%","% $key[0]%");
2362                         while ($i < $count){
2363                                         $query .= " and (
2364                                                                         biblio.author like ? or biblio.author like ? or
2365                                                                         additionalauthors.author like ? or additionalauthors.author like ?
2366                                                                         )";
2367                                         push(@bind,"$key[$i]%","% $key[$i]%","$key[$i]%","% $key[$i]%");
2368                                 $i++;
2369                         }
2370                         $query .= ")";
2371                         if ($search->{'title'} ne ''){
2372                                 my @key=split(' ',$search->{'title'});
2373                                 my $count=@key;
2374                                 my $i=0;
2375                                 $query.= " and (((title like ? or title like ?)";
2376                                 push(@bind,"$key[0]%","% $key[0]%");
2377                                 while ($i<$count){
2378                                         $query .= " and (title like ? or title like ?)";
2379                                         push(@bind,"$key[$i]%","% $key[$i]%");
2380                                         $i++;
2381                                 }
2382                                 $query.=") or ((seriestitle like ? or seriestitle like ?)";
2383                                 push(@bind,"$key[0]%","% $key[0]%");
2384                                 for ($i=1;$i<$count;$i++){
2385                                         $query.=" and (seriestitle like ? or seriestitle like ?)";
2386                                         push(@bind,"$key[$i]%","% $key[$i]%");
2387                                         }
2388                                 $query.=") or ((unititle like ? or unititle like ?)";
2389                                 push(@bind,"$key[0]%","% $key[0]%");
2390                                 for ($i=1;$i<$count;$i++){
2391                                         $query.=" and (unititle like ? or unititle like ?)";
2392                                         push(@bind,"$key[$i]%","% $key[$i]%");
2393                                         }
2394                                 $query .= "))";
2395                         }
2396                         if ($search->{'abstract'} ne ''){
2397                                 $query.= " and (abstract like ?)";
2398                                 push(@bind,"%$search->{'abstract'}%");
2399                         }
2400                         if ($search->{'date-before'} ne ''){
2401                                 $query.= " and (copyrightdate like ?)";
2402                                 push(@bind,"%$search->{'date-before'}%");
2403                         }
2404                         $query.=" group by biblio.biblionumber";
2405                 } else {
2406                         if ($search->{'title'} ne '') {
2407                                 if ($search->{'ttype'} eq 'exact'){
2408                                         $query="select * from biblio
2409                                         where
2410                                         (biblio.title=? or (biblio.unititle = ?
2411                                         or biblio.unititle like ? or
2412                                         biblio.unititle like ? or
2413                                         biblio.unititle like ?) or
2414                                         (biblio.seriestitle = ? or
2415                                         biblio.seriestitle like ? or
2416                                         biblio.seriestitle like ? or
2417                                         biblio.seriestitle like ?)
2418                                         )";
2419                                         @bind=($search->{'title'},$search->{'title'},"$search->{'title'} |%","%| $search->{'title'} |%","%| $search->{'title'}",$search->{'title'},"$search->{'title'} |%","%| $search->{'title'} |%","%| $search->{'title'}");
2420                                 } else {
2421                                         my @key=split(' ',$search->{'title'});
2422                                         my $count=@key;
2423                                         my $i=1;
2424                                         $query="select biblio.biblionumber,author,title,unititle,notes,abstract,serial,seriestitle,copyrightdate,timestamp,subtitle from biblio
2425                                         left join bibliosubtitle on
2426                                         biblio.biblionumber=bibliosubtitle.biblionumber
2427                                         where
2428                                         (((title like ? or title like ?)";
2429                                         @bind=("$key[0]%","% $key[0]%");
2430                                         while ($i<$count){
2431                                                 $query .= " and (title like ? or title like ?)";
2432                                                 push(@bind,"$key[$i]%","% $key[$i]%");
2433                                                 $i++;
2434                                         }
2435                                         $query.=") or ((subtitle like ? or subtitle like ?)";
2436                                         push(@bind,"$key[0]%","% $key[0]%");
2437                                         for ($i=1;$i<$count;$i++){
2438                                                 $query.=" and (subtitle like ? or subtitle like ?)";
2439                                                 push(@bind,"$key[$i]%","% $key[$i]%");
2440                                         }
2441                                         $query.=") or ((seriestitle like ? or seriestitle like ?)";
2442                                         push(@bind,"$key[0]%","% $key[0]%");
2443                                         for ($i=1;$i<$count;$i++){
2444                                                 $query.=" and (seriestitle like ? or seriestitle like ?)";
2445                                                 push(@bind,"$key[$i]%","% $key[$i]%");
2446                                         }
2447                                         $query.=") or ((unititle like ? or unititle like ?)";
2448                                         push(@bind,"$key[0]%","% $key[0]%");
2449                                         for ($i=1;$i<$count;$i++){
2450                                                 $query.=" and (unititle like ? or unititle like ?)";
2451                                                 push(@bind,"$key[$i]%","% $key[$i]%");
2452                                         }
2453                                         $query .= "))";
2454                                 }
2455                                 if ($search->{'abstract'} ne ''){
2456                                         $query.= " and (abstract like ?)";
2457                                         push(@bind,"%$search->{'abstract'}%");
2458                                 }
2459                                 if ($search->{'date-before'} ne ''){
2460                                         $query.= " and (copyrightdate like ?)";
2461                                         push(@bind,"%$search->{'date-before'}%");
2462                                 }
2463
2464                         } elsif ($search->{'dewey'} ne ''){
2465                                 $query="select * from biblioitems,biblio
2466                                 where biblio.biblionumber=biblioitems.biblionumber
2467                                 and biblioitems.dewey like ?";
2468                                 @bind=("$search->{'dewey'}%");
2469                         } elsif ($search->{'illustrator'} ne '') {
2470                                         $query="select * from biblioitems,biblio
2471                                 where biblio.biblionumber=biblioitems.biblionumber
2472                                 and biblioitems.illus like ?";
2473                                         @bind=("%".$search->{'illustrator'}."%");
2474                         } elsif ($search->{'publisher'} ne ''){
2475                                 $query = "Select * from biblio,biblioitems where biblio.biblionumber
2476                                 =biblioitems.biblionumber and (publishercode like ?)";
2477                                 @bind=("%$search->{'publisher'}%");
2478                         } elsif ($search->{'abstract'} ne ''){
2479                                 $query = "Select * from biblio where abstract like ?";
2480                                 @bind=("%$search->{'abstract'}%");
2481                         } elsif ($search->{'date-before'} ne ''){
2482                                 $query = "Select * from biblio where copyrightdate like ?";
2483                                 @bind=("%$search->{'date-before'}%");
2484                         }elsif ($search->{'branch'} ne ''){
2485                                 $query = "Select * from biblio,items  where biblio.biblionumber
2486                                 =items.biblionumber and holdingbranch like ?";
2487                                 @bind=("$search->{'branch'}");
2488                         }elsif ($search->{'class'} ne ''){
2489                                 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
2490
2491                                 $query.= " where itemtype= ?";
2492                                 @bind=("$search->{'class'}");
2493                         }
2494                         $query .=" group by biblio.biblionumber";
2495                 }
2496         }
2497         if ($type eq 'subject'){
2498                 my @key=split(' ',$search->{'subject'});
2499                 my $count=@key;
2500                 my $i=1;
2501                 $query="select * from bibliosubject, biblioitems where
2502 (bibliosubject.biblionumber = biblioitems.biblionumber) and ( subject like ? or subject like ? or subject like ?)";
2503                 @bind=("$key[0]%","% $key[0]%","%($key[0])%");
2504                 while ($i<$count){
2505                         $query.=" and (subject like ? or subject like ? or subject like ?)";
2506                         push(@bind,"$key[$i]%","% $key[$i]%","%($key[$i])%");
2507                         $i++;
2508                 }
2509
2510                 # FIXME - Wouldn't it be better to fix the database so that if a
2511                 # book has a subject "NZ", then it also gets added the subject
2512                 # "New Zealand"?
2513                 # This can also be generalized by adding a table of subject
2514                 # synonyms to the database: just declare "NZ" to be a synonym for
2515                 # "New Zealand", "SF" a synonym for both "Science fiction" and
2516                 # "Fantastic fiction", etc.
2517
2518                 if (lc($search->{'subject'}) eq 'nz'){
2519                         $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
2520                         or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
2521                 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
2522                         $query=~ s/ nz/ NEW ZEALAND/ig;
2523                         $query=~ s/nz /NEW ZEALAND /ig;
2524                         $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
2525                 }
2526         }
2527         if ($type eq 'precise'){
2528                 if ($search->{'itemnumber'} ne ''){
2529                         $query="select * from items,biblio ";
2530                         my $search2=uc $search->{'itemnumber'};
2531                         $query=$query." where
2532                         items.biblionumber=biblio.biblionumber
2533                         and barcode=?";
2534                         @bind=($search2);
2535                                         # FIXME - .= <<EOT;
2536                 }
2537                 if ($search->{'isbn'} ne ''){
2538                         $query = "Select * from biblio,biblioitems where biblio.biblionumber
2539                                 =biblioitems.biblionumber and (isbn like ?)";
2540                                 @bind=("$search->{'isbn'}%");
2541                 }
2542         }
2543         if ($type ne 'precise' && $type ne 'subject'){
2544                 if ($search->{'author'} ne ''){
2545                         $query .= " order by biblio.author,title";
2546                 } else {
2547                         $query .= " order by title";
2548                 }
2549         } else {
2550                 if ($type eq 'subject'){
2551                         $query .= " group by subject ";
2552                 }
2553         }
2554         my $sth=$dbh->prepare($query);
2555         $sth->execute(@bind);
2556         my $count=1;
2557         my $i=0;
2558         my $limit= $num+$offset;
2559         while (my $data=$sth->fetchrow_hashref){
2560                 my $query="select classification,dewey,subclass,publishercode from biblioitems where biblionumber=?";
2561                 my @bind=($data->{'biblionumber'});
2562                 if ($search->{'class'} ne ''){
2563                         my @temp=split(/\|/,$search->{'class'});
2564                         my $count=@temp;
2565                         $query.= " and ( itemtype= ?";
2566                         push(@bind,$temp[0]);
2567                         for (my $i=1;$i<$count;$i++){
2568                         $query.=" or itemtype=?";
2569                         push(@bind,$temp[$i]);
2570                         }
2571                         $query.=")";
2572                 }
2573                 if ($search->{'dewey'} ne ''){
2574                         $query.=" and dewey=? ";
2575                         push(@bind,$search->{'dewey'});
2576                 }
2577                 if ($search->{'illustrator'} ne ''){
2578                         $query.=" and illus like ?";
2579                         push(@bind,"%$search->{'illustrator'}%");
2580                 }
2581                 if ($search->{'publisher'} ne ''){
2582                         $query.= " and (publishercode like ?)";
2583                         push(@bind,"%$search->{'publisher'}%");
2584                 }
2585                 my $sti=$dbh->prepare($query);
2586                 $sti->execute(@bind);
2587                 my $classification;
2588                 my $dewey;
2589                 my $subclass;
2590                 my $true=0;
2591                 my $publishercode;
2592                 my $bibitemdata;
2593                 if ($bibitemdata = $sti->fetchrow_hashref()){
2594                         $true=1;
2595                         $classification=$bibitemdata->{'classification'};
2596                         $dewey=$bibitemdata->{'dewey'};
2597                         $subclass=$bibitemdata->{'subclass'};
2598                         $publishercode=$bibitemdata->{'publishercode'};
2599                 }
2600                 #  print STDERR "$dewey $subclass $publishercode\n";
2601                 # FIXME - The Dewey code is a string, not a number.
2602                 $dewey=~s/\.*0*$//;
2603                 ($dewey == 0) && ($dewey='');
2604                 ($dewey) && ($dewey.=" $subclass");
2605                 $data->{'classification'}=$classification;
2606                 $data->{'dewey'}=$dewey;
2607                 $data->{'publishercode'}=$publishercode;
2608                 $sti->finish;
2609                 if ($true == 1){
2610                         if ($count > $offset && $count <= $limit){
2611                                 $results[$i]=$data;
2612                                 $i++;
2613                         }
2614                         $count++;
2615                 }
2616         }
2617         $sth->finish;
2618         $count--;
2619         return($count,@results);
2620 }
2621
2622 sub updatesearchstats{
2623   my ($dbh,$query)=@_;
2624
2625 }
2626
2627 =item subsearch
2628
2629   @results = &subsearch($env, $subject);
2630
2631 Searches for books that have a subject that exactly matches
2632 C<$subject>.
2633
2634 C<&subsearch> returns an array of results. Each element of this array
2635 is a string, containing the book's title, author, and biblionumber,
2636 separated by tabs.
2637
2638 C<$env> is ignored.
2639
2640 =cut
2641 #'
2642 sub subsearch {
2643   my ($env,$subject)=@_;
2644   my $dbh = C4::Context->dbh;
2645   my $sth=$dbh->prepare("Select * from biblio,bibliosubject where
2646   biblio.biblionumber=bibliosubject.biblionumber and
2647   bibliosubject.subject=? group by biblio.biblionumber
2648   order by biblio.title");
2649   $sth->execute($subject);
2650   my $i=0;
2651   my @results;
2652   while (my $data=$sth->fetchrow_hashref){
2653     push @results, $data;
2654     $i++;
2655   }
2656   $sth->finish;
2657   return(@results);
2658 }
2659
2660 =item ItemInfo
2661
2662   @results = &ItemInfo($env, $biblionumber, $type);
2663
2664 Returns information about books with the given biblionumber.
2665
2666 C<$type> may be either C<intra> or anything else. If it is not set to
2667 C<intra>, then the search will exclude lost, very overdue, and
2668 withdrawn items.
2669
2670 C<$env> is ignored.
2671
2672 C<&ItemInfo> returns a list of references-to-hash. Each element
2673 contains a number of keys. Most of them are table items from the
2674 C<biblio>, C<biblioitems>, C<items>, and C<itemtypes> tables in the
2675 Koha database. Other keys include:
2676
2677 =over 4
2678
2679 =item C<$data-E<gt>{branchname}>
2680
2681 The name (not the code) of the branch to which the book belongs.
2682
2683 =item C<$data-E<gt>{datelastseen}>
2684
2685 This is simply C<items.datelastseen>, except that while the date is
2686 stored in YYYY-MM-DD format in the database, here it is converted to
2687 DD/MM/YYYY format. A NULL date is returned as C<//>.
2688
2689 =item C<$data-E<gt>{datedue}>
2690
2691 =item C<$data-E<gt>{class}>
2692
2693 This is the concatenation of C<biblioitems.classification>, the book's
2694 Dewey code, and C<biblioitems.subclass>.
2695
2696 =item C<$data-E<gt>{ocount}>
2697
2698 I think this is the number of copies of the book available.
2699
2700 =item C<$data-E<gt>{order}>
2701
2702 If this is set, it is set to C<One Order>.
2703
2704 =back
2705
2706 =cut
2707 #'
2708 sub ItemInfo {
2709         my ($env,$biblionumber,$type) = @_;
2710         my $dbh   = C4::Context->dbh;
2711         my $query = "SELECT *,items.notforloan as itemnotforloan FROM items, biblio, biblioitems
2712                                         left join itemtypes on biblioitems.itemtype = itemtypes.itemtype
2713                                         WHERE items.biblionumber = ?
2714                                         AND biblioitems.biblioitemnumber = items.biblioitemnumber
2715                                         AND biblio.biblionumber = items.biblionumber";
2716         $query .= " order by items.dateaccessioned desc";
2717         my $sth=$dbh->prepare($query);
2718         $sth->execute($biblionumber);
2719         my $i=0;
2720         my @results;
2721 my ($date_due, $count_reserves);
2722         while (my $data=$sth->fetchrow_hashref){
2723                 my $datedue = '';
2724                 my $isth=$dbh->prepare("Select issues.*,borrowers.cardnumber from issues,borrowers where itemnumber = ? and returndate is null and issues.borrowernumber=borrowers.borrowernumber");
2725                 $isth->execute($data->{'itemnumber'});
2726                 if (my $idata=$isth->fetchrow_hashref){
2727                 $data->{borrowernumber} = $idata->{borrowernumber};
2728                 $data->{cardnumber} = $idata->{cardnumber};
2729                 $datedue = format_date($idata->{'date_due'});
2730                 }
2731                 if ($datedue eq ''){
2732         #       $datedue="Available";
2733                         my ($restype,$reserves)=C4::Reserves2::CheckReserves($data->{'itemnumber'});
2734                         if ($restype) {
2735 #                               $datedue=$restype;
2736                                 $count_reserves = $restype;
2737                         }
2738                 }
2739                 $isth->finish;
2740         #get branch information.....
2741                 my $bsth=$dbh->prepare("SELECT * FROM branches WHERE branchcode = ?");
2742                 $bsth->execute($data->{'holdingbranch'});
2743                 if (my $bdata=$bsth->fetchrow_hashref){
2744                         $data->{'branchname'} = $bdata->{'branchname'};
2745                 }
2746                 my $date=format_date($data->{'datelastseen'});
2747                 $data->{'datelastseen'}=$date;
2748                 $data->{'datedue'}=$datedue;
2749                 $data->{'count_reserves'} = $count_reserves;
2750         # get notforloan complete status if applicable
2751                 my $sthnflstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.notforloan"');
2752                 $sthnflstatus->execute;
2753                 my ($authorised_valuecode) = $sthnflstatus->fetchrow;
2754                 if ($authorised_valuecode) {
2755                         $sthnflstatus = $dbh->prepare("select lib from authorised_values where category=? and authorised_value=?");
2756                         $sthnflstatus->execute($authorised_valuecode,$data->{itemnotforloan});
2757                         my ($lib) = $sthnflstatus->fetchrow;
2758                         $data->{notforloan} = $lib;
2759                 }
2760
2761 # my stack procedures
2762
2763                 my $stackstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.stack"');
2764                 $stackstatus->execute;
2765
2766                 ($authorised_valuecode) = $stackstatus->fetchrow;
2767                 if ($authorised_valuecode) {
2768                         $stackstatus = $dbh->prepare("select lib from authorised_values where category=? and authorised_value=?");
2769                         $stackstatus->execute($authorised_valuecode,$data->{stack});
2770
2771                         my ($lib) = $stackstatus->fetchrow;
2772                         $data->{stack} = $lib;
2773                 }
2774                 $results[$i]=$data;
2775                 $i++;
2776         }
2777         $sth->finish;
2778
2779         return(@results);
2780 }
2781
2782 =item GetItems
2783
2784   @results = &GetItems($env, $biblionumber);
2785
2786 Returns information about books with the given biblionumber.
2787
2788 C<$env> is ignored.
2789
2790 C<&GetItems> returns an array of strings. Each element is a
2791 tab-separated list of values: biblioitemnumber, itemtype,
2792 classification, Dewey number, subclass, ISBN, volume, number, and
2793 itemdata.
2794
2795 Itemdata, in turn, is a string of the form
2796 "I<barcode>C<[>I<holdingbranch>C<[>I<flags>" where I<flags> contains
2797 the string C<NFL> if the item is not for loan, and C<LOST> if the item
2798 is lost.
2799
2800 =cut
2801 #'
2802 sub GetItems {
2803    my ($env,$biblionumber)=@_;
2804    #debug_msg($env,"GetItems");
2805    my $dbh = C4::Context->dbh;
2806    my $sth=$dbh->prepare("Select * from biblioitems where (biblionumber = ?)");
2807    $sth->execute($biblionumber);
2808    #debug_msg($env,"executed query");
2809    my $i=0;
2810    my @results;
2811    while (my $data=$sth->fetchrow_hashref) {
2812       print ($env,$data->{'biblioitemnumber'});
2813       my $dewey = $data->{'dewey'};
2814       $dewey =~ s/0+$//;
2815         my $isbn= $data->{'isbn'};
2816
2817
2818       my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
2819       $line .= "\t$data->{'classification'}\t$dewey";
2820       $line .= "\t$data->{'subclass'}\t$data->{'isbn'}";
2821       $line .= "\t$data->{'volume'}\t$data->{number}";
2822       my $isth= $dbh->prepare("select * from items where biblioitemnumber = ?");
2823       $isth->execute($data->{'biblioitemnumber'});
2824       while (my $idata = $isth->fetchrow_hashref) {
2825         my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
2826         if ($idata->{'notforloan'} == 1) {
2827           $iline .= "NFL ";
2828         }
2829         if ($idata->{'itemlost'} == 1) {
2830           $iline .= "LOST ";
2831         }
2832         $line .= "\t$iline";
2833       }
2834       $isth->finish;
2835       $results[$i] = $line;
2836       $i++;
2837    }
2838    $sth->finish;
2839    return(@results);
2840 }
2841
2842 =item itemdata
2843
2844   $item = &itemdata($barcode);
2845
2846 Looks up the item with the given barcode, and returns a
2847 reference-to-hash containing information about that item. The keys of
2848 the hash are the fields from the C<items> and C<biblioitems> tables in
2849 the Koha database.
2850
2851 =cut
2852 #'
2853 sub itemdata {
2854   my ($barcode)=@_;
2855   my $dbh = C4::Context->dbh;
2856   my $sth=$dbh->prepare("Select * from items,biblioitems where barcode=?
2857   and items.biblioitemnumber=biblioitems.biblioitemnumber");
2858   $sth->execute($barcode);
2859   my $data=$sth->fetchrow_hashref;
2860   $sth->finish;
2861   return($data);
2862 }
2863
2864 =item bibdata
2865
2866   $data = &bibdata($biblionumber, $type);
2867
2868 Returns information about the book with the given biblionumber.
2869
2870 C<$type> is ignored.
2871
2872 C<&bibdata> returns a reference-to-hash. The keys are the fields in
2873 the C<biblio>, C<biblioitems>, and C<bibliosubtitle> tables in the
2874 Koha database.
2875
2876 In addition, C<$data-E<gt>{subject}> is the list of the book's
2877 subjects, separated by C<" , "> (space, comma, space).
2878
2879 If there are multiple biblioitems with the given biblionumber, only
2880 the first one is considered.
2881
2882 =cut
2883 #'
2884 sub bibdata {
2885         my ($bibnum, $type) = @_;
2886         my $dbh   = C4::Context->dbh;
2887         my $sth   = $dbh->prepare("Select *, biblioitems.notes AS bnotes, biblio.notes
2888                                                                 from biblio, biblioitems
2889                                                                 left join bibliosubtitle on
2890                                                                 biblio.biblionumber = bibliosubtitle.biblionumber
2891                                                                 left join itemtypes on biblioitems.itemtype=itemtypes.itemtype
2892                                                                 where biblio.biblionumber = ?
2893                                                                 and biblioitems.biblionumber = biblio.biblionumber");
2894         $sth->execute($bibnum);
2895         my $data;
2896         $data  = $sth->fetchrow_hashref;
2897         $sth->finish;
2898         # handle management of repeated subtitle
2899         $sth   = $dbh->prepare("Select * from bibliosubtitle where biblionumber = ?");
2900         $sth->execute($bibnum);
2901         my @subtitles;
2902         while (my $dat = $sth->fetchrow_hashref){
2903                 my %line;
2904                 $line{subtitle} = $dat->{subtitle};
2905                 push @subtitles, \%line;
2906         } # while
2907         $data->{subtitles} = \@subtitles;
2908         $sth->finish;
2909         $sth   = $dbh->prepare("Select * from bibliosubject where biblionumber = ?");
2910         $sth->execute($bibnum);
2911         my @subjects;
2912         while (my $dat = $sth->fetchrow_hashref){
2913                 my %line;
2914                 $line{subject} = $dat->{'subject'};
2915                 push @subjects, \%line;
2916         } # while
2917         $data->{subjects} = \@subjects;
2918         $sth->finish;
2919         $sth   = $dbh->prepare("Select * from additionalauthors where biblionumber = ?");
2920         $sth->execute($bibnum);
2921         while (my $dat = $sth->fetchrow_hashref){
2922                 $data->{'additionalauthors'} .= "$dat->{'author'} - ";
2923         } # while
2924         chop $data->{'additionalauthors'};
2925         chop $data->{'additionalauthors'};
2926         chop $data->{'additionalauthors'};
2927         $sth->finish;
2928         return($data);
2929 } # sub bibdata
2930
2931 =item bibitemdata
2932
2933   $itemdata = &bibitemdata($biblioitemnumber);
2934
2935 Looks up the biblioitem with the given biblioitemnumber. Returns a
2936 reference-to-hash. The keys are the fields from the C<biblio>,
2937 C<biblioitems>, and C<itemtypes> tables in the Koha database, except
2938 that C<biblioitems.notes> is given as C<$itemdata-E<gt>{bnotes}>.
2939
2940 =cut
2941 #'
2942 sub bibitemdata {
2943     my ($bibitem) = @_;
2944     my $dbh   = C4::Context->dbh;
2945     my $sth   = $dbh->prepare("Select *,biblioitems.notes as bnotes from biblio, biblioitems,itemtypes where biblio.biblionumber = biblioitems.biblionumber and biblioitemnumber = ? and biblioitems.itemtype = itemtypes.itemtype");
2946     my $data;
2947
2948     $sth->execute($bibitem);
2949
2950     $data = $sth->fetchrow_hashref;
2951
2952     $sth->finish;
2953     return($data);
2954 } # sub bibitemdata
2955
2956 =item subject
2957
2958   ($count, $subjects) = &subject($biblionumber);
2959
2960 Looks up the subjects of the book with the given biblionumber. Returns
2961 a two-element list. C<$subjects> is a reference-to-array, where each
2962 element is a subject of the book, and C<$count> is the number of
2963 elements in C<$subjects>.
2964
2965 =cut
2966 #'
2967 sub subject {
2968   my ($bibnum)=@_;
2969   my $dbh = C4::Context->dbh;
2970   my $sth=$dbh->prepare("Select * from bibliosubject where biblionumber=?");
2971   $sth->execute($bibnum);
2972   my @results;
2973   my $i=0;
2974   while (my $data=$sth->fetchrow_hashref){
2975     $results[$i]=$data;
2976     $i++;
2977   }
2978   $sth->finish;
2979   return($i,\@results);
2980 }
2981
2982 =item addauthor
2983
2984   ($count, $authors) = &addauthors($biblionumber);
2985
2986 Looks up the additional authors for the book with the given
2987 biblionumber.
2988
2989 Returns a two-element list. C<$authors> is a reference-to-array, where
2990 each element is an additional author, and C<$count> is the number of
2991 elements in C<$authors>.
2992
2993 =cut
2994 #'
2995 sub addauthor {
2996   my ($bibnum)=@_;
2997   my $dbh = C4::Context->dbh;
2998   my $sth=$dbh->prepare("Select * from additionalauthors where biblionumber=?");
2999   $sth->execute($bibnum);
3000   my @results;
3001   my $i=0;
3002   while (my $data=$sth->fetchrow_hashref){
3003     $results[$i]=$data;
3004     $i++;
3005   }
3006   $sth->finish;
3007   return($i,\@results);
3008 }
3009
3010 =item subtitle
3011
3012   ($count, $subtitles) = &subtitle($biblionumber);
3013
3014 Looks up the subtitles for the book with the given biblionumber.
3015
3016 Returns a two-element list. C<$subtitles> is a reference-to-array,
3017 where each element is a subtitle, and C<$count> is the number of
3018 elements in C<$subtitles>.
3019
3020 =cut
3021 #'
3022 sub subtitle {
3023   my ($bibnum)=@_;
3024   my $dbh = C4::Context->dbh;
3025   my $sth=$dbh->prepare("Select * from bibliosubtitle where biblionumber=?");
3026   $sth->execute($bibnum);
3027   my @results;
3028   my $i=0;
3029   while (my $data=$sth->fetchrow_hashref){
3030     $results[$i]=$data;
3031     $i++;
3032   }
3033   $sth->finish;
3034   return($i,\@results);
3035 }
3036
3037 =item itemissues
3038
3039   @issues = &itemissues($biblioitemnumber, $biblio);
3040
3041 Looks up information about who has borrowed the bookZ<>(s) with the
3042 given biblioitemnumber.
3043
3044 C<$biblio> is ignored.
3045
3046 C<&itemissues> returns an array of references-to-hash. The keys
3047 include the fields from the C<items> table in the Koha database.
3048 Additional keys include:
3049
3050 =over 4
3051
3052 =item C<date_due>
3053
3054 If the item is currently on loan, this gives the due date.
3055
3056 If the item is not on loan, then this is either "Available" or
3057 "Cancelled", if the item has been withdrawn.
3058
3059 =item C<card>
3060
3061 If the item is currently on loan, this gives the card number of the
3062 patron who currently has the item.
3063
3064 =item C<timestamp0>, C<timestamp1>, C<timestamp2>
3065
3066 These give the timestamp for the last three times the item was
3067 borrowed.
3068
3069 =item C<card0>, C<card1>, C<card2>
3070
3071 The card number of the last three patrons who borrowed this item.
3072
3073 =item C<borrower0>, C<borrower1>, C<borrower2>
3074
3075 The borrower number of the last three patrons who borrowed this item.
3076
3077 =back
3078
3079 =cut
3080 #'
3081 sub itemissues {
3082     my ($bibitem, $biblio)=@_;
3083     my $dbh   = C4::Context->dbh;
3084     # FIXME - If this function die()s, the script will abort, and the
3085     # user won't get anything; depending on how far the script has
3086     # gotten, the user might get a blank page. It would be much better
3087     # to at least print an error message. The easiest way to do this
3088     # is to set $SIG{__DIE__}.
3089     my $sth   = $dbh->prepare("Select * from items where
3090 items.biblioitemnumber = ?")
3091       || die $dbh->errstr;
3092     my $i     = 0;
3093     my @results;
3094
3095     $sth->execute($bibitem)
3096       || die $sth->errstr;
3097
3098     while (my $data = $sth->fetchrow_hashref) {
3099         # Find out who currently has this item.
3100         # FIXME - Wouldn't it be better to do this as a left join of
3101         # some sort? Currently, this code assumes that if
3102         # fetchrow_hashref() fails, then the book is on the shelf.
3103         # fetchrow_hashref() can fail for any number of reasons (e.g.,
3104         # database server crash), not just because no items match the
3105         # search criteria.
3106         my $sth2   = $dbh->prepare("select * from issues,borrowers
3107 where itemnumber = ?
3108 and returndate is NULL
3109 and issues.borrowernumber = borrowers.borrowernumber");
3110
3111         $sth2->execute($data->{'itemnumber'});
3112         if (my $data2 = $sth2->fetchrow_hashref) {
3113             $data->{'date_due'} = $data2->{'date_due'};
3114             $data->{'card'}     = $data2->{'cardnumber'};
3115             $data->{'borrower'}     = $data2->{'borrowernumber'};
3116         } else {
3117             if ($data->{'wthdrawn'} eq '1') {
3118                 $data->{'date_due'} = 'Cancelled';
3119             } else {
3120                 $data->{'date_due'} = 'Available';
3121             } # else
3122         } # else
3123
3124         $sth2->finish;
3125
3126         # Find the last 3 people who borrowed this item.
3127         $sth2 = $dbh->prepare("select * from issues, borrowers
3128                                                 where itemnumber = ?
3129                                                                         and issues.borrowernumber = borrowers.borrowernumber
3130                                                                         and returndate is not NULL
3131                                                                         order by returndate desc,timestamp desc") ;
3132         $sth2->execute($data->{'itemnumber'}) ;
3133         for (my $i2 = 0; $i2 < 2; $i2++) { # FIXME : error if there is less than 3 pple borrowing this item
3134             if (my $data2 = $sth2->fetchrow_hashref) {
3135                 $data->{"timestamp$i2"} = $data2->{'timestamp'};
3136                 $data->{"card$i2"}      = $data2->{'cardnumber'};
3137                 $data->{"borrower$i2"}  = $data2->{'borrowernumber'};
3138             } # if
3139         } # for
3140
3141         $sth2->finish;
3142         $results[$i] = $data;
3143         $i++;
3144     }
3145
3146     $sth->finish;
3147     return(@results);
3148 }
3149
3150 =item itemnodata
3151
3152   $item = &itemnodata($env, $dbh, $biblioitemnumber);
3153
3154 Looks up the item with the given biblioitemnumber.
3155
3156 C<$env> and C<$dbh> are ignored.
3157
3158 C<&itemnodata> returns a reference-to-hash whose keys are the fields
3159 from the C<biblio>, C<biblioitems>, and C<items> tables in the Koha
3160 database.
3161
3162 =cut
3163 #'
3164 sub itemnodata {
3165   my ($env,$dbh,$itemnumber) = @_;
3166   $dbh = C4::Context->dbh;
3167   my $sth=$dbh->prepare("Select * from biblio,items,biblioitems
3168     where items.itemnumber = ?
3169     and biblio.biblionumber = items.biblionumber
3170     and biblioitems.biblioitemnumber = items.biblioitemnumber");
3171 #  print $query;
3172   $sth->execute($itemnumber);
3173   my $data=$sth->fetchrow_hashref;
3174   $sth->finish;
3175   return($data);
3176 }
3177
3178 =item BornameSearch
3179
3180   ($count, $borrowers) = &BornameSearch($env, $searchstring, $type);
3181
3182 Looks up patrons (borrowers) by name.
3183
3184 C<$env> is ignored.
3185
3186 BUGFIX 499: C<$type> is now used to determine type of search.
3187 if $type is "simple", search is performed on the first letter of the
3188 surname only.
3189
3190 C<$searchstring> is a space-separated list of search terms. Each term
3191 must match the beginning a borrower's surname, first name, or other
3192 name.
3193
3194 C<&BornameSearch> returns a two-element list. C<$borrowers> is a
3195 reference-to-array; each element is a reference-to-hash, whose keys
3196 are the fields of the C<borrowers> table in the Koha database.
3197 C<$count> is the number of elements in C<$borrowers>.
3198
3199 =cut
3200 #'
3201 #used by member enquiries from the intranet
3202 #called by member.pl
3203 sub BornameSearch  {
3204         my ($env,$searchstring,$orderby,$type)=@_;
3205         my $dbh = C4::Context->dbh;
3206         my $query = ""; my $count; my @data;
3207         my @bind=();
3208
3209         if($type eq "simple")   # simple search for one letter only
3210         {
3211                 $query="Select * from borrowers where surname like '$searchstring%' order by $orderby";
3212 #               @bind=("$searchstring%");
3213         }
3214         else    # advanced search looking in surname, firstname and othernames
3215         {
3216 ### Try to determine whether numeric like cardnumber
3217         if ($searchstring+1>1) {
3218         $query="Select * from borrowers where  cardnumber  like '$searchstring%' ";
3219
3220         }else{
3221
3222         my @words=split / /,$searchstring;
3223         foreach my $word(@words){
3224         $word="+".$word;
3225
3226         }
3227         $searchstring=join " ",@words;
3228
3229                 $query="Select * from borrowers where  MATCH(surname,firstname,othernames) AGAINST('$searchstring'  in boolean mode)";
3230
3231         }
3232                 $query=$query." order by $orderby";
3233         }
3234
3235         my $sth=$dbh->prepare($query);
3236 #       warn "Q $orderby : $query";
3237         $sth->execute();
3238         my @results;
3239         my $cnt=$sth->rows;
3240         while (my $data=$sth->fetchrow_hashref){
3241         push(@results,$data);
3242         }
3243         #  $sth->execute;
3244         $sth->finish;
3245         return ($cnt,\@results);
3246 }
3247
3248 =item borrdata
3249
3250   $borrower = &borrdata($cardnumber, $borrowernumber);
3251
3252 Looks up information about a patron (borrower) by either card number
3253 or borrower number. If $borrowernumber is specified, C<&borrdata>
3254 searches by borrower number; otherwise, it searches by card number.
3255
3256 C<&borrdata> returns a reference-to-hash whose keys are the fields of
3257 the C<borrowers> table in the Koha database.
3258
3259 =cut
3260 #'
3261 sub borrdata {
3262   my ($cardnumber,$bornum)=@_;
3263   $cardnumber = uc $cardnumber;
3264   my $dbh = C4::Context->dbh;
3265   my $sth;
3266 if ($bornum eq ''&& $cardnumber eq ''){ return undef; }
3267   if ($bornum eq ''){
3268     $sth=$dbh->prepare("Select * from borrowers where cardnumber=?");
3269     $sth->execute($cardnumber);
3270   } else {
3271     $sth=$dbh->prepare("Select * from borrowers where borrowernumber=?");
3272   $sth->execute($bornum);
3273   }
3274   my $data=$sth->fetchrow_hashref;
3275   $sth->finish;
3276   if ($data) {
3277         return($data);
3278         } else { # try with firstname
3279                 if ($cardnumber) {
3280                         my $sth=$dbh->prepare("select * from borrowers where firstname=?");
3281                         $sth->execute($cardnumber);
3282                         my $data=$sth->fetchrow_hashref;
3283                         $sth->finish;
3284                         return($data);
3285                 }
3286         }
3287         return undef;
3288 }
3289
3290 =item borrissues
3291
3292   ($count, $issues) = &borrissues($borrowernumber);
3293
3294 Looks up what the patron with the given borrowernumber has borrowed.
3295
3296 C<&borrissues> returns a two-element array. C<$issues> is a
3297 reference-to-array, where each element is a reference-to-hash; the
3298 keys are the fields from the C<issues>, C<biblio>, and C<items> tables
3299 in the Koha database. C<$count> is the number of elements in
3300 C<$issues>.
3301
3302 =cut
3303 #'
3304 sub borrissues {
3305   my ($bornum)=@_;
3306   my $dbh = C4::Context->dbh;
3307   my $sth=$dbh->prepare("Select * from issues,biblio,items where borrowernumber=?
3308    and items.itemnumber=issues.itemnumber
3309         and items.biblionumber=biblio.biblionumber
3310         and issues.returndate is NULL order by date_due");
3311     $sth->execute($bornum);
3312   my @result;
3313   while (my $data = $sth->fetchrow_hashref) {
3314     push @result, $data;
3315   }
3316   $sth->finish;
3317   return(scalar(@result), \@result);
3318 }
3319
3320 =item allissues
3321
3322   ($count, $issues) = &allissues($borrowernumber, $sortkey, $limit);
3323
3324 Looks up what the patron with the given borrowernumber has borrowed,
3325 and sorts the results.
3326
3327 C<$sortkey> is the name of a field on which to sort the results. This
3328 should be the name of a field in the C<issues>, C<biblio>,
3329 C<biblioitems>, or C<items> table in the Koha database.
3330
3331 C<$limit> is the maximum number of results to return.
3332
3333 C<&allissues> returns a two-element array. C<$issues> is a
3334 reference-to-array, where each element is a reference-to-hash; the
3335 keys are the fields from the C<issues>, C<biblio>, C<biblioitems>, and
3336 C<items> tables of the Koha database. C<$count> is the number of
3337 elements in C<$issues>
3338
3339 =cut
3340 #'
3341 sub allissues {
3342   my ($bornum,$order,$limit)=@_;
3343   #FIXME: sanity-check order and limit
3344   my $dbh = C4::Context->dbh;
3345   my $query="Select * from issues,biblio,items,biblioitems
3346   where borrowernumber=? and
3347   items.biblioitemnumber=biblioitems.biblioitemnumber and
3348   items.itemnumber=issues.itemnumber and
3349   items.biblionumber=biblio.biblionumber order by $order";
3350   if ($limit !=0){
3351     $query.=" limit $limit";
3352   }
3353   #print $query;
3354   my $sth=$dbh->prepare($query);
3355   $sth->execute($bornum);
3356   my @result;
3357   my $i=0;
3358   while (my $data=$sth->fetchrow_hashref){
3359     $result[$i]=$data;;
3360     $i++;
3361   }
3362   $sth->finish;
3363   return($i,\@result);
3364 }
3365
3366 =item borrdata2
3367
3368   ($borrowed, $due, $fine) = &borrdata2($env, $borrowernumber);
3369
3370 Returns aggregate data about items borrowed by the patron with the
3371 given borrowernumber.
3372
3373 C<$env> is ignored.
3374
3375 C<&borrdata2> returns a three-element array. C<$borrowed> is the
3376 number of books the patron currently has borrowed. C<$due> is the
3377 number of overdue items the patron currently has borrowed. C<$fine> is
3378 the total fine currently due by the borrower.
3379
3380 =cut
3381 #'
3382 sub borrdata2 {
3383   my ($env,$bornum)=@_;
3384   my $dbh = C4::Context->dbh;
3385   my $query="Select count(*) from issues where borrowernumber='$bornum' and
3386     returndate is NULL";
3387     # print $query;
3388   my $sth=$dbh->prepare($query);
3389   $sth->execute;
3390   my $data=$sth->fetchrow_hashref;
3391   $sth->finish;
3392   $sth=$dbh->prepare("Select count(*) from issues where
3393     borrowernumber='$bornum' and date_due < now() and returndate is NULL");
3394   $sth->execute;
3395   my $data2=$sth->fetchrow_hashref;
3396   $sth->finish;
3397   $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
3398     borrowernumber='$bornum'");
3399   $sth->execute;
3400   my $data3=$sth->fetchrow_hashref;
3401   $sth->finish;
3402
3403 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
3404 }
3405
3406 sub borrdata3 {
3407   my ($env,$bornum)=@_;
3408   my $dbh = C4::Context->dbh;
3409   my $query="Select count(*) from  reserveissue as r where r.borrowernumber='$bornum'
3410      and rettime is null";
3411     # print $query;
3412   my $sth=$dbh->prepare($query);
3413   $sth->execute;
3414   my $data=$sth->fetchrow_hashref;
3415   $sth->finish;
3416   $sth=$dbh->prepare("Select count(*),timediff(now(),  duetime  ) as elapsed, hour(timediff(now(),  duetime  )) as hours, MINUTE(timediff(now(),  duetime  )) as min from
3417     reserveissue as r where  r.borrowernumber='$bornum' and rettime is null and duetime< now() group by r.borrowernumber");
3418   $sth->execute;
3419
3420   my $data2=$sth->fetchrow_hashref;
3421 my $resfine;
3422 my $rescharge=C4::Context->preference('resmaterialcharge');
3423 if (!$rescharge){
3424 $rescharge=1;
3425 }
3426 if ($data2->{'elapsed'}>0){
3427  $resfine=($data2->{'hours'}+$data2->{'min'}/60)*$rescharge;
3428 $resfine=sprintf  ("%.1f",$resfine);
3429 }
3430   $sth->finish;
3431   $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
3432     borrowernumber='$bornum'");
3433   $sth->execute;
3434   my $data3=$sth->fetchrow_hashref;
3435   $sth->finish;
3436
3437
3438 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'},$resfine);
3439 }
3440 =item getboracctrecord
3441
3442   ($count, $acctlines, $total) = &getboracctrecord($env, $borrowernumber);
3443
3444 Looks up accounting data for the patron with the given borrowernumber.
3445
3446 C<$env> is ignored.
3447
3448 (FIXME - I'm not at all sure what this is about.)
3449
3450 C<&getboracctrecord> returns a three-element array. C<$acctlines> is a
3451 reference-to-array, where each element is a reference-to-hash; the
3452 keys are the fields of the C<accountlines> table in the Koha database.
3453 C<$count> is the number of elements in C<$acctlines>. C<$total> is the
3454 total amount outstanding for all of the account lines.
3455
3456 =cut
3457 #'
3458 sub getboracctrecord {
3459    my ($env,$params) = @_;
3460    my $dbh = C4::Context->dbh;
3461    my @acctlines;
3462    my $numlines=0;
3463    my $sth=$dbh->prepare("Select * from accountlines where
3464 borrowernumber=? order by date desc,timestamp desc");
3465 #   print $query;
3466    $sth->execute($params->{'borrowernumber'});
3467    my $total=0;
3468    while (my $data=$sth->fetchrow_hashref){
3469    #FIXME before reinstating: insecure?
3470 #      if ($data->{'itemnumber'} ne ''){
3471 #        $query="Select * from items,biblio where items.itemnumber=
3472 #       '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
3473 #       my $sth2=$dbh->prepare($query);
3474 #       $sth2->execute;
3475 #       my $data2=$sth2->fetchrow_hashref;
3476 #       $sth2->finish;
3477 #       $data=$data2;
3478  #     }
3479       $acctlines[$numlines] = $data;
3480       $numlines++;
3481       $total += $data->{'amountoutstanding'};
3482    }
3483    $sth->finish;
3484    return ($numlines,\@acctlines,$total);
3485 }
3486
3487 =item itemcount
3488
3489   ($count, $lcount, $nacount, $fcount, $scount, $lostcount,
3490   $mending, $transit,$ocount) =
3491     &itemcount($env, $biblionumber, $type);
3492
3493 Counts the number of items with the given biblionumber, broken down by
3494 category.
3495
3496 C<$env> is ignored.
3497
3498 If C<$type> is not set to C<intra>, lost, very overdue, and withdrawn
3499 items will not be counted.
3500
3501 C<&itemcount> returns a nine-element list:
3502
3503 C<$count> is the total number of items with the given biblionumber.
3504
3505 C<$lcount> is the number of items at the Levin branch.
3506
3507 C<$nacount> is the number of items that are neither borrowed, lost,
3508 nor withdrawn (and are therefore presumably on a shelf somewhere).
3509
3510 C<$fcount> is the number of items at the Foxton branch.
3511
3512 C<$scount> is the number of items at the Shannon branch.
3513
3514 C<$lostcount> is the number of lost and very overdue items.
3515
3516 C<$mending> is the number of items at the Mending branch (being
3517 mended?).
3518
3519 C<$transit> is the number of items at the Transit branch (in transit
3520 between branches?).
3521
3522 C<$ocount> is the number of items that haven't arrived yet
3523 (aqorders.quantity - aqorders.quantityreceived).
3524
3525 =cut
3526 #'
3527
3528 # FIXME - There's also a &C4::Biblio::itemcount.
3529 # Since they're all exported, acqui/acquire.pl doesn't compile with -w.
3530 sub itemcount {
3531   my ($env,$bibnum,$type)=@_;
3532   my $dbh = C4::Context->dbh;
3533   my $query="Select * from items where
3534   biblionumber=? ";
3535   if ($type ne 'intra'){
3536     $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
3537     (wthdrawn <> 1 or wthdrawn is NULL)";
3538   }
3539   my $sth=$dbh->prepare($query);
3540   #  print $query;
3541   $sth->execute($bibnum);
3542   my $count=0;
3543   my $lcount=0;
3544   my $nacount=0;
3545   my $fcount=0;
3546   my $scount=0;
3547   my $lostcount=0;
3548   my $mending=0;
3549   my $transit=0;
3550   my $ocount=0;
3551   while (my $data=$sth->fetchrow_hashref){
3552     $count++;
3553
3554     my $sth2=$dbh->prepare("select * from issues,items where issues.itemnumber=
3555     ? and returndate is NULL
3556     and items.itemnumber=issues.itemnumber and ((items.itemlost <>1 and
3557     items.itemlost <> 2) or items.itemlost is NULL)
3558     and (wthdrawn <> 1 or wthdrawn is NULL)");
3559     $sth2->execute($data->{'itemnumber'});
3560     if (my $data2=$sth2->fetchrow_hashref){
3561        $nacount++;
3562     } else {
3563       if ($data->{'holdingbranch'} eq 'C' || $data->{'holdingbranch'} eq 'LT'){
3564         $lcount++;
3565       }
3566       if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
3567         $fcount++;
3568       }
3569       if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
3570         $scount++;
3571       }
3572       if ($data->{'itemlost'} eq '1'){
3573         $lostcount++;
3574       }
3575       if ($data->{'itemlost'} eq '2'){
3576         $lostcount++;
3577       }
3578       if ($data->{'holdingbranch'} eq 'FM'){
3579         $mending++;
3580       }
3581       if ($data->{'holdingbranch'} eq 'TR'){
3582         $transit++;
3583       }
3584     }
3585     $sth2->finish;
3586   }
3587 #  if ($count == 0){
3588     my $sth2=$dbh->prepare("Select * from aqorders where biblionumber=?");
3589     $sth2->execute($bibnum);
3590     if (my $data=$sth2->fetchrow_hashref){
3591       $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
3592     }
3593 #    $count+=$ocount;
3594     $sth2->finish;
3595   $sth->finish;
3596   return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
3597 }
3598
3599 =item itemcount2
3600
3601   $counts = &itemcount2($env, $biblionumber, $type);
3602
3603 Counts the number of items with the given biblionumber, broken down by
3604 category.
3605
3606 C<$env> is ignored.
3607
3608 C<$type> may be either C<intra> or anything else. If it is not set to
3609 C<intra>, then the search will exclude lost, very overdue, and
3610 withdrawn items.
3611
3612 C<$&itemcount2> returns a reference-to-hash, with the following fields:
3613
3614 =over 4
3615
3616 =item C<total>
3617
3618 The total number of items with this biblionumber.
3619
3620 =item C<order>
3621
3622 The number of items on order (aqorders.quantity -
3623 aqorders.quantityreceived).
3624
3625 =item I<branchname>
3626
3627 For each branch that has at least one copy of the book, C<$counts>
3628 will have a key with the branch name, giving the number of copies at
3629 that branch.
3630
3631 =back
3632
3633 =cut
3634 #'
3635 sub itemcount2 {
3636   my ($env,$bibnum,$type)=@_;
3637   my $dbh = C4::Context->dbh;
3638   my $query="Select * from items,branches where
3639   biblionumber=? and items.holdingbranch=branches.branchcode";
3640   if ($type ne 'intra'){
3641     $query.=" and ((itemlost <>1 and itemlost <> 2) or itemlost is NULL) and
3642     (wthdrawn <> 1 or wthdrawn is NULL)";
3643   }
3644   my $sth=$dbh->prepare($query);
3645   #  print $query;
3646   $sth->execute($bibnum);
3647   my %counts;
3648   $counts{'total'}=0;
3649   while (my $data=$sth->fetchrow_hashref){
3650     $counts{'total'}++;
3651     my $status;
3652     for my $test (
3653       [
3654         'Item Lost',
3655         'select * from items
3656           where itemnumber=?
3657             and not ((items.itemlost <>1 and items.itemlost <> 2)
3658                       or items.itemlost is NULL)'
3659       ], [
3660         'Withdrawn',
3661         'select * from items
3662           where itemnumber=? and not (wthdrawn <> 1 or wthdrawn is NULL)'
3663       ], [
3664         'On Loan', "select * from issues,items
3665           where issues.itemnumber=? and returndate is NULL
3666             and items.itemnumber=issues.itemnumber"
3667       ],
3668     ) {
3669         my($testlabel, $query2) = @$test;
3670
3671         my $sth2=$dbh->prepare($query2);
3672         $sth2->execute($data->{'itemnumber'});
3673
3674         # FIXME - fetchrow_hashref() can fail for any number of reasons
3675         # (e.g., a database server crash). Perhaps use a left join of some
3676         # sort for this?
3677         $status = $testlabel if $sth2->fetchrow_hashref;
3678         $sth2->finish;
3679     last if defined $status;
3680     }
3681 ## find the shelving name from stack
3682 my $stackstatus = $dbh->prepare('select authorised_value from marc_subfield_structure where kohafield="items.stack"');
3683                 $stackstatus->execute;
3684
3685                 my ($authorised_valuecode) = $stackstatus->fetchrow;
3686                 if ($authorised_valuecode) {
3687                         $stackstatus = $dbh->prepare("select lib from authorised_values where category=? and authorised_value=?");
3688                         $stackstatus->execute($authorised_valuecode,$data->{stack});
3689
3690                         my ($lib) = $stackstatus->fetchrow;
3691                         $data->{stack} = $lib;
3692                 }
3693
3694
3695     $status = $data->{'branchname'}."[".$data->{'stack'}."]" unless defined $status;
3696     $counts{$status}++;
3697
3698   }
3699   my $sth2=$dbh->prepare("Select * from aqorders where biblionumber=? and
3700   datecancellationprinted is NULL and quantity > quantityreceived");
3701   $sth2->execute($bibnum);
3702   if (my $data=$sth2->fetchrow_hashref){
3703       $counts{'order'}=$data->{'quantity'} - $data->{'quantityreceived'};
3704   }
3705   $sth2->finish;
3706   $sth->finish;
3707   return (\%counts);
3708 }
3709
3710 =item ItemType
3711
3712   $description = &ItemType($itemtype);
3713
3714 Given an item type code, returns the description for that type.
3715
3716 =cut
3717 #'
3718
3719 # FIXME - I'm pretty sure that after the initial setup, the list of
3720 # item types doesn't change very often. Hence, it seems slow and
3721 # inefficient to make yet another database call to look up information
3722 # that'll only change every few months or years.
3723 #
3724 # Much better, I think, to automatically build a Perl file that can be
3725 # included in those scripts that require it, e.g.:
3726 #       @itemtypes = qw( ART BCD CAS CD F ... );
3727 #       %itemtypedesc = (
3728 #               ART     => "Art Prints",
3729 #               BCD     => "CD-ROM from book",
3730 #               CD      => "Compact disc (WN)",
3731 #               F       => "Free Fiction",
3732 #               ...
3733 #       );
3734 # The web server can then run a cron job to rebuild this file from the
3735 # database every hour or so.
3736 #
3737 # The same thing goes for branches, book funds, book sellers, currency
3738 # rates, printers, stopwords, and perhaps others.
3739 sub ItemType {
3740   my ($type)=@_;
3741   my $dbh = C4::Context->dbh;
3742   my $sth=$dbh->prepare("select description from itemtypes where itemtype=?");
3743   $sth->execute($type);
3744   my $dat=$sth->fetchrow_hashref;
3745   $sth->finish;
3746   return ($dat->{'description'});
3747 }
3748
3749 =item bibitems
3750
3751   ($count, @results) = &bibitems($biblionumber);
3752
3753 Given the biblionumber for a book, C<&bibitems> looks up that book's
3754 biblioitems (different publications of the same book, the audio book
3755 and film versions, etc.).
3756
3757 C<$count> is the number of elements in C<@results>.
3758
3759 C<@results> is an array of references-to-hash; the keys are the fields
3760 of the C<biblioitems> and C<itemtypes> tables of the Koha database. In
3761 addition, C<itemlost> indicates the availability of the item: if it is
3762 "2", then all copies of the item are long overdue; if it is "1", then
3763 all copies are lost; otherwise, there is at least one copy available.
3764
3765 =cut
3766 #'
3767 sub bibitems {
3768     my ($bibnum) = @_;
3769     my $dbh   = C4::Context->dbh;
3770     my $sth   = $dbh->prepare("SELECT biblioitems.*,
3771                         itemtypes.*,
3772                         MIN(items.itemlost)        as itemlost,
3773                         MIN(items.dateaccessioned) as dateaccessioned
3774                           FROM biblioitems, itemtypes, items
3775                          WHERE biblioitems.biblionumber     = ?
3776                            AND biblioitems.itemtype         = itemtypes.itemtype
3777                            AND biblioitems.biblioitemnumber = items.biblioitemnumber
3778                       GROUP BY items.biblioitemnumber");
3779     my $count = 0;
3780     my @results;
3781     $sth->execute($bibnum);
3782     while (my $data = $sth->fetchrow_hashref) {
3783         $results[$count] = $data;
3784         $count++;
3785     } # while
3786     $sth->finish;
3787     return($count, @results);
3788 } # sub bibitems
3789
3790 =item barcodes
3791
3792   @barcodes = &barcodes($biblioitemnumber);
3793
3794 Given a biblioitemnumber, looks up the corresponding items.
3795
3796 Returns an array of references-to-hash; the keys are C<barcode> and
3797 C<itemlost>.
3798
3799 The returned items include very overdue items, but not lost ones.
3800
3801 =cut
3802 #'
3803 sub barcodes{
3804     #called from request.pl
3805     my ($biblioitemnumber)=@_;
3806     my $dbh = C4::Context->dbh;
3807     my $sth=$dbh->prepare("SELECT barcode, itemlost, holdingbranch,onloan,itemnumber  FROM items
3808                            WHERE biblioitemnumber = ?
3809                              AND (wthdrawn <> 1 OR wthdrawn IS NULL)");
3810     $sth->execute($biblioitemnumber);
3811     my @barcodes;
3812     my $i=0;
3813     while (my $data=$sth->fetchrow_hashref){
3814         $barcodes[$i]=$data;
3815         $i++;
3816     }
3817     $sth->finish;
3818     return(@barcodes);
3819 }
3820
3821 =item getwebsites
3822
3823   ($count, @websites) = &getwebsites($biblionumber);
3824
3825 Looks up the web sites pertaining to the book with the given
3826 biblionumber.
3827
3828 C<$count> is the number of elements in C<@websites>.
3829
3830 C<@websites> is an array of references-to-hash; the keys are the
3831 fields from the C<websites> table in the Koha database.
3832
3833 =cut
3834 #'
3835 sub getwebsites {
3836     my ($biblionumber) = @_;
3837     my $dbh   = C4::Context->dbh;
3838     my $sth   = $dbh->prepare("Select * from websites where biblionumber = ?");
3839     my $count = 0;
3840     my @results;
3841
3842     $sth->execute($biblionumber);
3843     while (my $data = $sth->fetchrow_hashref) {
3844         # FIXME - The URL scheme shouldn't be stripped off, at least
3845         # not here, since it's part of the URL, and will be useful in
3846         # constructing a link to the site. If you don't want the user
3847         # to see the "http://" part, strip that off when building the
3848         # HTML code.
3849         $data->{'url'} =~ s/^http:\/\///;       # FIXME - Leaning toothpick
3850                                                 # syndrome
3851         $results[$count] = $data;
3852         $count++;
3853     } # while
3854
3855     $sth->finish;
3856     return($count, @results);
3857 } # sub getwebsites
3858
3859 =item getwebbiblioitems
3860
3861   ($count, @results) = &getwebbiblioitems($biblionumber);
3862
3863 Given a book's biblionumber, looks up the web versions of the book
3864 (biblioitems with itemtype C<WEB>).
3865
3866 C<$count> is the number of items in C<@results>. C<@results> is an
3867 array of references-to-hash; the keys are the items from the
3868 C<biblioitems> table of the Koha database.
3869
3870 =cut
3871 #'
3872 sub getwebbiblioitems {
3873     my ($biblionumber) = @_;
3874     my $dbh   = C4::Context->dbh;
3875     my $sth   = $dbh->prepare("Select * from biblioitems where biblionumber = ?
3876 and itemtype = 'WEB'");
3877     my $count = 0;
3878     my @results;
3879
3880     $sth->execute($biblionumber);
3881     while (my $data = $sth->fetchrow_hashref) {
3882         $data->{'url'} =~ s/^http:\/\///;
3883         $results[$count] = $data;
3884         $count++;
3885     } # while
3886
3887     $sth->finish;
3888     return($count, @results);
3889 } # sub getwebbiblioitems
3890
3891
3892
3893 =item isbnsearch
3894
3895   ($count, @results) = &isbnsearch($isbn,$title);
3896
3897 Given an isbn and/or a title, returns the biblios having it.
3898 Used in acqui.simple, isbnsearch.pl only
3899
3900 C<$count> is the number of items in C<@results>. C<@results> is an
3901 array of references-to-hash; the keys are the items from the
3902 C<biblioitems> table of the Koha database.
3903
3904 =cut
3905
3906 sub isbnsearch {
3907     my ($isbn,$title) = @_;
3908     my $dbh   = C4::Context->dbh;
3909     my $count = 0;
3910     my ($query,@bind);
3911     my $sth;
3912     my @results;
3913
3914     $query = "Select distinct biblio.*, biblioitems.classification from biblio, biblioitems where
3915                                 biblio.biblionumber = biblioitems.biblionumber";
3916         @bind=();
3917         if ($isbn) {
3918                 $query .= " and isbn like ?";
3919                 @bind=(uc($isbn)."%");
3920         }
3921         if ($title) {
3922                 $query .= " and title like ?";
3923                 @bind=($title."%");
3924         }
3925     $sth   = $dbh->prepare($query);
3926
3927     $sth->execute(@bind);
3928     while (my $data = $sth->fetchrow_hashref) {
3929         $results[$count] = $data;
3930         $count++;
3931     } # while
3932
3933     $sth->finish;
3934     return($count, @results);
3935 } # sub isbnsearch
3936
3937 =item getbranchname
3938
3939   $branchname = &getbranchname($branchcode);
3940
3941 Given the branch code, the function returns the corresponding
3942 branch name for a comprehensive information display
3943
3944 =cut
3945
3946 sub getbranchname
3947 {
3948         my ($branchcode) = @_;
3949         my $dbh = C4::Context->dbh;
3950         my $sth = $dbh->prepare("SELECT branchname FROM branches WHERE branchcode = ?");
3951         $sth->execute($branchcode);
3952         my $branchname = $sth->fetchrow();
3953         $sth->finish();
3954         return $branchname;
3955 } # sub getbranchname
3956
3957 =item getborrowercategory
3958
3959   $description = &getborrowercategory($categorycode);
3960
3961 Given the borrower's category code, the function returns the corresponding
3962 description for a comprehensive information display.
3963
3964 =cut
3965
3966 sub getborrowercategory
3967 {
3968         my ($catcode) = @_;
3969         my $dbh = C4::Context->dbh;
3970         my $sth = $dbh->prepare("SELECT description FROM categories WHERE categorycode = ?");
3971         $sth->execute($catcode);
3972         my $description = $sth->fetchrow();
3973         $sth->finish();
3974         return $description;
3975 } # sub getborrowercategory
3976
3977 sub getborrowercategoryinfo
3978 {
3979         my ($catcode) = @_;
3980         my $dbh = C4::Context->dbh;
3981         my $sth = $dbh->prepare("SELECT * FROM categories WHERE categorycode = ?");
3982         $sth->execute($catcode);
3983         my $category = $sth->fetchrow_hashref;
3984         $sth->finish();
3985         return $category;
3986 } # sub getborrowercategoryinfo
3987
3988 sub getMARCnotes {
3989         my ($dbh, $bibid, $marcflavour) = @_;
3990         my ($mintag, $maxtag);
3991         if ($marcflavour eq "MARC21") {
3992                 $mintag = "500";
3993                 $maxtag = "599";
3994         } else {           # assume unimarc if not marc21
3995                 $mintag = "300";
3996                 $maxtag = "399";
3997         }
3998
3999
4000
4001         my $record=MARCgetbiblio($dbh,$bibid);
4002
4003         my @marcnotes;
4004         my $note = "";
4005         my $tag = "";
4006         my $marcnote;
4007
4008         foreach my $field ($record->field('5..')) {
4009                 my $value = $field->as_string();
4010                 if ( $note ne "") {
4011                         $marcnote = {marcnote => $note,};
4012                         push @marcnotes, $marcnote;
4013                         $note=$value;
4014                 }
4015                 if ($note ne $value) {
4016                         $note = $note." ".$value;
4017                 }
4018         }
4019
4020         if ($note) {
4021                 $marcnote = {marcnote => $note};
4022                 push @marcnotes, $marcnote;   #load last tag into array
4023         }
4024
4025
4026
4027         my $marcnotesarray=\@marcnotes;
4028         return $marcnotesarray;
4029 }  # end getMARCnotes
4030
4031
4032 sub getMARCsubjects {
4033     my ($dbh, $bibid, $marcflavour) = @_;
4034         my ($mintag, $maxtag);
4035         if ($marcflavour eq "MARC21") {
4036                 $mintag = "600";
4037                 $maxtag = "699";
4038         } else {           # assume unimarc if not marc21
4039                 $mintag = "600";
4040                 $maxtag = "619";
4041         }
4042         my $record=MARCgetbiblio($dbh,$bibid);
4043         my @marcsubjcts;
4044         my $subjct = "";
4045         my $subfield = "";
4046         my $marcsubjct;
4047
4048         foreach my $field ($record->field('6..')) {
4049                 #my $value = $field->subfield('a');
4050                 #$marcsubjct = {MARCSUBJCT => $value,};
4051                 $marcsubjct = {MARCSUBJCT => $field->as_string(),};
4052                 push @marcsubjcts, $marcsubjct;
4053                 #$subjct = $value;
4054
4055         }
4056         my $marcsubjctsarray=\@marcsubjcts;
4057         return $marcsubjctsarray;
4058 }  #end getMARCsubjects
4059
4060
4061 sub getMARCurls {
4062     my ($dbh, $bibid, $marcflavour) = @_;
4063         my ($mintag, $maxtag);
4064         if ($marcflavour eq "MARC21") {
4065                 $mintag = "856";
4066                 $maxtag = "856";
4067         } else {           # assume unimarc if not marc21
4068                 $mintag = "600";
4069                 $maxtag = "619";
4070         }
4071
4072 my $record=MARCgetbiblio($dbh,$bibid);
4073         my @marcurls;
4074         my $url = "";
4075         my $subfil = "";
4076         my $marcurl;
4077
4078         foreach my $field ($record->field('856')) {
4079
4080
4081                 my $value = $field->subfield('u');
4082 #               my $subfil = $data->[1];
4083                 if ( $value ne $url) {
4084                         $marcurl = {MARCURLS => $value,};
4085                         push @marcurls, $marcurl;
4086                         $url = $value;
4087                 }
4088         }
4089
4090
4091         my $marcurlsarray=\@marcurls;
4092         return $marcurlsarray;
4093 }  #end getMARCurls
4094
4095
4096 sub searchZOOM {
4097     my ($search_or_scan,$type,$query,$num,$startfrom,$then_sort_by,$expanded_facet) = @_;
4098         # establish database connections
4099     my $dbh = C4::Context->dbh;
4100     my $zconn=C4::Context->Zconn("biblioserver");
4101         my $branches = GetBranches();
4102         # make sure all is well with the connection
4103     if ($zconn eq "error") {
4104         return("error with connection",undef); #FIXME: better error handling
4105     }
4106
4107     my $zoom_query_obj;
4108
4109         # prepare the query depending on the type
4110     if ($type eq 'ccl') {
4111                 #$query =~ s/(\(|\))//g;
4112         eval {
4113                 $zoom_query_obj = new ZOOM::Query::CCL2RPN($query,$zconn);
4114                 };
4115                 if ($@) {
4116             return ("error: Sorry, there was a problem with your query: $@",undef); #FIXME: better error handling
4117                 }
4118     } elsif ($type eq 'cql') {
4119         eval {
4120             $zoom_query_obj = new ZOOM::Query::CQL2RPN($query,$zconn);
4121         };
4122         if ($@) {
4123             return ("error: Sorry, there was a problem with your query: $@",undef); #FIXME: better error handling
4124         }
4125     } else {
4126         eval {
4127             $zoom_query_obj = new ZOOM::Query::PQF($query);
4128         };
4129         if ($@) {
4130             return("error with search: $@",undef); #FIXME: better error handling
4131         }
4132     }
4133
4134     # PERFORM THE SEARCH OR SCAN
4135     my $result;
4136     my @results;
4137     my $numresults;
4138     if ($search_or_scan =~ /scan/) {
4139         eval {
4140             $result = $zconn->scan($zoom_query_obj);
4141         };
4142         if ($@) {
4143             return ("error with scan: $@",undef);
4144         }
4145     } else {
4146         eval {
4147             $result = $zconn->search($zoom_query_obj);
4148         };
4149         if ($@) {
4150             return("error with search: $@",undef); #FIXME: better error handling
4151         }
4152     }
4153
4154     #### RESORT RESULT SET
4155     if ($then_sort_by) {
4156         $result->sort("yaz", "$then_sort_by")
4157     }
4158         ### New Facets Stuff
4159         my $facets_counter = ();
4160         my $facets_info = ();
4161         my $facets = [ {
4162                 link_value => 'su-t',
4163                 label_value => 'Subject - Topic',
4164                 tags => ['650', '651',],
4165                 subfield => 'a',
4166                 },
4167                 {
4168         link_value => 'au',
4169         label_value => 'Authors',
4170         tags => ['100','700',],
4171         subfield => 'a',
4172                 },
4173                 {
4174         link_value => 'se',
4175         label_value => 'Series',
4176         tags => ['440','490',],
4177         subfield => 'a',
4178         },
4179                 {
4180         link_value => 'branch',
4181         label_value => 'Branches',
4182         tags => ['952',],
4183         subfield => 'b',
4184                 expanded => '1',
4185         },
4186         ];
4187
4188     #### INITIALIZE SOME VARS USED CREATE THE FACETED RESULTS
4189         my @facets_loop; # stores the ref to array of hashes for template
4190         #### LOOP THROUGH THE RESULTS
4191     $numresults = 0 | $result->size() if  ($result);
4192     for ( my $i=$startfrom; $i<(($startfrom+$num<=$numresults) ? ($startfrom+$num):$numresults) ; $i++){
4193                 ## This is just an index scan
4194         if  ($search_or_scan =~ /scan/) {
4195             my ($term,$occ) = $result->term($i);
4196             # here we create a minimal MARC record and hand it off to the
4197             # template just like a normal result ... perhaps not ideal, but
4198             # it works for now FIXME: distinguish between MARC21 and UNIMARC
4199             use MARC::Record;
4200             my $tmprecord = MARC::Record->new();
4201             $tmprecord->encoding('UTF-8');
4202             my $tmptitle = MARC::Field->new( '245',' ',' ',
4203                         a => $term,
4204                         b => $occ);
4205                         $tmprecord->append_fields($tmptitle);
4206             push @results, $tmprecord->as_usmarc();
4207                 ## This is a real search
4208         } else {
4209             my $rec = $result->record($i);
4210             push(@results,$rec->raw()) if $rec; #FIXME: sometimes this fails
4211
4212             ##### BUILD FACETS AND LIMITS ####
4213                         my $facet_record = MARC::Record->new_from_usmarc($rec->raw());
4214
4215                         for (my $i=0;$i<=@$facets;$i++) {
4216                                         if ($facets->[$i]) {
4217                                                 my @fields;
4218                                                 for my $tag (@{$facets->[$i]->{'tags'}}) {
4219                                                         push @fields, $facet_record->field($tag);
4220                                                 }
4221                                                 for my $field (@fields) {
4222                                                         my @subfields = $field->subfields();
4223                                                         for my $subfield (@subfields) {
4224                                                                 my ($code,$data) = @$subfield;
4225                                                                 if ($code eq $facets->[$i]->{'subfield'}) {
4226                                                                         $facets_counter->{ $facets->[$i]->{'link_value'} }->{ $data }++;
4227                                                                 }
4228                                                         }
4229                                                 }
4230                                                 $facets_info->{ $facets->[$i]->{'link_value'} }->{ 'label_value' } = $facets->[$i]->{'label_value'};
4231                                                 $facets_info->{ $facets->[$i]->{'link_value'} }->{ 'expanded' } = $facets->[$i]->{'expanded'};
4232                                         }
4233                         }
4234
4235         }
4236     }
4237         # BUILD FACETS
4238         for my $link_value ( sort { $facets_counter->{$b} <=> $facets_counter->{$a} } keys %$facets_counter) {
4239                 my $expandable;
4240                 my $number_of_facets;
4241                 my @this_facets_array;
4242                 for my $one_facet (sort { $facets_counter->{ $link_value }->{$b} <=> $facets_counter->{ $link_value }->{$a} } keys %{$facets_counter->{ $link_value }} ) {
4243                         $number_of_facets++;
4244                         if (($number_of_facets < 6) || ($expanded_facet eq $link_value) || ($facets_info->{ $link_value }->{ 'expanded'})) {
4245
4246                                 # sanitize the link value ), ( will cause errors with CCL
4247                                 my $facet_link_value = $one_facet;
4248                                 $facet_link_value =~ s/(\(|\))/ /g;
4249
4250                                 # fix the length that will display in the label
4251                                 my $facet_label_value = $one_facet;
4252                                 $facet_label_value = substr($one_facet,0,20)."..." unless length($facet_label_value)<=20;
4253                                 # well, if it's a branch, label by the name, not the code
4254                                 if ($link_value =~/branch/) {
4255                                         warn "branch";
4256                                         $facet_label_value = $branches->{$one_facet}->{'branchname'};
4257                                 }
4258
4259                                 # but we're down with the whole label being in the link's title
4260                                 my $facet_title_value = $one_facet;
4261
4262                                 push @this_facets_array ,
4263                                 ( { facet_count => $facets_counter->{ $link_value }->{ $one_facet },
4264                                         facet_label_value => $facet_label_value,
4265                                         facet_title_value => $facet_title_value,
4266                                         facet_link_value => $facet_link_value,
4267                                         type_link_value => $link_value,
4268                                         },
4269                                 );
4270                                 }
4271                 }
4272                 unless ($facets_info->{ $link_value }->{ 'expanded'}) {
4273                         $expandable=1 if (($number_of_facets > 6) && ($expanded_facet ne $link_value));
4274                 }
4275                 push @facets_loop,
4276                 ( {     type_link_value => $link_value,
4277                         type_id => $link_value."_id",
4278                         type_label  => $facets_info->{ $link_value }->{ 'label_value' },
4279                         facets => \@this_facets_array,
4280                         expandable => $expandable,
4281                         expand => $link_value,
4282                         }
4283                 );
4284         }
4285
4286         return(undef,$numresults,\@facets_loop,@results);
4287 }
4288
4289 sub getRecords {
4290     my ($zoom_query_ref,$sort_by_ref,$servers_ref,$count,$offset) = @_;
4291     my @zoom_query = @$zoom_query_ref;
4292     my @servers = @$servers_ref;
4293     my @sort_by = @$sort_by_ref;
4294
4295     # build the query string
4296     my $zoom_query;
4297     foreach my $query (@zoom_query) {
4298         $zoom_query.="$query " if $query;
4299     }
4300
4301     # create the zoom connection and query object
4302     my $zconn;
4303     my @zconns;
4304     my @results;
4305     my @results_array; # stores the final array of hashes of arrays
4306     for (my $i = 0; $i < @servers; $i++) {
4307         $zconns[$i] = new ZOOM::Connection($servers[$i], 0,
4308                                 async => 1, # asynchronous mode
4309                                 count => 1, # piggyback retrieval count
4310                                 preferredRecordSyntax => "usmarc");
4311         $zconns[$i]->option(    cclfile=> "/koha/etc/ccl.properties");
4312         # perform the search, create the results objects
4313         $results[$i] = $zconns[$i]->search(new ZOOM::Query::CCL2RPN($zoom_query,$zconns[$i]));
4314
4315         # concatenate the sort_by limits and pass them to the results object
4316         my $sort_by;
4317         foreach my $sort (@sort_by) {
4318             $sort_by.=$sort." "; # used to be $sort,
4319         }
4320         $results[$i]->sort("yaz", $sort_by) if $sort_by;
4321     }
4322     while ((my $i = ZOOM::event(\@zconns)) != 0) {
4323         my $ev = $zconns[$i-1]->last_event();
4324         #print("<td><tr>connection ", $i-1, ": ", ZOOM::event_str($ev), "</tr></td>\n");
4325         if ($ev == ZOOM::Event::ZEND) {
4326             my $size = $results[$i-1]->size();
4327             if ($size) {
4328                 my $results_hash;
4329                 $results_hash->{'server'} = $servers[$i-1];
4330                 $results_hash->{'hits'} = $size;
4331                 for ( my $j=$offset; $j<(($offset+$count<=$size) ? ($offset+$count):$size) ; $j++){
4332                     my $records_hash;
4333                     my $record = $results[$i-1]->record($j)->raw();
4334                     warn $record;
4335                     my ($error,$final_record) = changeEncoding($record,'MARC','MARC21','UTF-8');
4336                     $records_hash->{'record'} = $final_record;
4337                     $results_hash->{'RECORDS'}[$j] = $records_hash;
4338                     my $dbh = C4::Context->dbh;
4339                     use MARC::Record;
4340                     my $record_obj = MARC::Record->new_from_usmarc($final_record);
4341                     my $oldbiblio = MARCmarc2koha($dbh,$record_obj,'');
4342                     $results_hash->{'BIBLIOS'}[$j] = $oldbiblio;
4343
4344                 }
4345                 push @results_array, $results_hash;
4346             }
4347             #print "connection ", $i-1, ": $size hits";
4348             #print $results[$i-1]->record(0)->render() if $size > 0;
4349         }
4350     }
4351     return (undef, @results_array);
4352 }
4353
4354
4355 sub buildQuery {
4356     my ($operators,$operands,$limits,$sort_by) = @_;
4357     my @operators = @$operators if $operators;
4358     my @operands = @$operands if $operands;
4359     my @limits = @$limits if $limits;
4360     my @sort_by = @$sort_by if $sort_by;
4361     my $previous_operand;   # a flag used to keep track if there was a previous query
4362                             # if there was, we can apply the current operator
4363     my @ccl;
4364
4365     # construct the query with operators
4366     for (my $i=0; $i<=@operands; $i++) {
4367         if ($operands[$i]) {
4368
4369             # only add an operator if there is a previous operand
4370             if ($previous_operand) {
4371                 if ($operators[$i]) {
4372                     push @ccl,( {operator => $operators[$i], operand => $operands[$i]} );
4373                 }
4374
4375                 # the default operator is and
4376                 else {
4377                     push @ccl,( {operator => 'and', operand => $operands[$i]} );
4378                 }
4379             }
4380             else {
4381                 push @ccl, ( {operand => $operands[$i]} );
4382                 $previous_operand = 1;
4383             }
4384         }
4385     }
4386
4387     # add limits
4388     foreach my $limit (@limits) {
4389         push @ccl, ( {limit => $limit} ) if $limit;
4390     }
4391
4392     return (undef,@ccl);
4393 }
4394 sub searchResults {
4395     my ($searchdesc,$num,$count,@marcresults)=@_;
4396     use C4::Date;
4397
4398     my $dbh= C4::Context->dbh;
4399     my $toggle;
4400     my $even=1;
4401     my @newresults;
4402         my @span_terms = split (/ /, $searchdesc);
4403     #Build brancnames hash
4404     #find branchname
4405     #get branch information.....
4406     my %branches;
4407     my $bsth=$dbh->prepare("SELECT branchcode,branchname FROM branches");
4408     $bsth->execute();
4409     while (my $bdata=$bsth->fetchrow_hashref){
4410         $branches{$bdata->{'branchcode'}}= $bdata->{'branchname'};
4411     }
4412
4413     #search item field code
4414     my $sth = $dbh->prepare(
4415         "select tagfield from marc_subfield_structure where kohafield like 'items.itemnumber'"
4416         );
4417     $sth->execute;
4418     my ($itemtag) = $sth->fetchrow;
4419
4420     ## find column names of items related to MARC
4421     my $sth2=$dbh->prepare("SHOW COLUMNS from items");
4422     $sth2->execute;
4423     my %subfieldstosearch;
4424     while ((my $column)=$sth2->fetchrow){
4425         my ($tagfield,$tagsubfield) = &MARCfind_marc_from_kohafield($dbh,"items.".$column,"");
4426         $subfieldstosearch{$column}=$tagsubfield;
4427     }
4428     if ($num>$count) {
4429             $num = $count;
4430     }
4431     for ( my $i=0; $i<$num ; $i++){
4432         my $marcrecord;
4433         $marcrecord = MARC::File::USMARC::decode($marcresults[$i]);
4434         my $oldbiblio = MARCmarc2koha($dbh,$marcrecord,'');
4435                 # add spans to search term in results
4436                 foreach my $term (@span_terms) {
4437                         if (length($term) > 3) {
4438                                 $term =~ s/(.*=|\)|\))//g;
4439                                 $oldbiblio->{'title'} =~ s/$term/<span class=term>$term<\/span>/gi;
4440                                 $oldbiblio->{'subtitle'} =~ s/$term/<span class=term>$term<\/span>/gi;
4441                                 $oldbiblio->{'author'} =~ s/$term/<span class=term>$term<\/span>/gi;
4442                                 $oldbiblio->{'publishercode'} =~ s/$term/<span class=term>$term<\/span>/gi;
4443                                 $oldbiblio->{'place'} =~ s/$term/<span class=term>$term<\/span>/gi;
4444                                 $oldbiblio->{'pages'} =~ s/$term/<span class=term>$term<\/span>/gi;
4445                                 $oldbiblio->{'notes'} =~ s/$term/<span class=term>$term<\/span>/gi;
4446                                 $oldbiblio->{'size'} =~ s/$term/<span class=term>$term<\/span>/gi;
4447                         }
4448                 }
4449
4450         if ($i % 2) {
4451             $toggle="#ffffcc";
4452         } else {
4453             $toggle="white";
4454         }
4455         $oldbiblio->{'toggle'}=$toggle;
4456         my @fields = $marcrecord->field($itemtag);
4457         my @items;
4458         my $item;
4459         my %counts;
4460         $counts{'total'}=0;
4461
4462 #
4463 ##Loop for each item field
4464         foreach my $field (@fields) {
4465         foreach my $code ( keys %subfieldstosearch ) {
4466
4467         $item->{$code}=$field->subfield($subfieldstosearch{$code});
4468         }
4469
4470         my $status;
4471         $item->{'branchname'}=$branches{$item->{'homebranch'}};
4472         $item->{'date_due'}=$item->{onloan};
4473         $status="Lost" if ($item->{itemlost});
4474         $status="Withdrawn" if ($item->{wthdrawn});
4475         $status =" On loan" if ($item->{onloan});
4476         #$status="Due:".format_date($item->{onloan}) if ($item->{onloan}>0 );
4477         # $status="On Loan" if ($item->{onloan} );
4478         if ($item->{'location'}){
4479             $status = $item->{'branchname'}."[".$item->{'location'}."]" unless defined $status;
4480         }else{
4481             $status = $item->{'branchname'} unless defined $status;
4482         }
4483         $counts{$status}++;
4484         $counts{'total'}++;
4485         push @items,$item;
4486     }
4487     my $norequests = 1;
4488     my $noitems    = 1;
4489     if (@items) {
4490         $noitems = 0;
4491         foreach my $itm (@items) {
4492             $norequests = 0 unless $itm->{'itemnotforloan'};
4493         }
4494     }
4495     $oldbiblio->{'noitems'} = $noitems;
4496     $oldbiblio->{'norequests'} = $norequests;
4497     $oldbiblio->{'even'} = $even = not $even;
4498     $oldbiblio->{'itemcount'} = $counts{'total'};
4499     my $totalitemcounts = 0;
4500     foreach my $key (keys %counts){
4501         if ($key ne 'total'){
4502             $totalitemcounts+= $counts{$key};
4503             $oldbiblio->{'locationhash'}->{$key}=$counts{$key};
4504         }
4505     }
4506     my ($locationtext, $locationtextonly, $notavailabletext) = ('','','');
4507     foreach (sort keys %{$oldbiblio->{'locationhash'}}) {
4508         if ($_ eq 'notavailable') {
4509             $notavailabletext="Not available";
4510             my $c=$oldbiblio->{'locationhash'}->{$_};
4511             $oldbiblio->{'not-available-p'}=$c;
4512         } else {
4513             $locationtext.="$_";
4514             my $c=$oldbiblio->{'locationhash'}->{$_};
4515             if ($_ eq 'Item Lost') {
4516                 $oldbiblio->{'lost-p'} = $c;
4517             } elsif ($_ eq 'Withdrawn') {
4518                 $oldbiblio->{'withdrawn-p'} = $c;
4519             } elsif ($_ eq 'On Loan') {
4520                 $oldbiblio->{'on-loan-p'} = $c;
4521             } else {
4522                 $locationtextonly.= $_;
4523                 $locationtextonly.= " ($c)<br/> " if $totalitemcounts > 1;
4524             }
4525             if ($totalitemcounts>1) {
4526                 $locationtext.=" ($c)<br/> ";
4527             }
4528         }
4529     }
4530     if ($notavailabletext) {
4531         $locationtext.= $notavailabletext;
4532     } else {
4533         $locationtext=~s/, $//;
4534     }
4535     $oldbiblio->{'location'} = $locationtext;
4536     $oldbiblio->{'location-only'} = $locationtextonly;
4537     $oldbiblio->{'use-location-flags-p'} = 1;
4538     push (@newresults, $oldbiblio);
4539
4540     }
4541     return @newresults;
4542 }
4543
4544 END { }       # module clean-up code here (global destructor)
4545
4546 1;
4547 __END__
4548
4549 =back
4550
4551 =head1 AUTHOR
4552
4553 Koha Developement team <info@koha.org>
4554
4555 =cut