-
- #BEWARE, order of parenthesis and LEFT JOIN is important for speed
- my $strsth;
- my $dbdriver = C4::Context->config("db_scheme") || "mysql";
-
- # warn " $dbdriver";
- if ( $dbdriver eq "mysql" ) {
- $strsth = "SELECT aqbasket.basketno,
- DATE(aqbasket.closedate) as orderdate, aqorders.quantity - IFNULL(aqorders.quantityreceived,0) as quantity, aqorders.rrp as unitpricesupplier,aqorders.ecost as unitpricelib,
- (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp as subtotal, aqbookfund.bookfundname as budget, borrowers.branchcode as branch,
- aqbooksellers.name as supplier,
- aqorders.title, biblio.author, biblioitems.publishercode as publisher, biblioitems.publicationyear,
- DATEDIFF(CURDATE( ),closedate) AS latesince
- FROM
- (( (
- (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber
- ) LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber
- ) LEFT JOIN aqbookfund on aqorderbreakdown.bookfundid = aqbookfund.bookfundid
- ),(aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
- WHERE aqorders.basketno = aqbasket.basketno AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
- AND ((datereceived = '' OR datereceived is null) OR (aqorders.quantityreceived < aqorders.quantity) ) ";
- $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
- $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
- if ($branch);
- $strsth .=
- " AND borrowers.branchcode like \'"
- . C4::Context->userenv->{branch} . "\'"
- if ( C4::Context->preference("IndependantBranches")
- && C4::Context->userenv
- && C4::Context->userenv->{flags} != 1 );
- $strsth .=
-" HAVING quantity<>0 AND unitpricesupplier<>0 AND unitpricelib<>0 ORDER BY latesince,basketno,borrowers.branchcode, supplier ";
- }
- else {
- $strsth = "SELECT aqbasket.basketno,
- DATE(aqbasket.closedate) as orderdate,
- aqorders.quantity, aqorders.rrp as unitpricesupplier,aqorders.ecost as unitpricelib, aqorders.quantity * aqorders.rrp as subtotal
- aqbookfund.bookfundname as budget, borrowers.branchcode as branch,
- aqbooksellers.name as supplier,
- biblio.title, biblio.author, biblioitems.publishercode as publisher, biblioitems.publicationyear,
- (CURDATE - closedate) AS latesince
- FROM
- (( (
- (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber
- ) LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber
- ) LEFT JOIN aqbookfund on aqorderbreakdown.bookfundid = aqbookfund.bookfundid
- ),(aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
- WHERE aqorders.basketno = aqbasket.basketno AND (closedate < (CURDATE -(INTERVAL $delay DAY))
- AND ((datereceived = '' OR datereceived is null) OR (aqorders.quantityreceived < aqorders.quantity) ) ";
- $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
- $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
- if ($branch);
- $strsth .=
- " AND borrowers.branchcode like \'"
- . C4::Context->userenv->{branch} . "\'"
- if ( C4::Context->preference("IndependantBranches")
- && C4::Context->userenv->{flags} != 1 );
- $strsth .=
- " ORDER BY latesince,basketno,borrowers.branchcode, supplier";
- }
- warn "C4::Acquisition : getlateorders SQL:" . $strsth;
- my $sth = $dbh->prepare($strsth);
- $sth->execute;
- my @results;
- my $hilighted = 1;
- while ( my $data = $sth->fetchrow_hashref ) {
- $data->{hilighted} = $hilighted if ( $hilighted > 0 );
- $data->{orderdate} = format_date( $data->{orderdate} );
- push @results, $data;
- $hilighted = -$hilighted;
- }