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    = new CGI;
  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         authnotrequired => 0,
  51         flagsrequired   => { acquisition => '*' },
  52         debug           => 1,
  53     }
  54 );
  55
  56 my $query = <<EOQ;
  57 SELECT
  58     aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
  59     quantity-quantityreceived AS tleft,
  60     budgetdate, entrydate,
  61     aqbasket.booksellerid,
  62     GROUP_CONCAT(DISTINCT itype SEPARATOR '|') as itypes,
  63     title,
  64     aqorders.invoiceid,
  65     aqinvoices.invoicenumber,
  66     quantityreceived,
  67     unitprice_tax_included,
  68     datereceived,
  69     aqbooksellers.name as vendorname
  70 FROM (aqorders, aqbasket)
  71 LEFT JOIN biblio ON
  72     biblio.biblionumber=aqorders.biblionumber
  73 LEFT JOIN aqorders_items ON
  74     aqorders.ordernumber = aqorders_items.ordernumber
  75 LEFT JOIN items ON
  76     aqorders_items.itemnumber = items.itemnumber
  77 LEFT JOIN aqinvoices ON
  78     aqorders.invoiceid = aqinvoices.invoiceid
  79 LEFT JOIN aqbooksellers ON
  80     aqbasket.booksellerid = aqbooksellers.id
  81 WHERE
  82     aqorders.basketno=aqbasket.basketno AND
  83     budget_id=? AND
  84     (datecancellationprinted IS NULL OR
  85         datecancellationprinted='0000-00-00') 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_tax_included,
  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_tax_included'});
 112         $data->{'rowtotal'}  = sprintf( "%.2f", $rowtotal );
 113         $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{'unitprice_tax_included'} );
 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;