USP_KPIINSTANCE_GETTREE

CREATE procedure dbo.USP_KPIINSTANCE_GETTREE(@CURRENTAPPUSERID uniqueidentifier = null)
as
    set nocount on;

    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 KPIINSTANCE.ID,
        KPIINSTANCE.KPICATALOGID,
        KPIINSTANCE.NAME,
        KPIINSTANCE.DESCRIPTION,
        KPIINSTANCE.GOALVALUE,
        KPIINSTANCE.PARAMETERSXML,
        KPICATALOG.GOALTYPECODE,
        KPICATALOG.UIFOLDER,
        KPICATALOG.DECIMALPLACES,
        KPICATALOG.SPECXML.value('declare namespace kpi="bb_appfx_kpi"; (/kpi:KpiSpec/kpi:KpiFormDefinition)[1]/@CurrencyFieldID', 'nvarchar(max)') CURRENCYFIELDID
    from dbo.KPIINSTANCE
    inner join dbo.KPICATALOG on KPIINSTANCE.KPICATALOGID = KPICATALOG.ID
    where (
            KPICATALOG.HASSITEFILTER = 0 or 
            KPIINSTANCE.SITEFILTERENABLEDFORINSTANCE = 0 or 
            @SITEPRODUCTINSTALLED = 0 or
            @ISSYSADMIN = 1 or
            exists
            (
                select 1
                from dbo.SYSTEMROLEAPPUSER
                where
                    SYSTEMROLEAPPUSER.APPUSERID = @CURRENTAPPUSERID and
                    -- Make sure the kpi instance is permissioned for this role 

                    exists 
                    (
                        select 1 
                        from dbo.SYSTEMROLEKPIINSTANCE 
                        where 
                            SYSTEMROLEKPIINSTANCE.SYSTEMROLEID = SYSTEMROLEAPPUSER.SYSTEMROLEID and
                            SYSTEMROLEKPIINSTANCE.KPIINSTANCEID = KPIINSTANCE.ID 
                    ) and
                    (
                        SYSTEMROLEAPPUSER.SECURITYMODECODE = 0 or -- All Sites

                        (
                            SYSTEMROLEAPPUSER.SECURITYMODECODE = 1 and -- No Site

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

                            exists
                            (
                                select 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;