From fc88d58a44c3aed9a9a1bc497508061b59690887 Mon Sep 17 00:00:00 2001 From: Mathieu Saby Date: Fri, 4 Apr 2014 09:36:52 +0200 Subject: [PATCH 1/2] Bug 10758 - Show bibliographic information of deleted records in acquisitions Content-Type: text/plain; charset="utf-8" (I removed the changes to Koha/Schema/Result/Aqorder.pm, as it should not be integrated in the main patch) Fully testable, but a second patch will be provided for fixing and improving the UTs Aim of the patch : when a record is deleted but used in an order, display information stored in deletedbiblio and deletedbiblioitems tables on some pages of acquisition module Note that this patch will only have real effects for records deleted AFTER its application. Changes : - modification of database structure : new column "deletedbiblionumber" in aqorders We keep the constraint between aqorders.biblionumber and biblio.biblionumber, but we create a new one between aqorders.deletedbiblionumber and deletedbiblio.biblionumber With this system, we can make a JOIN between aqorders and deleted(biblio/biblioitems) table, and retreive information. - modification of GetOrder, GetOrders, GetCancelledOrders and SearchOrders in C4::Acquisition : Those subs are now also getting bibliographic information from deleted tables deletedbiblio and deletedbiblioitems Only usefull or potentially usefull fields are retreived (no more biblio.* and biblioitems.*) For each field, I used COALESCE in SQL query. Ex: COALESCE (biblio.title,deletedbiblio.title) AS title - modification of DelBiblio in C4::Biblio Wen a record is deleted with DelBiblio, the sub checks if it used in an order If so, the biblionumber is deleted from aqorders.biblionumber (as before), but it is copied to aqorder.deletedbiblionumber - modification of files in acqui : basket.pl, neworderempty.pl To transmit the new key "deletedbiblionumber" to the templates - modification of templates in intranet/module/acqui : basket.tt, histsearch.tt, lateorders.tt, neworderempty.tt, parcel.tt Test plan : A. BEFORE applying the patch - create a basket with at least 4 orders,using different records - in the catalog, delete one of the record used by 1st order, whithout cancelling the order (there will be an alert, but you can do that) - return to the basket page (or refresh it) : the information about the record is lost - if you want, try to receive orders or to search orders : in those pages too the information for this record will be lost B. Apply the patch and run updatedatabase.pl C. Go back to your basket and refresh it - the information of the record deleted before the application of the patch is still lost ("Can't find title") - in the catalog, delete one of the record used by 2d order, whithout cancelling the order - return to the basket page (or refresh it) : the information about the record is still visible, with the mention (Deleted record) - Try to modify the 2d order : click on "Modifiy" on the right column of the table - you will get on neworderempty.pl, but with a message "(Deleted record, not editable)". You will be able to change accounting details but not bibliographic information (In a future development, we could imagine to recreate a record and in order to make the order editable even if the original record is deleted, but it would be dangerous to do so now) - in the basket, cancel the 3rd order AND the record (click on "Delete order and record") - in the "Cancelled orders", you still could see bibliographic information about the cancelled order, with the mention "(Deleted record)" D. Go on late orders page - check you can see information about 2d order (which is linked with a deleted record) E. Go on order advanced search page - fill the form with the title of 2d order and click on Search - check Koha can retreive the order, and show correct information F. Try to receive orders from the vendor used for your basket - in the filters on the left of the page which displays the table of pending orders, search the title of the 2d order - Koha should retreive the 2d order - you must have a mention "Deleted record, order cannot be received" above the title - you must not have a link "Receive" on the left of the table (In a future development, we could imagine to recreate a record to do the receipt, but it would be dangerous to do so now. The best to do if a record was deleted and the order not yet received is probably to cancel the order, and to recreate it by hand) --- C4/Acquisition.pm | 241 ++++++++++++++++---- C4/Biblio.pm | 29 ++- acqui/basket.pl | 2 +- acqui/neworderempty.pl | 1 + installer/data/mysql/kohastructure.sql | 2 + installer/data/mysql/updatedatabase.pl | 8 +- .../intranet-tmpl/prog/en/modules/acqui/basket.tt | 26 ++- .../prog/en/modules/acqui/histsearch.tt | 12 +- .../prog/en/modules/acqui/lateorders.tt | 4 + .../prog/en/modules/acqui/neworderempty.tt | 24 +- .../intranet-tmpl/prog/en/modules/acqui/parcel.tt | 18 +- 11 files changed, 287 insertions(+), 80 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 0cff235..55f14e8 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -1036,14 +1036,26 @@ sub GetBasketgroups { @orders = &GetOrders($basketnumber, $orderby); -Looks up the pending (non-cancelled) orders with the given basket -number. If C<$booksellerID> is non-empty, only orders from that seller -are returned. - -return : -C<&basket> returns a two-element array. 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. +Looks up the non-cancelled (pending and received) orders with the given basketnumber. +Parameters: +- C<$basketnumber> : mandatory parameter, the basket number +- C<$orderby> : optional parameter, used to sort the results (must be a valid expression +using some fields of aqorders, aqbugdets, biblio, deletedbiblio, +biblioitems, deletedbiblioitems, aqorders_transfers tables) +If C<$orderby> is not provided, results are sorted according to publishercode and title + +Return : +The returned C<@orders> is an array of references-to-hash, whose keys are: +- the fields from the aqorders table +- the fields from the aqbudgets table +- 2 fields from aqorders_transfers table : ordernumber_from, return as transferred_from +and timestamp, return as transferred_from_timestamp +- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate +- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, +editionstatement, issn, isbn, ean, itemtype, volume, number +If the record still exists, the biblionumber key contains the biblionumber +If the record have been deleted, the biblionumber key is blank, +and the deletedbiblionumber key contains the biblionumber of the deleted record. =cut @@ -1052,7 +1064,21 @@ sub GetOrders { return () unless $basketno; my $dbh = C4::Context->dbh; my $query =" - SELECT biblio.*,biblioitems.*, + SELECT + COALESCE(biblio.title, deletedbiblio.title) AS title, + COALESCE(biblio.author, deletedbiblio.author) AS author, + COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, + COALESCE(biblio.serial, deletedbiblio.serial) AS serial, + COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, + COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, + COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, + COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, + COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, + COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, + COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, + COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, + COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, + COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, aqorders.*, aqbudgets.*, aqorders_transfers.ordernumber_from AS transferred_from, @@ -1061,12 +1087,14 @@ sub GetOrders { 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 deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber + LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber WHERE basketno=? AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') "; - $orderby = "biblioitems.publishercode,biblio.title" unless $orderby; + $orderby = "COALESCE(biblioitems.publishercode,deletedbiblioitems.publishercode), COALESCE(biblio.title,deletedbiblio.title)" unless $orderby; $query .= " ORDER BY $orderby"; my $result_set = $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno ); @@ -1093,9 +1121,10 @@ sub GetOrdersByBiblionumber { return unless $biblionumber; my $dbh = C4::Context->dbh; my $query =" - SELECT biblio.*,biblioitems.*, - aqorders.*, - aqbudgets.* + 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 @@ -1116,8 +1145,26 @@ sub GetOrdersByBiblionumber { Looks up an order by order number. -Returns a reference-to-hash describing the order. The keys of -C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database. +Returns a reference-to-hash describing the order. +The keys of C<$order> are +- all fields from the aqorders table +- 2 fields from aqorders table returned with alternate names : rrp as unitpricesupplier, ecost as unitpricelib +- 1 field from aqbasket table : basketname +- 1 field from borrowers table : branchcode +- 1 field from aqbudgets table : budget_name returned as budget +- 2 fields from aqbooksellers : name returned as supplier, id returned as supplierid +- estimateddeliverydate : ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) +- quantity_to_receive : aqorders.quantity - COALESCE(aqorders.quantityreceived,0) +- subtotal : (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp +- orderdate : DATE (aqbasket.closedate) +- latesince : DATEDIFF(CURDATE( ),closedate) +- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate +- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, +editionstatement, issn, isbn, ean, itemtype, volume, number +If the record still exists, the biblionumber key contains the biblionumber +If the record have been deleted, the biblionumber key is blank, +and the deletedbiblionumber key contains the biblionumber of the deleted record. + =cut @@ -1126,19 +1173,26 @@ sub GetOrder { return unless $ordernumber; my $dbh = C4::Context->dbh; +# FIXME publishercode column should probably be returned only as publisher or publishercode, but we must first investigate templates to check the use of this value my $query = qq{SELECT + COALESCE(biblio.title, deletedbiblio.title) AS title, + COALESCE(biblio.author, deletedbiblio.author) AS author, + COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, + COALESCE(biblio.serial, deletedbiblio.serial) AS serial, + COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, + COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, + COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, + COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publisher, + COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, + COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, + COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, + COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, + COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, + COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, + COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, 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, @@ -1146,7 +1200,6 @@ sub GetOrder { 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, @@ -1154,6 +1207,8 @@ sub GetOrder { 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 deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber + LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.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 @@ -1396,9 +1451,28 @@ sub ModItemOrder { =head3 GetCancelledOrders - my @orders = GetCancelledOrders($basketno, $orderby); - -Returns cancelled orders for a basket + my @orders = GetCancelledOrders($basketnumber, $orderby); + +Looks up the cancelled orders with the given basketnumber. +Parameters: +- C<$basketnumber> : mandatory parameter, the basket number +- C<$orderby> : optional parameter, used to sort the results (must be a valid expression +using some fields of aqorders, aqbugdets, biblio, deletedbiblio, +biblioitems, deletedbiblioitems, aqorders_transfers tables) +If C<$orderby> is not provided, results are sorted according to publishercode and title + +Return : +The returned C<@orders> is an array of references-to-hash, whose keys are: +- the fields from the aqorders table +- the fields from the aqbudgets table +- 2 fields from aqorders_transfers table : ordernumber_from, return as transferred_from +and timestamp, return as transferred_from_timestamp +- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate +- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, +editionstatement, issn, isbn, ean, itemtype, volume, number +If the record still exists, the biblionumber key contains the biblionumber +If the record have been deleted, the biblionumber key is blank, +and the deletedbiblionumber key contains the biblionumber of the deleted record. =cut @@ -1410,8 +1484,20 @@ sub GetCancelledOrders { my $dbh = C4::Context->dbh; my $query = " SELECT - biblio.*, - biblioitems.*, + COALESCE(biblio.title, deletedbiblio.title) AS title, + COALESCE(biblio.author, deletedbiblio.author) AS author, + COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, + COALESCE(biblio.serial, deletedbiblio.serial) AS serial, + COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, + COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, + COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, + COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, + COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, + COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, + COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, + COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, + COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, + COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, aqorders.*, aqbudgets.*, aqorders_transfers.ordernumber_to AS transferred_to, @@ -1420,6 +1506,8 @@ sub GetCancelledOrders { 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 deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber + LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber WHERE basketno = ? AND (datecancellationprinted IS NOT NULL @@ -1717,7 +1805,11 @@ 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 -from aqorders, biblio, biblioitems and aqbasket tables. +from aqorders, biblio, deletedbiblio, biblioitems, deletedbiblioitems, +aqbasket and aqbasketgroups tables. +If the record still exists, the biblionumber key contains the biblionumber +If the record have been deleted, the biblionumber key is blank, +and the deletedbiblionumber key contains the biblionumber of the deleted record. =cut @@ -1742,9 +1834,20 @@ sub SearchOrders { SELECT aqbasket.basketno, borrowers.surname, borrowers.firstname, - biblio.*, - biblioitems.isbn, - biblioitems.biblioitemnumber, + COALESCE(biblio.title, deletedbiblio.title) AS title, + COALESCE(biblio.author, deletedbiblio.author) AS author, + COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, + COALESCE(biblio.serial, deletedbiblio.serial) AS serial, + COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, + COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, + COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, + COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, + COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, + COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, + COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, + COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, + COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, + COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, aqbasket.authorisedby, aqbasket.booksellerid, aqbasket.closedate, @@ -1759,6 +1862,8 @@ sub SearchOrders { 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 deletedbiblio ON aqorders.deletedbiblionumber=deletedbiblio.biblionumber + LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber }; # If we search on ordernumber, we retrieve the transfered order if a transfer has been done. @@ -1799,11 +1904,11 @@ sub SearchOrders { push @args, $biblionumber; } if( $search ) { - $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)'; + $query .= ' AND (COALESCE (biblio.title, deletedbiblio.title) LIKE ? OR COALESCE (biblio.author, deletedbiblio.author) LIKE ? OR COALESCE (biblioitems.isbn, deletedbiblioitems.isbn) LIKE ?)'; push @args, ("%$search%","%$search%","%$search%"); } if ( $ean ) { - $query .= ' AND biblioitems.ean = ?'; + $query .= ' AND (COALESCE biblioitems.ean,deletedbiblioitems.ean) = ?'; push @args, $ean; } if ( $booksellerid ) { @@ -1947,7 +2052,7 @@ Looks up all of the received items from the supplier with the given bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders. C<@results> is an array of references-to-hash. The keys of each element are fields from -the aqorders, biblio, and biblioitems tables of the Koha database. +the aqorders, aqbasket, aqinvoices, borrowers, biblio, and deletedbiblio tables of the Koha database. C<@results> is sorted alphabetically by book title. @@ -1976,11 +2081,12 @@ sub GetParcel { aqorders.rrp, aqorders.ecost, aqorders.gstrate, - biblio.title + COALESCE (biblio.title, deletedbiblio.title) AS title FROM aqorders 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 deletedbiblio ON aqorders.deletedbiblionumber=deletedbiblio.biblionumber LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid WHERE aqbasket.booksellerid = ? @@ -2096,13 +2202,41 @@ sub GetParcels { =head3 GetLateOrders - @results = &GetLateOrders; + @results = &GetLateOrders ($delay, $supplierid, $branch, $estimateddeliverydatefrom, $estimateddeliverydateto) ; -Searches for bookseller with late orders. +Looks up the orders to receive (non cancelled and non completely received) from a given supplier. return: the table of supplier with late issues. This table is full of hashref. +Looks up the cancelled orders with the given basketnumber. +Optional parameters are used to limit the results : +- C<$delay> : the number of days after the closing of the basket after which an order is late +- C<$supplierid> : the id of the vendor this function has to get orders +- C<$branch> : the branchcode of the branch this function has to get orders +- C<$estimateddeliverydatefrom> : the oldest expected date of delivery (based on aqbooksellers.deliverytime) +- C<$estimateddeliverydateto> : the most recent expected date of delivery (based on aqbooksellers.deliverytime). If only +C<$estimateddeliverydatefrom> is provided and not C<$estimateddeliverydatefrom>, the current day is used as most recent +expected date of delivery. + +Return : +The returned C<@results> is an array of references-to-hash, whose keys are: +- 9 fields from the aqorders table : ordernumber, biblionumber, deletedbiblionumber, claims_count, claims_date, +closedate as orderdate, rrp as unitpricesupplier, ecost in unitpricelib, budget_name as budget +- 1 field from the aqbasket table : basketno +- 1 field from the borrowers table : branchcode as branch +- 2 fields from aqbooksellers : name as supplier, id as supplierid +- 2 fields of biblio or deletedbiblio tables : title, author +- 2 fields of biblioitems or deletedbiblioitems tables : publishercode as publisher, publicationyear +- estimateddeliverydate : ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) +- quantity ; aqorders.quantity - COALESCE(aqorders.quantityreceived,0) +- subtotal : (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal +- latesince : DATEDIFF(CAST(now() AS date),closedate) AS latesince +If the record still exists, the biblionumber key contains the biblionumber +If the record have been deleted, the biblionumber key is blank, +and the deletedbiblionumber key contains the biblionumber of the deleted record. + + =cut sub GetLateOrders { @@ -2121,6 +2255,8 @@ sub GetLateOrders { my $select = " SELECT aqbasket.basketno, aqorders.ordernumber, + aqorders.biblionumber, + aqorders.deletedbiblionumber, DATE(aqbasket.closedate) AS orderdate, aqbasket.basketname AS basketname, aqbasket.basketgroupid AS basketgroupid, @@ -2133,15 +2269,18 @@ sub GetLateOrders { borrowers.branchcode AS branch, aqbooksellers.name AS supplier, aqbooksellers.id AS supplierid, - biblio.author, biblio.title, - biblioitems.publishercode AS publisher, - biblioitems.publicationyear, + COALESCE (biblio.author, deletedbiblio.author) AS author, + COALESCE (biblio.title, deletedbiblio.title) AS title, + COALESCE (biblioitems.publishercode, deletedbiblioitems.publishercode) AS publisher, + COALESCE (biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, "; my $from = " FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber + LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber = aqorders.deletedbiblionumber + LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber = deletedbiblio.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 @@ -2269,6 +2408,9 @@ returns: $total_qty is the sum of all of the quantities in $order_loop $total_price is the cost of each in $order_loop times the quantity $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop +If the record still exists, the biblionumber key contains the biblionumber +If the record have been deleted, the biblionumber key is blank, +and the deletedbiblionumber key contains the biblionumber of the deleted record. =cut @@ -2305,6 +2447,7 @@ sub GetHistory { COALESCE(biblio.author, deletedbiblio.author) AS author, COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, + aqorders.deletedbiblionumber, aqorders.basketno, aqbasket.basketname, aqbasket.basketgroupid, @@ -2334,8 +2477,8 @@ sub GetHistory { LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber 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 + LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber + LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber "; if ( C4::Context->preference("IndependentBranches") ) { @@ -2356,22 +2499,22 @@ sub GetHistory { } if ( $title ) { - $query .= " AND biblio.title LIKE ? "; + $query .= " AND COALESCE (biblio.title,deletedbiblio.title) LIKE ? "; $title =~ s/\s+/%/g; push @query_params, "%$title%"; } if ( $author ) { - $query .= " AND biblio.author LIKE ? "; + $query .= " AND COALESCE (biblio.author,deletedbiblio.author) LIKE ? "; push @query_params, "%$author%"; } if ( $isbn ) { - $query .= " AND biblioitems.isbn LIKE ? "; + $query .= " AND COALESCE (biblioitems.isbn,deletedbiblioitems.isbn) LIKE ? "; push @query_params, "%$isbn%"; } if ( $ean ) { - $query .= " AND biblioitems.ean = ? "; + $query .= " AND OALESCE (biblioitems.ean,deletedbiblioitems.ean) = ? "; push @query_params, "$ean"; } if ( $name ) { diff --git a/C4/Biblio.pm b/C4/Biblio.pm index d465cf6..8a3453a 100644 --- a/C4/Biblio.pm +++ b/C4/Biblio.pm @@ -410,10 +410,15 @@ sub ModBiblioframework { my $error = &DelBiblio($biblionumber); -Exported function (core API) for deleting a biblio in koha. -Deletes biblio record from Zebra and Koha tables (biblio & biblioitems) -Also backs it up to deleted* tables. -Checks to make sure that the biblio has no items attached. +Exported function (core API) for deleting a biblio in Koha. +Action : +It the record has an item, do not delete the record, and return an error message. +If the record has no item, +- deletes biblio record from Zebra and Koha tables (biblio,biblioitems), +- copy biblio and biblioitems tables deletedbiblio and deletedbiblioitems tables, +- delete attached suscriptions +- if the record is used in an order, move the biblionumber from aqorders.biblionumber to aqorders.deletedbiblionumber +- record the action in logs return: C<$error> : undef unless an error occurs @@ -448,6 +453,9 @@ sub DelBiblio { foreach my $res ( @$reserves ) { C4::Reserves::CancelReserve({ reserve_id => $res->{'reserve_id'} }); } + # Get all orders using this record. Must be done before deleting the record to be able to find orders to update after deletion + require C4::Acquisition; + my @orders = C4::Acquisition::GetOrdersByBiblionumber ($biblionumber); # Delete in Zebra. Be careful NOT to move this line after _koha_delete_biblio # for at least 2 reasons : @@ -455,9 +463,10 @@ sub DelBiblio { # and we would have no way to remove it (except manually in zebra, but I bet it would be very hard to handle the problem) ModZebra( $biblionumber, "recordDelete", "biblioserver" ); - # delete biblioitems and items from Koha tables and save in deletedbiblioitems,deleteditems + # delete the record from biblioitems table and save it in deletedbiblioitems,deleteditems $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?"); $sth->execute($biblionumber); + #FIXME : the loop is probably unneeded while ( my $biblioitemnumber = $sth->fetchrow ) { # delete this biblioitem @@ -470,7 +479,17 @@ sub DelBiblio { # delete cascade will prevent deletedbiblioitems rows # from being generated by _koha_delete_biblioitems $error = _koha_delete_biblio( $dbh, $biblionumber ); + return $error if $error; + # If the record is used in an order, move the biblionumber from aqorders.biblionumber to aqorders.deletedbiblionumber + if (scalar @orders > 0) { + for my $myorder (@orders) { + $sth = $dbh->prepare("UPDATE aqorders + SET deletedbiblionumber = ? + WHERE ordernumber=?"); + $sth->execute($biblionumber,$myorder->{ordernumber}); + } + } logaction( "CATALOGUING", "DELETE", $biblionumber, "biblio" ) if C4::Context->preference("CataloguingLog"); return; diff --git a/acqui/basket.pl b/acqui/basket.pl index 0ca8e3a..0822afa 100755 --- a/acqui/basket.pl +++ b/acqui/basket.pl @@ -479,7 +479,7 @@ sub get_order_infos { $line{'title'} .= " / $seriestitle" if $seriestitle; $line{'title'} .= " / $volume" if $volume; } else { - $line{'title'} = "Deleted bibliographic notice, can't find title."; + $line{'title'} = "Can't find title."; } my $biblionumber = $order->{'biblionumber'}; diff --git a/acqui/neworderempty.pl b/acqui/neworderempty.pl index 256175a..a699115 100755 --- a/acqui/neworderempty.pl +++ b/acqui/neworderempty.pl @@ -361,6 +361,7 @@ $template->param( surnamesuggestedby => $suggestion->{surnamesuggestedby}, firstnamesuggestedby => $suggestion->{firstnamesuggestedby}, biblionumber => $biblionumber, + deletedbiblionumber => $data->{'deletedbiblionumber'}, uncertainprice => $data->{'uncertainprice'}, authorisedbyname => $borrower->{'firstname'} . " " . $borrower->{'surname'}, discount_2dp => sprintf( "%.2f", $bookseller->{'discount'} ) , # for display diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index b46dc6a..cd4ca35 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2992,6 +2992,7 @@ DROP TABLE IF EXISTS `aqorders`; CREATE TABLE `aqorders` ( -- information related to the basket line items `ordernumber` int(11) NOT NULL auto_increment, -- primary key and unique identifier assigned by Koha to each line `biblionumber` int(11) default NULL, -- links the order to the biblio being ordered (biblio.biblionumber) + `deletedbiblionumber` int(11) default NULL, -- links the order to the deletedbiblio being ordered (deletedbiblio.biblionumber) `entrydate` date default NULL, -- the date the bib was added to the basket `quantity` smallint(6) default NULL, -- the quantity ordered `currency` varchar(3) default NULL, -- the currency used for the purchase @@ -3034,6 +3035,7 @@ CREATE TABLE `aqorders` ( -- information related to the basket line items CONSTRAINT `aqorders_ibfk_1` FOREIGN KEY (`basketno`) REFERENCES `aqbasket` (`basketno`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT aqorders_ibfk_3 FOREIGN KEY (invoiceid) REFERENCES aqinvoices (invoiceid) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT aqorders_ibfk_4 FOREIGN KEY (deletedbiblionumber) REFERENCES deletedbiblio (biblionumber) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `aqorders_subscriptionid` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 531bb12..cdd15cd 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8550,7 +8550,6 @@ if (CheckVersion($DBversion)) { print "Upgrade to $DBversion done (Bug 11629 - Add ability to update not for loan status on checkin)\n"; SetVersion($DBversion); } - $DBversion = "3.17.00.008"; if ( CheckVersion($DBversion) ) { $dbh->do(q| @@ -8587,6 +8586,13 @@ if ( CheckVersion($DBversion) ) { $dbh->do("INSERT INTO language_descriptions(subtag, type, lang, description) VALUES( 'hr', 'language', 'de', 'Kroatisch')"); print "Upgrade to $DBversion done (Bug 12649: Add Croatian language)\n"; SetVersion ($DBversion); + +$DBversion = "3.17.00.XXX"; +if ( CheckVersion($DBversion) ) { + $dbh->do("ALTER TABLE aqorders ADD COLUMN deletedbiblionumber INT(11) DEFAULT NULL AFTER biblionumber;"); + $dbh->do("ALTER TABLE aqorders ADD CONSTRAINT `aqorders_ibfk_4` FOREIGN KEY (`deletedbiblionumber`) REFERENCES `deletedbiblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE;"); + print "Upgrade to $DBversion done (Bug 10758 - Show bibliographic information of deleted records in acquisitions)\n"; + SetVersion($DBversion); } =head1 FUNCTIONS diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt index 37ac2d2..1977b04 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt @@ -476,10 +476,18 @@ [% books_loo.ordernumber %] -

- [% IF ( books_loo.order_received ) %] (rcvd)[% END %] - [% books_loo.title |html %] by [% books_loo.author %] -
+

[% IF ( books_loo.order_received ) %] (rcvd)[% END %] + [% IF (books_loo.biblionumber) %] + [% books_loo.title |html %] by [% books_loo.author %] +
+ [% ELSIF (books_loo.deletedbiblionumber) %] + (Deleted record) +
+ [% books_loo.title |html %] by [% books_loo.author %] +
+ [% ELSE %] + Can't find title
+ [% END %] [% IF ( books_loo.isbn ) %] - [% books_loo.isbn %][% END %] [% IF ( books_loo.issn ) %] - [% books_loo.issn %][% END %] [% IF ( books_loo.publishercode ) %], [% books_loo.publishercode %][% END %] @@ -598,10 +606,14 @@

[% IF ( order.order_received ) %] (rcvd)[% END %] - [% IF (order.title) %] - [% order.title |html %] by [% order.author %]
+ [% IF (order.deletedbiblionumber) %] + (Deleted record) +
+ [% END %] + [% IF (order.biblionumber)||(order.deletedbiblionumber) %] + [% order.title |html %] by [% order.author %]
[% ELSE %] - Deleted bibliographic record, can't find title
+ Can't find title
[% END %] [% IF ( order.order_internalnote ) %] [% order.order_internalnote %][% END %] [% IF ( order.isbn ) %] - [% order.isbn %][% END %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/histsearch.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/histsearch.tt index 3bb7fcb..af6ee5a 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/histsearch.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/histsearch.tt @@ -178,8 +178,16 @@ [% END %] - [% order.title |html %] -
[% order.author %]
[% order.isbn %] + [% IF (order.biblionumber) %] + [% order.title |html %] +
[% order.author %]
[% order.isbn %] + [% ELSIF (order.deletedbiblionumber) %] + (Deleted record) +
+ [% order.title |html %]
[% order.author %]
[% order.isbn %] + [% ELSE %] + Can't find title + [% END %] [% order.name %] [% order.creationdate | $KohaDates %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt index efa0db6..bd9960f 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt @@ -146,6 +146,7 @@ $(document).ready(function() { ([% lateorder.supplierid %]) + [% IF (lateorder.biblionumber) || (lateorder.deletedbiblionumber) %] [% lateorder.title |html %] [% IF ( lateorder.author ) %]
Author: [% lateorder.author %][% END %] [% IF ( lateorder.publisher ) %] @@ -154,6 +155,9 @@ $(document).ready(function() { in [% lateorder.publicationyear %] [% END %] [% END %] + [% ELSE %] + Can't find title + [% END %] [% lateorder.unitpricesupplier %]x[% lateorder.quantity %] = diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt index 64ad028..03f2c6b 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt @@ -232,6 +232,8 @@ $(document).ready(function() Catalog details [% IF ( biblionumber ) %] Edit record + [% ELSIF (deletedbiblionumber) %] + (Deleted record, not editable) [% END %] [% UNLESS ( existing ) %] @@ -252,8 +254,8 @@ $(document).ready(function() [% END %]

  1. - [% IF ( biblionumber ) %] - Title + [% IF ( biblionumber )||( deletedbiblionumber ) %] + Title: [% title |html %] [% ELSE %] @@ -261,7 +263,7 @@ $(document).ready(function() [% END %]
  2. - [% IF ( biblionumber ) %] + [% IF ( biblionumber )||( deletedbiblionumber ) %] Author: [% author %] [% ELSE %] @@ -270,7 +272,7 @@ $(document).ready(function() [% END %]
  3. - [% IF ( biblionumber ) %] + [% IF ( biblionumber )||( deletedbiblionumber ) %] Publisher: [% publishercode %] [% ELSE %] @@ -279,7 +281,7 @@ $(document).ready(function() [% END %]
  4. - [% IF ( biblionumber ) %] + [% IF ( biblionumber )||( deletedbiblionumber ) %] Edition: [% editionstatement %] @@ -289,7 +291,7 @@ $(document).ready(function() [% END %]
  5. - [% IF ( biblionumber ) %] + [% IF ( biblionumber )||( deletedbiblionumber ) %] Publication year: [% publicationyear %] [% ELSE %] @@ -298,7 +300,7 @@ $(document).ready(function() [% END %]
  6. - [% IF ( biblionumber ) %] + [% IF ( biblionumber )||( deletedbiblionumber ) %] ISBN: [% isbn %] [% ELSE %] @@ -308,7 +310,7 @@ $(document).ready(function()
  7. [% IF (UNIMARC) %]
  8. - [% IF ( biblionumber ) %] + [% IF ( biblionumber )||( deletedbiblionumber ) %] EAN: [% ean %] [% ELSE %] @@ -318,7 +320,7 @@ $(document).ready(function()
  9. [% END %]
  10. - [% IF ( biblionumber ) %] + [% IF ( biblionumber )||( deletedbiblionumber ) %] Series: [% seriestitle %] [% ELSE %] @@ -326,10 +328,10 @@ $(document).ready(function() [% END %]
  11. - [% UNLESS ( biblionumber ) %] + [% UNLESS (( biblionumber )||( deletedbiblionumber )) %] [% IF ( itemtypeloop ) %]
  12. - Item type: + Item type: