<?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.InboundOrderItemsMapper">

    <resultMap type="com.ruoyi.inventory.domain.InboundOrderItems" id="InboundOrderItemsResult">
        <result property="id"    column="id"    />
        <result property="orderId"    column="order_id"    />
        <result property="materialId"    column="material_id"    />
        <result property="batchId"    column="batch_id"    />
        <result property="warehouseId"    column="warehouse_id"    />
        <result property="locationId"    column="location_id"    />
        <result property="plannedQuantity"    column="planned_quantity"    />
        <result property="actualQuantity"    column="actual_quantity"    />
        <result property="plannedPackages"    column="planned_packages"    />
        <result property="actualPackages"    column="actual_packages"    />
        <result property="divisor"    column="divisor"    />
        <result property="labelColor"    column="label_color"    />
        <result property="voucherNumber"    column="voucher_number"    />
        <result property="unitPrice"    column="unit_price"    />
        <result property="itemStatus"    column="item_status"    />
        <result property="receivedAt"    column="received_at"    />
        <result property="receivedBy"    column="received_by"    />
        <result property="labelQuantity" column="label_quantity"/>
        <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="inboundOrderId"    column="inbound_order_id"    />
    </resultMap>



    <sql id="selectInboundOrderItemsVo">
        select id, order_id, material_id, batch_id, warehouse_id, location_id, planned_quantity, actual_quantity, planned_packages, actual_packages, divisor, label_color, voucher_number, unit_price, item_status, received_at, received_by, label_quantity, remark, is_used, sort_no, create_time, create_user_code, update_time, update_user_code,inbound_order_id from inbound_order_items
    </sql>

    <select id="selectInboundOrderItemsList" parameterType="com.ruoyi.inventory.domain.InboundOrderItems" resultMap="InboundOrderItemsResult">
        <include refid="selectInboundOrderItemsVo"/>
        <where>
            <if test="inboundOrderId != null  and inboundOrderId != ''"> and inbound_order_id = #{inboundOrderId}</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="warehouseId != null  and warehouseId != ''"> and warehouse_id = #{warehouseId}</if>
            <if test="locationId != null  and locationId != ''"> and location_id = #{locationId}</if>
            <if test="plannedQuantity != null "> and planned_quantity = #{plannedQuantity}</if>
            <if test="actualQuantity != null "> and actual_quantity = #{actualQuantity}</if>
            <if test="plannedPackages != null "> and planned_packages = #{plannedPackages}</if>
            <if test="actualPackages != null "> and actual_packages = #{actualPackages}</if>
            <if test="divisor != null "> and divisor = #{divisor}</if>
            <if test="labelColor != null "> and label_color = #{labelColor}</if>
            <if test="voucherNumber != null  and voucherNumber != ''"> and voucher_number = #{voucherNumber}</if>
            <if test="unitPrice != null "> and unit_price = #{unitPrice}</if>
            <if test="itemStatus != null "> and item_status = #{itemStatus}</if>
            <if test="receivedAt != null "> and received_at = #{receivedAt}</if>
            <if test="receivedBy != null  and receivedBy != ''"> and received_by = #{receivedBy}</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>
        </where>
    </select>

    <select id="selectInboundOrderItemsById" parameterType="String" resultMap="InboundOrderItemsResult">
        <include refid="selectInboundOrderItemsVo"/>
        where id = #{id}
    </select>


    <resultMap type="com.ruoyi.inventory.domain.TO.InboundItemsTO" id="InboundOrderItemsAndMnameResult">
        <result property="id"    column="id"    />
        <result property="orderId"    column="order_id"    />
        <result property="materialId"    column="material_id"    />
        <result property="sapNo"    column="sap_no"    />
        <result property="materialName"    column="material_name"    />
        <result property="unitWeight"    column="unit_weight"  />
        <result property="batchId"    column="batch_id"    />
        <result property="warehouseId"    column="warehouse_id"    />
        <result property="warehousesName"    column="warehouses_name"    />
        <result property="locationId"    column="location_id"    />
        <result property="locationName"    column="location_name"    />
        <result property="plannedQuantity"    column="planned_quantity"    />
        <result property="labelQuantity" column="label_quantity"/>
        <result property="actualQuantity"    column="actual_quantity"    />
        <result property="plannedPackages"    column="planned_packages"    />
        <result property="actualPackages"    column="actual_packages"    />
        <result property="divisor"    column="divisor"    />
        <result property="labelColor"    column="label_color"    />
        <result property="voucherNumber"    column="voucher_number"    />
        <result property="unitPrice"    column="unit_price"    />
        <result property="itemStatus"    column="item_status"    />
        <result property="receivedAt"    column="received_at"    />
        <result property="receivedBy"    column="received_by"    />
        <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="inboundOrderId"    column="inbound_order_id"    />

    </resultMap>

    <select id="selectInboundOrderItemsListAndMaterialName"
            parameterType="com.ruoyi.inventory.domain.InboundOrderItems"
            resultMap="InboundOrderItemsAndMnameResult">
        SELECT
        ii.id,
        ii.order_id,
        ii.material_id,
        m.material_name,
        m.sap_no,
        m.unit_weight,
        ii.batch_id,
        ii.warehouse_id,
        w.warehouses_name,
        ii.location_id,
        sl.location_name,
        ii.planned_quantity,
        ii.actual_quantity,
        ii.planned_packages,
        ii.actual_packages,
        ii.divisor,
        ii.label_color,
        ii.voucher_number,
        ii.unit_price,
        ii.item_status,
        ii.received_at,
        ii.received_by,
        ii.label_quantity,
        ii.remark,
        ii.is_used,
        ii.sort_no,
        ii.create_time,
        ii.create_user_code,
        ii.update_time,
        ii.update_user_code,
        ii.inbound_order_id,
        m.material_name
        FROM inbound_order_items ii
        LEFT JOIN materials m ON ii.material_id = m.id
        left join warehouses w on ii.warehouse_id = w.id
        left join storage_locations sl on ii.location_id = sl.id
        <where>
            <!-- 移除条件前的and，<where>标签会自动处理首个条件的and/or -->
            <if test="inboundOrderId != null and inboundOrderId != ''">
                and inbound_order_id = #{inboundOrderId}
            </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="warehouseId != null and warehouseId != ''">
                and warehouse_id = #{warehouseId}
            </if>
            <if test="locationId != null and locationId != ''">
                and location_id = #{locationId}
            </if>
            <if test="plannedQuantity != null">
                and planned_quantity = #{plannedQuantity}
            </if>
            <if test="actualQuantity != null">
                and actual_quantity = #{actualQuantity}
            </if>
            <if test="plannedPackages != null">
                and planned_packages = #{plannedPackages}
            </if>
            <if test="actualPackages != null">
                and actual_packages = #{actualPackages}
            </if>
            <if test="divisor != null">
                and divisor = #{divisor}
            </if>
            <if test="labelColor != null">
                and label_color = #{labelColor}
            </if>
            <if test="voucherNumber != null and voucherNumber != ''">
                and voucher_number = #{voucherNumber}
            </if>
            <if test="unitPrice != null">
                and unit_price = #{unitPrice}
            </if>
            <if test="itemStatus != null">
                and item_status = #{itemStatus}
            </if>
            <if test="receivedAt != null">
                and received_at = #{receivedAt}
            </if>
            <if test="receivedBy != null and receivedBy != ''">
                and received_by = #{receivedBy}
            </if>
            <if test="labelQuantity != null and labelQuantity != ''">
                and label_quantity = #{labelQuantity}
            </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>

        </where>
    </select>
