cbjBzE5L

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