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

	<!-- 목표관리 -->
	
	<resultMap id="ReportGoalMap" type="reportGoalVO">
		<result property="goYear" column="go_Year"/>
		<result property="goMonth" column="go_Month"/>
		<result property="goPlace1" column="go_place1"/>
		<result property="goId" column="go_id"/>
		<result property="goGoal1" column="go_Goal1"/>
		<result property="goGoal2" column="go_Goal1"/>
		<result property="goGoal3" column="go_Goal1"/>
		<result property="goGoal4" column="go_Goal1"/>
		<result property="goTotal" column="go_Total"/>
		<result property="rn" column="rn"/>
		<result property="name" column="name"/>
	</resultMap>

	<select id="ReportGoal.selectList" parameterType="reportSearchVO" resultMap="ReportGoalMap">
		SELECT 
			T.*, ROWNUM AS RN
		FROM
			(
				SELECT 
					A.NAME, 
					A.USERID AS go_id, 
					NVL(G.GO_GOAL1, 0) AS goGoal1, 
					NVL(G.GO_GOAL2, 0) AS goGoal2, 
					NVL(G.GO_GOAL3, 0) AS goGoal3, 
					NVL(G.GO_GOAL4, 0) AS goGoal4,
					NVL(G.GO_TOTAL, 0) AS goTotal
				FROM 
					(SELECT * FROM TAGENT WHERE PLACE1 = #{placeGroup}) A 
						LEFT OUTER JOIN  
					(SELECT DISTINCT * FROM RPT_AGENT_GOAL WHERE GO_MONTH = #{month} AND GO_YEAR = #{year}) G
						ON G.GO_ID = A.USERID
				ORDER BY 
					POSITION DESC, PLACE2, PLACE3, DUTIES, NAME
			) T
	</select>
	
	<select id="ReportGoal.select" parameterType="reportGoalVO" resultMap="ReportGoalMap">
		SELECT
			*
		FROM
			RPT_AGENT_GOAL
		WHERE
			GO_ID = #{goId}
	</select>
	
	<insert id="ReportGoal.insert" parameterType="reportGoalVO">
		INSERT INTO 
			RPT_AGENT_GOAL
		VALUES 
			(#{goYear}, #{goMonth}, #{goPlace1}, #{goId}, #{goGoal1}, #{goGoal2}, #{goGoal3}, #{goGoal4}, #{goTotal})
	</insert>
	
	<update id="ReportGoal.update" parameterType="reportGoalVO">
		UPDATE 
			RPT_AGENT_GOAL
		SET
			GO_YEAR = #{goYear},
			GO_MONTH = #{goMonth},
			GO_GOAL1 = #{goGoal1},
			GO_GOAL2 = #{goGoal2},
			GO_GOAL3 = #{goGoal3},
			GO_GOAL4 = #{goGoal4},
			GO_TOTAL = #{goTotal}
		WHERE
			GO_ID = #{goId}
	</update>
	
	<select id="ReportGoal.getStats" parameterType="reportSearchVO" resultType="hashMap">
		SELECT
			ROWNUM AS RN, T.*
		FROM
			(
				SELECT 
					NAME, 
					NVL(GO_GOAL1, 0) AS GOAL1, NVL(GO_GOAL2, 0) AS GOAL2, NVL(GO_GOAL3, 0) AS GOAL3, NVL(GO_GOAL4, 0) AS GOAL4, 
					NVL(GO_GOAL1, 0) + NVL(GO_GOAL2, 0) + NVL(GO_GOAL3, 0) + NVL(GO_GOAL4, 0) AS GOAL_TOTAL,
					(SELECT COUNT(*) FROM RPT_INFO WHERE IN_COMPLETE = 1 AND IN_USERID = A.USERID AND IN_INFO_YEAR = G.GO_YEAR AND IN_INFO_MONTH = G.GO_MONTH AND IN_CATEGORY4 = '01') AS RESULT1,
					(SELECT COUNT(*) FROM RPT_INFO WHERE IN_COMPLETE = 1 AND IN_USERID = A.USERID AND IN_INFO_YEAR = G.GO_YEAR AND IN_INFO_MONTH = G.GO_MONTH AND IN_CATEGORY4 = '02') AS RESULT2,
					(SELECT COUNT(*) FROM RPT_INFO WHERE IN_COMPLETE = 1 AND IN_USERID = A.USERID AND IN_INFO_YEAR = G.GO_YEAR AND IN_INFO_MONTH = G.GO_MONTH AND IN_CATEGORY4 IN ('03', '04', '05')) AS RESULT3,
					(SELECT COUNT(*) FROM RPT_INFO WHERE IN_COMPLETE = 1 AND IN_USERID = A.USERID AND IN_INFO_YEAR = G.GO_YEAR AND IN_INFO_MONTH = G.GO_MONTH AND IN_CATEGORY4 = '06') AS RESULT4, 
					(SELECT COUNT(*) FROM RPT_INFO WHERE IN_COMPLETE = 1 AND IN_USERID = A.USERID AND IN_INFO_YEAR = G.GO_YEAR AND IN_INFO_MONTH = G.GO_MONTH) AS RESULT_TOTAL
				FROM 
					(SELECT * FROM TAGENT WHERE PLACE1 = #{placeGroup}) A 
						LEFT OUTER JOIN 
					(SELECT DISTINCT * FROM RPT_AGENT_GOAL WHERE GO_YEAR = #{year} AND GO_MONTH = #{month}) G 
						ON G.GO_ID = A.USERID
				ORDER BY
					POSITION DESC, PLACE2, PLACE3, DUTIES, NAME
			) T
	</select>
	
	<select id="ReportGoal.getStatsPlace" parameterType="reportSearchVO" resultType="hashMap">
		SELECT
			ROWNUM AS RN, T.*
		FROM
			(
				SELECT 
					CODE2 AS CODE, CODENM AS NAME, 
					SUM(NVL(GO_GOAL1, 0)) AS GOAL1, SUM(NVL(GO_GOAL2, 0)) AS GOAL2, SUM(NVL(GO_GOAL3, 0)) AS GOAL3, SUM(NVL(GO_GOAL4, 0)) AS GOAL4, 
					SUM(NVL(GO_GOAL1, 0)) + SUM(NVL(GO_GOAL2, 0)) + SUM(NVL(GO_GOAL3, 0)) + SUM(NVL(GO_GOAL4, 0)) AS GOAL_TOTAL,
					(SELECT COUNT(*) FROM RPT_INFO WHERE IN_COMPLETE = 1 AND IN_INFO_YEAR = G.GO_YEAR AND IN_INFO_MONTH = G.GO_MONTH AND IN_CATEGORY4 = '01') AS RESULT1,
					(SELECT COUNT(*) FROM RPT_INFO WHERE IN_COMPLETE = 1 AND IN_INFO_YEAR = G.GO_YEAR AND IN_INFO_MONTH = G.GO_MONTH AND IN_CATEGORY4 = '02') AS RESULT2,
					(SELECT COUNT(*) FROM RPT_INFO WHERE IN_COMPLETE = 1 AND IN_INFO_YEAR = G.GO_YEAR AND IN_INFO_MONTH = G.GO_MONTH AND IN_CATEGORY4 IN ('03', '04', '05')) AS RESULT3,
					(SELECT COUNT(*) FROM RPT_INFO WHERE IN_COMPLETE = 1 AND IN_INFO_YEAR = G.GO_YEAR AND IN_INFO_MONTH = G.GO_MONTH AND IN_CATEGORY4 = '06') AS RESULT4,
					(SELECT COUNT(*) FROM RPT_INFO WHERE IN_COMPLETE = 1 AND IN_INFO_YEAR = G.GO_YEAR AND IN_INFO_MONTH = G.GO_MONTH) AS RESULT_TOTAL
				FROM 
					(SELECT * FROM TCODE WHERE CODE1 = 'C001') P
					LEFT OUTER JOIN 
					(SELECT DISTINCT * FROM RPT_AGENT_GOAL WHERE GO_YEAR = #{year} AND GO_MONTH = #{month}) G
					ON G.GO_PLACE1 = P.CODE2
				GROUP BY 
					CODE2, CODENM, GO_YEAR, GO_MONTH
				ORDER BY
					CODE2
			) T
	</select>
</mapper>