iPRP1nhr

1--drop table if exists jenkins.rt_loader_receipts;
2
3--create table jenkins.rt_loader_receipts as 
4select 
5	distinct mr.id,
6	mr.patient_id, 
7	t.lpucode as kodlpu,
8	to_hex(mr.patient_id) as kod,
9	left(q.snils, 3) || '-' || right(left(q.snils, 6), 3) || '-' || right(left(q.snils, 9), 3) || ' ' || right(q.snils, 2) as snils,
10	null::text polis,
11	i.surname as fam,
12	i."name" as "name",	
13	i.patr_name as otch,
14	case when g.code = 'FEMALE' then 'Ж' when g.code='MALE' then 'М' else null end as pol,
15	i.birth_dt drod,
16	null::text mkb,
17	null::int kod_v,
18	null::char(1) vid,
19	null::text gr,
20	mr.series as seria,
21	mr."number" as nomer,
22	mr.issue_dt as datr,
23	null::numeric(6,1) prep,
24	null::text prep9,
25	null::bigint c_mnn,
26	null::bigint c_trn,
27	null::int c_lf,
28	null::text dz,
29	null::int c_dls,
30	null::text kol,
31	case when decision then '1' else null end as p_kek,
32	'1'::text dtd,
33	null::text v_lf,
34	null::int c_vlf,
35	null::text m_lf,
36	null::int c_mlf,
37	null::int n_doza,	
38	null::text c_dls_code, 
39	null::text[] msg
40from md_receipt mr
41join md_receipt_type mrt on mrt.id = mr.type_id
42join pci_benefit pb on pb.id = mr.benefit_id
43join pci_benefit_definition pbd on pbd.id = pb.benefit_def_id
44join pci_benefit_type pbt on pb.benefit_type_id = pbt.id
45join pim_individual i on i.id = mr.patient_id
46left join pim_gender g on i.gender_id = g.id
47left join (
48	select right(poc.code, 4) as lpucode
49	from pim_org_code poc
50	join pim_code_type pct on pct.id = poc.type_id 
51	where poc.org_id = 304	and pct.code='CODE_OMS'
52	order by issue_dt desc nulls last, poc.id desc limit 1
53) t on true
54left join lateral (
55	select regexp_replace("number", '\D', '', 'g') as snils
56	from pim_individual_doc pid join pim_doc_type pdt on pdt.id = pid.type_id
57	where pid.indiv_id = mr.patient_id and pdt.code='SNILS'
58	order by is_active desc nulls last, issue_dt desc nulls last 
59	limit 1
60) q on true
61where mr.clinic_id = 304
62	and (pbt.code in ('4', '3') or pbd.code = '95') 
63	and mrt.code = 'PRIVILEGED' 
64	and not mr.canceled 
65	and mr.issue_dt::date between to_date('01.01.2016', 'dd.mm.yyyy') and to_date('30.09.2016', 'dd.mm.yyyy');