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
&GetOrderNumber &GetLateOrders &GetOrderFromItemnumber
&SearchOrder &GetHistory &GetRecentAcqui
&ModReceiveOrder &ModOrderBiblioitemNumber
+ &GetCancelledOrders
- &NewOrderItem &ModOrderItem
+ &NewOrderItem &ModOrderItem &ModItemOrder
&GetParcels &GetParcel
&GetContracts &GetContract
&GetItemnumbersFromOrder
+
+ &AddClaim
);
}
$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
LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
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)
- ";
- ## FIXME Why 180 days ???
+ AND datecancellationprinted IS NULL";
my @query_params = ( $supplierid );
my $userenv = C4::Context->userenv;
if ( C4::Context->preference("IndependantBranches") ) {
=cut
sub NewOrderItem {
- #my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
my ($itemnumber, $ordernumber) = @_;
my $dbh = C4::Context->dbh;
my $query = qq|
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);
+}
+
#------------------------------------------------------------#
$sth->execute( $biblioitemnumber, $ordernumber, $biblionumber );
}
+=head3 GetCancelledOrders
+
+ my @orders = GetCancelledOrders($basketno, $orderby);
+
+Returns cancelled orders for a basket
+
+=cut
+
+sub GetCancelledOrders {
+ my ( $basketno, $orderby ) = @_;
+
+ return () unless $basketno;
+
+ my $dbh = C4::Context->dbh;
+ my $query = "
+ SELECT biblio.*, biblioitems.*, aqorders.*, aqbudgets.*
+ FROM aqorders
+ LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
+ LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
+ LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
+ WHERE basketno = ?
+ AND (datecancellationprinted IS NOT NULL
+ AND datecancellationprinted <> '0000-00-00')
+ ";
+
+ $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
+ unless $orderby;
+ $query .= " ORDER BY $orderby";
+ my $sth = $dbh->prepare($query);
+ $sth->execute($basketno);
+ my $results = $sth->fetchall_arrayref( {} );
+
+ return @$results;
+}
+
+
#------------------------------------------------------------#
=head3 ModReceiveOrder
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;
-# warn "DATE BEFORE : $daterecieved";
-# $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved;
-# warn "DATE REC : $daterecieved";
$datereceived = C4::Dates->output('iso') unless $datereceived;
- my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
+ my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
if ($suggestionid) {
ModSuggestion( {suggestionid=>$suggestionid,
- STATUS=>'AVAILABLE',
- biblionumber=> $biblionumber}
- );
+ STATUS=>'AVAILABLE',
+ biblionumber=> $biblionumber}
+ );
}
my $sth=$dbh->prepare("
$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 $sth = $dbh->prepare($query);
$sth->execute( $bibnum, $ordernumber );
$sth->finish;
+ my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
+ foreach my $itemnumber (@itemnumbers){
+ C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
+ }
+
}
=head2 FUNCTIONS ABOUT PARCELS
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,
DATE(aqbasket.closedate) AS orderdate,
aqorders.rrp AS unitpricesupplier,
aqorders.ecost AS unitpricelib,
+ aqorders.claims_count AS claims_count,
+ aqorders.claimed_date AS claimed_date,
aqbudgets.budget_name AS budget,
borrowers.branchcode AS branch,
aqbooksellers.name AS supplier,
+ aqbooksellers.id AS supplierid,
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 @results;
while (my $data = $sth->fetchrow_hashref) {
$data->{orderdate} = format_date($data->{orderdate});
+ $data->{claimed_date} = format_date($data->{claimed_date});
push @results, $data;
}
return @results;
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";
my @query_params = ();
- if ( defined $title ) {
+ if ( $title ) {
$query .= " AND biblio.title LIKE ? ";
$title =~ s/\s+/%/g;
push @query_params, "%$title%";
}
- if ( defined $author ) {
+ if ( $author ) {
$query .= " AND biblio.author LIKE ? ";
push @query_params, "%$author%";
}
- if ( defined $isbn ) {
+ if ( $isbn ) {
$query .= " AND biblioitems.isbn LIKE ? ";
push @query_params, "%$isbn%";
}
-
- if ( defined $name ) {
+ if ( defined $ean and $ean ) {
+ $query .= " AND biblioitems.ean = ? ";
+ push @query_params, "$ean";
+ }
+ if ( $name ) {
$query .= " AND aqbooksellers.name LIKE ? ";
push @query_params, "%$name%";
}
- if ( defined $from_placed_on ) {
+ if ( $from_placed_on ) {
$query .= " AND creationdate >= ? ";
push @query_params, $from_placed_on;
}
- if ( defined $to_placed_on ) {
+ if ( $to_placed_on ) {
$query .= " AND creationdate <= ? ";
push @query_params, $to_placed_on;
}
$line->{count} = $cnt++;
$line->{toggle} = 1 if $cnt % 2;
push @order_loop, $line;
- $line->{creationdate} = format_date( $line->{creationdate} );
- $line->{datereceived} = format_date( $line->{datereceived} );
$total_qty += $line->{'quantity'};
$total_qtyreceived += $line->{'quantityreceived'};
$total_price += $line->{'quantity'} * $line->{'ecost'};
return $result;
}
+=head3 AddClaim
+
+=over 4
+
+&AddClaim($ordernumber);
+
+Add a claim for an order
+
+=back
+
+=cut
+sub AddClaim {
+ my ($ordernumber) = @_;
+ my $dbh = C4::Context->dbh;
+ my $query = "
+ UPDATE aqorders SET
+ claims_count = claims_count + 1,
+ claimed_date = CURDATE()
+ WHERE ordernumber = ?
+ ";
+ my $sth = $dbh->prepare($query);
+ $sth->execute($ordernumber);
+
+}
+
1;
__END__