1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
-- ==============================================================
-- GNUmed database schema change script
--
-- License: GPL v2 or later
-- Author: karsten.hilbert@gmx.net
--
-- ==============================================================
-- $Id: v11-dem-v_provider_inbox.sql,v 1.2 2009-07-15 12:27:22 ncq Exp $
-- $Revision: 1.2 $
-- --------------------------------------------------------------
\set ON_ERROR_STOP 1
-- --------------------------------------------------------------
\unset ON_ERROR_STOP
drop view dem.v_provider_inbox cascade;
\set ON_ERROR_STOP 1
create view dem.v_provider_inbox as
select
pi.modified_when as received_when,
(select short_alias from dem.staff where dem.staff.pk = pi.fk_staff) as provider,
pi.importance,
vit.category,
vit.l10n_category,
vit.type,
vit.l10n_type,
pi.comment,
pi.ufk_context as pk_context,
pi.data as data,
pi.pk as pk_provider_inbox,
pi.fk_staff as pk_staff,
vit.pk_category,
pi.fk_inbox_item_type as pk_type
from
dem.provider_inbox pi,
dem.v_inbox_item_type vit
where
pi.fk_inbox_item_type = vit.pk_type
union
select
now() as received_when,
(select short_alias from dem.staff where dem.staff.pk = vo4dnd.pk_intended_reviewer)
as provider,
0 as importance,
'clinical'
as category,
_('clinical')
as l10n_category,
'review docs'
as type,
_('review docs')
as l10n_type,
(select _('unreviewed documents for patient') || ' ['
|| dn.lastnames || ', '
|| dn.firstnames || ']'
from dem.names dn
where
dn.id_identity = vo4dnd.pk_patient
and
dn.active is True
)
as comment,
vo4dnd.pk_patient
as pk_context,
NULL
as data,
NULL
as pk_provider_inbox,
vo4dnd.pk_intended_reviewer
as pk_staff,
(select pk_category from dem.v_inbox_item_type where type = 'review docs')
as pk_category,
(select pk_type from dem.v_inbox_item_type where type = 'review docs')
as pk_type
from
blobs.v_obj4doc_no_data vo4dnd
where
reviewed is False
union
select
now() as received_when,
(select short_alias from dem.staff where dem.staff.pk = vtr.pk_intended_reviewer)
as provider,
0 as importance,
'clinical'
as category,
_('clinical')
as l10n_category,
'review results'
as type,
_('review results')
as l10n_type,
(select _('unreviewed results for patient') || ' ['
|| dn.lastnames || ', '
|| dn.firstnames || ']'
from dem.names dn
where
dn.id_identity = vtr.pk_patient
and
dn.active is True
)
as comment,
vtr.pk_patient
as pk_context,
NULL
as data,
NULL
as pk_provider_inbox,
vtr.pk_intended_reviewer
as pk_staff,
(select pk_category from dem.v_inbox_item_type where type = 'review results')
as pk_category,
(select pk_type from dem.v_inbox_item_type where type = 'review results')
as pk_type
from
clin.v_test_results vtr
where
reviewed is False
;
comment on view dem.v_provider_inbox is
'Denormalized messages for the providers.
Using UNION makes sure we get the right level of uniqueness.';
grant select on dem.v_provider_inbox to group "gm-doctors";
-- --------------------------------------------------------------
select gm.log_script_insertion('$RCSfile: v11-dem-v_provider_inbox.sql,v $', '$Revision: 1.2 $');
-- ==============================================================
-- $Log: v11-dem-v_provider_inbox.sql,v $
-- Revision 1.2 2009-07-15 12:27:22 ncq
-- - rewrite to not access v_basic_person
--
-- Revision 1.1 2009/05/24 16:25:43 ncq
-- - new
--
--
|
