1 package C4::Search; #asummes C4/Search
3 #requires DBI.pm to be installed
7 # Copyright 2000-2002 Katipo Communications
9 # This file is part of Koha.
11 # Koha is free software; you can redistribute it and/or modify it under the
12 # terms of the GNU General Public License as published by the Free Software
13 # Foundation; either version 2 of the License, or (at your option) any later
16 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
17 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
18 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License along with
21 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
22 # Suite 330, Boston, MA 02111-1307 USA
31 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
33 # set the version for version checking
37 @EXPORT = qw(&CatSearch &BornameSearch &ItemInfo &KeywordSearch &subsearch
38 &itemdata &bibdata &GetItems &borrdata &itemnodata &itemcount
39 &borrdata2 &NewBorrowerNumber &bibitemdata &borrissues
40 &getboracctrecord &ItemType &itemissues &subject &subtitle
41 &addauthor &bibitems &barcodes &findguarantees &allissues &systemprefs
43 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
45 # your exported package globals go here,
46 # as well as any optionally exported functions
48 @EXPORT_OK = qw($Var1 %Hashit);
51 # non-exported package globals go here
52 use vars qw(@more $stuff);
54 # initalize package globals, first exported ones
59 # then the others (which are still accessible as $Some::Module::stuff)
63 # all file-scoped lexicals must be created before
64 # the functions below that use them.
66 # file-private lexicals go here
70 # here's a file-private function as a closure,
71 # callable as &$priv_func; it cannot be prototyped.
76 # make all your functions, whether exported or not;
80 my $query="select cardnumber,borrowernumber from borrowers where
82 my $sth=$dbh->prepare($query);
86 while (my $data=$sth->fetchrow_hashref){
97 my $query="select guarantor from borrowers where
98 borrowernumber='$bornum'";
99 my $sth=$dbh->prepare($query);
101 my $data=$sth->fetchrow_hashref;
103 $query="Select * from borrowers where
104 borrowernumber='$data->{'guarantor'}'";
105 $sth=$dbh->prepare($query);
107 $data=$sth->fetchrow_hashref;
116 my $sth=$dbh->prepare("select variable,value from systempreferences");
118 while (my ($variable,$value)=$sth->fetchrow) {
119 $systemprefs{$variable}=$value;
123 return(%systemprefs);
126 sub NewBorrowerNumber {
128 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
130 my $data=$sth->fetchrow_hashref;
132 $data->{'max(borrowernumber)'}++;
134 return($data->{'max(borrowernumber)'});
139 my ($env,$type,$search,$num,$offset)=@_;
140 my $dbh = &C4Connect;
142 $search->{'keyword'}=~ s/ +$//;
143 $search->{'keyword'}=~ s/'/\\'/;
144 my @key=split(' ',$search->{'keyword'});
148 my $query="Select biblionumber from biblio
149 where ((title like '$key[0]%' or title like '% $key[0]%')";
151 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
154 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
155 for ($i=1;$i<$count;$i++){
156 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
158 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
159 for ($i=1;$i<$count;$i++){
160 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
163 my $sth=$dbh->prepare($query);
166 while (my @res=$sth->fetchrow_array){
167 $results[$i]=$res[0];
171 my $set1=Set::Scalar->new(@results);
172 $query="Select biblionumber from bibliosubtitle where
173 ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
174 for ($i=1;$i<$count;$i++){
175 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
178 $sth=$dbh->prepare($query);
181 while (my @res=$sth->fetchrow_array){
182 $results[$i]=$res[0];
186 my $set2=Set::Scalar->new(@results);
190 $query ="Select biblionumber from biblioitems where
191 ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
192 for ($i=1;$i<$count;$i++){
193 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
196 $sth=$dbh->prepare($query);
199 while (my @res=$sth->fetchrow_array){
200 $results[$i]=$res[0];
204 my $set3=Set::Scalar->new(@results);
208 $query="Select biblionumber from bibliosubject where subject like '%$search->{'keyword'}%' group by biblionumber";
209 $sth=$dbh->prepare($query);
212 while (my @res=$sth->fetchrow_array){
213 $results[$i]=$res[0];
217 my $set4=Set::Scalar->new(@results);
226 my @res = $set1->members;
230 # print "count $count";
231 if ($search->{'class'} ne ''){
233 my $query="select * from biblio,biblioitems where
234 biblio.biblionumber='$res[$i2]' and
235 biblio.biblionumber=biblioitems.biblionumber ";
236 if ($search->{'class'} ne ''){
237 my @temp=split(/\|/,$search->{'class'});
239 $query.= "and ( itemtype='$temp[0]'";
240 for (my $i=1;$i<$count;$i++){
241 $query.=" or itemtype='$temp[$i]'";
245 my $sth=$dbh->prepare($query);
247 if (my $data2=$sth->fetchrow_hashref){
248 my $dewey= $data2->{'dewey'};
249 my $subclass=$data2->{'subclass'};
251 ($dewey == 0) && ($dewey='');
252 ($dewey) && ($dewey.=" $subclass") ;
254 my $end=$offset +$num;
259 if ($i4 <=$end && $i4 > $offset){
260 $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
263 # print "in here $i3<br>";
274 while ($i2 < $num && $i2 < $count){
275 my $query="select * from biblio,biblioitems where
276 biblio.biblionumber='$res[$i2+$offset]' and
277 biblio.biblionumber=biblioitems.biblionumber ";
278 if ($search->{'class'} ne ''){
279 my @temp=split(/\|/,$search->{'class'});
281 $query.= "and ( itemtype='$temp[0]'";
282 for (my $i=1;$i<$count;$i++){
283 $query.=" or itemtype='$temp[$i]'";
287 if ($search->{'dewey'} ne ''){
288 $query.= "and (dewey like '$search->{'dewey'}%') ";
291 my $sth=$dbh->prepare($query);
293 if (my $data2=$sth->fetchrow_hashref){
294 my $dewey= $data2->{'dewey'};
295 my $subclass=$data2->{'subclass'};
297 ($dewey == 0) && ($dewey='');
298 ($dewey) && ($dewey.=" $subclass") ;
300 $data2->{dewey}=~s/[\.0]*$//;
301 ($data2->{dewey}==0) && ($data2->{dewey}='');
302 push @$resulthash, $data2;
303 $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
312 return($count,$resulthash,@res2);
316 my ($env,$type,$search,$num,$offset)=@_;
317 my $dbh = &C4Connect;
318 $search->{'keyword'}=~ s/ +$//;
319 $search->{'keyword'}=~ s/'/\\'/;
320 my @key=split(' ',$search->{'keyword'});
324 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
325 biblio.biblionumber=biblioitems.biblionumber and
326 biblio.biblionumber=bibliosubtitle.biblionumber and
327 (((title like '$key[0]%' or title like '% $key[0]%')";
329 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
332 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
333 for ($i=1;$i<$count;$i++){
334 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
336 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
337 for ($i=1;$i<$count;$i++){
338 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
340 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
341 for ($i=1;$i<$count;$i++){
342 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
344 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
345 for ($i=1;$i<$count;$i++){
346 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
348 if ($search->{'keyword'} =~ /new zealand/i){
349 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
350 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
351 or author like '% nz %' or author like '% nz')"
353 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
354 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
355 $search->{'keyword'} =~ / nz/i){
356 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
357 or title like '% new zealand' or subtitle like 'new zealand%' or
358 subtitle like '% new zealand %'
359 or subtitle like '% new zealand' or author like 'new zealand%'
360 or author like '% new zealand %' or author like '% new zealand' or
361 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
362 or seriestitle like '% new zealand')"
365 if ($search->{'class'} ne ''){
366 my @temp=split(/\|/,$search->{'class'});
368 $query.= "and ( itemtype='$temp[0]'";
369 for (my $i=1;$i<$count;$i++){
370 $query.=" or itemtype='$temp[$i]'";
374 if ($search->{'dewey'} ne ''){
375 $query.= "and (dewey like '$search->{'dewey'}%') ";
377 $query.="group by biblio.biblionumber";
378 #$query.=" order by author,title";
379 my $sth=$dbh->prepare($query);
382 while (my $data=$sth->fetchrow_hashref){
383 # my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
386 # my ($dewey, $subclass) = $sti->fetchrow;
387 my $dewey=$data->{'dewey'};
388 my $subclass=$data->{'subclass'};
390 ($dewey == 0) && ($dewey='');
391 ($dewey) && ($dewey.=" $subclass");
393 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
394 # print $results[$i];
398 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
399 like '%$search->{'keyword'}%' group by biblionumber");
401 while (my $data=$sth->fetchrow_hashref){
402 $query="Select * from biblio,biblioitems where
403 biblio.biblionumber=$data->{'biblionumber'} and
404 biblio.biblionumber=biblioitems.biblionumber ";
405 if ($search->{'class'} ne ''){
406 my @temp=split(/\|/,$search->{'class'});
408 $query.= " and ( itemtype='$temp[0]'";
409 for (my $i=1;$i<$count;$i++){
410 $query.=" or itemtype='$temp[$i]'";
415 if ($search->{'dewey'} ne ''){
416 $query.= "and (dewey like '$search->{'dewey'}%') ";
418 my $sth2=$dbh->prepare($query);
420 while (my $data2=$sth2->fetchrow_hashref){
421 my $dewey= $data2->{'dewey'};
422 my $subclass=$data2->{'subclass'};
424 ($dewey == 0) && ($dewey='');
425 ($dewey) && ($dewey.=" $subclass") ;
427 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
428 # print $results[$i];
434 @results=sort @results;
441 while ($i2 < $count){
442 if ($results[$i2] ne $res[$i-1]){
443 $res[$i]=$results[$i2];
451 while ($i2 < $num && $i2 < $count){
452 $res2[$i2]=$res[$i2+$offset];
464 my ($env,$type,$search,$num,$offset)=@_;
465 my $dbh = &C4Connect;
468 $search->{'title'}=~ s/'/\\'/g;
469 $search->{'author'}=~ s/'/\\'/g;
470 $search->{'illustrator'}=~ s/'/\\'/g;
471 my $title = lc($search->{'title'});
473 if ($type eq 'loose') {
474 if ($search->{'author'} ne ''){
475 my @key=split(' ',$search->{'author'});
478 $query="select *,biblio.author,biblio.biblionumber from
480 left join additionalauthors
481 on additionalauthors.biblionumber =biblio.biblionumber
483 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
484 additionalauthors.author like '$key[0]%' or additionalauthors.author
488 $query=$query." and (
489 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
490 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
495 if ($search->{'title'} ne ''){
496 my @key=split(' ',$search->{'title'});
499 $query.= " and (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
501 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
504 # $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
505 # for ($i=1;$i<$count;$i++){
506 # $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
508 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
509 for ($i=1;$i<$count;$i++){
510 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
512 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
513 for ($i=1;$i<$count;$i++){
514 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
517 #$query=$query. " and (title like '%$search->{'title'}%'
518 #or seriestitle like '%$search->{'title'}%')";
521 $query.=" group by biblio.biblionumber";
523 if ($search->{'title'} ne '') {
524 if ($search->{'ttype'} eq 'exact'){
525 $query="select * from biblio
527 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
528 or biblio.unititle like '$search->{'title'} |%' or
529 biblio.unititle like '%| $search->{'title'} |%' or
530 biblio.unititle like '%| $search->{'title'}') or
531 (biblio.seriestitle = '$search->{'title'}' or
532 biblio.seriestitle like '$search->{'title'} |%' or
533 biblio.seriestitle like '%| $search->{'title'} |%' or
534 biblio.seriestitle like '%| $search->{'title'}')
537 my @key=split(' ',$search->{'title'});
540 $query="select * from biblio
541 left join bibliosubtitle on
542 biblio.biblionumber=bibliosubtitle.biblionumber
544 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
546 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
549 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%' or subtitle like '% $key[0]')";
550 for ($i=1;$i<$count;$i++){
551 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%' or subtitle like '% $key[$i]')";
553 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
554 for ($i=1;$i<$count;$i++){
555 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
557 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
558 for ($i=1;$i<$count;$i++){
559 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
563 } elsif ($search->{'class'} ne ''){
564 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
565 my @temp=split(/\|/,$search->{'class'});
567 $query.= " and ( itemtype='$temp[0]'";
568 for (my $i=1;$i<$count;$i++){
569 $query.=" or itemtype='$temp[$i]'";
572 if ($search->{'illustrator'} ne ''){
573 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
575 if ($search->{'dewey'} ne ''){
576 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
578 } elsif ($search->{'dewey'} ne ''){
579 $query="select * from biblioitems,biblio
580 where biblio.biblionumber=biblioitems.biblionumber
581 and biblioitems.dewey like '$search->{'dewey'}%'";
582 } elsif ($search->{'illustrator'} ne '') {
583 if ($search->{'illustrator'} ne ''){
584 $query="select * from biblioitems,biblio
585 where biblio.biblionumber=biblioitems.biblionumber
586 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
589 $query .=" group by biblio.biblionumber";
592 if ($type eq 'subject'){
593 my @key=split(' ',$search->{'subject'});
596 $query="select distinct(subject) from bibliosubject where( subject like
597 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
599 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
600 or subject like '% $key[$i]'
601 or subject like '%($key[$i])%')";
604 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
605 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
606 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
607 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
608 $query=~ s/ nz/ NEW ZEALAND/ig;
609 $query=~ s/nz /NEW ZEALAND /ig;
610 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
613 if ($type eq 'precise'){
614 $query="select * from items,biblio ";
615 if ($search->{'item'} ne ''){
616 my $search2=uc $search->{'item'};
617 $query=$query." where
618 items.biblionumber=biblio.biblionumber
619 and barcode='$search2'";
621 if ($search->{'isbn'} ne ''){
622 my $search2=uc $search->{'isbn'};
623 my $query1 = "select * from biblioitems where isbn='$search2'";
624 my $sth1=$dbh->prepare($query1);
627 while (my $data=$sth1->fetchrow_hashref) {
628 $query="select * from biblioitems,biblio where
629 biblio.biblionumber = $data->{'biblionumber'}
630 and biblioitems.biblionumber = biblio.biblionumber";
631 my $sth=$dbh->prepare($query);
633 my $data=$sth->fetchrow_hashref;
634 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
636 ($dewey == 0) && ($dewey='');
637 ($dewey) && ($dewey.=" $subclass");
638 $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
645 if ($type ne 'precise' && $type ne 'subject'){
646 if ($search->{'author'} ne ''){
647 $query=$query." order by biblio.author,title";
649 $query=$query." order by title";
652 if ($type eq 'subject'){
653 $query=$query." order by subject";
656 my $sth=$dbh->prepare($query);
660 my $limit= $num+$offset;
661 while (my $data=$sth->fetchrow_hashref){
662 my $query="select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}";
663 if ($search->{'class'} ne ''){
664 my @temp=split(/\|/,$search->{'class'});
666 $query.= " and ( itemtype='$temp[0]'";
667 for (my $i=1;$i<$count;$i++){
668 $query.=" or itemtype='$temp[$i]'";
672 if ($search->{'dewey'} ne ''){
673 $query.=" and dewey='$search->{'dewey'}' ";
675 if ($search->{'illustrator'} ne ''){
676 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
678 my $sti=$dbh->prepare($query);
683 if (($dewey, $subclass) = $sti->fetchrow || $type eq 'subject'){
687 ($dewey == 0) && ($dewey='');
688 ($dewey) && ($dewey.=" $subclass");
691 if ($count > $offset && $count <= $limit){
692 if ($type ne 'subject' && $type ne 'precise'){
693 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
694 } elsif ($search->{'isbn'} ne '' || $search->{'item'} ne ''){
695 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
697 $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
705 #if ($type ne 'precise'){
709 return($count,@results);
712 sub updatesearchstats{
718 my ($env,$subject)=@_;
720 $subject=$dbh->quote($subject);
721 my $query="Select * from biblio,bibliosubject where
722 biblio.biblionumber=bibliosubject.biblionumber and
723 bibliosubject.subject=$subject group by biblio.biblionumber
724 order by biblio.title";
725 my $sth=$dbh->prepare($query);
729 while (my $data=$sth->fetchrow_hashref){
730 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
740 my ($env,$biblionumber,$type)=@_;
741 my $dbh = &C4Connect;
742 my $query="Select * from items,biblio,biblioitems,branches
743 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
744 and biblioitems.biblionumber=biblio.biblionumber
745 and biblio.biblionumber='$biblionumber' and branches.branchcode=
746 items.holdingbranch ";
748 if ($type ne 'intra'){
749 $query.=" and (items.itemlost<>1 or items.itemlost is NULL)
750 and (wthdrawn <> 1 or wthdrawn is NULL)";
752 $query=$query."order by items.dateaccessioned desc";
753 my $sth=$dbh->prepare($query);
757 while (my $data=$sth->fetchrow_hashref){
758 my $iquery = "Select * from issues
759 where itemnumber = '$data->{'itemnumber'}'
760 and returndate is null";
762 my $isth=$dbh->prepare($iquery);
764 if (my $idata=$isth->fetchrow_hashref){
765 my @temp=split('-',$idata->{'date_due'});
766 $datedue = "$temp[2]/$temp[1]/$temp[0]";
768 if ($data->{'itemlost'} eq '1'){
771 if ($data->{'wthdrawn'} eq '1'){
772 $datedue="Cancelled";
775 my ($rescount,$reserves)=Findgroupreserve($data->{'biblioitemnumber'},$biblionumber);
782 my $class = $data->{'classification'};
783 my $dewey = $data->{'dewey'};
785 if ($dewey eq "000.") { $dewey = "";};
786 if ($dewey < 10){$dewey='00'.$dewey;}
787 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
792 $class = $class.$dewey;
794 $class = $class.$data->{'subclass'};
796 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
797 my @temp=split('-',$data->{'datelastseen'});
798 my $date="$temp[2]/$temp[1]/$temp[0]";
799 $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$class\t$data->{'itemnumber'}\t$data->{'itemtype'}\t$date\t$data->{'biblioitemnumber'}\t$data->{'volumeddesc'}";
800 # print "$results[$i] <br>";
804 my $query2="Select * from aqorders where biblionumber=$biblionumber";
805 my $sth2=$dbh->prepare($query2);
809 if ($data=$sth2->fetchrow_hashref){
810 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
812 $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$ocount\tOn Order\t\t$data->{'itemnumber'}\t$data->{'itemtype'}\t\t$data->{'biblioitemnumber'}\t$data->{'volumeddesc'}";
822 my ($env,$biblionumber)=@_;
823 #debug_msg($env,"GetItems");
824 my $dbh = &C4Connect;
825 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
826 #debug_msg($env,$query);
827 my $sth=$dbh->prepare($query);
829 #debug_msg($env,"executed query");
832 while (my $data=$sth->fetchrow_hashref) {
833 #debug_msg($env,$data->{'biblioitemnumber'});
834 my $dewey = $data->{'dewey'};
836 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
837 $line = $line."\t$data->{'classification'}\t$dewey";
838 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
839 $line = $line."\t$data->{'volume'}\t$data->{number}";
840 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
842 while (my $idata = $isth->fetchrow_hashref) {
843 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
844 if ($idata->{'notforloan'} == 1) {
845 $iline = $iline."NFL ";
847 if ($idata->{'itemlost'} == 1) {
848 $iline = $iline."LOST ";
850 $line = $line."\t$iline";
853 $results[$i] = $line;
864 my $query="Select * from items,biblioitems where barcode='$barcode'
865 and items.biblioitemnumber=biblioitems.biblioitemnumber";
866 my $sth=$dbh->prepare($query);
868 my $data=$sth->fetchrow_hashref;
875 my ($bibnum,$type)=@_;
877 my $query="Select *,biblio.notes
878 from biblio,biblioitems
879 left join bibliosubtitle on
880 biblio.biblionumber=bibliosubtitle.biblionumber
882 where biblio.biblionumber=$bibnum
883 and biblioitems.biblionumber=$bibnum";
884 my $sth=$dbh->prepare($query);
886 my $data=$sth->fetchrow_hashref;
888 $query="Select * from bibliosubject where biblionumber='$bibnum'";
889 $sth=$dbh->prepare($query);
891 while (my $dat=$sth->fetchrow_hashref){
892 $data->{'subject'}.=" | $dat->{'subject'}";
903 my $query="Select *,biblioitems.notes as bnotes from biblio,biblioitems,itemtypes where biblio.biblionumber=
904 biblioitems.biblionumber and biblioitemnumber=$bibitem and
905 biblioitems.itemtype=itemtypes.itemtype";
906 my $sth=$dbh->prepare($query);
908 my $data=$sth->fetchrow_hashref;
917 my $query="Select * from bibliosubject where biblionumber=$bibnum";
918 my $sth=$dbh->prepare($query);
922 while (my $data=$sth->fetchrow_hashref){
928 return($i,\@results);
934 my $query="Select * from additionalauthors where biblionumber=$bibnum";
935 my $sth=$dbh->prepare($query);
939 while (my $data=$sth->fetchrow_hashref){
945 return($i,\@results);
951 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
952 my $sth=$dbh->prepare($query);
956 while (my $data=$sth->fetchrow_hashref){
962 return($i,\@results);
968 my ($bibitem,$biblio)=@_;
970 my $query="Select * from items where
971 items.biblioitemnumber='$bibitem'";
972 my $sth=$dbh->prepare($query) || die $dbh->errstr;
973 $sth->execute || die $sth->errstr;
976 while (my $data=$sth->fetchrow_hashref) {
977 my $query2="select * from issues,borrowers where itemnumber=$data->{'itemnumber'}
978 and returndate is NULL and issues.borrowernumber=borrowers.borrowernumber";
979 my $sth2=$dbh->prepare($query2);
981 if (my $data2=$sth2->fetchrow_hashref) {
982 $data->{'date_due'}=$data2->{'date_due'};
983 $data->{'card'}=$data2->{'cardnumber'};
985 if ($data->{'wthdrawn'} eq '1') {
986 $data->{'date_due'}='Cancelled';
988 $data->{'date_due'}='Available';
992 $query2="select * from issues,borrowers where itemnumber='$data->{'itemnumber'}'
993 and issues.borrowernumber=borrowers.borrowernumber
994 order by date_due desc";
995 my $sth2=$dbh->prepare($query2) || die $dbh->errstr;
996 $sth2->execute || die $sth2->errstr;
997 for (my $i2=0;$i2<2;$i2++){
998 if (my $data2=$sth2->fetchrow_hashref){
999 $data->{"timestamp$i2"}=$data2->{'timestamp'};
1000 $data->{"card$i2"}=$data2->{'cardnumber'};
1001 $data->{"borrower$i2"}=$data2->{'borrowernumber'};
1014 my ($env,$dbh,$itemnumber) = @_;
1016 my $query="Select * from biblio,items,biblioitems
1017 where items.itemnumber = '$itemnumber'
1018 and biblio.biblionumber = items.biblionumber
1019 and biblioitems.biblioitemnumber = items.biblioitemnumber";
1020 my $sth=$dbh->prepare($query);
1022 my $data=$sth->fetchrow_hashref;
1028 #used by member enquiries from the intranet
1029 #called by member.pl
1031 my ($env,$searchstring,$type)=@_;
1032 my $dbh = &C4Connect;
1033 $searchstring=~ s/\'/\\\'/g;
1034 my @data=split(' ',$searchstring);
1036 my $query="Select * from borrowers
1037 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
1038 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
1039 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
1041 for (my $i=1;$i<$count;$i++){
1042 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
1043 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
1044 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
1046 $query=$query.") or cardnumber = \"$searchstring\"
1047 order by surname,firstname";
1048 my $sth=$dbh->prepare($query);
1052 while (my $data=$sth->fetchrow_hashref){
1053 push(@results,$data);
1059 return ($cnt,\@results);
1063 my ($cardnumber,$bornum)=@_;
1064 $cardnumber = uc $cardnumber;
1068 $query="Select * from borrowers where cardnumber='$cardnumber'";
1070 $query="Select * from borrowers where borrowernumber='$bornum'";
1072 my $sth=$dbh->prepare($query);
1074 my $data=$sth->fetchrow_hashref;
1084 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1085 items.itemnumber=issues.itemnumber and
1086 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
1088 my $sth=$dbh->prepare($query);
1092 while (my $data=$sth->fetchrow_hashref){
1098 return($i,\@result);
1102 my ($bornum,$order,$limit)=@_;
1105 $query="Select * from issues,biblio,items,biblioitems
1106 where borrowernumber='$bornum' and
1107 items.biblioitemnumber=biblioitems.biblioitemnumber and
1108 items.itemnumber=issues.itemnumber and
1109 items.biblionumber=biblio.biblionumber";
1110 $query.=" order by $order";
1112 $query.=" limit $limit";
1114 my $sth=$dbh->prepare($query);
1118 while (my $data=$sth->fetchrow_hashref){
1124 return($i,\@result);
1128 my ($env,$bornum)=@_;
1130 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1131 returndate is NULL";
1132 my $sth=$dbh->prepare($query);
1134 my $data=$sth->fetchrow_hashref;
1136 $sth=$dbh->prepare("Select count(*) from issues where
1137 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1139 my $data2=$sth->fetchrow_hashref;
1141 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1142 borrowernumber='$bornum'");
1144 my $data3=$sth->fetchrow_hashref;
1148 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1152 sub getboracctrecord {
1153 my ($env,$params) = @_;
1157 my $query= "Select * from accountlines where
1158 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1159 my $sth=$dbh->prepare($query);
1162 while (my $data=$sth->fetchrow_hashref){
1163 # if ($data->{'itemnumber'} ne ''){
1164 # $query="Select * from items,biblio where items.itemnumber=
1165 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1166 # my $sth2=$dbh->prepare($query);
1168 # my $data2=$sth2->fetchrow_hashref;
1172 $acctlines[$numlines] = $data;
1174 $total = $total+ $data->{'amountoutstanding'};
1178 return ($numlines,\@acctlines,$total);
1182 my ($env,$bibnum,$type)=@_;
1184 my $query="Select * from items where
1185 biblionumber=$bibnum ";
1186 if ($type ne 'intra'){
1187 $query.=" and (itemlost <>1 or itemlost is NULL) and
1188 (wthdrawn <> 1 or wthdrawn is NULL)";
1190 my $sth=$dbh->prepare($query);
1201 while (my $data=$sth->fetchrow_hashref){
1203 my $query2="select * from issues,items where issues.itemnumber=
1204 '$data->{'itemnumber'}' and returndate is NULL
1205 and items.itemnumber=issues.itemnumber and (items.itemlost <>1 or
1206 items.itemlost is NULL)";
1207 my $sth2=$dbh->prepare($query2);
1209 if (my $data2=$sth2->fetchrow_hashref){
1212 if ($data->{'holdingbranch'} eq 'C'){
1215 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1218 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1221 if ($data->{'itemlost'} eq '1'){
1224 if ($data->{'holdingbranch'} eq 'FM'){
1227 if ($data->{'holdingbranch'} eq 'TR'){
1234 my $query2="Select * from aqorders where biblionumber=$bibnum";
1235 my $sth2=$dbh->prepare($query2);
1237 if (my $data=$sth2->fetchrow_hashref){
1238 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1244 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1250 my $query="select description from itemtypes where itemtype='$type'";
1251 my $sth=$dbh->prepare($query);
1253 my $dat=$sth->fetchrow_hashref;
1256 return ($dat->{'description'});
1262 my $query="Select * from biblioitems,itemtypes,items where
1263 biblioitems.biblionumber='$bibnum' and biblioitems.itemtype=itemtypes.itemtype and
1264 biblioitems.biblioitemnumber=items.biblioitemnumber group by
1265 items.biblioitemnumber";
1266 my $sth=$dbh->prepare($query);
1270 while (my $data=$sth->fetchrow_hashref){
1276 return($i,@results);
1280 my ($biblioitemnumber)=@_;
1282 my $query="Select barcode from items where
1283 biblioitemnumber='$biblioitemnumber'";
1284 my $sth=$dbh->prepare($query);
1288 while (my $data=$sth->fetchrow_hashref){
1289 $barcodes[$i]=$data->{'barcode'};
1297 END { } # module clean-up code here (global destructor)