Bug 20600: (follow-up) Fix filtering
[koha.git] / acqui / spent.pl
blob69a25379f93d8271deb894a5168f00b2d83e9e47
1 #!/usr/bin/perl
3 # script to show a breakdown of committed and spent budgets
5 # Copyright 2002-2009 Katipo Communications Limited
6 # Copyright 2010,2011 Catalyst IT Limited
7 # This file is part of Koha.
9 # Koha is free software; you can redistribute it and/or modify it
10 # under the terms of the GNU General Public License as published by
11 # the Free Software Foundation; either version 3 of the License, or
12 # (at your option) any later version.
14 # Koha is distributed in the hope that it will be useful, but
15 # WITHOUT ANY WARRANTY; without even the implied warranty of
16 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 # GNU General Public License for more details.
19 # You should have received a copy of the GNU General Public License
20 # along with Koha; if not, see <http://www.gnu.org/licenses>.
22 =head1 NAME
24 spent.pl
26 =head1 DESCRIPTION
28 this script is designed to show the spent amount in budgets
30 =cut
32 use C4::Context;
33 use C4::Auth;
34 use C4::Output;
35 use Modern::Perl;
36 use CGI qw ( -utf8 );
37 use Koha::Acquisition::Invoice::Adjustments;
39 my $dbh = C4::Context->dbh;
40 my $input = new CGI;
41 my $bookfund = $input->param('fund');
42 my $fund_code = $input->param('fund_code');
44 my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
46 template_name => "acqui/spent.tt",
47 query => $input,
48 type => "intranet",
49 authnotrequired => 0,
50 flagsrequired => { acquisition => '*' },
51 debug => 1,
55 my $query = <<EOQ;
56 SELECT
57 aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
58 quantity-quantityreceived AS tleft,
59 budgetdate, entrydate,
60 aqbasket.booksellerid,
61 itype,
62 title,
63 aqorders.invoiceid,
64 aqinvoices.invoicenumber,
65 quantityreceived,
66 unitprice_tax_included,
67 datereceived,
68 aqbooksellers.name as vendorname
69 FROM (aqorders, aqbasket)
70 LEFT JOIN biblio ON
71 biblio.biblionumber=aqorders.biblionumber
72 LEFT JOIN aqorders_items ON
73 aqorders.ordernumber = aqorders_items.ordernumber
74 LEFT JOIN items ON
75 aqorders_items.itemnumber = items.itemnumber
76 LEFT JOIN aqinvoices ON
77 aqorders.invoiceid = aqinvoices.invoiceid
78 LEFT JOIN aqbooksellers ON
79 aqbasket.booksellerid = aqbooksellers.id
80 WHERE
81 aqorders.basketno=aqbasket.basketno AND
82 budget_id=? AND
83 (datecancellationprinted IS NULL OR
84 datecancellationprinted='0000-00-00') AND
85 datereceived IS NOT NULL
86 GROUP BY aqorders.ordernumber
87 EOQ
88 my $sth = $dbh->prepare($query);
89 $sth->execute($bookfund);
90 if ( $sth->err ) {
91 die "An error occurred fetching records: " . $sth->errstr;
93 my $subtotal = 0;
94 my @spent;
95 while ( my $data = $sth->fetchrow_hashref ) {
96 my $recv = $data->{'quantityreceived'};
97 if ( $recv > 0 ) {
98 my $rowtotal = $recv * $data->{'unitprice_tax_included'};
99 $data->{'rowtotal'} = sprintf( "%.2f", $rowtotal );
100 $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{'unitprice_tax_included'} );
101 $subtotal += $rowtotal;
102 push @spent, $data;
107 my $total = $subtotal;
108 $query = qq{
109 SELECT invoicenumber, shipmentcost
110 FROM aqinvoices
111 WHERE shipmentcost_budgetid = ?
113 $sth = $dbh->prepare($query);
114 $sth->execute($bookfund);
115 my @shipmentcosts;
116 while (my $data = $sth->fetchrow_hashref) {
117 push @shipmentcosts, {
118 shipmentcost => sprintf("%.2f", $data->{shipmentcost}),
119 invoicenumber => $data->{invoicenumber}
121 $total += $data->{shipmentcost};
123 $sth->finish;
125 my $adjustments = Koha::Acquisition::Invoice::Adjustments->search({budget_id => $bookfund, closedate => { '!=' => undef } }, { join => 'invoiceid' } );
126 while ( my $adj = $adjustments->next ){
127 $total += $adj->adjustment;
130 $total = sprintf( "%.2f", $total );
132 $template->param(
133 fund => $bookfund,
134 spent => \@spent,
135 subtotal => $subtotal,
136 shipmentcosts => \@shipmentcosts,
137 adjustments => $adjustments,
138 total => $total,
139 fund_code => $fund_code
142 output_html_with_http_headers $input, $cookie, $template->output;