<?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="egovframework.com.its.web.server.mapper.AdminMapper">

	<select id="getUser" parameterType="java.lang.String" resultType="egovframework.com.its.web.server.vo.AdminVO">
		SELECT 
				EMAIL AS email,
				MEMBER_NM AS username,
				PWD AS password,
				IS_ACCOUNT_LOCK AS is_account_lock
		FROM 
				TB_WWW_MEMBER
		WHERE 
				EMAIL  = #{id}
	</select>

	<select id="getPopupList" parameterType="java.util.HashMap" resultType="egovframework.com.its.web.server.vo.PopupVO">
		SELECT POPUPID,
               IMG_ID,
               IMG_NAME,
               POST_FROM,
               POST_TO,
               TO_CHAR(REGDATE,'YYYY-MM-DD HH24:MI') REGDATE,
               TITLE,
               URL,
               USE_YN
        FROM TB_WWW_POPUP
        <if test="id != null">
        	WHERE POPUPID = #{id}
        </if>
        ORDER BY USE_YN DESC, REGDATE ASC
	</select>

	<insert id="addPopup" parameterType="java.util.HashMap">
		INSERT INTO TB_WWW_POPUP
		(
		  POPUPID,
		  TITLE,
		  URL,
		  POST_FROM,
		  POST_TO,
		  IMG_ID,
		  IMG_NAME,
		  REGDATE
		)
		VALUES
		(
		  (SELECT NVL(MAX(TO_NUMBER(POPUPID)),0)+1 FROM TB_WWW_POPUP),
		  #{title},
		  #{url},
		  #{postFrom},
		  #{postTo},
		  #{imgId},
		  #{imgName},
		  TO_DATE(TO_CHAR(SYSDATE,'yymmddhh24'), 'YYMMDDHH24')
		)
	</insert>
	
	<update id="updatePopupUseYN" parameterType="java.util.HashMap">
		UPDATE TB_WWW_POPUP 
		   SET USE_YN = #{useYN}
		 WHERE POPUPID = #{id}
	</update>
	
	<update id="deletePopup" parameterType="java.util.HashMap">
		DELETE TB_WWW_POPUP 
		 WHERE POPUPID = #{id}
	</update>
	
	<update id="updatePopup" parameterType="java.util.HashMap">
		UPDATE TB_WWW_POPUP 
		   SET TITLE     = #{title},
		       URL       = #{url},
		       POST_FROM = #{postFrom},
		       POST_TO   = #{postTo}
			   <if test="imgId != null or imgId != ''">
			   ,IMG_ID   = #{imgId},
			   IMG_NAME  = #{imgName}
			   </if>
		  WHERE POPUPID = #{id}

	</update>

	<select id="checkPrePw" parameterType="java.util.HashMap" resultType="egovframework.com.its.web.server.vo.AdminInfoVO">
		SELECT EMAIL,	
		       PWD PASSWORD
		  FROM TB_WWW_MEMBER
		 WHERE EMAIL = #{id}
		   AND PWD = #{prePw}
	</select>
	
	<update id="updatePw" parameterType="java.util.HashMap">
		UPDATE TB_WWW_MEMBER 
		   SET PWD = #{pwd}
		 WHERE EMAIL = #{id}
	</update>
