smgp_v_group_member_role cciw customizations

USE [ArenaDB]
GO

/****** Object:  View [dbo].[smgp_v_group_member_role]    Script Date: 5/31/2019 3:41:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



Alter VIEW [dbo].[smgp_v_group_member_role]

AS
-- Fetch Leaders from smgp_group
SELECT G.leader_person_id AS person_id, 1 AS role_luid, - 1 AS role_order,
       GC.group_id, GC.active AS active, GC.category_id, C.credit_as_small_group,
       G.date_created AS date_created, C.leader_caption AS role_name, 3 AS 'status',
       G.organization_id,
       1 as leadership_role, --CCIW Customization
       G.group_cluster_id --CCIW Customization
FROM smgp_group G
INNER JOIN smgp_v_group_category GC ON G.group_id = GC.group_id AND G.organization_id = GC.organization_id
INNER JOIN smgp_category C ON GC.category_id = C.category_id AND GC.organization_id = C.organization_id
WHERE G.leader_person_id <> - 1

UNION

--fetch members from smgp_member, may include assistant leaders
SELECT M.person_id, M.role_luid, LU.lookup_order AS role_order, GC.group_id,
       (GC.active & M.active) AS active, GC.category_id, C.credit_as_small_group,
       M.date_created AS date_created, LU.lookup_value AS role_name, 2 AS 'status',
       M.organization_id,
       CASE WHEN LU.lookup_qualifier2 = 'true' Then 1 ELSE 0 END as leadership_role, --CCIW Customization
       G.group_cluster_id --CCIW Customization
FROM smgp_member M
INNER JOIN smgp_v_group_category GC ON M.group_id = GC.group_id AND M.organization_id = GC.organization_id
INNER JOIN smgp_group G on G.group_id = M.group_id
INNER JOIN core_lookup LU ON LU.lookup_id = M.role_luid AND LU.organization_id = M.organization_id
INNER JOIN smgp_category C ON GC.category_id = C.category_id AND GC.organization_id = C.organization_id

UNION

----fetch registrants from smgp_registration R
SELECT RP.person_id, - 1 AS role_luid, - 1 AS role_order, R.group_id, - 1 AS active,
       CASE
         WHEN R.group_id IS NOT NULL
           THEN GC.category_id
         WHEN R.group_id IS NULL AND R.group_cluster_id IS NOT NULL
           THEN CT.category_id
       END AS category_id,
       CASE
         WHEN R.group_id IS NOT NULL
          THEN C.credit_as_small_group ELSE - 1
       END AS credit_as_small_group,
       R.date_created AS date_created,
       CASE
         WHEN R.group_id IS NOT NULL
           THEN 'Pending'
           ELSE 'Unassigned'
       END AS role_name,
       CASE
         WHEN R.group_id IS NOT NULL
         THEN '1' ELSE '0'
       END AS 'status',
       R.organization_id,
       0 as leadership_role, --CCIW Customization
       R.group_cluster_id --CCIW Customization
FROM smgp_registration R
INNER JOIN smgp_registration_person RP ON RP.registration_id = R.registration_id
LEFT OUTER JOIN smgp_v_group_category GC ON R.group_id = GC.group_id AND R.organization_id = GC.organization_id
LEFT OUTER JOIN smgp_category C ON GC.category_id = C.category_id AND GC.organization_id = C.organization_id
LEFT OUTER JOIN smgp_group_cluster SGC ON R.group_cluster_id = SGC.group_cluster_id AND R.organization_id = SGC.organization_id
LEFT OUTER JOIN smgp_cluster_type CT ON SGC.cluster_type_id = CT.cluster_type_id
GO