@@ -, +, @@ --- ill/ill-requests.pl | 2 +- ...81-add_new_illrequests_status_alias_column.perl | 31 ++++++++++++++++++++-- installer/data/mysql/kohastructure.sql | 4 +-- .../prog/en/modules/ill/ill-requests.tt | 8 +++--- 4 files changed, 36 insertions(+), 9 deletions(-) --- a/ill/ill-requests.pl +++ a/ill/ill-requests.pl @@ -174,7 +174,7 @@ if ( $backends_available ) { $request->price_paid($params->{price_paid}); $request->notesopac($params->{notesopac}); $request->notesstaff($params->{notesstaff}); - my $alias = ($params->{status_alias} =~ /\d/) ? + my $alias = ($params->{status_alias}) ? $params->{status_alias} : undef; $request->status_alias($alias); --- a/installer/data/mysql/atomicupdate/bug_20581-add_new_illrequests_status_alias_column.perl +++ a/installer/data/mysql/atomicupdate/bug_20581-add_new_illrequests_status_alias_column.perl @@ -2,10 +2,37 @@ $DBversion = 'XXX'; # will be replaced by the RM if( CheckVersion( $DBversion ) ) { if ( !column_exists( 'illrequests', 'status_alias' ) ) { - $dbh->do( "ALTER TABLE illrequests ADD COLUMN status_alias integer DEFAULT NULL AFTER status" ); + # Fresh upgrade, just add the column and constraint + $dbh->do( "ALTER TABLE illrequests ADD COLUMN status_alias varchar(80) DEFAULT NULL AFTER status" ); + } else { + # Migrate all existing foreign keys from referencing authorised_values.id + # to referencing authorised_values.authorised_value + # First remove the foreign key constraint and index + if ( foreign_key_exists( 'illrequests', 'illrequests_safk' ) ) { + $dbh->do( "ALTER TABLE illrequests DROP FOREIGN KEY illrequests_safk"); + } + if ( index_exists( 'illrequests', 'illrequests_safk' ) ) { + $dbh->do( "DROP INDEX illrequests_safk IN illrequests" ); + } + # Now change the illrequests.status_alias column definition from int to varchar + $dbh->do( "ALTER TABLE illrequests MODIFY COLUMN status_alias varchar(80)" ); + # Now replace all references to authorised_values.id with their + # corresponding authorised_values.authorised_value + my $sth = $dbh->prepare( "SELECT illrequest_id, status_alias FROM illrequests WHERE status_alias IS NOT NULL" ); + $sth->execute(); + while (my @row = $sth->fetchrow_array()) { + my $r_id = $row[0]; + my $av_id = $row[1]; + # Get the authorised value's authorised_value value + my ($av_val) = $dbh->selectrow_array( "SELECT authorised_value FROM authorised_values WHERE id = ?", {}, $av_id ); + # Now update illrequests.status_alias + if ($av_val) { + $dbh->do( "UPDATE illrequests SET status_alias = ? WHERE illrequest_id = ?", {}, ($av_val, $r_id) ); + } + } } if ( !foreign_key_exists( 'illrequests', 'illrequests_safk' ) ) { - $dbh->do( "ALTER TABLE illrequests ADD CONSTRAINT illrequests_safk FOREIGN KEY (status_alias) REFERENCES authorised_values(id) ON UPDATE CASCADE ON DELETE SET NULL" ); + $dbh->do( "ALTER TABLE illrequests ADD CONSTRAINT illrequests_safk FOREIGN KEY (status_alias) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE SET NULL" ); } $dbh->do( "INSERT IGNORE INTO authorised_value_categories SET category_name = 'ILLSTATUS'"); --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -4171,7 +4171,7 @@ CREATE TABLE illrequests ( biblio_id integer DEFAULT NULL, -- Potential bib linked to request branchcode varchar(50) NOT NULL, -- The branch associated with the request status varchar(50) DEFAULT NULL, -- Current Koha status of request - status_alias integer DEFAULT NULL, -- Foreign key to relevant authorised_values.id + status_alias varchar(80) DEFAULT NULL, -- Foreign key to relevant authorised_values.authorised_value placed date DEFAULT NULL, -- Date the request was placed replied date DEFAULT NULL, -- Last API response updated timestamp DEFAULT CURRENT_TIMESTAMP -- Last modification to request @@ -4195,7 +4195,7 @@ CREATE TABLE illrequests ( ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT `illrequests_safk` FOREIGN KEY (`status_alias`) - REFERENCES `authorised_values` (`id`) + REFERENCES `authorised_values` (`authorised_value`) ON UPDATE CASCADE ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; --- a/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt @@ -204,10 +204,10 @@ [% request.capabilities.$stat.name | html %] [% FOREACH alias IN AuthorisedValues.Get('ILLSTATUS') %] - [% IF alias.id == current_alias %] - @@ -874,7 +874,7 @@ // Get our data from the API and process it prior to passing // it to datatables var ajax = $.ajax( - '/api/v1/illrequests?embed=metadata,patron,capabilities,library' + '/api/v1/illrequests?embed=metadata,patron,capabilities,library,status_alias' ).done(function() { var data = JSON.parse(ajax.responseText); // Make a copy, we'll be removing columns next and need --