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;
217 # if no start/end dates given defaut to everything
219 $start = '0000-00-00';
223 # do a query for spent totals.
225 Select distinct quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived
226 as qrev,subscription,title,itype as itemtype,aqorders.biblionumber,aqorders.booksellerinvoicenumber,
227 quantity-quantityreceived as tleft,
229 as ordnum,entrydate,budgetdate,aqbasket.booksellerid,aqbasket.basketno
231 inner join aqorderbreakdown on aqorderbreakdown.ordernumber = aqorders.ordernumber
232 inner join aqbasket on aqbasket.basketno = aqorders.basketno
233 left join items on items.biblionumber=aqorders.biblionumber
235 and (datereceived >= ? and datereceived < ?)
236 and (datecancellationprinted is NULL or
237 datecancellationprinted='0000-00-00')
238 and (closedate >= ? and closedate < ?)
240 my $sth = $dbh->prepare($query);
241 $sth->execute( $id, $start, $end, $start, $end);
244 while ( my $data = $sth->fetchrow_hashref ) {
245 if($data->{datereceived}){
246 if ( $data->{'subscription'} == 1 ) {
247 $spent += $data->{'quantity'} * $data->{'unitprice'};
250 $spent += ( $data->{'unitprice'} ) * ($data->{'qrev'}?$data->{'qrev'}:0);
256 # then do a seperate query for commited totals, (pervious single query was
257 # returning incorrect comitted results.
260 SELECT quantity,datereceived,freight,unitprice,
261 listprice,ecost,quantityreceived AS qrev,
262 subscription,title,itemtype,aqorders.biblionumber,
263 aqorders.booksellerinvoicenumber,
264 quantity-quantityreceived AS tleft,quantityreceived,
265 aqorders.ordernumber AS ordnum,entrydate,budgetdate
267 LEFT JOIN aqbasket USING (basketno)
268 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=aqorders.biblioitemnumber
269 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
271 AND (budgetdate >= ? AND budgetdate < ?)
272 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
273 AND (closedate >= ? AND closedate <= ?)
276 $sth = $dbh->prepare($query);
277 # warn "$start $end";
278 $sth->execute( $id, $start, $end , $start, $end);
282 while ( my $data = $sth->fetchrow_hashref ) {
283 if(not $data->{datereceived}){
284 my $left = $data->{'tleft'};
285 if ( !$left || $left eq '' ) {
286 $left = $data->{'quantity'};
288 if ( $left && $left > 0 ) {
289 my $subtotal = $left * $data->{'ecost'};
290 $data->{subtotal} = $subtotal;
291 $data->{'left'} = $left;
295 # use Data::Dumper; warn Dumper($data);
299 return ( $spent, $comtd );
304 &NewBookFund(bookfundid, bookfundname, branchcode);
306 this function create a new bookfund into the database.
311 my ($bookfundid, $bookfundname, $branchcode) = @_;
312 $branchcode = undef unless $branchcode;
313 my $dbh = C4::Context->dbh;
317 (bookfundid, bookfundname, branchcode)
321 my $sth=$dbh->prepare($query);
322 $sth->execute($bookfundid,$bookfundname,"$branchcode");
325 #-------------------------------------------------------------#
329 &ModBookFund($bookfundname,$bookfundid,$current_branch, $branchcode)
331 This function updates the bookfundname and the branchcode in the aqbookfund table.
335 # FIXME: use placeholders, ->prepare(), ->execute()
338 my ($bookfundname,$bookfundid,$current_branch, $branchcode) = @_;
340 my $dbh = C4::Context->dbh;
342 my $retval = $dbh->do("
344 SET bookfundname = '$bookfundname',
345 branchcode = '$branchcode'
346 WHERE bookfundid = '$bookfundid'
347 AND branchcode = '$current_branch'
352 # budgets depending on a bookfund must have the same branchcode
354 # if the bookfund branchcode is set, and previous update is successfull, then update aqbudget.branchcode too.
355 if (defined $branchcode && $retval > 0) {
356 my $query = "UPDATE aqbudget
358 WHERE bookfundid = ? ";
360 my $sth=$dbh->prepare($query);
361 $sth->execute($branchcode, $bookfundid) ;
365 #-------------------------------------------------------------#
367 =head3 SearchBookFund
369 @results = SearchBookFund(
370 $bookfundid,$filter,$filter_bookfundid,
371 $filter_bookfundname,$filter_branchcode);
373 this function searchs among the bookfunds corresponding to our filtering rules.
378 my $dbh = C4::Context->dbh;
381 $filter_bookfundname,
396 if ($filter_bookfundid) {
397 $query.= "AND bookfundid = ?";
398 push @bindings, $filter_bookfundid;
400 if ($filter_bookfundname) {
401 $query.= "AND bookfundname like ?";
402 push @bindings, '%'.$filter_bookfundname.'%';
404 if ($filter_branchcode) {
405 $query.= "AND branchcode = ?";
406 push @bindings, $filter_branchcode;
409 $query.= "ORDER BY bookfundid";
411 my $sth = $dbh->prepare($query);
412 $sth->execute(@bindings);
414 while (my $row = $sth->fetchrow_hashref) {
420 #-------------------------------------------------------------#
424 &ModCurrencies($currency, $newrate);
426 Sets the exchange rate for C<$currency> to be C<$newrate>.
431 my ( $currency, $rate ) = @_;
432 my $dbh = C4::Context->dbh;
438 my $sth = $dbh->prepare($query);
439 $sth->execute( $rate, $currency );
442 #-------------------------------------------------------------#
446 $number = Countbookfund($bookfundid);
448 this function count the number of bookfund with id given on input arg.
450 the result of the SQL query as a number.
455 my $bookfundid = shift;
456 my $branchcode = shift;
457 my $dbh = C4::Context->dbh;
464 my $sth = $dbh->prepare($query);
465 $sth->execute($bookfundid,"$branchcode");
466 return $sth->fetchrow;
470 #-------------------------------------------------------------#
472 =head3 ConvertCurrency
474 $foreignprice = &ConvertCurrency($currency, $localprice);
476 Converts the price C<$localprice> to foreign currency C<$currency> by
477 dividing by the exchange rate, and returns the result.
479 If no exchange rate is found, C<&ConvertCurrency> assumes the rate is one
484 sub ConvertCurrency {
485 my ( $currency, $price ) = @_;
486 my $dbh = C4::Context->dbh;
492 my $sth = $dbh->prepare($query);
493 $sth->execute($currency);
494 my $cur = ( $sth->fetchrow_array() )[0];
498 return ( $price / $cur );
501 #-------------------------------------------------------------#
505 &DelBookFund($bookfundid);
506 this function delete a bookfund which has $bokfundid as parameter on aqbookfund table and delete the approriate budget.
511 my $bookfundid = shift;
512 my $branchcode=shift;
513 my $dbh = C4::Context->dbh;
515 DELETE FROM aqbookfund
519 my $sth=$dbh->prepare($query);
520 $sth->execute($bookfundid,$branchcode);
523 DELETE FROM aqbudget where bookfundid=? and branchcode=?
525 $sth=$dbh->prepare($query);
526 $sth->execute($bookfundid,$branchcode);
530 END { } # module clean-up code here (global destructor)
538 Koha Developement team <info@koha.org>