<?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.SaleMgrMgnMapper">

    <select id="selectPopSaleMgrMgnGridList" resultType="com.oqpo.api.entity.oper.SaleMgrMgntEntity">
        select /*selectPopSaleMgrMgnGridList*/
           sale_mgr_id, sale_mgr_nm, fn_get_telno(rprst_tel_no) as rprst_tel_no , rprst_emai
            , st_cd , fn_code_nm('ST_CD', st_cd) as st_nm
        from sale_mgr_mgnt
        where st_cd = 'S000'
        <if test="sSaleMgrNm != null and sSaleMgrNm != ''">
            and sale_mgr_nm like concat('%',#{sSaleMgrNm},'%')
        </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 sale_mgr_nm asc
                    </if>
                    <if test="gridRequest.sord == 'desc'.toString()">
                        order by sale_mgr_nm desc
                    </if>
                </if>
            </when>
            <otherwise>
                order by sale_mgr_nm asc
            </otherwise>
        </choose>
        <if test="gridRequest.pagingYn == true">
            limit #{gridRequest.gridFirst}, #{gridRequest.gridSize}
        </if>
    </select>

    <select id="selectPopSaleMgrMgnGridCnt" resultType="int">
        select /*selectPopSaleMgrMgnGridCnt*/
                count(*)
        from sale_mgr_mgnt
        where st_cd = 'S000'
        <if test="sSaleMgrNm != null and sSaleMgrNm != ''">
            and sale_mgr_nm like concat('%',#{sSaleMgrNm},'%')
        </if>
            </select>

    <select id="selectSaleMgrMgnGridList" resultType="com.oqpo.api.entity.oper.SaleMgrMgntEntity">
        select  /* selectSaleMgrMgnGridList */
                sale_mgr_id, sale_mgr_nm, st_cd , fn_code_nm('ST_CD', st_cd) as st_nm, sale_type, fn_code_nm('SALE_TYPE', sale_type) as sale_type_nm,
                date_format(reg_dt,'%Y.%m.%d') as reg_dt
        from sale_mgr_mgnt
        where 1=1
        <if test="fromDt != null and fromDt != '' and toDt != null and toDt != '' ">
            and   reg_dt between  #{fromDt} and #{toDt}
        </if>
        <if test="sStCd != null and sStCd != ''">
            and   st_cd = #{sStCd}
        </if>
        <if test="sSaleMgrNm != null and sSaleMgrNm != ''">
            and   sale_mgr_nm like concat('%',#{sSaleMgrNm},'%')
        </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 sale_mgr_nm asc
                    </if>
                    <if test="gridRequest.sord == 'desc'.toString()">
                        order by sale_mgr_nm desc
                    </if>
                </if>
            </when>
            <otherwise>
                order by sale_mgr_nm asc
            </otherwise>
        </choose>
        <if test="gridRequest.pagingYn == true">
            limit #{gridRequest.gridFirst}, #{gridRequest.gridSize}
        </if>
    </select>

    <select id="selectSaleMgrMgnGridCnt" resultType="int">
        select  /* selectSaleMgrMgnGridCnt */
                count(*)
        from sale_mgr_mgnt
        where 1=1
        <if test="fromDt != null and fromDt != '' and toDt != null and toDt != '' ">
            and   reg_dt between  #{fromDt} and #{toDt}
        </if>
        <if test="sStCd != null and sStCd != ''">
            and   st_cd = #{sStCd}
        </if>
        <if test="sSaleMgrNm != null and sSaleMgrNm != ''">
            and   sale_mgr_nm like concat('%',#{sSaleMgrNm},'%')
        </if>
    </select>


    <select id="selectSaleMgrIdDupYn" resultType="String">
        select  /* selectSaleMgrIdDupYn */
                case when count(*) <![CDATA[ > ]]> 0 then 'Y' else 'N' end as dup_yn
        from sale_mgr_mgnt
        where sale_mgr_id = #{saleMgrId}
    </select>


    <insert id="insertSaleMgrMgn" >
        insert into /* insertSaleMgrMgn */
            sale_mgr_mgnt
        ( sale_mgr_id, sale_mgr_nm, st_cd, sale_type, pswd
        , rprst_tel_no, rprst_fax_no, rprst_emai, zip_no, addr1
        , addr2,  lat_x, long_y,  reg_dt, reg_id, reg_tm, sys_reg_dttm
        , sys_reg_id, sys_chg_dttm, sys_chg_id )
        values
        ( #{entity.saleMgrId}, #{entity.saleMgrNm}, #{entity.stCd}, #{entity.saleType},null
        , #{entity.rprstTelNo}, #{entity.rprstFaxNo}, #{entity.rprstEmai}, #{entity.zipNo}, #{entity.addr1}
        , #{entity.addr2}, #{entity.latX}, #{entity.longY},date_format(now(),'%Y%m%d'), #{userId}, '000000', now()
        , #{userId}, now(), #{userId} )
    </insert>

    <update id="updateSaleMgrMgn">
        update sale_mgr_mgnt
        set sale_mgr_nm = #{entity.saleMgrNm}
          , st_cd = #{entity.stCd}
          , sale_type = #{entity.saleType}
          , rprst_tel_no = #{entity.rprstTelNo}
          , rprst_fax_no = #{entity.rprstFaxNo}
          , rprst_emai = #{entity.rprstEmai}
          , zip_no = #{entity.zipNo}
          , addr1 = #{entity.addr1}
          , addr2 = #{entity.addr2}
          , lat_x = #{entity.latX}
          , long_y = #{entity.longY}
          , sys_chg_dttm = NOW()
          , sys_chg_id = #{userId}
        where sale_mgr_id = #{entity.saleMgrId}
    </update>


    <select id="selectSaleMgrMgn" resultType="com.oqpo.api.entity.oper.SaleMgrMgntEntity">
        select sale_mgr_id, sale_mgr_nm, st_cd, fn_code_nm('ST_CD', st_cd) as st_nm, sale_type, fn_code_nm('SALE_TYPE', sale_type) as sale_type_nm
                , fn_get_telno(rprst_tel_no) as rprst_tel_no, fn_get_telno(rprst_fax_no) as rprst_fax_no, rprst_emai, zip_no, addr1
                , addr2, lat_x, long_y,  date_format(concat(reg_dt,reg_tm), '%y.%m.%d') as reg_dt, reg_id, date_format(concat(reg_dt,reg_tm), '%h:%i:%s') as reg_tm
                , date_format( sys_reg_dttm, '%y.%m.%d %h:%i:%s') as sys_reg_dttm  , sys_reg_id, date_format( sys_chg_dttm, '%y.%m.%d %h:%i:%s') as sys_chg_dttm, sys_chg_id
        from sale_mgr_mgnt
        where sale_mgr_id =#{saleMgrId}
    </select>

    <select id="selectSaleMgrInitPw" resultType="String">
        select rprst_tel_no
         from sale_mgr_mgnt
         where sale_mgr_id  = #{saleMgrId}
    </select>


</mapper>