<!--    添加入库明细sql 暂无用，新增明细的方法目前在主表的batchInboundOrderItems-->
    <insert id="insertInboundOrderItems" parameterType="com.ruoyi.inventory.domain.InboundOrderItems">
        insert into inbound_order_items
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="orderId != null">order_id,</if>
            <if test="materialId != null">material_id,</if>
            <if test="batchId != null">batch_id,</if>
            <if test="warehouseId != null">warehouse_id,</if>
            <if test="locationId != null">location_id,</if>
            <if test="plannedQuantity != null">planned_quantity,</if>
            <if test="actualQuantity != null">actual_quantity,</if>
            <if test="plannedPackages != null">planned_packages,</if>
            <if test="actualPackages != null">actual_packages,</if>
            <if test="divisor != null">divisor,</if>
            <if test="labelColor != null">label_color,</if>
            <if test="voucherNumber != null">voucher_number,</if>
            <if test="unitPrice != null">unit_price,</if>
            <if test="itemStatus != null">item_status,</if>
            <if test="receivedAt != null">received_at,</if>
            <if test="receivedBy != null">received_by,</if>
            <if test="labelQuantity != null">label_quantity,</if>
            <if test="remark != null">remark,</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="inboundOrderId != null">inbound_order_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="orderId != null">#{orderId},</if>
            <if test="materialId != null">#{materialId},</if>
            <if test="batchId != null">#{batchId},</if>
            <if test="warehouseId != null">#{warehouseId},</if>
            <if test="locationId != null">#{locationId},</if>
            <if test="plannedQuantity != null">#{plannedQuantity},</if>
            <if test="actualQuantity != null">#{actualQuantity},</if>
            <if test="plannedPackages != null">#{plannedPackages},</if>
            <if test="actualPackages != null">#{actualPackages},</if>
            <if test="divisor != null">#{divisor},</if>
            <if test="labelColor != null">#{labelColor},</if>
            <if test="voucherNumber != null">#{voucherNumber},</if>
            <if test="unitPrice != null">#{unitPrice},</if>
            <if test="itemStatus != null">#{itemStatus},</if>
            <if test="receivedAt != null">#{receivedAt},</if>
            <if test="receivedBy != null">#{receivedBy},</if>
            <if test="labelQuantity != null">#{labelQuantity},</if>
            <if test="remark != null">#{remark},</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="inboundOrderId != null">#{inboundOrderId},</if>
        </trim>
    </insert>
