USP_DATALIST_MKTACKNOWLEDGEMENTEXPORTPROCESS

CREATE procedure dbo.[USP_DATALIST_MKTACKNOWLEDGEMENTEXPORTPROCESS]
(
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null    
)
as
  set nocount on

  declare @CURRENTDATE as datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

  select
    [MKTSEGMENTATIONEXPORTPROCESS].[ID],
    cast(null as uniqueidentifier) as [ID2], -- unused field, leave in for backwards compatibility

    [MKTSEGMENTATION].[ID] as [SEGMENTATIONID],
    [MKTSEGMENTATION].[NAME],
    [MKTSEGMENTATIONEXPORTPROCESS].[DESCRIPTION],
    [MKTSEGMENTATION].[IDINTEGER],
    [MKTSEGMENTATION].[CODE],
    [MKTSEGMENTATION].[MAILDATE],
    [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[NAME] as [ACKNOWLEDGEMENTPROCESSNAME],
    [MKTSEGMENTATION].[DATEADDED] as [PROCESSDATE],
    case
       --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

       when cast([MKTSEGMENTATION].[DATEADDED] as date) = dateadd(day, 0, @CURRENTDATE) then 'Today'
       when cast([MKTSEGMENTATION].[DATEADDED] as date) = dateadd(day, -1, @CURRENTDATE) then 'Yesterday'
       when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -6, @CURRENTDATE) then 'Last week'             
       when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -13, @CURRENTDATE) then 'Two weeks ago'
       when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -20, @CURRENTDATE) then 'Three weeks ago'
       when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -30, @CURRENTDATE) then 'Last month'
       else 'Older'
    end [PROCESSDATETEXT],
    case
       --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

       when cast([MKTSEGMENTATION].[DATEADDED] as date) = dateadd(day, 0, @CURRENTDATE) then 0
       when cast([MKTSEGMENTATION].[DATEADDED] as date) = dateadd(day, -1, @CURRENTDATE) then 1
       when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -6, @CURRENTDATE) then 2             
       when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -13, @CURRENTDATE) then 3
       when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -20, @CURRENTDATE) then 4
       when [MKTSEGMENTATION].[DATEADDED] >= dateadd(day, -30, @CURRENTDATE) then 5
       else 6
    end [PROCESSDATEORDER],
    convert(bit, case when [MKTSEGMENTATION].[ACTIVE] = 1 or 
                           ((select isnull(count([ID]), 0) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]) > 0 and 
                            dbo.[UFN_MKTSEGMENTATION_ISRECORDCOUNTCACHECURRENT]([MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID], 0) = 1 and
                            dbo.[UFN_MKTSEGMENTATION_ISCALCULATING]([MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]) = 0 and
                            dbo.[UFN_MKTSEGMENTATION_ISACTIVATING]([MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]) = 0)
                      then 1 else 0 end) as [RUNNABLE]
  from dbo.[MKTSEGMENTATIONEXPORTPROCESS]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID]
  inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[SEGMENTATIONID]=[MKTSEGMENTATION].[ID]
  left outer join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[ID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] 
  left outer join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESS] on [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[PARAMETERSETID] 
  left outer join dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] on [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID] = [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ACKNOWLEDGEMENTMAILINGTEMPLATEID]
  where [MKTSEGMENTATION].[ISHISTORICAL] = 0
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTSEGMENTATION].[SITEID] or (SITEID is null and [MKTSEGMENTATION].[SITEID] is null)))
  order by [MKTSEGMENTATION].[NAME];

  return 0;