USP_SEARCHLIST_MKTMAILINGHEIRARCHY

CREATE procedure dbo.USP_SEARCHLIST_MKTMAILINGHEIRARCHY
(
  @NAME nvarchar(max) = null,
  @PLAN bit = 1,
  @APPEAL bit = 1,
  @MAILING bit = 1,
  @MAXROWS smallint = 500,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEID uniqueidentifier = null
)
as
  set nocount on;

set @NAME = ('%' + @NAME + '%');

with [PLANS]([ID], [HIERARCHY], [IDTREE], [SITEID]) as
(
  select 
    [MKTMARKETINGPLANITEM].[ID], 
    convert(nvarchar(max), [MKTMARKETINGPLANITEM].[NAME]) as [HIERARCHY], 
    convert(nvarchar(max), [MKTMARKETINGPLANITEM].[ID]) as [IDTREE],
    [MKTMARKETINGPLAN].[SITEID]
  from 
    dbo.[MKTMARKETINGPLANITEM]
    inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MKTMARKETINGPLANITEM].[MARKETINGPLANID]
  where 
    [MKTMARKETINGPLANITEM].[PARENTMARKETINGPLANITEMID] is null
    and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTMARKETINGPLAN].[SITEID]) = 1
  union all
  select 
    [MKTMARKETINGPLANITEM].[ID], 
    convert(nvarchar(max),[PLANS].[HIERARCHY] + '\' + [MKTMARKETINGPLANITEM].[NAME]) as [HIERARCHY], 
    convert(nvarchar(max),[PLANS].[IDTREE] + '\' + convert(nvarchar(36),[MKTMARKETINGPLANITEM].[ID])),
    [MKTMARKETINGPLAN].[SITEID]
  from 
    dbo.[MKTMARKETINGPLANITEM]
    inner join [PLANS] on [MKTMARKETINGPLANITEM].[PARENTMARKETINGPLANITEMID] = [PLANS].[ID]
    inner join dbo.[MKTMARKETINGPLAN] on [MKTMARKETINGPLAN].[ID] = [MKTMARKETINGPLANITEM].[MARKETINGPLANID]
  where
    dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTMARKETINGPLAN].[SITEID]) = 1
)
select [ID], [HIERARCHY], [TYPE], [SITE], [SITEID]
from (
  select 
    'PLANITEM|' + convert(nvarchar(36),[P1].[ID]) as [ID], 
    [P1].[HIERARCHY],
    'Marketing plan' as [TYPE],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([P1].[SITEID]) as [SITE],
    [P1].[SITEID]
  from [PLANS] as [P1]
  where [P1].[ID] in 
    (select 
       [P1].[ID] 
     from 
       [PLANS] [P2] 
     where 
       [P2].[ID] in (select [MARKETINGPLANITEMID] from dbo.[MKTSEGMENTATION] where [MARKETINGPLANITEMID] is not null and [ACTIVE] = 1) 
       and charindex(convert(varchar(36),[P1].[ID]),[P2].[IDTREE]) > 0
    ) 
  and @PLAN = 1

  union all

  select 
    'MARKETING EFFORT|' + convert(nvarchar(36),[MKTSEGMENTATION].[ID]), 
    convert(nvarchar(max),[MKTSEGMENTATIONACTIVATE].[APPEALDESCRIPTION] + '\' + [MKTSEGMENTATION].[NAME]),
    'Marketing Effort',
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEGMENTATION].[SITEID]) as [SITE],
    [MKTSEGMENTATION].[SITEID]
  from dbo.[MKTSEGMENTATIONACTIVATE]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  where @MAILING = 1
  and [MKTSEGMENTATION].[MAILINGTYPECODE] <> 4
  and [MKTSEGMENTATION].[ACTIVE] = 1
  and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTSEGMENTATION].[SITEID]) = 1
  and [MKTSEGMENTATION].[COMMUNICATIONTYPECODE] = 0

  union all

  select distinct 
    'APPEAL|' + [MKTSEGMENTATIONACTIVATE].[APPEALID],
    [MKTSEGMENTATIONACTIVATE].[APPEALDESCRIPTION],
    'Appeal',
    dbo.UFN_TRANSLATIONFUNCTION_SITE_GETNAME([MKTSEGMENTATION].[SITEID]) as [SITE],
    [MKTSEGMENTATION].[SITEID]
  from dbo.[MKTSEGMENTATIONACTIVATE]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]
  where @APPEAL = 1
  and [MKTSEGMENTATION].[MAILINGTYPECODE] <> 4
  and [MKTSEGMENTATION].[ACTIVE] = 1
  and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTSEGMENTATION].[SITEID]) = 1
  and [MKTSEGMENTATION].[COMMUNICATIONTYPECODE] = 0
) as [RESULT]
where ([HIERARCHY] like @NAME  or @NAME is null)
  and ([SITEID] = @SITEID or @SITEID is null)
order by [HIERARCHY];

  return 0;