@@ -, +, @@
---
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
--