<?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 人员关系显示汉字 -->
	<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>

	<select id="getFirefightersName" resultType="string">
		SELECT
		       a.name
		FROM
		     cb_firefighters a
		WHERE
		      a.is_delete =0
		  and
		      a.name like concat ('%',#{contactName},'%')
	</select>

</mapper>
