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);
384 my ($title,$author,$copyright)=@_;
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];
393 $query="insert into biblio (biblionumber,title,author,copyrightdate) values
394 ($bibnum,'$title','$author','$copyright')";
395 $sth=$dbh->prepare($query);
404 my ($bibnum,$title,$author,$copyright,$seriestitle,$serial,$unititle,$notes)=@_;
406 #$title=~ s/\'/\\\'/g;
407 #$author=~ s/\'/\\\'/g;
408 my $query="update biblio set title='$title',
409 author='$author',copyrightdate='$copyright',
410 seriestitle='$seriestitle',serial='$serial',unititle='$unititle',notes='$notes'
412 biblionumber=$bibnum";
413 my $sth=$dbh->prepare($query);
421 my ($bibnum,$subtitle)=@_;
423 my $query="update bibliosubtitle set subtitle='$subtitle' where biblionumber=$bibnum";
424 my $sth=$dbh->prepare($query);
431 my ($bibnum,$author)=@_;
433 my $query="Delete from additionalauthors where biblionumber=$bibnum";
434 my $sth=$dbh->prepare($query);
436 $query="insert into additionalauthors (author,biblionumber) values ('$author','$bibnum')";
438 $sth=$dbh->prepare($query);
445 my ($bibnum,$force,@subject)=@_;
449 for (my $i=0;$i<$count;$i++){
450 $subject[$i]=~ s/^ //g;
451 $subject[$i]=~ s/ $//g;
452 my $query="select * from catalogueentry where entrytype='s' and
453 catalogueentry='$subject[$i]'";
454 my $sth=$dbh->prepare($query);
456 if (my $data=$sth->fetchrow_hashref){
459 if ($force eq $subject[$i]){
460 #subject not in aut, chosen to force anway
461 #so insert into cataloguentry so its in auth file
462 $query="Insert into catalogueentry (entrytype,catalogueentry)
463 values ('s','$subject[$i]')";
464 my $sth2=$dbh->prepare($query);
469 $error="$subject[$i]\n does not exist in the subject authority file";
470 $query= "Select * from catalogueentry where
471 entrytype='s' and (catalogueentry like '$subject[$i] %' or
472 catalogueentry like '% $subject[$i] %' or catalogueentry like
474 my $sth2=$dbh->prepare($query);
477 while (my $data=$sth2->fetchrow_hashref){
478 $error=$error."<br>$data->{'catalogueentry'}";
481 # $error=$error."<br>$query";
487 my $query="Delete from bibliosubject where biblionumber=$bibnum";
489 my $sth=$dbh->prepare($query);
493 for (my $i=0;$i<$count;$i++){
494 $sth=$dbh->prepare("Insert into bibliosubject values ('$subject[$i]',$bibnum)");
495 # print $subject[$i];
505 my ($bibitemnum,$itemtype,$isbn,$publishercode,$publicationdate,$classification,$dewey,$subclass,$illus,$pages,$volumeddesc,$notes,$size,$place)=@_;
507 my $query="update biblioitems set itemtype='$itemtype',
508 isbn='$isbn',publishercode='$publishercode',publicationyear='$publicationdate',
509 classification='$classification',dewey='$dewey',subclass='$subclass',illus='$illus',
510 pages='$pages',volumeddesc='$volumeddesc',notes='$notes',size='$size',place='$place'
512 biblioitemnumber=$bibitemnum";
513 my $sth=$dbh->prepare($query);
521 my ($bibitemnum,$note)=@_;
523 my $query="update biblioitems set notes='$note' where
524 biblioitemnumber='$bibitemnum'";
525 my $sth=$dbh->prepare($query);
532 my ($bibnum,$itemtype,$isbn,$volinf,$class)=@_;
534 my $query="Select max(biblioitemnumber) from biblioitems";
535 my $sth=$dbh->prepare($query);
537 my $data=$sth->fetchrow_arrayref;
538 my $bibitemnum=$$data[0];
541 $query="insert into biblioitems (biblionumber,biblioitemnumber,
542 itemtype,isbn,volumeddesc,classification)
544 ($bibnum,$bibitemnum,'$itemtype','$isbn','$volinf','$class')";
545 $sth=$dbh->prepare($query);
556 my $query="insert into bibliosubject (biblionumber) values
558 my $sth=$dbh->prepare($query);
568 my $query="insert into bibliosubtitle (biblionumber) values
570 my $sth=$dbh->prepare($query);
578 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
579 if ($budget eq 'now'){
582 $budget="'2001-07-01'";
590 my $query="insert into aqorders (biblionumber,title,basketno,
591 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
592 biblioitemnumber,rrp,ecost,gst,budgetdate,unitprice,subscription,booksellerinvoicenumber)
595 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
596 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst',$budget,'$cost',
598 my $sth=$dbh->prepare($query);
602 $query="select * from aqorders where
603 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
604 $sth=$dbh->prepare($query);
606 my $data=$sth->fetchrow_hashref;
608 $ordnum=$data->{'ordernumber'};
609 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
610 ($ordnum,'$bookfund')";
611 $sth=$dbh->prepare($query);
619 my ($bibnum,$ordnum)=@_;
621 my $query="update aqorders set datecancellationprinted=now()
622 where biblionumber='$bibnum' and
623 ordernumber='$ordnum'";
624 my $sth=$dbh->prepare($query);
628 my $count=itemcount($bibnum);
636 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
638 my $query="update aqorders set title='$title',
639 quantity='$quantity',listprice='$listprice',basketno='$basketno',
640 rrp='$rrp',ecost='$ecost',unitprice='$cost',
641 booksellerinvoicenumber='$invoice'
643 ordernumber=$ordnum and biblionumber=$bibnum";
644 my $sth=$dbh->prepare($query);
648 $query="update aqorderbreakdown set bookfundid=$bookfund where
649 ordernumber=$ordnum";
650 $sth=$dbh->prepare($query);
659 my $query="Select max(ordernumber) from aqorders";
660 my $sth=$dbh->prepare($query);
662 my $data=$sth->fetchrow_arrayref;
663 my $ordnum=$$data[0];
671 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
673 my $query="update aqorders set quantityreceived='$quantrec',
674 datereceived=now(),booksellerinvoicenumber='$invoiceno',
675 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
677 where biblionumber=$biblio and ordernumber=$ordnum
680 my $sth=$dbh->prepare($query);
683 $query="update aqorderbreakdown set bookfundid=$bookfund where
684 ordernumber=$ordnum";
685 $sth=$dbh->prepare($query);
692 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
694 my $query="update aqorders set
695 unitprice='$cost', rrp='$rrp'
696 where biblionumber=$biblio and ordernumber=$ordnum
699 my $sth=$dbh->prepare($query);
702 $query="update aqorderbreakdown set bookfundid=$bookfund where
703 ordernumber=$ordnum";
704 $sth=$dbh->prepare($query);
712 my ($currency,$price)=@_;
714 my $query="Select rate from currency where currency='$currency'";
715 my $sth=$dbh->prepare($query);
717 my $data=$sth->fetchrow_hashref;
720 my $cur=$data->{'rate'};
724 my $price=$price / $cur;
730 my $query="Select * from currency";
731 my $sth=$dbh->prepare($query);
735 while (my $data=$sth->fetchrow_hashref){
741 return($i,\@results);
747 my $query="Select * from currency where currency='$cur'";
748 my $sth=$dbh->prepare($query);
751 my $data=$sth->fetchrow_hashref;
757 sub updatecurrencies {
758 my ($currency,$rate)=@_;
760 my $query="update currency set rate=$rate where currency='$currency'";
761 my $sth=$dbh->prepare($query);
770 my $query="Update aqbooksellers set
771 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
772 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
773 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
774 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
775 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
776 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
777 '$data->{'contnotes'}', active=$data->{'active'},
778 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
779 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
780 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
781 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
782 nocalc='$data->{'nocalc'}'
783 where id='$data->{'id'}'";
784 my $sth=$dbh->prepare($query);
794 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
796 my $data2=$sth->fetchrow_hashref;
798 $data2->{'max(id)'}++;
799 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
802 $data->{'id'}=$data2->{'max(id)'};
805 return($data->{'id'});
810 ($count,$bibitemno,$biblio,$replacement,$price,$booksellerid,$branch,$loan,@barcodes)=@_;
812 my $sth=$dbh->prepare("Select max(itemnumber) from items");
814 my $data=$sth->fetchrow_hashref;
815 my $item=$data->{'max(itemnumber)'};
819 for (my $i=0;$i<$count;$i++){
820 $barcodes[$i]=uc $barcodes[$i];
821 my $query="Insert into items (biblionumber,biblioitemnumber,itemnumber,barcode,
822 booksellerid,dateaccessioned,homebranch,holdingbranch,price,replacementprice,
823 replacementpricedate,notforloan) values
824 ($biblio,$bibitemno,$item,'$barcodes[$i]','$booksellerid',now(),'$branch',
825 '$branch','$price','$replacement',now(),$loan)";
826 my $sth=$dbh->prepare($query);
828 $error.=$sth->errstr;
838 my ($count,@barcodes)=@_;
841 for (my $i=0;$i<$count;$i++){
842 $barcodes[$i]=uc $barcodes[$i];
843 my $query="Select * from items where barcode='$barcodes[$i]'";
844 my $sth=$dbh->prepare($query);
846 if (my $data=$sth->fetchrow_hashref){
847 $error.=" Duplicate Barcode: $barcodes[$i]";
856 my ($loan,$itemnum,$bibitemnum,$barcode,$notes,$homebranch,$lost,$wthdrawn,$replacement)=@_;
858 my $query="update items set biblioitemnumber=$bibitemnum,
859 barcode='$barcode',itemnotes='$notes'
860 where itemnumber=$itemnum";
862 $query="update items set biblioitemnumber=$bibitemnum,notforloan=$loan where itemnumber=$itemnum";
865 $query="update items set biblioitemnumber=$bibitemnum,
866 barcode='$barcode',itemnotes='$notes',homebranch='$homebranch',
867 itemlost='$lost',wthdrawn='$wthdrawn' where itemnumber=$itemnum";
869 if ($replacement ne ''){
870 $query=~ s/ where/,replacementprice='$replacement' where/;
873 my $sth=$dbh->prepare($query);
880 my($price,$rrp,$itemnum)=@_;
882 my $query="update items set price='$price',replacementprice='$rrp'
883 where itemnumber=$itemnum";
884 my $sth=$dbh->prepare($query);
892 my $query="Select count(*) from items where biblioitemnumber='$bibitemnum'";
893 my $sth=$dbh->prepare($query);
895 my $data=$sth->fetchrow_hashref;
898 return($data->{'count(*)'});
902 my ($biblionumber)=@_;
904 my $query="Select * from biblioitems,items,itemtypes where
905 biblioitems.biblionumber=$biblionumber
906 and biblioitems.biblioitemnumber=items.biblioitemnumber and
907 itemtypes.itemtype=biblioitems.itemtype
908 order by items.biblioitemnumber";
909 my $sth=$dbh->prepare($query);
913 while (my $data=$sth->fetchrow_hashref){
923 my ($bibitemnum,$itemtype)=@_;
925 my $query="Select * from biblioitems where biblioitemnumber=$bibitemnum
926 and itemtype='$itemtype'";
927 my $sth=$dbh->prepare($query);
930 if (my $data=$sth->fetchrow_hashref){
941 my $query="select * from items where itemnumber=$itemnum";
942 my $sth=$dbh->prepare($query);
944 my @data=$sth->fetchrow_array;
946 $query="Insert into deleteditems values (";
947 foreach my $temp (@data){
948 $query=$query."'$temp',";
952 $sth=$dbh->prepare($query);
955 $query = "Delete from items where itemnumber=$itemnum";
956 $sth=$dbh->prepare($query);
965 my $query="select * from biblioitems where biblioitemnumber=$itemnum";
966 my $sth=$dbh->prepare($query);
968 if (my @data=$sth->fetchrow_array){
970 $query="Insert into deletedbiblioitems values (";
971 foreach my $temp (@data){
973 $query=$query."'$temp',";
977 $sth=$dbh->prepare($query);
980 $query = "Delete from biblioitems where biblioitemnumber=$itemnum";
981 $sth=$dbh->prepare($query);
992 my $query="select * from biblio where biblionumber=$biblio";
993 my $sth=$dbh->prepare($query);
995 if (my @data=$sth->fetchrow_array){
997 $query="Insert into deletedbiblio values (";
998 foreach my $temp (@data){
1000 $query=$query."'$temp',";
1004 $sth=$dbh->prepare($query);
1007 $query = "Delete from biblio where biblionumber=$biblio";
1008 $sth=$dbh->prepare($query);
1016 END { } # module clean-up code here (global destructor)