UFN_GROUPINTERACTIONLIST2_QUERY

CREATE function dbo.UFN_GROUPINTERACTIONLIST2_QUERY (
    @GROUPID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = 'E6317D90-8D21-4647-B9BD-4D391A747809',
    @SECURITYFEATURETYPE tinyint = 10
    )
returns @INTERACTIONS table (
    ID uniqueidentifier,
    DATE datetime,
    GROUPORPRIMARYSORTVALUE nvarchar(200),
    STATUS nvarchar(200),
    OWNERID uniqueidentifier,
    FUNDRAISER nvarchar(200),
    OBJECTIVE nvarchar(200),
    INTERACTIONTYPE nvarchar(200),
    ISSTEP bit,
    HASDOCUMENTATION bit,
    HASPERMISSIONS bit,
    GROUPORPRIMARYSORT nvarchar(200),
    CATEGORY nvarchar(200),
    SUBCATEGORY nvarchar(200),
    VIEWFORMID uniqueidentifier,
    SITES nvarchar(200),
    STARTTIME nvarchar(4),
    ENDTIME nvarchar(4),
    TIMEZONE nvarchar(200),
    ISSTEWARDSHIPSTEP bit,
    ISINDIVIDUALSTEP bit,
    EXPECTEDDATE datetime,
    ACTUALDATE datetime,
    EXPECTEDSTARTTIME nvarchar(4),
    ACTUALSTARTTIME nvarchar(4),
    EXPECTEDENDTIME nvarchar(4),
    ACTUALENDTIME nvarchar(4),
    COMMENT nvarchar(max),
    HASPARTICIPANTS bit,
    LOCATION nvarchar(300)
    )
