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
60 #-------------------------------------------------------------#
64 $dataaqbookfund = &GetBookFund($bookfundid);
66 this function get the bookfundid, bookfundname, the bookfundgroup, the branchcode
67 from aqbookfund table for bookfundid given on input arg.
69 C<$dataaqbookfund> is a hashref full of bookfundid, bookfundname, bookfundgroup,
75 my $bookfundid = shift;
76 my $branchcode = shift;
77 $branchcode=($branchcode?$branchcode:'');
78 my $dbh = C4::Context->dbh;
88 my $sth=$dbh->prepare($query);
89 $sth->execute($bookfundid,$branchcode);
90 my $data=$sth->fetchrow_hashref;
97 $sth = &GetBookFundsId
98 Read on aqbookfund table and execute a simple SQL query.
101 $sth->execute. Don't forget to fetch row from the database after using
102 this function by using, for example, $sth->fetchrow_hashref;
104 C<@results> is an array of id existing on the database.
109 my @bookfundids_loop;
110 my $dbh= C4::Context->dbh;
112 SELECT bookfundid,branchcode
115 my $sth = $dbh->prepare($query);
120 #-------------------------------------------------------------#
124 @results = &GetBookFunds;
126 Returns a list of all book funds.
128 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
129 alphabetically by book fund name.
135 my $dbh = C4::Context->dbh;
136 my $userenv = C4::Context->userenv;
139 if ( $branch ne '' ) {
142 FROM aqbookfund,aqbudget
143 WHERE aqbookfund.bookfundid=aqbudget.bookfundid
146 AND (aqbookfund.branchcode='' OR aqbookfund.branchcode= ? )
147 GROUP BY aqbookfund.bookfundid ORDER BY bookfundname";
154 WHERE aqbookfund.bookfundid=aqbudget.bookfundid
157 GROUP BY aqbookfund.bookfundid ORDER BY bookfundname
160 my $sth = $dbh->prepare($strsth);
161 if ( $branch ne '' ) {
162 $sth->execute($branch);
168 while ( my $data = $sth->fetchrow_hashref ) {
169 push( @results, $data );
175 #-------------------------------------------------------------#
179 @currencies = &GetCurrencies;
181 Returns the list of all known currencies.
183 C<$currencies> is a array; its elements are references-to-hash, whose
184 keys are the fields from the currency table in the Koha database.
189 my $dbh = C4::Context->dbh;
194 my $sth = $dbh->prepare($query);
197 while ( my $data = $sth->fetchrow_hashref ) {
198 push( @results, $data );
204 #-------------------------------------------------------------#
206 =head3 GetBookFundBreakdown
208 ( $spent, $comtd ) = &GetBookFundBreakdown( $id, $year, $start, $end );
210 returns the total comtd & spent for a given bookfund, and a given year
211 used in acqui-home.pl
215 sub GetBookFundBreakdown {
216 my ( $id, $year, $start, $end ) = @_;
217 my $dbh = C4::Context->dbh;
219 # if no start/end dates given defaut to everything
221 $start = '0000-00-00';
225 # do a query for spent totals.
227 SELECT quantity,datereceived,freight,unitprice,listprice,ecost,
228 quantityreceived,subscription
230 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
232 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
233 AND ((datereceived >= ? and datereceived < ?) OR (budgetdate >= ? and budgetdate < ?))
235 my $sth = $dbh->prepare($query);
236 $sth->execute( $id, $start, $end, $start, $end );
239 while ( my $data = $sth->fetchrow_hashref ) {
240 if ( $data->{'subscription'} == 1 ) {
241 $spent += $data->{'quantity'} * $data->{'unitprice'};
245 my $leftover = $data->{'quantity'} - ($data->{'quantityreceived'}?$data->{'quantityreceived'}:0);
246 $spent += ( $data->{'unitprice'} ) * ($data->{'quantityreceived'}?$data->{'quantityreceived'}:0);
251 # then do a seperate query for commited totals, (pervious single query was
252 # returning incorrect comitted results.
255 SELECT quantity,datereceived,freight,unitprice,
256 listprice,ecost,quantityreceived AS qrev,
257 subscription,title,itemtype,aqorders.biblionumber,
258 aqorders.booksellerinvoicenumber,
259 quantity-quantityreceived AS tleft,
260 aqorders.ordernumber AS ordnum,entrydate,budgetdate,
261 booksellerid,aqbasket.basketno
262 FROM aqorderbreakdown,
265 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=aqorders.biblioitemnumber
267 AND aqorders.ordernumber=aqorderbreakdown.ordernumber
268 AND aqorders.basketno=aqbasket.basketno
269 AND (budgetdate >= ? AND budgetdate < ?)
270 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
273 $sth = $dbh->prepare($query);
274 $sth->execute( $id, $start, $end );
279 while ( my $data = $sth->fetchrow_hashref ) {
280 my $left = $data->{'tleft'};
281 if ( !$left || $left eq '' ) {
282 $left = $data->{'quantity'};
284 if ( $left && $left > 0 ) {
285 my $subtotal = $left * $data->{'ecost'};
286 $data->{subtotal} = $subtotal;
287 $data->{'left'} = $left;
293 return ( $spent, $comtd );
298 &NewBookFund(bookfundid, bookfundname, branchcode);
300 this function create a new bookfund into the database.
305 my ($bookfundid, $bookfundname, $branchcode) = @_;
306 $branchcode = undef unless $branchcode;
307 my $dbh = C4::Context->dbh;
311 (bookfundid, bookfundname, branchcode)
315 my $sth=$dbh->prepare($query);
316 $sth->execute($bookfundid,$bookfundname,"$branchcode");
319 #-------------------------------------------------------------#
323 &ModBookFund($bookfundname,$branchcode,$bookfundid);
324 this function update the bookfundname and the branchcode on aqbookfund table
330 my ($bookfundname,$bookfundid,$branchcode) = @_;
331 my $dbh = C4::Context->dbh;
338 warn "name : $bookfundname";
339 my $sth=$dbh->prepare($query);
340 $sth->execute($bookfundname,$bookfundid,"$branchcode");
341 # budgets depending on a bookfund must have the same branchcode
342 # if the bookfund branchcode is set
343 if (defined $branchcode) {
348 $sth=$dbh->prepare($query);
349 $sth->execute($branchcode);
353 #-------------------------------------------------------------#
355 =head3 SearchBookFund
357 @results = SearchBookFund(
358 $bookfundid,$filter,$filter_bookfundid,
359 $filter_bookfundname,$filter_branchcode);
361 this function searchs among the bookfunds corresponding to our filtering rules.
366 my $dbh = C4::Context->dbh;
369 $filter_bookfundname,
384 if ($filter_bookfundid) {
385 $query.= "AND bookfundid = ?";
386 push @bindings, $filter_bookfundid;
388 if ($filter_bookfundname) {
389 $query.= "AND bookfundname like ?";
390 push @bindings, '%'.$filter_bookfundname.'%';
392 if ($filter_branchcode) {
393 $query.= "AND branchcode = ?";
394 push @bindings, $filter_branchcode;
397 $query.= "ORDER BY bookfundid";
399 my $sth = $dbh->prepare($query);
400 $sth->execute(@bindings);
402 while (my $row = $sth->fetchrow_hashref) {
408 #-------------------------------------------------------------#
412 &ModCurrencies($currency, $newrate);
414 Sets the exchange rate for C<$currency> to be C<$newrate>.
419 my ( $currency, $rate ) = @_;
420 my $dbh = C4::Context->dbh;
426 my $sth = $dbh->prepare($query);
427 $sth->execute( $rate, $currency );
430 #-------------------------------------------------------------#
434 $number = Countbookfund($bookfundid);
436 this function count the number of bookfund with id given on input arg.
438 the result of the SQL query as a number.
443 my $bookfundid = shift;
444 my $branchcode = shift;
445 my $dbh = C4::Context->dbh;
452 my $sth = $dbh->prepare($query);
453 $sth->execute($bookfundid,$branchcode);
454 return $sth->fetchrow;
458 #-------------------------------------------------------------#
460 =head3 ConvertCurrency
462 $foreignprice = &ConvertCurrency($currency, $localprice);
464 Converts the price C<$localprice> to foreign currency C<$currency> by
465 dividing by the exchange rate, and returns the result.
467 If no exchange rate is found, C<&ConvertCurrency> assumes the rate is one
472 sub ConvertCurrency {
473 my ( $currency, $price ) = @_;
474 my $dbh = C4::Context->dbh;
480 my $sth = $dbh->prepare($query);
481 $sth->execute($currency);
482 my $cur = ( $sth->fetchrow_array() )[0];
486 return ( $price / $cur );
489 #-------------------------------------------------------------#
493 &DelBookFund($bookfundid);
494 this function delete a bookfund which has $bokfundid as parameter on aqbookfund table and delete the approriate budget.
499 my $bookfundid = shift;
500 my $branchcode=shift;
501 my $dbh = C4::Context->dbh;
503 DELETE FROM aqbookfund
507 my $sth=$dbh->prepare($query);
508 $sth->execute($bookfundid,$branchcode);
511 DELETE FROM aqbudget where bookfundid=? and branchcode=?
513 $sth=$dbh->prepare($query);
514 $sth->execute($bookfundid,$branchcode);
518 END { } # module clean-up code here (global destructor)
526 Koha Developement team <info@koha.org>