USP_DATALIST_SELECTIONSCALENDARITEM

CREATE procedure dbo.USP_DATALIST_SELECTIONSCALENDARITEM
(
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @SELECTIONS xml = null,
    @INCLUDEMAILINGSWITHOUTSELECTIONS bit = null,
    @INCLUDETASKS bit = null
)
as
set nocount on;

select distinct
    MKTSEGMENTATION.ID,
    MKTSEGMENTATION.NAME + ' - ' + APPEAL.NAME as NAME,
    MKTSEGMENTATION.MAILDATE,
    0, --Mailing

    MKTSEGMENTATION.DESCRIPTION,
    MKTSEGMENTATION.ID as PARENTID
from
    dbo.MKTSEGMENTATION
inner join
    dbo.APPEALMAILING on APPEALMAILING.ID = MKTSEGMENTATION.ID
left join
    dbo.MKTSEGMENTATIONSEGMENT on MKTSEGMENTATIONSEGMENT.SEGMENTATIONID = MKTSEGMENTATION.ID
left join
    dbo.MKTSEGMENT on MKTSEGMENT.ID = MKTSEGMENTATIONSEGMENT.SEGMENTID
left join 
    dbo.MKTSEGMENTSELECTION on MKTSEGMENTSELECTION.SEGMENTID = MKTSEGMENT.ID
left join
    dbo.IDSETREGISTER on IDSETREGISTER.ID = MKTSEGMENTSELECTION.SELECTIONID
left join
    dbo.APPEALMAILINGSETUPSELECTION on APPEALMAILINGSETUPSELECTION.APPEALMAILINGSETUPID = MKTSEGMENTATION.ID
left join
  dbo.APPEAL on APPEAL.ID = APPEALMAILING.APPEALID
where
    (MKTSEGMENTATION.MAILDATE is not null)
    and
    (
        ( (MKTSEGMENTATION.MAILDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
        or ( (MKTSEGMENTATION.MAILDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
        or ( (MKTSEGMENTATION.MAILDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
    )
    and
    (
        (
            IDSETREGISTER.ID is null
            and APPEALMAILINGSETUPSELECTION.SELECTIONID is null
            and @INCLUDEMAILINGSWITHOUTSELECTIONS = 1
        )
        or IDSETREGISTER.ID in (
            select
                T.c.value('(ID)[1]','uniqueidentifier') AS 'ID'
            from 
                @SELECTIONS.nodes('/SELECTIONS/ITEM') T(c)
            where 
                T.c.value('(CHECKED)[1]','bit') = 1
        )
        or APPEALMAILINGSETUPSELECTION.SELECTIONID in (
            select
                T.c.value('(ID)[1]','uniqueidentifier') AS 'ID'
            from 
                @SELECTIONS.nodes('/SELECTIONS/ITEM') T(c)
            where 
                T.c.value('(CHECKED)[1]','bit') = 1
        )
    )

union

select distinct
    APPEALMAILINGTASK.ID,
    APPEALMAILINGTASK.SUBJECT,
    dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE),
    1, --Task

    '' DESCRIPTION,
    APPEALMAILINGTASK.SEGMENTATIONID as PARENTID
from
    dbo.APPEALMAILINGTASK
inner join
    dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = APPEALMAILINGTASK.SEGMENTATIONID
inner join
    dbo.APPEALMAILING on APPEALMAILING.ID = MKTSEGMENTATION.ID
left join
    dbo.MKTSEGMENTATIONSEGMENT on MKTSEGMENTATIONSEGMENT.SEGMENTATIONID = MKTSEGMENTATION.ID
left join
    dbo.MKTSEGMENT on MKTSEGMENT.ID = MKTSEGMENTATIONSEGMENT.SEGMENTID
left join 
    dbo.MKTSEGMENTSELECTION on MKTSEGMENTSELECTION.SEGMENTID = MKTSEGMENT.ID
left join
    dbo.IDSETREGISTER on IDSETREGISTER.ID = MKTSEGMENTSELECTION.SELECTIONID
left join
    dbo.APPEALMAILINGSETUPSELECTION on APPEALMAILINGSETUPSELECTION.APPEALMAILINGSETUPID = MKTSEGMENTATION.ID
where
    (APPEALMAILINGTASK.DATEDUE <> '00000000')
    and
    (
        ( (dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
        or ( (dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
        or ( (dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
    )
    and @INCLUDETASKS = 1
    and
    (
        (
            IDSETREGISTER.ID is null
            and APPEALMAILINGSETUPSELECTION.SELECTIONID is null
            and @INCLUDEMAILINGSWITHOUTSELECTIONS = 1
        )
        or IDSETREGISTER.ID in (
            select
                T.c.value('(ID)[1]','uniqueidentifier') AS 'ID'
            from 
                @SELECTIONS.nodes('/SELECTIONS/ITEM') T(c)
            where 
                T.c.value('(CHECKED)[1]','bit') = 1
        )
        or APPEALMAILINGSETUPSELECTION.SELECTIONID in (
            select
                T.c.value('(ID)[1]','uniqueidentifier') AS 'ID'
            from 
                @SELECTIONS.nodes('/SELECTIONS/ITEM') T(c)
            where 
                T.c.value('(CHECKED)[1]','bit') = 1
        )
    )
order by NAME