USP_DATALIST_CONSTITUENTAPPEAL

CREATE procedure dbo.USP_DATALIST_CONSTITUENTAPPEAL 
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null
)
as
    set nocount on;

    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,

        case
            when MAILLETTER.ID is not null then MAILLETTER.NAME + ' (Mail)'
            when EMAILLETTER.ID is not null then EMAILLETTER.NAME + ' (Email)'
            else ''
        end    LETTER,

        MKTPACKAGE.NAME as PACKAGE,
        MKTSEGMENTATION.NAME as MAILING,
        dbo.UFN_MKTSEGMENTATIONSEGMENT_GETNAME(CONSTITUENTAPPEAL.MKTSEGMENTATIONSEGMENTID) as SEGMENT,
        dbo.UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME(CONSTITUENTAPPEAL.MKTSEGMENTATIONTESTSEGMENTID) as TESTSEGMENT,
        (case 
            when exists(select 1 from dbo.CONSTITUENTAPPEALRESPONSE where CONSTITUENTAPPEALRESPONSE.CONSTITUENTAPPEALID=CONSTITUENTAPPEAL.ID) then convert(bit,1) 
            else convert(bit,0) 
        end) as HASRESPONSES,
        cast((case when APPEALMAILING.ID is not null then 1 else 0 end) as bit) as ISAPPEALMAILING,
        case
            when CONSTITUENTAPPEAL.DATESENT >= dateadd(month, 1, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then 'Future'
            when CONSTITUENTAPPEAL.DATESENT >= dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0) then 'This month'
            when CONSTITUENTAPPEAL.DATESENT >= dateadd(month, -1, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then 'Last month'
            when CONSTITUENTAPPEAL.DATESENT >= dateadd(month, -2, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then 'Two months ago'
            else 'Older'
        end TIMEFRAMETEXT,
        case
            when CONSTITUENTAPPEAL.DATESENT >= dateadd(month, 1, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then '0'
            when CONSTITUENTAPPEAL.DATESENT >= dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0) then '2'
            when CONSTITUENTAPPEAL.DATESENT >= dateadd(month, -1, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then '4'
            when CONSTITUENTAPPEAL.DATESENT >= dateadd(month, -2, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then '6'
            else '8'
        end TIMEFRAMEGROUPSORT, 
        SITE.NAME,
        MKTSEGMENTATION.MAILINGFAMILYTYPECODE
    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.APPEALMAILING 
        on APPEALMAILING.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.SITE 
        on SITE.ID=APPEAL.SITEID
    where CONSTITUENTAPPEAL.CONSTITUENTID = @ID
    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, APPEAL.SITEID) = 1 and
            (@SITEFILTERMODE = 0
                or exists(
                    select 1
                    from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
                    where SITEFILTER.SITEID = APPEAL.SITEID)
            )
    order by TIMEFRAMEGROUPSORT asc, CONSTITUENTAPPEAL.DATESENT desc;