From 808ae7b37d80c461cd6b73068835394bf7ae2349 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 | 12 ++- installer/data/mysql/atomicupdate/bug_XXX.perl | 68 +++++++++++++++++ .../mysql/en/mandatory/account_credit_types.sql | 13 ++++ .../mysql/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, 524 insertions(+), 5 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 0000000..d08bacb --- /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 0000000..8f5ba45 --- /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 0000000..0ab6ebf --- /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 0000000..cdf3080 --- /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 0000000..3efc503 --- /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 0000000..fa0862f --- /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 d3fa5b3..a42d777 100644 --- a/Koha/Schema/Result/Accountline.pm +++ b/Koha/Schema/Result/Accountline.pm @@ -81,6 +81,12 @@ __PACKAGE__->table("accountlines"); is_nullable: 1 size: 5 +=head2 paymenttype + + data_type: 'varchar' + is_nullable: 1 + size: 5 + =head2 amountoutstanding data_type: 'decimal' @@ -150,6 +156,8 @@ __PACKAGE__->add_columns( { data_type => "mediumtext", is_nullable => 1 }, "accounttype", { data_type => "varchar", is_nullable => 1, size => 5 }, + "paymenttype", + { data_type => "varchar", is_nullable => 1, size => 5 }, "amountoutstanding", { data_type => "decimal", is_nullable => 1, size => [28, 6] }, "lastincrement", @@ -221,8 +229,8 @@ __PACKAGE__->belongs_to( ); -# Created by DBIx::Class::Schema::Loader v0.07042 @ 2016-01-26 17:18:34 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:RCQohhphtg+0+RszpB4wLg +# Created by DBIx::Class::Schema::Loader v0.07042 @ 2016-11-23 12:41:10 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:JiVg7P7Y+CRHQ1jxn2OSxg # You can replace this text with custom content, and it will be preserved on regeneration diff --git a/installer/data/mysql/atomicupdate/bug_XXX.perl b/installer/data/mysql/atomicupdate/bug_XXX.perl new file mode 100644 index 0000000..df4c8ae --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_XXX.perl @@ -0,0 +1,68 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + +$dbh->do( " + CREATE TABLE IF NOT EXISTS account_debit_types ( + type_code varchar(5) NOT NULL, + default_amount decimal(28,6) 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) + ) COMMENT='' ENGINE='InnoDB' COLLATE 'utf8_unicode_ci' +" ); + +$dbh->do( " + 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) + ) COMMENT='' ENGINE='InnoDB' COLLATE 'utf8_unicode_ci' +" ); + +$dbh->do( " + 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( " + 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( " + 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( " + ALTER IGNORE TABLE `accountlines` ADD `paymenttype` varchar(5) COLLATE 'utf8_unicode_ci' NULL AFTER `accounttype` +" ); + + 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 0000000..c96927f --- /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 0000000..7534bb1 --- /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 24f036b..b4e00fd 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 514c13d..02709ea 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2678,6 +2678,31 @@ CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_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` -- @@ -2693,6 +2718,7 @@ CREATE TABLE `accountlines` ( `description` mediumtext, `dispute` mediumtext, `accounttype` varchar(5) default NULL, + `paymenttype` 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.1.4