<?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.TsfLoginDao">

	<!-- 로그인체크 정보 조회 -->
	<select id="getLoginCheckInfo" parameterType="Login" resultType="Login">
		/* TsfLogin.getLoginCheckInfo */
		SELECT CUST_NO                                                   /*고객번호*/
		     , CUST_ID                                                   /*고객ID*/
		     , CUST_NM                                                   /*고객명*/
		     , PASSWD                                                    /*비밀번호*/
		     , CUST_GB                                                   /*고객구분*/
		     , FN_GET_CODE_NM('G100',CUST_GB)          AS CUST_GB_NM     /*고객구분명*/
		     , CUST_GRADE                                                /*고객등급*/
		     , FN_GET_CODE_NM('G110',CUST_GRADE)       AS CUST_GRADE_NM  /*고객등급명*/
		     , CUST_STAT                                                 /*회원상태*/
		     , CELL_PHNNO                                                /*휴대전화번호*/
		     , EMAIL                                                     /*이메일*/
		     , #{snsType}                              AS SNS_TYPE       /*SNS유형*/
		     , #{snsId}                                AS SNS_ID         /*SNS가입ID*/
		     , IFNULL((SELECT CASE WHEN LOGIN_FAIL_CNT > 10 THEN 10
		                           ELSE LOGIN_FAIL_CNT
		                      END
		               FROM   TB_LOGIN_FAIL
		               WHERE  CUST_ID = #{custId}
		               AND    IP_ADDR = #{ipAddr}
		               AND    SITE_CD = #{siteCd}
		              ),0)                             AS LOGIN_FAIL_CNT /*로그인실패건수*/
		     , TEMP_PASSWD_YN                                            /*임시비밀번호여부*/
		     , IFNULL(DATEDIFF(NOW(),PASSWD_CHG_DT),0) AS PWD_CHG_DAY    /*비밀번호변경일자*/
		     , CI                                                        /*CI본인인증여부*/
		     , BIRTH_YMD                                                 /*생년월일*/
		     , SEX_GB                                                    /*성별*/
		     , APP_AGREE_YN
		     , DATE_FORMAT(APP_AGREE_DT, '%Y%m%d%H%i%S')  AS APP_AGREE_DT
		     , MK_AGREE_YN
		     , DATE_FORMAT(MK_AGREE_DT, '%Y%m%d%H%i%S')   AS MK_AGREE_DT
		FROM   TB_CUSTOMER A
		WHERE  1 = 1
		<choose>
		    <when test="snsType != null and snsType != ''"> <!-- SNS 로그인  -->
		AND    CUST_NO = (
		                  SELECT CUST_NO
		                  FROM   TB_CUSTOMER_SNS
		                  WHERE  SNS_TYPE = #{snsType}
		                  AND    SNS_ID = #{snsId}
		                 )
		    </when>
		    <when test="custNo != null and custNo != ''">
		AND    CUST_NO = #{custNo}
		    </when>
		    <otherwise> <!-- 일반로그인 -->
		AND    CUST_ID = #{custId}
		    </otherwise>
		</choose>
	</select>

	<!-- 로그인실패 남기기 -->
	<insert id="createLoginFail" parameterType="Login">
		/* TsfLogin.createLoginFail */
		INSERT INTO TB_LOGIN_FAIL (
		       CUST_ID
		     , IP_ADDR
		     , SITE_CD
		     , LOGIN_FAIL_CNT
		     , UPD_DT
		)
		VALUES (
		       #{custId}
		     , #{ipAddr}
		     , #{siteCd}
		     , 1
		     , NOW()
		)
		ON DUPLICATE KEY UPDATE
		       LOGIN_FAIL_CNT = CASE WHEN #{loginFailYn} = 'Y' THEN
		                                 CASE WHEN LOGIN_FAIL_CNT = 11 THEN LOGIN_FAIL_CNT
		                                      ELSE LOGIN_FAIL_CNT + 1
		                                 END
		                             ELSE
		                                 0
		                        END
		     , UPD_DT = CASE WHEN #{loginFailYn} = 'Y' THEN
		                                 CASE WHEN LOGIN_FAIL_CNT = 11 THEN UPD_DT
		                                      ELSE NOW()
		                                 END
		                     ELSE NOW()
		                END
	</insert>

	<!-- 로그인 실패건수 조회 -->
	<select id="getLoginFailCount" parameterType="Login" resultType="int">
		/* TsfLogin.getLoginFailCount */
		SELECT IFNULL((SELECT CASE WHEN LOGIN_FAIL_CNT > 10 THEN 10
		                           ELSE LOGIN_FAIL_CNT
		                      END
		               FROM   TB_LOGIN_FAIL
		               WHERE  CUST_ID = #{custId}
		               AND    IP_ADDR = #{ipAddr}
		               AND    SITE_CD = #{siteCd}
		              ),0) AS CNT
		FROM   DUAL
	</select>
	
	<!-- 로그인실패정보 조회 -->
	<select id="getLoginFailInfo" parameterType="Login" resultType="Login">
		/* TsfLogin.getLoginFailInfo */
		SELECT CASE WHEN LOGIN_FAIL_CNT > 10 THEN 10
		            ELSE LOGIN_FAIL_CNT

		        END           AS LOGIN_FAIL_CNT
		     , CASE WHEN LOGIN_FAIL_CNT <![CDATA[>=]]> 10 THEN
		                180 - TIMESTAMPDIFF(SECOND,UPD_DT,NOW())
		            ELSE
		                0
		       END            AS BLOCK_SECS /*로그인블락시간(초)*/
		FROM   TB_LOGIN_FAIL
		WHERE  CUST_ID = #{custId}
		AND    IP_ADDR = #{ipAddr}
		AND    SITE_CD = #{siteCd}
	</select>

	<!-- 최종로그인일시 Update -->
	<update id="updateLastLoginDate" parameterType="Integer">
		/* TsfLogin.updateLastLoginDate */
		UPDATE TB_CUSTOMER
		SET    LOGIN_LDT = NOW()
		     , UPD_NO = #{custNo}
		     , UPD_DT = NOW()
		WHERE  CUST_NO = #{custNo}
	</update>

	<!-- 로그인이력 남기기 -->
	<insert id="createLoginHistory" parameterType="Login">
		/* TsfLogin.createLoginHistory */
		INSERT INTO TB_LOGIN_HST (
		       LOGIN_HST_SQ
		     , CUST_NO
		     , IP_ADDR
		     , SITE_CD
		     , FRONT_GB
		     , LOGIN_DT
		     , REG_NO
		     , REG_DT
		)
		VALUES (
		       NULL
		     , #{custNo}
		     , #{ipAddr}
		     , #{siteCd}
		     , #{frontGb}
		     , NOW()
		     , #{custNo}
		     , NOW()
		)
	</insert>

	<!-- 로그인 세션정보 조회 -->
	<select id="getLoginSessionInfo" parameterType="Login" resultType="Login">
		/* TsfLogin.getLoginSessionInfo */
		SELECT CUST_NO                                                /*고객번호*/
		     , CUST_ID                                                /*고객ID*/
		     , CUST_NM                                                /*고객명*/
		     , PASSWD                                                 /*비밀번호*/
		     , CELL_PHNNO                                             /*휴대전화번호*/
		     , EMAIL                                                  /*이메일*/
		     , CUST_GB                                                /*회원구분코드*/
		     , FN_GET_CODE_NM('G100',CUST_GB)        AS CUST_GB_NM    /*회원구분명*/
		     , CUST_GRADE                                             /*회원등급코드*/
		     , FN_GET_CODE_NM('G110',CUST_GRADE)     AS CUST_GRADE_NM /*회원등급명*/
		     , SITE_CD                                                /*사이트코드*/
		     , TO_CHAR(LOGIN_LDT,'YYYYMMDDHH24MISS') AS LOGIN_LDT     /*최종로그인일시*/
		     , MANAGED_RSN                                            /*관리대상지정사유(공통코드G120)*/
		     , CASE WHEN ADD_MONTHS(PASSWD_CHG_DT,6) <![CDATA[<]]> NOW() THEN 'Y'
		            ELSE 'N'
		       END                                   AS PASSWD_CHG_YN /*비밀번호변경도래여부*/
		FROM   TB_CUSTOMER A
		WHERE  CUST_ID = #{custId}
		AND    CUST_STAT = 'G104_10' /*활동회원*/
	</select>

	<!-- 로그인유지토큰 생성 -->
	<insert id="createPersistentToken" parameterType="PersistentToken">
		/* TsfLogin.createPersistentToken */
		INSERT INTO TB_PERSISTENT_TOKEN (
		       CUST_NO
		     , REMEMBERME_TOKEN
		     , LIMIT_DT
		)
		VALUES (
		       #{custNo}
		     , #{remembermeToken}
		     , STR_TO_DATE(#{limitDt},'%Y%m%d%H%i%S')
		)
		ON DUPLICATE KEY UPDATE
		       LIMIT_DT = STR_TO_DATE(#{limitDt},'%Y%m%d%H%i%S')
	</insert>

	<!-- 로그인유지토큰 조회 -->
	<select id="getPersistentToken" parameterType="String" resultType="PersistentToken">
		/* TsfLogin.getPersistentToken */
		SELECT PT.CUST_NO
		     , PT.REMEMBERME_TOKEN
		     , PT.LIMIT_DT
		     , C.CUST_ID
		FROM   TB_PERSISTENT_TOKEN PT
		     , TB_CUSTOMER C
		WHERE  PT.CUST_NO = C.CUST_NO
		AND    PT.REMEMBERME_TOKEN = #{remembermeToken}
		AND    NOW() <![CDATA[<=]]> PT.LIMIT_DT
	</select>

	<!-- 로그인유지토큰 갱신 -->
	<update id="updatePersistentToken" parameterType="PersistentToken">
		/* TsfLogin.updatePersistentToken */
		UPDATE TB_PERSISTENT_TOKEN
		SET    REMEMBERME_TOKEN = #{remembermeToken}
		     , LIMIT_DT = STR_TO_DATE(#{limitDt},'%Y%m%d%H%i%S')
		WHERE  CUST_NO = #{custNo}
		AND    REMEMBERME_TOKEN = #{prevRemembermeToken}
	</update>

	<!-- 로그인유지토근 삭제 -->
	<delete id="deletePersistentToken" parameterType="String">
		/* TsfLogin.deletePersistentToken */
		DELETE FROM TB_PERSISTENT_TOKEN
		WHERE  REMEMBERME_TOKEN = #{remembermeToken}
	</delete>

	<!-- 로그인 실패횟수 초기화-->
	<update id="updateLoginFailInfo" parameterType="Login">
		UPDATE TB_LOGIN_FAIL
		SET    LOGIN_FAIL_CNT = 0
		WHERE  CUST_ID = #{custId}
		AND    IP_ADDR = #{ipAddr}
		AND    SITE_CD = #{siteCd}
	</update>

</mapper>