USP_KPIINSTANCE_FINDBYNAME

create procedure dbo.[USP_KPIINSTANCE_FINDBYNAME](@APPUSERID uniqueidentifier, @KPIINSTANCENAME nvarchar(100))

as
    set nocount on;

    declare @ISSYSADMIN bit;
    select @ISSYSADMIN = [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @APPUSERID;

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

    select [KPIINSTANCE].[ID],
        [KPIINSTANCE].[NAME]
    from dbo.[KPIINSTANCE]
  left join dbo.[KPICATALOG] on [KPIINSTANCE].[KPICATALOGID] = [KPICATALOG].[ID]
    where [KPIINSTANCE].[NAME] like '%' + @KPIINSTANCENAME + '%'
  and dbo.[UFN_SECURITY_APPUSER_GRANTED_KPIINSTANCE](@APPUSERID, [KPIINSTANCE].[ID]) = 1 
  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] = @APPUSERID
        -- 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]
            )
          )
        )       
      )
    )
    order by [KPIINSTANCE].[NAME];