<?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.TsaPollDao">
	<!-- POLL 저장 -->
	<update id="savePoll" parameterType="Poll">
		<selectKey keyProperty="pollSqTemp" resultType="int" order="AFTER">
			SELECT LAST_INSERT_ID()
		</selectKey>
		/* TsaPoll.savePoll */
		INSERT INTO
	      TB_POLL(
	            POLL_SQ 
	          , SITE_CD
	          , POLL_TITLE
	          , POLL_CONTENT
	          , POLL_STDT
	          , POLL_EDDT
	          , DUP_PARTI_YN
	          , FST_COM_YN
	          , PARTI_CNT
	          , DEL_YN
	          , REG_NO
	          , REG_DT
	          , UPD_NO
	          , UPD_DT        
	      )
	   VALUES (
	     	    #{pollSqTemp}
	   		   ,#{siteCd}
	   		   ,#{pollTitle}
	   		   ,#{pollContent}
	   		   ,DATE_FORMAT(#{pollStdt}, '%Y-%m-%d %H:%i:%s') 
			   ,DATE_FORMAT(#{pollEddt}, '%Y-%m-%d %H:%i:%s') 
	   		   ,#{dupPartiYn}
	   		   ,#{fstComYn}
	   		   ,#{partiCnt}
	   		   , 'N'
	   		   , #{regNo}                                          
               , now()                                           
               , #{updNo}                                            
               , now()
	   	     )
	   ON
	      DUPLICATE KEY
	   UPDATE
	        POLL_TITLE = #{pollTitle}                                       
		  , SITE_CD = #{siteCd}   	
		  , DUP_PARTI_YN = #{dupPartiYn}
		  , FST_COM_YN   = #{fstComYn} 
		  , PARTI_CNT    = #{partiCnt}                                        
		  , POLL_STDT = DATE_FORMAT(#{pollStdt}, '%Y-%m-%d %H:%i:%s')      
		  , POLL_EDDT = DATE_FORMAT(#{pollEddt}, '%Y-%m-%d %H:%i:%s')      
		  , UPD_NO = #{updNo}                                            
		  , UPD_DT = now()                              
	</update>
	
	<!-- POLL 문항 삭제 -->
	<delete id="deletePollQuestion" parameterType="Poll">
		/* TsaPoll.deletePollQuestion */
		DELETE 
		FROM TB_POLL_QUESTION
		WHERE  1=1
		   AND POLL_SQ = #{pollSq}
	</delete>
	
	<!-- POLL 문항 등록 -->
	<insert id="createPollQuestion" parameterType="Poll">
	/* TsaPoll.createPollQuestion */
		INSERT INTO TB_POLL_QUESTION (
		                POLL_QSQ
		       		  ,	POLL_SQ
		       		  , POLL_QTITLE
		       		  , POLL_QTYPE
		       		  , POLL_QVAL1
		       		  , POLL_QVAL2
		       		  , POLL_QVAL3
		       		  , POLL_QVAL4
		       		  , POLL_QVAL5
		       		  , POLL_QVAL6
		       		  , POLL_QVAL7
		       		  , POLL_QVAL8
		       		  , POLL_QVAL9
		       		  , POLL_QVAL10
		       		  , DISP_ORD
		       		  , DEL_YN
		       		  , REG_NO
		       		  , REG_DT
		       		  , UPD_NO
		       		  , UPD_DT
		       ) VALUES (
		            #{pollQsq}
		          , #{pollSq}
		          , #{pollQtitle}
		          , #{pollQtype}
		          , #{pollQval1}
		          , #{pollQval2}
		          , #{pollQval3}
		          , #{pollQval4}
		          , #{pollQval5}
		          , #{pollQval6}
		          , #{pollQval7}
		          , #{pollQval8}
		          , #{pollQval9}
		          , #{pollQval10}
		          , #{dispOrd}
		          , 'N'
		          , #{regNo}
		          , now()
		          , #{updNo}
		          , now()
		       )
	   ON
	      DUPLICATE KEY
	   UPDATE
		   POLL_QTITLE = #{pollQtitle} 
		 , POLL_QTYPE  = #{pollQtype}  
		 , POLL_QVAL1  = #{pollQval1}  
		 , POLL_QVAL2  = #{pollQval2}  
		 , POLL_QVAL3  = #{pollQval3}  
		 , POLL_QVAL4  = #{pollQval4}  
		 , POLL_QVAL5  = #{pollQval5}  
		 , POLL_QVAL6  = #{pollQval6}  
		 , POLL_QVAL7  = #{pollQval7}  
		 , POLL_QVAL8  = #{pollQval8}  
		 , POLL_QVAL9  = #{pollQval9}  
		 , POLL_QVAL10 = #{pollQval10} 
		 , DISP_ORD    = #{dispOrd}    
		 , UPD_NO  = #{updNo}             
		 , UPD_DT = now()                              
	</insert>
	
	<!-- poll 단일 문항 삭제 -->
	<update id="removeQuestion" parameterType="Poll">
		UPDATE tb_poll_question 
		SET DEL_YN = 'Y'
		WHERE POLL_QSQ = #{pollQsq}
	</update>
	
	<!-- POLL 목록 -->
	<select id="getPollList" parameterType="Poll" resultType="Poll">
		/* TsaPoll.getPollList */
		 SELECT   M.POLL_SQ                                                                                                     
		        , M.POLL_TITLE                                                                                                 
		        , M.DUP_PARTI_YN                                                                                               
		        , M.FST_COM_YN                                                                                                 
		        , M.PARTI_CNT                                                                                                  
		        , M.SITE_CD 
		        , M.POLL_STDT
		        , M.POLL_EDDT
		        , M.REG_DT                                                                                                   
		        , M.POLL_QSQ                                                                                                   
		        , M.POLL_QTITLE                                                                                                
		        , M.POLL_QTYPE      
		        , M.POLL_QVAL1                                                                                                 
		        , M.POLL_QVAL2                                                                                                 
		        , M.POLL_QVAL3                                                                                                 
		        , M.POLL_QVAL4                                                                                                 
		        , M.POLL_QVAL5                                                                                                 
		        , M.POLL_QVAL6                                                                                                 
		        , M.POLL_QVAL7                                                                                                 
		        , M.POLL_QVAL8                                                                                                 
		        , M.POLL_QVAL9                                                                                                 
		        , M.POLL_QVAL10    
		        , M.POLL_QTYPE_NM
		 FROM
		 (		      
		   SELECT A.POLL_SQ
		        , A.POLL_TITLE 
		        , A.DUP_PARTI_YN 
		        , A.FST_COM_YN 
		        , A.PARTI_CNT 
		        , A.SITE_CD
		        , A.POLL_STDT
		        , A.POLL_EDDT
		        , A.REG_DT 
		        , B.POLL_QSQ 
		        , B.POLL_QTITLE 
		        , CASE B.POLL_QTYPE WHEN 10 THEN '단수형' WHEN 20 THEN '복수형' WHEN 30 THEN '단답형' ELSE '서술형' END AS POLL_QTYPE_NM
		        , B.POLL_QVAL1 
		        , B.POLL_QVAL2 
		        , B.POLL_QVAL3 
		        , B.POLL_QVAL4 
		        , B.POLL_QVAL5 
		        , B.POLL_QVAL6 
		        , B.POLL_QVAL7 
		        , B.POLL_QVAL8
		        , B.POLL_QVAL9
		        , B.POLL_QVAL10
		        , B.POLL_QTYPE
		  FROM   TB_POLL A LEFT JOIN TB_POLL_QUESTION B ON A.POLL_SQ = B.POLL_SQ 
		  WHERE 1=1
		     AND A.DEL_YN = 'N'
		     AND B.DEL_YN = 'N'
		   <if test="pollSq != null and pollSq != ''">
		        AND    A.POLL_SQ = #{pollSq}
			</if>
			<if test="stDate != null and stDate != '' and edDate != null and edDate != ''">
		        AND    A.REG_DT <![CDATA[ >= ]]> DATE_FORMAT(#{stDate},'%Y-%m-%d %H:%i:%S')
		        AND    A.REG_DT <![CDATA[ <= ]]> DATE_FORMAT(#{edDate},'%Y-%m-%d %H:%i:%S')
			</if>
			<if test="siteCd != null and siteCd != ''">
		        AND    A.SITE_CD = #{siteCd}
			</if>
			<if test="pollTitle != null and pollTitle != ''">
		        AND    A.POLL_TITLE LIKE CONCAT('%', #{pollTitle},'%') 
			</if>
		 GROUP  BY  A.POLL_SQ
	              , A.POLL_TITLE 
	              , A.DUP_PARTI_YN 
	              , A.FST_COM_YN 
	              , A.PARTI_CNT 
	              , A.SITE_CD 
	              , A.POLL_STDT
	              , A.POLL_EDDT
	              , B.POLL_QSQ 
	              , B.POLL_QTITLE 
	              , B.POLL_QVAL1 
	              , B.POLL_QVAL2 
	              , B.POLL_QVAL3 
	              , B.POLL_QVAL4 
	              , B.POLL_QVAL5 
	              , B.POLL_QVAL6 
	              , B.POLL_QVAL7 
	              , B.POLL_QVAL8
	              , B.POLL_QVAL9
	              , B.POLL_QVAL10
         )M
         ORDER BY M.REG_DT DESC
	
	</select>
	
	<!-- POLL관리 투표자 목록 조회 -->
	<select id="getPollVoterList" parameterType="Poll" resultType="Poll">
		/* TsaPoll.getPollVoterList */
		WITH TAB AS (
		    -- 설문의 질문 목록
		    SELECT POLL_SQ
		         , POLL_QSQ
		         , POLL_QTITLE
		         , RANK() OVER(ORDER BY POLL_QSQ) AS RK
		    FROM   TB_POLL_QUESTION
		    WHERE  POLL_SQ = #{pollSq}
		    AND    DEL_YN = 'N'
		    ORDER  BY POLL_QSQ
		    LIMIT 10
		)
		SELECT Z.ANS_CUST_NO
		      ,Z.REG_DT AS VOTE_DT
		      ,Z.POLL_SQ
		      ,Z.ANS_INDEX
		      ,C.CUST_NM 
		      ,C.CUST_ID 
		      ,Z.DUMMY1  
		      ,Z.DUMMY2  
		      ,Z.DUMMY3  
		      ,Z.DUMMY4  
		      ,Z.DUMMY5  
		      ,Z.DUMMY6  
		      ,Z.DUMMY7  
		      ,Z.DUMMY8  
		      ,Z.DUMMY9  
		      ,Z.DUMMY10 
		FROM (
		-- 고객별 답변 목록
		SELECT B.ANS_CUST_NO
		     , B.ANS_INDEX
		     , A.POLL_SQ
		     , DATE_FORMAT(B.REG_DT, '%Y-%m-%d') AS REG_DT 
		     , MAX(CASE WHEN A.RK = 1  THEN B.DUMMY ELSE '' END) AS DUMMY1
		     , MAX(CASE WHEN A.RK = 2  THEN B.DUMMY ELSE '' END) AS DUMMY2
		     , MAX(CASE WHEN A.RK = 3  THEN B.DUMMY ELSE '' END) AS DUMMY3
		     , MAX(CASE WHEN A.RK = 4  THEN B.DUMMY ELSE '' END) AS DUMMY4
		     , MAX(CASE WHEN A.RK = 5  THEN B.DUMMY ELSE '' END) AS DUMMY5
		     , MAX(CASE WHEN A.RK = 6  THEN B.DUMMY ELSE '' END) AS DUMMY6
		     , MAX(CASE WHEN A.RK = 7  THEN B.DUMMY ELSE '' END) AS DUMMY7
		     , MAX(CASE WHEN A.RK = 8  THEN B.DUMMY ELSE '' END) AS DUMMY8
		     , MAX(CASE WHEN A.RK = 9  THEN B.DUMMY ELSE '' END) AS DUMMY9
		     , MAX(CASE WHEN A.RK = 10 THEN B.DUMMY ELSE '' END) AS DUMMY10
		FROM   TAB A
		     , TB_POLL_ANSWER B
		WHERE  A.POLL_QSQ = B.POLL_QSQ
		GROUP  BY B.ANS_CUST_NO
		         ,DATE_FORMAT(B.REG_DT, '%Y-%m-%d')
		         ,B.ANS_INDEX
		         ,A.POLL_SQ
		)Z
		INNER JOIN TB_CUSTOMER C ON Z.ANS_CUST_NO = C.CUST_NO 
		WHERE Z.ANS_INDEX != 0
		ORDER BY Z.REG_DT ASC
	</select>
	
	<!-- POLL QUESTION 보기 조회 -->
	<select id="getPollQuestion" parameterType="Poll" resultType="Poll">
		/* TsaPoll.getPollQuestion */
		SELECT POLL_QSQ 
		      ,POLL_QTITLE 
		      ,POLL_QTYPE 
		      ,POLL_QVAL1 
		      ,POLL_QVAL2
		      ,POLL_QVAL3 
		      ,POLL_QVAL4 
		      ,POLL_QVAL5 
		      ,POLL_QVAL6 
		      ,POLL_QVAL7 
		      ,POLL_QVAL8 
		      ,POLL_QVAL9 
		      ,POLL_QVAL10 
		FROM TB_POLL_QUESTION
		WHERE POLL_QSQ = #{pollQsq}
	</select>
	
	<!-- POLL ANSWER 조회 -->
	<select id="getPollAnswer" parameterType="Poll" resultType="Poll">
		/* TsaPoll.getPollAnswer */
			SELECT M.NAME
				  ,COUNT(M.NAME) AS VOTE_CNT
				  ,M.POLL_QSQ
				  ,M.POLL_QTYPE
				  ,(COUNT(M.NAME)/CNT)*100 AS VOTE_RATE
			FROM 
			(
				SELECT
				  A.POLL_QSQ
				  ,SUBSTRING_INDEX(SUBSTRING_INDEX(A.DUMMY , '|', NUMBERS.N), '|', -1) NAME
				  ,A.POLL_QTYPE  
				  ,COUNT(*) OVER(PARTITION BY POLL_QSQ)  AS CNT
				FROM
					  (SELECT 1 N UNION ALL
					   SELECT 2 UNION ALL SELECT 3 UNION ALL
					   SELECT 4 UNION ALL SELECT 5 UNION ALL
					   SELECT 6 UNION ALL SELECT 7 UNION ALL
					   SELECT 8 UNION ALL SELECT 9 UNION ALL
					   SELECT 10 ) NUMBERS 
				   INNER JOIN TB_POLL_ANSWER A ON CHAR_LENGTH(A.DUMMY)-CHAR_LENGTH(REPLACE(A.DUMMY, '|', ''))>=NUMBERS.N-1
				WHERE POLL_QSQ = #{pollQsq}
			)M
			GROUP BY NAME,M.POLL_QSQ,M.POLL_QTYPE
	</select>
	
	<select id="getPollAllQuestionList" parameterType="Poll" resultType="Poll">
		/* TsaPoll.getPollAllQuestionList */
		SELECT POLL_QTITLE 
		FROM TB_POLL_QUESTION TPQ 
		WHERE POLL_SQ = #{pollSq}
		AND DEL_YN = 'N'
	</select>
</mapper>