1 package C4::Search; #asummes C4/Search
3 #requires DBI.pm to be installed
12 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
14 # set the version for version checking
18 @EXPORT = qw(&CatSearch &BornameSearch &ItemInfo &KeywordSearch &subsearch
19 &itemdata &bibdata &GetItems &borrdata &getacctlist &itemnodata &itemcount
20 &OpacSearch &borrdata2 &NewBorrowerNumber &bibitemdata &borrissues
21 &getboracctrecord &ItemType &itemissues &FrontSearch &subject &subtitle
22 &addauthor &bibitems &barcodes &findguarantees &allissues &systemprefs
24 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
26 # your exported package globals go here,
27 # as well as any optionally exported functions
29 @EXPORT_OK = qw($Var1 %Hashit);
32 # non-exported package globals go here
33 use vars qw(@more $stuff);
35 # initalize package globals, first exported ones
40 # then the others (which are still accessible as $Some::Module::stuff)
44 # all file-scoped lexicals must be created before
45 # the functions below that use them.
47 # file-private lexicals go here
51 # here's a file-private function as a closure,
52 # callable as &$priv_func; it cannot be prototyped.
57 # make all your functions, whether exported or not;
61 my $query="select cardnumber,borrowernumber from borrowers where
63 my $sth=$dbh->prepare($query);
67 while (my $data=$sth->fetchrow_hashref){
78 my $query="select guarantor from borrowers where
79 borrowernumber='$bornum'";
80 my $sth=$dbh->prepare($query);
82 my $data=$sth->fetchrow_hashref;
84 $query="Select * from borrowers where
85 borrowernumber='$data->{'guarantor'}'";
86 $sth=$dbh->prepare($query);
88 $data=$sth->fetchrow_hashref;
97 my $sth=$dbh->prepare("select variable,value from systempreferences");
99 while (my ($variable,$value)=$sth->fetchrow) {
100 $systemprefs{$variable}=$value;
104 return(%systemprefs);
107 sub NewBorrowerNumber {
109 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
111 my $data=$sth->fetchrow_hashref;
113 $data->{'max(borrowernumber)'}++;
115 return($data->{'max(borrowernumber)'});
119 my ($env,$type,$search,$num,$offset)=@_;
120 my $dbh = &C4Connect;
121 $search->{'keyword'}=~ s/'/\\'/g;
122 my @key=split(' ',$search->{'keyword'});
126 my $query ="Select count(*) from biblio where
127 ((title like '$key[0]%' or title like '% $key[0]%')";
129 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
132 $query=$query.") or ((author like '$key[0]%' or author like '% $key[0]%')";
135 $query=$query." and (author like '$key[$i]%' or author like '% $key[$i]%')";
138 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
139 for ($i=1;$i<$count;$i++){
140 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
142 $query.= ") or ((notes like '$key[0]%' or notes like '% $key[0]%')";
143 for ($i=1;$i<$count;$i++){
144 $query.=" and (notes like '$key[$i]%' or notes like '% $key[$i]%')";
146 $query=$query.") order by title";
147 my $sth=$dbh->prepare($query);
149 my $data=$sth->fetchrow_hashref;
150 my $count=$data->{'count(*)'};
152 $query=~ s/count\(\*\)/\*/;
153 $query= $query." limit $offset,$num";
154 $sth=$dbh->prepare($query);
158 while (my $data=$sth->fetchrow_hashref){
159 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
161 my ($dewey, $subclass) = $sti->fetchrow;
163 ($dewey == 0) && ($dewey='');
164 ($dewey) && ($dewey.=" $subclass");
166 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$dewey";
171 return($count,@results);
177 my ($env,$type,$search,$num,$offset)=@_;
178 my $dbh = &C4Connect;
179 $search->{'front'}=~ s/ +$//;
180 $search->{'front'}=~ s/'/\\'/;
181 my @key=split(' ',$search->{'front'});
185 my $query ="Select * from biblio,bibliosubtitle where
186 biblio.biblionumber=bibliosubtitle.biblionumber and
187 ((title like '$key[0]%' or title like '% $key[0]%'
188 or subtitle like '$key[0]%' or subtitle like '% $key[0]%'
189 or author like '$key[0]%' or author like '% $key[0]%')";
191 $query=$query." and (title like '%$key[$i]%' or subtitle like '%$key[$i]%')";
194 $query=$query.") group by biblio.biblionumber order by author,title";
196 my $sth=$dbh->prepare($query);
199 while (my $data=$sth->fetchrow_hashref){
200 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
202 my ($dewey, $subclass) = $sti->fetchrow;
204 ($dewey == 0) && ($dewey='');
205 ($dewey) && ($dewey.=" $subclass");
207 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
208 # print $results[$i];
212 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
213 like '%$search->{'keyword'}%'");
215 while (my $data=$sth->fetchrow_hashref){
216 my $sth2=$dbh->prepare("Select * from biblio where
217 biblionumber=$data->{'biblionumber'}");
219 while (my $data2=$sth2->fetchrow_hashref){
221 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data->{'copyrightdate'}";
222 # print $results[$i];
228 @results=sort @results;
233 while ($i2 < $count){
234 if ($results[$i2] ne $res[$i-1]){
235 $res[$i]=$results[$i2];
243 while ($i2 < $num && $i2 < $count){
244 $res2[$i2]=$res[$i2+$offset];
255 my ($env,$type,$search,$num,$offset)=@_;
256 my $dbh = &C4Connect;
257 $search->{'keyword'}=~ s/ +$//;
258 $search->{'keyword'}=~ s/'/\\'/;
259 my @key=split(' ',$search->{'keyword'});
263 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
264 biblio.biblionumber=biblioitems.biblionumber and
265 biblio.biblionumber=bibliosubtitle.biblionumber and
266 (((title like '$key[0]%' or title like '% $key[0]%')";
269 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
272 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
273 for ($i=1;$i<$count;$i++){
274 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
276 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
277 for ($i=1;$i<$count;$i++){
278 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
280 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
281 for ($i=1;$i<$count;$i++){
282 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
284 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
285 for ($i=1;$i<$count;$i++){
286 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
288 if ($search->{'keyword'} =~ /new zealand/i){
289 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
290 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
291 or author like '% nz %' or author like '% nz')"
293 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
294 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
295 $search->{'keyword'} =~ / nz/i){
296 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
297 or title like '% new zealand' or subtitle like 'new zealand%' or
298 subtitle like '% new zealand %'
299 or subtitle like '% new zealand' or author like 'new zealand%'
300 or author like '% new zealand %' or author like '% new zealand' or
301 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
302 or seriestitle like '% new zealand')"
305 if ($search->{'class'} ne ''){
306 my @temp=split(/\|/,$search->{'class'});
308 $query.= "and ( itemtype='$temp[0]'";
309 for (my $i=1;$i<$count;$i++){
310 $query.=" or itemtype='$temp[$i]'";
314 if ($search->{'dewey'} ne ''){
315 $query.= "and (dewey like '$search->{'dewey'}%') ";
317 $query.="group by biblio.biblionumber";
318 #$query.=" order by author,title";
320 my $sth=$dbh->prepare($query);
323 while (my $data=$sth->fetchrow_hashref){
324 # my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}
327 # my ($dewey, $subclass) = $sti->fetchrow;
328 my $dewey=$data->{'dewey'};
329 my $subclass=$data->{'subclass'};
331 ($dewey == 0) && ($dewey='');
332 ($dewey) && ($dewey.=" $subclass");
334 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
335 # print $results[$i];
339 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
340 like '%$search->{'keyword'}%' group by biblionumber");
342 while (my $data=$sth->fetchrow_hashref){
343 $query="Select * from biblio,biblioitems where
344 biblio.biblionumber=$data->{'biblionumber'} and
345 biblio.biblionumber=biblioitems.biblionumber ";
346 if ($search->{'class'} ne ''){
347 my @temp=split(/\|/,$search->{'class'});
349 $query.= " and ( itemtype='$temp[0]'";
350 for (my $i=1;$i<$count;$i++){
351 $query.=" or itemtype='$temp[$i]'";
356 if ($search->{'dewey'} ne ''){
357 $query.= "and (dewey like '$search->{'dewey'}%') ";
359 my $sth2=$dbh->prepare($query);
362 while (my $data2=$sth2->fetchrow_hashref){
363 my $dewey= $data2->{'dewey'};
364 my $subclass=$data2->{'subclass'};
366 ($dewey == 0) && ($dewey='');
367 ($dewey) && ($dewey.=" $subclass") ;
369 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
370 # print $results[$i];
376 @results=sort @results;
383 while ($i2 < $count){
384 if ($results[$i2] ne $res[$i-1]){
385 $res[$i]=$results[$i2];
393 while ($i2 < $num && $i2 < $count){
394 $res2[$i2]=$res[$i2+$offset];
405 my ($env,$type,$search,$num,$offset)=@_;
406 my $dbh = &C4Connect;
409 $search->{'title'}=~ s/'/\\'/g;
410 $search->{'author'}=~ s/'/\\'/g;
411 $search->{'illustrator'}=~ s/'/\\'/g;
412 my $title = lc($search->{'title'});
414 if ($type eq 'loose') {
415 if ($search->{'author'} ne ''){
416 my @key=split(' ',$search->{'author'});
419 $query="select *,biblio.author,biblio.biblionumber from
421 left join additionalauthors
422 on additionalauthors.biblionumber =biblio.biblionumber
423 where biblioitems.biblionumber=biblio.biblionumber
425 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
426 additionalauthors.author like '$key[0]%' or additionalauthors.author
430 $query=$query." and (
431 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
432 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
437 if ($search->{'title'} ne ''){
438 $query=$query. " and (title like '%$search->{'title'}%'
439 or seriestitle like '%$search->{'title'}%')";
441 if ($search->{'class'} ne ''){
442 my @temp=split(/\|/,$search->{'class'});
444 $query.= "and ( itemtype='$temp[0]'";
445 for (my $i=1;$i<$count;$i++){
446 $query.=" or itemtype='$temp[$i]'";
450 if ($search->{'dewey'} ne ''){
451 $query.=" and dewey='$search->{'dewey'}' ";
453 if ($search->{'illustrator'} ne ''){
454 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
457 $query.=" group by biblio.biblionumber";
459 if ($search->{'title'} ne '') {
460 if ($search->{'ttype'} eq 'exact'){
461 $query="select * from biblio
463 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
464 or biblio.unititle like '$search->{'title'} |%' or
465 biblio.unititle like '%| $search->{'title'} |%' or
466 biblio.unititle like '%| $search->{'title'}') or
467 (biblio.seriestitle = '$search->{'title'}' or
468 biblio.seriestitle like '$search->{'title'} |%' or
469 biblio.seriestitle like '%| $search->{'title'} |%' or
470 biblio.seriestitle like '%| $search->{'title'}')
473 my @key=split(' ',$search->{'title'});
476 $query="select * from biblio,bibliosubtitle,biblioitems
478 (biblio.biblionumber=bibliosubtitle.biblionumber and
479 biblioitems.biblionumber=biblio.biblionumber) and
480 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
482 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
485 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
486 for ($i=1;$i<$count;$i++){
487 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
489 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0] %' or seriestitle like '% $key[0]')";
490 for ($i=1;$i<$count;$i++){
491 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i] %')";
493 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0] %' or unititle like '% $key[0]')";
494 for ($i=1;$i<$count;$i++){
495 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i] %')";
498 if ($search->{'class'} ne ''){
499 my @temp=split(/\|/,$search->{'class'});
501 $query.= " and ( itemtype='$temp[0]'";
502 for (my $i=1;$i<$count;$i++){
503 $query.=" or itemtype='$temp[$i]'";
507 if ($search->{'dewey'} ne ''){
508 $query.=" and dewey='$search->{'dewey'}' ";
510 if ($search->{'illustrator'} ne ''){
511 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
514 } elsif ($search->{'class'} ne ''){
515 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
516 my @temp=split(/\|/,$search->{'class'});
518 $query.= " and ( itemtype='$temp[0]'";
519 for (my $i=1;$i<$count;$i++){
520 $query.=" or itemtype='$temp[$i]'";
523 if ($search->{'illustrator'} ne ''){
524 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
526 if ($search->{'dewey'} ne ''){
527 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
529 } elsif ($search->{'dewey'} ne ''){
530 $query="select * from biblioitems,biblio
531 where biblio.biblionumber=biblioitems.biblionumber
532 and biblioitems.dewey like '$search->{'dewey'}%'";
533 } elsif ($search->{'illustrator'} ne '') {
534 if ($search->{'illustrator'} ne ''){
535 $query="select * from biblioitems,biblio
536 where biblio.biblionumber=biblioitems.biblionumber
537 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
540 $query .=" group by biblio.biblionumber";
543 if ($type eq 'subject'){
544 my @key=split(' ',$search->{'subject'});
547 $query="select distinct(subject) from bibliosubject where( subject like
548 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
550 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
551 or subject like '% $key[$i]'
552 or subject like '%($key[$i])%')";
555 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
556 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
557 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
558 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
559 $query=~ s/ nz/ NEW ZEALAND/ig;
560 $query=~ s/nz /NEW ZEALAND /ig;
561 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
564 if ($type eq 'precise'){
565 $query="select * from items,biblio ";
566 if ($search->{'item'} ne ''){
567 my $search2=uc $search->{'item'};
568 $query=$query." where
569 items.biblionumber=biblio.biblionumber
570 and barcode='$search2'";
572 if ($search->{'isbn'} ne ''){
573 my $search2=uc $search->{'isbn'};
574 my $query1 = "select * from biblioitems where isbn='$search2'";
575 my $sth1=$dbh->prepare($query1);
579 while (my $data=$sth1->fetchrow_hashref) {
580 $query="select * from biblioitems,biblio where
581 biblio.biblionumber = $data->{'biblionumber'}
582 and biblioitems.biblionumber = biblio.biblionumber";
583 my $sth=$dbh->prepare($query);
585 my $data=$sth->fetchrow_hashref;
586 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
588 ($dewey == 0) && ($dewey='');
589 ($dewey) && ($dewey.=" $subclass");
590 $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
598 if ($type ne 'precise' && $type ne 'subject'){
599 if ($search->{'author'} ne ''){
600 $query=$query." order by biblio.author,title";
602 $query=$query." order by title";
605 if ($type eq 'subject'){
606 $query=$query." order by subject";
609 my $sth=$dbh->prepare($query);
613 my $limit= $num+$offset;
614 while (my $data=$sth->fetchrow_hashref){
615 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
617 my ($dewey, $subclass) = $sti->fetchrow;
619 ($dewey == 0) && ($dewey='');
620 ($dewey) && ($dewey.=" $subclass");
622 if ($count > $offset && $count <= $limit){
623 if ($type ne 'subject' && $type ne 'precise'){
624 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
625 } elsif ($search->{'isbn'} ne '' || $search->{'item'} ne ''){
626 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
628 $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
635 #if ($type ne 'precise'){
639 return($count,@results);
642 sub updatesearchstats{
648 my ($env,$subject)=@_;
650 my $query="Select * from biblio,bibliosubject where
651 biblio.biblionumber=bibliosubject.biblionumber and
652 bibliosubject.subject='$subject' group by biblio.biblionumber
653 order by biblio.title";
654 my $sth=$dbh->prepare($query);
659 while (my $data=$sth->fetchrow_hashref){
660 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
670 my ($env,$biblionumber,$type)=@_;
671 my $dbh = &C4Connect;
672 my $query="Select * from items,biblio,biblioitems,branches
673 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
674 and biblioitems.biblionumber=biblio.biblionumber
675 and biblio.biblionumber='$biblionumber' and branches.branchcode=
676 items.holdingbranch ";
678 if ($type ne 'intra'){
679 $query.=" and (items.itemlost<>1 or items.itemlost is NULL)
680 and (wthdrawn <> 1 or wthdrawn is NULL)";
682 $query=$query."order by items.dateaccessioned desc";
683 my $sth=$dbh->prepare($query);
688 while (my $data=$sth->fetchrow_hashref){
689 my $iquery = "Select * from issues
690 where itemnumber = '$data->{'itemnumber'}'
691 and returndate is null";
693 my $isth=$dbh->prepare($iquery);
695 if (my $idata=$isth->fetchrow_hashref){
696 my @temp=split('-',$idata->{'date_due'});
697 $datedue = "$temp[2]/$temp[1]/$temp[0]";
699 if ($data->{'itemlost'} eq '1'){
702 if ($data->{'wthdrawn'} eq '1'){
703 $datedue="Cancelled";
706 my ($rescount,$reserves)=FindReserves($biblionumber,'');
712 my $class = $data->{'classification'};
713 my $dewey = $data->{'dewey'};
715 if ($dewey eq "000.") { $dewey = "";};
716 if ($dewey < 10){$dewey='00'.$dewey;}
717 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
722 $class = $class.$dewey;
723 $class = $class.$data->{'subclass'};
724 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
725 my @temp=split('-',$data->{'datelastseen'});
726 my $date="$temp[2]/$temp[1]/$temp[0]";
727 $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'}";
728 # print "$results[$i] <br>";
737 my ($env,$biblionumber)=@_;
738 #debug_msg($env,"GetItems");
739 my $dbh = &C4Connect;
740 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
741 #debug_msg($env,$query);
742 my $sth=$dbh->prepare($query);
744 #debug_msg($env,"executed query");
747 while (my $data=$sth->fetchrow_hashref) {
748 #debug_msg($env,$data->{'biblioitemnumber'});
749 my $dewey = $data->{'dewey'};
751 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
752 $line = $line."\t$data->{'classification'}\t$dewey";
753 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
754 $line = $line."\t$data->{'volume'}\t$data->{number}";
755 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
757 while (my $idata = $isth->fetchrow_hashref) {
758 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
759 if ($idata->{'notforloan'} == 1) {
760 $iline = $iline."NFL ";
762 if ($idata->{'itemlost'} == 1) {
763 $iline = $iline."LOST ";
765 $line = $line."\t$iline";
768 $results[$i] = $line;
779 my $query="Select * from items,biblioitems where barcode='$barcode'
780 and items.biblioitemnumber=biblioitems.biblioitemnumber";
782 my $sth=$dbh->prepare($query);
784 my $data=$sth->fetchrow_hashref;
791 my ($bibnum,$type)=@_;
793 my $query="Select *,biblio.notes from biblio,biblioitems,bibliosubtitle where biblio.biblionumber=$bibnum
794 and biblioitems.biblionumber=$bibnum and
795 (bibliosubtitle.biblionumber=$bibnum)";
797 my $sth=$dbh->prepare($query);
799 my $data=$sth->fetchrow_hashref;
801 $query="Select * from bibliosubject where biblionumber='$bibnum'";
802 $sth=$dbh->prepare($query);
804 while (my $dat=$sth->fetchrow_hashref){
805 $data->{'subject'}.=" | $dat->{'subject'}";
817 my $query="Select * from biblio,biblioitems,itemtypes where biblio.biblionumber=
818 biblioitems.biblionumber and biblioitemnumber=$bibitem and
819 biblioitems.itemtype=itemtypes.itemtype";
821 my $sth=$dbh->prepare($query);
823 my $data=$sth->fetchrow_hashref;
832 my $query="Select * from bibliosubject where biblionumber=$bibnum";
833 my $sth=$dbh->prepare($query);
837 while (my $data=$sth->fetchrow_hashref){
843 return($i,\@results);
849 my $query="Select * from additionalauthors where biblionumber=$bibnum";
850 my $sth=$dbh->prepare($query);
854 while (my $data=$sth->fetchrow_hashref){
860 return($i,\@results);
866 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
867 my $sth=$dbh->prepare($query);
871 while (my $data=$sth->fetchrow_hashref){
877 return($i,\@results);
883 my ($bibitem,$biblio)=@_;
885 my $query="Select * from items where
886 items.biblioitemnumber='$bibitem'";
887 my $sth=$dbh->prepare($query) || die $dbh->errstr;
888 $sth->execute || die $sth->errstr;
891 while (my $data=$sth->fetchrow_hashref) {
892 my $query2="select * from issues,borrowers where itemnumber=$data->{'itemnumber'}
893 and returndate is NULL and issues.borrowernumber=borrowers.borrowernumber";
894 my $sth2=$dbh->prepare($query2);
896 if (my $data2=$sth2->fetchrow_hashref) {
897 $data->{'date_due'}=$data2->{'date_due'};
898 $data->{'card'}=$data2->{'cardnumber'};
900 if ($data->{'wthdrawn'} eq '1') {
901 $data->{'date_due'}='Cancelled';
903 $data->{'date_due'}='Available';
907 $query2="select * from issues,borrowers where itemnumber='$data->{'itemnumber'}'
908 and issues.borrowernumber=borrowers.borrowernumber
909 order by date_due desc";
910 my $sth2=$dbh->prepare($query2) || die $dbh->errstr;
911 $sth2->execute || die $sth2->errstr;
912 for (my $i2=0;$i2<2;$i2++){
913 if (my $data2=$sth2->fetchrow_hashref){
914 $data->{"timestamp$i2"}=$data2->{'timestamp'};
915 $data->{"card$i2"}=$data2->{'cardnumber'};
916 $data->{"borrower$i2"}=$data2->{'borrowernumber'};
929 my ($env,$dbh,$itemnumber) = @_;
931 my $query="Select * from biblio,items,biblioitems
932 where items.itemnumber = '$itemnumber'
933 and biblio.biblionumber = items.biblionumber
934 and biblioitems.biblioitemnumber = items.biblioitemnumber";
935 my $sth=$dbh->prepare($query);
938 my $data=$sth->fetchrow_hashref;
944 #used by member enquiries from the intranet
947 my ($env,$searchstring,$type)=@_;
948 my $dbh = &C4Connect;
949 $searchstring=~ s/\'/\\\'/g;
950 my @data=split(' ',$searchstring);
952 my $query="Select * from borrowers
953 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
954 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
955 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
957 for (my $i=1;$i<$count;$i++){
958 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
959 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
960 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
962 $query=$query.") or cardnumber = \"$searchstring\"
963 order by surname,firstname";
965 my $sth=$dbh->prepare($query);
969 while (my $data=$sth->fetchrow_hashref){
970 push(@results,$data);
976 return ($cnt,\@results);
980 my ($cardnumber,$bornum)=@_;
981 $cardnumber = uc $cardnumber;
985 $query="Select * from borrowers where cardnumber='$cardnumber'";
987 $query="Select * from borrowers where borrowernumber='$bornum'";
990 my $sth=$dbh->prepare($query);
992 my $data=$sth->fetchrow_hashref;
1002 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1003 items.itemnumber=issues.itemnumber and
1004 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
1007 my $sth=$dbh->prepare($query);
1011 while (my $data=$sth->fetchrow_hashref){
1017 return($i,\@result);
1024 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1025 items.itemnumber=issues.itemnumber and
1026 items.biblionumber=biblio.biblionumber order
1029 my $sth=$dbh->prepare($query);
1033 while (my $data=$sth->fetchrow_hashref){
1039 return($i,\@result);
1045 my ($env,$bornum)=@_;
1047 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1048 returndate is NULL";
1050 my $sth=$dbh->prepare($query);
1052 my $data=$sth->fetchrow_hashref;
1054 $sth=$dbh->prepare("Select count(*) from issues where
1055 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1057 my $data2=$sth->fetchrow_hashref;
1059 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1060 borrowernumber='$bornum'");
1062 my $data3=$sth->fetchrow_hashref;
1066 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1070 my ($env,$params) = @_;
1074 my $query = "Select borrowernumber, accountno, date, amount, description,
1075 dispute, accounttype, amountoutstanding, barcode, title
1076 from accountlines,items,biblio
1077 where borrowernumber = $params->{'borrowernumber'} ";
1078 if ($params->{'acctno'} ne "") {
1079 my $query = $query." and accountlines.accountno = $params->{'acctno'} ";
1081 my $query = $query." and accountlines.itemnumber = items.itemnumber
1082 and items.biblionumber = biblio.biblionumber
1083 and accountlines.amountoutstanding<>0 order by date";
1084 my $sth=$dbh->prepare($query);
1088 while (my $data=$sth->fetchrow_hashref){
1089 $acctlines[$numlines] = $data;
1091 $total = $total+ $data->{'amountoutstanding'};
1093 return ($numlines,\@acctlines,$total);
1098 sub getboracctrecord {
1099 my ($env,$params) = @_;
1103 my $query= "Select * from accountlines where
1104 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1105 my $sth=$dbh->prepare($query);
1109 while (my $data=$sth->fetchrow_hashref){
1110 # if ($data->{'itemnumber'} ne ''){
1111 # $query="Select * from items,biblio where items.itemnumber=
1112 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1113 # my $sth2=$dbh->prepare($query);
1115 # my $data2=$sth2->fetchrow_hashref;
1119 $acctlines[$numlines] = $data;
1121 $total = $total+ $data->{'amountoutstanding'};
1125 return ($numlines,\@acctlines,$total);
1129 my ($env,$bibnum,$type)=@_;
1131 my $query="Select * from items where
1132 biblionumber=$bibnum ";
1133 if ($type ne 'intra'){
1134 $query.=" and (itemlost <>1 or itemlost is NULL) and
1135 (wthdrawn <> 1 or wthdrawn is NULL)";
1137 my $sth=$dbh->prepare($query);
1149 while (my $data=$sth->fetchrow_hashref){
1151 my $query2="select * from issues,items where issues.itemnumber=
1152 '$data->{'itemnumber'}' and returndate is NULL
1153 and items.itemnumber=issues.itemnumber and (items.itemlost <>1 or
1154 items.itemlost is NULL)";
1155 my $sth2=$dbh->prepare($query2);
1157 if (my $data2=$sth2->fetchrow_hashref){
1160 if ($data->{'holdingbranch'} eq 'C'){
1163 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1166 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1169 if ($data->{'itemlost'} eq '1'){
1172 if ($data->{'holdingbranch'} eq 'FM'){
1175 if ($data->{'holdingbranch'} eq 'TR'){
1182 my $query2="Select * from aqorders where biblionumber=$bibnum";
1183 my $sth2=$dbh->prepare($query2);
1185 if (my $data=$sth2->fetchrow_hashref){
1186 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1192 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1198 my $query="select description from itemtypes where itemtype='$type'";
1199 my $sth=$dbh->prepare($query);
1201 my $dat=$sth->fetchrow_hashref;
1204 return ($dat->{'description'});
1210 my $query="Select * from biblioitems,itemtypes,items where
1211 biblioitems.biblionumber='$bibnum' and biblioitems.itemtype=itemtypes.itemtype and
1212 biblioitems.biblioitemnumber=items.biblioitemnumber group by
1213 items.biblioitemnumber";
1214 my $sth=$dbh->prepare($query);
1218 while (my $data=$sth->fetchrow_hashref){
1224 return($i,@results);
1228 my ($biblioitemnumber)=@_;
1230 my $query="Select barcode from items where
1231 biblioitemnumber='$biblioitemnumber'";
1232 my $sth=$dbh->prepare($query);
1236 while (my $data=$sth->fetchrow_hashref){
1237 $barcodes[$i]=$data->{'barcode'};
1245 END { } # module clean-up code here (global destructor)