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 &makeitems &modbibitem
17 &getcurrencies &modsubtitle &modsubject &modaddauthor &moditem &countitems
18 &findall &needsmod &delitem &delbibitem &delbiblio &delorder &branches
19 &getallorders &updatecurrencies &getorder);
20 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
22 # your exported package globals go here,
23 # as well as any optionally exported functions
25 @EXPORT_OK = qw($Var1 %Hashit);
28 # non-exported package globals go here
29 use vars qw(@more $stuff);
31 # initalize package globals, first exported ones
37 # then the others (which are still accessible as $Some::Module::stuff)
41 # all file-scoped lexicals must be created before
42 # the functions below that use them.
44 # file-private lexicals go here
48 # here's a file-private function as a closure,
49 # callable as &$priv_func; it cannot be prototyped.
54 # make all your functions, whether exported or not;
59 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
60 booksellerid='$supplierid' and (datereceived = '0000-00-00' or
61 datereceived is NULL) and (cancelledby is NULL or cancelledby = '')";
62 $query.=" group by basketno order by entrydate";
64 my $sth=$dbh->prepare($query);
68 while (my $data=$sth->fetchrow_hashref){
74 return ($i,\@results);
80 my $query="Select count(*) from items where biblionumber=$biblio";
81 my $sth=$dbh->prepare($query);
83 my $data=$sth->fetchrow_hashref;
86 return($data->{'count(*)'});
92 my $query="Select ordernumber from aqorders where biblionumber=$bib and
93 biblioitemnumber='$bi'";
94 my $sth=$dbh->prepare($query);
96 my $ordnum=$sth->fetchrow_hashref;
98 my $order=getsingleorder($ordnum->{'ordernumber'});
107 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
108 where aqorders.ordernumber=$ordnum
109 and biblio.biblionumber=aqorders.biblionumber and
110 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
111 aqorders.ordernumber=aqorderbreakdown.ordernumber";
112 my $sth=$dbh->prepare($query);
114 my $data=$sth->fetchrow_hashref;
123 my $query="Select * from aqorders,biblio,biblioitems where
124 booksellerinvoicenumber='$invoice'
125 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
126 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
129 my $sth=$dbh->prepare($query);
131 while (my $data=$sth->fetchrow_hashref){
141 #gets all orders from a certain supplier, orders them alphabetically
144 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
145 and (cancelledby is NULL or cancelledby = '')
146 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
147 aqorders.biblioitemnumber
148 group by aqorders.biblioitemnumber
153 my $sth=$dbh->prepare($query);
155 while (my $data=$sth->fetchrow_hashref){
165 my ($search,$biblio,$catview)=@_;
167 my $query="Select *,biblio.title from aqorders,biblioitems,biblio
168 where aqorders.biblioitemnumber=
169 biblioitems.biblioitemnumber and biblio.biblionumber=aqorders.biblionumber
170 and (datecancellationprinted is NULL or datecancellationprinted =
173 my @data=split(' ',$search);
175 for (my $i=0;$i<$count;$i++){
176 $query.= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
179 $query.=" ) or biblioitems.isbn='$search'
180 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
181 if ($catview ne 'yes'){
182 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
184 $query.=" group by aqorders.ordernumber";
185 my $sth=$dbh->prepare($query);
190 while (my $data=$sth->fetchrow_hashref){
191 my $sth2=$dbh->prepare("Select * from biblio where
192 biblionumber='$data->{'biblionumber'}'");
194 my $data2=$sth2->fetchrow_hashref;
196 $data->{'author'}=$data2->{'author'};
197 $data->{'seriestitle'}=$data2->{'seriestitle'};
198 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
199 ordernumber=$data->{'ordernumber'}");
201 $data2=$sth2->fetchrow_hashref;
203 $data->{'branchcode'}=$data2->{'branchcode'};
204 $data->{'bookfundid'}=$data2->{'bookfundid'};
215 my ($searchstring)=@_;
217 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
218 id = '$searchstring'";
219 my $sth=$dbh->prepare($query);
223 while (my $data=$sth->fetchrow_hashref){
235 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
236 my $sth=$dbh->prepare($query);
240 while (my $data=$sth->fetchrow_hashref){
246 return($i,\@results);
252 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
253 where basketno='$basketno'
254 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
255 =aqorders.biblioitemnumber
256 and (datecancellationprinted is NULL or datecancellationprinted =
258 group by aqorders.ordernumber";
259 my $sth=$dbh->prepare($query);
264 while (my $data=$sth->fetchrow_hashref){
275 my $query="Select max(basketno) from aqorders";
276 my $sth=$dbh->prepare($query);
278 my $data=$sth->fetchrow_arrayref;
279 my $basket=$$data[0];
288 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
289 =aqbudget.bookfundid group by aqbookfund.bookfundid order by bookfundname";
290 my $sth=$dbh->prepare($query);
294 while (my $data=$sth->fetchrow_hashref){
305 my $query="Select * from branches";
306 my $sth=$dbh->prepare($query);
310 while (my $data=$sth->fetchrow_hashref){
319 sub bookfundbreakdown {
322 my $query="Select quantity,datereceived,freight,unitprice,listprice
323 from aqorders,aqorderbreakdown where bookfundid='$id' and
324 aqorders.ordernumber=aqorderbreakdown.ordernumber and entrydate >=
326 my $sth=$dbh->prepare($query);
330 while (my $data=$sth->fetchrow_hashref){
331 if ($data->{'datereceived'} =~ /0000/){
332 $comtd+=($data->{'listprice'}+$data->{'freight'})*$data->{'quantity'};
334 $spent+=($data->{'unitprice'}+$data->{'freight'})*$data->{'quantity'};
339 return($spent,$comtd);
344 my ($title,$author,$copyright)=@_;
346 my $query="Select max(biblionumber) from biblio";
347 my $sth=$dbh->prepare($query);
349 my $data=$sth->fetchrow_arrayref;
350 my $bibnum=$$data[0];
353 $query="insert into biblio (biblionumber,title,author,copyrightdate) values
354 ($bibnum,'$title','$author','$copyright')";
355 $sth=$dbh->prepare($query);
364 my ($bibnum,$title,$author,$copyright,$seriestitle,$serial,$unititle,$notes)=@_;
366 # $title=~ s/\'/\\\'/g;
367 # $author=~ s/\'/\\\'/g;
368 my $query="update biblio set title='$title',
369 author='$author',copyrightdate='$copyright',
370 seriestitle='$seriestitle',serial='$serial',unititle='$unititle',notes='$notes'
372 biblionumber=$bibnum";
373 my $sth=$dbh->prepare($query);
381 my ($bibnum,$subtitle)=@_;
383 my $query="update bibliosubtitle set subtitle='$subtitle' where biblionumber=$bibnum";
384 my $sth=$dbh->prepare($query);
391 my ($bibnum,$author)=@_;
393 my $query="Select * from additionalauthors where biblionumber=$bibnum";
394 my $sth=$dbh->prepare($query);
396 if (my $data=$sth->fetchrow_hashref){
397 $query="update additionalauthors set author='$author' where biblionumber=$bibnum";
399 $query="insert into additionalauthors (author,biblionumber) values ('$author','$bibnum')";
402 $sth=$dbh->prepare($query);
409 my ($bibnum,$force,@subject)=@_;
413 for (my $i=0;$i<$count;$i++){
414 $subject[$i]=~ s/^ //g;
415 $subject[$i]=~ s/ $//g;
416 my $query="select * from catalogueentry where entrytype='s' and
417 catalogueentry='$subject[$i]'";
418 my $sth=$dbh->prepare($query);
420 if (my $data=$sth->fetchrow_hashref){
423 if ($force eq $subject[$i]){
424 #subject not in aut, chosen to force anway
425 #so insert into cataloguentry so its in auth file
426 $query="Insert into catalogueentry (entrytype,catalogueentry)
427 values ('s','$subject[$i]')";
428 my $sth2=$dbh->prepare($query);
433 $error="$subject[$i]\n does not exist in the subject authority file";
434 $query= "Select * from catalogueentry where
435 entrytype='s' and (catalogueentry like '$subject[$i] %' or
436 catalogueentry like '% $subject[$i] %' or catalogueentry like
438 my $sth2=$dbh->prepare($query);
441 while (my $data=$sth2->fetchrow_hashref){
442 $error=$error."<br>$data->{'catalogueentry'}";
445 # $error=$error."<br>$query";
451 my $query="Delete from bibliosubject where biblionumber=$bibnum";
453 my $sth=$dbh->prepare($query);
457 for (my $i=0;$i<$count;$i++){
458 $sth=$dbh->prepare("Insert into bibliosubject values ('$subject[$i]',$bibnum)");
459 # print $subject[$i];
469 my ($bibitemnum,$itemtype,$isbn,$publishercode,$publicationdate,$classification,$dewey,$subclass,$illus,$pages,$volumeddesc,$notes,$size,$place)=@_;
471 my $query="update biblioitems set itemtype='$itemtype',
472 isbn='$isbn',publishercode='$publishercode',publicationyear='$publicationdate',
473 classification='$classification',dewey='$dewey',subclass='$subclass',illus='$illus',
474 pages='$pages',volumeddesc='$volumeddesc',notes='$notes',size='$size',place='$place'
476 biblioitemnumber=$bibitemnum";
477 my $sth=$dbh->prepare($query);
485 my ($bibnum,$itemtype,$isbn,$volinf,$class)=@_;
487 my $query="Select max(biblioitemnumber) from biblioitems";
488 my $sth=$dbh->prepare($query);
490 my $data=$sth->fetchrow_arrayref;
491 my $bibitemnum=$$data[0];
494 $query="insert into biblioitems (biblionumber,biblioitemnumber,
495 itemtype,isbn,volumeddesc,classification)
497 ($bibnum,$bibitemnum,'$itemtype','$isbn','$volinf','$class')";
498 $sth=$dbh->prepare($query);
509 my $query="insert into bibliosubject (biblionumber) values
511 my $sth=$dbh->prepare($query);
521 my $query="insert into bibliosubtitle (biblionumber) values
523 my $sth=$dbh->prepare($query);
531 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,
532 $notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst)=@_;
534 my $query="insert into aqorders (biblionumber,title,basketno,
535 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
536 biblioitemnumber,rrp,ecost,gst)
538 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
539 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst')";
540 my $sth=$dbh->prepare($query);
544 $query="select * from aqorders where
545 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
546 $sth=$dbh->prepare($query);
548 my $data=$sth->fetchrow_hashref;
550 $ordnum=$data->{'ordernumber'};
551 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
552 ($ordnum,'$bookfund')";
553 $sth=$dbh->prepare($query);
561 my ($bibnum,$ordnum)=@_;
563 my $query="update aqorders set datecancellationprinted=now()
564 where biblionumber='$bibnum' and
565 ordernumber='$ordnum'";
566 my $sth=$dbh->prepare($query);
570 my $count=itemcount($bibnum);
578 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst)=@_;
580 my $query="update aqorders set title='$title',
581 quantity='$quantity',listprice='$listprice',basketno='$basketno',
582 rrp='$rrp',ecost='$ecost'
584 ordernumber=$ordnum and biblionumber=$bibnum";
585 my $sth=$dbh->prepare($query);
589 $query="update aqorderbreakdown set bookfundid=$bookfund where
590 ordernumber=$ordnum";
591 $sth=$dbh->prepare($query);
600 my $query="Select max(ordernumber) from aqorders";
601 my $sth=$dbh->prepare($query);
603 my $data=$sth->fetchrow_arrayref;
604 my $ordnum=$$data[0];
612 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund)=@_;
614 my $query="update aqorders set quantityreceived='$quantrec',
615 datereceived=now(),booksellerinvoicenumber='$invoiceno',
616 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight'
617 where biblionumber=$biblio and ordernumber=$ordnum
620 my $sth=$dbh->prepare($query);
623 $query="update aqorderbreakdown set bookfundid=$bookfund where
624 ordernumber=$ordnum";
625 $sth=$dbh->prepare($query);
633 my ($currency,$price)=@_;
635 my $query="Select rate from currency where currency='$currency'";
636 my $sth=$dbh->prepare($query);
638 my $data=$sth->fetchrow_hashref;
641 my $cur=$data->{'rate'};
645 my $price=$price / $cur;
651 my $query="Select * from currency";
652 my $sth=$dbh->prepare($query);
656 while (my $data=$sth->fetchrow_hashref){
662 return($i,\@results);
665 sub updatecurrencies {
666 my ($currency,$rate)=@_;
668 my $query="update currency set rate=$rate where currency='$currency'";
669 my $sth=$dbh->prepare($query);
678 my $query="Update aqbooksellers set
679 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
680 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
681 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
682 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
683 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
684 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
685 '$data->{'contnotes'}', active=$data->{'active'},
686 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
687 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
688 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
689 discount='$data->{'discount'}'
690 where id='$data->{'id'}'";
691 my $sth=$dbh->prepare($query);
701 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
703 my $data2=$sth->fetchrow_hashref;
705 $data2->{'max(id)'}++;
706 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
709 $data->{'id'}=$data2->{'max(id)'};
712 return($data->{'id'});
717 ($count,$bibitemno,$biblio,$replacement,$price,$booksellerid,$branch,$loan,@barcodes)=@_;
719 my $sth=$dbh->prepare("Select max(itemnumber) from items");
721 my $data=$sth->fetchrow_hashref;
722 my $item=$data->{'max(itemnumber)'};
726 for (my $i=0;$i<$count;$i++){
727 $barcodes[$i]=uc $barcodes[$i];
728 my $query="Insert into items (biblionumber,biblioitemnumber,itemnumber,barcode,
729 booksellerid,dateaccessioned,homebranch,holdingbranch,price,replacementprice,
730 replacementpricedate,notforloan) values
731 ($biblio,$bibitemno,$item,'$barcodes[$i]','$booksellerid',now(),'$branch',
732 '$branch','$price','$replacement',now(),$loan)";
733 my $sth=$dbh->prepare($query);
735 $error.=$sth->errstr;
745 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn)=@_;
747 my $query="update items set biblioitemnumber=$bibitemnum,
748 barcode='$barcode',itemnotes='$notes'
749 where itemnumber=$itemnum";
751 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
754 $query="update items set biblioitemnumber=$bibitemnum,
755 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
756 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
759 my $sth=$dbh->prepare($query);
768 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
769 my $sth=$dbh->prepare($query);
771 my $data=$sth->fetchrow_hashref;
774 return($data->{'count(*)'});
778 my ($biblionumber)=@_;
780 my $query="Select * from biblioitems,items,itemtypes where
781 biblioitems.biblionumber=$biblionumber
782 and biblioitems.biblioitemnumber=items.biblioitemnumber and
783 itemtypes.itemtype=biblioitems.itemtype
784 order by items.biblioitemnumber";
785 my $sth=$dbh->prepare($query);
789 while (my $data=$sth->fetchrow_hashref){
799 my ($bibitemnum,$itemtype)=@_;
801 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
802 and itemtype='$itemtype'";
803 my $sth=$dbh->prepare($query);
806 if (my $data=$sth->fetchrow_hashref){
817 my $query="select * from items where itemnumber=$itemnum";
818 my $sth=$dbh->prepare($query);
820 my @data=$sth->fetchrow_array;
822 $query="Insert into deleteditems values (";
823 foreach my $temp (@data){
824 $query=$query."'$temp',";
828 $sth=$dbh->prepare($query);
831 $query = "Delete from items where itemnumber=$itemnum";
832 $sth=$dbh->prepare($query);
841 my $query="select * from biblioitems where biblioitemnumber=$itemnum";
842 my $sth=$dbh->prepare($query);
844 if (my @data=$sth->fetchrow_array){
846 $query="Insert into deletedbiblioitems values (";
847 foreach my $temp (@data){
849 $query=$query."'$temp',";
853 $sth=$dbh->prepare($query);
856 $query = "Delete from biblioitems where biblioitemnumber=$itemnum";
857 $sth=$dbh->prepare($query);
868 my $query="select * from biblio where biblionumber=$biblio";
869 my $sth=$dbh->prepare($query);
871 if (my @data=$sth->fetchrow_array){
873 $query="Insert into deletedbiblio values (";
874 foreach my $temp (@data){
876 $query=$query."'$temp',";
880 $sth=$dbh->prepare($query);
883 $query = "Delete from biblio where biblionumber=$biblio";
884 $sth=$dbh->prepare($query);
892 END { } # module clean-up code here (global destructor)