Zy5CrN03

1
2
3declare uniq text;
4
5        rec record; 
6
7        ret json;
8
9        rb integer;
10
11        rec1 record;
12
13        rc3 record;
14
15        hsp_id_variable INTEGER;
16
17begin
18
19
20
21begin --trans
22
23
24
25perform set_config('app.user', (select login from sec_user where id = ($1::JSON->>'user')::INTEGER), true); 
26
27perform set_config('app.source', 'typing2', true);
28
29
30
31insert into sec_audit_entry(id,date,user_id)
32
33	select nextval('sec_audit_entry_seq'),now(),($1->>'userId')::INTEGER ;
34
35uniq = '';
36
37execute'
38
39
40
41---updated 04.08.2015 test
42
43
44
45-- 1. читаю структуру
46
47----------------------------------------------------
48
49
50
51create temp table "typing_diagnos" ON COMMIT DROP AS  
52
53with t as (
54
55 select
56
57  json_array_elements(value) d
58
59 from json_each(
60
61$1 -- эта строка - параметр
62
63 ) where key=''diagnosis''
64
65)
66
67
68
69select
70
71 d ->> ''id'' _id,
72
73 d ->> ''mkb_id'' _mkb_id,
74
75 d ->> ''disease_type_id'' _disease_type_id,
76
77 d ->> ''type_id'' _type_id,
78
79 d ->> ''is_main'' _is_main,
80
81 d ->> ''stage_id'' _stage_id,
82
83 d ->> ''step_id'' _step_id,
84
85 d ->> ''step_num'' step_num,
86
87 d ->> ''disp_id'' _disp_id,
88
89 d ->> ''disp_in_out'' disp_in_out,
90
91 d ->> ''nosol_registr_id'' _nosol_registr_id,
92
93 d ->> ''dispensary_group_id'' _dispensary_group_id,
94
95 d ->> ''reg_in_dt'' _reg_in_dt,
96
97 d ->> ''reg_out_dt'' _reg_out_dt,
98
99 d ->> ''reg_in_doctor_id'' _reg_in_doctor_id,
100
101 d ->> ''reg_out_doctor_id'' _reg_out_doctor_id,
102
103 d ->> ''reg_stage_id'' _reg_stage_id,
104
105 d ->> ''reg_out_reason_id'' _reg_out_reason_id,
106
107 d ->> ''note'' note
108
109from t
110
111;
112
113
114
115
116
117create temp table "typing_services"  ON COMMIT DROP AS 
118
119with t as (
120
121 select
122
123  json_array_elements(value) d
124
125 from json_each(
126
127$1 -- эта строка - параметр
128
129 ) where key=''services''
130
131)
132
133
134
135select
136
137 d ->> ''id'' _id,
138
139 d ->> ''step_num'' step_num,
140
141 d ->> ''step_id'' _step_id,
142
143 d ->> ''service_num'' service_num,
144
145 d ->> ''service_id'' _service_id,
146
147 d ->> ''bdate'' _bdate,
148
149 d ->> ''funding_id'' _funding_id,
150
151 d ->> ''quantity'' _quantity,
152
153 d ->> ''res_group_id'' _res_group_id,
154
155 d ->> ''mkb_id'' _mkb_id,
156
157 d ->> ''doctor_id'' _doctor_id,
158
159 d ->> ''cul'' _cul,
160
161 d ->> ''note'' _note,
162
163 d ->> ''diag_disease_type_id'' _diag_disease_type_id,
164
165 d ->> ''diag_stage_id'' _diag_stage_id,
166
167 d ->> ''diag_type_id'' _diag_type_id,
168
169 d ->> ''tooth_number'' tooth_number,
170
171 typing.to_int(d->>''anatomic_zone_id'') anatomic_zone_id,
172
173 d ->> ''is_refused'' _is_refused,
174
175 d->> ''org_id'' _org_id,
176
177 d->> ''result_type_id'' _result_type_id,
178
179 d->> ''value'' result_value,
180
181 typing.to_int(d->>''brigade_id'') brigade_id, 
182
183 typing.to_int(d->>''srv_result_id'') srv_result_id,
184
185 typing.to_int(d->>''health_group_id'') health_group_id,
186
187 case when(d->>''is_amalgam_filling'' =''1'') then true else false end is_amalgam_filling
188
189
190
191from t
192
193;
194
195
196
197create temp table  "typing_visits"  ON COMMIT DROP AS  
198
199with t as (
200
201 select
202
203  json_array_elements(value) d
204
205 from json_each(
206
207$1 -- эта строка - параметр
208
209 ) where key=''visits''
210
211)
212
213
214
215select
216
217 d ->> ''id'' _id,
218
219 d ->> ''step_num'' step_num,
220
221 d ->> ''type_id'' _type_id,
222
223 d ->> ''admission_date'' _admission_date,
224
225 d ->> ''outcome_date'' _outcome_date,
226
227 d ->> ''place_id'' _place_id,
228
229 d ->> ''initiator_id'' _initiator_id,
230
231 d ->> ''goal_id'' _goal_id,
232
233 d ->> ''profile_id'' _profile_id,
234
235 d ->> ''standard_id'' _standard_id,
236
237 d ->> ''mes_id'' _mes_id,
238
239 d ->> ''res_group_id'' _res_group_id,
240
241 d ->> ''doctor_id'' _doctor_id,
242
243 d ->> ''result_id'' _result_id,
244
245 d ->> ''stepResultReason_id'' _stepResultReason_id,
246
247 d ->> ''outcome_id'' _outcome_id,
248
249 d ->> ''is_viewed'' _is_viewed,
250
251 d ->> ''is_needed'' _is_needed,
252
253 d ->> ''is_sanitized'' _is_sanitized,
254
255 d ->> ''department_id'' _department_id,
256
257 d ->> ''bed_profile_id'' _bed_profile_id,
258
259 d ->> ''csg_id'' _csg_id,
260
261 d ->> ''deviation_reason_id'' _deviation_reason_id,
262
263 d ->> ''complexity_level_id'' _complexity_level_id,
264
265 d ->> ''hosp_department_id'' _hosp_department_id,
266
267 d ->> ''vmp_type_id'' _vmp_type_id,
268
269 typing.to_int(d ->> ''funding_id'') funding_id,
270
271 typing.to_int(d->>''brigade_id'') brigade_id, 
272
273 d ->> ''vmp_method_id'' _vmp_method_id,
274
275 typing.to_time(d ->> ''admission_time'') admission_time,
276
277 typing.to_time(d ->> ''outcome_time'') outcome_time
278
279 
280
281from t
282
283;
284
285
286
287
288
289create temp table  "typing_case"   ON COMMIT DROP AS  
290
291
292
293with t as (
294
295select
296
297$1::json d
298
299)
300
301
302
303
304
305select
306
307 d ->> ''id'' _id,
308
309 d ->> ''case_type_id'' _case_type_id,
310
311 d ->> ''patient_id'' _patient_id,
312
313 d ->> ''clinic_id'' _clinic_id,
314
315 d ->> ''uid'' uid,
316
317 d ->> ''care_regimen_id'' _care_regimen_id,
318
319 d ->> ''funding_id'' _funding_id,
320
321 d ->> ''init_goal_id'' _init_goal_id,
322
323 d ->> ''care_level_id'' _care_level_id,
324
325 d ->> ''payment_method_id'' _payment_method_id,
326
327 d ->> ''admission_reason_id'' _admission_reason_id,
328
329 d ->> ''repeat_count_id'' _repeat_count_id,
330
331 d ->> ''referral_id'' _referral_id,
332
333 d ->> ''ref_organization_id'' _ref_organization_id,
334
335 d ->> ''ref_doctor_id'' _ref_doctor_id,
336
337 d ->> ''referral_date'' _referral_date,
338
339 d ->> ''recv_organization_id'' _recv_organization_id,
340
341 d ->> ''ref_mkb_id'' _ref_mkb_id,
342
343 d ->> ''order_number'' order_number,
344
345 d ->> ''referral_type_id'' _referral_type_id,
346
347 d ->> ''sicklist_id'' _sicklist_id,
348
349 d ->> ''sl_code'' sl_code,
350
351 d ->> ''disability_reason_id'' _disability_reason_id,
352
353 d ->> ''disability_from_dt'' _disability_from_dt,
354
355 d ->> ''disability_to_dt'' _disability_to_dt,
356
357 coalesce(d ->> ''care_providing_from_id'',d ->> ''care_providing_form_id'') _care_providing_form_id,
358
359 d ->> ''admission_state'' _admission_state_id,
360
361 d ->> ''drunkenness_type_id'' _drunkenness_type_id,
362
363 d->> ''time_gone_id'' _time_gone_id,
364
365 d->> ''provision_condition_id'' _provision_condition_id,
366
367 d->> ''note'' _note,
368
369 d->> ''workplace_print'' workplace_print,
370
371 d->> ''ready_to_work_dt'' _ready_to_work_dt,
372
373 d->> ''md_sicklist_state'' _md_sicklist_state,
374
375 d->> ''sl_name'' sl_name,
376
377 d->> ''sl_surname'' sl_surname,
378
379 d->> ''sl_patr_name'' sl_patr_name,
380
381 d->> ''employee_position_id'' _employee_position_id,
382
383 typing.to_int(d->> ''userId'') user_id,
384
385 typing.to_int(d->>''talon'') talon,
386
387 typing.to_int(d->> ''health_group_id'')  health_group_id,
388
389 typing.to_int(d->>''case_result_id'') result_id,
390
391 typing.to_int(d->>''admission_type_id'') admission_type_id
392
393
394
395from t
396
397;
398
399
400
401
402
403
404
405create temp table "typing_resources" ON COMMIT DROP AS 
406
407with t as (
408
409 select
410
411  json_array_elements(value) d
412
413 from json_each(
414
415$1 -- эта строка - параметр
416
417 ) where key=''res''
418
419)
420
421
422
423select
424
425 d ->> ''role_id'' _role_id,
426
427 d ->> ''step_num'' step_num,
428
429coalesce(d ->> ''resourse_id'',d ->> ''resource_id'') _resource_id,
430
431 d ->> ''bed_id'' _bed_id,
432
433 d ->> ''bdatetime'' _bdatetime,
434
435 d ->> ''edatetime'' _edatetime,
436
437 d ->> ''service_num'' _serv_num
438
439from t
440
441;
442
443
444
445create temp table "typing_cur"  ON COMMIT DROP AS 
446
447with t as (
448
449 select
450
451  json_array_elements(value) d
452
453 from json_each(
454
455$1 -- эта строка - параметр
456
457 ) where key=''cur''
458
459)
460
461
462
463select
464
465 d ->> ''criteria_id'' _criteria_id,
466
467 d ->> ''step_num'' step_num,
468
469 d ->> ''value'' _value,
470
471 d ->> ''id'' _id
472
473from t
474
475;
476
477create temp table "typing_standarts"  ON COMMIT DROP AS 
478
479with t as (
480
481 select
482
483  json_array_elements(value) d
484
485 from json_each(
486
487$1 -- эта строка - параметр
488
489 ) where key=''standarts''
490
491 )
492
493
494
495select
496
497 typing.to_int(d ->> ''csg_id'') csg_id,
498
499 d->> ''step_num''  step_num,
500
501 typing.to_int(d ->> ''vmp_type_id'')vmp_type_id,
502
503 typing.to_int(d ->> ''vmp_method_id'')vmp_method_id
504
505
506
507from t
508
509;
510
511
512
513 
514
515
516
517
518
519
520
521-- 2. Случай--------------------------------------------------------------------------
522
523
524
525-- 2.1 Добавляю реальные колонки
526
527alter table "typing_case"
528
529 add column id integer,
530
531 add column case_type_id integer,
532
533 add column patient_id integer,
534
535 add column clinic_id integer,
536
537 add column care_regimen_id integer,
538
539 add column funding_id integer,
540
541 add column init_goal_id integer,
542
543 add column care_level_id integer,
544
545 add column payment_method_id integer,
546
547 add column admission_reason_id integer,
548
549 add column repeat_count_id integer,
550
551 add column referral_id integer,
552
553 add column ref_organization_id integer,
554
555 add column ref_doctor_id integer,
556
557 add column ref_mkb_id integer,
558
559 add column referral_date date,
560
561 add column recv_organization_id integer,
562
563 add column referral_type_id integer,
564
565 add column sicklist_id integer,
566
567 add column disability_reason_id integer,
568
569 add column disability_from_dt date,
570
571 add column disability_to_dt date,
572
573 add column care_providing_form_id integer,
574
575 add column admission_state_id integer,
576
577 add column drunkenness_type_id integer,
578
579add COLUMN time_gone_id integer,
580
581add COLUMN provision_condition_id INTEGER,
582
583add column ready_to_work_dt date,
584
585add column  md_sicklist_state integer,
586
587add column employee_position_id integer,
588
589
590
591 add column errors text default ''''
592
593;
594
595-- 2.2 Заполняю реальные колонки
596
597update "typing_case" set
598
599 id = typing.to_int(_id),
600
601 case_type_id = coalesce(typing.to_int(_case_type_id), 1),
602
603 patient_id = typing.to_int(_patient_id),
604
605 clinic_id = typing.to_int(_clinic_id),
606
607 care_regimen_id = typing.to_int(_care_regimen_id),
608
609 funding_id = typing.to_int(_funding_id),
610
611 init_goal_id = typing.to_int(_init_goal_id),
612
613 care_level_id = typing.to_int(_care_level_id),
614
615 payment_method_id = typing.to_int(_payment_method_id),
616
617 admission_reason_id = typing.to_int(_admission_reason_id),
618
619 repeat_count_id = typing.to_int(_repeat_count_id),
620
621 referral_id = typing.to_int(_referral_id),
622
623 ref_organization_id = typing.to_int(_ref_organization_id),
624
625 ref_mkb_id = typing.to_int(_ref_mkb_id),
626
627 ref_doctor_id = typing.to_int(_ref_doctor_id),
628
629 referral_date = typing.to_dt(_referral_date),
630
631 recv_organization_id = typing.to_int(_recv_organization_id),
632
633 referral_type_id = typing.to_int(_referral_type_id),
634
635 sicklist_id = typing.to_int(_sicklist_id),
636
637 disability_reason_id = typing.to_int(_disability_reason_id),
638
639 disability_from_dt = typing.to_dt(_disability_from_dt),
640
641 disability_to_dt = typing.to_dt(_disability_to_dt),
642
643care_providing_form_id  = typing.to_int(_care_providing_form_id),
644
645admission_state_id = typing.to_int(_admission_state_id),
646
647drunkenness_type_id = typing.to_int(_drunkenness_type_id),
648
649time_gone_id = typing.to_int(_time_gone_id),
650
651provision_condition_id = typing.to_int(_provision_condition_id),
652
653ready_to_work_dt = typing.to_dt(_ready_to_work_dt),
654
655md_sicklist_state = typing.to_int(_md_sicklist_state),
656
657employee_position_id = typing.to_int(_employee_position_id)
658
659
660
661
662
663
664
665
666
667;
668
669
670
671
672
673-- 2.3. обновляю данные в случае, если есть id при этом проверяю все ограничения
674
675update mc_med_case_result r 
676
677set health_group_id = c.health_group_id
678
679from typing_case c  where c.result_id = r.id
680
681and exists(select 1 from mc_health_group g where g.id =c.health_group_id);
682
683
684
685update mc_case c set
686
687 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,
688
689 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,
690
691    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,
692
693admission_type_id = t.admission_type_id
694
695
696
697from "typing_case" t
698
699where
700
701 c.id = t.id and
702
703 exists (select 1 from pci_patient p where p.id = t.patient_id) and
704
705 exists (select 1 from md_clinic cln where cln.id = t.clinic_id) and
706
707 (exists (select 1 from mc_care_regimen r where r.id = t.care_regimen_id) or t.care_regimen_id is null) and
708
709 (exists (select 1 from fin_funding_source_type f where f.id = t.funding_id) or t.funding_id is null) and
710
711 (exists (select 1 from mc_case_init_goal f where f.id = t.init_goal_id)) and
712
713 (exists (select 1 from mc_care_level cl where cl.id = t.care_level_id) or t.care_level_id is null) and
714
715 (exists (select 1 from mc_payment_method pm where pm.id = t.payment_method_id) or t.payment_method_id is null) and
716
717 (exists (select 1 from mc_admission_reason ar where ar.id = t.admission_reason_id) or t.admission_reason_id is null) and
718
719 (exists (select 1 from mc_repeat_count cl where cl.id = t.repeat_count_id) or t.repeat_count_id is null) and
720
721 (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
722
723;
724
725
726
727-- 2.4. Запись ошибок (не найден id обновляемого случая) при обновлении случая
728
729update "typing_case" t set errors = ''{"level":"case", "message":"не найден случай по id", "column_name":"id"}''
730
731where id is not null and not exists (select 1 from mc_case c where c.id = t.id);
732
733
734
735-- 2.5. Добавляю случай, если нет id, при этом проверяю все ограничения
736
737alter table "typing_case" add column new_id integer;
738
739update "typing_case" t
740
741set new_id = nextval(''mc_case_seq'')
742
743where t.id is null and
744
745 exists (select 1 from pci_patient p where p.id = t.patient_id) and
746
747 exists (select 1 from md_clinic cln where cln.id = t.clinic_id) and
748
749 exists (select 1 from mc_case_type ct where ct.id = t.case_type_id) and
750
751 (exists (select 1 from mc_care_regimen r where r.id = t.care_regimen_id) or t.care_regimen_id is null) and
752
753 (exists (select 1 from fin_funding_source_type f where f.id = t.funding_id) or t.funding_id is null) and
754
755 (exists (select 1 from mc_case_init_goal f where f.id = t.init_goal_id)) and
756
757 (exists (select 1 from mc_care_level cl where cl.id = t.care_level_id) or t.care_level_id is null) and
758
759 (exists (select 1 from mc_payment_method pm where pm.id = t.payment_method_id) or t.payment_method_id is null) and
760
761 (exists (select 1 from mc_admission_reason ar where ar.id = t.admission_reason_id) or t.admission_reason_id is null) and
762
763 (exists (select 1 from mc_repeat_count cl where cl.id = t.repeat_count_id) or t.repeat_count_id is null) and
764
765 (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
766
767 (exists (select 1 from mc_health_group g where g.id = t.health_group_id) or t.health_group_id is null) and 
768
769 (exists (select 1 from mc_admission_type m where m.id = t.admission_type_id) or t.admission_type_id is null)
770
771
772
773;
774
775update typing_case t set result_id =nextval(''mc_med_case_result_id'')
776
777where result_id is null 
778
779and exists (select 1 from mc_health_group g where g.id = t.health_group_id);
780
781
782
783
784
785insert into mc_med_case_result (id,health_group_id)
786
787select result_id ,health_group_id from typing_case t
788
789where result_id is not null;
790
791 
792
793 
794
795
796
797;
798
799update typing_case t set result_id =nextval(''mc_med_case_result_id'')
800
801where result_id is null 
802
803and exists (select 1 from mc_health_group g where g.id = t.health_group_id);
804
805
806
807
808
809insert into mc_med_case_result (id,health_group_id)
810
811select result_id ,health_group_id from typing_case t
812
813where result_id is not null;
814
815
816
817
818
819
820
821insert into mc_case(id, case_type_id, create_date, clinic_id, patient_id, care_regimen_id, funding_id, init_goal_id, care_level_id, payment_method_id, admission_reason_id, repeat_count_id, referral_id, uid, care_providing_form_id ,
822
823admission_state_id,
824
825drunkenness_type_id ,
826
827time_gone_id,
828
829provision_condition_id,note,admission_type_id)
830
831select new_id, case_type_id, current_date, clinic_id, patient_id, care_regimen_id, funding_id, init_goal_id, care_level_id, payment_method_id, admission_reason_id, repeat_count_id, referral_id, uid, care_providing_form_id ,
832
833admission_state_id,
834
835drunkenness_type_id ,
836
837time_gone_id,
838
839provision_condition_id,
840
841_note,
842
843admission_type_id
844
845
846
847 from 
848
849 "typing_case" where new_id
850
851 is not null;
852
853
854
855
856
857--update "typing_case" set id = new_id where new_id is not null;
858
859
860
861-- 2.6 Добавление оставшихся ошибок (при обновлении, либо добавлении случая)
862
863update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден тип случая по case_type_id = '', case_type_id, ''", "column_name":"case_type_id"}'')
864
865 where not exists (select 1 from mc_case_type ct where ct.id = t.case_type_id);
866
867update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден admission_type_id = '', admission_type_id, ''", "column_name":"admission_type_id"}'')
868
869 where not exists (select 1 from mc_admission_type ct where ct.id = t.admission_type_id)
870
871and t.admission_type_id is not null
872
873;
874
875
876
877update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден пациент по patient_id = '', patient_id, ''", "column_name":"id"}'')
878
879 where not exists (select 1 from pci_patient p where p.id = t.patient_id);
880
881update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найдена МО по clinic_id = '', clinic_id, ''", "column_name":"clinic_id"}'')
882
883 where not exists (select 1 from md_clinic cln where cln.id = t.clinic_id);
884
885update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден care_regimen_id = '', care_regimen_id, ''", "column_name":"care_regimen_id"}'')
886
887 where care_regimen_id is not null and not exists (select 1 from mc_care_regimen cr where cr.id = t.care_regimen_id);
888
889update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден funding_id = '', funding_id, ''", "column_name":"funding_id"}'')
890
891 where funding_id is not null and not exists (select 1 from fin_funding_source_type x where x.id = t.funding_id);
892
893update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден init_goal_id = '', init_goal_id, ''", "column_name":"init_goal_id"}'')
894
895 where not exists (select 1 from mc_case_init_goal x where x.id = t.init_goal_id);
896
897update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден care_level_id = '', care_level_id, ''", "column_name":"care_level_id"}'')
898
899 where care_level_id is not null and not exists (select 1 from mc_care_level x where x.id = t.care_level_id);
900
901update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден payment_method_id = '', payment_method_id, ''", "column_name":"payment_method_id"}'')
902
903 where payment_method_id is not null and not exists (select 1 from mc_payment_method x where x.id = t.payment_method_id);
904
905update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден admission_reason_id = '', admission_reason_id, ''", "column_name":"admission_reason_id"}'')
906
907 where admission_reason_id is not null and not exists (select 1 from mc_admission_reason x where x.id = t.admission_reason_id);
908
909update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден repeat_count_id = '', repeat_count_id, ''", "column_name":"repeat_count_id"}'')
910
911 where repeat_count_id is not null and not exists (select 1 from mc_repeat_count x where x.id = t.repeat_count_id);
912
913update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден referral_id = '', referral_id, ''", "column_name":"referral_id"}'')
914
915 where referral_id is not null and not exists (select 1 from md_referral x where x.id = t.referral_id);
916
917update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден uid = '', uid, ''", "column_name":"uid"}'')
918
919 where referral_id is not null and not exists (select 1 from md_referral x where x.id = t.referral_id);
920
921update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"Данный случай включен в реестр, изменение невозможно", 
922
923"column_name":"id"}'')
924
925 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);
926
927update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден health_group_id = '', health_group_id, ''", "column_name":"health_group_id"}'')
928
929 where health_group_id is not null and not exists (select 1 from mc_health_group x where x.id = t.health_group_id);
930
931update "typing_case" t set errors = concat(errors, ''{"level":"case", "message":"не найден result_id = '', result_id, ''", "column_name":"result_id"}'')
932
933 where result_id is not null and not exists (select 1 from mc_med_case_result x where x.id = t.health_group_id);
934
935  
936
937update "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"}'')
938
939 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) ;
940
941  
942
943
944
945
946
947
948
949
950
951-- 2.5 Ресурс-------------------------------------------------------------------------
952
953-- 2.5.1 добавляю реальные колонки----------------------------------------------------
954
955
956
957alter table "typing_resources"
958
959 add column role_id integer,
960
961 add column serv_num    integer,
962
963 add column resource_id integer,
964
965 add column bdatetime   date,
966
967 add column edatetime   date,
968
969  add column group_id    integer,
970
971 add column bed_id integer,
972
973 add column org_id      integer,
974
975 add column responsible_id integer,
976
977 add column errors      text default ''''
978
979;
980
981
982
983--- 2.5.2 Заполняю реальные колонки
984
985update "typing_resources" set
986
987 role_id    = typing.to_int(_role_id),
988
989 serv_num    = typing.to_int(_serv_num ),
990
991 resource_id = typing.to_int(_resource_id),
992
993 bdatetime  = typing.to_dt(_bdatetime),
994
995 edatetime  = typing.to_dt(_edatetime),
996
997 bed_id = typing.to_int(_bed_id)
998
999 ;
1000
1001
1002
1003
1004
1005---Добавляю группу и организацию
1006
1007update "typing_resources" set
1008
1009group_id= nextval(''sr_res_group_seq'')
1010
1011WHERE resource_id in(SELECT max(resource_id) FROM "typing_resources" group by serv_num, step_num);
1012
1013
1014
1015update "typing_resources" set
1016
1017org_id =  (SELECT clinic_id FROM  "typing_case" limit 1);
1018
1019
1020
1021
1022
1023update "typing_resources" r set
1024
1025responsible_id = employee_position_id
1026
1027FROM pim_employee_position_resource epr WHERE epr.id = r.resource_id and  group_id is not null
1028
1029and exists(SELECT 1 FROM sr_resource sr WHERE sr.id = r.resource_id)
1030
1031and exists(SELECT 1 FROM sr_res_role x WHERE id = r.role_id and x.kind_id =1)
1032
1033;
1034
1035
1036
1037
1038
1039insert into sr_res_group(id, org_id,edate, bdate, responsible_id, is_system, department_id)
1040
1041SELECT group_id, org_id, edatetime, bdatetime, responsible_id, true, null FROM  "typing_resources" r WHERE  group_id is not null
1042
1043and exists(SELECT 1 FROM pim_organization o WHERE o.id = r.org_id);
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053--Заполняю группу
1054
1055update "typing_resources" r set
1056
1057group_id= t.group_id
1058
1059FROM "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;
1060
1061
1062
1063insert into sr_res_group_relationship(id, resource_id,bdatetime, edatetime,group_id, role_id,is_disabled)
1064
1065  SELECT nextval(''sr_res_group_relationship_seq''), resource_id,bdatetime,edatetime, group_id, role_id, CASE when edatetime is not null then true end
1066
1067  FROM  "typing_resources" t
1068
1069WHERE exists(SELECT 1 FROM sr_resource r WHERE  r.id = t.resource_id)
1070
1071and exists(SELECT 1 FROM sr_res_role r WHERE r.id= t.role_id ) 
1072
1073and exists (select 1 from sr_res_group x where x.id = t.group_id)
1074
1075;
1076
1077
1078
1079insert into sr_res_group_relationship(id, resource_id,bdatetime, edatetime,group_id, role_id,is_disabled)
1080
1081  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
1082
1083  FROM  "typing_resources" t
1084
1085WHERE exists(SELECT 1 FROM sr_resource r WHERE  r.id = t.bed_id)
1086
1087and exists(SELECT 1 FROM sr_res_role r WHERE r.id= t.role_id ) 
1088
1089;
1090
1091
1092
1093
1094
1095
1096
1097update "typing_resources" t set errors = concat(errors, ''{"level":"resource", "message":"не найден ресурс по resource_id = '', resource_id, ''", "column_name":"resource_id"}'')
1098
1099 where not exists (select 1 from sr_resource ct where ct.id = t.resource_id);
1100
1101update "typing_resources" t set errors = concat(errors, ''{"level":"resource", "message": "не найдена роль по role_id = '', role_id, ''", "column_name":"role_id"}'')
1102
1103 where not exists (select 1 from sr_res_role ct where ct.id = t.role_id);
1104
1105update "typing_resources" t set errors = concat(errors, ''{"level":"resource", "message": "МО сотрудника и МО случая не совпадают", "column_name":"id"}'')
1106
1107 where not exists (select 1 
1108
1109	 from pim_employee_position_resource epr
1110
1111     join pim_employee_position ep on epr.employee_position_id = ep.id
1112
1113     join pim_employee e on ep.employee_id = e.id
1114
1115	 WHERE epr.id = t.resource_id
1116
1117	 and e.organization_id = t.org_id
1118
1119	 ) and  t.resource_id is not null
1120
1121    ;
1122
1123 
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133-- 3. Шаги--------------------------------------------------------------------------
1134
1135-- 3.1 добавление реальных колонок
1136
1137alter table "typing_visits"
1138
1139 add column case_id integer,
1140
1141 add column id integer,
1142
1143 add column type_id integer,
1144
1145 add column admission_date date,
1146
1147 add column outcome_date date,
1148
1149 add column place_id integer,
1150
1151 add column initiator_id integer,
1152
1153 add column goal_id integer,
1154
1155 add column profile_id integer,
1156
1157 add column standard_id integer,
1158
1159 add column mes_id integer,
1160
1161 add column res_group_id integer,
1162
1163 add column result_id integer,
1164
1165 add column stepResultReason_id integer,
1166
1167 add column outcome_id integer,
1168
1169 add column patient_id integer,
1170
1171 add column clinic_id integer,
1172
1173 add column doctor_id integer,
1174
1175 add column is_viewed boolean,
1176
1177 add column is_needed boolean,
1178
1179 add column is_sanitized boolean,
1180
1181add COLUMN department_id INTEGER,
1182
1183add COLUMN hosp_department_id integer,
1184
1185add COLUMN bed_profile_id INTEGER,
1186
1187    add column csg_id INTEGER,
1188
1189add column complexity_level_id INTEGER,
1190
1191add column deviation_reason_id INTEGER,
1192
1193add column vmp_type_id INTEGER,
1194
1195add column vmp_method_id INTEGER,
1196
1197 add column errors text default ''''
1198
1199 
1200
1201;
1202
1203
1204
1205-- 3.2 Заполнение реальных колонок
1206
1207update "typing_visits" set
1208
1209 case_id = (select coalesce(id,new_id) from "typing_case"),
1210
1211 patient_id = (select patient_id from "typing_case"),
1212
1213 clinic_id = (select clinic_id from "typing_case"),
1214
1215 id = typing.to_int(_id),
1216
1217 type_id = typing.to_int(_type_id),
1218
1219 admission_date = typing.to_dt(_admission_date),
1220
1221 outcome_date = typing.to_dt(_outcome_date),
1222
1223 place_id = typing.to_int(_place_id),
1224
1225 initiator_id = typing.to_int(_initiator_id),
1226
1227 goal_id = typing.to_int(_goal_id),
1228
1229 profile_id = typing.to_int(_profile_id),
1230
1231 standard_id = typing.to_int(_standard_id),
1232
1233 mes_id = typing.to_int(_mes_id),
1234
1235 res_group_id = typing.to_int(_res_group_id),
1236
1237 stepResultReason_id = typing.to_int(_stepResultReason_id),
1238
1239 result_id = typing.to_int(_result_id),
1240
1241 outcome_id = typing.to_int(_outcome_id),
1242
1243 doctor_id = typing.to_int(_doctor_id),
1244
1245department_id = typing.to_int(_department_id),
1246
1247bed_profile_id = typing.to_int(_bed_profile_id),
1248
1249csg_id = typing.to_int(_csg_id),
1250
1251hosp_department_id = typing.to_int(_hosp_department_id),
1252
1253complexity_level_id= typing.to_int(_complexity_level_id),
1254
1255deviation_reason_id = typing.to_int(_deviation_reason_id),
1256
1257vmp_type_id  = typing.to_int(_vmp_type_id),
1258
1259vmp_method_id = typing.to_int(_vmp_method_id),
1260
1261 is_viewed =  case when _is_viewed = ''true'' or _is_viewed = ''1'' then true else false end,
1262
1263 is_needed =  case when _is_needed = ''true'' or _is_needed = ''1'' then true else false end,
1264
1265 is_sanitized =  case when _is_sanitized = ''true'' or _is_sanitized = ''1'' then true else false end
1266
1267;
1268
1269
1270
1271update "typing_visits" v
1272
1273set res_group_id = group_id
1274
1275FROM "typing_resources" r
1276
1277WHERE (v.step_num = r.step_num
1278
1279or (r.step_num is null)) --- если для всего случая - r.step_id должен быть пустой 
1280
1281and v.res_group_id is null
1282
1283;
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293-- 3.2.1 Удаляю информацию по закрытию случая.
1294
1295update mc_step s 
1296
1297set result_id =null
1298
1299FROM  "typing_case" c WHERE c.id is not null and c.id =s.case_id
1300
1301and exists(SELECT 1 FROM mc_step_result r WHERE  r.id = s.result_id and is_closed = true);
1302
1303
1304
1305update "typing_visits" t
1306
1307  set res_group_id =  typing.typing_gen_res_group(t.doctor_id, null::INTEGER)
1308
1309  WHERE exists(SELECT 1 FROM "typing_visits" t JOIN mc_step s ON s.id = t.id)
1310
1311and exists(SELECT 1 FROM sr_res_group s WHERE s.id = t.res_group_id and is_system =false)
1312
1313and t.doctor_id is not null;
1314
1315
1316
1317update "typing_visits" t
1318
1319  set res_group_id = typing.typing_gen_res_group(null, t.res_group_id)
1320
1321  WHERE exists(SELECT 1 FROM "typing_visits" t JOIN mc_step s ON s.id = t.id)
1322
1323and exists(SELECT 1 FROM sr_res_group s WHERE s.id = t.res_group_id and is_system =false)
1324
1325and t.res_group_id is not null
1326
1327and id is null
1328
1329;
1330
1331
1332
1333update "typing_visits" t
1334
1335  set res_group_id =    typing.typing_gen_res_group(null, t.res_group_id)
1336
1337from mc_step step
1338
1339  WHERE exists(SELECT 1 FROM "typing_visits" t JOIN mc_step s ON s.id = t.id)
1340
1341and exists(SELECT 1 FROM sr_res_group s WHERE s.id = t.res_group_id and is_system =false)
1342
1343
1344
1345and t.id is not null
1346
1347and t.id = step.id
1348
1349;
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359-- 3.3 Oбновляю шаги
1360
1361update mc_step st set
1362
1363 admission_date = t.admission_date,
1364
1365 outcome_date =  coalesce(t.outcome_date,t.admission_date),
1366
1367 profile_id = t.profile_id,
1368
1369 mes_id = t.mes_id,
1370
1371 standard_id = t.standard_id,
1372
1373 res_group_id =  t.res_group_id,
1374
1375 result_id = t.result_id,
1376
1377 reason_id = t.stepResultReason_id,
1378
1379 outcome_id = t.outcome_id,
1380
1381 vmp_method_id =t.vmp_method_id,
1382
1383 vmp_type_id = t.vmp_type_id
1384
1385from "typing_visits" t where st.id = t.id and
1386
1387 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
1388
1389 exists (select 1 from plc_visit_place x where x.id = t.place_id) and
1390
1391 (exists (select 1 from md_profile x where x.id = t.profile_id) or t.profile_id is null) and
1392
1393 (exists (select 1 from md_mes x where x.id = t.mes_id) or t.mes_id is null) and
1394
1395 (exists (select 1 from md_standard x where x.id = t.standard_id) or t.standard_id is null) and
1396
1397 --(exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null) and
1398
1399 (exists (select 1 from mc_case_init_goal x where x.id = t.goal_id)) and
1400
1401 (exists (select 1 from plc_initiator x where x.id = t.initiator_id) or t.initiator_id is null) and
1402
1403 (exists (select 1 from plc_visit_type x where x.id = t.type_id) or t.type_id is null) and
1404
1405 (exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null) and
1406
1407 (exists (select 1 from mc_step_result x where x.id = t.result_id) or t.result_id is null) and
1408
1409 (exists (select 1 from mc_step_result_reason x where x.id = t.stepResultReason_id) or t.stepResultReason_id is null) and
1410
1411 (exists (select 1 from mc_step_care_result x where x.id = t.outcome_id) or t.outcome_id is null)
1412
1413and (exists(SELECT 1 FROM mc_vmp_method x WHERE x.id = t.vmp_method_id) or  t.vmp_method_id is null)
1414
1415and (exists(SELECT 1 FROM mc_vmp_type x WHERE x.id = t.vmp_type_id) or t.vmp_type_id is null)
1416
1417;
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433update plc_visit v set
1434
1435 goal_id = t.goal_id,
1436
1437 initiator_id = t.initiator_id,
1438
1439 place_id = t.place_id,
1440
1441 type_id = t.type_id,
1442
1443 is_viewed = t.is_viewed,
1444
1445 is_needed = t.is_needed,
1446
1447 is_sanitized = t.is_sanitized
1448
1449from "typing_visits" t  CROSS JOIN "typing_case" c   where v.id = t.id and c.case_type_id <> 2 and
1450
1451 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
1452
1453 exists (select 1 from plc_visit_place x where x.id = t.place_id) and
1454
1455 (exists (select 1 from md_profile x where x.id = t.profile_id) or t.profile_id is null) and
1456
1457 (exists (select 1 from md_mes x where x.id = t.mes_id) or t.mes_id is null) and
1458
1459 (exists (select 1 from md_standard x where x.id = t.standard_id) or t.standard_id is null) and
1460
1461 (exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null) and
1462
1463 (exists (select 1 from mc_case_init_goal x where x.id = t.goal_id)) and
1464
1465 (exists (select 1 from plc_initiator x where x.id = t.initiator_id) or t.initiator_id is null) and
1466
1467 (exists (select 1 from plc_visit_type x where x.id = t.type_id) or t.type_id is null) and
1468
1469 (exists (select 1 from mc_step_result x where x.id = t.result_id) or t.result_id is null) and
1470
1471 (exists (select 1 from mc_step_care_result x where x.id = t.outcome_id) or t.outcome_id is null) and
1472
1473 (exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null) and
1474
1475 (exists (select 1 from mc_step_result x where x.id = t.result_id) or t.result_id is null) and
1476
1477 (exists (select 1 from mc_step_care_result x where x.id = t.outcome_id) or t.outcome_id is null)
1478
1479
1480
1481 ;
1482
1483
1484
1485update sr_res_group_relationship rgr set resource_id = t.doctor_id
1486
1487from "typing_visits" t, sr_res_role r where t.res_group_id = rgr.group_id and r.id = rgr.role_id and r.code = ''DOCTOR'' and t.doctor_id is not null 
1488
1489and exists(SELECT 1 FROM "typing_visits" t JOIN mc_step s ON s.id = t.id)
1490
1491and exists(SELECT 1 FROM sr_res_group s WHERE s.id = t.res_group_id and is_system = true);
1492
1493 
1494
1495
1496
1497
1498
1499 update hsp_record h set
1500
1501 mes_id = t.mes_id,
1502
1503 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,
1504
1505 funding_id =coalesce(t.funding_id, c.funding_id)
1506
1507 from "typing_visits" t CROSS JOIN "typing_case" c   where h.id = t.id and c.case_type_id = 2 and h.id = t.id  and exists (select 1 from mc_case c where c.id = t.case_id) and exists (select 1 from mc_step st where st.id = t.id) and t.admission_date is not null ;
1508
1509
1510
1511
1512
1513
1514
1515-- 3.4 Добавляю ошибку, если обновляемый шаг не существует
1516
1517update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден шаг по id = '', id, ''", "column_name":"id"}'')
1518
1519 where id is not null and not exists (select 1 from mc_step x where x.id = t.id);
1520
1521
1522
1523update typing_visits set case_id = coalesce(c.id,c.new_id) from typing_case c;
1524
1525
1526
1527-- 3.5 Добавляю шаги, если нет id
1528
1529alter table "typing_visits" add column new_id integer;
1530
1531update "typing_visits" t
1532
1533set new_id = nextval(''mc_step_seq'')
1534
1535where id is null and
1536
1537 exists (select 1 from mc_case c where c.id = t.case_id) and t.admission_date is not null and
1538
1539 exists (select 1 from plc_visit_place x where x.id = t.place_id) and
1540
1541 (exists (select 1 from md_profile x where x.id = t.profile_id) or t.profile_id is null) and
1542
1543 (exists (select 1 from md_mes x where x.id = t.mes_id) or t.mes_id is null) and
1544
1545 (exists (select 1 from md_standard x where x.id = t.standard_id) or t.standard_id is null) and
1546
1547 (exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null) and
1548
1549 (exists (select 1 from mc_case_init_goal x where x.id = t.goal_id)) and
1550
1551 (exists (select 1 from plc_initiator x where x.id = t.initiator_id) or t.initiator_id is null) and
1552
1553 (exists (select 1 from plc_visit_type x where x.id = t.type_id) or t.type_id is null) and
1554
1555 (exists (select 1 from mc_step_result x where x.id = t.result_id) or t.result_id is null) and
1556
1557 (exists (select 1 from mc_step_care_result x where x.id = t.outcome_id) or t.outcome_id is null) 
1558
1559 and(exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null)
1560
1561;
1562
1563
1564
1565update "typing_visits" t
1566
1567set new_id = nextval(''mc_step_seq'')
1568
1569where  id is null and exists (select 1 from mc_case c where c.id = t.case_id) and t.admission_date is not null
1570
1571and(exists(SELECT 1 FROM mc_deviation_reason x WHERE x.id = t.deviation_reason_id) or t.deviation_reason_id is null)
1572
1573and(exists(select 1 FROM mc_complexity_level x WHERE x.id = t.complexity_level_id) or t.complexity_level_id is null)
1574
1575and (exists(SELECT 1 FROM md_profile p WHERE p.id = t.profile_id) or t.profile_id is null) 
1576
1577and (exists(select 1 FROM mc_step_result x WHERE t.result_id = x.id)
1578
1579or  t.result_id is null)
1580
1581and (exists(SELECT 1 FROM mc_step_care_result x WHERE x.id = t.outcome_id ) or t.outcome_id is null)
1582
1583and (exists(SELECT 1 FROM mc_vmp_method x WHERE x.id = t.vmp_method_id) or  t.vmp_method_id is null)
1584
1585and (exists(SELECT 1 FROM mc_vmp_type x WHERE x.id = t.vmp_type_id) or t.vmp_type_id is null)
1586
1587
1588
1589;
1590
1591
1592
1593update "typing_visits" t
1594
1595set 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;
1596
1597
1598
1599insert into mc_step(id, admission_date,admission_time, outcome_date,outcome_time, case_id, outcome_id, result_id, reason_id, profile_id, mes_id,csg_id, res_group_id,regimen_id,outcome_regimen_id,deviation_reason_id, vmp_method_id, vmp_type_id)
1600
1601select t.new_id, t.admission_date,coalesce(admission_time,''0:0:0''::TIME),  CASE when t.outcome_date is null then t.admission_date else t.outcome_date end,outcome_time,t.case_id, t.outcome_id, t.result_id, t.stepResultReason_id, t.profile_id, t.mes_id,coalesce(t.csg_id,standarts.csg_id),t.res_group_id, c.care_regimen_id, c.care_regimen_id,deviation_reason_id,coalesce(t.vmp_method_id,standarts.vmp_method_id),coalesce(t.vmp_type_id,standarts.vmp_type_id) from "typing_visits" t
1602
1603  cross join   "typing_case" c left join (select * from typing_standarts standarts limit 1)  standarts on true
1604
1605 where
1606
1607 t.new_id is not null --and case_id is not null and admission_date is not null and place_id is not null
1608
1609
1610
1611;
1612
1613
1614
1615
1616
1617insert into plc_visit(id, goal_id, initiator_id, place_id, type_id, is_viewed, is_needed, is_sanitized)
1618
1619select t.new_id, goal_id, initiator_id, place_id, type_id, is_viewed, is_needed, is_sanitized from "typing_visits" t  cross join   "typing_case" where case_type_id <> 2
1620
1621and t.new_id is not null and place_id is not null
1622
1623and exists(SELECT 1 FROM mc_step x WHERE x.id =t.new_id)
1624
1625and exists(SELECT 1 FROM plc_visit_place x WHERE x.id =t.place_id)
1626
1627and t.new_id is not null
1628
1629 --and case_id is not null and admission_date is not null and place_id is not null
1630
1631
1632
1633;
1634
1635
1636
1637
1638
1639insert into hsp_record(id,mes_id,funding_id,department_id, bed_profile_id,issue_planned_date,complexity_level_id)
1640
1641select t.new_id, mes_id ,coalesce(t.funding_id, "typing_case".funding_id), CASE WHEN step_num =''1'' then COALESCE(hosp_department_id, department_id) else coalesce(department_id, hosp_department_id) end,bed_profile_id, CASE when exists(SELECT 1 FROM mc_step_result x WHERE is_closed = true and id in(SELECT result_id FROM "typing_visits")) then (select max(outcome_date) from "typing_visits" ) end,complexity_level_id from "typing_visits" t cross join   "typing_case"   where case_type_id = 2
1642
1643and t.new_id is not null 
1644
1645and exists(SELECT 1 FROM mc_step s WHERE s.id = t.new_id)
1646
1647and exists(select 1 FROM pim_department x WHERE x.id =COALESCE(hosp_department_id, department_id))
1648
1649;
1650
1651
1652
1653
1654
1655
1656
1657update hsp_record h
1658
1659set previous_id = (SELECT max(t.new_id) FROM "typing_visits" t WHERE t.new_id<h.id)
1660
1661FROM "typing_visits" t WHERE h.id in (SELECT new_id FROM "typing_visits")
1662
1663and exists( select 1 from hsp_record  where id = (SELECT max(t.new_id) FROM "typing_visits" t WHERE t.new_id<h.id))
1664
1665;
1666
1667
1668
1669update "typing_visits" set id = new_id where new_id is not null;
1670
1671
1672
1673-- 3.6 Добавляю все ошибки при добавлении либо обновлении шага
1674
1675update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не заполнен admission_date", "column_name":"admission_date"}'')
1676
1677 where t.admission_date is null;
1678
1679update "typing_visits" t set errors = concat(t.errors, ''{"level":"step", "message":"не заполнен place_id", "column_name":"place_id"}'')
1680
1681from  "typing_case" c
1682
1683 where t.place_id is null
1684
1685 and not exists (SELECT 1 FROM  "typing_case" where case_type_id = 2);
1686
1687update "typing_visits" t set errors = concat(t.errors, ''{"level":"step", "message":"неверный place_id = ", "column_name":"place_id"}'')
1688
1689from  "typing_case" c
1690
1691 where not exists(SELECT 1 FROM plc_visit_place x WHERE x.id =t.place_id) and t.place_id is not null;
1692
1693update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден profile_id = '', profile_id, ''", "column_name":"profile_id"}'')
1694
1695 where profile_id is not null and not exists (select 1 from md_profile x where x.id = t.profile_id)
1696
1697;
1698
1699update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден mes_id = '', mes_id, ''", "column_name":"mes_id"}'')
1700
1701 where mes_id is not null and not exists (select 1 from md_mes x where x.id = t.mes_id);
1702
1703update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден standard_id = '', standard_id, ''", "column_name":"standard_id"}'')
1704
1705 where standard_id is not null and not exists (select 1 from md_standard x where x.id = t.standard_id);
1706
1707update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден res_group_id = '', res_group_id, ''", "column_name":"res_group_id"}'')
1708
1709 where res_group_id is not null and not exists (select 1 from sr_res_group x where x.id = t.res_group_id);
1710
1711update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден goal_id = '', goal_id, ''", "column_name":"goal_id"}'')
1712
1713 where not exists (select 1 from mc_case_init_goal x where x.id = t.goal_id);
1714
1715update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден initiator_id = '', initiator_id, ''", "column_name":"initiator_id"}'')
1716
1717 where initiator_id is not null and not exists (select 1 from plc_initiator x where x.id = t.initiator_id);
1718
1719update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден type_id = '', type_id, ''", "column_name":"type_id"}'')
1720
1721 where type_id is not null and not exists (select 1 from plc_visit_type x where x.id = t.type_id);
1722
1723update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден result_id = '', result_id, ''", "column_name":"result_id"}'')
1724
1725 where result_id is not null and not exists (select 1 from mc_step_result x where x.id = t.result_id);
1726
1727update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден outcome_id = '', outcome_id, ''", "column_name":"outcome_id"}'')
1728
1729 where outcome_id is not null and not exists (select 1 from mc_step_care_result x where x.id = t.outcome_id);
1730
1731
1732
1733update "typing_visits" t set errors = concat(errors, ''{"level":"step", "message":"не найден result_id '', result_id, ''", "column_name":"result_id"}'')
1734
1735 where not exists(SELECT 1 FROM "typing_visits" WHERE result_id is not null );
1736
1737update "typing_visits" t set  errors = concat(t.errors, ''{"level":"step", "message": "Не найден department_id =  '', department_id, ''", "column_name":"outcome_id"}'')
1738
1739FROM "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;
1740
1741update "typing_visits" t set  errors = concat(t.errors, ''{"level":"step", "message": "Не найден hosp_department_id =  '', hosp_department_id, ''", "column_name":"outcome_id"}'')
1742
1743FROM "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;
1744
1745
1746
1747update "typing_visits" t set  errors = concat(t.errors, ''{"level":"step", "message": "МО сотрудника и МО случая не совпадают  '', doctor_id, ''", "column_name":"doctor_id"}'')
1748
1749where not exists (select 1 
1750
1751	 from pim_employee_position_resource epr
1752
1753     join pim_employee_position ep on epr.employee_position_id = ep.id
1754
1755     join pim_employee e on ep.employee_id = e.id
1756
1757	 WHERE epr.id = t.doctor_id
1758
1759	 and e.organization_id = t.clinic_id)
1760
1761and t.doctor_id is not null;
1762
1763
1764
1765 update "typing_visits" t set errors= concat(t.errors, ''{"level":"step", "message": "Дата поступления в зог меньше даты выбытия из предыдущего!", "column_name":"doctor_id"}'')
1766
1767	from typing_visits t1 where t.admission_date < t1.outcome_date and t.outcome_date >t1.outcome_date;
1768
1769
1770
1771update "typing_visits" t set errors = concat(t.errors, ''{"level":"step", "message":"не заполнен Ресурс", "column_name":"res_group_id"}'')
1772
1773 where res_group_id is null;
1774
1775
1776
1777-- 3.5 Коэффициент курации
1778
1779alter table "typing_cur"
1780
1781 add column id integer,
1782
1783 add column criteria_id integer,
1784
1785 add column new_id integer,
1786
1787 add column value numeric,
1788
1789 add column errors text default ''''
1790
1791;
1792
1793
1794
1795
1796
1797update typing_cur set
1798
1799id = typing.to_int(_id),
1800
1801criteria_id=typing.to_int(_criteria_id),
1802
1803value = to_numeric(_value)
1804
1805;
1806
1807
1808
1809--- Обновляю, если есть id
1810
1811update mc_step_cur_coef_criteria c
1812
1813set value = t.value,
1814
1815criteria_id = t.criteria_id
1816
1817FROM typing_cur t WHERE c.id = t.id
1818
1819;
1820
1821
1822
1823
1824
1825
1826
1827update typing_cur c
1828
1829set new_id = nextval(''mc_step_cur_seq'')
1830
1831WHERE exists(SELECT 1 FROM mc_cur_coef_criteria x WHERE x.id = c.criteria_id)
1832
1833and value is not null
1834
1835;
1836
1837
1838
1839insert into mc_step_cur_coef_criteria(id, criteria_id,step_id,value)
1840
1841SELECT 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
1842
1843WHERE c.new_id is not null and v.new_id is not null;
1844
1845
1846
1847
1848
1849-- Добавляю ошибки
1850
1851
1852
1853update "typing_cur" t set  errors = concat(t.errors, ''{"level":"cur", "message":" Не указан criteria_id '', criteria_id, ''", "column_name":"criteria_id"}'')
1854
1855 where criteria_id is null;
1856
1857update "typing_cur" t set  errors = concat(t.errors, ''{"level":"cur", "message":" Не указан step_num'', step_num, ''", "column_name":"step_num"}'')
1858
1859where step_num is null;
1860
1861update "typing_cur" t set  errors = concat(t.errors, ''{"level":"cur", "message":" Не указан value'', value, ''", "column_name":"value"}'')
1862
1863 where value is null;
1864
1865update "typing_cur" t set  errors = concat(t.errors, ''{"level":"cur", "message":" Не найден criteria_id ='', criteria_id, ''", "column_name":"criteria_id"}'')
1866
1867 where criteria_id is not null and not exists(SELECT 1 FROM mc_cur_coef_criteria x WHERE x.id = t.criteria_id);
1868
1869
1870
1871
1872
1873-- 4. Услуги-----------------------------------------------------------------------------------------------------------------------------
1874
1875-- 4.1 Добавление реальных колонок
1876
1877alter table "typing_services"
1878
1879 add column id integer,
1880
1881 add column step_id integer,
1882
1883 add column case_id integer,
1884
1885 add column service_id integer,
1886
1887 add column bdate date,
1888
1889 add column funding_id integer,
1890
1891 add column quantity integer,
1892
1893 add column res_group_id integer,
1894
1895 add column mkb_id integer,
1896
1897 add column clinic_id integer,
1898
1899 add column patient_id integer,
1900
1901 add column new_id integer,
1902
1903 add column doctor_id integer,
1904
1905 add column cul numeric,
1906
1907 add column is_refused boolean,
1908
1909 add column diag_type_id int,
1910
1911 add column diag_disease_type_id int,
1912
1913 add column diag_stage_id int,
1914
1915 add column org_id int,
1916
1917add column result_type_id int,
1918
1919add column comment text,
1920
1921 add column errors text default ''''
1922
1923;
1924
1925
1926
1927-- 4.2 Заполнение реальных колонок 
1928
1929update "typing_services" s set
1930
1931 case_id = (select coalesce(id,new_id) from "typing_case"),
1932
1933 id = typing.to_int(_id),
1934
1935 step_id = typing.to_int(_step_id),
1936
1937 service_id = typing.to_int(_service_id),
1938
1939 bdate =  typing.to_dt(_bdate),
1940
1941 funding_id = typing.to_int(_funding_id),
1942
1943 quantity = typing.to_int(_quantity),
1944
1945 res_group_id = typing.to_int(_res_group_id),
1946
1947 mkb_id = typing.to_int(_mkb_id),
1948
1949 is_refused = CASE when _is_refused is null then false else _is_refused = ''1'' end,
1950
1951 doctor_id = typing.to_int(_doctor_id),
1952
1953 clinic_id = (select clinic_id from "typing_case" where clinic_id is not null),
1954
1955 patient_id = (select patient_id from "typing_case"),
1956
1957 diag_stage_id = typing.to_int(_diag_stage_id),
1958
1959 diag_type_id = typing.to_int(_diag_type_id) ,
1960
1961 diag_disease_type_id= typing.to_int(_diag_disease_type_id),
1962
1963 cul = to_numeric(_cul),
1964
1965 org_id = typing.to_int(_org_id),
1966
1967 result_type_id = typing.to_int(_result_type_id)
1968
1969;
1970
1971
1972
1973
1974
1975update "typing_services" s
1976
1977set res_group_id = group_id
1978
1979FROM "typing_resources" r
1980
1981WHERE (s.step_num = r.step_num
1982
1983or (r.step_num is null)) --- если для всего случая - r.step_id должен быть пустой 
1984
1985and s.res_group_id is null
1986
1987;
1988
1989
1990
1991update "typing_services" s
1992
1993set comment = ''Бригада = '' || srg.name
1994
1995from typing_visits v join typing_services sr on  v.step_num = sr.step_num  join  sr_res_group srg on srg.id = coalesce(sr.brigade_id,v.brigade_id)
1996
1997where s.service_num = sr.service_num
1998
1999;
2000
2001
2002
2003
2004
2005-- 4.3 Обновляю услуги
2006
2007update sr_srv_rendered r set
2008
2009       bdate = t.bdate, funding_id = t.funding_id, res_group_id =coalesce( v.res_group_id, t.res_group_id) /*res_group_id = t.res_group_id*/, service_id = t.service_id, customer_id = t.patient_id, org_id = t.clinic_id, cul = t.cul,is_amalgam_filling =t.is_amalgam_filling,comment = t.comment
2010
2011  from "typing_services" t left JOIN "typing_visits"  v ON v.step_num =t.step_num
2012
2013 where t.id = r.id and exists (select 1 from mc_case c where c.id = t.case_id)
2014
2015   and (exists (select 1 from fin_funding_source_type x where x.id = t.funding_id) or t.funding_id is null)
2016
2017   --and (exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null)
2018
2019   and (exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null)
2020
2021   and exists (select 1 from sr_service x where x.id = t.service_id)
2022
2023   and exists (select 1 from pci_patient x where x.id = t.patient_id)
2024
2025   and exists (select 1 from md_clinic x where x.id = t.clinic_id)
2026
2027   and (exists (select 1 from mc_step x where x.id = t.step_id) or t.step_id is null)
2028
2029   and (exists (select 1 from md_diagnosis x where x.id = t.mkb_id) or t.mkb_id is null)
2030
2031;
2032
2033
2034
2035update md_srv_rendered r set
2036
2037 step_id = t.step_id, diagnosis_id = t.mkb_id, case_id = t.case_id, health_group_id =t.health_group_id
2038
2039from "typing_services" t
2040
2041where t.id = r.id and exists (select 1 from mc_case c where c.id = t.case_id) and
2042
2043 (exists (select 1 from fin_funding_source_type x where x.id = t.funding_id) or t.funding_id is null) and
2044
2045 --(exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null) and
2046
2047 (exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null) and
2048
2049 (exists (select 1 from sr_service x where x.id = t.service_id)) and
2050
2051 exists (select 1 from pci_patient x where x.id = t.patient_id) and
2052
2053 exists (select 1 from md_clinic x where x.id = t.clinic_id) and
2054
2055 (exists (select 1 from mc_step x where x.id = t.step_id) or t.step_id is null) and
2056
2057 (exists (select 1 from md_diagnosis x where x.id = t.mkb_id) or t.mkb_id is null) and
2058
2059 (exists (select 1 from mc_health_group x where x.id =t.health_group_id) or t.health_group_id is null)
2060
2061;
2062
2063
2064
2065
2066
2067update sr_srv_result r
2068
2069set value = result_value,
2070
2071result_type_id = t.result_type_id
2072
2073from "typing_services" t
2074
2075where t.id is not null
2076
2077and r.service_id = t.id 
2078
2079and r.id = t.srv_result_id
2080
2081and (exists (select 1 from sr_srv_result_type x where x.id = t.result_type_id) or t.result_type_id is null)
2082
2083;
2084
2085
2086
2087
2088
2089
2090
2091-- 4.4 Добавляю ошибку (отсутствует id услуги)
2092
2093update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найдена услуга по id = '', id, ''", "column_name":"id"}'')
2094
2095 where id is not null and not exists (select 1 from sr_srv_rendered x where x.id = t.id);
2096
2097
2098
2099-- 4.5 Добавляю услуги, если нет id
2100
2101-- 4.5 Добавляю услуги, если нет id
2102
2103update "typing_services" t set new_id = nextval(''sr_srv_rendered_seq'')
2104
2105where id is null and exists (select 1 from mc_case c where c.id = t.case_id) and
2106
2107(exists (select 1 from fin_funding_source_type x where x.id = t.funding_id) or t.funding_id is null) and
2108
2109 --(exists (select 1 from sr_res_group x where x.id = t.res_group_id) or t.res_group_id is null) and
2110
2111 (exists (select 1 from sr_resource x where x.id = t.doctor_id) or t.doctor_id is null) and
2112
2113--(exists (select 1 from sr_service x where x.id = t.service_id)) and
2114
2115exists (select 1 from pci_patient x where x.id = t.patient_id) and
2116
2117 exists (select 1 from md_clinic x where x.id = t.clinic_id) and
2118
2119 (exists (select 1 from mc_step x where x.id = t.step_id) or t.step_id is null) and
2120
2121 (exists (select 1 from md_diagnosis x where x.id = t.mkb_id) or t.mkb_id is null)
2122
2123and  (exists (select 1 from mc_health_group x where x.id =t.health_group_id) or t.health_group_id is null)
2124
2125 ;
2126
2127
2128
2129
2130
2131insert 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)
2132
2133select s.new_id,
2134
2135--CASE when s.bdate is null then v.admission_date else s.bdate end
2136v.admission_date,
2137
2138--coalesce(s.bdate,v.admission_date)
2139v.admission_date ,
2140
2141 s.service_id, s.patient_id, s.quantity, 
2142
2143COALESCE(s.org_id,s.clinic_id),
2144
2145 true,
2146
2147      coalesce(typing.typing_gen_res_group(s.doctor_id, s.res_group_id),v.res_group_id),
2148
2149     s.cul, s.funding_id, s.comment, 
2150
2151CASE when (s.is_refused is null) then false else s.is_refused end,
2152
2153    s.tooth_number, is_amalgam_filling
2154
2155  from "typing_services" s left JOIN "typing_visits"  v ON v.step_num =s.step_num  where s.new_id is not null and s.service_id is not null
2156
2157  and (exists (select 1 from sr_service x where x.id = s.service_id and x.org_id = s.org_id)
2158
2159   or exists (select 1 from sr_service x where x.id = s.service_id and x.org_id = s.clinic_id))
2160
2161
2162
2163;
2164
2165insert into md_srv_rendered(id, step_id, case_id, diagnosis_id,anatomic_zone_id, health_group_id)
2166
2167select s.new_id, 
2168
2169CASE when (s.org_id is null or s.org_id = s.clinic_id) then coalesce(v.id,v.new_id) end,
2170
2171CASE when (s.org_id is null or s.org_id = s.clinic_id) then s.case_id end,
2172
2173CASE when (s.org_id is null or s.org_id = s.clinic_id) then s.mkb_id end,
2174
2175anatomic_zone_id,health_group_id
2176
2177from "typing_services" s left join "typing_visits" v on v.step_num = s.step_num
2178
2179where s.new_id is not null
2180
2181and exists(SELECT 1 FROM mc_case x WHERE  x.id = s.case_id)
2182
2183  and (exists (select 1 from sr_service x where x.id = s.service_id and x.org_id = s.org_id)
2184
2185   or exists (select 1 from sr_service x where x.id = s.service_id and x.org_id = s.clinic_id))
2186
2187and (exists(select 1 from md_anatomic_zone x where x.id = s.anatomic_zone_id) or  s.anatomic_zone_id is null)
2188
2189
2190
2191;
2192
2193insert into sr_srv_result (id,value,service_id, result_type_id)
2194
2195select nextval(''sr_srv_result_id_seq''),result_value,coalesce(s.id,s.new_id),result_type_id
2196
2197from typing_services  s where s.result_value is not null;
2198
2199
2200
2201
2202
2203SELECT typing.typing_insert_into_disp();
2204
2205
2206
2207update "typing_services" s set id = new_id where new_id is not null and s.service_id is not null;
2208
2209
2210
2211-- 4.6 Добавляю все ошибки услуг
2212
2213update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден funding_id = '', funding_id, ''", "column_name":"funding_id"}'')
2214
2215 where t.funding_id is not null and not exists (select 1 from fin_funding_source_type x where x.id = t.funding_id);
2216
2217update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден res_group_id = '', res_group_id, ''", "column_name":"res_group_id"}'')
2218
2219 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);
2220
2221--update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден service_id = '', service_id, ''", "column_name":"service_id"}'')
2222
2223-- where not exists (select 1 from sr_service x where x.id = t.service_id);
2224
2225update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден step_id = '', step_id, ''", "column_name":"step_id"}'')
2226
2227 where t.step_id is not null and not exists (select 1 from mc_step x where x.id = t.step_id);
2228
2229--update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден mkb_id = '', mkb_id, ''", "column_name":"mkb_id"}'')
2230
2231-- where t.mkb_id is not null and not exists (select 1 from md_diagnosis x where x.id = t.mkb_id);
2232
2233update "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"}'')
2234
2235 where not exists (select 1 from sr_service x where x.id = t.service_id and x.org_id =t.org_id)
2236
2237and not exists (select 1 from sr_service x where x.id = t.service_id and x.org_id =t.clinic_id)
2238
2239and t.service_id is not null;
2240
2241update "typing_services" t set  errors = concat(t.errors, ''{"level":"srv", "message": "МО сотрудника и МО случая не совпадают  '', doctor_id, ''", "column_name":"doctor_id"}'')
2242
2243where not exists (select 1 
2244
2245	 from pim_employee_position_resource epr
2246
2247     join pim_employee_position ep on epr.employee_position_id = ep.id
2248
2249     join pim_employee e on ep.employee_id = e.id
2250
2251	 WHERE epr.id = t.doctor_id
2252
2253	 and e.organization_id = t.clinic_id)
2254
2255and t.doctor_id is not null;
2256
2257
2258
2259update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден с result_type_id='', result_type_id, ''", "column_name":"result_type_id"}'')
2260
2261 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;
2262
2263update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"не найден с srv_result_id='', srv_result_id, ''", "column_name":"result_type_id"}'')
2264
2265where not exists (select 1 from sr_srv_result x where x.id = t.srv_result_id) and t.srv_result_id is not null;
2266
2267
2268
2269update "typing_services" t set errors = concat(errors, ''{"level":"srv"
2270
2271, "message":"Русурс в услуге отличается от ресурса в посещении s.res_group_id='', (select d ->> ''res_group_id'' _res_group_id from (select  json_array_elements(value) d from json_each($1) where key=''services'')t )
2272
2273,'' <> v.res_group_id='',(select d ->> ''res_group_id'' _res_group_id from ( select  json_array_elements(value) d from json_each($1) where key=''visits'')t)
2274
2275, '' , ", "column_name":"res_group_id"}'')
2276
2277where 
2278
2279case when exists (select 1 from mc_case c where c.id = t.case_id and c.aud_when_create<>current_timestamp) then 1<>1 else 
2280
2281case when  exists(select 1 from (select  json_array_elements(value) d from json_each($1) where key=''services'')t where  d ->> ''res_group_id'' ='''')
2282
2283then 1<>1
2284
2285else (select d ->> ''res_group_id'' _res_group_id from (select  json_array_elements(value) d from json_each($1) where key=''services'')t )<>(select d ->> ''res_group_id'' _res_group_id from ( select  json_array_elements(value) d from json_each($1) where key=''visits'')t)
2286
2287end 
2288
2289end;
2290
2291/*update "typing_services" t set errors = concat(errors, ''{"level":"srv", "message":"дата в услуге отличается от даты посещения = '', bdate, ''", "column_name":"bdate"}'')
2292
2293 where case when t.bdate  is not null then 
2294 t.bdate not in(
2295 select (d ->> ''admission_date'')::date _admission_date from ( select  json_array_elements(value) d from json_each($1) where key=''visits'')t)
2296 else 1<>1
2297 end;*/
2298
2299 ;
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309-- 5. Диагнозы-----------------------------------------------------------------------------------------------------------------------------
2310
2311-- 5.1 Добавляю реальные колонки
2312
2313alter table "typing_diagnos"
2314
2315 add column id integer,
2316
2317 add column mkb_id integer,
2318
2319 add column disease_type_id integer,
2320
2321 add column is_main boolean,
2322
2323 add column stage_id integer,
2324
2325 add column step_id integer,
2326
2327 add column disp_id integer,
2328
2329 add column nosol_registr_id integer,
2330
2331 add column dispensary_group_id integer,
2332
2333 add column reg_in_dt date,
2334
2335 add column reg_out_dt date,
2336
2337 add column reg_in_doctor_id integer,
2338
2339 add column reg_out_doctor_id integer,
2340
2341 add column reg_stage_id integer,
2342
2343 add column reg_out_reason_id integer,
2344
2345 add column case_id integer,
2346
2347 add column patient_id integer,
2348
2349 add column clinic_id integer,
2350
2351 add column type_id integer,
2352
2353 add column new_id integer,
2354
2355 add column errors text default ''''
2356
2357
2358
2359;
2360
2361
2362
2363-- 5.2 Заполняю реальные колонки
2364
2365update "typing_diagnos" set
2366
2367 id = typing.to_int(_id),
2368
2369 mkb_id = typing.to_int(_mkb_id),
2370
2371 disease_type_id = typing.to_int(_disease_type_id),
2372
2373 is_main = case when _is_main = ''true'' or _is_main = ''1'' then true else false end,
2374
2375 stage_id = typing.to_int(_stage_id),
2376
2377 disp_id = typing.to_int(_disp_id),
2378
2379 nosol_registr_id = typing.to_int(_nosol_registr_id),
2380
2381 dispensary_group_id = typing.to_int(_dispensary_group_id),
2382
2383 reg_in_dt = typing.to_dt(_reg_in_dt),
2384
2385 reg_out_dt = typing.to_dt(_reg_out_dt),
2386
2387 reg_in_doctor_id = typing.to_int(_reg_in_doctor_id),
2388
2389 reg_out_doctor_id = typing.to_int(_reg_out_doctor_id),
2390
2391 reg_stage_id = typing.to_int(_reg_stage_id),
2392
2393 reg_out_reason_id = typing.to_int(_reg_out_reason_id),
2394
2395 type_id = typing.to_int(_type_id)
2396
2397;
2398
2399
2400
2401
2402
2403update "typing_diagnos" d set
2404
2405 case_id = coalesce(v.id, v.new_id),
2406
2407 patient_id = v.patient_id, 
2408
2409 clinic_id = v.clinic_id
2410
2411from "typing_case" v
2412
2413;
2414
2415
2416
2417update "typing_diagnos" d set
2418
2419 step_id = v.id
2420
2421from "typing_visits" v where v.step_num = d.step_num
2422
2423;
2424
2425
2426
2427
2428
2429
2430
2431-- 5.3 Обновляю диагнозы, если есть id
2432
2433update mc_diagnosis d set
2434
2435 diagnos_id = t.mkb_id,
2436
2437 disease_type_id = t.disease_type_id,
2438
2439 is_main = t.is_main,
2440
2441 case_id = t.case_id,
2442
2443 step_id = t.step_id,
2444
2445 patient_id = t.patient_id,
2446
2447 stage_id = t.stage_id,
2448
2449 type_id = t.type_id
2450
2451
2452
2453from "typing_diagnos" t
2454
2455 where d.id = t.id and
2456
2457  exists (select 1 from pci_patient x where x.id = t.patient_id) and
2458
2459  exists (select 1 from md_clinic x where x.id = t.clinic_id) and
2460
2461  exists (select 1 from mc_case c where c.id = t.case_id) and
2462
2463  exists (select 1 from md_diagnosis x where x.id = t.mkb_id) and
2464
2465  (exists (select 1 from mc_disease_type x where x.id = t.disease_type_id) or t.disease_type_id is null) and
2466
2467  (exists (select 1 from mc_stage x where x.id = t.stage_id) or t.stage_id is null) and
2468
2469  (exists (select 1 from mc_diagnosis_type x where x.id = t.type_id) or t.type_id is null)
2470
2471;
2472
2473
2474
2475-- 5.4 Добавляю ошибку (отсутствует id диагноза)
2476
2477update "typing_diagnos" t set errors = concat(errors, ''{"level":"dgn", "message":"не найден диагноз по id = '', id, ''", "column_name":"id"}'')
2478
2479 where id is not null and not exists (select 1 from mc_diagnosis x where x.id = t.id);
2480
2481
2482
2483-- 5.5 добавляю диагнозы, если нет id
2484
2485
2486
2487update "typing_diagnos" t set new_id = nextval(''mc_diagnosis_seq'')
2488
2489 where id is null and
2490
2491  exists (select 1 from pci_patient x where x.id = t.patient_id) and
2492
2493  exists (select 1 from md_clinic x where x.id = t.clinic_id) and
2494
2495  exists (select 1 from mc_case c where c.id = t.case_id) and
2496
2497  exists (select 1 from md_diagnosis x where x.id = t.mkb_id) and
2498
2499  (exists (select 1 from mc_disease_type x where x.id = t.disease_type_id) or t.disease_type_id is null) and
2500
2501  (exists (select 1 from mc_stage x where x.id = t.stage_id) or t.stage_id is null) and
2502
2503  (exists (select 1 from mc_diagnosis_type x where x.id = t.type_id) or t.type_id is null);
2504
2505
2506
2507update  "typing_diagnos" t
2508
2509set stage_id =4
2510
2511WHERE step_id =(select max(step_id) FROM "typing_diagnos" t)
2512
2513and not exists(select 1 FROM "typing_diagnos"  WHERE stage_id =4);
2514
2515
2516
2517insert into mc_diagnosis(id, diagnos_id, disease_type_id, case_id, patient_id, stage_id, step_id, type_id, is_main,establishment_date, note)
2518
2519 select d.new_id, d.mkb_id, d.disease_type_id, d.case_id, d.patient_id, d.stage_id, d.step_id, d.type_id, CASE when(select count(new_id)>1 FROM "typing_diagnos" WHERE is_main = true) then false else d.is_main end, admission_date, note from "typing_diagnos" d LEFT JOIN "typing_visits" v ON d.step_id=v.id  where d.new_id is not null
2520
2521--and (exists(SELECT 1 FROM mc_step x WHERE x.id =d.step_id))
2522
2523and d.mkb_id is not null
2524
2525;
2526
2527
2528
2529update mc_diagnosis d
2530
2531set is_main = true
2532
2533FROM (SELECT max(new_id) as id FROM typing_diagnos WHERE (select count(new_id)>1 FROM "typing_diagnos" WHERE is_main = true) 
2534
2535and case when exists(select 1 from typing_diagnos d where type_id =1 ) then type_id =1 else true end
2536
2537group by step_num
2538
2539
2540
2541
2542
2543) x
2544
2545WHERE x.id = d.id
2546
2547;
2548
2549
2550
2551
2552
2553
2554
2555update "typing_diagnos" set id = new_id where new_id is not null;
2556
2557
2558
2559
2560
2561update md_srv_rendered msr
2562
2563set diagnosis_id = CASE when msr.diagnosis_id is null then d.mkb_id else msr.diagnosis_id end,
2564
2565step_id = v.id
2566
2567from "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
2568
2569and (s.org_id is null or s.org_id = s.clinic_id)
2570
2571and d.mkb_id <> -1
2572
2573;
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583-- 5.6 добавляю все ошибки диагнозов
2584
2585update "typing_diagnos" t set errors = concat(errors, ''{"level":"dgn", "message":"не найден mkb_id = '', _mkb_id, ''", "column_name":"mkb_id"}'')
2586
2587WHERE mkb_id =-1;
2588
2589update "typing_diagnos" t set errors = concat(errors, ''{"level":"dgn", "message":"не найден disease_type_id = '', disease_type_id, ''", "column_name":"disease_type_id"}'')
2590
2591 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);
2592
2593update "typing_diagnos" t set errors = concat(errors, ''{"level":"dgn", "message":"не найден stage_id = '', stage_id, ''", "column_name":"stage_id"}'')
2594
2595 where t.stage_id is not null and not exists (select 1 from mc_stage x where x.id = t.stage_id);
2596
2597update "typing_diagnos" t set errors = concat(errors, ''{"level":"dgn", "message":"не найден type_id = '', type_id, ''", "column_name":"type_id"}'')
2598
2599 where t.type_id is not null and not exists (select 1 from mc_diagnosis_type x where x.id = t.type_id);
2600
2601
2602
2603-- 6. Больничный----------------------------------------------------------------------------
2604
2605-- 6.1 Обновление больничного
2606
2607update 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
2608
2609 from "typing_case" c
2610
2611  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));
2612
2613
2614
2615-- 6.2 Добавляю ошибку (не найден id больничного)
2616
2617update "typing_case" t set errors = concat(errors, ''{"level":"sl", "message":"не найден sicklist_id = '', sicklist_id, ''", "column_name":"sicklist_id"}'')
2618
2619 where t.sicklist_id is not null and not exists (select 1 from md_sicklist x where x.id = t.sicklist_id);
2620
2621
2622
2623-- 6.3 Добавляю больничный
2624
2625alter table "typing_case" add column new_sl_id integer;
2626
2627
2628
2629update "typing_case" c set new_sl_id = nextval(''md_sicklist_seq'')
2630
2631 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));
2632
2633
2634
2635update "typing_case" c
2636
2637set sl_surname= i.surname,
2638
2639sl_name = i.name,
2640
2641sl_patr_name = i.patr_name
2642
2643FROM pim_individual i WHERE i.id =c.patient_id
2644
2645and (sl_surname is null
2646
2647or sl_name is null
2648
2649or sl_patr_name is null or
2650
2651sl_surname = ''''
2652
2653or sl_name =''''
2654
2655or sl_patr_name =''''
2656
2657)
2658
2659; 
2660
2661
2662
2663insert 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)
2664
2665
2666
2667select new_sl_id,
2668
2669disability_from_dt,
2670
2671disability_to_dt,
2672
2673sl_code,
2674
2675disability_reason_id,
2676
2677clinic_id,
2678
2679patient_id,
2680
26811,
2682
26831,
2684
2685disability_from_dt,
2686
2687workplace_print,
2688
2689ready_to_work_dt,
2690
2691x.id,
2692
2693CASE when md_sicklist_state is null then 3 else md_sicklist_state end,
2694
2695sl_name,
2696
2697sl_surname,
2698
2699sl_patr_name,
2700
2701new_id
2702
2703from "typing_case" c  cross join(select md.id from "typing_diagnos" x
2704
2705Join mc_diagnosis mc on mc.id = x.id  
2706
2707join md_diagnosis md on md.id = mc.diagnos_id  
2708
2709left join mc_stage s on s.id = mc.stage_id and mc.is_main
2710
2711order by stage_order desc, id
2712
2713limit 1) x
2714
2715where new_sl_id is not null;
2716
2717
2718
2719
2720
2721
2722
2723insert into md_sicklist_period
2724
2725SELECT nextval(''md_sicklist_period__seq''),
2726
2727disability_from_dt,
2728
2729disability_to_dt,
2730
2731employee_position_id,
2732
2733new_sl_id,
2734
2735null
2736
2737from "typing_case" c
2738
2739where new_sl_id is not null
2740
2741and exists(select 1 FROM pim_employee_position p WHERE p.id =c.employee_position_id)
2742
2743and employee_position_id is not null
2744
2745and new_sl_id is not null
2746
2747;
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757update "typing_case" set sicklist_id = new_sl_id where new_sl_id is not null;
2758
2759
2760
2761-- 6.4 Добавляю ошибки больничного
2762
2763update "typing_case" t set errors = concat(errors, ''{"level":"sl", "message":"не найден disability_reason_id = '', disability_reason_id, ''", "column_name":"disability_reason_id"}'')
2764
2765 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);
2766
2767update "typing_case" t set errors = concat(errors, ''{"level":"sl", "message":"не указан номер больничного", "column_name":"sl_code"}'')
2768
2769 where (sl_code is null or sl_code = '''') and t.disability_reason_id is not null; --disability_reason_id - это признак того, что больничный заполнен
2770
2771  update "typing_case" t set errors = concat(errors, ''{"level":"sl", "message":"не найден employee_position_id = '', employee_position_id, ''", "column_name":"employee_position_id"}'')
2772
2773 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 );
2774
2775 update "typing_case" t set errors = concat(errors, ''{"level":"sl", "message":"не указан employee_position_id", "column_name":"employee_position_id"}'')
2776
2777 where (employee_position_id is null) and t.disability_reason_id is not null;
2778
2779
2780
2781 
2782
2783
2784
2785
2786
2787-- 7. Направления----------------------------------------------------------------------------
2788
2789-- 7.1 Обновляю направления
2790
2791update 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,
2792
2793 recv_organization_id = c.recv_organization_id, referral_type_id = c.referral_type_id
2794
2795from "typing_case" c where c.referral_id = r.id and c.referral_date is not null and
2796
2797 (exists(select 1 from pim_organization o where o.id = c.ref_organization_id)) and
2798
2799 (c.recv_organization_id is null or exists(select 1 from pim_organization o where o.id = c.recv_organization_id)) and
2800
2801 (c.ref_doctor_id is null or exists(select 1 from md_employee_position x where x.id = c.ref_doctor_id)) and
2802
2803 (c.ref_mkb_id is null or exists(select 1 from md_diagnosis x where x.id = c.ref_mkb_id)) and
2804
2805 (c.referral_type_id is null or exists(select 1 from md_referral_type x where x.id = c.referral_type_id)) and
2806
2807  exists (select 1 from pci_patient x where x.id = c.patient_id) and
2808
2809  exists (select 1 from md_clinic x where x.id = c.clinic_id)
2810
2811 -- exists (select 1 from mc_case x where x.id = c.id)
2812
2813;
2814
2815-- 7.2 Добавляю ошибку (не найден id направления в бд)
2816
2817update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден referral_id = '', referral_id, ''", "column_name":"referral_id"}'')
2818
2819 where t.referral_id is not null and not exists (select 1 from md_referral x where x.id = t.referral_id);
2820
2821
2822
2823-- 7.3 Добавляю направление
2824
2825alter table "typing_case" add column new_ref_id integer;
2826
2827
2828
2829update typing_case set referral_date = (select min(admission_date) from typing_visits) where referral_date is null;
2830
2831 
2832
2833
2834
2835update "typing_case" c set new_ref_id = nextval(''md_referral_seq'')
2836
2837where c.referral_id is null and
2838
2839 c.referral_date is not null and
2840
2841 (exists(select 1 from pim_organization o where o.id = c.ref_organization_id)) and
2842
2843 (c.recv_organization_id is null or exists(select 1 from pim_organization o where o.id = c.recv_organization_id)) and
2844
2845 (c.ref_doctor_id is null or exists(select 1 from md_employee_position x where x.id = c.ref_doctor_id)) and
2846
2847 (c.ref_mkb_id is null or exists(select 1 from md_diagnosis x where x.id = c.ref_mkb_id)) and
2848
2849 (c.referral_type_id is null or exists(select 1 from md_referral_type x where x.id = c.referral_type_id)) and
2850
2851  exists (select 1 from pci_patient x where x.id = c.patient_id) and
2852
2853  exists (select 1 from md_clinic x where x.id = c.clinic_id) and
2854
2855  exists (select 1 from mc_case x where x.id = coalesce(c.id,c.new_id))
2856
2857;
2858
2859insert 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)
2860
2861select 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
2862
2863 where new_ref_id is not null
2864
2865 and v.step_num = ''1'' ;
2866
2867
2868
2869--update "typing_case" set id = new_ref_id where new_ref_id is not null;
2870
2871
2872
2873update mc_case c set referral_id =new_ref_id from "typing_case" t where c.id = t.new_id;
2874
2875
2876
2877-- 7.4 Добавляю ошибки направления
2878
2879update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден ref_organization_id = '', ref_organization_id, ''", "column_name":"ref_organization_id"}'')
2880
2881 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));
2882
2883update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден recv_organization_id = '', recv_organization_id, ''", "column_name":"recv_organization_id"}'')
2884
2885 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));
2886
2887update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден ref_doctor_id = '', ref_doctor_id, ''", "column_name":"ref_doctor_id"}'')
2888
2889 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));
2890
2891update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден ref_mkb_id = '', ref_mkb_id, ''", "column_name":"ref_mkb_id"}'')
2892
2893 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));
2894
2895update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не найден referral_type_id = '', referral_type_id, ''", "column_name":"referral_type_id"}'')
2896
2897 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));
2898
2899--update "typing_case" t set errors = concat(errors, ''{"level":"rfl", "message":"не заполнено referral_date", "column_name":"referral_date"}'')
2900
2901 --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;
2902
2903
2904
2905
2906
2907-- 8. Д-учет----------------------------------------------------------------------------------------------
2908
2909-- 8.1 Обновляю Д-учет
2910
2911update pci_dispensary disp set
2912
2913 med_case_in_id = case when d.disp_in_out = ''in'' then case_id end,
2914
2915 med_case_out_id = case when d.disp_in_out = ''out'' then case_id end,
2916
2917 reg_in_dt = d.reg_in_dt,
2918
2919 reg_out_dt = d.reg_out_dt,
2920
2921 reg_in_doctor_id = d.reg_in_doctor_id,
2922
2923 reg_out_doctor_id = d.reg_out_doctor_id,
2924
2925 patient_id = d.patient_id,
2926
2927 reg_out_reason_id = d.reg_out_reason_id,
2928
2929 reg_stage_id = d.reg_stage_id,
2930
2931 clinic_id = d.clinic_id,
2932
2933 diagnosis_id = d.mkb_id,
2934
2935 dispensary_group_id = d.dispensary_group_id,
2936
2937 nosol_registr_id = d.nosol_registr_id
2938
2939from "typing_diagnos" d
2940
2941where d.disp_id = disp.id and d.reg_in_dt is not null and
2942
2943 exists(select 1 from md_diagnosis x where d.mkb_id = x.id) and
2944
2945 exists(select 1 from md_employee_position x where d.reg_in_doctor_id = x.id) and
2946
2947 exists(select 1 from md_nosol_registr x where d.nosol_registr_id = x.id) and
2948
2949 exists(select 1 from md_reg_stage x where d.reg_stage_id = x.id) and
2950
2951 exists(select 1 from mc_case x where d.case_id = x.id) and
2952
2953 exists(select 1 from pci_patient x where d.patient_id = x.id) and
2954
2955 exists(select 1 from md_clinic x where d.clinic_id = x.id) and
2956
2957 exists(select 1 from md_reg_stage x where d.reg_stage_id = x.id) and
2958
2959 (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
2960
2961 (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
2962
2963 (d.dispensary_group_id is null or exists(select 1 from md_dispensary_group x where x.id = d.dispensary_group_id)) and
2964
2965 d.disp_in_out in (''in'', ''out'')
2966
2967;
2968
2969
2970
2971-- 8.2 Добавляю ошибку (не найден id д-учета)
2972
2973update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден disp_id = '', disp_id, ''", "column_name":"disp_id"}'')
2974
2975 where t.disp_id is not null and not exists (select 1 from pci_dispensary x where x.id = t.disp_id);
2976
2977
2978
2979
2980
2981-- 8.3 Добавляю Д-учет
2982
2983alter table "typing_diagnos" add column new_disp_id integer;
2984
2985update "typing_diagnos" d set new_disp_id = nextval(''pci_dispensary_seq'')
2986
2987where d.disp_id is null and d.reg_in_dt is not null and
2988
2989 exists(select 1 from md_diagnosis x where d.mkb_id = x.id) and
2990
2991 exists(select 1 from md_employee_position x where d.reg_in_doctor_id = x.id) and
2992
2993 exists(select 1 from md_nosol_registr x where d.nosol_registr_id = x.id) and
2994
2995 exists(select 1 from md_reg_stage x where d.reg_stage_id = x.id) and
2996
2997 exists(select 1 from mc_case x where d.case_id = x.id) and
2998
2999 exists(select 1 from pci_patient x where d.patient_id = x.id) and
3000
3001 exists(select 1 from md_clinic x where d.clinic_id = x.id) and
3002
3003 exists(select 1 from md_reg_stage x where d.reg_stage_id = x.id) and
3004
3005 (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
3006
3007 (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
3008
3009 (d.dispensary_group_id is null or exists(select 1 from md_dispensary_group x where x.id = d.dispensary_group_id)) and
3010
3011 d.disp_in_out in (''in'', ''out'')
3012
3013;
3014
3015
3016
3017insert 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,
3018
3019 dispensary_group_id, nosol_registr_id)
3020
3021select
3022
3023 new_disp_id,
3024
3025 case when disp_in_out = ''in'' then case_id end,
3026
3027 case when disp_in_out = ''out'' then case_id end,
3028
3029 reg_in_dt,
3030
3031 reg_out_dt,
3032
3033 reg_in_doctor_id,
3034
3035 reg_out_doctor_id,
3036
3037 patient_id,
3038
3039 reg_out_reason_id,
3040
3041 reg_stage_id,
3042
3043 clinic_id,
3044
3045 mkb_id,
3046
3047 dispensary_group_id,
3048
3049 nosol_registr_id
3050
3051from "typing_diagnos"
3052
3053where new_disp_id is not null
3054
3055;
3056
3057
3058
3059update "typing_diagnos" set disp_id = new_disp_id where new_disp_id is not null;
3060
3061
3062
3063-- 8.4 добавляю все ошибки д-учета
3064
3065update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не заполнен reg_in_dt", "column_name":"reg_in_dt"}'')
3066
3067 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;
3068
3069update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден reg_in_doctor_id = '', reg_in_doctor_id, ''", "column_name":"reg_in_doctor_id"}'')
3070
3071 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;
3072
3073update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден nosol_registr_id = '', nosol_registr_id, ''", "column_name":"nosol_registr_id"}'')
3074
3075 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;
3076
3077update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден reg_out_doctor_id = '', reg_out_doctor_id, ''", "column_name":"reg_out_doctor_id"}'')
3078
3079 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);
3080
3081update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден reg_out_reason_id = '', reg_out_reason_id, ''", "column_name":"reg_out_reason_id"}'')
3082
3083 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);
3084
3085update "typing_diagnos" t set errors = concat(errors, ''{"level":"disp", "message":"не найден dispensary_group_id = '', dispensary_group_id, ''", "column_name":"dispensary_group_id"}'')
3086
3087 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);
3088
3089
3090
3091
3092
3093-- 9. Устанавливаю основной диагноз
3094
3095
3096
3097with t as (
3098
3099 select md.case_id, md.id from "typing_diagnos" x
3100
3101 join mc_diagnosis md on md.id = x.id
3102
3103 join mc_stage s on s.id = md.stage_id and md.is_main
3104
3105
3106
3107 order by stage_order desc
3108
3109limit 1
3110
3111)
3112
3113update mc_case c 
3114
3115 set main_diagnos_id = t.id
3116
3117from t where t.case_id = c.id;
3118
3119
3120
3121create temp table "typing_tmp"  ON COMMIT DROP AS  
3122
3123with t as (
3124
3125 select md.* from "typing_diagnos" x
3126
3127 join mc_diagnosis md on md.id = x.id
3128
3129 join mc_stage s on s.id = md.stage_id and md.is_main =true
3130
3131
3132
3133 order by stage_order desc
3134
3135
3136
3137limit 1
3138
3139)
3140
3141select nextval(''mc_diagnosis_seq'') new_id, * from t where stage_id < 4;
3142
3143
3144
3145insert into mc_diagnosis(id, diagnos_id, disease_type_id, case_id, patient_id, stage_id, step_id, type_id, establishment_date, is_main)
3146
3147 select nextval(''mc_diagnosis_seq''), s.mkb_id, s.diag_disease_type_id, s.case_id, s.patient_id, s.diag_stage_id, v.new_id, s.diag_type_id, s.bdate,false from "typing_services" s JOIN "typing_visits" v ON s.step_num=v.step_num  where s.new_id is  not  null and s.mkb_id not in(SELECT d.mkb_id FROM   "typing_diagnos" d  JOIN "typing_visits" v ON d.step_num =v.step_num) and (SELECT count(new_id) FROM "typing_tmp") = 0
3148
3149and exists (SELECT 1 FROM mc_step x WHERE x.id = v.new_id)
3150
3151and s.mkb_id is not null
3152
3153 ;
3154
3155
3156
3157
3158
3159insert 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)
3160
3161select new_id, establishment_date, note, diagnos_id, disease_type_id, doctor_id, injury_type_id, case_id, patient_id, 4, null, type_id, 
3162
3163is_main, is_suspicion from "typing_tmp" t
3164
3165WHERE exists(SELECT 1 FROM mc_step x WHERE x.id = t.step_id)
3166
3167and diagnos_id is not null
3168
3169;
3170
3171
3172
3173
3174
3175
3176
3177
3178
3179
3180
3181update mc_case c set main_diagnos_id = coalesce(t.new_id, t.id)
3182
3183from "typing_tmp" t where t.case_id = c.id
3184
3185and exists (select 1 from mc_diagnosis where id = coalesce(t.new_id, t.id))
3186
3187;
3188
3189
3190
3191
3192
3193
3194
3195-- 10. Cобираю JSON
3196
3197update "typing_case" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
3198
3199update "typing_visits" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
3200
3201update "typing_services" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
3202
3203update "typing_diagnos" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
3204
3205update "typing_resources" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
3206
3207update "typing_cur" set errors = concat(''"errors":['', replace(errors, ''}{'', ''},{''), '']'');
3208
3209
3210
3211-- 11. Закрытие случая, если есть шаг с результатом, устанавливаю state_id = 1
3212
3213
3214
3215with t as (
3216
3217 select case_id from "typing_visits" where result_id is not null limit 1
3218
3219)
3220
3221update mc_case c set state_id = 1 from t where t.case_id = c.id;
3222
3223
3224
3225-- 12. Проставляю каждому шагу основной диагноз
3226
3227with t as (
3228
3229 select step_id, id from "typing_diagnos"
3230
3231where is_main =true
3232
3233and coalesce(type_id,1) =1
3234
3235)
3236
3237update mc_step st set main_diagnosis_id = t.id from t where t.step_id = st.id;
3238
3239
3240
3241' using $1;
3242
3243
3244
3245
3246
3247
3248
3249perform typing.typing_regional_changes((select new_id from "typing_case" ));
3250
3251
3252
3253drop table if exists "typing_validate";
3254
3255-- Запуск валидаций, копирую таблицу с валидациями
3256
3257execute'
3258
3259 create TEMP table "typing_validate" ON COMMIT DROP AS  select *
3260
3261 from typing.typing_validate( (select new_id from "typing_case"), $1 ) as tabl1(rb boolean, vld text, msg text, clmn_name text )
3262
3263' using $2;
3264
3265
3266
3267update md_appointment a
3268
3269set state_id = 3,
3270
3271srv_rendered_id = COALESCE(s.id,s.new_id)
3272
3273from typing_case c join typing_services s on true
3274
3275
3276
3277 where c.talon = a.id
3278
3279and c.talon is not null;
3280
3281
3282
3283
3284
3285
3286
3287for rec in execute('
3288
3289with t as
3290
3291(
3292
3293select 
3294
3295 concat(''{"id":"'',coalesce(new_id::text, _id),
3296
3297  ''","patient_id":"'',_patient_id,''","clinic_id":"'',_clinic_id,''","uid":"'',uid,''","care_regimen_id":"'',_care_regimen_id,''","funding_id":"'',_funding_id,
3298
3299  ''","init_goal_id":"'',_init_goal_id,''","care_level_id":"'',_care_level_id,''","payment_method_id":"'',_payment_method_id,''","admission_reason_id":"'',_admission_reason_id,
3300
3301  ''","repeat_count_id":"'',_repeat_count_id,''","referral_id":"'',coalesce(referral_id::text, new_ref_id::text,_referral_id),
3302
3303  ''","ref_organization_id":"'',_ref_organization_id,''","ref_doctor_id":"'',_ref_doctor_id,
3304
3305  ''","referral_date":"'',_referral_date,''","recv_organization_id":"'',_recv_organization_id,''","ref_mkb_id":"'',_ref_mkb_id,''","order_number":"'',order_number,
3306
3307  ''","referral_type_id":"'',_referral_type_id,''","sicklist_id":"'',coalesce(new_sl_id::text, sicklist_id::text, _sicklist_id),
3308
3309  ''","sl_code":"'',sl_code,''","disability_reason_id":"'',_disability_reason_id,
3310
3311  ''","disability_from_dt":"'',_disability_from_dt,''","disability_to_dt":"'',_disability_to_dt,''",'',errors,
3312
3313  
3314
3315  '',"visits":['',
3316
3317    (
3318
3319    select
3320
3321     array_to_string(array_agg(
3322
3323     concat(''{"id":"'',coalesce(id::text, new_id::text, _id),
3324
3325     ''","step_num":"'',step_num,''","type_id":"'',_type_id,''","admission_date":"'',_admission_date,''","place_id":"'',_place_id,''","initiator_id":"'',_initiator_id,
3326
3327     ''","goal_id":"'',_goal_id,''","profile_id":"'',_profile_id,''","standard_id":"'',_standard_id,''","mes_id":"'',_mes_id,''","res_group_id":"'', (select res_group_id from mc_step st where st.id = coalesce(t.id, t.new_id)), ''","result_id":"'',_result_id, ''","stepResultReason_id":"'',_stepResultReason_id, ''","doctor_id":"'',_doctor_id, ''","vmp_method_id":"'',_vmp_method_id,''","vmp_type_id":"'',_vmp_type_id,
3328
3329     ''","outcome_id":"'',_outcome_id,''","is_viewed":"'',_is_viewed,''","is_needed":"'',_is_needed,''","is_sanitized":"'',_is_sanitized,''",'',errors,''}'')
3330
3331     ), '','')
3332
3333    from "typing_visits" t
3334
3335    ), '']'',
3336
3337
3338
3339  '',"services":['',
3340
3341    (
3342
3343    select
3344
3345     array_to_string(array_agg(
3346
3347     concat(''{"id":"'',coalesce(id::text, new_id::text, _id),
3348
3349     ''","step_num":"'',step_num,''","step_id":"'',_step_id,''","service_id":"'',_service_id,''","bdate":"'',_bdate,''","funding_id":"'',_funding_id,''","doctor_id":"'',_doctor_id,
3350
3351     ''","quantity":"'',_quantity,''","res_group_id":"'',(select res_group_id from sr_srv_rendered r where r.id = coalesce(t.id, t.new_id)),''","mkb_id":"'',_mkb_id,''",'',errors,''}'')
3352
3353     ), '','')
3354
3355    from "typing_services" t
3356
3357    ), '']'',
3358
3359
3360
3361
3362
3363  '',"diagnosis":['',
3364
3365    (
3366
3367    select
3368
3369       array_to_string(array_agg(
3370
3371       concat(''{"id":"'',coalesce(id::text, new_id::text, _id),
3372
3373       ''","step_num":"'',step_num,''","mkb_id":"'',_mkb_id,''","disease_type_id":"'',_disease_type_id,''","type_id":"'',_type_id,''","is_main":"'',_is_main,
3374
3375       ''","stage_id":"'',_stage_id,''","step_id":"'',_step_id,
3376
3377       ''","disp_id":"'',coalesce(disp_id::text, new_disp_id::text, _disp_id),''","disp_in_out":"'',disp_in_out,''","nosol_registr_id":"'',_nosol_registr_id,
3378
3379       ''","dispensary_group_id":"'',_dispensary_group_id,''","reg_in_dt":"'',_reg_out_dt,''","reg_in_doctor_id":"'',_reg_in_doctor_id,''","reg_out_doctor_id":"'',_reg_out_doctor_id,
3380
3381       ''","reg_stage_id":"'',_reg_stage_id,''","reg_out_reason_id":"'',_reg_out_reason_id,''",'',errors,''}'')
3382
3383       ), '','')
3384
3385    from "typing_diagnos"
3386
3387    ), '']'',
3388
3389
3390
3391  '',"validate":['',
3392
3393    (
3394
3395    select
3396
3397       array_to_string(array_agg(
3398
3399       concat(''{"rb":"'', rb::text , ''" ,"vld":"'', vld, ''","msg":"'', msg, ''","clmn_name":"'', clmn_name, ''" }'')
3400
3401       ), '','')
3402
3403    from "typing_validate"
3404
3405    ), '']'',
3406
3407
3408
3409  '',"resources":['',
3410
3411    (
3412
3413    select
3414
3415       array_to_string(array_agg(
3416
3417       concat(''{"role_id":"'', role_id::text, ''" ,"step_num":"'', step_num::text, ''","resource_id":"'', resource_id::text, ''","group_id":"'', group_id::text,''","org_id":"'', org_id::text,''", '' ,errors,''}'') 
3418
3419       ), '','')
3420
3421    from "typing_resources"
3422
3423    ), '']'',
3424
3425
3426
3427'',"cur":['',
3428
3429    (
3430
3431    select
3432
3433       array_to_string(array_agg(
3434
3435       concat(''{"id":"'', coalesce(id::text, new_id::text, _id), ''" ,"criteria_id":"'', criteria_id ::text, ''","value":"'', value::text,''", '' ,errors,''}'')
3436
3437       ), '','')
3438
3439    from "typing_cur"
3440
3441    ), '']'',
3442
3443  ''}'') x
3444
3445from "typing_case"
3446
3447)
3448
3449
3450
3451select x::json x from t
3452
3453
3454
3455
3456
3457')
3458
3459 loop
3460
3461  ret = rec.x;
3462
3463 end loop;
3464
3465
3466
3467-- Просталяю соц. положение в случай
3468
3469 execute('
3470
3471  update mc_case c set soc_group_id = p.social_group_id
3472
3473  from "typing_case" cc, pci_patient p where cc.id = c.id and c.patient_id = p.id
3474
3475 ');
3476
3477
3478
3479
3480
3481
3482
3483for rec in select * from typing_validate loop
3484
3485if rec.rb then raise exception ''; end if;
3486
3487end loop;
3488
3489
3490
3491
3492
3493
3494
3495
3496
3497if exists (SELECT 1 from typing_services WHERE errors <> '"errors":[]' ) or
3498
3499exists (SELECT 1 from typing_case WHERE errors <> '"errors":[]' ) or
3500
3501exists (SELECT 1 from typing_diagnos WHERE errors <> '"errors":[]' ) or
3502
3503exists (SELECT 1 from typing_resources WHERE errors <> '"errors":[]' ) or
3504
3505exists (SELECT 1 from typing_visits WHERE errors <> '"errors":[]' ) or
3506
3507exists (SELECT 1 from typing_cur WHERE errors <> '"errors":[]' ) 
3508
3509then raise exception ''; end if;
3510
3511
3512
3513/*Добавляю то что получилось в аудит
3514
3515*/
3516
3517
3518
3519--mc_case_aud
3520
3521insert into mc_case_aud(id,rev,revtype,create_date, case_type_id, clinic_id, patient_id, care_regimen_id, funding_id, init_goal_id, care_level_id, payment_method_id, admission_reason_id, repeat_count_id, referral_id, uid, care_providing_form_id ,
3522
3523admission_state_id,
3524
3525drunkenness_type_id ,
3526
3527time_gone_id,
3528
3529provision_condition_id,note)
3530
3531select id,currval('sec_audit_entry_seq'), case when exists (select 1 from typing_case where id is null) then 1 else 0 end,
3532
3533create_date, case_type_id, clinic_id, patient_id, care_regimen_id, funding_id, init_goal_id, care_level_id, payment_method_id, admission_reason_id, repeat_count_id, referral_id, uid, care_providing_form_id ,
3534
3535admission_state_id,
3536
3537drunkenness_type_id,
3538
3539time_gone_id,
3540
3541provision_condition_id,note
3542
3543from mc_case where id = (select coalesce(new_id, id) from typing_case limit 1) ;
3544
3545--mc_step
3546
3547insert into mc_step_aud (id,rev,revtype,admission_date,admission_time, outcome_date, case_id, outcome_id, result_id, reason_id, profile_id, mes_id,csg_id, res_group_id,regimen_id,outcome_regimen_id,deviation_reason_id, vmp_method_id, vmp_type_id)
3548
3549select s.id, currval('sec_audit_entry_seq'),case when v.new_id is null then 0 else 1 end, s.admission_date,s.admission_time, s.outcome_date, s.case_id, s.outcome_id, s.result_id, s.reason_id, s.profile_id, s.mes_id,s.csg_id, s.res_group_id,s.regimen_id,s.outcome_regimen_id,s.deviation_reason_id, s.vmp_method_id, s.vmp_type_id
3550
3551from mc_step s join typing_visits v on  coalesce(v.new_id, v.id) =s.id;
3552
3553
3554
3555--hsp_record
3556
3557
3558
3559insert into hsp_record_aud(id,	 rev,														mes_id,    funding_id,    department_id, bed_profile_id,    issue_planned_date,   complexity_level_id)
3560
3561										select v.id, currval('sec_audit_entry_seq'),h.mes_id, h.funding_id, h.department_id, h.bed_profile_id, h.issue_planned_date, h.complexity_level_id 
3562
3563from hsp_record h join typing_visits v on h.id = coalesce(v.new_id, v.id);
3564
3565
3566
3567-- plc_visit
3568
3569
3570
3571insert into plc_visit_aud(id, goal_id, initiator_id, place_id, type_id, is_viewed, is_needed, is_sanitized,rev)
3572
3573select p.id, p.goal_id, p.initiator_id, p.place_id, p.type_id, p.is_viewed, p.is_needed, p.is_sanitized, currval('sec_audit_entry_seq')
3574
3575from plc_visit p  join typing_visits v on p.id = coalesce(v.new_id, v.id);
3576
3577  
3578
3579  
3580
3581--mc_diagnosis
3582
3583insert into mc_diagnosis_aud(id, diagnos_id, disease_type_id, case_id, patient_id, stage_id, step_id, type_id, is_main,establishment_date, note,rev,revtype)
3584
3585select d.id, d.diagnos_id, d.disease_type_id, d.case_id, d.patient_id, d.stage_id, d.step_id, d.type_id, d.is_main,d.establishment_date, d.note, currval('sec_audit_entry_seq'), case when t.new_id is null then 0 else 1 end
3586
3587from mc_diagnosis d
3588
3589 left join typing_diagnos t on d.id = coalesce(t.new_id, t.id)
3590
3591where d.case_id = (select coalesce(id,new_id) from typing_case)
3592
3593;
3594
3595--mc_step_cur
3596
3597
3598
3599insert into mc_step_cur_coef_criteria_aud(id, criteria_id,step_id,value,rev,revtype)
3600
3601select c.id, c.criteria_id,c.step_id,c.value,currval('sec_audit_entry_seq'), case when t.new_id is null then 0 else 1 end
3602
3603from  mc_step_cur_coef_criteria c join typing_cur t on  c.id = coalesce(t.new_id, t.id);
3604
3605
3606
3607
3608
3609--sr_srv_rendered
3610
3611insert into sr_srv_rendered_aud(id, bdate, service_id, customer_id, quantity, org_id, is_rendered, res_group_id, cul, funding_id,comment, is_refused, tooth_number,rev,revtype)
3612
3613select s.id, s.bdate, s.service_id, s.customer_id, s.quantity, s.org_id, s.is_rendered, s.res_group_id, s.cul, s.funding_id,s.comment, s.is_refused, s.tooth_number,currval('sec_audit_entry_seq'), case when t.new_id is null then 0 else 1 end
3614
3615from sr_srv_rendered s  join typing_services t on  s.id = coalesce(t.new_id, t.id);
3616
3617
3618
3619--md_srv_rendered
3620
3621insert into md_srv_rendered_aud(id, step_id, case_id, diagnosis_id,rev)
3622
3623  select m.id, m.step_id, m.case_id, m.diagnosis_id,currval('sec_audit_entry_seq')
3624
3625  from md_srv_rendered m join typing_services t on  m.id = coalesce(t.new_id, t.id);
3626
3627
3628
3629
3630
3631--sr_srv_result 
3632
3633insert into sr_srv_result_aud(id,value,service_id,result_type_id,rev,revtype)
3634
3635  select r.id,r.value,r.service_id,r.result_type_id,currval('sec_audit_entry_seq'), case when t.new_id is null then 0 else 1 end
3636
3637from sr_srv_result r join typing_services t on r.service_id = coalesce(t.new_id, t.id); 
3638
3639
3640
3641exception when RAISE_EXCEPTION then raise notice '123';
3642
3643 end; -- trans
3644
3645
3646
3647
3648
3649 return ret;
3650
3651end;