X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/2c86cb0ad16866113be6e6d8dff239623b77a576..03ff37cb621e68f7d224d2520099ee86f612f833:/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql

diff --git a/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql b/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql
new file mode 100644
index 000000000..a071442ba
--- /dev/null
+++ b/sql/Pg-upgrade2/add_tax_id_to_acc_trans.sql
@@ -0,0 +1,25 @@
+-- @tag: add_tax_id_to_acc_trans
+-- @description: Neue Spalte tax_id in der acc_trans
+-- @depends: release_2_7_0 
+
+  --Neue Spalte tax_id in acc_trans:
+  ALTER TABLE acc_trans ADD tax_id integer;
+
+  --Spalte mit Werten füllen:
+  UPDATE acc_trans ac SET tax_id=
+                (select tk.tax_id from taxkeys tk 
+                      where tk.taxkey_id=ac.taxkey 
+                      AND tk.startdate <= COALESCE(
+                            (select ar.deliverydate from ar where ar.id=ac.trans_id), 
+                            (select ar.transdate from ar where ar.id=ac.trans_id), 
+                            (select ap.transdate from ap where ap.id=ac.trans_id), 
+                            (select gl.transdate from gl where gl.id=ac.trans_id), 
+                            ac.transdate )
+                      order by startdate desc limit 1);
+
+  --Spalten, die noch null sind (nur bei Einträgen möglich, wo auch taxkey null ist)
+  UPDATE acc_trans SET tax_id= (SELECT id FROM tax WHERE taxkey=0 LIMIT 1) where tax_id is null;
+ 
+  --tax_id als Pflichtfeld definieren:
+  ALTER TABLE acc_trans ALTER tax_id SET NOT NULL;
+