<?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.shelf.TShelfStorageLocationMapper">
    
    <resultMap type="TShelfStorageLocation" id="TShelfStorageLocationResult">
        <result property="shelfStorageLocationId"    column="f_shelf_storage_location_id"    />
        <result property="shelfId"    column="f_shelf_id"    />
        <result property="label"    column="f_label"    />
        <result property="location"    column="f_location"    />
        <result property="index"    column="f_index"    />
        <result property="rid"    column="f_rid"    />
        <result property="inboundTime"    column="f_inbound_time"    />
        <result property="outboundTime"    column="f_outbound_time"    />
    </resultMap>

    <sql id="selectTShelfStorageLocationVo">
        SELECT
            location.f_shelf_storage_location_id,
            location.f_shelf_id,
            location.f_label,
            location.f_location,
            location.f_index,
            location.f_rid,
            location.f_inbound_time,
            location.f_outbound_time,
            material.material_code AS materialCode,
            material.material_name AS materialName,
            material.material_specifications AS materialSpecifications
        FROM
            t_shelf_storage_location location
        LEFT JOIN t_rack_material_relation relation ON relation.rack_code = location.f_rid
        LEFT JOIN t_rack_material_info material ON material.material_id = relation.material_id
    </sql>

    <select id="selectTShelfStorageLocationList" parameterType="TShelfStorageLocation" resultMap="TShelfStorageLocationResult">
        <include refid="selectTShelfStorageLocationVo"/>
        <where>  
            <if test="shelfId != null "> and location.f_shelf_id = #{shelfId}</if>
            <if test="label != null  and label != ''"> and location.f_label = #{label}</if>
            <if test="location != null  and location != ''"> and location.f_location = #{location}</if>
            <if test="index != null "> and location.f_index = #{index}</if>
            <if test="rid != null  and rid != ''"> and location.f_rid = #{rid}</if>
            <if test="inboundTime != null "> and location.f_inbound_time = #{inboundTime}</if>
            <if test="outboundTime != null "> and location.f_outbound_time = #{outboundTime}</if>
        </where>
        ORDER BY location.f_index
    </select>
    
    <select id="selectTShelfStorageLocationById" parameterType="Long" resultMap="TShelfStorageLocationResult">
        <include refid="selectTShelfStorageLocationVo"/>
        where location.f_shelf_storage_location_id = #{shelfStorageLocationId}
    </select>
        
    <insert id="insertTShelfStorageLocation" parameterType="TShelfStorageLocation" useGeneratedKeys="true" keyProperty="fShelfStorageLocationId">
        insert into t_shelf_storage_location
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="shelfId != null">f_shelf_id,</if>
            <if test="label != null">f_label,</if>
            <if test="location != null and fLocation != ''">f_location,</if>
            <if test="index != null">f_index,</if>
            <if test="rid != null">f_rid,</if>
            <if test="inboundTime != null">f_inbound_time,</if>
            <if test="outboundTime != null">f_outbound_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="shelfId != null">#{shelfId},</if>
            <if test="label != null">#{label},</if>
            <if test="location != null and location != ''">#{location},</if>
            <if test="index != null">#{index},</if>
            <if test="rid != null">#{rid},</if>
            <if test="inboundTime != null">#{inboundTime},</if>
            <if test="outboundTime != null">#{outboundTime},</if>
         </trim>
    </insert>

    <update id="updateTShelfStorageLocation" parameterType="TShelfStorageLocation">
        update t_shelf_storage_location
        <trim prefix="SET" suffixOverrides=",">
            <if test="shelfId != null">f_shelf_id = #{shelfId},</if>
            <if test="label != null">f_label = #{label},</if>
            <if test="location != null and location != ''">f_location = #{location},</if>
            <if test="index != null">f_index = #{index},</if>
            <if test="rid != null">f_rid = #{rid},</if>
            <if test="inboundTime != null">f_inbound_time = #{inboundTime},</if>
            <if test="outboundTime != null">f_outbound_time = #{outboundTime},</if>
        </trim>
        where f_shelf_storage_location_id = #{fShelfStorageLocationId}
    </update>

    <update id="updateTShelfStorageLocationByLocationAndShelfId" parameterType="TShelfStorageLocation">
        update t_shelf_storage_location
        <trim prefix="SET" suffixOverrides=",">
            <if test="label != null">f_label = #{label},</if>
            <if test="index != null">f_index = #{index},</if>
            <if test="rid != null and inboundTime != null">f_rid = #{rid},</if>
            <if test="inboundTime != null">f_inbound_time = #{inboundTime},</if>
            <if test="outboundTime != null">
                f_rid = null,
                f_inbound_time = null,
                f_outbound_time = #{outboundTime},
            </if>
        </trim>
        where f_location = #{location} AND f_shelf_id = #{shelfId}
    </update>

    <delete id="deleteTShelfStorageLocationById" parameterType="Long">
        delete from t_shelf_storage_location where f_shelf_storage_location_id = #{shelfStorageLocationId}
    </delete>

    <delete id="deleteTShelfStorageLocationByIds" parameterType="String">
        delete from t_shelf_storage_location where f_shelf_storage_location_id in 
        <foreach item="fShelfStorageLocationId" collection="array" open="(" separator="," close=")">
            #{shelfStorageLocationId}
        </foreach>
    </delete>
</mapper>