From 06b1a3fac2ba1c695deb7fbe4f60a4b5f9425d35 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 --- 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 a257017..070901d 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 eaff256..7a3daf5 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 e7279ff..13ef40f 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 73b86b6..7ecc7c8 100644 --- a/C4/Reserves.pm +++ b/C4/Reserves.pm @@ -1121,7 +1121,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; @@ -2010,7 +2010,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 d226304..ccec4ae 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 fed3aa2..e41d04b 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 24be0d2..4c815fe 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 d4870e3..244479c 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 92af1c3..c2a835d 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 c8e319e..55361a4 100644 --- a/Koha/Patron.pm +++ b/Koha/Patron.pm @@ -247,7 +247,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 ab1021c..4ff237d 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 146e88e..0633a39 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 57f3ff3..c915ffc 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 4470746..e7a2492 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 6e36368..061700f 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 2be6991..93c75f7 100755 --- a/opac/opac-reserve.pl +++ b/opac/opac-reserve.pl @@ -294,7 +294,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 ) { @@ -545,7 +545,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 b7bd98a..e92a4c3 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 cd5fbe4..3bb5a1e 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 bb43bb1..e53c695 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 ef09873..0a02666 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 2880f11..49e3da1 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.1.4