USP_DATALIST_PROSPECT_CONSTITUENTGROUPPROSPECTPLANS

CREATE procedure dbo.USP_DATALIST_PROSPECT_CONSTITUENTGROUPPROSPECTPLANS
(
    @CURRENTAPPUSERID uniqueidentifier,
    @GROUPID uniqueidentifier,
    @INCLUDEINACTIVEPLANS bit = 0,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
) 
as begin
    set nocount on;

    declare @PROSPECTIDS table
    (
      ID uniqueidentifier,
      NAME nvarchar(700),
      PLANID uniqueidentifier
    );

    insert into @PROSPECTIDS
    select distinct
        PROSPECTPLAN.PROSPECTID ID,
        NF.NAME NAME,
        PROSPECTPLAN.ID PLANID
    from
        dbo.PROSPECTPLAN 
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PROSPECTID) NF
    where
        PROSPECTPLAN.PROSPECTID in 
          (
              select @GROUPID

              union all

              (select ID from dbo.UFN_GROUP_GETCURRENTMEMBERSNOPERMISSIONCHECK(@GROUPID))
          )

    union all

    select distinct
        PROSPECTPLAN.PROSPECTID ID,
        NF.NAME NAME,
        PROSPECTPLAN.ID PLANID
    from dbo.PLANPARTICIPANT
    inner join dbo.PROSPECTPLAN on PLANPARTICIPANT.PROSPECTPLANID = PROSPECTPLAN.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PROSPECTID) NF
    where PLANPARTICIPANT.CONSTITUENTID = @GROUPID

    declare @STEWARDIDS table
    (
      ID uniqueidentifier,
      NAME nvarchar(700),
      PLANID uniqueidentifier
    );

    insert into @STEWARDIDS
    select distinct
        STEWARDSHIPPLAN.CONSTITUENTID ID,
        NF.NAME NAME,
        STEWARDSHIPPLAN.ID PLANID
    from dbo.STEWARDSHIPPLAN
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.CONSTITUENTID) NF
    where 
        STEWARDSHIPPLAN.CONSTITUENTID in 
            (
                select @GROUPID

                union all

                (select ID from dbo.UFN_GROUP_GETCURRENTMEMBERSNOPERMISSIONCHECK(@GROUPID))
            )

    union all

    select distinct
        STEWARDSHIPPLAN.CONSTITUENTID ID,
        NF.NAME NAME,
        STEWARDSHIPPLAN.ID PLANID
    from dbo.STEWARDSHIPPLANSTEWARD 
    left join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLANSTEWARD.PLANID = STEWARDSHIPPLAN.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.CONSTITUENTID) NF
    where STEWARDSHIPPLANSTEWARD.CONSTITUENTID = @GROUPID



    select 
        PP.ID as PLANID,
        PROSPECT.ID as PROSPECTID,
        case
            when PP.PROSPECTID = @GROUPID then 0
            else 1
        end as GROUPMEMBERPLAN,
        case
            when exists(select ID from dbo.PROSPECT where ID = PROSPECT.ID) then 1
            else 0
        end as ISPROSPECT,
        'C48113CC-888F-44da-ACE1-F22A4420CC90' as VIEWDATAFORMID,
        PP.ISACTIVE,
        0 as ISSTEWARDSHIPPLAN,
        PROSPECT.NAME as PROSPECTNAME,
        PPT.DESCRIPTION as PLANTYPE,
        PP.NAME as PLANNAME,
        dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID) SITES,
        PP.STARTDATE,
        PP.DATEADDED
    from dbo.PROSPECTPLAN PP     
    inner join @PROSPECTIDS PROSPECT on PP.PROSPECTID = PROSPECT.ID and PROSPECT.PLANID = PP.ID
    left outer join dbo.PROSPECTPLANTYPECODE PPT on PPT.ID = PP.PROSPECTPLANTYPECODEID

    where 
        (@INCLUDEINACTIVEPLANS = 1 or PP.ISACTIVE = 1)
        and (
            select count(*) 
            from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PLANSITE 
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANSITE].[SITEID] or (SITEID is null and [PLANSITE].[SITEID] is null)))
        ) > 0
        and (
            @SITEFILTERMODE = 0
                or PP.ID in (
                    select PROSPECTPLANSITE.PROSPECTPLANID
                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                        inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
                )
        )

    union all

    select
        STEWARDSHIPPLAN.ID as PLANID,
        STEWARD.ID as PROSPECTID,
        case
        when STEWARDSHIPPLAN.CONSTITUENTID = @GROUPID then 0
            else 1
        end as GROUPMEMBERPLAN,
        case
            when exists(select ID from dbo.PROSPECT where ID = STEWARDSHIPPLAN.CONSTITUENTID) then 1
            else 0
        end as ISPROSPECT,
        '30D20C5C-DF18-4DBD-AFDF-4A4034834563' as VIEWDATAFORMID,
        STEWARDSHIPPLAN.ISACTIVE as ISACTIVE,
        1 as ISSTEWARDSHIPPLAN,
        STEWARD.NAME as PROSPECTNAME,
        'Stewardship' as PLANTYPE,
        STEWARDSHIPPLAN.NAME as PLANNAME,
        dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLAN.ID) SITES,
        STEWARDSHIPPLAN.STARTDATE,
        STEWARDSHIPPLAN.DATEADDED
    from dbo.STEWARDSHIPPLAN
    inner join @STEWARDIDS STEWARD on STEWARDSHIPPLAN.CONSTITUENTID = STEWARD.ID and STEWARD.PLANID = STEWARDSHIPPLAN.ID
    where 
        (
            select count(*) 
            from dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID(STEWARDSHIPPLAN.ID) as PLANSITE 
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANSITE].[SITEID] or (SITEID is null and [PLANSITE].[SITEID] is null)))
        ) > 0
        and (
            @SITEFILTERMODE = 0
                or STEWARDSHIPPLAN.ID in (
                    select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                        inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
                )
        )

end