Задачи, Контрагенты и Сделки, где ответственный - Лазарева

CREATE TABLE #tbl_OpportunityRight(
	[ID] [uniqueidentifier] NULL,
	[RecordID] [uniqueidentifier] NULL,
	[AdminUnitID] [uniqueidentifier] NULL,
	[CanRead] [int] NULL,
	[CanWrite] [int] NULL,
	[CanDelete] [int] NULL,
	[CanChangeAccess] [int] NULL)
go 
 
insert into #tbl_OpportunityRight (RecordID) (select ID from tbl_Opportunity where ID in (select OpportunityID from tbl_OpportunityInGroup where GroupID = 'B66CAAD9-AF6B-4F12-B88C-1E3453F591C4'))
 
go
 
update #tbl_OpportunityRight set ID = NEWID(), AdminUnitID = '687D0624-5B5E-4F19-961E-D9F03A96939A', CanRead = '1', CanWrite = '1', CanDelete = '0', CanChangeAccess = '0';
 
insert into tbl_OpportunityRight select * from #tbl_OpportunityRight

update tbl_OpportunityRight set CanDelete = '0', CanWrite = '0', CanChangeAccess = '0'
	where ID in (select ID from tbl_OpportunityRight where AdminUnitID = 'D268BFB9-8118-4E3B-9F06-1D703D25C23E' and CanWrite = '1') 

