/*
****************************************
calculate area_ace for a user.
take care that a user can be a memebr of one
or more security profiles
*/
CREATE function ols_fn_getAreaAceByUser
( @_uri        nvarchar(255)
)
returns  integer
begin

    -- calculate the area code
    declare @_pac            integer;            -- present area code temp. valud of a single security profile
    declare @_cumAreaCode    integer;            -- cumulative area code
    declare @_pae            integer;            -- present area enabled temp. valud of a single security profile

    --- check if user is set
    if ( @_uri is null )
    begin
        select @_cumAreaCode = (select set_val_lng from ca_settings where set_id = 900);
        return @_cumAreaCode;
    end;

    -- open cursor to search all security profile
    -- where the user is a member of and pick up the area_ace
    declare @_found  integer;
    set @_found = 0;
    declare cur_pace cursor for select  area_ace
                        from ca_security_profile sp,
                            ca_link_dis_user_sec_profile usp,
                            ca_discovered_user u
                        where  u.uri = @_uri
                            and u.user_uuid = usp.user_uuid
                            and usp.security_profile_uuid = sp.security_profile_uuid
                            and sp.type <> 1;        -- exclude everyone profile


    open cur_pace;
    fetch from cur_pace into @_pac;    -- get first
    set @_cumAreaCode = 0;
    while @@fetch_status = 0
    begin

            set @_cumAreaCode = @_cumAreaCode | @_pac;    -- ORing all aces
            set @_found = 1;
            fetch from cur_pace into @_pac;        --fetch next

    end;
    close cur_pace;
    deallocate cur_pace;

    -- use the config value if a user is not linked to any security profile
    if(@_found = 0)
    begin
        select @_cumAreaCode = (select set_val_lng from ca_settings where set_id = 900);
        return @_cumAreaCode;
    end


    return @_cumAreaCode;
end
GO
GRANT EXECUTE ON  [dbo].[ols_fn_getAreaAceByUser] TO [ca_itrm_group]
GRANT EXECUTE ON  [dbo].[ols_fn_getAreaAceByUser] TO [ca_itrm_group_ams]
GO