From 473bf22e42dfe9297a22ed5c6d5c0bdc3e97b99f Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Fri, 1 Sep 2017 07:51:49 -0400 Subject: [PATCH] Bug 14826: Add account offset type table Signed-off-by: Tomas Cohen Arazi Signed-off-by: Jonathan Druart --- C4/Installer.pm | 1 + installer/data/mysql/account_offset_types.sql | 11 ++++++++++ .../data/mysql/atomicupdate/bug_14826.sql | 21 +++++++++++++++++-- installer/data/mysql/kohastructure.sql | 13 +++++++++++- misc/devel/populate_db.pl | 1 + 5 files changed, 44 insertions(+), 3 deletions(-) create mode 100644 installer/data/mysql/account_offset_types.sql diff --git a/C4/Installer.pm b/C4/Installer.pm index 866d1f2d73..74ae288aef 100644 --- a/C4/Installer.pm +++ b/C4/Installer.pm @@ -327,6 +327,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/mandatory/refund_lost_item_fee_rules.sql"; + push @fnames, C4::Context->config('intranetdir') . "/installer/data/mysql/mandatory/account_offset_types.sql"; foreach my $file (@fnames) { # warn $file; undef $/; diff --git a/installer/data/mysql/account_offset_types.sql b/installer/data/mysql/account_offset_types.sql new file mode 100644 index 0000000000..2f9004c5c7 --- /dev/null +++ b/installer/data/mysql/account_offset_types.sql @@ -0,0 +1,11 @@ +INSERT INTO account_offset_types ( type ) VALUES +('Writeoff'), +('Payment'), +('Lost Item'), +('Manual Debit'), +('Reverse Payment'), +('Forgiven'), +('Dropbox'), +('Rental Fee'), +('Fine Update'), +('Fine'); diff --git a/installer/data/mysql/atomicupdate/bug_14826.sql b/installer/data/mysql/atomicupdate/bug_14826.sql index 91d183bba4..bfbcf1bb66 100644 --- a/installer/data/mysql/atomicupdate/bug_14826.sql +++ b/installer/data/mysql/atomicupdate/bug_14826.sql @@ -1,4 +1,8 @@ -DROP TABLE IF EXISTS `accountoffsets`; +CREATE TABLE `account_offset_types` ( + `type` varchar(16) NOT NULL, -- The type of offset this is + PRIMARY KEY (`type`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + CREATE TABLE IF NOT EXISTS `account_offsets` ( `id` int(11) NOT NULL auto_increment, -- unique identifier for each offset `credit_id` int(11) NULL DEFAULT NULL, -- The id of the accountline the increased the patron's balance @@ -8,5 +12,18 @@ CREATE TABLE IF NOT EXISTS `account_offsets` ( `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), CONSTRAINT `account_offsets_ibfk_p` FOREIGN KEY (`credit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `account_offsets_ibfk_f` FOREIGN KEY (`debit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `account_offsets_ibfk_f` FOREIGN KEY (`debit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `account_offsets_ibfk_t` FOREIGN KEY (`type`) REFERENCES `account_offset_types` (`type`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + +INSERT INTO account_offset_types ( type ) VALUES +('Writeoff'), +('Payment'), +('Lost Item'), +('Manual Debit'), +('Reverse Payment'), +('Forgiven'), +('Dropbox'), +('Rental Fee'), +('Fine Update'), +('Fine'); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index d339e41cef..e229d3c85a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2736,6 +2736,16 @@ CREATE TABLE `accountlines` ( CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +-- +-- Table structure for table `account_offset_types` +-- + +DROP TABLE IF EXISTS `account_offset_types`; +CREATE TABLE `account_offset_types` ( + `type` varchar(16) NOT NULL, -- The type of offset this is + PRIMARY KEY (`type`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + -- -- Table structure for table `account_offsets` -- @@ -2750,7 +2760,8 @@ CREATE TABLE `account_offsets` ( `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), CONSTRAINT `account_offsets_ibfk_p` FOREIGN KEY (`credit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `account_offsets_ibfk_f` FOREIGN KEY (`debit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `account_offsets_ibfk_f` FOREIGN KEY (`debit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `account_offsets_ibfk_t` FOREIGN KEY (`type`) REFERENCES `account_offset_types` (`type`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- diff --git a/misc/devel/populate_db.pl b/misc/devel/populate_db.pl index 9bad0495ab..e419e3743b 100755 --- a/misc/devel/populate_db.pl +++ b/misc/devel/populate_db.pl @@ -106,6 +106,7 @@ my @sample_files_mandatory = ( "$data_dir/sysprefs.sql", "$data_dir/userflags.sql", "$data_dir/userpermissions.sql", + "$data_dir/account_offset_types.sql", ); my @sample_lang_files_mandatory = ( glob $root . "/installer/data/mysql/$lang/mandatory/*.sql" ); my @sample_lang_files_optional = ( glob $root . "/installer/data/mysql/$lang/optional/*.sql" ); -- 2.39.5