projekt kivitendo

    curr                => { type => 'text' },

    curr                    => { type => 'text' },

    curr      => { type => 'text' },

    curr                      => { type => 'text' },

    curr                    => { type => 'text' },

    curr                 => { type => 'text' },

    curr           => { type => 'text' },

  unless ($transdate && $curr) {

  'Enter the abbreviations separated by a colon (i.e CAD:USD:EUR) for your native and foreign currencies' => 'Geben Sie Ihre und weitere Währungen als Abkürzungen durch Doppelpunkte getrennt ein (z.B. EUR:USD:CAD)',

-- @tag: convert_curr_to_text

-- @description: Spalte 'curr' von 'char(3)' nach 'text' konvertieren

-- @depends: release_2_7_0

-- @charset: utf-8

-- Zuerst alle Spaltentypen konvertieren.

ALTER TABLE ap              ALTER COLUMN curr TYPE text;

ALTER TABLE ar              ALTER COLUMN curr TYPE text;

ALTER TABLE customer        ALTER COLUMN curr TYPE text;

ALTER TABLE delivery_orders ALTER COLUMN curr TYPE text;

ALTER TABLE exchangerate    ALTER COLUMN curr TYPE text;

ALTER TABLE rma             ALTER COLUMN curr TYPE text;

ALTER TABLE vendor          ALTER COLUMN curr TYPE text;

-- Eventuell falsche Inhalte (Leerzeichenpadding) auf leere Strings setzen.

UPDATE ap              SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);

UPDATE ar              SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);

UPDATE customer        SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);

UPDATE delivery_orders SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);

UPDATE exchangerate    SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);

UPDATE oe              SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);

UPDATE rma             SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);

UPDATE vendor          SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);

-- Nun noch die stored procedures anpassen.

CREATE OR REPLACE FUNCTION del_exchangerate() RETURNS trigger

  LANGUAGE plpgsql

  AS $$

    DECLARE

      t_transdate date;

      t_curr      text;

      t_id        int;

      d_curr      text;

    BEGIN

      SELECT INTO d_curr substring(curr FROM '[^:]*') FROM DEFAULTS;

      IF TG_RELNAME = 'ar' THEN

        SELECT INTO t_curr, t_transdate curr, transdate FROM ar WHERE id = old.id;

      END IF;

      IF TG_RELNAME = 'ap' THEN

        SELECT INTO t_curr, t_transdate curr, transdate FROM ap WHERE id = old.id;

      END IF;

      IF TG_RELNAME = 'oe' THEN

        SELECT INTO t_curr, t_transdate curr, transdate FROM oe WHERE id = old.id;

      END IF;

      IF TG_RELNAME = 'delivery_orders' THEN

        SELECT INTO t_curr, t_transdate curr, transdate FROM delivery_orders WHERE id = old.id;

      END IF;

      IF d_curr != t_curr THEN

        SELECT INTO t_id a.id FROM acc_trans ac

          JOIN ar a ON (a.id = ac.trans_id)

          WHERE (a.curr       = t_curr)

            AND (ac.transdate = t_transdate)

        EXCEPT SELECT a.id

          FROM ar a

          WHERE (a.id = old.id)

        UNION

        SELECT a.id

          FROM acc_trans ac

          JOIN ap a ON (a.id = ac.trans_id)

          WHERE (a.curr       = t_curr)

            AND (ac.transdate = t_transdate)

        EXCEPT SELECT a.id

          FROM ap a

          WHERE (a.id = old.id)

        UNION

        SELECT o.id

          FROM oe o

          WHERE (o.curr      = t_curr)

            AND (o.transdate = t_transdate)

        EXCEPT SELECT o.id

        FROM oe o

        WHERE (o.id = old.id)

        UNION

        SELECT dord.id

          FROM delivery_orders dord

          WHERE (dord.curr      = t_curr)

            AND (dord.transdate = t_transdate)

        EXCEPT SELECT dord.id

        FROM delivery_orders dord

        WHERE (dord.id = old.id);

        IF NOT FOUND THEN

          DELETE FROM exchangerate

          WHERE (curr      = t_curr)

            AND (transdate = t_transdate);

        END IF;

      END IF;

      RETURN old;

    END;

$$;

-- Und die stored procedure auch auf delivery_orders anwenden

CREATE TRIGGER del_exchangerate

    BEFORE DELETE ON delivery_orders

    FOR EACH ROW

    EXECUTE PROCEDURE del_exchangerate();

   (1) [% 'Enter the abbreviations separated by a colon (i.e CAD:USD:EUR) for your native and foreign currencies' | $T8 %]