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 &getrecorders &updatecurrencies &getorder &getcurrency &updaterecorder
20 &updatecost &checkitems &modnote);
21 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
23 # your exported package globals go here,
24 # as well as any optionally exported functions
26 @EXPORT_OK = qw($Var1 %Hashit);
29 # non-exported package globals go here
30 use vars qw(@more $stuff);
32 # 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;
61 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
62 booksellerid='$supplierid' and (quantity > quantityreceived or
63 quantityreceived is NULL)
64 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
65 $query.=" group by basketno order by entrydate desc";
67 my $sth=$dbh->prepare($query);
71 while (my $data=$sth->fetchrow_hashref){
77 return ($i,\@results);
83 my $query="Select count(*) from items where biblionumber=$biblio";
85 my $sth=$dbh->prepare($query);
87 my $data=$sth->fetchrow_hashref;
90 return($data->{'count(*)'});
96 my $query="Select ordernumber from aqorders where biblionumber=$bib and
97 biblioitemnumber='$bi'";
98 my $sth=$dbh->prepare($query);
100 my $ordnum=$sth->fetchrow_hashref;
102 my $order=getsingleorder($ordnum->{'ordernumber'});
105 return ($order,$ordnum->{'ordernumber'});
111 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
112 where aqorders.ordernumber='$ordnum'
113 and biblio.biblionumber=aqorders.biblionumber and
114 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
115 aqorders.ordernumber=aqorderbreakdown.ordernumber";
116 my $sth=$dbh->prepare($query);
118 my $data=$sth->fetchrow_hashref;
127 my $query="Select * from aqorders,biblio,biblioitems where
128 booksellerinvoicenumber='$invoice'
129 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
130 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
133 my $sth=$dbh->prepare($query);
135 while (my $data=$sth->fetchrow_hashref){
145 #gets all orders from a certain supplier, orders them alphabetically
148 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
149 and (cancelledby is NULL or cancelledby = '')
150 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
151 aqorders.biblioitemnumber
152 group by aqorders.biblioitemnumber
157 my $sth=$dbh->prepare($query);
159 while (my $data=$sth->fetchrow_hashref){
169 #gets all orders from a certain supplier, orders them alphabetically
172 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
173 and (cancelledby is NULL or cancelledby = '')
174 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
175 aqorders.biblioitemnumber and
176 aqorders.quantityreceived>0
177 and aqorders.datereceived >=now()
178 group by aqorders.biblioitemnumber
183 my $sth=$dbh->prepare($query);
185 while (my $data=$sth->fetchrow_hashref){
195 my ($search,$biblio,$catview)=@_;
197 my $query="Select *,biblio.title from aqorders,biblioitems,biblio
198 where aqorders.biblioitemnumber=
199 biblioitems.biblioitemnumber and biblio.biblionumber=aqorders.biblionumber
200 and (datecancellationprinted is NULL or datecancellationprinted =
203 my @data=split(' ',$search);
205 for (my $i=0;$i<$count;$i++){
206 $query.= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
209 $query.=" ) or biblioitems.isbn='$search'
210 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
211 if ($catview ne 'yes'){
212 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
214 $query.=" group by aqorders.ordernumber";
215 my $sth=$dbh->prepare($query);
220 while (my $data=$sth->fetchrow_hashref){
221 my $sth2=$dbh->prepare("Select * from biblio where
222 biblionumber='$data->{'biblionumber'}'");
224 my $data2=$sth2->fetchrow_hashref;
226 $data->{'author'}=$data2->{'author'};
227 $data->{'seriestitle'}=$data2->{'seriestitle'};
228 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
229 ordernumber=$data->{'ordernumber'}");
231 $data2=$sth2->fetchrow_hashref;
233 $data->{'branchcode'}=$data2->{'branchcode'};
234 $data->{'bookfundid'}=$data2->{'bookfundid'};
245 my ($searchstring)=@_;
247 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
248 id = '$searchstring'";
249 my $sth=$dbh->prepare($query);
253 while (my $data=$sth->fetchrow_hashref){
265 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
266 my $sth=$dbh->prepare($query);
270 while (my $data=$sth->fetchrow_hashref){
276 return($i,\@results);
280 my ($basketno,$supplier)=@_;
282 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
283 where basketno='$basketno'
284 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
285 =aqorders.biblioitemnumber
286 and (datecancellationprinted is NULL or datecancellationprinted =
288 if ($supplier ne ''){
289 $query.=" and aqorders.booksellerid='$supplier'";
291 $query.=" group by aqorders.ordernumber";
292 my $sth=$dbh->prepare($query);
297 while (my $data=$sth->fetchrow_hashref){
308 my $query="Select max(basketno) from aqorders";
309 my $sth=$dbh->prepare($query);
311 my $data=$sth->fetchrow_arrayref;
312 my $basket=$$data[0];
321 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
323 and aqbudget.startdate='2001=07-01'
324 group by aqbookfund.bookfundid order by bookfundname";
325 my $sth=$dbh->prepare($query);
329 while (my $data=$sth->fetchrow_hashref){
340 my $query="Select * from branches";
341 my $sth=$dbh->prepare($query);
345 while (my $data=$sth->fetchrow_hashref){
354 sub bookfundbreakdown {
357 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
358 from aqorders,aqorderbreakdown where bookfundid='$id' and
359 aqorders.ordernumber=aqorderbreakdown.ordernumber and ((budgetdate >=
360 '2001-07-01' and budgetdate <'2002-07-01') or
361 (datereceived >= '2001-07-01' and datereceived < '2002-07-01'))
362 and (datecancellationprinted is NULL or
363 datecancellationprinted='0000-00-00')";
364 my $sth=$dbh->prepare($query);
368 while (my $data=$sth->fetchrow_hashref){
369 if ($data->{'subscription'} == 1){
370 $spent+=$data->{'quantity'}*$data->{'unitprice'};
372 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
373 $comtd+=($data->{'ecost'})*$leftover;
374 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
379 return($spent,$comtd);
385 my $dbh = &C4Connect;
386 my $query = "Select max(biblionumber) from biblio";
387 my $sth = $dbh->prepare($query);
389 my $data = $sth->fetchrow_arrayref;
390 my $bibnum = $$data[0] + 1;
393 $biblio->{'title'} = $dbh->quote($biblio->{'title'});
394 $biblio->{'author'} = $dbh->quote($biblio->{'author'});
395 $biblio->{'copyright'} = $dbh->quote($biblio->{'copyright'});
396 $biblio->{'seriestitle'} = $dbh->quote($biblio->{'seriestitle'});
397 $biblio->{'notes'} = $dbh->quote($biblio->{'notes'});
398 if ($biblio->{'seriestitle'}) { $series = 1 };
401 $query = "insert into biblio set
402 biblionumber = $bibnum,
403 title = $biblio->{'title'},
404 author = $biblio->{'author'},
405 copyrightdate = $biblio->{'copyright'},
407 seriestitle = $biblio->{'seriestitle'},
408 notes = $biblio->{'notes'}";
410 $sth = $dbh->prepare($query);
420 my ($bibnum,$title,$author,$copyright,$seriestitle,$serial,$unititle,$notes)=@_;
422 #$title=~ s/\'/\\\'/g;
423 #$author=~ s/\'/\\\'/g;
424 my $query="update biblio set title='$title',
425 author='$author',copyrightdate='$copyright',
426 seriestitle='$seriestitle',serial='$serial',unititle='$unititle',notes='$notes'
428 biblionumber=$bibnum";
429 my $sth=$dbh->prepare($query);
437 my ($bibnum,$subtitle)=@_;
439 my $query="update bibliosubtitle set subtitle='$subtitle' where biblionumber=$bibnum";
440 my $sth=$dbh->prepare($query);
447 my ($bibnum,$author)=@_;
449 my $query="Delete from additionalauthors where biblionumber=$bibnum";
450 my $sth=$dbh->prepare($query);
452 $query="insert into additionalauthors (author,biblionumber) values ('$author','$bibnum')";
454 $sth=$dbh->prepare($query);
461 my ($bibnum,$force,@subject)=@_;
465 for (my $i=0;$i<$count;$i++){
466 $subject[$i]=~ s/^ //g;
467 $subject[$i]=~ s/ $//g;
468 my $query="select * from catalogueentry where entrytype='s' and
469 catalogueentry='$subject[$i]'";
470 my $sth=$dbh->prepare($query);
472 if (my $data=$sth->fetchrow_hashref){
475 if ($force eq $subject[$i]){
476 #subject not in aut, chosen to force anway
477 #so insert into cataloguentry so its in auth file
478 $query="Insert into catalogueentry (entrytype,catalogueentry)
479 values ('s','$subject[$i]')";
480 my $sth2=$dbh->prepare($query);
485 $error="$subject[$i]\n does not exist in the subject authority file";
486 $query= "Select * from catalogueentry where
487 entrytype='s' and (catalogueentry like '$subject[$i] %' or
488 catalogueentry like '% $subject[$i] %' or catalogueentry like
490 my $sth2=$dbh->prepare($query);
493 while (my $data=$sth2->fetchrow_hashref){
494 $error=$error."<br>$data->{'catalogueentry'}";
497 # $error=$error."<br>$query";
503 my $query="Delete from bibliosubject where biblionumber=$bibnum";
505 my $sth=$dbh->prepare($query);
509 for (my $i=0;$i<$count;$i++){
510 $sth=$dbh->prepare("Insert into bibliosubject values ('$subject[$i]',$bibnum)");
511 # print $subject[$i];
521 my ($bibitemnum,$itemtype,$isbn,$publishercode,$publicationdate,$classification,$dewey,$subclass,$illus,$pages,$volumeddesc,$notes,$size,$place)=@_;
523 my $query="update biblioitems set itemtype='$itemtype',
524 isbn='$isbn',publishercode='$publishercode',publicationyear='$publicationdate',
525 classification='$classification',dewey='$dewey',subclass='$subclass',illus='$illus',
526 pages='$pages',volumeddesc='$volumeddesc',notes='$notes',size='$size',place='$place'
528 biblioitemnumber=$bibitemnum";
529 my $sth=$dbh->prepare($query);
537 my ($bibitemnum,$note)=@_;
539 my $query="update biblioitems set notes='$note' where
540 biblioitemnumber='$bibitemnum'";
541 my $sth=$dbh->prepare($query);
548 my ($bibnum,$itemtype,$isbn,$volinf,$class)=@_;
550 my $query="Select max(biblioitemnumber) from biblioitems";
551 my $sth=$dbh->prepare($query);
553 my $data=$sth->fetchrow_arrayref;
554 my $bibitemnum=$$data[0];
557 $query="insert into biblioitems (biblionumber,biblioitemnumber,
558 itemtype,isbn,volumeddesc,classification)
560 ($bibnum,$bibitemnum,'$itemtype','$isbn','$volinf','$class')";
561 $sth=$dbh->prepare($query);
572 my $query="insert into bibliosubject (biblionumber) values
574 my $sth=$dbh->prepare($query);
584 my $query="insert into bibliosubtitle (biblionumber) values
586 my $sth=$dbh->prepare($query);
594 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
595 if ($budget eq 'now'){
598 $budget="'2001-07-01'";
606 my $query="insert into aqorders (biblionumber,title,basketno,
607 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
608 biblioitemnumber,rrp,ecost,gst,budgetdate,unitprice,subscription,booksellerinvoicenumber)
611 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
612 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst',$budget,'$cost',
614 my $sth=$dbh->prepare($query);
618 $query="select * from aqorders where
619 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
620 $sth=$dbh->prepare($query);
622 my $data=$sth->fetchrow_hashref;
624 $ordnum=$data->{'ordernumber'};
625 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
626 ($ordnum,'$bookfund')";
627 $sth=$dbh->prepare($query);
635 my ($bibnum,$ordnum)=@_;
637 my $query="update aqorders set datecancellationprinted=now()
638 where biblionumber='$bibnum' and
639 ordernumber='$ordnum'";
640 my $sth=$dbh->prepare($query);
644 my $count=itemcount($bibnum);
652 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
654 my $query="update aqorders set title='$title',
655 quantity='$quantity',listprice='$listprice',basketno='$basketno',
656 rrp='$rrp',ecost='$ecost',unitprice='$cost',
657 booksellerinvoicenumber='$invoice'
659 ordernumber=$ordnum and biblionumber=$bibnum";
660 my $sth=$dbh->prepare($query);
664 $query="update aqorderbreakdown set bookfundid=$bookfund where
665 ordernumber=$ordnum";
666 $sth=$dbh->prepare($query);
675 my $query="Select max(ordernumber) from aqorders";
676 my $sth=$dbh->prepare($query);
678 my $data=$sth->fetchrow_arrayref;
679 my $ordnum=$$data[0];
687 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
689 my $query="update aqorders set quantityreceived='$quantrec',
690 datereceived=now(),booksellerinvoicenumber='$invoiceno',
691 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
693 where biblionumber=$biblio and ordernumber=$ordnum
696 my $sth=$dbh->prepare($query);
699 $query="update aqorderbreakdown set bookfundid=$bookfund where
700 ordernumber=$ordnum";
701 $sth=$dbh->prepare($query);
708 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
710 my $query="update aqorders set
711 unitprice='$cost', rrp='$rrp'
712 where biblionumber=$biblio and ordernumber=$ordnum
715 my $sth=$dbh->prepare($query);
718 $query="update aqorderbreakdown set bookfundid=$bookfund where
719 ordernumber=$ordnum";
720 $sth=$dbh->prepare($query);
728 my ($currency,$price)=@_;
730 my $query="Select rate from currency where currency='$currency'";
731 my $sth=$dbh->prepare($query);
733 my $data=$sth->fetchrow_hashref;
736 my $cur=$data->{'rate'};
740 my $price=$price / $cur;
746 my $query="Select * from currency";
747 my $sth=$dbh->prepare($query);
751 while (my $data=$sth->fetchrow_hashref){
757 return($i,\@results);
763 my $query="Select * from currency where currency='$cur'";
764 my $sth=$dbh->prepare($query);
767 my $data=$sth->fetchrow_hashref;
773 sub updatecurrencies {
774 my ($currency,$rate)=@_;
776 my $query="update currency set rate=$rate where currency='$currency'";
777 my $sth=$dbh->prepare($query);
786 my $query="Update aqbooksellers set
787 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
788 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
789 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
790 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
791 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
792 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
793 '$data->{'contnotes'}', active=$data->{'active'},
794 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
795 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
796 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
797 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
798 nocalc='$data->{'nocalc'}'
799 where id='$data->{'id'}'";
800 my $sth=$dbh->prepare($query);
810 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
812 my $data2=$sth->fetchrow_hashref;
814 $data2->{'max(id)'}++;
815 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
818 $data->{'id'}=$data2->{'max(id)'};
821 return($data->{'id'});
826 ($count,$bibitemno,$biblio,$replacement,$price,$booksellerid,$branch,$loan,@barcodes)=@_;
828 my $sth=$dbh->prepare("Select max(itemnumber) from items");
830 my $data=$sth->fetchrow_hashref;
831 my $item=$data->{'max(itemnumber)'};
835 for (my $i=0;$i<$count;$i++){
836 $barcodes[$i]=uc $barcodes[$i];
837 my $query="Insert into items (biblionumber,biblioitemnumber,itemnumber,barcode,
838 booksellerid,dateaccessioned,homebranch,holdingbranch,price,replacementprice,
839 replacementpricedate,notforloan) values
840 ($biblio,$bibitemno,$item,'$barcodes[$i]','$booksellerid',now(),'$branch',
841 '$branch','$price','$replacement',now(),$loan)";
842 my $sth=$dbh->prepare($query);
844 $error.=$sth->errstr;
854 my ($count,@barcodes)=@_;
857 for (my $i=0;$i<$count;$i++){
858 $barcodes[$i]=uc $barcodes[$i];
859 my $query="Select * from items where barcode='$barcodes[$i]'";
860 my $sth=$dbh->prepare($query);
862 if (my $data=$sth->fetchrow_hashref){
863 $error.=" Duplicate Barcode: $barcodes[$i]";
872 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
874 my $query="update items set biblioitemnumber=$bibitemnum,
875 barcode='$barcode',itemnotes='$notes'
876 where itemnumber=$itemnum";
878 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
881 $query="update items set biblioitemnumber=$bibitemnum,
882 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
883 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
885 if ($replacement ne ''){
886 $query=~ s/ where/,replacementprice='$replacement' where/;
889 my $sth=$dbh->prepare($query);
896 my($price,$rrp,$itemnum)=@_;
898 my $query="update items set price='$price',replacementprice='$rrp'
899 where itemnumber=$itemnum";
900 my $sth=$dbh->prepare($query);
908 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
909 my $sth=$dbh->prepare($query);
911 my $data=$sth->fetchrow_hashref;
914 return($data->{'count(*)'});
918 my ($biblionumber)=@_;
920 my $query="Select * from biblioitems,items,itemtypes where
921 biblioitems.biblionumber=$biblionumber
922 and biblioitems.biblioitemnumber=items.biblioitemnumber and
923 itemtypes.itemtype=biblioitems.itemtype
924 order by items.biblioitemnumber";
925 my $sth=$dbh->prepare($query);
929 while (my $data=$sth->fetchrow_hashref){
939 my ($bibitemnum,$itemtype)=@_;
941 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
942 and itemtype='$itemtype'";
943 my $sth=$dbh->prepare($query);
946 if (my $data=$sth->fetchrow_hashref){
957 my $query="select * from items where itemnumber=$itemnum";
958 my $sth=$dbh->prepare($query);
960 my @data=$sth->fetchrow_array;
962 $query="Insert into deleteditems values (";
963 foreach my $temp (@data){
964 $query=$query."'$temp',";
968 $sth=$dbh->prepare($query);
971 $query = "Delete from items where itemnumber=$itemnum";
972 $sth=$dbh->prepare($query);
981 my $query="select * from biblioitems where biblioitemnumber=$itemnum";
982 my $sth=$dbh->prepare($query);
984 if (my @data=$sth->fetchrow_array){
986 $query="Insert into deletedbiblioitems values (";
987 foreach my $temp (@data){
989 $query=$query."'$temp',";
993 $sth=$dbh->prepare($query);
996 $query = "Delete from biblioitems where biblioitemnumber=$itemnum";
997 $sth=$dbh->prepare($query);
1008 my $query="select * from biblio where biblionumber=$biblio";
1009 my $sth=$dbh->prepare($query);
1011 if (my @data=$sth->fetchrow_array){
1013 $query="Insert into deletedbiblio values (";
1014 foreach my $temp (@data){
1015 $temp=~ s/\'/\\\'/g;
1016 $query=$query."'$temp',";
1020 $sth=$dbh->prepare($query);
1023 $query = "Delete from biblio where biblionumber=$biblio";
1024 $sth=$dbh->prepare($query);
1032 END { } # module clean-up code here (global destructor)