0BsJXYsA

1 -- ПОДГОТОВКА ДАННЫХ
2drop index if exists kurgan_polis_src_h_element_idx;
3drop index if exists kurgan_polis_src_h_parent_element_idx;
4create index kurgan_polis_src_h_element_idx on kurgan_polis_src_h using btree (xml_element_id);
5create index kurgan_polis_src_h_parent_element_idx on kurgan_polis_src_h using btree (xml_parent_element_id);
6
7drop table if exists kurgan_polis_src_h1;
8create table kurgan_polis_src_h1 as
9select 
10 d.xml_data_value id_pac,
11 e.xml_data_value vpolis,
12 f.xml_data_value spolis,
13 g.xml_data_value npolis,
14 h.xml_data_value smo_ogrn,
15 i.xml_data_value smo_ok,
16 j.xml_data_value smo,
17 k.xml_data_value mcod,
18 l.xml_data_value odpolis,
19 m.xml_data_value edpolis,
20 cs.xml_data_value idcase,
21 n.xml_data_value pr_date_1,
22 o.xml_data_value pr_date_2 
23from kurgan_polis_src_h a
24 join kurgan_polis_src_h b on b.xml_parent_element_id=a.xml_element_id and b.xml_data_name='ZAP' and b.xml_data_type_description='START_ELEMENT'
25 join kurgan_polis_src_h sl on sl.xml_parent_element_id=b.xml_element_id and sl.xml_data_name='SLUCH' and sl.xml_data_type_description='START_ELEMENT'
26 join kurgan_polis_src_h cs on cs.xml_parent_element_id=sl.xml_element_id and cs.xml_data_name='IDCASE' and cs.xml_data_type_description='CHARACTERS'
27  join kurgan_polis_src_h c on c.xml_parent_element_id=b.xml_element_id and c.xml_data_name='PACIENT' and c.xml_data_type_description='START_ELEMENT'
28   left join kurgan_polis_src_h d on d.xml_parent_element_id=c.xml_element_id and d.xml_data_name='ID_PAC' and d.xml_data_type_description='CHARACTERS'
29   left join kurgan_polis_src_h e on e.xml_parent_element_id=c.xml_element_id and e.xml_data_name='VPOLIS' and e.xml_data_type_description='CHARACTERS'
30   left join kurgan_polis_src_h f on f.xml_parent_element_id=c.xml_element_id and f.xml_data_name='SPOLIS' and f.xml_data_type_description='CHARACTERS'
31   left join kurgan_polis_src_h g on g.xml_parent_element_id=c.xml_element_id and g.xml_data_name='NPOLIS' and g.xml_data_type_description='CHARACTERS'
32   left join kurgan_polis_src_h h on h.xml_parent_element_id=c.xml_element_id and h.xml_data_name='SMO_OGRN' and h.xml_data_type_description='CHARACTERS'
33   left join kurgan_polis_src_h i on i.xml_parent_element_id=c.xml_element_id and i.xml_data_name='SMO_OK' and i.xml_data_type_description='CHARACTERS'
34   left join kurgan_polis_src_h j on j.xml_parent_element_id=c.xml_element_id and j.xml_data_name='SMO' and j.xml_data_type_description='CHARACTERS'
35   left join kurgan_polis_src_h k on k.xml_parent_element_id=c.xml_element_id and k.xml_data_name='MCOD' and k.xml_data_type_description='CHARACTERS'
36   left join kurgan_polis_src_h l on l.xml_parent_element_id=c.xml_element_id and l.xml_data_name='ODPOLIS' and l.xml_data_type_description='CHARACTERS'
37   left join kurgan_polis_src_h m on m.xml_parent_element_id=c.xml_element_id and m.xml_data_name='EDPOLIS' and m.xml_data_type_description='CHARACTERS'
38   left join kurgan_polis_src_h n on n.xml_parent_element_id=c.xml_element_id and n.xml_data_name='PR_DATE_1' and n.xml_data_type_description='CHARACTERS'   
39   left join kurgan_polis_src_h o on o.xml_parent_element_id=c.xml_element_id and o.xml_data_name='PR_DATE_2' and o.xml_data_type_description='CHARACTERS'   
40where a.xml_data_name='ZL_LIST' and a.xml_data_type_description='START_ELEMENT';
41
42
43drop table if exists kurgan_polis_src_h;
44create table kurgan_polis_src_h as select * from kurgan_polis_src_h1;
45drop table kurgan_polis_src_h1;
46
47--!!!!!!!!!!!!!! если что-то не так, то вернуть
48--убираем спецсимволы из серии и номера полисов
49--update kurgan_polis_src_h set spolis = regexp_replace(trim(spolis), '-|\s|\/|\\', '', 'g'), npolis = regexp_replace(trim(npolis), '-|\s|\/|\\', '', 'g');  
50
51alter table kurgan_polis_src_h add column indiv_id integer;
52--update kurgan_polis_src_h set id_pac=substring(id_pac from 1 for position('@' in id_pac)-1) where id_pac like '%@%';
53delete from kurgan_polis_src_h where id_pac !~ '\d' and id_pac not like '%@%';
54update kurgan_polis_src_h set indiv_id = to_number(id_pac,'999999999999999999999999999999');
55create index kurgan_polis_src_h_indiv_idx on kurgan_polis_src_h using btree (indiv_id);
56
57
58--перечень случаев выносим в массив
59create table kurgan_polis_src_h1 as 
60select id_pac,vpolis,spolis,npolis,smo_ogrn,smo_ok,smo,mcod,odpolis,edpolis,pr_date_1,pr_date_2,indiv_id,cast(array_agg(idcase) as integer[]) as idcase_arr from kurgan_polis_src_h h
61group by 1,2,3,4,5,6,7,8,9,10,11,12,13
62;
63
64drop table if exists kurgan_polis_src_h;
65create table kurgan_polis_src_h as select * from kurgan_polis_src_h1;
66
67
68--очищаем таблицу фактов прохождения идентификации
69with q as (
70	select idcase_arr from kurgan_polis_src_h 
71)
72delete from fin_case_passing_identity_kurgan p using q 
73where p.case_id = any (q.idcase_arr)
74;
75
76--заполняем таблицу фактов прохождения идентификации
77/*insert into fin_case_passing_identity_kurgan (case_id)
78select * from (select distinct unnest(idcase_arr) a from jenkins.kurgan_polis_src_h h) h
79where exists (select 1 from mc_case where id = a);
80*/
81
82
83--даты прикрепления
84alter table kurgan_polis_src_h add column pr_from_dt date, add column pr_to_dt date;
85
86update kurgan_polis_src_h set pr_from_dt = pr_date_1::date
87where pr_date_1 ~ '\d{4}-\d{2}-\d{2}';
88
89update kurgan_polis_src_h set pr_to_dt = pr_date_2::date
90where pr_date_2 ~ '\d{4}-\d{2}-\d{2}';
91
92alter table kurgan_polis_src_h drop column pr_date_1, drop column pr_date_2;
93
94
95
96INSERT INTO fin_case_passing_identity_kurgan ( 	open_date 	,policy_type_code 	,policy_series 	,policy_number 	,issuer_id 	,case_id , mcod, pr_date_1, pr_date_2	)
97SELECT mc.open_date
98	,CASE 		WHEN h.vpolis = '3' 			THEN '26' 		WHEN h.vpolis = '2' 			THEN '25' 		WHEN h.vpolis = '1' 			THEN '24' 		END AS policy_type_code 	
99,h.spolis AS policy_series 	,h.npolis AS policy_number 	, poc.org_id as issuer_id, a, h.mcod, h.pr_from_dt, h.pr_to_dt
100FROM ( 	SELECT DISTINCT unnest(idcase_arr) a 		,h.* 	FROM jenkins.kurgan_polis_src_h h 	) h
101JOIN mc_case mc ON mc.id = a
102left join pim_org_code poc on poc.code = h.smo and poc.type_id = 8;
103
104
105-- Удаляю записи с несуществующими id индивида
106delete from kurgan_polis_src_h h
107where not exists (select 1 from pim_individual i where h.indiv_id = i.id);
108
109--сохраняем все записи в таблице - для связи с L файлом
110drop table if exists kurgan_polis_src_h2;
111create table kurgan_polis_src_h2 as select distinct id_pac,idcase_arr from kurgan_polis_src_h;
112create index kurgan_polis_src_h2_id_pac_idx on kurgan_polis_src_h2 using btree(id_pac);
113
114-- Индекс для поиска полисов
115alter table kurgan_polis_src_h add column id serial primary key, add column id_id integer, add column new_id_id integer;
116create index kurgan_polis_src_h_complex_idx on kurgan_polis_src_h using btree (upper(COALESCE(spolis, ''::character varying)::text || npolis::text) COLLATE pg_catalog."default");
117alter table kurgan_polis_src_h add column rn integer;
118
119-- добавляю организацию, страх.
120alter table kurgan_polis_src_h add column org_id integer;
121update kurgan_polis_src_h set org_id = (select org_id from pim_org_code oc join pim_code_type ct on ct.id = oc.type_id and ct.code = 'CODE_OMS' and oc.code = smo limit 1);
122
123-- удаляю полисы без страховой
124delete from kurgan_polis_src_h src where org_id is null;
125
126-- Оставляю по одному полису для каждого индивида
127
128with t as (select id, row_number()over(partition by indiv_id order by case when vpolis = '3' then 1 when vpolis = '1' then 2 when vpolis = '2' then 3 else 4 end) rn 
129from kurgan_polis_src_h)
130update kurgan_polis_src_h src set rn = t.rn
131from t where t.id = src.id;
132
133--INC000001547767
134--delete from kurgan_polis_src_h src where rn != 1;
135
136
137-- проставляю тип полиса
138alter table kurgan_polis_src_h add column type_id integer;
139update kurgan_polis_src_h set type_id = case vpolis 
140 when '1' then (select id from pim_doc_type dt where dt.code = 'MHI_OLDER' limit 1)
141 when '2' then (select id from pim_doc_type dt where dt.code = 'MHI_TEMP' limit 1)
142 when '3' then (select id from pim_doc_type dt where dt.code = 'MHI_UNIFORM' limit 1)
143 end;
144
145-- Удаляю дублированные полисы
146
147with t as (
148 select id, row_number() over(partition by type_id, (COALESCE(org_id, 0)), upper(COALESCE(spolis, ''::character varying)::text || npolis::text)) rn from kurgan_polis_src_h
149)
150delete from kurgan_polis_src_h src using t where t.id = src.id and t.rn > 1;
151
152
153 
154
155-- орг прикр.
156alter table kurgan_polis_src_h add column prik_org_id integer;
157update kurgan_polis_src_h set prik_org_id = (select org_id from pim_org_code oc join pim_code_type ct on ct.id = oc.type_id and ct.code = 'CODE_OMS' and oc.code = mcod limit 1);
158
159-- определяю даты выдачи и даты окончания действия полисов
160alter table kurgan_polis_src_h add column issue_dt date, add column expire_dt date;
161
162update kurgan_polis_src_h set expire_dt = edpolis::date
163where edpolis ~ '\d{4}-\d{2}-\d{2}';
164
165update kurgan_polis_src_h set issue_dt = odpolis::date
166where odpolis ~ '\d{4}-\d{2}-\d{2}';
167
168alter table kurgan_polis_src_h drop column rn, drop column edpolis, drop column odpolis;
169alter table kurgan_polis_src_h drop column smo, drop column smo_ok, drop column smo_ogrn, drop column mcod;
170
171
172-- добавляю прикрепление у кого небыло
173insert into pci_patient_reg (patient_id, clinic_id, state_id, type_id, request_dt, reg_dt, unreg_dt, request_uid)
174select indiv_id, prik_org_id, 1, 1,COALESCE(pr_from_dt,current_date),COALESCE(pr_from_dt,current_date), pr_to_dt, 'автоматически' from kurgan_polis_src_h src
175where not exists (select 1 from pci_patient_reg pr where pr.patient_id = src.indiv_id and pr.type_id = 1 and pr.state_id = 1 and prik_org_id = pr.clinic_id) and prik_org_id is not null;
176
177--обновляю даты прикрепления
178with q as (
179 select r.id,h.pr_from_dt,h.pr_to_dt,r.request_dt,r.reg_dt from pci_patient_reg r 
180  join kurgan_polis_src_h h on r.patient_id = h.indiv_id and r.clinic_id = h.prik_org_id and r.type_id = 1 and r.state_id = 1
181 where (coalesce(r.reg_dt,'01.01.1900') <> h.pr_from_dt) or (coalesce(r.unreg_dt,'31.12.9999') <> coalesce(h.pr_to_dt,'31.12.9999'))   
182)
183update pci_patient_reg r
184set reg_dt     = q.pr_from_dt,
185    request_dt = q.pr_from_dt,
186    unreg_dt   = q.pr_to_dt
187from q
188where r.id = q.id
189;  
190
191--сохраняем сведения о прикрепление возвращенные идентификацией
192with q as (
193	select unnest(src.idcase_arr) as case_id from kurgan_polis_src_h src 
194 	 join pci_patient_reg p on p.patient_id = src.indiv_id
195                           and p.clinic_id = src.prik_org_id
196                           and p.state_id = 1 and p.type_id = 1
197                           and p.reg_dt = coalesce(src.pr_from_dt,current_date)
198                           and coalesce(p.unreg_dt,'01.01.1900') = coalesce(src.pr_to_dt,'01.01.1900')	
199)
200delete from billing.fin_bill_cases_to_patient_reg r using q where r.case_id = q.case_id
201;
202
203insert into billing.fin_bill_cases_to_patient_reg (case_id,patient_reg_id)
204select unnest(src.idcase_arr),p.id from kurgan_polis_src_h src 
205 join lateral ( select id from pci_patient_reg p 
206                 where p.patient_id = src.indiv_id
207                       and p.clinic_id = src.prik_org_id
208                       and p.state_id = 1 and p.type_id = 1
209                       and p.reg_dt = coalesce(src.pr_from_dt,current_date)
210                       and coalesce(p.unreg_dt,'01.01.1900') = coalesce(src.pr_to_dt,'01.01.1900')
211                limit 1       
212               ) as p on true    
213;  
214
215--закрываю даты других прикреплений
216with q as (
217 select r.id,r.clinic_id,(h.pr_from_dt - 1) as unreg_dt from pci_patient_reg r 
218  join kurgan_polis_src_h h on r.patient_id = h.indiv_id and r.type_id = 1 and r.state_id = 1
219 where r.clinic_id <> h.prik_org_id and r.unreg_dt is null 
220)
221update pci_patient_reg r
222set unreg_dt = case when q.unreg_dt < reg_dt then reg_dt else q.unreg_dt end
223from q
224where r.id = q.id
225;  
226
227alter table kurgan_polis_src_h drop column prik_org_id;
228alter table kurgan_polis_src_h drop column pr_from_dt, drop column pr_to_dt;
229
230
231--нормализуемся - оставляем по одной записи дял каждого полиса
232drop table if exists kurgan_polis_src_h1;
233
234create table kurgan_polis_src_h1 as
235select id_pac,vpolis,spolis,npolis,indiv_id,id_id,new_id_id,org_id,type_id,issue_dt,expire_dt,max(id) as id,array_agg(distinct idcase_arr) as idcase_arr from (
236  select id_pac,vpolis,spolis,npolis,indiv_id,unnest(idcase_arr) as idcase_arr,id,id_id,new_id_id,org_id,type_id,issue_dt,expire_dt from kurgan_polis_src_h h
237)l
238group by id_pac,vpolis,spolis,npolis,indiv_id,id_id,new_id_id,org_id,type_id,issue_dt,expire_dt
239;
240
241drop table if exists kurgan_polis_src_h;
242create table kurgan_polis_src_h as select * from kurgan_polis_src_h1;
243
244alter table kurgan_polis_src_h drop column id_pac;
245
246-- ищу полисы
247alter table kurgan_polis_src_h add column id_indiv_id integer, add column id_code_id integer;
248with t as (
249 select src.id, id.id id_id, id.indiv_id id_indiv_id, id.code_id id_code_id from kurgan_polis_src_h src
250 join (select 1) x on src.vpolis in ('1', '2', '3')
251 join pim_doc_type dt on dt.id = src.type_id
252--rmissup-324
253 join pim_individual_doc id on UPPER(COALESCE(regexp_replace(id.series, '-|\s|\/|\\', '', 'g'),'')||regexp_replace(id.number, '-|\s|\/|\\', '', 'g')) = UPPER(COALESCE(regexp_replace(spolis::character varying, '-|\s|\/|\\', '', 'g'),'')||regexp_replace(npolis::character varying, '-|\s|\/|\\', '', 'g'))
254 -- join pim_individual_doc id on upper(COALESCE(id.series, ''::character varying)::text || id.number::text) = upper(COALESCE(spolis,''::character varying)::text || npolis::text)
255 --join pim_individual_doc id on lower(concat(COALESCE(trim(id.series), ''),trim(id.number))) = 
256 --                              lower(concat(COALESCE(trim(regexp_replace(spolis, '-|\s|\/|\\', '', 'g')), ''),trim(regexp_replace(npolis, '-|\s|\/|\\', '', 'g')))) 
257                               and id.type_id = src.type_id and id.issuer_id = src.org_id
258                               --and case when src.issue_dt is not null then src.issue_dt between COALESCE(id.issue_dt,'01.01.1900') and COALESCE(id.expire_dt,'31.12.9999') else true end 
259								--https://jira.egovdev.ru/browse/RMISSUP-73  https://45.r-mis.ru/jenkins/job/kurgan_xml_polis/2450/console убрал отсечку по нижней границе даты выдачи полиса, все-равно их апдейтит потом
260								and case when src.issue_dt is not null then src.issue_dt between '01.01.1900' and COALESCE(id.expire_dt,'31.12.9999') else true end 
261
262)
263update kurgan_polis_src_h src set id_indiv_id = t.id_indiv_id, id_code_id = t.id_code_id, id_id = t.id_id
264from t where t.id = src.id;
265
266-- обновляю индивиды полисов и активирую полисы
267update pim_individual_doc id set is_active = true, indiv_id = src.indiv_id
268from kurgan_polis_src_h src where src.id_id = id.id;
269
270alter table kurgan_polis_src_h drop column id_indiv_id;
271
272-- удаляю из кодов несвязанные с документами енп
273delete from pim_indiv_code ic where not exists (select 1 from pim_individual_doc id where id.code_id = ic.id) and ic.type_id = (select id from pim_code_type ct where ct.code = 'ENP');
274
275-- добавляю енп в коды
276alter table kurgan_polis_src_h add column new_ic_id integer;
277update kurgan_polis_src_h src set new_ic_id = nextval('pim_indiv_code_id_seq') where src.id_id is null and vpolis = '3';
278
279insert into pim_indiv_code(id, type_id, code, indiv_id)
280select new_ic_id, ct.id, concat(spolis, npolis), indiv_id from kurgan_polis_src_h src join pim_code_type ct on ct.code = 'ENP' where new_ic_id is not null;
281
282-- обновляю имеющиеся коды
283update pim_indiv_code ic set type_id = ct.id, code = concat(spolis, npolis), indiv_id = src.indiv_id
284from kurgan_polis_src_h src 
285join pim_code_type ct on ct.code = 'ENP'
286where src.vpolis = '3' and new_ic_id is null and id_code_id is not null and id_code_id = ic.id;
287
288-- обновляю все документы INC000001577922
289update pim_individual_doc id set is_active = true, issue_dt = src.issue_dt, expire_dt = src.expire_dt
290from kurgan_polis_src_h src where src.id_id = id.id and (src.expire_dt is null or src.expire_dt >= current_date);
291
292
293-- добавляю все документы
294update kurgan_polis_src_h set new_id_id = nextval('pim_individual_doc_id_seq') where id_id is null;
295
296insert into pim_individual_doc(id, type_id, series, number, indiv_id, issuer_id, issue_dt, expire_dt, is_active, code_id)
297select new_id_id, type_id, regexp_replace(trim(spolis), '-|\s|\/|\\', '', 'g')  , npolis, indiv_id, org_id, issue_dt, expire_dt, true, new_ic_id from kurgan_polis_src_h where new_id_id is not null;
298
299-- деактивирую остальные полисы
300update pim_individual_doc id set is_active = false 
301from kurgan_polis_src_h src, pim_doc_type dt 
302where
303 dt.id = id.type_id and dt.code in ('MHI_OLDER', 'MHI_UNIFORM', 'MHI_TEMP') and
304 src.indiv_id = id.indiv_id and id.id != coalesce(src.id_id, src.new_id_id);
305
306
307-- сдвигаю дату окончания полиса для случаев, которые были позже
308/*
309with t as (
310 select id.id, max(id.expire_dt) expire_dt, max(st.admission_date) admission_date from pim_individual_doc id
311 join pim_doc_type dt on id.type_id = dt.id and dt.code in ('MHI_UNIFORM', 'MHI_OLDER', 'MHI_UNIFORM') and id.expire_dt is not null and id.is_active 
312 join mc_case c on c.patient_id = id.indiv_id 
313 join mc_step st on st.case_id = c.id and st.admission_date > id.expire_dt
314 where exists (select 1 from mc_case c join mc_step st on st.case_id = c.id and st.admission_date > id.expire_dt and c.patient_id = id.indiv_id)
315 group by id.id
316)
317update pim_individual_doc id set expire_dt = admission_date
318from t where t.id = id.id;
319*/
320
321-- деактивирую просроченные на текущий момент полисы
322update pim_individual_doc id set is_active = false
323from pim_doc_type dt where dt.id = id.type_id and dt.code in ('MHI_UNIFORM', 'MHI_OLDER', 'MHI_TEMP')
324 and id.expire_dt < current_date and id.is_active;
325
326-- делаю активным последний полис тем, у кого нет ни одного активного (при условии, что он не просрочен, если просрочен, то увы, у человека не будет ни одного активного)
327with t as (
328 select id.indiv_id from pim_individual_doc id 
329 join pim_doc_type dt on dt.code in ('MHI_OLDER', 'MHI_UNIFORM', 'MHI_TEMP')
330 group by id.indiv_id having bool_or(is_active) = false
331),
332t1 as (
333 select id.id, id.indiv_id, issue_dt, row_number() over(partition by id.indiv_id order by issue_dt desc, case when expire_dt is null then 0 else 1 end) rn, expire_dt < current_date not_ok from pim_individual_doc id 
334 join t on t.indiv_id = id.indiv_id
335)
336update pim_individual_doc id set is_active = true
337from t1 where t1.id = id.id and rn = 1 and not_ok is null;
338
339
340
341--недопускаем дублирвоания caseid во всей таблице
342with q as (
343select COALESCE(h.id_id,h.new_id_id) as doc_id, unnest(h.idcase_arr) as case_id from kurgan_polis_src_h h
344)
345delete from pim_individual_doc_on_case_kurgan k using q 
346 where exists (select 1 from q where k.case_id = q.case_id and k.doc_id <> q.doc_id)
347;
348
349
350--добавляем новые записи в успешно пройденные идентификацию случаи 
351insert into pim_individual_doc_on_case_kurgan (doc_id,case_id)
352with q as (
353select COALESCE(h.id_id,h.new_id_id) as id,unnest(h.idcase_arr) as case_id from kurgan_polis_src_h h 
354)
355select id as doc_id,case_id from q 
356 where not exists (select 1 from pim_individual_doc_on_case_kurgan k where k.case_id = q.case_id and k.doc_id = q.id)
357;