<?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.front.biz.dao.TsfCounselDao">

	<!-- 페이징을 위한 select절 상단 -->
	<sql id="selectForPagingHeader">
		SELECT *
		FROM   (
	</sql>

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>
	
	<!-- 1:1문의 생성 -->
	<insert id="createOneToOneQna" parameterType="Counsel" useGeneratedKeys="true" keyProperty="counselSq">
		/* TsfCounsel.createOneToOneQna */
		INSERT INTO TB_COUNSEL (
		       COUNSEL_SQ
		     , SITE_CD
		     , COUNSEL_CLSF
		     , COUNSEL_DCLSF
		     , CUST_NO
		     , CELL_PHNNO
		     , SMS_REQ_YN
		     , SMS_SEND_YN
		     , EMAIL
		     , EMAIL_REQ_YN
		     , EMAIL_SEND_YN
		     , COUNSEL_TYPE
		     , QUEST_TITLE
		     , QUEST_CONTENT
		     , QUEST_DT
		     , ORG_FILE_NM1
		     , SYS_FILE_NM1
		     , ORG_FILE_NM2
		     , SYS_FILE_NM2
		     , ANS_STAT
		     , DEL_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       NULL
		     , #{siteCd}
		     , #{counselClsf}
		     , #{counselDclsf}
		     , #{custNo}
		     , #{cellPhnno}
		     , #{smsReqYn} /*SMS(알림톡)답변수신여부*/
		     , 'N'
		     , #{email}
		     , 'N'
		     , 'N'
		     , 'C' /*상담유형(1:1문의)*/
		     , #{questTitle}
		     , #{questContent}
		     , NOW()
		     , #{orgFileNm1}
		     , #{sysFileNm1}
		     , #{orgFileNm2}
		     , #{sysFileNm2}
		     , 'G060_00' /*답변상태:대기중*/
		     , 'N'
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
	</insert>
	
	<!-- 1:1문의(총,답변완료,처리중) 건수 -->
	<select id="getOneToOneQnaCountInfo" parameterType="Counsel" resultType="Counsel">
		/* TsfCounsel.getOneToOneQnaCountInfo */
		SELECT COUNT(*)                                              AS TOT_CNT /*총건수*/
		     , SUM(CASE WHEN ANS_STAT = 'G060_20' THEN 1 ELSE 0 END) AS ANS_CNT /*답변완료건수*/
		     , SUM(CASE WHEN ANS_STAT = 'G060_10' THEN 1 ELSE 0 END) AS ING_CNT /*처리중건수*/
		FROM   TB_COUNSEL
		WHERE  CUST_NO = #{custNo}
		AND    SITE_CD = #{siteCd}
		AND    COUNSEL_TYPE = 'C' /*상담유형(1:1문의)*/
		AND    DEL_YN = 'N' /*삭제안된넘*/
	</select>
	
	<!-- 1:1문의 총건수 -->
	<select id="getOneToOneQnaTotalCount" parameterType="Counsel" resultType="int">
		/* TsfCounsel.getOneToOneQnaTotalCount */
		SELECT COUNT(*) AS CNT
		FROM   TB_COUNSEL
		WHERE  CUST_NO = #{custNo}
		AND    SITE_CD = #{siteCd}
		AND    COUNSEL_TYPE = 'C' /*상담유형(1:1문의)*/
		AND    DEL_YN = 'N' /*삭제안된넘*/
	</select>
	
	<!-- 1:1문의 목록 -->
	<select id="getOneToOneQnaList" parameterType="Counsel" resultType="Counsel">
		/* TsfCounsel.getOneToOneQnaList */
		<include refid="selectForPagingHeader"/>
		SELECT COUNSEL_SQ                                                     /*상담일련번호*/
		     , SITE_CD                                                        /*사이트코드*/
		     , FN_GET_CODE_NM('G059',COUNSEL_CLSF)        AS COUNSEL_CLSF_NM  /*상담분류명*/
		     , FN_GET_CODE_NM(COUNSEL_CLSF,COUNSEL_DCLSF) AS COUNSEL_DCLSF_NM /*상담상세분류명*/
		     , COUNSEL_DCLSF                                                  /*상담상세분류*/
		     , DATE_FORMAT(QUEST_DT,'%Y.%m.%d')           AS QUEST_DT         /*문의일자*/
		     , QUEST_TITLE                                                    /*문의제목*/
		     , QUEST_CONTENT                                                  /*질문내용*/
		     , ORG_FILE_NM1                                                   /*원본파일명1*/
		     , SYS_FILE_NM1                                                   /*시스템파일명1*/
		     , ORG_FILE_NM2                                                   /*원본파일명2*/
		     , SYS_FILE_NM2                                                   /*시스템파일명2*/
		     , ANS_TITLE                                                      /*답변제목*/
		     , ANS_CONTENT                                                    /*답변내용*/
		     , ANS_STAT                                                       /*답변상태코드*/
		     , FN_GET_CODE_NM('G060',ANS_STAT)            AS ANS_STAT_NM      /*답변상태명*/
		     , DATE_FORMAT(ANS_DT,'%Y.%m.%d')             AS ANS_DT           /*답변일자*/
		     , RANK() OVER(ORDER BY QUEST_DT DESC)        AS NUMB
		FROM   TB_COUNSEL
		WHERE  CUST_NO = #{custNo}
		AND    SITE_CD = #{siteCd}
		AND    COUNSEL_TYPE = 'C' /*상담유형(1:1문의)*/
		AND    DEL_YN = 'N' /*삭제안된넘*/
		<include refid="selectForPagingFooter"/>
	</select>
	
	<!-- 1:1문의 삭제 -->
	<update id="deleteOneToOneQna" parameterType="Counsel">
		/* TsfCounsel.deleteOneToOneQna */
		UPDATE TB_COUNSEL
		SET    DEL_YN = 'Y'
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  COUNSEL_SQ = #{counselSq}
		AND    CUST_NO = #{custNo}
		AND    ANS_STAT != 'G060_20' /*답변완료 상태가 아닐 때*/
	</update>

	<!-- 1:1문의 상세 -->
	<select id="getOneToOneQna" parameterType="Counsel" resultType="Counsel">
		/* TsfCounsel.getOneToOneQna */
		SELECT COUNSEL_SQ    /*상담일련번호*/
		     , SITE_CD       /*사이트코드*/
		     , COUNSEL_CLSF  /*상담분류*/
		     , COUNSEL_DCLSF /*상담상세분류*/
		     , QUEST_TITLE   /*문의제목*/
		     , QUEST_CONTENT /*질문내용*/
		     , ORG_FILE_NM1  /*원본파일명1*/
		     , SYS_FILE_NM1  /*시스템파일명1*/
		     , ORG_FILE_NM2  /*원본파일명2*/
		     , SYS_FILE_NM2  /*시스템파일명2*/
		     , CELL_PHNNO    /*휴대전화번호*/
		     , SMS_REQ_YN    /*SMS(알림톡)답변수신여부*/
		     , ANS_STAT      /*답변상태코드*/
		FROM   TB_COUNSEL
		WHERE  COUNSEL_SQ = #{counselSq}
		AND    CUST_NO = #{custNo}
		AND    SITE_CD = #{siteCd}
		AND    COUNSEL_TYPE = 'C' /*상담유형(1:1문의)*/
		AND    DEL_YN = 'N' /*삭제안된넘*/
	</select>
	
	<!-- 1:1문의 수정 -->
	<insert id="updateOneToOneQna" parameterType="Counsel">
		/* TsfCounsel.updateOneToOneQna */
		UPDATE TB_COUNSEL
		SET    COUNSEL_CLSF = #{counselClsf}
		     , COUNSEL_DCLSF = #{counselDclsf}
		     , CELL_PHNNO = #{cellPhnno}
		     , SMS_REQ_YN = #{smsReqYn}
		     , QUEST_TITLE = #{questTitle}
		     , QUEST_CONTENT = #{questContent}
		     , ORG_FILE_NM1 = #{orgFileNm1}
		     , SYS_FILE_NM1 = #{sysFileNm1}
		     , ORG_FILE_NM2 = #{orgFileNm2}
		     , SYS_FILE_NM2 = #{sysFileNm2}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  COUNSEL_SQ = #{counselSq}
		AND    CUST_NO = #{custNo}
		AND    ANS_STAT = 'G060_00' /*답변상태:대기중*/
	</insert>
	
	<!-- 1:1문의 이미지 삭제 -->
	<update id="deleteOneToOneQnaImage" parameterType="Counsel">
		/* TsfCounsel.deleteOneToOneQnaImage */
		UPDATE TB_COUNSEL
		SET    ORG_FILE_NM1 = #{orgFileNm1}
		     , SYS_FILE_NM1 = #{sysFileNm1}
		     , ORG_FILE_NM2 = #{orgFileNm2}
		     , SYS_FILE_NM2 = #{sysFileNm2}
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  COUNSEL_SQ = #{counselSq}
		AND    CUST_NO = #{custNo}
		AND    ANS_STAT != 'G060_20' /*답변완료 상태가 아닐 때*/
	</update>
	
	<select id="getOneToOneImgList" parameterType="Counsel"  resultType="Counsel">
		/* TsfCounsel.getOneToOnePhotoList */
		SELECT  Z.*
		      , ROW_NUMBER() OVER() AS RNUM
		FROM
		(
		   SELECT  SYS_FILE_NM1 AS SYS_FILE_NM
		         , COUNSEL_SQ 
		   FROM  TB_COUNSEL TC 
		   WHERE COUNSEL_SQ = #{counselSq}
		   UNION 
		   SELECT SYS_FILE_NM2 AS SYS_FILE_NM
		         , COUNSEL_SQ
		   FROM TB_COUNSEL TC 
		   WHERE COUNSEL_SQ = #{counselSq}
		)Z     
	</select>
	
	<!-- 1:1문의용 답변 템플릿 조회 -->
	<select id="getOnetooneQnaAnswerTemplate" parameterType="String" resultType="String">
		/* TsfCounsel.getOnetooneQnaAnswerTemplate */
		SELECT CD_DESC
		FROM   TB_COMMON_CODE
		WHERE  CD_GB = 'G059'
		AND    CD = #{counselClsf}
	</select>
	
	<!-- 상품문의 생성 -->
	<insert id="createGoodsQna" parameterType="Counsel">
		/* TsfCounsel.createGoodsQna */
		INSERT INTO TB_COUNSEL (
		       COUNSEL_SQ
		     , SITE_CD
		     , COUNSEL_CLSF
		     , CUST_NO
		     , CELL_PHNNO
		     , SMS_REQ_YN
		     , SMS_SEND_YN
		     , EMAIL
		     , EMAIL_REQ_YN
		     , EMAIL_SEND_YN
		     , REL_GOODS_CD
		     , COUNSEL_TYPE
		     , QUEST_TITLE
		     , QUEST_CONTENT
		     , QUEST_DT
		     , SECRET_YN
		     , ANS_STAT
		     , ANS_TRANS_YN
		     , ANS_COMP_CD
		     , ANS_TRANS_NO
		     , ANS_TRANS_DT
		     , DEL_YN
		     , REG_NO
		     , REG_DT
		     , UPD_NO
		     , UPD_DT
		)
		VALUES (
		       NULL
		     , #{siteCd}
		     , 'G596' /*문의종류:상품문의*/
		     , #{custNo}
		     , #{cellPhnno}
		     , #{smsReqYn} /*SMS(알림톡)답변수신여부*/
		     , 'N'
		     , #{email}
		     , 'N'
		     , 'N'
		     , #{relGoodsCd}
		     , 'G' /*상담유형(상품문의)*/
		     , CONCAT('[',#{relGoodsCd},'] 상품문의')
		     , #{questContent}
		     , NOW()
		     , IFNULL(#{secretYn},'N')
		     , 'G060_10' /*답변상태:처리중*/
		     , CASE WHEN IFNULL((SELECT SUPPLY_COMP_CD
		                         FROM   TB_GOODS
		                         WHERE  GOODS_CD = #{relGoodsCd}
		                         AND    SELF_GOODS_YN = 'N' /*입점상품*/
		                        ),'') = '' THEN
		                'N'
		            ELSE
		                'Y' /*입점상품은 답변의뢰한 것으로*/
		       END
		     , (SELECT SUPPLY_COMP_CD
		        FROM   TB_GOODS
		        WHERE  GOODS_CD = #{relGoodsCd}
		        AND    SELF_GOODS_YN = 'N' /*입점상품*/
		       )
		     , CASE WHEN IFNULL((SELECT SUPPLY_COMP_CD
		                         FROM   TB_GOODS
		                         WHERE  GOODS_CD = #{relGoodsCd}
		                         AND    SELF_GOODS_YN = 'N' /*입점상품*/
		                        ),'') = '' THEN
		                NULL
		            ELSE
		                24  /*입점상품은 답변의뢰자를 스타일이십사로*/
		       END
		     , CASE WHEN IFNULL((SELECT SUPPLY_COMP_CD
		                         FROM   TB_GOODS
		                         WHERE  GOODS_CD = #{relGoodsCd}
		                         AND    SELF_GOODS_YN = 'N' /*입점상품*/
		                        ),'') = '' THEN
		                NULL
		            ELSE
		                NOW() /*입점상품은 답변의뢰한 것으로*/
		       END
		     , 'N'
		     , #{regNo}
		     , NOW()
		     , #{updNo}
		     , NOW()
		)
	</insert>
	
	<!-- 상품문의(총,답변완료,처리중) 건수 -->
	<select id="getGoodsQnaCountInfo" parameterType="Counsel" resultType="Counsel">
		/* TsfCounsel.getGoodsQnaCountInfo */
		SELECT COUNT(*)                                              AS TOT_CNT /*총건수*/
		     , SUM(CASE WHEN A.ANS_STAT = 'G060_20' THEN 1 ELSE 0 END) AS ANS_CNT /*답변완료건수*/
		     , SUM(CASE WHEN A.ANS_STAT = 'G060_10' THEN 1 ELSE 0 END) AS ING_CNT /*처리중건수*/
		FROM   TB_COUNSEL A
		INNER  JOIN TB_GOODS G
		ON     A.REL_GOODS_CD = G.GOODS_CD
		WHERE  A.CUST_NO = #{custNo}
		AND    A.SITE_CD = #{siteCd}
		AND    A.COUNSEL_TYPE = 'G' /*상담유형(상품문의)*/
		AND    A.DEL_YN = 'N' /*삭제안된넘*/
	</select>
	
	<!-- 상품문의 총건수 -->
	<select id="getGoodsQnaTotalCount" parameterType="Counsel" resultType="int">
		/* TsfCounsel.getGoodsQnaTotalCount */
		SELECT COUNT(*) AS CNT
		FROM   TB_COUNSEL A
		     , TB_GOODS G
		<if test="goodsType != null and goodsType == 'G056_D'">
		     , TB_GOODS_COMPOSE C
		</if>
		WHERE  1 = 1
		<choose>
		    <when test="goodsType != null and goodsType == 'G056_D'">
		AND    A.REL_GOODS_CD = G.GOODS_CD
		AND    G.GOODS_CD = C.COMPS_GOODS_CD
		        <if test="relGoodsCd != null and relGoodsCd != '' ">
		AND    C.GOODS_CD = #{relGoodsCd}
		        </if>
		    </when>
		    <otherwise>
		AND    A.REL_GOODS_CD = G.GOODS_CD
		        <if test="relGoodsCd != null and relGoodsCd != '' ">
		AND    A.REL_GOODS_CD = #{relGoodsCd}
		        </if>
		    </otherwise>
		</choose>
		<if test="custNo != null and custNo > 0 ">
		AND    A.CUST_NO = #{custNo}
		</if>
		<if test="relGoodsCd != null and relGoodsCd != '' ">
		AND    A.REL_GOODS_CD = #{relGoodsCd}
		</if>
		<if test="secretYn != null and secretYn != '' ">
		AND    A.SECRET_YN = #{secretYn}
		</if>
		AND    A.SITE_CD = #{siteCd}
		AND    A.COUNSEL_TYPE = 'G' /*상담유형(상품문의)*/
		AND    A.DEL_YN = 'N' /*삭제안된넘*/
	</select>
	
	<!-- 상품문의 목록 - 고객센터용-->
	<select id="getGoodsQnaList" parameterType="Counsel" resultType="Counsel">
		/* TsfCounsel.getGoodsQnaList */
		<include refid="selectForPagingHeader"/>
		SELECT A.COUNSEL_SQ                                       /*상담일련번호*/
		     , A.SITE_CD                                          /*사이트코드*/
		     , DATE_FORMAT(A.QUEST_DT,'%Y.%m.%d')  AS QUEST_DT    /*문의일자*/
		     , A.SECRET_YN                                        /*비밀글여부*/
		     , A.CELL_PHNNO                                       /*휴대전화번호*/
		     , A.SMS_REQ_YN                                       /*SMS(알림톡)답변수신여부*/
		     , A.REL_GOODS_CD                                     /*관련상품코드*/
		     , G.GOODS_NM                                         /*상품명*/
		     , A.QUEST_TITLE                                      /*문의제목*/
		     , A.QUEST_CONTENT                                    /*질문내용*/
		     , A.ANS_TITLE                                        /*답변제목*/
		     , A.ANS_CONTENT                                      /*답변내용*/
		     , A.ANS_STAT                                         /*답변상태*/
		     , FN_GET_CODE_NM('G060',ANS_STAT)     AS ANS_STAT_NM /*답변상태명*/
		     , DATE_FORMAT(A.ANS_DT,'%Y.%m.%d')    AS ANS_DT      /*답변일자*/
		     , RANK() OVER(ORDER BY QUEST_DT DESC) AS NUMB
		FROM   TB_COUNSEL A
		     , TB_GOODS G
		WHERE  A.REL_GOODS_CD = G.GOODS_CD
		<if test="custNo != null and custNo > 0 ">
		AND    A.CUST_NO = #{custNo}
		</if>
		<if test="relGoodsCd != null and relGoodsCd != '' ">
		AND    A.REL_GOODS_CD = #{relGoodsCd}
		</if>
		<if test='secretYn != null and secretYn == "Y" '>
		AND    A.SECRET_YN = #{secretYn}
		</if>
		AND    A.SITE_CD = #{siteCd}
		AND    A.COUNSEL_TYPE = 'G' /*상담유형(상품문의)*/
		AND    A.DEL_YN = 'N' /*삭제안된넘*/
		<include refid="selectForPagingFooter"/>
	</select>
	
	<!-- 상품문의 목록 - 상품목록용 -->
	<select id="getGoodsQnaListForGoods" parameterType="Counsel" resultType="Counsel">
		/* TsfCounsel.getGoodsQnaListForGoods */
		<include refid="selectForPagingHeader"/>
		SELECT Z.*
		     , RANK() OVER(ORDER BY SELF_GB DESC, QUEST_DT_ORD DESC) AS NUMB
		FROM (
		SELECT A.COUNSEL_SQ                                       /*상담일련번호*/
		     , A.SITE_CD                                          /*사이트코드*/
		     , DATE_FORMAT(A.QUEST_DT,'%Y.%m.%d')  AS QUEST_DT    /*문의일자*/
		     , A.SECRET_YN                                        /*비밀글여부*/
		     , A.CELL_PHNNO                                       /*휴대전화번호*/
		     , A.SMS_REQ_YN                                       /*SMS(알림톡)답변수신여부*/
		     , A.REL_GOODS_CD                                     /*관련상품코드*/
		     , G.GOODS_NM                                         /*상품명*/
		     , A.QUEST_TITLE                                      /*문의제목*/
		     , A.QUEST_CONTENT                                    /*질문내용*/
		     , A.ANS_TITLE                                        /*답변제목*/
		     , A.ANS_CONTENT                                      /*답변내용*/
		     , A.ANS_STAT                                         /*답변상태*/
		     , FN_GET_CODE_NM('G060',ANS_STAT)     AS ANS_STAT_NM /*답변상태명*/
		     , DATE_FORMAT(A.ANS_DT,'%Y.%m.%d')    AS ANS_DT      /*답변일자*/
		     , C.CUST_ID                                          /*등록자ID*/
		     , (CASE WHEN A.CUST_NO  = #{custNo} THEN 1 ELSE 0 END) AS SELF_GB
		     , A.QUEST_DT AS QUEST_DT_ORD
		FROM   TB_COUNSEL A
		     , TB_GOODS G
		     , TB_CUSTOMER C
		WHERE  A.REL_GOODS_CD = G.GOODS_CD
		AND    A.CUST_NO = C.CUST_NO
		AND    A.REL_GOODS_CD = #{relGoodsCd}
		<if test='secretYn != null and secretYn == "N" '>
		AND    A.SECRET_YN = #{secretYn}
		</if>
		AND    A.SITE_CD = #{siteCd}
		AND    A.COUNSEL_TYPE = 'G' /*상담유형(상품문의)*/
		AND    A.DEL_YN = 'N' /*삭제안된넘*/
		) Z
		<include refid="selectForPagingFooter"/>
	</select>
	
	<!-- 상품문의 삭제 -->
	<update id="deleteGoodsQna" parameterType="Counsel">
		/* TsfCounsel.deleteGoodsQna */
		UPDATE TB_COUNSEL
		SET    DEL_YN = 'Y'
		     , UPD_NO = #{updNo}
		     , UPD_DT = NOW()
		WHERE  COUNSEL_SQ = #{counselSq}
		AND    CUST_NO = #{custNo}
		AND    ANS_STAT = 'G060_20' /*답변상태:답변완료*/
	</update>

</mapper>