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
240 ORDER BY datereceived
242 my $sth = $dbh->prepare($query);
245 my ($spent, $comtd) = (0, 0);
246 while ( my $data = $sth->fetchrow_hashref ) {
248 my $recv = $data->{'qrev'};
249 my $left = $data->{'tleft'};
250 my $ecost = $data->{'ecost'};
253 if($data->{datereceived}){
255 $spent += $recv * $data->{'unitprice'};
258 $left = $data->{quantity} if(not $recv);
260 $comtd += $left * $ecost;
265 return ( $spent, $comtd );
270 &NewBookFund(bookfundid, bookfundname, branchcode);
272 this function create a new bookfund into the database.
277 my ($bookfundid, $bookfundname, $branchcode) = @_;
278 $branchcode = undef unless $branchcode;
279 my $dbh = C4::Context->dbh;
283 (bookfundid, bookfundname, branchcode)
287 my $sth=$dbh->prepare($query);
288 $sth->execute($bookfundid,$bookfundname,"$branchcode");
291 #-------------------------------------------------------------#
295 &ModBookFund($bookfundname,$bookfundid,$current_branch, $branchcode)
297 This function updates the bookfundname and the branchcode in the aqbookfund table.
301 # FIXME: use placeholders, ->prepare(), ->execute()
304 my ($bookfundname,$bookfundid,$current_branch, $branchcode) = @_;
306 my $dbh = C4::Context->dbh;
308 my $retval = $dbh->do("
310 SET bookfundname = '$bookfundname',
311 branchcode = '$branchcode'
312 WHERE bookfundid = '$bookfundid'
313 AND branchcode = '$current_branch'
318 # budgets depending on a bookfund must have the same branchcode
320 # if the bookfund branchcode is set, and previous update is successfull, then update aqbudget.branchcode too.
321 if (defined $branchcode && $retval > 0) {
322 my $query = "UPDATE aqbudget
324 WHERE bookfundid = ? ";
326 my $sth=$dbh->prepare($query);
327 $sth->execute($branchcode, $bookfundid) ;
331 #-------------------------------------------------------------#
333 =head3 SearchBookFund
335 @results = SearchBookFund(
336 $bookfundid,$filter,$filter_bookfundid,
337 $filter_bookfundname,$filter_branchcode);
339 this function searchs among the bookfunds corresponding to our filtering rules.
344 my $dbh = C4::Context->dbh;
347 $filter_bookfundname,
362 if ($filter_bookfundid) {
363 $query.= "AND bookfundid = ?";
364 push @bindings, $filter_bookfundid;
366 if ($filter_bookfundname) {
367 $query.= "AND bookfundname like ?";
368 push @bindings, '%'.$filter_bookfundname.'%';
370 if ($filter_branchcode) {
371 $query.= "AND branchcode = ?";
372 push @bindings, $filter_branchcode;
375 $query.= "ORDER BY bookfundid";
377 my $sth = $dbh->prepare($query);
378 $sth->execute(@bindings);
380 while (my $row = $sth->fetchrow_hashref) {
386 #-------------------------------------------------------------#
390 &ModCurrencies($currency, $newrate);
392 Sets the exchange rate for C<$currency> to be C<$newrate>.
397 my ( $currency, $rate ) = @_;
398 my $dbh = C4::Context->dbh;
404 my $sth = $dbh->prepare($query);
405 $sth->execute( $rate, $currency );
408 #-------------------------------------------------------------#
412 $number = Countbookfund($bookfundid);
414 this function count the number of bookfund with id given on input arg.
416 the result of the SQL query as a number.
421 my $bookfundid = shift;
422 my $branchcode = shift;
423 my $dbh = C4::Context->dbh;
430 my $sth = $dbh->prepare($query);
431 $sth->execute($bookfundid,"$branchcode");
432 return $sth->fetchrow;
436 #-------------------------------------------------------------#
438 =head3 ConvertCurrency
440 $foreignprice = &ConvertCurrency($currency, $localprice);
442 Converts the price C<$localprice> to foreign currency C<$currency> by
443 dividing by the exchange rate, and returns the result.
445 If no exchange rate is found, C<&ConvertCurrency> assumes the rate is one
450 sub ConvertCurrency {
451 my ( $currency, $price ) = @_;
452 my $dbh = C4::Context->dbh;
458 my $sth = $dbh->prepare($query);
459 $sth->execute($currency);
460 my $cur = ( $sth->fetchrow_array() )[0];
464 return ( $price / $cur );
467 #-------------------------------------------------------------#
471 &DelBookFund($bookfundid);
472 this function delete a bookfund which has $bokfundid as parameter on aqbookfund table and delete the approriate budget.
477 my $bookfundid = shift;
478 my $branchcode=shift;
479 my $dbh = C4::Context->dbh;
481 DELETE FROM aqbookfund
485 my $sth=$dbh->prepare($query);
486 $sth->execute($bookfundid,$branchcode);
489 DELETE FROM aqbudget where bookfundid=? and branchcode=?
491 $sth=$dbh->prepare($query);
492 $sth->execute($bookfundid,$branchcode);
496 END { } # module clean-up code here (global destructor)
504 Koha Developement team <info@koha.org>