3 # script to show a breakdown of committed and spent budgets
5 # needs to be templated at some point
13 my $dbh = C4::Context->dbh;
15 my $bookfund = $input->param('bookfund');
16 my $start = $input->param('start');
17 my $end = $input->param('end');
19 my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
21 template_name => "acqui/spent.tmpl",
25 flagsrequired => { acquisition => 1 },
30 #James Winter 3/4/2009: Original query does not select spent rows
31 # correctly due to missing joins between tables
34 "Select distinct quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived
35 as qrev,subscription,title,itype as itemtype,aqorders.biblionumber,aqorders.booksellerinvoicenumber,
36 quantity-quantityreceived as tleft,
38 as ordnum,entrydate,budgetdate,aqbasket.booksellerid,aqbasket.basketno
40 inner join aqorderbreakdown on aqorderbreakdown.ordernumber = aqorders.ordernumber
41 inner join aqbasket on aqbasket.basketno = aqorders.basketno
42 left join items on items.biblionumber=aqorders.biblionumber
43 where bookfundid=? and
44 aqorders.ordernumber=aqorderbreakdown.ordernumber and
45 aqorders.basketno=aqbasket.basketno
47 (datereceived >= ? and datereceived < ?))
48 and (datecancellationprinted is NULL or
49 datecancellationprinted='0000-00-00')
53 my $sth = $dbh->prepare($query);
54 $sth->execute( $bookfund, $start, $end );
59 while ( my $data = $sth->fetchrow_hashref ) {
60 my $recv = $data->{'qrev'};
62 my $subtotal = $recv * $data->{'unitprice'};
63 $data->{'subtotal'} = $subtotal;
64 $data->{'unitprice'} += 0;
72 $data->{'toggle'} = $toggle;
73 push @spent_loop, $data;
79 SPENTLOOP => \@spent_loop,
85 output_html_with_http_headers $input, $cookie, $template->output;