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

	<select id="getFirefightersJobTitleCount"
		resultType="com.yeejoin.amos.boot.biz.common.utils.FirefightersTreeDto">
		select COUNT(a.sequence_nbr) num, a.job_title_code
		jobTitleCode
		from cb_firefighters a
		where a.is_delete = 0
		GROUP BY
		a.job_title_code
	</select>
	<!--消防队员列表按时间倒叙排列add desc 2021-09-08 by kongfm -->
	<select id="getFirefighters"
		resultType="com.yeejoin.amos.boot.module.common.api.dto.FirefightersZhDto">
		select a.*,b.areas_expertise areasExpertise ,b.areas_expertise_code
		areasExpertiseCode from cb_firefighters a LEFT JOIN
		cb_firefighters_post b on
		a.sequence_nbr=b.firefighters_id where
		a.is_delete=0
		<if test='par.postQualification!=null'>and b.post_qualification_code = #{par.postQualification}</if>
		<if test='par.areasExpertise!=null'>and b.areas_expertise_code= #{par.areasExpertise}</if>
		<if test='par.name!=null'>and a.name like concat ('%',#{par.name},'%')</if>
		<if test='par.state!=null'>and a.state_code= #{par.state}</if>
		<if test='par.fireTeamId!=null'>and a.fire_team_id= #{par.fireTeamId}</if>
		<if test='par.jobTitle!=null'>and a.job_title_code =#{par.jobTitle}</if>
		<if
			test='par.areasExpertiseCode!=null and par.areasExpertiseCode!="0"'>and b.areas_expertise_code =#{par.areasExpertiseCode}</if>
		<if test='par.areasExpertiseCode=="0"'>and b.areas_expertise_code is not null</if>
		ORDER BY a.rec_date desc limit #{pageNum},#{pageSize}
	</select>
	<!--消防队员列表按时间倒叙排列add desc 2021-09-08 by kongfm -->
	<select id="getFirefightersCount" resultType="Map">
		select COUNT(a.sequence_nbr) num from cb_firefighters a LEFT JOIN
		cb_firefighters_post b on a.sequence_nbr=b.firefighters_id where
		a.is_delete=0
		<if test='par.postQualification!=null'>and b.post_qualification_code = #{par.postQualification}</if>
		<if test='par.areasExpertise!=null'>and b.areas_expertise_code= #{par.areasExpertise}</if>
		<if test='par.name!=null'>and a.name like concat ('%',#{par.name},'%')</if>
		<if test='par.state!=null'>and a.state_code= #{par.state}</if>
		<if test='par.fireTeamId!=null'>and a.fire_team_id= #{par.fireTeamId}</if>
		<if test='par.jobTitle!=null'>and a.job_title_code =#{par.jobTitle}</if>
		<if
			test='par.areasExpertiseCode!=null and par.areasExpertiseCode!="0"'>and b.areas_expertise_code =#{par.areasExpertiseCode}</if>
		<if test='par.areasExpertiseCode=="0"'>and b.areas_expertise_code is not null</if>

		ORDER BY a.rec_date desc
	</select>


	<select id="listToSelectById" resultType="Map">
		SELECT
		IFNULL(a.personnel_photos, '') personnelPhotos,
		a.sequence_nbr
		sequenceNbr,
		IFNULL(a.`name`, '无') `name`,
		IFNULL(a.job_title, '无')
		jobTitle,
		IFNULL(b.administrative_position, '无')
		administrativePosition,
		IFNULL(c.`name`, '无') fireTeamName,
		IFNULL(a.state, '无') state,
		IFNULL(b.employee_hierarchy, '无')
		employeeHierarchy,
		IFNULL(b.areas_expertise, '无') areasExpertise,
		IFNULL(a.gender, '无') gender,
		IFNULL(b.post_qualification, '无')
		postQualification, year ( from_days( datediff( now( ),
		a.birthday_time))) age
		FROM cb_firefighters a LEFT JOIN
		cb_firefighters_post b
		ON a.sequence_nbr
		= b.firefighters_id LEFT JOIN
		cb_fire_team c on
		c.sequence_nbr=a.fire_team_id
		WHERE a.is_delete =0
		and
		a.sequence_nbr=#{id}
	</select>


	<!-- BUG3553 BY kongfm 人员关系显示汉字 -->
	<!---陈浩修改导出的数据量 2021-09-13-->
	<select id="exportToExcel"
		resultType="com.yeejoin.amos.boot.module.common.api.dto.FirefightersExcelDto">
		<!-- SELECT f.*, ( SELECT cb_fire_team.NAME FROM cb_fire_team WHERE cb_fire_team.sequence_nbr 
			= f.fire_team_id ) fireTeam, emergency_contact, da.NAME AS relationship, 
			emergency_contact_phone FROM cb_firefighters f LEFT JOIN cb_firefighters_contacts 
			fc ON f.sequence_nbr = fc.firefighters_id left join cb_data_dictionary da 
			on da.CODE = fc.relationship where f.is_delete = #{isDelete} -->
		SELECT
	f.*,
	(
		SELECT
			cb_fire_team. NAME
		FROM
			cb_fire_team
		WHERE
			cb_fire_team.sequence_nbr = f.fire_team_id
	) fireTeam,
	emergency_contact,
	(
		SELECT
			NAME
		FROM
			cb_data_dictionary
		WHERE
			CODE = fc.relationship
		AND type = 'RJGX'
	) AS relationship,
	emergency_contact_phone,
	fw.working_hours,
	fw.fire_working_hours,
	(
		SELECT
			NAME
		FROM
			cb_data_dictionary
		WHERE
			CODE = fe.first_degree
		AND type = 'XLLX'
	) AS first_degree,
	(
		SELECT
			NAME
		FROM
			cb_data_dictionary
		WHERE
			CODE = fe.highest_education
		AND type = 'XLLX'
	) AS highest_education,
	(
		SELECT
			NAME
		FROM
			cb_data_dictionary
		WHERE
			CODE = fe.academic_degree
		AND type = 'XWLX'
	) AS academic_degree,
	fe.school,
	fe.professional_name,
	fp.*
FROM
	cb_firefighters f
LEFT JOIN cb_firefighters_contacts fc ON f.sequence_nbr = fc.firefighters_id
LEFT JOIN cb_firefighters_workexperience fw ON f.sequence_nbr = fw.firefighters_id
LEFT JOIN cb_firefighters_education fe ON f.sequence_nbr = fe.firefighters_id
LEFT JOIN cb_firefighters_post fp ON f.sequence_nbr = fp.firefighters_id
WHERE
	f.is_delete = 0
AND fc.is_delete = 0
AND fw.is_delete = 0
AND fe.is_delete = 0
AND fp.is_delete = 0
	</select>

	<select id="getFirefightersName" resultType="string">
		SELECT
			cb_firefighters.name
		FROM
			cb_firefighters
		WHERE
			cb_firefighters.is_delete = 0 AND
			cb_firefighters.name IS NOT NULL AND
			cb_firefighters.name != ''
		group by cb_firefighters.name
	</select>

</mapper>
