From a92699a01198a86bfc79947797c198a96c936b83 Mon Sep 17 00:00:00 2001 From: Josef Moravec Date: Wed, 23 Nov 2016 14:14:16 +0000 Subject: [PATCH] Bug 17702: Account types configuration - db changes Test plan: 1) Read the diff and confirm that it does make sense 2) Comment what does not make sense ;) --- Koha/Account/CreditType.pm | 60 +++++++++ Koha/Account/CreditTypes.pm | 52 ++++++++ Koha/Account/DebitType.pm | 60 +++++++++ Koha/Account/DebitTypes.pm | 52 ++++++++ Koha/Schema/Result/AccountCreditType.pm | 81 ++++++++++++ Koha/Schema/Result/AccountDebitType.pm | 89 +++++++++++++ Koha/Schema/Result/Accountline.pm | 14 +- .../data/mysql/atomicupdate/bug_XXX.perl | 124 ++++++++++++++++++ .../en/mandatory/account_credit_types.sql | 13 ++ .../en/mandatory/account_debit_types.sql | 13 ++ installer/data/mysql/en/optional/auth_val.sql | 3 - installer/data/mysql/kohastructure.sql | 26 ++++ 12 files changed, 581 insertions(+), 6 deletions(-) create mode 100644 Koha/Account/CreditType.pm create mode 100644 Koha/Account/CreditTypes.pm create mode 100644 Koha/Account/DebitType.pm create mode 100644 Koha/Account/DebitTypes.pm create mode 100644 Koha/Schema/Result/AccountCreditType.pm create mode 100644 Koha/Schema/Result/AccountDebitType.pm create mode 100644 installer/data/mysql/atomicupdate/bug_XXX.perl create mode 100644 installer/data/mysql/en/mandatory/account_credit_types.sql create mode 100644 installer/data/mysql/en/mandatory/account_debit_types.sql diff --git a/Koha/Account/CreditType.pm b/Koha/Account/CreditType.pm new file mode 100644 index 0000000000..d08bacb0fe --- /dev/null +++ b/Koha/Account/CreditType.pm @@ -0,0 +1,60 @@ +package Koha::Account::CreditType; + +# Copyright ByWater Solutions 2014 +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 3 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use Modern::Perl; + +use Carp; + +use Koha::Database; +use Koha::Exceptions; + +use base qw(Koha::Object); + +=head1 NAME + +Koha::Account::CreditType - Koha Account credit type Object class + +=head1 API + +=head2 Class Methods + +=cut + +=head3 delete + +=cut + +sub delete { + my ($self) = @_; + + if(not $self->can_be_deleted) { + Koha::Exceptions::CannotDeleteDefault->throw; + } + return $self->SUPER::delete($self); +} + +=head3 type + +=cut + +sub _type { + return 'AccountCreditType'; +} + +1; diff --git a/Koha/Account/CreditTypes.pm b/Koha/Account/CreditTypes.pm new file mode 100644 index 0000000000..8f5ba45d65 --- /dev/null +++ b/Koha/Account/CreditTypes.pm @@ -0,0 +1,52 @@ +package Koha::Account::CreditTypes; + +# Copyright ByWater Solutions 2014 +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 3 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use Modern::Perl; + +use Carp; + +use Koha::Database; + +use Koha::Account::CreditType; + +use base qw(Koha::Objects); + +=head1 NAME + +Koha::Account::CreditTypes - Koha Account credit types Object set class + +=head1 API + +=head2 Class Methods + +=cut + +=head3 type + +=cut + +sub _type { + return 'AccountCreditType'; +} + +sub object_class { + return 'Koha::Account::CreditType'; +} + +1; diff --git a/Koha/Account/DebitType.pm b/Koha/Account/DebitType.pm new file mode 100644 index 0000000000..0ab6ebf709 --- /dev/null +++ b/Koha/Account/DebitType.pm @@ -0,0 +1,60 @@ +package Koha::Account::DebitType; + +# Copyright ByWater Solutions 2014 +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 3 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use Modern::Perl; + +use Carp; + +use Koha::Database; +use Koha::Exceptions; + +use base qw(Koha::Object); + +=head1 NAME + +Koha::Account::DebitType - Koha Account debit type Object class + +=head1 API + +=head2 Class Methods + +=cut + +=head3 delete + +=cut + +sub delete { + my ($self) = @_; + + if(not $self->can_be_deleted) { + Koha::Exceptions::CannotDeleteDefault->throw; + } + return $self->SUPER::delete($self); +} + +=head3 type + +=cut + +sub _type { + return 'AccountDebitType'; +} + +1; diff --git a/Koha/Account/DebitTypes.pm b/Koha/Account/DebitTypes.pm new file mode 100644 index 0000000000..cdf30800fe --- /dev/null +++ b/Koha/Account/DebitTypes.pm @@ -0,0 +1,52 @@ +package Koha::Account::DebitTypes; + +# Copyright ByWater Solutions 2014 +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 3 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use Modern::Perl; + +use Carp; + +use Koha::Database; + +use Koha::Account::DebitType; + +use base qw(Koha::Objects); + +=head1 NAME + +Koha::Account::DebitTypes - Koha Account debit types Object set class + +=head1 API + +=head2 Class Methods + +=cut + +=head3 type + +=cut + +sub _type { + return 'AccountDebitType'; +} + +sub object_class { + return 'Koha::Account::DebitType'; +} + +1; diff --git a/Koha/Schema/Result/AccountCreditType.pm b/Koha/Schema/Result/AccountCreditType.pm new file mode 100644 index 0000000000..3efc50301e --- /dev/null +++ b/Koha/Schema/Result/AccountCreditType.pm @@ -0,0 +1,81 @@ +use utf8; +package Koha::Schema::Result::AccountCreditType; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Koha::Schema::Result::AccountCreditType + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("account_credit_types"); + +=head1 ACCESSORS + +=head2 type_code + + data_type: 'varchar' + is_nullable: 0 + size: 5 + +=head2 description + + data_type: 'varchar' + is_nullable: 1 + size: 200 + +=head2 can_be_deleted + + data_type: 'tinyint' + default_value: 1 + is_nullable: 0 + +=head2 can_be_added_manually + + data_type: 'tinyint' + default_value: 1 + is_nullable: 0 + +=cut + +__PACKAGE__->add_columns( + "type_code", + { data_type => "varchar", is_nullable => 0, size => 5 }, + "description", + { data_type => "varchar", is_nullable => 1, size => 200 }, + "can_be_deleted", + { data_type => "tinyint", default_value => 1, is_nullable => 0 }, + "can_be_added_manually", + { data_type => "tinyint", default_value => 1, is_nullable => 0 }, +); + +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("type_code"); + + +# Created by DBIx::Class::Schema::Loader v0.07042 @ 2016-11-23 14:11:07 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:QwGpcrmSuarm9pnZ+s4VNg + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/Koha/Schema/Result/AccountDebitType.pm b/Koha/Schema/Result/AccountDebitType.pm new file mode 100644 index 0000000000..fa0862f6bd --- /dev/null +++ b/Koha/Schema/Result/AccountDebitType.pm @@ -0,0 +1,89 @@ +use utf8; +package Koha::Schema::Result::AccountDebitType; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Koha::Schema::Result::AccountDebitType + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("account_debit_types"); + +=head1 ACCESSORS + +=head2 type_code + + data_type: 'varchar' + is_nullable: 0 + size: 5 + +=head2 default_amount + + data_type: 'decimal' + is_nullable: 1 + size: [28,6] + +=head2 description + + data_type: 'varchar' + is_nullable: 1 + size: 200 + +=head2 can_be_deleted + + data_type: 'tinyint' + default_value: 1 + is_nullable: 0 + +=head2 can_be_added_manually + + data_type: 'tinyint' + default_value: 1 + is_nullable: 0 + +=cut + +__PACKAGE__->add_columns( + "type_code", + { data_type => "varchar", is_nullable => 0, size => 5 }, + "default_amount", + { data_type => "decimal", is_nullable => 1, size => [28, 6] }, + "description", + { data_type => "varchar", is_nullable => 1, size => 200 }, + "can_be_deleted", + { data_type => "tinyint", default_value => 1, is_nullable => 0 }, + "can_be_added_manually", + { data_type => "tinyint", default_value => 1, is_nullable => 0 }, +); + +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("type_code"); + + +# Created by DBIx::Class::Schema::Loader v0.07042 @ 2016-11-23 12:41:10 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:UtAqvoSzWkOVSJTqlbL8VQ + + +# You can replace this text with custom code or comments, and it will be preserved on regeneration +1; diff --git a/Koha/Schema/Result/Accountline.pm b/Koha/Schema/Result/Accountline.pm index b5e220a90e..3dc5ee36db 100644 --- a/Koha/Schema/Result/Accountline.pm +++ b/Koha/Schema/Result/Accountline.pm @@ -81,6 +81,12 @@ __PACKAGE__->table("accountlines"); is_nullable: 1 size: 80 +=head2 credit_type + + data_type: 'varchar' + is_nullable: 1 + size: 5 + =head2 amountoutstanding data_type: 'decimal' @@ -140,6 +146,8 @@ __PACKAGE__->add_columns( { data_type => "varchar", is_nullable => 1, size => 5 }, "payment_type", { data_type => "varchar", is_nullable => 1, size => 80 }, + "credit_type", + { data_type => "varchar", is_nullable => 1, size => 5 }, "amountoutstanding", { data_type => "decimal", is_nullable => 1, size => [28, 6] }, "lastincrement", @@ -264,9 +272,9 @@ __PACKAGE__->belongs_to( ); -# Created by DBIx::Class::Schema::Loader v0.07046 @ 2019-01-03 16:10:04 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:BO7iSB+QzoJNuZ8Uttba6A - +# Created by DBIx::Class::Schema::Loader v0.07046 @ 2019-02-28 10:13:25 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:eorONnG+aKxpCptCfcl8Aw + sub koha_objects_class { 'Koha::Account::Lines'; } diff --git a/installer/data/mysql/atomicupdate/bug_XXX.perl b/installer/data/mysql/atomicupdate/bug_XXX.perl new file mode 100644 index 0000000000..14682d4f07 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_XXX.perl @@ -0,0 +1,124 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if ( CheckVersion($DBversion) ) { + + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_debit_types ( + type_code varchar(5) NOT NULL, + description varchar(200) NULL, + can_be_deleted tinyint NOT NULL DEFAULT '1', + can_be_added_manually tinyint(4) NOT NULL DEFAULT '1', + default_amount decimal(28, 6) NULL, + PRIMARY KEY (type_code) + ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci + } + ); + + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_credit_types ( + type_code varchar(5) NOT NULL, + description varchar(200) NULL, + can_be_deleted tinyint NOT NULL DEFAULT '1', + can_be_added_manually tinyint(4) NOT NULL DEFAULT '1', + PRIMARY KEY (type_code) + ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci + } + ); + + $dbh->do( + qq{ + INSERT IGNORE INTO account_debit_types ( + type_code, + default_amount, + description, + can_be_deleted, + can_be_added_manually + ) + VALUES + ('A', NULL, 'Account management fee', 0, 1), + ('F', NULL, 'Overdue fine', 0, 1), + ('FU', NULL, 'Accruing overdue fine', 0, 0), + ('L', NULL, 'Lost item', 0, 1), + ('LR', NULL, 'Lost and returned', 0, 0), + ('M', NULL, 'Sundry', 0, 1), + ('N', NULL, 'New card', 0, 1), + ('O', NULL, 'Overdue fine', 0, 0), + ('Rent', NULL, 'Rental fee', 0, 1), + ('Rep', NULL, 'Replacement', 0, 0), + ('Res', NULL, 'Reserve charge', 0, 1) + } + ); + + $dbh->do( + qq{ + INSERT IGNORE INTO account_debit_types ( + type_code, + default_amount, + description, + can_be_deleted, + can_be_added_manually + ) + SELECT + SUBSTR(authorised_value, 1, 5), + lib, + authorised_value, + 1, + 1 + FROM + authorised_values + WHERE + category = 'MANUAL_INV' + } + ); + + $dbh->do( + qq{ + INSERT IGNORE INTO account_credit_types ( + type_code, + description, + can_be_deleted, + can_be_added_manually + ) + VALUES + ('C', 'Credit', 0, 1), + ('CC', 'Credit card', 0, 0), + ('CR', 'Refunded found lost item', 0, 0), + ('FFOR', 'Forgiven overdue fine', 0, 0), + ('FOR', 'Forgiven', 0, 1), + ('OL', 'Other online payment service', 0, 0), + ('Pay', 'Cash', 0, 0), + ('Pay00', 'Cash via SIP2', 0, 0), + ('Pay01', 'VISA via SIP2', 0, 0), + ('Pay02', 'Credit card via SIP2', 0, 0), + ('PayPa', 'PayPal', 0, 0), + ('W', 'Write Off', 0, 0) + } + ); + + $dbh->do( + qq{ + ALTER IGNORE TABLE accountlines + ADD + credit_type varchar(5) COLLATE utf8mb4_unicode_ci NULL + AFTER + accounttype + } + ); + + # 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 XXXXX - description)\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..c96927f100 --- /dev/null +++ b/installer/data/mysql/en/mandatory/account_credit_types.sql @@ -0,0 +1,13 @@ +INSERT INTO account_credit_types (type_code, description, can_be_deleted, can_be_added_manually) VALUES + ('C', 'Credit', 0, 1), + ('CC', 'Credit card', 0, 0), + ('CR', 'Refunded found lost item', 0, 0), + ('FFOR', 'Forgiven overdue fine', 0, 0), + ('FOR', 'Forgiven', 0, 1), + ('OL', 'Other online payment service', 0, 0), + ('Pay', 'Cash', 0, 0), + ('Pay00', 'Cash via SIP2', 0, 0), + ('Pay01', 'VISA via SIP2', 0, 0), + ('Pay02', 'Credit card via SIP2', 0, 0), + ('PayPa', 'PayPal', 0, 0), + ('W', 'Write Off', 0, 0); diff --git a/installer/data/mysql/en/mandatory/account_debit_types.sql b/installer/data/mysql/en/mandatory/account_debit_types.sql new file mode 100644 index 0000000000..7534bb13ae --- /dev/null +++ b/installer/data/mysql/en/mandatory/account_debit_types.sql @@ -0,0 +1,13 @@ +INSERT INTO account_debit_types (type_code, default_amount, description, can_be_deleted, can_be_added_manually) VALUES + ('A', NULL, 'Account management fee', 0, 1), + ('F', NULL, 'Overdue fine', 0, 1), + ('FU', NULL, 'Accruing overdue fine', 0, 0), + ('L', NULL, 'Lost item', 0, 1), + ('LR', NULL, 'Lost and returned', 0, 0), + ('M', NULL, 'Sundry', 0, 1), + ('N', NULL, 'New card', 0, 1), + ('O', NULL, 'Overdue fine', 0, 0), + ('Rent', NULL, 'Rental fee', 0, 1), + ('Rep', NULL, 'Replacement', 0, 0), + ('Res', NULL, 'Reserve charge', 0, 1), + ('Copie', 0.5, 'Copier fees', 1, 1); diff --git a/installer/data/mysql/en/optional/auth_val.sql b/installer/data/mysql/en/optional/auth_val.sql index 24f036bd20..b4e00fd8b2 100644 --- a/installer/data/mysql/en/optional/auth_val.sql +++ b/installer/data/mysql/en/optional/auth_val.sql @@ -48,9 +48,6 @@ INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('NOT_L -- restricted status of an item, linked to items.restricted INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('RESTRICTED','1','Restricted Access'); --- manual invoice types -INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('MANUAL_INV','Copier Fees','.25'); - -- custom borrower notes INSERT INTO `authorised_values` (category, authorised_value, lib) VALUES ('BOR_NOTES','ADDR','Address Notes'); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 76798c3163..36073d715e 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2699,6 +2699,31 @@ CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou CONSTRAINT `messages_borrowernumber` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE 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` ( + `type_code` varchar(5) COLLATE utf8_unicode_ci NOT NULL, + `description` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, + `can_be_deleted` tinyint(4) NOT NULL DEFAULT '1', + `can_be_added_manually` tinyint(4) NOT NULL DEFAULT '1', + PRIMARY KEY (`type_code`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + +-- +-- Table structure for table `account_debit_types` +-- +DROP TABLE IF EXISTS `account_debit_types`; +CREATE TABLE `account_debit_types` ( + `type_code` varchar(5) COLLATE utf8_unicode_ci NOT NULL, + `default_amount` decimal(28,6) DEFAULT NULL, + `description` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, + `can_be_deleted` tinyint(4) NOT NULL DEFAULT '1', + `can_be_added_manually` tinyint(4) NOT NULL DEFAULT '1', + PRIMARY KEY (`type_code`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + -- -- Table structure for table `accountlines` -- @@ -2715,6 +2740,7 @@ CREATE TABLE `accountlines` ( `description` LONGTEXT, `accounttype` varchar(5) default NULL, `payment_type` varchar(80) default NULL, -- optional authorised value PAYMENT_TYPE + `credit_type` varchar(5) default NULL, `amountoutstanding` decimal(28,6) default NULL, `lastincrement` decimal(28,6) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- 2.20.1