<?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.common.api.mapper.OrgUsrMapper">

    <select id="selectUpUnitByParam" resultType="java.lang.String">
        SELECT
        biz_org_name
        FROM
        `cb_org_usr`
        WHERE
        1=1
        <if test="id != null and id != ''">
            and `sequence_nbr` = ( SELECT parent_id FROM `cb_org_usr` WHERE sequence_nbr = #{id} and is_delete = 0 limit
            1)
        </if>

    </select>

    <select id="selectPersonListCount" resultType="Integer">
        select count(1) from (
        SELECT
        DISTINCT
        u.sequence_nbr sequenceNbr,
        u.biz_org_name bizOrgName,
        u.biz_org_code bizOrgCode,
        <if test="fields != null">
            <foreach collection="fields" item="item" separator=",">MAX(case f.field_code when #{item} then v.field_value
                end) ${item}
            </foreach>
        </if>
        FROM
        cb_org_usr u LEFT JOIN
        cb_dynamic_form_instance v on u.sequence_nbr = v.instance_id
        LEFT JOIN cb_dynamic_form_column f ON f.sequence_nbr = v.form_column_id
        where
        u.biz_org_type = #{bizOrgType}
        AND u.is_delete = 0
        <if test="bizOrgName != null">
            AND u.biz_org_name = #{bizOrgName}
        </if>
        <if test="bizOrgCode != null and bizOrgCode != '-1'">
            AND u.biz_org_code like concat(#{bizOrgCode}, '%')
        </if>
        GROUP BY
        u.sequence_nbr ,
        u.biz_org_name ,
        u.biz_org_code
        )a where a.sequenceNbr is not null

        <if test="fieldsValue != null">
            <foreach collection="fieldsValue.keys" item="item">AND a.${item} = #{fieldsValue[${item}]}</foreach>
        </if>
    </select>

    <!--机场单位人员按时间倒叙排列add order by u.rec_date desc 2021-09-08 by kongfm  -->
    <select id="selectPersonList" resultType="Map">
        select * from (
        SELECT
        DISTINCT
        u.sequence_nbr sequenceNbr,
        u.biz_org_name bizOrgName,
        u.biz_org_code bizOrgCode,
        <if test="map.fields != null">
            <foreach collection="map.fields" item="item" separator=",">MAX(case f.field_code when #{item} then IFNULL(v.field_value_label, v.field_value)
                end) ${item}
            </foreach>
        </if>
        FROM
        cb_org_usr u
        LEFT JOIN
        cb_dynamic_form_instance v on u.sequence_nbr = v.instance_id
        LEFT JOIN cb_dynamic_form_column f ON f.sequence_nbr = v.form_column_id
        where
        u.biz_org_type = #{map.bizOrgType}
        AND u.is_delete = 0
        <if test="map.bizOrgName != null">
            AND u.biz_org_name like concat('%',#{map.bizOrgName},'%')
        </if>
        <if test="map.personNumber!= null">
            AND v.field_value like concat('%',#{map.personNumber},'%')
        </if>
        <if test="map.bizOrgCode != null and map.bizOrgCode != '-1'">
            AND u.biz_org_code like concat(#{map.bizOrgCode}, '%')
        </if>
        GROUP BY
        u.sequence_nbr ,
        u.biz_org_name ,
        u.biz_org_code
        order by u.rec_date desc
        )a where a.sequenceNbr is not null
        <if test="map.fieldsValue != null">
            <foreach collection="map.fieldsValue.keys" item="item">AND a.${item} = #{map.fieldsValue[${item}]}</foreach>
        </if>
        LIMIT #{map.pageNum}, #{map.pageSize}
    </select>


    <select id="selectPersonAllList" resultType="Map">
        select * from (
        SELECT
        DISTINCT
        u.sequence_nbr sequenceNbr,
        u.biz_org_name bizOrgName,
        u.biz_org_code bizOrgCode,
        <if test="fields != null">
            <foreach collection="fields" item="item" separator=",">MAX(case f.field_code when #{item} then IFNULL(v.field_value_label, v.field_value)
                end) ${item}
            </foreach>
        </if>
        FROM
        cb_org_usr u LEFT JOIN
        cb_dynamic_form_instance v on u.sequence_nbr = v.instance_id
        LEFT JOIN cb_dynamic_form_column f ON f.sequence_nbr = v.form_column_id
        where u.parent_id = #{parentId}
        AND u.biz_org_type = #{bizOrgType}
        AND U.is_delete = 0
        GROUP BY
        u.sequence_nbr ,
        u.biz_org_name ,
        u.biz_org_code
        )a where a.sequenceNbr is not null
    </select>

    <select id="selectCompanyDepartmentMsg" resultType="com.yeejoin.amos.boot.module.common.api.entity.OrgUsr">
        SELECT sequence_nbr,
               biz_org_name,
               biz_org_code,
               biz_org_type,
               parent_id
        FROM `cb_org_usr`
        WHERE is_delete = 0
            and (biz_org_type = "COMPANY"
           or biz_org_type = "DEPARTMENT")
    </select>

    <select id="queryOrgUsrListByBizOrgCode" resultType="com.yeejoin.amos.boot.module.common.api.entity.OrgUsr">
        SELECT sequence_nbr,
               biz_org_name,
               biz_org_code,
               amos_org_id,
               amos_org_code,
               biz_org_type,
               build_name,
               build_id,
               parent_id,
               org_expand_attr1,
               org_expand_attr2,
               org_expand_attr3,
               org_expand_attr4,
               org_expand_attr5,
               org_expand_attr6,
               org_expand_attr7,
               org_expand_attr8,
               rec_user_name,
               rec_user_id,
               rec_date,
               is_delete
        FROM `cb_org_usr`
        WHERE is_delete = 0
          and biz_org_code like concat(#{bizOrgCode}, '%')
    </select>

    <!--消防队伍列表按时间倒叙排列order by x.rec_date desc 2021-09-08 by kongfm -->
    <select id="queryOrgPersonDtoList" resultType="com.yeejoin.amos.boot.module.common.api.dto.OrgPersonExcelDto">
        select u.*,
               v.person_number,
               v.certificates_type,
               v.gender,
               v.certificates_number,
               v.telephone,
               v.state,
               v.safety_training,
               v.administrative_position,
               v.internal_position,
               v.fireManagement_post,
               v.position_type,
               v.certificate_type,
               v.holding_time,
               v.audit_cycle,
               v.person_img,
               v.certificate_img
        from cb_org_usr u
                 left join
             (
                 select instance_id,
                        max(case field_code when 'personNumber' then field_value end)                   person_number,
                        max(case field_code when 'certificatesTypeCode' then field_value end)           certificates_type,
                        max(case field_code when 'gender' then field_value end)                         gender,
                        max(case field_code when 'certificatesNumber' then field_value end)             certificates_number,
                        max(case field_code when 'telephone' then field_value end)                      telephone,
                        max(case field_code when 'stateCode' then field_value end)                      state,
                        max(case field_code when 'safetyTraining' then field_value end)                 safety_training,
                        max(
                                case field_code when 'administrativePositionCode' then field_value end) administrative_position,
                        max(case field_code when 'internalPositionCode' then field_value end)           internal_position,
                        max(case field_code when 'fireManagementPostCode' then field_value end)         fireManagement_post,
                        max(case field_code when 'positionType' then field_value end)                   position_type,
                        max(case field_code when 'certificateType' then field_value end)                certificate_type,
                        max(case field_code when 'holdingTime' then field_value end)                    holding_time,
                        max(case field_code when 'auditCycle' then field_value end)                     audit_cycle,
                        max(case field_code when 'personImg' then field_value end)                      person_img,
                        max(case field_code when 'certificateImg' then field_value end)                 certificate_img
                 from cb_dynamic_form_instance
                 where group_code = 246
                 group by instance_id
             ) v
             on u.sequence_nbr = v.instance_id
        where u.is_delete = #{isDelete}
        order by u.rec_date desc
    </select>
    <select id="listOrgUserById" resultType="com.yeejoin.amos.boot.module.common.api.entity.OrgUsr">
        SELECT *
        FROM cb_org_usr
        WHERE is_delete = 0
          <if test="orgUserId != null">
              AND biz_org_code LIKE CONCAT((SELECT biz_org_code FROM cb_org_usr WHERE sequence_nbr = #{orgUserId}),'%');
          </if>
    </select>

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

        SELECT
        a.id sequenceNbr,
        a.name,
        a.longitude,
        a.latitude,
        a.companyMaleEmployees,
        a.companyFemaleEmployees,
        a.managementType,
        Round(st_distance(point(a.longitude,a.latitude),point(#{par.longitude},#{par.latitude}))*111195,1) AS distance,
        case when cks_d.count is null then  0 else cks_d.count end as keySiteNum
        FROM important_companys a left join (
           SELECT cks.belong_id ,count(cks.belong_id) count from cb_key_site cks GROUP BY cks.belong_id
        )cks_d
 		on a.id=cks_d.belong_id
        where  a.longitude is not null and a.latitude is not null
        <if test='par.distance!=null'>
            and Round(st_distance(point(a.longitude,a.latitude),point(#{par.longitude},#{par.latitude}))*111195,1) &lt;=
            #{par.distance}
        </if>
        ORDER BY distance limit #{pageNum},#{pageSize}
    </select>

    <select id="listContractDtoCount" resultType="Integer">
        SELECT
        COUNT(a.id) num
        FROM important_companys a
        where a.longitude is not null and a.latitude is not null
        <if test='par.distance!=null'>
            and Round(st_distance(point(a.longitude,a.latitude),point(#{par.longitude},#{par.latitude}))*111195,1) &lt;=
            #{par.distance}
        </if>
    </select>

    <select id="getOrgUsrzhDto" resultType="com.yeejoin.amos.boot.module.common.api.dto.OrgUsrzhDto">
       SELECT
	a.biz_org_name bizOrgName,
	a.build_name buildName,
	a.build_id buildId,
    c.keySiteCount,
	( SELECT v.biz_org_name FROM cb_org_usr v WHERE v.sequence_nbr = a.parent_id ) parentName,
	max( CASE b.field_code WHEN 'businessCategory' THEN b.field_value_label ELSE "" END ) AS 'businessCategory',
	max( CASE b.field_code WHEN 'companyFemaleEmployees' THEN b.field_value ELSE "" END ) AS 'companyFemaleEmployees',
	max( CASE b.field_code WHEN 'companyLocation' THEN b.field_value ELSE "" END ) AS 'companyLocation',
	max( CASE b.field_code WHEN 'companyMaleEmployees' THEN b.field_value ELSE "" END ) AS 'companyMaleEmployees',
	max( CASE b.field_code WHEN 'companyNature' THEN b.field_value_label ELSE "" END ) AS 'companyNature',
	max( CASE b.field_code WHEN 'companyPhone' THEN b.field_value ELSE "" END ) AS 'companyPhone',
	max( CASE b.field_code WHEN 'companyPhoto' THEN b.field_value ELSE "" END ) AS 'companyPhoto',
	max( CASE b.field_code WHEN 'managementType' THEN b.field_value_label ELSE "" END ) AS 'managementType'
FROM
	cb_org_usr a
	LEFT JOIN cb_dynamic_form_instance b ON a.sequence_nbr = b.instance_id
    LEFT JOIN (select belong_id, count(*) keySiteCount from cb_key_site group by belong_id) c on c.belong_id = a.sequence_nbr
WHERE
	a.biz_org_name = #{bizOrgName}
    </select>
<select id="getCompanyAndCountDepartment" resultType="com.yeejoin.amos.boot.module.common.api.dto.OrgUsrTreeDto">

SELECT
	result_main.sequenceNbr AS sequenceNbr,
	result_main.bizOrgName AS bizOrgName,
	result_main.parentId AS parentId,
	result_main.num1 + result_main.num2 AS num
FROM
	(
		SELECT
			CASE
		WHEN result1.num IS NULL THEN
			0
		ELSE
			result1.num
		END AS num1,
		CASE
	WHEN result2.num IS NULL THEN
		0
	ELSE
		result2.num
	END AS num2,
	result1.sequenceNbr,
	result1.bizOrgName,
	result1.parentId
FROM
	(
		SELECT
			company_sur.sequence_nbr AS sequenceNbr,
			company_sur.biz_org_name AS bizOrgName,
			company_sur.parent_id AS parentId,
			CASE
		WHEN keysite_sur.num IS NULL THEN
			0
		ELSE
			keysite_sur.num
		END AS num
		FROM
			(
				SELECT
					company.sequence_nbr,
					company.parent_id,
					company.biz_org_name
				FROM
					cb_org_usr company
				WHERE
					company.biz_org_type = 'COMPANY'
				AND company.is_delete = FALSE
			) company_sur
		LEFT JOIN (
			SELECT
				keysite.belong_id,
				COUNT(keysite.belong_id) AS num
			FROM
				cb_key_site keysite
			WHERE
				keysite.is_delete = FALSE
			GROUP BY
				keysite.belong_id
		) keysite_sur ON company_sur.sequence_nbr = keysite_sur.belong_id
	) result1
LEFT JOIN (
	SELECT
		company_sur.sequence_nbr AS sequenceNbr,
		company_sur.biz_org_name AS bizOrgName,
		company_sur.parent_id AS parentId,
		CASE
	WHEN keysite_sur.num IS NULL THEN
		0
	ELSE
		keysite_sur.num
	END AS num
	FROM
		(
			SELECT
				company.sequence_nbr,
				company.parent_id,
				company.biz_org_name
			FROM
				cb_org_usr company
			WHERE
				company.biz_org_type = 'COMPANY'
			AND company.is_delete = FALSE
		) company_sur
	LEFT JOIN (
		SELECT
			keysite.belong_id,
			COUNT(keysite.belong_id) AS num
		FROM
			cb_key_site keysite
		WHERE
			keysite.is_delete = FALSE
		GROUP BY
			keysite.belong_id
	) keysite_sur ON company_sur.sequence_nbr = keysite_sur.belong_id
) result2 ON result1.sequenceNbr = result2.parentId
	) result_main
GROUP BY
	result_main.sequenceNbr
</select>




    <select id="getparent" resultType="Map">
       SELECT  important_companys.id ,important_companys.name

      FROM important_companys

    </select>
    <select id="getUserUnit" resultType="com.yeejoin.amos.boot.module.common.api.dto.UserUnitDto">
        SELECT
            u.sequence_nbr AS personSeq,
            u.biz_org_name AS personName,
            '2' AS identityType,
            IFNULL( u.biz_org_code, '' ) AS companyId
        FROM
            `cb_org_usr` u
        <where>
            <if test="id != null and id != ''">
                u.amos_org_id = #{id}
            </if>
            <if test="type != null and type != ''">
                AND u.biz_org_type = #{type}
            </if>
            <if test="code != null and code != ''">
                AND u.biz_org_code LIKE CONCAT(#{code}, '%')
            </if>
        </where>
        ORDER BY
            u.sequence_nbr DESC
    </select>
    <!--BUG2655 导出机场人员存在已删除数据 bykongfm-->
    <select id="exportToExcel" resultType="com.yeejoin.amos.boot.module.common.api.dto.OrgUsrExcelDto">
         select
    a.biz_org_name bizOrgName,
    (select c.biz_org_name from cb_org_usr c  where c.sequence_nbr=a.parent_id) parentId,
    b.*
    from cb_org_usr a LEFT JOIN
    (SELECT
    instance_id,
    max(case field_code when 'administrativePositionCode' then field_value_label end)     administrativePositionCode,
    max(case field_code when 'auditCycle' then field_value_label end)                     auditCycle,
    max(case field_code when 'certificatesNumber' then field_value end)                   certificatesNumber,
    max(case field_code when 'certificatesTypeCode' then field_value_label end)           certificatesTypeCode,
    max(case field_code when 'certificateType' then field_value_label end)                certificateType,
    max(case field_code when 'fireManagementPostCode' then field_value_label end)         fireManagementPostCode,
    max(case field_code when 'gender' then field_value_label end)                         gender,
    max(case field_code when 'internalPositionCode' then field_value_label end)           internalPositionCode,
    max(case field_code when 'personNumber' then field_value end)                         personNumber,
    max(case field_code when 'positionType' then field_value_label end)                   positionType,
    max(case field_code when 'safetyTraining' then field_value_label end)                 safetyTraining,
    max(case field_code when 'stateCode' then field_value_label end)                      stateCode,
    max(case field_code when 'telephone' then field_value end)                            telephone
    FROM cb_dynamic_form_instance GROUP BY instance_id) b

    on b.instance_id=a.sequence_nbr where a.biz_org_name is not null and a.is_delete = 0
    </select>

<select id="getCompanyAndKeySite" resultType="com.yeejoin.amos.boot.module.common.api.dto.CheckObjectDto" >

    SELECT
        company_sur.sequence_nbr as sequenceNbr,
        company_sur.biz_org_name as bizOrgName ,
        company_sur.parent_id as parentId,
        company_sur.biz_org_type as bizOrgType,
--         cb.field_value_label,
        (SELECT field_value_label FROM cb_dynamic_form_instance as cb WHERE company_sur.sequence_nbr = cb.instance_id AND field_code = 'companyNature' ) as companyNature,
        CASE
        WHEN keysite_sur.num IS NULL THEN
            0
        ELSE
            keysite_sur.num
        END AS num
    FROM
        (
            SELECT
                company.sequence_nbr,
                company.parent_id,
                company.biz_org_name,
                company.biz_org_type
            FROM
                cb_org_usr company
            WHERE
                (company.biz_org_type = 'COMPANY' OR company.biz_org_type = 'DEPARTMENT')
            AND company.is_delete = FALSE
            <if test="companyId != null and companyId != ''">
                AND biz_org_code LIKE CONCAT((SELECT biz_org_code FROM cb_org_usr WHERE sequence_nbr = #{companyId}),'%')
            </if>
        ) company_sur
    LEFT JOIN (
        SELECT
            keysite.belong_id,
        COUNT(keysite.belong_id) as num
        FROM
            cb_key_site keysite
        WHERE
         keysite.is_delete = FALSE
        GROUP BY
            keysite.belong_id
    ) keysite_sur ON company_sur.sequence_nbr = keysite_sur.belong_id
--     LEFT JOIN cb_dynamic_form_instance as cb ON company_sur.sequence_nbr = cb.instance_id where field_code = 'companyNature'

</select>

    <!--BUG2657 导出选中单位及子单位人员数据 by kongfm-->
    <select id="exportPersonToExcelByParentId" resultType="com.yeejoin.amos.boot.module.common.api.dto.OrgUsrExcelDto">
         select
    a.biz_org_name bizOrgName,
    (select c.biz_org_name from cb_org_usr c  where c.sequence_nbr=a.parent_id) parentId,
    b.*
    from cb_org_usr a LEFT JOIN
    (SELECT
    instance_id,
    max(case field_code when 'administrativePositionCode' then field_value_label end)     administrativePositionCode,
    max(case field_code when 'auditCycle' then field_value_label end)                     auditCycle,
    max(case field_code when 'certificatesNumber' then field_value end)                   certificatesNumber,
    max(case field_code when 'certificatesTypeCode' then field_value_label end)           certificatesTypeCode,
    max(case field_code when 'certificateType' then field_value_label end)                certificateType,
    max(case field_code when 'fireManagementPostCode' then field_value_label end)         fireManagementPostCode,
    max(case field_code when 'gender' then field_value_label end)                         gender,
    max(case field_code when 'internalPositionCode' then field_value_label end)           internalPositionCode,
    max(case field_code when 'personNumber' then field_value end)                         personNumber,
    max(case field_code when 'positionType' then field_value_label end)                   positionType,
    max(case field_code when 'safetyTraining' then field_value_label end)                 safetyTraining,
    max(case field_code when 'stateCode' then field_value_label end)                      stateCode,
    max(case field_code when 'telephone' then field_value end)                            telephone
    FROM cb_dynamic_form_instance GROUP BY instance_id) b

    on b.instance_id=a.sequence_nbr where a.biz_org_name is not null and a.is_delete = 0
        and a.biz_org_type = "PERSON"

        <if test="parentId != null and parentId != -1 ">
            and a.biz_org_code like
            concat((select t.biz_org_code from cb_org_usr t where t.sequence_nbr = #{parentId}), '%')
        </if>


    </select>

    <select id="amosIdExist" resultType="com.yeejoin.amos.boot.module.common.api.entity.OrgUsr">
        SELECT *
        FROM cb_org_usr
        WHERE is_delete = 0
            amos_org_id = #{amosId}
    </select>

</mapper>
