<?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.njcn.csdevice.mapper.WlRecordMapper">

    <insert id="saveDataRecord">
        insert into wl_record_test_data
        select #{testRecordId},#{dataRecordId} from dual
        where not exists (select 1 from wl_record_test_data wr where wr.test_item_id = #{testRecordId} and wr.data_id = #{dataRecordId})
    </insert>

    <delete id="deleteDataRecord">
        delete from wl_record_test_data where test_item_id = #{testRecordId}
        <if test="dataRecordId!=null and dataRecordId!= ''">
            and data_id = #{dataRecordId}
        </if>
    </delete>

    <select id="getDataRecordBytestId" resultType="string">
        select data_id from wl_record_test_data where test_item_id = #{testRecordId};
    </select>

    <select id="getRecordAll" resultType="com.njcn.csdevice.pojo.vo.RecordAllDevTreeVo">
        <if test="wlRecordPageParam.isTrueFlag == 0">
            select a.dev_id as id,b.name as name,b.run_status as runStatus from  wl_record a
            left join cs_equipment_delivery b on a.dev_id = b.id
            where a.type=1 and a.state =1 and a.end_time is not null
            and
            (select count(1) from wl_record wl where wl.dev_id = a.dev_id) >
            (select count(1) from wl_record_test_data wd where wd.test_item_id = #{wlRecordPageParam.id}
            and data_id in (select w.id from wl_record w where w.dev_id =  a.dev_id))
            group by a.dev_id,b.name having a.dev_id is not null and b.name is not null
        </if>
        <if test="wlRecordPageParam.isTrueFlag == 1">
            select a.dev_id as id,b.name as name,b.run_status as runStatus from  wl_record a
            left join cs_equipment_delivery b on a.dev_id = b.id
            where a.type=1 and a.state =1 and a.end_time is not null
            and exists
            (select 1 from wl_record wl where wl.dev_id = a.dev_id and wl.type=1 and wl.state =1
            and exists (select 1 from wl_record_test_data wd where wd.test_item_id = #{wlRecordPageParam.id} and wd.data_id = wl.id))
            group by a.dev_id,b.name having a.dev_id is not null and b.name is not null
        </if>
    </select>

    <select id="getRecordAllLine" resultType="com.njcn.csdevice.pojo.vo.RecordAllDevTreeVo$RecordAllLineTreeVo">
        select concat(a.line_id,'-',#{wlRecordPageParam.devId}) as id,c.name as name, #{wlRecordPageParam.devId} as pid from  wl_record a
        left join cs_line c on a.line_id = c.line_id
        where a.type=1 and a.state =1 and a.dev_id=#{wlRecordPageParam.devId} and
        <if test="wlRecordPageParam.isTrueFlag == 0">
            not
        </if>
        exists
        (select 1 from wl_record wl where wl.id = a.id and wl.type=1 and wl.state =1
          and exists (select 1 from wl_record_test_data wd where wd.test_item_id = #{wlRecordPageParam.id} and wd.data_id = wl.id))
        group by a.line_id,c.name having a.line_id is not null and c.name is not null
    </select>

    <select id="getRecordAllTime" resultType="com.njcn.csdevice.pojo.vo.RecordAllDevTreeVo$RecordAllTimeTreeVo">
        select a.id as id,concat(a.start_time,'-',a.end_time) as name, concat(#{wlRecordPageParam.lineId},'-',#{wlRecordPageParam.devId}) as pid from  wl_record a
        left join cs_line c on a.line_id = c.line_id
        where a.type=1 and a.state =1 and a.end_time is not null and a.dev_id=#{wlRecordPageParam.devId} and a.line_id=#{wlRecordPageParam.lineId}
        and
        <if test="wlRecordPageParam.isTrueFlag == 0">
            not
        </if>
         exists
        (select 1 from wl_record_test_data wd where wd.test_item_id = #{wlRecordPageParam.id} and wd.data_id = a.id)
        order by a.start_time desc
    </select>

    <select id="getAll" resultType="com.njcn.csdevice.pojo.vo.CsLedgerVO">
        select a.dev_id as id,b.name as name ,2 as level,b.ndid nDid from  wl_record a
        left join cs_equipment_delivery b on a.dev_id = b.id
        where a.type=1 and a.state =1
        group by a.dev_id,b.name having a.dev_id is not null and b.name is not null
    </select>

    <select id="getAllLine" resultType="com.njcn.csdevice.pojo.vo.CsLedgerVO">
        select a.line_id as id,c.name as name from  wl_record a
        left join cs_line c on a.line_id = c.line_id
        where a.type=1 and a.state =1 and a.dev_id=#{devId}
        group by a.line_id,c.name having a.line_id is not null and c.name is not null
    </select>

    <select id="getDataRecordByTestId" resultType="com.njcn.csdevice.pojo.po.WlRecord">
        select * from wl_record b where b.id in (
        select data_id from wl_record_test_data where
        <if test="flag == 1">
            test_item_id in (select a.id from wl_record a where a.p_id = #{testRecordId})
        </if>
        <if test="flag == 0">
            test_item_id = #{testRecordId}
        </if>
        )
        order by start_time
    </select>

    <select id="getWlAssByWlId" resultType="com.njcn.csdevice.pojo.po.WlRecord">
        SELECT
            a.id,
            a.start_time,
            a.end_time,
            a.line_id,
            a.type,
            c.data_level AS dataLevel,
            b.ct_ratio AS ct,
            b.pt_ratio AS pt,
            NULL AS itemName,
            NULL AS voltageLevel,
            NULL AS capacitySscb,
            NULL AS capacitySscmin,
            NULL AS capacitySt,
            NULL AS capacitySi
        FROM
            wl_record a
                INNER JOIN cs_line b ON a.line_id = b.line_id
                INNER JOIN cs_data_set c ON b.data_set_id = c.id
        WHERE
            a.type = 1
          AND a.id IN (
            SELECT
                data_id
            FROM
                wl_record_test_data
            WHERE
                test_item_id = #{wlId} )
        UNION
        SELECT
            a.id,
            a.start_time,
            a.end_time,
            a.line_id,
            a.type,
            NULL AS dataLevel,
            (a.ct / a.ct1) AS ct,
            (a.pt / a.pt1) AS pt,
            a.item_name,
            a.voltage_level,
            a.capacity_sscb,
            a.capacity_sscmin,
            a.capacity_st,
            a.capacity_si
        FROM
            wl_record a
        WHERE
            a.id = #{wlId}
    </select>
</mapper>