<?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, 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) 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(
                        SELECT warehouse_id, material_id, SUM(number) AS putNumber
                        FROM erp_material_inventory_record
                        WHERE type = '1'
                        GROUP BY warehouse_id, material_id
                ) put ON r.warehouse_id = put.warehouse_id AND r.material_id = put.material_id
                LEFT JOIN(
                    SELECT warehouse_id, material_id, SUM(number) AS retNumber
                    FROM erp_material_inventory_record
                    WHERE type = '2'
                    GROUP BY warehouse_id, material_id
                ) ret ON r.warehouse_id = ret.warehouse_id AND r.material_id = ret.material_id
                LEFT JOIN(
                    SELECT warehouse_id, material_id, SUM(number) AS picNumber
                    FROM erp_material_inventory_record
                    WHERE type = '3'
                    GROUP BY warehouse_id, material_id
                ) pic ON r.warehouse_id = pic.warehouse_id AND r.material_id = pic.material_id
                LEFT JOIN(
                    SELECT warehouse_id, material_id, SUM(number) AS bacNumber
                    FROM erp_material_inventory_record
                    WHERE type = '4'
                    GROUP BY warehouse_id, material_id
                ) bac ON r.warehouse_id = bac.warehouse_id AND r.material_id = bac.material_id
                GROUP BY r.warehouse_id, r.material_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">
        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 material_id, SUM(number) AS putNumber
                FROM erp_material_inventory_record
                WHERE type = '1' AND warehouse_id = #{warehouseId}
                GROUP BY material_id
            ) put ON m.id = put.material_id
                LEFT JOIN (
                SELECT material_id, SUM(number) AS retNumber
                FROM erp_material_inventory_record
                WHERE type = '2' AND warehouse_id = #{warehouseId}
                GROUP BY material_id
            ) ret ON m.id = ret.material_id
                LEFT JOIN (
                SELECT material_id, SUM(number) AS picNumber
                FROM erp_material_inventory_record
                WHERE type = '3' AND warehouse_id = #{warehouseId}
                GROUP BY material_id
            ) pic ON m.id = pic.material_id
                LEFT JOIN (
                SELECT material_id, SUM(number) AS bacNumber
                FROM erp_material_inventory_record
                WHERE type = '4' AND warehouse_id = #{warehouseId}
                GROUP BY material_id
            ) bac ON m.id = bac.material_id
        WHERE
            put.putNumber IS NOT NULL OR
            ret.retNumber IS NOT NULL OR
            pic.picNumber IS NOT NULL OR
            bac.bacNumber IS NOT NULL
    </select>


</mapper>
