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


    <select id="getCompanyDto"  resultType="com.yeejoin.amos.boot.module.hygf.api.dto.CompanyDto">

select
  `SEQUENCE_NBR` sequenceNbr,
  `COMPANY_NAME` companyName,
  `COMPANY_CODE` companyCode,
  `DESCRIPTION` description,
  `ADDRESS` address,
(select DICT_DATA_VALUE from systemctl_dictionarie_value where IS_DELETED=0 and AGENCY_CODE='JXIOP' and DICT_CODE='COMPANY_LEVEL' and privilege_company.`LEVEL`=systemctl_dictionarie_value.DICT_DATA_KEY )levelName,
(select DICT_DATA_VALUE from systemctl_dictionarie_value where IS_DELETED=0 and AGENCY_CODE='JXIOP' and DICT_CODE='COMPANY_TYPE' and privilege_company.COMPANY_TYPE=systemctl_dictionarie_value.DICT_DATA_KEY) companyTypeName
from  privilege_company  where IS_DELETED=0 and AGENCY_CODE='JXIOP'


            <if test="dto.companyName!=null">
             and privilege_company.COMPANY_NAME like concat(concat("%",#{dto.companyName}),"%")
            </if>
            <if test="dto.level!=null">
                and privilege_company.LEVEL=#{dto.level}
            </if>
            <if test="dto.companyType!=null">
                and privilege_company.COMPANY_TYPE=#{dto.companyType}
            </if>
            <if test="dto.orgCode!=null">
                and privilege_company.ORG_CODE like concat(concat(#{dto.orgCode}),"%")
            </if>
            <if test="dto.comid!=null">
                and privilege_company.SEQUENCE_NBR !=#{dto.comid}
            </if>




    </select>

    <select id="getuserList" resultType="Map">

        select
        DISTINCT std_user_biz.real_name realName
        from
        std_user_biz LEFT JOIN hygf_personnel_business on std_user_biz.sequence_nbr =hygf_personnel_business.foundation_id

        <where>
            <if test="role!=null">
                and std_user_biz.role like concat(concat("%",#{role}),"%")
            </if>
            <if test="regionalCompaniesSeq!=null">
                and hygf_personnel_business.regional_companies_seq=#{regionalCompaniesSeq}
            </if>
            <if test="amosUnitId!=null">
                and hygf_personnel_business.amos_unit_id=#{amosUnitId}
            </if>
            <if test="userName!=null">
                and  std_user_biz.amos_user_id=#{userName}
            </if>
        </where>


    </select>


    <select id="getuserListByOrgCode" resultType="Map">

        select
        DISTINCT std_user_biz.real_name realName
        from
        std_user_biz LEFT JOIN hygf_personnel_business on std_user_biz.sequence_nbr =hygf_personnel_business.foundation_id

        <where>
            <if test="role!=null">
                and std_user_biz.role like concat(concat("%",#{role}),"%")
            </if>
            <if test="regionalCompaniesSeq!=null">
                and hygf_personnel_business.regional_companies_seq=#{regionalCompaniesSeq}
            </if>
            <if test="amosOrgCode!=null">
                and hygf_personnel_business.amos_unit_org_code like concat ('%',#{amosOrgCode},'%')
            </if>
            <if test="userName!=null">
                and  std_user_biz.amos_user_id=#{userName}
            </if>
        </where>


    </select>


    <select id="getuserListtelephone" resultType="Map">

        select

        std_user_biz.emergency_telephone  emergencyTelephone
        from
        std_user_biz LEFT JOIN hygf_personnel_business on std_user_biz.sequence_nbr =hygf_personnel_business.foundation_id

        <where>
            <if test="role!=null">
                and std_user_biz.role like concat(concat("%",#{role}),"%")
            </if>
            <if test="regionalCompaniesSeq!=null">
                and hygf_personnel_business.regional_companies_seq=#{regionalCompaniesSeq}
            </if>
            <if test="amosUnitId!=null">
                and hygf_personnel_business.amos_unit_id=#{amosUnitId}
            </if>
            <if test="userName!=null">
                and  std_user_biz.real_name=#{userName}
            </if>
        </where>
        LIMIT 1

    </select>


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

    select  hygf_unit_info.* from hygf_unit_info left
    JOIN  hygf_regional_companies on  hygf_regional_companies.unit_info_id=hygf_unit_info.sequence_nbr
    where hygf_unit_info.is_delete=0 and hygf_unit_info.audit_status=2
    GROUP BY hygf_unit_info.sequence_nbr

    </select>

</mapper>
