Solved Bug: 385 Skontokonzept (WIKI) und USTVA vertragen sich wieder.

[kivitendo-erp.git] / sql / lx-office.sql

diff --git a/sql/lx-office.sql b/sql/lx-office.sql
index 3be0a85..3e0befb 100644 (file)
--- a/sql/lx-office.sql
+++ b/sql/lx-office.sql
@@ -109,12 +109,23 @@ CREATE TABLE "defaults" (
        "rfqnumber" text,
         "customernumber" text,
         "vendornumber" text,
+        "audittrail" bool default 'f',
         "articlenumber" text,
         "servicenumber" text,
         "itime" timestamp DEFAULT now(),
         "mtime" timestamp
 );
-INSERT INTO defaults (version) VALUES ('2.1.2');
+INSERT INTO defaults (version,audittrail) VALUES ('2.2.0', 't');
+
+CREATE TABLE audittrail (
+        "trans_id" int,
+        "tablename" text,
+        "reference" text,
+        "formname" text,
+        "action" text,
+        "transdate" timestamp default current_timestamp,
+        "employee_id" int
+);
 
 CREATE TABLE "acc_trans" (
        "trans_id" integer,
@@ -149,7 +160,11 @@ CREATE TABLE "invoice" (
        "deliverydate" date,
        "serialnumber" text,
         "itime" timestamp DEFAULT now(),
-        "mtime" timestamp
+        "mtime" timestamp,
+        "pricegroup_id" integer,
+        "ordnumber" text,
+        "transdate" text,
+        "cusordnumber" text
 );
 
 CREATE TABLE "vendor" (
@@ -228,9 +243,10 @@ CREATE TABLE "customer" (
         "obsolete" boolean DEFAULT 'f',
         "ustid" varchar(12),
         "username" varchar(50),
-        "user_password" varchar(12),
+        "user_password" text,
         "salesman_id" integer,
-        "c_vendor_id" text
+        "c_vendor_id" text,
+        "klass" integer DEFAULT 0
         
 );
 
@@ -269,6 +285,7 @@ CREATE TABLE "ar" (
        "paid" numeric (15,5),
        "datepaid" date,
        "duedate" date,
+       "deliverydate" date,
        "invoice" boolean DEFAULT 'f',
        "shippingpoint" text,
        "terms" smallint DEFAULT 0,
@@ -370,7 +387,7 @@ CREATE TABLE "oe" (
 );
 
 CREATE TABLE "orderitems" (
-       "trans_id" integer REFERENCES oe (id),
+       "trans_id" integer,
        "parts_id" integer REFERENCES parts (id),
        "description" text,
        "qty" real,
@@ -381,9 +398,13 @@ CREATE TABLE "orderitems" (
        "reqdate" date,
        "ship" real,
        "serialnumber" text,
-       "id" integer DEFAULT nextval('orderitemsid'::text) PRIMARY KEY,
+       "id" integer DEFAULT nextval('orderitemsid'::text),
         "itime" timestamp DEFAULT now(),
-        "mtime" timestamp
+        "mtime" timestamp,
+        "pricegroup_id" integer,
+        "ordnumber" text,
+        "transdate" text,
+        "cusordnumber" text
 );
 
 CREATE TABLE "exchangerate" (
@@ -433,7 +454,7 @@ CREATE TABLE "shipto" (
 
 CREATE TABLE "project" (
        "id" integer DEFAULT nextval('id'::text) PRIMARY KEY,
-       "projectnumber" text,
+       "projectnumber" text UNIQUE,
        "description" text,
         "itime" timestamp DEFAULT now(),
         "mtime" timestamp
@@ -467,7 +488,7 @@ CREATE TABLE "status" (
 
 CREATE SEQUENCE "invoiceid" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;
 
-CREATE SEQUENCE "orderitemsid" start 1 increment 1 maxvalue 100000 minvalue 1 cache 1 cycle;
+CREATE SEQUENCE "orderitemsid" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1 cycle;
 
 CREATE TABLE "warehouse" (
        "id" integer DEFAULT nextval('id'::text) PRIMARY KEY,
@@ -540,6 +561,22 @@ CREATE TABLE "licenseinvoice" (
   license_id integer
 );
 
+-- Preisgruppenverwaltung
+CREATE TABLE "pricegroup" (
+  "id" integer DEFAULT nextval('id'::text),
+  "pricegroup" text not null,
+  PRIMARY KEY (id)
+);
+--Preisverwaltung der Preisgruppen
+CREATE TABLE "prices" (
+  "parts_id" integer REFERENCES parts(id),
+  "pricegroup_id" integer REFERENCES pricegroup(id),
+  "price" numeric(15,5)
+);
+
+
+
+
 -- Table of tax authoritys
 -- for module ustva
 -- Lx office