@@ -, +, @@
- 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
- add UT for DelBiblio
- add UT for GetOrder and partially to NewOrder
- adding UT for GetOrders and GetCancelledOrders
- maybe improve template in receipt page
- doing the same for invoice
- 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
- launch unit tests :
---
C4/Acquisition.pm | 345 ++++++++++++++++++--
C4/Biblio.pm | 31 +-
acqui/basket.pl | 3 +-
acqui/neworderempty.pl | 1 +
installer/data/mysql/kohastructure.sql | 2 +
installer/data/mysql/updatedatabase.pl | 6 +
.../intranet-tmpl/prog/en/modules/acqui/basket.tt | 11 +-
.../prog/en/modules/acqui/neworderempty.tt | 43 +--
t/db_dependent/Acquisition.t | 310 +++++++++++++++++-
t/db_dependent/Biblio.t | 136 +++++++-
10 files changed, 825 insertions(+), 63 deletions(-)
--- a/C4/Acquisition.pm
+++ a/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,29 +929,130 @@ 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 returned in deletedbiblionumber column.
=cut
sub GetOrders {
my ( $basketno, $orderby ) = @_;
my $dbh = C4::Context->dbh;
+ # get all the fields of biblio, and all of biblioitems, except marc (marc blob) and timestamp
+ # timestamp is used in biblio and in aqorders, so rename biblio.timestamp into biblio_timestamp
+ # notes are used in biblio, biblioitems and aqorders. Rename biblio.notes and biblio into biblio_notes and biblioitems_notes
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 biblionumber,
+ frameworkcode,
+ author,
+ title,
+ unititle,
+ notes AS biblio_notes,
+ serial,
+ seriestitle,
+ copyrightdate,
+ timestamp AS biblio_timestamp,
+ datecreated,
+ abstract
+ FROM biblio
+ UNION ALL
+ SELECT biblionumber,
+ frameworkcode,
+ author,
+ title,
+ unititle,
+ notes AS biblio_notes,
+ serial,
+ seriestitle,
+ copyrightdate,
+ timestamp AS biblio_timestamp,
+ datecreated,
+ abstract
+ FROM deletedbiblio
+ ) AS all_biblio ON ((all_biblio.biblionumber=aqorders.biblionumber) OR (all_biblio.biblionumber=aqorders.deletedbiblionumber))
+ LEFT JOIN (
+ SELECT biblionumber,
+ biblioitemnumber,
+ volume,
+ number,
+ itemtype,
+ isbn,
+ issn,
+ ean,
+ publicationyear,
+ publishercode,
+ volumedate,
+ volumedesc,
+ collectiontitle,
+ collectionissn,
+ collectionvolume,
+ editionstatement,
+ editionresponsibility,
+ illus,
+ pages,
+ notes AS biblioitems_notes,
+ size,
+ place,
+ lccn,
+ marc,
+ url,
+ cn_source,
+ cn_class,
+ cn_item,
+ cn_suffix,
+ cn_sort,
+ agerestriction,
+ totalissues,
+ marcxml
+ FROM biblioitems
+ UNION ALL
+ SELECT biblionumber,
+ biblioitemnumber,
+ volume,
+ number,
+ itemtype,
+ isbn,
+ issn,
+ ean,
+ publicationyear,
+ publishercode,
+ volumedate,
+ volumedesc,
+ collectiontitle,
+ collectionissn,
+ collectionvolume,
+ editionstatement,
+ editionresponsibility,
+ illus,
+ pages,
+ notes AS biblioitems_notes,
+ size,
+ place,
+ lccn,
+ marc,
+ url,
+ cn_source,
+ cn_class,
+ cn_item,
+ cn_suffix,
+ cn_sort,
+ agerestriction,
+ totalissues,
+ marcxml
+ 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);
@@ -1005,19 +1106,121 @@ 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 returned in deletedbiblionumber column.
=cut
sub GetOrder {
my ($ordernumber) = @_;
my $dbh = C4::Context->dbh;
+ # get all the fields of biblio, and all of biblioitems, except marc (marc blob) and timestamp
+ # timestamp is used in biblio and in aqorders, so rename biblio.timestamp into biblio_timestamp
+ # notes are used in biblio, biblioitems and aqorders. Rename biblio.notes and biblio into biblio_notes and biblioitems_notes
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 biblionumber,
+ frameworkcode,
+ author,
+ title,
+ unititle,
+ notes AS biblio_notes,
+ serial,
+ seriestitle,
+ copyrightdate,
+ timestamp AS biblio_timestamp,
+ datecreated,
+ abstract
+ FROM biblio
+ UNION ALL
+ SELECT biblionumber,
+ frameworkcode,
+ author,
+ title,
+ unititle,
+ notes AS biblio_notes,
+ serial,
+ seriestitle,
+ copyrightdate,
+ timestamp AS biblio_timestamp,
+ datecreated,
+ abstract
+ FROM deletedbiblio
+ ) AS all_biblio ON ((all_biblio.biblionumber=aqorders.biblionumber) OR (all_biblio.biblionumber=aqorders.deletedbiblionumber))
+ LEFT JOIN (
+ SELECT biblionumber,
+ biblioitemnumber,
+ volume,
+ number,
+ itemtype,
+ isbn,
+ issn,
+ ean,
+ publicationyear,
+ publishercode,
+ volumedate,
+ volumedesc,
+ collectiontitle,
+ collectionissn,
+ collectionvolume,
+ editionstatement,
+ editionresponsibility,
+ illus,
+ pages,
+ notes AS biblioitems_notes,
+ size,
+ place,
+ lccn,
+ marc,
+ url,
+ cn_source,
+ cn_class,
+ cn_item,
+ cn_suffix,
+ cn_sort,
+ agerestriction,
+ totalissues,
+ marcxml
+ FROM biblioitems
+ UNION ALL
+ SELECT biblionumber,
+ biblioitemnumber,
+ volume,
+ number,
+ itemtype,
+ isbn,
+ issn,
+ ean,
+ publicationyear,
+ publishercode,
+ volumedate,
+ volumedesc,
+ collectiontitle,
+ collectionissn,
+ collectionvolume,
+ editionstatement,
+ editionresponsibility,
+ illus,
+ pages,
+ notes AS biblioitems_notes,
+ size,
+ place,
+ lccn,
+ marc,
+ url,
+ cn_source,
+ cn_class,
+ cn_item,
+ cn_suffix,
+ cn_sort,
+ agerestriction,
+ totalissues,
+ marcxml
+ FROM deletedbiblioitems
+ ) AS all_biblioitems ON ((all_biblioitems.biblionumber=aqorders.biblionumber) OR (all_biblioitems.biblionumber=aqorders.deletedbiblionumber))
WHERE aqorders.ordernumber=?
-
";
my $sth= $dbh->prepare($query);
$sth->execute($ordernumber);
@@ -1026,6 +1229,7 @@ sub GetOrder {
return $data;
}
+
=head3 GetLastOrderNotReceivedFromSubscriptionid
$order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
@@ -1113,8 +1317,7 @@ Else, the upcoming July 1st is used.
=item defaults entrydate to Now
-The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".
-
+The following keys are used: "biblionumber", "basketno", "quantity", "notes", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".
=back
=cut
@@ -1257,7 +1460,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 +1473,115 @@ 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 aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
+ LEFT JOIN (
+ SELECT biblionumber,
+ frameworkcode,
+ author,
+ title,
+ unititle,
+ notes AS biblio_notes,
+ serial,
+ seriestitle,
+ copyrightdate,
+ timestamp AS biblio_timestamp,
+ datecreated,
+ abstract
+ FROM biblio
+ UNION ALL
+ SELECT biblionumber,
+ frameworkcode,
+ author,
+ title,
+ unititle,
+ notes AS biblio_notes,
+ serial,
+ seriestitle,
+ copyrightdate,
+ timestamp AS biblio_timestamp,
+ datecreated,
+ abstract
+ FROM deletedbiblio
+ ) AS all_biblio ON ((all_biblio.biblionumber=aqorders.biblionumber) OR (all_biblio.biblionumber=aqorders.deletedbiblionumber))
+ LEFT JOIN (
+ SELECT biblionumber,
+ biblioitemnumber,
+ volume,
+ number,
+ itemtype,
+ isbn,
+ issn,
+ ean,
+ publicationyear,
+ publishercode,
+ volumedate,
+ volumedesc,
+ collectiontitle,
+ collectionissn,
+ collectionvolume,
+ editionstatement,
+ editionresponsibility,
+ illus,
+ pages,
+ notes AS biblioitems_notes,
+ size,
+ place,
+ lccn,
+ marc,
+ url,
+ cn_source,
+ cn_class,
+ cn_item,
+ cn_suffix,
+ cn_sort,
+ agerestriction,
+ totalissues,
+ marcxml
+ FROM biblioitems
+ UNION ALL
+ SELECT biblionumber,
+ biblioitemnumber,
+ volume,
+ number,
+ itemtype,
+ isbn,
+ issn,
+ ean,
+ publicationyear,
+ publishercode,
+ volumedate,
+ volumedesc,
+ collectiontitle,
+ collectionissn,
+ collectionvolume,
+ editionstatement,
+ editionresponsibility,
+ illus,
+ pages,
+ notes AS biblioitems_notes,
+ size,
+ place,
+ lccn,
+ marc,
+ url,
+ cn_source,
+ cn_class,
+ cn_item,
+ cn_suffix,
+ cn_sort,
+ agerestriction,
+ totalissues,
+ marcxml
+ 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 +1597,6 @@ sub GetCancelledOrders {
return @$results;
}
-
#------------------------------------------------------------#
=head3 ModReceiveOrder
--- a/C4/Biblio.pm
+++ a/C4/Biblio.pm
@@ -402,10 +402,16 @@ 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,items)
-Also backs it up to deleted* tables
-Checks to make sure there are not issues on any of the items
+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),
+- backs Koha tables it up to deleted* 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
@@ -441,15 +447,20 @@ 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
# 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
@@ -462,7 +473,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, "" ) if C4::Context->preference("CataloguingLog");
return;
--- a/acqui/basket.pl
+++ a/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;
}
--- a/acqui/neworderempty.pl
+++ a/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
--- a/installer/data/mysql/kohastructure.sql
+++ a/installer/data/mysql/kohastructure.sql
@@ -2904,6 +2904,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
@@ -2944,6 +2945,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;
--- a/installer/data/mysql/updatedatabase.pl
+++ a/installer/data/mysql/updatedatabase.pl
@@ -7303,6 +7303,12 @@ if ( CheckVersion($DBversion) ) {
});
print "Upgrade to $DBversion done (Bug 10565 - Add a 'Patron List' feature for storing and manipulating collections of patrons)\n";
+
+$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);
}
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/basket.tt
+++ a/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 %]
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/neworderempty.tt
+++ a/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 %]