9p7HuyFQ

1DROP TABLE IF EXISTS tmp_report_en0002;
2
3CREATE LOCAL TEMPORARY TABLE IF NOT EXISTS tmp_report_en0002 (
4  id uuid,
5  total_connect_patient_id uuid,
6  total_in_program_patient_id uuid,
7  total_is_visit_patient_id uuid,
8  total_questionnaire_id uuid,
9  total_is_take_place_questionnaire_id uuid,
10  total_call_questionnaire_id uuid,
11  total_not_in_program_patient_id uuid,
12  total_is_died_patient_id uuid,
13  total_is_eliminated_patient_id uuid,
14  total_is_not_found_patient_id uuid,
15  total_is_refusing_patient_id uuid,
16  total_is_fear_status_patient_id uuid,
17  total_is_an_incon_services_patient_id uuid,
18  total_is_does_not_receive_services_patient_id uuid,
19  total_is_going_another_region_patient_id uuid,
20  total_is_other_patient_id uuid
21);
22
23/* Кол-во прикрепленных пациентов */
24INSERT INTO tmp_report_en0002 (id, total_connect_patient_id)
25SELECT
26  dn.id,
27  dp.id
28FROM data_user AS dn
29  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
30                                    dpn.row_status = 10 AND
31                                    dpn.nurse_date = (SELECT max(_dpn.nurse_date)
32                                                      FROM data_patient_nurse AS _dpn
33                                                      WHERE _dpn.patient_id = dpn.patient_id AND
34                                                            _dpn.nurse_id = dpn.nurse_id AND
35                                                            _dpn.row_status = 10)
36  JOIN data_patient AS dp ON dpn.patient_id = dp.id
37  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
38                                   dor.row_status = 10 AND
39                                   (:org IS NULL OR dor.id = :org) AND
40                                   (:region IS NULL OR dor.address_region = :region) AND 
41                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
42WHERE dn.row_status = 10;
43
44/* Количество пациентов, от которых получено информированное согласие */
45INSERT INTO tmp_report_en0002 (id, total_in_program_patient_id)
46SELECT
47  dn.id,
48  dp.id
49FROM data_user AS dn
50  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
51                                    dpn.row_status = 10 AND
52                                    dpn.nurse_date = (SELECT max(_dpn.nurse_date)
53                                                      FROM data_patient_nurse AS _dpn
54                                                      WHERE _dpn.patient_id = dpn.patient_id AND
55                                                            _dpn.nurse_id = dpn.nurse_id AND
56                                                            _dpn.row_status = 10)
57  JOIN data_patient AS dp ON dpn.patient_id = dp.id AND
58                             dp.row_status = 10 AND
59                             dp.is_programme = TRUE
60  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
61                                   dor.row_status = 10 AND
62                                   (:org IS NULL OR dor.id = :org) AND
63                                   (:region IS NULL OR dor.address_region = :region) AND 
64                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
65                             
66WHERE dn.row_status = 10;
67
68/* Кол-во пациентов, к которым осуществлен хотя бы 1 визит за отчетный период */
69INSERT INTO tmp_report_en0002 (id, total_is_visit_patient_id)
70SELECT DISTINCT
71  dn.id,
72  dpn.patient_id
73FROM data_user AS dn
74  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
75                                    dpn.row_status = 10 AND
76                                    dpn.nurse_date = (SELECT max(_dpn.nurse_date)
77                                                      FROM data_patient_nurse AS _dpn
78                                                      WHERE _dpn.patient_id = dpn.patient_id AND
79                                                            _dpn.nurse_id = dpn.nurse_id AND
80                                                            _dpn.row_status = 10)
81  JOIN data_questionnaire AS dq ON dpn.patient_id = dq.patient_id AND
82                                   dq.row_status = 10 AND
83                                   dq.type_questionnaire = 10 AND
84                                   dq.visit_datetime BETWEEN :period_begin AND :period_end
85  JOIN data_patient AS dp ON dpn.patient_id = dp.id AND
86                             dp.row_status = 10
87  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
88                                   dor.row_status = 10 AND
89                                   (:org IS NULL OR dor.id = :org) AND
90                                   (:region IS NULL OR dor.address_region = :region) AND 
91                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
92WHERE dn.row_status = 10;
93
94/* Количество визитов */
95INSERT INTO tmp_report_en0002 (id, total_questionnaire_id)
96SELECT
97  dn.id,
98  dq.id
99FROM data_user AS dn
100  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
101                                    dpn.row_status = 10 AND
102                                    dpn.nurse_date = (SELECT max(_dpn.nurse_date)
103                                                      FROM data_patient_nurse AS _dpn
104                                                      WHERE _dpn.patient_id = dpn.patient_id AND
105                                                            _dpn.nurse_id = dpn.nurse_id AND
106                                                            _dpn.row_status = 10)
107  JOIN data_questionnaire AS dq ON dpn.patient_id = dq.patient_id AND
108                                   dq.row_status = 10 AND
109                                   dq.type_questionnaire = 10 AND
110                                   dq.visit_datetime BETWEEN :period_begin AND :period_end
111  JOIN data_patient AS dp ON dpn.patient_id = dp.id AND
112                             dp.row_status = 10
113  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
114                                   dor.row_status = 10 AND
115                                   (:org IS NULL OR dor.id = :org) AND
116                                   (:region IS NULL OR dor.address_region = :region) AND 
117                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
118WHERE dn.row_status = 10;
119
120/* В т.ч. состоявшиеся */
121INSERT INTO tmp_report_en0002 (id, total_is_take_place_questionnaire_id)
122SELECT
123  dn.id,
124  dq.id
125FROM data_user AS dn
126  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
127                                    dpn.row_status = 10 AND
128                                    dpn.nurse_date = (SELECT max(_dpn.nurse_date)
129                                                      FROM data_patient_nurse AS _dpn
130                                                      WHERE _dpn.patient_id = dpn.patient_id AND
131                                                            _dpn.nurse_id = dpn.nurse_id AND
132                                                            _dpn.row_status = 10)
133  JOIN data_questionnaire AS dq ON dpn.patient_id = dq.patient_id AND
134                                   dq.row_status = 10 AND
135                                   dq.type_questionnaire = 10 AND
136                                   dq.is_visit_not_take_place = FALSE AND
137                                   dq.visit_datetime BETWEEN :period_begin AND :period_end
138  JOIN data_patient AS dp ON dpn.patient_id = dp.id AND
139                             dp.row_status = 10
140  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
141                                   dor.row_status = 10 AND
142                                   (:org IS NULL OR dor.id = :org) AND
143                                   (:region IS NULL OR dor.address_region = :region) AND 
144                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
145WHERE dn.row_status = 10;
146
147/* Количество звонков за отчетный период */
148INSERT INTO tmp_report_en0002 (id, total_call_questionnaire_id)
149SELECT
150  dn.id,
151  dq.id
152FROM data_user AS dn
153  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
154                                    dpn.row_status = 10 AND
155                                    dpn.nurse_date = (SELECT max(_dpn.nurse_date)
156                                                      FROM data_patient_nurse AS _dpn
157                                                      WHERE _dpn.patient_id = dpn.patient_id AND
158                                                            _dpn.nurse_id = dpn.nurse_id AND
159                                                            _dpn.row_status = 10)
160  JOIN data_questionnaire AS dq ON dpn.patient_id = dq.patient_id AND
161                                   dq.row_status = 10 AND
162                                   dq.type_questionnaire = 20 AND
163                                   dq.visit_datetime BETWEEN :period_begin AND :period_end
164  JOIN data_patient AS dp ON dpn.patient_id = dp.id AND
165                             dp.row_status = 10
166  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
167                                   dor.row_status = 10 AND
168                                   (:org IS NULL OR dor.id = :org) AND
169                                   (:region IS NULL OR dor.address_region = :region) AND 
170                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
171WHERE dn.row_status = 10;
172
173/* Кол-во пациентов, от которых не получены информированные согласия */
174INSERT INTO tmp_report_en0002 (id, total_not_in_program_patient_id)
175SELECT
176  dn.id,
177  dp.id
178FROM data_user AS dn
179  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
180                                    dpn.row_status = 10 AND
181                                    dpn.nurse_date = (SELECT max(_dpn.nurse_date)
182                                                      FROM data_patient_nurse AS _dpn
183                                                      WHERE _dpn.patient_id = dpn.patient_id AND
184                                                            _dpn.nurse_id = dpn.nurse_id AND
185                                                            _dpn.row_status = 10)
186  JOIN data_patient AS dp ON dpn.patient_id = dp.id AND
187                             dp.row_status = 10 AND
188                             dp.is_programme = FALSE
189  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
190                                   dor.row_status = 10 AND
191                                   (:org IS NULL OR dor.id = :org) AND
192                                   (:region IS NULL OR dor.address_region = :region) AND 
193                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
194WHERE dn.row_status = 10;
195
196/* Умер */
197INSERT INTO tmp_report_en0002 (id, total_is_died_patient_id)
198SELECT
199  dn.id,
200  dp.id
201FROM data_user AS dn
202  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
203                                    dpn.row_status = 10
204  JOIN data_patient AS dp ON dp.id = dpn.patient_id AND
205                             dp.is_programme = FALSE AND
206                             dp.reasons_not_programme = 10 AND
207                             dp.row_status = 10
208  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
209                                   dor.row_status = 10 AND
210                                   (:org IS NULL OR dor.id = :org) AND
211                                   (:region IS NULL OR dor.address_region = :region) AND 
212                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
213WHERE dn.row_status = 10;
214
215/* Выбыл */
216INSERT INTO tmp_report_en0002 (id, total_is_eliminated_patient_id)
217SELECT
218  dn.id,
219  dp.id
220FROM data_user AS dn
221  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
222                                    dpn.row_status = 10
223  JOIN data_patient AS dp ON dp.id = dpn.patient_id AND
224                             dp.is_programme = FALSE AND
225                             dp.reasons_not_programme = 20 AND
226                             dp.row_status = 10
227  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
228                                   dor.row_status = 10 AND
229                                   (:org IS NULL OR dor.id = :org) AND
230                                   (:region IS NULL OR dor.address_region = :region) AND 
231                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
232WHERE dn.row_status = 10;
233
234/* Не найден */
235INSERT INTO tmp_report_en0002 (id, total_is_not_found_patient_id)
236SELECT
237  dn.id,
238  dp.id
239FROM data_user AS dn
240  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
241                                    dpn.row_status = 10
242  JOIN data_patient AS dp ON dp.id = dpn.patient_id AND
243                             dp.is_programme = FALSE AND
244                             dp.reasons_not_programme = 30 AND
245                             dp.row_status = 10
246  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
247                                   dor.row_status = 10 AND
248                                   (:org IS NULL OR dor.id = :org) AND
249                                   (:region IS NULL OR dor.address_region = :region) AND 
250                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
251WHERE dn.row_status = 10;
252
253/* Отказ */
254INSERT INTO tmp_report_en0002 (id, total_is_refusing_patient_id)
255SELECT
256  dn.id,
257  dp.id
258FROM data_user AS dn
259  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
260                                    dpn.row_status = 10
261  JOIN data_patient AS dp ON dp.id = dpn.patient_id AND
262                             dp.is_programme = FALSE AND
263                             dp.reasons_not_programme = 40 AND
264                             dp.row_status = 10
265  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
266                                   dor.row_status = 10 AND
267                                   (:org IS NULL OR dor.id = :org) AND
268                                   (:region IS NULL OR dor.address_region = :region) AND 
269                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
270WHERE dn.row_status = 10;
271
272/* В т.ч. Боязнь раскрытия статуса */
273INSERT INTO tmp_report_en0002 (id, total_is_fear_status_patient_id)
274SELECT
275  dn.id,
276  dp.id
277FROM data_user AS dn
278  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
279                                    dpn.row_status = 10
280  JOIN data_patient AS dp ON dp.id = dpn.patient_id AND
281                             dp.is_programme = FALSE AND
282                             dp.reasons_not_programme = 40 AND
283                             dp.reasons_failure = 10 AND
284                             dp.row_status = 10
285  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
286                                   dor.row_status = 10 AND
287                                   (:org IS NULL OR dor.id = :org) AND
288                                   (:region IS NULL OR dor.address_region = :region) AND 
289                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
290WHERE dn.row_status = 10;
291
292/* Неудобный способ оказания услуг */
293INSERT INTO tmp_report_en0002 (id, total_is_an_incon_services_patient_id)
294SELECT
295  dn.id,
296  dp.id
297FROM data_user AS dn
298  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
299                                    dpn.row_status = 10
300  JOIN data_patient AS dp ON dp.id = dpn.patient_id AND
301                             dp.is_programme = FALSE AND
302                             dp.reasons_not_programme = 40 AND
303                             dp.reasons_failure = 20 AND
304                             dp.row_status = 10
305  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
306                                   dor.row_status = 10 AND
307                                   (:org IS NULL OR dor.id = :org) AND
308                                   (:region IS NULL OR dor.address_region = :region) AND 
309                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
310WHERE dn.row_status = 10;
311
312/* Не хочет получать услуги */
313INSERT INTO tmp_report_en0002 (id, total_is_does_not_receive_services_patient_id)
314SELECT
315  dn.id,
316  dp.id
317FROM data_user AS dn
318  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
319                                    dpn.row_status = 10
320  JOIN data_patient AS dp ON dp.id = dpn.patient_id AND
321                             dp.is_programme = FALSE AND
322                             dp.reasons_not_programme = 40 AND
323                             dp.reasons_failure = 30 AND
324                             dp.row_status = 10
325  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
326                                   dor.row_status = 10 AND
327                                   (:org IS NULL OR dor.id = :org) AND
328                                   (:region IS NULL OR dor.address_region = :region) AND 
329                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
330WHERE dn.row_status = 10;
331
332/* Собирается уехать в др регион */
333INSERT INTO tmp_report_en0002 (id, total_is_going_another_region_patient_id)
334SELECT
335  dn.id,
336  dp.id
337FROM data_user AS dn
338  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
339                                    dpn.row_status = 10
340  JOIN data_patient AS dp ON dp.id = dpn.patient_id AND
341                             dp.is_programme = FALSE AND
342                             dp.reasons_not_programme = 40 AND
343                             dp.reasons_failure = 40 AND
344                             dp.row_status = 10
345  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
346                                   dor.row_status = 10 AND
347                                   (:org IS NULL OR dor.id = :org) AND
348                                   (:region IS NULL OR dor.address_region = :region) AND 
349                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
350WHERE dn.row_status = 10;
351
352/* Другое */
353INSERT INTO tmp_report_en0002 (id, total_is_other_patient_id)
354SELECT
355  dn.id,
356  dp.id
357FROM data_user AS dn
358  JOIN data_patient_nurse AS dpn ON dpn.nurse_id = dn.id AND
359                                    dpn.row_status = 10
360  JOIN data_patient AS dp ON dp.id = dpn.patient_id AND
361                             dp.is_programme = FALSE AND
362                             dp.reasons_not_programme = 40 AND
363                             dp.reasons_failure = 50 AND
364                             dp.row_status = 10
365  JOIN data_organization AS dor ON dor.id = dp.disp_org_id AND 
366                                   dor.row_status = 10 AND
367                                   (:org IS NULL OR dor.id = :org) AND
368                                   (:region IS NULL OR dor.address_region = :region) AND 
369                                   (:unit_area IS NULL OR dor.address_unit_area = :unit_area)
370WHERE dn.row_status = 10;
371
372SELECT
373  dn.id,
374  dn.first_name,
375  dn.last_name,
376  dn.middle_name,
377  sum(CASE WHEN tmp.total_connect_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_connect_patient,
378  sum(CASE WHEN tmp.total_in_program_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_in_program_patient,
379  sum(CASE WHEN tmp.total_is_visit_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_is_visit_patient,
380  sum(CASE WHEN tmp.total_questionnaire_id NOTNULL THEN 1 ELSE 0 END) AS total_questionnaire,
381  sum(CASE WHEN tmp.total_is_take_place_questionnaire_id NOTNULL THEN 1 ELSE 0 END) AS total_is_take_place_questionnaire,
382  sum(CASE WHEN tmp.total_call_questionnaire_id NOTNULL THEN 1 ELSE 0 END) AS total_call_questionnaire,
383  sum(CASE WHEN tmp.total_not_in_program_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_not_in_program_patient,
384  sum(CASE WHEN tmp.total_is_died_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_is_died_patient,
385  sum(CASE WHEN tmp.total_is_eliminated_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_is_eliminated_patient,
386  sum(CASE WHEN tmp.total_is_not_found_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_is_not_found_patient,
387  sum(CASE WHEN tmp.total_is_refusing_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_is_refusing_patient,
388  sum(CASE WHEN tmp.total_is_fear_status_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_is_fear_status_patient,
389  sum(CASE WHEN tmp.total_is_an_incon_services_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_is_an_incon_services_patient,
390  sum(CASE WHEN tmp.total_is_does_not_receive_services_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_is_does_not_receive_services_patient,
391  sum(CASE WHEN tmp.total_is_going_another_region_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_is_going_another_region_patient,
392  sum(CASE WHEN tmp.total_is_other_patient_id NOTNULL THEN 1 ELSE 0 END) AS total_is_other_patient
393FROM data_user AS dn
394  JOIN tmp_report_en0002 AS tmp ON tmp.id = dn.id
395WHERE dn.role = 10
396GROUP BY dn.id, dn.first_name, dn.last_name, dn.middle_name
397ORDER BY dn.last_name, dn.first_name,  dn.middle_name;