USP_DATALIST_CLIENT_SITES_SECURITY

CREATE procedure [dbo].[USP_DATALIST_CLIENT_SITES_SECURITY](@CLIENTUSERSID int, @TASKGUIDXML XML, @GETALLCLIENTSITESWITHANYTASK bit=0)
as
    set nocount on;

--Load the overriding guids into a table variable

declare @GUIDS table ([GUID] uniqueidentifier)
insert into @GUIDS
select    TGS.TG.value('@value', 'uniqueidentifier')
from @TASKGUIDXML.nodes('/guids/guid') TGS(TG)

declare @ISADMIN bit
set @ISADMIN = isnull((select 1 from dbo.CLIENTUSERS c where c.ID = @CLIENTUSERSID and (c.INTERNALUSER = 1 or c.ISSUPERVISOR = 1)),0)

--determine if we have rights to the organization 'site' (site id of null)

declare @RIGHTSTOORG bit
set @RIGHTSTOORG = isnull((select distinct 1 from dbo.UFN_CLIENTUSERS_TASKS(@CLIENTUSERSID, null) t where t.CLIENTSITESID is null and t.ISSITETASK = 1),0)

if @GETALLCLIENTSITESWITHANYTASK = 1 and @ISADMIN = 0 and @RIGHTSTOORG = 0
begin
    declare @SITES table ([ID] int, [HASRIGHTS] bit)

    insert into @SITES
    select distinct CS.ID, 1
    from dbo.CLIENTSITES CS
    inner join dbo.UFN_CLIENTUSERS_TASKS(@CLIENTUSERSID, null) T on T.CLIENTSITESID = CS.ID

    insert into @SITES
    select distinct P.ID, 0
    from @SITES S
    cross apply dbo.UFN_CLIENTSITE_PARENTIDS(S.ID) P
    left outer join @SITES S2 on S2.ID = P.ID
    where S2.ID is null

    select CS.ID, CS.Name, CS.Deleted, CS.Guid, CS.ParentSiteID, S.HASRIGHTS, dbo.UFN_CLIENTSITE_ISPUBLISHED(CS.ID) PUBLISHED
    from @SITES S
    inner join dbo.ClientSites CS on CS.ID = S.ID    
    order by CS.Name
end
else
begin

    --Determine if we have any of these guids for ALL sites

    declare @RIGHTSINALLSITES bit
    set @RIGHTSINALLSITES = (select 1 where EXISTS(select 1
                            from @GUIDS G
                            inner join dbo.UFN_CLIENTUSERS_TASKS(@CLIENTUSERSID, null) T on T.TASKGUID = G.[GUID]
                            where T.CLIENTSITESID is null))

    if @RIGHTSINALLSITES = 1 or @ISADMIN = 1 or (@GETALLCLIENTSITESWITHANYTASK = 1 and @RIGHTSTOORG = 1)
    begin
        select CS.ID, CS.Name, CS.Deleted, CS.Guid, CS.ParentSiteID, 1, dbo.UFN_CLIENTSITE_ISPUBLISHED(CS.ID) PUBLISHED
        from dbo.ClientSites CS
        order by CS.Name
    end
    else
    begin
        declare @RESULTS table ([ID] int, [HASRIGHTS] bit)

        insert into @RESULTS
        select DISTINCT T.CLIENTSITESID, 1
        from @GUIDS G
        inner join dbo.UFN_CLIENTUSERS_TASKS(@CLIENTUSERSID, null) T on T.TASKGUID = G.[GUID]

        insert into @RESULTS
        select distinct P.ID, 0
        from @RESULTS R
        cross apply dbo.UFN_CLIENTSITE_PARENTIDS(R.ID) P
        left outer join @RESULTS R2 on R2.ID = P.ID
        where R2.ID is null

        select CS.ID, CS.Name, CS.Deleted, CS.Guid, CS.ParentSiteID, R.HASRIGHTS, dbo.UFN_CLIENTSITE_ISPUBLISHED(CS.ID) PUBLISHED
        from @RESULTS R
        inner join dbo.ClientSites CS on CS.ID = R.ID    
        order by CS.Name
    end
end