USP_DATALIST_APPUSERWORKSPACEKPI

CREATE procedure dbo.USP_DATALIST_APPUSERWORKSPACEKPI
(
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null
)
as
    set nocount on;

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

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

    with 
    [USERKPIS] as 
    (
        select distinct [KPIINSTANCEID]
        from
            (
                -- Gets all KPI instances directly added on app-user's system role
                select
                    distinct [SYSTEMROLEKPIINSTANCE].[KPIINSTANCEID]
                from
                    dbo.[SYSTEMROLEKPIINSTANCE]
                    inner join dbo.[SYSTEMROLEAPPUSER] on [SYSTEMROLEKPIINSTANCE].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
                where
                    [SYSTEMROLEAPPUSER].[APPUSERID] = @CURRENTAPPUSERID

                union all

                -- Gets all KPI instances of the KPI types to which the app-user
                -- has been granted permission through their assigned system roles
                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]
    )
    select
        'res:' + case
            when HISTORY.VALUE is null then 'roadblank'
            when KPIINSTANCE.GOALAIMTYPECODE = 0 then 
                case
                    when HISTORY.VALUE < case when KPIINSTANCE.GOALAIMTYPECODE = 0 then KPIINSTANCE.GOALUPWARDLOWVALUE else KPIINSTANCE.GOALDOWNWARDHIGHVALUE end then 'road0'
                    when HISTORY.VALUE < case when KPIINSTANCE.GOALAIMTYPECODE = 0 then KPIINSTANCE.GOALUPWARDMIDVALUE else KPIINSTANCE.GOALDOWNWARDMIDVALUE end then 'road1'
                    else 'road2'
                end
            when KPIINSTANCE.GOALAIMTYPECODE = 1 then
                case
                    when HISTORY.VALUE > case when KPIINSTANCE.GOALAIMTYPECODE = 0 then KPIINSTANCE.GOALUPWARDLOWVALUE else KPIINSTANCE.GOALDOWNWARDHIGHVALUE end then 'road0'
                    when HISTORY.VALUE > case when KPIINSTANCE.GOALAIMTYPECODE = 0 then KPIINSTANCE.GOALUPWARDMIDVALUE else KPIINSTANCE.GOALDOWNWARDMIDVALUE end then 'road1'
                    else 'road2'
                end
            else ''
        end [STATUSIMAGE],
        KPIINSTANCE.ID,
        KPIINSTANCE.NAME,
        KPIINSTANCE.DESCRIPTION,
        KPICATALOG.NAME KPINAME
    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
                             and ASOFDATE <= dbo.UFN_DATE_GETLATESTTIME(getDate())
                             order by ASOFDATE desc) as HISTORY
    where
         (
            @ISSYSADMIN = 1
            or exists
            (
                select [KPIINSTANCEID]
                from [USERKPIS]
                where [USERKPIS].[KPIINSTANCEID] = [KPIINSTANCE].[ID]
            )
        )  --Security check
        and
        (
            @ISSYSADMIN = 1 or
            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]
                        )
                    )
                )
            )
        )
    order by
        KPIINSTANCE.NAME;

    return 0;