USP_KPIDASHBOARDPAGETABKPIINSTANCE_GETLIST

CREATE procedure dbo.USP_KPIDASHBOARDPAGETABKPIINSTANCE_GETLIST(@KPIDASHBOARDPAGETABID uniqueidentifier, @CURRENTAPPUSERID uniqueidentifier = null)
as

    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 KPIDASHBOARDPAGETABKPIINSTANCE.ID,
        KPIDASHBOARDPAGETABKPIINSTANCE.KPIINSTANCEID,
        KPIINSTANCE.NAME,
        KPICATALOG.GOALTYPECODE,
        KPIDASHBOARDPAGETABKPIINSTANCE.SEQUENCE
    from dbo.KPIDASHBOARDPAGETABKPIINSTANCE
    inner join dbo.KPIINSTANCE on KPIDASHBOARDPAGETABKPIINSTANCE.KPIINSTANCEID = KPIINSTANCE.ID
    inner join dbo.KPICATALOG on KPIINSTANCE.KPICATALOGID = KPICATALOG.ID
    where 
        KPIDASHBOARDPAGETABKPIINSTANCE.KPIDASHBOARDPAGETABID = @KPIDASHBOARDPAGETABID
        and 
        (
            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 
        KPIDASHBOARDPAGETABKPIINSTANCE.SEQUENCE;