<?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="com.interplug.qcast.biz.commCode.CommCodeMapper">
	<insert id="setCommHUpdate" parameterType="com.interplug.qcast.biz.commCode.dto.CodeRes">
    	/* sqlid : com.interplug.qcast.commCode.setCommHUpdate */
    	MERGE M_COMM_H AS A
    		USING
    		( SELECT #{headCd} AS HEAD_CD ) AS D
    		ON (A.HEAD_CD = D.HEAD_CD
    		)
    	WHEN MATCHED THEN
    		UPDATE SET
    			HEAD_ID = #{headId}
    			, HEAD_NM = #{headJp}
    			, HEAD_JP = #{headJp}
    			, HEAD_4TH = #{head4Th}
    			, REF_CHR1 = #{refChr1}
    			, REF_CHR2 = #{refChr2}
    			, REF_CHR3 = #{refChr3}
    			, REF_CHR4 = #{refChr4}
    			, REF_CHR5 = #{refChr5}
    			, REF_NUM1 = #{refNum1}
    			, REF_NUM2 = #{refNum2}
    			, REF_NUM3 = #{refNum3}
    			, REF_NUM4 = #{refNum4}
    			, REF_NUM5 = #{refNum5}
    			, REMARKS = #{remarks}
    			, DEL_FLG = #{delFlg}
    			, LAST_EDIT_DATETIME = GETDATE()
    	WHEN NOT MATCHED THEN
    		INSERT (
				HEAD_CD
				, HEAD_ID
				, HEAD_NM
				, HEAD_JP
				, HEAD_4TH
				, REF_CHR1
    			, REF_CHR2
    			, REF_CHR3
    			, REF_CHR4
    			, REF_CHR5
    			, REF_NUM1
    			, REF_NUM2
    			, REF_NUM3
    			, REF_NUM4
    			, REF_NUM5
    			, REMARKS
    			, DEL_FLG
    			, REGIST_DATETIME
    			, LAST_EDIT_DATETIME
			) VALUES (
				#{headCd}
				, #{headId}
    			, #{headJp}
    			, #{headJp}
    			, #{head4Th}
    			, #{refChr1}
    			, #{refChr2}
    			, #{refChr3}
    			, #{refChr4}
    			, #{refChr5}
    			, #{refNum1}
    			, #{refNum2}
    			, #{refNum3}
    			, #{refNum4}
    			, #{refNum5}
    			, #{remarks}
    			, #{delFlg}
    			, GETDATE()
    			, GETDATE()
			);
    </insert>
    
	<insert id="setCommLUpdate" parameterType="com.interplug.qcast.biz.commCode.dto.CodeRes">
    	/* sqlid : com.interplug.qcast.commCode.setCommLUpdate */
    	MERGE M_COMM_L AS A
    		USING
    		( SELECT #{clHeadCd} AS HEAD_CD, #{clCode} AS CODE ) AS D
    		ON (A.HEAD_CD = D.HEAD_CD AND A.CODE = D.CODE
    		)
    	WHEN MATCHED THEN
    		UPDATE SET
    			CODE = #{clCode}
    			, READ_CD = #{clReadCd}
    			, CODE_NM = #{clCodeJp}
    			, CODE_JP = #{clCodeJp}
    			, CODE_4TH = #{clCode4Th}
    			, REF_CHR1 = #{clRefChr1}
    			, REF_CHR2 = #{clRefChr2}
    			, REF_CHR3 = #{clRefChr3}
    			, REF_CHR4 = #{clRefChr4}
    			, REF_CHR5 = #{clRefChr5}
    			, REF_NUM1 = #{clRefNum1}
    			, REF_NUM2 = #{clRefNum2}
    			, REF_NUM3 = #{clRefNum3}
    			, REF_NUM4 = #{clRefNum4}
    			, REF_NUM5 = #{clRefNum5}
    			, PRIORITY = #{clPriority}
    			, REF_CNT = #{clRefCnt}
    			, DEL_FLG = #{clDelFlg}
    			, LAST_EDIT_DATETIME = GETDATE()
    	WHEN NOT MATCHED THEN
    		INSERT (
				HEAD_CD
				, CODE
				, READ_CD
				, CODE_NM
    			, CODE_JP
    			, CODE_4TH
    			, REF_CHR1
    			, REF_CHR2
    			, REF_CHR3
    			, REF_CHR4
    			, REF_CHR5
    			, REF_NUM1
    			, REF_NUM2
    			, REF_NUM3
    			, REF_NUM4
    			, REF_NUM5
    			, PRIORITY
    			, REF_CNT
    			, DEL_FLG
    			, REGIST_DATETIME
    			, LAST_EDIT_DATETIME
			) VALUES (
				#{clHeadCd}
				, #{clCode}
				, #{clReadCd}
				, #{clCodeJp}
				, #{clCodeJp}
				, #{clCode4Th}
				, #{clRefChr1}
				, #{clRefChr2}
				, #{clRefChr3}
				, #{clRefChr4}
				, #{clRefChr5}
				, #{clRefNum1}
				, #{clRefNum2}
				, #{clRefNum3}
				, #{clRefNum4}
				, #{clRefNum5}
				, #{clPriority}
				, #{clRefCnt}
				, #{clDelFlg}
				, GETDATE()
				, GETDATE()
			);
    </insert>
    
    <select id="selectQcastCommCode" resultType="com.interplug.qcast.biz.commCode.dto.CodeRes">
    /* sqlid : com.interplug.qcast.commCode.selectQcastCommCode (QCAST 에서 사용하는 공통코드)*/
	    SELECT A.*
	    FROM (
	    	SELECT
	    		CL.HEAD_CD		AS CL_HEAD_CD
	    		, CL.CODE		AS CL_CODE
	    		, CL.READ_CD	AS CL_READ_CD
	    		, CL.CODE_NM	AS CL_CODE_NM
	    		, CL.CODE_JP	AS CL_CODE_JP
	    		, CL.PRIORITY	AS CL_PRIORITY
	    		, CL.REF_CHR1	AS CL_REF_CHR1
	    		, CL.REF_CHR2	AS CL_REF_CHR2
	    	FROM
	    		M_COMM_H (NOLOCK) CH
	    	INNER JOIN
	    		M_COMM_L (NOLOCK) CL
	    	ON
	    		CH.HEAD_CD = CL.HEAD_CD
	    	AND
	    		CH.DEL_FLG = 0
	    	AND
	    		CL.DEL_FLG = 0
	    ) A
	    ORDER BY A.CL_HEAD_CD, A.CL_PRIORITY
    </select>
    
    <insert id="setHeadCodeSyncSave" parameterType="com.interplug.qcast.biz.commCode.dto.HeadCodeRequest">
    /* sqlid : com.interplug.qcast.commCode.setHeadCodeSyncSave (배치 공통코드 헤더 정보 동기화 저장)*/
    	MERGE M_COMM_H AS A
    		USING
    		( SELECT #{headCd} AS HEAD_CD ) AS D
    		ON (A.HEAD_CD = D.HEAD_CD
    		)
    	WHEN MATCHED THEN
    		UPDATE SET
    			HEAD_ID = #{headId}
    			, HEAD_NM = #{headJp}
    			, HEAD_JP = #{headJp}
    			, HEAD_4TH = #{head4Th}
    			, REF_CHR1 = #{refChr1}
    			, REF_CHR2 = #{refChr2}
    			, REF_CHR3 = #{refChr3}
    			, REF_CHR4 = #{refChr4}
    			, REF_CHR5 = #{refChr5}
    			, REF_NUM1 = #{refNum1}
    			, REF_NUM2 = #{refNum2}
    			, REF_NUM3 = #{refNum3}
    			, REF_NUM4 = #{refNum4}
    			, REF_NUM5 = #{refNum5}
    			, REMARKS = #{remarks}
    			, DEL_FLG = #{delFlg}
    			, LAST_EDIT_DATETIME = GETDATE()
    	WHEN NOT MATCHED THEN
    		INSERT (
				HEAD_CD
				, HEAD_ID
				, HEAD_NM
				, HEAD_JP
				, HEAD_4TH
				, REF_CHR1
    			, REF_CHR2
    			, REF_CHR3
    			, REF_CHR4
    			, REF_CHR5
    			, REF_NUM1
    			, REF_NUM2
    			, REF_NUM3
    			, REF_NUM4
    			, REF_NUM5
    			, REMARKS
    			, DEL_FLG
    			, REGIST_DATETIME
    			, LAST_EDIT_DATETIME
			) VALUES (
				#{headCd}
				, #{headId}
    			, #{headJp}
    			, #{headJp}
    			, #{head4Th}
    			, #{refChr1}
    			, #{refChr2}
    			, #{refChr3}
    			, #{refChr4}
    			, #{refChr5}
    			, #{refNum1}
    			, #{refNum2}
    			, #{refNum3}
    			, #{refNum4}
    			, #{refNum5}
    			, #{remarks}
    			, #{delFlg}
    			, GETDATE()
    			, GETDATE()
			);
    </insert>
    
    <insert id="setCommonCodeSyncSave" parameterType="com.interplug.qcast.biz.commCode.dto.DetailCodeRequest">
    /* sqlid : com.interplug.qcast.commCode.setCommonCodeSyncSave (배치 공통코드 상세 정보 동기화 저장)*/
    MERGE M_COMM_L AS A
    		USING
    		( SELECT #{headCd} AS HEAD_CD, #{code} AS CODE ) AS D
    		ON (A.HEAD_CD = D.HEAD_CD AND A.CODE = D.CODE
    		)
    	WHEN MATCHED THEN
    		UPDATE SET
    			CODE = #{code}
    			, READ_CD = #{readCd}
    			, CODE_NM = #{codeJp}
    			, CODE_JP = #{codeJp}
    			, CODE_4TH = #{code4Th}
    			, REF_CHR1 = #{refChr1}
    			, REF_CHR2 = #{refChr2}
    			, REF_CHR3 = #{refChr3}
    			, REF_CHR4 = #{refChr4}
    			, REF_CHR5 = #{refChr5}
    			, REF_NUM1 = #{refNum1}
    			, REF_NUM2 = #{refNum2}
    			, REF_NUM3 = #{refNum3}
    			, REF_NUM4 = #{refNum4}
    			, REF_NUM5 = #{refNum5}
    			, PRIORITY = #{priority}
    			, REF_CNT = #{refCnt}
    			, DEL_FLG = #{delFlg}
    			, LAST_EDIT_DATETIME = GETDATE()
    	WHEN NOT MATCHED THEN
    		INSERT (
				HEAD_CD
				, CODE
				, READ_CD
				, CODE_NM
    			, CODE_JP
    			, CODE_4TH
    			, REF_CHR1
    			, REF_CHR2
    			, REF_CHR3
    			, REF_CHR4
    			, REF_CHR5
    			, REF_NUM1
    			, REF_NUM2
    			, REF_NUM3
    			, REF_NUM4
    			, REF_NUM5
    			, PRIORITY
    			, REF_CNT
    			, DEL_FLG
    			, REGIST_DATETIME
    			, LAST_EDIT_DATETIME
			) VALUES (
				#{headCd}
				, #{code}
				, #{readCd}
				, #{codeJp}
				, #{codeJp}
				, #{code4Th}
				, #{refChr1}
				, #{refChr2}
				, #{refChr3}
				, #{refChr4}
				, #{refChr5}
				, #{refNum1}
				, #{refNum2}
				, #{refNum3}
				, #{refNum4}
				, #{refNum5}
				, #{priority}
				, #{refCnt}
				, #{delFlg}
				, GETDATE()
				, GETDATE()
			);
    </insert>
    
</mapper>