<?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.largeScreen.mapper.LargeScreenMapper">

    <!--领料数据-->
    <select id="getMaterialReqData" resultType="com.bonus.sgzb.largeScreen.domain.MaTypeDo">
        SELECT lod.out_num AS num,
               lod.company_id AS companyId,
               sd.dept_name AS companyName
        FROM lease_out_details lod
        LEFT JOIN ma_type mt ON lod.type_id = mt.type_id AND mt.`level` = '4'
        LEFT JOIN sys_dept sd ON mt.company_id = sd.dept_id
        WHERE lod.create_time BETWEEN CONCAT(#{nowDate}, ' 00:00:00') AND CONCAT(#{nowDate}, ' 23:59:59')
    </select>
    <!--退料数据-->
    <select id="getMaterialReturnData" resultType="com.bonus.sgzb.largeScreen.domain.MaTypeDo">
        SELECT bad.audit_num AS num,
               mt.company_id AS companyId,
               sd.dept_name AS companyName
        FROM back_apply_details bad
        LEFT JOIN ma_type mt ON bad.type_id = mt.type_id AND mt.`level` = '4'
        LEFT JOIN sys_dept sd ON mt.company_id = sd.dept_id
        WHERE bad.create_time BETWEEN CONCAT(#{nowDate}, ' 00:00:00') AND CONCAT(#{nowDate}, ' 23:59:59')
    </select>
    <!--当月领料分析-->
    <select id="getPickingAnalysisByMonth" resultType="com.bonus.sgzb.largeScreen.domain.MaTypeDo">
        SELECT lod.out_num AS num,
               mt.type_name AS typeName,
               mt.manage_type AS manageType
        FROM lease_out_details lod
        LEFT JOIN ma_type mt ON lod.type_id = mt.type_id AND mt.`level` = '4'
        LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
        WHERE lod.create_time BETWEEN CONCAT(#{startDate}, ' 00:00:00') AND CONCAT(#{endDate}, ' 23:59:59')
        <if test="maType!=null and maType == 1">
            AND sd.dept_name = #{maTypeName}
        </if>
        <if test="maType!=null and maType == 2">
            AND sd.dept_name = #{maTypeName}
        </if>
    </select>
    <!--设备分布-->
    <select id="getEquipmentDisByMap" resultType="com.bonus.sgzb.material.domain.EquipmentDis">
        select count(*) as num,province from bm_project_lot where province is not null
        <if test="province!=null and province != ''">
            AND province = #{province}
        </if>
        GROUP BY province
    </select>
    <!--新购验收入库分析-->
    <select id="getAcceptanceStorage" resultType="java.util.Map">
        /*验收*/
        <if test="type == 1">
            SELECT pcd.check_num AS num,
                   mt.type_name AS typeName
            FROM purchase_check_info pci
            LEFT JOIN purchase_check_details pcd ON pci.task_id = pcd.task_id
            LEFT JOIN ma_type mt ON pcd.type_id = mt.type_id AND mt.`level` = '4'
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            WHERE DATE_FORMAT(pci.arrival_time, '%Y-%m') = #{date}
            AND mt.del_flag = '0'
            <if test="maType!=null and maType == 1">
                AND sd.dept_name = #{maTypeName}
            </if>
            <if test="maType!=null and maType == 2">
                AND sd.dept_name = #{maTypeName}
            </if>
        </if>
        /*入库*/
        <if test="type == 2">
            SELECT iad.input_num AS num,
                   mt.type_name AS typeName
            FROM input_apply_details iad
            LEFT JOIN ma_type mt ON iad.type_id = mt.type_id AND mt.`level` = '4'
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            WHERE iad.create_time BETWEEN CONCAT(#{startDate}, ' 00:00:00') AND CONCAT(#{endDate}, ' 23:59:59') AND iad.input_type = '1'
            AND mt.del_flag = '0'
            <if test="maType!=null and maType == 1">
                AND sd.dept_name = #{maTypeName}
            </if>
            <if test="maType!=null and maType == 2">
                AND sd.dept_name = #{maTypeName}
            </if>
        </if>
    </select>
    <!--当月报废分析-->
    <select id="getScrapAnalysisByMonth" resultType="com.bonus.sgzb.largeScreen.domain.MaterialDataVo">
        /*领用*/
        <if test="type == 1">
            SELECT SUM(lod.out_num) AS num,
                   bui.unit_name AS unitName
            FROM lease_out_details lod
            LEFT JOIN ma_type mt ON lod.type_id = mt.type_id AND mt.`level` = '4'
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            LEFT JOIN lease_apply_info lai ON lod.parent_id = lai.id
            LEFT JOIN tm_task_agreement tta ON lai.task_id = tta.task_id
            LEFT JOIN bm_agreement_info bai ON tta.agreement_id = bai.agreement_id
            LEFT JOIN bm_unit_info bui ON bai.unit_id = bui.unit_id
            <where>
                <if test="maType!=null and maType == 1">
                    sd.dept_name = #{maTypeName}
                </if>
                <if test="maType!=null and maType == 2">
                    AND sd.dept_name = #{maTypeName}
                </if>
            </where>
            GROUP BY bai.unit_id
            ORDER BY num DESC
        </if>
        /*报废*/
        <if test="type == 2">
            SELECT SUM(scrap_num) AS num,
                   bui.unit_name AS unitName
            FROM scrap_apply_details sad
            LEFT JOIN ma_type mt ON sad.type_id = mt.type_id AND mt.`level` = '4'
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            LEFT JOIN tm_task_agreement tta ON sad.task_id = tta.task_id
            LEFT JOIN bm_agreement_info bai ON tta.agreement_id = bai.agreement_id
            LEFT JOIN bm_unit_info bui ON bai.unit_id = bui.unit_id
            WHERE sad.status = '1'
            <if test="maType!=null and maType == 1">
                AND sd.dept_name = #{maTypeName}
            </if>
            <if test="maType!=null and maType == 2">
                AND sd.dept_name = #{maTypeName}
            </if>
            GROUP BY bai.unit_id
            ORDER BY num DESC
        </if>
    </select>
    <!--当月退料分析-->
    <select id="getMaterialReturnByMonth" resultType="com.bonus.sgzb.largeScreen.domain.ScrapAnalysisVo">
        SELECT bad.pre_num AS planNum,
               bad.audit_num AS realityNum,
               mt.type_name AS  typeName
        FROM back_apply_details bad
        LEFT JOIN ma_type mt ON bad.type_id = mt.type_id AND mt.`level` = '4'
        LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
        WHERE bad.create_time BETWEEN CONCAT(#{startDate}, ' 00:00:00') AND CONCAT(#{endDate}, ' 23:59:59')
        <if test="maType!=null and maType == 1">
            AND sd.dept_name = #{maTypeName}
        </if>
        <if test="maType!=null and maType == 2">
            AND sd.dept_name = #{maTypeName}
        </if>
    </select>
    <!--当月维修分析-->
    <select id="getMaintenanceByMonth" resultType="com.bonus.sgzb.largeScreen.domain.ScrapAnalysisVo">
        SELECT rad.repair_num AS repairNum,
               rad.repaired_num AS repairedNum,
               rad.scrap_num AS scrapNum,
               mt.type_name AS typeName
        FROM repair_apply_details rad
        LEFT JOIN ma_type mt ON rad.type_id = mt.type_id AND mt.`level` = '4'
        LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
        WHERE rad.create_time BETWEEN CONCAT(#{startDate}, ' 00:00:00') AND CONCAT(#{endDate}, ' 23:59:59')
        <if test="maType!=null and maType == 1">
            AND sd.dept_name = #{maTypeName}
        </if>
        <if test="maType!=null and maType == 2">
            AND sd.dept_name = #{maTypeName}
        </if>
    </select>
    <!--施工机具/工器具总保有量-->
    <select id="getTotalOwnership" resultType="com.bonus.sgzb.largeScreen.domain.ScrapAnalysisVo">
        /*在库*/
        <if test="type == 1">
            SELECT mt.type_name AS typeName,
                   mt.num AS num
            FROM ma_type mt
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            WHERE mt.`level` = '4' AND mt.del_flag = '0'
            <if test="maType!=null and maType == 1">
                AND sd.dept_name = #{maTypeName}
            </if>
            <if test="maType!=null and maType == 2">
                AND sd.dept_name = #{maTypeName}
            </if>
            <if test="companyId!=null and companyId !=''">
                AND sd.dept_id = #{companyId}
            </if>
        </if>
        /*待入库 新购/修试*/
        <if test="type == 2">
            SELECT pcd.purchase_num AS num,
                   pcd.input_num AS num2,
                   mt.type_name AS typeName
            FROM purchase_check_details pcd
            LEFT JOIN ma_type mt ON pcd.type_id = mt.type_id AND mt.`level` = '4'
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            WHERE pcd.status = '1'
            <if test="maType!=null and maType == 1">
                AND sd.dept_name = #{maTypeName}
            </if>
            <if test="maType!=null and maType == 2">
                AND sd.dept_name = #{maTypeName}
            </if>
            <if test="companyId!=null and companyId !=''">
                AND sd.dept_id = #{companyId}
            </if>
        UNION ALL
            SELECT rid.repair_num AS num,
                   rid.input_num AS num2,
                   mt.type_name AS typeName
            FROM repair_input_details rid
            LEFT JOIN ma_type mt ON rid.type_id = mt.type_id AND mt.`level` = '4'
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            <where>
                <if test="maType!=null and maType == 1">
                    sd.dept_name = #{maTypeName}
                </if>
                <if test="maType!=null and maType == 2">
                    AND sd.dept_name = #{maTypeName}
                </if>
                <if test="companyId!=null and companyId !=''">
                    AND sd.dept_id = #{companyId}
                </if>
            </where>
        </if>
        /*在用*/
        <if test="type == 3">
            SELECT  lod.out_num AS num,
                   '1' AS type,
                    mt.type_name AS typeName
            FROM lease_out_details lod
            LEFT JOIN ma_type mt ON lod.type_id = mt.type_id AND mt.`level` = '4'
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            <where>
                <if test="maType!=null and maType == 1">
                    sd.dept_name = #{maTypeName}
                </if>
                <if test="maType!=null and maType == 2">
                    AND sd.dept_name = #{maTypeName}
                </if>
                <if test="companyId!=null and companyId !=''">
                    AND sd.dept_id = #{companyId}
                </if>
            </where>
        UNION ALL
            SELECT bad.audit_num AS num,
                    '2' AS type,
                    mt.type_name AS typeName
            FROM back_apply_details bad
            LEFT JOIN ma_type mt ON bad.type_id = mt.type_id AND mt.`level` = '4'
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            <where>
                <if test="maType!=null and maType == 1">
                    sd.dept_name = #{maTypeName}
                </if>
                <if test="maType!=null and maType == 2">
                    AND sd.dept_name = #{maTypeName}
                </if>
                <if test="companyId!=null and companyId !=''">
                    AND sd.dept_id = #{companyId}
                </if>
            </where>
        </if>
        /*在修*/
        <if test="type == 4">
            SELECT rad.repair_num AS repairNum,
                   rad.repaired_num AS repairedNum,
                   rad.scrap_num AS scrapNum,
                   mt.type_name AS typeName
            FROM repair_apply_details rad
            LEFT JOIN ma_type mt ON rad.type_id = mt.type_id AND mt.`level` = '4'
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            <where>
                <if test="maType!=null and maType == 1">
                    sd.dept_name = #{maTypeName}
                </if>
                <if test="maType!=null and maType == 2">
                    AND sd.dept_name = #{maTypeName}
                </if>
                <if test="companyId!=null and companyId !=''">
                    AND sd.dept_id = #{companyId}
                </if>
            </where>
        </if>
        /*报废*/
        <if test="type == 5">
            SELECT sad.scrap_num AS num,
            mt.type_name AS typeName
            FROM scrap_apply_details sad
            LEFT JOIN ma_type mt ON sad.type_id = mt.type_id AND mt.`level` = '4'
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            WHERE sad.status = '1'
            <if test="maType!=null and maType == 1">
                AND sd.dept_name = #{maTypeName}
            </if>
            <if test="maType!=null and maType == 2">
                AND sd.dept_name = #{maTypeName}
            </if>
            <if test="companyId!=null and companyId !=''">
                AND sd.dept_id = #{companyId}
            </if>
        </if>
        /*修饰待入库*/
        <if test="type == 6">
            SELECT rid.repair_num AS num,
            rid.input_num AS num2,
            mt.type_name AS typeName
            FROM repair_input_details rid
            LEFT JOIN ma_type mt ON rid.type_id = mt.type_id AND mt.`level` = '4'
            LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
            <where>
                <if test="maType!=null and maType == 1">
                    sd.dept_name = #{maTypeName}
                </if>
                <if test="maType!=null and maType == 2">
                    AND sd.dept_name = #{maTypeName}
                </if>
                <if test="companyId!=null and companyId !=''">
                    AND sd.dept_id = #{companyId}
                </if>
            </where>
        </if>
    </select>
    <!--检修预警-->
    <select id="getMaintenanceWarning" resultType="com.bonus.sgzb.largeScreen.domain.MaintenanceWarningVo">
        SELECT mm.ma_code AS maCode,
               a.typeName,
               a.typeName2 AS machineName,
               mm.next_check_time
        FROM ma_machine mm
        LEFT JOIN (
            SELECT mt.type_id,mt.type_name AS typeName,mt2.type_name AS typeName2
            FROM ma_type mt
            LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id AND mt2.`level` = '3'
            WHERE mt.`level` = '4'
        )a ON mm.type_id = a.type_id
        WHERE mm.next_check_time BETWEEN #{startDate} AND #{endDate}
    </select>
    <select id="getCompany" resultType="com.bonus.sgzb.largeScreen.domain.TotalOwnershipVo">
        SELECT
            sd2.dept_id as companyId,
            sd2.dept_name as companyName
        FROM
            sys_dept sd
                LEFT JOIN sys_dept sd2 on sd2.parent_id=sd.dept_id
        WHERE
            sd.del_flag='0'
          and sd2.del_flag='0'
          and sd.parent_id='0'
    </select>
    <select id="getMaterialReqDetailData" resultType="com.bonus.sgzb.largeScreen.domain.MaterialDetailVo">
        SELECT
        lod.create_time AS materialTime,
        mt2.type_name AS typeName,
        mt2.type_id AS typeId,
        mt.type_name AS typeModelName,
        lod.out_num AS num,
        lai.`code` AS materialCode,
        bui.unit_name AS unitName,
        bai.unit_id AS unitId,
        bpl.lot_id AS projectId,
        su.user_name AS userName,
        bpl.lot_name AS projectName,
        sd.dept_name AS companyName
        FROM
        lease_out_details lod
        LEFT JOIN ma_type mt ON lod.type_id = mt.type_id
        AND mt.`level` = '4'
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        LEFT JOIN ma_type_keeper mtk ON mt.type_id = mtk.type_id
        LEFT JOIN sys_user su ON mtk.user_id = su.user_id
        LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
        LEFT JOIN lease_apply_info lai ON lod.parent_id = lai.id
        LEFT JOIN tm_task_agreement tta ON lai.task_id = tta.task_id
        LEFT JOIN bm_agreement_info bai ON tta.agreement_id = bai.agreement_id
        LEFT JOIN bm_unit_info bui ON bai.unit_id = bui.unit_id
        LEFT JOIN bm_project_lot bpl ON bai.project_id = bpl.lot_id
        WHERE
        DATE( lod.create_time ) = CURDATE()
        <if test="maTypeName!=null and maTypeName != ''">
            AND sd.dept_name = #{maTypeName}
        </if>
        <if test="materialCode!=null and materialCode != ''">
            AND lai.`code` LIKE CONCAT('%',#{materialCode},'%')
        </if>
        <if test="unitId!=null and unitId != ''">
            AND bai.unit_id = #{unitId}
        </if>
        <if test="projectId!=null and projectId != ''">
            AND bai.project_id = #{projectId}
        </if>
        <if test="typeId!=null and typeId != ''">
            AND mt2.type_id = #{typeId}
        </if>
    </select>
    <select id="getMaterialReturnDetailData" resultType="com.bonus.sgzb.largeScreen.domain.MaterialDetailVo">
        SELECT
        bad.create_time AS materialTime,
        mt2.type_name AS typeName,
        mt2.type_id AS typeId,
        mt.type_name AS typeModelName,
        bad.audit_num AS num,
        ba.`code` AS materialCode,
        bui.unit_name AS unitName,
        bai.unit_id AS unitId,
        bpl.lot_id AS projectId,
        bpl.lot_name AS projectName,
        tt.task_status AS auditStatus,
        CASE
        tt.task_status
        WHEN '37' THEN
        '待退料审核'
        WHEN '38' THEN
        '退料审核通过'
        WHEN '39' THEN
        '退料核查中'
        WHEN '40' THEN
        '退料完成' ELSE ''
        END AS statusName,
        sd.dept_name AS companyName
        FROM
        back_apply_details bad
        LEFT JOIN ma_type mt ON bad.type_id = mt.type_id
        AND mt.`level` = '4'
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
        LEFT JOIN back_apply_info ba ON bad.parent_id = ba.id
        LEFT JOIN tm_task tt ON ba.task_id = tt.task_id
        LEFT JOIN tm_task_agreement tta ON ba.task_id = tta.task_id
        LEFT JOIN bm_agreement_info bai ON tta.agreement_id = bai.agreement_id
        LEFT JOIN bm_unit_info bui ON bai.unit_id = bui.unit_id
        LEFT JOIN bm_project_lot bpl ON bai.project_id = bpl.lot_id
        WHERE
        DATE( bad.create_time ) = CURDATE()
        <if test="maTypeName!=null and maTypeName != ''">
            AND sd.dept_name = #{maTypeName}
        </if>
        <if test="materialCode!=null and materialCode != ''">
            AND ba.`code` LIKE CONCAT('%',#{materialCode},'%')
        </if>
        <if test="unitId!=null and unitId != ''">
            AND bai.unit_id = #{unitId}
        </if>
        <if test="projectId!=null and projectId != ''">
            AND bai.project_id = #{projectId}
        </if>
        <if test="typeId!=null and typeId != ''">
            AND mt2.type_id = #{typeId}
        </if>
    </select>
    <select id="getTotalOwnerDetailsShip" resultType="com.bonus.sgzb.largeScreen.domain.TotalOwnerVo">
        SELECT
        mt2.type_name AS typeName,
        mt2.type_id AS typeId,
        mt.type_name AS typeModelName,
        mt.unit_name AS unitName,
        IFNULL( mt.num, 0 ) AS stockNum,
        IFNULL( subquery1.usNum, 0 ) AS useNum,
        IFNULL( subquery2.repairNum, 0 ) AS inRepairNum,
        IFNULL( subquery3.repairInputNum, 0 ) AS inputNum,
        IFNULL( subquery4.inputNum, 0 ) AS purchaseNum,
        IFNULL( mt.num, 0 ) + IFNULL( subquery1.usNum, 0 ) + IFNULL( subquery2.repairNum, 0 ) + IFNULL( subquery3.repairInputNum, 0 ) AS totalOwnershipNum,
        sd.dept_name AS companyName
        FROM
        ma_type mt
        LEFT JOIN (
        SELECT
        subquery1.type_id,
        subquery1.typeName,
        subquery1.typeModelName,
        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
        FROM
        (
        SELECT
        mt.type_id,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        SUM(IFNULL( lod.out_num, 0 )) AS outNum
        FROM
        lease_out_details lod
        LEFT JOIN ma_type mt ON mt.type_id = lod.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        GROUP BY
        mt.type_id
        ) AS subquery1
        LEFT JOIN (
        SELECT
        mt.type_id,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        SUM(IFNULL( bcd.back_num, 0 )) backNum
        FROM
        back_check_details bcd
        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
        GROUP BY
        mt.type_id
        ) AS subquery2 ON subquery1.type_id = subquery2.type_id
        ) AS subquery1 ON mt.type_id = subquery1.type_id
        LEFT JOIN (
        SELECT
        mt.type_id,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        SUM(
        IFNULL( rad.repair_num, 0 ) - IFNULL( rad.repaired_num, 0 ) - IFNULL( rad.scrap_num, 0 )) AS repairNum
        FROM
        repair_apply_details rad
        LEFT JOIN ma_type mt ON mt.type_id = rad.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        WHERE
        IFNULL( rad.repair_num, 0 ) - IFNULL( rad.repaired_num, 0 ) - IFNULL( rad.scrap_num, 0 ) > 0
        GROUP BY
        mt.type_id
        ) AS subquery2 ON subquery2.type_id = mt.type_id
        LEFT JOIN (
        SELECT
        mt.type_id,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        SUM(
        IFNULL( rid.repair_num, 0 ) - IFNULL( rid.input_num, 0 )) AS repairInputNum
        FROM
        repair_input_details rid
        LEFT JOIN ma_type mt ON mt.type_id = rid.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        WHERE
        IFNULL( rid.repair_num, 0 ) - IFNULL( rid.input_num, 0 ) > 0
        GROUP BY
        mt.type_id
        ) AS subquery3 ON subquery3.type_id = mt.type_id
        LEFT JOIN (
        SELECT
        mt.type_id,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        SUM(
        IFNULL( pcd.check_num, 0 ) - IFNULL( pcd.input_num, 0 )) AS inputNum
        FROM
        purchase_check_details pcd
        LEFT JOIN ma_type mt ON mt.type_id = pcd.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        WHERE
        IFNULL( pcd.check_num, 0 ) - IFNULL( pcd.input_num, 0 ) > 0
        GROUP BY
        mt.type_id
        ) AS subquery4 ON subquery4.type_id = mt.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
        WHERE
        mt.`level` = 4
        AND mt.del_flag = '0'
        <if test="maTypeName!=null and maTypeName != ''">
            AND sd.dept_name = #{maTypeName}
        </if>
        <if test="typeId!=null and typeId != ''">
            AND mt2.type_id = #{typeId}
        </if>
    </select>
    <select id="getAcceptanceDetailStorage" resultType="com.bonus.sgzb.largeScreen.domain.StorageDetailVo">
        SELECT
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        mt2.type_id AS typeId,
        IFNULL( subquery1.purchaseNum, 0 ) AS purchaseNum,
        IFNULL( subquery1.checkNum, 0 ) AS checkNum,
        IFNULL( subquery2.inputNum, 0 ) AS inputNum,
        sd.dept_name AS companyName
        FROM
        ma_type mt
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
        LEFT JOIN (
        SELECT
        mt.type_id,
        SUM(IFNULL( pcd.purchase_num, 0 )) AS purchaseNum,
        SUM(IFNULL( pcd.check_num, 0 )) AS checkNum,
        pci.arrival_time AS arrivalTime
        FROM
        purchase_check_details pcd
        LEFT JOIN ma_type mt ON mt.type_id = pcd.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        LEFT JOIN purchase_check_info pci ON pci.task_id = pcd.task_id
        where
        1 = 1
        <if test="type == 1 and startDate != null ">
            AND DATE_FORMAT(pci.arrival_time, '%Y-%m') = #{startDate}
        </if>
        GROUP BY
        mt.type_id
        ) AS subquery1 ON subquery1.type_id = mt.type_id
        LEFT JOIN (
        SELECT
        mt.type_id,
        SUM(IFNULL( iad.input_num, 0 )) AS inputNum,
        iad.create_time AS inputTime
        FROM
        input_apply_details iad
        LEFT JOIN ma_type mt ON mt.type_id = iad.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        where
        iad.input_type = '1'
        <if test="type == 2 and startDate != null ">
            AND DATE_FORMAT(iad.create_time, '%Y-%m') = #{startDate}
        </if>
        GROUP BY
        mt.type_id
        ) AS subquery2 ON subquery2.type_id = mt.type_id
        WHERE
        mt.`level` = 4
        AND mt.del_flag = '0'
        <if test="maTypeName!=null and maTypeName != ''">
            AND sd.dept_name = #{maTypeName}
        </if>
        <if test="typeId!=null and typeId != ''">
            AND mt2.type_id = #{typeId}
        </if>
    </select>
    <select id="getScrapAnalysisDetailByMonth" resultType="com.bonus.sgzb.largeScreen.domain.ScrapVo">
        SELECT
            tt.`code` AS scrapCode,
            sad.scrap_source AS scrapSource,
            CASE
                sad.scrap_source
                WHEN '1' THEN
                    '退料报废'
                WHEN '2' THEN
                    '维修报废'
                WHEN '3' THEN
                    '盘点报废' ELSE ''
                END AS scrapSourceName,
            sad.create_time AS scrapTime,
            mt2.type_name AS typeName,
            mt2.type_id AS typeId,
            mt.type_name AS typeModelName,
            scrap_num AS scrapNum,
            sad.`status` AS scrapStatus,
            CASE
                sad.`status`
                WHEN '0' THEN
                    '进行中'
                WHEN '1' THEN
                    '已审核'
                WHEN '2' THEN
                    '驳回'
                WHEN '3' THEN
                    '待处置'
                WHEN '4' THEN
                    '已处置' ELSE ''
                END AS statusName,
        sd.dept_name AS companyName
        FROM
            scrap_apply_details sad
                LEFT JOIN ma_type mt ON sad.type_id = mt.type_id
                AND mt.`level` = '4'
                LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
                LEFT JOIN tm_task tt ON tt.task_id = sad.task_id
        WHERE
            sad.create_time BETWEEN CONCAT(#{startDate}, ' 00:00:00' ) AND CONCAT(#{endDate}, ' 23:59:59' )
        <if test="maTypeName != null and maTypeName != ''">
            AND sd.dept_name = #{maTypeName}
        </if>
        <if test="scrapCode!=null and scrapCode != ''">
            AND tt.`code` LIKE CONCAT('%',#{scrapCode},'%')
        </if>
        <if test="scrapSource!=null and scrapSource != ''">
            AND sad.scrap_source = #{scrapSource}
        </if>
        <if test="scrapStatus!=null and scrapStatus != ''">
            AND sad.`status` = #{scrapStatus}
        </if>
        <if test="typeId!=null and typeId != ''">
            AND mt2.type_id = #{typeId}
        </if>
    </select>
    <select id="getPickingAnalysisDetailByMonth"
            resultType="com.bonus.sgzb.largeScreen.domain.MaterialDetailVo">
        SELECT
        lai.`code` AS materialCode,
        bai.unit_id AS unitId,
        bui.unit_name AS unitName,
        bpl.lot_id AS projectId,
        bpl.lot_name AS projectName,
        lod.out_num AS num,
        lod.create_time AS materialTime,
        tt.task_status AS auditStatus,
        CASE
        tt.task_status
        WHEN '30' THEN
        '待分公司审核'
        WHEN '31' THEN
        '待机具分公司审核'
        WHEN '32' THEN
        '待施工部审核'
        WHEN '33' THEN
        '待出库'
        WHEN '34' THEN
        '出库进行中'
        WHEN '35' THEN
        '完成' ELSE ''
        END AS statusName,
        sd.dept_name AS companyName,
        mt.manage_type AS manageType
        FROM
        lease_out_details lod
        LEFT JOIN ma_type mt ON lod.type_id = mt.type_id
        AND mt.`level` = '4'
        LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
        LEFT JOIN lease_apply_info lai ON lod.parent_id = lai.id
        LEFT JOIN tm_task_agreement tta ON lai.task_id = tta.task_id
        LEFT JOIN tm_task tt ON lai.task_id = tt.task_id
        LEFT JOIN bm_agreement_info bai ON tta.agreement_id = bai.agreement_id
        LEFT JOIN bm_unit_info bui ON bai.unit_id = bui.unit_id
        LEFT JOIN bm_project_lot bpl ON bai.project_id = bpl.lot_id
        WHERE
              bpl.del_flag = '0' and
        lod.create_time BETWEEN CONCAT(#{startDate}, ' 00:00:00') AND CONCAT(#{endDate}, ' 23:59:59')
        <if test="maTypeName != null and maTypeName != ''">
            AND sd.dept_name = #{maTypeName}
        </if>
        <if test="materialCode != null and materialCode != ''">
            AND lai.`code` LIKE CONCAT('%',#{materialCode},'%')
        </if>
        <if test="unitId != null and unitId != ''">
            AND bai.unit_id = #{unitId}
        </if>
        <if test="projectId != null and projectId != ''">
            AND bai.project_id = #{projectId}
        </if>
        GROUP BY
        materialCode
    </select>
    <select id="getMaterialReturnDetailByMonth"
            resultType="com.bonus.sgzb.largeScreen.domain.MaterialDetailVo">
        SELECT
            ba.`code` AS materialCode,
            bai.unit_id AS unitId,
            bui.unit_name AS unitName,
            bpl.lot_id AS projectId,
            bpl.lot_name AS projectName,
            bad.pre_num AS num,
            bad.audit_num AS realityNum,
            bad.create_time AS materialTime,
            tt.task_status AS auditStatus,
            CASE
                tt.task_status
                WHEN '37' THEN
                    '待退料审核'
                WHEN '38' THEN
                    '退料审核通过'
                WHEN '39' THEN
                    '退料核查中'
                WHEN '40' THEN
                    '退料完成' ELSE ''
                END AS statusName,
            sd.dept_name AS companyName

        FROM
            back_apply_details bad
                LEFT JOIN ma_type mt ON bad.type_id = mt.type_id
                AND mt.`level` = '4'
                LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
                LEFT JOIN back_apply_info ba ON bad.parent_id = ba.id
                LEFT JOIN tm_task tt ON ba.task_id = tt.task_id
                LEFT JOIN tm_task_agreement tta ON ba.task_id = tta.task_id
                LEFT JOIN bm_agreement_info bai ON tta.agreement_id = bai.agreement_id
                LEFT JOIN bm_unit_info bui ON bai.unit_id = bui.unit_id
                LEFT JOIN bm_project_lot bpl ON bai.project_id = bpl.lot_id
        WHERE
        bad.create_time BETWEEN CONCAT(#{startDate}, ' 00:00:00') AND CONCAT(#{endDate}, ' 23:59:59')
        <if test="maTypeName != null and maTypeName != ''">
            AND sd.dept_name = #{maTypeName}
        </if>
        <if test="materialCode != null and materialCode != ''">
            AND lai.`code` LIKE CONCAT('%',#{materialCode},'%')
        </if>
        <if test="unitId != null and unitId != ''">
            AND bai.unit_id = #{unitId}
        </if>
        <if test="projectId != null and projectId != ''">
            AND bai.project_id = #{projectId}
        </if>
        GROUP BY
            materialCode
    </select>

    <select id="getRepairList" resultType="com.bonus.sgzb.largeScreen.domain.AccessVo">
        SELECT
        CASE
        rar.repair_type
        WHEN 1 THEN
        mpt2.pa_name ELSE rar.part_name
        END partName,
        CASE
        rar.repair_type
        WHEN 1 THEN
        SUM( rpd.part_num ) ELSE SUM( rar.part_num )
        END partNum,
        sd.dept_name AS companyName
        FROM
        repair_apply_record rar
        LEFT JOIN ma_type mt ON rar.type_id = mt.type_id
        LEFT JOIN sys_dept sd ON sd.dept_id = mt.company_id
        LEFT JOIN repair_part_details rpd ON rpd.task_id = rar.task_id
        AND rpd.type_id = rar.type_id
        AND rar.repair_type = '1'
        LEFT JOIN ma_part_type mpt ON mpt.pa_id = rpd.part_id
        LEFT JOIN ma_part_type mpt2 ON mpt2.pa_id = mpt.parent_id
        WHERE
            rar.create_time BETWEEN CONCAT(#{startDate}, ' 00:00:00') AND CONCAT(#{endDate}, ' 23:59:59')
            <if test="maTypeName != null and maTypeName != ''">
                AND sd.dept_name = #{maTypeName}
            </if>
        GROUP BY
            partName
        ORDER BY
            partNum DESC
    </select>
    <select id="getMaintenanceDetailByMonth" resultType="com.bonus.sgzb.largeScreen.domain.RepairDetailVo">
        SELECT
        tt.CODE AS repairCode,
        bai2.unit_id AS unitId,
        bui.unit_name AS unitName,
        bpi.lot_id AS projectId,
        bpi.lot_name AS projectName,
        su.user_name AS createBy,
        tt.create_time AS createTime,
        bai.CODE AS materialCode,
        sd.NAME AS statusName,
        tt.task_status AS repairStatus,
        tt.company_id AS companyId,
        sdd.dept_name AS companyName,
        GROUP_CONCAT( DISTINCT mt2.type_name ) AS typeName
        FROM
        repair_apply_details rd
        LEFT JOIN ma_type mt ON rd.type_id = mt.type_id
        LEFT JOIN sys_dept sdd ON sdd.dept_id = mt.company_id
        LEFT JOIN ma_type mt2 ON mt.parent_id = mt2.type_id
        LEFT JOIN tm_task tt ON rd.task_id = tt.task_id
        LEFT JOIN back_apply_info bai ON rd.back_id = bai.id
        LEFT JOIN tm_task_agreement tta ON bai.task_id = tta.task_id
        LEFT JOIN bm_agreement_info bai2 ON tta.agreement_id = bai2.agreement_id
        LEFT JOIN bm_unit_info bui ON bai2.unit_id = bui.unit_id
        LEFT JOIN bm_project_lot bpi ON bai2.project_id = bpi.lot_id
        AND bpi.STATUS = '0'
        AND bpi.del_flag = '0'
        LEFT JOIN sys_user su ON rd.create_by = su.user_id
        LEFT JOIN sys_dic sd ON sd.id = tt.task_status
        WHERE
        tt.create_time BETWEEN CONCAT(#{startDate}, ' 00:00:00') AND CONCAT(#{endDate}, ' 23:59:59')
        <if test="maTypeName != null and maTypeName != ''">
            AND sdd.dept_name = #{maTypeName}
        </if>
        <if test="repairCode != null and repairCode != ''">
            AND tt.CODE LIKE CONCAT('%',#{repairCode},'%')
        </if>
        <if test="unitId != null and unitId != ''">
            AND bai2.unit_id = #{unitId}
        </if>
        <if test="projectId != null and projectId != ''">
            AND bpi.lot_id = #{projectId}
        </if>
        GROUP BY
        bui.unit_name,
        bpi.lot_name,
        bai.CODE,
        su.user_name
        ORDER BY
        tt.create_time DESC
    </select>
    <select id="getEquipmentDis" resultType="com.bonus.sgzb.largeScreen.domain.EquipmentDisVo">
        select longitude,
               lot_id as lotId,
               latitude,
               lot_name as lotName,
               address,
               province
        from bm_project_lot where province = #{province}
    </select>
    <select id="getIotMaCodeMachine" resultType="com.bonus.sgzb.largeScreen.domain.IotMaCodeMachine">
        SELECT
            im.iot_code as iotCode,
            mt.type_name as typeModelName,
            mt1.type_name as typeName,
            bpl.lot_id as projectId,
            sai.agreement_id as agreementId,
            bpl.lot_name as projectName,
            sd.name as maStatus,
            mm.type_id,
            mm.ma_code,
            mm.ma_id
        FROM
            iot_machine im
                LEFT JOIN iot_machine_bind imb ON imb.iot_id = im.id
                LEFT JOIN ma_machine mm ON imb.ma_code = mm.ma_code
                left join ma_type mt on mm.type_id = mt.type_id
                left join ma_type mt1 on mt1.type_id = mt.parent_id
                LEFT JOIN sys_dic sd on mm.ma_status = sd.id
                LEFT JOIN slt_agreement_info sai ON mm.type_id = sai.type_id
                AND mm.ma_id = sai.ma_id
                AND mm.ma_id is not null
                LEFT JOIN bm_agreement_info bai ON sai.agreement_id = bai.agreement_id
                LEFT JOIN bm_project_lot bpl ON bai.project_id = bpl.lot_id
        WHERE
            im.bind_status = 0
          AND imb.unbind_time IS NULL and sai.`status` = 0 and bpl.province = #{province}
    </select>

</mapper>