&GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket
&GetBasketAsCSV
&GetBasketsByBookseller &GetBasketsByBasketgroup
+ &GetBasketsInfosByBookseller
&ModBasketHeader
return $results
}
+=head3 GetBasketsInfosByBookseller
+
+ my $baskets = GetBasketsInfosByBookseller($supplierid);
+
+Returns in a arrayref of hashref all about booksellers baskets, plus:
+ total_biblios: Number of distinct biblios in basket
+ total_items: Number of items in basket
+ expected_items: Number of non-received items in basket
+
+=cut
+
+sub GetBasketsInfosByBookseller {
+ my ($supplierid) = @_;
+
+ return unless $supplierid;
+
+ my $dbh = C4::Context->dbh;
+ my $query = qq{
+ SELECT aqbasket.*,
+ SUM(aqorders.quantity) AS total_items,
+ COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
+ SUM(IF(aqorders.datereceived IS NULL, aqorders.quantity, 0)) AS expected_items
+ FROM aqbasket
+ LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
+ WHERE booksellerid = ?
+ GROUP BY aqbasket.basketno
+ };
+ my $sth = $dbh->prepare($query);
+ $sth->execute($supplierid);
+ return $sth->fetchall_arrayref({});
+}
+
+
#------------------------------------------------------------#
=head3 GetBasketsByBasketgroup
my $dbh = C4::Context->dbh;
my $strsth = "
SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
- surname,firstname,aqorders.*,biblio.*,biblioitems.isbn,
- aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname
+ surname,firstname,biblio.*,biblioitems.isbn,
+ aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname,
+ aqorders.*
FROM aqorders
LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
my $delay = shift;
my $supplierid = shift;
my $branch = shift;
+ my $estimateddeliverydatefrom = shift;
+ my $estimateddeliverydateto = shift;
my $dbh = C4::Context->dbh;
#BEWARE, order of parenthesis and LEFT JOIN is important for speed
my $dbdriver = C4::Context->config("db_scheme") || "mysql";
- my @query_params = ($delay); # delay is the first argument regardless
+ my @query_params = ();
my $select = "
SELECT aqbasket.basketno,
aqorders.ordernumber,
biblio.author, biblio.title,
biblioitems.publishercode AS publisher,
biblioitems.publicationyear,
+ ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
";
my $from = "
FROM
OR datereceived IS NULL
OR aqorders.quantityreceived < aqorders.quantity
)
+ AND aqbasket.closedate IS NOT NULL
AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
";
my $having = "";
$select .= "
aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
(aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
- DATEDIFF(CURDATE( ),closedate) AS latesince
+ DATEDIFF(CAST(now() AS date),closedate) AS latesince
";
- $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) ";
+ if ( defined $delay ) {
+ $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
+ push @query_params, $delay;
+ }
$having = "
HAVING quantity <> 0
AND unitpricesupplier <> 0
$select .= "
aqorders.quantity AS quantity,
aqorders.quantity * aqorders.rrp AS subtotal,
- (CURDATE - closedate) AS latesince
+ (CAST(now() AS date) - closedate) AS latesince
";
- $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) ";
+ if ( defined $delay ) {
+ $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
+ push @query_params, $delay;
+ }
}
if (defined $supplierid) {
$from .= ' AND aqbasket.booksellerid = ? ';
$from .= ' AND borrowers.branchcode LIKE ? ';
push @query_params, $branch;
}
+ if ( defined $estimateddeliverydatefrom ) {
+ $from .= '
+ AND aqbooksellers.deliverytime IS NOT NULL
+ AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
+ push @query_params, $estimateddeliverydatefrom;
+ }
+ if ( defined $estimateddeliverydatefrom and defined $estimateddeliverydateto ) {
+ $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
+ push @query_params, $estimateddeliverydateto;
+ } elsif ( defined $estimateddeliverydatefrom ) {
+ $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
+ }
if (C4::Context->preference("IndependantBranches")
&& C4::Context->userenv
&& C4::Context->userenv->{flags} != 1 ) {