<!--    修改明细，暂无用-->
    <update id="updateInboundOrderItems" parameterType="com.ruoyi.inventory.domain.InboundOrderItems">
        update inbound_order_items
        <trim prefix="SET" suffixOverrides=",">
            <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="warehouseId != null">warehouse_id = #{warehouseId},</if>
            <if test="locationId != null">location_id = #{locationId},</if>
            <if test="plannedQuantity != null">planned_quantity = #{plannedQuantity},</if>
            <if test="actualQuantity != null">actual_quantity = #{actualQuantity},</if>
            <if test="plannedPackages != null">planned_packages = #{plannedPackages},</if>
            <if test="actualPackages != null">actual_packages = #{actualPackages},</if>
            <if test="divisor != null">divisor = #{divisor},</if>
            <if test="labelColor != null">label_color = #{labelColor},</if>
            <if test="voucherNumber != null">voucher_number = #{voucherNumber},</if>
            <if test="unitPrice != null">unit_price = #{unitPrice},</if>
            <if test="itemStatus != null">item_status = #{itemStatus},</if>
            <if test="receivedAt != null">received_at = #{receivedAt},</if>
            <if test="receivedBy != null">received_by = #{receivedBy},</if>
            <if test="labelQuantity != null">label_quantity = #{labelQuantity},</if>
            <if test="remark != null">remark = #{remark},</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="inboundOrderId != null">inbound_order_id = #{inboundOrderId},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteInboundOrderItemsById" parameterType="String">
        delete from inbound_order_items where id = #{id}
    </delete>

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

