USE [Polaris]
GO

/****** Object:  StoredProcedure [Polaris].[SILS_RPT_PermissionGroupsByPermission]    Script Date: 2023-11-24 8:32:18 AM ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [Polaris].[SILS_RPT_PermissionGroupsByPermission]

	@OrganizationList varchar(MAX), -- generate a selectable list of agencies in the dropdown.
	@StaffList varchar(MAX), -- generate a selectable list of staff in the dropdown.
	@Permission varchar(MAX) -- generate a selectable list of permission groups in the dropdown.

AS

BEGIN 

SET NOCOUNT ON


CREATE TABLE #Branches (OrganizationID INT)  
	IF LEN(@OrganizationList) = 1 AND @OrganizationList= '0' -- ALL SELECTED
	BEGIN
		INSERT INTO #Branches 
			SELECT OrganizationID FROM Polaris.Organizations(NOLOCK) WHERE OrganizationCodeID in (3)
	END
	ELSE
	BEGIN
		EXEC ('INSERT INTO #Branches SELECT OrganizationID FROM Polaris.Organizations (NOLOCK) WHERE OrganizationID in(' + @OrganizationList + ')')  
	END

CREATE TABLE #Staff (PolarisUserID INT)  
	IF LEN(@StaffList) = 1 AND @StaffList= '0' -- ALL SELECTED
	BEGIN
		INSERT INTO #Staff 
			SELECT PolarisUserID FROM Polaris.PolarisUsers (NOLOCK)
	END
	ELSE
	BEGIN
		EXEC ('INSERT INTO #Staff SELECT PolarisUserID FROM Polaris.PolarisUsers (NOLOCK) WHERE PolarisUserID in(' + @StaffList + ')')  
	END

CREATE TABLE #Perms (GroupID INT)  
	IF LEN(@Permission) = 1 AND @Permission= '0' -- ALL SELECTED
	BEGIN
		INSERT INTO #Perms 
			SELECT GroupID FROM polaris.Groups (NOLOCK)
	END
	ELSE
	BEGIN
		EXEC ('INSERT INTO #Perms SELECT GroupID FROM polaris.Groups (NOLOCK) WHERE GroupID in(' + @Permission + ')')  
	END

select distinct p.Name,g.GroupName,crd.ControlRecordName,pn.PermissionName from polaris.PermissionGroups pg

inner join polaris.Groups g
on pg.GroupID = g.GroupID

inner join polaris.PermissionDefs pd
on pd.PermissionID = pg.PermissionID

inner join Polaris.ControlRecords cr
on cr.ControlRecordID = pg.ControlRecordID

inner join Polaris.ControlRecordDefs crd
on crd.ControlRecordDefID = cr.ControlRecordDefID

inner join Polaris.PermissionNames pn
on pn.PermissionNameID = pd.PermissionNameID

inner join Polaris.GroupUsers gu
on gu.GroupID = g.GroupID

inner join Polaris.PolarisUsers p
on p.PolarisUserID = gu.PolarisUserID

inner join #Branches b
on b.OrganizationID = p.OrganizationID

inner join #Staff s
on s.PolarisUserID = p.PolarisUserID

inner join #Perms pe
on g.GroupID = pe.GroupID

left join polaris.UsersPPPP pppp (nolock)
	on pppp.PolarisUserID = p.PolarisUserID AND pppp.AttrID = 659

WHERE p.name not like 'Email%'
and (pppp.Value != 'Suspended' or pppp.Value is null)
--and p.Enabled = 1

order by p.Name,GroupName,ControlRecordName,PermissionName

END 

GO


