<?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.TTrayInfoMapper">
    
    <resultMap type="TTrayInfo" id="TTrayInfoResult">
        <result property="fTrayId"    column="f_tray_id"    />
        <result property="fTrayCode"    column="f_tray_code"    />
        <result property="fStoreyCode"    column="f_storey_code"    />
        <result property="fStatus"    column="f_status"    />
        <result property="fBindingTime"    column="f_binding_time"    />
        <result property="fUnbindingTime"    column="f_unbinding_time"    />
        <result property="fCreateTime"    column="f_create_time"    />
        <result property="fAgingStartTime"    column="f_aging_start_time"    />
    </resultMap>

    <sql id="selectTTrayInfoVo">
        SELECT
            trayInfo.f_tray_id,
            trayInfo.f_tray_code,
            trayInfo.f_storey_code,
            trayInfo.f_status,
            trayInfo.f_binding_time,
            trayInfo.f_unbinding_time,
            trayInfo.f_create_time,
            ( SELECT count( 1 ) FROM t_pallet_device_binding WHERE f_tray_id = trayInfo.f_tray_id AND ( f_device_code != '' ) AND f_unbinding_time IS NULL ) AS boardCount ,
            storeyInfo.f_aging_start_time
        FROM
            t_tray_info trayInfo
                LEFT JOIN t_storey_info storeyInfo ON trayInfo.f_storey_code = storeyInfo.f_storey_code
    </sql>

    <select id="selectTTrayInfoList" parameterType="TTrayInfo" resultMap="TTrayInfoResult">
        <include refid="selectTTrayInfoVo"/>
        <where>  
            <if test="fTrayCode != null  and fTrayCode != ''"> and trayInfo.f_tray_code like concat('%',#{fTrayCode},'%')</if>
            <if test="fStoreyCode != null  and fStoreyCode != ''"> and trayInfo.f_storey_code = #{fStoreyCode}</if>
            <if test="fStatus != null  and fStatus != ''"> and trayInfo.f_status = #{fStatus}</if>
            <if test="fBindingTime != null "> and trayInfo.f_binding_time = #{fBindingTime}</if>
            <if test="fUnbindingTime != null "> and trayInfo.f_unbinding_time = #{fUnbindingTime}</if>
            <if test="fCreateTime != null "> and trayInfo.f_create_time = #{fCreateTime}</if>
        </where>
        ORDER BY trayInfo.f_tray_id
    </select>
    
    <select id="selectTTrayInfoById" parameterType="Long" resultMap="TTrayInfoResult">
        <include refid="selectTTrayInfoVo"/>
        where trayInfo.f_tray_id = #{fTrayId}
    </select>
    <select id="selectTTrayInfoByCode" parameterType="string" resultMap="TTrayInfoResult">
        <include refid="selectTTrayInfoVo"/>
        where trayInfo.f_tray_code = #{fTrayCode}
    </select>
    <select id="selectTTrayInfoByStoreyCode" parameterType="string" resultMap="TTrayInfoResult">
        <include refid="selectTTrayInfoVo"/>
        where trayInfo.f_storey_code = #{fStoreyCode}
    </select>
        
    <insert id="insertTTrayInfo" parameterType="TTrayInfo" useGeneratedKeys="true" keyProperty="fTrayId">
        insert into t_tray_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="fTrayCode != null">f_tray_code,</if>
            <if test="fStoreyCode != null">f_storey_code,</if>
            <if test="fStatus != null">f_status,</if>
            <if test="fBindingTime != null">f_binding_time,</if>
            <if test="fUnbindingTime != null">f_unbinding_time,</if>
            <if test="fCreateTime != null">f_create_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="fTrayCode != null">#{fTrayCode},</if>
            <if test="fStoreyCode != null">#{fStoreyCode},</if>
            <if test="fStatus != null">#{fStatus},</if>
            <if test="fBindingTime != null">#{fBindingTime},</if>
            <if test="fUnbindingTime != null">#{fUnbindingTime},</if>
            <if test="fCreateTime != null">#{fCreateTime},</if>
         </trim>
    </insert>

    <insert id="batchInsertTTrayInfo" parameterType="list" >
        insert into t_tray_info (f_tray_code, f_storey_code, f_status, f_binding_time, f_unbinding_time, f_create_time) values
        <foreach item="item" index="index" collection="list" separator=",">
            (#{item.fTrayCode}, #{item.fStoreyCode}, #{item.fStatus}, #{item.fBindingTime}, #{item.fUnbindingTime}, #{item.fCreateTime})
        </foreach>
    </insert>

    <update id="updateStatusByStoreyCode" parameterType="TTrayInfo">
        update t_tray_info
        set f_status = #{fStatus}
        where f_storey_code = #{fStoreyCode}
    </update>
    <update id="updateStatusByTrayCode" parameterType="TTrayInfo">
        update t_tray_info
        set f_status = #{fStatus}
        where f_tray_code = #{fTrayCode}
    </update>
    <update id="updateStatusByTrayId" parameterType="TTrayInfo">
        update t_tray_info
        set f_status = #{fStatus}
        where f_tray_id = #{fTrayId}
    </update>
    <update id="updateToBoundWhenStateIsIdle" parameterType="long">
        update t_tray_info
        set f_status = '4' where f_status = '0' and f_tray_id = #{fTrayId}
    </update>
    <update id="clearStoreyCodeByStoreyCode" parameterType="string">
        update t_tray_info
        set f_storey_code = null,
            f_unbinding_time = NOW()
        where f_storey_code = #{storeyCode}
    </update>
    <update id="initStatusByTrayId" parameterType="long">
        update t_tray_info
        set f_status = '0'
        where f_tray_id = #{fTrayId}
    </update>
    <update id="updateTTrayInfo" parameterType="TTrayInfo">
        update t_tray_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="fTrayCode != null">f_tray_code = #{fTrayCode},</if>
            <if test="fStoreyCode != null">f_storey_code = #{fStoreyCode},</if>
            <if test="fStatus != null">f_status = #{fStatus},</if>
            <if test="fBindingTime != null">f_binding_time = #{fBindingTime},</if>
            <if test="fUnbindingTime != null">f_unbinding_time = #{fUnbindingTime},</if>
            <if test="fUnbindingTime == null">f_unbinding_time = null,</if>
            <if test="fCreateTime != null">f_create_time = #{fCreateTime},</if>
        </trim>
        where f_tray_id = #{fTrayId}
    </update>

    <update id="unbindByCode" parameterType="TTrayInfo">
        update t_tray_info
        set f_storey_code = null,
        f_status = 2,
        f_unbinding_time = #{fUnbindingTime}
        where f_tray_code = #{fTrayCode}
    </update>
    <delete id="deleteTTrayInfoById" parameterType="Long">
        delete from t_tray_info where f_tray_id = #{fTrayId}
    </delete>

    <delete id="deleteTTrayInfoByIds" parameterType="String">
        delete from t_tray_info where f_tray_id in 
        <foreach item="fTrayId" collection="array" open="(" separator="," close=")">
            #{fTrayId}
        </foreach>
    </delete>
</mapper>