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

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

        select
        std_user_biz.sequence_nbr sequenceNbr,
        hygf_personnel_business.regional_companies_name regionalCompaniesName,

        std_user_biz.amos_user_name amosUserName,

        std_user_biz.real_name realName,

        (case std_user_biz.lock_status
        when "LOCK" THEN "禁用"
        else "启用" end
        )lockStatus,

        std_user_biz.emergency_telephone emergencyTelephone,

        (case hygf_personnel_business.user_type
        when "1" THEN "普通用户"
        else "管理员" end
        )userType,
        REGEXP_REPLACE(IFNULL(std_user_biz.position, ''), '\\[|\\]|\\"', '') position
        from  std_user_biz LEFT join  hygf_personnel_business  on std_user_biz.sequence_nbr =hygf_personnel_business.foundation_id


        where std_user_biz.is_delete=0

        <if test="dto.realName!=null">
            and std_user_biz.real_name like concat(concat("%",#{dto.realName}),"%")
        </if>
        <if test="dto.lockStatus!=null">
            and std_user_biz.lock_status=#{dto.lockStatus}
        </if>
        <if test="dto.amosUserName!=null">
            and std_user_biz.amos_user_name  like concat(concat("%",#{dto.amosUserName}),"%")
        </if>
        <if test="dto.amosUnitOrgCode!=null">
            and hygf_personnel_business.amos_unit_org_code like concat(concat(#{dto.amosUnitOrgCode}),"%")
        </if>





    </select>

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

select  ORG_CODE orgCode from  privilege_company  where privilege_company.SEQUENCE_NBR=#{id}

    </select>

    <select id="getUserUnitInformationDto"  resultType="com.yeejoin.amos.boot.module.hygf.api.dto.UserUnitInformationDto">
    SELECT

    hygf_personnel_business.regional_companies_seq   regionalCompaniesSeq    ,
    hygf_personnel_business.regional_companies_code    regionalCompaniesCode ,
    hygf_personnel_business.regional_companies_name  regionalCompaniesName   ,

    hygf_personnel_business.amos_unit_id   amosUnitId  ,
    hygf_personnel_business.amos_unit_name    amosUnitName ,
    hygf_personnel_business.amos_unit_org_code  amosUnitOrgCode   ,

    hygf_personnel_business.amos_dealer_id  amosDealerId   ,
    privilege_company.COMPANY_NAME    amosDealerName ,
    privilege_company.ORG_CODE  amosDealerOrgCode,
	(select sequence_nbr from  hygf_unit_info h  where h.amos_company_seq=  hygf_personnel_business.amos_dealer_id )amosUnitInfoId


    FROM  std_user_biz LEFT JOIN  hygf_personnel_business
     LEFT JOIN hygf_unit_info on hygf_personnel_business.amos_unit_id=hygf_unit_info.amos_company_seq
    ON std_user_biz.sequence_nbr=hygf_personnel_business.foundation_id

    LEFT JOIN privilege_company on privilege_company.SEQUENCE_NBR=hygf_personnel_business.amos_dealer_id


    WHERE std_user_biz.amos_user_id=#{userId}

    </select>




    <select id="getMaintenanceUserUnitInformationDto"  resultType="com.yeejoin.amos.boot.module.hygf.api.dto.UserUnitInformationDto">
        SELECT
            hygf_maintenance.regional_companies_seq regionalCompaniesSeq,
            hygf_maintenance.regional_companies_code regionalCompaniesCode,
            hygf_maintenance.regional_companies_name regionalCompaniesName,
            std_user_biz.amos_id amosUnitId,
            privilege_company.COMPANY_NAME amosUnitName,
            privilege_company.ORG_CODE amosUnitOrgCode,
            hygf_maintenance.dealer_id amosDealerId,
            hygf_unit_info.`name` amosDealerName,
            hygf_unit_info.amos_company_code amosDealerOrgCode,
            hygf_unit_info.sequence_nbr amosUnitInfoId
        FROM
            std_user_biz
                JOIN hygf_maintenance on std_user_biz.sequence_nbr = hygf_maintenance.foundation_id
                JOIN hygf_unit_info on hygf_maintenance.dealer_id = hygf_unit_info.sequence_nbr
                JOIN privilege_company on privilege_company.SEQUENCE_NBR = std_user_biz.amos_id

        WHERE
                std_user_biz.amos_user_id =#{userId}






    </select>


</mapper>
