USE [Rostelecom]
GO

/****** Object:  UserDefinedTableType [dbo].[UserRole]    Script Date: 21.07.2017 17:30:18 ******/
CREATE TYPE [dbo].[UserRole] AS TABLE(
	[UserID] [int] NOT NULL,
	[UserRole] [int] NOT NULL
)
GO


CREATE TABLE [dbo].[UserDependency](
	[UserDependencyID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NULL,
	[ParentID] [int] NULL,
 CONSTRAINT [PK_UserDependency_UserDependencyID] PRIMARY KEY CLUSTERED
(
	[UserDependencyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[UserDependency]  WITH CHECK ADD  CONSTRAINT [FK_UserDependency_User_ParentID] FOREIGN KEY([ParentID])
REFERENCES [dbo].[User] ([UserID])
GO

ALTER TABLE [dbo].[UserDependency] CHECK CONSTRAINT [FK_UserDependency_User_ParentID]
GO

ALTER TABLE [dbo].[UserDependency]  WITH CHECK ADD  CONSTRAINT [FK_UserDependency_User_UserID] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO

ALTER TABLE [dbo].[UserDependency] CHECK CONSTRAINT [FK_UserDependency_User_UserID]
GO

ALTER TABLE [dbo].[UserDependency]  WITH CHECK ADD  CONSTRAINT [chkRoleRules] CHECK  (([dbo].[CheckRoleRules]([userId],[parentId])=(0)))
GO

ALTER TABLE [dbo].[UserDependency] CHECK CONSTRAINT [chkRoleRules]
GO




CREATE PROCEDURE [dbo].[SaveUserData]
@userId INT,
@fullname NVARCHAR(512),
@phone NVARCHAR(128),
@phoneWork NVARCHAR(128),
@email NVARCHAR(256),
@post NVARCHAR(512),
@subStr NVARCHAR(512),
@isSync BIT,
@roleId INT,
@sub INT,
@childs UserRole READONLY,
@parents UserRole READONLY
AS
      BEGIN
      SET NOCOUNT ON;
	  BEGIN TRY
	  BEGIN TRANSACTION
	      --Обновляем поля пользователя
		  UPDATE dbo.[User]
		  SET
		  fullname=@fullname,
		  Phone=@phone,
		  PhoneWork=@phoneWork,
		  Email=@email,
		  RoleID=@roleId,
		  SubdivisionID=@sub,
		  Post=@post,
		  SubdivisionStr=@subStr,
		  IsSync=@isSync
		  WHERE UserID=@userId

		  --Сохраняем роли для пользователей, которые их не имели (автоматическое сохранение роли)
	      MERGE dbo.[User] AS _target
		  USING (   SELECT *  FROM   @childs
		  			UNION ALL SELECT * FROM @parents
				  ) AS _source
			ON _source.UserID = [_target].UserID
			   AND [_target].RoleID IS NULL
			WHEN  MATCHED THEN
				UPDATE SET [_target].RoleID=[_source].UserRole;


		  --Обновляем зависимости подчиненных
		  WITH udChild AS (SELECT * FROM dbo.UserDependency WHERE ParentID=@userId)
		  MERGE udChild AS _target
		  USING (   SELECT *
					  FROM   @childs
				  ) AS _source
			ON _source.UserID = [_target].UserID
			WHEN NOT MATCHED BY TARGET THEN
				INSERT (UserID , ParentID )
				VALUES ( _source.UserID, @userId )
			WHEN NOT MATCHED BY SOURCE THEN	DELETE;

	    --Обновляем зависимости руководителей
		  WITH udParent AS (SELECT * FROM dbo.UserDependency WHERE UserID=@userId)
		  MERGE udParent AS _target
		  USING (   SELECT *
					  FROM   @parents
				  ) AS _source
			ON 	_source.UserID = [_target].ParentID
			AND  @userId =  [_target].UserID
			WHEN NOT MATCHED BY TARGET THEN
				INSERT (UserID , ParentID )
				VALUES ( @userId, _source.UserId  )
			WHEN NOT MATCHED BY SOURCE THEN	DELETE;

		COMMIT
	    RETURN 1;
		END TRY
		BEGIN CATCH

			IF @@TRANCOUNT > 0
				ROLLBACK
			RETURN -1;
		END CATCH
		END

GO


CREATE FUNCTION [dbo].[CheckRoleRules] (@userId int, @parentId int)
RETURNS int
AS
BEGIN
  DECLARE @retval INT = 0
  IF @parentId IS NULL RETURN 0;
  ELSE
  BEGIN
  DECLARE @userRole INT = (SELECT TOP 1 RoleID FROM dbo.[User] u WHERE u.UserID=@userId);
  DECLARE @parentRole INT = (SELECT TOP 1 RoleID FROM dbo.[User] u WHERE u.UserID=@parentId);
  --проверяем на соответствие иерархии ролей
  IF NOT EXISTS(SELECT 1 FROM dbo.RoleRule rr WHERE rr.RoleID = @userRole AND rr.ParentRoleID=@parentRole) RETURN 1;
  --проверяем отсутсвие некорректных привязок (пример: у Менеджера два Руководителя)
  IF((SELECT TOP 1 IsSingleParent FROM dbo.RoleRule rr WHERE rr.RoleID = @userRole)=1 AND (SELECT COUNT(*) FROM dbo.UserDependency WHERE UserID=@userId AND ParentID!=@parentId)>1) RETURN 1;
  --Если вставляем вставляем аналитика, как подчиненного Координатора 1-го уровня,
  --то не должно быть подчиненных у самого аналитика
  IF @userRole=5 AND @parentRole=3 AND EXISTS(SELECT 1 FROM dbo.UserDependency ud WHERE ud.ParentID=@userId) RETURN 1;
  --Проверяем что при вставке аналитика он не является одновременно подчиненным Координатора 1-го и Координатра 2-го уровня
  IF @userRole=5 AND EXISTS (SELECT 1
  FROM dbo.UserDependency ud
  INNER JOIN dbo.[User] u ON u.UserID=ud.ParentID
  WHERE ud.UserID=@userId
  GROUP BY u.RoleID
  HAVING COUNT(DISTINCT u.RoleID)>1) RETURN 1;
  END
  RETURN @retval
END;

GO