USP_CONSTITUENTGROUP_INTERACTIONS

CREATE procedure dbo.USP_CONSTITUENTGROUP_INTERACTIONS(
    @GROUPID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @TYPEFILTER uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @STATUS tinyint = null,
    @ROWCOUNT int = null output
) 
as
begin
    set nocount on;

    declare @DATALISTID uniqueidentifier = 'DC27B924-D2EC-4865-B5A6-77B52FDAA957';

  -- Drop temp table if needed

  if object_id('tempdb..#CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT') is not null
    drop table #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT;

    create table #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT(
        ID uniqueidentifier,
        DATEFROM datetime,
        DATETO datetime,
        ISGROUP bit,
        ISPRIMARY bit
    )

    create nonclustered index [IX_#CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT_ID] on #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT
    (ID) include (DATEFROM, DATETO, ISGROUP, ISPRIMARY);

    insert into #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT (ID, DATEFROM, DATETO, ISGROUP, ISPRIMARY)
    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,
        unique clustered(ID, LINKEDTHROUGHCONSTITUENTID)
    )

    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 dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1 
            else dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, CONSTITUENT.ID)
        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
        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
        where
            (
                (INTERACTION.DATE between @STARTDATE and @ENDDATE) or
                (@STARTDATE is null and @ENDDATE is null) or
                (INTERACTION.DATE >= @STARTDATE and @ENDDATE is null) or
                (@STARTDATE is null and INTERACTION.DATE <= @ENDDATE)
            ) and
            (
                INTERACTION.STATUSCODE = @STATUS or
                @STATUS is null
            ) and
            (
                (CONSTITUENT.DATEFROM is null and (CONSTITUENT.DATETO is null or CONSTITUENT.DATETO > INTERACTION.DATE)) or
                (CONSTITUENT.DATETO is null and (CONSTITUENT.DATEFROM is null or CONSTITUENT.DATEFROM <= INTERACTION.DATE)) or
                (CONSTITUENT.DATEFROM <= INTERACTION.DATE and CONSTITUENT.DATETO > INTERACTION.DATE)
            ) and
            (@TYPEFILTER is null or INTERACTION.INTERACTIONTYPECODEID = @TYPEFILTER) and
            exists(
                select top 1 INTERACTIONSITE.SITEID from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) INTERACTIONSITE
                where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 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
            (
                @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
                    )
                )
            )
    )
    select
        I.ID,
        I.DATE,
        I.GROUPORPRIMARYSORTVALUE,
        I.STATUS,
        FC.ID OWNERID,
        FC.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
    from INTERACTION_CTE I
        inner join dbo.INTERACTIONTYPECODE on I.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
        left outer join dbo.CONSTITUENT FC on FC.ID = I.OWNERID
        left outer join dbo.TIMEZONEENTRY on I.TIMEZONEENTRYID = TIMEZONEENTRY.ID
    group by I.ID, I.DATE, I.GROUPORPRIMARYSORTVALUE, I.STATUS, FC.ID, FC.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
    order by GROUPORPRIMARYSORT, I.DATE desc;

    set @ROWCOUNT = @@ROWCOUNT

    drop table #CONSTITUENTGROUPINTERACTION2_GROUPORMEMBERCONSTITUENT;

end