BEGIN {
# set the version for version checking
- $VERSION = 3.01;
+ $VERSION = 3.07.00.049;
require Exporter;
@ISA = qw(Exporter);
@EXPORT = qw(
&GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket
&GetBasketAsCSV
&GetBasketsByBookseller &GetBasketsByBasketgroup
+ &GetBasketsInfosByBookseller
&ModBasketHeader
&ModReceiveOrder &ModOrderBiblioitemNumber
&GetCancelledOrders
- &NewOrderItem &ModOrderItem
+ &NewOrderItem &ModOrderItem &ModItemOrder
&GetParcels &GetParcel
&GetContracts &GetContract
$dbh->do($query);
#find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
my $basket = $dbh->{'mysql_insertid'};
- ModBasketHeader($basket, $basketname || '', $basketnote || '', $basketbooksellernote || '', $basketcontractnumber || undef);
+ ModBasketHeader($basket, $basketname || '', $basketnote || '', $basketbooksellernote || '', $basketcontractnumber || undef, $booksellerid);
return $basket;
}
=head3 ModBasketHeader
- &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber);
+ &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
Modifies a basket's header.
=item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
+=item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
+
=back
=cut
sub ModBasketHeader {
- my ($basketno, $basketname, $note, $booksellernote, $contractnumber) = @_;
- my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=? WHERE basketno=?";
+ my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid) = @_;
+
+ my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=?, booksellerid=? WHERE basketno=?";
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
- $sth->execute($basketname,$note,$booksellernote,$basketno);
+ $sth->execute($basketname,$note,$booksellernote,$booksellerid,$basketno);
+
if ( $contractnumber ) {
my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
my $sth2 = $dbh->prepare($query2);
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
return 0;
}
+=head3 ModItemOrder
+
+ ModItemOrder($itemnumber, $ordernumber);
+
+Modifies the ordernumber of an item in aqorders_items.
+
+=cut
+
+sub ModItemOrder {
+ my ($itemnumber, $ordernumber) = @_;
+
+ return unless ($itemnumber and $ordernumber);
+
+ my $dbh = C4::Context->dbh;
+ my $query = qq{
+ UPDATE aqorders_items
+ SET ordernumber = ?
+ WHERE itemnumber = ?
+ };
+ my $sth = $dbh->prepare($query);
+ return $sth->execute($ordernumber, $itemnumber);
+}
+
#------------------------------------------------------------#
sub ModReceiveOrder {
my (
$biblionumber, $ordernumber, $quantrec, $user, $cost,
- $invoiceno, $freight, $rrp, $budget_id, $datereceived
+ $invoiceno, $freight, $rrp, $budget_id, $datereceived, $received_items
)
= @_;
my $dbh = C4::Context->dbh;
$datereceived = C4::Dates->output('iso') unless $datereceived;
- my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
+ my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
if ($suggestionid) {
ModSuggestion( {suggestionid=>$suggestionid,
STATUS=>'AVAILABLE',
$order->{'quantity'} -= $quantrec;
$order->{'quantityreceived'} = 0;
my $newOrder = NewOrder($order);
-} else {
+ # Change ordernumber in aqorders_items for items not received
+ my @orderitems = GetItemnumbersFromOrder( $order->{'ordernumber'} );
+ my $count = scalar @orderitems;
+
+ for (my $i=0; $i<$count; $i++){
+ foreach (@$received_items){
+ splice (@orderitems, $i, 1) if ($orderitems[$i] == $_);
+ }
+ }
+ foreach (@orderitems) {
+ ModItemOrder($_, $newOrder);
+ }
+ } else {
$sth=$dbh->prepare("update aqorders
set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
unitprice=?,freight=?,rrp=?
sub SearchOrder {
#### -------- SearchOrder-------------------------------
- my ($ordernumber, $search, $supplierid, $basket) = @_;
+ my ( $ordernumber, $search, $ean, $supplierid, $basket ) = @_;
my $dbh = C4::Context->dbh;
my @args = ();
$query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
push @args, ("%$search%","%$search%","%$search%");
}
- if($supplierid){
+ if ($ean) {
+ $query .= " AND biblioitems.ean = ?";
+ push @args, $ean;
+ }
+ if ($supplierid) {
$query .= "AND aqbasket.booksellerid = ?";
push @args, $supplierid;
}
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 ) {
my $title = $params{title};
my $author = $params{author};
my $isbn = $params{isbn};
+ my $ean = $params{ean};
my $name = $params{name};
my $from_placed_on = $params{from_placed_on};
my $to_placed_on = $params{to_placed_on};
my $basket = $params{basket};
my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
-
my @order_loop;
my $total_qty = 0;
my $total_qtyreceived = 0;
biblio.title,
biblio.author,
biblioitems.isbn,
+ biblioitems.ean,
aqorders.basketno,
- aqbasket.basketname,
- aqbasket.basketgroupid,
- aqbasketgroups.name as groupname,
+ aqbasket.basketname,
+ aqbasket.basketgroupid,
+ aqbasketgroups.name as groupname,
aqbooksellers.name,
- aqbasket.creationdate,
+ aqbasket.creationdate,
aqorders.datereceived,
aqorders.quantity,
aqorders.quantityreceived,
aqorders.biblionumber
FROM aqorders
LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
- LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
+ LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
$query .= " AND biblioitems.isbn LIKE ? ";
push @query_params, "%$isbn%";
}
-
+ if ( defined $ean and $ean ) {
+ $query .= " AND biblioitems.ean = ? ";
+ push @query_params, "$ean";
+ }
if ( $name ) {
$query .= " AND aqbooksellers.name LIKE ? ";
push @query_params, "%$name%";
projects / koha.git / blobdiff