<?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="approval">
	
    <resultMap id="approvalResult" extends="common.markedObjectResult" type="approval">
        <id property="approvalRequestId" column="APPROVAL_REQUEST_ID"/>
        <result property="dbDocId" column="DB_DOC_ID"/>
        <result property="userDocId" column="USER_DOC_ID"/>
        <result property="docType" column="DOC_TYPE"/>
        <result property="docApprovalStatusCode" column="DOC_APPROVAL_STATUS_CODE"/>
        <result property="requesterId" column="REQUESTER_ID"/>
        <result property="requesterName" column="REQUESTER_NAME"/>
        <result property="docTitle" column="DOC_TITLE"/>
        <result property="comment" column="COMMENT"/>
        <result property="draftDatetime" column="DRAFT_DATETIME" jdbcType="TIMESTAMP"/>
        <result property="finishDate" column="FINISH_DATE" jdbcType="TIMESTAMP"/>
        <result property="approvalFault" column="APPROVAL_FAULT"/>
        <result property="approvalVerSequence" column="APPROVAL_VER_SEQUENCE"/>
        <result property="status" column="STATUS"/>
        <result property="internalApproval" column="INTERNAL_APPROVAL"/>
        <result property="revision" column="REVISION"/>
        <result property="serverLocale" column="SERVER_LOCALE"/>
        <result property="lobId" column="LOB_ID"/>
        <result property="approvalType" column="APPROVAL_TYPE"/>
    </resultMap>
    
    <resultMap id="approvalStepResult" extends="common.markedObjectResult" type="approvalStep">
        <result property="approvalPersonName" column="APPROVAL_PERSON_NAME"/>
        <result property="approvalPersonId" column="APPROVAL_PERSON_ID"/>
        <result property="originPersonId" column="ORIGIN_PERSON_ID"/>
        <result property="approvalStatus" column="APPROVAL_STATUS"/>
        <result property="description" column="DESCRIPTION"/>
        <result property="finishDate" column="FINISH_DATE" jdbcType="TIMESTAMP"/>
        <result property="approvalStepSequence" column="APPROVAL_STEP_SEQUENCE"/>
        <result property="approvalStepFromDatetime" column="APPROVAL_STEP_FROM_DATETIME" jdbcType="TIMESTAMP"/>
        <result property="approvalStepType" column="APPROVAL_STEP_TYPE"/>
        <result property="approvalPersonCompanyName" column="APPROVAL_PERSON_COMPANY_NAME"/>
        <result property="approvalPersonDeptName" column="APPROVAL_PERSON_DEPT_NAME"/>
        <result property="approvalPersonPositionName" column="APPROVAL_PERSON_POSITION_NAME"/>
        <result property="approvalPersonDeptCode" column="APPROVAL_PERSON_DEPT_CODE"/>
        <result property="approvalPersonPositionCode" column="APPROVAL_PERSON_POSITION_CODE"/>
        <result property="approvalPersonCompanyCode" column="APPROVAL_PERSON_COMPANY_CODE"/>
        <result property="autoApproval" column="AUTO_APPROVAL"/>
        <result property="parallelProcess" column="PARALLEL_PROCESS"/>
        <result property="deleted" column="DELETED"/>
        <result property="deletedDate" column="DELETED_DATE" jdbcType="TIMESTAMP"/>
        <result property="delegated" column="DELEGATED"/>
        <result property="originPersonName" column="ORIGIN_PERSON_NAME"/>
        <result property="originPersonDeptName" column="ORIGIN_PERSON_DEPT_NAME"/>
        <result property="originPersonPositionName" column="ORIGIN_PERSON_POSITION_NAME"/>
        <result property="originPersonCompanyName" column="ORIGIN_PERSON_COMPANY_NAME"/>
        <result property="originPersonDeptCode" column="ORIGIN_PERSON_DEPT_CODE"/>
        <result property="originPersonPositionCode" column="ORIGIN_PERSON_POSITION_CODE"/>
        <result property="originPersonCompanyCode" column="ORIGIN_PERSON_COMPANY_CODE"/>
        <result property="arbitrary" column="ARBITRARY"/>
        <result property="routeModify" column="ROUTEMODIFY"/>
        <result property="bodyModify" column="BODYMODIFY"/>
        <result property="actionType" column="ACTION_TYPE"/>
    </resultMap>
    
    <resultMap id="approvalLobResult" extends="common.markedObjectResult" type="approvalLob">
        <id property="lobId" column="LOB_ID"/>
        <result property="approvalRequestId" column="APPROVAL_REQUEST_ID"/>
        <result property="lobDetail" column="LOB_DETAIL" jdbcType="CLOB" javaType="java.lang.String"/>
        <result property="mhtDetail" column="MHT_DETAIL" jdbcType="CLOB" javaType="java.lang.String"/>
    </resultMap>
    
    <resultMap id="dynamicApprovalPathResult" extends="common.markedObjectResult" type="dynamicApprovalPath">
    	<id property="docType" column="DOC_TYPE" />
    	<id property="approvalStepSequence" column="APPROVAL_STEP_SEQUENCE" />
    	<id property="approvalStepType" column="APPROVAL_STEP_TYPE" />
    	<id property="userId" column="USER_ID" />
    	<result property="userName" column="USER_NAME" />
    	<result property="grdName" column="GRD_NAME" />
    	<result property="deptName" column="DEPT_NAME" />
    	<result property="compName" column="COMP_NAME" />
    </resultMap>
    
    <resultMap id="requiredApprovalUserResult" extends="common.markedObjectResult" type="requiredApprovalUser">
    	<id property="docType" column="DOC_TYPE" />
    	<id property="userId" column="USER_ID" />
    	<result property="userName" column="USER_NAME" />
    	<result property="grdName" column="GRD_NAME" />
    	<result property="deptName" column="DEPT_NAME" />
    	<result property="compName" column="COMP_NAME" />
    </resultMap>
    
    <resultMap id="internalApprovalListResult" type="internalApproval">
        <id property="approvalRequestId" column="APPROVAL_REQUEST_ID" />
        <result property="docApprovalStatusCode" column="DOC_APPROVAL_STATUS_CODE" />
        <result property="docType" column="DOC_TYPE" />
        <result property="docTitle" column="DOC_TITLE" />
        <result property="requesterName" column="REQUESTER_NAME" />
        <result property="draftDatetime" column="DRAFT_DATETIME" />
        <result property="approvalStepFromDatetime" column="APPROVAL_STEP_FROM_DATETIME" />
        <result property="finishDate" column="FINISH_DATE" />
    </resultMap>
    
    <resultMap id="internalApprovalResult" type="internalApproval">
        <id property="approvalRequestId" column="APPROVAL_REQUEST_ID" />
        <result property="userDocId" column="USER_DOC_ID" />
        <result property="mhtDetail" column="MHT_DETAIL" />
        <result property="lobDetail" column="LOB_DETAIL" />
        <result property="approvalDocStatus" column="APPROVAL_DOC_STATUS" />
        <collection property="approvalStepList" column="{approvalRequestId=APPROVAL_REQUEST_ID}" select="selectApprovalStep" />
        <collection property="attachmentList" column="{downloadType=USER_DOC_ID, refId=USER_DOC_ID}" select="file.selectFileInfo" />
    </resultMap>
    
    <resultMap id="notApproveStepResult" type="approvalStep">
    	<id property="approvalRequestId" column="APPROVAL_REQUEST_ID" />
    	<id property="approvalStepSequence" column="APPROVAL_STEP_SEQUENCE" />
    	<id property="approvalPersonId" column="APPROVAL_PERSON_ID" />
    	<result property="approvalStepType" column="APPROVAL_STEP_TYPE" />
    </resultMap>
    
    <resultMap id="approverDelegateResult" type="approverDelegate">
    	<id property="userId" column="USER_ID" />
    	<id property="delegatorId" column="DELEGATOR_ID" />
    	<result property="startDate" column="START_DATE" />
    	<result property="endDate" column="END_DATE" />
    	<result property="delegatorName" column="DELEGATOR_NAME" />
    	<result property="delegatorCompanyName" column="DELEGATOR_COMPANY_NAME" />
    	<result property="delegatorDeptName" column="DELEGATOR_DEPT_NAME" />
    	<result property="delegatorPositionName" column="DELEGATOR_POSITION_NAME" />
    </resultMap>
    
    <sql id="tableApproval">TN_CF_APPROVAL</sql>
    <sql id="tableApprovalStep">TN_CF_APPROVAL_STEP</sql>
	<sql id="tableApprovalLob">TN_CF_APPROVAL_LOB</sql>
	<sql id="tableDynamicApprovalPath">TN_CF_DYNAMIC_APPROVAL_PATH</sql>
	<sql id="tableRequiredApprovalUser">TN_CF_REQUIRED_APPROVAL_USER</sql>
	<sql id="tableApprovalDelegate">TN_CF_APPROVER_DELEGATE</sql>
	
    <sql id="columnsApproval">
		APPROVAL_REQUEST_ID, DB_DOC_ID, USER_DOC_ID, DOC_TYPE, DOC_APPROVAL_STATUS_CODE,
		REQUESTER_ID, REQUESTER_NAME, COMMENT, DRAFT_DATETIME, DOC_TITLE, FINISH_DATE,
		APPROVAL_VER_SEQUENCE, LOB_ID, APPROVAL_FAULT, STATUS, INTERNAL_APPROVAL, REVISION, SERVER_LOCALE,
		FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID
	</sql>
	
    <sql id="columnsApprovalStep">
		APPROVAL_REQUEST_ID, APPROVAL_PERSON_ID, APPROVAL_PERSON_NAME, ORIGIN_PERSON_ID,
		APPROVAL_STATUS, DESCRIPTION, FINISH_DATE, APPROVAL_STEP_SEQUENCE, APPROVAL_STEP_FROM_DATETIME,
		APPROVAL_STEP_TYPE, APPROVAL_PERSON_COMPANY_NAME, APPROVAL_PERSON_DEPT_NAME, APPROVAL_PERSON_POSITION_NAME,
		APPROVAL_PERSON_DEPT_CODE, APPROVAL_PERSON_POSITION_CODE, APPROVAL_PERSON_COMPANY_CODE,
		FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID,
		AUTO_APPROVAL, PARALLEL_PROCESS, DELETED, DELETED_DATE, DELEGATED,
		ORIGIN_PERSON_NAME, ORIGIN_PERSON_DEPT_NAME, ORIGIN_PERSON_POSITION_NAME, ORIGIN_PERSON_COMPANY_NAME,
		ORIGIN_PERSON_DEPT_CODE, ORIGIN_PERSON_POSITION_CODE, ORIGIN_PERSON_COMPANY_CODE,
		ARBITRARY, ROUTEMODIFY, BODYMODIFY, ACTION_TYPE
	</sql>
	
	<sql id="columnsDynamicApprovalPath">
        DOC_TYPE, APPROVAL_STEP_SEQUENCE, APPROVAL_STEP_TYPE, USER_ID,
        USER_NAME, GRD_NAME, DEPT_NAME, COMP_NAME,
        FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID
	</sql>
	
	<sql id="columnsRequiredApprovalUser">
        DOC_TYPE, USER_ID, USER_NAME,
        GRD_NAME, DEPT_NAME, COMP_NAME,
        FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID
    </sql>
	
    <sql id="conditionApproval">
        <trim prefix="WHERE" prefixOverrides="AND|OR">
            <if test="@com.samsung.SdlComparator@isNotEmpty(docType)">
            DOC_TYPE = #{docType}
            </if>
            <if test="@com.samsung.SdlComparator@isNotEmpty(docNo)">
            AND DB_DOC_ID = #{docNo}
            </if>
        </trim>
    </sql>
    
    <sql id="conditionInternalApproval">
	    <if test="@com.samsung.SdlComparator@isNotEmpty(docType)">
	    AND A.DOC_TYPE = #{docType}
	    </if>
	    <if test="@com.samsung.SdlComparator@isNotEmpty(startDate)">
	    AND CONVERT(NVARCHAR(8), A.DRAFT_DATETIME, 112) <![CDATA[ >= ]]> #{startDate}
	    </if>
	    <if test="@com.samsung.SdlComparator@isNotEmpty(endDate)">
	    AND CONVERT(NVARCHAR(8), A.DRAFT_DATETIME, 112) <![CDATA[ <= ]]> #{endDate}
	    </if>
    </sql>
    
    <select id="maxVersionApproval" parameterType="java.util.HashMap" resultType="int">
        SELECT ISNULL(MAX(APPROVAL_VER_SEQUENCE), 0) AS VERSION
        FROM   <include refid="tableApproval" />
        <include refid="conditionApproval" />
    </select>

    <insert id="insertApproval" parameterType="approval">
        INSERT INTO <include refid="tableApproval" />
        (<include refid="columnsApproval" />)
        VALUES
        (#{approvalRequestId}, #{dbDocId}, #{userDocId}, #{docType}, #{docApprovalStatusCode},
         #{requesterId}, #{requesterName}, #{comment}, #{draftDatetime,jdbcType=TIMESTAMP}, #{docTitle}, #{finishDate,jdbcType=TIMESTAMP},
         #{approvalVerSequence}, #{lobId}, #{approvalFault}, #{status}, #{internalApproval}, #{revision}, #{serverLocale},
         #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId})
    </insert>

    <insert id="insertApprovalStep" parameterType="approvalStep">
        INSERT INTO <include refid="tableApprovalStep" />
        (<include refid="columnsApprovalStep" />)
        VALUES
        (#{approvalRequestId}, #{approvalPersonId}, #{approvalPersonName}, #{originPersonId},
         #{approvalStatus}, #{description}, #{finishDate,jdbcType=TIMESTAMP}, #{approvalStepSequence}, #{approvalStepFromDatetime,jdbcType=TIMESTAMP},
         #{approvalStepType}, #{approvalPersonCompanyName}, #{approvalPersonDeptName}, #{approvalPersonPositionName},
         #{approvalPersonDeptCode}, #{approvalPersonPositionCode}, #{approvalPersonCompanyCode},
         #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId},
         #{autoApproval}, #{parallelProcess}, #{deleted}, #{deletedDate,jdbcType=TIMESTAMP}, #{delegated},
         #{originPersonName}, #{originPersonDeptName}, #{originPersonPositionName}, #{originPersonCompanyName},
         #{originPersonDeptCode}, #{originPersonPositionCode}, #{originPersonCompanyCode},
         #{arbitrary}, #{routeModify}, #{bodyModify}, #{actionType})
    </insert>

    <update id="updateApprovalStep" parameterType="approvalStep">
        UPDATE <include refid="tableApprovalStep" />
        SET    APPROVAL_PERSON_NAME = #{approvalPersonName},
               ORIGIN_PERSON_ID = #{originPersonId},
               APPROVAL_STATUS = #{approvalStatus},
               DESCRIPTION = #{description},
               FINISH_DATE = #{finishDate,jdbcType=TIMESTAMP},
               APPROVAL_STEP_SEQUENCE = #{approvalStepSequence},
               APPROVAL_STEP_FROM_DATETIME = #{approvalStepFromDatetime,jdbcType=TIMESTAMP},
               APPROVAL_STEP_TYPE = #{approvalStepType},
               APPROVAL_PERSON_COMPANY_NAME = #{approvalPersonCompanyName},
               APPROVAL_PERSON_DEPT_NAME = #{approvalPersonDeptName},
               APPROVAL_PERSON_POSITION_NAME = #{approvalPersonPositionName},
               APPROVAL_PERSON_DEPT_CODE = #{approvalPersonDeptCode},
               APPROVAL_PERSON_POSITION_CODE = #{approvalPersonPositionCode},
               APPROVAL_PERSON_COMPANY_CODE = #{approvalPersonCompanyCode},
               LAST_MOD_DATETIME = #{lastModDatetime,jdbcType=TIMESTAMP},
               LAST_MODR_ID = #{lastModrId},
               AUTO_APPROVAL = #{autoApproval},
               PARALLEL_PROCESS = #{parallelProcess},
               DELETED = #{deleted},
               DELETED_DATE = #{deletedDate,jdbcType=TIMESTAMP},
               DELEGATED = #{delegated},
               ORIGIN_PERSON_NAME = #{originPersonName},
               ORIGIN_PERSON_DEPT_NAME = #{originPersonDeptName},
               ORIGIN_PERSON_POSITION_NAME = #{originPersonPositionName},
               ORIGIN_PERSON_COMPANY_NAME = #{originPersonCompanyName},
               ORIGIN_PERSON_DEPT_CODE = #{originPersonDeptCode},
               ORIGIN_PERSON_POSITION_CODE = #{originPersonPositionCode},
               ORIGIN_PERSON_COMPANY_CODE = #{originPersonCompanyCode},
               ARBITRARY = #{arbitrary},
               ROUTEMODIFY = #{routeModify},
               BODYMODIFY = #{bodyModify},
               ACTION_TYPE = #{actionType}
        WHERE  APPROVAL_REQUEST_ID = #{approvalRequestId}
        AND    APPROVAL_PERSON_ID = #{approvalPersonId}
        AND    APPROVAL_STEP_SEQUENCE = #{approvalStepSequence}
    </update>

    <insert id="insertApprovalLob" parameterType="approvalLob">
		INSERT INTO <include refid="tableApprovalLob" />
		(LOB_ID, LOB_DETAIL,
		 FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID,
		 MHT_DETAIL, APPROVAL_REQUEST_ID)
		VALUES
		(#{lobId}, #{lobDetail,jdbcType=VARCHAR},
		 #{firstRegDatetime,jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime,jdbcType=TIMESTAMP}, #{lastModrId},
		 #{mhtDetail,jdbcType=VARCHAR}, #{approvalRequestId})
	</insert>

    <select id="selectApprovalStep" parameterType="java.util.HashMap" resultMap="approvalStepResult">
        SELECT S.APPROVAL_REQUEST_ID,
               S.APPROVAL_PERSON_ID,
               S.APPROVAL_PERSON_NAME,
               S.APPROVAL_STATUS,
               S.DESCRIPTION,
               S.FINISH_DATE,
               S.APPROVAL_STEP_SEQUENCE,
               S.APPROVAL_STEP_FROM_DATETIME,
               S.APPROVAL_STEP_TYPE,
               S.APPROVAL_PERSON_COMPANY_NAME,
               S.APPROVAL_PERSON_DEPT_NAME,
               S.APPROVAL_PERSON_POSITION_NAME,
               S.APPROVAL_PERSON_DEPT_CODE,
               S.APPROVAL_PERSON_POSITION_CODE,
               S.APPROVAL_PERSON_COMPANY_CODE,
               S.FIRST_REG_DATETIME,
               S.FIRST_REGR_ID,
               S.LAST_MOD_DATETIME,
               S.LAST_MODR_ID,
               S.AUTO_APPROVAL,
               S.PARALLEL_PROCESS,
               S.DELETED,
               S.DELETED_DATE,
               S.DELEGATED,
               S.ORIGIN_PERSON_ID,
               S.ORIGIN_PERSON_NAME,
               S.ORIGIN_PERSON_DEPT_NAME,
               S.ORIGIN_PERSON_POSITION_NAME,
               S.ORIGIN_PERSON_COMPANY_NAME,
               S.ORIGIN_PERSON_DEPT_CODE,
               S.ORIGIN_PERSON_POSITION_CODE,
               S.ORIGIN_PERSON_COMPANY_CODE,
               S.ARBITRARY,
               S.ROUTEMODIFY,
               S.BODYMODIFY,
               S.ACTION_TYPE,
               A.DB_DOC_ID,
               A.APPROVAL_REQUEST_ID
        FROM   <include refid="tableApprovalStep" /> S,
               <include refid="tableApproval" /> A
        WHERE  A.APPROVAL_REQUEST_ID = S.APPROVAL_REQUEST_ID
        AND    S.DELETED = '0'
        <if test="@com.samsung.SdlComparator@isNotEmpty(docId)">
        AND    A.DB_DOC_ID = #{docId}
        </if>
        <if test="@com.samsung.SdlComparator@isNotEmpty(approvalRequestId)">
        AND    S.APPROVAL_REQUEST_ID = #{approvalRequestId}
        </if>
        <if test="@com.samsung.SdlComparator@isNotEmpty(approvalPersonId)">
        AND    S.APPROVAL_PERSON_ID = #{approvalPersonId}
        </if>
        ORDER BY S.APPROVAL_STEP_SEQUENCE ASC
    </select>

    <select id="selectApprovalRequestId" parameterType="java.util.HashMap" resultMap="approvalResult">
        SELECT APPROVAL_REQUEST_ID, SERVER_LOCALE
        FROM   (SELECT APPROVAL_REQUEST_ID, SERVER_LOCALE,
                       ROW_NUMBER() OVER (ORDER BY DRAFT_DATETIME ASC) AS CNT
                FROM   <include refid="tableApproval" />
                WHERE  DOC_APPROVAL_STATUS_CODE NOT IN ('COMPLETED', 'REJECTED', 'CANCELLED')
                <if test="@com.samsung.SdlComparator@isNotEmpty(approvalFault)">
                AND    APPROVAL_FAULT = #{approvalFault}
                </if>
                <if test="@com.samsung.SdlComparator@isNotEmpty(internalApproval)">
                AND	   INTERNAL_APPROVAL = #{internalApproval}
                </if>
                <if test="@com.samsung.SdlComparator@isNotEmpty(draftDatetime)">
                AND    DRAFT_DATETIME >= #{draftDatetime, jdbcType=TIMESTAMP}
                </if>) A
        <trim prefix="WHERE" prefixOverrides="AND|OR">
        <if test="@com.samsung.SdlComparator@isNotEmpty(limit)">
        AND CNT <![CDATA[ <= ]]>  #{limit}
        </if>
        </trim>
    </select>

    <select id="selectApproval" parameterType="java.util.HashMap" resultMap="approvalResult">
        SELECT <include refid="columnsApproval" />
        FROM   <include refid="tableApproval" />
        WHERE  APPROVAL_REQUEST_ID = #{approvalRequestId}
    </select>

    <select id="selectApprovalPagingList" parameterType="java.util.HashMap" resultMap="approvalResult">
        SELECT *
        FROM   (SELECT T1.*,
                       CEILING(ROW_NUMBER() OVER (ORDER BY DRAFT_DATETIME DESC) / (#{pageSize} * 1.0)) AS PAGE
                FROM   (SELECT <include refid="columnsApproval" />,
                               CASE
                                 WHEN INTERNAL_APPROVAL = '0' THEN 'Knox'
                                 ELSE 'Internal'
                               END APPROVAL_TYPE
                        FROM   <include refid="tableApproval" />
                        <trim prefix="WHERE" prefixOverrides="AND|OR">
                        <if test="@com.samsung.SdlComparator@isNotEmpty(internalApproval)">
                        AND    INTERNAL_APPROVAL = #{internalApproval}
                        </if>
                        <if test="@com.samsung.SdlComparator@isNotEmpty(docType)">
                        AND    DOC_TYPE = #{docType}
                        </if>
                        <if test="@com.samsung.SdlComparator@isNotEmpty(approvalStatus)">
                        AND    DOC_APPROVAL_STATUS_CODE = #{approvalStatus}
                        </if>
                        <if test="@com.samsung.SdlComparator@isNotEmpty(requesterName)">
                        AND    REQUESTER_NAME LIKE '%' + #{requesterName} + '%'
                        </if>
                        <if test="@com.samsung.SdlComparator@isNotEmpty(approvalFault)">
                        AND    APPROVAL_FAULT = #{approvalFault}
                        </if>
                        <if test="@com.samsung.SdlComparator@isNotEmpty(startDate)">
                        AND    CONVERT(VARCHAR(8), DRAFT_DATETIME, 112) <![CDATA[ >= ]]> #{startDate}
                        </if>
                        <if test="@com.samsung.SdlComparator@isNotEmpty(endDate)">
                        AND    CONVERT(VARCHAR(8), DRAFT_DATETIME, 112) <![CDATA[ <= ]]> #{endDate}
                        </if>
                        </trim>) T1
             ) T
        WHERE PAGE = #{pageIndex}
    </select>

    <select id="countApprovalPagingList" parameterType="java.util.HashMap" resultType="int">
        SELECT COUNT(*) AS TOTAL
        FROM   <include refid="tableApproval" />
        <trim prefix="WHERE" prefixOverrides="AND|OR">
        <if test="@com.samsung.SdlComparator@isNotEmpty(internalApproval)">
        AND    INTERNAL_APPROVAL = #{internalApproval}
        </if>
        <if test="@com.samsung.SdlComparator@isNotEmpty(docType)">
        AND    DOC_TYPE = #{docType}
        </if>
        <if test="@com.samsung.SdlComparator@isNotEmpty(approvalStatus)">
        AND    DOC_APPROVAL_STATUS_CODE = #{approvalStatus}
        </if>
        <if test="@com.samsung.SdlComparator@isNotEmpty(requesterName)">
        AND    REQUESTER_NAME LIKE '%' + #{requesterName} + '%'
        </if>
        <if test="@com.samsung.SdlComparator@isNotEmpty(approvalFault)">
        AND    APPROVAL_FAULT = #{approvalFault}
        </if>
        <if test="@com.samsung.SdlComparator@isNotEmpty(startDate)">
        AND    CONVERT(VARCHAR(8), DRAFT_DATETIME, 112) <![CDATA[ >= ]]> #{startDate}
        </if>
        <if test="@com.samsung.SdlComparator@isNotEmpty(endDate)">
        AND    CONVERT(VARCHAR(8), DRAFT_DATETIME, 112) <![CDATA[ <= ]]> #{endDate}
        </if>
        </trim>
    </select>

    <delete id="deleteApprovalStep" parameterType="approvalStep">
        DELETE FROM <include refid="tableApprovalStep" />
        WHERE  APPROVAL_REQUEST_ID = #{approvalRequestId}
        AND    APPROVAL_PERSON_ID = #{approvalPersonId}
    </delete>

    <update id="updateApproval" parameterType="approval">
        UPDATE <include refid="tableApproval" />
        SET    USER_DOC_ID = #{userDocId},
               DB_DOC_ID = #{dbDocId},
               COMMENT = #{comment},
               DOC_APPROVAL_STATUS_CODE = #{docApprovalStatusCode},
               DOC_TYPE = #{docType},
               FINISH_DATE = #{finishDate},
               REQUESTER_ID = #{requesterId},
               REQUESTER_NAME = #{requesterName},
               DRAFT_DATETIME = #{draftDatetime},
               DOC_TITLE = #{docTitle},
               APPROVAL_VER_SEQUENCE = #{approvalVerSequence},
               LOB_ID = #{lobId},
               LAST_MOD_DATETIME = #{lastModDatetime},
               LAST_MODR_ID = #{lastModrId},
               APPROVAL_FAULT = #{approvalFault},
               STATUS = #{status},
               INTERNAL_APPROVAL = #{internalApproval},
               REVISION = #{revision},
               SERVER_LOCALE = #{serverLocale}
        WHERE  APPROVAL_REQUEST_ID = #{approvalRequestId}
    </update>

    <update id="updateApprovalFault" parameterType="approval">
        UPDATE <include refid="tableApproval" />
        SET    APPROVAL_FAULT = #{approvalFault}
        WHERE  APPROVAL_REQUEST_ID = #{approvalRequestId}
    </update>
    
    <select id="selectDynamicApprovalPath" parameterType="java.util.HashMap" resultMap="dynamicApprovalPathResult">
    	SELECT <include refid="columnsDynamicApprovalPath" />
    	FROM   <include refid="tableDynamicApprovalPath" />
    	WHERE  DOC_TYPE = #{docType}
    </select>
    
    <delete id="deleteDynamicApprovalPath" parameterType="java.util.HashMap">
    	DELETE FROM <include refid="tableDynamicApprovalPath" />
    	WHERE  DOC_TYPE = #{docType}
    </delete>
    
    <insert id="insertDynamicApprovalPath" parameterType="dynamicApprovalPath">
    	INSERT INTO <include refid="tableDynamicApprovalPath" />
		(<include refid="columnsDynamicApprovalPath" />)
		VALUES
		(#{docType}, #{approvalStepSequence}, #{approvalStepType}, #{userId},
		 #{userName}, #{grdName}, #{deptName}, #{compName},
		 GETDATE(), #{firstRegrId}, GETDATE(), #{firstRegrId})
    </insert>
    
    <select id="selectRequiredApprovalUserList" parameterType="java.util.HashMap" resultMap="requiredApprovalUserResult">
    	SELECT <include refid="columnsRequiredApprovalUser" />
    	FROM   <include refid="tableRequiredApprovalUser" />
    	WHERE  DOC_TYPE = #{docType}
    </select>
    
    <delete id="deleteRequiredApprovalUser" parameterType="java.util.HashMap">
    	DELETE FROM <include refid="tableRequiredApprovalUser" />
    	WHERE  DOC_TYPE = #{docType}
    </delete>
    
    <insert id="insertRequiredApprovalUser" parameterType="requiredApprovalUser">
    	INSERT INTO <include refid="tableRequiredApprovalUser" />
    	(<include refid="columnsRequiredApprovalUser" />)
    	VALUES
    	(#{docType}, #{userId}, #{userName},
    	 #{grdName}, #{deptName}, #{compName},
    	 GETDATE(), #{firstRegrId}, GETDATE(), #{firstRegrId})
    </insert>
    
    <select id="checkExistDynamicApprovalPathByUserId" parameterType="java.util.HashMap" resultType="boolean">
    	SELECT CASE
    	          WHEN COUNT(USER_ID) > 0 THEN '1'
    	          ELSE '0'
    	       END CHECK_VAL
    	FROM   <include refid="tableDynamicApprovalPath" />
    	WHERE  DOC_TYPE = #{docType}
    	AND    USER_ID = #{userId}
    </select>
    
    <select id="selectInternalApprovalPagingListBySubmit" parameterType="java.util.HashMap" resultMap="internalApprovalListResult">
    	SELECT T.*
		FROM   (SELECT ROW_NUMBER() OVER(ORDER BY A.DRAFT_DATETIME DESC) RNUM,
		               A.APPROVAL_REQUEST_ID,
		               A.DOC_APPROVAL_STATUS_CODE,
		               A.DOC_TYPE,
		               A.DOC_TITLE,
		               A.REQUESTER_NAME,
		               A.DRAFT_DATETIME
		        FROM   <include refid="tableApproval" /> A
		        WHERE  A.INTERNAL_APPROVAL = '1'
		        AND    A.REQUESTER_ID = #{userId}
		        <include refid="conditionInternalApproval" />) T
		WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
    </select>
    
    <select id="countInternalApprovalListBySubmit" parameterType="java.util.HashMap" resultType="int">
    	SELECT COUNT(A.APPROVAL_REQUEST_ID) TOTAL
        FROM   <include refid="tableApproval" /> A
        WHERE  A.INTERNAL_APPROVAL = '1'
        AND    A.REQUESTER_ID = #{userId}
        <include refid="conditionInternalApproval" />
    </select>
    
    <select id="selectInternalApprovalPagingListByNotApprove" parameterType="java.util.HashMap" resultMap="internalApprovalListResult">
    	SELECT T.*
		FROM   (SELECT ROW_NUMBER() OVER(ORDER BY A.DRAFT_DATETIME DESC) RNUM,
		               A.APPROVAL_REQUEST_ID,
		               A.DOC_APPROVAL_STATUS_CODE,
    	               A.DOC_TYPE,
		               A.DOC_TITLE,
		               A.REQUESTER_NAME,
		               S.APPROVAL_STEP_FROM_DATETIME
		        FROM   <include refid="tableApproval" /> A,
		               <include refid="tableApprovalStep" /> S
		        WHERE  A.APPROVAL_REQUEST_ID = S.APPROVAL_REQUEST_ID
		        AND    A.INTERNAL_APPROVAL = '1'
		        AND    A.DOC_APPROVAL_STATUS_CODE = 'INPROCESS'
		        AND    S.APPROVAL_STEP_SEQUENCE != 0
		        AND    S.APPROVAL_PERSON_ID = #{userId}
		        AND    S.FINISH_DATE IS NULL
		        AND    S.APPROVAL_STEP_FROM_DATETIME IS NOT NULL
		        <include refid="conditionInternalApproval" />) T
		WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
    </select>
    
    <select id="countInternalApprovalListByNotApprove" parameterType="java.util.HashMap" resultType="int">
    	SELECT COUNT(A.APPROVAL_REQUEST_ID) TOTAL
        FROM   <include refid="tableApproval" /> A,
               <include refid="tableApprovalStep" /> S
        WHERE  A.APPROVAL_REQUEST_ID = S.APPROVAL_REQUEST_ID
        AND    A.INTERNAL_APPROVAL = '1'
        AND    A.DOC_APPROVAL_STATUS_CODE = 'INPROCESS'
        AND    S.APPROVAL_STEP_SEQUENCE != 0
        AND    S.APPROVAL_PERSON_ID = #{userId}
        AND    S.FINISH_DATE IS NULL
        AND    S.APPROVAL_STEP_FROM_DATETIME IS NOT NULL
        <include refid="conditionInternalApproval" />
    </select>
    
    <select id="selectInternalApprovalPagingListByApproved" parameterType="java.util.HashMap" resultMap="internalApprovalListResult">
    	SELECT T.*
		FROM   (SELECT ROW_NUMBER() OVER(ORDER BY A.DRAFT_DATETIME DESC) RNUM,
		               A.APPROVAL_REQUEST_ID,
		               A.DOC_APPROVAL_STATUS_CODE,
		               A.DOC_TYPE,
		               A.DOC_TITLE,
		               A.REQUESTER_NAME,
		               S.FINISH_DATE
		        FROM   <include refid="tableApproval" /> A,
		               <include refid="tableApprovalStep" /> S
		        WHERE  A.APPROVAL_REQUEST_ID = S.APPROVAL_REQUEST_ID
		        AND    A.INTERNAL_APPROVAL = '1'
		        AND    S.APPROVAL_STEP_SEQUENCE != 0
		        AND    S.FINISH_DATE IS NOT NULL
		        AND    S.APPROVAL_PERSON_ID = #{userId}
		        <include refid="conditionInternalApproval" />) T
		WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
    </select>
    
    <select id="countInternalApprovalListByApproved" parameterType="java.util.HashMap" resultType="int">
    	SELECT COUNT(A.APPROVAL_REQUEST_ID) TOTAL
		FROM   <include refid="tableApproval" /> A,
		       <include refid="tableApprovalStep" /> S
		WHERE  A.APPROVAL_REQUEST_ID = S.APPROVAL_REQUEST_ID
		AND    A.INTERNAL_APPROVAL = '1'
		AND    S.APPROVAL_STEP_SEQUENCE != 0
		AND    S.FINISH_DATE IS NOT NULL
		AND    S.APPROVAL_PERSON_ID = #{userId}
		<include refid="conditionInternalApproval" />
    </select>
    
    <select id="selectInternalApprovalPagingListByNotice" parameterType="java.util.HashMap" resultMap="internalApprovalListResult">
    	SELECT T.*
    	FROM   (SELECT ROW_NUMBER() OVER(ORDER BY A.DRAFT_DATETIME DESC) RNUM,
    	               A.APPROVAL_REQUEST_ID,
		               A.DOC_APPROVAL_STATUS_CODE,
		               A.DOC_TYPE,
		               A.DOC_TITLE,
		               A.REQUESTER_NAME,
		               S.APPROVAL_STEP_FROM_DATETIME
		        FROM   <include refid="tableApproval" /> A,
		               <include refid="tableApprovalStep" /> S
		        WHERE  A.APPROVAL_REQUEST_ID = S.APPROVAL_REQUEST_ID
		        AND    A.INTERNAL_APPROVAL = '1'
		        AND    A.DOC_APPROVAL_STATUS_CODE = 'COMPLETED'
		        AND    S.APPROVAL_STEP_TYPE = 'NOTIFICATION'
		        AND    S.APPROVAL_PERSON_ID = #{userId}
		        <include refid="conditionInternalApproval" />) T
		WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
    </select>
    
    <select id="countInternalApprovalListByNotice" parameterType="java.util.HashMap" resultType="int">
    	SELECT COUNT(A.APPROVAL_REQUEST_ID) TOTAL
		FROM   <include refid="tableApproval" /> A,
		       <include refid="tableApprovalStep" /> S
		WHERE  A.APPROVAL_REQUEST_ID = S.APPROVAL_REQUEST_ID
		AND    A.INTERNAL_APPROVAL = '1'
		AND    A.DOC_APPROVAL_STATUS_CODE = 'COMPLETED'
		AND    S.APPROVAL_STEP_TYPE = 'NOTIFICATION'
		AND    S.APPROVAL_PERSON_ID = #{userId}
		<include refid="conditionInternalApproval" />
    </select>
    
    <select id="selectInternalApproval" parameterType="java.util.HashMap" resultMap="internalApprovalResult">
    	SELECT A.APPROVAL_REQUEST_ID, A.USER_DOC_ID, L.MHT_DETAIL, L.LOB_DETAIL,
    	       A.DOC_APPROVAL_STATUS_CODE APPROVAL_DOC_STATUS
    	FROM   <include refid="tableApproval" /> A,
    	       <include refid="tableApprovalLob" /> L
    	WHERE  A.APPROVAL_REQUEST_ID = L.APPROVAL_REQUEST_ID
    	AND    A.APPROVAL_REQUEST_ID = #{approvalRequestId}
    </select>
    
    <update id="updateInternalApproval" parameterType="java.util.HashMap">
    	UPDATE <include refid="tableApproval" />
    	SET    DOC_APPROVAL_STATUS_CODE = #{docApprovalStatusCode},
    	       FINISH_DATE = GETDATE(),
    	       LAST_MOD_DATETIME = GETDATE(),
    	       LAST_MODR_ID = #{lastModrId}
    	WHERE  APPROVAL_REQUEST_ID = #{approvalRequestId}
    </update>
    
    <update id="updateInternalApprovalStep" parameterType="java.util.HashMap">
    	UPDATE <include refid="tableApprovalStep" />
    	SET    APPROVAL_STATUS = #{approvalStatus},
    	       DESCRIPTION = #{description},
    	       FINISH_DATE = #{finishDate}
    	       <if test="@com.samsung.SdlComparator@isNotEmpty(approvalStepType)">
    	       , APPROVAL_STEP_TYPE = #{approvalStepType}
    	       </if>
    	       <if test="@com.samsung.SdlComparator@isNotEmpty(approvalStepFromDatetime)">
    	       , APPROVAL_STEP_FROM_DATETIME = #{approvalStepFromDatetime}
    	       </if>
    	WHERE  APPROVAL_REQUEST_ID = #{approvalRequestId}
    	AND    APPROVAL_STEP_SEQUENCE = #{approvalStepSequence}
    	AND    APPROVAL_PERSON_ID = #{approvalPersonId}
    </update>
    
    <select id="selectCurrentStepSequence" parameterType="java.util.HashMap" resultType="int">
    	SELECT APPROVAL_STEP_SEQUENCE
		FROM   <include refid="tableApprovalStep" />
		WHERE  APPROVAL_STEP_SEQUENCE != 0
		AND    APPROVAL_REQUEST_ID = #{approvalRequestId}
		AND    APPROVAL_PERSON_ID = #{approvalPersonId}
    </select>
    
    <select id="selectNotApproveStepList" parameterType="java.util.HashMap" resultMap="notApproveStepResult">
    	SELECT APPROVAL_REQUEST_ID,
    	       APPROVAL_STEP_SEQUENCE,
    	       APPROVAL_PERSON_ID,
    	       APPROVAL_STEP_TYPE
		FROM   <include refid="tableApprovalStep" />
		WHERE  APPROVAL_STEP_TYPE != 'NOTIFICATION'
		AND    FINISH_DATE IS NULL
		AND    APPROVAL_REQUEST_ID = #{approvalRequestId}
		ORDER BY APPROVAL_STEP_SEQUENCE ASC
    </select>
    
    <update id="updateInternalApprovalNoticeStep" parameterType="java.util.HashMap">
    	UPDATE <include refid="tableApprovalStep" />
    	SET    APPROVAL_STEP_FROM_DATETIME = GETDATE(),
    	       FINISH_DATE = GETDATE()
    	WHERE  APPROVAL_REQUEST_ID = #{approvalRequestId}
    	AND    APPROVAL_STEP_TYPE = 'NOTIFICATION'
    </update>
    
    <select id="selectApproverDelegate" parameterType="java.util.HashMap" resultMap="approverDelegateResult">
    	SELECT D.USER_ID, D.DELEGATOR_ID,
    	       CONVERT(NVARCHAR, D.START_DATE, 23) START_DATE,
    	       CONVERT(NVARCHAR, D.END_DATE, 23) END_DATE,
    	       U.USER_NAME DELEGATOR_NAME,
    	       U.COMP_NAME DELEGATOR_COMPANY_NAME,
    	       U.DEPT_NAME DELEGATOR_DEPT_NAME,
    	       U.GRD_NAME DELEGATOR_POSITION_NAME
    	FROM   <include refid="tableApprovalDelegate" /> D,
    	       <include refid="user.tableUser" /> U
    	WHERE  D.DELEGATOR_ID = U.USER_ID
    	AND    D.USER_ID = #{userId}
    	AND    END_DATE <![CDATA[ > ]]> GETDATE()
    </select>
    
    <select id="checkExistsApproverDelegate" parameterType="java.util.HashMap" resultType="boolean">
    	SELECT CASE
    	          WHEN COUNT(DELEGATOR_ID) > 0 THEN '1'
    	          ELSE '0'
    	       END CHECK_VAL
    	FROM   <include refid="tableApprovalDelegate" />
    	WHERE  USER_ID = #{userId}
    </select>
    
    <insert id="insertApproverDelegate" parameterType="java.util.HashMap">
    	INSERT INTO <include refid="tableApprovalDelegate" />
    	(USER_ID, DELEGATOR_ID, START_DATE, END_DATE)
    	VALUES
    	(#{userId}, #{delegatorId}, CONVERT(DATETIME, #{startDate}), CONVERT(DATETIME, #{endDate} + ' 23:59:59'))
    </insert>
    
    <update id="updateApproverDelegate" parameterType="java.util.HashMap">
    	UPDATE <include refid="tableApprovalDelegate" />
    	SET    DELEGATOR_ID = #{delegatorId},
    	       START_DATE = CONVERT(DATETIME, #{startDate}),
    	       END_DATE = CONVERT(DATETIME, #{endDate} + ' 23:59:59')
    	WHERE  USER_ID = #{userId}
    </update>
    
    <delete id="deleteApproverDelegate" parameterType="java.util.HashMap">
    	DELETE FROM <include refid="tableApprovalDelegate" />
    	WHERE  USER_ID = #{userId}
    </delete>
    
</mapper>