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