From c8a3fef75e56acb4a3a513c4c0ea783a6f49e516 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Fri, 20 Jan 2017 11:51:53 +0100 Subject: [PATCH] Belegvorlagen: Datenbankschema, Rose-Models --- SL/DB/Helper/ALL.pm | 4 +- SL/DB/Helper/Mappings.pm | 4 +- SL/DB/Manager/RecordTemplate.pm | 24 ++++++ SL/DB/Manager/RecordTemplateItem.pm | 11 +++ SL/DB/MetaSetup/RecordTemplate.pm | 76 +++++++++++++++++++ SL/DB/MetaSetup/RecordTemplateItem.pm | 48 ++++++++++++ SL/DB/RecordTemplate.pm | 20 +++++ SL/DB/RecordTemplateItem.pm | 10 +++ .../create_record_template_tables.sql | 64 ++++++++++++++++ 9 files changed, 259 insertions(+), 2 deletions(-) create mode 100644 SL/DB/Manager/RecordTemplate.pm create mode 100644 SL/DB/Manager/RecordTemplateItem.pm create mode 100644 SL/DB/MetaSetup/RecordTemplate.pm create mode 100644 SL/DB/MetaSetup/RecordTemplateItem.pm create mode 100644 SL/DB/RecordTemplate.pm create mode 100644 SL/DB/RecordTemplateItem.pm create mode 100644 sql/Pg-upgrade2/create_record_template_tables.sql diff --git a/SL/DB/Helper/ALL.pm b/SL/DB/Helper/ALL.pm index a896cc372..2f747b9db 100644 --- a/SL/DB/Helper/ALL.pm +++ b/SL/DB/Helper/ALL.pm @@ -91,8 +91,10 @@ use SL::DB::ProjectRole; use SL::DB::ProjectStatus; use SL::DB::ProjectType; use SL::DB::PurchaseInvoice; -use SL::DB::RecordLink; use SL::DB::ReconciliationLink; +use SL::DB::RecordLink; +use SL::DB::RecordTemplate; +use SL::DB::RecordTemplateItem; use SL::DB::RequirementSpecAcceptanceStatus; use SL::DB::RequirementSpecComplexity; use SL::DB::RequirementSpecDependency; diff --git a/SL/DB/Helper/Mappings.pm b/SL/DB/Helper/Mappings.pm index 3a6f96575..0a20e1260 100644 --- a/SL/DB/Helper/Mappings.pm +++ b/SL/DB/Helper/Mappings.pm @@ -171,8 +171,10 @@ my %kivitendo_package_names = ( project_roles => 'project_role', project_statuses => 'project_status', project_types => 'project_type', - record_links => 'record_link', reconciliation_links => 'reconciliation_link', + record_links => 'record_link', + record_templates => 'record_template', + record_template_items => 'record_template_item', requirement_spec_acceptance_statuses => 'RequirementSpecAcceptanceStatus', requirement_spec_complexities => 'RequirementSpecComplexity', requirement_spec_item_dependencies => 'RequirementSpecDependency', diff --git a/SL/DB/Manager/RecordTemplate.pm b/SL/DB/Manager/RecordTemplate.pm new file mode 100644 index 000000000..48c782746 --- /dev/null +++ b/SL/DB/Manager/RecordTemplate.pm @@ -0,0 +1,24 @@ +package SL::DB::Manager::RecordTemplate; + +use strict; + +use base qw(SL::DB::Helper::Manager); + +use SL::DB::Helper::Paginated; +use SL::DB::Helper::Sorted; + +sub object_class { 'SL::DB::RecordTemplate' } + +__PACKAGE__->make_manager_methods; + +sub _sort_spec { + return ( + default => [ 'template_name', 1 ], + columns => { + SIMPLE => 'ALL', + template_name => 'lower(template_name)', + }, + ); +} + +1; diff --git a/SL/DB/Manager/RecordTemplateItem.pm b/SL/DB/Manager/RecordTemplateItem.pm new file mode 100644 index 000000000..1c3ba92f0 --- /dev/null +++ b/SL/DB/Manager/RecordTemplateItem.pm @@ -0,0 +1,11 @@ +package SL::DB::Manager::RecordTemplateItem; + +use strict; + +use parent qw(SL::DB::Helper::Manager); + +sub object_class { 'SL::DB::RecordTemplateItem' } + +__PACKAGE__->make_manager_methods; + +1; diff --git a/SL/DB/MetaSetup/RecordTemplate.pm b/SL/DB/MetaSetup/RecordTemplate.pm new file mode 100644 index 000000000..8fe4cf9dc --- /dev/null +++ b/SL/DB/MetaSetup/RecordTemplate.pm @@ -0,0 +1,76 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RecordTemplate; + +use strict; + +use parent qw(SL::DB::Object); + +__PACKAGE__->meta->table('record_templates'); + +__PACKAGE__->meta->columns( + ar_ap_chart_id => { type => 'integer' }, + cb_transaction => { type => 'boolean', default => 'false', not_null => 1 }, + currency_id => { type => 'integer', not_null => 1 }, + customer_id => { type => 'integer' }, + department_id => { type => 'integer' }, + description => { type => 'text' }, + direct_debit => { type => 'boolean', default => 'false', not_null => 1 }, + employee_id => { type => 'integer' }, + id => { type => 'serial', not_null => 1 }, + itime => { type => 'timestamp', default => 'now()', not_null => 1 }, + mtime => { type => 'timestamp', default => 'now()', not_null => 1 }, + notes => { type => 'text' }, + ob_transaction => { type => 'boolean', default => 'false', not_null => 1 }, + ordnumber => { type => 'text' }, + project_id => { type => 'integer' }, + reference => { type => 'text' }, + taxincluded => { type => 'boolean', default => 'false', not_null => 1 }, + template_name => { type => 'text', not_null => 1 }, + template_type => { type => 'enum', check_in => [ 'ar_transaction', 'ap_transaction', 'gl_transaction' ], db_type => 'record_template_type', not_null => 1 }, + vendor_id => { type => 'integer' }, +); + +__PACKAGE__->meta->primary_key_columns([ 'id' ]); + +__PACKAGE__->meta->allow_inline_column_values(1); + +__PACKAGE__->meta->foreign_keys( + ar_ap_chart => { + class => 'SL::DB::Chart', + key_columns => { ar_ap_chart_id => 'id' }, + }, + + currency => { + class => 'SL::DB::Currency', + key_columns => { currency_id => 'id' }, + }, + + customer => { + class => 'SL::DB::Customer', + key_columns => { customer_id => 'id' }, + }, + + department => { + class => 'SL::DB::Department', + key_columns => { department_id => 'id' }, + }, + + employee => { + class => 'SL::DB::Employee', + key_columns => { employee_id => 'id' }, + }, + + project => { + class => 'SL::DB::Project', + key_columns => { project_id => 'id' }, + }, + + vendor => { + class => 'SL::DB::Vendor', + key_columns => { vendor_id => 'id' }, + }, +); + +1; +; diff --git a/SL/DB/MetaSetup/RecordTemplateItem.pm b/SL/DB/MetaSetup/RecordTemplateItem.pm new file mode 100644 index 000000000..538ac0184 --- /dev/null +++ b/SL/DB/MetaSetup/RecordTemplateItem.pm @@ -0,0 +1,48 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RecordTemplateItem; + +use strict; + +use parent qw(SL::DB::Object); + +__PACKAGE__->meta->table('record_template_items'); + +__PACKAGE__->meta->columns( + amount1 => { type => 'numeric', not_null => 1, precision => 15, scale => 5 }, + amount2 => { type => 'numeric', precision => 15, scale => 5 }, + chart_id => { type => 'integer', not_null => 1 }, + id => { type => 'serial', not_null => 1 }, + memo => { type => 'text' }, + project_id => { type => 'integer' }, + record_template_id => { type => 'integer', not_null => 1 }, + source => { type => 'text' }, + tax_id => { type => 'integer', not_null => 1 }, +); + +__PACKAGE__->meta->primary_key_columns([ 'id' ]); + +__PACKAGE__->meta->foreign_keys( + chart => { + class => 'SL::DB::Chart', + key_columns => { chart_id => 'id' }, + }, + + project => { + class => 'SL::DB::Project', + key_columns => { project_id => 'id' }, + }, + + record_template => { + class => 'SL::DB::RecordTemplate', + key_columns => { record_template_id => 'id' }, + }, + + tax => { + class => 'SL::DB::Tax', + key_columns => { tax_id => 'id' }, + }, +); + +1; +; diff --git a/SL/DB/RecordTemplate.pm b/SL/DB/RecordTemplate.pm new file mode 100644 index 000000000..eb6289cec --- /dev/null +++ b/SL/DB/RecordTemplate.pm @@ -0,0 +1,20 @@ +package SL::DB::RecordTemplate; + +use strict; + +use SL::DB::MetaSetup::RecordTemplate; +use SL::DB::Manager::RecordTemplate; + +__PACKAGE__->meta->add_relationship( + record_template_items => { + type => 'one to many', + class => 'SL::DB::RecordTemplateItem', + column_map => { id => 'record_template_id' }, + }, +); + +__PACKAGE__->meta->initialize; + +sub items { goto &record_template_items; } + +1; diff --git a/SL/DB/RecordTemplateItem.pm b/SL/DB/RecordTemplateItem.pm new file mode 100644 index 000000000..942936c59 --- /dev/null +++ b/SL/DB/RecordTemplateItem.pm @@ -0,0 +1,10 @@ +package SL::DB::RecordTemplateItem; + +use strict; + +use SL::DB::MetaSetup::RecordTemplateItem; +use SL::DB::Manager::RecordTemplateItem; + +__PACKAGE__->meta->initialize; + +1; diff --git a/sql/Pg-upgrade2/create_record_template_tables.sql b/sql/Pg-upgrade2/create_record_template_tables.sql new file mode 100644 index 000000000..01adfd396 --- /dev/null +++ b/sql/Pg-upgrade2/create_record_template_tables.sql @@ -0,0 +1,64 @@ +-- @tag: create_record_template_tables +-- @description: Einführung echter Vorlagen in der Finanzbuchhaltung anstelle der Entwurfsfunktion +-- @depends: release_3_4_1 + +DROP TABLE IF EXISTS record_template_items; +DROP TABLE IF EXISTS record_templates; +DROP TYPE IF EXISTS record_template_type; + +CREATE TYPE record_template_type AS ENUM ('ar_transaction', 'ap_transaction', 'gl_transaction'); +CREATE TABLE record_templates ( + id SERIAL, + template_name TEXT NOT NULL, + template_type record_template_type NOT NULL, + + customer_id INTEGER, + vendor_id INTEGER, + currency_id INTEGER NOT NULL, + department_id INTEGER, + project_id INTEGER, + employee_id INTEGER, + taxincluded BOOLEAN NOT NULL DEFAULT FALSE, + direct_debit BOOLEAN NOT NULL DEFAULT FALSE, + ob_transaction BOOLEAN NOT NULL DEFAULT FALSE, + cb_transaction BOOLEAN NOT NULL DEFAULT FALSE, + + reference TEXT, + description TEXT, + ordnumber TEXT, + notes TEXT, + ar_ap_chart_id INTEGER, + + itime TIMESTAMP NOT NULL DEFAULT now(), + mtime TIMESTAMP NOT NULL DEFAULT now(), + + PRIMARY KEY (id), + CONSTRAINT record_templates_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customer (id) ON DELETE SET NULL, + CONSTRAINT record_templates_vendor_id_fkey FOREIGN KEY (vendor_id) REFERENCES vendor (id) ON DELETE SET NULL, + CONSTRAINT record_templates_currency_id_fkey FOREIGN KEY (currency_id) REFERENCES currencies (id) ON DELETE CASCADE, + CONSTRAINT record_templates_department_id_fkey FOREIGN KEY (department_id) REFERENCES department (id) ON DELETE SET NULL, + CONSTRAINT record_templates_project_id_fkey FOREIGN KEY (project_id) REFERENCES project (id) ON DELETE SET NULL, + CONSTRAINT record_templates_employee_id_fkey FOREIGN KEY (employee_id) REFERENCES employee (id) ON DELETE SET NULL, + CONSTRAINT record_templates_ar_ap_chart_id_fkey FOREIGN KEY (ar_ap_chart_id) REFERENCES chart (id) ON DELETE SET NULL +); + +CREATE TRIGGER mtime_record_templates BEFORE UPDATE ON record_templates FOR EACH ROW EXECUTE PROCEDURE set_mtime(); + +CREATE TABLE record_template_items ( + id SERIAL, + record_template_id INTEGER NOT NULL, + + chart_id INTEGER NOT NULL, + tax_id INTEGER NOT NULL, + project_id INTEGER, + amount1 NUMERIC (15, 5) NOT NULL, + amount2 NUMERIC (15, 5), + source TEXT, + memo TEXT, + + PRIMARY KEY (id), + CONSTRAINT record_template_items_record_template_id FOREIGN KEY (record_template_id) REFERENCES record_templates (id) ON DELETE CASCADE, + CONSTRAINT record_template_items_chart_id FOREIGN KEY (chart_id) REFERENCES chart (id) ON DELETE CASCADE, + CONSTRAINT record_template_items_tax_id FOREIGN KEY (tax_id) REFERENCES tax (id) ON DELETE CASCADE, + CONSTRAINT record_template_items_project_id FOREIGN KEY (project_id) REFERENCES project (id) ON DELETE SET NULL +); -- 2.20.1