SELECT n.*
,r.RubricName,
r.Rubricator,
r.EditionID,
r.RubricShortName,
r.SortOrder,
r.IsActive,
r.Gnizdo_rayon,
e.DepartmentAccountID,
dbo.GetAccessRight(uta.AccessTypeEdit,c.CanEdit) as CanEdit,
dbo.GetAccessRight(uta.AccessTypeDelete,c.CanDelete) as CanDelete
FROM Notice n            
inner join dbo.Rubric r on r.RubricID = n.RubricID
inner join dbo.Edition e on e.EditionID = r.EditionID
inner JOIN dbo.GetAccessClientsDepartments(1138) c  ON  c.ClientID = n.ClientID and e.DepartmentAccountID = c.DepartmentAccountID
INNER JOIN UserTypeAccessView uta on uta.NoticeTypeID=n.NoticeTypeID and uta.UserID=1138
WHERE n.DeletedFlag=0 and uta.AccessTypeRead = 1 and c.CanRead = 1

   Declare @access TABLE (ClientId int, DepartmentAccountID int ,CanEdit int ,CanDelete int) ;
 
	INSERT INTO @access
	SELECT ClientId, DepartmentAccountID,CanEdit,CanDelete 
	FROM 
	dbo.GetAccessClientsDepartments(@UserID) 
	where CanRead = 1
    
    SELECT n.*,r.RubricName,r.Rubricator,r.EditionID,r.RubricShortName,r.SortOrder,r.IsActive,r.Gnizdo_rayon,e.DepartmentAccountID,
    dbo.GetAccessRight(uta.AccessTypeEdit,c.CanEdit) as CanEdit,
    dbo.GetAccessRight(uta.AccessTypeDelete,c.CanDelete) as CanDelete
    FROM Notice n
    inner join dbo.Rubric r on r.RubricID = n.RubricID
    inner join dbo.Edition e on e.EditionID = r.EditionID
    INNER JOIN UserTypeAccessView uta on uta.NoticeTypeID=n.NoticeTypeID and uta.UserID=@UserID
    INNER JOIN @access c  ON  c.ClientID = n.ClientID and e.DepartmentAccountID = c.DepartmentAccountID
    where n.DeletedFlag=0 and   uta.AccessTypeRead = 1

ALTER FUNCTION [dbo].[GetAccessClientsDepartments]
(	
	-- Add the parameters for the function here
	@UserID int
)
RETURNS TABLE 
AS
RETURN 
	(
		SELECT cc.ClientID,ISNULL(AccessClientAdd,CanAdd) as CanAdd,ISNULL(AccessClientRead,CanRead) as CanRead,
		ISNULL(AccessClientEdit,CanEdit) as CanEdit,
		ISNULL(AccessClientDelete,CanDelete) as CanDelete,da.DepartmentAccountID,ISNULL(ac.IsManager,0) as IsManager,cc.IsActive,cc.ClientShortName,cc.ChiefConfirm,da.DepartmentID
		from ClientCard cc
		cross join DepartmentAccount da
		left outer join AccessClientDepartmentView ac on ac.AccessClientID = cc.ClientID and ac.UserID = @UserID and da.DepartmentAccountID=ac.DepartmentAccountID
		left outer join GetAccessObjectsDepartments(@UserID) ao on ao.ClientID = cc.ClientID and da.DepartmentAccountID=ao.DepartmentAccountID
		
	)

ALTER FUNCTION [dbo].[GetAccessObjectsDepartments]
(	
	-- Add the parameters for the function here
	@UserID int
)
RETURNS TABLE 
AS
RETURN 
	(
SELECT TOP 1 with ties c.objectid as ObjectID,c.ClientID,
   AccessObjectRead as CanRead,
   AccessObjectEdit as CanEdit,
   AccessObjectAdd as CanAdd,
   AccessObjectDelete as CanDelete,DepartmentAccountID
FROM AccessObjectDepartmentView 
  CROSS APPLY GetChildOjects(AccessObjectID) c
where UserID = @UserID
order by row_number() OVER ( partition BY c.objectid,DepartmentAccountID ORDER BY LevelNum )



ALTER FUNCTION [dbo].[GetChildOjects]
(	
	-- Add the parameters for the function here
	@ObjectID int
)
RETURNS TABLE 
AS
RETURN 
	(
WITH C (ObjectID,ClientID,  LevelNum) AS
(
	SELECT B.ObjectID,B.ClientID,  0 FROM Object AS B WHERE ObjectID = @ObjectID
	UNION ALL
	SELECT B.ObjectID,B.ClientID, (LevelNum + 1) FROM Object AS B
		INNER JOIN C ON C.ObjectID = B.ObjectParentID
)

SELECT * FROM C
	)


	)

SELECT n.*
,r.RubricName,
r.Rubricator,
r.EditionID,
r.RubricShortName,
r.SortOrder,
r.IsActive,
r.Gnizdo_rayon,
e.DepartmentAccountID,
dbo.GetAccessRight(uta.AccessTypeEdit,c.CanEdit) as CanEdit,
dbo.GetAccessRight(uta.AccessTypeDelete,c.CanDelete) as CanDelete
from 
	(select * from dbo.GetAccessClientsDepartments(1138) where CanRead = 1) c
	join Notice n ON  c.ClientID = n.ClientID
	join dbo.Rubric r on r.RubricID = n.RubricID
	join dbo.Edition e on  e.DepartmentAccountID = c.DepartmentAccountID and e.EditionID = r.EditionID
	join UserTypeAccessView uta on uta.NoticeTypeID=n.NoticeTypeID and uta.UserID=1138
where n.DeletedFlag=0 and uta.AccessTypeRead = 1