<!--    入库统计页面的表格查询sql和映射字段 -->
    <resultMap id="InboundDetailsResultMap" type="com.ruoyi.inventory.domain.vo.InboundDetailsVO">
        <!-- 基础物料字段 -->
        <result column="material_id" property="materialId" jdbcType="VARCHAR" />
        <result column="sap_no" property="sapNo" jdbcType="VARCHAR" />
        <result column="material_name" property="materialName" jdbcType="VARCHAR" />

        <!-- 新增：TS编码映射 -->
        <result column="ts_code" property="tsCode" jdbcType="VARCHAR" />
        <!-- 新增：危险等级ID映射 -->
        <result column="hazard_id" property="hazardId" jdbcType="VARCHAR" />
        <!-- 新增：物料规格映射 -->
        <result column="specification" property="specification" jdbcType="VARCHAR" />
        <!-- 新增：物料单位映射 -->
        <result column="material_unit" property="materialUnit" jdbcType="VARCHAR" />
        <!-- 新增：单重映射 -->
        <result column="unit_weight" property="unitWeight" jdbcType="DOUBLE" />
        <!-- 新增：计划入库数量映射 -->
        <result column="planned_quantity" property="plannedQuantity" jdbcType="BIGINT" />

        <!-- 入库单/批次/仓库/库位字段 -->
        <result column="order_id" property="orderId" jdbcType="VARCHAR" />
        <result column="batch_id" property="batchId" jdbcType="VARCHAR" />
        <result column="warehouse_id" property="warehouseId" jdbcType="VARCHAR" />
        <result column="warehouses_name" property="warehousesName" jdbcType="VARCHAR" />
        <result column="location_id" property="locationId" jdbcType="VARCHAR"/>
        <result column="location_name" property="locationName" jdbcType="VARCHAR" />

        <!-- 数值型字段 -->
        <result column="actual_quantity" property="actualQuantity" jdbcType="BIGINT" />
        <result column="actual_packages" property="actualPackages" jdbcType="BIGINT" />

        <!-- 字符串字段 -->
        <result column="label_color" property="labelColor" jdbcType="VARCHAR" />

        <!-- 金额字段 -->
        <result column="unit_price" property="unitPrice" jdbcType="DOUBLE" />
        <result column="total_price" property="totalPrice" jdbcType="DOUBLE" />

        <!-- 备注&日期字段 -->
        <result column="remark" property="remark" jdbcType="VARCHAR" />

        <result column="inbound_date" property="inboundDate" />
    </resultMap>

    <select id="selectInboundDetailsVOBySapNo" resultMap="InboundDetailsResultMap" parameterType="com.ruoyi.inventory.domain.vo.InboundDetailsVO">
        SELECT
        ioi.material_id,
        m.sap_no,
        m.material_name,
        m.ts_code,
        m.hazard_id,
        m.specification,
        m.material_unit,
        m.unit_weight,
        SUM(ioi.planned_quantity) AS planned_quantity,
        SUM(ioi.actual_quantity) AS actual_quantity,
        SUM(ioi.actual_packages) AS actual_packages,
        ioi.unit_price,
        SUM(ioi.unit_price * ioi.actual_quantity) AS total_price
        FROM inbound_order_items as ioi
        left join materials as m on ioi.material_id = m.id
        left join inbound_orders as io on ioi.inbound_order_id = io.id
        <where>
            io.order_status = 2
<!--            <if test="inboundDateStart != null">-->
<!--                AND DATE(io.inbound_date) &gt;= #{inboundDateStart}-->
<!--            </if>-->
<!--            <if test="inboundDateEnd != null">-->
<!--                AND DATE(io.inbound_date) &lt;= #{inboundDateEnd}-->
<!--            </if>-->
            <if test="sapNo != null and sapNo != ''">
                AND m.sap_no like concat('%', #{sapNo}, '%')
            </if>
            <if test="materialName != null and materialName != ''">
                AND m.material_name LIKE CONCAT('%', #{materialName}, '%')
            </if>
            <if test="orderId != null and orderId != ''">
                AND ioi.order_id like concat('%', #{orderId}, '%')
            </if>
            <if test="batchId != null and batchId != ''">
                AND ioi.batch_id like concat('%', #{batchId}, '%')
            </if>
            <if test="warehouseId != null and warehouseId != ''">
                AND ioi.warehouse_id = #{warehouseId}
            </if>
            <if test="locationId != null and locationId != ''">
                AND ioi.location_id = #{locationId}
            </if>
            <if test="labelColor != null and labelColor != ''">
                AND ioi.label_color = #{labelColor}
            </if>
        </where>
        GROUP BY
        ioi.material_id,
        ioi.unit_price,
        m.sap_no,
        m.material_name,
        m.ts_code,
        m.hazard_id,
        m.specification,
        m.material_unit,
        m.unit_weight
        order by ioi.material_id asc
    </select>
<!--    入库统计点击明细查看该物料的关联入库单和批次-->
    <select id="selectDetailsByMaterialId" parameterType="com.ruoyi.inventory.domain.vo.InboundDetailsVO" resultMap="InboundDetailsResultMap">
        SELECT
        ioi.material_id,
        m.material_name,
        ioi.order_id,
        ioi.batch_id,
        ioi.warehouse_id,
        w.warehouses_name,
        ioi.location_id,
        sl.location_name,
        ioi.planned_quantity,
        ioi.actual_quantity,
        ioi.unit_price,
        SUM(ioi.unit_price * ioi.actual_quantity) AS total_price,
        ioi.remark,
        io.inbound_date
        FROM inbound_order_items ioi
        left join materials m ON m.id = ioi.material_id
        left join inbound_orders io ON io.id = ioi.inbound_order_id
        left join warehouses w on ioi.warehouse_id = w.id
        left join storage_locations sl on ioi.location_id = sl.id
        <where>
            io.order_status = 2
            <if test="materialId != null and materialId != ''">
                AND ioi.material_id  = #{materialId}
            </if>
            <if test="inboundDateStart != null">
                AND DATE(io.inbound_date) &gt;= #{inboundDateStart}
            </if>
            <if test="inboundDateEnd != null">
                AND DATE(io.inbound_date) &lt;= #{inboundDateEnd}
            </if>
            <if test="orderId != null and orderId != ''">
                AND ioi.order_id like concat('%', #{orderId}, '%')
            </if>
            <if test="batchId != null and batchId != ''">
                AND ioi.batch_id like concat('%', #{batchId}, '%')
            </if>
            <if test="warehouseId != null and warehouseId != ''">
                AND ioi.warehouse_id = #{warehouseId}
            </if>
            <if test="locationId != null and locationId != ''">
                AND ioi.location_id = #{locationId}
            </if>
        </where>
        GROUP BY
        ioi.material_id,
        ioi.batch_id,
        ioi.warehouse_id,
        ioi.location_id,
        ioi.order_id,
        ioi.unit_price,
        ioi.remark,
        io.inbound_date
        order by date(inbound_date) desc
    </select>

    <select id="exportInboundDetails" resultMap="InboundDetailsResultMap" parameterType="com.ruoyi.inventory.domain.vo.InboundDetailsVO">
        SELECT
        ioi.material_id,
        m.sap_no,
        m.material_name,
        m.ts_code,
        m.hazard_id,
        m.specification,
        m.material_unit,
        m.unit_weight,

        ioi.order_id,
        ioi.batch_id,
        ioi.warehouse_id,
        w.warehouses_name,
        ioi.location_id,
        sl.location_name,
        ioi.remark,
        io.inbound_date,

        SUM(ioi.planned_quantity) AS planned_quantity,
        SUM(ioi.actual_quantity) AS actual_quantity,
        SUM(ioi.actual_packages) AS actual_packages,
        ioi.unit_price,
        SUM(ioi.unit_price * ioi.actual_quantity) AS total_price

        FROM inbound_order_items as ioi
        LEFT JOIN materials as m ON ioi.material_id = m.id
        LEFT JOIN inbound_orders as io ON ioi.inbound_order_id = io.id
        LEFT JOIN warehouses w ON ioi.warehouse_id = w.id
        LEFT JOIN storage_locations sl ON ioi.location_id = sl.id

        <where>
            io.order_status = 2

            <if test="sapNo != null and sapNo != ''">
                AND m.sap_no like concat('%', #{sapNo}, '%')
            </if>
            <if test="materialName != null and materialName != ''">
                AND m.material_name LIKE CONCAT('%', #{materialName}, '%')
            </if>

            <if test="warehouseId != null and warehouseId != ''">
                AND ioi.warehouse_id = #{warehouseId}
            </if>
            <if test="locationId != null and locationId != ''">
                AND ioi.location_id = #{locationId}
            </if>
            <if test="labelColor != null and labelColor != ''">
                AND ioi.label_color = #{labelColor}
            </if>

        </where>

        GROUP BY
        ioi.material_id,
        m.sap_no,
        m.material_name,
        m.ts_code,
        m.hazard_id,
        m.specification,
        m.material_unit,
        m.unit_weight,
        ioi.order_id,
        ioi.batch_id,
        ioi.warehouse_id,
        w.warehouses_name,
        ioi.location_id,
        sl.location_name,
        ioi.remark,
        io.inbound_date,
        ioi.unit_price

        ORDER BY DATE(io.inbound_date) DESC, ioi.material_id ASC
    </select>
</mapper>