<?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.ruoyi.business.mapper.TBaseStorageAreaLocationMapper">
    
    <resultMap type="TBaseStorageAreaLocation" id="TBaseStorageAreaLocationResult">
        <result property="locationId"    column="LOCATION_ID"    />
        <result property="pyName"    column="PY_NAME"    />
        <result property="storageId"    column="STORAGE_ID"    />
        <result property="islock"    column="ISLOCK"    />
        <result property="locationLong"    column="LOCATION_LONG"    />
        <result property="locationWidth"    column="LOCATION_WIDTH"    />
        <result property="locationHeight"    column="LOCATION_HEIGHT"    />
        <result property="volume"    column="VOLUME"    />
        <result property="hasvolume"    column="HASVOLUME"    />
        <result property="bearweight"    column="BEARWEIGHT"    />
        <result property="abcId"    column="ABC_ID"    />
        <result property="sort"    column="SORT"    />
        <result property="status"    column="STATUS"    />
        <result property="remark"    column="REMARK"    />
        <result property="version"    column="VERSION"    />
        <result property="operatorid"    column="OPERATORID"    />
        <result property="operatedate"    column="OPERATEDATE"    />
        <result property="row1"    column="ROW1"    />
        <result property="cln"    column="CLN"    />
        <result property="layer1"    column="LAYER1"    />
    </resultMap>

    <sql id="selectTBaseStorageAreaLocationVo">
        select LOCATION_ID, PY_NAME, STORAGE_ID, ISLOCK, LOCATION_LONG, LOCATION_WIDTH, LOCATION_HEIGHT, VOLUME, HASVOLUME, BEARWEIGHT, ABC_ID, SORT, STATUS, REMARK, VERSION, OPERATORID, OPERATEDATE, ROW1, CLN, LAYER1 from t_base_storage_area_location
    </sql>

    <select id="selectTBaseStorageAreaLocationList" parameterType="TBaseStorageAreaLocation" resultMap="TBaseStorageAreaLocationResult">
        <include refid="selectTBaseStorageAreaLocationVo"/>
        <where>
            <if test="locationId != null  and locationId != ''"> and LOCATION_ID = #{locationId}</if>
            <if test="pyName != null  and pyName != ''"> and PY_NAME like concat('%', #{pyName}, '%')</if>
            <if test="islock != null  and islock != ''"> and ISLOCK = #{islock}</if>
            <if test="locationLong != null "> and LOCATION_LONG = #{locationLong}</if>
            <if test="locationWidth != null "> and LOCATION_WIDTH = #{locationWidth}</if>
            <if test="locationHeight != null "> and LOCATION_HEIGHT = #{locationHeight}</if>
            <if test="volume != null "> and VOLUME = #{volume}</if>
            <if test="hasvolume != null "> and HASVOLUME = #{hasvolume}</if>
            <if test="bearweight != null "> and BEARWEIGHT = #{bearweight}</if>
            <if test="abcId != null  and abcId != ''"> and ABC_ID = #{abcId}</if>
            <if test="sort != null "> and SORT = #{sort}</if>
            <if test="status != null  and status != ''"> and STATUS = #{status}</if>
            <if test="remark != null  and remark != ''"> and REMARK = #{remark}</if>
            <if test="version != null  and version != ''"> and VERSION = #{version}</if>
            <if test="operatorid != null  and operatorid != ''"> and OPERATORID = #{operatorid}</if>
            <if test="operatedate != null "> and OPERATEDATE = #{operatedate}</if>
            <if test="row1 != null  and row1 != ''"> and ROW1 = #{row1}</if>
            <if test="cln != null  and cln != ''"> and CLN = #{cln}</if>
            <if test="layer1 != null  and layer1 != ''"> and LAYER1 = #{layer1}</if>
        </where>
    </select>
    
    <select id="selectTBaseStorageAreaLocationByLocationId" parameterType="String" resultMap="TBaseStorageAreaLocationResult">
        <include refid="selectTBaseStorageAreaLocationVo"/>
        where LOCATION_ID = #{locationId}
    </select>

    <select id="outOfRemainingWarning" resultType="String">
        SELECT (SELECT COUNT(*) FROM `t_base_storage_area_location` WHERE `STATUS`='0' AND ISLOCK='0') / (SELECT COUNT(*) FROM `t_base_storage_area_location`);
    </select>

    <select id="outOfStockWarning" resultType="Integer">
        SELECT (SELECT COUNT(*) FROM `t_base_storage_area_location`) - (SELECT COUNT(*) FROM `t_base_storage_area_location` WHERE `STATUS`='0' AND ISLOCK='0');
    </select>
        
    <insert id="insertTBaseStorageAreaLocation" parameterType="TBaseStorageAreaLocation">
        insert into t_base_storage_area_location
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="locationId != null">LOCATION_ID,</if>
            <if test="pyName != null">PY_NAME,</if>
            <if test="storageId != null">STORAGE_ID,</if>
            <if test="islock != null">ISLOCK,</if>
            <if test="locationLong != null">LOCATION_LONG,</if>
            <if test="locationWidth != null">LOCATION_WIDTH,</if>
            <if test="locationHeight != null">LOCATION_HEIGHT,</if>
            <if test="volume != null">VOLUME,</if>
            <if test="hasvolume != null">HASVOLUME,</if>
            <if test="bearweight != null">BEARWEIGHT,</if>
            <if test="abcId != null">ABC_ID,</if>
            <if test="sort != null">SORT,</if>
            <if test="status != null">STATUS,</if>
            <if test="remark != null">REMARK,</if>
            <if test="version != null">VERSION,</if>
            <if test="operatorid != null">OPERATORID,</if>
            <if test="operatedate != null">OPERATEDATE,</if>
            <if test="row1 != null">ROW1,</if>
            <if test="cln != null">CLN,</if>
            <if test="layer1 != null">LAYER1,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="locationId != null">#{locationId},</if>
            <if test="pyName != null">#{pyName},</if>
            <if test="storageId != null">#{storageId},</if>
            <if test="islock != null">#{islock},</if>
            <if test="locationLong != null">#{locationLong},</if>
            <if test="locationWidth != null">#{locationWidth},</if>
            <if test="locationHeight != null">#{locationHeight},</if>
            <if test="volume != null">#{volume},</if>
            <if test="hasvolume != null">#{hasvolume},</if>
            <if test="bearweight != null">#{bearweight},</if>
            <if test="abcId != null">#{abcId},</if>
            <if test="sort != null">#{sort},</if>
            <if test="status != null">#{status},</if>
            <if test="remark != null">#{remark},</if>
            <if test="version != null">#{version},</if>
            <if test="operatorid != null">#{operatorid},</if>
            <if test="operatedate != null">#{operatedate},</if>
            <if test="row1 != null">#{row1},</if>
            <if test="cln != null">#{cln},</if>
            <if test="layer1 != null">#{layer1},</if>
         </trim>
    </insert>

    <update id="updateTBaseStorageAreaLocation" parameterType="TBaseStorageAreaLocation">
        update t_base_storage_area_location
        <trim prefix="SET" suffixOverrides=",">
            <if test="pyName != null">PY_NAME = #{pyName},</if>
            <if test="storageId != null">STORAGE_ID = #{storageId},</if>
            <if test="islock != null">ISLOCK = #{islock},</if>
            <if test="locationLong != null">LOCATION_LONG = #{locationLong},</if>
            <if test="locationWidth != null">LOCATION_WIDTH = #{locationWidth},</if>
            <if test="locationHeight != null">LOCATION_HEIGHT = #{locationHeight},</if>
            <if test="volume != null">VOLUME = #{volume},</if>
            <if test="hasvolume != null">HASVOLUME = #{hasvolume},</if>
            <if test="bearweight != null">BEARWEIGHT = #{bearweight},</if>
            <if test="abcId != null">ABC_ID = #{abcId},</if>
            <if test="sort != null">SORT = #{sort},</if>
            <if test="status != null">STATUS = #{status},</if>
            <if test="remark != null">REMARK = #{remark},</if>
            <if test="version != null">VERSION = #{version},</if>
            <if test="operatorid != null">OPERATORID = #{operatorid},</if>
            <if test="operatedate != null">OPERATEDATE = #{operatedate},</if>
            <if test="row1 != null">ROW1 = #{row1},</if>
            <if test="cln != null">CLN = #{cln},</if>
            <if test="layer1 != null">LAYER1 = #{layer1},</if>
        </trim>
        where LOCATION_ID = #{locationId}
    </update>

    <delete id="deleteTBaseStorageAreaLocationByLocationId" parameterType="String">
        delete from t_base_storage_area_location where LOCATION_ID = #{locationId}
    </delete>

    <delete id="deleteTBaseStorageAreaLocationByLocationIds" parameterType="String">
        delete from t_base_storage_area_location where LOCATION_ID in 
        <foreach item="locationId" collection="array" open="(" separator="," close=")">
            #{locationId}
        </foreach>
    </delete>

</mapper>