X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=C4%2FAcquisition.pm;h=1bf41cbf65aaf8b5a1eccabccb8e6132cfb0bc4e;hb=e52b493611766b61a0018350adc590ab07e9c94b;hp=74aae85f4edb2a707ed573f617218ed30032c355;hpb=29689de399a8927547c36e53461c7766ecc604f0;p=koha_fer diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 74aae85f4e..1bf41cbf65 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,21 +43,24 @@ 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 &GetOrdersByBiblionumber + &NewOrder &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber &GetLateOrders &GetOrderFromItemnumber - &SearchOrder &GetHistory &GetRecentAcqui + &SearchOrders &GetHistory &GetRecentAcqui &ModReceiveOrder &CancelReceipt - &GetCancelledOrders + &GetCancelledOrders &TransferOrder &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid &NewOrderItem &ModItemOrder @@ -72,10 +75,12 @@ BEGIN { &CloseInvoice &ReopenInvoice &DelInvoice + &MergeInvoices &GetItemnumbersFromOrder &AddClaim + &GetBiblioCountByBasketno ); } @@ -157,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=? @@ -215,7 +219,7 @@ sub NewBasket { &CloseBasket($basketno); -close a basket (becomes unmodifiable,except for recieves) +close a basket (becomes unmodifiable, except for receives) =cut @@ -229,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'}); + } } #------------------------------------------------------------# @@ -294,7 +340,7 @@ sub GetBasketAsCSV { =head3 GetBasketGroupAsCSV -=over 4 +=over &GetBasketGroupAsCSV($basketgroupid); @@ -468,6 +514,7 @@ sub ModBasket { my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); $sth->execute(@params); + $sth->finish; } @@ -611,6 +658,149 @@ sub GetBasketsInfosByBookseller { 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; +} #------------------------------------------------------------# @@ -827,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("IndependentBranches") ) { - 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); @@ -939,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') "; @@ -1008,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; } @@ -1118,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; @@ -1265,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') @@ -1338,7 +1474,8 @@ sub ModReceiveOrder { # (entirely received) $sth=$dbh->prepare(" UPDATE aqorders - SET quantity = ? + SET quantity = ?, + orderstatus = 'partial' WHERE ordernumber = ? "); @@ -1347,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; @@ -1354,7 +1492,7 @@ sub ModReceiveOrder { $order->{'unitprice'} = $cost; $order->{'rrp'} = $rrp; $order->{ecost} = $ecost; - $order->{'orderstatus'} = 3; # totally received + $order->{'orderstatus'} = 'complete'; my $basketno; ( $basketno, $new_ordernumber ) = NewOrder($order); @@ -1366,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); @@ -1416,7 +1554,8 @@ sub CancelReceipt { UPDATE aqorders SET quantityreceived = ?, datereceived = ?, - invoiceid = ? + invoiceid = ?, + orderstatus = 'ordered' WHERE ordernumber = ? }; $sth = $dbh->prepare($query); @@ -1443,7 +1582,8 @@ sub CancelReceipt { } $query = qq{ UPDATE aqorders - SET quantity = ? + SET quantity = ?, + orderstatus = 'ordered' WHERE ordernumber = ? }; $sth = $dbh->prepare($query); @@ -1490,80 +1630,122 @@ 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). +C<$owner> Finds order for the logged in user. +C<$pending> Finds pending orders. Ignores completed and cancelled orders. -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 - -=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, + aqbasketgroups.id as basketgroupid, + aqbasketgroups.name as basketgroupname, + 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; - if($ordernumber){ - $query .= " AND (aqorders.ordernumber=?)"; + 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=?)'; 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({}); } #------------------------------------------------------------# @@ -1583,7 +1765,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); @@ -1596,6 +1778,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 @@ -1876,6 +2116,7 @@ sub GetLateOrders { $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); @@ -1901,10 +2142,19 @@ params: title author name + isbn + ean from_placed_on to_placed_on basket - search both basket name and number booksellerinvoicenumber + basketgroupname + budget + orderstatus (note that orderstatus '' will retrieve orders + of any status except cancelled) + biblionumber + get_canceled_order (if set to a true value, cancelled orders will + be included) returns: $order_loop is a list of hashrefs that each look like this: @@ -1944,6 +2194,11 @@ sub GetHistory { my $basket = $params{basket}; my $booksellerinvoicenumber = $params{booksellerinvoicenumber}; my $basketgroupname = $params{basketgroupname}; + my $budget = $params{budget}; + my $orderstatus = $params{orderstatus}; + my $biblionumber = $params{biblionumber}; + my $get_canceled_order = $params{get_canceled_order} || 0; + my @order_loop; my $total_qty = 0; my $total_qtyreceived = 0; @@ -1952,10 +2207,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, @@ -1970,22 +2225,42 @@ sub GetHistory { 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("IndependentBranches") ); + $query .= " WHERE 1 "; - $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') "; + unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) { + $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') "; + } my @query_params = (); + if ( $biblionumber ) { + $query .= " AND biblio.biblionumber = ?"; + push @query_params, $biblionumber; + } + if ( $title ) { $query .= " AND biblio.title LIKE ? "; $title =~ s/\s+/%/g; @@ -2001,7 +2276,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"; } @@ -2010,6 +2285,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; @@ -2020,6 +2300,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 = ? "; @@ -2055,9 +2340,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; } @@ -2158,7 +2443,7 @@ sub GetContract { =head3 AddClaim -=over 4 +=over &AddClaim($ordernumber); @@ -2167,6 +2452,7 @@ Add a claim for an order =back =cut + sub AddClaim { my ($ordernumber) = @_; my $dbh = C4::Context->dbh; @@ -2184,6 +2470,7 @@ sub AddClaim { my @invoices = GetInvoices( invoicenumber => $invoicenumber, + supplierid => $supplierid, suppliername => $suppliername, shipmentdatefrom => $shipmentdatefrom, # ISO format shipmentdateto => $shipmentdateto, # ISO format @@ -2228,6 +2515,8 @@ sub GetInvoices { FROM aqinvoices LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid + LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno + LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber @@ -2248,11 +2537,11 @@ sub GetInvoices { push @bind_args, "%$args{suppliername}%"; } if($args{shipmentdatefrom}) { - push @bind_strs, " aqinvoices.shipementdate >= ? "; + push @bind_strs, " aqinvoices.shipmentdate >= ? "; push @bind_args, $args{shipmentdatefrom}; } if($args{shipmentdateto}) { - push @bind_strs, " aqinvoices.shipementdate <= ? "; + push @bind_strs, " aqinvoices.shipmentdate <= ? "; push @bind_args, $args{shipmentdateto}; } if($args{billingdatefrom}) { @@ -2264,27 +2553,27 @@ sub GetInvoices { push @bind_args, $args{billingdateto}; } if($args{isbneanissn}) { - push @bind_strs, " (biblioitems.isbn LIKE ? OR biblioitems.ean LIKE ? OR biblioitems.issn LIKE ? ) "; + push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) "; push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn}; } if($args{title}) { - push @bind_strs, " biblio.title LIKE ? "; + push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') "; push @bind_args, $args{title}; } if($args{author}) { - push @bind_strs, " biblio.author LIKE ? "; + push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') "; push @bind_args, $args{author}; } if($args{publisher}) { - push @bind_strs, " biblioitems.publishercode LIKE ? "; + push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') "; push @bind_args, $args{publisher}; } if($args{publicationyear}) { - push @bind_strs, " biblioitems.publicationyear = ? "; - push @bind_args, $args{publicationyear}; + push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) "; + push @bind_args, $args{publicationyear}, $args{publicationyear}; } if($args{branchcode}) { - push @bind_strs, " aqorders.branchcode = ? "; + push @bind_strs, " borrowers.branchcode = ? "; push @bind_args, $args{branchcode}; } @@ -2354,7 +2643,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 @@ -2365,9 +2654,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 = ? }; @@ -2554,6 +2844,56 @@ sub DelInvoice { 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__