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=bibliosubtitle.biblionumber and
265 biblioitems.biblionumber=biblio.biblionumber and
266 (((title like '$key[0]%' or title like '% $key[0]%')";
268 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
271 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
272 for ($i=1;$i<$count;$i++){
273 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
275 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
276 for ($i=1;$i<$count;$i++){
277 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
279 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
280 for ($i=1;$i<$count;$i++){
281 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
283 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
284 for ($i=1;$i<$count;$i++){
285 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
287 if ($search->{'keyword'} =~ /new zealand/i){
288 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
289 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
290 or author like '% nz %' or author like '% nz')"
292 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
293 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
294 $search->{'keyword'} =~ / nz/i){
295 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
296 or title like '% new zealand' or subtitle like 'new zealand%' or
297 subtitle like '% new zealand %'
298 or subtitle like '% new zealand' or author like 'new zealand%'
299 or author like '% new zealand %' or author like '% new zealand' or
300 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
301 or seriestitle like '% new zealand')"
304 if ($search->{'class'} ne ''){
305 my @temp=split(/\|/,$search->{'class'});
307 $query.= "and ( itemtype='$temp[0]'";
308 for (my $i=1;$i<$count;$i++){
309 $query.=" or itemtype='$temp[$i]'";
313 $query.="group by biblio.biblionumber order by author,title";
315 my $sth=$dbh->prepare($query);
318 while (my $data=$sth->fetchrow_hashref){
319 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
321 my ($dewey, $subclass) = $sti->fetchrow;
323 ($dewey == 0) && ($dewey='');
324 ($dewey) && ($dewey.=" $subclass");
326 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey";
327 # print $results[$i];
331 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
332 like '%$search->{'keyword'}%'");
334 while (my $data=$sth->fetchrow_hashref){
335 $query="Select * from biblio,biblioitems where
336 biblio.biblionumber=$data->{'biblionumber'} and biblio.biblionumber=biblioitems.biblionumber";
337 if ($search->{'class'} ne ''){
338 my @temp=split(/\|/,$search->{'class'});
340 $query.= " and ( itemtype='$temp[0]'";
341 for (my $i=1;$i<$count;$i++){
342 $query.=" or itemtype='$temp[$i]'";
346 my $sth2=$dbh->prepare($query);
349 while (my $data2=$sth2->fetchrow_hashref){
350 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}";
351 # print $results[$i];
357 @results=sort @results;
364 while ($i2 < $count){
365 if ($results[$i2] ne $res[$i-1]){
366 $res[$i]=$results[$i2];
374 while ($i2 < $num && $i2 < $count){
375 $res2[$i2]=$res[$i2+$offset];
386 my ($env,$type,$search,$num,$offset)=@_;
387 my $dbh = &C4Connect;
390 $search->{'title'}=~ s/'/\\'/g;
391 $search->{'author'}=~ s/'/\\'/g;
392 $search->{'illustrator'}=~ s/'/\\'/g;
393 my $title = lc($search->{'title'});
395 if ($type eq 'loose') {
396 if ($search->{'author'} ne ''){
397 my @key=split(' ',$search->{'author'});
400 $query="select *,biblio.author,biblio.biblionumber from
402 left join additionalauthors
403 on additionalauthors.biblionumber =biblio.biblionumber
404 where biblioitems.biblionumber=biblio.biblionumber
406 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
407 additionalauthors.author like '$key[0]%' or additionalauthors.author
411 $query=$query." and (
412 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
413 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
418 if ($search->{'title'} ne ''){
419 $query=$query. " and (title like '%$search->{'title'}%'
420 or seriestitle like '%$search->{'title'}%')";
422 if ($search->{'class'} ne ''){
423 my @temp=split(/\|/,$search->{'class'});
425 $query.= "and ( itemtype='$temp[0]'";
426 for (my $i=1;$i<$count;$i++){
427 $query.=" or itemtype='$temp[$i]'";
431 if ($search->{'dewey'} ne ''){
432 $query.=" and dewey='$search->{'dewey'}' ";
434 if ($search->{'illustrator'} ne ''){
435 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
438 $query.=" group by biblio.biblionumber";
440 if ($search->{'title'} ne '') {
441 if ($search->{'ttype'} eq 'exact'){
442 $query="select * from biblio
444 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
445 or biblio.unititle like '$search->{'title'} |%' or
446 biblio.unititle like '%| $search->{'title'} |%' or
447 biblio.unititle like '%| $search->{'title'}') or
448 (biblio.seriestitle = '$search->{'title'}' or
449 biblio.seriestitle like '$search->{'title'} |%' or
450 biblio.seriestitle like '%| $search->{'title'} |%' or
451 biblio.seriestitle like '%| $search->{'title'}')
454 my @key=split(' ',$search->{'title'});
457 $query="select * from biblio,bibliosubtitle,biblioitems
459 (biblio.biblionumber=bibliosubtitle.biblionumber and
460 biblioitems.biblionumber=biblio.biblionumber) and
461 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
463 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
466 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
467 for ($i=1;$i<$count;$i++){
468 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
470 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0] %' or seriestitle like '% $key[0]')";
471 for ($i=1;$i<$count;$i++){
472 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i] %')";
474 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0] %' or unititle like '% $key[0]')";
475 for ($i=1;$i<$count;$i++){
476 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i] %')";
479 if ($search->{'class'} ne ''){
480 my @temp=split(/\|/,$search->{'class'});
482 $query.= " and ( itemtype='$temp[0]'";
483 for (my $i=1;$i<$count;$i++){
484 $query.=" or itemtype='$temp[$i]'";
488 if ($search->{'dewey'} ne ''){
489 $query.=" and dewey='$search->{'dewey'}' ";
491 if ($search->{'illustrator'} ne ''){
492 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
495 } elsif ($search->{'class'} ne ''){
496 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
497 my @temp=split(/\|/,$search->{'class'});
499 $query.= " and ( itemtype='$temp[0]'";
500 for (my $i=1;$i<$count;$i++){
501 $query.=" or itemtype='$temp[$i]'";
504 if ($search->{'illustrator'} ne ''){
505 $query.=" and illus like '%".$search->{'illustrator'}."%' ";
507 if ($search->{'dewey'} ne ''){
508 $query.=" and biblioitems.dewey like '$search->{'dewey'}%'";
510 } elsif ($search->{'dewey'} ne ''){
511 $query="select * from biblioitems,biblio
512 where biblio.biblionumber=biblioitems.biblionumber
513 and biblioitems.dewey like '$search->{'dewey'}%'";
514 } elsif ($search->{'illustrator'} ne '') {
515 if ($search->{'illustrator'} ne ''){
516 $query="select * from biblioitems,biblio
517 where biblio.biblionumber=biblioitems.biblionumber
518 and biblioitems.illus like '%".$search->{'illustrator'}."%'";
521 $query .=" group by biblio.biblionumber";
524 if ($type eq 'subject'){
525 my @key=split(' ',$search->{'subject'});
528 $query="select distinct(subject) from bibliosubject where( subject like
529 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
531 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
532 or subject like '% $key[$i]'
533 or subject like '%($key[$i])%')";
536 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
537 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
538 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
539 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
540 $query=~ s/ nz/ NEW ZEALAND/ig;
541 $query=~ s/nz /NEW ZEALAND /ig;
542 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
545 if ($type eq 'precise'){
546 $query="select * from items,biblio ";
547 if ($search->{'item'} ne ''){
548 my $search2=uc $search->{'item'};
549 $query=$query." where
550 items.biblionumber=biblio.biblionumber
551 and barcode='$search2'";
553 if ($search->{'isbn'} ne ''){
554 my $search2=uc $search->{'isbn'};
555 my $query1 = "select * from biblioitems where isbn='$search2'";
556 my $sth1=$dbh->prepare($query1);
560 while (my $data=$sth1->fetchrow_hashref) {
561 $query="select * from biblioitems,biblio where
562 biblio.biblionumber = $data->{'biblionumber'}
563 and biblioitems.biblionumber = biblio.biblionumber";
564 my $sth=$dbh->prepare($query);
566 my $data=$sth->fetchrow_hashref;
567 my ($dewey, $subclass) = ($data->{'dewey'}, $data->{'subclass'});
569 ($dewey == 0) && ($dewey='');
570 ($dewey) && ($dewey.=" $subclass");
571 $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'isbn'}\t$data->{'itemtype'}";
579 if ($type ne 'precise' && $type ne 'subject'){
580 if ($search->{'author'} ne ''){
581 $query=$query." order by biblio.author,title";
583 $query=$query." order by title";
586 if ($type eq 'subject'){
587 $query=$query." order by subject";
590 my $sth=$dbh->prepare($query);
594 my $limit= $num+$offset;
595 while (my $data=$sth->fetchrow_hashref){
596 my $sti=$dbh->prepare("select dewey,subclass from biblioitems where biblionumber=$data->{'biblionumber'}");
598 my ($dewey, $subclass) = $sti->fetchrow;
600 ($dewey == 0) && ($dewey='');
601 ($dewey) && ($dewey.=" $subclass");
603 if ($count > $offset && $count <= $limit){
604 if ($type ne 'subject' && $type ne 'precise'){
605 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
606 } elsif ($search->{'isbn'} ne '' || $search->{'item'} ne ''){
607 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
609 $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$dewey\t$data->{'illus'}";
616 #if ($type ne 'precise'){
620 return($count,@results);
623 sub updatesearchstats{
629 my ($env,$subject)=@_;
631 my $query="Select * from biblio,bibliosubject where
632 biblio.biblionumber=bibliosubject.biblionumber and
633 bibliosubject.subject='$subject' group by biblio.biblionumber
634 order by biblio.title";
635 my $sth=$dbh->prepare($query);
640 while (my $data=$sth->fetchrow_hashref){
641 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
651 my ($env,$biblionumber,$type)=@_;
652 my $dbh = &C4Connect;
653 my $query="Select * from items,biblio,biblioitems,branches
654 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
655 and biblioitems.biblionumber=biblio.biblionumber
656 and biblio.biblionumber='$biblionumber' and branches.branchcode=
657 items.holdingbranch ";
659 if ($type ne 'intra'){
660 $query.=" and (items.itemlost<>1 or items.itemlost is NULL)
661 and (wthdrawn <> 1 or wthdrawn is NULL)";
663 $query=$query."order by items.dateaccessioned desc";
664 my $sth=$dbh->prepare($query);
669 while (my $data=$sth->fetchrow_hashref){
670 my $iquery = "Select * from issues
671 where itemnumber = '$data->{'itemnumber'}'
672 and returndate is null";
674 my $isth=$dbh->prepare($iquery);
676 if (my $idata=$isth->fetchrow_hashref){
677 my @temp=split('-',$idata->{'date_due'});
678 $datedue = "$temp[2]/$temp[1]/$temp[0]";
680 if ($data->{'itemlost'} eq '1'){
683 if ($data->{'wthdrawn'} eq '1'){
684 $datedue="Cancelled";
687 my ($rescount,$reserves)=FindReserves($biblionumber,'');
693 my $class = $data->{'classification'};
694 my $dewey = $data->{'dewey'};
696 if ($dewey eq "000.") { $dewey = "";};
697 if ($dewey < 10){$dewey='00'.$dewey;}
698 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
703 $class = $class.$dewey;
704 $class = $class.$data->{'subclass'};
705 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
706 my @temp=split('-',$data->{'datelastseen'});
707 my $date="$temp[2]/$temp[1]/$temp[0]";
708 $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'}";
709 # print "$results[$i] <br>";
718 my ($env,$biblionumber)=@_;
719 #debug_msg($env,"GetItems");
720 my $dbh = &C4Connect;
721 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
722 #debug_msg($env,$query);
723 my $sth=$dbh->prepare($query);
725 #debug_msg($env,"executed query");
728 while (my $data=$sth->fetchrow_hashref) {
729 #debug_msg($env,$data->{'biblioitemnumber'});
730 my $dewey = $data->{'dewey'};
732 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
733 $line = $line."\t$data->{'classification'}\t$dewey";
734 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
735 $line = $line."\t$data->{'volume'}\t$data->{number}";
736 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
738 while (my $idata = $isth->fetchrow_hashref) {
739 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
740 if ($idata->{'notforloan'} == 1) {
741 $iline = $iline."NFL ";
743 if ($idata->{'itemlost'} == 1) {
744 $iline = $iline."LOST ";
746 $line = $line."\t$iline";
749 $results[$i] = $line;
760 my $query="Select * from items,biblioitems where barcode='$barcode'
761 and items.biblioitemnumber=biblioitems.biblioitemnumber";
763 my $sth=$dbh->prepare($query);
765 my $data=$sth->fetchrow_hashref;
772 my ($bibnum,$type)=@_;
774 my $query="Select *,biblio.notes from biblio,biblioitems,bibliosubtitle where biblio.biblionumber=$bibnum
775 and biblioitems.biblionumber=$bibnum and
776 (bibliosubtitle.biblionumber=$bibnum)";
778 my $sth=$dbh->prepare($query);
780 my $data=$sth->fetchrow_hashref;
782 $query="Select * from bibliosubject where biblionumber='$bibnum'";
783 $sth=$dbh->prepare($query);
785 while (my $dat=$sth->fetchrow_hashref){
786 $data->{'subject'}.=" | $dat->{'subject'}";
798 my $query="Select * from biblio,biblioitems,itemtypes where biblio.biblionumber=
799 biblioitems.biblionumber and biblioitemnumber=$bibitem and
800 biblioitems.itemtype=itemtypes.itemtype";
802 my $sth=$dbh->prepare($query);
804 my $data=$sth->fetchrow_hashref;
813 my $query="Select * from bibliosubject where biblionumber=$bibnum";
814 my $sth=$dbh->prepare($query);
818 while (my $data=$sth->fetchrow_hashref){
824 return($i,\@results);
830 my $query="Select * from additionalauthors where biblionumber=$bibnum";
831 my $sth=$dbh->prepare($query);
835 while (my $data=$sth->fetchrow_hashref){
841 return($i,\@results);
847 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
848 my $sth=$dbh->prepare($query);
852 while (my $data=$sth->fetchrow_hashref){
858 return($i,\@results);
864 my ($bibitem,$biblio)=@_;
866 my $query="Select * from items where
867 items.biblioitemnumber='$bibitem'";
868 my $sth=$dbh->prepare($query) || die $dbh->errstr;
869 $sth->execute || die $sth->errstr;
872 while (my $data=$sth->fetchrow_hashref) {
873 my $query2="select * from issues,borrowers where itemnumber=$data->{'itemnumber'}
874 and returndate is NULL and issues.borrowernumber=borrowers.borrowernumber";
875 my $sth2=$dbh->prepare($query2);
877 if (my $data2=$sth2->fetchrow_hashref) {
878 $data->{'date_due'}=$data2->{'date_due'};
879 $data->{'card'}=$data2->{'cardnumber'};
881 if ($data->{'wthdrawn'} eq '1') {
882 $data->{'date_due'}='Cancelled';
884 $data->{'date_due'}='Available';
888 $query2="select * from issues,borrowers where itemnumber='$data->{'itemnumber'}'
889 and issues.borrowernumber=borrowers.borrowernumber
890 order by date_due desc";
891 my $sth2=$dbh->prepare($query2) || die $dbh->errstr;
892 $sth2->execute || die $sth2->errstr;
893 for (my $i2=0;$i2<2;$i2++){
894 if (my $data2=$sth2->fetchrow_hashref){
895 $data->{"timestamp$i2"}=$data2->{'timestamp'};
896 $data->{"card$i2"}=$data2->{'cardnumber'};
897 $data->{"borrower$i2"}=$data2->{'borrowernumber'};
910 my ($env,$dbh,$itemnumber) = @_;
912 my $query="Select * from biblio,items,biblioitems
913 where items.itemnumber = '$itemnumber'
914 and biblio.biblionumber = items.biblionumber
915 and biblioitems.biblioitemnumber = items.biblioitemnumber";
916 my $sth=$dbh->prepare($query);
919 my $data=$sth->fetchrow_hashref;
925 #used by member enquiries from the intranet
928 my ($env,$searchstring,$type)=@_;
929 my $dbh = &C4Connect;
930 $searchstring=~ s/\'/\\\'/g;
931 my @data=split(' ',$searchstring);
933 my $query="Select * from borrowers
934 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
935 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
936 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
938 for (my $i=1;$i<$count;$i++){
939 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
940 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
941 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
943 $query=$query.") or cardnumber = \"$searchstring\"
944 order by surname,firstname";
946 my $sth=$dbh->prepare($query);
950 while (my $data=$sth->fetchrow_hashref){
951 push(@results,$data);
957 return ($cnt,\@results);
961 my ($cardnumber,$bornum)=@_;
962 $cardnumber = uc $cardnumber;
966 $query="Select * from borrowers where cardnumber='$cardnumber'";
968 $query="Select * from borrowers where borrowernumber='$bornum'";
971 my $sth=$dbh->prepare($query);
973 my $data=$sth->fetchrow_hashref;
983 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
984 items.itemnumber=issues.itemnumber and
985 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
988 my $sth=$dbh->prepare($query);
992 while (my $data=$sth->fetchrow_hashref){
1005 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
1006 items.itemnumber=issues.itemnumber and
1007 items.biblionumber=biblio.biblionumber order
1010 my $sth=$dbh->prepare($query);
1014 while (my $data=$sth->fetchrow_hashref){
1020 return($i,\@result);
1026 my ($env,$bornum)=@_;
1028 my $query="Select count(*) from issues where borrowernumber='$bornum' and
1029 returndate is NULL";
1031 my $sth=$dbh->prepare($query);
1033 my $data=$sth->fetchrow_hashref;
1035 $sth=$dbh->prepare("Select count(*) from issues where
1036 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
1038 my $data2=$sth->fetchrow_hashref;
1040 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
1041 borrowernumber='$bornum'");
1043 my $data3=$sth->fetchrow_hashref;
1047 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
1051 my ($env,$params) = @_;
1055 my $query = "Select borrowernumber, accountno, date, amount, description,
1056 dispute, accounttype, amountoutstanding, barcode, title
1057 from accountlines,items,biblio
1058 where borrowernumber = $params->{'borrowernumber'} ";
1059 if ($params->{'acctno'} ne "") {
1060 my $query = $query." and accountlines.accountno = $params->{'acctno'} ";
1062 my $query = $query." and accountlines.itemnumber = items.itemnumber
1063 and items.biblionumber = biblio.biblionumber
1064 and accountlines.amountoutstanding<>0 order by date";
1065 my $sth=$dbh->prepare($query);
1069 while (my $data=$sth->fetchrow_hashref){
1070 $acctlines[$numlines] = $data;
1072 $total = $total+ $data->{'amountoutstanding'};
1074 return ($numlines,\@acctlines,$total);
1079 sub getboracctrecord {
1080 my ($env,$params) = @_;
1084 my $query= "Select * from accountlines where
1085 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1086 my $sth=$dbh->prepare($query);
1090 while (my $data=$sth->fetchrow_hashref){
1091 # if ($data->{'itemnumber'} ne ''){
1092 # $query="Select * from items,biblio where items.itemnumber=
1093 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1094 # my $sth2=$dbh->prepare($query);
1096 # my $data2=$sth2->fetchrow_hashref;
1100 $acctlines[$numlines] = $data;
1102 $total = $total+ $data->{'amountoutstanding'};
1106 return ($numlines,\@acctlines,$total);
1110 my ($env,$bibnum,$type)=@_;
1112 my $query="Select * from items where
1113 biblionumber=$bibnum ";
1114 if ($type ne 'intra'){
1115 $query.=" and (itemlost <>1 or itemlost is NULL) and
1116 (wthdrawn <> 1 or wthdrawn is NULL)";
1118 my $sth=$dbh->prepare($query);
1130 while (my $data=$sth->fetchrow_hashref){
1132 my $query2="select * from issues,items where issues.itemnumber=
1133 '$data->{'itemnumber'}' and returndate is NULL
1134 and items.itemnumber=issues.itemnumber and (items.itemlost <>1 or
1135 items.itemlost is NULL)";
1136 my $sth2=$dbh->prepare($query2);
1138 if (my $data2=$sth2->fetchrow_hashref){
1141 if ($data->{'holdingbranch'} eq 'C'){
1144 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1147 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1150 if ($data->{'itemlost'} eq '1'){
1153 if ($data->{'holdingbranch'} eq 'FM'){
1156 if ($data->{'holdingbranch'} eq 'TR'){
1163 my $query2="Select * from aqorders where biblionumber=$bibnum";
1164 my $sth2=$dbh->prepare($query2);
1166 if (my $data=$sth2->fetchrow_hashref){
1167 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1173 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1179 my $query="select description from itemtypes where itemtype='$type'";
1180 my $sth=$dbh->prepare($query);
1182 my $dat=$sth->fetchrow_hashref;
1185 return ($dat->{'description'});
1191 my $query="Select * from biblioitems,itemtypes,items where
1192 biblioitems.biblionumber='$bibnum' and biblioitems.itemtype=itemtypes.itemtype and
1193 biblioitems.biblioitemnumber=items.biblioitemnumber group by
1194 items.biblioitemnumber";
1195 my $sth=$dbh->prepare($query);
1199 while (my $data=$sth->fetchrow_hashref){
1205 return($i,@results);
1209 my ($biblioitemnumber)=@_;
1211 my $query="Select barcode from items where
1212 biblioitemnumber='$biblioitemnumber'";
1213 my $sth=$dbh->prepare($query);
1217 while (my $data=$sth->fetchrow_hashref){
1218 $barcodes[$i]=$data->{'barcode'};
1226 END { } # module clean-up code here (global destructor)