PnjHVgBF

1ALTER TABLE mc_case ADD COLUMN is_control_case_init_goal BOOLEAN;
2ALTER TABLE mc_case_aud ADD COLUMN is_control_case_init_goal BOOLEAN;
3
4ALTER TABLE mc_case ADD COLUMN is_finance_type_control  BOOLEAN;
5ALTER TABLE mc_case_aud ADD COLUMN is_finance_type_control  BOOLEAN;
6ALTER TABLE public.md_clinic ADD COLUMN latitude character varying(255);
7ALTER TABLE public.mc_case ADD COLUMN planned_hospitalization_date date;
8ALTER TABLE public.mc_case_aud ADD COLUMN planned_hospitalization_date date;
9ALTER TABLE public.mc_case ADD COLUMN ticket_VMP varchar(255);
10ALTER TABLE public.mc_case_aud ADD COLUMN ticket_VMP varchar(255);
11ALTER TABLE public.mc_case ADD COLUMN ticket_VMP_date date;
12ALTER TABLE public.mc_case_aud ADD COLUMN ticket_VMP_date varchar(255);
13ALTER TABLE public.pim_organization ADD COLUMN latitude double precision;
14ALTER TABLE public.pim_organization ADD COLUMN longitude double precision;
15ALTER TABLE md_clinic ADD COLUMN is_offline BOOLEAN DEFAULT false;
16ALTER TABLE public.md_clinic ADD COLUMN  territory_id integer;
17
18
19
20alter table mc_case add column is_social_significant integer;
21alter table pci_patient add column employee_reg_death_position_id integer;
22alter table pim_individual add column list_main_contact varchar(255);
23alter table pim_employee_position add column leaving_reason_id integer;
24alter table pim_employee_position  add column target_training boolean;
25alter table md_referral add column is_operation_required boolean;
26alter table md_referral add column receiving_speciality_id integer;
27ALTER TABLE public.md_referral ADD COLUMN ref_doctor_code varchar(255);
28ALTER TABLE public.md_referral ADD COLUMN ref_organization_code varchar(255);
29ALTER TABLE public.md_referral_aud ADD COLUMN ref_organization_code varchar(255);
30ALTER TABLE public.md_referral_aud ADD COLUMN ref_organization_code varchar(255);
31ALTER TABLE public.mc_diagnosis ADD COLUMN injury_reason_id INTEGER;
32ALTER TABLE public.mc_diagnosis_aud ADD COLUMN injury_reason_id INTEGER;
33
34
35
36
37ALTER TABLE public.mc_step ADD COLUMN planned_date_closing_case DATE;
38ALTER TABLE public.mc_step_aud ADD COLUMN planned_date_closing_case DATE;
39ALTER TABLE public.sr_srv_rendered ADD COLUMN   "close_date" date;
40ALTER TABLE public.sr_srv_rendered_aud ADD COLUMN   "close_date" date;
41ALTER TABLE public.sr_service ADD COLUMN   "is_vmp" bool;
42ALTER TABLE public.sr_service ADD COLUMN  "is_need_close_date" bool;
43
44
45
46ALTER TABLE public.hsp_record add column  action_allowed_result_id integer;
47ALTER TABLE public.hsp_record_aud add column  action_allowed_result_id integer;
48ALTER TABLE public.sr_res_group add column  service_template_id integer;
49ALTER TABLE public.sr_res_group_aud add column  service_template_id integer;
50
51
52
53
54
55
56DROP TRIGGER IF EXISTS "trigger_update_plc_visit_or_hsp_record" on "public"."mc_step";
57  DROP FUNCTION IF EXISTS "public"."update_plc_visit_or_hsp_record"();
58  CREATE OR REPLACE FUNCTION "public"."update_plc_visit_or_hsp_record"()
59    RETURNS "pg_catalog"."trigger" AS $BODY$
60                  DECLARE
61													_app_code CHARACTER VARYING;
62                          _case_id integer;
63                          _case_mode_id integer;
64                          _step_id integer;
65                          -- visit
66                          _plc_goal_id integer;
67                          _plc_initiator_id integer;
68                          _plc_place_id integer;
69                          _plc_type_id integer;
70                          _plc_is_viewed boolean;
71                          _plc_is_needed boolean;
72                          _plc_is_sanitized boolean;
73                          _plc_appointment_id integer;
74                          _plc_planned_date date;
75                          _plc_dental_formula_id integer;
76                          -- hsp
77                          _hsp_department_id integer;
78                          _hsp_funding_id integer;
79                          _hsp_mes_id integer;
80                          _hsp_previous_id integer;
81                          _hsp_is_set_diagnosis boolean;
82                          _hsp_is_diag_not_eq boolean;
83                          _hsp_days_comp_algo_id integer;
84                          _hsp_is_admission_day_counts boolean;
85                          _hsp_missed_days_amount  integer;
86                          _hsp_bed_days_amount integer;
87                          _hsp_issue_planned_date date;
88                          _hsp_complexity_level_id integer;
89                          _hsp_bed_profile_id  integer;
90                          _hsp_refusal_employee_id integer;
91                          _hsp_plan_department_id integer;
92                          _hsp_is_finance_type_control boolean;
93                  BEGIN
94                      SELECT current_setting('app.source') INTO _app_code;
95                      IF ('n2o'=_app_code) THEN
96                      IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
97                          _step_id = NEW.id;
98                          _plc_goal_id = NEW.plc_goal_id;
99                          _plc_initiator_id = NEW.plc_initiator_id;
100                          _plc_place_id = NEW.plc_place_id;
101                          _plc_type_id = NEW.plc_type_id;
102                          _plc_is_viewed = NEW.plc_is_viewed;
103                          _plc_is_needed = NEW.plc_is_needed;
104                          _plc_is_sanitized = NEW.plc_is_sanitized;
105                          _plc_appointment_id = NEW.plc_appointment_id;
106                          _plc_planned_date = NEW.plc_planned_date;
107                          _plc_dental_formula_id = NEW.plc_dental_formula_id;
108                          _hsp_department_id = NEW.hsp_department_id;
109                          _hsp_funding_id = NEW.hsp_funding_id;
110                          _hsp_mes_id = NEW.hsp_mes_id;
111                          _hsp_previous_id = NEW.hsp_previous_id;
112                          _hsp_is_set_diagnosis = NEW.hsp_is_set_diagnosis;
113                          _hsp_is_diag_not_eq = NEW.hsp_is_diag_not_eq;
114                          _hsp_days_comp_algo_id = NEW.hsp_days_comp_algo_id;
115                          _hsp_is_admission_day_counts = NEW.hsp_is_admission_day_counts;
116                          _hsp_missed_days_amount = NEW.hsp_missed_days_amount;
117                          _hsp_bed_days_amount = NEW.hsp_bed_days_amount;
118                          _hsp_issue_planned_date = NEW.hsp_issue_planned_date;
119                          _hsp_complexity_level_id = NEW.hsp_complexity_level_id;
120                          _hsp_bed_profile_id  = NEW.hsp_bed_profile_id;
121                          _hsp_refusal_employee_id = NEW.hsp_refusal_employee_id;
122                          _hsp_plan_department_id = NEW.hsp_plan_department_id;
123                          _hsp_is_finance_type_control = NEW.is_finance_type_control;
124                          _case_mode_id = (select mct.case_mode_id
125                                              from mc_step ms
126                                              join mc_case mc on mc.id = ms.case_id
127                                              join mc_case_type mct on mc.case_type_id = mct.id
128  --where not exists (select 1 from mc_step s join mc_case c on c.id = s.case_id join hsp_record hs on hs.id = ms.id where s.id = ms.id)
129  --and mct.case_mode_id = 2
130  --and ms.hsp_department_id IS not NULL /*and ms.plc_place_id is null*/
131                                              where ms.id = NEW.id);
132                      ELSIF TG_OP = 'DELETE' THEN
133                          _step_id = OLD.id;
134                      ELSE
135                          RETURN NULL;
136                      END IF;
137                      IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
138                          IF _case_mode_id = 1 THEN
139                              IF EXISTS (SELECT 1 FROM hsp_record WHERE id = _step_id) THEN
140                                  DELETE FROM hsp_record WHERE id = _step_id;
141                              END IF;
142                              IF EXISTS (SELECT 1 FROM plc_visit WHERE id = _step_id) THEN
143                                  UPDATE plc_visit
144                                     SET goal_id = _plc_goal_id, initiator_id = _plc_initiator_id, place_id = _plc_place_id, type_id = _plc_type_id, is_viewed = _plc_is_viewed,
145                                         is_needed = _plc_is_needed, is_sanitized = _plc_is_sanitized, appointment_id = _plc_appointment_id, planned_date = _plc_planned_date,
146                                         dental_formula_id = _plc_dental_formula_id
147                                   WHERE id = _step_id;
148                              ELSE
149                                  INSERT INTO plc_visit (id, goal_id, initiator_id, place_id, type_id, is_viewed, is_needed, is_sanitized, appointment_id, planned_date, dental_formula_id)
150                                  VALUES (_step_id, _plc_goal_id, _plc_initiator_id, _plc_place_id, _plc_type_id, _plc_is_viewed, _plc_is_needed, _plc_is_sanitized, _plc_appointment_id, _plc_planned_date, _plc_dental_formula_id);
151                              END IF;
152                          ELSE
153                              IF EXISTS (SELECT 1 FROM plc_visit WHERE id = _step_id) THEN
154                                  DELETE FROM plc_visit WHERE id = _step_id;
155                              END IF;
156                              IF EXISTS (SELECT 1 FROM hsp_record WHERE id = _step_id) THEN
157                                  UPDATE hsp_record SET department_id = _hsp_department_id, funding_id = _hsp_funding_id, mes_id = _hsp_mes_id, previous_id = _hsp_previous_id,
158                                                  is_set_diagnosis = _hsp_is_set_diagnosis, is_diag_not_eq = _hsp_is_diag_not_eq, days_comp_algo_id = _hsp_days_comp_algo_id,
159                                                  is_admission_day_counts = _hsp_is_admission_day_counts,  missed_days_amount = _hsp_missed_days_amount, bed_days_amount = _hsp_bed_days_amount,
160                                                  issue_planned_date = _hsp_issue_planned_date, complexity_level_id = _hsp_complexity_level_id, bed_profile_id = _hsp_bed_profile_id,
161                                                  refusal_employee_id = _hsp_refusal_employee_id, plan_department_id = _hsp_plan_department_id, is_finance_type_control = _hsp_is_finance_type_control  WHERE id = _step_id;
162                              ELSE
163                                  if(_hsp_department_id is null) then RETURN null; end if;
164                                  INSERT INTO hsp_record (id, department_id, funding_id, mes_id, previous_id, is_set_diagnosis, is_diag_not_eq, days_comp_algo_id, is_admission_day_counts,
165                                                      missed_days_amount, bed_days_amount, issue_planned_date, complexity_level_id, bed_profile_id, refusal_employee_id, plan_department_id, is_finance_type_control)
166                                  VALUES (_step_id, _hsp_department_id, _hsp_funding_id, _hsp_mes_id, _hsp_previous_id, _hsp_is_set_diagnosis, _hsp_is_diag_not_eq, _hsp_days_comp_algo_id,
167                                  _hsp_is_admission_day_counts, _hsp_missed_days_amount, _hsp_bed_days_amount, _hsp_issue_planned_date, _hsp_complexity_level_id, _hsp_bed_profile_id, _hsp_refusal_employee_id, _hsp_plan_department_id, _hsp_is_finance_type_control);
168                              END IF;
169                          END IF;
170                      END IF;
171                      IF TG_OP = 'DELETE' THEN
172                          DELETE FROM plc_visit WHERE id = _step_id;
173                          DELETE FROM hsp_record WHERE id = _step_id;
174                      END IF;
175                      END IF;
176                      RETURN NULL;
177                  END;
178                  $BODY$
179    LANGUAGE 'plpgsql' VOLATILE COST 100
180  ;
181  CREATE CONSTRAINT TRIGGER "trigger_update_plc_visit_or_hsp_record" AFTER INSERT OR UPDATE OR DELETE ON "public"."mc_step"
182  DEFERRABLE INITIALLY DEFERRED FOR EACH ROW
183  EXECUTE PROCEDURE "update_plc_visit_or_hsp_record"();
184
185
186
187
188
189
190
191
192
193
194
195
196CREATE
197OR REPLACE FUNCTION "public"."copy_to_md_srv_rendered" () RETURNS "pg_catalog"."trigger" AS $BODY$
198DECLARE _app_code TEXT ;
199BEGIN
200    /*
201     Данный триггер производит изменения(insert или update) в таблице md_srv_rendered
202     при совершении тех же операций в таблице sr_srv_rendered. Это необходимо для того,
203     чтобы интегрировать в "Ай-Новусовский" N2O модуль "Стоматология", разработанный,
204     "РТЛабс". Позже необходимо избавиться от таблицы md_srv_rendered, как таковой,
205     т.к. необходисые её поля присутствуют в таблице sr_srv_rendered.
206  
207   */
208    SELECT
209        current_setting ('app.source') INTO _app_code ;
210    IF (_app_code = 'typing2' or _app_code = 'medservices-ws') THEN
211        RETURN NEW ;
212    END
213    IF ;
214    IF (tg_op = 'INSERT') THEN
215    BEGIN
216        INSERT INTO md_srv_rendered (
217            ID,
218            is_urgent,
219            is_use_cryogenic,
220            is_use_endoscopic,
221            is_use_laser,
222            anesthesia_type_id,
223            step_id,
224            complication_type_id,
225            diagnosis_id,
226            case_id,
227            referral_id,
228            result_category_id,
229            patient_prescription_id,
230            health_group_id,
231            vmp_type_id,
232            vmp_method_id,
233            anatomic_zone_id,
234            prescription_id,
235            entity_sync_num
236        )
237    VALUES
238        (
239            NEW.ID,
240            NEW.md_is_urgent,
241            NEW.md_is_use_cryogenic,
242            NEW.md_is_use_endoscopic,
243            NEW.md_is_use_laser,
244            NEW.md_anesthesia_type_id,
245            NEW.md_step_id,
246            NEW.md_complication_type_id,
247            NEW.md_diagnosis_id,
248            NEW.md_case_id,
249            NEW.md_referral_id,
250            NEW.md_result_category_id,
251            NEW.md_patient_prescription_id,
252            NEW.md_health_group_id,
253            NEW.md_vmp_type_id,
254            NEW.md_vmp_method_id,
255            NEW.md_anatomic_zone_id,
256            NEW.md_prescription_id,
257            NEW.entity_sync_num
258        ) ; EXCEPTION
259    WHEN OTHERS THEN
260        RAISE NOTICE 'Record with id = % already exists in table md_srv_rendered!',
261        NEW.ID ;
262    END ;
263    ELSEIF (tg_op = 'UPDATE') THEN
264    IF NOT EXISTS (
265        SELECT
266            ID
267        FROM
268            md_srv_rendered
269        WHERE
270            ID = NEW.ID
271    ) THEN
272    BEGIN
273        INSERT INTO md_srv_rendered (
274            ID,
275            is_urgent,
276            is_use_cryogenic,
277            is_use_endoscopic,
278            is_use_laser,
279            anesthesia_type_id,
280            step_id,
281            complication_type_id,
282            diagnosis_id,
283            case_id,
284            referral_id,
285            result_category_id,
286            patient_prescription_id,
287            health_group_id,
288            vmp_type_id,
289            vmp_method_id,
290            anatomic_zone_id,
291            prescription_id,
292            entity_sync_num
293        )
294    VALUES
295        (
296            NEW.ID,
297            NEW.md_is_urgent,
298            NEW.md_is_use_cryogenic,
299            NEW.md_is_use_endoscopic,
300            NEW.md_is_use_laser,
301            NEW.md_anesthesia_type_id,
302            NEW.md_step_id,
303            NEW.md_complication_type_id,
304            NEW.md_diagnosis_id,
305            NEW.md_case_id,
306            NEW.md_referral_id,
307            NEW.md_result_category_id,
308            NEW.md_patient_prescription_id,
309            NEW.md_health_group_id,
310            NEW.md_vmp_type_id,
311            NEW.md_vmp_method_id,
312            NEW.md_anatomic_zone_id,
313            NEW.md_prescription_id,
314            NEW.entity_sync_num
315        ) ; EXCEPTION
316    WHEN OTHERS THEN
317        RAISE NOTICE 'Record with id = % already exists in table md_srv_rendered!',
318        NEW.ID ;
319    END ;
320    ELSE
321    BEGIN
322        UPDATE md_srv_rendered
323    SET is_urgent = NEW.md_is_urgent,
324    is_use_cryogenic = NEW.md_is_use_cryogenic,
325    is_use_endoscopic = NEW.md_is_use_endoscopic,
326    is_use_laser = NEW.md_is_use_laser,
327    anesthesia_type_id = NEW.md_anesthesia_type_id,
328    step_id = NEW.md_step_id,
329    complication_type_id = NEW.md_complication_type_id,
330    diagnosis_id = NEW.md_diagnosis_id,
331    case_id = NEW.md_case_id,
332    referral_id = NEW.md_referral_id,
333    result_category_id = NEW.md_result_category_id,
334    patient_prescription_id = NEW.md_patient_prescription_id,
335    health_group_id = NEW.md_health_group_id,
336    vmp_type_id = NEW.md_vmp_type_id,
337    vmp_method_id = NEW.md_vmp_method_id,
338    anatomic_zone_id = NEW.md_anatomic_zone_id,
339    prescription_id = NEW.md_prescription_id,
340    entity_sync_num = NEW.entity_sync_num
341WHERE
342    ID = NEW.ID ; EXCEPTION
343WHEN OTHERS THEN
344    RAISE NOTICE 'Error on update md_srv_rendered record with id = % !',
345    NEW.ID ;
346END ;
347END
348IF ;
349END
350IF ; RETURN NULL ;
351END ; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100;
352 
353ALTER FUNCTION "public"."copy_to_md_srv_rendered" () OWNER TO "app_group_master";
354 
355CREATE TRIGGER "trigger_copy_to_md_srv_rendered" AFTER INSERT
356OR UPDATE ON "public"."sr_srv_rendered" FOR EACH ROW EXECUTE PROCEDURE "copy_to_md_srv_rendered" ();