· 6 years ago · Jan 29, 2020, 06:00 AM
1DROP MATERIALIZED VIEW IF EXISTS analytics.mv_pgg_main_cub3;
2DROP VIEW IF EXISTS analytics.v_pgg_main_cub3;
3DROP VIEW IF EXISTS analytics.v_pgg_care_regimen;
4DROP VIEW IF EXISTS analytics.v_pgg_main_cub_mv3;
5
6ALTER TABLE public.mc_care_regimen ALTER COLUMN e_code SET DATA TYPE VARCHAR(100);
7
8
9
10CREATE VIEW "analytics"."v_pgg_care_regimen" AS SELECT mc_care_regimen.id,
11 mc_care_regimen.name,
12 1 AS sort
13 FROM mc_care_regimen
14 WHERE ((mc_care_regimen.e_code)::text = ANY (ARRAY['1'::text, '2'::text, '3'::text, '4'::text, '5'::text, '6'::text, '7'::text, '8'::text]))
15UNION
16 VALUES (0,'Не указано'::character varying(255),2);
17
18ALTER TABLE "analytics"."v_pgg_care_regimen" OWNER TO "app_group_master";
19
20CREATE VIEW "analytics"."v_pgg_main_cub3" AS WITH wdiagn AS (
21 WITH RECURSIVE wmd_diagnosis AS (
22 SELECT md_diagnosis.id,
23 md_diagnosis.code,
24 md_diagnosis.name,
25 (regexp_replace(replace((md_diagnosis.code)::text, '.'::text, '_'::text), '[^A-Z0-9_]'::text, ''::text, 'g'::text))::ltree AS ltr,
26 md_diagnosis.parent_id,
27 regexp_replace(replace((md_diagnosis.code)::text, '.'::text, '_'::text), '[^A-Z0-9_]'::text, ''::text, 'g'::text) AS main_ltr
28 FROM md_diagnosis
29 WHERE ((md_diagnosis.parent_id IS NULL) AND COALESCE(md_diagnosis.is_injury, true))
30 UNION ALL
31 SELECT n.id,
32 n.code,
33 n.name,
34 (r.ltr || (regexp_replace(replace((n.code)::text, '.'::text, '_'::text), '[^A-Z0-9_]'::text, ''::text, 'g'::text))::ltree),
35 n.parent_id,
36 r.main_ltr
37 FROM (wmd_diagnosis r
38 JOIN md_diagnosis n ON ((n.parent_id = r.id)))
39 )
40 SELECT wmd_diagnosis.id,
41 wmd_diagnosis.code,
42 wmd_diagnosis.name,
43 wmd_diagnosis.ltr,
44 wmd_diagnosis.parent_id,
45 wmd_diagnosis.main_ltr
46 FROM wmd_diagnosis
47 WHERE (wmd_diagnosis.ltr @ '!X* & !Y* & !V* & !Z*'::ltxtquery)
48 ORDER BY wmd_diagnosis.ltr
49 ), wdiagn_uses AS (
50 SELECT d.id,
51 d.code,
52 d.name,
53 d.ltr,
54 d.parent_id,
55 d.main_ltr,
56 dr.diagnosis_group_id,
57 dr.diagnosis_group_name,
58 dr.diagnosis_group_rules
59 FROM (wdiagn d
60 JOIN analytics.v_pgg_diagnosis_group dr ON ((d.ltr @ dr.diagnosis_group_rules)))
61 ), wset AS (
62 SELECT mc.uid,
63 mc.clinic_id AS mc_clinic_id,
64 COALESCE(sr.org_id, mc.clinic_id) AS sr_clinic_id,
65 COALESCE(vcd.id, 0) AS district_id,
66 vcd.name AS district_name,
67 mcl.id AS care_level_id,
68 mcr.id AS care_regimen_id,
69 mcpf.id AS care_providing_form_id,
70 du.diagnosis_group_id,
71 i.id AS individual_id,
72 al.address_location_id,
73 fst.id AS funding_id,
74 i.gender_id,
75 i.birth_dt,
76 i.death_dt,
77 mc.id AS mc_id,
78 ms.id AS ms_id,
79 sr.id AS sr_id,
80 "mс_ra".id AS mc_range_age_a_id,
81 "mс_rb".id AS mc_range_age_b_id,
82 "mс_rc".id AS mc_range_age_c_id,
83 mc_x.id AS mc_democube_days_id,
84 mc_x.year_ AS mc_year_id,
85 mc_x.quarter_of_year AS mc_quarter_id,
86 mc_x.month_of_year AS mc_month_id,
87 ms_ra.id AS ms_range_age_a_id,
88 ms_rb.id AS ms_range_age_b_id,
89 ms_rc.id AS ms_range_age_c_id,
90 ms_x.id AS ms_democube_days_id,
91 ms_x.year_ AS ms_year_id,
92 ms_x.quarter_of_year AS ms_quarter_id,
93 ms_x.month_of_year AS ms_month_id,
94 sr_ra.id AS sr_range_age_a_id,
95 sr_rb.id AS sr_range_age_b_id,
96 sr_rc.id AS sr_range_age_c_id,
97 sr_x.id AS sr_democube_days_id,
98 sr_x.year_ AS sr_year_id,
99 sr_x.quarter_of_year AS sr_quarter_id,
100 sr_x.month_of_year AS sr_month_id,
101 mc.aud_source_create AS mc_aud_source_create,
102 ms.aud_source_create AS ms_aud_source_create,
103 sr.aud_source_create AS sr_aud_source_create,
104 CASE lcs.last_or_closing_step
105 WHEN true THEN ms_doctor.id
106 ELSE NULL::integer
107 END AS mc_doctor_id,
108 ms_doctor.id AS ms_doctor_id,
109 sr_doctor.id AS sr_doctor_id,
110 1
111 FROM (((((((((((((((((((((((((((((((mc_case mc
112 JOIN LATERAL ( SELECT mcl_1.id,
113 mcl_1.name,
114 mcl_1.sort
115 FROM analytics.v_pgg_care_level mcl_1
116 WHERE (mcl_1.id = COALESCE(mc.care_level_id, 0))
117 LIMIT 1) mcl ON (true))
118 JOIN LATERAL ( SELECT mcr_1.id,
119 mcr_1.name,
120 mcr_1.sort
121 FROM analytics.v_pgg_care_regimen mcr_1
122 WHERE (mcr_1.id = COALESCE(mc.care_regimen_id, 0))
123 LIMIT 1) mcr ON (true))
124 JOIN LATERAL ( SELECT mcpf_1.id,
125 mcpf_1.name,
126 mcpf_1.sort
127 FROM analytics.v_pgg_care_providing_form mcpf_1
128 WHERE (mcpf_1.id = COALESCE(mc.care_providing_form_id, 0))
129 LIMIT 1) mcpf ON (true))
130 JOIN mc_diagnosis mcd ON ((mcd.id = mc.main_diagnos_id)))
131 JOIN wdiagn_uses du ON ((du.id = mcd.diagnos_id)))
132 JOIN analytics.v_pgg_funding fst ON ((fst.id = mc.funding_id)))
133 JOIN pim_individual i ON (((i.id = mc.patient_id) AND (i.gender_id = ANY (ARRAY[1, 2])) AND (i.birth_dt IS NOT NULL))))
134 JOIN LATERAL ( SELECT COALESCE(al_1.adr_val[1], 1) AS address_location_id
135 FROM ( SELECT array_agg(
136 CASE
137 WHEN ((COALESCE(adr__get_element_as_text(ppa.addr_id, '(4,s,0)'::text), ''::character varying))::text <> ''::text) THEN 1
138 ELSE 2
139 END ORDER BY t_1.code) AS adr_val
140 FROM ((pim_party_address ppa
141 JOIN pim_party_addr_to_addr_type b ON (((b.party_address_id = ppa.id) AND (ppa.is_valid = true))))
142 JOIN pim_address_type t_1 ON (((t_1.id = b.address_type_id) AND ((t_1.code)::text = ANY (ARRAY[('ACTUAL'::character varying)::text, ('REGISTER'::character varying)::text])))))
143 WHERE (ppa.party_id = i.id)) al_1
144 GROUP BY COALESCE(al_1.adr_val[1], 1)) al ON (true))
145 LEFT JOIN LATERAL ( SELECT vcd_1.id,
146 vcd_1.name
147 FROM analytics.v_pgg_clinic_district_patient vcd_1
148 WHERE ((vcd_1.clinic_id = mc.clinic_id) AND (vcd_1.patient_id = mc.patient_id) AND ((mc.open_date >= COALESCE(vcd_1.open_dt, mc.open_date)) AND (mc.open_date <= COALESCE(vcd_1.to_dt, mc.open_date))))
149 LIMIT 1) vcd ON (true))
150 LEFT JOIN mc_step ms ON ((ms.case_id = mc.id)))
151 LEFT JOIN LATERAL ( SELECT pe.id
152 FROM ((((((((sr_res_group srg
153 JOIN pim_employee_position pep ON ((pep.id = srg.responsible_id)))
154 JOIN pim_position pp ON ((pp.id = pep.position_id)))
155 JOIN pim_position_role rol ON ((rol.id = pp.role_id)))
156 JOIN pim_position_category cat ON (((cat.id = rol.category_id) AND ((cat.e_code)::text = '1'::text))))
157 JOIN pim_employee pe ON ((pe.id = pep.employee_id)))
158 JOIN sr_res_group_relationship srgr ON ((srgr.group_id = srg.id)))
159 JOIN sr_res_role srr ON ((srr.id = srgr.role_id)))
160 JOIN sr_res_role_kind srrk ON (((srrk.id = srr.resource_kind_id) AND ((srrk.code)::text = 'employeePositionResource'::text))))
161 WHERE (srg.id = ms.res_group_id)
162 LIMIT 1) ms_doctor ON (true))
163 LEFT JOIN LATERAL ( SELECT
164 CASE mc.closing_step_id
165 WHEN ms.id THEN true
166 WHEN first_value(ms.id) OVER (PARTITION BY ms.admission_date, ms.admission_time ORDER BY ms.admission_date DESC, ms.admission_time DESC) THEN true
167 ELSE false
168 END AS last_or_closing_step
169 LIMIT 1) lcs ON (true))
170 LEFT JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(ms.outcome_date, ms.admission_date))::timestamp with time zone, (i.birth_dt)::timestamp with time zone)))::integer AS age) ms_age ON (true))
171 LEFT JOIN analytics.v_pgg_range_age_a ms_ra ON (((ms_age.age <@ ms_ra.range_age) AND (ms_ra.gender_id = i.gender_id))))
172 LEFT JOIN analytics.v_pgg_range_age_b ms_rb ON (((ms_age.age <@ ms_rb.range_age) AND (ms_rb.gender_id = i.gender_id))))
173 LEFT JOIN analytics.v_pgg_range_age_c ms_rc ON (((ms_age.age <@ ms_rc.range_age) AND (ms_rc.gender_id = i.gender_id))))
174 LEFT JOIN analytics.cube_days ms_x ON ((ms_x.calendar_date = date_trunc('month'::text, (COALESCE(ms.outcome_date, ms.admission_date))::timestamp with time zone))))
175 LEFT JOIN md_srv_rendered msr ON ((msr.case_id = mc.id)))
176 LEFT JOIN sr_srv_rendered sr ON ((sr.id = msr.id)))
177 LEFT JOIN LATERAL ( SELECT pe.id
178 FROM ((((((((sr_res_group srg
179 JOIN pim_employee_position pep ON ((pep.id = srg.responsible_id)))
180 JOIN pim_position pp ON ((pp.id = pep.position_id)))
181 JOIN pim_position_role rol ON ((rol.id = pp.role_id)))
182 JOIN pim_position_category cat ON (((cat.id = rol.category_id) AND ((cat.e_code)::text = '1'::text))))
183 JOIN pim_employee pe ON ((pe.id = pep.employee_id)))
184 JOIN sr_res_group_relationship srgr ON ((srgr.group_id = srg.id)))
185 JOIN sr_res_role srr ON ((srr.id = srgr.role_id)))
186 JOIN sr_res_role_kind srrk ON (((srrk.id = srr.resource_kind_id) AND ((srrk.code)::text = 'employeePositionResource'::text))))
187 WHERE (srg.id = sr.res_group_id)
188 LIMIT 1) sr_doctor ON (true))
189 LEFT JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(sr.edate, sr.bdate))::timestamp with time zone, (i.birth_dt)::timestamp with time zone)))::integer AS age) sr_age ON (true))
190 LEFT JOIN analytics.v_pgg_range_age_a sr_ra ON (((sr_age.age <@ sr_ra.range_age) AND (sr_ra.gender_id = i.gender_id))))
191 LEFT JOIN analytics.v_pgg_range_age_b sr_rb ON (((sr_age.age <@ sr_rb.range_age) AND (sr_rb.gender_id = i.gender_id))))
192 LEFT JOIN analytics.v_pgg_range_age_c sr_rc ON (((sr_age.age <@ sr_rc.range_age) AND (sr_rc.gender_id = i.gender_id))))
193 LEFT JOIN analytics.cube_days sr_x ON ((sr_x.calendar_date = date_trunc('month'::text, (COALESCE(sr.edate, sr.bdate))::timestamp with time zone))))
194 LEFT JOIN LATERAL ( SELECT min(t_1.dt) AS dt_min
195 FROM ( SELECT unnest(ARRAY[ms.outcome_date, ms.admission_date, sr.edate, sr.bdate]) AS unnest) t_1(dt)) dt_min ON (true))
196 JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(mc.close_date, mc.open_date, dt_min.dt_min))::timestamp with time zone, (i.birth_dt)::timestamp with time zone)))::integer AS age
197 LIMIT 1) "mс_age" ON (true))
198 JOIN analytics.v_pgg_range_age_a "mс_ra" ON ((("mс_age".age <@ "mс_ra".range_age) AND ("mс_ra".gender_id = i.gender_id))))
199 JOIN analytics.v_pgg_range_age_b "mс_rb" ON ((("mс_age".age <@ "mс_rb".range_age) AND ("mс_rb".gender_id = i.gender_id))))
200 JOIN analytics.v_pgg_range_age_c "mс_rc" ON ((("mс_age".age <@ "mс_rc".range_age) AND ("mс_rc".gender_id = i.gender_id))))
201 JOIN analytics.cube_days mc_x ON ((mc_x.calendar_date = date_trunc('month'::text, (COALESCE(mc.close_date, mc.open_date, dt_min.dt_min))::timestamp with time zone))))
202 WHERE (mc.create_date >= '2015-01-01'::date)
203 ), wdeath_all AS (
204 SELECT w.mc_clinic_id,
205 w.sr_clinic_id,
206 w.district_id,
207 w.care_level_id,
208 w.care_regimen_id,
209 w.care_providing_form_id,
210 w.diagnosis_group_id,
211 w.funding_id,
212 w.gender_id,
213 w.individual_id,
214 w.address_location_id,
215 dd_ra.id AS dd_range_age_a_id,
216 dd_rb.id AS dd_range_age_b_id,
217 dd_rc.id AS dd_range_age_c_id,
218 dd_x.id AS dd_democube_days_id,
219 dd_x.year_ AS dd_year_id,
220 dd_x.quarter_of_year AS dd_quarter_id,
221 dd_x.month_of_year AS dd_month_id,
222 w.individual_id AS dd_id,
223 w.sr_aud_source_create AS dd_aud_source_create,
224 CASE
225 WHEN (max(w.mc_doctor_id) IS NULL) THEN max(w.ms_doctor_id)
226 ELSE NULL::integer
227 END AS dd_doctor_id,
228 1
229 FROM (((((((((wset w
230 JOIN md_srv_protocol p ON ((p.srv_rendered_id = w.sr_id)))
231 JOIN md_ehr_protocol prot ON (((p.protocol_id = prot.id) AND ((prot.template_path)::text ~~* '%med%svid%smert%'::text))))
232 LEFT JOIN LATERAL ( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS estimated_death_dt
233 FROM (ehr_protocol_query_result r
234 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'EstimatedDeathDate'::text) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
235 ORDER BY r.id DESC
236 LIMIT 1) estimated_death_dt ON (true))
237 JOIN LATERAL ( SELECT COALESCE((( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS to_date
238 FROM (ehr_protocol_query_result r
239 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = ANY (ARRAY[('PerinatalCertificateDeathDate'::character varying)::text, ('CertificateDeathDate'::character varying)::text, ('DeathDateProtocol'::character varying)::text])) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
240 ORDER BY r.id DESC
241 LIMIT 1))::timestamp without time zone, w.death_dt, (estimated_death_dt.estimated_death_dt)::timestamp without time zone, '1900-01-01 00:00:00'::timestamp without time zone) AS death_dt) death_dt ON (true))
242 JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(death_dt.death_dt, w.death_dt))::timestamp with time zone, (w.birth_dt)::timestamp with time zone)))::integer AS age
243 LIMIT 1) dd_age ON (true))
244 JOIN analytics.v_pgg_range_age_a dd_ra ON (((dd_age.age <@ dd_ra.range_age) AND (dd_ra.gender_id = w.gender_id))))
245 JOIN analytics.v_pgg_range_age_b dd_rb ON (((dd_age.age <@ dd_rb.range_age) AND (dd_rb.gender_id = w.gender_id))))
246 JOIN analytics.v_pgg_range_age_c dd_rc ON (((dd_age.age <@ dd_rc.range_age) AND (dd_rc.gender_id = w.gender_id))))
247 JOIN analytics.cube_days dd_x ON ((dd_x.calendar_date = date_trunc('month'::text, ((COALESCE(death_dt.death_dt, w.death_dt))::date)::timestamp with time zone))))
248 GROUP BY w.mc_clinic_id, w.sr_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.funding_id, w.gender_id, w.individual_id, w.address_location_id, dd_ra.id, dd_rb.id, dd_rc.id, dd_x.id, dd_x.year_, dd_x.quarter_of_year, dd_x.month_of_year, w.sr_aud_source_create, w.district_id
249 ), wdeath_home AS (
250 SELECT w.mc_clinic_id,
251 w.sr_clinic_id,
252 w.district_id,
253 w.care_level_id,
254 w.care_regimen_id,
255 w.care_providing_form_id,
256 w.diagnosis_group_id,
257 w.funding_id,
258 w.gender_id,
259 w.individual_id,
260 w.address_location_id,
261 dd_ra.id AS dd_range_age_a_id,
262 dd_rb.id AS dd_range_age_b_id,
263 dd_rc.id AS dd_range_age_c_id,
264 dd_x.id AS dd_democube_days_id,
265 dd_x.year_ AS dd_year_id,
266 dd_x.quarter_of_year AS dd_quarter_id,
267 dd_x.month_of_year AS dd_month_id,
268 w.individual_id AS dd_id,
269 w.sr_aud_source_create AS dd_aud_source_create,
270 CASE
271 WHEN (max(w.mc_doctor_id) IS NULL) THEN max(w.ms_doctor_id)
272 ELSE NULL::integer
273 END AS dd_doctor_id,
274 1
275 FROM ((((((((((((wset w
276 JOIN md_srv_protocol p ON ((p.srv_rendered_id = w.sr_id)))
277 JOIN md_ehr_protocol prot ON (((p.protocol_id = prot.id) AND ((prot.template_path)::text ~~* '%med%svid%smert%'::text))))
278 LEFT JOIN LATERAL ( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS estimated_death_dt
279 FROM (ehr_protocol_query_result r
280 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'EstimatedDeathDate'::text) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
281 ORDER BY r.id DESC
282 LIMIT 1) estimated_death_dt ON (true))
283 JOIN LATERAL ( SELECT COALESCE((( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS to_date
284 FROM (ehr_protocol_query_result r
285 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = ANY (ARRAY[('PerinatalCertificateDeathDate'::character varying)::text, ('CertificateDeathDate'::character varying)::text, ('DeathDateProtocol'::character varying)::text])) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
286 ORDER BY r.id DESC
287 LIMIT 1))::timestamp without time zone, w.death_dt, (estimated_death_dt.estimated_death_dt)::timestamp without time zone, '1900-01-01 00:00:00'::timestamp without time zone) AS death_dt) death_dt ON (true))
288 JOIN LATERAL ( SELECT true AS bool
289 FROM pci_patient_reg reg_1
290 WHERE ((reg_1.patient_id = w.individual_id) AND ((reg_1.clinic_id = w.sr_clinic_id) AND (death_dt.death_dt >= reg_1.reg_dt) AND (death_dt.death_dt <= reg_1.unreg_dt)))
291 LIMIT 1) reg ON (true))
292 LEFT JOIN LATERAL ( SELECT r.value
293 FROM (ehr_protocol_query_result r
294 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'death_circumstances'::text) AND (r.protocol_id = prot.id))))
295 ORDER BY r.id DESC
296 LIMIT 1) deadth_home1 ON (true))
297 LEFT JOIN LATERAL ( SELECT r.value
298 FROM (ehr_protocol_query_result r
299 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'newborn_death_place'::text) AND (r.protocol_id = prot.id))))
300 WHERE ((prot.template_path)::text ~~ '%med%svid$perin$smert%'::text)
301 ORDER BY r.id DESC
302 LIMIT 1) deadth_home2 ON (true))
303 JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(death_dt.death_dt, w.death_dt))::timestamp with time zone, (w.birth_dt)::timestamp with time zone)))::integer AS age
304 LIMIT 1) dd_age ON (true))
305 JOIN analytics.v_pgg_range_age_a dd_ra ON (((dd_age.age <@ dd_ra.range_age) AND (dd_ra.gender_id = w.gender_id))))
306 JOIN analytics.v_pgg_range_age_b dd_rb ON (((dd_age.age <@ dd_rb.range_age) AND (dd_rb.gender_id = w.gender_id))))
307 JOIN analytics.v_pgg_range_age_c dd_rc ON (((dd_age.age <@ dd_rc.range_age) AND (dd_rc.gender_id = w.gender_id))))
308 JOIN analytics.cube_days dd_x ON ((dd_x.calendar_date = date_trunc('month'::text, ((COALESCE(death_dt.death_dt, w.death_dt))::date)::timestamp with time zone))))
309 WHERE ((deadth_home1.value = 'at0024'::text) OR (deadth_home2.value = 'дома'::text))
310 GROUP BY w.mc_clinic_id, w.sr_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.funding_id, w.gender_id, w.individual_id, w.address_location_id, dd_ra.id, dd_rb.id, dd_rc.id, dd_x.id, dd_x.year_, dd_x.quarter_of_year, dd_x.month_of_year, w.sr_aud_source_create, w.district_id
311 ), wdeath_pregnant AS (
312 SELECT w.uid,
313 w.mc_clinic_id,
314 w.sr_clinic_id,
315 w.district_id,
316 w.care_level_id,
317 w.care_regimen_id,
318 w.care_providing_form_id,
319 w.diagnosis_group_id,
320 w.funding_id,
321 w.gender_id,
322 w.individual_id,
323 w.address_location_id,
324 dd_ra.id AS dd_range_age_a_id,
325 dd_rb.id AS dd_range_age_b_id,
326 dd_rc.id AS dd_range_age_c_id,
327 dd_x.id AS dd_democube_days_id,
328 dd_x.year_ AS dd_year_id,
329 dd_x.quarter_of_year AS dd_quarter_id,
330 dd_x.month_of_year AS dd_month_id,
331 w.individual_id AS dd_id,
332 w.sr_aud_source_create AS dd_aud_source_create,
333 CASE
334 WHEN (max(w.mc_doctor_id) IS NULL) THEN max(w.ms_doctor_id)
335 ELSE NULL::integer
336 END AS dd_doctor_id,
337 1
338 FROM (((((((((((((wset w
339 JOIN md_srv_protocol p ON ((p.srv_rendered_id = w.sr_id)))
340 JOIN md_ehr_protocol prot ON (((p.protocol_id = prot.id) AND ((prot.template_path)::text ~~* '%med%svid%smert%'::text))))
341 LEFT JOIN LATERAL ( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS estimated_death_dt
342 FROM (ehr_protocol_query_result r
343 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'EstimatedDeathDate'::text) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
344 ORDER BY r.id DESC
345 LIMIT 1) estimated_death_dt ON (true))
346 JOIN LATERAL ( SELECT COALESCE((( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS to_date
347 FROM (ehr_protocol_query_result r
348 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = ANY (ARRAY[('PerinatalCertificateDeathDate'::character varying)::text, ('CertificateDeathDate'::character varying)::text, ('DeathDateProtocol'::character varying)::text])) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
349 ORDER BY r.id DESC
350 LIMIT 1))::timestamp without time zone, w.death_dt, (estimated_death_dt.estimated_death_dt)::timestamp without time zone, '1900-01-01 00:00:00'::timestamp without time zone) AS death_dt) death_dt ON (true))
351 JOIN LATERAL ( SELECT true AS bool
352 FROM pci_patient_reg reg_1
353 WHERE ((reg_1.patient_id = w.individual_id) AND ((reg_1.clinic_id = w.sr_clinic_id) AND (death_dt.death_dt >= reg_1.reg_dt) AND (death_dt.death_dt <= reg_1.unreg_dt)))
354 LIMIT 1) reg ON (true))
355 LEFT JOIN LATERAL ( SELECT r.value
356 FROM (ehr_protocol_query_result r
357 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'newborn_death_place'::text) AND (r.protocol_id = prot.id))))
358 ORDER BY r.id DESC
359 LIMIT 1) deadth_pregnant ON (true))
360 LEFT JOIN LATERAL ( SELECT r.value
361 FROM (ehr_protocol_query_result r
362 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'PregnantDeath'::text) AND (r.protocol_id = prot.id))))
363 ORDER BY r.id DESC
364 LIMIT 1) deadth_pregnant2 ON (true))
365 JOIN LATERAL ( SELECT true AS bool
366 WHERE ((deadth_pregnant.value = 'at0004'::text) OR (deadth_pregnant2.value = 'в процессе родов'::text))) pregnant ON (true))
367 JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(death_dt.death_dt, w.death_dt))::timestamp with time zone, (w.birth_dt)::timestamp with time zone)))::integer AS age
368 LIMIT 1) dd_age ON (true))
369 JOIN analytics.v_pgg_range_age_a dd_ra ON (((dd_age.age <@ dd_ra.range_age) AND (dd_ra.gender_id = w.gender_id))))
370 JOIN analytics.v_pgg_range_age_b dd_rb ON (((dd_age.age <@ dd_rb.range_age) AND (dd_rb.gender_id = w.gender_id))))
371 JOIN analytics.v_pgg_range_age_c dd_rc ON (((dd_age.age <@ dd_rc.range_age) AND (dd_rc.gender_id = w.gender_id))))
372 JOIN analytics.cube_days dd_x ON ((dd_x.calendar_date = date_trunc('month'::text, ((COALESCE(death_dt.death_dt, w.death_dt))::date)::timestamp with time zone))))
373 GROUP BY w.uid, w.mc_clinic_id, w.sr_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.funding_id, w.gender_id, w.individual_id, w.address_location_id, dd_ra.id, dd_rb.id, dd_rc.id, dd_x.id, dd_x.year_, dd_x.quarter_of_year, dd_x.month_of_year, deadth_pregnant.value, w.sr_aud_source_create, w.district_id
374 ), wdeath_child AS (
375 SELECT w.mc_clinic_id,
376 w.sr_clinic_id,
377 w.district_id,
378 w.care_level_id,
379 w.care_regimen_id,
380 w.care_providing_form_id,
381 w.diagnosis_group_id,
382 w.funding_id,
383 w.gender_id,
384 w.individual_id,
385 w.address_location_id,
386 dd_ra.id AS dd_range_age_a_id,
387 dd_rb.id AS dd_range_age_b_id,
388 dd_rc.id AS dd_range_age_c_id,
389 dd_x.id AS dd_democube_days_id,
390 dd_x.year_ AS dd_year_id,
391 dd_x.quarter_of_year AS dd_quarter_id,
392 dd_x.month_of_year AS dd_month_id,
393 w.individual_id AS dd_id,
394 w.sr_aud_source_create AS dd_aud_source_create,
395 CASE
396 WHEN (max(w.mc_doctor_id) IS NULL) THEN max(w.ms_doctor_id)
397 ELSE NULL::integer
398 END AS dd_doctor_id,
399 1
400 FROM (((((((((wset w
401 JOIN md_srv_protocol p ON ((p.srv_rendered_id = w.sr_id)))
402 JOIN md_ehr_protocol prot ON (((p.protocol_id = prot.id) AND ((prot.template_path)::text ~~* '%med%svid%smert%'::text))))
403 LEFT JOIN LATERAL ( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS estimated_death_dt
404 FROM (ehr_protocol_query_result r
405 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'EstimatedDeathDate'::text) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
406 ORDER BY r.id DESC
407 LIMIT 1) estimated_death_dt ON (true))
408 JOIN LATERAL ( SELECT COALESCE((( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS to_date
409 FROM (ehr_protocol_query_result r
410 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = ANY (ARRAY[('PerinatalCertificateDeathDate'::character varying)::text, ('CertificateDeathDate'::character varying)::text, ('DeathDateProtocol'::character varying)::text])) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
411 ORDER BY r.id DESC
412 LIMIT 1))::timestamp without time zone, w.death_dt, (estimated_death_dt.estimated_death_dt)::timestamp without time zone, '1900-01-01 00:00:00'::timestamp without time zone) AS death_dt) death_dt ON (true))
413 JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(death_dt.death_dt, w.death_dt))::timestamp with time zone, (w.birth_dt)::timestamp with time zone)))::integer AS age
414 LIMIT 1) dd_age ON ((dd_age.age <= 1)))
415 JOIN analytics.v_pgg_range_age_a dd_ra ON (((dd_age.age <@ dd_ra.range_age) AND (dd_ra.gender_id = w.gender_id))))
416 JOIN analytics.v_pgg_range_age_b dd_rb ON (((dd_age.age <@ dd_rb.range_age) AND (dd_rb.gender_id = w.gender_id))))
417 JOIN analytics.v_pgg_range_age_c dd_rc ON (((dd_age.age <@ dd_rc.range_age) AND (dd_rc.gender_id = w.gender_id))))
418 JOIN analytics.cube_days dd_x ON ((dd_x.calendar_date = date_trunc('month'::text, ((COALESCE(death_dt.death_dt, w.death_dt))::date)::timestamp with time zone))))
419 GROUP BY w.mc_clinic_id, w.sr_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.funding_id, w.gender_id, w.individual_id, w.address_location_id, dd_ra.id, dd_rb.id, dd_rc.id, dd_x.id, dd_x.year_, dd_x.quarter_of_year, dd_x.month_of_year, w.sr_aud_source_create, w.district_id
420 )
421 SELECT t.clinic_id,
422 t.mc_clinic_id,
423 t.care_level_id,
424 t.district_id,
425 t.care_regimen_id,
426 t.care_providing_form_id,
427 t.diagnosis_group_id,
428 t.address_location_id,
429 t.funding_id,
430 t.gender_id,
431 t.range_age_a_id,
432 t.range_age_b_id,
433 t.range_age_c_id,
434 t.democube_days_id,
435 t.year_id,
436 t.quarter_id,
437 t.month_id,
438 t.aud_source_create,
439 t.doctor_id,
440 COALESCE(pgg_asc.id, (5)::bigint) AS source_create_id,
441 sum(t.mc_count) AS mc_count,
442 sum(t.ms_count) AS ms_count,
443 sum(t.sr_count) AS sr_count,
444 sum(t.dd_count) AS dd_count,
445 sum(t.dh_count) AS dh_count,
446 sum(t.dp_count) AS dp_count,
447 sum(t.dc_count) AS dc_count
448 FROM (( SELECT w.mc_clinic_id AS clinic_id,
449 w.mc_clinic_id,
450 w.district_id,
451 w.care_level_id,
452 w.care_regimen_id,
453 w.care_providing_form_id,
454 w.diagnosis_group_id,
455 w.address_location_id,
456 w.funding_id,
457 w.gender_id,
458 w.mc_range_age_a_id AS range_age_a_id,
459 w.mc_range_age_b_id AS range_age_b_id,
460 w.mc_range_age_c_id AS range_age_c_id,
461 w.mc_democube_days_id AS democube_days_id,
462 w.mc_year_id AS year_id,
463 w.mc_quarter_id AS quarter_id,
464 w.mc_month_id AS month_id,
465 w.mc_aud_source_create AS aud_source_create,
466 CASE
467 WHEN (max(w.mc_doctor_id) IS NULL) THEN max(w.ms_doctor_id)
468 ELSE NULL::integer
469 END AS doctor_id,
470 count(DISTINCT w.mc_id) AS mc_count,
471 0 AS ms_count,
472 0 AS sr_count,
473 0 AS dd_count,
474 0 AS dh_count,
475 0 AS dp_count,
476 0 AS dc_count
477 FROM wset w
478 GROUP BY w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.mc_range_age_a_id, w.mc_range_age_b_id, w.mc_range_age_c_id, w.mc_democube_days_id, w.mc_year_id, w.mc_quarter_id, w.mc_month_id, w.mc_aud_source_create, w.district_id
479 UNION ALL
480 SELECT w.sr_clinic_id AS clinic_id,
481 w.mc_clinic_id,
482 w.district_id,
483 w.care_level_id,
484 w.care_regimen_id,
485 w.care_providing_form_id,
486 w.diagnosis_group_id,
487 w.address_location_id,
488 w.funding_id,
489 w.gender_id,
490 w.ms_range_age_a_id AS range_age_a_id,
491 w.ms_range_age_b_id AS range_age_b_id,
492 w.ms_range_age_c_id AS range_age_c_id,
493 w.ms_democube_days_id AS democube_days_id,
494 w.ms_year_id,
495 w.ms_quarter_id,
496 w.ms_month_id,
497 w.ms_aud_source_create AS aud_source_create,
498 COALESCE(max(w.ms_doctor_id), max(w.mc_doctor_id)) AS doctor_id,
499 0 AS mc_count,
500 count(DISTINCT w.ms_id) AS ms_count,
501 0 AS sr_count,
502 0 AS dd_count,
503 0 AS dh_count,
504 0 AS dp_count,
505 0 AS dc_count
506 FROM wset w
507 WHERE (w.ms_democube_days_id IS NOT NULL)
508 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.ms_range_age_a_id, w.ms_range_age_b_id, w.ms_range_age_c_id, w.ms_democube_days_id, w.ms_year_id, w.ms_quarter_id, w.ms_month_id, w.ms_aud_source_create, w.district_id
509 UNION ALL
510 SELECT w.sr_clinic_id AS clinic_id,
511 w.mc_clinic_id,
512 w.district_id,
513 w.care_level_id,
514 w.care_regimen_id,
515 w.care_providing_form_id,
516 w.diagnosis_group_id,
517 w.address_location_id,
518 w.funding_id,
519 w.gender_id,
520 w.sr_range_age_a_id AS range_age_a_id,
521 w.sr_range_age_b_id AS range_age_b_id,
522 w.sr_range_age_c_id AS range_age_c_id,
523 w.sr_democube_days_id AS democube_days_id,
524 w.sr_year_id,
525 w.sr_quarter_id,
526 w.sr_month_id,
527 w.sr_aud_source_create AS aud_source_create,
528 COALESCE(max(w.sr_doctor_id), max(w.mc_doctor_id)) AS doctor_id,
529 0 AS mc_count,
530 0 AS ms_count,
531 count(DISTINCT w.sr_id) AS sr_count,
532 0 AS dd_count,
533 0 AS dh_count,
534 0 AS dp_count,
535 0 AS dc_count
536 FROM wset w
537 WHERE (w.sr_democube_days_id IS NOT NULL)
538 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.sr_range_age_a_id, w.sr_range_age_b_id, w.sr_range_age_c_id, w.sr_democube_days_id, w.sr_year_id, w.sr_quarter_id, w.sr_month_id, w.sr_aud_source_create, w.district_id
539 UNION ALL
540 SELECT w.sr_clinic_id AS clinic_id,
541 w.mc_clinic_id,
542 w.district_id,
543 w.care_level_id,
544 w.care_regimen_id,
545 w.care_providing_form_id,
546 w.diagnosis_group_id,
547 w.address_location_id,
548 w.funding_id,
549 w.gender_id,
550 w.dd_range_age_a_id AS range_age_a_id,
551 w.dd_range_age_b_id AS range_age_b_id,
552 w.dd_range_age_c_id AS range_age_c_id,
553 w.dd_democube_days_id AS democube_days_id,
554 w.dd_year_id,
555 w.dd_quarter_id,
556 w.dd_month_id,
557 w.dd_aud_source_create AS aud_source_create,
558 max(w.dd_doctor_id) AS doctor_id,
559 0 AS mc_count,
560 0 AS ms_count,
561 0 AS sr_count,
562 count(DISTINCT w.individual_id) AS dd_count,
563 0 AS dh_count,
564 0 AS dp_count,
565 0 AS dc_count
566 FROM wdeath_all w
567 WHERE (w.dd_democube_days_id IS NOT NULL)
568 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.dd_range_age_a_id, w.dd_range_age_b_id, w.dd_range_age_c_id, w.dd_democube_days_id, w.dd_year_id, w.dd_quarter_id, w.dd_month_id, w.dd_aud_source_create, w.district_id
569 UNION ALL
570 SELECT w.sr_clinic_id AS clinic_id,
571 w.mc_clinic_id,
572 w.district_id,
573 w.care_level_id,
574 w.care_regimen_id,
575 w.care_providing_form_id,
576 w.diagnosis_group_id,
577 w.address_location_id,
578 w.funding_id,
579 w.gender_id,
580 w.dd_range_age_a_id AS range_age_a_id,
581 w.dd_range_age_b_id AS range_age_b_id,
582 w.dd_range_age_c_id AS range_age_c_id,
583 w.dd_democube_days_id AS democube_days_id,
584 w.dd_year_id,
585 w.dd_quarter_id,
586 w.dd_month_id,
587 w.dd_aud_source_create AS aud_source_create,
588 max(w.dd_doctor_id) AS doctor_id,
589 0 AS mc_count,
590 0 AS ms_count,
591 0 AS sr_count,
592 0 AS dd_count,
593 count(DISTINCT w.individual_id) AS dh_count,
594 0 AS dp_count,
595 0 AS dc_count
596 FROM wdeath_home w
597 WHERE (w.dd_democube_days_id IS NOT NULL)
598 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.dd_range_age_a_id, w.dd_range_age_b_id, w.dd_range_age_c_id, w.dd_democube_days_id, w.dd_year_id, w.dd_quarter_id, w.dd_month_id, w.dd_aud_source_create, w.district_id
599 UNION ALL
600 SELECT w.sr_clinic_id AS clinic_id,
601 w.mc_clinic_id,
602 w.district_id,
603 w.care_level_id,
604 w.care_regimen_id,
605 w.care_providing_form_id,
606 w.diagnosis_group_id,
607 w.address_location_id,
608 w.funding_id,
609 w.gender_id,
610 w.dd_range_age_a_id AS range_age_a_id,
611 w.dd_range_age_b_id AS range_age_b_id,
612 w.dd_range_age_c_id AS range_age_c_id,
613 w.dd_democube_days_id AS democube_days_id,
614 w.dd_year_id,
615 w.dd_quarter_id,
616 w.dd_month_id,
617 w.dd_aud_source_create AS aud_source_create,
618 max(w.dd_doctor_id) AS doctor_id,
619 0 AS mc_count,
620 0 AS ms_count,
621 0 AS sr_count,
622 0 AS dd_count,
623 0 AS dh_count,
624 count(DISTINCT w.individual_id) AS dp_count,
625 0 AS dc_count
626 FROM wdeath_pregnant w
627 WHERE (w.dd_democube_days_id IS NOT NULL)
628 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.dd_range_age_a_id, w.dd_range_age_b_id, w.dd_range_age_c_id, w.dd_democube_days_id, w.dd_year_id, w.dd_quarter_id, w.dd_month_id, w.dd_aud_source_create, w.district_id
629 UNION ALL
630 SELECT w.sr_clinic_id AS clinic_id,
631 w.mc_clinic_id,
632 w.district_id,
633 w.care_level_id,
634 w.care_regimen_id,
635 w.care_providing_form_id,
636 w.diagnosis_group_id,
637 w.address_location_id,
638 w.funding_id,
639 w.gender_id,
640 w.dd_range_age_a_id AS range_age_a_id,
641 w.dd_range_age_b_id AS range_age_b_id,
642 w.dd_range_age_c_id AS range_age_c_id,
643 w.dd_democube_days_id AS democube_days_id,
644 w.dd_year_id,
645 w.dd_quarter_id,
646 w.dd_month_id,
647 w.dd_aud_source_create AS aud_source_create,
648 COALESCE(max(w.dd_doctor_id), 0) AS doctor_id,
649 0 AS mc_count,
650 0 AS ms_count,
651 0 AS sr_count,
652 0 AS dd_count,
653 0 AS dh_count,
654 0 AS dp_count,
655 count(DISTINCT w.individual_id) AS dc_count
656 FROM wdeath_child w
657 WHERE (w.dd_democube_days_id IS NOT NULL)
658 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.dd_range_age_a_id, w.dd_range_age_b_id, w.dd_range_age_c_id, w.dd_democube_days_id, w.dd_year_id, w.dd_quarter_id, w.dd_month_id, w.dd_aud_source_create, w.district_id) t
659 LEFT JOIN LATERAL ( SELECT pgg_asc_1.id
660 FROM analytics.v_pgg_create_source pgg_asc_1
661 WHERE ((t.aud_source_create)::text = ANY (pgg_asc_1.create_source_code))
662 LIMIT 1) pgg_asc ON (true))
663 WHERE (t.democube_days_id IS NOT NULL)
664 GROUP BY t.clinic_id, t.mc_clinic_id, t.care_level_id, t.care_regimen_id, t.care_providing_form_id, t.diagnosis_group_id, t.address_location_id, t.funding_id, t.gender_id, t.range_age_a_id, t.range_age_b_id, t.range_age_c_id, t.democube_days_id, t.year_id, t.quarter_id, t.month_id, t.aud_source_create, COALESCE(pgg_asc.id, (5)::bigint), t.doctor_id, t.district_id;
665
666ALTER TABLE "analytics"."v_pgg_main_cub3" OWNER TO "app_group_master";
667
668CREATE MATERIALIZED VIEW "analytics"."mv_pgg_main_cub3"
669AS
670SELECT v_pgg_main_cub3.clinic_id,
671 v_pgg_main_cub3.mc_clinic_id,
672 v_pgg_main_cub3.care_level_id,
673 v_pgg_main_cub3.district_id,
674 v_pgg_main_cub3.care_regimen_id,
675 v_pgg_main_cub3.care_providing_form_id,
676 v_pgg_main_cub3.diagnosis_group_id,
677 v_pgg_main_cub3.address_location_id,
678 v_pgg_main_cub3.funding_id,
679 v_pgg_main_cub3.gender_id,
680 v_pgg_main_cub3.range_age_a_id,
681 v_pgg_main_cub3.range_age_b_id,
682 v_pgg_main_cub3.range_age_c_id,
683 v_pgg_main_cub3.democube_days_id,
684 v_pgg_main_cub3.year_id,
685 v_pgg_main_cub3.quarter_id,
686 v_pgg_main_cub3.month_id,
687 v_pgg_main_cub3.aud_source_create,
688 v_pgg_main_cub3.doctor_id,
689 v_pgg_main_cub3.source_create_id,
690 v_pgg_main_cub3.mc_count,
691 v_pgg_main_cub3.ms_count,
692 v_pgg_main_cub3.sr_count,
693 v_pgg_main_cub3.dd_count,
694 v_pgg_main_cub3.dh_count,
695 v_pgg_main_cub3.dp_count,
696 v_pgg_main_cub3.dc_count
697 FROM analytics.v_pgg_main_cub3;
698
699ALTER MATERIALIZED VIEW "analytics"."mv_pgg_main_cub3" OWNER TO "app_group_master";
700
701CREATE VIEW "analytics"."v_pgg_main_cub3" AS WITH wdiagn AS (
702 WITH RECURSIVE wmd_diagnosis AS (
703 SELECT md_diagnosis.id,
704 md_diagnosis.code,
705 md_diagnosis.name,
706 (regexp_replace(replace((md_diagnosis.code)::text, '.'::text, '_'::text), '[^A-Z0-9_]'::text, ''::text, 'g'::text))::ltree AS ltr,
707 md_diagnosis.parent_id,
708 regexp_replace(replace((md_diagnosis.code)::text, '.'::text, '_'::text), '[^A-Z0-9_]'::text, ''::text, 'g'::text) AS main_ltr
709 FROM md_diagnosis
710 WHERE ((md_diagnosis.parent_id IS NULL) AND COALESCE(md_diagnosis.is_injury, true))
711 UNION ALL
712 SELECT n.id,
713 n.code,
714 n.name,
715 (r.ltr || (regexp_replace(replace((n.code)::text, '.'::text, '_'::text), '[^A-Z0-9_]'::text, ''::text, 'g'::text))::ltree),
716 n.parent_id,
717 r.main_ltr
718 FROM (wmd_diagnosis r
719 JOIN md_diagnosis n ON ((n.parent_id = r.id)))
720 )
721 SELECT wmd_diagnosis.id,
722 wmd_diagnosis.code,
723 wmd_diagnosis.name,
724 wmd_diagnosis.ltr,
725 wmd_diagnosis.parent_id,
726 wmd_diagnosis.main_ltr
727 FROM wmd_diagnosis
728 WHERE (wmd_diagnosis.ltr @ '!X* & !Y* & !V* & !Z*'::ltxtquery)
729 ORDER BY wmd_diagnosis.ltr
730 ), wdiagn_uses AS (
731 SELECT d.id,
732 d.code,
733 d.name,
734 d.ltr,
735 d.parent_id,
736 d.main_ltr,
737 dr.diagnosis_group_id,
738 dr.diagnosis_group_name,
739 dr.diagnosis_group_rules
740 FROM (wdiagn d
741 JOIN analytics.v_pgg_diagnosis_group dr ON ((d.ltr @ dr.diagnosis_group_rules)))
742 ), wset AS (
743 SELECT mc.uid,
744 mc.clinic_id AS mc_clinic_id,
745 COALESCE(sr.org_id, mc.clinic_id) AS sr_clinic_id,
746 COALESCE(vcd.id, 0) AS district_id,
747 vcd.name AS district_name,
748 mcl.id AS care_level_id,
749 mcr.id AS care_regimen_id,
750 mcpf.id AS care_providing_form_id,
751 du.diagnosis_group_id,
752 i.id AS individual_id,
753 al.address_location_id,
754 fst.id AS funding_id,
755 i.gender_id,
756 i.birth_dt,
757 i.death_dt,
758 mc.id AS mc_id,
759 ms.id AS ms_id,
760 sr.id AS sr_id,
761 "mс_ra".id AS mc_range_age_a_id,
762 "mс_rb".id AS mc_range_age_b_id,
763 "mс_rc".id AS mc_range_age_c_id,
764 mc_x.id AS mc_democube_days_id,
765 mc_x.year_ AS mc_year_id,
766 mc_x.quarter_of_year AS mc_quarter_id,
767 mc_x.month_of_year AS mc_month_id,
768 ms_ra.id AS ms_range_age_a_id,
769 ms_rb.id AS ms_range_age_b_id,
770 ms_rc.id AS ms_range_age_c_id,
771 ms_x.id AS ms_democube_days_id,
772 ms_x.year_ AS ms_year_id,
773 ms_x.quarter_of_year AS ms_quarter_id,
774 ms_x.month_of_year AS ms_month_id,
775 sr_ra.id AS sr_range_age_a_id,
776 sr_rb.id AS sr_range_age_b_id,
777 sr_rc.id AS sr_range_age_c_id,
778 sr_x.id AS sr_democube_days_id,
779 sr_x.year_ AS sr_year_id,
780 sr_x.quarter_of_year AS sr_quarter_id,
781 sr_x.month_of_year AS sr_month_id,
782 mc.aud_source_create AS mc_aud_source_create,
783 ms.aud_source_create AS ms_aud_source_create,
784 sr.aud_source_create AS sr_aud_source_create,
785 CASE lcs.last_or_closing_step
786 WHEN true THEN ms_doctor.id
787 ELSE NULL::integer
788 END AS mc_doctor_id,
789 ms_doctor.id AS ms_doctor_id,
790 sr_doctor.id AS sr_doctor_id,
791 1
792 FROM (((((((((((((((((((((((((((((((mc_case mc
793 JOIN LATERAL ( SELECT mcl_1.id,
794 mcl_1.name,
795 mcl_1.sort
796 FROM analytics.v_pgg_care_level mcl_1
797 WHERE (mcl_1.id = COALESCE(mc.care_level_id, 0))
798 LIMIT 1) mcl ON (true))
799 JOIN LATERAL ( SELECT mcr_1.id,
800 mcr_1.name,
801 mcr_1.sort
802 FROM analytics.v_pgg_care_regimen mcr_1
803 WHERE (mcr_1.id = COALESCE(mc.care_regimen_id, 0))
804 LIMIT 1) mcr ON (true))
805 JOIN LATERAL ( SELECT mcpf_1.id,
806 mcpf_1.name,
807 mcpf_1.sort
808 FROM analytics.v_pgg_care_providing_form mcpf_1
809 WHERE (mcpf_1.id = COALESCE(mc.care_providing_form_id, 0))
810 LIMIT 1) mcpf ON (true))
811 JOIN mc_diagnosis mcd ON ((mcd.id = mc.main_diagnos_id)))
812 JOIN wdiagn_uses du ON ((du.id = mcd.diagnos_id)))
813 JOIN analytics.v_pgg_funding fst ON ((fst.id = mc.funding_id)))
814 JOIN pim_individual i ON (((i.id = mc.patient_id) AND (i.gender_id = ANY (ARRAY[1, 2])) AND (i.birth_dt IS NOT NULL))))
815 JOIN LATERAL ( SELECT COALESCE(al_1.adr_val[1], 1) AS address_location_id
816 FROM ( SELECT array_agg(
817 CASE
818 WHEN ((COALESCE(adr__get_element_as_text(ppa.addr_id, '(4,s,0)'::text), ''::character varying))::text <> ''::text) THEN 1
819 ELSE 2
820 END ORDER BY t_1.code) AS adr_val
821 FROM ((pim_party_address ppa
822 JOIN pim_party_addr_to_addr_type b ON (((b.party_address_id = ppa.id) AND (ppa.is_valid = true))))
823 JOIN pim_address_type t_1 ON (((t_1.id = b.address_type_id) AND ((t_1.code)::text = ANY (ARRAY[('ACTUAL'::character varying)::text, ('REGISTER'::character varying)::text])))))
824 WHERE (ppa.party_id = i.id)) al_1
825 GROUP BY COALESCE(al_1.adr_val[1], 1)) al ON (true))
826 LEFT JOIN LATERAL ( SELECT vcd_1.id,
827 vcd_1.name
828 FROM analytics.v_pgg_clinic_district_patient vcd_1
829 WHERE ((vcd_1.clinic_id = mc.clinic_id) AND (vcd_1.patient_id = mc.patient_id) AND ((mc.open_date >= COALESCE(vcd_1.open_dt, mc.open_date)) AND (mc.open_date <= COALESCE(vcd_1.to_dt, mc.open_date))))
830 LIMIT 1) vcd ON (true))
831 LEFT JOIN mc_step ms ON ((ms.case_id = mc.id)))
832 LEFT JOIN LATERAL ( SELECT pe.id
833 FROM ((((((((sr_res_group srg
834 JOIN pim_employee_position pep ON ((pep.id = srg.responsible_id)))
835 JOIN pim_position pp ON ((pp.id = pep.position_id)))
836 JOIN pim_position_role rol ON ((rol.id = pp.role_id)))
837 JOIN pim_position_category cat ON (((cat.id = rol.category_id) AND ((cat.e_code)::text = '1'::text))))
838 JOIN pim_employee pe ON ((pe.id = pep.employee_id)))
839 JOIN sr_res_group_relationship srgr ON ((srgr.group_id = srg.id)))
840 JOIN sr_res_role srr ON ((srr.id = srgr.role_id)))
841 JOIN sr_res_role_kind srrk ON (((srrk.id = srr.resource_kind_id) AND ((srrk.code)::text = 'employeePositionResource'::text))))
842 WHERE (srg.id = ms.res_group_id)
843 LIMIT 1) ms_doctor ON (true))
844 LEFT JOIN LATERAL ( SELECT
845 CASE mc.closing_step_id
846 WHEN ms.id THEN true
847 WHEN first_value(ms.id) OVER (PARTITION BY ms.admission_date, ms.admission_time ORDER BY ms.admission_date DESC, ms.admission_time DESC) THEN true
848 ELSE false
849 END AS last_or_closing_step
850 LIMIT 1) lcs ON (true))
851 LEFT JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(ms.outcome_date, ms.admission_date))::timestamp with time zone, (i.birth_dt)::timestamp with time zone)))::integer AS age) ms_age ON (true))
852 LEFT JOIN analytics.v_pgg_range_age_a ms_ra ON (((ms_age.age <@ ms_ra.range_age) AND (ms_ra.gender_id = i.gender_id))))
853 LEFT JOIN analytics.v_pgg_range_age_b ms_rb ON (((ms_age.age <@ ms_rb.range_age) AND (ms_rb.gender_id = i.gender_id))))
854 LEFT JOIN analytics.v_pgg_range_age_c ms_rc ON (((ms_age.age <@ ms_rc.range_age) AND (ms_rc.gender_id = i.gender_id))))
855 LEFT JOIN analytics.cube_days ms_x ON ((ms_x.calendar_date = date_trunc('month'::text, (COALESCE(ms.outcome_date, ms.admission_date))::timestamp with time zone))))
856 LEFT JOIN md_srv_rendered msr ON ((msr.case_id = mc.id)))
857 LEFT JOIN sr_srv_rendered sr ON ((sr.id = msr.id)))
858 LEFT JOIN LATERAL ( SELECT pe.id
859 FROM ((((((((sr_res_group srg
860 JOIN pim_employee_position pep ON ((pep.id = srg.responsible_id)))
861 JOIN pim_position pp ON ((pp.id = pep.position_id)))
862 JOIN pim_position_role rol ON ((rol.id = pp.role_id)))
863 JOIN pim_position_category cat ON (((cat.id = rol.category_id) AND ((cat.e_code)::text = '1'::text))))
864 JOIN pim_employee pe ON ((pe.id = pep.employee_id)))
865 JOIN sr_res_group_relationship srgr ON ((srgr.group_id = srg.id)))
866 JOIN sr_res_role srr ON ((srr.id = srgr.role_id)))
867 JOIN sr_res_role_kind srrk ON (((srrk.id = srr.resource_kind_id) AND ((srrk.code)::text = 'employeePositionResource'::text))))
868 WHERE (srg.id = sr.res_group_id)
869 LIMIT 1) sr_doctor ON (true))
870 LEFT JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(sr.edate, sr.bdate))::timestamp with time zone, (i.birth_dt)::timestamp with time zone)))::integer AS age) sr_age ON (true))
871 LEFT JOIN analytics.v_pgg_range_age_a sr_ra ON (((sr_age.age <@ sr_ra.range_age) AND (sr_ra.gender_id = i.gender_id))))
872 LEFT JOIN analytics.v_pgg_range_age_b sr_rb ON (((sr_age.age <@ sr_rb.range_age) AND (sr_rb.gender_id = i.gender_id))))
873 LEFT JOIN analytics.v_pgg_range_age_c sr_rc ON (((sr_age.age <@ sr_rc.range_age) AND (sr_rc.gender_id = i.gender_id))))
874 LEFT JOIN analytics.cube_days sr_x ON ((sr_x.calendar_date = date_trunc('month'::text, (COALESCE(sr.edate, sr.bdate))::timestamp with time zone))))
875 LEFT JOIN LATERAL ( SELECT min(t_1.dt) AS dt_min
876 FROM ( SELECT unnest(ARRAY[ms.outcome_date, ms.admission_date, sr.edate, sr.bdate]) AS unnest) t_1(dt)) dt_min ON (true))
877 JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(mc.close_date, mc.open_date, dt_min.dt_min))::timestamp with time zone, (i.birth_dt)::timestamp with time zone)))::integer AS age
878 LIMIT 1) "mс_age" ON (true))
879 JOIN analytics.v_pgg_range_age_a "mс_ra" ON ((("mс_age".age <@ "mс_ra".range_age) AND ("mс_ra".gender_id = i.gender_id))))
880 JOIN analytics.v_pgg_range_age_b "mс_rb" ON ((("mс_age".age <@ "mс_rb".range_age) AND ("mс_rb".gender_id = i.gender_id))))
881 JOIN analytics.v_pgg_range_age_c "mс_rc" ON ((("mс_age".age <@ "mс_rc".range_age) AND ("mс_rc".gender_id = i.gender_id))))
882 JOIN analytics.cube_days mc_x ON ((mc_x.calendar_date = date_trunc('month'::text, (COALESCE(mc.close_date, mc.open_date, dt_min.dt_min))::timestamp with time zone))))
883 WHERE (mc.create_date >= '2015-01-01'::date)
884 ), wdeath_all AS (
885 SELECT w.mc_clinic_id,
886 w.sr_clinic_id,
887 w.district_id,
888 w.care_level_id,
889 w.care_regimen_id,
890 w.care_providing_form_id,
891 w.diagnosis_group_id,
892 w.funding_id,
893 w.gender_id,
894 w.individual_id,
895 w.address_location_id,
896 dd_ra.id AS dd_range_age_a_id,
897 dd_rb.id AS dd_range_age_b_id,
898 dd_rc.id AS dd_range_age_c_id,
899 dd_x.id AS dd_democube_days_id,
900 dd_x.year_ AS dd_year_id,
901 dd_x.quarter_of_year AS dd_quarter_id,
902 dd_x.month_of_year AS dd_month_id,
903 w.individual_id AS dd_id,
904 w.sr_aud_source_create AS dd_aud_source_create,
905 CASE
906 WHEN (max(w.mc_doctor_id) IS NULL) THEN max(w.ms_doctor_id)
907 ELSE NULL::integer
908 END AS dd_doctor_id,
909 1
910 FROM (((((((((wset w
911 JOIN md_srv_protocol p ON ((p.srv_rendered_id = w.sr_id)))
912 JOIN md_ehr_protocol prot ON (((p.protocol_id = prot.id) AND ((prot.template_path)::text ~~* '%med%svid%smert%'::text))))
913 LEFT JOIN LATERAL ( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS estimated_death_dt
914 FROM (ehr_protocol_query_result r
915 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'EstimatedDeathDate'::text) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
916 ORDER BY r.id DESC
917 LIMIT 1) estimated_death_dt ON (true))
918 JOIN LATERAL ( SELECT COALESCE((( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS to_date
919 FROM (ehr_protocol_query_result r
920 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = ANY (ARRAY[('PerinatalCertificateDeathDate'::character varying)::text, ('CertificateDeathDate'::character varying)::text, ('DeathDateProtocol'::character varying)::text])) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
921 ORDER BY r.id DESC
922 LIMIT 1))::timestamp without time zone, w.death_dt, (estimated_death_dt.estimated_death_dt)::timestamp without time zone, '1900-01-01 00:00:00'::timestamp without time zone) AS death_dt) death_dt ON (true))
923 JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(death_dt.death_dt, w.death_dt))::timestamp with time zone, (w.birth_dt)::timestamp with time zone)))::integer AS age
924 LIMIT 1) dd_age ON (true))
925 JOIN analytics.v_pgg_range_age_a dd_ra ON (((dd_age.age <@ dd_ra.range_age) AND (dd_ra.gender_id = w.gender_id))))
926 JOIN analytics.v_pgg_range_age_b dd_rb ON (((dd_age.age <@ dd_rb.range_age) AND (dd_rb.gender_id = w.gender_id))))
927 JOIN analytics.v_pgg_range_age_c dd_rc ON (((dd_age.age <@ dd_rc.range_age) AND (dd_rc.gender_id = w.gender_id))))
928 JOIN analytics.cube_days dd_x ON ((dd_x.calendar_date = date_trunc('month'::text, ((COALESCE(death_dt.death_dt, w.death_dt))::date)::timestamp with time zone))))
929 GROUP BY w.mc_clinic_id, w.sr_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.funding_id, w.gender_id, w.individual_id, w.address_location_id, dd_ra.id, dd_rb.id, dd_rc.id, dd_x.id, dd_x.year_, dd_x.quarter_of_year, dd_x.month_of_year, w.sr_aud_source_create, w.district_id
930 ), wdeath_home AS (
931 SELECT w.mc_clinic_id,
932 w.sr_clinic_id,
933 w.district_id,
934 w.care_level_id,
935 w.care_regimen_id,
936 w.care_providing_form_id,
937 w.diagnosis_group_id,
938 w.funding_id,
939 w.gender_id,
940 w.individual_id,
941 w.address_location_id,
942 dd_ra.id AS dd_range_age_a_id,
943 dd_rb.id AS dd_range_age_b_id,
944 dd_rc.id AS dd_range_age_c_id,
945 dd_x.id AS dd_democube_days_id,
946 dd_x.year_ AS dd_year_id,
947 dd_x.quarter_of_year AS dd_quarter_id,
948 dd_x.month_of_year AS dd_month_id,
949 w.individual_id AS dd_id,
950 w.sr_aud_source_create AS dd_aud_source_create,
951 CASE
952 WHEN (max(w.mc_doctor_id) IS NULL) THEN max(w.ms_doctor_id)
953 ELSE NULL::integer
954 END AS dd_doctor_id,
955 1
956 FROM ((((((((((((wset w
957 JOIN md_srv_protocol p ON ((p.srv_rendered_id = w.sr_id)))
958 JOIN md_ehr_protocol prot ON (((p.protocol_id = prot.id) AND ((prot.template_path)::text ~~* '%med%svid%smert%'::text))))
959 LEFT JOIN LATERAL ( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS estimated_death_dt
960 FROM (ehr_protocol_query_result r
961 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'EstimatedDeathDate'::text) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
962 ORDER BY r.id DESC
963 LIMIT 1) estimated_death_dt ON (true))
964 JOIN LATERAL ( SELECT COALESCE((( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS to_date
965 FROM (ehr_protocol_query_result r
966 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = ANY (ARRAY[('PerinatalCertificateDeathDate'::character varying)::text, ('CertificateDeathDate'::character varying)::text, ('DeathDateProtocol'::character varying)::text])) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
967 ORDER BY r.id DESC
968 LIMIT 1))::timestamp without time zone, w.death_dt, (estimated_death_dt.estimated_death_dt)::timestamp without time zone, '1900-01-01 00:00:00'::timestamp without time zone) AS death_dt) death_dt ON (true))
969 JOIN LATERAL ( SELECT true AS bool
970 FROM pci_patient_reg reg_1
971 WHERE ((reg_1.patient_id = w.individual_id) AND ((reg_1.clinic_id = w.sr_clinic_id) AND (death_dt.death_dt >= reg_1.reg_dt) AND (death_dt.death_dt <= reg_1.unreg_dt)))
972 LIMIT 1) reg ON (true))
973 LEFT JOIN LATERAL ( SELECT r.value
974 FROM (ehr_protocol_query_result r
975 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'death_circumstances'::text) AND (r.protocol_id = prot.id))))
976 ORDER BY r.id DESC
977 LIMIT 1) deadth_home1 ON (true))
978 LEFT JOIN LATERAL ( SELECT r.value
979 FROM (ehr_protocol_query_result r
980 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'newborn_death_place'::text) AND (r.protocol_id = prot.id))))
981 WHERE ((prot.template_path)::text ~~ '%med%svid$perin$smert%'::text)
982 ORDER BY r.id DESC
983 LIMIT 1) deadth_home2 ON (true))
984 JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(death_dt.death_dt, w.death_dt))::timestamp with time zone, (w.birth_dt)::timestamp with time zone)))::integer AS age
985 LIMIT 1) dd_age ON (true))
986 JOIN analytics.v_pgg_range_age_a dd_ra ON (((dd_age.age <@ dd_ra.range_age) AND (dd_ra.gender_id = w.gender_id))))
987 JOIN analytics.v_pgg_range_age_b dd_rb ON (((dd_age.age <@ dd_rb.range_age) AND (dd_rb.gender_id = w.gender_id))))
988 JOIN analytics.v_pgg_range_age_c dd_rc ON (((dd_age.age <@ dd_rc.range_age) AND (dd_rc.gender_id = w.gender_id))))
989 JOIN analytics.cube_days dd_x ON ((dd_x.calendar_date = date_trunc('month'::text, ((COALESCE(death_dt.death_dt, w.death_dt))::date)::timestamp with time zone))))
990 WHERE ((deadth_home1.value = 'at0024'::text) OR (deadth_home2.value = 'дома'::text))
991 GROUP BY w.mc_clinic_id, w.sr_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.funding_id, w.gender_id, w.individual_id, w.address_location_id, dd_ra.id, dd_rb.id, dd_rc.id, dd_x.id, dd_x.year_, dd_x.quarter_of_year, dd_x.month_of_year, w.sr_aud_source_create, w.district_id
992 ), wdeath_pregnant AS (
993 SELECT w.uid,
994 w.mc_clinic_id,
995 w.sr_clinic_id,
996 w.district_id,
997 w.care_level_id,
998 w.care_regimen_id,
999 w.care_providing_form_id,
1000 w.diagnosis_group_id,
1001 w.funding_id,
1002 w.gender_id,
1003 w.individual_id,
1004 w.address_location_id,
1005 dd_ra.id AS dd_range_age_a_id,
1006 dd_rb.id AS dd_range_age_b_id,
1007 dd_rc.id AS dd_range_age_c_id,
1008 dd_x.id AS dd_democube_days_id,
1009 dd_x.year_ AS dd_year_id,
1010 dd_x.quarter_of_year AS dd_quarter_id,
1011 dd_x.month_of_year AS dd_month_id,
1012 w.individual_id AS dd_id,
1013 w.sr_aud_source_create AS dd_aud_source_create,
1014 CASE
1015 WHEN (max(w.mc_doctor_id) IS NULL) THEN max(w.ms_doctor_id)
1016 ELSE NULL::integer
1017 END AS dd_doctor_id,
1018 1
1019 FROM (((((((((((((wset w
1020 JOIN md_srv_protocol p ON ((p.srv_rendered_id = w.sr_id)))
1021 JOIN md_ehr_protocol prot ON (((p.protocol_id = prot.id) AND ((prot.template_path)::text ~~* '%med%svid%smert%'::text))))
1022 LEFT JOIN LATERAL ( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS estimated_death_dt
1023 FROM (ehr_protocol_query_result r
1024 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'EstimatedDeathDate'::text) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
1025 ORDER BY r.id DESC
1026 LIMIT 1) estimated_death_dt ON (true))
1027 JOIN LATERAL ( SELECT COALESCE((( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS to_date
1028 FROM (ehr_protocol_query_result r
1029 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = ANY (ARRAY[('PerinatalCertificateDeathDate'::character varying)::text, ('CertificateDeathDate'::character varying)::text, ('DeathDateProtocol'::character varying)::text])) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
1030 ORDER BY r.id DESC
1031 LIMIT 1))::timestamp without time zone, w.death_dt, (estimated_death_dt.estimated_death_dt)::timestamp without time zone, '1900-01-01 00:00:00'::timestamp without time zone) AS death_dt) death_dt ON (true))
1032 JOIN LATERAL ( SELECT true AS bool
1033 FROM pci_patient_reg reg_1
1034 WHERE ((reg_1.patient_id = w.individual_id) AND ((reg_1.clinic_id = w.sr_clinic_id) AND (death_dt.death_dt >= reg_1.reg_dt) AND (death_dt.death_dt <= reg_1.unreg_dt)))
1035 LIMIT 1) reg ON (true))
1036 LEFT JOIN LATERAL ( SELECT r.value
1037 FROM (ehr_protocol_query_result r
1038 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'newborn_death_place'::text) AND (r.protocol_id = prot.id))))
1039 ORDER BY r.id DESC
1040 LIMIT 1) deadth_pregnant ON (true))
1041 LEFT JOIN LATERAL ( SELECT r.value
1042 FROM (ehr_protocol_query_result r
1043 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'PregnantDeath'::text) AND (r.protocol_id = prot.id))))
1044 ORDER BY r.id DESC
1045 LIMIT 1) deadth_pregnant2 ON (true))
1046 JOIN LATERAL ( SELECT true AS bool
1047 WHERE ((deadth_pregnant.value = 'at0004'::text) OR (deadth_pregnant2.value = 'в процессе родов'::text))) pregnant ON (true))
1048 JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(death_dt.death_dt, w.death_dt))::timestamp with time zone, (w.birth_dt)::timestamp with time zone)))::integer AS age
1049 LIMIT 1) dd_age ON (true))
1050 JOIN analytics.v_pgg_range_age_a dd_ra ON (((dd_age.age <@ dd_ra.range_age) AND (dd_ra.gender_id = w.gender_id))))
1051 JOIN analytics.v_pgg_range_age_b dd_rb ON (((dd_age.age <@ dd_rb.range_age) AND (dd_rb.gender_id = w.gender_id))))
1052 JOIN analytics.v_pgg_range_age_c dd_rc ON (((dd_age.age <@ dd_rc.range_age) AND (dd_rc.gender_id = w.gender_id))))
1053 JOIN analytics.cube_days dd_x ON ((dd_x.calendar_date = date_trunc('month'::text, ((COALESCE(death_dt.death_dt, w.death_dt))::date)::timestamp with time zone))))
1054 GROUP BY w.uid, w.mc_clinic_id, w.sr_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.funding_id, w.gender_id, w.individual_id, w.address_location_id, dd_ra.id, dd_rb.id, dd_rc.id, dd_x.id, dd_x.year_, dd_x.quarter_of_year, dd_x.month_of_year, deadth_pregnant.value, w.sr_aud_source_create, w.district_id
1055 ), wdeath_child AS (
1056 SELECT w.mc_clinic_id,
1057 w.sr_clinic_id,
1058 w.district_id,
1059 w.care_level_id,
1060 w.care_regimen_id,
1061 w.care_providing_form_id,
1062 w.diagnosis_group_id,
1063 w.funding_id,
1064 w.gender_id,
1065 w.individual_id,
1066 w.address_location_id,
1067 dd_ra.id AS dd_range_age_a_id,
1068 dd_rb.id AS dd_range_age_b_id,
1069 dd_rc.id AS dd_range_age_c_id,
1070 dd_x.id AS dd_democube_days_id,
1071 dd_x.year_ AS dd_year_id,
1072 dd_x.quarter_of_year AS dd_quarter_id,
1073 dd_x.month_of_year AS dd_month_id,
1074 w.individual_id AS dd_id,
1075 w.sr_aud_source_create AS dd_aud_source_create,
1076 CASE
1077 WHEN (max(w.mc_doctor_id) IS NULL) THEN max(w.ms_doctor_id)
1078 ELSE NULL::integer
1079 END AS dd_doctor_id,
1080 1
1081 FROM (((((((((wset w
1082 JOIN md_srv_protocol p ON ((p.srv_rendered_id = w.sr_id)))
1083 JOIN md_ehr_protocol prot ON (((p.protocol_id = prot.id) AND ((prot.template_path)::text ~~* '%med%svid%smert%'::text))))
1084 LEFT JOIN LATERAL ( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS estimated_death_dt
1085 FROM (ehr_protocol_query_result r
1086 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = 'EstimatedDeathDate'::text) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
1087 ORDER BY r.id DESC
1088 LIMIT 1) estimated_death_dt ON (true))
1089 JOIN LATERAL ( SELECT COALESCE((( SELECT to_date(r.value, 'DD.MM.YYYY'::text) AS to_date
1090 FROM (ehr_protocol_query_result r
1091 JOIN ehr_protocol_query q ON (((r.query_id = q.id) AND ((q.code)::text = ANY (ARRAY[('PerinatalCertificateDeathDate'::character varying)::text, ('CertificateDeathDate'::character varying)::text, ('DeathDateProtocol'::character varying)::text])) AND (r.protocol_id = prot.id) AND (r.value ~~ '__.__.20__'::text))))
1092 ORDER BY r.id DESC
1093 LIMIT 1))::timestamp without time zone, w.death_dt, (estimated_death_dt.estimated_death_dt)::timestamp without time zone, '1900-01-01 00:00:00'::timestamp without time zone) AS death_dt) death_dt ON (true))
1094 JOIN LATERAL ( SELECT (date_part('year'::text, age((COALESCE(death_dt.death_dt, w.death_dt))::timestamp with time zone, (w.birth_dt)::timestamp with time zone)))::integer AS age
1095 LIMIT 1) dd_age ON ((dd_age.age <= 1)))
1096 JOIN analytics.v_pgg_range_age_a dd_ra ON (((dd_age.age <@ dd_ra.range_age) AND (dd_ra.gender_id = w.gender_id))))
1097 JOIN analytics.v_pgg_range_age_b dd_rb ON (((dd_age.age <@ dd_rb.range_age) AND (dd_rb.gender_id = w.gender_id))))
1098 JOIN analytics.v_pgg_range_age_c dd_rc ON (((dd_age.age <@ dd_rc.range_age) AND (dd_rc.gender_id = w.gender_id))))
1099 JOIN analytics.cube_days dd_x ON ((dd_x.calendar_date = date_trunc('month'::text, ((COALESCE(death_dt.death_dt, w.death_dt))::date)::timestamp with time zone))))
1100 GROUP BY w.mc_clinic_id, w.sr_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.funding_id, w.gender_id, w.individual_id, w.address_location_id, dd_ra.id, dd_rb.id, dd_rc.id, dd_x.id, dd_x.year_, dd_x.quarter_of_year, dd_x.month_of_year, w.sr_aud_source_create, w.district_id
1101 )
1102 SELECT t.clinic_id,
1103 t.mc_clinic_id,
1104 t.care_level_id,
1105 t.district_id,
1106 t.care_regimen_id,
1107 t.care_providing_form_id,
1108 t.diagnosis_group_id,
1109 t.address_location_id,
1110 t.funding_id,
1111 t.gender_id,
1112 t.range_age_a_id,
1113 t.range_age_b_id,
1114 t.range_age_c_id,
1115 t.democube_days_id,
1116 t.year_id,
1117 t.quarter_id,
1118 t.month_id,
1119 t.aud_source_create,
1120 t.doctor_id,
1121 COALESCE(pgg_asc.id, (5)::bigint) AS source_create_id,
1122 sum(t.mc_count) AS mc_count,
1123 sum(t.ms_count) AS ms_count,
1124 sum(t.sr_count) AS sr_count,
1125 sum(t.dd_count) AS dd_count,
1126 sum(t.dh_count) AS dh_count,
1127 sum(t.dp_count) AS dp_count,
1128 sum(t.dc_count) AS dc_count
1129 FROM (( SELECT w.mc_clinic_id AS clinic_id,
1130 w.mc_clinic_id,
1131 w.district_id,
1132 w.care_level_id,
1133 w.care_regimen_id,
1134 w.care_providing_form_id,
1135 w.diagnosis_group_id,
1136 w.address_location_id,
1137 w.funding_id,
1138 w.gender_id,
1139 w.mc_range_age_a_id AS range_age_a_id,
1140 w.mc_range_age_b_id AS range_age_b_id,
1141 w.mc_range_age_c_id AS range_age_c_id,
1142 w.mc_democube_days_id AS democube_days_id,
1143 w.mc_year_id AS year_id,
1144 w.mc_quarter_id AS quarter_id,
1145 w.mc_month_id AS month_id,
1146 w.mc_aud_source_create AS aud_source_create,
1147 CASE
1148 WHEN (max(w.mc_doctor_id) IS NULL) THEN max(w.ms_doctor_id)
1149 ELSE NULL::integer
1150 END AS doctor_id,
1151 count(DISTINCT w.mc_id) AS mc_count,
1152 0 AS ms_count,
1153 0 AS sr_count,
1154 0 AS dd_count,
1155 0 AS dh_count,
1156 0 AS dp_count,
1157 0 AS dc_count
1158 FROM wset w
1159 GROUP BY w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.mc_range_age_a_id, w.mc_range_age_b_id, w.mc_range_age_c_id, w.mc_democube_days_id, w.mc_year_id, w.mc_quarter_id, w.mc_month_id, w.mc_aud_source_create, w.district_id
1160 UNION ALL
1161 SELECT w.sr_clinic_id AS clinic_id,
1162 w.mc_clinic_id,
1163 w.district_id,
1164 w.care_level_id,
1165 w.care_regimen_id,
1166 w.care_providing_form_id,
1167 w.diagnosis_group_id,
1168 w.address_location_id,
1169 w.funding_id,
1170 w.gender_id,
1171 w.ms_range_age_a_id AS range_age_a_id,
1172 w.ms_range_age_b_id AS range_age_b_id,
1173 w.ms_range_age_c_id AS range_age_c_id,
1174 w.ms_democube_days_id AS democube_days_id,
1175 w.ms_year_id,
1176 w.ms_quarter_id,
1177 w.ms_month_id,
1178 w.ms_aud_source_create AS aud_source_create,
1179 COALESCE(max(w.ms_doctor_id), max(w.mc_doctor_id)) AS doctor_id,
1180 0 AS mc_count,
1181 count(DISTINCT w.ms_id) AS ms_count,
1182 0 AS sr_count,
1183 0 AS dd_count,
1184 0 AS dh_count,
1185 0 AS dp_count,
1186 0 AS dc_count
1187 FROM wset w
1188 WHERE (w.ms_democube_days_id IS NOT NULL)
1189 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.ms_range_age_a_id, w.ms_range_age_b_id, w.ms_range_age_c_id, w.ms_democube_days_id, w.ms_year_id, w.ms_quarter_id, w.ms_month_id, w.ms_aud_source_create, w.district_id
1190 UNION ALL
1191 SELECT w.sr_clinic_id AS clinic_id,
1192 w.mc_clinic_id,
1193 w.district_id,
1194 w.care_level_id,
1195 w.care_regimen_id,
1196 w.care_providing_form_id,
1197 w.diagnosis_group_id,
1198 w.address_location_id,
1199 w.funding_id,
1200 w.gender_id,
1201 w.sr_range_age_a_id AS range_age_a_id,
1202 w.sr_range_age_b_id AS range_age_b_id,
1203 w.sr_range_age_c_id AS range_age_c_id,
1204 w.sr_democube_days_id AS democube_days_id,
1205 w.sr_year_id,
1206 w.sr_quarter_id,
1207 w.sr_month_id,
1208 w.sr_aud_source_create AS aud_source_create,
1209 COALESCE(max(w.sr_doctor_id), max(w.mc_doctor_id)) AS doctor_id,
1210 0 AS mc_count,
1211 0 AS ms_count,
1212 count(DISTINCT w.sr_id) AS sr_count,
1213 0 AS dd_count,
1214 0 AS dh_count,
1215 0 AS dp_count,
1216 0 AS dc_count
1217 FROM wset w
1218 WHERE (w.sr_democube_days_id IS NOT NULL)
1219 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.sr_range_age_a_id, w.sr_range_age_b_id, w.sr_range_age_c_id, w.sr_democube_days_id, w.sr_year_id, w.sr_quarter_id, w.sr_month_id, w.sr_aud_source_create, w.district_id
1220 UNION ALL
1221 SELECT w.sr_clinic_id AS clinic_id,
1222 w.mc_clinic_id,
1223 w.district_id,
1224 w.care_level_id,
1225 w.care_regimen_id,
1226 w.care_providing_form_id,
1227 w.diagnosis_group_id,
1228 w.address_location_id,
1229 w.funding_id,
1230 w.gender_id,
1231 w.dd_range_age_a_id AS range_age_a_id,
1232 w.dd_range_age_b_id AS range_age_b_id,
1233 w.dd_range_age_c_id AS range_age_c_id,
1234 w.dd_democube_days_id AS democube_days_id,
1235 w.dd_year_id,
1236 w.dd_quarter_id,
1237 w.dd_month_id,
1238 w.dd_aud_source_create AS aud_source_create,
1239 max(w.dd_doctor_id) AS doctor_id,
1240 0 AS mc_count,
1241 0 AS ms_count,
1242 0 AS sr_count,
1243 count(DISTINCT w.individual_id) AS dd_count,
1244 0 AS dh_count,
1245 0 AS dp_count,
1246 0 AS dc_count
1247 FROM wdeath_all w
1248 WHERE (w.dd_democube_days_id IS NOT NULL)
1249 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.dd_range_age_a_id, w.dd_range_age_b_id, w.dd_range_age_c_id, w.dd_democube_days_id, w.dd_year_id, w.dd_quarter_id, w.dd_month_id, w.dd_aud_source_create, w.district_id
1250 UNION ALL
1251 SELECT w.sr_clinic_id AS clinic_id,
1252 w.mc_clinic_id,
1253 w.district_id,
1254 w.care_level_id,
1255 w.care_regimen_id,
1256 w.care_providing_form_id,
1257 w.diagnosis_group_id,
1258 w.address_location_id,
1259 w.funding_id,
1260 w.gender_id,
1261 w.dd_range_age_a_id AS range_age_a_id,
1262 w.dd_range_age_b_id AS range_age_b_id,
1263 w.dd_range_age_c_id AS range_age_c_id,
1264 w.dd_democube_days_id AS democube_days_id,
1265 w.dd_year_id,
1266 w.dd_quarter_id,
1267 w.dd_month_id,
1268 w.dd_aud_source_create AS aud_source_create,
1269 max(w.dd_doctor_id) AS doctor_id,
1270 0 AS mc_count,
1271 0 AS ms_count,
1272 0 AS sr_count,
1273 0 AS dd_count,
1274 count(DISTINCT w.individual_id) AS dh_count,
1275 0 AS dp_count,
1276 0 AS dc_count
1277 FROM wdeath_home w
1278 WHERE (w.dd_democube_days_id IS NOT NULL)
1279 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.dd_range_age_a_id, w.dd_range_age_b_id, w.dd_range_age_c_id, w.dd_democube_days_id, w.dd_year_id, w.dd_quarter_id, w.dd_month_id, w.dd_aud_source_create, w.district_id
1280 UNION ALL
1281 SELECT w.sr_clinic_id AS clinic_id,
1282 w.mc_clinic_id,
1283 w.district_id,
1284 w.care_level_id,
1285 w.care_regimen_id,
1286 w.care_providing_form_id,
1287 w.diagnosis_group_id,
1288 w.address_location_id,
1289 w.funding_id,
1290 w.gender_id,
1291 w.dd_range_age_a_id AS range_age_a_id,
1292 w.dd_range_age_b_id AS range_age_b_id,
1293 w.dd_range_age_c_id AS range_age_c_id,
1294 w.dd_democube_days_id AS democube_days_id,
1295 w.dd_year_id,
1296 w.dd_quarter_id,
1297 w.dd_month_id,
1298 w.dd_aud_source_create AS aud_source_create,
1299 max(w.dd_doctor_id) AS doctor_id,
1300 0 AS mc_count,
1301 0 AS ms_count,
1302 0 AS sr_count,
1303 0 AS dd_count,
1304 0 AS dh_count,
1305 count(DISTINCT w.individual_id) AS dp_count,
1306 0 AS dc_count
1307 FROM wdeath_pregnant w
1308 WHERE (w.dd_democube_days_id IS NOT NULL)
1309 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.dd_range_age_a_id, w.dd_range_age_b_id, w.dd_range_age_c_id, w.dd_democube_days_id, w.dd_year_id, w.dd_quarter_id, w.dd_month_id, w.dd_aud_source_create, w.district_id
1310 UNION ALL
1311 SELECT w.sr_clinic_id AS clinic_id,
1312 w.mc_clinic_id,
1313 w.district_id,
1314 w.care_level_id,
1315 w.care_regimen_id,
1316 w.care_providing_form_id,
1317 w.diagnosis_group_id,
1318 w.address_location_id,
1319 w.funding_id,
1320 w.gender_id,
1321 w.dd_range_age_a_id AS range_age_a_id,
1322 w.dd_range_age_b_id AS range_age_b_id,
1323 w.dd_range_age_c_id AS range_age_c_id,
1324 w.dd_democube_days_id AS democube_days_id,
1325 w.dd_year_id,
1326 w.dd_quarter_id,
1327 w.dd_month_id,
1328 w.dd_aud_source_create AS aud_source_create,
1329 COALESCE(max(w.dd_doctor_id), 0) AS doctor_id,
1330 0 AS mc_count,
1331 0 AS ms_count,
1332 0 AS sr_count,
1333 0 AS dd_count,
1334 0 AS dh_count,
1335 0 AS dp_count,
1336 count(DISTINCT w.individual_id) AS dc_count
1337 FROM wdeath_child w
1338 WHERE (w.dd_democube_days_id IS NOT NULL)
1339 GROUP BY w.sr_clinic_id, w.mc_clinic_id, w.care_level_id, w.care_regimen_id, w.care_providing_form_id, w.diagnosis_group_id, w.address_location_id, w.funding_id, w.gender_id, w.dd_range_age_a_id, w.dd_range_age_b_id, w.dd_range_age_c_id, w.dd_democube_days_id, w.dd_year_id, w.dd_quarter_id, w.dd_month_id, w.dd_aud_source_create, w.district_id) t
1340 LEFT JOIN LATERAL ( SELECT pgg_asc_1.id
1341 FROM analytics.v_pgg_create_source pgg_asc_1
1342 WHERE ((t.aud_source_create)::text = ANY (pgg_asc_1.create_source_code))
1343 LIMIT 1) pgg_asc ON (true))
1344 WHERE (t.democube_days_id IS NOT NULL)
1345 GROUP BY t.clinic_id, t.mc_clinic_id, t.care_level_id, t.care_regimen_id, t.care_providing_form_id, t.diagnosis_group_id, t.address_location_id, t.funding_id, t.gender_id, t.range_age_a_id, t.range_age_b_id, t.range_age_c_id, t.democube_days_id, t.year_id, t.quarter_id, t.month_id, t.aud_source_create, COALESCE(pgg_asc.id, (5)::bigint), t.doctor_id, t.district_id;
1346
1347ALTER TABLE "analytics"."v_pgg_main_cub3" OWNER TO "app_group_master";
1348
1349CREATE VIEW "analytics"."v_pgg_main_cub_mv3" AS SELECT mv_pgg_main_cub3.clinic_id,
1350 mv_pgg_main_cub3.mc_clinic_id,
1351 mv_pgg_main_cub3.care_level_id,
1352 mv_pgg_main_cub3.district_id,
1353 mv_pgg_main_cub3.care_regimen_id,
1354 mv_pgg_main_cub3.care_providing_form_id,
1355 mv_pgg_main_cub3.diagnosis_group_id,
1356 mv_pgg_main_cub3.address_location_id,
1357 mv_pgg_main_cub3.funding_id,
1358 mv_pgg_main_cub3.gender_id,
1359 mv_pgg_main_cub3.range_age_a_id,
1360 mv_pgg_main_cub3.range_age_b_id,
1361 mv_pgg_main_cub3.range_age_c_id,
1362 mv_pgg_main_cub3.democube_days_id,
1363 mv_pgg_main_cub3.year_id,
1364 mv_pgg_main_cub3.quarter_id,
1365 mv_pgg_main_cub3.month_id,
1366 mv_pgg_main_cub3.aud_source_create,
1367 mv_pgg_main_cub3.doctor_id,
1368 mv_pgg_main_cub3.source_create_id,
1369 mv_pgg_main_cub3.mc_count,
1370 mv_pgg_main_cub3.ms_count,
1371 mv_pgg_main_cub3.sr_count,
1372 mv_pgg_main_cub3.dd_count,
1373 mv_pgg_main_cub3.dh_count,
1374 mv_pgg_main_cub3.dp_count,
1375 mv_pgg_main_cub3.dc_count
1376 FROM analytics.mv_pgg_main_cub3;
1377
1378ALTER TABLE "analytics"."v_pgg_main_cub_mv3" OWNER TO "app_group_master";