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 Koha::Acquisition::Invoice::Adjustments;
  38
  39 my $dbh      = C4::Context->dbh;
  40 my $input    = new CGI;
  41 my $bookfund = $input->param('fund');
  42 my $fund_code = $input->param('fund_code');
  43
  44 my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
  45     {
  46         template_name   => "acqui/spent.tt",
  47         query           => $input,
  48         type            => "intranet",
  49         authnotrequired => 0,
  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     itype,
  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 OR
  84         datecancellationprinted='0000-00-00') AND
  85     datereceived IS NOT NULL
  86     GROUP BY aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber,
  87              tleft,
  88              ecost, budgetdate, entrydate,
  89              aqbasket.booksellerid,
  90              itype,
  91              title,
  92              aqorders.invoiceid,
  93              aqinvoices.invoicenumber,
  94              quantityreceived,
  95              unitprice,
  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     if ( $recv > 0 ) {
 110         my $rowtotal = $recv * $data->{'unitprice_tax_included'};
 111         $data->{'rowtotal'}  = sprintf( "%.2f", $rowtotal );
 112         $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{'unitprice_tax_included'} );
 113         $subtotal += $rowtotal;
 114         push @spent, $data;
 115     }
 116
 117 }
 118
 119 my $total = $subtotal;
 120 $query = qq{
 121     SELECT invoicenumber, shipmentcost
 122     FROM aqinvoices
 123     WHERE shipmentcost_budgetid = ?
 124 };
 125 $sth = $dbh->prepare($query);
 126 $sth->execute($bookfund);
 127 my @shipmentcosts;
 128 while (my $data = $sth->fetchrow_hashref) {
 129     push @shipmentcosts, {
 130         shipmentcost => sprintf("%.2f", $data->{shipmentcost}),
 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 } }, { join => '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;