@@ -, +, @@ --- C4/Acquisition.pm | 10 ++- C4/Biblio.pm | 99 ++++-------------------------- 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, 77 insertions(+), 181 deletions(-) --- a/C4/Acquisition.pm +++ a/C4/Acquisition.pm @@ -2291,10 +2291,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, @@ -2326,8 +2326,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 "; --- a/C4/Biblio.pm +++ a/C4/Biblio.pm @@ -318,7 +318,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(); @@ -387,7 +387,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 ) { @@ -418,7 +418,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 ) { @@ -759,7 +759,7 @@ NOTE : This function has been copy/paste from C4/Biblio.pm from head before zebr sub GetBiblioItemByBiblioNumber { my ($biblionumber) = @_; my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("Select * FROM biblioitems WHERE biblionumber = ?"); + my $sth = $dbh->prepare("Select * FROM biblioitems WHERE biblionumber = ? AND deleted_at IS NULL"); my $count = 0; my @results; @@ -1212,7 +1212,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'}; @@ -2830,7 +2830,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 ); @@ -3160,41 +3160,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 @@ -3212,38 +3179,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 @@ -3256,19 +3193,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 --- a/C4/Items.pm +++ a/C4/Items.pm @@ -830,6 +830,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){ @@ -936,7 +937,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; @@ -1060,7 +1061,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) @@ -1074,7 +1075,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; @@ -1181,7 +1182,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); @@ -1253,7 +1254,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 ) @@ -1300,7 +1301,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'}; @@ -2075,27 +2076,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; } @@ -2460,7 +2442,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 }; @@ -2811,7 +2793,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 ( --- a/C4/Reserves.pm +++ a/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'); --- a/Koha/Biblio.pm +++ a/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}; } --- a/Koha/Filter/MARC/EmbedItemsAvailability.pm +++ a/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 --- a/Koha/ItemType.pm +++ a/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; } --- a/Koha/OAI/Server/GetRecord.pm +++ a/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 = " --- a/Koha/OAI/Server/ListBase.pm +++ a/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 "; } --- a/Koha/Patron.pm +++ a/Koha/Patron.pm @@ -246,7 +246,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; --- a/admin/branches.pl +++ a/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; --- a/cataloguing/merge.pl +++ a/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 ) { --- a/labels/label-item-search.pl +++ a/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... --- a/misc/cronjobs/delete_patrons.pl +++ a/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, --- a/misc/export_records.pl +++ a/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; } --- a/opac/opac-reserve.pl +++ a/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}; --- a/reports/catalogue_stats.pl +++ a/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"; --- a/reports/itemslost.pl +++ a/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 --- a/t/db_dependent/Biblio.t +++ a/t/db_dependent/Biblio.t @@ -441,16 +441,15 @@ subtest 'IsMarcStructureInternal' => sub { is( grep( /^a$/, @internals ), 0, 'no subfield a' ); }; -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 --- a/t/db_dependent/Items.t +++ a/t/db_dependent/Items.t @@ -82,7 +82,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); --- a/t/db_dependent/Items/DelItem.t +++ a/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 { --