<?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="history">

	<resultMap id="menuUseHistoryResult" type="menuUseHistory">
		<id property="useFromDate" column="USE_FROM_DATE" />
		<id property="userId" column="USER_ID" />
		<id property="reqType" column="REQ_TYPE" />
		<result property="hitCount" column="HIT_COUNT" />
		<result property="menuId" column="MENU_ID" />
		<result property="menuFullPath" column="MENU_FULL_PATH" />
		<result property="apiFullPath" column="API_FULL_PATH" />
		<result property="userName" column="USER_NAME" />
		<result property="compName" column="COMP_NAME" />
		<result property="deptName" column="DEPT_NAME" />
		<result property="grdName" column="GRD_NAME" />
	</resultMap>

    <resultMap id="sysUseLogResult" type="sysUseLog">
		<id property="id" column="ID" />
		<result property="reqType" column="REQ_TYPE" />
		<result property="description" column="DESCRIPTION" />
		<result property="useFromDate" column="USE_FROM_DATE" />
		<result property="hitCount" column="HIT_COUNT" />
		<association property="user" javaType="user">
			<result property="compName" column="COMP_NAME" />
			<result property="deptName" column="DEPT_NAME" />
			<result property="userName" column="USER_NAME" />
			<result property="grdName" column="GRD_NAME" />
		</association>
	</resultMap>

	<resultMap id="loginOutResult" type="loginOut">
		<result property="userId" column="USER_ID" />
		<result property="loginTime" column="LOGIN_TIME" />
		<result property="logoutTime" column="LOGOUT_TIME" />
		<result property="useIp" column="USE_IP" />
		<result property="reqType" column="REQ_TYPE" />
		<result property="userName" column="USER_NAME" />
		<result property="compName" column="COMP_NAME" />
		<result property="deptName" column="DEPT_NAME" />
		<result property="grdName" column="GRD_NAME" />
	</resultMap>

	<sql id="tableMenuUseHistory">TN_CF_MENU_USE_HISTORY</sql>
    <sql id="tableSysUseLog">TN_CF_SYS_USE_LOG</sql>
	
	<sql id="columnMenuUseHistory">
	USE_FROM_DATE, USER_ID, REQ_TYPE,
	HIT_COUNT, MENU_ID, MENU_FULL_PATH,
	USER_NAME, COMP_NAME, DEPT_NAME, GRD_NAME
	</sql>
	
	<sql id="conditionMenuUseHistory">
		<if test="@com.samsung.SdlComparator@isNotEmpty(reqType)">
		AND REQ_TYPE = #{reqType}
		</if>
		<if test="@com.samsung.SdlComparator@isNotEmpty(userName)">
		AND USER_NAME LIKE CONCAT('%', #{userName}, '%')
		</if>
	</sql>

    <sql id="conditionSysUseLog">
		<if test="@com.samsung.SdlComparator@isNotEmpty(reqType)">
		AND SUL.REQ_TYPE = #{reqType}
		</if>
		<if test="@com.samsung.SdlComparator@isNotEmpty(userName)">
		AND U.USER_NAME LIKE CONCAT('%', #{userName}, '%')
		</if>
	</sql>

	<sql id="conditionLoginOut">
		<trim prefix="WHERE" prefixOverrides="AND|OR">
			<if test="@com.samsung.SdlComparator@isNotEmpty(searchKeyword)">
				<if test="searchCondition.equals('userId')">
					AND LOWER(U.USER_ID) LIKE CONCAT('%', LOWER(#{searchKeyword}), '%')
				</if>
				<if test="searchCondition.equals('userName')">
					AND LOWER(U.USER_NAME) LIKE CONCAT('%', LOWER(#{searchKeyword}), '%')
				</if>
				<if test="searchCondition.equals('deptName')">
					AND LOWER(U.DEPT_NAME) LIKE CONCAT('%', LOWER(#{searchKeyword}), '%')
				</if>
				<if test="searchCondition.equals('useIp')">
					AND LOWER(LO.USE_IP) like CONCAT('%', LOWER(#{searchKeyword}), '%')
				</if>
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(startDate) and @com.samsung.SdlComparator@isNotEmpty(endDate)">
				AND DATE_FORMAT(LO.LOGIN_TIME, '%Y%m%d') BETWEEN #{startDate} AND #{endDate}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(reqType)">
				AND LO.REQ_TYPE = #{reqType}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(userId)">
				AND LO.USER_ID = #{userId}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(useIp)">
				AND LO.USE_IP = #{useIp}
			</if>
		</trim>
	</sql>
	
	<delete id="deleteMenuUseHistory" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableMenuUseHistory" />
		WHERE  USE_FROM_DATE = #{targetDate}
	</delete>
	
	<insert id="insertMenuUseHistory" parameterType="java.util.HashMap">
		INSERT INTO <include refid="tableMenuUseHistory" />
		SELECT SUL.USE_FROM_DATE, SUL.USER_ID, SUL.REQ_TYPE,
		       SUL.HIT_COUNT, SUL.DESCRIPTION MENU_FULL_PATH,
		       U.USER_NAME, U.COMP_NAME, U.DEPT_NAME, U.GRD_NAME, SUL.MENU_ID
		FROM   (SELECT USE_FROM_DATE, USER_ID, REQ_TYPE,
		               MENU_ID, DESCRIPTION, COUNT(MENU_ID) HIT_COUNT
		        FROM   <include refid="accessLog.tableSysUseLog" />
		        WHERE  LOG_FLAG = '6'
		        AND    MENU_ID IS NOT NULL
		        AND    REQ_TYPE IS NOT NULL
		        AND    USE_FROM_DATE = #{targetDate}
		        GROUP BY USE_FROM_DATE,
		                 USER_ID,
		                 REQ_TYPE,
		                 MENU_ID,
		                 DESCRIPTION) SUL,
		       <include refid="user.tableUser" /> U
		WHERE  SUL.USER_ID = U.USER_ID
	</insert>
	
	<select id="selectMenuUseHistoryPagingListByDate" parameterType="java.util.HashMap" resultMap="menuUseHistoryResult">
		SELECT T.*
		FROM   (SELECT ROW_NUMBER() OVER(ORDER BY USE_FROM_DATE DESC, USER_NAME, MENU_FULL_PATH ASC) RNUM,
		               <include refid="columnMenuUseHistory" />
		        FROM   <include refid="tableMenuUseHistory" />
		        WHERE  USE_FROM_DATE <![CDATA[ >= ]]> #{startDate}
		        AND    USE_FROM_DATE <![CDATA[ <= ]]> #{endDate}
		        <include refid="conditionMenuUseHistory" />) T
		WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
	</select>

	<select id="countMenuUseHistoryListByDate" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(USER_ID) TOTAL
		FROM   <include refid="tableMenuUseHistory" />
		WHERE  USE_FROM_DATE <![CDATA[ >= ]]> #{startDate}
		AND    USE_FROM_DATE <![CDATA[ <= ]]> #{endDate}
		<include refid="conditionMenuUseHistory" />
	</select>

    <select id="selectMenuUseHistoryListByDate" parameterType="java.util.HashMap" resultMap="menuUseHistoryResult">
		SELECT <include refid="columnMenuUseHistory" />
        FROM   <include refid="tableMenuUseHistory" />
        WHERE  USE_FROM_DATE <![CDATA[ >= ]]> REPLACE(#{startDate}, '-', '')
        AND    USE_FROM_DATE <![CDATA[ <= ]]> REPLACE(#{endDate}, '-', '')
		<include refid="conditionMenuUseHistory" />
		ORDER BY USE_FROM_DATE DESC, USER_NAME, MENU_FULL_PATH
	</select>
	
	<select id="selectMenuUseHistoryPagingListByMonth" parameterType="java.util.HashMap" resultMap="menuUseHistoryResult">
		SELECT T.*
		FROM   (SELECT  ROW_NUMBER() OVER(ORDER BY USE_FROM_DATE DESC, USER_NAME, MENU_FULL_PATH ASC) RNUM,
						CONCAT(SUBSTR(USE_FROM_DATE, 1, 4), '-', SUBSTR(USE_FROM_DATE, 5, 2)) USE_FROM_DATE,
						USER_ID, REQ_TYPE,
						HIT_COUNT, MENU_ID, MENU_FULL_PATH,
						USER_NAME, COMP_NAME, DEPT_NAME, GRD_NAME
		        FROM   (SELECT SUBSTR(USE_FROM_DATE, 1, 6) USE_FROM_DATE, USER_ID, REQ_TYPE,
		                       SUM(HIT_COUNT) HIT_COUNT, MENU_ID, MENU_FULL_PATH,
		                       MIN(USER_NAME) USER_NAME, MIN(COMP_NAME) COMP_NAME,
		                       MIN(DEPT_NAME) DEPT_NAME, MIN(GRD_NAME) GRD_NAME
		                FROM   TN_CF_MENU_USE_HISTORY
		                WHERE  USE_FROM_DATE <![CDATA[ >= ]]> CONCAT(#{startDate}, '01')
		                AND    USE_FROM_DATE <![CDATA[ <= ]]> CONCAT(#{endDate}, '31')
		                <include refid="conditionMenuUseHistory" />
		                GROUP BY SUBSTR(USE_FROM_DATE, 1, 6), USER_ID, REQ_TYPE, MENU_ID, MENU_FULL_PATH) UH ) T
		WHERE  T.RNUM BETWEEN (#{pageIndex} - 1) * #{pageSize} + 1 AND #{pageIndex} * #{pageSize}
	</select>

	<select id="countMenuUseHistoryListByMonth" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(*) TOTAL
		FROM  (SELECT SUBSTR(USE_FROM_DATE, 1, 6) USE_FROM_DATE, USER_ID, REQ_TYPE, MENU_FULL_PATH
		       FROM   <include refid="tableMenuUseHistory" />
		       WHERE  USE_FROM_DATE <![CDATA[ >= ]]> CONCAT(#{startDate}, '01')
		       AND    USE_FROM_DATE <![CDATA[ <= ]]> CONCAT(#{endDate}, '31')
		       <include refid="conditionMenuUseHistory" />
		       GROUP BY SUBSTR(USE_FROM_DATE, 1, 6), USER_ID, REQ_TYPE, MENU_FULL_PATH) T
	</select>

    <select id="selectMenuUseHistoryListByMonth" parameterType="java.util.HashMap" resultMap="menuUseHistoryResult">
		SELECT  CONCAT(SUBSTR(USE_FROM_DATE, 1, 4), '-', SUBSTR(USE_FROM_DATE, 5, 2)) USE_FROM_DATE,
				USER_ID, REQ_TYPE,
				HIT_COUNT, MENU_FULL_PATH,
				USER_NAME, COMP_NAME, DEPT_NAME, GRD_NAME
		FROM   (SELECT SUBSTR(USE_FROM_DATE, 1, 6) USE_FROM_DATE, USER_ID, REQ_TYPE,
		               SUM(HIT_COUNT) HIT_COUNT, MENU_FULL_PATH,
		               MIN(USER_NAME) USER_NAME, MIN(COMP_NAME) COMP_NAME,
		               MIN(DEPT_NAME) DEPT_NAME, MIN(GRD_NAME) GRD_NAME
		        FROM   <include refid="tableMenuUseHistory" />
		        WHERE  USE_FROM_DATE <![CDATA[ >= ]]> CONCAT(REPLACE(#{startDate}, '-', ''), '01')
		        AND    USE_FROM_DATE <![CDATA[ <= ]]> CONCAT(REPLACE(#{endDate}, '-', ''), '31')
		        <include refid="conditionMenuUseHistory" />
		        GROUP BY SUBSTR(USE_FROM_DATE, 1, 6), USER_ID, REQ_TYPE, MENU_FULL_PATH) UH
		ORDER BY USE_FROM_DATE DESC, USER_NAME, MENU_FULL_PATH
	</select>

	<select id="selectApiUseHistoryList" parameterType="java.util.HashMap" resultMap="menuUseHistoryResult">
		SELECT DESCRIPTION API_FULL_PATH, COUNT(LOG_OCCUR_ID) HIT_COUNT
		FROM   <include refid="tableSysUseLog" />
		WHERE  USER_ID = #{userId}
		AND    REQ_TYPE = 'WEB'
		AND    LOG_FLAG = '4'
		AND    MENU_ID = #{menuId}
		AND    DESCRIPTION IS NOT NULL
		<if test="periodType.equals('day')">
		AND    USE_FROM_DATE <![CDATA[ >= ]]> #{startDate}
		AND    USE_FROM_DATE <![CDATA[ <= ]]> #{endDate}
		</if>
		<if test="periodType.equals('month')">
		AND    USE_FROM_DATE <![CDATA[ >= ]]> CONCAT(#{startDate}, '01')
		AND    USE_FROM_DATE <![CDATA[ <= ]]> CONCAT(#{endDate}, '31')
		</if>
		GROUP BY DESCRIPTION
	</select>

    <select id="selectFileDownloadLogPagingList" parameterType="java.util.HashMap" resultMap="sysUseLogResult">
		SELECT @ROWNUM:=@ROWNUM+1 AS ID,
		       P.*
		FROM   (SELECT T.*,
		               CEILING(ROW_NUMBER() OVER(<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">ORDER BY ${orderBy}</if>) / (#{pageSize} * 1.0)) PAGE
		        FROM   (SELECT U.COMP_NAME, U.DEPT_NAME, U.USER_NAME, U.GRD_NAME, SUL.DESCRIPTION, SUL.USE_FROM_DATE FROM_DATE,
		                       <if test="periodFlag == 1">
							   CONCAT(SUBSTR(SUL.USE_FROM_DATE, 1, 4), '-', SUBSTR(SUL.USE_FROM_DATE, 5, 2)) USE_FROM_DATE,
							   </if>
							   <if test="periodFlag == 2">
							   CONCAT(SUBSTR(SUL.USE_FROM_DATE, 1, 4), '-', SUBSTR(SUL.USE_FROM_DATE, 5, 2), '-', SUBSTR(SUL.USE_FROM_DATE, 7, 2)) USE_FROM_DATE,
				               </if>
							   SUL.USE_FROM_HHMMSS
		                FROM   <include refid="tableSysUseLog" /> SUL,
		                       <include refid="user.tableUser" /> U
		                WHERE  SUL.USER_ID = U.USER_ID
		                AND    SUL.LOG_FLAG = '5'
		                <if test="periodFlag == 1">
                        AND    SUBSTR(USE_FROM_DATE, 1, 6) <![CDATA[ >= ]]> #{startDate}
                        AND    SUBSTR(USE_FROM_DATE, 1, 6) <![CDATA[ <= ]]> #{endDate}
                        </if>
		                <if test="periodFlag == 2">
		                AND    SUL.USE_FROM_DATE <![CDATA[ >= ]]> #{startDate}
		                AND    SUL.USE_FROM_DATE <![CDATA[ <= ]]> #{endDate}
		                </if>
		                <include refid="conditionSysUseLog" />) T ) P,
		  		(SELECT @ROWNUM:=0) R
		WHERE  P.PAGE = #{pageIndex}
	</select>

    <select id="countFileDownloadLogList" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(SUL.USER_ID) TOTAL
		FROM   <include refid="tableSysUseLog" /> SUL,
		       <include refid="user.tableUser" /> U
		WHERE  SUL.USER_ID = U.USER_ID
		AND    SUL.LOG_FLAG = '5'
		<if test="periodFlag == 1">
        AND    SUBSTR(USE_FROM_DATE, 1, 6) <![CDATA[ >= ]]> #{startDate}
        AND    SUBSTR(USE_FROM_DATE, 1, 6) <![CDATA[ <= ]]> #{endDate}
        </if>
		<if test="periodFlag == 2">
		AND    SUL.USE_FROM_DATE <![CDATA[ >= ]]> #{startDate}
		AND    SUL.USE_FROM_DATE <![CDATA[ <= ]]> #{endDate}
		</if>
		<include refid="conditionSysUseLog" />
	</select>

    <select id="selectFileDownloadLogList" parameterType="java.util.HashMap" resultMap="sysUseLogResult">
		SELECT U.COMP_NAME, U.DEPT_NAME, U.USER_NAME, U.GRD_NAME,
		       SUL.DESCRIPTION,
		       <if test="periodFlag == 1">
			   CONCAT(SUBSTR(SUL.USE_FROM_DATE, 1, 4), '-', SUBSTR(SUL.USE_FROM_DATE, 5, 2)) USE_FROM_DATE,
			   </if>
			   <if test="periodFlag == 2">
			   CONCAT(SUBSTR(SUL.USE_FROM_DATE, 1, 4), '-', SUBSTR(SUL.USE_FROM_DATE, 5, 2), '-', SUBSTRING(SUL.USE_FROM_DATE, 7, 2)) USE_FROM_DATE,
               </if>
			   SUL.USE_FROM_HHMMSS
		FROM   <include refid="tableSysUseLog" /> SUL,
		       <include refid="user.tableUser" /> U
		WHERE  SUL.USER_ID = U.USER_ID
		AND    SUL.LOG_FLAG = '5'
		<if test="periodFlag == 1">
        AND    SUBSTR(USE_FROM_DATE, 1, 6) <![CDATA[ >= ]]> REPLACE(#{startDate}, '-', '')
        AND    SUBSTR(USE_FROM_DATE, 1, 6) <![CDATA[ <= ]]> REPLACE(#{endDate}, '-', '')
        </if>
		<if test="periodFlag == 2">
		AND    USE_FROM_DATE <![CDATA[ >= ]]> REPLACE(#{startDate}, '-', '')
		AND    USE_FROM_DATE <![CDATA[ <= ]]> REPLACE(#{endDate}, '-', '')
		</if>
		<include refid="conditionSysUseLog" />
		ORDER BY SUL.USE_FROM_DATE DESC, USE_FROM_HHMMSS DESC
	</select>

	<select id="selectListLoginOutPagingList" parameterType="java.util.HashMap" resultMap="loginOutResult">
		SELECT *
		FROM   (SELECT T1.*,
		               CEILING(ROW_NUMBER() OVER (<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">ORDER BY ${orderBy}</if>
		                                          <if test="@com.samsung.SdlComparator@isEmpty(orderBy)">ORDER BY USER_NAME</if>) / (#{pageSize} * 1.0)) AS PAGE
				FROM   (SELECT LO.USER_ID, LO.LOGIN_TIME,
				               LO.LOGOUT_TIME, LO.USE_IP, LO.REQ_TYPE,
				               U.USER_NAME, U.COMP_NAME,  U.DEPT_NAME, U.GRD_NAME
						FROM   <include refid="loginOut.tableLoginOut" /> LO
					        INNER JOIN <include refid="user.tableUser" /> U
					            ON LO.USER_ID = U.USER_ID
						<include refid="conditionLoginOut" />) T1 ) T2
		WHERE  PAGE = #{pageIndex}
		<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
		ORDER BY ${orderBy}
		</if>
	</select>

	<select id="countListLoginOutList" parameterType="java.util.HashMap" resultType="int">
		SELECT COUNT(*) AS TOTAL
		FROM   <include refid="loginOut.tableLoginOut" /> LO
		    INNER JOIN <include refid="user.tableUser" /> U
		        ON LO.USER_ID = U.USER_ID
		<include refid="conditionLoginOut" />
	</select>
	
</mapper>