/****** spNetAdminRoles_Lista ******/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'spNetAdminRoles_Lista' AND TYPE = 'P') DROP PROCEDURE dbo.spNetAdminRoles_Lista
GO  
CREATE PROCEDURE dbo.spNetAdminRoles_Lista
-- WITH ENCRYPTION  
AS
BEGIN 
SET NOCOUNT ON
	DECLARE @IDUsuarioTipo int
	SELECT @IDUsuarioTipo = IDUsuarioTipo FROM pNetUsuarioTipo WHERE Descripcion = 'Automotriz'

	SELECT R.IDRole, R.Descripcion, COUNT(P.IDPermiso) Permisos
	  FROM pNetRole R 
	  LEFT JOIN pNetRolePermiso P ON R.IDRole = P.IDRole
	 WHERE R.IDUsuarioTipo = @IDUsuarioTipo
	 GROUP BY R.IDRole, R.Descripcion
	 ORDER BY R.Descripcion

RETURN
SET NOCOUNT OFF
END
GO
/****** spNetAdminRoles_Nuevo ******/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'spNetAdminRoles_Nuevo' AND TYPE = 'P') DROP PROCEDURE dbo.spNetAdminRoles_Nuevo
GO  
CREATE PROCEDURE dbo.spNetAdminRoles_Nuevo
	@Descripcion	varchar(100)
-- WITH ENCRYPTION  
AS
BEGIN
SET NOCOUNT ON
	DECLARE @IDUsuarioTipo int
	SELECT @IDUsuarioTipo = IDUsuarioTipo FROM pNetUsuarioTipo WHERE Descripcion = 'Automotriz'

	IF NOT EXISTS(SELECT IDRole FROM pNetRole WHERE IDUsuarioTipo = @IDUsuarioTipo AND Descripcion = @Descripcion)
	BEGIN
		INSERT INTO pNetRole(IDUsuarioTipo, Descripcion)
		SELECT @IDUsuarioTipo, @Descripcion
	END

	SELECT IDRole FROM pNetRole WHERE IDUsuarioTipo = @IDUsuarioTipo AND Descripcion = @Descripcion
RETURN
SET NOCOUNT OFF
END
GO
/****** spNetAdminRoles_Borrar ******/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'spNetAdminRoles_Borrar' AND TYPE = 'P') DROP PROCEDURE dbo.spNetAdminRoles_Borrar
GO  
CREATE PROCEDURE dbo.spNetAdminRoles_Borrar
	@IDRole	int
-- WITH ENCRYPTION  
AS
BEGIN
SET NOCOUNT ON
	DECLARE @IDUsuarioTipo int
	SELECT @IDUsuarioTipo = IDUsuarioTipo FROM pNetUsuarioTipo WHERE Descripcion = 'Automotriz'

	IF NOT EXISTS(SELECT IDRole FROM pNetRoleUsuario WHERE IDRole = @IDRole)
	BEGIN
		DELETE FROM pNetRole WHERE IDUsuarioTipo = @IDUsuarioTipo AND IDRole = @IDRole
	END
	ELSE
	BEGIN
		RAISERROR('No es posible eliminar el rol debido a que hay usuarios asignados al mismo', 16, 1)
	END
RETURN
SET NOCOUNT OFF
END
GO
/****** spNetAdminRoles_Actualizar ******/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'spNetAdminRoles_Actualizar' AND TYPE = 'P') DROP PROCEDURE dbo.spNetAdminRoles_Actualizar
GO  
CREATE PROCEDURE dbo.spNetAdminRoles_Actualizar
	@IDRole			int,
	@Descripcion	varchar(100),
	@Permisos		varchar(MAX) = ''
-- WITH ENCRYPTION  
AS
BEGIN
SET NOCOUNT ON
	DECLARE @IDUsuarioTipo int
	SELECT @IDUsuarioTipo = IDUsuarioTipo FROM pNetUsuarioTipo WHERE Descripcion = 'Automotriz'

	UPDATE pNetRole SET Descripcion = @Descripcion WHERE IDUsuarioTipo = @IDUsuarioTipo AND IDRole = @IDRole

	IF(@Permisos <> '') AND EXISTS(SELECT IDRole FROM pNetRole WHERE IDRole = @IDRole)
	BEGIN
		DELETE pNetRolePermiso WHERE IDRole = @IDRole

		INSERT INTO pNetRolePermiso (IDRole, IDPermiso)
		SELECT DISTINCT @IDRole IDRole, S.VALOR IDPermiso
		  FROM dbo.fnSplitTable (@Permisos, ',') S
		  JOIN pNetPermiso P ON S.Valor = P.IDPermiso
		 WHERE P.IDUsuarioTipo = @IDUsuarioTipo
		 ORDER BY S.Valor

		WHILE EXISTS(SELECT P.IDPermisoPadre FROM pNetPermiso P JOIN pNetRolePermiso RP ON P.IDPermiso = RP.IDPermiso AND RP.IDRole = @IDRole
		WHERE P.IDUsuarioTipo = @IDUsuarioTipo and P.IDPermisoPadre IS NOT NULL AND P.IDPermisoPadre NOT IN (SELECT IDPermiso FROM pNetRolePermiso WHERE IDRole = @IDRole)) 
		BEGIN
			INSERT INTO pNetRolePermiso (IDRole, IDPermiso)
			SELECT @IDRole, P.IDPermisoPadre
			  FROM pNetPermiso P
			  JOIN pNetRolePermiso RP ON P.IDPermiso = RP.IDPermiso AND RP.IDRole = @IDRole
			 WHERE P.IDUsuarioTipo = @IDUsuarioTipo and P.IDPermisoPadre IS NOT NULL
			   AND P.IDPermisoPadre NOT IN (SELECT IDPermiso FROM pNetRolePermiso WHERE IDRole = @IDRole)
		END
	END
