From 5415f141d40c16048918df0cb72f6495787e59ea Mon Sep 17 00:00:00 2001 From: Mathieu Saby Date: Sat, 21 Sep 2013 18:18:40 +0200 Subject: [PATCH] Bug 10758 - Show bibliographic information of deleted records in acquisitions Content-Type: text/plain; charset="utf-8" This patch must be applied on top of patch 10869 Aim of the patch : display information stored in deletedbiblio and deletedbiblioitems tables in basket.tt and neworderempty.tt pages Content : - modification of database structure : new column "deletedbiblionumber" in aqorders - modification of GetOrder, GetOrders and GetCancelledOrders in C4::Acquisition - modification of DelBiblio in C4::Biblio - modification of the 2 templates Maybe some changes must be made to UT, and maybe the performance of my queries is not optimal. But please first test to check if it is doing what it is meant for... To test: - create a basket with 5 orders using records A,B,C,D (you can use existing records or create new ones, it does not matter) - delete 2 orders A & B, but keep the records - in the catalogue, suppress record A (used by a cancelled order) & D (used by an active order) - refresh the basket page : you should see the suppressed orders in Cancelled Orders block, with readable title and authors of deleted records, and a note (Deleted record) - click on "Modifiy" for order D. You should see the title, author, publisher, date... without the link [Edit Record] but with a message (Deleted record, non editable) - click on "Modify" for record C. You should have [Edit record] link - create an order E. - delete order E, and the record (link 'delete order and record') - refresh the basket. you should see the order in the Cancelled Orders, with a readable title and author --- C4/Acquisition.pm | 65 ++++++++++++++------ C4/Biblio.pm | 13 ++++ acqui/basket.pl | 3 +- acqui/neworderempty.pl | 1 + installer/data/mysql/kohastructure.sql | 2 + installer/data/mysql/updatedatabase.pl | 8 +++ .../intranet-tmpl/prog/en/modules/acqui/basket.tt | 11 +++- .../prog/en/modules/acqui/neworderempty.tt | 47 +++++++------- 8 files changed, 103 insertions(+), 47 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 42a58c4..d6c1e7c 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -921,7 +921,7 @@ sub GetPendingOrders { @orders = &GetOrders($basketnumber, $orderby); -Looks up the pending (non-cancelled) orders with the given basket +Looks up non-cancelled orders (pending and received) with the given basket number. If C<$booksellerID> is non-empty, only orders from that seller are returned. @@ -929,6 +929,7 @@ 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. +If the record have been suppressed, its biblionumber is only retuned in deletedbiblionumber column. =cut @@ -936,27 +937,34 @@ sub GetOrders { my ( $basketno, $orderby ) = @_; my $dbh = C4::Context->dbh; my $query =" - SELECT biblio.*,biblioitems.*, + SELECT all_biblio.*, + all_biblioitems.*, aqorders.*, aqbudgets.*, - 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 aqbudgets ON aqbudgets.budget_id = aqorders.budget_id + LEFT JOIN ( + SELECT * FROM biblio + UNION ALL + SELECT * FROM deletedbiblio + ) AS all_biblio ON ((all_biblio.biblionumber=aqorders.biblionumber) OR (all_biblio.biblionumber=aqorders.deletedbiblionumber)) + LEFT JOIN ( + SELECT * FROM biblioitems + UNION ALL + SELECT * FROM deletedbiblioitems + ) AS all_biblioitems ON ((all_biblioitems.biblionumber=aqorders.biblionumber) OR (all_biblioitems.biblionumber=aqorders.deletedbiblionumber)) 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 = "all_biblioitems.publishercode,all_biblio.title" unless $orderby; $query .= " ORDER BY $orderby"; my $sth = $dbh->prepare($query); $sth->execute($basketno); my $results = $sth->fetchall_arrayref({}); - $sth->finish; + $sth->finish; return @$results; } @@ -1005,6 +1013,7 @@ 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. +If the record have been suppressed, its biblionumber is only retuned in deletedbiblionumber column. =cut @@ -1012,10 +1021,19 @@ sub GetOrder { my ($ordernumber) = @_; my $dbh = C4::Context->dbh; my $query = " - SELECT biblioitems.*, biblio.*, aqorders.* + SELECT all_biblio.*, all_biblioitems.*, + aqorders.* FROM aqorders - LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber - LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber + LEFT JOIN ( + SELECT * FROM biblio + UNION ALL + SELECT * FROM deletedbiblio + ) AS all_biblio ON ((all_biblio.biblionumber=aqorders.biblionumber) OR (all_biblio.biblionumber=aqorders.deletedbiblionumber)) + LEFT JOIN ( + SELECT * FROM biblioitems + UNION ALL + SELECT * FROM deletedbiblioitems + ) AS all_biblioitems ON ((all_biblioitems.biblionumber=aqorders.biblionumber) OR (all_biblioitems.biblionumber=aqorders.deletedbiblionumber)) WHERE aqorders.ordernumber=? "; @@ -1026,6 +1044,7 @@ sub GetOrder { return $data; } + =head3 GetLastOrderNotReceivedFromSubscriptionid $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid); @@ -1257,7 +1276,8 @@ sub ModItemOrder { my @orders = GetCancelledOrders($basketno, $orderby); -Returns cancelled orders for a basket +Returns cancelled orders for a basket. +If the record have been suppressed, its biblionumber is only retuned in deletedbiblionumber column. =cut @@ -1269,17 +1289,25 @@ sub GetCancelledOrders { my $dbh = C4::Context->dbh; my $query = " SELECT - biblio.*, - biblioitems.*, + all_biblio.*, + all_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 + LEFT JOIN ( + SELECT * FROM biblio + UNION ALL + SELECT * FROM deletedbiblio + ) AS all_biblio ON ((all_biblio.biblionumber=aqorders.biblionumber) OR (all_biblio.biblionumber=aqorders.deletedbiblionumber)) + LEFT JOIN ( + SELECT * FROM biblioitems + UNION ALL + SELECT * FROM deletedbiblioitems + ) AS all_biblioitems ON ((all_biblioitems.biblionumber=aqorders.biblionumber) OR (all_biblioitems.biblionumber=aqorders.deletedbiblionumber)) + LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber WHERE basketno = ? AND (datecancellationprinted IS NOT NULL AND datecancellationprinted <> '0000-00-00') @@ -1295,7 +1323,6 @@ sub GetCancelledOrders { return @$results; } - #------------------------------------------------------------# =head3 ModReceiveOrder diff --git a/C4/Biblio.pm b/C4/Biblio.pm index cf83ae6..46718dd 100644 --- a/C4/Biblio.pm +++ b/C4/Biblio.pm @@ -441,6 +441,10 @@ sub DelBiblio { 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 : # - if something goes wrong, the biblio may be deleted from Koha but not from zebra @@ -463,6 +467,15 @@ sub DelBiblio { # from being generated by _koha_delete_biblioitems $error = _koha_delete_biblio( $dbh, $biblionumber ); + #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, "" ) if C4::Context->preference("CataloguingLog"); return; diff --git a/acqui/basket.pl b/acqui/basket.pl index 681cd60..12cb76d 100755 --- a/acqui/basket.pl +++ b/acqui/basket.pl @@ -383,7 +383,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'}; @@ -432,7 +432,6 @@ sub get_order_infos { }; } } - return \%line; } diff --git a/acqui/neworderempty.pl b/acqui/neworderempty.pl index 9c9eed3..05d667f 100755 --- a/acqui/neworderempty.pl +++ b/acqui/neworderempty.pl @@ -377,6 +377,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 fdab879..3a7ea0d 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2900,6 +2900,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 @@ -2940,6 +2941,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 31f494a..775c7e0 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7139,6 +7139,14 @@ if ( CheckVersion($DBversion) ) { SetVersion ($DBversion); } +$DBversion = "3.13.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 \n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) 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 0f12485..799c674 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt @@ -339,8 +339,13 @@ [% IF (books_loo.biblionumber) %] [% books_loo.title |html %] by [% books_loo.author %]
+ [% ELSIF (books_loo.deletedbiblionumber) %] + [% books_loo.title |html %] by [% books_loo.author %] +
+ (Deleted record) +
[% ELSE %] - Deleted bibliographic record, can't find title
+ Can't find title
[% END %] [% IF ( books_loo.isbn ) %] - [% books_loo.isbn %][% END %] [% IF ( books_loo.issn ) %] - [% books_loo.issn %][% END %] @@ -453,10 +458,10 @@

[% IF ( order.order_received ) %] (rcvd)[% END %] - [% IF (order.biblionumber) %] + [% 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.notes ) %] [% order.notes %][% END %] [% IF ( order.isbn ) %] - [% order.isbn %][% END %] 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 c261530..52ef288 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt @@ -214,7 +214,9 @@ $(document).ready(function() Catalog details [% IF ( biblionumber ) %] Edit record - [% END %] + [% ELSIF (deletedbiblionumber) %] + (Deleted record, not editable) + [% END %] [% UNLESS ( existing ) %] @@ -234,26 +236,26 @@ $(document).ready(function() [% END %]

  1. - [% IF ( biblionumber ) %] - Title - [% title |html %] - [% ELSE %] + [% IF ( biblionumber )||( deletedbiblionumber ) %] + Title: + [% title |html %] + [% ELSE %] [% END %]
  2. - [% IF ( biblionumber ) %] - Author: - [% author %] - [% ELSE %] + [% IF ( biblionumber )||( deletedbiblionumber ) %] + Author: + [% author %] + [% ELSE %] [% END %]
  3. - [% IF ( biblionumber ) %] - Publisher: + [% IF ( biblionumber )||( deletedbiblionumber ) %] + Publisher: [% publishercode %] [% ELSE %] @@ -261,18 +263,17 @@ $(document).ready(function() [% END %]
  4. - [% IF ( biblionumber ) %] - Edition: + [% IF ( biblionumber )||( deletedbiblionumber ) %] + Edition: [% editionstatement %] - [% ELSE %] [% END %]
  5. - [% IF ( biblionumber ) %] - Publication year: + [% IF ( biblionumber )||( deletedbiblionumber ) %] + Publication year: [% publicationyear %] [% ELSE %] @@ -280,8 +281,8 @@ $(document).ready(function() [% END %]
  6. - [% IF ( biblionumber ) %] - ISBN: + [% IF ( biblionumber )||( deletedbiblionumber ) %] + ISBN: [% isbn %] [% ELSE %] @@ -290,8 +291,8 @@ $(document).ready(function()
  7. [% IF (UNIMARC) %]
  8. - [% IF ( biblionumber ) %] - EAN: + [% IF ( biblionumber )||( deletedbiblionumber ) %] + EAN: [% ean %] [% ELSE %] @@ -300,15 +301,15 @@ $(document).ready(function()
  9. [% END %]
  10. - [% IF ( biblionumber ) %] - Series: + [% IF ( biblionumber )||( deletedbiblionumber ) %] + Series: [% seriestitle %] [% ELSE %] [% END %]
  11. - [% UNLESS ( biblionumber ) %] + [% UNLESS (( biblionumber )||( deletedbiblionumber )) %] [% IF ( itemtypeloop ) %]
  12. Item type: -- 1.7.9.5