1 package C4::Acquisitions; #assumes C4/Acquisitions.pm
4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # Suite 330, Boston, MA 02111-1307 USA
26 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
28 # set the version for version checking
33 &getorders &bookseller &breakdown &basket &newbasket &bookfunds
34 &ordersearch &neworder &newordernum
35 &modorder &getsingleorder &invoice &receiveorder
37 &bookfundbreakdown &curconvert &updatesup &insertsup
39 &branches &getallorders &getrecorders &updatecurrencies
40 &getorder &getcurrency &updaterecorder
43 %EXPORT_TAGS = ( ); # eg: TAG => [ qw!name1 name2! ],
45 # your exported package globals go here,
46 # as well as any optionally exported functions
48 @EXPORT_OK = qw($Var1 %Hashit);
51 # non-exported package globals go here
52 use vars qw(@more $stuff);
54 # initalize package globals, first exported ones
61 # then the others (which are still accessible as $Some::Module::stuff)
65 # all file-scoped lexicals must be created before
66 # the functions below that use them.
68 # file-private lexicals go here
72 # here's a file-private function as a closure,
73 # callable as &$priv_func; it cannot be prototyped.
78 # make all your functions, whether exported or not;
83 my $query = "Select count(*),authorisedby,entrydate,basketno from aqorders where
84 booksellerid='$supplierid' and (quantity > quantityreceived or
85 quantityreceived is NULL)
86 and (datecancellationprinted is NULL or datecancellationprinted = '0000-00-00')";
87 $query.=" group by basketno order by entrydate desc";
89 my $sth=$dbh->prepare($query);
93 while (my $data=$sth->fetchrow_hashref){
99 return ($i,\@results);
105 my $query="Select count(*) from items where biblionumber=$biblio";
107 my $sth=$dbh->prepare($query);
109 my $data=$sth->fetchrow_hashref;
112 return($data->{'count(*)'});
118 my $query="Select ordernumber
120 where biblionumber=? and biblioitemnumber=?";
121 my $sth=$dbh->prepare($query);
122 $sth->execute($bib,$bi);
123 my $ordnum=$sth->fetchrow_hashref;
125 my $order=getsingleorder($ordnum->{'ordernumber'});
128 return ($order,$ordnum->{'ordernumber'});
134 my $query="Select * from biblio,biblioitems,aqorders,aqorderbreakdown
135 where aqorders.ordernumber=?
136 and biblio.biblionumber=aqorders.biblionumber and
137 biblioitems.biblioitemnumber=aqorders.biblioitemnumber and
138 aqorders.ordernumber=aqorderbreakdown.ordernumber";
139 my $sth=$dbh->prepare($query);
140 $sth->execute($ordnum);
141 my $data=$sth->fetchrow_hashref;
150 my $query="Select * from aqorders,biblio,biblioitems where
151 booksellerinvoicenumber='$invoice'
152 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
153 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber";
156 my $sth=$dbh->prepare($query);
158 while (my $data=$sth->fetchrow_hashref){
168 #gets all orders from a certain supplier, orders them alphabetically
171 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
172 and (cancelledby is NULL or cancelledby = '')
173 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
174 aqorders.biblioitemnumber
175 group by aqorders.biblioitemnumber
180 my $sth=$dbh->prepare($query);
182 while (my $data=$sth->fetchrow_hashref){
192 #gets all orders from a certain supplier, orders them alphabetically
195 my $query="Select * from aqorders,biblio,biblioitems where booksellerid='$supid'
196 and (cancelledby is NULL or cancelledby = '')
197 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
198 aqorders.biblioitemnumber and
199 aqorders.quantityreceived>0
200 and aqorders.datereceived >=now()
201 group by aqorders.biblioitemnumber
206 my $sth=$dbh->prepare($query);
208 while (my $data=$sth->fetchrow_hashref){
218 my ($search,$biblio,$catview)=@_;
220 my $query="Select *,biblio.title from aqorders,biblioitems,biblio
221 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber
222 and biblio.biblionumber=aqorders.biblionumber
223 and ((datecancellationprinted is NULL)
224 or (datecancellationprinted = '0000-00-00')
226 my @data=split(' ',$search);
228 for (my $i=0;$i<$count;$i++){
229 $query.= "(biblio.title like '$data[$i]%' or biblio.title like '% $data[$i]%') and ";
232 $query.=" ) or biblioitems.isbn='$search'
233 or (aqorders.ordernumber='$search' and aqorders.biblionumber='$biblio')) ";
234 if ($catview ne 'yes'){
235 $query.=" and (quantityreceived < quantity or quantityreceived is NULL)";
237 $query.=" group by aqorders.ordernumber";
238 my $sth=$dbh->prepare($query);
243 while (my $data=$sth->fetchrow_hashref){
244 my $sth2=$dbh->prepare("Select * from biblio where
245 biblionumber='$data->{'biblionumber'}'");
247 my $data2=$sth2->fetchrow_hashref;
249 $data->{'author'}=$data2->{'author'};
250 $data->{'seriestitle'}=$data2->{'seriestitle'};
251 $sth2=$dbh->prepare("Select * from aqorderbreakdown where
252 ordernumber=$data->{'ordernumber'}");
254 $data2=$sth2->fetchrow_hashref;
256 $data->{'branchcode'}=$data2->{'branchcode'};
257 $data->{'bookfundid'}=$data2->{'bookfundid'};
268 my ($searchstring)=@_;
270 my $query="Select * from aqbooksellers where name like '%$searchstring%' or
271 id = '$searchstring'";
272 my $sth=$dbh->prepare($query);
276 while (my $data=$sth->fetchrow_hashref){
288 my $query="Select * from aqorderbreakdown where ordernumber='$id'";
289 my $sth=$dbh->prepare($query);
293 while (my $data=$sth->fetchrow_hashref){
299 return($i,\@results);
303 my ($basketno,$supplier)=@_;
305 my $query="Select *,biblio.title from aqorders,biblio,biblioitems
306 where basketno='$basketno'
307 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
308 =aqorders.biblioitemnumber
309 and (datecancellationprinted is NULL or datecancellationprinted =
311 if ($supplier ne ''){
312 $query.=" and aqorders.booksellerid='$supplier'";
314 $query.=" group by aqorders.ordernumber";
315 my $sth=$dbh->prepare($query);
320 while (my $data=$sth->fetchrow_hashref){
331 my $query="Select max(basketno) from aqorders";
332 my $sth=$dbh->prepare($query);
334 my $data=$sth->fetchrow_arrayref;
335 my $basket=$$data[0];
343 my ($day,$month,$year)=(localtime(time))[3,4,5];
346 my $today=sprintf "%4d-%02d-%02d", $year, $monthm, $day;
348 my $query="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
350 and aqbudget.startdate<'$today' and aqbudget.enddate > '$today'
351 group by aqbookfund.bookfundid order by bookfundname";
352 my $sth=$dbh->prepare($query);
356 while (my $data=$sth->fetchrow_hashref){
367 my $query="Select * from branches";
368 my $sth=$dbh->prepare($query);
373 while (my $data=$sth->fetchrow_hashref){
383 sub bookfundbreakdown {
386 my $query="Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
387 from aqorders,aqorderbreakdown where bookfundid='$id' and
388 aqorders.ordernumber=aqorderbreakdown.ordernumber and ((budgetdate >=
389 '2001-07-01' and budgetdate <'2002-07-01') or
390 (datereceived >= '2001-07-01' and datereceived < '2002-07-01'))
391 and (datecancellationprinted is NULL or
392 datecancellationprinted='0000-00-00')";
393 my $sth=$dbh->prepare($query);
397 while (my $data=$sth->fetchrow_hashref){
398 if ($data->{'subscription'} == 1){
399 $spent+=$data->{'quantity'}*$data->{'unitprice'};
401 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
402 $comtd+=($data->{'ecost'})*$leftover;
403 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
408 return($spent,$comtd);
413 my ($bibnum,$title,$ordnum,$basket,$quantity,$listprice,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice)=@_;
414 if ($budget eq 'now'){
417 $budget="'2001-07-01'";
425 my $query="insert into aqorders (biblionumber,title,basketno,
426 quantity,listprice,booksellerid,entrydate,requisitionedby,authorisedby,notes,
427 biblioitemnumber,rrp,ecost,gst,budgetdate,unitprice,subscription,booksellerinvoicenumber)
430 ($bibnum,'$title',$basket,$quantity,$listprice,'$supplier',now(),
431 '$who','$who','$notes',$bibitemnum,'$rrp','$ecost','$gst',$budget,'$cost',
433 my $sth=$dbh->prepare($query);
437 $query="select * from aqorders where
438 biblionumber=$bibnum and basketno=$basket and ordernumber >=$ordnum";
439 $sth=$dbh->prepare($query);
441 my $data=$sth->fetchrow_hashref;
443 $ordnum=$data->{'ordernumber'};
444 $query="insert into aqorderbreakdown (ordernumber,bookfundid) values
445 ($ordnum,'$bookfund')";
446 $sth=$dbh->prepare($query);
454 my ($bibnum,$ordnum)=@_;
456 my $query="update aqorders set datecancellationprinted=now()
457 where biblionumber='$bibnum' and
458 ordernumber='$ordnum'";
459 my $sth=$dbh->prepare($query);
463 my $count=itemcount($bibnum);
471 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice)=@_;
473 my $query="update aqorders set title='$title',
474 quantity='$quantity',listprice='$listprice',basketno='$basketno',
475 rrp='$rrp',ecost='$ecost',unitprice='$cost',
476 booksellerinvoicenumber='$invoice'
478 ordernumber=$ordnum and biblionumber=$bibnum";
479 my $sth=$dbh->prepare($query);
483 $query="update aqorderbreakdown set bookfundid=$bookfund where
484 ordernumber=$ordnum";
485 $sth=$dbh->prepare($query);
494 my $query="Select max(ordernumber) from aqorders";
495 my $sth=$dbh->prepare($query);
497 my $data=$sth->fetchrow_arrayref;
498 my $ordnum=$$data[0];
506 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$bibitemno,$freight,$bookfund,$rrp)=@_;
508 my $query="update aqorders set quantityreceived='$quantrec',
509 datereceived=now(),booksellerinvoicenumber='$invoiceno',
510 biblioitemnumber=$bibitemno,unitprice='$cost',freight='$freight',
512 where biblionumber=$biblio and ordernumber=$ordnum
515 my $sth=$dbh->prepare($query);
518 $query="update aqorderbreakdown set bookfundid=$bookfund where
519 ordernumber=$ordnum";
520 $sth=$dbh->prepare($query);
527 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
529 my $query="update aqorders set
530 unitprice='$cost', rrp='$rrp'
531 where biblionumber=$biblio and ordernumber=$ordnum
534 my $sth=$dbh->prepare($query);
537 $query="update aqorderbreakdown set bookfundid=$bookfund where
538 ordernumber=$ordnum";
539 $sth=$dbh->prepare($query);
547 my ($currency,$price)=@_;
550 my $query="Select rate from currency where currency='$currency'";
551 my $sth=$dbh->prepare($query);
553 my $data=$sth->fetchrow_hashref;
556 my $cur=$data->{'rate'};
560 $convertedprice=$price / $cur;
561 return($convertedprice);
566 my $query="Select * from currency";
567 my $sth=$dbh->prepare($query);
571 while (my $data=$sth->fetchrow_hashref){
577 return($i,\@results);
583 my $query="Select * from currency where currency='$cur'";
584 my $sth=$dbh->prepare($query);
587 my $data=$sth->fetchrow_hashref;
593 sub updatecurrencies {
594 my ($currency,$rate)=@_;
596 my $query="update currency set rate=$rate where currency='$currency'";
597 my $sth=$dbh->prepare($query);
606 my $query="Update aqbooksellers set
607 name='$data->{'name'}',address1='$data->{'address1'}',address2='$data->{'address2'}',
608 address3='$data->{'address3'}',address4='$data->{'address4'}',postal='$data->{'postal'}',
609 phone='$data->{'phone'}',fax='$data->{'fax'}',url='$data->{'url'}',
610 contact='$data->{'contact'}',contpos='$data->{'contpos'}',
611 contphone='$data->{'contphone'}', contfax='$data->{'contfax'}', contaltphone=
612 '$data->{'contaltphone'}', contemail='$data->{'contemail'}', contnotes=
613 '$data->{'contnotes'}', active=$data->{'active'},
614 listprice='$data->{'listprice'}', invoiceprice='$data->{'invoiceprice'}',
615 gstreg=$data->{'gstreg'}, listincgst=$data->{'listincgst'},
616 invoiceincgst=$data->{'invoiceincgst'}, specialty='$data->{'specialty'}',
617 discount='$data->{'discount'}',invoicedisc='$data->{'invoicedisc'}',
618 nocalc='$data->{'nocalc'}'
619 where id='$data->{'id'}'";
620 my $sth=$dbh->prepare($query);
630 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
632 my $data2=$sth->fetchrow_hashref;
634 $data2->{'max(id)'}++;
635 $sth=$dbh->prepare("Insert into aqbooksellers (id) values ($data2->{'max(id)'})");
638 $data->{'id'}=$data2->{'max(id)'};
641 return($data->{'id'});
645 END { } # module clean-up code here (global destructor)