UFN_GROUPINTERACTIONLIST_QUERY

CREATE function dbo.UFN_GROUPINTERACTIONLIST_QUERY (
    @GROUPID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATEFILTER tinyint = 0,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @TYPEFILTER uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @STATUS tinyint = null
    )
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
    )
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);

    if @DATEFILTER is null
        set @DATEFILTER = 3; -- this month


    declare @ASOFDATE datetime = getdate();

    if @DATEFILTER = 0 -- all

    begin
        set @STARTDATE = null;
        set @ENDDATE = null;
    end

    if @DATEFILTER = 1 -- this year

    begin
        set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@ASOFDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@ASOFDATE, 0);
    end

    if @DATEFILTER = 2 -- this quarter

    begin
        set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@ASOFDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@ASOFDATE, 0);
    end

    if @DATEFILTER = 3 -- this month

    begin
        set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@ASOFDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@ASOFDATE, 0);
    end

    if @DATEFILTER = 4 -- this week

    begin
        set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@ASOFDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@ASOFDATE, 0);
    end

    if @DATEFILTER = 5 -- next week

    begin
        set @STARTDATE = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(@ASOFDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTWEEK_LASTDAY(@ASOFDATE, 0);
    end

    if @DATEFILTER = 6 -- next month

    begin
        set @STARTDATE = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(@ASOFDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTMONTH_LASTDAY(@ASOFDATE, 0);
    end

    if @DATEFILTER = 7 -- next quarter

    begin
        set @STARTDATE = dbo.UFN_DATE_NEXTQUARTER_FIRSTDAY(@ASOFDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTQUARTER_LASTDAY(@ASOFDATE, 0);
    end

    if @DATEFILTER = 8 -- next year

    begin
        set @STARTDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_FIRSTDAY(@ASOFDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_LASTDAY(@ASOFDATE, 0);
    end



    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
        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 (
                (
                    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 (
                        @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,
        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;

    return;
end