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

	<resultMap id="sysUseLogResult"  type="sysUseLog">
		<result property="logOccurId" column="LOG_OCCUR_ID"/>
		<result property="userId" column="USER_ID"/>
		<result property="sysResourceId" column="SYS_RESOURCE_ID"/> 
		<result property="useFromDate" column="USE_FROM_DATE"/>
		<result property="useFromHhmmss" column="USE_FROM_HHMMSS"/>
		<result property="useThruDatetime" column="USE_THRU_DATETIME"/>
		<result property="useIp" column="USE_IP"/>
		<result property="description" column="DESCRIPTION"/>
		<result property="responseTime" column="RESPONSE_TIME"/>
		<result property="responseName" column="RESPONSE_NAME"/>
		<result property="errorName" column="ERROR_NAME"/>
		<result property="browserTypeName" column="BROWSER_TYPE_NAME"/>
		<result property="url" column="URL"/>
		<result property="path" column="PATH"/>
		<result property="parameter" column="PARAMETER"/>
		<result property="batchStatusCodeId" column="BATCH_STATUS_CODE_ID"/>
		<result property="batchResultCodeId" column="BATCH_RESULT_CODE_ID"/>
		<result property="logFlag" column="LOG_FLAG"/>
		<result property="nodeId" column="NODE_ID"/>
		<result property="reqType" column="REQ_TYPE"/>
		<result property="menuId" column="MENU_ID"/>
		<result property="pageId" column="PAGE_ID"/>
		<result property="method" column="METHOD"/>
		<association property="sysResource" column="{sysResourceId=SYS_RESOURCE_ID}" javaType="sysResource" select="sysResource.selectSysResource"/>
	</resultMap>

	<resultMap id="sysUseLogTotalResult"  type="sysUseLog">
		<result property="userId" column="USER_ID"/>
		<result property="description" column="DESCRIPTION"/>
		<result property="useFromDate" column="USE_FROM_DATE"/>
		<result property="reqType" column="REQ_TYPE"/>
		<result property="sysResourceId" column="SYS_RESOURCE_ID"/>
		<result property="count" column="COUNT"/>
		<association property="user" javaType="user">
			<id property="userId" column="USER_ID"/>
			<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="menuUtilizationResult"  type="menuUtilization">
		<id property="useDate" column="USE_DATE" />
		<id property="menuId" column="MENU_ID" />
		<result property="menuTypeCodeId" column="MENU_TYPE_CODE_ID" />
		<result property="label" column="LABEL" />
		<result property="labelJson" column="LABEL_JSON" />
		<result property="menuPathName" column="MENU_PATH_NAME" />
		<result property="menuUsePeriodCode" column="MENU_USE_PERIOD_CODE" />
		<result property="menuUsePeriodName" column="MENU_USE_PERIOD_NAME" />
		<result property="userNum" column="USER_NUM" />
		<result property="hitCount" column="HIT_COUNT" />
		<result property="utilizePercent" column="UTILIZE_PERCENT" />
		<result property="periodFlag" column="PERIOD_FLAG" />
		<result property="batchDate" column="BATCH_DATE" />
		<result property="menuUsed" column="MENU_USE_YN" />
		<result property="menuDeleted" column="MENU_DELETE_YN" />
	</resultMap>
	
	<sql id="tableSysUseLog">TN_CF_SYS_USE_LOG</sql>
	<sql id="tableMenuUseMm">TS_CF_MENU_USE_MM</sql>

	<sql id="columnsSysUseLog">
	LOG_OCCUR_ID, USER_ID, SYS_RESOURCE_ID,
	USE_FROM_DATE, USE_FROM_HHMMSS, USE_THRU_DATETIME,
	USE_IP, DESCRIPTION,
	RESPONSE_TIME, RESPONSE_NAME,
	ERROR_NAME, BROWSER_TYPE_NAME,
	URL, PATH, PARAMETER,
	BATCH_STATUS_CODE_ID, BATCH_RESULT_CODE_ID,
	LOG_FLAG, NODE_ID,
	FIRST_REG_DATETIME, REQ_TYPE,
	MENU_ID, PAGE_ID, METHOD
	</sql>

	<sql id="conditionSysUseLog">
		<trim prefix="WHERE" prefixOverrides="AND|OR">
					MENU_ID IS NOT NULL
			<if test="@com.samsung.SdlComparator@isNotEmpty(logFlag)">
				AND LOG_FLAG = #{logFlag}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(reqType)">
				AND REQ_TYPE = #{reqType}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(useFromDate)">
				AND USE_FROM_DATE >= #{useFromDate}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(sysResourceId)">
				AND SYS_RESOURCE_ID = #{sysResourceId}
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(useThruDate)">
				<![CDATA[
				AND TO_CHAR(USE_THRU_DATETIME, 'YYYYMMDD') <= #{useThruDate}
				]]>
			</if>
			<if test="@com.samsung.SdlComparator@isNotEmpty(searchKeyword)">
				AND EXISTS (SELECT 1 FROM TN_CF_USER USR WHERE USELOG.USER_ID = USR.USER_ID AND LOWER(USR.USER_NAME) LIKE '%' || LOWER(#{searchKeyword}) || '%')
			</if>
			<if test="period!=null and period.equals('mon')">
				<if test="@com.samsung.SdlComparator@isNotEmpty(startDate)">
					AND TO_CHAR(USE_FROM_DATE, 'YYYYMM') <![CDATA[ >= ]]> #{startDate}
				</if>
				<if test="@com.samsung.SdlComparator@isNotEmpty(endDate)">
					AND TO_CHAR(USE_FROM_DATE, 'YYYYMM') <![CDATA[ <= ]]> #{endDate}
				</if>
				AND USELOG.USER_ID = CFUSER.USER_ID
				AND USELOG.DESCRIPTION IS NOT NULL
				GROUP BY USELOG.USER_ID, MENU_ID, USELOG.DESCRIPTION, SUBSTR(USE_FROM_DATE, 1, 4) || '-' || SUBSTR(USE_FROM_DATE, 5, 2),
				CFUSER.COMP_NAME, CFUSER.DEPT_NAME, CFUSER.USER_NAME, CFUSER.GRD_NAME, REQ_TYPE, METHOD
			</if>
			<if test="period!=null and period.equals('day')">
				<if test="@com.samsung.SdlComparator@isNotEmpty(startDate)">
					AND TO_CHAR(USE_FROM_DATE, 'YYYYMMDD') <![CDATA[ >= ]]> #{startDate}
				</if>
				<if test="@com.samsung.SdlComparator@isNotEmpty(endDate)">
					AND TO_CHAR(USE_FROM_DATE, 'YYYYMMDD') <![CDATA[ <= ]]> #{endDate}
				</if>
				AND USELOG.USER_ID = CFUSER.USER_ID
				AND USELOG.DESCRIPTION IS NOT NULL
				GROUP BY USELOG.USER_ID, MENU_ID, USELOG.DESCRIPTION, SUBSTR(USE_FROM_DATE, 1, 4) || '-' || SUBSTR(USE_FROM_DATE, 5, 2) || '-' || SUBSTR(USE_FROM_DATE, 7, 2),
				CFUSER.COMP_NAME, CFUSER.DEPT_NAME, CFUSER.USER_NAME, CFUSER.GRD_NAME, REQ_TYPE, METHOD
			</if>
		</trim>
	</sql>

	<sql id="conditionMenuUtilization">
		<trim prefix="WHERE" prefixOverrides="AND|OR">
			<if test="period != null and period==1">
				<if test="@com.samsung.SdlComparator@isNotEmpty(period)">
					AND PERIOD_FLAG = #{period}
				</if>
				<if test="@com.samsung.SdlComparator@isNotEmpty(endDate)">
					AND TO_CHAR(USE_DATE, 'YYYYMM') = #{endDate}
				</if>
			</if>
			<if test="period != null and period==2">
				<if test="@com.samsung.SdlComparator@isNotEmpty(period)">
					AND PERIOD_FLAG = #{period}
				</if>
				<if test="@com.samsung.SdlComparator@isNotEmpty(startDate)">
					AND USE_DATE <![CDATA[ >= ]]> #{startDate}
				</if>
				<if test="@com.samsung.SdlComparator@isNotEmpty(endDate)">
					AND USE_DATE <![CDATA[ <= ]]> #{endDate}
				</if>
			</if>
		</trim>
	</sql>

	<select id="selectSysUseLogList" parameterType="java.util.HashMap" resultMap="sysUseLogResult">
		SELECT <include refid="columnsSysUseLog"/>
		FROM   <include refid="tableSysUseLog"/>
		<include refid="conditionSysUseLog"/>
	</select>
	
	<select id="selectSysUseLogPagingList" parameterType="java.util.HashMap" resultMap="sysUseLogResult">
		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 LOG_OCCUR_ID
						</if>
					) / (#{pageSize}*1.0) ) AS PAGE
			FROM
			(
					SELECT
							<include refid="columnsSysUseLog"/>
					FROM 	<include refid="tableSysUseLog"/>
					<include refid="conditionSysUseLog"/>
			) T1
		) SUB WHERE PAGE = #{pageIndex}
		<if test="@com.samsung.SdlComparator@isNotEmptyForDynamicSql(orderBy)">
			ORDER BY ${orderBy}
		</if>
	</select>

	<select id="countSysUseLog" resultType="int">
		SELECT 	COUNT(*) AS TOTAL
		FROM 	<include refid="tableSysUseLog"/>
		<include refid="conditionSysUseLog"/>
	</select>

	<insert id="insertSysUseLog" parameterType="sysUseLog">
		INSERT INTO <include refid="tableSysUseLog"/> ( <include refid="columnsSysUseLog"/>	)
		VALUES (
					#{logOccurId},
					#{userId,jdbcType=VARCHAR},
					#{sysResourceId,jdbcType=VARCHAR},
					#{useFromDate,jdbcType=VARCHAR},
					#{useFromHhmmss,jdbcType=VARCHAR},
					#{useThruDatetime,jdbcType=TIMESTAMP},
					#{useIp,jdbcType=VARCHAR},
					#{description,jdbcType=VARCHAR},
					#{responseTime},
					#{responseName,jdbcType=VARCHAR},
					#{errorName,jdbcType=VARCHAR},
					#{browserTypeName,jdbcType=VARCHAR},
					#{url,jdbcType=VARCHAR},
					#{path,jdbcType=VARCHAR},
					#{parameter,jdbcType=VARCHAR},
					#{batchStatusCodeId,jdbcType=VARCHAR},
					#{batchResultCodeId,jdbcType=VARCHAR},
					#{logFlag,jdbcType=VARCHAR},
					#{nodeId,jdbcType=VARCHAR},
					#{firstRegDatetime,jdbcType=TIMESTAMP},
					#{reqType,jdbcType=VARCHAR},
					#{menuId,jdbcType=VARCHAR},
					#{pageId,jdbcType=VARCHAR},
					#{method,jdbcType=VARCHAR}
		)
	</insert>
	<update id="updateSysUseLog"  parameterType="sysUseLog" >
		UPDATE TN_CF_SYS_USE_LOG SET	
				USE_THRU_DATETIME    = #{useThruDatetime,jdbcType=TIMESTAMP}, 
				ERROR_NAME           = #{errorName,jdbcType=VARCHAR},    
				RESPONSE_TIME		 = #{responseTime,jdbcType=VARCHAR},    
				RESPONSE_NAME		 = #{responseName,jdbcType=VARCHAR},         
				BATCH_STATUS_CODE_ID = #{batchStatusCodeId,jdbcType=VARCHAR},    
				BATCH_RESULT_CODE_ID = #{batchResultCodeId,jdbcType=VARCHAR},
				DESCRIPTION          = #{description,jdbcType=VARCHAR},
				LOG_FLAG             = #{logFlag,jdbcType=VARCHAR},
				SYS_RESOURCE_ID      = #{sysResourceId,jdbcType=VARCHAR}
	 	 WHERE LOG_OCCUR_ID       = #{logOccurId}
	</update>
	<delete id="deleteSysUseLog" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableSysUseLog"/>
		 WHERE USE_FROM_DATE = #{delDate}
		   AND NODE_ID = #{nodeId}
	</delete>
	
	
	<delete id="deleteExpiredRetentionPeriod" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableMenuUseMm" />
		WHERE  USE_DATE <![CDATA[ < ]]> #{targetMonth}
		AND    PERIOD_FLAG = '1'
	</delete>
	
	<delete id="deleteMenuUtilizationByBatchTargetDate" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableMenuUseMm" />
		WHERE  USE_DATE = #{targetDate}
	</delete>
	
	<select id="selectMenuUtilizeDtListForBatch" parameterType="java.util.HashMap" resultMap="menuUtilizationResult">
		SELECT #{targetDate} USE_DATE, M.MENU_ID, M.MENU_TYPE_CODE_ID, M.MENU_USE_PERIOD_CODE,
		       COALESCE(MUC.USER_NUM, 0) USER_NUM, COALESCE(HUC.HIT_COUNT, 0) HIT_COUNT,
		       CASE
		          WHEN COALESCE(MUC.USER_NUM, 0) != 0 AND COALESCE(HUC.HIT_COUNT, 0) != 0
		          THEN ROUND(HUC.HIT_COUNT / (MUC.USER_NUM * MUP.MONTH_CHNG_USE_PERIOD) * 100, 2)
		          ELSE 0
		       END UTILIZE_PERCENT,
		       '2' PERIOD_FLAG
		FROM   <include refid="menu.tableMenu" /> M
		       INNER JOIN <include refid="sysResource.tableSysResource" /> SR
		         ON M.MENU_ID = SR.SYS_RESOURCE_ID
		       LEFT OUTER JOIN (SELECT CNT.MENU_ID, COUNT(DISTINCT CNT.USER_ID) USER_NUM
		                        FROM   (SELECT WA.MENU_ID, U.USER_ID
		                                FROM   <include refid="user.tableUser" /> U,
		                                       (SELECT WA.SYS_RESOURCE_ID MENU_ID
		                                        FROM   <include refid="workgroupMenu.tableWorkAuthorization" /> WA
		                                        WHERE  WA.WORKGROUP_ID = 'DEFAULT_WORKGROUP') WA
		                                WHERE  U.ACTIVE_FLAG = '1'
		                                AND    U.DELETED = '0'
		                                UNION
		                                SELECT WA.SYS_RESOURCE_ID MENU_ID, UR.USER_ID
		                                FROM   <include refid="role.tableRole" /> R,
		                                       <include refid="userRole.tableUserRole" /> UR,
		                                       <include refid="user.tableUser" /> U,
		                                       <include refid="workgroupRole.tableWorkgroupRole" /> WR,
		                                       <include refid="workgroupMenu.tableWorkAuthorization" /> WA,
		                                       <include refid="workgroup.tableWorkgroup" /> W
		                                WHERE  R.ROLE_ID = UR.ROLE_ID
		                                AND    UR.ROLE_ID = WR.USER_ROLE_ID
		                                AND    UR.USER_ID = U.USER_ID
		                                AND    WR.WORKGROUP_ID = WA.WORKGROUP_ID
		                                AND    WR.WORKGROUP_ID = W.WORKGROUP_ID
		                                AND    R.DELETE_YN = '0'
		                                AND    U.ACTIVE_FLAG = '1'
		                                AND    U.DELETED = '0'
		                                AND    W.DELETE_YN = '0'
		                                UNION
		                                SELECT WA.SYS_RESOURCE_ID MENU_ID, WR.USER_ID
		                                FROM   <include refid="user.tableUser" /> U,
		                                       <include refid="workgroupRole.tableWorkgroupRole" /> WR,
		                                       <include refid="workgroupMenu.tableWorkAuthorization" /> WA,
		                                       <include refid="workgroup.tableWorkgroup" /> W
		                                WHERE  U.USER_ID = WR.USER_ROLE_ID
		                                AND    WR.WORKGROUP_ID = WA.WORKGROUP_ID
		                                AND    WR.WORKGROUP_ID = W.WORKGROUP_ID
		                                AND    U.ACTIVE_FLAG = '1'
		                                AND    U.DELETED = '0'
		                                AND    W.DELETE_YN = '0'
		                                AND    WR.THRU_DATE <![CDATA[ >= ]]> TO_CHAR(NOW(), 'YYYYMMDD')
		                                UNION
		                                SELECT UA.SYS_RESOURCE_ID MENU_ID, UA.USER_ID
		                                FROM   <include refid="user.tableUser" /> U,
		                                       <include refid="user.tableUserAuthorization" /> UA
		                                WHERE  U.USER_ID = UA.USER_ID
		                                AND    U.ACTIVE_FLAG = '1'
		                                AND    U.DELETED = '0'
		                                AND    UA.THRU_DATE <![CDATA[ >= ]]> TO_CHAR(NOW(), 'YYYYMMDD')) CNT
		                        GROUP BY CNT.MENU_ID) MUC
		         ON M.MENU_ID = MUC.MENU_ID
		       LEFT OUTER JOIN (SELECT MENU_ID, COUNT(USER_ID) HIT_COUNT
		                        FROM   <include refid="tableSysUseLog" />
		                        WHERE  USE_FROM_DATE = #{targetDate}
		                        AND    LOG_FLAG = '6'
		                        AND    MENU_ID IS NOT NULL
		                        GROUP BY MENU_ID) HUC
		         ON M.MENU_ID = HUC.MENU_ID
		       LEFT JOIN (SELECT MENU_USE_PERIOD_CODE,
		                         (1.0/30) * MONTH_CHNG_USE_PERIOD MONTH_CHNG_USE_PERIOD
		                  FROM   <include refid="commCode.tableMenuUsePeriod" />) MUP
		         ON M.MENU_USE_PERIOD_CODE = MUP.MENU_USE_PERIOD_CODE
		WHERE  SR.SYS_RESOURCE_ID != 'COMM_URL'
	</select>
	
	<select id="selectMenuUtilizeMmListForBatch" parameterType="java.util.HashMap" resultMap="menuUtilizationResult">
		SELECT #{targetMonth} USE_DATE, MUM.MENU_ID,
		       M.MENU_TYPE_CODE_ID, M.MENU_USE_PERIOD_CODE,
		       ROUND(MUM.USER_NUM_SUM / MUM.USER_NUM_COUNT, 2) USER_NUM,
		       MUM.HIT_COUNT, 
		       ROUND(MUM.UTILIZE_PERCENT_SUM / MUM.UTILIZE_PERCENT_COUNT, 2) UTILIZE_PERCENT,
		       '1' PERIOD_FLAG
		FROM   (SELECT MENU_ID,
		               SUM(USER_NUM) USER_NUM_SUM,
		               COUNT(USER_NUM) USER_NUM_COUNT,
		               SUM(HIT_COUNT) HIT_COUNT,
		               SUM(UTILIZE_PERCENT) UTILIZE_PERCENT_SUM,
		               COUNT(UTILIZE_PERCENT) UTILIZE_PERCENT_COUNT
		        FROM   <include refid="tableMenuUseMm" />
		        WHERE  USE_DATE LIKE #{targetMonth} || '%'
		        GROUP BY MENU_ID) MUM
		       LEFT JOIN <include refid="menu.tableMenu" /> M
		       ON MUM.MENU_ID = M.MENU_ID
	</select>
	
	<insert id="insertMenuUtilization" parameterType="java.util.HashMap">
		INSERT INTO <include refid="tableMenuUseMm" />
		(USE_DATE, MENU_ID, MENU_PATH_NAME,
		 MENU_TYPE_CODE_ID, MENU_USE_PERIOD_CODE,
		 USER_NUM, HIT_COUNT, UTILIZE_PERCENT,
		 PERIOD_FLAG, BATCH_DATE)
		VALUES
		(#{useDate}, #{menuId}, #{menuPathName},
		 #{menuTypeCodeId}, #{menuUsePeriodCode},
		 #{userNum}, #{hitCount}, #{utilizePercent},
		 #{periodFlag}, NOW())
	</insert>

	<select id="selectMenuUtilizationList" parameterType="java.util.HashMap" resultMap="menuUtilizationResult">
		SELECT CASE
		          WHEN #{periodFlag} = '1' THEN SUBSTR(MUM.USE_DATE, 1, 4) || '-' || SUBSTR(MUM.USE_DATE, 5, 2)
		          ELSE SUBSTR(MUM.USE_DATE, 1, 4) || '-' || SUBSTR(MUM.USE_DATE, 5, 2) || '-' || SUBSTR(MUM.USE_DATE, 7, 2)
		       END USE_DATE,
		       MUM.MENU_ID, MUM.MENU_PATH_NAME,
		       MUM.MENU_TYPE_CODE_ID, CC.LABEL, CC.LABEL_JSON,
		       MUM.MENU_USE_PERIOD_CODE, MUP.MENU_USE_PERIOD_NAME,
		       MUM.USER_NUM, MUM.HIT_COUNT, MUM.UTILIZE_PERCENT,
		       M.USE_YN MENU_USE_YN, M.DELETE_YN MENU_DELETE_YN
		FROM   <include refid="tableMenuUseMm" /> MUM,
		       <include refid="sysResource.tableCommCode" /> CC,
		       <include refid="commCode.tableMenuUsePeriod" /> MUP,
		       <include refid="menu.tableMenu" /> M
		WHERE  MUM.MENU_TYPE_CODE_ID = CC.CODE_ID
		AND    MUM.MENU_USE_PERIOD_CODE = MUP.MENU_USE_PERIOD_CODE
		AND    MUM.MENU_ID = M.MENU_ID
		AND    CC.COMM_CODE_TYPE_CODE = 'MENUTYPE'
		AND    MUM.PERIOD_FLAG = #{periodFlag}
		AND    MUM.USE_DATE <![CDATA[ >= ]]> #{startDate}
		AND    MUM.USE_DATE <![CDATA[ <= ]]> #{endDate}
		ORDER BY USE_DATE, MENU_PATH_NAME, M.MENU_LEVEL, M.MENU_SEQUENCE
	</select>

</mapper>