<?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.tofly.xrtygis.custom.mapper.CustomDmaTableMapper">

    <insert id="insertBindTable">
        insert into TF_YWPN_DMATABLEANDSTAITEM_W
        values (sys_guid(), #{tableId}, #{itid}, #{type})
    </insert>

    <select id="fuzzyQuery" resultType="map">
        select id,
        sch_code,
        sch,
        sch_name,
        bore,
        phenotype,
        factory,
        min_flow,
        decode(isflow,'0','不参与','1','参与') isflow,
        decode(ispress,'0','不采集','1','采集') ispress,
        address,
        lgtd,
        lttd,
        decode(usfl,'0','禁用','1','启用') usfl
        from TF_YWPN_DMATABLE_W
        <where>
            <if test="id != null">
                id = #{id}
            </if>
            <if test="dto != null">
                SCH || SCH_NAME || BORE || PHENOTYPE || FACTORY || ADDRESS LIKE '%' || #{dto} || '%'
            </if>
        </where>
    </select>

    <select id="likeQuery" resultType="map">
        select id,
        sch_code,
        sch,
        sch_name,
        bore,
        phenotype,
        factory,
        min_flow,
        decode(isflow,'0','不参与','1','参与') isflow,
        decode(ispress,'0','不采集','1','采集') ispress,
        address,
        lgtd,
        lttd,
        decode(usfl,'0','禁用','1','启用') usfl
        from TF_YWPN_DMATABLE_W
        <where>
            <if test="dto != null">
                SCH || SCH_NAME || BORE || PHENOTYPE || FACTORY || ADDRESS LIKE '%' || #{dto} || '%'
            </if>
        </where>
        order by id
    </select>

    <select id="useOrNot" resultType="map">
        select id
        from ${tableName}
        where TABLE_ID = #{id}
    </select>

    <select id="getBindTableInfo" resultType="map">
        select a.id, sch_name, c.itcd, decode(itype, '0', '瞬时流量', '1', '累计流量', '2', '压力测值') itype
        from TF_YWPN_DMATABLEANDSTAITEM_W a,
        tf_ywpn_dmatable_w b,
        TF_YWPN_SCADASTAITEM_W c
        where a.table_id = b.id
        and a.itid = c.id
        <if test="id != null">
            and a.table_id = #{id}
        </if>
        order by sch_name,itype
    </select>

    <select id="getTableInfo" resultType="map">
        select id,
               sch_code,
               sch,
               sch_name,
               bore,
               phenotype,
               factory,
               min_flow,
               decode(isflow, '0', '不参与', '1', '参与')  isflow,
               decode(ispress, '0', '不采集', '1', '采集') ispress,
               address,
               lgtd,
               lttd,
               decode(usfl, '0', '禁用', '1', '启用')     usfl
        from TF_YWPN_DMATABLE_W
    </select>

    <select id="oldTableData" resultType="map">
        select id,
               sch,
               factory,
               PHENOTYPE,
               bore,
               (select *
                from (select total_flow
                      from tf_ywpn_dmatableflow_w_view
                      where table_id = #{id}
                        and nvl(total_flow, 0) > 0
                      order by collect_time desc) T
                where rownum = 1) OLD_START
        from TF_YWPN_DMATABLE_W
        where id = #{id}
    </select>

    <select id="historicalTraffic" resultType="map">
        select collect_time, flow_rate
        from tf_ywpn_dmatableflow_w_view
        where table_id = #{id}
          and collect_time between to_date(#{start}, 'yyyy-mm-dd hh24:mi:ss')
            and to_date(#{end}, 'yyyy-mm-dd hh24:mi:ss')
          and flow_rate is not null
        order by collect_time
    </select>

    <select id="avgFlow" resultType="double">
        select nvl(round(avg(flow_rate), 1), 0) avgflow
        from tf_ywpn_dmatableflow_w_view
        where table_id = #{id}
          and collect_time between to_date(#{start}, 'yyyy-mm-dd hh24:mi:ss')
            and to_date(#{end}, 'yyyy-mm-dd hh24:mi:ss')
          and flow_rate is not null
    </select>

<!--    <select id="getAllMainDma" resultType="com.tofly.xrtygis.custom.entity.vo.QueryVo">-->
<!--        select to_char(ID) as id,-->
<!--               DMA_CODE as label-->
<!--        from TF_YWPN_DMAREGION_W-->
<!--        where PARTITION_LEVEL = 1-->
<!--    </select>-->

<!--    <select id="getDmaChild" resultType="com.tofly.xrtygis.custom.entity.vo.QueryVo">-->
<!--        select to_char(ID) as id,-->
<!--        DMA_CODE as label-->
<!--        from TF_YWPN_DMAREGION_W-->
<!--        where-->
<!--            1=1-->
<!--        <if test="id != null and id != ''">-->
<!--            AND PID = #{id}-->
<!--        </if>-->
<!--    </select>-->

<!--    <select id="getDmatablePage" resultType="DmatableVo">-->
<!--        select a.*,-->
<!--               b.CODE,-->
<!--               b.NAME,-->
<!--               b.MANUFACTURER,-->
<!--               b.MODEL_SPECIFICATION,-->
<!--               b.ADDRESS,-->
<!--               b.LONGITUDE,-->
<!--               b.LATITUDE-->
<!--        from TF_YWPN_DMATABLE_W a-->
<!--        LEFT JOIN TF_DEVICE_ARCHIVE_MANAGE b ON b.ID = a.DEVICE_ID-->
<!--        LEFT JOIN TF_YWPN_DMAANDOBSER_W c ON a.ID = c.TABLE_ID-->
<!--        LEFT JOIN TF_DMA_DEVICE_ASSOCIATION d ON d.DEVICE_ID = b.ID-->
<!--        where-->
<!--        1=1-->
<!--        <if test="dmaTableDto.dmaId != null and dmaTableDto.dmaId != ''">-->
<!--            AND d.DMA_ID = #{dmaTableDto.dmaId}-->
<!--        </if>-->
<!--        <if test="dmaTableDto.code != null and dmaTableDto.code.trim() != ''">-->
<!--            AND b.CODE like '%'||#{dmaTableDto.code}||'%'-->
<!--        </if>-->
<!--        <if test="dmaTableDto.name != null and dmaTableDto.name.trim() != ''">-->
<!--            AND b.NAME like '%'||#{dmaTableDto.name}||'%'-->
<!--        </if>-->
<!--    </select>-->

<!--    <select id="viewFlowIndexById" resultType="TfIndexAllocation">-->
<!--        select *-->
<!--        from TF_INDEX_ALLOCATION-->
<!--        where-->
<!--        1=1-->
<!--        <if test="id != null and id != ''">-->
<!--            AND DEVICE_ID = #{id}-->
<!--        </if>-->
<!--    </select>-->

<!--    <select id="getDmaAllLevelCount" resultType="Map">-->
<!--        select PARTITION_LEVEL,COUNT( PARTITION_LEVEL ) AS levelCount-->
<!--        from TF_YWPN_DMAREGION_W-->
<!--        GROUP BY PARTITION_LEVEL-->
<!--    </select>-->

</mapper>