use strict;
-
+# use Smart::Comments;
use vars qw($VERSION @ISA @EXPORT);
my ( $id, $start, $end ) = @_;
my $dbh = C4::Context->dbh;
+
# if no start/end dates given defaut to everything
if ( !$start ) {
$start = '0000-00-00';
# 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
- LEFT JOIN aqbookfund ON (aqorderbreakdown.bookfundid=aqbookfund.bookfundid and aqorderbreakdown.branchcode=aqbookfund.branchcode)
- LEFT JOIN aqbudget ON (aqbudget.bookfundid=aqbookfund.bookfundid and aqbudget.branchcode=aqbookfund.branchcode)
- WHERE aqorderbreakdown.bookfundid=?
- AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
- AND ((budgetdate >= ? and budgetdate < ?) OR (startdate>=? and enddate<=?))
+ SELECT quantity,datereceived,freight,unitprice,listprice,ecost,
+ quantityreceived AS qrev,subscription,title,aqorders.biblionumber,
+ aqorders.booksellerinvoicenumber,quantity-quantityreceived as tleft,
+ aqorders.ordernumber as ordnum,entrydate,budgetdate,aqbasket.booksellerid,
+ aqbasket.basketno
+ FROM aqorders
+ LEFT JOIN aqorderbreakdown USING (ordernumber)
+ LEFT JOIN aqbasket USING (basketno)
+ LEFT JOIN aqbudget USING (bookfundid)
+ WHERE bookfundid=?
+ AND (datecancellationprinted IS NULL OR datecancellationprinted = '0000-00-00')
+ AND closedate BETWEEN startdate AND enddate
+ AND creationdate > startdate
+ ORDER BY datereceived
";
my $sth = $dbh->prepare($query);
- $sth->execute( $id, $start, $end, $start, $end );
+ $sth->execute( $id);
- my ($spent) = 0;
+ my ($spent, $comtd) = (0, 0);
while ( my $data = $sth->fetchrow_hashref ) {
- if ( $data->{'subscription'} == 1 ) {
- $spent += $data->{'quantity'} * $data->{'unitprice'};
- }
- else {
- $spent += ( $data->{'unitprice'} ) * ($data->{'quantityreceived'}?$data->{'quantityreceived'}:0);
-
+
+ my $recv = $data->{'qrev'};
+ my $left = $data->{'tleft'};
+ my $ecost = $data->{'ecost'};
+
+
+ if($data->{datereceived}){
+ if ( $recv > 0 ) {
+ $spent += $recv * $data->{'unitprice'};
+ }
}
- }
-
- # then do a seperate query for commited totals, (pervious single query was
- # returning incorrect comitted results.
-
- $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
- FROM aqorders
- LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=aqorders.biblioitemnumber
- LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
- WHERE bookfundid=?
- AND (budgetdate >= ? AND budgetdate < ?)
- AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
- ";
+ $left = $data->{quantity} if(not $recv);
- $sth = $dbh->prepare($query);
-# warn "$start $end";
- $sth->execute( $id, $start, $end );
+ $comtd += $left * $ecost;
- my $comtd=0;
-
- while ( my $data = $sth->fetchrow_hashref ) {
- my $left = $data->{'tleft'};
- if ( (!$left && (!$data->{'datereceived'}||$data->{'datereceived'} eq '0000-00-00') ) || $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 ModBookFund
-&ModBookFund($bookfundname,$branchcode,$bookfundid);
-this function update the bookfundname and the branchcode on aqbookfund table
-on database.
+&ModBookFund($bookfundname,$bookfundid,$current_branch, $branchcode)
+
+This function updates the bookfundname and the branchcode in the aqbookfund table.
=cut
+# FIXME: use placeholders, ->prepare(), ->execute()
+
sub ModBookFund {
- my ($bookfundname,$bookfundid,$branchcode) = @_;
+ my ($bookfundname,$bookfundid,$current_branch, $branchcode) = @_;
+
my $dbh = C4::Context->dbh;
- my $query = "
- UPDATE aqbookfund
- SET bookfundname = ?
- WHERE bookfundid = ?
- AND branchcode= ?
- ";
- warn "name : $bookfundname";
- my $sth=$dbh->prepare($query);
- $sth->execute($bookfundname,$bookfundid,"$branchcode");
-# 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) ;
}
}
AND branchcode = ?
";
my $sth = $dbh->prepare($query);
- $sth->execute($bookfundid,$branchcode);
+ $sth->execute($bookfundid,"$branchcode");
return $sth->fetchrow;
}
projects / koha.git / blobdiff