as
begin
    declare @QUERYVIEWID uniqueidentifier = '2d55a6fb-adeb-4b9d-a0dc-ddac51b39b8e';

    declare @ISSYSADMIN bit = 0;
    select @ISSYSADMIN = ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID;

    declare @USERGRANTEDALLCONSTITUENTS bit = 0;
    set @USERGRANTEDALLCONSTITUENTS = dbo.UFN_SECURITY_APPUSER_GRANTED_QUERYVIEW_IN_NONRACROLE(@CURRENTAPPUSERID, @QUERYVIEWID);

    declare @ASOFDATE datetime = getdate();

    declare @CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT table (
        ID uniqueidentifier,
        DATEFROM datetime,
        DATETO datetime,
        ISGROUP bit,
        ISPRIMARY bit
        );

    insert into @CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT
    select CONSTITUENT.ID as ID,
        GMDR.DATEFROM,
        GMDR.DATETO,
        0 as ISGROUP,
        GROUPMEMBER.ISPRIMARY
    from dbo.GROUPMEMBER
    inner join dbo.CONSTITUENT
        on CONSTITUENT.ID = GROUPMEMBER.MEMBERID
    left join dbo.GROUPMEMBERDATERANGE GMDR
        on GMDR.GROUPMEMBERID = GROUPMEMBER.ID
    where GROUPMEMBER.GROUPID = @GROUPID

    union all

    select @GROUPID,
        null as DATEFROM,
        null as DATETO,
        1 as ISGROUP,
        0 as ISPRIMARY;

    declare @INTERACTIONFORGROUPORMEMBER table (
        ID uniqueidentifier,
        LINKEDTHROUGHCONSTITUENTID uniqueidentifier
        );

    insert into @INTERACTIONFORGROUPORMEMBER (
        ID,
        LINKEDTHROUGHCONSTITUENTID
        )
    select V_CONSTITUENT_INTERACTION.ID,
        GROUPORMEMBERCONSTITUENT.ID as LINKEDTHROUGHCONSTITUENTID
    from dbo.V_CONSTITUENT_INTERACTION
    inner join @CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT GROUPORMEMBERCONSTITUENT
        on GROUPORMEMBERCONSTITUENT.ID = V_CONSTITUENT_INTERACTION.CONSTITUENTID

    union

    select V_CONSTITUENT_INTERACTION.ID,
        GROUPORMEMBERCONSTITUENT.ID as LINKEDTHROUGHCONSTITUENTID
    from dbo.V_CONSTITUENT_INTERACTION
    inner join @CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT GROUPORMEMBERCONSTITUENT
        on GROUPORMEMBERCONSTITUENT.ID = V_CONSTITUENT_INTERACTION.CONTACTPERSONID

    union

    select V_CONSTITUENT_INTERACTION.ID,
        GROUPORMEMBERCONSTITUENT.ID as LINKEDTHROUGHCONSTITUENTID
    from dbo.V_CONSTITUENT_INTERACTION
    inner join dbo.INTERACTIONPARTICIPANT
        on INTERACTIONPARTICIPANT.INTERACTIONID = V_CONSTITUENT_INTERACTION.ID
    inner join @CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT GROUPORMEMBERCONSTITUENT
        on GROUPORMEMBERCONSTITUENT.ID = INTERACTIONPARTICIPANT.CONSTITUENTID;

    with INTERACTION_CTE
    as (
        select INTERACTION.ID,
            INTERACTION.DATE,
            INTERACTION.STATUS,
            INTERACTION.FUNDRAISERID as OWNERID,
            INTERACTION.OBJECTIVE,
            INTERACTION.INTERACTIONTYPECODEID,
            case 
                when INTERACTION.INTERACTIONBASETYPE = 2
                    then case 
                            when exists (
                                    select 1
                                    from dbo.STEWARDSHIPNOTE
                                    where STEWARDSHIPNOTE.STEPID = INTERACTION.ID
                                    )
                                or exists (
                                    select 1
                                    from dbo.STEWARDSHIPATTACHMENT
                                    where STEWARDSHIPATTACHMENT.STEPID = INTERACTION.ID
                                    )
                                or exists (
                                    select 1
                                    from dbo.STEWARDSHIPMEDIALINK
                                    where STEWARDSHIPMEDIALINK.STEPID = INTERACTION.ID
                                    )
                                then 1
                            else 0
                            end
                else case 
                    when exists (
                            select 1
                            from dbo.INTERACTIONNOTE
                            where INTERACTIONNOTE.INTERACTIONID = INTERACTION.ID
                            )
                        or exists (
                            select 1
                            from dbo.INTERACTIONATTACHMENT
                            where INTERACTIONATTACHMENT.INTERACTIONID = INTERACTION.ID
                            )
                        or exists (
                            select 1
                            from dbo.INTERACTIONMEDIALINK
                            where INTERACTIONMEDIALINK.INTERACTIONID = INTERACTION.ID
                            )
                        then 1
                    else 0
                    end
            end as HASDOCUMENTATION,
            case 
                when INTERACTION.INTERACTIONBASETYPE = 1
                    then 1
                else 0
            end as ISMOVE,
            INTERACTION.EVENTID,
            coalesce(INTERACTIONCATEGORY.NAME, STEWARDSHIPSTEPCATEGORYCODE.DESCRIPTION) as CATEGORY,
            INTERACTIONSUBCATEGORY.NAME as SUBCATEGORY,
            INTERACTION.STARTTIME,
            INTERACTION.ENDTIME,
            INTERACTION.TIMEZONEENTRYID,
            case 
                when INTERACTION.INTERACTIONBASETYPE = 2
                    then 1
                else 0
            end as ISSTEWARDSHIPSTEP,
            case 
                when CONSTITUENT.ISGROUP = 1
                    then '0'
                else case 
                    when CONSTITUENT.ISPRIMARY = 1
                        then '1'
                    else '2' + CONSTITUENTDETAIL.KEYNAME + ' ' + CONSTITUENTDETAIL.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36))
                    end
            end as GROUPORPRIMARYSORT,
            CONSTITUENTDETAIL.NAME as GROUPORPRIMARYSORTVALUE,
            case 
                when @ISSYSADMIN = 1
                    or @USERGRANTEDALLCONSTITUENTS = 1
                    or CONSTITSECURITY.ID is not null
                    then 1
                else 0
            end HASPERMISSIONS,
            INTERACTION.INTERACTIONBASETYPE,
            INTERACTION.PROSPECTPLANID,
            INTERACTION.STEWARDSHIPPLANID,
            case 
                when INTERACTIONCONSTITUENT.ISGROUP = 1
                    then 0
                when INTERACTIONCONSTITUENT.ISORGANIZATION = 1
                    then 0
                else 1
            end as ISINDIVIDUALSTEP,
            INTERACTION.EXPECTEDDATE,
            INTERACTION.ACTUALDATE,
            INTERACTION.EXPECTEDSTARTTIME,
            INTERACTION.ACTUALSTARTTIME,
            INTERACTION.EXPECTEDENDTIME,
            INTERACTION.ACTUALENDTIME,
            INTERACTION.COMMENT,
            case
                when exists(select 1 from dbo.INTERACTIONPARTICIPANT where INTERACTIONPARTICIPANT.INTERACTIONID = INTERACTION.ID) then 1
                else 0
            end as HASPARTICIPANTS,
            INTERACTION.LOCATION
        from @INTERACTIONFORGROUPORMEMBER GROUPINTERACTION
        inner join dbo.V_CONSTITUENT_INTERACTION INTERACTION
            on GROUPINTERACTION.ID = INTERACTION.ID
        left join dbo.INTERACTIONSUBCATEGORY
            on INTERACTIONSUBCATEGORY.ID = INTERACTION.INTERACTIONSUBCATEGORYID
        left join dbo.INTERACTIONCATEGORY
            on INTERACTIONCATEGORY.ID = INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID
        left join dbo.STEWARDSHIPSTEPCATEGORYCODE
            on STEWARDSHIPSTEPCATEGORYCODE.ID = INTERACTION.STEWARDSHIPSTEPCATEGORYCODEID
        left join dbo.INTERACTIONPARTICIPANT
            on INTERACTIONPARTICIPANT.INTERACTIONID = INTERACTION.ID
        inner join @CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT CONSTITUENT
            on GROUPINTERACTION.LINKEDTHROUGHCONSTITUENTID = CONSTITUENT.ID
        inner join dbo.CONSTITUENT as CONSTITUENTDETAIL
            on CONSTITUENT.ID = CONSTITUENTDETAIL.ID
        left join dbo.CONSTITUENT INTERACTIONCONSTITUENT
            on INTERACTIONCONSTITUENT.ID = INTERACTION.CONSTITUENTID
        left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORQUERYVIEW(@CURRENTAPPUSERID, @QUERYVIEWID) CONSTITSECURITY
            on CONSTITSECURITY.ID = CONSTITUENT.ID
        where (
            exists (
                select top 1 INTERACTIONSITE.SITEID
                from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) INTERACTIONSITE
                where (
                        @ISSYSADMIN = 1
                        or exists (
                            select 1
                            from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE)
                            where SITEID = [INTERACTIONSITE].[SITEID]
                                or (
                                    SITEID is null
                                    and [INTERACTIONSITE].[SITEID] is null
                                    )
                            )
                        )
                )
            and (
                (@ISSYSADMIN = 1 and @SITEFILTERMODE <> 3)
                or @SITEFILTERMODE = 0
                or (
                    INTERACTION.INTERACTIONBASETYPE = 0
                    and INTERACTION.ID in (
                        select INTERACTIONSITE.INTERACTIONID
                        from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                        inner join dbo.INTERACTIONSITE
                            on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
                        )
                    )
                or (
                    INTERACTION.INTERACTIONBASETYPE = 1
                    and INTERACTION.PROSPECTPLANID in (
                        select PROSPECTPLANSITE.PROSPECTPLANID
                        from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                        inner join dbo.PROSPECTPLANSITE
                            on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
                        )
                    )
                or (
                    INTERACTION.INTERACTIONBASETYPE = 2
                    and INTERACTION.STEWARDSHIPPLANID in (
                        select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
                        from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
   inner join dbo.STEWARDSHIPPLANSITE
                            on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
                        )
                    )
                )
            )
        )
    insert into @INTERACTIONS
    select I.ID,
        I.DATE,
        I.GROUPORPRIMARYSORTVALUE,
        I.STATUS,
        OWNER.ID OWNERID,
        OWNER.NAME FUNDRAISER,
        I.OBJECTIVE,
        INTERACTIONTYPECODE.DESCRIPTION INTERACTIONTYPE,
        I.ISMOVE ISSTEP,
        I.HASDOCUMENTATION,
        I.HASPERMISSIONS,
        min(I.GROUPORPRIMARYSORT) GROUPORPRIMARYSORT,
        I.CATEGORY,
        I.SUBCATEGORY,
        case 
            when I.INTERACTIONBASETYPE = 1
                then '6ce4b0e8-cfcf-4f57-94c3-00eea94fb499'
            when I.INTERACTIONBASETYPE = 2
                then '0a9d7c4b-018d-48b5-aa14-589cf6c0d97b'
            else 'b525985b-be02-4f02-a9b8-c110411cf936'
            end as VIEWFORMID,
        case 
            when I.INTERACTIONBASETYPE = 1
                then dbo.UFN_PROSPECTPLAN_GETSITELIST(I.PROSPECTPLANID)
            when I.INTERACTIONBASETYPE = 2
                then dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(I.STEWARDSHIPPLANID)
            else dbo.UFN_CONSTITUENTINTERACTION_GETSITELIST(I.ID)
            end as SITES,
        I.STARTTIME,
        I.ENDTIME,
        TIMEZONEENTRY.DISPLAYNAME TIMEZONE,
        I.ISSTEWARDSHIPSTEP,
        I.ISINDIVIDUALSTEP,
        I.EXPECTEDDATE,
        I.ACTUALDATE,
        I.EXPECTEDSTARTTIME,
        I.ACTUALSTARTTIME,
        I.EXPECTEDENDTIME,
        I.ACTUALENDTIME,
        I.COMMENT,
        I.HASPARTICIPANTS,
        I.LOCATION
    from INTERACTION_CTE I
    inner join dbo.INTERACTIONTYPECODE
        on I.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
    left outer join dbo.CONSTITUENT OWNER
        on OWNER.ID = I.OWNERID
    left outer join dbo.TIMEZONEENTRY
        on I.TIMEZONEENTRYID = TIMEZONEENTRY.ID
    group by I.ID,
        I.DATE,
        I.GROUPORPRIMARYSORTVALUE,
        I.STATUS,
        OWNER.ID,
        OWNER.NAME,
        I.OBJECTIVE,
        I.INTERACTIONTYPECODEID,
        I.ISMOVE,
        I.HASDOCUMENTATION,
        I.HASPERMISSIONS,
        I.CATEGORY,
        I.SUBCATEGORY,
        I.INTERACTIONBASETYPE,
        I.PROSPECTPLANID,
        I.STEWARDSHIPPLANID,
        INTERACTIONTYPECODE.DESCRIPTION,
        I.STARTTIME,
        I.ENDTIME,
        TIMEZONEENTRY.DISPLAYNAME,
        I.ISSTEWARDSHIPSTEP,
        I.ISINDIVIDUALSTEP,
        I.EXPECTEDDATE,
        I.ACTUALDATE,
        I.EXPECTEDSTARTTIME,
        I.ACTUALSTARTTIME,
        I.EXPECTEDENDTIME,
        I.ACTUALENDTIME,
        I.COMMENT,
        I.HASPARTICIPANTS,
        I.LOCATION
    order by GROUPORPRIMARYSORT,
        I.DATE desc;

    return;
end