Artikelpreishistorie automatisch über Trigger speichern

diff --git a/SL/DB/Helper/ALL.pm b/SL/DB/Helper/ALL.pm
index bce48ca..23cf4ac 100644 (file)
--- 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 5eb9a5a..2a074dd 100644 (file)
--- 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 (file)
index 0000000..34cd4dc
--- /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 (file)
index 0000000..7d21cd3
--- /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 (file)
index 0000000..424b487
--- /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 (file)
index 0000000..78fd9b5
--- /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();