<?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.LinkageUnitMapper">
	<select id="selectOne"
		resultType="com.yeejoin.amos.boot.module.common.api.dto.LinkageUnitDto">
		select
		d.*,
		lu.*
		from
		(
		select
		i.INSTANCE_ID instanceId,
		i.GROUP_CODE
		groupCode,
		<foreach collection="fieldCodes" item="value" index="key"
			separator=",">
			MAX(CASE WHEN i.FIELD_CODE = #{key} THEN i.FIELD_VALUE END)
			as ${key}
		</foreach>
		from
		cb_dynamic_form_instance i
		where i.GROUP_CODE = #{groupCode}
		and
		is_delete = #{isDelete}
		GROUP by
		i.INSTANCE_ID ) d,
		cb_linkage_unit lu
		where
		d.instanceId = lu.instance_id
		and lu.sequence_nbr = #{sequenceNbr}
		and is_delete = #{isDelete}
	</select>




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

		select
		a.sequence_nbr sequenceNbr,
		a.unit_name unitName,
		a.address,
		a.latitude,
		a.longitude,
		Round(st_distance(point(a.longitude,a.latitude),point(#{par.longitude},#{par.latitude}))*111195,1)
		AS distance,
		case when csps.person_number is null then 0 else
		csps.person_number end as userNum,
		case when cre.vehicle_number is null then 0 else cre.vehicle_number end as
		carNum
		FROM
		cb_linkage_unit a
		LEFT JOIN cb_special_position_staff csps ON a.sequence_nbr =
		csps.company_id
		LEFT JOIN cb_rescue_equipment cre on a.sequence_nbr = cre.company_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="listLinkageUnitZhDtoCount" resultType="Integer">
		select
		COUNT(a.sequence_nbr) num
		FROM cb_linkage_unit 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="getEmergencyLinkageUnitCodeGroupByAndCount"
		resultType="java.util.Map">
		SELECT
		emergency_linkage_unit_code as
		emergencyLinkageUnitCode,
		COUNT(unit_code) AS count
		FROM
		cb_linkage_unit
		WHERE
		is_delete = 0
		GROUP BY
		emergency_linkage_unit_code
	</select>

	<select id="exportToExcel"
		resultType="com.yeejoin.amos.boot.module.common.api.dto.LinkageUnitDto">
		select
		a.unit_name unitName,
		a.linkage_unit_type
		linkageUnitType,
		a.address ,
		a.latitude,
		a.longitude,
		a.agreement_start_date agreementStartDate,
		a.agreement_end_date
		agreementEndDate,
		a.contact_user contactUser,
		a.contact_phone
		contactPhone,
		b.*
		from cb_linkage_unit a LEFT JOIN
		(SELECT
		m.instance_id,
		max(case m.field_code when 'emergencyServiceContent' then
		m.field_value end) emergencyServiceContent,
		max(case m.field_code when
		'fireRescueCapability' then m.field_value end) fireRescueCapability,
		max(case m.field_code when 'responsibilitiesSituation' then
		m.field_value end) responsibilitiesSituation,
		max(case m.field_code
		when 'unitSituation' then m.field_value end) unitSituation
		FROM
		cb_dynamic_form_instance m GROUP BY m.instance_id) b

		on
		b.instance_id=a.instance_id where a.unit_name is not null
	</select>

	<!--联动单位列表按时间倒叙排列add order by clu.rec_date desc 同时处理单位根节点-1时查询全部数据问题 2021-09-08 by kongfm -->
	<select id="getEmergencyLinkageUnitList"
		resultType="java.util.Map">
		SELECT
		clu.sequence_nbr as sequenceNbr,
		clu.unit_name as unitName,
		clu.unit_code AS unitCode,
		clu.parent_id AS parentId,
		clu.linkage_unit_type AS linkageUnitType,
		clu.linkage_unit_type_code AS
		linkageUnitTypeCode,
		clu.administrative_divisions AS
		administrativeDivisions,
		clu.administrative_divisions_code AS
		administrativeDivisionsCode,
		clu.address AS address,
		clu.longitude AS
		longitude,
		clu.latitude AS latitude,
		clu.agreement_start_date AS
		agreementStartDate,
		clu.agreement_end_date AS agreementEndDate,
		clu.emergency_linkage_unit AS emergencyLinkageUnit,
		clu.emergency_linkage_unit_code AS emergencyLinkageUnitCode,
		clu.contact_user AS contactUser,
		clu.contact_phone AS contactPhone,
		clu.instance_id AS instanceId,
		clu.org_code AS orgCode,
		clu.rec_user_name AS recUserName,
		clu.rec_user_id AS recUserId,
		clu.rec_date AS recDate,
		clu.is_delete AS isDelete,
		(
		SELECT
		sum(cre.vehicle_number)
		FROM
		cb_rescue_equipment cre
		WHERE
		clu.sequence_nbr = cre.company_id
		) AS vehicleNumber,
		(
		SELECT
		sum(csps.person_number)
		FROM
		cb_special_position_staff csps
		WHERE
		clu.sequence_nbr = csps.company_id
		) AS personNumber
		FROM
		cb_linkage_unit clu
		WHERE clu.is_delete=0
		<if test="unitName != null and unitName != ''">
			AND clu.unit_name LIKE concat(#{unitName}, '%')
		</if>
		<if
			test="linkageUnitType != null and linkageUnitType != ''">
			AND clu.linkage_unit_type =#{linkageUnitType}
		</if>
		<if
			test="emergencyLinkageUnitCode != null and emergencyLinkageUnitCode != '' ">
			<if test="emergencyLinkageUnitCode != '-1'">
				AND clu.emergency_linkage_unit_code =#{emergencyLinkageUnitCode}
			</if>
		</if>
		<if
			test="linkageUnitTypeCode != null and linkageUnitTypeCode != ''">
			AND clu.linkage_unit_type_code =#{linkageUnitTypeCode}
		</if>
		order by clu.rec_date desc
	</select>
</mapper>
