GqWz9zaF

1ALTER TABLE public.pim_department ADD COLUMN at_home boolean;
2
3ALTER TABLE public.pim_department ADD COLUMN clinical_trials_per_shift integer;
4ALTER TABLE public.pim_department ADD COLUMN brigades_amount integer;
5
6
7ALTER TABLE public.pim_department ADD COLUMN clinical_trials_per_shift integer;
8
9            ALTER TABLE public.pim_department ADD COLUMN visits_per_shift integer;
10alter table pim_department add column is_social_significant integer;
11
12
13CREATE SEQUENCE "public"."sr_res_grp_srv_template_seq"
14 INCREMENT 1
15 MINVALUE 1
16 MAXVALUE 9223372036854775807
17 START 1
18 CACHE 1;
19
20ALTER TABLE "public"."sr_res_grp_srv_template_seq" OWNER TO "app_group_master";
21
22
23
24CREATE TABLE "public"."sr_res_grp_srv_template" (
25"id" int4 NOT NULL,
26"clinic_id" int4,
27"name" varchar COLLATE "default" NOT NULL,
28"code" varchar COLLATE "default",
29"active" bool,
30"aud_who" varchar COLLATE "default",
31"aud_when" timestamp(6),
32"aud_source" varchar COLLATE "default",
33"aud_who_create" varchar COLLATE "default",
34"aud_when_create" timestamp(6),
35"aud_source_create" varchar COLLATE "default",
36"position_role_id" int4,
37CONSTRAINT "sr_res_grp_srv_template_pk" PRIMARY KEY ("id"),
38CONSTRAINT "sr_res_grp_srv_template_position_role_id_fk" FOREIGN KEY ("position_role_id") REFERENCES "public"."pim_position_role" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
39CONSTRAINT "sr_res_grp_srv_template_clinic_fk" FOREIGN KEY ("clinic_id") REFERENCES "public"."md_clinic" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION,
40CONSTRAINT "sr_res_grp_srv_template_name_uk" UNIQUE ("name")
41)
42WITH (OIDS=FALSE)
43;
44
45ALTER TABLE "public"."sr_res_grp_srv_template" OWNER TO "app_group_master";
46
47COMMENT ON COLUMN "public"."sr_res_grp_srv_template"."position_role_id" IS 'Должность';
48
49
50
51CREATE TRIGGER "audit_trigger" BEFORE INSERT OR UPDATE ON "public"."sr_res_grp_srv_template"
52FOR EACH ROW
53EXECUTE PROCEDURE "audit_trigger_fun"();
54
55
56
57
58
59
60CREATE SEQUENCE "public"."sr_res_grp_srv_tmpl_srv_seq"
61 INCREMENT 1
62 MINVALUE 1
63 MAXVALUE 9223372036854775807
64 START 1
65 CACHE 1;
66
67ALTER TABLE "public"."sr_res_grp_srv_tmpl_srv_seq" OWNER TO "app_group_master";
68
69
70
71
72
73CREATE TABLE "public"."sr_res_grp_srv_tmpl_srv" (
74"id" int4 NOT NULL,
75"template_id" int4,
76"srv_type_id" int4,
77"start_date" date,
78"end_date" date,
79"default_service" bool,
80"referral_required" bool,
81"moderation_required" bool,
82"patient_multiple_appointment_per_day" bool,
83"power" int4,
84"aud_who" varchar COLLATE "default",
85"aud_when" timestamp(6),
86"aud_source" varchar COLLATE "default",
87"aud_who_create" varchar COLLATE "default",
88"aud_when_create" timestamp(6),
89"aud_source_create" varchar COLLATE "default",
90CONSTRAINT "sr_res_grp_srv_tmpl_srv_pk" PRIMARY KEY ("id"),
91CONSTRAINT "sr_res_grp_srv_template_fk" FOREIGN KEY ("template_id") REFERENCES "public"."sr_res_grp_srv_template" ("id") ON DELETE CASCADE ON UPDATE NO ACTION,
92CONSTRAINT "sr_res_grp_srv_template_srv_fk" FOREIGN KEY ("srv_type_id") REFERENCES "public"."sr_service" ("id") ON DELETE CASCADE ON UPDATE NO ACTION
93)
94WITH (OIDS=FALSE)
95;
96
97ALTER TABLE "public"."sr_res_grp_srv_tmpl_srv" OWNER TO "app_group_master";
98
99
100
101CREATE TRIGGER "audit_trigger" BEFORE INSERT OR UPDATE ON "public"."sr_res_grp_srv_tmpl_srv"
102FOR EACH ROW
103EXECUTE PROCEDURE "audit_trigger_fun"();
104
105
106
107select * from mc_stage
108
109
110
111CREATE TABLE "public"."sr_resgroup_srv_tmpl_fund" (
112"resgroup_service_id" int4 NOT NULL,
113"funding_type_id" int4 NOT NULL,
114"aud_who" varchar COLLATE "default",
115"aud_when" timestamp(6),
116"aud_source" varchar COLLATE "default",
117"aud_who_create" varchar COLLATE "default",
118"aud_when_create" timestamp(6),
119"aud_source_create" varchar COLLATE "default",
120CONSTRAINT "sr_resgroup_srv_tmpl_fund_pk" PRIMARY KEY ("resgroup_service_id", "funding_type_id"),
121CONSTRAINT "sr_resgroup_srv_tmpl_fund_fk" FOREIGN KEY ("funding_type_id") REFERENCES "public"."fin_funding_source_type" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION,
122CONSTRAINT "sr_resgroup_srv_tmpl_fund_rgs_fk" FOREIGN KEY ("resgroup_service_id") REFERENCES "public"."sr_res_grp_srv_tmpl_srv" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION
123)
124WITH (OIDS=FALSE)
125;
126
127ALTER TABLE "public"."sr_resgroup_srv_tmpl_fund" OWNER TO "app_group_master";
128
129
130
131CREATE TRIGGER "audit_trigger" BEFORE INSERT OR UPDATE ON "public"."sr_resgroup_srv_tmpl_fund"
132FOR EACH ROW
133EXECUTE PROCEDURE "audit_trigger_fun"();
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152CREATE OR REPLACE FUNCTION "public"."tr_fnc_set_fianl_diagnosis"()
153  RETURNS "pg_catalog"."trigger" AS $BODY$
154DECLARE
155    _md_diagnosis_id_new INTEGER;
156    _md_diagnosis_id_old INTEGER;
157    _disease_type_id INTEGER;
158    _patient_id INTEGER;
159    _establishment_date DATE; 
160    _id INTEGER;
161		_app_code CHARACTER VARYING;
162BEGIN
163/*
164    триггерная функция вызывается после insert/update mc_step
165    устанавливает заключительный диагноз случая равный диагнозу последнего шага
166     
167
168*/
169
170    IF (_app_code <> 'visits-ws') THEN
171        RETURN NEW ;
172    END
173    IF ;
174
175
176
177IF EXISTS (SELECT 1 FROM mc_case c WHERE c.id = NEW.case_id AND c.case_type_id <> 2) THEN RETURN NEW; END IF; --кроме неотложки
178IF (NEW.result_id IS NULL OR NEW.main_diagnosis_id IS NULL) THEN RETURN NEW; END IF; --если нет результата или диагноза, то выходим
179IF NOT EXISTS (SELECT 1 FROM mc_step_result r WHERE r.id = NEW.result_id AND r.is_closed) THEN RETURN NEW; END IF; --если результат не закрывает случай, то выходим
180--insert into tmp_reanim (kod,kuracia,srok) values (_md_diagnosis_id_new,_md_diagnosis_id_old,0);  
181SELECT d.diagnos_id,d.disease_type_id,d.patient_id,d.establishment_date
182       INTO _md_diagnosis_id_new, _disease_type_id, _patient_id, _establishment_date
183FROM mc_diagnosis d
184WHERE d.step_id = NEW.id AND d.stage_id = 3 AND COALESCE(d.type_id,1) = 1 AND d.is_main LIMIT 1;
185IF (_md_diagnosis_id_new IS NULL) THEN RETURN NEW; END IF;
186SELECT c.id, c.diagnos_id INTO _id, _md_diagnosis_id_old FROM mc_diagnosis c WHERE c.case_id = NEW.case_id AND c.stage_id = 4 LIMIT 1;
187--insert into tmp_reanim (kod,kuracia,srok) values (_md_diagnosis_id_new,_md_diagnosis_id_old,0);  
188IF (_md_diagnosis_id_old IS NULL) THEN -- если заключительного диагноза нет, то добавляем
189      
190    --insert into tmp_reanim (kod,kuracia,srok) values (_md_diagnosis_id_new,_md_diagnosis_id_old,0);  
191    INSERT INTO mc_diagnosis (id,establishment_date,diagnos_id,disease_type_id,case_id,patient_id,stage_id,is_main)
192    VALUES (nextval('mc_diagnosis_seq'),_establishment_date,_md_diagnosis_id_new,_disease_type_id,NEW.case_id,_patient_id,4,true);
193      
194ELSEIF (_md_diagnosis_id_new <> _md_diagnosis_id_old) THEN --иначе обновляем
195           
196      
197    UPDATE mc_diagnosis
198    SET
199        diagnos_id = _md_diagnosis_id_new
200    WHERE
201        id = _id;
202END IF;
203--копирование сопутсвующих диагнозов и осложнений
204INSERT INTO mc_diagnosis (
205    id,
206    establishment_date,
207    note,
208    diagnos_id,
209    disease_type_id,
210    doctor_id,
211    injury_type_id,
212    case_id,
213    patient_id,
214    stage_id,
215    step_id,
216    type_id,
217    is_main,
218    is_suspicion
219)
220SELECT
221    nextval('mc_diagnosis_seq'),
222    d.establishment_date,
223    d.note,
224    d.diagnos_id,
225    d.disease_type_id,
226    d.doctor_id,
227    d.injury_type_id,
228    d.case_id,
229    d.patient_id,
230    4 AS stage_id,
231    NULL AS step_id,
232    d.type_id,
233    FALSE AS is_main,
234    d.is_suspicion  
235FROM
236    mc_diagnosis d
237WHERE
238    d.step_id = NEW.id AND COALESCE(d.type_id,1) <> 1
239    AND d.stage_id = 3
240    AND NOT EXISTS (
241        SELECT 1 FROM mc_diagnosis dd
242        WHERE dd.case_id = d.case_id
243              AND dd.step_id IS NULL
244              AND dd.stage_id = 4
245              AND dd.type_id = d.type_id
246              AND dd.diagnos_id = d.diagnos_id
247              AND NOT COALESCE(dd.is_main,FALSE)
248      )
249;    
250  
251RETURN NEW;
252END;
253$BODY$
254  LANGUAGE 'plpgsql' VOLATILE COST 100
255;
256ALTER FUNCTION "public"."tr_fnc_set_fianl_diagnosis"() OWNER TO "app_group_master";
257
258
259CREATE TRIGGER "mc_step_tr_set_final_diagnosis" AFTER INSERT OR UPDATE ON "public"."mc_step"
260FOR EACH ROW
261EXECUTE PROCEDURE "tr_fnc_set_fianl_diagnosis"();
262
263
264select * from mc_diagnosis where case_id = 22199000;