1 package C4::Search; #asummes C4/Search
3 #requires DBI.pm to be installed
13 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
15 # set the version for version checking
19 @EXPORT = qw(&CatSearch &BornameSearch &ItemInfo &KeywordSearch &subsearch
20 &itemdata &bibdata &GetItems &borrdata &getacctlist &itemnodata &itemcount
21 &OpacSearch &borrdata2 &NewBorrowerNumber &bibitemdata &borrissues
22 &getboracctrecord &ItemType &itemissues &FrontSearch &subject &subtitle
23 &addauthor &bibitems &barcodes &findguarantees &allissues &systemprefs
25 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
27 # your exported package globals go here,
28 # as well as any optionally exported functions
30 @EXPORT_OK = qw($Var1 %Hashit);
33 # non-exported package globals go here
34 use vars qw(@more $stuff);
36 # initalize package globals, first exported ones
41 # then the others (which are still accessible as $Some::Module::stuff)
45 # all file-scoped lexicals must be created before
46 # the functions below that use them.
48 # file-private lexicals go here
52 # here's a file-private function as a closure,
53 # callable as &$priv_func; it cannot be prototyped.
58 # make all your functions, whether exported or not;
62 my $query="select cardnumber,borrowernumber from borrowers where
64 my $sth=$dbh->prepare($query);
68 while (my $data=$sth->fetchrow_hashref){
79 my $query="select guarantor from borrowers where
80 borrowernumber='$bornum'";
81 my $sth=$dbh->prepare($query);
83 my $data=$sth->fetchrow_hashref;
85 $query="Select * from borrowers where
86 borrowernumber='$data->{'guarantor'}'";
87 $sth=$dbh->prepare($query);
89 $data=$sth->fetchrow_hashref;
98 my $sth=$dbh->prepare("select variable,value from systempreferences");
100 while (my ($variable,$value)=$sth->fetchrow) {
101 $systemprefs{$variable}=$value;
105 return(%systemprefs);
108 sub NewBorrowerNumber {
110 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
112 my $data=$sth->fetchrow_hashref;
114 $data->{'max(borrowernumber)'}++;
116 return($data->{'max(borrowernumber)'});
120 my ($env,$type,$search,$num,$offset)=@_;
121 my $dbh = &C4Connect;
122 $search->{'keyword'}=~ s/'/\\'/g;
123 my @key=split(' ',$search->{'keyword'});
127 my $query ="Select count(*) from biblio where
128 ((title like '$key[0]%' or title like '% $key[0]%')";
130 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
133 $query=$query.") or ((author like '$key[0]%' or author like '% $key[0]%')";
136 $query=$query." and (author like '$key[$i]%' or author like '% $key[$i]%')";
139 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
140 for ($i=1;$i<$count;$i++){
141 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
143 $query.= ") or ((notes like '$key[0]%' or notes like '% $key[0]%')";
144 for ($i=1;$i<$count;$i++){
145 $query.=" and (notes like '$key[$i]%' or notes like '% $key[$i]%')";
147 $query=$query.") order by title";
148 my $sth=$dbh->prepare($query);
150 my $data=$sth->fetchrow_hashref;
151 my $count=$data->{'count(*)'};
153 $query=~ s/count\(\*\)/\*/;
154 $query= $query." limit $offset,$num";
155 $sth=$dbh->prepare($query);
159 while (my $data=$sth->fetchrow_hashref){
160 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
162 my ($dewey, $subclass) = $sti->fetchrow;
164 ($dewey == 0) && ($dewey='');
165 ($dewey) && ($dewey.=" $subclass");
167 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$dewey";
172 return($count,@results);
178 my ($env,$type,$search,$num,$offset)=@_;
179 my $dbh = &C4Connect;
180 $search->{'front'}=~ s/ +$//;
181 $search->{'front'}=~ s/'/\\'/;
182 my @key=split(' ',$search->{'front'});
186 my $query ="Select * from biblio,bibliosubtitle where
187 biblio.biblionumber=bibliosubtitle.biblionumber and
188 ((title like '$key[0]%' or title like '% $key[0]%'
189 or subtitle like '$key[0]%' or subtitle like '% $key[0]%'
190 or author like '$key[0]%' or author like '% $key[0]%')";
192 $query=$query." and (title like '%$key[$i]%' or subtitle like '%$key[$i]%')";
195 $query=$query.") group by biblio.biblionumber order by author,title";
197 my $sth=$dbh->prepare($query);
200 while (my $data=$sth->fetchrow_hashref){
201 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
203 my ($dewey, $subclass) = $sti->fetchrow;
205 ($dewey == 0) && ($dewey='');
206 ($dewey) && ($dewey.=" $subclass");
208 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
209 # print $results[$i];
213 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
214 like '%$search->{'keyword'}%'");
216 while (my $data=$sth->fetchrow_hashref){
217 my $sth2=$dbh->prepare("Select * from biblio where
218 biblionumber=$data->{'biblionumber'}");
220 while (my $data2=$sth2->fetchrow_hashref){
222 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data->{'copyrightdate'}";
223 # print $results[$i];
229 @results=sort @results;
234 while ($i2 < $count){
235 if ($results[$i2] ne $res[$i-1]){
236 $res[$i]=$results[$i2];
244 while ($i2 < $num && $i2 < $count){
245 $res2[$i2]=$res[$i2+$offset];
256 my ($env,$type,$search,$num,$offset)=@_;
257 my $dbh = &C4Connect;
258 $search->{'keyword'}=~ s/ +$//;
259 $search->{'keyword'}=~ s/'/\\'/;
260 my @key=split(' ',$search->{'keyword'});
264 my $query="Select biblionumber from biblio
265 where ((title like '$key[0]%' or title like '% $key[0]%')";
267 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
270 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
271 for ($i=1;$i<$count;$i++){
272 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
274 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
275 for ($i=1;$i<$count;$i++){
276 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
280 my $sth=$dbh->prepare($query);
283 while (my @res=$sth->fetchrow_array){
284 $results[$i]=$res[0];
288 my $set1=Set::Scalar->new(@results);
289 $query="Select biblionumber from bibliosubtitle where
290 ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
291 for ($i=1;$i<$count;$i++){
292 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
296 $sth=$dbh->prepare($query);
299 while (my @res=$sth->fetchrow_array){
300 $results[$i]=$res[0];
304 my $set2=Set::Scalar->new(@results);
308 $query ="Select biblionumber from biblioitems where
309 ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
310 for ($i=1;$i<$count;$i++){
311 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
315 $sth=$dbh->prepare($query);
318 while (my @res=$sth->fetchrow_array){
319 $results[$i]=$res[0];
323 my $set3=Set::Scalar->new(@results);
327 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
328 like '%$search->{'keyword'}%' group by biblionumber");
331 while (my @res=$sth->fetchrow_array){
332 $results[$i]=$res[0];
336 my $set4=Set::Scalar->new(@results);
345 my @res = $set1->members;
349 # print "count $count";
350 if ($search->{'class'} ne ''){
352 my $query="select * from biblio,biblioitems where
353 biblio.biblionumber='$res[$i2]' and
354 biblio.biblionumber=biblioitems.biblionumber ";
355 if ($search->{'class'} ne ''){
356 my @temp=split(/\|/,$search->{'class'});
358 $query.= "and ( itemtype='$temp[0]'";
359 for (my $i=1;$i<$count;$i++){
360 $query.=" or itemtype='$temp[$i]'";
364 my $sth=$dbh->prepare($query);
367 if (my $data2=$sth->fetchrow_hashref){
368 my $dewey= $data2->{'dewey'};
369 my $subclass=$data2->{'subclass'};
371 ($dewey == 0) && ($dewey='');
372 ($dewey) && ($dewey.=" $subclass") ;
374 my $end=$offset +$num;
379 if ($i4 <=$end && $i4 > $offset){
380 $res2[$i3]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
383 # print "in here $i3<br>";
394 while ($i2 < $num && $i2 < $count){
395 my $query="select * from biblio,biblioitems where
396 biblio.biblionumber='$res[$i2+$offset]' and
397 biblio.biblionumber=biblioitems.biblionumber ";
398 if ($search->{'class'} ne ''){
399 my @temp=split(/\|/,$search->{'class'});
401 $query.= "and ( itemtype='$temp[0]'";
402 for (my $i=1;$i<$count;$i++){
403 $query.=" or itemtype='$temp[$i]'";
407 if ($search->{'dewey'} ne ''){
408 $query.= "and (dewey like '$search->{'dewey'}%') ";
411 my $sth=$dbh->prepare($query);
414 if (my $data2=$sth->fetchrow_hashref){
415 my $dewey= $data2->{'dewey'};
416 my $subclass=$data2->{'subclass'};
418 ($dewey == 0) && ($dewey='');
419 ($dewey) && ($dewey.=" $subclass") ;
421 $res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
431 return($count,@res2);
435 my ($env,$type,$search,$num,$offset)=@_;
436 my $dbh = &C4Connect;
437 $search->{'keyword'}=~ s/ +$//;
438 $search->{'keyword'}=~ s/'/\\'/;
439 my @key=split(' ',$search->{'keyword'});
443 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
444 biblio.biblionumber=biblioitems.biblionumber and
445 biblio.biblionumber=bibliosubtitle.biblionumber and
446 (((title like '$key[0]%' or title like '% $key[0]%')";
448 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
451 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
452 for ($i=1;$i<$count;$i++){
453 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
455 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
456 for ($i=1;$i<$count;$i++){
457 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
459 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
460 for ($i=1;$i<$count;$i++){
461 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
463 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
464 for ($i=1;$i<$count;$i++){
465 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
467 if ($search->{'keyword'} =~ /new zealand/i){
468 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
469 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
470 or author like '% nz %' or author like '% nz')"
472 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
473 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
474 $search->{'keyword'} =~ / nz/i){
475 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
476 or title like '% new zealand' or subtitle like 'new zealand%' or
477 subtitle like '% new zealand %'
478 or subtitle like '% new zealand' or author like 'new zealand%'
479 or author like '% new zealand %' or author like '% new zealand' or
480 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
481 or seriestitle like '% new zealand')"
484 if ($search->{'class'} ne ''){
485 my @temp=split(/\|/,$search->{'class'});
487 $query.= "and ( itemtype='$temp[0]'";
488 for (my $i=1;$i<$count;$i++){
489 $query.=" or itemtype='$temp[$i]'";
493 if ($search->{'dewey'} ne ''){
494 $query.= "and (dewey like '$search->{'dewey'}%') ";
496 $query.="group by biblio.biblionumber";
497 #$query.=" order by author,title";
499 my $sth=$dbh->prepare($query);
502 while (my $data=$sth->fetchrow_hashref){
503 # my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
506 # my ($dewey, $subclass) = $sti->fetchrow;
507 my $dewey=$data->{'dewey'};
508 my $subclass=$data->{'subclass'};
510 ($dewey == 0) && ($dewey='');
511 ($dewey) && ($dewey.=" $subclass");
513 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
514 # print $results[$i];
518 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
519 like '%$search->{'keyword'}%' group by biblionumber");
521 while (my $data=$sth->fetchrow_hashref){
522 $query="Select * from biblio,biblioitems where
523 biblio.biblionumber=$data->{'biblionumber'} and
524 biblio.biblionumber=biblioitems.biblionumber ";
525 if ($search->{'class'} ne ''){
526 my @temp=split(/\|/,$search->{'class'});
528 $query.= " and ( itemtype='$temp[0]'";
529 for (my $i=1;$i<$count;$i++){
530 $query.=" or itemtype='$temp[$i]'";
535 if ($search->{'dewey'} ne ''){
536 $query.= "and (dewey like '$search->{'dewey'}%') ";
538 my $sth2=$dbh->prepare($query);
541 while (my $data2=$sth2->fetchrow_hashref){
542 my $dewey= $data2->{'dewey'};
543 my $subclass=$data2->{'subclass'};
545 ($dewey == 0) && ($dewey='');
546 ($dewey) && ($dewey.=" $subclass") ;
548 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
549 # print $results[$i];
555 @results=sort @results;
562 while ($i2 < $count){
563 if ($results[$i2] ne $res[$i-1]){
564 $res[$i]=$results[$i2];
572 while ($i2 < $num && $i2 < $count){
573 $res2[$i2]=$res[$i2+$offset];
585 my ($env,$type,$search,$num,$offset)=@_;
586 my $dbh = &C4Connect;
589 $search->{'title'}=~ s/'/\\'/g;
590 $search->{'author'}=~ s/'/\\'/g;
591 $search->{'illustrator'}=~ s/'/\\'/g;
592 my $title = lc($search->{'title'});
594 if ($type eq 'loose') {
595 if ($search->{'author'} ne ''){
596 my @key=split(' ',$search->{'author'});
599 $query="select *,biblio.author,biblio.biblionumber from
601 left join additionalauthors
602 on additionalauthors.biblionumber =biblio.biblionumber
603 where biblioitems.biblionumber=biblio.biblionumber
605 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
606 additionalauthors.author like '$key[0]%' or additionalauthors.author
610 $query=$query." and (
611 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
612 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
617 if ($search->{'title'} ne ''){
618 my @key=split(' ',$search->{'title'});
621 $query.= " and (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
623 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
626 # $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
627 # for ($i=1;$i<$count;$i++){
628 # $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
630 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
631 for ($i=1;$i<$count;$i++){
632 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
634 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
635 for ($i=1;$i<$count;$i++){
636 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
639 #$query=$query. " and (title like '%$search->{'title'}%'
640 #or seriestitle like '%$search->{'title'}%')";
642 if ($search->{'class'} ne ''){
643 my @temp=split(/\|/,$search->{'class'});
645 $query.= "and ( itemtype='$temp[0]'";
646 for (my $i=1;$i<$count;$i++){
647 $query.=" or itemtype='$temp[$i]'";
651 if ($search->{'dewey'} ne ''){
652 $query.=" and dewey='$search->{'dewey'}' ";
654 if ($search->{'illustrator'} ne ''){
655 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
658 $query.=" group by biblio.biblionumber";
660 if ($search->{'title'} ne '') {
661 if ($search->{'ttype'} eq 'exact'){
662 $query="select * from biblio
664 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
665 or biblio.unititle like '$search->{'title'} |%' or
666 biblio.unititle like '%| $search->{'title'} |%' or
667 biblio.unititle like '%| $search->{'title'}') or
668 (biblio.seriestitle = '$search->{'title'}' or
669 biblio.seriestitle like '$search->{'title'} |%' or
670 biblio.seriestitle like '%| $search->{'title'} |%' or
671 biblio.seriestitle like '%| $search->{'title'}')
674 my @key=split(' ',$search->{'title'});
677 $query="select * from biblio,biblioitems
678 left join bibliosubtitle on
679 biblio.biblionumber=bibliosubtitle.biblionumber
681 biblioitems.biblionumber=biblio.biblionumber and
682 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
684 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
687 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%' or subtitle like '% $key[0]')";
688 for ($i=1;$i<$count;$i++){
689 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%' or subtitle like '% $key[$i]')";
691 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%' or seriestitle like '% $key[0]')";
692 for ($i=1;$i<$count;$i++){
693 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
695 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0]%' or unititle like '% $key[0]')";
696 for ($i=1;$i<$count;$i++){
697 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i]%')";
700 if ($search->{'class'} ne ''){
701 my @temp=split(/\|/,$search->{'class'});
703 $query.= " and ( itemtype='$temp[0]'";
704 for (my $i=1;$i<$count;$i++){
705 $query.=" or itemtype='$temp[$i]'";
709 if ($search->{'dewey'} ne ''){
710 $query.=" and dewey='$search->{'dewey'}' ";
712 if ($search->{'illustrator'} ne ''){
713 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
716 } elsif ($search->{'class'} ne ''){
717 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
718 my @temp=split(/\|/,$search->{'class'});
720 $query.= " and ( itemtype='$temp[0]'";
721 for (my $i=1;$i<$count;$i++){
722 $query.=" or itemtype='$temp[$i]'";
725 if ($search->{'illustrator'} ne ''){
726 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
728 if ($search->{'dewey'} ne ''){
729 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
731 } elsif ($search->{'dewey'} ne ''){
732 $query="select * from biblioitems,biblio
733 where biblio.biblionumber=biblioitems.biblionumber
734 and biblioitems.dewey like '$search->{'dewey'}%'";
735 } elsif ($search->{'illustrator'} ne '') {
736 if ($search->{'illustrator'} ne ''){
737 $query="select * from biblioitems,biblio
738 where biblio.biblionumber=biblioitems.biblionumber
739 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
742 $query .=" group by biblio.biblionumber";
745 if ($type eq 'subject'){
746 my @key=split(' ',$search->{'subject'});
749 $query="select distinct(subject) from bibliosubject where( subject like
750 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
752 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
753 or subject like '% $key[$i]'
754 or subject like '%($key[$i])%')";
757 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
758 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
759 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
760 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
761 $query=~ s/ nz/ NEW ZEALAND/ig;
762 $query=~ s/nz /NEW ZEALAND /ig;
763 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
766 if ($type eq 'precise'){
767 $query="select * from items,biblio ";
768 if ($search->{'item'} ne ''){
769 my $search2=uc $search->{'item'};
770 $query=$query." where
771 items.biblionumber=biblio.biblionumber
772 and barcode='$search2'";
774 if ($search->{'isbn'} ne ''){
775 my $search2=uc $search->{'isbn'};
776 my $query1 = "select * from biblioitems where isbn='$search2'";
777 my $sth1=$dbh->prepare($query1);
781 while (my $data=$sth1->fetchrow_hashref) {
782 $query="select * from biblioitems,biblio where
783 biblio.biblionumber = $data->{'biblionumber'}
784 and biblioitems.biblionumber = biblio.biblionumber";
785 my $sth=$dbh->prepare($query);
787 my $data=$sth->fetchrow_hashref;
788 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
790 ($dewey == 0) && ($dewey='');
791 ($dewey) && ($dewey.=" $subclass");
792 $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
800 if ($type ne 'precise' && $type ne 'subject'){
801 if ($search->{'author'} ne ''){
802 $query=$query." order by biblio.author,title";
804 $query=$query." order by title";
807 if ($type eq 'subject'){
808 $query=$query." order by subject";
812 my $sth=$dbh->prepare($query);
816 my $limit= $num+$offset;
817 while (my $data=$sth->fetchrow_hashref){
818 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
820 my ($dewey, $subclass) = $sti->fetchrow;
822 ($dewey == 0) && ($dewey='');
823 ($dewey) && ($dewey.=" $subclass");
825 if ($count > $offset && $count <= $limit){
826 if ($type ne 'subject' && $type ne 'precise'){
827 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
828 } elsif ($search->{'isbn'} ne '' || $search->{'item'} ne ''){
829 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
831 $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
838 #if ($type ne 'precise'){
842 return($count,@results);
845 sub updatesearchstats{
851 my ($env,$subject)=@_;
853 $subject=$dbh->quote($subject);
854 my $query="Select * from biblio,bibliosubject where
855 biblio.biblionumber=bibliosubject.biblionumber and
856 bibliosubject.subject=$subject group by biblio.biblionumber
857 order by biblio.title";
858 my $sth=$dbh->prepare($query);
863 while (my $data=$sth->fetchrow_hashref){
864 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
874 my ($env,$biblionumber,$type)=@_;
875 my $dbh = &C4Connect;
876 my $query="Select * from items,biblio,biblioitems,branches
877 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
878 and biblioitems.biblionumber=biblio.biblionumber
879 and biblio.biblionumber='$biblionumber' and branches.branchcode=
880 items.holdingbranch ";
882 if ($type ne 'intra'){
883 $query.=" and (items.itemlost<>1 or items.itemlost is NULL)
884 and (wthdrawn <> 1 or wthdrawn is NULL)";
886 $query=$query."order by items.dateaccessioned desc";
887 my $sth=$dbh->prepare($query);
892 while (my $data=$sth->fetchrow_hashref){
893 my $iquery = "Select * from issues
894 where itemnumber = '$data->{'itemnumber'}'
895 and returndate is null";
897 my $isth=$dbh->prepare($iquery);
899 if (my $idata=$isth->fetchrow_hashref){
900 my @temp=split('-',$idata->{'date_due'});
901 $datedue = "$temp[2]/$temp[1]/$temp[0]";
903 if ($data->{'itemlost'} eq '1'){
906 if ($data->{'wthdrawn'} eq '1'){
907 $datedue="Cancelled";
910 my ($rescount,$reserves)=FindReserves($biblionumber,'');
916 my $class = $data->{'classification'};
917 my $dewey = $data->{'dewey'};
919 if ($dewey eq "000.") { $dewey = "";};
920 if ($dewey < 10){$dewey='00'.$dewey;}
921 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
926 $class = $class.$dewey;
928 $class = $class.$data->{'subclass'};
930 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
931 my @temp=split('-',$data->{'datelastseen'});
932 my $date="$temp[2]/$temp[1]/$temp[0]";
933 $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'}";
934 # print "$results[$i] <br>";
943 my ($env,$biblionumber)=@_;
944 #debug_msg($env,"GetItems");
945 my $dbh = &C4Connect;
946 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
947 #debug_msg($env,$query);
948 my $sth=$dbh->prepare($query);
950 #debug_msg($env,"executed query");
953 while (my $data=$sth->fetchrow_hashref) {
954 #debug_msg($env,$data->{'biblioitemnumber'});
955 my $dewey = $data->{'dewey'};
957 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
958 $line = $line."\t$data->{'classification'}\t$dewey";
959 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
960 $line = $line."\t$data->{'volume'}\t$data->{number}";
961 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
963 while (my $idata = $isth->fetchrow_hashref) {
964 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
965 if ($idata->{'notforloan'} == 1) {
966 $iline = $iline."NFL ";
968 if ($idata->{'itemlost'} == 1) {
969 $iline = $iline."LOST ";
971 $line = $line."\t$iline";
974 $results[$i] = $line;
985 my $query="Select * from items,biblioitems where barcode='$barcode'
986 and items.biblioitemnumber=biblioitems.biblioitemnumber";
988 my $sth=$dbh->prepare($query);
990 my $data=$sth->fetchrow_hashref;
997 my ($bibnum,$type)=@_;
999 my $query="Select *,biblio.notes from biblio,biblioitems,bibliosubtitle where biblio.biblionumber=$bibnum
1000 and biblioitems.biblionumber=$bibnum and
1001 (bibliosubtitle.biblionumber=$bibnum)";
1003 my $sth=$dbh->prepare($query);
1005 my $data=$sth->fetchrow_hashref;
1007 $query="Select * from bibliosubject where biblionumber='$bibnum'";
1008 $sth=$dbh->prepare($query);
1010 while (my $dat=$sth->fetchrow_hashref){
1011 $data->{'subject'}.=" | $dat->{'subject'}";
1023 my $query="Select *,biblioitems.notes as bnotes from biblio,biblioitems,itemtypes where biblio.biblionumber=
1024 biblioitems.biblionumber and biblioitemnumber=$bibitem and
1025 biblioitems.itemtype=itemtypes.itemtype";
1027 my $sth=$dbh->prepare($query);
1029 my $data=$sth->fetchrow_hashref;
1038 my $query="Select * from bibliosubject where biblionumber=$bibnum";
1039 my $sth=$dbh->prepare($query);
1043 while (my $data=$sth->fetchrow_hashref){
1049 return($i,\@results);
1055 my $query="Select * from additionalauthors where biblionumber=$bibnum";
1056 my $sth=$dbh->prepare($query);
1060 while (my $data=$sth->fetchrow_hashref){
1066 return($i,\@results);
1072 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
1073 my $sth=$dbh->prepare($query);
1077 while (my $data=$sth->fetchrow_hashref){
1083 return($i,\@results);
1089 my ($bibitem,$biblio)=@_;
1091 my $query="Select * from items where
1092 items.biblioitemnumber='$bibitem'";
1093 my $sth=$dbh->prepare($query) || die $dbh->errstr;
1094 $sth->execute || die $sth->errstr;
1097 while (my $data=$sth->fetchrow_hashref) {
1098 my $query2="select * from issues,borrowers where itemnumber=$data->{'itemnumber'}
1099 and returndate is NULL and issues.borrowernumber=borrowers.borrowernumber";
1100 my $sth2=$dbh->prepare($query2);
1102 if (my $data2=$sth2->fetchrow_hashref) {
1103 $data->{'date_due'}=$data2->{'date_due'};
1104 $data->{'card'}=$data2->{'cardnumber'};
1106 if ($data->{'wthdrawn'} eq '1') {
1107 $data->{'date_due'}='Cancelled';
1109 $data->{'date_due'}='Available';
1113 $query2="select * from issues,borrowers where itemnumber='$data->{'itemnumber'}'
1114 and issues.borrowernumber=borrowers.borrowernumber
1115 order by date_due desc";
1116 my $sth2=$dbh->prepare($query2) || die $dbh->errstr;
1117 $sth2->execute || die $sth2->errstr;
1118 for (my $i2=0;$i2<2;$i2++){
1119 if (my $data2=$sth2->fetchrow_hashref){
1120 $data->{"timestamp$i2"}=$data2->{'timestamp'};
1121 $data->{"card$i2"}=$data2->{'cardnumber'};
1122 $data->{"borrower$i2"}=$data2->{'borrowernumber'};
1135 my ($env,$dbh,$itemnumber) = @_;
1137 my $query="Select * from biblio,items,biblioitems
1138 where items.itemnumber = '$itemnumber'
1139 and biblio.biblionumber = items.biblionumber
1140 and biblioitems.biblioitemnumber = items.biblioitemnumber";
1141 my $sth=$dbh->prepare($query);
1144 my $data=$sth->fetchrow_hashref;
1150 #used by member enquiries from the intranet
1151 #called by member.pl
1153 my ($env,$searchstring,$type)=@_;
1154 my $dbh = &C4Connect;
1155 $searchstring=~ s/\'/\\\'/g;
1156 my @data=split(' ',$searchstring);
1158 my $query="Select * from borrowers
1159 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
1160 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
1161 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
1163 for (my $i=1;$i<$count;$i++){
1164 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
1165 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
1166 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
1168 $query=$query.") or cardnumber = \"$searchstring\"
1169 order by surname,firstname";
1170 # print $query,"\n";
1171 my $sth=$dbh->prepare($query);
1175 while (my $data=$sth->fetchrow_hashref){
1176 push(@results,$data);
1182 return ($cnt,\@results);
1186 my ($cardnumber,$bornum)=@_;
1187 $cardnumber = uc $cardnumber;
1191 $query="Select * from borrowers where cardnumber='$cardnumber'";
1193 $query="Select * from borrowers where borrowernumber='$bornum'";
1196 my $sth=$dbh->prepare($query);
1198 my $data=$sth->fetchrow_hashref;
1208 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1209 items.itemnumber=issues.itemnumber and
1210 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
1213 my $sth=$dbh->prepare($query);
1217 while (my $data=$sth->fetchrow_hashref){
1223 return($i,\@result);
1227 my ($bornum,$order,$limit)=@_;
1230 $query="Select * from issues,biblio,items,biblioitems
1231 where borrowernumber='$bornum' and
1232 items.biblioitemnumber=biblioitems.biblioitemnumber and
1233 items.itemnumber=issues.itemnumber and
1234 items.biblionumber=biblio.biblionumber";
1235 $query.=" order by $order";
1237 $query.=" limit $limit";
1240 my $sth=$dbh->prepare($query);
1244 while (my $data=$sth->fetchrow_hashref){
1250 return($i,\@result);
1254 my ($env,$bornum)=@_;
1256 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1257 returndate is NULL";
1259 my $sth=$dbh->prepare($query);
1261 my $data=$sth->fetchrow_hashref;
1263 $sth=$dbh->prepare("Select count(*) from issues where
1264 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1266 my $data2=$sth->fetchrow_hashref;
1268 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1269 borrowernumber='$bornum'");
1271 my $data3=$sth->fetchrow_hashref;
1275 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1279 my ($env,$params) = @_;
1283 my $query = "Select borrowernumber, accountno, date, amount, description,
1284 dispute, accounttype, amountoutstanding, barcode, title
1285 from accountlines,items,biblio
1286 where borrowernumber = $params->{'borrowernumber'} ";
1287 if ($params->{'acctno'} ne "") {
1288 my $query = $query." and accountlines.accountno = $params->{'acctno'} ";
1290 my $query = $query." and accountlines.itemnumber = items.itemnumber
1291 and items.biblionumber = biblio.biblionumber
1292 and accountlines.amountoutstanding<>0 order by date";
1293 my $sth=$dbh->prepare($query);
1297 while (my $data=$sth->fetchrow_hashref){
1298 $acctlines[$numlines] = $data;
1300 $total = $total+ $data->{'amountoutstanding'};
1302 return ($numlines,\@acctlines,$total);
1307 sub getboracctrecord {
1308 my ($env,$params) = @_;
1312 my $query= "Select * from accountlines where
1313 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1314 my $sth=$dbh->prepare($query);
1318 while (my $data=$sth->fetchrow_hashref){
1319 # if ($data->{'itemnumber'} ne ''){
1320 # $query="Select * from items,biblio where items.itemnumber=
1321 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1322 # my $sth2=$dbh->prepare($query);
1324 # my $data2=$sth2->fetchrow_hashref;
1328 $acctlines[$numlines] = $data;
1330 $total = $total+ $data->{'amountoutstanding'};
1334 return ($numlines,\@acctlines,$total);
1338 my ($env,$bibnum,$type)=@_;
1340 my $query="Select * from items where
1341 biblionumber=$bibnum ";
1342 if ($type ne 'intra'){
1343 $query.=" and (itemlost <>1 or itemlost is NULL) and
1344 (wthdrawn <> 1 or wthdrawn is NULL)";
1346 my $sth=$dbh->prepare($query);
1358 while (my $data=$sth->fetchrow_hashref){
1360 my $query2="select * from issues,items where issues.itemnumber=
1361 '$data->{'itemnumber'}' and returndate is NULL
1362 and items.itemnumber=issues.itemnumber and (items.itemlost <>1 or
1363 items.itemlost is NULL)";
1364 my $sth2=$dbh->prepare($query2);
1366 if (my $data2=$sth2->fetchrow_hashref){
1369 if ($data->{'holdingbranch'} eq 'C'){
1372 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1375 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1378 if ($data->{'itemlost'} eq '1'){
1381 if ($data->{'holdingbranch'} eq 'FM'){
1384 if ($data->{'holdingbranch'} eq 'TR'){
1391 my $query2="Select * from aqorders where biblionumber=$bibnum";
1392 my $sth2=$dbh->prepare($query2);
1394 if (my $data=$sth2->fetchrow_hashref){
1395 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1401 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1407 my $query="select description from itemtypes where itemtype='$type'";
1408 my $sth=$dbh->prepare($query);
1410 my $dat=$sth->fetchrow_hashref;
1413 return ($dat->{'description'});
1419 my $query="Select * from biblioitems,itemtypes,items where
1420 biblioitems.biblionumber='$bibnum' and biblioitems.itemtype=itemtypes.itemtype and
1421 biblioitems.biblioitemnumber=items.biblioitemnumber group by
1422 items.biblioitemnumber";
1423 my $sth=$dbh->prepare($query);
1427 while (my $data=$sth->fetchrow_hashref){
1433 return($i,@results);
1437 my ($biblioitemnumber)=@_;
1439 my $query="Select barcode from items where
1440 biblioitemnumber='$biblioitemnumber'";
1441 my $sth=$dbh->prepare($query);
1445 while (my $data=$sth->fetchrow_hashref){
1446 $barcodes[$i]=$data->{'barcode'};
1454 END { } # module clean-up code here (global destructor)