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
22 # use Smart::Comments;
24 use vars qw($VERSION @ISA @EXPORT);
26 # set the version for version checking
31 C4::Bookfund - Koha functions for dealing with bookfund, currency & money.
39 the functions in this modules deal with bookfund, currency and money.
40 They allow to get and/or set some informations for a specific budget or currency.
46 &GetBookFund &GetBookFunds &GetBookFundsId &GetBookFundBreakdown &GetCurrencies
48 &ModBookFund &ModCurrencies
59 #-------------------------------------------------------------#
63 $dataaqbookfund = &GetBookFund($bookfundid);
65 this function get the bookfundid, bookfundname, the bookfundgroup, the branchcode
66 from aqbookfund table for bookfundid given on input arg.
68 C<$dataaqbookfund> is a hashref full of bookfundid, bookfundname, bookfundgroup,
74 my $bookfundid = shift;
75 my $branchcode = shift;
76 $branchcode=($branchcode?$branchcode:'');
77 my $dbh = C4::Context->dbh;
87 my $sth=$dbh->prepare($query);
88 $sth->execute($bookfundid,$branchcode);
89 my $data=$sth->fetchrow_hashref;
96 $sth = &GetBookFundsId
97 Read on aqbookfund table and execute a simple SQL query.
100 $sth->execute. Don't forget to fetch row from the database after using
101 this function by using, for example, $sth->fetchrow_hashref;
103 C<@results> is an array of id existing on the database.
108 my @bookfundids_loop;
109 my $dbh= C4::Context->dbh;
111 SELECT bookfundid,branchcode
114 my $sth = $dbh->prepare($query);
119 #-------------------------------------------------------------#
123 @results = &GetBookFunds;
125 Returns a list of all book funds.
127 C<@results> is an array of references-to-hash, whose keys are fields from the aqbookfund and aqbudget tables of the Koha database. Results are ordered
128 alphabetically by book fund name.
134 my $dbh = C4::Context->dbh;
135 my $userenv = C4::Context->userenv;
138 if ( $branch ne '' ) {
142 LEFT JOIN aqbudget ON aqbookfund.bookfundid=aqbudget.bookfundid
143 WHERE startdate<now()
145 AND (aqbookfund.branchcode='' OR aqbookfund.branchcode= ? )
146 GROUP BY aqbookfund.bookfundid ORDER BY bookfundname";
152 LEFT JOIN aqbudget ON aqbookfund.bookfundid=aqbudget.bookfundid
153 WHERE startdate<now()
155 GROUP BY aqbookfund.bookfundid ORDER BY bookfundname
158 my $sth = $dbh->prepare($strsth);
159 if ( $branch ne '' ) {
160 $sth->execute($branch);
166 while ( my $data = $sth->fetchrow_hashref ) {
167 push( @results, $data );
173 #-------------------------------------------------------------#
177 @currencies = &GetCurrencies;
179 Returns the list of all known currencies.
181 C<$currencies> is a array; its elements are references-to-hash, whose
182 keys are the fields from the currency table in the Koha database.
187 my $dbh = C4::Context->dbh;
192 my $sth = $dbh->prepare($query);
195 while ( my $data = $sth->fetchrow_hashref ) {
196 push( @results, $data );
202 #-------------------------------------------------------------#
204 =head3 GetBookFundBreakdown
206 ( $spent, $comtd ) = &GetBookFundBreakdown( $id, $start, $end );
208 returns the total comtd & spent for a given bookfund, and a given year
209 used in acqui-home.pl
213 sub GetBookFundBreakdown {
214 my ( $id, $start, $end ) = @_;
215 my $dbh = C4::Context->dbh;
218 # if no start/end dates given defaut to everything
220 $start = '0000-00-00';
224 # do a query for spent totals.
226 SELECT quantity,datereceived,freight,unitprice,listprice,ecost,
227 quantityreceived AS qrev,subscription,title,aqorders.biblionumber,
228 aqorders.booksellerinvoicenumber,quantity-quantityreceived as tleft,
229 aqorders.ordernumber as ordnum,entrydate,budgetdate,aqbasket.booksellerid,
232 LEFT JOIN aqorderbreakdown USING (ordernumber)
233 LEFT JOIN aqbasket USING (basketno)
234 LEFT JOIN aqbudget USING (bookfundid)
236 AND (datecancellationprinted IS NULL OR datecancellationprinted = '0000-00-00')
237 AND closedate BETWEEN startdate AND enddate
238 AND creationdate > startdate
239 ORDER BY datereceived
241 my $sth = $dbh->prepare($query);
244 my ($spent, $comtd) = (0, 0);
245 while ( my $data = $sth->fetchrow_hashref ) {
247 my $recv = $data->{'qrev'};
248 my $left = $data->{'tleft'};
249 my $ecost = $data->{'ecost'};
252 if($data->{datereceived}){
254 $spent += $recv * $data->{'unitprice'};
257 $left = $data->{quantity} if(not $recv);
259 $comtd += $left * $ecost;
264 return ( $spent, $comtd );
269 &NewBookFund(bookfundid, bookfundname, branchcode);
271 this function create a new bookfund into the database.
276 my ($bookfundid, $bookfundname, $branchcode) = @_;
277 $branchcode = undef unless $branchcode;
278 my $dbh = C4::Context->dbh;
282 (bookfundid, bookfundname, branchcode)
286 my $sth=$dbh->prepare($query);
287 $sth->execute($bookfundid,$bookfundname,"$branchcode");
290 #-------------------------------------------------------------#
294 &ModBookFund($bookfundname,$bookfundid,$current_branch, $branchcode)
296 This function updates the bookfundname and the branchcode in the aqbookfund table.
300 # FIXME: use placeholders, ->prepare(), ->execute()
303 my ($bookfundname,$bookfundid,$current_branch, $branchcode) = @_;
305 my $dbh = C4::Context->dbh;
307 my $retval = $dbh->do("
309 SET bookfundname = '$bookfundname',
310 branchcode = '$branchcode'
311 WHERE bookfundid = '$bookfundid'
312 AND branchcode = '$current_branch'
317 # budgets depending on a bookfund must have the same branchcode
319 # if the bookfund branchcode is set, and previous update is successfull, then update aqbudget.branchcode too.
320 if (defined $branchcode && $retval > 0) {
321 my $query = "UPDATE aqbudget
323 WHERE bookfundid = ? ";
325 my $sth=$dbh->prepare($query);
326 $sth->execute($branchcode, $bookfundid) ;
330 #-------------------------------------------------------------#
332 =head3 SearchBookFund
334 @results = SearchBookFund(
335 $bookfundid,$filter,$filter_bookfundid,
336 $filter_bookfundname,$filter_branchcode);
338 this function searchs among the bookfunds corresponding to our filtering rules.
343 my $dbh = C4::Context->dbh;
346 $filter_bookfundname,
361 if ($filter_bookfundid) {
362 $query.= "AND bookfundid = ?";
363 push @bindings, $filter_bookfundid;
365 if ($filter_bookfundname) {
366 $query.= "AND bookfundname like ?";
367 push @bindings, '%'.$filter_bookfundname.'%';
369 if ($filter_branchcode) {
370 $query.= "AND branchcode = ?";
371 push @bindings, $filter_branchcode;
374 $query.= "ORDER BY bookfundid";
376 my $sth = $dbh->prepare($query);
377 $sth->execute(@bindings);
379 while (my $row = $sth->fetchrow_hashref) {
385 #-------------------------------------------------------------#
389 &ModCurrencies($currency, $newrate);
391 Sets the exchange rate for C<$currency> to be C<$newrate>.
396 my ( $currency, $rate ) = @_;
397 my $dbh = C4::Context->dbh;
403 my $sth = $dbh->prepare($query);
404 $sth->execute( $rate, $currency );
407 #-------------------------------------------------------------#
411 $number = Countbookfund($bookfundid);
413 this function count the number of bookfund with id given on input arg.
415 the result of the SQL query as a number.
420 my $bookfundid = shift;
421 my $branchcode = shift;
422 my $dbh = C4::Context->dbh;
429 my $sth = $dbh->prepare($query);
430 $sth->execute($bookfundid,"$branchcode");
431 return $sth->fetchrow;
435 #-------------------------------------------------------------#
437 =head3 ConvertCurrency
439 $foreignprice = &ConvertCurrency($currency, $localprice);
441 Converts the price C<$localprice> to foreign currency C<$currency> by
442 dividing by the exchange rate, and returns the result.
444 If no exchange rate is found, C<&ConvertCurrency> assumes the rate is one
449 sub ConvertCurrency {
450 my ( $currency, $price ) = @_;
451 my $dbh = C4::Context->dbh;
457 my $sth = $dbh->prepare($query);
458 $sth->execute($currency);
459 my $cur = ( $sth->fetchrow_array() )[0];
463 return ( $price / $cur );
466 #-------------------------------------------------------------#
470 &DelBookFund($bookfundid);
471 this function delete a bookfund which has $bokfundid as parameter on aqbookfund table and delete the approriate budget.
476 my $bookfundid = shift;
477 my $branchcode=shift;
478 my $dbh = C4::Context->dbh;
480 DELETE FROM aqbookfund
484 my $sth=$dbh->prepare($query);
485 $sth->execute($bookfundid,$branchcode);
488 DELETE FROM aqbudget where bookfundid=? and branchcode=?
490 $sth=$dbh->prepare($query);
491 $sth->execute($bookfundid,$branchcode);
495 END { } # module clean-up code here (global destructor)
503 Koha Developement team <info@koha.org>