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     itype,
  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              itype,
  92              title,
  93              aqorders.invoiceid,
  94              aqinvoices.invoicenumber,
  95              quantityreceived,
  96              unitprice_tax_included,
  97              datereceived,
  98              aqbooksellers.name
  99
 100 EOQ
 101 my $sth = $dbh->prepare($query);
 102 $sth->execute($bookfund);
 103 if ( $sth->err ) {
 104     die "An error occurred fetching records: " . $sth->errstr;
 105 }
 106 my $subtotal = 0;
 107 my @spent;
 108 while ( my $data = $sth->fetchrow_hashref ) {
 109     my $recv = $data->{'quantityreceived'};
 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 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         invoicenumber => $data->{invoicenumber}
 133     };
 134     $total += $data->{shipmentcost};
 135 }
 136 $sth->finish;
 137
 138 my $adjustments = Koha::Acquisition::Invoice::Adjustments->search({budget_id => $bookfund, closedate => { '!=' => undef } }, { prefetch => 'invoiceid' },  );
 139 while ( my $adj = $adjustments->next ){
 140     $total += $adj->adjustment;
 141 }
 142
 143 $total = sprintf( "%.2f", $total );
 144
 145 $template->param(
 146     fund => $bookfund,
 147     spent => \@spent,
 148     subtotal => $subtotal,
 149     shipmentcosts => \@shipmentcosts,
 150     adjustments => $adjustments,
 151     total => $total,
 152     fund_code => $fund_code
 153 );
 154
 155 output_html_with_http_headers $input, $cookie, $template->output;