USE [Our_backup]
GO
/****** Object:  StoredProcedure [dbo].[tsp_UpdateRightsByDefaults]    Script Date: 07/25/2013 16:57:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[tsp_UpdateRightsByDefaults] 
	@ARightTableName sysname,
	@Owner_ID uniqueidentifier,
	@Record_ID uniqueidentifier,
	@ADBSchema sysname = 'dbo'
with execute as 'fkeys'
AS
BEGIN
	SET NOCOUNT ON;
  DECLARE @ServiceTableID uniqueidentifier
  DECLARE @AdminUnitID uniqueidentifier
  DECLARE @RecordID uniqueidentifier
  DECLARE @TableRightsName varchar(250)
  DECLARE @DefaultGroupID uniqueidentifier
  set @DefaultGroupID = (select ID from [tbl_AdminUnit] where Name = 'продажники') -- меняем имя или пишем конкретный ИД
 
  SET @ServiceTableID = (SELECT [ID] 
  FROM [dbo].[tbl_Service] 
	   WHERE [Code] = (replace(@ARightTableName, 'Right', ''))
    AND [ServiceTypeCode] = N'Table')
 
  SET @AdminUnitID = (SELECT ID from [dbo].[tbl_AdminUnit] where UserContactID = @Owner_ID)
  SET @TableRightsName = '[' + @ADBSchema + '].[' + @ARightTableName +']'
  SET @RecordID = @Record_ID
 
 exec(' DELETE' +@TableRightsName + ' where RecordID = ''' + @RecordID + ''' 
 
  INSERT INTO ' +@TableRightsName + ' (
    [ID]
    ,[RecordID]
    ,[AdminUnitID]
    ,[CanRead]
    ,[CanWrite]
    ,[CanDelete]
    ,[CanChangeAccess])
  SELECT
    newid()
    ,''' + @RecordID + '''
    ,''' + @AdminUnitID + '''
    ,1
    ,1
    ,0
    ,0
 
  INSERT INTO ' + @TableRightsName + ' (
    [ID]
    ,[RecordID]
    ,[AdminUnitID]
    ,[CanRead]
    ,[CanWrite]
    ,[CanDelete]
    ,[CanChangeAccess])
  SELECT
    newid()
    ,''' + @RecordID + '''
    ,[D].[SubjectAdminUnitID]
    ,[D].[CanRead]
    ,[D].[CanWrite]
    ,[D].[CanDelete]
    ,[D].[CanChangeAccess]
  FROM (
    SELECT
      [D].[SubjectAdminUnitID]
      ,MAX([D].[CanRead]) AS [CanRead]
      ,MAX([D].[CanWrite]) AS [CanWrite]
      ,MAX([D].[CanDelete]) AS [CanDelete]
      ,MAX([D].[CanChangeAccess]) AS [CanChangeAccess]
    FROM [dbo].[tbl_TableDefaultRight] AS [D]
    WHERE ([D].[TableServiceID] = ''' + @ServiceTableID + ''')
    AND ([D].[AdminUnitID] = ''' + @DefaultGroupID + ''' 
		 )
  GROUP BY [D].[SubjectAdminUnitID])
	AS [D]
 ')
	SET NOCOUNT OFF;
END

//см приложение

SELECT
	s1.[Code] AS [Code]
FROM
	[tbl_Service] AS s1
WHERE s1.[ServiceTypeCode] = 'Table'
and s1.Code <> 'tbl_TableField'
and exists( 
  select * from tbl_Service as s2
  where s2.Code = s1.Code + 'Right'
)

	set @UserIsActive = (SELECT COUNT (*) FROM tbl_AdminUnit WHERE UserContactID = @OwnerID and UserIsEnabled = 1) -- UserIsEnabled = 1 опционально
	if (@UserIsActive != 0)
		set @AdminUnitID = (SELECT ID FROM tbl_AdminUnit WHERE UserContactID = @OwnerID)
	else set @AdminUnitID = @DefaultGroupID

DECLARE @RightsTableCode nvarchar(100);
DECLARE @TableCode nvarchar(100);
 
DECLARE @DeleteQuery nvarchar(4000);
DECLARE @DeleteQueryExec nvarchar(4000);
 
DECLARE @InsertQuery nvarchar(max);
DECLARE @InsertQueryExec nvarchar(max);
 
DECLARE @ServiceTableID uniqueidentifier;
 
SET @DeleteQuery = 'DELETE FROM @RightsTableCode
	WHERE EXISTS (SELECT ID FROM tbl_AdminUnit AS AU WHERE AU.IsGroup = 1 AND AU.ID = @RightsTableCode.AdminUnitID)';
 
SET @InsertQuery = 'INSERT INTO @RightsTableCode(
    ID
    ,RecordID
    ,AdminUnitID
    ,CanRead
    ,CanWrite
    ,CanDelete
    ,CanChangeAccess)
  SELECT
    newid()
    ,I.ID
    ,D.SubjectAdminUnitID
    ,D.CanRead
    ,D.CanWrite
    ,D.CanDelete
    ,D.CanChangeAccess
  FROM (
    SELECT
      D.SubjectAdminUnitID
      ,MAX(D.CanRead) AS CanRead
      ,MAX(D.CanWrite) AS CanWrite
      ,MAX(D.CanDelete) AS CanDelete
      ,MAX(D.CanChangeAccess) AS CanChangeAccess
    FROM tbl_TableDefaultRight AS D
    LEFT OUTER JOIN tbl_AdminUnit AS AU ON AU.ID = D.SubjectAdminUnitID
    WHERE (D.TableServiceID = ''@ServiceTableID'')
		AND (AU.IsGroup = 1)
  GROUP BY D.SubjectAdminUnitID)
	AS D, @TableCode I';
 
DECLARE c_Something CURSOR FOR
	SELECT Code FROM tbl_Service
	WHERE ServiceTypeCode = 'Table' AND Code LIKE 'tbl_%Right' AND Code NOT LIKE 'tbl_%GroupRight';
 
OPEN c_Something;
FETCH NEXT FROM c_Something INTO @RightsTableCode
WHILE @@FETCH_STATUS =0
BEGIN
	PRINT @RightsTableCode;
	SET @TableCode = REPLACE(@RightsTableCode, 'Right', '');
	PRINT @TableCode;
 
	SET @ServiceTableID = (SELECT ID FROM tbl_Service WHERE Code = @TableCode AND ServiceTypeCode = N'Table');
 
	IF (@ServiceTableID IS NOT NULL) 
		BEGIN
		BEGIN TRAN
		BEGIN TRY
 
			-- delete group rights
			SET @DeleteQueryExec = REPLACE(@DeleteQuery, '@RightsTableCode', @RightsTableCode);
			PRINT @DeleteQueryExec;
			--EXECUTE sp_executesql @DeleteQueryExec;
 
			-- insert group rights
			SET @InsertQueryExec = REPLACE(@InsertQuery, '@RightsTableCode', @RightsTableCode);
			SET @InsertQueryExec = REPLACE(@InsertQueryExec, '@TableCode', @TableCode);
			SET @InsertQueryExec = REPLACE(@InsertQueryExec, '@ServiceTableID', @ServiceTableID);
			PRINT @InsertQueryExec;
			--EXECUTE sp_executesql @InsertQueryExec;
 
		COMMIT TRAN
		END TRY
		BEGIN CATCH
			SELECT 
				ERROR_NUMBER() AS ErrorNumber,
				ERROR_SEVERITY() AS ErrorSeverity,
				ERROR_STATE() as ErrorState,
				ERROR_PROCEDURE() as ErrorProcedure,
				ERROR_LINE() as ErrorLine,
				ERROR_MESSAGE() as ErrorMessage;
 
			ROLLBACK TRAN
		END CATCH 
		END;
 
    FETCH NEXT FROM c_Something INTO @RightsTableCode;
END
CLOSE c_Something
DEALLOCATE c_Something