projekt kivitendo

use SL::DB::PartsPriceHistory;

  parts_price_history            => 'PartsPriceHistory',

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;

# 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;

;

# 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;

-- @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();