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);
23 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
25 # your exported package globals go here,
26 # as well as any optionally exported functions
28 @EXPORT_OK = qw($Var1 %Hashit);
31 # non-exported package globals go here
32 use vars qw(@more $stuff);
34 # initalize package globals, first exported ones
39 # then the others (which are still accessible as $Some::Module::stuff)
43 # all file-scoped lexicals must be created before
44 # the functions below that use them.
46 # file-private lexicals go here
50 # here's a file-private function as a closure,
51 # callable as &$priv_func; it cannot be prototyped.
56 # make all your functions, whether exported or not;
60 my $query="select cardnumber,borrowernumber from borrowers where
62 my $sth=$dbh->prepare($query);
66 while (my $data=$sth->fetchrow_hashref){
75 sub NewBorrowerNumber {
77 my $sth=$dbh->prepare("Select max(borrowernumber) from borrowers");
79 my $data=$sth->fetchrow_hashref;
81 $data->{'max(borrowernumber)'}++;
83 return($data->{'max(borrowernumber)'});
87 my ($env,$type,$search,$num,$offset)=@_;
89 $search->{'keyword'}=~ s/'/\\'/g;
90 my @key=split(' ',$search->{'keyword'});
94 my $query ="Select count(*) from biblio where
95 ((title like '$key[0]%' or title like '% $key[0]%')";
97 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
100 $query=$query.") or ((author like '$key[0]%' or author like '% $key[0]%')";
103 $query=$query." and (author like '$key[$i]%' or author like '% $key[$i]%')";
106 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
107 for ($i=1;$i<$count;$i++){
108 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
110 $query.= ") or ((notes like '$key[0]%' or notes like '% $key[0]%')";
111 for ($i=1;$i<$count;$i++){
112 $query.=" and (notes like '$key[$i]%' or notes like '% $key[$i]%')";
114 $query=$query.") order by title";
115 my $sth=$dbh->prepare($query);
117 my $data=$sth->fetchrow_hashref;
118 my $count=$data->{'count(*)'};
120 $query=~ s/count\(\*\)/\*/;
121 $query= $query." limit $offset,$num";
122 $sth=$dbh->prepare($query);
126 while (my $data=$sth->fetchrow_hashref){
127 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}";
132 return($count,@results);
138 my ($env,$type,$search,$num,$offset)=@_;
139 my $dbh = &C4Connect;
140 $search->{'front'}=~ s/ +$//;
141 $search->{'front'}=~ s/'/\\'/;
142 my @key=split(' ',$search->{'front'});
146 my $query ="Select * from biblio,bibliosubtitle where
147 biblio.biblionumber=bibliosubtitle.biblionumber and
148 ((title like '$key[0]%' or title like '% $key[0]%'
149 or subtitle like '$key[0]%' or subtitle like '% $key[0]%'
150 or author like '$key[0]%' or author like '% $key[0]%')";
152 $query=$query." and (title like '%$key[$i]%' or subtitle like '%$key[$i]%')";
155 $query=$query.") group by biblio.biblionumber order by author,title";
157 my $sth=$dbh->prepare($query);
160 while (my $data=$sth->fetchrow_hashref){
161 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}";
162 # print $results[$i];
166 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
167 like '%$search->{'keyword'}%'");
169 while (my $data=$sth->fetchrow_hashref){
170 my $sth2=$dbh->prepare("Select * from biblio where
171 biblionumber=$data->{'biblionumber'}");
173 while (my $data2=$sth2->fetchrow_hashref){
175 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data->{'copyrightdate'}";
176 # print $results[$i];
182 @results=sort @results;
187 while ($i2 < $count){
188 if ($results[$i2] ne $res[$i-1]){
189 $res[$i]=$results[$i2];
197 while ($i2 < $num && $i2 < $count){
198 $res2[$i2]=$res[$i2+$offset];
209 my ($env,$type,$search,$num,$offset)=@_;
210 my $dbh = &C4Connect;
211 $search->{'keyword'}=~ s/ +$//;
212 $search->{'keyword'}=~ s/'/\\'/;
213 my @key=split(' ',$search->{'keyword'});
217 my $query ="Select * from biblio,bibliosubtitle,biblioitems where
218 biblio.biblionumber=bibliosubtitle.biblionumber and
219 biblioitems.biblionumber=biblio.biblionumber and
220 (((title like '$key[0]%' or title like '% $key[0]%')";
222 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
225 $query.= ") or ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";
226 for ($i=1;$i<$count;$i++){
227 $query.= " and (subtitle like '$key[$i]%' or subtitle like '% $key[$i]%')";
229 $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0]%')";
230 for ($i=1;$i<$count;$i++){
231 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i]%')";
233 $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% $key[0]%')";
234 for ($i=1;$i<$count;$i++){
235 $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% $key[$i]%')";
237 $query.= ") or ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% $key[0]%')";
238 for ($i=1;$i<$count;$i++){
239 $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes like '% $key[$i]%')";
241 if ($search->{'keyword'} =~ /new zealand/i){
242 $query.= "or (title like 'nz%' or title like '% nz %' or title like '% nz' or subtitle like 'nz%'
243 or subtitle like '% nz %' or subtitle like '% nz' or author like 'nz %'
244 or author like '% nz %' or author like '% nz')"
246 if ($search->{'keyword'} eq 'nz' || $search->{'keyword'} eq 'NZ' ||
247 $search->{'keyword'} =~ /nz /i || $search->{'keyword'} =~ / nz /i ||
248 $search->{'keyword'} =~ / nz/i){
249 $query.= "or (title like 'new zealand%' or title like '% new zealand %'
250 or title like '% new zealand' or subtitle like 'new zealand%' or
251 subtitle like '% new zealand %'
252 or subtitle like '% new zealand' or author like 'new zealand%'
253 or author like '% new zealand %' or author like '% new zealand' or
254 seriestitle like 'new zealand%' or seriestitle like '% new zealand %'
255 or seriestitle like '% new zealand')"
258 if ($search->{'class'} ne ''){
259 my @temp=split(/\|/,$search->{'class'});
261 $query.= "and ( itemtype='$temp[0]'";
262 for (my $i=1;$i<$count;$i++){
263 $query.=" or itemtype='$temp[$i]'";
267 $query.="group by biblio.biblionumber order by author,title";
269 my $sth=$dbh->prepare($query);
272 while (my $data=$sth->fetchrow_hashref){
273 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}";
274 # print $results[$i];
278 $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
279 like '%$search->{'keyword'}%'");
281 while (my $data=$sth->fetchrow_hashref){
282 $query="Select * from biblio,biblioitems where
283 biblio.biblionumber=$data->{'biblionumber'} and biblio.biblionumber=biblioitems.biblionumber";
284 if ($search->{'class'} ne ''){
285 my @temp=split(/\|/,$search->{'class'});
287 $query.= " and ( itemtype='$temp[0]'";
288 for (my $i=1;$i<$count;$i++){
289 $query.=" or itemtype='$temp[$i]'";
293 my $sth2=$dbh->prepare($query);
296 while (my $data2=$sth2->fetchrow_hashref){
297 $results[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}";
298 # print $results[$i];
304 @results=sort @results;
311 while ($i2 < $count){
312 if ($results[$i2] ne $res[$i-1]){
313 $res[$i]=$results[$i2];
321 while ($i2 < $num && $i2 < $count){
322 $res2[$i2]=$res[$i2+$offset];
333 my ($env,$type,$search,$num,$offset)=@_;
334 my $dbh = &C4Connect;
337 $search->{'title'}=~ s/'/\\'/g;
338 $search->{'author'}=~ s/'/\\'/g;
339 my $title = lc($search->{'title'});
341 if ($type eq 'loose') {
342 if ($search->{'author'} ne ''){
343 my @key=split(' ',$search->{'author'});
346 $query="select *,biblio.author,biblio.biblionumber from
348 left join additionalauthors
349 on additionalauthors.biblionumber =biblio.biblionumber
350 where biblioitems.biblionumber=biblio.biblionumber
352 ((biblio.author like '$key[0]%' or biblio.author like '% $key[0]%' or
353 additionalauthors.author like '$key[0]%' or additionalauthors.author
357 $query=$query." and (
358 biblio.author like '$key[$i]%' or biblio.author like '% $key[$i]%' or
359 additionalauthors.author like '$key[$i]%' or additionalauthors.author like '% $key[$i]%'
364 if ($search->{'title'} ne ''){
365 $query=$query. " and title like '%$search->{'title'}%'";
367 if ($search->{'class'} ne ''){
368 my @temp=split(/\|/,$search->{'class'});
370 $query.= "and ( itemtype='$temp[0]'";
371 for (my $i=1;$i<$count;$i++){
372 $query.=" or itemtype='$temp[$i]'";
376 if ($search->{'dewey'} ne ''){
377 $query.=" and dewey='$search->{'dewey'}' ";
380 $query.=" group by biblio.biblionumber";
382 if ($search->{'title'} ne ''){
383 if ($search->{'ttype'} eq 'exact'){
384 $query="select * from biblio
386 (biblio.title='$search->{'title'}' or (biblio.unititle = '$search->{'title'}'
387 or biblio.unititle like '$search->{'title'} |%' or
388 biblio.unititle like '%| $search->{'title'} |%' or
389 biblio.unititle like '%| $search->{'title'}') or
390 (biblio.seriestitle = '$search->{'title'}' or
391 biblio.seriestitle like '$search->{'title'} |%' or
392 biblio.seriestitle like '%| $search->{'title'} |%' or
393 biblio.seriestitle like '%| $search->{'title'}')
396 my @key=split(' ',$search->{'title'});
399 $query="select * from biblio,bibliosubtitle,biblioitems
401 (biblio.biblionumber=bibliosubtitle.biblionumber and
402 biblioitems.biblionumber=biblio.biblionumber) and
403 (((title like '$key[0]%' or title like '% $key[0]%' or title like '% $key[0]')";
405 $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%' or title like '% $key[$i]')";
408 $query.=") or ((subtitle like '$key[0]%' or subtitle like '% $key[0] %' or subtitle like '% $key[0]')";
409 for ($i=1;$i<$count;$i++){
410 $query.=" and (subtitle like '$key[$i]%' or subtitle like '% $key[$i] %' or subtitle like '% $key[$i]')";
412 $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% $key[0] %' or seriestitle like '% $key[0]')";
413 for ($i=1;$i<$count;$i++){
414 $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% $key[$i] %')";
416 $query.=") or ((unititle like '$key[0]%' or unititle like '% $key[0] %' or unititle like '% $key[0]')";
417 for ($i=1;$i<$count;$i++){
418 $query.=" and (unititle like '$key[$i]%' or unititle like '% $key[$i] %')";
421 if ($search->{'class'} ne ''){
422 my @temp=split(/\|/,$search->{'class'});
424 $query.= " and ( itemtype='$temp[0]'";
425 for (my $i=1;$i<$count;$i++){
426 $query.=" or itemtype='$temp[$i]'";
430 if ($search->{'dewey'} ne ''){
431 $query.=" and dewey='$search->{'dewey'}' ";
434 } elsif ($search->{'class'} ne ''){
435 $query="select * from biblioitems,biblio where biblio.biblionumber=biblioitems.biblionumber";
436 my @temp=split(/\|/,$search->{'class'});
438 $query.= " and ( itemtype='$temp[0]'";
439 for (my $i=1;$i<$count;$i++){
440 $query.=" or itemtype='$temp[$i]'";
443 } elsif ($search->{'dewey'} ne ''){
444 $query="select * from biblioitems,biblio
445 where biblio.biblionumber=biblioitems.biblionumber
446 and biblioitems.dewey like '$search->{'dewey'}%'";
448 $query .=" group by biblio.biblionumber";
452 if ($type eq 'subject'){
453 my @key=split(' ',$search->{'subject'});
456 $query="select distinct(subject) from bibliosubject where( subject like
457 '$key[0]%' or subject like '% $key[0]%' or subject like '% $key[0]' or subject like '%($key[0])%')";
459 $query.=" and (subject like '$key[$i]%' or subject like '% $key[$i]%'
460 or subject like '% $key[$i]'
461 or subject like '%($key[$i])%')";
464 if ($search->{'subject'} eq 'NZ' || $search->{'subject'} eq 'nz'){
465 $query.= " or (subject like 'NEW ZEALAND %' or subject like '% NEW ZEALAND %'
466 or subject like '% NEW ZEALAND' or subject like '%(NEW ZEALAND)%' ) ";
467 } elsif ( $search->{'subject'} =~ /^nz /i || $search->{'subject'} =~ / nz /i || $search->{'subject'} =~ / nz$/i){
468 $query=~ s/ nz/ NEW ZEALAND/ig;
469 $query=~ s/nz /NEW ZEALAND /ig;
470 $query=~ s/\(nz\)/\(NEW ZEALAND\)/gi;
473 if ($type eq 'precise'){
474 $query="select * from items,biblio ";
475 if ($search->{'item'} ne ''){
476 my $search2=uc $search->{'item'};
477 $query=$query." where
478 items.biblionumber=biblio.biblionumber
479 and barcode='$search2'";
481 if ($search->{'isbn'} ne ''){
482 my $search2=uc $search->{'isbn'};
483 my $query1 = "select * from biblioitems where isbn='$search2'";
484 my $sth1=$dbh->prepare($query1);
488 while (my $data=$sth1->fetchrow_hashref) {
489 $query="select * from biblioitems,biblio where
490 biblio.biblionumber = $data->{'biblionumber'}
491 and biblioitems.biblionumber = biblio.biblionumber";
492 my $sth=$dbh->prepare($query);
494 my $data=$sth->fetchrow_hashref;
495 $results[$i2]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}\t$data->{'isbn'}\t$data->{'itemtype'}";
503 if ($type ne 'precise' && $type ne 'subject'){
504 if ($search->{'author'} ne ''){
505 $query=$query." order by biblio.author,title";
507 $query=$query." order by title";
510 if ($type eq 'subject'){
511 $query=$query." order by subject";
515 my $sth=$dbh->prepare($query);
519 my $limit= $num+$offset;
520 while (my $data=$sth->fetchrow_hashref){
521 if ($count > $offset && $count <= $limit){
522 if ($type ne 'subject' && $type ne 'precise'){
523 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}";
524 } elsif ($search->{'isbn'} ne '' || $search->{'item'} ne ''){
525 $results[$i]="$data->{'author'}\t$data->{'title'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}";
527 $results[$i]="$data->{'author'}\t$data->{'subject'}\t$data->{'biblionumber'}\t$data->{'copyrightdate'}";
534 #if ($type ne 'precise'){
538 return($count,@results);
541 sub updatesearchstats{
547 my ($env,$subject)=@_;
549 my $query="Select * from biblio,bibliosubject where
550 biblio.biblionumber=bibliosubject.biblionumber and
551 bibliosubject.subject='$subject' group by biblio.biblionumber
552 order by biblio.title";
553 my $sth=$dbh->prepare($query);
558 while (my $data=$sth->fetchrow_hashref){
559 $results[$i]="$data->{'title'}\t$data->{'author'}\t$data->{'biblionumber'}";
569 my ($env,$biblionumber,$type)=@_;
570 my $dbh = &C4Connect;
571 my $query="Select * from items,biblio,biblioitems,branches
572 where (items.biblioitemnumber = biblioitems.biblioitemnumber)
573 and biblioitems.biblionumber=biblio.biblionumber
574 and biblio.biblionumber='$biblionumber' and branches.branchcode=
575 items.holdingbranch ";
577 if ($type ne 'intra'){
578 $query.=" and (items.itemlost<>1 or items.itemlost is NULL)
579 and (wthdrawn <> 1 or wthdrawn is NULL)";
581 $query=$query."order by items.dateaccessioned desc";
582 my $sth=$dbh->prepare($query);
587 while (my $data=$sth->fetchrow_hashref){
588 my $iquery = "Select * from issues
589 where itemnumber = '$data->{'itemnumber'}'
590 and returndate is null";
592 my $isth=$dbh->prepare($iquery);
594 if (my $idata=$isth->fetchrow_hashref){
595 my @temp=split('-',$idata->{'date_due'});
596 $datedue = "$temp[2]/$temp[1]/$temp[0]";
598 if ($data->{'itemlost'} eq '1'){
601 if ($data->{'wthdrawn'} eq '1'){
602 $datedue="Cancelled";
605 my ($rescount,$reserves)=FindReserves($biblionumber,'');
611 my $class = $data->{'classification'};
612 my $dewey = $data->{'dewey'};
614 if ($dewey eq "000.") { $dewey = "";};
615 if ($dewey < 10){$dewey='00'.$dewey;}
616 if ($dewey < 100 && $dewey > 10){$dewey='0'.$dewey;}
621 $class = $class.$dewey;
622 $class = $class.$data->{'subclass'};
623 # $results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
624 my @temp=split('-',$data->{'datelastseen'});
625 my $date="$temp[2]/$temp[1]/$temp[0]";
626 $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'}";
627 # print "$results[$i] <br>";
636 my ($env,$biblionumber)=@_;
637 #debug_msg($env,"GetItems");
638 my $dbh = &C4Connect;
639 my $query = "Select * from biblioitems where (biblionumber = $biblionumber)";
640 #debug_msg($env,$query);
641 my $sth=$dbh->prepare($query);
643 #debug_msg($env,"executed query");
646 while (my $data=$sth->fetchrow_hashref) {
647 #debug_msg($env,$data->{'biblioitemnumber'});
648 my $dewey = $data->{'dewey'};
650 my $line = $data->{'biblioitemnumber'}."\t".$data->{'itemtype'};
651 $line = $line."\t$data->{'classification'}\t$dewey";
652 $line = $line."\t$data->{'subclass'}\t$data->{isbn}";
653 $line = $line."\t$data->{'volume'}\t$data->{number}";
654 my $isth= $dbh->prepare("select * from items where biblioitemnumber = $data->{'biblioitemnumber'}");
656 while (my $idata = $isth->fetchrow_hashref) {
657 my $iline = $idata->{'barcode'}."[".$idata->{'holdingbranch'}."[";
658 if ($idata->{'notforloan'} == 1) {
659 $iline = $iline."NFL ";
661 if ($idata->{'itemlost'} == 1) {
662 $iline = $iline."LOST ";
664 $line = $line."\t$iline";
667 $results[$i] = $line;
678 my $query="Select * from items,biblioitems where barcode='$barcode'
679 and items.biblioitemnumber=biblioitems.biblioitemnumber";
681 my $sth=$dbh->prepare($query);
683 my $data=$sth->fetchrow_hashref;
690 my ($bibnum,$type)=@_;
692 my $query="Select *,biblio.notes from biblio,biblioitems,bibliosubtitle where biblio.biblionumber=$bibnum
693 and biblioitems.biblionumber=$bibnum and
694 (bibliosubtitle.biblionumber=$bibnum)";
696 my $sth=$dbh->prepare($query);
698 my $data=$sth->fetchrow_hashref;
700 $query="Select * from bibliosubject where biblionumber='$bibnum'";
701 $sth=$dbh->prepare($query);
703 while (my $dat=$sth->fetchrow_hashref){
704 $data->{'subject'}.=" | $dat->{'subject'}";
716 my $query="Select * from biblio,biblioitems,itemtypes where biblio.biblionumber=
717 biblioitems.biblionumber and biblioitemnumber=$bibitem and
718 biblioitems.itemtype=itemtypes.itemtype";
720 my $sth=$dbh->prepare($query);
722 my $data=$sth->fetchrow_hashref;
731 my $query="Select * from bibliosubject where biblionumber=$bibnum";
732 my $sth=$dbh->prepare($query);
736 while (my $data=$sth->fetchrow_hashref){
742 return($i,\@results);
748 my $query="Select * from additionalauthors where biblionumber=$bibnum";
749 my $sth=$dbh->prepare($query);
753 while (my $data=$sth->fetchrow_hashref){
759 return($i,\@results);
765 my $query="Select * from bibliosubtitle where biblionumber=$bibnum";
766 my $sth=$dbh->prepare($query);
770 while (my $data=$sth->fetchrow_hashref){
776 return($i,\@results);
782 my ($bibitem,$biblio)=@_;
784 my $query="Select * from items where
785 items.biblioitemnumber='$bibitem'";
787 my $sth=$dbh->prepare($query) || die $dbh->errstr;
788 $sth->execute || die $sth->errstr;
791 while (my $data=$sth->fetchrow_hashref){
792 my $query2="select * from issues,borrowers where itemnumber=$data->{'itemnumber'}
793 and returndate is NULL and issues.borrowernumber=borrowers.borrowernumber";
794 my $sth2=$dbh->prepare($query2);
796 if (my $data2=$sth2->fetchrow_hashref){
797 $data->{'date_due'}=$data2->{'date_due'};
798 $data->{'card'}=$data2->{'cardnumber'};
800 if ($data->{'wthdrawn'} eq '1'){
801 $data->{'date_due'}='Cancelled';
803 # my ($rescount,$reserves)=FindReserves($biblio,'');
804 # if ($rescount >0){#
805 # $data->{'date_due'}='Request';
807 $data->{'date_due'}='Available';
812 $query2="select * from issues,borrowers where itemnumber='$data->{'itemnumber'}'
813 and issues.borrowernumber=borrowers.borrowernumber
814 order by date_due desc";
815 my $sth2=$dbh->prepare($query2) || die $dbh->errstr;
817 $sth2->execute || die $sth2->errstr;
818 for (my $i2=0;$i2<2;$i2++){
819 if (my $data2=$sth2->fetchrow_hashref){
820 $data->{"timestamp$i2"}=$data2->{'timestamp'};
821 $data->{"card$i2"}=$data2->{'cardnumber'};
822 $data->{"borrower$i2"}=$data2->{'borrowernumber'};
835 my ($env,$dbh,$itemnumber) = @_;
837 my $query="Select * from biblio,items,biblioitems
838 where items.itemnumber = '$itemnumber'
839 and biblio.biblionumber = items.biblionumber
840 and biblioitems.biblioitemnumber = items.biblioitemnumber";
841 my $sth=$dbh->prepare($query);
844 my $data=$sth->fetchrow_hashref;
850 #used by member enquiries from the intranet
853 my ($env,$searchstring,$type)=@_;
854 my $dbh = &C4Connect;
855 $searchstring=~ s/\'/\\\'/g;
856 my @data=split(' ',$searchstring);
858 my $query="Select * from borrowers
859 where ((surname like \"$data[0]%\" or surname like \"% $data[0]%\"
860 or firstname like \"$data[0]%\" or firstname like \"% $data[0]%\"
861 or othernames like \"$data[0]%\" or othernames like \"% $data[0]%\")
863 for (my $i=1;$i<$count;$i++){
864 $query=$query." and (surname like \"$data[$i]%\" or surname like \"% $data[$i]%\"
865 or firstname like \"$data[$i]%\" or firstname like \"% $data[$i]%\"
866 or othernames like \"$data[$i]%\" or othernames like \"% $data[$i]%\")";
868 $query=$query.") or cardnumber = \"$searchstring\"
869 order by surname,firstname";
871 my $sth=$dbh->prepare($query);
875 while (my $data=$sth->fetchrow_hashref){
876 push(@results,$data);
882 return ($cnt,\@results);
886 my ($cardnumber,$bornum)=@_;
887 $cardnumber = uc $cardnumber;
891 $query="Select * from borrowers where cardnumber='$cardnumber'";
893 $query="Select * from borrowers where borrowernumber='$bornum'";
896 my $sth=$dbh->prepare($query);
898 my $data=$sth->fetchrow_hashref;
908 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
909 items.itemnumber=issues.itemnumber and
910 items.biblionumber=biblio.biblionumber and issues.returndate is NULL order
913 my $sth=$dbh->prepare($query);
917 while (my $data=$sth->fetchrow_hashref){
930 $query="Select * from issues,biblio,items where borrowernumber='$bornum' and
931 items.itemnumber=issues.itemnumber and
932 items.biblionumber=biblio.biblionumber order
935 my $sth=$dbh->prepare($query);
939 while (my $data=$sth->fetchrow_hashref){
951 my ($env,$bornum)=@_;
953 my $query="Select count(*) from issues where borrowernumber='$bornum' and
956 my $sth=$dbh->prepare($query);
958 my $data=$sth->fetchrow_hashref;
960 $sth=$dbh->prepare("Select count(*) from issues where
961 borrowernumber='$bornum' and date_due < now() and returndate is NULL");
963 my $data2=$sth->fetchrow_hashref;
965 $sth=$dbh->prepare("Select sum(amountoutstanding) from accountlines where
966 borrowernumber='$bornum'");
968 my $data3=$sth->fetchrow_hashref;
972 return($data2->{'count(*)'},$data->{'count(*)'},$data3->{'sum(amountoutstanding)'});
976 my ($env,$params) = @_;
980 my $query = "Select borrowernumber, accountno, date, amount, description,
981 dispute, accounttype, amountoutstanding, barcode, title
982 from accountlines,items,biblio
983 where borrowernumber = $params->{'borrowernumber'} ";
984 if ($params->{'acctno'} ne "") {
985 my $query = $query." and accountlines.accountno = $params->{'acctno'} ";
987 my $query = $query." and accountlines.itemnumber = items.itemnumber
988 and items.biblionumber = biblio.biblionumber
989 and accountlines.amountoutstanding<>0 order by date";
990 my $sth=$dbh->prepare($query);
994 while (my $data=$sth->fetchrow_hashref){
995 $acctlines[$numlines] = $data;
997 $total = $total+ $data->{'amountoutstanding'};
999 return ($numlines,\@acctlines,$total);
1004 sub getboracctrecord {
1005 my ($env,$params) = @_;
1009 my $query= "Select * from accountlines where
1010 borrowernumber=$params->{'borrowernumber'} order by date desc,timestamp desc";
1011 my $sth=$dbh->prepare($query);
1015 while (my $data=$sth->fetchrow_hashref){
1016 # if ($data->{'itemnumber'} ne ''){
1017 # $query="Select * from items,biblio where items.itemnumber=
1018 # '$data->{'itemnumber'}' and biblio.biblionumber=items.biblionumber";
1019 # my $sth2=$dbh->prepare($query);
1021 # my $data2=$sth2->fetchrow_hashref;
1025 $acctlines[$numlines] = $data;
1027 $total = $total+ $data->{'amountoutstanding'};
1031 return ($numlines,\@acctlines,$total);
1035 my ($env,$bibnum,$type)=@_;
1037 my $query="Select * from items where
1038 biblionumber=$bibnum ";
1039 if ($type ne 'intra'){
1040 $query.=" and (itemlost <>1 or itemlost is NULL) and
1041 (wthdrawn <> 1 or wthdrawn is NULL)";
1043 my $sth=$dbh->prepare($query);
1055 while (my $data=$sth->fetchrow_hashref){
1057 my $query2="select * from issues,items where issues.itemnumber=
1058 '$data->{'itemnumber'}' and returndate is NULL
1059 and items.itemnumber=issues.itemnumber and (items.itemlost <>1 or
1060 items.itemlost is NULL)";
1061 my $sth2=$dbh->prepare($query2);
1063 if (my $data2=$sth2->fetchrow_hashref){
1066 if ($data->{'holdingbranch'} eq 'C'){
1069 if ($data->{'holdingbranch'} eq 'F' || $data->{'holdingbranch'} eq 'FP'){
1072 if ($data->{'holdingbranch'} eq 'S' || $data->{'holdingbranch'} eq 'SP'){
1075 if ($data->{'itemlost'} eq '1'){
1078 if ($data->{'holdingbranch'} eq 'FM'){
1081 if ($data->{'holdingbranch'} eq 'TR'){
1088 my $query2="Select * from aqorders where biblionumber=$bibnum";
1089 my $sth2=$dbh->prepare($query2);
1091 if (my $data=$sth2->fetchrow_hashref){
1092 $ocount=$data->{'quantity'} - $data->{'quantityreceived'};
1098 return ($count,$lcount,$nacount,$fcount,$scount,$lostcount,$mending,$transit,$ocount);
1104 my $query="select description from itemtypes where itemtype='$type'";
1105 my $sth=$dbh->prepare($query);
1107 my $dat=$sth->fetchrow_hashref;
1110 return ($dat->{'description'});
1116 my $query="Select * from biblioitems,itemtypes,items where
1117 biblioitems.biblionumber='$bibnum' and biblioitems.itemtype=itemtypes.itemtype and
1118 biblioitems.biblioitemnumber=items.biblioitemnumber group by
1119 items.biblioitemnumber";
1120 my $sth=$dbh->prepare($query);
1124 while (my $data=$sth->fetchrow_hashref){
1130 return($i,@results);
1134 my ($biblioitemnumber)=@_;
1136 my $query="Select barcode from items where
1137 biblioitemnumber='$biblioitemnumber'";
1138 my $sth=$dbh->prepare($query);
1142 while (my $data=$sth->fetchrow_hashref){
1143 $barcodes[$i]=$data->{'barcode'};
1151 END { } # module clean-up code here (global destructor)