<?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.style24.admin.biz.dao.TsaMailTemplateDao">

	<!-- 메일헤더푸터 목록 -->
	<select id="getMailHeaderFooterList" parameterType="MailHeaderFooter" resultType="MailHeaderFooter">
		/* TsaMailTemplate.getMailHeaderFooterList */
		SELECT MAILHF_SQ                                    /*메일헤더푸터일련번호*/
		   	 , MAILHF_NM                                    /*메일헤더푸터명*/
		     , MAILHF_GB                                    /*메일헤더푸터구분*/
		     , MAILHF_CONTENT                               /*메일헤더푸터내용*/
		     , USE_YN                                       /*사용여부*/
		     , FN_GET_USER_NM(REG_NO)             AS REG_NM
		     , DATE_FORMAT(REG_DT,'%Y%m%d%H%i%S') AS REG_DT
		     , FN_GET_USER_NM(UPD_NO)             AS UPD_NM
		     , DATE_FORMAT(UPD_DT,'%Y%m%d%H%i%S') AS UPD_DT
		FROM   TB_MAILHF
		WHERE  1 = 1
		<if test="mailhfGb !=null and mailhfGb !='' ">
		AND    MAILHF_GB = #{mailhfGb}
		</if>
		<if test="mailhfNm != null and mailhfNm !='' ">
		AND    LOWER(MAILHF_NM) LIKE CONCAT('%',LOWER(#{mailhfNm}),'%')
		</if>
		ORDER  BY MAILHF_SQ
	</select>
	
	<!-- 메일헤더푸터 조회 -->
	<select id="getMailHeaderFooter" parameterType="Integer" resultType="MailHeaderFooter">
		/* TsaMailTemplate.getMailHeaderFooter */
		SELECT MAILHF_SQ      /*메일헤더푸터일련번호*/
		     , MAILHF_NM      /*메일헤더푸터명*/
		     , MAILHF_GB      /*메일헤더푸터구분*/
		     , MAILHF_CONTENT /*메일헤더푸터내용*/
		FROM   TB_MAILHF
		WHERE  MAILHF_SQ = #{mailhfSq}
	</select>
	
	<!-- 메일헤더푸터 저장 -->
	<insert id="saveMailHeaderFooter" parameterType="MailHeaderFooter">
		/* TsaMailTemplate.saveMailHeaderFooter */
		INSERT INTO TB_MAILHF (
		       MAILHF_SQ
		     , MAILHF_NM
		     , MAILHF_GB
		     , MAILHF_CONTENT
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{mailhfSq}
		     , #{mailhfNm}
		     , #{mailhfGb}
		     , #{mailhfContent}
		     , 'Y'
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       MAILHF_NM = #{mailhfNm}
		     , MAILHF_GB = #{mailhfGb}
		     , MAILHF_CONTENT = #{mailhfContent}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 메일헤더푸터 삭제 -->
	<update id='deleteMailHeaderFooter' parameterType='MailHeaderFooter'>
		/* TsaMailTemplate.deleteMailHeaderFooter */
		UPDATE TB_MAILHF
		SET    USE_YN = 'N'
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  MAILHF_SQ = #{mailhfSq}
	</update>
	
	<!-- 메일템플릿 목록 -->
	<select id="getMailTemplateList" parameterType="MailTemplate" resultType="MailTemplate">
		/* TsaMailTemplate.getMailTemplateList */
		SELECT MAILT_SQ                                     /*메일템플릿일련번호*/
		     , MAILT_GB                                     /*메일템플릿구분*/
		     , MAILT_NM                                     /*메일템플릿명*/
		     , USE_YN                                       /*사용여부*/
		     , FN_GET_USER_NM(REG_NO)             AS REG_NM /*등록자명*/
		     , DATE_FORMAT(REG_DT,'%Y%m%d%H%i%S') AS REG_DT /*등록일시*/
		     , FN_GET_USER_NM(UPD_NO)             AS UPD_NM /*수정자명*/
		     , DATE_FORMAT(UPD_DT,'%Y%m%d%H%i%S') AS UPD_DT /*수정일시*/
		FROM   TB_MAILT
		WHERE  1 = 1
		<if test="mailtSq != null and mailtSq != ''">
		AND    MAILT_SQ = #{mailtSq}
		</if>
		<if test="mailtGb != null and mailtGb != ''">
		AND    MAILT_GB = #{mailtGb}
		</if>
		<if test="mailtNm != null and mailtNm != ''">
		AND    LOWER(MAILT_NM) LIKE CONCAT('%',LOWER(#{mailtNm}),'%')
		</if>
		<if test="useYn != null and useYn != ''">
		AND    USE_YN = #{useYn}
		</if>
		ORDER  BY MAILT_SQ
	</select>
	
	<!-- 메일헤더푸터콤보박스 목록 -->
	<select id="getMailHeaderFooterComboList" parameterType="String" resultType="CommonCode">
		/* TsaMailTemplate.getMailHeaderFooterComboList */
		SELECT MAILHF_SQ AS CD
		     , MAILHF_NM AS CD_NM
		FROM   TB_MAILHF
		WHERE  MAILHF_GB = #{mailhfGb}
		AND    USE_YN = 'Y' /*사용하는넘만*/
		ORDER  BY MAILHF_SQ
	</select>
	
	<!-- 메일템플릿 저장 -->
	<insert id="saveMailTemplate" parameterType="MailTemplate" keyProperty="mailtSq">
		/* TsaMailTemplate.saveMailTemplate */
		INSERT INTO TB_MAILT (
		       MAILT_SQ
		     , MAILT_NM
		     , MAILT_GB
		     , MAILH_SQ
		     , MAILF_SQ
		     , MAIL_CONTENT
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{mailtSq}
		     , #{mailtNm}
		     , #{mailtGb}
		     , #{mailhSq}
		     , #{mailfSq}
		     , #{mailContent}
		     , 'Y'
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       MAILT_NM = #{mailtNm}
		     , MAILT_GB = #{mailtGb}
		     , MAILH_SQ = #{mailhSq}
		     , MAILF_SQ = #{mailfSq}
		     , MAIL_CONTENT = #{mailContent}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 메일템플릿코너 저장 -->
	<insert id="saveMailTemplateCorner" parameterType="MailTemplateCorner" keyProperty="mailtcSq">
		/* TsaMailTemplate.saveMailTemplateCorner */
		INSERT INTO TB_MAILTC (
		       MAILTC_SQ
		     , MAILT_SQ
		     , MAILTC_NM
		     , GOODS_CPR
		     , MAILTC_LINK
		     , USE_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{mailtcSq}
		     , #{mailtSq}
		     , #{mailtcNm}
		     , #{goodsCpr}
		     , #{mailtcLink}
		     , 'Y'
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       MAILTC_NM = #{mailtcNm}
		     , GOODS_CPR = #{goodsCpr}
		     , MAILTC_LINK = #{mailtcLink}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 메일템플릿코너상품 저장 -->
	<insert id="saveMailTemplateCornerGoods" parameterType="MailTemplateGoods">
		/* TsaMailTemplate.saveMailTemplateCornerGoods */
		INSERT INTO TB_MAILTC_GOODS (
		       MAILTC_SQ
		     , GOODS_CD
		     , DISP_ORD
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       #{mailtcSq}
		     , #{goodsCd}
		     , #{dispOrd}
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       DISP_ORD = #{dispOrd}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
	</insert>
	
	<!-- 메일템플릿코너 삭제 -->
	<delete id="deleteMailTemplateCorner" parameterType='MailTemplateCorner'>
		/* TsaMailTemplate.deleteMailTemplateCorner */
		UPDATE TB_MAILTC
		SET    USE_YN = 'N'
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  MAILTC_SQ = #{mailtcSq}
	</delete>
	
	<!-- 메일템플릿코너상품 삭제 -->
	<delete id="deleteMailTemplateCornerGoods" parameterType='MailTemplateGoods'>
		/* TsaMailTemplate.deleteMailTemplateCornerGoods */
		DELETE FROM TB_MAILTC_GOODS
		WHERE  MAILTC_SQ = #{mailtcSq}
		AND    GOODS_CD = #{goodsCd}
	</delete>
	
	<!-- 메일템플릿 삭제 -->
	<update id="deleteMailTemplate" parameterType="MailTemplate">
		/* TsaMailTemplate.deleteMailTemplate */
		UPDATE  TB_MAILT
		SET     USE_YN = #{useYn}
		      , UPD_NO = #{updNo}
		      , UPD_DT = NOW()
		WHERE   MAILT_SQ = #{mailtSq}
	</update>
	
</mapper>