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 strict;
  36 use warnings;
  37 use CGI qw ( -utf8 );
  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     ecost, budgetdate, entrydate,
  60     aqbasket.booksellerid,
  61     itype,
  62     title,
  63     aqorders.invoiceid,
  64     aqinvoices.invoicenumber,
  65     quantityreceived,
  66     unitprice,
  67     datereceived
  68 FROM (aqorders, aqbasket)
  69 LEFT JOIN biblio ON
  70     biblio.biblionumber=aqorders.biblionumber
  71 LEFT JOIN aqorders_items ON
  72     aqorders.ordernumber = aqorders_items.ordernumber
  73 LEFT JOIN items ON
  74     aqorders_items.itemnumber = items.itemnumber
  75 LEFT JOIN aqinvoices ON
  76     aqorders.invoiceid = aqinvoices.invoiceid
  77 WHERE
  78     aqorders.basketno=aqbasket.basketno AND
  79     budget_id=? AND
  80     (datecancellationprinted IS NULL OR
  81         datecancellationprinted='0000-00-00') AND
  82     datereceived IS NOT NULL
  83     GROUP BY aqorders.ordernumber
  84 EOQ
  85 my $sth = $dbh->prepare($query);
  86 $sth->execute($bookfund);
  87 if ( $sth->err ) {
  88     die "An error occurred fetching records: " . $sth->errstr;
  89 }
  90 my $subtotal = 0;
  91 my @spent;
  92 while ( my $data = $sth->fetchrow_hashref ) {
  93     my $recv = $data->{'quantityreceived'};
  94     if ( $recv > 0 ) {
  95         my $rowtotal = $recv * $data->{'unitprice'};
  96         $data->{'rowtotal'}  = sprintf( "%.2f", $rowtotal );
  97         $data->{'unitprice'} = sprintf( "%.2f", $data->{'unitprice'} );
  98         $subtotal += $rowtotal;
  99         push @spent, $data;
 100     }
 101
 102 }
 103
 104 my $total = $subtotal;
 105 $query = qq{
 106     SELECT invoicenumber, shipmentcost
 107     FROM aqinvoices
 108     WHERE shipmentcost_budgetid = ?
 109 };
 110 $sth = $dbh->prepare($query);
 111 $sth->execute($bookfund);
 112 my @shipmentcosts;
 113 while (my $data = $sth->fetchrow_hashref) {
 114     push @shipmentcosts, {
 115         shipmentcost => sprintf("%.2f", $data->{shipmentcost}),
 116         invoicenumber => $data->{invoicenumber}
 117     };
 118     $total += $data->{shipmentcost};
 119 }
 120 $sth->finish;
 121
 122 $total = sprintf( "%.2f", $total );
 123
 124 $template->param(
 125     fund => $bookfund,
 126     spent => \@spent,
 127     subtotal => $subtotal,
 128     shipmentcosts => \@shipmentcosts,
 129     total => $total,
 130     fund_code => $fund_code
 131 );
 132
 133 output_html_with_http_headers $input, $cookie, $template->output;