Gg40f1Pk

1DROP TABLE IF EXISTS "jenkins"."log_update_patient_part_case";
2CREATE TABLE "jenkins"."log_update_patient_part_case" ("log" varchar(250));
3
4do language plpgsql $$
5declare
6_r record;
7_from_dt date;
8_id integer;
9BEGIN
10for _r in (
11select (current_date - pi.birth_dt) date_diff, pi.id patient_id, pi.birth_dt, ppc.id part_case_id, pppc.from_dt, pppc.to_dt, pppc.id
12from pim_individual pi
13join pci_patient pp on pp.id = pi.id
14left join pci_patient_part_case pppc on pppc.patient_id = pi.id
15left join pci_part_case ppc on ppc.id = pppc.part_case_id and ppc.id in (0,5)
16where pi.birth_dt is not null
17and not exists(
18select 1 
19from pim_individual_doc pid 
20join pim_doc_type pdt on pdt.id = pid.type_id 
21and pdt.code in ('MHI_OLDER','MHI_TEMP','MHI_UNIFORM')
22and pid.is_active = true 
23and (pid.expire_dt is null or pid.expire_dt > current_date)
24where pid.indiv_id = pi.id) 
25--and pi.id = 1007913
26)
27loop
28
29if _r.date_diff > 30 then
30	RAISE NOTICE 'лог: %, pci_patient_part_case.id = %, patient_id = %', 'date_diff > 30', _r.id, _r.patient_id;
31	INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'date_diff > 30, pci_patient_part_case.id = '|| COALESCE(_r.id::varchar(20), 'null') ||', patient_id = '|| _r.patient_id::varchar(20);
32	if _r.part_case_id is null then
33		RAISE NOTICE 'лог: %, pci_patient_part_case.id = %, patient_id = %', 'нет особого случая', _r.id, _r.patient_id;
34		INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'нет особого случая, pci_patient_part_case.id = '|| COALESCE(_r.id::varchar(20), 'null') ||', patient_id = '|| _r.patient_id::varchar(20);
35		CONTINUE;
36	else
37		if _r.from_dt is null or _r.from_dt <> _r.birth_dt then
38			RAISE NOTICE 'лог: %, pci_patient_part_case.id = %, patient_id = %', 'from_dt null или не равна birth_dt', _r.id, _r.patient_id;
39			INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'from_dt null или не равна birth_dt, pci_patient_part_case.id = '|| _r.id::varchar(20) ||', patient_id = '|| _r.patient_id::varchar(20);
40			select _r.birth_dt into _from_dt;
41			update pci_patient_part_case set from_dt = _from_dt where id = _r.id;
42			RAISE NOTICE 'лог: %, pci_patient_part_case.id = %, patient_id = %', 'from_dt ставим равна birth_dt', _r.id, _r.patient_id;
43			INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'from_dt null или не равна birth_dt, pci_patient_part_case.id = '|| _r.id::varchar(20) ||', patient_id = '|| _r.patient_id::varchar(20);
44			if _r.to_dt is not null and (_r.to_dt - _from_dt) <= 30 then
45				RAISE NOTICE 'лог: %, pci_patient_part_case.id = %, patient_id = %', 'to_dt <= 30 от from_dt', _r.id, _r.patient_id;
46				INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'to_dt <= 30 от from_dt, pci_patient_part_case.id = '|| _r.id::varchar(20) ||', patient_id = '|| _r.patient_id::varchar(20);
47				CONTINUE;
48			else 
49				RAISE NOTICE 'лог: %, pci_patient_part_case.id = %, patient_id = %', 'to_dt ставим равна birth_dt + 30 дней', _r.id, _r.patient_id;
50				INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'to_dt ставим равна birth_dt + 30 дней, pci_patient_part_case.id = '|| _r.id::varchar(20) ||', patient_id = '|| _r.patient_id::varchar(20);
51				update pci_patient_part_case set to_dt = (_from_dt + INTERVAL '30 DAY') where id = _r.id;
52				CONTINUE;
53			end if;
54		else
55			if _r.to_dt is null or _r.to_dt > (_r.birth_dt + INTERVAL '30 DAY') then
56				RAISE NOTICE 'лог: %, pci_patient_part_case.id = %, patient_id = %', 'to_dt ставим равна birth_dt + 30 дней', _r.id, _r.patient_id;
57				INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'to_dt ставим равна birth_dt + 30 дней, pci_patient_part_case.id = '|| _r.id::varchar(20) ||', patient_id = '|| _r.patient_id::varchar(20);
58				update pci_patient_part_case set to_dt = (_r.birth_dt + INTERVAL '30 DAY') where id = _r.id;
59			end if;
60		end if;
61	end if;
62	
63else
64
65	RAISE NOTICE 'лог: %, pci_patient_part_case.id = %, patient_id = %', 'date_diff <= 30', _r.id, _r.patient_id;
66	INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'date_diff <= 30, pci_patient_part_case.id = '|| COALESCE(_r.id::varchar(20), 'null') ||', patient_id = '|| _r.patient_id::varchar(20);	
67	if _r.part_case_id is null then
68		select nextval('pci_patient_part_case_id_seq') into _id; 
69		INSERT INTO pci_patient_part_case (id, from_dt, to_dt, part_case_id, patient_id) select _id, _r.birth_dt, (_r.birth_dt + INTERVAL '30 DAY'), 0, _r.patient_id;
70		RAISE NOTICE 'лог: %, pci_patient_part_case.id = %, patient_id = %', 'добавляем особый случай', _id, _r.patient_id;
71		INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'добавляем особый случай, pci_patient_part_case.id = '|| _id::varchar(20) ||', patient_id = '|| _r.patient_id::varchar(20);
72	else
73		select _r.birth_dt into _from_dt;
74		if _r.from_dt is null or _r.from_dt <> _r.birth_dt then
75			RAISE NOTICE 'лог: %, pci_patient_part_case.id = %, patient_id = %', 'from_dt null или не равна birth_dt', _r.id, _r.patient_id;
76			INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'from_dt null или не равна birth_dt, pci_patient_part_case.id = '|| _r.id::varchar(20) ||', patient_id = '|| _r.patient_id::varchar(20);		
77			update pci_patient_part_case set from_dt = _from_dt where id = _r.id;
78		end if;
79		if _r.to_dt is null or _r.to_dt <> (_from_dt + INTERVAL '30 DAY') then
80			RAISE NOTICE 'лог: %, pci_patient_part_case.id = %, patient_id = %', 'to_dt null или не равна birth_dt + 30', _r.id, _r.patient_id;
81			INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'to_dt null или не равна birth_dt + 30, pci_patient_part_case.id = '|| _r.id::varchar(20) ||', patient_id = '|| _r.patient_id::varchar(20);		
82			update pci_patient_part_case set to_dt = (_from_dt + INTERVAL '30 DAY') where id = _r.id;		
83		end if;		
84	end if;		
85end if;
86end loop;
87
88for _r in (
89select pi.id patient_id, pppc.id, pid.issue_dt
90from pim_individual pi
91join pci_patient pp on pp.id = pi.id
92join pci_patient_part_case pppc on pppc.patient_id = pi.id
93join pci_part_case ppc on ppc.id = pppc.part_case_id and ppc.id in (0,5)
94join pim_individual_doc pid on pid.indiv_id = pi.id
95join pim_doc_type pdt on pdt.id = pid.type_id and pdt.code in ('MHI_OLDER','MHI_TEMP','MHI_UNIFORM')
96where pi.birth_dt is not null
97and pid.is_active = true and (pid.expire_dt is null or pid.expire_dt > current_date)
98and pppc.to_dt >= pid.issue_dt
99)
100
101loop
102INSERT INTO "jenkins"."log_update_patient_part_case" ("log") select 'проставляем дату особого случая, если добавили полис, pci_patient_part_case.id = '|| _r.id::varchar(20) ||', patient_id = '|| _r.patient_id::varchar(20);
103update pci_patient_part_case set to_dt = (_r.issue_dt - INTERVAL '1 DAY') where id = _r.id;
104
105end loop;
106
107end $$;