e0CPcxPM

1--создание таблицы персональных данных
2
3create index lxml_element_idx on tuva_loader_l using btree (xml_element_id);
4create index lxml_parent_element_idx on tuva_loader_l using btree (xml_parent_element_id);
5drop table if exists tuva_loader_pers;
6create table tuva_loader_pers as
7select 
8 a.xml_element_id lxml_id, 
9 b.xml_data_value id_pac,
10 c.xml_data_value fam,
11 d.xml_data_value im,
12 e.xml_data_value ot,
13 f.xml_data_value w,
14 g.xml_data_value dr,
15 h.xml_data_value snils,
16 i.xml_data_value doctype,
17 j.xml_data_value docnum,
18 k.xml_data_value docser
19from tuva_loader_l a
20left join tuva_loader_l b on b.xml_parent_element_id=a.xml_element_id and b.xml_data_name='ID_PAC' and b.xml_data_type_description='CHARACTERS'
21left join tuva_loader_l c on c.xml_parent_element_id=a.xml_element_id and c.xml_data_name='FAM' and c.xml_data_type_description='CHARACTERS'
22left join tuva_loader_l d on d.xml_parent_element_id=a.xml_element_id and d.xml_data_name='IM' and d.xml_data_type_description='CHARACTERS'
23left join tuva_loader_l e on e.xml_parent_element_id=a.xml_element_id and e.xml_data_name='OT' and e.xml_data_type_description='CHARACTERS'
24left join tuva_loader_l f on f.xml_parent_element_id=a.xml_element_id and f.xml_data_name='W' and f.xml_data_type_description='CHARACTERS'
25left join tuva_loader_l g on g.xml_parent_element_id=a.xml_element_id and g.xml_data_name='DR' and g.xml_data_type_description='CHARACTERS'
26left join tuva_loader_l h on h.xml_parent_element_id=a.xml_element_id and h.xml_data_name='SNILS' and h.xml_data_type_description='CHARACTERS'
27left join tuva_loader_l i on i.xml_parent_element_id=a.xml_element_id and i.xml_data_name='DOCTYPE' and i.xml_data_type_description='CHARACTERS'
28left join tuva_loader_l j on j.xml_parent_element_id=a.xml_element_id and j.xml_data_name='DOCNUM' and j.xml_data_type_description='CHARACTERS'
29left join tuva_loader_l k on k.xml_parent_element_id=a.xml_element_id and k.xml_data_name='DOCSER' and k.xml_data_type_description='CHARACTERS'
30where a.xml_data_name='PERS' and a.xml_data_type_description='START_ELEMENT'
31;
32
33--проверка на полноту и валидность обязательных полей индивида
34--лог
35insert into tuva_loader_log(pac_id, act, act_code, act_result, act_result_code, loader_rn)
36select id_pac, 'проверка исходных данных пациента','pers check', 'отсутствует одно из обязательных полей или невалидно (фио/др/пол/снилс)', 'not full pers', (select max(loader_rn) from tuva_loader_log) from tuva_loader_pers where
37not(w in('1','2') 
38and substring(dr,1,1) in ('1','2')
39and substring(dr,2,1) in ('0','1','2','3','4','5','6','7','8','9')
40and substring(dr,3,1) in ('0','1','2','3','4','5','6','7','8','9')
41and substring(dr,4,1) in ('0','1','2','3','4','5','6','7','8','9')
42and substring(dr,5,1) = '-'
43and substring(dr,6,1) in ('0','1')
44and substring(dr,7,1) in ('0','1','2','3','4','5','6','7','8','9')
45and substring(dr,8,1) = '-'
46and substring(dr,9,1) in ('0','1','2','3')
47and substring(dr,10,1) in ('0','1','2','3','4','5','6','7','8','9')
48and snils is not null
49and substring(snils,1,1) in ('0','1','2','3','4','5','6','7','8','9')
50and substring(snils,2,1) in ('0','1','2','3','4','5','6','7','8','9')
51and substring(snils,3,1) in ('0','1','2','3','4','5','6','7','8','9')
52
53and substring(snils,5,1) in ('0','1','2','3','4','5','6','7','8','9')
54and substring(snils,6,1) in ('0','1','2','3','4','5','6','7','8','9')
55and substring(snils,7,1) in ('0','1','2','3','4','5','6','7','8','9')
56
57and substring(snils,9,1) in ('0','1','2','3','4','5','6','7','8','9')
58and substring(snils,10,1) in ('0','1','2','3','4','5','6','7','8','9')
59and substring(snils,11,1) in ('0','1','2','3','4','5','6','7','8','9')
60
61and substring(snils,13,1) in ('0','1','2','3','4','5','6','7','8','9')
62and substring(snils,14,1) in ('0','1','2','3','4','5','6','7','8','9')
63
64and upper(ot)!='НЕТ' and upper(fam)!='НЕТ' and upper(im)!='НЕТ' 
65and upper(trim(ot))!='' and upper(trim(fam))!='' and upper(trim(im))!='' )
66;
67
68drop table if exists tuva_loader_pers1;
69create table tuva_loader_pers1 as
70select lxml_id, id_pac, fam, im, ot, to_number(w,'9') w, dr, substring(snils,1,3)||substring(snils,5,3)||substring(snils,9,3)||substring(snils,13,2) snils, doctype, upper(docnum) docnum, upper(docser) docser from tuva_loader_pers 
71where w in('1','2') 
72and substring(dr,1,1) in ('1','2')
73and substring(dr,2,1) in ('0','1','2','3','4','5','6','7','8','9')
74and substring(dr,3,1) in ('0','1','2','3','4','5','6','7','8','9')
75and substring(dr,4,1) in ('0','1','2','3','4','5','6','7','8','9')
76and substring(dr,5,1) = '-'
77and substring(dr,6,1) in ('0','1')
78and substring(dr,7,1) in ('0','1','2','3','4','5','6','7','8','9')
79and substring(dr,8,1) = '-'
80and substring(dr,9,1) in ('0','1','2','3')
81and substring(dr,10,1) in ('0','1','2','3','4','5','6','7','8','9')
82and snils is not null
83and substring(snils,1,1) in ('0','1','2','3','4','5','6','7','8','9')
84and substring(snils,2,1) in ('0','1','2','3','4','5','6','7','8','9')
85and substring(snils,3,1) in ('0','1','2','3','4','5','6','7','8','9')
86
87and substring(snils,5,1) in ('0','1','2','3','4','5','6','7','8','9')
88and substring(snils,6,1) in ('0','1','2','3','4','5','6','7','8','9')
89and substring(snils,7,1) in ('0','1','2','3','4','5','6','7','8','9')
90
91and substring(snils,9,1) in ('0','1','2','3','4','5','6','7','8','9')
92and substring(snils,10,1) in ('0','1','2','3','4','5','6','7','8','9')
93and substring(snils,11,1) in ('0','1','2','3','4','5','6','7','8','9')
94
95and substring(snils,13,1) in ('0','1','2','3','4','5','6','7','8','9')
96and substring(snils,14,1) in ('0','1','2','3','4','5','6','7','8','9')
97
98and upper(ot)!='НЕТ' and upper(fam)!='НЕТ' and upper(im)!='НЕТ' 
99and upper(trim(ot))!='' and upper(trim(fam))!='' and upper(trim(im))!='' 
100;
101
102
103--проверка на валидность снилс по контрольной сумме
104--лог
105insert into tuva_loader_log(pac_id, act, act_code, act_result, act_result_code, loader_rn)
106select id_pac, 'проверка контрольной суммы снилс','snils check', 'неверная контрольная сумма снилс', 'crc snils invalid', (select max(loader_rn) from tuva_loader_log) from tuva_loader_pers1 
107where
108
109not(
110(
111 to_number(substring(snils,1,1),'9')*9+
112 to_number(substring(snils,2,1),'9')*8+
113 to_number(substring(snils,3,1),'9')*7+
114 to_number(substring(snils,4,1),'9')*6+
115 to_number(substring(snils,5,1),'9')*5+
116 to_number(substring(snils,6,1),'9')*4+
117 to_number(substring(snils,7,1),'9')*3+
118 to_number(substring(snils,8,1),'9')*2+
119 to_number(substring(snils,9,1),'9')  
120)%101=to_number(substring(snils,10,2),'99') 
121
122or
123
124(
125 to_number(substring(snils,1,1),'9')*9+
126 to_number(substring(snils,2,1),'9')*8+
127 to_number(substring(snils,3,1),'9')*7+
128 to_number(substring(snils,4,1),'9')*6+
129 to_number(substring(snils,5,1),'9')*5+
130 to_number(substring(snils,6,1),'9')*4+
131 to_number(substring(snils,7,1),'9')*3+
132 to_number(substring(snils,8,1),'9')*2+
133 to_number(substring(snils,9,1),'9')  
134)%101 = 100 and to_number(substring(snils,10,2),'99') = 0
135);
136
137
138
139
140drop table if exists tuva_loader_pers2;
141create table tuva_loader_pers2 as
142select 
143*
144
145from tuva_loader_pers1
146where 
147
148(
149 to_number(substring(snils,1,1),'9')*9+
150 to_number(substring(snils,2,1),'9')*8+
151 to_number(substring(snils,3,1),'9')*7+
152 to_number(substring(snils,4,1),'9')*6+
153 to_number(substring(snils,5,1),'9')*5+
154 to_number(substring(snils,6,1),'9')*4+
155 to_number(substring(snils,7,1),'9')*3+
156 to_number(substring(snils,8,1),'9')*2+
157 to_number(substring(snils,9,1),'9')  
158)%101=to_number(substring(snils,10,2),'99') 
159
160or
161
162(
163 to_number(substring(snils,1,1),'9')*9+
164 to_number(substring(snils,2,1),'9')*8+
165 to_number(substring(snils,3,1),'9')*7+
166 to_number(substring(snils,4,1),'9')*6+
167 to_number(substring(snils,5,1),'9')*5+
168 to_number(substring(snils,6,1),'9')*4+
169 to_number(substring(snils,7,1),'9')*3+
170 to_number(substring(snils,8,1),'9')*2+
171 to_number(substring(snils,9,1),'9')  
172)%101 = 100 and to_number(substring(snils,10,2),'99') = 0
173
174;
175
176
177
178--select * from tuva_loader_pers2
179
180
181--создаем таблицу соответствий снилс и индивида
182drop table if exists tuva_loader_snils;
183create table tuva_loader_snils as 
184select distinct ic.indiv_id, ic.code from pim_indiv_code ic
185join pim_code_type ct on ct.id=ic.type_id and ct.code='SNILS'
186;
187
188--дубли снилс в системе - одинаковые снилс у разных пациентов
189drop table if exists tuva_loader_snils1;
190create table tuva_loader_snils1 as 
191select code from tuva_loader_snils group by code having count(1)>1
192;
193
194create index tuva_loader_snils1_code_idx on tuva_loader_snils1 using btree (code);
195create index tuva_loader_pers2_snils_idx on tuva_loader_pers2  using btree (snils);
196
197--лог
198insert into tuva_loader_log(pac_id, act, act_code, act_result, act_result_code, loader_rn)
199select id_pac, 'поиск пациента по снилс','snils search', 'по снилс данного пациента находится несколько индивидов в системе, пациент пропущен', 'double snils', (select max(loader_rn) from tuva_loader_log) 
200from tuva_loader_snils1 a
201join tuva_loader_pers2 b on a.code=b.snils
202;
203
204delete from tuva_loader_pers2 b
205using tuva_loader_snils1 a
206where a.code=b.snils
207;
208
209--уникальные снилс в системе
210drop table if exists tuva_loader_snils2;
211create table tuva_loader_snils2 as 
212select indiv_id, code from tuva_loader_snils where tuva_loader_snils.code in (select code from tuva_loader_snils group by code having count(1)=1);
213
214
215create index tuva_loader_snils2_code_idx on tuva_loader_snils2 using btree (code);
216
217--таблица с найденными по снилс индивидами
218drop table if exists tuva_loader_pers3;
219create table tuva_loader_pers3 as
220select a.*, b.indiv_id snils_indiv_id
221from tuva_loader_pers2 a
222left join tuva_loader_snils2 b on b.code=a.snils
223;
224
225drop table if exists tuva_loader_ind;
226create table tuva_loader_ind as
227select upper(trim(surname)) || upper(trim(name)) || upper(trim(patr_name)) || to_char(birth_dt,'yyyy-mm-dd') || trim(to_char(gender_id,'9')) srch_str, id from pim_individual;
228
229drop table if exists tuva_loader_ind1;
230create table tuva_loader_ind1 as
231select * from tuva_loader_ind where srch_str in (select srch_str from tuva_loader_ind group by srch_str having count(1)=1);
232
233drop table if exists tuva_loader_pers4;
234create table tuva_loader_pers4 as
235select *, upper(trim(fam)) || upper(trim(im)) || upper(trim(ot)) || trim(dr) || trim(to_char(w,'9')) srch_str from tuva_loader_pers3
236;
237
238drop table if exists tuva_loader_pers5;
239create table tuva_loader_pers5 as
240select a.*, b.id ind_indiv_id from tuva_loader_pers4 a
241left join tuva_loader_ind1 b on a.srch_str=b.srch_str
242;
243
244drop table if exists tuva_loader_pers6;
245create table tuva_loader_pers6 as
246select 
247*, 
248case 
249 when snils_indiv_id=ind_indiv_id then '='
250 when snils_indiv_id!=ind_indiv_id then '!='
251 when snils_indiv_id is null and ind_indiv_id is null then 'new all'
252 when snils_indiv_id is null and ind_indiv_id is not null then 'create snils'
253 when snils_indiv_id is not null and ind_indiv_id is null then 'update fio'
254end act 
255from tuva_loader_pers5
256;
257
258--лог
259insert into tuva_loader_log(pac_id, act, act_code, act_result, act_result_code, loader_rn)
260select id_pac, 'поиск пациента по снилс и по по фио/др/пол','ptn search', 'по снилс находится пациент ' || trim(to_char(snils_indiv_id,'999999999999')) || ' по фио/др/пол ' || trim(to_char(ind_indiv_id,'999999999999')), 'ptn search ambiguous', (select max(loader_rn) from tuva_loader_log) from 
261tuva_loader_pers6 where act='!='
262;
263
264delete from tuva_loader_pers6 where act='!='
265;
266
267
268
269
270--создание новых снилс
271drop table if exists tuva_loader_pers7;
272
273create table tuva_loader_pers7 as
274select distinct ind_indiv_id, snils from tuva_loader_pers6 where act='create snils';
275
276alter table tuva_loader_pers7 add column new_snils_id bigint;
277
278update tuva_loader_pers7 set new_snils_id=nextval('pim_indiv_code_id_seq');
279
280insert into pim_indiv_code(id, indiv_id, code, type_id)
281select new_snils_id, ind_indiv_id, snils, ct.id from tuva_loader_pers7 
282join pim_code_type ct on ct.code='SNILS';
283
284--лог
285insert into tuva_loader_log(ind_id, act, act_code, act_result, act_result_code, loader_rn)
286select ind_indiv_id, 'добавление снилс','add snils', 'добавлен снилс pim_indiv_code.id=' || trim(to_char(new_snils_id,'999999999999')), 'add snils ok', (select max(loader_rn) from tuva_loader_log) from 
287tuva_loader_pers7;
288
289alter table tuva_loader_pers6 add column new_snils_id bigint;
290
291update tuva_loader_pers6 a set new_snils_id=x.new_snils_id
292from tuva_loader_pers7 x
293where x.ind_indiv_id=a.ind_indiv_id and x.snils=a.snils;
294
295
296--создание новых пациентов со снилс
297--update tuva_loader_pers6 set new_snils_id=nextval('pim_indiv_code_id_seq') where act='new all';
298--select * from tuva_loader_pers6 a where act='new all'
299drop table if exists tuva_loader_pers7;
300
301create table tuva_loader_pers7 as
302select distinct snils from tuva_loader_pers6 where act='new all';
303
304alter table tuva_loader_pers7 add column new_snils_id bigint;
305
306update tuva_loader_pers7 set new_snils_id=nextval('pim_indiv_code_id_seq');
307
308alter table tuva_loader_pers7 add column new_indiv_id bigint;
309
310update tuva_loader_pers7 set new_indiv_id=nextval('pim_party_id_seq');
311
312alter table tuva_loader_pers6 add column new_indiv_id bigint;
313
314update tuva_loader_pers6 a set new_indiv_id=x.new_indiv_id, new_snils_id=x.new_snils_id
315from tuva_loader_pers7 x
316where a.act='new all' and a.snils=x.snils;
317
318insert into pim_party(id, type_id)
319select new_indiv_id, 1 from tuva_loader_pers7;
320
321with t as (select fam, im, ot, snils, dr, w, new_snils_id, new_indiv_id, row_number()over(partition by snils) rn from tuva_loader_pers6 where act='new all')
322insert into pim_individual(surname, name, patr_name, birth_dt, gender_id, id)
323select t.fam, t.im, t.ot, to_date(t.dr,'yyyy-mm-dd'), t.w, t.new_indiv_id from t
324where t.rn=1;
325
326insert into pci_patient(id)
327select new_indiv_id from tuva_loader_pers7;
328
329insert into pim_indiv_code(id, indiv_id, code, type_id)
330select new_snils_id, new_indiv_id, snils, ct.id from tuva_loader_pers7 
331join pim_code_type ct on ct.code='SNILS';
332
333--лог
334insert into tuva_loader_log(ind_id, act, act_code, act_result, act_result_code, loader_rn)
335select new_indiv_id, 'добавление пациента со снилс', 'add ptn', 'пациент добавлен, снилс ему добавлен снилс=' || snils || ' pim_indiv_code.id=' || new_snils_id, 'add ptn ok',(select max(loader_rn) from tuva_loader_log) from tuva_loader_pers7;
336
337--соответствие pers xml с indiv_id
338drop table if exists tuva_loader_xml_pac_to_indiv_id;
339create table tuva_loader_xml_pac_to_indiv_id as
340select id_pac, coalesce(snils_indiv_id, ind_indiv_id, new_indiv_id) id from tuva_loader_pers6;
341
342insert into pci_patient(id)
343select distinct a.id from tuva_loader_xml_pac_to_indiv_id a
344left join pci_patient b on b.id=a.id
345where b.id is null;