<select id="getHHStatus" parameterType="java.util.HashMap" resultType="egovframework.com.its.web.server.vo.StatsVO">
	SELECT
			AA.PAGE_NM AS TITLE,
			BB.CONNCNT AS CONNCNT,
			BB.STATHOUR AS DT,
			NVL(BB.TOTAL_CONNCNT, 0) AS TOTAL_CONNCNT
	FROM
			TB_WWW_PAGE AA
	LEFT OUTER JOIN(
					SELECT
							A.*,
							B.TOTAL_CONNCNT
					FROM
							(
							SELECT
									PAGEID,
									SUBSTR (MAX (SYS_CONNECT_BY_PATH (CONNCNT, ',')), 2) CONNCNT,
									SUBSTR (MAX (SYS_CONNECT_BY_PATH (STATHOUR, ',')), 2) STATHOUR
							FROM
									(
									SELECT
											CONNCNT,
											PAGEID,
											STATDATE,
											STATHOUR,
											ROW_NUMBER () OVER (PARTITION BY PAGEID ORDER BY STATHOUR) rnum
									FROM
											(
											SELECT
													CONN_CNT CONNCNT,
													PAGE_ID PAGEID,
													STAT_DAY STATDATE,
													STAT_HH STATHOUR
											FROM
													TB_WWW_CONN_PAGE_STAT_HH
											WHERE
													STAT_DAY = #{statDate}
											)
									)
							START WITH rnum = 1
							CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR PAGEID = PAGEID
							GROUP BY
									PAGEID
							ORDER BY
									PAGEID
							)A, (
								 SELECT
										 PAGE_ID PAGEID,
										 SUM(CONN_CNT) TOTAL_CONNCNT
								 FROM
										 TB_WWW_CONN_PAGE_STAT_HH
								 WHERE
										 STAT_DAY = #{statDate}
								 GROUP BY
										 PAGE_ID
								 ORDER BY
										 PAGE_ID
								)B
					WHERE
							A.PAGEID = B.PAGEID
					)BB
	ON
			AA.PAGE_ID = BB.PAGEID
	ORDER BY
			--PAGE_TP DESC,
			AA.PAGE_ID
	</select>
	<select id="getDDStatus" parameterType="java.util.HashMap" resultType="egovframework.com.its.web.server.vo.StatsVO">
		SELECT
				--AA.PAGE_TP || ':' || AA.PAGE_NM TITLE,
				AA.PAGE_NM TITLE,
				BB.CONNCNT,
				BB.STATDAY DT,
				NVL(BB.TOTAL_CONNCNT, 0) TOTAL_CONNCNT
		FROM
				TB_WWW_PAGE AA
		LEFT OUTER JOIN(
						SELECT
								A.*,
								B.TOTAL_CONNCNT
						FROM
								(
								SELECT
										PAGEID,
										SUBSTR (MAX (SYS_CONNECT_BY_PATH (CONNCNT, ',')), 2) CONNCNT,
										SUBSTR (MAX (SYS_CONNECT_BY_PATH (STATDAY, ',')), 2) STATDAY
								FROM
										(
										SELECT
												CONNCNT,
												PAGEID,
												STATMONTH,
												STATDAY,
												ROW_NUMBER () OVER (PARTITION BY PAGEID ORDER BY STATDAY) rnum
										FROM
												(
												SELECT
														CONN_CNT CONNCNT,
														PAGE_ID PAGEID,
														STAT_MON STATMONTH,
														STAT_DD STATDAY
												FROM
														TB_WWW_CONN_PAGE_STAT_DD
												WHERE
														STAT_MON = #{statMonth}
												)
										)
								START WITH rnum = 1
								CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR PAGEID = PAGEID
								GROUP BY
										PAGEID
								ORDER BY
								    	PAGEID
								)A, (
									SELECT
											PAGE_ID PAGEID,
											SUM(CONN_CNT) TOTAL_CONNCNT
									FROM
											TB_WWW_CONN_PAGE_STAT_DD
									WHERE
											STAT_MON = #{statMonth}
									GROUP BY
											PAGE_ID
									ORDER BY
											PAGEID
									)B
						WHERE
						    	A.PAGEID = B.PAGEID
						)BB
		ON
				AA.PAGE_ID = BB.PAGEID
		ORDER BY
				--PAGE_TP DESC,
				AA.PAGE_ID
	</select>
	<select id="getMMStatus" parameterType="java.util.HashMap" resultType="egovframework.com.its.web.server.vo.StatsVO">
		SELECT
				AA.PAGE_NM TITLE,
				BB.CONNCNT,
				BB.STATMON DT,
				NVL(BB.TOTAL_CONNCNT, 0) TOTAL_CONNCNT
		FROM
				TB_WWW_PAGE AA
		LEFT OUTER JOIN(
						SELECT
								A.*,
								B.TOTAL_CONNCNT
						FROM
								(
								SELECT
										PAGEID,
										SUBSTR (MAX (SYS_CONNECT_BY_PATH (CONNCNT, ',')), 2) CONNCNT,
										SUBSTR (MAX (SYS_CONNECT_BY_PATH (STATMON, ',')), 2) STATMON
								FROM
										(
										SELECT
											CONNCNT,
											PAGEID,
											STATYEAR,
											STATMON,
											ROW_NUMBER () OVER (PARTITION BY PAGEID ORDER BY STATMON) rnum
										FROM
										(
										SELECT
										CONN_CNT CONNCNT,
										PAGE_ID PAGEID,
										STAT_YEAR STATYEAR,
										STAT_MN STATMON
										FROM
										TB_WWW_CONN_PAGE_STAT_MN
										WHERE
										STAT_YEAR = #{statYear}
										)
										)
								START WITH rnum = 1
								CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR PAGEID = PAGEID
								GROUP BY
								    	PAGEID
								ORDER BY
								    	PAGEID
								)A, (
									SELECT
											PAGE_ID PAGEID,
											SUM(CONN_CNT) TOTAL_CONNCNT
									FROM
											TB_WWW_CONN_PAGE_STAT_MN
									WHERE
									    	STAT_YEAR = #{statYear}
									GROUP BY
									    	PAGE_ID
									ORDER BY
									    	PAGEID
									)B
						WHERE
								A.PAGEID = B.PAGEID
						)BB
		ON
				AA.PAGE_ID = BB.PAGEID
		ORDER BY AA.PAGE_ID
	</select>
	<select id="getYearStatsList" resultType="egovframework.com.its.web.server.vo.PeriodStatVO">
		SELECT
			AA.yy AS statYear,
			AA.mm AS statMn,
			AA.PAGE_ID AS pageId,
			AA.PAGE_NM AS pageName,
			COALESCE(BB.CONN_CNT, 0) AS connCnt
		  FROM (
				  SELECT TO_CHAR(A.dt, 'YYYY') AS yy,
						 TO_CHAR(A.dt, 'MM') AS mm,
						 B.*
					FROM (SELECT ADD_MONTHS(TO_DATE(#{from},'YYYY-MM'), LEVEL - 1) AS dt
							FROM DUAL
						 CONNECT BY LEVEL <![CDATA[<=]]> MONTHS_BETWEEN(TO_DATE(#{to},'YYYY-MM'),TO_DATE(#{from},'YYYY-MM')) + 1)
		                 A, (SELECT * FROM TB_WWW_PAGE) B
		  ) AA
		  LEFT OUTER JOIN TB_WWW_CONN_PAGE_STAT_MN BB
		    ON AA.yy = BB.STAT_YEAR
		   AND AA.mm = BB.STAT_MN
		   AND AA.PAGE_ID = BB.PAGE_ID
		 ORDER BY AA.PAGE_ID, AA.yy, AA.mm
	</select>

	<select id="getMonthStatsList" resultType="egovframework.com.its.web.server.vo.PeriodStatVO">
		SELECT
			AA.mm AS statMn,
			AA.dd AS statDd,
			AA.PAGE_ID AS pageId,
			AA.PAGE_NM AS pageName,
			COALESCE(BB.CONN_CNT, 0) AS connCnt
		  FROM (
				SELECT TO_CHAR(A.dt, 'YYYYMM') AS mm,
					   TO_CHAR(A.dt, 'DD') AS dd,
					   B.*
				  FROM (SELECT (TO_DATE(#{from},'YYYY-MM-DD') + LEVEL - 1) AS dt
						  FROM DUAL
					   CONNECT BY LEVEL <![CDATA[<=]]> GREATEST((TO_DATE(#{to},'YYYY-MM-DD') - TO_DATE(#{from},'YYYY-MM-DD')) + 1, 1))
				  A, (SELECT * FROM TB_WWW_PAGE) B
			 ) AA
		  LEFT OUTER JOIN TB_WWW_CONN_PAGE_STAT_DD BB
		    ON AA.mm = BB.STAT_MON
		   AND AA.dd = BB.STAT_DD
		   AND AA.PAGE_ID = BB.PAGE_ID
		 ORDER BY AA.PAGE_ID, AA.mm, AA.dd
	</select>

	<select id="getHourStatsList" resultType="egovframework.com.its.web.server.vo.PeriodStatVO">
		SELECT
			AA.stat_day AS statDd,
			AA.stat_hh AS statHh,
			AA.PAGE_ID AS pageId,
			AA.PAGE_NM AS pageName,
			COALESCE(BB.CONN_CNT, 0) AS connCnt
		FROM (
				 SELECT #{date} AS stat_day,
						LPAD(TO_CHAR(A.hh), 2, '0') AS stat_hh,
						B.*
				 FROM (SELECT LEVEL - 1 AS hh
					     FROM DUAL
					  CONNECT BY LEVEL <![CDATA[<=]]> 24)
					  A, (SELECT * FROM TB_WWW_PAGE ORDER BY PAGE_ID) B
			 ) AA
		LEFT OUTER JOIN TB_WWW_CONN_PAGE_STAT_HH BB
		     	     ON AA.stat_day = BB.stat_day
					AND AA.stat_hh = BB.stat_hh
					AND AA.PAGE_ID = BB.PAGE_ID
</select>
	<update id="adminLoginMake" parameterType="java.util.HashMap">
	INSERT INTO
	    		TB_USER_CNNC_HS(LOGIN_HMS,
	    		                USER_ID,
	    		                LOGOUT_HMS,
	    		                OPER_SYST_ID) VALUES (TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
	    		                                      'ADMIN',
	    		                                      NULL,
	    		                                      'WEB')
	</update>
	<update id="adminLogoutMake" parameterType="java.util.HashMap">

			UPDATE
					TB_USER_CNNC_HS
			SET
			    	LOGOUT_HMS = TO_CHAR(SYSDATE,'yyyymmddhh24miss')
			WHERE
			    	LOGIN_HMS = (select
										LOGIN_HMS
			    	             from
										(select
												T.LOGIN_HMS,
												T.USER_ID,
												T.LOGOUT_HMS,
												M.MAXHMS
										from
												TB_USER_CNNC_HS T, (select
																			MAX(LOGIN_HMS) as MAXHMS
																    from
																			TB_USER_CNNC_HS
																   ) M
										)
								 where
										MAXHMS=LOGIN_HMS
								 )
	</update>

	<update id="setFailCount" parameterType="java.util.HashMap">
		UPDATE
			TB_WWW_MEMBER
		SET
			LOGIN_FAIL_COUNT = #{count}
		WHERE
			EMAIL = #{email}
	</update>

<!--	<select id="getFailCount" parameterType="java.util.HashMap" resultType="java.util.HashMap">-->
	<select id="getFailCount" parameterType="java.util.HashMap" resultType="java.lang.Integer">
		SELECT
			LOGIN_FAIL_COUNT
		FROM
			TB_WWW_MEMBER
		WHERE
			EMAIL = #{email}
	</select>

	<update id="setAcountLock" parameterType="java.util.HashMap">
		UPDATE
			TB_WWW_MEMBER
		SET
			IS_ACCOUNT_LOCK = 'Y'
		WHERE
			EMAIL = #{email}
	</update>
</mapper>