UFN_GROUPAPPEALLIST_QUERY

CREATE function dbo.UFN_GROUPAPPEALLIST_QUERY (
    @CONSTITUENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATEFILTER tinyint = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
    )
returns @APPEALS table (
    ID uniqueidentifier,
    APPEALID uniqueidentifier,
    APPEAL nvarchar(200),
    NAME nvarchar(200),
    DESCRIPTION nvarchar(200),
    MAILING nvarchar(200),
    LETTER nvarchar(200),
    DATESENT datetime,
    PACKAGE nvarchar(200),
    SOURCECODE nvarchar(200),
    SEGMENT nvarchar(200),
    TESTSEGMENT nvarchar(200),
    FINDERNUMBER bigint,
    COMMENTS nvarchar(200),
    MKTSEGMENTATIONID uniqueidentifier,
    MKTSEGMENTATIONSEGMENTID uniqueidentifier,
    TIMEFRAMETEXT nvarchar(200),
    TIMEFRAMEGROUPSORT nvarchar(200),
    HASPERMISSIONS bit,
    HASRESPONSES bit
    )
as
begin
    declare @ISSYSADMIN bit = 0;

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

    declare @QUERYVIEWID uniqueidentifier;

    set @QUERYVIEWID = '066c74da-220b-401e-8948-5f12820b1066';

    declare @USERGRANTEDALLCONSTITUENTS bit = 0;

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

    if @DATEFILTER is null
    begin
        set @DATEFILTER = 6;-- current month;

    end

    declare @STARTDATE datetime;
    declare @ENDDATE datetime;
    declare @ASOFDATE datetime = getdate();

    set @STARTDATE = @ASOFDATE;
    set @ENDDATE = @STARTDATE;

    -- @DATEFILTER=1 is for today only

    if @DATEFILTER = 2
    begin
        -- Next 7 days

        set @ENDDATE = dateadd(day, 7, @ENDDATE);
    end
    else if @DATEFILTER = 3
    begin
        -- Last 7 days

        set @STARTDATE = dateadd(day, - 7, @STARTDATE);
    end
    else if @DATEFILTER = 4
    begin
        -- Next 30 days

        set @ENDDATE = dateadd(day, 30, @ENDDATE);
    end
    else if @DATEFILTER = 5
    begin
        -- Last 30 days

        set @STARTDATE = dateadd(day, - 30, @STARTDATE);
    end
    else if @DATEFILTER = 6
    begin
        -- Current month

        set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 7
    begin
        -- Previous month

        set @STARTDATE = dbo.UFN_DATE_LASTMONTH_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_LASTMONTH_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 8
    begin
        -- Next 12 months

        set @ENDDATE = dateadd(year, 1, @ENDDATE);
    end
    else if @DATEFILTER = 9
    begin
        -- Last 12 months

        set @STARTDATE = dateadd(year, - 1, @STARTDATE);
    end
    else if @DATEFILTER = 10
    begin
        -- All dates

        set @STARTDATE = '17530102';
        set @ENDDATE = '99991230';
    end
    else if @DATEFILTER = 11
    begin
        -- Current fiscal year

        set @STARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 12
    begin
        -- Previous fiscal year

        set @STARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year, - 1, @STARTDATE), 0);
        set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(dateadd(year, - 1, @ENDDATE), 1);
    end
    else if @DATEFILTER = 13
    begin
        -- Next fiscal year

        set @STARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year, 1, @STARTDATE), 0);
        set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(dateadd(year, 1, @ENDDATE), 1);
    end
    else if @DATEFILTER = 14
    begin
        -- Within 7 days

        set @STARTDATE = dateadd(day, - 7, @STARTDATE);
        set @ENDDATE = dateadd(day, 7, @ENDDATE);
    end
    else if @DATEFILTER = 15
    begin
        -- Within 30 days

        set @STARTDATE = dateadd(day, - 30, @STARTDATE);
        set @ENDDATE = dateadd(day, 30, @ENDDATE);
    end
    else if @DATEFILTER = 16
    begin
        -- This calendar year

        set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 17
    begin
        -- Last calendar year

        set @STARTDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 18
    begin
        -- Next calendar year

        set @STARTDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 19
    begin
        -- This week

        set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 20
    begin
        -- Last week

        set @STARTDATE = dbo.UFN_DATE_LASTWEEK_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_LASTWEEK_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 21
    begin
        -- Next week

        set @STARTDATE = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTWEEK_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 22
    begin
        -- Next month

        set @STARTDATE = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTMONTH_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 23
    begin
        -- This quarter

        set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 24
    begin
        -- Last quarter

        set @STARTDATE = dbo.UFN_DATE_LASTQUARTER_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_LASTQUARTER_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 25
    begin
        -- Next quarter

        set @STARTDATE = dbo.UFN_DATE_NEXTQUARTER_FIRSTDAY(@STARTDATE, 0);
        set @ENDDATE = dbo.UFN_DATE_NEXTQUARTER_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 26
    begin
        --No date assigned

        set @STARTDATE = null
        set @ENDDATE = null
    end
    else if @DATEFILTER = 27
    begin
        --Remainder of the month

        set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 28
    begin
        --Remainder of this fiscal year

        set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@ENDDATE, 1);
    end
    else if @DATEFILTER = 29
    begin
        -- Last 6 months

        set @STARTDATE = dateadd(month, - 6, @STARTDATE);
    end
    else if @DATEFILTER = 30
    begin
        -- Last 24 months

        set @STARTDATE = dateadd(year, - 2, @STARTDATE);
    end

    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

    declare @CURRENTDATE datetime = getdate();
    declare @THISMONTHSTART datetime = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@CURRENTDATE, 0);
    declare @LASTMONTHSTART datetime = dateadd(month, - 1, @THISMONTHSTART);
    declare @TWOMONTHSAGOSTART datetime = dateadd(month, - 2, @THISMONTHSTART);

    with APPEALS_CTE
    as (
        select CONSTITUENTAPPEAL.ID,
            CONSTITUENTAPPEAL.APPEALID,
            APPEAL.NAME APPEALNAME,
            APPEAL.DESCRIPTION,
            CONSTITUENTAPPEAL.DATESENT,
            CONSTITUENTAPPEAL.SOURCECODE,
            case 
                when CONSTITUENTAPPEAL.FINDERNUMBER = 0
                    then null
else CONSTITUENTAPPEAL.FINDERNUMBER
                end as FINDERNUMBER,
            CONSTITUENTAPPEAL.COMMENTS,
            CONSTITUENTAPPEAL.MKTSEGMENTATIONID,
            CONSTITUENTAPPEAL.MKTSEGMENTATIONSEGMENTID,
            MKTPACKAGE.NAME as PACKAGE,
            MKTSEGMENTATION.NAME as MAILING,
            MKTSEGMENT.NAME as SEGMENT,
            isnull(
                (case MKTSEGMENTATIONTESTSEGMENT.PREFIXCODE
                    when 0 then
                        -- No prefix

                        MKTSEGMENTATIONTESTSEGMENT.NAME
                    when 1 then
                        -- Segment name

                        [MKTSEGMENT].[NAME] + ' - ' + [MKTSEGMENTATIONTESTSEGMENT].[NAME]
                    when 2 then
                        -- Segment code

                        [MKTSEGMENT].[CODE] + ' - ' + [MKTSEGMENTATIONTESTSEGMENT].[NAME]
                end),
                ''
            ) as TESTSEGMENT,
            CONSTITUENTAPPEAL.CONSTITUENTID as CONSTITUENTID,
            case 
                when exists (
                        select 1
                        from dbo.CONSTITUENTAPPEALRESPONSE
                        where CONSTITUENTAPPEALRESPONSE.CONSTITUENTAPPEALID = CONSTITUENTAPPEAL.ID
                        )
                    then convert(bit, 1)
                else convert(bit, 0)
                end as HASRESPONSES,
            case 
                when MAILLETTER.ID is not null
                    then MAILLETTER.NAME + ' (Mail)'
                when EMAILLETTER.ID is not null
                    then EMAILLETTER.NAME + ' (Email)'
                else ''
                end LETTER,
            APPEAL.SITEID as SITEID
        from dbo.CONSTITUENTAPPEAL
        inner join dbo.APPEAL
            on APPEAL.ID = CONSTITUENTAPPEAL.APPEALID
        left join dbo.MKTPACKAGE
            on MKTPACKAGE.ID = CONSTITUENTAPPEAL.MKTPACKAGEID
        left join dbo.MKTSEGMENTATION
            on MKTSEGMENTATION.ID = CONSTITUENTAPPEAL.MKTSEGMENTATIONID
        inner join dbo.GROUPMEMBER GM
            on CONSTITUENTAPPEAL.CONSTITUENTID = GM.MEMBERID
        left join dbo.GROUPMEMBERDATERANGE GMDR
            on GM.ID = GMDR.GROUPMEMBERID
        left join dbo.APPEALMAILINGSETUPLETTER MAILLETTER
            on CONSTITUENTAPPEAL.MKTSEGMENTATIONID = MAILLETTER.APPEALMAILINGSETUPID
                and CONSTITUENTAPPEAL.MKTPACKAGEID = MAILLETTER.MAILPACKAGEID
        left join dbo.APPEALMAILINGSETUPLETTER EMAILLETTER
            on CONSTITUENTAPPEAL.MKTSEGMENTATIONID = EMAILLETTER.APPEALMAILINGSETUPID
                and CONSTITUENTAPPEAL.MKTPACKAGEID = EMAILLETTER.EMAILPACKAGEID
        left join dbo.MKTSEGMENTATIONSEGMENT
            on MKTSEGMENTATIONSEGMENT.ID = CONSTITUENTAPPEAL.MKTSEGMENTATIONSEGMENTID
        left join dbo.MKTSEGMENT
            on MKTSEGMENT.ID = MKTSEGMENTATIONSEGMENT.SEGMENTID
        left join dbo.MKTSEGMENTATIONTESTSEGMENT
            on MKTSEGMENTATIONTESTSEGMENT.ID = CONSTITUENTAPPEAL.MKTSEGMENTATIONTESTSEGMENTID
        left join dbo.MKTSEGMENTATIONSEGMENT TESTSEGMENTATIONSEGMENT
            on TESTSEGMENTATIONSEGMENT.ID = MKTSEGMENTATIONTESTSEGMENT.SEGMENTID
        left join MKTSEGMENT TESTSEGMENT
            on TESTSEGMENT.ID = TESTSEGMENTATIONSEGMENT.SEGMENTID
        where GM.GROUPID = @CONSTITUENTID
            and (
                (
                    GMDR.DATEFROM is null
                    and (
                        GMDR.DATETO is null
                        or GMDR.DATETO >= CONSTITUENTAPPEAL.DATESENT
                        )
                    )
                or (
                    GMDR.DATETO is null
                    and (
                        GMDR.DATEFROM is null
                        or GMDR.DATEFROM <= CONSTITUENTAPPEAL.DATESENT
                        )
                    )
                or (
                    GMDR.DATEFROM <= CONSTITUENTAPPEAL.DATESENT
                    and GMDR.DATETO >= CONSTITUENTAPPEAL.DATESENT
                    )
                )
            and (
                CONSTITUENTAPPEAL.DATESENT between @STARTDATE
                    and @ENDDATE
                or CONSTITUENTAPPEAL.DATESENT is null
                )

        union all

        select CONSTITUENTAPPEAL.ID,
            CONSTITUENTAPPEAL.APPEALID,
            APPEAL.NAME APPEALNAME,
            APPEAL.DESCRIPTION,
            CONSTITUENTAPPEAL.DATESENT,
            CONSTITUENTAPPEAL.SOURCECODE,
            case 
                when CONSTITUENTAPPEAL.FINDERNUMBER = 0
                    then null
                else CONSTITUENTAPPEAL.FINDERNUMBER
                end as FINDERNUMBER,
            CONSTITUENTAPPEAL.COMMENTS,
            CONSTITUENTAPPEAL.MKTSEGMENTATIONID,
            CONSTITUENTAPPEAL.MKTSEGMENTATIONSEGMENTID,
            MKTPACKAGE.NAME as PACKAGE,
            MKTSEGMENTATION.NAME as MAILING,
            MKTSEGMENT.NAME as SEGMENT,
            isnull(
                (case MKTSEGMENTATIONTESTSEGMENT.PREFIXCODE
                    when 0 then
                        -- No prefix

                        MKTSEGMENTATIONTESTSEGMENT.NAME
                    when 1 then
                        -- Segment name

                        [MKTSEGMENT].[NAME] + ' - ' + [MKTSEGMENTATIONTESTSEGMENT].[NAME]
                    when 2 then
                        -- Segment code

                        [MKTSEGMENT].[CODE] + ' - ' + [MKTSEGMENTATIONTESTSEGMENT].[NAME]
                end),
                ''
            ) as TESTSEGMENT,
            CONSTITUENTAPPEAL.CONSTITUENTID as CONSTITUENTID,
            case 
                when exists (
                        select 1
                        from dbo.CONSTITUENTAPPEALRESPONSE
                        where CONSTITUENTAPPEALRESPONSE.CONSTITUENTAPPEALID = CONSTITUENTAPPEAL.ID
                        )
                    then convert(bit, 1)
                else convert(bit, 0)
                end as HASRESPONSES,
            case 
                when MAILLETTER.ID is not null
                    then MAILLETTER.NAME + ' (Mail)'
                when EMAILLETTER.ID is not null
                    then EMAILLETTER.NAME + ' (Email)'
                else ''
                end LETTER,
            APPEAL.SITEID as SITEID
        from dbo.CONSTITUENTAPPEAL
        inner join dbo.APPEAL
            on APPEAL.ID = CONSTITUENTAPPEAL.APPEALID
        left join dbo.MKTPACKAGE
            on MKTPACKAGE.ID = CONSTITUENTAPPEAL.MKTPACKAGEID
        left join dbo.MKTSEGMENTATION
            on MKTSEGMENTATION.ID = CONSTITUENTAPPEAL.MKTSEGMENTATIONID
        left join dbo.APPEALMAILINGSETUPLETTER MAILLETTER
            on CONSTITUENTAPPEAL.MKTSEGMENTATIONID = MAILLETTER.APPEALMAILINGSETUPID
                and CONSTITUENTAPPEAL.MKTPACKAGEID = MAILLETTER.MAILPACKAGEID
        left join dbo.APPEALMAILINGSETUPLETTER EMAILLETTER
            on CONSTITUENTAPPEAL.MKTSEGMENTATIONID = EMAILLETTER.APPEALMAILINGSETUPID
                and CONSTITUENTAPPEAL.MKTPACKAGEID = EMAILLETTER.EMAILPACKAGEID
        left join dbo.MKTSEGMENTATIONSEGMENT
            on MKTSEGMENTATIONSEGMENT.ID = CONSTITUENTAPPEAL.MKTSEGMENTATIONSEGMENTID
        left join dbo.MKTSEGMENT
            on MKTSEGMENT.ID = MKTSEGMENTATIONSEGMENT.SEGMENTID
        left join dbo.MKTSEGMENTATIONTESTSEGMENT
            on MKTSEGMENTATIONTESTSEGMENT.ID = CONSTITUENTAPPEAL.MKTSEGMENTATIONTESTSEGMENTID
        left join dbo.MKTSEGMENTATIONSEGMENT TESTSEGMENTATIONSEGMENT
            on TESTSEGMENTATIONSEGMENT.ID = MKTSEGMENTATIONTESTSEGMENT.SEGMENTID
        left join MKTSEGMENT TESTSEGMENT
            on TESTSEGMENT.ID = TESTSEGMENTATIONSEGMENT.SEGMENTID
        where CONSTITUENTAPPEAL.CONSTITUENTID = @CONSTITUENTID
            and (
                CONSTITUENTAPPEAL.DATESENT between @STARTDATE
                    and @ENDDATE
                or CONSTITUENTAPPEAL.DATESENT is null
                )
        )
    insert into @APPEALS
    select C.ID,
        APPEALID,
        APPEALNAME,
        NF.NAME CONSTITUENTNAME,
        DESCRIPTION,
        MAILING,
        LETTER,
        DATESENT,
        PACKAGE,
        SOURCECODE,
        SEGMENT,
        TESTSEGMENT,
        FINDERNUMBER,
        COMMENTS,
        MKTSEGMENTATIONID,
        MKTSEGMENTATIONSEGMENTID,
        case 
            when C.DATESENT >= @THISMONTHSTART
                then 'This month'
            when C.DATESENT >= @LASTMONTHSTART
                then 'Last month'
            when C.DATESENT >= @TWOMONTHSAGOSTART
                then 'Two months ago'
            else 'Older'
            end TIMEFRAMETEXT,
        case 
            when C.DATESENT >= @THISMONTHSTART
                then '0'
            when C.DATESENT >= @LASTMONTHSTART
                then '2'
            when C.DATESENT >= @TWOMONTHSAGOSTART
                then '4'
            else '6'
            end TIMEFRAMEGROUPSORT,
        case 
            when @ISSYSADMIN = 1
                or @USERGRANTEDALLCONSTITUENTS = 1
                or CONSTITSECURITY.ID is not null
                then 1
            else 0
            end as 'HASPERMISSIONS',
        HASRESPONSES
    from APPEALS_CTE C
    left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORQUERYVIEW(@CURRENTAPPUSERID, @QUERYVIEWID) CONSTITSECURITY
        on CONSTITSECURITY.ID = C.CONSTITUENTID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.CONSTITUENTID) NF
    where (
            --Apply site filter

            (
                @ISSYSADMIN = 1
                and @SITEFILTERMODE <> 3
                )
            or @SITEFILTERMODE = 0
            or C.SITEID in (
                select SITEID
                from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
                )
            )
    order by TIMEFRAMEGROUPSORT asc,
        APPEALNAME asc;

    return;
end