<?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.asset.mapper.AmsAssetLocationMapper">
    
    <resultMap type="AmsAssetLocation" id="AmsAssetLocationResult">
        <result property="locationId"    column="location_id"    />
        <result property="locationCode"    column="location_code"    />
        <result property="locationName"    column="location_name"    />
        <result property="warehouseId"    column="warehouse_id"    />
        <result property="warehouseName"    column="warehouse_name"    />
        <result property="detailAddress"    column="detail_address"    />
        <result property="enabled"    column="enabled"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <result property="delFlag"    column="del_flag"    />
    </resultMap>

    <sql id="selectAmsAssetLocationVo">
        select l.location_id, l.location_code, l.location_name, l.warehouse_id, w.warehouse_name, l.detail_address, l.enabled, l.create_by, l.create_time, l.update_by, l.update_time, l.remark, l.del_flag
        from ams_asset_location l
        left join ams_warehouse w on l.warehouse_id = w.warehouse_id and w.del_flag = '0'
    </sql>

    <select id="selectAmsAssetLocationList" parameterType="AmsAssetLocation" resultMap="AmsAssetLocationResult">
        <include refid="selectAmsAssetLocationVo"/>
        <where>
            l.del_flag = '0'
            <if test="locationCode != null  and locationCode != ''"> and l.location_code = #{locationCode}</if>
            <if test="locationName != null  and locationName != ''"> and l.location_name like concat('%', #{locationName}, '%')</if>
            <if test="warehouseId != null "> and l.warehouse_id = #{warehouseId}</if>
            <if test="enabled != null  and enabled != ''"> and l.enabled = #{enabled}</if>
        </where>
    </select>
    
    <select id="selectAmsAssetLocationByLocationId" parameterType="Long" resultMap="AmsAssetLocationResult">
        <include refid="selectAmsAssetLocationVo"/>
        where l.location_id = #{locationId} and l.del_flag = '0'
    </select>

    <select id="checkLocationCodeUnique" parameterType="String" resultMap="AmsAssetLocationResult">
        <include refid="selectAmsAssetLocationVo"/>
        where l.location_code = #{locationCode} and l.del_flag = '0' limit 1
    </select>

    <select id="countLocationReferences" parameterType="Long" resultType="int">
        select
            (select count(1) from ams_asset where location_id = #{locationId} and del_flag = '0')
            + (select count(1) from ams_asset_lifecycle_log where del_flag = '0' and (before_location_id = #{locationId} or after_location_id = #{locationId}))
            + (select count(1) from ams_borrow_order_item where del_flag = '0' and (before_location_id = #{locationId} or return_location_id = #{locationId}))
            + (select count(1) from ams_inbound_order_item where location_id = #{locationId} and del_flag = '0')
            + (select count(1) from ams_inventory_task where location_id = #{locationId} and del_flag = '0')
            + (select count(1) from ams_inventory_task_item where del_flag = '0' and (book_location_id = #{locationId} or inventory_location_id = #{locationId}))
            + (select count(1) from ams_receive_order_item where before_location_id = #{locationId} and del_flag = '0')
            + (select count(1) from ams_return_order_item where after_location_id = #{locationId} and del_flag = '0')
            + (select count(1) from ams_transfer_order_item where del_flag = '0' and (old_location_id = #{locationId} or new_location_id = #{locationId}))
    </select>

    <insert id="insertAmsAssetLocation" parameterType="AmsAssetLocation" useGeneratedKeys="true" keyProperty="locationId">
        insert into ams_asset_location
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="locationCode != null and locationCode != ''">location_code,</if>
            <if test="locationName != null and locationName != ''">location_name,</if>
            <if test="warehouseId != null">warehouse_id,</if>
            <if test="detailAddress != null">detail_address,</if>
            <if test="enabled != null and enabled != ''">enabled,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
            <if test="delFlag != null and delFlag != ''">del_flag,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="locationCode != null and locationCode != ''">#{locationCode},</if>
            <if test="locationName != null and locationName != ''">#{locationName},</if>
            <if test="warehouseId != null">#{warehouseId},</if>
            <if test="detailAddress != null">#{detailAddress},</if>
            <if test="enabled != null and enabled != ''">#{enabled},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
            <if test="delFlag != null and delFlag != ''">#{delFlag},</if>
         </trim>
    </insert>

    <update id="updateAmsAssetLocation" parameterType="AmsAssetLocation">
        update ams_asset_location
        <trim prefix="SET" suffixOverrides=",">
            <if test="locationCode != null and locationCode != ''">location_code = #{locationCode},</if>
            <if test="locationName != null and locationName != ''">location_name = #{locationName},</if>
            <if test="warehouseId != null">warehouse_id = #{warehouseId},</if>
            <if test="detailAddress != null">detail_address = #{detailAddress},</if>
            <if test="enabled != null and enabled != ''">enabled = #{enabled},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="delFlag != null and delFlag != ''">del_flag = #{delFlag},</if>
        </trim>
        where location_id = #{locationId}
    </update>

    <update id="deleteAmsAssetLocationByLocationId" parameterType="Long">
        update ams_asset_location set del_flag = '1' where location_id = #{locationId}
    </update>

    <update id="deleteAmsAssetLocationByLocationIds" parameterType="String">
        update ams_asset_location set del_flag = '1' where location_id in
        <foreach item="locationId" collection="array" open="(" separator="," close=")">
            #{locationId}
        </foreach>
    </update>

</mapper>
