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

    <select id="getCqbjjk" parameterType="com.zjrs.zwnw.fxfk.dto.YcywTjInputDTO" resultType="com.zjrs.zwnw.fxfk.dto.YcywCqbjjkOutputDTO">
        select weekcq,preweekcq,(case  when preweekcq = 0 then  '-' else round((weekcq - preweekcq)/preweekcq * 100, 2) || '%' end) weekcqbl,
        monthscq,premonthcq,(case  when premonthcq = 0 then  '-' else round((monthscq - premonthcq)/premonthcq * 100, 2) || '%' end) monthscqbl
        from (select
        nvl(sum(case when substr(v.bjsj,0,8) &gt;= to_char(trunc(sysdate, 'd')-6,'yyyymmdd') and substr(v.sqsj,0,8) &lt;= to_char(trunc(sysdate, 'd'),'yyyymmdd') and v.bjzt='-1' then 1 else 0 end),0) preweekcq,
        nvl(sum(case when substr(v.bjsj,0,8) &gt;= to_char(trunc(sysdate, 'd')+1,'yyyymmdd') and v.bjzt='-1' then 1 else 0 end),0) weekcq,
        nvl(sum(case when substr(v.bjsj,0,6) =to_char(add_months(trunc(sysdate), -1), 'yyyymm') and v.bjzt='-1' then 1 else 0 end),0) premonthcq,
        nvl(sum(case when substr(v.bjsj,0,6) =to_char(trunc(sysdate), 'yyyymm') and v.bjzt='-1' then 1 else 0 end),0) monthscq
        from v_rs_slxx_process v where v.ssdq = #{ dto.ssdq })
    </select>
    <select id="getTop5" parameterType="com.zjrs.zwnw.fxfk.dto.YcywTjInputDTO" resultType="com.zjrs.zwnw.fxfk.dto.YcywTop5OutputDTO">
        select *
        from (select
        v.zbks name, count(1) cnt
        from v_rs_slxx_process v
        where v.ssdq = #{ dto.ssdq }
        group by
        v.zbks
        order by cnt desc)
        where rownum &lt; 6
    </select>

    <select id="getZbcqtj" parameterType="com.zjrs.zwnw.fxfk.dto.YcywTjInputDTO" resultType="com.zjrs.zwnw.fxfk.dto.YcywTop5OutputDTO">
        select id code,AREA_NAME name, nvl(cnt, 0) cnt
        from ((select ID, decode(ID,'440840','经开区',AREA_NAME) AREA_NAME
        from s_sg_area
        where AREA_CODE like '4408%'
        and length(AREA_CODE) = 6) a left join
        (select v.ssdq, count(1) cnt
        from v_rs_slxx_process v
        where v.yjzt = '02'
        group by v.ssdq) b on a.id = b.ssdq)
    </select>
    <select id="getZbcqjk" parameterType="com.zjrs.zwnw.fxfk.dto.YcywTjInputDTO" resultType="com.zjrs.zwnw.fxfk.dto.YcywZbcqjkOutputDTO">
        select v.slbh,v.sxmc,v.zbks,
        (select ACTION_DEF_NAME from wf_work_item wf where wf.bae007=v.bae007 and wf.state='2' and rownum=1) dqhj,
        nvl(abs(v.cqts),'0') ||'工作日' cqts
        from v_rs_slxx_process v
        where v.ssdq = #{ dto.ssdq }
        order by to_number(abs(v.cqts)) desc
    </select>
    <select id="getYjywjk" parameterType="com.zjrs.zwnw.fxfk.dto.YcywTjInputDTO" resultType="com.zjrs.zwnw.fxfk.dto.YcywZbcqjkOutputDTO">
        select v.slbh,v.sxmc,v.zbks,
        (select ACTION_DEF_NAME from wf_work_item wf where wf.bae007=v.bae007 and wf.state='2' and rownum=1) dqhj,
        nvl(abs(v.cqts),'0') ||'工作日' cqts
        from v_rs_slxx_process v
        where v.ssdq = #{ dto.ssdq }
        order by to_number(abs(v.cqts)) desc
    </select>
</mapper>
