<?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.zjrs.zwnw.fxfk.entity.QyywjkMapper">

    <select id="getQyywzstByDate" parameterType="com.zjrs.zwnw.fxfk.dto.QyywzstInputDTO" resultType="com.zjrs.zwnw.fxfk.dto.QyywzstOutputDTO">
        select to_char(TRUNC(SYSDATE-6),'mm-dd') lb,
        nvl(sum(case when substr(v.sqsj,0,8) = to_char(TRUNC(SYSDATE-6),'yyyymmdd') then 1 else 0 end),0) sll,
        nvl(sum(case when substr(v.bjsj,0,8) = to_char(TRUNC(SYSDATE-6),'yyyymmdd') and v.status not in ('2') then 1 else 0 end),0) bjl
        from v_rs_slxx_process v
        where v.ssdq=#{ dto.ssdq }
    </select>

    <select id="getQyywzstByYear" parameterType="com.zjrs.zwnw.fxfk.dto.QyywzstInputDTO" resultType="com.zjrs.zwnw.fxfk.dto.QyywzstOutputDTO">
        select to_char(add_months(trunc(sysdate), -11),'yyyy-mm') lb,
        nvl(sum(case when substr(v.sqsj,0,6) = to_char(add_months(trunc(sysdate), -11),'yyyymm') then 1 else 0 end),0) sll,
        nvl(sum(case when substr(v.bjsj,0,6) = to_char(add_months(trunc(sysdate), -11),'yyyymm') and v.status not in ('2') then 1 else 0 end),0) bjl
        from v_rs_slxx_process v
        where v.ssdq=#{ dto.ssdq }
    </select>

    <select id="getTop5" parameterType="com.zjrs.zwnw.fxfk.dto.QyywTop5InputDTO" resultType="com.zjrs.zwnw.fxfk.dto.QyywTop5OutputDTO">
        select *
        from (select v.sxmc name, count(1) cnt
        from v_rs_slxx_process v
        where v.ssdq = #{ dto.ssdq }
        <if test='dto.sj == "7"'>
            and v.sqsj &gt; to_char(TRUNC(SYSDATE-7),'yyyymmdd')||'240000'
        </if>
        group by
        <if test='dto.lb == "1"'>
            v.sxmc
        </if>
        <if test='dto.lb != "1"'>
            v.zbks
        </if>
        order by cnt desc)
        where rownum &lt; 6
    </select>
    <select id="getYwltj" parameterType="com.zjrs.zwnw.fxfk.dto.QyywTjInputDTO" resultType="com.zjrs.zwnw.fxfk.dto.QyywTjOutputDTO">
        select
        todaysll,'' todaysllbl,
        todaybjl,'' todaybjlbl,
        weeksll,'' weeksllbl,
        weekbjl,'' weekbjlbl,
        monthsll,'' monthsllbl,
        monthbjl,'' monthbjlbl
        from (select
        nvl(sum(1),0) yesterdaysll,
        nvl(sum(1),0) yesterdaybjl,
        nvl(sum(1),0) todaysll,
        nvl(sum(1),0) todaybjl,
        nvl(sum(1),0) preweeksll,
        nvl(sum(1),0) preweekbjl,
        nvl(sum(1),0) weeksll,
        nvl(sum(1),0) weekbjl,
        nvl(sum(1),0) premonthsll,
        nvl(sum(1),0) premonthbjl,
        nvl(sum(1),0) monthsll,
        nvl(sum(1),0) monthbjl
        from v_rs_slxx_process v where v.ssdq = #{ dto.ssdq })
    </select>

</mapper>
