1VH6pdyP

1--создаём копию таблицы
2
3CREATE TABLE "supp"."md_diagnosis_TAS000000230952" (
4"id" int4 NOT NULL,
5"code" varchar(255) COLLATE "default" NOT NULL,
6"name" varchar(255) COLLATE "default" NOT NULL,
7"parent_id" int4,
8"is_injury" bool,
9"level" int4,
10"is_leaf" bool,
11"e_code" varchar(100) COLLATE "default",
12"from_dt" date,
13"to_dt" date,
14"oms" bool,
15"stomat" bool,
16"aud_who" varchar COLLATE "default",
17"aud_when" timestamp(6),
18"aud_source" varchar COLLATE "default",
19"aud_who_create" varchar COLLATE "default",
20"aud_when_create" timestamp(6),
21"aud_source_create" varchar COLLATE "default"
22)
23WITH (OIDS=FALSE)
24;
25
26ALTER TABLE "supp"."md_diagnosis_TAS000000230952" OWNER TO "app_group_master";
27
28--копируем данные в темповую таблицу
29insert into  "supp"."md_diagnosis_TAS000000230952"  select * from "public"."md_diagnosis";
30
31--удалить лишние пробелы в таблице
32update md_diagnosis set code=trim(code);
33
34update md_diagnosis set name =trim(name);
35
36--создаем таблицу для загрузки новых диагнозов
37DROP TABLE IF EXISTS "public"."tmp_md_diagnosis_add";
38CREATE TABLE "public"."tmp_md_diagnosis_add" (
39"code" varchar(255) COLLATE "default" NOT NULL,
40"name" varchar(255) COLLATE "default" NOT NULL,
41"par_code" varchar(255) 
42)
43WITH (OIDS=FALSE);
44
45-- загрузить МКБ
46
47--создаем таблицу для диагнозов, которые надо удалить
48
49DROP TABLE IF EXISTS "public"."tmp_md_diagnosis_del";
50CREATE TABLE "public"."tmp_md_diagnosis_del" (
51"code" varchar(255) COLLATE "default" NOT NULL
52)
53WITH (OIDS=FALSE);
54
55--добавляем в таблицу диагнозы, которые надо удалить
56insert into tmp_md_diagnosis_del
57select code from md_diagnosis
58where code not  in(
59select code from  "public"."tmp_md_diagnosis_add"); --1187
60
61--обновить наименования
62
63UPDATE md_diagnosis
64SET name = (select tmp_md_diagnosis_add.name from tmp_md_diagnosis_add where tmp_md_diagnosis_add.code = md_diagnosis.code and md_diagnosis.name <> tmp_md_diagnosis_add.name)
65WHERE exists
66   (
67      select 1
68      from tmp_md_diagnosis_add
69      where md_diagnosis.code=tmp_md_diagnosis_add.code and md_diagnosis.name <> tmp_md_diagnosis_add.name
70   ); --14216
71   
72-- проставить дату закрытия у  неактуальных диагнозов  
73do $$
74declare
75r RECORD;
76ref_id integer;
77begin
78select rv.id into ref_id 
79  from mdm_refbook_version rv 
80  join mdm_internal_refbook ir on ir.id=rv.refbook_id and ir.table_name='md_diagnosis';
81
82for r in (select del.code, d.id 
83            from tmp_md_diagnosis_del del
84            join md_diagnosis d on lower(del.code)=lower(d.code))
85loop
86   if exists (select 1 from mdm_table_record where record_id::integer=r.id and refbook_id=ref_id)
87   then 
88      update mdm_table_record set close_dt='2016-06-30' where record_id::integer=r.id and refbook_id=ref_id;
89   else 
90      insert into mdm_table_record (refbook_id, record_id, close_dt) values (ref_id, ''||r.id||'', '2016-06-30');
91   end if;
92   
93if   ( select to_dt from md_diagnosis where id=r.id ) is NULL then 
94update md_diagnosis set to_dt='30.06.2016'  where id=r.id;
95end if;
96end loop;
97end $$   
98
99-- добавлям недостающие диагнозы
100
101insert into md_diagnosis (id, code, name, from_dt)
102select 
103nextval ('"public"."md_diagnosis_seq"') as id ,
104code,
105name,
106'01.01.2016' as from_dt
107 from  "public"."tmp_md_diagnosis_add"  where code not in(
108select code from md_diagnosis )
109
110--проставляем уровень и родителей
111
112do $$
113declare
114r RECORD;
115ref_id integer;
116begin
117
118for r in (SELECT m1.id id1, m2.id id2 FROM md_diagnosis m1
119JOIN md_diagnosis m2
120 ON SUBSTRING(m1.code , 1 , 3) = SUBSTRING(m2.code , 1 ,3)
121 WHERE  m2.code like  '___._'
122 AND m1.code LIKE '___'
123ORDER BY m1.code)
124
125loop
126   
127--if   ( select parent_id from md_diagnosis where id=r.id2 ) is NULL then 
128update md_diagnosis set parent_id=r.id1  where id=r.id2;
129--end if;
130end loop;
131end $$;
132
133--Parent to code LIKE 'X99'
134do $$
135declare
136r RECORD;
137ref_id integer;
138begin
139
140for r in (SELECT  d1.id id2,dp.id id1, d1.code, dp.code
141 FROM md_diagnosis  d1
142  JOIN md_diagnosis  dp ON SUBSTRING(dp.code,1,1) = SUBSTRING(d1.code,1,1)
143       AND LENGTH(dp.code) = 7
144				AND  d1.code between left (dp.code, 3) and right (dp.code, 3)
145where d1.code like '___' and dp.code like '___-___' AND (dp.code NOT LIKE '_00-_9_' 
146AND  dp.code NOT LIKE '_00-_59' 
147AND  dp.code NOT LIKE '_00-_48'  AND  dp.code NOT LIKE '_00-_89' AND dp.code NOT like '_50-_89' 
148AND dp.code NOT like '_60-_95'
149AND dp.code NOT like 'T20-T32'
150AND dp.code NOT like 'M00-M25'
151AND dp.code NOT like 'M40-M54'
152AND dp.code NOT like 'M60-M79'
153AND dp.code NOT like 'M80-M94'
154AND dp.code NOT like 'Y40-Y84'
155AND dp.code NOT like 'V01-Y98'
156AND dp.code NOT like 'V01-V99'
157)
158
159order by d1.code)
160
161loop
162   
163--if   ( select parent_id from md_diagnosis where id=r.id2 ) is NULL then 
164update md_diagnosis set parent_id=r.id1  where id=r.id2;
165--end if;
166end loop;
167end $$;
168  
169
170------------------------Level------------------------------------------------
171CREATE OR REPLACE FUNCTION recursive_update_level_to_diagnosis(diagnosis_id integer, level_value integer)
172RETURNS pg_catalog.void AS
173$body$
174DECLARE next_level_value integer;
175DECLARE row record;
176begin
177 IF ($1 is not null and $2 is not null) THEN
178  UPDATE md_diagnosis SET level = $2 WHERE id = $1;
179  next_level_value:= $2+1;
180  for row in select id  FROM md_diagnosis where parent_id = $1
181  loop
182   PERFORM recursive_update_level_to_diagnosis(row.id, next_level_value);
183  end loop;
184 END IF;
185end;
186$body$
187LANGUAGE 'plpgsql';
188SELECT recursive_update_level_to_diagnosis(id, 0) FROM md_diagnosis WHERE parent_id IS null;
189------------------------End Level------------------------------------------
190------------------------is_leaf------------------------------------------
191BEGIN TRANSACTION;
192UPDATE md_diagnosis m1 SET is_leaf = true
193WHERE not exists(select 1 from md_diagnosis where parent_id=m1.id)
194AND m1.parent_id IS NOT NULL;
195COMMIT TRANSACTION;
196------------------------End is_leaf------------------------------------------