1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
SET TERM ^ ;
create or alter procedure SP_ACT_MEK2014_UPDATE (
CH_ID integer)
as
declare variable T0_ID integer;
declare variable ACT_NUM varchar(20);
begin
delete from mek2014_t0 t
where t.ch_id = :ch_id;
act_num = (select ch.y || '/' || lpad(ch.m, 2, '0') || '/' || lpad(ch.lpu, 3, '0')
from lpu_ch ch where ch.id = :ch_id);
--------------------------------------------------------------------------------
-- 1. Весь счет (без превышения плана и диспансеризации)
insert into MEK2014_T0 (CH_ID, MEK_TYPE, ACT_NUM, K_USLF, SUM_USLF, SUM_USL, FAIL_CNT, FAIL_SUM)
select :ch_id, 1, :act_num || '/1', sum(a.k_uslf), sum(a.sum_uslf), sum(a.sum_usl),
sum(iif(a.fail_code is null, 0, 1)), sum(iif(a.fail_code is null, 0, a.s_mek))
from ora a
where a.ch_id = :ch_id and a.is_disp = 0 and coalesce(a.is_omu, 0) = 0
group by 1, 2, 3
returning ID into :t0_id;
insert into MEK2014_T1 (T0_ID, DPFS, DS, DATE_1, DATE_2, FAIL_CODE, FC_NAME, S_MEK)
select :t0_id, trim(coalesce(a.ser_sp, '') || ' ' || a.num_sp),
a.ds1, a.date_1, a.date_2, f.id, f.name, a.s_mek
from ora a
join h_fc_2011 f on f.id = a.fail_code
where a.ch_id = :ch_id and a.is_disp = 0 and a.fail_code is not null
and a.is_disp = 0 and coalesce(a.is_omu, 0) = 0;
insert into MEK2014_T2 (T0_ID, Q_U, USL_NAME, PROF_NAME, K_USLF, SUM_USLF, K_MEK, S_MEK, K_USL, SUM_USL)
select :t0_id, a.q_u, a.usl_name, a.prof_name, sum(a.k_uslf), sum(a.sum_uslf),
sum(a.k_mek), sum(a.s_mek), sum(a.k_usl), sum(a.sum_usl)
from ora a
where a.ch_id = :ch_id and a.is_disp = 0 and coalesce(a.is_omu, 0) = 0
group by 1, 2, 3, 4;
insert into MEK2014_T3 (T0_ID, Q_U, USL_NAME, K_USLF, SUM_USLF, K_MEK, S_MEK, K_USL, SUM_USL)
select :t0_id, a.q_u, a.usl_name, sum(a.k_uslf), sum(a.sum_uslf),
sum(a.k_mek), sum(a.s_mek), sum(a.k_usl), sum(a.sum_usl)
from ora a
where a.ch_id = :ch_id and a.is_disp = 0 and coalesce(a.is_omu, 0) = 0
group by 1, 2, 3;
--------------------------------------------------------------------------------
t0_id = null;
-- 2. Только по снятиям по превышению плана
insert into MEK2014_T0 (CH_ID, MEK_TYPE, K_USLF, SUM_USLF, FAIL_CNT, FAIL_SUM, SUM_USL, ACT_NUM)
select a.ch_id, 2, sum(a.k_uslf), sum(a.sum_uslf),
count(*), sum(a.s_over), sum(a.s_over), :act_num || '/2'
from ora a
where a.ch_id = :ch_id and a.is_disp = 0 and a.prz = 2
group by 1, 2
returning ID into :t0_id;
if (t0_id is not null) then
begin
insert into MEK2014_T1 (T0_ID, DPFS, DS, DATE_1, DATE_2, FAIL_CODE, FC_NAME, S_MEK)
select :t0_id, trim(coalesce(a.ser_sp, '') || ' ' || a.num_sp),
a.ds1, a.date_1, a.date_2, f.id, f.name, a.s_mek
from ora a
join h_fc_2011 f on f.id = '5.3.2'
where a.ch_id = :ch_id and a.prz = 1 and a.fail_code is not null
and a.is_disp = 0;
insert into MEK2014_T2 (T0_ID, Q_U, USL_NAME, PROF_NAME, K_USLF, SUM_USLF, K_MEK, S_MEK, K_USL, SUM_USL)
select :t0_id, a.q_u, a.usl_name, a.prof_name, sum(a.k_uslf), sum(a.sum_uslf),
sum(a.k_over), sum(a.s_over), sum(a.k_usl), sum(a.sum_usl)
from ora a
where a.ch_id = :ch_id and a.prz = 1 and a.is_disp = 0
group by 1, 2, 3, 4;
insert into MEK2014_T3 (T0_ID, Q_U, USL_NAME, K_USLF, SUM_USLF, K_MEK, S_MEK, K_USL, SUM_USL)
select :t0_id, a.q_u, a.usl_name, sum(a.k_uslf), sum(a.sum_uslf),
sum(a.k_over), sum(a.s_over), sum(a.k_usl), sum(a.sum_usl)
from ora a
where a.ch_id = :ch_id and a.prz = 1 and a.is_disp = 0
group by 1, 2, 3;
end
--------------------------------------------------------------------------------
t0_id = null;
-- 3. Диспансеризация
insert into MEK2014_T0 (CH_ID, MEK_TYPE, K_USLF, SUM_USLF, FAIL_CNT, FAIL_SUM, SUM_USL, ACT_NUM)
select a.ch_id, 3, sum(a.k_uslf), sum(a.sum_uslf),
sum(iif(a.fail_code is null, 0, 1)),
sum(iif(a.fail_code is null, 0, a.s_mek)), sum(a.sum_usl),
:act_num || '/3'
from ora a
where a.ch_id = :ch_id and a.is_disp = 1
group by 1, 2
returning ID into :t0_id;
if (t0_id is not null) then
begin
insert into MEK2014_T1 (T0_ID, DPFS, DS, DATE_1, DATE_2, FAIL_CODE, FC_NAME, S_MEK)
select :t0_id, trim(coalesce(a.ser_sp, '') || ' ' || a.num_sp),
a.ds1, a.date_1, a.date_2, f.id, f.name,
a.s_mek
from ora a
join h_fc_2011 f on f.id = a.fail_code
where a.ch_id = :ch_id and a.is_disp = 1 and a.fail_code is not null;
insert into MEK2014_T2 (T0_ID, Q_U, USL_NAME, PROF_NAME, K_USLF, SUM_USLF, K_MEK, S_MEK, K_USL, SUM_USL)
select :t0_id, a.q_u, a.usl_name, a.prof_name, sum(a.k_uslf), sum(a.sum_uslf),
sum(a.k_mek), sum(a.s_mek), sum(a.k_usl), sum(a.sum_usl)
from ora a
where a.ch_id = :ch_id and a.is_disp = 1
group by 1, 2, 3, 4;
insert into MEK2014_T3 (T0_ID, Q_U, USL_NAME, K_USLF, SUM_USLF, K_MEK, S_MEK, K_USL, SUM_USL)
select :t0_id, a.q_u, a.usl_name, sum(a.k_uslf), sum(a.sum_uslf),
sum(a.k_mek), sum(a.s_mek), sum(a.k_usl), sum(a.sum_usl)
from ora a
where a.ch_id = :ch_id and a.is_disp = 1
group by 1, 2, 3;
end
--------------------------------------------------------------------------------
t0_id = null;
-- 4. Отдельные медицинские услуги
insert into MEK2014_T0 (CH_ID, MEK_TYPE, K_USLF, SUM_USLF, FAIL_CNT, FAIL_SUM, SUM_USL, ACT_NUM)
select a.ch_id, 4, sum(a.k_uslf), sum(a.sum_uslf),
sum(iif(a.fail_code is null, 0, 1)),
sum(iif(a.fail_code is null, 0, a.s_mek)), sum(a.sum_usl),
:act_num || '/3'
from ora a
where a.ch_id = :ch_id and a.is_omu = 1
group by 1, 2
returning ID into :t0_id;
if (t0_id is not null) then
begin
insert into MEK2014_T1 (T0_ID, DPFS, DS, DATE_1, DATE_2, FAIL_CODE, FC_NAME, S_MEK)
select :t0_id, trim(coalesce(a.ser_sp, '') || ' ' || a.num_sp),
a.ds1, a.date_1, a.date_2, f.id, f.name,
a.s_mek
from ora a
join h_fc_2011 f on f.id = a.fail_code
where a.ch_id = :ch_id and a.is_omu = 1 and a.fail_code is not null;
insert into MEK2014_T2 (T0_ID, Q_U, USL_NAME, PROF_NAME, K_USLF, SUM_USLF, K_MEK, S_MEK, K_USL, SUM_USL)
select :t0_id, a.q_u, a.usl_name, a.prof_name, sum(a.k_uslf), sum(a.sum_uslf),
sum(a.k_mek), sum(a.s_mek), sum(a.k_usl), sum(a.sum_usl)
from ora a
where a.ch_id = :ch_id and a.is_omu = 1
group by 1, 2, 3, 4;
insert into MEK2014_T3 (T0_ID, Q_U, USL_NAME, K_USLF, SUM_USLF, K_MEK, S_MEK, K_USL, SUM_USL)
select :t0_id, a.q_u, a.usl_name, sum(a.k_uslf), sum(a.sum_uslf),
sum(a.k_mek), sum(a.s_mek), sum(a.k_usl), sum(a.sum_usl)
from ora a
where a.ch_id = :ch_id and a.is_omu = 1
group by 1, 2, 3;
end
end^
SET TERM ; ^
