UFN_QUERY_MKTCOMMUNICATIONEFFORTKPISLIST

CREATE function dbo.[UFN_QUERY_MKTCOMMUNICATIONEFFORTKPISLIST]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
returns @KPI_DETAIL table
(
  [KPIINSTANCEID] uniqueidentifier,
  [NAME] nvarchar(255),
  [GOALTYPECODE] tinyint,
  [KPIVALUEMONEY] decimal(20,4),
  [KPIVALUENUMBER] decimal(20,4),
  [KPIVALUEPERCENT] decimal(20,4),    
  [GOALVALUEMONEY] decimal(20,4),
  [GOALVALUENUMBER] decimal(20,4),
  [GOALVALUEPERCENT] decimal(20,4),    
  [YELLOWTARGETMONEY] decimal(20,4),
  [YELLOWTARGETNUMBER] decimal(20,4),
  [YELLOWTARGETPERCENT] decimal(20,4),    
  [GREENTARGETMONEY] decimal(20,4),
  [GREENTARGETNUMBER] decimal(20,4),
  [GREENTARGETPERCENT] decimal(20,4),    
  [GOALSTATUSIMAGE] nvarchar(255),
  [VARIANCE] decimal(20,4),
  [CLOSESTASOFDATE] datetime,    
  [USERHASRIGHTSTOKPI] bit,    
  [CURRENCYID] uniqueidentifier,
  [ISLOCKED] bit,
  [LOCKEDPIC] nvarchar(255)
)
with execute as caller
as
begin

  declare @ISSYSADMIN bit = (select [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID);
  declare @EFFORTNAME nvarchar(100) = (select [NAME] from dbo.[MKTSEGMENTATION] where [ID] = @ID);

  with 
  xmlnamespaces ('bb_appfx_dataforms' as [DFI]),
  [USERKPIS] as 
  (
    select distinct [KPIINSTANCEID]
    from
  (select 
    distinct [SYSTEMROLEKPIINSTANCE].[KPIINSTANCEID]
  from dbo.[SYSTEMROLEKPIINSTANCE]
  inner join dbo.[SYSTEMROLEAPPUSER] on [SYSTEMROLEKPIINSTANCE].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
  where [SYSTEMROLEAPPUSER].[APPUSERID] = @CURRENTAPPUSERID

  union all

  select
    [KPIINSTANCE].[ID]
  from dbo.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_KPI] [SECURITYVIEW]
  inner join dbo.[KPIINSTANCE] on [SECURITYVIEW].[KPICATALOGID] = [KPIINSTANCE].[KPICATALOGID]
  where [SECURITYVIEW].[APPUSERID] = @CURRENTAPPUSERID
  group by [KPIINSTANCE].[ID]
  having  min(cast([SECURITYVIEW].[GRANTORDENY] as tinyint)) = 1) as [KPI]
  )

  insert into @KPI_DETAIL
  (
    [KPIINSTANCEID],
    [NAME],
    [GOALTYPECODE],
    [KPIVALUEMONEY],
    [KPIVALUENUMBER],
    [KPIVALUEPERCENT],
    [GOALVALUEMONEY],
    [GOALVALUENUMBER],
    [GOALVALUEPERCENT],
    [YELLOWTARGETMONEY],
    [YELLOWTARGETNUMBER],
    [YELLOWTARGETPERCENT],
    [GREENTARGETMONEY],
    [GREENTARGETNUMBER],
    [GREENTARGETPERCENT],
    [GOALSTATUSIMAGE],
    [VARIANCE],
    [CLOSESTASOFDATE],
    [USERHASRIGHTSTOKPI],
    [CURRENCYID],
    [ISLOCKED],
    [LOCKEDPIC]
  )
    select 
      [KPIINSTANCE].[ID] [KPIINSTANCEID],
      case when [KPIINSTANCE].[NAME] like @EFFORTNAME + ' - %'
            then right([KPIINSTANCE].[NAME], (len([KPIINSTANCE].[NAME]) - len(@EFFORTNAME)) - 3)
            else [KPIINSTANCE].[NAME] 
      end as [NAME],
      [KPICATALOG].[GOALTYPECODE],

      convert(money, case [KPICATALOG].[GOALTYPECODE] 
        when 0 then [HISTORY].[VALUE] 
        else null 
      end) [KPIVALUEMONEY],
      convert(bigint, case [KPICATALOG].[GOALTYPECODE] 
        when 1 then [HISTORY].[VALUE] 
        else null 
      end) [KPIVALUENUMBER],
      case [KPICATALOG].[GOALTYPECODE] 
        when 2 then [HISTORY].[VALUE]
        else null 
      end [KPIVALUEPERCENT],    

      convert(money, case [KPICATALOG].[GOALTYPECODE] 
        when 0 then [KPIINSTANCE].[GOALVALUE] 
        else null 
      end) [GOALVALUEMONEY],
      convert(bigint, case [KPICATALOG].[GOALTYPECODE] 
        when 1 then [KPIINSTANCE].[GOALVALUE] 
        else null 
      end) [GOALVALUENUMBER],
      case [KPICATALOG].[GOALTYPECODE] 
        when 2 then [KPIINSTANCE].[GOALVALUE] 
        else null 
      end [GOALVALUEPERCENT],

      convert(money, case [KPICATALOG].[GOALTYPECODE] 
        when 0 then case [KPIINSTANCE].[GOALAIMTYPECODE] 
          when 0 then [GOALUPWARDLOWVALUE]
          else [GOALDOWNWARDHIGHVALUE] 
          end
        else null 
      end) [YELLOWTARGETMONEY],
      convert(bigint, case [KPICATALOG].[GOALTYPECODE] 
        when 1 then case [KPIINSTANCE].[GOALAIMTYPECODE] 
          when 0 then [GOALUPWARDLOWVALUE] 
          else [GOALDOWNWARDHIGHVALUE] 
          end 
        else null
      end) [YELLOWTARGETNUMBER],    
      case [KPICATALOG].[GOALTYPECODE] 
        when 2 then case [KPIINSTANCE].[GOALAIMTYPECODE] 
          when 0 then [GOALUPWARDLOWVALUE] 
          else [GOALDOWNWARDHIGHVALUE] 
          end
        else null 
      end [YELLOWTARGETPERCENT],

      convert(money, case [KPICATALOG].[GOALTYPECODE] 
        when 0 then case [KPIINSTANCE].[GOALAIMTYPECODE] 
          when 0 then [GOALUPWARDMIDVALUE] 
          else [GOALDOWNWARDMIDVALUE] 
        end 
      end) [GREENTARGETMONEY],
      convert(bigint, case [KPICATALOG].[GOALTYPECODE] 
        when 1 then case [KPIINSTANCE].[GOALAIMTYPECODE] 
          when 0 then [GOALUPWARDMIDVALUE] 
          else [GOALDOWNWARDMIDVALUE] 
        end 
      end) [GREENTARGETNUMBER],
      case [KPICATALOG].[GOALTYPECODE] 
        when 2 then 
          case [KPIINSTANCE].[GOALAIMTYPECODE] 
            when 0 then [GOALUPWARDMIDVALUE] 
            else [GOALDOWNWARDMIDVALUE] 
          end 
      end [GREENTARGETPERCENT],

      case 
        when [HISTORY].[VALUE] is null then 'res:roadblank'
        else 
          case [KPIINSTANCE].[GOALAIMTYPECODE]
            when 0 then 
              case 
                when [HISTORY].[VALUE] < [GOALUPWARDMIDVALUE] then 'res:road0'
                when [HISTORY].[VALUE] < [GOALUPWARDLOWVALUE] then 'res:road1'
                else 'res:road2'
              end
            when 1 then 
              case 
                when [HISTORY].[VALUE] > [GOALDOWNWARDMIDVALUE] then 'res:road0'
                when [HISTORY].[VALUE] > [GOALDOWNWARDHIGHVALUE] then 'res:road1'
                else 'res:road2'
              end          
          end
      end [GOALSTATUSIMAGE],

      case 
        when [KPIINSTANCE].[GOALVALUE] <> 0 then 
          case [KPIINSTANCE].[GOALAIMTYPECODE]
            when 0 then ([HISTORY].[VALUE] - [KPIINSTANCE].[GOALVALUE]) / abs([KPIINSTANCE].[GOALVALUE])
            when 1 then -1 * (([HISTORY].[VALUE] - [KPIINSTANCE].[GOALVALUE]) / abs([KPIINSTANCE].[GOALVALUE]))
          end
        else 0
      end [VARIANCE],

      [HISTORY].[ASOFDATE] as [CLOSESTASOFDATE],    
      1 as [USERHASRIGHTSTOKPI],
      [KPIINSTANCE].PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="CURRENCYID"]/DFI:Value)[1]','varchar(36)') as [CURRENCYID],
      cast(isnull((select [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[LOCKED] from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTKPI] inner join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID] and [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = @ID where [KPICATALOGID] = [KPICATALOG].[ID]), 0) as bit) as [ISLOCKED],
      case
        when isnull((select [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[LOCKED] 
                      from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTKPI]
                        inner join dbo.[MKTCOMMUNICATIONTEMPLATE] on [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID] and [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = @ID
                        where [KPICATALOGID] = [KPICATALOG].[ID]),0) = 1 then 'catalog:Blackbaud.AppFx.Marketing.Catalog.dll,Blackbaud.AppFx.Marketing.Catalog.padlock_locked_16.png'
        else ''
      end as [LOCKEDPIC]
    from dbo.[KPIINSTANCE]
      inner join dbo.[KPICATALOG] on [KPIINSTANCE].[KPICATALOGID] = [KPICATALOG].[ID]
      outer apply (
        select top (1) [ID], [KPIINSTANCEID], [VALUE], [ASOFDATE]
        from dbo.[KPIINSTANCEHISTORY]
        where [KPIINSTANCEID] = [KPIINSTANCE].[ID]
        order by [ASOFDATE] desc
      ) as [HISTORY]
    where
      [KPIINSTANCE].[CONTEXTRECORDID] = cast(@ID as nvarchar(100)) and
      (@ISSYSADMIN = 1
        or exists
        (select [KPIINSTANCEID]
        from [USERKPIS]
        where [USERKPIS].[KPIINSTANCEID] = [KPIINSTANCE].[ID])
        )  --Security check

    order by [KPIINSTANCE].[NAME];

  return;

end;