add helper views for custom ui for membership analysis

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 

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; 

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); 

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;

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'