<?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="go.kr.project.system.user.mapper.UserMapper">

    <select id="selectUserAcntDuplicateCheck" parameterType="String" resultType="int">
        select count(USER_ID) as cnt
          from tb_user
         where USER_ACNT = #{userAcnt}
    </select>

    <select id="selectUser" parameterType="String" resultType="SystemUserVO">
        select u.USER_ID
             , u.USER_ACNT
             , u.USER_NM
             , u.EMP_NO
             , u.GENDER
             , u.ZIP
             , u.ADDR
             , u.DADDR
             , u.AREA_NO
             , u.EML_ADDR
             , u.ORG_CD
             , g.GROUP_NM as USER_GROUP_ID_NM
             , u.USER_GROUP_ID
             , cd_nstt.CD_NM as STT_CD_NAME
             , u.NSTT_CD
             , u.POS_NM
             , u.CRTFC_DN
             , u.LOCK_YN
             , u.LOCK_CNT
             , u.LOCK_DTTM
             , u.REG_DTTM
             , u.RGTR
             , u.MDFCN_DTTM
             , u.MDFR
             , u.USER_STATUS_CD
             , cd_userStatus.CD_NM as USER_STATUS_NM
             , u.FXNO
             , u.TELNO
             , u.MBL_TELNO
             , u.BRDT
             , u.DEPT_CD
             , cd_dept.CD_NM as DEPT_CD_NM
             , u.USE_YN
             , u.RSDNT_NO
             , cd_org.CD_NM as ORG_CD_NM
             , u.PASSWD_INIT_YN
        from tb_user u
        left join tb_group g on u.USER_GROUP_ID = g.GROUP_ID
        left join tb_cd_detail cd_nstt on u.NSTT_CD = cd_nstt.CD_ID and cd_nstt.CD_GROUP_ID = 'NSTT_CD'
        left join tb_cd_detail cd_userStatus on u.USER_STATUS_CD = cd_userStatus.CD_ID and cd_userStatus.CD_GROUP_ID = 'USER_STATUS_CD'
        left join tb_cd_detail cd_dept on u.DEPT_CD = cd_dept.CD_ID and cd_dept.CD_GROUP_ID = 'DEPT_CD'
        left join tb_cd_detail cd_org on u.ORG_CD = cd_org.CD_ID and cd_org.CD_GROUP_ID = 'ORG_CD'
        where USER_ID = #{selectUserId}
    </select>

    <select id="selectUserList" parameterType="SystemUserSearchVO" resultType="SystemUserVO">
        select u.USER_ID
             , u.USER_ACNT
             , u.USER_NM
             , u.EMP_NO
             , u.GENDER
             , u.ZIP
             , u.ADDR
             , u.DADDR
             , u.AREA_NO
             , u.EML_ADDR
             , u.ORG_CD
             , g.GROUP_NM as USER_GROUP_ID_NM
             , u.USER_GROUP_ID
             , cd_nstt.CD_NM as STT_CD_NAME
             , u.NSTT_CD
             , u.POS_NM
             , u.CRTFC_DN
             , u.LOCK_YN
             , u.LOCK_CNT
             , u.LOCK_DTTM
             , u.REG_DTTM
             , u.RGTR
             , u.MDFCN_DTTM
             , u.MDFR
             , u.USER_STATUS_CD
             , cd_userStatus.CD_NM as USER_STATUS_NM
             , u.FXNO
             , u.TELNO
             , u.MBL_TELNO
             , u.BRDT
             , u.DEPT_CD
             , cd_dept.CD_NM as DEPT_CD_NM
             , u.USE_YN
             , u.RSDNT_NO
             , cd_org.CD_NM as ORG_CD_NM
             , u.PASSWD_INIT_YN
        from tb_user u
        left join tb_group g on u.USER_GROUP_ID = g.GROUP_ID
        left join tb_cd_detail cd_nstt on u.NSTT_CD = cd_nstt.CD_ID and cd_nstt.CD_GROUP_ID = 'NSTT_CD'
        left join tb_cd_detail cd_userStatus on u.USER_STATUS_CD = cd_userStatus.CD_ID and cd_userStatus.CD_GROUP_ID = 'USER_STATUS_CD'
        left join tb_cd_detail cd_dept on u.DEPT_CD = cd_dept.CD_ID and cd_dept.CD_GROUP_ID = 'DEPT_CD'
        left join tb_cd_detail cd_org on u.ORG_CD = cd_org.CD_ID and cd_org.CD_GROUP_ID = 'ORG_CD'
        where 1 = 1
        <if test='searchUseYn != null and searchUseYn != ""'>
            and u.USE_YN = #{searchUseYn}
        </if>
        <if test='searchKeyword != null and searchKeyword != ""'>
            and (
            USER_ID like CONCAT('%', #{searchKeyword}, '%')
            or USER_NM like CONCAT('%', #{searchKeyword}, '%')
            or USER_ACNT like CONCAT('%', #{searchKeyword}, '%')
            )
        </if>
        <if test="sortAscending != null and sortColumn != null and sortColumn != ''">
            <choose>
                <when test="sortColumn == 'userId'">order by USER_ID</when>
                <when test="sortColumn == 'userAcnt'">order by USER_ACNT</when>
                <when test="sortColumn == 'userNm'">order by USER_NM</when>
                <otherwise>
                    order by USER_ID
                </otherwise>
            </choose>
            <choose>
                <when test="sortAscending == true">ASC</when>
                <when test="sortAscending == false">DESC</when>
                <otherwise>
                    ASC
                </otherwise>
            </choose>
        </if>
        <if test='pagingYn != null and pagingYn == "Y"'>
            LIMIT #{startIndex}, #{perPage}
        </if>
    </select>

    <!-- 사용자 목록 총 개수 조회 -->
    <select id="selectUserListTotalCount" parameterType="SystemUserSearchVO" resultType="int">
        select COUNT(*)
        from tb_user
        where 1 = 1
        <if test='searchUseYn != null and searchUseYn != ""'>
            and USE_YN = #{searchUseYn}
        </if>
        <if test='searchKeyword != null and searchKeyword != ""'>
            and (
                    USER_ID like CONCAT('%', #{searchKeyword}, '%')
                    or USER_NM like CONCAT('%', #{searchKeyword}, '%')
                    or USER_ACNT like CONCAT('%', #{searchKeyword}, '%')
                )
        </if>
    </select>

    <!-- 사용자 ID 생성 -->
    <select id="generateUserId" resultType="String">
        SELECT CONCAT('USR', LPAD(NEXTVAL(seq_user_id), 8, '0')) AS userId
    </select>

    <!-- 사용자 등록 -->
    <insert id="insertUser" parameterType="SystemUserVO">
        INSERT INTO tb_user (
            USER_ID,
            USER_ACNT,
            USER_NM,
            PASSWD,
            PASSWD_HINT,
            PASSWD_NSR,
            PASSWD_INIT_YN,
            EMP_NO,
            GENDER,
            ZIP,
            ADDR,
            DADDR,
            AREA_NO,
            EML_ADDR,
            ORG_CD,
            USER_GROUP_ID,
            NSTT_CD,
            POS_NM,
            CRTFC_DN,
            LOCK_YN,
            LOCK_CNT,
            LOCK_DTTM,
            REG_DTTM,
            RGTR,
            USER_STATUS_CD,
            FXNO,
            TELNO,
            MBL_TELNO,
            BRDT,
            DEPT_CD,
            USE_YN,
            RSDNT_NO
        ) VALUES (
            #{userId},
            #{userAcnt},
            #{userNm},
            #{passwd},
            #{passwdHint},
            #{passwdNsr},
            'Y',
            #{empNo},
            #{gender},
            #{zip},
            #{addr},
            #{daddr},
            #{areaNo},
            #{emlAddr},
            #{orgCd},
            #{userGroupId},
            #{nsttCd},
            #{posNm},
            #{crtfcDn},
            #{lockYn},
            #{lockCnt},
            #{lockDttm},
            NOW(),
            #{rgtr},
            #{userStatusCd},
            #{fxno},
            #{telno},
            #{mblTelno},
            #{brdt},
            #{deptCd},
            #{useYn},
            #{rsdntNo}
        )
    </insert>

    <!-- 사용자 수정 -->
    <update id="updateUser" parameterType="SystemUserVO">
        UPDATE tb_user
        SET
            <!--USER_ACNT = #{userAcnt},-->
            USER_NM = #{userNm},
            <if test='passwdHint != null and passwdHint != ""'>
            PASSWD_HINT = #{passwdHint},
            </if>
            <if test='passwdNsr != null and passwdNsr != ""'>
            PASSWD_NSR = #{passwdNsr},
            </if>
            <if test='empNo != null and empNo != ""'>
            EMP_NO = #{empNo},
            </if>
            <if test='gender != null and gender != ""'>
            GENDER = #{gender},
            </if>
            <if test='zip != null and zip != ""'>
            ZIP = #{zip},
            </if>
            <if test='addr != null and addr != ""'>
            ADDR = #{addr},
            </if>
            <if test='daddr != null and daddr != ""'>
            DADDR = #{daddr},
            </if>
            <if test='areaNo != null and areaNo != ""'>
            AREA_NO = #{areaNo},
            </if>
            <if test='emlAddr != null and emlAddr != ""'>
            EML_ADDR = #{emlAddr},
            </if>
            <if test='orgCd != null and orgCd != ""'>
            ORG_CD = #{orgCd},
            </if>
            <if test='userGroupId != null and userGroupId != ""'>
            USER_GROUP_ID = #{userGroupId},
            </if>
            <if test='nsttCd != null and nsttCd != ""'>
            NSTT_CD = #{nsttCd},
            </if>
            <if test='posNm != null and posNm != ""'>
            POS_NM = #{posNm},
            </if>
            <if test='crtfcDn != null and crtfcDn != ""'>
            CRTFC_DN = #{crtfcDn},
            </if>
            <if test='lockYn != null and lockYn != ""'>
            LOCK_YN = #{lockYn},
            </if>
            <if test='lockCnt != null and lockCnt != ""'>
            LOCK_CNT = #{lockCnt},
            </if>
            <if test='lockDttm != null and lockDttm != ""'>
            LOCK_DTTM = #{lockDttm},
            </if>
            <if test='userStatusCd != null and userStatusCd != ""'>
            USER_STATUS_CD = #{userStatusCd},
            </if>
            <if test='fxno != null and fxno != ""'>
            FXNO = #{fxno},
            </if>
            <if test='telno != null and telno != ""'>
            TELNO = #{telno},
            </if>
            <if test='mblTelno != null and mblTelno != ""'>
            MBL_TELNO = #{mblTelno},
            </if>
            <if test='brdt != null and brdt != ""'>
            BRDT = #{brdt},
            </if>
            <if test='deptCd != null and deptCd != ""'>
            DEPT_CD = #{deptCd},
            </if>
            <if test='useYn != null and useYn != ""'>
            USE_YN = #{useYn},
            </if>
            <if test='rsdntNo != null and rsdntNo != ""'>
            RSDNT_NO = #{rsdntNo},
            </if>
            <if test='passwdInitYn != null and passwdInitYn != ""'>
            PASSWD_INIT_YN = #{passwdInitYn},
            </if>
            MDFCN_DTTM = NOW(),
            MDFR = #{mdfr}
        WHERE USER_ID = #{userId}
    </update>

    <!-- 그룹 목록 조회 -->
    <select id="selectGroupList" parameterType="GroupSearchVO" resultType="GroupVO">
        select GROUP_ID
        , GROUP_NM
        , GROUP_DC
        , SORT_ORDR
        , USE_YN
        , REG_DTTM
        , RGTR
        , MDFCN_DTTM
        , MDFR
        from tb_group
        where 1 = 1
        <if test='searchUseYn != null and searchUseYn != ""'>
            and USE_YN = #{searchUseYn}
        </if>
        <if test='excludeGroupId != null and excludeGroupId != ""'>
            and GROUP_ID != #{excludeGroupId}
        </if>
        order by SORT_ORDR asc
    </select>

    <!-- 비밀번호 변경 -->
    <update id="updatePassword" parameterType="SystemUserVO">
        UPDATE TB_USER
        SET
        PASSWD = #{passwd},
        PASSWD_INIT_YN = #{passwdInitYn},
        MDFCN_DTTM = NOW(),
        MDFR = #{userId}
        WHERE USER_ID = #{userId}
    </update>

    <!-- 비밀번호 확인 -->
    <select id="checkPassword" parameterType="SystemUserVO" resultType="int">
        SELECT COUNT(*)
        FROM TB_USER
        WHERE USER_ID = #{userId}
        AND PASSWD = #{passwd}
    </select>

    <!-- 사용자 잠금 해제 -->
    <update id="unlockUser" parameterType="String">
        UPDATE TB_USER
        SET
            LOCK_YN = 'N',
            LOCK_CNT = 0,
            MDFCN_DTTM = NOW()
        WHERE USER_ID = #{userId}
    </update>

</mapper>
