LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
WHERE booksellerid=?
- AND (quantity > quantityreceived OR quantityreceived is NULL)
AND datecancellationprinted IS NULL
AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)
";
if($closed){
- $strsth .= " AND closedate IS NOT NULL ";
+ $strsth .= "
+ AND (quantity > quantityreceived OR quantityreceived is NULL)
+ AND closedate IS NOT NULL ";
}
## FIXME Why 180 days ???
my @query_params = ( $supplierid );
# do a query for spent totals.
my $query = "
- Select distinct quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived
- as qrev,subscription,title,itype as itemtype,aqorders.biblionumber,aqorders.booksellerinvoicenumber,
+ Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived
+ as qrev,subscription,title,itemtype,aqorders.biblionumber,aqorders.booksellerinvoicenumber,
quantity-quantityreceived as tleft,
aqorders.ordernumber
- as ordnum,entrydate,budgetdate,aqbasket.booksellerid,aqbasket.basketno
- from aqorders
- inner join aqorderbreakdown on aqorderbreakdown.ordernumber = aqorders.ordernumber
- inner join aqbasket on aqbasket.basketno = aqorders.basketno
- left join items on items.biblionumber=aqorders.biblionumber
- where bookfundid=?
- and (datereceived >= ? and datereceived < ?)
+ as ordnum,entrydate,budgetdate,booksellerid,aqbasket.basketno
+ from aqorderbreakdown,aqbasket,aqorders
+ left join biblioitems on biblioitems.biblioitemnumber=aqorders.biblioitemnumber
+ where bookfundid=? and
+ aqorders.ordernumber=aqorderbreakdown.ordernumber and
+ aqorders.basketno=aqbasket.basketno
+ and (
+ (datereceived >= ? and datereceived < ?))
and (datecancellationprinted is NULL or
datecancellationprinted='0000-00-00')
and (closedate >= ? and closedate < ?)
my ($spent) = 0;
while ( my $data = $sth->fetchrow_hashref ) {
if($data->{datereceived}){
- if ( $data->{'subscription'} == 1 ) {
- $spent += $data->{'quantity'} * $data->{'unitprice'};
- }
- else {
- $spent += ( $data->{'unitprice'} ) * ($data->{'qrev'}?$data->{'qrev'}:0);
+ my $recv = $data->{'qrev'};
+ if ( $recv > 0 ) {
+ $spent += $recv * $data->{'unitprice'};
}
}