USP_DATALIST_APPEALMAILING

CREATE procedure dbo.[USP_DATALIST_APPEALMAILING]
(
      @APPEALID uniqueidentifier,
      @ACTIVESTATUS bit = null,
      @MAILDATEFROM datetime = null,
      @MAILDATETO datetime = null,
      @SHOWUNSCHEDULED bit = 1
)
as
    set nocount on;

    select
        [APPEALMAILING].[ID],
        [MKTSEGMENTATIONACTIVATEPROCESS].[ID] as [MKTSEGMENTATIONACTIVATEPROCESSID],
        [MKTSEGMENTATION].[NAME],
        [MKTSEGMENTATION].[DESCRIPTION],
        [MKTSEGMENTATION].[MAILDATE],
        [MKTSEGMENTATION].[ACTIVE],
        dbo.[UFN_MKTSEGMENTATION_ISCALCULATING]([MKTSEGMENTATION].[ID]) as ISCALCULATING,
        dbo.[UFN_MKTSEGMENTATION_ISACTIVATING]([MKTSEGMENTATION].[ID]) as ISACTIVATING,
        (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as NUMSEGMENTS,
        cast(case when APPEALMAILINGSETUP.ID is null or APPEALMAILINGSETUP.MAILPACKAGEID is not null or APPEALMAILINGSETUP.EMAILPACKAGEID is not null then 1 else 0 end as bit) as PACKAGEDEFINED,
        [MKTPACKAGE].[NAME] [PACKAGE],
        [APPEALMAILINGSELECTION].[NAME] [SELECTION],
        [MKTSEGMENTATION].[BASECURRENCYID],
        cast(case when exists(select COMMUNICATIONLETTER.ID from dbo.COMMUNICATIONLETTER inner join dbo.COMMUNICATIONLETTERSELECTION on COMMUNICATIONLETTER.ID = COMMUNICATIONLETTERSELECTION.COMMUNICATIONLETTERID and COMMUNICATIONLETTERSELECTION.FILTERTYPECODE = 0 where COMMUNICATIONLETTER.SEGMENTATIONID = APPEALMAILING.ID) then 1 else 0 end as bit) [HASLETTERSELECTIONS],
        cast(case when exists(select COMMUNICATIONLETTER.ID from dbo.COMMUNICATIONLETTER where COMMUNICATIONLETTER.SEGMENTATIONID = APPEALMAILING.ID and COMMUNICATIONLETTER.CHANNELCODE > 0 and (len(COMMUNICATIONLETTER.EMAILSUBJECT) = 0 or len(COMMUNICATIONLETTER.EMAILFROMADDRESS) = 0 or len(COMMUNICATIONLETTER.EMAILFROMDISPLAYNAME) = 0 or len(COMMUNICATIONLETTER.EMAILCONTENTHTML) = 0)) then 1 else 0 end as bit) [HASINCOMPLETEEMAILLETTER]
    from dbo.[APPEALMAILING]
    inner join dbo.[MKTSEGMENTATION] 
        on [MKTSEGMENTATION].[ID] = [APPEALMAILING].[ID]
    inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] 
        on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    left join dbo.APPEALMAILINGSETUP 
        on APPEALMAILINGSETUP.ID = APPEALMAILING.ID
    left join dbo.[MKTSEGMENTATIONSEGMENT] 
        on [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
    left join dbo.[MKTPACKAGE] 
        on [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID]
    left join dbo.[MKTSEGMENT] 
        on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]
    left outer join dbo.[MKTSEGMENTSELECTION] 
        on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left outer join dbo.[IDSETREGISTER] as [APPEALMAILINGSELECTION] 
        on [APPEALMAILINGSELECTION].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID]
    where [APPEALMAILING].[APPEALID] = @APPEALID
        and (@ACTIVESTATUS is null or @ACTIVESTATUS = [MKTSEGMENTATION].[ACTIVE])
        and (@MAILDATEFROM is null or ([MKTSEGMENTATION].[MAILDATE] is not null and [MKTSEGMENTATION].[MAILDATE] >= @MAILDATEFROM) or (@SHOWUNSCHEDULED = 1 and [MKTSEGMENTATION].[MAILDATE] is null))
        and (@MAILDATETO is null or ([MKTSEGMENTATION].[MAILDATE] is not null and [MKTSEGMENTATION].[MAILDATE] <= @MAILDATETO) or (@SHOWUNSCHEDULED = 1 and [MKTSEGMENTATION].[MAILDATE] is null))
        and ((@SHOWUNSCHEDULED is null) or (@SHOWUNSCHEDULED = 1) or (@SHOWUNSCHEDULED = 0 and [MKTSEGMENTATION].[MAILDATE] is not null))
    order by [MKTSEGMENTATION].[MAILDATE] desc, [MKTSEGMENTATION].[ACTIVATEDATE] desc, [MKTSEGMENTATION].[DATEADDED] desc, [MKTSEGMENTATION].[NAME] asc;

  return 0;