Merge von 845 aus unstable: Bugfix Nachtrag zu r839

[mfinanz.git] / sql / Pg-upgrade-2.1.1-2.1.2.sql

diff --git a/sql/Pg-upgrade-2.1.1-2.1.2.sql b/sql/Pg-upgrade-2.1.1-2.1.2.sql
index 7ff54927a0206f8c4a5ba38490171f26cc4004cb..068ee23198f08495882db8e1a3bb6e7cbf9f5c37 100644 (file)
--- a/sql/Pg-upgrade-2.1.1-2.1.2.sql
+++ b/sql/Pg-upgrade-2.1.1-2.1.2.sql
@@ -116,15 +116,17 @@ alter table vendor drop column creditlimitold;
 --New Fields for customer and vendor
 alter table vendor add column obsolete boolean;
 alter table vendor alter column obsolete set default 'false';
+update table vendor set obsolete='false';
 alter table customer add column obsolete boolean;
 alter table customer alter column obsolete set default 'false';
+update table customer set obsolete='false';
 alter table customer add column ustid varchar(12);
 alter table vendor add column ustid varchar(12);
 
 alter table customer add column username varchar(50);
 alter table vendor add column username varchar(50);
-alter table customer add column user_password varchar(12);
-alter table vendor add column user_password varchar(12);
+alter table customer add column user_password text;
+alter table vendor add column user_password text;
 alter table customer add column salesman_id integer;
 alter table vendor add column salesman_id integer;
 
@@ -132,6 +134,8 @@ alter table vendor add column salesman_id integer;
 alter table shipto add column shiptodepartment_1 varchar(75);
 alter table shipto add column shiptodepartment_2 varchar(75);
 
+
+
 -- Addon for business
 alter table business add column salesman boolean;
 alter table business alter column salesman set default 'false';
@@ -150,6 +154,22 @@ alter table ar alter invnumber set NOT NULL;
 alter table ap alter invnumber set NOT NULL;
 alter table oe alter ordnumber set NOT NULL;
 
+alter table gl alter id set NOT NULL;
+alter table chart alter id set NOT NULL;
+alter table parts alter id set NOT NULL;
+alter table invoice alter id set NOT NULL;
+alter table vendor alter id set NOT NULL;
+alter table customer alter id set NOT NULL;
+alter table contacts alter cp_id set NOT NULL;
+alter table ar alter id set NOT NULL;
+alter table ap alter id set NOT NULL;
+alter table oe alter id set NOT NULL;
+alter table employee alter id set NOT NULL;
+alter table warehouse alter id set NOT NULL;
+alter table business alter id set NOT NULL;
+alter table license alter id set NOT NULL;
+alter table orderitems alter id set NOT NULL;
+
 alter table gl add primary key (id);
 alter table chart add primary key (id);
 alter table parts add primary key (id);
@@ -164,7 +184,6 @@ alter table employee add primary key (id);
 alter table warehouse add primary key (id);
 alter table business add primary key (id);
 alter table license add primary key (id);
-alter table orderitems add primary key (id);
 
 alter table acc_trans add foreign key (chart_id) references chart (id);
 alter table invoice add foreign key (parts_id) references parts (id);
@@ -183,6 +202,62 @@ update vendor set temp_account_number=account_number;
 alter table vendor drop column account_number;
 alter table vendor rename temp_account_number to  account_number;
 
-update defaults set version = '2.1.2';
+-- audit
+alter table defaults add column audittrail bool;
+CREATE TABLE audittrail (
+  trans_id int,
+  tablename text,
+  reference text,
+  formname text,
+  action text,
+  transdate timestamp default current_timestamp,
+  employee_id int
+);
+
+-- pricegroups
+
+CREATE TABLE "pricegroup" (
+  "id" integer DEFAULT nextval('id'::text),
+  "pricegroup" text not null,
+  PRIMARY KEY (id)
+);
+
+CREATE TABLE "prices" (
+  "parts_id" integer REFERENCES parts(id),
+  "pricegroup_id" integer,
+  "price" numeric(15,5)
+);
+
+ALTER TABLE customer ADD column klass integer;
+ALTER TABLE customer ALTER column klass set default 0;
+
+-- 
+ALTER TABLE invoice ADD column pricegroup_id integer;
+ALTER TABLE orderitems ADD column pricegroup_id integer;
+
+
+-- USTVA Update solve Bug 49 conributed by Andre Schubert
+update chart set pos_ustva='861' where accno='1771';
+update chart set pos_ustva='511' where accno='1775';
+-- update chart set pos_ustva='511' where pos_ustva='51r';
+-- update chart set pos_ustva='861' where pos_ustva='86r';
+-- update chart set pos_ustva='971' where pos_ustva='97r';
+-- update chart set pos_ustva='931' where pos_ustva='93r';
+
+-- add fields for ordnumber/transdate/cusordnumber in invoice/orderitems (r690 cleanup)
+alter table orderitems add column ordnumber text;
+alter table orderitems add column transdate text;
+alter table orderitems add column cusordnumber text;
+alter table invoice add column ordnumber text;
+alter table invoice add column transdate text;
+alter table invoice add column cusordnumber text;
 --
 
+--
+-- add unqiue constraint to project
+ALTER TABLE project ADD constraint project_projectnumber_key UNIQUE(projectnumber);
+--
+-- add column deliverydate to ar
+ALTER TABLE ar ADD COLUMN deliverydate date;
+
+update defaults set version = '2.1.2';