<?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="Member">
	
	<select id="Member.getMemberListAll" parameterType="memberSearchVO" resultType="memberVO">
	/*Member.getMemberListAll*/
	SELECT
			  USR_ID as userid
			, USR_NAME as name
			, USR_PLACE1 as place1
			, USR_PLACE2 as place2
			, USR_DUTY as duties
			, T2.CC_CODE_NAME AS place1Str
			, T3.CC_CODE_NAME AS dutyStr
			, T4.CC_CODE_NAME AS place2Str
		FROM H2_USER_INFO T1 
	    	LEFT JOIN  H2_COMMON_CODE T2 ON  T1.USR_PLACE1 = T2.CC_CODE AND T2.CC_GROUP = 'PLA1'
			LEFT JOIN  H2_COMMON_CODE T3 ON  T1.USR_DUTY = T3.CC_CODE AND T3.CC_GROUP = 'DUTY'
			LEFT JOIN  H2_COMMON_CODE T4 ON  T1.USR_PLACE2 = T4.CC_CODE AND T4.CC_GROUP = T1.USR_PLACE1
	WHERE
		USR_NAME LIKE CONCAT('%',#{userid},'%') 
		OR USR_ID LIKE CONCAT('%',#{userid},'%')
	</select>	
	
	<select id="Member.getMemberInfo" parameterType="String" resultType="memberVO">
	/*Member.getMemberInfo*/
	SELECT
		  USR_ID as userid
		, USR_NAME as name
		, USR_PLACE1 as place1
		, USR_PLACE2 as place2
		, USR_DUTY as duties
		, T2.CC_CODE_NAME AS place1Str
		, T3.CC_CODE_NAME AS dutyStr
		, T4.CC_CODE_NAME AS place2Str
		, USR_DUTY as duties
		, T1.USR_LV usegrade
		, T5.ROLE_LEVEL AS rolelevel
	FROM H2_USER_INFO T1 
		LEFT JOIN  H2_COMMON_CODE T2 ON  T1.USR_PLACE1 = T2.CC_CODE AND T2.CC_GROUP = 'PLA1'
		LEFT JOIN  H2_COMMON_CODE T3 ON  T1.USR_DUTY = T3.CC_CODE AND T3.CC_GROUP = 'DUTY'
		LEFT JOIN  H2_COMMON_CODE T4 ON  T1.USR_PLACE2 = T4.CC_CODE AND T4.CC_GROUP = T1.USR_PLACE1
		LEFT JOIN T_SSO_AUTH_CODE T5 ON T5.CODE_ID = T1.USR_LV 
	WHERE
		USR_ID = #{userId} 
	</select>
	
	<select id="Member.getHeadMember" parameterType="memberSearchVO" resultType="memberVO">
	SELECT
			  USR_ID as userid
			, USR_NAME as name
			, USR_PLACE1 as place1
			, USR_PLACE2 as place2
			, USR_DUTY as duties
			, T2.CC_CODE_NAME AS place1Str
			, T3.CC_CODE_NAME AS dutyStr
			, T4.CC_CODE_NAME AS place2Str
		FROM H2_USER_INFO T1 
	    	LEFT JOIN  H2_COMMON_CODE T2 ON  T1.USR_PLACE1 = T2.CC_CODE AND T2.CC_GROUP = 'PLA1'
			LEFT JOIN  H2_COMMON_CODE T3 ON  T1.USR_DUTY = T3.CC_CODE AND T3.CC_GROUP = 'DUTY'
			LEFT JOIN  H2_COMMON_CODE T4 ON  T1.USR_PLACE2 = T4.CC_CODE AND T4.CC_GROUP = T1.USR_PLACE1
	WHERE
		<!--20180517 권한관련 팀장4, 담당관3 수정 -->
		USR_LV = '3'
	LIMIT 1 
	</select>	
	
	<select id="Member.getSectionMember" parameterType="memberSearchVO" resultType="memberVO">
	SELECT
			  USR_ID as userid
			, USR_NAME as name
			, USR_PLACE1 as place1
			, USR_PLACE2 as place2
			, USR_DUTY as duties
			, T2.CC_CODE_NAME AS place1Str
			, T3.CC_CODE_NAME AS dutyStr
			, T4.CC_CODE_NAME AS place2Str
		FROM H2_USER_INFO T1 
	    	LEFT JOIN  H2_COMMON_CODE T2 ON  T1.USR_PLACE1 = T2.CC_CODE AND T2.CC_GROUP = 'PLA1'
			LEFT JOIN  H2_COMMON_CODE T3 ON  T1.USR_DUTY = T3.CC_CODE AND T3.CC_GROUP = 'DUTY'
			LEFT JOIN  H2_COMMON_CODE T4 ON  T1.USR_PLACE2 = T4.CC_CODE AND T4.CC_GROUP = T1.USR_PLACE1
	WHERE
		<!--20180517 권한관련 팀장4, 담당관3 수정 -->
		USR_LV = '4'
	LIMIT 1 
	</select>
	
	<select id="Member.getMemberListUserVO" parameterType="memberSearchVO"  resultType="loginUserVO">
	<![CDATA[
	/* Member.getMemberListUserVO */
		SELECT 
			 T2.CC_CODE_NAME AS place1Str
			, T3.CC_CODE_NAME AS place2Str
			, T4.CC_CODE_NAME AS dutyStr
  			, T1.USR_ID  AS userid
  			, T1.USR_NAME AS name
  			, T1.USR_PWD AS passwd
  			, T1.USR_PWD_CHG loginchk
  			, T1.USR_DUTY duties
  			, T1.USR_PLACE1 AS place1
  			, T1.USR_PLACE2 AS place2
 			, IFNULL(T1.USR_RPT_AUTH, 0) as authCode
			, IFNULL(T1.USR_RPT_AUTH, 0) & 1 AS authSearch
			, IFNULL(T1.USR_RPT_AUTH, 0) & 2 AS authStatistics
			, IFNULL(T1.USR_RPT_AUTH, 0) & 4 AS authWaitList
			, IFNULL(T1.USR_RPT_AUTH, 0) & 8 AS authManage
			, IFNULL(T1.USR_RPT_AUTH, 0) & 16 AS authProxySection
			, IFNULL(T1.USR_RPT_AUTH, 0) & 32 AS authProxyHead
  			, T1.USR_LV USEGRADE
  			, T5.ROLE_LEVEL AS ROLELEVEL
  			, IFNULL(T1.USR_RPT_AUTH, 0) AS RPT_AUTH
		FROM  
  			H2_USER_INFO T1
  			LEFT JOIN H2_COMMON_CODE T2 ON T1.USR_PLACE1 = T2.CC_CODE 
			LEFT JOIN H2_COMMON_CODE T3 ON T1.USR_PLACE2 = T3.CC_CODE AND T1.USR_PLACE1 = T3.CC_GROUP
			LEFT JOIN H2_COMMON_CODE T4 ON T1.USR_DUTY = T4.CC_CODE AND T4.CC_GROUP = 'DUTY'
  			LEFT JOIN T_SSO_AUTH_CODE T5 ON T5.CODE_ID = T1.USR_LV
  		WHERE
	  		T1.USR_LV > 1
	  		]]>
			<if test="place1 != null and place1 != ''  ">
			AND USR_PLACE1 = #{place1}
  			</if> 
  			<if test='searchKeyword != null and searchKeyword != ""  '>
			AND USR_NAME LIKE CONCAT('%', #{searchKeyword} ,'%')
  			</if> 
  		LIMIT  #{firstIndex} , #{recordCountPerPage}
	</select>
	
	<select id="Member.getMemberList" parameterType="memberSearchVO"  resultType="memberVO">
	/* Member.getMemberList */
		SELECT 
			 T2.CC_CODE_NAME AS place1Str
			, T3.CC_CODE_NAME AS place2Str
			, T4.CC_CODE_NAME AS dutyStr
  			, T1.USR_ID  AS userid
  			, T1.USR_NAME AS name
  			, T1.USR_PWD AS passwd
  			, T1.USR_PWD_CHG loginchk
  			, T1.USR_DUTY duties
  			, T1.USR_PLACE1 AS place1
  			, T1.USR_PLACE2 AS place2
  			, T1.USR_LV USEGRADE
  			, T5.ROLE_LEVEL AS ROLELEVEL
		FROM  
  			H2_USER_INFO T1
  			LEFT JOIN H2_COMMON_CODE T2 ON T1.USR_PLACE1 = T2.CC_CODE 
			LEFT JOIN H2_COMMON_CODE T3 ON T1.USR_PLACE2 = T3.CC_CODE AND T1.USR_PLACE1 = T3.CC_GROUP
			LEFT JOIN H2_COMMON_CODE T4 ON T1.USR_DUTY = T4.CC_CODE AND T4.CC_GROUP = 'DUTY'
  			LEFT JOIN T_SSO_AUTH_CODE T5 ON T5.CODE_ID = T1.USR_LV
  		WHERE
  			1 = 1
	  		<![CDATA[
	  		AND T1.USR_LV > 1
	  		]]>
			<if test="place1 != null and place1 != ''  ">
			AND USR_PLACE1 = #{place1}
  			</if> 
  			<if test='searchKeyword != null and searchKeyword != ""  '>
			AND USR_NAME LIKE CONCAT('%', #{searchKeyword} ,'%')
  			</if> 
  		LIMIT  #{firstIndex} , #{recordCountPerPage}
	</select>
	
		<select id="Member.getMemberListForExel" parameterType="memberSearchVO"  resultType="memberVO">
		/* Member.getMemberListForExel */
		SELECT 
			 T2.CC_CODE_NAME AS place1Str
			, T3.CC_CODE_NAME AS place2Str
			, T4.CC_CODE_NAME AS dutyStr
  			, T1.USR_ID  AS userid
  			, T1.USR_NAME AS name
  			, T1.USR_PWD AS passwd
  			, T1.USR_PWD_CHG loginchk
  			, T1.USR_DUTY duties
  			, T1.USR_PLACE1 AS place1
  			, T1.USR_PLACE2 AS place2
  			, T1.USR_LV USEGRADE
  			, T5.ROLE_LEVEL AS ROLELEVEL
		FROM  
  			H2_USER_INFO T1
  			LEFT JOIN H2_COMMON_CODE T2 ON T1.USR_PLACE1 = T2.CC_CODE 
			LEFT JOIN H2_COMMON_CODE T3 ON T1.USR_PLACE2 = T3.CC_CODE AND T1.USR_PLACE1 = T3.CC_GROUP
			LEFT JOIN H2_COMMON_CODE T4 ON T1.USR_DUTY = T4.CC_CODE AND T4.CC_GROUP = 'DUTY'
  			LEFT JOIN T_SSO_AUTH_CODE T5 ON T5.CODE_ID = T1.USR_LV
  		WHERE
  			1 = 1
	  		<![CDATA[
	  		AND T1.USR_LV > 1
	  		]]>
			<if test="place1 != null and place1 != ''  ">
			AND USR_PLACE1 = #{place1}
  			</if>  
	</select>
	
	<select id="Member.getMemberListCnt" parameterType="memberSearchVO"  resultType="int">
		/* Member.getMemberListCnt */
		SELECT 
			COUNT(1) CNT
		FROM  
  			H2_USER_INFO T1
  		WHERE
  			1 = 1
  		<![CDATA[
	  		AND T1.USR_LV > 1
	  		]]>
			<if test="place1 != null and place1 != ''  ">
			AND USR_PLACE1 = #{place1}
  			</if> 
	</select>
	
	<select id="Member.getMemberListAllCnt" parameterType="memberSearchVO"  resultType="int">
		/* Member.getMemberListAllCnt */
		SELECT 
			COUNT(1) CNT
		FROM  
  			H2_USER_INFO T1
  		WHERE
  		<![CDATA[
	  		T1.USR_LV > 1
	  		]]>
	</select>
	
	<update id="Member.updateUsrLv" parameterType="memberVO">
	<![CDATA[
	/* Member.updateUsrLv */
		UPDATE
          H2_USER_INFO
        SET
          USR_LV = #{usegrade}
        WHERE
          USR_ID = #{userid}
	]]>
	</update>

	<update id="Member.updateRtpAuth" parameterType="memberVO">
	<![CDATA[
	/* Member.updateRtpAuth */
		UPDATE
          H2_USER_INFO
        SET
          USR_RPT_AUTH = #{rptAuth}
        WHERE
          USR_ID = #{userid}
	]]>
	</update>
	
	<insert id="Member.insertUserInfo" parameterType="memberVO">
	INSERT INTO H2_USER_INFO(
			USR_ID,
			USR_NAME,
			USR_PWD,
			USR_PWD_CHG,
			USR_DUTY,
			USR_PLACE1,
			USR_PLACE2,
			USR_LV,
			USR_REGDATE,
			USR_CHGDATE
		)values(
			#{userid}, 
			#{name}, 
			#{passwd}, 
			'Y', 
			#{duties},
	 		#{place1}, 
	 		#{place2}, 
	 		'6', 
	 		NOW(), 
	 		NOW()
 		)
	</insert>
	
	<update id="Member.updateUserInfo" parameterType="memberVO">
	UPDATE H2_USER_INFO
	SET
		USR_NAME = #{name}
		<if test=" passwd != null and passwd != '' ">
		, USR_PWD = #{passwd}
		</if>
		, USR_DUTY = #{duties}
		, USR_PLACE1 = #{place1}
		, USR_PLACE2 = #{place2}
		, USR_CHGDATE = NOW()
	WHERE
		USR_ID = #{userid}
	</update>
	
	<select id="Member.selectCheckId" resultType="int" parameterType="String">
	SELECT
		COUNT(1)
	FROM
		H2_USER_INFO
	WHERE
		USR_ID = #{userid}
	</select>
	
</mapper>