<?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.AfflMapper">
    <select id="selectTargetAfflGridList" resultType="com.oqpo.api.entity.oper.AfflEntity">
        select /*selectTargetAfflGridList*/
            affl_shop_id, affl_shop_nm, affl_abbr
        from affl_shop_base_info
        where st_cd = 'S000'
        <if test="authTpCd == '20' ">
        and   affl_shop_id in (select affl_shop_id from sale_mgr_mgnt where sale_mgr_id = #{userId} )
        </if>
        <if test="authTpCd == '30' or authTpCd == '40' or authTpCd == '50' or authTpCd == '60' ">
        and   affl_shop_id = (select affl_shop_id from np_user_info where user_id = #{userId} )
        </if>
    </select>


    <select id="selectPopAfflGridList" resultType="com.oqpo.api.entity.oper.AfflEntity">
        select /*selectTargetAfflGridList*/
                affl_shop_id, affl_shop_nm, affl_abbr
        from affl_shop_base_info
        where st_cd = 'S000'
        <if test="authTpCd == '20' ">
            and   affl_shop_id in (select affl_shop_id from sale_mgr_mgnt where sale_mgr_id = #{userId} )
        </if>
        <if test="authTpCd == '30' or authTpCd == '40' or authTpCd == '50' or authTpCd == '60' ">
            and   affl_shop_id = (select affl_shop_id from np_user_info where user_id = #{userId} )
        </if>
        <if test="afflShopNm != null and afflShopNm != ''">
            and affl_shop_nm like concat('%',#{afflShopNm},'%')
        </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 affl_shop_nm asc
                    </if>
                    <if test="gridRequest.sord == 'desc'.toString()">
                        order by affl_shop_nm desc
                    </if>
                </if>
            </when>
            <otherwise>
                order by affl_shop_nm asc
            </otherwise>
        </choose>
        <if test="gridRequest.pagingYn == true">
            limit #{gridRequest.gridFirst}, #{gridRequest.gridSize}
        </if>
    </select>

    <select id="selectPopAfflGridCnt" resultType="int">
        select /*selectPopAfflGridCnt*/
                count(*)
        from affl_shop_base_info
        where st_cd = 'S000'
        <if test="authTpCd == '20' ">
            and   affl_shop_id in (select affl_shop_id from sale_mgr_mgnt where sale_mgr_id = #{userId} )
        </if>
        <if test="authTpCd == '30' or authTpCd == '40' or authTpCd == '50' or authTpCd == '60' ">
            and   affl_shop_id = (select affl_shop_id from np_user_info where user_id = #{userId} )
        </if>
        <if test="afflShopNm != null and afflShopNm != ''">
            and affl_shop_nm like concat('%',#{afflShopNm},'%')
        </if>
    </select>

    <select id="selectAfflShopGridList" resultType="com.oqpo.api.entity.oper.AfflEntity">
        select  /* selectAfflShopGridList */
        affl_shop_id, affl_shop_nm, corp_type, fn_code_nm('CORP_TYPE', corp_type) as corp_type_nm, st_cd, fn_code_nm('ST_CD', st_cd) as st_nm,  owner_nm
        , fn_get_telno(rprst_tel_no) as rprst_tel_no, concat(date_format(reg_dt,'%Y.%m.%d'), ' ~ ', date_format(reg_dt,'%Y.%m.%d')) as join_dt
        from affl_shop_base_info
        where 1=1
        and   st_cd = 'S000'
        <if test="fromDt != null and fromDt != '' and toDt != null and toDt != '' ">
            and   join_stt_dt between  #{fromDt} and #{toDt}
        </if>
        <if test="sStCd != null and sStCd != ''">
            and   st_cd = #{sStCd}
        </if>
        <if test="sAfflShopNm != null and sAfflShopNm != ''">
            and   affl_shop_nm like concat('%',#{sAfflShopNm},'%')
        </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 affl_shop_nm asc
                    </if>
                    <if test="gridRequest.sord == 'desc'.toString()">
                        order by affl_shop_nm desc
                    </if>
                </if>
            </when>
            <otherwise>
                order by affl_shop_nm asc
            </otherwise>
        </choose>
        <if test="gridRequest.pagingYn == true">
            limit #{gridRequest.gridFirst}, #{gridRequest.gridSize}
        </if>
    </select>


    <select id="selectAfflShopGridCnt" resultType="int">
        select  /* selectAfflShopGridCnt */
        count(*) as cnt
        from affl_shop_base_info
        where 1=1
        and   st_cd = 'S000'
        <if test="fromDt != null and fromDt != '' and toDt != null and toDt != '' ">
            and   join_stt_dt between  #{fromDt} and #{toDt}
        </if>
        <if test="sStCd != null and sStCd != ''">
            and   st_cd = #{sStCd}
        </if>
        <if test="sAfflShopNm != null and sAfflShopNm != ''">
            and   affl_shop_nm like concat('%',#{sAfflShopNm},'%')
        </if>
    </select>


    <select id="selectAfflAbbrDupYn" resultType="String">
        select  /* selectAfflShopBaseIdDupYn */
                case when count(*) <![CDATA[ > ]]> 0 then 'Y' else 'N' end as dup_yn
        from affl_shop_base_info
        where affl_abbr in ('AFS','BRD','STR',#{afflAbbr})
    </select>

    <insert id="insertAfflShopBaseInfo" >
        insert into affl_shop_base_info  /* insertAfflShopBaseInfo */
            ( affl_shop_id , affl_shop_nm, st_cd, affl_abbr, bsns_reg_no, corp_reg_no, auth_type, bsns_cndt, ctgr_bsns, owner_nm,
             owner_tel_no, rprst_fax_no, rprst_emai, zip_no, addr1, addr2, rprst_tel_no, lat_x, long_y, reg_dt, reg_tm, reg_id, join_stt_dt, join_end_dt,
             cmpny_logo_file_no, del_yn, corp_type, sys_reg_dttm, sys_reg_id, sys_chg_dttm, sys_chg_id )
        values
           (#{entity.afflShopId}, #{entity.afflShopNm}, #{entity.stCd}, #{entity.afflAbbr},  #{entity.bsnsRegNo}, #{entity.corpRegNo}, #{entity.authType}, #{entity.bsnsCndt}, #{entity.ctgrBsns}, #{entity.ownerNm},
            #{entity.ownerTelNo}, #{entity.rprstFaxNo}, #{entity.rprstEmai}, #{entity.zipNo}, #{entity.addr1}, #{entity.addr2}, #{entity.rprstTelNo}, #{entity.latX}, #{entity.longY}, date_format(now(),'%Y%m%d'), '000000', #{userId}, #{entity.joinSttDt}, #{entity.joinEndDt},
            #{entity.cmpnyLogoFileNo}, 'N', #{entity.corpType}, now(), #{userId}, now(), #{userId} )
    </insert>

    <update id="updateAfflShopBaseInfo">
        update affl_shop_base_info  /* updateAfflShopBaseInfo */
        set
           affl_shop_nm = #{entity.afflShopNm  }
           ,st_cd = #{entity.stCd }
           ,bsns_reg_no = #{entity.bsnsRegNo }
           ,corp_reg_no = #{entity.corpRegNo }
           ,auth_type = #{entity.authType }
           ,bsns_cndt = #{entity.bsnsCndt }
           ,ctgr_bsns = #{entity.ctgrBsns }
           ,owner_nm = #{entity.ownerNm }
           ,owner_tel_no = #{entity.ownerTelNo }
           ,rprst_fax_no = #{entity.rprstFaxNo }
           ,rprst_emai = #{entity.rprstEmai }
           ,zip_no = #{entity.zipNo }
           ,addr1 = #{entity.addr1 }
           ,addr2 = #{entity.addr2 }
           ,rprst_tel_no = #{entity.rprstTelNo }
           ,lat_x = #{entity.latX }
           ,long_y = #{entity.longY }
           ,join_stt_dt = #{entity.joinSttDt }
           ,join_end_dt = #{entity.joinEndDt }
           ,cmpny_logo_file_no = #{entity.cmpnyLogoFileNo }
           ,corp_type = #{entity.corpType }
           ,sys_chg_dttm = now()
           ,sys_chg_id = #{userId }
        where affl_shop_id  = #{entity.afflShopId }
    </update>

    <insert id="insertAfflSaleInfo" >
        insert into /** insertAfflSaleInfo */ affl_sale_info
        (affl_shop_id, sale_mgr_id, sys_reg_dttm, sys_reg_id, sys_chg_dttm, sys_chg_id)
        values
        <foreach collection="gridInsertData" item="item" separator=",">
            ( #{item.afflShopId},#{item.saleMgrId}, now(), #{userId}, now(), #{userId})
        </foreach>
    </insert>

    <update id="deleteUserAuth">
        update affl_sale_info
        set    del_yn = 'Y'
        where  affl_shop_id = #{afflShopId}
        and    sale_mgr_id   in
        <foreach collection="gridDeleteData" item="item" separator="," open="(" close=")">
            #{item.saleMgrId}
        </foreach>
    </update>

    <select id="selectAfflShopBaseInfo" resultType="com.oqpo.api.entity.oper.AfflEntity">
        select
               affl_shop_id , affl_shop_nm, st_cd, fn_code_nm('ST_CD',st_cd) as st_nm
               , affl_abbr, fn_get_regno(1,bsns_reg_no) as bsns_reg_no, fn_get_regno(2,corp_reg_no) as corp_reg_no
               , auth_type, fn_code_nm('AUTH_TYPE',auth_type) as auth_type_nm, bsns_cndt, ctgr_bsns, owner_nm
               , fn_get_telno(owner_tel_no) as owner_tel_no, fn_get_telno(rprst_fax_no) as rprst_fax_no, rprst_emai, zip_no, addr1, addr2, fn_get_telno(rprst_tel_no) as rprst_tel_no, lat_x, long_y
                , date_format(reg_dt,'%Y.%m.%d') as reg_dt, reg_id, date_format(join_stt_dt,'%Y.%m.%d') as join_stt_dt,  date_format(join_end_dt,'%Y.%m.%d') as join_end_dt
               , cmpny_logo_file_no, del_yn, corp_type,  fn_code_nm('CORP_TYPE',corp_type) as corp_type_nm, sys_reg_dttm, sys_reg_id, sys_chg_dttm, sys_chg_id
         from affl_shop_base_info
         where affl_shop_id  = #{afflShopId}
    </select>

    <select id="selectAfflSaleTargetList" resultType="com.oqpo.api.entity.oper.AfflSaleInfoEntity">
        select sinfo.affl_shop_id , sinfo.sale_mgr_id, mgrm.sale_mgr_nm  as 'saleMgrInfo.sale_mgr_nm', mgrm.rprst_emai as 'saleMgrInfo.rprst_emai'
        ,fn_get_telno(mgrm.rprst_tel_no)   as 'saleMgrInfo.rprst_tel_no'
        , mgrm.st_cd as 'saleMgrInfo.st_cd', fn_code_nm('ST_CD',mgrm.st_cd) as 'saleMgrInfo.st_nm'
        from affl_sale_info  sinfo
        join sale_mgr_mgnt mgrm on sinfo.sale_mgr_id = mgrm.sale_mgr_id and mgrm.st_cd <![CDATA[ <> ]]> 'S001'
        where sinfo.del_yn = 'N'
        and   sinfo.affl_shop_id = #{afflShopId}
    </select>

    <select id="selectAfflShopInitPw" resultType="String">
        select bsns_reg_no
         from affl_shop_base_info
         where affl_shop_id  = #{afflShopId}
    </select>



</mapper>
