[Koha-patches] [PATCH] Bug 10758 - Show bibliographic information of deleted records in acquisitions
Mathieu Saby
mathieu.saby at univ-rennes2.fr
Sat Sep 21 19:29:25 CEST 2013
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) %]
<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=[% books_loo.biblionumber %]">[% books_loo.title |html %]</a> by [% books_loo.author %]
<br />
+ [% ELSIF (books_loo.deletedbiblionumber) %]
+ [% books_loo.title |html %] by [% books_loo.author %]
+ <br />
+ <em>(Deleted record)</em>
+ <br />
[% ELSE %]
- <em>Deleted bibliographic record, can't find title</em><br />
+ <em>Can't find title</em><br />
[% END %]
[% IF ( books_loo.isbn ) %] - [% books_loo.isbn %][% END %]
[% IF ( books_loo.issn ) %] - [% books_loo.issn %][% END %]
@@ -453,10 +458,10 @@
<td>
<p>
[% IF ( order.order_received ) %] (rcvd)[% END %]
- [% IF (order.biblionumber) %]
+ [% IF (order.biblionumber)||(order.deletedbiblionumber) %]
[% order.title |html %] by [% order.author %]<br />
[% ELSE %]
- <em>Deleted bibliographic record, can't find title</em><br />
+ <em>Can't find title</em><br />
[% 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 ) %]
<span><a href="/cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=[% biblionumber %]"> Edit record</a></span>
- [% END %]
+ [% ELSIF (deletedbiblionumber) %]
+ <span> (Deleted record, not editable)</span>
+ [% END %]
</legend>
[% UNLESS ( existing ) %]
<input type="hidden" name="existing" value="no" />
@@ -234,26 +236,26 @@ $(document).ready(function()
[% END %]
<ol><li>
- [% IF ( biblionumber ) %]
- <span class="label">Title</span>
- <input type="hidden" name="title" value="[% title |html %]" /> <span class="title">[% title |html %]</span>
- [% ELSE %]
+ [% IF ( biblionumber )||( deletedbiblionumber ) %]
+ <span class="label">Title:</span>
+ <input type="hidden" name="title" value="[% title |html %]" /> <span class="title">[% title |html %]</span>
+ [% ELSE %]
<label for="entertitle" class="required">Title: </label>
<input type="text" id="entertitle" size="50" name="title" value="[% title |html %]" class="focus" />
[% END %]
</li>
<li>
- [% IF ( biblionumber ) %]
- <span class="label">Author: </span>
- <input type="hidden" name="author" id="author" value="[% author %]" />[% author %]
- [% ELSE %]
+ [% IF ( biblionumber )||( deletedbiblionumber ) %]
+ <span class="label">Author: </span>
+ <input type="hidden" name="author" id="author" value="[% author %]" />[% author %]
+ [% ELSE %]
<label for="author">Author: </label>
<input type="text" size="50" name="author" id="author" value="[% author %]" />
[% END %]
</li>
<li>
- [% IF ( biblionumber ) %]
- <span class="label">Publisher: </span>
+ [% IF ( biblionumber )||( deletedbiblionumber ) %]
+ <span class="label">Publisher: </span>
<input type="hidden" name="publishercode" id="publishercode" value="[% publishercode %]" />[% publishercode %]
[% ELSE %]
<label for="publishercode"> Publisher: </label>
@@ -261,18 +263,17 @@ $(document).ready(function()
[% END %]
</li>
<li>
- [% IF ( biblionumber ) %]
- <span class="label">Edition: </span>
+ [% IF ( biblionumber )||( deletedbiblionumber ) %]
+ <span class="label">Edition: </span>
<input type="hidden" name="editionstatement" id="editionstatement" value="[% editionstatement %]" />[% editionstatement %]
-
[% ELSE %]
<label for="editionstatement">Edition: </label>
<input type="text" size="20" name="editionstatement" id="editionstatement" value="[% editionstatement %]" />
[% END %]
</li>
<li>
- [% IF ( biblionumber ) %]
- <span class="label">Publication year: </span>
+ [% IF ( biblionumber )||( deletedbiblionumber ) %]
+ <span class="label">Publication year: </span>
<input type="hidden" name="publicationyear" id="publicationyear" value="[% publicationyear %]" />[% publicationyear %]
[% ELSE %]
<label for="publicationyear">Publication year: </label>
@@ -280,8 +281,8 @@ $(document).ready(function()
[% END %]
</li>
<li>
- [% IF ( biblionumber ) %]
- <span class="label">ISBN: </span>
+ [% IF ( biblionumber )||( deletedbiblionumber ) %]
+ <span class="label">ISBN: </span>
<input type="hidden" name="isbn" id="ISBN" value="[% isbn %]" />[% isbn %]
[% ELSE %]
<label for="ISBN">ISBN: </label>
@@ -290,8 +291,8 @@ $(document).ready(function()
</li>
[% IF (UNIMARC) %]
<li>
- [% IF ( biblionumber ) %]
- <span class="label">EAN: </span>
+ [% IF ( biblionumber )||( deletedbiblionumber ) %]
+ <span class="label">EAN: </span>
<input type="hidden" name="ean" id="EAN" value="[% ean %]" />[% ean %]
[% ELSE %]
<label for="EAN">EAN: </label>
@@ -300,15 +301,15 @@ $(document).ready(function()
</li>
[% END %]
<li>
- [% IF ( biblionumber ) %]
- <span class="label">Series: </span>
+ [% IF ( biblionumber )||( deletedbiblionumber ) %]
+ <span class="label">Series: </span>
<input type="hidden" name="series" id="series" value="[% seriestitle %]" />[% seriestitle %]
[% ELSE %]
<label for="series">Series: </label>
<input type="text" size="50" name="series" id="series" value="[% seriestitle %]" />
[% END %]
</li>
- [% UNLESS ( biblionumber ) %]
+ [% UNLESS (( biblionumber )||( deletedbiblionumber )) %]
[% IF ( itemtypeloop ) %]
<li>
<span class="label">Item type:</span>
--
1.7.9.5
--
Mathieu Saby
Service d'Informatique Documentaire
Service Commun de Documentation
Université Rennes 2
Téléphone : 02 99 14 12 65
Courriel : mathieu.saby at univ-rennes2.fr
More information about the Koha-patches
mailing list