<?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.goldenwater.yjjt.mapper.YjjtCountMapper">

    <!-- 按年份和单位统计数据条数 -->
    <select id="selectCount" resultType="com.goldenwater.yjjt.domain.YjjtCount">
        select ty, year, addvcd, count as count from (
            select 'SL' as ty,
                   TO_CHAR(spt, 'YYYY') as year,
                   UPPER(addvcd) as addvcd,
                   count(1) as count
            from gx_yjjt_sl_data
            <where>
                <if test="year != null and year != ''">
                    AND TO_CHAR(spt, 'YYYY') = #{year}
                </if>
                <if test="month != null and month != ''">
                    AND TO_CHAR(spt, 'MM') = #{month}
                </if>
                <if test="addvcd != null and addvcd != ''">
                    AND UPPER(addvcd) = UPPER(#{addvcd})
                </if>
            </where>
            group by TO_CHAR(spt, 'YYYY'), UPPER(addvcd)
            union all
            select 'SZ' as ty,
                   TO_CHAR(spt, 'YYYY') as year,
                   UPPER(addvcd) as addvcd,
                   count(1) as count
            from gx_yjjt_sz_data
            <where>
                <if test="year != null and year != ''">
                    AND TO_CHAR(spt, 'YYYY') = #{year}
                </if>
                <if test="month != null and month != ''">
                    AND TO_CHAR(spt, 'MM') = #{month}
                </if>
                <if test="addvcd != null and addvcd != ''">
                    AND UPPER(addvcd) = UPPER(#{addvcd})
                </if>
            </where>
            group by TO_CHAR(spt, 'YYYY'), UPPER(addvcd)
        ) order by year desc, ty
    </select>

    <!-- 按年份和单位统计站点监测天数 -->
    <select id="selectStcdCount" resultType="com.goldenwater.yjjt.domain.YjjtCount">
        select ty, year, addvcd, count(distinct stcd || '_' || dt) as stcdNm from (
            select 'SL' as ty,
                   TO_CHAR(spt, 'YYYY') as year,
                   UPPER(addvcd) as addvcd,
                   stcd,
                   TO_CHAR(spt, 'YYYY-MM-DD') as dt
            from gx_yjjt_sl_data
            <where>
                <if test="year != null and year != ''">
                    AND TO_CHAR(spt, 'YYYY') = #{year}
                </if>
                <if test="month != null and month != ''">
                    AND TO_CHAR(spt, 'MM') = #{month}
                </if>
                <if test="addvcd != null and addvcd != ''">
                    AND UPPER(addvcd) = UPPER(#{addvcd})
                </if>
            </where>
            union all
            select 'SZ' as ty,
                   TO_CHAR(spt, 'YYYY') as year,
                   UPPER(addvcd) as addvcd,
                   stcd,
                   TO_CHAR(spt, 'YYYY-MM-DD') as dt
            from gx_yjjt_sz_data
            <where>
                <if test="year != null and year != ''">
                    AND TO_CHAR(spt, 'YYYY') = #{year}
                </if>
                <if test="month != null and month != ''">
                    AND TO_CHAR(spt, 'MM') = #{month}
                </if>
                <if test="addvcd != null and addvcd != ''">
                    AND UPPER(addvcd) = UPPER(#{addvcd})
                </if>
            </where>
        ) group by ty, year, addvcd
        order by year desc, ty
    </select>

    <!-- 按站点统计水量数据条数 -->
    <select id="selectStcdSLCount" resultType="com.goldenwater.yjjt.domain.YjjtCount">
        select stcd, count(1) as count,
               UPPER(addvcd) as addvcd,
               TO_CHAR(spt, 'YYYY') as year
        from gx_yjjt_sl_data
        <where>
            <if test="year != null and year != ''">
                AND TO_CHAR(spt, 'YYYY') = #{year}
            </if>
            <if test="addvcd != null and addvcd != ''">
                AND UPPER(addvcd) = UPPER(#{addvcd})
            </if>
        </where>
        group by stcd, UPPER(addvcd), TO_CHAR(spt, 'YYYY')
        order by count desc
    </select>

    <!-- 按站点统计水质数据条数 -->
    <select id="selectStcdSZCount" resultType="com.goldenwater.yjjt.domain.YjjtCount">
        select stcd, count(1) as count,
               UPPER(addvcd) as addvcd,
               TO_CHAR(spt, 'YYYY') as year
        from gx_yjjt_sz_data
        <where>
            <if test="year != null and year != ''">
                AND TO_CHAR(spt, 'YYYY') = #{year}
            </if>
            <if test="addvcd != null and addvcd != ''">
                AND UPPER(addvcd) = UPPER(#{addvcd})
            </if>
        </where>
        group by stcd, UPPER(addvcd), TO_CHAR(spt, 'YYYY')
        order by count desc
    </select>

    <!-- 按年份和单位统计监测频次 -->
    <select id="selectnum" resultType="com.goldenwater.yjjt.domain.YjjtCount">
        select ty, year, addvcd, sum(cnt) as count from (
            select 'SL' as ty,
                   TO_CHAR(spt, 'YYYY') as year,
                   UPPER(addvcd) as addvcd,
                   count(distinct TO_CHAR(spt, 'YYYY-MM-DD')) as cnt
            from gx_yjjt_sl_data
            <where>
                <if test="year != null and year != ''">
                    AND TO_CHAR(spt, 'YYYY') = #{year}
                </if>
                <if test="month != null and month != ''">
                    AND TO_CHAR(spt, 'MM') = #{month}
                </if>
                <if test="addvcd != null and addvcd != ''">
                    AND UPPER(addvcd) = UPPER(#{addvcd})
                </if>
            </where>
            group by TO_CHAR(spt, 'YYYY'), UPPER(addvcd)
            union all
            select 'SZ' as ty,
                   TO_CHAR(spt, 'YYYY') as year,
                   UPPER(addvcd) as addvcd,
                   count(distinct TO_CHAR(spt, 'YYYY-MM-DD')) as cnt
            from gx_yjjt_sz_data
            <where>
                <if test="year != null and year != ''">
                    AND TO_CHAR(spt, 'YYYY') = #{year}
                </if>
                <if test="month != null and month != ''">
                    AND TO_CHAR(spt, 'MM') = #{month}
                </if>
                <if test="addvcd != null and addvcd != ''">
                    AND UPPER(addvcd) = UPPER(#{addvcd})
                </if>
            </where>
            group by TO_CHAR(spt, 'YYYY'), UPPER(addvcd)
        ) group by ty, year, addvcd
        order by year desc, ty
    </select>

    <!-- 按日期范围统计每日水量和水质数据量（用于趋势图） -->
    <select id="selectDailySum" resultType="java.util.Map">
        select TO_CHAR(d.dt, 'YYYY-MM-DD') as dt,
               NVL(sl.sl_count, 0) as sl_count,
               NVL(sz.sz_count, 0) as sz_count
        from (
            select TRUNC(TO_DATE(#{beginDate}, 'YYYY-MM-DD')) + LEVEL - 1 as dt
            from dual
            CONNECT BY LEVEL &lt;= (TO_DATE(#{endDate}, 'YYYY-MM-DD') - TO_DATE(#{beginDate}, 'YYYY-MM-DD') + 1)
        ) d
        left join (
            select TRUNC(spt) as spt_date, count(1) as sl_count
            from gx_yjjt_sl_data
            where spt &gt;= TO_DATE(#{beginDate}, 'YYYY-MM-DD')
            and spt &lt;= TO_DATE(#{endDate} || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
            group by TRUNC(spt)
        ) sl on d.dt = sl.spt_date
        left join (
            select TRUNC(spt) as spt_date, count(1) as sz_count
            from gx_yjjt_sz_data
            where spt &gt;= TO_DATE(#{beginDate}, 'YYYY-MM-DD')
            and spt &lt;= TO_DATE(#{endDate} || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
            group by TRUNC(spt)
        ) sz on d.dt = sz.spt_date
        order by d.dt
    </select>

</mapper>
