USP_DATALIST_MKTSEGMENTWHITEMAIL

CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTWHITEMAIL]
(
  @SEGMENTID uniqueidentifier = null,
  @SOURCECODE nvarchar(50) = null,
  @GROUPID uniqueidentifier = null,
  @SEGMENTCATEGORYCODEID uniqueidentifier = null,
  @INCLUDEINACTIVE bit = 0,
  @CURRENCYCODE tinyint = 0,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @INACTIVEFILTER bit = 0
)
as
  set nocount on;
  declare @NOW datetime = getdate();
  declare @YESTERDAY datetime = dateadd(d, -1, @NOW);
  declare @TOMORROW datetime = dateadd(d, 1, @NOW);

  -- To maintain backwards compatibility, override the inactivefilter filter if INCLUDEINACTIVE is set

  if @INCLUDEINACTIVE = 1
    set @INACTIVEFILTER = null;

with [SEGMENTACTIVESTATUS] ([ID], [ISACTIVE]) as 
(
  select 
    [ID],
    case when [MKTSEGMENTWHITEMAIL].[STATUSCODE] = 0 or ([MKTSEGMENTWHITEMAIL].[STATUSCODE] = 1 
                                                         and @NOW between isnull([MKTSEGMENTWHITEMAIL].[ACTIVEFROM], @YESTERDAY) 
                                                         and isnull(dateadd(d, 1, [MKTSEGMENTWHITEMAIL].[ACTIVETO]), @TOMORROW)) then 1 else 0 end as [ISACTIVE]
  from dbo.[MKTSEGMENTWHITEMAIL]
)
  select
    [MKTSEGMENT].[ID],
    dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTWHITEMAIL].[ID], null, null) as [SOURCECODE],
    [MKTSEGMENT].[NAME],
    [MKTSEGMENTGROUP].[ID] as [GROUPID],
    [MKTSEGMENTGROUP].[NAME] as [GROUPNAME],
    [MKTSEGMENTCATEGORYCODE].[DESCRIPTION] as [SEGMENTCATEGORYCODE],
    [SEGMENTACTIVESTATUS].[ISACTIVE] as [ACTIVE],
    [MKTSEGMENTWHITEMAILCACHE].[RESPONDERS],
    [MKTSEGMENTWHITEMAILCACHE].[RESPONSES],
    case @CURRENCYCODE when 1 then [MKTSEGMENTWHITEMAILCACHE].[ORGANIZATIONTOTALGIFTAMOUNT] else [MKTSEGMENTWHITEMAILCACHE].[TOTALGIFTAMOUNT] end as [TOTALGIFTAMOUNT],
    case @CURRENCYCODE when 1 then [MKTSEGMENTWHITEMAILCACHE].[ORGANIZATIONAVERAGEGIFTAMOUNT] else [MKTSEGMENTWHITEMAILCACHE].[AVERAGEGIFTAMOUNT] end as [AVERAGEGIFTAMOUNT],
    [MKTSEGMENTWHITEMAILREFRESHPROCESS].[DATEREFRESHED],
    [MKTSEGMENT].[DESCRIPTION],
    [MKTSEGMENTWHITEMAILREFRESHPROCESS].[ID] as [SEGMENTWHITEMAILREFRESHPROCESSID],
    case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() else [MKTSEGMENT].[BASECURRENCYID] end as [BASECURRENCYID],
    isnull([SITE].[NAME], '') as [SITE]
  from dbo.[MKTSEGMENTWHITEMAIL]
  inner join [SEGMENTACTIVESTATUS] on [SEGMENTACTIVESTATUS].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
  inner join dbo.[MKTSEGMENTWHITEMAILREFRESHPROCESS] on [MKTSEGMENTWHITEMAILREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENTWHITEMAIL].[ID]
  left outer join dbo.[MKTSEGMENTWHITEMAILCACHE] on [MKTSEGMENTWHITEMAILCACHE].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
  left outer join dbo.[MKTGROUPSEGMENTS] on [MKTSEGMENT].[ID] = [MKTGROUPSEGMENTS].[SEGMENTID]
  left outer join dbo.[MKTSEGMENTGROUP] on [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = [MKTSEGMENTGROUP].[ID]
  left outer join dbo.[MKTSEGMENTCATEGORYCODE] on [MKTSEGMENTCATEGORYCODE].[ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]
  left outer join dbo.[SITE] on [SITE].[ID] = [MKTSEGMENT].[SITEID]
  where 
    (@SEGMENTID is null or [MKTSEGMENT].[ID] = @SEGMENTID)
    and (@SOURCECODE is null or dbo.[UFN_MKTSOURCECODE_BUILDCODE]([MKTSEGMENTWHITEMAIL].[ID], null, null) like @SOURCECODE + '%')
    and (@GROUPID is null or [MKTSEGMENTGROUP].[ID] = @GROUPID)
    and (@SEGMENTCATEGORYCODEID is null or [MKTSEGMENT].[SEGMENTCATEGORYCODEID] = @SEGMENTCATEGORYCODEID)
    and (@INACTIVEFILTER is null 
         or (@INACTIVEFILTER = 0 and [SEGMENTACTIVESTATUS].[ISACTIVE] = 1)
         or (@INACTIVEFILTER = 1 and [SEGMENTACTIVESTATUS].[ISACTIVE] = 0))
    and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTSEGMENT].[SITEID] or (SITEID is null and [MKTSEGMENT].[SITEID] is null)))
    and (@SITEFILTERMODE = 0 or [MKTSEGMENT].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  order by [MKTSEGMENTGROUP].[NAME], [MKTGROUPSEGMENTS].[SEQUENCE], [MKTSEGMENT].[NAME];

  return 0;