USP_KPIINSTANCE_GETLISTBYKPIDASHBOARDPAGETABID

CREATE procedure dbo.USP_KPIINSTANCE_GETLISTBYKPIDASHBOARDPAGETABID(@CURRENTAPPUSERID uniqueidentifier,
    @KPIDASHBOARDPAGETABID uniqueidentifier,
    @STARTDATE datetime,
    @ENDDATE datetime,
    @CONTEXTRECORDTYPE nvarchar(255),
    @CONTEXTRECORDID nvarchar(100),
    @MAXROWS int)
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 top(@MAXROWS) 
        KPIINSTANCE.KPICATALOGID,
        KPIINSTANCE.ID KPIINSTANCEID,
        KPIINSTANCE.NAME,
        KPICATALOG.GOALTYPECODE,
        KPICATALOG.GOALTYPE,
        KPICATALOG.GOALRETRIEVALTYPECODE,
        KPIINSTANCE.GOALVALUE,
        KPIINSTANCE.GOALINDICATORTYPECODE,
        KPIINSTANCE.GOALAIMTYPECODE,
        KPIINSTANCE.GOALVALUE,
        KPIINSTANCE.GOALUPWARDLOWVALUE,
        KPIINSTANCE.GOALUPWARDMIDVALUE,
        KPIINSTANCE.GOALDOWNWARDMIDVALUE,
        KPIINSTANCE.GOALDOWNWARDHIGHVALUE,
        KPIINSTANCEHISTORY.VALUE KPIVALUE,
        case when KPIINSTANCEHISTORY.VALUE is null then convert(bit, 0)
            else convert(bit, 1) end HASVALUE,
        KPIINSTANCEHISTORY.ASOFDATE CLOSESTASOFDATE,
        KPIINSTANCE.PARAMETERSXML,
        (select top(1) KPIINSTANCEMILESTONE.ASOFDATE from dbo.KPIINSTANCEMILESTONE
            where KPIINSTANCEMILESTONE.ASOFDATE <= KPIINSTANCEHISTORY.ASOFDATE
            order by KPIINSTANCEMILESTONE.ASOFDATE desc) MILESTONEASOFDATE,
        (select top(1) KPIINSTANCEMILESTONE.VALUE from dbo.KPIINSTANCEMILESTONE
            where KPIINSTANCEMILESTONE.ASOFDATE <= KPIINSTANCEHISTORY.ASOFDATE
            order by KPIINSTANCEMILESTONE.ASOFDATE desc) MILESTONEVALUE,
        KPICATALOG.DECIMALPLACES
    from dbo.KPIINSTANCE
        left join dbo.KPIINSTANCEHISTORY on KPIINSTANCE.ID = KPIINSTANCEHISTORY.KPIINSTANCEID
        inner join dbo.KPICATALOG on KPIINSTANCE.KPICATALOGID = KPICATALOG.ID
        left join dbo.KPIDASHBOARDPAGETABKPIINSTANCE on KPIINSTANCE.ID = KPIDASHBOARDPAGETABKPIINSTANCE.KPIINSTANCEID and KPIDASHBOARDPAGETABKPIINSTANCE.KPIDASHBOARDPAGETABID = @KPIDASHBOARDPAGETABID
    where 
        -- Double-check security here; this is needed when the specified tab ID is blank, so in case security doesn't need to be double-checked

        -- for every KPI instance, this function call must be moved into the part of the WHERE clause that checks for a blank tab ID.

        dbo.UFN_SECURITY_APPUSER_GRANTED_KPIINSTANCE(@CURRENTAPPUSERID, KPIINSTANCE.ID) = 1 
        and 
            ((KPIINSTANCEHISTORY.ASOFDATE between dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) and dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)) or KPIINSTANCEHISTORY.ID is null)
        and (
            -- No tab ID was supplied; just return all the KPIs to which he has rights.

            @KPIDASHBOARDPAGETABID is null 
            or @KPIDASHBOARDPAGETABID = '00000000-0000-0000-0000-000000000000'
            or KPIDASHBOARDPAGETABKPIINSTANCE.ID is not null
        )
        and ((@CONTEXTRECORDID is null or len(@CONTEXTRECORDID) = 0 or @CONTEXTRECORDID = '00000000-0000-0000-0000-000000000000') 
            or (len(@CONTEXTRECORDID) > 0 and KPIINSTANCE.CONTEXTRECORDID = @CONTEXTRECORDID and exists(select RECORDTYPE.ID from dbo.RECORDTYPE where ID = KPICATALOG.CONTEXTRECORDTYPEID and RECORDTYPE.NAME = @CONTEXTRECORDTYPE)))
        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, KPIINSTANCEHISTORY.ASOFDATE desc;