From 58190585c35976c33c0594e920251ec8f7e3d40e Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Mon, 30 May 2016 11:51:22 +0200 Subject: [PATCH] =?utf8?q?Artikelpreishistorie=20automatisch=20=C3=BCber?= =?utf8?q?=20Trigger=20speichern?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/DB/Helper/ALL.pm | 1 + SL/DB/Helper/Mappings.pm | 1 + SL/DB/Manager/PartsPriceHistory.pm | 11 ++++++ SL/DB/MetaSetup/PartsPriceHistory.pm | 30 ++++++++++++++++ SL/DB/PartsPriceHistory.pm | 13 +++++++ sql/Pg-upgrade2/add_parts_price_history.sql | 39 +++++++++++++++++++++ 6 files changed, 95 insertions(+) create mode 100644 SL/DB/Manager/PartsPriceHistory.pm create mode 100644 SL/DB/MetaSetup/PartsPriceHistory.pm create mode 100644 SL/DB/PartsPriceHistory.pm create mode 100644 sql/Pg-upgrade2/add_parts_price_history.sql diff --git a/SL/DB/Helper/ALL.pm b/SL/DB/Helper/ALL.pm index bce48cae5..23cf4ac5a 100644 --- a/SL/DB/Helper/ALL.pm +++ b/SL/DB/Helper/ALL.pm @@ -71,6 +71,7 @@ use SL::DB::Order; use SL::DB::OrderItem; use SL::DB::Part; use SL::DB::PartsGroup; +use SL::DB::PartsPriceHistory; use SL::DB::PaymentTerm; use SL::DB::PeriodicInvoice; use SL::DB::PeriodicInvoicesConfig; diff --git a/SL/DB/Helper/Mappings.pm b/SL/DB/Helper/Mappings.pm index 5eb9a5a3f..2a074dd4d 100644 --- a/SL/DB/Helper/Mappings.pm +++ b/SL/DB/Helper/Mappings.pm @@ -152,6 +152,7 @@ my %kivitendo_package_names = ( oe => 'order', parts => 'part', partsgroup => 'parts_group', + parts_price_history => 'PartsPriceHistory', payment_terms => 'payment_term', periodic_invoices => 'periodic_invoice', periodic_invoices_configs => 'periodic_invoices_config', diff --git a/SL/DB/Manager/PartsPriceHistory.pm b/SL/DB/Manager/PartsPriceHistory.pm new file mode 100644 index 000000000..34cd4dc1f --- /dev/null +++ b/SL/DB/Manager/PartsPriceHistory.pm @@ -0,0 +1,11 @@ +package SL::DB::Manager::PartsPriceHistory; + +use strict; + +use parent qw(SL::DB::Helper::Manager); + +sub object_class { 'SL::DB::PartsPriceHistory' } + +__PACKAGE__->make_manager_methods; + +1; diff --git a/SL/DB/MetaSetup/PartsPriceHistory.pm b/SL/DB/MetaSetup/PartsPriceHistory.pm new file mode 100644 index 000000000..7d21cd367 --- /dev/null +++ b/SL/DB/MetaSetup/PartsPriceHistory.pm @@ -0,0 +1,30 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::PartsPriceHistory; + +use strict; + +use parent qw(SL::DB::Object); + +__PACKAGE__->meta->table('parts_price_history'); + +__PACKAGE__->meta->columns( + id => { type => 'serial', not_null => 1 }, + lastcost => { type => 'numeric', precision => 15, scale => 5 }, + listprice => { type => 'numeric', precision => 15, scale => 5 }, + part_id => { type => 'integer', not_null => 1 }, + sellprice => { type => 'numeric', precision => 15, scale => 5 }, + valid_from => { type => 'timestamp', not_null => 1 }, +); + +__PACKAGE__->meta->primary_key_columns([ 'id' ]); + +__PACKAGE__->meta->foreign_keys( + part => { + class => 'SL::DB::Part', + key_columns => { part_id => 'id' }, + }, +); + +1; +; diff --git a/SL/DB/PartsPriceHistory.pm b/SL/DB/PartsPriceHistory.pm new file mode 100644 index 000000000..424b48765 --- /dev/null +++ b/SL/DB/PartsPriceHistory.pm @@ -0,0 +1,13 @@ +# This file has been auto-generated only because it didn't exist. +# Feel free to modify it at will; it will not be overwritten automatically. + +package SL::DB::PartsPriceHistory; + +use strict; + +use SL::DB::MetaSetup::PartsPriceHistory; +use SL::DB::Manager::PartsPriceHistory; + +__PACKAGE__->meta->initialize; + +1; diff --git a/sql/Pg-upgrade2/add_parts_price_history.sql b/sql/Pg-upgrade2/add_parts_price_history.sql new file mode 100644 index 000000000..78fd9b55c --- /dev/null +++ b/sql/Pg-upgrade2/add_parts_price_history.sql @@ -0,0 +1,39 @@ +-- @tag: add_parts_price_history +-- @description: Tabelle für Entwicklung der Stammdatenpreise +-- @depends: release_3_4_0 +DROP TRIGGER IF EXISTS add_parts_price_history_entry_after_changes_on_parts ON parts; +DROP FUNCTION IF EXISTS add_parts_price_history_entry(); +DROP TABLE IF EXISTS parts_price_history; + +CREATE TABLE parts_price_history ( + id SERIAL, + part_id INTEGER NOT NULL, + valid_from TIMESTAMP NOT NULL, + lastcost NUMERIC(15, 5), + listprice NUMERIC(15, 5), + sellprice NUMERIC(15, 5), + + PRIMARY KEY (id), + FOREIGN KEY (part_id) REFERENCES parts (id) ON DELETE CASCADE +); + +INSERT INTO parts_price_history (part_id, valid_from, lastcost, listprice, sellprice) +SELECT id, COALESCE(COALESCE(mtime, itime), now()), lastcost, listprice, sellprice +FROM parts; + +CREATE FUNCTION add_parts_price_history_entry() RETURNS "trigger" AS $$ + BEGIN + IF (TG_OP = 'UPDATE') AND (OLD.lastcost = NEW.lastcost) AND (OLD.listprice = NEW.listprice) AND (OLD.sellprice = NEW.sellprice) THEN + RETURN NEW; + END IF; + + INSERT INTO parts_price_history (part_id, lastcost, listprice, sellprice, valid_from) + VALUES (NEW.id, NEW.lastcost, NEW.listprice, NEW.sellprice, now()); + + RETURN NEW; + END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER add_parts_price_history_entry_after_changes_on_parts +AFTER INSERT OR UPDATE on parts +FOR EACH ROW EXECUTE PROCEDURE add_parts_price_history_entry(); -- 2.20.1