<?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.maintain.business.mapper.ErpMaterialInventoryRecordMapper">

    <resultMap type="com.maintain.business.domain.ErpMaterialInventoryRecord" id="ErpMaterialInventoryRecordResult">
        <result property="id" column="id"/>
        <result property="infoId" column="info_id"/>
        <result property="materialId" column="material_id"/>
        <result property="deptId" column="dept_id"/>
        <result property="warehouseId" column="warehouse_id"/>
        <result property="number" column="number"/>
        <result property="price" column="price"/>
        <result property="money" column="money"/>
        <result property="type" column="type"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <resultMap type="com.maintain.business.domain.vo.ErpMaterialInventoryRecordVo" id="ErpMaterialInventoryRecordResultVo">
        <result property="id" column="id"/>
        <result property="infoId" column="info_id"/>
        <result property="materialId" column="material_id"/>
        <result property="deptId" column="dept_id"/>
        <result property="warehouseId" column="warehouse_id"/>
        <result property="number" column="number"/>
        <result property="price" column="price"/>
        <result property="money" column="money"/>
        <result property="type" column="type"/>
    </resultMap>
    <sql id="custom">
        SELECT t.* FROM (SELECT r.warehouse_id,
                                r.type,
                                w.name                   as warehouseName,
                                r.material_id,
                                r.vendor_id,
                                v.vendor_name,
                                m.material_code,
                                m.material_name,
                                m.material_specifications,
                                m.material_unit,
                                m.material_brand,
                                m.procurement_subject,
                                m.compatible_with,
                                m.material_warranty_period,
                                m.reference_price,
                                m.selling_price,
                                m.material_url,
                                IFNULL(put.putNumber, 0) AS putNumber,
                                IFNULL(ret.retNumber, 0) AS retNumber,
                                IFNULL(pic.picNumber, 0) AS picNumber,
                                IFNULL(bac.bacNumber, 0) AS bacNumber,
                                IFNULL(put.putNumber, 0) - IFNULL(ret.retNumber, 0) - IFNULL(pic.picNumber, 0) +
                                IFNULL(bac.bacNumber, 0) AS inventory
                                  FROM erp_material_inventory_record r
                                  LEFT JOIN erp_material m ON r.material_id = m.id
                                  LEFT JOIN erp_warehouse w ON r.warehouse_id = w.id
                                  LEFT JOIN erp_material_vendor v ON r.vendor_id = v.id
                                  LEFT JOIN(SELECT warehouse_id, material_id, vendor_id, SUM(number) AS putNumber
                                            FROM erp_material_inventory_record
                                            WHERE type = '1'
                                            GROUP BY warehouse_id, material_id, vendor_id) put
                                           ON r.warehouse_id = put.warehouse_id AND r.material_id = put.material_id AND r.vendor_id = put.vendor_id
                                  LEFT JOIN(SELECT warehouse_id, vendor_id, material_id, SUM(number) AS retNumber
                                            FROM erp_material_inventory_record
                                            WHERE type = '2'
                                            GROUP BY warehouse_id, vendor_id, material_id) ret
                                           ON r.warehouse_id = ret.warehouse_id AND r.material_id = ret.material_id AND r.vendor_id = ret.vendor_id
                                  LEFT JOIN(SELECT warehouse_id, material_id, vendor_id, SUM(number) AS picNumber
                                            FROM erp_material_inventory_record
                                            WHERE type = '3'
                                            GROUP BY warehouse_id, vendor_id, material_id) pic
                                           ON r.warehouse_id = pic.warehouse_id AND r.material_id = pic.material_id AND r.vendor_id = pic.vendor_id
                                  LEFT JOIN(SELECT warehouse_id, material_id, vendor_id, SUM(number) AS bacNumber
                                            FROM erp_material_inventory_record
                                            WHERE type = '4'
                                            GROUP BY warehouse_id, vendor_id, material_id) bac
                                           ON r.warehouse_id = bac.warehouse_id AND r.material_id = bac.material_id AND r.vendor_id = bac.vendor_id
                         GROUP BY r.warehouse_id, r.vendor_id, r.material_id
                         ) t
    </sql>
    <sql id="list">
        SELECT
            t.*
        FROM
            (
                SELECT
                    r.id AS recordId,
                    r.info_id,
                    r.material_id,
                    r.warehouse_id,
                    r.vendor_id,
                    r.price,
                    r.type,
                    r.create_time,
                    IFNULL( put.putaway_number, 0 ) - IFNULL( put.issued_number, 0 ) - IFNULL( put.returned_number, 0 ) AS inventory,m.material_code,
                    m.material_name,
                    m.material_specifications,
                    m.material_unit,
                    m.material_brand,
                    m.procurement_subject,
                    m.compatible_with,
                    m.material_warranty_period,
                    m.reference_price,
                    m.selling_price,
                    m.material_url,
                    v.vendor_name,
                    w.NAME AS warehouseName
                FROM
                    erp_material_inventory_record r
                        LEFT JOIN erp_material m ON r.material_id = m.id
                        LEFT JOIN erp_warehouse w ON r.warehouse_id = w.id
                        LEFT JOIN erp_material_vendor v ON r.vendor_id = v.id
                        LEFT JOIN erp_material_putaway_info put ON r.info_id = put.id
            ) t
    </sql>
    <select id="customQueryPageInventoryList" resultType="com.maintain.business.domain.vo.ErpMaterialInventoryRecordVo">
        <include refid="list"/>
        ${ew.getCustomSqlSegment}
    </select>
    <select id="customQueryPageList" resultType="com.maintain.business.domain.vo.ErpMaterialInventoryRecordVo">
        <include refid="custom"/>
        ${ew.getCustomSqlSegment}
    </select>
    <select id="queryListByWarehouseIdAndMaterialId" resultType="com.maintain.business.domain.vo.ErpMaterialInventoryRecordVo">
        SELECT r.id, r.number, r.price, r.money, r.create_by, r.create_time,
        <if test="type != null and type == 1">
            p.putaway_code
        </if>
        <if test="type != null and type == 2">
            mr.returns_code, mr.putaway_code
        </if>
        <if test="type != null and type == 3">
            mp.out_number, mp.repair_number
        </if>
        <if test="type != null and type == 4">
            mp.out_number, mp.repair_number, mp.return_number
        </if>
        FROM erp_material_inventory_record r
        <if test="type != null and type == 1">
            LEFT JOIN erp_material_putaway_info i ON r.info_id = i.id
            LEFT JOIN erp_material_putaway p ON i.putaway_id = p.id
        </if>
        <if test="type != null and type == 2">
            LEFT JOIN erp_material_returns_info ri ON r.info_id = ri.id
            LEFT JOIN erp_material_returns mr ON ri.returns_id = mr.id
        </if>
        <if test="type != null and type == 3">
            LEFT JOIN erp_material_picking_info pi ON r.info_id = pi.id
            LEFT JOIN erp_material_picking mp ON pi.picking_id = mp.id
        </if>
        <if test="type != null and type == 4">
            LEFT JOIN erp_material_picking_info pi ON r.info_id = pi.id
            LEFT JOIN erp_material_picking mp ON pi.picking_id = mp.id
        </if>
        WHERE r.warehouse_id = #{warehouseId} AND r.vendor_id = #{vendorId} AND r.material_id = #{materialId} and r.type = #{type}
    </select>

    <select id="customQueryList" resultType="com.maintain.business.domain.vo.ErpMaterialInventoryRecordVo">
        <include refid="custom"/>
        ${ew.getCustomSqlSegment}
    </select>
    <select id="queryMaterialByWarehouseIdAndMaterialId"
            resultType="com.maintain.business.domain.vo.ErpMaterialInventoryRecordVo">
        <include refid="custom"/>
        WHERE t.warehouse_id = #{warehouseId} AND t.vendor_id = #{vendorId} AND t.material_id = #{materialId}
    </select>
    <select id="customQueryInventoryById"
            resultType="com.maintain.business.domain.vo.ErpMaterialInventoryRecordVo">
        <include refid="list"/>
        <where>
            and t.type = 1
            <if test="id != null">
                and t.recordId = #{id}
            </if>
        </where>
    </select>


</mapper>
