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

	<resultMap id="weakplaceResult" type="weakPlaceVO">
		<result property="place1" column="PLACE1" />
		<result property="place1str" column="CODENM" />
		<result property="wpNo" column="WP_NO" />
		<result property="name" column="NAME" />
		<result property="location" column="LOCATION" />
		<result property="comments" column="COMMENTS" />
		<result property="filename1" column="FILENAME1" />
		<result property="filename2" column="FILENAME2" />
		<result property="writer" column="WRITER" />
		<result property="credate" column="CREDATE" />
		<result property="logdate" column="LOGDATE" />
	</resultMap>
	
	<select id="WeakPlace.select" parameterType="weakPlaceVO" resultMap="weakplaceResult">
	<![CDATA[	
		SELECT
			WP.*, C.CODENM
		FROM 
			TWEAKPLACE WP, TCODE C
		WHERE
			UPPER(WP.PLACE1) = UPPER(C.CODE2)
          	AND C.CODE1 = 'C001'
			AND WP.PLACE1 = #{place1}
			AND WP.WP_NO = #{wpNo}
	]]>
	</select>

	<select id="WeakPlace.selectList" parameterType="weakPlaceSearchVO" resultMap="weakplaceResult">
	<![CDATA[
        SELECT
        	WP.*, C.CODENM
        FROM
        	TWEAKPLACE WP, TCODE C
        WHERE
        	UPPER(WP.PLACE1) = UPPER(C.CODE2)
          	AND C.CODE1 = 'C001'
  	]]>
  		<if test="place1 != null and place1 != '' and place1 != 'PS00'">
			AND WP.PLACE1 = #{place1}
		</if>
	<![CDATA[
		ORDER BY
			WP.PLACE1 ASC, WP.WP_NO ASC
	]]>
	</select>
	
	<select id="WeakPlace.selectStatsPerPlace" parameterType="hashmap" resultType="hashmap" >
	<![CDATA[
		SELECT 
			* 
		FROM 
		(
	        SELECT
	        	'0' AS PLACE1, '전체' AS CODENM_YAK, COUNT(*) AS CNT
			FROM
			    TWEAKPLACE
			UNION
			SELECT
			  	C.CODE2, C.CODENM_YAK, COUNT(WP.WP_NO) AS CNT
			FROM
			  	TCODE C LEFT JOIN TWEAKPLACE WP ON UPPER(C.CODE2) = UPPER(WP.PLACE1)
			WHERE
			  	C.CODE1 = 'C001'
			  	AND UPPER(C.CODE2) IN
	]]>
			  	<foreach collection="placeList" item="item" index="index" separator="," open="(" close=")">
		  			UPPER(#{item})
			  	</foreach>
	<![CDATA[			  	
			GROUP BY
			  	C.CODE2, C.CODENM_YAK
		) AS list
        ORDER BY 
          	UPPER(PLACE1) ASC
  	]]>
	</select>	

	<select id="selectMaxWpNo" parameterType="String" resultType="int">
	<![CDATA[
		SELECT 
			COALESCE(WP_NO, 0) 
		FROM 
		(
	        SELECT 
	        	WP_NO 
	        FROM 
	        	TWEAKPLACE
	        WHERE
	        	1=1
				AND PLACE1 = #{_parameter}
	        ORDER BY WP_NO DESC
		)
        WHERE
        	ROWNUM = 1
  	]]>
	</select>

	<insert id="insert" parameterType="weakPlaceVO">
	<![CDATA[
		INSERT
	    INTO TWEAKPLACE
	    (
	        PLACE1, WP_NO, NAME, LOCATION, COMMENTS, 
	    	FILENAME1, FILENAME2, WRITER, CREDATE, LOGDATE
	    )
	    VALUES
	    (
	      	#{place1}, #{wpNo}, #{name}, #{location}, #{comments},
	      	#{filename1}, #{filename2}, #{writer}, TO_CHAR(NOW(), 'YYYYMMDDHH24MISS'), NULL
	    )
	]]>
	</insert>

	<update id="update" parameterType="weakPlaceVO">
	<![CDATA[
		UPDATE 
			TWEAKPLACE
		SET 
			NAME = #{name},
			LOCATION = #{location},
			COMMENTS = #{comments}, 
			FILENAME1 = #{filename1},
			FILENAME2 = #{filename2}, 
			LOGDATE = TO_CHAR(NOW(), 'YYYYMMDDHH24MISS')
		WHERE 
			PLACE1 = #{place1}
			AND WP_NO = #{wpNo}
	]]>
	</update>

	<delete id="delete" parameterType="weakPlaceVO">
	<![CDATA[
		DELETE 
		FROM
			TWEAKPLACE
		WHERE 
			PLACE1 = #{place1}
			AND WP_NO = #{wpNo}
	]]>
	</delete>

</mapper>
