USP_DATALIST_APPEALMAILINGSELECTIONSBYQUERYFOLDER

CREATE procedure dbo.USP_DATALIST_APPEALMAILINGSELECTIONSBYQUERYFOLDER
(
    @FOLDERID uniqueidentifier,    
    @RECORDTYPEID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @IDSETTOEXCLUDE uniqueidentifier = null
)
as
set nocount on;    

set @FOLDERID = isnull(@FOLDERID,'00000000-0000-0000-0000-000000000000')

declare @RESULTS table
(
    ID uniqueidentifier,
    NAME nvarchar(300),
    TYPECODE integer
)

insert into @RESULTS (ID, NAME, TYPECODE)
select
    ID,
    NAME,
    0
from 
    dbo.UFN_ADHOCQUERYFOLDERS_GETUSERSFOLDERS(@CURRENTAPPUSERID)    
where
    isnull(PARENTFOLDERID,'00000000-0000-0000-0000-000000000000') = @FOLDERID
order by
    DISPLAYORDER;

insert into @RESULTS (ID, NAME, TYPECODE)
select distinct
    IDSETREGISTER.ID,
    IDSETREGISTER.NAME,
    1 as TYPE
from 
    dbo.IDSETREGISTER 
    left outer join dbo.IDSETREGISTERADHOCQUERY on IDSETREGISTER.ID = IDSETREGISTERADHOCQUERY.IDSETREGISTERID
    left outer join dbo.ADHOCQUERY on IDSETREGISTERADHOCQUERY.ADHOCQUERYID = ADHOCQUERY.ID

    -- Filter by appeal mailing

    inner join 
        dbo.MKTSEGMENTSELECTION on MKTSEGMENTSELECTION.SELECTIONID = IDSETREGISTER.ID
    inner join
        dbo.MKTSEGMENT on MKTSEGMENT.ID = MKTSEGMENTSELECTION.SEGMENTID
    inner join 
        dbo.MKTSEGMENTATIONSEGMENT on MKTSEGMENTATIONSEGMENT.SEGMENTID = MKTSEGMENT.ID
    inner join
        dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = MKTSEGMENTATIONSEGMENT.SEGMENTATIONID
    inner join
        dbo.APPEALMAILING on APPEALMAILING.ID = MKTSEGMENTATION.ID
where
    IDSETREGISTER.ACTIVE = 1 and
    IDSETREGISTER.ISSYSTEM = 0 and
    IDSETREGISTER.DBOBJECTNAME not in(select SQLFUNCTIONCATALOG.FUNCTIONNAME from dbo.SQLFUNCTIONCATALOG) and
    GROUPNAME is null and
    ISNULL(ADHOCQUERY.FOLDERID,'00000000-0000-0000-0000-000000000000') = @FOLDERID and
    IDSETREGISTER.RECORDTYPEID = @RECORDTYPEID and
    IDSETREGISTER.ID not in (select IDSETREGISTERID from dbo.IDSETREGISTERMERGE) and
    IDSETREGISTER.ID <> isnull(@IDSETTOEXCLUDE,'00000000-0000-0000-0000-000000000000') and

    -- Appeal mailings for this year and next year

    (MKTSEGMENTATION.MAILDATE is not null) and
    (year(MKTSEGMENTATION.MAILDATE) between year(getdate()) and (year(getdate()) + 1))

union

select distinct
    IDSETREGISTER.ID,
    IDSETREGISTER.NAME,
    1 as TYPE
from 
    dbo.IDSETREGISTER 
    left outer join dbo.IDSETREGISTERADHOCQUERY on IDSETREGISTER.ID = IDSETREGISTERADHOCQUERY.IDSETREGISTERID
    left outer join dbo.ADHOCQUERY on IDSETREGISTERADHOCQUERY.ADHOCQUERYID = ADHOCQUERY.ID

    -- For appeal mailings not yet started

    inner join 
        APPEALMAILINGSETUPSELECTION on APPEALMAILINGSETUPSELECTION.SELECTIONID = IDSETREGISTER.ID
    inner join
        APPEALMAILINGSETUP on APPEALMAILINGSETUP.ID = APPEALMAILINGSETUPSELECTION.APPEALMAILINGSETUPID
    inner join
        MKTSEGMENTATION on MKTSEGMENTATION.ID = APPEALMAILINGSETUP.ID
where
    IDSETREGISTER.ACTIVE = 1 and
    IDSETREGISTER.DBOBJECTNAME not in(select SQLFUNCTIONCATALOG.FUNCTIONNAME from dbo.SQLFUNCTIONCATALOG) and
    GROUPNAME is null and
    ISNULL(ADHOCQUERY.FOLDERID,'00000000-0000-0000-0000-000000000000') = @FOLDERID and
    IDSETREGISTER.RECORDTYPEID = @RECORDTYPEID and
    IDSETREGISTER.ID not in (select IDSETREGISTERID from dbo.IDSETREGISTERMERGE) and
    IDSETREGISTER.ID <> isnull(@IDSETTOEXCLUDE,'00000000-0000-0000-0000-000000000000') and

    -- Appeal mailings for this year and next year

    (MKTSEGMENTATION.MAILDATE is not null) and
    (year(MKTSEGMENTATION.MAILDATE) between year(getdate()) and (year(getdate()) + 1))

ORDER BY
  3,2;

select
    ID,
    NAME,
    TYPECODE
from 
    @RESULTS;