icVuKcBp

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