TVC9vFWQ

1
2declare uniq text;
3        rec record; 
4        ret json;
5        rb integer;
6        rec1 record;
7        rc3 record;
8        hsp_id_variable INTEGER;
9begin
10
11begin --trans
12
13perform set_config('app.user', (select login from sec_user where id = ($1::JSON->>'user')::INTEGER), true); 
14perform set_config('app.source', 'typing2', true);
15
16insert into sec_audit_entry(id,date,user_id)
17	select nextval('sec_audit_entry_seq'),now(),($1->>'userId')::INTEGER ;
18uniq = '';
19execute'
20
21---updated 04.08.2015 test
22
23-- 1. читаю структуру
24----------------------------------------------------
25
26create temp table "typing_diagnos" ON COMMIT DROP AS  
27with t as (
28 select
29  json_array_elements(value) d
30 from json_each(
31$1 -- эта строка - параметр
32 ) where key=''diagnosis''
33)
34
35select
36 d ->> ''id'' _id,
37 d ->> ''mkb_id'' _mkb_id,
38 d ->> ''disease_type_id'' _disease_type_id,
39 d ->> ''type_id'' _type_id,
40 d ->> ''is_main'' _is_main,
41 d ->> ''stage_id'' _stage_id,
42 d ->> ''step_id'' _step_id,
43 d ->> ''step_num'' step_num,
44 d ->> ''disp_id'' _disp_id,
45 d ->> ''disp_in_out'' disp_in_out,
46 d ->> ''nosol_registr_id'' _nosol_registr_id,
47 d ->> ''dispensary_group_id'' _dispensary_group_id,
48 d ->> ''reg_in_dt'' _reg_in_dt,
49 d ->> ''reg_out_dt'' _reg_out_dt,
50 d ->> ''reg_in_doctor_id'' _reg_in_doctor_id,
51 d ->> ''reg_out_doctor_id'' _reg_out_doctor_id,
52 d ->> ''reg_stage_id'' _reg_stage_id,
53 d ->> ''reg_out_reason_id'' _reg_out_reason_id,
54 d ->> ''note'' note
55from t
56;
57
58
59create temp table "typing_services"  ON COMMIT DROP AS 
60with t as (
61 select
62  json_array_elements(value) d
63 from json_each(
64$1 -- эта строка - параметр
65 ) where key=''services''
66)
67
68select
69 d ->> ''id'' _id,
70 d ->> ''step_num'' step_num,
71 d ->> ''step_id'' _step_id,
72 d ->> ''service_num'' service_num,
73 d ->> ''service_id'' _service_id,
74 d ->> ''bdate'' _bdate,
75 d ->> ''funding_id'' _funding_id,
76 d ->> ''quantity'' _quantity,
77 d ->> ''res_group_id'' _res_group_id,
78 d ->> ''mkb_id'' _mkb_id,
79 d ->> ''doctor_id'' _doctor_id,
80 d ->> ''cul'' _cul,
81 d ->> ''note'' _note,
82 d ->> ''diag_disease_type_id'' _diag_disease_type_id,
83 d ->> ''diag_stage_id'' _diag_stage_id,
84 d ->> ''diag_type_id'' _diag_type_id,
85 d ->> ''tooth_number'' tooth_number,
86 typing.to_int(d->>''anatomic_zone_id'') anatomic_zone_id,
87 d ->> ''is_refused'' _is_refused,
88 d->> ''org_id'' _org_id,
89 d->> ''result_type_id'' _result_type_id,
90 d->> ''value'' result_value,
91 typing.to_int(d->>''srv_result_id'') srv_result_id,
92 typing.to_int(d->>''health_group_id'') health_group_id,
93 case when(d->>''is_amalgam_filling'' =''1'') then true else false end is_amalgam_filling
94
95from t
96;
97
98create temp table  "typing_visits"  ON COMMIT DROP AS  
99with t as (
100 select
101  json_array_elements(value) d
102 from json_each(
103$1 -- эта строка - параметр
104 ) where key=''visits''
105)
106
107select
108 d ->> ''id'' _id,
109 d ->> ''step_num'' step_num,
110 d ->> ''type_id'' _type_id,
111 d ->> ''admission_date'' _admission_date,
112 d ->> ''outcome_date'' _outcome_date,
113 d ->> ''place_id'' _place_id,
114 d ->> ''initiator_id'' _initiator_id,
115 d ->> ''goal_id'' _goal_id,
116 d ->> ''profile_id'' _profile_id,
117 d ->> ''standard_id'' _standard_id,
118 d ->> ''mes_id'' _mes_id,
119 d ->> ''res_group_id'' _res_group_id,
120 d ->> ''doctor_id'' _doctor_id,
121 d ->> ''result_id'' _result_id,
122 d ->> ''stepResultReason_id'' _stepResultReason_id,
123 d ->> ''outcome_id'' _outcome_id,
124 d ->> ''is_viewed'' _is_viewed,
125 d ->> ''is_needed'' _is_needed,
126 d ->> ''is_sanitized'' _is_sanitized,
127 d ->> ''department_id'' _department_id,
128 d ->> ''bed_profile_id'' _bed_profile_id,
129 d ->> ''csg_id'' _csg_id,
130 d ->> ''deviation_reason_id'' _deviation_reason_id,
131 d ->> ''complexity_level_id'' _complexity_level_id,
132 d ->> ''hosp_department_id'' _hosp_department_id,
133 d ->> ''vmp_type_id'' _vmp_type_id,
134 typing.to_int(d ->> ''funding_id'') funding_id,
135 d ->> ''vmp_method_id'' _vmp_method_id,
136 typing.to_time(d ->> ''admission_time'') admission_time,
137 typing.to_time(d ->> ''outcome_time'') outcome_time
138 
139from t
140;
141
142
143create temp table  "typing_case"   ON COMMIT DROP AS  
144
145with t as (
146select
147$1::json d
148)
149
150
151select
152 d ->> ''id'' _id,
153 d ->> ''case_type_id'' _case_type_id,
154 d ->> ''patient_id'' _patient_id,
155 d ->> ''clinic_id'' _clinic_id,
156 d ->> ''uid'' uid,
157 d ->> ''care_regimen_id'' _care_regimen_id,
158 d ->> ''funding_id'' _funding_id,
159 d ->> ''init_goal_id'' _init_goal_id,
160 d ->> ''care_level_id'' _care_level_id,
161 d ->> ''payment_method_id'' _payment_method_id,
162 d ->> ''admission_reason_id'' _admission_reason_id,
163 d ->> ''repeat_count_id'' _repeat_count_id,
164 d ->> ''referral_id'' _referral_id,
165 d ->> ''ref_organization_id'' _ref_organization_id,
166 d ->> ''ref_doctor_id'' _ref_doctor_id,
167 d ->> ''referral_date'' _referral_date,
168 d ->> ''recv_organization_id'' _recv_organization_id,
169 d ->> ''ref_mkb_id'' _ref_mkb_id,
170 d ->> ''order_number'' order_number,
171 d ->> ''referral_type_id'' _referral_type_id,
172 d ->> ''sicklist_id'' _sicklist_id,
173 d ->> ''sl_code'' sl_code,
174 d ->> ''disability_reason_id'' _disability_reason_id,
175 d ->> ''disability_from_dt'' _disability_from_dt,
176 d ->> ''disability_to_dt'' _disability_to_dt,
177 coalesce(d ->> ''care_providing_from_id'',d ->> ''care_providing_form_id'') _care_providing_form_id,
178 d ->> ''admission_state'' _admission_state_id,
179 d ->> ''drunkenness_type_id'' _drunkenness_type_id,
180 d->> ''time_gone_id'' _time_gone_id,
181 d->> ''provision_condition_id'' _provision_condition_id,
182 d->> ''note'' _note,
183 d->> ''workplace_print'' workplace_print,
184 d->> ''ready_to_work_dt'' _ready_to_work_dt,
185 d->> ''md_sicklist_state'' _md_sicklist_state,
186 d->> ''sl_name'' sl_name,
187 d->> ''sl_surname'' sl_surname,
188 d->> ''sl_patr_name'' sl_patr_name,
189 d->> ''employee_position_id'' _employee_position_id,
190 typing.to_int(d->> ''userId'') user_id,
191 typing.to_int(d->>''talon'') talon,
192 typing.to_int(d->> ''health_group_id'')  health_group_id,
193 typing.to_int(d->>''case_result_id'') result_id
194
195from t
196;
197
198
199
200create temp table "typing_resources" ON COMMIT DROP AS 
201with t as (
202 select
203  json_array_elements(value) d
204 from json_each(
205$1 -- эта строка - параметр
206 ) where key=''res''
207)
208
209select
210 d ->> ''role_id'' _role_id,
211 d ->> ''step_num'' step_num,
212d ->> ''resource_id'' _resource_id,
213d ->> ''bed_id'' _bed_id,
214 d ->> ''bdatetime'' _bdatetime,
215 d ->> ''edatetime'' _edatetime,
216 d ->> ''service_num'' _serv_num
217from t
218;
219
220create temp table "typing_cur"  ON COMMIT DROP AS 
221with t as (
222 select
223  json_array_elements(value) d
224 from json_each(
225$1 -- эта строка - параметр
226 ) where key=''cur''
227)
228
229select
230 d ->> ''criteria_id'' _criteria_id,
231 d ->> ''step_num'' step_num,
232 d ->> ''value'' _value,
233 d ->> ''id'' _id
234from t
235;
236create temp table "typing_standarts"  ON COMMIT DROP AS 
237with t as (
238 select
239  json_array_elements(value) d
240 from json_each(
241$1 -- эта строка - параметр
242 ) where key=''standarts''
243 )
244
245select
246 typing.to_int(d ->> ''csg_id'') csg_id,
247 d->> ''step_num''  step_num,
248 typing.to_int(d ->> ''vmp_type_id'')vmp_type_id,
249 typing.to_int(d ->> ''vmp_method_id'')vmp_method_id
250
251from t
252;
253
254 
255
256
257
258-- 2. Случай--------------------------------------------------------------------------
259
260-- 2.1 Добавляю реальные колонки
261alter table "typing_case"
262 add column id integer,
263 add column case_type_id integer,
264 add column patient_id integer,
265 add column clinic_id integer,
266 add column care_regimen_id integer,
267 add column funding_id integer,
268 add column init_goal_id integer,
269 add column care_level_id integer,
270 add column payment_method_id integer,
271 add column admission_reason_id integer,
272 add column repeat_count_id integer,
273 add column referral_id integer,
274 add column ref_organization_id integer,
275 add column ref_doctor_id integer,
276 add column ref_mkb_id integer,
277 add column referral_date date,
278 add column recv_organization_id integer,
279 add column referral_type_id integer,
280 add column sicklist_id integer,
281 add column disability_reason_id integer,
282 add column disability_from_dt date,
283 add column disability_to_dt date,
284 add column care_providing_form_id integer,
285 add column admission_state_id integer,
286 add column drunkenness_type_id integer,
287add COLUMN time_gone_id integer,
288add COLUMN provision_condition_id INTEGER,
289add column ready_to_work_dt date,
290add column  md_sicklist_state integer,
291add column employee_position_id integer,
292
293 add column errors text default ''''
294;
295-- 2.2 Заполняю реальные колонки
296update "typing_case" set
297 id = typing.to_int(_id),
298 case_type_id = coalesce(typing.to_int(_case_type_id), 1),
299 patient_id = typing.to_int(_patient_id),
300 clinic_id = typing.to_int(_clinic_id),
301 care_regimen_id = typing.to_int(_care_regimen_id),
302 funding_id = typing.to_int(_funding_id),
303 init_goal_id = typing.to_int(_init_goal_id),
304 care_level_id = typing.to_int(_care_level_id),
305 payment_method_id = typing.to_int(_payment_method_id),
306 admission_reason_id = typing.to_int(_admission_reason_id),
307 repeat_count_id = typing.to_int(_repeat_count_id),
308 referral_id = typing.to_int(_referral_id),
309 ref_organization_id = typing.to_int(_ref_organization_id),
310 ref_mkb_id = typing.to_int(_ref_mkb_id),
311 ref_doctor_id = typing.to_int(_ref_doctor_id),
312 referral_date = typing.to_dt(_referral_date),
313 recv_organization_id = typing.to_int(_recv_organization_id),
314 referral_type_id = typing.to_int(_referral_type_id),
315 sicklist_id = typing.to_int(_sicklist_id),
316 disability_reason_id = typing.to_int(_disability_reason_id),
317 disability_from_dt = typing.to_dt(_disability_from_dt),
318 disability_to_dt = typing.to_dt(_disability_to_dt),
319care_providing_form_id  = typing.to_int(_care_providing_form_id),
320admission_state_id = typing.to_int(_admission_state_id),
321drunkenness_type_id = typing.to_int(_drunkenness_type_id),
322time_gone_id = typing.to_int(_time_gone_id),
323provision_condition_id = typing.to_int(_provision_condition_id),
324ready_to_work_dt = typing.to_dt(_ready_to_work_dt),
325md_sicklist_state = typing.to_int(_md_sicklist_state),
326employee_position_id = typing.to_int(_employee_position_id)
327
328
329
330
331;
332
333
334-- 2.3. обновляю данные в случае, если есть id при этом проверяю все ограничения
335update mc_med_case_result r 
336set health_group_id = c.health_group_id
337from typing_case c  where c.result_id = r.id
338and exists(select 1 from mc_health_group g where g.id =c.health_group_id);
339
340update mc_case c set
341 clinic_id = t.clinic_id, care_regimen_id = t.care_regimen_id, funding_id = t.funding_id, init_goal_id = t.init_goal_id, care_level_id = t.care_level_id, payment_method_id = t.payment_method_id,
342 admission_reason_id = t.admission_reason_id, repeat_count_id = t.repeat_count_id, referral_id = t.referral_id, patient_id = t.patient_id, uid = t.uid,
343    care_providing_form_id = t.care_providing_form_id,admission_state_id = t.admission_state_id,drunkenness_type_id = t.drunkenness_type_id,time_gone_id = t.time_gone_id,provision_condition_id = t.provision_condition_id
344
345from "typing_case" t
346where
347 c.id = t.id and
348 exists (select 1 from pci_patient p where p.id = t.patient_id) and
349 exists (select 1 from md_clinic cln where cln.id = t.clinic_id) and
350 (exists (select 1 from mc_care_regimen r where r.id = t.care_regimen_id) or t.care_regimen_id is null) and
351 (exists (select 1 from fin_funding_source_type f where f.id = t.funding_id) or t.funding_id is null) and
352 (exists (select 1 from mc_case_init_goal f where f.id = t.init_goal_id)) and
353 (exists (select 1 from mc_care_level cl where cl.id = t.care_level_id) or t.care_level_id is null) and
354 (exists (select 1 from mc_payment_method pm where pm.id = t.payment_method_id) or t.payment_method_id is null) and
355 (exists (select 1 from mc_admission_reason ar where ar.id = t.admission_reason_id) or t.admission_reason_id is null) and
356 (exists (select 1 from mc_repeat_count cl where cl.id = t.repeat_count_id) or t.repeat_count_id is null) and
357 (exists (select 1 from md_referral rf where rf.id = t.referral_id) or t.referral_id is null) and t.uid is not null
358;
359
360-- 2.4. Запись ошибок (не найден id обновляемого случая) при обновлении случая
361update "typing_case" t set errors = ''{"level":"case", "message":"не найден случай по id", "column_name":"id"}''
362where id is not null and not exists (select 1 from mc_case c where c.id = t.id);
363
364-- 2.5. Добавляю случай, если нет id, при этом проверяю все ограничения
365alter table "typing_case" add column new_id integer;
366update "typing_case" t
367set new_id = nextval(''mc_case_seq'')
368where t.id is null and
369 exists (select 1 from pci_patient p where p.id = t.patient_id) and
370 exists (select 1 from md_clinic cln where cln.id = t.clinic_id) and
371 exists (select 1 from mc_case_type ct where ct.id = t.case_type_id) and
372 (exists (select 1 from mc_care_regimen r where r.id = t.care_regimen_id) or t.care_regimen_id is null) and
373 (exists (select 1 from fin_funding_source_type f where f.id = t.funding_id) or t.funding_id is null) and
374 (exists (select 1 from mc_case_init_goal f where f.id = t.init_goal_id)) and
375 (exists (select 1 from mc_care_level cl where cl.id = t.care_level_id) or t.care_level_id is null) and
376 (exists (select 1 from mc_payment_method pm where pm.id = t.payment_method_id) or t.payment_method_id is null) and
377 (exists (select 1 from mc_admission_reason ar where ar.id = t.admission_reason_id) or t.admission_reason_id is null) and
378 (exists (select 1 from mc_repeat_count cl where cl.id = t.repeat_count_id) or t.repeat_count_id is null) and
379 (exists (select 1 from md_referral rf where rf.id = t.referral_id) or t.referral_id is null) and t.uid is not null and
380 (exists (select 1 from mc_health_group g where g.id = t.health_group_id) or t.health_group_id is null)
381
382;
383update typing_case t set result_id =nextval(''mc_med_case_result_id'')
384where result_id is null 
385and exists (select 1 from mc_health_group g where g.id = t.health_group_id);
386
387
388insert into mc_med_case_result (id,health_group_id)
389select result_id ,health_group_id from typing_case t
390where result_id is not null;
391 
392 
393
394;
395update typing_case t set result_id =nextval(''mc_med_case_result_id'')
396where result_id is null 
397and exists (select 1 from mc_health_group g where g.id = t.health_group_id);
398
399
400insert into mc_med_case_result (id,health_group_id)
401select result_id ,health_group_id from typing_case t
402where result_id is not null;
403
404
405
406insert into mc_case(id, case_type_id, create_date, clinic_id, patient_id, care_regimen_id, funding_id, init_goal_id, care_level_id, payment_method_id, admission_reason_id, repeat_count_id, referral_id, uid, care_providing_form_id ,
407admission_state_id,
408drunkenness_type_id ,
409time_gone_id,
410provision_condition_id,note)
411select new_id, case_type_id, current_date, clinic_id, patient_id, care_regimen_id, funding_id, init_goal_id, care_level_id, payment_method_id, admission_reason_id, repeat_count_id, referral_id, uid, care_providing_form_id ,
412admission_state_id,
413drunkenness_type_id ,
414time_gone_id,
415provision_condition_id,
416_note
417
418 from 
419 "typing_case" where new_id
420 is not null;
421
422
423--update "typing_case" set id = new_id where new_id is not null;
424
425-- 2.6 Добавление оставшихся ошибок (при обновлении, либо добавлении случая)
426update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден тип случая по case_type_id = '', case_type_id, ''", "column_name":"case_type_id"}'')
427 where not exists (select 1 from mc_case_type ct where ct.id = t.case_type_id);
428update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден пациент по patient_id = '', patient_id, ''", "column_name":"id"}'')
429 where not exists (select 1 from pci_patient p where p.id = t.patient_id);
430update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найдена МО по clinic_id = '', clinic_id, ''", "column_name":"clinic_id"}'')
431 where not exists (select 1 from md_clinic cln where cln.id = t.clinic_id);
432update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден care_regimen_id = '', care_regimen_id, ''", "column_name":"care_regimen_id"}'')
433 where care_regimen_id is not null and not exists (select 1 from mc_care_regimen cr where cr.id = t.care_regimen_id);
434update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден funding_id = '', funding_id, ''", "column_name":"funding_id"}'')
435 where funding_id is not null and not exists (select 1 from fin_funding_source_type x where x.id = t.funding_id);
436update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден init_goal_id = '', init_goal_id, ''", "column_name":"init_goal_id"}'')
437 where not exists (select 1 from mc_case_init_goal x where x.id = t.init_goal_id);
438update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден care_level_id = '', care_level_id, ''", "column_name":"care_level_id"}'')
439 where care_level_id is not null and not exists (select 1 from mc_care_level x where x.id = t.care_level_id);
440update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден payment_method_id = '', payment_method_id, ''", "column_name":"payment_method_id"}'')
441 where payment_method_id is not null and not exists (select 1 from mc_payment_method x where x.id = t.payment_method_id);
442update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден admission_reason_id = '', admission_reason_id, ''", "column_name":"admission_reason_id"}'')
443 where admission_reason_id is not null and not exists (select 1 from mc_admission_reason x where x.id = t.admission_reason_id);
444update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден repeat_count_id = '', repeat_count_id, ''", "column_name":"repeat_count_id"}'')
445 where repeat_count_id is not null and not exists (select 1 from mc_repeat_count x where x.id = t.repeat_count_id);
446update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден referral_id = '', referral_id, ''", "column_name":"referral_id"}'')
447 where referral_id is not null and not exists (select 1 from md_referral x where x.id = t.referral_id);
448update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден uid = '', uid, ''", "column_name":"uid"}'')
449 where referral_id is not null and not exists (select 1 from md_referral x where x.id = t.referral_id);
450update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"Данный случай включен в реестр, изменение невозможно", 
451"column_name":"id"}'')
452 where exists(select 1 FROM fin_bill_spec_item f JOIN md_srv_rendered m ON m.id = f.service_id WHERE m.case_id =t.id);
453update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден health_group_id = '', health_group_id, ''", "column_name":"health_group_id"}'')
454 where health_group_id is not null and not exists (select 1 from mc_health_group x where x.id = t.health_group_id);
455update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден result_id = '', result_id, ''", "column_name":"result_id"}'')
456 where result_id is not null and not exists (select 1 from mc_med_case_result x where x.id = t.health_group_id);
457  
458
459
460
461
462
463-- 2.5 Ресурс-------------------------------------------------------------------------
464-- 2.5.1 добавляю реальные колонки----------------------------------------------------
465
466alter table "typing_resources"
467 add column role_id integer,
468 add column serv_num    integer,
469 add column resource_id integer,
470 add column bdatetime   date,
471 add column edatetime   date,
472  add column group_id    integer,
473 add column bed_id integer,
474 add column org_id      integer,
475 add column responsible_id integer,
476 add column errors      text default ''''
477;
478
479--- 2.5.2 Заполняю реальные колонки
480update "typing_resources" set
481 role_id    = typing.to_int(_role_id),
482 serv_num    = typing.to_int(_serv_num ),
483 resource_id = typing.to_int(_resource_id),
484 bdatetime  = typing.to_dt(_bdatetime),
485 edatetime  = typing.to_dt(_edatetime),
486 bed_id = typing.to_int(_bed_id)
487 ;
488
489
490---Добавляю группу и организацию
491update "typing_resources" set
492group_id= nextval(''sr_res_group_seq'')
493WHERE resource_id in(SELECT max(resource_id) FROM "typing_resources" group by serv_num, step_num);
494
495update "typing_resources" set
496org_id =  (SELECT clinic_id FROM  "typing_case" limit 1);
497
498
499update "typing_resources" r set
500responsible_id = employee_position_id
501FROM pim_employee_position_resource epr WHERE epr.id = r.resource_id and  group_id is not null
502and exists(SELECT 1 FROM sr_resource sr WHERE sr.id = r.resource_id)
503and exists(SELECT 1 FROM sr_res_role x WHERE id = r.role_id and x.kind_id =1)
504;
505
506
507insert into sr_res_group(id, org_id,edate, bdate, responsible_id, is_system, department_id)
508SELECT group_id, org_id, edatetime, bdatetime, responsible_id, true, null FROM  "typing_resources" r WHERE  group_id is not null
509and exists(SELECT 1 FROM pim_organization o WHERE o.id = r.org_id);
510
511
512
513
514--Заполняю группу
515update "typing_resources" r set
516group_id= t.group_id
517FROM "typing_resources" t WHERE t.group_id is not null and ((t.serv_num = r.serv_num) or (t.serv_num is null and r.serv_num is null ))  and t.step_num = r.step_num and r.group_id is null;
518
519insert into sr_res_group_relationship(id, resource_id,bdatetime, edatetime,group_id, role_id,is_disabled)
520  SELECT nextval(''sr_res_group_relationship_seq''), resource_id,bdatetime,edatetime, group_id, role_id, CASE when edatetime is not null then true end
521  FROM  "typing_resources" t
522WHERE exists(SELECT 1 FROM sr_resource r WHERE  r.id = t.resource_id)
523and exists(SELECT 1 FROM sr_res_role r WHERE r.id= t.role_id ) 
524and exists (select 1 from sr_res_group x where x.id = t.group_id)
525;
526
527insert into sr_res_group_relationship(id, resource_id,bdatetime, edatetime,group_id, role_id,is_disabled)
528  SELECT nextval(''sr_res_group_relationship_seq''), resource_id,bdatetime,edatetime, bed_id, (SELECT id from sr_res_role where code = ''BED''), CASE when edatetime is not null then true end
529  FROM  "typing_resources" t
530WHERE exists(SELECT 1 FROM sr_resource r WHERE  r.id = t.bed_id)
531and exists(SELECT 1 FROM sr_res_role r WHERE r.id= t.role_id ) 
532;
533
534
535
536update "typing_resources" t set errors = concat(errors, ''{"level":"resource", "message":"не найден ресурс по resource_id = '', resource_id, ''", "column_name":"resource_id"}'')
537 where not exists (select 1 from sr_resource ct where ct.id = t.resource_id);
538update "typing_resources" t set errors = concat(errors, ''{"level":"resource", "message": "не найдена роль по role_id = '', role_id, ''", "column_name":"role_id"}'')
539 where not exists (select 1 from sr_res_role ct where ct.id = t.role_id);
540update "typing_resources" t set errors = concat(errors, ''{"level":"resource", "message": "МО сотрудника и МО случая не совпадают", "column_name":"id"}'')
541 where not exists (select 1 
542	 from pim_employee_position_resource epr
543     join pim_employee_position ep on epr.employee_position_id = ep.id
544     join pim_employee e on ep.employee_id = e.id
545	 WHERE epr.id = t.resource_id
546	 and e.organization_id = t.org_id
547	 ) and  t.resource_id is not null
548    ;
549 
550
551
552
553
554-- 3. Шаги--------------------------------------------------------------------------
555-- 3.1 добавление реальных колонок
556alter table "typing_visits"
557 add column case_id integer,
558 add column id integer,
559 add column type_id integer,
560 add column admission_date date,
561 add column outcome_date date,
562 add column place_id integer,
563 add column initiator_id integer,
564 add column goal_id integer,
565 add column profile_id integer,
566 add column standard_id integer,
567 add column mes_id integer,
568 add column res_group_id integer,
569 add column result_id integer,
570 add column stepResultReason_id integer,
571 add column outcome_id integer,
572 add column patient_id integer,
573 add column clinic_id integer,
574 add column doctor_id integer,
575 add column is_viewed boolean,
576 add column is_needed boolean,
577 add column is_sanitized boolean,
578add COLUMN department_id INTEGER,
579add COLUMN hosp_department_id integer,
580add COLUMN bed_profile_id INTEGER,
581    add column csg_id INTEGER,
582add column complexity_level_id INTEGER,
583add column deviation_reason_id INTEGER,
584add column vmp_type_id INTEGER,
585add column vmp_method_id INTEGER,
586 add column errors text default ''''
587 
588;
589
590-- 3.2 Заполнение реальных колонок
591update "typing_visits" set
592 case_id = (select coalesce(id,new_id) from "typing_case"),
593 patient_id = (select patient_id from "typing_case"),
594 clinic_id = (select clinic_id from "typing_case"),
595 id = typing.to_int(_id),
596 type_id = typing.to_int(_type_id),
597 admission_date = typing.to_dt(_admission_date),
598 outcome_date = typing.to_dt(_outcome_date),
599 place_id = typing.to_int(_place_id),
600 initiator_id = typing.to_int(_initiator_id),
601 goal_id = typing.to_int(_goal_id),
602 profile_id = typing.to_int(_profile_id),
603 standard_id = typing.to_int(_standard_id),
604 mes_id = typing.to_int(_mes_id),
605 res_group_id = typing.to_int(_res_group_id),
606 stepResultReason_id = typing.to_int(_stepResultReason_id),
607 result_id = typing.to_int(_result_id),
608 outcome_id = typing.to_int(_outcome_id),
609 doctor_id = typing.to_int(_doctor_id),
610department_id = typing.to_int(_department_id),
611bed_profile_id = typing.to_int(_bed_profile_id),
612csg_id = typing.to_int(_csg_id),
613hosp_department_id = typing.to_int(_hosp_department_id),
614complexity_level_id= typing.to_int(_complexity_level_id),
615deviation_reason_id = typing.to_int(_deviation_reason_id),
616vmp_type_id  = typing.to_int(_vmp_type_id),
617vmp_method_id = typing.to_int(_vmp_method_id),
618 is_viewed =  case when _is_viewed = ''true'' or _is_viewed = ''1'' then true else false end,
619 is_needed =  case when _is_needed = ''true'' or _is_needed = ''1'' then true else false end,
620 is_sanitized =  case when _is_sanitized = ''true'' or _is_sanitized = ''1'' then true else false end
621;
622
623update "typing_visits" v
624set res_group_id = group_id
625FROM "typing_resources" r
626WHERE (v.step_num = r.step_num
627or (r.step_num is null)) --- если для всего случая - r.step_id должен быть пустой 
628and v.res_group_id is null
629;
630
631
632
633
634-- 3.2.1 Удаляю информацию по закрытию случая.
635update mc_step s 
636set result_id =null
637FROM  "typing_case" c WHERE c.id is not null and c.id =s.case_id
638and exists(SELECT 1 FROM mc_step_result r WHERE  r.id = s.result_id and is_closed = true);
639
640update "typing_visits" t
641  set res_group_id =  typing.typing_gen_res_group(t.doctor_id, null::INTEGER)
642  WHERE exists(SELECT 1 FROM "typing_visits" t JOIN mc_step s ON s.id = t.id)
643and exists(SELECT 1 FROM sr_res_group s WHERE s.id = t.res_group_id and is_system =false)
644and t.doctor_id is not null;
645
646update "typing_visits" t
647  set res_group_id = typing.typing_gen_res_group(null, t.res_group_id)
648  WHERE exists(SELECT 1 FROM "typing_visits" t JOIN mc_step s ON s.id = t.id)
649and exists(SELECT 1 FROM sr_res_group s WHERE s.id = t.res_group_id and is_system =false)
650and t.res_group_id is not null
651and id is null
652;
653
654update "typing_visits" t
655  set res_group_id =  case when 
656			coalesce(typing.typing_get_nosystem_res_group(step.res_group_id),1) = coalesce(t.res_group_id,-1) then  typing.typing_gen_res_group(null, t.res_group_id)else step.res_group_id end
657from mc_step step
658  WHERE exists(SELECT 1 FROM "typing_visits" t JOIN mc_step s ON s.id = t.id)
659and exists(SELECT 1 FROM sr_res_group s WHERE s.id = t.res_group_id and is_system =false)
660and t.doctor_id is not null
661and t.id is not null
662and t.id = step.id
663;
664
665
666
667
668-- 3.3 Oбновляю шаги
669update mc_step st set
670 admission_date = t.admission_date,
671 outcome_date =  coalesce(t.outcome_date,t.admission_date),
672 profile_id = t.profile_id,
673 mes_id = t.mes_id,
674 standard_id = t.standard_id,
675 res_group_id =  t.res_group_id,
676 result_id = t.result_id,
677 reason_id = t.stepResultReason_id,
678 outcome_id = t.outcome_id,
679 vmp_method_id =t.vmp_method_id,
680 vmp_type_id = t.vmp_type_id
681from "typing_visits" t where st.id = t.id and
682 exists (select 1 from mc_case c where c.id = t.case_id) and exists (select 1 from mc_step st where st.id = t.id) and t.admission_date is not null and
683 exists (select 1 from plc_visit_place x where x.id = t.place_id) and
684 (exists (select 1 from md_profile x where x.id = t.profile_id) or t.profile_id is null) and
685 (exists (select 1 from md_mes x where x.id = t.mes_id) or t.mes_id is null) and
686 (exists (select 1 from md_standard x where x.id = t.standard_id) or t.standard_id is null) and
687 --(exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null) and
688 (exists (select 1 from mc_case_init_goal x where x.id = t.goal_id)) and
689 (exists (select 1 from plc_initiator x where x.id = t.initiator_id) or t.initiator_id is null) and
690 (exists (select 1 from plc_visit_type x where x.id = t.type_id) or t.type_id is null) and
691 (exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null) and
692 (exists (select 1 from mc_step_result x where x.id = t.result_id) or t.result_id is null) and
693 (exists (select 1 from mc_step_result_reason x where x.id = t.stepResultReason_id) or t.stepResultReason_id is null) and
694 (exists (select 1 from mc_step_care_result x where x.id = t.outcome_id) or t.outcome_id is null)
695and (exists(SELECT 1 FROM mc_vmp_method x WHERE x.id = t.vmp_method_id) or  t.vmp_method_id is null)
696and (exists(SELECT 1 FROM mc_vmp_type x WHERE x.id = t.vmp_type_id) or t.vmp_type_id is null)
697;
698
699
700
701
702
703
704
705update plc_visit v set
706 goal_id = t.goal_id,
707 initiator_id = t.initiator_id,
708 place_id = t.place_id,
709 type_id = t.type_id,
710 is_viewed = t.is_viewed,
711 is_needed = t.is_needed,
712 is_sanitized = t.is_sanitized
713from "typing_visits" t  CROSS JOIN "typing_case" c   where v.id = t.id and c.case_type_id <> 2 and
714 exists (select 1 from mc_case c where c.id = t.case_id) and exists (select 1 from mc_step st where st.id = t.id) and t.admission_date is not null and
715 exists (select 1 from plc_visit_place x where x.id = t.place_id) and
716 (exists (select 1 from md_profile x where x.id = t.profile_id) or t.profile_id is null) and
717 (exists (select 1 from md_mes x where x.id = t.mes_id) or t.mes_id is null) and
718 (exists (select 1 from md_standard x where x.id = t.standard_id) or t.standard_id is null) and
719 (exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null) and
720 (exists (select 1 from mc_case_init_goal x where x.id = t.goal_id)) and
721 (exists (select 1 from plc_initiator x where x.id = t.initiator_id) or t.initiator_id is null) and
722 (exists (select 1 from plc_visit_type x where x.id = t.type_id) or t.type_id is null) and
723 (exists (select 1 from mc_step_result x where x.id = t.result_id) or t.result_id is null) and
724 (exists (select 1 from mc_step_care_result x where x.id = t.outcome_id) or t.outcome_id is null) and
725 (exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null) and
726 (exists (select 1 from mc_step_result x where x.id = t.result_id) or t.result_id is null) and
727 (exists (select 1 from mc_step_care_result x where x.id = t.outcome_id) or t.outcome_id is null)
728
729 ;
730
731update sr_res_group_relationship rgr set resource_id = t.doctor_id
732from "typing_visits" t, sr_res_role r where t.res_group_id = rgr.group_id and r.id = rgr.role_id and r.code = ''DOCTOR'' and t.doctor_id is not null 
733and exists(SELECT 1 FROM "typing_visits" t JOIN mc_step s ON s.id = t.id)
734and exists(SELECT 1 FROM sr_res_group s WHERE s.id = t.res_group_id and is_system = true);
735 
736
737
738 update hsp_record h set
739 mes_id = t.mes_id,
740 issue_planned_date = CASE when exists(SELECT 1 FROM mc_step_result x WHERE is_closed = true and id in(SELECT result_id FROM "typing_visits")) then (select max(outcome_date) from "typing_visits" ) end,
741 funding_id =coalesce(t.funding_id, c.funding_id)
742 from "typing_visits" t CROSS JOIN "typing_case" c   where h.id = t.id and c.case_type_id = 2 and h.id = t.id  and exists (select 1 from mc_case c where c.id = t.case_id) and exists (select 1 from mc_step st where st.id = t.id) and t.admission_date is not null ;
743
744
745
746-- 3.4 Добавляю ошибку, если обновляемый шаг не существует
747update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден шаг по id = '', id, ''", "column_name":"id"}'')
748 where id is not null and not exists (select 1 from mc_step x where x.id = t.id);
749
750update typing_visits set case_id = coalesce(c.id,c.new_id) from typing_case c;
751
752-- 3.5 Добавляю шаги, если нет id
753alter table "typing_visits" add column new_id integer;
754update "typing_visits" t
755set new_id = nextval(''mc_step_seq'')
756where id is null and
757 exists (select 1 from mc_case c where c.id = t.case_id) and t.admission_date is not null and
758 exists (select 1 from plc_visit_place x where x.id = t.place_id) and
759 (exists (select 1 from md_profile x where x.id = t.profile_id) or t.profile_id is null) and
760 (exists (select 1 from md_mes x where x.id = t.mes_id) or t.mes_id is null) and
761 (exists (select 1 from md_standard x where x.id = t.standard_id) or t.standard_id is null) and
762 (exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null) and
763 (exists (select 1 from mc_case_init_goal x where x.id = t.goal_id)) and
764 (exists (select 1 from plc_initiator x where x.id = t.initiator_id) or t.initiator_id is null) and
765 (exists (select 1 from plc_visit_type x where x.id = t.type_id) or t.type_id is null) and
766 (exists (select 1 from mc_step_result x where x.id = t.result_id) or t.result_id is null) and
767 (exists (select 1 from mc_step_care_result x where x.id = t.outcome_id) or t.outcome_id is null) 
768 and(exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null)
769;
770
771update "typing_visits" t
772set new_id = nextval(''mc_step_seq'')
773where  id is null and exists (select 1 from mc_case c where c.id = t.case_id) and t.admission_date is not null
774and(exists(SELECT 1 FROM mc_deviation_reason x WHERE x.id = t.deviation_reason_id) or t.deviation_reason_id is null)
775and(exists(select 1 FROM mc_complexity_level x WHERE x.id = t.complexity_level_id) or t.complexity_level_id is null)
776and (exists(SELECT 1 FROM md_profile p WHERE p.id = t.profile_id) or t.profile_id is null) 
777and (exists(select 1 FROM mc_step_result x WHERE t.result_id = x.id)
778or  t.result_id is null)
779and (exists(SELECT 1 FROM mc_step_care_result x WHERE x.id = t.outcome_id ) or t.outcome_id is null)
780and (exists(SELECT 1 FROM mc_vmp_method x WHERE x.id = t.vmp_method_id) or  t.vmp_method_id is null)
781and (exists(SELECT 1 FROM mc_vmp_type x WHERE x.id = t.vmp_type_id) or t.vmp_type_id is null)
782
783;
784
785update "typing_visits" t
786set res_group_id = case when coalesce(res_group_id, doctor_id) is not null then typing.typing_gen_res_group(doctor_id, res_group_id) end;
787
788insert into mc_step(id, admission_date,admission_time, outcome_date,outcome_time, case_id, outcome_id, result_id, reason_id, profile_id, mes_id,csg_id, res_group_id,regimen_id,outcome_regimen_id,deviation_reason_id, vmp_method_id, vmp_type_id)
789select t.new_id, t.admission_date,coalesce(admission_time,''0:0:0''::TIME),  CASE when t.outcome_date is null then t.admission_date else t.outcome_date end,outcome_time,t.case_id, t.outcome_id, t.result_id, t.stepResultReason_id, t.profile_id, t.mes_id,coalesce(t.csg_id,standarts.csg_id),t.res_group_id, c.care_regimen_id, c.care_regimen_id,deviation_reason_id,coalesce(t.vmp_method_id,standarts.vmp_method_id),coalesce(t.vmp_type_id,standarts.vmp_type_id) from "typing_visits" t
790  cross join   "typing_case" c left join (select * from typing_standarts standarts limit 1)  standarts on true
791 where
792 t.new_id is not null --and case_id is not null and admission_date is not null and place_id is not null
793
794;
795
796
797insert into plc_visit(id, goal_id, initiator_id, place_id, type_id, is_viewed, is_needed, is_sanitized)
798select t.new_id, goal_id, initiator_id, place_id, type_id, is_viewed, is_needed, is_sanitized from "typing_visits" t  cross join   "typing_case" where case_type_id <> 2
799and t.new_id is not null and place_id is not null
800and exists(SELECT 1 FROM mc_step x WHERE x.id =t.new_id)
801and exists(SELECT 1 FROM plc_visit_place x WHERE x.id =t.place_id)
802and t.new_id is not null
803 --and case_id is not null and admission_date is not null and place_id is not null
804
805;
806
807
808insert into hsp_record(id,mes_id,funding_id,department_id, bed_profile_id,issue_planned_date,complexity_level_id)
809select t.new_id, mes_id ,coalesce(t.funding_id, "typing_case".funding_id), CASE WHEN step_num =''1'' then COALESCE(hosp_department_id, department_id) else coalesce(department_id, hosp_department_id) end,bed_profile_id, CASE when exists(SELECT 1 FROM mc_step_result x WHERE is_closed = true and id in(SELECT result_id FROM "typing_visits")) then (select max(outcome_date) from "typing_visits" ) end,complexity_level_id from "typing_visits" t cross join   "typing_case"   where case_type_id = 2
810and t.new_id is not null 
811and exists(SELECT 1 FROM mc_step s WHERE s.id = t.new_id)
812and exists(select 1 FROM pim_department x WHERE x.id =COALESCE(hosp_department_id, department_id))
813;
814
815
816
817update hsp_record h
818set previous_id = (SELECT max(t.new_id) FROM "typing_visits" t WHERE t.new_id<h.id)
819FROM "typing_visits" t WHERE h.id in (SELECT new_id FROM "typing_visits")
820and exists( select 1 from hsp_record  where id = (SELECT max(t.new_id) FROM "typing_visits" t WHERE t.new_id<h.id))
821;
822
823update "typing_visits" set id = new_id where new_id is not null;
824
825-- 3.6 Добавляю все ошибки при добавлении либо обновлении шага
826update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не заполнен admission_date", "column_name":"admission_date"}'')
827 where t.admission_date is null;
828update "typing_visits" t set errors = concat(t.errors, ''{"level":"step", "message":"не заполнен place_id", "column_name":"place_id"}'')
829from  "typing_case" c
830 where t.place_id is null
831 and not exists (SELECT 1 FROM  "typing_case" where case_type_id = 2);
832update "typing_visits" t set errors = concat(t.errors, ''{"level":"step", "message":"неверный place_id = ", "column_name":"place_id"}'')
833from  "typing_case" c
834 where not exists(SELECT 1 FROM plc_visit_place x WHERE x.id =t.place_id) and t.place_id is not null;
835update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден profile_id = '', profile_id, ''", "column_name":"profile_id"}'')
836 where profile_id is not null and not exists (select 1 from md_profile x where x.id = t.profile_id)
837;
838update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден mes_id = '', mes_id, ''", "column_name":"mes_id"}'')
839 where mes_id is not null and not exists (select 1 from md_mes x where x.id = t.mes_id);
840update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден standard_id = '', standard_id, ''", "column_name":"standard_id"}'')
841 where standard_id is not null and not exists (select 1 from md_standard x where x.id = t.standard_id);
842update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден res_group_id = '', res_group_id, ''", "column_name":"res_group_id"}'')
843 where res_group_id is not null and not exists (select 1 from sr_res_group x where x.id = t.res_group_id);
844update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден goal_id = '', goal_id, ''", "column_name":"goal_id"}'')
845 where not exists (select 1 from mc_case_init_goal x where x.id = t.goal_id);
846update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден initiator_id = '', initiator_id, ''", "column_name":"initiator_id"}'')
847 where initiator_id is not null and not exists (select 1 from plc_initiator x where x.id = t.initiator_id);
848update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден type_id = '', type_id, ''", "column_name":"type_id"}'')
849 where type_id is not null and not exists (select 1 from plc_visit_type x where x.id = t.type_id);
850update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден result_id = '', result_id, ''", "column_name":"result_id"}'')
851 where result_id is not null and not exists (select 1 from mc_step_result x where x.id = t.result_id);
852update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден outcome_id = '', outcome_id, ''", "column_name":"outcome_id"}'')
853 where outcome_id is not null and not exists (select 1 from mc_step_care_result x where x.id = t.outcome_id);
854
855update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден result_id '', result_id, ''", "column_name":"result_id"}'')
856 where not exists(SELECT 1 FROM "typing_visits" WHERE result_id is not null);
857update "typing_visits" t set  errors = concat(t.errors, ''{"level":"step", "message": "Не найден department_id =  '', department_id, ''", "column_name":"outcome_id"}'')
858FROM "typing_case" tc where not exists(select 1 FROM pim_department x WHERE x.id =t.department_id) and tc.case_type_id = 2 and t.department_id is not null;
859update "typing_visits" t set  errors = concat(t.errors, ''{"level":"step", "message": "Не найден hosp_department_id =  '', hosp_department_id, ''", "column_name":"outcome_id"}'')
860FROM "typing_case" tc where not exists(select 1 FROM pim_department x WHERE x.id =t.hosp_department_id) and tc.case_type_id = 2 and t.hosp_department_id is not null;
861
862update "typing_visits" t set  errors = concat(t.errors, ''{"level":"step", "message": "МО сотрудника и МО случая не совпадают  '', doctor_id, ''", "column_name":"doctor_id"}'')
863where not exists (select 1 
864	 from pim_employee_position_resource epr
865     join pim_employee_position ep on epr.employee_position_id = ep.id
866     join pim_employee e on ep.employee_id = e.id
867	 WHERE epr.id = t.doctor_id
868	 and e.organization_id = t.clinic_id)
869and t.doctor_id is not null;
870
871 update "typing_visits" t set errors= concat(t.errors, ''{"level":"step", "message": "Дата поступления в зог меньше даты выбытия из предыдущего!", "column_name":"doctor_id"}'')
872	from typing_visits t1 where t.admission_date < t1.outcome_date and t.outcome_date >t1.outcome_date;
873
874
875
876-- 3.5 Коэффициент курации
877alter table "typing_cur"
878 add column id integer,
879 add column criteria_id integer,
880 add column new_id integer,
881 add column value numeric,
882 add column errors text default ''''
883;
884
885
886update typing_cur set
887id = typing.to_int(_id),
888criteria_id=typing.to_int(_criteria_id),
889value = to_numeric(_value)
890;
891
892--- Обновляю, если есть id
893update mc_step_cur_coef_criteria c
894set value = t.value,
895criteria_id = t.criteria_id
896FROM typing_cur t WHERE c.id = t.id
897;
898
899
900
901update typing_cur c
902set new_id = nextval(''mc_step_cur_seq'')
903WHERE exists(SELECT 1 FROM mc_cur_coef_criteria x WHERE x.id = c.criteria_id)
904and value is not null
905;
906
907insert into mc_step_cur_coef_criteria(id, criteria_id,step_id,value)
908SELECT c.new_id, c.criteria_id,v.new_id, c.value FROM typing_cur c JOIN typing_visits v ON c.step_num = v.step_num
909WHERE c.new_id is not null and v.new_id is not null;
910
911
912-- Добавляю ошибки
913
914update "typing_cur" t set  errors = concat(t.errors, ''{"level":"cur", "message":" Не указан criteria_id '', criteria_id, ''", "column_name":"criteria_id"}'')
915 where criteria_id is null;
916update "typing_cur" t set  errors = concat(t.errors, ''{"level":"cur", "message":" Не указан step_num'', step_num, ''", "column_name":"step_num"}'')
917where step_num is null;
918update "typing_cur" t set  errors = concat(t.errors, ''{"level":"cur", "message":" Не указан value'', value, ''", "column_name":"value"}'')
919 where value is null;
920update "typing_cur" t set  errors = concat(t.errors, ''{"level":"cur", "message":" Не найден criteria_id ='', criteria_id, ''", "column_name":"criteria_id"}'')
921 where criteria_id is not null and not exists(SELECT 1 FROM mc_cur_coef_criteria x WHERE x.id = t.criteria_id);
922
923
924-- 4. Услуги-----------------------------------------------------------------------------------------------------------------------------
925-- 4.1 Добавление реальных колонок
926alter table "typing_services"
927 add column id integer,
928 add column step_id integer,
929 add column case_id integer,
930 add column service_id integer,
931 add column bdate date,
932 add column funding_id integer,
933 add column quantity integer,
934 add column res_group_id integer,
935 add column mkb_id integer,
936 add column clinic_id integer,
937 add column patient_id integer,
938 add column new_id integer,
939 add column doctor_id integer,
940 add column cul numeric,
941 add column is_refused boolean,
942 add column diag_type_id int,
943 add column diag_disease_type_id int,
944 add column diag_stage_id int,
945 add column org_id int,
946add column result_type_id int,
947 add column errors text default ''''
948;
949
950-- 4.2 Заполнение реальных колонок
951update "typing_services" set
952 case_id = (select coalesce(id,new_id) from "typing_case"),
953 id = typing.to_int(_id),
954 step_id = typing.to_int(_step_id),
955 service_id = typing.to_int(_service_id),
956 bdate =  typing.to_dt(_bdate),
957 funding_id = typing.to_int(_funding_id),
958 quantity = typing.to_int(_quantity),
959 res_group_id = typing.to_int(_res_group_id),
960 mkb_id = typing.to_int(_mkb_id),
961 is_refused = CASE when _is_refused is null then false else _is_refused = ''1'' end,
962 doctor_id = typing.to_int(_doctor_id),
963 clinic_id = (select clinic_id from "typing_case" where clinic_id is not null),
964 patient_id = (select patient_id from "typing_case"),
965 diag_stage_id = typing.to_int(_diag_stage_id),
966 diag_type_id = typing.to_int(_diag_type_id) ,
967 diag_disease_type_id= typing.to_int(_diag_disease_type_id),
968 cul = to_numeric(_cul),
969 org_id = typing.to_int(_org_id),
970 result_type_id = typing.to_int(_result_type_id)
971;
972
973
974update "typing_services" s
975set res_group_id = group_id
976FROM "typing_resources" r
977WHERE (s.step_num = r.step_num
978or (r.step_num is null)) --- если для всего случая - r.step_id должен быть пустой 
979and s.res_group_id is null
980;
981
982-- 4.3 Обновляю услуги
983update sr_srv_rendered r set
984       bdate = t.bdate, funding_id = t.funding_id, res_group_id = t.res_group_id, service_id = t.service_id, customer_id = t.patient_id, org_id = t.clinic_id, cul = t.cul,is_amalgam_filling =t.is_amalgam_filling
985  from "typing_services" t
986 where t.id = r.id and exists (select 1 from mc_case c where c.id = t.case_id)
987   and (exists (select 1 from fin_funding_source_type x where x.id = t.funding_id) or t.funding_id is null)
988   --and (exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null)
989   and (exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null)
990   and exists (select 1 from sr_service x where x.id = t.service_id)
991   and exists (select 1 from pci_patient x where x.id = t.patient_id)
992   and exists (select 1 from md_clinic x where x.id = t.clinic_id)
993   and (exists (select 1 from mc_step x where x.id = t.step_id) or t.step_id is null)
994   and (exists (select 1 from md_diagnosis x where x.id = t.mkb_id) or t.mkb_id is null)
995;
996
997update md_srv_rendered r set
998 step_id = t.step_id, diagnosis_id = t.mkb_id, case_id = t.case_id, health_group_id =t.health_group_id
999from "typing_services" t
1000where t.id = r.id and exists (select 1 from mc_case c where c.id = t.case_id) and
1001 (exists (select 1 from fin_funding_source_type x where x.id = t.funding_id) or t.funding_id is null) and
1002 --(exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null) and
1003 (exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null) and
1004 (exists (select 1 from sr_service x where x.id = t.service_id)) and
1005 exists (select 1 from pci_patient x where x.id = t.patient_id) and
1006 exists (select 1 from md_clinic x where x.id = t.clinic_id) and
1007 (exists (select 1 from mc_step x where x.id = t.step_id) or t.step_id is null) and
1008 (exists (select 1 from md_diagnosis x where x.id = t.mkb_id) or t.mkb_id is null) and
1009 (exists (select 1 from mc_health_group x where x.id =t.health_group_id) or t.health_group_id is null)
1010;
1011
1012
1013update sr_srv_result r
1014set value = result_value,
1015result_type_id = t.result_type_id
1016from "typing_services" t
1017where t.id is not null
1018and r.service_id = t.id 
1019and r.id = t.srv_result_id
1020and (exists (select 1 from sr_srv_result_type x where x.id = t.result_type_id) or t.result_type_id is null)
1021;
1022
1023
1024
1025-- 4.4 Добавляю ошибку (отсутствует id услуги)
1026update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найдена услуга по id = '', id, ''", "column_name":"id"}'')
1027 where id is not null and not exists (select 1 from sr_srv_rendered x where x.id = t.id);
1028
1029-- 4.5 Добавляю услуги, если нет id
1030-- 4.5 Добавляю услуги, если нет id
1031update "typing_services" t set new_id = nextval(''sr_srv_rendered_seq'')
1032where id is null and exists (select 1 from mc_case c where c.id = t.case_id) and
1033(exists (select 1 from fin_funding_source_type x where x.id = t.funding_id) or t.funding_id is null) and
1034 --(exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null) and
1035 (exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null) and
1036--(exists (select 1 from sr_service x where x.id = t.service_id)) and
1037exists (select 1 from pci_patient x where x.id = t.patient_id) and
1038 exists (select 1 from md_clinic x where x.id = t.clinic_id) and
1039 (exists (select 1 from mc_step x where x.id = t.step_id) or t.step_id is null) and
1040 (exists (select 1 from md_diagnosis x where x.id = t.mkb_id) or t.mkb_id is null)
1041and  (exists (select 1 from mc_health_group x where x.id =t.health_group_id) or t.health_group_id is null)
1042 ;
1043
1044
1045insert into sr_srv_rendered(id, bdate, service_id, customer_id, quantity, org_id, is_rendered, res_group_id, cul, funding_id,comment, is_refused, tooth_number,is_amalgam_filling)
1046select s.new_id,
1047CASE when s.bdate is null then v.admission_date else s.bdate end,
1048 s.service_id, s.patient_id, s.quantity, 
1049COALESCE(s.org_id,s.clinic_id),
1050 true,
1051      coalesce(typing.typing_gen_res_group(s.doctor_id, s.res_group_id),v.res_group_id),
1052     s.cul, s.funding_id, s._note, 
1053CASE when (s.is_refused is null) then false else s.is_refused end,
1054    s.tooth_number, is_amalgam_filling
1055  from "typing_services" s left JOIN "typing_visits"  v ON v.step_num =s.step_num  where s.new_id is not null and s.service_id is not null
1056  and (exists (select 1 from sr_service x where x.id = s.service_id and x.org_id = s.org_id)
1057   or exists (select 1 from sr_service x where x.id = s.service_id and x.org_id = s.clinic_id))
1058
1059;
1060insert into md_srv_rendered(id, step_id, case_id, diagnosis_id,anatomic_zone_id, health_group_id)
1061select s.new_id, 
1062CASE when (s.org_id is null or s.org_id = s.clinic_id) then coalesce(v.id,v.new_id) end,
1063CASE when (s.org_id is null or s.org_id = s.clinic_id) then s.case_id end,
1064CASE when (s.org_id is null or s.org_id = s.clinic_id) then s.mkb_id end,
1065anatomic_zone_id,health_group_id
1066from "typing_services" s left join "typing_visits" v on v.step_num = s.step_num
1067where s.new_id is not null
1068and exists(SELECT 1 FROM mc_case x WHERE  x.id = s.case_id)
1069  and (exists (select 1 from sr_service x where x.id = s.service_id and x.org_id = s.org_id)
1070   or exists (select 1 from sr_service x where x.id = s.service_id and x.org_id = s.clinic_id))
1071and (exists(select 1 from md_anatomic_zone x where x.id = s.anatomic_zone_id) or  s.anatomic_zone_id is null)
1072
1073;
1074insert into sr_srv_result (id,value,service_id, result_type_id)
1075select nextval(''sr_srv_result_id_seq''),result_value,coalesce(s.id,s.new_id),result_type_id
1076from typing_services  s where s.result_value is not null;
1077
1078
1079SELECT typing.typing_insert_into_disp();
1080
1081update "typing_services" s set id = new_id where new_id is not null and s.service_id is not null;
1082
1083-- 4.6 Добавляю все ошибки услуг
1084update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден funding_id = '', funding_id, ''", "column_name":"funding_id"}'')
1085 where t.funding_id is not null and not exists (select 1 from fin_funding_source_type x where x.id = t.funding_id);
1086update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден res_group_id = '', res_group_id, ''", "column_name":"res_group_id"}'')
1087 where t.res_group_id is not null and not exists (select 1 from sr_res_group x where x.id = t.res_group_id);
1088--update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден service_id = '', service_id, ''", "column_name":"service_id"}'')
1089-- where not exists (select 1 from sr_service x where x.id = t.service_id);
1090update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден step_id = '', step_id, ''", "column_name":"step_id"}'')
1091 where t.step_id is not null and not exists (select 1 from mc_step x where x.id = t.step_id);
1092--update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден mkb_id = '', mkb_id, ''", "column_name":"mkb_id"}'')
1093-- where t.mkb_id is not null and not exists (select 1 from md_diagnosis x where x.id = t.mkb_id);
1094update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"Услуга «'', (select name from sr_service s where s.id = t.service_id limit 1) ,''»  с id='', service_id, '' не оказывается в данной МО", "column_name":"service_id"}'')
1095 where not exists (select 1 from sr_service x where x.id = t.service_id and x.org_id =t.org_id)
1096and not exists (select 1 from sr_service x where x.id = t.service_id and x.org_id =t.clinic_id)
1097and t.service_id is not null;
1098update "typing_services" t set  errors = concat(t.errors, ''{"level":"srv", "message": МО сотрудника и МО случая не совпадают"  '', doctor_id, ''", "column_name":"doctor_id"}'')
1099where not exists (select 1 
1100	 from pim_employee_position_resource epr
1101     join pim_employee_position ep on epr.employee_position_id = ep.id
1102     join pim_employee e on ep.employee_id = e.id
1103	 WHERE epr.id = t.doctor_id
1104	 and e.organization_id = t.clinic_id)
1105and t.doctor_id is not null;
1106
1107update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден с result_type_id='', result_type_id, ''", "column_name":"result_type_id"}'')
1108 where not exists (select 1 from sr_srv_result_type x where x.id = t.result_type_id) and t.result_type_id is not null;
1109update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден с srv_result_id='', srv_result_id, ''", "column_name":"result_type_id"}'')
1110where not exists (select 1 from sr_srv_result x where x.id = t.srv_result_id) and t.srv_result_id is not null;
1111
1112update "typing_services" t set  errors = concat(t.errors, ''{"level":"srv", "message": "Дата оказания услуги '', t.bdate, '', не должны быть меньше даты открытия случая  '',  (select x.admission_date::text  from "typing_visits"  x order by x.admission_date limit 1),  ''", "column_name":"bdate"}'')
1113where t.bdate::date<(select x.admission_date::text  from "typing_visits"  x order by x.admission_date limit 1)::date;
1114 ;
1115
1116
1117
1118
1119-- 5. Диагнозы-----------------------------------------------------------------------------------------------------------------------------
1120-- 5.1 Добавляю реальные колонки
1121alter table "typing_diagnos"
1122 add column id integer,
1123 add column mkb_id integer,
1124 add column disease_type_id integer,
1125 add column is_main boolean,
1126 add column stage_id integer,
1127 add column step_id integer,
1128 add column disp_id integer,
1129 add column nosol_registr_id integer,
1130 add column dispensary_group_id integer,
1131 add column reg_in_dt date,
1132 add column reg_out_dt date,
1133 add column reg_in_doctor_id integer,
1134 add column reg_out_doctor_id integer,
1135 add column reg_stage_id integer,
1136 add column reg_out_reason_id integer,
1137 add column case_id integer,
1138 add column patient_id integer,
1139 add column clinic_id integer,
1140 add column type_id integer,
1141 add column new_id integer,
1142 add column errors text default ''''
1143
1144;
1145
1146-- 5.2 Заполняю реальные колонки
1147update "typing_diagnos" set
1148 id = typing.to_int(_id),
1149 mkb_id = typing.to_int(_mkb_id),
1150 disease_type_id = typing.to_int(_disease_type_id),
1151 is_main = case when _is_main = ''true'' or _is_main = ''1'' then true else false end,
1152 stage_id = typing.to_int(_stage_id),
1153 disp_id = typing.to_int(_disp_id),
1154 nosol_registr_id = typing.to_int(_nosol_registr_id),
1155 dispensary_group_id = typing.to_int(_dispensary_group_id),
1156 reg_in_dt = typing.to_dt(_reg_in_dt),
1157 reg_out_dt = typing.to_dt(_reg_out_dt),
1158 reg_in_doctor_id = typing.to_int(_reg_in_doctor_id),
1159 reg_out_doctor_id = typing.to_int(_reg_out_doctor_id),
1160 reg_stage_id = typing.to_int(_reg_stage_id),
1161 reg_out_reason_id = typing.to_int(_reg_out_reason_id),
1162 type_id = typing.to_int(_type_id)
1163;
1164
1165
1166update "typing_diagnos" d set
1167 case_id = coalesce(v.id, v.new_id),
1168 patient_id = v.patient_id, 
1169 clinic_id = v.clinic_id
1170from "typing_case" v
1171;
1172
1173update "typing_diagnos" d set
1174 step_id = v.id
1175from "typing_visits" v where v.step_num = d.step_num
1176;
1177
1178
1179
1180-- 5.3 Обновляю диагнозы, если есть id
1181update mc_diagnosis d set
1182 diagnos_id = t.mkb_id,
1183 disease_type_id = t.disease_type_id,
1184 is_main = t.is_main,
1185 case_id = t.case_id,
1186 step_id = t.step_id,
1187 patient_id = t.patient_id,
1188 stage_id = t.stage_id,
1189 type_id = t.type_id
1190
1191from "typing_diagnos" t
1192 where d.id = t.id and
1193  exists (select 1 from pci_patient x where x.id = t.patient_id) and
1194  exists (select 1 from md_clinic x where x.id = t.clinic_id) and
1195  exists (select 1 from mc_case c where c.id = t.case_id) and
1196  exists (select 1 from md_diagnosis x where x.id = t.mkb_id) and
1197  (exists (select 1 from mc_disease_type x where x.id = t.disease_type_id) or t.disease_type_id is null) and
1198  (exists (select 1 from mc_stage x where x.id = t.stage_id) or t.stage_id is null) and
1199  (exists (select 1 from mc_diagnosis_type x where x.id = t.type_id) or t.type_id is null)
1200;
1201
1202-- 5.4 Добавляю ошибку (отсутствует id диагноза)
1203update "typing_diagnos" t set errors = concat(errors, ''{"level":"dgn", "message":"не найден диагноз по id = '', id, ''", "column_name":"id"}'')
1204 where id is not null and not exists (select 1 from mc_diagnosis x where x.id = t.id);
1205
1206-- 5.5 добавляю диагнозы, если нет id
1207
1208update "typing_diagnos" t set new_id = nextval(''mc_diagnosis_seq'')
1209 where id is null and
1210  exists (select 1 from pci_patient x where x.id = t.patient_id) and
1211  exists (select 1 from md_clinic x where x.id = t.clinic_id) and
1212  exists (select 1 from mc_case c where c.id = t.case_id) and
1213  exists (select 1 from md_diagnosis x where x.id = t.mkb_id) and
1214  (exists (select 1 from mc_disease_type x where x.id = t.disease_type_id) or t.disease_type_id is null) and
1215  (exists (select 1 from mc_stage x where x.id = t.stage_id) or t.stage_id is null) and
1216  (exists (select 1 from mc_diagnosis_type x where x.id = t.type_id) or t.type_id is null);
1217
1218update  "typing_diagnos" t
1219set stage_id =4
1220WHERE step_id =(select max(step_id) FROM "typing_diagnos" t)
1221and not exists(select 1 FROM "typing_diagnos"  WHERE stage_id =4);
1222
1223insert into mc_diagnosis(id, diagnos_id, disease_type_id, case_id, patient_id, stage_id, step_id, type_id, is_main,establishment_date, note)
1224 select d.new_id, d.mkb_id, d.disease_type_id, d.case_id, d.patient_id, d.stage_id, d.step_id, d.type_id, CASE when(select count(new_id)>1 FROM "typing_diagnos" WHERE is_main = true) then false else d.is_main end, admission_date, note from "typing_diagnos" d LEFT JOIN "typing_visits" v ON d.step_id=v.id  where d.new_id is not null
1225--and (exists(SELECT 1 FROM mc_step x WHERE x.id =d.step_id))
1226and d.mkb_id is not null
1227;
1228
1229update mc_diagnosis d
1230set is_main = true
1231FROM (SELECT max(new_id) as id FROM typing_diagnos WHERE (select count(new_id)>1 FROM "typing_diagnos" WHERE is_main = true) 
1232group by step_num) x
1233WHERE x.id = d.id
1234;
1235
1236update "typing_diagnos" set id = new_id where new_id is not null;
1237
1238
1239update md_srv_rendered msr
1240set diagnosis_id = CASE when msr.diagnosis_id is null then d.mkb_id else msr.diagnosis_id end,
1241step_id = v.id
1242from "typing_services" s JOIN "typing_visits" v ON v.step_num=s.step_num  JOIN "typing_diagnos" d ON d.step_id = v.id  where s.new_id is not null and msr.id =s.new_id
1243and (s.org_id is null or s.org_id = s.clinic_id)
1244and d.mkb_id <> -1
1245;
1246
1247
1248
1249
1250-- 5.6 добавляю все ошибки диагнозов
1251update "typing_diagnos" t set errors = concat(errors, ''{"level":"dgn", "message":"не найден mkb_id = '', _mkb_id, ''", "column_name":"mkb_id"}'')
1252WHERE mkb_id =-1;
1253update "typing_diagnos" t set errors = concat(errors, ''{"level":"dgn", "message":"не найден disease_type_id = '', disease_type_id, ''", "column_name":"disease_type_id"}'')
1254 where t.disease_type_id is not null and not exists (select 1 from mc_disease_type x where x.id = t.disease_type_id);
1255update "typing_diagnos" t set errors = concat(errors, ''{"level":"dgn", "message":"не найден stage_id = '', stage_id, ''", "column_name":"stage_id"}'')
1256 where t.stage_id is not null and not exists (select 1 from mc_stage x where x.id = t.stage_id);
1257update "typing_diagnos" t set errors = concat(errors, ''{"level":"dgn", "message":"не найден type_id = '', type_id, ''", "column_name":"type_id"}'')
1258 where t.type_id is not null and not exists (select 1 from mc_diagnosis_type x where x.id = t.type_id);
1259
1260-- 6. Больничный----------------------------------------------------------------------------
1261-- 6.1 Обновление больничного
1262update md_sicklist sl set disability_reason_id = c.disability_reason_id, disability_from_dt = c.disability_from_dt, disability_to_dt = c.disability_to_dt, code = c.sl_code, clinic_id = c.clinic_id, patient_id = c.patient_id
1263 from "typing_case" c
1264  where c.sicklist_id = sl.id and (c.disability_reason_id is null or exists(select 1 from md_sl_disability_reason x where x.id = c.disability_reason_id));
1265
1266-- 6.2 Добавляю ошибку (не найден id больничного)
1267update "typing_case" t set errors = concat(errors, ''{"level":"sl", "message":"не найден sicklist_id = '', sicklist_id, ''", "column_name":"sicklist_id"}'')
1268 where t.sicklist_id is not null and not exists (select 1 from md_sicklist x where x.id = t.sicklist_id);
1269
1270-- 6.3 Добавляю больничный
1271alter table "typing_case" add column new_sl_id integer;
1272
1273update "typing_case" c set new_sl_id = nextval(''md_sicklist_seq'')
1274 where sl_code is not null and sl_code != '''' and (c.disability_reason_id is null or exists(select 1 from md_sl_disability_reason x where x.id = c.disability_reason_id));
1275
1276update "typing_case" c
1277set sl_surname= i.surname,
1278sl_name = i.name,
1279sl_patr_name = i.patr_name
1280FROM pim_individual i WHERE i.id =c.patient_id
1281and (sl_surname is null
1282or sl_name is null
1283or sl_patr_name is null or
1284sl_surname = ''''
1285or sl_name =''''
1286or sl_patr_name =''''
1287)
1288; 
1289
1290insert into md_sicklist(id, disability_from_dt, disability_to_dt, code, disability_reason_id, clinic_id, patient_id, type_id,workplace_type_id,issue_dt,workplace_print,ready_to_work_dt,final_diagnosis_id,state_id,name,surname,patr_name,case_id)
1291
1292select new_sl_id,
1293disability_from_dt,
1294disability_to_dt,
1295sl_code,
1296disability_reason_id,
1297clinic_id,
1298patient_id,
12991,
13001,
1301disability_from_dt,
1302workplace_print,
1303ready_to_work_dt,
1304x.id,
1305CASE when md_sicklist_state is null then 3 else md_sicklist_state end,
1306sl_name,
1307sl_surname,
1308sl_patr_name,
1309new_id
1310from "typing_case" c  cross join(select md.id from "typing_diagnos" x
1311Join mc_diagnosis mc on mc.id = x.id  
1312join md_diagnosis md on md.id = mc.diagnos_id  
1313left join mc_stage s on s.id = mc.stage_id and mc.is_main
1314order by stage_order desc, id
1315limit 1) x
1316where new_sl_id is not null;
1317
1318
1319
1320insert into md_sicklist_period
1321SELECT nextval(''md_sicklist_period__seq''),
1322disability_from_dt,
1323disability_to_dt,
1324employee_position_id,
1325new_sl_id,
1326null
1327from "typing_case" c
1328where new_sl_id is not null
1329and exists(select 1 FROM pim_employee_position p WHERE p.id =c.employee_position_id)
1330and employee_position_id is not null
1331and new_sl_id is not null
1332;
1333
1334
1335
1336
1337update "typing_case" set sicklist_id = new_sl_id where new_sl_id is not null;
1338
1339-- 6.4 Добавляю ошибки больничного
1340update "typing_case" t set errors = concat(errors, ''{"level":"sl", "message":"не найден disability_reason_id = '', disability_reason_id, ''", "column_name":"disability_reason_id"}'')
1341 where t.disability_reason_id is not null and not exists (select 1 from md_sl_disability_reason x where x.id = t.disability_reason_id);
1342update "typing_case" t set errors = concat(errors, ''{"level":"sl", "message":"не указан номер больничного", "column_name":"sl_code"}'')
1343 where (sl_code is null or sl_code = '''') and t.disability_reason_id is not null; --disability_reason_id - это признак того, что больничный заполнен
1344  update "typing_case" t set errors = concat(errors, ''{"level":"sl", "message":"не найден employee_position_id = '', employee_position_id, ''", "column_name":"employee_position_id"}'')
1345 where t.employee_position_id is not null and not exists (select 1 from pim_employee_position x where x.id = t.employee_position_id );
1346 update "typing_case" t set errors = concat(errors, ''{"level":"sl", "message":"не указан employee_position_id", "column_name":"employee_position_id"}'')
1347 where (employee_position_id is null) and t.disability_reason_id is not null;
1348
1349 
1350
1351
1352-- 7. Направления----------------------------------------------------------------------------
1353-- 7.1 Обновляю направления
1354update md_referral r set ref_organization_id = c.ref_organization_id, ref_doctor_id = c.ref_doctor_id, diagnosis_id = c.ref_mkb_id, referral_date = c.referral_date,
1355 recv_organization_id = c.recv_organization_id, referral_type_id = c.referral_type_id
1356from "typing_case" c where c.referral_id = r.id and c.referral_date is not null and
1357 (exists(select 1 from pim_organization o where o.id = c.ref_organization_id)) and
1358 (c.recv_organization_id is null or exists(select 1 from pim_organization o where o.id = c.recv_organization_id)) and
1359 (c.ref_doctor_id is null or exists(select 1 from md_employee_position x where x.id = c.ref_doctor_id)) and
1360 (c.ref_mkb_id is null or exists(select 1 from md_diagnosis x where x.id = c.ref_mkb_id)) and
1361 (c.referral_type_id is null or exists(select 1 from md_referral_type x where x.id = c.referral_type_id)) and
1362  exists (select 1 from pci_patient x where x.id = c.patient_id) and
1363  exists (select 1 from md_clinic x where x.id = c.clinic_id)
1364 -- exists (select 1 from mc_case x where x.id = c.id)
1365;
1366-- 7.2 Добавляю ошибку (не найден id направления в бд)
1367update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден referral_id = '', referral_id, ''", "column_name":"referral_id"}'')
1368 where t.referral_id is not null and not exists (select 1 from md_referral x where x.id = t.referral_id);
1369
1370-- 7.3 Добавляю направление
1371alter table "typing_case" add column new_ref_id integer;
1372
1373update "typing_case" c set new_ref_id = nextval(''md_referral_seq'')
1374where c.referral_id is null and
1375 c.referral_date is not null and
1376 (exists(select 1 from pim_organization o where o.id = c.ref_organization_id)) and
1377 (c.recv_organization_id is null or exists(select 1 from pim_organization o where o.id = c.recv_organization_id)) and
1378 (c.ref_doctor_id is null or exists(select 1 from md_employee_position x where x.id = c.ref_doctor_id)) and
1379 (c.ref_mkb_id is null or exists(select 1 from md_diagnosis x where x.id = c.ref_mkb_id)) and
1380 (c.referral_type_id is null or exists(select 1 from md_referral_type x where x.id = c.referral_type_id)) and
1381  exists (select 1 from pci_patient x where x.id = c.patient_id) and
1382  exists (select 1 from md_clinic x where x.id = c.clinic_id) and
1383  exists (select 1 from mc_case x where x.id = c.id)
1384;
1385insert into md_referral(id, ref_organization_id, ref_doctor_id, diagnosis_id, referral_date, recv_organization_id, referral_type_id,patient_id,step_id)
1386select new_ref_id, ref_organization_id, ref_doctor_id, ref_mkb_id, referral_date, recv_organization_id, referral_type_id,c.patient_id, v.new_id from "typing_case" c cross join typing_visits v
1387 where new_ref_id is not null
1388 and v.step_num = ''1'' ;
1389
1390--update "typing_case" set id = new_ref_id where new_ref_id is not null;
1391
1392-- 7.4 Добавляю ошибки направления
1393update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден ref_organization_id = '', ref_organization_id, ''", "column_name":"ref_organization_id"}'')
1394 where t.referral_date is not null and (t.ref_organization_id is not null and not exists(select 1 from pim_organization o where o.id = t.ref_organization_id));
1395update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден recv_organization_id = '', recv_organization_id, ''", "column_name":"recv_organization_id"}'')
1396 where t.referral_date is not null and (t.recv_organization_id is not null and not exists(select 1 from pim_organization o where o.id = t.recv_organization_id));
1397update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден ref_doctor_id = '', ref_doctor_id, ''", "column_name":"ref_doctor_id"}'')
1398 where t.referral_date is not null and (t.ref_doctor_id is not null and not exists(select 1 from md_employee_position o where o.id = t.ref_doctor_id));
1399update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден ref_mkb_id = '', ref_mkb_id, ''", "column_name":"ref_mkb_id"}'')
1400 where t.referral_date is not null and (t.ref_mkb_id is not null and not exists(select 1 from md_diagnosis o where o.id = t.ref_mkb_id));
1401update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден referral_type_id = '', referral_type_id, ''", "column_name":"referral_type_id"}'')
1402 where t.referral_date is not null and (t.referral_type_id is not null and not exists(select 1 from md_referral_type o where o.id = t.referral_type_id));
1403update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не заполнено referral_date", "column_name":"referral_date"}'')
1404 where t.referral_date is null and coalesce(ref_organization_id, referral_id, recv_organization_id, ref_doctor_id, ref_mkb_id, referral_type_id) is not null;
1405
1406
1407-- 8. Д-учет----------------------------------------------------------------------------------------------
1408-- 8.1 Обновляю Д-учет
1409update pci_dispensary disp set
1410 med_case_in_id = case when d.disp_in_out = ''in'' then case_id end,
1411 med_case_out_id = case when d.disp_in_out = ''out'' then case_id end,
1412 reg_in_dt = d.reg_in_dt,
1413 reg_out_dt = d.reg_out_dt,
1414 reg_in_doctor_id = d.reg_in_doctor_id,
1415 reg_out_doctor_id = d.reg_out_doctor_id,
1416 patient_id = d.patient_id,
1417 reg_out_reason_id = d.reg_out_reason_id,
1418 reg_stage_id = d.reg_stage_id,
1419 clinic_id = d.clinic_id,
1420 diagnosis_id = d.mkb_id,
1421 dispensary_group_id = d.dispensary_group_id,
1422 nosol_registr_id = d.nosol_registr_id
1423from "typing_diagnos" d
1424where d.disp_id = disp.id and d.reg_in_dt is not null and
1425 exists(select 1 from md_diagnosis x where d.mkb_id = x.id) and
1426 exists(select 1 from md_employee_position x where d.reg_in_doctor_id = x.id) and
1427 exists(select 1 from md_nosol_registr x where d.nosol_registr_id = x.id) and
1428 exists(select 1 from md_reg_stage x where d.reg_stage_id = x.id) and
1429 exists(select 1 from mc_case x where d.case_id = x.id) and
1430 exists(select 1 from pci_patient x where d.patient_id = x.id) and
1431 exists(select 1 from md_clinic x where d.clinic_id = x.id) and
1432 exists(select 1 from md_reg_stage x where d.reg_stage_id = x.id) and
1433 (d.reg_out_doctor_id is null or exists(select 1 from md_employee_position x where x.id = d.reg_out_doctor_id)) and
1434 (d.reg_out_reason_id is null or exists(select 1 from pci_unreg_reason x where x.id = d.reg_out_reason_id)) and
1435 (d.dispensary_group_id is null or exists(select 1 from md_dispensary_group x where x.id = d.dispensary_group_id)) and
1436 d.disp_in_out in (''in'', ''out'')
1437;
1438
1439-- 8.2 Добавляю ошибку (не найден id д-учета)
1440update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден disp_id = '', disp_id, ''", "column_name":"disp_id"}'')
1441 where t.disp_id is not null and not exists (select 1 from pci_dispensary x where x.id = t.disp_id);
1442
1443
1444-- 8.3 Добавляю Д-учет
1445alter table "typing_diagnos" add column new_disp_id integer;
1446update "typing_diagnos" d set new_disp_id = nextval(''pci_dispensary_seq'')
1447where d.disp_id is null and d.reg_in_dt is not null and
1448 exists(select 1 from md_diagnosis x where d.mkb_id = x.id) and
1449 exists(select 1 from md_employee_position x where d.reg_in_doctor_id = x.id) and
1450 exists(select 1 from md_nosol_registr x where d.nosol_registr_id = x.id) and
1451 exists(select 1 from md_reg_stage x where d.reg_stage_id = x.id) and
1452 exists(select 1 from mc_case x where d.case_id = x.id) and
1453 exists(select 1 from pci_patient x where d.patient_id = x.id) and
1454 exists(select 1 from md_clinic x where d.clinic_id = x.id) and
1455 exists(select 1 from md_reg_stage x where d.reg_stage_id = x.id) and
1456 (d.reg_out_doctor_id is null or exists(select 1 from md_employee_position x where x.id = d.reg_out_doctor_id)) and
1457 (d.reg_out_reason_id is null or exists(select 1 from pci_unreg_reason x where x.id = d.reg_out_reason_id)) and
1458 (d.dispensary_group_id is null or exists(select 1 from md_dispensary_group x where x.id = d.dispensary_group_id)) and
1459 d.disp_in_out in (''in'', ''out'')
1460;
1461
1462insert into pci_dispensary(id, med_case_in_id, med_case_out_id, reg_in_dt, reg_out_dt, reg_in_doctor_id, reg_out_doctor_id, patient_id, reg_out_reason_id, reg_stage_id, clinic_id, diagnosis_id,
1463 dispensary_group_id, nosol_registr_id)
1464select
1465 new_disp_id,
1466 case when disp_in_out = ''in'' then case_id end,
1467 case when disp_in_out = ''out'' then case_id end,
1468 reg_in_dt,
1469 reg_out_dt,
1470 reg_in_doctor_id,
1471 reg_out_doctor_id,
1472 patient_id,
1473 reg_out_reason_id,
1474 reg_stage_id,
1475 clinic_id,
1476 mkb_id,
1477 dispensary_group_id,
1478 nosol_registr_id
1479from "typing_diagnos"
1480where new_disp_id is not null
1481;
1482
1483update "typing_diagnos" set disp_id = new_disp_id where new_disp_id is not null;
1484
1485-- 8.4 добавляю все ошибки д-учета
1486update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не заполнен reg_in_dt", "column_name":"reg_in_dt"}'')
1487 where t.reg_in_dt is null and coalesce(reg_in_doctor_id, disp_id, reg_in_doctor_id, nosol_registr_id) is not null;
1488update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден reg_in_doctor_id = '', reg_in_doctor_id, ''", "column_name":"reg_in_doctor_id"}'')
1489 where not exists(select 1 from md_employee_position x where t.reg_in_doctor_id = x.id) and coalesce(reg_in_doctor_id, disp_id, reg_in_doctor_id, nosol_registr_id) is not null;
1490update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден nosol_registr_id = '', nosol_registr_id, ''", "column_name":"nosol_registr_id"}'')
1491 where not exists(select 1 from md_nosol_registr x where t.nosol_registr_id = x.id) and coalesce(reg_in_doctor_id, disp_id, reg_in_doctor_id, nosol_registr_id) is not null;
1492update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден reg_out_doctor_id = '', reg_out_doctor_id, ''", "column_name":"reg_out_doctor_id"}'')
1493 where t.reg_out_doctor_id is not null and not exists(select 1 from md_employee_position x where t.reg_out_doctor_id = x.id);
1494update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден reg_out_reason_id = '', reg_out_reason_id, ''", "column_name":"reg_out_reason_id"}'')
1495 where t.reg_out_reason_id is not null and not exists(select 1 from pci_unreg_reason x where t.reg_out_reason_id = x.id);
1496update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден dispensary_group_id = '', dispensary_group_id, ''", "column_name":"dispensary_group_id"}'')
1497 where t.dispensary_group_id is not null and not exists(select 1 from md_dispensary_group x where t.dispensary_group_id = x.id);
1498
1499
1500-- 9. Устанавливаю основной диагноз
1501
1502with t as (
1503 select md.case_id, md.id from "typing_diagnos" x
1504 join mc_diagnosis md on md.id = x.id
1505 join mc_stage s on s.id = md.stage_id and md.is_main
1506 order by stage_order desc
1507limit 1
1508)
1509update mc_case c 
1510 set main_diagnos_id = t.id
1511from t where t.case_id = c.id;
1512
1513create temp table "typing_tmp"  ON COMMIT DROP AS  
1514with t as (
1515 select md.* from "typing_diagnos" x
1516 join mc_diagnosis md on md.id = x.id
1517 join mc_stage s on s.id = md.stage_id and md.is_main
1518 order by stage_order desc
1519limit 1
1520)
1521select nextval(''mc_diagnosis_seq'') new_id, * from t where stage_id < 4;
1522
1523insert into mc_diagnosis(id, diagnos_id, disease_type_id, case_id, patient_id, stage_id, step_id, type_id, establishment_date, is_main)
1524 select nextval(''mc_diagnosis_seq''), s.mkb_id, s.diag_disease_type_id, s.case_id, s.patient_id, s.diag_stage_id, v.new_id, s.diag_type_id, s.bdate,false from "typing_services" s JOIN "typing_visits" v ON s.step_num=v.step_num  where s.new_id is  not  null and s.mkb_id not in(SELECT d.mkb_id FROM   "typing_diagnos" d  JOIN "typing_visits" v ON d.step_num =v.step_num) and (SELECT count(new_id) FROM "typing_tmp") = 0
1525and exists (SELECT 1 FROM mc_step x WHERE x.id = v.new_id)
1526and s.mkb_id is not null
1527 ;
1528
1529
1530insert into mc_diagnosis(id, establishment_date, note, diagnos_id, disease_type_id, doctor_id, injury_type_id, case_id, patient_id, stage_id, step_id, type_id, is_main, is_suspicion)
1531select new_id, establishment_date, note, diagnos_id, disease_type_id, doctor_id, injury_type_id, case_id, patient_id, 4, null, type_id, 
1532is_main, is_suspicion from "typing_tmp" t
1533WHERE exists(SELECT 1 FROM mc_step x WHERE x.id = t.step_id)
1534and diagnos_id is not null
1535;
1536
1537
1538
1539
1540
1541update mc_case c set main_diagnos_id = coalesce(t.new_id, t.id)
1542from "typing_tmp" t where t.case_id = c.id;
1543
1544update md_appointment a
1545set state_id = 3
1546from typing_case c where c.talon = a.id
1547and c.talon is not null;
1548
1549
1550-- 10. Cобираю JSON
1551update "typing_case" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
1552update "typing_visits" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
1553update "typing_services" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
1554update "typing_diagnos" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
1555update "typing_resources" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
1556update "typing_cur" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
1557
1558-- 11. Закрытие случая, если есть шаг с результатом, устанавливаю state_id = 1
1559
1560with t as (
1561 select case_id from "typing_visits" where result_id is not null limit 1
1562)
1563update mc_case c set state_id = 1 from t where t.case_id = c.id;
1564
1565-- 12. Проставляю каждому шагу основной диагноз
1566with t as (
1567 select step_id, id from "typing_diagnos"
1568)
1569update mc_step st set main_diagnosis_id = t.id from t where t.step_id = st.id;
1570
1571' using $1;
1572
1573drop table if exists "typing_validate";
1574-- Запуск валидаций, копирую таблицу с валидациями
1575execute'
1576 create TEMP table "typing_validate" ON COMMIT DROP AS  select *
1577 from typing.typing_validate( (select new_id from "typing_case"), $1 ) as tabl1(rb int4, vld text, msg text, clmn_name text )
1578' using $2;
1579
1580
1581
1582
1583for rec in execute('
1584with t as
1585(
1586select 
1587 concat(''{"id":"'',coalesce(new_id::text, _id),
1588  ''","patient_id":"'',_patient_id,''","clinic_id":"'',_clinic_id,''","uid":"'',uid,''","care_regimen_id":"'',_care_regimen_id,''","funding_id":"'',_funding_id,
1589  ''","init_goal_id":"'',_init_goal_id,''","care_level_id":"'',_care_level_id,''","payment_method_id":"'',_payment_method_id,''","admission_reason_id":"'',_admission_reason_id,
1590  ''","repeat_count_id":"'',_repeat_count_id,''","referral_id":"'',coalesce(referral_id::text, new_ref_id::text,_referral_id),
1591  ''","ref_organization_id":"'',_ref_organization_id,''","ref_doctor_id":"'',_ref_doctor_id,
1592  ''","referral_date":"'',_referral_date,''","recv_organization_id":"'',_recv_organization_id,''","ref_mkb_id":"'',_ref_mkb_id,''","order_number":"'',order_number,
1593  ''","referral_type_id":"'',_referral_type_id,''","sicklist_id":"'',coalesce(new_sl_id::text, sicklist_id::text, _sicklist_id),
1594  ''","sl_code":"'',sl_code,''","disability_reason_id":"'',_disability_reason_id,
1595  ''","disability_from_dt":"'',_disability_from_dt,''","disability_to_dt":"'',_disability_to_dt,''",'',errors,
1596  
1597  '',"visits":['',
1598    (
1599    select
1600     array_to_string(array_agg(
1601     concat(''{"id":"'',coalesce(id::text, new_id::text, _id),
1602     ''","step_num":"'',step_num,''","type_id":"'',_type_id,''","admission_date":"'',_admission_date,''","place_id":"'',_place_id,''","initiator_id":"'',_initiator_id,
1603     ''","goal_id":"'',_goal_id,''","profile_id":"'',_profile_id,''","standard_id":"'',_standard_id,''","mes_id":"'',_mes_id,''","res_group_id":"'', (select res_group_id from mc_step st where st.id = coalesce(t.id, t.new_id)), ''","result_id":"'',_result_id, ''","stepResultReason_id":"'',_stepResultReason_id, ''","doctor_id":"'',_doctor_id, ''","vmp_method_id":"'',_vmp_method_id,''","vmp_type_id":"'',_vmp_type_id,
1604     ''","outcome_id":"'',_outcome_id,''","is_viewed":"'',_is_viewed,''","is_needed":"'',_is_needed,''","is_sanitized":"'',_is_sanitized,''",'',errors,''}'')
1605     ), '','')
1606    from "typing_visits" t
1607    ), '']'',
1608
1609  '',"services":['',
1610    (
1611    select
1612     array_to_string(array_agg(
1613     concat(''{"id":"'',coalesce(id::text, new_id::text, _id),
1614     ''","step_num":"'',step_num,''","step_id":"'',_step_id,''","service_id":"'',_service_id,''","bdate":"'',_bdate,''","funding_id":"'',_funding_id,''","doctor_id":"'',_doctor_id,
1615     ''","quantity":"'',_quantity,''","res_group_id":"'',(select res_group_id from sr_srv_rendered r where r.id = coalesce(t.id, t.new_id)),''","mkb_id":"'',_mkb_id,''",'',errors,''}'')
1616     ), '','')
1617    from "typing_services" t
1618    ), '']'',
1619
1620
1621  '',"diagnosis":['',
1622    (
1623    select
1624       array_to_string(array_agg(
1625       concat(''{"id":"'',coalesce(id::text, new_id::text, _id),
1626       ''","step_num":"'',step_num,''","mkb_id":"'',_mkb_id,''","disease_type_id":"'',_disease_type_id,''","type_id":"'',_type_id,''","is_main":"'',_is_main,
1627       ''","stage_id":"'',_stage_id,''","step_id":"'',_step_id,
1628       ''","disp_id":"'',coalesce(disp_id::text, new_disp_id::text, _disp_id),''","disp_in_out":"'',disp_in_out,''","nosol_registr_id":"'',_nosol_registr_id,
1629       ''","dispensary_group_id":"'',_dispensary_group_id,''","reg_in_dt":"'',_reg_out_dt,''","reg_in_doctor_id":"'',_reg_in_doctor_id,''","reg_out_doctor_id":"'',_reg_out_doctor_id,
1630       ''","reg_stage_id":"'',_reg_stage_id,''","reg_out_reason_id":"'',_reg_out_reason_id,''",'',errors,''}'')
1631       ), '','')
1632    from "typing_diagnos"
1633    ), '']'',
1634
1635  '',"validate":['',
1636    (
1637    select
1638       array_to_string(array_agg(
1639       concat(''{"rb":"'', rb::text, ''" ,"vld":"'', vld, ''","msg":"'', msg, ''","clmn_name":"'', clmn_name, ''" }'')
1640       ), '','')
1641    from "typing_validate" where vld is not null
1642    ), '']'',
1643
1644  '',"resources":['',
1645    (
1646    select
1647       array_to_string(array_agg(
1648       concat(''{"role_id":"'', role_id::text, ''" ,"step_num":"'', step_num::text, ''","resource_id":"'', resource_id::text, ''","group_id":"'', group_id::text,''","org_id":"'', org_id::text,''", '' ,errors,''}'') 
1649       ), '','')
1650    from "typing_resources"
1651    ), '']'',
1652
1653'',"cur":['',
1654    (
1655    select
1656       array_to_string(array_agg(
1657       concat(''{"id":"'', coalesce(id::text, new_id::text, _id), ''" ,"criteria_id":"'', criteria_id ::text, ''","value":"'', value::text,''", '' ,errors,''}'')
1658       ), '','')
1659    from "typing_cur"
1660    ), '']'',
1661  ''}'') x
1662from "typing_case"
1663)
1664
1665select x::json x from t
1666
1667
1668')
1669 loop
1670  ret = rec.x;
1671 end loop;
1672
1673-- Просталяю соц. положение в случай
1674 execute('
1675  update mc_case c set soc_group_id = p.social_group_id
1676  from "typing_case" cc, pci_patient p where cc.id = c.id and c.patient_id = p.id
1677 ');
1678
1679
1680
1681 for rec in execute('
1682 select rb
1683 from "typing_validate"
1684')
1685loop
1686 rb = rec.rb;
1687
1688if rb = 0 or (rb=1 and $2::TEXT= '0')
1689
1690then raise exception ''; end if
1691;
1692end loop;
1693
1694
1695
1696
1697
1698if exists (SELECT 1 from typing_services WHERE errors <> '"errors":[]' ) or
1699exists (SELECT 1 from typing_case WHERE errors <> '"errors":[]' ) or
1700exists (SELECT 1 from typing_diagnos WHERE errors <> '"errors":[]' ) or
1701exists (SELECT 1 from typing_resources WHERE errors <> '"errors":[]' ) or
1702exists (SELECT 1 from typing_visits WHERE errors <> '"errors":[]' ) or
1703exists (SELECT 1 from typing_cur WHERE errors <> '"errors":[]' ) 
1704then raise exception ''; end if;
1705
1706/*Добавляю то что получилось в аудит
1707*/
1708
1709--mc_case_aud
1710insert into mc_case_aud(id,rev,revtype,create_date, case_type_id, clinic_id, patient_id, care_regimen_id, funding_id, init_goal_id, care_level_id, payment_method_id, admission_reason_id, repeat_count_id, referral_id, uid, care_providing_form_id ,
1711admission_state_id,
1712drunkenness_type_id ,
1713time_gone_id,
1714provision_condition_id,note)
1715select id,currval('sec_audit_entry_seq'), case when exists (select 1 from typing_case where id is null) then 1 else 0 end,
1716create_date, case_type_id, clinic_id, patient_id, care_regimen_id, funding_id, init_goal_id, care_level_id, payment_method_id, admission_reason_id, repeat_count_id, referral_id, uid, care_providing_form_id ,
1717admission_state_id,
1718drunkenness_type_id,
1719time_gone_id,
1720provision_condition_id,note
1721from mc_case where id = (select coalesce(new_id, id) from typing_case limit 1) ;
1722--mc_step
1723insert into mc_step_aud (id,rev,revtype,admission_date,admission_time, outcome_date, case_id, outcome_id, result_id, reason_id, profile_id, mes_id,csg_id, res_group_id,regimen_id,outcome_regimen_id,deviation_reason_id, vmp_method_id, vmp_type_id)
1724select s.id, currval('sec_audit_entry_seq'),case when v.new_id is null then 0 else 1 end, s.admission_date,s.admission_time, s.outcome_date, s.case_id, s.outcome_id, s.result_id, s.reason_id, s.profile_id, s.mes_id,s.csg_id, s.res_group_id,s.regimen_id,s.outcome_regimen_id,s.deviation_reason_id, s.vmp_method_id, s.vmp_type_id
1725from mc_step s join typing_visits v on  coalesce(v.new_id, v.id) =s.id;
1726
1727--hsp_record
1728
1729insert into hsp_record_aud(id,	 rev,														mes_id,    funding_id,    department_id, bed_profile_id,    issue_planned_date,   complexity_level_id)
1730										select v.id, currval('sec_audit_entry_seq'),h.mes_id, h.funding_id, h.department_id, h.bed_profile_id, h.issue_planned_date, h.complexity_level_id 
1731from hsp_record h join typing_visits v on h.id = coalesce(v.new_id, v.id);
1732
1733-- plc_visit
1734
1735insert into plc_visit_aud(id, goal_id, initiator_id, place_id, type_id, is_viewed, is_needed, is_sanitized,rev)
1736select p.id, p.goal_id, p.initiator_id, p.place_id, p.type_id, p.is_viewed, p.is_needed, p.is_sanitized, currval('sec_audit_entry_seq')
1737from plc_visit p  join typing_visits v on p.id = coalesce(v.new_id, v.id);
1738  
1739  
1740--mc_diagnosis
1741insert into mc_diagnosis_aud(id, diagnos_id, disease_type_id, case_id, patient_id, stage_id, step_id, type_id, is_main,establishment_date, note,rev,revtype)
1742select d.id, d.diagnos_id, d.disease_type_id, d.case_id, d.patient_id, d.stage_id, d.step_id, d.type_id, d.is_main,d.establishment_date, d.note, currval('sec_audit_entry_seq'), case when t.new_id is null then 0 else 1 end
1743from mc_diagnosis d
1744 left join typing_diagnos t on d.id = coalesce(t.new_id, t.id)
1745where d.case_id = (select coalesce(id,new_id) from typing_case)
1746;
1747--mc_step_cur
1748
1749insert into mc_step_cur_coef_criteria_aud(id, criteria_id,step_id,value,rev,revtype)
1750select c.id, c.criteria_id,c.step_id,c.value,currval('sec_audit_entry_seq'), case when t.new_id is null then 0 else 1 end
1751from  mc_step_cur_coef_criteria c join typing_cur t on  c.id = coalesce(t.new_id, t.id);
1752
1753
1754--sr_srv_rendered
1755insert into sr_srv_rendered_aud(id, bdate, service_id, customer_id, quantity, org_id, is_rendered, res_group_id, cul, funding_id,comment, is_refused, tooth_number,rev,revtype)
1756select s.id, s.bdate, s.service_id, s.customer_id, s.quantity, s.org_id, s.is_rendered, s.res_group_id, s.cul, s.funding_id,s.comment, s.is_refused, s.tooth_number,currval('sec_audit_entry_seq'), case when t.new_id is null then 0 else 1 end
1757from sr_srv_rendered s  join typing_services t on  s.id = coalesce(t.new_id, t.id);
1758
1759--md_srv_rendered
1760insert into md_srv_rendered_aud(id, step_id, case_id, diagnosis_id,rev)
1761  select m.id, m.step_id, m.case_id, m.diagnosis_id,currval('sec_audit_entry_seq')
1762  from md_srv_rendered m join typing_services t on  m.id = coalesce(t.new_id, t.id);
1763
1764
1765--sr_srv_result 
1766insert into sr_srv_result_aud(id,value,service_id,result_type_id,rev,revtype)
1767  select r.id,r.value,r.service_id,r.result_type_id,currval('sec_audit_entry_seq'), case when t.new_id is null then 0 else 1 end
1768from sr_srv_result r join typing_services t on r.service_id = coalesce(t.new_id, t.id); 
1769
1770exception when RAISE_EXCEPTION then raise notice '123';
1771 end; -- trans
1772
1773
1774 return ret;
1775end;