USP_MKTSEGMENTATIONACTIVATE_GETSYSTEMROLESFORSITE

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_GETSYSTEMROLESFORSITE]
(
  @SITEID uniqueidentifier,
  @KPIINSTANCEID uniqueidentifier = null
)
as
begin
  set nocount on;                  

  declare @KPICATALOGID uniqueidentifier;
  select @KPICATALOGID = [KPICATALOGID] from dbo.[KPIINSTANCE] where [ID] = @KPIINSTANCEID;

  if @SITEID is null
    /* Doesn't matter if SITESECURITYMODE = 0 or 1.  Permission is granted to roles that have users that are not limited by site and users that are limited to records with no site */
    select distinct
      [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
    from dbo.[SYSTEMROLEAPPUSER]
    left join dbo.[SYSTEMROLEPERM_KPI] on [SYSTEMROLEPERM_KPI].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
    where 
      [SECURITYMODECODE] not in (2, 3)
      -- If the kpi is marked is neither grant nor deny, then grant access.

      and (@KPICATALOGID is null or ((not exists (select * 
                                                  from [SYSTEMROLEPERM_KPI] 
                                                  where 
                                                    [SYSTEMROLEPERM_KPI].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
                                                    and [KPICATALOGID] = @KPICATALOGID)
           -- If the kpi is marked as granted.

           or ([SYSTEMROLEPERM_KPI].[KPICATALOGID] = @KPICATALOGID and [SYSTEMROLEPERM_KPI].[GRANTORDENY] = 1))))
  else
    /* Permission is granted to roles with users that are limited to this site or roles that have users that are not limited by site */
    select distinct
      [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
    from dbo.[SYSTEMROLEAPPUSER]
    left join dbo.[SYSTEMROLEPERM_KPI] on [SYSTEMROLEPERM_KPI].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
    where 
      [SECURITYMODECODE] = 0
      -- If the kpi is marked is neither grant nor deny, then grant access.

      and (@KPICATALOGID is null or ((not exists (select * 
                                                  from [SYSTEMROLEPERM_KPI] 
                                                  where 
                                                    [SYSTEMROLEPERM_KPI].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
                                                    and [KPICATALOGID] = @KPICATALOGID)
           -- If the kpi is marked as granted.

           or ([SYSTEMROLEPERM_KPI].[KPICATALOGID] = @KPICATALOGID and [SYSTEMROLEPERM_KPI].[GRANTORDENY] = 1))))

    union

    select distinct 
      [SITEPERMISSION].[SYSTEMROLEID]
    from dbo.[SITEPERMISSION]
    left join dbo.[SYSTEMROLEPERM_KPI] on [SYSTEMROLEPERM_KPI].[SYSTEMROLEID] = [SITEPERMISSION].[SYSTEMROLEID]
    where 
      [SITEID] = @SITEID
      -- If the kpi is marked is neither grant nor deny, then grant access.

      and (@KPICATALOGID is null or ((not exists (select * 
                                                  from [SYSTEMROLEPERM_KPI] 
                                                  where 
                                                    [SYSTEMROLEPERM_KPI].[SYSTEMROLEID] = [SITEPERMISSION].[SYSTEMROLEID]
                                                    and [KPICATALOGID] = @KPICATALOGID)
           -- If the kpi is marked as granted.

           or ([SYSTEMROLEPERM_KPI].[KPICATALOGID] = @KPICATALOGID and [SYSTEMROLEPERM_KPI].[GRANTORDENY] = 1))));

    return 0;
  end