<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
		"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="api">

	<resultMap id="apiResult" type="api">
		<id property="apiId" column="API_ID" />
		<result property="authorizationType" column="AUTHORIZATION_ID" />
		<result property="serverId" column="SERVER_ID" />
		<result property="httpMethod" column="HTTP_METHOD" />
		<result property="apiUrl" column="API_URL" />
		<result property="apiPath" column="API_PATH" />
		<result property="apiParameters" column="API_PARAMETERS" />
		<result property="apiName" column="API_NAME" />
		<result property="used" column="USE_YN" />
	</resultMap>

	<sql id="tableApi">TN_CF_API</sql>

	<select id="selectApiList" parameterType="java.util.HashMap" resultMap="apiResult">
		SELECT A.API_ID, SA.AUTHORIZATION_ID, A.API_NAME,
		       A.API_URL, A.HTTP_METHOD, A.SERVER_ID, A.USE_YN
		FROM   <include refid="tableApi" /> A,
		       <include refid="sysResource.tableSysResource" /> SR,
		       <include refid="sysResource.tableSysResourceMapp" /> SRM,
		       <include refid="sysResource.tableSysAuthorization" /> SA
		WHERE  A.API_ID = SR.SYS_RESOURCE_ID
		AND    A.API_ID = SRM.SUB_SYS_RESOURCE_ID
		AND    A.API_ID = SA.SYS_RESOURCE_ID
		AND    A.DELETE_YN = '0'
		AND    SRM.MAIN_SYS_RESOURCE_ID = #{menuId}
	</select>

	<insert id="insertApi" parameterType="api">
		INSERT INTO <include refid="tableApi" />
		(API_ID, API_NAME, API_URL, API_PATH, API_PARAMETERS,
		 HTTP_METHOD, SERVER_ID, USE_YN, DELETE_YN)
		VALUES
		(#{apiId}, #{apiName}, #{apiUrl}, #{apiPath}, #{apiParameters},
		 #{httpMethod}, #{serverId}, #{used}, '0')
	</insert>

	<update id="updateApi" parameterType="api">
		UPDATE <include refid="tableApi" />
		SET    API_NAME = #{apiName},
		       API_URL = #{apiUrl},
		       API_PATH = #{apiPath},
		       API_PARAMETERS = #{apiParameters},
		       HTTP_METHOD = #{httpMethod},
		       SERVER_ID = #{serverId},
		       USE_YN = #{used}
		WHERE  API_ID = #{apiId}
	</update>

	<delete id="deleteApi" parameterType="java.util.HashMap">
		UPDATE <include refid="tableApi" />
		SET    USE_YN = '0',
		       DELETE_YN = '1'
		WHERE  API_ID = #{apiId}
	</delete>

	<delete id="deleteApiListByMenu" parameterType="java.util.HashMap">
		UPDATE <include refid="tableApi" />
		SET    USE_YN = '0',
		       DELETE_YN = '1'
		WHERE  EXISTS (SELECT SUB_SYS_RESOURCE_ID
		               FROM   <include refid="sysResource.tableSysResourceMapp" />
		               WHERE  SUB_SYS_RESOURCE_ID = <include refid="tableApi" />.API_ID
		               AND    MAIN_SYS_RESOURCE_ID = #{menuId})
	</delete>

	<delete id="deleteApiListByMenuId" parameterType="java.util.HashMap">
		UPDATE <include refid="tableApi" />
		SET    DELETE_YN = '1'
		WHERE  EXISTS (SELECT SRM.SUB_SYS_RESOURCE_ID
		               FROM   <include refid="sysResource.tableSysResourceMapp" /> SRM
		               WHERE  SRM.SUB_SYS_RESOURCE_ID = API_ID
		               AND    SRM.MAIN_SYS_RESOURCE_ID = #{menuId})
	</delete>

	<select id="selectDefaultApiList" parameterType="java.util.HashMap" resultMap="apiResult">
		SELECT API.API_ID, API.AUTHORIZATION_ID, API.SERVER_ID,
		       API.HTTP_METHOD, API.API_URL, API.API_NAME, API.USE_YN
		FROM   <include refid="menu.tableMenu" /> M,
		       <include refid="sysResource.tableSysResource" /> SR,
		       (SELECT SR.UPPER_SYS_RESOURCE_ID, A.API_ID, SA.AUTHORIZATION_ID,
		                A.API_NAME, A.API_URL, A.HTTP_METHOD, A.SERVER_ID, A.USE_YN
		        FROM   <include refid="tableApi" /> A,
		               <include refid="sysResource.tableSysResource" /> SR,
		               <include refid="sysResource.tableSysAuthorization" /> SA
		        WHERE  A.API_ID = SR.SYS_RESOURCE_ID
		        AND    A.API_ID = SA.SYS_RESOURCE_ID
		        AND    A.USE_YN = '1'
		        AND    A.DELETE_YN = '0') API
		WHERE  M.MENU_ID = SR.SYS_RESOURCE_ID
		AND    M.MENU_ID = API.UPPER_SYS_RESOURCE_ID
		AND    M.USE_YN = '1'
		AND    M.DELETE_YN = '0'
		AND    SR.UPPER_SYS_RESOURCE_ID = 'COMM_URL'
		ORDER BY M.MENU_ID ASC, API.AUTHORIZATION_ID DESC
	</select>

	<select id="selectUserAuthApiList" parameterType="java.util.HashMap" resultMap="apiResult">
		SELECT API.API_ID, API.AUTHORIZATION_ID, API.SERVER_ID, API.HTTP_METHOD,
		       API.API_URL, API.API_PATH, API.API_PARAMETERS, API.API_NAME, API.USE_YN
		FROM   (SELECT ROLE_ID USER_ROLE_ID
		        FROM   <include refid="role.tableRole" />
		        WHERE  ROLE_NAME = 'DEFAULT_ROLE'
		        UNION
		        SELECT ROLE_ID USER_ROLE_ID
		        FROM   <include refid="userRole.tableUserRole" />
		        WHERE  USER_ID = #{userId}
		        AND    THRU_DATE > CONVERT(VARCHAR(8), NOW(), 112)
		        UNION
		        SELECT #{userId} USER_ROLE_ID) UR,
		       <include refid="workgroupRole.tableWorkgroupRole" /> WR,
		       <include refid="workgroupMenu.tableWorkAuthorization" /> WA,
		       <include refid="menu.tableMenu" /> M,
		       <include refid="sysResource.tableSysResource" /> SR,
		       (SELECT SR.UPPER_SYS_RESOURCE_ID, A.API_ID, SA.AUTHORIZATION_ID, A.SERVER_ID, A.HTTP_METHOD,
		               A.API_URL, A.API_PATH, A.API_PARAMETERS, A.API_NAME, A.USE_YN
		        FROM   <include refid="tableApi" /> A,
		               <include refid="sysResource.tableSysResource" /> SR,
		               <include refid="sysResource.tableSysAuthorization" /> SA
		        WHERE  A.API_ID = SR.SYS_RESOURCE_ID
		        AND    A.API_ID = SA.SYS_RESOURCE_ID
		        AND    A.USE_YN = '1'
		        AND    A.DELETE_YN = '0') API
		WHERE  UR.USER_ROLE_ID = WR.USER_ROLE_ID
		AND    WR.WORKGROUP_ID = WA.WORKGROUP_ID
		AND    WA.SYS_RESOURCE_ID = M.MENU_ID
		AND    M.MENU_ID = SR.SYS_RESOURCE_ID
		AND    SR.SYS_RESOURCE_ID = API.UPPER_SYS_RESOURCE_ID
		AND    WA.AUTHORIZATION_ID = API.AUTHORIZATION_ID
		AND    M.USE_YN = '1'
		AND    M.DELETE_YN = '0'
		AND    M.MENU_ID = #{menuId}
		ORDER BY M.MENU_ID ASC, API.AUTHORIZATION_ID DESC
	</select>

	<select id="selectUserAuthApiListByUserId" parameterType="java.util.HashMap" resultMap="apiResult">
		SELECT API.API_ID,
		       API.HTTP_METHOD,
		       API.API_NAME,
		       API.API_URL,
		       API.API_PATH,
		       API.API_PARAMETERS
		FROM   (SELECT W.LABEL WORKGROUP_NAME,
		               M.MENU_ID,
		               M.LABEL MENU_NAME,
		               WM.AUTHORIZATION_ID
		        FROM   (SELECT #{userId} USER_ROLE_ID
		                UNION ALL
		                SELECT ROLE_ID USER_ROLE_ID
		                FROM   <include refid="userRole.tableUserRole" />
		                WHERE  USER_ID = #{userId}) UR,
		               <include refid="workgroupRole.tableWorkgroupRole" /> WR,
		               <include refid="workgroup.tableWorkgroup" /> W,
		               <include refid="workgroupMenu.tableWorkAuthorization" /> WM,
		               <include refid="menu.tableMenu" /> M
		        WHERE  UR.USER_ROLE_ID = WR.USER_ROLE_ID
		        AND    WR.WORKGROUP_ID = W.WORKGROUP_ID
		        AND    WR.WORKGROUP_ID = WM.WORKGROUP_ID
		        AND    WM.SYS_RESOURCE_ID = M.MENU_ID
		        AND    W.DELETE_YN = '0'
		        UNION
		        SELECT 'USER_AUTH_MENU' WORKGROUP_NAME,
		               M.MENU_ID,
		               M.LABEL MENU_NAME,
		               UM.AUTHORIZATION_ID
		        FROM   <include refid="user.tableUserAuthorization" /> UM,
		               <include refid="menu.tableMenu" /> M
		        WHERE  UM.SYS_RESOURCE_ID = M.MENU_ID
		        AND    UM.USER_ID = #{userId}
		        AND    UM.THRU_DATE > DATE_FORMAT(NOW(), '%Y%m%d')) UAM,
		       (SELECT SRM.MAIN_SYS_RESOURCE_ID MENU_ID,
		               A.API_ID,
		               A.HTTP_METHOD,
		               A.API_NAME,
		               A.API_URL,
		               A.API_PATH,
		               A.API_PARAMETERS,
		               SA.AUTHORIZATION_ID
		        FROM   <include refid="sysResource.tableSysResourceMapp" /> SRM,
		               <include refid="sysResource.tableSysAuthorization" /> SA,
		               <include refid="tableApi" /> A
		        WHERE  SRM.SUB_SYS_RESOURCE_ID = SA.SYS_RESOURCE_ID
		        AND    SRM.SUB_SYS_RESOURCE_ID = A.API_ID
		        AND    A.DELETE_YN = '0'
		        AND    A.USE_YN = '1') API
		WHERE  UAM.MENU_ID = API.MENU_ID
		AND    UAM.AUTHORIZATION_ID = API.AUTHORIZATION_ID
		<if test="@com.samsung.SdlComparator@isNotEmpty(menuId)">
		AND    UAM.MENU_ID = #{menuId}
		</if>
		UNION
		SELECT A.API_ID, A.HTTP_METHOD, A.API_NAME,
		       A.API_URL, A.API_PATH, A.API_PARAMETERS
		FROM   <include refid="workgroupRole.tableWorkgroupRole" /> WR,
		       <include refid="workgroupMenu.tableWorkAuthorization" /> WA,
		       <include refid="sysResource.tableSysResourceMapp" /> SRM,
		       <include refid="tableApi" /> A,
		       <include refid="sysResource.tableSysAuthorization" /> SA
		WHERE  WR.WORKGROUP_ID = WA.WORKGROUP_ID
		AND    WA.SYS_RESOURCE_ID = SRM.MAIN_SYS_RESOURCE_ID
		AND    SRM.SUB_SYS_RESOURCE_ID = A.API_ID
		AND    WA.AUTHORIZATION_ID = SA.AUTHORIZATION_ID
		AND    A.API_ID = SA.SYS_RESOURCE_ID
		AND    A.DELETE_YN = '0'
		AND    A.USE_YN = '1'
		AND    WR.ROLE_ID = 'DEFAULT_ROLE'
		GROUP BY A.API_ID, A.HTTP_METHOD, A.API_NAME, A.API_URL, A.API_PATH, A.API_PARAMETERS
	</select>

</mapper>