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
25 use vars qw($VERSION @ISA @EXPORT);
27 # set the version for version checking
28 $VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); };
32 C4::Bookfund - Koha functions for dealing with bookfund, currency & money.
40 the functions in this modules deal with bookfund, currency and money.
41 They allow to get and/or set some informations for a specific budget or currency.
47 &GetBookFund &GetBookFunds &GetBookFundsId &GetBookFundBreakdown &GetCurrencies
49 &ModBookFund &ModCurrencies
62 #-------------------------------------------------------------#
68 $dataaqbookfund = &GetBookFund($bookfundid);
70 this function get the bookfundid, bookfundname, the bookfundgroup, the branchcode
71 from aqbookfund table for bookfundid given on input arg.
73 C<$dataaqbookfund> is a hashref full of bookfundid, bookfundname, bookfundgroup,
81 my $bookfundid = shift;
82 my $dbh = C4::Context->dbh;
92 my $sth=$dbh->prepare($query);
93 $sth->execute($bookfundid);
94 return $sth->fetchrow_hashref;
102 $sth = &GetBookFundsId
103 Read on aqbookfund table and execute a simple SQL query.
106 $sth->execute. Don't forget to fetch row from the database after using
107 this function by using, for example, $sth->fetchrow_hashref;
109 C<@results> is an array of id existing on the database.
116 my @bookfundids_loop;
117 my $dbh= C4::Context->dbh;
122 my $sth = $dbh->prepare($query);
127 #-------------------------------------------------------------#
133 @results = &GetBookFunds;
135 Returns a list of all book funds.
137 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
138 alphabetically by book fund name.
146 my $dbh = C4::Context->dbh;
147 my $userenv = C4::Context->userenv;
148 my $branch = $userenv->{branch};
154 FROM aqbookfund,aqbudget
155 WHERE aqbookfund.bookfundid=aqbudget.bookfundid
158 AND (aqbookfund.branchcode IS NULL OR aqbookfund.branchcode='' OR aqbookfund.branchcode= ? )
159 GROUP BY aqbookfund.bookfundid ORDER BY bookfundname";
166 WHERE aqbookfund.bookfundid=aqbudget.bookfundid
169 GROUP BY aqbookfund.bookfundid ORDER BY bookfundname
172 my $sth = $dbh->prepare($strsth);
174 $sth->execute($branch);
180 while ( my $data = $sth->fetchrow_hashref ) {
181 push( @results, $data );
187 #-------------------------------------------------------------#
193 @currencies = &GetCurrencies;
195 Returns the list of all known currencies.
197 C<$currencies> is a array; its elements are references-to-hash, whose
198 keys are the fields from the currency table in the Koha database.
205 my $dbh = C4::Context->dbh;
210 my $sth = $dbh->prepare($query);
213 while ( my $data = $sth->fetchrow_hashref ) {
214 push( @results, $data );
220 #-------------------------------------------------------------#
222 =head3 GetBookFundBreakdown
226 ( $spent, $comtd ) = &GetBookFundBreakdown( $id, $year, $start, $end );
228 returns the total comtd & spent for a given bookfund, and a given year
229 used in acqui-home.pl
235 sub GetBookFundBreakdown {
236 my ( $id, $year, $start, $end ) = @_;
237 my $dbh = C4::Context->dbh;
239 # if no start/end dates given defaut to everything
241 $start = '0000-00-00';
245 # do a query for spent totals.
247 SELECT quantity,datereceived,freight,unitprice,listprice,ecost,
248 quantityreceived,subscription
250 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
252 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
253 AND ((datereceived >= ? and datereceived < ?) OR (budgetdate >= ? and budgetdate < ?))
255 my $sth = $dbh->prepare($query);
256 $sth->execute( $id, $start, $end, $start, $end );
259 while ( my $data = $sth->fetchrow_hashref ) {
260 if ( $data->{'subscription'} == 1 ) {
261 $spent += $data->{'quantity'} * $data->{'unitprice'};
265 my $leftover = $data->{'quantity'} - $data->{'quantityreceived'};
266 $spent += ( $data->{'unitprice'} ) * $data->{'quantityreceived'};
271 # then do a seperate query for commited totals, (pervious single query was
272 # returning incorrect comitted results.
275 SELECT quantity,datereceived,freight,unitprice,
276 listprice,ecost,quantityreceived AS qrev,
277 subscription,biblio.title,itemtype,aqorders.biblionumber,
278 aqorders.booksellerinvoicenumber,
279 quantity-quantityreceived AS tleft,
280 aqorders.ordernumber AS ordnum,entrydate,budgetdate,
281 booksellerid,aqbasket.basketno
282 FROM aqorderbreakdown,
285 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
287 AND aqorders.ordernumber=aqorderbreakdown.ordernumber
288 AND aqorders.basketno=aqbasket.basketno
289 AND (budgetdate >= ? AND budgetdate < ?)
290 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
293 my $sth = $dbh->prepare($query);
294 $sth->execute( $id, $start, $end );
299 while ( my $data = $sth->fetchrow_hashref ) {
300 my $left = $data->{'tleft'};
301 if ( !$left || $left eq '' ) {
302 $left = $data->{'quantity'};
304 if ( $left && $left > 0 ) {
305 my $subtotal = $left * $data->{'ecost'};
306 $data->{subtotal} = $subtotal;
307 $data->{'left'} = $left;
313 return ( $spent, $comtd );
320 &NewBookFund(bookfundid, bookfundname, branchcode);
322 this function create a new bookfund into the database.
329 my ($bookfundid, $bookfundname, $branchcode) = @_;
330 $branchcode = undef unless $branchcode;
331 my $dbh = C4::Context->dbh;
335 (bookfundid, bookfundname, branchcode)
339 my $sth=$dbh->prepare($query);
340 $sth->execute($bookfundid,$bookfundname,$branchcode);
343 #-------------------------------------------------------------#
349 &ModBookFund($bookfundname,$branchcode,$bookfundid);
350 this function update the bookfundname and the branchcode on aqbookfund table
358 my ($bookfundname,$branchcode,$bookfundid) = @_;
359 my $dbh = C4::Context->dbh;
362 SET bookfundname = ?,
366 my $sth=$dbh->prepare($query);
367 $sth->execute($bookfundname,$branchcode,$bookfundid);
368 # budgets depending on a bookfund must have the same branchcode
369 # if the bookfund branchcode is set
370 if (defined $branchcode) {
375 $sth=$dbh->prepare($query);
376 $sth->execute($branchcode);
380 #-------------------------------------------------------------#
382 =head3 SearchBookFund
385 @results = SearchBookFund(
386 $bookfundid,$filter,$filter_bookfundid,
387 $filter_bookfundname,$filter_branchcode);
389 this function searchs among the bookfunds corresponding to our filtering rules.
396 my $dbh = C4::Context->dbh;
399 $filter_bookfundname,
414 if ($filter_bookfundid) {
415 $query.= "AND bookfundid = ?";
416 push @bindings, $filter_bookfundid;
418 if ($filter_bookfundname) {
419 $query.= "AND bookfundname like ?";
420 push @bindings, '%'.$filter_bookfundname.'%';
422 if ($filter_branchcode) {
423 $query.= "AND branchcode = ?";
424 push @bindings, $filter_branchcode;
427 $query.= "ORDER BY bookfundid";
429 my $sth = $dbh->prepare($query);
430 $sth->execute(@bindings);
432 while (my $row = $sth->fetchrow_hashref) {
438 #-------------------------------------------------------------#
444 &ModCurrencies($currency, $newrate);
446 Sets the exchange rate for C<$currency> to be C<$newrate>.
453 my ( $currency, $rate ) = @_;
454 my $dbh = C4::Context->dbh;
460 my $sth = $dbh->prepare($query);
461 $sth->execute( $rate, $currency );
464 #-------------------------------------------------------------#
470 $number = Countbookfund($bookfundid);
472 this function count the number of bookfund with id given on input arg.
474 the result of the SQL query as a number.
481 my $bookfundid = shift;
482 my $dbh = C4::Context->dbh;
488 my $sth = $dbh->prepare($query);
489 $sth->execute($bookfundid);
490 return $sth->fetchrow;
494 #-------------------------------------------------------------#
496 =head3 ConvertCurrency
500 $foreignprice = &ConvertCurrency($currency, $localprice);
502 Converts the price C<$localprice> to foreign currency C<$currency> by
503 dividing by the exchange rate, and returns the result.
505 If no exchange rate is found, C<&ConvertCurrency> assumes the rate is one
512 sub ConvertCurrency {
513 my ( $currency, $price ) = @_;
514 my $dbh = C4::Context->dbh;
520 my $sth = $dbh->prepare($query);
521 $sth->execute($currency);
522 my $cur = ( $sth->fetchrow_array() )[0];
526 return ( $price / $cur );
529 #-------------------------------------------------------------#
535 &DelBookFund($bookfundid);
536 this function delete a bookfund which has $bokfundid as parameter on aqbookfund table and delete the approriate budget.
543 my $bookfundid = shift;
544 my $dbh = C4::Context->dbh;
546 DELETE FROM aqbookfund
549 my $sth=$dbh->prepare($query);
550 $sth->execute($bookfundid);
553 DELETE FROM aqbudget where bookfundid=?
555 $sth=$dbh->prepare($query);
556 $sth->execute($bookfundid);
560 END { } # module clean-up code here (global destructor)
570 Koha Developement team <info@koha.org>