<?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.oqpo.api.mapper.oper.PopMapper">
    <select id="selectPopGridList" resultType="com.oqpo.api.entity.oper.PopEntity">
        select pop_no, pop_nm, fn_code_nm('VIEW_TARGET_CD', view_target_cd) as  view_target_nm
                , case when  date_format(now(),'%Y%m%d') <![CDATA[ >= ]]> pop_st_day and date_format(now(),'%Y%m%d') <![CDATA[ <= ]]> pop_ed_day then '공지중'
                when  date_format(now(),'%Y%m%d') <![CDATA[ < ]]> pop_st_day then '대기'
                when  date_format(now(),'%Y%m%d') <![CDATA[ > ]]> pop_ed_day then '공지종료' end as pop_stat_nm
                , date_format(pop_st_day ,'%Y.%m.%d') as pop_st_day
                , date_format(add_dt ,'%Y.%m.%d') as add_dt
                , pop_width, pop_height
        from   np_pop
        where  1 = 1
        <if test="fromDt != null and fromDt != '' and toDt != null and toDt != '' ">
            and pop_st_day  between #{fromDt} and #{toDt}
        </if>
        <if test="sPopNm != null and sPopNm != '' ">
            and pop_nm like concat('%', #{sPopNm}, '%')
        </if>

        <choose>
            <when test="gridRequest.sidx != null and gridRequest.sidx != ''">
                <if test="gridRequest.sidx == 'VIEW_NUM'.toString()">
                    <if test="gridRequest.sord == 'asc'.toString()">
                        order by pop_no asc
                    </if>
                    <if test="gridRequest.sord == 'desc'.toString()">
                        order by pop_no desc
                    </if>
                </if>
            </when>
            <otherwise>
                order by pop_no asc
            </otherwise>
        </choose>
        <if test="gridRequest.pagingYn == true">
            limit #{gridRequest.gridFirst}, #{gridRequest.gridSize}
        </if>
    </select>

    <select id="selectPopGridCnt" resultType="int">
        select count(*) as cnt
        from   np_pop
        where  1 = 1
        <if test="fromDt != null and fromDt != '' and toDt != null and toDt != '' ">
            and pop_st_day  between #{fromDt} and #{toDt}
        </if>
        <if test="sPopNm != null and sPopNm != '' ">
            and pop_nm like concat('%', #{sPopNm}, '%')
        </if>
    </select>

    <select id="selectPopInfo" resultType="com.oqpo.api.entity.oper.PopEntity">
        select np.pop_no, np.pop_nm
              , date_format(np.pop_st_day ,'%Y.%m.%d') as pop_st_day, date_format(np.pop_ed_day ,'%Y.%m.%d') as pop_ed_day
              , np.del_yn, np.pop_type_cd,  fn_code_nm('POP_TYPE_CD', np.pop_type_cd) as  pop_type_nm
              , np.pop_desc, np.pop_file_no
              , np.view_target_cd, fn_code_nm('VIEW_TARGET_CD', np.view_target_cd) as  view_target_nm
              , np.menu_no, fn_menu_path(np.menu_no) AS menu_path
              , nfile.file_path 'file.file_path'
              , np.pop_width, np.pop_height
        from np_pop np
        left outer join np_file nfile on nfile.file_no = np.pop_file_no
        where pop_no = #{popNo}
    </select>

    <insert id="insertPop" >
        insert into np_pop
        (pop_no, pop_nm, pop_st_day, pop_ed_day, del_yn, pop_type_cd, pop_desc, pop_file_no, view_target_cd, menu_no, pop_width, pop_height, add_dt, add_id, chg_dt, chg_id)
        values
        (#{entity.popNo}, #{entity.popNm}, #{entity.popStDay}, #{entity.popEdDay}, #{entity.delYn}, #{entity.popTypeCd}, #{entity.popDesc}, #{entity.popFileNo}, #{entity.viewTargetCd}, #{entity.menuNo},  #{entity.popWidth},  #{entity.popHeight}, now(), #{userId}, now(), #{userId})
    </insert>

    <update id="updatePop">
         update np_pop
        set	chg_dt = now(),
            chg_id = #{userId},
            pop_nm = #{entity.popNm},
            pop_st_day = #{entity.popStDay},
            pop_ed_day = #{entity.popEdDay},
            del_yn = #{entity.delYn},
            pop_type_cd = #{entity.popTypeCd},
            pop_desc = #{entity.popDesc},
            pop_file_no = #{entity.popFileNo},
            view_target_cd = #{entity.viewTargetCd},
            menu_no = #{entity.menuNo},
            pop_width =  #{entity.popWidth},
            pop_height =  #{entity.popHeight}
        where pop_no = #{entity.popNo}
    </update>

    <select id="selectOpenPop" resultType="com.oqpo.api.entity.oper.PopEntity">
        select np.pop_no, np.pop_nm, np.view_target_cd, nm.menu_id 'menu.menu_id', np.pop_width, np.pop_height
        from   np_pop np
        left outer join np_sys_menu nm on np.menu_no = nm.menu_no
        where date_format(now() ,'%Y%m%d') between pop_st_day and  pop_ed_day
        <if test="sViewTargetCd != null and sViewTargetCd != '' ">
        and np.view_target_cd = #{sViewTargetCd}
        </if>
        and   np.del_yn = 'N'
    </select>
</mapper>
