# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
# Suite 330, Boston, MA 02111-1307 USA
-# $Id$
-
use strict;
-
+# use Smart::Comments;
use vars qw($VERSION @ISA @EXPORT);
# set the version for version checking
-$VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); };
+$VERSION = 3.00;
=head1 NAME
@ISA = qw(Exporter);
@EXPORT = qw(
- &GetBookFund &GetBookFunds &GetBookFundBreakdown &GetCurrencies
+ &GetBookFund &GetBookFunds &GetBookFundsId &GetBookFundBreakdown &GetCurrencies
&NewBookFund
&ModBookFund &ModCurrencies
- &Countbookfund
+ &SearchBookFund
+ &Countbookfund
&ConvertCurrency
&DelBookFund
);
=head1 FUNCTIONS
-=over 2
-
=cut
#-------------------------------------------------------------#
-=head3 GetBookFund
-
-=over 4
+=head2 GetBookFund
$dataaqbookfund = &GetBookFund($bookfundid);
C<$dataaqbookfund> is a hashref full of bookfundid, bookfundname, bookfundgroup,
and branchcode.
-=back
-
=cut
sub GetBookFund {
- my $bookfundid = @_;
+ my $bookfundid = shift;
+ my $branchcode = shift;
+ $branchcode=($branchcode?$branchcode:'');
my $dbh = C4::Context->dbh;
my $query = "
SELECT
branchcode
FROM aqbookfund
WHERE bookfundid = ?
- ";
+ AND branchcode = ?";
my $sth=$dbh->prepare($query);
- return $sth->fetchrow_hashref;
+ $sth->execute($bookfundid,$branchcode);
+ my $data=$sth->fetchrow_hashref;
+ return $data;
+}
+
+
+=head3 GetBookFundsId
+
+$sth = &GetBookFundsId
+Read on aqbookfund table and execute a simple SQL query.
+
+return:
+$sth->execute. Don't forget to fetch row from the database after using
+this function by using, for example, $sth->fetchrow_hashref;
+
+C<@results> is an array of id existing on the database.
+
+=cut
+
+sub GetBookFundsId {
+ my @bookfundids_loop;
+ my $dbh= C4::Context->dbh;
+ my $query = "
+ SELECT bookfundid,branchcode
+ FROM aqbookfund
+ ";
+ my $sth = $dbh->prepare($query);
+ $sth->execute;
+ return $sth;
}
#-------------------------------------------------------------#
=head3 GetBookFunds
-=over 4
-
@results = &GetBookFunds;
Returns a list of all book funds.
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
alphabetically by book fund name.
-=back
-
=cut
sub GetBookFunds {
my ($branch) = @_;
my $dbh = C4::Context->dbh;
my $userenv = C4::Context->userenv;
- my $branch = $userenv->{branch};
my $strsth;
if ( $branch ne '' ) {
$strsth = "
SELECT *
- FROM aqbookfund,aqbudget
- WHERE aqbookfund.bookfundid=aqbudget.bookfundid
- AND startdate<now()
+ FROM aqbookfund
+ LEFT JOIN aqbudget ON aqbookfund.bookfundid=aqbudget.bookfundid
+ WHERE startdate<now()
AND enddate>now()
- AND (aqbookfund.branchcode IS NULL OR aqbookfund.branchcode='' OR aqbookfund.branchcode= ? )
+ AND (aqbookfund.branchcode='' OR aqbookfund.branchcode= ? )
GROUP BY aqbookfund.bookfundid ORDER BY bookfundname";
}
else {
$strsth = "
SELECT *
- FROM aqbookfund,
- aqbudget
- WHERE aqbookfund.bookfundid=aqbudget.bookfundid
- AND startdate<now()
+ FROM aqbookfund
+ LEFT JOIN aqbudget ON aqbookfund.bookfundid=aqbudget.bookfundid
+ WHERE startdate<now()
AND enddate>now()
GROUP BY aqbookfund.bookfundid ORDER BY bookfundname
";
=head3 GetCurrencies
-=over 4
-
@currencies = &GetCurrencies;
Returns the list of all known currencies.
C<$currencies> is a array; its elements are references-to-hash, whose
keys are the fields from the currency table in the Koha database.
-=back
-
=cut
sub GetCurrencies {
=head3 GetBookFundBreakdown
-=over 4
-
-( $spent, $comtd ) = &GetBookFundBreakdown( $id, $year, $start, $end );
+( $spent, $comtd ) = &GetBookFundBreakdown( $id, $start, $end );
returns the total comtd & spent for a given bookfund, and a given year
used in acqui-home.pl
-=back
-
=cut
sub GetBookFundBreakdown {
- my ( $id, $year, $start, $end ) = @_;
+ my ( $id, $start, $end ) = @_;
my $dbh = C4::Context->dbh;
# if no start/end dates given defaut to everything
# do a query for spent totals.
my $query = "
- SELECT quantity,datereceived,freight,unitprice,listprice,ecost,
- quantityreceived,subscription
- FROM aqorders
- LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
- WHERE bookfundid=?
- AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
- AND ((datereceived >= ? and datereceived < ?) OR (budgetdate >= ? and budgetdate < ?))
+ Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived
+ as qrev,subscription,title,itype as itemtype,aqorders.biblionumber,aqorders.booksellerinvoicenumber,
+ quantity-quantityreceived as tleft,
+ aqorders.ordernumber
+ as ordnum,entrydate,budgetdate,aqbasket.booksellerid,aqbasket.basketno
+ from aqorders
+ inner join aqorderbreakdown on aqorderbreakdown.ordernumber = aqorders.ordernumber
+ inner join aqbasket on aqbasket.basketno = aqorders.basketno
+ left join items on items.biblionumber=aqorders.biblionumber
+ where bookfundid=?
+ and (datereceived >= ? and datereceived < ?)
+ and (datecancellationprinted is NULL or
+ datecancellationprinted='0000-00-00')
+ and (closedate >= ? and closedate < ?)
+ ORDER BY datereceived
";
my $sth = $dbh->prepare($query);
- $sth->execute( $id, $start, $end, $start, $end );
+ $sth->execute( $id, $start, $end, $start, $end);
- my $spent = 0;
+ my ($spent) = 0;
while ( my $data = $sth->fetchrow_hashref ) {
- if ( $data->{'subscription'} == 1 ) {
- $spent += $data->{'quantity'} * $data->{'unitprice'};
- }
- else {
-
- my $leftover = $data->{'quantity'} - $data->{'quantityreceived'};
- $spent += ( $data->{'unitprice'} ) * $data->{'quantityreceived'};
+ if($data->{datereceived}){
+ my $recv = $data->{'qrev'};
+ if ( $recv > 0 ) {
+ $spent += $recv * $data->{'unitprice'};
+ }
}
}
# then do a seperate query for commited totals, (pervious single query was
# returning incorrect comitted results.
- my $query = "
+ $query = "
SELECT quantity,datereceived,freight,unitprice,
listprice,ecost,quantityreceived AS qrev,
subscription,title,itemtype,aqorders.biblionumber,
aqorders.booksellerinvoicenumber,
- quantity-quantityreceived AS tleft,
- aqorders.ordernumber AS ordnum,entrydate,budgetdate,
- booksellerid,aqbasket.basketno
- FROM aqorderbreakdown,
- aqbasket,
- aqorders
+ quantity-quantityreceived AS tleft,quantityreceived,
+ aqorders.ordernumber AS ordnum,entrydate,budgetdate
+ FROM aqorders
+ LEFT JOIN aqbasket USING (basketno)
LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=aqorders.biblioitemnumber
+ LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
WHERE bookfundid=?
- AND aqorders.ordernumber=aqorderbreakdown.ordernumber
- AND aqorders.basketno=aqbasket.basketno
AND (budgetdate >= ? AND budgetdate < ?)
AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
+ AND (closedate >= ? AND closedate <= ?)
";
- my $sth = $dbh->prepare($query);
- $sth->execute( $id, $start, $end );
+ $sth = $dbh->prepare($query);
+# warn "$start $end";
+ $sth->execute( $id, $start, $end , $start, $end);
- my $comtd;
+ my $comtd=0;
- my $total = 0;
while ( my $data = $sth->fetchrow_hashref ) {
- my $left = $data->{'tleft'};
- if ( !$left || $left eq '' ) {
- $left = $data->{'quantity'};
- }
- if ( $left && $left > 0 ) {
- my $subtotal = $left * $data->{'ecost'};
- $data->{subtotal} = $subtotal;
- $data->{'left'} = $left;
- $comtd += $subtotal;
+ if(not $data->{datereceived}){
+ my $left = $data->{'tleft'};
+ if ( !$left || $left eq '' ) {
+ $left = $data->{'quantity'};
+ }
+ if ( $left && $left > 0 ) {
+ my $subtotal = $left * $data->{'ecost'};
+ $data->{subtotal} = $subtotal;
+ $data->{'left'} = $left;
+ $comtd += $subtotal;
+ }
}
+# use Data::Dumper; warn Dumper($data);
}
$sth->finish;
=head3 NewBookFund
-=over 4
-
&NewBookFund(bookfundid, bookfundname, branchcode);
this function create a new bookfund into the database.
-=back
-
=cut
sub NewBookFund{
(?, ?, ?)
";
my $sth=$dbh->prepare($query);
- $sth->execute($bookfundid,$bookfundname,$branchcode);
+ $sth->execute($bookfundid,$bookfundname,"$branchcode");
}
#-------------------------------------------------------------#
=head3 ModBookFund
-=over 4
-
-&ModBookFund($bookfundname,$branchcode,$bookfundid);
-this function update the bookfundname and the branchcode on aqbookfund table
-on database.
+&ModBookFund($bookfundname,$bookfundid,$current_branch, $branchcode)
-=back
+This function updates the bookfundname and the branchcode in the aqbookfund table.
=cut
+# FIXME: use placeholders, ->prepare(), ->execute()
+
sub ModBookFund {
- my ($bookfundname,$branchcode,$bookfundid) = @_;
+ my ($bookfundname,$bookfundid,$current_branch, $branchcode) = @_;
+
my $dbh = C4::Context->dbh;
- my $query = "
- UPDATE aqbookfund
- SET bookfundname = ?,
- branchcode = ?
- WHERE bookfundid = ?
- ";
- my $sth=$dbh->prepare($query);
- $sth->execute($bookfundname,$branchcode,$bookfundid);
-# budgets depending on a bookfund must have the same branchcode
-# if the bookfund branchcode is set
- if (defined $branchcode) {
- $query = "
- UPDATE aqbudget
- SET branchcode = ?
- ";
- $sth=$dbh->prepare($query);
- $sth->execute($branchcode);
+
+ my $retval = $dbh->do("
+ UPDATE aqbookfund
+ SET bookfundname = '$bookfundname',
+ branchcode = '$branchcode'
+ WHERE bookfundid = '$bookfundid'
+ AND branchcode = '$current_branch'
+ ");
+
+ ### $retval
+
+ # budgets depending on a bookfund must have the same branchcode
+
+ # if the bookfund branchcode is set, and previous update is successfull, then update aqbudget.branchcode too.
+ if (defined $branchcode && $retval > 0) {
+ my $query = "UPDATE aqbudget
+ SET branchcode = ?
+ WHERE bookfundid = ? ";
+
+ my $sth=$dbh->prepare($query);
+ $sth->execute($branchcode, $bookfundid) ;
}
}
=head3 SearchBookFund
-=over 4
@results = SearchBookFund(
$bookfundid,$filter,$filter_bookfundid,
$filter_bookfundname,$filter_branchcode);
this function searchs among the bookfunds corresponding to our filtering rules.
-=back
-
=cut
sub SearchBookFund {
bookfundgroup,
branchcode
FROM aqbookfund
- WHERE 1 = 1";
+ WHERE 1 ";
if ($filter) {
if ($filter_bookfundid) {
=head3 ModCurrencies
-=over 4
-
&ModCurrencies($currency, $newrate);
Sets the exchange rate for C<$currency> to be C<$newrate>.
-=back
-
=cut
sub ModCurrencies {
=head3 Countbookfund
-=over 4
-
-$data = Countbookfund($bookfundid);
+$number = Countbookfund($bookfundid);
this function count the number of bookfund with id given on input arg.
return :
-the result of the SQL query as an hashref.
-
-=back
+the result of the SQL query as a number.
=cut
sub Countbookfund {
- my $bookfundid = @_;
+ my $bookfundid = shift;
+ my $branchcode = shift;
my $dbh = C4::Context->dbh;
my $query ="
SELECT COUNT(*)
- FROM aqbookfund
+ FROM aqbookfund
WHERE bookfundid = ?
+ AND branchcode = ?
";
my $sth = $dbh->prepare($query);
- $sth->execute($bookfundid);
- return $sth->fetchrow_hashref;
+ $sth->execute($bookfundid,"$branchcode");
+ return $sth->fetchrow;
}
=head3 ConvertCurrency
-=over 4
-
$foreignprice = &ConvertCurrency($currency, $localprice);
Converts the price C<$localprice> to foreign currency C<$currency> by
If no exchange rate is found, C<&ConvertCurrency> assumes the rate is one
to one.
-=back
-
=cut
sub ConvertCurrency {
my $sth = $dbh->prepare($query);
$sth->execute($currency);
my $cur = ( $sth->fetchrow_array() )[0];
- if ( $cur == 0 ) {
+ unless($cur) {
$cur = 1;
}
return ( $price / $cur );
=head3 DelBookFund
-=over 4
-
&DelBookFund($bookfundid);
this function delete a bookfund which has $bokfundid as parameter on aqbookfund table and delete the approriate budget.
-=back
-
=cut
sub DelBookFund {
my $bookfundid = shift;
+ my $branchcode=shift;
my $dbh = C4::Context->dbh;
my $query = "
DELETE FROM aqbookfund
WHERE bookfundid=?
+ AND branchcode=?
";
my $sth=$dbh->prepare($query);
- $sth->execute($bookfundid);
+ $sth->execute($bookfundid,$branchcode);
$sth->finish;
$query = "
- DELETE FROM aqbudget where bookfundid=?
+ DELETE FROM aqbudget where bookfundid=? and branchcode=?
";
$sth=$dbh->prepare($query);
- $sth->execute($bookfundid);
+ $sth->execute($bookfundid,$branchcode);
$sth->finish;
}
__END__
-=back
-
=head1 AUTHOR
Koha Developement team <info@koha.org>