1 CREATE OR REPLACE VIEW activity_vw AS
3 agl.group_id, 'trackeropen'::text AS section, agl.group_artifact_id AS ref_id,
4 a.artifact_id as subref_id, a.summary as description, a.open_date AS activity_date, u.user_id, u.user_name, u.realname
5 FROM artifact_group_list agl JOIN artifact a using (group_artifact_id),
6 users u WHERE u.user_id=a.submitted_by
9 agl.group_id, 'trackerclose'::text AS section, agl.group_artifact_id AS ref_id,
10 a.artifact_id as subref_id, a.summary as description, a.close_date AS activity_date, u.user_id, u.user_name, u.realname
11 FROM artifact_group_list agl JOIN artifact a using (group_artifact_id), users u WHERE u.user_id=a.assigned_to
12 --actually should join against
16 frsp.group_id, 'frsrelease'::text as section,frsp.package_id as ref_id,
17 frsr.release_id as subref_id, frsr.name AS description, frsr.release_date AS activity_date,
18 u.user_id, u.user_name, u.realname FROM frs_package frsp JOIN frs_release frsr USING (package_id), users u WHERE
19 u.user_id=frsr.released_by
22 fgl.group_id, 'forumpost'::text as section,fgl.group_forum_id as ref_id, forum.msg_id
23 as subref_id, forum.subject AS description, forum.post_date AS activity_date, u.user_id,
24 u.user_name, u.realname FROM forum_group_list fgl JOIN forum USING (group_forum_id), users u WHERE
25 u.user_id=forum.posted_by
27 SELECT group_id, 'docmannew'::text AS section, doc_group AS ref_id, docid AS subref_id,
28 filename AS description, createdate AS activity_date, created_by as user_id,
29 user_name, realname FROM docdata_vw
31 SELECT group_id, 'docmanupdate'::text AS section, doc_group AS ref_id, docid AS subref_id,
32 filename AS description, updatedate AS activity_date, created_by as user_id,
33 user_name, realname FROM docdata_vw
35 SELECT news_bytes.group_id,'news' AS section,news_bytes.id AS ref_id,news_bytes.forum_id AS subref_id,
36 news_bytes.summary AS description, news_bytes.post_date AS activity_date, u.user_id, u.user_name, u.realname
37 FROM news_bytes, users u WHERE u.user_id = news_bytes.submitted_by;