From e199aeec4d578957d2aedf1ba23feff80dddb546 Mon Sep 17 00:00:00 2001 From: Francesco Rivetti Date: Tue, 27 Feb 2018 09:59:08 +0100 Subject: [PATCH] Bug 20271: merging delete biblio/items The present bug merges the following tables: deletedbiblio -> biblio deletedbiblioitems -> biblioitems deletedbiblio_metadata -> biblio_metadata deleteditems -> items and adds a column deleted_at to signify time of deletion, if deleted it replaces all occurrences of the mentioned occurences and uses of mentioned deleted* tables upgrade is handled by moving rows from deleted* tables to the live ones. If all rows are moved, the corresponding tables are dropped. If there are duplicates or conflicts (duplicate barcodes e.g.) they reminders are left for scrutiny and table renamed to _deleted* Test plan: 1) Make sure you have a db with at least a few deleted biblios, biblioitems and items. 2) Apply patch and run updatedatabase.pl 3) Make note of the db upgrade messages, if you get a message like: There were x deleteditems that could not be moved, please check _deleteditems you should check the _deleteditems table and verify that they are actual conflicts 4) Click around in the web interface and make sure you can delete items and biblios 5) "Undelete" an item by NULLing the deleted_at column for an item and verify that it returns in interface (Not neccessary really) Signed-off-by: Brendan A Gallagher Signed-off-by: Martin Renvoize --- C4/Acquisition.pm | 10 ++- C4/Biblio.pm | 97 ++++-------------------------- C4/Items.pm | 38 +++--------- C4/Reserves.pm | 4 +- Koha/Biblio.pm | 2 +- Koha/Filter/MARC/EmbedItemsAvailability.pm | 1 + Koha/ItemType.pm | 2 +- Koha/OAI/Server/GetRecord.pm | 14 ++--- Koha/OAI/Server/ListBase.pm | 10 +-- Koha/Patron.pm | 2 +- admin/branches.pl | 3 +- cataloguing/merge.pl | 2 +- labels/label-item-search.pl | 2 +- misc/cronjobs/delete_patrons.pl | 2 +- misc/export_records.pl | 22 +++---- opac/opac-reserve.pl | 4 +- reports/catalogue_stats.pl | 19 +++--- reports/itemslost.pl | 1 + t/db_dependent/Biblio.t | 11 ++-- t/db_dependent/Items.t | 2 +- t/db_dependent/Items/DelItem.t | 8 +-- 21 files changed, 76 insertions(+), 180 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index a257017dc7..070901d36d 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -2307,10 +2307,10 @@ sub GetHistory { my $dbh = C4::Context->dbh; my $query =" SELECT - COALESCE(biblio.title, deletedbiblio.title) AS title, - COALESCE(biblio.author, deletedbiblio.author) AS author, - COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, - COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, + biblio.title, + biblio.author, + biblioitems.isbn, + biblioitems.ean, aqorders.basketno, aqbasket.basketname, aqbasket.basketgroupid, @@ -2342,8 +2342,6 @@ 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 borrowers ON aqbasket.authorisedby=borrowers.borrowernumber "; diff --git a/C4/Biblio.pm b/C4/Biblio.pm index eaff2564b6..7a3daf5851 100644 --- a/C4/Biblio.pm +++ b/C4/Biblio.pm @@ -315,7 +315,7 @@ sub ModBiblio { # update biblionumber and biblioitemnumber in MARC # FIXME - this is assuming a 1 to 1 relationship between # biblios and biblioitems - my $sth = $dbh->prepare("select biblioitemnumber from biblioitems where biblionumber=?"); + my $sth = $dbh->prepare("select biblioitemnumber from biblioitems where biblionumber=? AND deleted_at IS NULL"); $sth->execute($biblionumber); my ($biblioitemnumber) = $sth->fetchrow; $sth->finish(); @@ -384,7 +384,7 @@ sub DelBiblio { my $error; # for error handling # First make sure this biblio has no items attached - my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber=?"); + my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber=? AND deleted_at IS NULL"); $sth->execute($biblionumber); if ( my $itemnumber = $sth->fetchrow ) { @@ -415,7 +415,7 @@ sub DelBiblio { ModZebra( $biblionumber, "recordDelete", "biblioserver" ); # delete biblioitems and items from Koha tables and save in deletedbiblioitems,deleteditems - $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=?"); + $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=? AND deleted_at IS NULL"); $sth->execute($biblionumber); while ( my $biblioitemnumber = $sth->fetchrow ) { @@ -1138,7 +1138,7 @@ sub GetMarcBiblio { } my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=? "); + my $sth = $dbh->prepare("SELECT biblioitemnumber FROM biblioitems WHERE biblionumber=? AND deleted_at IS NULL"); $sth->execute($biblionumber); my $row = $sth->fetchrow_hashref; my $biblioitemnumber = $row->{'biblioitemnumber'}; @@ -2761,7 +2761,7 @@ sub EmbedItemsInMarcBiblio { # ... and embed the current items my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ?"); + my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ? and deleted_at IS NULL"); $sth->execute($biblionumber); my @item_fields; my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField( "items.itemnumber", $frameworkcode ); @@ -3091,41 +3091,8 @@ C<$biblionumber> - the biblionumber of the biblio to be deleted sub _koha_delete_biblio { my ( $dbh, $biblionumber ) = @_; - # get all the data for this biblio - my $sth = $dbh->prepare("SELECT * FROM biblio WHERE biblionumber=?"); - $sth->execute($biblionumber); - - # FIXME There is a transaction in _koha_delete_biblio_metadata - # But actually all the following should be done inside a single transaction - if ( my $data = $sth->fetchrow_hashref ) { - - # save the record in deletedbiblio - # find the fields to save - my $query = "INSERT INTO deletedbiblio SET "; - my @bind = (); - foreach my $temp ( keys %$data ) { - $query .= "$temp = ?,"; - push( @bind, $data->{$temp} ); - } - - # replace the last , by ",?)" - $query =~ s/\,$//; - my $bkup_sth = $dbh->prepare($query); - $bkup_sth->execute(@bind); - $bkup_sth->finish; - - _koha_delete_biblio_metadata( $biblionumber ); - - # delete the biblio - my $sth2 = $dbh->prepare("DELETE FROM biblio WHERE biblionumber=?"); - $sth2->execute($biblionumber); - # update the timestamp (Bugzilla 7146) - $sth2= $dbh->prepare("UPDATE deletedbiblio SET timestamp=NOW() WHERE biblionumber=?"); - $sth2->execute($biblionumber); - $sth2->finish; - } - $sth->finish; - return; + my $sth = $dbh->prepare("UPDATE biblio SET deleted_at = NOW() WHERE biblionumber=?"); + return $sth->execute($biblionumber) ? undef : ($DBI::errstr || "unknown error"); } =head2 _koha_delete_biblioitems @@ -3143,38 +3110,8 @@ C<$biblionumber> - the biblioitemnumber of the biblioitem to be deleted sub _koha_delete_biblioitems { my ( $dbh, $biblioitemnumber ) = @_; - - # get all the data for this biblioitem - my $sth = $dbh->prepare("SELECT * FROM biblioitems WHERE biblioitemnumber=?"); - $sth->execute($biblioitemnumber); - - if ( my $data = $sth->fetchrow_hashref ) { - - # save the record in deletedbiblioitems - # find the fields to save - my $query = "INSERT INTO deletedbiblioitems SET "; - my @bind = (); - foreach my $temp ( keys %$data ) { - $query .= "$temp = ?,"; - push( @bind, $data->{$temp} ); - } - - # replace the last , by ",?)" - $query =~ s/\,$//; - my $bkup_sth = $dbh->prepare($query); - $bkup_sth->execute(@bind); - $bkup_sth->finish; - - # delete the biblioitem - my $sth2 = $dbh->prepare("DELETE FROM biblioitems WHERE biblioitemnumber=?"); - $sth2->execute($biblioitemnumber); - # update the timestamp (Bugzilla 7146) - $sth2= $dbh->prepare("UPDATE deletedbiblioitems SET timestamp=NOW() WHERE biblioitemnumber=?"); - $sth2->execute($biblioitemnumber); - $sth2->finish; - } - $sth->finish; - return; + my $sth = $dbh->prepare("UPDATE biblioitems SET deleted_at = NOW() WHERE biblioitemnumber=?"); + return $sth->execute($biblioitemnumber) ? undef : ($DBI::errstr || 'unknown error'); } =head2 _koha_delete_biblio_metadata @@ -3187,19 +3124,9 @@ C<$biblionumber> - the biblionumber of the biblio metadata to be deleted sub _koha_delete_biblio_metadata { my ($biblionumber) = @_; - - my $dbh = C4::Context->dbh; - my $schema = Koha::Database->new->schema; - $schema->txn_do( - sub { - $dbh->do( q| - INSERT INTO deletedbiblio_metadata (biblionumber, format, marcflavour, metadata) - SELECT biblionumber, format, marcflavour, metadata FROM biblio_metadata WHERE biblionumber=? - |, undef, $biblionumber ); - $dbh->do( q|DELETE FROM biblio_metadata WHERE biblionumber=?|, - undef, $biblionumber ); - } - ); + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare("UPDATE biblio_metadata SET deleted_at = NOW() WHERE biblionumber=?"); + return $sth->execute($biblionumber); } =head1 UNEXPORTED FUNCTIONS diff --git a/C4/Items.pm b/C4/Items.pm index e7279ff8a1..13ef40fec9 100644 --- a/C4/Items.pm +++ b/C4/Items.pm @@ -838,6 +838,7 @@ sub GetItemsForInventory { FROM items LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber LEFT JOIN biblioitems on items.biblionumber = biblioitems.biblionumber + WHERE (items.deleted_at IS NULL AND biblio.deleted_at IS NULL AND biblioitems.deleted_at IS NULL) }; if ($statushash){ for my $authvfield (keys %$statushash){ @@ -944,7 +945,7 @@ Called by C sub GetItemsByBiblioitemnumber { my ( $bibitem ) = @_; my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ?") || die $dbh->errstr; + my $sth = $dbh->prepare("SELECT * FROM items WHERE items.biblioitemnumber = ? AND items.deleted_at IS NULL") || die $dbh->errstr; # Get all items attached to a biblioitem my $i = 0; my @results; @@ -1068,7 +1069,7 @@ sub GetItemsInfo { FROM items LEFT JOIN branches AS holding ON items.holdingbranch = holding.branchcode LEFT JOIN branches AS home ON items.homebranch=home.branchcode - LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber + LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber AND biblio.deleted_at IS NULL LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber LEFT JOIN issues USING (itemnumber) LEFT JOIN borrowers USING (borrowernumber) @@ -1082,7 +1083,7 @@ sub GetItemsInfo { AND localization.lang = ? |; - $query .= " WHERE items.biblionumber = ? ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ; + $query .= " WHERE items.biblionumber = ? AND items.deleted_at IS NULL ORDER BY home.branchname, items.enumchron, LPAD( items.copynumber, 8, '0' ), items.dateaccessioned DESC" ; my $sth = $dbh->prepare($query); $sth->execute($language, $biblionumber); my $i = 0; @@ -1189,7 +1190,7 @@ sub GetItemsLocationInfo { location, itemcallnumber, cn_sort FROM items, branches as a, branches as b WHERE homebranch = a.branchcode AND holdingbranch = b.branchcode - AND biblionumber = ? + AND biblionumber = ? AND deleted_at IS NULL ORDER BY cn_sort ASC"; my $sth = $dbh->prepare($query); $sth->execute($biblionumber); @@ -1261,7 +1262,7 @@ sub GetLastAcquisitions { my $number_of_itemtypes = @{$data->{itemtypes}}; - my @where = ('WHERE 1 '); + my @where = ('WHERE (items.deleted_at IS NULL AND biblio.deleted_at IS NULL AND biblioitems.deleted_at IS NULL) '); $number_of_branches and push @where , 'AND holdingbranch IN (' , join(',', ('?') x $number_of_branches ) @@ -1308,7 +1309,7 @@ sub GetItemnumbersForBiblio { my $biblionumber = shift; my @items; my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ?"); + my $sth = $dbh->prepare("SELECT itemnumber FROM items WHERE biblionumber = ? AND deleted_at IS NULL"); $sth->execute($biblionumber); while (my $result = $sth->fetchrow_hashref) { push @items, $result->{'itemnumber'}; @@ -2083,27 +2084,8 @@ sub _koha_delete_item { my ( $itemnum ) = @_; my $dbh = C4::Context->dbh; - # save the deleted item to deleteditems table - my $sth = $dbh->prepare("SELECT * FROM items WHERE itemnumber=?"); - $sth->execute($itemnum); - my $data = $sth->fetchrow_hashref(); - - # There is no item to delete - return 0 unless $data; - - my $query = "INSERT INTO deleteditems SET "; - my @bind = (); - foreach my $key ( keys %$data ) { - next if ( $key eq 'timestamp' ); # timestamp will be set by db - $query .= "$key = ?,"; - push( @bind, $data->{$key} ); - } - $query =~ s/\,$//; - $sth = $dbh->prepare($query); - $sth->execute(@bind); - # delete from items table - $sth = $dbh->prepare("DELETE FROM items WHERE itemnumber=?"); + my $sth = $dbh->prepare("UPDATE items SET deleted_at = NOW() WHERE itemnumber = ?"); my $deleted = $sth->execute($itemnum); return ( $deleted == 1 ) ? 1 : 0; } @@ -2468,7 +2450,7 @@ sub SearchItems { LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber LEFT JOIN biblio_metadata ON biblio_metadata.biblionumber = biblio.biblionumber - WHERE 1 + WHERE items.deleted_at IS NULL }; if (defined $where_str and $where_str ne '') { $query .= qq{ AND $where_str }; @@ -2819,7 +2801,7 @@ sub ToggleNewStatus { SELECT items.biblionumber, items.itemnumber FROM items LEFT JOIN biblioitems ON biblioitems.biblionumber = items.biblionumber - WHERE 1 + WHERE items.deleted_at IS NULL |; for my $condition ( @$conditions ) { if ( diff --git a/C4/Reserves.pm b/C4/Reserves.pm index 359fd984b6..9451788d55 100644 --- a/C4/Reserves.pm +++ b/C4/Reserves.pm @@ -1132,7 +1132,7 @@ sub IsAvailableForItemLevelRequest { return 1; } elsif ( $on_shelf_holds == 2 ) { my @items = - Koha::Items->search( { biblionumber => $item->{biblionumber} } ); + Koha::Items->search( { biblionumber => $item->{biblionumber}, deleted_at => undef } ); my $any_available = 0; @@ -2054,7 +2054,7 @@ sub GetMaxPatronHoldsForRecord { my ( $borrowernumber, $biblionumber ) = @_; my $patron = Koha::Patrons->find($borrowernumber); - my @items = Koha::Items->search( { biblionumber => $biblionumber } ); + my @items = Koha::Items->search( { biblionumber => $biblionumber, deleted_at => undef } ); my $controlbranch = C4::Context->preference('ReservesControlBranch'); diff --git a/Koha/Biblio.pm b/Koha/Biblio.pm index d226304267..ccec4ae473 100644 --- a/Koha/Biblio.pm +++ b/Koha/Biblio.pm @@ -250,7 +250,7 @@ or list of Koha::Item objects in list context. sub items { my ($self) = @_; - $self->{_items} ||= Koha::Items->search( { biblionumber => $self->biblionumber() } ); + $self->{_items} ||= Koha::Items->search( { biblionumber => $self->biblionumber(), deleted_at => undef } ); return wantarray ? $self->{_items}->as_list : $self->{_items}; } diff --git a/Koha/Filter/MARC/EmbedItemsAvailability.pm b/Koha/Filter/MARC/EmbedItemsAvailability.pm index fed3aa2d11..e41d04bbfd 100644 --- a/Koha/Filter/MARC/EmbedItemsAvailability.pm +++ b/Koha/Filter/MARC/EmbedItemsAvailability.pm @@ -79,6 +79,7 @@ sub _processrecord { my $not_onloan_items = Koha::Items->search({ biblionumber => $biblionumber, onloan => undef, + deleted_at => undef, })->count; # check for field 999 diff --git a/Koha/ItemType.pm b/Koha/ItemType.pm index 24be0d2297..4c815fe9af 100644 --- a/Koha/ItemType.pm +++ b/Koha/ItemType.pm @@ -101,7 +101,7 @@ Counts up the number of biblioitems and items with itemtype (code) and hands bac sub can_be_deleted { my ($self) = @_; - my $nb_items = Koha::Items->search( { itype => $self->itemtype } )->count; + my $nb_items = Koha::Items->search( { itype => $self->itemtype, deleted_at => undef } )->count; my $nb_biblioitems = Koha::Biblioitems->search( { itemtype => $self->itemtype } )->count; return $nb_items + $nb_biblioitems == 0 ? 1 : 0; } diff --git a/Koha/OAI/Server/GetRecord.pm b/Koha/OAI/Server/GetRecord.pm index d4870e3887..244479cfdb 100644 --- a/Koha/OAI/Server/GetRecord.pm +++ b/Koha/OAI/Server/GetRecord.pm @@ -39,21 +39,17 @@ sub new { my $sql = " SELECT timestamp FROM biblioitems - WHERE biblionumber=? + WHERE biblionumber=? AND deleted_at IS NULL "; my @bind_params = ($biblionumber); if ( $items_included ) { # Take latest timestamp of biblio and any items $sql .= " - UNION - SELECT timestamp from deleteditems - WHERE biblionumber=? UNION SELECT timestamp from items WHERE biblionumber=? "; push @bind_params, $biblionumber; - push @bind_params, $biblionumber; $sql = " SELECT max(timestamp) FROM ($sql) bib @@ -66,8 +62,8 @@ sub new { unless ( ($timestamp = $sth->fetchrow) ) { $sql = " SELECT timestamp - FROM deletedbiblio - WHERE biblionumber=? + FROM biblio + WHERE biblionumber=? AND deleted_at IS NOT NULL "; @bind_params = ($biblionumber); @@ -75,8 +71,8 @@ sub new { # Take latest timestamp among biblio and items $sql .= " UNION - SELECT timestamp from deleteditems - WHERE biblionumber=? + SELECT timestamp from items + WHERE biblionumber=? AND deleted_at IS NULL "; push @bind_params, $biblionumber; $sql = " diff --git a/Koha/OAI/Server/ListBase.pm b/Koha/OAI/Server/ListBase.pm index 92af1c3661..c2a835d7fb 100644 --- a/Koha/OAI/Server/ListBase.pm +++ b/Koha/OAI/Server/ListBase.pm @@ -68,12 +68,12 @@ sub GetRecords { if ($include_items) { $sql .= " - OR biblionumber IN (SELECT biblionumber from deleteditems WHERE timestamp >= ? AND timestamp <= ?) + OR biblionumber IN (SELECT biblionumber from items WHERE timestamp >= ? AND timestamp <= ? AND deleted_at IS NOT NULL) "; push @bind_params, ($token->{'from_arg'}, $token->{'until_arg'}); if (!$deleted) { $sql .= " - OR biblionumber IN (SELECT biblionumber from items WHERE timestamp >= ? AND timestamp <= ?) + OR biblionumber IN (SELECT biblionumber from items WHERE timestamp >= ? AND timestamp <= ? AND deleted_at IS NULL) "; push @bind_params, ($token->{'from_arg'}, $token->{'until_arg'}); } @@ -105,7 +105,7 @@ sub GetRecords { FROM ( SELECT timestamp FROM deletedbiblio_metadata WHERE biblionumber = ? UNION - SELECT timestamp FROM deleteditems WHERE biblionumber = ? + SELECT timestamp FROM items WHERE biblionumber = ? AND deleted_at IS NOT NULL ) bis "; } else { @@ -114,9 +114,9 @@ sub GetRecords { FROM ( SELECT timestamp FROM biblio_metadata WHERE biblionumber = ? UNION - SELECT timestamp FROM deleteditems WHERE biblionumber = ? + SELECT timestamp FROM items WHERE biblionumber = ? AND deleted_at IS NULL UNION - SELECT timestamp FROM items WHERE biblionumber = ? + SELECT timestamp FROM items WHERE biblionumber = ? AND deleted_at IS NOT NULL ) bi "; } diff --git a/Koha/Patron.pm b/Koha/Patron.pm index 35cfce4ca1..1da16360e9 100644 --- a/Koha/Patron.pm +++ b/Koha/Patron.pm @@ -326,7 +326,7 @@ sub do_check_for_previous_checkout { my ( $self, $item ) = @_; # Find all items for bib and extract item numbers. - my @items = Koha::Items->search({biblionumber => $item->{biblionumber}}); + my @items = Koha::Items->search({biblionumber => $item->{biblionumber}, deleted_at => undef }); my @item_nos; foreach my $item (@items) { push @item_nos, $item->itemnumber; diff --git a/admin/branches.pl b/admin/branches.pl index ab1021cdf6..4ff237d0b6 100755 --- a/admin/branches.pl +++ b/admin/branches.pl @@ -108,7 +108,8 @@ if ( $op eq 'add_form' ) { my $items_count = Koha::Items->search( { -or => { holdingbranch => $branchcode, - homebranch => $branchcode + homebranch => $branchcode, + deleted_at => undef, }, } )->count; diff --git a/cataloguing/merge.pl b/cataloguing/merge.pl index 146e88e598..0633a39097 100755 --- a/cataloguing/merge.pl +++ b/cataloguing/merge.pl @@ -88,7 +88,7 @@ if ($merge) { # Moving items from the other record to the reference record foreach my $biblionumber (@biblionumbers) { - my $items = Koha::Items->search({ biblionumber => $biblionumber }); + my $items = Koha::Items->search({ biblionumber => $biblionumber, deleted_at => undef }); while ( my $item = $items->next) { my $res = MoveItemFromBiblio( $item->itemnumber, $biblionumber, $ref_biblionumber ); if ( not defined $res ) { diff --git a/labels/label-item-search.pl b/labels/label-item-search.pl index 57f3ff3ea8..c915ffc90b 100755 --- a/labels/label-item-search.pl +++ b/labels/label-item-search.pl @@ -137,7 +137,7 @@ if ($show_results) { push (@results_set, $biblio); my $biblionumber = $biblio->{'biblionumber'}; #DEBUG Notes: Grab the item numbers associated with this MARC record... - my $items = Koha::Items->search({ biblionumber => $biblionumber }, { order_by => { -desc => 'itemnumber' }}); + my $items = Koha::Items->search({ biblionumber => $biblionumber, deleted_at => undef }, { order_by => { -desc => 'itemnumber' }}); #DEBUG Notes: Retrieve the item data for each number... while ( my $item = $items->next ) { #DEBUG Notes: Build an array element 'item' of the correct bib (results) hash which contains item-specific data... diff --git a/misc/cronjobs/delete_patrons.pl b/misc/cronjobs/delete_patrons.pl index 4470746a96..e7a2492249 100755 --- a/misc/cronjobs/delete_patrons.pl +++ b/misc/cronjobs/delete_patrons.pl @@ -45,7 +45,7 @@ cronlogaction(); my $members = GetBorrowersToExpunge( { - not_borrowed_since => $not_borrowed_since, + not_borrowed_since => $not_borrowed_since, expired_before => $expired_before, last_seen => $last_seen, category_code => $category_code, diff --git a/misc/export_records.pl b/misc/export_records.pl index 6e363686a3..061700ff5e 100755 --- a/misc/export_records.pl +++ b/misc/export_records.pl @@ -103,22 +103,14 @@ $timestamp = ($timestamp) ? output_pref({ dt => dt_from_string($timestamp), date if ( $record_type eq 'bibs' ) { if ( $timestamp ) { push @record_ids, $_->{biblionumber} for @{ - $dbh->selectall_arrayref(q| ( - SELECT biblio_metadata.biblionumber - FROM biblio_metadata - LEFT JOIN items USING(biblionumber) - WHERE biblio_metadata.timestamp >= ? - OR items.timestamp >= ? - ) UNION ( - SELECT biblio_metadata.biblionumber - FROM biblio_metadata - LEFT JOIN deleteditems USING(biblionumber) - WHERE biblio_metadata.timestamp >= ? - OR deleteditems.timestamp >= ? - ) |, { Slice => {} }, ( $timestamp ) x 4 ); + $dbh->selectall_arrayref(q| + SELECT biblionumber + FROM biblioitems + |, { Slice => {} }, ( $timestamp ) x 4 ); }; } else { my $conditions = { + deleted_at => undef, ( $starting_biblionumber or $ending_biblionumber ) ? ( "me.biblionumber" => { @@ -189,8 +181,8 @@ if ($deleted_barcodes) { for my $record_id ( @record_ids ) { my $barcode = $dbh->selectall_arrayref(q| SELECT DISTINCT barcode - FROM deleteditems - WHERE deleteditems.biblionumber = ? + FROM items + WHERE deleteditems.biblionumber = ? AND deleted_at IS NOT NULL |, { Slice => {} }, $record_id ); say $_->{barcode} for @$barcode; } diff --git a/opac/opac-reserve.pl b/opac/opac-reserve.pl index a9af8d168a..c3fd1a2cb4 100755 --- a/opac/opac-reserve.pl +++ b/opac/opac-reserve.pl @@ -286,7 +286,7 @@ if ( $query->param('place_reserve') ) { } unless ( $can_place_hold_if_available_at_pickup ) { - my $items_in_this_library = Koha::Items->search({ biblionumber => $biblioNum, holdingbranch => $branch }); + my $items_in_this_library = Koha::Items->search({ biblionumber => $biblioNum, holdingbranch => $branch, deleted_at => undef }); my $nb_of_items_issued = $items_in_this_library->search({ 'issue.itemnumber' => { not => undef }}, { join => 'issue' })->count; my $nb_of_items_unavailable = $items_in_this_library->search({ -or => { lost => { '!=' => 0 }, damaged => { '!=' => 0 }, } }); if ( $items_in_this_library->count > $nb_of_items_issued + $nb_of_items_unavailable ) { @@ -537,7 +537,7 @@ foreach my $biblioNum (@biblionumbers) { $numCopiesAvailable++; unless ( $can_place_hold_if_available_at_pickup ) { - my $items_in_this_library = Koha::Items->search({ biblionumber => $itemInfo->{biblionumber}, holdingbranch => $itemInfo->{holdingbranch} }); + my $items_in_this_library = Koha::Items->search({ biblionumber => $itemInfo->{biblionumber}, holdingbranch => $itemInfo->{holdingbranch}, deleted_at => undef }); my $nb_of_items_issued = $items_in_this_library->search({ 'issue.itemnumber' => { not => undef }}, { join => 'issue' })->count; if ( $items_in_this_library->count > $nb_of_items_issued ) { push @not_available_at, $itemInfo->{holdingbranch}; diff --git a/reports/catalogue_stats.pl b/reports/catalogue_stats.pl index b7bd98a024..e92a4c3f55 100755 --- a/reports/catalogue_stats.pl +++ b/reports/catalogue_stats.pl @@ -172,7 +172,10 @@ sub calculate { my $barcodelike = @$filters[16]; my $barcodefilter = @$filters[17]; my $not; - my $itemstable = ($cellvalue eq 'deleteditems') ? 'deleteditems' : 'items'; + my $itemstable = 'items'; + my $deleted_where = "items.deleted_at IS NULL"; + $deleted_where = "1" if $cellvalue eq 'allitems'; + $deleted_where = "items.deleted_at IS NOT NULL" if $cellvalue eq 'deleteditems'; my $dbh = C4::Context->dbh; @@ -273,12 +276,9 @@ sub calculate { # 1st, loop rows. my $origline = $line; - $line =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems"); my $linefield; if ( ( $line =~ /itemcallnumber/ ) and ($cotedigits) ) { $linefield = "left($line,$cotedigits)"; - } elsif ( $line =~ /^deleteditems\.timestamp$/ ) { - $linefield = "DATE($line)"; } else { $linefield = $line; } @@ -286,7 +286,7 @@ sub calculate { my $strsth = "SELECT DISTINCTROW $linefield FROM $itemstable LEFT JOIN biblioitems USING (biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) - WHERE 1 "; + WHERE $deleted_where "; $strsth .= " AND barcode $not LIKE ? " if ($barcodefilter); if (@linefilter) { if ( $linefilter[1] ) { @@ -335,7 +335,6 @@ sub calculate { # 2nd, loop cols. my $origcolumn = $column; - $column =~ s/^items\./deleteditems./ if($cellvalue eq "deleteditems"); my $colfield; if ( ( $column =~ /itemcallnumber/ ) and ($cotedigits) ) { $colfield = "left($column,$cotedigits)"; @@ -352,7 +351,7 @@ sub calculate { USING (biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) - WHERE 1 "; + WHERE $deleted_where "; $strsth2 .= " AND barcode $not LIKE ?" if $barcodefilter; if ( (@colfilter) and ( $colfilter[1] ) ) { @@ -418,7 +417,7 @@ sub calculate { FROM $itemstable LEFT JOIN biblioitems ON ($itemstable.biblioitemnumber = biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) - WHERE 1 "; + WHERE $deleted_where "; my @sqlargs; @@ -501,13 +500,13 @@ sub calculate { push @sqlargs, @$filters[14]; } if ( $cellvalue eq 'deleteditems' and @$filters[15] ) { - $strcalc .= " AND DATE(deleteditems.timestamp) >= ? "; + $strcalc .= " AND DATE(deleted_at) >= ? "; @$filters[15] =~ s/\*/%/g; push @sqlargs, @$filters[15]; } if ( $cellvalue eq 'deleteditems' and @$filters[16] ) { @$filters[16] =~ s/\*/%/g; - $strcalc .= " AND DATE(deleteditems.timestamp) <= ?"; + $strcalc .= " AND DATE(deleted_at) <= ?"; push @sqlargs, @$filters[16]; } $strcalc .= " group by $linefield, $colfield order by $linefield,$colfield"; diff --git a/reports/itemslost.pl b/reports/itemslost.pl index cd5fbe4b95..3bb5a1e514 100755 --- a/reports/itemslost.pl +++ b/reports/itemslost.pl @@ -115,6 +115,7 @@ if ( $op eq 'export' ) { my $notforloanfilter = $params->{'notforloanfilter'} || undef; my $params = { + deleted_at => undef, ( $branchfilter ? ( homebranch => $branchfilter ) : () ), ( $loststatusfilter diff --git a/t/db_dependent/Biblio.t b/t/db_dependent/Biblio.t index bb43bb1dd0..e53c695d81 100755 --- a/t/db_dependent/Biblio.t +++ b/t/db_dependent/Biblio.t @@ -443,16 +443,15 @@ subtest 'IsMarcStructureInternal' => sub { is( grep( /^ind2_defaultvalue$/, @internals ), 1, 'check indicator 2 default value' ); }; -subtest 'deletedbiblio_metadata' => sub { +subtest 'delete biblio' => sub { plan tests => 2; my ($biblionumber, $biblioitemnumber) = AddBiblio(MARC::Record->new, ''); my $biblio_metadata = C4::Biblio::GetXmlBiblio( $biblionumber ); - C4::Biblio::DelBiblio( $biblionumber ); - my ( $moved ) = $dbh->selectrow_array(q|SELECT biblionumber FROM deletedbiblio WHERE biblionumber=?|, undef, $biblionumber); - is( $moved, $biblionumber, 'Found in deletedbiblio' ); - ( $moved ) = $dbh->selectrow_array(q|SELECT biblionumber FROM deletedbiblio_metadata WHERE biblionumber=?|, undef, $biblionumber); - is( $moved, $biblionumber, 'Found in deletedbiblio_metadata' ); + my $err = C4::Biblio::DelBiblio( $biblionumber ); + is($err, undef, 'Biblio deleted without error'); + my $data = GetBiblioData( $biblionumber ); + isnt( $data->{deleted_at}, undef, 'Biblio deleted with timestamp' ); }; # Cleanup diff --git a/t/db_dependent/Items.t b/t/db_dependent/Items.t index ef098734c9..0a02666bba 100755 --- a/t/db_dependent/Items.t +++ b/t/db_dependent/Items.t @@ -81,7 +81,7 @@ subtest 'General Add, Get and Del tests' => sub { # Delete item. DelItem({ biblionumber => $bibnum, itemnumber => $itemnumber }); my $getdeleted = GetItem($itemnumber); - is($getdeleted->{'itemnumber'}, undef, "Item deleted as expected."); + isnt($getdeleted->{'deleted_at'}, undef, "Item deleted as expected."); ($item_bibnum, $item_bibitemnum, $itemnumber) = AddItem({ homebranch => $library->{branchcode}, holdingbranch => $library->{branchcode}, location => $location, permanent_location => 'my permanent location', itype => $itemtype->{itemtype} } , $bibnum); $getitem = GetItem($itemnumber); diff --git a/t/db_dependent/Items/DelItem.t b/t/db_dependent/Items/DelItem.t index 2880f11b03..49e3da11af 100644 --- a/t/db_dependent/Items/DelItem.t +++ b/t/db_dependent/Items/DelItem.t @@ -28,20 +28,20 @@ my ( $item_bibnum, $item_bibitemnum, $itemnumber ); my $deleted = DelItem( { biblionumber => $biblionumber, itemnumber => $itemnumber } ); is( $deleted, 1, "DelItem should return 1 if the item has been deleted" ); my $deleted_item = GetItem($itemnumber); -is( $deleted_item->{itemnumber}, undef, "DelItem with biblionumber parameter - the item should be deleted." ); +isnt( $deleted_item->{deleted_at}, undef, "DelItem with biblionumber parameter - the item should be deleted." ); ( $item_bibnum, $item_bibitemnum, $itemnumber ) = AddItem( { homebranch => $library->{branchcode}, holdingbranch => $library->{branchcode} }, $biblionumber ); $deleted = DelItem( { biblionumber => $biblionumber, itemnumber => $itemnumber } ); is( $deleted, 1, "DelItem should return 1 if the item has been deleted" ); $deleted_item = GetItem($itemnumber); -is( $deleted_item->{itemnumber}, undef, "DelItem without biblionumber parameter - the item should be deleted." ); +isnt( $deleted_item->{deleted_at}, undef, "DelItem without biblionumber parameter - the item should be deleted." ); $deleted = DelItem( { itemnumber => $itemnumber + 1} ); -is ( $deleted, 0, "DelItem should return 0 if no item has been deleted" ); +is( $deleted, 0, "DelItem should return 0 if no item has been deleted" ); $deleted = DelItem( { itemnumber => $itemnumber + 1, biblionumber => $biblionumber } ); -is ( $deleted, 0, "DelItem should return 0 if no item has been deleted" ); +is( $deleted, 0, "DelItem should return 0 if no item has been deleted" ); # Helper method to set up a Biblio. sub get_biblio { -- 2.14.2