提交

廖井涛

2024-03-27 6a77a2b08b1780189cd60124ff5b5d3ef406ea69

提交

 north-glass-erp/.idea/dataSources.local.xml

@@ -23,6 +23,7 @@
      <database-info product="MySQL" version="8.0.19" jdbc-version="4.2" driver-name="MySQL Connector/J" driver-version="mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)" dbms="MYSQL" exact-version="8.0.19" exact-driver-version="8.0">
        <extra-name-characters>#@</extra-name-characters>
        <identifier-quote-string>`</identifier-quote-string>
        <jdbc-catalog-is-schema>true</jdbc-catalog-is-schema>
      </database-info>
      <case-sensitivity plain-identifiers="lower" quoted-identifiers="lower" />
      <secret-storage>master_key</secret-storage>

 north-glass-erp/target/classes/mapper/mm/MaterialInventory.xml

New file
@@ -0,0 +1,317 @@
<?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.example.erp.mapper.mm.MaterialInventoryMapper">
    <resultMap id="SelectMaterialOutbound" type="com.example.erp.entity.mm.MaterialOutboundDetail">

        <result column="mid" property="id"/>
        <result column="material_outbound_id" property="materialOutboundId"/>
        <result column="material_outbound_number" property="materialOutboundNumber"/>
        <result column="inventory_organization" property="inventoryOrganization"/>
        <result column="material_code" property="materialCode"/>
        <result column="material_name" property="materialName"/>
        <result column="producer" property="producer"/>
        <result column="width" property="width"/>
        <result column="height" property="height"/>
        <result column="unit" property="unit"/>
        <result column="thickness" property="thickness"/>
        <result column="outbound_quantity" property="outboundQuantity"/>
        <result column="single_piece_area" property="singlePieceArea"/>
        <result column="inventory_area" property="inventoryArea"/>
        <result column="date_of_manufacture" property="dateOfManufacture"/>
        <result column="remarks" property="remarks"/>

        <result column="inventory_quantity" property="materialInventory.inventoryQuantity"/>
        <result column="available_quantity" property="materialInventory.availableQuantity"/>

        <result column="reviewed_state" property="materialOutbound.reviewedState"/>


    </resultMap>


    <select id="getSelectMaterialInventoryDate">
        select
        mi.id,
        mi.inventory_organization as inventoryOrganization,
        mi.material_code AS materialCode,
        mi.producer,
        mi.inventory_quantity AS inventoryQuantity,
        mi.available_quantity AS availableQuantity,
        mi.safety_stock AS safetyStock,
        mi.total_area AS totalArea,
        mi.single_piece_area singlePieceArea,
        date(mi.date_of_manufacture) AS dateOfManufacture,
        mi.quality_guarantee_period qualityGuaranteePeriod,
        mi.inventory_area AS inventoryArea,
        mi.dead_stock AS deadStock,
        mi.remarks,
        date(mi.create_time) AS createTime,
        ms.json
        from mm.material_inventory mi left join mm.material_store ms on mi.material_code=ms.id
        <where>
                date(mi.create_time)>=#{startDate} and date(mi.create_time) &lt;= #{endDate} and mi.available_quantity>0

        </where>
        limit #{offset},#{pageSize};
    </select>

    <select id="getSelectMaterialInventoryDatePageTotal">
        select
        CEILING(count(id)/#{pageSize}) as 'pageTotal',
        count(id) as 'total'
        from
        mm.material_inventory mi
        <where>
                date(mi.create_time)>=#{startDate} and date(mi.create_time) &lt;= #{endDate} and mi.available_quantity>0

        </where>
        limit #{offset},#{pageSize};
    </select>


    <select id="getSelectMaterialInventory">
        select
        mi.id,
        mi.inventory_organization as inventoryOrganization,
        mi.material_code AS materialCode,
        mi.producer,
        mi.inventory_quantity AS inventoryQuantity,
        mi.available_quantity AS availableQuantity,
        mi.safety_stock AS safetyStock,
        mi.total_area AS totalArea,
        mi.single_piece_area singlePieceArea,
        date(mi.date_of_manufacture) AS dateOfManufacture,
        mi.quality_guarantee_period qualityGuaranteePeriod,
        mi.inventory_area AS inventoryArea,
        mi.dead_stock AS deadStock,
        mi.remarks,
        date(mi.create_time) AS createTime,
        ms.json
        from mm.material_inventory mi left join mm.material_store ms on mi.material_code=ms.id
        <where>
            <if test="materialInventory.stockId != null and materialInventory.stockId != ''">
                and mi.id regexp #{materialInventory.stockId}
            </if>

        </where>
        limit #{offset},#{pageSize};
    </select>

    <select id="getSelectMaterialInventoryPageTotal">
        select
        CEILING(count(id)/#{pageSize}) as 'pageTotal',
        count(id) as 'total'
        from
        mm.material_inventory mi
        <where>
            <if test="materialInventory.id != null and materialInventory.id != ''">
                and mi.id regexp #{materialInventory.id}
            </if>

        </where>

        limit #{offset},#{pageSize};
    </select>


    <select id="getSelectMaterialOutboundDate">
        select
        *
        from
        mm.material_outbound mo
        <where>
            date(mo.create_time)>=#{startDate} and date(mo.create_time) &lt;= #{endDate}
        </where>
        order by mo.reviewed_state desc,mo.create_time desc
        limit #{offset},#{pageSize};
    </select>

    <select id="getSelectMaterialOutboundDatePageTotal">
        select
        CEILING(count(id)/#{pageSize}) as 'pageTotal',
        count(id) as 'total'
        from
        mm.material_outbound mo
        <where>
            date(mo.create_time)>=#{startDate} and date(mo.create_time) &lt;= #{endDate}
        </where>
        limit #{offset},#{pageSize};
    </select>

    <select id="getSelectMaterialOutbound">
        select
        *
        from
        mm.material_outbound mo
        <where>
            <if test="materialOutboundDetail.materialOutboundId != null and materialOutboundDetail.materialOutboundId != ''">
                and mo.material_outbound_id regexp #{materialOutboundDetail.materialOutboundId}
            </if>
        </where>
        limit #{offset},#{pageSize};
    </select>

    <select id="getSelectMaterialOutboundDetail" >
        select
        modd.inventory_id as id,
        modd.inventory_organization as inventoryOrganization,
        modd.material_code as materialCode,
        modd.material_name as materialName,
        modd.producer,
        modd.width,
        modd.height,
        modd.thickness,
        modd.unit,
        modd.outbound_quantity as outboundQuantity,
        date(modd.date_of_manufacture) as dateOfManufacture,
        modd.single_piece_area as singlePieceArea,
        modd.inventory_area as inventoryArea,
        modd.remarks,
        mi.inventory_quantity as inventoryQuantity,
        if(mo.reviewed_state!=1,modd.outbound_quantity+mi.available_quantity,mi.available_quantity) as availableQuantity,
        ms.json
        from mm.material_outbound_detail modd left join mm.material_inventory mi on modd.inventory_id=mi.id
            left join mm.material_outbound mo on modd.material_outbound_id=mo.material_outbound_id
            left join mm.material_store ms on modd.material_code=ms.id
        <where>
            <if test="materialOutboundDetail.materialOutboundId != null and materialOutboundDetail.materialOutboundId != ''">
                and modd.material_outbound_id regexp #{materialOutboundDetail.materialOutboundId}
            </if>
        </where>
        limit #{offset},#{pageSize};
    </select>

    <select id="getSelectMaterialOutboundDetailPageTotal">
        select
        CEILING(count(id)/#{pageSize}) as 'pageTotal',
        count(id) as 'total'
        from
        mm.material_outbound_detail modd
        <where>
            <if test="materialOutboundDetail.materialOutboundId != null and materialOutboundDetail.materialOutboundId != ''">
                and modd.material_outbound_id regexp #{materialOutboundDetail.materialOutboundId}
            </if>
        </where>
        limit #{offset},#{pageSize};
    </select>


    <insert id="insertMaterialStore"  useGeneratedKeys="true"  >
        insert into mm.material_store (type,json,create_time
        )
        values (
                   #{type},#{json},now()
               )
    </insert>

    <update id="updateMaterialStore" >
        update mm.material_store set json=#{json} where id=#{id}
    </update>

    <delete id="deleteMaterialOutbound" >
        delete from mm.material_outbound where material_outbound_id=#{materialOutboundId}
    </delete>

    <delete id="deleteMaterialOutboundDetail" >
        delete from mm.material_outbound_detail where material_outbound_id=#{materialOutboundId}
    </delete>

    <select id="getmaximum" >
        select count(*) from mm.material_outbound where  date(create_time)=CURDATE()
    </select>

    <select id="getMaterialOutboundCount" >
        select count(*) from mm.material_outbound where  material_outbound_id=#{materialOutboundId}
    </select>

    <select id="getMaterialOutboundDetailMaximum" >
        select count(*) from mm.material_outbound_detail where  material_outbound_id=#{materialOutboundId}
    </select>

    <select id="getMaterialInventoryCount" >
        select count(*) from mm.material_inventory where  material_code=#{materialCode} and date_of_manufacture=#{dateOfManufacture}
    </select>

    <select id="getIsNotMaterialOutboundDetail" >
        select * from mm.material_outbound_detail where  material_outbound_id=#{materialOutboundId}
    </select>

    <insert id="insertMaterialInventoryMapper"  useGeneratedKeys="true" >
        insert into mm.material_outbound(material_outbound_id,material_requisition_personnel,material_requisition_team,order_id,outbound_type,
                                         warehouse_manager,material_requisition_date,reviewed_state,create_time)
        values (
                  #{number} ,#{materialOutbound.materialRequisitionPersonnel},#{materialOutbound.materialRequisitionTeam},
                #{materialOutbound.orderId},#{materialOutbound.outboundType},#{materialOutbound.warehouseManager},#{materialOutbound.materialRequisitionDate},
                   0,now()
               )
    </insert>

    <insert id="insertMaterialOutboundDetail"  useGeneratedKeys="true" >
        insert into mm.material_outbound_detail (material_outbound_id,material_outbound_number,material_code,material_name,inventory_id,inventory_organization,producer,unit,width,
                                                 height,thickness,outbound_quantity,single_piece_area,inventory_area,date_of_manufacture,remarks)
        values (
                   #{number} ,#{materialOutboundNumber},#{materialOutboundDetail.materialCode},#{materialOutboundDetail.materialName},#{materialOutboundDetail.id},
                   #{materialOutboundDetail.inventoryOrganization},#{materialOutboundDetail.producer},#{materialOutboundDetail.unit},
                   #{materialOutboundDetail.width},#{materialOutboundDetail.height},#{materialOutboundDetail.thickness},#{materialOutboundDetail.outboundQuantity},
                #{materialOutboundDetail.singlePieceArea},#{materialOutboundDetail.inventoryArea},#{materialOutboundDetail.dateOfManufacture},#{materialOutboundDetail.remarks}
               )
    </insert>

    <insert id="insertMaterialInventory"  useGeneratedKeys="true" >
        insert into mm.material_inventory (inventory_organization, material_code, producer, stock_id,
                                            inventory_quantity, available_quantity, safety_stock, total_area, single_piece_area,
                                           date_of_manufacture, quality_guarantee_period, inventory_area, dead_stock, remarks, create_time)
        values (
                   #{materialInventory.inventoryOrganization} ,#{materialCode},
                #{materialInventory.producer},0,#{materialInventory.inventoryQuantity},#{materialInventory.inventoryQuantity},0,
                   #{totalArea},#{singlePieceArea},#{materialInventory.dateOfManufacture},#{materialInventory.qualityGuaranteePeriod},
                   #{materialInventory.inventoryArea},0,#{materialInventory.remarks},now()
               )
    </insert>

    <update id="updateMaterialInventoryAvailableOut">
        update mm.material_inventory
        set available_quantity=available_quantity-#{quantity}
        where id=#{inventoryId}
    </update>

    <update id="updateMaterialInventoryAvailableInt">
        update mm.material_inventory
        set available_quantity=available_quantity+#{quantity}
        where id=#{inventoryId}
    </update>

    <update id="updateMaterialInventoryInventoryOut">
        update mm.material_inventory
        set inventory_quantity=inventory_quantity-#{quantity}
        where id=#{inventoryId}
    </update>

    <update id="updateMaterialInventoryInventoryInt">
        update mm.material_inventory
        set inventory_quantity=inventory_quantity+#{quantity}
        where id=#{inventoryId}
    </update>


    <update id="updateMaterialOutboundToExamine">
        update mm.material_outbound set reviewed_state=#{reviewedState},reviewed=#{reviewed},reviewed_time=now()
        where material_outbound_id=#{materialOutboundId}
    </update>

    <update id="updateMaterialOutboundCounterExamination">
        update mm.material_outbound set reviewed_state=#{reviewedState}
        where material_outbound_id=#{materialOutboundId}
    </update>

    <update id="updateMaterialInventory" >
        update mm.material_inventory set inventory_quantity=inventory_quantity+#{materialInventory.inventoryQuantity},
                                         available_quantity=available_quantity+#{materialInventory.inventoryQuantity},
                                         total_area=total_area+#{totalArea}
                                     where  material_code=#{materialCode} and date_of_manufacture=#{materialInventory.dateOfManufacture}
    </update>


</mapper>