UFN_QUERY_MKTCOMMUNICATIONEFFORTS

CREATE function dbo.[UFN_QUERY_MKTCOMMUNICATIONEFFORTS]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null
)
returns table as
return 
(

  with [APPEALS_CTE] ([SEGMENTATIONID], [APPEALNAME]) as
  (
    select
      [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID],
      (case when [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0' then [APPEAL].[NAME] else [MKTSEGMENTATIONACTIVATE].[APPEALDESCRIPTION] end) as [APPEALNAME]
    from dbo.[MKTSEGMENTATIONACTIVATE]
    left join dbo.[APPEAL] on [APPEAL].[ID] = nullif([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID], '')
  )
  select
    [MKTSEGMENTATION].[ID],
    [MKTSEGMENTATION].[NAME],
    [PARENTTEMPLATE].[NAME] as [TEMPLATE],
    [MKTSEGMENTATION].[CHANNEL] as [CHANNEL],
    (case [EFFORTSTATUS].[STATUSCODE]
       when 1 then 'Setup started'
       when 2 then 'Counts generated'
       when 3 then 'File exported'
       when 4 then 'Activated'
       when 5 then 'Past launch date'
     end) as [STATUS],
    (case [EFFORTSTATUS].[STATUSCODE]
       when 1 then 'CATALOG:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.status_1.png'
       when 2 then 'CATALOG:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.status_2.png'
       when 3 then 'CATALOG:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.status_3.png'
       when 4 then 'CATALOG:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.status_4.png'
       when 5 then 'CATALOG:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.status_4.png'
     end) as [STATUSIMAGE],
    [MKTSEGMENTATION].[DUEDATE],
    [MKTSEGMENTATION].[MAILDATE] as [LAUNCHDATE],
    [SITE].[NAME] as [SITE],
    [MKTSEGMENTATION].[DESCRIPTION],
    [MKTSEGMENTATION].[CODE],

    --APPEAL

    (case when (select count(*) from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) > 1 then
       --Get a comma delimited list of appeals

       isnull(stuff((
         select ', ' + [APPEALNAME]
         from [APPEALS_CTE]
         where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
         for xml path(''), type
       ).value('.', 'varchar(max)'), 1, 2, ''), '')
     else
       (select top 1 [APPEALNAME] from [APPEALS_CTE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID])
     end) as [APPEAL],

    (select count(*) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [NUMSEGMENTS],
    (select count(*) from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [NUMPACKAGES],
    [MKTSEGMENTATION].[DATEADDED] as [CREATEDDATE],

    [EFFORTSTATUS].[CALCULATEDATE],
    [EFFORTSTATUS].[CALCULATEPROCESSID],
    [EFFORTSTATUS].[EXPORTDATE],
    [EFFORTSTATUS].[EXPORTPROCESSID],
    [EFFORTSTATUS].[ACTIVATEDATE],
    [EFFORTSTATUS].[ACTIVATEPROCESSID],
    [EFFORTSTATUS].[DATEREFRESHED],
    [EFFORTSTATUS].[REFRESHPROCESSID],

    [MKTSEGMENTATION].[ACTIVE],
    [MKTSEGMENTATION].[ISHISTORICAL],

    --ONLYHASVENDORMANAGEDLISTSEGMENTS

    cast((case when exists(select *
                      from dbo.[MKTSEGMENTATIONSEGMENT]
                      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
                      inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
                      and [MKTSEGMENTLIST].[TYPECODE] = 1)  --vendor managed only

      and not exists(select *
                     from dbo.[MKTSEGMENTATIONSEGMENT]
                     inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
                     left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
                     where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
                     and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or [MKTSEGMENTLIST].[TYPECODE] = 0))  --all other segments including imported lists

      then 1 else 0 end) as bit) as [ONLYHASVENDORMANAGEDLISTSEGMENTS],

    --GIFTSOURCESDEFINED

    cast((case when [MKTSEGMENTATION].[ACTIVE] = 0 then
      (select
         (case when count(*) = 0 then 
           (case when (select count(*) from dbo.[MKTGIFTRECORDSOURCE]) > 0 then 1 else 0 end)
          else
           0
          end)
       from dbo.[MKTSEGMENTATIONSEGMENT]
       inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
       left outer join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
       where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
       and [MKTGIFTRECORDSOURCE].[ID] is null
      )
     else
      1
     end
    ) as bit) as [GIFTSOURCESDEFINED],

    [EFFORTSTATUS].[ISCALCULATING],
    [EFFORTSTATUS].[ISACTIVATING],

    --RECORDCOUNTCACHEISCURRENT

    cast((case when [MKTSEGMENTATION].[ACTIVE] = 0 and exists(
      select *
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
      left join dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] on [MKTSEGMENTATIONSEGMENTCACHEINFO].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
      and (
        [MKTSEGMENT].[SEGMENTTYPECODE] <> 2 -- ignore list segments, they are always current

        or
        ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 and [MKTSEGMENTLIST].[TYPECODE] = 0)
      )
      and [MKTSEGMENT].[ISHISTORICAL] = 0  -- ignore historical

      and (
        [MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] is null
        or
        [MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] < [MKTSEGMENTATIONSEGMENT].[DATECHANGED]
        or
        [MKTSEGMENTATIONSEGMENTCACHEINFO].[RECORDCOUNTDATECACHED] < (
          select max([T].[DATE])
          from (
            --Get the date changed for all the selections in the universe/exclusions...

            (select max([DATECHANGED]) as [DATE] from dbo.[MKTSEGMENTATIONFILTERSELECTION] where [SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID])
            union all
            --Get the last date that a selection in the universe/exclusions was deleted...

            (select max([AUDITDATE]) as [DATE] from dbo.[MKTSEGMENTATIONFILTERSELECTIONAUDIT] where [SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [AUDITTYPECODE] = 2)
            union all
            --Get the date changed for all the previous mailing exclusions...

            (select max([DATECHANGED]) as [DATE] from dbo.[MKTSEGMENTATIONFILTERSEGMENTATION] where [SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID])
            union all
            --Get the last date that a previous mailing exclusion was deleted...

            (select max([AUDITDATE]) as [DATE] from dbo.[MKTSEGMENTATIONFILTERSEGMENTATIONAUDIT] where [SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [AUDITTYPECODE] = 2)
          ) as [T]
        )
      )
    ) then 0 else 1 end) as bit) as [RECORDCOUNTCACHEISCURRENT],

    [APPUSER].[DISPLAYNAME] as [OWNER],

    cast((
      select top 1
        nullif([APPEALSYSTEMID], '')
      from dbo.[MKTSEGMENTATIONACTIVATE]
      where [SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
      and [RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0'
    ) as uniqueidentifier) as [BBECAPPEALID],

    cast((case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED] = 1 
             and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATALOCKED] = 1 
                and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNMARKETINGEXCLUSIONSREPORTLOCKED] = 1
          then 1 else 0 end) as bit) as [ALLCALCULATIONOPTIONSLOCKED],

    cast((case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ACTIVATEOPTIONSLOCKED] = 1 
                and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[APPEALLOCKED] = 1 
                and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATAACTIVATELOCKED] = 1
                and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTAFTERACTIVATELOCKED] = 1
          then 1 else 0 end) as bit) as [ALLACTIVATEOPTIONSLOCKED],

    cast((case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTDESCRIPTIONLOCKED] = 1 
                and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[MAILEXPORTDEFINITIONIDLOCKED] = 1 
                and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EMAILEXPORTDEFINITIONIDLOCKED] = 1
                and [MKTCOMMUNICATIONTEMPLATEDEFAULT].[PHONEEXPORTDEFINITIONIDLOCKED] = 1
          then 1 else 0 end) as bit) as [ALLEXPORTOPTIONSLOCKED],

    [MKTSEGMENTATION].[IDINTEGER],

    cast((case
      when [MKTSEGMENTATION].[DATEADDED] < [PARENTTEMPLATE].[DATECHANGED]
        then 1
      else 0 end) as bit) as [TEMPLATEHASCHANGED],

    [PARENTTEMPLATE].[ISACTIVE] as [TEMPLATEISACTIVE],
    [MKTSEGMENTATION].[MARKETINGPLANITEMID],
    [PLAN].[PATH] as [PLAN]

    /*#EXTENSION*/

  from dbo.[MKTSEGMENTATION]
  left join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTCOMMUNICATIONTEMPLATE] as [PARENTTEMPLATE] on [PARENTTEMPLATE].[ID] = [MKTCOMMUNICATIONTEMPLATE].[PARENTCOMMUNICATIONTEMPLATEID]
  left join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [PARENTTEMPLATE].[ID]
  left join dbo.[SITE] on [SITE].[ID] = [MKTSEGMENTATION].[SITEID]
  left join dbo.[APPUSER] on [APPUSER].[ID] = [MKTSEGMENTATION].[OWNERID]
  left join dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETSTATUSINFO_BULK]() as [EFFORTSTATUS] on [EFFORTSTATUS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  outer apply dbo.[UFN_MKTMARKETINGPLANITEM_PATH_BULK]([MKTSEGMENTATION].[MARKETINGPLANITEMID], 1) as [PLAN]

  where 
    [MKTSEGMENTATION].[MAILINGTYPECODE] = 0    -- direct marketing efforts only

    and [MKTSEGMENTATION].[COMMUNICATIONTYPECODE] = 0    -- no BBEC appeal mailings

    and [MKTSEGMENTATION].[PARENTSEGMENTATIONID] is null --no test efforts


    /* Site security */
    and (
      (select [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID) = 1
      or
      exists(
        select top 1 *
        from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, '09F8C47D-72B1-44A0-9049-3F1C0EEB54F1', 10)
        where [SITEID] = [MKTSEGMENTATION].[SITEID] or ([SITEID] is null and [MKTSEGMENTATION].[SITEID] is null)
      )
    )

    and
    (
      @SITEFILTERMODE = 0
      or [MKTSEGMENTATION].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
    )

)