RETURN
SET NOCOUNT OFF
END
GO
/****** spNetAdminRoles_Permisos ******/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'spNetAdminRoles_Permisos' AND TYPE = 'P') DROP PROCEDURE dbo.spNetAdminRoles_Permisos
GO  
CREATE PROCEDURE dbo.spNetAdminRoles_Permisos
	@IDRole int = NULL
-- WITH ENCRYPTION  
AS
BEGIN
SET NOCOUNT ON
	DECLARE @IDUsuarioTipo int
	SELECT @IDUsuarioTipo = IDUsuarioTipo FROM pNetUsuarioTipo WHERE Descripcion = 'Automotriz'

	SELECT DISTINCT P.IDPermiso, P.Descripcion, P.IDPermisoPadre, P.Mostrar, P.Icon, P.Ruta,
		   CASE WHEN RP.IDRole IS NULL THEN 0 WHEN @IDRole IS NULL THEN 0 ELSE 1 END Seleccionado
	  FROM pNetPermiso P
	  LEFT JOIN pNetRolePermiso RP ON P.IDPermiso = RP.IDPermiso AND RP.IDRole = isnull(@IDRole, RP.IDRole)
	 WHERE P.IDUsuarioTipo = @IDUsuarioTipo
	 ORDER BY P.IDPermisoPadre, P.IDPermiso
RETURN
SET NOCOUNT OFF
END
GO
/****** spNetAdminRoles_Validar ******/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'spNetAdminRoles_Validar' AND TYPE = 'P') DROP PROCEDURE dbo.spNetAdminRoles_Validar
GO  
CREATE PROCEDURE dbo.spNetAdminRoles_Validar
	@Usuario	varchar(10),
	@RouteLink	varchar(MAX)
-- WITH ENCRYPTION  
AS
BEGIN
SET NOCOUNT ON
 	DECLARE @IDUsuarioTipo INT;
	SELECT @IDUsuarioTipo = IDUsuarioTipo 
	FROM pNetRole 
	WHERE Descripcion = 'Root';
	IF EXISTS(
		SELECT P.IDPermiso
		FROM Usuario U
		JOIN pNetRoleUsuario RU ON U.Usuario = RU.Usuario
		JOIN pNetRole R ON RU.IDRole = R.IDRole AND R.IDUsuarioTipo = @IDUsuarioTipo
		JOIN pNetRolePermiso RP ON R.IDRole = RP.IDRole
		JOIN pNetPermiso P ON RP.IDPermiso = P.IDPermiso
      	WHERE	U.Usuario = @Usuario AND P.Ruta <> ''
      AND SUBSTRING(@RouteLink, 0, 
                    CASE 
                      WHEN CHARINDEX('?', @RouteLink) > 0 
                      THEN CHARINDEX('?', @RouteLink) 
                      ELSE LEN(@RouteLink) + 1 
                    END) like 
          SUBSTRING(P.Ruta, 0, 
                    CASE 
                      WHEN CHARINDEX('?', P.Ruta) > 0 
                      THEN CHARINDEX('?', P.Ruta) 
                      ELSE LEN(P.Ruta) + 1 
                    END) + '%'
	)
	BEGIN
		SELECT 1 Acceso
	END
	ELSE
	BEGIN
		SELECT 0 Acceso
	END
RETURN
SET NOCOUNT OFF
END
GO
/****** spNetRoles_Permisos ******/
IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'spNetRoles_Permisos' AND TYPE = 'P') DROP PROCEDURE dbo.spNetRoles_Permisos
GO  
CREATE PROCEDURE dbo.spNetRoles_Permisos
	@IDRole int = NULL
-- WITH ENCRYPTION  
AS
BEGIN
SET NOCOUNT ON
	DECLARE @IDUsuarioTipo int
	SELECT @IDUsuarioTipo = IDUsuarioTipo FROM pNetUsuarioTipo WHERE Descripcion = 'Automotriz'

	SELECT DISTINCT P.IDPermiso, P.Descripcion, P.IDPermisoPadre, P.Mostrar, P.Icon, P.Ruta,
		   CASE WHEN RP.IDRole IS NULL THEN 0 WHEN @IDRole IS NULL THEN 0 ELSE 1 END Seleccionado
	  FROM pNetPermiso P
	  JOIN pNetRolePermiso RP ON P.IDPermiso = RP.IDPermiso AND RP.IDRole = isnull(@IDRole, RP.IDRole)
	 WHERE P.IDUsuarioTipo = @IDUsuarioTipo AND P.Mostrar = 1
	 ORDER BY P.IDPermisoPadre, P.IDPermiso
RETURN
SET NOCOUNT OFF
END
GO


--EXEC spNetAdminRoles_Nuevo @Descripcion = 'Rol Prueba 1'

--EXEC spNetAdminRoles_Borrar @IDRole = 6
--EXEC spNetAdminRoles_Actualizar @IDRole = 8, @Descripcion = 'Rol Prueba 1', @Permisos = '700,702,708'

--EXEC spNetAdminRoles_Lista
--EXEC spNetAdminRoles_Validar @Usuario = 'BORISOUS', @RouteLink = '/admin/users/edit/INTADMIN'
--EXEC spNetAdminRoles_Permisos @IDRole = 13
