1 package C4::Acquisitions; #assumes C4/Acquisitions.pm
8 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
10 # set the version for version checking
15 &getorders &bookseller &breakdown &basket &newbasket &bookfunds
16 &ordersearch &neworder &newordernum
17 &modorder &getsingleorder &invoice &receiveorder
19 &bookfundbreakdown &curconvert &updatesup &insertsup
21 &branches &getallorders &getrecorders &updatecurrencies
22 &getorder &getcurrency &updaterecorder
25 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
27 # your exported package globals go here,
28 # as well as any optionally exported functions
30 @EXPORT_OK = qw($Var1 %Hashit);
33 # non-exported package globals go here
34 use vars qw(@more $stuff);
36 # initalize package globals, first exported ones
43 # then the others (which are still accessible as $Some::Module::stuff)
47 # all file-scoped lexicals must be created before
48 # the functions below that use them.
50 # file-private lexicals go here
54 # here's a file-private function as a closure,
55 # callable as &$priv_func; it cannot be prototyped.
60 # make all your functions, whether exported or not;
65 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
66 booksellerid='$supplierid' and (quantity > quantityreceived or
67 quantityreceived is NULL)
68 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
69 $query.=" group by basketno order by entrydate desc";
71 my $sth=$dbh->prepare($query);
75 while (my $data=$sth->fetchrow_hashref){
81 return ($i,\@results);
87 my $query="Select count(*) from items where biblionumber=$biblio";
89 my $sth=$dbh->prepare($query);
91 my $data=$sth->fetchrow_hashref;
94 return($data->{'count(*)'});
100 my $query="Select ordernumber
102 where biblionumber=? and biblioitemnumber=?";
103 my $sth=$dbh->prepare($query);
104 $sth->execute($bib,$bi);
105 my $ordnum=$sth->fetchrow_hashref;
107 my $order=getsingleorder($ordnum->{'ordernumber'});
110 return ($order,$ordnum->{'ordernumber'});
116 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
117 where aqorders.ordernumber=?
118 and biblio.biblionumber=aqorders.biblionumber and
119 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
120 aqorders.ordernumber=aqorderbreakdown.ordernumber";
121 my $sth=$dbh->prepare($query);
122 $sth->execute($ordnum);
123 my $data=$sth->fetchrow_hashref;
132 my $query="Select * from aqorders,biblio,biblioitems where
133 booksellerinvoicenumber='$invoice'
134 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
135 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
138 my $sth=$dbh->prepare($query);
140 while (my $data=$sth->fetchrow_hashref){
150 #gets all orders from a certain supplier, orders them alphabetically
153 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
154 and (cancelledby is NULL or cancelledby = '')
155 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
156 aqorders.biblioitemnumber
157 group by aqorders.biblioitemnumber
162 my $sth=$dbh->prepare($query);
164 while (my $data=$sth->fetchrow_hashref){
174 #gets all orders from a certain supplier, orders them alphabetically
177 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
178 and (cancelledby is NULL or cancelledby = '')
179 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
180 aqorders.biblioitemnumber and
181 aqorders.quantityreceived>0
182 and aqorders.datereceived >=now()
183 group by aqorders.biblioitemnumber
188 my $sth=$dbh->prepare($query);
190 while (my $data=$sth->fetchrow_hashref){
200 my ($search,$biblio,$catview)=@_;
202 my $query="Select *,biblio.title from aqorders,biblioitems,biblio
203 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
204 and biblio.biblionumber=aqorders.biblionumber
205 and ((datecancellationprinted is NULL)
206 or (datecancellationprinted = '0000-00-00')
208 my @data=split(' ',$search);
210 for (my $i=0;$i<$count;$i++){
211 $query.= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
214 $query.=" ) or biblioitems.isbn='$search'
215 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
216 if ($catview ne 'yes'){
217 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
219 $query.=" group by aqorders.ordernumber";
220 my $sth=$dbh->prepare($query);
225 while (my $data=$sth->fetchrow_hashref){
226 my $sth2=$dbh->prepare("Select * from biblio where
227 biblionumber='$data->{'biblionumber'}'");
229 my $data2=$sth2->fetchrow_hashref;
231 $data->{'author'}=$data2->{'author'};
232 $data->{'seriestitle'}=$data2->{'seriestitle'};
233 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
234 ordernumber=$data->{'ordernumber'}");
236 $data2=$sth2->fetchrow_hashref;
238 $data->{'branchcode'}=$data2->{'branchcode'};
239 $data->{'bookfundid'}=$data2->{'bookfundid'};
250 my ($searchstring)=@_;
252 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
253 id = '$searchstring'";
254 my $sth=$dbh->prepare($query);
258 while (my $data=$sth->fetchrow_hashref){
270 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
271 my $sth=$dbh->prepare($query);
275 while (my $data=$sth->fetchrow_hashref){
281 return($i,\@results);
285 my ($basketno,$supplier)=@_;
287 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
288 where basketno='$basketno'
289 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
290 =aqorders.biblioitemnumber
291 and (datecancellationprinted is NULL or datecancellationprinted =
293 if ($supplier ne ''){
294 $query.=" and aqorders.booksellerid='$supplier'";
296 $query.=" group by aqorders.ordernumber";
297 my $sth=$dbh->prepare($query);
302 while (my $data=$sth->fetchrow_hashref){
313 my $query="Select max(basketno) from aqorders";
314 my $sth=$dbh->prepare($query);
316 my $data=$sth->fetchrow_arrayref;
317 my $basket=$$data[0];
326 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
328 and aqbudget.startdate='2001-07-01'
329 group by aqbookfund.bookfundid order by bookfundname";
330 my $sth=$dbh->prepare($query);
334 while (my $data=$sth->fetchrow_hashref){
345 my $query="Select * from branches";
346 my $sth=$dbh->prepare($query);
351 while (my $data=$sth->fetchrow_hashref){
361 sub bookfundbreakdown {
364 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
365 from aqorders,aqorderbreakdown where bookfundid='$id' and
366 aqorders.ordernumber=aqorderbreakdown.ordernumber and ((budgetdate >=
367 '2001-07-01' and budgetdate <'2002-07-01') or
368 (datereceived >= '2001-07-01' and datereceived < '2002-07-01'))
369 and (datecancellationprinted is NULL or
370 datecancellationprinted='0000-00-00')";
371 my $sth=$dbh->prepare($query);
375 while (my $data=$sth->fetchrow_hashref){
376 if ($data->{'subscription'} == 1){
377 $spent+=$data->{'quantity'}*$data->{'unitprice'};
379 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
380 $comtd+=($data->{'ecost'})*$leftover;
381 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
386 return($spent,$comtd);
391 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
392 if ($budget eq 'now'){
395 $budget="'2001-07-01'";
403 my $query="insert into aqorders (biblionumber,title,basketno,
404 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
405 biblioitemnumber,rrp,ecost,gst,budgetdate,unitprice,subscription,booksellerinvoicenumber)
408 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
409 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst',$budget,'$cost',
411 my $sth=$dbh->prepare($query);
415 $query="select * from aqorders where
416 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
417 $sth=$dbh->prepare($query);
419 my $data=$sth->fetchrow_hashref;
421 $ordnum=$data->{'ordernumber'};
422 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
423 ($ordnum,'$bookfund')";
424 $sth=$dbh->prepare($query);
432 my ($bibnum,$ordnum)=@_;
434 my $query="update aqorders set datecancellationprinted=now()
435 where biblionumber='$bibnum' and
436 ordernumber='$ordnum'";
437 my $sth=$dbh->prepare($query);
441 my $count=itemcount($bibnum);
449 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
451 my $query="update aqorders set title='$title',
452 quantity='$quantity',listprice='$listprice',basketno='$basketno',
453 rrp='$rrp',ecost='$ecost',unitprice='$cost',
454 booksellerinvoicenumber='$invoice'
456 ordernumber=$ordnum and biblionumber=$bibnum";
457 my $sth=$dbh->prepare($query);
461 $query="update aqorderbreakdown set bookfundid=$bookfund where
462 ordernumber=$ordnum";
463 $sth=$dbh->prepare($query);
472 my $query="Select max(ordernumber) from aqorders";
473 my $sth=$dbh->prepare($query);
475 my $data=$sth->fetchrow_arrayref;
476 my $ordnum=$$data[0];
484 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
486 my $query="update aqorders set quantityreceived='$quantrec',
487 datereceived=now(),booksellerinvoicenumber='$invoiceno',
488 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
490 where biblionumber=$biblio and ordernumber=$ordnum
493 my $sth=$dbh->prepare($query);
496 $query="update aqorderbreakdown set bookfundid=$bookfund where
497 ordernumber=$ordnum";
498 $sth=$dbh->prepare($query);
505 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
507 my $query="update aqorders set
508 unitprice='$cost', rrp='$rrp'
509 where biblionumber=$biblio and ordernumber=$ordnum
512 my $sth=$dbh->prepare($query);
515 $query="update aqorderbreakdown set bookfundid=$bookfund where
516 ordernumber=$ordnum";
517 $sth=$dbh->prepare($query);
525 my ($currency,$price)=@_;
528 my $query="Select rate from currency where currency='$currency'";
529 my $sth=$dbh->prepare($query);
531 my $data=$sth->fetchrow_hashref;
534 my $cur=$data->{'rate'};
538 $convertedprice=$price / $cur;
539 return($convertedprice);
544 my $query="Select * from currency";
545 my $sth=$dbh->prepare($query);
549 while (my $data=$sth->fetchrow_hashref){
555 return($i,\@results);
561 my $query="Select * from currency where currency='$cur'";
562 my $sth=$dbh->prepare($query);
565 my $data=$sth->fetchrow_hashref;
571 sub updatecurrencies {
572 my ($currency,$rate)=@_;
574 my $query="update currency set rate=$rate where currency='$currency'";
575 my $sth=$dbh->prepare($query);
584 my $query="Update aqbooksellers set
585 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
586 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
587 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
588 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
589 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
590 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
591 '$data->{'contnotes'}', active=$data->{'active'},
592 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
593 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
594 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
595 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
596 nocalc='$data->{'nocalc'}'
597 where id='$data->{'id'}'";
598 my $sth=$dbh->prepare($query);
608 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
610 my $data2=$sth->fetchrow_hashref;
612 $data2->{'max(id)'}++;
613 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
616 $data->{'id'}=$data2->{'max(id)'};
619 return($data->{'id'});
623 END { } # module clean-up code here (global destructor)