C4/Bookfund.pm

   1 package C4::Bookfund;
   2
   3 # Copyright 2000-2002 Katipo Communications
   4 #
   5 # This file is part of Koha.
   6 #
   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
  10 # version.
  11 #
  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.
  15 #
  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
  19
  20
  21 use strict;
  22 # use Smart::Comments;
  23
  24 use vars qw($VERSION @ISA @EXPORT);
  25
  26 # set the version for version checking
  27 $VERSION = 3.01;
  28
  29 =head1 NAME
  30
  31 C4::Bookfund - Koha functions for dealing with bookfund, currency & money.
  32
  33 =head1 SYNOPSIS
  34
  35 use C4::Bookfund;
  36
  37 =head1 DESCRIPTION
  38
  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.
  41
  42 =cut
  43
  44 @ISA    = qw(Exporter);
  45 @EXPORT = qw(
  46     &GetBookFund &GetBookFunds &GetBookFundsId &GetBookFundBreakdown &GetCurrencies
  47     &NewBookFund
  48     &ModBookFund &ModCurrencies
  49     &SearchBookFund
  50     &Countbookfund
  51     &GetLocalCurrency
  52     &ConvertCurrency
  53     &DelBookFund
  54 );
  55
  56 =head1 FUNCTIONS
  57
  58 =cut
  59
  60 #-------------------------------------------------------------#
  61
  62 =head2 GetBookFund
  63
  64 $dataaqbookfund = &GetBookFund($bookfundid);
  65
  66 this function get the bookfundid, bookfundname, the bookfundgroup,  the branchcode
  67 from aqbookfund table for bookfundid given on input arg.
  68 return:
  69 C<$dataaqbookfund> is a hashref full of bookfundid, bookfundname, bookfundgroup,
  70 and branchcode.
  71
  72 =cut
  73
  74 sub GetBookFund {
  75     my $bookfundid = shift;
  76     my $branchcode = shift;
  77     $branchcode=($branchcode?$branchcode:'');
  78     my $dbh = C4::Context->dbh;
  79     my $query = "
  80         SELECT
  81             bookfundid,
  82             bookfundname,
  83             bookfundgroup,
  84             branchcode
  85         FROM aqbookfund
  86         WHERE bookfundid = ?
  87         AND branchcode = ?";
  88     my $sth=$dbh->prepare($query);
  89     $sth->execute($bookfundid,$branchcode);
  90     my $data=$sth->fetchrow_hashref;
  91     return $data;
  92 }
  93
  94
  95 =head3 GetBookFundsId
  96
  97 $sth = &GetBookFundsId
  98 Read on aqbookfund table and execute a simple SQL query.
  99
 100 return:
 101 $sth->execute. Don't forget to fetch row from the database after using
 102 this function by using, for example, $sth->fetchrow_hashref;
 103
 104 C<@results> is an array of id existing on the database.
 105
 106 =cut
 107
 108 sub GetBookFundsId {
 109     my @bookfundids_loop;
 110     my $dbh= C4::Context->dbh;
 111     my $query = "
 112         SELECT bookfundid,branchcode
 113         FROM aqbookfund
 114     ";
 115     my $sth = $dbh->prepare($query);
 116     $sth->execute;
 117     return $sth;
 118 }
 119
 120 #-------------------------------------------------------------#
 121
 122 =head3 GetBookFunds
 123
 124 @results = &GetBookFunds;
 125
 126 Returns a list of all book funds.
 127
 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.
 130
 131 =cut
 132
 133 sub GetBookFunds {
 134     my ($branch) = @_;
 135     my $dbh      = C4::Context->dbh;
 136     my $userenv  = C4::Context->userenv;
 137     my $strsth;
 138
 139     if ( $branch ne '' ) {
 140         $strsth = "
 141         SELECT *
 142         FROM   aqbookfund
 143         LEFT JOIN aqbudget ON aqbookfund.bookfundid=aqbudget.bookfundid
 144         WHERE  startdate<now()
 145             AND enddate>now()
 146             AND (aqbookfund.branchcode='' OR aqbookfund.branchcode= ? )
 147       GROUP BY aqbookfund.bookfundid ORDER BY bookfundname";
 148     }
 149     else {
 150         $strsth = "
 151             SELECT *
 152             FROM   aqbookfund
 153             LEFT JOIN aqbudget ON aqbookfund.bookfundid=aqbudget.bookfundid
 154             WHERE startdate<now()
 155                 AND enddate>now()
 156             GROUP BY aqbookfund.bookfundid ORDER BY bookfundname
 157         ";
 158     }
 159     my $sth = $dbh->prepare($strsth);
 160     if ( $branch ne '' ) {
 161         $sth->execute($branch);
 162     }
 163     else {
 164         $sth->execute;
 165     }
 166     my @results = ();
 167     while ( my $data = $sth->fetchrow_hashref ) {
 168         push( @results, $data );
 169     }
 170     $sth->finish;
 171     return @results;
 172 }
 173
 174 #-------------------------------------------------------------#
 175
 176 =head3 GetCurrencies
 177
 178 @currencies = &GetCurrencies;
 179
 180 Returns the list of all known currencies.
 181
 182 C<$currencies> is a array; its elements are references-to-hash, whose
 183 keys are the fields from the currency table in the Koha database.
 184
 185 =cut
 186
 187 sub GetCurrencies {
 188     my $dbh = C4::Context->dbh;
 189     my $query = "
 190         SELECT *
 191         FROM   currency
 192     ";
 193     my $sth = $dbh->prepare($query);
 194     $sth->execute;
 195     my @results = ();
 196     while ( my $data = $sth->fetchrow_hashref ) {
 197         push( @results, $data );
 198     }
 199     $sth->finish;
 200     return @results;
 201 }
 202
 203 #-------------------------------------------------------------#
 204
 205 =head3 GetLocalCurrency
 206
 207 $currency = GetLocalCurrency;
 208
 209 Returns the currency with exchange rate 1 (the local currency).
 210
 211 $currency is a reference-to-hash, whose keys are the fields from the currency
 212 table in the Koha database.
 213
 214 =cut
 215
 216 sub GetLocalCurrency {
 217     my $dbh = C4::Context->dbh;
 218
 219     my $result = $dbh->selectrow_hashref("
 220       SELECT
 221         currency, symbol, timestamp, rate
 222         FROM currency
 223         WHERE rate = 1
 224     ");
 225
 226     die "No currency found with rate 1" if ( !defined( $result ) );
 227
 228     return $result;
 229 }
 230
 231 #-------------------------------------------------------------#
 232
 233 =head3 GetBookFundBreakdown
 234
 235 ( $spent, $comtd ) = &GetBookFundBreakdown( $id, $start, $end );
 236
 237 returns the total comtd & spent for a given bookfund, and a given year
 238 used in acqui-home.pl
 239
 240 =cut
 241
 242 sub GetBookFundBreakdown {
 243     my ( $id, $start, $end ) = @_;
 244     my $dbh = C4::Context->dbh;
 245
 246     # if no start/end dates given defaut to everything
 247     if ( !$start ) {
 248         $start = '0000-00-00';
 249         $end   = 'now()';
 250     }
 251
 252     # do a query for spent totals.
 253     my $query = "
 254         SELECT quantity,datereceived,freight,unitprice,listprice,ecost,
 255                quantityreceived,subscription
 256         FROM   aqorders
 257         LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
 258         LEFT JOIN aqbookfund ON (aqorderbreakdown.bookfundid=aqbookfund.bookfundid and aqorderbreakdown.branchcode=aqbookfund.branchcode)
 259         LEFT JOIN aqbudget ON (aqbudget.bookfundid=aqbookfund.bookfundid and aqbudget.branchcode=aqbookfund.branchcode)
 260         WHERE  aqorderbreakdown.bookfundid=?
 261             AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
 262             AND ((budgetdate >= ? and budgetdate < ?) OR (startdate>=? and enddate<=?))
 263     ";
 264     my $sth = $dbh->prepare($query);
 265     $sth->execute( $id, $start, $end, $start, $end );
 266
 267     my ($spent) = 0;
 268     while ( my $data = $sth->fetchrow_hashref ) {
 269         if ( $data->{'subscription'} == 1 ) {
 270             $spent += $data->{'quantity'} * $data->{'unitprice'};
 271         }
 272         else {
 273             $spent += ( $data->{'unitprice'} ) * ($data->{'quantityreceived'}?$data->{'quantityreceived'}:0);
 274
 275         }
 276     }
 277
 278     # then do a seperate query for commited totals, (pervious single query was
 279     # returning incorrect comitted results.
 280
 281     $query = "
 282         SELECT  quantity,datereceived,freight,unitprice,
 283                 listprice,ecost,quantityreceived AS qrev,
 284                 subscription,title,itemtype,aqorders.biblionumber,
 285                 aqorders.booksellerinvoicenumber,
 286                 quantity-quantityreceived AS tleft,
 287                 aqorders.ordernumber AS ordnum,entrydate,budgetdate
 288         FROM    aqorders
 289         LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=aqorders.biblioitemnumber
 290         LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
 291         WHERE   bookfundid=?
 292             AND (budgetdate >= ? AND budgetdate < ?)
 293             AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
 294     ";
 295
 296     $sth = $dbh->prepare($query);
 297 #      warn "$start $end";
 298     $sth->execute( $id, $start, $end );
 299
 300     my $comtd=0;
 301
 302     while ( my $data = $sth->fetchrow_hashref ) {
 303         my $left = $data->{'tleft'};
 304         if ( !$left || $left eq '' ) {
 305             $left = $data->{'quantity'};
 306         }
 307         if ( $left && $left > 0 ) {
 308             my $subtotal = $left * $data->{'ecost'};
 309             $data->{subtotal} = $subtotal;
 310             $data->{'left'} = $left;
 311             $comtd += $subtotal;
 312         }
 313 #         use Data::Dumper; warn Dumper($data);
 314     }
 315
 316     $sth->finish;
 317     return ( $spent, $comtd );
 318 }
 319
 320 =head3 NewBookFund
 321
 322 &NewBookFund(bookfundid, bookfundname, branchcode);
 323
 324 this function create a new bookfund into the database.
 325
 326 =cut
 327
 328 sub NewBookFund{
 329     my ($bookfundid, $bookfundname, $branchcode) = @_;
 330     $branchcode = undef unless $branchcode;
 331     my $dbh = C4::Context->dbh;
 332     my $query = "
 333         INSERT
 334         INTO aqbookfund
 335             (bookfundid, bookfundname, branchcode)
 336         VALUES
 337             (?, ?, ?)
 338     ";
 339     my $sth=$dbh->prepare($query);
 340     $sth->execute($bookfundid,$bookfundname,"$branchcode");
 341 }
 342
 343 #-------------------------------------------------------------#
 344
 345 =head3 ModBookFund
 346
 347 &ModBookFund($bookfundname,$bookfundid,$current_branch, $branchcode)
 348
 349 This function updates the bookfundname and the branchcode in the aqbookfund table.
 350
 351 =cut
 352
 353 # FIXME: use placeholders,  ->prepare(), ->execute()
 354
 355 sub ModBookFund {
 356     my ($bookfundname,$bookfundid,$current_branch, $branchcode) = @_;
 357
 358     my $dbh = C4::Context->dbh;
 359
 360     my $retval = $dbh->do("
 361      UPDATE aqbookfund
 362         SET    bookfundname = '$bookfundname',
 363                branchcode = '$branchcode'
 364         WHERE  bookfundid = '$bookfundid'
 365         AND branchcode = '$current_branch'
 366     ");
 367
 368     ### $retval
 369
 370     # budgets depending on a bookfund must have the same branchcode
 371
 372     # if the bookfund branchcode is set, and previous update is successfull, then update aqbudget.branchcode too.
 373     if (defined $branchcode && $retval > 0) {
 374         my $query = "UPDATE  aqbudget
 375             SET     branchcode = ?
 376             WHERE   bookfundid = ? ";
 377
 378         my $sth=$dbh->prepare($query);
 379         $sth->execute($branchcode, $bookfundid) ;
 380     }
 381 }
 382
 383 #-------------------------------------------------------------#
 384
 385 =head3 SearchBookFund
 386
 387 @results = SearchBookFund(
 388         $bookfundid,$filter,$filter_bookfundid,
 389         $filter_bookfundname,$filter_branchcode);
 390
 391 this function searchs among the bookfunds corresponding to our filtering rules.
 392
 393 =cut
 394
 395 sub SearchBookFund {
 396     my $dbh = C4::Context->dbh;
 397     my ($filter,
 398         $filter_bookfundid,
 399         $filter_bookfundname,
 400         $filter_branchcode
 401        ) = @_;
 402
 403     my @bindings;
 404
 405     my $query = "
 406         SELECT  bookfundid,
 407                 bookfundname,
 408                 bookfundgroup,
 409                 branchcode
 410         FROM aqbookfund
 411         WHERE 1 ";
 412
 413     if ($filter) {
 414         if ($filter_bookfundid) {
 415             $query.= "AND bookfundid = ?";
 416             push @bindings, $filter_bookfundid;
 417         }
 418         if ($filter_bookfundname) {
 419             $query.= "AND bookfundname like ?";
 420             push @bindings, '%'.$filter_bookfundname.'%';
 421         }
 422         if ($filter_branchcode) {
 423             $query.= "AND branchcode = ?";
 424             push @bindings, $filter_branchcode;
 425         }
 426     }
 427     $query.= "ORDER BY bookfundid";
 428
 429     my $sth = $dbh->prepare($query);
 430     $sth->execute(@bindings);
 431     my @results;
 432     while (my $row = $sth->fetchrow_hashref) {
 433         push @results, $row;
 434     }
 435     return @results;
 436 }
 437
 438 #-------------------------------------------------------------#
 439
 440 =head3 ModCurrencies
 441
 442 &ModCurrencies($currency, $newrate);
 443
 444 Sets the exchange rate for C<$currency> to be C<$newrate>.
 445
 446 =cut
 447
 448 sub ModCurrencies {
 449     my ( $currency, $rate ) = @_;
 450     my $dbh = C4::Context->dbh;
 451     my $query = "
 452         UPDATE currency
 453         SET    rate=?
 454         WHERE  currency=?
 455     ";
 456     my $sth = $dbh->prepare($query);
 457     $sth->execute( $rate, $currency );
 458 }
 459
 460 #-------------------------------------------------------------#
 461
 462 =head3 Countbookfund
 463
 464 $number = Countbookfund($bookfundid);
 465
 466 this function count the number of bookfund with id given on input arg.
 467 return :
 468 the result of the SQL query as a number.
 469
 470 =cut
 471
 472 sub Countbookfund {
 473     my $bookfundid = shift;
 474     my $branchcode = shift;
 475     my $dbh = C4::Context->dbh;
 476     my $query ="
 477         SELECT COUNT(*)
 478         FROM  aqbookfund
 479         WHERE bookfundid = ?
 480         AND   branchcode = ?
 481     ";
 482     my $sth = $dbh->prepare($query);
 483     $sth->execute($bookfundid,"$branchcode");
 484     return $sth->fetchrow;
 485 }
 486
 487
 488 #-------------------------------------------------------------#
 489
 490 =head3 ConvertCurrency
 491
 492 $foreignprice = &ConvertCurrency($currency, $localprice);
 493
 494 Converts the price C<$localprice> to foreign currency C<$currency> by
 495 dividing by the exchange rate, and returns the result.
 496
 497 If no exchange rate is found, C<&ConvertCurrency> assumes the rate is one
 498 to one.
 499
 500 =cut
 501
 502 sub ConvertCurrency {
 503     my ( $currency, $price ) = @_;
 504     my $dbh = C4::Context->dbh;
 505     my $query = "
 506         SELECT rate
 507         FROM   currency
 508         WHERE  currency=?
 509     ";
 510     my $sth = $dbh->prepare($query);
 511     $sth->execute($currency);
 512     my $cur = ( $sth->fetchrow_array() )[0];
 513     unless($cur) {
 514         $cur = 1;
 515     }
 516     return ( $price / $cur );
 517 }
 518
 519 #-------------------------------------------------------------#
 520
 521 =head3 DelBookFund
 522
 523 &DelBookFund($bookfundid);
 524 this function delete a bookfund which has $bokfundid as parameter on aqbookfund table and delete the approriate budget.
 525
 526 =cut
 527
 528 sub DelBookFund {
 529     my $bookfundid = shift;
 530     my $branchcode=shift;
 531     my $dbh = C4::Context->dbh;
 532     my $query = "
 533         DELETE FROM aqbookfund
 534         WHERE bookfundid=?
 535         AND branchcode=?
 536     ";
 537     my $sth=$dbh->prepare($query);
 538     $sth->execute($bookfundid,$branchcode);
 539     $sth->finish;
 540     $query = "
 541         DELETE FROM aqbudget where bookfundid=? and branchcode=?
 542     ";
 543     $sth=$dbh->prepare($query);
 544     $sth->execute($bookfundid,$branchcode);
 545     $sth->finish;
 546 }
 547
 548 END { }    # module clean-up code here (global destructor)
 549
 550 1;
 551
 552 __END__
 553
 554 =head1 AUTHOR
 555
 556 Koha Developement team <info@koha.org>
 557
 558 =cut