From dbd72dcfa1cc51072a48cabea9b7ce244db8b3b8 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Thu, 24 Oct 2019 17:28:40 +0100 Subject: [PATCH] Bug 23049: DBRev 19.06.00.043 Signed-off-by: Martin Renvoize --- Koha.pm | 2 +- .../mysql/atomicupdate/bug_23049_debit.perl | 229 ----------------- installer/data/mysql/updatedatabase.pl | 230 ++++++++++++++++++ 3 files changed, 231 insertions(+), 230 deletions(-) delete mode 100644 installer/data/mysql/atomicupdate/bug_23049_debit.perl diff --git a/Koha.pm b/Koha.pm index 4f46c53772..a75c02cf8d 100644 --- a/Koha.pm +++ b/Koha.pm @@ -29,7 +29,7 @@ use vars qw{ $VERSION }; # - #4 : the developer version. The 4th number is the database subversion. # used by developers when the database changes. updatedatabase take care of the changes itself # and is automatically called by Auth.pm when needed. -$VERSION = "19.06.00.042"; +$VERSION = "19.06.00.043"; sub version { return $VERSION; diff --git a/installer/data/mysql/atomicupdate/bug_23049_debit.perl b/installer/data/mysql/atomicupdate/bug_23049_debit.perl deleted file mode 100644 index b7c6e5bb2e..0000000000 --- a/installer/data/mysql/atomicupdate/bug_23049_debit.perl +++ /dev/null @@ -1,229 +0,0 @@ -$DBversion = 'XXX'; # will be replaced by the RM -if ( CheckVersion($DBversion) ) { - - # Adding account_debit_types - $dbh->do( - qq{ - CREATE TABLE IF NOT EXISTS account_debit_types ( - code varchar(80) NOT NULL, - description varchar(200) NULL, - can_be_added_manually tinyint(4) NOT NULL DEFAULT 1, - default_amount decimal(28, 6) NULL, - is_system tinyint(1) NOT NULL DEFAULT 0, - archived tinyint(1) NOT NULL DEFAULT 0, - PRIMARY KEY (code) - ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci - } - ); - - # Adding account_debit_types_branches - $dbh->do( - qq{ - CREATE TABLE IF NOT EXISTS account_debit_types_branches ( - debit_type_code VARCHAR(80), - branchcode VARCHAR(10), - FOREIGN KEY (debit_type_code) REFERENCES account_debit_types(code) ON DELETE CASCADE, - FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci - } - ); - - # Populating account_debit_types - $dbh->do( - qq{ - INSERT IGNORE INTO account_debit_types ( - code, - description, - can_be_added_manually, - default_amount, - is_system - ) - VALUES - ('ACCOUNT', 'Account creation fee', 0, NULL, 1), - ('ACCOUNT_RENEW', 'Account renewal fee', 0, NULL, 1), - ('RESERVE_EXPIRED', 'Hold waiting too long', 0, NULL, 1), - ('LOST', 'Lost item', 1, NULL, 1), - ('MANUAL', 'Manual fee', 1, NULL, 0), - ('NEW_CARD', 'New card fee', 1, NULL, 1), - ('OVERDUE', 'Overdue fine', 0, NULL, 1), - ('PROCESSING', 'Lost item processing fee', 0, NULL, 1), - ('RENT', 'Rental fee', 0, NULL, 1), - ('RENT_DAILY', 'Daily rental fee', 0, NULL, 1), - ('RENT_RENEW', 'Renewal of rental item', 0, NULL, 1), - ('RENT_DAILY_RENEW', 'Rewewal of daily rental item', 0, NULL, 1), - ('RESERVE', 'Hold fee', 0, NULL, 1) - } - ); - - # Update accountype 'Res' to 'RESERVE' - $dbh->do( - qq{ - UPDATE accountlines SET accounttype = 'RESERVE' WHERE accounttype = 'Res' - } - ); - - # Update accountype 'PF' to 'PROCESSING' - $dbh->do( - qq{ - UPDATE accountlines SET accounttype = 'PROCESSING' WHERE accounttype = 'PF' - } - ); - - # Update accountype 'HE' to 'RESERVE_EXPIRED' - $dbh->do( - qq{ - UPDATE accountlines SET accounttype = 'RESERVE_EXPIRED' WHERE accounttype = 'HE' - } - ); - - # Update accountype 'N' to 'NEW_CARD' - $dbh->do( - qq{ - UPDATE accountlines SET accounttype = 'NEW_CARD' WHERE accounttype = 'N' - } - ); - - # Update accountype 'M' to 'MANUAL' - $dbh->do( - qq{ - UPDATE accountlines SET accounttype = 'MANUAL' WHERE accounttype = 'M' - } - ); - - # Catch 'F' cases introduced since bug 22521 - $dbh->do(qq{ - UPDATE - accountlines - SET - accounttype = 'OVERDUE', - status = 'RETURNED' - WHERE - accounttype = 'F'; - }); - - # Moving MANUAL_INV to account_debit_types - $dbh->do( - qq{ - INSERT IGNORE INTO account_debit_types ( - code, - default_amount, - description, - can_be_added_manually, - is_system - ) - SELECT - authorised_value, - lib, - authorised_value, - 1, - 0 - FROM - authorised_values - WHERE - category = 'MANUAL_INV' - } - ); - - # Update uncaught partial accounttypes left behind after bugs 23539 and 22521 - my $sth = $dbh->prepare( "SELECT code, SUBSTR(code, 1,5) AS subcode FROM account_debit_types" ); - $sth->execute(); - while ( my $row = $sth->fetchrow_hashref ) { - $dbh->do( - qq{ - UPDATE accountlines SET accounttype = ? WHERE accounttype = ? - }, - {}, - ( - $row->{code}, - $row->{subcode} - ) - ); - } - - # Add any unexpected accounttype codes to debit_types as appropriate - $dbh->do( - qq{ - INSERT IGNORE INTO account_debit_types ( - code, - description, - can_be_added_manually, - default_amount, - is_system - ) - SELECT - DISTINCT(accounttype), - "Unexpected type found during upgrade", - 1, - NULL, - 0 - FROM - accountlines - WHERE - amount >= 0 - } - ); - - # Adding debit_type_code to accountlines - unless ( column_exists('accountlines', 'debit_type_code') ) { - $dbh->do( - qq{ - ALTER IGNORE TABLE accountlines - ADD - debit_type_code varchar(80) DEFAULT NULL - AFTER - accounttype - } - ); - } - - # Linking debit_type_code in accountlines to code in account_debit_types - unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_debit_type' ) ) { - $dbh->do( - qq{ - ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) ON DELETE RESTRICT ON UPDATE CASCADE - } - ); - } - - # Adding a check constraints to accountlines - $dbh->do( - qq{ - ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR debit_type_code IS NOT NULL) - } - ); - - # Populating debit_type_code - $dbh->do( - qq{ - UPDATE accountlines SET debit_type_code = accounttype, accounttype = NULL WHERE accounttype IN (SELECT code from account_debit_types) - } - ); - - # Remove MANUAL_INV - $dbh->do( - qq{ - DELETE FROM authorised_values WHERE category = 'MANUAL_INV' - } - ); - $dbh->do( - qq{ - DELETE FROM authorised_value_categories WHERE category_name = 'MANUAL_INV' - } - ); - - # Add new permission - $dbh->do( - q{ - INSERT IGNORE INTO permissions (module_bit, code, description) - VALUES - ( - 3, - 'manage_accounts', - 'Manage Account Debit and Credit Types' - ) - } - ); - - SetVersion($DBversion); - print "Upgrade to $DBversion done (Bug 23049 - Add account debit_types)\n"; -} diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index b7deb3c434..bac3f54845 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -19707,6 +19707,236 @@ if( CheckVersion( $DBversion ) ) { print "Upgrade to $DBversion done (Bug 22445 - Add new pref *CustomCoverImages*)\n"; } +$DBversion = '19.06.00.043'; +if ( CheckVersion($DBversion) ) { + + # Adding account_debit_types + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_debit_types ( + code varchar(80) NOT NULL, + description varchar(200) NULL, + can_be_added_manually tinyint(4) NOT NULL DEFAULT 1, + default_amount decimal(28, 6) NULL, + is_system tinyint(1) NOT NULL DEFAULT 0, + archived tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY (code) + ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci + } + ); + + # Adding account_debit_types_branches + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_debit_types_branches ( + debit_type_code VARCHAR(80), + branchcode VARCHAR(10), + FOREIGN KEY (debit_type_code) REFERENCES account_debit_types(code) ON DELETE CASCADE, + FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci + } + ); + + # Populating account_debit_types + $dbh->do( + qq{ + INSERT IGNORE INTO account_debit_types ( + code, + description, + can_be_added_manually, + default_amount, + is_system + ) + VALUES + ('ACCOUNT', 'Account creation fee', 0, NULL, 1), + ('ACCOUNT_RENEW', 'Account renewal fee', 0, NULL, 1), + ('RESERVE_EXPIRED', 'Hold waiting too long', 0, NULL, 1), + ('LOST', 'Lost item', 1, NULL, 1), + ('MANUAL', 'Manual fee', 1, NULL, 0), + ('NEW_CARD', 'New card fee', 1, NULL, 1), + ('OVERDUE', 'Overdue fine', 0, NULL, 1), + ('PROCESSING', 'Lost item processing fee', 0, NULL, 1), + ('RENT', 'Rental fee', 0, NULL, 1), + ('RENT_DAILY', 'Daily rental fee', 0, NULL, 1), + ('RENT_RENEW', 'Renewal of rental item', 0, NULL, 1), + ('RENT_DAILY_RENEW', 'Rewewal of daily rental item', 0, NULL, 1), + ('RESERVE', 'Hold fee', 0, NULL, 1) + } + ); + + # Update accountype 'Res' to 'RESERVE' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'RESERVE' WHERE accounttype = 'Res' + } + ); + + # Update accountype 'PF' to 'PROCESSING' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'PROCESSING' WHERE accounttype = 'PF' + } + ); + + # Update accountype 'HE' to 'RESERVE_EXPIRED' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'RESERVE_EXPIRED' WHERE accounttype = 'HE' + } + ); + + # Update accountype 'N' to 'NEW_CARD' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'NEW_CARD' WHERE accounttype = 'N' + } + ); + + # Update accountype 'M' to 'MANUAL' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'MANUAL' WHERE accounttype = 'M' + } + ); + + # Catch 'F' cases introduced since bug 22521 + $dbh->do(qq{ + UPDATE + accountlines + SET + accounttype = 'OVERDUE', + status = 'RETURNED' + WHERE + accounttype = 'F'; + }); + + # Moving MANUAL_INV to account_debit_types + $dbh->do( + qq{ + INSERT IGNORE INTO account_debit_types ( + code, + default_amount, + description, + can_be_added_manually, + is_system + ) + SELECT + authorised_value, + lib, + authorised_value, + 1, + 0 + FROM + authorised_values + WHERE + category = 'MANUAL_INV' + } + ); + + # Update uncaught partial accounttypes left behind after bugs 23539 and 22521 + my $sth = $dbh->prepare( "SELECT code, SUBSTR(code, 1,5) AS subcode FROM account_debit_types" ); + $sth->execute(); + while ( my $row = $sth->fetchrow_hashref ) { + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = ? WHERE accounttype = ? + }, + {}, + ( + $row->{code}, + $row->{subcode} + ) + ); + } + + # Add any unexpected accounttype codes to debit_types as appropriate + $dbh->do( + qq{ + INSERT IGNORE INTO account_debit_types ( + code, + description, + can_be_added_manually, + default_amount, + is_system + ) + SELECT + DISTINCT(accounttype), + "Unexpected type found during upgrade", + 1, + NULL, + 0 + FROM + accountlines + WHERE + amount >= 0 + } + ); + + # Adding debit_type_code to accountlines + unless ( column_exists('accountlines', 'debit_type_code') ) { + $dbh->do( + qq{ + ALTER IGNORE TABLE accountlines + ADD + debit_type_code varchar(80) DEFAULT NULL + AFTER + accounttype + } + ); + } + + # Linking debit_type_code in accountlines to code in account_debit_types + unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_debit_type' ) ) { + $dbh->do( + qq{ + ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) ON DELETE RESTRICT ON UPDATE CASCADE + } + ); + } + + # Adding a check constraints to accountlines + $dbh->do( + qq{ + ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR debit_type_code IS NOT NULL) + } + ); + + # Populating debit_type_code + $dbh->do( + qq{ + UPDATE accountlines SET debit_type_code = accounttype, accounttype = NULL WHERE accounttype IN (SELECT code from account_debit_types) + } + ); + + # Remove MANUAL_INV + $dbh->do( + qq{ + DELETE FROM authorised_values WHERE category = 'MANUAL_INV' + } + ); + $dbh->do( + qq{ + DELETE FROM authorised_value_categories WHERE category_name = 'MANUAL_INV' + } + ); + + # Add new permission + $dbh->do( + q{ + INSERT IGNORE INTO permissions (module_bit, code, description) + VALUES + ( + 3, + 'manage_accounts', + 'Manage Account Debit and Credit Types' + ) + } + ); + + SetVersion($DBversion); + print "Upgrade to $DBversion done (Bug 23049 - Add account debit_types)\n"; +} + # SEE bug 13068 # if there is anything in the atomicupdate, read and execute it. my $update_dir = C4::Context->config('intranetdir') . '/installer/data/mysql/atomicupdate/'; -- 2.20.1