[Noalyss-commit] [noalyss] 101/218: Currency : SQL quant_fin is adapted

[Dany De Bontridder]

sparkyx pushed a commit to branch entreprise
in repository noalyss.

commit 5d16283d952ff00646cee3796ea331dad25b3bcb
Author: Dany De Bontridder <address@hidden>
Date:   Wed Nov 28 12:27:16 2018 +0100

    Currency : SQL quant_fin is adapted
---
 include/sql/patch/upgrade130.sql | 46 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 46 insertions(+)

diff --git a/include/sql/patch/upgrade130.sql b/include/sql/patch/upgrade130.sql
new file mode 100644
index 0000000..cfecdda
--- /dev/null
+++ b/include/sql/patch/upgrade130.sql
@@ -0,0 +1,46 @@
+begin;
+
+CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$
+DECLARE x NUMERIC;
+BEGIN
+    x = $1::NUMERIC;
+    RETURN TRUE;
+EXCEPTION WHEN others THEN
+    RETURN FALSE;
+END;
+$$
+STRICT
+LANGUAGE plpgsql IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION isdate(text,text) RETURNS BOOLEAN AS $$
+DECLARE x timestamp;
+BEGIN
+    x := to_date($1,$2);
+    RETURN TRUE;
+EXCEPTION WHEN others THEN
+    RETURN FALSE;
+END;
+$$
+LANGUAGE plpgsql;
+
+ALTER TABLE public.jrn_def ADD currency_id int NULL;
+ALTER TABLE public.jrn_def ALTER COLUMN currency_id SET DEFAULT 0;
+update  public.jrn_def  set currency_id = 0 ; 
+ALTER TABLE public.jrn_def ALTER COLUMN currency_id SET NOT NULL;
+ALTER TABLE public.jrn_def ADD CONSTRAINT jrn_def_currency_fk FOREIGN KEY 
(currency_id) REFERENCES public.currency(id);
+
+COMMENT ON COLUMN public.jrn_def.currency_id IS 'Default currency for 
financial ledger';
+
+
+alter table quant_fin add j_id bigint;
+
+with j_fin as (
+select jrnx.j_id,quant_fin.qf_id from quant_fin join jrn using (jr_id) join 
jrnx on (j_grpt=jr_grpt_id and f_id=qf_other)
+)
+update quant_fin set j_id =j_fin.j_id from j_fin where 
j_fin.qf_id=quant_fin.qf_id;
+
+alter table quant_fin add constraint jrnx_j_id_fk foreign key (j_id ) 
references jrnx(j_id) on delete cascade on update cascade;
+
+
+insert into version (val,v_description) values (131,'Currency : adapt 
quant_fin');
+commit;