UFN_PROXYUSER_CHECKBRANCHHIERARCHYPERMISSION

create function dbo.UFN_PROXYUSER_CHECKBRANCHHIERARCHYPERMISSION(
    @PROXYOWNERID uniqueidentifier,
    @SYSTEMROLEID uniqueidentifier,
    @BRANCHSITEID uniqueidentifier
    )
returns bit
with execute as caller
as begin

    declare @SITESHIERARCHYTABLE table (SITEID uniqueidentifier,PARENTSITEID 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.Get all the levels of sites that proxy users selected

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

                union all

                select  SITESHIERARCHYTABLE.PARENTSITEID ,
                        SITESHIERARCHYTABLE.SITEID
                from    @SITESHIERARCHYTABLE SITESHIERARCHYTABLE
                inner join 
                        SITEPERMISSION as SITEPERMISSION on SITESHIERARCHYTABLE.SITEID = SITEPERMISSION.PARENTSITEID
                )

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


             select @COUNT=count(distinct SITEPERMISSION.SITEID) from @SITESLEVEL as SITEPERMISSION 
             inner join SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.BRANCHSITEID = SITEPERMISSION.SITEID 
             where SYSTEMROLEAPPUSER.APPUSERID=@PROXYOWNERID;

          --if we need to go upward in the tree

            if(@COUNT = 0)
                begin
                    delete from @SITESLEVEL;

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

                        union all  

                        select SITESHIERARCHYTABLE.PARENTSITEID,
                               SITESHIERARCHYTABLE.SITEID
                        from   @SITESHIERARCHYTABLE SITESHIERARCHYTABLE  
                        inner join 
                        SITEPERMISSION as SITEPERMISSION  ON  SITESHIERARCHYTABLE.PARENTSITEID =SITEPERMISSION.SITEID
                    )  

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

                    select @COUNT=count(distinct SITEPERMISSION.SITEID) from @SITESLEVEL as SITEPERMISSION 
                    inner join SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.BRANCHSITEID = SITEPERMISSION.SITEID 
                    where SYSTEMROLEAPPUSER.APPUSERID=@PROXYOWNERID;

        end

                if @COUNT > 0
                    set @RETURN = 1;

                    return @RETURN;
end