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

    <select id="getZbtop10List" parameterType="com.zjrs.zwnw.fxfk.dto.Zbtop10DTO" resultType="com.zjrs.zwnw.fxfk.dto.Zbtop10ListDTO">
        select *
        from (select sxmc, count(1) cnt
        from v_rs_slxx_process a
        where a.status = '2'
        and a.ssdq = #{ dto.ssdq }
        group by a.sxmc order by cnt desc)
        where rownum &lt; 11
    </select>
    <select id="getFbqsSllBy1Month" resultType="com.zjrs.zwnw.fxfk.dto.FbqssllListDTO">
        select 'sll' lb, to_char(count(1)) cnt
        from v_rs_slxx_process v where v.sqsj &gt; to_char(add_months(trunc(sysdate), -1), 'yyyymmdd') || '240000'
    </select>

    <select id="getFbqsSllBy3Month" resultType="com.zjrs.zwnw.fxfk.dto.FbqssllListDTO">
        select 'sll' lb, to_char(count(1)) cnt
        from v_rs_slxx_process v where v.sqsj &gt; to_char(add_months(trunc(sysdate), -3), 'yyyymmdd') || '240000'
    </select>

    <select id="getFbqsSllBy1Week" resultType="com.zjrs.zwnw.fxfk.dto.FbqssllListDTO">
        select 'sll' lb, to_char(count(1)) cnt from v_rs_slxx_process v where v.sqsj &gt; to_char(TRUNC(SYSDATE - 7),'yyyymmdd')||'240000'
  </select>
    <select id="getFbqsSllDqBy1Month" resultType="com.zjrs.zwnw.fxfk.dto.FbqssllDqListDTO">
        select '湛江市' dq ,nvl(dqsll,0) dqsll,
        nvl(gtjbsll,0) gtjbsll,(case  when  dqsll = 0 then  '-' else round(gtjbsll/dqsll * 100, 2) || '%' end) gtjbsllbl,
        nvl(ggfwsll,0) ggfwsll,(case when  dqsll = 0 then '-' else round(ggfwsll/dqsll * 100, 2) || '%' end) ggfwsllbl,
        nvl(xcxsll,0) xcxsll,(case when dqsll = 0 then '-' else round(xcxsll/dqsll * 100, 2) || '%' end) xcxsllbl,
        nvl(dqbjl,0) dqbjl,nvl(gtjbbjl,0) gtjbbjl, (case when dqbjl = 0 then '-' else round(gtjbbjl/dqbjl * 100, 2) || '%' end) gtjbbjlbl,
        nvl(ggfwbjl,0) ggfwbjl, (case when dqbjl = 0 then '-' else round(ggfwbjl/dqbjl * 100, 2) || '%' end) ggfwbjlbl,
        nvl(xcxbjl,0) xcxbjl,  (case when dqbjl = 0 then '-' else round(xcxbjl/dqbjl * 100, 2) || '%' end) xcxbjlbl
        from (select
        nvl(sum(1),0) dqsll,
        nvl(sum(1),0) dqbjl,
        nvl(sum(1),0) gtjbsll,
        nvl(sum(1),0) gtjbbjl,
        nvl(sum(1),0) ggfwsll,
        nvl(sum(1),0) ggfwbjl,
        nvl(sum(1),0) xcxsll,
        nvl(sum(1),0) xcxbjl
        from v_rs_slxx_process v where v.ssdq = '440800')
    </select>

    <select id="getFbqsSllDqBy3Month" resultType="com.zjrs.zwnw.fxfk.dto.FbqssllDqListDTO">
        select '湛江市' dq ,nvl(dqsll,0) dqsll,
        nvl(gtjbsll,0) gtjbsll,(case  when  dqsll = 0 then  '-' else round(gtjbsll/dqsll * 100, 2) || '%' end) gtjbsllbl,
        nvl(ggfwsll,0) ggfwsll,(case when  dqsll = 0 then '-' else round(ggfwsll/dqsll * 100, 2) || '%' end) ggfwsllbl,
        nvl(xcxsll,0) xcxsll,(case when dqsll = 0 then '-' else round(xcxsll/dqsll * 100, 2) || '%' end) xcxsllbl,
        nvl(dqbjl,0) dqbjl,nvl(gtjbbjl,0) gtjbbjl, (case when dqbjl = 0 then '-' else round(gtjbbjl/dqbjl * 100, 2) || '%' end) gtjbbjlbl,
        nvl(ggfwbjl,0) ggfwbjl, (case when dqbjl = 0 then '-' else round(ggfwbjl/dqbjl * 100, 2) || '%' end) ggfwbjlbl,
        nvl(xcxbjl,0) xcxbjl,  (case when dqbjl = 0 then '-' else round(xcxbjl/dqbjl * 100, 2) || '%' end) xcxbjlbl
        from (select
        nvl(sum(case when v.sqsj &gt; to_char(add_months(trunc(sysdate), -3), 'yyyymmdd') || '240000' then 1 else 0 end),0) dqsll,
        nvl(sum(case when v.bjsj &gt; to_char(add_months(trunc(sysdate), -3), 'yyyymmdd') || '240000' and v.status not in ('2') then 1 else 0 end),0) dqbjl,
        nvl(sum(case when v.sqsj &gt; to_char(add_months(trunc(sysdate), -3), 'yyyymmdd') || '240000' and v.sjly = '1' then 1 else 0 end),0) gtjbsll,
        nvl(sum(case when v.bjsj &gt; to_char(add_months(trunc(sysdate), -3), 'yyyymmdd') || '240000' and v.status not in ('2') and v.sjly = '1'  then 1
        else 0 end),0) gtjbbjl,
        nvl(sum(case when v.sqsj &gt; to_char(add_months(trunc(sysdate), -3), 'yyyymmdd') || '240000' and v.sjly = '2' then 1 else 0 end),0) ggfwsll,
        nvl(sum(case when v.bjsj &gt; to_char(add_months(trunc(sysdate), -3), 'yyyymmdd') || '240000' and v.status not in ('2') and v.sjly = '2'  then 1
        else 0 end),0) ggfwbjl,
        nvl(sum(case when v.sqsj &gt; to_char(add_months(trunc(sysdate), -3), 'yyyymmdd') || '240000' and v.sjly = '3' then 1
        else 0 end),0) xcxsll,
        nvl(sum(case when v.bjsj &gt; to_char(add_months(trunc(sysdate), -3), 'yyyymmdd') || '240000' and v.status not in ('2') and v.sjly = '3'  then 1
        else 0 end),0) xcxbjl
        from v_rs_slxx_process v where v.ssdq = '440800')
    </select>

    <select id="getFbqsSllDqBy1Week" resultType="com.zjrs.zwnw.fxfk.dto.FbqssllDqListDTO">
        select '湛江市' dq ,nvl(dqsll,0) dqsll,
        nvl(gtjbsll,0) gtjbsll,(case  when  dqsll = 0 then  '-' else round(gtjbsll/dqsll * 100, 2) || '%' end) gtjbsllbl,
        nvl(ggfwsll,0) ggfwsll,(case when  dqsll = 0 then '-' else round(ggfwsll/dqsll * 100, 2) || '%' end) ggfwsllbl,
        nvl(xcxsll,0) xcxsll,(case when dqsll = 0 then '-' else round(xcxsll/dqsll * 100, 2) || '%' end) xcxsllbl,
        nvl(dqbjl,0) dqbjl,nvl(gtjbbjl,0) gtjbbjl, (case when dqbjl = 0 then '-' else round(gtjbbjl/dqbjl * 100, 2) || '%' end) gtjbbjlbl,
        nvl(ggfwbjl,0) ggfwbjl, (case when dqbjl = 0 then '-' else round(ggfwbjl/dqbjl * 100, 2) || '%' end) ggfwbjlbl,
        nvl(xcxbjl,0) xcxbjl,  (case when dqbjl = 0 then '-' else round(xcxbjl/dqbjl * 100, 2) || '%' end) xcxbjlbl
        from (select
        nvl(sum(case when v.sqsj &gt; to_char(TRUNC(SYSDATE - 7),'yyyymmdd')||'240000' then 1 else 0 end),0) dqsll,
        nvl(sum(case when v.bjsj &gt; to_char(TRUNC(SYSDATE - 7),'yyyymmdd')||'240000' and v.status not in ('2') then 1 else 0 end),0) dqbjl,
        nvl(sum(case when v.sqsj &gt; to_char(TRUNC(SYSDATE - 7),'yyyymmdd')||'240000' and v.sjly = '1' then 1 else 0 end),0) gtjbsll,
        nvl(sum(case when v.bjsj &gt; to_char(TRUNC(SYSDATE - 7),'yyyymmdd')||'240000' and v.status not in ('2') and v.sjly = '1'  then 1
        else 0 end),0) gtjbbjl,
        nvl(sum(case when v.sqsj &gt; to_char(TRUNC(SYSDATE - 7),'yyyymmdd')||'240000' and v.sjly = '2' then 1 else 0 end),0) ggfwsll,
        nvl(sum(case when v.bjsj &gt; to_char(TRUNC(SYSDATE - 7),'yyyymmdd')||'240000' and v.status not in ('2') and v.sjly = '2'  then 1
        else 0 end),0) ggfwbjl,
        nvl(sum(case when v.sqsj &gt; to_char(TRUNC(SYSDATE - 7),'yyyymmdd')||'240000' and v.sjly = '3' then 1
        else 0 end),0) xcxsll,
        nvl(sum(case when v.bjsj &gt; to_char(TRUNC(SYSDATE - 7),'yyyymmdd')||'240000' and v.status not in ('2') and v.sjly = '3'  then 1
        else 0 end),0) xcxbjl
        from v_rs_slxx_process v where v.ssdq = '440800')
    </select>

    <select id="getQsywjk" resultType="com.zjrs.zwnw.fxfk.dto.QsywjkDTO">
        select *  from
        (select sum(1) qsljsl,
        sum(case when v.status not in ('2') then 1 else 0 end) qsljbj,
        sum(case when v.status in ('2') then 1 else 0 end) qsdqzb ,
        sum(case when v.status in ('2') then 1 else 0 end) zbywzl ,
        sum(case when v.status in ('2') and v.yjzt='1' then 1 else 0 end) zcyw ,
        sum(case when v.status in ('2') and v.yjzt='0' then 1 else 0 end) yjyw ,
        sum(case when v.status in ('2') and v.yjzt='-1' then 1 else 0 end) cqyw
        from v_rs_slxx_process v )
    </select>
    <select id="getQsywjkDq" resultType="com.zjrs.zwnw.fxfk.dto.QsywjkDqDTO">
        select '湛江市' dq ,nvl(ljsl,0) ljsl, nvl(ljbj,0) ljbj,nvl(dqzb,0) dqzb,
        (case  when ljsl = 0 then  '-' else round(ljbj/ljsl * 100, 2) || '%' end) ljbjbl,
        (case  when ljsl = 0 then  '-' else round(dqzb/ljsl * 100, 2) || '%' end) dqzbbl,
        nvl(zcyw,0) zcyw,nvl(yjyw,0) yjyw,nvl(cqyw,0) cqyw,
        (case  when dqzb = 0  then  '-' else round(cqyw/dqzb * 100, 2) || '%' end) cqywbl
        from (select
        nvl(sum(1),0) ljsl,
        nvl(sum(case when v.status not in ('2') then 1 else 0 end),0) ljbj,
        nvl(sum(case when v.status in ('2') then 1 else 0 end),0) dqzb,
        nvl(sum(case when v.status in ('2') and v.yjzt='1' then 1 else 0 end),0) zcyw,
        nvl(sum(case when v.status in ('2') and v.yjzt='0' then 1 else 0 end),0) yjyw,
        nvl(sum(case when v.status in ('2') and v.yjzt='-1' then 1 else 0 end),0) cqyw
        from v_rs_slxx_process v where v.ssdq = '440800')
    </select>
</mapper>
