-- 권한코드로 권한명을 반환
CREATE OR REPLACE FUNCTION fncauthorcodenm(param1 character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$BEGIN
RETURN  (SELECT	array_to_string(array_agg(b.author_nm), ',')
		FROM	unnest(string_to_array(param1, ',')) as a
				, comtnauthorinfo as b
		WHERE	a.a = b.author_code);
     end; $function$
;
COMMENT ON FUNCTION fncauthorcodenm(varchar) IS '권한코드 명을 반환한다.
fncauthorcodenm(author_code)';

-- 파일ID로 링크정보를 반환
CREATE OR REPLACE FUNCTION fnconvertfileid(param character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$BEGIN
RETURN  (
select array_to_string(array_agg(xx.link), ',') 
from tnfile xx, (
select unnest(string_to_array(param, ',')) as fileid
) yy 
where xx.file_id = yy.fileid
);
end; $function$
;
COMMENT ON FUNCTION fncmenudownurl(varchar) IS '파일아이디목록을 파일링크목록으로 변환한다.
fnconvertfileid(fileIdList)';

-- 메뉴ID, 사용자고유ID로 접속가능한 메뉴 반환(dir 제외)
CREATE OR REPLACE FUNCTION fncmenudownurl(param1 character varying, param2 character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$BEGIN
RETURN  (
		with recursive menuView as (
			select a.menu_no
				, upper_menu_no
				, b.progr_url
				, 1 as depth
				, array[b.progr_url::text] as path
				, array[a.menu_ordr] as ordr
				, false as cycle
			from TNMENUINFO a
				inner join TNMENUPROGRAMINFO b on a.menu_no = b.menu_no
			where a.menu_no = param1
			and a.use_yn = 'Y'
			and b.default_yn = 'Y'
			union all
			select b.menu_no
				, b.upper_menu_no
				, c.progr_url
				, depth + 1
				, array_append(path, c.progr_url::text)
				, array_append(ordr, b.menu_ordr)
				, c.progr_url::text <> any(a.path)
			from menuView a
				inner join TNMENUINFO b on a.menu_no = b.upper_menu_no
				inner join TNMENUPROGRAMINFO c on b.menu_no = c.menu_no
				inner join TNMENUAUTHOINFO d on c.progr_url = d.progr_url
			where b.use_yn = 'Y'
			and c.default_yn = 'Y'
			and a.upper_menu_no <> 'ROOT'
			and not a.cycle
			and (
					d.author_code IN
					 (
					 	select unnest(string_to_array(coalesce(
						(
							select author_code
							from comtnemplyrscrtyestbs c
							where scrty_dtrmn_trget_id = param2
						)
						, 'ROLE_ANONYMOUS'), ','))
					)
					or d.USER_ID = param2
				)
		)
		select a.progr_url
		from menuView a
		order by a.cycle desc, a.ordr
		limit 1
);
     end; $function$
;
COMMENT ON FUNCTION fncmenudownurl(varchar,varchar) IS '하위 프로그램URL 을 반환한다.
fncmenudownurl(menu_no, estntl_id)';

-- 공통코드로 공통코드명을 반환
CREATE OR REPLACE FUNCTION fnccomcodenm(param1 character varying, param2 character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$BEGIN
RETURN  (SELECT CODE_NM
	  FROM COMTCCMMNDETAILCODE
	  WHERE CODE = PARAM1
	    AND CODE_ID = PARAM2);
     end; $function$
;


COMMENT ON FUNCTION fnccomcodenm(varchar,varchar) IS '공통코드 명을 반환한다.
fnccomcodenm(code, code_id)';
