From 8cca4fbd3797d893496a5b3cf7c4a00f19a05e33 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Mon, 14 Oct 2019 10:57:13 +0100 Subject: [PATCH] Bug 23805: Add account_credit_types tables Signed-off-by: Kyle Hall Signed-off-by: Tomas Cohen Arazi Signed-off-by: Martin Renvoize --- C4/Installer.pm | 1 + installer/data/mysql/account_credit_types.sql | 8 ++ .../mysql/atomicupdate/bug_23805_credit.perl | 94 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 31 +++++- 4 files changed, 132 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/account_credit_types.sql create mode 100644 installer/data/mysql/atomicupdate/bug_23805_credit.perl diff --git a/C4/Installer.pm b/C4/Installer.pm index e4cae05c06..0a6fca8d9d 100644 --- a/C4/Installer.pm +++ b/C4/Installer.pm @@ -332,6 +332,7 @@ sub load_sql_in_order { push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/userpermissions.sql"; push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/audio_alerts.sql"; push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/account_offset_types.sql"; + push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/account_credit_types.sql"; push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/account_debit_types.sql"; foreach my $file (@fnames) { # warn $file; diff --git a/installer/data/mysql/account_credit_types.sql b/installer/data/mysql/account_credit_types.sql new file mode 100644 index 0000000000..7828d09199 --- /dev/null +++ b/installer/data/mysql/account_credit_types.sql @@ -0,0 +1,8 @@ +INSERT INTO account_debit_types ( code, description, can_be_added_manually, is_system ) VALUES +('Pay', 'Payment', 0, 1), +('PAY', 'Payment', 0, 1), +('W', 'Writeoff', 0, 1), +('WO', 'Writeoff', 0, 1), +('FOR', 'Forgiven', 1, 1), +('C', 'Credit', 1, 1), +('LOST_RETURN', 'Lost item fee refund', 0, 1); diff --git a/installer/data/mysql/atomicupdate/bug_23805_credit.perl b/installer/data/mysql/atomicupdate/bug_23805_credit.perl new file mode 100644 index 0000000000..46b0d552b7 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_23805_credit.perl @@ -0,0 +1,94 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if ( CheckVersion($DBversion) ) { + + # Adding account_credit_types + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_credit_types ( + code varchar(80) NOT NULL, + description varchar(200) NULL, + can_be_added_manually tinyint(4) NOT NULL DEFAULT 1, + is_system tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY (code) + ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci + } + ); + + # Adding account_credit_types_branches + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_credit_types_branches ( + credit_type_code VARCHAR(80), + branchcode VARCHAR(10), + FOREIGN KEY (credit_type_code) REFERENCES account_credit_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_credit_types + $dbh->do( + qq{ + INSERT IGNORE INTO account_credit_types ( + code, + description, + can_be_added_manually, + is_system + ) + VALUES + ('Pay', 'Payment', 0, 1), + ('PAY', 'Payment', 0, 1), + ('W', 'Writeoff', 0, 1), + ('WO', 'Writeoff', 0, 1), + ('FOR', 'Forgiven', 1, 1), + ('C', 'Credit', 1, 1), + ('LOST_RETURN', 'Lost item fee refund', 0, 1) + } + ); + + # Adding credit_type_code to accountlines + unless ( column_exists('accountlines', 'credit_type_code') ) { + $dbh->do( + qq{ + ALTER IGNORE TABLE accountlines + ADD + credit_type_code varchar(80) DEFAULT NULL + AFTER + accounttype + } + ); + } + + # Linking credit_type_code in accountlines to code in account_credit_types + unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_credit_type' ) ) { + $dbh->do( + qq{ + ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_credit_type` FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) ON DELETE SET NULL ON UPDATE CASCADE + } + ); + } + + # Dropping the check constraint in accountlines + $dbh->do( + qq{ + ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR credit_type_code IS NOT NULL) + } + ); + + # Populating credit_type_code + $dbh->do( + qq{ + UPDATE accountlines SET credit_type_code = accounttype, accounttype = NULL WHERE accounttype IN (SELECT code from account_credit_types) + } + ); + + # Adding a check constraints to accountlines + $dbh->do( + qq{ + ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR credit_type_code IS NOT NULL) + } + ); + + SetVersion($DBversion); + print "Upgrade to $DBversion done (Bug 23049 - Add account debit_credit)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index c11e9e245a..41f1ee37bb 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2621,6 +2621,31 @@ CREATE TABLE `cash_registers` ( CONSTRAINT cash_registers_branch FOREIGN KEY (branch) REFERENCES branches (branchcode) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; +-- +-- Table structure for table `account_credit_types` +-- + +DROP TABLE IF EXISTS `account_credit_types`; +CREATE TABLE `account_credit_types` ( + `code` varchar(80) NOT NULL, + `description` varchar(200) DEFAULT NULL, + `can_be_added_manually` tinyint(4) NOT NULL DEFAULT 1, + `is_system` tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY (`code`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `account_credit_types_branches` +-- + +DROP TABLE IF EXISTS `account_credit_types_branches`; +CREATE TABLE `account_credit_types_branches` ( + `credit_type_code` VARCHAR(80), + `branchcode` VARCHAR(10), + FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) ON DELETE CASCADE, + FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + -- -- Table structure for table `account_debit_types` -- @@ -2661,7 +2686,7 @@ CREATE TABLE `accountlines` ( `date` date default NULL, `amount` decimal(28,6) default NULL, `description` LONGTEXT, - `accounttype` varchar(80) default NULL, + `credit_type_code` varchar(80) default NULL, `debit_type_code` varchar(80) default NULL, `status` varchar(16) default NULL, `payment_type` varchar(80) default NULL, -- optional authorised value PAYMENT_TYPE @@ -2675,6 +2700,7 @@ CREATE TABLE `accountlines` ( PRIMARY KEY (`accountlines_id`), KEY `acctsborridx` (`borrowernumber`), KEY `timeidx` (`timestamp`), + KEY `credit_type_code` (`credit_type_code`), KEY `debit_type_code` (`debit_type_code`), KEY `itemnumber` (`itemnumber`), KEY `branchcode` (`branchcode`), @@ -2684,8 +2710,9 @@ CREATE TABLE `accountlines` ( CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `accountlines_ibfk_credit_type` FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) ON DELETE RESTRICT ON UPDATE CASCADE, - CONSTRAINT `accountlines_check_type` CHECK (`accounttype` IS NOT NULL OR `debit_type_code` IS NOT NULL) + CONSTRAINT `accountlines_check_type` CHECK (`credit_type_code` IS NOT NULL OR `debit_type_code` IS NOT NULL) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- 2.39.5