drop function fs_connect_by(integer);
create function fs_connect_by(integer) returns varchar as '
declare
	id              alias for $1;
        pid             integer;
BEGIN
        select into pid parent_id from fs_files where id = file_id;
	IF pid is null
	THEN 
		return fs_gen_key(id);
	ELSE
		return fs_connect_by(pid) || ''/'' || fs_gen_key(id);
	END IF;	
END;
' language 'plpgsql';


create function ticket_user_can_close_issue_p(integer, integer)
returns char as '
DECLARE
	v_user_id alias for $1;
	v_msg_id alias for $2;
	msg_closeable record;
BEGIN
	if ad_admin_group_member_p(''bits'','''', v_user_id) = ''t''
	then return ''t'';
	end if;

	select into msg_closeable
               count(ti.msg_id) as chk,
               max(ti.project_id) as pid 
          from ticket_issues ti
         where ti.user_id = v_user_id 
           and ti.msg_id  = v_msg_id
           and 0 < (select count(*) 
                      from user_group_map 
                     where user_id= v_user_id 
                       and (role=''internal'' or role=''administrator'') 
                       and group_id = (select tm.team_id 
                                from ticket_project_teams tm
                               where parent_project_p(tm.project_id,ti.project_id)=''t''));

	IF msg_closeable.chk > 0
	THEN return(''t'');
	ELSE return(''f'');
	END IF;
END;
' language 'plpgsql';	
