1 package C4::Acquisition;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
27 use vars qw($VERSION @ISA @EXPORT);
29 # set the version for version checking
34 C4::Acquisition - Koha functions for dealing with orders and acquisitions
42 The functions in this module deal with acquisitions, managing book
43 orders, converting money to different currencies, and so forth.
53 &getbasket &getbasketcontent &newbasket &closebasket
55 &getorders &getallorders &getrecorders
56 &getorder &neworder &delorder
57 &ordersearch &histsearch
58 &modorder &getsingleorder &invoice &receiveorder
59 &updaterecorder &newordernum
60 &getsupplierlistwithlateorders
63 &bookfunds &curconvert &getcurrencies &bookfundbreakdown
64 &updatecurrencies &getcurrency
67 &bookseller &breakdown
79 $aqbasket = &getbasket($basketnumber);
81 get all basket informations in aqbasket for a given basket
86 my $dbh=C4::Context->dbh;
87 my $sth=$dbh->prepare("select aqbasket.*,borrowers.firstname+' '+borrowers.surname as authorisedbyname, borrowers.branchcode as branch from aqbasket left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber where basketno=?");
88 $sth->execute($basketno);
89 return($sth->fetchrow_hashref);
92 =item getbasketcontent
94 ($count, @orders) = &getbasketcontent($basketnumber, $booksellerID);
96 Looks up the pending (non-cancelled) orders with the given basket
97 number. If C<$booksellerID> is non-empty, only orders from that seller
100 C<&basket> returns a two-element array. C<@orders> is an array of
101 references-to-hash, whose keys are the fields from the aqorders,
102 biblio, and biblioitems tables in the Koha database. C<$count> is the
103 number of elements in C<@orders>.
107 sub getbasketcontent {
108 my ($basketno,$supplier,$orderby)=@_;
109 my $dbh = C4::Context->dbh;
110 my $query="Select biblio.*,biblioitems.*,aqorders.*,aqorderbreakdown.*,biblio.title from aqorders,biblio,biblioitems
111 left join aqorderbreakdown on aqorderbreakdown.ordernumber=aqorders.ordernumber
112 where basketno='$basketno'
113 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber
114 =aqorders.biblioitemnumber
115 and (datecancellationprinted is NULL or datecancellationprinted =
117 if ($supplier ne ''){
118 $query.=" and aqorders.booksellerid='$supplier'";
121 $orderby="biblioitems.publishercode" unless $orderby;
122 $query.=" order by $orderby";
123 my $sth=$dbh->prepare($query);
128 while (my $data=$sth->fetchrow_hashref){
138 $basket = &newbasket();
140 Create a new basket in aqbasket table
144 my ($booksellerid,$authorisedby) = @_;
145 my $dbh = C4::Context->dbh;
146 my $sth=$dbh->do("insert into aqbasket (creationdate,booksellerid,authorisedby) values(now(),'$booksellerid','$authorisedby')");
147 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
148 my $basket = $dbh->{'mysql_insertid'};
154 &newbasket($basketno);
156 close a basket (becomes unmodifiable,except for recieves
161 my $dbh = C4::Context->dbh;
162 my $sth=$dbh->prepare("update aqbasket set closedate=now() where basketno=?");
163 $sth->execute($basketno);
168 &neworder($basket, $biblionumber, $title, $quantity, $listprice,
169 $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
170 $ecost, $gst, $budget, $unitprice, $subscription,
171 $booksellerinvoicenumber);
173 Adds a new order to the database. Any argument that isn't described
174 below is the new value of the field with the same name in the aqorders
175 table of the Koha database.
177 C<$ordnum> is a "minimum order number." After adding the new entry to
178 the aqorders table, C<&neworder> finds the first entry in aqorders
179 with order number greater than or equal to C<$ordnum>, and adds an
180 entry to the aqorderbreakdown table, with the order number just found,
181 and the book fund ID of the newly-added order.
183 C<$budget> is effectively ignored.
185 C<$subscription> may be either "yes", or anything else for "no".
190 my ($basketno,$bibnum,$title,$quantity,$listprice,$booksellerid,$authorisedby,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice,$sort1,$sort2)=@_;
191 if ($budget eq 'now'){
194 $budget="'2001-07-01'";
201 # if $basket empty, it's also a new basket, create it
203 $basketno=newbasket($booksellerid,$authorisedby);
205 my $dbh = C4::Context->dbh;
206 my $sth=$dbh->prepare("insert into aqorders
207 (biblionumber,title,basketno,quantity,listprice,notes,
208 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2)
209 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
210 $sth->execute($bibnum,$title,$basketno,$quantity,$listprice,$notes,
211 $bibitemnum,$rrp,$ecost,$gst,$cost,$sub,$sort1,$sort2);
213 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
214 my $ordnum = $dbh->{'mysql_insertid'};
215 $sth=$dbh->prepare("insert into aqorderbreakdown (ordernumber,bookfundid) values
217 $sth->execute($ordnum,$bookfund);
224 &delorder($biblionumber, $ordernumber);
226 Cancel the order with the given order and biblio numbers. It does not
227 delete any entries in the aqorders table, it merely marks them as
233 my ($bibnum,$ordnum)=@_;
234 my $dbh = C4::Context->dbh;
235 my $sth=$dbh->prepare("update aqorders set datecancellationprinted=now()
236 where biblionumber=? and ordernumber=?");
237 $sth->execute($bibnum,$ordnum);
243 &modorder($title, $ordernumber, $quantity, $listprice,
244 $biblionumber, $basketno, $supplier, $who, $notes,
245 $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
246 $unitprice, $booksellerinvoicenumber);
248 Modifies an existing order. Updates the order with order number
249 C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
250 update the fields with the same name in the aqorders table of the Koha
253 Entries with order number C<$ordernumber> in the aqorderbreakdown
254 table are also updated to the new book fund ID.
259 my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice,$sort1,$sort2)=@_;
260 my $dbh = C4::Context->dbh;
261 my $sth=$dbh->prepare("update aqorders set title=?,
262 quantity=?,listprice=?,basketno=?,
263 rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?,
264 notes=?,sort1=?, sort2=?
266 ordernumber=? and biblionumber=?");
267 $sth->execute($title,$quantity,$listprice,$basketno,$rrp,$ecost,$cost,$invoice,$notes,$sort1,$sort2,$ordnum,$bibnum);
269 $sth=$dbh->prepare("update aqorderbreakdown set bookfundid=? where
271 unless ($sth->execute($bookfund,$ordnum)) { # zero rows affected [Bug 734]
272 my $query="insert into aqorderbreakdown (ordernumber,bookfundid) values (?,?)";
273 $sth=$dbh->prepare($query);
274 $sth->execute($ordnum,$bookfund);
281 $order = &newordernum();
283 Finds the next unused order number in the aqorders table of the Koha
284 database, and returns it.
288 # FIXME - Race condition
290 my $dbh = C4::Context->dbh;
291 my $sth=$dbh->prepare("Select max(ordernumber) from aqorders");
293 my $data=$sth->fetchrow_arrayref;
294 my $ordnum=$$data[0];
302 &receiveorder($biblionumber, $ordernumber, $quantityreceived, $user,
303 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
304 $freight, $bookfund, $rrp);
306 Updates an order, to reflect the fact that it was received, at least
307 in part. All arguments not mentioned below update the fields with the
308 same name in the aqorders table of the Koha database.
310 Updates the order with bibilionumber C<$biblionumber> and ordernumber
313 Also updates the book fund ID in the aqorderbreakdown table.
318 my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$freight,$rrp)=@_;
319 my $dbh = C4::Context->dbh;
320 my $sth=$dbh->prepare("update aqorders set quantityreceived=?,datereceived=now(),booksellerinvoicenumber=?,
321 unitprice=?,freight=?,rrp=?
322 where biblionumber=? and ordernumber=?");
323 $sth->execute($quantrec,$invoiceno,$cost,$freight,$rrp,$biblio,$ordnum);
329 &updaterecorder($biblionumber, $ordernumber, $user, $unitprice,
332 Updates the order with biblionumber C<$biblionumber> and order number
333 C<$ordernumber>. C<$bookfundid> is the new value for the book fund ID
334 in the aqorderbreakdown table of the Koha database. All other
335 arguments update the fields with the same name in the aqorders table.
342 my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_;
343 my $dbh = C4::Context->dbh;
344 my $sth=$dbh->prepare("update aqorders set
346 where biblionumber=? and ordernumber=?
348 $sth->execute($cost,$rrp,$biblio,$ordnum);
350 $sth=$dbh->prepare("update aqorderbreakdown set bookfundid=? where ordernumber=?");
351 $sth->execute($bookfund,$ordnum);
363 ($count, $orders) = &getorders($booksellerid);
365 Finds pending orders from the bookseller with the given ID. Ignores
366 completed and cancelled orders.
368 C<$count> is the number of elements in C<@{$orders}>.
370 C<$orders> is a reference-to-array; each element is a
371 reference-to-hash with the following fields:
377 Gives the number of orders in with this basket number.
379 =item C<authorizedby>
385 These give the value of the corresponding field in the aqorders table
386 of the Koha database.
390 Results are ordered from most to least recent.
396 my $dbh = C4::Context->dbh;
397 my $strsth ="Select count(*),authorisedby,creationdate,aqbasket.basketno,
398 closedate,surname,firstname,aqorders.title
400 left join aqbasket on aqbasket.basketno=aqorders.basketno
401 left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber
402 where booksellerid=? and (quantity > quantityreceived or
403 quantityreceived is NULL) and datecancellationprinted is NULL ";
405 if (C4::Context->preference("IndependantBranches")) {
406 my $userenv = C4::Context->userenv;
407 unless ($userenv->{flags} == 1){
408 $strsth .= " and (borrowers.branchcode = '".$userenv->{branch}."' or borrowers.branchcode ='')";
411 $strsth.=" group by basketno order by aqbasket.basketno";
412 my $sth=$dbh->prepare($strsth);
413 $sth->execute($supplierid);
415 while (my $data=$sth->fetchrow_hashref){
416 push(@results,$data);
419 return (scalar(@results),\@results);
424 ($order, $ordernumber) = &getorder($biblioitemnumber, $biblionumber);
426 Looks up the order with the given biblionumber and biblioitemnumber.
428 Returns a two-element array. C<$ordernumber> is the order number.
429 C<$order> is a reference-to-hash describing the order; its keys are
430 fields from the biblio, biblioitems, aqorders, and aqorderbreakdown
431 tables of the Koha database.
437 my $dbh = C4::Context->dbh;
438 my $sth=$dbh->prepare("Select ordernumber from aqorders where biblionumber=? and biblioitemnumber=?");
439 $sth->execute($bib,$bi);
440 # FIXME - Use fetchrow_array(), since we're only interested in the one
442 my $ordnum=$sth->fetchrow_hashref;
444 my $order=getsingleorder($ordnum->{'ordernumber'});
445 return ($order,$ordnum->{'ordernumber'});
450 $order = &getsingleorder($ordernumber);
452 Looks up an order by order number.
454 Returns a reference-to-hash describing the order. The keys of
455 C<$order> are fields from the biblio, biblioitems, aqorders, and
456 aqorderbreakdown tables of the Koha database.
462 my $dbh = C4::Context->dbh;
463 my $sth=$dbh->prepare("Select * from biblio,biblioitems,aqorders left join aqorderbreakdown
464 on aqorders.ordernumber=aqorderbreakdown.ordernumber
465 where aqorders.ordernumber=?
466 and biblio.biblionumber=aqorders.biblionumber and
467 biblioitems.biblioitemnumber=aqorders.biblioitemnumber");
468 $sth->execute($ordnum);
469 my $data=$sth->fetchrow_hashref;
476 ($count, @results) = &getallorders($booksellerid);
478 Looks up all of the pending orders from the supplier with the given
479 bookseller ID. Ignores cancelled and completed orders.
481 C<$count> is the number of elements in C<@results>. C<@results> is an
482 array of references-to-hash. The keys of each element are fields from
483 the aqorders, biblio, and biblioitems tables of the Koha database.
485 C<@results> is sorted alphabetically by book title.
490 #gets all orders from a certain supplier, orders them alphabetically
492 my $dbh = C4::Context->dbh;
494 my $strsth="Select *,aqorders.title as suggestedtitle,biblio.title as truetitle from aqorders,biblio,biblioitems,aqbasket,aqbooksellers ";
495 $strsth .= ",borrowers " if (C4::Context->preference("IndependantBranches"));
496 $strsth .=" where aqorders.basketno=aqbasket.basketno and aqbasket.booksellerid=aqbooksellers.id and biblio.biblionumber=aqorders.biblionumber ";
497 $strsth .= " and aqbasket.authorisedby=borrowers.borrowernumber" if (C4::Context->preference("IndependantBranches"));
498 $strsth.=" and booksellerid=? and (cancelledby is NULL or cancelledby = '')
499 and (quantityreceived < quantity or quantityreceived is NULL)
500 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
501 aqorders.biblioitemnumber ";
502 if (C4::Context->preference("IndependantBranches")) {
503 my $userenv = C4::Context->userenv;
504 unless ($userenv->{flags} == 1){
505 $strsth .= " and (borrowers.branchcode = '".$userenv->{branch}."' or borrowers.branchcode ='')";
508 $strsth .= " group by aqorders.biblioitemnumber order by biblio.title";
509 my $sth=$dbh->prepare($strsth);
510 $sth->execute($supplierid);
511 while (my $data=$sth->fetchrow_hashref){
512 push(@results,$data);
515 return(scalar(@results),@results);
517 =item getsupplierlistwithlateorders
519 %results = &getsupplierlistwithlateorders;
521 Searches for suppliers with late orders.
525 sub getsupplierlistwithlateorders {
527 my $dbh = C4::Context->dbh;
528 #FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so
529 #should be tested with other DBMs
532 my $dbdriver = C4::Context->config("db_scheme")||"mysql";
533 if ($dbdriver eq "mysql"){
534 $strsth="SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
535 FROM aqorders, aqbasket
536 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
537 WHERE aqorders.basketno = aqbasket.basketno AND
538 (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY) AND (datereceived = '' or datereceived is null))
541 $strsth="SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
542 FROM aqorders, aqbasket
543 LEFT JOIN aqbooksellers ON aqbasket.aqbooksellerid = aqbooksellers.id
544 WHERE aqorders.basketno = aqbasket.basketno AND
545 (closedate < (CURDATE( )-(INTERVAL $delay DAY))) AND (datereceived = '' or datereceived is null))
548 # warn "C4::Acquisition getsupplierlistwithlateorders : ".$strsth;
549 my $sth = $dbh->prepare($strsth);
552 while (my ($id,$name) = $sth->fetchrow) {
553 $supplierlist{$id} = $name;
555 return %supplierlist;
560 %results = &getlateorders;
562 Searches for suppliers with late orders.
568 my $supplierid = shift;
571 my $dbh = C4::Context->dbh;
572 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
574 my $dbdriver = C4::Context->config("db_scheme")||"mysql";
576 if ($dbdriver eq "mysql"){
577 $strsth ="SELECT aqbasket.basketno,
578 DATE(aqbasket.closedate) as orderdate, aqorders.quantity, aqorders.rrp as unitpricesupplier,aqorders.ecost as unitpricelib,
579 aqorders.quantity * aqorders.rrp as subtotal, aqbookfund.bookfundname as budget, borrowers.branchcode as branch,
580 aqbooksellers.name as supplier,
581 biblio.title, biblio.author, biblioitems.publishercode as publisher, biblioitems.publicationyear,
582 DATEDIFF(CURDATE( ),closedate) AS latesince
585 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber
586 ) LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber
587 ) LEFT JOIN aqbookfund on aqorderbreakdown.bookfundid = aqbookfund.bookfundid
588 ),(aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
589 WHERE aqorders.basketno = aqbasket.basketno AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
590 AND ((datereceived = '' OR datereceived is null) OR (aqorders.quantityreceived < aqorders.quantity) ) ";
591 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
592 $strsth .= " AND borrowers.branchcode like \'".$branch."\'" if ($branch);
593 $strsth .= " AND borrowers.branchcode like \'".C4::Context->userenv->{branch}."\'" if (C4::Context->preference("IndependantBranches") && C4::Context->userenv && C4::Context->userenv->{flags}!=1);
594 $strsth .= " ORDER BY latesince,basketno,borrowers.branchcode, supplier";
596 $strsth ="SELECT aqbasket.basketno,
597 DATE(aqbasket.closedate) as orderdate,
598 aqorders.quantity, aqorders.rrp as unitpricesupplier,aqorders.ecost as unitpricelib, aqorders.quantity * aqorders.rrp as subtotal
599 aqbookfund.bookfundname as budget, borrowers.branchcode as branch,
600 aqbooksellers.name as supplier,
601 biblio.title, biblio.author, biblioitems.publishercode as publisher, biblioitems.publicationyear,
602 (CURDATE - closedate) AS latesince
605 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber
606 ) LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber
607 ) LEFT JOIN aqbookfund on aqorderbreakdown.bookfundid = aqbookfund.bookfundid
608 ),(aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
609 WHERE aqorders.basketno = aqbasket.basketno AND (closedate < (CURDATE -(INTERVAL $delay DAY))
610 AND ((datereceived = '' OR datereceived is null) OR (aqorders.quantityreceived < aqorders.quantity) ) ";
611 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
612 $strsth .= " AND borrowers.branchcode like \'".$branch."\'" if ($branch);
613 $strsth .= " AND borrowers.branchcode like \'".C4::Context->userenv->{branch}."\'" if (C4::Context->preference("IndependantBranches") && C4::Context->userenv->{flags}!=1);
614 $strsth .= " ORDER BY latesince,basketno,borrowers.branchcode, supplier";
616 # warn "C4::Acquisition : getlateorders SQL:".$strsth;
617 my $sth = $dbh->prepare($strsth);
621 while (my $data = $sth->fetchrow_hashref) {
622 $data->{hilighted}=$hilighted if ($hilighted>0);
623 push @results, $data;
624 $hilighted= -$hilighted;
627 return(scalar(@results),@results);
632 #gets all orders from a certain supplier, orders them alphabetically
634 my $dbh = C4::Context->dbh;
636 my $sth=$dbh->prepare("Select * from aqorders,biblio,biblioitems where booksellerid=?
637 and (cancelledby is NULL or cancelledby = '')
638 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
639 aqorders.biblioitemnumber and
640 aqorders.quantityreceived>0
641 and aqorders.datereceived >=now()
642 group by aqorders.biblioitemnumber
645 $sth->execute($supid);
646 while (my $data=$sth->fetchrow_hashref){
647 push(@results,$data);
650 return(scalar(@results),@results);
655 ($count, @results) = &ordersearch($search, $biblionumber, $complete);
659 C<$search> may take one of several forms: if it is an ISBN,
660 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
661 order number, C<&ordersearch> returns orders with that order number
662 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
663 to be a space-separated list of search terms; in this case, all of the
664 terms must appear in the title (matching the beginning of title
667 If C<$complete> is C<yes>, the results will include only completed
668 orders. In any case, C<&ordersearch> ignores cancelled orders.
670 C<&ordersearch> returns an array. C<$count> is the number of elements
671 in C<@results>. C<@results> is an array of references-to-hash with the
689 my ($search,$id,$biblio,$catview) = @_;
690 my $dbh = C4::Context->dbh;
691 my @data = split(' ',$search);
692 my @searchterms = ($id);
693 map { push(@searchterms,"$_%","% $_%") } @data;
694 push(@searchterms,$search,$search,$biblio);
695 my $sth=$dbh->prepare("Select biblio.*,biblioitems.*,aqorders.*,aqbasket.*,biblio.title from aqorders,biblioitems,biblio,aqbasket
696 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber and
697 aqorders.basketno = aqbasket.basketno
698 and aqbasket.booksellerid = ?
699 and biblio.biblionumber=aqorders.biblionumber
700 and ((datecancellationprinted is NULL)
701 or (datecancellationprinted = '0000-00-00'))
703 .(join(" and ",map { "(biblio.title like ? or biblio.title like ?)" } @data))
704 .") or biblioitems.isbn=? or (aqorders.ordernumber=? and aqorders.biblionumber=?)) "
705 .(($catview ne 'yes')?" and (quantityreceived < quantity or quantityreceived is NULL)":"")
706 ." group by aqorders.ordernumber");
707 $sth->execute(@searchterms);
709 my $sth2=$dbh->prepare("Select * from biblio where biblionumber=?");
710 my $sth3=$dbh->prepare("Select * from aqorderbreakdown where ordernumber=?");
711 while (my $data=$sth->fetchrow_hashref){
712 $sth2->execute($data->{'biblionumber'});
713 my $data2=$sth2->fetchrow_hashref;
714 $data->{'author'}=$data2->{'author'};
715 $data->{'seriestitle'}=$data2->{'seriestitle'};
716 $sth3->execute($data->{'ordernumber'});
717 my $data3=$sth3->fetchrow_hashref;
718 $data->{'branchcode'}=$data3->{'branchcode'};
719 $data->{'bookfundid'}=$data3->{'bookfundid'};
720 push(@results,$data);
725 return(scalar(@results),@results);
730 my ($title,$author,$name,$from_placed_on,$to_placed_on)=@_;
734 # don't run the query if there are no parameters (list would be too long for sure !
735 if ($title || $author || $name || $from_placed_on || $to_placed_on) {
736 my $dbh= C4::Context->dbh;
737 my $query = "select biblio.title,biblio.author,aqorders.basketno,name,aqbasket.creationdate,aqorders.datereceived, aqorders.quantity, aqorders.ecost from aqorders,aqbasket,aqbooksellers,biblio";
739 $query .= ",borrowers " if (C4::Context->preference("IndependantBranches"));
740 $query .=" where aqorders.basketno=aqbasket.basketno and aqbasket.booksellerid=aqbooksellers.id and biblio.biblionumber=aqorders.biblionumber ";
741 $query .= " and aqbasket.authorisedby=borrowers.borrowernumber" if (C4::Context->preference("IndependantBranches"));
742 $query .= " and biblio.title like ".$dbh->quote("%".$title."%") if $title;
743 $query .= " and biblio.author like ".$dbh->quote("%".$author."%") if $author;
744 $query .= " and name like ".$dbh->quote("%".$name."%") if $name;
745 $query .= " and creationdate >" .$dbh->quote($from_placed_on) if $from_placed_on;
746 $query .= " and creationdate<".$dbh->quote($to_placed_on) if $to_placed_on;
747 if (C4::Context->preference("IndependantBranches")) {
748 my $userenv = C4::Context->userenv;
749 if (($userenv) &&($userenv->{flags} != 1)){
750 $query .= " and (borrowers.branchcode = '".$userenv->{branch}."' or borrowers.branchcode ='')";
753 $query .=" order by booksellerid";
754 my $sth = $dbh->prepare($query);
757 while (my $line = $sth->fetchrow_hashref) {
758 $line->{count}=$cnt++;
759 $line->{toggle}=1 if $cnt %2;
760 push @order_loop, $line;
761 $line->{creationdate} = format_date($line->{creationdate});
762 $line->{datereceived} = format_date($line->{datereceived});
763 $total_qty += $line->{'quantity'};
764 $total_price += $line->{'quantity'}*$line->{'ecost'};
767 return \@order_loop,$total_qty,$total_price;;
777 ($count, @results) = &invoice($booksellerinvoicenumber);
779 Looks up orders by invoice number.
781 Returns an array. C<$count> is the number of elements in C<@results>.
782 C<@results> is an array of references-to-hash; the keys of each
783 elements are fields from the aqorders, biblio, and biblioitems tables
784 of the Koha database.
790 my $dbh = C4::Context->dbh;
792 my $sth=$dbh->prepare("Select * from aqorders,biblio,biblioitems where
793 booksellerinvoicenumber=?
794 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
795 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber");
796 $sth->execute($invoice);
797 while (my $data=$sth->fetchrow_hashref){
798 push(@results,$data);
801 return(scalar(@results),@results);
806 ($count, @results) = &bookfunds();
808 Returns a list of all book funds.
810 C<$count> is the number of elements in C<@results>. C<@results> is an
811 array of references-to-hash, whose keys are fields from the aqbookfund
812 and aqbudget tables of the Koha database. Results are ordered
813 alphabetically by book fund name.
819 my $dbh = C4::Context->dbh;
823 $strsth="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
824 =aqbudget.bookfundid and (aqbookfund.branchcode is null or aqbookfund.branchcode='' or aqbookfund.branchcode= ? )
825 group by aqbookfund.bookfundid order by bookfundname";
827 $strsth="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
829 group by aqbookfund.bookfundid order by bookfundname";
831 my $sth=$dbh->prepare($strsth);
833 $sth->execute($branch);
838 while (my $data=$sth->fetchrow_hashref){
839 push(@results,$data);
842 return(scalar(@results),@results);
845 =item bookfundbreakdown
847 returns the total comtd & spent for a given bookfund
848 used in acqui-home.pl
852 sub bookfundbreakdown {
854 my $dbh = C4::Context->dbh;
855 my $sth=$dbh->prepare("Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription
856 from aqorders,aqorderbreakdown where bookfundid=? and
857 aqorders.ordernumber=aqorderbreakdown.ordernumber
858 and (datecancellationprinted is NULL or
859 datecancellationprinted='0000-00-00')");
863 while (my $data=$sth->fetchrow_hashref){
864 if ($data->{'subscription'} == 1){
865 $spent+=$data->{'quantity'}*$data->{'unitprice'};
867 my $leftover=$data->{'quantity'}-$data->{'quantityreceived'};
868 $comtd+=($data->{'ecost'})*$leftover;
869 $spent+=($data->{'unitprice'})*$data->{'quantityreceived'};
873 return($spent,$comtd);
880 $foreignprice = &curconvert($currency, $localprice);
882 Converts the price C<$localprice> to foreign currency C<$currency> by
883 dividing by the exchange rate, and returns the result.
885 If no exchange rate is found, C<&curconvert> assumes the rate is one
891 my ($currency,$price)=@_;
892 my $dbh = C4::Context->dbh;
893 my $sth=$dbh->prepare("Select rate from currency where currency=?");
894 $sth->execute($currency);
895 my $cur=($sth->fetchrow_array())[0];
900 return($price / $cur);
905 ($count, $currencies) = &getcurrencies();
907 Returns the list of all known currencies.
909 C<$count> is the number of elements in C<$currencies>. C<$currencies>
910 is a reference-to-array; its elements are references-to-hash, whose
911 keys are the fields from the currency table in the Koha database.
916 my $dbh = C4::Context->dbh;
917 my $sth=$dbh->prepare("Select * from currency");
920 while (my $data=$sth->fetchrow_hashref){
921 push(@results,$data);
924 return(scalar(@results),\@results);
927 =item updatecurrencies
929 &updatecurrencies($currency, $newrate);
931 Sets the exchange rate for C<$currency> to be C<$newrate>.
935 sub updatecurrencies {
936 my ($currency,$rate)=@_;
937 my $dbh = C4::Context->dbh;
938 my $sth=$dbh->prepare("update currency set rate=? where currency=?");
939 $sth->execute($rate,$currency);
951 ($count, @results) = &bookseller($searchstring);
953 Looks up a book seller. C<$searchstring> may be either a book seller
954 ID, or a string to look for in the book seller's name.
956 C<$count> is the number of elements in C<@results>. C<@results> is an
957 array of references-to-hash, whose keys are the fields of of the
958 aqbooksellers table in the Koha database.
963 my ($searchstring)=@_;
964 my $dbh = C4::Context->dbh;
965 my $sth=$dbh->prepare("Select * from aqbooksellers where name like ? or id = ?");
966 $sth->execute("$searchstring%",$searchstring);
968 while (my $data=$sth->fetchrow_hashref){
969 push(@results,$data);
972 return(scalar(@results),@results);
977 ($count, $results) = &breakdown($ordernumber);
979 Looks up an order by order ID, and returns its breakdown.
981 C<$count> is the number of elements in C<$results>. C<$results> is a
982 reference-to-array; its elements are references-to-hash, whose keys
983 are the fields of the aqorderbreakdown table in the Koha database.
989 my $dbh = C4::Context->dbh;
990 my $sth=$dbh->prepare("Select * from aqorderbreakdown where ordernumber=?");
993 while (my $data=$sth->fetchrow_hashref){
994 push(@results,$data);
997 return(scalar(@results),\@results);
1002 &updatesup($bookseller);
1004 Updates the information for a given bookseller. C<$bookseller> is a
1005 reference-to-hash whose keys are the fields of the aqbooksellers table
1006 in the Koha database. It must contain entries for all of the fields.
1007 The entry to modify is determined by C<$bookseller-E<gt>{id}>.
1009 The easiest way to get all of the necessary fields is to look up a
1010 book seller with C<&booksellers>, modify what's necessary, then call
1011 C<&updatesup> with the result.
1017 my $dbh = C4::Context->dbh;
1018 my $sth=$dbh->prepare("Update aqbooksellers set
1019 name=?,address1=?,address2=?,address3=?,address4=?,postal=?,
1020 phone=?,fax=?,url=?,contact=?,contpos=?,contphone=?,contfax=?,contaltphone=?,
1021 contemail=?,contnotes=?,active=?,
1022 listprice=?, invoiceprice=?,gstreg=?, listincgst=?,
1023 invoiceincgst=?, specialty=?,discount=?,invoicedisc=?,
1026 $sth->execute($data->{'name'},$data->{'address1'},$data->{'address2'},
1027 $data->{'address3'},$data->{'address4'},$data->{'postal'},$data->{'phone'},
1028 $data->{'fax'},$data->{'url'},$data->{'contact'},$data->{'contpos'},
1029 $data->{'contphone'},$data->{'contfax'},$data->{'contaltphone'},
1030 $data->{'contemail'},
1031 $data->{'contnote'},$data->{'active'},$data->{'listprice'},
1032 $data->{'invoiceprice'},$data->{'gstreg'},$data->{'listincgst'},
1033 $data->{'invoiceincgst'},$data->{'specialty'},$data->{'discount'},
1034 $data->{'invoicedisc'},$data->{'nocalc'},$data->{'id'});
1040 $id = &insertsup($bookseller);
1042 Creates a new bookseller. C<$bookseller> is a reference-to-hash whose
1043 keys are the fields of the aqbooksellers table in the Koha database.
1044 All fields must be present.
1046 Returns the ID of the newly-created bookseller.
1052 my $dbh = C4::Context->dbh;
1053 my $sth=$dbh->prepare("Select max(id) from aqbooksellers");
1055 my $data2=$sth->fetchrow_hashref;
1057 $data2->{'max(id)'}++;
1058 $sth=$dbh->prepare("Insert into aqbooksellers (id) values (?)");
1059 $sth->execute($data2->{'max(id)'});
1061 $data->{'id'}=$data2->{'max(id)'};
1063 return($data->{'id'});
1066 END { } # module clean-up code here (global destructor)
1075 Koha Developement team <info@koha.org>