Bug 28704: Library MARCOrgCode field needs maxlength attribute
[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;
34 use C4::Output;
35 use Modern::Perl;
36 use CGI qw ( -utf8 );
37 use C4::Acquisition;
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         debug           => 1,
52     }
53 );
54
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     GROUP_CONCAT(DISTINCT itype SEPARATOR '|') as itypes,
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 AND
84     datereceived IS NOT NULL
85     GROUP BY aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
86              tleft,
87              budgetdate, entrydate,
88              aqbasket.booksellerid,
89              title,
90              aqorders.invoiceid,
91              aqinvoices.invoicenumber,
92              quantityreceived,
93              unitprice_tax_included,
94              datereceived,
95              aqbooksellers.name
96
97 EOQ
98 my $sth = $dbh->prepare($query);
99 $sth->execute($bookfund);
100 if ( $sth->err ) {
101     die "An error occurred fetching records: " . $sth->errstr;
102 }
103 my $subtotal = 0;
104 my @spent;
105 while ( my $data = $sth->fetchrow_hashref ) {
106     my $recv = $data->{'quantityreceived'};
107     $data->{'itemtypes'} = [split('\|', $data->{itypes})];
108     if ( $recv > 0 ) {
109         my $rowtotal = $recv * get_rounded_price($data->{'unitprice_tax_included'});
110         $data->{'rowtotal'}  = sprintf( "%.2f", $rowtotal );
111         $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{'unitprice_tax_included'} );
112         $subtotal += $rowtotal;
113         push @spent, $data;
114     }
115
116 }
117
118 my $total = $subtotal;
119 $query = qq{
120     SELECT invoiceid, invoicenumber, shipmentcost
121     FROM aqinvoices
122     WHERE shipmentcost_budgetid = ?
123 };
124 $sth = $dbh->prepare($query);
125 $sth->execute($bookfund);
126 my @shipmentcosts;
127 while (my $data = $sth->fetchrow_hashref) {
128     push @shipmentcosts, {
129         shipmentcost => sprintf("%.2f", $data->{shipmentcost}),
130         invoiceid => $data->{invoiceid},
131         invoicenumber => $data->{invoicenumber}
132     };
133     $total += $data->{shipmentcost};
134 }
135 $sth->finish;
136
137 my $adjustments = Koha::Acquisition::Invoice::Adjustments->search({budget_id => $bookfund, closedate => { '!=' => undef } }, { prefetch => 'invoiceid' },  );
138 while ( my $adj = $adjustments->next ){
139     $total += $adj->adjustment;
140 }
141
142 $total = sprintf( "%.2f", $total );
143
144 $template->param(
145     fund => $bookfund,
146     spent => \@spent,
147     subtotal => $subtotal,
148     shipmentcosts => \@shipmentcosts,
149     adjustments => $adjustments,
150     total => $total,
151     fund_code => $fund_code
152 );
153
154 output_html_with_http_headers $input, $cookie, $template->output;