USP_KPIINSTANCE_GETLISTBYAPPUSERID

CREATE procedure dbo.[USP_KPIINSTANCE_GETLISTBYAPPUSERID]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @ASOFDATE datetime = null,
  @ONLYSHOWINSTANCESWITHVALUES bit = 0,
  @MAXROWS int = 500,
  @SITEFILTERMODE tinyint = null,
  @SITESSELECTED xml = null,
  @GOALSTATUS int = null,
  @CATEGORY nvarchar(200) = null,
  @TYPEID uniqueidentifier = null,
  @CONTEXTRECORDTYPE nvarchar(50) = null,
  @CONTEXTRECORDID nvarchar(100) = null
)
as
  set nocount on;

  declare @LATESTTIME datetime;

  if @ASOFDATE is null set @ASOFDATE = getdate();

  set @LATESTTIME = dbo.[UFN_DATE_GETLATESTTIME](@ASOFDATE);

  declare @ISSYSADMIN bit;
  declare @SITEPRODUCTINSTALLED bit;

  set @ISSYSADMIN = dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID);
  set @SITEPRODUCTINSTALLED = dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('133f9bca-00f1-4007-9792-586b931340c6');

  select top (@MAXROWS)
    [KPIINSTANCE].[ID] as [KPIINSTANCEID],
    [KPIINSTANCE].[KPICATALOGID],
    [KPIINSTANCE].[NAME],
    [KPICATALOG].[GOALTYPECODE],
    [KPIINSTANCE].[GOALINDICATORTYPECODE],
    [KPIINSTANCE].[GOALAIMTYPECODE],
    [KPIINSTANCE].[GOALVALUE],
    [KPIINSTANCE].[GOALUPWARDLOWVALUE],
    [KPIINSTANCE].[GOALUPWARDMIDVALUE],
    [KPIINSTANCE].[GOALDOWNWARDMIDVALUE],
    [KPIINSTANCE].[GOALDOWNWARDHIGHVALUE],
    cast((case when [HISTORY].[VALUE] is null then 0 else 1 end) as bit) as [HASVALUE],
    [KPICATALOG].[GOALRETRIEVALTYPECODE],
    [HISTORY].[VALUE] as [KPIVALUE],
    [HISTORY].[ASOFDATE] as [CLOSESTASOFDATE],
    [KPIINSTANCE].[PARAMETERSXML],
    [RECORDTYPE].[NAME] as [CONTEXTRECORDTYPE],
    [KPIINSTANCE].[CONTEXTRECORDID],
    dbo.[UFN_SECURITY_APPUSER_GRANTED_KPI](@CURRENTAPPUSERID, [KPIINSTANCE].[KPICATALOGID]) as [USERHASRIGHTSTOKPI],
    [KPICATALOG].[DECIMALPLACES],
    case 
      when [KPIINSTANCE].[SITEFILTERENABLEDFORINSTANCE] = 1 
      then
      (
        select 
           dbo.[UDA_BUILDLIST]([SITE].[NAME]) as [SITES]
        from dbo.[KPIINSTANCESITE]
        inner join dbo.[SITE] on [KPIINSTANCESITE].[SITEID] = [SITE].[ID]
        where [KPIINSTANCESITE].[KPIINSTANCEID] = [KPIINSTANCE].[ID]
      )
      else ''
    end as [SITES],
    [KPICATALOG].[NAME] as [KPICATALOGNAME]
  from dbo.[KPIINSTANCE]
  inner join dbo.[KPICATALOG] on [KPIINSTANCE].[KPICATALOGID] = [KPICATALOG].[ID]
  left join dbo.[RECORDTYPE] on [KPICATALOG].[CONTEXTRECORDTYPEID] = [RECORDTYPE].[ID]
  outer apply (
    select top (1) [ID], [KPIINSTANCEID], [VALUE], [ASOFDATE]
    from dbo.[KPIINSTANCEHISTORY]
    where [KPIINSTANCEID] = [KPIINSTANCE].[ID]
    and [ASOFDATE] <= @LATESTTIME
    order by [ASOFDATE] desc
  ) as [HISTORY]
  where dbo.[UFN_SECURITY_APPUSER_GRANTED_KPIINSTANCE](@CURRENTAPPUSERID, [KPIINSTANCE].[ID]) = 1 
  and ((@ONLYSHOWINSTANCESWITHVALUES = 0 and [HISTORY].[ID] is null) or [HISTORY].[ID] is not null) 
  and
    (
      isnull(@SITEFILTERMODE, 0) = 0
      or exists
      (
        select top 1 1
        from dbo.[KPIINSTANCESITE]
        inner join dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) as [VALIDSITE] on [KPIINSTANCESITE].[SITEID] = [VALIDSITE].[SITEID]
        where [KPIINSTANCESITE].[KPIINSTANCEID] = [KPIINSTANCE].[ID]
      )
    )
    and 
    (
      [KPICATALOG].[HASSITEFILTER] = 0
      or [KPIINSTANCE].[SITEFILTERENABLEDFORINSTANCE] = 0
      or @SITEPRODUCTINSTALLED = 0
      or @ISSYSADMIN = 1
      or exists
      (
        select top 1 1
        from dbo.[SYSTEMROLEAPPUSER]
        where [SYSTEMROLEAPPUSER].[APPUSERID] = @CURRENTAPPUSERID
        -- make sure the kpi instance is permissioned for this role 

        and exists 
        (
          select top 1 1 
          from dbo.[SYSTEMROLEKPIINSTANCE]
          where [SYSTEMROLEKPIINSTANCE].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
          and [SYSTEMROLEKPIINSTANCE].[KPIINSTANCEID] = [KPIINSTANCE].[ID]
        )
        and
        (
          [SYSTEMROLEAPPUSER].[SECURITYMODECODE] = 0 -- all sites

          or
          (
            [SYSTEMROLEAPPUSER].[SECURITYMODECODE] = 1 -- no site

            and not exists (select top 1 1 from dbo.[KPIINSTANCESITE] where [KPIINSTANCEID] = [KPIINSTANCE].[ID])
          ) 
          or
          (
            [SYSTEMROLEAPPUSER].[SECURITYMODECODE] in (2, 3) -- records with one of these sites assigned or within a branch

            and exists
            (
              select top 1 1
              from dbo.[SITEPERMISSION]
              inner join dbo.[KPIINSTANCESITE] on [SITEPERMISSION].[SITEID] = [KPIINSTANCESITE].[SITEID]
              where [SITEPERMISSION].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
              and [SITEPERMISSION].[APPUSERID] = [SYSTEMROLEAPPUSER].[APPUSERID]
              and [KPIINSTANCESITE].[KPIINSTANCEID] = [KPIINSTANCE].[ID]
            )
          )
        )       
      )
    )
    and
    (
      @GOALSTATUS is null
      or
      (
        @GOALSTATUS = 0 -- red

        and
        (
          (
            [KPIINSTANCE].[GOALAIMTYPECODE] = 0 -- high is good

            and [HISTORY].[VALUE] < [KPIINSTANCE].[GOALUPWARDLOWVALUE]
          )
          or
          (
            [KPIINSTANCE].[GOALAIMTYPECODE] = 1 -- low is good

            and [HISTORY].[VALUE] > [KPIINSTANCE].[GOALDOWNWARDHIGHVALUE]
          )
        )
      )
      or
      (
        @GOALSTATUS = 1 -- yellow

        and
        (
          (
            [KPIINSTANCE].[GOALAIMTYPECODE] = 0 -- high is good

            and [HISTORY].[VALUE] >= [KPIINSTANCE].[GOALUPWARDLOWVALUE]
            and [HISTORY].[VALUE] < [KPIINSTANCE].[GOALUPWARDMIDVALUE]
          )
          or
          (
            [KPIINSTANCE].[GOALAIMTYPECODE] = 1 -- low is good

            and [HISTORY].[VALUE] <= [KPIINSTANCE].[GOALDOWNWARDHIGHVALUE]
            and [HISTORY].[VALUE] > [KPIINSTANCE].[GOALDOWNWARDMIDVALUE]
          )
        )
      )
      or
      (
        @GOALSTATUS = 2 -- green

        and
        (
          (
            [KPIINSTANCE].[GOALAIMTYPECODE] = 0 -- high is good

            and [HISTORY].[VALUE] >= [KPIINSTANCE].[GOALUPWARDMIDVALUE]
          )
          or
          (
            [KPIINSTANCE].[GOALAIMTYPECODE] = 1 -- low is good

            and [HISTORY].[VALUE] <= [KPIINSTANCE].[GOALDOWNWARDMIDVALUE]
          )
        )
      )
    )
    and (@CATEGORY is null or [KPICATALOG].[UIFOLDER] like @CATEGORY + '%')
    and (@TYPEID is null or [KPICATALOG].[ID] = @TYPEID)
    and (@CONTEXTRECORDTYPE is null or [RECORDTYPE].[NAME] = @CONTEXTRECORDTYPE)
    and (@CONTEXTRECORDID is null or [KPIINSTANCE].[CONTEXTRECORDID] = @CONTEXTRECORDID)
  order by [KPIINSTANCE].[NAME];

  return 0;