zFp4pnvy

1--получаем поля с датами к типу date
2alter table jenkins.pfr_raw_data add column birth_dt_date date;
3alter table jenkins.pfr_raw_data add column doc_when_date date;
4alter table jenkins.pfr_raw_data add column reg_date_date date;
5alter table jenkins.pfr_raw_data add column out_date_date date;
6
7--индекс для поиска по фио + др
8CREATE INDEX pfr_raw_data_fiodr_idx ON jenkins.pfr_raw_data USING btree (lower(trim(family)),lower(trim(name)),lower(trim(patr_name)),birth_dt_date); 
9
10
11update jenkins.pfr_raw_data 
12set 
13	birth_dt_date = to_date(birth_date,'yyyy/mm/dd')
14where birth_date ~ '\d{4}/\d{2}/\d{2}'       
15;  
16
17update jenkins.pfr_raw_data 
18set 
19	doc_when_date = to_date(doc_when,'yyyy/mm/dd')
20where doc_when ~ '\d{4}/\d{2}/\d{2}'       
21;   
22
23update jenkins.pfr_raw_data 
24set 
25	reg_date_date = to_date(reg_date,'yyyy/mm/dd')
26where reg_date ~ '\d{4}/\d{2}/\d{2}'       
27;   
28
29update jenkins.pfr_raw_data 
30set 
31	out_date_date = to_date(out_date,'yyyy/mm/dd')
32where out_date ~ '\d{4}/\d{2}/\d{2}'       
33;
34
35--храним снилс без разделителей (как в РМИС)
36update jenkins.pfr_raw_data 
37set snils = replace(replace(snils,'-',''),' ','')
38;
39
40--поле под хранение пациента
41alter table jenkins.pfr_raw_data add column indiv_id integer;
42
43--прсотавялем ид индивида для однозначно совпавших
44with q as (
45select p.id, i.id as indiv_id from jenkins.pfr_raw_data p 
46 join pim_individual i on lower(trim(i.surname)) = lower(trim(p.family)) 
47                          and lower(trim(i.name)) = lower(trim(p.name))
48                          and lower(trim(i.patr_name)) = lower(trim(p.patr_name)) 
49                          and i.birth_dt = p.birth_dt_date 
50 where (select count(distinct id) from pim_individual i 
51        where lower(trim(i.surname)) = lower(trim(p.family)) 
52              and lower(trim(i.name)) = lower(trim(p.name))
53              and lower(trim(i.patr_name)) = lower(trim(p.patr_name)) 
54              and i.birth_dt = p.birth_dt_date 
55       ) = 1
56       and not exists (select 1 from pim_individual_doc d 
57                        where d.indiv_id = i.id and trim(d.number) <> trim(p.snils) 
58                              and d.type_id = (select id from pim_doc_type c where code = 'SNILS')
59                       )
60)
61update jenkins.pfr_raw_data p 
62set indiv_id = q.indiv_id                        
63from q
64where p.id = q.id
65;
66
67--проставляем id индивида по номеру снилс и совпавшему фио или др
68with q as (
69select p.id,i.id as indiv_id from jenkins.pfr_raw_data p
70 join pim_individual_doc d on trim(p.snils)=trim(d.number) 
71                              and (d.type_id = (select id from pim_doc_type where code = 'SNILS')) 
72 join pim_individual i on d.indiv_id = i.id                             
73where p.indiv_id is null and
74      (
75       (
76         lower(trim(p.family)) = lower(trim(i.surname))
77         and lower(trim(p.name)) = lower(trim(i.name))
78         and lower(trim(p.patr_name)) = lower(trim(i.patr_name))
79       ) or 
80       (p.birth_dt_date = i.birth_dt)  
81      )
82)
83update jenkins.pfr_raw_data p 
84set indiv_id = q.indiv_id
85from q
86where p.id = q.id
87; 
88
89--выносим не найденных в ошибку
90drop table if exists jenkins.pfr_error_record;
91create table jenkins.pfr_error_record as 
92select id,family,name,patr_name,birth_dt_date,snils,reg_addr,CAST('Не найден в РМИС по указанным реквизитам' as varchar) as cause from jenkins.pfr_raw_data p
93where indiv_id is null
94;
95
96delete from jenkins.pfr_raw_data where indiv_id is null;
97
98--поле для хранения типа документа удл
99alter table jenkins.pfr_raw_data add column doc_type_id integer;
100
101update jenkins.pfr_raw_data p 
102set doc_type_id = 
103       case trim(doc_code)
104         when 'ПАСПОРТ' 		then (select id from pim_doc_type where code = 'PASSUSSR' limit 1)
105         when 'ПАСПОРТ РОССИИ' 	then (select id from pim_doc_type where code = 'PASSPORT_RUSSIAN_FEDERATION' limit 1)
106         when 'СВИД О РОЖД' 	then (select id from pim_doc_type where code = 'BIRTH' limit 1)
107         when 'СПРАВКА ОБ ОСВ' 	then (select id from pim_doc_type where code = 'DISMISS' limit 1)
108         when 'СВИД БЕЖЕНЦА' 	then (select id from pim_doc_type where code = '10' limit 1)
109         when 'ВИД НА ЖИТЕЛЬ' 	then (select id from pim_doc_type where code = '11' limit 1)
110         when 'УДОСТ БЕЖЕНЦА' 	then (select id from pim_doc_type where code = 'REFUGEE' limit 1)
111         when 'ВОЕННЫЙ БИЛЕТ' 	then (select id from pim_doc_type where code = 'VOEN' limit 1)
112         when 'УДОСТ ЛИЧ ВОЕН' 	then (select id from pim_doc_type where code = '4' limit 1)
113         when 'ДИППАСПОРТ РФ' 	then (select id from pim_doc_type where code = '8' limit 1)
114         when 'ПАСПОРТ МОРФЛТ' 	then (select id from pim_doc_type where code = '6' limit 1)
115         when 'ПАСПОРТ МОРЯКА' 	then (select id from pim_doc_type where code = '16' limit 1)
116         when 'ЗГПАСПОРТ' 		then (select id from pim_doc_type where code = '2' limit 1)
117         when 'ИНПАСПОРТ' 		then (select id from pim_doc_type where code = '9' limit 1)
118         when 'ЗГПАСПОРТ РФ' 	then (select id from pim_doc_type where code = 'PASSZAGRAN' limit 1)
119         when 'ВРЕМ УДОСТ' 		then (select id from pim_doc_type where code = 'MHI_TEMP' limit 1)
120         when 'ВОЕН БИЛЕТ 03' 	then (select id from pim_doc_type where code = '17' limit 1)
121         when 'ПРОЧЕЕ' 			then (select id from pim_doc_type where code = 'EDU_DOCS' limit 1)
122         when 'УДОСТ ТОЖ ФОТО'	then (select id from pim_doc_type where code = 'PERSON' limit 1) 
123       end
124where nullif(trim(doc_code),'') is not null       
125;    
126
127--поле для хранения льготы
128alter table jenkins.pfr_raw_data add column benefit_def_id integer; 
129
130with q as (
131select p.id,d.id as benefit_def_id from jenkins.pfr_raw_data p 
132 join pci_benefit_definition d on case when trim(p.gsp1) <> '000' then trim(p.gsp1) = trim(d.code) else trim(p.gsp2) = trim(d.code) end
133where d.benefit_type_id = (select id from pci_benefit_type where code = '2') 
134)
135update jenkins.pfr_raw_data p
136set benefit_def_id = q.benefit_def_id
137from q
138where p.id = q.id
139;
140
141--с неизвестными кодами льготы выбрасываем в ошибку
142insert into jenkins.pfr_error_record 
143select id,family,name,patr_name,birth_dt_date,snils,reg_addr,
144       concat('Не установленный код льготы ГСП1: ',gsp1,' ГСП2: ',gsp2) as cause
145from jenkins.pfr_raw_data p where p.benefit_def_id is null
146;
147
148delete from jenkins.pfr_raw_data p where p.benefit_def_id is null;
149
150--убераем дубли документов УДЛ
151with q as ( 
152	select p.doc_ser,p.doc_num,doc_type_id,count(*),array_agg(p.id order by p.id) as ids from jenkins.pfr_raw_data p
153	group by 1,2,3
154	having count(*)>1
155)
156update jenkins.pfr_raw_data p
157set 
158	doc_type_id = null,
159    doc_ser     = null,
160    doc_num     = null,
161    doc_code  = null,
162    doc_issue = null,
163    doc_when  = null,
164    doc_when_date = null
165from q
166where p.id = any(q.ids) and p.id <> ids[1]
167;    
168
169
170
171
172--добавляем документ УДЛ, тем у кого нет
173insert into pim_individual_doc (id,indiv_id,type_id,series,number,issue_dt,issuer_text)
174select nextval('pim_individual_doc_id_seq'), p.indiv_id,p.doc_type_id,p.doc_ser,RIGHT (trim(p.doc_num), 6),p.doc_when_date,p.doc_issue from jenkins.pfr_raw_data p 
175where p.doc_type_id is not null
176      and not exists (select 1 from pim_individual_doc d where d.indiv_id = p.indiv_id and d.type_id = p.doc_type_id)
177      and not exists (select 1 from pim_individual_doc d where trim(d.series) = trim(p.doc_ser) and trim(d.number) = RIGHT (trim(p.doc_num), 6) and d.type_id = p.doc_type_id)
178
179;
180
181--добавляем новые льготы
182insert into pci_benefit (patient_id,benefit_def_id,benefit_type_id,from_dt,to_dt)
183with t as (
184	select id from pci_benefit_type where code = '2' --федеральная льгота
185)
186select p.indiv_id,p.benefit_def_id,t.id as benefit_type_id,p.reg_date_date,p.out_date_date from jenkins.pfr_raw_data p,t
187 where not exists (select 1 from pci_benefit b where b.patient_id = p.indiv_id and b.benefit_type_id = t.id)
188;
189
190
191--обновляем существующие
192with t as (
193	select id from pci_benefit_type where code = '2' --федеральная льгота
194),
195q as (
196	select b.id,p.reg_date_date,p.out_date_date,p.benefit_def_id from jenkins.pfr_raw_data p 
197 	 join pci_benefit b on p.indiv_id = b.patient_id 
198 	 join t on t.id = b.benefit_type_id
199    where coalesce(b.from_dt,'01.01.1900') <> coalesce(p.reg_date_date,'01.01.1900')
200          or coalesce(b.to_dt,'01.01.1900') <> coalesce(p.out_date_date,'01.01.1900')  
201          or b.benefit_def_id <> p.benefit_def_id
202)
203update pci_benefit b 
204set
205	from_dt = q.reg_date_date,
206    to_dt   = q.out_date_date,
207	benefit_def_id = q.benefit_def_id
208from q
209where b.id = q.id 
210; 
211
212
213--закрываем несуществующие льготы текущей датой
214with t as (
215	select id from pci_benefit_type where code = '2' --федеральная льгота
216),
217q as (
218select b.id from pci_benefit b
219 join t on b.benefit_type_id = t.id 
220where not exists (select 1 from jenkins.pfr_raw_data p where b.patient_id = p.indiv_id and b.benefit_def_id = p.benefit_def_id)
221)
222update pci_benefit b
223set
224	to_dt = current_date
225from q
226where b.id = q.id and '${ext}' = 'MS0'
227;