<?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.project.zcustom.mapper.logistics.PlatAssetMapper">
    <resultMap type="com.project.zcustom.domain.addional.LargeLogistics" id="LargePlatLogisticsResult">
        <result property="id"    column="id"    />
        <result property="orgId"    column="app_code"    />
        <result property="appName"    column="app_name"    />
        <result property="parentOrgId"    column="app_parent_code"    />
        <result property="codedNum"    column="coded_num"    />
        <result property="labelNum"    column="label_num"    />
        <result property="assetNum"    column="asset_num"    />
        <result property="officeAssetNum"    column="office_asset_num"    />
        <result property="stockOfficeAssetNum"    column="stock_office_asset_num"    />
        <result property="newOfficeAssetNum"    column="new_office_asset_num"    />
        <result property="scrapOfficeAssetNum"    column="scrap_office_asset_num"    />
        <result property="buildingAssetNum"    column="building_asset_num"    />
        <result property="stockBuildingAssetNum"    column="stock_building_asset_num"    />
        <result property="newBuildingAssetNum"    column="new_building_asset_num"    />
        <result property="scrapBuildingAssetNum"    column="scrap_building_asset_num"    />
        <result property="serviceAssetNum"    column="service_asset_num"    />
        <result property="stockServiceAssetNum"    column="stock_service_asset_num"    />
        <result property="newServiceAssetNum"    column="new_service_asset_num"    />
        <result property="scrapServiceAssetNum"    column="scrap_service_asset_num"    />
        <result property="sceneRate"    column="scene_rate"    />
        <result property="inspectionSchemeNum"    column="inspection_scheme_num"    />
        <result property="importantDeviceTypeInspectionExecuteRate"    column="important_device_type_inspection_execute_rate"    />
        <result property="inspectionAnomalyRecordNum"    column="inspection_anomaly_record_num"    />
        <result property="repairRecordNum"    column="repair_record_num"    />
        <result property="maintenanceRecordNum"    column="maintenance_record_num"    />
    </resultMap>

    <resultMap type="com.project.zcustom.domain.addional.LargeAssetDirs" id="LargePlatAssetDirsResult">
        <result property="id"    column="id"    />
        <result property="appCode"    column="app_code"    />
        <result property="categoryName"    column="category_name"    />
        <result property="assetNum"    column="asset_num"    />
    </resultMap>

    <insert id="insertLargePlatLogistics" parameterType="com.project.zcustom.domain.addional.LargeLogistics">
        insert into large_plat_logistics (app_code,
                                          app_parent_code,
                                          coded_num,
                                          label_num,
                                          asset_num,
                                          office_asset_num,
                                          stock_office_asset_num,
                                            new_office_asset_num,
                                            scrap_office_asset_num,
                                            building_asset_num,
                                            stock_building_asset_num,
                                            new_building_asset_num,
                                            scrap_building_asset_num,
                                            service_asset_num,
                                            stock_service_asset_num,
                                            new_service_asset_num,
                                            scrap_service_asset_num,
                                            scene_rate,
                                            inspection_scheme_num,
                                            important_device_type_inspection_execute_rate,
                                            inspection_anomaly_record_num,
                                            repair_record_num,
                                            maintenance_record_num                            )
        values
        <foreach collection="list" item="item" separator="," close=";">
            <trim prefix="(" suffix=")" suffixOverrides=",">
            #{item.orgId},
            #{item.parentOrgId},
            #{item.codedNum},
            #{item.labelNum},
            #{item.assetNum},
            #{item.officeAssetNum},
            #{item.stockOfficeAssetNum},
            #{item.newOfficeAssetNum},
            #{item.scrapOfficeAssetNum},
            #{item.buildingAssetNum},
            #{item.stockBuildingAssetNum},
            #{item.newBuildingAssetNum},
            #{item.scrapBuildingAssetNum},
            #{item.serviceAssetNum},
            #{item.stockServiceAssetNum},
            #{item.newServiceAssetNum},
            #{item.scrapServiceAssetNum},
            #{item.sceneRate},
            #{item.inspectionSchemeNum},
            #{item.importantDeviceTypeInspectionExecuteRate},
            #{item.inspectionAnomalyRecordNum},
            #{item.repairRecordNum},
            #{item.maintenanceRecordNum},
            </trim>
        </foreach>
    </insert>


    <insert id="insertOfficeAssetDirs" parameterType="com.project.zcustom.domain.addional.LargeAssetDirs">
        insert into large_plat_logistics_office_asset (app_code,category_name,asset_num)
        values
        <foreach collection="list" item="item" separator="," close=";">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                #{item.appCode},
                #{item.categoryName},
                #{item.assetNum}
            </trim>
        </foreach>
    </insert>

    <insert id="insertBuildingAssetDirs" parameterType="com.project.zcustom.domain.addional.LargeAssetDirs">
        insert into large_plat_logistics_building_asset (app_code,category_name,asset_num)
        values
        <foreach collection="list" item="item" separator="," close=";">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                #{item.appCode},
                #{item.categoryName},
                #{item.assetNum}
            </trim>
        </foreach>
    </insert>

    <insert id="insertServiceAssetDirs" parameterType="com.project.zcustom.domain.addional.LargeAssetDirs">
        insert into large_plat_logistics_service_asset (app_code,category_name,asset_num)
        values
        <foreach collection="list" item="item" separator="," close=";">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                #{item.appCode},
                #{item.categoryName},
                #{item.assetNum}
            </trim>
        </foreach>
    </insert>

    <delete id="deleteLogistics">
        DELETE FROM `large_plat_logistics`
    </delete>

    <delete id="deleteOfficeAssetDirs">
        DELETE FROM `large_plat_logistics_office_asset`
    </delete>

    <delete id="deleteBuildingAssetDirs">
        DELETE FROM `large_plat_logistics_building_asset`
    </delete>

    <delete id="deleteServiceAssetDirs">
        DELETE FROM `large_plat_logistics_service_asset`
    </delete>


    <select id="getBuildingAssetRate" parameterType="String" resultMap="LargePlatAssetDirsResult">
        SELECT
        a.category_name,
        SUM( a.asset_num ) AS asset_num
        FROM
        large_plat_logistics_building_asset a
        LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
        GROUP BY
        a.category_name
    </select>

    <select id="getBuildingAssetNum" parameterType="String" resultType="Long">
        SELECT
        SUM( building_asset_num ) AS building_asset_num
        FROM
            large_plat_logistics a
                LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
    </select>

    <select id="getServiceAssetRate" parameterType="String" resultMap="LargePlatAssetDirsResult">
        SELECT
        a.category_name,
        SUM( a.asset_num ) AS asset_num
        FROM
        large_plat_logistics_service_asset a
        LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
        GROUP BY
        a.category_name
    </select>

    <select id="getServiceAssetNum" parameterType="String" resultType="Long">
        SELECT
        SUM( service_asset_num ) AS service_asset_num
        FROM
            large_plat_logistics a
                LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
    </select>

    <select id="getOfficeAssetRate" parameterType="String" resultMap="LargePlatAssetDirsResult">
        SELECT
        a.category_name,
        SUM( a.asset_num ) AS asset_num
        FROM
        large_plat_logistics_office_asset a
        LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
        GROUP BY
        a.category_name
    </select>

    <select id="getOfficeAssetNum" parameterType="String" resultType="Long">
        SELECT
            SUM( office_asset_num ) AS office_asset_num
        FROM
            large_plat_logistics a
                LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
    </select>

    <select id="getStockAssetNum" parameterType="String" resultMap="LargePlatLogisticsResult">
        SELECT
            SUM( stock_building_asset_num ) AS stock_building_asset_num,
            SUM( stock_service_asset_num ) AS stock_service_asset_num,
            SUM( stock_office_asset_num ) AS stock_office_asset_num
        FROM
            large_plat_logistics a
                LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
    </select>

    <select id="getNewAssetNum" parameterType="String" resultMap="LargePlatLogisticsResult">
        SELECT
        SUM( new_building_asset_num ) AS new_building_asset_num,
        SUM( new_service_asset_num ) AS new_service_asset_num,
        SUM( new_office_asset_num ) AS new_office_asset_num
        FROM
            large_plat_logistics a
                LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
    </select>

    <select id="getScrapAssetNum" parameterType="String" resultMap="LargePlatLogisticsResult">
        SELECT
        SUM( scrap_building_asset_num ) AS scrap_building_asset_num,
        SUM( scrap_service_asset_num ) AS scrap_service_asset_num,
        SUM( scrap_office_asset_num ) AS scrap_office_asset_num
        FROM
            large_plat_logistics a
                LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
    </select>

    <select id="getCodedAndLabelRate" parameterType="String" resultType="Map">
        SELECT
                TRIM(
                        TRAILING '.'
                        FROM
                        ROUND( SUM( a.coded_num ) / SUM( a.asset_num ), 3 )) * 100 AS codedRate,
                TRIM(
                        TRAILING '.'
                        FROM
                        ROUND( SUM( a.label_num ) / SUM( a.asset_num ), 3 )) * 100 AS labelRate
        FROM
            large_plat_logistics a
                LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
    </select>

    <select id="getMainlyRate" parameterType="String" resultMap="LargePlatLogisticsResult">
        SELECT
        SUM( building_asset_num ) AS building_asset_num,
        SUM( service_asset_num ) AS service_asset_num,
        SUM( office_asset_num ) AS office_asset_num
        FROM
            large_plat_logistics a
                LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
    </select>

    <select id="getSceneRateX" resultMap="LargePlatLogisticsResult">
        SELECT
            app_name,
            scene_rate
        FROM
            large_plat_logistics a
                RIGHT JOIN large_plat_app_org b ON a.app_code = b.app_code
        WHERE
            b.app_parent_org = '10001'
          AND b.app_org NOT IN ('10001','1A001','1A002','1A004','1A005')
        order by b.sort
    </select>

    <select id="getSceneRateY" resultMap="LargePlatLogisticsResult">
        SELECT
            app_name,
            scene_rate
        FROM
            large_plat_logistics a
                RIGHT JOIN large_plat_app_org b ON a.app_code = b.app_code
        WHERE
            b.app_parent_org = '10001' order by b.sort
    </select>

    <select id="getOperation" resultMap="LargePlatLogisticsResult">
        SELECT
            SUM( inspection_anomaly_record_num ) AS inspection_anomaly_record_num,
            SUM( repair_record_num ) AS repair_record_num,
            SUM( maintenance_record_num ) AS maintenance_record_num,
            ROUND(AVG(important_device_type_inspection_execute_rate), 2) AS important_device_type_inspection_execute_rate
        FROM
            large_plat_logistics a
                LEFT JOIN large_plat_app_org b ON a.app_code = b.app_code
        <if test="appOrg != '0000'">WHERE b.app_org = #{appOrg}</if>
    </select>

    <select id="getInspectionSchemeNumX" resultMap="LargePlatLogisticsResult">
        SELECT
            app_name,
            inspection_scheme_num
        FROM
            large_plat_logistics a
                RIGHT JOIN large_plat_app_org b ON a.app_code = b.app_code
        WHERE
             b.app_parent_org = '10001'
            AND b.app_org NOT IN ('10001','1A001','1A002','1A004','1A005')
        order by b.sort
    </select>

    <select id="getInspectionSchemeNumY" resultMap="LargePlatLogisticsResult">
        SELECT
            app_name,
            inspection_scheme_num
        FROM
            large_plat_logistics a
                RIGHT JOIN large_plat_app_org b ON a.app_code = b.app_code
        WHERE
            b.app_parent_org = '10001' order by b.sort
    </select>
</mapper>
