<?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="cn.cecep.talroad.mapper.BasDeviceMapper">

    <resultMap type="cn.cecep.talroad.domain.BasDevice" id="BasDeviceResult">
        <result property="deviceId"    column="device_id"    />
        <result property="factoryId"    column="factory_id"    />
        <result property="name"    column="name"    />
        <result property="industry"    column="industry"    />
        <result property="workStep"    column="work_step"    />
        <result property="type"    column="type"    />
        <result property="status"    column="status"    />
        <result property="craftParamValue"    column="craft_param_value"    />
        <result property="craftParam"    column="craft_param"    />
        <result property="dcsBrand"    column="dcs_brand"    />
        <result property="dcsModel"    column="dcs_model"    />
        <result property="tlCraft"    column="tl_craft"    />
        <result property="tlNum"    column="tl_num"    />
        <result property="txCraft"    column="tx_craft"    />
        <result property="txNum"    column="tx_num"    />
        <result property="ccCraft"    column="cc_craft"    />
        <result property="ccNum"    column="cc_num"    />
        <result property="cemsNum"    column="cems_num"    />
        <result property="shareDevId"    column="share_dev_id"    />
        <result property="shareType"    column="share_type"    />
        <result property="lon"    column="lon"    />
        <result property="lat"    column="lat"    />
        <result property="lonMap"    column="lon_map"    />
        <result property="latMap"    column="lat_map"    />
        <result property="oriDeviceId"    column="ori_device_id"    />
        <result property="oriPwd"    column="ori_pwd"    />
        <result property="craftImageArg"    column="craft_image_arg"    />
        <result property="videoDevId"    column="video_dev_id"    />
        <result property="memo"    column="memo"    />
        <result property="createTime"    column="create_time"    />
    </resultMap>


    <sql id="selectBasDeviceVo">
        select device_id, factory_id, name, industry, work_step, type, status, craft_param_value, craft_param, dcs_brand, dcs_model, tl_craft, tl_num, tx_craft, tx_num, cc_craft, cc_num, cems_num, share_dev_id, share_type, lon, lat, lon_map, lat_map, ori_device_id, ori_pwd, craft_image_arg, video_dev_id, memo, create_time from bas_device
    </sql>

    <select id="selectBasDeviceList" parameterType="cn.cecep.talroad.domain.BasDevice" resultMap="BasDeviceResult">
        <include refid="selectBasDeviceVo"/>
        <where>
            <if test="factoryId != null "> and factory_id = #{factoryId}</if>
            <if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>
            <if test="industry != null  and industry != ''"> and industry = #{industry}</if>
            <if test="workStep != null  and workStep != ''"> and work_step like concat('%', #{workStep}, '%') </if>
            <if test="type != null  and type != ''"> and type = #{type}</if>
            <if test="status != null "> and status = #{status}</if>
            <if test="craftParamValue != null  and craftParamValue != ''"> and craft_param_value = #{craftParamValue}</if>
            <if test="craftParam != null  and craftParam != ''"> and craft_param = #{craftParam}</if>
            <if test="dcsBrand != null  and dcsBrand != ''"> and dcs_brand = #{dcsBrand}</if>
            <if test="dcsModel != null  and dcsModel != ''"> and dcs_model = #{dcsModel}</if>
            <if test="tlCraft != null  and tlCraft != ''"> and tl_craft = #{tlCraft}</if>
            <if test="tlNum != null  and tlNum != ''"> and tl_num = #{tlNum}</if>
            <if test="txCraft != null  and txCraft != ''"> and tx_craft = #{txCraft}</if>
            <if test="txNum != null  and txNum != ''"> and tx_num = #{txNum}</if>
            <if test="ccCraft != null  and ccCraft != ''"> and cc_craft = #{ccCraft}</if>
            <if test="ccNum != null  and ccNum != ''"> and cc_num = #{ccNum}</if>
            <if test="cemsNum != null  and cemsNum != ''"> and cems_num = #{cemsNum}</if>
            <if test="shareDevId != null  and shareDevId != ''"> and share_dev_id = #{shareDevId}</if>
            <if test="shareType != null  and shareType != ''"> and share_type = #{shareType}</if>
            <if test="lon != null  and lon != ''"> and lon = #{lon}</if>
            <if test="lat != null  and lat != ''"> and lat = #{lat}</if>
            <if test="lonMap != null  and lonMap != ''"> and lon_map = #{lonMap}</if>
            <if test="latMap != null  and latMap != ''"> and lat_map = #{latMap}</if>
            <if test="oriDeviceId != null  and oriDeviceId != ''"> and ori_device_id = #{oriDeviceId}</if>
            <if test="oriPwd != null  and oriPwd != ''"> and ori_pwd = #{oriPwd}</if>
            <if test="craftImageArg != null  and craftImageArg != ''"> and craft_image_arg = #{craftImageArg}</if>
            <if test="videoDevId != null  and videoDevId != ''"> and video_dev_id = #{videoDevId}</if>
            <if test="memo != null  and memo != ''"> and memo = #{memo}</if>
        </where>
    </select>
    <select id="selectDeviceType" resultType="java.util.Map">
        select
            t.device_code as "deviceType",
            t.name
        from
            iot_device_type t
        where
            t.industry_code = #{industryCategory}
        AND t.status = 1
        AND t.delete_flag = 0
        ORDER by t.show_order, t.dict_id
    </select>

    <select id="selectMapPoints" resultType="cn.cecep.talroad.vo.MapPointVo">
        select
            name point_name,
            device_id point_code,
            lon_map point_lng,
            lat_map point_lat
        from
            bas_device
        where
            factory_id = #{factoryId}
    </select>
    <select id="listDCSSystemParam" resultType="cn.cecep.talroad.vo.BasDeviceDCSSystemParamVo">
        select
        bd.device_id deviceId,
        bd.name,
        bd.work_step workStep
        from
        bas_device bd
        <where>
            <if test="query.paramName != null and query.paramName != ''">
                bd.device_id in (select device_id from bas_device_param
                where param_name like concat('%',#{query.paramName},'%'))
            </if>
            <if test="query.industry != null and query.industry != ''">
                AND bd.industry = #{query.industry}
            </if>
            <if test="query.deviceId != null and query.deviceId != ''">
                AND bd.device_id = #{query.deviceId}
            </if>

            <if test="query.name != null and query.name != ''">
                AND bd.name LIKE CONCAT(#{query.name},'%')
            </if>
            <if test="query.type != null and query.type != ''">
                AND bd.type = #{query.type}
            </if>
            <if test="query.workStep != null and query.workStep != ''">
                AND bd.work_step = #{query.workStep}
            </if>
            <if test="query.factoryId != null and query.factoryId != ''">
                AND bd.factory_id = #{query.factoryId}
            </if>
        </where>
        ORDER BY bd.device_id
    </select>
    <select id="groupByWorkStepSelect" resultType="java.lang.String">
        select
        work_step workStep
        from
            bas_device
        <where>
            AND factory_id = #{factoryId}
            AND status = 1
            AND industry = #{industryCategory}
        </where>
        GROUP BY work_step
    </select>
    <select id="exportDCSSystemParam" resultType="cn.cecep.talroad.vo.BasDeviceDCSSystemParamExcelVo">
        select
            bd.device_id deviceId,
            bd.name,
            bd.work_step workStep,
            bdp.param_name paramName,
            bdp.param_unit paramUnit,
            bdp.max_value maxValue,
            bdp.min_value minValue,
            bdp.normal_value normalValue
        from
            bas_device bd
        left join bas_device_param bdp on
            bd.device_id = bdp.device_id
        <where>
            AND bd.status = 1
            AND bdp.is_status = '1'
            <if test="query.paramName != null and query.paramName != ''">
                AND bdp.param_name = #{query.paramName}
            </if>
            <if test="query.industry != null and query.industry != ''">
                AND bd.industry = #{query.industry}
            </if>
            <if test="query.type != null and query.type != ''">
                AND bd.type = #{query.type}
            </if>
            <if test="query.workStep != null and query.workStep != ''">
                AND bd.work_step = #{query.workStep}
            </if>
            <if test="query.factoryId != null and query.factoryId != ''">
                AND bd.factory_id = #{query.factoryId}
            </if>
        </where>
        ORDER BY bd.device_id,bdp,id
    </select>
    <select id="groupByParamNameSelect" resultType="java.lang.String">
        select
        bdp.param_name paramName
        from
        bas_device bd
        inner join bas_device_param bdp on
        bd.device_id = bdp.device_id
        <where>
            AND bd.factory_id = #{factoryId}
            AND bd.status = 1
            AND bd.industry = #{industryCategory}
            AND bdp.param_name  is not null
        </where>
        GROUP BY bdp.param_name
    </select>

    <update id="updateBatchCoordinate" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
            UPDATE bas_device
            <set>

                <if test="item.lonMap != null and item.lonMap != ''">lon_map=#{item.lonMap},lon=#{item.lonMap},</if>
                <if test="item.lonMap == null or item.lonMap == ''">lon_map = null,lon = null,</if>
                <if test="item.latMap != null  and item.latMap != ''">lat_map=#{item.latMap},lat=#{item.latMap},</if>
                <if test="item.latMap == null or item.latMap == ''">lat_map = null,lat = null,</if>
            </set>
            WHERE device_id=#{item.deviceId}
        </foreach>
    </update>
    <select id="selectFactoryDeviceVoByDeviceId"  resultType="cn.cecep.talroad.vo.FactoryDeviceVo">
        select
            d.factory_id as factoryId,
            p.factory_name as factoryName,
            d.device_id as deviceId,
            d."name" as deviceName,
            d.work_step as workStep,
            d."type" as deviceType,
            p.country_code as cityCode,
            p.country_name as cityName,
            p.industry_category as industry,
            pi.indus_name as  industryName
        from bas_device d
        inner join pc_factory p on d.factory_id = p.id
        left join pc_industry pi ON pi.indus_code = p.industry_category
        where d.device_id = #{deviceId} and d.is_status = '1'
    </select>
    <select id="selectFactoryDeviceVoByParam"  resultType="cn.cecep.talroad.vo.FactoryDeviceVo">
        select
            d.factory_id as factoryId,
            p.factory_name as factoryName,
            d.device_id as deviceId,
            d."name" as deviceName,
            d.work_step as workStep,
            d."type" as deviceType,
            p.country_code as cityCode,
            p.country_name as cityName,
            p.industry_category as industry,
            pi.indus_name as  industryName,
            p.industry_permission_name as industryPermissionName
        from bas_device d
                 inner join pc_factory p on d.factory_id = p.id
                 left join pc_industry pi ON pi.indus_code = p.industry_category
        <where>
            bd.industry = #{industry} and d.is_status = '1'
            AND bd."type" = #{deviceType}
            and EXISTS (
                SELECT
                1
                FROM
                bas_device_param bdp
                WHERE bd.device_id = bdp.device_id
                AND bdp.normal_value IS NOT NULL
            )
        </where>
    </select>
    <select id="selectDeviceStatusChart" resultType="java.util.Map">
        select
            update_time as time,
            operation_status as status
        from s_ra_real_time_analysis_run
        where  update_time >= to_timestamp(#{startTime}, 'yyyy-mm-dd hh24:MI:SS')
        and update_time &lt;= to_timestamp(#{endTime}, 'yyyy-mm-dd hh24:MI:SS')
        and device_id = #{deviceId}
        and operation_status IS NOT NULL
        ORDER BY update_time
    </select>
    <select id="selectCountOverview" resultType="cn.cecep.talroad.vo.BasDeviceVo$CountOverview">
        SELECT
            count(*) as "deviceCount",
            count(DISTINCT t1.factory_id) as "factoryCount",
            count(DISTINCT t1.industry) as "industryCount",
            count(DISTINCT t1.work_step) as "workStepCount",
            count(CASE WHEN t3.operation_status = '1' THEN 1 END) as "normalCount",
            count(CASE WHEN t3.operation_status = '2' THEN 1 END) as "stopCount",
            count(CASE WHEN t3.operation_status IN ('3', '4') THEN 1 END) as "startStopCount",
            count(CASE WHEN t3.operation_status IS NULL OR t3.operation_status NOT IN ('1', '2', '3', '4') THEN 1 END) as "offlineCount"
        FROM bas_device t1
        INNER JOIN pc_factory t2 ON t1.factory_id = t2.id
        LEFT JOIN s_ra_real_time_analysis_run_one t3 ON t1.device_id = t3.device_id
        WHERE t1.industry IN (SELECT indus_code FROM pc_industry)
        AND t2.country_code IN
        <foreach collection="regionCodeList" open="(" separator="," close=")" item="item">
            #{item}
        </foreach>
    </select>
    <select id="selectCoalCountOverview" resultType="cn.cecep.talroad.vo.BasDeviceVo$CoalCountOverview">
        SELECT
            count(DISTINCT CASE WHEN t1.industry = 'GT' THEN t1.factory_id END) as "gtFactoryCount",
            count(DISTINCT CASE WHEN t1.industry = 'JH' THEN t1.factory_id END) as "jhFactoryCount"
        FROM bas_device t1
        INNER JOIN pc_factory t2 ON t1.factory_id = t2.id
        WHERE t1.industry IN ('JH', 'GT')
        AND t2.country_code IN
        <foreach collection="regionCodeList" open="(" separator="," close=")" item="item">
            #{item}
        </foreach>
    </select>
    <select id="selectCoalDiffuseList" resultType="cn.cecep.talroad.vo.BasDeviceVo$CoalDiffuse">
        SELECT
            t1.factory_id,
            t4.factory_name,
            t1.device_id,
            t1.name AS device_name,
            t4.production_lon,
            t4.production_lat,
            case t3.coal_gas_status
                WHEN '正常放散' THEN 1
                WHEN '未放散' THEN 3
                WHEN '异常放散' THEN 2
                ELSE 4
            END AS status
        FROM bas_device t1
        LEFT JOIN (
            SELECT
                device_id,
                max(mon_time) mon_time
                FROM bas_device_sal_analysis
            GROUP BY device_id
        ) t2 ON t1.device_id = t2.device_id
        LEFT JOIN bas_device_sal_analysis t3 ON t2.device_id = t3.device_id AND t2.mon_time = t3.mon_time
        INNER JOIN pc_factory t4 ON t1.factory_id = t4.id
        WHERE t1.industry IN ('GT', 'JH')
        AND t1.type IN ('GLMQ', 'ZLMQ', 'MQGW', 'GLMQG', 'ZL')
        <if test="deviceName != null and deviceName != ''">
            AND t1.name like CONCAT('%', #{deviceName}, '%')
        </if>
        <if test="status != null and status != ''">
            <choose>
                <when test='status == "1"'>
                    AND t3.coal_gas_status = '正常放散'
                </when>
                <when test='status == "2"'>
                    AND t3.coal_gas_status = '异常放散'
                </when>
                <when test='status == "3"'>
                    AND t3.coal_gas_status = '未放散'
                </when>
                <otherwise>
                    AND (t3.coal_gas_status IS NULL OR t3.coal_gas_status = '')
                </otherwise>
            </choose>
        </if>
        ORDER BY t1.factory_id, t1.name, t1.device_id
    </select>
    <select id="selectCoalMapPoints" resultType="cn.cecep.talroad.vo.MapPointVo">
        SELECT
            CONCAT(t4.factory_name, '-', t1.name) as pointName,
            t1.device_id as pointCode,
            t1.lon as pointLng,
            t1.lat as pointLat,
            t1.type as pointType,
            case
                when t3.coal_gas_status IS NOT NULL AND t3.coal_gas_status != '' THEN t3.coal_gas_status
                ELSE '离线/异常'
            END as pointStatus,
            t1.factory_id
        FROM bas_device t1
        LEFT JOIN (
            SELECT
                device_id,
                max(mon_time) mon_time
            FROM bas_device_sal_analysis
            GROUP BY device_id
        ) t2 ON t1.device_id = t2.device_id
        LEFT JOIN bas_device_sal_analysis t3 ON t2.device_id = t3.device_id AND t2.mon_time = t3.mon_time
        LEFT JOIN pc_factory t4 ON t1.factory_id = t4.id
        WHERE t1.industry IN ('GT', 'JH')
          AND t1.type IN ('GLMQ', 'ZLMQ', 'MQGW', 'GLMQG', 'ZL')
          AND t1.lon IS NOT NULL
          AND t1.lat IS NOT NULL
          <if test="name != null and name != ''">
              AND t1.name like CONCAT('%', #{name}, '%')
          </if>
        ORDER BY
            case t3.coal_gas_status
                WHEN '正常放散' THEN 3
                WHEN '未放散' THEN 2
                WHEN '异常放散' THEN 4
                ELSE 1
            END
    </select>
    <select id="selectListByIndustry" resultMap="BasDeviceResult">
        select
            bd.device_id
        from bas_device bd
        left join bas_device_param_analyse bdpa on bd.industry =bdpa.industry and bd."type" = bdpa.device_cart_code
        <where>
            bd.industry = #{industry} and bd.is_status ='1' and bdpa.data_type ='1'
            <if test="dataType!=null  and dataType!='' and dataType=='1'">
                and bdpa.data_logic_deficiency_ew = '1'
            </if>
            <if test="dataType!=null  and dataType!='' and dataType=='2'">
                and bdpa.data_logic_abnormal_ew = '1'
            </if>
            <if test="dataType!=null  and dataType!='' and dataType=='3'">
                and bdpa.contour_ew = '1'
            </if>
        </where>
        group by bd.device_id
    </select>

</mapper>
