Bug 19377: Remove $5 charge from sample item types
[koha.git] / acqui / spent.pl
blobf31b2830607e792cf4f2f7b1aca32c2e7a35edfc
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 strict;
36 use warnings;
37 use CGI qw ( -utf8 );
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 ecost, budgetdate, entrydate,
60 aqbasket.booksellerid,
61 itype,
62 title,
63 aqorders.invoiceid,
64 aqinvoices.invoicenumber,
65 quantityreceived,
66 unitprice,
67 datereceived
68 FROM (aqorders, aqbasket)
69 LEFT JOIN biblio ON
70 biblio.biblionumber=aqorders.biblionumber
71 LEFT JOIN aqorders_items ON
72 aqorders.ordernumber = aqorders_items.ordernumber
73 LEFT JOIN items ON
74 aqorders_items.itemnumber = items.itemnumber
75 LEFT JOIN aqinvoices ON
76 aqorders.invoiceid = aqinvoices.invoiceid
77 WHERE
78 aqorders.basketno=aqbasket.basketno AND
79 budget_id=? AND
80 (datecancellationprinted IS NULL OR
81 datecancellationprinted='0000-00-00') AND
82 datereceived IS NOT NULL
83 GROUP BY aqorders.ordernumber
84 EOQ
85 my $sth = $dbh->prepare($query);
86 $sth->execute($bookfund);
87 if ( $sth->err ) {
88 die "An error occurred fetching records: " . $sth->errstr;
90 my $subtotal = 0;
91 my @spent;
92 while ( my $data = $sth->fetchrow_hashref ) {
93 my $recv = $data->{'quantityreceived'};
94 if ( $recv > 0 ) {
95 my $rowtotal = $recv * $data->{'unitprice'};
96 $data->{'rowtotal'} = sprintf( "%.2f", $rowtotal );
97 $data->{'unitprice'} = sprintf( "%.2f", $data->{'unitprice'} );
98 $subtotal += $rowtotal;
99 push @spent, $data;
104 my $total = $subtotal;
105 $query = qq{
106 SELECT invoicenumber, shipmentcost
107 FROM aqinvoices
108 WHERE shipmentcost_budgetid = ?
110 $sth = $dbh->prepare($query);
111 $sth->execute($bookfund);
112 my @shipmentcosts;
113 while (my $data = $sth->fetchrow_hashref) {
114 push @shipmentcosts, {
115 shipmentcost => sprintf("%.2f", $data->{shipmentcost}),
116 invoicenumber => $data->{invoicenumber}
118 $total += $data->{shipmentcost};
120 $sth->finish;
122 $total = sprintf( "%.2f", $total );
124 $template->param(
125 fund => $bookfund,
126 spent => \@spent,
127 subtotal => $subtotal,
128 shipmentcosts => \@shipmentcosts,
129 total => $total,
130 fund_code => $fund_code
133 output_html_with_http_headers $input, $cookie, $template->output;