UFN_PROXYUSER_CHECKSITEHIERARCHYPERMISSION

create function dbo.UFN_PROXYUSER_CHECKSITEHIERARCHYPERMISSION(
    @PROXYOWNERID uniqueidentifier=null,
    @SITES xml,
    @SITECOUNT int ,
    @SYSTEMROLEID uniqueidentifier
)
returns bit
with execute as caller
as begin

    declare @SITESHIERARCHYTABLE table (SITEID uniqueidentifier,PARENTSITEID uniqueidentifier);
    declare @SITESTABLE table (SITEID uniqueidentifier);
    declare @SITESLEVEL table (PARENTSITEID uniqueidentifier, SITEID uniqueidentifier);
    declare @SELECTEDSITES table (SITEID uniqueidentifier);
    declare @COUNT int;
    declare @RETURN bit;

    set @RETURN=0;

            --1.get the hierarchy of all the sites

                 insert into @SITESHIERARCHYTABLE(SITEID,PARENTSITEID)
                    select 
                        SITE.ID as SITEID, 
                        (select 
                            PARENTSITE.ID 
                        from 
                            dbo.SITE AS PARENTSITE 
                        where 
                            SITE.HIERARCHYPATH.GetAncestor(1) = PARENTSITE.HIERARCHYPATH
                        ) as PARENTSITEID
                    from dbo.SITE 
                    order by HIERARCHYPATH;

            --2.create temp table for the sites that proxy owner has


                insert into @SITESTABLE
                    select SITEID 
                    from 
                           dbo.SYSTEMROLEAPPUSERSITE
                    inner join
                           SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.ID = SYSTEMROLEAPPUSERSITE.SYSTEMROLEAPPUSERID  
                    where 
                           SYSTEMROLEAPPUSER.APPUSERID=@PROXYOWNERID and SYSTEMROLEAPPUSER.SYSTEMROLEID=@SYSTEMROLEID;


                --3.Get all the levels of sites that proxy Owner has

                ;with SITEPERMISSION (PARENTSITEID, SITEID)
                AS
                (
                    select  SITESHIERARCHYTABLE.PARENTSITEID ,
                            SITESHIERARCHYTABLE.SITEID
                    from    
                        @SITESHIERARCHYTABLE as SITESHIERARCHYTABLE 
                    where SITEID in (select SITEID from @SITESTABLE)

                    UNION all

                    select  SITESHIERARCHYTABLE.PARENTSITEID ,
                            SITESHIERARCHYTABLE.SITEID
                    from
                        @SITESHIERARCHYTABLE as SITESHIERARCHYTABLE
                    inner join SITEPERMISSION AS SITEPERMISSION on SITESHIERARCHYTABLE.PARENTSITEID = SITEPERMISSION.SITEID
                )
                 --insert all the child sites in a table

                    insert into @SITESLEVEL
                        select SITEPERMISSION.PARENTSITEID, SITEPERMISSION.SITEID from SITEPERMISSION;

                --4.Check if hierarchy matched with proxy owner


                    insert into @SELECTEDSITES
                        select t.x.value('SITEID[1]','uniqueidentifier')
                        from @SITES.nodes('/SITES/ITEM') t(x);

                    select @COUNT=  count(distinct SITESLEVEL.SITEID) 
                    from @SITESLEVEL SITESLEVEL
                    inner join @SELECTEDSITES SELECTEDSITES on SELECTEDSITES.SITEID=SITESLEVEL.SITEID;

                    if(@COUNT = @SITECOUNT)
                        set @RETURN=1;

                    return @RETURN;
end