X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FAcquisition.pm;h=aabc0eddaab294d831820bba2dcdd2c2964a7a9b;hb=9abfb7a7645388b159bfb0a249d98f9fe29cde1f;hp=77010c5d5a88e72e80249c6fca75601e048e4823;hpb=8084a70a3b13a87a516844ee7b7f3665aa8c1e95;p=koha.git diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 77010c5d5a..aabc0eddaa 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -28,7 +28,7 @@ use MARC::Record; use C4::Suggestions; use C4::Biblio; use C4::Debug; -use C4::SQLHelper qw(InsertInTable); +use C4::SQLHelper qw(InsertInTable UpdateInTable); use C4::Bookseller qw(GetBookSellerFromId); use C4::Templates qw(gettemplate); @@ -43,23 +43,26 @@ BEGIN { require Exporter; @ISA = qw(Exporter); @EXPORT = qw( - &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket + &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket &GetBasketAsCSV &GetBasketGroupAsCSV &GetBasketsByBookseller &GetBasketsByBasketgroup &GetBasketsInfosByBookseller + &GetBasketUsers &ModBasketUsers + &CanUserManageBasket + &ModBasketHeader &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup &GetBasketgroups &ReOpenBasketgroup - &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders - &GetOrderNumber &GetLateOrders &GetOrderFromItemnumber - &SearchOrder &GetHistory &GetRecentAcqui - &ModReceiveOrder &CancelReceipt &ModOrderBiblioitemNumber - &GetCancelledOrders + &NewOrder &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber + &GetLateOrders &GetOrderFromItemnumber + &SearchOrders &GetHistory &GetRecentAcqui + &ModReceiveOrder &CancelReceipt + &GetCancelledOrders &TransferOrder &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid - &NewOrderItem &ModOrderItem &ModItemOrder + &NewOrderItem &ModItemOrder &GetParcels &GetParcel &GetContracts &GetContract @@ -71,10 +74,13 @@ BEGIN { &ModInvoice &CloseInvoice &ReopenInvoice + &DelInvoice + &MergeInvoices &GetItemnumbersFromOrder &AddClaim + &GetBiblioCountByBasketno ); } @@ -156,8 +162,7 @@ sub GetBasket { my $dbh = C4::Context->dbh; my $query = " SELECT aqbasket.*, - concat( b.firstname,' ',b.surname) AS authorisedbyname, - b.branchcode AS branch + concat( b.firstname,' ',b.surname) AS authorisedbyname FROM aqbasket LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber WHERE basketno=? @@ -214,7 +219,7 @@ sub NewBasket { &CloseBasket($basketno); -close a basket (becomes unmodifiable,except for recieves) +close a basket (becomes unmodifiable, except for receives) =cut @@ -228,6 +233,48 @@ sub CloseBasket { "; my $sth = $dbh->prepare($query); $sth->execute($basketno); + + my @orders = GetOrders($basketno); + foreach my $order (@orders) { + $query = qq{ + UPDATE aqorders + SET orderstatus = 'ordered' + WHERE ordernumber = ?; + }; + $sth = $dbh->prepare($query); + $sth->execute($order->{'ordernumber'}); + } +} + +=head3 ReopenBasket + + &ReopenBasket($basketno); + +reopen a basket + +=cut + +sub ReopenBasket { + my ($basketno) = @_; + my $dbh = C4::Context->dbh; + my $query = " + UPDATE aqbasket + SET closedate=NULL + WHERE basketno=? + "; + my $sth = $dbh->prepare($query); + $sth->execute($basketno); + + my @orders = GetOrders($basketno); + foreach my $order (@orders) { + $query = qq{ + UPDATE aqorders + SET orderstatus = 'new' + WHERE ordernumber = ?; + }; + $sth = $dbh->prepare($query); + $sth->execute($order->{'ordernumber'}); + } } #------------------------------------------------------------# @@ -293,7 +340,7 @@ sub GetBasketAsCSV { =head3 GetBasketGroupAsCSV -=over 4 +=over &GetBasketGroupAsCSV($basketgroupid); @@ -467,6 +514,7 @@ sub ModBasket { my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); $sth->execute(@params); + $sth->finish; } @@ -568,7 +616,11 @@ sub GetBasketsByBookseller { =head3 GetBasketsInfosByBookseller - my $baskets = GetBasketsInfosByBookseller($supplierid); + my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets); + +The optional second parameter allbaskets is a boolean allowing you to +select all baskets from the supplier; by default only active baskets (open or +closed but still something to receive) are returned. Returns in a arrayref of hashref all about booksellers baskets, plus: total_biblios: Number of distinct biblios in basket @@ -578,7 +630,7 @@ Returns in a arrayref of hashref all about booksellers baskets, plus: =cut sub GetBasketsInfosByBookseller { - my ($supplierid) = @_; + my ($supplierid, $allbaskets) = @_; return unless $supplierid; @@ -595,14 +647,160 @@ sub GetBasketsInfosByBookseller { ) AS expected_items FROM aqbasket LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno - WHERE booksellerid = ? - GROUP BY aqbasket.basketno - }; + WHERE booksellerid = ?}; + if(!$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({}); } +=head3 GetBasketUsers + + $basketusers_ids = &GetBasketUsers($basketno); + +Returns a list of all borrowernumbers that are in basket users list + +=cut + +sub GetBasketUsers { + my $basketno = shift; + + return unless $basketno; + + my $query = qq{ + SELECT borrowernumber + FROM aqbasketusers + WHERE basketno = ? + }; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute($basketno); + my $results = $sth->fetchall_arrayref( {} ); + $sth->finish(); + + my @borrowernumbers; + foreach (@$results) { + push @borrowernumbers, $_->{'borrowernumber'}; + } + + return @borrowernumbers; +} + +=head3 ModBasketUsers + + my @basketusers_ids = (1, 2, 3); + &ModBasketUsers($basketno, @basketusers_ids); + +Delete all users from basket users list, and add users in C<@basketusers_ids> +to this users list. + +=cut + +sub ModBasketUsers { + my ($basketno, @basketusers_ids) = @_; + + return unless $basketno; + + my $dbh = C4::Context->dbh; + my $query = qq{ + DELETE FROM aqbasketusers + WHERE basketno = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($basketno); + $sth->finish(); + + $query = qq{ + INSERT INTO aqbasketusers (basketno, borrowernumber) + VALUES (?, ?) + }; + $sth = $dbh->prepare($query); + foreach my $basketuser_id (@basketusers_ids) { + $sth->execute($basketno, $basketuser_id); + } +} + +=head3 CanUserManageBasket + + my $bool = CanUserManageBasket($borrower, $basket[, $userflags]); + my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]); + +Check if a borrower can manage a basket, according to system preference +AcqViewBaskets, user permissions and basket properties (creator, users list, +branch). + +First parameter can be either a borrowernumber or a hashref as returned by +C4::Members::GetMember. + +Second parameter can be either a basketno or a hashref as returned by +C4::Acquisition::GetBasket. + +The third parameter is optional. If given, it should be a hashref as returned +by C4::Auth::getuserflags. If not, getuserflags is called. + +If user is authorised to manage basket, returns 1. +Otherwise returns 0. + +=cut + +sub CanUserManageBasket { + my ($borrower, $basket, $userflags) = @_; + + if (!ref $borrower) { + $borrower = C4::Members::GetMember(borrowernumber => $borrower); + } + if (!ref $basket) { + $basket = GetBasket($basket); + } + + return 0 unless ($basket and $borrower); + + my $borrowernumber = $borrower->{borrowernumber}; + my $basketno = $basket->{basketno}; + + my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets'); + + if (!defined $userflags) { + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?"); + $sth->execute($borrowernumber); + my ($flags) = $sth->fetchrow_array; + $sth->finish; + + $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh); + } + + unless ($userflags->{superlibrarian} + || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all}) + || (!ref $userflags->{acquisition} && $userflags->{acquisition})) + { + if (not exists $userflags->{acquisition}) { + return 0; + } + + if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage}) + || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) { + return 0; + } + + if ($AcqViewBaskets eq 'user' + && $basket->{authorisedby} != $borrowernumber + && grep($borrowernumber, GetBasketUsers($basketno)) == 0) { + return 0; + } + + if ($AcqViewBaskets eq 'branch' && defined $basket->{branch} + && $basket->{branch} ne $borrower->{branchcode}) { + return 0; + } + } + + return 1; +} #------------------------------------------------------------# @@ -819,96 +1017,6 @@ sub GetBasketgroups { =head2 FUNCTIONS ABOUT ORDERS -=cut - -#------------------------------------------------------------# - -=head3 GetPendingOrders - -$orders = &GetPendingOrders($supplierid,$grouped,$owner,$basketno,$ordernumber,$search,$ean); - -Finds pending orders from the bookseller with the given ID. Ignores -completed and cancelled orders. - -C<$booksellerid> contains the bookseller identifier -C<$owner> contains 0 or 1. 0 means any owner. 1 means only the list of orders entered by the user itself. -C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket -in a single result line -C<$orders> is a reference-to-array; each element is a reference-to-hash. - -Used also by the filter in parcel.pl -I have added: - -C<$ordernumber> -C<$search> -C<$ean> - -These give the value of the corresponding field in the aqorders table -of the Koha database. - -Results are ordered from most to least recent. - -=cut - -sub GetPendingOrders { - my ($supplierid,$grouped,$owner,$basketno,$ordernumber,$search,$ean) = @_; - my $dbh = C4::Context->dbh; - my $strsth = " - SELECT ".($grouped?"count(*),":"")."aqbasket.basketno, - 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 biblio ON biblio.biblionumber=aqorders.biblionumber - LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber - WHERE (quantity > quantityreceived OR quantityreceived is NULL) - AND datecancellationprinted IS NULL"; - my @query_params; - my $userenv = C4::Context->userenv; - if ( C4::Context->preference("IndependantBranches") ) { - if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { - $strsth .= " AND (borrowers.branchcode = ? - or borrowers.branchcode = '')"; - push @query_params, $userenv->{branch}; - } - } - if ($supplierid) { - $strsth .= " AND aqbasket.booksellerid = ?"; - push @query_params, $supplierid; - } - if($ordernumber){ - $strsth .= " AND (aqorders.ordernumber=?)"; - push @query_params, $ordernumber; - } - if($search){ - $strsth .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)"; - push @query_params, ("%$search%","%$search%","%$search%"); - } - if ($ean) { - $strsth .= " AND biblioitems.ean = ?"; - push @query_params, $ean; - } - if ($basketno) { - $strsth .= " AND aqbasket.basketno=? "; - push @query_params, $basketno; - } - if ($owner) { - $strsth .= " AND aqbasket.authorisedby=? "; - push @query_params, $userenv->{'number'}; - } - $strsth .= " group by aqbasket.basketno" if $grouped; - $strsth .= " order by aqbasket.basketno"; - my $sth = $dbh->prepare($strsth); - $sth->execute( @query_params ); - my $results = $sth->fetchall_arrayref({}); - $sth->finish; - return $results; -} - -#------------------------------------------------------------# - =head3 GetOrders @orders = &GetOrders($basketnumber, $orderby); @@ -931,11 +1039,14 @@ sub GetOrders { SELECT biblio.*,biblioitems.*, aqorders.*, aqbudgets.*, - biblio.title + biblio.title, + aqorders_transfers.ordernumber_from AS transferred_from, + aqorders_transfers.timestamp AS transferred_from_timestamp 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 + LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber WHERE basketno=? AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') "; @@ -950,36 +1061,38 @@ sub GetOrders { } #------------------------------------------------------------# +=head3 GetOrdersByBiblionumber -=head3 GetOrderNumber - - $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber); - -Looks up the ordernumber with the given biblionumber and biblioitemnumber. - -Returns the number of this order. - -=over + @orders = &GetOrdersByBiblionumber($biblionumber); -=item C<$ordernumber> is the order number. +Looks up the orders with linked to a specific $biblionumber, including +cancelled orders and received orders. -=back +return : +C<@orders> is an array of references-to-hash, whose keys are the +fields from the aqorders, biblio, and biblioitems tables in the Koha database. =cut -sub GetOrderNumber { - my ( $biblionumber,$biblioitemnumber ) = @_; - my $dbh = C4::Context->dbh; - my $query = " - SELECT ordernumber - FROM aqorders - WHERE biblionumber=? - AND biblioitemnumber=? +sub GetOrdersByBiblionumber { + my $biblionumber = shift; + return unless $biblionumber; + 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 aqorders.biblionumber=? "; my $sth = $dbh->prepare($query); - $sth->execute( $biblionumber, $biblioitemnumber ); - - return $sth->fetchrow; + $sth->execute($biblionumber); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return @$results; } #------------------------------------------------------------# @@ -998,17 +1111,43 @@ C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha d sub GetOrder { my ($ordernumber) = @_; my $dbh = C4::Context->dbh; - my $query = " - SELECT biblioitems.*, biblio.*, aqorders.* - FROM aqorders - LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber - LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber - WHERE aqorders.ordernumber=? - - "; + my $query = qq{SELECT + aqorders.*, + biblio.title, + biblio.author, + aqbasket.basketname, + borrowers.branchcode, + biblioitems.publicationyear, + biblio.copyrightdate, + biblioitems.editionstatement, + biblioitems.isbn, + biblioitems.ean, + biblio.seriestitle, + biblioitems.publishercode, + 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, + aqbooksellers.name AS supplier, + aqbooksellers.id AS supplierid, + biblioitems.publishercode AS publisher, + ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, + DATE(aqbasket.closedate) AS orderdate, + aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive, + (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, + 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 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 + 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; } @@ -1100,7 +1239,7 @@ Else, the upcoming July 1st is used. =item defaults entrydate to Now -The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "biblioitemnumber", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id". +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". =back @@ -1108,7 +1247,7 @@ The following keys are used: "biblionumber", "title", "basketno", "quantity", "n sub NewOrder { my $orderinfo = shift; -#### ------------------------------ + my $dbh = C4::Context->dbh; my @params; @@ -1215,37 +1354,6 @@ sub ModOrder { #------------------------------------------------------------# -=head3 ModOrderItem - - &ModOrderItem(\%hashref); - -Modifies the itemnumber in the aqorders_items table. The input hash needs three entities: - -=over - -=item - itemnumber: the old itemnumber -=item - ordernumber: the order this item is attached to -=item - newitemnumber: the new itemnumber we want to attach the line to - -=back - -=cut - -sub ModOrderItem { - my $orderiteminfo = shift; - if (! $orderiteminfo->{'ordernumber'} || ! $orderiteminfo->{'itemnumber'} || ! $orderiteminfo->{'newitemnumber'}){ - die "Ordernumber, itemnumber and newitemnumber is required"; - } - - my $dbh = C4::Context->dbh; - - my $query = "UPDATE aqorders_items set itemnumber=? where itemnumber=? and ordernumber=?"; - my @params = ($orderiteminfo->{'newitemnumber'}, $orderiteminfo->{'itemnumber'}, $orderiteminfo->{'ordernumber'}); - my $sth = $dbh->prepare($query); - $sth->execute(@params); - return 0; -} - =head3 ModItemOrder ModItemOrder($itemnumber, $ordernumber); @@ -1271,29 +1379,6 @@ sub ModItemOrder { #------------------------------------------------------------# - -=head3 ModOrderBibliotemNumber - - &ModOrderBiblioitemNumber($biblioitemnumber,$ordernumber, $biblionumber); - -Modifies the biblioitemnumber for an existing order. -Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>. - -=cut - -#FIXME: is this used at all? -sub ModOrderBiblioitemNumber { - my ($biblioitemnumber,$ordernumber, $biblionumber) = @_; - my $dbh = C4::Context->dbh; - my $query = " - UPDATE aqorders - SET biblioitemnumber = ? - WHERE ordernumber = ? - AND biblionumber = ?"; - my $sth = $dbh->prepare($query); - $sth->execute( $biblioitemnumber, $ordernumber, $biblionumber ); -} - =head3 GetCancelledOrders my @orders = GetCancelledOrders($basketno, $orderby); @@ -1309,11 +1394,18 @@ sub GetCancelledOrders { my $dbh = C4::Context->dbh; my $query = " - SELECT biblio.*, biblioitems.*, aqorders.*, aqbudgets.* + SELECT + biblio.*, + biblioitems.*, + aqorders.*, + aqbudgets.*, + aqorders_transfers.ordernumber_to AS transferred_to, + aqorders_transfers.timestamp AS transferred_to_timestamp 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 + LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber WHERE basketno = ? AND (datecancellationprinted IS NOT NULL AND datecancellationprinted <> '0000-00-00') @@ -1335,8 +1427,7 @@ sub GetCancelledOrders { =head3 ModReceiveOrder &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user, - $unitprice, $invoiceid, $biblioitemnumber, - $bookfund, $rrp, \@received_itemnumbers); + $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers); 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 @@ -1383,7 +1474,8 @@ sub ModReceiveOrder { # (entirely received) $sth=$dbh->prepare(" UPDATE aqorders - SET quantity = ? + SET quantity = ?, + orderstatus = 'partial' WHERE ordernumber = ? "); @@ -1392,6 +1484,7 @@ sub ModReceiveOrder { $sth->finish; delete $order->{'ordernumber'}; + $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} ); $order->{'quantity'} = $quantrec; $order->{'quantityreceived'} = $quantrec; $order->{'datereceived'} = $datereceived; @@ -1399,8 +1492,9 @@ sub ModReceiveOrder { $order->{'unitprice'} = $cost; $order->{'rrp'} = $rrp; $order->{ecost} = $ecost; - $order->{'orderstatus'} = 3; # totally received - $new_ordernumber = NewOrder($order); + $order->{'orderstatus'} = 'complete'; + my $basketno; + ( $basketno, $new_ordernumber ) = NewOrder($order); if ($received_items) { foreach my $itemnumber (@$received_items) { @@ -1410,9 +1504,9 @@ sub ModReceiveOrder { } else { $sth=$dbh->prepare("update aqorders set quantityreceived=?,datereceived=?,invoiceid=?, - unitprice=?,rrp=?,ecost=? + unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete' where biblionumber=? and ordernumber=?"); - $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$biblionumber,$ordernumber); + $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,$biblionumber,$ordernumber); $sth->finish; } return ($datereceived, $new_ordernumber); @@ -1460,7 +1554,8 @@ sub CancelReceipt { UPDATE aqorders SET quantityreceived = ?, datereceived = ?, - invoiceid = ? + invoiceid = ?, + orderstatus = 'ordered' WHERE ordernumber = ? }; $sth = $dbh->prepare($query); @@ -1487,7 +1582,8 @@ sub CancelReceipt { } $query = qq{ UPDATE aqorders - SET quantity = ? + SET quantity = ?, + orderstatus = 'ordered' WHERE ordernumber = ? }; $sth = $dbh->prepare($query); @@ -1534,80 +1630,120 @@ sub CancelReceipt { #------------------------------------------------------------# -=head3 SearchOrder +=head3 SearchOrders -@results = &SearchOrder($search, $biblionumber, $complete); +@results = &SearchOrders({ + ordernumber => $ordernumber, + search => $search, + biblionumber => $biblionumber, + ean => $ean, + booksellerid => $booksellerid, + basketno => $basketno, + owner => $owner, + pending => $pending +}); Searches for orders. -C<$search> may take one of several forms: if it is an ISBN, -C<&ordersearch> returns orders with that ISBN. If C<$search> is an -order number, C<&ordersearch> returns orders with that order number -and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered -to be a space-separated list of search terms; in this case, all of the -terms must appear in the title (matching the beginning of title -words). - -If C<$complete> is C, the results will include only completed -orders. In any case, C<&ordersearch> ignores cancelled orders. - -C<&ordersearch> returns an array. -C<@results> is an array of references-to-hash with the following keys: - -=over 4 - -=item C +C<$owner> Finds order for the logged in user. +C<$pending> Finds pending orders. Ignores completed and cancelled orders. -=item C -=item C - -=item C - -=back +C<@results> is an array of references-to-hash with the keys are fields +from aqorders, biblio, biblioitems and aqbasket tables. =cut -sub SearchOrder { -#### -------- SearchOrder------------------------------- - my ( $ordernumber, $search, $ean, $supplierid, $basket ) = @_; +sub SearchOrders { + my ( $params ) = @_; + my $ordernumber = $params->{ordernumber}; + my $search = $params->{search}; + my $ean = $params->{ean}; + my $booksellerid = $params->{booksellerid}; + my $basketno = $params->{basketno}; + my $basketname = $params->{basketname}; + my $basketgroupname = $params->{basketgroupname}; + my $owner = $params->{owner}; + my $pending = $params->{pending}; my $dbh = C4::Context->dbh; my @args = (); - my $query = - "SELECT * - FROM aqorders + my $query = q{ + SELECT aqbasket.basketno, + borrowers.surname, + borrowers.firstname, + biblio.*, + biblioitems.isbn, + biblioitems.biblioitemnumber, + aqbasket.closedate, + aqbasket.creationdate, + aqbasket.basketname, + aqorders.* + FROM aqorders + LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno + LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id + LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber - LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno - WHERE (datecancellationprinted is NULL)"; + WHERE (datecancellationprinted is NULL) + }; + + $query .= q{ + AND (quantity > quantityreceived OR quantityreceived is NULL) + } if $pending; + + my $userenv = C4::Context->userenv; + if ( C4::Context->preference("IndependentBranches") ) { + if ( ( $userenv ) and ( $userenv->{flags} != 1 ) ) { + $query .= q{ + AND ( + borrowers.branchcode = ? + OR borrowers.branchcode = '' + ) + }; + push @args, $userenv->{branch}; + } + } - if($ordernumber){ - $query .= " AND (aqorders.ordernumber=?)"; + if ( $ordernumber ) { + $query .= ' AND (aqorders.ordernumber=?)'; push @args, $ordernumber; } - if($search){ - $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)"; + if( $search ) { + $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)'; push @args, ("%$search%","%$search%","%$search%"); } - if ($ean) { - $query .= " AND biblioitems.ean = ?"; + if ( $ean ) { + $query .= ' AND biblioitems.ean = ?'; push @args, $ean; } - if ($supplierid) { - $query .= "AND aqbasket.booksellerid = ?"; - push @args, $supplierid; + if ( $booksellerid ) { + $query .= 'AND aqbasket.booksellerid = ?'; + push @args, $booksellerid; + } + if( $basketno ) { + $query .= 'AND aqbasket.basketno = ?'; + push @args, $basketno; } - if($basket){ - $query .= "AND aqorders.basketno = ?"; - push @args, $basket; + if( $basketname ) { + $query .= 'AND aqbasket.basketname LIKE ?'; + push @args, "%$basketname%"; + } + if( $basketgroupname ) { + $query .= ' AND aqbasketgroups.name LIKE ?'; + push @args, "%$basketgroupname%"; + } + + if ( $owner ) { + $query .= ' AND aqbasket.authorisedby=? '; + push @args, $userenv->{'number'}; } + $query .= ' ORDER BY aqbasket.basketno'; + my $sth = $dbh->prepare($query); $sth->execute(@args); - my $results = $sth->fetchall_arrayref({}); - $sth->finish; - return $results; + return $sth->fetchall_arrayref({}); } #------------------------------------------------------------# @@ -1627,7 +1763,7 @@ sub DelOrder { my $dbh = C4::Context->dbh; my $query = " UPDATE aqorders - SET datecancellationprinted=now() + SET datecancellationprinted=now(), orderstatus='cancelled' WHERE biblionumber=? AND ordernumber=? "; my $sth = $dbh->prepare($query); @@ -1640,6 +1776,64 @@ sub DelOrder { } +=head3 TransferOrder + + my $newordernumber = TransferOrder($ordernumber, $basketno); + +Transfer an order line to a basket. +Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered +to BOOKSELLER on DATE' and create new order with internal note +'Transfered from BOOKSELLER on DATE'. +Move all attached items to the new order. +Received orders cannot be transfered. +Return the ordernumber of created order. + +=cut + +sub TransferOrder { + my ($ordernumber, $basketno) = @_; + + return unless ($ordernumber and $basketno); + + my $order = GetOrder( $ordernumber ); + return if $order->{datereceived}; + my $basket = GetBasket($basketno); + return unless $basket; + + my $dbh = C4::Context->dbh; + my ($query, $sth, $rv); + + $query = qq{ + UPDATE aqorders + SET datecancellationprinted = CAST(NOW() AS date) + WHERE ordernumber = ? + }; + $sth = $dbh->prepare($query); + $rv = $sth->execute($ordernumber); + + delete $order->{'ordernumber'}; + $order->{'basketno'} = $basketno; + my $newordernumber; + (undef, $newordernumber) = NewOrder($order); + + $query = qq{ + UPDATE aqorders_items + SET ordernumber = ? + WHERE ordernumber = ? + }; + $sth = $dbh->prepare($query); + $sth->execute($newordernumber, $ordernumber); + + $query = q{ + INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to) + VALUES (?, ?) + }; + $sth = $dbh->prepare($query); + $sth->execute($ordernumber, $newordernumber); + + return $newordernumber; +} + =head2 FUNCTIONS ABOUT PARCELS =cut @@ -1695,7 +1889,7 @@ sub GetParcel { AND aqorders.datereceived = ? "; my @query_params = ( $supplierid, $code, $datereceived ); - if ( C4::Context->preference("IndependantBranches") ) { + if ( C4::Context->preference("IndependentBranches") ) { my $userenv = C4::Context->userenv; if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { $strsth .= " and (borrowers.branchcode = ? @@ -1914,12 +2108,13 @@ sub GetLateOrders { if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) { $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)'; } - if (C4::Context->preference("IndependantBranches") + if (C4::Context->preference("IndependentBranches") && C4::Context->userenv && C4::Context->userenv->{flags} != 1 ) { $from .= ' AND borrowers.branchcode LIKE ? '; push @query_params, C4::Context->userenv->{branch}; } + $from .= " AND orderstatus <> 'cancelled' "; my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier"; $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params); my $sth = $dbh->prepare($query); @@ -1988,6 +2183,9 @@ sub GetHistory { my $basket = $params{basket}; my $booksellerinvoicenumber = $params{booksellerinvoicenumber}; my $basketgroupname = $params{basketgroupname}; + my $budget = $params{budget}; + my $orderstatus = $params{orderstatus}; + my @order_loop; my $total_qty = 0; my $total_qtyreceived = 0; @@ -1996,10 +2194,10 @@ sub GetHistory { my $dbh = C4::Context->dbh; my $query =" SELECT - biblio.title, - biblio.author, - biblioitems.isbn, - biblioitems.ean, + COALESCE(biblio.title, deletedbiblio.title) AS title, + COALESCE(biblio.author, deletedbiblio.author) AS author, + COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, + COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, aqorders.basketno, aqbasket.basketname, aqbasket.basketgroupid, @@ -2011,21 +2209,35 @@ sub GetHistory { aqorders.quantityreceived, aqorders.ecost, aqorders.ordernumber, + aqorders.invoiceid, aqinvoices.invoicenumber, aqbooksellers.id as id, - aqorders.biblionumber + aqorders.biblionumber, + aqorders.orderstatus, + aqorders.parent_ordernumber, + aqbudgets.budget_name + "; + $query .= ", aqbudgets.budget_id AS budget" if defined $budget; + $query .= " FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno 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 biblioitems ON biblioitems.biblionumber=aqorders.biblionumber LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber - LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid"; + LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id + LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid + LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber + LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber + "; + + if ( C4::Context->preference("IndependentBranches") ) { + $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"; + } - $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber" - if ( C4::Context->preference("IndependantBranches") ); + $query .= " WHERE 1 "; - $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') "; + $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') " if $orderstatus ne 'cancelled'; my @query_params = (); @@ -2044,7 +2256,7 @@ sub GetHistory { $query .= " AND biblioitems.isbn LIKE ? "; push @query_params, "%$isbn%"; } - if ( defined $ean and $ean ) { + if ( $ean ) { $query .= " AND biblioitems.ean = ? "; push @query_params, "$ean"; } @@ -2053,6 +2265,11 @@ sub GetHistory { push @query_params, "%$name%"; } + if ( $budget ) { + $query .= " AND aqbudgets.budget_id = ? "; + push @query_params, "$budget"; + } + if ( $from_placed_on ) { $query .= " AND creationdate >= ? "; push @query_params, $from_placed_on; @@ -2063,6 +2280,11 @@ sub GetHistory { push @query_params, $to_placed_on; } + if ( defined $orderstatus and $orderstatus ne '') { + $query .= " AND aqorders.orderstatus = ? "; + push @query_params, "$orderstatus"; + } + if ($basket) { if ($basket =~ m/^\d+$/) { $query .= " AND aqorders.basketno = ? "; @@ -2083,7 +2305,7 @@ sub GetHistory { push @query_params, "%$basketgroupname%"; } - if ( C4::Context->preference("IndependantBranches") ) { + if ( C4::Context->preference("IndependentBranches") ) { my $userenv = C4::Context->userenv; if ( $userenv && ($userenv->{flags} || 0) != 1 ) { $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) "; @@ -2098,9 +2320,9 @@ sub GetHistory { $line->{count} = $cnt++; $line->{toggle} = 1 if $cnt % 2; push @order_loop, $line; - $total_qty += $line->{'quantity'}; - $total_qtyreceived += $line->{'quantityreceived'}; - $total_price += $line->{'quantity'} * $line->{'ecost'}; + $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0; + $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0; + $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0; } return \@order_loop, $total_qty, $total_price, $total_qtyreceived; } @@ -2201,7 +2423,7 @@ sub GetContract { =head3 AddClaim -=over 4 +=over &AddClaim($ordernumber); @@ -2210,6 +2432,7 @@ Add a claim for an order =back =cut + sub AddClaim { my ($ordernumber) = @_; my $dbh = C4::Context->dbh; @@ -2397,7 +2620,7 @@ sub GetInvoiceDetails { } my $dbh = C4::Context->dbh; - my $query = qq{ + my $query = q{ SELECT aqinvoices.*, aqbooksellers.name AS suppliername FROM aqinvoices LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id @@ -2408,9 +2631,10 @@ sub GetInvoiceDetails { my $invoice = $sth->fetchrow_hashref; - $query = qq{ - SELECT aqorders.*, biblio.* + $query = q{ + SELECT aqorders.*, biblio.*, aqbasket.basketname FROM aqorders + LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber WHERE invoiceid = ? }; @@ -2564,6 +2788,89 @@ sub ReopenInvoice { $sth->execute($invoiceid); } +=head3 DelInvoice + + DelInvoice($invoiceid); + +Delete an invoice if there are no items attached to it. + +=cut + +sub DelInvoice { + my ($invoiceid) = @_; + + return unless $invoiceid; + + my $dbh = C4::Context->dbh; + my $query = qq{ + SELECT COUNT(*) + FROM aqorders + WHERE invoiceid = ? + }; + my $sth = $dbh->prepare($query); + $sth->execute($invoiceid); + my $res = $sth->fetchrow_arrayref; + if ( $res && $res->[0] == 0 ) { + $query = qq{ + DELETE FROM aqinvoices + WHERE invoiceid = ? + }; + my $sth = $dbh->prepare($query); + return ( $sth->execute($invoiceid) > 0 ); + } + return; +} + +=head3 MergeInvoices + + MergeInvoices($invoiceid, \@sourceids); + +Merge the invoices identified by the IDs in \@sourceids into +the invoice identified by $invoiceid. + +=cut + +sub MergeInvoices { + my ($invoiceid, $sourceids) = @_; + + return unless $invoiceid; + foreach my $sourceid (@$sourceids) { + next if $sourceid == $invoiceid; + my $source = GetInvoiceDetails($sourceid); + foreach my $order (@{$source->{'orders'}}) { + $order->{'invoiceid'} = $invoiceid; + ModOrder($order); + } + DelInvoice($source->{'invoiceid'}); + } + return; +} + +=head3 GetBiblioCountByBasketno + +$biblio_count = &GetBiblioCountByBasketno($basketno); + +Looks up the biblio's count that has basketno value $basketno + +Returns a quantity + +=cut + +sub GetBiblioCountByBasketno { + my ($basketno) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT COUNT( DISTINCT( biblionumber ) ) + FROM aqorders + WHERE basketno = ? + AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') + "; + + my $sth = $dbh->prepare($query); + $sth->execute($basketno); + return $sth->fetchrow; +} + 1; __END__