<?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.BigScreenMapper">


    <select id="getHourWater" resultType="map">
        select gsl,qsl from (
        select sum(flow) gsl,
        (select flow from "TF_YWPN_DMATABLEHOUR_W" d2 WHERE d2.TABLE_ID = #{qsId} and d1.UHOUR = d2.UHOUR) qsl
        FROM "TF_YWPN_DMATABLEHOUR_W" d1
        WHERE d1.TABLE_ID in
        <foreach collection="list" item="qsIds" open="(" separator="," close=")">
            #{qsIds}
        </foreach>
        and to_char(d1.uhour, 'yyyy-mm-dd hh24:mi:ss') =
        concat(to_char(sysdate, 'yyyy-mm-dd ') || (to_char(sysdate, 'hh24') - 1), ':00:00')
        group by d1.uhour
        order by d1.uhour desc
        ) where rownum = 1

    </select>

    <select id="waterVolume" resultType="map">
        select
        <choose>
            <when test="code == 0">
                sum(GSL_TOTAL) GSL_TOTAL,
            </when>
            <when test="code == 1">
                sum(SSL_TOTAL) SSL_TOTAL,
            </when>
        </choose>
        to_char(UP_DATE,'yyyy') up_date
        from TF_YWPN_COSTRLWATER_W
        where MARK = 0
        <if test="start != null and end != null">
            and to_char(UP_DATE,'yyyy') between #{start} and #{end}
        </if>
        <if test="start == null and end == null">
            and to_char(up_date,'yyyy') between to_char(add_months(sysdate,-36),'yyyy') and
            to_char(sysdate,'yyyy')
        </if>
        group by to_char(UP_DATE,'yyyy')
    </select>

    <select id="hourGslHistory" resultType="map">
        select sum(flow) gsl,
        uhour
        FROM TF_YWPN_DMATABLEHOUR_W
        WHERE TABLE_ID in
        <foreach collection="list" item="qsIds" open="(" separator="," close=")">
            #{qsIds}
        </foreach>
        and to_char(uhour, 'yyyy-mm-dd hh24:mi:ss') between #{start} and #{end}
        group by uhour
        order by uhour desc
    </select>

    <select id="hourQslHistory" resultType="map">
        select flow qsl,
               uhour
        from TF_YWPN_DMATABLEHOUR_W
        WHERE TABLE_ID = #{qsId}
          and to_char(uhour, 'yyyy-mm-dd hh24:mi:ss') between #{start} and #{end}
        order by uhour desc
    </select>

    <select id="getwaterVolume" resultType="map">
        select *
        from (select QSL, GSL
              from TF_YWPN_WATERTREND_W
              where to_char(UTIME, 'yyyy-mm-dd') = to_char(sysdate - 1, 'yyyy-mm-dd')
                and SCID = #{scid}
              order by utime desc)
        where rownum = 1
    </select>

    <select id="getStationHistory" resultType="map">
        select DWHD,
        UP_DATE,
        (select STNM from TF_YWPN_SCADASTATION_B where ID = PRE_TYPE) prename
        from TF_YWPN_COSTPRESTATION_W s
        where mark = '0'
        <if test="start != null and end != null">
            and to_char(UP_DATE,'yyyy-mm-dd hh24:mi:ss') between #{start} and #{end}
        </if>
        <if test="start == null and end == null">
            and up_date between add_months(sysdate,-3) and sysdate
        </if>

        order by prename, up_date
    </select>

    <select id="getWaterValHistory" resultType="map">
        select UDAY,${param}
        from TF_YWPN_WATERTREND_W
        where SCID = #{scid}
        <if test="start != null and end != null">
            and to_char(uday,'yyyy-mm-dd hh24:mi:ss') between #{start} and #{end}
        </if>
        <if test="start == null and end == null">
            and uday between add_months(sysdate,-3) and sysdate
        </if>
        order by uday
    </select>

    <select id="getConsumeHistory" resultType="map">
        select UP_DATE,${param}
        from TF_YWPN_COSTRLWATER_W
        where MARK = 0
        <if test="start != null and end != null ">
            and to_char(UP_DATE,'yyyy-mm-dd hh24:mi:ss') between #{start} and #{end}
        </if>
        <if test="start == null and end == null">
            and UP_DATE between add_months(sysdate,-3) and sysdate
        </if>
        order by UP_DATE
    </select>

    <select id="getIndexHistory" resultType="map">
        select ITVAL, SCADA_TIME
        from TF_YWPN_SCADAHISTORY_R_VIEW
        where ITCD = #{param}
        <if test="start != null and end != null ">
            and to_char(SCADA_TIME,'yyyy-mm-dd hh24:mi:ss') between #{start} and #{end}
        </if>
        <if test="start == null and end == null">
            and SCADA_TIME between add_months(sysdate,-3) and sysdate
        </if>
    </select>

    <select id="getPowerHistory" resultType="map">
        select UP_DATE, ${param}
        from TF_YWPN_COSTRLWATER_W
        where MARK = '0'
        <if test="start != null and end != null">
            and to_char(up_date,'yyyy-mm-dd hh24:mi:ss') between #{start} and #{end}
        </if>
        <if test="start == null and end == null">
            and up_date between add_months(sysdate,-3) and sysdate
        </if>
    </select>

    <select id="getStationPower" resultType="long">
        select sum(DWHD)
        from TF_YWPN_COSTPRESTATION_W
        where to_char(UP_DATE, 'yyyy-mm-dd') = to_char(sysdate - 1, 'yyyy-mm-dd')
          and MARK = '0'
        group by to_char(UP_DATE, 'yyyy-mm-dd')
    </select>

    <select id="getValue" resultType="map">
        select PAC_ONE                                      pacone,
               PAC_TWO                                      pactwo,
               SY_ONE                                       syone,
               YL_TWO                                       yltwo,
               round(DLDL_ONE / nullif(ZSL_ONE, 0) * 10, 5) dhone,
               round(DLDL_TWO / nullif(ZSL_TWO, 0) * 10, 5) dhtwo
        from TF_YWPN_COSTRLWATER_W
        where MARK = '0'
          and to_char(UP_DATE, 'yyyy-mm-dd') = to_char(sysdate - 1, 'yyyy-mm-dd')

    </select>

    <select id="getRealVal" resultType="map">
        select ITCD,ITVAL,SCADA_TIME
        from TF_YWPN_SCADAREALTIME_R
        where ITCD in
        <foreach collection="list" item="itcd" open="(" separator="," close=")">
            #{itcd}
        </foreach>
    </select>

    <select id="monthNrw" resultType="map">
        select trunc(((nvl(GSL_TOTAL, 0) +
                       nvl(GSL_QBJ, 0) -
                       nvl(SSL_TOTAL, 0)) /
                      nullif(nvl(GSL_TOTAL, 0) + nvl(GSL_QBJ, 0), 0) * 100),
                     4)                    nrw,
               extract(month from UP_DATE) month
        from TF_YWPN_COSTRLWATER_W
        where extract(year from UP_DATE) = extract(year from sysdate)
          and MARK = '1'
        order by month
    </select>

    <select id="yearNrwPlan" resultType="double">
        select trunc(((nvl(sum(GSL_TOTAL), 0) +
                       nvl(sum(GSL_QBJ), 0) -
                       nvl(sum(SSL_TOTAL), 0)) /
                      nullif(nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0), 0) * 100), 2) nrw
        from TF_YWPN_COSTBGWATER_W tbg
        where extract(year from sysdate) =
              (select YEAR from TF_YWPN_COSTBGWATERYEAR_W tbgYear where tbg.YEAR_ID = tbgYear.ID)
        group by YEAR_ID
    </select>

    <select id="yearNrw" resultType="double">
        select trunc(((nvl(sum(GSL_TOTAL), 0) +
                       nvl(sum(GSL_QBJ), 0) -
                       nvl(sum(SSL_TOTAL), 0)) /
                      nullif(nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0), 0) * 100), 4) nrw
        from TF_YWPN_COSTRLWATER_W
        where extract(year from UP_DATE
                  ) = extract(year from sysdate)
          and MARK = '1'
        group by extract(year from UP_DATE
                     )
    </select>

    <select id="monthLeakage" resultType="map">
        select trunc(((nvl(GSL_TOTAL, 0) + nvl(GSL_QBJ, 0) - nvl(SSL_TOTAL, 0) -
                       nvl(WSPSL, 0)) / nullif(nvl(GSL_TOTAL, 0) + nvl(GSL_QBJ, 0), 0) * 100), 4) leakage,
               extract(month from UP_DATE)                                                        month
        from TF_YWPN_COSTRLWATER_W
        where extract(year from UP_DATE) = extract(year from sysdate)
          and MARK = '1'
        order by month
    </select>

    <select id="yearLeakage" resultType="double">
        select trunc(((nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0) -
                       nvl(sum(SSL_TOTAL), 0) - nvl(sum(WSPSL), 0)) /
                      nullif(nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0), 0) * 100), 4) leakage
        from TF_YWPN_COSTRLWATER_W
        where extract(year from UP_DATE
                  ) = extract(year from sysdate)
          and MARK = '1'
        group by extract(year from UP_DATE
                     )
    </select>

    <select id="yearLeakagePlan" resultType="double">
        select trunc(((nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0) -
                       nvl(sum(SSL_TOTAL), 0) - nvl(sum(WSPSL), 0)) /
                      nullif(nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0), 0) * 100), 4) leakage
        from TF_YWPN_COSTBGWATER_W tbg
        where extract(year from sysdate) =
              (select YEAR from TF_YWPN_COSTBGWATERYEAR_W tbgYear where tbg.YEAR_ID = tbgYear.ID)
        group by YEAR_ID
    </select>

    <select id="monSupSellWater" resultType="map">
        select nvl(GSL_TOTAL, 0) gsl,
               nvl(SSL_TOTAL, 0) ssl,
               extract(month from UP_DATE
                   )             month
        from TF_YWPN_COSTRLWATER_W
        where MARK = '1'
          and extract(year from sysdate) = extract(year from UP_DATE)
        order by month
    </select>

    <select id="monSupSellWaterPlan" resultType="map">
        select nvl(GSL_TOTAL, 0) gsl, nvl(SSL_TOTAL, 0) ssl, MONTH
        from TF_YWPN_COSTBGWATER_W tbg
        where extract(year from sysdate) = (select year from TF_YWPN_COSTBGWATERYEAR_W year where tbg.YEAR_ID = year.ID)
    </select>

    <select id="yearSupSellWater" resultType="map">
        select nvl(sum(GSL_TOTAL), 0) gsl,
               nvl(sum(SSL_TOTAL), 0) ssl
        from TF_YWPN_COSTRLWATER_W
        where MARK = '1'
          and extract(year from sysdate) = extract(year from UP_DATE)
        group by extract(year from UP_DATE)
    </select>

    <select id="yearSupSellWaterPlan" resultType="map">
        select nvl(sum(GSL_TOTAL), 0) gsl, nvl(sum(SSL_TOTAL), 0) ssl
        from TF_YWPN_COSTBGWATER_W tbg
        where extract(year from sysdate) = (select year from TF_YWPN_COSTBGWATERYEAR_W year where tbg.YEAR_ID = year.ID)
        group by YEAR_ID
    </select>

    <select id="monthFixed" resultType="map">
        select to_char(fixed.UP_DATE, 'yyyy-mm') month,
               trunc(((decode(sum(YSF), null, 0, sum(YSF)) +
                       decode(sum(SZYSF_TOTAL), null, 0, sum(SZYSF_TOTAL)) +
                       decode(sum(fixed.JBDF_TOTAL), null, 0, sum(fixed.JBDF_TOTAL)) +
                       decode(sum(fixed.DLDF_TOTAL), null, 0, sum(fixed.DLDF_TOTAL)) +
                       decode(sum(fixed.PACZFY_ONE), null, 0, sum(fixed.PACZFY_ONE)) +
                       decode(sum(fixed.PACZFY_TWO), null, 0, sum(fixed.PACZFY_TWO)) +
                       decode(sum(fixed.YLZFY_TWO), null, 0, sum(fixed.YLZFY_TWO)) +
                       decode(sum(fixed.SYZFY_ONE), null, 0, sum(fixed.SYZFY_ONE)) +
                       decode(sum(fixed.PAMZSZFY), null, 0, sum(fixed.PAMZSZFY)) +
                       decode(sum(fixed.PAMWNZFY), null, 0, sum(fixed.PAMWNZFY)) +
                       decode(sum(fixed.FTZFY), null, 0, sum(fixed.FTZFY)) +
                       decode(sum(fixed.GMSJFHYZFY), null, 0, sum(fixed.GMSJFHYZFY)) +
                       decode(sum(fixed.CNZFY), null, 0, sum(fixed.CNZFY)) +
                       decode(sum(fixed.BCSZFY), null, 0, sum(fixed.BCSZFY)) +
                       decode(sum(fixed.WNZYF), null, 0, sum(fixed.WNZYF))) * 1000 /
                      nullif(sum(TYCW.ZSL_TOTAL), 0)),
                     2)                          cost
        from TF_YWPN_COSTRLFIXED_W fixed
                 full join TF_YWPN_COSTRLWATER_W TYCW
                           on to_char(fixed.UP_DATE
                                  , 'yyyy-mm') = to_char(TYCW.UP_DATE
                                  , 'yyyy-mm')
        where fixed.MARK = '1'
          and to_char(fixed.UP_DATE, 'yyyy') = extract(year from sysdate)
        group by to_char(fixed.UP_DATE, 'yyyy-mm')
        order by month
    </select>

    <select id="yearFixed" resultType="double">
        select trunc(((decode(sum(YSF), null, 0, sum(YSF)) +
                       decode(sum(SZYSF_TOTAL), null, 0, sum(SZYSF_TOTAL)) +
                       decode(sum(fixed.JBDF_TOTAL), null, 0, sum(fixed.JBDF_TOTAL)) +
                       decode(sum(fixed.DLDF_TOTAL), null, 0, sum(fixed.DLDF_TOTAL)) +
                       decode(sum(fixed.PACZFY_ONE), null, 0, sum(fixed.PACZFY_ONE)) +
                       decode(sum(fixed.PACZFY_TWO), null, 0, sum(fixed.PACZFY_ONE)) +
                       decode(sum(fixed.YLZFY_TWO), null, 0, sum(fixed.YLZFY_TWO)) +
                       decode(sum(fixed.SYZFY_ONE), null, 0, sum(fixed.SYZFY_ONE)) +
                       decode(sum(fixed.PAMZSZFY), null, 0, sum(fixed.PAMZSZFY)) +
                       decode(sum(fixed.PAMWNZFY), null, 0, sum(fixed.PAMWNZFY)) +
                       decode(sum(fixed.FTZFY), null, 0, sum(fixed.FTZFY)) +
                       decode(sum(fixed.GMSJFHYZFY), null, 0, sum(fixed.GMSJFHYZFY)) +
                       decode(sum(fixed.CNZFY), null, 0, sum(fixed.CNZFY)) +
                       decode(sum(fixed.BCSZFY), null, 0, sum(fixed.BCSZFY)) +
                       decode(sum(fixed.WNZYF), null, 0, sum(fixed.WNZYF))) * 1000 /
                      nullif(sum(TYCW.ZSL_TOTAL), 0)),
                     2) cost
        from TF_YWPN_COSTRLFIXED_W fixed
                 full join TF_YWPN_COSTRLWATER_W TYCW
                           on to_char(fixed.UP_DATE, 'yyyy') = to_char(TYCW.UP_DATE, 'yyyy')
        where fixed.MARK = '1'
          and extract(year from fixed.UP_DATE
                  ) = extract(year from sysdate)
        group by extract(year from fixed.UP_DATE
                     )
    </select>

    <select id="yearFixedPlan" resultType="double">
        select trunc(((decode(sum(YSF), null, 0, sum(YSF)) +
                       decode(sum(SZYSF_TOTAL), null, 0, sum(SZYSF_TOTAL)) +
                       decode(sum(fixed.JBDF_TOTAL), null, 0, sum(fixed.JBDF_TOTAL)) +
                       decode(sum(fixed.DLDF_TOTAL), null, 0, sum(fixed.DLDF_TOTAL)) +
                       decode(sum(fixed.PACZFY_ONE), null, 0, sum(fixed.PACZFY_ONE)) +
                       decode(sum(fixed.PACZFY_TWO), null, 0, sum(fixed.PACZFY_TWO)) +
                       decode(sum(fixed.YLZFY_TWO), null, 0, sum(fixed.YLZFY_TWO)) +
                       decode(sum(fixed.SYZFY_ONE), null, 0, sum(fixed.SYZFY_ONE)) +
                       decode(sum(fixed.PAMZSZFY), null, 0, sum(fixed.PAMZSZFY)) +
                       decode(sum(fixed.PAMWNZFY), null, 0, sum(fixed.PAMWNZFY)) +
                       decode(sum(fixed.FTZFY), null, 0, sum(fixed.FTZFY)) +
                       decode(sum(fixed.GMSJFHYZFY), null, 0, sum(fixed.GMSJFHYZFY)) +
                       decode(sum(fixed.CNZFY), null, 0, sum(fixed.CNZFY)) +
                       decode(sum(fixed.BCSZFY), null, 0, sum(fixed.BCSZFY)) +
                       decode(sum(fixed.WNZYF), null, 0, sum(fixed.WNZYF))) * 1000 /
                      nullif(sum(TYCW.ZSL_TOTAL), 0)), 2) cost
        from TF_YWPN_COSTBGFIXED_W fixed
                 full join TF_YWPN_COSTBGWATER_W tycw
                           on fixed.MOUTH = fixed.MOUTH
        where to_char(to_date(fixed.MOUTH, 'yyyy-mm'), 'yyyy') = extract(year from sysdate)
        group by to_char(to_date(fixed.MOUTH, 'yyyy-mm'), 'yyyy')
    </select>

    <select id="getTodayWater" resultType="double">
        select itval
        from TF_YWPN_SCADAHISTORY_R_VIEW
        where SCADA_TIME >= to_date(concat(to_char(sysdate, 'yyyy-mm-dd'), ' 08:00:00'), 'yyyy-mm-dd HH24:mi:ss')
          and itcd = #{itcd}
          and rownum = 1
    </select>

    <select id="getYesterday" resultType="double">
        select itval
        from TF_YWPN_SCADAHISTORY_R_VIEW
        where SCADA_TIME >= to_date(concat(to_char(sysdate - 1, 'yyyy-mm-dd'), ' 08:00:00'), 'yyyy-mm-dd HH24:mi:ss')
          and itcd = #{itcd}
          and rownum = 1
    </select>

    <select id="medicine" resultType="map">
        select (decode(PAC_ONE, null, 0, PAC_ONE) + decode(SY_ONE, null, 0, SY_ONE)) ychl,
               (decode(PAC_TWO, null, 0, PAC_TWO) + decode(YL_TWO, null, 0, YL_TWO)) echl
        from TF_YWPN_COSTRLWATER_W
        where rownum = 1
          and MARK = '0'
          and UP_DATE &lt;= to_date(concat(to_char(sysdate, 'yyyy-mm-dd'), ' 00:00:00'), 'yyyy-mm-dd hh24:mi:ss')
    </select>

    <select id="power" resultType="map">
        select DH_ONE ycdh, DH_TWO ecdh
        from TF_YWPN_COSTRLUNIT_W
        where rownum = 1
          and MARK = '0'
          and UP_DATE
            &lt;= to_date(concat(to_char(sysdate, 'yyyy-mm-dd'), ' 00:00:00'), 'yyyy-mm-dd hh24:mi:ss')
    </select>

    <select id="nrwByMonth" resultType="map">
        select CXC nrw,
        to_char(UP_DATE
        ,'yyyy-mm') month
        from TF_YWPN_COSTRLWATER_W
        where MARK = 1
        <if test="year != null and code == 0">
            and extract(year from UP_DATE
            ) = #{year}
        </if>
        <if test="code == 2">
            and extract(year from UP_DATE
            ) between extract(year from sysdate) - 3 and extract(year from sysdate)
        </if>
        order by month
    </select>

    <select id="nrwByCodePlan" resultType="map">
        select
        CXC nrw,
        month
        from TF_YWPN_COSTBGWATER_W
        <where>
            <if test="year != null and code == 0">
                substr(MONTH,1,4) = #{year}
            </if>
            <if test="code == 2">
                substr(MONTH,1,4) between extract(year from sysdate) - 3 and extract(year from sysdate)
            </if>
            order by month
        </where>

    </select>

    <select id="nrwByQuarterly" resultType="map">
        select trunc(((sum(GSL_TOTAL) + sum(GSL_QBJ) - sum(SSL_TOTAL)) /
                      nullif(sum(GSL_TOTAL) + sum(GSL_QBJ), 0) * 100), 4) nrw,
               to_char(UP_DATE, 'Q')                                      que,
               extract(year from UP_DATE)                                 year
        from TF_YWPN_COSTRLWATER_W
        where MARK = 1
          and extract(year from UP_DATE) = #{year}
        group by to_char(UP_DATE, 'Q'), extract(year from UP_DATE)
        order by year, que
    </select>

    <select id="nrwByQuarterlyPlan" resultType="map">
        select trunc(((sum(GSL_TOTAL) - sum(SSL_TOTAL) + sum(GSL_QBJ)) /
                      nullif(sum(GSL_TOTAL) + sum(GSL_QBJ), 0) * 100), 2) nrw,
               to_char(to_date(MONTH, 'yyyy-mm'), 'Q')                    que,
               substr(MONTH, 1, 4)                                        year
        from TF_YWPN_COSTBGWATER_W
        where substr(MONTH, 1, 4) = #{year}
        group by to_char(to_date(MONTH, 'yyyy-mm'), 'Q'), substr(MONTH, 1, 4)
        order by year, que
    </select>

    <select id="leakageByMonth" resultType="map">
        select LSL leakage,
        to_char(UP_DATE
        ,'yyyy-mm') month
        from TF_YWPN_COSTRLWATER_W
        where MARK = '1'
        <if test="year != null and code == 0">
            and extract(year from UP_DATE) = #{year}
        </if>
        <if test="code == 2">
            and extract(year from UP_DATE) between extract(year from sysdate) - 3 and extract(year from sysdate)
        </if>
        order by month
    </select>

    <select id="leakageByCodePlan" resultType="map">
        select LSL leakage,
        month
        from TF_YWPN_COSTBGWATER_W
        <where>
            <if test="year != null and code == 0">
                substr(MONTH,1,4) = #{year}
            </if>
            <if test="code == 2">
                substr(MONTH,1,4) between extract(year from sysdate) - 3 and extract(year from sysdate)
            </if>
            order by month
        </where>
    </select>

    <select id="leakageByQuarterly" resultType="map">
        select trunc(((sum(GSL_TOTAL) -
                       sum(SSL_TOTAL) +
                       sum(GSL_QBJ) -
                       sum(WSPSL)) / nullif(sum(GSL_TOTAL) + sum(GSL_QBJ), 0) *
                      100), 2)            leakage,
               to_char(UP_DATE, 'Q')      que,
               extract(year from UP_DATE) year
        from TF_YWPN_COSTRLWATER_W
        where MARK = '1'
          and extract(year from UP_DATE) = #{year}
        group by to_char(UP_DATE, 'Q'), extract(year from UP_DATE)
        order by year, que
    </select>

    <select id="leakageByQuarterlyPlan" resultType="map">
        select trunc(((sum(GSL_TOTAL) -
                       sum(SSL_TOTAL) -
                       sum(WSPSL) +
                       sum(GSL_QBJ)) /
                      nullif(sum(GSL_TOTAL) + sum(GSL_QBJ), 0) * 100),
                     2)                                leakage,
               to_char(to_date(MONTH, 'yyyy-mm'), 'Q') que,
               substr(MONTH, 1, 4)                     year
        from TF_YWPN_COSTBGWATER_W
        where substr(MONTH, 1, 4) = #{year}
        group by to_char(to_date(MONTH, 'yyyy-mm'), 'Q'), substr(MONTH, 1, 4)
        order by year, que
    </select>

    <select id="supSellMore" resultType="map">
        select nvl(GSL_TOTAL, 0) gsl,
        nvl(SSL_TOTAL, 0) ssl,
        to_char(UP_DATE,'yyyy-mm') month
        from TF_YWPN_COSTRLWATER_W
        where MARK = '1'
        <if test="code == 0">
            and extract(year from sysdate) = #{year}
        </if>
        <if test="code == 2">
            and extract(year from UP_DATE
            ) between extract(year from sysdate) - 3 and extract(year from sysdate)
        </if>
        order by month
    </select>

    <select id="supSellMoreQuarterly" resultType="map">
        select nvl(sum(GSL_TOTAL), 0)     gsl,
               nvl(sum(SSL_TOTAL), 0)     ssl,
               to_char(UP_DATE, 'Q')      que,
               extract(year from UP_DATE) year
        from TF_YWPN_COSTRLWATER_W
        where MARK = '1'
          and extract(year from UP_DATE) = #{year}
        group by to_char(UP_DATE, 'Q'), extract(year from UP_DATE)
        order by year, que
    </select>

    <select id="supSellMorePlan" resultType="map">
        select nvl(GSL_TOTAL, 0) gsl,
        nvl(SSL_TOTAL, 0) ssl,
        MONTH
        from TF_YWPN_COSTBGWATER_W
        <where>
            <if test="code == 0">
                substr(MONTH,1,4) = #{year}
            </if>
            <if test="code == 2">
                substr(MONTH,1,4) between extract(year from sysdate) - 3 and extract(year from sysdate)
            </if>
            order by month
        </where>
    </select>

    <select id="supSellMoreQuarterlyPlan" resultType="map">
        select nvl(sum(GSL_TOTAL), 0)                  gsl,
               nvl(sum(SSL_TOTAL), 0)                  ssl,
               to_char(to_date(MONTH, 'yyyy-mm'), 'Q') que,
               substr(MONTH, 1, 4)                     year
        from TF_YWPN_COSTBGWATER_W
        where substr(MONTH, 1, 4) = #{year}
        group by to_char(to_date(MONTH, 'yyyy-mm'), 'Q'), substr(MONTH, 1, 4)
        order by year, que
    </select>

    <select id="firstPlantFixed" resultType="map">
        select DWCB_ONE fixed,
        to_char(fixed.UP_DATE,'yyyy-mm') mon
        from TF_YWPN_COSTRLFIXED_W fixed
        where fixed.MARK = 1
        <if test="code == 0">
            and extract(year from fixed.UP_DATE) = #{year}
        </if>
        <if test="code == 2">
            and to_char(fixed.UP_DATE,'yyyy')
            between to_char(add_months(to_date(#{year},'yyyy'),-36),'yyyy') and #{year}
        </if>
        order by mon
    </select>
    <!--备份-->
    <!--<select id="firstPlantFixed" resultType="map">
        select
        trunc((decode(sum(fixed.PACZFY_ONE),null, 0,sum(fixed.PACZFY_ONE)) +
        decode(sum(fixed.SYZFY_ONE),null,0,sum(fixed.SYZFY_ONE)) +
        decode(sum(fixed.CNZFY),null, 0,sum(fixed.CNZFY)) +
        decode(sum(fixed.BCSZFY),null, 0,sum(fixed.BCSZFY)) +
        decode(sum(fixed.DLDF_ONE),null,0,sum(fixed.DLDF_ONE)) +
        decode(sum(fixed.JBDF_ONE),null,0,sum(fixed.JBDF_ONE)) +
        decode(sum(tycw.ZSL_HQSK),null,0,sum(tycw.ZSL_HQSK)) * 0.24 +
        decode(sum(tycw.ZSL_ONE),null, 0,sum(tycw.ZSL_ONE)) * 0.088)/decode(sum(tycw.GSL_ONE),0,null,sum(tycw.GSL_ONE))
        ,5) fixed,
        to_char(fixed.UP_DATE
        ,'yyyy-mm') mon
        from TF_YWPN_COSTRLFIXED_W fixed
        full join TF_YWPN_COSTRLWATER_W tycw
        on to_char(tycw.UP_DATE
        ,'yyyy-mm') = to_char(fixed.UP_DATE
        ,'yyyy-mm')
        <where>
            <if test="code == 0">
                extract(year from fixed.UP_DATE) = #{year}
            </if>
            <if test="code == 2">
                to_char(fixed.UP_DATE,'yyyy')
                between to_char(add_months(to_date(#{year},'yyyy'),-36),'yyyy') and #{year}
            </if>
        </where>
        group by to_char(fixed.UP_DATE,'yyyy-mm')
        order by mon
    </select>-->

    <select id="firstPlantFixedYear" resultType="map">
        select trunc((nvl(sum(ZFY_ONE), 0) / nullif(sum(tycw.GSL_TOTAL), 0)) * 1000, 3) info,
               extract(year from fixed.UP_DATE)                                         year
        from TF_YWPN_COSTRLFIXED_W fixed
                 full join TF_YWPN_COSTRLWATER_W tycw
                           on to_char(tycw.UP_DATE, 'yyyy-mm') = to_char(fixed.UP_DATE, 'yyyy-mm')
                               and tycw.MARK = fixed.MARK
        where tycw.MARK = 1
          and extract(year from fixed.UP_DATE) = #{year}
        group by extract(year from fixed.UP_DATE)
    </select>

    <select id="secondPlantFixed" resultType="map">
        select DWCB_TWO fixed,
        to_char(fixed.UP_DATE,'yyyy-mm') mon
        from TF_YWPN_COSTRLFIXED_W fixed
        where fixed.MARK = 1
        <if test="code == 0">
            and extract(year from fixed.UP_DATE) = #{year}
        </if>
        <if test="code == 2">
            and to_char(fixed.UP_DATE,'yyyy')
            between to_char(add_months(to_date(#{year},'yyyy'),-36),'yyyy') and #{year}
        </if>
        order by mon
    </select>

    <!--  secondPlantFixed  备份
    select trunc((decode(sum(fixed.PACZFY_TWO),null, 0,sum(fixed.PACZFY_TWO)) +
        decode(sum(fixed.YLZFY_TWO),null, 0,sum(fixed.YLZFY_TWO)) +
        decode(sum(fixed.PAMZSZFY),null, 0,sum(fixed.PAMZSZFY)) +
        decode(sum(fixed.WNZYF),null, 0,sum(fixed.WNZYF)) +
        decode(sum(fixed.FTZFY),null, 0,sum(fixed.FTZFY)) +
        decode(sum(fixed.GMSJFHYZFY),null, 0,sum(fixed.GMSJFHYZFY)) +
        decode(sum(fixed.WNZYF),null, 0,sum(fixed.WNZYF)) +
        decode(sum(tycw.ZSL_ONE),null, 0,sum(tycw.ZSL_ONE)) * 0.088) /
        decode(sum(tycw.GSL_TWO),0,null,sum(tycw.GSL_TWO)),
        5) fixed,
        to_char(fixed.UP_DATE
        ,'yyyy-mm') mon
        from TF_YWPN_COSTRLFIXED_W fixed
        full join TF_YWPN_COSTRLWATER_W tycw
        on to_char(tycw.UP_DATE
        ,'yyyy-mm') = to_char(fixed.UP_DATE
        ,'yyyy-mm')
        <where>
            <if test="code == 0">
                extract(year from fixed.UP_DATE
                ) = #{year}
            </if>
            <if test="code == 2">
                to_char(fixed.UP_DATE,'yyyy')
                between to_char(add_months(to_date(#{year},'yyyy'),-36),'yyyy') and #{year}
            </if>
        </where>
        group by to_char(fixed.UP_DATE,'yyyy-mm')
        order by mon-->

    <select id="secondPlantFixedYear" resultType="map">
        select trunc((nvl(sum(ZFY_TWO), 0) /
                      nullif(sum(tycw.GSL_TOTAL), 0)) * 1000, 3) fixed,
               extract(year from fixed.UP_DATE)                  year
        from TF_YWPN_COSTRLFIXED_W fixed
                 full join TF_YWPN_COSTRLWATER_W tycw
                           on to_char(tycw.UP_DATE, 'yyyy-mm') = to_char(fixed.UP_DATE, 'yyyy-mm')
                               and tycw.MARK = fixed.MARK
        where fixed.MARK = 1
          and extract(year from fixed.UP_DATE) = #{year}
        group by extract(year from fixed.UP_DATE)
    </select>

    <select id="firstPlantFixedPlan" resultType="map">
        select
        DWCB_ONE fixed,
        fixed.MOUTH
        from TF_YWPN_COSTBGFIXED_W fixed
        <where>
            <if test="code == 0">
                substr(fixed.mouth,1,4) = #{year}
            </if>
            <if test="code == 2">
                substr(fixed.mouth,1,4) between to_char(add_months(to_date(#{year},'yyyy'),-36),'yyyy') and #{year}
            </if>
        </where>
        order by fixed.MOUTH
    </select>
    <!--备份-->
    <!--<select id="firstPlantFixedPlan" resultType="map">
        select
        trunc((decode(PACZFY_ONE,null,0,PACZFY_ONE) +
        decode(SYZFY_ONE,null,0,SYZFY_ONE) +
        decode(CNZFY,null,0,CNZFY) +
        decode(BCSZFY,null,0,BCSZFY) +
        decode(DLDF_ONE,null,0,DLDF_ONE) +
        decode(JBDF_ONE,null,0,JBDF_ONE) +
        decode(ZSL_HQSK,null,0,ZSL_HQSK) * 0.24 +
        decode(ZSL_ONE,null,0,ZSL_ONE) * 0.088)/decode(GSL_ONE,0,null,GSL_ONE) ,5) fixed,
        fixed.MOUTH
        from TF_YWPN_COSTBGFIXED_W fixed
        full join TF_YWPN_COSTBGWATER_W tycw
        on tycw.MONTH = fixed.MOUTH
        <where>
            <if test="code == 0">
                substr(fixed.mouth,1,4) = #{year}
            </if>
            <if test="code == 2">
                substr(fixed.mouth,1,4) between to_char(add_months(to_date(#{year},'yyyy'),-36),'yyyy') and #{year}
            </if>
        </where>
        order by fixed.MOUTH
    </select>-->

    <select id="firstPlantFixedPlanYear" resultType="map">
        select trunc((nvl(sum(ZFY_ONE), 0) / nullif(sum(GSL_TOTAL), 0)) * 1000, 3) info,
               substr(MOUTH, 1, 4)                                                 year
        from TF_YWPN_COSTBGFIXED_W fixed
                 full join TF_YWPN_COSTBGWATER_W tycw
                           on tycw.MONTH = fixed.MOUTH
        where substr(MOUTH, 1, 4) = #{year}
        group by substr(MOUTH, 1, 4)
    </select>

    <select id="secondPlantFixedPlan" resultType="map">
        select DWCB_TWO fixed,MOUTH
        from TF_YWPN_COSTBGFIXED_W fixed
        <where>
            <if test="code == 0">
                substr(fixed.mouth,1,4) = #{year}
            </if>
            <if test="code == 2">
                substr(fixed.mouth,1,4) between to_char(add_months(to_date(#{year},'yyyy'),-36),'yyyy') and #{year}
            </if>
        </where>
        order by fixed.MOUTH
    </select>

    <select id="secondPlantFixedPlanYear" resultType="map">
        select trunc((nvl(sum(ZFY_TWO), 0) / nullif(sum(GSL_TOTAL), 0)) * 1000, 3) fixed,
               substr(MOUTH, 1, 4)                                                 year
        from TF_YWPN_COSTBGFIXED_W fixed
                 full join TF_YWPN_COSTBGWATER_W tycw
                           on tycw.MONTH = fixed.MOUTH
        where substr(MOUTH, 1, 4) = #{year}
        group by substr(MOUTH, 1, 4)
    </select>

    <select id="firstPlantQue" resultType="map">
        select trunc((nvl(sum(fixed.ZFY_ONE), 0) / nullif(sum(tycw.GSL_TOTAL), 0) * 1000), 3) fixed,
               to_char(fixed.UP_DATE, 'Q')                                                    que,
               extract(year from fixed.UP_DATE)                                               year
        from TF_YWPN_COSTRLFIXED_W fixed
                 full join TF_YWPN_COSTRLWATER_W tycw
                           on to_char(tycw.UP_DATE
                                  , 'yyyy-mm') = to_char(fixed.UP_DATE
                                  , 'yyyy-mm')
        where extract(year from fixed.UP_DATE) = #{year}
        group by to_char(fixed.UP_DATE, 'Q'), extract(year from fixed.UP_DATE)
        order by year, que
    </select>

    <select id="secondPlantQue" resultType="map">
        select trunc((nvl(sum(fixed.ZFY_TWO), 0) /
                      nullif(sum(tycw.GSL_TOTAL), 0) * 1000), 3) fixed,
               to_char(fixed.UP_DATE, 'Q')                       que,
               extract(year from fixed.UP_DATE)                  year

        from TF_YWPN_COSTRLFIXED_W fixed
                 full join TF_YWPN_COSTRLWATER_W tycw
                           on to_char(tycw.UP_DATE, 'yyyy-mm') = to_char(fixed.UP_DATE, 'yyyy-mm')
        where extract(year from fixed.UP_DATE) = #{year}
        group by to_char(fixed.UP_DATE, 'Q'), extract(year from fixed.UP_DATE)
        order by year, que
    </select>

    <select id="firstPlantQuePlan" resultType="map">
        select trunc((nvl(sum(fixed.ZFY_ONE), 0) /
                      nullif(sum(tycw.GSL_TOTAL), 0) * 1000), 3) fixed,
               to_char(to_date(fixed.MOUTH, 'yyyy-mm'), 'Q')     que,
               substr(fixed.mouth, 1, 4)                         year
        from TF_YWPN_COSTBGFIXED_W fixed
                 full join TF_YWPN_COSTBGWATER_W tycw
                           on tycw.MONTH = fixed.MOUTH
        where substr(fixed.mouth, 1, 4) = #{year}
        group by to_char(to_date(fixed.MOUTH, 'yyyy-mm'), 'Q'), substr(fixed.mouth, 1, 4)
        order by que
    </select>

    <select id="secondPlantQuePlan" resultType="map">
        select trunc((nvl(sum(fixed.ZFY_TWO), 0) /
                      nullif(sum(tycw.GSL_TOTAL), 0) * 1000), 3) fixed,
               to_char(to_date(fixed.MOUTH, 'yyyy-mm'), 'Q')     que,
               substr(fixed.mouth, 1, 4)                         year
        from TF_YWPN_COSTBGFIXED_W fixed
                 full join TF_YWPN_COSTBGWATER_W tycw
                           on tycw.MONTH = fixed.MOUTH
        where substr(fixed.mouth, 1, 4) = #{year}
        group by to_char(to_date(fixed.MOUTH, 'yyyy-mm'), 'Q'), substr(fixed.mouth, 1, 4)
        order by que
    </select>

    <select id="getWarn" resultType="map">
        select warn_type,id,
        (select RANGEID from TF_YWPN_SCADASTATION_B where ID = (select STATION_ID from TF_YWPN_SCADASTAITEM_W where
        TF_YWPN_SCADASTAITEM_W.ITCD = TF_YWPN_WARN_W.ITCD)) RANGE,
        notes,
        ITCD,
        address,
        num,
        isconfirm,
        isclear,
        scada_time
        from TF_YWPN_WARN_W
        <if test="code != null">
            where isconfirm = #{code}
        </if>
        order by SCADA_TIME desc
    </select>

    <select id="getWarHistory" resultType="map">
        select id,
        itcd,
        warn_type,
        notes,
        address,
        num,
        isconfirm,
        isclear,
        scada_time
        from TF_YWPN_WARNHISTORY_W_VIEW
        <where>
            <if test="type != null">
                WARN_TYPE = #{type}
            </if>
            <if test="start != null and end != null">
                and to_char(scada_time,'yyyy-mm-dd hh24:mi:ss') between #{start} and #{end}
            </if>
            <if test="isclear != null">
                and isclear = #{isclear}
            </if>
            <if test="isconfirm != null">
                and isconfirm = #{isconfirm}
            </if>
        </where>
    </select>

    <select id="countWarnType" resultType="map">
        select (select CNAME from TF_YWPN_SYSCHILDCODE_D where PCID = 845 and CCODE = WARN_TYPE) typeName,
               count(WARN_TYPE)                                                                  sum
        from TF_YWPN_WARN_W
        where ISCONFIRM = 0
        group by WARN_TYPE
    </select>

    <select id="getRealIndexInfo" resultType="com.tofly.xrtygis.custom.entity.vo.IndexInfoVo">
        select ID,ITCD, ITVAL
        from TF_YWPN_SCADAREALTIME_R
        where ITCD in
        <foreach collection="list" item="itcd" open="(" close=")" separator=",">
            #{itcd}
        </foreach>
    </select>

    <select id="existWarn" resultType="map">
        select WARN_TYPE
        from TF_YWPN_WARN_W
        where ITCD = #{itcd,jdbcType = VARCHAR}
    </select>

    <select id="getWarnType" resultType="map">
        select distinct (select CNAME from TF_YWPN_SYSCHILDCODE_D where PCID = 845 and CCODE = WARN_TYPE) type_name,
                        WARN_TYPE
        from TF_YWPN_WARNHISTORY_W_VIEW
    </select>

    <select id="getEveryCostOne" resultType="map">
        select sum(YSFCB)       YSFCB,
               sum(SZYSFCB_ONE) SZYSFCB_ONE,
               sum(DFCB_ONE)    DFCB_ONE,
               sum(ZSCLCB_ONE)  ZSCLCB_ONE,
               sum(GSL_ONE)     GSL_ONE
        from TF_YWPN_COSTRLFIXED_W f,
             TF_YWPN_COSTRLWATER_W w
        where to_char(f.UP_DATE, 'yyyy') = to_char(w.UP_DATE, 'yyyy')
          and f.MARK = w.MARK
          and f.MARK = 1
          and to_char(f.UP_DATE, 'yyyy') = #{year}
    </select>

    <select id="getEveryCostOneMonth" resultType="map">
        select YSFCB,
               SZYSFCB_ONE,
               DFCB_ONE,
               ZSCLCB_ONE,
               GSL_ONE,
               to_char(f.UP_DATE, 'yyyy-mm') month
        from TF_YWPN_COSTRLFIXED_W f,
             TF_YWPN_COSTRLWATER_W w
        where to_char(f.UP_DATE, 'yyyy-mm') = to_char(w.UP_DATE, 'yyyy-mm')
          and f.MARK = w.MARK
          and f.MARK = 1
          and to_char(f.UP_DATE, 'yyyy') = #{year}
        order by month
    </select>

    <select id="getEveryCostOneQue" resultType="map">
        select sum(YSFCB),
               sum(SZYSFCB_ONE),
               sum(DFCB_ONE),
               sum(ZSCLCB_ONE),
               sum(GSL_ONE),
               to_char(f.UP_DATE, 'Q') que
        from TF_YWPN_COSTRLFIXED_W f,
             TF_YWPN_COSTRLWATER_W w
        where to_char(f.UP_DATE, 'yyyy-mm') = to_char(w.UP_DATE, 'yyyy-mm')
          and f.MARK = w.MARK
          and f.MARK = 1
          and to_char(f.UP_DATE, 'yyyy') = #{year}
        group by to_char(f.UP_DATE, 'Q')
        order by que
    </select>

    <select id="getEveryCostTwo" resultType="map">
        select sum(SZYSFCB_TWO) SZYSFCB_TWO,
               sum(DFCB_TWO)    DFCB_TWO,
               sum(ZSCLCB_TWO)  ZSCLCB_TWO,
               sum(GSL_TWO)     GSL_TWO
        from TF_YWPN_COSTRLFIXED_W f,
             TF_YWPN_COSTRLWATER_W w
        where to_char(f.UP_DATE, 'yyyy') = to_char(w.UP_DATE, 'yyyy')
          and f.MARK = w.MARK
          and f.MARK = 1
          and to_char(f.UP_DATE, 'yyyy') = #{year}
    </select>

    <select id="getEveryCostTwoMonth" resultType="map">
        select SZYSFCB_TWO,
               DFCB_TWO,
               ZSCLCB_TWO,
               GSL_TWO,
               to_char(f.UP_DATE, 'yyyy-mm') month
        from TF_YWPN_COSTRLFIXED_W f,
             TF_YWPN_COSTRLWATER_W w
        where to_char(f.UP_DATE, 'yyyy-mm') = to_char(w.UP_DATE, 'yyyy-mm')
          and f.MARK = w.MARK
          and f.MARK = 1
          and to_char(f.UP_DATE, 'yyyy') = #{year}
        order by month
    </select>

    <select id="getEveryCostTwoQue" resultType="map">
        select sum(SZYSFCB_TWO),
               sum(DFCB_TWO),
               sum(ZSCLCB_TWO),
               sum(GSL_TWO),
               to_char(f.UP_DATE, 'Q') que
        from TF_YWPN_COSTRLFIXED_W f,
             TF_YWPN_COSTRLWATER_W w
        where to_char(f.UP_DATE, 'yyyy-mm') = to_char(w.UP_DATE, 'yyyy-mm')
          and f.MARK = w.MARK
          and f.MARK = 1
          and to_char(f.UP_DATE, 'yyyy') = #{year}
        group by to_char(f.UP_DATE, 'Q')
        order by que
    </select>

    <select id="getEveryCostAll" resultType="map">
        select sum(YSFCB)         YSFCB,
               sum(SZYSFCB_TOTAL) SZYSFCB_TOTAL,
               sum(DFCB_TOTAL)    DFCB_TOTAL,
               sum(ZSCLCB_TOTAL)  ZSCLCB_TOTAL,
               sum(GSL_TOTAL)     GSL_TOTAL
        from TF_YWPN_COSTRLFIXED_W f,
             TF_YWPN_COSTRLWATER_W w
        where to_char(f.UP_DATE, 'yyyy') = to_char(w.UP_DATE, 'yyyy')
          and f.MARK = w.MARK
          and f.MARK = 1
          and to_char(f.UP_DATE, 'yyyy') = #{year}
    </select>

    <select id="viewWarnInfo" resultType="com.tofly.xrtygis.entity.Warnhistory">
        select id,
               itcd,
               warn_type,
               isloc,
               notes,
               address,
               duration,
               num,
               isconfirm,
               isclear,
               confirm_time,
               confirm_user,
               scada_time,
               create_time
        from TF_YWPN_WARNHISTORY_W_VIEW
        where ID = #{id}
    </select>

    <select id="getEveryCostAllMonth" resultType="map">
        select YSFCB,
               SZYSFCB_TOTAL,
               DFCB_TOTAL,
               ZSCLCB_TOTAL,
               GSL_TOTAL,
               to_char(f.UP_DATE, 'yyyy-mm') month
        from TF_YWPN_COSTRLFIXED_W f,
             TF_YWPN_COSTRLWATER_W w
        where to_char(f.UP_DATE, 'yyyy-mm') = to_char(w.UP_DATE, 'yyyy-mm')
          and f.MARK = w.MARK
          and f.MARK = 1
          and to_char(f.UP_DATE, 'yyyy') = #{year}
        order by month
    </select>

    <select id="getEveryCostAllQue" resultType="map">
        select sum(YSFCB),
               sum(SZYSFCB_TOTAL),
               sum(DFCB_TOTAL),
               sum(ZSCLCB_TOTAL),
               sum(GSL_TOTAL),
               to_char(f.UP_DATE, 'Q') que
        from TF_YWPN_COSTRLFIXED_W f,
             TF_YWPN_COSTRLWATER_W w
        where to_char(f.UP_DATE, 'yyyy-mm') = to_char(w.UP_DATE, 'yyyy-mm')
          and f.MARK = w.MARK
          and f.MARK = 1
          and to_char(f.UP_DATE, 'yyyy') = #{year}
        group by to_char(f.UP_DATE, 'Q')
        order by que
    </select>

    <select id="allFixed" resultType="map">
        select DWCB_TOTAL fixed,
        to_char(f.UP_DATE,'yyyy-mm') month
        from TF_YWPN_COSTRLFIXED_W f
        where f.MARK = 1
        <if test="code == 0">
            and to_char(f.UP_DATE, 'yyyy') = #{year}
        </if>
        <if test="code == 2">
            and to_char(f.UP_DATE, 'yyyy') between to_char(add_months(to_date(#{year},'yyyy'),-36),'yyyy') and #{year}
        </if>
        order by month
    </select>

    <select id="allFixedYear" resultType="map">
        select trunc((nvl(sum(ZFY), 0) / nullif(sum(GSL_TOTAL), 0)) * 1000, 3) info,
               to_char(f.UP_DATE, 'yyyy')                                      year
        from TF_YWPN_COSTRLFIXED_W f
                 full join TF_YWPN_COSTRLWATER_W w on to_char(f.UP_DATE, 'yyyy') = to_char(f.UP_DATE, 'yyyy')
            and f.MARK = w.MARK
        where f.MARK = 1
        and extract(year from f.UP_DATE) = #{year}
        group by to_char(f.UP_DATE, 'yyyy')
    </select>

    <select id="allFixedPlan" resultType="map">
        select DWCB_TOTAL fixed,
        f.MOUTH month
        from TF_YWPN_COSTBGFIXED_W f,
        TF_YWPN_COSTBGWATER_W w
        where f.MOUTH = w.MONTH
        <if test="code == 0">
            and substr(f.MOUTH,1,4) = #{year}
        </if>
        <if test="code == 2">
            and substr(f.MOUTH,1,4) between to_char(add_months(to_date(#{year},'yyyy'),-36),'yyyy') and #{year}
        </if>
        order by month
    </select>

    <select id="allFixedPlanYear" resultType="map">
        select trunc((nvl(sum(ZFY),0) / nullif(sum(GSL_TOTAL),0)) *1000,3) info,
        substr(MOUTH,1,4) year
        from TF_YWPN_COSTBGFIXED_W f,
        TF_YWPN_COSTBGWATER_W w
        where f.MOUTH = w.MONTH
        <if test="code == 0">
            and substr(f.MOUTH,1,4) = #{year}
        </if>
        <if test="code == 2">
            and substr(f.MOUTH,1,4) between to_char(add_months(to_date(#{year},'yyyy'),-36),'yyyy') and #{year}
        </if>
        group by substr(MOUTH,1,4)
    </select>

    <select id="allFixedQue" resultType="map">
        select trunc((nvl(sum(ZFY), 0) / nullif(sum(GSL_TOTAL), 0)) * 1000, 3) fixed,
               to_char(f.UP_DATE, 'Q')                                         que
        from TF_YWPN_COSTRLFIXED_W f,
             TF_YWPN_COSTRLWATER_W w
        where to_char(f.UP_DATE, 'yyyy') = to_char(f.UP_DATE, 'yyyy')
          and f.MARK = w.MARK
          and f.MARK = 1
          and to_char(f.UP_DATE, 'yyyy') = #{year}
        group by to_char(f.UP_DATE, 'Q')
        order by que
    </select>

    <select id="allFixedQuePlan" resultType="map">
        select trunc((nvl(sum(ZFY), 0) / nullif(sum(GSL_TOTAL), 0) * 1000), 3) fixed,
               to_char(to_date(f.MOUTH, 'yyyy-mm'), 'Q')                       que
        from TF_YWPN_COSTBGFIXED_W f,
             TF_YWPN_COSTBGWATER_W w
        where f.MOUTH = w.MONTH
          and substr(f.MOUTH, 1, 4) = #{year}
        group by to_char(to_date(f.MOUTH, 'yyyy-mm'), 'Q')
        order by que
    </select>

    <update id="changeHistory">
        update ${table}
        <set>
            ISCONFIRM = 1,
            CONFIRM_USER = #{userId},
            <if test="close != null">
                ISCLEAR = 1
            </if>
        </set>
        where id = #{id}

    </update>

    <select id="viewWarnHistory" resultType="date">
        select SCADA_TIME
        from TF_YWPN_WARNHISTORY_W_VIEW
        where ID = #{id}
    </select>

    <select id="getSumIndexInfo" resultType="double">
        select sum(ITNUM_VAL) itval
        from TF_YWPN_SCADAREALTIME_R
        where ITCD in
        <foreach collection="list" item="index" open="(" close=")" separator=",">
            #{index}
        </foreach>

    </select>

    <select id="getNrwByYear" resultType="map">
        select trunc(((nvl(sum(GSL_TOTAL), 0) +
                       nvl(sum(GSL_QBJ), 0) -
                       nvl(sum(SSL_TOTAL), 0)) /
                      nullif(nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0), 0) * 100),
                     4)                   info,
               extract(year from UP_DATE) year
        from TF_YWPN_COSTRLWATER_W
        where extract(year from UP_DATE) = #{year}
          and MARK = '1'
        group by extract(year from UP_DATE)
    </select>

    <select id="getNrwPlanByYear" resultType="map">
        select trunc(((nvl(sum(GSL_TOTAL), 0) +
                       nvl(sum(GSL_QBJ), 0) -
                       nvl(sum(SSL_TOTAL), 0)) /
                      nullif(nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0), 0) * 100),
                     4)            info,
               substr(MONTH, 1, 4) year
        from TF_YWPN_COSTBGWATER_W
        where substr(MONTH, 1, 4) = #{year}
        group by substr(MONTH, 1, 4)
    </select>

    <select id="getLeakageByYear" resultType="map">
        select trunc(((nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0) - nvl(sum(SSL_TOTAL), 0) -
                       nvl(sum(WSPSL), 0)) / nullif(nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0), 0) * 100),
                     4)                   info,
               extract(year from UP_DATE) year
        from TF_YWPN_COSTRLWATER_W
        where extract(year from UP_DATE) = #{year}
          and MARK = '1'
        group by extract(year from UP_DATE)
    </select>

    <select id="getLeakagePlanByYear" resultType="map">
        select trunc(((nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0) - nvl(sum(SSL_TOTAL), 0) -
                       nvl(sum(WSPSL), 0)) / nullif(nvl(sum(GSL_TOTAL), 0) + nvl(sum(GSL_QBJ), 0), 0) * 100),
                     4)            info,
               substr(MONTH, 1, 4) year
        from TF_YWPN_COSTBGWATER_W
        where substr(MONTH, 1, 4) = #{year}
        group by substr(MONTH, 1, 4)
    </select>

    <select id="getSupSellByYear" resultType="map">
        select nvl(sum(GSL_TOTAL), 0)     gsl,
               nvl(sum(SSL_TOTAL), 0)     ssl,
               extract(year from UP_DATE) year
        from TF_YWPN_COSTRLWATER_W
        where MARK = '1'
          and extract(year from UP_DATE) = #{year}
        group by extract(year from UP_DATE)
    </select>

    <select id="getSupSellPlanByYear" resultType="map">
        select nvl(sum(GSL_TOTAL), 0) gsl,
               nvl(sum(SSL_TOTAL), 0) ssl,
               substr(MONTH, 1, 4)    year
        from TF_YWPN_COSTBGWATER_W
        where substr(MONTH, 1, 4) = #{year}
        group by substr(MONTH, 1, 4)
    </select>
    <select id="getWarnInfo" resultType="map">
        select (select cname from TF_YWPN_SYSCHILDCODE_D where PCID = 845 and CCODE = WARN_TYPE) type_name,
        ADDRESS,
        itcd,
        WARN_TYPE,
        case
        when WARN_TYPE = 2
        then null
        else (select itval from TF_YWPN_SCADAREALTIME_R where w.ITCD = itcd)
        end new_val,
        round((sysdate - SCADA_TIME) * 24 * 60, 2) interval_time,
        case
        when WARN_TYPE = 2
        then null
        else (select stnm
        from TF_YWPN_SCADASTATION_B
        where id = (select STATION_ID from TF_YWPN_SCADASTAITEM_W where itcd = w.ITCD))
        end station_name,
        SCADA_TIME
        from TF_YWPN_WARN_W w
        where to_char(SCADA_TIME,'yyyy-mm-dd') between #{start} and #{end}
        <if test="type != null">
            and WARN_TYPE = #{type}
        </if>

    </select>
</mapper>