<?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.inventory.mapper.InventoryMapper">

    <resultMap type="Inventory" id="InventoryResult">
        <result property="id"    column="id"    />
        <result property="inventoryType"    column="inventory_type"    />
        <result property="orderId"    column="order_id"    />
        <result property="materialId"    column="material_id"    />
        <result property="materialName" column="material_name"/>
        <result property="batchId"    column="batch_id"    />
        <result property="warehousesCode"    column="warehouses_code"  />
        <result property="warehousesName" column="warehouses_name"/>
        <result property="locationId"    column="location_id"    />
        <result property="locationName" column="location_name"/>
        <result property="ownerId"    column="owner_id"    />
        <result property="quantity"    column="quantity"    />
        <result property="lockedQuantity"    column="locked_quantity"    />
        <result property="unitWeight"    column="unit_weight"    />
        <result property="totalWeight"    column="total_weight"    />
        <result property="totalVolume"    column="total_volume"    />
        <result property="productionDate"    column="production_date"    />
        <result property="expirationDate"    column="expiration_date"    />
        <result property="inventoryStatus"    column="inventory_status"    />
        <result property="lastInboundTime"    column="last_inbound_time"    />
        <result property="lastOutboundTime"    column="last_outbound_time"    />
        <result property="isUsed"    column="is_used"    />
        <result property="sortNo"    column="sort_no"    />
        <result property="createTime"    column="create_time"    />
        <result property="createUserCode"    column="create_user_code"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateUserCode"    column="update_user_code"    />
        <result property="warehousesId"    column="warehouses_id"    />
        <result property="materialName"    column="material_name"    />
        <result property="minStockLevel" column="min_stock_level" jdbcType="BIGINT"/>
        <result property="maxStockLevel" column="max_stock_level" jdbcType="BIGINT"/>
        <result property="sapNo"    column="sap_no"    />
        <result property="tsCode"    column="ts_code"    />
        <result property="hazardId"    column="hazard_id"    />
        <result property="specification"    column="specification"    />
        <result property="materialUnit"    column="material_unit"    />
        <result property="unitWeight"    column="unit_weight"    />
        <result property="packageWeight"    column="package_weight"    />
        <result property="totalWeight"    column="total_weight"    />
        <result property="shelfLifeDays"    column="shelf_life_days"    />
        <result property="storageTemperature"    column="storage_temperature"    />
        <result property="specialRequirements"    column="special_requirements"    />
        <result property="alterType"    column="alterType"    />
        <result property="warehousesName"    column="warehouses_name"    />
        <result property="locationName"    column="location_name"    />
        <result property="ownerName"    column="owner_name"    />
    </resultMap>

    <resultMap type="com.ruoyi.inventory.domain.TO.StocktakeItemsTo" id="StocktakeItemsResult">
        <result property="id"    column="id"    />
        <result property="stocktakeId"    column="stocktake_id"    />
        <result property="materialId"    column="material_id"    />
        <result property="batchCode"    column="batch_code"    />
        <result property="warehouseId"    column="warehouse_id"    />
        <result property="locationId"    column="location_id"    />
        <result property="warehouseName"    column="warehouse_name"    />
        <result property="locationName"    column="location_name"    />
        <result property="systemQuantity"    column="system_quantity"    />
        <result property="actualQuantity"    column="actual_quantity"    />
        <result property="varianceQuantity"    column="variance_quantity"    />
        <result property="varianceAmount"    column="variance_amount"    />
        <result property="stocktakeStatus"    column="stocktake_status"    />
        <result property="countedBy"    column="counted_by"    />
        <result property="countedAt"    column="counted_at"    />
        <result property="adjusted"    column="adjusted"    />
        <result property="adjustmentReason"    column="adjustment_reason"    />
        <result property="adjustedBy"    column="adjusted_by"    />
        <result property="adjustedAt"    column="adjusted_at"    />
        <result property="startTime"    column="start_time"    />
        <result property="endTime"    column="end_time"    />
        <result property="materialRange"    column="material_range"    />
        <result property="locationRange"    column="location_range"    />
        <result property="remark"    column="remark"    />
        <result property="isUsed"    column="is_used"    />
        <result property="sortNo"    column="sort_no"    />
        <result property="createTime"    column="create_time"    />
        <result property="createUserCode"    column="create_user_code"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateUserCode"    column="update_user_code"    />
        <result property="inventoryId"    column="inventory_id"    />
        <result property="unitPrice"    column="unit_price"    />
    </resultMap>

    <resultMap type="com.ruoyi.inventory.domain.vo.InventoryVo" id="InventoryVoResult">
        <result property="id"    column="id"    />
        <result property="inventoryType"    column="inventory_type"    />
        <result property="orderId"    column="order_id"    />
        <result property="materialId"    column="material_id"    />
        <result property="batchId"    column="batch_id"    />
        <result property="warehousesCode"    column="warehouses_code"    />
        <result property="locationId"    column="location_id"    />
        <result property="ownerId"    column="owner_id"    />
        <result property="quantity"    column="quantity"    />
        <result property="lockedQuantity"    column="locked_quantity"    />
        <result property="unitWeight"    column="unit_weight"    />
        <result property="totalWeight"    column="total_weight"    />
        <result property="totalVolume"    column="total_volume"    />
        <result property="productionDate"    column="production_date"    />
        <result property="expirationDate"    column="expiration_date"    />
        <result property="inventoryStatus"    column="inventory_status"    />
        <result property="lastInboundTime"    column="last_inbound_time"    />
        <result property="lastOutboundTime"    column="last_outbound_time"    />
        <result property="isUsed"    column="is_used"    />
        <result property="sortNo"    column="sort_no"    />
        <result property="createTime"    column="create_time"    />
        <result property="createUserCode"    column="create_user_code"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateUserCode"    column="update_user_code"    />
        <result property="warehousesId"    column="warehouses_id"    />
        <result property="materialName"    column="material_name"    />
        <result property="minStockLevel" column="min_stock_level" jdbcType="BIGINT"/>
        <result property="maxStockLevel" column="max_stock_level" jdbcType="BIGINT"/>
        <result property="sapNo"    column="sap_no"    />
        <result property="tsCode"    column="ts_code"    />
        <result property="hazardId"    column="hazard_id"    />
        <result property="specification"    column="specification"    />
        <result property="materialUnit"    column="material_unit"    />
        <result property="unitWeight"    column="unit_weight"    />
        <result property="packageWeight"    column="package_weight"    />
        <result property="totalWeight"    column="total_weight"    />
        <result property="shelfLifeDays"    column="shelf_life_days"    />
        <result property="storageTemperature"    column="storage_temperature"    />
        <result property="specialRequirements"    column="special_requirements"    />
        <result property="alterType"    column="alterType"    />
        <result property="warehousesName"    column="warehouses_name"    />
        <result property="locationName"    column="location_name"    />
        <result property="ownerName"    column="owner_name"    />
    </resultMap>

    <resultMap type="com.ruoyi.inventory.domain.vo.InventorySummaryVO" id="InventorySummaryResult">
        <result property="materialId"    column="material_id"    />
        <result property="materialName"    column="material_name"    />
        <result property="minStockLevel" column="min_stock_level" jdbcType="BIGINT"/>
        <result property="maxStockLevel" column="max_stock_level" jdbcType="BIGINT"/>
        <result property="sapNo"    column="sap_no"    />
        <result property="tsCode"    column="ts_code"    />
        <result property="hazardId"    column="hazard_id"    />
        <result property="specification"    column="specification"    />
        <result property="materialUnit"    column="material_unit"    />
        <result property="unitWeight"    column="unit_weight"    />
        <result property="packageWeight"    column="package_weight"    />
        <result property="totalWeight"    column="total_weight"    />
        <result property="shelfLifeDays"    column="shelf_life_days"    />
        <result property="storageTemperature"    column="storage_temperature"    />
        <result property="specialRequirements"    column="special_requirements"    />
        <result property="totalQuantity"    column="total_quantity"    />
        <result property="totalLockedQuantity"    column="total_locked_quantity"    />
        <result property="totalAvailableQuantity"    column="total_available_quantity"    />
        <result property="alterType"    column="alterType"    />
    </resultMap>

    <sql id="selectInventoryVo">
        select id, inventory_type,warehouses_id, material_id, batch_id, location_id, owner_id, quantity, locked_quantity, unit_weight, total_weight, total_volume, production_date, expiration_date, inventory_status, last_inbound_time, last_outbound_time, is_used, sort_no, create_time, create_user_code, update_time, update_user_code from inventory
    </sql>

    <select id="selectInventoryList" parameterType="Inventory" resultMap="InventoryResult">
        <include refid="selectInventoryVo"/>
        <where>
            <if test="inventoryType != null "> and inventory_type = #{inventoryType}</if>
            <if test="orderId != null  and orderId != ''"> and order_id = #{orderId}</if>
            <if test="materialId != null  and materialId != ''"> and material_id = #{materialId}</if>
            <if test="batchId != null  and batchId != ''"> and batch_id = #{batchId}</if>
            <if test="locationId != null  and locationId != ''"> and location_id = #{locationId}</if>
            <if test="ownerId != null  and ownerId != ''"> and owner_id = #{ownerId}</if>
            <if test="quantity != null "> and quantity = #{quantity}</if>
            <if test="lockedQuantity != null "> and locked_quantity = #{lockedQuantity}</if>
            <if test="unitWeight != null "> and unit_weight = #{unitWeight}</if>
            <if test="totalWeight != null "> and total_weight = #{totalWeight}</if>
            <if test="totalVolume != null "> and total_volume = #{totalVolume}</if>
            <if test="productionDate != null "> and production_date = #{productionDate}</if>
            <if test="expirationDate != null "> and expiration_date = #{expirationDate}</if>
            <if test="inventoryStatus != null "> and inventory_status = #{inventoryStatus}</if>
            <if test="lastInboundTime != null "> and last_inbound_time = #{lastInboundTime}</if>
            <if test="lastOutboundTime != null "> and last_outbound_time = #{lastOutboundTime}</if>
            <if test="isUsed != null "> and is_used = #{isUsed}</if>
            <if test="sortNo != null "> and sort_no = #{sortNo}</if>
            <if test="createUserCode != null  and createUserCode != ''"> and create_user_code = #{createUserCode}</if>
            <if test="updateUserCode != null  and updateUserCode != ''"> and update_user_code = #{updateUserCode}</if>
            <if test="warehousesId != null "> and warehouses_id = #{warehousesId}</if>
        </where>
    </select>

    <!-- 按物料汇总统计库存 -->
    <sql id="selectInventoryCount">
        select
            i.material_id,
            m.material_name,m.sap_no,
            m.min_stock_level,
            m.max_stock_level,
            m.ts_code,
            m.hazard_id,
            m.specification,
            m.material_unit,
            m.unit_weight,
            m.package_weight,
            m.total_weight,
            m.shelf_life_days,
            m.storage_temperature,
            m.special_requirements,
            sum(i.quantity) as total_quantity,
            sum(i.locked_quantity) as total_locked_quantity,
            sum(i.quantity - ifnull(i.locked_quantity, 0)) as total_available_quantity
        from inventory i
                 left join materials m on i.material_id = m.id
                 left join warehouses w on i.warehouses_id = w.id
                 left join storage_locations sl on i.location_id = sl.id
                 left join owners o on i.owner_id = o.owner_code
    </sql>
    <select id="selectInventorySummaryList" parameterType="Inventory" resultMap="InventorySummaryResult">
        select tab.*, case when total_quantity &lt; min_stock_level then '2' when total_quantity &gt; max_stock_level then '1' else '3' end alterType from (
        <include refid="selectInventoryCount"/>
        <where>
            and i.quantity > 0 and i.inventory_status = 1 and i.is_used = 1
            <if test="inventoryType != null "> and i.inventory_type = #{inventoryType}</if>
            <if test="materialId != null  and materialId != ''"> and m.material_code like concat('%', #{materialId}, '%')</if>
            <if test="batchId != null  and batchId != ''"> and i.batch_id like concat('%', #{batchId}, '%')</if>
            <if test="warehousesId != null  and warehousesId != ''"> and w.warehouses_id = #{warehousesId}</if>
            <if test="locationId != null  and locationId != ''"> and i.location_id = #{locationId}</if>
            <if test="ownerId != null  and ownerId != ''"> and i.owner_id = #{ownerId}</if>
            <if test="inventoryStatus != null "> and i.inventory_status = #{inventoryStatus}</if>
            <if test="isUsed != null "> and i.is_used = #{isUsed}</if>
            <!-- 预警类型过滤（需要根据业务逻辑判断，这里暂时不实现） -->
        </where>
        group by i.material_id, m.material_name, m.sap_no,
        m.ts_code,
        m.hazard_id,
        m.specification,
        m.material_unit,
        m.unit_weight,
        m.package_weight,
        m.total_weight,
        m.shelf_life_days,
        m.storage_temperature,
        m.special_requirements  ) tab
        <where>
            <if test="alertType == '0'.toString()"> and total_quantity &lt; min_stock_level or total_quantity &gt; max_stock_level</if>
            <if test="alertType == '1'.toString()"> and total_quantity &gt; max_stock_level</if>
            <if test="alertType == '2'.toString()"> and total_quantity &lt; min_stock_level</if>
        </where>
        order by total_quantity desc,material_id
    </select>
    <select id="selectInventorySummaryList2" parameterType="Inventory" resultMap="InventorySummaryResult">
        select * from (
        <include refid="selectInventoryCount"/>
        <where>
            and i.quantity > 0 and i.inventory_status = 1 and i.is_used = 1
            <if test="inventoryType != null "> and i.inventory_type = #{inventoryType}</if>
            <if test="materialId != null  and materialId != ''"> and m.material_code like concat('%', #{materialId}, '%')</if>
            <if test="batchId != null  and batchId != ''"> and i.batch_id like concat('%', #{batchId}, '%')</if>
            <if test="warehousesId != null  and warehousesId != ''"> and w.warehouses_id = #{warehousesId}</if>
            <if test="locationId != null  and locationId != ''"> and i.location_id = #{locationId}</if>
            <if test="ownerId != null  and ownerId != ''"> and i.owner_id = #{ownerId}</if>
            <if test="inventoryStatus != null "> and i.inventory_status = #{inventoryStatus}</if>
            <if test="isUsed != null "> and i.is_used = #{isUsed}</if>
        </where>
        group by i.material_id, m.material_name, m.sap_no,
        m.ts_code,
        m.hazard_id,
        m.specification,
        m.material_unit,
        m.unit_weight,
        m.package_weight,
        m.total_weight,
        m.shelf_life_days,
        m.storage_temperature,
        m.special_requirements) tab
        <where>
            <if test="alertType == '0' "> and total_quantity &lt; min_stock_level or total_quantity &gt; max_stock_level</if>
            <if test="alertType == '1'.toString() "> and total_quantity &gt; max_stock_level</if>
            <if test="alertType == '2'.toString() "> and total_quantity &lt; min_stock_level</if>
        </where>
    </select>

    <!-- 查询库存明细列表（根据物料标识及检索条件） -->
    <sql id="selectInventoryDetailVo">
        select
            i.id,
            i.inventory_type,
            i.warehouses_id,
            i.material_id,
            i.batch_id,
            i.location_id,
            i.owner_id,
            i.quantity,
            i.locked_quantity,
            i.unit_weight,
            i.total_weight,
            i.total_volume,
            i.production_date,
            i.expiration_date,
            i.inventory_status,
            i.last_inbound_time,
            i.last_outbound_time,
            m.material_name,
            m.sap_no,
            m.min_stock_level,
            m.max_stock_level,
            m.ts_code,
            m.hazard_id,
            m.specification,
            m.material_unit,
            m.unit_weight,
            m.package_weight,
            m.total_weight,
            m.shelf_life_days,
            m.storage_temperature,
            m.special_requirements,
            w.warehouses_name,
            sl.location_name,
            o.owner_name
        from
            inventory i
                left join materials m on i.material_id = m.id
                left join warehouses w on i.warehouses_id = w.id
                left join storage_locations sl on i.location_id = sl.id
                left join owners o on i.owner_id = o.owner_code
    </sql>
    <select id="selectInventoryDetailList" parameterType="Inventory" resultMap="InventoryResult">
        <include refid="selectInventoryDetailVo"/>
        <where>
            and i.quantity > 0 and i.inventory_status = 1 and i.is_used = 1
            <if test="inventoryType != null "> and i.inventory_type = #{inventoryType}</if>
            <if test="materialId != null  and materialId != ''"> and i.material_id = #{materialId}</if>
            <if test="batchId != null  and batchId != ''"> and i.batch_id like concat('%', #{batchId}, '%')</if>
            <if test="warehousesId != null  and warehousesId != ''"> and w.warehouses_id = #{warehousesId}</if>
            <if test="locationId != null  and locationId != ''"> and i.location_id = #{locationId}</if>
            <if test="ownerId != null  and ownerId != ''"> and i.owner_id = #{ownerId}</if>
            <if test="inventoryStatus != null "> and i.inventory_status = #{inventoryStatus}</if>
        </where>
        order by w.warehouses_code, sl.location_code, material_id desc
    </select>

    <select id="selectInventoryVoList" parameterType="Inventory" resultMap="InventoryVoResult">
        <include refid="selectInventoryDetailVo"/>
        <where>
            and i.inventory_status = 1 and i.is_used = 1
            <if test="inventoryType != null "> and i.inventory_type = #{inventoryType}</if>
            <if test="materialId != null  and materialId != ''"> and i.material_id = #{materialId}</if>
            <if test="batchId != null  and batchId != ''"> and i.batch_id like concat('%', #{batchId}, '%')</if>
            <if test="warehousesId != null  and warehousesId != ''"> and w.warehouses_id = #{warehousesId}</if>
            <if test="locationId != null  and locationId != ''"> and i.location_id = #{locationId}</if>
            <if test="ownerId != null  and ownerId != ''"> and i.owner_id = #{ownerId}</if>
            <if test="inventoryStatus != null "> and i.inventory_status = #{inventoryStatus}</if>
        </where>
        order by w.warehouses_code, sl.location_code, material_id desc
    </select>

    <select id="selectInventory" parameterType="Inventory" resultMap="InventoryResult">
        <include refid="selectInventoryVo"/>
        <where>
            is_used = 1
            <if test="inventoryType != null "> and inventory_type = #{inventoryType}</if>
            <if test="orderId != null  and orderId != ''"> and order_id = #{orderId}</if>
            <if test="materialId != null  and materialId != ''"> and material_id = #{materialId}</if>
            <if test="batchId != null  and batchId != ''"> and batch_id = #{batchId}</if>
            <if test="warehousesCode != null  and warehousesCode != ''"> and warehouses_code = #{warehousesCode}</if>
            <if test="locationId != null  and locationId != ''"> and location_id = #{locationId}</if>
            <if test="ownerId != null  and ownerId != ''"> and owner_id = #{ownerId}</if>
            <if test="quantity != null "> and quantity = #{quantity}</if>
            <if test="lockedQuantity != null "> and locked_quantity = #{lockedQuantity}</if>
            <if test="unitWeight != null "> and unit_weight = #{unitWeight}</if>
            <if test="totalWeight != null "> and total_weight = #{totalWeight}</if>
            <if test="totalVolume != null "> and total_volume = #{totalVolume}</if>
            <if test="productionDate != null "> and production_date = #{productionDate}</if>
            <if test="expirationDate != null "> and expiration_date = #{expirationDate}</if>
            <if test="inventoryStatus != null "> and inventory_status = #{inventoryStatus}</if>
            <if test="lastInboundTime != null "> and last_inbound_time = #{lastInboundTime}</if>
            <if test="lastOutboundTime != null "> and last_outbound_time = #{lastOutboundTime}</if>
            <if test="isUsed != null "> and is_used = #{isUsed}</if>
            <if test="sortNo != null "> and sort_no = #{sortNo}</if>
            <if test="createUserCode != null  and createUserCode != ''"> and create_user_code = #{createUserCode}</if>
            <if test="updateUserCode != null  and updateUserCode != ''"> and update_user_code = #{updateUserCode}</if>
            <if test="warehousesId != null and warehousesId != ''"> and warehouses_id = #{warehousesId}</if>
        </where>
    </select>

    <select id="selectstocktakeItemsList" resultMap="StocktakeItemsResult">
        select id as inventory_id,material_id, batch_id, location_id, warehouses_id as warehouse_id, quantity as system_quantity
        from inventory
        where is_used = 1 and inventory_status = 1
        order by warehouse_id,location_id,material_id
    </select>

    <select id="selectInventoryById" parameterType="String" resultMap="InventoryResult">
        <include refid="selectInventoryVo"/>
        where id = #{id}
    </select>
    <select id="listByMaterialId" parameterType="String" resultMap="InventoryResult">
        SELECT
        i.id,
        i.inventory_type,
        i.warehouses_id,
        w.warehouses_name,
        i.order_id,
        i.material_id,
        m.material_name,
        i.batch_id,
        i.location_id,
        sl.location_name,
        i.owner_id,
        i.quantity,
        i.locked_quantity,
        i.unit_weight,
        i.total_weight,
        i.total_volume,
        i.production_date,
        i.expiration_date,
        i.inventory_status,
        i.last_inbound_time,
        i.last_outbound_time,
        i.is_used,
        i.sort_no,
        i.create_time,
        i.create_user_code,
        i.update_time,
        i.update_user_code
        FROM inventory i
        LEFT JOIN materials m ON i.material_id = m.id AND m.is_used = 1
        LEFT JOIN warehouses w ON i.warehouses_id = w.id AND w.is_enabled = 1 AND w.is_used = 1
        LEFT JOIN storage_locations sl ON i.location_id = sl.id AND sl.is_enabled = 1 AND sl.is_used = 1
        WHERE 1=1
        <if test="materialId != null and materialId.trim() != ''">
            and material_id = #{materialId}
        </if>
        <![CDATA[
and inventory_status = '1'
]]>
    </select>
    <insert id="insertInventory" parameterType="Inventory">
        insert into inventory
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="inventoryType != null">inventory_type,</if>
            <if test="orderId != null">order_id,</if>
            <if test="materialId != null">material_id,</if>
            <if test="batchId != null">batch_id,</if>
            <if test="locationId != null">location_id,</if>
            <if test="ownerId != null">owner_id,</if>
            <if test="quantity != null">quantity,</if>
            <if test="lockedQuantity != null">locked_quantity,</if>
            <if test="unitWeight != null">unit_weight,</if>
            <if test="totalWeight != null">total_weight,</if>
            <if test="totalVolume != null">total_volume,</if>
            <if test="productionDate != null">production_date,</if>
            <if test="expirationDate != null">expiration_date,</if>
            <if test="inventoryStatus != null">inventory_status,</if>
            <if test="lastInboundTime != null">last_inbound_time,</if>
            <if test="lastOutboundTime != null">last_outbound_time,</if>
            <if test="isUsed != null">is_used,</if>
            <if test="sortNo != null">sort_no,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createUserCode != null">create_user_code,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="updateUserCode != null">update_user_code,</if>
            <if test="warehousesId != null"> warehouses_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="inventoryType != null">#{inventoryType},</if>
            <if test="orderId != null">#{orderId},</if>
            <if test="materialId != null">#{materialId},</if>
            <if test="batchId != null">#{batchId},</if>
            <if test="locationId != null">#{locationId},</if>
            <if test="ownerId != null">#{ownerId},</if>
            <if test="quantity != null">#{quantity},</if>
            <if test="lockedQuantity != null">#{lockedQuantity},</if>
            <if test="unitWeight != null">#{unitWeight},</if>
            <if test="totalWeight != null">#{totalWeight},</if>
            <if test="totalVolume != null">#{totalVolume},</if>
            <if test="productionDate != null">#{productionDate},</if>
            <if test="expirationDate != null">#{expirationDate},</if>
            <if test="inventoryStatus != null">#{inventoryStatus},</if>
            <if test="lastInboundTime != null">#{lastInboundTime},</if>
            <if test="lastOutboundTime != null">#{lastOutboundTime},</if>
            <if test="isUsed != null">#{isUsed},</if>
            <if test="sortNo != null">#{sortNo},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createUserCode != null">#{createUserCode},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="updateUserCode != null">#{updateUserCode},</if>
            <if test="warehousesId != null"> #{warehousesId},</if>
        </trim>
    </insert>

    <update id="updateInventory" parameterType="Inventory">
        update inventory
        <trim prefix="SET" suffixOverrides=",">
            <if test="inventoryType != null">inventory_type = #{inventoryType},</if>
            <if test="orderId != null">order_id = #{orderId},</if>
            <if test="materialId != null">material_id = #{materialId},</if>
            <if test="batchId != null">batch_id = #{batchId},</if>
            <if test="locationId != null">location_id = #{locationId},</if>
            <if test="ownerId != null">owner_id = #{ownerId},</if>
            <if test="quantity != null">quantity = #{quantity},</if>
            <if test="lockedQuantity != null">locked_quantity = #{lockedQuantity},</if>
            <if test="unitWeight != null">unit_weight = #{unitWeight},</if>
            <if test="totalWeight != null">total_weight = #{totalWeight},</if>
            <if test="totalVolume != null">total_volume = #{totalVolume},</if>
            <if test="productionDate != null">production_date = #{productionDate},</if>
            <if test="expirationDate != null">expiration_date = #{expirationDate},</if>
            <if test="inventoryStatus != null">inventory_status = #{inventoryStatus},</if>
            <if test="lastInboundTime != null">last_inbound_time = #{lastInboundTime},</if>
            <if test="lastOutboundTime != null">last_outbound_time = #{lastOutboundTime},</if>
            <if test="isUsed != null">is_used = #{isUsed},</if>
            <if test="sortNo != null">sort_no = #{sortNo},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="createUserCode != null">create_user_code = #{createUserCode},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateUserCode != null">update_user_code = #{updateUserCode},</if>
            <if test="warehousesId != null"> warehouses_id = #{warehousesId},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteInventoryById" parameterType="String">
        delete from inventory where id = #{id}
    </delete>

    <delete id="deleteInventoryByIds" parameterType="String">
        delete from inventory where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <!--    库存物料超出预警值统计-->
    <resultMap id="InventoryExceedWarnResultMap" type="com.ruoyi.inventory.domain.vo.InventoryExceedWarnVO">
        <result column="material_name" property="materialName" jdbcType="VARCHAR"/>
        <result column="category_name" property="categoryName" jdbcType="VARCHAR"/>
        <result column="sap_no" property="sapNo" jdbcType="VARCHAR"/>
        <result column="quantity" property="quantity" jdbcType="BIGINT"/>
        <result column="min_stock_level" property="minStockLevel" jdbcType="BIGINT"/>
        <result column="max_stock_level" property="maxStockLevel" jdbcType="BIGINT"/>
    </resultMap>
    <select id="selectInventoryExceedWarnList" resultMap="InventoryExceedWarnResultMap">
        select m.material_name,mc.category_name,m.sap_no,
               sum(COALESCE(i.quantity, 0)) AS quantity,
               COALESCE(m.min_stock_level, 0) AS min_stock_level,
               COALESCE(m.max_stock_level, 9999) AS max_stock_level
        from materials as m
                 inner join inventory as i on i.material_id = m.id
                 inner join materials_category as mc on m.category_code = mc.category_code
        where (COALESCE(i.quantity, 0) &gt; COALESCE(m.max_stock_level, 999999)) or (COALESCE(i.quantity, 0) &lt; COALESCE(m.min_stock_level, 0))
            and m.is_used = 1
        group by m.id
    </select>

    <select id="selectInventoryTopTenByAmount" resultType="java.util.Map">
        select
            m.material_name as name,
            sum(i.quantity) as value
        from inventory i
            left join materials m on i.material_id = m.id
        where i.is_used = 1 and i.last_inbound_time &gt;= DATE_FORMAT(CURDATE(), '%Y-%m-01')
          and i.last_inbound_time &lt; DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
        group by m.material_name
        order by sum(i.quantity) desc
    </select>

    <select id="selectInventoryTopTenByQuantity" resultType="java.util.Map">
        select
            m.material_name as name,
            sum(i.quantity)*i.unit_price as value
        from inventory i
            left join materials m on i.material_id = m.id
        where i.is_used = 1 and i.last_inbound_time &gt;= DATE_FORMAT(CURDATE(), '%Y-%m-01')
          and i.last_inbound_time &lt; DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
        group by m.material_name
        order by sum(i.quantity)*i.unit_price desc
    </select>
</mapper>