<?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.zehong.system.mapper.TStoreyInfoMapper">
    
    <resultMap type="TStoreyInfo" id="TStoreyInfoResult">
        <result property="fStoreyId"    column="f_storey_id"    />
        <result property="fEquipmentId"    column="f_equipment_id"    />
        <result property="fEquipmentCode"    column="f_equipment_code"    />
        <result property="fStoreyCode"    column="f_storey_code"    />
        <result property="fTrayCode"    column="f_tray_code"    />
        <result property="fIp"    column="f_ip"    />
        <result property="fStatus"    column="f_status"    />
        <result property="fPort"    column="f_port"    />
        <result property="fAgingStartTime"    column="f_aging_start_time"    />
        <result property="fAgingEndTime"    column="f_aging_end_time"    />
        <result property="fUpdateTime"    column="f_update_time"    />
        <result property="fCreateTime"    column="f_create_time"    />
        <result property="fAlarmTime"    column="f_alarm_time"    />

        <result property="blankingCommand"    column="f_blanking_command"    />
        <result property="feedingCommand"    column="f_feeding_command"    />
    </resultMap>

    <sql id="selectTStoreyInfoVo">
        select storeyInfo.f_storey_id,
               storeyInfo.f_equipment_id,
               storeyInfo.f_storey_code,
               storeyInfo.f_ip,
               storeyInfo.f_status,
               storeyInfo.f_port,
               storeyInfo.f_aging_start_time,
               storeyInfo.f_aging_end_time,
               storeyInfo.f_update_time,
               storeyInfo.f_create_time,
               storeyInfo.f_alarm_time,
               storeyInfo.f_blanking_command,
               storeyInfo.f_feeding_command,
               trayInfo.f_tray_code,
               equipmentInfo.f_equipment_code
        from t_storey_info storeyInfo
        left join t_tray_info trayInfo on storeyInfo.f_storey_code = trayInfo.f_storey_code
        left join t_equipment_info equipmentInfo on storeyInfo.f_equipment_id = equipmentInfo.f_equipment_id
    </sql>

    <select id="selectByEquipmentIds" parameterType="list" resultMap="TStoreyInfoResult">
        <include refid="selectTStoreyInfoVo"/>
        where storeyInfo.f_equipment_id in
        <foreach item="item" collection="list" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="selectTStoreyInfoList" parameterType="TStoreyInfo" resultMap="TStoreyInfoResult">
        <include refid="selectTStoreyInfoVo"/>
        <where>
            <if test="fEquipmentCode != null "> and equipmentInfo.f_equipment_code like concat('%',#{fEquipmentCode},'%')</if>
            <if test="fStoreyCode != null  and fStoreyCode != ''"> and storeyInfo.f_storey_code like concat('%',#{fStoreyCode},'%')</if>
            <if test="fIp != null  and fIp != ''"> and storeyInfo.f_ip like concat concat('%',#{fIp},'%')</if>
            <if test="fStatus != null  and fStatus != ''"> and storeyInfo.f_status = #{fStatus}</if>
            <if test="fPort != null "> and storeyInfo.f_port = #{fPort}</if>
            <if test="fUpdateTime != null "> and storeyInfo.f_update_time = #{fUpdateTime}</if>
            <if test="fCreateTime != null "> and storeyInfo.f_create_time = #{fCreateTime}</if>
            <if test="fAlarmTime != null "> and storeyInfo.f_alarm_time = #{fAlarmTime}</if>
        </where>
        order by storeyInfo.f_storey_id asc
    </select>
    <select id="queryByDepartmentId" parameterType="long" resultMap="TStoreyInfoResult">
        <include refid="selectTStoreyInfoVo"/>
        where storeyInfo.f_equipment_id = #{fEquipmentId}
        order by storeyInfo.f_storey_id asc
    </select>
    
    <select id="selectTStoreyInfoById" parameterType="Long" resultMap="TStoreyInfoResult">
        <include refid="selectTStoreyInfoVo"/>
        where f_storey_id = #{fStoreyId}
    </select>
    <select id="selectByBlankingCommand" parameterType="string" resultMap="TStoreyInfoResult">
        <include refid="selectTStoreyInfoVo"/>
        where storeyInfo.f_blanking_command = #{blankingCommand}
    </select>

    <select id="selectTStoreyInfoByCode" parameterType="String" resultMap="TStoreyInfoResult">
        <include refid="selectTStoreyInfoVo"/>
        where storeyInfo.f_storey_code = #{fStoreyCode}
    </select>

    <select id="selectNearestFreeStorey" resultMap="TStoreyInfoResult">
        SELECT
            f_storey_id,
            f_equipment_id,
            f_storey_code,
            f_ip,
            f_status,
            f_port,
            f_aging_start_time,
            f_aging_end_time,
            f_update_time,
            f_create_time,
            f_alarm_time,
            f_blanking_command,
            f_feeding_command
        FROM (
                 SELECT
                     *,
                     -- 计算距离：北边（1-15）距离范围1-150，南边（16-30）距离范围151-300（确保北边优先）
                     CASE
                         -- 北边柜子：(柜子编号-1)*10 + 层号（1-1→1，1-10→10，15-10→150）
                         WHEN cabinet_num BETWEEN 1 AND 15 THEN (cabinet_num - 1) * 10 + layer_num
                         -- 南边柜子：在北边最大距离（150）基础上累加，确保所有南边距离>北边（16-1→151，30-10→300）
                         WHEN cabinet_num BETWEEN 16 AND 30 THEN 150 + (cabinet_num - 16) * 10 + layer_num
                         END AS distance
                 FROM (
                          SELECT
                              *,
                              -- 拆分柜子编号（如1-1中的“1”）
                              CAST(SUBSTRING_INDEX(f_storey_code, '-', 1) AS UNSIGNED) AS cabinet_num,
                              -- 拆分层号（如1-1中的“1”）
                              CAST(SUBSTRING_INDEX(f_storey_code, '-', -1) AS UNSIGNED) AS layer_num
                          FROM t_storey_info
                          WHERE f_status = '0'  -- 只筛选空闲状态
                      ) AS parsed
             ) AS calculated
-- 按距离升序排序（北边先于南边，同区域内近的在前）
        ORDER BY distance ASC
            LIMIT 1;
    </select>
    <insert id="insertBatch" parameterType="list">
        insert into t_storey_info ( f_equipment_id, f_storey_code, f_ip, f_status, f_port, f_create_time) values
        <foreach item="item" index="index" collection="storeyInfos" separator=",">
            ( #{item.fEquipmentId}, #{item.fStoreyCode}, #{item.fIp}, #{item.fStatus}, #{item.fPort},  #{item.fCreateTime})
        </foreach>
    </insert>
    <insert id="insertTStoreyInfo" parameterType="TStoreyInfo">
        insert into t_storey_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="fStoreyId != null">f_storey_id,</if>
            <if test="fEquipmentId != null">f_equipment_id,</if>
            <if test="fStoreyCode != null">f_storey_code,</if>
            <if test="fIp != null">f_ip,</if>
            <if test="fStatus != null">f_status,</if>
            <if test="fPort != null">f_port,</if>
            <if test="fUpdateTime != null">f_update_time,</if>
            <if test="fCreateTime != null">f_create_time,</if>
            <if test="fAlarmTime != null">f_alarm_time,</if>

            <if test="blankingCommand != null">f_blanking_command,</if>
            <if test="feedingCommand != null">f_feeding_command,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="fStoreyId != null">#{fStoreyId},</if>
            <if test="fEquipmentId != null">#{fEquipmentId},</if>
            <if test="fStoreyCode != null">#{fStoreyCode},</if>
            <if test="fIp != null">#{fIp},</if>
            <if test="fStatus != null">#{fStatus},</if>
            <if test="fPort != null">#{fPort},</if>
            <if test="fUpdateTime != null">#{fUpdateTime},</if>
            <if test="fCreateTime != null">#{fCreateTime},</if>
            <if test="fAlarmTime != null">#{fAlarmTime},</if>

            <if test="blankingCommand != null">#{blankingCommand},</if>
            <if test="feedingCommand != null">#{feedingCommand},</if>
         </trim>
    </insert>

    <update id="updateBatch" parameterType="list">
        <foreach collection="storeyInfos" item="item"  index="index" separator=";">
            update t_storey_info
            <trim prefix="SET" suffixOverrides=",">
                <if test="item.blankingCommand != null">f_blanking_command = #{item.blankingCommand},</if>
                <if test="item.feedingCommand != null">f_feeding_command = #{item.feedingCommand},</if>
            </trim>
            where f_storey_id = #{item.fStoreyId}
        </foreach>
    </update>
    <update id="updateTStoreyInfo" parameterType="TStoreyInfo">
        update t_storey_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="fEquipmentId != null">f_equipment_id = #{fEquipmentId},</if>
            <if test="fStoreyCode != null">f_storey_code = #{fStoreyCode},</if>
            <if test="fIp != null">f_ip = #{fIp},</if>
            <if test="fStatus != null">f_status = #{fStatus},</if>
            <if test="fPort != null">f_port = #{fPort},</if>
            <if test="fAgingStartTime != null">f_aging_start_time = #{fAgingStartTime},</if>
            <if test="fAgingStartTime == null">f_aging_start_time = null,</if>
            <if test="fAgingEndTime != null">f_aging_end_time = #{fAgingEndTime},</if>
            <if test="fAgingEndTime == null">f_aging_end_time = null,</if>
            <if test="fUpdateTime != null">f_update_time = #{fUpdateTime},</if>
            <if test="fCreateTime != null">f_create_time = #{fCreateTime},</if>
            <if test="fAlarmTime != null">f_alarm_time = #{fAlarmTime},</if>
            <if test="fAlarmTime == null">f_alarm_time = null,</if>

            <if test="blankingCommand != null">f_blanking_command = #{blankingCommand},</if>
            <if test="feedingCommand != null">f_feeding_command = #{feedingCommand},</if>
        </trim>
        where f_storey_id = #{fStoreyId}
    </update>
    <update id="updateAllStatusAndAgingStartTime" parameterType="TStoreyInfo">
        update t_storey_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="fStatus != null">f_status = #{fStatus},</if>
            <if test="fAgingStartTime != null">f_aging_start_time = #{fAgingStartTime},</if>
            <if test="fAgingStartTime == null">f_aging_start_time = null,</if>
            <if test="fAgingEndTime != null">f_aging_end_time = #{fAgingEndTime},</if>
            <if test="fAgingEndTime == null">f_aging_end_time = null,</if>
        </trim>
        where f_storey_id = #{fStoreyId}
    </update>

    <update id="unbindByCode" parameterType="TStoreyInfo">
        update t_storey_info
        set f_status = '0', f_aging_start_time = null,f_aging_end_time = null
        where f_storey_code = #{fStoreyCode}
    </update>
    <update id="updateStatusByCode" parameterType="TStoreyInfo">
        update t_storey_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="fStatus != null">f_status = #{fStatus},</if>
            <if test="fUpdateTime != null">f_update_time = #{fUpdateTime},</if>
        </trim>
        where f_storey_code = #{fStoreyCode}
    </update>
    <delete id="deleteTStoreyInfoById" parameterType="Long">
        delete from t_storey_info where f_storey_id = #{fStoreyId}
    </delete>

    <delete id="deleteTStoreyInfoByIds" parameterType="String">
        delete from t_storey_info where f_storey_id in 
        <foreach item="fStoreyId" collection="array" open="(" separator="," close=")">
            #{fStoreyId}
        </foreach>
    </delete>
</mapper>