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

    <resultMap type="com.maintain.business.domain.vo.ErpMaterialVo" id="ErpMaterialResult">
        <result property="id" column="id"/>
        <result property="materialTypeId" column="material_type_id"/>
        <result property="materialTypeName" column="material_type_name"/>
        <result property="warehouseLocationId" column="warehouse_location_id"/>
        <result property="warehouseLocationType" column="warehouse_location_type"/>
        <result property="warehouseId" column="warehouse_id"/>
        <result property="warehouseName" column="warehouse_name"/>
        <result property="vendorId" column="vendor_id"/>
        <result property="vendorName" column="vendor_name"/>
        <result property="materialCode" column="material_code"/>
        <result property="materialName" column="material_name"/>
        <result property="materialSpecifications" column="material_specifications"/>
        <result property="materialUnit" column="material_unit"/>
        <result property="materialWarrantyPeriod" column="material_warranty_period"/>
        <result property="referencePrice" column="reference_price"/>
        <result property="sellingPrice" column="selling_price"/>
        <result property="inventory" column="inventory"/>
        <result property="isEnable" column="is_enable"/>
        <result property="isUniversal" column="is_universal"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
    </resultMap>
    <sql id="customQuerySql">
        SELECT
            t.*
        FROM(
            SELECT
                r.warehouse_id, w.name as warehouse_name, 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, material_id, vendor_id, SUM(number) AS retNumber
                       FROM erp_material_inventory_record
                       WHERE type = '2'
                       GROUP BY warehouse_id, material_id, vendor_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, material_id, vendor_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, material_id, vendor_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.material_id, r.vendor_id
            ) t
    </sql>
    <select id="customQueryPageList" resultType="com.maintain.business.domain.vo.ErpMaterialVo">
        <include refid="customQuerySql" />
        ${ew.getCustomSqlSegment}
    </select>
    <select id="customQueryPageListByWarehouseId" resultType="com.maintain.business.domain.vo.ErpMaterialVo">
        SELECT
            m.id as material_id, m.material_code, m.material_name, m.material_specifications,m.vendor_id, m.vendor_name,
            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) - IFNULL(ret.retNumber,0) - IFNULL(pic.picNumber,0) + IFNULL(bac.bacNumber,0) AS inventory
        FROM erp_material m
         LEFT JOIN(
            SELECT warehouse_id, material_id, SUM(number) AS putNumber
            FROM erp_material_inventory_record
            WHERE type = '1' AND warehouse_id = #{warehouseId}
            GROUP BY warehouse_id, material_id
        ) put ON m.id = put.material_id
        LEFT JOIN(
            SELECT warehouse_id, material_id, SUM(number) AS retNumber
            FROM erp_material_inventory_record
            WHERE type = '2' AND warehouse_id = #{warehouseId}
            GROUP BY warehouse_id, material_id
        ) ret ON m.id = ret.material_id
        LEFT JOIN(
            SELECT warehouse_id, material_id, SUM(number) AS picNumber
            FROM erp_material_inventory_record
            WHERE type = '3' AND warehouse_id = #{warehouseId}
            GROUP BY warehouse_id, material_id
        ) pic ON m.id = pic.material_id
        LEFT JOIN(
            SELECT warehouse_id, material_id, SUM(number) AS bacNumber
            FROM erp_material_inventory_record
            WHERE type = '4' AND warehouse_id = #{warehouseId}
            GROUP BY warehouse_id, material_id
        ) bac ON m.id = bac.material_id
        ${ew.getCustomSqlSegment}
    </select>
    <select id="customQueryListByWarehouseId" resultType="com.maintain.business.domain.vo.ErpMaterialVo">
        <include refid="customQuerySql" />
        where t.warehouse_id = #{warehouseId}
    </select>


</mapper>
