<?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.biz.common.dao.mapper.DataDictionaryMapper">
<select id="getDataDictionaryListAndCount" resultType="com.yeejoin.amos.boot.biz.common.entity.DataDictionary">

SELECT
	cdd.*, count(cfe.sequence_nbr) AS count
FROM
	cb_data_dictionary cdd
LEFT JOIN (
	SELECT
		*
	FROM
		cb_fire_experts
	WHERE
		is_delete = 0
) cfe ON cdd.`code` = cfe.expert_code
WHERE
	cdd.type = 'ZJLY'
AND cdd.is_delete = 0
GROUP BY
	cdd. CODE ORDER BY sort_num 


</select>




<select id ="getNoInLinkUnit" resultType="com.yeejoin.amos.boot.biz.common.entity.DataDictionary">
	SELECT
		CONCAT(cbb.`name`,' (',IFNULL(elink.count, 0),')') as `name`,
		cbb.*,
		elink.count as count
	FROM
		cb_data_dictionary cbb
		LEFT JOIN (
		SELECT
		emergency_linkage_unit_code,
		COUNT(1) count
		FROM
		cb_linkage_unit
		WHERE is_delete = 0
		GROUP BY
		emergency_linkage_unit_code
		) elink ON elink.emergency_linkage_unit_code = cbb.`code`
	WHERE
		cbb.type = 'YJLDDW' and cbb.is_delete = 0
</select>

	<select id ="getByCode" resultType="com.yeejoin.amos.boot.biz.common.entity.DataDictionary">
		SELECT
			*
		FROM
			cb_data_dictionary cbb

		WHERE
			cbb.code = #{code} and cbb.is_delete = 0 and cbb.type = #{type}
	</select>
	
	<select id="getFireTeamTypeTree" resultType="com.yeejoin.amos.boot.biz.common.entity.DataDictionary">
	SELECT
		cdd.*,getChildLstCode(cdd.sequence_nbr) treeCode,  case when fire.count is null then 0 else fire.count end as count
	FROM
		cb_data_dictionary cdd
	LEFT JOIN (
		SELECT
			cf.type_code,
			COUNT(cf.sequence_nbr) AS count
		FROM
			cb_fire_team cf
		WHERE
			cf.is_delete = 0
		<if test='bizOrgCode != null and bizOrgCode!=""'>and cf.biz_org_code like CONCAT(  #{bizOrgCode},'%')</if>
		GROUP BY
		cf.type_code
	) fire ON cdd.`code` = fire.type_code
	WHERE
	cdd.type = 'XFJGLX'
	</select>

	<select id ="getFirefightersJobTitle" resultType="com.yeejoin.amos.boot.biz.common.entity.DataDictionary">
		SELECT
			cbb.*,getChildLstCode(cbb.sequence_nbr) treeCode
		FROM
			cb_data_dictionary cbb

		WHERE
			cbb.type = #{type}
	</select>



	<select id="getwaterResourceTypeTree" resultType="com.yeejoin.amos.boot.biz.common.entity.DataDictionary">
	SELECT
	cdd.*, case when fire.count is null then 0 else fire.count end as count
FROM
	cb_data_dictionary cdd
LEFT JOIN (
	SELECT
		cf.resource_type,
		COUNT(cf.sequence_nbr) AS count
	FROM
		cb_water_resource cf
	WHERE
		cf.is_delete = 0
	<if test='bizOrgCode != null and bizOrgCode!=""'>and cf.biz_org_code like CONCAT(  #{bizOrgCode},'%')</if>
	GROUP BY
		cf.resource_type
) fire ON cdd.`code` = fire.resource_type
WHERE
	cdd.type = 'XFSYLX'
	
	</select>
	
	
</mapper>
