1 package C4::Acquisitions; #asummes C4/Acquisitions.pm
7 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
9 # set the version for version checking
13 @EXPORT = qw(&getorders &bookseller &breakdown &basket &newbasket &bookfunds
14 &ordersearch &newbiblio &newbiblioitem &newsubject &newsubtitle &neworder
15 &newordernum &modbiblio &modorder &getsingleorder &invoice &receiveorder
16 &bookfundbreakdown &curconvert &updatesup &insertsup &newitems &modbibitem
17 &getcurrencies &modsubtitle &modsubject &modaddauthor &moditem &countitems
18 &findall &needsmod &delitem &delbibitem &delbiblio &delorder &branches
19 &getallorders &getrecorders &updatecurrencies &getorder &getcurrency &updaterecorder
20 &updatecost &checkitems &modnote &getitemtypes &getbiblio
21 &getbiblioitem &getitemsbybiblioitem &isbnsearch &keywordsearch
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
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;
63 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
64 booksellerid='$supplierid' and (quantity > quantityreceived or
65 quantityreceived is NULL)
66 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
67 $query.=" group by basketno order by entrydate desc";
69 my $sth=$dbh->prepare($query);
73 while (my $data=$sth->fetchrow_hashref){
79 return ($i,\@results);
85 my $query="Select count(*) from items where biblionumber=$biblio";
87 my $sth=$dbh->prepare($query);
89 my $data=$sth->fetchrow_hashref;
92 return($data->{'count(*)'});
98 my $query="Select ordernumber from aqorders where biblionumber=$bib and
99 biblioitemnumber='$bi'";
100 my $sth=$dbh->prepare($query);
102 my $ordnum=$sth->fetchrow_hashref;
104 my $order=getsingleorder($ordnum->{'ordernumber'});
107 return ($order,$ordnum->{'ordernumber'});
113 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
114 where aqorders.ordernumber='$ordnum'
115 and biblio.biblionumber=aqorders.biblionumber and
116 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
117 aqorders.ordernumber=aqorderbreakdown.ordernumber";
118 my $sth=$dbh->prepare($query);
120 my $data=$sth->fetchrow_hashref;
129 my $query="Select * from aqorders,biblio,biblioitems where
130 booksellerinvoicenumber='$invoice'
131 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
132 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
135 my $sth=$dbh->prepare($query);
137 while (my $data=$sth->fetchrow_hashref){
147 #gets all orders from a certain supplier, orders them alphabetically
150 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
151 and (cancelledby is NULL or cancelledby = '')
152 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
153 aqorders.biblioitemnumber
154 group by aqorders.biblioitemnumber
159 my $sth=$dbh->prepare($query);
161 while (my $data=$sth->fetchrow_hashref){
171 #gets all orders from a certain supplier, orders them alphabetically
174 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
175 and (cancelledby is NULL or cancelledby = '')
176 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
177 aqorders.biblioitemnumber and
178 aqorders.quantityreceived>0
179 and aqorders.datereceived >=now()
180 group by aqorders.biblioitemnumber
185 my $sth=$dbh->prepare($query);
187 while (my $data=$sth->fetchrow_hashref){
197 my ($search,$biblio,$catview) = @_;
199 my $query = "Select *,biblio.title from aqorders,biblioitems,biblio
200 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
201 and biblio.biblionumber=aqorders.biblionumber
202 and ((datecancellationprinted is NULL)
203 or (datecancellationprinted = '0000-00-00')
205 my @data = split(' ',$search);
207 for (my $i = 0; $i < $count; $i++) {
208 $query .= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
211 $query.=" ) or biblioitems.isbn='$search'
212 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
213 if ($catview ne 'yes'){
214 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
216 $query.=" group by aqorders.ordernumber";
217 my $sth=$dbh->prepare($query);
222 while (my $data=$sth->fetchrow_hashref){
223 my $sth2=$dbh->prepare("Select * from biblio where
224 biblionumber='$data->{'biblionumber'}'");
226 my $data2=$sth2->fetchrow_hashref;
228 $data->{'author'}=$data2->{'author'};
229 $data->{'seriestitle'}=$data2->{'seriestitle'};
230 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
231 ordernumber=$data->{'ordernumber'}");
233 $data2=$sth2->fetchrow_hashref;
235 $data->{'branchcode'}=$data2->{'branchcode'};
236 $data->{'bookfundid'}=$data2->{'bookfundid'};
247 my ($searchstring)=@_;
249 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
250 id = '$searchstring'";
251 my $sth=$dbh->prepare($query);
255 while (my $data=$sth->fetchrow_hashref){
267 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
268 my $sth=$dbh->prepare($query);
272 while (my $data=$sth->fetchrow_hashref){
278 return($i,\@results);
282 my ($basketno,$supplier)=@_;
284 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
285 where basketno='$basketno'
286 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
287 =aqorders.biblioitemnumber
288 and (datecancellationprinted is NULL or datecancellationprinted =
290 if ($supplier ne ''){
291 $query.=" and aqorders.booksellerid='$supplier'";
293 $query.=" group by aqorders.ordernumber";
294 my $sth=$dbh->prepare($query);
299 while (my $data=$sth->fetchrow_hashref){
310 my $query="Select max(basketno) from aqorders";
311 my $sth=$dbh->prepare($query);
313 my $data=$sth->fetchrow_arrayref;
314 my $basket=$$data[0];
323 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
325 and aqbudget.startdate='2001=07-01'
326 group by aqbookfund.bookfundid order by bookfundname";
327 my $sth=$dbh->prepare($query);
331 while (my $data=$sth->fetchrow_hashref){
342 my $query="Select * from branches";
343 my $sth=$dbh->prepare($query);
347 while (my $data=$sth->fetchrow_hashref){
356 sub bookfundbreakdown {
359 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
360 from aqorders,aqorderbreakdown where bookfundid='$id' and
361 aqorders.ordernumber=aqorderbreakdown.ordernumber and ((budgetdate >=
362 '2001-07-01' and budgetdate <'2002-07-01') or
363 (datereceived >= '2001-07-01' and datereceived < '2002-07-01'))
364 and (datecancellationprinted is NULL or
365 datecancellationprinted='0000-00-00')";
366 my $sth=$dbh->prepare($query);
370 while (my $data=$sth->fetchrow_hashref){
371 if ($data->{'subscription'} == 1){
372 $spent+=$data->{'quantity'}*$data->{'unitprice'};
374 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
375 $comtd+=($data->{'ecost'})*$leftover;
376 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
381 return($spent,$comtd);
387 my $dbh = &C4Connect;
388 my $query = "Select max(biblionumber) from biblio";
389 my $sth = $dbh->prepare($query);
391 my $data = $sth->fetchrow_arrayref;
392 my $bibnum = $$data[0] + 1;
395 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
396 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
397 $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'});
398 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'});
399 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
400 $biblio->{'abstract'} = $dbh->quote($biblio->{'abstract'});
401 if ($biblio->{'seriestitle'}) { $series = 1 };
404 $query = "insert into biblio set
405 biblionumber = $bibnum,
406 title = $biblio->{'title'},
407 author = $biblio->{'author'},
408 copyrightdate = $biblio->{'copyright'},
410 seriestitle = $biblio->{'seriestitle'},
411 notes = $biblio->{'notes'},
412 abstract = $biblio->{'abstract'}";
414 $sth = $dbh->prepare($query);
423 my ($bibnum, $title, $author, $copyright, $seriestitle, $serial, $unititle, $notes)=@_;
425 my $query = "Update biblio set
428 copyrightdate = '$copyright',
429 seriestitle = '$seriestitle',
431 unititle = '$unititle',
433 where biblionumber = $bibnum";
434 my $sth = $dbh->prepare($query);
444 my ($bibnum,$subtitle)=@_;
446 my $query="update bibliosubtitle set subtitle='$subtitle' where biblionumber=$bibnum";
447 my $sth=$dbh->prepare($query);
454 my ($bibnum, $author) = @_;
456 my $query = "Delete from additionalauthors where biblionumber = $bibnum";
457 my $sth = $dbh->prepare($query);
463 $query = "Insert into additionalauthors set
465 biblionumber = '$bibnum'";
466 $sth = $dbh->prepare($query);
478 my ($bibnum,$force,@subject)=@_;
482 for (my $i=0;$i<$count;$i++){
483 $subject[$i]=~ s/^ //g;
484 $subject[$i]=~ s/ $//g;
485 my $query="select * from catalogueentry where entrytype='s' and
486 catalogueentry='$subject[$i]'";
487 my $sth=$dbh->prepare($query);
489 if (my $data=$sth->fetchrow_hashref){
492 if ($force eq $subject[$i]){
493 #subject not in aut, chosen to force anway
494 #so insert into cataloguentry so its in auth file
495 $query="Insert into catalogueentry (entrytype,catalogueentry)
496 values ('s','$subject[$i]')";
497 my $sth2=$dbh->prepare($query);
502 $error="$subject[$i]\n does not exist in the subject authority file";
503 $query= "Select * from catalogueentry where
504 entrytype='s' and (catalogueentry like '$subject[$i] %' or
505 catalogueentry like '% $subject[$i] %' or catalogueentry like
507 my $sth2=$dbh->prepare($query);
510 while (my $data=$sth2->fetchrow_hashref){
511 $error=$error."<br>$data->{'catalogueentry'}";
514 # $error=$error."<br>$query";
520 my $query="Delete from bibliosubject where biblionumber=$bibnum";
522 my $sth=$dbh->prepare($query);
526 for (my $i=0;$i<$count;$i++){
527 $sth=$dbh->prepare("Insert into bibliosubject values ('$subject[$i]',$bibnum)");
528 # print $subject[$i];
538 my ($bibitemnum,$itemtype,$isbn,$publishercode,$publicationdate,$classification,$dewey,$subclass,$illus,$pages,$volumeddesc,$notes,$size,$place)=@_;
540 my $query="update biblioitems set itemtype='$itemtype',
541 isbn='$isbn',publishercode='$publishercode',publicationyear='$publicationdate',
542 classification='$classification',dewey='$dewey',subclass='$subclass',illus='$illus',
543 pages='$pages',volumeddesc='$volumeddesc',notes='$notes',size='$size',place='$place'
545 biblioitemnumber=$bibitemnum";
546 my $sth=$dbh->prepare($query);
554 my ($bibitemnum,$note)=@_;
556 my $query="update biblioitems set notes='$note' where
557 biblioitemnumber='$bibitemnum'";
558 my $sth=$dbh->prepare($query);
565 my ($biblioitem) = @_;
567 my $query = "Select max(biblioitemnumber) from biblioitems";
568 my $sth = $dbh->prepare($query);
572 $biblioitem->{'volume'} = $dbh->quote($biblioitem->{'volume'});
573 $biblioitem->{'number'} = $dbh->quote($biblioitem->{'number'});
574 $biblioitem->{'classification'} = $dbh->quote($biblioitem->{'classification'});
575 $biblioitem->{'itemtype'} = $dbh->quote($biblioitem->{'itemtype'});
576 $biblioitem->{'url'} = $dbh->quote($biblioitem->{'url'});
577 $biblioitem->{'isbn'} = $dbh->quote($biblioitem->{'isbn'});
578 $biblioitem->{'issn'} = $dbh->quote($biblioitem->{'issn'});
579 $biblioitem->{'dewey'} = $dbh->quote($biblioitem->{'dewey'});
580 $biblioitem->{'subclass'} = $dbh->quote($biblioitem->{'subclass'});
581 $biblioitem->{'publicationyear'} = $dbh->quote($biblioitem->{'publicationyear'});
582 $biblioitem->{'publishercode'} = $dbh->quote($biblioitem->{'publishercode'});
583 $biblioitem->{'volumedate'} = $dbh->quote($biblioitem->{'volumedate'});
584 $biblioitem->{'volumeddesc'} = $dbh->quote($biblioitem->{'volumeddesc'}); $biblioitem->{'illus'} = $dbh->quote($biblioitem->{'illus'});
585 $biblioitem->{'pages'} = $dbh->quote($biblioitem->{'pages'});
586 $biblioitem->{'notes'} = $dbh->quote($biblioitem->{'notes'});
587 $biblioitem->{'size'} = $dbh->quote($biblioitem->{'size'});
588 $biblioitem->{'place'} = $dbh->quote($biblioitem->{'place'});
591 $data = $sth->fetchrow_arrayref;
592 $bibitemnum = $$data[0] + 1;
596 $query = "insert into biblioitems set
597 biblioitemnumber = $bibitemnum,
598 biblionumber = $biblioitem->{'biblionumber'},
599 volume = $biblioitem->{'volume'},
600 number = $biblioitem->{'number'},
601 classification = $biblioitem->{'classification'},
602 itemtype = $biblioitem->{'itemtype'},
603 url = $biblioitem->{'url'},
604 isbn = $biblioitem->{'isbn'},
605 issn = $biblioitem->{'issn'},
606 dewey = $biblioitem->{'dewey'},
607 subclass = $biblioitem->{'subclass'},
608 publicationyear = $biblioitem->{'publicationyear'},
609 publishercode = $biblioitem->{'publishercode'},
610 volumedate = $biblioitem->{'volumedate'},
611 volumeddesc = $biblioitem->{'volumeddesc'},
612 illus = $biblioitem->{'illus'},
613 pages = $biblioitem->{'pages'},
614 notes = $biblioitem->{'notes'},
615 size = $biblioitem->{'size'},
616 place = $biblioitem->{'place'}";
618 $sth = $dbh->prepare($query);
629 my $query="insert into bibliosubject (biblionumber) values
631 my $sth=$dbh->prepare($query);
639 my ($bibnum, $subtitle) = @_;
641 $subtitle = $dbh->quote($subtitle);
642 my $query = "insert into bibliosubtitle set
643 biblionumber = $bibnum,
644 subtitle = $subtitle";
645 my $sth = $dbh->prepare($query);
654 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
655 if ($budget eq 'now'){
658 $budget="'2001-07-01'";
666 my $query="insert into aqorders (biblionumber,title,basketno,
667 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
668 biblioitemnumber,rrp,ecost,gst,budgetdate,unitprice,subscription,booksellerinvoicenumber)
671 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
672 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst',$budget,'$cost',
674 my $sth=$dbh->prepare($query);
678 $query="select * from aqorders where
679 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
680 $sth=$dbh->prepare($query);
682 my $data=$sth->fetchrow_hashref;
684 $ordnum=$data->{'ordernumber'};
685 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
686 ($ordnum,'$bookfund')";
687 $sth=$dbh->prepare($query);
695 my ($bibnum,$ordnum)=@_;
697 my $query="update aqorders set datecancellationprinted=now()
698 where biblionumber='$bibnum' and
699 ordernumber='$ordnum'";
700 my $sth=$dbh->prepare($query);
704 my $count=itemcount($bibnum);
712 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
714 my $query="update aqorders set title='$title',
715 quantity='$quantity',listprice='$listprice',basketno='$basketno',
716 rrp='$rrp',ecost='$ecost',unitprice='$cost',
717 booksellerinvoicenumber='$invoice'
719 ordernumber=$ordnum and biblionumber=$bibnum";
720 my $sth=$dbh->prepare($query);
724 $query="update aqorderbreakdown set bookfundid=$bookfund where
725 ordernumber=$ordnum";
726 $sth=$dbh->prepare($query);
735 my $query="Select max(ordernumber) from aqorders";
736 my $sth=$dbh->prepare($query);
738 my $data=$sth->fetchrow_arrayref;
739 my $ordnum=$$data[0];
747 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
749 my $query="update aqorders set quantityreceived='$quantrec',
750 datereceived=now(),booksellerinvoicenumber='$invoiceno',
751 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
753 where biblionumber=$biblio and ordernumber=$ordnum
756 my $sth=$dbh->prepare($query);
759 $query="update aqorderbreakdown set bookfundid=$bookfund where
760 ordernumber=$ordnum";
761 $sth=$dbh->prepare($query);
768 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
770 my $query="update aqorders set
771 unitprice='$cost', rrp='$rrp'
772 where biblionumber=$biblio and ordernumber=$ordnum
775 my $sth=$dbh->prepare($query);
778 $query="update aqorderbreakdown set bookfundid=$bookfund where
779 ordernumber=$ordnum";
780 $sth=$dbh->prepare($query);
788 my ($currency,$price)=@_;
790 my $query="Select rate from currency where currency='$currency'";
791 my $sth=$dbh->prepare($query);
793 my $data=$sth->fetchrow_hashref;
796 my $cur=$data->{'rate'};
800 my $price=$price / $cur;
806 my $query="Select * from currency";
807 my $sth=$dbh->prepare($query);
811 while (my $data=$sth->fetchrow_hashref){
817 return($i,\@results);
823 my $query="Select * from currency where currency='$cur'";
824 my $sth=$dbh->prepare($query);
827 my $data=$sth->fetchrow_hashref;
833 sub updatecurrencies {
834 my ($currency,$rate)=@_;
836 my $query="update currency set rate=$rate where currency='$currency'";
837 my $sth=$dbh->prepare($query);
846 my $query="Update aqbooksellers set
847 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
848 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
849 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
850 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
851 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
852 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
853 '$data->{'contnotes'}', active=$data->{'active'},
854 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
855 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
856 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
857 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
858 nocalc='$data->{'nocalc'}'
859 where id='$data->{'id'}'";
860 my $sth=$dbh->prepare($query);
870 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
872 my $data2=$sth->fetchrow_hashref;
874 $data2->{'max(id)'}++;
875 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
878 $data->{'id'}=$data2->{'max(id)'};
881 return($data->{'id'});
886 my ($item, @barcodes) = @_;
888 my $query = "Select max(itemnumber) from items";
889 my $sth = $dbh->prepare($query);
895 $data = $sth->fetchrow_hashref;
896 $itemnumber = $data->{'max(itemnumber)'} + 1;
899 $item->{'booksellerid'} = $dbh->quote($item->{'bookselletid'});
900 $item->{'homebranch'} = $dbh->quote($item->{'homebranch'});
901 $item->{'price'} = $dbh->quote($item->{'price'});
902 $item->{'replacementprice'} = $dbh->quote($item->{'replacementprice'});
903 $item->{'itemnotes'} = $dbh->quote($item->{'itemnotes'});
905 foreach my $barcode (@barcodes) {
906 $barcode = uc($barcode);
907 $query = "Insert into items set
908 itemnumber = $itemnumber,
909 biblionumber = $item->{'biblionumber'},
910 biblioitemnumber = $item->{'biblioitemnumber'},
912 booksellerid = $item->{'booksellerid'},
913 dateaccessioned = NOW(),
914 homebranch = $item->{'homebranch'},
915 holdingbranch = $item->{'homebranch'},
916 price = $item->{'price'},
917 replacementprice = $item->{'replacementprice'},
918 replacementpricedate = NOW(),
919 itemnotes = $item->{'itemnotes'}";
921 if ($item->{'loan'}) {
923 notforloan = $item->{'loan'}";
926 $sth = $dbh->prepare($query);
929 $error .= $sth->errstr;
940 my ($count,@barcodes)=@_;
943 for (my $i=0;$i<$count;$i++){
944 $barcodes[$i]=uc $barcodes[$i];
945 my $query="Select * from items where barcode='$barcodes[$i]'";
946 my $sth=$dbh->prepare($query);
948 if (my $data=$sth->fetchrow_hashref){
949 $error.=" Duplicate Barcode: $barcodes[$i]";
958 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
960 my $query="update items set biblioitemnumber=$bibitemnum,
961 barcode='$barcode',itemnotes='$notes'
962 where itemnumber=$itemnum";
964 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
967 $query="update items set biblioitemnumber=$bibitemnum,
968 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
969 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
971 if ($replacement ne ''){
972 $query=~ s/ where/,replacementprice='$replacement' where/;
975 my $sth=$dbh->prepare($query);
982 my($price,$rrp,$itemnum)=@_;
984 my $query="update items set price='$price',replacementprice='$rrp'
985 where itemnumber=$itemnum";
986 my $sth=$dbh->prepare($query);
994 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
995 my $sth=$dbh->prepare($query);
997 my $data=$sth->fetchrow_hashref;
1000 return($data->{'count(*)'});
1004 my ($biblionumber)=@_;
1006 my $query="Select * from biblioitems,items,itemtypes where
1007 biblioitems.biblionumber=$biblionumber
1008 and biblioitems.biblioitemnumber=items.biblioitemnumber and
1009 itemtypes.itemtype=biblioitems.itemtype
1010 order by items.biblioitemnumber";
1011 my $sth=$dbh->prepare($query);
1015 while (my $data=$sth->fetchrow_hashref){
1025 my ($bibitemnum,$itemtype)=@_;
1027 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
1028 and itemtype='$itemtype'";
1029 my $sth=$dbh->prepare($query);
1032 if (my $data=$sth->fetchrow_hashref){
1043 my $query="select * from items where itemnumber=$itemnum";
1044 my $sth=$dbh->prepare($query);
1046 my @data=$sth->fetchrow_array;
1048 $query="Insert into deleteditems values (";
1049 foreach my $temp (@data){
1050 $query=$query."'$temp',";
1054 $sth=$dbh->prepare($query);
1057 $query = "Delete from items where itemnumber=$itemnum";
1058 $sth=$dbh->prepare($query);
1067 my $query="select * from biblioitems where biblioitemnumber=$itemnum";
1068 my $sth=$dbh->prepare($query);
1070 if (my @data=$sth->fetchrow_array){
1072 $query="Insert into deletedbiblioitems values (";
1073 foreach my $temp (@data){
1074 $temp=~ s/\'/\\\'/g;
1075 $query=$query."'$temp',";
1079 $sth=$dbh->prepare($query);
1082 $query = "Delete from biblioitems where biblioitemnumber=$itemnum";
1083 $sth=$dbh->prepare($query);
1094 my $query="select * from biblio where biblionumber=$biblio";
1095 my $sth=$dbh->prepare($query);
1097 if (my @data=$sth->fetchrow_array){
1099 $query="Insert into deletedbiblio values (";
1100 foreach my $temp (@data){
1101 $temp=~ s/\'/\\\'/g;
1102 $query=$query."'$temp',";
1106 $sth=$dbh->prepare($query);
1109 $query = "Delete from biblio where biblionumber=$biblio";
1110 $sth=$dbh->prepare($query);
1120 my $dbh = C4Connect;
1121 my $query = "select * from itemtypes";
1122 my $sth = $dbh->prepare($query);
1123 # || die "Cannot prepare $query" . $dbh->errstr;
1128 # || die "Cannot execute $query\n" . $sth->errstr;
1129 while (my $data = $sth->fetchrow_hashref) {
1130 @results[$count] = $data;
1136 return($count, @results);
1137 } # sub getitemtypes
1141 my ($biblionumber) = @_;
1142 my $dbh = C4Connect;
1143 my $query = "Select * from biblio where biblionumber = $biblionumber";
1144 my $sth = $dbh->prepare($query);
1145 # || die "Cannot prepare $query\n" . $dbh->errstr;
1150 # || die "Cannot execute $query\n" . $sth->errstr;
1151 while (my $data = $sth->fetchrow_hashref) {
1152 $results[$count] = $data;
1158 return($count, @results);
1163 my ($biblioitemnum) = @_;
1164 my $dbh = C4Connect;
1165 my $query = "Select * from biblioitems where
1166 biblioitemnumber = $biblioitemnum";
1167 my $sth = $dbh->prepare($query);
1173 while (my $data = $sth->fetchrow_hashref) {
1174 $results[$count] = $data;
1180 return($count, @results);
1181 } # sub getbiblioitem
1184 sub getitemsbybiblioitem {
1185 my ($biblioitemnum) = @_;
1186 my $dbh = C4Connect;
1187 my $query = "Select * from items, biblio where
1188 biblio.biblionumber = items.biblionumber and biblioitemnumber
1190 my $sth = $dbh->prepare($query);
1191 # || die "Cannot prepare $query\n" . $dbh->errstr;
1196 # || die "Cannot execute $query\n" . $sth->errstr;
1197 while (my $data = $sth->fetchrow_hashref) {
1198 $results[$count] = $data;
1204 return($count, @results);
1205 } # sub getitemsbybiblioitem
1210 my $dbh = C4Connect;
1216 $isbn = $dbh->quote($isbn);
1217 $query = "Select * from biblioitems where isbn = $isbn";
1218 $sth = $dbh->prepare($query);
1221 while (my $data = $sth->fetchrow_hashref) {
1222 $results[$count] = $data;
1228 return($count, @results);
1233 my ($keywordlist) = @_;
1234 my $dbh = C4Connect;
1235 my $query = "Select * from biblio where";
1239 my @keywords = split(/ +/, $keywordlist);
1240 my $keyword = shift(@keywords);
1242 $keyword =~ s/%/\\%/g;
1243 $keyword =~ s/_/\\_/;
1244 $keyword = "%" . $keyword . "%";
1245 $keyword = $dbh->quote($keyword);
1246 $query .= " (author like $keyword) or
1247 (title like $keyword) or (unititle like $keyword) or
1248 (notes like $keyword) or (seriestitle like $keyword) or
1249 (abstract like $keyword)";
1251 foreach $keyword (@keywords) {
1252 $keyword =~ s/%/\\%/;
1253 $keyword =~ s/_/\\_/;
1254 $keyword = "%" . $keyword . "%";
1255 $keyword = $dbh->quote($keyword);
1256 $query .= " or (author like $keyword) or
1257 (title like $keyword) or (unititle like $keyword) or
1258 (notes like $keyword) or (seriestitle like $keyword) or
1259 (abstract like $keyword)";
1262 $sth = $dbh->prepare($query);
1265 while (my $data = $sth->fetchrow_hashref) {
1266 $results[$count] = $data;
1272 return($count, @results);
1273 } # sub keywordsearch
1277 my ($keywordlist) = @_;
1278 my $dbh = C4Connect;
1279 my $query = "Select distinct biblio.* from biblio, biblioitems where
1280 biblio.biblionumber = biblioitems.biblionumber and (";
1284 my @keywords = split(/ +/, $keywordlist);
1285 my $keyword = shift(@keywords);
1287 $keyword =~ s/%/\\%/g;
1288 $keyword =~ s/_/\\_/;
1289 $keyword = "%" . $keyword . "%";
1290 $keyword = $dbh->quote($keyword);
1291 $query .= " (url like $keyword)";
1293 foreach $keyword (@keywords) {
1294 $keyword =~ s/%/\\%/;
1295 $keyword =~ s/_/\\_/;
1296 $keyword = "%" . $keyword . "%";
1297 $keyword = $dbh->quote($keyword);
1298 $query .= " or (url like $keyword)";
1302 $sth = $dbh->prepare($query);
1305 while (my $data = $sth->fetchrow_hashref) {
1306 $results[$count] = $data;
1312 return($count, @results);
1313 } # sub websitesearch
1316 END { } # module clean-up code here (global destructor)