<?xml version="1.0" encoding="UTF-8"?><!--Converted at: Wed May 11 15:49:38 KST 2016-->
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="LoginDAO">

 	<select id="selectLoginUserInfo" parameterType="NlibLoginVO" resultType="NlibLoginVO">
 	    <![CDATA[
		SELECT
		          A.MB_INFO_ID
		        , A.LOGIN_USER_ID
		        , A.NAME
		        , A.MOBILE_NO
		        , A.PASSWORD
		        , A.EMAIL
		        , A.GENDER
		        , A.ZIP_CODE
		        , A.ADDRESS
		        , A.ADDRESS_DETAIL
		        , A.BIRTHDAY
		        , A.STATUS
		        , DATE_FORMAT(A.DMT_PROC_DD, '%Y%m%d%H%i%s') AS DMT_PROC_DD
		        , A.JOIN_DATE
		        , DATE_FORMAT(A.VERIFICATION_DD, '%Y%m%d%H%i%s') AS VERIFICATION_DD
		        , DATE_FORMAT(A.LAST_ACCESS_DD, '%Y%m%d%H%i%s') AS LAST_ACCESS_DD
		        , A.LEAVE_SITE_DATE
		        , A.REG_ID
		        , DATE_FORMAT(A.REG_DD, '%Y%m%d%H%i%s') AS REG_DD
		        , A.MOD_ID
		        , DATE_FORMAT(A.MOD_DD, '%Y%m%d%H%i%s') AS MOD_DD
		        , B.SNS_TYPE
		        , B.SNS_ID
                , 'ROLE_USER' AS AUTHORITY_LIST
                , C.JOIN_COUNCIL_CD 
		FROM UAC_CLTR_DB.MB_INFO A /* 사용자정보 */
		JOIN UAC_CLTR_DB.MB_SNS B  /* 사용자 SNS 정보 */
		ON A.MB_INFO_ID = B.MB_INFO_ID 
        LEFT OUTER JOIN UAC_CLTR_DB.MB_SITE /* 가입문화원정보 */ C 
        ON A.MB_INFO_ID = C.MB_INFO_ID AND C.JOIN_COUNCIL_CD = #{joinCouncilCd}
		WHERE B.SNS_ID = #{snsId}
		  AND UPPER(SNS_TYPE) = UPPER(#{snsType})
		  AND B.UNLINK_DD IS NULL /* 연동해제건 제외 */
		LIMIT 1	
		]]>		
 	</select>
 	
 	<select id="selectCouncilInfoByDnsHost" parameterType="String" resultType="HashMap">
		SELECT JOIN_COUNCIL_CD AS councilCd
		     , JOIN_COUNCIL_NM AS councilNm
		FROM UAC_CLTR_DB.TMP_MB_SITE_INFO
		WHERE JOIN_COUNCIL_DNS_HOST = #{councilDnsHost}
        LIMIT 1         
    </select>
    
    <update id="activateDormantAcc" parameterType="NlibLoginVO">
        UPDATE UAC_CLTR_DB.MB_INFO A /* 사용자정보 */
        SET   A.STATUS = 'S' /* 정상 */
            , A.DMT_PROC_DD = NULL
            , A.MOD_ID = #{mbInfoId}
            , A.MOD_DD = NOW() 
        WHERE MB_INFO_ID = #{mbInfoId}
        AND A.STATUS = 'P' /* 휴면 */ 
    </update>
    
    <update id="updateLoginTimeStamp" parameterType="NlibLoginVO">
        UPDATE UAC_CLTR_DB.MB_INFO A /* 사용자정보 */
        SET   A.IP = #{ip}
            , A.LAST_ACCESS_DD = NOW() 
            , A.MOD_ID = #{mbInfoId}
            , A.MOD_DD = NOW() 
        WHERE MB_INFO_ID = #{mbInfoId}
    </update>
    
    <insert id="insertLoginLog" parameterType="NlibLoginVO">
		INSERT INTO SM_ACC_LOG /* 접속로그 */ 
		(    USER_ID
		    ,USER_NM
		    ,DEPT_SEQ
		    ,DEPT_NM
		    ,ACC_IP
		    ,LOGIN_SUCS_YN
		    ,ACC_DATE
		) VALUES (
		     #{mbInfoId}
		    ,#{name}
		    ,#{joinCouncilCd}
		    ,#{joinCouncilNm}
		    ,#{ip}
		    ,'Y'
		    ,NOW()
		)        
    </insert> 	
 	
 	
    <insert id="agreeProvInfo" parameterType="NlibLoginVO">
        INSERT INTO MB_SITE /* 가입문화원정보 */ 
        (    MB_INFO_ID
            ,JOIN_TYPE
            ,JOIN_AGREE_DD
            ,JOIN_COUNCIL_CD
            ,MASTER_YN
        )
        SELECT 
             #{mbInfoId}
            ,#{joinType}
            ,NOW()
            ,#{joinCouncilCd}
            ,'N'
		FROM UAC_CLTR_DB.TMP_MB_SITE_INFO
		WHERE JOIN_COUNCIL_CD = #{joinCouncilCd}
		AND NOT EXISTS (
		    SELECT MB_SITE_ID
		    FROM MB_SITE S 
		    WHERE S.MB_INFO_ID = #{mbInfoId}
		    AND S.JOIN_COUNCIL_CD = #{joinCouncilCd}
		)
    </insert>   
    
    
 	
</mapper>