X-Git-Url: http://wagnertech.de/git?p=kivitendo-erp.git;a=blobdiff_plain;f=sql%2FPg-upgrade2%2Fcreate_record_template_tables.sql;fp=sql%2FPg-upgrade2%2Fcreate_record_template_tables.sql;h=01adfd396051be3d947521a7b0c3d69dcaf0b2af;hp=0000000000000000000000000000000000000000;hb=53593baa211863fbf66540cf1bcc36c8fb37257f;hpb=deb4d2dbb676d7d6f69dfe7815d6e0cb09bd4a44

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
+);