Mapper格式记录

Mapper格式文件

<?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.system.mapper.ms.Wm.DetialViewMapper">
​
    <resultMap type="DetialView" id="DetialViewResult">
        <result property="orgId"    column="org_id"    />
        <result property="orgName"    column="org_name"    />
        <result property="barcodeId"    column="barcode_id"    />
        <result property="isStocked"    column="is_stocked"    />
        <result property="itemCode"    column="item_code"    />
        <result property="itemName"    column="item_name"    />
        <result property="itemId"    column="item_id"    />
        <result property="sourceBill"    column="source_bill"    />
        <result property="sourceId"    column="source_id"    />
        <result property="sourceLineNo"    column="source_line_no"    />
        <result property="batchNumber"    column="batch_number"    />
        <result property="baseUnit"    column="base_unit"    />
        <result property="qty"    column="qty"    />
        <result property="boxCode"    column="box_code"    />
        <result property="warehouseName"    column="warehouse_name"    />
        <result property="warehouseCode"    column="warehouse_code"    />
        <result property="locationCode"    column="location_code"    />
        <result property="locationName"    column="location_name"    />
        <result property="areaName"    column="area_name"    />
        <result property="areaCode"    column="area_code"    />
    </resultMap>
    <!--视图表的查询-->
    <sql id="selectDetialViewVo">
        select org_id,org_name, barcode_id, is_stocked, item_code, item_name, item_id, source_bill, source_id, source_line_no, batch_number, base_unit, qty,box_code, warehouse_name, warehouse_code, location_code, location_name, area_name, area_code from wm_stock_detail_view
    </sql>
    <!--原本表的查询-->
    <sql id="selectDetialView">
        select org_id,org_name, barcode_id, is_stocked, item_code, item_name, item_id, source_bill, source_id, source_line_no, batch_number, base_unit, qty,box_code, warehouse_name, warehouse_code, location_code, location_name, area_name, area_code from detial_view
    </sql>
    <!--库存现有量 仓库-->
    <select id="selectDetialViewList" parameterType="DetialView" resultMap="DetialViewResult">
        <include refid="selectDetialViewVo"/>
        <where>
            <if test="orgId != null  and orgId != ''"> and org_id = #{orgId}</if>
            <if test="orgName != null  and orgName != ''"> and org_name like concat('%', #{orgName}, '%')</if>
            <if test="barcodeId != null  and barcodeId != ''"> and barcode_id = #{barcodeId}</if>
            <if test="isStocked != null  and isStocked != ''"> and is_stocked = #{isStocked}</if>
            <if test="itemCode != null  and itemCode != ''"> and item_code like concat('%', #{itemCode}, '%')</if>
            <if test="itemName != null  and itemName != ''"> and item_name like concat('%', #{itemName}, '%')</if>
            <if test="itemId != null  and itemId != ''"> and item_id = #{itemId}</if>
            <if test="sourceBill != null  and sourceBill != ''"> and source_bill = #{sourceBill}</if>
            <if test="sourceId != null  and sourceId != ''"> and source_id = #{sourceId}</if>
            <if test="sourceLineNo != null  and sourceLineNo != ''"> and source_line_no = #{sourceLineNo}</if>
            <if test="batchNumber != null  and batchNumber != ''"> and batch_number like concat('%',  #{batchNumber}, '%')</if>
            <if test="baseUnit != null  and baseUnit != ''"> and base_unit = #{baseUnit}</if>
            <if test="qty != null  and qty != ''"> and qty = #{qty}</if>
            <if test="boxCode != null  and boxCode != ''"> and box_code = #{boxCode}</if>
            <if test="warehouseName != null  and warehouseName != ''"> and warehouse_name like concat('%', #{warehouseName}, '%')</if>
            <if test="warehouseCode != null  and warehouseCode != ''"> and warehouse_code = #{warehouseCode}</if>
            <if test="locationCode != null  and locationCode != ''"> and location_code = #{locationCode}</if>
            <if test="locationName != null  and locationName != ''"> and location_name like concat('%', #{locationName}, '%')</if>
            <if test="areaName != null  and areaName != ''"> and area_name like concat('%', #{areaName}, '%')</if>
            <if test="areaCode != null  and areaCode != ''"> and area_code = #{areaCode} </if>
        </where>
    </select>
​
    <select id="DetialList" parameterType="DetialView" resultMap="DetialViewResult">
        <include refid="selectDetialView"/>
        <where>
            <if test="orgId != null  and orgId != ''"> and org_id = #{orgId}</if>
            <if test="orgName != null  and orgName != ''"> and org_name like concat('%', #{orgName}, '%')</if>
            <if test="barcodeId != null  and barcodeId != ''"> and barcode_id = #{barcodeId}</if>
            <if test="isStocked != null  and isStocked != ''"> and is_stocked = #{isStocked}</if>
            <if test="itemCode != null  and itemCode != ''"> and item_code = #{itemCode}</if>
            <if test="itemName != null  and itemName != ''"> and item_name like concat('%', #{itemName}, '%')</if>
            <if test="itemId != null  and itemId != ''"> and item_id = #{itemId}</if>
            <if test="sourceBill != null  and sourceBill != ''"> and source_bill = #{sourceBill}</if>
            <if test="sourceId != null  and sourceId != ''"> and source_id = #{sourceId}</if>
            <if test="sourceLineNo != null  and sourceLineNo != ''"> and source_line_no = #{sourceLineNo}</if>
            <if test="batchNumber != null  and batchNumber != ''"> and batch_number = #{batchNumber}</if>
            <if test="baseUnit != null  and baseUnit != ''"> and base_unit = #{baseUnit}</if>
            <if test="qty != null  and qty != ''"> and qty = #{qty}</if>
            <if test="boxCode != null  and boxCode != ''"> and box_code = #{boxCode}</if>
            <if test="warehouseName != null  and warehouseName != ''"> and warehouse_name like concat('%', #{warehouseName}, '%')</if>
            <if test="warehouseCode != null  and warehouseCode != ''"> and warehouse_code = #{warehouseCode}</if>
            <if test="locationCode != null  and locationCode != ''"> and location_code = #{locationCode}</if>
            <if test="locationName != null  and locationName != ''"> and location_name like concat('%', #{locationName}, '%')</if>
            <if test="areaName != null  and areaName != ''"> and area_name like concat('%', #{areaName}, '%')</if>
            <if test="areaCode != null  and areaCode != ''"> and area_code = #{areaCode}</if>
        </where>
    </select>
    <!--按最大的箱序号优先排序  批次号（生产订单号）=日期+3位流水-->
    <select id="selectDetialList" parameterType="DetialView" resultMap="DetialViewResult">
        select org_id,org_name, barcode_id, is_stocked, item_code, item_name, item_id, source_bill, source_id, source_line_no, batch_number, base_unit, qty,box_code, warehouse_name, warehouse_code, location_code, location_name, area_name, area_code from wm_stock_detail_view
        where area_code != 'R' and area_code != 'CH'
        group by area_code
        ORDER BY box_code desc
    </select>
​
    <!--以物料为维度的查询==台账-->
    <select id="selectDetialItemList" parameterType="DetialView" resultMap="DetialViewResult">
        select org_id,org_name, barcode_id, is_stocked, item_code, item_name, item_id, source_bill, source_id, source_line_no, batch_number, base_unit, sum(qty) as qty, warehouse_name, warehouse_code, location_code, location_name, area_name, area_code from wm_stock_detail_view
        <where>
            <if test="orgId != null  and orgId != ''"> and org_id = #{orgId}</if>
            <if test="orgName != null  and orgName != ''"> and org_name like concat('%', #{orgName}, '%')</if>
            <if test="barcodeId != null  and barcodeId != ''"> and barcode_id = #{barcodeId}</if>
            <if test="isStocked != null  and isStocked != ''"> and is_stocked = #{isStocked}</if>
            <if test="itemCode != null  and itemCode != ''"> and item_code = #{itemCode}</if>
            <if test="itemName != null  and itemName != ''"> and item_name like concat('%', #{itemName}, '%')</if>
            <if test="itemId != null  and itemId != ''"> and item_id = #{itemId}</if>
            <if test="sourceBill != null  and sourceBill != ''"> and source_bill = #{sourceBill}</if>
            <if test="sourceId != null  and sourceId != ''"> and source_id = #{sourceId}</if>
            <if test="sourceLineNo != null  and sourceLineNo != ''"> and source_line_no = #{sourceLineNo}</if>
            <if test="batchNumber != null  and batchNumber != ''"> and batch_number = #{batchNumber}</if>
            <if test="baseUnit != null  and baseUnit != ''"> and base_unit = #{baseUnit}</if>
            <if test="qty != null  and qty != ''"> and qty = #{qty}</if>
            <if test="boxCode != null  and boxCode != ''"> and box_code = #{boxCode}</if>
            <if test="warehouseName != null  and warehouseName != ''"> and warehouse_name like concat('%', #{warehouseName}, '%')</if>
            <if test="warehouseCode != null  and warehouseCode != ''"> and warehouse_code = #{warehouseCode}</if>
            <if test="locationCode != null  and locationCode != ''"> and location_code = #{locationCode}</if>
            <if test="locationName != null  and locationName != ''"> and location_name like concat('%', #{locationName}, '%')</if>
            <if test="areaName != null  and areaName != ''"> and area_name like concat('%', #{areaName}, '%')</if>
            <if test="areaCode != null  and areaCode != ''"> and area_code != R</if>
        </where>
        group by item_code,org_id
        order by item_code asc
    </select>
​
    <!--库存现有量：组织，库位，料号-->
    <select id="selectDetialNumList" parameterType="DetialView" resultMap="DetialViewResult">
        select org_id,org_name, barcode_id, is_stocked, item_code, item_name, item_id, source_bill, source_id, source_line_no, batch_number, base_unit, sum(qty) as qty, warehouse_name, warehouse_code, location_code, location_name, area_name, area_code from wm_stock_detail_view
        <where>
            <if test="orgId != null  and orgId != ''"> and org_id = #{orgId}</if>
            <if test="orgName != null  and orgName != ''"> and org_name like concat('%', #{orgName}, '%')</if>
            <if test="barcodeId != null  and barcodeId != ''"> and barcode_id = #{barcodeId}</if>
            <if test="isStocked != null  and isStocked != ''"> and is_stocked = #{isStocked}</if>
            <if test="itemCode != null  and itemCode != ''"> and item_code = #{itemCode}</if>
            <if test="itemName != null  and itemName != ''"> and item_name like concat('%', #{itemName}, '%')</if>
            <if test="itemId != null  and itemId != ''"> and item_id = #{itemId}</if>
            <if test="sourceBill != null  and sourceBill != ''"> and source_bill = #{sourceBill}</if>
            <if test="sourceId != null  and sourceId != ''"> and source_id = #{sourceId}</if>
            <if test="sourceLineNo != null  and sourceLineNo != ''"> and source_line_no = #{sourceLineNo}</if>
            <if test="batchNumber != null  and batchNumber != ''"> and batch_number = #{batchNumber}</if>
            <if test="baseUnit != null  and baseUnit != ''"> and base_unit = #{baseUnit}</if>
            <if test="qty != null  and qty != ''"> and qty = #{qty}</if>
            <if test="boxCode != null  and boxCode != ''"> and box_code = #{boxCode}</if>
            <if test="warehouseName != null  and warehouseName != ''"> and warehouse_name like concat('%', #{warehouseName}, '%')</if>
            <if test="warehouseCode != null  and warehouseCode != ''"> and warehouse_code = #{warehouseCode}</if>
            <if test="locationCode != null  and locationCode != ''"> and location_code = #{locationCode}</if>
            <if test="locationName != null  and locationName != ''"> and location_name like concat('%', #{locationName}, '%')</if>
            <if test="areaName != null  and areaName != ''"> and area_name like concat('%', #{areaName}, '%')</if>
            <if test="areaCode != null  and areaCode != ''"> and area_code = #{areaCode}</if>
        </where>
        group by org_id,item_code,batch_number,area_code
    </select>
​
    <select id="selectDetialViewByOrgId" parameterType="String" resultMap="DetialViewResult">
        <include refid="selectDetialViewVo"/>
        where org_id = #{orgId}
    </select>
​
    <insert id="insertDetialView" parameterType="DetialView">
        insert into detial_view
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="orgId != null">org_id,</if>
            <if test="orgName != null">org_name,</if>
            <if test="barcodeId != null">barcode_id,</if>
            <if test="isStocked != null">is_stocked,</if>
            <if test="itemCode != null">item_code,</if>
            <if test="itemName != null">item_name,</if>
            <if test="itemId != null">item_id,</if>
            <if test="sourceBill != null">source_bill,</if>
            <if test="sourceId != null">source_id,</if>
            <if test="sourceLineNo != null">source_line_no,</if>
            <if test="batchNumber != null">batch_number,</if>
            <if test="baseUnit != null">base_unit,</if>
            <if test="qty != null">qty,</if>
            <if test="boxCode != null">box_code,</if>
            <if test="warehouseName != null">warehouse_name,</if>
            <if test="warehouseCode != null">warehouse_code,</if>
            <if test="locationCode != null">location_code,</if>
            <if test="locationName != null">location_name,</if>
            <if test="areaName != null">area_name,</if>
            <if test="areaCode != null">area_code,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="orgId != null">#{orgId},</if>
            <if test="orgName != null">#{orgName},</if>
            <if test="barcodeId != null">#{barcodeId},</if>
            <if test="isStocked != null">#{isStocked},</if>
            <if test="itemCode != null">#{itemCode},</if>
            <if test="itemName != null">#{itemName},</if>
            <if test="itemId != null">#{itemId},</if>
            <if test="sourceBill != null">#{sourceBill},</if>
            <if test="sourceId != null">#{sourceId},</if>
            <if test="sourceLineNo != null">#{sourceLineNo},</if>
            <if test="batchNumber != null">#{batchNumber},</if>
            <if test="baseUnit != null">#{baseUnit},</if>
            <if test="qty != null">#{qty},</if>
            <if test="boxCode != null">#{boxCode},</if>
            <if test="warehouseName != null">#{warehouseName},</if>
            <if test="warehouseCode != null">#{warehouseCode},</if>
            <if test="locationCode != null">#{locationCode},</if>
            <if test="locationName != null">#{locationName},</if>
            <if test="areaName != null">#{areaName},</if>
            <if test="areaCode != null">#{areaCode},</if>
        </trim>
    </insert>
​
    <update id="updateDetialView" parameterType="DetialView">
        update detial_view
        <trim prefix="SET" suffixOverrides=",">
            <if test="orgName != null">org_name = #{orgName},</if>
            <if test="barcodeId != null">barcode_id = #{barcodeId},</if>
            <if test="isStocked != null">is_stocked = #{isStocked},</if>
            <if test="itemCode != null">item_code = #{itemCode},</if>
            <if test="itemName != null">item_name = #{itemName},</if>
            <if test="itemId != null">item_id = #{itemId},</if>
            <if test="sourceBill != null">source_bill = #{sourceBill},</if>
            <if test="sourceId != null">source_id = #{sourceId},</if>
            <if test="sourceLineNo != null">source_line_no = #{sourceLineNo},</if>
            <if test="batchNumber != null">batch_number = #{batchNumber},</if>
            <if test="baseUnit != null">base_unit = #{baseUnit},</if>
            <if test="qty != null">qty = #{qty},</if>
            <if test="boxCode != null">box_code = #{boxCode},</if>
            <if test="warehouseName != null">warehouse_name = #{warehouseName},</if>
            <if test="warehouseCode != null">warehouse_code = #{warehouseCode},</if>
            <if test="locationCode != null">location_code = #{locationCode},</if>
            <if test="locationName != null">location_name = #{locationName},</if>
            <if test="areaName != null">area_name = #{areaName},</if>
            <if test="areaCode != null">area_code = #{areaCode},</if>
        </trim>
        where org_id = #{orgId}
    </update>
​
    <delete id="deleteDetialViewByOrgId" parameterType="String">
        delete from detial_view
    </delete>
​
    <delete id="deleteDetialViewByOrgIds" parameterType="String">
        delete from detial_view where org_id in
        <foreach item="orgId" collection="array" open="(" separator="," close=")">
            #{orgId}
        </foreach>
    </delete>
</mapper>