USP_MKTSEGMENTATIONACTIVATEKPI_GETFIELDS

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_GETFIELDS]
(
  @SEGMENTATIONID uniqueidentifier,
  @SELECTEDONLY bit,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @MAILINGTYPECODE tinyint;
  declare @KPIRECORDTYPE uniqueidentifier;
  declare @KPINAMESUFFIX nvarchar(50);
  declare @MEMBERSHIPKPINAMESUFFIX nvarchar(50);
  declare @ACTIVE bit;
  declare @DEFAULTS table([KPICATALOGID] uniqueidentifier primary key);

  select 
    @MAILINGTYPECODE = [MAILINGTYPECODE]
  from dbo.[MKTSEGMENTATION]
  where [ID] = @SEGMENTATIONID;

  --Get the KPI record type and set the name suffix to remove from the names, so the names don't look cluttered in the activate UI...

  set @KPIRECORDTYPE = dbo.[UFN_RECORDTYPE_GETIDBYNAME]('Marketing Effort');
  set @KPINAMESUFFIX = ' for Marketing Effort';
  set @MEMBERSHIPKPINAMESUFFIX = ' for Membership Effort';
  set @ACTIVE = (case when @SEGMENTATIONID is null then 0 else dbo.[UFN_MKTSEGMENTATION_ISACTIVE](@SEGMENTATIONID) end);

  /* If using a communication template, load the lock settings from the template */
  if exists(select top 1 1 from dbo.[MKTCOMMUNICATIONTEMPLATE] where [MKTSEGMENTATIONID] = @SEGMENTATIONID)
    begin
      /* Get KPIs from the activate table and also grab lock information from template */
      declare @TEMPLATETYPECODE tinyint = (select [TEMPLATETYPECODE] from dbo.[MKTCOMMUNICATIONTEMPLATE] where [MKTSEGMENTATIONID] = @SEGMENTATIONID);
      select
        [MKTSEGMENTATIONACTIVATEKPI].[KPICATALOGID] as [ID],
        cast(1 as bit) as [SELECTED],
        case when [KPICATALOG].[NAME] like '%' + @KPINAMESUFFIX
             then left([KPICATALOG].[NAME], len([KPICATALOG].[NAME]) - len(@KPINAMESUFFIX))
             when [KPICATALOG].[NAME] like '%' + @MEMBERSHIPKPINAMESUFFIX
             then left([KPICATALOG].[NAME], len([KPICATALOG].[NAME]) - len(@MEMBERSHIPKPINAMESUFFIX))
             else [KPICATALOG].[NAME] 
        end as [NAME],
        [KPICATALOG].[GOALTYPECODE],
        cast(1 as bit) as [DEFAULT],
        cast(isnull((select [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[LOCKED] from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTKPI] inner join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID] and [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = @SEGMENTATIONID where [KPICATALOGID] = [KPICATALOG].[ID]),0) as bit) as [LOCKED],
        @TEMPLATETYPECODE as [TEMPLATETYPECODE]
      from dbo.[MKTSEGMENTATIONACTIVATEKPI]
      inner join dbo.[KPICATALOG] on [MKTSEGMENTATIONACTIVATEKPI].[KPICATALOGID] = [KPICATALOG].[ID]
      where [MKTSEGMENTATIONACTIVATEKPI].[SEGMENTATIONID] = @SEGMENTATIONID
      order by [KPICATALOG].[NAME]
    end
  else
    begin
      /* Insert default kpis */
      insert into @DEFAULTS
        select [KPICATALOGID]
        from dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI]
        where [MAILINGTYPECODE] = @MAILINGTYPECODE;

      select
        [KPICATALOG].[ID],
        cast(case @ACTIVE
               when 1 
               then
                 case when exists(select top 1 1 from dbo.[MKTSEGMENTATIONACTIVATEKPI] where [KPICATALOGID] = [KPICATALOG].[ID] and [SEGMENTATIONID] = @SEGMENTATIONID)
                      then 1
                      else 0
                 end
               when 0
               then
                 case @SELECTEDONLY
                   when 0 then
                     case when exists(select top 1 1 from @DEFAULTS as [D] where [D].[KPICATALOGID] = [KPICATALOG].[ID])
                          then 1
                          else 0
                     end
                   when 1 then
                     case when exists(select top 1 1 from dbo.[MKTSEGMENTATIONACTIVATEKPI] where [KPICATALOGID] = [KPICATALOG].[ID] and [SEGMENTATIONID] = @SEGMENTATIONID)
                          then 1
                          else 0
              end
                 end
             end as bit) as [SELECTED],
        case when [KPICATALOG].[NAME] like '%' + @KPINAMESUFFIX
             then left([KPICATALOG].[NAME], len([KPICATALOG].[NAME]) - len(@KPINAMESUFFIX))
             when [KPICATALOG].[NAME] like '%' + @MEMBERSHIPKPINAMESUFFIX
             then left([KPICATALOG].[NAME], len([KPICATALOG].[NAME]) - len(@MEMBERSHIPKPINAMESUFFIX))
             else [KPICATALOG].[NAME] 
        end as [NAME],
        [KPICATALOG].[GOALTYPECODE],
        cast (case when exists(select 1 from @DEFAULTS as [D] where [D].[KPICATALOGID] = [KPICATALOG].[ID])
                  then 1
                  else 0
              end as bit) as [DEFAULT],
        cast(0 as bit) as [LOCKED],
        cast(0 as tinyint) as [TEMPLATETYPECODE]
      from dbo.[KPICATALOG]
      where 
        [KPICATALOG].[CONTEXTRECORDTYPEID] = @KPIRECORDTYPE
        and (@CURRENTAPPUSERID is null or dbo.[UFN_SECURITY_APPUSER_GRANTED_KPI](@CURRENTAPPUSERID, [KPICATALOG].[ID]) = 1)
      and 
        (
        [SPECXML].value(
        'declare namespace bbafx="bb_appfx_kpi";
         declare namespace c="bb_appfx_commontypes";
         (bbafx:KpiSpec/c:MetaTags/@MarketingMembershipMailingKPI)[1]', 
        'char(1)') is null
        or
        [SPECXML].value(
        'declare namespace bbafx="bb_appfx_kpi";
         declare namespace c="bb_appfx_commontypes";
         (bbafx:KpiSpec/c:MetaTags/@MarketingMembershipMailingKPI)[1]', 
        'char(1)') = (case when @MAILINGTYPECODE = 2 then '1' else '0' end)
        )
      order by [KPICATALOG].[NAME];

    end

  return 0;