publicationyear => $bd->{'publicationyear'},
publishercode => $bd->{'publishercode'},
collectiontitle => $bd->{'collectiontitle'},
- notes => $order->{'notes'},
+ notes => $order->{'order_vendornote'},
quantity => $order->{'quantity'},
rrp => $order->{'rrp'},
deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
rrp => $order->{rrp},
discount => $bookseller->{discount},
ecost => $order->{ecost},
- notes => $order->{notes},
+ notes => $order->{order_vendornote},
entrydate => $order->{entrydate},
booksellername => $bookseller->{name},
bookselleraddress => $bookseller->{address1},
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
$sth->execute($basketno);
- $sth->finish;
+ return;
}
#------------------------------------------------------------#
my $sth = $dbh->prepare($query);
$sth->execute(@params);
- $sth->finish;
+ return;
}
#------------------------------------------------------------#
my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
my $sth2 = $dbh->prepare($query2);
$sth2->execute($contractnumber,$basketno);
- $sth2->finish;
}
- $sth->finish;
+ return;
}
#------------------------------------------------------------#
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
$sth->execute($booksellerid);
- my $results = $sth->fetchall_arrayref({});
- $sth->finish;
- return $results
+ return $sth->fetchall_arrayref({});
}
=head3 GetBasketsInfosByBookseller
return unless $supplierid;
my $dbh = C4::Context->dbh;
- my $query = qq{
+ my $query = q{
SELECT aqbasket.*,
SUM(aqorders.quantity) AS total_items,
+ SUM(
+ IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
+ ) AS total_items_cancelled,
COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
SUM(
IF(aqorders.datereceived IS NULL
FROM aqbasket
LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
WHERE booksellerid = ?};
- if(!$allbaskets) {
+
+ unless ( $allbaskets ) {
$query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
}
$query.=" GROUP BY aqbasket.basketno";
my $sth = $dbh->prepare($query);
$sth->execute($supplierid);
- return $sth->fetchall_arrayref({});
+ my $baskets = $sth->fetchall_arrayref({});
+
+ # Retrieve the number of biblios cancelled
+ my $cancelled_biblios = $dbh->selectall_hashref( q|
+ SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
+ FROM aqbasket
+ LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
+ WHERE booksellerid = ?
+ AND aqorders.orderstatus = 'cancelled'
+ GROUP BY aqbasket.basketno
+ |, 'basketno', {}, $supplierid );
+ map {
+ $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
+ } @$baskets;
+
+ return $baskets;
}
=head3 GetBasketUsers
my $sth = $dbh->prepare($query);
$sth->execute($basketno);
my $results = $sth->fetchall_arrayref( {} );
- $sth->finish();
my @borrowernumbers;
foreach (@$results) {
};
my $sth = $dbh->prepare($query);
$sth->execute($basketno);
- $sth->finish();
$query = qq{
INSERT INTO aqbasketusers (basketno, borrowernumber)
foreach my $basketuser_id (@basketusers_ids) {
$sth->execute($basketno, $basketuser_id);
}
+ return;
}
=head3 CanUserManageBasket
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
$sth->execute($basketgroupid);
- my $results = $sth->fetchall_arrayref({});
- $sth->finish;
- return $results
+ return $sth->fetchall_arrayref({});
}
#------------------------------------------------------------#
$sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
$sth->execute($basketgroupinfo->{'id'}, $basketno);
- $sth->finish;
}
}
- $sth->finish;
+ return;
}
#------------------------------------------------------------#
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare($query);
$sth->execute($basketgroupid);
- $sth->finish;
+ return;
}
#------------------------------------------------------------#
$basketgroup = &GetBasketgroup($basketgroupid);
-Returns a reference to the hash containing all infermation about the basketgroup.
+Returns a reference to the hash containing all information about the basketgroup.
=cut
sub GetBasketgroup {
my $basketgroupid = shift;
die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
- my $query = "SELECT * FROM aqbasketgroups WHERE id=?";
my $dbh = C4::Context->dbh;
- my $sth = $dbh->prepare($query);
- $sth->execute($basketgroupid);
- my $result = $sth->fetchrow_hashref;
- $sth->finish;
- return $result
+ my $result_set = $dbh->selectall_arrayref(
+ 'SELECT * FROM aqbasketgroups WHERE id=?',
+ { Slice => {} },
+ $basketgroupid
+ );
+ return $result_set->[0]; # id is unique
}
#------------------------------------------------------------#
sub GetOrders {
my ( $basketno, $orderby ) = @_;
+ return () unless $basketno;
my $dbh = C4::Context->dbh;
my $query ="
SELECT biblio.*,biblioitems.*,
$orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
$query .= " ORDER BY $orderby";
- my $sth = $dbh->prepare($query);
- $sth->execute($basketno);
- my $results = $sth->fetchall_arrayref({});
- $sth->finish;
- return @$results;
+ my $result_set =
+ $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
+ return @{$result_set};
+
}
#------------------------------------------------------------#
LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
WHERE aqorders.biblionumber=?
";
- my $sth = $dbh->prepare($query);
- $sth->execute($biblionumber);
- my $results = $sth->fetchall_arrayref({});
- $sth->finish;
- return @$results;
+ my $result_set =
+ $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
+ return @{$result_set};
+
}
#------------------------------------------------------------#
sub GetOrder {
my ($ordernumber) = @_;
+ return unless $ordernumber;
+
my $dbh = C4::Context->dbh;
my $query = qq{SELECT
aqorders.*,
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
WHERE aqorders.basketno = aqbasket.basketno
AND ordernumber=?};
- my $sth= $dbh->prepare($query);
- $sth->execute($ordernumber);
- my $data = $sth->fetchrow_hashref;
- $data->{orderdate} = format_date( $data->{orderdate} );
- $sth->finish;
- return $data;
+ my $result_set =
+ $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
+
+ # result_set assumed to contain 1 match
+ return $result_set->[0];
}
=head3 GetLastOrderNotReceivedFromSubscriptionid
AND aqorders.datereceived IS NULL
LIMIT 1
|;
- my $sth = $dbh->prepare( $query );
- $sth->execute( $subscriptionid );
- my $order = $sth->fetchrow_hashref;
- return $order;
+ my $result_set =
+ $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
+
+ # result_set assumed to contain 1 match
+ return $result_set->[0];
}
=head3 GetLastOrderReceivedFromSubscriptionid
ORDER BY ordernumber DESC
LIMIT 1
|;
- my $sth = $dbh->prepare( $query );
- $sth->execute( $subscriptionid, $subscriptionid );
- my $order = $sth->fetchrow_hashref;
- return $order;
+ my $result_set =
+ $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
+
+ # result_set assumed to contain 1 match
+ return $result_set->[0];
}
=item defaults entrydate to Now
-The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".
+The following keys are used: "biblionumber", "title", "basketno", "quantity", "order_vendornote", "order_internalnote", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".
=back
}
$query .= "timestamp=NOW() WHERE ordernumber=?";
-# push(@params, $specorderinfo{'ordernumber'});
push(@params, $orderinfo->{'ordernumber'} );
$sth = $dbh->prepare($query);
$sth->execute(@params);
- $sth->finish;
+ return;
}
#------------------------------------------------------------#
=head3 ModReceiveOrder
- &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
- $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
+ &ModReceiveOrder({
+ biblionumber => $biblionumber,
+ ordernumber => $ordernumber,
+ quantityreceived => $quantityreceived,
+ user => $user,
+ cost => $cost,
+ ecost => $ecost,
+ invoiceid => $invoiceid,
+ rrp => $rrp,
+ budget_id => $budget_id,
+ datereceived => $datereceived,
+ received_itemnumbers => \@received_itemnumbers,
+ order_internalnote => $order_internalnote,
+ order_vendornote => $order_vendornote,
+ });
Updates an order, to reflect the fact that it was received, at least
in part. All arguments not mentioned below update the fields with the
sub ModReceiveOrder {
- my (
- $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
- $invoiceid, $rrp, $budget_id, $datereceived, $received_items
- )
- = @_;
+ my ( $params ) = @_;
+ my $biblionumber = $params->{biblionumber};
+ my $ordernumber = $params->{ordernumber};
+ my $quantrec = $params->{quantityreceived};
+ my $user = $params->{user};
+ my $cost = $params->{cost};
+ my $ecost = $params->{ecost};
+ my $invoiceid = $params->{invoiceid};
+ my $rrp = $params->{rrp};
+ my $budget_id = $params->{budget_id};
+ my $datereceived = $params->{datereceived};
+ my $received_items = $params->{received_items};
+ my $order_internalnote = $params->{order_internalnote};
+ my $order_vendornote = $params->{order_vendornote};
my $dbh = C4::Context->dbh;
$datereceived = C4::Dates->output('iso') unless $datereceived;
);
}
- my $sth=$dbh->prepare("
- SELECT * FROM aqorders
- WHERE biblionumber=? AND aqorders.ordernumber=?");
+ my $result_set = $dbh->selectall_arrayref(
+q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
+ { Slice => {} }, $biblionumber, $ordernumber
+ );
- $sth->execute($biblionumber,$ordernumber);
- my $order = $sth->fetchrow_hashref();
- $sth->finish();
+ # we assume we have a unique order
+ my $order = $result_set->[0];
my $new_ordernumber = $ordernumber;
if ( $order->{quantity} > $quantrec ) {
# without received items (the quantity is decreased),
# the second part is a new order line with quantity=quantityrec
# (entirely received)
- $sth=$dbh->prepare("
+ my $query = q|
UPDATE aqorders
SET quantity = ?,
- orderstatus = 'partial'
- WHERE ordernumber = ?
- ");
-
- $sth->execute($order->{quantity} - $quantrec, $ordernumber);
+ orderstatus = 'partial'|;
+ $query .= q|, order_internalnote = ?| if defined $order_internalnote;
+ $query .= q|, order_vendornote = ?| if defined $order_vendornote;
+ $query .= q| WHERE ordernumber = ?|;
+ my $sth = $dbh->prepare($query);
- $sth->finish;
+ $sth->execute(
+ $order->{quantity} - $quantrec,
+ ( defined $order_internalnote ? $order_internalnote : () ),
+ ( defined $order_vendornote ? $order_vendornote : () ),
+ $ordernumber
+ );
delete $order->{'ordernumber'};
$order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
}
}
} else {
- $sth=$dbh->prepare("update aqorders
- set quantityreceived=?,datereceived=?,invoiceid=?,
- unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'
- where biblionumber=? and ordernumber=?");
- $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,$biblionumber,$ordernumber);
- $sth->finish;
+ my $query = q|
+ update aqorders
+ set quantityreceived=?,datereceived=?,invoiceid=?,
+ unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'|;
+ $query .= q|, order_internalnote = ?| if defined $order_internalnote;
+ $query .= q|, order_vendornote = ?| if defined $order_vendornote;
+ $query .= q| where biblionumber=? and ordernumber=?|;
+ my $sth = $dbh->prepare( $query );
+ $sth->execute(
+ $quantrec,
+ $datereceived,
+ $invoiceid,
+ $cost,
+ $rrp,
+ $ecost,
+ $budget_id,
+ ( defined $order_internalnote ? $order_internalnote : () ),
+ ( defined $order_vendornote ? $order_vendornote : () ),
+ $biblionumber,
+ $ordernumber
+ );
}
return ($datereceived, $new_ordernumber);
}
basketno => $basketno,
owner => $owner,
pending => $pending
+ ordered => $ordered
});
Searches for orders.
C<$owner> Finds order for the logged in user.
C<$pending> Finds pending orders. Ignores completed and cancelled orders.
+C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
C<@results> is an array of references-to-hash with the keys are fields
my $basketgroupname = $params->{basketgroupname};
my $owner = $params->{owner};
my $pending = $params->{pending};
+ my $ordered = $params->{ordered};
+ my $biblionumber = $params->{biblionumber};
my $dbh = C4::Context->dbh;
my @args = ();
biblio.*,
biblioitems.isbn,
biblioitems.biblioitemnumber,
+ aqbasket.authorisedby,
+ aqbasket.booksellerid,
aqbasket.closedate,
aqbasket.creationdate,
aqbasket.basketname,
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
- WHERE (datecancellationprinted is NULL)
};
+ # If we search on ordernumber, we retrieve the transfered order if a transfer has been done.
$query .= q{
- AND (quantity > quantityreceived OR quantityreceived is NULL)
- } if $pending;
+ LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
+ } if $ordernumber;
+
+ $query .= q{
+ WHERE (datecancellationprinted is NULL)
+ };
+
+ if ( $pending or $ordered ) {
+ $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)};
+ }
+ if ( $ordered ) {
+ $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
+ }
my $userenv = C4::Context->userenv;
if ( C4::Context->preference("IndependentBranches") ) {
- if ( ( $userenv ) and ( $userenv->{flags} != 1 ) ) {
+ unless ( C4::Context->IsSuperLibrarian() ) {
$query .= q{
AND (
borrowers.branchcode = ?
}
if ( $ordernumber ) {
- $query .= ' AND (aqorders.ordernumber=?)';
- push @args, $ordernumber;
+ $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
+ push @args, ( $ordernumber, $ordernumber );
+ }
+ if ( $biblionumber ) {
+ $query .= 'AND aqorders.biblionumber = ?';
+ push @args, $biblionumber;
}
if( $search ) {
$query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
";
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 );
+ C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
}
-
+ return;
}
=head3 TransferOrder
my $dbh = C4::Context->dbh;
my ($query, $sth, $rv);
- $query = qq{
+ $query = q{
UPDATE aqorders
SET datecancellationprinted = CAST(NOW() AS date)
WHERE ordernumber = ?
$rv = $sth->execute($ordernumber);
delete $order->{'ordernumber'};
+ delete $order->{parent_ordernumber};
$order->{'basketno'} = $basketno;
my $newordernumber;
(undef, $newordernumber) = NewOrder($order);
- $query = qq{
+ $query = q{
UPDATE aqorders_items
SET ordernumber = ?
WHERE ordernumber = ?
my @query_params = ( $supplierid, $code, $datereceived );
if ( C4::Context->preference("IndependentBranches") ) {
- my $userenv = C4::Context->userenv;
- if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
+ unless ( C4::Context->IsSuperLibrarian() ) {
$strsth .= " and (borrowers.branchcode = ?
or borrowers.branchcode = '')";
- push @query_params, $userenv->{branch};
+ push @query_params, C4::Context->userenv->{branch};
}
}
$strsth .= " ORDER BY aqbasket.basketno";
- # ## parcelinformation : $strsth
- my $sth = $dbh->prepare($strsth);
- $sth->execute( @query_params );
- while ( my $data = $sth->fetchrow_hashref ) {
- push( @results, $data );
- }
- # ## countparcelbiblio: scalar(@results)
- $sth->finish;
+ my $result_set = $dbh->selectall_arrayref(
+ $strsth,
+ { Slice => {} },
+ @query_params);
- return @results;
+ return @{$result_set};
}
#------------------------------------------------------------#
$sth->execute( @query_params );
my $results = $sth->fetchall_arrayref({});
- $sth->finish;
- return @$results;
+ return @{$results};
}
#------------------------------------------------------------#
SELECT aqbasket.basketno,
aqorders.ordernumber,
DATE(aqbasket.closedate) AS orderdate,
+ aqbasket.basketname AS basketname,
+ aqbasket.basketgroupid AS basketgroupid,
+ aqbasketgroups.name AS basketgroupname,
aqorders.rrp AS unitpricesupplier,
aqorders.ecost AS unitpricelib,
aqorders.claims_count AS claims_count,
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
+ LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
WHERE aqorders.basketno = aqbasket.basketno
AND ( datereceived = ''
OR datereceived IS NULL
$from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
}
if (C4::Context->preference("IndependentBranches")
- && C4::Context->userenv
- && C4::Context->userenv->{flags} != 1 ) {
+ && !C4::Context->IsSuperLibrarian() ) {
$from .= ' AND borrowers.branchcode LIKE ? ';
push @query_params, C4::Context->userenv->{branch};
}
$sth->execute(@query_params);
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 $orderstatus = $params{orderstatus};
my $biblionumber = $params{biblionumber};
my $get_canceled_order = $params{get_canceled_order} || 0;
+ my $ordernumber = $params{ordernumber};
+ my $search_children_too = $params{search_children_too} || 0;
my @order_loop;
my $total_qty = 0;
push @query_params, "%$basketgroupname%";
}
+ if ($ordernumber) {
+ $query .= " AND (aqorders.ordernumber = ? ";
+ push @query_params, $ordernumber;
+ if ($search_children_too) {
+ $query .= " OR aqorders.parent_ordernumber = ? ";
+ push @query_params, $ordernumber;
+ }
+ $query .= ") ";
+ }
+
+
if ( C4::Context->preference("IndependentBranches") ) {
- my $userenv = C4::Context->userenv;
- if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
+ unless ( C4::Context->IsSuperLibrarian() ) {
$query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
- push @query_params, $userenv->{branch};
+ push @query_params, C4::Context->userenv->{branch};
}
}
$query .= " ORDER BY id";
WHERE booksellerid=?
AND contractenddate >= CURDATE( )";
}
- my $sth = $dbh->prepare($query);
- $sth->execute( $booksellerid );
- my @results;
- while (my $data = $sth->fetchrow_hashref ) {
- push(@results, $data);
- }
- $sth->finish;
- return @results;
+ my $result_set =
+ $dbh->selectall_arrayref( $query, { Slice => {} }, $booksellerid );
+ return @{$result_set};
}
#------------------------------------------------------------#
NULL
)
) AS receivedbiblios,
+ COUNT(
+ DISTINCT IF(
+ aqorders.subscriptionid IS NOT NULL,
+ aqorders.subscriptionid,
+ NULL
+ )
+ ) AS is_linked_to_subscriptions,
SUM(aqorders.quantityreceived) AS receiveditems
FROM aqinvoices
LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid