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

    <resultMap type="Materials" id="MaterialsResult">
        <result property="id"    column="id"    />
        <result property="materialCode"    column="material_code"    />
        <result property="materialName"    column="material_name"    />
        <result property="materialEname"    column="material_ename"    />
        <result property="sapNo"    column="sap_no"    />
        <result property="tsCode"    column="ts_code"    />
        <result property="categoryCode"    column="category_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="volume"    column="volume"    />
        <result property="shelfLifeDays"    column="shelf_life_days"    />
        <result property="storageTemperature"    column="storage_temperature"    />
        <result property="specialRequirements"    column="special_requirements"    />
        <result property="isBatchManaged"    column="is_batch_managed"    />
        <result property="isSerialManaged"    column="is_serial_managed"    />
        <result property="minStockLevel"    column="min_stock_level"    />
        <result property="maxStockLevel"    column="max_stock_level"    />
        <result property="isUsed"    column="is_used"    />
        <result property="isActive"    column="is_active"    />
        <result property="riskLevel"    column="risk_level"    />
        <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="stock"    column="stock"    />
    </resultMap>

    <sql id="selectMaterialsVo">
        select id, material_code, material_name, material_ename, sap_no, ts_code, category_code, hazard_id, specification, material_unit, unit_weight, package_weight, total_weight, volume, shelf_life_days, storage_temperature, special_requirements, is_batch_managed, is_serial_managed, min_stock_level, max_stock_level, is_used, is_active, risk_level, sort_no, create_time, create_user_code, update_time, update_user_code from materials
    </sql>

    <select id="selectMaterialsList" parameterType="Materials" resultMap="MaterialsResult">
        <include refid="selectMaterialsVo"/>
        <where>
            is_used = 1
            <if test="materialCode != null  and materialCode != ''"> and material_code like concat('%', #{materialCode}, '%')</if>
            <if test="materialName != null  and materialName != ''"> and material_name like concat('%', #{materialName}, '%')</if>
            <if test="sapNo != null  and sapNo != ''"> and sap_no like concat('%', #{sapNo}, '%')</if>
            <if test="tsCode != null  and tsCode != ''"> and ts_code like concat('%', #{tsCode}, '%')</if>
            <if test="categoryCode != null  and categoryCode != ''"> and category_code = #{categoryCode}</if>
            <if test="hazardId != null  and hazardId != ''"> and hazard_id like concat('%', #{hazardId}, '%')</if>
            <if test="specification != null  and specification != ''"> and specification like concat('%', #{specification}, '%')</if>
            <if test="materialUnit != null  and materialUnit != ''"> and material_unit like concat('%', #{materialUnit}, '%')</if>
            <if test="unitWeight != null "> and unit_weight = #{unitWeight}</if>
            <if test="packageWeight != null "> and package_weight = #{packageWeight}</if>
            <if test="totalWeight != null "> and total_weight = #{totalWeight}</if>
            <if test="volume != null "> and volume = #{volume}</if>
            <if test="shelfLifeDays != null "> and shelf_life_days = #{shelfLifeDays}</if>
            <if test="storageTemperature != null  and storageTemperature != ''"> and storage_temperature like concat('%', #{storageTemperature}, '%')</if>
            <if test="specialRequirements != null  and specialRequirements != ''"> and special_requirements like concat('%', #{specialRequirements}, '%')</if>
            <if test="isBatchManaged != null "> and is_batch_managed = #{isBatchManaged}</if>
            <if test="isSerialManaged != null "> and is_serial_managed = #{isSerialManaged}</if>
            <if test="minStockLevel != null "> and min_stock_level = #{minStockLevel}</if>
            <if test="maxStockLevel != null "> and max_stock_level = #{maxStockLevel}</if>
            <if test="isUsed != null "> and is_used = #{isUsed}</if>
            <if test="isActive != null "> and is_active = #{isActive}</if>
            <if test="riskLevel != null  and riskLevel != ''"> and risk_level like concat('%', #{riskLevel}, '%')</if>
            <if test="sortNo != null "> and sort_no = #{sortNo}</if>
            <if test="createTime != null "> and create_time like concat('%', #{createTime}, '%')</if>
            <if test="updateTime != null "> and update_time like concat('%', #{updateTime}, '%')</if>
        </where>
        order by sort_no asc
    </select>

    <select id="selectMaterialsInventoryList" parameterType="Materials" resultMap="MaterialsResult">
        select
        m.id,
        m.material_code,
        m.material_name,
        m.material_ename,
        m.sap_no,
        m.ts_code,
        m.category_code,
        m.hazard_id,
        m.specification,
        m.material_unit,
        m.unit_weight,
        m.package_weight,
        m.total_weight,
        m.volume,
        m.shelf_life_days,
        m.storage_temperature,
        m.special_requirements,
        m.is_batch_managed,
        m.is_serial_managed,
        m.min_stock_level,
        m.max_stock_level,
        IFNULL(stock_sum.total_stock, 0) as stock,
        m.is_used,
        m.is_active,
        m.risk_level,
        m.sort_no,
        m.create_time,
        m.create_user_code,
        m.update_time,
        m.update_user_code
        from materials m
        left join (
        select
        material_id,
        SUM(quantity - locked_quantity) as total_stock
        from inventory
        group by material_id
        ) stock_sum on m.id = stock_sum.material_id
        <where>
            m.is_used = 1
            <if test="materialCode != null  and materialCode != ''"> and m.material_code like concat('%', #{materialCode}, '%')</if>
            <if test="materialName != null  and materialName != ''"> and m.material_name like concat('%', #{materialName}, '%')</if>
            <if test="sapNo != null  and sapNo != ''"> and m.sap_no like concat('%', #{sapNo}, '%')</if>
            <if test="tsCode != null  and tsCode != ''"> and m.ts_code like concat('%', #{tsCode}, '%')</if>
            <if test="categoryCode != null  and categoryCode != ''"> and m.category_code = #{categoryCode}</if>
            <if test="hazardId != null  and hazardId != ''"> and m.hazard_id like concat('%', #{hazardId}, '%')</if>
            <if test="specification != null  and specification != ''"> and m.specification like concat('%', #{specification}, '%')</if>
            <if test="materialUnit != null  and materialUnit != ''"> and m.material_unit like concat('%', #{materialUnit}, '%')</if>
            <if test="unitWeight != null "> and m.unit_weight = #{unitWeight}</if>
            <if test="packageWeight != null "> and m.package_weight = #{packageWeight}</if>
            <if test="totalWeight != null "> and m.total_weight = #{totalWeight}</if>
            <if test="volume != null "> and m.volume = #{volume}</if>
            <if test="shelfLifeDays != null "> and m.shelf_life_days = #{shelfLifeDays}</if>
            <if test="storageTemperature != null  and storageTemperature != ''"> and m.storage_temperature like concat('%', #{storageTemperature}, '%')</if>
            <if test="specialRequirements != null  and specialRequirements != ''"> and m.special_requirements like concat('%', #{specialRequirements}, '%')</if>
            <if test="isBatchManaged != null "> and m.is_batch_managed = #{isBatchManaged}</if>
            <if test="isSerialManaged != null "> and m.is_serial_managed = #{isSerialManaged}</if>
            <if test="minStockLevel != null "> and m.min_stock_level = #{minStockLevel}</if>
            <if test="maxStockLevel != null "> and m.max_stock_level = #{maxStockLevel}</if>
            <if test="isUsed != null "> and m.is_used = #{isUsed}</if>
            <if test="isActive != null "> and m.is_active = #{isActive}</if>
            <if test="riskLevel != null  and riskLevel != ''"> and m.risk_level like concat('%', #{riskLevel}, '%')</if>
            <if test="sortNo != null "> and m.sort_no = #{sortNo}</if>
            <if test="createTime != null "> and m.create_time like concat('%', #{createTime}, '%')</if>
            <if test="updateTime != null "> and m.update_time like concat('%', #{updateTime}, '%')</if>
            <if test="stock != null "> and stock_sum.total_stock = #{stock}</if>
        </where>
        order by stock desc
    </select>

    <select id="selectMaterialsById" parameterType="String" resultMap="MaterialsResult">
        <include refid="selectMaterialsVo"/>
        where id = #{id}
        and is_used = 1
        order by sort_no asc
    </select>
    <select id="selectMaterialsBySapNo" parameterType="String" resultMap="MaterialsResult">
        <include refid="selectMaterialsVo"/>
        where sap_no = #{id}
        and is_used = 1
        order by sort_no asc
    </select>
    <select id="selectMaterialsByCategory" parameterType="String" resultMap="MaterialsResult">
        <include refid="selectMaterialsVo"/>
        where category_code = #{id}
        and is_used = 1
        order by sort_no asc
    </select>
<!--    获取物料 的 sap_no Name 做成字典-->
    <select id="getMapList" resultType="java.util.Map">
        select id, IFNULL(material_name, '') as material_name from materials where is_used = 1;
    </select>

    <insert id="insertMaterials" parameterType="Materials">
        insert into materials
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="materialCode != null">material_code,</if>
            <if test="materialName != null">material_name,</if>
            <if test="materialEname != null">material_ename,</if>
            <if test="sapNo != null">sap_no,</if>
            <if test="tsCode != null">ts_code,</if>
            <if test="categoryCode != null">category_code,</if>
            <if test="hazardId != null">hazard_id,</if>
            <if test="specification != null">specification,</if>
            <if test="materialUnit != null">material_unit,</if>
            <if test="unitWeight != null">unit_weight,</if>
            <if test="packageWeight != null">package_weight,</if>
            <if test="totalWeight != null">total_weight,</if>
            <if test="volume != null">volume,</if>
            <if test="shelfLifeDays != null">shelf_life_days,</if>
            <if test="storageTemperature != null">storage_temperature,</if>
            <if test="specialRequirements != null">special_requirements,</if>
            <if test="isBatchManaged != null">is_batch_managed,</if>
            <if test="isSerialManaged != null">is_serial_managed,</if>
            <if test="minStockLevel != null">min_stock_level,</if>
            <if test="maxStockLevel != null">max_stock_level,</if>
            <if test="isUsed != null">is_used,</if>
            <if test="isActive != null">is_active,</if>
            <if test="riskLevel != null">risk_level,</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>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="materialCode != null">#{materialCode},</if>
            <if test="materialName != null">#{materialName},</if>
            <if test="materialEname != null">#{materialEname},</if>
            <if test="sapNo != null">#{sapNo},</if>
            <if test="tsCode != null">#{tsCode},</if>
            <if test="categoryCode != null">#{categoryCode},</if>
            <if test="hazardId != null">#{hazardId},</if>
            <if test="specification != null">#{specification},</if>
            <if test="materialUnit != null">#{materialUnit},</if>
            <if test="unitWeight != null">#{unitWeight},</if>
            <if test="packageWeight != null">#{packageWeight},</if>
            <if test="totalWeight != null">#{totalWeight},</if>
            <if test="volume != null">#{volume},</if>
            <if test="shelfLifeDays != null">#{shelfLifeDays},</if>
            <if test="storageTemperature != null">#{storageTemperature},</if>
            <if test="specialRequirements != null">#{specialRequirements},</if>
            <if test="isBatchManaged != null">#{isBatchManaged},</if>
            <if test="isSerialManaged != null">#{isSerialManaged},</if>
            <if test="minStockLevel != null">#{minStockLevel},</if>
            <if test="maxStockLevel != null">#{maxStockLevel},</if>
            <if test="isUsed != null">#{isUsed},</if>
            <if test="isActive != null">#{isActive},</if>
            <if test="riskLevel != null">#{riskLevel},</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>
         </trim>
    </insert>

    <update id="updateMaterials" parameterType="Materials">
        update materials
        <trim prefix="SET" suffixOverrides=",">
            <if test="materialCode != null">material_code = #{materialCode},</if>
            <if test="materialName != null">material_name = #{materialName},</if>
            <if test="materialEname != null">material_ename = #{materialEname},</if>
            <if test="sapNo != null">sap_no = #{sapNo},</if>
            <if test="tsCode != null">ts_code = #{tsCode},</if>
            <if test="categoryCode != null">category_code = #{categoryCode},</if>
            <if test="hazardId != null">hazard_id = #{hazardId},</if>
            <if test="specification != null">specification = #{specification},</if>
            <if test="materialUnit != null">material_unit = #{materialUnit},</if>
            <if test="unitWeight != null">unit_weight = #{unitWeight},</if>
            <if test="packageWeight != null">package_weight = #{packageWeight},</if>
            <if test="totalWeight != null">total_weight = #{totalWeight},</if>
            <if test="volume != null">volume = #{volume},</if>
            <if test="shelfLifeDays != null">shelf_life_days = #{shelfLifeDays},</if>
            <if test="storageTemperature != null">storage_temperature = #{storageTemperature},</if>
            <if test="specialRequirements != null">special_requirements = #{specialRequirements},</if>
            <if test="isBatchManaged != null">is_batch_managed = #{isBatchManaged},</if>
            <if test="isSerialManaged != null">is_serial_managed = #{isSerialManaged},</if>
            <if test="minStockLevel != null">min_stock_level = #{minStockLevel},</if>
            <if test="maxStockLevel != null">max_stock_level = #{maxStockLevel},</if>
            <if test="isUsed != null">is_used = #{isUsed},</if>
            <if test="isActive != null">is_active = #{isActive},</if>
            <if test="riskLevel != null">risk_level = #{riskLevel},</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>
        </trim>
        where id = #{id}
    </update>

    <update id="updateMaterialsIsUsedByIds">
        update materials set is_used = 0 where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <delete id="deleteMaterialsById" parameterType="String">
        delete from materials where id = #{id}
    </delete>

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

<!--    <resultMap id="SapAndIdMap" type="java.util.Map">-->
<!--        <result column="sap_no" property="key"/>-->
<!--        <result column="id" property="value"/>-->
<!--    </resultMap>-->
    <select id="selectMaterialIdAndSapMap" resultType="java.util.Map">
        select id,sap_no from materials where is_used = 1
    </select>

    <!-- 精简后的resultMap -->
    <resultMap type="Materials" id="MaterialSapResult">
       <result column="sap_no" property="sapNo"/>
    </resultMap>

    <select id="sapIsExist" resultMap="MaterialSapResult" parameterType="java.util.List">
        select sap_no from materials
        <where>
            is_used = 1
            and sap_no in
            <foreach collection="list" item="sapNo" separator="," open="(" close=")">
                #{sapNo}
            </foreach>
        </where>
    </select>
</mapper>