B9F77VCk

1drop table if exists kaluga_si_usl_tarifn;
2create table kaluga_si_usl_tarifn(
3from_dt text,
4mo_level text,
5is_usl text,
6pos_code text,
7price text,
8to_dt text,
9mo_code text
10);
11
12
13
14kaluga_si_usl_tarifn
15
16
17
18-- добавляем нумерацию
19alter table kaluga_si_usl_tarifn add column id serial;
20-- добавляем столбец для отбора актуальных записей из файла
21alter table kaluga_si_usl_tarifn add column rn integer;
22-- отбираем актуальные записи
23with rasp as (
24select row_number() over(partition by mo_code, pos_code order by to_date(from_dt,'dd.mm.yyyy') desc) as lvl, id from kaluga_si_usl_tarifn) 
25update kaluga_si_usl_tarifn t set rn = rasp.lvl from rasp where t.id = rasp.id;
26-- удаляем старые записи
27delete from kaluga_si_usl_tarifn where rn <> 1;
28-- добавляем колонку для прайса
29alter table kaluga_si_usl_tarifn add column p_id integer;
30-- добавляем колонку поиска (p - позиция, m - модификатор, n - не найдена в бд)
31alter table kaluga_si_usl_tarifn add column search text;
32-- проставляем актуальные прайсы
33with clin as (
34select mc.id, poc.code as oms 
35from md_clinic mc 
36join pim_organization o on o.id = mc.id
37join pim_org_code poc on poc.org_id = o.id and poc.type_id = (select id from pim_code_type where code = 'CODE_OMS')
38where current_date between coalesce(o.from_dt,'-infinity') and coalesce(o.to_dt,'infinity')), 
39_pid as ( 
40select row_number() over(partition by fpl.clinic_id order by fpl.from_dt desc) as rn, fpl.id as pr_id, clin.* 
41from clin
42join fin_price_list fpl on fpl.clinic_id=clin.id and current_date between coalesce(fpl.from_dt,'-infinity') and coalesce(fpl.to_dt,'infinity') and fpl.commentary='для счетов по ОМС'),
43pid as (select oms, pr_id from _pid where rn = 1)
44update kaluga_si_usl_tarifn t set p_id = pid.pr_id from pid where t.mo_code = pid.oms;
45
46
47
48
49
50
51
52
53
54
55
56
57
58do $$ 
59declare 
60	r record;
61	pl_pos fin_pl_position%rowtype;
62	pr_modif fin_price_modifier%rowtype;
63	mod_to_pl fin_modifier_to_pl_pos %rowtype;
64	new_modif integer;
65	new_pos integer;
66begin 
67	for r in 
68		select pos_code,to_date(from_dt,'dd.mm.yyyy') as from_dt,to_date(to_dt,'dd.mm.yyyy') as to_dt,replace(price,',','.')::numeric(10,2) as price,p_id,id from kaluga_si_usl_tarifn where p_id is not null order by p_id
69	loop 
70		-- поиск в позициях
71		select * into pl_pos from fin_pl_position where price_list_id = r.p_id and code = r.pos_code order by from_dt desc limit 1; -- ищем актуальную позицию в прейскуранте по коду и дате
72		if pl_pos.id is not null then -- позиция найдена
73			update kaluga_si_usl_tarifn set search='p' where id = r.id;
74			if r.from_dt = pl_pos.from_dt then -- если дата начала в файле совпадает с датой в прейскуранте
75				if r.price <> pl_pos.price then-- тариф из файла не совпадает с тарифом из прейскуранта
76					update fin_pl_position set price = r.price where id = pl_pos.id; -- обновляем тариф
77				end if;
78				if r.to_dt <> pl_pos.to_dt then -- если даты окончания не совпадают
79					update fin_pl_position set to_dt = r.to_dt where id = pl_pos.id; -- обновляем дату окончания позиции, на дату окончания из файла
80				end if;
81			elsif r.from_dt > pl_pos.from_dt then -- дата начала в файле > даты начала в прейскуранте
82				update fin_pl_position set to_dt = r.from_dt-1 where id = pl_pos.id; -- старую позицию закрываем датой, равной дате начала действия нового тарифа
83				insert into fin_pl_position(id,code,from_dt,name,price,to_dt,price_list_id,cost_item_type_id) 
84				select nextval('fin_pl_position_seq'),pl_pos.code,r.from_dt,pl_pos.name,r.price,r.to_dt,pl_pos.price_list_id,pl_pos.cost_item_type_id; -- создаем новую позицию
85				new_pos  = currval('fin_pl_position_seq');
86				insert into fin_pl_pos_to_clinic_srv(clinic_service_id,pl_position_id,price_list_id) 
87				select clinic_service_id,new_pos,price_list_id from fin_pl_pos_to_clinic_srv where pl_position_id = pl_pos.id; -- копируем услуги в новую позицию
88				insert into fin_modifier_to_pl_pos(id,pl_position_id,price_modifier_id) 
89				select nextval('fin_modifier_to_pl_pos_seq'),new_pos,price_modifier_id from fin_modifier_to_pl_pos where pl_position_id = pl_pos.id; -- копируем модификаторы в новую позицию
90			end if;
91			continue; -- позиция найдена и обработана. Переходим к новой позиции из файла
92	  	end if;
93		-- поиск в модификаторах
94		select fpl.* into pl_pos from fin_pl_position fpl 
95		join fin_modifier_to_pl_pos mtp on fpl.id = mtp.pl_position_id 
96		join fin_price_modifier m on mtp.price_modifier_id = m.id and m.code = r.pos_code and m.price_list_id = r.p_id
97		order by fpl.from_dt desc limit 1; 
98		
99		select m.* into pr_modif from fin_pl_position fpl 
100		join fin_modifier_to_pl_pos mtp on fpl.id = mtp.pl_position_id 
101		join fin_price_modifier m on mtp.price_modifier_id = m.id and m.code = r.pos_code and m.price_list_id = r.p_id
102		order by fpl.from_dt desc limit 1;
103
104		select mtp.* into mod_to_pl from fin_pl_position fpl 
105		join fin_modifier_to_pl_pos mtp on fpl.id = mtp.pl_position_id 
106		join fin_price_modifier m on mtp.price_modifier_id = m.id and m.code = r.pos_code and m.price_list_id = r.p_id
107		order by fpl.from_dt desc limit 1; -- ищем позицию по модификатору с кодом и прейскурантом как в файле
108	
109		if pl_pos.id is not null then -- позиция найдена
110			update kaluga_si_usl_tarifn set search='m' where id = r.id;
111			if r.from_dt = pl_pos.from_dt then -- если дата начала в файле совпадает в прейскурантом
112				if r.price <> pr_modif.value::numeric(10,2) then -- тариф в модификаторе не совпадает с тарифом из файла
113					insert into fin_price_modifier (id,commentary,condition,name,type,value,scope_id,price_list_id,code,apply_order,cost_item_type_id)
114					select nextval('fin_price_modifier_seq'),pr_modif.commentary,pr_modif.condition,pr_modif.name,pr_modif.type,r.price::varchar,
115					pr_modif.scope_id,pr_modif.price_list_id,pr_modif.code,pr_modif.apply_order,pr_modif.cost_item_type_id; -- создаем копию модификатора
116					new_modif = currval('fin_price_modifier_seq');
117					update fin_modifier_to_pl_pos set price_modifier_id = new_modif where id = mod_to_pl.id; -- заменяем старый модикатор позиции на новый (обновляем тариф в модификаторе)
118				end if;
119			elsif r.from_dt > pl_pos.from_dt then 
120				update fin_pl_position set to_dt = r.from_dt-1 where id = pl_pos.id; -- старую позицию закрываем датой, равной дате начала действия нового тарифа
121				insert into fin_pl_position(id,code,from_dt,name,price,to_dt,price_list_id,cost_item_type_id)
122				select nextval('fin_pl_position_seq'),pl_pos.code,r.from_dt,pl_pos.name,pl_pos.price,r.to_dt,pl_pos.price_list_id,pl_pos.cost_item_type_id; -- создаем новую позицию
123				new_pos  = currval('fin_pl_position_seq');
124				insert into fin_pl_pos_to_clinic_srv(clinic_service_id,pl_position_id,price_list_id) 
125				select clinic_service_id,new_pos,price_list_id from fin_pl_pos_to_clinic_srv where pl_position_id = pl_pos.id; -- копируем услуги в новую позицию
126				if r.price <> pr_modif.value::numeric(10,2) then -- тариф модификатора и тариф из файла не совпадают
127					insert into fin_price_modifier (id,commentary,condition,name,type,value,scope_id,price_list_id,code,apply_order,cost_item_type_id)
128					select nextval('fin_price_modifier_seq'),pr_modif.commentary,pr_modif.condition,pr_modif.name,pr_modif.type,r.price::varchar,
129					pr_modif.scope_id,pr_modif.price_list_id,pr_modif.code,pr_modif.apply_order,pr_modif.cost_item_type_id; -- создаем копию модификатора с новым тарифом
130					new_modif = currval('fin_price_modifier_seq');
131					insert into fin_modifier_to_pl_pos(id,pl_position_id,price_modifier_id) 
132					select nextval('fin_modifier_to_pl_pos_seq'),new_pos,new_modif; -- привязываем новый модификатор к новой позиции
133					insert into fin_modifier_to_pl_pos(id,pl_position_id,price_modifier_id) 
134					select nextval('fin_modifier_to_pl_pos_seq'),new_pos,price_modifier_id 
135					from fin_modifier_to_pl_pos where pl_position_id = pl_pos.id and id <> mod_to_pl.id; -- копируем все модификаторы в новую позицию, кроме старого который уже привязали
136				else 
137					insert into fin_modifier_to_pl_pos(id,pl_position_id,price_modifier_id) 
138					select nextval('fin_modifier_to_pl_pos_seq'),new_pos,price_modifier_id from fin_modifier_to_pl_pos where pl_position_id = pl_pos.id; -- тариф модиф совпал с файлом, копируем модификаторы
139				end if;
140			end if;
141			continue;
142		end if;
143		update kaluga_si_usl_tarifn set search='n' where id = r.id;
144	end loop;
145end $$