<?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="cn.ey.biz.module.system.dal.mapper.codeblock.CodeBlockMapper">
    <resultMap id="BaseResultMap" type="cn.ey.biz.module.system.dal.dataobject.codeblock.CodeBlockDO">
        <result column="code_block_id" jdbcType="BIGINT" property="codeBlockId"/>
        <result column="code_block_code" jdbcType="VARCHAR" property="codeBlockCode"/>
        <result column="area_code" jdbcType="VARCHAR" property="areaCode"/>
        <result column="area_name" jdbcType="VARCHAR" property="areaName"/>
        <result column="country_region_code" jdbcType="VARCHAR" property="countryRegionCode"/>
        <result column="country_region_name" jdbcType="VARCHAR" property="countryRegionName"/>
        <result column="geo_region_code" jdbcType="VARCHAR" property="geoRegionCode"/>
        <result column="geo_region_name" jdbcType="VARCHAR" property="geoRegionName"/>
        <result column="legal_entity_code" jdbcType="VARCHAR" property="legalEntityCode"/>
        <result column="legal_entity_name" jdbcType="VARCHAR" property="legalEntityName"/>
        <result column="bu_code" jdbcType="VARCHAR" property="buCode"/>
        <result column="bu_name" jdbcType="VARCHAR" property="buName"/>
        <result column="ou_code" jdbcType="VARCHAR" property="ouCode"/>
        <result column="ou_name" jdbcType="VARCHAR" property="ouName"/>
        <result column="mu_code" jdbcType="VARCHAR" property="muCode"/>
        <result column="mu_name" jdbcType="VARCHAR" property="muName"/>
        <result column="smu_code" jdbcType="VARCHAR" property="smuCode"/>
        <result column="smu_name" jdbcType="VARCHAR" property="smuName"/>
        <result column="sl_code" jdbcType="VARCHAR" property="slCode"/>
        <result column="sl_name" jdbcType="VARCHAR" property="slName"/>
        <result column="ssl_code" jdbcType="VARCHAR" property="sslCode"/>
        <result column="ssl_name" jdbcType="VARCHAR" property="sslName"/>
        <result column="competency_code" jdbcType="VARCHAR" property="competencyCode"/>
        <result column="competency_name" jdbcType="VARCHAR" property="competencyName"/>
        <result column="market_segment_code" jdbcType="VARCHAR" property="marketSegmentCode"/>
        <result column="market_segment_name" jdbcType="VARCHAR" property="marketSegmentName"/>
        <result column="mr_code" jdbcType="VARCHAR" property="mrCode"/>
        <result column="mr_name" jdbcType="VARCHAR" property="mrName"/>
        <result column="city_code" jdbcType="VARCHAR" property="cityCode"/>
        <result column="city_name" jdbcType="VARCHAR" property="cityName"/>
        <result column="mercury_competency_code" jdbcType="VARCHAR" property="mercuryCompetencyCode"/>
        <result column="mercury_competency_name" jdbcType="VARCHAR" property="mercuryCompetencyCode"/>
        <result column="category_code" jdbcType="VARCHAR" property="categoryCode"/>
    </resultMap>
    <sql id="Base_Column_List">
        code_block_id,code_block_code,country_region_code,country_region_name,area_code,area_name,
        geo_region_code,geo_region_name, legal_entity_code, legal_entity_name,bu_code,bu_name,
        ou_code,ou_name,
        mu_code, mu_name,smu_code, smu_name,sl_code, sl_name,ssl_code,ssl_name, competency_code, competency_name,
        market_segment_code,market_segment_name, mr_code,mr_name, city_code,city_name,
        mercury_competency_code, mercury_competency_name,category_code
    </sql>

    <select id="selectDistinctCountryRegionCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT country_region_code as code,country_region_name as name from dict_code_block where deleted=0 and
        status=0 and new_version=1
        and country_region_code is not null and country_region_code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>

    <select id="selectDistinctGeoRegionCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT geo_region_code as code,geo_region_name as name from dict_code_block where deleted=0 and
        status=0 and new_version=1
        and geo_region_code is not null and geo_region_code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>

    <select id="selectDistinctBuCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT bu_code as code , bu_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and bu_code is not null and bu_code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>

    <select id="selectDistinctSlCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT sl_code as code ,sl_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and sl_code is not null and sl_code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>

    <select id="selectDistinctSslCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT ssl_code as code ,ssl_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and ssl_code is not null and ssl_code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>


    <select id="selectDistinctCompetencyCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT competency_code as code ,competency_name as name from dict_code_block where deleted=0 and
        status=0 and new_version=1
        and competency_code is not null and competency_code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>

    <select id="selectDistinctSmuCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT smu_code as code ,smu_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and smu_code is not null and smu_code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>


    <select id="selectDistinctMuCodeBackList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT mu_code as code ,mu_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and mu_code is not null and mu_code!=''
        <include refid="column_condition_back"/>
        order by code
    </select>

    <select id="selectDistinctOuCodeBackList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT ou_code as code ,ou_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and ou_code is not null and ou_code!=''
        <include refid="column_condition_back"/>
        order by code
    </select>

    <select id="selectDistinctLegalEntityCodeBackList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT legal_entity_code as code ,legal_entity_name as name from dict_code_block where deleted=0 and
        status=0 and new_version=1
        and legal_entity_code is not null and legal_entity_code!=''
        <include refid="column_condition_back"/>
        order by code
    </select>

    <select id="selectDistinctBuCodeBackList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT bu_code as code ,bu_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and bu_code is not null and bu_code!=''
        <include refid="column_condition_back"/>
        order by code
    </select>

    <select id="selectDistinctSlCodeBackList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT sl_code as code ,sl_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and sl_code is not null and sl_code!=''
        <include refid="column_condition_back"/>
        order by code
    </select>

    <select id="selectDistinctSslCodeBackList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT ssl_code as code ,ssl_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and ssl_code is not null and ssl_code!=''
        <include refid="column_condition_back"/>
        order by code
    </select>

    <select id="selectDistinctSmuCodeBackList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT smu_code as code ,smu_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and smu_code is not null and smu_code!=''
        <include refid="column_condition_back"/>
        order by code
    </select>

    <select id="selectDistinctMuCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT mu_code as code ,mu_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and mu_code is not null and mu_code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>

    <select id="selectDistinctOuCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT ou_code as code ,ou_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and ou_code is not null and ou_code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>


    <select id="selectDistinctLegalEntityList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT legal_entity_code as code ,legal_entity_name as name from dict_code_block where deleted=0 and
        status=0 and new_version=1
        and legal_entity_code is not null and legal_entity_code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>

    <select id="selectDistinctMrCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT mr_Code as code ,mr_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and mr_Code is not null and mr_Code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>


    <select id="selectDistinctAreaCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT area_code as code ,area_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and area_code is not null and area_code!='' and source_type='firm'
        <include refid="column_condition"/>
        order by code
    </select>


    <sql id="column_condition">
        <if test="countryRegionCode !=null and countryRegionCode!=''">
            and country_region_code=#{countryRegionCode} and country_region_code is not null and country_region_code!=''
        </if>
        <if test="geoRegionCode !=null and geoRegionCode!=''">
            and geo_region_code=#{geoRegionCode} and geo_region_code is not null and geo_region_code!=''
        </if>
        <if test="legalEntityCode !=null and legalEntityCode!=''">
            and legal_entity_code=#{legalEntityCode} and legal_entity_code is not null and legal_entity_code!=''
        </if>
        <if test="buCode !=null and buCode!=''">
            and bu_code=#{buCode} and bu_code is not null and bu_code!=''
        </if>
        <if test="ouCode !=null and ouCode!=''">
            and ou_code=#{ouCode} and ou_code is not null and ou_code!=''
        </if>
        <if test="muCode !=null and muCode!=''">
            and mu_code=#{muCode} and mu_code is not null and mu_code!=''
        </if>
        <if test="slCode !=null and slCode!=''">
            and sl_code=#{slCode} and sl_code is not null and sl_code!=''
        </if>
        <if test="sslCode !=null and sslCode!=''">
            and ssl_code=#{sslCode} and ssl_code is not null and ssl_code!=''
        </if>
        <if test="competencyCode !=null and competencyCode!=''">
            and competency_code=#{competencyCode} and competency_code is not null and competency_code!=''
        </if>
        <if test="marketSegmentCode !=null and marketSegmentCode!=''">
            and market_segment_code=#{marketSegmentCode} and market_segment_code is not null and market_segment_code!=''
        </if>
        <if test="mrCode !=null and mrCode!=''">
            and mr_code=#{mrCode} and mr_code is not null and mr_code!=''
        </if>
        <if test="cityCode !=null and cityCode!=''">
            and city_code=#{cityCode} and city_code is not null and city_code!=''
        </if>
        <if test="mercuryCompetencyCode !=null and mercuryCompetencyCode!=''">
            and mercury_competency_code=#{mercuryCompetencyCode} and mercury_competency_code is not null and
            mercury_competency_code!=''
        </if>
        <if test="categoryCode !=null and categoryCode!=''">
            and category_code=#{categoryCode} and category_code is not null and category_code!=''
        </if>
        <if test="smuCode !=null and smuCode!=''">
            and smu_code=#{smuCode} and smu_code is not null and smu_code!=''
        </if>
        <if test="areaCode !=null and areaCode!=''">
            and area_code=#{areaCode} and area_code is not null and area_code!=''
        </if>
    </sql>

    <sql id="column_condition_back">

        <if test="legalEntityCode !=null and legalEntityCode.size() > 0">
            and legal_entity_code in
            <foreach collection="legalEntityCode" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
            and legal_entity_code is not null and legal_entity_code!=''
        </if>
        <if test="buCode !=null and buCode.size() > 0">
            and bu_code in
            <foreach collection="buCode" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
            and bu_code is not null and bu_code!=''
        </if>
        <if test="ouCode !=null and ouCode.size() > 0">
            and ou_code
            in
            <foreach collection="ouCode" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
            and ou_code is not null and ou_code!=''
        </if>
        <if test="muCode !=null and muCode.size() > 0">
            and mu_code
            in
            <foreach collection="muCode" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
            and mu_code is not null and mu_code!=''
        </if>
        <if test="smuCode !=null and smuCode.size() > 0">
            and smu_code
            in
            <foreach collection="smuCode" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
            and smu_code is not null and smu_code!=''
        </if>
        <if test="slCode !=null and slCode.size() > 0">
            and sl_code
            in
            <foreach collection="slCode" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
            and sl_code is not null and sl_code!=''
        </if>
        <if test="sslCode !=null and sslCode.size() > 0">
            and ssl_code
            in
            <foreach collection="sslCode" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
            and ssl_code is not null and ssl_code!=''
        </if>
    </sql>

    <select id="pullDownLegalEntityCode" resultType="java.lang.String">
        select DISTINCT legal_entity_code
        from dict_code_block
        where deleted = 0 and status=0 and new_version=1
        and legal_entity_code is not null
        and legal_entity_code != ''
        order by legal_entity_code
    </select>


    <select id="pullDownSl" resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT sl_code as code, sl_name as name
        from dict_code_block
        where deleted = 0 and status=0 and new_version=1
        and legal_entity_code = #{legalEntityCode}
        and sl_code is not null
        and sl_code != ''
        order by code
    </select>

    <select id="pullDownSlList" resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT sl_code as code,sl_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and legal_entity_code
        in
        <foreach collection="legalEntityCode" item="value" separator="," open="(" close=")">
            #{value}
        </foreach>
        and sl_code is not null and sl_code!=''
        order by code
    </select>

    <select id="pullDownSmu" resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT smu_code as code, smu_name as name
        from dict_code_block
        where deleted = 0 and status=0 and new_version=1
        and legal_entity_code = #{legalEntityCode}
        <if test="slCode!=null and slCode!='' ">
            and sl_code = #{slCode}
        </if>
        <if test="sslCode!=null and sslCode!='' ">
            and ssl_code = #{sslCode}
        </if>
        and smu_code is not null
        and smu_code != ''
        order by code
    </select>

    <select id="pullDownSmuList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT smu_code as code,smu_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and legal_entity_code
        in
        <foreach collection="legalEntityCode" item="value" separator="," open="(" close=")">
            #{value}
        </foreach>
        and sl_code
        in
        <foreach collection="slCode" item="value" separator="," open="(" close=")">
            #{value}
        </foreach>
        and ssl_code
        in
        <foreach collection="sslCode" item="value" separator="," open="(" close=")">
            #{value}
        </foreach>
        and smu_code is not null and smu_code!=''
        order by code
    </select>

    <select id="pullDownSslCodeList"
            resultType="cn.ey.biz.module.system.controller.admin.codeblock.vo.CodeBlockCodeValueVO">
        select DISTINCT ssl_code as code,ssl_name as name from dict_code_block where deleted=0 and status=0 and
        new_version=1
        and legal_entity_code
        in
        <foreach collection="legalEntityCode" item="value" separator="," open="(" close=")">
            #{value}
        </foreach>
        and sl_code
        in
        <foreach collection="slCode" item="value" separator="," open="(" close=")">
            #{value}
        </foreach>
        and ssl_code is not null and ssl_code!=''
        order by code
    </select>

    <select id="filterList" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from dict_code_block
        <where>
            deleted =0 and status=0 and new_version=1
            <if test="distinctList !=null and distinctList.size() > 0">
                and(
                <foreach collection="distinctList" item="item" separator="or">
                    (
                    <if test="item.legalEntityCode!=null and item.legalEntityCode!=''">
                        legal_entity_code = #{item.legalEntityCode}
                        and legal_entity_code is not null and legal_entity_code!=''
                    </if>
                    <if test="item.smuCode!=null and item.smuCode!=''">
                        <if test="item.legalEntityCode!=null and item.legalEntityCode!=''">
                            and
                        </if>
                        smu_code = #{item.smuCode}
                        and smu_code is not null and smu_code!=''
                    </if>
                    <if test="item.slCode!=null and item.slCode!=''">
                        <if test="(item.smuCode!=null and item.smuCode!='')||(item.legalEntityCode!=null and item.legalEntityCode!='')">
                            and
                        </if>
                        sl_code = #{item.slCode}
                        and sl_code is not null and sl_code!=''
                    </if>
                    <if test="item.sslCode!=null and item.sslCode!=''">
                        <if test="(item.smuCode!=null and item.smuCode!='')||(item.legalEntityCode!=null and item.legalEntityCode!='')||(item.slCode!=null and item.slCode!='')">
                            and
                        </if>
                        ssl_code = #{item.sslCode}
                        and ssl_code is not null and ssl_code!=''
                    </if>
                    )
                </foreach>
                )
            </if>
        </where>
        order by code_block_id
    </select>

    <select id="financeFilterList" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from dict_code_block where deleted =0 and source_type='gds' and status=0 and new_version=1
        and code_block_code in(select DISTINCT employee_code_block from dict_gds_rate_value where status=0 and deleted
        =0 )
        order by code_block_id
    </select>

    <select id="getCodeBlockList" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from dict_code_block where status=0 and deleted =0 and new_version=1 order by code_block_id
    </select>

    <select id="financeFilterFirmList" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from dict_code_block where deleted =0 and source_type='firm' and status=0 and new_version=1
        order by code_block_id
    </select>


    <select id="filterListPage" resultMap="BaseResultMap">
        select
        code_block_id,code_block_code,country_region_code,country_region_name,area_code,area_name,geo_region_code,
        geo_region_name,legal_entity_code,legal_entity_name,bu_code,bu_name,ou_code,ou_name,mu_code,
        mu_name,smu_code,smu_name,sl_code,sl_name,ssl_code,ssl_name,competency_code,competency_name,
        market_segment_code,market_segment_name,mr_code,mr_name,city_code,city_name,mercury_competency_code,mr_name,
        mercury_competency_name,
        category_code
        from dict_code_block
        where
        deleted=0
        <if test="pageReqVO.mrName!=null and pageReqVO.mrName!=''">
            and mr_name like concat('%', #{pageReqVO.mrName},'%')
        </if>
        <if test="pageReqVO.muName!=null and pageReqVO.muName!=''">
            and mu_name like concat('%', #{pageReqVO.muName},'%')
        </if>
        <if test="pageReqVO.ouName!=null and pageReqVO.ouName!=''">
            and ou_name like concat('%', #{pageReqVO.ouName},'%')
        </if>
        <if test="pageReqVO.codeBlockCode!=null and pageReqVO.codeBlockCode!=''">
            and code_block_code like concat('%', #{pageReqVO.codeBlockCode},'%')
        </if>
        <if test="pageReqVO.legalEntityName!=null and pageReqVO.legalEntityName!=''">
            and legal_entity_name like concat('%', #{pageReqVO.legalEntityName},'%')
        </if>
        <if test="codeBlockServiceLineVO !=null and codeBlockServiceLineVO.size() > 0">
            and(
            <foreach collection="codeBlockServiceLineVO" item="item" index="index" separator="or">
                (
                <if test="item.sl!=null and item.sl!=''">
                    sl_code=#{item.sl}
                </if>
                <if test="item.ssl!=null and item.ssl!=''">
                    <if test="item.sl!=null and item.sl!=''">
                        and
                    </if>
                    ssl_code=#{item.ssl}
                </if>

                <if test="item.competency!=null and item.competency!=''">
                    <if test="(item.ssl!=null and item.ssl!='') || (item.sl!=null and item.sl!='')">
                        and
                    </if>
                    competency_code=#{item.competency}
                </if>

                <if test="item.smu!=null and item.smu!=''">
                    <if test="(item.competency!=null and item.competency!='') || (item.sl!=null and item.sl!='') ||((item.ssl!=null and item.ssl!='')) ">
                        and
                    </if>
                    smu_code=#{item.smu}
                </if>

                )
            </foreach>
            )
        </if>
        <if test="codeBlockAreaVO !=null and codeBlockAreaVO.size() > 0">
            and(
            <foreach collection="codeBlockAreaVO" item="item" index="index" separator="or">
                (

                <if test="item.marketSegmentCode!=null and item.marketSegmentCode!=''">
                    market_segment_code=#{item.marketSegmentCode}
                </if>

                <if test="item.cityCode!=null and item.cityCode!=''">
                    <if test="item.marketSegmentCode!=null and item.marketSegmentCode!=''">
                        and
                    </if>
                    city_code=#{item.cityCode}
                </if>

                <if test="item.buCode!=null and item.buCode!=''">
                    <if test="(item.cityCode!=null and item.cityCode!='')||(item.marketSegmentCode!=null and item.marketSegmentCode!='')">
                        and
                    </if>
                    bu_code=#{item.buCode}
                </if>

                )
            </foreach>
            )
        </if>
    </select>

    <select id="getCodeBlockListByGpn" resultMap="BaseResultMap">
        select
        cb.code_block_code,
        cb.sl_code,
        cb.ssl_code,
        cb.ou_code,
        cb.mu_code,
        cb.smu_code,
        cb.sl_name,
        cb.ssl_name,
        cb.ou_name,
        cb.mu_name,
        cb.smu_name,
        cb.mr_code,
        cb.mr_name
        from dict_code_block cb
        LEFT JOIN system_role_codeblock rc on rc.code_block=cb.code_block_code
        LEFT JOIN system_data_role da on da.id=rc.role_id
        LEFT JOIN system_user_data_role uda on uda.role_id=da.id
        WHERE
        cb.deleted = 0
        AND rc.deleted = 0
        AND da.deleted = 0
        AND uda.deleted =0
        <if test="app!=null and app!=''">
            and da.app=#{app}
        </if>
        <if test="userId!=null and userId!=''">
            and uda.user_id=#{userId}
        </if>
        <if test="configType!=null and configType!=''">
            and da.config_type=#{configType}
        </if>
    </select>

    <select id="getGpnListByCodeBlock" resultType="java.lang.String">
        select
        us.gpn
        from system_users us
        LEFT JOIN system_user_data_role ud on ud.user_id=us.id
        LEFT JOIN system_data_role da on da.id=ud.role_id
        LEFT JOIN system_role_codeblock rc on rc.role_id=da.id
        WHERE us.deleted=0
        and ud.deleted=0
        and da.deleted=0
        and rc.deleted=0
        <if test="app!=null and app!=''">
            and da.app=#{app}
        </if>
        <if test="codeBlock!=null and codeBlock!=''">
            and rc.code_block=#{codeBlock}
        </if>
        <if test="configType!=null and configType!=''">
            and da.config_type=#{configType}
        </if>
    </select>

    <select id="getCodeBlockListByGpnUpgrade" resultMap="BaseResultMap">
        select
        cb.code_block_code,
        cb.sl_code,
        cb.ssl_code,
        cb.ou_code,
        cb.mu_code,
        cb.smu_code,
        cb.sl_name,
        cb.ssl_name,
        cb.ou_name,
        cb.mu_name,
        cb.smu_name,
        cb.mr_code,
        cb.mr_name
        from dict_code_block cb
        LEFT JOIN system_role_codeblock rc on rc.code_block=cb.code_block_code
        LEFT JOIN system_data_role da on da.id=rc.role_id
        WHERE
        cb.deleted = 0
        AND rc.deleted = 0
        AND da.deleted = 0
        <if test="app!=null and app!=''">
            and da.app=#{app}
        </if>
        <if test="configType!=null and configType!=''">
            and da.config_type=#{configType}
        </if>
        <if test="roleIds!=null and roleIds.size() > 0">
            and da.id in
            <foreach collection="roleIds" item="value" separator="," open="(" close=")">
                #{value}
            </foreach>
        </if>
    </select>

</mapper>