<?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="com.dbnt.faisp.fpiMgt.affair.mapper.AffairMapper">
  <sql id="selectAffairBoardWhere">
    <where>
      <if test='affairCategory != null and affairCategory != ""'>
        and a.affair_category = #{affairCategory}
      </if>
      <if test='wrtUserSeq != null and wrtUserSeq != ""'>
        and a.wrt_user_seq = #{wrtUserSeq}
      </if>
      <if test='wrtUserNm != null and wrtUserNm != ""'>
        and a.wrt_user_nm like '%'||#{wrtUserNm}||'%'
      </if>
      <if test='wrtOrgan != null and wrtOrgan != ""'>
        and a.wrt_organ = #{wrtOrgan}
      </if>
      <if test='affairType1 != null and affairType1 != ""'>
        and a.affair_type1 = #{affairType1}
      </if>
      <if test='affairType2 != null and affairType2 != ""'>
        and a.affair_type2 = #{affairType2}
      </if>
      <if test='affairType3 != null and affairType3 != ""'>
        and a.affair_type3 = #{affairType3}
      </if>
      <if test='affairType4 != null and affairType4 != ""'>
        and a.affair_type4 = #{affairType4}
      </if>
      <if test='title != null and title != ""'>
        and a.title like '%'||#{title}||'%'
      </if>
      <if test='affairStatus != null and affairStatus != ""'>
        and c.affair_status = #{affairStatus}
      </if>
      <if test='startDate != null and startDate != ""'>
        and a.wrt_dt >= #{startDate}::date
      </if>
      <if test='endDate != null and endDate != ""'>
        and a.wrt_dt &lt;= #{endDate}::date+1
      </if>
      <if test='ratingOrgan != null and ratingOrgan != ""'>
        and c.rating_organ = #{ratingOrgan}
      </if>
      <if test="downOrganCdList != null">
        and a.wrt_organ in
        <foreach collection="downOrganCdList" item="organCd" separator="," open="(" close=")">
          #{organCd}
        </foreach>
      </if>
      <if test='userType != null and userType != ""'>
        <if test='userType == "normalStayList"'>
          and c.affair_status in ('DST002', 'DST004')
        </if>
        <if test='userType == "normalCommitList"'>
          and c.affair_status in ('DST003', 'DST005', 'DST006')
        </if>
        <if test='userType == "sectionCommitList"'>
          and c.affair_status in ('DST003', 'DST004', 'DST005', 'DST006')
        </if>
        <if test='userType == "headCommitList"'>
          and c.affair_status in ('DST003', 'DST005', 'DST006')
        </if>
        <if test='userType == "managerStayList"'>
          and c.affair_status in ('DST002', 'DST004')
        </if>
        <if test='userType == "managerCommitList"'>
          and c.affair_status in ('DST003', 'DST005', 'DST006')
        </if>
      </if>
    </where>
  </sql>
  <select id="selectAffairBoardList" resultType="AffairBoard" parameterType="AffairBoard">
    select a.affair_key,
           a.affair_type1,
           a.affair_type2,
           a.affair_type3,
           a.affair_type4,
           a.title,
           a.wrt_organ,
           a.wrt_part,
           a.wrt_user_nm,
           a.wrt_user_seq,
           a.wrt_dt,
           b.fileCnt,
           c.affair_status,
           c.affair_rate,
           c.organ_up
    from affair_board a
        left outer join (select affair_key,
                                   count(file_seq) as fileCnt
                            from affair_file
                            group by affair_key) b
            on a.affair_key = b.affair_key
        inner join affair_rating c
            on a.affair_key = c.affair_key
    <include refid="selectAffairBoardWhere"></include>
    order by a.affair_key desc
    limit #{rowCnt} offset #{firstIndex}
  </select>
  <select id="selectAffairBoardCnt" resultType="int" parameterType="AffairBoard">
    select count(*)
    from affair_board a
        inner join affair_rating c
            on a.affair_key = c.affair_key
    <include refid="selectAffairBoardWhere"></include>
  </select>
  <select id="selectHashTags" resultType="string" parameterType="int">
    select aa.hashTags
    from (select a.affair_key,
                 array_to_string(array_agg(b.tag_nm), ' ') as hashTags
          from hash_tag_link a
            inner join hash_tag b on a.tag_key = b.tag_key
          where a.affair_key = #{affairKey}
          group by a.affair_key) aa
  </select>
  
  <select id="selectType1List" resultType="TypeStatistics" parameterType="TypeStatistics">
  	select item_cd as affairType,
		   item_value
	from code_mgt
	where category_cd = 'DC01'
	<choose>
		<when test='category1 != null and category1 != ""'>
		and item_cd in
		<foreach collection="category1" item="item" index="index" separator="," open="(" close=")">
	       #{item}
	    </foreach>
		</when>
		<otherwise>
		and item_cd not in (select item_cd from code_mgt where category_cd = 'DC01')
		</otherwise>
	</choose>
	order by affairType asc
  </select>
  
  <select id="selectType2List" resultType="TypeStatistics" parameterType="TypeStatistics">
  	select item_cd as affairType,
		   item_value
	from code_mgt
	where category_cd = 'DC02'
	<choose>
		<when test='category2 != null and category2 != ""'>
		and item_cd in
		<foreach collection="category2" item="item" index="index" separator="," open="(" close=")">
	       #{item}
	    </foreach>
		</when>
		<otherwise>
		and item_cd not in (select item_cd from code_mgt where category_cd = 'DC02')
		</otherwise>
	</choose>
	order by affairType asc
  </select>
  
  <select id="selectType3List" resultType="TypeStatistics" parameterType="TypeStatistics">
  	select item_cd as affairType,
		   item_value
	from code_mgt
	where category_cd = 'DC03'
	<choose>
		<when test='category3 != null and category3 != ""'>
		and item_cd in
		<foreach collection="category3" item="item" index="index" separator="," open="(" close=")">
	       #{item}
	    </foreach>
		</when>
		<otherwise>
		and item_cd not in (select item_cd from code_mgt where category_cd = 'DC03')
		</otherwise>
	</choose>
	order by affairType asc
  </select>
  
  <select id="selectType4List" resultType="TypeStatistics" parameterType="TypeStatistics">
  	select item_cd as affairType,
		   item_value
	from code_mgt
	where category_cd = 'DC04'
	<choose>
		<when test='category4 != null and category4 != ""'>
		and item_cd in
		<foreach collection="category4" item="item" index="index" separator="," open="(" close=")">
	       #{item}
	    </foreach>
		</when>
		<otherwise>
		and item_cd not in (select item_cd from code_mgt where category_cd = 'DC04')
		</otherwise>
	</choose>
	order by affairType asc
  </select>
  
  <select id="selectStatusTotal" resultType="TypeStatistics" parameterType="TypeStatistics">
  	select item_cd as wrt_organ,
  		   item_value,
	       coalesce(cnt,0) as cnt
	from(
		select item_cd,
			   item_value
		from code_mgt
		where category_cd = 'OG'
		and use_chk = 'T'
		<choose>
			<when test='organList != null and organList != ""'>
			and item_cd in
			<foreach collection="organList" item="item" index="index" separator="," open="(" close=")">
	        #{item}
	        </foreach>
			</when>
			<otherwise>
			and item_cd not in (select item_cd from code_mgt where category_cd = 'OG' and use_chk = 'T')
			</otherwise>
		</choose>
		order by item_cd asc) a left outer join
		(select wrt_organ,
		   count(*) as cnt
		from affair_board
		<trim prefix="WHERE" prefixOverrides="AND |OR ">
			<if test='category1 != null and category1 != ""'>
				or affair_type1 in
				<foreach collection="category1" item="item" index="index" separator="," open="(" close=")">
		        #{item}
		        </foreach>
			</if>
			<if test='category2 != null and category2 != ""'>
				or affair_type2 in
				<foreach collection="category2" item="item" index="index" separator="," open="(" close=")">
		        #{item}
		        </foreach>
			</if>
			<if test='category3 != null and category3 != ""'>
				or affair_type3 in
				<foreach collection="category3" item="item" index="index" separator="," open="(" close=")">
		        #{item}
		        </foreach>
			</if>
			<if test='category4 != null and category4 != ""'>
				or affair_type4 in
				<foreach collection="category4" item="item" index="index" separator="," open="(" close=")">
		        #{item}
		        </foreach>
			</if>
		</trim>
		group by wrt_organ)b on 
		a.item_cd = b.wrt_organ
		order by wrt_organ asc
  </select>
  
  <select id="selecType1ListCnt" resultType="TypeStatistics" parameterType="TypeStatistics">
  	select b.item_cd as wrt_organ ,
		   b.item_value,
		   a.item_cd as affair_type,
		   coalesce(cnt,0) as cnt
	from
		(select item_cd,
			    item_value
		from code_mgt
		where category_cd = 'DC01'
		<choose>
			<when test='category1 != null and category1 != ""'>
			and item_cd in
			<foreach collection="category1" item="item" index="index" separator="," open="(" close=")">
		       #{item}
		    </foreach>
			</when>
			<otherwise>
			and item_cd not in (select item_cd from code_mgt where category_cd = 'DC01')
			</otherwise>
		</choose>
		)a left join 
		(select item_cd,
				item_value
		from code_mgt
		where category_cd = 'OG'
		and use_chk = 'T'
		<if test='organList != null and organList != ""'>
		and item_cd in
		<foreach collection="organList" item="item" index="index" separator="," open="(" close=")">
        #{item}
        </foreach>
		</if>
		order by item_cd asc) b on 1=1
		left outer join 
		(select wrt_organ,
			   affair_type1 as affair_type,
			   count(*) as cnt
		from affair_board
		group by wrt_organ,affair_type1) c 
		on a.item_cd = c.affair_type and b.item_cd = c.wrt_organ
	order by wrt_organ,affair_type asc
  </select>
  
  <select id="selecType2ListCnt" resultType="TypeStatistics" parameterType="TypeStatistics">
  	select b.item_cd as wrt_organ ,
		   b.item_value,
		   a.item_cd as affair_type,
		   coalesce(cnt,0) as cnt
	from
		(select item_cd,
			    item_value
		from code_mgt
		where category_cd = 'DC02'
		<choose>
		<when test='category2 != null and category2 != ""'>
		and item_cd in
		<foreach collection="category2" item="item" index="index" separator="," open="(" close=")">
	       #{item}
	    </foreach>
		</when>
		<otherwise>
		and item_cd not in (select item_cd from code_mgt where category_cd = 'DC02')
		</otherwise>
	</choose>
		)a left join 
		(select item_cd,
				item_value
		from code_mgt
		where category_cd = 'OG'
		and use_chk = 'T'
		<if test='organList != null and organList != ""'>
		and item_cd in
		<foreach collection="organList" item="item" index="index" separator="," open="(" close=")">
        #{item}
        </foreach>
		</if>
		order by item_cd asc) b on 1=1
		left outer join 
		(select wrt_organ,
			   affair_type2 as affair_type,
			   count(*) as cnt
		from affair_board
		group by wrt_organ,affair_type2) c 
		on a.item_cd = c.affair_type and b.item_cd = c.wrt_organ
	order by wrt_organ,affair_type asc
  </select>
  
  <select id="selecType3ListCnt" resultType="TypeStatistics" parameterType="TypeStatistics">
  	select b.item_cd as wrt_organ ,
		   b.item_value,
		   a.item_cd as affair_type,
		   coalesce(cnt,0) as cnt
	from
		(select item_cd,
			    item_value
		from code_mgt
		where category_cd = 'DC03'
		<choose>
		<when test='category3 != null and category3 != ""'>
		and item_cd in
		<foreach collection="category3" item="item" index="index" separator="," open="(" close=")">
	       #{item}
	    </foreach>
		</when>
		<otherwise>
		and item_cd not in (select item_cd from code_mgt where category_cd = 'DC03')
		</otherwise>
	</choose>
		)a left join 
		(select item_cd,
				item_value
		from code_mgt
		where category_cd = 'OG'
		and use_chk = 'T'
		<if test='organList != null and organList != ""'>
		and item_cd in
		<foreach collection="organList" item="item" index="index" separator="," open="(" close=")">
        #{item}
        </foreach>
		</if>
		order by item_cd asc) b on 1=1
		left outer join 
		(select wrt_organ,
			   affair_type3 as affair_type,
			   count(*) as cnt
		from affair_board
		group by wrt_organ,affair_type3) c 
		on a.item_cd = c.affair_type and b.item_cd = c.wrt_organ
	order by wrt_organ,affair_type asc
  </select>
  
  <select id="selecType4ListCnt" resultType="TypeStatistics" parameterType="TypeStatistics">
  	select b.item_cd as wrt_organ ,
		   b.item_value,
		   a.item_cd as affair_type,
		   coalesce(cnt,0) as cnt
	from
		(select item_cd,
			    item_value
		from code_mgt
		where category_cd = 'DC04'
		<choose>
		<when test='category4 != null and category4 != ""'>
		and item_cd in
		<foreach collection="category4" item="item" index="index" separator="," open="(" close=")">
	       #{item}
	    </foreach>
		</when>
		<otherwise>
		and item_cd not in (select item_cd from code_mgt where category_cd = 'DC04')
		</otherwise>
	</choose>
		)a left join 
		(select item_cd,
				item_value
		from code_mgt
		where category_cd = 'OG'
		and use_chk = 'T'
		<if test='organList != null and organList != ""'>
		and item_cd in
		<foreach collection="organList" item="item" index="index" separator="," open="(" close=")">
        #{item}
        </foreach>
		</if>
		order by item_cd asc) b on 1=1
		left outer join 
		(select wrt_organ,
			   affair_type4 as affair_type,
			   count(*) as cnt
		from affair_board
		group by wrt_organ,affair_type4) c 
		on a.item_cd = c.affair_type and b.item_cd = c.wrt_organ
	order by wrt_organ,affair_type asc
  </select>
</mapper>