acqui/spent.pl

   1 #!/usr/bin/perl
   2
   3 # script to show a breakdown of committed and spent budgets
   4
   5 # needs to be templated at some point
   6
   7 use C4::Context;
   8 use C4::Auth;
   9 use C4::Output;
  10 use strict;
  11 use CGI;
  12
  13 my $dbh      = C4::Context->dbh;
  14 my $input    = new CGI;
  15 my $bookfund = $input->param('bookfund');
  16 my $start    = $input->param('start');
  17 my $end      = $input->param('end');
  18
  19 my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
  20     {
  21         template_name   => "acqui/spent.tmpl",
  22         query           => $input,
  23         type            => "intranet",
  24         authnotrequired => 0,
  25         flagsrequired   => { acquisition => 1 },
  26         debug           => 1,
  27     }
  28 );
  29
  30 my $query =
  31 "Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived
  32     as qrev,subscription,title,itemtype,aqorders.biblionumber,aqorders.booksellerinvoicenumber,
  33     quantity-quantityreceived as tleft,
  34     aqorders.ordernumber
  35     as ordnum,entrydate,budgetdate,booksellerid,aqbasket.basketno
  36     from aqorders,aqorderbreakdown,aqbasket
  37     left join biblioitems on  biblioitems.biblioitemnumber=aqorders.biblioitemnumber
  38     where bookfundid=? and
  39     aqorders.ordernumber=aqorderbreakdown.ordernumber and
  40     aqorders.basketno=aqbasket.basketno
  41    and (
  42         (datereceived >= ? and datereceived < ?))
  43     and (datecancellationprinted is NULL or
  44            datecancellationprinted='0000-00-00')
  45
  46
  47   ";
  48 my $sth = $dbh->prepare($query);
  49 $sth->execute( $bookfund, $start, $end );
  50
  51 my $total = 0;
  52 my $toggle;
  53 my @spent_loop;
  54 while ( my $data = $sth->fetchrow_hashref ) {
  55     my $recv = $data->{'qrev'};
  56     if ( $recv > 0 ) {
  57         my $subtotal = $recv * $data->{'unitprice'};
  58         $data->{'subtotal'} = $subtotal;
  59         $data->{'unitprice'} += 0;
  60         $total               += $subtotal;
  61         if ($toggle) {
  62             $toggle = 0;
  63         }
  64         else {
  65             $toggle = 1;
  66         }
  67         $data->{'toggle'} = $toggle;
  68         push @spent_loop, $data;
  69     }
  70
  71 }
  72
  73 $template->param(
  74     SPENTLOOP => \@spent_loop,
  75     total     => $total
  76 );
  77 $sth->finish;
  78
  79 $dbh->disconnect;
  80 output_html_with_http_headers $input, $cookie, $template->output;