Details
-
New Feature
-
Resolution: Unresolved
-
Minor
-
None
-
None
-
None
-
None
Description
create or replace view penn_membership_analysis1_v as
|
select gg.id as group_id, gg.name as group_name, gg.extension as group_extension,
|
gg.display_extension as group_display_extension, gm.id as member_id, gm.subject_id, gm.subject_source as subject_source_id,
|
case when exists |
(select 1 from grouper_memberships_lw_v gmlv where gmlv.group_id = gg.id and gmlv.member_id = gm.id and gmlv.list_name = 'members') |
then 'T' |
else 'F' |
end as is_user,
|
(select min(ppmlv.the_start_time/1000000) from grouper_pit_groups gpg, penn_pit_memberships_lw_v ppmlv, grouper_pit_members gpm, grouper_pit_fields gpf |
where gpg.id = ppmlv.owner_group_id and gpg.source_id = gg.id and ppmlv.member_id = gpm.id
|
and gpm.source_id = gm.id and gpf.name = 'members' and gpf.id = ppmlv.field_id and ppmlv.the_active = 'T') |
as user_start_secs_since_1970,
|
case when exists |
(select 1 from grouper_pit_groups gpg, penn_pit_memberships_lw_v ppmlv, grouper_pit_members gpm, grouper_pit_fields gpf |
where gpg.id = ppmlv.owner_group_id and gpg.source_id = gg.id and ppmlv.member_id = gpm.id
|
and gpm.source_id = gm.id and gpf.name = 'members' and gpf.id = ppmlv.field_id) |
then 'T' |
else 'F' |
end as has_been_in_group,
|
(select max(ppmlv.the_end_time/1000000) from grouper_pit_groups gpg, penn_pit_memberships_lw_v ppmlv, grouper_pit_members gpm, grouper_pit_fields gpf |
where gpg.id = ppmlv.owner_group_id and gpg.source_id = gg.id and ppmlv.member_id = gpm.id
|
and gpm.source_id = gm.id and gpf.name = 'members' and gpf.id = ppmlv.field_id) |
as user_end_secs_since_1970
|
from grouper_members gm, grouper_groups gg
|
Audit memberships
create or replace view penn_audit_add_membership_v as
|
select gm_user_using_grouper.name as user_using_grouper_name, to_timestamp(gaev.audit_entry_last_updated/1000) as the_timestamp, |
gm_acted_on.subject_id as user_acted_on_subject_id, gaev.string07 as group_name, gaev.string06 as group_id,
|
gm_acted_on.name as user_acted_on_name, gm_acted_on.id as user_acted_on_member_id, gm_acted_on.subject_source as user_acted_on_subject_source_id,
|
gm_user_using_grouper.id as user_using_grouper_member_id, gm_user_using_grouper.subject_id as user_using_grouper_subject_id,
|
gm_user_using_grouper.subject_source as user_using_grouper_subject_source
|
from grouper_audit_entry_v gaev, grouper_members gm_acted_on, grouper_members gm_user_using_grouper
|
where gaev.audit_category = 'membership' and gaev.action_name = 'addGroupMembership' |
and gaev.string03 = 'members' |
and gaev.string04 = gm_acted_on.id and gaev.logged_in_member_id = gm_user_using_grouper.id;
|
mship help
create or replace view penn_custom_ui_mships_help_v as
|
select group_id, group_name, group_extension, group_display_extension,
|
member_id, subject_id, subject_source_id, is_user,
|
user_start_secs_since_1970, has_been_in_group, user_end_secs_since_1970,
|
case when user_start_secs_since_1970 is null then 'F' |
when (extract(EPOCH from clock_timestamp()) - user_start_secs_since_1970/60)>30 then 'T' |
else 'F' |
end as is_user_for_30_min,
|
to_timestamp(user_start_secs_since_1970) user_start_timestamp,
|
to_timestamp(user_end_secs_since_1970) user_end_timestamp
|
from penn_membership_analysis1_v
|
order by lower(group_display_extension);
|
mship
create or replace view penn_custom_ui_mships_v as
|
select group_id, group_name, group_extension, group_display_extension,
|
member_id, subject_id, subject_source_id, is_user,
|
user_start_secs_since_1970, has_been_in_group, user_end_secs_since_1970,
|
is_user_for_30_min, user_start_timestamp, user_end_timestamp,
|
case when is_user = 'T' then |
'User has been in group ' || group_extension || ' since ' || to_char(user_start_timestamp, 'YYYY/MM/DD HH24:MI') |
when has_been_in_group = 'T' then |
'User was in group ' || group_extension || ' until ' || to_char(user_end_timestamp, 'YYYY/MM/DD HH24:MI') |
else
|
'User has never been in group ' || group_extension |
end as mship_desc,
|
case when is_user = 'T' and is_user_for_30_min = 'T' then |
'User has been in group ' || group_extension || ' since ' || to_char(user_start_timestamp, 'YYYY/MM/DD HH24:MI') || ' and is provisioned to WebLogin.' |
when is_user = 'T' and is_user_for_30_min != 'T' then |
'User has been in group ' || group_extension || ' since ' || to_char(user_start_timestamp, 'YYYY/MM/DD HH24:MI') || ' and pending provisioning to WebLogin.' |
when has_been_in_group = 'T' then |
'User was in group ' || group_extension || ' until ' || to_char(user_end_timestamp, 'YYYY/MM/DD HH24:MI') |
else
|
'User has never been in group ' || group_extension |
end as mship_shib_desc
|
from penn_custom_ui_mships_help_v;
|
|
imm disabled date
create view penn_imm_mship_disabled_date_v as
|
select to_char(to_timestamp(immediate_mship_disabled_time/1000),'YYYY/MM/DD HH24:MI') as disabled_time, gg.name as group_name, |
gm.subject_source as subject_source_id, gm.subject_id as subject_id
|
from grouper_memberships_all_v gmav, grouper_groups gg, grouper_fields gf, grouper_members gm
|
where gmav.owner_group_id = gg.id and gmav.field_id = gf.id and gf.name = 'members' |
and gmav.member_id = gm.id and mship_type = 'immediate' |