mhvvEdLq

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