Bugzilla – Attachment 94166 Details for
Bug 23805
Add a dedicated credit_types table
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 23805: Add account_credit_types tables
Bug-23805-Add-accountcredittypes-tables.patch (text/plain), 9.14 KB, created by
Martin Renvoize (ashimema)
on 2019-10-15 11:05:29 UTC
(
hide
)
Description:
Bug 23805: Add account_credit_types tables
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2019-10-15 11:05:29 UTC
Size:
9.14 KB
patch
obsolete
>From 685852ba7b74edcb20ecc5c7acff0511a01ba775 Mon Sep 17 00:00:00 2001 >From: Martin Renvoize <martin.renvoize@ptfs-europe.com> >Date: Mon, 14 Oct 2019 10:57:13 +0100 >Subject: [PATCH] Bug 23805: Add account_credit_types tables > >--- > C4/Installer.pm | 1 + > installer/data/mysql/account_credit_types.sql | 8 ++ > .../mysql/atomicupdate/bug_23805_credit.perl | 94 +++++++++++++++++++ > .../en/mandatory/account_credit_types.sql | 8 ++ > installer/data/mysql/kohastructure.sql | 29 +++++- > 5 files changed, 139 insertions(+), 1 deletion(-) > create mode 100644 installer/data/mysql/account_credit_types.sql > create mode 100644 installer/data/mysql/atomicupdate/bug_23805_credit.perl > create mode 100644 installer/data/mysql/en/mandatory/account_credit_types.sql > >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/en/mandatory/account_credit_types.sql b/installer/data/mysql/en/mandatory/account_credit_types.sql >new file mode 100644 >index 0000000000..7828d09199 >--- /dev/null >+++ b/installer/data/mysql/en/mandatory/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/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index e720809171..215dd6e96a 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` > -- >@@ -2660,7 +2685,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 >@@ -2674,6 +2699,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,6 +2710,7 @@ 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 SET NULL ON UPDATE CASCADE, > CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) ON DELETE SET NULL ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; > >-- >2.20.1
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 23805
:
94153
|
94154
|
94155
|
94156
|
94157
|
94158
|
94159
|
94160
|
94161
|
94162
|
94163
|
94164
|
94166
|
94167
|
94168
|
94169
|
94170
|
94171
|
94172
|
94173
|
94174
|
94175
|
94176
|
94177
|
94178
|
94801
|
94815
|
94832
|
94895