<?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.bonus.sgzb.material.mapper.ProjUsingRecordMapper">

    <select id="getProjUsingRecordList" resultType="com.bonus.sgzb.material.domain.ProjUsingRecord">
        SELECT subquery1.agreementCode,
        subquery1.unitName,
        subquery1.proName,
        subquery1.typeName,
        subquery1.typeModelName,
        subquery1.unit,
        IFNULL(subquery1.outNum, 0) as outNum,
        IFNULL(subquery2.backNum, 0) as backNum,
        CASE
        WHEN IFNULL(subquery1.outNum, 0) - IFNULL(subquery2.backNum, 0) > 0
        THEN IFNULL(subquery1.outNum, 0) - IFNULL(subquery2.backNum, 0)
        ELSE 0
        END as usNum,

        CASE
        WHEN IFNULL(subquery1.outNum, 0) - IFNULL(subquery2.backNum, 0) > 0 THEN
        IFNULL(subquery1.rent_price, 0) *
        (IFNULL(subquery1.outNum, 0) - IFNULL(subquery2.backNum, 0))
        ELSE
        0
        END as usPrice,
        IFNULL(subquery1.outNum, 0) * IFNULL(subquery1.rent_price, 0) as totalPrice
        FROM (SELECT bai.agreement_id,
        mt.type_id,
        mt.rent_price,
        bai.agreement_code AS agreementCode,
        bui.unit_name AS unitName,
        bpl.lot_name AS proName,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        mt.unit_name AS unit,
        SUM(IFNULL(lod.out_num, 0)) AS outNum
        FROM lease_out_details lod
        LEFT JOIN lease_apply_info lai ON lai.id = lod.parent_id
        LEFT JOIN tm_task_agreement tta ON tta.task_id = lai.task_id
        LEFT JOIN bm_agreement_info bai ON bai.agreement_id = tta.agreement_id
        LEFT JOIN bm_project_lot bpl ON bpl.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        LEFT JOIN ma_type mt ON mt.type_id = lod.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        LEFT JOIN ma_machine mm ON mm.ma_id = lod.ma_id
        LEFT JOIN sys_user su ON su.user_id = lai.lease_person
        where 1=1
        <if test="keyWord != null and keyWord != ''">
            and (bai.agreement_code like concat('%',#{keyWord},'%') or
            bui.unit_name like concat('%',#{keyWord},'%') or
            bpl.lot_name like concat('%',#{keyWord},'%') or
            mt2.type_name like concat('%',#{keyWord},'%') or
            mt.type_name like concat('%',#{keyWord},'%') or
            mt.unit_name like concat('%',#{keyWord},'%')
            )
        </if>
        <if test="unitId != null">
            and bui.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and bpl.lot_id = #{proId}
        </if>
        <if test="typeName != null and typeName != ''">
            and mt2.type_name like concat('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and mt.type_name like concat('%',#{typeModelName},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            AND lod.create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
        </if>
        GROUP BY bai.agreement_id,
        mt.type_id) AS subquery1
        LEFT JOIN (SELECT bai.agreement_id,
        mt.type_id,
        mt.rent_price,
        bai.agreement_code AS agreementCode,
        bui.unit_name AS unitName,
        bpl.lot_name AS proName,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        mt.unit_name AS unit,
        SUM(IFNULL(bcd.back_num, 0)) backNum
        FROM back_check_details bcd
        LEFT JOIN back_apply_info baif ON baif.id = bcd.parent_id
        LEFT JOIN tm_task_agreement tta ON tta.task_id = baif.task_id
        LEFT JOIN bm_agreement_info bai ON bai.agreement_id = tta.agreement_id
        LEFT JOIN bm_project_lot bpl ON bpl.lot_id = bai.project_id
        LEFT JOIN bm_unit_info bui ON bui.unit_id = bai.unit_id
        LEFT JOIN ma_type mt ON mt.type_id = bcd.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        LEFT JOIN ma_machine mm ON mm.ma_id = bcd.ma_id
        LEFT JOIN sys_user su ON su.user_id = baif.back_person
        where 1=1
        <if test="keyWord != null and keyWord != ''">
            and (bai.agreement_code like concat('%',#{keyWord},'%') or
            bui.unit_name like concat('%',#{keyWord},'%') or
            bpl.lot_name like concat('%',#{keyWord},'%') or
            mt2.type_name like concat('%',#{keyWord},'%') or
            mt.type_name like concat('%',#{keyWord},'%') or
            mt.unit_name like concat('%',#{keyWord},'%')
            )
        </if>
        <if test="unitId != null">
            and bui.unit_id = #{unitId}
        </if>
        <if test="proId != null">
            and bpl.lot_id = #{proId}
        </if>
        <if test="typeName != null and typeName != ''">
            and mt2.type_name like concat('%',#{typeName},'%')
        </if>
        <if test="typeModelName != null and typeModelName != ''">
            and mt.type_name like concat('%',#{typeModelName},'%')
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != ''">
            AND bcd.create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
        </if>
        GROUP BY bai.agreement_id,
        mt.type_id) AS subquery2 ON subquery1.type_id = subquery2.type_id
        AND subquery1.agreement_id = subquery2.agreement_id
        <if test="useFlag == 1">
            WHERE IFNULL(subquery1.outNum, 0) - IFNULL(subquery2.backNum, 0) > 0
        </if>
    </select>
</mapper>