<?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="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"    />
    </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.InventorySummaryVO" id="InventorySummaryResult">
        <result property="materialId"    column="material_id"    />
        <result property="materialName"    column="material_name"    />
        <result property="inventoryTypeName"    column="inventory_type_name"    />
        <result property="warehousesCode"    column="warehouses_code"    />
        <result property="warehouseName"    column="warehouse_name"    />
        <result property="locationId"    column="location_id"    />
        <result property="locationName"    column="location_name"    />
        <result property="ownerId"    column="owner_id"    />
        <result property="ownerName"    column="owner_name"    />
        <result property="totalQuantity"    column="total_quantity"    />
        <result property="totalLockedQuantity"    column="total_locked_quantity"    />
        <result property="totalAvailableQuantity"    column="total_available_quantity"    />
        <result property="totalWeight"    column="total_weight"    />
        <result property="totalVolume"    column="total_volume"    />
        <result property="inventoryStatusName"    column="inventory_status_name"    />
    </resultMap>

    <sql id="selectInventoryVo">
        select id, inventory_type,warehouses_id, order_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>

    <!-- 按物料汇总统计库存 -->
    <select id="selectInventorySummaryList" parameterType="Inventory" resultMap="InventorySummaryResult">
        select
            i.material_id,
            m.material_name,
            case i.inventory_type when 1 then '普通' when 2 then '退库' else '未知' end as inventory_type_name,
            i.warehouses_code,
            w.warehouses_name as warehouse_name,
            i.location_id,
            sl.location_name,
            i.owner_id,
            o.owner_name,
            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,
            sum(ifnull(i.total_weight, 0)) as total_weight,
            sum(ifnull(i.total_volume, 0)) as total_volume,
            case max(i.inventory_status) when 0 then '已出库' when 1 then '正常' else '未知' end as inventory_status_name
        from inventory i
        left join materials m on i.material_id = m.material_code
        left join warehouses w on i.warehouses_code = w.warehouses_code
        left join storage_locations sl on i.location_id = sl.location_code
        left join owners o on i.owner_id = o.owner_code
        <where>
            <if test="inventoryType != null "> and i.inventory_type = #{inventoryType}</if>
            <if test="materialId != null  and materialId != ''"> and (i.material_id like concat('%', #{materialId}, '%') or m.material_code like concat('%', #{materialId}, '%'))</if>
            <if test="batchId != null  and batchId != ''"> and i.batch_id = #{batchId}</if>
            <if test="warehousesCode != null  and warehousesCode != ''"> and i.warehouses_code = #{warehousesCode}</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, i.warehouses_code, i.location_id, i.owner_id, i.inventory_type, i.inventory_status
        order by i.material_id, i.warehouses_code, i.location_id
    </select>

    <!-- 查询库存明细列表（根据物料标识及检索条件） -->
    <select id="selectInventoryDetailList" 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="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="inventoryStatus != null "> and inventory_status = #{inventoryStatus}</if>
            <if test="isUsed != null "> and is_used = #{isUsed}</if>
            <if test="warehousesId != null "> and warehouses_id = #{warehousesId}</if>
        </where>
        order by material_id, warehouses_code, location_id, create_time desc
    </select>

    <select id="selectInventory" 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="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">
        <include refid="selectInventoryVo"/>
        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>
</mapper>