<?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.yeejoin.amos.boot.module.hygf.api.mapper.RegionalCompaniesMapper">

    <select id="selectListData" resultType="com.yeejoin.amos.boot.module.hygf.api.entity.RegionalCompanies">
        select * from (

        select
        hygf_regional_companies.*,
        (SELECT GROUP_CONCAT(REGION_NAME) from
        (SELECT T2.REGION_NAME
        FROM (
        SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM systemctl_region WHERE sequence_nbr = _id) AS parent_id,
        @l := @l + 1 AS lvl
        FROM
        (SELECT @r := privilege_company.REGION_SEQ, @l := 0) vars,
        systemctl_region h
        WHERE @r != 0) T1
        JOIN systemctl_region T2
        ON T1._id = T2.sequence_nbr
        ORDER BY T1.lvl DESC) s) regionalAddress
        from hygf_regional_companies LEFT JOIN privilege_company on
        hygf_regional_companies.regional_companies_seq=privilege_company.SEQUENCE_NBR
        where hygf_regional_companies.is_delete=0

        <if test="regionalCompaniesName!=null and regionalCompaniesName!=''">
            and hygf_regional_companies.regional_companies_name like concat(concat("%",#{regionalCompaniesName}),"%")
        </if>

        <if test="unitId!=null and unitId!=''">
            and hygf_regional_companies.unit_id=#{unitId}
        </if>
        <if test="sequenceNbr!=null and sequenceNbr!=''">
            and hygf_regional_companies.unit_info_id=#{sequenceNbr}
        </if>
        ) a
    </select>

    <select id="selectByid" resultType="com.yeejoin.amos.boot.module.hygf.api.entity.RegionalCompanies">


        select
        (SELECT GROUP_CONCAT(REGION_NAME) from
        (SELECT T2.REGION_NAME
        FROM (
        SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM systemctl_region WHERE sequence_nbr = _id) AS parent_id,
        @l := @l + 1 AS lvl
        FROM
        (SELECT @r := privilege_company.REGION_SEQ, @l := 0) vars,
        systemctl_region h
        WHERE @r != 0) T1
        JOIN systemctl_region T2
        ON T1._id = T2.sequence_nbr
        ORDER BY T1.lvl DESC) s) regionalAddress
        from hygf_regional_companies LEFT JOIN privilege_company on
        hygf_regional_companies.regional_companies_seq=privilege_company.SEQUENCE_NBR
        where hygf_regional_companies.is_delete=0 and   hygf_regional_companies.sequence_nbr=#{sequenceNbr}



    </select>

    <select id="selectRegionName" resultType="com.yeejoin.amos.boot.module.hygf.api.entity.RegionalCompanies">
         select
	    privilege_company.COMPANY_CODE companyCode,
	    privilege_company.COMPANY_NAME regionalCompaniesName,
	    privilege_company.ORG_CODE  regionalCompaniesCode,
        (SELECT GROUP_CONCAT(REGION_NAME) from
        (SELECT T2.REGION_NAME
        FROM (
        SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM systemctl_region WHERE sequence_nbr = _id) AS parent_id,
        @l := @l + 1 AS lvl
        FROM
        (SELECT @r := privilege_company.REGION_SEQ, @l := 0) vars,
        systemctl_region h
        WHERE @r != 0) T1
        JOIN systemctl_region T2
        ON T1._id = T2.sequence_nbr
        ORDER BY T1.lvl DESC) s) regionalAddress
        from privilege_company where privilege_company.SEQUENCE_NBR=#{sequenceNbr}

    </select>



    <select id="selectListRegionalCompanies" resultType="com.yeejoin.amos.boot.module.hygf.api.entity.RegionalCompanies">

select * from   hygf_regional_companies
    Left JOIN  hygf_unit_info on hygf_unit_info.sequence_nbr = hygf_regional_companies.unit_info_id
where hygf_regional_companies.is_delete=0
 and  hygf_regional_companies.unit_info_id=#{sequenceNbr}
    </select>
</mapper>
