/*2025.18*/ create or alter procedure SP_CALC_ALL ( ZS_ID integer) as declare variable TAR DM_MONEY_15_2; declare variable DATE1 DM_DATE; declare variable Q_U integer; declare variable TAR_USL DM_MONEY_15_2; declare variable TARGET integer; declare variable IDB integer; declare variable SUM_USLF DM_MONEY_15_2; declare variable SEX char(1); declare variable PTYPE integer; declare variable AGE_Y integer; declare variable AGE_GRP integer; declare variable HDR_ID integer; declare variable TROMB_CODE DM_CHAR_20; declare variable TAR_USL_DOP DM_MONEY_15_2; declare variable SUM_USLF_DOP DM_MONEY_15_2; declare variable IDSP integer; declare variable MMB DM_NUM_3_2; declare variable OKATO DM_OKATO; declare variable P_CEL DM_CHAR_10; declare variable CODE_USL DM_CHAR_20; declare variable AGE_DATE2 DM_INT; declare variable LEK_NAME DM_CHAR_250; declare variable LEK_COL integer; declare variable LEK_TAR DM_MONEY_15_2; declare variable LEK_SUM DM_MONEY_15_2; declare variable LEK_COMMENT DM_CHAR_250; declare variable PROFIL_T DM_CHAR_10; declare variable TARGET_TARIF DM_INT; declare variable SHRM DM_INT; declare variable MKB DM_DS; declare variable IS_CHIELD DM_BOOL; declare variable STAGE DM_INT; declare variable AI DM_INT; declare variable SRVPLACE DM_INT; begin if ((select coalesce(is_close,0) from zs where id = :zs_id) = 1) then exit; select z.date_2, z.q_u, z.target, p.sex, z.payment_type_id, extract(year from z.date_2) - extract(year from p.dr), z.hdr_id, iif(coalesce(z.vbr,0) = 0, 1, 1.2), z.idsp, iif(dateadd(year, extract(year from z.date_2) - extract(year from p.dr), p.dr) > z.date_2, extract(year from z.date_2) - extract(year from p.dr) - 1, extract(year from z.date_2) - extract(year from p.dr)), z.disp_stage from zs z join patients p on p.id = z.patient_id where z.id = :zs_id into :date1, :q_u, :target, :sex, :ptype, :age_y, :hdr_id, :mmb, :idsp, age_date2, :stage; /***проставим УЕТы***/ -- if (:q_u = 4) then -- begin -- update zsd d -- set d.k_uslf = (select first 1 iif(:age_date2>=18,uet,chield_uet) from h_usl u where u.code = d.code_usl and u.q_u containing '4' -- and u.PAYMENT_TYPE_LIST containing '1' and u.target containing '"1"' and d.date_2 between u.datebeg and u.dateend)*coalesce(d.dcount,1), -- d.dcount = coalesce(d.dcount,1) -- where d.zs_id = :zs_id and ((coalesce(d.dcount,0)=0) or (coalesce(d.k_uslf,0)=0)) and coalesce(d.code_usl,'') <> ''; -- end execute procedure SP_REESTR_EXTRACT_FOR_V2020(:ZS_ID); if (:ptype > 1) then exit; if (:date1 is null) then date1 = current_date; update zs z set z.os_sluch = null where z.id = :zs_id; if (:mmb = 1) then begin mmb = iif((select first 1 z.id from zs z join kalendar k on k.k_day = z.date_1 where z.id = :zs_id and z.disp_stage = 1 and z.target in (9,16) and z.date_1 = z.date_2 and k.day_type in (1,2)) is null, 1, 1.2); if ((:mmb > 1) and (:target in (9,16))) then update zs z set z.os_sluch = 6 where z.id = :zs_id; end okato = (select r.tariff_okato from contragents c join contragents_regions r on r.contragent_id = c.id where r.code = (select lpu from invoice i join zs_hdr h on h.name = i.id where h.id = :hdr_id)); if (:okato is null) then okato = (select first 1 c.tf_okato from contragents c where c.org_code = (select lpu from invoice i join zs_hdr h on h.name = i.id where h.id = :hdr_id)); if (:okato is null) then okato = (select first 1 c.tf_okato from contragents c where c.type_id = 1); if (:okato is null) then okato = '71140'; if (:ptype in (2,3)) then exit; if (:target in (9,10,11,12,13,14,15,16,17)) then begin if ((select coalesce(t.days_cnt,0) from h_target t where t.id = :target) <> 0) then begin update zsd d set d.zachet = 1 where d.date_1 < (:date1 - (select t.days_cnt from h_target t where t.id = :target)) and d.zs_id = :zs_id; update zsd d set d.zachet = 0 where (d.date_1 >= (:date1 - (select t.days_cnt from h_target t where t.id = :target)) ) and d.zs_id = :zs_id; end update zsd d set d.zachet = 0 where (d.code_usl in ('A02.07.004', 'A01.30.026', 'A02.12.002', 'B04.070.002')) and d.zs_id = :zs_id; update zsd d set d.k_uslf = 0 where (d.zachet = 1 or d.is_no_get_usl = 1 or d.zachet_stac = 1) and d.zs_id = :zs_id; update zsd d set d.k_uslf = 1 where (d.k_uslf = 0) and (coalesce(d.zachet,0) = 0 and coalesce(d.is_no_get_usl,0) = 0 and coalesce(d.zachet_stac,0) = 0) and d.zs_id = :zs_id; end update zsd d set d.tar_usl = null, d.sum_uslf = null, d.comment_local = null, d.dop_tarif = null, d.dop_sum = null, d.ksg = null, d.tar_base = null, d.sum_base = null, d.kskp_base = null, d.kskp = null, d.koef_up = null where d.zs_id = :zs_id; update zs z set z.tar_usl = null, z.sum_usl = null, z.sum_uslf = null where z.id = :zs_id; if ((select coalesce(i.is_nz,0) from invoice i join zs_hdr h on h.name = i.id where h.id = :hdr_id) = 1) then begin if (target in (9,10,11,13,14,15,16,17)) then exit; tar = (select first 1 s.tarif from h_tarif_nz s where s.q_u = iif(:q_u = 4,2,:q_u) and :date1 between s.bp and s.epv); if (q_u = 1) then update zsd d set d.tar_usl = :tar, d.sum_uslf =:tar where d.zs_id = :zs_id and d.code_usl is null; else update zsd d set d.tar_usl = :tar, d.sum_uslf =:tar where d.zs_id = :zs_id; if (q_u = 4) then update zs z set z.tar_usl = :tar, z.sum_uslf = z.dcount*:tar where id = :zs_id; else if (q_u = 1) then update zs z set z.tar_usl = :tar, z.sum_uslf = :tar where id = :zs_id; else update zs z set z.tar_usl = :tar, z.sum_uslf = (select sum(sum_uslf) from zsd where zs_id = :zs_id) where id = :zs_id; exit; end /********************Поликлиника, без диспансеризации и профов************************/ ----------------------------------------------------------------------------------------------------- if (:q_u = 2 and :target not in (9,10,11,12,13,14,15,16,17,18,20) and (:date1 >= '01.01.2020')) then begin for select b.id from zsd b where b.zs_id = :zs_id order by b.date_1 into :idb do begin --Ищем в справочнике услуг сумму за услугу, если она больше 0 select first 1 u.sumf, coalesce(u.sumf,0)*b.k_uslf, b.code_usl, b.p_cel, b.shrm, b.ds, b.is_chield from zs a join zsd b on a.id = b.zs_id join h_usl u on u.code = coalesce(b.code_usl,'') and u.q_u containing a.q_u and (u.target is null or (u.target containing a.target)) and u.datebeg <= a.date_2 and u.dateend >=a.date_2 and u.payment_type_id = a.payment_type_id and ((coalesce(u.okato,'') = :okato) or (coalesce(u.okato,'') = '')) where b.id = :idb and b.code_usl is not null and coalesce(u.sumf,0) > 0 order by u.okato desc into :tar, :sum_uslf, :code_usl, :p_cel, :shrm, :mkb, :is_chield; if (okato = '28000') then --Тверская обл. считать по полю вида вмешательства select first 1 u.sumf, coalesce(u.sumf,0)*b.k_uslf, b.code_usl from zs a join zsd b on a.id = b.zs_id join h_usl u on u.code = coalesce(b.vid_vme,'') and u.q_u containing a.q_u and (u.target is null or (u.target containing a.target)) and u.datebeg <= a.date_2 and u.dateend >=a.date_2 and u.payment_type_id = 1 where b.id = :idb and b.code_usl is not null and coalesce(u.sumf,0) > 0 into :tar, :sum_uslf, :code_usl; tar_usl_dop = null; sum_uslf_dop = null; --Доп. тарифы за доставку диализных больных if ((select d.dop_code_usl from zsd d where d.id = :idb) is not null) then select first 1 u.sumf, coalesce(u.sumf,0)*b.k_uslf from zs a join zsd b on a.id = b.zs_id join h_usl u on u.code = coalesce(b.dop_code_usl,'') and u.q_u containing a.q_u and (u.target is null or (u.target containing a.target)) and u.datebeg <= a.date_2 and u.dateend >=a.date_2 and u.payment_type_id = 1 and u.finans_type is not null where b.id = :idb and b.dop_code_usl is not null and coalesce(u.sumf,0) > 0 into :tar_usl_dop, :sum_uslf_dop; srvplace = 0; --Добываем нужные в дальнейшем переменные select b.code_usl, b.p_cel, b.shrm, b.ds, b.is_chield, a.srvplace from zs a join zsd b on a.id = b.zs_id where b.id = :idb into :code_usl, :p_cel, :shrm, :mkb, :is_chield, :srvplace; --Вычисляем комплексный тип тарифа для расчёта далее target_tarif = case when :p_cel = '1.4' and :code_usl = 'B04.012.001.097' and (:okato = '71140') then 18 --школа сахарного диабета 1 типа взрослые when :p_cel = '1.4' and :code_usl = 'B04.012.001.098' and (:okato = '71140') then 19 --школа сахарного диабета 2 типа взрослые when :p_cel = '1.4' and :code_usl = 'B04.012.001.099' and (:okato = '71140') then 20 --школа сахарного диабета дети when :p_cel = '1.9' and :code_usl in ('B04.057.003','B04.015.001','B04.015.002','B04.025.001', 'B04.037.003','B04.040.001','B04.058.001','B04.023.004','B04.023.005','B04.023.006', 'B04.025.003','B04.025.004','B04.023.003','B04.004.003','B04.015.006','B04.037.004', 'B04.070.007','B05.069.008') then 25 --школа ХНЗ when (:p_cel in ('1.0','1.2','2.3','2.5','3.1','2.6')) or ((:p_cel = '1.3') and (:age_date2 < 18)) then 0 --посещение when :p_cel = '1.1' then 1 -- посещение по неотложной помощи when :p_cel = '3.0' and coalesce(:shrm,0) = 0 then 2 -- обращение по заболеванию when (coalesce(:shrm,0) > 0) then (select first 1 tt.id from h_target_tarif tt where tt.shrm = coalesce(:shrm,0) and tt.is_child = coalesce(:is_chield,0) and tt.code = (select first 1 iif(coalesce(:is_chield,0)=0,mkb.h_target_tarif_code,mkb.h_target_tarif_code_det) from h_mkb mkb where mkb.code = :mkb) ) -- реабилитация ЯНАО when ((:p_cel in ('1.3','4.1','4.2')) and (:age_date2 >= 18) and (:srvplace in (8,9))) then 24 --диспансерное наблюдение по месту работы/учебы when ((:p_cel in ('1.3','4.1','4.2')) and (:age_date2 >= 18) and ((:mkb starting 'C') or (:mkb starting 'D0'))) then 21 --диспансерное наблюдение взрослые онкология when ((:p_cel in ('1.3','4.1','4.2')) and (:age_date2 >= 18) and ((:mkb >= 'E10') and (:mkb < 'E12'))) then 22 --диспансерное наблюдение взрослые сахарный диабет when ((:p_cel in ('1.3','4.1','4.2')) and (:age_date2 >= 18) and ((:mkb starting 'I0') or (:mkb starting 'I1') or (:mkb starting 'I28') or (:mkb starting 'I71') or (:mkb starting 'E78') or (:mkb starting 'Q2') or ((:mkb >= 'I20.0') and (:mkb <= 'I26.9')) or ((:mkb >= 'I33.0') and (:mkb <= 'I42.9')) or ((:mkb >= 'I44.0') and (:mkb <= 'I51.2')) or ((:mkb >= 'I69.0') and (:mkb <= 'I69.4')) or ((:mkb >= 'Z95.0') and (:mkb <= 'Z95.5')) or (:mkb in ('I51.4','I65.2','I67.8','Z95.8','Z95.9','I27.0','I27.2','I27.8')) )) then 23 --диспансерное наблюдение болезни системы кровообращения when ((:p_cel in ('1.3','4.1','4.2')) and (:age_date2 >= 18) and ((:mkb in ('B18.0', 'B18.1', 'B18.2', 'D12.6', 'D12.8', 'D13.4', 'D13.7', 'D29.1', 'D30.0', 'D30.3', 'D30.4', 'D35.0', 'D35.1', 'D35.2', 'D35.8', 'D37.6', 'D39.1', 'D41.0', 'E04.1', 'E04.2', 'E05.1', 'E05.2', 'E21.0', 'E22.0', 'E28.2', 'E34.5', 'E34.8', 'J38.1', 'J41.0', 'J41.1', 'J41.8', 'J44.0', 'J44.8', 'J44.9', 'J45.0', 'J45.1', 'J45.8', 'J45.9', 'J47.0', 'J84.1', 'K13.0', 'K13.2', 'K21.0', 'K22.0', 'K22.2', 'K22.7', 'K29.4', 'K29.5', 'K31.7', 'K62.1', 'K70.3', 'K74.3', 'K74.4', 'K74.5', 'K74.6', 'L57.1', 'M81.5', 'N18.1', 'N18.9', 'N48.0', 'N85.0', 'N85.1', 'N87.1', 'N87.2', 'N88.0', 'R73.0', 'R73.9', 'Q78.1', 'Q78.1', 'Q78.4', 'Q82.1', 'Q82.5', 'Q85.1')) or (:mkb starting 'D10') or (:mkb starting 'D11') or (:mkb starting 'D14') or (:mkb starting 'D16') or (:mkb starting 'D22') or (:mkb starting 'D23') or (:mkb starting 'D24') or (:mkb starting 'D31') or (:mkb starting 'J12') or (:mkb starting 'J13') or (:mkb starting 'J14') or (:mkb starting 'J31') or (:mkb starting 'J33') or (:mkb starting 'J37') or (:mkb starting 'K20') or (:mkb starting 'K25') or (:mkb starting 'K26') or (:mkb starting 'K50') or (:mkb starting 'K51') or (:mkb starting 'K86') or (:mkb starting 'L43') or (:mkb starting 'L82') or (:mkb starting 'M85') or (:mkb starting 'M88') or (:mkb starting 'M96') or (:mkb starting 'N60') or (:mkb starting 'N84') or ((:mkb >= 'B20.0') and (:mkb <= 'B24.9')))) then 17 --диспансерное наблюдение взрослые else -1 end; if (:tar is null) then /*if (okato = '28000') then select first 1 h.tarif, h.tarif, b.p_cel from zs a join zsd b on a.id = b.zs_id join h_usl u on u.code = coalesce(b.code_usl,'') and u.q_u containing a.q_u and (u.target is null or (u.target containing a.target)) and u.datebeg <= a.date_2 and u.dateend >=a.date_2 and u.payment_type_id = a.payment_type_id join h_tarif_app_tgroup t on a.date_2 between t.bp and t.epv left join h_tarif_app h on a.date_2 between h.bp and h.epv and (h.target = iif(b.p_cel in ('1.0','1.2','1.3','2.3','2.5','1.4','3.1') , 0, iif(b.p_cel = '1.1', 1, iif(b.p_cel = '3.0',2,-1)))) and ((b.p_cel <> '1.1' and h.name_id = ((cast(u.code_2 as integer)))) or (b.p_cel = '1.1' and b.mp = h.idsp)) where b.id = :idb order by h.target nulls last into :tar, :sum_uslf, :p_cel; else */ select first 1 h.tarif, h.tarif, b.p_cel from zs a join zsd b on a.id = b.zs_id join h_tarif_app_tgroup t on a.date_2 between t.bp and t.epv left join h_tarif_app h on a.date_2 between h.bp and h.epv and coalesce(h.is_child,0) = iif(:okato = '40000', coalesce(b.is_chield,0),0) and (coalesce(h.okato,'71140') = :okato) and (h.target = :target_tarif) left join h_tarif_app_spec n on n.name_id = h.name_id and n.code_spec_v021 = iif(b.code_spec_v021 = 39 and a.age < 18, 49, b.code_spec_v021) where b.id = :idb order by h.target nulls last into :tar, :sum_uslf, :p_cel; if ((:p_cel in ('4.1','4.2')) and (:okato = '71140')) then begin tar = cast(:tar*1.0264 as int); sum_uslf = cast(:sum_uslf*1.0264 as int); end profil_t = null; if ((:okato = '71140') or (:okato = '40000') or (:okato = '87000') or (:okato = '27000') or (:okato = '86000') or (:okato = '11000') or (:okato = '24000') or (:okato = '17000')) then update ZSD b set b.tar_usl = iif((:target_tarif >= 2 and (:idsp<>28)) /*$$IBEC$$ or ((:idsp = 28) and (:code_usl not starting 'A04') and (:code_usl not starting 'A08') and (:code_usl not starting 'A05') and (:code_usl not starting 'A06')) $$IBEC$$*/ , 0, coalesce(:tar, 0)), b.sum_uslf = iif(:mmb = 1, cast(iif((:target_tarif >= 2 and (:idsp<>28)) /*$$IBEC$$ or ((:idsp = 28) and (:code_usl not starting 'A04') and (:code_usl not starting 'A08') and (:code_usl not starting 'A05') and (:code_usl not starting 'A06')) $$IBEC$$*/, 0, coalesce(:sum_uslf, 0)) as numeric(15,2)), cast(iif((:target_tarif >= 2 and (:idsp<>28)) /*$$IBEC$$ or ((:idsp = 28) and (:code_usl not starting 'A04') and (:code_usl not starting 'A08') and (:code_usl not starting 'A05') and (:code_usl not starting 'A06')) $$IBEC$$*/, 0, coalesce(:sum_uslf, 0))*:mmb as integer)), b.dop_tarif = coalesce(:tar_usl_dop,0), b.dop_sum = coalesce(:sum_uslf_dop,0) where b.id = :idb; else update ZSD b set b.tar_usl = coalesce(:tar, 0), b.sum_uslf = coalesce(:sum_uslf, 0), b.dop_tarif = coalesce(:tar_usl_dop,0), b.dop_sum = coalesce(:sum_uslf_dop,0) where b.id = :idb; if (((:target_tarif < 2) and (:idsp <> 28)) /*$$IBEC$$ or ((:okato = '17000') and (coalesce(:tar, 0) > 0)) $$IBEC$$*/) then begin tar = null; sum_uslf = null; end end if (((:target_tarif >= 2 and (:idsp<>28)) /*$$IBEC$$ or ((:idsp = 28) and (:code_usl not starting 'A04') and (:code_usl not starting 'A08') and (:code_usl not starting 'A05') and (:code_usl not starting 'A06')) $$IBEC$$*/) and ((:okato = '71140') or (:okato = '40000') or (:okato = '87000') or (:okato = '27000') or (:okato = '86000') or (:okato = '11000') or (:okato = '24000') or (:okato = '24000')/*$$IBEC$$ or (:okato = '17000') $$IBEC$$*/)) then begin update ZSD b set b.tar_usl = coalesce(:tar, 0), b.sum_uslf = iif(:mmb = 1, cast(coalesce(:sum_uslf, 0)*:mmb as numeric(15,2)), cast(coalesce(:sum_uslf, 0)*:mmb as integer)) where b.zsd_id = b.id and b.zs_id = :zs_id; --b.id = :idb; end if ((:target_tarif >= 2) and (:idsp<>28) and (:okato = '17000')) then begin update ZSD b set b.tar_usl = coalesce(:tar, 0), b.sum_uslf = iif(:mmb = 1, cast(coalesce(:sum_uslf, 0)*:mmb as numeric(15,2)), cast(coalesce(:sum_uslf, 0)*:mmb as integer)) where b.id = :idb; end if ((:idsp = 28) and (:okato = '71140') and ((:code_usl starting 'A05') or (:code_usl starting 'A06') or (:code_usl starting 'A08') or (:code_usl starting 'A26'))) then update ZSD b set b.tar_usl = 0, b.sum_uslf = 0 where b.zs_id = :zs_id and b.id <> (select first 1 id from zsd where zs_id = b.zs_id order by tar_usl desc); sum_uslf = (select sum(b.sum_uslf) from zsd b where b.zs_id = :zs_id) + (select sum(coalesce(b.dop_sum,0)) from zsd b where b.zs_id = :zs_id); update zs a set a.tar_usl = coalesce((select sum(d.tar_usl) from zsd d where d.zs_id = a.id), 0), a.sum_uslf = coalesce(:sum_uslf, 0) where a.id = :zs_id; if ((coalesce((select z.set_usl_auto from zs z where z.id = :zs_id),0) = 1) and (:okato = '71140')) then execute procedure SP_SET_USL_ZSD(:ZS_ID); exit; end /********************Стоматология************************/ if ((:q_u = 4) and (:okato <> '28000')) then begin tar = (select s.tarif from h_tarif_stom s where :date1 between s.bp and s.epv); update zsd d set d.tar_usl = :tar, d.sum_uslf = cast(d.k_uslf*:tar*:mmb as numeric(15,2)) where d.zs_id = :zs_id; update zs z set z.tar_usl = :tar, z.sum_uslf = (select sum(sum_uslf) from zsd where zs_id = :zs_id) where id = :zs_id; end if ((:q_u = 4) and (:okato = '68000')) then begin tar = (select s.tarif from h_tarif_stom s where :date1 between s.bp and s.epv); update zsd d set d.tar_usl = :tar, d.sum_uslf = d.k_uslf*:tar where d.zs_id = :zs_id; update zs z set z.tar_usl = :tar, z.sum_uslf = (select sum(k_uslf)*:tar from zsd where zs_id = :zs_id) where id = :zs_id; end if ((:q_u = 4) and (:okato = '28000')) then begin select first 1 h.tarif, h.tarif, b.p_cel from zs a join zsd b on a.id = b.zs_id join h_usl u on u.code = coalesce(b.code_usl,'') and u.q_u containing a.q_u and (u.target is null or (u.target containing a.target)) and u.datebeg <= a.date_2 and u.dateend >=a.date_2 and u.payment_type_id = a.payment_type_id join h_tarif_app_tgroup t on a.date_2 between t.bp and t.epv left join h_tarif_app h on a.date_2 between h.bp and h.epv and (h.target = iif(b.p_cel in ('1.0','1.2','1.3','2.3','2.5','2.6','3.1') , 0, iif(b.p_cel = '1.1', 1, iif(b.p_cel = '3.0',2,-1)))) and ((b.p_cel <> '1.1' and h.name_id = ((cast(u.code_2 as integer)))) or (b.p_cel = '1.1' and b.mp = h.idsp)) where b.zs_id = :zs_id order by h.target nulls last into :tar, :sum_uslf, :p_cel; update zsd d set d.tar_usl = null, d.sum_uslf = null where d.zs_id = :zs_id; update zs z set z.tar_usl = :tar, z.sum_uslf = :tar*iif(:p_cel = '3.0',z.k_uslf/9.4,z.k_uslf/4) where id = :zs_id; end /********************Скорая помощь************************/ if (:q_u = 5) then begin -- if (:date1 >= '01.01.2018') then tromb_code = 'A25.30.036'; -- else tromb_code = 'A25.30.035'; tromb_code = coalesce((select first 1 code_usl from zsd d where d.zs_id = :zs_id and coalesce(code_usl, '') <> ''),''); tar = (select s.tarif from h_tarif_smp s where :date1 between s.bp and s.epv); tar_usl = coalesce((select coalesce(u.sumf,0) from h_usl u where u.code = :tromb_code and u.q_u containing '5' and :date1 between u.datebeg and u.dateend),0); update zsd d set d.tar_usl = iif(coalesce(d.code_usl,'') = '', :tar, :tar_usl), d.sum_uslf = d.k_uslf*iif(coalesce(d.code_usl,'') = '', :tar, :tar_usl) where d.zs_id = :zs_id; update zs z set z.tar_usl = (select max(tar_usl) from zsd where zs_id = :zs_id), z.sum_uslf = (select sum(sum_uslf) from zsd where zs_id = :zs_id) where id = :zs_id; end /********************Стационары ВМП************************/ if ((:q_u IN (1,3)) and ((select first 1 1 from zsd d where coalesce(d.metod_hmp,0) > 0 and zs_id = :zs_id) = 1)) then begin select first 1 v.tarif from zs z join zsd d on d.zs_id = z.id join h_tarif_vmp v on v.id = d.vmp_tarif_id where z.id = :zs_id and v.okato = :okato into :tar; update zs a set a.tar_usl = :tar, a.sum_uslf = :tar, a.ksg = null, a.kslp = null where a.id = :zs_id; update zsd b set b.ksg = null, b.ksg_base = null, b.tar_base = :tar, b.sum_base = :tar, b.kskp_base = null, b.sum_uslf = :tar, b.tar_usl = :tar, b.comment_local = null, b.kskp = null, b.koef_up = null, b.formula_tarif = 'Расчёт по ВМП: тариф = ' || :tar, b.formula_sum = 'Расчёт по ВМП: тариф = ' || :tar, b.bs = null, b.koef_z = null, b.koef_d = null, b.koef_u = null where b.zs_id = :zs_id and b.date_1 = (select min(date_1) from zsd where zs_id = :zs_id) and coalesce(b.metod_hmp,0) > 0; exit; end if (:q_u IN (1,3) and (:okato = '45000')) then begin select first 1 u.sumf from zs z join zsd d on d.zs_id = z.id join h_usl u on u.code = d.code_usl and u.q_u containing :q_u and u.payment_type_list containing '1' and u.target containing '"1"' and d.date_2 between u.datebeg and u.dateend where z.id = :zs_id and u.okato = :okato into :tar; lek_sum = 0; lek_tar = null; lek_col = 0; lek_name = ''; lek_comment = ''; if ((select first 1 1 from zs z where z.id = :zs_id and (z.ds1 starting 'C50' or (z.ds1 starting 'C61') or (z.ds1 starting 'C64') or (z.ds1 starting 'C34') or (z.ds1 starting 'C16') or (z.ds1 starting 'C56') or (z.ds1 starting 'C67') or (z.ds1 starting 'C19') or (z.ds1 starting 'C20') or (z.ds1 starting 'C34') or (z.ds1 starting 'C0') or (z.ds1 starting 'C10') or (z.ds1 starting 'C11') or (z.ds1 starting 'CC12') or (z.ds1 starting 'C13') or (z.ds1 starting 'C14'))) = 1) then for select t.sumf, t.name, sum(pr.col_lek_pr) from zs z join zsd d on d.zs_id = z.id join onk_usl usl on usl.zsd_id = d.id join onk_lek_pr pr on pr.onk_usl_id = usl.id join h_tarif_lek_pr t on t.id = pr.tarif_lek_pr_id where z.id = :zs_id and t.okato = :okato group by 1,2 into :lek_tar, :lek_name, :lek_col do begin lek_sum = :lek_sum + :lek_tar*:lek_col; lek_comment = :lek_comment || :lek_name || '(' || :lek_tar || ')*Кол-во(' || :lek_col || ') +'; end update zs a set a.tar_usl = :tar + :lek_sum, a.sum_uslf = :tar + :lek_sum, a.ksg = null, a.kslp = null where a.id = :zs_id; update zsd b set b.ksg = null, b.ksg_base = null, b.tar_base = :tar + :lek_sum, b.sum_base = :tar + :lek_sum, b.kskp_base = null, b.sum_uslf = :tar + :lek_sum, b.tar_usl = :tar + :lek_sum, b.comment_local = null, b.kskp = null, b.koef_up = null, b.formula_tarif = 'Тариф на законченный случай лечения (' || :tar || ')+Оплата лекарственных препаратов(' || :lek_comment || ')', b.formula_sum = :tar + :lek_sum, b.bs = null, b.koef_z = null, b.koef_d = null, b.koef_u = null where b.zs_id = :zs_id and b.date_1 = (select min(date_1) from zsd where zs_id = :zs_id); exit; end /********************Стационары************************/ if (:q_u in (1,3) and :date1 >= '01.01.2020') then execute procedure sp_calc_stac_2020(:zs_id); if ((:okato = '75000') and (:date1 >= '01.07.2022')) then begin for --Доставка диализных больных - доп. тариф. select b.id from zsd b where b.zs_id = :zs_id into :idb do begin tar_usl_dop = null; sum_uslf_dop = null; if ((select d.dop_code_usl from zsd d where d.id = :idb) is not null) then select first 1 u.sumf, coalesce(u.sumf,0)*b.k_uslf from zs a join zsd b on a.id = b.zs_id join h_usl u on u.code = coalesce(b.dop_code_usl,'') and u.q_u containing a.q_u and (u.target is null or (u.target containing a.target)) and u.datebeg <= a.date_2 and u.dateend >=a.date_2 and u.payment_type_id = 1 and u.finans_type is not null where b.id = :idb and b.dop_code_usl is not null and coalesce(u.sumf,0) > 0 into :tar_usl_dop, :sum_uslf_dop; if (coalesce(:tar_usl_dop,0) > 0) then update ZSD b set b.dop_tarif = coalesce(:tar_usl_dop,0), b.dop_sum = coalesce(:sum_uslf_dop,0) where b.id = :idb; tar_usl_dop = null; sum_uslf_dop = null; sum_uslf = (select sum(b.sum_uslf) + sum(coalesce(b.dop_sum,0)) from zsd b where b.zs_id = :zs_id); tar = (select sum(b.tar_usl) + sum(coalesce(b.dop_tarif,0)) from zsd b where b.zs_id = :zs_id); update zs a set a.tar_usl = coalesce(:tar, 0), a.sum_uslf = coalesce(:sum_uslf, 0) where a.id = :zs_id; end if ((:date1 < '01.01.2023') and (:date1 >= '01.07.2022')) then exit; end -- end /****************Расчёт по услугам***********************************************/ if ((q_u <> 4) and not((:target in (9,10,11,12,13,14,15,16,17,20)) and (:stage = 1))) then begin tar = null; sum_uslf = null; for select b.id from zsd b where b.zs_id = :zs_id into :idb do begin select first 1 u.sumf, coalesce(u.sumf,0)*b.k_uslf, coalesce(b.ai,0) from zs a join zsd b on a.id = b.zs_id join h_usl u on u.code = coalesce(b.code_usl,'') and u.q_u containing a.q_u and (u.target is null or (u.target containing a.target)) and u.datebeg <= a.date_2 and u.dateend >=a.date_2 and u.payment_type_id = 1 where b.id = :idb and b.code_usl is not null and coalesce(u.sumf,0) > 0 into :tar, :sum_uslf, :ai; /*$$IBEC$$ Для услуг с искусственным интеллектом (со звёздочкой) $$IBEC$$*/ if ((target = 18) and (:ai = 1)) then select first 1 u.sumf, coalesce(u.sumf,0)*b.k_uslf, coalesce(b.ai,0) from zs a join zsd b on a.id = b.zs_id join h_usl u on u.code = coalesce(b.code_usl,'') || '*' and u.q_u containing a.q_u and (u.target is null or (u.target containing a.target)) and u.datebeg <= a.date_2 and u.dateend >=a.date_2 and u.payment_type_id = 1 where b.id = :idb and b.code_usl is not null and coalesce(u.sumf,0) > 0 into :tar, :sum_uslf, :ai; tar_usl_dop = null; sum_uslf_dop = null; if ((select d.dop_code_usl from zsd d where d.id = :idb) is not null) then select first 1 u.sumf, coalesce(u.sumf,0)*b.k_uslf from zs a join zsd b on a.id = b.zs_id join h_usl u on u.code = coalesce(b.dop_code_usl,'') and u.q_u containing a.q_u and (u.target is null or (u.target containing a.target)) and u.datebeg <= a.date_2 and u.dateend >=a.date_2 and u.payment_type_id = 1 and u.finans_type is not null where b.id = :idb and b.dop_code_usl is not null and coalesce(u.sumf,0) > 0 into :tar_usl_dop, :sum_uslf_dop; if (coalesce(:tar,0) > 0) then update ZSD b set b.tar_usl = coalesce(:tar, 0), b.sum_uslf = coalesce(:sum_uslf, 0), b.dop_tarif = coalesce(:tar_usl_dop,0), b.dop_sum = coalesce(:sum_uslf_dop,0) where b.id = :idb; tar = null; sum_uslf = null; end if (target = 18) then begin for select p.code_usl from h_usl_prof p where p.target = 18 and :date1 between p.datebeg and p.epv and coalesce(p.is_complex,0) = 1 into :code_usl do if (not exists(select first 1 id from zsd d where d.zs_id = :zs_id and d.code_usl = :code_usl)) then update zsd b set b.tar_usl = 0, b.sum_uslf = 0 where b.zs_id = :zs_id and exists(select first 1 p1.id from h_usl_prof p1 where p1.target = 18 and :date1 between p1.datebeg and p1.epv and coalesce(p1.is_complex,0) = 1 and p1.code_usl = b.code_usl); update zsd d set d.tar_usl = 0, d.sum_uslf = 0 where d.zs_id = :zs_id and coalesce(d.zachet,0) = 1; end sum_uslf = (select sum(b.sum_uslf) from zsd b where b.zs_id = :zs_id) + (select sum(coalesce(b.dop_sum,0)) from zsd b where b.zs_id = :zs_id); tar = (select sum(b.tar_usl) from zsd b where b.zs_id = :zs_id) + (select sum(coalesce(b.dop_tarif,0)) from zsd b where b.zs_id = :zs_id); if (:sum_uslf is not null) then update zs a set a.tar_usl = coalesce(:tar, 0), a.sum_uslf = coalesce(:sum_uslf, 0) where a.id = :zs_id; end /********************Поликлиника - 9 диспансеризация взрослых************************/ if ((:q_u = 2 and :target = 9) or (q_u = 2 and :target = 16 and date1 >= '01.05.2019')) then begin execute procedure sp_calc_app_t09(:zs_id); exit; end /********************Поликлиника - 10,17 диспансеризация детей-сирот и усыновленных************************/ if (q_u = 2 and target in (10,17)) then begin for select b.id from zsd b join zs z on z.id = b.zs_id where z.id = :zs_id into :idb do begin tar = (select d.tarif from zs a join zsd b on b.zs_id = a.id and b.date_1 between a.date_1 and a.date_2 and coalesce(b.is_no_get_usl,0) <> 1 and b.date_1 >= a.date_1 --без учета переазчетов и отказов join h_tarif_disp_10 d on d.stage = a.disp_stage and d.sex = :sex and a.date_2 between d.bp and d.epv and cast(d.usl_code as varchar(20)) = iif(coalesce(b.ai,0) = 1, b.code_usl || '*', b.code_usl) and :age_date2 between d.y1 and d.y2 where b.id = :idb); update zsd b set b.sum_uslf = cast(coalesce(b.k_uslf * :tar, 0)*:mmb as int), b.tar_base = cast(coalesce(:tar, 0)*:mmb as int), b.tar_usl = cast(coalesce(:tar, 0)*:mmb as int), b.k_uslf = iif(coalesce(:tar, 0) = 0, 0, b.k_uslf) where b.id = :idb; end update zs z set z.sum_uslf = (select sum(sum_uslf) from zsd d where d.zs_id = :zs_id), z.tar_usl = (select sum(tar_usl) from zsd d where d.zs_id = :zs_id) where z.id = :zs_id; exit; end /********************Поликлиника - 13 профы детей************************/ if (q_u = 2 and target = 13) then begin for select b.id from zsd b join zs z on z.id = b.zs_id where z.id = :zs_id into :idb do begin tar = (select first 1 d.tarif from zs a join zsd b on b.zs_id = a.id and b.date_1 between a.date_1 and a.date_2 and coalesce(b.is_no_get_usl,0) <> 1 and b.date_1 >= a.date_1 --без учета переазчетов и отказов join h_tarif_disp_13_17 d on d.target = a.target and d.stage = a.disp_stage and d.sex = :sex and a.date_2 between d.bp and d.epv and cast(d.usl_code as varchar(20)) = b.code_usl and d.age_grp = a.age_grp where b.id = :idb); update zsd b set b.sum_uslf = cast(coalesce(b.k_uslf * :tar, 0)*:mmb as int), b.tar_base = cast(coalesce(:tar, 0)*:mmb as int), b.tar_usl = cast(coalesce(:tar, 0)*:mmb as int), b.k_uslf = iif(coalesce(:tar, 0) = 0, 0, b.k_uslf) where b.id = :idb; end update zs z set z.sum_uslf = (select sum(sum_uslf) from zsd d where d.zs_id = :zs_id), z.tar_usl = (select sum(tar_usl) from zsd d where d.zs_id = :zs_id) where z.id = :zs_id; exit; end /********************Поликлиника - 20 Репродуктивный возраст************************/ if (q_u = 2 and target in (20)) then begin for select b.id from zsd b join zs z on z.id = b.zs_id where z.id = :zs_id into :idb do begin tar = (select first 1 d.tarif from zs a join patients p on p.id = a.patient_id join zsd b on b.zs_id = a.id and b.date_1 between a.date_1 and a.date_2 and coalesce(b.is_no_get_usl,0) <> 1 and b.date_1 >= a.date_1 --без учета переазчетов и отказов join h_tarif_disp_20 d on d.stage = a.disp_stage and d.sex = :sex and a.date_2 between d.bp and d.epv and cast(d.usl_code as varchar(20)) = b.code_usl and (extract(year from a.date_1) - extract(year from p.dr)) between d.y1 and d.y2 where b.id = :idb); update zsd b set b.sum_uslf = cast(coalesce(b.k_uslf * :tar, 0)*:mmb as int), b.tar_base = cast(coalesce(:tar, 0)*:mmb as int), b.tar_usl = cast(coalesce(:tar, 0)*:mmb as int), b.k_uslf = iif(coalesce(:tar, 0) = 0, 0, b.k_uslf) where b.id = :idb; end update zs z set z.sum_uslf = (select sum(sum_uslf) from zsd d where d.zs_id = :zs_id), z.tar_usl = (select sum(tar_usl) from zsd d where d.zs_id = :zs_id) where z.id = :zs_id; exit; end end; COMMIT WORK;