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

    <select id="listMonitorFireBrigade" resultType="com.yeejoin.amos.boot.module.common.api.dto.FireBrigadeResourceDto">
        SELECT ft.sequence_nbr                 id,
               ft.NAME,
               ft.img AS pic,
               COUNT(DISTINCT ff.sequence_nbr) personCount
        FROM cb_fire_team ft
                 LEFT JOIN cb_firefighters ff ON ff.fire_team_id = ft.sequence_nbr
        WHERE ft.is_delete = 0
          and ft.type_code = #{code}
        GROUP BY ft.sequence_nbr
    </select>
    <select id="queryFighterByTeamId" resultType="com.yeejoin.amos.boot.module.common.api.dto.FirefightersDto">
        SELECT
        ff.*,
        (select name from cb_fire_team where sequence_nbr = ff.fire_team_id) fireTeamName
        FROM
        cb_firefighters ff
        where is_delete = 0
        and fire_team_id in
        <if test="teamIds != null">
            <foreach collection="teamIds" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>

    </select>
    <!--消防队伍列表按时间倒叙排列order by x.rec_date desc 2021-09-08 by kongfm -->
    <select id="getFireTeamForPage" resultType="com.yeejoin.amos.boot.module.common.api.dto.FireTeamCardDto">
        SELECT
        a.sequence_nbr sequenceNbr,
        a.img,
        a.NAME,
        a.contact_user contactUser,
        a.contact_phone contactPhone,
        a.address
        FROM
        cb_fire_team a
        WHERE
        a.is_delete = 0
        <if test='par.parent != null'>and a.parent = #{par.parent}</if>
        <if test='par.typeCode != null'>and a.type_code = #{par.typeCode}</if>
        <if test='par.companyCode != null'>and a.company_code = #{par.companyCode}</if>
        <if test='par.name != null'>and a.name like concat('%', #{par.name}, '%')</if>
        <if test='par.nodeType != null and par.nodeType == "1" and par.nodeIds != null'>
            and a.company in
            <foreach collection="par.nodeIds" separator="," open="(" item="nodeId" close=")">
                #{nodeId}
            </foreach>
        </if>
        <if test='par.nodeType != null and par.nodeType == "0" and par.nodeIds != null'>
            <if test="par.nodeIds.size() > 0" >
                and a.parent in
                <foreach collection="par.nodeIds" separator="," open="(" item="nodeId" close=")">
                    #{nodeId}
                </foreach>
                or a.sequence_nbr = #{par.nodeId}
            </if>
            <if test="par.nodeIds.size() == 0" >
                and a.sequence_nbr = #{par.nodeId}
            </if>
        </if>
        order by a.rec_date desc
    </select>
    <select id="listFireTeamDto" resultType="com.yeejoin.amos.boot.module.common.api.dto.FireTeamDto">
        select t.*, p.name parentName
        from cb_fire_team t
                 left join cb_fire_team p on t.parent = p.sequence_nbr
        where t.is_delete = #{isDelete}
    </select>

    <!--消防队伍列表按时间倒叙排列order by x.rec_date desc 2021-09-08 by kongfm -->
    <!--BUG 2846 最初开发人员在动态语句后加入分号导致错误  2021-09-10 by kongfm -->
    <select id="listFireTeamById" resultType="com.yeejoin.amos.boot.module.common.api.entity.FireTeam">
        SELECT *
        FROM cb_fire_team
        WHERE is_delete = 0
        <if test="teamId != null">
            AND tree_code LIKE CONCAT((SELECT tree_code FROM cb_fire_team WHERE sequence_nbr = #{teamId}),'%')
        </if>
        order by cb_fire_team.rec_date desc
    </select>



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

        SELECT
        a.sequence_nbr sequenceNbr,
        a.company_name companyName,
        a.longitude,
        a.latitude,
        a.address,
        a.type ,
        a.type_code typeCode,
        a.name ,
        a.contact_user contactUser,
        a.contact_phone contactPhone,
        ( SELECT count( 1 ) FROM cb_firefighters WHERE fire_team_id = a.sequence_nbr AND is_delete = 0 ) userNum,
        Round(st_distance(point(a.longitude,a.latitude),point(#{par.longitude},#{par.latitude}))*111195,1) AS distance
        FROM cb_fire_team a
        where  a.is_delete=0 and a.longitude is not null and a.latitude   is not null
        <if test='par.typeCode!=null and par.typeCode!=""'>
            and  a.type_code in (#{par.typeCode})
        </if>
        <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="getFireTeamListCount" resultType="Integer">
        SELECT
        COUNT(a.sequence_nbr) num
        FROM cb_fire_team a
        where  a.is_delete=0 and a.longitude is not null and a.latitude   is not null

        <if test='par.typeCode!=null and par.typeCode!=""'>
            and  a.type_code in (#{par.typeCode})
        </if>
        <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="getFireTeamCountList" resultType="com.yeejoin.amos.boot.module.common.api.dto.FireTeamZhDto">

        SELECT
        a.name ,
        a.sequence_nbr sequenceNbr,
        ( SELECT count( 1 ) FROM cb_firefighters WHERE fire_team_id = a.sequence_nbr AND is_delete = 0 ) userNum
        FROM cb_fire_team a
        where  a.is_delete=0


    </select>


    <select id="getFighterNumByTeamId" resultType="Integer">
        SELECT
			count(1)
		FROM
			cb_firefighters
		WHERE
                fire_team_id = #{teamId}
				AND is_delete = 0
    </select>

    <select id="byTeamId" resultType="com.yeejoin.amos.boot.module.common.api.entity.FireTeam">
        SELECT
            *
        FROM
            cb_fire_team
        WHERE
            company_code = #{teamId}
          AND is_delete = 0
    </select>



</mapper>
