Koha 22.11.00 Rosalie is here!
[koha.git] / acqui / spent.pl
1 #!/usr/bin/perl
2
3 # script to show a breakdown of committed and spent budgets
4
5 # Copyright 2002-2009 Katipo Communications Limited
6 # Copyright 2010,2011 Catalyst IT Limited
7 # This file is part of Koha.
8 #
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.
13 #
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.
18 #
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>.
21
22 =head1 NAME
23
24  spent.pl
25
26 =head1 DESCRIPTION
27
28 this script is designed to show the spent amount in budgets
29
30 =cut
31
32 use C4::Context;
33 use C4::Auth qw( get_template_and_user );
34 use C4::Output qw( output_html_with_http_headers );
35 use Modern::Perl;
36 use CGI qw ( -utf8 );
37 use C4::Acquisition qw( get_rounded_price );
38 use Koha::Acquisition::Invoice::Adjustments;
39
40 my $dbh      = C4::Context->dbh;
41 my $input    = CGI->new;
42 my $bookfund = $input->param('fund');
43 my $fund_code = $input->param('fund_code');
44
45 my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
46     {
47         template_name   => "acqui/spent.tt",
48         query           => $input,
49         type            => "intranet",
50         flagsrequired   => { acquisition => '*' },
51     }
52 );
53
54 my $query = <<EOQ;
55 SELECT
56     aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
57     quantity-quantityreceived AS tleft,
58     budgetdate, entrydate,
59     aqbasket.booksellerid,
60     GROUP_CONCAT(DISTINCT itype SEPARATOR '|') as itypes,
61     title,
62     aqorders.invoiceid,
63     aqinvoices.invoicenumber,
64     quantityreceived,
65     unitprice_tax_included,
66     datereceived,
67     aqbooksellers.name as vendorname
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 LEFT JOIN aqbooksellers ON
78     aqbasket.booksellerid = aqbooksellers.id
79 WHERE
80     aqorders.basketno=aqbasket.basketno AND
81     budget_id=? AND
82     datecancellationprinted IS NULL AND
83     datereceived IS NOT NULL
84     GROUP BY aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
85              tleft,
86              budgetdate, entrydate,
87              aqbasket.booksellerid,
88              title,
89              aqorders.invoiceid,
90              aqinvoices.invoicenumber,
91              quantityreceived,
92              unitprice_tax_included,
93              datereceived,
94              aqbooksellers.name
95
96 EOQ
97 my $sth = $dbh->prepare($query);
98 $sth->execute($bookfund);
99 if ( $sth->err ) {
100     die "An error occurred fetching records: " . $sth->errstr;
101 }
102 my $subtotal = 0;
103 my @spent;
104 while ( my $data = $sth->fetchrow_hashref ) {
105     my $recv = $data->{'quantityreceived'};
106     $data->{'itemtypes'} = [split('\|', $data->{itypes})];
107     if ( $recv > 0 ) {
108         my $rowtotal = $recv * get_rounded_price($data->{'unitprice_tax_included'});
109         $data->{'rowtotal'}  = sprintf( "%.2f", $rowtotal );
110         $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{'unitprice_tax_included'} );
111         $subtotal += $rowtotal;
112         push @spent, $data;
113     }
114
115 }
116
117 my $total = $subtotal;
118 $query = qq{
119     SELECT invoiceid, invoicenumber, shipmentcost
120     FROM aqinvoices
121     WHERE shipmentcost_budgetid = ?
122 };
123 $sth = $dbh->prepare($query);
124 $sth->execute($bookfund);
125 my @shipmentcosts;
126 while (my $data = $sth->fetchrow_hashref) {
127     push @shipmentcosts, {
128         shipmentcost => sprintf("%.2f", $data->{shipmentcost}),
129         invoiceid => $data->{invoiceid},
130         invoicenumber => $data->{invoicenumber}
131     };
132     $total += $data->{shipmentcost};
133 }
134 $sth->finish;
135
136 my $adjustments = Koha::Acquisition::Invoice::Adjustments->search({budget_id => $bookfund, closedate => { '!=' => undef } }, { prefetch => 'invoiceid' },  );
137 while ( my $adj = $adjustments->next ){
138     $total += $adj->adjustment;
139 }
140
141 $total = sprintf( "%.2f", $total );
142
143 $template->param(
144     fund => $bookfund,
145     spent => \@spent,
146     subtotal => $subtotal,
147     shipmentcosts => \@shipmentcosts,
148     adjustments => $adjustments,
149     total => $total,
150     fund_code => $fund_code
151 );
152
153 output_html_with_http_headers $input, $cookie, $template->output;