Fixing hierarchy processing

[koha.git] / acqui / bookfund.pl

diff --git a/acqui/bookfund.pl b/acqui/bookfund.pl
index fa2b0fe..bac0bf9 100755 (executable)
--- a/acqui/bookfund.pl
+++ b/acqui/bookfund.pl
@@ -1,4 +1,4 @@
-#!/usr/bin/perl -w
+#!/usr/bin/perl
 
 # Copyright 2006 Katipo Communications
 #                                     
@@ -21,7 +21,7 @@ use C4::Context;
 use strict;
 use CGI;
 use C4::Auth;
-use C4::Interface::CGI::Output;
+use C4::Output;
 
 my $dbh      = C4::Context->dbh;
 my $input    = new CGI;
@@ -40,20 +40,41 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
     }
 );
 
-my $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 aqorders,aqorderbreakdown,aqbasket                                                                                     
-left join biblioitems on  biblioitems.biblioitemnumber=aqorders.biblioitemnumber
-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')
-";
-warn $query;
+my $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 aqorders
+    INNER JOIN aqorderbreakdown
+      ON aqorderbreakdown.ordernumber = aqorders.ordernumber
+    INNER JOIN aqbasket
+      ON aqbasket.basketno = aqorders.basketno
+    LEFT JOIN biblioitems
+      ON biblioitems.biblioitemnumber = aqorders.biblioitemnumber
+  WHERE bookfundid = ?
+    AND budgetdate >= ?
+    AND budgetdate < ?
+    AND (datecancellationprinted IS NULL
+         OR datecancellationprinted = \'0000-00-00\')
+    AND (closedate >= ? and closedate < ?)
+';
 my $sth = $dbh->prepare($query);
-$sth->execute( $bookfund, $start, $end );
+$sth->execute( $bookfund, $start, $end, $start, $end);
 my @commited_loop;
 
 my $total = 0;
@@ -64,7 +85,7 @@ while ( my $data = $sth->fetchrow_hashref ) {
     }
     if ( $left && $left > 0 ) {
         my $subtotal = $left * $data->{'ecost'};
-        $data->{subtotal} = $subtotal;
+        $data->{subtotal} = sprintf("%.2f",$subtotal);
         $data->{'left'} = $left;
         push @commited_loop, $data;
         $total += $subtotal;
@@ -72,8 +93,8 @@ while ( my $data = $sth->fetchrow_hashref ) {
 }
 
 $template->param(
-    COMMITEDLOOP => \@commited_loop,
-    total        => $total
+    COMMITTEDLOOP => \@commited_loop,
+    total        =>  sprintf("%.2f",$total),
 );
 $sth->finish;
 $dbh->disconnect;