<?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="kr.co.uplus.ez.api.sysMgt.SysMgtMapper">

	<select id="selectAdmUserTotalCnt"
		parameterType="kr.co.uplus.ez.api.sysMgt.dto.AdminListReqDto"
		resultType="int">
		/* sysMgt-mapper.xml(selectAdmUserTotalCnt) */
		SELECT
		COUNT(*)
		FROM
		hubez_admin.EZ_ADM_USER T1
		LEFT JOIN hubez_imdb.EZ_IM_USER T2 ON T1.OPRTR_ID = T2.LOGIN_ID
		WHERE 1 = 1
		<include refid="adminListCondition"></include>
	</select>

	<select id="selectAdminList"
		parameterType="kr.co.uplus.ez.api.sysMgt.dto.AdminListReqDto"
		resultType="kr.co.uplus.ez.api.sysMgt.dto.AdminInfo">
		/* sysMgt-mapper.xml(selectAdminList) */
		SELECT
		@ROWNUM := @ROWNUM + 1 AS NO,
		A.*
		FROM
		(
		SELECT
		(SELECT AUT_NM FROM hubez_admin.EZ_ADM_AUT WHERE AUT_CD = T1.AUT_CD) AS
		AUTH
		, T1.OPRTR_NM AS NAME
		, T1.OPRTR_ID AS ADMIN_ID
		,(SELECT DTL_CD_NM FROM hubez_common.EZ_CD_DTL WHERE GRP_CD =
		'ADM_STTUS_CD' AND DTL_CD = T1.STTUS_CD) AS ADMIN_STAT
		, DATE_FORMAT(T1.REG_DT, '%Y-%m-%d') AS REG_DT
		FROM
		hubez_admin.EZ_ADM_USER T1
		LEFT JOIN hubez_imdb.EZ_IM_USER T2 ON T1.OPRTR_ID = T2.LOGIN_ID
		WHERE 1 = 1
		<include refid="adminListCondition"></include>
		ORDER BY T1.REG_DT DESC
		LIMIT #{page}, #{pagePerRows}) A,
		( SELECT @ROWNUM := #{page} ) AS R
	</select>

	<select id="selectSearchMadangId"
		parameterType="kr.co.uplus.ez.api.sysMgt.dto.SearchMadangIdReqDto"
		resultType="kr.co.uplus.ez.api.sysMgt.dto.SearchMadangId">
		/* sysMgt-mapper.xml(selectSearchMadangId) */
		SELECT
		LOGIN_ID AS madangId
		,NAME AS name
		,MOBILE AS mdn
		,EMAIL AS email
		,USER_CODE AS code
		FROM
		hubez_imdb.EZ_IM_USER
		WHERE LOGIN_ID = #{madangId}
	</select>

	<select id="selectAdminDetail"
		parameterType="kr.co.uplus.ez.api.sysMgt.dto.AdminDetailReqDto"
		resultType="kr.co.uplus.ez.api.sysMgt.dto.AdminDetail">
		/* sysMgt-mapper.xml(selectAdminDetail) */
		SELECT
		OPRTR_ID AS adminId
		,OPRTR_NM AS name
		,HP_NO AS mdn
		,EMAIL AS email
		,AUT_CD AS auth
		,STTUS_CD AS stat
		/* 유치채널 코드 컬럼 확인 필요 */
		FROM
		hubez_admin.EZ_ADM_USER
		WHERE OPRTR_ID = #{adminId}
	</select>

	<sql id="adminListCondition">
		<if test="searchType1 != null and searchType1 != ''">
			AND T1.AUT_CD = #{searchType1}
		</if>
		<if test="searchType2 != null and searchType2 != ''">
			AND T1.STTUS_CD = #{searchType2}
		</if>
		<if test="searchText1 != null and searchText1 != ''">
			AND T1.OPRTR_ID LIKE CONCAT('%', #{searchText1}, '%')
		</if>
		<if test="searchText2 != null and searchText2 != ''">
			AND T1.OPRTR_NM LIKE CONCAT('%', #{searchText2}, '%')
		</if>
	</sql>

	<insert id="insertAdmUser"
		parameterType="kr.co.uplus.ez.api.sysMgt.dto.InsertAdminReqDto">
		/* sysMgt-mapper.xml(insertAdmUser) */
		INSERT INTO
		hubez_admin.EZ_ADM_USER(
		OPRTR_ID
		, OPRTR_NM
		, STTUS_CD
		, PWD
		, EMAIL
		, HP_NO
		, AUT_CD
		, PWD_CHG_DT
		, AUTHCHR_FAIL_CNT
		, LOGIN_FAIL_CNT
		, REG_ID
		, REG_DT
		, CHG_ID
		, CHG_DT)
		VALUES (
		#{madangId}
		, #{name}
		, #{stat}
		, #{adminPw}
		, #{email}
		, #{mdn}
		, #{auth}
		, NOW()
		, 0
		, 0
		, #{madangId}
		, NOW()
		, #{madangId}
		, NOW()
		)
	</insert>

	<select id="selectAuthList"
		resultType="kr.co.uplus.ez.api.sysMgt.dto.AuthInfo">
		/* sysMgt-mapper.xml(selectAuthList) */
		SELECT
		@ROWNUM := @ROWNUM + 1 AS no,
		A.*
		FROM
		(
		SELECT
			AUT_CD AS authCd
			,AUT_NM AS authNm
			,CASE
			 WHEN USE_YN='Y' THEN '사용'
			 WHEN USE_YN='N' THEN '정지'
			 END AS authStat
			,DATE_FORMAT(REG_DT, '%Y-%m-%d') AS regDt
		FROM hubez_admin.EZ_ADM_AUT
		ORDER BY REG_DT DESC
		) A, ( SELECT @ROWNUM := 0 ) AS R
	</select>

	<insert id="insertAuth" parameterType="hashMap">
		/* sysMgt-mapper.xml(insertAuth) */
		INSERT INTO
		hubez_admin.EZ_ADM_AUT(
			 AUT_CD
			,AUT_NM
			<if test="stat != null and stat != ''">
			,USE_YN
			</if>
			,AUT_DESC
			,REG_ID
			,REG_DT
			,CHG_ID
			,CHG_DT
			)
		VALUES (
			 #{authCd}
			,#{authNm}
			<if test="stat != null and stat != ''">
				<choose>
					<when test="stat eq '01'">,'Y'</when>
					<otherwise>,'N'</otherwise>
				</choose>
			</if>
			,#{authDesc}
			,#{regId}
			,NOW()
			,#{regId}
			,NOW()
			)
	</insert>

	<insert id="insertAuthMenu" parameterType="hashMap">
		/* sysMgt-mapper.xml(insertAuthMenu) */
		INSERT INTO
		hubez_admin.EZ_ADM_AUTMENU(
			 AUT_CD
			,MENU_NO
			,REG_ID
			,REG_DT
			)
		VALUES (
			 #{authCd}
			,#{menuNo}
			,#{regId}
			,NOW()
			)
	</insert>

	<update id="updateAdmUser"
		parameterType="kr.co.uplus.ez.api.sysMgt.dto.UpdateAdminReqDto">
		/* sysMgt-mapper.xml(updateAdmUser) */
		UPDATE
		hubez_admin.EZ_ADM_USER
		SET
		CHG_ID = #{adminId}
		, CHG_DT = NOW()
		<if test="adminPw != null and adminPw != ''">
			, PWD = #{adminPw}
			, PWD_CHG_DT = NOW()
		</if>
		<if test="auth != null and auth != ''">
			, AUT_CD = #{auth}
		</if>
		<if test="stat != null and stat != ''">
			, STTUS_CD = #{stat}
		</if>
		WHERE OPRTR_ID = #{adminId}
	</update>

	<update id="updateAuth" parameterType="hashMap">
		/* sysMgt-mapper.xml(updateAuth) */
		UPDATE hubez_admin.EZ_ADM_AUT
		SET
			CHG_ID = #{regId}
			,CHG_DT = NOW()
			<if test="authNm != null and authNm != ''">
				,AUT_NM = #{authNm}
			</if>
			<if test="stat != null and stat != ''">
				<choose>
					<when test="stat eq '01'">
					,USE_YN = 'Y'
					</when>
					<otherwise>
					,USE_YN = 'N'
					</otherwise>
				</choose>
			</if>
			<if test="authDesc != null and authDesc != ''">
			,AUT_DESC = #{authDesc}
			</if>
		WHERE
			AUT_CD = #{authCd}
	</update>

	<insert id="insertMdAcc"
		parameterType="kr.co.uplus.ez.api.sysMgt.dto.InsertAdminReqDto">
		/* sysMgt-mapper.xml(insertMdAcc) */
		INSERT INTO
		hubez_imdb.EZ_IM_USER(
		LOGIN_ID
		, NAME
		, USER_CODE
		, STATUS
		, MOBILE
		, EMAIL
		, REG_ID
		, REG_DT
		, CHG_ID
		, CHG_DT)
		VALUES (
		#{madangId}
		, #{name}
		, #{stat}
		, #{adminPw}
		, #{email}
		, #{mdn}
		, #{auth}
		, NOW()
		, #{madangId}
		, NOW()
		, #{madangId}
		, NOW()
		)
	</insert>

	<update id="updateMdAcc"
		parameterType="kr.co.uplus.ez.api.sysMgt.dto.UpdateAdminReqDto">
		/* sysMgt-mapper.xml(updateMdAcc) */
		UPDATE hubez_imdb.EZ_IM_USER
		SET
		CHG_ID
		, CHG_DT = NOW()
		<if test="adminPw != null and adminPw != ''">
			, PWD = #{adminPw}
			, PWD_CHG_DT = NOW()
		</if>
		<if test="stat != null and stat != ''">
			, STTUS_CD = #{stat}
		</if>
		WHERE LOGIN_ID = #{adminId}
	</update>

	<delete id="deleteAdminList" parameterType="List">
		/* sysMgt-mapper.xml(deleteAdminList) */
		DELETE FROM
		hubez_admin.EZ_ADM_USER
		<where>
			OPRTR_ID IN
			<foreach collection="list" item="item" index="i" open="("
				separator="," close=")">
				#{item.adminId}
			</foreach>
		</where>
	</delete>

	<delete id="deleteAuth" parameterType="hashMap">
		/* sysMgt-mapper.xml(deleteAuth) */
		DELETE FROM
		hubez_admin.EZ_ADM_AUT
		WHERE AUT_CD = #{authCd}
	</delete>

	<delete id="deleteAuthMenu" parameterType="hashMap">
		/* sysMgt-mapper.xml(deleteAuthMenu) */
		DELETE FROM
		hubez_admin.EZ_ADM_AUTMENU
		WHERE AUT_CD = #{authCd}
	</delete>

	<select id="selectAuthDetail"
		parameterType="kr.co.uplus.ez.api.sysMgt.dto.AuthDetailReqDto"
		resultType="kr.co.uplus.ez.api.sysMgt.dto.AuthDetail">
		/* sysMgt-mapper.xml(selectAuthDetail) */
		SELECT
			AUT_CD AS authCd
			,AUT_NM AS authNm
			,IFNULL(AUT_DESC,'') AS authDesc
			,CASE
			 WHEN USE_YN='Y' THEN '01'
			 WHEN USE_YN='N' THEN '02'
			 END AS authStat
		FROM hubez_admin.EZ_ADM_AUT
		WHERE AUT_CD = #{authCd}
	</select>

	<select id="selectAuthMemuList"
		parameterType="kr.co.uplus.ez.api.sysMgt.dto.AuthDetailReqDto"
		resultType="kr.co.uplus.ez.api.sysMgt.dto.AuthMenu">
		/* sysMgt-mapper.xml(selectAuthMemuList) */
		SELECT
		T2.MENU_NO AS menuNo
		,T3.PRNTS_MENU_NO AS upperMenuNo
		,T3.MENU_NM AS name
		,T3.MENU_ODRG AS menuOrder
		,T3.USE_YN AS useYn
		,T3.MENU_LVL AS menuLv
		FROM
		hubez_admin.EZ_ADM_AUTMENU AS T2
		INNER JOIN hubez_admin.EZ_ADM_MENU AS T3
		ON T2.MENU_NO = T3.MENU_NO
		WHERE T2.AUT_CD = #{authCd}
	</select>

	<select id="checkAdmin" parameterType="hashMap" resultType="int">
		/* sysMgt-mapper.xml(checkAdminId) */
		SELECT
			COUNT(*)
		FROM
			hubez_admin.EZ_ADM_USER eau
		WHERE 1=1
		<if test="searchType != null and searchType != ''">
			<if test="searchType == '01' ">
				AND OPRTR_ID = #{adminId}
			</if>
			<if test="searchType == '02' ">
				AND AUT_CD = #{authCd}
			</if>
		</if>


	</select>
	
	
	<select id="batchListSelect" resultType="kr.co.uplus.ez.api.sysMgt.dto.BatchList">
		/* sysMgt-mapper.xml(batchListSelect) 배치 리스트 조회*/
		SELECT
		      	ebi.BATCH_ID AS BATCH_ID,
		      	ebi.BATCH_NM AS BATCH_NM,
				ebl.REG_DT AS REG_DT,
				ebi.BATCH_DESC AS BATCH_DESC,
			CASE 
				WHEN ebl.STTUS_CD = '01' THEN  '시작'
				WHEN ebl.STTUS_CD = '02' THEN  '성공'
				WHEN ebl.STTUS_CD = '03' THEN  '실패'
			ELSE '실패'
			END AS STTUS_CD,
			ebl.ERR_MSG AS ERR_MSG
		FROM
			hubez_admin.EZ_BTEXE_LOG ebl
		INNER JOIN 
			hubez_admin.EZ_BATCH_INFO ebi ON ebl.BATCH_ID = ebi.BATCH_ID
		WHERE 
			(ebl.BATCH_ID ,ebl.REG_DT) 
			IN (
				SELECT 
						BATCH_ID, 
						max(REG_DT) AS REG_DT 
				FROM 	hubez_admin.EZ_BTEXE_LOG ebl2 
				GROUP 	BY BATCH_ID
				)
		GROUP BY BATCH_ID		
		ORDER BY STTUS_CD DESC, BATCH_DESC DESC, BATCH_ID ASC
	</select>
	
	
	<select id="batchDetailSelect" parameterType="kr.co.uplus.ez.api.sysMgt.dto.BatchDetailReqDto" resultType="kr.co.uplus.ez.api.sysMgt.dto.BatchDetail">
		/* sysMgt-mapper.xml(batchDetailSelect) 배치 상세 조회*/
		SELECT 
			ebl.BATCH_ID AS batchId, 
			ebl.ST_DT AS stDt, 
			ebl.FNS_DT AS fnsDt, 
			CASE 
				WHEN ebl.STTUS_CD = '01' THEN  '시작'
				WHEN ebl.STTUS_CD = '02' THEN  '성공'
				WHEN ebl.STTUS_CD = '03' THEN  '실패'
			ELSE ebl.STTUS_CD
			END STTUS_CD,
			ebl.ERR_MSG AS errMsg
		FROM 
			hubez_admin.EZ_BTEXE_LOG ebl 
		WHERE 
			ebl.BATCH_ID =#{batchId}
		<if test= "startDt != null and endDt !=null">
		AND DATE_FORMAT(ebl.ST_DT,'%Y-%m-%d') BETWEEN DATE_FORMAT(#{startDt},'%Y-%m-%d') AND DATE_FORMAT(#{endDt},'%Y-%m-%d')
		</if>
		<if test ="sttusCd != null and sttusCd !=''">
		AND ebl.STTUS_CD = #{sttusCd}
		</if>
		ORDER BY 
			ebl.REG_DT desc
		LIMIT #{page}, #{pagePerRows}
	</select>
	
	
	<select id="batchDetailCnt" parameterType="kr.co.uplus.ez.api.sysMgt.dto.BatchDetailReqDto" resultType="int">
		/* sysMgt-mapper.xml(batchDetailCnt) 배치 상세 조회 카운트*/
		SELECT 
			COUNT(1)
		FROM 
			hubez_admin.EZ_BTEXE_LOG ebl 
		WHERE 
			ebl.BATCH_ID =#{batchId}
		<if test= "startDt != null and endDt !=null">
		AND DATE_FORMAT(ebl.ST_DT,'%Y-%m-%d') BETWEEN DATE_FORMAT(#{startDt},'%Y-%m-%d') AND DATE_FORMAT(#{endDt},'%Y-%m-%d')
		</if>
		<if test ="sttusCd != null and sttusCd !=''">
		AND ebl.STTUS_CD = #{sttusCd}
		</if>
	</select>
	
	<select id="notiListSelect" parameterType="kr.co.uplus.ez.api.sysMgt.dto.NotiListReqDto" resultType="kr.co.uplus.ez.api.sysMgt.dto.NotiList">
		/* sysMgt-mapper.xml(notiListSelect) 알림 이력 조회*/
		SELECT
				enh.SND_YMD,
				esu.USER_ID,
				(SELECT ecc.DTL_CD_NM FROM hubez_common.EZ_CD_DTL ecc WHERE ecc.GRP_CD = 'NOTI_DIV_CD' AND ecc.DTL_CD = enh.NOTI_DIV) as NOTI_DIV,
				enh.NOTI_SEQ,
				enh.NOTI_MSG,
				enh.WEB_REQ_ID,
				enh.REG_DT,
				'상세내역' AS msgTitle
		FROM
				hubez_common.EZ_NOTI_HIST enh
		INNER JOIN
				hubez_common.EZ_SVC_USER esu ON enh.USER_SEQ = esu.USER_SEQ
		WHERE
				1=1
		<if test = "startDt != null and endDt != null">
			AND DATE_FORMAT(enh.SND_YMD,'%Y-%m-%d') BETWEEN DATE_FORMAT(#{startDt},'%Y-%m-%d') AND DATE_FORMAT(#{endDt},'%Y-%m-%d')
		</if>
		<if test = "srchGbn != null and srchGbn != ''">
			<if test= "srchGbn == '01'">
			AND esu.USER_ID LIKE CONCAT ('%',#{search},'%')
			</if>
			<if test = "srchGbn == '02'">
			AND enh.WEB_REQ_ID LIKE CONCAT ('%',#{search},'%')
			</if>
		</if>
		<if test = "srchGbn2 != null and srchGbn2 != ''">
			<if test= "srchGbn2 == '01'">
				AND enh.NOTI_DIV = '01'		
			</if>
			<if test= "srchGbn2 == '02'">
				AND enh.NOTI_DIV = '02'		
			</if>
		</if>
		ORDER BY 
				enh.REG_DT desc, enh.NOTI_DIV
		LIMIT #{page}, #{pagePerRows}
	</select>
	
	
	<select id="notiListSelectCnt" parameterType="kr.co.uplus.ez.api.sysMgt.dto.NotiListReqDto" resultType="int">
		/* sysMgt-mapper.xml(notiListSelect) 알림 이력 조회 카운트*/
		SELECT
				COUNT(1)
		FROM
				hubez_common.EZ_NOTI_HIST enh
		INNER JOIN
				hubez_common.EZ_SVC_USER esu ON enh.USER_SEQ = esu.USER_SEQ
		WHERE
				1=1
		<if test = "startDt != null and endDt != null">
			AND DATE_FORMAT(enh.SND_YMD,'%Y-%m-%d') BETWEEN DATE_FORMAT(#{startDt},'%Y-%m-%d') AND DATE_FORMAT(#{endDt},'%Y-%m-%d')
		</if>
		<if test = "srchGbn != null and srchGbn != ''">
			<if test= "srchGbn == '01'">
			AND esu.USER_ID LIKE CONCAT ('%',#{search},'%')
			</if>
			<if test = "srchGbn == '02'">
			AND enh.WEB_REQ_ID LIKE CONCAT ('%',#{search},'%')
			</if>
		</if>
		<if test = "srchGbn2 != null and srchGbn2 != ''">
			<if test= "srchGbn2 == '01'">
				AND enh.NOTI_DIV = '01'		
			</if>
			<if test= "srchGbn2 == '02'">
				AND enh.NOTI_DIV = '02'		
			</if>
		</if>
	</select>
</mapper>