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  acqui/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 # Get correct unitprice field
  55 my ($unitprice_field) = C4::Budgets->FieldsForCalculatingFundValues();
  56
  57 my $query = <<EOQ;
  58 SELECT
  59     aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
  60     quantity-quantityreceived AS tleft,
  61     budgetdate, entrydate,
  62     aqbasket.booksellerid,
  63     GROUP_CONCAT(DISTINCT itype SEPARATOR '|') as itypes,
  64     title,
  65     aqorders.invoiceid,
  66     aqinvoices.invoicenumber,
  67     quantityreceived,
  68     $unitprice_field,
  69     datereceived,
  70     aqbooksellers.name as vendorname
  71 FROM (aqorders, aqbasket)
  72 LEFT JOIN biblio ON
  73     biblio.biblionumber=aqorders.biblionumber
  74 LEFT JOIN aqorders_items ON
  75     aqorders.ordernumber = aqorders_items.ordernumber
  76 LEFT JOIN items ON
  77     aqorders_items.itemnumber = items.itemnumber
  78 LEFT JOIN aqinvoices ON
  79     aqorders.invoiceid = aqinvoices.invoiceid
  80 LEFT JOIN aqbooksellers ON
  81     aqbasket.booksellerid = aqbooksellers.id
  82 WHERE
  83     aqorders.basketno=aqbasket.basketno AND
  84     budget_id=? AND
  85     datecancellationprinted IS NULL AND
  86     datereceived IS NOT NULL
  87     GROUP BY aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
  88              tleft,
  89              budgetdate, entrydate,
  90              aqbasket.booksellerid,
  91              title,
  92              aqorders.invoiceid,
  93              aqinvoices.invoicenumber,
  94              quantityreceived,
  95              $unitprice_field,
  96              datereceived,
  97              aqbooksellers.name
  98
  99 EOQ
 100 my $sth = $dbh->prepare($query);
 101 $sth->execute($bookfund);
 102 if ( $sth->err ) {
 103     die "An error occurred fetching records: " . $sth->errstr;
 104 }
 105 my $subtotal = 0;
 106 my @spent;
 107 while ( my $data = $sth->fetchrow_hashref ) {
 108     my $recv = $data->{'quantityreceived'};
 109     $data->{'itemtypes'} = [split('\|', $data->{itypes})];
 110     if ( $recv > 0 ) {
 111         my $rowtotal = $recv * get_rounded_price($data->{$unitprice_field});
 112         $data->{'rowtotal'}  = sprintf( "%.2f", $rowtotal );
 113         $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{$unitprice_field} );
 114         $subtotal += $rowtotal;
 115         push @spent, $data;
 116     }
 117
 118 }
 119
 120 my $total = $subtotal;
 121 $query = qq{
 122     SELECT invoiceid, invoicenumber, shipmentcost
 123     FROM aqinvoices
 124     WHERE shipmentcost_budgetid = ?
 125 };
 126 $sth = $dbh->prepare($query);
 127 $sth->execute($bookfund);
 128 my @shipmentcosts;
 129 while (my $data = $sth->fetchrow_hashref) {
 130     push @shipmentcosts, {
 131         shipmentcost => sprintf("%.2f", $data->{shipmentcost}),
 132         invoiceid => $data->{invoiceid},
 133         invoicenumber => $data->{invoicenumber}
 134     };
 135     $total += $data->{shipmentcost};
 136 }
 137 $sth->finish;
 138
 139 my $adjustments = Koha::Acquisition::Invoice::Adjustments->search({budget_id => $bookfund, closedate => { '!=' => undef } }, { prefetch => 'invoiceid' },  );
 140 while ( my $adj = $adjustments->next ){
 141     $total += $adj->adjustment;
 142 }
 143
 144 $total = sprintf( "%.2f", $total );
 145
 146 $template->param(
 147     fund => $bookfund,
 148     spent => \@spent,
 149     subtotal => $subtotal,
 150     shipmentcosts => \@shipmentcosts,
 151     adjustments => $adjustments,
 152     total => $total,
 153     fund_code => $fund_code
 154 );
 155
 156 output_html_with_http_headers $input, $cookie, $template->output;